mysql3_查询

# ### mysql查询
"""注意: select后面跟的值要用 , 隔开,group by后面的多个值也要用 , 隔开"""
"""分组后组里有多项的字段,用group_concat括起来"""
"""提到"各"就要记得用分组,各部门,各班级..."""
# sql 查询语句的完整语法
'''select from where group by having order by limit'''

# 一.where 条件的使用
"""功能:对表中的数据进行筛选过滤"""

语法:
1.= > < >= <= 不等于(!= <>)(都是不等于),判断的符号:

2.and or not 拼接条件的关键字

3. between and 查询的区间范围值 between
between 小值 and 大值 [小值,大值] 查询两者之间这个范围的所有数据(两个边界值都能取到)

4. in 查询具体某个值的范围
in(1,-9,-10,"a") 指定范围

5. like "%" 模糊查询 通配符
like "%a" 匹配以a结尾的任意长度的字符串
like "a%" 匹配以a开头的任意长度的字符串
like "%a%" 匹配含有a字母的任意长度字符串
like "_a" 个数一共2个字符,必须以a结尾,前面这个字符随意
like "a__" 个数一共3个字符,必须以a开头,后面这个两字符随意
6.distinct() 自动去重,将需要去重的数据塞到括号里就可以去重

# ### part1 单表查询

# 单表练习

#创建表
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)
;

# 练习:where

# (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) null关键字 在搜索的时候,要用is进行判定,不能用 ="""
# 查询 post_comment 是空的NULL 所有数据
select * from employee where post_comment = NULL 数据是空,搜索不到
select * from employee where post_comment is NULL
select * from employee where post_comment is not NULL

update employee set post_comment = "" where id = 1
select * from employee where post_comment = '';

# (5) 关键字 in 的查询
# 查询收入是 3500 或 4000 或者 5000 或者 8300 所有员工姓名和收入
select emp_name,salary from employee where salary=3500 or salary=5000 or salary=8300 or salary=4000;
# 用in优化,在小括号里面写上可能的值
select emp_name,salary from employee where salary in (3500,5000,8300,4000);
# 不在括号中的值,搜索出来
select emp_name,salary from employee where salary not in (3500,5000,8300,4000);

# (6) 关键字 like 模糊查询
# (1) % 通配符
select emp_name,age,post from employee where emp_name like "%on";
# (2) _ 通配符
select emp_name,age,post from employee where emp_name like "a_e_";

# (7) concat
select concat("姓名:",emp_name,"薪资:",salary) as aaa from employee;
# concat_ws(拼接的符号,参数1,参数2,参数3 ... )
select concat_ws(" : ",emp_name,salary) as bbb from employee;
# 可以在sql中使用四则运算(+ - * /)
select concat_ws(" : ",emp_name, salary * 12 ) as bbb from employee;

# 二.group by 子句 分组,分类
"""group by 对数据进行分类, by 后面接的字段,就是select要搜索的字段"""
"""by 什么,搜什么"""
select sex from employee group by sex;
select post from employee group by post;
# group_concat 按照分组形式进行字段的拼接
"""将emp_name姓名按性别分类,括号里面写姓名(有多个名字,所以用group_concat括起来),
select 后面跟的值的顺序自己看需求写,会按你写的顺序组成表格"""
select group_concat(emp_name),sex from employee where id>1 group by sex;
 

# 聚合函数
# 统计总数 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

# 一般来说 使用时 分组 + 聚合函数 配合使用
# 1. 查询部门名以及各部门的平均薪资
"""先找到部门分组,再找部门分组里的平均值"""
"""其实select后面的两个值换位置也可以,会按你写的顺序组成表格"""
select post , avg(salary) from employee group by post;
# 2. 查询部门名以及各部门的最高薪资
select post , max(salary) from employee group by post;
# 3. 查询部门名以及各部门的最低薪资
select post , min(salary) from employee group by post;
# 4. 查询公司内男员工和女员工的个数
"""先找性别分组,再找性别分组中的人数"""
select sex,count(*) from employee group by sex
# 5. 查询部门名以及部门包含的所有员工名字
select group_concat(emp_name) , post from employee group by post
# select emp_name,post from employee group by post,emp_name 没意义啊

# 三.having 查询数据之后在进行过滤,一般是配合group by使用, 主要用分组后过滤
"""having和group by合用,having放在后面,where和group by合用,where放前面"""
# 找出各部门平均薪资大于10000的所有部门及其平均薪资
select post,avg(salary) from employee group by post having avg(salary) > 10000;
# 1.查询各岗位内包含的员工个数小于2的岗位名,员工名,个数
select post,group_concat(emp_name),count(*) 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的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000
select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000

# 四.order by 排序 , 按照什么字段进行排序
# 默认值asc 升序排序
# 按照desc 降序排序
select * from employee order by age (默认升序)
select * from employee order by age desc (降序)

# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
"""order by 后面跟多个条件,就是先按什么条件排序,后按什么条件排序,条件之间用 , 隔开"""
select * from employee order by age,hire_date desc
# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc
# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc

# 五.limit 限制查询的条数 (数据分页)
"""limit语句一般放到命令的最后"""
limit m,n m代表从第几条开始查询,n代表查询几条 m=0 代表的是第一条
select * from employee limit 0,5
"""从第一条开始查,查5条,而不是从第一条开始查到第4条或者第5条"""
"""0是第一条"""
select * from employee limit 5,5 从第六条开始查,查5条
# 只查询一条数据
select * from employee limit 1
# 想要瞬间得到数据表中,最后一条数据
"""先按id降序排列,再找出第一条数据"""
select * from employee order by id desc limit 1
# 拿到最后三条数据
select * from employee order by id desc limit 3

# 六.regexp (了解) 后面跟正则表达式,可用来查询数据 (不推荐使用,不好用效率不高)
select * from employee where emp_name regexp ".*on$" # .*? 的?号不识别
select * from employee where emp_name regexp "^程";
select * from employee where emp_name regexp "^程.*金";

 

# ### part2 多表查询

# 多表练习:
#建表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

# 内连接:(内联查询 inner join ) : 两表或者多表满足条件的所有数据查询出来[两个表之间共同具有的数据]

"""
# 两表联表查询
select 字段 from 表1 inner join 表2 on 条件
# 多表联表查询
select 字段 from 表1 inner join 表2 on 条件 inner join 表3 on 条件

where写法
# 也可以select xx from 表1,表2 一次性搜两表 where 条件(这个条件可以分别是两个表里的)

"""

# 基本语法 inner join 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) 所有数据全都合并起来(其中包含自动去重)
如果不用去重,就用union all,并且执行起来,union all要比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

# ### part3 子查询
"""
子查询: 嵌套查询
(1) 子查询是查询的语句当中又嵌套的另外一条sql语句,用括号()包起来,表达一个整体
(2) 一般应用在from 子句后面表达一张表,或者 where 子句后面表达一个条件
(3) 速度从快到慢 单表查询速度最快 -> 联表查询 -> 子查询
"""

# (1)找出平均年龄大于25岁以上的部门
# 普通的where 相当于内联查询
select
d.id,d.name
from
employee e,department d
where
e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25;

# (2) inner join #不是查平均年龄大于25岁的部门和平均年龄吗
select d.name,avg(e.age)
from employee as e inner join department as d on e.dep_id=d.id
group by d.name
having avg(e.age)>25;


# (3) 子查询
"""子查询可以将查询到的子表还可以inner join ..on到另一个表上进行查询"""
# 1.先选出平均年龄大于25岁的部门id
select dep_id from employee group by dep_id having avg(age)>25
# 2.通过部门id,找部门名字
select id,name from department where id in (?)
# 3.综合拼接:
select id,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.inner join 实现
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 where employee.dep_id = ?

# 3.综合拼接
select name from employee where employee.dep_id = (select id from department where name = "技术")

# (3)查看哪个部门没员工

# 联表写法
select
d.id,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 => (200,201,202,204)
# 2.把不在部门列表中的数据找出来
select from department where id not in (1)
# 3.综合拼接
select id,name from department where id not in (select dep_id from employee group by dep_id)

# (4)查询大于平均年龄的员工名与年龄
# 假设平均年龄是18岁
select name,age from employee where age > ?
# 找平均年龄
select avg(age) from employee
# 综合拼装
select name,age from employee where age > (select avg(age) from employee)

# (5)把大于其本部门平均年龄的员工名和姓名查出来

# 1.先计算平均年龄
select dep_id,avg(age) from employee group by dep_id

 

 # 2.把子查询查出来的数据和employee作拼接,联合成一张更大的表,做一次单表查询;

select
*
from
employee as t1 inner join (1) as t2 on t1.dep_id = t2.dep_id

# 3.综合拼接
select
*
from
employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id

# 4.把额外的比较的条件加进去
select
*
from
employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id
where
t1.age > t2.avg_age

# (6)查询每个部门最新入职的那位员工 # 利用上一套数据表进行查询;
# 1.找每个部门最大的入职时间
select post,max(hire_date) as max_date from employee group by post

# 2.把子查询查出来的数据和employee联合成一张更大的表,做一次单表查询
select
from
employee as t1 inner join (1) as t2 on t1.post = t2.post
where
t1.hire_date = t2.max_date

# 3.综合拼接
select
t1.emp_name,t1.hire_date
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 employee where id = 1)
如果表里面有id=1的数据,这句话就相当于 select * from employee
反之如果没有id=1的数据,外层sql不执行,返回空


"""
子查询总结:
子查询可以单独作为一个子句,也可以作为一个表或者某个字段
一般用在from where select 子句后面
通过查询出来的临时表,可以跟任意的表重新拼接,组成更大的表,在通过筛选达成自己的目的
"""