MySQL Ʃ丮

: ̻(sangyong@nownuri.net)
: 1999 9 9
Ÿ: 1999 10 13(capricon@g2j.co.kr )

------------------------------------------------------------------------------
  MySQL 3.22.25  Ŵ 8 κ  Դϴ.  
̳  ̻ϴٸ  ڿ ּҷ  ָ ڽϴ.
 ͵ ְ ǿ ͵ ְ   ٲ ͵ ְ, 
߰ ͵ ֽϴ.   ٸ κе  Դϴ.
 ϱ ٶϴ.
------------------------------------------------------------------------------

mysql̶ Ŭ̾Ʈ α׷ ̿Ͽ MySQL   . mysql
 ͺ̽    ְ ִ α׷ '͹̳ '
Ȥ  '' Ѵ.

mysql ȭ α׷μ  ϰ,  ϰ,  ȭ鿡
ִ  Ѵ. mysql ġ (batch mode)   ִ.

̸ Ͽ sql ɹ ־ΰ mysql   ϶ ϸ 
ȴ(ڿ ˾  'mysql -vvv < batch_test.txt'  ϸ ȴ).

mysql ɼǵ  --help ɼ ٿ ϸ ȴ:

shell> mysql --help

 Ʃ丮󿡼 mysql ġǾ    ִ MySQL  ִٴ
 Ѵ. ׷  MySQL ڿ ϶( ڶ
MySQL  ٸ κ 캼 ʿ䰡  ̴).

 Ʃ丮󿡼 ͺ̽ ϰ ϴ   ٷ. ̹ 
ϴ ͺ̽ ϴ Ϳ  ִٸ ͺ̽  ȿ 
 ̺     ǳʶپ .

Ʃ丮  ̶ ڼ   ʴ´. ⿡ ޵ Ϳ  
 ڼ ˰ Ͱŵ MySQL  Ŵ  ȴ.

shell>  Ʈ, mysql> MySQL Ʈ Ÿ.


1.  ϱ/

 Ϸ mysql    MySQL  ̸ 밳  
н带  ־  ̴.   α ǻͰ ƴ Ϳ 
ȴٸ ȣƮ ̸  ʿ䰡  ̴(ȣƮ ̸,  ̸, 
н).   ˾Ҵٸ ó   ִ:

shell> mysql -h host -u user -p
Enter password: ********

******* κ н. 'Enter password' Ʈ ̸ н带 
ָ ȴ. ϸ  Ұ ޽ 'mysql>' Ʈ   
 ̴.

shell> mysql -h host -u user -p
 Enter password: ********
 Welcome to the MySQL monitor. Commands end with ; or \g.
 Your MySQL connection id is 459 to server version: 3.22.20a-log

 Type 'help' for help.

 mysql>


'mysql>' Ʈ ǹϴ ٴ غǾ ɾ Է϶ ̴.

 ġϴ   MySQL  ȣƮ(local host) Ǵ 
 " (anonymous user)"   ְ Ѵ. ̷ 쿡 
ܼ 

shell> mysql

ó ؼ   ִ.

 Ͽٸ 'mysql>' Ʈ   'QUIT'̶ ļ 
   ִ:

mysql> QUIT
Bye

CtrlŰ D Ű ÿ     ִ.

̾   κ    ¶  Ѵ. 
'mysql>' Ʈ   ¶  Ÿ.


2.  ϱ(Entering Queries)

  Ͽ  ӵ  Ȯ. ̷ Ѵٰ ۾ 
 ͺ̽ ϴ(: USE ɾ ̿Ͽ Ÿ ̽
ڴٰ ؾ   ֽϴ)  ƴ ¶  ؾ Ѵ. 
 Ȳ ͺ̽ȿ ̺ , ̺ ڷḦ ø, 
̺ ڷḦ  ͺٴ ϴ  ణ̶    
߿ϴ. ̹  ɾ Է ⺻ Ģ     ˾ 
.    mysql ϴ ͼ ̴.

