MySQL快速复习笔记

MySQL数据库基础

(一) 基础定义

  1. 数据库，英文名称为Database，简称DB。从字面意思来看就是存储数据的仓库；从专业角度解释为存储在计算机磁盘上的有组织、可共享的大量数据的集合。

  2. 数据库分为关系型数据库和非关系型数据库两大类。

名称 描述 命令
数据定义语言 （ DDL ） 数据库、数据表的创建、修改和删除 CREATE、ALTER、DROP
数据操作语言 （ DML ） 数据的增加、修改和删除 INSERT、UPDATE、DELETE
数据查询语言 （ DQL ） 数据的查询 SELECT
数据控制语言 （ DCL ） 用户授权、事务的提交和回滚 GRANT、COMMIT、ROLLBACK

(二) 数据库操作

以lesson举例

2.1创建

CREATE DATABASE IF NOT EXISTS lesson DEFAULT CHARACTER SET GBK COLLATE
GBK_CHINESE_CI;

创建数据库lesson，并指定字符集为 GBK ，排序规则为 GBK_CHINESE_CI

2.2 修改

ALTER DATABASE lesson CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

修改数据库lesson的字符集为 UTF8 ，排序规则为 UTF8_GENERAL_CI

2.3 删除

DROP DATABASE IF EXISTS lesson;

2.4 查看

SHOW DATABASES;

2.5 使用

USE lesson;

(三) 列类型

在 MySQL 中，常用列类型主要分为数值类型、日期时间类型、字符串类型。

tinyint smallint mediumint int bigint float double decimal(m, d)
1字节 2字节 3字节 4字节 8字节 4字节 8字节 m个字节
DATE TIME DATETIME TIMESTAMP YEAR
YYYY-MM-dd HH:mm:ss YY-MM-dd HH:mm:ss YYYY-MM-dd HH:mm:ss YYYY
char [(M)] varchar [(M)] text
固定长字符串，检索快但费空间， 0 <= M <= 255 可变字符串0 <= M <= 65535 文本串
属性 示例
UNSIGNED INT(4) UNSIGNED，表示只能为4位大于等于0的整数
ZEROFILL INT(4) ZEROFILL ，如果给定的值为10，不足的2位由0填充，最终值为0010
NOT NULL VARCHAR (20) NOT NULL，表示该列数据不能为空值
DEFAULT INT(4) DEFAULT 0，表示该列不赋值时默认为0
AUTO_INCREMENT INT(11) AUTO_INCREMENT NOT NULLPRIMARY KEY。第一次为该列中插入值时为1，第二次为2

(四)数据表操作

4.1 类型

MySQL 中的数据表类型有许多，如 MyISAM 、 InnoDB 、 HEAP 、 BOB 、 CSV 等。其中最常用的就是MyISAM 和 InnoDB

InnoDB支持事务处理,数据行锁定,外键约束,不支持外键约束,MyISAM相反

InnoDB表空间大小较大,约为MyISAM2倍

事务：涉及的所有操作是一个整体，要么都执行，要么都不执行。

数据行锁定：一行数据，当一个用户在修改该数据时，可以直接将该条数据锁定。

如何选择

当涉及的业务操作以查询居多，修改和删除较少时，可以使用MyISAM。当涉及的业务操作经常会有修改和删除操作时，使用InnoDB。

4.2创建

CREATE TABLE [IF NOT EXISTS] 数据表名称(
	字段名1 列类型(长度) [修饰属性] [键/索引] [注释],
	字段名2 列类型(长度) [修饰属性] [键/索引] [注释],
	字段名3 列类型(长度) [修饰属性] [键/索引] [注释],
	......
	字段名n 列类型(长度) [修饰属性] [键/索引] [注释]
) [ENGINE = 数据表类型][CHARSET=字符集编码] [COMMENT=注释];

示例：创建学生表，表中有字段学号、姓名、性别、年龄和成绩

