MySQL的单表查询

单表查询

单表查询语法：

select distinct 字段1,字段2... from 表名
                              where 条件
                              group by field
                              having筛选
                              order by

关键字执行的优先级：

• 1.找到表:from
• 2.拿着where指定的约束条件，去文件/表中取出一条条记录
• 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
• 4.执行select（去重）
• 5.将分组的结果进行having过滤
• 6.将结果按条件排序：order by
• 7.限制结果的显示条数

简单查询：

company.employee
    员工id      id                  int
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int

# 创建表
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male',
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);

# 查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| emp_name     | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.09 sec)

#插入记录
#三个部门：教学，销售，运营
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('彭哈哈','male',18,'20170301','教学',7300.33,401,1),
('赵晓明','male',78,'20150302','教学',1000000.31,401,1),
('爱祖国','male',81,'20130305','教学',8300,401,1),
('延阻聚','male',73,'20140701','教学',3500,401,1),
('陈继承','male',28,'20121101','教学',2100,401,1),
('李小荷','female',18,'20110211','教学',9000,401,1),
('赵晓明','male',18,'19000301','教学',30000,401,1),
('高富帅','male',48,'20101111','教学',10000,401,1),

('菟丝子','female',48,'20150311','销售',3000.13,402,2),
('张晓敏','female',38,'20101101','销售',2000.35,402,2),
('冯小刚','female',18,'20110312','销售',1000.37,402,2),
('老小月','female',18,'20160513','销售',3000.29,402,2),
('格格','female',28,'20170127','销售',4000.33,402,2),

('彭消息','male',28,'20160311','运营',10000.13,403,3),
('张国','male',18,'19970312','运营',20000,403,3),
('小路小','female',18,'20130311','运营',19000,403,3),
('罗超','male',18,'20150411','运营',18000,403,3),
('张罗好','female',18,'20140512','运营',17000,403,3);

只查看某张表指定的字段：

select 字段1,字段2,字段3,字段n from 表名；

mysql> select id, emp_name, sex, age from employee;    #查看员工姓名性别年龄
+----+-----------+--------+-----+
| id | emp_name  | sex    | age |
+----+-----------+--------+-----+
| 19 | 彭哈哈    | male   |  18 |
| 20 | 彭哈哈    | male   |  18 |
| 21 | 赵晓明    | male   |  78 |
| 22 | 爱祖国    | male   |  81 |
| 23 | 延阻聚    | male   |  73 |
| 24 | 陈继承    | male   |  28 |
| 25 | 李小荷    | female |  18 |
| 26 | 赵晓明    | male   |  18 |
| 27 | 高富帅    | male   |  48 |
| 28 | 菟丝子    | female |  48 |
| 29 | 张晓敏    | female |  38 |
| 30 | 冯小刚    | female |  18 |
| 31 | 老小月    | female |  18 |
| 32 | 格格      | female |  28 |
| 33 | 彭消息    | male   |  28 |
| 34 | 张国      | male   |  18 |
| 35 | 小路小    | female |  18 |
| 36 | 罗超      | male   |  18 |
| 37 | 张罗好    | female |  18 |
+----+-----------+--------+-----+
19 rows in set (0.00 sec)

查看整张表：

select * from 表名;