Ʒ MySQL   ¥ ϴ ɾ δ. '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   ͵   ִ:

  SQL   ڿ ݷ(;) ̷(ݷ ʿ
ܰ ֱϴ. QUIT   ϳ. ߿ ̰Ϳ  ٽ ϰڴ).

   , mysql     ǰ ϰ,  
 ְ ٽ    Ʈ('mysqld>') .

 mysql ̺ (  ̷)  ش. ù   
    ִ. ι°  ʹ   δ. ,  
 ͺ̽ ̺   ̸̴.   ó 
̺  ̸ ƴ ǥ(expression)   󺧸  ǥ 
ȴ.

 mysql     ð(뷫    )  
ش.   ð Ȯ  ƴϴ. ֳϸ  ð wall clock 
time(CPU ð ƴϴ)̶ Ͱ    Ʈũ Ͽ  
ð  ޱ ̴(     κ 
Ÿ ʰڴ).

Ű( :'' մϴ. ̸  ̶    
 ̸Դϴ) 빮ڷ ϵ, ҹڷ ϵ . Ʒ   
 ϴ:

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> SELECT version(), current_date;
mysql> seLect vErSiOn(), current_DATE;

ٸ  ϳ  캸. 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 ݷ     
аѴ(mysql    Է ޾ δ. Է   
ݷ   Ѵ).

 ٿ     :

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

 Է  ù Էϰ Ű  Ʈ 'mysql>' 
'->' ٲ  ָ϶. ̰    ϼ ʾ,  
 Է ٸٶ mysql п ˸ ̴. Ʈ  
ģ ȳڴ.   п ˷ ش. Ʈ ˷ִ 
͵  mysql  ٸ ִ ׻    ̴.

ɾ Է  Ϸ  \c ָ ȴ:

mysql> SELECT
     -> USER()
     -> \c
mysql>

Ʈ ȭ  . \c ģ 'mysql>' ٲ.  ɾ ޾Ƶ 
غ Ǿٴ  ˸ ̴.

 ǥ ġ  Ʈ  ǹ̸  ̴.


Ʈ                                 ǹ
-----------------------------------------------
mysql>         ޾  غ Ǿ
-----------------------------------------------
 ->      ɾ  ٿ      
         ٸ  ǹ
