目录大纲

Blog

MySQL数据库学习笔记

2020-04-29 13:06:52 · 创建时间 2021-10-27 20:52:24 · 更新时间

Sql

本文内容将不定期修订与完善，请以最新版本为准。转载或引用本文内容时，请明确注明作者及原文链接。

摘要

老师给自己讲蒙了，满肚子的疑问，也懒得跟老师交流。让我直接把网课打上了静音。我还不如自己学。

正文

以下是个人牢骚。

有个场景，很可笑。

某上课场景

学生：老师，我的xx按你说的运行不了啊。

老师：你安装得不对，卸载了重新安。

其实是学生安装的是最新版，老师非要让学生下载老古董。更气人的是，某些老古董，都已经找不到完整的原程序了。

某面试场景

面试官：你用过什么数据库？

面试者：我们老师教过小海豚标志的数据库，我用得很熟练。

教我们的某个老师，自己就搞不懂，mysql是啥，sqlyog又是啥。教的时候，就把sqlyog当做mysql来操作了。
mysql是数据库，可以通过命令行来操作；sqlyog是数据库的界面可视化工具

大部分老师，是不懂得更新自己知识的，毕竟职业已经是铁饭碗，谁还去想，教的东西有没有用，学生到底学到了什么？

好老师有吗？当然有。一些刚入职教师行业的老师，还很理想化。就拿我某个老师来说，他一直说，教书育人是件很神圣的事，上课也要有仪式感。我们也都觉得这个老师好，但是他自己呢？工资低、同时也没有副业。还有的老师，兢兢业业备课，认认真真教书。就连期末学生给他送钱，求个及格，老师都拒绝了。过得清廉，然而他也只是个老师而已。

某些老师，教学混日子，上课出去抽根烟，出去撒泡尿，半节课就过去了。但是他自己有很多职称，有很多副业，有很多关系，有很多专利。你即使去问问题，他也不鸟你。学生确实没学到东西，但是他自己钱包赚得满满的。

当然，还有些老师，教学认真教，各种关系处得也很好，自己手头也有很多项目。这种老师要看自己机缘了。

本文以MySQL8.0+为例，仅做入门参考。具体内容，参照MySQL官方文档。

一、数据库概念

英文：DataBase 简称DB

概念：用于存储和管理数据的仓库

数据库的特点：

持久化存储数据。本质是一个文件系统
方便存储和管理数据
使用了统一的方式（SQL）操作数据库

常见的数据库：

全球数据库排名DB-Engines Ranking

Oracle：收费的，大型关系数据库，Oracle公司的产品。Oracle收购Sun公司，收购MySQL
MySQL：开源免费的数据库，小型关系数据库。被Oracle收购之后，开始收费
- 免费：免费版本的具有开源协议，在免费版本上进行的任何修改所发布的软件，也必须是开源的（开源牛逼！）
- 收费：如果你想将修改后的软件出售获利，就必须得购买收费版本才可以。此外，收费版本卖的不是软件，而是服务，当数据出现问题的时候，购买的服务可以帮助处理发生问题的数据。
SQLServer：MicroSoft公司收费的中型关系型数据库。C#，.Net等语言常使用。
DB2：IBM公司的大型关系数据库产品，收费的。常用在银行系统中
SQLite：嵌入式的小型关系型数据库，内置到软件或系统中去，比方说浏览器。应用在Android手机端
SyBase：已经淡出历史舞台。提供了一个非常专业的数据建模工具PowerDesigner

二、SQL概念

SQL：结构化查询语言（Structured Query Language ），其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，就类似于“方言”。

SQL通用语法：

SQL语句可以单行或者多行书写，以分号结尾
使用空格和缩进来提高语句可读性
SQL语句不区分大小写，关键字建议使用大写
注释
- 单行注释：-- 注释内容或者 # 注释内容（MySQL特有）
- 多行注释：/* 注释 */

sql
1
2
3
-- 单行注释
# 单行注释，MySQL特有
/* 注释 */

SQL分类：

DDL(Data Definition Language)数据定义语言。用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
DML(Data Manipulation Language)数据操作语言。用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
DQL(Data Query Language)数据查询语言。用来查询数据库中表的记录(数据)。关键字：select, where 等
DCL(Data Control Language)数据控制语言。用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

三、MySQL使用

数据库可视化操作工具：

Navicat Premium（目前为止，感觉最好用的一款）
SQLyog（最新版的界面依旧是上古风格，不过还是挺好用的，推荐）
DBeaver（别人推荐，我还没用过）

题外话，讲讲使用技巧

Navicat生成ER关系图并导出

SQLyog生成关系图

Navicat查看sql语句

SQLyod查看sql语句

MySQL下载：地址

推荐解压版，拒绝傻瓜式。身为开发者，动手能力必须要强。我某同学，至今还以为mysql是个软件。我曾经给人安装了解压版，然后人家跟我说，这不是mysql，有海豚标志的才是mysql（可视化工具sqlyog的图标）。我就回了一句，哦，我还不太会安，你找别人吧。

常用命令行（进入到mysql的bin目录下）：

mysql -u 用户名 -p 用户名或mysql -u 用户名 -p; - - 连接数据库
show databases; - - 展示全部数据库
create database name; - - 创建name数据库
use name; - - 使用name数据库
show tables; - - 展示其中的表
create table name (id int,name varchar(20),age int); - - 创建name表，其中有3列，int型id列，varchar型长度为20的name列，int型age列
desc name; - - 查看name表的结构
source sql.sql; - - 执行本地的sql.sql文件
drop table name; - - 删除name表
drop database name; - - 删除name数据库
exit或quit; - - 退出数据库终端

windows下用命令行操作，是件极爽的事情，最进下载了一套数学视频，文件名都被打了小广告，cmd一键批处理，爽地不要不要的

本来想着写一篇cmd命令学习笔记，但奈何内容太庞大了，等哪天有足够的时间，来系统学习的话，我会写一篇的。

四、SQL操作

以下例子都是基于MySQL

4.1 DDL：操作数据库、表

