### part1 单表查询
sql查询完整语法:
select .. from .. where .. group by .. having .. order by .. limit ..
一.where 条件的使用
"""功能:对表中的数据进行过滤筛选"""
"""
语法:
1.判断条件的符号
= > < >= <= != <>(不等于)
2.拼接条件关键字
and or not
3.查询区间范围值
between 小值 and 大值 [小值,大值] 查找两者之间的范围
4.查找某个具体范围值
in(值1,值2,值3) 在括号里这个范围内查询
5.模糊查询 like '%' 通配符
like '%a' 匹配以a结尾的任意长度的字符串
like 'a%' 匹配以a开头的任意长度的字符串
like '%a%' 匹配字符串中含有a字符的字符串
like '_a' 一共是2个长度,以a结尾,前面那个字符是什么无所谓
like 'a__' 一共是3个长度,以a开头,后面是什么字符无所谓
"""
# (1) 单条件查询:
# 查询部门是sale的所有员工姓名:
select emp_name from employee where post = 'sale';
# (2) 多条件查询
# 部门是teacher,并且收入大于10000的所有数据
select * from employee where post = "teacher" and salary > 10000 ;
# (3) 关键 between .. and
# 收入在1万到2万之间的所有姓名和收入
select emp_name,salary from employee where salary between 10000 and 20000;
# 收入不在1万到2万之间的所有姓名和收入
select emp_name,salary from employee where salary not between 10000 and 20000;
# (4) 关键字is null (判断某个字段是不是null , 不能用等号, 只能用is)
# 查询 post_comment 是空的 null
select emp_name,salary,post_comment from employee where post_comment is null;
# 表达 post_comment 不是空的 is not null
select emp_name,salary,post_comment from employee where post_comment is not null;
select emp_name,salary,post_comment from employee where post_comment = null;
# 设置一个值是空的
update employee set post_comment = '' where id = 3;
select emp_name,salary,post_comment from employee where post_comment = '';
# (5) 关键字in的查询
# 查收入是 3000 或者 2500 或者 4000 或者9000的所有员工和收入.
select emp_name,salary from employee where salary=3000 or salary=2500 or salary=4000 or salary=9000;
# 优化: 在当前括号里面查找
select emp_name,salary from employee where salary in(3000,2500,4000,9000);
# 在这个范围用in ,不在这个范围用not in
select emp_name,salary from employee where salary not in(3000,2500,4000,9000,3000.13);
# (6) 关键字like 模糊查询
# 1. 通配符 %
select * from employee where emp_name like "%on";
# 2. 通配符 _
select * from employee where emp_name like "a_e_";
# (7) concat sql内置函数 concat(参数1,参数2,参数3) 把所有参数拼接在一起
# as 用来起别名
select emp_name,concat("姓名:",emp_name,"薪水:",salary) as ss from employee;
# concat_ws("符号",参数1,参数2,参数3) 第一个参数是分隔符,后面写上要拼接的参数
select emp_name,concat_ws(" : ",emp_name,salary) as aa from employee;
# 在sql中可以做四则运算(+ - * /)
select emp_name,concat_ws(" : ",emp_name,salary*12) as aa from employee;
二.group by 分组:
"""group by 分组分类 by 后面的字段 一般是select 后面要搜索的字段"""
select post from employee where depart_id > 1 group by post
# group_concat 按照分组的形式拼接字段
select group_concat(emp_name),post from employee where depart_id > 1 group by post;
# 聚合函数:
# 统计总数 count *代表所有.
select count(*) from employee;
# 统计最大值 max
select max(salary) from employee;
# 统计最小值 min
select min(salary) from employee;
# 统计平均值 avg
select avg(salary) from employee;
# 统计总和sum
select sum(salary) from employee;
# 一般来说 分组 + 聚合函数在一起使用
# 求各部门的平均工资
select post,avg(salary) from employee group by post
select depart_id,avg(salary) from employee group by depart_id
# 查询部门名以及各部门的最高薪资
select post,max(salary) from employee group by post
# 查询公司内男员工和女员工的个数
select sex,count(*) from employee group by sex
# 查询部门名以及部门包含的所有员工名字
select post,emp_name from employee group by post,emp_name
select group_concat(emp_name),post from employee group by post;
三.having 查询数据之后在进行过滤 , 一般是配合group by 使用,主要用于分组之后在过滤
# 比如:求各个部门平均薪资,找出平均薪资大于10000以上的所有部门
select post,avg(salary) from employee group by post having avg(salary) > 10000;
# 1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,count(*),group_concat(emp_name) from employee group by post having count(*) < 2
# 2.查询各岗位平均薪资小于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) < 10000;
# 3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
(1)select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000
(2)select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
四.order by 按照什么字段排序
# 默认升序asc 从小到大排序
select emp_name,age,post from employee order by age
select emp_name,age,post from employee order by age asc
# 倒序 desc 从大到小排序
select emp_name from employee where post ="teacher" order by age desc
# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * from employee order by age asc , hire_date desc
# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;
# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
五.limit 限制查询的条数[用于做数据分页]
# limit m,n 默认m值是0 代表第一条数据,n所代表的是查询几条,从m+1条件开始,查询n条数据
select * from employee limit 0,5
# 从第6条,继续往下搜,搜5条数据.
select * from employee limit 5,5
# 从第11条,继续往下搜,搜5条数据.
select * from employee limit 10,5
# 查询最后一条数据 limit 一个参数,就是查询几条的意思.
select * from employee order by id desc limit 1
select * from employee order by id desc limit 3
六.使用正则表达式查询数据(了解,不好用,查询速度慢,部分结果与python 不一致)
select * from employee where emp_name regexp 'on$';
select * from employee where emp_name regexp '^程';
select * from employee where emp_name regexp '程.*金'; # .*? 这里的?号识别不了
### part2 多表查询
# 内连接(内联查询 inner join ) : 两表或者多表满足条件的数据查询出来 [表与表之间都有的部分会查出来]
"""
语法:
双表的内联:select 字段 from 表1 inner join 表2 on 条件
多表的内联:select 字段 from 表1 inner join 表2 on 条件 inner join 表3 on 条件 ... ...
"""
# 基本写法:
select * from employee inner join department on employee.dep_id = department.id
# 用as 起别名
select * from employee as e inner join department as d on e.dep_id = d.id
# as 也可以省略
select * from employee e inner join department d on e.dep_id = d.id
# 用普通的where 条件来进行查询 默认使用的内联方式
select * from employee,department where employee.dep_id = department.id
select * from employee as e,department as d where e.dep_id = d.id
# 外连接
#(1)左连接(左联查询 left join): 以左表为主,右表为辅,完整查询左表数据,右表没有的数据补null
"""select 字段 from 表1 left join 表2 on 条件"""
select * from employee left join department on employee.dep_id = department.id
#(2)右链接(右联查询 right join):以右表为主,左表为辅,完整查询右表数据,左表没有的数据补null
"""select 字段 from 表1 right join 表2 on 条件"""
select * from employee right join department on employee.dep_id = department.id
#(3)全连接(union)
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
# 例子1:找出年龄大于25岁的员工姓名及所在部门名字
# 内联:
select
employee.id , employee.name as en , department.name as dn
from
employee inner join department on employee.dep_id = department.id
where
age > 25
# 例子2:查询employee 和 department 关联数据,以age字段升序排序
# 内联
select
*
from
employee inner join department on employee.dep_id = department.id
order by
age desc
# where写法:
select *
from
employee,department
where
employee.dep_id = department.id
order by
age desc
### part3 子查询
"""
子查询:嵌套查询
1.子查询是讲一个查询语句嵌套在另外一个查询语句之中,用括号()抱起来,表达一个整体
2.一般应用在from 或者 where 或者 字段中 .子查询这个整体可以作为表,也可以作为where 后面条件表达式
3. 速度从快到慢: 单表查询速度最快 > 其他是联表操作 > 子查询
"""
# (1)找出平均年龄大于25岁以上的部门
# 1.普通的where 联表查询
select
d.name ,d.id
from
employee e ,department d
where
e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25
# 2.内联查询
select
d.id,d.name
from
employee e inner join department d on e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25
# 3.子查询
# 1.先选出平均年龄大于25岁的部门id
select dep_id from employee group by dep_id having avg(age) > 25;
# 2.根据结果,选出在这个范围中的数据
select name from department where id in(201,202)
# 综合拼接:
select
name
from
department
where
id in(select dep_id from employee group by dep_id having avg(age) > 25)
# (2)查看技术部门员工姓名
# 1.普通的where 联表查询
select
e.name
from
employee e ,department d
where
e.dep_id = d.id and d.name = "技术"
# 2.内联查询
select
e.name
from
employee e inner join department d on e.dep_id = d.id
where
d.name = "技术";
# 3.子查询
# 1.找技术部门对应的id是谁
select id from department where name = "技术"
# 2.通过id找员工姓名
select name from employee dep_id = ?
# 3.综合拼接
select name from employee where dep_id = (select id from department where name = "技术")
# (3)查看哪个部门没员工
# 右联方法:
select
d.name
from
employee e right join department d on e.dep_id = d.id
where
e.dep_id is null
# 子查询:
# (1) 先查询 员工都在哪些部门
select dep_id from employee group by dep_id
# (2) 把不在部门列表中的这个部门找出来
select id from department where id not in ?
# 综合拼接
select id from department where id not in(select dep_id from employee group by dep_id)
# (4)查询大于平均年龄的员工名与年龄
# 假如平均年龄是28岁
select * from 表 where age > 28
# 找平均年龄
select avg(age) from employee
# 综合拼接
select name,age from employee where age > (select avg(age) from employee)
# (5)把大于其本部门平均年龄的员工名和姓名查出来
select * from employee
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id | avg(age)
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 | 10
| 2 | alex | female | 48 | 201 | 11
| 3 | wupeiqi | male | 38 | 201 | 11
| 4 | yuanhao | female | 28 | 202 | 13
| 5 | liwenzhou | male | 18 | 200 | 10
| 6 | jingliyang | female | 18 | 204 | 15
+----+------------+--------+------+--------+
#(1) 先计算各个部门平均年龄
select dep_id,avg(age) from employee group by dep_id;
#(2) 让子查询单独作为一张临时表和employee联表变成一张更大的表
select *
from
employee t1 inner join
(select dep_id,avg(age) age from employee group by dep_id) as t2
on t1.dep_id = t2.dep_id
#(3) 让这张大表,当成一次单表查询;
select *
from
employee t1 inner join
(select dep_id,avg(age) as age from employee group by dep_id) as t2
on t1.dep_id = t2.dep_id
where
t1.age > t2.age
# (6)查询每个部门最新入职的那位员工 # 利用上一套数据表进行查询;
"""
# 每个部门都对应很员工,每个员工都对应一个入职时间,如果这时间最大就代表放入职
"""
# 找每个部门的最大入职时间
select post,max(hire_date) from employee group by post;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | max时间
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | 1
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | 2
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | | 8300.00 | 401 | 1 | 3
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | 4
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | 5
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | 成龙 | 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 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
"""
先把需要连接的字段查出来(最大值),然后在和旧表连接成想要的新表数据,从这个新的大表当中,搜索想要的字段.
"""
select
*
from
employee as t1 inner join
(select post,max(hire_date) as max_date from employee group by post) as t2
on t1.post = t2.post
where
t1.hire_date = t2.max_date
# (7)带EXISTS关键字的子查询
"""
exists 关键字表示存在
如果内层sql能够查到数据,返回真True,外层sql执行查询操作
如果内层sql不能查到数据,返回False ,外层sql 就不执行查询操作;
"""
select * from employee
where exists
(select * from department where id = 200)
总结:
"""
子查询可以单独作为一个子句,也可以作为一个表或者某个字段
一般用在where from select 后面的一个字段
通过查询出来的临时表,可以在和其他表数据进行联接,形成一张更大的表.
然后可以把更大的表当成一次单表查询操作,拿出想要的字段和条件完成任务;
"""