MySQL数据库(子查询, 多表关联与多表查询＜内连接与外连接＞)

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询.
分类：
按子查询出现的位置：
select后面：仅仅支持标量子查询
from后面：支持表子查询
where或having后面：支持标量子查询，列子查询，行子查询
按功能、结果集的行列数不同：

标量子查询（结果集只有一行一列)

进行更换操作(在mySQL中仅限异表操作)

UPDATE t_student SET NAME='aaa' WHERE num=(SELECT num FROM stu WHERE height =180)

进行删除操作

DELETE FROM t_student NAME='aaa' WHERE num=(SELECT num FROM stu WHERE height =180)

在一条SQL中如果出现两个以上的表名,可以定义别名

在select后子查询用外面查询结果为条件

SELECT ts.num,(SELECT t.name FROM t_student AS t WHERE t.name=ts.name) FROM t_student AS ts

列子查询（结果集只有一列多行）

SELECT *FROM t_student WHERE height=(SELECT MAX(height)FROM t_student)

行子查询（结果集有一行多列）

（较少）

SELECT *FROM t_student WHERE (height,weight)=(SELECT MAX(height),MAX(weight)FROM t_student)

表子查询（结果集一般为多行多列）

SELECT *FROM (SELECT sex,COUNT(*)c FROM t_student GROUP BY sex) AS t WHERE t.c>2

多表关联

为什么需要多表关联?

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。

​ 目前关系数据库有5种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

​ 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了

数据库设计范式

1．第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该 数据库表满足了第一范式

2．第二范式就是要有主键，要求其他字段都依赖于主键。
• 没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。
• 其他字段为什么要依赖于主键？因为不依赖于主键，就找不到他们。更重要的是，其
他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依
赖于主键，也就成了唯一的 (任意一个字段都只能依赖表中同一字段)

3 .第三范式 就是指表中的所有数据元素不但要能唯一地被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系。也就是说，对于一个满足2nd NF 的数据结构来说，表中有可能存在某些数据元素依赖于其他非关键字数据元素的现象,必须消除 (一张表中最多存在2层同类信息)

外键：引用另外一个数据表的某条记录。
外键列类型与主键列类型保持一致
数据表之间的关联/引用关系是依靠具体的**主键（primary key）和外键（foreign key）**建立起来的。

create table score(
cid int not null auto_increment primary key,
score int,
sid int,
CONSTRAINT 约束名 foreign key(sid) references student(sid)
);

添加外键约束

ALTER TABLE 表名 ADD [CONSTRAINT 约束名] FOREIGN KEY(外键列) REFERENCES 外表(属性)

示例:

ALTER TABLE student ADD CONSTRAINT fk_gradeid FOREIGN KEY(gradeid) REFERENCES grade(id)

关联表(主键);
删除外检键

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名

多表关联时注意事项:

1、当主表中没有对应的记录时，不能将记录添加到从表
2、不能更改主表中的值而导致从表中的记录孤立
3、从表存在与主表对应的记录，不能从主表中删除该行
4、删除主表前，先删从表

关联查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

SELECT*FROM student,grade,WHERE s.id=g.id

多表时,给表定义别名,通过别名去调用表中列,这样就不会重复了

按照功能分为内连接与外连接

内连接分为: 等值连接,非等值连接与自连接

等值连接: 等值连接必须要有等值的条件，当条件不同时连接的结果也不相同，两个关系可以没有相同的属性列

SELECT s.num,s.name,s.sex,g.name FROM student s INNER JOIN grade g ON s.gradeid=g.id

把满足条件的部分查找出来

非等值连接: 必须要有相同的属性列才能进行，即等值连接之后要去除相同的属性列

SELECT s.num,s.name,sl.lev_name FROM student s INNER JOIN score_level sl ON s.score BETWEEN sl.lower_score AND sl.upper.score

自连接:

SELECT td.name,tp.name pname FROM t_area ta INNER JOIN t_area tp ON ta.pid=tp.id 

外连接:

左外连接:

SELECT s.num,s.name,s.sex,g.name FROM student s LEFT FROM grade g ON s.gradeid=g.id

右外关联:

SELECT *FROM student s RIGHT JOIN grade g ON s.gradeid=g.id GROUP BY g.id,g.name