MySQL 入门课程与练习 · 连接数据库与准备环境至表关系与权限模型

返回分篇目录

从建表开始，用同一组数据完成查询，再学习表关系、事务和索引。

MySQL 入门课程与练习

从建表开始，用同一组数据完成查询，再学习表关系、事务和索引。

连接数据库与准备环境

数据库是有组织的数据集合，MySQL 是管理这些数据的数据库管理系统（DBMS）。SQL 用于定义表结构、读写数据和控制事务；客户端可以使用终端或图形工具。

mysql -h 127.0.0.1 -P 3306 -u root -p

输入密码后查看版本和字符集。本文的建表、修改与删除都在专用练习库 mysql_practice 中进行。

SELECT VERSION();
SHOW VARIABLES LIKE 'character_set%';
CREATE DATABASE IF NOT EXISTS mysql_practice CHARACTER SET utf8mb4;
SHOW DATABASES;
SHOW CREATE DATABASE mysql_practice;
USE mysql_practice;

中文异常时，同时检查客户端连接、数据库、表和字段的字符集。SET NAMES utf8mb4; 只调整当前连接，不会转换表内数据。退出客户端使用 EXIT;。

建表、字段类型与约束

类型与约束怎么选

需求	类型或约束	使用要点
整数编号、年龄	INT、BIGINT	按取值范围选择；显示宽度不等于存储位数
工资、金额	DECIMAL(10,2)	保存定点小数
名称、固定编码、长文本	VARCHAR(n)、CHAR(n)、TEXT	根据长度和查询需求选择，避免套用固定长度结论
日期与时间	DATE、TIME、DATETIME、TIMESTAMP	先区分日期、时刻和取值范围
唯一标识一行	PRIMARY KEY	主键唯一且非空
限制空值与重复	NOT NULL、UNIQUE	根据字段含义选择
默认值与说明	DEFAULT、COMMENT	默认值不等于强制非空

自增编号不能当作连续序号，也不能依赖删除后复用编号。表名和字段名需要引用时用反引号，字符串使用单引号。

创建统一的练习表

CREATE TABLE dept (
  deptno INT PRIMARY KEY,
  dname VARCHAR(50) NOT NULL,
  loc VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE emp (
  empno INT PRIMARY KEY AUTO_INCREMENT,
  ename VARCHAR(50) NOT NULL,
  job VARCHAR(30) NOT NULL,
  mgr INT NULL,
  hiredate DATE NOT NULL,
  sal DECIMAL(10,2) NOT NULL,
  comm DECIMAL(10,2) NULL,
  deptno INT NOT NULL,
  CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) REFERENCES dept(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SHOW TABLES;
SHOW CREATE TABLE emp;
DESC emp;

后续查询都使用这两张表，避免同一个 emp 在不同练习中代表不同字段。mgr 是主管编号，NULL 表示没有主管；comm 是奖金。

修改表结构

在独立临时表上练习，避免改坏后续查询依赖的 emp。

CREATE TABLE hero (id INT PRIMARY KEY, name VARCHAR(30));
ALTER TABLE hero ADD money INT AFTER name;
ALTER TABLE hero CHANGE name hero_name VARCHAR(50);
ALTER TABLE hero MODIFY money DECIMAL(10,2);
RENAME TABLE hero TO heros;
ALTER TABLE heros DROP money;
DROP TABLE heros;

插入、修改与删除数据

准备练习数据

先插入部门，再插入引用部门的员工。显式列出字段名，避免因字段顺序变化而插错位置。

INSERT INTO dept (deptno, dname, loc) VALUES
  (10, '研发', '北京'), (20, '销售', '上海'), (30, '支持', '成都');

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
  (1, 'LIN', 'MANAGER', NULL, '2020-01-10', 8000, NULL, 10),
  (2, 'LI', 'ENGINEER', 1, '2021-03-01', 6000, 500, 10),
  (3, 'WANG', 'SALESMAN', 1, '2022-05-12', 4000, 800, 20),
  (4, 'SUN', 'CLERK', 3, '2023-07-08', 3000, NULL, 20);

SELECT empno, ename, sal, deptno FROM emp;

有条件地修改与删除

先用相同条件查询目标行。下面两条修改语句是独立示例，不要求为后续查询实际改变数据。

SELECT empno, ename, comm FROM emp WHERE empno = 4;
UPDATE emp SET comm = 0 WHERE empno = 4;

SELECT empno, ename FROM emp WHERE empno = 4;
DELETE FROM emp WHERE empno = 4;

UPDATE 或 DELETE 缺少 WHERE 会影响整张表。DELETE 删除行，TRUNCATE TABLE 清空整表，DROP TABLE 删除表结构；不要将后两者放进期待普通回滚的实验中。

条件查询、排序与分页

NULL、比较与组合条件

-- NULL 使用 IS NULL / IS NOT NULL，而不是 = NULL
SELECT empno, ename FROM emp WHERE mgr IS NULL;
SELECT ename, comm FROM emp WHERE comm IS NOT NULL;
SELECT ename, sal FROM emp WHERE sal BETWEEN 3000 AND 6000;
SELECT ename, deptno FROM emp WHERE deptno IN (10, 20);
SELECT ename, job FROM emp WHERE job <> 'MANAGER';

-- 混合 AND、OR 时写出括号，明确过滤范围
SELECT ename, sal, deptno FROM emp
WHERE (deptno = 10 OR deptno = 20) AND sal >= 4000;

BETWEEN 包含两端。comm IS NOT NULL 与 comm > 0 含义不同，奖金为零不是空值。

模糊查询、别名与去重

LIKE 中 % 匹配任意长度字符串，_ 匹配单个字符。大小写匹配还受字段排序规则影响。

SELECT ename FROM emp WHERE ename LIKE 'L%';
SELECT ename FROM emp WHERE ename LIKE '%N';
SELECT ename FROM emp WHERE ename LIKE '_I%';
SELECT DISTINCT job FROM emp;
SELECT ename AS employee_name, sal AS salary FROM emp;

稳定排序与分页

SELECT empno, ename, sal FROM emp
ORDER BY sal DESC, empno ASC
LIMIT 2 OFFSET 0;

-- 第二页，每页两条；也可以写作 LIMIT 2, 2
SELECT empno, ename, sal FROM emp
ORDER BY sal DESC, empno ASC
LIMIT 2 OFFSET 2;

相同工资再按唯一编号排序，避免页内顺序不确定。大偏移量优化见 MySQL 分页查询实践。

函数、分组与统计

常用函数

用途	表达式
拼接、长度、查找	CONCAT(ename, ' / ', job)、CHAR_LENGTH(ename)、INSTR(ename, 'I')、LOCATE('I', ename)
截取、替换与去空白	LEFT(ename, 2)、RIGHT(ename, 2)、SUBSTRING(ename, 2, 3)、REPLACE(ename, 'I', 'i')、TRIM(ename)
插入、重复与反转	INSERT('abcdef', 3, 2, 'X')、REPEAT('ab', 2)、REVERSE(ename)
大小写转换	UPPER(ename)、LOWER(ename)
空值替代	IFNULL(comm, 0)
数值处理	FLOOR(sal)、ROUND(sal, 2)、TRUNCATE(sal, 2)、MOD(5, 3)、RAND()
当前日期与时间	NOW()、CURDATE()、CURTIME()、DATE(NOW())、TIME(NOW())
时间分量	EXTRACT(YEAR FROM hiredate)
格式转换	DATE_FORMAT(hiredate, '%Y-%m-%d')、STR_TO_DATE('14.08.2018', '%d.%m.%Y')

计算金额时先处理奖金空值：

SELECT ename, sal * 12 + IFNULL(comm, 0) AS annual_total FROM emp;

GROUP BY 与 HAVING

SELECT deptno,
       COUNT(*) AS employee_count,
       SUM(sal) AS total_salary,
       AVG(sal) AS average_salary,
       MIN(sal) AS min_salary,
       MAX(sal) AS max_salary
FROM emp
WHERE sal >= 3000
GROUP BY deptno
HAVING AVG(sal) > 4000
ORDER BY average_salary DESC, deptno ASC;

WHERE 筛选参与统计的行，HAVING 筛选分组后的统计结果。非聚合字段应按分组语义处理，不将任意未分组列混进结果。

COUNT(*) 统计行数，COUNT(comm) 只统计奖金非空的行；多字段分组可写作 GROUP BY deptno, mgr。

SELECT COUNT(*) AS total_rows, COUNT(comm) AS rows_with_bonus FROM emp;
SELECT deptno,
       GROUP_CONCAT(ename ORDER BY empno SEPARATOR ', ') AS employees
FROM emp GROUP BY deptno;

查询子句按 SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT 组织。

子查询与多表连接

子查询：先求条件，再查记录

-- 高于全体平均工资的员工
SELECT empno, ename, sal FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);

-- 所有最高工资员工，保留并列结果
SELECT empno, ename, sal FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp);

