3.1 SQL语言概述

3.1.1 SQL语言概念

3.1.2 SQL标准发展历史

• 20世纪70年代由IBM公司研制的SEQUEL语言演变出SQL语言

• 1979年ORACLE公司首先推出商用SQL

• 1986 美国国家标准局批准了SQL作为关系型数据库语言的ANSI标准。

• 1987年国际标准化组织(ISO)将其采纳为国际标准SQL86。

• ISO先后推出国际标准SQL-89、SQL-92、SQL:1999、SQL:2003、SQL:2006、SQL:2008、SQL:2011、 SQL:2016等

3.1.3 SQL应用情况

• Oracle

• Sybase

• DB2

• Microsoft SQL Server

• MySql

3.1.4 SQL语言特点

• 一体化

• 使用方式灵活

• 非过程化

• 语言语句简单

3.1.5 SQL对关系数据库的操作原理

SQL主要功能如下：

• 数据库对象创建、修改、删除（对数据库的操作）

• 数据库表的数据插入、修改、删除、查询、统计（对关系表的操作）

• 存储过程、触发器、函数等程序执行（对函数以及触发器的操作）

• 数据库权限、角色、用户等管理 （权限管理）

 

3.1.6 SQL语言语句类型

• 数据定义语言
• 数据定义语言（Data Definition Language，DDL）是SQL语言中用于创建、修改或删除数据库对象的语句（就是在操作数据库中对象的语言，如操作数据库，操作关系表，操作索引等）。下面是一些数据定义SQL的示例（示例不用背，只要知道哪些SQL语句是数据定义语言就好了）：
• CREATE DATABASE - 创建新数据库
• DROP DATABASE – 删除数据库
• ALTER DATABASE - 修改数据库属性
• CREATE TABLE - 创建新表
• ALTER TABLE – 修改数据库表结构
• DROP TABLE - 删除表
• CREATE INDEX - 创建索引
• DROP INDEX - 删除索引

• 数据操纵语言：
• 数据操纵语言（Data Manipulation Language，DML）是SQL语言中用于增添、修改、删除数据的语句（就是在操作数据库中数据的语言）。下面是一些数据操纵语言的示例（同样不用背）：
• INSERT - 向数据库表中插入数据
• UPDATE - 更新数据库表中的数据
• DELETE - 从数据库表中删除数据

• 数据查询语言：
• 数据查询语言（Data Query Language，DQL）是SQL语言中用于对数据库进行数据查询的语句。（如SELECT）

• 数据控制语言：
• 数据控制语言（Data Control Language，DCL）是用于对数据库对象访问权进行控制的SQL语句（只要把这里的控制理解为访问权的控制即可）。下面是一些数据控制语言的示例（同样不用背）：
• GRANT – 授予用户对数据库对象的权限
• DENY – 拒绝授予用户对数据库对象的权限
• REVOKE – 撤消用户对数据库对象的权限

• 事务处理语言：
• 事务处理语言（Transaction Process Language，TPL）是SQL语言中用于数据库内部事务处理的语句（事务处理语言是用来处理事务的很合理吧）。下面是一些事务处理语言的示例（不用背）：
• BEGIN TRANSACTION – 开始事务
• COMMIT – 提交事务
• ROLLBACK – 回滚事务

• 游标控制语言：
• 游标控制语言（Cursor Control Language，CCL）是SQL语言中用于数据库游标操作的语句（游标控制语言是用来处理游标的很合理吧）。下面是一些游标控制语言的示例（不用背）：
• DECLARE CURSOR – 定义游标
• FETCH INTO – 提交游标数据
• CLOSE CURSOR– 关闭游标

3.1.7 SQL语言的数据类型

• SQL语言基本数据类型
• 字符：CHAR（是固定长度字符串，长度不足时填充空格）、VARCHAR（长度可变字符串，也需要指定最大长度，但不会填充空白字符来达到最大长度，只有实际存储的字符会占用空间）、TEXT（存储大量文本数据，不需要在定义时指定长度）
• 整数：SMALLINT（16位有符号整数）、INTEGESQLR（32位有符号整数）
• 浮点数：NUMBER(n,d)、FLOAT(n,d)（都是表示数据位数为n（整+小），小数的位数为d。如果没有小数部分就自动补0，所以NUMBER（5,2）能表示的最大的数为999.99。另外需要注意FLOAT不一定支持FLOAT(n,d)这种写法）
• 日期：DATE（包含年月日）、DATETIME（包含年月日时分秒）
• 货币：MONEY

