[SQL]명령어 기본

[DDL]
CREATE, ALTER, DROP

-테이블 생성

create table 테이블명 (

필드명 데이터타입 [NOT NULL]

{, 필드명 데이터타입 [NOT NULL]}*

[,PRIMARY KEY (필드명)]   )

-테이블구조 및 속성변경

기존의 테이블구조 변경

1. 필드추가생성

ALTER TABLE 테이블명 ADD [COLUMN] 필드명 필드타입

예) ALTER TABLE userdb ADD COLUMN addr VARCHAR(100)

2. 필드속성변경

ALTER TABLE 테이블명 CHANGE [COLUMN] 기존필드명 새필드명 필드타입

예) ALTER TABLE userdb CHANGE COLUMN addr address VARCHAR(255)

3. 필드삭제

ALTER TABLE 테이블명 DROP [COLUMN] 필드명

예) ALTER TABLE userdb DROP COLUMN address

4. 기존의 테이블삭제

DROP TABLE 테이블명

[DML]

SELECT, INSERT, UPDATE, DELETE

[1. SELECT (데이터검색하기)]

SELECT [DISTINCT] 테이블명 {, 테이블명} * (AS 테이블명)

FROM 필드명 {,필드명} *

[WHERE 검색조건]

[ORDER BY 필드명[ASC or DESC] {,필드명 [ASC or DESC]}*]

[GROUP BY 필드명 {, 필드명}*]

[HAVING  검색조건]

1. 일반적인 검색

SELECT name, id FROM userdb WHERE level = 'B'

SELCET * FROM userdb

SELECT name, id, email, sex, milage, level FROM userdb

2. 결과레코드의 중복제거

SELECT level FROM userdb

SELECT DISTINCT level FROM userdb

3. 조건검색

SELECT name, id, email 

FROM userdb 

WHERE milage > 3000 AND sex = 'M'

4. 검색결과의 정렬

SELECT name, id, milage FROM userdb 

WHERE milage >= 3000 

ORDER BY milage DESC

5. 검색결과에 대한 산술계산 및 문자열처리

SELECT name, '님의 마일리지는 ', milage, '점입니다.' 

FROM userdb WHERE milage >= 3000 

ORDER BY milage

SELECT name, id, email, milage+300 FROM userdb

WHERE id = 'soony'

6. 그룹함수(group function)를 이용한 검색

SELECT count(*) FROM userdb 

WHERE sex = 'F'

SELECT avg(milage) FROM userdb 

WHERE sex='F'

7. GROUP BY를 이용한 검색

SELECT level, max(milage), min(milage), avg(milage) 

FROM userdb GROUP BY level

SELECT sex, max(milage), min(milage), avg(milage) 

FROM userdb GROUP BY sex

8. HAVING을 이용한 검색

SELECT sex, max(milage), min(milage), avg(milage)
FROM userdb
GROUP BY sex
HAVING sex = 'F'

9. BETWEEN 연산자를 이용한 검색

SELECT name, email, mailage, sex
FROM userdb
WHERE sex = 'F' AND milage BETWEEN 3000 AND 4000

SELECT name, email, milage, sex
FROM userdb
WHERE sex = 'F' AND milage >= 3000 AND milage <= 4000

SELECT name, sex, milage
FROM userdb
WHERE milage NOT IN (3300,3500,3700)

10. LIKE를 사용한 검색

SELECT name
FROM userdb
WHERE name, LIKE '%현%'

SELECT name, id,
FROM userdb
WHERE id LIKE 'm%'

11. NULL값을 갖는 데이터검색

SELECT name, id FROM userdb WHERE email IS NULL

SELECT name, id FROM userdb WHERE email IS NOT NULL

[2. INSERT (새로운 데이터의 삽입)]

INSERT INTO 테이블명 [(필드명{[, 필드명]}*)] VALUES (필드값)

INSERT INTO userdb (name, id, email, sex, milage, level)
VALUES ('이완철', 'yunka1018','sspark@kornet.net'.'M','3500','B')

INSERT INTO userdb
VALUES('이완철', 'yunka1018','sspark@kornet.net','M','3500','B')

INSERT INTO userdb (id, name)
VALUES('yunka1018','정양미')

[3. 데이터의 수정(UPDATE)]

UPDATE 테이블명
SET 필드명=필드값 또는 산술식 {, 필드명 = 필드값 또는 산술식}*
[WHERE 검색조건]

UPDATE userdb
SET sex = 'F'
WHERE name = '김현아'

UPDATE userdb
SET sex = 'F'

