? 
上图为数据库操作分类：
?
?
创建数据库 运行脚本建表：
mysql> create database mytest;Query OK, 1 row affected (0.07 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || mytest || performance_schema |+--------------------+4 rows in set (0.00 sec)mysql> use myteERROR 1049 (42000): Unknown database ‘myte‘mysql> use mytest;Database changedmysql> show tables;Empty set (0.00 sec)mysql> source /home/huangcd/mysql_scripts/create.sqlmysql> source /home/huangcd/mysql_scripts/populate.sql?
mysql> show tables;+------------------+| Tables_in_mytest |+------------------+| customers || orderitems || orders || productnotes || products || vendors |+------------------+6 rows in set (0.02 sec)删除反复行：
mysql> select vend_id from products;
+---------+
| vend_id |
+---------+
|??? 1001 |
|??? 1001 |
|??? 1001 |
|??? 1002 |
|??? 1002 |
|??? 1003 |
|??? 1003 |
|??? 1003 |
|??? 1003 |
|??? 1003 |
|??? 1003 |
|??? 1003 |
|??? 1005 |
|??? 1005 |
+---------+
14 rows in set (0.00 sec)
mysql> select distinct vend_id from products;
+---------+
| vend_id |
+---------+
|??? 1001 |
|??? 1002 |
|??? 1003 |
|??? 1005 |
+---------+
4 rows in set (0.04 sec)
?
?返回结果中不多于5行：
mysql> select prod_name from products
??? -> limit 5;
+--------------+
| prod_name??? |
+--------------+
| .5 ton anvil |
| 1 ton anvil? |
| 2 ton anvil? |
| Detonator??? |
| Bird seed??? |
+--------------+
5 rows in set (0.00 sec)
?从第5行開始的5行：
mysql> select prod_name
??? -> from products
??? -> limit 5,5;
+--------------+
| prod_name??? |
+--------------+
| Carrots????? |
| Fuses??????? |
| JetPack 1000 |
| JetPack 2000 |
| Oil can????? |
+--------------+
5 rows in set (0.00 sec)
order by 子句对一个或者多个列进行输出排序：
mysql> select prod_name
??? -> from products
??? -> order by prod_name;
+----------------+
| prod_name????? |
+----------------+
| .5 ton anvil?? |
| 1 ton anvil??? |
| 2 ton anvil??? |
| Bird seed????? |
| Carrots??????? |
| Detonator????? |
| Fuses????????? |
| JetPack 1000?? |
| JetPack 2000?? |
| Oil can??????? |
| Safe?????????? |
| Sling????????? |
| TNT (1 stick)? |
| TNT (5 sticks) |
+----------------+
首先按price排序，仅仅有price同样时，才依照名称排序：
mysql> select prod_id, prod_price, prod_name
??? -> from products
??? -> order by prod_price, prod_name;
+---------+------------+----------------+
| prod_id | prod_price | prod_name????? |
+---------+------------+----------------+
| FC????? |?????? 2.50 | Carrots??????? |
| TNT1??? |?????? 2.50 | TNT (1 stick)? |
| FU1???? |?????? 3.42 | Fuses????????? |
| SLING?? |?????? 4.49 | Sling????????? |
| ANV01?? |?????? 5.99 | .5 ton anvil?? |
| OL1???? |?????? 8.99 | Oil can??????? |
| ANV02?? |?????? 9.99 | 1 ton anvil??? |
| FB????? |????? 10.00 | Bird seed????? |
| TNT2??? |????? 10.00 | TNT (5 sticks) |
| DTNTR?? |????? 13.00 | Detonator????? |
| ANV03?? |????? 14.99 | 2 ton anvil??? |
| JP1000? |????? 35.00 | JetPack 1000?? |
| SAFE??? |????? 50.00 | Safe?????????? |
| JP2000? |????? 55.00 | JetPack 2000?? |
+---------+------------+----------------+
14 rows in set (0.05 sec)
默认排序方式是升序，若用降序必须用DESCkeyword：
mysql> select prod_id, prod_price, prod_name from products order by prod_price DESC;
+---------+------------+----------------+
| prod_id | prod_price | prod_name????? |
+---------+------------+----------------+
| JP2000? |????? 55.00 | JetPack 2000?? |
| SAFE??? |????? 50.00 | Safe?????????? |
| JP1000? |????? 35.00 | JetPack 1000?? |
| ANV03?? |????? 14.99 | 2 ton anvil??? |
| DTNTR?? |????? 13.00 | Detonator????? |
| TNT2??? |????? 10.00 | TNT (5 sticks) |
| FB????? |????? 10.00 | Bird seed????? |
| ANV02?? |?????? 9.99 | 1 ton anvil??? |
| OL1???? |?????? 8.99 | Oil can??????? |
| ANV01?? |?????? 5.99 | .5 ton anvil?? |
| SLING?? |?????? 4.49 | Sling????????? |
| FU1???? |?????? 3.42 | Fuses????????? |
| FC????? |?????? 2.50 | Carrots??????? |
| TNT1??? |?????? 2.50 | TNT (1 stick)? |
+---------+------------+----------------+
14 rows in set (0.02 sec)
mysql> select prod_id, prod_price, prod_name from products order by prod_price DESC, prod_name;
+---------+------------+----------------+
| prod_id | prod_price | prod_name????? |
+---------+------------+----------------+
| JP2000? |????? 55.00 | JetPack 2000?? |
| SAFE??? |????? 50.00 | Safe?????????? |
| JP1000? |????? 35.00 | JetPack 1000?? |
| ANV03?? |????? 14.99 | 2 ton anvil??? |
| DTNTR?? |????? 13.00 | Detonator????? |
| FB????? |????? 10.00 | Bird seed????? |
| TNT2??? |????? 10.00 | TNT (5 sticks) |
| ANV02?? |?????? 9.99 | 1 ton anvil??? |
| OL1???? |?????? 8.99 | Oil can??????? |
| ANV01?? |?????? 5.99 | .5 ton anvil?? |
| SLING?? |?????? 4.49 | Sling????????? |
| FU1???? |?????? 3.42 | Fuses????????? |
?
?价格最高的一个：
mysql> select prod_price -> from products -> order by prod_price desc -> limit 1;+------------+| prod_price |+------------+| 55.00 |+------------+1 row in set (0.12 sec)?
?
?
mysql> select prod_name, prod_price -> from products -> where prod_price between 5 and 10;+----------------+------------+| prod_name | prod_price |+----------------+------------+| .5 ton anvil | 5.99 || 1 ton anvil | 9.99 || Bird seed | 10.00 || Oil can | 8.99 || TNT (5 sticks) | 10.00 |+----------------+------------+5 rows in set (0.09 sec)?空值检查：
mysql> select prod_name -> from products -> where prod_price is null;Empty set (0.01 sec)mysql> select cust_id -> from customers -> where cust_email is null;+---------+| cust_id |+---------+| 10002 || 10005 |+---------+2 rows in set (0.00 sec)?
?or操作符：
mysql> select prod_name,prod_price from products where vend_id =1002 or vend_id = 1003;+----------------+------------+| prod_name | prod_price |+----------------+------------+| Detonator | 13.00 || Bird seed | 10.00 || Carrots | 2.50 || Fuses | 3.42 || Oil can | 8.99 || Safe | 50.00 || Sling | 4.49 || TNT (1 stick) | 2.50 || TNT (5 sticks) | 10.00 |+----------------+------------+9 rows in set (0.05 sec)?
?in操作符：
mysql> select prod_name, prod_price -> from products -> where vend_id in (1002, 1003) -> order by prod_name;+----------------+------------+| prod_name | prod_price |+----------------+------------+| Bird seed | 10.00 || Carrots | 2.50 || Detonator | 13.00 || Fuses | 3.42 || Oil can | 8.99 || Safe | 50.00 || Sling | 4.49 || TNT (1 stick) | 2.50 || TNT (5 sticks) | 10.00 |+----------------+------------+9 rows in set (0.07 sec)in最大的长处是能够包括其它select子句。
?
not操作符：
mysql> select prod_name, prod_price -> from products -> where vend_id not in (1002, 1003) -> order by prod_name;+--------------+------------+| prod_name | prod_price |+--------------+------------+| .5 ton anvil | 5.99 || 1 ton anvil | 9.99 || 2 ton anvil | 14.99 || JetPack 1000 | 35.00 || JetPack 2000 | 55.00 |+--------------+------------+5 rows in set (0.00 sec)?
?在搜索子句中使用通配符，必须使用like操作符。like指示后跟的搜索模式是通配符而不是直接相等匹配。
?
?
?%通配符：随意字符出现随意次数
mysql> select prod_id, prod_name -> from products -> where prod_name like ‘jet%‘ -> ;+---------+--------------+| prod_id | prod_name |+---------+--------------+| JP1000 | JetPack 1000 || JP2000 | JetPack 2000 |+---------+--------------+2 rows in set (0.05 sec)mysql> select prod_id, prod_name from products where prod_name like ‘%anvil%‘;+---------+--------------+| prod_id | prod_name |+---------+--------------+| ANV01 | .5 ton anvil || ANV02 | 1 ton anvil || ANV03 | 2 ton anvil |+---------+--------------+3 rows in set (0.00 sec)_通配符：匹配随意单个字符
mysql> select prod_id, prod_name from products where prod_name like ‘_ton anvil‘;Empty set (0.00 sec)mysql> select prod_id, prod_name from products where prod_name like ‘_ ton anvil‘;+---------+-------------+| prod_id | prod_name |+---------+-------------+| ANV02 | 1 ton anvil || ANV03 | 2 ton anvil |+---------+-------------+2 rows in set (0.00 sec)?
?
?
