Mysql基础-多表查询
文章目录
- Mysql基础-多表查询
- 1 多表关系
- 1.1 一对多
- 1.2 多对多
- 1.3 一对一
- 2 多表查询概述
- 2.1 多表查询分类
- 3 内连接
- 4 外连接
- 5 自连接
- 6 联合查询-union union all
- 7 子查询
- 7.1 标量子查询
- 7.2 列子查询
- 7.3 行子查询
- 7.4 表子查询
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;