■1. select句とwhere句 1. courseテーブルのすべてのデータ mysql> select * from 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列のデータ mysql> select title from course; +-----------------+ | title | +-----------------+ | PHP Programming | | Linux Basic | | Linux Master | | PHP Basic | | PHP Master | +-----------------+ 5 rows in set (0.00 sec) 3. scheduleテーブルのstart_date列とend_date列のデータ mysql> select start_date, end_date from schedule; +------------+------------+ | 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' のデータ mysql> select * from course where title = 'PHP Programming'; +------+-----------------+-------+ | code | title | price | +------+-----------------+-------+ | 100 | PHP Programming | 30000 | +------+-----------------+-------+ 1 row in set (0.00 sec) ※文字列は、単一引用符と二重引用符を区別しない mysql> select * from course where title = "PHP Programming"; +------+-----------------+-------+ | code | title | price | +------+-----------------+-------+ | 100 | PHP Programming | 30000 | +------+-----------------+-------+ 1 row in set (0.00 sec) ※文字列では、大文字小文字を意識しない mysql> select * from course where title = 'php programming'; +------+-----------------+-------+ | code | title | price | +------+-----------------+-------+ | 100 | PHP Programming | 30000 | +------+-----------------+-------+ 1 row in set (0.00 sec) 5. courseテーブルのprice列が3万円以上5万円以下のデータ mysql> select * from course where price >= 30000 and price <= 50000; +------+-----------------+-------+ | code | title | price | +------+-----------------+-------+ | 100 | PHP Programming | 30000 | | 201 | Linux Basic | 50000 | +------+-----------------+-------+ 2 rows in set (0.00 sec) 6. scheduleテーブルのstaff列が10または20のデータ mysql> select * from schedule where staff = 10 or staff = 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月以降のデータ mysql> select * from schedule where start_date >= '2010-09-01'; +------+--------+-------+------------+------------+ | 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月のデータ mysql> select * from schedule where end_date >= '2011-04-01' and end_date <= '2011-09-30'; +------+--------+-------+------------+------------+ | 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順 mysql> select * from course order by 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順 mysql> select title from course order by 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順 mysql> select start_date, end_date from schedule order by 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の降順 mysql> select * from course where (price >= 30000) and (price <= 50000) order by price desc; +------+-----------------+-------+ | code | title | price | +------+-----------------+-------+ | 201 | Linux Basic | 50000 | | 100 | PHP Programming | 30000 | +------+-----------------+-------+ 2 rows in set (0.00 sec) 6. scheduleテーブルのstaff列が10または20のデータ ... course順 mysql> select * from schedule where start_date >= '2010-09-01' order by 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) 7. scheduleテーブルのstart_date列が2010年9月以降のデータ ... start_date順 mysql> select * from schedule where end_date >= '2011-04-01' and end_date <= '2011-09-30' order by start_date; mysql> select * from schedule where start_date >= '2010-09-01' order by 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順 mysql> select * from schedule where end_date >= '2011-04-01' and end_date <= '2011-09-30' order by 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の最小値と最大値、合計値、平均値 mysql> select min(price), max(price), sum(price), avg(price) from course; +------------+------------+------------+------------+ | 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の平均値 mysql> select avg(price) from course where (price >= 30000) and (price <= 50000); +------------+ | avg(price) | +------------+ | 40000.0000 | +------------+ 1 row in set (0.00 sec) 6. scheduleテーブルのstaff列が10または20のデータ ... start_date の最小値 mysql> select min(start_date) from schedule where staff = 10 or staff = 20; +-----------------+ | min(start_date) | +-----------------+ | 2010-06-18 | +-----------------+ 1 row in set (0.00 sec) 7. scheduleテーブルのstart_date列が2010年9月以降のデータ... start_date の最大値 mysql> select max(start_date) from schedule where start_date >= '2010-09-01'; +-----------------+ | max(start_date) | +-----------------+ | 2011-02-10 | +-----------------+ 1 row in set (0.00 sec)