만일 위와 같이 WHERE절로 검색조건을 명시하지 않은 경우에는 해당테이블내의 모든레코드의 필드값이 설정한값으로 수정되므로 유의해야한다.

UPDATE userdb 
SET milage = milage + 300

[4. DELETE (데이터의 삭제) ]

DELETE FROM 테이블명
[WHERE 검색조건]

DELETE FROM userdb
WHERE id = 'king94'

DELETE FROM userdb

만일 위와 같이 검색조건을 지정하지 않으면 해당테이블내의 모든레코드가 삭제되므로 주의해야한다.

[ MYSQL 데이터베이스의 사용법 ]

1. MYSQL서버의 기동과 종료

이하 디렉토리위치는 /usr/local/mysql/bin이다.

./bin/safe_mysql &

2. MYSQL서버의 종료 및 재기동

safelab.skku.ac.kr.pid파일은 MYSQL의 PID를 저장하고 있다.

kill -9 PID

mysqladmin -u root  -p shutdown

mysqladmin -u root -p reload

3. mysqladmin 사용법

mysqladmin -u root  -p reload

mysqladmin -u root -p create sample

mysqladmin -u root -p drop sample

4. mysql 클라이언트사용법

mysql -u root -pphp99 mysql

mysql -u root -p mysql

mysql>show databases;

mysql>show tables;

mysql>show columns from db;

mysql>desc db;

mysql>use mysql;

mysql>select host, user, password from user;     //mysql db의 user란 테이블이 존재.

mysql>update user set password = password('php99') where user = 'root'; //비번변경

mysql>select host, user, password from user;

mysql>drop database sample;

5. 생성된 데이터베이스의 등록

mysql>insert into db values('%','sample','php','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql>select host, db, user from db;

6. 새로운 사용자(데이터베이스 소유자)등록

mysql>insert into user (host, user, password)
          values('localhost','php','password('php99'));

mysql>select host, user, password from user;

7. 테이블의 생성과 수정 및 삭제

mysql -u root -p sample

mysql>show tables;

mysql>desc userdb;

mysql>alter table userdb change column id varchar(12) NOT NULL;

mysql>desc userdb;

mysql>drop table userdb;

mysql>show tables;

8. *.sql파일로 쉘에서 SQL명령어 실행

mysql -u root -p php99  sample < userdb.sql

mysql -u php -p sample

mysql>show tables;

9. 데이터의 백업과 복구

mysqldump -u [DB사용자명] -p [백업할 데이터베이스명] > [외부로 저장할 파일명]

mysqldump -u php -p sample > sample_backup.sql

cat sample_backup.sql

mysql -u php -p php99 sample < sample_backup.sql

[ MySQL Dump 방법 및 복구 방법 ]

1. dump

전체 덤프

 -  $> mysqldump -u[사용자아이디] -p[패스워드] 데이터베이스명 > 저장될 파일명.sql
 - 예) mysqldump -udis -phmcdis dis > dis_081231.sql
   이렇게 하면 디비(dis)를 전체 덤프를 뜨게된다.

테이블만 덤프

 -  $> mysqldump -u[사용자아이디] -p[패스워드] 데이터베이스명 테이블명 > 저장될 파일명.sql
 - 예) mysqldump -udis -phmcdis dis termininal_info > dis_termininal_info_table.sql

2. 복구하기

  -  $> mysql -u[사용자아이디] -p[패스워드] [디비명] < 덤프파일명
  - 예) mysql -udis -p dis < dis_081231.sql
  - 예) mysql -udis -p dis termininal_info < dis_termininal_info_table.sql

3. 특정 db의 특정 table에서 원하는 값만 덤프받기

 - edu라는 디비에 a,b,c라는 테이블이 있다. 여기서 a라는 테이블에서 no가 7번이상이고 10번 이하인 값만 덤프하기
 - 사용되는 옵션 -w
 - 예) mysqldump -u mysql_admin -p edu a -w'no=>7 and no=<10' > edu_a_cond.sql  : 위와같이 하면 no가 7~10번까지가 덤프될것이다.

c:\>mysqldump -ucheckmate -p***** cfwdata trenddata -w"trendtime>'2009100711'" > d:\trenddata.sql

2. DataBase 스키마(Schema)만 백업받기

 - edu라는 디비의 모든 테이블 스키마를 백업받으려면
 - 예) mysqldump -u mysql_admin -p -d edu > edu_db.sql

 - edu라는 디비의 a라는 테이블 스키마를 백업받으려면
 - 예) mysqldump -u mysql_admin -p -d edu a> edu_a_table.sql

[출처] Mysql Dump 방법 및 복구 방법 (Developer Forum) |작성자 찐