MySQL

本文主要是介绍MySQL，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MySQL

一、DB、DBMS、SQL之间的关系

DB:

存储数据的仓库。它保存了一系列有组织的数据
数据库在硬盘里以文件的形式存在

DBMS:

用来创建数据库并管理数据库的软件。
常见的有：mysql（关系型数据库）、oracle、DB2

SQL:

结构化查询语言，是一门标准通用的语言。适合所有数据库产品。

SQL语言属于高级语言，需要数据库软件(DBMS)编译完成

三者之间的关系：

二、DQL

含义：数据查询语言

理解：就是select语句

1.基础查询语言

1.1 基础语法

语法：select 查询列表 from 表名；

查询列表可以为字段，常量，表达式，函数

注意：

查询的结果是一个虚拟的表格

字符型和日期型的常量值需要用单引号引起来，而数值型则不需要

例如：

1.2 起别名

目的
提高可读性。
再之后的操作时，可以引用别名代指。

用法
方式一：字段 as 别名

方式二：字段别名

1.3 去重

目的：

在查询到的结果中去重

用法：

在字段前加distinct

注意：

只能对查询中的一个字段结果进行去重

1.4 “+”

注意：

mysql中只能做数值运算
只要有字符型，就会试图把字符转换为数字，如果转换失败了，就把字符转为0，并继续做加法运算
只要有一个为null，结果为null
运算符与null做运算，一般结果为null

2.条件查询

2.1 语法

2.2 筛选条件分类

2.2.1 条件表达式

2.2.2 逻辑表达式

逻辑表达式两侧为条件表达式

2.2.3 模糊查询(也是条件表达式)

like

一般和通配符进行搭配使用
%
表示0~n个字符

_
仅表示一个字符

支持转义
第一种方式：用“\”转义
第二种方式：用escape指定某符号具有转义功能

between and
作用
取区间范围内的值（包含临界值）

注意：
两个临界值不要调换顺序

in
作用
查询满足in范围其中任意元素的值

不支持in里有通配符的元素（通配符要用like），in相当于等于，like则是一个模糊的范围
in里的值要相互兼容，最起码得可以相互转换

is null
作用
判断字段是否为空

注意：
=或<>/!=是不可以判断null值

面试题：

3.排序

语法：

asc为升序、desc为降序

特点：

order by子句中可以支持单个字段、多个字段、表达式、函数、别名
按多个字段排序：

order by 一般是放在查询语句的最后面，limit子句除外

练习：

4.常用函数

语法：

select 函数名（实参列表）【from 表】（当函数名里的实参用到表中的字段）

分类：

单行函数
分组函数（聚合函数、统计函数）

1.单行函数

4.1 字符函数

语法：顾名思义，实参为字符

length

作用：获取参数值的字节个数

concat

作用：拼接字符串

upper、lower

将参数的字母转为大写或小写

substr

作用：截取字符串
注意：sql语言索引从1开始
语法：
截取从指定索引后面的所有字符

截取从指定索引处指定字符长度的字符

小综合练习：

instr

作用：返回子串第一次出现的索引，如果找不到返回0

trim

作用：

如果不指明去除的字符，则默认去掉字符的前后空格

如果指明去除的字符，则去除字符前后的指定字符

lpad

作用：用指定的字符实现左填充指定长度

rpad

用指定的字符实现右填充指定长度

replace

作用：将指定字符进行全部替换

4.2 数学函数

round
作用：四舍五入

ceil
作用：向上取整，返回大于等于该参数的最小整数

floor
作用：向下取整，返回小于等于该参数的最大整数

truncate
作用：截断：小数点后保留几位小数

mod
作用：取模

4.3 日期函数

注意：实参是日期，所以要加引号

返回当前时间

获取时间的一部分

转换

格式符

str_to_date

作用：给定字符串（日期可以不是正常顺序），指定格式，转换为正常顺序的日期

date_format

给定的日期一定要是正常顺序，按格式转换成字符串

4.4 流程控制函数

if函数

作用：if else效果
case结构
注意：

如果是表达式则不需要加分号，如果是语句则需要加分号。
表达式可以出现在begin end里也可以出现在外面。而语句只能出现来begin end里面。
表达式结尾直接用end，语句用end case结尾。

用法一：相当于switch case
用法二：相当于多重if else
案例：
查询成绩对应的级别
delimiter $
CREATE PROCEDURE getgrade(IN score INT)
BEGIN
	CASE
	WHEN score >= 90 THEN SELECT 'A';
	WHEN score >= 80 THEN SELECT 'B';
	WHEN score >= 60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;
END $
delimiter ;
CALL getgrade(78);

4.5 常用函数的总练习

练习：

2.聚合函数

理解：对多个记录进行统计，获取统计结果

1.简单使用：

2.支持一次性查询多个聚合函数

3.特点

sum、avg用于处理数值型
max、min、count可以处理任何类型
所有的分组函数都忽略null值
分组函数实参里可以放distinct

和分组函数一同查询的字段要求是group by后的字段
或者是要查询的字段里只有分组函数

count

统计行数
一般使用count(*)用作统计行数
count(常数)，相当于把常数加到每一行上，然后再统计常数的个数

5.分组查询

语法：

特点：

注意：

分组查询的查询列表一定是聚合函数或者是group by后出现的字段
group by支持单个字段、多个字段、表达式、函数

例题：

简单的分组查询

添加分组前的筛选

再分组后进行筛选（having）

复杂的sql：先写最基础的，之后再加筛选条件

6.连接查询

作用：当查询的字段来自于多个表时

分类：

按年代分类
sql92标准：仅支持内连接
sql199标准【推荐】：不支持全外连接

按功能分类：
内连接
等值连接
非等值连接
自连接

外连接
左外连接
右外连接
全外连接

交叉连接

6.1 sql92

6.1.1 等值连接

特点

多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表查询的结果也是一张表，支持排序、分组、条件查询等

#1.查询女神名和对应的男神名
SELECT name,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id

#2.多表联查频繁访问相同字段时，需要频繁指明表名，这样的话可以起别名
SELECT last_name,e.job_id,job_title
from employees e,jobs j
WHERE e.job_id = j.job_id（连接条件）

#3.再等值连接的基础上，加筛选条件
SELECT department_name,city
from locations,departments
WHERE departments.location_id = locations.location_id
AND city like '_o%'

#4.加上分组查询
SELECT count(department_id) 
from locations l,departments d
WHERE l.location_id = d.location_id
GROUP BY city

#5.支持排序
SELECT count(*),j.job_title
from employees e,jobs j
WHERE e.job_id = j.job_id
group by job_title
ORDER BY count(*) DESC

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

6.1.2 非等值连接

理解：就是把where里的等值条件换成其他条件

SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal and g.highest_sal

6.1.3 自连接

理解：顾名思义就是自己连接自己

SELECT e1.last_name 员工,e2.last_name 领导
from employees e1,employees e2
WHERE e1.manager_id = e2.employee_id

6.2 sql99

语法：

 select 查询列表
 from 表1 别名
 【连接类型】join 表2 别名
 on 连接条件
 【where 筛选条件】
 【group by 分组】
 【having 筛选条件】
 【order by 排序列表】

6.2.1 内连接

语法：

select 查询列表

from 表1 别名

inner join 表2 别名

on 连接条件

等值连接

特点
1. inner 可以省略不写
2. 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
3. inner join 和sql92语法中的等值连接效果是一样的，都是查询多表的交集

案例：

#1.查询员工名、部门名
SELECT last_name,department_name
FROM departments d
INNER JOIN employees e
on e.department_id = d.department_id
                                                                                                                                                                                                                                                                                                      
#2.查询名字中包含e的员工名和工种名（添加筛选条件）
SELECT last_name,job_title
from employees e
INNER join jobs j
on e.job_id = j.job_id
WHERE e.last_name like '%e%'
                                                                                                                                                                                                                                                                                                      
#3.查询部门个数 >3的城市名和部门个数（添加分组+筛选）
SELECT city,COUNT(department_id)
FROM departments d
INNER JOIN locations l
on d.location_id = l.location_id
GROUP BY city
HAVING count(department_id) > 3
                                                                                                                                                                                                                                                                                                      
#4.查询哪个部门的员工个数 > 3的部门名和员工个数，并按个数降序（分组+筛选+排序）
SELECT department_name,COUNT(*)
from employees e
INNER join departments d
on e.department_id = d.department_id
GROUP BY
e.department_id
HAVING count(*) > 3
ORDER BY COUNT(*) DESC
                                                                                                                                                                                                                                                                                                      
#5.查询员工名、部门名、工种名，并按部门名降序（三表连接+降序）
SELECT last_name,department_name,job_title
from employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN jobs j
on e.job_id = j.job_id
ORDER BY department_name DESC

非等值连接

#查询是此工资级别的人的个数> 20的情况，并按工资级别降序
SELECT count(*),grade_level
FROM employees e
INNER join job_grades g
on e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING count(*) > 20
ORDER BY grade_level DESC

自连接

#查询姓名中包含字符k的员工名字以及上级的名字
SELECT e1.last_name,e2.last_name
FROM employees e1
JOIN employees e2
ON e1.manager_id = e2.employee_id
WHERE e1.last_name LIKE '%k%'

6.2.2 外连接

应用场景：

用于查询一个表中有，另一个表没有的记录

特点：
外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值

如果从表中没有和它匹配的，则显示null值

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

2.左外连接（outer可以省略）

left join 左边的是主表
#1.查询不在男神.表的女神名
SELECT b.`name`,bo.boyName
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id
右外连接
right join 右边的是主表（outer可以省略）
#1.查询哪个部门没有员工
SELECT *
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id
全外连接（mysql不支持）

理解：全外连接=内连接结果+表1中有但表2没有的+表2中有但表1没有

关键字：full outer join（outer可以省略）

交叉连接

理解：实际上就是笛卡尔乘积

关键字：cross join

6.3 sql92与sql99的区别

功能：sql99支持的较多

可读性：sql99实现了连接条件和筛选条件的分离，可读性较高

7.子查询

理解：套娃，select语句再嵌套select语句

分类：

行子查询可以多行多列，只不过一行多列的情况比较多

7.1 where/having后面

支持：

标量子查询（单行子查询）
列子查询（多行子查询）
行子查询（多行多列）

特点：

子查询放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配着单行操作符使用
单行操作符：> < >= <= = <>

列子查询，一般搭配着多行操作符使用
多行操作符：in、any/some、all

子查询要比主查询要早，因为子查询的结果要作为主查询的条件

7.1.1 标量子查询

案例：

#1.查询比Abel工资高的员工信息
SELECT * 
FROM employees
WHERE
salary > (
					SELECT salary
					FROM employees
					WHERE last_name = 'Abel'
)

#2.返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = 
#子查询查出与141号员工的job_id
(
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
)
#查出比143号员工的工资多的员工
AND salary >
(
		SELECT salary
		FROM employees
		WHERE employee_id = 143
)

#3.返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = 
(
	SELECT MIN(salary)
	FROM employees
)

#4.查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > 
(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
)

7.1.2 列子查询

列子查询（多行子查询）搭配多行操作符使用
多行操作符
- 操作符含义
  IN/NOT IN 等于列表中的任意一个
  ANY/SOME 和子查询返回的某一个值比较（只要比较一个满足条件就行了）
  ALL 和子查询返回的所有值比较
案例

操作符	含义
IN/NOT IN	等于列表中的任意一个
ANY/SOME	和子查询返回的某一个值比较（只要比较一个满足条件就行了）
ALL	和子查询返回的所有值比较

#1.返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN
(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
)

#2.返回其他工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY
(
	SELECT salary
	FROM employees
	WHERE job_id= 'IT_PROG'
) AND job_id <> 'IT_PROG'