mysql> select * from employee;    # 查看整张表
+----+-----------+--------+-----+------------+--------+--------------+------------+--------+-----------+
| id | emp_name  | sex    | age | hire_date  | post   | post_comment | salary     | office | depart_id |
+----+-----------+--------+-----+------------+--------+--------------+------------+--------+-----------+
| 19 | 彭哈哈    | male   |  18 | 2017-03-01 | 教学   | NULL         |    7300.33 |    401 |         1 |
| 20 | 彭哈哈    | male   |  18 | 2017-03-01 | 教学   | NULL         |    7300.33 |    401 |         1 |
| 21 | 赵晓明    | male   |  78 | 2015-03-02 | 教学   | NULL         | 1000000.31 |    401 |         1 |
| 22 | 爱祖国    | male   |  81 | 2013-03-05 | 教学   | NULL         |    8300.00 |    401 |         1 |
| 23 | 延阻聚    | male   |  73 | 2014-07-01 | 教学   | NULL         |    3500.00 |    401 |         1 |
| 24 | 陈继承    | male   |  28 | 2012-11-01 | 教学   | NULL         |    2100.00 |    401 |         1 |
| 25 | 李小荷    | female |  18 | 2011-02-11 | 教学   | NULL         |    9000.00 |    401 |         1 |
| 26 | 赵晓明    | male   |  18 | 1900-03-01 | 教学   | NULL         |   30000.00 |    401 |         1 |
| 27 | 高富帅    | male   |  48 | 2010-11-11 | 教学   | NULL         |   10000.00 |    401 |         1 |
| 28 | 菟丝子    | female |  48 | 2015-03-11 | 销售   | NULL         |    3000.13 |    402 |         2 |
| 29 | 张晓敏    | female |  38 | 2010-11-01 | 销售   | NULL         |    2000.35 |    402 |         2 |
| 30 | 冯小刚    | female |  18 | 2011-03-12 | 销售   | NULL         |    1000.37 |    402 |         2 |
| 31 | 老小月    | female |  18 | 2016-05-13 | 销售   | NULL         |    3000.29 |    402 |         2 |
| 32 | 格格      | female |  28 | 2017-01-27 | 销售   | NULL         |    4000.33 |    402 |         2 |
| 33 | 彭消息    | male   |  28 | 2016-03-11 | 运营   | NULL         |   10000.13 |    403 |         3 |
| 34 | 张国      | male   |  18 | 1997-03-12 | 运营   | NULL         |   20000.00 |    403 |         3 |
| 35 | 小路小    | female |  18 | 2013-03-11 | 运营   | NULL         |   19000.00 |    403 |         3 |
| 36 | 罗超      | male   |  18 | 2015-04-11 | 运营   | NULL         |   18000.00 |    403 |         3 |
| 37 | 张罗好    | female |  18 | 2014-05-12 | 运营   | NULL         |   17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+--------+--------------+------------+--------+-----------+
19 rows in set (0.00 sec)

避免重复distinct：

select distinct post from 表名;

去重功能将重复的去掉只留一个显示

mysql> select distinct post from employee;    #查看部门
+--------+
| post   |
+--------+
| 教学   |
| 销售   |
| 运营   |
+--------+

四则运算查询：

需求：查看所有员工的年薪，薪资乘于12就等于年薪

mysql> select emp_name, salary * 12 FROM employee;    #工资乘于12
+-----------+-------------+
| emp_name  | salary * 12 |
+-----------+-------------+
| 彭哈哈    |    87603.96 |
| 彭哈哈    |    87603.96 |
| 赵晓明    | 12000003.72 |
| 爱祖国    |    99600.00 |
| 延阻聚    |    42000.00 |
| 陈继承    |    25200.00 |
| 李小荷    |   108000.00 |
| 赵晓明    |   360000.00 |
| 高富帅    |   120000.00 |
| 菟丝子    |    36001.56 |
| 张晓敏    |    24004.20 |
| 冯小刚    |    12004.44 |
| 老小月    |    36003.48 |
| 格格      |    48003.96 |
| 彭消息    |   120001.56 |
| 张国      |   240000.00 |
| 小路小    |   228000.00 |
| 罗超      |   216000.00 |
| 张罗好    |   204000.00 |
+-----------+-------------+
19 rows in set (0.00 sec)

需求：查看所有员工的年薪，薪资乘于12就等于年薪并且把字段salary改成Annual_salary

