MySQL_请注意保持“命令行”连接状态,为后续查询保持数据库资源。 前面的任务中,我们已

MySQL学习笔记，供参考

1、初识MySQL

JavaEE : 企业级Java开发 Web结构：

​ 前端 ：（页面 ：展示，数据！）

​ 后台 ：（连接点 ：链接数据库JDBC，连接前端（控制，控制试图跳转，和给前端传 递数据））

​ 数据库（存数据，Text，word，Excel）

只会写代码，基本混饭吃！

操作系统，数据结构与算法，当一个不错得程序员！

离散数学，数字电路，体系结构，编译原理。+实战经验， 高级的_{优秀程序员}

1.1、 为什么学习数据库

1、岗位需求

2、现在的世界，大数据时代~，得数据者得天下。

3、被迫需求：存数据

4、数据库是所有软件体系种最核心得存在 DBA

1.2、什么是数据库

数据库（DB，Database）

概念：数据仓库，软件，安装在操作系统（Windows，Linux，mac、…）之上！SQL，可以存储大量数据。500万！

作用：存储数据，管理数据

1.3、数据库分类

关系型数据库 ： （SQL）

• MySQL，Oracle，SQL Server，DB2，SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储， 学院信息表，考勤表，…

非关系型数据库 ：（NoSQL） Not Only

• Redis,MongDB
• 非关系型数据库，对象存储，通过对象的自身的属性来决定。

DBMS(DataBase Management System ) 数据库管理系统

• 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据；

1.4、MySQL简介

MySQL是一个关系型数据库管理系统

前世：瑞典MySQL AB公司

今生：属于Oracle旗下产品

MySQL是最好的RDBMS(Relational DataBase Management System，关系型数据库管理系统)应用软件之一。

开源的数据库软件~

体积小、速度快、总体拥有成本低，招人成本比较低，所有人必须会~

中小型网站、或者大型网站，集群！

官网：https://www.mysql.com

安装建议：

1、尽量不要用exe，会添加到注册表种，卸载比较麻烦

2、尽可能使用压缩包安装，只需要自己配下环境变量就行~

1.5、安装MySQL

教程：

1、解压

2、把压缩包放到电脑目录下，要记住放哪里了~

3、配置环境变量

4、新建mysql.ini配置文件

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:/mysql/mysql-8.0.28-winx64
# 设置mysql数据库的数据的存放目录 (data文件夹如果没有的话会自动创建)
datadir=D:/mysql/mysql-8.0.28-winx64/data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
# 跳过密码认证
skip-grant-tables
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8
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

5、启动cmd（管理员模式）

6、进入解压文件夹种的bin目录

cd /d D:\mysql\mysql-8.0.28-winx64\bin # 这是我自己的目录
1

7、初始化MySQL

mysqld --initialize-insecure
1

8、安装MySQL

mysqld --install
1

9、启动MySQL服务

net start mysql # 启动
net stop mysql  # 停止
# 如果安装失败了,以下命令删除mysql服务,重新按照以上步骤进行操作
sc delete mysql
1
2
3
4

10、进入MySQL，并修改密码

mysql -uroot -p  -- 进入命令,不输密码，跳过验证

-- 进入后，将密码修改为123456
mysql>update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
mysql>flush privileges;
1
2
3
4
5

11、注释掉ini配置文件种的跳过密码认证：skip-grant-tables

12、将MySQL目录种的bin文件夹加入环境变量的Path变量中~

13、重启MySQL服务，连接输入密码进入MySQL，大功告成！

1.6、连接数据库

命令行连接：

mysql -uroot -p123456
-- 出现mysql>的字样则连接成功!

-- ---------------------------------
-- 所有语句使用；结尾
mysql>show databases; -- 查看所有数据库

mysql>use mysql; -- 切换数据库

mysql>show tables; -- 查看数据库所有的表
mysql>describe student; -- 查看当前数据库中的名为student的表结构

mysql>create database school; -- 创建一个名为school的数据库

mysql>exit --退出连接

-- 单行注释

/*
多行
注释
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

数据库语言：

​ DDL 数据库定义语言

​ DML 数据库**管理（操作）**语言

​ DQL 数据库查询语言

​ DCL 数据库控制语言

2、操作数据库

数据库 > 库中表 > 表中数据（字段，值）

MySQL关键字不区分大小写

2.1、了解基本命令概念

1、创建数据库

mysql>create database [if not exists] school;
1

2、使用/切换数据库

-- 为了避免和和特殊字符重合，我么使用自己定义的字符时尽量用``符号括起来（TAB键的上面）
mysql>use school;
1
2

3、删除数据库

mysql>drop database [if exists] school;
1

学习思路：

在SQLyoug中使用后，对比SQLyog的历史记录，可以让我们快速的查看命令

常用的语法和关键字须记住！

2.2、数据库的列类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节 常用
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串形式的浮点数 金融计算的时候，一般用decimal

字符串

• char 字符串固定的大小 0~255
• varchar 可变长的字符串 0~65535 相当于java中的String 常用
• tinytext 微型文本 2^8 - 1
• text 文本串 2^16 - 1 保存大文本

时间日期

java.util.Date

• date YYYY-MM-DD，日期格式
• time HH:MM:ss，时间格式
• datetime YYYY-MM-DD HH:MM:ss 最常用的时间格式
• timestamp 时间戳， 1970年1月1日到现在的毫秒数！ 也较为常用！
• year 年份

NULL

• 没有值，未知
• 不要使用NULL进行运算，因为结果还是NULL！

2.3、数据库的字段属性（重点）

Unsigned：

• 无符号的整数
• 不能为负！

zerofill:

• 0填充
• 不足的位数，用0填充，如int(5), 5---->00005

自增（AUTO_INCREMENT)：

• 通常理解为自增，自动在上一条记录上 + 1（默认）
• 通常用来设置唯一的主键，index，必须是整型
• 可以自定义设置主键自增的起始值和步长

非空： NOT NULL，NULL

• 设置为NOT NULL，如果不赋值，则会报错！
• 设置为NULL，如果不填，默认为NULL！

DEFAULT（默认）：

• 设置默认的值
• sex，设置默认值为男，如果不指定该列的值，则默认男！

拓展：

/* 每一个表，都必须存在以下五个字段！（未来做项目用的，表示每一个记录存在意义！）

这是阿里巴巴规范里面的

id   主键
`verson`    乐观锁
is_delete   伪删除
gmt_create  创建时间
gmt_update  修改时间
*/
1
2
3
4
5
6
7
8
9
10

