06--写sql语句，单表查询，联表查询，子查询
目录
一、单表查询的语法
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
关键字的执行优先级(重点)
#重点中的重点：关键字的执行优先级
from # 找到表:from
where # 拿着where指定的约束条件，去文件/表中取出一条条记录
group by # 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
having # 将分组的结果进行having过滤
select # 执行select
distinct # 去重
order by # 将结果按条件排序：order by
limit # 限制结果的显示条数
#虽然执行顺序和书写顺序不一致，按照书写顺序的方式写sql
select * 先用*号占位
之后去补全后面的sql语句
最后将*号替换后你想要的具体字段
where筛选条件
# 作用：是对整体数据的一个筛选操作
# 1，查询id大于等于3小于等于6的数据
select id,name,age from emp where id >= 3 and id <=6;
select id,name,from emp where id between 3 and 6; 两者等价
# 2，查询薪资是2万或者1万8或者1万7的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in(20000,18000,17000);
# 3,查询员工姓名中包含字母o的员工的姓名和薪资
模糊查询
like --% 匹配任意多个字符 _ 匹配任意单个字符
select name,salary from emp where name like '%o%';
# 4，查询员工姓名是由4个字符组成的 姓名和薪资char_length()
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name)=4;
# 5，查询id小于3或者id大于6的数据
select * from emp where id not between 3 and 6;
# 6,查询薪资不在2万，1万8,1万7范围的数据
select * from emp where salary not in (20000,18000,17000);
# 7，查询岗位描述为空的员工姓名和岗位名 针对null不用等号 用is
select name,post from emp where post_comment = NULL; 错
select name,post from emp where post_comment is NULL;
#针对null数据，判断的时候用is不要用=
where post_comment is null;
group by分组
# 分组实际应用场景 分组应用场景非常多
男女比例、部门平均薪资、国家之间数据统计
# 1，按照部门分组
select * from emp group by post;
分组之后，最小可操作单位应该是组，还不再是组内的单个数据
上述命令在你没有设置严格模式的时候是可正常执行的，返回的是分组之后，每个组的第一条数据，但是这不符合分组的规范；分组之后不应该考虑单个数据，而应该以组为操作单位（分组之后，没办法直接获取组内单个数据）
如果设置了严格模式，那么上述命令会直接报错
set global sel_mode = 'strict_trans_tables,only_full_group_by';
设置了严格模式之后 ，分组，默认只能拿到分组的数据
select post from emp group by post;
按照什么分组就只能拿到分组，其他字段不能直接获取，需要借助于一些方法
什么时候需要分组？ 看关键字——每个、平均、最高、最低
聚合函数：max,min,sum,count,avg ,用来帮助分组的操作
# 1，获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post; # as是起别名
select post '部门'，max(salary) '最高薪资' from emp group by post;
# as 可以给字段起别名，也可以直接省略不写，但是不推荐。造成语义不明确，容易错乱
as 语法不单单可以给字段起别名，还可以给表 临时 起别名
select emp.id,emp.name from emp;
select emp.id,emp.name from emp as t1; 报错
select t1.id,t1.name from emp as t1;
# 2，获取每个部门的最低薪资
select post,min(salary) from emp group by post;
# 3，获取每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 4，获取每个部门的工资总和
select post,sum(salary) from emp group by post;
# 5，获取每个部门的人数
select post,count(salary) from emp group by post;# 常用，符合逻辑
select post,count(id) from emp group by post;
select post,count(age) from emp group by post;
select post,count(post_comment) from emp group by post; #不能用null
# 7，查询每个人的年薪，12薪
select name,salary *12 from emp;
concat 拼接字符
# 6，查询分组之后的部门名称和每个部门下所有的员工姓名
# group_concat 不单单可以支持你获取分组之后的其他字段，还支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'_dsb') from emp group by post; # 拼接'_dsb'
select post,group_concat(name,':',salary) from emp group by post; #拼接
# concat不分组的时候用,拼接
select concant('NAME:'name),concat('SAL:',salary) from emp;
#concat_ws,如果多个字段之间的连接符 是相同的情况下，你可以直接使用concat_ws来完成
select concat_ws(':',name,age,sex) from emp;
#给用户表加拼接字符
select concat(user,"@",host) from user;
分组注意事项
# 关键字where和group by 同时出现的时候，group by 必须在where的后面。
where先对整体数据进行过滤之后再分组操作
聚合函数只能在分组之后使用,where筛选条件不能使用聚合函数
select id,name,age from emp where max(salary) > 3000; # 报错，非法使用聚合函数
select max(salary) from emp; # 不分组，默认整体就是一组
# 统计各部门年龄在30岁以上的员工平均薪资
1，先求所有年龄大于30岁的员工
select * from emp where age > 30;
2，再对结果进行分组
select * from emp where age > 30 group by post;
select post,avg(salary) from emp where age >30 group by post;
having分组之后的筛选条件
having的语法和where是一致的，只不过having是在分组之后进行的过滤操作，
即having是可以直接使用聚合函数的
# 统计各部门年龄在30岁以上的员工平均工资并保留平均薪资大于1万的部门
select post,avg(salary) from emp
where age>30
group by post
having avg(salary)>10000;
distinct去重
# 一定要注意，必须是完全一样的数据才可以去重！！！
# 一定不要将主键忽视了，有主键存在的情况下，是不可能去重的
[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18},
]
ORM 对象关系映射，让不懂sql语句的人也会操作数据库
select distinct id,age from emp; # 无法去重
select diftinct age from emp; 可以
order by 排序
select * from emp order by salary; 等价于 ： select * from emp order by salary asc;
order by 默认是升序 asc 该asc可以省略不写
也可以修改为降序 desc
select * from emp order by salary desc;
select * from emp order by age desc,salary asc;
# 先按照age降序排，如果碰到age相同 则再按照salary升序排.支持多个字段备用比较
# 统计各部门年龄在10岁以上的员工平均工资并保留平均薪资大于1千的部门，然后对平均工资降序排序
select post,avg(salary) from emp
where age>10
group by post
having avg(salary)>1000
order by avg(salary) desc;
limit 限制展示条数
select * from emp;针对数据过多的情况，我们通常都是做分页处理select * from emp limit 3;
# 只展示3条数据select * from emp limit 0,5;
select * from emp limit 5,5;第一个参数是起始位置，第二个参数是展示条数
聚合函数，函数总结
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组。
聚合函数必须在分组之后使用。count()
#统计password()
#加密distinct()
#去重now()
#当前时间database()
#当前数据库max()
#最大值 如：select max(population) from country;min()
#最小值 如： select min(population) from country;sum()
#求和 如： select sum(population) from country;avg()
#求平均值 如： select avg(population) from country;
正则 regexp
select * from emp where name regexp '^j.*(n|y)$';
正则是一门独立的语言，需要借助re模块#1，re模块中常用的方法findall：分组优先展示 ^j.*(n|y)$ ，不会展示所有正则表达式匹配的内容，而仅仅展示括号内正则表达式匹配到的内容match：从头匹配aearch：从整体匹配
#2.贪婪匹配与非贪婪匹配正则表达式默认都是贪婪匹配的而贪婪变成非贪婪只需要在正则表达式后面加?.*贪婪 .*? 非贪婪
二、单表查询的案例
# 准备表
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);
#插入记录
#三个部门：教学，销售，运营
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);
#简单查询
select id,name,sex,age,post,depart_id from employee;select * from employee;
#避免重复
distinctselect distinct post from employee;
#通过四则运算查询
select name,salary*12 from employee;
# 直接查算出来的年薪
select name,salary*12 as annual_salary from employee;
#字段名改了，as可以不加
#定义显示格式
select concat('<名字:',name,'> ','<薪资:',salary,'>') from employee;CONCAT() 函数用于连接字符串
结合case语句
select
( case
when name = 'egon' then
name
when name = 'alex' then
concat(name,'_teacher')
else
concat(name,'_vip')
end ) as new_name from employee;
#取出每个部门的员工数
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;
#求年龄在20岁以上的男人数与女人数
select sex,count(id) from employee where age > 20 group by sex;
#求每个部门20岁以上人的平均薪资
select post,avg(salary) from employee where age > 20 group by post;
#多聚合函数select post,avg(salary),max(salary),min(age),count(id),sum(age) from employee group by post;
select post,group_concat(name,":",age) from employee group by post;
#查出平均薪资在10000以上的部门
select post,avg(salary) from employee group by post having avg(salary) > 10000;
#查出部门内男员工平均工资在3000以上的部门并排序
select post,avg(salary) from employee where sex="male" group by post having avg(salary) > 3000 order by avg(salary);
##物理表与虚拟表链接
#查询每个部门最近入职的员工
select employee.name,employee.hire_date,employee.post,t1.post,t1.m_d from employeeinner join(select post,max(hire_date) as m_d from employee group by post) as t1 on employee.post = t1.postwhere employee.hire_date = t1.m_d;
三、多表查询
多表操作
前期表准备
# 建表
create table dep(id int primary key auto_increment,name varchar(20));create table emp(id int primary key auto_increment,name varchar(20),sex enum('male','female') not null default 'male',age int,dep_id int);
# 插入数据insert dep values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营'); insert emp(name,sex,age,dep_id) values('jason','male',18,200),('egon','female',28,201),('kevin','male',38,201),('nick','male',48,202),('jerry','female',18,204);
联表查询
select * from dep,emp; # 结果是笛卡尔积
select * from emp,dep where emp.dep_id = dep.id; # 不建议用
mysql也知道，你在后面查询数据过程中，肯定会经常用到拼表操作，所以特地开设了对应的方法，没有对应的就用null补全
#要加上表的前缀，不然容易造成冲突
inner join 内连接
left join 左连接
right join 右连接
union 全连接
# inner join # 只拼接2张表中共有的数据部分 （常用）
select * from emp inner join dep on emp.dep_id = dep.id;
# left join # 左表所有的数据都展示出来，没有对应的项就用NULL
select * from emp left join dep on emp.dep_id = dep.id;
# right join # 右表所有的数据都展示出来，没有对应的项就用NULL
select * from emp right join dep on emp.dep_id = dep.id;
# union # 左右2表所有的数据都展示出来 （不常用）
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
# 全外连接：full join
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
#查询技术部的员工姓名
select emp.name from emp inner join dep on emp.dep_id = dep.id where dep.name = "技术";
#查询平均年龄大于25岁的部门名
select dep.name,avg(age) from emp inner join dep on emp.dep_id = dep.id group by dep.name having avg(age) > 25;
子查询
子查询就是我们平时解决问题的思路
分步骤解决问题 第一步 第二步...
将一个查询语句的结果当做另外一个查询语句的条件去用
(当做条件的时候，用括号括起来)
# 查询部门是技术或者人力资源的员工信息
1，先获取部门的id号
2，再去员工表里面筛选出对应的员工
select id from dep where name = '技术' or name = '人力资源'；
select name from emp where dep_id in (200,201);
-- select * from emp where dep_id in (select id from dep where name = '技术' or name = '人力资源');
表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一张虚拟表跟其他表关联
多表查询就2种方式
先拼接表再查询
子查询一步一步来
子查询扩展
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等
1 、带IN、ANY、all 关键字的子查询
#查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术');
#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);
#not in 无法处理null的值，即子查询中如果存在null的值，not in将无法处理
#查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的资本家
select * from employee where salary > all
(select avg(salary) from employee group by post);
#查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
select * from employee where salary < all
(select avg(salary) from employee group by post);
#查询出薪资不垫底的员工=》薪资在任一部门平均线以上的员工
select * from employee where salary > any
(select avg(salary) from employee group by post);
#查询出薪资不冒尖的员工=》薪资在任一部门平均线以下的员工
select * from employee where salary < any
(select avg(salary) from employee group by post);
案例
# 查询平均年龄在25岁以上的部门名称
----只要是多表查询，就有2种思路，联表，子查询----
# 联表操作
1，先拿到部门和员工表，拼接之后的结果
2，分析语义，得出需要进行分组
select dep.name from emp inner join dep on emp.dep_id = dep.id group by dep.name having avg(age) > 25;
--涉及到多表操作的时候，一定要加上表的前缀--
# 子查询
select name from dep where id in
(select dep_id from emp group by dep_id
having avg(age) > 25);
# 关键字exists（了解）
只返回布尔值 True False
返回True 的时候外层查询语句执行
返回False的时候外层查询语句不再执行
select * from emp where exists
(select id from dep where id>3);
select * from emp where exists
（select id from dep where id>300);
总结
#书写sql语句的时候 select后面先用*占位，之后写完再改；
#在写较为复杂的sql语句的时候，不要想着一口气写完，写一点查一点看一点再写！！！（只要是涉及到数据查询相关的语法都不应该一次性写完，不太现实）
#在做多表查询的时候，联表操作和子查询可能会结合使用