CREATE TABLE IF NOT EXISTS student(
	`number` VARCHAR(30) NOT NULL PRIMARY KEY COMMENT '学号，主键',
	name VARCHAR(30) NOT NULL COMMENT '姓名',
	sex TINYINT(1) UNSIGNED DEFAULT 0 COMMENT '性别：0-男 1-女 2-其他',
	age TINYINT(3) UNSIGNED DEFAULT 0 COMMENT '年龄',
	score DOUBLE(5, 2) UNSIGNED COMMENT '成绩'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='学生表';

4.4 修改

  • 修改表名
ALTER TABLE 表名 RENAME AS 新表名;
  • 增加字段
ALTER TABLE 表名 ADD 字段名 列类型(长度) [修饰属性] [键/索引] [注释];
  • 查看表结构
DESC 表名; -- 查看表结构
  • 修改字段
-- MODIFY 只能修改字段的修饰属性
ALTER TABLE 表名 MODIFY 字段名 列类型(长度) [修饰属性] [键/索引] [注释];
-- CHANGE 可以修改字段的名字以及修饰属性
ALTER TABLE 表名 CHANGE 字段名 新字段名 列类型(长度) [修饰属性] [键/索引] [注释];

示例： 将 stu 表中的 sex 字段的类型设置为 VARCHAR ，长度为2，默认值为'男'，注释为 "性别，男，女，其他

ALTER TABLE stu MODIFY sex VARCHAR(2) DEFAULT '男' COMMENT '性别：男，女，其他';

示例：将 stu 表中 phone 字段修改为 mobile ，属性保持不变

ALTER TABLE stu CHANGE phone mobile VARCHAR(11) NOT NULL COMMENT '联系电话';
  • 删除字段
ALTER TABLE 表名 DROP 字段名;
  • 删除数据表
DROP TABLE [IF EXISTS] 表名;

(五)MySQL数据的增删改查

5.1 DML

DML全称为Data Manipulation Language，表示数据操作语言。主要体现于对表数据的增删改操作,仅包括INSERT、UPDATE和DELEETE语句。

INSERT

-- 需要注意，VALUES后的字段值必须与表名后的字段名一一对应
INSERT INTO 表名(字段名1, 字段名2, ..., 字段名n) VALUES(字段值1, 字段值2, ..., 字段值n);
-- 需要注意，VALUES后的字段值必须与创建表时的字段顺序保持一一对应
INSERT INTO 表名 VALUES(字段值1, 字段值2, ..., 字段值n);

-- 一次性插入多条数据
INSERT INTO 表名(字段名1, 字段名2, ..., 字段名n) VALUES(字段值1, 字段值2, ..., 字段值n),(字段值1, 字段值2, ..., 字段值n), ... , (字段值1, 字段值2, ..., 字段值n);
INSERT INTO 表名 VALUES(字段值1, 字段值2, ..., 字段值n), (字段值1, 字段值2, ..., 字段值n), ..., (字段值1, 字段值2, ..., 字段值n);

eg:

INSERT INTO course(`number`, name, score, `time`) VALUES (1, 'Java基础', 4, 40);
INSERT INTO course VALUES (2, '数据库', 3, 20);
INSERT INTO course(`number`, score, name, `time`) VALUES (3, 5, 'Jsp', 40);
INSERT INTO course(`number`, name, score, `time`) VALUES (4, 'Spring', 4, 5),(5,'Spring Mvc', 2, 5);
INSERT INTO course VALUES (6, 'SSM', 2, 3), (7, 'Spring Boot', 2, 2);

UPDATE

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

在Java中，条件的表示通常都是使用关系运算符来表示，在SQL语句中也是一样，使用 >, <, >=, <=, != 来表示。

在Java中，条件之间的衔接通常都是使用逻辑运算符来表示，在SQL语句中也是一样，但通常使用AND来表示逻辑与(&&)，使用OR来表示逻辑或(||)

WHERE time > 20 && time < 40; <=> WHERE time > 20 and time <40;

-- 将数据库的学分更改为4，学时更改为15
UPDATE course SET score=4, `time`=15 WHERE name='数据库';

DELETE

DELETE FROM 表名 [WHERE 删除条件];
-- 删除课程表中课程编号为1的数据
DELETE FROM course WHERE `number`=1;

TRUNCATE

-- 清空表中数据
TRUNCATE [TABLE] 表名;
-- 清空课程表数据
TRUNCATE course;

DELETE与TRUNCATE区别

  • DELETE语句根据条件删除表中数据，而TRUNCATE语句则是将表中数据全部清空；如果DELETE语句要删除表中所有数据，那么在效率上要低。
  • 如果表中有自增长列，TRUNCATE语句会重置自增长的计数器，但DELETE语句不会。
  • TRUNCATE语句执行后，数据无法恢复，而DELETE语句执行后，可以使用事务回滚进行恢复。

5.2 DQL

DQL全称是Data Query Language，表示数据查询语言。体现在数据的查询操作上，因此，DQL仅包括SELECT语句。

SELECT

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]FROM表名 WHERE 查询条件
-- 从课程表中查询课程编号小于5的课程名称
SELECT name FROM course WHERE `number`<5;
-- 从课程表中查询课程名称为"Java基础"的学分和学时
SELECT score, `time` FROM course WHERE name='Java基础';

ALL表示查询所有满足条件的记录，可以省略；DISTINCT表示去掉查询结果中重复的记录

AS可以给数据列、数据表取一个别名

5.3比较操作符

IS NULL IS NOT NULL BETWEEN...AND LIKE'%匹配内容% IN(值1，值2，...， 值n)
如果字段的值为NULL，则条件满足 如果字段的值不为NULL，则条件满足 如果字段的值在最小值与最大值之间（能够取到最小值和最大值），则条件满足 如果字段值包含有匹配内容，则条件满足 如果字段值在值1,值2, ...，值n中，则条件满足

eg:

-- 从课程表查询课程名为NULL的课程信息
SELECT * FROM course WHERE name IS NULL;

-- 从课程表查询课程名不为NULL的课程信息
SELECT * FROM course WHERE name IS NOT NULL;

-- 从课程表查询学分在2~4之间的课程信息
SELECT * FROM course WHERE score BETWEEN 2 AND 4;

-- 从课程表查询课程名包含"V"的课程信息
SELECT * FROM course WHERE name LIKE '%v%';

-- 从课程表查询课程名以"J"开头的课程信息
SELECT * FROM course WHERE name LIKE 'J%';

-- 从课程表查询课程名以"p"结尾的课程信息
SELECT * FROM course WHERE name LIKE '%p';

-- 从课程表查询课程编号为1,3,5的课程信息
SELECT * FROM course WHERE `number` IN (1, 3, 5);

5.4 分组

数据表准备：新建学生表student，包含字段学号（no），类型为长整数，长度为20，是主键，自增长，非空；姓名（name），类型为字符串，长度为20，非空；性别（sex），类型为字符串，长度为2，默认值为"男"；年龄（age），类型为整数，长度为3，默认值为0；成绩（score），类型为浮点数，长度为5，小数点后面保留2位有效数字

DROP TABLE IF EXISTS student;
CREATE TABLE student(
	no BIGINT(20) AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '学号，主键',
	name VARCHAR(20) NOT NULL COMMENT '姓名',
	sex VARCHAR(2) DEFAULT '男' COMMENT '性别',
	age INT(3) DEFAULT 0 COMMENT '年龄',
 	score DOUBLE(5, 2) COMMENT '成绩'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='学生表';

分组查询

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]FROM 表名 WHERE 查询条件 GROUP BY 字段名1，字段名2,..., 字段名n

