本文目录导读：
关系数据库标准语言SQL总结笔记
SQL概述
关系数据库标准语言SQL（Structured Query Language）是用于管理关系数据库的通用语言，它具有以下重要特点：
图片来源于网络，如有侵权联系删除
（一）功能一体化
1、数据定义（DDL）
- 可以创建、修改和删除数据库对象，如数据库（CREATE DATABASE、DROP DATABASE）、表（CREATE TABLE、ALTER TABLE、DROP TABLE）、视图（CREATE VIEW、DROP VIEW）等，创建一个包含学生信息的表：
```sql
CREATE TABLE student (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
age INT,
major VARCHAR(30)
);
```
- 在定义表结构时，能够指定数据类型（如整数型INT、字符型VARCHAR等）、约束条件（如主键约束PRIMARY KEY、唯一约束UNIQUE等），主键约束确保表中的每行记录具有唯一标识，这有助于维护数据的完整性和一致性。
2、数据操纵（DML）
- 实现对数据库中数据的插入（INSERT）、更新（UPDATE）和删除（DELETE）操作，向student表中插入一条学生记录：
```sql
INSERT INTO student (student_id, student_name, age, major)
VALUES (1, 'John', 20, 'Computer Science');
```
- 当需要修改学生的年龄时，可以使用UPDATE语句：
```sql
UPDATE student
SET age = 21
WHERE student_id = 1;
```
- 如果某个学生退学，需要从表中删除其记录，可以使用DELETE语句：
```sql
DELETE FROM student
WHERE student_id = 1;
```
3、数据查询（DQL）
- SQL最强大的功能之一就是数据查询，通过SELECT语句可以从一个或多个表中检索数据，查询所有计算机科学专业的学生信息：
```sql
SELECT * FROM student
WHERE major = 'Computer Science';
```
- 还可以进行多表连接查询，如从学生表student和选课表course_selection中查询学生的选课信息：
```sql
SELECT student.student_name, course_selection.course_name
FROM student
JOIN course_selection ON student.student_id = course_selection.student_id;
```
- 并且能够使用聚合函数（如SUM、AVG、COUNT等）进行数据统计，计算每个专业的学生人数：
```sql
SELECT major, COUNT(*) AS student_count
FROM student
GROUP BY major;
```
4、数据控制（DCL）
- 用于管理数据库用户的权限，包括授予（GRANT）和收回（REVOKE）权限，授予用户user1对student表的查询权限：
```sql
GRANT SELECT ON student TO user1;
```
- 如果需要收回该用户的查询权限，可以使用REVOKE语句：
```sql
REVOKE SELECT ON student FROM user1;
```
（二）非过程化语言
1、用户无需指定执行顺序
- 与传统的编程语言（如C、Java等）不同，SQL用户不需要详细指定数据操作的执行步骤和顺序，在查询学生信息时，用户只需描述想要获取的结果（如查询所有年龄大于20岁的学生），而数据库管理系统（DBMS）会自动确定如何从存储的数据中获取这些结果，它会根据表的索引、数据存储结构等内部机制来优化查询执行计划，以提高查询效率。
2、关注结果而非过程
- SQL更关注的是要得到什么样的数据结果，而不是如何获取这些数据的具体过程，这使得数据库用户可以更专注于业务逻辑和数据需求，当需要查询每个专业的平均年龄时，用户只需要写出类似“SELECT major, AVG(age) FROM student GROUP BY major”这样的语句，而不需要关心数据库是如何从磁盘存储中读取数据、如何计算平均值等底层操作。
SQL的数据类型
1、数值类型
- 包括整数类型（如TINYINT、SMALLINT、INT、BIGINT等）和小数类型（如FLOAT、DOUBLE、DECIMAL等），整数类型用于存储整数值，不同的整数类型可以表示不同范围的数值，TINYINT可以表示 - 128到127之间的整数，而BIGINT可以表示非常大的整数范围，小数类型用于存储带有小数部分的数据，其中DECIMAL类型可以精确地表示小数，适合存储货币等对精度要求较高的数据。
2、字符类型
- 常见的有CHAR和VARCHAR，CHAR类型是定长字符类型，当定义为CHAR(10)时，无论实际存储的字符长度是多少，都会占用10个字节的存储空间，VARCHAR类型是变长字符类型，它只占用实际存储字符长度加上少量字节（用于存储长度信息）的存储空间，存储一个人的名字，使用VARCHAR类型可以更节省存储空间。
3、日期和时间类型
- 有DATE（存储日期，如'2023 - 01 - 01'）、TIME（存储时间，如'12:30:00'）、DATETIME（存储日期和时间，如'2023 - 01 - 01 12:30:00'）等类型，这些类型方便在数据库中存储与时间相关的信息，并且可以进行日期和时间的计算，如计算两个日期之间的间隔等。
SQL的表操作
1、表的创建
- 在创建表时，除了定义列名、数据类型和约束条件外，还可以设置默认值，创建一个订单表order_table：
```sql
CREATE TABLE order_table (
order_id INT PRIMARY KEY,
order_date DATE DEFAULT CURRENT_DATE,
customer_id INT,
total_amount DECIMAL(10, 2)
);
```
- 这里的order_date列设置了默认值为当前日期，当插入新订单记录时，如果没有指定order_date的值，数据库会自动将当前日期作为该列的值。
2、表的修改
- 可以使用ALTER TABLE语句对表进行修改，添加一个新列到student表中：
```sql
ALTER TABLE student
ADD COLUMN email VARCHAR(50);
```
- 或者修改列的数据类型，如将student表中的age列的数据类型从INT修改为SMALLINT：
```sql
ALTER TABLE student
MODIFY COLUMN age SMALLINT;
图片来源于网络，如有侵权联系删除
```
3、表的删除
- 当不再需要某个表时，可以使用DROP TABLE语句将其删除，删除一个临时测试表test_table：
```sql
DROP TABLE test_table;
```
- 需要注意的是，表删除操作是不可逆的，所以在执行删除操作之前应该谨慎确认。
SQL的约束
1、主键约束（PRIMARY KEY）
- 主键是表中的一列或多列组合，用于唯一标识表中的每一行记录，在student表中，student_id列被定义为主键：
```sql
CREATE TABLE student (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
age INT,
major VARCHAR(30)
);
```
- 主键约束确保了数据的唯一性，并且不允许主键列的值为NULL，这有助于在数据库中准确地定位和操作每一条记录。
2、外键约束（FOREIGN KEY）
- 外键用于建立两个表之间的关系，在选课表course_selection中，student_id列是一个外键，它引用了student表中的student_id列：
```sql
CREATE TABLE course_selection (
selection_id INT PRIMARY KEY,
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES student(student_id)
);
```
- 外键约束确保了数据的参照完整性，即选课表中的student_id值必须是学生表中已存在的student_id值，这样可以防止出现孤立的数据，例如不存在的学生却有选课记录的情况。
3、唯一约束（UNIQUE）
- 唯一约束确保表中的某一列或多列组合的值是唯一的，与主键约束不同的是，唯一约束列可以包含NULL值（但只能有一个NULL值），在员工表employee中，可以为员工的身份证号码列设置唯一约束：
```sql
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
id_card_number VARCHAR(18) UNIQUE,
department VARCHAR(30)
);
```
4、非空约束（NOT NULL）
- 非空约束指定表中的列不允许为NULL值，在学生表中，学生姓名student_name列应该设置为非空约束：
```sql
CREATE TABLE student (
student_id INT PRIMARY KEY,
student_name VARCHAR(50) NOT NULL,
age INT,
major VARCHAR(30)
);
```
- 这有助于确保数据的完整性，因为在实际应用中，学生姓名是一个必要的信息，不应该为空。
SQL的查询操作
1、单表查询
- 单表查询是最基本的查询操作，可以使用SELECT语句选择表中的列，并可以使用WHERE子句进行条件筛选，查询年龄大于20岁的学生姓名：
```sql
SELECT student_name FROM student
WHERE age > 20;
```
- 还可以使用ORDER BY子句对查询结果进行排序，按照年龄从小到大对学生进行排序：
```sql
SELECT * FROM student
ORDER BY age;
```
- 以及使用LIMIT子句限制查询结果的数量，只查询前5名学生的信息：
```sql
SELECT * FROM student
LIMIT 5;
```
2、多表查询
内连接（INNER JOIN）
- 内连接返回两个表中满足连接条件的行，查询学生及其所选课程的信息：
```sql
SELECT student.student_name, course.course_name
FROM student
INNER JOIN course_selection ON student.student_id = course_selection.student_id
INNER JOIN course ON course_selection.course_id = course.course_id;
```
- 这里通过student_id和course_id两个连接条件，将student表、course_selection表和course表进行内连接，只返回在选课表中有选课记录的学生及其所选课程的信息。
外连接（LEFT JOIN、RIGHT JOIN、FULL JOIN）
- 左外连接（LEFT JOIN）返回左表中的所有行以及右表中满足连接条件的行，查询所有学生及其所选课程的信息（即使有些学生没有选课也显示）：
```sql
SELECT student.student_name, course.course_name
FROM student
LEFT JOIN course_selection ON student.student_id = course_selection.student_id
LEFT JOIN course ON course_selection.course_id = course.course_id;
```
- 右外连接（RIGHT JOIN）则返回右表中的所有行以及左表中满足连接条件的行，而全外连接（FULL JOIN）返回两个表中的所有行，当连接条件不满足时，对应的列值为NULL，不过需要注意的是，不同的数据库管理系统对全外连接的支持情况可能有所不同。
3、子查询
- 子查询是嵌套在其他查询中的查询，查询年龄大于平均年龄的学生信息：
```sql
图片来源于网络，如有侵权联系删除
SELECT * FROM student
WHERE age > (SELECT AVG(age) FROM student);
```
- 这里先通过内部子查询计算出学生的平均年龄，然后在外部查询中筛选出年龄大于平均年龄的学生记录，子查询可以出现在SELECT、FROM、WHERE等子句中，并且可以是多层嵌套的。
4、聚合函数查询
- 聚合函数用于对一组数据进行统计计算，常见的聚合函数有SUM（求和）、AVG（求平均值）、COUNT（计数）、MAX（求最大值）和MIN（求最小值）等，计算学生的平均年龄：
```sql
SELECT AVG(age) FROM student;
```
- 计算每个专业的学生人数：
```sql
SELECT major, COUNT(*) FROM student
GROUP BY major;
```
- 在使用聚合函数时，常常会结合GROUP BY子句对数据进行分组统计，如果想要进一步筛选分组后的结果，可以使用HAVING子句，查询学生人数大于10的专业：
```sql
SELECT major, COUNT(*) AS student_count
FROM student
GROUP BY major
HAVING student_count > 10;
```
SQL的视图操作
1、视图的创建
- 视图是从一个或多个表（或其他视图）中导出的虚拟表，创建视图可以简化复杂的查询操作，创建一个视图，显示计算机科学专业的学生信息：
```sql
CREATE VIEW computer_science_students AS
SELECT * FROM student
WHERE major = 'Computer Science';
```
- 视图并不存储实际的数据，它只是一个查询定义，当对视图进行查询时，数据库会根据视图的定义从基础表中获取数据。
2、视图的查询
- 可以像查询普通表一样查询视图，查询视图computer_science_students中的学生姓名：
```sql
SELECT student_name FROM computer_science_students;
```
3、视图的更新
- 在某些情况下，可以通过视图对基础表进行更新操作，这需要满足一定的条件，例如视图定义中的查询不能包含聚合函数、DISTINCT关键字、GROUP BY、HAVING等子句，如果视图是基于多个表创建的，并且包含连接操作，那么更新操作可能会受到更多限制，如果视图computer_science_students满足可更新的条件，就可以使用UPDATE语句通过视图来更新学生的年龄：
```sql
UPDATE computer_science_students
SET age = 22
WHERE student_id = 1;
```
4、视图的删除
- 当视图不再需要时，可以使用DROP VIEW语句将其删除，删除视图computer_science_students：
```sql
DROP VIEW computer_science_students;
```
SQL的存储过程和函数
1、存储过程
- 存储过程是一组预编译的SQL语句，它们被存储在数据库中，可以被重复调用，存储过程可以接受输入参数，并可以返回输出参数或结果集，创建一个存储过程，用于根据学生的专业查询学生信息：
```sql
CREATE PROCEDURE get_students_by_major (IN major_name VARCHAR(30))
BEGIN
SELECT * FROM student
WHERE major = major_name;
END;
```
- 可以使用CALL语句调用存储过程：
```sql
CALL get_students_by_major('Computer Science');
```
- 存储过程具有以下优点：
- 提高性能：预编译的特性使得存储过程在执行时不需要再次解析和编译SQL语句，从而提高了执行效率。
- 封装业务逻辑：可以将复杂的业务逻辑封装在存储过程中，使得数据库应用程序的结构更加清晰。
- 安全性：可以通过授予用户执行存储过程的权限，而不必授予用户对基础表的直接访问权限，从而提高数据库的安全性。
2、函数
- SQL函数是一种可返回单个值的命名程序块，函数可以接受输入参数，并根据输入参数进行计算，最终返回一个结果，创建一个函数，用于计算两个整数的和：
```sql
CREATE FUNCTION add_numbers (IN num1 INT, IN num2 INT)
RETURNS INT
BEGIN
RETURN num1 + num2;
END;
```
- 可以在SQL查询中使用函数，
```sql
SELECT add_numbers(1, 2) AS result;
```
- 函数与存储过程的区别在于：
- 函数必须返回一个值，而存储过程可以不返回值或者返回多个结果集。
- 函数可以在SQL表达式中使用，而存储过程通常是通过CALL语句单独调用。
SQL的事务处理
1、事务的概念
- 事务是一组作为单个逻辑工作单元执行的操作，这些操作要么全部成功执行，要么全部失败回滚，在银行转账业务中，从一个账户转出资金并转入另一个账户的操作应该作为一个事务来处理，如果转出操作成功而转入操作失败，那么整个事务应该回滚，以确保数据的一致性。
2、事务的特性（ACID）
原子性（Atomicity）
- 事务中的所有操作是一个不可分割的整体，要么全部执行成功，要么全部失败回滚，在订单处理系统中，创建订单、扣减库存和更新用户订单记录等操作应该作为一个原子事务，如果其中任何一个操作失败，整个订单处理过程应该回滚到初始状态，就好像这个事务从未发生过一样。
一致性（Consistency）
- 事务执行前后，数据库的状态必须保持一致，这意味着事务必须遵循数据库的完整性约束，在转账事务中，转账前后银行账户的总金额应该
评论列表