2.4、创建数据库表（重点）

CREATE 的语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
[(
COLUMN_DEFINITION,(这个有可选属性)
    ...
)]
[TABLE_OPTIONS]
[SELECT_STATEMENT];
1
2
3
4
5
6
7

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)   使用SQL创建
-- 学号int  登录密码varchar(20) 姓名varchar(30),性别varchar(2),出生日期(datetime),家庭住址(address),    邮箱email

-- 注意,使用英文(),表的名称和字段尽量用``括起来
-- AUTO_INCREMENT自增
-- 字符串用单引号或双引号括起来！
-- 所有的语句后面加，(英文的),最后一个不加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键！

CREATE TABLE `school`(
 `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` VARCHAR(30) NOT NULL COMMENT '姓名',
 `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
 `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
 `birthday` DATETIME NOT NULL COMMENT '出生日期',
 `address` VARCHAR(100) NULL COMMENT '家庭住址',
 `email` VARCHAR(20) NULL COMMENT '邮箱',
 PRIMARY KEY(`id`)
)ENGINE = INNODB, CHARSET = utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

格式

create table `表名`(
 `字段名` 数据类型 [属性] [索引] [注释],
 ... ,
 `字段名` 数据类型 [属性] [索引] [注释]
)ENGINE = 引擎名, CHARSET = 字符集, [注释];
1
2
3
4
5

2.5、数据表的类型

-- 关于数据库引擎
/*
INNODB 默认使用~
MYISAM 早些年使用的
*/
1
2
3
4
5

常规使用操作：

• MYISAM 节约空间，速度较快
• INNODB 安全性高，事务处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储！

MySQL引擎在物理文件上的区别：

• INNODB在数据库表中只要一个*.frm文件，以及上级目录下的ibdata1文件
• MYISAM对应文件：
  • *.frm 表结构的定义文件
  • *.MYD 数据文件（data）
  • *.MYI 索引文件（index）

设置数据库表的字符集编码

CAHRSET = utf8
1

不设置的话，会是MySQL默认的字符集编码~（不支持中文！）

MySQL的默认编码是Latin1，不支持中文

可以在my.ini配置文件中设置默认编码：

character-set-server = utf8
1

2.6、修改删除表

修改

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名;
ALTER TABLE `school` RENAME AS `school1`;
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性;
ALTER TABLE school1 ADD `phone` INT(11);

-- 修改表的字段 （重命名，修改约束！）
ALTER TABLE `school1` CHANGE `phone` `phone1` INT(1);-- 重命名
ALTER TABLE `school1` MODIFY `phone` VARCHAR(11);-- 修改约束

-- 删除表的字段
ALTER TABLE `school1` DROP `phone1`;
1
2
3
4
5
6
7
8
9
10
11

删除

-- 删除表（如果存在再删除）
DROP TABLE IF EXISTS `school1`;
1
2

所有的创建和删除操作尽量加上判断，以免报错~

注意点：

• `` 所有的字段名用这个符号括起来
• 注释 – /**/
• SQL语句不区分大小写，建议用小写
• 所有符号用英文

3、MySQL数据管理

3.1、外键（了解即可）

在创建表的时候，增加约束（麻烦，比较复杂）

CREATE TABLE `grade`(
  `gradeid` INT NOT NULL AUTO_INCREMENT COMMENT '班级id',
  `gradename` VARCHAR(30) NOT NULL COMMENT '班级名称',
  PRIMARY KEY(`gradeid`)
)ENGINE = INNODB, CHARSET = utf8;

-- student表的gradeid字段，要去引用年级表的gradeid字段
-- 定义外键key
-- 给外键增加约束（执行引用），references 引用

CREATE TABLE `student`(
 `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` VARCHAR(30) NOT NULL COMMENT '姓名',
 `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
 `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
 `birthday` DATETIME NOT NULL COMMENT '出生日期',
 `gradeid` INT NOT NULL COMMENT '班级id',
 `address` VARCHAR(100) NULL COMMENT '家庭住址',
 `email` VARCHAR(20) NULL COMMENT '邮箱',
 PRIMARY KEY(`id`),
 KEY `FK_gradeid` (`gradeid`),
 CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE = INNODB, CHARSET = utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

删除又外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（父表）

方式二 ：创建表成功之后，增加外键约束

CREATE TABLE `grade`(
  `gradeid` INT NOT NULL AUTO_INCREMENT COMMENT '班级id',
  `gradename` VARCHAR(30) NOT NULL COMMENT '班级名称',
  PRIMARY KEY(`gradeid`)
)ENGINE = INNODB, CHARSET = utf8;

CREATE TABLE `student`(
 `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` VARCHAR(30) NOT NULL COMMENT '姓名',
 `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
 `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
 `birthday` DATETIME NOT NULL COMMENT '出生日期',
 `gradeid` INT NOT NULL COMMENT '班级id',
 `address` VARCHAR(100) NULL COMMENT '家庭住址',
 `email` VARCHAR(20) NULL COMMENT '邮箱',
 PRIMARY KEY(`id`)
)ENGINE = INNODB, CHARSET = utf8;

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

-- ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表字段) REFERENCES 主表名 (主表字段)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

以上的操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰）

最佳实践：

• 数据库就是单纯的表，只用来存储数据，只要有行（数据）和列（字段）
• 我们想使用多张表的数据，想使用外键（程序去实现）

3.2、DML语言（全部记住）

**数据库意义：**数据存储，数据管理

DML语言：数据操作（管理）语言

• Insert
• Update
• Delete

3.3、添加 ( INSERT )

insert 语法：

INSERT [LOW_PRIORITY |DELAYED| HIGH_PRIORITY]
[IGNORE][INTO]tbl_name[(col_name,...)]
VALUES ({expr| DEFAULT},...),(...),...
[ON DUPLICATE KEY UPDATEcol_name=expr,... ]

INSERT [LOW_PRIORITY |DELAYED| HIGH_PRIORITY]
[IGNORE][INTO]tbl_name
SET col_name={expr| DEFAULT},... ]

INSERT [LOW_PRIORITY | HIGH_PRIORITY]
[IGNORE][INTO]tbl_name[(col_name,...)]
SELECT ...[ON DUPLICATE KEY UPDATEcol_name=expr,... ]

1
2
3
4
5
6
7
8
9
10
11
12
13

-- 插入语句（添加）
-- insert into 表名([字段1],[字段2],[字段3],...) values('值1','值2','值3',...);

INSERT INTO `grade`(`gradename`) VALUES('大一'),('大二'),('大三'),('大四');
-- 一个字段插入多个值，每个值用括号括住，并用逗号分开
-- 一般写插入语句，我们一定要将字段和值一一对应！

-- 插入多个字段
INSERT INTO `student`(`name`,`password`,`sex`) 
VALUES('赵六','aaaaaa','男'),('吴七','bbbbbb','女'),('沈八','cccccc','男');


1
2
3
4
5
6
7
8
9
10
11
12

语法：insert into 表名([字段1],[字段2],[字段3],...) values('值1','值2','值3',...);

注意事项：

1. 字段和字段之间使用英文隔开
1. 字段是可以省略的，但是后面的值必须一一对应，一个字段也不能少
1. 可以同时插入多条数据，VALUES后面的值，先括住，再用逗号隔开即可      `VALUES(),(),(),...`
1
2
3

3.4、修改（UPDATE）

update 语法：

-- ================ 单表更改 ===================
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]

-- ================ 多表更改 ===================
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
1
2
3
4
5
6
7
8
9
10
11

-- 修改学生名字，带条件
UPDATE `student` SET `name` = '东方红' WHERE `name` = '张三';

-- 修改学生名字，不带条件，会全部修改！
UPDATE `student` SET `name` = '东方红';

-- 修改多个属性，用逗号隔开
UPDATE `student` SET `name` = '黄河',`email` = '1059615162@qq.com' WHERE `id` = 1;

-- 语法：
-- UPDATE 表名 SET column_name = value,[column_name = value,......] [where 条件]


1
2
3
4
5
6
7
8
9
10
11
12
13

条件：where子句 运算符 id等于某个值，大于某个值，在某个区间内修改…

操作符会返回 布尔值

-- 通过多条件定位数据
UPDATE `student` SET `name` = '长江' WHERE `name` = '东方红' AND `sex` = '女';
1
2

语法：UPDATE 表名 SET column_name = value,[column_name = value,......] [where 条件];

注意：

• column_name 是数据库的列，尽量带上``
• 条件，筛选的条件，如果没有指定，则会修改所有的列
• value，是一个具体的值，也可以是一个变量

UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `id` BETWEEN 2 AND 4;
1

3.5、删除

delete 命令

语法：delete from 表名 [where 条件]

-- 删除数据（避免这样操作,删库跑路）
delete from `student`;

-- 删除指定数据
delete from `student` where `id` = 1;
1
2
3
4
5

TRUNCATE 命令

作用：完全清空一个数据表，表的结构和索引不会变！

-- 清空student 表
TRUNCATE `student`;
1
2

delete 和 truncate 的区别

• 相同点：都能删除表中数据，且不会删除表结构
• 不同点：
  • TRUNCATE 会重新设置自增列，计数器归零
  • TRUNCATE 不会影响事务

