section_3.单表\多表查询

# select .. from .. where .. group by .. having  .. order by .. limit ..

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), #以下是教学部
(‘kxq‘,‘male‘,78,‘20150302‘,‘teacher‘,1000000.31,401,1),
(‘wu‘,‘male‘,81,‘20130305‘,‘teacher‘,8300,401,1),
(‘yh‘,‘male‘,73,‘20140701‘,‘teacher‘,3500,401,1),
(‘liwen‘,‘male‘,28,‘20121101‘,‘teacher‘,2100,401,1),
(‘jingli‘,‘female‘,18,‘20110211‘,‘teacher‘,9000,401,1),
(‘jinxin‘,‘male‘,18,‘19000301‘,‘teacher‘,30000,401,1),
(‘chenglong‘,‘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.判断的符号
= > >= < <= != <> 不等于 
2.拼接条件的关键字
and or not
3.查询范围区间 between
between  小值 and 大值 [小值,大值] 查询两者之间的这个范围所有数据
4.查询某个值在具体某个范围里 in
in(1,2,3,4)
5.模糊查询 like "%" "_" 通配符        
    like "%a"  匹配以a结尾的任意长度的字符串
    like "a%"  匹配以a开头的任意长度的字符串
    like "%a%" 匹配含有a字母的任意长度的字符串
    like "_a"  个数一共是2个字符,必须以a结尾,前面字符随意
    like "a__" 个数一共是3个字符,必须以a开头,后面字符随意
"""

"""group by 字段 对当前字段进行分类 , by后面接什么字段,select 就搜什么字段"""

select sex from employee group by sex;
select post from employee group by post;
-- group_concat 按照分类的形式进行字段的拼接
select group_concat(emp_name) from employee 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;

""" 
按照什么字段进行排序
asc  升序: 从小到大 (默认)
desc 降序: 从大到小
"""

select * from employee order by age #(默认是asc升序)
select * from employee order by age desc #(默认是desc 倒序)

"""limit m,n m代表从第几条数据进行查询,0代表第一条,n代表的查询几条"""

select * from employee limit 0,5  -- 从第一条数据开始搜, 搜5条数据
select * from employee limit 5,5  -- 从第六条数据开始搜, 搜5条数据

-- 只搜索一条数据
select * from employee limit 1
-- 只搜索3条数据
select * from employee limit 3
-- 搜索这个表中最后一条数据
select * from employee order by id desc limit 1

select * from employee where emp_name regexp ".*on$"; # .*? 这个?号 mysql 不识别
select * from employee where emp_name regexp "程";
select * from employee where emp_name regexp "程.*金";

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
(‘eg‘,‘male‘,18,200),
(‘kxq‘,‘female‘,48,201),
(‘lin‘,‘male‘,38,201),
(‘wang‘,‘female‘,28,202),
(‘liwen‘,‘male‘,18,200),
(‘jl‘,‘female‘,18,204);

"""
department
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
employee
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | eg         | male   |   18 |    200 |
|  2 | kxq        | female |   48 |    201 |
|  3 | lin        | male   |   38 |    201 |
|  4 | wang       | female |   28 |    202 |
|  5 | liwen      | male   |   18 |    200 |
|  6 | jl         | female |   18 |    204 |
+----+------------+--------+------+--------+
"""

# 内连接 : (inner join ) -> 两表或者多表满足条件的所有数据查询出来(两表之间的共有数据)

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

select * from employee inner join department on employee.dep_id = department.id;

select * from employee as e inner join department as d on e.dep_id = d.id;

select * from employee e inner join department d on e.dep_id = d.id;

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 employee left join department on employee.dep_id = department.id;
-- 2.右连接(右联查询 right join ) 以右表为主,左表为辅,完整查询右表所有数据,左表没有的补null
select * from employee right join department on employee.dep_id = department.id;
-- 3.全连接
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) sql语句当中又嵌套了另外一条sql语句,用()括号抱起来,表达一个整体
    (2) 一般应用在from 字符后面(表达一张表),where 子句后面(表达一个条件)
    (3) 查询速度从快到慢 : 单表查询 -> 联表速度 -> 子查询
"""

select distinct depart_id from employee;

# 一.找出平均年龄大于25岁以上的部门

# 二.查看技术部门员工姓名

# 三.查看哪个部门没员工

# 四.查询大于平均年龄的员工名与年龄

-- 如果平均年龄是25;
select name,age from employee where age > 25
-- 计算平均年龄
select avg(age) from employee
-- 综合拼接
select name,age from employee where age > (select avg(age) from employee);

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

-- 1.先计算各部门平均年龄是多少
select dep_id,avg(age) as 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

# 六.查询每个部门最新入职的那位员工  # 利用上一套数据表进行查询;

# 七.带EXISTS关键字的子查询