★ MySQL DB SQL Statement

새로운 테이블의 생성(CREATE TABLE)
예제
CREATE TABLE userdb (
name CHAR(8),
id VARCHAR(10 NOT NULL,
email VARCHAR(40),
sex CHAR(1),
PRIMARY KEY(id)
)

# 특정 테이블을 다른 이름으로 똑같이 복사
CREATE TABLE userdb2 AS SELECT * FROM userdb

 

# 기존 테이블의 필요한 필드의 데이터만 가져와 테이블 생성
CREATE TABLE userdb3 AS SELECT name, id FROM userdb

생성된 테이블의 변경(ALTER TABLE)
ALTER TABLE userdb ADD COLUMN addr VARCHAR(100)
or
ALTER TABLE userdb ADD addr VARCHAR(100)

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

ALTER TABLE userdb DROP COLUMN address
or
ALTER TABLE userdb DROP address

 

# 생성된 테이블의 삭제(DROP TABLE)
DROP TABLE 테이블명
ex) DROP TABLE userdb

 

# 데이터 검색(SELECT)
SELECT name, id FROM userdb WHERE level = 'B'
SELECT * FROM userdb
SELECT name, id, email, sex, milage, level FROM userdb
SELECT DISTINCT level FROM userdb
SELECT name, id, email FROM userdb WHERE milage > 3000 AND sex = 'M'
SELECT name, id, milage FROM userdb WHERE milage >= 3000 ORDER BY milage DESC

SELECT name, id, milage FROM userdb WHERE milage >= 3000 ORDER BY milage ASC
or
SELECT name, id, milage FROM userdb WHERE milage >= 3000 ORDER BY milage

SELECT name, id, email, milage+300 FROM userdb WHERE id = 'soony'

SELECT count(*) FROM userdb WHERE sex = 'F'
SELECT avg(milage) FROM userdb WHERE sex = 'F'
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

SELECT sex, max(milage), min(milage), avg(milage) FROM userdb GROUP BY sex HAVING sex = 'F'
SELECT name, email, milage, 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, level FROM userdb WHERE level IN ('A', 'C')
SELECT name, sex, milage FROM userdb WHERE milage NOT IN (3300, 3500, 3700)

 

# LIKE 를 사용한 검색
SELECT name FROM userdb WHERE name LIKE '%현%'
SELECT name FROM userdb WHERE name LIKE '%현%'
SELECT name, id FROM userdb WHERE id LIKE 'm%'

 

# NULL 값을 갖는 데이터 검색
SELECT name, id FROM userdb WHERE email IS NULL
SELECT name, id FROM userdb WHERE email IS NOT NULL

 

# LIMIT 연산자를 이용한 검색
SELECT name, id, milage FROM userdb LIMIT 3
SELECT name, id, email FROM userdb WHERE milage > 3000 LIMIT 2
SELECT name, id, milage FROM userdb ORDER BY milage DESC LIMIT 3

 

# 1번 레코드부터 시작하여 3개의 레코드를 가져오라
# 레코드 시작은 0번째 레코드부터 시작된다.
SELECT name, id, milage FROM userdb ORDER BY milage DESC LIMIT 1, 3

 

# 새로운 데이터의 삽입(INSERT)
INSERT INTO userdb (name, id, email, sex, milage, level) VALUES('이완철','yunkal018','wclee@safelab.skku.ac.kr','M',3500,'B')
INSERT INTO userdb VALUES('이완철','yunkal018','wclee@safelab.skku.ac.kr','M',3500,'B')
INSERT INTO userdb (id, name) VALUES('hyunah', '김현아')
INSERT INTO userdb (id, name) VALUES(hyunah, 김현아)

 

# 데이터의 수정(UPDATE)
UPDATE userdb SET sex = 'F' WHERE name = '김현아'
UPDATE userdb SET sex = 'F'
UPDATE userdb SET milage = milage + 300

 

# 데이터의 삭제(DELETE)
DELETE FROM userdb WHERE id = 'king94'
DELETE FROM userdb // userdb 테이블의 모든 데이터가 삭제된다.

 

# MySQL 데이터베이스의 사용법

# MySQL 서버의 기동과 종료
./bin/safe_mysqld &

 

# MySQL 서버의 종료
./bin/mysqladmini -u root shutdown

 

# 실행중인 MySQL 서버의 재기동
./bin/mysqladmin -u root reload

 

# mysql client 사용법
[root@localhost /root] # mysql
mysql > show databases; // 모든 명령문은 세미콜론(;) 이나 "\g"로 끝나야 한다.
mysql > \q

[root@localhost /root] # mysql mysql // mysql 데이터베이스로 접속
mysql > show tables; // mysql데이터베이스 내의 테이블리스트를 출력한다.
------------------
 Tables_in_mysql
------------------
 columns_priv
 db
 func
 host
 tables_priv
 user
------------------
mysql > show colums from db; // 테이블 db내의 열(필드)에 대한 정보를 출력한다.
mysql > desc db; // 위 명령과 동일한 정보가 출력된다.

mysql > select host,user,password from user;

mysql > update user set password = password('php2001') where user = 'root';

mysql > Select host, user, password from user;

[root@localhost /root]# mysqladmin -u root reload // mysql 서버 재기동
[root@localhost /root]# mysql -u root mysql // 암호 입력없으면 실패
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
[root@localhost /root]# mysql -u root -p mysql
Enter password : // 여기에 'php2001'을 입력하면 연결된다.