-- 测试DELETE 和 TRUNCATE 的区别
CREATE TABLE `test`(
  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'id',
  `coll` VARCHAR(30) NOT NULL COMMENT 'coll',
  PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `test` (`coll`) VALUES('1'),('2'),('3');

DELETE FROM `test` -- 不会影响自增

TRUNCATE TABLE `test` -- 自增会归零
1
2
3
4
5
6
7
8
9
10
11
12

DELETE删除的问题 删除后重启数据库的现象：

• INNODB 自增列会重新从1开始计数（存在内存中的，断电即失）
• MYISAM 自增列会从上一次的自增量开始计数（存在文件中的，不会丢失，除非硬盘损坏）

4、DQL查询数据（最重点）

4.1、DQL的**重要性**

( Data Query Language : 数据查询语言 )

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询它都能做~
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

straight distinct high_priorty rollup

Select 的完整语法：

SELECT
	[ALL | DISTINCT | DISTINCTROW]
		[HIGH_PRIORTY]
		[STRAIGHT_JOIN]
		[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
		[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
	select_expr [,select_expr ...]
	[FROM table_references
    	PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
    	[ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
    	[ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
    	[CHARACTER SET charset_name]
    	export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
	[FOR UPDATE | LOCK IN SHARE MODE]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

4.2、查询指定字段

-- 查询全部学生 SELECT 字段 FROM 表名
SELECT * FROM `student_information`;

-- 查询指定字段
SELECT `name` FROM `student_information`;

-- 别名，给结果起一个名字，可以给起别名，也可以给表起别名
SELECT `name` AS 姓名,`favorite` AS 兴趣爱好 FROM `student_information`;

-- 函数 CONCAT() 字符串拼接函数
SELECT CONCAT('姓名：',`name`,'  兴趣爱好：',`favorite`) AS 新名字 FROM `student_information`;
1
2
3
4
5
6
7
8
9
10
11

语法：SELECT 字段,... from 表名

有的时候，列名字不是那么的见名知意，所以我们可以起别名

关键字：AS

用法：

• 字段名 AS 别名
• 表名 AS 别名

去重 distinct

作用：去除SELECT查询出来的结果中重复的数据，只显示一条

-- 查询一下哪些同学参加了考试
SELECT * FROM `student_score` -- 查看成绩表
SELECT `s_id` FROM `student_score` -- 查询有哪些同学参加了考试
SELECT DISTINCT `s_id` FROM `student_score` -- 发现重复数据，去重
1
2
3
4

数据库的列（表达式）

-- 查询一下哪些同学参加了考试
SELECT * FROM `student_score` -- 查看成绩表
SELECT `s_id` FROM `student_score` -- 查询有哪些同学参加了考试
SELECT DISTINCT `s_id` FROM `student_score` -- 发现重复数据，去重

SELECT VERSION() -- 查看系统版本号（函数）
SELECT 100*3-1 AS 计算结果 -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（变量）

-- 查看学院考试成绩并+1分
SELECT `s_id`,`score`+1 AS 提分后 FROM `student_score`
1
2
3
4
5
6
7
8
9
10
11

数据库中的表达式： 文本值，列，NULL，函数，计算表达式，系统变量，… …

select 表达式 from 表名

4.3、where条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或多个表达式组成！结果 布尔值

逻辑运算符

尽量使用英文字母

-- ======================= where =============================
SELECT `s_id`,`score` FROM `student_score`

-- 查询考试成绩在60-75之间
SELECT `s_id`,`score` FROM `student_score`
WHERE `score`>=60 AND `score`<=75

-- and &&
SELECT `s_id`,`score` FROM `student_score`
WHERE `score`>=60 && `score`<= 75

-- 模糊查询（基于区间）
SELECT `s_id`,`score` FROM `student_score`
WHERE `score` BETWEEN 60 AND 75

-- 除了12341学生外的学生成绩
SELECT `s_id`,`score` FROM `student_score`
WHERE `s_id` != 12341

-- != NOT
SELECT `s_id`,`score` FROM `student_score`
WHERE NOT `s_id` = 12341
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

模糊查询

-- ======================= 模糊查询 =============================
-- like结合 通配符 %（0 - 任意个字符），_（一个字符）
-- 查询姓康的学生
SELECT `id`,`name` FROM `student_information`
WHERE `name` LIKE '康%';

-- 查询康姓且两字的学生
SELECT `id`,`name` FROM `student_information`
WHERE `name` LIKE '康_';
-- 查询姓赵的学生
SELECT `id`,`name` FROM `student_information`
WHERE `name` LIKE '赵%';

-- 查询detail中含有‘健康’的学生
SELECT `id`,`name` FROM `student_information`
WHERE `detail` LIKE '%健康';


-- =================== IN （具体的一个或多个的值）===================
-- 查询id为12341,12342学员
SELECT `id`,`name` FROM `student_information`
WHERE `id` IN (12341,12342);

-- 查询age为19和18的学生
SELECT `id`,`name` FROM `student_information`
WHERE `age` IN (18,19);


-- IS NULL，IS NOT NULL
-- 查询detail不为空的学生
SELECT `id`,`name` FROM `student_information`
WHERE `detail` IS NOT NULL;

-- 查询detail为空的学生
SELECT `id`,`name` FROM `student_information`
WHERE `detail` IS NULL;
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

4.4、连表查询

Join对比

-- ============ Join 对比 ==============
-- 查询学号，姓名，分数
-- Inner Join
SELECT si.`id`,`name`,`score`
FROM `student_information` si
INNER JOIN `student_score` ss
ON si.`id` = ss.`s_id`

-- Left Join
SELECT si.`id`,`name`,`score`
FROM `student_information` si
LEFT JOIN `student_score` ss
ON si.`id` = ss.`s_id`

-- Right Join
SELECT si.`id`,`name`,`score`
FROM `student_information` si
RIGHT JOIN `student_score` ss
ON si.`id` = ss.`s_id`
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- ============================== 连表查询（Join） =======================================


/*思路：
1. 分析需求，分析查询的字段来自哪些表
2. 确定使用哪种连接（7种之一）
3. 确定交叉点（两个表之间的共同列数据）
4. 判断的条件 ：学生信息表中的id = 学生分数表中的s_id
	条件语法有两个：
		1. Join （连接的表）on （判断条件） 固定语法 --连接查询
		2. Where  --等值查询
*/


-- 双表查询（学号，姓名，分数编号，分数）
-- 这里用的Inner Join查询（也可以使用另外两种，不同点就是基于哪张表而已）
 SELECT si.`id`,`name`,ss.`id`,`score`
 FROM `student_information` si
 INNER JOIN `student_score` ss
 ON si.`id` = ss.`s_id`
 
 -- 三表查询（学号，姓名，科目名称，分数）
 /*思路：
  先将查询的字段放在两张表查，查出来再加上下一张表的字段，
  一层一层往下走。
 */
 
SELECT si.`id`,`name`,`major_source`,`score`
FROM `student_information` AS si
INNER JOIN `student_score` AS sc
ON si.`id` = sc.`s_id`
INNER JOIN `student_source` AS so
ON sc.`so_id` = so.`id`
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

自连接

父类：

子类：

查询父类对应的子类关系：

-- ================== 自连接查询 =======================
-- 方式一 (Where)
SELECT a.`categoryName` AS 父项,b.`categoryName` AS 子项
FROM `category` a,`category` b
WHERE a.`categoryid` = b.`pid`

-- 方式二 （Inner Join）,不可以用Left/Right Join
SELECT a.`categoryName` AS 父项,b.`categoryName` AS 子项
FROM `category` a
INNER JOIN `category` b
ON a.`categoryid` = b.`pid` 
1
2
3
4
5
6
7
8
9
10
11

4.5、分页和排序

排序 语法 ：ORDER BY 列数据 ASC / DESC

-- 排序 ： 升序 ASC，降序 DESC
-- 语法 ：ORDER BY 列数据 ASC/DESC
-- 根据成绩结果排序（降序）
SELECT `sin`.`id`,`name`,`major_source`,`score`
FROM `student_information` `sin`
INNER JOIN `student_score` `ssc`
ON `sin`.id = `ssc`.`s_id`
INNER JOIN `student_source` `sso`
ON `ssc`.`so_id` = `sso`.`id`
ORDER BY `score` DESC
1
2
3
4
5
6
7
8
9
10

分页 语法 ：LIMIT 数据起始下标 pageSize

/* 假设有100万条数据，为何要进行分页？
      1. 缓解数据库压力
      2. 给人更好的体验
   也有不分页的，叫瀑布流
	如：百度搜图片，刷抖音，刷快手（划到最下面，会自动加载）
*/

-- 每页只显示三条数据
-- 语法 ：LIMIT 数据起始下标, 页面大小
SELECT `sin`.`id`,`name`,`major_source`,`score`
FROM `student_information` `sin`
INNER JOIN `student_score` `ssc`
ON `sin`.id = `ssc`.`s_id`
INNER JOIN `student_source` `sso`
ON `ssc`.`so_id` = `sso`.`id`
ORDER BY `sin`.`id` DESC
-- ORDER BY `score` DESC
LIMIT 0,3

-- 第一页  limit 0,3    (1-1)*3
-- 第二页  limit 3,3	 (2-1)*3	
-- 第三页  limit 6,3	 (3-1)*3
-- 第四页  limit 9,3	 (4-1)*3
-- 第n页                (n-1)*pageSize
-- 【pageSize ：页面大小】
-- 【(n-1)*pageSize : 起始下标】
-- 【n ：当前页数】
-- 【总页数(pageCount) = 数据总量(dataTotal) / 页面大小(pageSize)】
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

练习：

-- 查询查询英语成绩排名前二的，并且分数大于80的
SELECT `sin`.`id`,`name`,`score`
FROM `student_information` `sin`
INNER JOIN `student_score` `ssc`
ON `sin`.`id` = `ssc`.`s_id`
WHERE `so_id` = 43213 AND `score`>=80
ORDER BY `score` DESC
LIMIT 0,2

-- 第二种方式
SELECT `sin`.`id`,`name`,`score`
FROM `student_information` `sin`, `student_score` `ssc`
WHERE `sin`.`id` = `ssc`.`s_id` AND `so_id` = 43213 AND `score`>=80
ORDER BY `score` DESC
LIMIT 0,2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

4.6、子查询

where ( 这里再嵌套一个SQL语句 )

本质 ：在where中嵌套一个查询语句

-- 查询英语成绩，并且分数大于80的
-- 方式一
SELECT `sin`.`id`,`name`
FROM `student_information` `sin`
RIGHT JOIN `student_score` `ssc`
ON `sin`.`id` = `ssc`.`s_id`
WHERE `score`>=80 AND `so_id` = (
	SELECT `id`
	FROM `student_source`
	WHERE `major_source` = '英语'
);


-- 方式二
SELECT `id`,`name` FROM `student_information` 
WHERE `id` IN (
 SELECT `s_id` FROM `student_score` WHERE `score`>=80 AND `so_id` = (
    SELECT `id` FROM `student_source` WHERE `major_source` = '英语'
 )
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

4.7、分组过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
SELECT `major_source`,AVG(`score`) AS 平均分,MAX(`score`) AS 最高分,MIN(`score`) AS 最低分
FROM `student_score` `ssc`
INNER JOIN `student_source` `sso`
ON `sso`.`id` = `ssc`.`so_id`
GROUP BY `major_source` -- 通过什么来分组
HAVING 平均分>=80 -- 分组后的过滤条件
1
2
3
4
5
6
7

5、MySQL函数

官网：https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html

5.1、常用函数

-- ============== 常用函数 ==============

-- 数学
SELECT ABS(-18) -- 绝对值
SELECT CEIL(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 随机值
SELECT SIGN(-2) -- 返回符号类型 0~0,-2~-1,2~1

-- 字符串函数
SELECT CHAR_LENGTH('jijiji') -- 字符串长度
SELECT CONCAT('jiji','ji') -- 拼接字符串
SELECT INSERT('jiji',2,2,'kw') -- 在指定位置插入并替换原字符串
SELECT LOWER('kAng') -- 转小写
SELECT UPPER('kang') -- 转大写
SELECT INSTR('kang','k') -- 返回第一次出现子串的索引
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换指定字符串
SELECT SUBSTR('坚持就能成功',1,2) -- 获取子串
SELECT REVERSE('坚持就能成功') -- 反转字符串

-- 时间和日期
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() -- 获取本地时间
SELECT SYSDATE() -- 获取系统时间

SELECT YEAR(NOW()) -- 获取年份
SELECT MONTH(NOW()) -- 获取月份
SELECT DAY(NOW()) -- 获取日期
SELECT HOUR(NOW()) -- 获取小时
SELECT MINUTE(NOW()) -- 获取分钟
SELECT SECOND(NOW()) -- 获取秒数

-- 系统
SELECT SYSTEM_USER() -- 获取当前用户
SELECT USER() -- 获取当前用户
SELECT VERSION() -- 获取当前版本号
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

5.2、聚合函数（常用）

-- ============ 聚合函数 ==========
-- COUNT()  都能够统计表中的数据（想查询一个表中有多少条记录，就用Count()）
SELECT COUNT(id) FROM `student_information` -- Count(字段),会忽略所有的NULL值
SELECT COUNT(*) FROM `student_information` -- Count(*),不会忽略NULL值，本质：计算行数
SELECT COUNT(1) FROM `student_information` -- Count(1),不会忽略NULL值，本质：计算行数

SELECT SUM(score) AS 总和 FROM `student_score`
SELECT AVG(score) AS 平均分 FROM `student_score`
SELECT MAX(score) AS 最大值 FROM `student_score`
SELECT MIN(score) AS 最小值 FROM `student_score`
1
2
3
4
5
6
7
8
9
10

5.3、自定义函数

创建自定义函数的语法：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aBHCFfiJ-1686195380855)(C:\Users\LT\AppData\Roaming\Typora\typora-user-images\image-20221013175730544.png)]

删除函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LSTJrAQP-1686195380856)(C:\Users\LT\AppData\Roaming\Typora\typora-user-images\image-20221013180023241.png)]

5.4、数据库级别的MD5加密

什么是MD5？

主要增强算法复杂度和不可逆性

MD5不可逆，具体的MD5的值是一样的

MD5破解的原理，背后有一个字典，{加密后的值，加密前的值}

-- ============ MD5测试加密 ============
CREATE TABLE `testmd5`(
 `id` INT NOT NULL,
 `name` VARCHAR(20) NOT NULL,
 `psd` VARCHAR(10) NOT NULL,
 PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 明文密码
INSERT INTO `testmd5` VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')

-- 加密
UPDATE `testmd5` SET `psd` = MD5(`psd`)

-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4,'小明',MD5('123456'))

-- 用户传进来的密码如何校验
SELECT * FROM `testmd5` WHERE `id` = 4 AND `psd` = MD5('123456')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

6、事务

1、事务定义

事务是一个最小的不可在分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务是一个最小的工作单元)。

一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成。

事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同。

2.事务是什么？

多个操作同时进行,那么同时成功，那么同时失败。这就是事务。

事务有四个特性：一致性、持久性、原子性、隔离性

比如有一个订单业务

1.订单表当中添加一条记录 2.商品数量数据更新(减少) 3…

当多个任务同时进行操作的时候，这些任务只能同时成功，或者同时失败。

原子性（Atomicity）

• 原子性意味着数据库中的事务执行是作为原子。即不可再分，整个语句要么执行，要么不执行。
  在SQL SERVER中，每一个单独的语句都可以看作是默认包含在一个事务之中，每一个语句本身具有原子性，要么全部执行，要么全部不执行，不会有中间状态。
• 例如：
  银行转账功能，从A账户减去100，在B账户增加100，如果这两个语句不能保证原子性的话，比如从A账户减去100后，服务器断电，而在B账户中却没有增加100.虽然这种情况会让银行很开心，但作为开发人员的你可不希望这种结果.而默认事务中，即使出错了也不会整个事务进行回滚。而是失败的语句抛出异常，而正确的语句成功执行。这样会破坏原子性。所以SQL SERVER给予了一些选项来保证事务的原子性。

一致性(Consistency)

一致性即在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

一致性体现在两个层面：

• 数据库机制层面
  数据库层面的一致性是，在一个事务执行之前和之后，数据会符合你设置的约束（唯一约束，外键约束,Check约束 等)和触发器设置.这一点是由SQL SERVER进行保证的.

• 业务层面

  ​ 对于业务层面来说,一致性是保持业务的一致性.这个业务一致性需要由开发人员进行保证.很多业务方面的一致性可以 通过转移到数据库机制层面进行保证.比如，产品只有两个型号，则可以转移到使用CHECK约束使某一列必须只能存 这两个型号.

隔离性（Isolation)

• 事务的执行是互不干扰的，一个事务不可能看到其他事务运行时，中间某一时刻的数据。
  在Windows中，如果多个进程对同一个文件进行修改是不允许的，Windows通过这种方式来保证不同进程的隔离性，而SQL Server中，通过SQL SERVER对数据库文件进行管理，从而可以让多个进程可以同时访问数据库:SQL Server利用加锁和阻塞来保证事务之间不同等级的隔离性.
• 一般情况下，完全的隔离性是不现实的，完全的隔离性要求数据库同一时间只执行一条事务，这样的性能可想而知.想要理解SQL Server中对于隔离性的保障，首先要了解事务之间是如何干扰的.事务之间的互相影响的情况分为几种，分别为:脏读(Dirty Read)，不可重复读，幻读。

持久性（Durability)

