MySQL多表查询

单表查询

mysql > select * from tb_dept,tb_emp;      #查询两个表的所有信息
mysql > select id,name from master.master_tab ;   #查询指定列

笛卡尔积:是指在数学中,两个集合的所有组合情况,就相当与把A集合中的每一条数据拿出来和B集合组合。

总数据量=A集合的数据量* B集合的数据量

-- 多表查询:直接在from之后跟上多张表就可以了,多张表之间使用逗号分隔
-- 在多表查询时,需要消除无效的笛卡尔积,只保留表关联部分的数据
-- 在SQL语句中,只需要给多表查询加上连接查询的条件即可去除无效的笛卡尔积
select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;

分类

1.内连接：相当于查询表A和表B，这两张表相交的部分

2.外连接

* 左外连接：是以左表为基准，查询左表当中所有的数据（包括两张表交集部分的数据）

* 有外连接：是以右表位基准，查询右表所有的数据（包括两两张表交集部分数据）

内连接

隐式内连接

select  字段列表   from   表1 , 表2   where  条件 ... ;

显式内连接

select  字段列表   from   表1  [ inner ]  join 表2  on  连接条件 ... ;

-- =============================内连接==============================
-- A.查询员工的姓名,及所属的部门名称(隐式内连接实现)
-- 隐式内连接:select 字段列表  from  表1,表2  where  条件...;
-- 提问:刚才所插入进来的员工表的测试数据一共有17条,为什么查询出来的结果只有16条呢?
-- 因为第17条员工的数据它是没有分配部门的,既然没有分配部门,说明这条记录它和部门表是没有关系的
-- 内连接查询的是两张表交集部分的数据,即A表当中没有和B表产生关联的数据是查询不出来的
select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;
select tb_emp.name, tb_dept.name  -- 分别查询返回两张表中的数据
from tb_emp,tb_dept  -- 关联两张表
where tb_emp.dept_id = tb_dept.id; -- 连接查询条件消除笛卡尔积
 
-- 在多表查询时,如果表名较长写起来比较繁琐,可以给表起别名
select e.name as 员工姓名 , d.name as 部门名称 from tb_emp as e ,
tb_dept as d where e.dept_id = d.id;
 
-- B.查询员工的姓名,及所属的部门名称(显示内连接实现)
-- 显式内连接:select  字段列表  from  表1  [inner]  join  表2  on  连接条件...;
select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;

外连接

左外连接

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;

右外连接

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;

-- =======================外连接================================
-- A.查询员工表中 所有 员工的姓名,和对应的部门名称(左外连接)
-- 注意看,要查询返回所有员工的姓名
-- 左外连接:select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;
-- 由于是左外连接,因此会完全包含左表的数据,也就是tb_emp员工表的数据
select emp.name, dept.name
from tb_emp as emp
         left join tb_dept as dept on emp.dept_id = dept.id;
 
-- B.查询部门表中  所有  部门的名称,和对应的员工名称(右外连接)
-- 注意看,要查询返回所有部门的名称
-- 右外连接会完全包含右表的数据,即使右表当中有一部分数据和左表没有关联,也会查询出来
select emp.name as 员工姓名, dept.name as 部门名称
from tb_emp as emp
         right join tb_dept as dept on emp.dept_id = dept.id;

子查询

所谓子查询指的就是在SQL语句当中嵌套select查询语句，我们把嵌套的这个select查询语句叫做嵌套查询，也称为子查询。

这条查询语句的查询条件是取决于另外一个查询语句的。

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

根据子查询返回的结果不同，我们将子查询分为四类，根据子查询结果的不同分为：

1. 标量子查询：子查询返回的结果为单个值[一行一列]

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

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

-- ======================子查询===============================
-- 标量子查询:子查询返回的结果是一个单行单列的值
-- A.查询 "教研部" 的所有员工信息
-- 首先明确"教研部"是部门ID
-- a.查询 教研部 的部门ID --- tb_dept
select id from tb_dept where name = '教研部';  #查询结果:2
-- b.再查询该部门ID下的员工信息 --- tb_emp
select * from tb_emp where dept_id = 2;
 
-- 合并以上两条SQL语句,改写成一行
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
 
 
-- B.查询在 "方东白" 入职之后的员工信息
-- a.查询"方东白"的入职时间
select entrydate from tb_emp where name = '方东白';
-- b.再查询大于该入职时间的员工信息
select * from tb_emp where entrydate > '2012-11-01';
 
-- 合并以上两条SQL语句,改写成一行
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');

2. 列子查询：子查询返回的结果为一列，但可以是多行

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

常用的操作符：in 、not in等

-- 列子查询:子查询返回的结果是一列多行
-- Example:查询 "教研部" 和 "咨询部" 的所有员工信息
 
-- a.查询 "教研部" 和 "咨询部" 的部门ID --- tb_dept
-- 方式一:使用or关键字连接多个条件
select id from tb_dept where name = '教研部' or name = '咨询部'; #查询结果:3,2
-- 方式二:in关键字
select id from tb_dept where name in('教研部','咨询部');  #查询结果:3,2
 
