【开发】MySQL学习笔记

【开发】MySQL学习笔记

DDL(Data Definition Language)

数据库操作

• 查询

查询所有数据库：

SHOW DATABASES;

查询当前数据库：

SELECT DATABASE();

• 创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]

• 删除

DROP DATABASE [IF EXISTS] 数据库名;

• 使用

USE 数据库名;

表操作

• 查询

查询当前所有表：

SHOW TABLES;

查询表结构：

DESC name;

查询指定表的建表语句：

SHOW CREATE TABLE name;

• 创建

CREATE TABLE(
    字段i 字段i类型 [COMMENT '注释'],
);

• 数据类型

数值类：

(TINY SMALL MEDIUM BIG)INT
FLOAT DOUBLE DECIMAL

字符（串）类：

(VAR)CHAR
(TINY MEDIUM LONG)BLOB
(TINY MEDIUM LONG)TEXT

日期时间类：

DATE
TIME
YEAR
DATETIME
TIMESTAMP

• 修改

添加字段：

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT '注释'][约束];

修改数据类型：

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度)

修改字段名和数据类型：

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT '注释'][约束];

修改表名：

ALTER TABLE 表名 RENAME TO 新表名;

• 删除

删除字段

ALTER TABLE 表名 DROP 字段名;

删除表：

DROP TABLE [IF EXIST] 表名;

删除指定表，并重新创建该表：

TRUNCATE TABLE 表名;

DML(Data Manipulation Language)

• 添加数据

给指定字段添加数据：

INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);

给全部字段添加数据：

INSERT INTO 表名 VALUES (值1, 值2, ..., 值n);

批量添加数据：

INSERT INTO 表名 [(字段1, 字段2, ...)] VALUES (a值1, a值2, ...), (b值1, b值2, ...), ...;

• 修改数据

UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [WHERE 条件];

• 删除数据

DELETE FROM 表名 [WHERE 条件];

DQL(Data Query Language)

关键语句：SELECT

基本查询

• 查询多个字段

SELECT 字段1, 字段2, 字段3, ... FROM 表名;

SELECT * FROM 表名 # 查询所有内容

• 设置别名

SELECT 字段1 [[AS] 别名1], 字段2 [[AS] 别名2], ... FROM 表名;

• 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

条件查询

语法：

SELECT 字段列表 FROM 表名 WHERE 条件列表;

条件：

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN ... AND ...	在区间内
IN(...)	在列表中任意一个
LIKE 占位符	模糊搜索("_"匹配单个字符，"%"匹配任意个字符)
IS NULL	是NULL
AND 或 &&	并且
OR 或 ||	或者
NOT 或 !	非

聚合函数

语法：

SELECT 聚合函数(字段列表) FROM 表名;

常用聚合函数：

函数	功能
COUNT	统计数量
MAX	最大值
MIN	最小值
AVG	均值
SUM	求和

注：NULL不参与聚合运算

分组查询

语法：

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 过滤后条件]

辨析WHERE和HAVING：

• 执行时机不同：WHERE是分组前判断；HAVING是分组后过滤；
• 判断条件不同：WHER不能对聚合函数判断而HAVING可以。

排序查询

语法：

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式, 字段2 排序方式, ...;

字段顺序代表排序的优先级

排序方式：

• ASC：升序（默认）
• DESC：降序

分页查询

语法：

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

注：

• 起始索引从0开始，起始索引 = (查询页码 - 1) * 每页记录数【其实就是从0开始算下标】;
• 分页查询是数据库方言，在MySQL中是LIMIT;
• 如果查询的是第一页，起始索引可以省略，只写记录数。

执行顺序

FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
SELECT
	字段列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

注：这不是编写的顺序

DCL(Data Control Language)

用户管理

• 查询用户

USE mysql;
SELECT * FROM user;

• 创建用户

注：主机名处用'%'说明任意主机可访问

CREATE USER '用户名' @ '主机名' IDENTIFIED BY 密码

• 修改用户密码

ALTER USER '用户名' @ '主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

• 删除用户

DROP USER '用户名' @ '主机名';

权限控制

语法：

• 查询权限