操作数据库：CRUD

也就是所谓的增删改查

C(Create)：创建创建beauty数据库，判断是否存在，并设置字符集为UTF-8：create database if not exists beauty character set utf8;
R(Retrieve)：读取查询所有数据库：show databases;
U(Update)：更新
查看数据库的字符集：show create database beauty; 修改数据库的字符集：alter database beauty character set gbk;
D(Delete)：删除删除数据库：drop database if exists beauty;
使用数据库：use beauty;

查看当前使用的数据库：select database();

操作表：CRUD

C(Create)：创建语法：create table 表名 (列名1 数据类型1,列名2 数据类型2,...,列名n 数据类型n);
创建表：create table girls(id int,name varchar(20),score double(4,1),birthday date,insert_time timestamp);
复制表：create table 表名 like 被复制的表名;
R(Retrieve)：读取查询所有表：show tables; 查询表结构：desc 表名
U(Update)：更新修改表名：alter table 表名 rename to 新表名; 查看表的字符集：show create table 表名; 修改表的字符集：alter table 表名 character set utf8; 添加一列：alter table 表名 add 列名数据类型; 修改列的类型：alter table 表名 modify 列名新类型; 修改列名称类型： alter table 表名 change 列名新列名新类型; 删除列：alter table 表名 drop 列名;
D(Delete)：删除删除表：drop table if exists 表名;

数据类型的注意点：
double(4,1)表示总长度为4的数，其中小数有1位
date表示日期，只包含年月日，yyyy/MM/dd。我在开发starry的时候，发现在ios的safari中，只识别yyyy/MM/dd，而不识别yyyy-MM-dd，使用的时候注意。
datetime表示日期，包含年月日时分秒，yyyy/MM/dd HH:mm:ss
timestamp表示时间戳，包含年月日时分秒，yyyy/MM/dd HH:mm:ss。有种说法，“如果将来不给这个字段赋值，或者赋值为null，则默认使用系统当前时间，来自动赋值。”，经测验无效。

4.2 DML：增删改表中数据

添加数据

语法：insert into 表名(列名1,列名2,...,列名n) values(值1,值2,...,值n);

sql
1
INSERT INTO fairy(id,NAME,score) VALUES(1,'胡列娜',80.1);

注意：

列名和值，要一一对应。
如果表名后，不定义列名，则默认给所有列添加值。insert into 表名 values(值1,值2,...,值n);
除了数字类型，其他类型都需要使用引号（单双引号都可以）引起来

删除数据

语法：delete from 表名 where 条件;

sql
1
DELETE FROM fairy WHERE NAME='水冰儿';

注意：

如果不加条件，则会删除表中所有记录。
如果要删除所有记录。
- delete from 表名; 不推荐使用，有多少条记录，就会执行多少次删除操作。效率比较低。
- truncate table 表名; 推荐使用，先删除表，然后再创建一张一模一样的表。效率比较高。

修改数据

语法：update 表名 set 列名1=值1,列名2=值2,...,列名n=值n where 条件;

sql
1
UPDATE fairy SET NAME='水月儿' WHERE id=2;

注意：

如果不加任何条件，则会将表中所有记录全部修改

4.3 DQL：查询表中记录

导入数据：

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
USE `beauty`;

/*Table structure for table `fairy` */

DROP TABLE IF EXISTS `fairy`;

