MySQL基础：数据库与数据表的概念与操作

数据库的增删改查操作

-- 显示所有已存在的数据库（用于查看当前数据库环境中的数据库列表）
SHOW DATABASES;

-- 获取当前正在使用的数据库名称（用于验证当前会话的数据库上下文）
SELECT DATABASE();

-- 切换到名为demo的数据库
USE demo;

-- 创建名为demo的数据库（如果已存在则不执行任何操作）
CREATE DATABASE IF NOT EXISTS demo 
    [CHARACTER SET utf8mb4]      -- 可选字符集声明（默认为utf8mb4）
    [COLLATE utf8mb4_general_ci];-- 可选排序规则声明（默认为utf8mb4_general_ci）

-- 删除名为demo的数据库（如果不存在则报错）
DROP DATABASE demo;

-- 安全删除数据库（如果不存在则忽略错误）
DROP DATABASE IF EXISTS demo;

-- 修改demo数据库的字符集和排序规则配置
ALTER DATABASE demo 
    CHARACTER SET utf8mb4         -- 使用utf8mb4字符集（支持更多Unicode字符，包括Emoji）
    COLLATE utf8mb4_0900_ai_ci;   -- 使用最新的排序规则（ai=True表示大小写不敏感）

SQL数据类型

常见的SQL数据类型

数字类型：整型、浮点型、定点型等。
字符串类型：字符型、文本型、枚举型、集合型等。
日期时间类型：日期型、日期时间型、时间戳型等。

数据类型的属性解释

NULL：数据列可包含NULL值。
NOT NULL：数据列不允许包含NULL值。
DEFAULT：默认值。
PRIMARY KEY：KEY 主键。
AUTO_INCREMENT：自动递增，适用于整数类型。
UNSIGNED：是指数值类型只能为正数。
CHARACTER SET name：指定一个字符集。
COMMENT：对表或者字段说明。

整数类型

专门用来保存整数
区分有符号和无符号，默认就是有符号的
可以在数据类型后加上 unsigned 表示是无符号的
在设置整型时，可以设置将来显示的位宽，不足的补足，超出不管。
默认填充的是空格。
tinyint(2)：设置位宽是 2，如果查询的是1，显示的就是' 1'。

类型	字节	有符号取值范围	无符号取值范围	说明	典型应用场景
tinyint	1	-128 ~ 127	0 ~ 255	超小整数	状态码、布尔标志
smallint	2	-32,768 ~ 32,767	0 ~ 65,535	小整数	计数器、评分等级
mediumint	3	-3,888,608 ~ 3,888,607	0 ~ 16,777,215	中等整数	分区标识、中等规模计数器
int	4	-2,147,483,648 ~ 2,147,483,647	0 ~ 4,294,967,295	标准整数	主键自增、常规数值存储
bigint	8	-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807	0 ~ 18,446,744,073,709,551,615	大整数	用户ID、时间戳、金融计算

浮点类型

专门保存小数，会丢失精度
不要用来保存不希望丢失精度的数据

类型	字节	精度（有效数字位数）	说明
float	4	6	单精度浮点数（±1.17549435×10⁻³⁸ ~ ±3.4028235×10³⁸）
double	8	16	双精度浮点数（±2.225073858497757×10⁻³⁰⁸ ~ ±1.7976931348623157×10³⁰⁸）

定点类型

专门保存小数，不会丢失精度
可以用来保存不希望丢失精度的数据

类型	说明
DECIMAL	`DECIMAL(size, d)`● `size`：数字总位数（整数部分 + 小数部分），● `d`：小数部分位数● 约束条件： - `size ≥ d + 1` - `d ≥ 0` - `size`最大支持38位

字符串类型

