WebDB(II) 시간에 입력한 예제프로그램들 입니다.
<?php $link = mysqli_connect("localhost", "i0058001", "yourPassword"); // MySQL 서버 연결 mysqli_select_db($link, "db0058001"); // Database 선택 mysqli_set_charset($link, "utf8"); // DB 문자셋 설정 $sql = ""; mysqli_query($link, $sql); // SQL 실행 mysqli_close($link); // MySQL 연결 종료 ?>
<?php $dbHost = "localhost"; $dbName = "db0058001"; $dbUser = "i0058001"; $dbPass = "yourPassword" ?>
<?php require './dbConn.php'; $link = mysqli_connect($dbHost, $dbUser, $dbPass); // MySQL 서버 연결 mysqli_select_db($link, $dbName); // Database 선택 mysqli_set_charset($link, "utf8"); // DB 문자셋 설정 $sql = "DROP TABLE `user`;"; echo $sql; mysqli_query($link, $sql); // SQL 실행 mysqli_close($link); // MySQL 연결 종료 ?>
CREATE TABLE `user` ( `userid` varchar(20) NOT NULL, `passwd` varchar(128) NOT NULL, `name` varchar(20) NOT NULL, `email` varchar(50) NOT NULL, PRIMARY KEY (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE `user`; INSERT INTO `user` (`userid`, `name`) VALUES ('test01', '홍길동') INSERT INTO `user` VALUES ('test01', '1111', '홍길동', 'test01@test.com'); INSERT INTO `user` (`userid`, `name`, `passwd`, `email`) VALUES ('test02', '김철수', password('1234'), 'test02@test.com'); INSERT INTO `user` VALUES ('test03', '3333', '김철수', 'test03@daum.net'), ('test04', '4444', '홍명보', 'test04@daum.net'); INSERT INTO `user` (`userid`, `passwd`, `name`, `email`) VALUES ('test05', password('3333'), '김대중', 'test05@naver.com'), ('test06', password('4444'), '김영삼', 'test06@naver.com'); SELECT `userid`, `name` FROM `user`; SELECT * FROM `user`; SELECT * FROM `user` WHERE `name` = '홍길동'; SELECT * FROM `user` WHERE `age` BETWEEN 20 AND 30; SELECT * FROM `user` WHERE `age` >= 20 AND `age` <= 30; SELECT * FROM `user` WHERE `name` IN ( '홍길동', '김철수', '전우치' ); SELECT * FROM `user` WHERE `name` = '홍길동' OR `name` = '김철수' OR `name` = '전우치'; SELECT * FROM `user` WHERE `name` LIKE '홍%'; SELECT * FROM `user` WHERE `userid` LIKE 't_____'; SELECT * FROM `user` WHERE `email` LIKE '%.com'; SELECT * FROM `user` WHERE `email` LIKE '%naver%'; SELECT * FROM `user` ORDER BY `name` , `userid`; SELECT DISTINCT `name`, `email` FROM `user`; SELECT COUNT(`userid`), `name` FROM `user` GROUP BY `name`; SELECT * FROM `user` LIMIT 1, 3; UPDATE `user` SET `name` = '고길동' WHERE `userid` = 'test05'; UPDATE `user` SET `age` = `age` + 1 UPDATE `user` SET `name` = '홍길동', `age` = `age` + 1 WHERE `userid` = 'test05'; DELETE FROM `user` WHERE `userid` = 'test05'; DELETE FROM `user`;