03-库和表 – Skyshin34的博客

本文最后更新于91 天前，其中的信息可能已经过时，如有错误请留言

基础术语

库
可以指数据库管理系统，即DBMS。
也可以是使用DBMS构建数据的容器，其中存储有若干表、视图等数据库对象

表 Table
最基础的数据库对象，用二维结构（行和列）用来存储数据

行列
行也叫记录Record
通常每行数据是唯一的，两行记录不一样
列也叫字段Field，不能有名字相同的列

主键 Primary Key
唯一标识一条记录的值，用来体现实体完整性

外键 Foreign Key

在数据库应用中，往往不是把一个或几个事物的数据统统放到一个表中
为了有效管理数据，通常建立若干相对独立的表用于存储数据，外键用于保持表之间的联系

实体 Entity
是指某类事物的集合

数据库理论的提出 Codd首先提议把关系代数作为数据库查询语言的基础，他把数学和计算机科学在信检索领域做了创造性的结合，使得计算机对数据的查询检索有了严格的数学保证，正是由于他的开创性工作直接推动了关系数据库的诞生

库有关的基本操作

3.1 创建库

Navigator 区域：RMC选择create schema
控制面板：

//注意库名不能加引号
CREATE DATABASE 数据库名;
//或使用
create SCHEMA 数据库名;

3.2 修改库的属性

不提供修改库名称的操作，只能修改库的字符集，字符序，数据库引擎
Character Set字符集：决定了能存哪些文字 / 符号，确保数据能正确存储（比如用utf8mb4才能存 emoji，用gbk存会乱码）
Collection 字符序：决定了查询时 “A和a是否视为相同”“à和a是否视为相同”，以及排序结果的顺序
数据库引擎（Storage Engine）：决定了数据库支持的功能（比如事务、外键）、性能表现（读写速度）、数据安全

在库名的右侧隐藏菜单中选择扳手 – 进入库属性

3.3 删除库

RMC库名 – 选择Drop Scheme

直接删除数据库（若数据库不存在，会报错）
DROP DATABASE 数据库名;
安全删除：判断数据库存在则删除，不存在则不报错
DROP DATABASE IF EXISTS 数据库名;

数据类型

数据类型本质是一种存储规则，便于计算机存储、查找数据

字符串类

类型	长度特性	长度范围	存储规则	典型适用场景
CHAR	定长显式声明长度	0-255 字节	空格填充 / 读取时去末尾空格	定长字符串手机号、身份证号
VARCHAR	变长显式	0-65535 字节	记录实际长度（占 1-2 字节）	变长字符串姓名、短评
TINYTEXT	变长无需指定长度	0-255 字节	记录长度（占 2 字节）	短文本字符串用户备注
TEXT	变长无需指定长度	0-65535 字节	记录长度（占 2 字节）	长文本数据文章正文

CHAR：
定长，即不管实际存储的字符串长度是多少，都会占用声明时指定的长度
若实际字符串长度不足声明长度，会用空格填充；读取时会自动去掉末尾空格。
适用场景：存储长度固定的字符串，如手机号、身份证号、性别（“男”“女”）等。

VARCHAR：
实际存储长度由内容决定，不会填充空格；会额外占用 1-2 字节来记录字符串的实际长度。

数字类

时间日期类

集合类

表操作

3.4 创建表，修改表的属性

创建一个库CREATE DATABASE XXX；设置这个库为当前库USE XXX；下面构建一个名为tb_student的表

CREATE TABLE 表名 (
    列名1 数据类型 [约束条件],
    列名2 数据类型 [约束条件],

    [表级约束条件] -- 比如主键、外键等
);

CREATE TABLE ‘tb’.‘tb_student’ 
(
  stu_num CHAR(11) NOT NULL,
  name VARCHAR(20) NULL,
  intro TINYTEXT NULL,

  PRIMARY KEY (stu_num)
);

tb.tb_student：用库名.表名的格式指定表的归属（避免同名表冲突）。
NULL（可省略，默认就是 NULL）表示该字段允许存储空值，即可以不赋值
NOT NULL表示该字段不允许存储空值，必须为其赋值
通过 PRIMARY KEY ('stu_num') 将 stu_num 字段设置为主键。主键具有唯一性和非空性，保证了表中每条记录都能被唯一标识

列名（Column Name）：表中每一列的名称
主键（PK – Primary Key）：勾选表示该列是表的主键。唯一性和非空性
非空（NN – Not Null）：勾选表示该列不允许为NULL值，即插入或修改数据时，该列必须有值
唯一（UQ – Unique）：勾选表示该列的值在表中必须唯一，但与主键不同的是，唯一约束的列可以为NULL（若列没设置NN）。用于保证列中数据的唯一性，比如若有 “身份证号” 列，可设为唯一

3.5 修改表的结构 – 删除列，添加列，修改列名

ALTER只负责修改 “表的骨架”（列、约束、表名等结构），而修改 “表中的内容”（比如更新学生姓名、删除某条学生记录），用的是其他 SQL 语句，和ALTER无关：

修改列：ALTER TABLE 表名 CHANGE COLUMN 原列名 新列名 类型 约束;
新增列：ALTER TABLE 表名 ADD COLUMN 新列名 类型 约束;
删除列：ALTER TABLE 表名 DROP COLUMN 列名;
修改表名：ALTER TABLE 原表名 RENAME TO 新表名;（MySQL 中）
修改列名：ALTER TABLE 表名 CHANGE COLUMN 原列名 新列名 类型 约束;
新增主键：ALTER TABLE 表名 ADD PRIMARY KEY (列名)

3.6 删除表

Drop Table 表名

3.7 批量插入数据

INSERT INTO 表名 (列1, 列2, 列3, …, 列n)
VALUES
(值1-1, 值1-2, 值1-3, …, 值1-n), — 第一条记录
(值2-1, 值2-2, 值2-3, …, 值2-n), — 第二条记录
…
(值m-1, 值m-2, 值m-3, …, 值m-n); — 第m条记录（最后一条无需逗号）

INSERT INTO tb_student VALUES
('16100101', '马诗' ,'男'),
('17130203', '马诗' ,'女');

作业代码

//建立数据库MyLib
CREATE DATABASE MyLib;
//创建student表
//stuID设为INT 类型（主键，自增更合理，可选）；
//name设为VARCHAR(50)；
//gender设为CHAR(2)，默认值设为'男'（或'女'，根据需求选择）；
//school设为VARCHAR(100)。
USE MyLib;
CREATE TABLE student (
    stuID INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    gender CHAR(2) DEFAULT '男',
    school VARCHAR(100)
);
创建book表
bookID设为INT 类型（主键，自增）；
title设为VARCHAR(100)；
author设为VARCHAR(50)；
publish设为VARCHAR(100)；
price设为DECIMAL(10,2)，默认值设为0。
CREATE TABLE book (
    bookID INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(50),
    publish VARCHAR(100),
    price DECIMAL(10,2) DEFAULT 0
);
创建record表
stuID和bookID作为联合主键，同时作为外键关联student和book表；
borrowdate和returndate设为DATE 类型。
CREATE TABLE record (
    stuID INT,
    bookID INT,
    borrowdate DATE,
    returndate DATE,
    PRIMARY KEY (stuID, bookID),
    FOREIGN KEY (stuID) REFERENCES student(stuID),
    FOREIGN KEY (bookID) REFERENCES book(bookID)
);
向student表插入 3 条记录
INSERT INTO student (name, gender, school) VALUES 
('张三', '男', '第一中学'),
('李四', '女', '第二中学'),
('王五', '男', '第一中学');

INSERT INTO book (title, author, publish, price) VALUES 
('MySQL从入门到精通', '张三', '机械工业出版社', 59.9),
('Python编程', '李四', '人民邮电出版社', 69.9),
('数据结构', '王五', '清华大学出版社', 45.0);

INSERT INTO record (stuID, bookID, borrowdate, returndate) VALUES 
(1, 1, '2025-10-01', '2025-10-15'),
(2, 2, '2025-10-02', '2025-10-20'),
(3, 3, '2025-10-03', '2025-10-25');

3.8 修改表中数据

UPDATE 表名
SET 列名1 = 新值1, 列名2 = 新值2, ..., 列名n = 新值n
[WHERE 条件]; -- 关键：指定要修改的记录，省略则修改全表所有记录

在WorkBench中，如果没有给表指定主键出于安全考虑会报错（因为通过WHERE定位的stu_man可能不唯一），但如果讲其复制到命令行中可以执行，证明命令行的操作权限更大

3.9 删除表中数据

DELETE FROM 表名
[WHERE 条件]; -- 关键：筛选要删除的记录，省略则删除全表所有记录

删除姓名为“博文一”的记录
Delete From XX表名 WHERE name = ‘ 博文一’；

补充：查看表结构

