本文目录导读：
图片来源于网络，如有侵权联系删除
《关系数据库标准语言SQL第三章整理手册》
SQL基础概念回顾
关系数据库标准语言SQL（Structured Query Language）是用于管理关系数据库的通用语言，它具有数据定义（DDL）、数据操作（DML）、数据控制（DCL）等多种功能。
（一）数据定义语言（DDL）
1、创建表（CREATE TABLE）
- 在SQL中，创建表是构建数据库结构的基础操作，创建一个名为“students”的表，包含“student_id”（学号，通常为整数类型且为主键）、“student_name”（学生姓名，字符串类型）、“age”（年龄，整数类型）等字段，语法如下：
```sql
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
age INT
);
```
- 这里的“PRIMARY KEY”约束确保了“student_id”字段的唯一性，它是表中记录的标识，而“VARCHAR(50)”表示“student_name”字段可以存储最多50个字符的字符串。
2、修改表（ALTER TABLE）
- 随着业务需求的变化，可能需要对已存在的表结构进行修改，如果要在“students”表中添加一个“gender”（性别）字段，可以使用以下语句：
```sql
ALTER TABLE students ADD gender VARCHAR(10);
```
- 若要修改某个字段的数据类型，比如将“age”字段的数据类型从“INT”改为“SMALLINT”，语句为：
```sql
ALTER TABLE students MODIFY age SMALLINT;
```
3、删除表（DROP TABLE）
- 当某个表不再需要时，可以使用“DROP TABLE”语句将其删除。
```sql
DROP TABLE students;
```
- 需要注意的是，执行此操作将永久删除表中的所有数据，所以在操作前要谨慎确认。
（二）数据操作语言（DML）
1、插入数据（INSERT INTO）
- 向“students”表中插入一条学生记录的语句如下：
```sql
INSERT INTO students (student_id, student_name, age) VALUES (1, 'John', 20);
```
- 这里要确保插入的值的类型和顺序与表定义中的字段相匹配，如果要插入多条记录，可以使用一条“INSERT INTO”语句，
```sql
INSERT INTO students (student_id, student_name, age) VALUES (2, 'Alice', 19),(3, 'Bob', 21);
```
2、更新数据（UPDATE）
- John”同学的年龄发生了变化，要将其年龄更新为21岁，可以使用以下语句：
```sql
UPDATE students SET age = 21 WHERE student_name = 'John';
```
- “WHERE”子句用于指定要更新的记录的条件，如果省略“WHERE”子句，将会更新表中的所有记录，这通常是非常危险的操作。
3、删除数据（DELETE）
- 假设要删除名为“Bob”的学生记录，语句为：
```sql
DELETE FROM students WHERE student_name = 'Bob';
```
- 同样，没有“WHERE”子句时会删除表中的所有数据。
（一）查询基础（SELECT）
1、简单查询
- 最基本的查询语句是从表中获取所有记录的所有字段，例如查询“students”表中的所有数据：
```sql
SELECT * FROM students;
```
- 如果只想要获取特定的字段，比如只查询学生的姓名和年龄，可以使用：
```sql
SELECT student_name, age FROM students;
```
2、带条件的查询（WHERE子句）
- 除了前面提到的在更新和删除操作中的“WHERE”子句，在查询中也经常使用，查询年龄大于20岁的学生：
```sql
SELECT * FROM students WHERE age > 20;
```
- 还可以使用逻辑运算符组合多个条件，如查询年龄在18到20岁之间的学生：
```sql
SELECT * FROM students WHERE age >= 18 AND age <= 20;
```
3、排序查询结果（ORDER BY子句）
- 可以根据某个字段对查询结果进行排序，按照年龄从小到大对学生进行排序：
```sql
SELECT * FROM students ORDER BY age;
```
- 如果要按照年龄从大到小排序，可以使用“DESC”（降序）关键字：
```sql
SELECT * FROM students ORDER BY age DESC;
```
图片来源于网络，如有侵权联系删除
（二）聚合函数
1、COUNT函数
- 用于计算表中的记录数，查询“students”表中的学生总数：
```sql
SELECT COUNT(*) FROM students;
```
- 也可以计算满足特定条件的记录数，如查询年龄大于20岁的学生人数：
```sql
SELECT COUNT(*) FROM students WHERE age > 20;
```
2、SUM函数
- students”表中有一个“score”字段表示学生的成绩，要计算所有学生的总成绩，可以使用：
```sql
SELECT SUM(score) FROM students;
```
3、AVG函数
- 计算平均成绩的语句为：
```sql
SELECT AVG(score) FROM students;
```
4、MAX和MIN函数
- 要找出最高成绩和最低成绩，可以分别使用“MAX”和“MIN”函数：
```sql
SELECT MAX(score) FROM students;
SELECT MIN(score) FROM students;
```
（三）分组查询（GROUP BY）
1、基本分组
- 假设要按照性别统计学生人数，可以使用“GROUP BY”子句，首先在“students”表中添加“gender”字段并插入一些数据，然后进行分组查询：
```sql
SELECT gender, COUNT(*) FROM students GROUP BY gender;
```
- 这里的“GROUP BY”子句按照“gender”字段对记录进行分组，COUNT(*)”函数计算每个组中的记录数。
2、带条件的分组（HAVING子句）
- 如果要查询人数大于2的性别分组，可以使用“HAVING”子句。“HAVING”子句类似于“WHERE”子句，但它是用于对分组后的结果进行筛选的。
```sql
SELECT gender, COUNT(*) FROM students GROUP BY gender HAVING COUNT(*) > 2;
```
SQL第三章中的高级查询
（一）多表连接查询
1、内连接（INNER JOIN）
- 当数据库中有多个相关的表时，例如有一个“courses”表（包含“course_id”、“course_name”等字段）和一个“student_courses”表（用于关联学生和课程，包含“student_id”和“course_id”等字段），要查询学生及其所选课程的信息，可以使用内连接。
```sql
SELECT students.student_name, courses.course_name
FROM students
INNER JOIN student_courses ON students.student_id = student_courses.student_id
INNER JOIN courses ON student_courses.course_id = courses.course_id;
```
- 内连接只会返回在两个表中匹配的记录，在这个例子中，只有当“students”表中的“student_id”与“student_courses”表中的“student_id”匹配，student_courses”表中的“course_id”与“courses”表中的“course_id”匹配时，才会返回结果。
2、外连接（LEFT JOIN、RIGHT JOIN、FULL JOIN）
左外连接（LEFT JOIN）
- 如果想要查询所有学生及其所选课程的信息，包括那些没有选课的学生，可以使用左外连接，左外连接以“LEFT JOIN”关键字左边的表（在这个例子中是“students”表）为基础，返回左边表中的所有记录以及与右边表（“student_courses”表）匹配的记录，如果右边表中没有匹配的记录，则相应的字段值为NULL。
```sql
SELECT students.student_name, student_courses.course_id
FROM students
LEFT JOIN student_courses ON students.student_id = student_courses.student_id;
```
右外连接（RIGHT JOIN）
- 右外连接与左外连接相反，以“RIGHT JOIN”关键字右边的表为基础，要查询所有课程以及选修这些课程的学生信息（包括没有学生选修的课程），可以使用右外连接。
```sql
SELECT courses.course_name, student_courses.student_id
FROM student_courses
RIGHT JOIN courses ON student_courses.course_id = courses.course_id;
```
全外连接（FULL JOIN）
- 全外连接会返回两个表中的所有记录，无论是否匹配，不过，在一些数据库系统（如MySQL）中不直接支持“FULL JOIN”，可以通过组合“LEFT JOIN”和“RIGHT JOIN”来实现类似的效果。
```sql
SELECT
FROM (
SELECT * FROM students
LEFT JOIN student_courses ON students.student_id = student_courses.student_id
) AS left_result
RIGHT JOIN (
SELECT * FROM student_courses
RIGHT JOIN courses ON student_courses.course_id = courses.course_id
) AS right_result ON left_result.student_courses_id = right_result.student_courses_id;
```
（二）子查询
1、单行子查询
图片来源于网络，如有侵权联系删除
- 子查询是嵌套在其他查询中的查询，要查询成绩高于平均成绩的学生，可以先计算平均成绩，然后在主查询中使用这个结果。
```sql
SELECT student_name, score
FROM students
WHERE score > (SELECT AVG(score) FROM students);
```
- 这里内部的“SELECT AVG(score) FROM students”就是子查询，它返回一个单一的值（平均成绩），外部查询根据这个值筛选出成绩高于平均成绩的学生记录。
2、多行子查询（IN、ANY、ALL）
IN子查询
- 假设存在一个“top_students”表，其中包含成绩优秀的学生的“student_id”，要查询这些优秀学生在“students”表中的信息，可以使用“IN”子查询。
```sql
SELECT * FROM students
WHERE student_id IN (SELECT student_id FROM top_students);
```
ANY和ALL子查询
- “ANY”和“ALL”子查询用于比较一个值和子查询返回的一组值，有一个“class_scores”表记录了不同班级的学生成绩，要查询成绩比某个班级中任何一个学生成绩都高的学生，可以使用“ANY”子查询。
```sql
SELECT student_name, score
FROM students
WHERE score > ANY (SELECT score FROM class_scores WHERE class_id = 1);
```
- 如果要查询成绩比某个班级中所有学生成绩都高的学生，则使用“ALL”子查询：
```sql
SELECT student_name, score
FROM students
WHERE score > ALL (SELECT score FROM class_scores WHERE class_id = 1);
```
SQL第三章中的数据完整性
（一）约束（Constraints）
1、主键约束（PRIMARY KEY）
- 如前面创建“students”表时提到的，主键约束用于确保表中的某一列或一组列的值是唯一的，并且不为空，它是表中记录的标识，student_id”作为“students”表的主键，保证了每个学生都有一个唯一的学号。
- 在关系数据库中，主键约束有助于维护数据的完整性，防止数据的重复和混乱。
2、外键约束（FOREIGN KEY）
- 在多表关系中，外键约束用于建立表之间的关联，在“student_courses”表中，“student_id”是指向“students”表的外键，“course_id”是指向“courses”表的外键。
- 外键约束确保了数据的一致性，在“student_courses”表中插入的“student_id”必须是“students”表中存在的“student_id”值，防止出现无效的关联。
3、唯一约束（UNIQUE）
- 唯一约束与主键约束类似，但它允许字段为空值（NULL），如果在“students”表中有一个“email”字段，要求每个学生的电子邮件地址是唯一的（允许部分学生没有填写电子邮件地址），可以使用唯一约束。
```sql
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
age INT,
email VARCHAR(100) UNIQUE
);
```
4、检查约束（CHECK）
- 检查约束用于限制字段的值的范围，在“students”表中，如果要确保年龄在15到30岁之间，可以使用检查约束：
```sql
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
age INT CHECK (age BETWEEN 15 AND 30),
email VARCHAR(100) UNIQUE
);
```
（二）事务（Transactions）
1、事务的概念
- 事务是一组数据库操作，这些操作要么全部成功执行，要么全部不执行，在银行转账系统中，从一个账户转出资金并转入另一个账户是一个事务，如果转出操作成功，但转入操作失败，整个事务应该回滚，即恢复到转账操作之前的状态，以确保数据的一致性。
2、事务的特性（ACID）
原子性（Atomicity）
- 事务是原子的，意味着事务中的所有操作被视为一个不可分割的单元，就像前面提到的银行转账例子，如果转账事务中的任何一个操作失败，整个转账操作（包括转出和转入）都应该被取消，数据库应该恢复到事务开始之前的状态。
一致性（Consistency）
- 事务必须保持数据库的一致性，在转账事务中，转账前后银行账户的总金额应该保持不变（不考虑手续费等其他因素），如果转账操作破坏了这种一致性（例如转出后总金额减少但转入失败导致总金额没有增加），则事务是失败的。
隔离性（Isolation）
- 多个事务并发执行时，每个事务应该感觉不到其他事务的存在，它们之间是相互隔离的，有两个转账事务同时进行，一个从账户A转账到账户B，另一个从账户C转账到账户D，这两个事务应该相互独立，不会相互干扰，不同的数据库系统提供了不同的隔离级别来控制事务之间的相互影响程度。
持久性（Durability）
- 一旦事务提交成功，其对数据库的修改应该是永久性的，即使在事务提交后数据库系统发生故障（如断电、硬件故障等），已提交事务的修改也不会丢失，这通常是通过数据库的日志系统等机制来实现的。
3、事务的操作（BEGIN、COMMIT、ROLLBACK）
- 在SQL中，可以使用“BEGIN”语句开始一个事务，“COMMIT”语句提交事务（使事务中的所有操作生效），“ROLLBACK”语句回滚事务（取消事务中的所有操作），在一个简单的数据库更新事务中：
```sql
BEGIN;
UPDATE students SET age = age + 1 WHERE student_name = 'John';
UPDATE students SET age = age - 1 WHERE student_name = 'Alice';
COMMIT;
```
- 如果在执行这两个更新操作的过程中出现错误，可以使用“ROLLBACK”语句取消这两个操作，使数据库恢复到事务开始之前的状态。
关系数据库标准语言SQL第三章涵盖了从基础的查询操作到高级的多表连接、子查询以及数据完整性等重要内容，这些内容对于有效地管理关系数据库、确保数据的准确性和一致性以及实现复杂的业务逻辑都具有至关重要的意义，通过深入理解和熟练掌握这些内容，数据库管理员和开发人员能够更好地构建、维护和操作关系数据库系统。
评论列表