多表查询

数据表的关联关系

关联关系介绍

mysql是一个关系型数据库，不仅可以存储数据，还可以体现数据与数据之间的关系。一个表中的外键字段的一个数据值，就代表所联系的另一个表的一条记录
注意：外键数据值，必须在另一个数据表中存在

• 数据之间的关系分为四种：
  1.一对一关联
  2.一对多关联
  3.多对一关联
  4.多对多关联

• 一对一关联（学生与学号的关系）

  • 方案一：主键关联。两个数据表中，主键相同的数据互为对应的数据
  • 方案二：唯一外键。这个外键与其他表的主键进行关联，外键所添加的值必须在关联的主键中存在(外键设置为unique)

• 一对多关联（学生与班级的关系）

  • 方案：在多的一段添加外键，但不设置为unique

• 多对多关联（学生与选课的关系）

  • 方案：创建表，其中两个字段都为外键，分别与两个数据表的主键关联，进而代表两个表中记录之间的对应关系，可以视情添加字段。

外键约束

将一个字段添加外键约束，与另一个表的主键进行关联之后，这个外键约束添加数据时所添加的值，必须在另一个表中存在

• 案例1：学生表与班级表（在学生表中添加外键与班级表的主键关联）
  • 步骤：
    1.先创建没有关系的表，也就是被关联的表
    
    2.创建学生表（创建时添加外键约束、创建后添加外键约束）
    关键语句：constraint 外键逻辑名(用于修改操作的方便，规则一般是：FK_表1_表2) foreign key(cid) references 所关联的表名(所关联的字段名)
    

CREATE TABLE goodscover(
	id INT PRIMARY key,
	src VARCHAR(200),
	gid INT,
	CONSTRAINT FK_GOODSCOVER_GOODS FOREIGN KEY(gid) REFERENCES goods(id)
);

3.添加数据
班级表

学生表

cid字段是可以选择的，这就是不同于其他字段的区别

上述方式是在创建时定义外键；也可以在创建之后添加外键定义，利用单独修改语句添加外键
语句：alter table students add constraint FK_STUDENTS_CLASSES foregin key(cid) references classes(classid);
删除外键约束：alter table students drop foreign key FK_STUDENTS_CLASSES;

级联操作

当需要修改被关联的表中的主键的时候，如果这个主键已经被关联了，那么是无法直接修改的；并且是不能删除的


修改方式1：
1.先将关联的数据表中的外键值设置为null
2.再修改被关联数据表的主键信息
3.在修改关联表信息
注意：再sql中字段等于null语句不是用字段=null;来表示的，而是用字段 is null;来表示的。

修改方式2：
前置条件，需要将外键设置为级联操作，这样一来，可以在只修改被关联表逐渐的同时，修改关联表的外键
关键语句（利用独立语句添加外键，同时声明级联操作）：alter table students add constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(classid) ON UPDATE CASCADE ON DELETE CASCADE;

ON UPDATE CASCADE ON DELETE CASCADE;声明做修改和做删除的级联操作
而后对主键外键的操作只需要一条语句即可：

连接查询

从多张表中查询数据，就是在查询学生信息的同时查询出班级信息；所需的数据来自多张表
这时我们就可以通过连接查询来达到这样的目的（从多张表中查询数据）。
再mysql中可以使用join关键字实现多表的连接查询

• inner join 内连接
• left join 左连接
• right join 右链接

navicat的使用，在查询选项中点击新建查询即可书写sql语句

inner join 内连接

语法：select <字段名> from tableName1 inner tableName2;意为用表1内连接表2
eg.select * from students inner join classes;学生表中每个记录斗鱼课程表中的每个记录都进行匹配，这样就形成了，一个学生记录对应了四条班级记录，就是笛卡尔积

其中有很多数据是没有意义的，我们使用内连接条件查询