-----------------------------------------------
'>    Է Ÿ.  ' ϴ 
     ڿ ϴ ̶  Ÿ
     (ڿ Է  ڿ  Է
        ' ٿ ٰ)
-----------------------------------------------
">   '> .  ̴ ڿ ' ƴ
       "  θٴ ̴.
-----------------------------------------------

 
ݷ ̴  ؾ 쿬 Ȥ Ǽ  ٿ ġ  
Է   ִ.    mysql Է  ٸ:

mysql> SELECT USER()
     ->

̷  mysql ݷ ٸ ִ ̴(    
  Էߴٰ  mysql ׷ ʴ. ݷ  
̴). Ʈ ٲ ġ ä Ѵٸ  ٸ  
 ð   ִ. ݷ  ־  ϼϸ 
  ̴:

mysql> SELECT USER()
     -> ;
+--------------------+
| USER()             |
+--------------------+
| joesmith@localhost |
+--------------------+

'> "> ڿ  ߿  Ÿ Ʈ̴. MySQL ڵ  
'  "  ѷθ ڿ ȴ(  'hello', "goodbye"̴).  
 ٿ  ڿ Է  ִ.'>  "> Ʈ Ÿ ̰ 
' " ϴ ڿ ϴ ɾ  ־ ݴ '  "   
  ʾҴٴ  ǹϴ ̴.  ٿ  ڿ Է  
.  ڿ  ٿ Էϰ ϴ 찡 󸶳 ɱ? 
״  ʴ. κ , '>  "> Ʈ ݴ '  "  
Ծٰ ˷ִ ǹ ̴.    .

mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
     ">

  SELECT  Էϰ Ű ġ  ٸ ص ƹ 
   ̴. " ƹ   ϱ?" ̻ϰ  
"> Ʈ Ÿ ǹ̸  . ڿ ݴ ο ȣ Ծٴ
 ˸ ִ.    ߸ ִ. "Smith  "  ̴.

,  ؾ ұ?     ϴ ̴. ׷ 
\c ĥ . ֳϸ \c "  Էϱ  ڿ Ϻη 
 ̱ ̴.  "\c Էϸ ȴ:

mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
     "> "\c
mysql>

Ʈ mysql> ǵ.  ̰ " ɾ  غ Ϸ" 
̴. 

'> "> ǹϴ ٸ ϴ  ߿ϴ. ߸Ͽ ݴ ο ȣ 
Ծ   Էϴ ͵  õǴ ϰ ̱ ̴(⿡
QUIT Եȴ).   ϱ  ݴ οȣ  Ѵٴ 
𸣸 ̰ ſ ȥ  ̴.


3.  ̽  ϱ

ɾ Է  ˾ ͺ̽     Ǿ.

 ֿϵ Űٰ  . ֿϵ  ؼ  
ΰ ϰ  ̴. ͺ̽   ȿ ̺  
⿡ ϴ ͸ ־θ ȴ. ׷ ϸ ̺ ڷḦ ͼ 
ֿϵ     ˾   ִ.   ̷ ͵
Ͽ   ׵ ٷ :

 ͺ̽ 
 ̺ 
 ̺ ڷ ֱ
 ̺ ڷ  
  ̺ ϱ 

ͺ̽ ̸ menagerie(''̶ ̴) . menagerie 
ͺ̽ ſ ϳ  Ȱ  ͺ̽ ϴ
찡 ִ.     ϴ ͺ̽  ϴ 
γ ֿϵ ġ  ܵξ ϴ ǻ翡    ִ.

SHOW  Ͽ    ͺ̽    ִ:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

   ٸ  ִ.  mysql, test ͺ̽ ׻ 
̴. mysql ͺ̽      ִ ߿ 
ͺ̴̽. test  ״ ϱ  ִ ͺ̽. test
ͺ̽ ִٸ ó ؼ   ִ:

mysql> USE test
Database changed

QUITó USE  ݷ ʿ ʴٴ  (ݷ 
. ׳ ϰ   ݷ ٰ  δ ͵ ).
USE    ٿ ؾ Ѵٴ ͵ ݵ .

test ͺ̽    ̰   ִ.   
ͺ̽( 쿣 test)   ִ   ̶  
   ڷ ٸ    ϴ. ̰   
  ִٴ ̴. ׷ MySQL ڿ и ͺ̽
  ޶ ûؾ Ѵ. ⼭ menagerie . 
ڴ   ɹ  ʿ䰡 ִ:

mysql> GRANT ALL ON menagerie.* TO your_mysql_name;

your_mysql_name    MySQL  ġؾ Ѵ.


3.1 ͺ̽  ϱ

ڰ     ͺ̽  ־ٸ װ ׳ 
ϸ ȴ. ׷  ó Ͽ ռ   ָ ȴ:

mysql> CREATE DATABASE menagerie;

н ͺ̽ ̸ ҹڸ Ѵ(SQL Ű ׷ ʴ).
 ͺ̽ ̸  ׻ 'menagerie'  ؾ  Menagerie, MENAGERIE, 
meNaGerIE  ȵȴ. ̺ ̸  ҹڸ Ѵ.

ͺ̽ ٰ ؼ ϰڴٰ ˸  ƴϴ. 
 ϰڴٰ ־ Ѵ:

mysql> USE menagerie
Database changed

ͺ̽ ѹ     use  ̿Ͽ  
ͺ̽ ؾ Ѵ. 翬  ƴұ? ٸ δ mysql 
  ͺ̽ ̸  ־ ȴ:

shell> mysql -h host -u user -p menagerie
Enter password: ********

⼭ menagerie н ƴϴ. ȥ . н带   
ٷ -p ڿ ٿ  ־ Ѵ(:    Ȼ ٶ 
  ƴմϴ. н尡 ڱ״ ȭ鿡 ̱ Դϴ.   
ʸӷ ִٸ   ΰ? н尡 ڱ״ ȭ鿡 ٴ 
   Դϴ.    ڶ -p ڿ н带 
 ִ ""   Դϴ. MySQL ڵ  ̷ ߴ..). 
н尡 ƴ϶  ͺ̽ ̸̴.


3.2 ̺ 

    ó ͺ̽   .   Ŀ 
 ó

mysql> SHOW TABLES;
Empty set (0.00 sec)

ͺ̽  ִ. 翬ϴ.   µ ִ    
. 

SHOW TABLES;  õ ͺ̽ ִ ̺ ̴ ̴.

̺    . Ÿ̽  , CREATE  
ȴ. õڿ ˾ .

 ߿ϰ ư   ͺ̽   ΰ̴.
 ̺ ʿϰ  ȿ  ڷ ־  ؾ Ѵ.

⼭   ֿ    ڵ带 ξ  ̴. pet 
̶̺ ̸ .  ̺ ֿ  ̸, (ı ̸ 
̴), , ( )   Էϰ  ̴.

̴? ̵ ʿ   ð  ϴ  ̹Ƿ ̿ 
   ־  ̴.    ؾ Ѵ. ̷ 
Ȳ ̸ ο ΰ ؾ Ѵٴ  ͺ̽  ޴ 
ƴұ Ѵ. ̴ ð  ϹǷ   ΰ  ¥
̷κ ϸ  ̴. MySQL   ƾ ϹǷ ̰ 
  ƴϴ. ̴   δ   ΰ  ִ:

 ٰ ֿ   ̸ ˷ִ Ͽ   ִ( 
̶...   , ̰ ٸ     ִ. 
   ?   ī带  ϴ  ʿ䰡 
 ?).

  ¥  ٸ ¥ ε ̸   ִ.   
   ֿ      ̴.

ֿ   μ ٸ ͵鵵   ְ   . 
ϴ.

CREATE TABLE  ̺   ִ:

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

CREATE TABLE  ̺ ̸  ְ ȣ ȿ  ̸   ڷ
 ¦ Ͽ ǥ Ͽ  ָ ȴ. name, owner,  species, sex,
birth  ̸̸, VARCHAR(20), CHAR(2), DATE ڷ̴. ڷ̶ 
 ״ ڷ ̴. ڷ ڿ  ְ, ¥  ְ,  
 ִ.

 ǥ  ̺ :

pet TABLE
      1   2   3    4   5   6 
̸  name owner species sex  birth death


 ڷḦ ԷϴĿ  ٸ  "밨"  ó  
 ִ.

 name   owner  species  sex  birth   death
-------------------------------------------
밨 ȫ浿          1998-3-4  NULL


VARCHAR ̰ ϴ ڿ Ѵ. ̸, ,  ̰ 
 ڿ  ڷ     ̴. VARCHAR  鿡 ,
̴ ݵ   ʿ䵵  20  ʿ䵵 . 1 255
̸   ִ. ϰ ָ ȴ(߿ ALTER TABLE   
 ִ). ̺  ͺ̽ ̺ Ͽ ߰Ǿ° 
Ȯ:

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

̺ Ѵ  Ȯϱ ؼ DESCRIBE  Ѵ:

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(2)     | YES |     | NULL    |      |
+---------+-------------+-----+-----+---------+------+
| birth   |  date       | YES |     | NULL    |      |
+---------+-------------+-----+-----+---------+------+
| death   |  date       | YES |     | NULL    |      |
+---------+-------------+-----+-----+---------+------+

Filed κа Type κ   ̸ ڷ Ȯ. DESCRIBE 
   ִ. ̺   ̸  ڷ ؾ  ϸ 
ϴ.

3.3 ̺ ڷḦ ־ .

̺  Ŀ ̺ ڷḦ ־ Ѵ. LOAD DATA Ȥ INSERT  
ϸ ȴ.

ֿϵ ڷᰡ  ٰ (MySQL YYYY-MM-DD  ¥ 
䱸Ѵ).

  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    Dianne     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

  ̺ ϹǷ ̸ Ͽ    ڷḦ  ΰ 
Ͽ о ̺ ä  ̴. 

pet.txt (̸ ƹ̵ )  ٿ ϳ ڵ带 
ϸ ȴ. ó:

# cat pet.txt
Fluffy  Harold cat      f       1993-02-04
- -

  Ű ϳ ϸ CREATE TABLE      
 ؾ Ѵ. ص Ǵ (  ¥ ) ؼ 
NULL   ִ. ؽƮ Ͽ NULL Ÿ ؼ \N ̶ 
ָ ȴ.    Whistler    ̴.

Whistler Gwen bird \N 1997-12-09 \N

pet.txt  εϱ ؼ ó LOAD DATA   Ѵ:

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

  :

LOAD DATA LOCAL INFILE "̸" INTO TABLE ̸̺;

ѹ   ڵ带 ߰ϰ    ̴. CREATE TABLE  
Ͽ       ָ ȴ.  ´ 
ڷ ̴:

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

ڿ  ¥  ǥ '  οϿ.   NULL Է 
 ִ(\N̶  ȵȴ). 


3.4 ̺κ  ˻ .

SELECT  ϸ ȴ. Ϲ   :

SELECT <˻> FROM <̺> WHERE <˻>

<˻>    ˸ ̴.   ǥ Ͽ 
     ǹϴ *    ִ. WHERE κ    
ִ. WHERE    ˻  ش. ˻  ʴ  
˻󿡼 ܵȴ.

3.4.1  ͸ ˻.

  SELECT  ·  ó   ִ:

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       |
+----------+--------+---------+------+------------+------------+

̷  SELECT ϴ  ̺ ü     ϴ.
  ʱ  ġ ÷   ÷ Ȯ   ִ.
   ׷,    ߸   ִ: Bower ڰ 
ں ʴ.  ڿ ¾?! Ȯ  birth 1989-08-31 
Ǿ    ־ٰ ϸ ̸  ĥ?

ΰ    ִ:

  pet.txt Ͽ Ѵ. ̺  pet.txt ٽ о 
δ:

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

 ̷ ϸ 3.3  insert ̿Ͽ Է Puffball
ؼ ٽ Էؾ Ѵ.  ϰ ٶ ?

 ߸  Ѵ. UPDATE  Ѵ:

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

   ֵ, ü ̺    . ׷   ̷ 
 ʴ´. ̺ ũⰡ Ŀ   ΰ?  ڷ ˻  
     ΰ?  Ư  ϴ ڷ鸸 ̾Ƽ 
  ̴.

3.4.2 ־ ǿ ´ Ư ุ ˻ .

 ̺ Ư 鸸 ̾Ƴ  ִ.   Bower  
 ٲ° Ȯϱ  Bower ڵ常 ̾Ƴ  ִ:

mysql> SELECT * FROM pet WHERE name = "Bower";
+--------+-------+---------+------+------------+-----------+
| name   | owner | species | sex  | birth      | death     |
+--------+-------+---------+------+------------+-----------+
| Bowser | Diane  | dog    | m    |1989-08-31  |1995-07-29 |
+--------+-------+---------+------+------------+-----------+

birth   1998 ƴ 1989 ùٸ Ǿ Ȯ  ִ.

ڿ 񱳴 ҹڸ ϴ 񱳴.  "bowser", "BOWSER"   
ڿ ǹѴ( "Bowser" ߴ).

  ؼ     ִ.   1998 Ŀ ¾ 
 ˰ ʹٸ birth   ˻ϸ ȴ:

mysql> SELECT * FROM pet WHERE birth >= "1998-1-1"
+----------+-------+---------+------+-----------+------+
| name     | owner | species | sex  | birth     |death |
+----------+-------+---------+------+-----------+------+
| Chirp y  | 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  |
+-------+--------+---------+------+------------+-------+

  ̸鼭   ˻ϴ ̴.

 AND Ͽ OR   ִ:

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  |
+----------+-------+---------+------+-----------+-------+

AND OR    ִ. ̷   ׷ ǵ ȣ 
  :

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  |
+-------+--------+---------+------+------------+-------+

3.4.3 Ư  ϱ

̺  ü  ٴ " Ÿ" 鸸  ʹٸ  ϴ 
 ̸ ó ϸ ȴ(Ʒ  name, birth   ̴):

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 |
+--------+

DISTINCT Ű带 ϸ ȴ.

 ó  ̸  Ӹƴ϶ WHERE  Ͽ  
߰ν  ð     ִ.     ̿ 
ؼ ̸, ,  ˰    ó SELECT   ִ:

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  |
+--------+---------+------------+

3.4.4  ϱ

ݱ     ĵǾ  ʾ    ̴. 
 ĵȴٸ ϴ ڷḦ ξ  ˾ƺ  ִ. ϱ ؼ 
ORDER BY  ϸ ȴ. Ʒ    ̴.

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 |
+----------+------------+

 Ϸ DESC Ű带  ̸ ڿ ش:

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 |
+----------+---------+-----------+

DESC Ű ٷ    ̸(birth) ȴٴ  ϶. 
species   ĵȴ.

3.4.5 ¥ 

MySQL ¥ ٷ  Լ  ش.

ֿ  ̰ 󸶳 Ǵ Ϸ  ¥  ¥ ϰ,
 ¥ ϼ ȯ ,    ϼ  365Ϸ  ָ  ̴:

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 |
+----------+-------------------------------------------+

TO_DAYS(NOW()) ݱ , TO_DAYS(birth) ϱ 
ǹѴ.

⼭ ΰ   .  ̸ Ȥ   ĵǾ 
ڰ, ̿ شϴ 󺧸 ǥ ״ °ͺٴ "age" 
ϴ   ̴:

mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age 
     -> FROM pet ORDER BY name;

̼ Ϸ ORDER BY name  ORDER BY age  ָ ȴ.

 ̵   ˾   ִ:

mysql> SELECT name, (TO_DAYS(death)-TO_DAYS(birth))/365 AS age 
     -> FROM pet WHERE death IS NOT NULL ORDER BY age;

NOW() death ϸ ȴ. ⼭      Ѵٴ
 ǹϱ  death ʵ尡 NULL ƴ 츦   ־ 
.   'death IS NOT NULL' ó  ־ Ѵ. 
death != NULL ó ־ ȵȴ. NULL  ڸ   . 
߿   ٽ ٷ ̴.

 ޿   ˷  ؾ ұ? ̷   MySQL
¥ ,  ϴ Լ Ѵ: YEAR(), MONTH(), DAY().MONTH()
  ˾ :

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 |
+----------+------------+--------------+

MONTH ޿ شϴ   ȯ ָ,     1 12̴. 
 ³ ؼ 1   ָ ȴ:

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 10;

׷  ִ. 12  13  ־ ϳ? 13̶ . 
  ̵  ο ǽ   Ѵ. ⿡ 
ΰ ҰѴ:

 MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));