语句	特点	适用场景
`DESC / DESCRIBE 表名`	简洁、快速，核心信息一目了然	日常快速查看表结构
`SHOW COLUMNS FROM 表名`	与 DESC 功能一致，语法不同	习惯这种写法的场景
`SHOW CREATE TABLE 表名`	详细、完整，含约束 / 存储引擎等所有信息	需复制建表语句、排查约束问题等

数据完整性约束

3.10 创建非空约束

列名1 数据类型 NOT NULL, -- 该列必须填值，不能为空
列名2 数据类型 -- 该列默认允许为空

创建学生表时，给stu_num和name加非空约束
CREATE TABLE tb_student (
  stu_num CHAR(11) NOT NULL, -- 学号必须填
  name VARCHAR(20) NOT NULL, -- 姓名必须填
  intro TINYTEXT -- 简介允许为空
);

3.11 创建Check约束

CHECK约束用于强制限制列中数据的取值范围 / 规则，确保插入或更新的数据符合预设条件（比如年龄必须大于 0、性别只能是 “男” 或 “女” 等）

CREATE TABLE 表名 (
  列名1 数据类型 [约束] CHECK (列名1的条件),
  列名2 数据类型 [约束] -- 其他列
);

//创建表时添加表级 CHECK 约束（多列关联）
CREATE TABLE 表名 (
  列名1 数据类型 [约束],
  列名2 数据类型 [约束],
  列名3 数据类型 [约束],
  -- 表级CHECK约束（适用于多列关联规则）
  CONSTRAINT 约束名 CHECK (多列关联条件)
);

//创建学生表，给age列加 “年龄大于 0 且小于 150” 的约束，给gender列加 “只能是男 / 女” 的约束
CREATE TABLE `tb`.`tb_student` (
  stu_num CHAR(11) NOT NULL PRIMARY KEY, -- 学号：主键+非空
  name VARCHAR(20) NOT NULL, -- 姓名：非空
  age INT NULL CHECK (age > 0 AND age < 150), -- 列级CHECK：年龄合法范围
  gender CHAR(1) NULL CHECK (gender IN ('男', '女')), -- 列级CHECK：性别只能是男/女
  intro TINYTEXT NULL
);

3.12 创建单一键值的主键约束

CREATE TABLE 表名 (
  主键列名 数据类型 PRIMARY KEY, -- 直接给单个列添加主键约束（列级语法）
  其他列名1 数据类型 [约束],
  其他列名2 数据类型 [约束],
  ...
);

插入两条数据
INSERT INTO tb_student2(stu_num,name)
VALUES("16100101","马诗"),
("17130203","马诗");

会发现如果插入的两个数据的主键值相同，数据都不会被插入，原因后面会提到是MYSQL事务处理机制

3.13 创建非单一键值的主键约束（复合主键）

CREATE TABLE 表名 (
  列1 数据类型,
  列2 数据类型,
  其他列 数据类型 [约束],
  -- 表级语法：多列组合作为复合主键
  [CONSTRAINT 主键约束名] PRIMARY KEY (列1, 列2)
);

中括号[]包裹的内容表示 “可选项”，即可加可不加，不影响语法的合法性

创建借阅表（tb_record1），定义四个属性，学生的学号（stu_num），条码号（barcode）和借书日期（borrow_time），它们的组合作为借阅表的联合主键，还书日期（return_time）是借阅表的另外一个属性

CREATE TABLE tb_record1(
stu_num CHAR(20),
barcode CHAR(32),
borrow_time DATETIME,
return_time DATETIME,
CONSTRAINT PK_tb_record1 PRIMARY KEY(stu_num, barcode, borrow_time)
);

INSERT INTO tb_record1
VALUES('16100101', 'I247.56', '2019-04-17 16:35:54', '2019-04-18 17:36:15');

INSERT INTO tb_record1
VALUES('17130202', 'TP311.12', '2019-05-03 16:54:41', NULL);

非单一键值冲突会插入一条，并不像上面一样全部忽略

3.14 创建外键约束（参照完整性）

外键约束（Foreign Key）用于建立两个表之间的关联关系，强制外键列的值必须匹配主表（父表）中主键列或唯一约束列的值，保障数据的参照完整性（比如学生表的 “学院 ID” 必须对应学院表中已存在的 “学院 ID”）

