键盘敲烂,月薪过万作业不做,等于没学
当前系列: SQL 修改讲义

我们现在有了:

  • 一个Student表,记录的是“源栈”学生的信息;
  • 一个Teacher表,记录的是“源栈”老师的信息。

那么,如何体现出某个学生的老师是谁呢?

只需要一个在Student表上添加一列TeacherId,记录该学生的老师的Id就可以了。

如果需要查看某个学生老师的完整信息,通过他的TeacherId值,再到Teacher表中进行查找。

TeacherId据说Student表的

外键

通常,我们都是使用主键作为外键(Foreign Key)。但是,我们也可以使用其他唯一列,或者能唯一标记行的多个列。

也就是在两张表之间建立主外键关系,比如:Student上有TeacherId外键(一个Teacher多个Student):

  • 使用外键的表被称之为从(子)表,即:Student
  • 被外键引用的被称之为主(父)表,即:Teacher

记忆小窍门:先有主表,才有从表,从表不能脱离主表单独存在(后文详述:级联)

但是,主外键关系也可以在同一张表中产生(面试陷阱)!比如,学生被变成了组,每个组都有一个组长,组长也是学生。

所以,外键列可以指向主表自己。

外键约束

PS:和主键/主键约束(复习)经常被混用一样,外键和外键约束虽然在严格意义上讲有区别,但也一样被混用。

和主键上通常都会添加主键约束一样,外键上也会添加外键约束。

添加外键约束,可以在进行表数据操作的时候,维护表数据的完整(正确)性,即:

从表不能出现主表中没有的外键值。

对应现实:一个Student不能有一个不存在的老师。

添加外键约束的核心SQL语句:

