MySQL的多表操作

文章目录

1. 多表关系

之前我们都是对一个表进行操作，但一个二维表存储过种类的数据可能会造成数据冗余，也就是同样的数据多次存储，例如我们的学生选课，如果将学生信息与其选课信息放在一个表中，就会出现很多重复的数据，而我们将他们分为两个表，通过主键和外键进行连接，这可以避免这种情况。
表的关系有：

1. 一对多
2. 多对一
3. 多对多

其中一对多和多对一这两种关系最为简单，只需两张表即可，而多对多的关系需要一个中间表进行链接。

2. 外键约束

  MySQL的外键约束（foreign key）是表的一个特殊字段，它通常与主键连用，外键的多用其实就是用来与其他表进行连接的，而拥有主键的表又称为主表，一个表既可以是主表也可以是从表，这取决于连接数据的对应关系。

定义外键时需要遵守一下规则：

1. 对应的主表必须已经存在数据库中，或是当前的表中
2. 必须为主表定义主键，只有这样才从表才能与主表连接
3. 主表中主键对应字段的值不能为空，但从表中外键对应字段的值可以为空，也就是说只要外键中的每个非空值出现在指定的主键中，这个外键就是正确的
4. 在主表后面指定列或列名的组合，这个列或列名的组合必须时主表的主键或候选键。
5. 外键对应列的数目与主键对应列的数目相同
6. 外键中列的数据类型必须与主表中对应主键的列的数据类型相等

2.1创建外键约束

1.在create中创建外键约束

Constraint 外键名 foregin key 字段名(可多个) reference 主表名 主键列(可多个);

2.在创建表后添加外键约束
我们用alter

Altertable 表名 addconstraint 外键名 foreignkey 字段名 reference 主表名 列名;

2.2删除外键约束

用alter
语法格式

Altertable 表名 dropforeignkey 外键约束名;--（外键约束名是自己创建外键约束时起的名字）

例如

Altertable emp dropforeignkey emp_k;

3.对表联合查询

  多表查询就是对两个或两个以上的表进行查询，因为我们需要的数据可能是来自多个表中的数据。

而多表连接的方式有一下几种：

1. 交叉连接：产生笛卡尔积
2. 内连接：分为隐式内连接和显示内连接
3. 外连接：分为左外连接，右外连接和满链接

3.1交叉连接

  交叉连接可以理解为排列组合，假设我们有两组数据，并且一组是主键对应的有n个，一组是外键对应的有m个，而交叉连接就是将外键中的每个数据都与主键的每个数据进行组合，从而得到nm行数据。

3.2内连接

内连接其实就是通过主键和外键的连接取两个表的交集。

也就是在从表的外键中，找出所有与主表主键中有对应值得数据。

  而内连接有隐式和显式，其实它们的效果都是一样的，只是语法格式不同，且他们是两个不同标准的语法，当然现在两种都支持。

隐式内连接（SQL92标准）：select * from A,B where 条件;
显式内连接（SQL99标准）：select * from A inner join B on 条件;

这里我们可以看到隐式内连接后面的子句用的式where，显式内连接用的是on，并且用了关键字inner join。

3.3外连接

  外连接有左外连接，右外连接和满链接，我们知道主键中的值和外键中的值是通常是一一对应的，比如学生表（学号，姓名，年龄），选课表（学号，课程号，课程），我们通过选课表中的学号对应学生表中的学生，通过两个表的连接从而得出每个学生分别选了哪些课。
但是学生不一定把所有课都选了，而课也不应定就被选了，我们通过这个例子分别解释各种外连接。

1. 左外连接：
  左外连接就是保留左边表的所有值，因为左表的外键不一定就有在右表中有对应的值，它对应右表可能为空值。

2. 右外连接：
  右外连接与左外连接相反，右外连接就保留右表的所有值。

3. 满链接：
  满链接算是左外连接和右外连接的并集，即左右两边的表的值都保留下来。

4.子查询及子查询关键字

  子查询其实就是语句的嵌套，与编程语言中函数的调用、循环的嵌套一样。比如select的嵌套。

举例：

  当我们知道又一个表emp时，但我们不知道其内部的数据或则数据很多，几百上千个，我们需要找到工资最高的员工时，就可以进行嵌套。

Select*from emp where salary =(selectmax(salary)from emp);

分析：

(select max(salary) from emp)这个子查询用来查询工资多高是多少，将查找出的值返回，假设它的返回值为50000，那么整个语句就是查询工资为50000的员工信息。
相当于select * from emp where salary = 50000
但由于我们并不知道最大工资是多少，所以我们通过嵌套的方式进行查询。

在子查询中还含有一些常见的关键字，它们可以提供更丰富的查询功能：

1. ALL关键字
2. ANY关键字
3. SOME关键字
4. IN关键字
5. EXISTS关键字

4.1 ALL关键字

语法格式：

Select … from … where c >all(查询语句);-- 举例

等价于

Select … from … where c > result1 and c > result2 and c > result3 … ;

All的意思就是满足所有条件，也就是说c要大于子查询语句返回的所有值。

特点：

与子查询返回的所有值进行比较，成立则返回true，否则false; ALL可以与判断符号结合使用，如比较大小的这些。

4.2 ANY和SOME关键字

举例：

Select … from … where c >any(查询语句);

等价于

Select … from … where c > result1 or c > result2 or c > result3 … ;

Any和some的作用是一样的，可以将some理解为any的别名。

特点和all一样，不过any和some则是只需满足其中一个结果即返回true，如果所有结果都不满足则返回false。

4.3 IN关键字

Select … from … where c in(查询语句);

等价于

Select … from … where c = result1 or c = result2 or c = result3 or result4;

特点：

In关键字就是判断某个记录的值是否在指定的集合中
Not in 则是反过来，判断记录是否不存在指定的集合中

4.4 EXISTS关键字

格式：

Select … from … whereexists(查询语句);

特点：

EXISTS子句不返回结果，只返回true和false
EXISTS子查询是用来判断是否有返回值，有返回值则true，外层查询执行，没有则false，外层查询不执行。
EXISTS的执行效率比IN高，因此在实际开发中，特别是大量数据时，推荐使用EXISTS关键字。

例如：
查询公司是否有大于60岁的员工，有则输出

Select*from emp as a whereexists(select*from emp as b where a.age >60);

4.5 自关联查询

  自关联查询就是自己和自己进行关联，一个表既可以是主表，也可以是从表，这取决于连接的关系，所以一个表里面既可以有主键也可以有外键，而一个表既有主键也有外键，并且该外键是与自己的主键相连，那就可以进行自关联查询。

同时要注意，进行自关联查询时要给表取别名来区分

格式：

Select 字段列表 from 表1 a，表1 b where 条件;

或者

Select 字段列表 from 表1 a [left]join 表2 b on 条件;