多表查询
多表关系
-
一对多
一个部门可以对应多个员工，但一个员工只能对应一个部门。
此时在多的一方建立外键。如上面的例子，就是在员工方建立外键连接部门方。
-
多对多
一个学生可以选修多门课程，同时一门课程也可以被多名学生选择。
此时建立第三张中间表，中间表内至少包含两个外键，分别关联两方主键。
-
一对一
用户和用户信息之间的关系。
多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另外一张表中，提升操作效率。
此时在任意一方加入外键，关联另外一方的主键，并设置外键为唯一的（UNIQUE），保证一个字段只能对应一个他表字段。
多表查询
当直接查询两个及以上的表时，会出现n*m条结果。
select * from table1,table2;
这种情况被称为笛卡尔积，即在数学中两个集合AB的所有组合情况。
多表查询时，需要消除无效的笛卡尔积。
select * from table1,table2 where table1.a = table2.b;
此时将两个表之间相关联的数据作为限定条件，即可去除无效数据。
多表查询的步骤
- 确定要查询的表以及内容
- 确定连接条件
- 名字太长可以考虑给表起别名。起别名后必须使用，详见select语句执行顺序。
分类
- 连接查询
- 内连接：相当于查询A、B交集部分数据。
- 外连接：
- 左外连接：查询左表所有数据和两张表的交集数据。
- 右外连接：同理
- 自连接：当前表与自身的连接查询，自链接必须使用表别名。
- 子查询
连接查询--内连接
就是查询两张表中交集的部分
比如 查询每一个学生的姓名，以及对应的班主任的生日
这里关联到两张表：学生信息和班主任信息，对于班主任的生日这条信息，学生信息表里是不包含的，这时候就要调用外部的表，即班主任信息表。
语法如下：
//隐式内连接
SELECT 想查询的内容 FROM table1,table2 WHERE 条件...;
//显式内连接
SELECT 想查询的内容 FROM table1 [INNER] JOIN table2 ON 条件...;
连接查询--外连接
左外连接
select 想查询的内容 from table1 left [outer] join table2 on 条件;
包含左侧表的所有内容，即使条件为空仍然显示。
右外连接
select 想查询的内容 from table1 right [outer] join table2 on 条件;
包含右侧表的所有内容，即使条件为空仍然显示。
连接查询--自连接
必须起别名！
SELECT 想查询的内容 FROM table1 别名1 [INNER] JOIN table1 别名2 ON 条件...;
SELECT 想查询的内容 FROM table1 别名1,table2 别名2 WHERE 条件...;
既可以是内连接查询，也可以是外连接。
可以在表内查询自有的关系，比如查询某个员工的领导。
可以把自己的两个别名表视为两个表来进行操作
给想要查询的内容起别名胡直接体现在查询出的表上。
联合查询-union，union all
将多次查询的结果合并，形成新的查询结果集。
SELCT 查询内容 FROM table1 ...
UNION [ALL]
SELECT 查询内容 FROM table2 ...;
和直接在条件中用or或者其他关键字的区别在于，联合查询可以查询不同表的内容，单独用Where进行条件限制只能查询一个表的内容。
- union 和union all展示的方式就是将两个表直接前后拼在一起。
这就导致当查询同一个表的时候，可能会出现某条数据同时满足 多个条件的情况，这时会产生重复。
这时两个词才体现出不同的功能：union会使数据仅出现一次， 而union all无论数据是否出现过，都会展示。
- 对联合查询的多张表，列数必须保持一致，字段类型也需要保持一致。
子查询
概念：SQL语句中嵌套SELECT语句，也叫嵌套查询。
SELECT * FROM table1 WHERE column1 = (SELECT column1 FROM table2);
此处的外部语句可以是INSERT/UPDATE/DELETE/SELECT的任意一个。
根据结果不同，可分类为：
- 标量子查询（结果为单个值）
- 列（一列）
- 行
- 表（多行多列）
根据位置不同，分为
- WHERE之后
- FROM之后
- SELECT之后
标量子查询
嵌套的内容（括号内的内容）返回单个数值
相当于函数的嵌套 f（g(x))，此时g（x）查询到的内容为一个单独的数值
就是把原来分步查询的内容并在一起
列子查询
括号内的内容处在同一列
all（）
any()
some()
#查询比财务部所有人工资都高的员工信息
select * from emp where salary >all(select salary from emp where dept_id =(select id from dept where name = '财务部') );
要查询的主体永远紧跟在select后面。
思路：首先要查询的内容是员工信息，所以要把每个员工的所有信息都打印出来，第一个select后跟*。
此时形成
select * from emp (条件)
接着确定条件：“比财务部的所有人的工资都高的”，也就是要比较的内容是工资，也可以理解为高的对象是工资，因此大于号前面是salary，后面也是salary，且后面的salary也有条件。
where salary >(select salary from emp (条件))
但是工资前面有修饰语，是财务部所有人，因此财务部是新的条件。
where dept_id =(财务部id)
财务部id未知，所以要进行查询：
select id where name = '财务部'
然后套进去就可以了。
行子查询
嵌套的内容有多个，且在同一行
比如 查询的是和a的生日月份以及家乡城市相同的人的所有信息。
select * from people where (birthmonth,hometown)=(1,'上海');
#此处的1和‘上海’都可以被select语句替换掉
select * from people where (birthmonth,hometown)=(select birthmonth,hometown from people where name = 'a');
表子查询
多行多列
常用操作符：IN 表示多选一，满足任一条件即可
select * from emp where (job,salary) in(select job,salary from emp where name = 'a' or name = 'b');
表示选择和a或b职位或薪资相同的人的信息，in表示任意一个条件满足即可，是和all、any、some同类的关键字，而出现多个条件时，可以用二元式匹配的形式来写。
此处in后面的条件中，出现了嵌套的select语句，即子查询，查询的内容是多个人的信息，显然为多行多列，这也就是表子查询的体现。
表子查询的内容既可以作为where后比较的条件，也可以作为想要查询的表，比如：
select e.*,d.* from(select * from emp where entrydate > '2005-01-03') e left join dept d on e.dept_id = d.id;
此处就将要查询的表限定在入职日期在20050103之前的人的信息中，并且由于要连接查询部门信息，选择使用左连接将二者一并展示。前面提到过，要查询的内容是紧跟着select的，因此在最前面的select是同样可以限定被链接过来的表的内容的。
浙公网安备 33010602011771号