关系型数据库基础

关系型数据库是一种基于关系模型的数据库，它使用表格来存储数据，通过表与表之间的关联来实现数据的管理和查询。从数据管理的理论框架来看，关系型数据库建立在集合论和一阶谓词逻辑的基础之上，为数据存储提供了结构化的范式。本部分将介绍关系型数据库的基本操作和 SQL 语法。

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

在 UNIX 或类 UNIX 系统中，我们可以通过命令行连接数据库并执行 SQL 脚本。以下是一些常用的基础操作命令。

# mysql -u root -p          # 以 root 用户登录 MySQL，系统会提示输入密码
# source FileName.sql       # 在 MySQL 交互式界面中执行指定的 SQL 文件

建立数据库

在关系型数据库中，创建数据库是使用数据库的第一步。我们可以通过 SQL 语句来创建新的数据库。

create database db_name;

• db_name：数据库名

数据库名称区分大小写，长度最长为 64 个字符，别名长度最长为 256 个字符，不能使用数据库关键字。

数据库重命名

rename database old_name to new_name;

• old_name：旧数据库名

• new_name：新数据库名

查看数据库

show databases;

进入数据库

use db_name;

• db_name：数据库名

删除数据库

drop database db_name;

• db_name：数据库名

更改数据库字符集

alter database xxx
default character set UTF-8;

SQL 数据类型

• tinyint (1B), smallint (2B), mediumint (3B), int (4B), bigint (8B)

• float (4B), double (8B), decimal(整体位数, 小数点后位数)

• char 是字符类型，想存字符串用 char(字符数)

• unsigned + 数据类型 可以设置为无符号的数据类型

建立表

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：新表名

查看数据库有哪些表

show tables;

展示表结构

desc table_name;

显示表全部信息（select 后面细说）

select * from table_name;

SQL 语法：注释

-- 注释不会被执行

在脚本中添加注释是一种良好习惯，可以避免在一段时间后难以理解代码的用途。

思考题

如果代码注释仅仅起到助记作用，那么任何注释都不属于代码本身。除非已经失去了再次编写相同代码的能力，否则也没有必要刻意去理解之前编写的代码。

请读者思考代码注释的真正意义。

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

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

信息写入表

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 列名;

表修改列数据类型

alter table table_name modify column col_name new_data_type;

• new_data_type：新数据类型

表添加主键

alter table table_name
add primary key (col_name);

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

表删除主键

alter table table_name drop primary key;

唯一性约束

表添加唯一性约束

alter table table_name
add constraint constraint_name unique (col_name);

• constraint_name：唯一约束名

• col_name：列名

表删除唯一性约束

alter table table_name
drop index constraint_name;

外键

表添加外键

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

• fk_column_name：外键名

• fk_column：子表中的外键列名

表删除外键

alter table table_name
drop foreign key fk_name;

• table_name：子表名

• fk_name：外键名

外键的概念

外键是用于建立和强制两个表之间链接的一列或多列。它指向另一个表的主键或唯一键，确保数据的一致性和完整性。

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

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

删除父表数据时失败

• 原因：子表中存在引用该数据的记录。

• 解决：使用 ON DELETE CASCADE 或先删除子表中的记录。

• ON DELETE CASCADE 涉及较多内容，此处暂不展开，详见后文“自动维护父表和子表之间的参照完整性”一节。

父子表和外键示例

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

-- 创建子表 orders
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT 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 更新父表主键时，子表外键列会自动更新。

修改列的数据类型

ALTER TABLE 表名
MODIFY COLUMN 列名 新数据类型;

-- 例如（重设数据类型并不许为空、设置默认值）
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 父表名(父列名);

修改表信息、删除行

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

注：SET 子句用于指定需要修改的列。如果只希望修改部分记录，需要通过 WHERE 子句限定条件，以明确修改的范围。

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

下面给几个示例：

update book_table
set Price = Price * 1.2
where Publisher = "人民邮电出版社";

该示例将表 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

逻辑运算符

• and 两条件都为真才返回真，否则为假

• or 两条件有一个为真就是真，全是假才为假

• not 反转真假

• xor 仅一个条件为真才返回真，否则返回假

特殊运算符

• in：“值在列表中”运算符，用于筛选符合条件的记录，并返回布尔结果，供 update 或 delete 语句使用。例如 5 in (1, 3, 5) 返回真。

• between 范围匹配：例如 5 between 1 and 10 返回真。

• like 模式匹配：'abc' like 'a%' 返回真。百分号在这里类似 sh 的通配符。

• IS NULL “判断表项是否为 NULL”：NULL IS NULL 返回 TRUE。 例子：查看书名条目是否存在 《xxx 设计 yyy》。

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

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

在一个书籍管理库中会返回类似《MySQL 数据库设计》的书名。

通过上述说明，可以更好地理解 WHERE 子句中条件表达式的含义。

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

参照完整性是数据库的重要特性，它确保了相关表之间数据的一致性。当父表中的数据发生变化时，子表中的相关数据也需要相应地调整。

• CASCADE 级联操作：父表记录删除/更新时，子表中相关记录也被删除/更新。

• SET NULL 将子表中的外键列设置为 NULL（要求外键列允许 NULL）。

• RESTRICT 拒绝操作：不允许删除/更新父表中被引用的记录（立即返回错误）。

• NO ACTION 类似 RESTRICT，但延迟到事务结束时才检查完整性。

• SET DEFAULT 设置为默认值，MySQL 不支持。

SELECT 语句

SELECT 语句是 SQL 中最常用的查询语句，用于从数据库中检索数据。

SELECT 关键字后指定要查询的列名，使用 * 表示选择所有列。

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

用于显示 student_table 中年龄等于该表最大年龄值的记录。

升序输出和降序输出

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

ASC 升序，DESC 降序。

SELECT student_name FROM student_table ORDER BY student_name DESC;

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

SELECT * from `学生表`
ORDER BY `学号` desc
LIMIT 0,3;

连接查询

连接查询是关系型数据库的核心功能之一，它允许我们从多个相关联的表中同时获取数据。

显式连接

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';

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