MySQL笔记——多表操作

目录

 

外键

操作关联表

连接查询

子查询

---

 

• 外键

外键是指引用另一个表中的一列或多列，被应用的列应该具有主键约束或唯一性约束。外键用于建立和加强两个表数据之间的连接。

引入外键后，外键列只能插入参照列存在的值，参照列被参照的值不能被删除，这就保证了数据的完整性。

操作	解释
为外键添加约束	alter table 表名 add constraint waijian foreign key(外键字段名) references 主表表名（主键字段名）; 在为表添加外键约束时，需要注意： 建立外键的表必须是InnoDB型，不能是临时表。（MySQL中只有InnoDB才支持外键）。 定义外键名时，不能加引号。 可以通过一下语法避免产生垃圾数据 alter table 表名 add constraint waijian foreign key(外键字段名) references 主表表名（主键字段名）; [on delete {cascade | set null | no action | restrict}] [on update {cascade | set null | no action | restrict}]
删除外键约束	alter table 表名 drop foreign key 外键名;

• 操作关联表

关联关系

1. 多对一：数据表中的常用关系，如员工与部门的关系
2. 多对多：例如学生与课程之间的关系，通常情况下，实现多对多需要一张连接表，该表会存在两个外键，分别参照学生表和课程表。
3. 一对一：例如人与身份证之间的关系。一对一关系需要分清主从关系，从表需要主表存在才有意义。不过这种关系在数据库中并不常见。

   操作	解释
   添加数据	alter table b2 add constraint waijian foreign key（外键字段名） reference b1（id）; ——添加外键,建立两表的关联性 成功添加外键约束后，如果想要添加数据，就要先为主表b1添加数据 insert into b1 (id,name) values (1,'网络一'); insert into b2(sid,sname,gid) values(1,'赵一',1); insert into b2(sid,sname,gid) values(2,'钱二',1); 此时两表之间的数据巨具有关联性，如果需要查网络一有那些学生，就要先查网络一的id select id from grade where name='网络一'; select sname from student where gid=1;
   删除数据	在删除主表中的数据时，一定要先把从表中对应的数据先删除。 delete from b2 where sname='赵一'; delete from b2 where sname='钱二'; delete from b1 where id=1;

• 连接查询

连接查询包括交叉连接查询、内连接查询、外连接查询

连接类型	格式
交叉连接	select * from b1 cross join b2  交叉连接返回的结果是被连接的两个表中的所有数据的笛卡尔积，也就是返回 “第一个表中符合查询条件的数据行×意第二个表中符合查询条件的数据行数”
内连接	select 查询字段 from b1 [insert] join b2 on b1.关系字段=b2.关系字段; 内连接是最常见的连接查询，在内连接中，只有满足条件的记录才能出现在查询结果中
外连接	有时需要返回结果中包括不符合条件的记录时，可以使用外连接，外连接分为左外连接和右外连接。 select 所查字段 from b1 left|right [outer] join b2 on b1.关系字段=b2.关系字段 where 条件; 左连接：返回包括左表中的所有记录和右表中符合连接条件的记录 右连接：返回右表中的所有记录和左表中符合连接条件的记录

• 子查询

  关键字	例子
  带in关键字	select * from department where did in(select did from employee where age=20); 查询年龄为20岁的员工的部门 带in关键字进行子查询时，内层查询语句进返回一个数据列，这个数据列的值将供外层查询语句进行比较
  带exist关键字	select * from department where exist(select did from employee where age>21; 查询employee表中是否存在年龄大于21岁的缘故，如果存在，则查询department表中的所有记录。 exist关键字后面的参数可以是任意一个子查询，这个子查询的作用相当于测试，它不产生任何数据，只返回ture or false。
  带any关键字	select * from department where did>any(select did from employee); any 关键字表示满足其中任意一个条件，它允许创建以一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件。
  带all关键字的子查询	select * from department where did>all(select did from employee); all关键字和any有点类似，只不过带all关键字的子查询返回的结果需要同时满足所有内层查询条件。
  带比较运算符的子查询	select * from department where did=(select did from employee where name='赵四'); 子查询中可以使用其他的比较运算符：如"<"、">"、">="、"="、"!="