mysql> SELECT emp_name, salary*12 AS Annual_salary FROM employee;
+-----------+---------------+
| emp_name  | Annual_salary |
+-----------+---------------+
| 彭哈哈    |      87603.96 |
| 彭哈哈    |      87603.96 |
| 赵晓明    |   12000003.72 |
| 爱祖国    |      99600.00 |
| 延阻聚    |      42000.00 |
| 陈继承    |      25200.00 |
| 李小荷    |     108000.00 |
| 赵晓明    |     360000.00 |
| 高富帅    |     120000.00 |
| 菟丝子    |      36001.56 |
| 张晓敏    |      24004.20 |
| 冯小刚    |      12004.44 |
| 老小月    |      36003.48 |
| 格格      |      48003.96 |
| 彭消息    |     120001.56 |
| 张国      |     240000.00 |
| 小路小    |     228000.00 |
| 罗超      |     216000.00 |
| 张罗好    |     204000.00 |
+-----------+---------------+
19 rows in set (0.06 sec)

定义格式显示：

concat()函数用于连接字符串

select concat('字符串1',字段,'字符串',字段) from 表名;

mysql> select concat('姓名：',emp_name,'年薪',salary*12) from employee;
+-------------------------------------------------+
| concat('姓名：',emp_name,'年薪',salary*12)      |
+-------------------------------------------------+
| 姓名：彭哈哈年薪87603.96                        |
| 姓名：彭哈哈年薪87603.96                        |
| 姓名：赵晓明年薪12000003.72                     |
| 姓名：爱祖国年薪99600.00                        |
| 姓名：延阻聚年薪42000.00                        |
| 姓名：陈继承年薪25200.00                        |
| 姓名：李小荷年薪108000.00                       |
| 姓名：赵晓明年薪360000.00                       |
| 姓名：高富帅年薪120000.00                       |
| 姓名：菟丝子年薪36001.56                        |
| 姓名：张晓敏年薪24004.20                        |
| 姓名：冯小刚年薪12004.44                        |
| 姓名：老小月年薪36003.48                        |
| 姓名：格格年薪48003.96                          |
| 姓名：彭消息年薪120001.56                       |
| 姓名：张国年薪240000.00                         |
| 姓名：小路小年薪228000.00                       |
| 姓名：罗超年薪216000.00                         |
| 姓名：张罗好年薪204000.00                       |
+-------------------------------------------------+
19 rows in set (0.02 sec)

concat_ws()第一个参数为分隔符

需求：查看每个员工的年薪，格式为：姓名：年薪

mysql> select concat_ws(':', emp_name, salary * 12) AS Annual_salary from employee;    #第一个数为分隔符
+-----------------------+
| Annual_salary         |
+-----------------------+
| 彭哈哈:87603.96       |
| 彭哈哈:87603.96       |
| 赵晓明:12000003.72    |
| 爱祖国:99600.00       |
| 延阻聚:42000.00       |
| 陈继承:25200.00       |
| 李小荷:108000.00      |
| 赵晓明:360000.00      |
| 高富帅:120000.00      |
| 菟丝子:36001.56       |
| 张晓敏:24004.20       |
| 冯小刚:12004.44       |
| 老小月:36003.48       |
| 格格:48003.96         |
| 彭消息:120001.56      |
| 张国:240000.00        |
| 小路小:228000.00      |
| 罗超:216000.00        |
| 张罗好:204000.00      |
+-----------------------+
19 rows in set (0.00 sec)

 where约束：

where字句中可以使用：

• 1. 比较运算符：> < >= <= <> !=
• 2. between 80 and 100 值在80到100之间
• 3. in(80,90,100) 值是80或90或100
• 4. like 'e%'
•     通配符可以是%或_，
•     %表示任意多字符
•     _表示一个字符 
• 5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not    #与或非

条件查询：

select 字段 from 表名

　　 where 字段 = ...;

需求：查询员工表工资等于20000的

select emp_name , salary from employee where salary = 20000;

mysql> select emp_name , salary from employee where salary = 20000;
+----------+----------+
| emp_name | salary   |
+----------+----------+
| 张国     | 20000.00 |
+----------+----------+
1 row in set (0.00 sec)

多条件查询：

需求：查询教学部工资大于10000的

SELECT emp_name,salary FROM employee

WHERE post='teacher' AND salary>10000;

mysql>     SELECT emp_name,salary FROM employee
    ->         WHERE post='教学' AND salary>10000;