SHOW GRANTS FOR '用户名' @ '主机名';

• 授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名' @ '主机名';

• 撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名' @ '主机名';

常见权限：

权限	说明
ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

函数

字符串函数

函数	功能
CONCAT(S1, S2, ...)	字符串拼接
LOWER(S)	转小写
UPPER(S)	转大写
LPAD(S, n, T)	用T左填充直到n
RPAD(S, n, T)	用T右填充直到n
TRIM(S)	去掉头尾部空格
SUBSTRING(S, start, len)	子串（下标从1开始）

数值函数

函数	功能
CEIL(x)	上取整
FLOOR(x)	下取整
MOD(x, y)	x % y
RAND()	0~1的随机数
ROUND(x, y)	x保留y位小数，四舍五入

日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获得指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL expr TYPE)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1, date2)	返回起始时间date1和结束时间date2之间的天数

注：

• INTERVAL expr TYPE举例可以是：

SELECT DATE_ADD('2026-8-10', INTERVAL 70 DAY);

• DATEDIFF实际上是date1 - date2

流程函数

函数	功能
IF(value, x, y)	如果value为true则返回x，否则返回y
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [val1] THEN [res1], ... ELSE [default] END	如果val1为true则返回res1, ...，否则返回default
CASE [expr] WHEN [val1] THEN [res1], ... ELSE [default] END	如果expr的值为val1则返回res1, ..., 否则返回default

• CASE ... WHEN的例子

SELECT * FROM test.employee;
SELECT name, department,
    (CASE department
        WHEN '技术部'
            THEN '技术部'
        	ELSE '非技术部'
     END) AS '是否为技术部'
FROM employee;

约束

概述

约束是作用与表中字段上的规则，用于限制存储在表中的数据。这种操作可以保证数据库中数据的正确性、有效性和完整性。

分类：

约束	描述	关键字
非空约束	限制字段数据不能为null	NOT NULL
唯一约束	保证该字段唯一、不重复	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束	保证字段满足某一条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

约束可以在创建/修改表的时候添加。

外键约束

• 添加外键

CREATE TABLE 表名(
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

• 删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

这里展示一个具体的流程：

首先生成两张表，主表代表部分楼栋信息，另外一张表代表员工信息

CREATE TABLE department_info (
    dept_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
    dept_name VARCHAR(30) NOT NULL UNIQUE COMMENT '部门名称',
    location VARCHAR(50) COMMENT '部门地点'
);

INSERT INTO department_info (dept_name, location) VALUES
('技术部', 'A栋3楼'),
('人事部', 'B栋2楼'),
('财务部', 'C栋1楼'),
('市场部', 'A栋5楼'),
('运营部', 'B栋4楼');

其中AUTO_INCREMENT代表dept_id会自增。

CREATE TABLE employee_profile (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    age INT COMMENT '年龄',
    salary DECIMAL(10,2) COMMENT '工资',
    dept_id INT COMMENT '部门ID'
);

这时候就可以写外键约束

ALTER TABLE employee_profile
ADD CONSTRAINT foreign_key_emp_dept
FOREIGN KEY (dept_id)
REFERENCES department_info(dept_id);

这样就把员工表的dept_id与主表的dept_id绑定在一起了，这样的话下面有两个语句

INSERT INTO employee_profile (name, age, salary, dept_id)
VALUES ('周八', 24, 7800.00, 1); # 成功，因为确实存在dept_id为1的部门

INSERT INTO employee_profile (name, age, salary, dept_id)
VALUES ('吴九', 27, 9200.00, 99); # 报错，因为并没有dept_id为99的部门

可以看出从表只能从主表中选择外键值，这样就保证了数据的一致性

• 删除/更新行为

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主字段名) [行为信息]

行为	说明
NO ACTION	父表删除/更新对应记录时，检查是否有对应外键，若有则不允许删除/更新
RESTRICT	（与NO ACTION一致）
CASCADE	父表删除/更新对应记录时，检查是否有对应外键，如果有也删除/更新外键在子表的记录
SET NULL	父表删除/更新对应记录时，检查是否有对应外键，如果有则设置为null(前提是要允许null)
SET DEFAULT	父表删除/更新对应记录时，检查是否有对应外键，如果有则设置为默认值