这样的查询效率是比较低的，因为sql执行顺序是先从数据表中获得笛卡尔积，然后再从笛卡尔积中获取满足条件的记录
解决方式：
语句：select * from students inner join classes on students.cid=classes.classid;这个语句的执行顺序就是先进行匹配，然后再进行记录的连接

所以，条件查询，一般建议使用on关键字，也可以在最后利用where进行条件查询，比如说查询男生
内连接特点：只获取两个表中匹配条件成立的数据，任何一个表在另一个表中，如果没有找到匹配的结果，则该条记录不会被显示出来

left join 左连接

需求：查询所有的学生信息，如果学生有对应的班级信息，则将对应的班级信息也查询出来。
与内连接拆线呢不同的是，内连接只显示信息匹配的记录，而做链接是将所有记录都显示，不管有没有匹配到主键

• 语句：select * from left join on 匹配条件;，就是有外键的表，放在左边
  左连接：显示左表的所有数据，如果在有右表中存在与左表关联的数据也一同显示出来
  eg.select * from students left join classes on students.cid=classes.classid;
  

right join 右连接

• 语法：select * from right join on 匹配条件;
  eg.select * from students right join classes on students.cid=classes.classid;
  坐标游标的书写顺序与左连接是一样的，不变
  与左连接的意义有所不同，显示右表中的所有记录，如果有的课没有被选，也会显示
  

子查询/嵌套查询

子查询：先进行一次查询，第一次查询的结果作为第二次查询的源/条件（第二次查询是基于第一次查询的结果来进行的）

返回结果 单行

• 需求1：查询软工01班中的学生信息
  select * from students WHERE cid = 1;
  这样的查询，是在知道班级编号的情况下进行的，当班级很多的时候，我们只拥有班级名称这一信息，那么我们该如何进行查询呢？
  这个时候就需要用到子查询：
  • 1.先查询软工01班的id
    select * from classes WHERE classname = '软工01';
  • 2.再利用这个id查询去查询这个班级中的学生信息
    select * from students WHERE cid = 1;

将这两个步骤整合为一个步骤，就叫做子查询
select * from students WHERE cid = (select * from classes WHERE classname = '软工01');

返回结果 多行

• 需求2：查询所有软工班级的学生信息，也就是软工01、软工02、软工03、软工04班级中的学生信息
  • 1.查询所有软工班
    select classid from classes where classname like '软工%';
  • 2.查询这些班级编号中的学生信息
    select * from classes where cid = 1;
    select * from classes where cid = 2;
    select * from classes where cid = 3;
    select * from classes where cid = 4;
    
    这样查询出的结果有四个，观察非常不方便，可以利用关键字union，将四个结果联合起来
    select * from students where cid = 1
    union
    select * from students where cid = 2
    union
    select * from students where cid = 3
    union
    select * from students where cid = 4;
    
    但是这样仍然面临一个问题就是，当cid的值非常多的时候，就非常不便于第二步的查询，所以利用子查询，当子查询返回的结果有多个值，则子查询不能用“=”来表示对等关系，要用关键字in，因为，多个结果是一个集合；还有not in，含义相反
    select * from students WHERE cid in (select classid from classes WHERE classname LIKE '软工%');

返回结果 作为虚拟表

• 需求3：查询cid=1中的班级中性别为男的学生信息
  select * from student where cid = 1 and gender = '男';利用and关键字连接查询条件
  • 利用子查询：
    将查询结果作为临时表进行查询：select * from (select * from students where cid = 1) temp where age = 18;
    
    注意：如果使用子查询出现临时表的情况，那么就必须为这个临时表命名，这是上面学的知识，直接空格加表明即可；字段名的临时定义在使用as关键字

数据表别名

如果在连接查询的多张表中，存在字段名相同的字段，可以使用tableName.字段名来进行区别，但有时这样的明明太长不便于书写，可以使用数据表别名
利用如下方式设置简便的数据表别名，以方便条件的书写，而且查询内容也可以在前面重新设置，比如只查询s表中的所有记录，以及只查询c表中的classname字段