> For the complete documentation index, see [llms.txt](https://book.bsdcn.org/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://book.bsdcn.org/di-33-zhang-shu-ju-ku-guan-li/di-33.1-jie-shu-ju-ku-gai-lun.md).

# 33.1 数据库概论

关系型数据库基于关系模型，以二维表格存储数据，通过表间关联关系组织、管理与查询数据。

关系型数据库管理系统（Relational Database Management System，RDBMS）通过结构化查询语言（Structured Query Language，SQL）提供数据定义、数据操作和数据控制功能，通过事务机制保证数据的一致性与完整性。

本节介绍关系型数据库的基本操作与 SQL 语法。本节中的 SQL 示例均基于 MySQL 语法和 PostgreSQL 语法。每个非标准语法后均附有 PostgreSQL 的对应语法。

PostgreSQL 的默认命令行客户端 psql 提供了一系列以反斜杠开头的元命令（如 `\l`、`\c`、`\dt`、`\d` 等），用于快速查看和操作数据库对象。这些元命令是 psql 客户端的快捷方式，并非 SQL 语句，仅在 psql 交互式界面中可用；在其他客户端（如 pgAdmin、编程语言数据库驱动等）中需使用对应的标准 SQL 查询替代。下文各处出现的 psql 元命令在可能的情况下均附有等效的标准 SQL 语句。

## 在 UNIX 系统中连接数据库并执行 SQL 脚本

在 UNIX 或类 UNIX 系统中，可通过命令行连接数据库并执行 SQL 脚本。

以下命令连接 MySQL 数据库并执行 SQL 脚本：

```sh
# mysql -u root -p          # 以 root 用户登录 MySQL，-u 指定用户名，-p 表示需要密码验证
mysql> source FileName.sql  # 在 MySQL 交互式界面中执行指定的 SQL 文件，source 命令读取文件内容并逐条执行 SQL 语句
```

## 建立数据库

在关系型数据库中，数据库是存储相关数据表的容器，每个数据库具有独立的权限控制机制与存储空间。

可通过 SQL 语句创建新的数据库：

```sql
create database db_name;
```

* `db_name`：数据库名，用于标识数据库。数据库名在数据库实例中必须唯一。

## 查看数据库

MySQL 语法：

```sql
show databases;
```

PostgreSQL 标准 SQL：

```sql
SELECT datname FROM pg_database;
```

psql 元命令：

```sql
\l
```

## 进入数据库

MySQL 语法：

```sql
use db_name;
```

psql 元命令（切换数据库连接，无标准 SQL 等价语句）：

```sql
\c db_name
```

* `db_name`：数据库名

## 删除数据库

```sql
drop database db_name;
```

* `db_name`：数据库名

## 更改数据库字符集

MySQL 语法（修改数据库的存储编码）：

```sql
alter database xxx
default character set utf8mb4;
```

PostgreSQL 语法（仅修改连接时默认的客户端编码，不改变已有数据的存储编码）：

```sql
ALTER DATABASE xxx SET client_encoding TO 'UTF8';
```

注意：MySQL 的 `DEFAULT CHARACTER SET` 会更改数据库的存储编码，影响后续创建的表和列的默认编码；PostgreSQL 的 `SET client_encoding` 仅设置该数据库的客户端连接编码默认值（GUC 参数），不会改变数据库的实际存储编码。PostgreSQL 数据库的存储编码在创建时由 `CREATE DATABASE ... ENCODING 'UTF8'` 指定，创建后不可更改。

## SQL 数据类型

SQL 数据类型定义了表中列可以存储的数据种类和格式。以下数据类型为 MySQL 语法，部分类型（如 `tinyint`、`mediumint`、`unsigned`）为 MySQL 专有。

PostgreSQL 语法说明：PostgreSQL 不支持 `tinyint` 和 `mediumint`，对应使用 `smallint`（2B）和 `integer`（4B）；PostgreSQL 也不支持 `unsigned` 修饰符，需使用 `CHECK` 约束替代，例如 `CHECK (col >= 0)`。

* 整数类型

| 类型          | 大小 | 备注       |
| ----------- | -- | -------- |
| `tinyint`   | 1B | MySQL 专有 |
| `smallint`  | 2B |          |
| `mediumint` | 3B | MySQL 专有 |
| `int`       | 4B |          |
| `bigint`    | 8B |          |

* 浮点与定点类型

| 类型              | 大小                |
| --------------- | ----------------- |
| `float`         | 4B                |
| `double`        | 8B                |
| `decimal(M, D)` | M 为整体位数，D 为小数点后位数 |

* 字符类型

| 类型        | 说明            |
| --------- | ------------- |
| `char(N)` | 定长字符类型，N 为字符数 |

* 类型修饰符

| 关键字        | 说明                      |
| ---------- | ----------------------- |
| `unsigned` | 将整数类型设置为无符号类型（MySQL 专有） |

## 建立表

```sql
create table table_name (
  column_name data_type,
  column_name data_type,
  column_name data_type
);
```

* `table_name`：表名
* `column_name`：列名
* `data_type`：数据类型

## 表重命名

```sql
alter table old_name rename to new_name;
```

* `old_name`：旧表名
* `new_name`：新表名

## 查看数据库中有哪些表

MySQL 语法：

```sql
show tables;
```

PostgreSQL 标准 SQL：

```sql
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
```

psql 元命令：

```sql
\dt
```

## 展示表结构

MySQL 语法：

```sql
desc table_name;
```

PostgreSQL 标准 SQL：

```sql
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'table_name';
```

psql 元命令：

```sql
\d table_name
```

## 显示表全部信息（select \* from 表名）

```sql
select * from table_name;
```

## SQL 语法：注释

单行注释：

```sql
-- 这是单行注释：注释不会被执行，用于说明 SQL 语句的用途或注意事项
```

多行注释：

```sql
/*
   这里是多行注释
   可以跨越多行，用于描述复杂的业务逻辑或临时禁用代码块
*/
```

## 主键、唯一约束、非空约束与自动编号

MySQL 语法：

```sql
create table 表名
(
  -- 主键
  列名 数据类型 primary key,
  -- 唯一
  列名 char(20) unique,
  -- 不允许为空
  列名 数据类型 not null,
  -- 自动编号
  列名 数据类型 auto_increment
);
```

PostgreSQL 语法：

```sql
CREATE TABLE 表名
(
  -- 主键
  列名 数据类型 PRIMARY KEY,
  -- 唯一
  列名 CHAR(20) UNIQUE,
  -- 不允许为空
  列名 数据类型 NOT NULL,
  -- 自动编号（推荐使用 GENERATED ... AS IDENTITY，SERIAL 为历史向后兼容语法）
  -- 列名 INT GENERATED BY DEFAULT AS IDENTITY
  -- 或（SERIAL 本身是数据类型，等价于 INT + 自增序列，但不符合 SQL 标准）
  列名 SERIAL
);
```

## 信息写入表

```sql
insert into table_name (col1, col2) values (123, '李明');
```

* `col1`：列名 1
* `col2`：列名 2

## 表新增列

```sql
alter table table_name
add column new_col data_type;
```

* `new_col`：新列名
* `data_type`：数据类型

## 表删除列

```sql
alter table 表名 drop column 列名;
```

## 修改列的数据类型

MySQL 语法：

```sql
ALTER TABLE 表名
MODIFY COLUMN 列名 新数据类型; -- MySQL 专有语法

-- 例如（重设数据类型并不允许为空、设置默认值）
ALTER TABLE book_table
MODIFY COLUMN BookName CHAR(100) NOT NULL DEFAULT '《书名》';

-- 如果修改的列是外键，必须先删除外键约束，随后修改数据类型，最后重新添加外键约束
alter table 子表 drop FOREIGN KEY 外键名;
alter table 子表 modify column 列名 新数据类型;
alter table 子表 add constraint 外键名 FOREIGN KEY (列名) REFERENCES 父表名(父列名);
```

PostgreSQL 语法：

```sql
ALTER TABLE 表名 ALTER COLUMN 列名 TYPE 新数据类型;
-- 例如（重设数据类型并不允许为空、设置默认值）
ALTER TABLE book_table ALTER COLUMN BookName TYPE CHAR(100);
ALTER TABLE book_table ALTER COLUMN BookName SET NOT NULL;
ALTER TABLE book_table ALTER COLUMN BookName SET DEFAULT '《书名》';

-- 如果修改的列是外键，必须先删除外键约束，随后修改数据类型，最后重新添加外键约束
ALTER TABLE 子表 DROP CONSTRAINT 外键名;
ALTER TABLE 子表 ALTER COLUMN 列名 TYPE 新数据类型;
ALTER TABLE 子表 ADD CONSTRAINT 外键名 FOREIGN KEY (列名) REFERENCES 父表名(父列名);
```

## 表添加主键

```sql
alter table table_name
add primary key (col_name);
```

* `col_name`：作为主键的列名（通常为已存在的列）

## 表删除主键

MySQL 语法：

```sql
alter table table_name drop primary key;
```

PostgreSQL 语法：

```sql
ALTER TABLE table_name DROP CONSTRAINT 约束名;
-- 需指定约束名，可通过 \d table_name 查看
```

## 唯一性约束

### 表添加唯一性约束

```sql
alter table table_name
add constraint constraint_name unique (col_name);
```

* `constraint_name`：唯一约束名
* `col_name`：列名

### 表删除唯一性约束

MySQL 语法：

```sql
alter table table_name
drop index constraint_name;
```

PostgreSQL 语法：

```sql
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
```

## 外键

### 添加外键

```sql
ALTER TABLE table_name
ADD CONSTRAINT fk_column_name
FOREIGN KEY (fk_column)
REFERENCES tstu(sid);
```

* `fk_column_name`：外键名
* `fk_column`：子表中的外键列名

### 删除外键

MySQL 语法：

```sql
alter table table_name
drop foreign key fk_name;
```

PostgreSQL 语法：

```sql
ALTER TABLE table_name DROP CONSTRAINT fk_name;
```

* `table_name`：子表名
* `fk_name`：外键名

### 外键的概念

外键用于建立两个表之间的关联关系，由一列或多列组成，指向另一个表的主键或唯一键，确保数据的一致性与完整性。数据库管理系统在外键约束上维护参照完整性，确保子表中的外键值必须存在于父表的主键或唯一键中，或者为 NULL（如果允许）。

子表：包含外键的表，子表中的外键列指向父表的主键或唯一键。例如下文示例中的 orders 表。

父表：外键引用的表，父表中的被引用列通常是主键（PRIMARY KEY）或唯一键（UNIQUE）。例如下文示例中的 customers 表。

![customers 表](/files/jlVOCe1pzPa7s9zjKc99)

### 父表数据删除失败

原因：子表中存在引用该数据的记录，数据库管理系统会阻止删除操作以维护参照完整性。 解决：使用 `ON DELETE CASCADE` 自动删除子表中的相关记录，或先手动删除子表记录，再删除父表记录。 `ON DELETE CASCADE` 是外键约束的一个选项，父表记录删除时，数据库自动删除子表中所有引用该记录的行。此机制在“自动维护父表和子表之间的参照完整性”一节中有详细说明。

### 父子表和外键示例

MySQL 语法：

```sql
-- 创建父表 customers，包含以下列：
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 主键，自动编号（AUTO_INCREMENT 为 MySQL 专有）
    name VARCHAR(100) NOT NULL,        -- 客户名
    email VARCHAR(150) UNIQUE          -- 唯一的电子邮件
);

-- 创建子表 orders
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY, -- 主键，订单编号（AUTO_INCREMENT 为 MySQL 专有）
    order_date DATE NOT NULL,                -- 订单日期
    customer_id INT,                         -- 外键列，关联客户 ID
    amount DECIMAL(10,2),                    -- 订单金额
    FOREIGN KEY (customer_id) REFERENCES customers(id) -- 定义外键
    ON DELETE CASCADE -- 当父表记录被删除时，级联删除子表相关记录
    ON UPDATE CASCADE -- 当父表主键更新时，子表外键自动更新
);
```

PostgreSQL 语法：

```sql
-- 创建父表 customers，使用 GENERATED BY DEFAULT AS IDENTITY（推荐，符合 SQL 标准）
CREATE TABLE customers (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 主键，自动编号
    name VARCHAR(100) NOT NULL,                          -- 客户名
    email VARCHAR(150) UNIQUE                            -- 唯一的电子邮件
);

CREATE TABLE orders (
    order_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 主键，订单编号
    order_date DATE NOT NULL,                                  -- 订单日期
    customer_id INT,                                           -- 外键列，关联客户 ID
    amount DECIMAL(10,2),                                      -- 订单金额
    FOREIGN KEY (customer_id) REFERENCES customers(id)         -- 定义外键
    ON DELETE CASCADE -- 当父表记录被删除时，级联删除子表相关记录
    ON UPDATE CASCADE -- 当父表主键更新时，子表外键自动更新
);

-- 或使用 SERIAL（历史向后兼容语法，SERIAL 本身是数据类型，等价于 INT + 自增序列，但不符合 SQL 标准）
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,                 -- 主键，自动编号
    name VARCHAR(100) NOT NULL,            -- 客户名
    email VARCHAR(150) UNIQUE              -- 唯一的电子邮件
);

-- 创建子表 orders
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,           -- 主键，订单编号
    order_date DATE NOT NULL,              -- 订单日期
    customer_id INT,                       -- 外键列，关联客户 ID
    amount DECIMAL(10,2),                  -- 订单金额
    FOREIGN KEY (customer_id) REFERENCES customers(id) -- 定义外键
    ON DELETE CASCADE -- 当父表记录被删除时，级联删除子表相关记录
    ON UPDATE CASCADE -- 当父表主键更新时，子表外键自动更新
);
```

1. 创建父表 customers： 包含列： id：主键，用于唯一标识客户。 name：客户姓名。 email：唯一约束，用于防止重复的电子邮件地址。
2. 创建子表 orders： order\_id：主键，用于唯一标识订单。 order\_date：记录订单日期。 customer\_id：外键列，用于关联客户。 amount：订单金额。
3. 设置外键： `FOREIGN KEY (customer_id) REFERENCES customers(id)` 表示子表的 customer\_id 列引用父表的 id 列。 `ON DELETE CASCADE` 删除父表记录时，子表中引用该记录的行也会被删除。 `ON UPDATE CASCADE` 更新父表主键时，子表外键列会自动更新。

## 修改表信息、删除行

```sql
-- 修改表信息
update 表名
set 列名 = 新值
where 条件;
```

`SET` 子句用于指定需要修改的列。如需仅修改部分记录，应通过 `WHERE` 子句限定条件，以明确修改范围。

```sql
-- 删除行
DELETE FROM book_table
WHERE BookNumber < 200;
```

下面给出几个示例：

```sql
update book_table
set Price = Price * 1.2
where Publisher = "人民邮电出版社"; -- MySQL 默认允许使用双引号包裹字符串值，标准 SQL 及 PostgreSQL 中双引号为标识符引用，字符串值必须使用单引号
-- 注意：若 MySQL 开启了 ANSI_QUOTES SQL 模式（SET sql_mode = 'ANSI_QUOTES'），则双引号行为与标准 SQL 一致，仅用于引用标识符，不能再用于包裹字符串值
```

该示例将 `book_table` 表中 `Publisher` 值为“人民邮电出版社”的记录的 `Price` 值统一乘以 `1.2`，即在原价基础上增加 20%。

```sql
DELETE FROM book_table
WHERE BookNumber < 200;

DELETE FROM book_table
WHERE author IN ('王阳', '刘天洋');
```

### WHERE 子句

WHERE 子句是 SQL 语句中的重要组成部分，用于指定筛选条件，仅操作符合条件的记录。

```sql
...(update 或 delete)
where Price < 50;
...(update 或 delete)
WHERE author = '王阳' or author = '刘天洋';
...(update 或 delete)
WHERE name in ('张三', '李四');
```

1. 如果 `Price` 值小于 50，则执行相应操作。
2. 如果 `author` 值为王阳或刘天洋，则执行相应操作。
3. 如果 `name` 值为张三或李四，则执行相应操作。

WHERE 子句指定条件表达式，该表达式计算并返回布尔值。

### SQL 运算符

运算符是 SQL 语句中用于数据计算和比较的符号，分为算术运算符、比较运算符、逻辑运算符和特殊运算符等几类。

* 算术运算符

| 运算符 | 说明    |
| --- | ----- |
| `+` | 加法运算符 |
| `-` | 减法运算符 |
| `*` | 乘法运算符 |
| `/` | 除法运算符 |
| `%` | 取模运算符 |

* 比较运算符

| 运算符   | 说明                                           |
| ----- | -------------------------------------------- |
| `=`   | 等于（在 SQL 中用于比较，而非赋值）                         |
| `!=`  | 不等于                                          |
| `<`   | 小于                                           |
| `>`   | 大于                                           |
| `<=`  | 小于等于                                         |
| `>=`  | 大于等于                                         |
| `<=>` | NULL 安全等于，NULL <=> NULL，返回结果是 TRUE（MySQL 专有） |

PostgreSQL 语法说明：PostgreSQL 不支持 `<=>` 运算符，判断 NULL 相等需使用 `IS NOT DISTINCT FROM`。

* 逻辑运算符

| 运算符   | 说明                           |
| ----- | ---------------------------- |
| `and` | 两条件都为真时才返回真，否则为假             |
| `or`  | 两条件有一个为真时返回真，全为假时才返回假        |
| `not` | 反转真假                         |
| `xor` | 仅一个条件为真时才返回真，否则返回假（MySQL 专有） |

PostgreSQL 语法说明：PostgreSQL 不支持 `xor` 运算符，需使用 `(a AND NOT b) OR (NOT a AND b)` 替代。

* 特殊运算符

| 运算符       | 说明                                                                            |
| --------- | ----------------------------------------------------------------------------- |
| `in`      | “值在列表中”运算符，用于筛选符合条件的记录，并返回布尔结果，供 update 或 delete 语句使用。例如 `5 in (1, 3, 5)` 返回真 |
| `between` | 范围匹配：例如 `5 between 1 and 10` 返回真                                              |
| `like`    | 模式匹配：`'abc' like 'a%'` 返回真。百分号（%）匹配零个或多个任意字符，下划线（\_）匹配单个任意字符                  |
| `IS NULL` | “判断表项是否为 NULL”：`NULL IS NULL` 返回 TRUE                                         |

示例：查看书名条目是否存在 `《xxx 设计 yyy》`。

```sql
select * from book_table
where BookName like '%设计%';

select * from book_table
where BookName like '_____';
-- 五个下划线代表五个字，返回长度为 5 个字的书名。
```

在书籍管理数据库中，使用 LIKE '%设计%' 会返回类似《MySQL 数据库设计》的书名。

## 自动维护父表和子表之间的参照完整性

参照完整性确保相关表之间数据的一致性。父表数据变化时，子表中的相关数据也需相应调整。

| 选项              | 说明                                                                                                                                           |
| --------------- | -------------------------------------------------------------------------------------------------------------------------------------------- |
| **CASCADE**     | 级联操作：父表记录删除/更新时，子表中相关记录也一并删除/更新                                                                                                              |
| **SET NULL**    | 将子表中的外键列设置为 NULL（要求外键列允许 NULL）                                                                                                               |
| **RESTRICT**    | 拒绝操作：不允许删除/更新父表中被引用的记录（立即返回错误）                                                                                                               |
| **NO ACTION**   | 类似 RESTRICT。在 MySQL 中，二者完全等价，均立即拒绝违反参照完整性的操作。在 SQL 标准中，RESTRICT 不可延迟；若约束为 DEFERRABLE，则 NO ACTION 可延迟到事务结束时检查完整性。PostgreSQL 遵循 SQL 标准，二者行为有区别 |
| **SET DEFAULT** | 设置为默认值，MySQL 不支持                                                                                                                             |

## SELECT 语句

SELECT 语句是 SQL 中最常用的查询语句，用于从数据库中检索数据。SELECT 关键字后指定要查询的列名，使用 `*` 表示选择所有列。SELECT 语句的基本语法包括 SELECT 子句（指定要返回的列）、FROM 子句（指定数据源表）、WHERE 子句（指定过滤条件）、GROUP BY 子句（分组聚合）、HAVING 子句（分组过滤）和 ORDER BY 子句（结果排序）。

```sql
select * from student_table
where age = (select max(age) from student_table);
```

该查询用于显示 student\_table 中年龄等于该表最大年龄值的记录。内部子查询 `(select max(age) from student_table)` 首先执行，返回表中的最大年龄值；外部查询使用 WHERE 子句过滤出年龄等于该最大值的所有记录。

### 升序输出和降序输出

排序是查询的重要功能，通过 ORDER BY 子句可以指定查询结果的排序方式。

ASC 升序，DESC 降序。

```sql
SELECT student_name FROM student_table ORDER BY student_name DESC;
```

MySQL 语法：`LIMIT a, b` 用于限制查询结果的数量，其中 a 表示起始位置（从 0 开始），b 表示返回的记录条数。PostgreSQL 语法：`LIMIT b OFFSET a`。

MySQL 语法：

```sql
SELECT * from `学生表` -- 反引号为 MySQL 专有的标识符引用方式，标准 SQL 使用双引号
ORDER BY `学号` desc
LIMIT 0,3;
```

PostgreSQL 语法：

```sql
SELECT * FROM "学生表"
ORDER BY "学号" DESC
LIMIT 3 OFFSET 0;
```

### 连接查询

连接查询是关系型数据库的核心功能，可从多个相关联的表中同时获取数据。

#### 显式连接

```sql
SELECT name, score --name 来自学生表，score 来自分数表
FROM stu_table -- 表一
JOIN score_table -- 表二
ON stu_table.stu_id = score_table.s_id; -- 连接条件
WHERE name = 'Jack' -- 筛选条件，后面还可追加 AND 或 OR。
```

`name` 取自 `stu_table` `score` 取自 `score_table`

#### 隐式连接

```sql
SELECT name, score
FROM stu_table, score_table
WHERE stu_table.stu_id = score_table.s_id
AND name = 'Jack';
```

虽然隐式连接语法较为简洁，但实际开发中推荐使用显式连接，可提高可读性和可维护性。


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://book.bsdcn.org/di-33-zhang-shu-ju-ku-guan-li/di-33.1-jie-shu-ju-ku-gai-lun.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.