本篇内容为上一篇内容的补充:
数据库2—数据库基础·关系数据库和SQL
总目录:
数据库知识 - 索引目录
一、关系型数据库
1、数据模型
1.1 关系模型的基本概念
关系型数据库是一种基于关系模型的数据库管理系统,它使用表格来组织和存储数据。在关系型数据库中,数据以行和列的形式存储在表格中,每个表格代表一个实体或关系,而每行代表一个具体的数据记录,每列代表一个属性或字段。
关系模型的基本概念包括以下几个要点:
-
表(Table):关系模型中的基本组织单位,用于存储特定类型的数据。表由行和列组成,行表示特定实例的数据记录,列表示该实例的属性或字段。
-
行(Row):表中的每一行代表一个特定实例的数据记录,也称为元组(Tuple)。每行包含了该实例的各个属性的数值或空值。
-
列(Column):表中的每一列代表一个属性或字段,用于描述实例的特征或属性。每列包含了相同类型的数据。
-
键(Key):在关系模型中,键用于唯一标识表中的每一行数据记录。主键(Primary Key)是一种特殊的键,用于唯一标识表中的每一行,并且不允许包含重复值。
示例:
下面以一个学生信息管理的示例来说明关系模型的基本概念:
假设我们有一个学生信息管理系统,需要存储学生的基本信息,包括学号、姓名、性别和年龄等数据。我们可以使用关系模型来设计数据库表格,如下所示:
表名:Student
学号(StudentID) | 姓名(Name) | 性别(Gender) | 年龄(Age) |
---|---|---|---|
1 | 小明 | 男 | 20 |
2 | 小红 | 女 | 19 |
3 | 小华 | 男 | 21 |
在上面的示例中,表名为Student,每行代表一个学生的信息记录,每列分别表示学生的学号、姓名、性别和年龄。学号作为主键,用于唯一标识每个学生的信息记录。
1.2 关系模型的组成:关系、元组、属性、域、度、基数
-
关系(Relation): 在数据库中,关系就是一张表。比如前面提到的学生信息表就是一个关系。
-
元组(Tuple): 这就是表中的一行,代表了一条具体的数据记录,就像一个学生的信息。
-
属性(Attribute): 表中的一列,描述了数据的某一方面。比如,学生表中的学号、姓名、性别、年龄就是属性。
-
域(Domain): 属性的取值范围,也就是说,它规定了某个属性可以存储的数据类型和范围。比如,年龄属性的域可能是自然数。
-
度(Degree): 表中的属性数量,也就是表的宽度。学生表的度就是4,因为有学号、姓名、性别、年龄四个属性。
-
基数(Cardinality): 表中元组的数量,也就是表的长度。学生表的基数就是表中学生信息的数量。
示例:
让我们通过一个简单的例子来说明这些概念:
假设我们有一个关系(表)叫做Book
,用于存储图书信息。它有三个属性:ISBN
(书号)、Title
(标题)、Author
(作者)。
表名:Book
ISBN | Title | Author |
---|---|---|
978-1-12345-1 | “Database Basics” | John Smith |
978-1-12345-2 | “SQL Mastery” | Jane Doe |
978-1-12345-3 | “Data Modeling 101” | Bob Johnson |
在这个例子中:
- 关系(表)是
Book
。 - 元组(行)是每一本书的信息。
- 属性(列)是
ISBN
、Title
、Author
。 - 域是每个属性可以取的值的范围,比如
ISBN
的域可能是所有合法的书号。 - 度是3,因为有3个属性。
- 基数是3,因为有3本书的信息。
2、关系数据库的特点
2.1 数据独立性
数据独立性包括逻辑数据独立性和物理数据独立性。
- 逻辑数据独立性: 这指的是应用程序与数据的逻辑结构是相互独立的。也就是说,如果数据库的逻辑结构发生变化(比如表的结构变化),应用程序的逻辑结构不受影响,不需要修改应用程序的代码。这样,数据库的逻辑结构可以根据需要进行调整,而不会对应用程序造成影响。
举个🌰: 假设有一个存储学生信息的数据库表,包括学号、姓名和年龄。如果将年龄改为出生日期,那么只需要修改数据库的逻辑结构,而不需要修改使用这些数据的应用程序,这就是逻辑数据独立性的体现。
- 物理数据独立性: 这指的是应用程序与数据的物理存储是相互独立的。也就是说,如果数据库的物理存储结构发生变化(比如存储介质、索引方式等变化),应用程序的物理存储结构不受影响,不需要修改应用程序的代码。这样,数据库的物理存储结构可以根据需要进行调整,而不会对应用程序造成影响。
举个🌰: 如果将数据库从磁盘迁移到内存中,或者从单机数据库迁移到分布式数据库,只需要修改数据库的物理存储结构,而不需要修改应用程序的代码,这就是物理数据独立性的体现。
2.2 数据一致性
数据一致性: 数据一致性指的是数据库中的数据应该始终保持一致的状态。这意味着数据应该满足所有的约束、规则和关系,不会出现冲突或矛盾的情况。无论是在同一时间点还是不同时间点,数据在数据库中都应该是一致的。
举个🌰: 假设有一个存储订单信息的数据库表,其中包括订单号、客户信息、订单金额等字段。数据一致性要求订单号必须唯一,客户信息不能为空,订单金额必须大于等于0。如果数据库中出现了两条订单号相同的记录,或者订单金额为负数,这就违反了数据一致性的原则。
为了保证数据一致性,关系型数据库通常会使用以下方法和技术:
- 主键约束:保证每条记录都有唯一的标识,避免数据重复。
- 外键约束:保证不同表之间的关联关系,避免数据之间的矛盾。
- 数据完整性约束:定义数据的合法取值范围,避免不合法的数据出现。
通过以上约束和规则,数据库系统可以保证数据的一致性,确保数据始终处于合法、正确的状态。
2.3 数据完整性
数据完整性指的是数据库中的数据应该符合预定义的规则、约束和限制。这确保了数据的准确性、一致性和有效性。
数据完整性的保持可以通过以下方式来实现:
- 实体完整性(Entity Integrity):实体完整性是指每个表必须有一个主键,并且每条记录都必须具有唯一且非空的主键值。主键是用来唯一标识表中每条记录的字段,确保每条记录都是唯一的。如果表中有缺少主键或主键值重复的情况,就会违反实体完整性。
举个🌰:
假设有一个存储学生信息的表(Student),其中学生ID是主键。下面是一个简单的示例表:
学生ID | 姓名 | 年龄 | 班级 |
---|---|---|---|
1 | 小明 | 20 | 10 |
2 | 小红 | 21 | 11 |
3 | 小刚 | 19 | 10 |
1 | 小丽 | 22 | 12 |
在上面的示例中,最后一行数据违反了实体完整性,因为学生ID为1的记录出现了重复。
- 参照完整性(Referential Integrity):参照完整性确保不同表之间的关联关系是有效的。这通常通过外键来实现,外键是一个表中字段,它指向另一个表的主键,用于建立表与表之间的关联。参照完整性要求外键值必须存在于另一个表的主键中,防止了无效的关联关系。
举个🌰:
假设有一个存储课程信息的表(Course)和一个存储学生选课信息的表(Student_Course)。其中,Student_Course表中有一个字段StudentID作为外键,参考了Student表中的学生ID字段,确保学生选课记录与学生信息是对应的。
学生ID | 课程ID |
---|---|
1 | 101 |
2 | 102 |
3 | 103 |
4 | 104 |
在上面的示例中,如果StudentID字段引用的学生ID在Student表中不存在,就违反了参照完整性。
- 域完整性(Domain Integrity):域完整性确保数据的合法性和正确性,即规定了每个字段的取值范围和数据类型。例如,一个字段的数据类型是整数,就不应该存储非整数类型的数据。
举个🌰:
假设有一个存储商品信息的表(Product),其中有一个字段Price用于存储商品价格,要求价格必须大于等于0。
商品ID | 商品名称 | 价格 |
---|---|---|
1 | 商品A | 50 |
2 | 商品B | -10 |
3 | 商品C | 80 |
在上面的示例中,第二行的价格为负数违反了域完整性。
数据完整性确保了数据库中数据的正确性和有效性,通过实体完整性、参照完整性和域完整性的规则和约束来维护数据的合法性。
3、表的结构和设计
3.1 表的定义和术语
-
表的定义:
- 表(Table): 表是关系型数据库中的基本对象,用于存储数据。表由行和列组成,每行表示表中的一条记录,每列表示记录中的一个字段。
-
表的术语:
- 字段(Field): 表中的每一列都称为字段。每个字段都有一个特定的数据类型,例如整数、字符串、日期等。
- 记录(Record): 表中的每一行都称为记录。记录包含了表中每个字段的具体数值。
- 主键(Primary Key): 主键是表中唯一标识每条记录的字段。主键的值必须是唯一且非空的,用于确保表中的每条记录都可以被唯一标识。
- 外键(Foreign Key): 外键是表中的一个字段,它与另一表的主键建立关联,用于实现表与表之间的关系。
- 索引(Index): 索引是一种数据结构,用于提高数据检索的速度。通过创建索引,可以加速对表中特定字段的搜索操作。
- 约束(Constraint): 约束是对表中数据的限制和规则,确保数据的有效性和完整性。包括主键约束、外键约束、唯一约束等。
举个🌰:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT,
Class VARCHAR(10)
);
在上面的例子中,我们定义了一个名为Students的表,包含了字段StudentID、FirstName、LastName、Age和Class。其中,StudentID被定义为主键,确保每个学生的ID是唯一的。
🌰数据:
StudentID | FirstName | LastName | Age | Class |
---|---|---|---|---|
1 | John | Doe | 20 | A101 |
2 | Jane | Smith | 22 | B102 |
3 | Bob | Johnson | 21 | A101 |
这是一个简单的学生信息表,每一行代表一个学生的记录,每一列代表一个属性。这个表的设计考虑了主键的唯一性和字段的数据类型。
3.2 表的创建和修改
表的创建
在关系型数据库中,我们使用CREATE TABLE
语句来创建表。
举个🌰:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50)
);
上述语句创建了一个名为Employees的表,包含了字段EmployeeID、FirstName、LastName和Department。其中,EmployeeID被定义为主键,确保每个员工的ID是唯一的。
表的修改
1. 添加新列
如果需要向已存在的表中添加新列,可以使用ALTER TABLE
语句。例如,添加一个新的列Salary:
ALTER TABLE Employees
ADD Salary DECIMAL(10, 2);
上述语句在Employees表中添加了一个名为Salary的新列,其数据类型为十进制数(DECIMAL),包含10位数字,其中有2位小数。
2. 修改列的数据类型
如果需要修改列的数据类型,可以使用ALTER TABLE
语句。例如,将Salary列的数据类型修改为整数:
ALTER TABLE Employees
ALTER COLUMN Salary INT;
上述语句修改了Employees表中Salary列的数据类型为整数(INT)。
3. 添加主键约束
如果在创建表时未定义主键,可以使用ALTER TABLE
语句添加主键约束:
ALTER TABLE Employees
ADD PRIMARY KEY (EmployeeID);
上述语句为Employees表添加了主键约束,确保EmployeeID列的唯一性。
4. 添加外键约束
如果需要在表中添加外键约束,可以使用ALTER TABLE
语句。假设有一个Departments表,其中有一个DepartmentID列作为主键,可以将其作为外键关联到Employees表的Department列:
ALTER TABLE Employees
ADD FOREIGN KEY (Department) REFERENCES Departments(DepartmentID);
上述语句建立了Employees表的Department列与Departments表的DepartmentID列之间的外键关系。
表的删除
如果需要删除表,可以使用DROP TABLE
语句:
DROP TABLE Employees;
上述语句删除了Employees表及其所有数据。
3.3 表设计的小建议
-
遵循规范化原则: 规范化是数据库设计中的重要概念,它有助于减少数据冗余、提高数据一致性,并且有利于维护和查询性能。通常情况下,我们应该尽量将数据分解成更小的、相关的表,以减少数据冗余。
-
选择合适的数据类型: 在定义表的字段时,应该选择最适合存储数据的数据类型,以节省存储空间并提高查询效率。例如,对于整数类型的数据,应该选择合适大小的整数类型,而对于文本类型的数据,应该选择合适长度的字符类型。
-
定义主键和外键: 每个表应该有一个主键来唯一标识每条记录,确保数据的唯一性和完整性。同时,如果表与表之间存在关联关系,应该使用外键来建立关联,确保数据的一致性。
-
使用索引优化查询: 对于经常被用于查询条件的字段,可以考虑创建索引来提高查询性能。但是要注意,过多的索引可能会影响写入性能,因此需要权衡利弊。
-
避免使用保留字和特殊字符: 在命名表和字段时,应该避免使用数据库系统的保留字和特殊字符,以免引起命名冲突和不必要的麻烦。
-
合理命名: 表和字段的命名应该具有描述性,能够清晰地表达其所存储的数据内容,避免使用含糊不清或过于简单的命名。
-
考虑性能和扩展性: 在设计表结构时,需要考虑到数据库的性能和扩展性,避免设计过于复杂的关系,以及过度嵌套的结构。
4、关键字和索引
4.1 主键、候选键、超键、外键的定义和作用
1. 主键(Primary Key)
- 定义: 主键是表中唯一标识每条记录的字段或字段组合。主键的值必须是唯一的,并且不允许为空。
- 作用: 主键确保表中的每条记录都能够被唯一标识,保障数据的唯一性和完整性。
举个🌰:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
在上述例子中,StudentID被定义为Students表的主键。
2. 候选键(Candidate Key)
- 定义: 候选键是能够唯一标识记录的字段或字段组合,与主键类似,但在表的设计中可以选择其中一个作为主键。
- 作用: 提供备选的唯一标识方案,其中一个候选键可能被选为主键。
举个🌰:
CREATE TABLE Employees (
EmployeeID INT,
SSN VARCHAR(11) UNIQUE, -- SSN作为候选键
FirstName VARCHAR(50),
LastName VARCHAR(50),
PRIMARY KEY (EmployeeID)
);
在上述例子中,SSN是Employees表的一个候选键。
3. 超键(Super Key)
- 定义: 超键是能够唯一标识记录的字段或字段组合,不一定是最小的。超键可以包含主键、候选键以及其他字段。
- 作用: 提供更广泛的唯一标识方案,超过了最小必需的唯一性标识。
举个🌰:
CREATE TABLE Orders (
OrderID INT,
CustomerID INT,
ProductID INT,
OrderDate DATE,
PRIMARY KEY (OrderID),
UNIQUE (CustomerID, OrderDate) -- 超键包含主键和其他字段
);
在上述例子中,(CustomerID, OrderDate)是Orders表的一个超键。
4. 外键(Foreign Key)
- 定义: 外键是表中的一个字段,它与另一表的主键或唯一键建立关联,用于实现表与表之间的关系。
- 作用: 建立表与表之间的关联,确保数据的一致性和完整性。
举个🌰:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
在上述例子中,Orders表的CustomerID是一个外键,与Customers表的CustomerID建立关联。
4.2 索引的类型、创建、作用
1. 单列索引(Single-Column Index)
- 类型: 单列索引是针对单个表字段创建的索引,用于加快针对该字段的查询速度。
- 作用: 通过单列索引,可以快速定位和访问表中特定字段的数据,提高查询效率。
举个🌰:
CREATE INDEX idx_last_name ON Employees (LastName);
上述示例创建了一个名为idx_last_name的单列索引,用于加速对Employees表中LastName字段的查询。
2. 复合索引(Composite Index)
- 类型: 复合索引是针对多个表字段组合创建的索引,用于加快针对这些字段组合的查询速度。
- 作用: 通过复合索引,可以加速多个字段的组合查询,提高多条件查询的效率。
举个🌰:
CREATE INDEX idx_name_dept ON Employees (LastName, DepartmentID);
上述示例创建了一个名为idx_name_dept的复合索引,用于加速对Employees表中LastName和DepartmentID字段组合的查询。
3. 唯一索引(Unique Index)
- 类型: 唯一索引确保索引列中的所有值都是唯一的,类似于主键,但允许空值。
- 作用: 通过唯一索引,可以确保表中索引列的唯一性,避免重复数据的插入。
举个🌰:
CREATE UNIQUE INDEX idx_ssn ON Employees (SSN);
上述示例创建了一个名为idx_ssn的唯一索引,用于确保Employees表中SSN字段的唯一性。
4. 全文索引(Full-Text Index)
- 类型: 全文索引用于对文本字段进行全文搜索,允许对文本内容进行关键字搜索。
- 作用: 通过全文索引,可以实现对文本内容的高效搜索和匹配。
举个🌰:
CREATE FULLTEXT INDEX idx_description ON Products (Description);
上述示例创建了一个名为idx_description的全文索引,用于对Products表中Description字段进行全文搜索。
5. 空间索引(Spatial Index)
- 类型: 空间索引用于对空间数据进行索引,例如地理信息系统(GIS)中的地理位置数据。
- 作用: 通过空间索引,可以加速对空间数据的查询和分析。
举个🌰:
CREATE SPATIAL INDEX idx_location ON Stores (Location);
上述示例创建了一个名为idx_location的空间索引,用于加速对Stores表中Location字段的空间查询。
4.3 索引的维护
索引的维护通常涉及到重新构建、重新组织或更新索引,以适应数据库中数据的变化。
1. 重建索引(Rebuilding Index)
索引的重建是指完全重新构建整个索引结构,通常在索引空间不足或需要进行大规模数据删除操作时执行。这可以帮助优化索引的物理存储和提高查询性能。
举个🌰:
假设我们有一个名为Orders的表,其中包含大量订单数据,而且OrderDate字段上有一个索引。如果进行了大规模的订单数据删除,可能需要重建该索引。
-- 重建OrderDate索引
ALTER INDEX idx_order_date ON Orders REBUILD;
2. 重新组织索引(Reorganizing Index)
索引的重新组织是指优化索引的物理存储结构,通常在索引碎片较多时执行,以减少磁盘空间的使用和提高查询性能。
举个🌰:
假设我们有一个名为Customers的表,其中的LastName字段上有一个索引。如果进行了大量的数据更新或删除,可能导致索引碎片,需要重新组织索引。
-- 重新组织LastName索引
ALTER INDEX idx_last_name ON Customers REORGANIZE;
3. 更新统计信息(Update Statistics)
数据库管理系统使用统计信息来优化查询执行计划。在进行大规模数据变更后,更新统计信息可以确保数据库系统有最新的数据分布信息,以帮助生成更有效的查询计划。
举个🌰:
假设我们有一个名为Products的表,进行了大规模的价格更新,可能需要更新价格字段的统计信息。
-- 更新Price字段的统计信息
UPDATE STATISTICS Products (Price);
二、SQL
1、SQL概述
1.1 SQL的历史和标准
当我们谈到SQL(Structured Query Language)时,我们是在讨论一种专门用于管理和操作关系型数据库的语言。
SQL的历史可以追溯到上世纪70年代,它的发展经历了多个版本和标准化过程。
1. SQL的历史
-
起源:
SQL的起源可以追溯到20世纪70年代初,IBM的一组工程师开发了一种用于操作数据库的语言,称之为SEQUEL(Structured English QUEry Language)。这后来演化成了SQL。 -
标准化过程:
SQL的标准化工作始于20世纪80年代。在这个过程中,美国国家标准协会(ANSI)和国际标准化组织(ISO)都对SQL提出了自己的标准。ANSI SQL和ISO SQL标准逐渐融合,形成了SQL-92标准,这是SQL语言的一次较大的标准化。 -
进一步发展:
之后,SQL标准经历了多次更新和修订。SQL-99、SQL:2003、SQL:2008等版本相继发布,每个版本都引入了新的功能和改进,以适应数据库管理系统的发展和用户需求。 -
开源发展:
随着开源数据库管理系统的兴起,如MySQL和PostgreSQL,SQL的使用变得更加广泛。这些系统通常遵循SQL标准,并根据需要进行扩展和优化。
2. SQL的标准
SQL的标准由国际组织ISO维护,最新版本是SQL:2016。
SQL标准定义了语法规则、数据类型、操作符、函数等,以确保不同的数据库管理系统(DBMS)都能理解和执行SQL语句。
然而,不同的DBMS可能会在标准之外提供自己的扩展功能。
举个🌰:
让我们看一个简单的SQL查询的例子。
假设我们有一个名为Employees的表,存储员工信息:
-- 创建Employees表
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
HireDate DATE
);
-- 插入数据
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES
(1, 'John', 'Doe', '2020-01-15'),
(2, 'Jane', 'Smith', '2019-08-22'),
(3, 'Bob', 'Johnson', '2021-03-10');
-- 查询所有员工的信息
SELECT * FROM Employees;
上述SQL示例创建了一个名为Employees的表,插入了一些员工信息,并使用SELECT语句查询了所有员工的信息。这是一个简单的示例,但SQL语句可以执行复杂的数据操作,包括查询、插入、更新、删除等。
1.2 SQL的分类:DDL、DML、DCL、TCL
SQL主要分为四个类别:DDL(数据定义语言)、DML(数据操作语言)、DCL(数据控制语言)和TCL(事务控制语言)。
1. DDL(数据定义语言)
DDL用于定义数据库的结构和组件。这些语句用于创建、修改和删除数据库对象,例如表格(表)、索引、视图、存储过程等。
主要的DDL命令包括:
- CREATE:用于创建数据库对象,比如创建表格。
- ALTER:用于修改数据库对象的结构,比如修改表格的列或者添加新列。
- DROP:用于删除数据库对象,比如删除表格或索引。
- TRUNCATE:用于快速删除表格中的所有数据,但保留表格结构。
举个🌰 - DDL示例:
-- 创建一个新表格
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT
);
-- 向表格添加一个新列
ALTER TABLE Students ADD GPA DECIMAL(3, 2);
-- 删除表格
DROP TABLE Students;
-- 清空表格数据但保留表格结构
TRUNCATE TABLE Students;
2. DML(数据操作语言)
DML用于操作数据库中的数据。这些语句用于检索、插入、更新和删除数据,使用户能够管理数据库中的实际信息。
主要的DML命令包括:
- SELECT:用于从数据库中检索数据。
- INSERT:用于向数据库中插入新的数据行。
- UPDATE:用于更新数据库中现有的数据行。
- DELETE:用于从数据库中删除数据行。
举个🌰 - DML示例:
-- 从表格中选择所有学生的信息
SELECT * FROM Students;
-- 向表格插入新的学生信息
INSERT INTO Students (StudentID, FirstName, LastName, Age, GPA)
VALUES (1, 'Alice', 'Smith', 20, 3.5);
-- 更新学生信息
UPDATE Students SET Age = 21 WHERE StudentID = 1;
-- 删除学生信息
DELETE FROM Students WHERE StudentID = 1;
3. DCL(数据控制语言)
DCL用于控制数据库的访问权限和安全性,管理用户的访问权限以及控制数据库的完整性。
主要的DCL命令包括:
- GRANT:赋予用户访问数据库对象的权限。
- REVOKE:撤销用户对数据库对象的权限。
4. TCL(事务控制语言)
TCL用于管理数据库中的事务处理。它控制事务的开始、提交和回滚,并帮助确保数据库的完整性和一致性。
主要的TCL命令包括:
- COMMIT:将数据库中的所有修改保存到数据库中。
- ROLLBACK:撤销事务中对数据库的所有修改。
- SAVEPOINT:设置事务中的保存点,允许在事务中的某一点进行部分回滚。
2、数据定义语言(DDL)
2.1 创建、修改、删除表的操作
SQL的数据定义语言(DDL):用于定义和管理数据库结构的一组命令。
DDL主要包括创建、修改和删除表格以及其他数据库对象的操作。
1. 创建表格(CREATE TABLE)
使用CREATE TABLE
语句可以创建新的表格,并定义表格的结构,包括列名、数据类型和约束。以下是一个简单的例子:
-- 创建一个新表格
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT
);
解释:
CREATE TABLE Students
:指定要创建的表格的名称为Students。( ... )
:定义表格的列和其属性。StudentID INT PRIMARY KEY
:定义了一个名为StudentID的整数列,作为主键。FirstName VARCHAR(50)
:定义了一个最大长度为50的字符串列。LastName VARCHAR(50)
:定义了另一个最大长度为50的字符串列。Age INT
:定义了一个整数列。
2. 修改表格(ALTER TABLE)
使用ALTER TABLE
语句可以修改已存在的表格,包括添加新列、修改列属性或添加约束。以下是一个例子:
-- 向表格添加一个新列
ALTER TABLE Students ADD GPA DECIMAL(3, 2);
解释:
ALTER TABLE Students
:指定要修改的表格为Students。ADD GPA DECIMAL(3, 2)
:添加了一个名为GPA的新列,它是一个具有3位整数和2位小数的十进制数。
3. 删除表格(DROP TABLE)
使用DROP TABLE
语句可以完全删除一个表格,包括表格中的所有数据和结构。请谨慎使用,因为它会永久删除数据。以下是一个例子:
-- 删除表格
DROP TABLE Students;
解释:
DROP TABLE Students
:删除名为Students的表格。
请注意,在执行DROP TABLE
时,将删除表格及其所有数据,所以务必谨慎使用。
2.2 索引和视图的创建和管理
SQL的数据定义语言(DDL)中的索引和视图:优化数据库的性能和提高查询效率。
1. 索引的创建与管理
- 索引:
- 创建索引可以加速特定列的查询操作,尤其是在大型表格中。
- 然而,索引的创建和维护会增加写操作的开销,因此需要权衡。
创建索引(CREATE INDEX)
索引是一种数据结构,可以提高数据库查询的速度。通过在表的列上创建索引,可以加速数据检索操作。
举个🌰:
-- 在Students表格的LastName列上创建索引
CREATE INDEX idx_LastName ON Students(LastName);
解释:
CREATE INDEX idx_LastName
:创建名为idx_LastName
的索引。ON Students(LastName)
:指定在Students表格的LastName列上创建索引。
删除索引(DROP INDEX)
如果不再需要某个索引,可以使用DROP INDEX
语句删除它。
举个🌰:
-- 删除名为idx_LastName的索引
DROP INDEX idx_LastName ON Students;
解释:
DROP INDEX idx_LastName
:删除名为idx_LastName
的索引。ON Students
:指定要删除索引的表格为Students。
2. 视图的创建与管理
- 视图:
- 创建视图可以简化复杂查询,并提供一种抽象层,隐藏底层表格的复杂性。
- 视图的选择性使用可以提高查询的可读性和维护性。
创建视图(CREATE VIEW)
视图是虚拟表格,它是基于一个或多个实际表格的查询结果。通过创建视图,可以简化复杂的查询操作。
举个🌰:
-- 创建一个包含学生ID和姓名的简化视图
CREATE VIEW StudentNames AS
SELECT StudentID, FirstName, LastName
FROM Students;
解释:
CREATE VIEW StudentNames
:创建名为StudentNames
的视图。AS SELECT StudentID, FirstName, LastName FROM Students
:定义了视图的查询逻辑,该视图包含了从Students表格中选择的学生ID、FirstName和LastName。
删除视图(DROP VIEW)
如果不再需要某个视图,可以使用DROP VIEW
语句删除它。
举个🌰:
-- 删除名为StudentNames的视图
DROP VIEW StudentNames;
解释:
DROP VIEW StudentNames
:删除名为StudentNames
的视图。
3、数据操作语言(DML)
3.1 插入、更新、删除数据的操作
1. 插入数据(INSERT INTO)
使用INSERT INTO
语句可以向数据库表格中插入新的数据。
举个🌰:
-- 向Students表格插入新的学生数据
INSERT INTO Students (StudentID, FirstName, LastName, Age)
VALUES (1, 'John', 'Doe', 20);
解释:
INSERT INTO Students
:指定要插入数据的表格为Students。(StudentID, FirstName, LastName, Age)
:指定要插入数据的列。VALUES (1, 'John', 'Doe', 20)
:提供要插入的具体数据。
2. 更新数据(UPDATE)
使用UPDATE
语句可以修改表格中已存在的数据。
举个🌰:
-- 更新Students表格中学生John Doe的年龄
UPDATE Students
SET Age = 21
WHERE FirstName = 'John' AND LastName = 'Doe';
解释:
UPDATE Students
:指定要更新数据的表格为Students。SET Age = 21
:指定要修改的列和新的值。WHERE FirstName = 'John' AND LastName = 'Doe'
:限定更新的条件,确保只有符合条件的行被更新。
3. 删除数据(DELETE FROM)
使用DELETE FROM
语句可以删除表格中的数据。
举个🌰:
-- 删除Students表格中学生John Doe的数据
DELETE FROM Students
WHERE FirstName = 'John' AND LastName = 'Doe';
解释:
DELETE FROM Students
:指定要删除数据的表格为Students。WHERE FirstName = 'John' AND LastName = 'Doe'
:限定删除的条件,确保只有符合条件的行被删除。
3.2 查询数据:SELECT语句的各种用法
SELECT
语句有多种用法,包括从表格中选择数据、使用条件过滤数据、对数据进行排序等。
1. 基本的SELECT语句
举个🌰:
-- 从Students表格中选择所有列的数据
SELECT * FROM Students;
解释:
SELECT *
:表示选择所有列。FROM Students
:指定从Students表格中选择数据。
2. 选择特定列的数据
举个🌰:
-- 从Students表格中选择学生的ID和姓名
SELECT StudentID, FirstName, LastName FROM Students;
解释:
SELECT StudentID, FirstName, LastName
:指定要选择的列。FROM Students
:指定从Students表格中选择数据。
3. 使用条件过滤数据
举个🌰:
-- 从Students表格中选择年龄大于等于21岁的学生
SELECT * FROM Students
WHERE Age >= 21;
解释:
WHERE Age >= 21
:指定条件,只选择符合条件的行。
4. 对数据进行排序
举个🌰:
-- 从Students表格中选择学生,并按年龄降序排序
SELECT * FROM Students
ORDER BY Age DESC;
解释:
ORDER BY Age DESC
:指定按年龄降序排序结果。
5. 使用聚合函数
举个🌰:
-- 计算Students表格中学生的平均年龄
SELECT AVG(Age) AS AverageAge FROM Students;
解释:
AVG(Age) AS AverageAge
:使用AVG
聚合函数计算年龄的平均值,并使用AS
为结果起别名。
6. 联结多个表格
举个🌰:
-- 从Students和Courses表格中选择学生姓名和所选课程
SELECT Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
JOIN Courses ON Enrollments.CourseID = Courses.CourseID;
解释:
JOIN
:用于联结多个表格。ON Students.StudentID = Enrollments.StudentID
和ON Enrollments.CourseID = Courses.CourseID
:指定联结条件。
4、数据控制语言(DCL)和事务控制语言(TCL)
4.1 DCL(数据控制语言):用户权限的授予和撤销
DCL用于授予或撤销数据库对象的访问权限,这包括表格、视图等。
举个🌰 - 授予权限:
-- 授予用户John对Students表格的SELECT权限
GRANT SELECT ON Students TO John;
解释:
GRANT SELECT ON Students
:表示授予SELECT权限。TO John
:指定将权限授予给用户John。
举个🌰 - 撤销权限:
-- 撤销用户John对Students表格的SELECT权限
REVOKE SELECT ON Students FROM John;
解释:
REVOKE SELECT ON Students
:表示撤销SELECT权限。FROM John
:指定从用户John撤销权限。
4.2 事务控制语言(TCL):COMMIT、ROLLBACK、SAVEPOINT
在SQL中,事务是一个包含一系列SQL语句的逻辑操作单元,这些语句要么全部执行成功,要么全部失败。
TCL用于控制事务的提交(COMMIT)、回滚(ROLLBACK)以及保存点的设置(SAVEPOINT)。
事务的提交(COMMIT)
举个🌰:
-- 开始一个新的事务
BEGIN;
-- 执行一系列DML操作
-- 提交事务,将更改保存到数据库
COMMIT;
解释:
BEGIN
:标志事务的开始。COMMIT
:提交事务,将所有更改保存到数据库。
在这个例子中,如果在COMMIT
之前没有发生错误,所有的更改将被永久保存到数据库。
事务的回滚(ROLLBACK)
举个🌰:
-- 开始一个新的事务
BEGIN;
-- 执行一系列DML操作
-- 回滚事务,撤销所有更改
ROLLBACK;
解释:
ROLLBACK
:回滚事务,撤销所有未提交的更改。
在这个例子中,如果在ROLLBACK
之前发生了错误,所有的更改将被撤销,数据库回到事务开始之前的状态。
保存点的设置(SAVEPOINT)
举个🌰:
-- 开始一个新的事务
BEGIN;
-- 执行一系列DML操作
-- 设置保存点
SAVEPOINT my_savepoint;
-- 执行更多DML操作
-- 如果发生错误,回滚到保存点
ROLLBACK TO my_savepoint;
-- 提交事务
COMMIT;
解释:
SAVEPOINT my_savepoint
:在事务中设置一个保存点。ROLLBACK TO my_savepoint
:如果在设置保存点后发生错误,可以回滚到这个保存点。
在这个例子中,保存点允许在事务中的特定位置进行回滚,而不是回滚整个事务。
5、SQL高级特性
5.1 连接(JOIN)的类型和用法
连接用于联结多个表格,从而能够在结果集中获取来自这些表格的相关数据。
连接的类型
-
内连接(INNER JOIN):
- 内连接返回两个表格中满足连接条件的行。
- 举个🌰:
SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
- 解释:
INNER JOIN
:表示进行内连接。Orders.CustomerID = Customers.CustomerID
:指定连接条件。
-
左连接(LEFT JOIN 或 LEFT OUTER JOIN):
- 左连接返回左边表格中所有的行,以及右边表格中满足连接条件的行。
- 举个🌰:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
- 解释:
LEFT JOIN
或LEFT OUTER JOIN
:表示进行左连接。
-
右连接(RIGHT JOIN 或 RIGHT OUTER JOIN):
- 右连接返回右边表格中所有的行,以及左边表格中满足连接条件的行。
- 举个🌰:
SELECT Orders.OrderID, Customers.CustomerName FROM Orders RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
- 解释:
RIGHT JOIN
或RIGHT OUTER JOIN
:表示进行右连接。
-
全连接(FULL JOIN 或 FULL OUTER JOIN):
- 全连接返回两个表格中所有的行,无论是否满足连接条件。
- 举个🌰:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
- 解释:
FULL JOIN
或FULL OUTER JOIN
:表示进行全连接。
连接的用法
连接通常用于关联表格中的列,并在结果集中获取相关数据。连接条件是关键,它指定了在哪些列上进行连接。
以下是一些常见的连接用法:
-
连接多个表格:
SELECT Customers.CustomerName, Orders.OrderID, Products.ProductName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID;
这个例子中连接了四个表格,获取了客户名、订单ID和产品名的信息。
-
自连接(Self Join):
SELECT e.EmployeeID, e.EmployeeName, m.EmployeeName AS ManagerName FROM Employees e LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;
这个例子中通过自连接获取了员工及其经理的信息。
-
使用连接和聚合函数:
SELECT Customers.CustomerName, COUNT(Orders.OrderID) AS OrderCount FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID, Customers.CustomerName;
这个例子中使用连接和聚合函数计算了每个客户的订单数量。
5.2 子查询和嵌套查询
在SQL中,子查询和嵌套查询是用于在查询中嵌套另一个查询的强大工具。它们可以用于从一个查询中获取结果,并将其用作另一个查询的条件或数据源。
子查询的用法
-
标量子查询(Scalar Subquery):
- 子查询返回单一的值,可以嵌套在其他查询中。
- 举个🌰:
SELECT CustomerName, ( SELECT MAX(OrderDate) FROM Orders WHERE Customers.CustomerID = Orders.CustomerID ) AS LastOrderDate FROM Customers;
- 解释:
- 子查询
(SELECT MAX(OrderDate) FROM Orders WHERE Customers.CustomerID = Orders.CustomerID)
返回每个客户的最后订单日期。
- 子查询
-
行子查询(Row Subquery):
- 子查询返回一行数据,可以嵌套在其他查询中。
- 举个🌰:
SELECT CustomerName FROM Customers WHERE (CustomerID, Country) IN ( SELECT CustomerID, Country FROM Orders WHERE OrderDate > '2023-01-01' );
- 解释:
- 子查询
(SELECT CustomerID, Country FROM Orders WHERE OrderDate > '2023-01-01')
返回了在指定日期后有订单的客户ID和国家,然后主查询使用这些条件过滤客户信息。
- 子查询
嵌套查询的用法
-
嵌套查询作为条件:
- 主查询中的条件使用了嵌套查询的结果。
- 举个🌰:
SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice > ( SELECT AVG(UnitPrice) FROM Products );
- 解释:
- 嵌套查询
(SELECT AVG(UnitPrice) FROM Products)
返回产品价格的平均值,然后主查询选择价格高于平均值的产品。
- 嵌套查询
-
嵌套查询作为数据源:
- 嵌套查询的结果用作主查询中的数据源。
- 举个🌰:
SELECT CustomerName, ( SELECT COUNT(OrderID) FROM Orders WHERE Customers.CustomerID = Orders.CustomerID ) AS OrderCount FROM Customers;
- 解释:
- 嵌套查询
(SELECT COUNT(OrderID) FROM Orders WHERE Customers.CustomerID = Orders.CustomerID)
返回每个客户的订单数量,主查询选择客户名和订单数量。
- 嵌套查询
5.3 视图的进阶用法
1. 更新视图(Updatable Views)
视图可以是可更新的,这意味着您可以通过视图对底层表格进行数据更新。但有一些限制,例如视图必须是单表的,并且不能包含以下元素:聚合函数、GROUP BY 子句、DISTINCT 关键字等。
举个🌰:
-- 创建一个简单的可更新视图
CREATE VIEW HighSalaryEmployees AS
SELECT EmployeeID, EmployeeName, Salary
FROM Employees
WHERE Salary > 70000;
-- 更新视图中的数据
UPDATE HighSalaryEmployees
SET Salary = Salary + 5000
WHERE EmployeeID = 101;
-- 查询更新后的数据
SELECT * FROM HighSalaryEmployees;
解释:
-
CREATE VIEW HighSalaryEmployees AS ...
:创建一个名为HighSalaryEmployees
的视图,显示工资大于70000的员工信息。 -
UPDATE HighSalaryEmployees SET Salary = Salary + 5000 WHERE EmployeeID = 101;
:通过更新视图,将员工ID为101的工资增加5000。 -
SELECT * FROM HighSalaryEmployees
:查询更新后的数据。
2. 联结视图(Join Views)
视图可以联结多个表格,形成联结视图,使得用户能够以统一的方式查看多个表格的数据。
举个🌰:
-- 创建一个联结视图
CREATE VIEW OrderDetailsView AS
SELECT Orders.OrderID, Customers.CustomerName, OrderDetails.ProductID, Products.ProductName
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN Products ON OrderDetails.ProductID = Products.ProductID;
-- 查询联结视图的数据
SELECT * FROM OrderDetailsView;
解释:
-
CREATE VIEW OrderDetailsView AS ...
:创建一个名为OrderDetailsView
的联结视图,显示订单、客户、产品的详细信息。 -
SELECT * FROM OrderDetailsView
:查询联结视图的数据。
3. 带参数的视图
有时,您希望在查询中使用参数,以动态地过滤视图的结果。这可以通过带参数的视图实现。
举个🌰:
-- 创建一个带参数的视图
CREATE VIEW HighSalaryEmployeesByDepartment AS
SELECT EmployeeID, EmployeeName, Salary, Department
FROM Employees
WHERE Salary > ? AND Department = ?;
-- 查询带参数的视图
SELECT * FROM HighSalaryEmployeesByDepartment
WHERE Salary > 80000 AND Department = 'IT';
解释:
-
CREATE VIEW HighSalaryEmployeesByDepartment AS ...
:创建一个名为HighSalaryEmployeesByDepartment
的带参数的视图,显示工资高于指定值且属于特定部门的员工信息。 -
SELECT * FROM HighSalaryEmployeesByDepartment WHERE Salary > 80000 AND Department = 'IT';
:查询带参数的视图,获取工资高于80000且属于IT部门的员工信息。
三、数据完整性和约束
1、数据完整性的概念
数据完整性的类型:实体完整性、域完整性、参照完整性、用户定义完整性
1. 实体完整性(Entity Integrity)
实体完整性确保每个表格中的记录都具有唯一的标识,通常通过定义主键来实现。主键是一个唯一标识表格中记录的字段。
举个🌰:
-- 创建一个带有主键的表格
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
Age INT
);
解释:
PRIMARY KEY
:定义了StudentID
字段为主键,确保每个学生记录具有唯一的标识。
2. 域完整性(Domain Integrity)
域完整性确保每个表格中的列都满足特定的数据类型、约束和规则。包括数据类型、唯一性约束、默认值等。
举个🌰:
-- 创建一个带有唯一性约束的表格
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
Email VARCHAR(50) UNIQUE,
Salary DECIMAL(10, 2) CHECK (Salary >= 0)
);
解释:
UNIQUE
:确保Email
列的值是唯一的,避免重复的邮箱地址。CHECK (Salary >= 0)
:确保Salary
列的值大于等于0,防止负数的工资。
3. 参照完整性(Referential Integrity)
参照完整性确保在关系数据库中的表格之间存在正确的关系。通常通过定义外键实现,确保引用其他表格的值的一致性。
举个🌰:
-- 创建一个包含外键的表格
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
解释:
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
:定义了Orders
表格中的CustomerID
列为外键,引用了Customers
表格中的CustomerID
列。
4. 用户定义完整性(User-Defined Integrity)
用户定义完整性是指根据特定业务规则和需求定义的数据完整性。可能涉及到触发器、存储过程或应用层面的逻辑。
举个🌰:
-- 创建一个触发器以确保年龄大于等于18
CREATE TRIGGER CheckAge
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
IF NEW.Age < 18 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Age must be 18 or older';
END IF;
END;
解释:
BEFORE INSERT ON Students FOR EACH ROW
:定义了在插入数据前触发的触发器。IF NEW.Age < 18 THEN ...
:检查插入的学生记录的年龄是否大于等于18。
2、约束的类型和使用
在SQL中,约束用于定义对表格中数据的限制和规则,以确保数据的完整性和一致性。
2.1 主键约束(Primary Key Constraint)
主键约束用于唯一标识表格中的每条记录,确保每条记录都具有唯一的标识。主键约束通常与主键字段一起使用。
举个🌰:
-- 创建一个带有主键约束的表格
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
Age INT
);
解释:
PRIMARY KEY
:定义了StudentID
字段为主键,确保每个学生记录具有唯一的标识。
2.2 外键约束(Foreign Key Constraint)
外键约束用于定义表格之间的关系,确保引用其他表格的值的一致性。外键约束通常与外键字段一起使用。
举个🌰:
-- 创建一个包含外键约束的表格
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
解释:
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
:定义了Orders
表格中的CustomerID
列为外键,引用了Customers
表格中的CustomerID
列。
2.3 唯一约束(Unique Constraint)
唯一约束确保表格中的列具有唯一的值,但允许空值。唯一约束通常用于确保某些列的值不重复。
举个🌰:
-- 创建一个带有唯一约束的表格
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
Email VARCHAR(50) UNIQUE,
Salary DECIMAL(10, 2)
);
解释:
UNIQUE
:确保Email
列的值是唯一的,避免重复的邮箱地址。
2.4 检查约束(Check Constraint)
检查约束用于定义列的取值范围或条件,确保插入或更新的值满足特定的条件。
举个🌰:
-- 创建一个带有检查约束的表格
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(50),
Price DECIMAL(10, 2) CHECK (Price >= 0)
);
解释:
CHECK (Price >= 0)
:确保Price
列的值大于等于0,防止负数的价格。
2.5 默认约束(Default Constraint)
默认约束用于为列指定默认值,当插入新记录时,如果未提供该列的值,则将使用默认值。
举个🌰:
-- 创建一个带有默认约束的表格
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
Age INT DEFAULT 18
);
解释:
Age INT DEFAULT 18
:指定了Age
列的默认值为18,如果插入记录时未提供年龄,则将使用默认值18。
3、约束的实现和管理
3.1 约束的创建和删除
约束的创建
1. 创建主键约束
-- 创建主键约束
ALTER TABLE Students
ADD CONSTRAINT PK_Students PRIMARY KEY (StudentID);
2. 创建外键约束
-- 创建外键约束
ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);
3. 创建唯一约束
-- 创建唯一约束
ALTER TABLE Employees
ADD CONSTRAINT UQ_Email UNIQUE (Email);
4. 创建检查约束
-- 创建检查约束
ALTER TABLE Products
ADD CONSTRAINT CHK_Price CHECK (Price >= 0);
5. 创建默认约束
-- 创建默认约束
ALTER TABLE Students
ALTER COLUMN Age SET DEFAULT 18;
约束的删除
1. 删除主键约束
-- 删除主键约束
ALTER TABLE Students
DROP CONSTRAINT PK_Students;
2. 删除外键约束
-- 删除外键约束
ALTER TABLE Orders
DROP CONSTRAINT FK_CustomerID;
3. 删除唯一约束
-- 删除唯一约束
ALTER TABLE Employees
DROP CONSTRAINT UQ_Email;
4. 删除检查约束
-- 删除检查约束
ALTER TABLE Products
DROP CONSTRAINT CHK_Price;
5. 删除默认约束
-- 删除默认约束
ALTER TABLE Students
ALTER COLUMN Age DROP DEFAULT;
3.2 约束的启用和禁用
SQL约束的启用和禁用
在SQL中,可以通过启用(enable)和禁用(disable)约束来控制其生效和失效。这对于在某些情况下暂时取消约束检查或重新启用约束非常有用。
启用约束
1. 启用主键约束
-- 启用主键约束
ALTER TABLE Students
CHECK CONSTRAINT PK_Students;
2. 启用外键约束
-- 启用外键约束
ALTER TABLE Orders
CHECK CONSTRAINT FK_CustomerID;
3. 启用唯一约束
-- 启用唯一约束
ALTER TABLE Employees
CHECK CONSTRAINT UQ_Email;
4. 启用检查约束
-- 启用检查约束
ALTER TABLE Products
CHECK CONSTRAINT CHK_Price;
5. 启用默认约束
-- 启用默认约束
ALTER TABLE Students
ALTER COLUMN Age SET DEFAULT 18;
禁用约束
1. 禁用主键约束
-- 禁用主键约束
ALTER TABLE Students
NOCHECK CONSTRAINT PK_Students;
2. 禁用外键约束
-- 禁用外键约束
ALTER TABLE Orders
NOCHECK CONSTRAINT FK_CustomerID;
3. 禁用唯一约束
-- 禁用唯一约束
ALTER TABLE Employees
NOCHECK CONSTRAINT UQ_Email;
4. 禁用检查约束
-- 禁用检查约束
ALTER TABLE Products
NOCHECK CONSTRAINT CHK_Price;
5. 禁用默认约束
-- 禁用默认约束
ALTER TABLE Students
ALTER COLUMN Age DROP DEFAULT;
3.3 约束的影响和约束条件的选择
约束的影响
1. 性能影响
- 主键和唯一约束:
- 加速检索和提高查询性能,但在插入和更新时需要额外的开销。
- 外键约束:
- 提供了数据完整性,但在插入、更新和删除时可能引入一些性能开销。
- 检查约束:
- 在插入和更新时对数据进行条件检查,可能影响性能。
2. 数据完整性
- 主键约束:
- 保证唯一性,确保每条记录都有唯一标识。
- 外键约束:
- 确保关联表之间的数据一致性,但可能要求更复杂的操作。
- 唯一约束:
- 确保列中的值是唯一的。
- 检查约束:
- 通过条件检查确保数据满足特定要求。
约束条件的选择
1. 主键约束
- 选择一个稳定且很少改变的列作为主键,避免频繁更新。
2. 外键约束
- 使用外键确保数据一致性,但考虑到性能,可能在大批量数据操作时禁用外键检查。
3. 唯一约束
- 适用于确保某列的值是唯一的场景,如邮箱地址、用户名等。
4. 检查约束
- 使用检查约束来确保数据的有效性,但要注意不要设置过于复杂的条件,以避免性能问题。
5. 默认约束
- 使用默认约束时,确保默认值合理且符合业务需求,避免引入不必要的复杂性。