多表查询

概述

首先多表关系应当会有三种情况：

• 一对多（多对一）：部门 ====> 员工

前两种情况的对应关系实现可以在多的一方建立外键，指向一的一方的主键

• 一对一：用户 <====> 用户详情

将一张表的基础字段放到另一张表中，提高操作效率

做法是任意一方加入外键，关联另一方的主键，设置为UNIQUE

• 多对多：学生 <====> 课程

这种时候就需要建立一张中间表，中间表至少包含两个外键分别关联两张表的主键

这时候用一个例子说明一下，设想一个学生与课程对应的场景，中间表就是选课表

-- 1. 学生表
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
    name VARCHAR(50) NOT NULL COMMENT '学生姓名',
    gender CHAR(1) COMMENT '性别',
    age INT COMMENT '年龄',
    class_name VARCHAR(50) COMMENT '班级'
) COMMENT '学生表';


-- 2. 课程表
CREATE TABLE course (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '课程ID',
    course_name VARCHAR(50) NOT NULL COMMENT '课程名',
    teacher VARCHAR(50) COMMENT '授课老师',
    credit DECIMAL(3,1) COMMENT '学分'
) COMMENT '课程表';

这时候建立一个选课表

-- 3. 中间表（选课表）
CREATE TABLE student_course (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
    student_id INT NOT NULL COMMENT '学生ID',
    course_id INT NOT NULL COMMENT '课程ID',
    score DECIMAL(5, 2) COMMENT '成绩',
    CONSTRAINT fk_sc_student FOREIGN KEY (student_id) REFERENCES student(id),
    CONSTRAINT fk_sc_score FOREIGN KEY (course_id) REFERENCES course(id)
) COMMENT '选课表';

就可以用这样的关系来查询了，写一个简单的语句查询“张三”的成绩

SELECT s.name AS '学生名字', c.course_name AS '课程名', sc.score AS '成绩'
FROM student s
JOIN student_course sc ON s.id = student_id
JOIN course c ON c.id = sc.course_id
WHERE s.name = '张三';

多表查询的其中一个目的，就是消除无效的笛卡尔积，这个时候就会用到连接查询的语法：

• 内连接：相当于查询A、B交集部分的数据
• 外连接
  • 左外连接：查询左表所有数据，以及两张表交集部分数据
  • 右外连接：查询右表所有数据，以及两张表交集部分数据
• 自连接：当前表与自身的连接查询，自连接必须使用表的别名

另外还会使用到子查询

连接查询

内连接

• 隐式内连接

SELECT 字段列表 FROM 表1, 表2 WHERE 条件;

• 显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表二 ON 连接条件;

前面给的例子就是内连接

外连接

• 左外连接

查询左表所有数据包含表1和表2的交集

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

• 右外连接

查询右表所有数据包含表1和表2的交集

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

其实就是打算在查询的时候把某个表的信息全部展示出来，即便有些信息在另一个表没有呈现

假如我在学生表中加入一个嘉豪：

INSERT INTO student (name, gender, age, class_name) VALUES
('嘉豪', '男', '17', '软件0班');

嘉豪并没有选课，但是我想得到所有学生的选课信息，就可以用外连接查询

-- 查询所有学生及其选课信息，包括没选课的学生
SELECT s.name, sc.course_id, sc.score
FROM student s
LEFT JOIN student_course sc on s.id = sc.student_id;

查出来会发现嘉豪那一行是下面这样：

name	course_id	score
嘉豪	< null >	< null >

右外连接由于语义对称，因此在使用过程中完全可以全部用左外连接写等价写法

自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表B 别名B ON 条件;

自连接看起来是一张表自己和自己连接，感觉有些奇怪

其实是因为有些表本身带有层级信息，但这又只是一张表，这时候就需要把同一张表当作两张不同性质的表去看待

比如一张学生的表，有学生自己的班级信息，也有是否是班长的信息，这个时候我要查询某位学生的班长，这就是类似多表查询要做的事情，但本质是在一张表，就需要用自连接

首先生成一张表：

