mysql学习--sql基本使用 上图为数据库操作分类： 以下的操作参考（mysql必知必会） 创建数据库 执行脚本建表： mysql create database mytest;query ok, 1 row affected (0.07 sec)mysql show databases;+--------------------+| database |+----------------
mysql学习--sql基本使用
上图为数据库操作分类：
以下的操作参考（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          |