1. SQL概述
SQL(Structured Query Language,结构化查询语言)是一种用于定义、查询、更新和控制关系数据库的标准化语言。
-
它包含了数据定义语言(DDL)、数据操纵语言(DML)、数据查询语言(DQL)、多个方面的功能。
SQL的设计初衷是让用户能够以一种高效、简洁的方式与数据库交互,实现数据管理和操作。
SQL是—个通用的、功能极强的关系数据库语言
2. SQL的特点
-
综合性:SQL集成了数据定义(DDL)、数据操纵(DML)、数据查询(DQL)功能
- 可以独立完成数据库生命周期中的全部活动:
- 定义、修改和删除
- 查询和更新
- 重构和维护
- 安全性、完整性控制,以及事务控制
- 用户数据库投入运行后,可根据需要随时修改模式,不影响数据的运行
- 数据操作符统一
- 可以独立完成数据库生命周期中的全部活动:
-
非过程化:SQL是一种声明性语言,用户只需指定“做什么”,而不必关心“怎么做”
-
即无需定义数据的存取路径,数据的查询和操作由数据库管理系统(DBMS)自动优化完成。
-
非关系数据模型的数据操纵语言“面向过程',必须制定存取路径
-
存取路径的选择以及SQL的操作过程由DBMS自动完成。
-
-
面向集合:
-
层次、网状模型采用面向记录的操作方式,操作对象是—条记录
-
SQL的操作对象是集合(元组的集合)
-
-
统一的语法结构:SQL具有统一的语法规则,既可以作为独立的语言,也可嵌入到高级编程语言(如C、C++、Java)中使用,提供了高度的灵活性。
-
简洁易学:SQL语言相对简洁,核心功能主要由9个关键命令组成(如SELECT、INSERT、UPDATE、DELETE、CREATE、DROP等),使其易于学习和掌握。
-
视图(view)
-
视图是—个虚表,从—个或几个基本表导出的表
-
数据库中只存放视图的定义而不存放视图对应的数据
-
-
基本表(basetable)
- 本身独立存在的表,一个关系就对应一个基本表
- 一个(或多个)基本表对应一个存储文件
- 一个表可以带若干索引,索引也存放在存储文件中
-
存储文件(storedfile)
- 存储文件的逻辑结构组成了关系数据库的内模式
- 物理结构是任意的,对用户透明
3. 数据定义(DDL)
-
数据库
-
数据文件
主要数据文件(.mdf) (只有一个)
次要数据文件(.ndf) (可以有多个)
-
事务日志文件
-
-
SQL的数据定义功能
- 数据库模式定义(Schema)
- 表定义(Table)
- 视图(View)和索引(Index)的定义
3.1 数据库的定义与删除
创建数据库
使用CREATE DATABASE
语句,例如:
CREATE DATABASE SchoolDB;
删除数据库
使用DROP DATABASE
语句,例如:
DROP DATABASE SchoolDB;
3.2 表的定义
创建表
通过CREATE TABLE
语句定义表的结构、列名、数据类型及约束条件。例如:
CREATE TABLE Students (
StudentID CHAR(8) PRIMARY KEY, commit '主码' '<列级完整性约束条件>'
Name VARCHAR(50) NOT NULL, commit '非空'
BirthDate DATE UNIQUE, commit '取唯一值'
Major VARCHAR(50),
FOREIGN KEY (Cpno) REFERENCES Course(~no) commit'<表级完整性约束条件>'
);
- PRIMARY KEY与UNIQUE的区别?
- 可使用UNIQUE约束确保在非主键列中不输入重复值。
- 一个表可定义多个UNIQUE约束,而只能定义—个PRIMARY
- 允许空值的列上可以定义UNIQUE约束,而不能定义PRIMARY KEY约束。
修改表
-
增加列和完整性约束
- 语法:
ALTER TABLE <表名> ADD <新列名> <数据类型> [完整性约束]
- 示例:
ALTER TABLE Student ADD S_entrance DATE;
解释:向Student表增加名为S_entrance的列,数据类型为DATE
,新列默认值为空。
- 语法:
-
删除列
- 语法:
ALTER TABLE <表名> DROP COLUMN <列名> [CASCADE | RESTRICT]
- 示例:
ALTER TABLE Student DROP COLUMN Sage CASCADE;
解释:删除Student表中的Sage列,若使用CASCADE
,引用该列的对象也会被删除;若使用RESTRICT
,则当该列被引用时会拒绝删除。
- 语法:
-
删除约束条件
- 语法:
ALTER TABLE <表名> DROP CONSTRAINT <约束名> [CASCADE | RESTRICT]
- 示例:
ALTER TABLE Student DROP CONSTRAINT PK_Student;
解释:删除Student表上的主键约束PK_Student
。
- 语法:
-
修改列定义
- 语法:
ALTER TABLE <表名> ALTER COLUMN <列名> <新数据类型>
- 示例:
ALTER TABLE Student ALTER COLUMN Sage INT;
解释:将Student表中Sage列的数据类型从字符型改为整型,这可能会影响已有数据。
- 语法:
-
增加表级完整性约束
- 语法:
ALTER TABLE <表名> ADD <表级完整性约束>
- 示例:
ALTER TABLE Course ADD NOT NULL(Cname);
解释:为Course表中的Cname列增加非空约束。
- 语法:
注意事项:
- 增加的列默认值为
NULL
,即使已有数据存在。 - 修改数据类型时需谨慎,可能导致数据丢失或冲突。
删除基本表
-
RESTRICT:删除表有约束。如果表被其他对象引用,无法删除。
- 例如:表存在外键关系或依赖对象时,系统会拒绝删除。
-
CASCADE:没有删除限制。删除表时,依赖该表的对象(如索引、视图、触发器)也一并删除。
-
默认行为:如果不指定,默认为
RESTRICT
。
示例:
DROP TABLE Student CASCADE;
- 基本表定义被删除,数据被删除
- 表上建立的索引、视图、触发器等—般也将被删除
3.3 模式的定义
-
关系数据库管理系统提供层次化的数据库对象命名机制 -
一个关系数据库管理系统的实例(Instance)中可以建立多个数据库
—个数据库中可以建立多个模式
—个模式下通常包括多个表、视图和索引等数据库对象
-
定义模式实际上就定义了—个命名空间
-
简而言之,模式就是数据库对象的集合
创建模式
语法:
CREATE SCHEMA schema_name AUTHORIZATION user_name;
schema_name
:指定要创建的模式名称。- 创建模式必须具有DBA权限,或获得了DBA授予的CREATE SCHEMA权限。
AUTHORIZATION user_name
:指定拥有该模式的用户,表示该用户对模式拥有所有权限。
示例:
CREATE SCHEMA School AUTHORIZATION admin;
该示例为用户admin
创建了一个名为School
的模式。School
模式可以包含与学校管理相关的数据库对象(如学生表、教师表等)。
删除模式
语法:
DROP SCHEMA schema_name [CASCADE | RESTRICT];
CASCADE
:删除模式时,自动删除模式中包含的所有对象。RESTRICT
:如果模式中包含对象(下属的数据库对象(如表、视图等)),则拒绝删除操作(默认行为)。
示例:
DROP SCHEMA School CASCADE;
该示例删除了School
模式以及模式中定义的所有对象(如表、视图、索引等)。
3.4 模式与表
每—个基本表都属于某—个模式,—个模式包含多个基本表
定义基本表所属模式
-
方法一:在表名中显式指定模式名
直接在表名中给出模式名,创建表时指定所属的模式。Create table "S-C-SC".Student(...);
// Student 表所属的模式是 S-C-
-
方法二:在创建模式时同时创建表
在同一个创建语句中创建模式和表。这样表会自动属于创建的模式。 -
方法三:设置默认所属模式
若没有指定模式,系统根据当前的搜索路径确定表所属的模式。- 系统会使用搜索路径中第一个存在的模式作为数据库对象的默认模式。
- 如果路径中指定的模式不存在,系统会报错。
搜索路径:
- 查看当前搜索路径:
SHOW SEARCH_PATH;
- 默认值:
$user, PUBLIC
在创建表时,若未明确指定模式,系统会按照搜索路径的顺序查找合适的模式。
3.5 数据类型
- SQL中域的概念用数据类型来实现
- 定义表的属性时需要指明其数据类型及长度
以下是将SQL的数据类型和属性分为两个详细表格:
数据类型表格
数据类型 | 描述 | 使用格式 |
---|---|---|
整数类型 | 存储整数值,分为tinyint , smallint , mediumint , int , bigint 。 | INT(5) :括号内数字表示显示宽度。 |
小数类型 | 存储带精度的小数值,分为float , double , decimal 。 | DECIMAL(10, 2) :10位数字,2位小数。 |
字符串类型 | char 为定长字符串,varchar 为变长字符串,text 存储大段文字。 | VARCHAR(255) :最大255个字符。 |
二进制类型 | blob 存储二进制数据,常用于图像和文件等。 | BLOB :存储最大64KB的数据。 |
日期/时间类型 | year , timestamp , date , datetime , time 用于存储日期和时间。 | DATETIME :存储日期和时间。 |
属性表格
属性 | 描述 | 使用格式 |
---|---|---|
NULL | 允许字段为空值,默认不允许为空。 | column_name INT NULL |
DEFAULT | 设置字段的默认值。 | DEFAULT 'default_value' |
PRIMARY KEY | 用于唯一标识记录,不可重复。 | PRIMARY KEY (column_name) |
AUTO_INCREMENT | 自动为新记录生成唯一的递增值。 | AUTO_INCREMENT |
UNIQUE | 确保字段值唯一,防止重复。 | UNIQUE (column_name) |
COMMENT | 为字段添加注释。 | COMMENT 'This is a comment' |
3.6 索引
-
谁可以建立索引?
- 数据库管理员(DBA)或表的所有者(即建立该表的人)可以创建索引。
- 数据库管理系统(DBMS)通常会自动在以下列上建立索引:
- PRIMARY KEY(主键)
- UNIQUE(唯一键)
-
谁维护索引?
- 索引的维护工作由DBMS自动完成。
-
谁使用索引?
- DBMS会自动决定是否使用索引以及使用哪些索引。
建立索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名> [<次序>] [, <列名> [<次序>]]…);
- <表名>:需要建立索引的基本表的名称。
- <列名>:索引可以建立在表的一列或多列上,列名之间用逗号分隔。
- <次序>:指定索引值的排列顺序,升序(ASC)或降序(DESC)。默认值为升序(ASC)。
- UNIQUE:表示此索引是唯一索引,每个索引值只对应唯一的数据记录。
- CLUSTER:表示要创建的是聚簇索引。
-
Student表按学号升序建立唯一索引:
CREATE UNIQUE INDEX Stusno ON Student(Sno);
-
Course表按课程号升序建立唯一索引:
CREATE UNIQUE INDEX Coucno ON Course(Cno);
-
SC表按学号升序和课程号降序建立唯一索引:
CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);
唯一索引
- 已包含重复值的列不能建立UNIQUE索引。
- 对某列建立UNIQUE索引后,DBMS会在插入新记录时自动检查该列是否包含重复值,这相当于增加了UNIQUE约束。
聚簇索引
-
建立聚簇索引后,基本表中的数据将按指定的聚簇属性值的顺序(升序或降序)存放。即,聚簇索引的顺序与表中记录的物理顺序一致。
-
示例:
CREATE CLUSTER INDEX Stusname ON Student(Sname);
在此例中,Student表的Sname列上建立了聚簇索引,Student表中的记录将按照Sname列的升序存储。
修改索引
语法格式:
ALTER INDEX <旧索引名> RENAME TO <新索引名>;
-
示例3.14:将SC表的SCno索引名修改为SCSno:
ALTER INDEX SCno RENAME TO SCSno;
删除索引
语法格式:
DROP INDEX <索引名>;
-
删除索引时,系统会自动从数据字典中删除该索引的描述信息。
-
示例3.15:删除Student表的Stusname索引:
DROP INDEX Stusname;
-
如果需要修改索引,除了使用
ALTER INDEX
,还可以先删除旧索引,再创建新索引的方式进行修改。
索引的优缺点
优点:
- 保证数据的唯一性:通过创建唯一索引,可以确保每条数据记录的唯一性。
- 加快数据检索速度:索引能够显著提高查询性能,尤其是在需要频繁检索、分组或者排序数据时。
- 提高表连接效率:索引在表与表之间的连接操作中能加速查询。
缺点:
- 创建与维护索引耗时:系统在创建和维护索引时需要花费一定的时间和资源。
- 占用存储空间:每个索引都需要额外的存储空间。
- 影响数据操作效率:虽然索引加快了检索速度,但在插入、修改或删除数据时,由于索引也需要更新,可能会消耗额外的时间。
3.7 数字字典
数据字典是关系数据库管理系统(RDBMS)中的一组系统表,它用于记录数据库中的各种定义信息,包括:
- 关系模式定义:描述数据库中各表的结构和属性。
- 视图定义:记录数据库中视图的结构和相关信息。
- 索引定义:保存关于数据库中已创建的索引的信息。
- 完整性约束定义:记录数据库中各类约束条件,如主键、外键和唯一性等。
- 用户权限:管理各类用户对数据库的操作权限,控制访问权限。
- 统计信息:存储与数据分布、查询性能等相关的统计数据。
执行SQL中的数据定义语言(DDL)语句时,实际上是对数据字典中的相应信息进行更新。
此外,数据字典中的信息是查询优化和处理过程中非常重要的参考依据,它帮助数据库管理系统提高查询效率。
4. 数据查询
下面是重写后的查询语句部分:
4.1 基本查询语句格式
SELECT [ALL | DISTINCT] <目标列表达式> [, <目标列表达式>] …
FROM <表名或视图名> [, <表名或视图名>] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名> [ ASC | DESC ] ] ;
解释:
SELECT
子句:用于指定要显示的属性列。FROM
子句:指定查询的数据来源(表或视图)。WHERE
子句:用于指定筛选条件。GROUP BY
子句:用于根据某列的值对查询结果进行分组。通常与聚合函数一同使用。HAVING
子句:对分组后的数据进行筛选。ORDER BY
子句:对查询结果进行排序,可选择升序(ASC)或降序(DESC)。
4.2 单表查询
- 注意事项:
- 使用
IS NULL
或IS NOT NULL
判断列是否为空。 IS
不能替代为=
。- 在使用
GROUP BY
后,SELECT
子句中只能出现分组属性和聚合函数。 HAVING
和WHERE
的区别:WHERE
子句作用于基表或视图,筛选满足条件的行。HAVING
子句作用于分组后的数据,筛选满足条件的组。
- 使用
4.3 连接查询 ⭐️
-
连接查询:涉及多个表的查询。
-
连接条件或谓词:用于指定表之间的连接条件。
-
格式:
[<表1>.]<列名1> <比较运算符> [<表2>.]<列名2>
-
比较运算符:
=
、>
、<
、>=
、<=
、!=
。 -
连接字段:连接谓词中的列名,字段类型需可比,名称可以不同。
1. 等值连接与非等值连接
-
等值连接:连接条件使用
=
运算符。 -
示例:
SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno = SC.Sno;
连接操作的执行方法
- 嵌套循环法 (Nested-Loop):对两个表进行循环,每次匹配一个元组。
- 排序合并法 (Sort-Merge):对两个表进行排序,然后按顺序合并匹配。
- 索引连接法 (Index-Join):对表2建立索引,根据表1的连接字段在表2中查找匹配行。
2. 自然连接
-
自然连接是等值连接的一种特殊情况,去除重复的属性列。
-
示例:
SELECT Student.Sno, Sname, Ssex, Sbirthdate, Smajor, Cno, Grade FROM Student, SC WHERE Student.Sno = SC.Sno;
3. 复合条件连接
-
复合条件连接:WHERE 子句中包含多个连接条件。
-
示例:
SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND SC.Cno = '81002' AND SC.Grade > 90;
4. 自身连接
-
自身连接:一个表与自身进行连接,需要为表设置别名以区分,由于所有属性名都是同名属性,因此必须使用别名前缀
-
示例:查询每门课的间接先修课(即先修课的先修课)。
SELECT A.Cno, B.Cpno FROM Course A, Course B WHERE A.Cpno = B.Cno;
5. 外连接
- 外连接:区别于普通连接,外连接会输出不满足连接条件的元组,通常分为左外连接和右外连接。
- 左外连接:列出左表中的所有元组,即左侧表为主表
- 右外连接:列出右表中的所有元组,即右侧表为主表
6. 多表连接查询
-
示例:查询每个学生的学号、姓名、选修的课程名及成绩。
SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
-
执行过程:
- 先将
Student
与SC
表进行连接,获取学号、姓名和课程号、成绩。 - 然后与
Course
表连接,获取最终的结果。
- 先将