上图为数据库操作分类：

以下的操作参考（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)

mysql必知必会前八章如上。