跳转至

SQL 入门

Tip
  1. 使用 Ctrl + L 可以清除屏幕并将光标移到顶部 (macOS: 也是control + l)
  2. 千万别忘了分号！！！

基本概念

SQL（Structured Query Language，结构化查询语言）是一种用于管理和操作关系型数据库的声明性语言。

  1. 语言特性

    • 声明性：用户描述要做什么，不用关心如何它背后的实现过程。
    • 基于集合：支持一次操作多个记录。
    • 跨平台：在不同的数据库系统中通用。

  2. 基础功能

    • 数据查询 (SELECT)：检索数据
      SQL
      1
      SELECT * FROM employees WHERE department = 'Sales';
    • 数据插入 (INSERT)：插入新记录
      SQL
      1
      INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');
    • 数据更新 (UPDATE)：修改已有数据
      SQL
      1
      UPDATE employees SET department = 'Marketing' WHERE id = 1;
    • 数据删除 (DELETE)：删除记录
      SQL
      1
      DELETE FROM employees WHERE id = 1;
    • 表管理 (CREATE, ALTER, DROP)：创建、修改、删除表
      SQL
      1
      CREATE TABLE employees (id INT, first_name VARCHAR(50));

SQL有几个值得注意的特点：

  • 脚本语言（类似 Python）：可以交互执行也可以事先写好后一次性执行；
  • 弱类型：
    SQL
    1
2
3
4
5
6
7
    mysql> SELECT 1 + "1"; -- You can just do calculus in this way
+---------+
| 1 + "1" |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

  • 关键字不区分大小写；而数据库名，表名，字段名等区分大小写。如：

    SQL
    1
2
3
4
5
    mysql> cReAtE dAtAbAsE hello;
Query OK, 1 row affected (0.01 sec)

mysql> cReAtE dAtAbAsE Hello;
Query OK, 1 row affected (0.02 sec)

    这样就分别创建了 hello 和 Hello 两个 完全不同的数据库。

    SQL
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello              |
| hello              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.02 sec)
Warning