#3.返回其他工种中比job_id为‘IT_PROG’工种所有工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL
(
	SELECT salary
	FROM employees
	WHERE job_id= 'IT_PROG'
) AND job_id <> 'IT_PROG'

7.1.3 行子查询

用的较少，不做讨论

7.2 select后面

7.2.1 标量子查询

案例：

#1.查询每个部门的员工个数
SELECT
(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.department_id
)
FROM departments d

7.3 from后面

7.3.1 表子查询

注意：

将子查询充当一张表，因为它不是真实存在的，为了引用里面的字段，所以一定要起别名

案例：

7.4 exists后面

语法：exists(完整的查询语句)

结果：0或1

注意：先执行主查询，再执行exists

案例：
#1.查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS
(
	SELECT * 
	FROM employees e
	WHERE d.department_id = e.department_id
)

7.5 综合练习

#1.查询和Zlotkey相同部门的员工姓名和工资(子查询在where后面)
SELECT last_name,salary
FROM employees
WHERE department_id = 
(
	SELECT department_id
	FROM employees
	WHERE last_name = 'Zlotkey'
)

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资（子查询在where后面）
SELECT employee_id,last_name,salary
FROM employees
WHERE salary >
(
	SELECT AVG(salary)
	FROM employees
)

#3.查询各部门中工资比本部平均工资高的员工的员工号，姓名和工资（子查询在from后面）
SELECT employee_id,last_name,salary
FROM employees e
INNER JOIN
(
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE e.salary > ag_dep.ag

#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名（子查询在where后面）
SELECT employee_id,last_name
FROM employees
WHERE department_id IN 
(
	SELECT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
)

#5.查询在部门的location_id为1700的部门工作的员工的员工号（子查询在where后面）
SELECT employee_id
FROM employees
WHERE department_id = ANY 
(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id = 1700
)

#6.查询管理者是K_ing的员工姓名和工资（内连接）
SELECT e1.last_name,e1.salary
FROM employees e1
INNER JOIN employees e2
ON e1.manager_id = e2.employee_id
WHERE e2.last_name = 'K_ing'

#7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓.名（子查询在where后面）
SELECT CONCAT(first_name,last_name) '姓.名'
FROM employees
WHERE salary = 
(
	SELECT MAX(salary)
	FROM employees
)

8.分页查询

语法：

特点：

limit语句放在查询语句的最后面

分页下标从0开始

动态分页的公式：

要显示的页数page，每页的条目数size

limit （page-1）* size，size
#1.查询前五条员工的信息
SELECT *
FROM employees
LIMIT 0,5

#2.有奖金的，且工资为前10的员工
SELECT * 
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 0,10

9.联合查询

语法：
查询语句 1
union
查询语句2
union
...
应用场景：要查询的结果来自多个表，且多个表没有直接的连接关系，但这几个表查询的信息一致时

特点：

union就是取并集
要求多条查询语句的查询列数是一致的
要求多条查询语句的查询的每一列的类型和顺序最好一致
union关键字默认去重，如果想不去重，则可以使用union all

案例：
#1.查询部门编号>90或邮箱包含a的员工信心
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id > 90

10.执行顺序

总结：

三、DML

1.插入语句

语法一：

insert into 表名（列名，…）values（值1，…）；

特点：

插入的值得类型要与列的类型一致或兼容

不可以为null的列必须插入值，可以为null的列可以插入null值或插入有效值

列的顺序可以调换，但是列要与值一一对应上

列数和值的个数必须一致

5.可以省略列名，但默认为所有列，而且列的顺序和表中列的顺序一致

6.支持子查询

语法二：

insert into 表名
set 列名=值，列名=值，....

案例：

INSERT INTO beauty
SET id=19,NAME='白梦妍',phone='000'

两种插入语法的区别
语法一支持批量插入，语法二不行
语法一支持子查询，语法二不支持
INSERT INTO beauty(id,Name,phone)
SELECT 26,'宋茜','5422154'

2.修改语句

2.1 修改单表的记录

语法：

update 表名
set 列=新值，列=新值，...
where 筛选条件;

案例：

#1.修改beauty中姓唐的女神的电话为....（修改单个字段值）
UPDATE beauty
SET phone = '13899888899'
WHERE name LIKE '唐%'

#2.修改boys中id为2的名称为张飞，魅力值10(修改多个字段值)
UPDATE boys 
SET boyname='张飞',usercp = 10
WHERE id = 2

2.2 修改多表的记录（了解）

语法：

sql92:
update 表1 别名，表2 别名
set 列=值，...
where 连接条件
and 筛选条件

sql99:
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值，...
where 筛选条件；

案例：

#1.修改张无忌的女朋友的手机号为113
UPDATE boys bo
INNER JOIN beauty b
ON bo.id = b.boyfriend_id
SET b.phone = '113'
WHERE bo.boyName='张无忌'

3.删除语句

3.1 delete

语法：

delete from 表名【where 筛选条件】【limit 条目数】

3.1.1 单表删除

#1.删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9'

3.1.2 多表的删除

sql92语法：

#要删除一个表的数据，就写那个表对应的名字或别名
delete 【表1的别名，表2的别名】
from 表1 别名，表2别名
where 连接条件
and 筛选条件

案例：

DELETE b,bo 
FROM boys bo,beauty b
WHERE bo.id = b.boyfriend_id
AND bo.name = '张无忌'

sql99语法：

#要删除一个表的数据，就写那个表对应的名字或别名
delete 【表1的别名，表2的别名】
from 表1 别名
inner | left | right join 表2 别名 
on 连接条件
where 筛选条件

案例：

#多表连接，删除单个表的某个记录
DELETE b 
FROM boys bo
INNER JOIN beauty b
ON bo.id = b.boyfriend_id
WHERE bo.name = '张无忌'

#多表连接，删除多个表的某个记录
DELETE b,bo 
FROM boys bo
INNER JOIN beauty b
ON bo.id = b.boyfriend_id
WHERE bo.name = '张无忌'

3.2 truncate

作用:

清空整个表的数据

注意：

删除某个表时不用加条件

案例：
truncate table boys

3.3 delete与truncate的区别

区别：

delete后面可以加where条件，truncate却不能

如果设置表中的一个字段为自增，delete删除数据后，再插入数据，自增值会从断点开始

而truncate删除后，再插入数据，自增的值从1开始

truncate删除没有返回影响几行行数的返回值，delete有

truncate删除不能回滚，delete删除可以回滚

四、DDL

作用：对数据库和表的管理

关键字：

创建：create

删：drop

改：alter

1. 对库的操作

创建库语法：
#1.加上if not exists增强了容错性
create database 【if not exists】 数据库名
修改库：

库创建后，一般不会去做修改，因为轻易修改库结构，有很多数据容易崩掉
#1.修改库的字符集为gbk
alter database 数据库名 character set gbk
删除库

语法：drop database 【if exists】数据名

2. 对表的操作

1.创建表：

create table 表名（
	列名 列的类型【长度，约束】，
	列名 列的类型【长度，约束】，
	列名 列的类型【长度，约束】
	...
）

案例：

CREATE TABLE book
(
    id int,
    name varchar(20)
)

2.修改表：

语法：

alter table 表名 add|drop|modify|change column 列名【列类型约束】；

约束：

列级约束：

语法：alter table modify/add column 字段名字段类型新约束

表级约束

语法：alter table 表名 add/modify 【constraint 约束名】约束类型（字段名）

约束删除操作：

案例：
#1.修改字段的名字
ALTER TABLE book
CHANGE COLUMN name bname VARCHAR(10)

#2.修改字段的类型或约束
ALTER TABLE book
MODIFY COLUMN bname char(20)

#3.添加字段
ALTER TABLE book
ADD COLUMN author VARCHAR(10)

#4.删除字段
ALTER TABLE book
DROP COLUMN author

#5.修改表名
ALTER TABLE book
RENAME TO books
3.删除表
#1.删除表
DROP TABLE IF EXISTS book
4.复制表
#1.仅仅复制表的结构
CREATE TABLE copy LIKE book

#2.复制表的字段+全部数据
CREATE TABLE copy2 
SELECT * FROM author

#3.复制部分字段、数据
CREATE TABLE copy3
SELECT id,name
FROM author
WHERE nation='中国'

#4.仅仅复制某些字段，不复制数据
CREATE TABLE copy3
SELECT id,name
FROM author
WHERE 0（恒不成立即可）

3. mysql中的数据类型

整型
小数
定点数
浮点数

字符型
较短：char、varchar
较长：text、blob

日期型

3.1 整型

分类

tinyint：1字节
smallint：2字节
mediumint：3字节
int/integer：4字节
bigint：8字节

特点：

如果不设置无符号还是有符号，默认是有符号，如果要设置无符号，需要在类型后面加unsigned
插入数据超过类型的范围，会有错误，然后插入表格的数据是临界值
如果不指明整型的长度，会有默认长度。
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

3.2 浮点型

分类

浮点数
float（M,D）
double（M,D）

定点数（精度更高）
dec(M,D)/decimal(M,D)

解释M,D
M
整数部分加小数部分一共的总位数，若超过了，则整数部分和小数部分分别取各自的临界值

D
小数后保留几位小数，若小数超过了设定的，那么就会四舍五入

默认值
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度（换句话说插啥都行）

3.3 字符型

特点：

写法 M的意思特点空间的耗费效率
char char（M）最大的字符数，可以省略，默认为1 固定长度的字符比较耗费高
varchar varchar（M）最大的字符数，不可以省略可变长度的字符（根据定义的大小分配对应大小的空间）比较节省低

	写法	M的意思	特点	空间的耗费	效率
char	char（M）	最大的字符数，可以省略，默认为1	固定长度的字符	比较耗费	高
varchar	varchar（M）	最大的字符数，不可以省略	可变长度的字符（根据定义的大小分配对应大小的空间）	比较节省	低

3.4 日期型

分类：

日期型存储范围
date 日期
time 时间
year 年
datetime 日期+时间
timestamp 日期+时间

datetime与timestamp区别：

字节范围是否受时区影响
datetime 8 1000-9999 不受
timestamp 4 1970-2038 受

总结：

如果这两个都能用，优先用timestamp。

1.timestamp的存储空间更小

2.timestamp受时区的影响而发生改变，这样换了时区，时间也会变化成对应时区的。

日期型	存储范围
date	日期
time	时间
year	年
datetime	日期+时间
timestamp	日期+时间

	字节	范围	是否受时区影响
datetime	8	1000-9999	不受
timestamp	4	1970-2038	受

3.5 其他

其他：

其他作用
binary、varbinary 用于保存较短的二进制
enum 用于保存枚举
set 用于保存集合

其他	作用
binary、varbinary	用于保存较短的二进制
enum	用于保存枚举
set	用于保存集合

4. 常见约束

理解：

对表的数据进行限制，为了保证表中的数据的准确和可靠性

约束作用
NOT NULL 保证该字段不能为空
DEFAULT 保证该字段有默认值
PRIMARY KEY 保证该字段的值具有唯一性、非空
UNIQUE 保证该字段的值具有唯一性，但可以为空
CHECK 检查该字段值是否符合设定的要求（mysql不支持，但不会报错）（mysql8支持）
FOREGIN KEY 用于限制两个表的关系，保证该字段的值必须来自于主表的对应列的值。在从表添加外键约束，用于引用主表中某列的值

主键与唯一键的区别

唯一性是否为空一个表可以有几个是否允许组合
主键是否至多有一个是
unique 是是可以有多个是

外键：

注意点：

要求在从表设置外键关系
从表的外键类型和主表的关联类的类型要求一致或兼容，名称无要求
主表的关联列必须是一个key（一般是主键或unique）
插入数据是，先插入主表，再插入从表。删除数据时，先删除从表，再删除主表

分类：
列级约束：

不支持check约束和外键约束

案例：
# 列级约束支持这4个约束
CREATE TABLE stuinfo(
    id INT PRIMARY kEY,
    name VARCHAR(10) NOT NULL UNIQUE,
    gender char(1),
    age DEFAULT 18 NOT NULL,
    seat UNIQUE
)
表级约束

除了非空、default约束、check约束，其他都支持
语法：【CONSTRAINT 约束名】约束类型(字段名)

案例：
CREATE TABLE stuinfo(
    id INT,
    name VARCHAR(20),
    seat INT,
                                                        
	CONSTRAINT pk PRIMARY KEY(id),
    CONSTRAINT uq UNIQUE(seat),
    CONSTRAINT fk_从表名_主表名 FOREIGN KEY(majorid) REFERENCES 主表名(id) 
)

约束	作用
NOT NULL	保证该字段不能为空
DEFAULT	保证该字段有默认值
PRIMARY KEY	保证该字段的值具有唯一性、非空
UNIQUE	保证该字段的值具有唯一性，但可以为空
CHECK	检查该字段值是否符合设定的要求（mysql不支持，但不会报错）（mysql8支持）
FOREGIN KEY	用于限制两个表的关系，保证该字段的值必须来自于主表的对应列的值。在从表添加外键约束，用于引用主表中某列的值

	唯一性	是否为空	一个表可以有几个	是否允许组合
主键	是	否	至多有一个	是
unique	是	是	可以有多个	是

5 标识列（自增长列）

功能：可以不用手动的插入值，系统提供默认的序列值

语法：在字段的后面加auto_increment

案例：
CREATE TABLE test(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
特点

自增长列一定要和一个key搭配
一个表至多有一个自增长列
标识列的类型只能是数值型
标识列可以通过 set auto_increment_increment=3设置步长
可以通过手动插入值，设置起始值

标识列的相关操作

五、TCL

1. 事务

定义：

事务由单独单元的一个或多个sql语句组成，在这个单元中，每个mysql语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条sql语句一旦执行失败，整个单元将会回滚，所有受到影响的数据将会返回到事务开始以前的状态；如果单元中的所有sql语句均执行成功，则事务被顺利执行

理解：

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部都不执行

特点：

原子性

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性

事务的执行会使数据从一个一致性状态变换到另外一个一致性状态。

隔离性

一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰（具体看隔离级别）

持久性

一个事务一旦被提交，它对数据库中数据的改变就是永久性的，不能进行回滚操作

分类：

隐式事务：
事务没有明显的开启和结束的标记。比如insert、update、delete语句（每一条就是一个事务）。

显示事务：
事务需要手动开启和手动创建

1.1 显示事务的创建

步骤：

先将事务自动提交功能禁用：set autocommit=0；
开启事务 start transaction（可选，写了set autocommit=0；自动开启了事务）
编写事务中的sql语句（只能是select、insert、update、delete）
结束事务
commit 提交事务
rollback 回滚事务

案例：
#事务的使用步骤
#1.开启事务
SET autocommit=0；
START TRANSACTION;（可省）
#2.在这个事务中编写sql语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';
#3.结束事务（根据情况选择回滚还是提交）
#ROLLBACK;#回滚
COMMIT;#提交

1.2 常见的并发问题

脏读

当事务T1对表进行操作但未提交时，事务T2从表中读取数据，这时读取的数据时T1对表进行操作后的数据，万一T1选择回滚，这时数据就会不一致

不可重复读

当事务T1在表中读取数据未提交，T2对表进行了修改操作已提交，如果T1在读取表中数据，这时数据就会不一致

幻读

当事务T1在查询数据时，T2对表进行插入数据的操作并提交，T1再次查询数据，会发现数据多了。

1.3 事务的隔离级别

目的：解决对应的并发问题

注意：

数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱，性能也会越差，所以隔离级别应视情况而定。

mysql中事务的隔离级别

读未提交数据
读已提交数据
可重复读（默认）
串行化

1.4 回滚点

作用：就是回滚到指定位置

案例：

SET autocommit = 0;
START TRANSACTION;
DELETE FROM account WHERE id = 25;
SAVEPOINT a;#设置回滚点
DELETE FROM account WHERE id = 28;
ROLLBACK TO a;#回滚到回滚点，也就是只删除了id为25的数据，id为28的数据没有删除

六、视图

理解：

视图是一种虚拟存在的表，是在使用时动态生成的，因为只保存了sql语句，没有保存查询结果

应用场景：

多个地方用到同样的查询结果，该查询结果使用的sql语句较复杂

1. 创建视图

语法：

create view 视图名 as 查询语句；

案例：
#1.查询姓名中包含a字符的员工名、部门名和工种信息
①创建视图
CREATE VIEW info_a
AS
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN jobs j
ON e.job_id = j.job_id
②在视图中添加筛选条件获取对应数据
SELECT *
FROM info_a（视图名）
WHERE last_name LIKE '%a%'
好处：

重用sql语句
简化复杂的sql操作，不必知道它的查询细节（把一些复杂的sql语句封装到视图里了）
保护数据，提高安全性（只提供给视图，不会知道原始表的具体信息）

2. 修改视图

方式一：

create or replace view 视图名 as 查询语句

案例：
CREATE OR REPLACE VIEW myview
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
方式二：

alter view 视图名 as 查询语句；

案例：
ALTER VIEW myview
AS
SELECT * FROM employees;

3.删除视图

作用：支持连续删除多个视图

语法：drop view 视图名1，视图名2，…；
DROP VIEW myview1,myview2;

4.更新视图数据（很少用）

语法：

insert、update、delete也适用于视图，且语法和普通表一样。修改视图的属性值，原始表的属性值也会改变

案例：
#1.向视图插入数据
INSERT INTO myview VALUES('张飞'，‘zf@qq.com’);

#2.向视图修改数据
UPDATE myview 
SET last_name='张无忌'
WHERE last_name='张飞';

#3.删除视图中的数据
DELETE FROM myview
WHERE last_name = '张无忌';
以下情况视图数据不能更新：

5.视图和表的区别

创建语法的关键字是否实际占用物理空间使用
视图 view 只是保存了sql逻辑增删改查，但是增删改很少用
表 table 保存了表里的数据增删改查

	创建语法的关键字	是否实际占用物理空间	使用
视图	view	只是保存了sql逻辑	增删改查，但是增删改很少用
表	table	保存了表里的数据	增删改查

七、变量

分类：

系统变量
全局变量
会话变量
自定义变量
局部变量
用户变量

1.系统变量

说明：

系统变量是由系统提供的，不是用户定义，属于服务器层面

注意：

如果是全局级别，则需要加global，如果是会话级别，需要加session，如果不写，则默认session

语法功能
show global | session variables 查看系统变量
show global | variables like ‘%变量所包含的字符%’ 查看满足条件的部分系统变量
select @@global | session .系统变量名查看指定系统变量
set global | session 系统变量名=值
set @@global | session .系统变量名=值为某个系统变量赋值

语法	功能
show global \| session variables	查看系统变量
show global \| variables like ‘%变量所包含的字符%’	查看满足条件的部分系统变量
select @@global \| session .系统变量名	查看指定系统变量
set global \| session 系统变量名=值 set @@global \| session .系统变量名=值	为某个系统变量赋值

1.1 全局变量

作用域：

服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但重启了，就无效了
#1.查看所有全局变量
SHOW GLOBAL VARIABLES;
#2.查看部分全局变量
SHOW GLOBAL VARIABLES LIKE '字符'
#3.查看指定的全局变量
SELECT @@global.autocommit
#4.给指定全局变量赋值
SET @@global.autocommit=0;
SET global autocommit=0;

1.2 会话变量

作用域：

仅对当前会话（连接）有效

#1.查看全部会话变量
SHOW [SESSION] VARIABLES;
#2.查看部分会话变量
SHOW [SESSION] VARIABLES LIKE '字符';
#3.查看指定的会话变量
SELECT @@[session.]变量名;
#4.修改指定的会话变量
SET @@[session.]变量名=值;
SET [session] 变量名=值

2.自定义变量

含义：用户自己定义的变量

2.1 用户变量

作用域：

在当前会话（连接）中有效

用户变量的使用步骤：
声明并初始化

set @用户变量名=值
set @用户变量名:=值
select @用户变量名:=值
修改变量值
注意：mysql中用户变量的赋值是弱类型，赋给什么值就是什么类型（可以赋值为任意类型的数据）
方式一：
set @用户变量名=值

set @用户变量名:=值

select @用户变量名:=值
案例：
set @count=1;
set @count:='hh';
方式二：
select 字段 into @用户变量名 from 表;
案例：
select count(*) into @count from employee
查看用户变量的值

select @用户变量名
案例
SET @a = 1;
SET @b = 2;
SET @sum = @a+@b;
SELECT @sum;

2.2 局部变量

作用域：

仅仅在定义它的begin end中有效

局部变量的使用步骤：

声明

只声明不初始化：declare 变量名类型；
声明并初始化：declare 变量名类型 default 值;

赋值

方式一：
set 局部变量名=值
set 局部变量名:=值
select @局部变量名:=值

方式二：
select 字段 into 局部变量名 from 表;

查看局部变量的值

select 局部变量名;

2.3 用户变量与局部变量的区别

作用域定义个使用的位置语法
用户变量当前会话会话中的任何地方必须加@符号，不用限制类型，需要赋值
局部变量 begin end 只能在begin end中，且为第一句话一般不加@符号，需要限定类型，可以不用赋值

	作用域	定义个使用的位置	语法
用户变量	当前会话	会话中的任何地方	必须加@符号，不用限制类型，需要赋值
局部变量	begin end	只能在begin end中，且为第一句话	一般不加@符号，需要限定类型，可以不用赋值

八、存储过程

理解：

一组预先编译好的sql语句的集合

好处：

提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

1. 创建存储过程

1.创建语法：
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体(一组sql语句)
END
注意：
参数列表包含三部分

参数模式参数名参数类型
参数模式：
IN:该参数可以作为输入，需要传入值
OUT:该参数可以作为输出，也就是可以作为返回值
INOUT:既需要传入参数，又可以有返回值

如果存储过程仅仅只有一句话，begin end 可以省略

存储过程体中格的每条sql语句的结尾必须要加分号
存储过程的结尾可以使用delimiter

语法：delimiter 结束标记
目的：mysql每遇到一个分号就会执行对应的sql语句，存储过程体也有好多分号，为了想整体执行存储过程体，所以需要修改分隔符
流程：
//分隔符设为$
delimiter $
存储过程
//执行完存储过程后再切换为;
delimiter ;
2.调用语法

call 存储过程名(实参列表);

1.1 空参的存储过程

案例：

#创建存储过程
delimiter $
create procedure girl_idols()
BEGIN
	INSERT INTO beauty(name)
	VALUES('杨紫'),('李沁'),('白鹿');
END $

#调用空参的存储过程
call girl_idols()$

1.2 带in模式的存储过程

案例：

#创建带参数的存储过程
#查询女神名对应的男神的全部信息
delimiter $
CREATE PROCEDURE getBoy(IN girlName VARCHAR(20))
BEGIN
	SELECT * 
	FROM boys
	WHERE id = (
	SELECT boyfriend_id
	FROM beauty
	WHERE `name` = girlName
);
END $;

#调用存储过程
CALL getBoy('赵敏')$

#创建多个参数的存储过程
delimiter $
create procedure login(in username varchar(20),in password varchar(20))
begin
	#声明一个局部变量，并把最终结果赋给这个局部变量
	declare result varchar(20);
	#将赋给result变量
	select count(*) into result
	from admin
	where admin.username = username  #参数名和表里的字段有冲突，所以需要再前面加上表名
	and admin.password = password;
	
	select result;
end $

1.3 带out模式的存储过程

案例：

#带一个返回值的
#查询女神名对应的男神名
delimiter $
CREATE PROCEDURE boyfriend(IN girlName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM beauty b
	LEFT JOIN boys bo
	ON b.boyfriend_id = bo.id
	WHERE b.name = girlName;
END $
#调用的第一种传参方式
set @bName = ''$
call boyfriend('赵敏',@bName)$
select @bName$

#带多个返回值的
#查询女神名对应的男神名和男神魅力值
delimiter $
CREATE PROCEDURE boy_friend(IN girlName VARCHAR(20),OUT boyName VARCHAR(20),out userCP int)
BEGIN
	#给多个返回值赋值
	SELECT bo.boyName,bo.userCp INTO boyName,userCP
	FROM beauty b
	LEFT JOIN boys bo
	ON b.boyfriend_id = bo.id
	WHERE b.name = girlName;
END $
#调用的第二种传参方式
call boy_friend('赵敏',@bName,@usercp)$
select @bName,@usercp$

1.4 带inout模式的存储过程

案例：

#传入a和b两个值，a和b翻倍并返回
CREATE PROCEDURE doubleNum(INOUT a INT,INOUT b INT)
BEGIN
	SET a = a * 2;
	SET b = b * 2;
END;

#调用
SET @a = 1;
SET @b = 2;
CALL doubleNum(@a,@b);
SELECT @a,@b;

1.5 练习

#1.创建存储过程实现传入用户名和密码，插入到admin表中
CREATE PROCEDURE insertAdmin(IN userName varchar(20),IN password VARCHAR(20))
BEGIN
	#参数名与admin表中的字段相同，所以加admin限制一下
	INSERT INTO admin(username,admin.password)
	VALUES(userName,password);
END;
CALL insertAdmin('hh','123');

#2.创建存储过程实现传入女神编号，返回女神名称和女神电话
CREATE PROCEDURE girlInfo(IN id INT,OUT girlName VARCHAR(10),phone VARCHAR(20))
BEGIN
	SELECT name,beauty.phone INTO girlName,phone
	FROM beauty
	WHERE id = beauty.id;
END;
CALL girlInfo(3,@name,@phone);
SELECT @name,@phone;

#3.创建存储过程实现传入两个女神生日，返回大小
CREATE PROCEDURE compareAge(IN birthday1 datetime,IN birthday2 datetime,OUT result VARCHAR(20))
BEGIN
	DECLARE num INT DEFAULT(DATEDIFF(birthday1,birthday2));
	SET result = 
	CASE 
	WHEN num < 0 THEN '前者大'
	WHEN num = 0 THEN '生日一样'
	WHEN num > 0 THEN '后者大'
	END;
END;
CALL compareAge('1935-5-11','1368-9-15',@result);
SELECT @result;

#4.实现传入一个日期，格式化成xxxx年xx月xx日并返回
CREATE PROCEDURE formatdate(IN mydate datetime,OUT dateformat VARCHAR(20))
BEGIN
	SELECT DATE_FORMAT(mydate,'%Y年%m月%d日') INTO dateformat;
END;
#调用
CALL formatdate(NOW(),@str);
SELECT @str;

#5.实现传入女神名称，返回格式：女神 and 男神
CREATE PROCEDURE gb(IN girlName VARCHAR(10),OUT result VARCHAR(20))
BEGIN
	SELECT CONCAT(b.name,' and ',bo.boyName) INTO result
	FROM beauty b
	INNER JOIN boys bo
	ON b.boyfriend_id = bo.id
	WHERE b.name = girlName;
END;
CALL gb('赵敏',@result);
SELECT @result;

#6.根据条目数和起始索引实现分页查询
CREATE PROCEDURE page(IN pageindex INT,IN size INT)
BEGIN
	SELECT * FROM beauty
	LIMIT pageindex,size;
END;
CALL page(1,3);

2.存储过程的删除

语法：

drop procedure 存储过程名;

3.存储过程的查看

语法：

show create procedure 存储过程名;

九、函数

理解：

一组预先编译好的sql语句的集合

好处：

提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

函数与存储过程的区别

存储过程：可以有0个返回值，也可以有多个返回值，适合做批量插入、更新

函数：有且仅有1个返回值，适合做处理数据后返回一个结果

详细区别

1. 函数的创建

语法：
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END
注意：
参数列表包括参数名、参数类型两个部分

函数体必须有return语句

return语句没有放在函数体的最后也不会报错，但不建议

函数体中只有一句话，可以省略begin end
可以使用delimiter语句设置结束标记

语法：delimiter 结束标记
目的：mysql每遇到一个分号就会执行对应的sql语句，存储过程体也有好多分号，为了想整体执行函数体，所以需要修改分隔符
流程：
//分隔符设为$
delimiter $
函数
//执行完函数后再切换为;
delimiter ;

2. 函数的调用

语法：

select 函数名(参数列表)

案例：

#无参函数
#返回员工个数
delimiter $
CREATE FUNCTION num_employees() RETURNS INT
BEGIN
	DECLARE num INT;
	SELECT COUNT(*) INTO num 
	FROM employees;
	RETURN num;
END $
delimiter;
#调用函数
SELECT num_employees();

#有参有返回
#根据员工名，返回它的工资
delimiter $
CREATE FUNCTION getSalary(name VARCHAR(10)) RETURNS INT
BEGIN
	DECLARE salary INT DEFAULT 0;
	SELECT e.salary INTO salary
	FROM employees e
	WHERE e.last_name = name;
	RETURN salary;
END $
delimiter ;
#调用函数
SELECT getSalary('Chen');

3. 查看函数

语法：

show create function 函数名;

4. 删除函数

语法：

drop function 函数名;

十、流程控制结构

1.顺序结构

理解：程序自顶向下执行

2.分支结构

if函数

功能：类似if else
语法：if(表达式1，表达式2，表达式3)
执行顺序：
如果表达式1成立，则执行表达式2，不成立执行表达式3

case结构

注意：
如果是表达式则不需要加分号，如果是语句则需要加分号。
表达式可以出现在begin end里也可以出现在外面。而语句只能出现来begin end里面。
表达式/值结尾直接用end，语句用end case结尾。

方法一：相当switch case

方法二：相当于多重if else
if结构

语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
【else 语句n;】
end if;
注意：

只能用在begin end中
案例：
#查询分数对应的等级并返回
delimiter $
CREATE FUNCTION grade(score INT) RETURNS CHAR
BEGIN
	IF score >= 90 THEN RETURN 'A';
	ELSEIF score >= 80 THEN RETURN 'B';
	ELSEIF score >= 60 THEN RETURN 'C';
	ELSE RETURN 'D';
	END IF;
END $
delimiter ;
SELECT grade(90);

3.循环结构

循环结构关键字循环控制
while iterate（类似continue）
loop leave（类似break）
repeat 这些关键字都需要放在begin end里面

总结：

循环结构关键字	循环控制
while	iterate（类似continue）
loop	leave（类似break）
repeat	这些关键字都需要放在begin end里面

1. 1 while

语法：

【标签:】while 循环条件 do
		循环体;
end while 【标签】;

案例：

#不带循环控制的批量插入
delimiter $
CREATE PROCEDURE test1(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i <= num
	DO
	INSERT INTO admin(username,password)
	VALUES(num,num+1);
	set i = i+1;
	END WHILE;
END $
delimiter ;
CALL test1(10);

#带循环控制（leave）的批量插入
delimiter $
CREATE PROCEDURE test2(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i <= num 
	DO
	
	INSERT INTO admin(username,password) VALUES(num,num+1);
	
	#判断超过20就跳出循环
	IF i > 20 THEN LEAVE a; END IF;
	
	set i = i+1;
	END WHILE a;
END $
delimiter ;
CALL test2(30);

#带循环控制（iterate）的批量插入
delimiter $
CREATE PROCEDURE test2(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i <= num 
	DO
	set i = i+1;
	#是奇数就不执行下面的，直接再进行下一次循环
	IF i%2 <> 0 THEN ITERATE a;
	END IF;

	INSERT INTO admin(username,password) VALUES(i,i+1);

	END WHILE a;
END $
delimiter ;
CALL test2(30);

#随机生成字符串
CREATE TABLE stringcontent(
	id INT PRIMARY KEY auto_increment,
	content VARCHAR(26)
);
delimiter $
CREATE PROCEDURE test3(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE startIndex INT DEFAULT 1;
	DECLARE len INT DEFAULT 1;

	WHILE i < num DO
		SET startIndex = FLOOR(RAND()*26+1);
		SET len = FLOOR(RAND()*(26-startIndex+1)+1);
		INSERT INTO stringContent(content) VALUES(SUBSTR(str,startIndex,len));
		SET i = i+1;
	END WHILE;
END $
delimiter ;
CALL test3(10);

1.2 loop

语法：

【标签:】loop
		循环体;
end loop 【标签】;

1.3 repeat

语法：

【标签:】repeat
		循环体;
until 结束循环的条件
end repeat 【标签 】;

EIF score >= 80 THEN RETURN ‘B’;

 	ELSEIF score >= 60 THEN RETURN 'C';
 	ELSE RETURN 'D';
 	END IF;
 END $
 delimiter ;
 SELECT grade(90);
 ```

3.循环结构

循环结构关键字循环控制
while iterate（类似continue）
loop leave（类似break）
repeat 这些关键字都需要放在begin end里面

总结：

[外链图片转存中…(img-1ieAVIDP-1642299725617)]

循环结构关键字	循环控制
while	iterate（类似continue）
loop	leave（类似break）
repeat	这些关键字都需要放在begin end里面

1. 1 while

语法：

【标签:】while 循环条件 do
		循环体;
end while 【标签】;

案例：

#不带循环控制的批量插入
delimiter $
CREATE PROCEDURE test1(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i <= num
	DO
	INSERT INTO admin(username,password)
	VALUES(num,num+1);
	set i = i+1;
	END WHILE;
END $
delimiter ;
CALL test1(10);

#带循环控制（leave）的批量插入
delimiter $
CREATE PROCEDURE test2(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i <= num 
	DO
	
	INSERT INTO admin(username,password) VALUES(num,num+1);
	
	#判断超过20就跳出循环
	IF i > 20 THEN LEAVE a; END IF;
	
	set i = i+1;
	END WHILE a;
END $
delimiter ;
CALL test2(30);

#带循环控制（iterate）的批量插入
delimiter $
CREATE PROCEDURE test2(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i <= num 
	DO
	set i = i+1;
	#是奇数就不执行下面的，直接再进行下一次循环
	IF i%2 <> 0 THEN ITERATE a;
	END IF;

	INSERT INTO admin(username,password) VALUES(i,i+1);

	END WHILE a;
END $
delimiter ;
CALL test2(30);

#随机生成字符串
CREATE TABLE stringcontent(
	id INT PRIMARY KEY auto_increment,
	content VARCHAR(26)
);
delimiter $
CREATE PROCEDURE test3(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE startIndex INT DEFAULT 1;
	DECLARE len INT DEFAULT 1;

	WHILE i < num DO
		SET startIndex = FLOOR(RAND()*26+1);
		SET len = FLOOR(RAND()*(26-startIndex+1)+1);
		INSERT INTO stringContent(content) VALUES(SUBSTR(str,startIndex,len));
		SET i = i+1;
	END WHILE;
END $
delimiter ;
CALL test3(10);

1.2 loop

语法：

【标签:】loop
		循环体;
end loop 【标签】;

1.3 repeat

语法：

【标签:】repeat
		循环体;
until 结束循环的条件
end repeat 【标签 】;

这篇关于MySQL的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！