NOW()  ¥  ð ȯ ش. ⿡ 1̶ Ⱓ ϰ 
޷ ٲٸ ذȴ.

 MOD(MONTH(NOW()), 12) + 1;
MOD   ٸ     ȯϴ Լ̴. ù°  ڸ 
ι° ڷ   ȯѴ. ⼭   MONTH(NOW()) 12 
    Ÿ  1  ش. ̹  12̶ 12 
 0̹Ƿ ⿡ 1   1 Ÿ  ִ.

 SQL   :

mysql> SELECT name, birth FROM pet
    ->  WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(NOW()),12) + 1;

3.4.6 NULL  

NULL Ư ̴. ͼ  ȥ ̴.  NULL 
ǹϴ ٴ ",  ", "   Ȯ " ǹѴ. 
̰ ٸ  ٸ ޵ȴ. NULL      .
  NULL =, <, != ̿Ͽ ϴ  ǹ̰ . Ȯ  
 Ȯ     ΰ?  :

mysql> SELECT 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
|1 = NULL  |1 != NULL  |1 < NULL  | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |     NULL  |     NULL |    NULL  |
+----------+-----------+----------+----------+

  Ȯ Ȯ 񱳴 Ȯ ȴ. ǹ̰ . 
׷   ϸ ǹ̰ ִ:

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|        0  |             1 |
+-----------+---------------+

