linux12 -MYSQL数据库 --＞07数据库多表查询

一、重要关键字的执行顺序

# 书写顺序
select id,name from emp where id > 3;
# 执行顺序
from
where
select
虽然执行顺序和书写顺序不一致，按照书写顺序的方式写sql
	select * 先用*号占位
	之后去补全后面的sql语句
	最后将*号替换后你想要的具体字段

1、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 '%0%';

# 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;

2、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

# 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;

# 7，查询每个人的年薪，12薪
select name,salary *12 from emp;
#补充 as语法不单单可以给字段起别名 还可以给表临时起别名
select emp.id,emp.name from emp;
select t1.id,t1.name from emp as t1; #别名

3、分组注意事项

# 关键字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;

4、having分组之后的筛选条件

having的语法和where是一致的，只不过having是在分组之后进行的过滤操作，即having是可以直接使用聚合函数的

# 统计各部门年龄在30岁以上的员工平均工资并保留平均薪资大于1万的部门
select post,avg(salary) from emp
		where age>30
		group by post
		having avg(salary)>10000;

5、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; 可以

6、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;

7、limit 限制展示条数

select * from emp;
针对数据过多的情况，我们通常都是做分页处理
select * from emp limit 3;  # 只展示3条数据
select * from emp limit 0,5;
select * from emp limit 5,5;

8、正则 regexp

select * from emp where name regexp '^j.*(n/y)$';

二、多表操作

1、前期表准备

# 建表
create table dep(
id int,
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),
('oven','male',18,203),
('jerry','female',18,204);

2、表查询

select * from dep,emp; # 结果是笛卡尔积

select * from emp,dep where emp.dep_id = dep.id; # 不建议用

mysql也知道，你在后面查询数据过程中，肯定会经常用到拼表操作，所以特地开设了对应的方法
	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;

3、子查询

子查询就是我们平时解决问题的思路
	分步骤解决问题 第一步   第二步...
将一个查询语句的结果当做另外一个查询语句的条件去用

# 查询部门是技术或者人力资源的员工信息
	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 = '人力资源');	

4、知识点补充

# 查询平均年龄在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);

5、总结

表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一张虚拟表跟其他表关联

多表查询就2种方式
	先拼接表再查询
	子查询一步一步来
Powered by .NET 5.0 on Kubernetes

# 查询平均年龄在25岁以上的部门名称 
# 连表查询
mysql> select dep.name from emp inner join dep on emp.dep_id=dep.id group by dep.name having avg(age) >25;
# 子查询
mysql> select name from dep where id in (select dep_id from emp group by dep_id having avg(age) >25);


# 关键字exists(了解)
正确才返回ture，反之false

表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一张虚拟表跟其他表关联

多表查询就2种方式
	先拼接表再查询
	子查询一步一步来
Powered by .NET 5.0 on Kubernetes

# 查询平均年龄在25岁以上的部门名称 
# 连表查询
mysql> select dep.name from emp inner join dep on emp.dep_id=dep.id group by dep.name having avg(age) >25;
# 子查询
mysql> select name from dep where id in (select dep_id from emp group by dep_id having avg(age) >25);


# 关键字exists(了解)
正确才返回ture，反之false

三、mysql数据库多表查询

1、多表查询分类

1》多表连接查询
2》复合条件连接查询
3》子查询
123

2、多表查询准备

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


#查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+

mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+

mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+

   #表department与employee

3、多表连接查询

##!!!!重点：外链接语法

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;
12345

1、 交叉连接（ 笛卡尔积查询 ）

不适用任何匹配条件,生成笛卡尔积

1）笛卡尔积查询介绍

#select * from 表一,表二
   上面这个语句的执行是想查出来两张表中所有的数据,但是发现执行结果多出来好多数据,当联表查询的时候,mysql不知道如何连接两孩子那个表的

2）笛卡尔积查询使用

select * from emp,dep;      #将两张表拼在一起

select * from emp,dep where dep.id = emp.dep_id;    #找到两张表中对应的关系记录

select emp.name from dep,emp where dep.id = emp.dep_id and dep.name='技术';         #拿到筛选后的技术部门数据中emp.name的数据

mysql> select * from employee,department;
+----+------------+--------+------+--------+------+--------------+
| id | name       | sex    | age  | dep_id | id   | name         |
+----+------------+--------+------+--------+------+--------------+
|  1 | egon       | male   |   18 |    200 |  200 | 技术         |
|  1 | egon       | male   |   18 |    200 |  201 | 人力资源     |
|  1 | e