+-----------+------------+
| emp_name  | salary     |
+-----------+------------+
| 赵晓明    | 1000000.31 |
| 赵晓明    |   30000.00 |
+-----------+------------+
2 rows in set (0.00 sec)

关键字between and查询：
需求：查询员工薪资15000至20000的人

ysql> select emp_name,salary from employee
    -> where salary between 15000 and 20000;
+-----------+----------+
| emp_name  | salary   |
+-----------+----------+
| 张国      | 20000.00 |
| 小路小    | 19000.00 |
| 罗超      | 18000.00 |
| 张罗好    | 17000.00 |
+-----------+----------+
4 rows in set (0.00 sec)

关键字is null(判断某个字段是否为null不能用=号，需要用is)

 mysql>SELECT emp_name,post_comment FROM employee 
 ->       WHERE post_comment IS NULL;
+-----------+--------------+
| emp_name  | post_comment |
+-----------+--------------+
| 彭哈哈    | NULL         |
| 彭哈哈    | NULL         |
| 赵晓明    | NULL         |
| 爱祖国    | NULL         |
| 延阻聚    | NULL         |
| 陈继承    | NULL         |
| 李小荷    | NULL         |
| 赵晓明    | NULL         |
| 高富帅    | NULL         |
| 菟丝子    | NULL         |
| 张晓敏    | NULL         |
| 冯小刚    | NULL         |
| 老小月    | NULL         |
| 格格      | NULL         |
| 彭消息    | NULL         |
| 张国      | NULL         |
| 小路小    | NULL         |
| 罗超      | NULL         |
| 张罗好    | NULL         |
+-----------+--------------+
19 rows in set (0.00 sec)

关键字in集合查询：

#查询工资为3000或3500，4000或9000

mysql>SELECT emp_name,salary FROM employee
    ->WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000;
+-----------+---------+
| emp_name  | salary  |
+-----------+---------+
| 延阻聚    | 3500.00 |
| 李小荷    | 9000.00 |
+-----------+---------+
2 rows in set (0.00 sec)



mysql>SELECT emp_name,salary FROM employee
    ->WHERE salary IN (3000,3500,4000,9000) ;
+-----------+---------+
| emp_name  | salary  |
+-----------+---------+
| 延阻聚    | 3500.00 |
| 李小荷    | 9000.00 |
+-----------+---------+
2 rows in set (0.03 sec)


mysql>     SELECT emp_name,salary FROM employee

    ->         WHERE salary NOT IN (3000,3500,4000,9000) ;    #not不寻找某某或。。。

+-----------+------------+

| emp_name  | salary     |

+-----------+------------+

| 彭哈哈    |    7300.33 |

| 彭哈哈    |    7300.33 |

| 赵晓明    | 1000000.31 |

| 爱祖国    |    8300.00 |

| 陈继承    |    2100.00 |

| 赵晓明    |   30000.00 |

| 高富帅    |   10000.00 |

| 菟丝子    |    3000.13 |

| 张晓敏    |    2000.35 |

| 冯小刚    |    1000.37 |

| 老小月    |    3000.29 |

| 格格      |    4000.33 |

| 彭消息    |   10000.13 |

| 张国      |   20000.00 |

| 小路小    |   19000.00 |

| 罗超      |   18000.00 |

| 张罗好    |   17000.00 |

+-----------+------------+

17 rows in set (0.00 sec)

关键字like模糊查询：

 通配符'%'表示任意长度的任意内容

mysql>SELECT * FROM employee
    ->WHERE emp_name LIKE '张%';    #以张开头后面不限都能查询出来
+----+-----------+--------+-----+------------+--------+--------------+----------+--------+-----------+
| id | emp_name  | sex    | age | hire_date  | post   | post_comment | salary   | office | depart_id |
+----+-----------+--------+-----+------------+--------+--------------+----------+--------+-----------+
| 29 | 张晓敏    | female |  38 | 2010-11-01 | 销售   | NULL         |  2000.35 |    402 |         2 |
| 34 | 张国      | male   |  18 | 1997-03-12 | 运营   | NULL         | 20000.00 |    403 |         3 |
| 37 | 张罗好    | female |  18 | 2014-05-12 | 运营   | NULL         | 17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+--------+--------------+----------+--------+-----------+
3 rows in set (0.00 sec)

