SQL-DQL（3）之排序查询

作者: 小白201808 | 来源:发表于2018-08-29 09:46 被阅读12次

SQL-DQL（3）之排序查询
mysql排序查询
SQL-DQL之条件查询（2）
Oracle【tips2】
MySQL-6:查询语句
Mysql 1.数据库进阶
MySQL 排序
2020-08-03（约束）
MySQL排序查询，分组查询，分页查询，聚合函数
mysql基础-DQL查询(三)

语法

select 查询列表（3）
from 表 （1）
【where 筛选条件】（2）
order by 排序列表【asc/desc】（4）

#（1）（2）（3）（4）代表执行顺序。
#asc:升序(默认）；desc:降序
#order by 子句中可以支持单个字段，多个字段，表达式，别名，函数。
#order by 子句一般放在查询语句的最后，limit子句出除外。

√排序查询练习

#案例一：查询员工信息，要求工资从高到低排序。

mysql> select * from employees order by salary desc;
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name  | last_name   | email    | phone_number       | job_id     | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
|         100 | Steven      | K_ing       | SKING    | 515.123.4567       | AD_PRES    | 24000.00 |           NULL |       NULL |            90 | 1992-04-03 00:00:00 |
|         101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | AD_VP      | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | AD_VP      | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         145 | John        | Russell     | JRUSSEL  | 011.44.1344.429268 | SA_MAN     | 14000.00 |           0.40 |        100 |            80 | 2002-12-23 00:00:00 |
|         146 | Karen       | Partners    | KPARTNER | 011.44.1344.467268 | SA_MAN     | 13500.00 |           0.30 |        100 |            80 | 2002-12-23 00:00:00 |
|         201 | Michael     | Hartstein   | MHARTSTE | 515.123.5555       | MK_MAN     | 13000.00 |           NULL |        100 |            20 | 2016-03-03 00:00:00 |
...
... 

#案例二：查询员工信息，要求工资从低到高排序。
mysql> select * from employees order by salary asc;
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name  | last_name   | email    | phone_number       | job_id     | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
|         132 | TJ          | Olson       | TJOLSON  | 650.124.8234       | ST_CLERK   |  2100.00 |           NULL |        121 |            50 | 2004-02-06 00:00:00 |
|         128 | Steven      | Markle      | SMARKLE  | 650.124.1434       | ST_CLERK   |  2200.00 |           NULL |        120 |            50 | 2004-02-06 00:00:00 |
|         136 | Hazel       | Philtanker  | HPHILTAN | 650.127.1634       | ST_CLERK   |  2200.00 |           NULL |        122 |            50 | 2002-12-23 00:00:00 |
|         127 | James       | Landry      | JLANDRY  | 650.124.1334       | ST_CLERK   |  2400.00 |           NULL |        120 |            50 | 2004-02-06 00:00:00 |
|         135 | Ki          | Gee         | KGEE     | 650.127.1734       | ST_CLERK   |  2400.00 |           NULL |        122 |            50 | 2002-12-23 00:00:00 |
|         119 | Karen       | Colmenares  | KCOLMENA | 515.127.4566       | PU_CLERK   |  2500.00 |           NULL |        114 |            30 | 2000-09-09 00:00:00 |
|         131 | James       | Marlow      | JAMRLOW  | 650.124.7234       | ST_CLERK   |  2500.00 |           NULL |        121 |            50 | 2004-02-06 00:00:00 |
...
...

