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 脚本：

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

建立数据库

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

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

create database db_name;

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

查看数据库

MySQL 语法：

show databases;

PostgreSQL 标准 SQL：

SELECT datname FROM pg_database;

psql 元命令：

\l

进入数据库

MySQL 语法：

use db_name;

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

\c db_name

• db_name：数据库名

删除数据库

drop database db_name;

• db_name：数据库名

更改数据库字符集

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

alter database xxx
default character set utf8mb4;

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

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 专有）

建立表

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

• table_name：表名
• column_name：列名
• data_type：数据类型

表重命名

alter table old_name rename to new_name;

• old_name：旧表名
• new_name：新表名

查看数据库中有哪些表

MySQL 语法：

show tables;

PostgreSQL 标准 SQL：

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

psql 元命令：

\dt

展示表结构

MySQL 语法：

desc table_name;

PostgreSQL 标准 SQL：

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

psql 元命令：

\d table_name

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

select * from table_name;

SQL 语法：注释

单行注释：

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

多行注释：

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

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

MySQL 语法：

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

PostgreSQL 语法：

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

信息写入表

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

• col1：列名 1
• col2：列名 2

表新增列

alter table table_name
add column new_col data_type;

• new_col：新列名
• data_type：数据类型

表删除列

alter table 表名 drop column 列名;

修改列的数据类型

MySQL 语法：

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 语法：

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 父表名(父列名);

表添加主键

alter table table_name
add primary key (col_name);

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

表删除主键

MySQL 语法：

alter table table_name drop primary key;

PostgreSQL 语法：

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

唯一性约束

表添加唯一性约束

alter table table_name
add constraint constraint_name unique (col_name);

• constraint_name：唯一约束名
• col_name：列名

表删除唯一性约束

MySQL 语法：

alter table table_name
drop index constraint_name;

PostgreSQL 语法：

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

外键

添加外键

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

• fk_column_name：外键名
• fk_column：子表中的外键列名

删除外键

MySQL 语法：

alter table table_name
drop foreign key fk_name;

PostgreSQL 语法：

ALTER TABLE table_name DROP CONSTRAINT fk_name;

• table_name：子表名
• fk_name：外键名

外键的概念

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

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

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

customers 表

父表数据删除失败

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

父子表和外键示例

MySQL 语法：

-- 创建父表 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 语法：

-- 创建父表 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 更新父表主键时，子表外键列会自动更新。

修改表信息、删除行

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

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

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

下面给出几个示例：

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%。

DELETE FROM book_table
WHERE BookNumber < 200;

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

WHERE 子句

WHERE 子句是 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》。

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 子句（结果排序）。

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 降序。

SELECT student_name FROM student_table ORDER BY student_name DESC;

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

MySQL 语法：

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

PostgreSQL 语法：

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

连接查询

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

显式连接

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

隐式连接

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

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