第十三单元 多表联查和子查询
多表联查
1.一对一
一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。
2.一对多（1方建主表(id为主键字段), 多方建外键字段）
一个实体的某个数据与另外一个实体的多个数据有关联系， 一对多的关系在设计的时候，需要设计表的外键。
创建数据库表
constraint 约束
foreign key就是表与表之间的某种约定的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强。
foreign key语句的式例：FOREIGN KEY(Sno) REFERENCES Student(Sno)
注意：表的外键必须是另一张表的主键
//创建班级表
create table class(id int primary key auto_increment,name varchar(20));
//创建学生表
//constraint： 约束
//foreign key(class_id) 外键，括号里面是当前表的某个字段
//references ： 关联
// class(id) 另一个表，括号里面是另一个表的某个字段
create table student(id int primary key auto_increment,name varchar(20),sex varchar(20),class_id int,constraint foreign key(class_id) references class(id));
//插入班级数据
insert into class values(1,'ceshiban');
insert into class values(2,'kaifa');
//插入学生数据
insert into student values(1,'zhangsan','nan',1);
//联查
//先查括号里面的，查到kaifa班级的id，根据id去student表查到对应class_id的学生
select * from student where class_id=(select id from class where name="kaifa");
补一个外键的概念（默认是约束): 删除主键信息时，当该主键字段值在外键表中存在时，该记录是不能删除的。---要把外键表的相关信息删除之后，才能删除。
多对多（3个表= 2个实体表+1个关系表)
一个实体的数据对应另外一个实体的多个数据，另外实体的数据也同样对应当前实体的多个数据。
一个学生可以有多个老师，一个老师可以教多个学生
解决方案：创建一个中间表，专门用来维护多表之间的对应关系，通常是能够唯一标识出数据的字段（主键）
create table teacher(id int primary key,name varchar(100));
create table student (id int primary key,name varchar(100));
//重点：中间表，用来表述学生和老师对应关系的
create table teacher_student(teacher_id int,student_id int,constraint foreign key(teacher_id) references teacher(id),constraint foreign key(student_id) references student(id));
insert into teacher values(1,'梁托尔斯泰老师');
insert into teacher values(2,'李老师');
insert into student values(1,”张三”);
insert into student values(2,”李四”);
//重点理解：
//第一个字段老师的id
//第二个字段是学生的id
//翻译过来insert into teacher_student values("梁托尔斯泰老师",“张三”);
insert into teacher_student values(1,1);
insert into teacher_student values(1,2);
insert into teacher_student values(2,1);
insert into teacher_student values(2,2);
//查询李老师所教的学生
select id from teacher where name=’李老师’
select student_id from teacher_student where teacher_id=id
select * from student where id in(select student_id from teacher_student where teacher_id =(select id from teacher where name='李老师'));
//查询张三的所有老师
select * from teacher where id in(select teacher_id from teacher_student where student_id=(select id from student where name='张三'));
二.连表查询
分类：内连接、外连接、交叉连接
初始定义表结构
create table customer(id int primary key auto_increment,name varchar(20),city varchar(20));
create table orders(id int primary key auto_increment,good_name varchar(20),price float(8,2),customer_id int);
insert into customer (name,city) values('李老师','东北');
insert into customer (name,city) values('崔老师','山西');
insert into customer (name,city) values('张老师','内蒙');
insert into customer (name,city) values('闫老师','天津');
insert into orders(good_name,price,customer_id) values('电脑',59,1);
insert into orders(good_name,price,customer_id) values('笔记本',88,2);
insert into orders(good_name,price,customer_id) values('吹风机',99,1);
insert into orders(good_name,price,customer_id) values('香水',300,3);
insert into orders(good_name,price,customer_id) values('牛奶',100,6);
1.交叉查询
交叉查询，又叫笛卡尔积查询，会将左表和右表的信息，做一个乘积将所有信息查询出来，会产生临时表，比较占用内存，生成的记录数=表1数据个数X表2数据个数
select * from customer,orders;
select * from customer cross join orders;
2.内连接查询
内连接，inner join on 查询两张表，设定条件，将两张表中对应的数据查询出来，不会产生笛卡尔积，不会产生临时表，性能高
select * from customer c inner join orders o on c.id=o.customer_id;
select * from customer,orders where customer.id=orders.customer_id;
select * from customer c,orders o where c.id=o.customer_id;
3.左外连接
左外连接 left join on 设定条件，将两张表对应的数据查询出来，同时将左表自己没有关联的数据也查询出来
注意：join前面是左，后面是右
select * from customer c left join orders o on c.id=o.customer_id;
4.右外连接
右外连接 right join on 设定条件，将两张表对应的数据查询出来，同时将右表自己没有关联的所有数据查询出来
select * from customer c right join orders o on c.id=o.customer_id;
联合查询
select * from customer left join orders on customer.id=orders.customer_id having price>20;