MySQL   0  1 Ÿ.

3.4.7  ġ

 ġ  ſ  ̴.   ϰ ϴ  
Ͽ ˻  ְ ִ ̱ ̴.

MySQL ǥ SQL  Ӹƴ϶ н ϴ  ǥĿ شϴ 
 ġ ɵ Ѵ.

SQL _    ڸ ǹϸ, %   (0 ڸ )
Ų. SQL  ҹڸ  ʴ´. LIKE Ŀ  شٴ 
 . Ʒ  :

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       |
+----------+-------+---------+------+------------+------------+

Ȯϰ 5 ڷ ̷ ̸ ؼ ˻ ?

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  |
+-------+--------+---------+------+------------+-------+

 5  ش. 

 ǥ  ġ  ˾ .
----------------------------------------------------------------------
ǥĿ Ǵ     |               
------------------------------|---------------------------------------
       .		      |           ϳ
       *                      |   տ   0 ̻ ݺ
       ^                      |         ڿ ó
       $                      |         ڿ 
      [,]                     |   ȣ ڵ鿡 ġ
      {,}                     |   ݺ Ÿ .  n ݺ  
                              |   {n} ´.
----------------------------------------------------------------------


⼭ SQL  ü  ġؾ "ġѴ"  ǥ   
κа ġص "ġѴ" Ѵٴ  ؾ Ѵ.  ,

