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 语法:
PostgreSQL 标准 SQL:
psql 元命令:
进入数据库
MySQL 语法:
psql 元命令(切换数据库连接,无标准 SQL 等价语句):
db_name:数据库名
删除数据库
db_name:数据库名
更改数据库字符集
MySQL 语法(修改数据库的存储编码):
PostgreSQL 语法(仅修改连接时默认的客户端编码,不改变已有数据的存储编码):
注意: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 专有)
建立表
table_name:表名column_name:列名data_type:数据类型
表重命名
old_name:旧表名new_name:新表名
查看数据库中有哪些表
MySQL 语法:
PostgreSQL 标准 SQL:
psql 元命令:
展示表结构
MySQL 语法:
PostgreSQL 标准 SQL:
psql 元命令:
显示表全部信息(select * from 表名)
SQL 语法:注释
单行注释:
多行注释:
主键、唯一约束、非空约束与自动编号
MySQL 语法:
PostgreSQL 语法:
信息写入表
col1:列名 1col2:列名 2
表新增列
new_col:新列名data_type:数据类型
表删除列
修改列的数据类型
MySQL 语法:
PostgreSQL 语法:
表添加主键
col_name:作为主键的列名(通常为已存在的列)
表删除主键
MySQL 语法:
PostgreSQL 语法:
唯一性约束
表添加唯一性约束
constraint_name:唯一约束名col_name:列名
表删除唯一性约束
MySQL 语法:
PostgreSQL 语法:
外键
添加外键
fk_column_name:外键名fk_column:子表中的外键列名
删除外键
MySQL 语法:
PostgreSQL 语法:
table_name:子表名fk_name:外键名
外键的概念
外键用于建立两个表之间的关联关系,由一列或多列组成,指向另一个表的主键或唯一键,确保数据的一致性与完整性。数据库管理系统在外键约束上维护参照完整性,确保子表中的外键值必须存在于父表的主键或唯一键中,或者为 NULL(如果允许)。
子表:包含外键的表,子表中的外键列指向父表的主键或唯一键。例如下文示例中的 orders 表。
父表:外键引用的表,父表中的被引用列通常是主键(PRIMARY KEY)或唯一键(UNIQUE)。例如下文示例中的 customers 表。

父表数据删除失败
原因:子表中存在引用该数据的记录,数据库管理系统会阻止删除操作以维护参照完整性。 解决:使用 ON DELETE CASCADE 自动删除子表中的相关记录,或先手动删除子表记录,再删除父表记录。 ON DELETE CASCADE 是外键约束的一个选项,父表记录删除时,数据库自动删除子表中所有引用该记录的行。此机制在“自动维护父表和子表之间的参照完整性”一节中有详细说明。
父子表和外键示例
MySQL 语法:
PostgreSQL 语法:
创建父表 customers: 包含列: id:主键,用于唯一标识客户。 name:客户姓名。 email:唯一约束,用于防止重复的电子邮件地址。
创建子表 orders: order_id:主键,用于唯一标识订单。 order_date:记录订单日期。 customer_id:外键列,用于关联客户。 amount:订单金额。
设置外键:
FOREIGN KEY (customer_id) REFERENCES customers(id)表示子表的 customer_id 列引用父表的 id 列。ON DELETE CASCADE删除父表记录时,子表中引用该记录的行也会被删除。ON UPDATE CASCADE更新父表主键时,子表外键列会自动更新。
修改表信息、删除行
SET 子句用于指定需要修改的列。如需仅修改部分记录,应通过 WHERE 子句限定条件,以明确修改范围。
下面给出几个示例:
该示例将 book_table 表中 Publisher 值为“人民邮电出版社”的记录的 Price 值统一乘以 1.2,即在原价基础上增加 20%。
WHERE 子句
WHERE 子句是 SQL 语句中的重要组成部分,用于指定筛选条件,仅操作符合条件的记录。
如果
Price值小于 50,则执行相应操作。如果
author值为王阳或刘天洋,则执行相应操作。如果
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》。
在书籍管理数据库中,使用 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 子句(结果排序)。
该查询用于显示 student_table 中年龄等于该表最大年龄值的记录。内部子查询 (select max(age) from student_table) 首先执行,返回表中的最大年龄值;外部查询使用 WHERE 子句过滤出年龄等于该最大值的所有记录。
升序输出和降序输出
排序是查询的重要功能,通过 ORDER BY 子句可以指定查询结果的排序方式。
ASC 升序,DESC 降序。
MySQL 语法:LIMIT a, b 用于限制查询结果的数量,其中 a 表示起始位置(从 0 开始),b 表示返回的记录条数。PostgreSQL 语法:LIMIT b OFFSET a。
MySQL 语法:
PostgreSQL 语法:
连接查询
连接查询是关系型数据库的核心功能,可从多个相关联的表中同时获取数据。
显式连接
name 取自 stu_table score 取自 score_table
隐式连接
虽然隐式连接语法较为简洁,但实际开发中推荐使用显式连接,可提高可读性和可维护性。
最后更新于