• 不同数据库支持的数据类型（数据库支持的类型应该是由DBMS决定的，因为数据库本身应该就是一个文件，并不能标识不同的数据类型，将数据库中的字节序转换为什么数据类型是由DBMS决定的）
这里只需要特别关注一下PostgreSQL中的主要数据类型即可
• PostgreSQL主要数据类型（这个可以去看cal的ppt能了解更多的数据类型）



需要注意的是，在 PostgreSQL 中，DECIMAL和 NUMERIC是完全相同的，并且同样需要使用NUMERIC（5,2）这样的形式来表示数据长度5位，小数部分为两位
• SQL Server主要数据类型
• MySQL主要数据类型

3.2 数据定义SQL语句

3.2.1 数据库创建SQL语句

• 语句基本格式

3.2.2 数据库修改SQL语句

• 语句基本格式

3.2.3 数据库删除SQL语句

• 语句基本格式

3.2.4 数据库表创建SQL语句

• 语句基本格式
这里提到了列完整性约束（实际上就是用户自定义完整性约束），这里稍微介绍一下一些SQL语句中的列完整性约束关键字

• 列完整性约束关键字(都是大写)
• PRIMARY KEY——主键
• NOT NULL——非空值
• NULL——空值
• UNIQUE——值唯一
• CHECK——有效性检查
• DEFAULT——缺省值

• 数据库表创建SQL语句实例
对下表：



创建数据库表的SQL语句为：
需要注意的是，
• 使用括号（）
• 在括号内部，每一行的结尾都需要加上逗号,除了最后一行。
• 并且创建表SQL语句结束需要加上分号
• 主键唯一且非空，PRIMARY KEY;必填字段，Not Null，其他都是Null



在这里并没有使用到所有的列完整性约束关键字，所以下面再额外介绍剩下的几个列完整性约束关键字的使用
PostgreSQL 约束 | 菜鸟教程
PostgreSQL 约束 PostgreSQL 约束用于规定表中的数据规则。 如果存在违反约束的数据行为，行为会被约束终止。 约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。 约束确保了数据库中数据的准确性和可靠性。 约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。 以下是在 PostgreSQL 中常用的约束。 NOT NULL：指示某..

https://www.runoob.com/postgresql/postgresql-constraints.html

• 其它的列完整性约束应用
• UNIQUE——值唯一
• CHECK——有效性检查
• DEFAULT——缺省值
下面给出一个使用了上面三个关键字的SQL语句例子：



• 可以发现实际上这三个关键字UNIQUE,CHECK,DEFAULT都是要在数据库表创建主键和非空/空的二级关键字
• CHECK关键字在前面说是有效性检查，正常使用的时候就是用来确定一个属性的值域
• 还有就是这三个关键字的使用格式，因为比较少使用，所以这里需要特别记一下，
• CHECK(… IN (…));
• UNIQUE
• NOT NULL DEFAULT(…)

• 表约束定义主键（主要是定义复合主键）
💡
使用列约束关键词PRIMARY KEY定义表的主键列只能定义单列主键
若要定义由多个列构成的复合主键，则需要使用表约束方式来定义
• 表约束定义主键语句基本格式
• 从这里可以看出表约束定义主键实际上就是将主键列合起来并且为其取了一个别名（也就是约束名）
如要创建下表的主键：



• 这里就使用了复合主键，主键由CourseID和TeacherID两列构成，并为这个复合主键取了一个别名CoursePlan_PK。同样的，由于比较少使用复合主键，所以表约束定义主键也比较少见，所以这里也需要特别记一下表约束定义主键的格式
• 原来需要填写主键的地方，不需要了，因为CONSTRAIN会统一赋予PRIMARY Key



 

• 使用表约束定义代理键
💡
先复习一下代理键是什么——首先代理键本身也是一个主键，只不过它是由数据库系统DBMS自动产生的（并且在写创建数据库表的SQL语句时并不会给代理键加上primary key关键字。而体现一个属性为代理键的关键就是这个属性的数据类型是自动编号，然后需要将代理键作为主键就一定要使用表约束来指定代理键为主键）。所以使用表约束定义代理键也就是使用表约束定义主键了
• 使用表约束定义代理键语句格式：
如要创建下表：



• 需要注意的点这里再强调一次，就是代理键的数据类型一定是serial（自动生成），那个具体的字段要求可以忽略。
• 并且如果要将代理键作为主键使用的话，就一定要使用表约束。
CONSTRAIN CoursePlan_PK Primary Key(CoursePlan_ID)