通配符：'_'一个字符长度的任意内容

mysql>     SELECT * FROM employee
    ->             WHERE emp_name LIKE '赵__';
+----+-----------+------+-----+------------+--------+--------------+------------+--------+-----------+
| id | emp_name  | sex  | age | hire_date  | post   | post_comment | salary     | office | depart_id |
+----+-----------+------+-----+------------+--------+--------------+------------+--------+-----------+
| 21 | 赵晓明    | male |  78 | 2015-03-02 | 教学   | NULL         | 1000000.31 |    401 |         1 |
| 26 | 赵晓明    | male |  18 | 1900-03-01 | 教学   | NULL         |   30000.00 |    401 |         1 |
+----+-----------+------+-----+------------+--------+--------------+------------+--------+-----------+
2 rows in set (0.05 sec)

聚合函数：聚合函数聚合的是组的内容，若是没有分组，则默认一组

1. count 求个数
2. max 求最大值
3. min 求最小值
4. sum 求和
5. avg 求平均

mysql> select count( * ) from employee;    #求雇员表的个数
+------------+
| count( * ) |
+------------+
|         19 |
+------------+
1 row in set (0.00 sec)

mysql> select max(salary) from employee;    #求雇员表里的薪资最大值
+-------------+
| max(salary) |
+-------------+
|  1000000.31 |
+-------------+
1 row in set (0.00 sec)

mysql> select min(salary) from employee;    #求雇员表里的薪资最大值
+-------------+
| min(salary) |
+-------------+
|     1000.37 |
+-------------+
1 row in set (0.00 sec)

mysql> select  sum(salary) from employee;    #求雇员表的和
+-------------+
| sum(salary) |
+-------------+
|  1174502.57 |
+-------------+
1 row in set (0.00 sec)

mysql> select avg(salary) from employee;    #求雇员表的平均值
+--------------+
| avg(salary)  |
+--------------+
| 61815.924737 |
+--------------+
1 row in set (0.00 sec)

having：过滤语句

#！！！执行优先级从高到低：where > group by > having 
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

• 在having条件中可以使用聚合函数,在where中不行
• 适合去筛选符合条件的某一组数据,而不是某一行数据
• 先分组再过滤 : 求平均薪资大于xx的部门,求人数大于xx的性别,求大于xx人的年龄段
• 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
• group by post having count(id) < 2;

排序： order by

• 默认是升序  asc
• 降序  desc
• select * from employee order by age, salary desc;
• 优先根据age从小到大排,在age相同的情况下,再根据薪资从大到小排