SELECT * FROM pet WHERE name REGEXP "ffy";



SELECT * FROM pet WHERE name LIKE "ffy";

  ٸ  ´.  ԷϿ ˾ƺ ٶ.

 a b c ϳ  Ű ǥ [abc]̴. [a-c]ó  ־ 
ǥ  ִ. ǥ ҹڸ Ѵ.  빮ڵ ҹڵ 
 ĺ  ϳ Ű ǥ [a-zA-Z] ؾ Ѵ.

* 0 ̻ ڵ̶ ߴ. x*  x, xx, xxx ...  شѴ. [0-9]* 7,
12, 345, 678  ̸   Ÿ. ^abc abc ϴ 
abc$ abc  ڿ ǹѴ. 

 ǥ   LIKE REGEXP Ѵ.

   .

̸ ҹ 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  |
+--------+--------+---------+------+-----------+-------+

Ȯϰ 5 ڷ ̸    ǥ ġѴ:

        ^.....$

̰ ݺ ڸ ̿Ͽ ó   ִ.

        ^.{5}$


3.4.8  

  ֿ  ̳ ߴ  ˾Ƴ  ? 

̿   COUNT()Լ ϸ Ǹ ϰ  ο ָ ȴ. 

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  |
+--------+----------+

 owner  ڵ ѵ   GROUP BY    ָ϶.
