子查询与表联结

---

子查询

当需要的信息不在一个表中，如果使用简单的查询语句不得不逐条查询

-- (1) 检索包含物品TNT2的所有订单的编号
select order_num from orderitems where prod_id = 'TNT2';
-- (2) 检索具有前⼀步骤列出的订单编号的所有客户的ID
select cust_id from orders where order_num IN (20005,20007);
-- (3) 检索前⼀步骤返回的所有客户ID的客户信息
select cust_name,cust_contact from customers where cust_id in (10001,10004);

而MySQL支持where里面嵌套其他的select语句，即进行子查询

select cust_name,cust_contact
from customers
where cust_id in (select cust_id
	from orders
	where order_num IN (select order_num
	from orderitems
	where prod_id = 'TNT2'));

使用子查询的另一种方式是创建计算字段

-- 假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中
-- (1) 从customers表中检索客户列表
select cust_id,cust_name from customers ;
-- (2) 对于检索出的每个客户，统计其在orders表中的订单数⽬
select count(*) as orders from orders where cust_id = 10001;

select选取的字段本身也可以替换为子查询的内容

select cust_id,cust_name,
 (select count(*)
 from orders
 where orders.cust_id = customers.cust_id) as orders
from customers
order by cust_name;

---

关系型数据库

关系型数据库将重复的信息分成不同的表进行存储，可以更有效地进行存储和管理数据
因此，关系型数据库的可伸缩性要远高于非关系型数据库

---

表关系

一对一关系与外键

一对一的表关系代表一个表中的数据对应着另一个表中的唯一数据

id	name	age
1	张三	14
2	李四	15

yid	school	birthday
1	阳光中学	2006-7-23
2	第一中学	2005-6-14

上面两个表中的id与yid一一对应，这种对应关系称为一对一关系

外键：在一个表中，定义一个字段，该字段是另一个表的主键
外键的实现方式分为两种：物理外键、逻辑外键

1. 物理外键
   物理外键是在定义字段时，用SQL语句将其与另一个表的某个字段进行强关联
2. 逻辑外键
   逻辑外键定义的字段并没有实现强关联，是通过程序的方式让两个字段相关

两种实现方式各有利弊，物理外键虽然易于实现，但是某字段一旦定义为了外键
要对其强关联的表中某行进行删除时会出现SQL报错，必须要先删除外键
而逻辑外键虽然在删除上避免了这种问题，但是实现起来不方便

---

一对多关系

一对多的表关系代表一个表中的数据对应着另一个表中的多条数据

id	type	name
1	1	手机
2	2	电脑

id	typeid	name
1	1	小米手机
2	1	苹果手机

上面第一个表中的type对应第二个表中的多个typeid，这种对应关系为一对多关系

---

多对多关系

多对多的表关系，通过举例

classtid	teacher
1	胡老师、严老师、赵老师
2	胡老师、严老师
3	严老师、赵老师

teacherid	name	classid
1	胡老师	1、2
2	严老师	1、2、3
3	赵老师	1、3

上面的一个老师对应了多个班级id，而一个班级id也对应了多个老师执教
这种对应关系即为多对多关系

---

表联结查询

内部联结

表联结是一种查询机制，当需要查询的信息在多个表中，可以将表进行联结查询

1. where

用where将两个表的信息进行关联并进行字段的查询

select name,address,school from student,info where student.id = info.id;

2. inner join … on …

用join可以实现与where一样的结果，inner join+表名，on+上述where里的条件

select name,address,school from student inner join info on student.id=info.id;

需要注意的是，如果进行表联结查询时没有加where条件，则SQL会将两个表的行
进行笛卡尔积的运算，在数据量庞大的情况下会造成严重的结果

---

自联结

一个表自己和自己进行联结查询即称为自联结，自联结也是内部联结

假如现在需要查询张三学校里的其他学生的名字与年龄，用子查询的做法如下

select name,age 
from student 
where school = (select school from student where name = "张三");

这样做虽然没有问题，但是子查询的效率很低，因此需要自联结的方法替代

select s1.name,s1.age 
from student as s1,student as s2 
where s1.school=s2.school and s2.name="张三";

上述语句的含义是，同时选取两张student表，记为s1与s2，条件为将s2表里的名字
选取为张三，并让s1表和s2表的学校相等，这样便达到了查找张三学校的目的
同时前面字段要选取s1表里的name与age字段，因为s2表中此时只有张三这一行

除了使用where进行自联结，我们还可以用join实现

select s1.name,s1.age 
from student as s1 
inner join student as s2 
on s1.school=s2.school
where s2.name="张三";

注意，自联结也是表联结查询，如果没有加上where条件限制，一样会出现笛卡尔积

---

外部联结

上面用的where与inner join实现的是内部联结
内部联结是将两个表中相关联的数据进行的联结，而不关联的数据会不显示
而外部联结是以一个表为基准，去关联右侧的表进行联结，未关联的数据显示为null

select major.name,count(student.name) as nums
from major
left join student
on major.mid=student.major
GROUP BY major.name;

以上语句将major表作为基准去关联student表，显示的结果为

可以注意到即使密码学字段与学生表没有关联，但还是可以显示出该字段

left join与right join的原理是一样的，上述语句与下面的语句实现的效果是一模一样的

select major.name,count(student.name) as nums
from student
right join major
on major.mid=student.major
GROUP BY major.name;

---

组合查询UNION

union的几个使用条件：

• union必须由两条或以上的select语句组成，语句之间用union隔开
• union中的查询必须有着相同的列，表达式或者聚集函数
• 列数据类型必须兼容，类型不必完全相同，但必须可以进行隐式转换

select id,name,major,age from student where age<20 
union 
select id,name,major,age from student where age=22;

union会自动过滤重复的行，如果需要显示重复的行，可以使用union all

select id,name,major,age from student where age<20 
union all
select id,name,major,age from student where age=22;

如果需要对结果进行排序，可以在结尾的select语句加上order by

select id,name,major,age from student where age<20 
union 
select id,name,major,age from student where age=22 order by id;

这样做看似是只对结尾的查询内容做排序，实际上是对全部的查询内容进行的排序

---