-- b.根据部门ID,查询该部门下的员工信息 --- tb_emp
-- 方式一:使用or关键字连接多个条件
select * from tb_emp where dept_id = 3 or dept_id = 2;
-- 方式二:in关键字
select * from tb_emp where dept_id in (3,2);
 
-- 合并以上两条SQL语句,改写成一行
select * from tb_emp where dept_id in (select id from tb_dept where name in ('教研部','咨询部'));

3. 行子查询：子查询返回的结果为一行，但可以是多列

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

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

-- 行子查询:查询返回的结果是一行多列
-- Example:查询与 "韦一笑" 的入职日期及职位都相同的员工信息;
 
-- a.查询 "韦一笑" 的 入职日期 及 职位
select entrydate,job from tb_emp where name = '韦一笑'; #查询结果： 2007-01-01 , 2
 
-- b.查询与其入职日期 及 职位 都相同的员工信息
select * from tb_emp where entrydate = '2007-01-01' and job = 2;
 
-- 合并以上两条SQL语句,改写成一行
select *
from tb_emp
where entrydate = (select entrydate from tb_emp where name = '韦一笑')
  and job = (select job from tb_emp where name = '韦一笑');
-- 该SQL语句出现了多次子查询,性能其实并不高
-- 提问:怎么对这条SQL语句进行优化
-- 启发
select * from tb_emp where entrydate = '2007-01-01' and job = 2;
-- 改造
select * from tb_emp where (entrydate,job) = ('2007-01-01',2);
 
-- 正式改造,改造后只有一条子查询
select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name = '韦一笑');

4. 表子查询：子查询返回的结果为多行多列[相当于子查询结果是一张表]

表子查询指的是子查询返回的结果为多行多列，就相当于子查询返回的结果又是一张表。

子查询返回的结果是多行多列，常作为临时表来使用，这种子查询称为表子查询。

既然是作为一张临时表，就经常会出现在select语句的from之后

常用的操作符：in

-- 表子查询:子查询返回的结果是多行多列,常作为临时表来使用
-- Example:查询入职日期是 "2006-01-01" 之后的员工信息,及其部门名称
 
-- a.查询入职日期是 "2006-01-01" 之后的员工信息
select * from tb_emp where entrydate > '2006-01-01'; #查询到一共有14条记录
 
-- 基于查询到的员工信息,在查询对应的部门信息
-- 把上面这条SQL语句查询返回的结果作为一张临时表来使用
-- b.查询这部分员工信息及其部门名称
-- [表名.*] 就代表这张表的所有信息
-- 这是隐式内连接查询,注意:陈友谅入职时间是2015-03-21,但是并没有查询到陈友谅的信息
-- 因为内连接查询的是两张表交集部分的数据
select emp.*, dept.name
from (select * from tb_emp where entrydate > '2006-01-01') as emp,
     tb_dept as dept
where emp.dept_id = dept.id; #查询到一共有13条记录,少了陈友谅
 
 
-- 改进上述SQL语句,使其能够查询到陈友谅的信息
-- 使用左外连接,使其员工表为左表
-- 因为左外连接是以左表为基准,查询左表当中所有的数据
select emp.*, dept.name
from (select * from tb_emp where entrydate > '2006-01-01') as emp
         left join tb_dept as dept on emp.dept_id = dept.id;

视图

视图：View 对查询语句的封装

作⽤：

1.隐藏内部实现细节

2.封装复杂的查询语句

操作视图就跟操作表⼀样

语法格式：

新建：create view 视图名称 as 查询sql语句

使⽤：select * from 视图名称

删除：drop view 视图名称

视图就是封装了查询语句，所以表中的数据发⽣了改变，那么视图也可以获取最新的数据

-- 视图：封装查询语句
create view v_s2008 as
select user_id from sys_user union select role_id from sys_role;
-- 使⽤视图
select * from v_s2008;
-- 删除视图
drop view v_s2008;

触发器

触发器：满⾜⼀定的条件下，⾃动执⾏指定的SQL语句的结构体

触发器的类型：

1.⾏级触发器（使⽤）

2.表级触发器

触发器的条件：

1.时间：

after | before

2.操作⽅式：

insert | update | delete

6种触发条件

语法格式：

新建：

create trigger 触发器名称 after|before insert|uodate|delete on 表名 for each row

begin

触发执⾏的sql语句;

end；

删除：

drop trigger 触发器名称;

满⾜条件 ⾃动执⾏

获取原表操作的数据：

NEW:新变化的数据对象

OLD:⽼的数据对象

-- 触发器：满⾜条件⾃动执⾏指定SQL语句
delimiter $
create trigger tri_s2008
after insert on t_stu2008
for each row
begin
 insert into t_stulog2008(info,ctime) values(concat('新增了学⽣信
息：',NEW.id),sysdate());
end$
-- 测试触发器
delimiter ;
insert into t_stu2008(id,name,sex,ctime) values(3,'李奔','男',now());
select * from t_stulog2008;
-- 删除触发器
drop trigger tri_s2008;