CONSTRAINT FK_Teacher_Id -- 指定约束名称,外键建议以FK开头,后跟表和列名 
FOREIGN KEY (TeacherId) -- 约束类型:外键,且外键列为TeacherId 
REFERENCES Teacher(Id) -- 外键表为Teacher,作为外键使用的列为Teacher上的Id
  • 可以在ALTER TABLE时作为独立的约束添加
    ALTER TABLE Student -- 修改表 
    ADD CONSTRAINT ...
  • 也可以在CREATE TABLE时在约束区标明:
    CREATE TABLE Major
    ( 
        -- ...
        TaughtBy INT, 
        CONSTRAINT FK_Teacher_Id FOREIGN KEY(TaughtBy) REFERENCES Teacher(Id) 

演示:

  • 外键引用的列必须是能“唯一标识的”(PRIMARY KEY或UNIQUE)
  • mysql在外键列上自动建立索引,SQL Server没有
  • 无法插入不存在的主键列值

从表上的外键列值

  1. 可以为NULL,比如某Student.TeacherId为NULL,表明该学生还没有被分配老师(区别于:分配了Id为100的老师,但实际上没有一个Id为100的老师根本不存在)
  2. 也可以重复,比如学生atai和ls可以有同一个老师

外键约束的删除:

  • T-SQL:和其他约束一样,使用DROP CONSTRAINT即可
    ALTER TABLE Major
    DROP CONSTRAINT FK_Teacher_Id
  • mysql:使用关键字foreign key
    alter table Major
    drop foreign key FK_Teacher_Id

级联删改

同样因为外键约束,我们是不能在主表上删除已被从表外键引用的行的。(或者更改使其违反外键约束

但是,有时候,即使主键被从表引用,我们也要坚持删除主表行/更改主表主键,怎么办?

通常我们可以(事务中演示

  • 先UPDATE从表数据,将对应的外键列值设置成NULL值,我们简称:SET NULL
  • 先DELETE从表数据,将对应的从表行数据直接删除,我们简称为:CASCADE DELETE

上述过程可由数据库自动完成,被称之为级联(cascade)

这需要在建立外键关系时指定:(演示)

ALTER TABLE Teacher
ADD CONSTRAINT FK_Teacher_Id FOREIGN KEY (TeacherId) REFERENCES Teacher(Id)
ON DELETE CASCADE		-- 在主表数据被删除时自动删除所有从表数据
ON UPDATE SET NULL		-- 在主表主键被更新时自动设置相应从表外键值为NULL


E-R模型实现

ER模型是独立的,就是一种“对实体关系的建模”,将其归纳成:1对1、1对多和多对多,不属于任何一种语言或技术。

在面对对象的语言复习中,和关系数据库中有不同的实现

  • 实体:表现为一行数据;
  • 属性:表现为行中的一列;
  • 关系:通过外键实现。

一对一(或表示为1:1)

关系数据可以即让两种表使用相同的Id,如下所示:

广义的1:1包含了1:0的这种情况,如上表所示,Id为3的Smith还没有妻子。

这种情形可以让Husband为主表,Id自动生成;Wife为从表,Wife表中的Id为外键,不要自动生成。

但在实际开发中,我们很少使用“一对一”的关系,而是将其变成:

一对多(或表示为1:n)

可以Daddy做主表,Children做从表:

可以看出一个Daddy可以有多个Children,比如Jason的孩子是Jack和Alice。

这种方式也可以用于表示“一对一”,只需要我们在外键上添加唯一约束即可。

多对多(n:n)

这时候我们无法通过在表上添加列的方式予以实现,所以我们选用新建一张“关系表”,如下图所示:

Friends这张表就是“关系表”,它仅用于记录Chinese和American之间的关系。


三大范式

#常见面试题#

所谓范式,你可以理解为:规范式,即(数据库表设计时)应该遵守的规范、应该采取的模式。

学习的关键:理解范式的目的。

第一范式

要求表中的每个列都不可再分。

以下表为例:

左边的表中From列,还可以被拆分成FromProvince和FromCity两列,所以它不满足于第一范式的要求。

满足第一范式,需要转换成右表。

第二范式

主要是针对使用“联合主键”的场景。

要求在第一范式的基础之上,一行数据,所有非主键列都是由联合主键“整体确定的。

以上表为例,成绩(Score)是学生(Student)某门功课(Major)的成绩,一个学生一门功课只能有一个成绩,所以学生成绩是通过“学生和功课”两者整体确定的:这是符合第二范式的。

但是,一门功课就有一个老师(Teacher),老师是由功课直接决定的,和Student没有关系:这就违反了第二范式。

第三范式

在第二范式的基础之上,确保每一个非主键列都是由唯一主键直接确定的。

比如这样一张学生表:

仔细分析,我们发现,学生的Name、Age、FromCity确实都是由Id确定的;但FromProvince是由FromCity确定的:这就违反了第三范式。

为什么要这样“拆”?

#体会#:满足范式的方法就是“拆”,拆分列拆分表。

需求

满足第一范式的“拆分列”,主要是为了满足“业务需求”。

比如可能有这样的需求:统计出来自各省的同学共有多少人,在左表上就不便操作。

当然你还可能问:那右表还需不需要继续拆分呢?拆分什么时候到头?是不是要一直拆,拆成一个一个的字符?比如学生的姓和名是不是也要拆?

还是看需求。如果有按姓进行操作的需求,就要拆;没有,就不用拆。

冗余

第二第三范式“拆分表”,主要是为了消除数据冗余。

冗余的弊端非常明显:

  • 首先是浪费空间(把数据量想得“”一些);
  • 但更重要的是,由于各种原因,重复数据之间可能出现“冲突”。比如:以前南岸属于重庆,但后来行政区划出现了修改,将南岸划给了四川。没有冗余的、满足第三范式的表设计只需要修改Address表即可;不满足第三范式的表设计得修改Student表中所有来自南岸的学生列——这既不经济也容易出错(error prone),一旦出错就会造成数据冲突,表中有点南岸属于重庆,有点南岸属于四川……

但是有时候,因为某些特定原因(通常是提高性能),我们愿意“以空间换时间”,宁愿从单张表中立即获取数据,而不愿意多表联合查询(比如获取某学生所在省份)。这时候也使用冗余违反范式约定也是可以的!

#体会#:范式不是固定的、强制性的。

所以虽然还有第四第五……范式,但绝大部分情况,我们只要满足三大范式就足够了。

名词解释

实际上,我们学习的数据库是一种“关系型(Relational)”数据库,他所依赖的理论就是关系模型。(除了关系模型,还有:层次模型/网状模型……)

这个模型最早是非常学术化的,所以有一大堆很“学术范儿”的名词:

描述表的

  • 关系:二维表,按行与列排列
  • 元组:表中的一行,或称为一条记录
  • 属性:数据表中的每一列
  • 域:属性的取值范围
  • 属性值:行和列的交叉位置的值,某行某列值
  • )码:是表中用于确定一个元组的唯一数据,主键或主关键字
  • 关系模式:对关系(表)的描述,一般表示为:关系名(属性1,属性2.....属性n)

XX性

比如第一范式要将列拆分到不可再拆分,就可以说是保证列的原子性

还有保证数据的完整性(一致性),意思就是要保证数据:正确且符合逻辑(通常使用约束来实现),包括:

  1. 域完整性:限制每一列的数据。使用:数据类型/CHECK/NOT NULL等约束实现
  2. 实体完整性:要求某一列或多列上的每一行的数据都是唯一的,或者说每一行都有一个唯一标识符,以保证不能有完全相等的两行。使用:PRIMARY KEY/UNIQUE等约束实现
  3. 参照完整性(主从一致性):表与表(关联表)之间的数据是一致的,使用外键约束实现

依赖

第二范式:消除 非主属性 对于 码 的 部分函数依赖

第三范式:消除 非主属性 对于 码 的 传递函数依赖

  • 非主属性:主键以外的列。
  • 码:主键,或者其他可以唯一标记一行数据的列。
  • 函数依赖:在一行数据中,如果通过A列,一定能确定B列的值,我们说B依赖于A。
  • 部分函数依赖:B依赖于A,但是只依赖于A中的一部分(A是列的组合),对应的就是完全函数依赖。
  • 传递函数依赖:C依赖于B,但B又依赖于A,C就对A有传递依赖。

因为要遵守三大范式的要求,实际的开发中,数据库中会存在大量的零碎的“小”表。对于这些小表,我们常常使用


JOIN

将多个表 水平 联接起来,以获取单表无法提供的信息。比如我们现在有两张表Student和City:

为了简化,我们只用了很少的列执行如下SQL语句:

SELECT * FROM Student s -- *指的是Join过后两张表的所有列,s是给表Student的别名 
JOIN City c -- 给表City一个别名,使用JOIN将Student和City连接起来 
ON s.FromCityId = c.Id -- 指定表Student(别名s)和City(别名c)连接的条件

建议:总是使用表别名。如果不使用别名,相同的列名就需要使用表的全名前缀加以区分

得到的结果就是:

从FromCity和Id列,可以清楚的看到两表连接的方式或规则:同一行内,FromCityId的值总是等于(右边City的)Id的值。这是由SQL中:ON s.FromCityId = c.Id 决定的。意思就是:把Student的FromCityId和City的Id相等的行连接起来。

查看执行计划,可以看到SQL Server先进行了两张表的扫描,然后再进行了JOIN操作:

我们可以在SELECT子句中指定列,进一步整理显示结果,比如:

SELECT 
s.SName, -- Student表的Name 
c.FromProvince AS Province, -- City表的FromProvince,还给一个列别名Province 
c.FromCity City -- City表的FromCity,还给一个列别名City 
FROM Student s JOIN City c 
ON s.FromCityId = c.Id 

结果就会变成:

这就是SQL中默认的连接方式:

内连接

关键字 INNRE JOIN,其中INNER可以省略。

除此以外,还有:

外连接

关键字:OUTER,也可以省略。因为它又还细分为:

  • 左外连接(可简称为左连接),关键字:LEFT:左边所有行被保留
  • 右外连接(可简称为右连接),关键字:RIGHT:右边所有行被保留
  • 全外连接,关键字:FULL:左右两边都被保留

所谓“左”和“右”,是指在书写SQL语句时,在JOIN左边或右边。

为了展示左连接,我们修改一下上表数据,把“陈晓斌”的FromCityId改成NULL值。使用左连接:

SELECT * 
FROM Student s LEFT OUTER JOIN City c -- OUTER 可以省略
ON s.FromCityId = c.Id

因为要保证左表全部显示,“陈晓斌”就一定会被保留。但保留之后,City里没值怎么办呢?用NULL填充:

如果是内连接的话,因为NULL值不会和City.Id里的1,2,3,4相等,所以结果就会没有“陈晓斌”这一行:

同理,为了展示右连接,我们可以在City表中添加一行:5/四川/成都,这个城市在Student表中没有任何对应。使用右连接:

SELECT * FROM Student s RIGHT JOIN City c --  省略了OUTER 
ON s.FromCityId = c.Id

因为要保证由表全部显示,“5/四川/成都”就一定会被保留(但FromCityId=NULL的“陈晓斌”也不会保留)。保留之后,Student里没值还是用NULL填充:

@想一想@:Student LEFT JOIN City是不是等于 City RIGHT JOIN Student?

那么最后,全连接就非常好理解了:

SELECT * FROM Student s FULL JOIN City c 
ON s.FromCityId = c.Id

结果为:

最后,我们来了解一下

交叉连接

SELECT * FROM Student s CROSS JOIN City c

这种连接会不进行任何过滤,依次:

  1. 把左边的表的第一行和右边全表连接
  2. 把左边的表的第二行和右边全表连接
  3. 把左边的表的第三行和右边全表连接
  4. ……
  5. 把左边的表的最后一行和右边全表连接

这种运算方式又被称之为笛卡尔乘积。其计算结果的行数非常大。同学们了解即可,一般不需要使用。

其他

演示:

  • 可以JOIN更多的
  • JOIN条件可以扩展,除了=,>、<、<>……都可以,只要能返回一个boolean值就行
  • 使用JOIN连接的表,可以像单表一样使用WHERE/ORDER/GROUP/HAVING……等操作

但是,注意不要

混用WHERE和ON

在INNER JOIN的时候,可能没有什么问题,比如这两句SQL是等效的:

SELECT * FROM Student s JOIN City c 
ON s.FromCityId = c.Id 
AND s.SName = N'幸龙泰' -- 使用的是ON中的AND  

SELECT * FROM Student s JOIN City c 
ON s.FromCityId = c.Id 
WHERE s.SName = N'幸龙泰' -- 注意WHERE必须在ON的后面

在外连接的时候,

使用WHERE子句结果仍然正常(符合我们的预期)

SELECT * FROM Student s LEFT JOIN City c 
ON s.FromCityId = c.Id 
WHERE s.SName = N'幸龙泰' 
但使用AND的结果就变得“诡异”起来:
SELECT s.SName, c.FromProvince, c.FromCity -- 为了结果清晰只显示关键字段 
FROM Student s LEFT JOIN City c 
ON s.FromCityId = c.Id AND s.SName = N'幸龙泰'

整张表,包括姓名不是“幸龙泰”的同学都被查询出来了,而且我们还注意到除了“幸龙泰”,关联的City表中的值全部为NULL!这是怎么回事?

从逻辑上来讲,在ON中定义的条件是用于“连接”的,不是用于对查询结果进行过滤的。所以,根据左外连接的规则:

  1. 左边的所有数据都必须保留,无论连接条件是否满足;
  2. 只是当连接条件不能满足的时候,右表字段值为NULL.

从而形成了上述查询结果。

另外,对比两条语句的执行计划,看起来都差不多:

但是,把鼠标停在Student的Clustered Index Scan上,你就会发现区别:

  • 使用的是AND

  • 使用的WHERE子句

可以看出,在Student上设置的WHERE子句其实在JOIN之前都执行了!\(^o^)/为了代码的可读性,我们推荐“两个总是”:

  1. 总是使用ON设立联接条件,
  2. 总是使用WHERE对联接之后的结果进行筛选。

自联接

就像外键不仅仅用于多张表的连接一样,JOIN也不仅仅用于连接其他表,我们还可以让表自己和自己联接。

和多表连接唯一的不同:连接时必须使用表别名。@想一想@:为什么?)

比如有这么一个需求:找到姓名重复的学生。我们就可以使用自连接:

SELECT a.Id, b.Id, a.SName,b.SName FROM Student a JOIN Student b -- Student表自己和自己相连接 
ON a.SName = b.SName -- “两表”连接的条件是名称相同 

观察一下,姓名重复的同学自连接之后的特征是什么?如何过滤?

WHERE a.Id <> b.Id -- 如果说没有重复,就不可能出现a.Id <> b.Id

@想一想@:如何删除重复的数据(只保留一条)


UNION

可以将多个查询结构纵向联接,只要这些查询结果列的数量、顺序相同,且每一列的数据类型能够兼容(可隐式转换)。

比如我们可以把学生的老师用一个查询结果显示出来,并按姓名有序显示:

SELECT  SName, Age, 'Student' AS Roles
FROM Student
UNION ALL -- 没有ALL,会清除重复行 
SELECT TName, Age, 'Teacher'
FROM Teacher
ORDER BY SName;  -- 不是TName

注意以下几个语法点:

  1. UNION后可以接ALL,也可以不接。不接ALL(默认),会清除重复行(所有列/字段都重复才算重复);接ALL,不会清除重复行
  2. 列名以第一个查询子句为准,所以也只能使用第一个SELECT语句的列名


作业

  1. 观察并模拟17bang项目中的:
    1. 用户资料,新建用户资料(Profile)表,和User形成1:1关联。用SQL语句演示:
      1. 添加一个填写了用户资料的注册用户
      2. 一条SELECT语句JOIN出用户基本信息和及其Profile内容
      3. 删除某个Id的注册用户
    2. 求助列表,在Problem和User之间建立1:n关联(含约束)。用SQL语句演示:
      1. 某用户发布一篇求助
      2. 一条SELECT语句JOIN出求助的标题和作者用户名
      3. 将该求助的作者改成名为fg的另外一个用户
      4. 删除该求助
      5. 删除该用户
    3. 发布求助:在Keyword表和Problem形成n:n关联(含约束)。用SQL语句演示:
      1. 发布了一个使用了若干个关键字(有的是新的,有的是数据库中已有的)的求助
      2. 求助更新,重新使用了一批关键字
      3. 删除该求助
      4. 删除该求助的某个关键字
  2. 使用JOIN查出
    1. 每个求助使用了多少关键字,每个关键字被多少求助使用
    2. 关键字大于3个的求助
    3. 从未发布过求助的用户    
    4. 所有发布一篇以上(不含一篇)文章的用户信息
  3. 17bang的关键字有层级关系,每一个关键字都可以有“上一级”:
    1. 请在表Keyword中添加一个字段,记录这种关系
    2. 然后用一个SELECT语句查出所有普通关键字的上一级、以及再上一级的关键字名称,比如:
  4. 17bang中除了求助(Problem),还有意见建议(Suggest)和文章(Article),他们都包含Title、Content、PublishTime和Auhthor四个字段,但是:
    1. 建议和文章没有悬赏(Reward)
    2. 建议多一个类型:Kind NVARCHAR(20))
    3. 文章多一个分类:Category INT)
    请按上述描述建表。@想一想@:能不能有“重用性”一点?然后,用一个SQL语句显示某用户发表的求助、建议和文章的Title、Content,并按PublishTime降序排列
  5. 用自己的话说明ER模型和三大范式。
学习笔记
源栈学历
大多数人,都低估了编程学习的难度,而高估了自己的学习能力和毅力。

作业

觉得很 ,不要忘记分享哟!

任何问题,都可以直接加 QQ群:273534701

在当前系列 SQL 中继续学习:

多快好省!前端后端,线上线下,名师精讲

  • 先学习,后付费;
  • 不满意,不要钱。
  • 编程培训班,我就选源栈

更多了解 加:

QQ群:273534701

答疑解惑,远程debug……

B站 源栈-小九 的直播间

写代码要保持微笑 (๑•̀ㅂ•́)و✧

公众号:源栈一起帮

二维码