第2章: 数据库和表操作

学习创建和管理数据库与表

数据库操作

查看所有数据库

SHOW DATABASES;

创建数据库

-- 基本语法
CREATE DATABASE database_name;

-- 指定字符集
CREATE DATABASE mydb CHARACTER SET utf8mb4;

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS mydb;

选择数据库

USE database_name;

删除数据库

-- 删除数据库（谨慎使用！）
DROP DATABASE database_name;

-- 如果存在则删除
DROP DATABASE IF EXISTS database_name;

⚠️ 警告： DROP DATABASE 会永久删除数据库及其所有数据，操作前请务必备份！

数据库操作演示

                    点击按钮执行 SQL 命令
                

表操作

查看所有表

-- 查看当前数据库的所有表
SHOW TABLES;

-- 查看表结构
DESC table_name;
-- 或
DESCRIBE table_name;

创建表

-- 基本语法
CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

-- 示例：创建学生表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

常用约束详解

约束用于保证数据的完整性和一致性。

PRIMARY KEY - 主键约束

唯一标识表中的每一行记录，一个表只能有一个主键。

-- 单列主键
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 复合主键（多列组成）
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

FOREIGN KEY - 外键约束

建立表与表之间的关联关系，保证数据一致性。

-- 创建订单表（主表）
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_no VARCHAR(20)
);

-- 创建订单明细表（从表）
CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE    -- 删除订单时自动删除明细
    ON UPDATE CASCADE    -- 更新订单ID时自动更新明细
);

💡 外键的作用：
1. 保证数据完整性：不能插入不存在的外键值
2. 级联操作：主表数据变化时自动更新从表
3. 防止误删：有从表数据时不能删除主表记录

约束对比表

约束	说明	特点
PRIMARY KEY	主键，唯一标识每条记录	唯一 + 非空，一个表只能有一个
FOREIGN KEY	外键，关联其他表	保证表间数据一致性
UNIQUE	唯一约束	允许NULL，可以有多个
NOT NULL	非空约束	不允许NULL值
DEFAULT	默认值	未指定时使用默认值
AUTO_INCREMENT	自动递增	只能用于主键，从1开始
CHECK	检查约束	限制列值范围（MySQL 8.0+）

CHECK 约束示例

-- MySQL 8.0+ 支持 CHECK 约束
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    stock INT,
    CHECK (price > 0),           -- 价格必须大于0
    CHECK (stock >= 0)           -- 库存不能为负
);

修改表结构

添加列

-- 添加新列
ALTER TABLE students ADD COLUMN phone VARCHAR(20);

-- 添加列并指定位置
ALTER TABLE students ADD COLUMN address VARCHAR(200) AFTER email;

修改列

-- 修改列的数据类型
ALTER TABLE students MODIFY COLUMN age TINYINT;

-- 修改列名和类型
ALTER TABLE students CHANGE COLUMN email user_email VARCHAR(150);

删除列

-- 删除列
ALTER TABLE students DROP COLUMN phone;

重命名表

-- 重命名表
RENAME TABLE old_name TO new_name;

-- 或
ALTER TABLE old_name RENAME TO new_name;

删除表

-- 删除表（谨慎使用！）
DROP TABLE table_name;

-- 如果存在则删除
DROP TABLE IF EXISTS table_name;

-- 清空表数据但保留表结构
TRUNCATE TABLE table_name;

DROP vs TRUNCATE vs DELETE

命令	作用	速度	可回滚
DROP	删除表结构和数据	快	否
TRUNCATE	清空数据，保留表结构	快	否
DELETE	删除数据，可加条件	慢	是

完整示例

创建一个完整的数据库和表

-- 1. 创建数据库
CREATE DATABASE IF NOT EXISTS school CHARACTER SET utf8mb4;

-- 2. 使用数据库
USE school;

-- 3. 创建学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    gender ENUM('男', '女') DEFAULT '男' COMMENT '性别',
    birth_date DATE COMMENT '出生日期',
    class_id INT COMMENT '班级ID',
    score DECIMAL(5,2) DEFAULT 0 COMMENT '成绩',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) COMMENT='学生信息表';

-- 4. 创建班级表
CREATE TABLE classes (
    class_id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(50) NOT NULL,
    teacher VARCHAR(50)
);

-- 5. 查看表结构
DESC students;

-- 6. 查看创建表的 SQL
SHOW CREATE TABLE students;

表结构可视化

students 表结构

字段名	数据类型	约束	说明
student_id	INT	PRIMARY KEY, AUTO_INCREMENT	学号
name	VARCHAR(50)	NOT NULL	姓名
gender	ENUM('男','女')	DEFAULT '男'	性别
birth_date	DATE	-	出生日期
score	DECIMAL(5,2)	DEFAULT 0	成绩

注释

为数据库对象添加注释，提高可读性。

-- 创建表时添加注释
CREATE TABLE products (
    id INT PRIMARY KEY COMMENT '产品ID',
    name VARCHAR(100) COMMENT '产品名称',
    price DECIMAL(10,2) COMMENT '价格'
) COMMENT='产品表';

-- 修改表注释
ALTER TABLE products COMMENT='产品信息表';

-- 修改列注释
ALTER TABLE products MODIFY COLUMN name VARCHAR(100) COMMENT '产品名称（中文）';

🎯 互动练习：SQL 命令生成器

数据库名：

表名：

                点击按钮生成 SQL 语句
            

本章小结

使用 CREATE DATABASE 创建数据库
使用 USE 选择要操作的数据库
使用 CREATE TABLE 创建表
主键（PRIMARY KEY）唯一标识每条记录
常用约束：NOT NULL、UNIQUE、DEFAULT、AUTO_INCREMENT
使用 ALTER TABLE 修改表结构
使用 DROP 删除数据库或表（谨慎使用）
TRUNCATE 清空表数据但保留结构
可以为表和列添加注释提高可读性