数据库系统概论期末复习重点笔记(一)

一、数据库系统概述

1. 数据库的定义：

   • 数据库是一个组织、存储和管理数据的集合，可通过计算机进行访问和操作。

2. 数据库管理系统（DBMS）：

   • DBMS是一种软件，用于管理数据库系统的创建、维护、查询和操作。
   • DBMS负责数据的安全性、完整性、一致性和持久性。

3. 数据库系统的优势：

   • 数据共享：多用户可以访问和共享数据。
   • 数据一致性：通过数据库约束和事务保证数据一致性。
   • 数据独立性：数据与应用程序解耦，使得数据结构的修改不影响应用程序。
   • 数据安全性：通过权限管理和加密等手段保护数据安全。

4. 数据库模型：

   • 关系型模型（RDBMS）：数据以表格的形式进行组织，使用结构化查询语言（SQL）进行操作。
   • 非关系型模型（NoSQL）：采用灵活的数据模型，如文档型、键值型、列族型等，适用于大规模和分布式环境。

二、SQL语言

1. SQL语句结构：

        SQL语句通常由关键字、表名、列名、操作符、值等组成，语句以分号（;）结束。

2. 常见的SQL命令：

• CREATE DATABASE：用于创建数据库。

        演示如何使用 SQL 创建一个名为 university 的数据库：

CREATE DATABASE university;

这条 SQL 语句将在数据库管理系统中创建一个名为 university 的数据库。数据库一旦创建成功，你就可以在其中创建表、插入数据以及执行其他数据库操作。

• DROP DATABASE：用于删除数据库。

        使用 SQL 删除一个名为 university 的数据库：

DROP DATABASE university;

这条 SQL 语句将会删除数据库管理系统中的名为 university 的数据库。请注意，执行此操作将永久删除数据库及其所有相关数据，因此在执行之前务必确认是否真的需要删除数据库，并且备份重要数据以防意外发生。

• CREATE TABLE： 用于创建表格。

        假设我们要创建一个名为students的表，用于存储学生的信息，包括学生ID、姓名、年龄和所在班级。可以使用以下SQL语句创建这个表：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    class VARCHAR(20)
);

解释：

• CREATE TABLE：这是SQL语句的关键字，用于指示要创建一个新表。
• students：这是要创建的表的名称。
• ( )：括号内包含了表的列定义。
• student_id INT PRIMARY KEY：定义了一个名为student_id的列，数据类型为整数（INT），并将其设置为主键（PRIMARY KEY）。主键是唯一标识表中每行的值，确保每行的唯一性。
• name VARCHAR(50)：定义了一个名为name的列，数据类型为可变长度字符型（VARCHAR），最大长度为50个字符。
• age INT：定义了一个名为age的列，数据类型为整数（INT）。
• class VARCHAR(20)：定义了一个名为class的列，数据类型为可变长度字符型（VARCHAR），最大长度为20个字符。

通过执行以上SQL语句，你就可以成功创建一个名为students的表，用于存储学生的信息。

• DROP TABLE：用于删除表格。

        删除一个名为 students 的表：

DROP TABLE students;

这条 SQL 语句将会删除数据库中的名为 students 的表。请注意，执行此操作将永久删除表及其所有数据，因此在执行之前务必确认是否真的需要删除表，并且备份重要数据以防意外发生。

• ALTER TABLE：用于修改表格。 

ALTER TABLE 命令可以用于对表进行多种类型的修改，包括修改列、添加约束、删除约束、重命名表等。以下是一些常见的 ALTER TABLE 命令的示例：

        1. 修改列

        修改列名和数据类型：

ALTER TABLE table_name MODIFY COLUMN old_column_name new_data_type;

        添加列约束：

ALTER TABLE table_name MODIFY COLUMN column_name constraint_definition;

        2. 添加列

ALTER TABLE table_name ADD COLUMN column_name data_type;

     假设我们要向 students 表中添加一个新的列 email，用于存储学生的电子邮件地址。可以使用以下 SQL 语句：

ALTER TABLE students ADD COLUMN email VARCHAR(100);

这条 SQL 语句将会向 students 表中添加一个名为 email 的新列，数据类型为 VARCHAR(100)，即最大长度为 100 的字符串类型。

你也可以使用 ALTER TABLE 命令进行其他类型的修改，比如修改列的数据类型、删除列、添加约束等。具体的语法和用法取决于你的需求和数据库管理系统的支持。

        3. 删除列