#案例三：按年薪的高低显示员工的信息和年薪【按别名/表达式排序】
mysql> select *,salary*12*(1+ifnull(commission_pct,0)) as 年薪 from employees order by 年薪 desc;
#或者mysql> select *,salary*12*(1+ifnull(commission_pct,0)) as 年薪 from employees order by salary*12*(1+ifnull(commission_pct,0)) desc;
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+-----------+
| employee_id | first_name  | last_name   | email    | phone_number       | job_id     | salary   | commission_pct | manager_id | department_id | hiredate            | 年薪      |
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+-----------+
|         100 | Steven      | K_ing       | SKING    | 515.123.4567       | AD_PRES    | 24000.00 |           NULL |       NULL |            90 | 1992-04-03 00:00:00 | 288000.00 |
|         145 | John        | Russell     | JRUSSEL  | 011.44.1344.429268 | SA_MAN     | 14000.00 |           0.40 |        100 |            80 | 2002-12-23 00:00:00 | 235200.00 |
|         146 | Karen       | Partners    | KPARTNER | 011.44.1344.467268 | SA_MAN     | 13500.00 |           0.30 |        100 |            80 | 2002-12-23 00:00:00 | 210600.00 |
|         101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | AD_VP      | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 | 204000.00 |
|         102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | AD_VP      | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 | 204000.00 |
|         147 | Alberto     | Errazuriz   | AERRAZUR | 011.44.1344.429278 | SA_MAN     | 12000.00 |           0.30 |        100 |            80 | 2002-12-23 00:00:00 | 187200.00 |
|         168 | Lisa        | Ozer        | LOZER    | 011.44.1343.929268 | SA_REP     | 11500.00 |           0.25 |        148 |            80 | 2014-03-05 00:00:00 | 172500.00 |
|         148 | Gerald      | Cambrault   | GCAMBRAU | 011.44.1344.619268 | SA_MAN     | 11000.00 |           0.30 |        100 |            80 | 2002-12-23 00:00:00 | 171600.00 |
|         174 | Ellen       | Abel        | EABEL    | 011.44.1644.429267 | SA_REP     | 11000.00 |           0.30 |        149 |            80 | 2014-03-05 00:00:00 | 171600.00 |
|         156 | Janette     | K_ing       | JKING    | 011.44.1345.429268 | SA_REP     | 10000.00 |           0.35 |        146 |            80 | 2014-03-05 00:00:00 | 162000.00 |
...
...

#案例四：按姓名的长度显示员工的姓名和工资。
mysql> select  last_name ,salary from employees order by length(last_name) desc; 
+-------------+----------+
| last_name   | salary   |
+-------------+----------+
| Mikkilineni |  2700.00 |
| Colmenares  |  2500.00 |
| Philtanker  |  2200.00 |
| Livingston  |  8400.00 |
| Pataballa   |  4800.00 |
| Greenberg   | 12000.00 |
| De Haan     | 17000.00 |
| Lorentz     |  4200.00 |
| Sciarra     |  7700.00 |
| Grant       |  7000.00 |
| Grant       |  2600.00 |
| Gietz       |  8300.00 |
| Chen        |  8200.00 |
| Popp        |  6900.00 |
| Bell        |  4000.00 |
| Baer        | 10000.00 |
| Gee         |  2400.00 |
| Seo         |  2700.00 |
| Lee         |  6800.00 |
| Fox         |  9600.00 |
| Fay         |  6000.00 |
...
...
 
#案例五：查询员工信息，要求先按工资升序，再按员工编号降序排列。
 
mysql> select * from employees order by salary asc,employee_id desc;
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name  | last_name   | email    | phone_number       | job_id     | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
|         132 | TJ          | Olson       | TJOLSON  | 650.124.8234       | ST_CLERK   |  2100.00 |           NULL |        121 |            50 | 2004-02-06 00:00:00 |
|         136 | Hazel       | Philtanker  | HPHILTAN | 650.127.1634       | ST_CLERK   |  2200.00 |           NULL |        122 |            50 | 2002-12-23 00:00:00 |
|         128 | Steven      | Markle      | SMARKLE  | 650.124.1434       | ST_CLERK   |  2200.00 |           NULL |        120 |            50 | 2004-02-06 00:00:00 |
|         135 | Ki          | Gee         | KGEE     | 650.127.1734       | ST_CLERK   |  2400.00 |           NULL |        122 |            50 | 2002-12-23 00:00:00 |
|         127 | James       | Landry      | JLANDRY  | 650.124.1334       | ST_CLERK   |  2400.00 |           NULL |        120 |            50 | 2004-02-06 00:00:00 |
|         191 | Randall     | Perkins     | RPERKINS | 650.505.4876       | SH_CLERK   |  2500.00 |           NULL |        122 |            50 | 2014-03-05 00:00:00 |
|         182 | Martha      | Sullivan    | MSULLIVA | 650.507.9878       | SH_CLERK   |  2500.00 |           NULL |        120 |            50 | 2014-03-05 00:00:00 |
|         144 | Peter       | Vargas      | PVARGAS  | 650.121.2004       | ST_CLERK   |  2500.00 |           NULL |        124 |            50 | 2002-12-23 00:00:00 |
...
...

√额外练习

#练习一：查询员工的名字，部门号，年薪，按年薪降序，名字的升序。
mysql> select last_name , department_id ,salary*12*(1+ifnull(commission_pct,0))as 年薪 from employees order by 年薪 desc ,last_name asc;
+-------------+---------------+-----------+
| last_name   | department_id | 年薪      |
+-------------+---------------+-----------+
| K_ing       |            90 | 288000.00 |
| Russell     |            80 | 235200.00 |
| Partners    |            80 | 210600.00 |
| De Haan     |            90 | 204000.00 |
| Kochhar     |            90 | 204000.00 |
| Errazuriz   |            80 | 187200.00 |
| Ozer        |            80 | 172500.00 |
| Abel        |            80 | 171600.00 |
| Cambrault   |            80 | 171600.00 |
| K_ing       |            80 | 162000.00 |
| Vishney     |            80 | 157500.00 |
| Hartstein   |            20 | 156000.00 |
| Tucker      |            80 | 156000.00 |
| Sully       |            80 | 153900.00 |
| Zlotkey     |            80 | 151200.00 |
| McEwen      |            80 | 145800.00 |
| Bloom       |            80 | 144000.00 |
| Greenberg   |           100 | 144000.00 |
| Higgins     |           110 | 144000.00 |
| Bernstein   |            80 | 142500.00 |
| Fox         |            80 | 138240.00 |
| Hall        |            80 | 135000.00 |
...
...

#练习二：查询工资不在8000到17000的员工的名字和工资，按工资的降序排列。
mysql> select last_name, salary from employees where salary not between 8000 and 17000 order by salary desc;
+-------------+----------+
| last_name   | salary   |
+-------------+----------+
| K_ing       | 24000.00 |
| Kaufling    |  7900.00 |
| Urman       |  7800.00 |
| Sciarra     |  7700.00 |
| Cambrault   |  7500.00 |
| Doran       |  7500.00 |
| Smith       |  7400.00 |
| Bates       |  7300.00 |
| Marvins     |  7200.00 |
| Grant       |  7000.00 |
| Sewall      |  7000.00 |
| Tuvault     |  7000.00 |
| Popp        |  6900.00 |
| Lee         |  6800.00 |
| Vollman     |  6500.00 |
...
...


#练习3:查询邮箱中含有e的员工信息，并先按邮箱的字节数降序，再按部门号升序排序。

mysql> select * from employees where email like '%e%' order by length(email) desc,department_id asc;
+-------------+-------------+------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name  | last_name  | email    | phone_number       | job_id     | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+-------------+------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
|         201 | Michael     | Hartstein  | MHARTSTE | 515.123.5555       | MK_MAN     | 13000.00 |           NULL |        100 |            20 | 2016-03-03 00:00:00 |
|         114 | Den         | Raphaely   | DRAPHEAL | 515.127.4561       | PU_MAN     | 11000.00 |           NULL |        100 |            30 | 2000-09-09 00:00:00 |
|         119 | Karen       | Colmenares | KCOLMENA | 515.127.4566       | PU_CLERK   |  2500.00 |           NULL |        114 |            30 | 2000-09-09 00:00:00 |
|         186 | Julia       | Dellinger  | JDELLING | 650.509.3876       | SH_CLERK   |  3400.00 |           NULL |        121 |            50 | 2014-03-05 00:00:00 |
|         191 | Randall     | Perkins    | RPERKINS | 650.505.4876       | SH_CLERK   |  2500.00 |           NULL |        122 |            50 | 2014-03-05 00:00:00 |
|         193 | Britney     | Everett    | BEVERETT | 650.501.2876       | SH_CLERK   |  3900.00 |           NULL |        123 |            50 | 2014-03-05 00:00:00 |
|         198 | Donald      | OConnell   | DOCONNEL | 650.507.9833       | SH_CLERK   |  2600.00 |           NULL |        124 |            50 | 2014-03-05 00:00:00 |
|         107 | Diana       | Lorentz    | DLORENTZ | 590.423.5567       | IT_PROG    |  4200.00 |           NULL |        103 |            60 | 1998-03-03 00:00:00 |
|         162 | Clara       | Vishney    | CVISHNEY | 011.44.1346.129268 | SA_REP     | 10500.00 |           0.25 |        147 |            80 | 2014-03-05 00:00:00 |
|         146 | Karen       | Partners   | KPARTNER | 011.44.1344.467268 | SA_MAN     | 13500.00 |           0.30 |        100 |            80 | 2002-12-23 00:00:00 |
|         147 | Alberto     | Errazuriz  | AERRAZUR | 011.44.1344.429278 | SA_MAN     | 12000.00 |           0.30 |        100 |            80 | 2002-12-23 00:00:00 |
|         149 | Eleni       | Zlotkey    | EZLOTKEY | 011.44.1344.429018 | SA_MAN     | 10500.00 |           0.20 |        100 |            80 | 2002-12-23 00:00:00 |
|         151 | David       | Bernstein  | DBERNSTE | 011.44.1344.345268 | SA_REP     |  9500.00 |           0.25 |        145 |            80 | 2014-03-05 00:00:00 |
...
...

注：这是本人的学习笔记和练习，如果有错误的地方望指出一起讨论，谢谢！

SQL-DQL（3）之排序查询
语法 √排序查询练习 √额外练习注：这是本人的学习笔记和练习，如果有错误的地方望指出一起讨论，谢谢！
mysql排序查询
进阶3：排序查询 /*语法：select 查询列表from 表名【where 筛选条件】order by 排序的...
SQL-DQL之条件查询（2）
1.条件查询： 2.筛选条件的分类一. 按条件表达式筛选简单条件运算符：> , <, = , >= , <= ,...
Oracle【tips2】
1，不可以在group by后面使用子查询2，一般不在子查询排序；但top-n分析问题中，必须对子查询排序3，一...
MySQL-6:查询语句
1、DQL：查询语句： 1.排序查询 2.聚合函数 3.分组查询 4.分页查询 2、约束3、多表之间的关系4、范式...
Mysql 1.数据库进阶
1.DQL:查询语句排序查询聚合函数分组查询分页查询2.约束3.多表之间的联系4.范式5.数据库的备份与还原排序...
MySQL 排序
排序查询查询所有学生记录，按年龄升序排序查询所有学生记录，按年龄降序排序查询所有雇员，按月薪降序排序，如果月...
2020-08-03（约束）
1，dql：查询语句；排序查询，聚合函数，分组查询，分页查询； a，排序； order by ,asc...
MySQL排序查询，分组查询，分页查询，聚合函数
DQL:查询语句排序查询语法：order by 字句order by 排序字段1 排序方式1，排序字段2 排序...
mysql基础-DQL查询(三)
DQL查询分为：1.排序查询。2.聚合查询。3分组查询。4.分页查询 where 和having 的区别： 1.w...