• 使用表约束定义外键
💡
同样的首先先来复习一下外键是什么。如果一个属性在某张关系表中是主键，并且它在另一张表中也出现了，那么就可以将这张表中的该属性定义为外键来表示表之间的关系。此外，外键还涉及到了参照完整性约,即维持数据一致性。
• 表约束定义外键语句格式：
• 这里能看出跟上面表约束定义主键以及表约束定义代理键不一样的地方就是使用的关键字不一样。上面的表约束定义主键和代理键的CONSTRAINT中使用的就是PRIMARY Key，而在定义外键中使用的就是FOREIGN Key。
• 并且在表约束定义外键中还需要定义这个外键是参考哪张表中的哪个属性REFERENCE (参照表名)（（参照属性名） ON DELETE CASCADE。
• 此外还可以增加字段ON DELETE CASCADE以表示当主键某个值被删除的时候对应的外键元组是否需要被连带删除
• 例如要创建下表：



则可以使用一个表约束定义代理键还有两个表约束定义外键来实现：
• 同样的，这里也是在表创建SQL语句中的每一行都是需要加上逗号的，除了最后一行
• 注意非必填字段，可以也不初始化NULL,但是最好初始化写上
• 这里再复习一下数据类型

• 使用表约束的优点
• 便于定义复合主键（因为使用primary key关键字只能定义单列主键。）
• 可命名主键约束（可以给主键取一个别名，如上面的CoursePlan_PK）
• 便于定义代理键（也就是表约束定义代理键）
• 声明外键（也就是表约束定义外键）

3.2.5 数据库表修改SQL语句

• 语句基本格式

• 主要修改方式
• ADD修改方式，用于增加新列或列完整性约束
• 后面部分实际上就是对应了数据库表创建中的一行
• 这里还有一个小坑，如果需要添加的列有非空完整性约束的话，那么在表中已经有数据的情况下他就不能直接添加。因为如果已经添加了的话就会导致原有数据中的这一列不满足非空的完整性约束。





• 所以操作应该是先创建一个可以为空的属性列，将已有数据的所有该属性的值都填写了之后再给这一列添加列完整性约束。所以这里写的是可以添加<新列名称><数据类型>或者[完整性约束]（这个完整性约束就是指表约束）。并且根据copilot的提示，这里表约束是可以使用除了NOT NULL的所有关键字的（如何添加一个非空约束下面会介绍）。如创建一个唯一值表约束：
• DROP修改方式，用于删除指定列或列的完整性约束条件
需要注意的就是这里需要提供删除的对象是什么（是列还是约束）。并且这里将表约束称为完整性约束好像没有什么问题，因为表约束中定义的就是主键、代理键、外键，就对应了实体完整性约束以及参照完整性约束。
• RENAME修改方式，用于修改表名称、列名称
这里跟修改数据库的名称是一样的，都是使用rename to关键字。如果RENAME TO放在一起就是表示要修改数据库或数据库表的名称；如果是分开的话就要使用格式：rename 原列名 to 新列名
• ALTER修改方式，用于修改列的数据类型
需要通过COLUMN指定需要修改的列，并通过TYPE来指定新的数据类型
根据copilot的提示，如果要给一个列加上一个非空约束，需要使用下面的SQL语句：
需要注意的是这里ALTER搭配了SET关键字使用
需要注意的是这里的DROP和ALTER都是加上COLUMN关键字的

3.2.6 数据库表删除SQL语句

• 语句的基本格式

3.2.7 索引简介

• 索引（Index）是一种按照关系表中指定列的取值顺序组织元组数据存储的数据结构，使用它可以加快表中数据的查询访问。（牺牲空间赢得时间）。为了便于理解索引的原理，下面摆上一张图：

• 索引的作用：支持对数据库表中数据快速查找，其机理类似图书目录可以快速定位章节内容

• 索引的优点（说白了就是加快了处理速度）：
• 提高数据检索速度
• 可快速连接关联表
• 减少分组和排序时间

• 索引的开销（就是牺牲空间还有开销的问题）
• 创建和维护索引都需要较大开销
• 索引会占用额外存储空间
• 数据操纵因维护索引带来系统性能开销

3.2.8 索引创建SQL语句

• 语句基本格式

3.2.9 索引修改SQL语句

• 语句基本格式
• 例如，索引名称修改语句格式如下：

