網(wǎng)站建設(shè)推廣選stso88效果好91關(guān)鍵詞
文章目錄
- 1 排序數(shù)據(jù)
- 1.1 排序規(guī)則
- 1.2 單列排序
- 1.3 多列排序
- 排序演示代碼
- 2 分頁(yè)
- 2.1 背景
- 2.2 實(shí)現(xiàn)規(guī)則
- 2.3 拓展
- 分頁(yè)演示代碼
- 3 課后練習(xí)
1 排序數(shù)據(jù)
1.1 排序規(guī)則
使用 ORDER BY 子句排序
- ASC(ascend): 升序
- DESC(descend):降序
ORDER BY 子句在SELECT語(yǔ)句的結(jié)尾(默認(rèn)升序)。
1.2 單列排序
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;
/*部分輸出:
+-------------+------------+---------------+------------+
| last_name | job_id | department_id | hire_date |
+-------------+------------+---------------+------------+
| King | AD_PRES | 90 | 1987-06-17 |
| Whalen | AD_ASST | 10 | 1987-09-17 |
| Kochhar | AD_VP | 90 | 1989-09-21 |
| Hunold | IT_PROG | 60 | 1990-01-03 |
| Ernst | IT_PROG | 60 | 1991-05-21 |
| De Haan | AD_VP | 90 | 1993-01-13 |
*/SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;
/*部分輸出:
+-------------+------------+---------------+------------+
| last_name | job_id | department_id | hire_date |
+-------------+------------+---------------+------------+
| Banda | SA_REP | 80 | 2000-04-21 |
| Kumar | SA_REP | 80 | 2000-04-21 |
| Ande | SA_REP | 80 | 2000-03-24 |
| Markle | ST_CLERK | 50 | 2000-03-08 |
| Lee | SA_REP | 80 | 2000-02-23 |
| Philtanker | ST_CLERK | 50 | 2000-02-06 |
*/
SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;
/*部分輸出:
+-------------+-------------+-----------+
| employee_id | last_name | annsal |
+-------------+-------------+-----------+
| 132 | Olson | 25200.00 |
| 128 | Markle | 26400.00 |
| 136 | Philtanker | 26400.00 |
| 127 | Landry | 28800.00 |
| 135 | Gee | 28800.00 |
| 119 | Colmenares | 30000.00 |
*/
1.3 多列排序
-
可以使用不在SELECT列表中的列排序。
-
在對(duì)多列進(jìn)行排序的時(shí)候,首先排序的第一列必須有相同的列值,才會(huì)對(duì)第二列進(jìn)行排序。如果第一列數(shù)據(jù)中所有值都是唯一的,將不再對(duì)第二列進(jìn)行排序。
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
/*部分輸出
+-------------+---------------+----------+
| last_name | department_id | salary |
+-------------+---------------+----------+
| Grant | NULL | 7000.00 |
| Whalen | 10 | 4400.00 |
| Hartstein | 20 | 13000.00 |
| Fay | 20 | 6000.00 |
| Raphaely | 30 | 11000.00 |
| Khoo | 30 | 3100.00 |
| Baida | 30 | 2900.00 |
| Tobias | 30 | 2800.00 |
*/
排序演示代碼
#1. 排序# 如果沒(méi)有使用排序操作,默認(rèn)情況下查詢返回的數(shù)據(jù)是按照添加數(shù)據(jù)的順序顯示的。
SELECT * FROM employees;# 1.1 基本使用
# 使用 ORDER BY 對(duì)查詢到的數(shù)據(jù)進(jìn)行排序操作。
# 升序:ASC (ascend)
# 降序:DESC (descend)# 練習(xí):按照salary從高到低的順序顯示員工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;
/*部分輸出:
+-------------+-------------+----------+
| employee_id | last_name | salary |
+-------------+-------------+----------+
| 100 | King | 24000.00 |
| 101 | Kochhar | 17000.00 |
| 102 | De Haan | 17000.00 |
| 145 | Russell | 14000.00 |
| 146 | Partners | 13500.00 |
*/# 練習(xí):按照salary從低到高的順序顯示員工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary ASC;
/*部分輸出:
+-------------+-------------+----------+
| employee_id | last_name | salary |
+-------------+-------------+----------+
| 132 | Olson | 2100.00 |
| 128 | Markle | 2200.00 |
| 136 | Philtanker | 2200.00 |
| 127 | Landry | 2400.00 |
| 135 | Gee | 2400.00 |
| 119 | Colmenares | 2500.00 |
| 131 | Marlow | 2500.00 |
*/# 如果在ORDER BY 后沒(méi)有顯式指名排序的方式的話,則默認(rèn)按照升序排列。
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary;
/*部分輸出
+-------------+-------------+----------+
| employee_id | last_name | salary |
+-------------+-------------+----------+
| 132 | Olson | 2100.00 |
| 128 | Markle | 2200.00 |
| 136 | Philtanker | 2200.00 |
| 127 | Landry | 2400.00 |
| 135 | Gee | 2400.00 |
| 119 | Colmenares | 2500.00 |
| 131 | Marlow | 2500.00 |
*/#2. 我們可以使用列的別名,進(jìn)行排序
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;
/*部分輸出
+-------------+----------+------------+
| employee_id | salary | annual_sal |
+-------------+----------+------------+
| 132 | 2100.00 | 25200.00 |
| 128 | 2200.00 | 26400.00 |
| 136 | 2200.00 | 26400.00 |
| 127 | 2400.00 | 28800.00 |
| 135 | 2400.00 | 28800.00 |
*/#列的別名只能在 ORDER BY 中使用,不能在WHERE中使用。
#如下操作報(bào)錯(cuò)!
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
WHERE annual_sal > 81600;#3. 強(qiáng)調(diào)格式:WHERE 需要聲明在FROM后,ORDER BY之前。
#先用where過(guò)濾,再用ORDER BY排序
SELECT employee_id,salary
FROM employees
WHERE department_id IN (50,60,70)
ORDER BY department_id DESC;
/*部分輸出:
+-------------+----------+
| employee_id | salary |
+-------------+----------+
| 204 | 10000.00 |
| 103 | 9000.00 |
| 104 | 6000.00 |
| 105 | 4800.00 |
| 106 | 4800.00 |
| 107 | 4200.00 |
*/#4. 二級(jí)排序
#1.可以使用不在SELECT列表中的列排序。
#2.在對(duì)多列進(jìn)行排序的時(shí)候,首先排序的第一列必須有相同的列值,才會(huì)對(duì)第二列
#3.進(jìn)行排序。如果第一列數(shù)據(jù)中所有值都是唯一的,將不再對(duì)第二列進(jìn)行排序。#練習(xí):顯示員工信息,先按照department_id的降序排列,
#再salary的升序排列
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;
/*部分輸出:
+-------------+----------+---------------+
| employee_id | salary | department_id |
+-------------+----------+---------------+
| 206 | 8300.00 | 110 |
| 205 | 12000.00 | 110 |
| 113 | 6900.00 | 100 |
| 111 | 7700.00 | 100 |
| 112 | 7800.00 | 100 |
| 110 | 8200.00 | 100 |
| 109 | 9000.00 | 100 |
*/
2 分頁(yè)
2.1 背景
- 背景1:查詢返回的記錄太多了,查看起來(lái)很不方便,怎么樣能夠?qū)崿F(xiàn)分頁(yè)查詢呢?
- 背景2:表里有 4 條數(shù)據(jù),我們只想要顯示第 2、3 條數(shù)據(jù)怎么辦呢?
2.2 實(shí)現(xiàn)規(guī)則
分頁(yè)原理
所謂分頁(yè)顯示,就是將數(shù)據(jù)庫(kù)中的結(jié)果集,一段一段顯示出來(lái)需要的條件。
MySQL中使用 LIMIT
實(shí)現(xiàn)分頁(yè)
格式:
LIMIT 位置偏移量, 行數(shù)
第一個(gè)“位置偏移量”參數(shù)指示MySQL從哪一行開(kāi)始顯示,是一個(gè)可選參數(shù),如果不指定“位置偏移量”,將會(huì)從表中的第一條記錄開(kāi)始(第一條記錄的位置偏移量是0,第二條記錄的位置偏移量是1,以此類推);第二個(gè)參數(shù)“行數(shù)”指示返回的記錄條數(shù)。
舉例
–前10條記錄:
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
–第11至20條記錄:
SELECT * FROM 表名 LIMIT 10,10;
–第21至30條記錄:
SELECT * FROM 表名 LIMIT 20,10;
MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”,意思是獲取從
第5條記錄開(kāi)始后面的3條記錄,和“LIMIT4,3;”返回的結(jié)果相同。
分頁(yè)顯式公式:(當(dāng)前頁(yè)數(shù)-1)*每頁(yè)條數(shù),每頁(yè)條數(shù):
SELECT * FROM table
LIMIT(PageNo - 1)*PageSize,PageSize;
- 注意:LIMIT 子句必須放在整個(gè)SELECT語(yǔ)句的最后!
- 使用 LIMIT 的好處:
??約束返回結(jié)果的數(shù)量可以 減少數(shù)據(jù)表的網(wǎng)絡(luò)傳輸量 ,也可以 提升查詢效率 。如果我們知道返回結(jié)果只有1 條,就可以使用 LIMIT 1 ,告訴 SELECT 語(yǔ)句只需要返回一條記錄即可。這樣的好處就是 SELECT 不需要掃描完整的表,只需要檢索到一條符合條件的記錄即可返回。
2.3 拓展
在不同的 DBMS 中使用的關(guān)鍵字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 關(guān)鍵字,而且需要放到 SELECT 語(yǔ)句的最后面。
- 如果是 SQL Server 和 Access,需要使用 TOP 關(guān)鍵字,比如:
SELECT TOP 5 name, hp_max FROM heros
ORDER BY hp_max DESC
- 如果是 DB2,使用 FETCH FIRST 5 ROWS ONLY 這樣的關(guān)鍵字:
SELECT name, hp_max
FROM heros
ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY
- 如果是 Oracle,你需要基于 ROWNUM 來(lái)統(tǒng)計(jì)行數(shù):
SELECT rownum,last_name,salary
FROM employees
WHERE rownum < 5 ORDER BY salary DESC;
#需要說(shuō)明的是,這條語(yǔ)句是先取出來(lái)前 5 條數(shù)據(jù)行,然后再按照 hp_max
#從高到低的順序進(jìn)行排序。但這樣產(chǎn)生的結(jié)果和上述方法的并不一樣。
#在后面講到子查詢,你可以使用SELECT last_name,salary
FROM employees
ORDER BY salary DESC)
WHERE rownum < 10;
#得到與上述方法一致的結(jié)果。
分頁(yè)演示代碼
#2. 分頁(yè)
#2.1 mysql使用limit實(shí)現(xiàn)數(shù)據(jù)的分頁(yè)顯示# 需求1:每頁(yè)顯示20條記錄,此時(shí)顯示第1頁(yè)
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;#0是偏移量,20是顯示的條目數(shù)
/*輸出:
+-------------+------------+
| employee_id | last_name |
+-------------+------------+
| 100 | King |
| 101 | Kochhar |
| 102 | De Haan |
| 103 | Hunold |
| 104 | Ernst |
| 105 | Austin |
| 106 | Pataballa |
| 107 | Lorentz |
| 108 | Greenberg |
| 109 | Faviet |
| 110 | Chen |
| 111 | Sciarra |
| 112 | Urman |
| 113 | Popp |
| 114 | Raphaely |
| 115 | Khoo |
| 116 | Baida |
| 117 | Tobias |
| 118 | Himuro |
| 119 | Colmenares |
+-------------+------------+
20 rows in set (0.00 sec)
*/# 需求2:每頁(yè)顯示20條記錄,此時(shí)顯示第2頁(yè)
SELECT employee_id,last_name
FROM employees
LIMIT 20,20;#偏移量是20,顯示條目數(shù)是20
/*輸出
+-------------+-------------+
| employee_id | last_name |
+-------------+-------------+
| 120 | Weiss |
| 121 | Fripp |
| 122 | Kaufling |
| 123 | Vollman |
| 124 | Mourgos |
| 125 | Nayer |
| 126 | Mikkilineni |
| 127 | Landry |
| 128 | Markle |
| 129 | Bissot |
| 130 | Atkinson |
| 131 | Marlow |
| 132 | Olson |
| 133 | Mallin |
| 134 | Rogers |
| 135 | Gee |
| 136 | Philtanker |
| 137 | Ladwig |
| 138 | Stiles |
| 139 | Seo |
+-------------+-------------+
20 rows in set (0.00 sec)
*/# 需求3:每頁(yè)顯示20條記錄,此時(shí)顯示第3頁(yè)
SELECT employee_id,last_name
FROM employees
LIMIT 40,20;#需求:每頁(yè)顯示pageSize條記錄,此時(shí)顯示第pageNo頁(yè):
#公式:LIMIT (pageNo-1) * pageSize,pageSize;#2.2 WHERE ... ORDER BY ...LIMIT 聲明順序如下:
# LIMIT的格式: 嚴(yán)格來(lái)說(shuō):LIMIT 位置偏移量,條目數(shù)
# 結(jié)構(gòu)"LIMIT 0,條目數(shù)" 等價(jià)于 "LIMIT 條目數(shù)"
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
#limit 0,10;
LIMIT 10;
/*輸出:
+-------------+-----------+----------+
| employee_id | last_name | salary |
+-------------+-----------+----------+
| 100 | King | 24000.00 |
| 101 | Kochhar | 17000.00 |
| 102 | De Haan | 17000.00 |
| 145 | Russell | 14000.00 |
| 146 | Partners | 13500.00 |
| 201 | Hartstein | 13000.00 |
| 108 | Greenberg | 12000.00 |
| 147 | Errazuriz | 12000.00 |
| 205 | Higgins | 12000.00 |
| 168 | Ozer | 11500.00 |
+-------------+-----------+----------+
10 rows in set (0.00 sec)
*/#練習(xí):表里有107條數(shù)據(jù),我們只想要顯示第 32、33 條數(shù)據(jù)怎么辦呢?
SELECT employee_id,last_name
FROM employees
LIMIT 31,2;
/*輸出:
+-------------+-----------+
| employee_id | last_name |
+-------------+-----------+
| 131 | Marlow |
| 132 | Olson |
+-------------+-----------+
2 rows in set (0.00 sec)
*/#2.3 MySQL8.0新特性:LIMIT ... OFFSET ...#練習(xí):表里有107條數(shù)據(jù),我們只想要顯示第 32、33 條數(shù)據(jù)怎么辦呢?
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;#偏移量是31,顯示條目為2條
/*輸出
+-------------+-----------+
| employee_id | last_name |
+-------------+-----------+
| 131 | Marlow |
| 132 | Olson |
+-------------+-----------+
*/#練習(xí):查詢員工表中工資最高的員工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
#limit 0,1
LIMIT 1;
/*輸出
+-------------+-----------+----------+
| employee_id | last_name | salary |
+-------------+-----------+----------+
| 100 | King | 24000.00 |
+-------------+-----------+----------+
*/#2.4 LIMIT 可以使用在MySQL、PGSQL、MariaDB、SQLite 等數(shù)據(jù)庫(kù)中使用,表示分頁(yè)。
# 不能使用在SQL Server、DB2、Oracle!
3 課后練習(xí)
#第05章_排序與分頁(yè)的課后練習(xí)#1. 查詢員工的姓名和部門號(hào)和年薪,按年薪降序,按姓名升序顯示 SELECT last_name,department_id,salary * 12 annual_salary
FROM employees
ORDER BY annual_salary DESC,last_name ASC;
/*部分輸出
+-------------+---------------+---------------+
| last_name | department_id | annual_salary |
+-------------+---------------+---------------+
| King | 90 | 288000.00 |
| De Haan | 90 | 204000.00 |
| Kochhar | 90 | 204000.00 |
| Russell | 80 | 168000.00 |
| Partners | 80 | 162000.00 |
| Hartstein | 20 | 156000.00 |
| Errazuriz | 80 | 144000.00 |
*/#2. 選擇工資不在 8000 到 17000 的員工的姓名和工資,按工資降序,顯示第21到40位置的數(shù)據(jù) SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;
/*輸出
+-----------+---------+
| last_name | salary |
+-----------+---------+
| Ernst | 6000.00 |
| Fay | 6000.00 |
| Mourgos | 5800.00 |
| Austin | 4800.00 |
| Pataballa | 4800.00 |
| Whalen | 4400.00 |
| Lorentz | 4200.00 |
| Sarchand | 4200.00 |
| Bull | 4100.00 |
| Bell | 4000.00 |
| Everett | 3900.00 |
| Chung | 3800.00 |
| Dilly | 3600.00 |
| Ladwig | 3600.00 |
| Rajs | 3500.00 |
| Dellinger | 3400.00 |
| Mallin | 3300.00 |
| Bissot | 3300.00 |
| Taylor | 3200.00 |
| Stiles | 3200.00 |
+-----------+---------+
*/#3. 查詢郵箱中包含 e 的員工信息,并先按郵箱的字節(jié)數(shù)降序,
# 再按部門號(hào)升序SELECT employee_id,last_name,email,department_id
FROM employees
#where email like '%e%'
WHERE email REGEXP '[e]'
ORDER BY LENGTH(email) DESC,department_id;
/*部分輸出
+-------------+------------+----------+---------------+
| employee_id | last_name | email | department_id |
+-------------+------------+----------+---------------+
| 201 | Hartstein | MHARTSTE | 20 |
| 114 | Raphaely | DRAPHEAL | 30 |
| 119 | Colmenares | KCOLMENA | 30 |
| 186 | Dellinger | JDELLING | 50 |
| 191 | Perkins | RPERKINS | 50 |
| 193 | Everett | BEVERETT | 50 |
| 198 | OConnell | DOCONNEL | 50 |
*/