当出现数据库名，表名，字段名与关键字重复时，需要使用反引号`将其包裹。为了美观与可读性，建议 关键字统一使用大写表示

SQL
1
2
3
4
5
6
mysql> CREATE DATABASE table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table' at line 1
mysql> CREATE DATABASE `table`;
Query OK, 1 row affected (0.02 sec)

mysql>

跟关键字冲突的命名方式是不被系统许可的，因此如需要可以使用反引号包裹

（但是为什么要取跟关键字冲突的名字呢？这不是自找麻烦吗😄

数据类型

由于数据库的核心便是“数据”，不同的数据类型会影响数据的存储方式与检索方式，因此为每张表的字段选择最合适的数据类型是十分有必要的。MySQL 支持的类型大致可以分为数值、日期/时间和字符串三类，在此仅列举常用的类型：

  • 数值类型
    • INT：4 bytes，\([-2^{31},2^{31}-1]\)
    • BIGINT：8 bytes，\([-2^{63},2^{63}-1]\)
    • FLOAT：4 bytes
    • DOUBLE：8 bytes
    • 可使用 UNSIGEND 关键字来表示无符号类型，以表示更大的范围，例如：
      MySQL
      1
2
3
      CREATE TABLE your_table (
    your_column INT UNSIGNED
);
  • 字符串类型
    • CHAR：定长字符串，256 bytes，长度取值范围为 \([0,255]\) 个字符；
    • VARCHAR：变长字符串，长度取值范围为 \([0,65535]\) 个字符；
  • 日期和时间类型
    • TIMESTAMP：时间戳，形式为 YYYY-MM-DD hh:mm:ss，4 bytes，范围为 \([1970-01-01 00:00:01 \ \mathrm{UTC},2038-01-19 03:14:07 \ \mathrm{UTC}]\)，常见的写法有：
      MySQL
      1
2
3
4
      CREATE TABLE your_table (
    ...
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

特别的，NULL 表示空值（与 0 和 '' 作区分）。但字段在设置时应尽量“避免允许为 NULL”，这样可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断其是否为 NULL。

Warning

NULL就是NULL，不需要跟 0 和 '' 找所谓的异同

主键，外键与索引

alt text

我们根据这三张表详细解释下“主键、外键、索引”这组概念：

在这组表中，以下是主键、外键和索引的分析：

主键 (Primary Key)

  • Students 表:
    • id 字段：它是主键 (PRI) 并且 auto_increment，表示每次插入时自动生成唯一的标识符。
  • Courses 表:
    • id 字段：它是主键 (PRI) 并且 auto_increment。
  • Association 表:
    • id 字段：它是主键 (PRI) 并且 auto_increment。

外键 (Foreign Key)

  • Association 表:
    • student_id 字段：这个字段引用了 Students 表中的 id 字段，因此它是一个外键（MUL，可能是外键或索引）。
    • course_id 字段：这个字段引用了 Courses 表中的 id 字段，因此它也是一个外键（MUL，可能是外键或索引）。

索引 (Index)

  • Students 表:
    • sid 字段：它是唯一索引 (UNI)，确保每个学生的 sid 在表中都是唯一的。
  • Courses 表:
    • cid 字段：它是唯一索引 (UNI)，确保每门课程的 cid 在表中都是唯一的。

简要说明

  • 主键（Primary Key）用于唯一标识表中的每一行数据，因此主键字段具有 PRI 标识，并且通常不允许为空 (NULL)。
  • 外键（Foreign Key）用于引用其他表中的主键，确保数据之间的关系完整性，因此具有 MUL 标识（表明该字段可能是外键或索引的一部分）。
  • 索引（Index）通过 UNI 标识，可以确保字段中的数据唯一性，并且加快数据库查询的速度。

主键

关系型数据库要求 同一张数据表中的任意两条记录不得重复，要求能通过某字段唯一区分出不同的记录，该字段称为主键。常见的可作为主键字段的类型有：

  • 自增整数类型；
  • 全局唯一 GUID 类型：使用 GUID 算法生成一个全局唯一的字符串。

注意，尽管只要我们保证某一字段不重复，便可以将其作为主键，但我们不推荐将业务相关的字段作为主键，这主要时防止当业务需要变更时出现错误。

例如，对于Courses这张表，如将课程的课程号 cid 作为主键，尽管其是唯一的，但假如课程的课程号发生改变，或者其位数需要增加，那么在修改时我们还必须同时修改 Association 表中的内容，并且要确保没有重复的值或者确保在迁移数据时处理了重复值的情况。

除去将单一字段作为主键外，我们还可以使用联合主键，即用多个字段同时唯一确定某一条记录。

GUID

GUID（Globally Unique Identifier，全球唯一标识符）是一种由算法生成的128位的标识符，用来在分布式系统中唯一地标识对象或实体。

GUID 通常表示为32个十六进制字符，分为五组，格式为“8-4-4-4-12”，例如：

Text Only
1
123e4567-e89b-12d3-a456-426614174000
  • 全球唯一性：GUID 的生成算法确保了在全球范围内几乎不可能有两个相同的 GUID。
  • 无序性：GUID 没有包含任何顺序信息，它是无序的，并且通常不会被用户识别或使用，只作为程序内部的标识符。
  • 分布式生成：由于 GUID 是在本地生成的，它不依赖于中央协调，因此适用于分布式系统。

外键

与主键类似，外键也是一种约束性质，在 Association 数据表中，student_id 和 course_id 分别外链到 Students 和 Courses 两张数据表中的 id 属性。通过定义外键约束，关系型数据库可以保证无法插入无效的数据，即无法插入在 Students 表或 Courses 表中不存在的 id 号。

然而，外键约束会降低数据库的性能，所以在应用程序可以保证逻辑正确性的前提下，我们可以不设置外键约束。此时 student_id 和 course_id 只是两个普通的字段，但是其在使用逻辑上起到了外键的作用。

索引

在汉语词典中，我们可以按照拼音、笔画、偏旁部首等方式进行索引，通过这些方式进行索引通常可以获得更快的检索速度。

“索引”这一概念对于数据表也是这样。倘若我们为某一个字段建立了索引（为某种方式在词典中建立了目录），那么在通过这一字段进行检索时便通常可以获得更快的查询速度。

关系型数据库会自动为主键建立索引，由于主键会保证绝对唯一，因此采用主键索引往往是效率最高的。对于其他字段，当其重复项不多且常常依赖其进行查询时，我们也可以考虑为其创建索引，例如 Students 表中的 sid 和 Courses 表中的 cid。

然而，由于在插入、更新和删除记录时，需要同时修改索引，因此索引会减慢插入、更新和删除记录的速度。

应用

光说概念会有点枯燥，且很难理解，不如以一个例子进行介绍：

Note

其实笔者在对照其他资料写上面这组概念的时候也是头脑晕乎的，不过看完例子就很清晰了😄

INSERT

插入到 Students 表

SQL
1
2
3
4
INSERT INTO Students (sid, name, major) VALUES
('S1001', 'Alice', 'Computer Science'),
('S1002', 'Bob', 'Mathematics'),
('S1003', 'Charlie', 'Physics');

这个语句将三个学生记录插入到 Students 表中。注意 id 字段是自动递增的 (auto_increment)，不需要手动插入。

插入到 Courses 表

SQL
1
2
3
4
INSERT INTO Courses (cid, title, credit, time) VALUES
('C1001', 'Database Systems', 4, 'Mon 10-12'),
('C1002', 'Algorithms', 4, 'Wed 14-16'),
('C1003', 'Operating Systems', 3, 'Fri 9-11');

同理。

插入到 Association 表

SQL
1
2
3
4
INSERT INTO Association (student_id, course_id, grade) VALUES
(1, 1, 'A'),  -- Alice 参加 Database Systems，获得 A
(2, 2, 'B'),  -- Bob 参加 Algorithms，获得 B
(3, 1, 'A');  -- Charlie 参加 Database Systems，获得 A

这个语句将学生和课程之间的关联插入到 Association 表中。这里的 student_id 和 course_id 分别是 Students 和 Courses 表中的 id 字段值（从外部进行链接，此所谓外链）。

DELETE

删除 Students 表中一名学生

SQL
1
DELETE FROM Students WHERE sid = 'S1002';

这将删除学号为 S1002 的学生（Bob）。

级联删除

如果这个学生在 Association 表中有相关记录，应该先删除这些记录或设置外键约束为级联删除 (ON DELETE CASCADE)，否则会报错!!!

删除学生及其相关课程记录

假设你想要删除学生 Charlie 以及他在 Association 表中的所有关联，可以执行以下操作：

SQL
1
2
3
4
5
-- 先删除关联记录
DELETE FROM Association WHERE student_id = 3;

-- 然后删除学生记录
DELETE FROM Students WHERE id = 3;

理解：先删除指针关系和对应内容，再善后主体