3.2.10 索引删除SQl语句

• 语句基本格式

3.4 数据查询SQL语句

3.4.1 数据查询SQL语句格式

• 语句格式

3.4.2 从单个表读取指定列

• 语句基本格式

3.4.3 从单个表读取指定行

• 语句格式

3.4.4 从单个表中读取指定行和列

• 语句的基本格式

3.4.5 WHERE子句

• 在WHERE子句中还可以使用逻辑运算符（在SQL语句中是关键字）AND、OR、NOT以及IN或NOT IN关键词（这个是用来限定取值范围的）
如要从STUDENT表中查询性别为“男”，并且专业为“软件工程”的学生数据：
还有一个使用IN关键字的例子（从STUDENT表中查询“计算机应用”专业的学生）：

3.4.6 对结果集进行排序

• 在SELECT查询语句返回的结果集中,行的顺序是任意的。如果需要结果集排序，可以在SELECT语句中加入ORDER BY关键字。

• 例：若要从STUDENT表中按学生出生日期降序输出学生数据，其数据查询SQL语句如下。

• 在默认情况下，SQL查询的结果集是按指定列值的升序排列。可以使用关键词ASC和DESC选定排序是升序或降序。

• 如果结果集需要按多个列排序，可以分别加入关键字ASC或DESC改变。

3.4.7 SQL内置函数

• 聚合函数（如COUNT等）

• 算术函数

• 字符串函数

• 日期时间函数

• 数据类型转换函数

3.4.8 SQL聚合函数

• 聚合函数概念：聚合函数是一些对关系表中数值属性列进行计算并返回一个结果数值的函数（就是将很多东西聚合成一个数据）
• 常见的聚合函数有：



除了COUNT()计算结构集行数，其他都是关于列的函数。
• 例1 若要统计Student表中的学生人数，在SELECT语句中可以使用COUNT（）函数来计算，其查询SQL语句如下：
• COUNT关键字记录的是指定列非空的行数，这个在进行外连接的时候很重要。另外，需要注意的是这里的AS关键字就相当于是给COUNT出来的结果取了一个别名。执行结果如下：



• 例2:找出STUDENT表中年龄最大和年龄最小的学生出生日期，其查询SQL语句如下：



• 多个聚合函数的使用之间就使用逗号隔开，就好像之前选取多个列一样
• 这里就可以稍微讨论一下SQL语句的执行顺序了。因为聚合函数都是对结果集进行的操作，所以不难发现，实际上先是执行SELECT后的部分（比如FROM什么的），最后再执行SELECT语句从经过其他子句处理的结果视图中选取对应的属性列或者调用聚合函数

3.4.9 SQL分组统计

• SQL语句中的分组统计通常是通过Group By子语句和SQL内置函数中的聚合函数实现的（这个时候聚合函数就是对每个分组进行聚合了。或者理解为GROUP BY的结果集是一堆表格，COUNT函数需要作用在每一张表格上。还记得Group By子语句要放在SELECT语句的哪里吗？是放在WHERE子句后，在ORDER BY子句前）

• 分组统计SQL语句基本格式：
• 需要注意的是在GROUP BY的基础上还能再使用一次Having子句对GROUP BY的结果进行条件筛选
• 例1:若要分专业统计Student表中的学生人数。在SELECT语句中可以使用GROUP BY分组子句完成统计，其查询SQL语句如下：
• 需要注意的是这里的COUNT是对结果集进行的聚合，需要理解SQL语句的执行顺序。以上面的SQL语句为例，首先先执行FROM，经过FROM后结果集为Student表，然后执行GROUP BY，执行结束后结果集应该为两张表（是根据Major）进行分组的。
• 然后执行SELECT行。首先先是将两张表的Major取出作为专业（这个时候一张表中的所有数据的Major都是一样的，所以一张表只取出一个Major），然后对每个组进行在StudentID上的统计（也就是计算每个分组的学生人数）。
• 由于最终选取出的视图就应该只有两行两列（专业和学生人数）。另外这里的COUNT（StudentID）可以替换为COUNT（*），因为COUNT函数是不考虑去重的。



或许到这里就想知道如何统计专业的数量（因为像上面一样分组了再使用COUNT函数就是统计各个分组的结果了）：
 
