MySQL 사용법 (요약)

2009. 3. 4. 10:10

728x90

MySQL 사용법 (요약)

이강성

2000.1.26

시작하기/끝내기

시작하기

형식 : mysql -h hostname -u user_name -p DBname

hostname 의 default : localhost user_name의 default : login name

예1) mysql -u guest   # user가 guest이고 password가 없을 경우 (localhost) 
예1) mysql -h sr.kwangwoon.ac.kr -u guest   # user가 guest이고 password가 없을 경우 
예2) mysql -u guest -p  # password를 입력해야 함 
       Enter password:******** 
예3) mysql -u guest  pythonDB
 
참고) 컴퓨터 sr에서 mysql을 이용하여 연습을 하려면 여기서 사용하는 모든 데이터베이스 명 대신에 
test를 사용하라. 사용자는 누구라도 되고, 테이블을 만들고 검색하고 삭제할 수 있다. 
암호는 주어져 있지 않다.
실행예:  mysql -u guest test

끝내기

mysql> QUIT 
mysql> Ctrl-D 
 
*mysql> 은 프롬프트임

패스워드 설정/변경

변경:

mysql -u root -p
Enter password:*****
mysql> use mysql
mysql> update user set Password=PASSWORD('password') where user='userid';
mysql> flush privileges;

질의 입력하기

버전과 날짜 보기:

* 명령에 대소문자 구분은 없다 (case insensitive). mysql> 은 프롬프트임.

mysql> SELECT VERSION(), CURRENT_DATE;
+--------------+--------------+
| version()    | CURRENT_DATE |
+--------------+--------------+
| 3.22.20a-log | 1999-03-19   |
+--------------+--------------+
1 row in set (0.01 sec)
mysql>

계산기로 사용하기:

mysql> SELECT SIN(PI()/4), (4+1)*5;  
+-------------+---------+  
| SIN(PI()/4) | (4+1)*5 |  
+-------------+---------+  
|    0.707107 |      25 |  
+-------------+---------+

여러 명령 한줄에 입력하기:

mysql> SELECT VERSION(); SELECT NOW();  
+--------------+  
| version()    |  
+--------------+  
| 3.22.20a-log |  
+--------------+
+---------------------+  
| NOW()               |  
+---------------------+  
| 1999-03-19 00:15:33 |  
+---------------------+

여려줄에 한 명령 입력하기:

mysql> SELECT  
    -> USER()  
    -> ,  
    -> CURRENT_DATE;  
+--------------------+--------------+  
| USER()             | CURRENT_DATE |  
+--------------------+--------------+  
| joesmith@localhost | 1999-03-18   |  
+--------------------+--------------+

데이터베이스 만들고 선택하기

데이터베이스 보기 :

SHOW DATABASES; 
+----------+  
| Database |  
+----------+  
| mysql    |  
| python   |  
| test     |  
+----------+

사용할 데이터베이스 선택하기 :

USE test

관리자로 하여금 지정된 사용자만 DB를 접근하도록 하기 :

      GRANT ALL ON dbname.* TO your_mysql_name;

새로운 데이터베이스 만들기:

CREATE DATABASE menagerie;

관리자가 해주던지 아니면 본인이 해야한다(허가권이 있다면).

사용할 데이터베이스 선택 :

USE menagerie

mysql을 호출할 때 db선택하기 :

mysql -h host -u user -p menagerie

데이터베이스 없애기: (한번에 모든 것을 날려 버리므로 조심해서 사용할 것)

DROP DATABASE database_name

테이블 만들기 / 없애기

테이블 보기 :

SHOW TABLES; 
 Empty set (0.00 sec)

테이블 만들기 :

CREATE TABLE pet ( 
    -> name VARCHAR(20),  
    -> owner VARCHAR(20), 
    -> species VARCHAR(20),  
    -> sex CHAR(1),  
    -> birth DATE,  
    -> death DATE);

테이블 다시 보기:

mysql> SHOW TABLES; 
+---------------------+ 
| Tables in menagerie | 
+---------------------+ 
| pet                 | 
+---------------------+

테이블 확인하기:

mysql> DESCRIBE pet; 
+---------+-------------+------+-----+---------+-------+ 
| Field   | Type        | Null | Key | Default | Extra | 
+---------+-------------+------+-----+---------+-------+ 
| name    | varchar(20) | YES  |     | NULL    |       | 
| owner   | varchar(20) | YES  |     | NULL    |       | 
| species | varchar(20) | YES  |     | NULL    |       | 
| sex     | char(1)     | YES  |     | NULL    |       | 
| birth   | date        | YES  |     | NULL    |       | 
| death   | date        | YES  |     | NULL    |       | 
+---------+-------------+------+-----+---------+-------+

