文章目录
- 子查询
- 多表关联
- 外键
- 关联查询
- 内连接(INNER JOIN)
- 外连接
子查询
含义:出现在其他语句中的SELECT语句,称为子查询或内查询;外部的查询语句,称为主查询或外查询。
分类:
按子查询出现的位置:
- SELECT后面:仅仅支持标量子查询;
- FROM后面:支持表子查询;
- WHERE或HAVING后面:支持标量子查询,列子查询,行子查询;
在修改、删除表中数据的语句中使用子查询,注意的是,子查询中不能使用当前正在操作的表。
按功能、结果集的行列数不同:
- 标量子查询(结果集只有一行一列)
- 列子查询(结果集只有一列多行)
- 行子查询(结果集有一行多列)
- 表子查询(结果集一般为多行多列)
子查询在SELECT语句内部可以出现SELECT语句。
语句结果可以作为外部语句中条件子句的一部分,也可以作为外部查询时的临时表。
在一条SQL语句中,如果出现2个以上的表名时,我们可以为这个表名定义别名
举例:
SELECT后面:
SELECT
st.sex,
(SELECT s.name FROM student s WHERE s.`name` = st.name)
FROM
student st
在FROM后面:
SELECT
*
FROM (SELECT sex,NAME FROM student)s
WHERE s.sex = '男'
在WHERE或HAVING之后:
-- 标量子查询
SELECT
*
FROM
student
WHERE height =(SELECT MAX(height) FROM student);
-- 列子查询
SELECT
*
FROM
student
WHERE height IN (SELECT height FROM student WHERE height > 170);
-- 行子查询
SELECT
*
FROM
student
WHERE (height, weight) =(SELECT MAX(height),MAX(weight) FROM student);
多表关联
在设计表的时候,我们有时候是需要多表关联的,主要为了减少数据的冗余,对表进行拆分。
数据库设计范式:
为了建立冗余较少,结构合理的数据库,设计数据库时必须遵循一定的规则。在关系型数据库中这种规则称为范式。范式是符合某一种设计要求的总结。
目前关系数据库有5种范式:
第一范式(1NF)
第二范式(2NF)就可以
第三范式(3NF)
第四范式(4NF)
第五范式(5NF)又称器完美范式
满足最低要求的范式就是第一范式。在第一范式的基础上进一步满足更多规范要求的称为第二范式,其余范式依次类推,一般来说,数据库只要到达第三范式就可以了。
- 第一范式(确保每列都保持原子性)
第一范式是对基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一范式。 - 第二范式(包含了主键,要求其他字段都要依赖于主键)
没有主键就没有唯一性,没有唯一性在集合中就定位不到这行数据记录,所以就要有主键。
其他字段为什么要依赖于主键?因为不依赖于主键,就找不到他们。更重要的是,其他字段组成的这行记录和主键表示的是同一个东西,而主键是唯一的,它们值需要依赖于主键,也就成了唯一的。
- 第三范式就是要消除传递依赖,方便理解,可以看作是“消除冗余”。
外键
就是“引用”另外一个数据表的某条记录。
外键类类型必须和主键列类型保持一致。
数据表之间的关联/引用关系是依靠具体的主键(PRIMARY KEY)和外键(FOREIGN KEY)建立起来的。
建表时就增加外键:
CREATE TABLE 表名(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
sid INT,
CONSTRAINT 约束名 FOREIGN KEY(sid) REFERENCES 关联表(主键)
);
添加外键约束名
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] FOREIGN KEY(外键列) REFERENCES 关联表(主键);
删除外键约束:
ALTER TABLE 表名 DROP FOREIGH KEY 外键约束名;
举例:
先建两张表
像这种有关系但没有关联的关系我们也叫做弱关联。没有实际之间的约束。
有外键的也就叫做强关联。(添加外键约束)
ALTER TABLE student
ADD CONSTRAINT fk_gradeId FOREIGN KEY (gId) REFERENCES grade(id);
意为将student表中的gId列与grade表中的id列进行关联。
进行关联后,表头也就有了一些的变化。
删除外键:
ALTER TABLE student DROP FOREIGN KEY fk_gradeId;
注意:
- 当主表中没有对应的记录时,是不能将记录添加到从表中的。
- 不能更改主表中的值而导致从表中的记录孤立。
- 从表存在与主表对应的记录,不能从主表中删除该行。
- 删除主表前,先删从表中的数据。
关联查询
含义:又称为多表查询,当查询的字段来自与多个表时,就会用到连接查询
(一对多、多对一、一对一、多对多)
笛卡尔乘积现象:表1有m行,表2有n行,结果 = m*n;
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
代码演示:
SELECT * FROM student,grade ;
解决办法:
添加条件,多表时,为表定义别名,通过别名去调用表中的列,这样就不会重复了。
SELECT
*
FROM
student s,
grade g
WHERE s.gId = g.id -- 先合并表,后筛选
按功能分类:
- 内连接
等值连接、非等值连接、自连接 - 外连接
左外连接、右外连接
内连接(INNER JOIN)
主要通过设置连接条件的方式,来移除查询结果中某些数据行的交叉连接。
说人话就是:
是利用条件表达式来消除交叉连接的某些数据行。
格式:
SELECT 列名 FROM 表1 INNER JOIN 表2 [ON子句]
等值连接:
使用等于号(=)比较被连接列的列值,在查询结果中列出被连接表中的所有列,包括其中的重复列。
SELECT
*
FROM
student s
INNER JOIN grade g
ON s.gId = g.id
不等连接:
在连接条件中,可以使用其他比较运算符,比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!< 和 <>。
SELECT
*
FROM
student s
INNER JOIN grade g
ON s.height BETWEEN 170
AND 180
自关联
所谓自关联是指,一个数据表中的某个字段关联了该数据表中的另外一个字段。
就是自己关联自己
在这我们再创建一个表来说明这个问题:
CREATE TABLE t_area(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
pid INT
)
加入数据:
SELECT
ta.name,
tp.name pname
FROM
t_area ta
INNER JOIN t_area tp
ON ta.pid = tp.id
WHERE ta.id = 5
外连接
- 左外连接(LEFT JOIN)
无论关联条件是否成立,都会将左边表的数据全部查询出来。
语法:
SELECT 列名 FROM 表名1 LEFT JOIN 表名2 ON 表1.column1 = 表2.column
- 右外连接(RIGHT JOIN)
无论关联条件是否成立,都会将右边表的数据全部查询出来。
语法:
SELECT 列名 FROM 表名1 RIGHT JOIN 表名2 ON 表1.column1 = 表2.column
举例:
将内关联,右关联,左关联放在一起举例:
student表数据做了一些修改(先看表数据):
grade表:
内连接SQL语句:
SELECT
*
FROM
student s
INNER JOIN grade g
ON s.gId = g.id;
左连接SQL语句:
SELECT
*
FROM
student s
LEFT JOIN grade g
ON s.gId = g.id;
右连接SQL语句:
SELECT
*
FROM
student s
RIGHT JOIN grade g
ON s. gId = g.id;