Mysql基础-多表查询

1 多表关系

  项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多(多对一)
• 多对多
• 一对一

1.1 一对多

• 案例: 部门 与 员工的关系

• 关系: 一个部门对应多个员工，一个员工对应一个部门

• 实现: 在多的一方建立外键，指向一的一方的主键

  

1.2 多对多

• 案例: 学生 与 课程的关系
• 关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
• 实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

create table student(
	id int auto_increment primary key comment '主键ID',
	name varchar(10) comment '姓名',
	no varchar(10) comment '学号'
) comment '学生表';
insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊',
'2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');

create table course(
	id int auto_increment primary key comment '主键ID',
	name varchar(10) comment '课程名称'
) comment '课程表';
insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') ,
(null, 'Hadoop');

create table student_course( 
    id int auto_increment comment '主键' primary key,
	studentid int not null comment '学生ID',
	courseid int not null comment '课程ID',
	constraint fk_courseid foreign key (courseid) references course (id),
	constraint fk_studentid foreign key (studentid) references student (id)
)comment '学生课程中间表';
insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),
(null,2,3),(null,3,4);

1.3 一对一

• 案例: 用户 与 用户详情的关系
• 关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
• 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

create table tb_user(
	id int auto_increment primary key comment '主键ID',
	name varchar(10) comment '姓名',
	age int comment '年龄',
	gender char(1) comment '1: 男 , 2: 女',
	phone char(11) comment '手机号'
) comment '用户基本信息表';
create table tb_user_edu(
	id int auto_increment primary key comment '主键ID',
	degree varchar(20) comment '学历',
	major varchar(50) comment '专业',
	primaryschool varchar(50) comment '小学',
	middleschool varchar(50) comment '中学' ,
    university varchar(50) comment '大学',
	userid int unique comment '用户ID',	--注意这里用了unique
	constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';

2 多表查询概述

• 概述：指从多张表中查询数据

• 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

• 基本语法：

  select * from 表1,表2,... 但是这样会产生笛卡尔积 可以加入条件消除笛卡尔积

  select * from 表1,表2 where ... 消除笛卡尔积

2.1 多表查询分类

• 连接查询

  内连接：相当于查询A、B交集部分数据

  外连接：

  • 左外连接：查询左表所有数据，以及两张表交集部分数据
  • 右外连接：查询右表所有数据，以及两张表交集部分数据

  自连接：当前表与自身的连接查询，自连接必须使用表别名

• 子查询

  

3 内连接

内连接查询语法：

• 隐式内连接

  SELECT 字段列表 FROM 表1,表2 WHERE 条件 ...;

• 显示内连接

  SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

内连接查询的是两张表的交集部分

-- 内连接演示
-- 查询每一个员工的姓名以及关联的部门的名称 分别使用隐式、显示内连接
SELECT emp.name,dept.name from emp,dept where emp.dept_id = dept.id;
SELECT e.name,d.name from emp e,dept d where e.dept_id = d.id;

SELECT emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;

4 外连接

外连接查询语法：

• 左外连接

  SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;

  相当于查询表1（左表）的所有数据 包含表1和表2交集部分的数据

• 右外连接

  SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;

  相当于查询表2（右表）的所有数据 包含表1和表2交集部分的数据

-- 外连接演示
-- 查询emp表的所有数据 和对应的部门信息（左外连接）
SELECT emp.*,dept.name from emp LEFT outer JOIn dept ON emp.dept_id = dept.id;
-- 查询dept表的所有数据 和对应的员工信息（右外连接）
SELECT dept.*,emp.* from emp RIGHT outer JOIn dept ON emp.dept_id = dept.id;

5 自连接

自连接查询语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

自连接查询，可以使内连接查询 也可以是外连接查询。

其实就是看成两张表就完事，两张表能做的，它也能做。不过别忘记给两张表都起别名，在FROM处起别名

6 联合查询-union union all

对于union查询 就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...

UNION [ALL]

SELECT 字段列表 FROM 表B ...;

-- 1. 将薪资低于5000的员工 和年龄大于 50岁的员工全部查询出来
select * from emp where salary < 5000
union all
select * from emp where age > 50;

UNION ALL 是直接将所有查询结果合并，UNION是将所有结果合并后，再将重复结果去除。

联合查询属于纵向合并，需要多个查询结果列数以及字段类型是相同的

7 子查询

• 概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询

  SELECT * FROM t1 WHERE column1 = (SELECT coulumn1 FROM t2);

  子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个

• 根据子查询结果不同，分为：

  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一行）
  • 表子查询（子查询结果为多行多列）

7.1 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为 标量子查询

常用的操作符：= <> > >= < <=

-- 标量子查询
-- 1. 查询销售部的所有员工信息
select id from dept where name = "销售部";
select * from emp where dept_id = (select id from dept where name = "销售部");

-- 2. 查询在“房东白”入职之后的员工信息
select entrydate from emp where name = '房东白';
select * from emp where entrydate > (select entrydate from emp where name = '房东白') ;

7.2 列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为 列子查询。

常用操作符：IN、NOT IN 、ANY 、SOME、 ALL

-- 查询 销售部 和 市场部 的所有员工信息
select id from dept where name = '销售部' or name = '市场部';
select * from emp where id in (select id from dept where name = '销售部' or name = '市场部');

-- 查询比财务部所有人工资都高的员工信息
select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');
select * from emp where salary > ALL(select salary from emp where dept_id = (select id from dept where name = '财务部'));


-- 查询比研发部其中任意一人工资高的员工信息
select salary from emp where dept_id = (select id from dept where name = '研发部');
select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name = '研发部'));

7.3 行子查询

子查询返回的结果是一行（可以使多列），这种子查询称为行子查询

常用操作符：= 、<>、IN 、 NOT IN

-- 1 查询与“张无忌” 的薪资及直属领导相同的员工信息;
select salary ,managerid from emp where name = '张无忌';
select * from emp where (salary ,managerid) = (select salary ,managerid from emp where name = '张无忌');

7.4 表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询

常用的操作符：IN

-- 1 查询与“鹿杖客” ， “送元钱”的职位和薪资相同跟的员工信息
select job,salary from emp where name = "鹿杖客"  or name = "松原桥";
select * from emp where (job,salary) in (select job,salary from emp where name = "鹿杖客"  or name = "松原桥");

-- 2 查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
select * from emp where entrydate > '2006-01-01';
select * from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;