语法：
一、单表查询的语法
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
二、关键字的执行优先级（重点）
重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数创建公司员工表，表的字段和数据类型
company.employee
员工id id int
姓名 name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int创建表,设置字段的约束条件
mysql> create table employee(id int primary key auto_increment,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);
Query OK, 0 rows affected (0.39 sec)
查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| 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.00 sec)
插入记录
三个部门：教学，销售，运营
mysql> insert into employee(name ,sex,age,hire_date,post,salary,office,depart_id) values
('zhangsan','male',18,'20170301','公关部',7300.33,401,1), #以下是教学部
('lisi','male',78,'20150302','teacher',1000000.31,401,1),
('wangwu','male',81,'20130305','teacher',8300,401,1),
('zhuliu','male',73,'20140701','teacher',3500,401,1),
('chenqi','male',28,'20121101','teacher',2100,401,1),
('wangba','female',18,'20110211','teacher',9000,401,1),
('shenjiu','male',18,'19000301','teacher',30000,401,1),
('tianshi','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
Query OK, 18 rows affected (0.03 sec)
Records: 18 Duplicates: 0 Warnings: 0
查询表数据
mysql> mysql> select * from employee;
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 1 | zhangsan | male | 18 | 2017-03-01 | 公关部 | NULL | 7300.33 | 401 | 1 |
| 2 | lisi | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wangwu | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | zhuliu | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | chenqi | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | wangba | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | shenjiu | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | tianshi | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)
（1）where 约束
where子句中可以使用
1.比较运算符：>、<、>=、<=、<>、!=
2.between 80 and 100 ：值在80到100之间
3.in(80,90,100)值是10或20或30
4.like 'xiaomagepattern': pattern可以是%或者_。%小时任意多字符，_表示一个字符
5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not验证结果:where条件约束
1 :单条件查询
mysql> select id,name from employee where id > 5;
+----+-----------+
| id | name |
+----+-----------+
| 6 | wangba |
| 7 | shenjiu |
| 8 | tianshi |
| 9 | 歪歪 |
| 10 | 丫丫 |
| 11 | 丁丁 |
| 12 | 星星 |
| 13 | 格格 |
| 14 | 张野 |
| 15 | 程咬金 |
| 16 | 程咬银 |
| 17 | 程咬铜 |
| 18 | 程咬铁 |
+----+-----------+
13 rows in set (0.01 sec)
2 :多条件查询
mysql> select id,name,salary from employee where post='teacher' and salary>10000;
+----+---------+------------+
| id | name | salary |
+----+---------+------------+
| 2 | lisi | 1000000.31 |
| 7 | shenjiu | 30000.00 |
+----+---------+------------+
2 rows in set (0.00 sec)
3 :关键字BETWEEN AND
mysql> select name,salary from employee where salary between 10000 and 20000;
+-----------+----------+
| name | salary |
+-----------+----------+
| tianshi | 10000.00 |
| 张野 | 10000.13 |
| 程咬金 | 20000.00 |
| 程咬银 | 19000.00 |
| 程咬铜 | 18000.00 |
| 程咬铁 | 17000.00 |
+-----------+----------+
6 rows in set (0.00 sec)
mysql> select name,salary from employee where salary not between 10000 and 20000;
+----------+------------+
| name | salary |
+----------+------------+
| zhangsan | 7300.33 |
| lisi | 1000000.31 |
| wangwu | 8300.00 |
| zhuliu | 3500.00 |
| chenqi | 2100.00 |
| wangba | 9000.00 |
| shenjiu | 30000.00 |
| 歪歪 | 3000.13 |
| 丫丫 | 2000.35 |
| 丁丁 | 1000.37 |
| 星星 | 3000.29 |
| 格格 | 4000.33 |
+----------+------------+
12 rows in set (0.00 sec)
4 :注意''是空字符串，不是null
mysql> select name,post_comment from employee where post_comment='';
Empty set (0.00 sec)
5:关键字IN集合查询
mysql> select name,salary from employee where salary=3000 or salary=3500 or salary=4000 or salary=9000 ;
+--------+---------+
| name | salary |
+--------+---------+
| zhuliu | 3500.00 |
| wangba | 9000.00 |
+--------+---------+
2 rows in set (0.00 sec)
mysql> select name,salary from employee where salary in (3000,3500,4000,9000);
+--------+---------+
| name | salary |
+--------+---------+
| zhuliu | 3500.00 |
| wangba | 9000.00 |
+--------+---------+
2 rows in set (0.00 sec)
mysql> select name,salary from employee where salary not in (3000,3500,4000,9000);
+-----------+------------+
| name | salary |
+-----------+------------+
| zhangsan | 7300.33 |
| lisi | 1000000.31 |
| wangwu | 8300.00 |
| chenqi | 2100.00 |
| shenjiu | 30000.00 |
| tianshi | 10000.00 |
| 歪歪 | 3000.13 |
| 丫丫 | 2000.35 |
| 丁丁 | 1000.37 |
| 星星 | 3000.29 |
| 格格 | 4000.33 |
| 张野 | 10000.13 |
| 程咬金 | 20000.00 |
| 程咬银 | 19000.00 |
| 程咬铜 | 18000.00 |
| 程咬铁 | 17000.00 |
+-----------+------------+
16 rows in set (0.00 sec)
6 :关键字LIKE模糊查询
通配符’%’
mysql> select * from employee where name like 'zhang%';
+----+----------+------+-----+------------+-----------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+----------+------+-----+------------+-----------+--------------+---------+--------+-----------+
| 1 | zhangsan | male | 18 | 2017-03-01 | 公关部 | NULL | 7300.33 | 401 | 1 |
+----+----------+------+-----+------------+-----------+--------------+---------+--------+-----------+
1 row in set (0.00 sec)
通配符'_'
mysql> select id,age,salary from employee where name like 'lis_';
+----+-----+------------+
| id | age | salary |
+----+-----+------------+
| 2 | 78 | 1000000.31 |
+----+-----+------------+
1 row in set (0.00 sec)
mysql> select id,age,salary from employee where name like 'li__';
+----+-----+------------+
| id | age | salary |
+----+-----+------------+
| 2 | 78 | 1000000.31 |
+----+-----+------------+
1 row in set (0.00 sec)
练习：根据上面创建的表
1 查看岗位是teacher的员工姓名、年龄
mysql> select name,age from employee where post='teacher';
+---------+-----+
| name | age |
+---------+-----+
| lisi | 78 |
| wangwu | 81 |
| zhuliu | 73 |
| chenqi | 28 |
| wangba | 18 |
| shenjiu | 18 |
| tianshi | 48 |
+---------+-----+
7 rows in set (0.00 sec)
2 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
mysql> select name,age from employee where post='teacher' and age >30;
+---------+-----+
| name | age |
+---------+-----+
| lisi | 78 |
| wangwu | 81 |
| zhuliu | 73 |
| tianshi | 48 |
+---------+-----+
4 rows in set (0.00 sec)
3 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
mysql> select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
+---------+-----+----------+
| name | age | salary |
+---------+-----+----------+
| wangba | 18 | 9000.00 |
| tianshi | 48 | 10000.00 |
+---------+-----+----------+
2 rows in set (0.00 sec)
4 查看岗位描述不为NULL的员工信息
mysql> select * from employee where post_comment is not null;
Empty set (0.00 sec)
5 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
mysql> select name,age,salary from employee where post='teacher' and salary in(9000,10000,30000);
+---------+-----+----------+
| name | age | salary |
+---------+-----+----------+
| wangba | 18 | 9000.00 |
| shenjiu | 18 | 30000.00 |
| tianshi | 48 | 10000.00 |
+---------+-----+----------+
3 rows in set (0.00 sec)
6 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
mysql> select name,age,salary from employee where post='teacher' and salary not in(9000,10000,30000);
+--------+-----+------------+
| name | age | salary |
+--------+-----+------------+
| lisi | 78 | 1000000.31 |
| wangwu | 81 | 8300.00 |
| zhuliu | 73 | 3500.00 |
| chenqi | 28 | 2100.00 |
+--------+-----+------------+
4 rows in set (0.00 sec)
7 查看岗位是teacher且名字是wang开头的员工姓名、年薪
mysql> select name,salary*12 from employee where post='teacher' and name like 'wang%';
+--------+-----------+
| name | salary*12 |
+--------+-----------+
| wangwu | 99600.00 |
| wangba | 108000.00 |
+--------+-----------+
2 rows in set (0.00 sec)
（2）group by 分组查询
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
#3、为何要分组呢？
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数
小窍门：‘每’这个字后面的字段，就是我们分组的依据
#4、大前提：
可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助
于聚合函数当执行以下sql语句的时候，是以post字段查询了组中的第一条数据，没有任何意义，因为我们现在想查出当前组的多条记录
mysql> select * from employee group by post; # 查询不全
+----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 2 | lisi | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 1 | zhangsan | male | 18 | 2017-03-01 | 公关部 | NULL | 7300.33 | 401 | 1 |
+----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)
由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的,如果想分组，则必须要设置全局的sql的模式为ONLY_FULL_GROUP_BY
mysql> set global sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.sql_mode;
+--------------------+
| @@global.sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)
继续验证通过group by分组之后，只能查看当前字段,如果想查看组内信息，需要借助于聚合函数
mysql> select post from employee group by post;
+-----------+
| post |
+-----------+
| operation |
| sale |
| teacher |
| 公关部 |
+-----------+
4 rows in set (0.00 sec)
（3）聚合函数
max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求总个数
group_concat(字段) 字段所有信息强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
每个部门有多少个员工
mysql> select post,count(id) from employee group by post;
+-----------+-----------+
| post | count(id) |
+-----------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 公关部 | 1 |
+-----------+-----------+
4 rows in set (0.00 sec)
每个部门的最高薪水
mysql> select post,max(salary) from employee group by post;
+-----------+-------------+
| post | max(salary) |
+-----------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 公关部 | 7300.33 |
+-----------+-------------+
4 rows in set (0.00 sec)
每个部门的最低薪水
mysql> select post,min(salary) from employee group by post;
+-----------+-------------+
| post | min(salary) |
+-----------+-------------+
| operation | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
| 公关部 | 7300.33 |
+-----------+-------------+
4 rows in set (0.00 sec)
每个部门的平均薪水
mysql> select post,avg(salary) from employee group by post;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 公关部 | 7300.330000 |
+-----------+---------------+
4 rows in set (0.00 sec)
每个部门的所有薪水
mysql> select post,sum(salary) from employee group by post;
+-----------+-------------+
| post | sum(salary) |
+-----------+-------------+
| operation | 84000.13 |
| sale | 13001.47 |
| teacher | 1062900.31 |
| 公关部 | 7300.33 |
+-----------+-------------+
4 rows in set (0.00 sec)
小练习:
1 查询岗位名以及岗位包含的所有员工名字
mysql> select post,group_concat(name) from employee group by post;
+-----------+--------------------------------------------------+
| post | group_concat(name) |
+-----------+--------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| sale | 格格,星星,丁丁,丫丫,歪歪 |
| teacher | tianshi,shenjiu,wangba,chenqi,zhuliu,wangwu,lisi |
| 公关部 | zhangsan |
+-----------+--------------------------------------------------+
4 rows in set (0.00 sec)
2 查询岗位名以及各岗位内包含的员工个数
mysql> select post,count(id) from employee group by post;
+-----------+-----------+
| post | count(id) |
+-----------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 公关部 | 1 |
+-----------+-----------+
4 rows in set (0.00 sec)
3 查询公司内男员工和女员工的个数
mysql> select sex,count(1) from employee group by sex;
+--------+----------+
| sex | count(1) |
+--------+----------+
| male | 10 |
| female | 8 |
+--------+----------+
2 rows in set (0.00 sec)
4 查询岗位名以及各岗位的平均薪资
mysql> select post,avg(salary) from employee group by post;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 公关部 | 7300.330000 |
+-----------+---------------+
4 rows in set (0.00 sec)
5 查询岗位名以及各岗位的最高薪资
mysql> select post,max(salary) from employee group by post;
+-----------+-------------+
| post | max(salary) |
+-----------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 公关部 | 7300.33 |
+-----------+-------------+
4 rows in set (0.00 sec)
6 查询岗位名以及各岗位的最低薪资
mysql> select post,min(salary) from employee group by post;
+-----------+-------------+
| post | min(salary) |
+-----------+-------------+
| operation | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
| 公关部 | 7300.33 |
+-----------+-------------+
4 rows in set (0.00 sec)
7 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
mysql> select sex,avg(salary) from employee group by sex;
+--------+---------------+
| sex | avg(salary) |
+--------+---------------+
| male | 110920.077000 |
| female | 7250.183750 |
+--------+---------------+
2 rows in set (0.00 sec)
（4）HAVING过滤
HAVING与WHERE不一样的地方在于
#！！！执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数mysql> select * from employee where salary>1000000;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | lisi | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)
mysql> select * from employee having salary>1000000;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | lisi | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)
错误，分组后无法直接取到salary字段
mysql> select post,group_concat(name) from emp group by post having salary > 10000;
ERROR 1146 (42S02): Table 'db1.emp' doesn't exist
小练习：
1 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
mysql> select post,group_concat(name),count(1) from employee group by post having count(id) < 2;
+-----------+--------------------+----------+
| post | group_concat(name) | count(1) |
+-----------+--------------------+----------+
| 公关部 | zhangsan | 1 |
+-----------+--------------------+----------+
1 row in set (0.00 sec)
2 查询各岗位平均薪资大于10000的岗位名、平均工资
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00 sec)
3 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
mysql> select post,avg(salary) from employee group by post having 10000 < avg(salary) and avg(salary) < 20000 ;
+-----------+--------------+
| post | avg(salary) |
+-----------+--------------+
| operation | 16800.026000 |
+-----------+--------------+
1 row in set (0.00 sec)
（5）order by 查询排序
按单列排序
SELECT * FROM employee ORDER BY age;
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age DESC;
按多列排序:先按照age升序排序，如果年纪相同，则按照id降序
SELECT * from employee
ORDER BY age ASC,
id DESC;验证多列排序
mysql> select * from employee order by age asc,id desc;
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 7 | shenjiu | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 6 | wangba | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 1 | zhangsan | male | 18 | 2017-03-01 | 公关部 | NULL | 7300.33 | 401 | 1 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 5 | chenqi | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 8 | tianshi | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 4 | zhuliu | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 2 | lisi | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wangwu | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)
小练习
1 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
mysql> select * from employee order by age asc,hire_date desc;
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 1 | zhangsan | male | 18 | 2017-03-01 | 公关部 | NULL | 7300.33 | 401 | 1 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 6 | wangba | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 7 | shenjiu | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 5 | chenqi | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 8 | tianshi | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 4 | zhuliu | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 2 | lisi | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wangwu | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)
2 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00 sec)
3 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| teacher | 151842.901429 |
| operation | 16800.026000 |
+-----------+---------------+
2 rows in set (0.00 sec)
（5）limit 限制查询的记录数:
示例：
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #默认初始位置为0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条小练习:
分页显示，每页5条
第一页数据
mysql> select * from employee limit 0,5;
+----+----------+------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+----------+------+-----+------------+-----------+--------------+------------+--------+-----------+
| 1 | zhangsan | male | 18 | 2017-03-01 | 公关部 | NULL | 7300.33 | 401 | 1 |
| 2 | lisi | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wangwu | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | zhuliu | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | chenqi | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
+----+----------+------+-----+------------+-----------+--------------+------------+--------+-----------+
5 rows in set (0.00 sec)
第二页数据
mysql> select * from employee limit 5,5;
+----+---------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+---------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 6 | wangba | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | shenjiu | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | tianshi | male | 48 | 2010-11-11 | teacher | NULL | 10000.
