SELECT attributes
FROM tables
WHERE conditions;
id๊ฐ 9์ธ ์ง์์ ์ด๋ฆ๊ณผ ์ง๊ตฐ์ ์ถ๋ ฅํ๋ผ
mysql> SELECT name, position FROM employee WHERE id = 9;
+-------+----------+
| name | position |
+-------+----------+
| HENRY | HR |
+-------+----------+
์กฐ๊ฑด id = 9
๋ฅผ selection condition ์ด๋ผ๊ณ ํ๋ค.
name, position
์ projection attributes ๋ผ๊ณ ํ๋ค. ์กฐ๊ฑด์ ๋ง๋ tuples์ ๊ฐ๋ค ์ค์์ projection attributes์ ์ํด ์ง์ ๋ attribute์ ๋์ํ๋ ๊ฐ๋ง ์ถ๋ ฅํ๋ค.
project 2002๋ฅผ ๋ฆฌ๋ฉํ๋ ์์ง์์ id์ ์ด๋ฆ๊ณผ ์ง๊ตฐ์ ์ถ๋ ฅํ๋ผ
mysql> SELECT employee.id, employee.name, employee.position
-> FROM employee, project
-> WHERE project.id = 2002 AND project.leader_id = employee.id;
+----+--------+----------+
| id | name | position |
+----+--------+----------+
| 13 | JISUNG | PO |
+----+--------+----------+
project.leader_id = employee.id
๋ฅผ join condition์ด๋ผ๊ณ ํ๋ค.
project.id = 2002 ์ธ ํํ๊ณผ join condition์ผ๋ก ์ธํด ์ด์ ๋์ํ๋ employee ํ ์ด๋ธ์ ํํ๋ ์ ํ์ด ๋๋ค.
๋ง์ง๋ง์ผ๋ก employee.id, employee.name, employee.position
๋ผ๋ projection attributes๋ฅผ ํตํด ํํ ์ค projection attributes์ ๋์ํ๋ ๊ฐ๋ง ์ถ๋ ฅ๋๋ ๊ฒ์ด๋ค.
SELECT๋ก ์กฐํํ ๋ ์กฐ๊ฑด๋ค์ ํฌํจํด์ ์กฐํ๋ฅผ ํ๋ค๋ฉด ์ด ์กฐ๊ฑด๋ค๊ณผ ๊ด๋ จ๋ attributes์ index๊ฐ ๊ฑธ๋ ค์์ด์ผ ํ๋ค. ๊ทธ๋ ์ง ์์ผ๋ฉด ๋ฐ์ดํฐ๊ฐ ๋ง์์ง์๋ก ์กฐํ ์๋๊ฐ ๋๋ ค์ง๊ฒ ๋๋ค.
ํ ์ด๋ธ์ด๋ฆ์ ๋ณ์นญ์ผ๋ก ๋ฐ๊ฟ ์ ์๋ค.
mysql> SELECT E.id, E.name, E.position
-> FROM employee AS E, project AS P
-> WHERE P.id = 2002 AND P.leader_id = E.id;
+----+--------+----------+
| id | name | position |
+----+--------+----------+
| 13 | JISUNG | PO |
+----+--------+----------+
์ถ๋ ฅ๋๋ attribute ์ด๋ฆ์ ๋ฐ๊ฟ ์ ์๋ค.
mysql> SELECT E.id AS leader_id, E.name AS leader_name, E.position
-> FROM employee AS E, project AS P
-> WHERE P.id = 2002 AND P.leader_id = E.id;
+-----------+-------------+----------+
| leader_id | leader_name | position |
+-----------+-------------+----------+
| 13 | JISUNG | PO |
+-----------+-------------+----------+
AS๋ ์๋ต์ด ๊ฐ๋ฅํ๋ค.
mysql> SELECT E.id leader_id, E.name leader_name, E.position
-> FROM employee E, project P
-> WHERE P.id = 2002 AND P.leader_id = E.id;
+-----------+-------------+----------+
| leader_id | leader_name | position |
+-----------+-------------+----------+
| 13 | JISUNG | PO |
+-----------+-------------+----------+
์ง๊ตฐ์ด ๋์์ด๋์ธ ์์ง์๋ค์ด ์ฐธ์ฌํ๊ณ ์๋ ํ๋ก์ ํธ๋ค์ id์ ์ด๋ฆ์ ์ถ๋ ฅํ๋ผ
3๊ฐ์ ํ ์ด๋ธ์ ์ฐธ๊ณ ํด์ผํ๋ค.
employee ํ ์ด๋ธ๊ณผ project ํ ์ด๋ธ์ ์ฐ๊ฒฐ์ํค๊ธฐ ์ํด works_on ํ ์ด๋ธ์ด ํ์ํ๋ค.
mysql> SELECT P.id, P.name
-> FROM employee E, works_on W, project P
-> WHERE E.position = 'DSGN'
-> AND E.id = W.empl_id AND P.id = W.proj_id;
+------+------------------+
| id | name |
+------+------------------+
| 2003 | ํํ์ด์ง UI ๊ฐ์ |
| 2003 | ํํ์ด์ง UI ๊ฐ์ |
+------+------------------+
mysql> SELECT DISTINCT P.id, P.name
-> FROM employee E, works_on W, project P
-> WHERE E.position = 'DSGN'
-> AND E.id = W.empl_id AND P.id = W.proj_id;
+------+------------------+
| id | name |
+------+------------------+
| 2003 | ํํ์ด์ง UI ๊ฐ์ |
+------+------------------+
ํญ๋ชฉ | ์ค๋ช |
---|---|
LIKE | ๋ฌธ์์ด pattern matching์ ์ฌ์ฉ |
% | 0๊ฐ ์ด์์ ์์์ ๊ฐ์๋ฅผ ๊ฐ์ง๋ ๋ฌธ์๋ค์ ์๋ฏธ |
_ | ํ๋์ ๋ฌธ์๋ฅผ ์๋ฏธ |
\ | % , _ ๋ฅผ ๋ฌธ์ ๊ทธ๋๋ก์ ์๋ฏธ๋ก ์ฌ์ฉํ๊ณ ์ถ์๋ ์ฌ์ฉ |
์ด๋ฆ์ด N์ผ๋ก ์์ํ๊ฑฐ๋ N์ผ๋ก ๋๋๋ ์ง์์ ์ด๋ฆ์ ์ถ๋ ฅํ๋ผ
mysql> SELECT name
-> FROM employee
-> WHERE name LIKE 'N%' OR name LIKE '%N';
+--------+
| name |
+--------+
| BROWN |
| NICOLE |
+--------+
name LIKE 'N%'
: ์ด๋ฆ์ด N์ผ๋ก ์์
name LIKE '%N'
: ์ด๋ฆ์ด N์ผ๋ก ๋๋จ
์ด๋ฆ์ NG๊ฐ ํฌํจ๋์ด์๋ ์ง์์ ์ด๋ฆ์ ์ถ๋ ฅํ๋ผ
mysql> SELECT name
-> FROM employee
-> WHERE name LIKE '%NG%';
+--------+
| name |
+--------+
| DINGYO |
| JISUNG |
+--------+
name LIKE '%NG%'
: ์ด๋ฆ์ NG๊ฐ ํฌํจ๋์ด ์์์ด๋ฆ์ด J๋ก ์์ํ๊ณ 4๊ธ์์ธ ์ง์์ ์ด๋ฆ์ ์ถ๋ ฅํ๋ผ
mysql> SELECT name
-> FROM employee
-> WHERE name LIKE 'J___';
+------+
| name |
+------+
| JANE |
+------+
name LIKE 'J___'
: ์ด๋ฆ์ด J๋ก ์์ํ๊ณ 4๊ธ์%
๋ _
๋ฅผ ๋ฌธ์ ๊ทธ๋๋ก์ ์๋ฏธ๋ก ์ฌ์ฉํ๊ณ ์ถ์๋ \%
, \_
๋ก ์์ \
๋ฅผ ๋ถ์ด๋ฉด ๋๋ค.
mysql> SELECT name
-> FROM project
-> WHERE name LIKE '\%%';
name LIKE '\%%'
: %๋ก ์์ํ๋ project ์ด๋ฆ
name LIKE '%\_'
: _๋ก ๋๋๋ project ์ด๋ฆ