[大数据]day08

多表联查
??
? 一对一

? ?一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然

? ? 有时候，为了业务，或者避免一张表中数据量过大，过复杂，在开发中 ? ? ?会进行一对一方式来设计表。

? 一对多
? ? 一个实体的某数据与另一个实体的多个数据有关联需要设计表的外键
?
? ? 班级表中的名称对应学生表中的班级名称

? ?2. 创建数据库表

? ? - constraint 约束

? ? ? ? - foreign key就是表与表之间的某种约定的关系，由于这种关系
? ? 的存在，能够让表与表之间的数据，更加的完整，关连性更强。

? ? ? ? - foreign key语句的式例：FOREIGN KEY(Sno) ?references ? ? ? ? Student(Sno)

? ? ? ? **注意：**表的外键必须是另一张表的主键
? ? //创建班级表
? ? create table class(id int primary key auto_increment,name varchar(20));
? ? //创建学生表
? ? 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);?
? ? insert into student values(2,'lisi','nan',2);?
? ? insert into student values(3,'jingjing','nan',2);?
? ? //联查?
? ? select * from student where class_id=(select id from class where id=2);

? ? ? ? 删除主键信息时，当该主键字段值在外键表中存在时，该记录是不能

? ? 删除的。---要把外键表的相关信息删除之后，才能删除。
? ??
? ? ? ? 解决:创建一个中间表，专门用来维护多表之间的对应关系，通常是能够唯一标识出数据的字段

? ? 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,”李四”);?

? ? 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数据个数*表2数据个数
? ? select * from customer,orders;?
? ? select * from customer cross join orders;
? ??
? ? 内连接查询
? ? ? ?内连接，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; ? ?
? ??
? ? ?左外连接
? ? 左外连接 left join on 设定条件，将两张表对应的数据查询出来，同时将左表自己没有关联 ? ?的数据也查询出来
? ? 注意：join前面是左，后面是右 ? ?
? ? ??
? ? select * from customer c left join orders o on c.id=o.customer_id;
? ??
? ? 右外连接
? ? ? ? 右外连接：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;

1.三个表关联查询，展示所有信息
2.查询6C班所有学生的成绩
3.查询6B班所有学生的语文成绩
4.查询6B班所有学生的语文平均成绩
5.查询刘备所在班级的所有同学的英语平均成绩
6.查询所有班级学生的语文平均成绩

--1（1） select * FROM class,student,score where class.id=student.c_id and student.id=score.s_id; ?
? ?（2） select * FROM class inner join teachear on class.id=teachear.c_id inner join student on teachaer.id=student.t_id;
? ? ?
--2 1 select * FROM class,student,score where class.id=student.c_id and tudent.id=score.s_id and class.cname='6C'

--3select * FROM class,student,score where class.id=student.c_id and student.id=score.s_id; ?
? and class.cname='6B' and score.chinses;

--4 select avg(score.chinses) FROM class,student,score where class.id=student.c_id and student.id=score.s_id and class.cname='6B';

-- 5.select avg(score.english) FROM class,student,score where class.id=student.c_id and student.id=score.s_id and student.sname='刘备';

--6 select avg(score.chinses)FROM class,student,score where class.id=student.c_id and student.id=score.s_id ;

update student set name=’张三’ where name=’zhangsan’?

constraint foreign key(class_id) references class(id));?
（物理外键）

create table score(id int primary key auto_increment,chinses varchar(20),english varchar(20),math varchar(20),s_id int,constraint foreign key(s_id) references student(id));
————————————————
版权声明：本文为CSDN博主「H1912Aliyanyan」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/H1912Aliyanyan/article/details/121642694