mysql学习笔记之查询操作（二）

链接查询

**链接查询：**将多张表（大于等于 2 张表）按照某个指定的条件进行数据的拼接，其最终结果记录数可能有变化，但字段数一定会增加。
意义：在用户查询数据的时候，需要显示的数据来自多张表。
链接查询： join
使用方式为：左表 join 右表。
链接查询分类：内连接，外连接，自然链接，交叉链接。

交叉链接

交叉连接： cross join，从一张表中循环取出每一条记录，每条记录都去另外一张表进行匹配，匹配的结果都保留（没有条件匹配），而连接本身的字段会增加，最终形成的结果为笛卡尔积形式。

基本语法： 左表 cross join 右边;
其结果与多表查询相同。
例如：select * from students cross join course;

交叉链接就是简单的笛卡尔积形式，加上筛选条件也能达到一些目的查询。

内连接

内连接：inner join，从左表中取出每一条记录，和右表中的所有记录进行匹配，并且仅当某个条件在左表和右表中的值相同时，结果才会保留，否则不保留。

基本语法：左表 + [inner] + join + 右表 + on + 左表.字段 = 右表.字段;
其中，关键字on表示连接条件，两表中的条件字段有着相同的业务含义。
例如： select * from students join score on students.sno=score.sno;

内连接可以没有连接条件，即可以没有on及之后的内容，这时内连接的结果全部保留，与交叉连接的结果完全相同。而且在内连接的时候可以使用where关键字代替on，但不建议这么做，因为where没有on的效率高

外连接

外连接： left\right join，以某张表为主表，取出里面的所有记录，然后让主表中的每条记录都与另外一张表进行连接，不管能否匹配成功，其最终结果都会保留，匹配成功，则正确保留；匹配失败，则将另外一张表的字段都置为NULL.

**基本语法：**左表 + left\right + join + 右表 + on + 左表.字段 = 右表.字段;
其中，关键字on表示连接条件，两表中的条件字段有着相同的业务含义。在这里，以主表为依据，外连接分为两种，分别为：
left join：左外连接（左连接），以左表为主表；
right join：右外连接（右连接），以右表为主表。
select * from students left join score on students.sno=score.sno;

select * from students left join score on students.sno=score.sno;

实际上，无论以那张表为主表，其外连接的结果（记录数量）都不会少于主表的记录总数。此外，虽然左连接与右连接有主表差异，但显示的结果都是：左表的数据在左边，右表的数据在右边。

自然链接

自然连接：nature join，自然连接其实就是自动匹配连接条件，系统以两表中同名字段作为匹配条件，如果两表有多个同名字段，那就都作为匹配条件。在这里，自然连接可以分为自然内连接和自然外连接。

自然内连接
基本语法：左表 + nature + join + 右表;
自然外连接
基本语法：左表 + nature + left/right + join + 右表;

外键

外键：foreign key，外面的键，即不在自己表中的键。如果一张表中有一个非主键的字段指向另外一张表的主键，那么将该字段称之为外键。每张表中，可以有多个外键。

增加外键

• 创建表时候增加外键
  foreign key + references + 外部表名（主键字段）；
• 创建表之后增加外键
  alter table + 表名 + add[constraint +外键名字]+ foreign key (外键字段)+references +外部表名(主键字段)

删除外键

alter table + 表名 + drop foreign key +外键名字；

外键作用

首先，给出父表和子表的定义：

• 父表，指外键所指向的表；
• 子表，指相对于父表，拥有外键的表。
  作用一：约束子表

在子表进行数据的写操作（增和改）的时候，如果对应的外键字段在父表找不到对应的匹配，那么操作就会失败。
作用二：约束父表

在父表进行数据的写操作（删和改，且涉及主键）的时候，如果对应的主键字段在子表已经被数据引用，那么操作就会失败。

外键约束

所谓外键约束，就是指外键的作用。之前所讲的外键的作用都是默认的作用，实际上，可以通过对外键的需求，进行定制操作。

外键约束有三种模式，分别为：

district：严格模式（默认），父表不能删除或更新一个已经被子表数据引用的记录；
cascade：级联模式，父表的操作，对应子表关联的数据也跟着被删除；
set null：置空模式，父表的操作之后，子表对应的数据（外键字段）被置空。
在此需要注意：以上三种模式，都是对父表的约束。
基本语法： foreign key(外键字段) + references + 父表(主键字段) + [on delete + 模式 + on update + 模式];
通常一个合理的做法（约束模式）是：删除的时候， 子表被置空；更新的时候，子表进行级联操作。

联合查询

联合查询由多条select语句构成，每条select语句获取的字段数相同，但与字段类型无关。

基本语法： select 语句1 + union + [union选项] + select 语句2 + …;
**union 选项：**与select选项一样有两种

• all：无论重复与否，保留所有记录；
• distinct：表示去重，为默认选项。

联合查询的意义有两种，分别为：
查询同一张表，按时需要不同，例如查询学生信息，要求男生按年龄升序排序，女生按年龄降序排序；
多表查询，多张表的结构是完全一样的，保持的数据结构也是一样的。
此外，如果数据量非常的大，就要进行分表（垂直分表和水平分表），而分表的依据无外乎数据多不多和常不常用。
联合查询，俗点说其实更像是一种剥洋葱的感觉，一点点的把数据查找或者规范成自己想要得到的样子。

子查询

子查询有两种分类方式，分别为：按按结果分类和位置分类。

按结果分类，即根据子查询得到的数据进行分类（理论上，任何一个查询结果都可以理解为一个二维表），分别为：

• 标量子查询：子查询得到的结果是一行一列，出现的位置在where之后；

• 列子查询：子查询得到的结果是一列多行，出现的位置在where之后；

• 行子查询：子查询得到的结果是多行一列（多行多列），出现的位置在where之后；

• 表子查询：子查询得到的结果是多行多列，出现的位置在from之后。
  按位置分类，即根据子查询（select语句）在外部查询（select语句）中出现的位置进行分类，分别为：

• from子查询：子查询出现在from之后；

• where子查询：子查询出现在where条件之中；

• exists子查询：子查询出现在exists里面。
  假如我现在想找到操作系统分数最高的学生的学生姓名：
  查询操作系统成绩：

 select sname,degree from students,score,course
 where students.sno=score.sno and
  course.cno=score.cno and
  cname= '操作系统' ;

显示成绩最高学生姓名和成绩；

  select sname,degree from students,score,course
 where students.sno=score.sno and
  course.cno=score.cno and
  cname= '操作系统' and 
  score.degree=
  (select max(degree) from score,course
   where cname='操作系统' and course.cno= score.cno);