테이블 없애기:

mysql> DROP table table_name;

데이터를 테이블에 저장하기

테이블에 입력할 정보가 다음과 같다고 하자:

name	owner	species	sex	birth	death
Fluffy	Harold	cat	f	1993-02-04
Claws	Gwen	cat	m	1994-03-17
Buffy	Harold	dog	f	1989-05-13
Fang	Benny	dog	m	1990-08-27
Bowser	Diane	dog	m	1998-08-31	1995-07-29
Chirpy	Gwen	bird	f	1998-09-11
Whistler	Gwen	bird		1997-12-09
Slim	Benny	snake	m	1996-04-29

텍스트 파일에 정보가 있다면 쉽게 일괄적으로 테이블에 입력할 수 있다. 각 레코드는 한 라인에 입력되며, 각 필드는 탭으로 구분되어야 하고, NULL인 필드는 \N이 입력되어야 한다. 예:

Whistler

Gwen

bird

\N

1997-12-09

\N

이와 같은 정보가 'pet.txt' 파일에 저장되어 있다고 한다면, 다음의 명령으로 일괄적으로 읽어들일 수 있다.

mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

한번에 한 레코드씩 입력하려면 다음과 같은 코맨드라인 명령을 쓴다.

mysql> INSERT INTO pet  
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

이 경우 값이 없으면 NULL을 사용한다. (\N는 아님)

테이블에서 데이터 검색하기

SELECT 문을 이용한다:

SELECT what_to_select  
FROM which_table  
WHERE conditions_to_satisfy

전체 데이터 선택하기

mysql> SELECT * FROM pet; 
+----------+--------+---------+------+------------+------------+ 
| name     | owner  | species | sex  | birth      | death      | 
+----------+--------+---------+------+------------+------------+ 
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       | 
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       | 
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       | 
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       | 
| Bowser   | Diane  | dog     | m    | 1998-08-31 | 1995-07-29 | 
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       | 
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       | 
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       | 
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       | 
+----------+--------+---------+------+------------+------------+

정보 갱신하기: Bowser의 생일을 1998년에서 1989로 수정하려면,

mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Bowser";

행 선택하여 출력하기:

mysql> SELECT * FROM pet WHERE name = "Bowser"; 
+--------+-------+---------+------+------------+------------+ 
| name   | owner | species | sex  | birth      | death      | 
+--------+-------+---------+------+------------+------------+ 
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 | 
+--------+-------+---------+------+------------+------------+
 
mysql> SELECT * FROM pet WHERE birth >= "1998-1-1"; 
+----------+-------+---------+------+------------+-------+ 
| name     | owner | species | sex  | birth      | death | 
+----------+-------+---------+------+------------+-------+ 
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  | 
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  | 
+----------+-------+---------+------+------------+-------+

mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f"; 
+-------+--------+---------+------+------------+-------+ 
| name  | owner  | species | sex  | birth      | death | 
+-------+--------+---------+------+------------+-------+ 
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  | 
+-------+--------+---------+------+------------+-------+
 
mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird"; 
+----------+-------+---------+------+------------+-------+ 
| name     | owner | species | sex  | birth      | death | 
+----------+-------+---------+------+------------+-------+ 
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  | 
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  | 
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  | 
+----------+-------+---------+------+------------+-------+
 
mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m") 
           -> OR (species = "dog" AND sex = "f"); 
+-------+--------+---------+------+------------+-------+ 
| name  | owner  | species | sex  | birth      | death | 
+-------+--------+---------+------+------------+-------+ 
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  | 
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  | 
+-------+--------+---------+------+------------+-------+

열 선택하여 출력하기

mysql> SELECT name, birth FROM pet; 
+----------+------------+ 
| name     | birth      | 
+----------+------------+ 
| Fluffy   | 1993-02-04 | 
| Claws    | 1994-03-17 | 
| Buffy    | 1989-05-13 | 
| Fang     | 1990-08-27 | 
| Bowser   | 1989-08-31 | 
| Chirpy   | 1998-09-11 | 
| Whistler | 1997-12-09 | 
| Slim     | 1996-04-29 | 
| Puffball | 1999-03-30 | 
+----------+------------+
 
