MySQL 数据定义与查询操作详解
数据库基础管理
-- 创建数据库
CREATE DATABASE school_db;
-- 查看数据库创建信息
SHOW CREATE DATABASE school_db;
-- 删除数据库
DROP DATABASE school_db;
-- 修改字符集
ALTER DATABASE school_db CHARACTER SET utf8mb4;
-- 切换当前数据库
USE school_db;数据表创建与结构管理
-- 创建员工信息表
CREATE TABLE staff (
staff_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR(30) NOT NULL,
gender ENUM('M', 'F') DEFAULT 'M',
birth_year YEAR,
entry_date DATE NOT NULL,
position VARCHAR(40),
remark TEXT,
monthly_salary DECIMAL(12, 2),
room_number SMALLINT,
dept_code INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 查看表结构
DESCRIBE staff;
-- 查看所有表
SHOW TABLES;
-- 查看建表语句
SHOW CREATE TABLE staff;表结构变更操作
-- 添加字段
ALTER TABLE staff ADD phone VARCHAR(15) AFTER fullname;
ALTER TABLE staff ADD email VARCHAR(50) FIRST;
-- 删除字段
ALTER TABLE staff DROP COLUMN remark;
-- 修改字段类型
ALTER TABLE staff MODIFY monthly_salary DECIMAL(15, 2);
-- 修改字段名及类型
ALTER TABLE staff CHANGE birth_year birth_date DATE;
-- 添加索引
ALTER TABLE staff ADD INDEX idx_name (fullname);
-- 删除索引
ALTER TABLE staff DROP INDEX idx_name;
-- 添加外键
ALTER TABLE staff ADD CONSTRAINT fk_dept
FOREIGN KEY (dept_code) REFERENCES department(id);
-- 删除外键
ALTER TABLE staff DROP FOREIGN KEY fk_dept;
-- 修改存储引擎
ALTER TABLE staff ENGINE=MyISAM;表复制操作
-- 复制表结构及数据（不复制索引）
CREATE TABLE staff_backup AS SELECT * FROM staff;
-- 仅复制表结构
CREATE TABLE staff_template AS SELECT * FROM staff WHERE 1=0;
-- 或使用 LIKE
CREATE TABLE staff_copy LIKE staff;完整性约束详解
主键约束
-- 单列主键
CREATE TABLE course (
course_no INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(50) NOT NULL
);
-- 或
CREATE TABLE course (
course_no INT AUTO_INCREMENT,
course_name VARCHAR(50) NOT NULL,
PRIMARY KEY (course_no)
);
-- 复合主键
CREATE TABLE enrollment (
student_id INT,
course_id INT,
enroll_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (student_id, course_id)
);
-- 后期添加复合主键
ALTER TABLE enrollment ADD PRIMARY KEY (student_id, course_id);
-- 删除主键（需先移除自增）
ALTER TABLE course MODIFY course_no INT NOT NULL;
ALTER TABLE course DROP PRIMARY KEY;唯一约束
-- 建表时添加
CREATE TABLE user_account (
uid INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) UNIQUE,
email VARCHAR(60),
UNIQUE KEY uk_email (email)
);
-- 后期添加
ALTER TABLE user_account ADD CONSTRAINT uk_phone UNIQUE (phone);
-- 删除唯一约束
ALTER TABLE user_account DROP INDEX uk_phone;表关系设计
一对多关系
将外键置于"多"的一方：
-- 班级表（一）
CREATE TABLE class_info (
class_id INT PRIMARY KEY,
class_name VARCHAR(30)
);
-- 学生表（多）
CREATE TABLE student_info (
stu_id INT PRIMARY KEY,
stu_name VARCHAR(20),
class_id INT,
FOREIGN KEY (class_id) REFERENCES class_info(class_id)
);多对多关系
创建中间关联表：
-- 商品表
CREATE TABLE product (
pid INT PRIMARY KEY,
pname VARCHAR(40)
);
-- 标签表
CREATE TABLE tag (
tid INT PRIMARY KEY,
tname VARCHAR(20)
);
-- 关联表
CREATE TABLE product_tag (
pid INT,
tid INT,
PRIMARY KEY (pid, tid),
FOREIGN KEY (pid) REFERENCES product(pid),
FOREIGN KEY (tid) REFERENCES tag(tid)
);一对一关系
在任意一方添加唯一外键，通常放在查询频繁的一方：
-- 用户基础信息
CREATE TABLE user_basic (
user_id INT PRIMARY KEY,
nickname VARCHAR(30)
);
-- 用户详情（一对一）
CREATE TABLE user_detail (
detail_id INT PRIMARY KEY,
address VARCHAR(100),
user_id INT UNIQUE,
FOREIGN KEY (user_id) REFERENCES user_basic(user_id)
);数据查询操作
语法执行顺序
-- 书写顺序
SELECT DISTINCT 字段列表
FROM 表名
JOIN 关联表 ON 连接条件
WHERE 过滤条件
GROUP BY 分组字段
HAVING 分组过滤
ORDER BY 排序字段
LIMIT 起始位置, 记录数;
-- 实际执行顺序
FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT条件查询
-- 比较运算
SELECT * FROM staff WHERE monthly_salary > 8000;
-- 逻辑运算
SELECT * FROM staff WHERE dept_code = 1 AND monthly_salary < 5000;
-- 范围查询
SELECT * FROM staff WHERE birth_year BETWEEN 1990 AND 2000;
-- 空值判断
SELECT * FROM staff WHERE remark IS NULL;
-- 集合查询
SELECT * FROM staff WHERE dept_code IN (1, 3, 5);
-- 模糊查询
SELECT * FROM staff WHERE fullname LIKE '张%'; -- 姓张
SELECT * FROM staff WHERE fullname LIKE '%伟'; -- 名伟
SELECT * FROM staff WHERE fullname LIKE '李_'; -- 姓李，单名分组与聚合
-- 统计各部门人数
SELECT dept_code, COUNT(*) AS total FROM staff GROUP BY dept_code;
-- 统计各部门平均薪资
SELECT dept_code, AVG(monthly_salary) AS avg_salary
FROM staff GROUP BY dept_code;
-- 分组后筛选（HAVING）
SELECT dept_code, AVG(monthly_salary) AS avg_salary
FROM staff
GROUP BY dept_code
HAVING avg_salary > 6000;
-- 分组后组内详情
SELECT dept_code, GROUP_CONCAT(fullname) AS members
FROM staff GROUP BY dept_code;子查询应用
-- 查询薪资高于平均水平的员工
SELECT * FROM staff
WHERE monthly_salary > (SELECT AVG(monthly_salary) FROM staff);
-- 查询各部门最新入职员工
SELECT * FROM staff
WHERE (dept_code, entry_date) IN (
SELECT dept_code, MAX(entry_date)
FROM staff GROUP BY dept_code
);排序与分页
-- 按薪资降序
SELECT * FROM staff ORDER BY monthly_salary DESC;
-- 多字段排序
SELECT * FROM staff ORDER BY dept_code ASC, monthly_salary DESC;
-- 限制返回条数
SELECT * FROM staff LIMIT 10; -- 前10条
SELECT * FROM staff LIMIT 20, 10; -- 第21条开始，取10条（偏移量20）多表连接查询
连接类型详解
-- 笛卡尔积（无连接条件）
SELECT * FROM department, employee;
-- 内连接：返回两表匹配记录
SELECT d.dname, e.ename
FROM department d
INNER JOIN employee e ON d.did = e.dept_id;
-- 左连接：返回左表全部，右表不匹配补NULL
SELECT d.dname, e.ename
FROM department d
LEFT JOIN employee e ON d.did = e.dept_id;
-- 右连接：返回右表全部，左表不匹配补NULL
SELECT d.dname, e.ename
FROM department d
RIGHT JOIN employee e ON d.did = e.dept_id;
-- 全连接：MySQL不支持FULL JOIN，用UNION模拟
SELECT d.dname, e.ename
FROM department d
LEFT JOIN employee e ON d.did = e.dept_id
UNION
SELECT d.dname, e.ename
FROM department d
RIGHT JOIN employee e ON d.did = e.dept_id;多表连接执行流程
-- 示例：三表关联查询
SELECT DISTINCT s.fullname, d.dname, p.project_title
FROM staff s
LEFT JOIN department d ON s.dept_code = d.did
LEFT JOIN project_assignment pa ON s.staff_id = pa.sid
LEFT JOIN project p ON pa.pid = p.project_id
WHERE s.entry_date > '2020-01-01'
GROUP BY s.staff_id
HAVING COUNT(pa.pid) > 2
ORDER BY s.fullname
LIMIT 0, 20;
-- 执行步骤：
-- 1. 从 staff 表获取数据
-- 2. 与 department 做左连接
-- 3. 与 project_assignment 做左连接
-- 4. 与 project 做左连接
-- 5. 应用 WHERE 过滤入职日期
-- 6. 按 staff_id 分组
-- 7. HAVING 筛选参与项目数>2的员工
-- 8. SELECT 选取字段并去重
-- 9. 按姓名排序
-- 10. 取前20条