CREATE TABLE student_self (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
    name VARCHAR(50) NOT NULL COMMENT '学生姓名',
    gender CHAR(1) COMMENT '性别',
    age INT COMMENT '年龄',
    class_name VARCHAR(50) COMMENT '班级',
    monitor_id INT COMMENT '班长ID，指向本表的id'
) COMMENT '学生自连接练习表';

INSERT INTO student_self (id, name, gender, age, class_name, monitor_id) VALUES
(1, '张三', '男', 20, '软件1班', NULL),
(2, '李四', '男', 19, '软件1班', 1),
(3, '王五', '女', 20, '软件1班', 1),

(4, '赵六', '男', 21, '软件2班', NULL),
(5, '孙七', '女', 20, '软件2班', 4),
(6, '周八', '男', 19, '软件2班', 4),

(7, '吴九', '女', 20, '软件3班', NULL),
(8, '郑十', '男', 21, '软件3班', 7),
(9, '钱一', '女', 20, '软件3班', 7),

(10, '冯二', '男', 22, '软件4班', NULL);

这个表的意思就是1、4、7、10号学生分别是本班班长

然后可以查询每个学生的班长是谁

-- 查询每个学生及其班长姓名
SELECT student.name AS '姓名',
       monitor.name AS '班长'
FROM student_self student
LEFT JOIN student_self monitor
ON student.monitor_id = monitor.id;

联合查询

联合查询可以把多次查询的结果合并起来，产生新的查询结果

SELECT 字段列表 FROM 表1
UNION [ALL]
SELECT 字段列表 FROM 表2;

从这样的形式可以看出，联合查询的不同结果必须列数相同，并且对应的字段类型也要相同

子查询

子查询又称嵌套查询，就是在SQL语句中嵌套SELECT查询的方式

SELECT * FROM 表2 WHERE 列1 = (SELECT 列1 FROM 表2)

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT中的任何一个

根据子查询结果不同又会分成不同类型

• 标量子查询：返回单个值

常用操作符：比较符号

• 列子查询：返回结果是一列（可多行）

常用操作符：IN、NOT IN、ANY、SOME、ALL

• 行子查询：返回结果是一行（可以是多列）

常用操作符：=、<>、IN、NOT IN

• 表子查询：返回结果是多行多列

常用操作符：IN

事务

概述

事务是一组操作的集合，是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，这些操作要么同时成功，要么同时失败。

使用的场景是这样的：假如A要向B转账，那么A的账户会减少钱，而B的账户会增加钱，如果不给这个操作集合设置为整个事务的话，可能会先执行第一个操作，而第二个操作由于某种原因无法实现，导致账户出错。因而把这两个操作用事务封装，一个是保证了能够一起成功或失败，另一个是能保证语义。

基本操作

• 开启事务

START TRANSACTION;

• 查看/设置事务提交方式

SELECT @@autocommit;
SET @@autocommit = 0;
# @@autocommit 的值 0/1 为 关闭/开启自动提交

• 提交事务

COMMIT;

• 回滚事务

ROLLBACK;

事务四大特性

事务四大特性为ACID

• 原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败；
• 一致性(Consistency)：事务完成时，必须所有的数据都保持一致状态；
• 隔离性(Isolation)：数据库系统提供的隔离机制，保证事务不受外部并发操作的影响的独立环境下运行；
• 持久性(Durability)：事务一旦回滚或提交，它对数据库数据的改变是永久的。

并发事务问题及解决

真实场景中同一个数据库需要同时处理多种不同事务，这时候会产生以下问题

问题	描述
脏读	一个事务读到另一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是插入数据时，又发现这行数据已存在

这个时候要设置事务隔离级别来防止这些问题存在（√指的是可能发生）：

隔离级别	说明	脏读	不可重复读	幻读
Read uncommitted	几乎不隔离	√	√	√
Read committed	只能读到已提交数据	×	√	√
Repeatable Read（默认）	同一事务内读到的结果保持一致	×	×	√
Serializable	严格按照顺序执行	×	×	×

• 查看事务隔离级别

SELECT @TRANSACTION_ISOLATION;

• 设置事务隔离级别

SET [SESSION GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE];