WebDB(II) 실습예제 소스 #1

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`;