上图为数据库操作分类：
以下的操作参考（mysql必知必会）
创建数据库 执行脚本建表：
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.sql
mysql> 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)
默认排序方式是升序，若用降序必须用DESC关键字：
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)