-- FROM 后的派生表需要别名
SELECT totals.deptno, totals.total_salary
FROM (
  SELECT deptno, SUM(sal) AS total_salary FROM emp GROUP BY deptno
) AS totals
WHERE totals.total_salary > 10000;

内连接、外连接与自连接

SELECT e.ename, d.dname, d.loc
FROM emp AS e JOIN dept AS d ON e.deptno = d.deptno;

-- 保留没有员工的部门，COUNT(e.empno) 不把空侧算作员工
SELECT d.deptno, d.dname, COUNT(e.empno) AS employee_count
FROM dept AS d LEFT JOIN emp AS e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname;

-- 没有主管的员工仍保留
SELECT e.ename AS employee, m.ename AS manager
FROM emp AS e LEFT JOIN emp AS m ON e.mgr = m.empno;

内连接取匹配记录；左连接保留左表所有记录，未匹配的右侧列为 NULL。右表过滤条件放在 WHERE 中可能排除这些空侧记录，先确认是否仍要保留未匹配行。漏写连接条件会产生不符合预期的组合行。

表关系与权限模型

关系	场景	表达方式
一对一	用户与资料、商品与详情	资料表中的用户编号使用主键或唯一约束
一对多	部门与员工、分类与商品	在“多”的一侧保存另一张表的编号
多对多	教师与学生、用户与角色	中间表保存两端编号，并限制重复组合

外键约束防止引用不存在的主记录；删除被引用的部门前，先处理关联员工。逻辑关联与数据库外键约束是不同选择，不应笼统地认为所有项目都要禁用外键。

权限模型延续三张实体表和两张关系表：用户、角色、权限、用户角色、角色权限。关系表使用组合主键，避免同一关系重复写入。

CREATE TABLE app_user (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE app_role (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE permission (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE user_role (uid INT, rid INT, PRIMARY KEY (uid, rid));
CREATE TABLE role_permission (rid INT, pid INT, PRIMARY KEY (rid, pid));

SELECT DISTINCT u.name AS user_name, p.name AS permission_name
FROM app_user AS u
JOIN user_role AS ur ON u.id = ur.uid
JOIN role_permission AS rp ON ur.rid = rp.rid
JOIN permission AS p ON rp.pid = p.id;