• 持久性，意味着在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
  即使出现了任何事故比如断电等，事务一旦提交，则持久化保存在数据库中.

隔离性导致的一些问题

脏读：（读未提交）

​ 指一个事务读取到了另一个事务未提交的数据。

不可重复读：（读已提交）

​ 对于事务A多次读取同一个数据时，由于其他是事务也在访问这个数据，进行修改且提交，对于事务A，读取同一个数据时，有可能导致数据不一致，叫不可重复读。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wd7NOUF1-1686195380856)(C:\Users\kang\AppData\Roaming\Typora\typora-user-images\image-20221013145104684.png)]

幻读：

• 原因：
  • 因为mysql数据库读取数据时，是将数据放入缓存中，当事务B对数据库进行操作：例如删除所有数据且提交时，事务A同样能访问到数据，这就产生了幻读。
• 问题：
  • 解决了可重复读，但是会产生一种问题，错误的读取数据，对于其他事务添加的数据也将访问不到

执行

-- ============================== 事务 =================================
-- MySQL是默认开启事务自动提交的
SET COMMIT = 0 -- 关闭
SET COMMIT = 1 -- 开启

-- 手动处理事务
SET COMMIT = 0 -- 关闭自动提交
	
START TRANSACTION -- 标记一个事务的开启，往后的sql语句都在同一个事务内