[root@localhost /root]# mysqladmin -u root reload // "-p"가 없으면 연결거부
[root@localhost /root]# mysqladmin -u root -p reload
Enter password :                                  // 비밀번호를 입력한다.
[root@localhost /root]#                           // 정상적으로 재기동한다.

# 아래와 같이 root의 암호가 'php2001'이므로 -p 다음에 바로 'php2001'을 붙여서 입력한다.
[root@localhost /root]# mysql -u root -pphp2001 mysql // -p다음에 비밀번호를 입력
mysql >

#새로운 데이터베이스 생성
[root@localhost /root]# mysqladmin -u root -p create sample
Enter password :
[root@localhost /root]# mysql -u root -p sample
Enter password :
mysql> show tables;
Empty set (0.00 sec)

mysql>

# 생성된 데이터베이스 삭제
[root@localhost /root]# mysqladmin -u root -p drop sample // 데이터베이스 삭제
mysql> drop database sample;

# 생성된 데이터베이스 등록
mysql> insert into db values('%','sample','php','y','y','y','y','y','y','y','y','y','y');
mysql> select * from db;
mysql>

# 새로운 사용자(데이터베이스 소유자) 등록
mysql> insert into user (host, user, password) values ('localhost','php',password('php2001'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;

mysql> exit

# 테이블의 생성과 수정 및 삭제
[root@localhost /root]# mysql -u php -p sample
mysql> show tables;
Empty set (0.00 sec)

mysql>
mysql> desc userdb;
mysql>
# userdb 테이블에서 id 필드의 데이터 타입을 varchar(12)로 수정하자.
mysql> alter table userdb modify column id varchar(12) NOT NULL;
mysql> desc userdb;
mysql> drop table userdb;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)

# 먼저 다음의 sql 문을 만들어 userdb.sql 이라는 파일로 저장한다.
create table userdb (
  name CHAR(8),
  id VARCHAR(10) NOT NULL,
  email VARCHAR(40),
  sex CHAR(1),
  milage INT,
  level CHAR(1),
  PRIMARY KEY(id)
);

[root@localhost /root]# mysql -u php -pphp2001 sample < userdb.sql
[root@localhost /root]# mysql -u php -p sample
Enter password:
mysql> show tables;
------------------
 Tables_in_sample
------------------
 user_db
------------------
1 row in set (0.00 sec)

mysql>

 

# MySQL 에서 SQL 사용하기
[root@localhost /root]# mysql -u php - p sample
mysql> insert into userdb(name,id,email,sex,milage,level)
   -> values('이완철','yunka1018','wclee@safelab.skku.ac.kr','M',3500,'B');
Query OK, 1 row affected (0.03 sec)

mysql> insert into userdb(name,id,email,sex,milage,level)
   -> values('김영미','guncat','ymkim@safelab.skku.ac.kr','F',4200,'A');
Query OK, 1 row affected (0.03 sec)

mysql> insert into userdb(name,id,email,sex,milage,level)
   -> values('길수현','suhyun','shyun@safelab.skku.ac.kr','F',2800,'C');
Query OK, 1 row affected (0.03 sec)

mysql>

mysql> select * from userdb;

mysql>

mysql> select name, id from userdb where level = 'B';
mysql>

mysql> select distict level from userdb;
mysql> select name,id,email from userdb where milage >= 3000 and sex = 'M';
mysql> select name,id,milage from userdb where milage >= 3000 order by milage desc;
mysql> select name,'님의 마일리지는 ', milage,'점 입니다.' from userdb order by milage;

mysql> selet name,id,email,milage+300 from userdb where id = 'soony';
mysql> select count(*) from userdb where sex = 'F';
mysql> select avg(milage) from userdb where sex = 'F';
mysql> select level,max(milage),min(milage),avg(milage) from userdb group by level;
mysql> select level,max(milage),min(milage),avg(milage) from userdb group by level having level = 'B';
mysql> select name,id,email,milage,sex from userdb where sex ='F' and milage between 3000 and 4000;
mysql> select * from userdb where level in ('A','C');
mysql> select * from userdb where level no in ('A','C');
mysql> select name from userdb where name like '%현%';
mysql> select id from userdb where id like 'm%';

mysql> insert into userdb (name, id) values ('김영배','king94');
Query OK, 1 row affected (0.00 sec)

mysql> select * from userdb;
mysql>

mysql> select * from userdb where email is null;
mysql>

mysql> select name,id,email from userdb limit 3;
mysql>

mysql> select name,id,email,milage from userdb where milage > 3000 limit 2;
mysql> select name,id,milage from userdb order by milage desc limit 3;
mysql> select name,id,milage from userdb order by milage desc limit 1,3;
mysql>

mysql> update userdb set sex = 'M', milage = 3200 where name = '김영배';
mysql> select * from userdb where name = '김영배';

mysql> delete from userdb where name = '김영배';
Query OK, 1 row affected (0.00 sec)

mysql> select * from userdb;
mysql>

 

# 데이터의 백업과 복구
1. 데이터의 백업
[root@localhost /root]# mysqldump -u php -p sample > sample_backup.sql
Enter password:
[root@localhost /root]# ls -l sample_backup.sql

[root@localhost /root]# mysqldump -uphp -pphp2001 sample > sample_backup.sql

# 백업 데이터의 복구
[root@localhost /root]# mysql -uphp -pphp2001 sample < sample_backup.sql
[root@localhost /root]#