#升序排序
mysql> select * from employee order by salary;    
+----+-----------+--------+-----+------------+--------+--------------+------------+--------+-----------+
| id | emp_name  | sex    | age | hire_date  | post   | post_comment | salary     | office | depart_id |
+----+-----------+--------+-----+------------+--------+--------------+------------+--------+-----------+
| 30 | 冯小刚    | female |  18 | 2011-03-12 | 销售   | NULL         |    1000.37 |    402 |         2 |
| 29 | 张晓敏    | female |  38 | 2010-11-01 | 销售   | NULL         |    2000.35 |    402 |         2 |
| 24 | 陈继承    | male   |  28 | 2012-11-01 | 教学   | NULL         |    2100.00 |    401 |         1 |
| 28 | 菟丝子    | female |  48 | 2015-03-11 | 销售   | NULL         |    3000.13 |    402 |         2 |
| 31 | 老小月    | female |  18 | 2016-05-13 | 销售   | NULL         |    3000.29 |    402 |         2 |
| 23 | 延阻聚    | male   |  73 | 2014-07-01 | 教学   | NULL         |    3500.00 |    401 |         1 |
| 32 | 格格      | female |  28 | 2017-01-27 | 销售   | NULL         |    4000.33 |    402 |         2 |
| 19 | 彭哈哈    | male   |  18 | 2017-03-01 | 教学   | NULL         |    7300.33 |    401 |         1 |
| 20 | 彭哈哈    | male   |  18 | 2017-03-01 | 教学   | NULL         |    7300.33 |    401 |         1 |
| 22 | 爱祖国    | male   |  81 | 2013-03-05 | 教学   | NULL         |    8300.00 |    401 |         1 |
| 25 | 李小荷    | female |  18 | 2011-02-11 | 教学   | NULL         |    9000.00 |    401 |         1 |
| 27 | 高富帅    | male   |  48 | 2010-11-11 | 教学   | NULL         |   10000.00 |    401 |         1 |
| 33 | 彭消息    | male   |  28 | 2016-03-11 | 运营   | NULL         |   10000.13 |    403 |         3 |
| 37 | 张罗好    | female |  18 | 2014-05-12 | 运营   | NULL         |   17000.00 |    403 |         3 |
| 36 | 罗超      | male   |  18 | 2015-04-11 | 运营   | NULL         |   18000.00 |    403 |         3 |
| 35 | 小路小    | female |  18 | 2013-03-11 | 运营   | NULL         |   19000.00 |    403 |         3 |
| 34 | 张国      | male   |  18 | 1997-03-12 | 运营   | NULL         |   20000.00 |    403 |         3 |
| 26 | 赵晓明    | male   |  18 | 1900-03-01 | 教学   | NULL         |   30000.00 |    401 |         1 |
| 21 | 赵晓明    | male   |  78 | 2015-03-02 | 教学   | NULL         | 1000000.31 |    401 |         1 |
+----+-----------+--------+-----+------------+--------+--------------+------------+--------+-----------+
19 rows in set (0.00 sec)

#倒序排序
mysql> select * from employee order by salary asc;
+----+-----------+--------+-----+------------+--------+--------------+------------+--------+-----------+
| id | emp_name  | sex    | age | hire_date  | post   | post_comment | salary     | office | depart_id |
+----+-----------+--------+-----+------------+--------+--------------+------------+--------+-----------+
| 30 | 冯小刚    | female |  18 | 2011-03-12 | 销售   | NULL         |    1000.37 |    402 |         2 |
| 29 | 张晓敏    | female |  38 | 2010-11-01 | 销售   | NULL         |    2000.35 |    402 |         2 |
| 24 | 陈继承    | male   |  28 | 2012-11-01 | 教学   | NULL         |    2100.00 |    401 |         1 |
| 28 | 菟丝子    | female |  48 | 2015-03-11 | 销售   | NULL         |    3000.13 |    402 |         2 |
| 31 | 老小月    | female |  18 | 2016-05-13 | 销售   | NULL         |    3000.29 |    402 |         2 |
| 23 | 延阻聚    | male   |  73 | 2014-07-01 | 教学   | NULL         |    3500.00 |    401 |         1 |
| 32 | 格格      | female |  28 | 2017-01-27 | 销售   | NULL         |    4000.33 |    402 |         2 |
| 19 | 彭哈哈    | male   |  18 | 2017-03-01 | 教学   | NULL         |    7300.33 |    401 |         1 |
| 20 | 彭哈哈    | male   |  18 | 2017-03-01 | 教学   | NULL         |    7300.33 |    401 |         1 |
| 22 | 爱祖国    | male   |  81 | 2013-03-05 | 教学   | NULL         |    8300.00 |    401 |         1 |
| 25 | 李小荷    | female |  18 | 2011-02-11 | 教学   | NULL         |    9000.00 |    401 |         1 |
| 27 | 高富帅    | male   |  48 | 2010-11-11 | 教学   | NULL         |   10000.00 |    401 |         1 |
| 33 | 彭消息    | male   |  28 | 2016-03-11 | 运营   | NULL         |   10000.13 |    403 |         3 |
| 37 | 张罗好    | female |  18 | 2014-05-12 | 运营   | NULL         |   17000.00 |    403 |         3 |
| 36 | 罗超      | male   |  18 | 2015-04-11 | 运营   | NULL         |   18000.00 |    403 |         3 |
| 35 | 小路小    | female |  18 | 2013-03-11 | 运营   | NULL         |   19000.00 |    403 |         3 |
| 34 | 张国      | male   |  18 | 1997-03-12 | 运营   | NULL         |   20000.00 |    403 |         3 |
| 26 | 赵晓明    | male   |  18 | 1900-03-01 | 教学   | NULL         |   30000.00 |    401 |         1 |
| 21 | 赵晓明    | male   |  78 | 2015-03-02 | 教学   | NULL         | 1000000.31 |    401 |         1 |
+----+-----------+--------+-----+------------+--------+--------------+------------+--------+-----------+
19 rows in set (0.00 sec)