CREATE TABLE 从表名 (
  从表列1 数据类型 [约束],
  外键列 数据类型 [约束], -- 要关联主表的列
  其他列 数据类型 [约束],
  -- 表级语法定义外键约束
  [CONSTRAINT 外键约束名] -- 可选：外键约束命名，便于后续管理
  FOREIGN KEY (外键列名) -- 指定从表中的外键列
  REFERENCES 主表名 (主表关联列名) -- 指定主表及被关联的列（主键/唯一约束列）
  [ON DELETE 级联操作] -- 可选：主表记录删除时的处理规则
  [ON UPDATE 级联操作] -- 可选：主表关联列值更新时的处理规则
);

例子：学生表（从表）关联学院表（主表）

-- 主表：学院表（college_id为主键）
CREATE TABLE `tb`.`tb_college` (
  college_id INT PRIMARY KEY AUTO_INCREMENT, -- 学院ID（主键）
  college_name VARCHAR(50) NOT NULL -- 学院名称
);

-- 从表：学生表（college_id为外键，关联学院表的college_id）
CREATE TABLE `tb`.`tb_student` (
  stu_num CHAR(11) PRIMARY KEY, -- 学号（主键）
  name VARCHAR(20) NOT NULL, -- 姓名
  college_id INT NULL, -- 外键列：学院ID
  age INT NULL CHECK (age > 0 AND 150), -- 年龄
  -- 表级语法定义外键约束，命名为fk_student_college
  CONSTRAINT fk_student_college
  FOREIGN KEY (college_id)
  REFERENCES tb_college (college_id)
  ON DELETE SET NULL -- 可选：主表学院删除时，从表学生的college_id设为NULL
  ON UPDATE CASCADE -- 可选：主表college_id更新时，从表学生的college_id同步更新
);

外键会强制要求 “学生表的college_id（外键列）的值，必须满足两个条件之一”：

要么是NULL（因为该外键列定义为INT NULL，允许学生暂时未分配学院）；
要么是 “学院表” 中已经存在的college_id（主键列）的值。

外键关联后的核心效果是强制保证数据的参照完整性（外键值必须匹配主表主键值），而 “改一边另一边自动改” 取决于外键约束的级联策略，并非默认就会自动同步

在GlobalEducation数据库中，Education_data表用于存储教育相关的统计数据。关于该表与参照完整性的描述，下列说法正确的有哪些？（选择所有适用项）
A. Education_data表若与Country表存在关联，其关联字段必须是Country表的主键，且该字段在Education_data表中不允许出现Country表中不存在的取值
B. 参照完整性要求Education_data表中所有外键字段的值必须与被参照表中对应主键字段的值完全一致，不允许出现空值
C. 若Education_data表通过“institution_id”字段关联Education_institution表，当删除Education_institution表中某条记录时，Education_data表中所有依赖该记录的“institution_id”值必须同时删除，否则会破坏参照完整性
D. 参照完整性仅关注表之间的主键与外键关系，与Education_data表自身的字段约束（如非空、唯一）无关
E. 当向Education_data表插入新数据时，若涉及外键关联，数据库会自动检查该外键值是否在被参照表的对应主键中存在，若不存在则插入失败

选项B：参照完整性规则核心在于外键取值需匹配主键或为空（允许空值情况）
选项 C：错误。删除主表记录时，需通过外键的ON DELETE级联策略（如CASCADE）自动删除子表记录；若未设置该策略，直接删除主表会触发报错，并非 “必须手动删除子表记录”

在 GlobalEducation 数据库中，Education_data 表与 Country 表通过某个字段建立关联以体现参照完整性。下列关于该关联字段的说法，正确的是？
A. 该关联字段可以是 Country 表中的任意非关键字段，只要其值在 Education_data 表中存在即可
B. 该关联字段必须是 Country 表的主键，且 Education_data 表中该字段的值不允许出现 Country 表主键中不存在的情况
C. 该关联字段若为 Country 表的主键，Education_data 表中该字段的值必须与 Country 表主键的所有值完全一一对应，不允许有空值
D. 参照完整性不要求该关联字段必须是 Country 表的主键，只需确保 Education_data 表中该字段的值在 Country 表中存在即可

选项 A：错误。关联字段不能是任意非主键字段（需是主键 / 唯一键），且需保证子表字段值存在于被参照表中，而非 “子表存在即可”。
选项 B：正确。关联字段需是Country表的主键，且子表中该字段值不能出现被参照表主键中不存在的情况，符合参照完整性约束。
选项 C：错误。子表字段值无需与被参照表主键 “所有值一一对应”（只需子表值存在于被参照表中），且允许空值（若字段设置允许）。
选项 D：错误。参照完整性要求被参照表的关联字段必须是主键 / 唯一键，并非 “只需值存在即可”（普通非唯一字段无法保证关联的唯一性和有效性）。

