数据库的联表查询

多表查询和子查询

多表查询和子查询是解决复杂查询问题的两种常用方法。

【1】子查询

• 就相当于是我们在一步步解决问题

• 将一条SQL语句的查询结果+括号当做另一条SQL语句的查询条件

-- 子查询

select * form * where *;

select * from * where (select * from * where *;);

​

【2】多查询

• 先将多张表拼接到一起 形成一张大表 然后基于单表查询回去数据

# eg:以昨天的员工表和部门表为例 查询dream所在的部门名称

# 连表操作

# 先将员工表和部门表按照某个字段拼接到一起

# 基于单表查

【3】数据准备

我先删除表

-- 显示本数据库中的所有表

show tables;

-- 删除表

drop table b1;

drop table b2;

drop table b3;

drop table b4;

drop table b5;

drop table b6;

drop table course;

drop table course_student;

drop table employee;

drop table employees;

drop table salary;

drop table department;

drop table student1;

( 1 ) 创建表

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


# 创建外键的方式有两种
# 一种是物理外键
# 一种是逻辑外键

（ 2 ）插入数据

insert into dep values
("300","王者荣耀模块"),
("301","和平精英模块"),
("302","cf模块"),
("303","QQ飞车模块"),
("304","LOL模块");

​
insert into emp(name,sex,age,dep_id) values
("xiao","male",26,300),
("mao","female",18,301),
("jing","male",38,302),
("bai","male",18,303),
("zhi","male",28,304),
("yi","male",18,305);

【4】子查询案例

• 获取员工mao所在的部门ID

  # 先查 mao 所在的部门ID
  select dep_id from emp where name = 'mao';
  
  ​
  # 根据部门ID查询到部门名称
  select name from dep where id=301;
  
  ​
  # 合为一体
  select name from dep where id = (select dep_id from emp where name = 'mao');

  

【5】多表查询

（ 1 ） 拼表的四种情况

• inner join：内连接

  • 只拼接两张表中共有的数据部分

• left join：左连接

  • 左表所有的数据都展示出来，没有对应的项就用null表示

• right join：右连接

  • 右表所有的数据都展示出来，没有对应的项就用null表示

• union：全连接

  • 左右两表的数据都展示出来

（ 2 ）拼表查询

select * from dep,emp;
# # 左边所有的部门名称都有四份
​
# 优化
select * from dep,emp where
dep.id = emp.dep_id;

【6】四种连接方式

【1】内连接

• 只拼接两张表中共有的数据部分

语法：

select *（列名/列表）
from 表1
inner join 表2 on 表1.列名 = 表2.列名;

-- 内链接 一定要在 on 后面加条件进行筛选
 select  * 
 from scores
 inner join school on scores.student_id = school.id;


create view scores_school as
select *
from scores,school
;

【2】左连接

• 左表所有的数据都展示出来，没有对应的项就用null表示

语法：

select *
from 表1
left join 表2
on 表1.列 = 表2.列;


select * 
from school
left join scores on school.id=scores.student_id ;  

【3】右连接

• 右表所有的数据都展示出来，没有对应的项就用null表示

语法：

select *
from 表1
right join 表2
on 表1.列 = 表2.列;

 select  * 
 from scores
 right join school on scores.student_id = school.id;

【4】全连接

• 全连接返回左表和右表中的所有行，如果没有匹配的行，则对应的列值为NULL。

语法：

select *
from 表1
left join 表2
on 表1.列 = 表2.列

union

select *
from 表1
right join 表2
on 表1.列 = 表2.列;


select * from school left join scores on school.id  = scores.student_id

union

select * from school right join scores on school.id  = scores.student_id;

​

【6】查询案例

先将两个表用内连接起来

select * from scores
inner join school
on scores.student_id = school.id;



select group_concat(scores.course,':' ,school.course,':',scores.age)
from scores
inner join school
on scores.student_id = school.id
group by school.lecturer
having avg(scores.age) > 30;