-- 从学生表查询成绩在80分以上的学生信息并按性别分组
SELECT * FROM student WHERE score>80 GROUP BY sex;

-- 从学生表查询成绩在60~80之间的学生信息并按性别和年龄分组
SELECT * FROM student WHERE score BETWEEN 60 AND 80 GROUP BY sex, age;

聚合函数

  • COUNT() ：统计满足条件的数据总条数

    -- 从学生表查询成绩在80分以上的学生人数
SELECT COUNT(*) total FROM student WHERE score>80;

  • SUM()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的总和

    -- 从学生表查询不及格的学生人数和总成绩
SELECT COUNT(*) totalCount, SUM(score) totalScore FROM student WHERE score<60;

  • AVG()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的平均值

  • MAX()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最大值

  • MIN()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最小值

eg:

SELECT sex, AVG(score) avgScore FROM student GROUP BY sex;
SELECT MAX(age) FROM student;
SELECT MIN(score) FROM student;

5.5分组查询结果筛选

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]FROM 表名 WHERE 查询条件 GROUP BY 字段名1，字段名2,..., 字段名n HAVING 筛选条件
eg:从学生表查询年龄在20~30之间的学生信息并按性别分组，找出组内平均分在74分以上的组
SELECT * FROM student WHERE age BETWEEN 20 AND 30 GROUP BY sex HAVING avg(score)>74;

5.6排序

ORDER BY 必须位于WHERE 条件之后。

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1,..., 字段名n AS 别名n]
FROM 表名 WHERE 查询条件 ORDER BY 字段名1 ASC|DESC，字段名2 ASC|DESC,..., 字段名n
ASC|DESC

eg:从学生表查询年龄在18~30岁之间的学生信息并按成绩从高到低列，如果成绩相同，则按年龄从小到大排列

SELECT * FROM student WHERE age BETWEEN 18 AND 30 ORDER BY score DESC, age ASC;

5.7分页

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]FROM 表名 WHERE 查询条件 LIMIT 偏移量, 查询条数

LIMIT第一个参数表示偏移量，也就是跳过的行数。

第二个参数表示查询返回的最大行数，可能没有给定的数量那么多行。

eg:从学生表分页查询成绩及格的学生信息，每页显示3条，查询第2页学生信息

SELECT * FROM student WHERE score>=60 LIMIT 3, 3;

注:如果一个查询中包含分组、排序和分页，那么它们之间必须按照分组->排序->分页的先后顺序排列。

(六)常用函数

6.1数学:

image-20261025031702251

6.2字符串:

image-20261025031720436

6.3日期:

image-20261025031736520

6.4条件判断函数

if函数:IF(条件, 表达式1,表达式2) IFNULL(字段, 表达式)

eg:将学生成绩展示为及格和不及格

eg:将未参加考试的学生成绩展示为缺考

SELECT id,stu_name,course, IF(score>=60, '及格','不及格') score FROM score;
SELECT id,stu_name,course, IFNULL(score, '缺考') score FROM score;

CASE...THEN 语句(相当于if...else函数)

CASE WHEN 条件1 THEN 表达式1 [WHEN 条件2 THEN 表达式2 ...] ELSE 表达式n END

eg:查询Java成绩

SELECT (CASE WHEN (course = 'Java') THEN score ELSE 0 END) Java FROM score;

CASE ... WHEN语句(相当于switch函数)

CASE 表达式 WHEN 值1 THEN 表达式1 [WHEN 值2 THEN 表达式2 ...] ELSE 表达式n END

eg:查询Java成绩

SELECT (CASE course WHEN 'Java' THEN score ELSE 0 END) Java FROM score;

6.5其他函数

数字格式化函数

FORMAT(X,D)，将数字X格式化，将X保留到小数点后D位，截断时要进行四舍五入。

系统信息函数

image-20261025032921670

(七)多表查询

7.1表的关系

表是用来描述实体信息，数据库通过主外键关联关系来体现表与表之间的关联关系：在一张表中添加一个字段引用另一张表的主键，该字段称为外键，这种关联关系称为主外键关联关系。

示例：创建班级表cls和学生表student，student表中的cls_number字段与cls表的number主键相关联