# 优先根据age从小到大排,在age相同的情况下,再根据薪资从大到小排
mysql> select * from employee order by age, salary desc;
+----+-----------+--------+-----+------------+--------+--------------+------------+--------+-----------+
| id | emp_name  | sex    | age | hire_date  | post   | post_comment | salary     | office | depart_id |
+----+-----------+--------+-----+------------+--------+--------------+------------+--------+-----------+
| 26 | 赵晓明    | male   |  18 | 1900-03-01 | 教学   | NULL         |   30000.00 |    401 |         1 |
| 34 | 张国      | male   |  18 | 1997-03-12 | 运营   | NULL         |   20000.00 |    403 |         3 |
| 35 | 小路小    | female |  18 | 2013-03-11 | 运营   | NULL         |   19000.00 |    403 |         3 |
| 36 | 罗超      | male   |  18 | 2015-04-11 | 运营   | NULL         |   18000.00 |    403 |         3 |
| 37 | 张罗好    | female |  18 | 2014-05-12 | 运营   | NULL         |   17000.00 |    403 |         3 |
| 25 | 李小荷    | female |  18 | 2011-02-11 | 教学   | NULL         |    9000.00 |    401 |         1 |
| 19 | 彭哈哈    | male   |  18 | 2017-03-01 | 教学   | NULL         |    7300.33 |    401 |         1 |
| 20 | 彭哈哈    | male   |  18 | 2017-03-01 | 教学   | NULL         |    7300.33 |    401 |         1 |
| 31 | 老小月    | female |  18 | 2016-05-13 | 销售   | NULL         |    3000.29 |    402 |         2 |
| 30 | 冯小刚    | female |  18 | 2011-03-12 | 销售   | NULL         |    1000.37 |    402 |         2 |
| 33 | 彭消息    | male   |  28 | 2016-03-11 | 运营   | NULL         |   10000.13 |    403 |         3 |
| 32 | 格格      | female |  28 | 2017-01-27 | 销售   | NULL         |    4000.33 |    402 |         2 |
| 24 | 陈继承    | male   |  28 | 2012-11-01 | 教学   | NULL         |    2100.00 |    401 |         1 |
| 29 | 张晓敏    | female |  38 | 2010-11-01 | 销售   | NULL         |    2000.35 |    402 |         2 |
| 27 | 高富帅    | male   |  48 | 2010-11-11 | 教学   | NULL         |   10000.00 |    401 |         1 |
| 28 | 菟丝子    | female |  48 | 2015-03-11 | 销售   | NULL         |    3000.13 |    402 |         2 |
| 23 | 延阻聚    | male   |  73 | 2014-07-01 | 教学   | NULL         |    3500.00 |    401 |         1 |
| 21 | 赵晓明    | male   |  78 | 2015-03-02 | 教学   | NULL         | 1000000.31 |    401 |         1 |
| 22 | 爱祖国    | male   |  81 | 2013-03-05 | 教学   | NULL         |    8300.00 |    401 |         1 |
+----+-----------+--------+-----+------------+--------+--------------+------------+--------+-----------+
19 rows in set (0.00 sec)