mysql> SELECT owner FROM pet; 
+--------+ 
| owner  | 
+--------+ 
| Harold | 
| Gwen   | 
| Harold | 
| Benny  | 
| Diane  | 
| Gwen   | 
| Gwen   | 
| Benny  | 
| Diane  | 
+--------+
 
mysql> SELECT DISTINCT owner FROM pet; 
+--------+ 
| owner  | 
+--------+ 
| Benny  | 
| Diane  | 
| Gwen   | 
| Harold | 
+--------+
 
mysql> SELECT name, species, birth FROM pet 
    -> WHERE species = "dog" OR species = "cat"; 
+--------+---------+------------+ 
| name   | species | birth      | 
+--------+---------+------------+ 
| Fluffy | cat     | 1993-02-04 | 
| Claws  | cat     | 1994-03-17 | 
| Buffy  | dog     | 1989-05-13 | 
| Fang   | dog     | 1990-08-27 | 
| Bowser | dog     | 1989-08-31 | 
+--------+---------+------------+

정렬하기

mysql> SELECT name, birth FROM pet ORDER BY birth; 
+----------+------------+ 
| name     | birth      | 
+----------+------------+ 
| Buffy    | 1989-05-13 | 
| Bowser   | 1989-08-31 | 
| Fang     | 1990-08-27 | 
| Fluffy   | 1993-02-04 | 
| Claws    | 1994-03-17 | 
| Slim     | 1996-04-29 | 
| Whistler | 1997-12-09 | 
| Chirpy   | 1998-09-11 | 
| Puffball | 1999-03-30 | 
+----------+------------+
 
mysql> SELECT name, birth FROM pet ORDER BY birth DESC; 
+----------+------------+ 
| name     | birth      | 
+----------+------------+ 
| Puffball | 1999-03-30 | 
| Chirpy   | 1998-09-11 | 
| Whistler | 1997-12-09 | 
| Slim     | 1996-04-29 | 
| Claws    | 1994-03-17 | 
| Fluffy   | 1993-02-04 | 
| Fang     | 1990-08-27 | 
| Bowser   | 1989-08-31 | 
| Buffy    | 1989-05-13 | 
+----------+------------+
 
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC; 
+----------+---------+------------+ 
| name     | species | birth      | 
+----------+---------+------------+ 
| Chirpy   | bird    | 1998-09-11 | 
| Whistler | bird    | 1997-12-09 | 
| Claws    | cat     | 1994-03-17 | 
| Fluffy   | cat     | 1993-02-04 | 
| Fang     | dog     | 1990-08-27 | 
| Bowser   | dog     | 1989-08-31 | 
| Buffy    | dog     | 1989-05-13 | 
| Puffball | hamster | 1999-03-30 | 
| Slim     | snake   | 1996-04-29 | 
+----------+---------+------------+

날짜계산하기

mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 FROM pet; 
+----------+-------------------------------------+ 
| name     | (TO_DAYS(NOW())-TO_DAYS(birth))/365 | 
+----------+-------------------------------------+ 
| Fluffy   |                                6.15 | 
| Claws    |                                5.04 | 
| Buffy    |                                9.88 | 
| Fang     |                                8.59 | 
| Bowser   |                                9.58 | 
| Chirpy   |                                0.55 | 
| Whistler |                                1.30 | 
| Slim     |                                2.92 | 
| Puffball |                                0.00 | 
+----------+-------------------------------------+
 
mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age 
    -> FROM pet ORDER BY name; 
+----------+------+ 
| name     | age  | 
+----------+------+ 
| Bowser   | 9.58 | 
| Buffy    | 9.88 | 
| Chirpy   | 0.55 | 
| Claws    | 5.04 | 
| Fang     | 8.59 | 
| Fluffy   | 6.15 | 
| Puffball | 0.00 | 
| Slim     | 2.92 | 
| Whistler | 1.30 | 
+----------+------+
 
mysql>  SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age 
    ->  FROM pet ORDER BY age; 
+----------+------+ 
| name     | age  | 
+----------+------+ 
| Puffball | 0.00 | 
| Chirpy   | 0.55 | 
| Whistler | 1.30 | 
| Slim     | 2.92 | 
| Claws    | 5.04 | 
| Fluffy   | 6.15 | 
| Fang     | 8.59 | 
| Bowser   | 9.58 | 
| Buffy    | 9.88 | 
+----------+------+
 
mysql>  SELECT name, birth, death, (TO_DAYS(death)-TO_DAYS(birth))/365 AS age 
    ->  FROM pet WHERE death IS NOT NULL ORDER BY age; 
