【学习笔记】数据库系统原理 第三章 SQL语言

以下内容为参考课件和《数据库系统概论》（第5版，王珊等著）的个人整理，若有错误欢迎指出

第三章 SQL语言

一、概述

1、特点：

• 综合统一，集数据定义、操纵等于一体
• 高度非过程化，用户只需要提出要求，无需关心怎么做
• 面向集合的操作方式
• 以同一种语法结构提供两种使用方式（可独立操作，也可以嵌入其他高级语言）
• 语言简洁易学

2、SQL对关系数据库模式（第一章提及的三级模式）的支持

关系数据库中一个关系对应一个基本表。视图则是从一个或多个基本表中导出的表，数据库中仅存放视图的定义但不存放数据，数据仍在基本表中。

以下SQL语句均是比较通用的，不同的DBMS可能有自己的方言

二、数据查询功能

1、查询语句

Select [ALL|DISTINCT]<目标表达式>[{,<目标表达式>}]
From <表名或视图名>[{,<表名或视图名>}]
[Where <条件表达式>]
[Group By <分组列> [{,<分组列>}][HAVING <条件表达式>]]
[Order By <排序列> <排序方式>[{,<排序列> <排序方式>}]

（1）Select子句：投影，选择属性（列）或者 其表达式（运算、函数等）

• 用*表示全部属性

• Distinct表示去重，默认为All

• 如果是直接放常量，则表示在得到的结果中加上值为常量的一列

  例：Select “xxx” School, Sname, 2014-Sage, iSLOWER(Sdept) From Student；

  这里School属性是表中原本没有的，操作后是得到的查询结果全加上这一列，且值为"xxx"。不写列名School也是可以的，只是这列将没有列名

（2）Where子句：条件

一些用法：

Select Sno From Student Where Sage BETWEEN 2015 AND 2019;
-- BETWEEN AND 包括上下界，可改写为<= AND >=
Select Sno From Student Where Sname IN ('xx', 'yy');
-- 相当于Sname = 'xx' OR Sname = 'yy'
Select Sno From Student Where Sname Like '刘%'；
-- % 表示0个或多个字符，_ 表示一个字符；如果要匹配普通字符%或_，要转义'\%'或‘\_’，并在语句最后加 ESCAPE '\' 

注意：查空值要用IS NULL而不能用= NULL，因为空值是区别于任何取值的，是未知的。

在数据库中NULL是未知的，很多时候需要额外考虑，避免出错

（3）Order by子句：排序

BOrder By 属性1 Asc/Desc, 属性2 Asc/Desc...

Asc/Desc：升序/降序

多个属性时，指先按属性1排，再按属性2排，以此类推

（4）聚集函数

• Count() 求元组个数
• Sum() 对数值列求总和
• Avg() 求数值列的平均值
• Max() 求最大值
• Min() 求最小值

注意：（1）只能用于Select子句、Group By 的Having子句，Where中不能用。（2）如果遇到NULL，会跳过该值。（3）类似的，Count(Distinct xx)表示去重，默认为All。

（5）Group By子句：分组

对于Group By attributA，会先把attributA相同的分为一组，然后对每一组进行操作。

• 使用Group By对Select子句的要求

  只能出现Group By中的属性 或者 聚合函数

  理解：

  执行Group By attributA后，会把原始表按照attributA值生成若干张虚表，每张虚表只有一行，attributA字段为唯一值，其他字段内则有被分到这里的元组的对应字段的所有值（也就是一行有多个值）。所以在Select其他字段时可以（也只能）用聚合函数。

  若Group By后有多个字段，则表示以这些字段构成的元组来划分。

• Having子句：选择满足条件的组

  • 有Group By才能用
  • Group By总是在Where之后执行的（SQL有特定的执行顺序），而且Where是对每一行进行选择，因此如果要筛选条件中需要用到组的性质（比如选总分大于x的同学，而每一行只是一门成绩，就要分组求和），要用Having。

2、连接查询

（1）等值连接

为避免多个表中存在重复列名，使用表名.属性；然后在Where中写连接条件（表名1.属性1=表名2.属性2）。

也可以用Join...On...，如：

Select Student.*, SC.* From Student Join SC On Student.Sno = SC.Sno;

也可以多表连接，写清楚连接条件即可

（2）自身连接

First，Second相当于给表起别名，如：

Select Fisrt.Cno, Second.Cpno From Course First, Course Second Where First.Cpno = Second.Cno;

（3）外连接

以左外连接为例：

Select Student.Sno From Student Left Outer Join SC On (Student.Sno = SC.Sno);
-- 左外连接，即会保留Student表中没被连接的悬浮元组
Select Student.Sno From Student, SC Where Student.Sno=SC.Sno(*);
-- 语义和上述一样，其中 * 为oracle数据库的方言

3、嵌套查询

一个SELECT-FROM-WHERE是一个查询块，可以将一个查询块嵌套在另一个查询块的WHERE 或HAVING子句中（常见用=、Exists、Any、All等连接子块），用括号括住。

分类：

• 不相关子查询：子查询可以单独执行，即不依赖于上一级查询块。此时的执行顺序是由内（子）向外（父），只执行一次。

• 相关子查询：子查询依赖于上一级查询。此时的执行顺序是先外再内，一般执行多次。

-- 不相关子查询例子
Select Sname, Sage From Student
Where Sdept = ‘CS’ And 
          Sage < Any(Select Sage From Student
                     Where Sdept = ‘IS’);
-- 相关子查询例子，其中子查询中的Sno来自上一级查询的Student表
-- 此语句先在Student中取元组，然后执行子查询，若返回值非空就被选择
Select Sname From Student
Where Exists (Select * from SC 
               Where Sno=Student.Sno And Cno=‘1’);

实际上，子查询可以用在所有需要数据的地方，如from后，就相当于把查询结果当作一张新表。

4、集合查询

并集：... Union ...

交集：... Intersect ...

差集：... Except ...

当然也可以用 Where 或 嵌套查询 完成上述功能

三、数据更新

1、插入新数据

Insert Into <表名> [(<属性列>[{,<属性列>}])] 
					Values(<值>[{,<值>}]);
-- 1.value部分也可以直接是子查询结果
-- 2.如果省略属性列名，默认插入一个元组

2、修改数据

Update <表名>
  		Set <列名>=<表达式>[{, <列名>=<表达式>}]
  		[Where <条件>];
-- where是要进行修改的数据(元组)的条件

3、删除数据

Delete From <表名> [Where <条件>]
-- 删除以元组为单位

四、定义功能

1、创建基本表

Create Table <表名>(
    <列名>  <数据类型>  [<列级完整性约束>]
    [{,<列名> <数据类型> [<列级完整性约束>]}]
    [{, [<表级完整性约束>]}]
);

数据类型：

• char（n）：固定长度的字符串。
• varchar（n）：可变长字符串。
• int：整数。
• smallint：小整数类型。
• numeric（p，d）：定点数共p位，小数点右边q位。
• Real, double precision ：浮点数与双精度浮点数,精度与机器有关。
• Float(n)：n位的精度浮点数。
• date：日期（年、月、日）。
• time：时间（小时、分、秒）。
• interval：两个date或time类型数据之间的差

列级完整性约束

• NULL/NOT NULL：允许为空/非空
• UNIQUE：值在一列中唯一，对应候选码

表级完整性约束

• PRIMARY KEY（…）：主码，值唯一且非空；可以是多个属性，若是单个属性可以写到列级那里。

• FOREIGN KEY（…）references Tablename(…)：外码

• CHECK（…）：对数据取值的约束

2、修改基本表

Alter Table <表名>
Add(<新列名><数据类型>[<完整性约束>]);
-- 插入一列，但在已有数据的情况下，应允许新加入列取空值
或 Drop <完整性约束名>;
-- 删除某种完整性约束
或 Modify(<列名><数据类型>);
-- 修改某列的数据类型

这里不允许使用Drop删除某一列，是防止删除这一列导致一些程序出错

3、删除基本表

Drop Table <表名>;

与之前删除数据的区别：删除数据以元组为单位，全删完后剩空表；此处是删掉整个表。

4、索引

为加速大量数据下的查询效率，可以添加索引。DBMS会为数据文件创建索引文件，这也意味着在更新数据时要维护索引文件。一般顺序索引为按照某（些）属性顺序排列，索引文件包含属性值和元组指针。

-- 创建：Unique是指索引的每一个索引值只对应一个元组；次序为ASC/DESC
Create [Unique][Cluster] Index <索引名> 
  On <表名>
 (<列名>[次序][, <列名>[次序]] …);
-- 删除；当索引多次更新时可能产生磁盘碎片，因此删除重建可能可以提高效率
Drop Index <索引名>;

5、视图

视图是根据一定条件从若干个基本表导出的虚表。它能够简化用户的操作，并且以多种角度看待同一数据，也提供了安全保护（权限）；同时为重构数据库提供了一定程度的逻辑独立性（即增加字段时，视图由于将表垂直划分，所以不受影响）。

-- 创建：这里子查询是指视图中数据的约束条件，即select...from...where；with check option是指数据增删改都需要满足约束条件，即只能对符合要求的数据进行
-- 可以在视图上再建立视图
-- 视图消解：DBMS执行对视图的查询时，会把视图定义中的子查询与用户查询合并
Create View <视图名> [(<列名>[,<列名>] …)]
    As <子查询>
   [With Check Option];
-- 删除
Drop View <视图名>;
-- 在视图中更新数据：与数据更新的语句一致，DBMS会转化成对基本表的更新，视图中不涉及的列会设置成空值
-- 能够以进行视图更新的约束：（创建视图的）子查询中，select语句中不能包含聚集函数、distinct、算数表达式计算的列，不能有group by子句。因为上述都无法具体推断出要插入的数据的值（如聚合函数是统计量，无法推断出个体值）
-- 相关规则还有很多，但一般而言，行列子集的视图（从单个表使用选择、投影导出，并包含主码）时可更新的
Insert Into <视图名> values(...);