̷    ޽   ̴.

mysql> SELECT owner, COUNT(owner) FROM pet;
ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause

COUNT() GROUP BY Ϳ   Ư οϴ   ִ. 
 鵵 :

  شϴ  :

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 |
+------+----------+

NULL " " ǹ̴. 

    :

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 |
+---------+------+----------+

3.5 ̺  ϱ

pet ̺ ֿϵ    ִ. ǻ翡 ġḦ   
Ƚ   ¥  ǵ鿡  ٸ  ϰ ʹٸ 
 ̺ ʿ ̴. ̺    䱸 ̴:
 
 ش  ̸  ־ Ѵ.  ֿϵ Ͼ  
   кؾ ϱ ̴.
  Ͼ  ˱  ¥  ʿϴ.
     ʿ䰡 ִ.
  зϷ   Ÿ ʵ嵵   ̴.

̿   Ͽ, ó ̺  :

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

pet ̺ ó Ϸκ ͸ ̺ ø. 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;

ݱ pet ̺ ٷ鼭   event ̺   
Ǹ غ   ̴.      ϴ°? 

    ̸ ˷  ؾ ϴ°? event ̺ 
   Ҵ     ̶簡, ֶ簡 ϴ 
 pet ̺ ؼ ˾Ƴ Ѵ.  SELECT    ΰ 