+--------+------------+------------+------+ 
| name   | birth      | death      | age  | 
+--------+------------+------------+------+ 
| Bowser | 1989-08-31 | 1995-07-29 | 5.91 | 
+--------+------------+------------+------+

DATE에서 해, 달, 일의 정보를 꺼내려면, YEAR(), MONTH(), DAYOFMONTH()를 이용한다.

mysql> SELECT name, birth, MONTH(birth) FROM pet; 
+----------+------------+--------------+ 
| name     | birth      | MONTH(birth) | 
+----------+------------+--------------+ 
| Fluffy   | 1993-02-04 |            2 | 
| Claws    | 1994-03-17 |            3 | 
| Buffy    | 1989-05-13 |            5 | 
| Fang     | 1990-08-27 |            8 | 
| Bowser   | 1989-08-31 |            8 | 
| Chirpy   | 1998-09-11 |            9 | 
| Whistler | 1997-12-09 |           12 | 
| Slim     | 1996-04-29 |            4 | 
| Puffball | 1999-03-30 |            3 | 
+----------+------------+--------------+

5월에 태어난 동물을 출력하려면:

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5; 
+-------+------------+ 
| name  | birth      | 
+-------+------------+ 
| Buffy | 1989-05-13 | 
+-------+------------+

패턴 매칭

'%'는 (0을 포함한) 임의의 수의 문자와 매칭한다. 'b'로 시작하는 이름의 동물을 찾으려면,

mysql> SELECT * FROM pet WHERE name LIKE "b%"; 
+--------+--------+---------+------+------------+------------+ 
| name   | owner  | species | sex  | birth      | death      | 
+--------+--------+---------+------+------------+------------+ 
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       | 
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 | 
+--------+--------+---------+------+------------+------------+

'fy'로 끝나는 이름의 동물은,

mysql> SELECT * FROM pet WHERE name LIKE "%fy"; 
+--------+--------+---------+------+------------+-------+ 
| name   | owner  | species | sex  | birth      | death | 
+--------+--------+---------+------+------------+-------+ 
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  | 
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  | 
+--------+--------+---------+------+------------+-------+

'w'가 있는 이름의 동물은,

mysql> SELECT * FROM pet WHERE name LIKE "%w%"; 
+----------+-------+---------+------+------------+------------+ 
| name     | owner | species | sex  | birth      | death      | 
+----------+-------+---------+------+------------+------------+ 
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       | 
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 | 
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       | 
+----------+-------+---------+------+------------+------------+

꼭 다섯자로 구성된 이름의 동물은,

mysql> SELECT * FROM pet WHERE name LIKE "_____"; 
+-------+--------+---------+------+------------+-------+ 
| name  | owner  | species | sex  | birth      | death | 
+-------+--------+---------+------+------------+-------+ 
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  | 
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  | 
+-------+--------+---------+------+------------+-------+

확장된 정규식

'.'는 임의의 한자와 매치
'[abc] 는 'a' 또는 'b' 또는 'c'
'*'는 0이상의 임의의 수의 앞선 문자와 매치 'x*'는 임의의 수의 'x'와 매치. [0-9]*는 임의의 수의 숫자와, '.*' 는 임의의 문자와 매치
대소문자를 구별함 (case sensitive)
테스트되는 임의의 어떤 부분에서 매치되면 됨 (SQL패턴 매치는 전체가 다 매치되어야함)
시작과 끝을 매치하기 위하여 '^' '$'를 사용한다.

'b'혹은 'B'로 시작하는 이름:

mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]"; 
+--------+--------+---------+------+------------+------------+ 
| name   | owner  | species | sex  | birth      | death      | 
+--------+--------+---------+------+------------+------------+ 
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       | 
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 | 
+--------+--------+---------+------+------------+------------+

마지막에 `fy' 로 끝나는 이름:

mysql> SELECT * FROM pet WHERE name REGEXP "fy$"; 
+--------+--------+---------+------+------------+-------+ 
| name   | owner  | species | sex  | birth      | death | 
+--------+--------+---------+------+------------+-------+ 
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  | 
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  | 
+--------+--------+---------+------+------------+-------+

`w'를 포함하는 이름:

mysql> SELECT * FROM pet WHERE name REGEXP "[wW]"; 
+----------+-------+---------+------+------------+------------+ 
| name     | owner | species | sex  | birth      | death      | 
+----------+-------+---------+------+------------+------------+ 
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       | 
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 | 
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       | 
+----------+-------+---------+------+------------+------------+

