[Mysql 查詢語句]——對查詢結果進一步的操作

Jelly_lyj發表於2017-03-18
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 |
+---------+

 

相關文章