■1. SELECT句とWHERE句

1. courseテーブルのすべてのデータ
+------+-----------------+-------+
| code | title           | price |
+------+-----------------+-------+
|  100 | PHP Programming | 30000 |
|  201 | Linux Basic     | 50000 |
|  202 | Linux Master    | 60000 |
|  101 | PHP Basic       | 25000 |
|  102 | PHP Master      | 25000 |
+------+-----------------+-------+
5 rows in set (0.00 sec)

2. courseテーブルのtitle列のデータ
+-----------------+
| title           |
+-----------------+
| PHP Programming |
| Linux Basic     |
| Linux Master    |
| PHP Basic       |
| PHP Master      |
+-----------------+
5 rows in set (0.00 sec)

3. scheduleテーブルのstart_date列とend_date列のデータ
+------------+------------+
| start_date | end_date   |
+------------+------------+
| 2010-07-30 | 2010-11-30 |
| 2010-10-10 | 2011-01-29 |
| 2010-06-18 | 2011-02-10 |
| 2010-09-27 | 2011-05-20 |
| 2011-02-10 | 2011-07-03 |
+------------+------------+
5 rows in set (0.00 sec)

4. courseテーブルのtitle列が 'PHP Programming' のデータ
+------+-----------------+-------+
| code | title           | price |
+------+-----------------+-------+
|  100 | PHP Programming | 30000 |
+------+-----------------+-------+
1 row in set (0.00 sec)

5. courseテーブルのprice列が3万円以上5万円以下のデータ
+------+-----------------+-------+
| code | title           | price |
+------+-----------------+-------+
|  100 | PHP Programming | 30000 |
|  201 | Linux Basic     | 50000 |
+------+-----------------+-------+
2 rows in set (0.00 sec)

6. scheduleテーブルのstaff列が10または20のデータ
+------+--------+-------+------------+------------+
| code | cource | staff | start_date | end_date   |
+------+--------+-------+------------+------------+
| 1001 |    100 |    10 | 2010-07-30 | 2010-11-30 |
| 1002 |    100 |    20 | 2010-10-10 | 2011-01-29 |
| 1003 |    201 |    10 | 2010-06-18 | 2011-02-10 |
| 1005 |    202 |    20 | 2011-02-10 | 2011-07-03 |
+------+--------+-------+------------+------------+
4 rows in set (0.00 sec)

7. scheduleテーブルのstart_date列が2010年9月以降のデータ
+------+--------+-------+------------+------------+
| code | cource | staff | start_date | end_date   |
+------+--------+-------+------------+------------+
| 1002 |    100 |    20 | 2010-10-10 | 2011-01-29 |
| 1004 |    201 |    30 | 2010-09-27 | 2011-05-20 |
| 1005 |    202 |    20 | 2011-02-10 | 2011-07-03 |
+------+--------+-------+------------+------------+
3 rows in set (0.00 sec)

8. scheduleテーブルのend_date列が2011年4月~9月のデータ
+------+--------+-------+------------+------------+
| code | cource | staff | start_date | end_date   |
+------+--------+-------+------------+------------+
| 1004 |    201 |    30 | 2010-09-27 | 2011-05-20 |
| 1005 |    202 |    20 | 2011-02-10 | 2011-07-03 |
+------+--------+-------+------------+------------+
2 rows in set (0.00 sec)


■2. 表示順

1. courseテーブルのすべてのデータ ... code順
+------+-----------------+-------+
| code | title           | price |
+------+-----------------+-------+
|  100 | PHP Programming | 30000 |
|  101 | PHP Basic       | 25000 |
|  102 | PHP Master      | 25000 |
|  201 | Linux Basic     | 50000 |
|  202 | Linux Master    | 60000 |
+------+-----------------+-------+
5 rows in set (0.00 sec)


2. courseテーブルのtitle列のデータ ... title順
+-----------------+
| title           |
+-----------------+
| Linux Basic     |
| Linux Master    |
| PHP Basic       |
| PHP Master      |
| PHP Programming |
+-----------------+
5 rows in set (0.00 sec)


3. scheduleテーブルのstart_date列とend_date列のデータ ... end_date順
+------------+------------+
| start_date | end_date   |
+------------+------------+
| 2010-07-30 | 2010-11-30 |
| 2010-10-10 | 2011-01-29 |
| 2010-06-18 | 2011-02-10 |
| 2010-09-27 | 2011-05-20 |
| 2011-02-10 | 2011-07-03 |
+------------+------------+
5 rows in set (0.00 sec)


5. courseテーブルのprice列が3万円以上5万円以下のデータ ... priceの降順
+------+-----------------+-------+
| code | title           | price |
+------+-----------------+-------+
|  201 | Linux Basic     | 50000 |
|  100 | PHP Programming | 30000 |
+------+-----------------+-------+
2 rows in set (0.00 sec)


6. scheduleテーブルのstaff列が10または20のデータ ... course順
+------+--------+-------+------------+------------+
| code | cource | staff | start_date | end_date   |
+------+--------+-------+------------+------------+
| 1004 |    201 |    30 | 2010-09-27 | 2011-05-20 |
| 1002 |    100 |    20 | 2010-10-10 | 2011-01-29 |
| 1005 |    202 |    20 | 2011-02-10 | 2011-07-03 |
+------+--------+-------+------------+------------+
3 rows in set (0.00 sec)


7. scheduleテーブルのstart_date列が2010年9月以降のデータ ... start_date順
+------+--------+-------+------------+------------+
| code | cource | staff | start_date | end_date   |
+------+--------+-------+------------+------------+
| 1004 |    201 |    30 | 2010-09-27 | 2011-05-20 |
| 1002 |    100 |    20 | 2010-10-10 | 2011-01-29 |
| 1005 |    202 |    20 | 2011-02-10 | 2011-07-03 |
+------+--------+-------+------------+------------+
3 rows in set (0.00 sec)


8. scheduleテーブルのend_date列が2011年4月?9月のデータ ... start_date順
+------+--------+-------+------------+------------+
| code | cource | staff | start_date | end_date   |
+------+--------+-------+------------+------------+
| 1004 |    201 |    30 | 2010-09-27 | 2011-05-20 |
| 1005 |    202 |    20 | 2011-02-10 | 2011-07-03 |
+------+--------+-------+------------+------------+
2 rows in set (0.00 sec)


■3. 集約関数

1. courseテーブルのすべてのデータ... priceの最小値と最大値、合計値、平均値
+------------+------------+------------+------------+
| min(price) | max(price) | sum(price) | avg(price) |
+------------+------------+------------+------------+
|      25000 |      60000 |     190000 | 38000.0000 |
+------------+------------+------------+------------+
1 row in set (0.00 sec)


5. courseテーブルのprice列が3万円以上5万円以下のデータ ... priceの平均値
+------------+
| avg(price) |
+------------+
| 40000.0000 |
+------------+
1 row in set (0.00 sec)


6. scheduleテーブルのstaff列が10または20のデータ ... start_date の最小値
+-----------------+
| min(start_date) |
+-----------------+
| 2010-06-18      |
+-----------------+
1 row in set (0.00 sec)

 
7. scheduleテーブルのstart_date列が2010年9月以降のデータ... start_date の最大値
+-----------------+
| max(start_date) |
+-----------------+
| 2011-02-10      |
+-----------------+
1 row in set (0.00 sec)