单表查询的语法及关键字执行的优先级
单表查询语法
select distinct 字段一,字段二,... from 表名 where 条件 group by field having 筛选 order by field limit 限制条数
关键字执行的优先级
1. 找到表: from
2. 拿着where指定的约束条件,去文件 / 表中取出一条条记录
3. 将取出的一条条记录进行分组group by , 如果没有group by ,则整体作为一组
4. 执行select (distinct --> 去重)
5. 将分组的结果进行having过滤
6. 将结果按条件排序: order by
7. 限制结果的显示条数
建表和数据的准备:
#创建表 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 ); #插入记录 #三个部门：教学，销售，运营 insert into employee(emp_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) ;
一 . 查询的语法:
1. select 语句:
- 基础命令:
- select * from 表名; 查看表中的所有数据
- select 字段名,字段名,... from 表名; 查看表中的某几列数据
- insert into 已建好的表名(字段,...) select 字段,... from 另一张表名; 从另一张表中导入需要的数据
- 重命名字段
- select 字段名 as 新名字 from 表名;
- select 字段名 新名字 from 表名;
- 因此加不加 as 的效果一样
- 去重:
- select distinct 字段 from 表名;
- 当查询的字段中存在相同的数据时,只会查询到不重复的数据
- 定义显示格式:
- 使用函数
- concat() 用于连接字符串
- select concat('要拼接的字符串',字段名,'要拼接的字符串',字段名) from 表名;
- concat ws() 第一个参数为分隔符
- select concat ws('类似于 | : - 的分隔符',字段名,字段名) from 表名; 将两个或多个字段通过分隔符连接起来
- concat() 用于连接字符串
- 结合case语句 (判断逻辑,相当于if条件判断句)
-
select ( case when 字段名1 = 'aaa' then 字段名 # 如果字段名1的内容为aaa时,就返回字段名原来的数据 when 字段名1 = 'bbb' then concat(字段名1,'wahaha') # 如果字段名1的内容为bbb时,就将字段名和wahaha拼接起来返回 else concat(字段名1,'shuangwaiwai') # 如果字段名1的内容不为上面两个时,就将字段名和shuangwaiwai拼接起来返回 end ) as new_name # 给这字段起一个新名字 from 表名
-
- 使用函数
2. 通过四则运算查询
- 从一张员工月薪表中查询员工的年薪
- select name,salary*12 from 表名; 乘法运算
3. where 筛选出所有符合条件的行
- 比较运算符: < , > , <= , >= , <> , != (后两个为不等于)
- 关键字is : 判断某个字段是否为null,不能用等号,要用is
- where 字段名 is null; 筛选字段为null的数据
- where 字段名 is not null; 筛选字段名不为null的数据
- 关键字between 值 and 值
- where 字段名 between 90 and 100; 筛选字段值在90到100的数据
- 不支持这种写法: 90 < 字段名> 100
- 关键字in查询
- where 字段名 in(80,90,100); 筛选字段值为80或90或100的数据
- 关键字like查询
- % : 通配符,表示可以匹配任意长度的任意内容
- select * from 表名 where 字段名 like 'xx%'; 查询字段名是以xx开头的数据
- select * from 表名 where 字段名 like '%xx'; 查询字段名是以xx结尾的数据
- _ : 通配符,表示可以匹配一个字符长度的任意内容
- select * from 表名 where 字段名 like '林__'; 查询出字段名是以 '林'开头,并且字符长度是3的数据
- % : 通配符,表示可以匹配任意长度的任意内容
4. 逻辑运算 (与 或 非)
- 在多个条件下可以直接使用逻辑运算符: 优先级: not > and > or
二 . 分组聚合
1. 分组: group by 字段名; 根据某个字段分组
- 根据谁分组,可以求出这个组的总人数,最大值,最小值,平均值,求和,但是这个求出来的值只是和分组字段对应,并不和其他任何字段对应,这个时候查出来的所有其他字段都不生效
单独使用GROUP BY关键字分组 SELECT post FROM employee GROUP BY post; 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数 GROUP BY关键字和GROUP_CONCAT()函数一起使用 SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名 SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post; GROUP BY与聚合函数一起使用 select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人
2. 聚合函数
- 强调: 聚合函数聚合的是组的内容,若是没有组,则默认表为一组
- count(字段名) 求个数
- select count(*) from 表名; 查询表中有多少行数据
- max 求最大值
- select 字段名,max(age) from 表名 group by 字段名; 根据字段名分组,然后查询每个分组中年龄最大的一个
- min 求最小值
- select min(age) from 表名; 查询表中年龄最小的一个
- sum 求和
- select sum(age) from 表名; 对表中age这一列的数据求和
- avg 求平均值
- select avg(age) from 表名; 查询表中年龄的平均值
三. having 过滤语句
- 执行优先级: where > group by > having
- where发生在group by之前,ywhere中可以有任意字段,所以不能用分组聚合函数
- 适合去筛选符合条件的某一组数据,而不是某一行数据
- having发生在分组group by之后,因而having中可以使用分组的字段,无法直接获取到其他字段,可以通过聚合函数来获取
mysql> select post,group_concat(emp_name) from emp group by post having salary > 10000; ERROR 1054 (42S22): Unknown column 'salary' in 'having clause' #错误，分组后无法直接取到salary字段 # 通过分组聚合获取值: 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | operation | 16800.026000 |
四. order by 排列语句
- 默认是升序(从小到大) --> asc
- 降序(从大到小) --> desc
# 按多列排序:先按照age排序，如果年纪相同，则按照薪资排序 select * from employee order by age,salary desc; # 查询各岗位平均薪资大于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)
五. limit 限制查询的记录条数
- limit m,n : 从 m+1 项开始,取n项,如果不写m,m默认为0
- 另一种写法: limit n offset m
# 从第0开始，即先查询出第0+1条，然后包含这一条在内往后查3条 select * from employee order by salary desc limit 3; # 从第5开始，即先查询出第6条，然后包含这一条在内往后查5条 select * from employee order by salary desc limit 5,5;