[Mysql 查询语句]——对查询结果进一步的操作

distinct 不显示重复的查询结果

(1) 对于表中一些没有唯一性约束的字段，可能存在重复的值，这时可以使用distinct来消除那些查询结果中的重复值

select  cust_id  from orders;
+---------+
| cust_id |
+---------+
|   10001 |
|   10001 |
|   10003 |
|   10004 |
|   10005 |
+---------+

select distinct cust_id from orders;
+---------+
| cust_id |
+---------+
|   10001 |
|   10003 |
|   10004 |
|   10005 |
+---------+

 

order by 查询结果排序

(1) order by 属性名 [desc|asc]
      这个语句的意义是:对于查询结果，指定根据这个属性来进行升序|降序的排列
(2) 排序时对于字段中的NULL值：
     asc升序时，NULL在最前面
     desc降序时，NULL在最后面

select distinct cust_id from orders order by cust_id desc;
+---------+
| cust_id |
+---------+
|   10005 |
|   10004 |
|   10003 |
|   10001 |
+---------+

 

limit 限制查询结果数目

(1) limit n:     表示显示前n条记录
(2) limit n,m: 表示从第n位置的记录开始，往后显示m条(注意:第一条记录的位置是0,往后依次类推)

select * from orders where order_num>2006;
+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20005 | 2005-09-01 00:00:00 |   10001 |
|     20006 | 2005-09-12 00:00:00 |   10003 |
|     20007 | 2005-09-30 00:00:00 |   10004 |
|     20008 | 2005-10-03 00:00:00 |   10005 |
|     20009 | 2005-10-08 00:00:00 |   10001 |
+-----------+---------------------+---------+

select * from orders where order_num>2006 limit 2;
+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20005 | 2005-09-01 00:00:00 |   10001 |
|     20006 | 2005-09-12 00:00:00 |   10003 |
+-----------+---------------------+---------+
select * from orders where order_num>2006 limit 0,2;
+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20005 | 2005-09-01 00:00:00 |   10001 |
|     20006 | 2005-09-12 00:00:00 |   10003 |
+-----------+---------------------+---------+
select * from orders where order_num>2006 limit 2,2;
+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20007 | 2005-09-30 00:00:00 |   10004 |
|     20008 | 2005-10-03 00:00:00 |   10005 |
+-----------+---------------------+---------+

 

union合并查询结果

(1) union all: 把所有查询结果合并

(2) union     : 把所有查询结果合并且去除重复行

select vend_id from vendors ;
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1004 |
|    1005 |
|    1006 |
+---------+

select vend_id from products;
+---------+
| vend_id |
+---------+
|    1001 |
|    1001 |
|    1001 |
|    1002 |
|    1002 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1005 |
|    1005 |
+---------+

select vend_id from vendors union all select vend_id from products;
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1004 |
|    1005 |
|    1006 |
|    1001 |
|    1001 |
|    1001 |
|    1002 |
|    1002 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1005 |
|    1005 |
+---------+

select vend_id from vendors union select vend_id from products;
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1004 |
|    1005 |
|    1006 |
+---------+