CREATE TABLE cls(
	number INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '班级编号，主键',
	name VARCHAR(20) NOT NULL COMMENT '班级名称',
	grade VARCHAR(20) NOT NULL COMMENT '年级'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='班级表';

CREATE TABLE student(
	number BIGINT(20) AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '学号，主键',
	name VARCHAR(20) NOT NULL COMMENT '姓名',
	sex VARCHAR(2) DEFAULT '男' COMMENT '性别',
	age TINYINT(3) DEFAULT 0 COMMENT '年龄',
	cls_number INT(11) NOT NULL COMMENT '所属班级',
	FOREIGN KEY(cls_number) REFERENCES cls(number)
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='学生表';

7.2 约束

-- 主键约束：保证数据的唯一性
ALTER TABLE 表名 ADD PRIMARY KEY(字段名1, 字段名2, ..., 字段名n);
ALTER TABLE 表名 DROP PRIMARY KEY;
-- 外键约束
ALTER TABLE 表名1 ADD CONSTRAINT 外键名称 FOREIGN KEY(表名1的字段名) REFERENCES 表名2(表名2的字段名);
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
-- 唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名称 UNIQUE(字段名1, 字段名2, ..., 字段名n);
ALTER TABLE 表名 DROP KEY 约束名称;
-- 非空约束
ALTER TABLE 表名 MODIFY 字段名 列类型 NOT NULL;
ALTER TABLE 表名 MODIFY 字段名 列类型 NULL;
-- 默认值约束
ALTER TABLE 表名 ALTER 字段名 SET DEFAULT 默认值;
ALTER TABLE 表名 ALTER 字段名 DROP DEFAULT;
-- 自增约束
ALTER TABLE 表名 MODIFY 字段名 列类型 AUTO_INCREMENT;
ALTER TABLE 表名 MODIFY 字段名 列类型;

7.3 索引

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的存储结构，可以类比书籍的目录来理解。

作用：保证数据的准确性、提高检索速度、提高系统性能

类型

类型 说明
唯一索引（UNIQUE） 不可以出现相同的值，可以有NULL值
普通索引（INDEX） 允许出现相同的索引内容
主键索引（PRIMARY KEY） 不允许出现相同的值
全文索引（FULLTEXT） 可以针对值中的某个单词，但效率不高
组合索引 将多个字段建到一个索引里，列值的组合必须唯一

创建、查看、删除

-- 创建索引
ALTER TABLE 表名 ADD INDEX 索引名称 (字段名1, 字段名2, ..., 字段名n);
-- 创建全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名称 (字段名1, 字段名2, ..., 字段名n);
-- 查看索引
SHOW INDEX FROM 表名;
-- 删除索引
ALTER TABLE 表名 DROP INDEX 索引名称;

使用注意事项

  • 索引提高查询速度，但会降低更新表的速度（INSERT、UPDATE、DELETE时还要维护索引文件）
  • 索引文件占用磁盘空间，索引创建过多反而降低数据库性能，索引要建立在经常进行查询操作的字段上
  • 不要在列上进行运算（包括函数运算），这会忽略索引的使用
  • like '%查询内容%'不会使用索引，而like '查询内容%'可以使用索引
  • 避免使用IS NULL、NOT IN、<>、!=、OR操作，这些操作会忽略索引而进行全表扫描

7.4 连接查询

笛卡尔积：表示两个集合相乘的结果。每一张表可以看做是一个数据的集合，多表关联时，这些表中的数据就会形成笛卡尔积。

内连接：相当于在笛卡尔积的基础上加上了连接条件。当没有连接条件时，内连接上升为笛卡尔积。

SELECT 字段名1, 字段名2, ..., 字段名n FROM 表1 [INNER] JOIN 表2 [ON 连接条件];
SELECT 字段名1, 字段名2, ..., 字段名n FROM 表1, 表2 [WHERE 关联条件 AND 查询条件];

eg:

-- 两种写法等价，统计学生表与成绩表关联的记录数
SELECT COUNT(*) FROM stu INNER JOIN score ON stu.id=score.stu_id;
SELECT COUNT(*) FROM stu, score WHERE stu.id=score.stu_id;

外连接：涉及到主表和从表，要查询的信息主要来自于哪张表，哪张表就是主表。外连接查询的结果为主表中所有的记录，如果从表中有匹配的则显示匹配的值，没有匹配的则显示null。

外连接查询的结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录

  • 左外连接：使用LEFT JOIN关键字，LEFT JOIN左边的是主表
  • 右外连接：使用RIGHT JOIN关键字，RIGHT JOIN右边的是主表
SELECT 字段名1, 字段名2, ..., 字段名n FROM 主表 LEFT JOIN 从表 [ON 连接条件];
SELECT 字段名1, 字段名2, ..., 字段名n FROM 从表 RIGHT JOIN 主表 [ON 连接条件];

eg:

-- 查询没有成绩的学生信息
SELECT * FROM stu a LEFT JOIN score b ON a.id=b.stu_id WHERE score IS NULL;

7.5 子查询

子查询就是嵌套在其他查询中的查询。子查询出现的位置只有3种情况：

位置 执行时机
SELECT...FROM之间 查询结果出来之后执行
FROM...WHERE之间 一开始就先执行
WHERE之后 筛选数据时执行

eg:

-- 在SELECT...FROM之间：查询stu表所有学生信息，并将性别按男、女、其他展示
SELECT
	id,
	`name`,
	(SELECT text FROM dict WHERE type='sex' AND value=sex) sex,
	birthday,
	class
FROM stu;

-- 在FROM...WHERE之间：查询年龄与Java成绩都与汤辰宇的年龄与Java成绩相同的学生信息
SELECT c.*, d.* FROM stu c
INNER JOIN score d ON c.id=d.stu_id
INNER JOIN
	(SELECT
		TIMESTAMPDIFF(YEAR, a.birthday, NOW()) age,
		b.score
	FROM stu a INNER JOIN score b ON a.id=b.stu_id
	WHERE a.name='汤辰宇' AND b.course='Java') e
ON TIMESTAMPDIFF(YEAR, c.birthday, NOW())=e.age AND d.score=e.score
WHERE d.course='Java';

-- 在WHERE之后：查询Java成绩最高的学生信息
SELECT a.*, b.* FROM stu a INNER JOIN score b ON a.id=b.stu_id
WHERE b.score=(SELECT MAX(score) FROM score WHERE course='Java')
AND b.course='Java';

(八)存储过程、函数、触发器和视图

8.1 变量

变量分为四种类型：局部变量、用户变量、会话变量和全局变量。其中局部变量和用户变量使用较多。

类型 说明
全局变量 影响服务器整体操作，修改需管理员权限，作用域为服务器的整个生命周期
会话变量 服务器为每个连接的客户端维护的变量，作用域仅限于当前连接
用户变量 不用提前声明，直接用"@变量名"使用，作用域为当前连接
局部变量 只能用在BEGIN/END语句块中，用DECLARE声明 
-- 全局变量：设置时GLOBAL不能省略
SHOW GLOBAL VARIABLES;
SET GLOBAL sql_warnings = ON;
SELECT @@GLOBAL.sql_warnings;

-- 会话变量：SESSION可省略（默认缺省为SESSION），也可用LOCAL替代
SHOW SESSION VARIABLES;
SET auto_increment_increment = 3;
SELECT @@auto_increment_increment;

-- 用户变量：SET时可用"="或":="赋值；SELECT时必须用":="赋值
SET @age = 19;
SELECT @age := age FROM stu WHERE `name` = '张华';
SELECT age INTO @age FROM stu WHERE `name` = '张华';

-- 局部变量
DECLARE age INT(3) DEFAULT 0;
SET age = 10;

8.2 存储过程

存储过程是一组为了完成特定功能而存储在数据库中的SQL语句集，一次编译后永久有效。

优点：运行速度快（创建时已解析和优化，执行后保留在内存中可直接调用）；减少网络传输（直接在数据库服务器上执行）；增强安全性（防止SQL被截获、篡改）

-- 声明分隔符
[DELIMITER $$]
CREATE PROCEDURE 存储过程名称([IN | OUT | INOUT] 参数名1 数据类型, ..., [IN | OUT | INOUT] 参数名n 数据类型)
BEGIN
	-- SQL语句集
END[$$]
-- 还原分隔符
[DELIMITER ;]
-- 调用存储过程
CALL 存储过程名(参数1, 参数2, ...);

示例：使用存储过程完成银行转账业务

CREATE PROCEDURE transfer(IN transferFrom BIGINT, IN transferTo BIGINT, IN transferMoney BIGINT(20))
BEGIN
	UPDATE account SET balance = balance - transferMoney WHERE account = transferFrom;
	UPDATE account SET balance = balance + transferMoney WHERE account = transferTo;
END

CALL transfer(123456, 123457, 2000);

注:若转账账户已将钱转出，而给目标账户增加余额时出现异常或账号输入错误，就会造成数据不一致，此时需要使用事务。

8.3 事务

事务（Transaction）是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。

事务的特性（ACID）

特性 说明
原子性（Atomicity） 事务的各元素是一个整体，要么都执行，要么都不执行
一致性（Consistency） 事务完成时必须保证所有数据保持一致状态，如转账前后总金额不变
隔离性（Isolation） 多个并发事务彼此独立，互不影响
持久性（Durability） 已提交事务对数据库的改变不会丢失，即使数据库出现故障

示例：使用事务解决银行转账问题

DROP PROCEDURE IF EXISTS transfer;
CREATE PROCEDURE transfer(IN transferFrom BIGINT, IN transferTo BIGINT, IN transferMoney BIGINT(20), OUT result TINYINT(1))
BEGIN
	-- 声明SQLEXCEPTION处理器，出现异常时将result更改为0；CONTINUE表示异常后继续执行，EXIT表示直接退出
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result = 0;
	-- 开启事务
	START TRANSACTION;
	UPDATE account SET balance = balance - transferMoney WHERE account = transferFrom AND balance >= transferMoney;
	SET result = ROW_COUNT();
	IF result = 1 THEN
		UPDATE account SET balance = balance + transferMoney WHERE account = transferTo;
		SET result = ROW_COUNT();
	END IF;
	-- result为1表示所有操作都成功，提交事务；否则回滚，数据恢复到更改之前的状态
	IF result = 1 THEN COMMIT;
	ELSE ROLLBACK;
	END IF;
END

CALL transfer(123456, 123457, 2000, @rs);
SELECT @rs;

8.4 自定义函数

函数是一组为了完成特定功能而存储在数据库中的SQL语句集，一次编译后永久有效。当内置函数不能满足开发需要时，可以自定义函数。

CREATE FUNCTION 函数名称(参数名1 数据类型, ..., 参数名n 数据类型)
RETURNS 数据类型
-- 函数特征：DETERMINISTIC不确定的 | NO SQL没有SQL语句 | READS SQL DATA只读取数据
--          MODIFIES SQL DATA要修改数据 | CONTAINS SQL包含SQL语句
BEGIN
	RETURN 结果;
END
-- 调用函数
SELECT 函数名(参数);

eg:

-- 求score表中的成绩最大差值
CREATE FUNCTION getMaxDiff()
RETURNS DOUBLE(5, 2)
DETERMINISTIC
BEGIN
	RETURN (SELECT MAX(score) - MIN(score) FROM score);
END
SELECT getMaxDiff();

循环结构

WHILE 循环条件 DO
	-- SQL语句集
END WHILE;

REPEAT
	-- SQL语句集
UNTIL 循环终止条件 END REPEAT;

标号: LOOP
	-- SQL语句集
	IF 循环终止条件 THEN LEAVE 标号;
	END IF;
END LOOP;

eg:求0~给定整数的累加和

DROP FUNCTION IF EXISTS getTotal;
CREATE FUNCTION getTotal(maxNum INT(11))
RETURNS INT(11)
NO SQL
BEGIN
	DECLARE i INT(11) DEFAULT 0;
	DECLARE total INT(11) DEFAULT 0;
	WHILE i < maxNum DO
		SET total = total + i;
		SET i = i + 1;
	END WHILE;
	RETURN total;
END
SELECT getTotal(100);

8.5 触发器

触发器（trigger）是用来保证数据完整性的一种方法，由事件来触发，比如当对一个表进行增删改操作时就会被激活执行。经常用于加强数据的完整性约束和业务规则。

-- 触发时机为BEFORE或者AFTER，触发事件为INSERT、UPDATE或者DELETE
CREATE TRIGGER 触发器名称 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 FOR EACH ROW
BEGIN
	-- 执行的SQL操作
END

DROP TRIGGER [IF EXISTS] 触发器名称;

触发器类型

触发器类型 NEW和OLD的使用
INSERT触发器 NEW表示将要或者已经新增的数据
UPDATE触发器 OLD表示修改前的数据，NEW表示修改后的数据
DELETE触发器 OLD表示将要或者已经删除的数据

eg:商品表goods和订单表order，订单的生成、取消、购买数量的更新都意味着商品数量的变动

-- 新增订单后，商品数量减少
CREATE TRIGGER addOrder AFTER INSERT ON `order` FOR EACH ROW
BEGIN
	UPDATE goods SET number = number - NEW.sale_count WHERE id = NEW.goods_id;
END

-- 取消订单后，商品数量增加
CREATE TRIGGER deleteOrder AFTER DELETE ON `order` FOR EACH ROW
BEGIN
	UPDATE goods SET number = number + OLD.sale_count WHERE id = OLD.goods_id;
END

-- 更新订单购买数量后，商品数量对应变动
CREATE TRIGGER updateOrder AFTER UPDATE ON `order` FOR EACH ROW
BEGIN
	DECLARE changeNum INT(11) DEFAULT 0;
	SET changeNum = NEW.sale_count - OLD.sale_count;
	UPDATE goods SET number = number - changeNum WHERE id = OLD.goods_id;
END

8.6 视图

视图是一张虚拟表，本身并不存储数据，当SQL操作视图时所有数据都是从其他表中查出来的。

-- 创建视图
CREATE VIEW 视图名称 AS SELECT 列1[,列2,...] FROM 表名 WHERE 条件;
-- 更新视图
CREATE OR REPLACE VIEW 视图名称 AS SELECT 列1[,列2,...] FROM 表名 WHERE 条件;
-- 删除视图
DROP VIEW IF EXISTS 视图名称;

作用

  • 定制用户数据，聚焦特定的数据
  • 简化数据操作：进行关联查询时涉及的表可能很多、SQL很长，如果这个动作频繁发生，可以创建视图
  • 提高安全性能：过滤掉隐私字段，如用户密码

eg:创建视图过滤掉用户表中的密码字段

CREATE OR REPLACE VIEW userInfo AS
SELECT username, salt, failure_times, last_log_time FROM `user`;

SELECT username, salt FROM userInfo;

注:视图并不能提升查询速度，只是方便了业务开发，但同时也加大了数据库服务器的压力，因此需要合理地使用视图。

(九)数据库设计

9.1 设计数据库

  • 实体：软件开发过程中所涉及到的事物，通常是一类数据对象的个体
  • 数据库设计：将实体与实体之间的关系进行规划和结构化的过程
  • 为什么要设计：设计不当会造成数据冗余、修改复杂、操作数据异常等问题；好的设计可以减少不必要的数据冗余，通过合理的数据规划提高系统的性能
  • 设计步骤：收集信息 → 标识实体（实体一般是名词，每个实体只描述一件事情） → 标识实体的详细属性 → 标识实体之间的关系

9.2 ER图

ER = Entity Relational（实体关系），ER图就是实体关系图。

图形 表示
矩形 实体，一般是名词
椭圆 实体属性，一般是名词
菱形 实体关系，一般是动词

eg:学生（学号，姓名，性别，年龄）--属于--班级（班级编号，班级名称）

9.3 数据库模型图

实体关系的描述称为关系模式，关系模式通常使用二维表的形式表示。

eg:学生（学号，姓名，性别，年龄，所属班级）、班级（班级编号，班级名称）

将关系模式转换为数据库模型图的步骤：

  1. 将各实体转换为对应的表，将各属性转换为各表对应的列
  2. 标识每个表的主键列
  3. 在表之间建立主外键，体现实体关系

9.4 数据库三大范式

范式 要求
第一范式 最基本的范式，确保每列保持原子性，每列不可再分
第二范式 在第一范式的基础上，每张表的属性完全依赖于主键，每张表只描述一件事情
第三范式 在第二范式的基础上，确保每列都直接依赖于主键，不能存在传递依赖

eg:地址"四川省成都市"按省、市拆分为两列满足第一范式；学生表中重复的班级信息拆出班级表满足第二范式；学生表中间接依赖主键的专业学费拆出专业表满足第三范式。

注:在实际开发过程中，为了满足性能的需要，数据库的设计可能会打破三大范式的约束。以空间换时间：当数据量越来越大、查询效率下降时，可能会在表中增加冗余字段来提升查询效率。

(十)JDBC

10.1 概念

JDBC（Java Database Connection）是Java数据库连接技术的简称，提供连接数据库的能力。Java在java.sql包中提供了统一的接口，由各大数据库厂商去实现。

10.2 JDBC API

接口/类 说明
Driver 数据库厂商提供的JDBC驱动包中必须包含该接口的实现，包含连接数据库的功能
DriverManager 管理各厂商的JDBC驱动，主要负责获取数据库连接对象Connection
Connection 连接接口，主要提供与数据库的交互功能
Statement SQL语句执行接口，主要提供执行SQL语句的功能
ResultSet 查询结果集接口，主要提供查询结果的获取功能 
// Connection 常用方法
Statement createStatement();                     // 创建SQL语句执行对象
PreparedStatement prepareStatement(String sql);  // 创建预处理SQL语句执行对象
void setAutoCommit(boolean autoCommit);          // 设置该连接上的操作是否自动提交
void commit();                                   // 提交事务
void rollback();                                 // 回滚事务
void close();                                    // 关闭连接
// 事务隔离级别：TRANSACTION_NONE=0 不支持事务，READ_UNCOMMITTED=1 读取未提交，
// READ_COMMITTED=2 读取已提交，REPEATABLE_READ=4 可重复读，SERIALIZABLE=8 串行化

// Statement 常用方法
ResultSet executeQuery(String sql);  // 执行查询，得到一个结果集
int executeUpdate(String sql);       // 执行更新，得到受影响的行数
void close();                        // 关闭SQL语句执行器

// ResultSet 常用方法
boolean next();              // 光标向前移动一行，如果存在数据则返回true，否则返回false
String getString(int columnIndex | String columnName);  // 获取指定列的值，还有getInt、getDouble、getObject等
ResultSetMetaData getMetaData();  // 获取结果集元数据：列名称、列数量、列别名等

10.3 JDBC操作步骤

  1. 引入驱动包：将 mysql-connector-java.jar 引入工程中
  2. 加载驱动
  3. 获取连接
  4. 创建SQL语句执行器
  5. 执行SQL语句
  6. 释放资源
// 2.加载驱动（MySQL 5.0为com.mysql.jdbc.Driver，MySQL 8.0如下）
Class.forName("com.mysql.cj.jdbc.Driver");
// 3.获取连接
Connection conn = DriverManager.getConnection(url, username, password);
// 4.创建SQL语句执行器
Statement statement = conn.createStatement();
// 5.执行SQL语句
ResultSet rs = statement.executeQuery(sql);      // 查询
int affectedRows = statement.executeUpdate(sql); // 更新
// 6.释放资源
rs.close();
statement.close();
conn.close();

示例：

String url = "jdbc:mysql://localhost:3306/lesson?serverTimezone=Asia/Shanghai";
String username = "root";
String password = "root";
try {
	// 加载驱动
	Class.forName("com.mysql.cj.jdbc.Driver");
	// 获取连接
	Connection conn = DriverManager.getConnection(url, username, password);
	// 在连接上创建SQL语句执行器
	Statement s = conn.createStatement();
	// 使用执行器执行查询，得到一个结果集，遍历时通过列名称或列索引获取列的值
	ResultSet rs = s.executeQuery("SELECT account,balance,state FROM account");
	while (rs.next()) {
		String account = rs.getString("account");
		double balance = rs.getDouble(2);
		int state = rs.getInt("state");
	}
	// 执行更新时，返回的都是受影响的行数
	int affectedRows = s.executeUpdate("UPDATE account SET balance = balance + 1000 WHERE account=123457");
	// 释放资源
	rs.close();
	s.close();
	conn.close();
} catch (ClassNotFoundException e) {
	e.printStackTrace();
} catch (SQLException throwables) {
	throwables.printStackTrace();
}

10.4 预处理SQL

拼接SQL时，如果用户输入 张华' or 1='1，SQL语句会变为：

SELECT id, name, sex, age FROM stu WHERE name='张华' or 1='1'

查询结果发生改变，这样的情况被称为SQL注入。为防止SQL注入，Java提供了PreparedStatement接口对SQL进行预处理，它是Statement的子接口。使用时SQL语句中的参数一律使用?号占位，然后通过setXxx()方法对占位的?号进行替换，将参数作为一个整体进行查询。

String sql = "SELECT id, name, sex, age FROM stu WHERE name=?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, name);   // 还有setInt、setLong、setDouble、setObject等
ResultSet rs = ps.executeQuery();

10.5 反射

JVM加载类的过程相当于对class文件内容进行解析，解析时需要找到类的共有特征并存储起来，即java.lang.Class对象（包含类名、包名、构造方法、字段、方法、接口、父类等）。通过Class对象反向推到类的内容再进行操作，这个过程就称为反射。

获取Class对象

Class<Student> c1 = Student.class;                  // 类名.class
Class<? extends Student> c2 = stu.getClass();       // 对象名.getClass()
Class<? super Student> c3 = c1.getSuperclass();     // 获取父类
Class c4 = Class.forName("com.cyx.jdbc.reflection.Student"); // 类的全限定名=包名+"."+类名
Class c5 = Integer.TYPE;                            // 包装类.TYPE

Class类常用方法

Field[] getFields();                 // 获取类中使用public修饰的字段
Field[] getDeclaredFields();         // 获取类中定义的所有字段
Field getDeclaredField(String name); // 通过给定的字段名获取类中定义的字段
Method[] getDeclaredMethods();       // 获取类中定义的所有方法
Method getDeclaredMethod(String name, Class<?>... parameterTypes); // 按方法名和参数类型获取方法
Constructor<?>[] getDeclaredConstructors();  // 获取类中定义的所有构造方法
Constructor<T> getDeclaredConstructor(Class<?>... parameterTypes);
String getName();                    // 获取类的全限定名
Package getPackage();                // 获取类所在的包
T newInstance();                     // 通过类的无参构造创建一个实例
// Field、Method、Constructor的共同父类AccessibleObject：
void setAccessible(boolean flag);    // 修改访问权限

示例：通过反射创建对象、为私有字段赋值、调用getter/setter

Class<Student> clazz = Student.class;
Constructor<? extends Student> c = clazz.getDeclaredConstructor();
c.setAccessible(true);           // Student类中的无参构造方法是私有的，需要先修改访问权限
Student s = c.newInstance();
Field nameField = clazz.getDeclaredField("name");
nameField.setAccessible(true);
nameField.set(s, "李四");        // 给指定对象中的该字段赋值

// 按字段名拼接getter方法名：get name => "get" + N + ame
String fieldName = nameField.getName();
String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Method m = clazz.getDeclaredMethod(methodName);
m.setAccessible(true);
String name = (String) m.invoke(s);

10.6 反射与数据库

数据库查询出的每一条数据基本上都会封装为一个对象，数据库中的每一个字段值都会存储在对象相应的属性中。如果查询结果的每一个字段都与对象中的属性名保持一致，就可以使用反射完成万能查询（可用列别名对齐，如 agent_id agentId）。

JdbcUtil工具类核心代码：

public class JdbcUtil {
	private static final String url = "jdbc:mysql://localhost:3306/lesson?serverTimezone=Asia/Shanghai";
	private static final String username = "root";
	private static final String password = "root";
	static {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");  // 静态代码块中加载驱动
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	// 万能更新：INSERT、UPDATE、DELETE
	public static int update(String sql, Object...params){
		Connection conn = null;
		PreparedStatement ps = null;
		int result = 0;
		try {
			conn = DriverManager.getConnection(url, username, password);
			ps = createPreparedStatement(conn, sql, params);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(ps, conn);
		}
		return result;
	}

	// 为占位符设置参数
	private static PreparedStatement createPreparedStatement(Connection conn, String sql, Object...params) throws SQLException {
		PreparedStatement ps = conn.prepareStatement(sql);
		if(params != null && params.length > 0){
			for(int i = 0; i < params.length; i++){
				ps.setObject(i + 1, params[i]);
			}
		}
		return ps;
	}

	// 万能查询：通过反射将结果集封装为对象列表
	public static <T> List<T> query(String sql, Class<T> clazz, Object...params){
		List<T> dataList = new ArrayList<>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DriverManager.getConnection(url, username, password);
			ps = createPreparedStatement(conn, sql, params);
			rs = ps.executeQuery();
			while (rs.next()){
				T t = createInstance(clazz, rs);
				dataList.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs, ps, conn);
		}
		return dataList;
	}

	// 反射创建对象：调用setter为字段赋值
	private static <T> T createInstance(Class<T> clazz, ResultSet rs) throws Exception{
		Constructor<T> c = clazz.getConstructor();   // 获取无参构造
		T t = c.newInstance();                       // 创建对象
		Field[] fields = clazz.getDeclaredFields();  // 获取类中定义的字段
		for(Field field : fields){
			String fieldName = field.getName();
			// setId => "set" + I + d
			String methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
			Method m = clazz.getDeclaredMethod(methodName, field.getType());
			try {
				Object value = rs.getObject(fieldName, field.getType());
				m.invoke(t, value);
			} catch (Exception e){}
		}
		return t;
	}

	// 关闭连接、执行器、结果集
	private static void close(AutoCloseable... closeables){
		if(closeables != null && closeables.length > 0){
			for(AutoCloseable ac : closeables){
				if(ac != null){
					try {
						ac.close();
					} catch (Exception e) {}
				}
			}
		}
	}
}

(十一)分层开发

11.1 三层结构

生活中的分层也可以应用于软件开发中，软件开发分层主要分为三层：

层次 包名 职责
界面层，又称控制层（controller） controller 与用户进行交互，主要负责数据采集和展示
业务逻辑层（service） service 处理功能模块的业务逻辑，以及界面层和数据访问层的数据流转
数据访问层（data access object） dao 只负责与数据库进行交互

11.2 分层原则

  • 封装性原则：每层只向外公开接口，但隐藏了内部实现细节
  • 顺序访问原则：下层为上层服务，但下层不能使用上层服务
  • 开闭原则：对扩展开放，对修改关闭

11.3 分层的优点

各层专注于自己所做的事情，便于提高开发质量；便于分工协作，提高开发效率；便于程序扩展；便于代码复用；易于维护。

11.4 分层开发案例：用户注册与登录

// 包结构
// com.cyx.layer
//	├── controller	UserController（控制层，调用业务层）
//	├── service		UserService / impl.UserServiceImpl（业务层，调用数据访问层）
//	├── dao			UserDao / impl.UserDaoImpl（数据访问层，只与数据库交互）
//	├── model		User（实体类）
//	└── util		JdbcUtil、MD5（工具类）

// 控制层：调用业务层完成业务处理
public class UserController {
	private UserService userService = new UserServiceImpl();

	public String register(String username, String password){
		return userService.register(username, password);
	}

	public String login(String username, String password){
		return userService.login(username, password);
	}
}

// 业务层：处理业务逻辑，需要数据时调用数据访问层
public class UserServiceImpl implements UserService {
	private UserDao userDao = new UserDaoImpl();

	public String register(String username, String password) {
		String salt = MD5.randomStr(30);
		String encrypt = MD5.encrypt(password, salt);  // 密码加盐加密
		int affectedRows = userDao.saveUser(username, encrypt, salt);
		return affectedRows == 1 ? "注册成功" : "注册失败";
	}

	public String login(String username, String password) {
		User user = userDao.getUserByUsername(username);
		if(user == null) return "账号不存在";
		String encrypt = MD5.encrypt(password, user.getSalt());
		return encrypt.equals(user.getPassword()) ? "登录成功" : "账号或密码错误";
	}
}

// 数据访问层：只负责与数据库交互
public class UserDaoImpl implements UserDao {

	public int saveUser(String username, String password, String salt) {
		String sql = "INSERT INTO `user` (`username`, `password`, `salt`) VALUES (?, ?, ?)";
		return JdbcUtil.update(sql, username, password, salt);
	}

	public User getUserByUsername(String username) {
		String sql = "SELECT username,password,salt FROM user WHERE username=?";
		List<User> users = JdbcUtil.query(sql, User.class, username);
		return users.size() == 0 ? null : users.get(0);
	}
}
posted @ 2026-10-25 15:20  程玖浔  阅读(8)  评论(0)    收藏  举报