CREATE TABLE `fairy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `gender` varchar(2) NOT NULL,
  `age` int(11) NOT NULL,
  `grade` int(11) NOT NULL,
  `school` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `position` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

/*Data for the table `fairy` */

insert  into `fairy`(`id`,`name`,`gender`,`age`,`grade`,`school`,`position`) values (1,'胡列娜','女',22,52,'武魂殿学院','武魂殿'),(2,'邱若水','女',18,37,'天水学院','天斗帝国'),(3,'水冰儿','女',17,43,'天水学院','天斗帝国'),(4,'水月儿','女',17,36,'天水学院','天斗帝国'),(5,'邪月','男',22,53,'武魂殿学院','武魂殿'),(6,'唐三','男',16,44,'史莱克','天斗帝国'),(7,'戴沐白','男',21,48,'史莱克学院','天斗帝国'),(8,'胡列娜','女',22,52,'武魂殿学院','武魂殿');

语法：select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件限定 order by 排序规则 limit 分页限定

基础查询

多个字段的查询 select 字段名1,字段名2,... from 表名; 如果查询所有字段，可以使用 * 来替代字段列表
去除重复 DISTINCE关键字
计算列一般可以使用四则运算，来计算列的值 IFNULL(表达式1,表达式2) 表达式1是指需要判断为NULL的字段，表达式2是指NULL的替换值。
起别名 AS关键字，也可以省略

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 多个字段查询
SELECT NAME,school FROM fairy;

-- 去除重复
SELECT DISTINCT school FROM fairy;
SELECT DISTINCT NAME,school FROM fairy;

-- 计算age跟grade之和
SELECT NAME,age,grade,age+grade FROM fairy;
-- 如果有NULL参与运算，将NULL换位0
SELECT NAME,age,grade,IFNULL(age,0)+IFNULL(grade,0) AS '年龄+等级' FROM fairy;

条件查询

where子句后跟条件
运算符

运算符	含义
>、<、<=、>=、=、<>	<>在 SQL 中表示不等于，在 mysql 中也可以使用!=，没有==这个运算符
BETWEEN...AND...	在一个范围之内，如：between 100 and 200 相当于条件在 100 到 200 之间，包头又包尾
IN(集合)	集合表示多个值，使用逗号分隔
LIKE '张%' 或 LIKE '张_'	模糊查询。%指多个占位符，_指单个占位符
IS NULL 或 IS NOT NULL	查询某一列为 NULL 的值，注：不能写=NULL
AND 或 &&	与，SQL 中建议使用前者，后者并不通用。
OR 或 \|\|	或
NOT 或 !	非

代码

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 查询年龄大于等于20岁的
SELECT NAME,age FROM fairy WHERE age>=20;

-- 查询年龄等于22岁的
SELECT NAME,age FROM fairy WHERE age=22;

-- 查询年龄不等于22岁的
SELECT NAME,age FROM fairy WHERE age!=22;
SELECT NAME,age FROM fairy WHERE age<>22;

-- 查询年龄大于等于18岁，小于等于22岁
SELECT NAME,age FROM fairy WHERE age>=18 AND age<=22;
SELECT NAME,age FROM fairy WHERE age>=18 && age<=22;
SELECT NAME,age FROM fairy WHERE age BETWEEN 18 AND 22;

-- 查询所有22岁，18岁，性别为女的人
SELECT * FROM fairy WHERE age=22 OR age=18;
SELECT * FROM fairy WHERE age IN (22,18);

-- 查询等级不为NULL的人
SELECT * FROM fairy WHERE grade IS NOT NULL;

-- 查询姓水的人
SELECT * FROM fairy WHERE NAME LIKE '水%';

-- 查询姓名中含有水字的人
SELECT * FROM fairy WHERE NAME LIKE '%水%';

排序查询

语法：order by 排序字段1 排序方式1,....;

排序方式：

ASC：升序排。默认值
DESC：降序排。

注意：

如果有多个排序条件，则当前面的值一样时，才会去判断第二条件

代码：

sql
1
2
3
4
5
6
7
8
-- 按age升序排
SELECT * FROM fairy ORDER BY age;

-- 按age降序排
SELECT * FROM fairy ORDER BY age DESC;

-- 按照年龄排序，如果年龄一样，则按照等级排序
SELECT * FROM fairy ORDER BY age ASC,grade ASC;

聚合函数

将一列数据作为一个整体，进行纵向计算。

count：计算个数一般选择非空的列：主键可以通过count(*)，但实际开发中，少用这个
max：计算最大值
min：计算最小值
sum：计算和
avg：计算平均值

注意：聚合函数的计算，排除NULL值

解决方案：

选择不包含非空的列进行计算
IFNULL(grade,0)

代码：

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- 显示总数
SELECT COUNT(NAME) FROM fairy;

-- 如果grade为NULL的时候，聚合函数就不会统计，可以这样处理
SELECT COUNT(IFNULL(grade,0)) FROM fairy;

-- 获取最大值与最小值
SELECT MAX(age) FROM fairy;
SELECT MIN(age) FROM fairy;

-- 获取等级之和
SELECT SUM(grade) FROM fairy;

-- 获取平均等级
SELECT AVG(grade) FROM fairy;

分组函数

语法：group by 分组字段；

注意：

分组之后查询的字段：分组字段或者聚合函数；
where和having的区别？
- where在分组之前进行限定，如果不满足条件，则不参与分组；having在分组之后进行限定，如果不满足条件，不会被查询出来
- where后跟不可跟聚合函数；having可以进行聚合函数的判断

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- 按照性别分组，求总人数，并且求不同组的平均等级
SELECT 
  gender,
  AVG(grade) '平均等级',
  COUNT(NAME) '人数' 
FROM
  fairy 
GROUP BY gender ;

-- 按照性别分组，求总人数，并且求不同组的平均等级。
-- 要求：等级低于44的，不参与分组
SELECT 
  gender,
  AVG(grade) '平均等级',
  COUNT(NAME) '人数' 
FROM
  fairy 
WHERE grade >= 44 
GROUP BY gender ;

-- 按照性别分组，求总人数，并且求不同组的平均等级。
-- 要求：等级低于44的，不参与分组。分组之后，人数要大于2人
SELECT 
  gender,
  AVG(grade) '平均等级',
  COUNT(NAME) '人数' 
FROM
  fairy 
WHERE grade >= 44 
GROUP BY gender 
HAVING COUNT(NAME) > 2 ;

SELECT 
  gender,
  AVG(grade) '平均等级',
  COUNT(NAME) '人数' 
FROM
  fairy 
WHERE grade >= 44 
GROUP BY gender 
HAVING 人数 > 2 ;

分页查询

语法：limit 开始的索引，每页查询的条数;

公式：开始的索引=(当前的页码-1)x每页显示的条数

注意：limit是MySQL的一个“方言”。不同的数据库分页操作是不同的

sql
1
2
3
4
5
-- 从第1条数据开始，显示3条
SELECT * FROM fairy LIMIT 0,3;

-- 从第4条数据开始，显示3条
SELECT * FROM fairy LIMIT 3,3;

多表查询

笛卡尔积：A、B两个集合，取这两个集合的所有组成情况。要完成多表查询，要消除无用的数据。

内连接

使用内连接的要求：

从哪些表中查询数据
条件是啥
查询哪些字段

隐式内连接

语法：select 字段列表 from 表名列表 where ...;

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 查询两张表所有数据
SELECT 
  * 
FROM
  memeber,
  position1 
WHERE memeber.`p_id` = position1.`id` ;

-- 查询第一张表的name,grade和第二张表的school,position
SELECT 
  NAME,
  grade,
  school,
  POSITION
FROM
  memeber,
  position1 
WHERE memeber.`p_id` = position1.`id` ;

-- 用表的别名进行优化，先将表命名为别名，再来写select后的别名.xx
SELECT 
  t1.`name`,
  t1.`grade`,
  t2.`school`,
  t2.`position` 
FROM
  memeber t1,
  position1 t2 
WHERE t1.`p_id` = t2.`id` ;

显式内连接

语法：select 字段列表 from 表1 [inner] join 表2 on 条件;

[]这个符号表示可以省略的意思

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 查询两张表所有数据
SELECT 
  * 
FROM
  memeber 
  JOIN position1 
    ON memeber.`p_id` = position1.`id` ;

-- 查询第一张表的name,grade和第二张表的school,position
SELECT 
  NAME,
  grade,
  school,
  POSITION
FROM
  memeber 
  JOIN position1 
    ON memeber.`p_id` = position1.`id` ;

-- 用表的别名进行优化。注意写法，先将表命名为别名，再来写select后的别名.xx
SELECT 
  t1.name,
  t1.`grade`,
  t2.`school`,
  t2.`position` 
FROM
  memeber t1 
  JOIN position1 t2 
    ON t1.`p_id` = t2.`id` ;

外连接

左外连接

语法：select 字段列表 from 表1 left [outer] join 表2 on 条件;

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
-- 查询所有人信息，如果有位置，则查询位置信息；如果没有位置，则不显示
SELECT 
  t1.*,
  t2.school 
FROM
  memeber t1,
  position1 t2 
WHERE t1.`p_id` = t2.`id` ;

-- 此时，如果有数据的外键是空的，就不会查出来了
-- 为解决这个问题，我们需要用到外连接
SELECT 
  t1.*,
  t2.school 
FROM
  memeber t1 
  LEFT JOIN position1 t2 
    ON t1.`p_id` = t2.`id` ;

左外连接：
查询的是左边表的所有数据，以及连接表的交集部分

右外连接

语法：select 字段列表 from 表1 right [outer] join 表2 on 条件;

sql
1
2
3
4
5
6
7
8
-- 右外连接，跟上面左外连接的结果一样，写法的区别就是表的顺序，左换右，右换左了
SELECT 
  t1.*,
  t2.school 
FROM
  position1 t2 
  RIGHT JOIN memeber t1 
    ON t1.`p_id` = t2.`id` ;

右外连接：
查询的是右边表的所有数据，以及连接表的交集部分

内连接与外连接区别

此处的外连接就用左外连接来代表了。左外连接在查询时，会将join左边的内容全部都查出来，右外连接会将join右边的内容全部都查出来，原理其实是一样的。

unit表

unit_id	unit_type	unit_name
1	1	aaa
2	1	bbb
3	1	ccc
4	1	ddd
5	1	eee

person表

person_id	person_name	unit_id
1	小明	1
2	小红	1
3	小黄	1
4	小光	4
5	小明	2
6	小明	3

内连接语句

sql
1
2
3
4
5
select
u.*,p.*
from
unit u
join person p on u.unit_id=p.unit_id;

结果

外连接语句

sql
1
2
3
4
5
select
u.*,p.*
from
unit u
left outer join person p on u.unit_id=p.unit_id;

结果

区别：
内连接如果关联的字段没有值，比如null，是不会查出来的
外连接会保证进行外连接的一方全查出来，也就是说，即使它关联的字段为null，也是会查出来的。

子查询

概念：查询中嵌套查询，称嵌套的这个查询，为子查询

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 查询等级最高的人
-- 1.查询最高等级
SELECT 
  MAX(grade) 
FROM
  memeber ;

-- 2.查询等级等于最高的人
SELECT 
  * 
FROM
  memeber 
WHERE grade = 54 ;

-- 使用子查询
SELECT 
  * 
FROM
  memeber 
WHERE grade = 
  (SELECT 
    MAX(grade) 
  FROM
    memeber) ;

子查询的不同情况

子查询的结果是单行单列的子查询可以作为条件，使用运算符去判断。>,>=, <, <=, =
子查询的结果是多行单列的子查询可以作为条件，使用运算符in来判断
子查询的结果是多行多列的子查询可以作为一张虚拟表参与查询

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- 子查询结果是单行单列的
-- 查询等级最高的人
SELECT 
  * 
FROM
  memeber 
WHERE grade = 
  (SELECT 
    MAX(grade) 
  FROM
    memeber) ;

-- 子查询结果是多行单列的
-- 查询武魂殿和天斗帝国的所有成员信息
SELECT 
  * 
FROM
  memeber 
WHERE p_id IN 
  (SELECT 
    id 
  FROM
    position1 
  WHERE POSITION= "武魂殿" 
    OR POSITION= "天斗帝国") ;

-- 子查询结果是多行多列的
-- 查询成员年龄为18以上的成员信息跟位置信息
SELECT 
  * 
FROM
  position1 t1,
  (SELECT 
    * 
  FROM
    memeber 
  WHERE age > 18) t2 
WHERE t1.`id` = t2.p_id ;

-- 可以使用普通内连接，会更好理解
SELECT 
  * 
FROM
  memeber t1,
  position1 t2 
WHERE t1.`p_id` = t2.`id` 
  AND t1.`age` > 18 ;

联合查询

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

将两个查询结果合并

sql
1
2
3
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

举例工作中的一个需求，一张审批表，要分页查询审批信息。

审批状态有审批中、变更中、审批不通过、变更不通过、通过5种状态。

审批通过之后，会生成infoResAllCode。每个审批通过后的资源信息的infoResAllCode是不同的
提交变更，会新生成一条数据，infoResId也是新的
变更通过之后，旧数据修改历史版本字段为1，表示是历史版本了。变更后的作为新数据。

看时序图会好理解一点。

需求听起来很简单，分页查询审批数据，只查询出最新版本的那条。

像审批，多次不通过之后，有一条通过的，应该只查出来最新的那条记录。这个好整，虽然审批不通过的时候，infoResAllCode是空的，但因为infoResId是唯一的，取本infoResId中最新时间即可。
像变更，多次变更通过之后，会生成新的infoResId，但此时infoResAllCode又是唯一的了，需要取infoResAllCode中的最新时间来显示。
这是两条SQL语句，如果想要通过一次分页查询出来，就需要用到union。这是一个解决思路，但是并不好。

最后的解决办法，添加一个字段oldInfoResId，save的时候，将oldInfoResId存为当前infoResId。这样在查询时，只需要根据oldInfoResId分页查询最新时间的那条就行。

练习

数据库的架构如图

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
-- 需求：
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
SELECT 
  t1.id,
  ename,
  salary,
  jname,
  description 
FROM
  emp t1,
  job t2 
WHERE t1.`job_id` = t2.`id` ;

-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT 
  t1.id,
  ename,
  salary,
  jname,
  description,
  dname,
  loc 
FROM
  emp t1,
  job t2,
  dept t3 
WHERE t1.`job_id` = t2.`id` 
  AND t1.`dept_id` = t3.`id` ;

 
-- 3.查询员工姓名，工资，工资等级
SELECT 
  t1.`ename`,
  t1.`salary`,
  t2.`grade` 
FROM
  emp t1,
  salarygrade t2 
WHERE t1.`salary` BETWEEN t2.`losalary` 
  AND t2.`hisalary` ;

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT 
  t1.`ename`,
  t1.`salary`,
  t2.`jname`,
  t2.`description`,
  t3.`dname`,
  t3.`loc`,
  t4.`grade` 
FROM
  emp t1,
  job t2,
  dept t3,
  salarygrade t4 
WHERE t1.`dept_id` = t3.`id` 
  AND t2.`id` = t1.`job_id` 
  AND t1.`salary` BETWEEN t4.`losalary` 
  AND t4.`hisalary` ;

-- 5.查询出部门编号、部门名称、部门位置、部门人数
SELECT 
  t1.`id`,
  t1.`dname`,
  t1.`loc`,
  t2.`总人数` 
FROM
  dept t1,
  (SELECT 
    dept_id,
    COUNT(id) 总人数 
  FROM
    emp 
  GROUP BY emp.`dept_id`) t2 
WHERE t1.`id` = t2.dept_id ;

-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
SELECT 
  t1.`id`,
  t1.`ename`,
  t2.ename 
FROM
  emp t1 
  LEFT JOIN 
    (SELECT 
      id,ename 
    FROM
      emp) t2 
    ON t1.`mgr` = t2.id ;

4.4 DCL：管理用户与权限

管理用户

查询用户

SELECT * FROM USER; 注意：通配符%表示可以在任意主机使用用户登录

sql
1
2
3
4
5
6
-- 查询用户
-- 1.切换到MySQL数据库
USE mysql;
-- 2.查询user表
SELECT * FROM USER;
-- 通配符%表示可以在任意主机使用用户登录

添加用户

create user '用户名'@'主机名' identified by '密码';

sql
1
2
3
-- 创建用户
create user '用户名'@'主机名' identified by '密码';
create user 'huliena'@'localhost' identified by '123456';

删除用户

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

sql
1
2
3
-- 删除用户
DROP USER '用户名'@'主机名';
DROP USER 'huliena'@'localhost';

修改用户密码

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
-- 修改密码
-- mysql5.7版本
UPDATE USER SET PASSWORD=PASSWORD('新密码') WHERE USER='用户名';
SET PASSWORD FOR 'huliena'@'localhost' ='123456';
-- mysql8.0+版本
ALTER USER '用户名'@'主机名' IDENTIFIED BY '密码';
ALTER USER 'huliena'@'localhost' IDENTIFIED BY '654321';
-- DCL特有的方式，高低版本都支持
SET PASSWORD FOR '用户名'@'主机名'='密码';

-- MySQL忘记了root用户的密码,管理员方式打开cmd
-- 1.停止mysql服务
net STOP mysql
-- 2.使用无验证方式启动mysql服务
mysqld --skip-GRANT-TABLES;
-- 3.另开一个窗口登录mysql,直接输入mysql即可
mysql
-- 4.修改密码后，关闭所有命令窗口，并去任务管理器里面关闭mysqld.exe
-- 5.启动mysql服务
net START mysql

管理权限

查询权限

sql
1
2
3
4
-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'huliena'@'localhost';
SHOW GRANTS FOR 'root'@'localhost';

授予权限

sql
1
2
3
4
5
-- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
GRANT SELECT,UPDATE,DELETE ON * TO 'huliena'@'localhost';
-- 授予所有权限
GRANT ALL ON *.* TO 'huliena'@'localhost';

撤销权限

sql
1
2
3
-- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
revoke all on *.* from 'huliena'@'localhost';

刷新权限

sql
1
2
-- 刷新权限
FLUSH PRIVILEGES;

4.5 约束

概念：对表中的数据进行限定，从而保证数据的正确性、有效性和完整性

约束：

主键约束：primary key
非空约束：not null
唯一约束：unique
外键约束：foreign key

非空约束

NOT NULL，值不能为NULL

创建表时，添加非空约束
已创建表，添加非空约束
删除非空约束

代码：

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- 创建表时添加非空约束
create table hero (
id INT,
NAME VARCHAR(20) NOT NULL
);

desc hero;

-- 删除name的非空约束
alter table hero modify name varchar(20);

-- 已经创建表后，添加约束
alter table hero modify name varchar(20) not null;

唯一约束

UNIQUE，值不能重复(但是允许存在多个NULL)

创建表时，添加唯一约束
已创建表，添加唯一约束
删除唯一约束

代码：

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- 创建表时，添加唯一约束
CREATE TABLE hero (
id INT,
phone_number VARCHAR(11) UNIQUE
);
-- 注意mysql中，唯一约束限定的列的值可以有多个NULL

-- 删除唯一约束，index索引，唯一约束也叫做唯一索引
ALTER TABLE hero DROP INDEX phone_number;

-- 在创建表之后，添加唯一约束
ALTER TABLE hero MODIFY phone_number VARCHAR(11) UNIQUE;

DESC hero;

主键约束

含义：表示非空且唯一

一张表只能有一个字段表示主键。主键就是表中记录的唯一标识。

主键约束：

创建表时，添加主键约束
已创建表，添加主键约束
删除主键约束

代码

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- 创建表时，添加主键约束
CREATE TABLE hero(
id INT PRIMARY KEY,
NAME VARCHAR(20)
);

-- 删除主键
ALTER TABLE hero DROP PRIMARY KEY;

-- 已创建表，添加主键约束
ALTER TABLE hero MODIFY id INT PRIMARY KEY;

DESC hero;

自动增长

概念：如果某一列是数值类型的，使用auto_increment 可以完成自动增长

自动增长：

创建表时，添加主键约束，并完成自动增长
已创建表，添加自动增长
删除自动增长

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- 创建表时，添加自动增长
CREATE TABLE hero(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);

-- 删除自动增长
ALTER TABLE hero MODIFY id INT;

-- 已创建表，添加自动增长
ALTER TABLE hero MODIFY id INT AUTO_INCREMENT;

DESC hero;

注意：
自动增长是根据上一条数据的大小来增加的。比防说上一条是9，如果不传值，下一条就会是10

外键约束

一张表的数据，会过于冗余，我们可以将其进行拆分，然后再将两张表，通过外键关联起来。

外键约束：

创建表时，添加外键约束

sql
1
2
3
4
5
create table 表名(
    ....
    外键列
    constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);

已创建表，添加外键约束
删除外键约束

级联操作(级联更新和级联删除)

sql
1
2
3
4
5
-- 添加级联操作
alter table 表名 add constraint 外键名称 foreign key (外键列名称) referencs 主表名称(主表列名称) on update cascade on delete cascade

--也可以只添加其中某一个，也可以都添加，
--实现的功能，就是在删除外键表的内容的时候，主键表与其相关的内容也会一起被删掉

代码：

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
-- 创建表时，添加外键约束
CREATE TABLE memeber1 (
  id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20) NOT NULL,
  gender VARCHAR(2) NOT NULL,
  age INT(11) NOT NULL,
  grade INT(11) NOT NULL,
  p_id INT(11) NOT NULL,
  CONSTRAINT m_p_fk FOREIGN KEY (p_id) REFERENCES position1(id)
);

-- 已创建表，添加外键约束
ALTER TABLE memeber1 ADD CONSTRAINT m_p_fk FOREIGN KEY (p_id) REFERENCES position1(id);

-- 删除外键约束
ALTER TABLE memeber1 DROP FOREIGN KEY m_p_fk;

-- 添加级联更新和级联删除
ALTER TABLE memeber ADD CONSTRAINT m_p_fk FOREIGN KEY (p_id) REFERENCES position1(id) ON UPDATE CASCADE ON DELETE CASCADE;

五、数据库的设计

5.1 多表之间的关系

案例

代码

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键，自动增长
-- cname 旅游线路分类名称非空，唯一，字符串 100
CREATE TABLE tab_category (
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(100) NOT NULL UNIQUE
);
-- 创建旅游线路表 tab_route
/*
rid 旅游线路主键，自动增长
rname 旅游线路名称非空，唯一，字符串 100
price 价格
rdate 上架时间，日期类型
cid 外键，所属分类
*/
CREATE TABLE tab_route(
    rid INT PRIMARY KEY AUTO_INCREMENT,
    rname VARCHAR(100) NOT NULL UNIQUE,
    price DOUBLE,
    rdate DATE,
    cid INT,
    FOREIGN KEY (cid) REFERENCES tab_category(cid)
);
/*创建用户表 tab_user
uid 用户主键，自增长
username 用户名长度 100，唯一，非空
password 密码长度 30，非空
name 真实姓名长度 100
birthday 生日
sex 性别，定长字符串 1
telephone 手机号，字符串 11
email 邮箱，字符串长度 100
*/
CREATE TABLE tab_user (
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    PASSWORD VARCHAR(30) NOT NULL,
    NAME VARCHAR(100),
    birthday DATE,
    sex CHAR(1) DEFAULT '男',
    telephone VARCHAR(11),
    email VARCHAR(100)
);
/*
创建收藏表 tab_favorite
rid 旅游线路 id，外键
date 收藏时间
uid 用户 id，外键
rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
CREATE TABLE tab_favorite (
    rid INT, -- 线路id
    DATE DATETIME,
    uid INT, -- 用户id
    -- 创建复合主键
    PRIMARY KEY(rid,uid), -- 联合主键
    FOREIGN KEY (rid) REFERENCES tab_route(rid),
    FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

打开架构设计器，就会自动生成如下关系图

一对多：可以通过左外连接查询
多对多：可以通过在左外连接获得的数据基础上，再次使用左外连接。
具体的教程参照mybatis

sql
1
2
3
select * from role
left outer join people_role on people_role.rid=role.rid
left outer join people on people_role.pid=people.pid; #在前两行的基础上，再进行左外连接查询

5.2 数据库设计的范式

概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须要遵循前边所有的范式要求

分类：

第一范式
第二范式
第三范式
其他范式

第一范式

第一范式理解：每一列都是不可分割的原子数据项。

像这样的一张表，就满足第一范式了，但是也存在问题

存在非常严重的数据冗余（重复）：姓名、系名、系主任
数据添加存在问题：添加新开设的系和系主任时，数据不合法
数据删除存在问题：如果有人毕业了，删除信息时，会将系一起删掉。

第二范式

第二范式理解：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）

函数依赖：A-->B，如果A属性（属性组）的值，可以唯一确定B属性的值。则成B依赖于A。例如，学号-->姓名；（学号，课程名称）-->分数

**完全函数依赖：**A-->B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中的所有的属性值。例如，（学号，课程名称）-->分数

**部分函数依赖：**A-->B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中的某一些值即可。例如，（学号，课程名称）-->姓名

**传递函数依赖：**A-->B，B-->C，如果通过A属性（属性组）的值，可以确定唯一B属性的值，再通过B属性（属性组）的值可以确定唯一C属性的值，则称C传递函数依赖于A。例如，学号-->系名，系名-->系主任

**码：**如果在一张表中，一个属性或者属性组，被其他所有属性所依赖，则称这个属性（属性组）为该表的码。例如，在上表中的码，就是（学号，课程名称）。

主属性：码属性组中的所有属性
非主属性：除码属性组的属性

详细了解，请移步这里

现在根据第二范式的理解，在1NF基础上基础上消除非主属性对主码的部分函数依赖。

第三范式

第三范式理解：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

其他范式

巴德斯科范式(BCNF)、第四范式(4NF)和第五范式(5NF)

六、数据库的备份与还原

数据库的备份和还原目的：防止出现问题，导致数据丢失的情况。

6.1 命令行

备份语法：mysqldmp -u用户名 -p密码数据库名称 > 保存的路径

还原语法：

登录数据库
创建数据库
使用数据库
执行文件：source 文件路径

6.2 可视化工具

不同的可视化工具，具体操作不同，不多赘述

七、事务

7.1 事务的了解

概念

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

举个例子，支付宝转钱。A给B转5块钱，成功的话，A账户金额-5，B账户金额+5。如果在A账户减少金额，或者在B账户增加金额的过程中出错，则所有事件回滚。

事务的操作

具体操作

开启事务
回滚
提交

开启事务：start transaction;

回滚：rollback;

提交：commit;

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- zs给ls转账500
-- 0 开启事务
start transaction;

-- 1 zs账户-500
update account set balance=balance-500 where name='zs';

-- 1.5 模拟中间异常
-- 我来打断，嘿咻嘿咻！

-- 2 ls账户+500
update account set balance=balance+500 where name='ls';

-- 2.5 模拟中间状态，此时可以通过查询看看中间结果
-- select * from account;
-- 会发现数据已经变了

-- 3 发现执行没有问题，提交事务，执行成功
commit;

-- 3 有问题的话，就回滚事务，执行失败
rollback;

事务提交的两种方式：

自动提交 MySQL就是自动提交的，然而Oracle默认是手动提交的。一条DML（增删改）语句会自动提交一次事务。
手动提交需要先开启事务，再提交

修改事务的默认提交方式：

查看事务的默认提交方式：SELECT @@autocommit; -- 1代表自动提交 0代表手动提交
修改默认提交方式：set @@autocommit=0;

7.2 事务的四大特征

ACID特征：

**原子性(atomicity)：**是不可分割的最小操作单位，要么同时成功，要么同时失败。
**持久性(durability)：**当事务提交或者回滚后，数据库会持久化地保存数据。
**隔离性(isolation)：**多个事务之间，相互独立。但是，真实的情况下，事务之间是相互影响的，所以，我们要了解事务的隔离级别
**一致性(consistency)：**事务操作前后，数据总量不变。

7.3 事务的隔离级别

概念：多个事务之间是隔离的，即相互独立的。但是，如果多个事务，操作同一批数据，则会引发一些问题，设置不同的隔离级别，就可以解决这些问题。

存在问题：

脏读：事务B执行过程中，修改了数据X，在未提交的时候，事务A来读取数据X，而事务B进行了回滚，那么此时事务A读取的数据就是错的，叫脏读。
虚读（不可重复读）：事务A读取了数据X，而事务B修改了数据X，当事务A再次进行读取时，发现数据不匹配，这叫做虚读。
幻读：事务A根据条件X，查询出了N条数据，此时，事务B又添加了M条数据，其中有几条是在条件X范围内的，事务A再次查询时，发现多出来了几条数据，这叫做幻读。

数据库并发事务存在的问题

脏读

幻读

隔离级别：

read uncommited 含义：读未提交产生的问题：脏读、不可重复读、幻读
read commited（Oracle中默认隔离级别）含义：读已提交产生的问题：不可重复读、幻读
repeatable read（MySQL中默认隔离级别）含义：可重复读产生问题：幻读
serializable 含义：串行化（相当于把表锁上，A事物不提交，B事物是没法查询得到的）可以解决任何问题

注意：
隔离级别从小到大，安全性越来越高，但是效率越来越低。我们要选择合适级别，既保证相对比较安全，又能保证效率较高

修改数据库的默认隔离级别：

查询隔离级别： mysql5.6-5.7：select @@tx_isolation; mysql8.0+：select @@transaction_isolation;
设置隔离级别：set global transaction isolation level 级别字符串;

MySQL8.0+系统变量文档

MySQL5.6系统变量文档

修改隔离级别之后，查询，如果没有改变。需要断开连接，重新连接之后，才会查出来。

八、触发器

本篇内容是做课程设计时，需要用触发器，由此就来总结一下。参考文章

8.1 概念

定义：触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

作用：协助应用在数据库端确保数据的完整性。

举例，比如我现在有两个表，unit和person表，person与unit通过unitid进行外键约束。当创建person就需要在其对应的unit表中更新总人数字段，我们可以通过编写程序语言逻辑来实现，如果通过触发器，会自动更新字段，更容易了。

触发器的类型

INSERT型触发器：在执行insert、load data、replace语句时触发
UPDATE型触发器：在执行update语句时触发
DELETE型触发器：在执行delete、replace时触发

load data语句是将文件的内容插入到表中，相当于是insert语句
replace语句在一般的情况下和insert差不多，但是如果表中存在primary或者unique索引的时候，如果插入的数据和原来的primary key或者unique相同的时候，会删除原来的数据，然后再增加一条新的护具。
可以理解成，replace在没有重复的时候，相当于insert；若有重复，则会删除原来的，再执行insert

8.2 语法

要素

触发器名称
触发时机
- BEFORE：之前
- AFTER：之后
触发事件
- INSERT
- UPDATE
- DELETE
表名：需要建立触发器的表名
执行语句
- 一条SQL语句
- 多条SQL语句，通过begin和and包含起来

创建触发器

sql
1
2
3
4
5
6
7
8
-- 一条执行语句
CREATE TRIGGER 触发器名称 触发时机 触发事件 ON 表名 FOR EACH ROW 执行语句 

-- 多条执行语句
CREATE TRIGGER 触发器名称 触发时机 触发事件 ON 表名 FOR EACH ROW
BEGIN
	执行语句列表 
END

以上图为例，创建触发器，来实现person的数据发生变化时，修改unit表的总人数

sql
1
2
3
4
5
6
7
8
9
CREATE TRIGGER insert_trigger AFTER INSERT ON person FOR EACH ROW
UPDATE unit AS a
JOIN ( SELECT count(*) sum, unitid FROM person GROUP BY unitid ) AS b ON a.unitid = b.unitid 
SET a.sum = b.sum;

CREATE TRIGGER delete_trigger AFTER DELETE ON person FOR EACH ROW
UPDATE unit AS a
JOIN ( SELECT count(*) sum, unitid FROM person GROUP BY unitid ) AS b ON a.unitid = b.unitid 
SET a.sum = b.sum;

在执行多条语句的时候，触发器中间的sql语句分支是用;来分割的。如果有多个触发器的话，最后也是需要加一个结束符号。而MySQL默认是以;作为结束执行语句，为避免与触发器的内部分支起冲突，我们可以通过使用DELIMITER $来将结束符号修改为$

一般用在命令行的时候会这样，直接使用可视化工具，可以不用写

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
DELIMITER $

CREATE TRIGGER insert_trigger AFTER INSERT ON tb003 FOR EACH ROW
BEGIN
		UPDATE tb002 AS a
		JOIN ( SELECT unitaccnum, sum( basenumber ) sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
		SET a.basenumber = b.sum;
	UPDATE tb002 AS a
	JOIN ( SELECT unitaccnum, sum( unitmonpaysum ) AS sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
	SET a.unitpaysum = b.sum;
	UPDATE tb002 AS a
	JOIN ( SELECT unitaccnum, sum( permonpaysum ) AS sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
	SET a.perpaysum = b.sum;
	UPDATE tb002 AS a
	JOIN ( SELECT unitaccnum, count( accnum ) AS sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
	SET a.persnum = b.sum;
END $

CREATE TRIGGER delete_trigger AFTER DELETE ON tb003 FOR EACH ROW
BEGIN
		UPDATE tb002 AS a
		JOIN ( SELECT unitaccnum, sum( basenumber ) sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
		SET a.basenumber = b.sum;
	UPDATE tb002 AS a
	JOIN ( SELECT unitaccnum, sum( unitmonpaysum ) AS sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
	SET a.unitpaysum = b.sum;
	UPDATE tb002 AS a
	JOIN ( SELECT unitaccnum, sum( permonpaysum ) AS sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
	SET a.perpaysum = b.sum;
	UPDATE tb002 AS a
	JOIN ( SELECT unitaccnum, count( accnum ) AS sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
	SET a.persnum = b.sum;
END $

DELIMITER ;

可以通过导出sql文件，来进行查看。

删除触发器

sql
1
2
-- 删除触发器
DROP TRIGGER 触发器名称

定义变量

在begin...end中可以自定义变量，但是只能在begin...end中使用

语法

sql
1
2
3
4
-- 在begin...and中定义变量
DECLARE
	变量名 变量类型 [默认值] 
	SET 变量名 =值

New&Old

NEW和OLD使用

NEW：表示将要或者已经增加（或修改或删除）的数据，用于INSERT、UPDATE型触发器
OLD：表示将要或者已经被删除的数据，用于UPDATE、DELETE型触发器

语法

sql
1
2
3
4
5
-- 新增或修改行的某列数据
NEW.列名 

-- 删除或修改行的某列数据
OLD.列名

8.3 案例

需求

当在users中插入一条数据江厌离，就会在logs中生成一条日志信息江厌离 is created。

创建users表

sql
1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `add_time` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `name`(`name`(250)) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

创建logs表

sql
1
2
3
4
5
6
DROP TABLE IF EXISTS `logs`;
CREATE TABLE `logs`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `log` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '日志说明',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '日志表' ROW_FORMAT = Dynamic;

实现

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
DROP TRIGGER IF EXISTS `user_log`;
delimiter ;;
CREATE TRIGGER `user_log` AFTER INSERT ON `users` FOR EACH ROW begin
declare s1 varchar(40);
declare s2 varchar(40);
set s2=' is created';
set s1=concat(new.name,s2);
insert into logs(log) values(s1);
end
;;
delimiter ;

所有的触发器都存在information_schema数据库下的triggers表中

九、存储过程

9.1 概念

理论概念，看看就行

存储过程：类似于java代码中的方法，可以直接调用，来实现某些功能逻辑。有参数跟返回值

Navicat中描述的就挺合理，一个函数代替了，哈哈
本质不就是个可调用的方法嘛

优点：

代码可复用
代码精简一致
可维护性高

缺点：

可移植性差
使用于模块较小的功能：跟代码一样，每个方法功能尽量小，这样既易维护，又方便使用

9.2 快速入门

直接上sql语句

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 删除存储过程
DROP PROCEDURE IF EXISTS getUsers;

-- 创建存储过程
CREATE PROCEDURE getUsers () 
BEGIN
	SELECT * FROM users;
END;

-- 调用存储过程
CALL getUsers ();

参数类型

IN：传递给存储过程
OUT：从存储过程传出
INOUT：对存储过程传入和传出

OUT

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
DROP PROCEDURE IF EXISTS getGrades;

-- 创建触发器，返回三个变量的值
DELIMITER ;;
CREATE PROCEDURE getGrades(
OUT minGrade DECIMAL(8,2),
OUT avgGrade DECIMAL(8,2),
OUT maxGrade DECIMAL(8,2)
)
BEGIN
SELECT MIN(grade) INTO minGrade FROM memeber;
SELECT AVG(grade) INTO avgGrade FROM memeber;
SELECT MAX(grade) INTO maxGrade FROM memeber;
END ;;
DELIMITER ;

-- 用三个变量a,b,c接收值
CALL getGrades (@a, @b, @c) ;


-- 查询三个变量的值
SELECT @a,@b,@c

在测试的过程中，发现
select name into username from memeber;
和
select name from memeber into username;
最后的效果是一样的，所以，这两条语句是等价的

sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
drop procedure if exists getNameById;

Delimiter ;;
create procedure getNameById(
in userId int,
out userName varchar(200)
)
begin
select name from memeber where id=userId INTO userName;
end;;
Delimiter ;

-- 传入id为7，然后用name接收结果
Call getNameById(7,@name);

-- 查询结果
select @name;

用sqlyog运行的时候，把字符串所占的字节也输出了，我就特意百度了一下，字节为啥有的2两个，有的占3个

原因是字符集的原因，gbk中，英文占1字节（Byte，简记为B）,汉字占2字节；utf-8，英文占1个字节，汉字占3个字节。可以参考这篇文章

今日校园实现自动监测并提交最新表单

java中的反射与注解