ALTER TABLE table_name DROP COLUMN column_name;

        4. 添加约束

        添加主键：

ALTER TABLE table_name ADD CONSTRAINT pk_constraint_name PRIMARY KEY (column_name);

        假设我们有一个名为 students 的表，其中包含 student_id 和 student_name 列，现在我们希望将 student_id 列设为主键。

ALTER TABLE students ADD CONSTRAINT pk_students PRIMARY KEY (student_id);

这条 SQL 语句将会在 students 表中添加一个名为 pk_students 的主键约束，该约束将 student_id 列设为主键。这意味着 student_id 列中的值将是唯一的，并且不能为空。

请注意，如果表中已经存在其他主键约束，你需要先删除或修改现有的主键约束，然后再添加新的主键约束

        添加外键：

ALTER TABLE table_name ADD CONSTRAINT fk_constraint_name FOREIGN KEY (column_name) REFERENCES parent_table (parent_column);

        5. 删除约束

        删除主键：

ALTER TABLE table_name DROP CONSTRAINT pk_constraint_name;

        删除外键：

ALTER TABLE table_name DROP CONSTRAINT fk_constraint_name;

        6. 其他修改

        重命名表：

ALTER TABLE old_table_name RENAME TO new_table_name;

        修改表名：

ALTER TABLE table_name RENAME column old_column_name TO new_column_name;

        这些是一些常见的 ALTER TABLE 命令示例，用于对表进行各种类型的修改。具体的语法和用法可能因数据库管理系统的不同而有所不同，因此在实际使用时，请参考相应数据库系统的文档。

• INSERT INTO： 用于向表格中插入新的行。

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

        假设我们有以下学生信息需要插入到 students 表中：

• 学生ID: 1
• 姓名: Alice
• 年龄: 20
• 班级: A1

可以使用以下 SQL 语句将这些数据插入到 students 表中：

INSERT INTO students (student_id, name, age, class)
VALUES (1, 'Alice', 20, 'A1');

解释：

• INSERT INTO students：这是 SQL 语句的关键字，用于指示要向哪个表中插入数据，这里是 students 表。
• (student_id, name, age, class)：指定要插入数据的列，确保与 VALUES 子句中的值一一对应。
• VALUES (1, 'Alice', 20, 'A1')：这里指定了要插入的具体值，分别对应于 student_id、name、age 和 class 列。

执行以上 SQL 语句后，就会向 students 表中插入一行数据，即表示 Alice 学生的信息。

• SELECT： 用于从一个或多个表格中选择数据。

SELECT column1, column2, ...
FROM table_name
WHERE condition;

        假设我们想要检索 students 表中所有学生的信息，包括学生ID、姓名、年龄和班级。可以使用以下 SQL 语句：

SELECT * FROM students;

解释：

• SELECT *：这是 SQL 语句的关键字，用于指示要检索哪些列的数据。星号 * 表示所有列。
• FROM students：这是 SQL 语句的关键字，用于指示要从哪个表中检索数据，这里是 students 表。

执行以上 SQL 语句后，将会检索 students 表中所有学生的信息。

        如果我们只想要检索特定列的数据，例如只想要检索学生的姓名和年龄，可以这样写：

SELECT name, age FROM students;

这样就只会检索 students 表中所有学生的姓名和年龄信息。

这是 SELECT 语句的基本用法。也可以通过添加条件来过滤数据，使用聚合函数（见本文常见函数）进行统计，或者进行多表连接等操作，以满足不同的查询需求。

• 嵌套查询 

嵌套查询是指在 SQL 查询中嵌套另一个查询，内部查询的结果作为外部查询的条件之一。

        假设我们有两个表，一个是 students 表，包含学生的姓名和成绩，另一个是 grades 表，包含不同成绩对应的等级。我们想要查询每个学生的成绩以及对应的等级，可以使用嵌套查询来实现。

SELECT student_name, score, (SELECT grade FROM grades WHERE min_score <= score ORDER BY min_score DESC LIMIT 1) AS grade FROM students;

ORDER BY min_score DESC 意思是按照 min_score 列进行降序排序。DESC 是降序的意思，即按照从大到小的顺序排列。

