MySQL学習ノート(三)
60951 ワード
1、データの取得
たんれつたんさく
複数カラムの取得
実際のカラム名の位置に(*)ワイルドカードを使用すると、SELECT文ですべてのカラムを検索できます.
異なるローの取得
DISTINCTキーワードでMysqlが異なる行だけを返すことができます
制限結果
最初の行または最初の行のみを返し、LIMIT句を使用します.
2、並べ替えデータ
複数列ソート
ソート順の指定
DESCキーワードは、その直前にあるカラム名にのみ適用されます.DESC:降順、ASC:昇順(デフォルト)
3、データのフィルタリング(WHERE句を使用)
WHERE句の位置:ORDER BYとWHERE句を同時に使用する場合、ORDER BYをWHEREの後に置くべきです.そうしないとエラーが発生します.
その他のWHERE句オペレータの使用
Null値チェック
ANDオペレータ
ORオペレータ
INオペレータ
NOTオペレータ
たんれつたんさく
1 mysql> SELECT prod_name
2 -> FROM products;
3 +----------------+
4 | prod_name |
5 +----------------+
6 | .5 ton anvil |
7 | 1 ton anvil |
8 | 2 ton anvil |
9 | Detonator |
10 | Bird seed |
11 | Carrots |
12 | Fuses |
13 | JetPack 1000 |
14 | JetPack 2000 |
15 | Oil can |
16 | Safe |
17 | Sling |
18 | TNT (1 stick) |
19 | TNT (5 sticks) |
20 +----------------+
21 14 rows in set (0.00 sec)複数カラムの取得
1 mysql> SELECT prod_id, prod_name, prod_price
2 -> FROM products;
3 +---------+----------------+------------+
4 | prod_id | prod_name | prod_price |
5 +---------+----------------+------------+
6 | ANV01 | .5 ton anvil | 5.99 |
7 | ANV02 | 1 ton anvil | 9.99 |
8 | ANV03 | 2 ton anvil | 14.99 |
9 | DTNTR | Detonator | 13.00 |
10 | FB | Bird seed | 10.00 |
11 | FC | Carrots | 2.50 |
12 | FU1 | Fuses | 3.42 |
13 | JP1000 | JetPack 1000 | 35.00 |
14 | JP2000 | JetPack 2000 | 55.00 |
15 | OL1 | Oil can | 8.99 |
16 | SAFE | Safe | 50.00 |
17 | SLING | Sling | 4.49 |
18 | TNT1 | TNT (1 stick) | 2.50 |
19 | TNT2 | TNT (5 sticks) | 10.00 |
20 +---------+----------------+------------+
21 14 rows in set (0.00 sec)実際のカラム名の位置に(*)ワイルドカードを使用すると、SELECT文ですべてのカラムを検索できます.
異なるローの取得
DISTINCTキーワードでMysqlが異なる行だけを返すことができます
mysql> SELECT DISTINCT vend_id
-> FROM products;
+---------+
| vend_id |
+---------+
| 1001 |
| 1002 |
| 1003 |
| 1005 |
+---------+
4 rows in set (0.08 sec)制限結果
最初の行または最初の行のみを返し、LIMIT句を使用します.
1 mysql> SELECT prod_name
2 -> FROM products
3 -> LIMIT 5;
4 +--------------+
5 | prod_name |
6 +--------------+
7 | .5 ton anvil |
8 | 1 ton anvil |
9 | 2 ton anvil |
10 | Detonator |
11 | Bird seed |
12 +--------------+
13 5 rows in set (0.00 sec)2、並べ替えデータ
1 mysql> SELECT prod_name
2 -> FROM products
3 -> ORDER BY prod_name;
4 +----------------+
5 | prod_name |
6 +----------------+
7 | .5 ton anvil |
8 | 1 ton anvil |
9 | 2 ton anvil |
10 | Bird seed |
11 | Carrots |
12 | Detonator |
13 | Fuses |
14 | JetPack 1000 |
15 | JetPack 2000 |
16 | Oil can |
17 | Safe |
18 | Sling |
19 | TNT (1 stick) |
20 | TNT (5 sticks) |
21 +----------------+
22 14 rows in set (0.27 sec)複数列ソート
1 mysql> SELECT prod_id, prod_price, prod_name
2 -> FROM products
3 -> ORDER BY prod_price, prod_name;
4 +---------+------------+----------------+
5 | prod_id | prod_price | prod_name |
6 +---------+------------+----------------+
7 | FC | 2.50 | Carrots |
8 | TNT1 | 2.50 | TNT (1 stick) |
9 | FU1 | 3.42 | Fuses |
10 | SLING | 4.49 | Sling |
11 | ANV01 | 5.99 | .5 ton anvil |
12 | OL1 | 8.99 | Oil can |
13 | ANV02 | 9.99 | 1 ton anvil |
14 | FB | 10.00 | Bird seed |
15 | TNT2 | 10.00 | TNT (5 sticks) |
16 | DTNTR | 13.00 | Detonator |
17 | ANV03 | 14.99 | 2 ton anvil |
18 | JP1000 | 35.00 | JetPack 1000 |
19 | SAFE | 50.00 | Safe |
20 | JP2000 | 55.00 | JetPack 2000 |
21 +---------+------------+----------------+
22 14 rows in set (0.03 sec)ソート順の指定
1 mysql> SELECT prod_id, prod_price, prod_name
2 -> FROM products
3 -> ORDER BY prod_price DESC;
4 +---------+------------+----------------+
5 | prod_id | prod_price | prod_name |
6 +---------+------------+----------------+
7 | JP2000 | 55.00 | JetPack 2000 |
8 | SAFE | 50.00 | Safe |
9 | JP1000 | 35.00 | JetPack 1000 |
10 | ANV03 | 14.99 | 2 ton anvil |
11 | DTNTR | 13.00 | Detonator |
12 | TNT2 | 10.00 | TNT (5 sticks) |
13 | FB | 10.00 | Bird seed |
14 | ANV02 | 9.99 | 1 ton anvil |
15 | OL1 | 8.99 | Oil can |
16 | ANV01 | 5.99 | .5 ton anvil |
17 | SLING | 4.49 | Sling |
18 | FU1 | 3.42 | Fuses |
19 | FC | 2.50 | Carrots |
20 | TNT1 | 2.50 | TNT (1 stick) |
21 +---------+------------+----------------+
22 14 rows in set (0.00 sec)DESCキーワードは、その直前にあるカラム名にのみ適用されます.DESC:降順、ASC:昇順(デフォルト)
3、データのフィルタリング(WHERE句を使用)
1 mysql> SELECT prod_name, prod_price
2 -> FROM products
3 -> WHERE prod_price = 2.50;
4 +---------------+------------+
5 | prod_name | prod_price |
6 +---------------+------------+
7 | Carrots | 2.50 |
8 | TNT (1 stick) | 2.50 |
9 +---------------+------------+
10 2 rows in set (0.03 sec)WHERE句の位置:ORDER BYとWHERE句を同時に使用する場合、ORDER BYをWHEREの後に置くべきです.そうしないとエラーが発生します.
その他のWHERE句オペレータの使用
1 mysql> SELECT prod_name, prod_price
2 -> FROM products
3 -> WHERE prod_name = 'fuses';
4 +-----------+------------+
5 | prod_name | prod_price |
6 +-----------+------------+
7 | Fuses | 3.42 |
8 +-----------+------------+
9 1 row in set (0.00 sec) 1 mysql> SELECT prod_name, prod_price
2 -> FROM products
3 -> WHERE prod_price < 10;
4 +---------------+------------+
5 | prod_name | prod_price |
6 +---------------+------------+
7 | .5 ton anvil | 5.99 |
8 | 1 ton anvil | 9.99 |
9 | Carrots | 2.50 |
10 | Fuses | 3.42 |
11 | Oil can | 8.99 |
12 | Sling | 4.49 |
13 | TNT (1 stick) | 2.50 |
14 +---------------+------------+
15 7 rows in set (0.27 sec) 1 mysql> SELECT vend_id, prod_name
2 -> FROM products
3 -> WHERE vend_id <> 1003;
4 +---------+--------------+
5 | vend_id | prod_name |
6 +---------+--------------+
7 | 1001 | .5 ton anvil |
8 | 1001 | 1 ton anvil |
9 | 1001 | 2 ton anvil |
10 | 1002 | Fuses |
11 | 1005 | JetPack 1000 |
12 | 1005 | JetPack 2000 |
13 | 1002 | Oil can |
14 +---------+--------------+
15 7 rows in set (0.53 sec) 1 mysql> SELECT prod_name, prod_price
2 -> FROM products
3 -> WHERE prod_price BETWEEN 5 AND 10;
4 +----------------+------------+
5 | prod_name | prod_price |
6 +----------------+------------+
7 | .5 ton anvil | 5.99 |
8 | 1 ton anvil | 9.99 |
9 | Bird seed | 10.00 |
10 | Oil can | 8.99 |
11 | TNT (5 sticks) | 10.00 |
12 +----------------+------------+
13 5 rows in set (0.00 sec)Null値チェック
1 mysql> SELECT cust_id
2 -> FROM customers
3 -> WHERE cust_email IS NULL;
4 +---------+
5 | cust_id |
6 +---------+
7 | 10002 |
8 | 10005 |
9 +---------+
10 2 rows in set (0.06 sec)ANDオペレータ
1 mysql> SELECT prod_id, prod_price, prod_name
2 -> FROM products
3 -> WHERE vend_id = 1003 AND prod_price <= 10;
4 +---------+------------+----------------+
5 | prod_id | prod_price | prod_name |
6 +---------+------------+----------------+
7 | FB | 10.00 | Bird seed |
8 | FC | 2.50 | Carrots |
9 | SLING | 4.49 | Sling |
10 | TNT1 | 2.50 | TNT (1 stick) |
11 | TNT2 | 10.00 | TNT (5 sticks) |
12 +---------+------------+----------------+
13 5 rows in set (0.00 sec)ORオペレータ
1 mysql> SELECT prod_name, prod_price
2 -> FROM products
3 -> WHERE vend_id = 1002 OR vend_id = 1003;
4 +----------------+------------+
5 | prod_name | prod_price |
6 +----------------+------------+
7 | Detonator | 13.00 |
8 | Bird seed | 10.00 |
9 | Carrots | 2.50 |
10 | Fuses | 3.42 |
11 | Oil can | 8.99 |
12 | Safe | 50.00 |
13 | Sling | 4.49 |
14 | TNT (1 stick) | 2.50 |
15 | TNT (5 sticks) | 10.00 |
16 +----------------+------------+
17 9 rows in set (0.00 sec)INオペレータ
1 mysql> SELECT prod_name, prod_price
2 -> FROM products
3 -> WHERE vend_id IN (1002, 1003)
4 -> ORDER BY prod_name;
5 +----------------+------------+
6 | prod_name | prod_price |
7 +----------------+------------+
8 | Bird seed | 10.00 |
9 | Carrots | 2.50 |
10 | Detonator | 13.00 |
11 | Fuses | 3.42 |
12 | Oil can | 8.99 |
13 | Safe | 50.00 |
14 | Sling | 4.49 |
15 | TNT (1 stick) | 2.50 |
16 | TNT (5 sticks) | 10.00 |
17 +----------------+------------+
18 9 rows in set (0.00 sec)NOTオペレータ
1 mysql> SELECT prod_name, prod_price
2 -> FROM products
3 -> WHERE vend_id NOT IN (1002, 1003);
4 +--------------+------------+
5 | prod_name | prod_price |
6 +--------------+------------+
7 | .5 ton anvil | 5.99 |
8 | 1 ton anvil | 9.99 |
9 | 2 ton anvil | 14.99 |
10 | JetPack 1000 | 35.00 |
11 | JetPack 2000 | 55.00 |
12 +--------------+------------+
13 5 rows in set (0.00 sec)