数据库-多表设计 多表查询

多表设计

一对多

一对多关系实现：在数据库表中多的一方，添加字段，来关联一的一方的主键

外键约束

-- 创建表时指定
create table 表名(
	字段名    数据类型,
	...
	[constraint]   [外键名称]  foreign  key (外键字段名)   references   主表 (字段名)	
);


-- 建完表后，添加外键
alter table  表名  add constraint  外键名称  foreign key (外键字段名) 
        references  主表(字段名);

一对一

一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率

实现:在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

多对多

实现:建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

案例 : 学生 与 课程的关系

关系 : 一个学生可以选修多门课程，一门课程也可以供多个学生选择

多表查询

多表查询 : 指从多张表中查询数据

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

内连接

隐式内连接： select  字段列表   from   表 1 , 表 2   where   条件 ... ;

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

select tb_emp.name,tb_dept.name from tb_dept,tb_emp where tb_emp.dept_id = tb_dept.id;

select e.name,d.name from tb_dept d,tb_emp e where e.dept_id = d.id;

select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;

外连接

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

右外连接： select  字段列表   from   表 1  right  [ outer ]   join 表 2  on  连接条件 ... ;

select e.name, d.name from tb_emp e left join tb_dept d on e.dept_id = d.id;

select e.name, d.name from tb_emp e right join tb_dept d on e.dept_id = d.id;

子查询

介绍： SQL 语句中嵌套 select 语句，称为嵌套查询，又称子查询。

形式： select  *   from   t1    where  column1 =  ( select  column1   from  t2  … );

子查询外部的语句可以是 insert / update / delete / select 的任何一个，最常见的是 select 。

标量子查询

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

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

select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');

select * from tb_emp where entrydate > (select entrydate from tb_emp where name = 'fdb');

列子查询

子查询返回的结果是一列（可以是多行）

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

select * from tb_emp where dept_id
        in (select id from tb_dept where name = 'jyb' or name = 'cxb');

行子查询

子查询返回的结果是一行（可以是多列）。

常用的操作符： =  、 <> 、 in 、 not  in

select * from tb_emp where (entrydate,job) = 
        (select entrydate,job from tb_emp where name = 'wyx');

表子查询

子查询返回的结果是多行多列，常作为临时表

常用的操作符： in

select * from (select * from tb_emp where entrydate > '2016-01-01') e ,tb_dept d 
        where e.dept_id = d.id;