̺ ʿϴ:

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         |
+--------+------+-----------------------------+

 κ  ˾Ƶξ   ִ:

 FROM   ̺   ־ Ѵ. ̰͵ ηκ 
    ʿϱ ̴.
  ̺  ̾    ̺ ڵ尡 ٸ ̺ 
   ڵ  ġϴ  ־ Ѵ. ⼭  ̺  name 
   ʵ带  Ƿ ̰ ̿ϸ ȴ.  WHERE 
   pet.name =  event.name  ν   ̺   شϴ 
   ڵ忡 ؼ Ǹ ϰ ȴ.  ٸٸ ǹ̰ .
  ̺  name ʵ带  Ƿ  ̺ ϴ ʵ
   ϱ  ≮̺>.<ʵ̸>   ־.  
   ̺ ̸ ʵ ̸  Ͽ  ش.

 ̺  ޶.    ̺ ؼ ó  
ʿ䰡   ִ.    ư  ¦ ַ  ؾ 
ϴ°?  ̺ ؼ   ٸ ˻ؾ Ѵ.   
 :

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     |
+--------+------+--------+------+---------+


3.6 ġ (ϰ ó ) ϱ

ݱ ȭ Ͽ. Ǹ  ְ    ݺ 
۾̾. 

۾  θ Ͽ    Ѳ ó  ִ. ̷ ϴ
۾ ġ ۾̶ Ѵٴ  ˾ .    Ѵ:

shell> mysql < batch-file

۾      ǥ Է  ȴ. ȣƮ  ڸ,
н带 Է ʿ䰡  ߰  ش:

shell> mysql -h host -u user -p < batch-file
Enter password: ********

 ġ 忡   ۼϴ  ٷ ũƮ ۼϴ 
̴.

⺻  ȭ  ʹ ٸ. SELECT DISTINCT species FROM pet 
  ȭİ ġ 忡  .     
ٸ.

ȭ:
+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

ġ :
species
bird
cat
dog
hamster
snake

ġ 忡 ȭ 忡    ϸ mysql  -t 
ɼ ָ ȴ.  Ǵ ɾ ¿ ϰ ʹٸ -vvv ٿ.

׷ٸ   ֱ淡 ġ 带 ұ?    ξ.

 Ǹ  Ѵٸ ũƮ  δ    Ź ٽ  
   ־ִ   ش.
 ̹ ۼ ũƮ Ͽ   ְ ο ũƮ ۼ  
   ִ  ִ.
  ٿ ġ ſ  Ǹ   ġ 尡   ̴.
   Ǽ   ȭ  ٽ ĳ־ ־ Ѵ. ġ ϶ 
   ϸ   ָ ȴ.  MySQL readline ̺귯(丮 
    ̺귯) ϹǷ ȭ   ٽ ɾ  ִ 
    ũ   ִ. 
   ſ ٸ ġ Ű (  ټ
    ִ α׷ Īϴ Ī)    ̴. 
    ó:

   shell> mysqlk < batch-file | less

   ٸ Ϸ   ִ.   ߰ ۾ 
    Ȱ  ִ.

   shell> mysql < batch-file > mysql.out

 ۼ ũƮ ٸ    ִ. ٸ   ۼ 
   ũƮ      ִ.
  ۾ ݻ ġ忡   ִ.  ð  
   ۾   cron ̿Ͽ ġ忡 ó  ۿ .