存储引擎


-- 创建主表 tb_student4
CREATE TABLE tb_student4(
    stu_num CHAR(9) PRIMARY KEY,
    name CHAR(20),
    school CHAR(10)
) ENGINE INNODB;

-- 创建关联表 tb_inf_student4（含外键关联）
CREATE TABLE tb_inf_student4(
    stu_num CHAR(9),
    address CHAR(64),
    CONSTRAINT FOREIGN KEY(stu_num) REFERENCES tb_student4(stu_num)
) ENGINE INNODB;

-- 问题（1）相关表操作
-- 判断表是否存在，存在则删除
DROP TABLE IF EXISTS tb_inf_student4;
DROP TABLE IF EXISTS tb_student4;

-- 插入数据（先插主表，再插关联表，符合外键约束）
INSERT INTO tb_student4 VALUES ('000000001','张三','信息学院');
INSERT INTO tb_inf_student4 VALUES ('000000001','北京市石景山区晋元庄路5号');

-- 创建主表 tb_student5（存储引擎为 MyISAM）
CREATE TABLE tb_student5(
    stu_num CHAR(9) PRIMARY KEY,
    name CHAR(20),
    school CHAR(10)
) ENGINE MyISAM;

-- 创建关联表 tb_inf_student5（含外键关联，存储引擎为 MyISAM）
CREATE TABLE tb_inf_student5(
    stu_num CHAR(9),
    address CHAR(64),
    CONSTRAINT FOREIGN KEY(stu_num) REFERENCES tb_student5(stu_num)
) ENGINE MyISAM;

-- 插入数据（先插主表，再插关联表，符合外键约束）
INSERT INTO tb_student5 VALUES ('000000001','张三','信息学院');
INSERT INTO tb_inf_student5 VALUES ('000000001','北京市石景山区晋元庄路5号');

InnoDB是默认的存储引擎，其能对外键约束进行判断但是MYISAM不能，所以代码最后的Insert并不会报错

例题：

A、SELECT：是 MySQL 中查询表中数据的核心关键字（例如 SELECT * FROM 表名; 可查看表中所有数据），符合题意。
B、SHOW：是一个命令前缀，本身不直接查看表数据（需结合子命令，如 SHOW DATABASES 看数据库），不符合。
C、DESCRIBE：用于查看表结构（字段、类型等），不是表数据，不符合。
D、SHOW TABLES：用于查看当前数据库下的表名列表，不是表数据，不符合。
E、SHOW COLUMNS：用于查看表的列信息（类似 DESCRIBE），不是表数据，不符合。

正确答案：只有选项 A（SELECT）

引擎

其中Innodb是MySQL默认的存储引擎

选项 A：正确
一个国家（Country）可以有多个教育机构（Education_institution），因此是一对多关系；实现方式是在 “多” 的一方（Education_institution）中设置外键，指向 “一” 的一方（Country）的主键

选项 B：错误

选项 C：正确
一个国家（Country）可以对应多条教育数据（Education_data），因此是一对多关系；实现方式是将 Country 的主键作为 Education_data 的外键

选项 D：正确
若一个教育机构对应多条教育数据，且一条教育数据对应多个机构，则是多对多关系；

选项 E：错误
逻辑：“国家与教育机构只能是一对多” 的表述过于绝对（比如某些跨国教育机构可能属于多个国家，此时可能形成多对多关系）

Country ↔ Education_institution	多对多	通过Education_data表的Country_ID和Institution_ID作为外键联合关联，形成中间表连接
Country ↔ Education_data	一对多	通过“Country_ID”关联；
Education_institution ↔ Education_data	一对多还是多对多

完整性

域完整性（Domain Integrity）：值要“合法”

常见约束：

NOT NULL（不能为空）
数据类型/长度（INT、VARCHAR(20)）
默认值 DEFAULT
枚举/集合（ENUM）
限定域范围/规则（CHECK age BETWEEN 0 AND 150，或用触发器/应用层校验）

实体完整性（Entity Integrity）：每条记录必须能被唯一标识
常见约束：PRIMARY KEY（主键：唯一 + 非空），设置候选键，UNIQUE（唯一约束：可保证唯一，但一般允许 NULL，具体看 DBMS）

参照完整性（Referential Integrity）：不能出现子表参照了父表不存在的行
常见约束：FOREIGN KEY 外键

选 C：记录唯一性