数据类型	说明
char(size)	字符型，固定长度字符串，可包含字母、数字及特殊字符。size范围：0~255
varchar(size)	字符型，可变长度字符串，可包含字母、数字及特殊字符。size范围：0~65535
tinytext	文本类型，长度为0~255的字符串文本
text	文本类型，长度为0~65535的字符串文本
mediumtext	文本类型，长度为0~16,777,215的字符串文本
longtext	文本类型，长度为0~4,294,967,295的字符串文本
enum(v1, v2, ...)	枚举类型，插入数据必须匹配预定义值列表中的其中一个值，示例：`ENUM('男', '女')`
set(v1, v2, ...)	集合类型，插入数据可匹配预定义值列表中的一个或多个值，示例：`SET('A', 'B', 'C')`

日期时间类型

专门用来保存日期和时间
datetime 和 timestamp 可以自动初始化和更新为当前日期时间

类型	格式	取值范围	说明
year	`YYYY`	`1901~2155` 或 `0000`	年份类型，支持四位数字（MySQL 5.7+ 默认范围1901-2155）
date	`YYYY-MM-DD`	`1000-01-01 ~ 9999-12-31`	日期类型，包含年月日（范围比MySQL默认的1900-0000更广）
time	`hh:mm:ss` 或 `hhh:mm:ss`	`-838:59:59 ~ 838:59:59`	时间类型，支持负数时间表示凌晨（三位小时格式更易读）
datetime	`YYYY-MM-DD HH:mm:ss`	`1000-01-01 00:00:00 ~ 9999-12-31 23:59:59`	日期时间组合类型，精度到秒
timestamp	`YYYY-MM-DD HH:mm:ss`	`1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC`	时间戳类型，基于Unix时间戳（注意2038年溢出问题）

数据表的增删改查操作

-- 获取当前连接的数据库名称（用于确认当前操作的数据库环境）
SELECT DATABASE();

-- 切换到名为 `demo` 的数据库（若数据库不存在会报错）
USE demo;

-- 查看当前数据库中的所有表（需要 SELECT 权限）
SHOW TABLES;

/*
 * 创建表
 * 注意：
 * 1. 最后一列后不能有逗号
 * 2. 推荐添加主键约束和默认值
 */
CREATE TABLE student (
    id INT UNSIGNED,          -- 无符号整数类型（0-4294967295）
    name VARCHAR(20),         -- 变长字符串（最多20字符），建议添加 NOT NULL 约束
    age TINYINT UNSIGNED,      -- 无符号小整数（0-255），适合年龄字段
    gender ENUM('男', '女', '保密'),  -- 枚举类型（仅允许指定值）
    createdAt TIMESTAMP        -- 时间戳类型（记录创建时间）
);

/*
 * 创建表（如果不存在则跳过，避免重复创建报错）
 * 适用于需要初始化脚本的场景
 */
CREATE TABLE IF NOT EXISTS student (
    id INT UNSIGNED,
    name VARCHAR(20),
    age TINYINT UNSIGNED,
    gender ENUM('男', '女', '保密'),
    createdAt TIMESTAMP
);

-- 查看数据表的创建语句，包括表的结构和属性
SHOW CREATE TABLE student;

-- 查看表结构
DESCRIBE student;

-- 删除表（执行后不可逆！）
DROP TABLE student;

-- 安全删除表（如果不存在也不报错）
DROP TABLE IF EXISTS student;

-- 修改表名（新表名不能已存在）
ALTER TABLE student RENAME TO stu;

/*
 * 添加字段
 */
ALTER TABLE student ADD updatedAt TIMESTAMP;

-- 删除字段（需确保字段存在）
ALTER TABLE student DROP updatedAt;

-- 修改字段类型
ALTER TABLE student MODIFY createdAt DATETIME;

-- 同时修改字段名和类型
ALTER TABLE student CHANGE createdAt createAt TIMESTAMP;

SQL约束

1. 主键约束 (PRIMARY KEY)

作用：唯一标识表中每一行，确保数据完整性。
特点：
- 必须唯一且非空 (NOT NULL + UNIQUE)。
- 一个表只能有一个主键，但可包含多列（复合主键）。

