mysql> SELECT * FROM member;
+-----------+--------+----------+------+
| member_id | name   | phone    | age  |
+-----------+--------+----------+------+
| N1        | James  | 010-7777 | 43   |
| N2        | John   | 010-8888 | 35   |
| N3        | James  | 010-1111 | 24   |
| N4        | Tom    | 010-2222 | 34   |
| N5        | Robert | 010-3333 | 54   |
+-----------+--------+----------+------+
5 rows in set (0.00 sec)

mysql> SELECT name, phone FROM member;
+--------+----------+
| name   | phone    |
+--------+----------+
| James  | 010-7777 |
| John   | 010-8888 |
| James  | 010-1111 |
| Tom    | 010-2222 |
| Robert | 010-3333 |
+--------+----------+
5 rows in set (0.00 sec)

mysql> SELECT DISTINCT name FROM member;
+--------+
| name   |
+--------+
| James  |
| John   |
| Tom    |
| Robert |
+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM tape;
+---------+---------------+---------+--------+
| tape_id | title         | genre   | rating |
+---------+---------------+---------+--------+
| T1      | Love Actually | romance |      2 |
| T2      | 50/50         | NULL    |      3 |
| T3      | Iron Man 1    | action  |      2 |
| T4      | Iron Man 2    | action  |      1 |
| T5      | If only       | romance |      1 |
+---------+---------------+---------+--------+
5 rows in set (0.00 sec)

mysql> SELECT rating * 5 FROM tape;
+------------+
| rating * 5 |
+------------+
|         10 |
|         15 |
|         10 |
|          5 |
|          5 |
+------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM rental;
+-----------+---------+------------+
| member_id | tape_id | date       |
+-----------+---------+------------+
| N1        | T1      | 1999-02-01 |
| N1        | T2      | 1999-02-01 |
| N3        | T2      | 1999-02-01 |
| N1        | T3      | 1999-02-01 |
| N3        | T3      | 1999-02-01 |
| N4        | T3      | 1999-02-01 |
| N2        | T4      | 1999-02-01 |
| N4        | T4      | 1999-02-01 |
| N2        | T5      | 1999-02-01 |
| N5        | T5      | 1999-02-01 |
+-----------+---------+------------+
10 rows in set (0.00 sec)

mysql> SELECT COUNT(DISTINCT tape_id) FROM rental;
+-------------------------+
| COUNT(DISTINCT tape_id) |
+-------------------------+
|                       5 |
+-------------------------+
1 row in set (0.01 sec)

mysmysql> SELECT COUNT(DISTINCT tape_id) FROM rental;
+-------------------------+
| COUNT(DISTINCT tape_id) |
+-------------------------+
|                       5 |
+-------------------------+
1 row in set (0.01 sec)

• 조건 지정 검색

mysql> SELECT name, phone, age FROM member WHERE age >=30;
+--------+----------+------+
| name   | phone    | age  |
+--------+----------+------+
| James  | 010-7777 | 43   |
| John   | 010-8888 | 35   |
| Tom    | 010-2222 | 34   |
| Robert | 010-3333 | 54   |
+--------+----------+------+
4 rows in set (0.00 sec)

mysql> SELECT name FROM member WHERE age >=30 AND age <=39;
+------+
| name |
+------+
| John |
| Tom  |
+------+

mysql> SELECT title FROM tape WHERE genre IS NULL;
+-------+
| title |
+-------+
| 50/50 |
+-------+

mysql> SELECT name, phone FROM member WHERE name LIKE 'J%';
+-------+----------+
| name  | phone    |
+-------+----------+
| James | 010-7777 |
| John  | 010-8888 |
| James | 010-1111 |
+-------+----------+

mysql> SELECT name FROM member WHERE name LIKE '%t';
+--------+
| name   |
+--------+
| Robert |
+--------+
1 row in set (0.00 sec)

• 부속 질의

mysql> SELECT name, phone FROM member WHERE member_id IN(
    -> SELECT member_id FROM rental WHERE tape_id = 'T3');
+-------+----------+
| name  | phone    |
+-------+----------+
| James | 010-7777 |
| James | 010-1111 |
| Tom   | 010-2222 |
+-------+----------+

mysql> SELECT name FROM member WHERE EXISTS (
    -> SELECT * FROM rental WHERE member.member_id = rental.member_id AND tape_id = 'T3');
+-------+
| name  |
+-------+
| James |
| James |
| Tom   |
+-------+

*정렬 검색

mysql> SELECT name, phone, age FROM member WHERE age >=30 ORDER BY age ASC;
+--------+----------+------+
| name   | phone    | age  |
+--------+----------+------+
| Tom    | 010-2222 | 34   |
| John   | 010-8888 | 35   |
| James  | 010-7777 | 43   |
| Robert | 010-3333 | 54   |
+--------+----------+------+

mysql> SELECT tape_id, title, rating FROM tape WHERE genre IS NOT NULL ORDER BY rating ASC, tape_id DESC;
+---------+---------------+--------+
| tape_id | title         | rating |
+---------+---------------+--------+
| T5      | If only       |      1 |
| T4      | Iron Man 2    |      1 |
| T3      | Iron Man 1    |      2 |
| T1      | Love Actually |      2 |
+---------+---------------+--------+

• 복수 테이블 검색

mysql> SELECT name, phone FROM member, rental WHERE member.member_id = rental.member_id AND tape_id = 'T3';
+-------+----------+
| name  | phone    |
+-------+----------+
| James | 010-7777 |
| James | 010-1111 |
| Tom   | 010-2222 |
+-------+----------+

• 그룹 지정 검색

mysql> SELECT member_id, COUNT(*) FROM rental GROUP BY member_id;
+-----------+----------+
| member_id | COUNT(*) |
+-----------+----------+
| N1        |        3 |
| N2        |        2 |
| N3        |        2 |
| N4        |        2 |
| N5        |        1 |
+-----------+----------+

mysql> SELECT member_id, COUNT(*) FROM rental GROUP BY member_id HAVING COUNT(*) >2 ;
+-----------+----------+
| member_id | COUNT(*) |
+-----------+----------+
| N1        |        3 |
+-----------+----------+

• 집합

mysql> SELECT member_id FROM member WHERE age >=35 UNION SELECT member_id FROM rental WHERE tape_id = 'T3';
+-----------+
| member_id |
+-----------+
| N1        |
| N2        |
| N5        |
| N3        |
| N4        |
+-----------+

mysql> SELECT member_id FROM member WHERE age >=35 INTERSECT SELECT member_id FROM rental WHERE tape_id = 'T3';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERSECT SELECT member_id FROM rental WHERE tape_id = 'T3'' at line 1

mysql> SELECT member_id FROM member WHERE age >=35 MINUS SELECT member_id FROM rental WHERE tape_id = 'T3';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MINUS SELECT member_id FROM rental WHERE tape_id = 'T3'' at line 1