INSERT xxx
INSERT xxx


COMMIT -- 提交：持久化（成功！）

ROLLBACK -- 回滚：回到原来的地方（失败）

-- COMMIT 或 ROLLBACK 执行后，事务会被自动关闭

-- 保留点 SAVEPOINT （在SQL代码中保留点名越多越好，这样就可以灵活回滚）
SAVEPOINT 保留点名 -- 在此处定义保留点名
ROLLBACK TO 定义的保留点名 -- 回滚到定义的保留点名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

模拟事务

-- 在shop库下建立account表,并插入数据
CREATE DATABASE `shop`
USE `shop`


CREATE TABLE `account`(
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '账户标识',
  `name` VARCHAR(30) NOT NULL COMMENT '账户名字',
  `money` DECIMAL(10,2) NOT NULL COMMENT '账户余额',
  PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `account` (`name`,`money`)
VALUES ('A','5000.00'),('B','10000.00');

-- =================模拟转账（事务）=====================

SET autocommit = 0; -- 关闭自动提交
SHOW autocommit
START TRANSACTION; -- 开始一个事务

UPDATE `account` SET `money` = `money` - 2200 WHERE `name` = 'A';-- A减2200
UPDATE `account` SET `money` = `money` + 2200 WHERE `name` = 'B';-- B加2200

COMMIT; -- 提交事务
ROLLBACK; -- 回滚事务

SET autocommit = 1; -- 恢复自动提交
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

7、索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质：索引是数据结构。

7.1、索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个。

• 主键索引（PRIMARY KEY）
  • 主键的数据不可重复，只能有一个列作为主键
• 唯一索引（UNIQUE KEY）
  • 避免出现重复的数据，唯一索引可以有多个列
• 常规索引（KEY / INDEX）
  • 默认的索引
• 全文索引（FULLTEXT）
  • 快速定位数据

基础语法：

-- 显示所有的索引信息
SHOW INDEX FROM student_information;

-- 给指定的列增加一个全文索引
ALTER TABLE `student_information` ADD FULLTEXT INDEX `name` (`name`);

-- EXPLAIN 分析SQL语句执行的状况
EXPLAIN SELECT * FROM `student_information` WHERE MATCH(`name`) AGAINST('康');
1
2
3
4
5
6
7
8

模拟测试

-- 使用自定义函数插入100万条数据
DELIMITER $$ -- 将命令执行符;修改为$$
CREATE FUNCTION create_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `student_information`(`name`,`age`,`favorite`,`detail`,`pwd`) VALUES (CONCAT('用户',i),CEIL(RAND()*50),'学编程','健康',UUID());
		SET i = i + 1;
	END WHILE;
	RETURN i;
END

SELECT create_data()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

7.2、测试索引

-- 添加索引的语法：CREATE INDEX 索引名 ON 表名(字段)

-- 没有索引
SELECT * FROM `student_information` WHERE `name` = '用户9999' -- 执行4.523 sec

-- 给name增加索引
CREATE INDEX id_student_information_name ON `student_information`(`name`)

-- 有索引
SELECT * FROM `student_information` WHERE `name` = '用户9999' -- 执行0.347 sec
1
2
3
4
5
6
7
8
9
10

索引在数据量小的时候，区别不大，在大数据时候，区别十分明显~

7.4、索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上！

索引的数据结构

通常默认的数据类型为Hash

但INNODB默认的数据类型为BTREE

8、视图

8.1、视图的作用

为什么使用视图？

• 重用SQL语句。
• 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的细节。
• 使用表的组成部分而不是整个表。
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的范围权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

8.2、视图的使用

语法：

CREATE VIEW 创建视图

SHOW CREATE VIEW view_name； 查询创建的视图

DROP VIEW view_name; 删除视图

CREATE OR REPLACE VIEW; 更新视图

-- 在男性用户里，查询年龄为34，26,45的用户

-- 先创建一个只有男性用户的视图
CREATE VIEW male_account AS
SELECT `name` FROM `student_information`
WHERE `sex` = '男';

-- 在创建的视图里查询年龄为34,26,45的用户
SELECT si.`name` FROM male_account `ma`
INNER JOIN `student_information` `si`
ON `si`.`name` = `ma`.`name`
WHERE si.`age` IN (34,26,45)

-- 刚刚创建视图没有添加age字段，现修改重来
CREATE VIEW `male_account2` AS
SELECT `name`,`age` FROM `student_information`
WHERE `sex` = '男';

SELECT * FROM `male_account2` -- 查看视图

DROP VIEW `male_account2` -- 删除视图
-- 在创建的视图里查询年龄为34,26,45的用户
SELECT `name`,`age` FROM `male_account2`
WHERE `age` IN (34,26,45)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

8.3、视图的规则与限制

常见的规则：

• 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
• 对于可以创建的视图数目没有限制。
• 为了创建视图，必须具有足够的反问权限。这些限制通常有数据库管理人员授予。
• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
• ORDER BY 可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY 也将被覆盖。
• 视图不能索引，也不能有关联的触发器或默认值。
• 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

9、存储过程

9.1、存储过程的作用

为什么要使用存储过程？

优点：

• 通过吧处理封装在容易使用的单元中，简化复杂的操作。
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都是用同一（实验和测试）存储过程，则所使用的的代码都是相同的。

这一点得延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。

• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。

这一点的延伸就是安全性。通过存储过程限制对基础局的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。

• 提高性能。因为使用存储过程比使用单独的SQL语句要快。
• 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用他们来编写功能更强更灵活的代码。

换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。显然，他们都很重要。

缺点：

• 一般来说，存储过程的编写比基本SQL句复杂，编写存储过程需要更高的技能，更丰富的经验。
• 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

尽管有缺陷，存储过程还是非常有用的，并且应该尽可能地使用它。

9.2、存储过程的使用

语法：

– 创建存储过程

CREATE PROCEDURE name(

IN argument1, IN（传递给存储过程）

OUT argument2, OUT（从存储过程传出，返回给调用者）

… …

)

[BEGIN

​ SELECT … … FROM … … INTO …; INTO (将计算得到的值保存到相应的变量)

​ … …

END];

​

– 执行存储过程

CALL name（@argument1, 所有MySQL变量名必须以@

​ @argument2）;

DROP PROCEDURE name; – 删除存储过程

显示创建的存储过程

SHOW PROCEDURE name;

显示所有存储过程的详细信息

SHOW PROCEDURE STATUS;

DELIMITER //
CREATE PROCEDURE count_age(
 OUT min_age INT,
 OUT max_age INT,
 OUT avg_age INT
)
BEGIN
 SELECT MIN(age) FROM `student_information` INTO min_age;
 SELECT MAX(age) FROM `student_information` INTO max_age;
 SELECT AVG(age) FROM `student_information` INTO avg_age;
END//
DELIMITER ;

CALL count_age(@min,@max,@avg);

DROP PROCEDURE count_age;

SELECT @min,@max,@avg;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

10、游标

10.1、步骤

在MySQL中，游标只能用于存储过程（和函数）。

使用游标的步骤：

1. 在能够使用游标之前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
2. 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句吧数据实际检索出来。
3. 对于填有数据的游标，根据需要取出（检索）各行。
4. 在结束游标使用时，必须关闭游标。
5. 在声明游标后，可根据需要频繁的打开和关闭游标。在游标打开后，可根据需要频繁地执行取操作。

10.2、使用游标

创建游标

CREATE PROCEDURE name()

BIGIN

​ DELCARE 游标名称 CURSOR

​ FOR

​ SELECT … …; 要检索的语句

END;

打开，关闭游标

OPEN 游标名称；

CLOSE 游标名称；（如果不关闭，MySQL将会在END语句执行时关闭它）

从使用游标检索的数据中，取数据

FETCH 游标名称

11、触发器

11.1、步骤

• 唯一的触发器名；
• 触发器关联的表；
• 触发器应该形影的活动；（DELETE、INSERT、UPDATE）
• 触发器何时执行；（BEFORE、AFTER）

尽量保持每个数据库的触发器名唯一！

11.2、使用触发器

创建触发器

CREATE TRIGGER 触发器名称 AFTER ( BEFORE ) INSERT ( DELETE、UPDATE ) ON 表名

FOR EACH ROW 【SELECT … … 触发器激活后要执行的语句】

删除触发器

DROP TRIGGER 触发器名称；

触发器仅支持表（ 视图和临时表都不行 ）！！！

…

)