다섯 문자로 이루어진 이름:

mysql> SELECT * FROM pet WHERE name REGEXP "^.....$"; 
+-------+--------+---------+------+------------+-------+ 
| name  | owner  | species | sex  | birth      | death | 
+-------+--------+---------+------+------------+-------+ 
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  | 
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  | 
+-------+--------+---------+------+------------+-------+

`{n}' 연산자 (repeat-n-times)를 사용하여 아래와 같이 쓸 수도 있음:

mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}$"; 
+-------+--------+---------+------+------------+-------+ 
| name  | owner  | species | sex  | birth      | death | 
+-------+--------+---------+------+------------+-------+ 
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  | 
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  | 
+-------+--------+---------+------+------------+-------+

행의 수 세기

전체 엔트리 수 세기:

mysql> SELECT COUNT(*) FROM pet; 
+----------+ 
| COUNT(*) | 
+----------+ 
|        9 | 
+----------+

주인이 가지고 있는 동물의 수 세기:

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; 
+--------+----------+ 
| owner  | COUNT(*) | 
+--------+----------+ 
| Benny  |        2 | 
| Diane  |        2 | 
| Gwen   |        3 | 
| Harold |        2 | 
+--------+----------+

종별 수:

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species; 
+---------+----------+ 
| species | COUNT(*) | 
+---------+----------+ 
| bird    |        2 | 
| cat     |        2 | 
| dog     |        3 | 
| hamster |        1 | 
| snake   |        1 | 
+---------+----------+

성별 수:

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex; 
+------+----------+ 
| sex  | COUNT(*) | 
+------+----------+ 
| NULL |        1 | 
| f    |        4 | 
| m    |        4 | 
+------+----------+

종 및 성별 수:

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; 
+---------+------+----------+ 
| species | sex  | COUNT(*) | 
+---------+------+----------+ 
| bird    | NULL |        1 | 
| bird    | f    |        1 | 
| cat     | f    |        1 | 
| cat     | m    |        1 | 
| dog     | f    |        1 | 
| dog     | m    |        2 | 
| hamster | f    |        1 | 
| snake   | m    |        1 | 
+---------+------+----------+

개와 고양이에 대해서만 적용:

mysql> SELECT species, sex, COUNT(*) FROM pet 
    -> WHERE species = "dog" OR species = "cat" 
    -> GROUP BY species, sex; 
+---------+------+----------+ 
| species | sex  | COUNT(*) | 
+---------+------+----------+ 
| cat     | f    |        1 | 
| cat     | m    |        1 | 
| dog     | f    |        1 | 
| dog     | m    |        2 | 
+---------+------+----------+

알려진 성별에 대해서만 성별 동물의 수:

mysql> SELECT species, sex, COUNT(*) FROM pet 
    -> WHERE sex IS NOT NULL 
    -> GROUP BY species, sex; 
+---------+------+----------+ 
| species | sex  | COUNT(*) | 
+---------+------+----------+ 
| bird    | f    |        1 | 
| cat     | f    |        1 | 
| cat     | m    |        1 | 
| dog     | f    |        1 | 
| dog     | m    |        2 | 
| hamster | f    |        1 | 
| snake   | m    |        1 | 
+---------+------+----------+

하나이상의 테이블 사용하기

테이블 하나 더 만들기:

mysql> CREATE TABLE event  ( 
    -> name VARCHAR(20),  
    -> date DATE, 
    -> type VARCHAR(15),  
    -> remark VARCHAR(255));

파일 'event.txt'

Fluffy	1995-05-15	litter	4 kittens, 3 female, 1 male
Buffy	1993-06-23	litter	5 puppies, 2 female, 3 male
Buffy	1994-06-19	litter	3 puppies, 3 female
Chirpy	1999-03-21	vet	needed beak straightened
Slim	1997-08-03	vet	broken rib
Bowser	1991-10-12	kennel
Fang	1991-10-12	kennel
Fang	1998-08-28	birthday	Gave him a new chew toy
Claws	1998-03-17	birthday	Gave him a new flea collar
Whistler	1998-12-09	birthday	First birthday

데이터 읽어들이기:

mysql> LOAD DATA LOCAL INFILE "event.txt" INTO TABLE event;

출산 때의 동물의 나이를 계산 (JOIN):

mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark 
    -> FROM pet, event 
    -> WHERE pet.name = event.name AND type = "litter"; 
+--------+------+-----------------------------+ 
| name   | age  | remark                      | 
+--------+------+-----------------------------+ 
| Fluffy | 2.27 | 4 kittens, 3 female, 1 male | 
| Buffy  | 4.12 | 5 puppies, 2 female, 3 male | 
| Buffy  | 5.10 | 3 puppies, 3 female         | 
+--------+------+-----------------------------+

짝찾아보기 (JOINing itself):

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species 
    -> FROM pet AS p1, pet AS p2 
    -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m"; 
+--------+------+--------+------+---------+ 
| name   | sex  | name   | sex  | species | 
+--------+------+--------+------+---------+ 
| Fluffy | f    | Claws  | m    | cat     | 
| Buffy  | f    | Fang   | m    | dog     | 
| Buffy  | f    | Bowser | m    | dog     | 
+--------+------+--------+------+---------+

기타의 연산

CREATE TABLE shop ( 
 article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, 
 dealer  CHAR(20)                 DEFAULT ''     NOT NULL, 
 price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL, 
 PRIMARY KEY(article, dealer));

INSERT INTO shop VALUES 
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69), 
(3,'D',1.25),(4,'D',19.95);

전체 데이터 예는:

SELECT * FROM shop 
+---------+--------+-------+ 
| article | dealer | price | 
+---------+--------+-------+ 
|    0001 | A      |  3.45 | 
|    0001 | B      |  3.99 | 
|    0002 | A      | 10.99 | 
|    0003 | B      |  1.45 | 
|    0003 | C      |  1.69 | 
|    0003 | D      |  1.25 | 
|    0004 | D      | 19.95 | 
+---------+--------+-------+

최대 아이템 수는?

SELECT MAX(article) AS article FROM shop 
+---------+ 
| article | 
+---------+ 
|       4 | 
+---------+

가장 비싼 항목의 번호, 판매자, 가격?

SELECT article, dealer, price 
FROM   shop 
WHERE  price=(SELECT MAX(price) FROM shop)

각 항목의 최고 가격은?

SELECT article, MAX(price) AS price 
FROM   shop 
GROUP BY article
+---------+-------+ 
| article | price | 
+---------+-------+ 
|    0001 |  3.99 | 
|    0002 | 10.99 | 
|    0003 |  1.69 | 
|    0004 | 19.95 | 
+---------+-------+

각 항목에 대하여 가장 비싼 가격의 딜러를 찾아라!

SELECT article, dealer, price 
FROM   shop s1 
WHERE  price=(SELECT MAX(s2.price) 
              FROM shop s2 
              WHERE s1.article = s2.article)

항목 삭제하기 (DELETE)

문법 : DELETE FROM tbl_name [WHERE where_condition] [LIMIT rows]

외부키(Foreign Key)사용하기

 
CREATE TABLE persons ( 
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,  
    name CHAR(60) NOT NULL, 
    PRIMARY KEY (id) 
);
 
CREATE TABLE shirts ( 
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL, 
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL, 
    owner SMALLINT UNSIGNED NOT NULL REFERENCES persons, 
    PRIMARY KEY (id) 
);
 
INSERT INTO persons VALUES (NULL, 'Antonio Paz');
 
INSERT INTO shirts VALUES 
(NULL, 'polo', 'blue', LAST_INSERT_ID()), 
(NULL, 'dress', 'white', LAST_INSERT_ID()), 
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());
 
INSERT INTO persons VALUES (NULL, 'Lilliana Angelovska');
 
INSERT INTO shirts VALUES 
(NULL, 'dress', 'orange', LAST_INSERT_ID()), 
(NULL, 'polo', 'red', LAST_INSERT_ID()), 
(NULL, 'dress', 'blue', LAST_INSERT_ID()), 
(NULL, 't-shirt', 'white', LAST_INSERT_ID());
 
SELECT * FROM persons; 
+----+---------------------+ 
| id | name                | 
+----+---------------------+ 
|  1 | Antonio Paz         | 
|  2 | Lilliana Angelovska | 
+----+---------------------+
 
SELECT * FROM shirts; 
+----+---------+--------+-------+ 
| id | style   | color  | owner | 
+----+---------+--------+-------+ 
|  1 | polo    | blue   |     1 | 
|  2 | dress   | white  |     1 | 
|  3 | t-shirt | blue   |     1 | 
|  4 | dress   | orange |     2 | 
|  5 | polo    | red    |     2 | 
|  6 | dress   | blue   |     2 | 
|  7 | t-shirt | white  |     2 | 
+----+---------+--------+-------+
 
SELECT s.* FROM persons p, shirts s 
 WHERE p.name LIKE 'Lilliana%' 
   AND s.owner = p.id 
   AND s.color &lt;&gt; 'white';
+----+-------+--------+-------+ 
| id | style | color  | owner | 
+----+-------+--------+-------+ 
|  4 | dress | orange |     2 | 
|  5 | polo  | red    |     2 | 
|  6 | dress | blue   |     2 | 
+----+-------+--------+-------+

테이블 구조 바꾸기

테이블 이름 바꾸기

t1에서 t2로 이름 바꾸기
 
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
mysql> ALTER TABLE t1 RENAME t2;

컬럼의 형 바꾸기

테이블 t2의 컬럼 a를 INTEGER에서 TINYINT NOT NULL 형으로 전환하기
 
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL;

컬럼의 길이 바꾸기

테이블 t2의 컬럼 b CHAR(10)을 c CHAR(20)으로 길이와 이름 바꾸기
 
mysql> ALTER TABLE t2 CHANGE b c CHAR(20);

새 컬럼 추가하기

timestamp란 컬럼을 t2에 추가하기
 
mysql> ALTER TABLE t2 ADD d TIMESTAMP;

인덱스 컬럼 만들기 / primary key 설정하기

t2에 컬럼 d의 인덱스를 추가하기, 컬럼 a를 주 키로 설정한다.
 
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

컬럼 없애기

테이블 t2에서 컬럼 c를 없앤다.
 
mysql> ALTER TABLE t2 DROP COLUMN c;

새 컬럼 추가하기

테이블 t2에 INT UNSIGNED NOT NULL AUTO_INCREMENT 형 컬럼 c를 새로 만들기
 
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT;

테이블 없애기

mysql> DROP TABLE table_name

언어 참조

스트링

'a string' "another string"

Escape sequences

\0   An ASCII 0 (NUL) character.   
\n   A newline character.   
\t   A tab character.   
\r   A carriage return character.   
\b   A backspace character.   
\'   A single quote (`'') character.   
\"   A double quote (`"') character.   
\\   A backslash (`\') character.   
\%   A `%' character. This is used to search for literal instances of `%' in contexts  
       where `%' would otherwise be interpreted as a wildcard character.   
\_   A `_' character. This is used to search for literal instances of `_' in contexts  
       where `_' would otherwise be interpreted as a wildcard character. 
mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello'; 
+-------+---------+-----------+--------+--------+ 
| hello | "hello" | ""hello"" | hel'lo | 'hello | 
+-------+---------+-----------+--------+--------+
 
mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello"; 
+-------+---------+-----------+--------+--------+ 
| hello | 'hello' | ''hello'' | hel"lo | "hello | 
+-------+---------+-----------+--------+--------+
 
mysql> SELECT "This\nIs\nFour\nlines"; 
+--------------------+ 
| This 
Is 
Four 
lines | 
+--------------------+

숫자

정수형 (1221, 0, -32), 부동소숫점형 (3.4, -23.45e+10, 148.00), 16진수(0x23fc)

Column Types

Numeric types

Column type	Storage required
`TINYINT`	1 byte
`SMALLINT`	2 bytes
`MEDIUMINT`	3 bytes
`INT`	4 bytes
`INTEGER`	4 bytes
`BIGINT`	8 bytes
`FLOAT(X)`	4 if X <= 24 or 8 if 25 <= X <= 53
`FLOAT`	4 bytes
`DOUBLE`	8 bytes
`DOUBLE PRECISION`	8 bytes
`REAL`	8 bytes
`DECIMAL(M,D)`	`M` bytes (`D`+2, if `M < D`)
`NUMERIC(M,D)`	`M` bytes (`D`+2, if `M < D`)

Date and time types

Column type	Storage required
`DATE`	3 bytes
`DATETIME`	8 bytes
`TIMESTAMP`	4 bytes
`TIME`	3 bytes
`YEAR`	1 byte

date, time이 값의 범위를 벋어날 경우는 zero value를 갖는다.

Column type	``Zero'' value
`DATETIME`	`'0000-00-00 00:00:00'`
`DATE`	`'0000-00-00'`
`TIMESTAMP`	`00000000000000` (length depends on display size)
`TIME`	`'00:00:00'`
`YEAR`	`0000`

Timestamp format

Column type	Display format
`TIMESTAMP(14)`	`YYYYMMDDHHMMSS`
`TIMESTAMP(12)`	`YYMMDDHHMMSS`
`TIMESTAMP(10)`	`YYMMDDHHMM`
`TIMESTAMP(8)`	`YYYYMMDD`
`TIMESTAMP(6)`	`YYMMDD`
`TIMESTAMP(4)`	`YYMM`
`TIMESTAMP(2)`	`YY`

String types

Column type	Storage required
`CHAR(M)`	`M` bytes, `1 <= M <= 255`
`VARCHAR(M)`	`L`+1 bytes, where `L <= M` and `1 <= M <= 255`
`TINYBLOB`, `TINYTEXT`	`L`+1 bytes, where `L` < 2^8
`BLOB`, `TEXT`	`L`+2 bytes, where `L` < 2^16
`MEDIUMBLOB`, `MEDIUMTEXT`	`L`+3 bytes, where `L` < 2^24
`LONGBLOB`, `LONGTEXT`	`L`+4 bytes, where `L` < 2^32
`ENUM('value1','value2',...)`	1 or 2 bytes, depending on the number of enumeration values (65535 values maximum)
`SET('value1','value2',...)`	1, 2, 3, 4 or 8 bytes, depending on the number of set members (64 members maximum)

기억 용량 비교

Value	`CHAR(4)`	Storage required	`VARCHAR(4)`	Storage required
`''`	`' '`	4 bytes	`''`	1 byte
`'ab'`	`'ab '`	4 bytes	`'ab'`	3 bytes
`'abcd'`	`'abcd'`	4 bytes	`'abcd'`	5 bytes
`'abcdefgh'`	`'abcd'`	4 bytes	`'abcd'`	5 bytes

* BLOB 는 case-sensitive, TEXT는 case-insensitive

인덱스

적절한 인덱스의 사용은 SELECT문의 성능을 크게 향상시켜준다. 최대 16개까지의 인덱스를 갖는다. 최대 인덱스 길이는 256바이트이다. CHAR과 VARCHAR에 대해서 앞 문자열을 대상으로 인덱스 할 수 있다. BLOB와 TEXT에 대해서 앞문자열을 사용하는 것은 인덱스 할 때 필수이다.

mysql> CREATE TABLE test ( 
           name CHAR(200) NOT NULL, 
           KEY index_name (name(10)));

복수개 컬럼에 대해서 인덱스를 만들 수 있다. 최대 15개 컬럼을 사용할 수 있다.

 
mysql> CREATE TABLE test ( 
           id INT NOT NULL, 
           last_name CHAR(30) NOT NULL, 
           first_name CHAR(30) NOT NULL, 
           PRIMARY KEY (id), 
           INDEX name (last_name,first_name));

name인덱스의 사용예:

 
mysql> SELECT * FROM test WHERE last_name="Widenius";
 
mysql> SELECT * FROM test WHERE last_name="Widenius" 
                          AND first_name="Michael";
 
mysql> SELECT * FROM test WHERE last_name="Widenius" 
                          AND (first_name="Michael" OR first_name="Monty");
 
mysql> SELECT * FROM test WHERE last_name="Widenius" 
                          AND first_name >="M" AND first_name < "N";

연산자

다음의 연산자는 select문에서 사용가능하다.

산술 연산자

+, -, *, /, MOD(a,b) : a를 b로 나눈 나머지

비트 연산자

|, &, <<, >>, ~, BIT_COUNT(N) : N의 설정된 비트 수

논리 연산자

!, ||, &&,

비교 연산자

=, >, <, >=, <=, <> (!=),   
<=> (Null safe equal),   
IS NULL, IS NOT NULL,   
between a and b  
expr IN (value, ...), expr NOT IN (value, ...)  
ISNULL(expr), COALESCE(list), INTERVAL(N,N1,N2,N3,...)
 
 
mysql> select 1 <=> 1, NULL <=> NULL, 1 <=> NULL; 
                  -> 1 1 0
 
mysql> select 1 BETWEEN 2 AND 3; 
           -> 0
 
mysql> select 'b' BETWEEN 'a' AND 'c'; 
           -> 1
 
mysql> select 2 BETWEEN 2 AND '3'; 
           -> 1
 
mysql> select 2 BETWEEN 2 AND 'x-3'; 
           -> 0
 
mysql> select 2 IN (0,3,5,'wefwf'); 
           -> 0
 
mysql> select 'wefwf' IN (0,3,5,'wefwf'); 
           -> 1
.......

728x90

저작자표시 비영리 변경금지

Typiler

MySQL 사용법 (요약)

+ Recent posts

티스토리툴바