示例：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL
);

2. 外键约束 (FOREIGN KEY)

作用：建立表间关系，强制引用完整性。
特点：
- 仅InnoDB引擎支持。
- 外键列值必须匹配被引用表的主键/唯一索引。
- 可定义级联操作（如 ON DELETE CASCADE）。

示例：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

3. 唯一性约束 (UNIQUE)

作用：确保列或列组合的值唯一。
特点：
- 允许NULL值（除非同时加 NOT NULL）。
- 多个唯一约束可共存。

示例：

CREATE TABLE emails (
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE
);

4. 非空约束 (NOT NULL)

作用：强制列必须填写值，禁止NULL。
特点：
- 常与 DEFAULT 结合使用（如 NOT NULL DEFAULT 'value'）。
- 唯一约束允许NULL时，NOT NULL 会覆盖此行为。

示例：

CREATE TABLE products (
    name VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00
);

5. 默认值约束 (DEFAULT)

作用：当未指定列值时自动填充默认值。
特点：
- 数据类型需与列匹配。
- 适用于INSERT操作，不影响UPDATE（除非显式设置）。

示例：

CREATE TABLE logs (
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

6. 检查约束 (CHECK)

作用：限制列的取值范围或格式。
特点：
- MySQL语法支持，但实际由存储引擎处理（如InnoDB部分支持）。
- 跨字段逻辑可能失效，需结合触发器或应用层校验。

示例：

CREATE TABLE students (
    age INT CHECK (age BETWEEN 18 AND 100)
);

7. 自增约束 (AUTO_INCREMENT)

作用：为主键列自动生成递增整数。
特点：
- 仅适用于整数类型列。
- 默认从1开始，可自定义起始值。

示例：

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200)
);

约束对比

约束类型	唯一性	非空	允许NULL	复合支持	引擎依赖
PRIMARY KEY	✔️	✔️	❌	✔️	所有
FOREIGN KEY	-	-	-	❌	InnoDB
UNIQUE	✔️	❌	✔️	✔️	所有
NOT NULL	-	✔️	❌	❌	所有
DEFAULT	-	-	✔️	❌	所有
CHECK	-	-	✔️	✔️	部分引擎
AUTO_INCREMENT	-	❌	❌	❌	所有

外键约束

什么是外键？

外键（Foreign Key） 是一个字段（或一组字段），它的值必须匹配另一张表中的 主键（Primary Key） 的值。 👉 目的：确保两张表之间的数据关联关系正确，防止「无效引用」。

外键通常定义在从表（子表）中，用于引用主表（父表）的主键或唯一键。
在一对多关系中，外键始终在“多”的一方（从表）创建。

基础外键约束

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) 
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

复合外键约束

CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) 
        REFERENCES orders(order_id)
        ON DELETE CASCADE,
    FOREIGN KEY (product_id) 
        REFERENCES products(product_id)
        ON DELETE RESTRICT
);

外键的常见规则

匹配主键 外键字段的值必须完全匹配主表主键的值（包括类型和长度）。
级联操作（可选） 可以通过 ON DELETE 和 ON UPDATE 控制关联行为。
外键操作类型
- 严格操作：restrict
  - 主表中不存在对应的数据，从表不允许添加
  - 从表引用着数据，主表对应的数据不允许删除
  - 从表引用着数据，主表对应的主键不允许修改
- 置空操作：set null
  - 修改或删除主表 id 时，所有跟它关联的从表字段都会被设置为null。
- 级联操作：cascade
  - 修改或删除主表 id 时，所有跟它关联的从表字段都会做同样的操作。

何时用外键？

当两张表有明确的 一对多关系 时（如班级和学生）。
需要严格确保数据关联时（如订单和商品）。
不推荐过度使用：外键会增加写操作的开销，需权衡灵活性与一致性。