LIMIT 1 则是限制结果集只返回第一个结果。因为我们是在查找满足条件的最高分数等级，所以我们只需要返回排序后的第一个结果，即最高分数对应的等级。

在这个查询中，内部查询 (SELECT grade FROM grades WHERE min_score <= score ORDER BY min_score DESC LIMIT 1) 返回了对应于学生成绩的等级，它在外部查询中作为一个字段 grade 返回。内部查询首先查找满足条件 min_score <= score 的最高分数等级，然后将其返回给外部查询。

这是一个简单的嵌套查询示例，用于根据外部查询的结果动态获取内部查询的条件或数据。在实际应用中，嵌套查询可以用于更复杂的数据分析和处理操作。

• UPDATE： 用于更新表格中的现有行。

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

        假设我们需要更新学生ID为 1 的学生的年龄为 21 岁，可以使用以下 SQL 语句：

UPDATE students SET age = 21 WHERE student_id = 1;

解释：

• UPDATE students：这是 SQL 语句的关键字，用于指示要更新哪个表中的数据，这里是 students 表。
• SET age = 21：这是 SQL 语句的关键字，用于指定要更新的列和它们的新值。在这个例子中，我们将 age 列设置为 21。
• WHERE student_id = 1：这是 SQL 语句的关键字，用于指定更新的条件。在这个例子中，我们只想更新学生ID为 1 的学生的信息。

执行以上 SQL 语句后，将会更新 students 表中学生ID为 1 的学生的年龄为 21 岁

• DELETE FROM： 用于从表格中删除行。

DELETE FROM table_name
WHERE condition;

        假设我们需要删除学生ID为 1 的学生的信息，可以使用以下 SQL 语句：

DELETE FROM students WHERE student_id = 1;

解释：

• DELETE FROM students：这是 SQL 语句的关键字，用于指示要从哪个表中删除数据，这里是 students 表。
• WHERE student_id = 1：这是 SQL 语句的关键字，用于指定删除的条件。在这个例子中，我们只想删除学生ID为 1 的学生的信息。

执行以上 SQL 语句后，将会从 students 表中删除学生ID为 1 的学生的信息。

需要注意的是，DELETE 语句会直接删除符合条件的数据，因此务必谨慎使用，以免误删数据。通常在执行 DELETE 语句之前，最好先使用 SELECT 语句确认要删除的数据。

• GRANT：用于授予用户执行特定操作的权限。

        假设我们有一个名为 students 的表，我们想要授予用户 user1 对这个表的 SELECT 权限。

GRANT SELECT ON students TO user1;

这条 SQL 语句将会授予 user1 对 students 表的 SELECT 权限，允许他查询表中的数据。

需要注意的是，具体的语法和用法可能因数据库管理系统的不同而有所不同。上述示例适用于大多数主流的关系型数据库系统，如 MySQL、PostgreSQL、Oracle 等。

• REVOKE：用于撤销用户的权限。

        假设我们之前授予了用户 user1 对表 students 的 SELECT 权限，现在我们想要撤销这个权限。

REVOKE SELECT ON students FROM user1;

这条 SQL 语句将会撤销用户 user1 对表 students 的 SELECT 权限，不再允许他查询表中的数据。

需要注意的是，撤销权限的同时会撤销之前授予的所有权限，因此在执行 REVOKE 命令时需要谨慎操作，以免影响到其他用户的权限设置。

3. SQL中的基本操作符：

• 比较操作符： =、<、>、<=、>=、<>等。
• 逻辑操作符： AND、OR、NOT等。
• 通配符操作符： %（匹配零个或多个字符）、_（匹配一个字符）等。

4.SQL中的常见数据类型

1. 整数类型：

   • INT：整数类型，通常用于存储整数值。其大小通常是平台相关的，但通常范围在-2^31到2^31-1之间。
   • SMALLINT：较小的整数类型，通常用于存储较小范围的整数值。大小通常在-32768到32767之间。
   • BIGINT：较大的整数类型，用于存储大范围的整数值。大小通常在-2^63到2^63-1之间。

2. 浮点数类型：

   • FLOAT：单精度浮点数，用于存储小数值。它有固定的精度和范围。
   • DOUBLE 或 REAL：双精度浮点数，用于存储更大范围和更高精度的小数值。