只需要加上一个DISTINCT关键字即可。同样来理一下执行顺序。首先执行FROM，结果集为Student，然后执行DISTINCT（因为括号的存在），使得Student表被投影到Major属性上（因为是投影，所以要去重），结果集为若干行一列（就是Major列），然后在Major上统计结果集中的行数，就计算出了专业的数量
下面再给出一个比较复杂的例子（若要分专业统计STUDENT表中男生人数，但限定只显示人数大于2的人数）：
同样的这里SQL 语句的执行顺序也是先按顺序执行SELECT后的语句，然后再执行SELECT行。这里最重要的应该是怎么写出来这个SQL语句。首先要先明白条件的先后顺序。首先是要分专业统计，然后还要求是男生，那么就一定要先使用WHERE语句满足男生条件才能进行分组（因为WHERE子句是在分组子句之前的。这与现实是不一样的，在现实中可以先选出男生再统计专业，也可以先选出专业再统计男生），分组之后再使用HAVING子句满足数量大于2的条件即可

• Group by与having理解
• group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面或者包含在having 后的聚合函数里（可以理解为GROUP BY子句是将结果集投影到给定的属性列上，其他的属性只对聚合函数可见）。疑问这个HAVING怎么理解？并且只有当使用了GROUP BY子句之后，列的名称（这个时候经过GROUP BY后列就变成了聚合列了）才允许和内置函数一起混合使用（是因为如果没有使用GROUP BY的话聚合函数的结果行数就不能和列行数对应了）
下面有一个混合使用的反例：
这里就是因为MaxHours列不是一个聚合列，所以查询出来的结果会使得聚合函数结果行数与该列的行数不对应。如果在SELECT子句后面只使用了SUM(MaxHours)，那么就是一个正确的查询
• where 子句的作用是在对查询结果进行分组前将不符合where条件的行去掉，即在分组之前过滤数据（条件中不能包含聚合函数）。
下面是一个反例：
这个查询语句不规范是因为在WHERE子句中使用了聚合函数。但是像上面的这种情况应该是会经常出现的。下面给出一种解决方法（使用子查询）：
这样就避免了在WHERE子句中使用聚合函数
• having 子句的作用是筛选满足条件的组（也就是限制的是组，而不是元组），即在分组之后过滤数据（条件中经常包含聚合函数）。
给上面做一个总结，实际上只要将GROUP BY子句当成投影操作，其他属性列仅对聚合函数可见（还有行信息。甚至不对紧跟在他后面执行的HAVING子句可见），聚合函数只能和聚合列（聚合列就是CROUP BY后指定的属性列）一同展示。并且聚合函数只能使用在SELECT和HAVING中

3.4.10 子查询实现多表关联查询

• 语言格式

• 例题：在选课管理系统数据库中，希望能检索出“计算机学院”的教师名单。

3.4.11 使用连接关联实现多表关联查询

• 基本格式

• 例子：在选课管理系统数据库中，希望获得各个学院的教师信息列表，包括学院名称、教师编号、教师姓名、教师性别、职称等信息。要求按学院名称、教师编号分别排序输出，其查询SQL语句如下：

3.4.12 JOIN …ON关键字实现多表查询

• 语句格式

3.4.13 外部连接

• LEFT JOIN: 左外连接，即使没有与右表关联列值匹配，也从左表返回所有的行（行数取的是左表的行数）。

• RIGHT JOIN: 右外连接，即使没有与左表关联列值匹配，也从右表返回所有的行（行数取的是右表的行数）。

• FULL JOIN: 全外连接，同时进行左连接和右连接，就返回所有行（这个就与内连接完全相反，行数取的是两张表中行数大的）。

• 如果要使用这些连接就只需要将上面使用JOIN关键字连接替换为对应的关键字即可（如LEFT JOIN等）。

3.6 视图SQL语句

• 什么是视图？
• 视图——是一种通过基础表或其它视图构建的虚拟表。它本身没有自己的数据，而是使用了存储在基础表中的数据。

3.6.1 视图创建SQL语句

• 例子：在选课管理系统数据库中，若需要建立一个查看基础课数据的视图BasicCourseView，其创建SQL语句如下。

• 例子：使用SELECT语句查询该视图数据，并按课程名称排序输出，其SQL语句如下：

3.6.2 视图删除SQL语句

• 语句基本格式

3.6.3 SQL视图应用

• 提供一定程度的数据逻辑独立性：当数据表结构发生改变，只要视图结构不变，应用程序可以不作修改

• 集中展示用户所感兴趣的特定数据：通过视图，可以将部分用户不关心的数据进行过滤，仅仅提供他们所感兴趣的数据

3.7 数据库实践