本节内容：
1、单表查询的语法
2、关键字的执行优先级（重点）
3、简单查询
4、where约束（重点）
5、group by 分组查询（重点）
6、having 过滤（重点）
7、order by 查询排序（重点）
8、limit 限制查询的记录数（重点）
9、使用正则表达式查询
一、单表查询的语法
查询数据的本质：mysql会到你本地的硬盘上找到对应的文件，
然后打开文件，按照你的查询条件来找出你需要的数据。
下面是完整的一个单表查询的语法
select * from 表名； # 这个select * 指的是要查询所有字段的数据。
select distinct 字段1，字段2... from 库名.表名； # from后面是说从库的某个表中去找数据
where 条件 # 根据where的条件查询
group by field(字段) # 根据字段分组
having 条件 （筛选功能） # 根据having的条件筛选，得到结果
order by field（字段） # 将结果按照这个条件排序
limit 限制条数 # 限制查询出来的数据记录条数
我们写的查询语句是这样的：select id,name from employee;实际上我们在select每个字段的时候，省略了一个表名，
有的人可能会这样写，select employee.id,employee.name from employee;你会发现查询出来的结果是一样的，
但是如果你要将查询出来的结果表，起一个新表名的话，带着表名这样写就错了
下面开始详细介绍每一个命令
Bash
二、关键字的执行优先级（重点）
1、关键字的执行优先级：重点中的重点
# 从上往下是执行先后顺序
from 1.找到表：from
where 2.根据where指定的约束条件，从文件/表中取出每一条记录
group by 3.将取出的每一条记录，根据group by分组，若没有group by，则整体为一组
having 4.对分组的结果进行having过滤，就是进一步的筛选
select 5.把符合上面的条件的结果，执行select选出来
distinct 6.去重
order by 7.对结果再进行条件排序：order by
limit 8.限制显示的条数
Bash
详见：
三、简单查询
注意一点，在查询语句里面select 字段 from 表，这几项是必须要有的，
其他的什么where、group by等等都是可有可无的
先创建一个表，为后面执行查询操作演示
#我们来创建一个员工表，然后对员工表进行一个简单的查询，来看一下效果，下面是员工表的字段
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,
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;
#插入记录
#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','驻沙河办事处外交大使',7300.33,401,1), #以下是教学部，全都是老师
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','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)
;
#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
Bash
1、查询操作
相关查询操作
1、简单的查询
select id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee; 查询每一项
select * from employee; # 查询每一项， 不推荐使用，效率低，原因是：需要先识别统计所有字段再进行下一步
select name,salary from employee; # 查找其中的两项
2、distinct（去重，避免显示重复数据）
select post from employee; # 我们会看到很多重复的内容，我们只是想看看有几个部门
select distinct post from employee; # 只写一个时，根据post删除一样的数据，根据每个不同的post，每个保留一项
select distinct post,salary from employee; # 只有post和salary两项同时重复，才会被去掉
3、通过四则运算查询
select name,salary*12 from employee; # 查询年薪，但是薪资的字段变成salary*12,需要给个字段名
select name,salary*12 as Annual_salary from employee; # as + 新字段名，就可以给查询结果起字段名
# 其他运算方法相同，这里就不做演示
4、自定义显示格式，自己规定查询结果的显示格式
concat() # 可以将多个查询结果连接在一起显示，还可以拼接自己输入的字符串
fe:
select concat("姓名:",name,"年薪:",salary*12) as Annual_salary from employee;
# 结果：
+---------------------------------------+
| Annual_salary |
+---------------------------------------+
| 姓名: egon 年薪: 87603.96 |
还可以concat多个，
4.2 结合case语句：结合条件对不同的结果进行不同的一些加工
SELECT
(
CASE
WHEN NAME = 'egon' THEN
NAME
WHEN NAME = 'alex' THEN
CONCAT(name,'_BIGSB')
ELSE
concat(NAME, 'SB')
END
) as new_name,sex
FROM
employee;
#看结果:
+--------------+--------+
| new_name | sex |
+--------------+--------+
| egon | male |
| alex_BIGSB | male |
| wupeiqiSB | male |
| yuanhaoSB | male |
| liwenzhouSB | male |
| jingliyangSB | female |
| jinxinSB | male |
| 成龙SB | male |
...
+--------------+
Bash
fe:小练习
1 查出所有员工的名字，薪资,格式为
<名字:egon> <薪资:3000>
2 查出所有的岗位（去掉重复）
3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
答案
select concat('<名字:',name,'> ','<薪资:',salary,'>') from employee;
select distinct depart_id from employee;
select name,salary*12 annual_salary from employee;
四、where约束
1、where的约束条件
1、where不能使用聚合函数，原因：（对照having）
Where 发生在分组group by之前，因而Where中可以有任意字段，
但是绝对不能使用聚合函数。
1、比较运算符：> < >= <= <> !=
fe: where id=1
2、between 80 and 100 # 值在80到100之间数
fe: where age between 1 and 10
3、in （80,90,100) # 值是80或90或100的数
fe: where age in (80,90,100)
4、like "egon%" # 根据开头模糊查询，最后一个字符是模糊查询的pattern，%是所有字符都匹配，_只匹配一个
fe: where like "eg%" # 查找所有eg开头的内容，
5、逻辑运算符
多个条件组合查询，使用and or not
fe: where post="teacher" and salary>10000
具体的查询代码示例
1、单条件查询
select name from employee where post='sale'; # 注意优先级，先找到employee表，根据post='sale'，然后在select
2、多条件查询
select name,salary from employee where post='teacher' and salary>10000;
3、关键字 between x1 and x2 ：选择一个区间的数
# 得到10000到20000之间的数
select name,salary from employee where salary between 10000 and 20000;
# 加not，就是得到非这个区间的数
4、关键字 is null （判断某个字段是否为null，只能用is，固定用法）
select name,post_comment from employee where post_comment is null;
注意：
select name,post_comment from employee where post_comment = "";
这里的""是空字符串，不是null，null是啥也没有，这是两种数据类型
5、关键字 in ：集合查询
select name,salary from employee where salary in (3000,3500,4000,8000);
# 查找在这四个值得数据
6、关键字 like 模糊查询
# 通配符’%’ #匹配任意所有字符
select * from employee where name like "eg%";
# 通配符’_’ #匹配任意一个字符 , 多少个下划线，就匹配多少个字符
select * from employee where name like "al__" # 以al开头再模糊匹配两个字符
Bash
2、where条件及select执行的顺序
以下面这条sql语句为例：
select id,name,age from employee where id>7;
执行步骤：
1、首先from找到employee这个表
2、mysql根据where后面的条件，把符合id大于7的一整条数据，交给select
3、select按照自己的条件，只拿id,name,age这个三个字段的数据，打印出来
每条数据执行一遍这个流程，直到把所有数据过滤一遍，才结束。
fe:where小练习
1. 查看岗位是teacher的员工姓名、年龄
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
4. 查看岗位描述不为NULL的员工信息
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
Bash
答案
select name,age from employee where post = 'teacher';
select name,age from employee where post='teacher' and age > 30;
select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
select * from employee where post_comment is not null;
select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
select name,salary*12 from employee where post='teacher' and name like 'jin%';
Bash
五、group by : 分组查询
1、在where之后使用，即分组是基于where之后得到的记录而进行的分组
2、分组：指的是根据某个相同的字段进行分类。
3、分组的作用： 以组为单位进行一些数据统计，或是进行一些计算
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数
4、大前提：
可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，
如果想查看组内信息，需要借助于聚合函数
1、根据post分组的结果分析
mysql> select name,post from employee group by post;
+--------+-----------------------------------------+
| name | post |
+--------+-----------------------------------------+
| 张野 | operation |
| 歪歪 | sale |
| alex | teacher |
| egon | 驻沙河办事处外交大使 |
+--------+-----------------------------------------+
通过结果可以看出，如果直接通过post部门字段来进行分组，默认拿到的结果都是每组的第一条数据
我们分组的目的是为了统计整个组的情况，不是看开头的第一个人啊，
接着往下看：
Bash
2、ONLY_FULL_GROUP_BY
# 查看MySQL 5.7默认的sql_mode如下：
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
注意：
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，
简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，
要么是来自于group by list中的表达式的值。
#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Bash
代码示例
1、默认没有设置only_full_group_by,这样只能拿到每个分组的第一条数据
mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| |
+-------------------+
2、mysql> select * from emp group by post;
# 根据部门分组，只能拿到每个分组的第一条数据，这样的数据并没有意义
3、设置only_full_group_by后，必须重新登录才生效
mysql> set global sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> quit #设置成功后，一定要退出，然后重新登录方可生效
Bye
# 重新登录，根据分组查询
mysql> select * from emp group by post; # 报错
ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY
# 意思是告诉你，你select后面取的字段必须在你的group by后面的字段里面才行
# 正确的分组查询的命令
mysql> select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数
+----------------------------+-----------+
| post | count(id) |
+----------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+----------------------------+-----------+
一般我们都会在这种模式下进行分组，下面我们在看看group by，下面的内容
Bash
3、group by的使用及配合聚合函数
1、单独使用group by关键字分组
select post from employee group by post;
注意：按照post字段分组，那么select查询的字段只能是post，
想要获取组内的其他相关信息，需要借助函数
2、group by关键字 和 group_concat()函数一起使用
select post,group_by(name) from employee group by post; # 查看不同岗位的所有成员
SELECT post,GROUP_CONCAT(name,':',salary) as emp_members FROM employee GROUP BY post;
# 聚合就是将分组的数据聚集到一起，合并起来搞事情，拿到一个最后的结果
select post,count(id) as count from employee group by post; # 查看每个部门的人数
3、mysql提供了以下几种聚合函数：
count、max、min、avg、sum等，
上面的group_concat也算是一个聚合函数了，做字符串拼接的操作
4、强调：
如果我们用设置了unique约束的字段作为分组的依据，则每一条记录自成一组，
这种分组没有意义，多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
Bash
4、聚合函数
强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
SELECT COUNT(*) FROM employee; # count是统计个数用的
SELECT COUNT(*) FROM employee WHERE depart_id=1; # where条件满足depart_id=1所有记录的个数
SELECT MAX(salary) FROM employee; # max（）统计分组后每组的最大值；这里没有写group by，就是整个表中薪资最大的
SELECT MIN(salary) FROM employee; # 查找最小值
SELECT AVG(salary) FROM employee; # 查找平均值
SELECT SUM(salary) FROM employee; # 求和
SELECT SUM(salary) FROM employee WHERE depart_id=3 # 满足where条件的数据求和
Bash
5、小练习
1、查询岗位名以及岗位包含的所有员工名字
2、查询岗位名以及各岗位内包含的员工个数
3、查询公司内男员工和女员工的个数
4、查询岗位名以及各岗位的平均薪资
5、查询岗位名以及各岗位的最高薪资
6、查询岗位名以及各岗位的最低薪资
7、查询男员工与男员工的平均薪资，女员工与女员工的平均薪资。（提示：性别）
8、统计各部门年龄在30岁及以上的员工平均薪资
Bash
答案
select post,group_concat(name) from employee group by post;
select post,count(id) from employee group by post;
select sex,count(id) from employee group by sex;
select post,avg(salary) from employee group by post;
select post,max(salary) from employee group by post;
select post,min(salary) from employee group by post;
select sex,avg(salary) from employee group by sex;
select post,avg(salary) from employee where age>=30 group by post;
Bash
六、having过滤（也是筛选的效果）
1、在group by 后使用。
2、having的语法格式和where是一模一样的，
3、having可以使用聚合函数，原因：（对照where）
Having发生在分组group by之后，因而Having中可以使用分组的字段，
无法直接取到其他字段,having是可以使用聚合函数
4、having不能单独使用，having必须跟在group by后面使用，
1、having的简单测试
来个需求：统计各部门年龄在30岁及以上的员工的平均薪资，并且保留平均工资大于10000的部门
正确答案：
select post,avg(salary) as new_sa from employee where age>=30
group by post having avg(salary) > 10000;
看结果：
+---------+---------------+
| post | new_sa |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
单独写个having会报错： 必须配合group by才可以使用
mysql> select * from employee having avg(salary) > 10000; # 报错
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause
Bash
2、小练习
1、查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
2、查询各岗位平均薪资大于10000的岗位名、平均工资
3、查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
Bash
答案
select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
select post,avg(salary) from employee group by post having avg(salary) > 10000;
select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;
Bash
六.五、distinct 去重
1、将查询的结果进行去重：select distinct post from employee;
2、注意distinct去重要写在查询字段的前面，不然会报错，
3、distinct不能返回其他的字段，只能返回目标字段
1、distinct去重要写在查询字段的前面，不然会报错，
select distinct post from employee;
2、distinct只能返回目标字段
mysql> select count(distinct post) from employee;
+----------------------+
| count(distinct post) |
+----------------------+
| 4 |
+----------------------+
mysql> select id,count(distinct post) from employee; # 报错
Bash
七、order by 查询排序
1、单列排序
SELECT * FROM employee ORDER BY salary; # 默认是升序排列
SELECT * FROM employee ORDER BY salary ASC; # 升序
SELECT * FROM employee ORDER BY salary DESC; # 降序
Bash
2、多列排序
数据在第一个排序条件相同的情况下，进行下一个条件排序
1、如果我们只是根据一个age条件来排序，那么age相同时，它是怎么排序呢，乱排
mysql> SELECT * FROM employee ORDER BY age;
+----+------------+--------+-----+------------+-----------------------------------------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
| 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 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.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 | liwenzhou | 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 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
+----+------------+--------+-----+------------+-----------------------------------------+
这里我们就可以用多列排序，指定年龄相同的，再依据其他条件进行排序：
fe:age相同时，根据salary排序，（多个条件之间用逗号隔开）
SELECT * from employee
ORDER BY age, # 注意排序的条件用逗号分隔
salary DESC;
Bash
3、小练习
1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
答案
select * from employee ORDER BY age asc,hire_date desc;
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;
#注意：查询语句的语法是固定上面这样写的，但是运行顺序是这样的：
1、from 2、where 3、group by 4、having 5、select 6、distinct 7、order by 8、limit，我们下面要学的
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
Bash
八、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条
Bash
1、分页显示，每页显示5条数据
好多的网站都可以看到一个分页的功能。
select * from employee limit 0,5; # 第一页
select * from employee limit 5,5; # 第二页
select * from employee limit 10,5; # 第三页
# 到最后不够五条了怎么办，完全不影响，接着写
select * from employee limit 15,5; # 第四页
Bash
九、正则表达式的查询
之前我们用like做模糊匹配，只有%和_，局限性比较强，所以我们说一个正则，
之前我们是不是学过正则匹配，你之前学的正则表达式都可以用，正则是通用的
SELECT * FROM employee WHERE name REGEXP '^ale'; # 正则匹配以ale开头
SELECT * FROM employee WHERE name REGEXP 'on$'; # 以on结尾
SELECT * FROM employee WHERE name REGEXP 'm{2}';
小结：对字符串匹配的方式
WHERE name = 'egon'; # 字符串全匹配
WHERE name LIKE 'yua%'; # 模糊匹配
WHERE name REGEXP 'on$'; # 正则匹配
2、小练习：
查看所有员工中名字是jin开头，n或者g结尾的员工信息
select * from employee where name regexp '^jin.*[g|n]$';