3. 字符类型：

   • CHAR(n)：固定长度的字符类型，其中 n 表示字符的最大长度。
   • VARCHAR(n)：可变长度的字符类型，其中 n 表示字符的最大长度。VARCHAR 类型可以节省存储空间，因为它只存储实际使用的字符。
   • TEXT：用于存储大量文本数据，没有长度限制。

4. 日期和时间类型（Date and Time Types）：

   • DATE：用于存储日期（年、月、日）yyyy-mm-dd,如：2023-02-16。
   • TIME：用于存储时间（时、分、秒）hh:mm:ss,如：15：30：02。
   • DATETIME 或 TIMESTAMP：用于存储日期和时间的组合值。

5. 布尔类型（Boolean Type）：

   • BOOLEAN 或 BOOL：用于存储布尔值，即 true 或 false。

5.SQL中的函数：

        SQL提供了许多内置函数，用于数据操作、数学运算、字符串处理等，如COUNT()、SUM()、AVG()、MAX()、MIN()、UPPER()、LOWER()等。

        以下是 SQL 中常见的一些内置函数，具体的函数列表可能会因数据库管理系统的不同而有所变化。要了解某个特定数据库系统支持的函数及其用法，请参考相应的数据库文档。

    1.聚合函数：

  ·COUNT()：计算行数或非空值的数量。

        假设我们有一个名为 students 的表，用于存储学生的信息，现在我们想要统计表中学生的总数。可以使用以下 SQL 查询：

SELECT COUNT(*) AS total_students FROM students;

解释：

• SELECT COUNT(*)：这是 SQL 查询的部分，用于执行计数操作。COUNT(*) 表示计算表中的所有行数。
• AS total_students：这是为返回的计数值指定一个别名。在这个例子中，我们将计数值命名为 total_students。
• FROM students：这是 SQL 查询的一部分，指定要从哪个表中进行计数操作，这里是 students 表。

执行以上 SQL 查询后，将返回 students 表中学生的总数，并将其命名为 total_students。

        如果要统计某个特定列中非空值的数量，可以指定列名而不是使用 *。例如，要统计学生表中 age 列中非空值的数量，可以这样写：

SELECT COUNT(age) AS total_students_with_age FROM students;

这将返回 students 表中具有年龄信息的学生的数量，并将其命名为 total_students_with_age。

  ·SUM()：计算数值列的总和。

        假设我们有一个名为 sales 的表，用于存储销售订单信息，其中有一个 amount 列用于存储每个订单的销售金额。现在我们想要计算所有销售订单的总销售额。可以使用以下 SQL 查询：

SELECT SUM(amount) AS total_sales FROM sales;

解释：

• SELECT SUM(amount)：这是 SQL 查询的部分，用于执行求和操作。SUM(amount) 表示计算 amount 列中所有数值的总和。
• AS total_sales：这是为返回的总和值指定一个别名。在这个例子中，我们将总和值命名为 total_sales。
• FROM sales：这是 SQL 查询的一部分，指定要从哪个表中进行求和操作，这里是 sales 表。

执行以上 SQL 查询后，将返回 sales 表中所有销售订单的总销售额，并将其命名为 total_sales。

需要注意的是，SUM() 函数通常用于数值列，因此在使用时确保所选择的列包含数值类型的数据。如果所选择的列包含非数值类型的数据，SUM() 函数将会返回 NULL。

  ·AVG()：计算数值列的平均值。

        假设我们有一个名为 scores 的表，用于存储学生的考试成绩信息，其中有一个 score 列用于存储每个学生的成绩。现在我们想要计算所有学生的平均成绩。可以使用以下 SQL 查询：

SELECT AVG(score) AS average_score FROM scores;

解释：

• SELECT AVG(score)：这是 SQL 查询的部分，用于执行平均值计算操作。AVG(score) 表示计算 score 列中所有数值的平均值。
• AS average_score：这是为返回的平均值指定一个别名。在这个例子中，我们将平均值命名为 average_score。
• FROM scores：这是 SQL 查询的一部分，指定要从哪个表中进行平均值计算操作，这里是 scores 表。

执行以上 SQL 查询后，将返回 scores 表中所有学生的平均成绩，并将其命名为 average_score。

需要注意的是，AVG() 函数通常用于数值列，因此在使用时确保所选择的列包含数值类型的数据。如果所选择的列包含非数值类型的数据，AVG() 函数将会返回 NULL。

  ·MIN()：找到数值列的最小值。

        假设我们有一个名为 products 的表，用于存储产品信息，其中有一个 price 列用于存储每个产品的价格。现在我们想要找到所有产品中价格最低的产品。可以使用以下 SQL 查询：