[BEGIN

​ SELECT … … FROM … … INTO …; INTO (将计算得到的值保存到相应的变量)

​ … …

END];

​

– 执行存储过程

CALL name（@argument1, 所有MySQL变量名必须以@

​ @argument2）;

DROP PROCEDURE name; – 删除存储过程

显示创建的存储过程

SHOW PROCEDURE name;

显示所有存储过程的详细信息

SHOW PROCEDURE STATUS;

DELIMITER //
CREATE PROCEDURE count_age(
 OUT min_age INT,
 OUT max_age INT,
 OUT avg_age INT
)
BEGIN
 SELECT MIN(age) FROM `student_information` INTO min_age;
 SELECT MAX(age) FROM `student_information` INTO max_age;
 SELECT AVG(age) FROM `student_information` INTO avg_age;
END//
DELIMITER ;

CALL count_age(@min,@max,@avg);

DROP PROCEDURE count_age;

SELECT @min,@max,@avg;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

10、游标

10.1、步骤

在MySQL中，游标只能用于存储过程（和函数）。

使用游标的步骤：

1. 在能够使用游标之前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
2. 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句吧数据实际检索出来。
3. 对于填有数据的游标，根据需要取出（检索）各行。
4. 在结束游标使用时，必须关闭游标。
5. 在声明游标后，可根据需要频繁的打开和关闭游标。在游标打开后，可根据需要频繁地执行取操作。

10.2、使用游标

创建游标

CREATE PROCEDURE name()

BIGIN

​ DELCARE 游标名称 CURSOR

​ FOR

​ SELECT … …; 要检索的语句

END;

打开，关闭游标

OPEN 游标名称；

CLOSE 游标名称；（如果不关闭，MySQL将会在END语句执行时关闭它）

从使用游标检索的数据中，取数据

FETCH 游标名称

11、触发器

11.1、步骤

• 唯一的触发器名；
• 触发器关联的表；
• 触发器应该形影的活动；（DELETE、INSERT、UPDATE）
• 触发器何时执行；（BEFORE、AFTER）

尽量保持每个数据库的触发器名唯一！

11.2、使用触发器

创建触发器

CREATE TRIGGER 触发器名称 AFTER ( BEFORE ) INSERT ( DELETE、UPDATE ) ON 表名

FOR EACH ROW 【SELECT … … 触发器激活后要执行的语句】

删除触发器

DROP TRIGGER 触发器名称；

触发器仅支持表（ 视图和临时表都不行 ）！！！