SELECT MIN(price) AS min_price FROM products;

解释：

• SELECT MIN(price)：这是 SQL 查询的部分，用于执行最小值计算操作。MIN(price) 表示计算 price 列中所有数值的最小值。
• AS min_price：这是为返回的最小值指定一个别名。在这个例子中，我们将最小值命名为 min_price。
• FROM products：这是 SQL 查询的一部分，指定要从哪个表中进行最小值计算操作，这里是 products 表。

执行以上 SQL 查询后，将返回 products 表中所有产品的最低价格，并将其命名为 min_price。

需要注意的是，MIN() 函数通常用于数值列，因此在使用时确保所选择的列包含数值类型的数据。如果所选择的列包含非数值类型的数据，MIN() 函数将会返回 NULL。

  ·MAX()：找到数值列的最大值。

        假设我们有一个名为 products 的表，用于存储产品信息，其中有一个 price 列用于存储每个产品的价格。现在我们想要找到所有产品中价格最高的产品。可以使用以下 SQL 查询：

SELECT MAX(price) AS max_price FROM products;

解释：

• SELECT MAX(price)：这是 SQL 查询的部分，用于执行最大值计算操作。MAX(price) 表示计算 price 列中所有数值的最大值。
• AS max_price：这是为返回的最大值指定一个别名。在这个例子中，我们将最大值命名为 max_price。
• FROM products：这是 SQL 查询的一部分，指定要从哪个表中进行最大值计算操作，这里是 products 表。

执行以上 SQL 查询后，将返回 products 表中所有产品的最高价格，并将其命名为 max_price。

需要注意的是，MAX() 函数通常用于数值列，因此在使用时确保所选择的列包含数值类型的数据。如果所选择的列包含非数值类型的数据，MAX() 函数将会返回 NULL。

    2.字符串函数：

  ·CONCAT()：连接两个或多个字符串。

  ·SUBSTRING() 或 SUBSTR()：提取子字符串。

  ·UPPER()：将字符串转换为大写。

  ·LOWER()：将字符串转换为小写。

  ·LENGTH() 或 LEN()：返回字符串的长度。

  ·TRIM()：删除字符串两端的空格。

  ·LEFT()：返回字符串左侧的指定长度的子字符串。

  ·RIGHT()：返回字符串右侧的指定长度的子字符串。

    3.日期和时间函数：

  ·NOW() 或 CURRENT_TIMESTAMP：返回当前日期和时间。

  ·DATE()：提取日期部分。

  ·TIME()：提取时间部分。

  ·YEAR()：提取年份。

  ·MONTH()：提取月份。

  ·DAY() 或 DAYOFMONTH()：提取日期中的天数。

  ·HOUR()：提取小时数。

  ·MINUTE()：提取分钟数。

  ·SECOND()：提取秒数。

  ·DATEDIFF()：计算两个日期之间的天数差异。

    4.逻辑函数：

  ·IF() 或 CASE：条件判断函数，根据条件返回不同的值。

        在MySQL中使用if函数举例：

        假设我们有一个名为 students 的表，其中包含 score 列，表示学生的分数。我们想要根据学生的分数判断他们的成绩等级，可以使用 IF() 函数。

SELECT 
    student_name,
    score,
    IF(score >= 90, 'A',
       IF(score >= 80, 'B',
          IF(score >= 70, 'C',
             IF(score >= 60, 'D', 'F')))) AS grade
FROM students;

这个查询会根据学生的分数给出相应的成绩等级并将其记录在grade中，如果分数大于等于 90 则为 A 等级，大于等于 80 则为 B 等级，以此类推。

    5.数学函数：

  ·ABS()：返回数值的绝对值。

  ·ROUND()：四舍五入为指定的小数位数。

  ·CEILING() 或 CEIL()：返回大于或等于指定数字的最小整数。

  ·FLOOR()：返回小于或等于指定数字的最大整数。

  ·SQRT()：返回数的平方根。

    6.类型转换函数：

  ·CAST()：将一个数据类型转换为另一个数据类型。

  ·CONVERT()：将一个数据类型转换为另一个数据类型（在一些数据库系统中）。

注意：未完全更新，后续还有更多内容~