MySQL

最新推荐文章于 2024-07-23 13:22:23 发布

AlvinUnity

最新推荐文章于 2024-07-23 13:22:23 发布

阅读量401

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/Alvin199765/article/details/103210851

版权

MySQL 专栏收录该内容

21 篇文章 1 订阅

订阅专栏

前言

重新开始的MySQL

常见命令

	查看表结构
	DESC 表名;
	
	查看所有的库
	SHOW DATABASES;

	打开指定库
	USE 库名;

	查看当前库的所有表 
	SHOW TABLES;

	查看其它库的所有表
	SHOW TABLES FROM 库名;

	创建表
	CREATE TABLES 表名(
		列名，列类型;
		列名，列类型
	);

语法规范

	注释：
		单行注释：#注释文字
		单行注释：-- 注释文字
		多行注释：/* 注释文字 */

	转义：
		在条件后面添加，用于查询字段中包含的特殊字符，这里使用ESCAPE将$设置为转义符，也可以不使用ESCAPE，直接使用\
		SELECT
			last_name,
			salary
		FROM
			employees
		WHERE
			last_name LIKE '_$_%' ESCAPE '$';

常见函数

单行函数

字符函数

函数	作用
LENGTH(str)	获取参数的字节个数
CONCAT(str1, str2,…)	拼接字符
UPPER(str)、LOWER(str)	控制字符大小写
SUBSTR(str ,pos)	截取从pos开始到最后的字符(MySQL中的索引从1开始)
SUBSTR(str, pos, len)	截取包含pos开始len个长度的字符
INSTR(str,substr)	在str中查找substr，找不到返回0
TRIM(str)	去掉str中的空格
TRIM(chstr FROM str)	去掉str中前后的chstr
LPAD(str,len,padstr)	向str左填充padstr实现总长度len
RPAD(str,len,padstr)	向str右填充padstr实现总长度len
REPLACE(str,from_str,to_str)	将str中的from_str替换成to_str

数学函数

函数	作用
ROUND(X)	四舍五入
ROUND(X,D)	四舍五入保留D位小数
CEIL(X)	向上取整，返回>=该参数的最小整数
FLOOR(X)	向下取整，返回<=该参数的最大整数
TRUNCATE(X,D)	截断保留D位小数
MOD(N,M)	取余，可以套用N-N/M*M

日期函数

在这里插入图片描述

函数	作用
NOW()	返回当前系统日期+时间
CURDATE()	返回当前系统日期（不包含时间）
CURTIME()	返回当前时间（不包含日期）
YEAR、MONTH、DAY(date)	年、月、日
HOUR、MINUTE、SECOND(date)	时、分、秒
STR_TO_DATE(‘3-2 1998’,’%c-%d %Y’)	将字符通过指定的格式转换成日期
DATE_FORMAT(‘1998-03-02 00:00:00’,’%m月%d日 %Y年’)	将日期转换成字符
DATEDIFF(expr1,expr2)	两个日期之间的天数

其他函数

函数	作用

流程控制函数

	IF-ELSE
	`IF`(条件,结果1,结果2)  

	CASE 1.
	CASE 字段或表达式
		WHEN 常量1 THEN 值或语句
		WHEN 常量2 THEN 值或语句
		...
		ELSE 值或语句
	END

	CASE 2.
	CASE
		WHEN 条件1 THEN 值或语句
		WHEN 条件2 THEN 值或语句
		...
		ELSE 值或语句
	END

分组函数

函数	作用
SUM([DISTINCT] expr)	和
AVG([DISTINCT] expr)	平均数
MIN([DISTINCT] expr)	最小值
MAX([DISTINCT] expr)	最大值
COUNT([DISTINCT] expr)	个数

可以与DISTINCT进行组合使用
COUNT(1)和COUNT(*)都可查询个数

DQL

基础查询

	起别名（别名里有关键字时需要加双引号）
	方式一：
	SELECT 列名 AS 别名, 列名 AS 别名 FROM 别名
	
	方式二：
	SELECT 列名 别名, 列名 别名 FROM 别名

	去重
    SELECT DISTINCT 列名 FROM 表名

	合并列名查询
	SELECT CONCAT(字符1,字符2,...) AS 别名 FROM 表名

条件查询

	条件表达式
	简单条件运算符：>、< 、=、<>、 >=、 <=
 			
	逻辑表达式
	逻辑运算符：&& || ! AND OR NOT

	模糊查询	
	LIKE：一般和通配符搭配使用 
	%：任意多个字符包含0个字符
	_：任意单个字符
	LIKE('%字段%')

			
	(不在)在...之间
	(NOT) BETWEEN 条件 AND 条件

	IN
	判断某字段的值是否属于IN列表中的某一项
	WHERE 列名 IN(value1,value2,...)

	IS NULL、IS NOT NULL
	=或<>无法判断null
	
	<=>
	完全等于：可以判断null值

排序查询

	ASC 升序
	DESC 降序
	默认是升序
	
	ORDER BY子句支持表达式、别名、函数、
	多个字段排序
	ORDER BY 字段1 ASC, 字段2 DESC,...

分组查询

	SELECT 分组函数，列（需要出现在group by的后面）
	FROM 表
	WHERE 条件
	GROUP BY 分组列表
	HAVING 分组函数条件
	ORDER BY 列;
	
	PS:MySQL中的Having和Order BY都支持别名查询，只有WHERE不支持。

连接查询

	SQL92
		SELECT 查询列表
		FROM 表1 别名,表2 别名
		
		等值：
			WHERE表1.key=表2.key
		
		非等值：
			WHERE非等值的连接条件
		
		自连接：	
		SELECT
			e.last_name,
			e.manager_id,
			ne.last_name
		FROM
			employees e,employees m
		WHERE
			e.manager_id = m.employee_id AND e.manager_id IS NOT NULL
		
		
		也支持一部分外连接（用于oracle、sqlserver，mysql不支
	持）
	
	SQL99【推荐使用】
		SELECT 查询列表
		FROM 表1 别名
	
		内连接：
			【INNER】 JOIN 表2 别名 ON 连接条件
		特点：
			1.表的顺序可以调换
			2.内连接的结果=多表的交集
			3.n表连接至少需要n-1个连接条件

		外连接：
			LEFT|RIGHT|FULL【OUTER】 JOIN 表2 别名 ON 连接条件
		特点：
			1.查询的结果=主表中所有的行，如果从表和它匹配将显示匹配行，如果从表不匹配则显示null
			2.当需要查询主表哪一个数据未使用过时，确定好谁是主表，谁是从表，然后判断从表的唯一列是否为空即可
			3.LEFT JOIN左边的就是主表，RIGHT JOIN右边的就是主表，FULL JOIN两边都是主表
			4.一般用于查询除了交集部分的剩余的不匹配的行
		

		交叉连接：
			CROSS JOIN 表2 别名
		特点：
			查询结果类似于笛卡尔乘积

子查询

	一、含义：
		- 嵌套在其他语句内部的SELECT语句称为子查询或内查询
		- 外面如果为SELECT语句，则此语句称为外查询或主查询


	二、分类
		1.按结果集的行列
			标量子查询（单行子查询）：结果集为一行一列
			列子查询（多行子查询）：结果集为多行一列
			行子查询：结果集为多行多列
			表子查询：结果集为多行多列

	三、特点
		单行子查询：
			- 子查询放在()内
			- 子查询一般放在条件的右侧
			- 标量子查询一般搭配单行操作符（>、<、>=、<=、=、<>）使用
			- 子查询的执行优先于主查询执行
			
		多行子查询：
			- 结果集有多行
			- 一般搭配多行操作符使用：any、all、in、not in
			- in： 属于子查询结果中的任意一个就行
			- any和all往往可以用其他查询代替类似MIN或MAX
	
			
	三、示例
		1.WHERE或HAVING后面
			IN()
				WHERE 字段 IN(单行子查询条件)
				WHERE 字段 = ANY(单行子查询条件)
			
			ANY()/SOME()
				WHERE 字段 < ANY(多行子查询条件)
				判断字段小于或大于查询条件中的任意一个

			ALL()
				WHERE 字段 < ALL(多行子查询条件)
				判断字段小于或大于所有查询条件	

			行子查询
				例：
					SELECT *
					FROM employees
					WHERE (employee_id, salary) = (
							SELECT MIN(employee_id), MAX(salary)
							FROM employees
					);


		2.SELECT后面
			只支持标量子查询
			例：
				SELECT d.department_name, (
					SELECT COUNT(*)
					FROM employees e
					WHERE d.department_id = e.department_id
				)
				FROM departments d;
				
		3.FROM后面
			将子查询结果充当一张表，必须起别名
			例：
				查询各部门的平均工资级别
				SELECT ag_dep.*, g.grade_level
				FROM (
					SELECT AVG(salary) ag, department_id
					FROM employees
					GROUP BY department_id
				) ag_dep
				INNER JOIN job_grades g 
				ON ag_dep.ag BETWEEN lowest_sal AND highest_sal	;


		3.EXISTS后面（相关子查询）
			与IN相通
			结果：0或1
			例：
				SELECT department_name
				FROM departments d
				WHERE EXISTS (
					SELECT *
					FROM employees e
					WHERE d.department_id = e.department_id
				);

分页查询

	一、语法
		SELECT 查询列表
		FROM 表
		LIMIT [offset,] size
		
		PS:
		offset为起始条目，可省略（默认从0开始）
		size是要显示的条数
	
		公式：
		假设要显示的页数page，每页的条目数size
		limit (page-1)*size,size;

联合查询

	一、语法
		查询语句1
		UNION
		查询语句2
		UNION
		...
		...
		
	二、应用场景
		要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致。 

	三、特点
 		1. 要求多条查询语句的查询列数一致
 		2. 要求多条查询语句的查询的每一列的类型和顺序最好一致
 		3. UNION关键字默认去重，非去重需要使用UNION ALL

总结

	执行顺序
	SELECT 查询列表		7
	FROM 表				1
	连接类型 JOIN 表2		2	
	ON 连接条件			3
	WHERE 筛选条件		4
	GROUP BY 分组列表	5
	HAVING 分组后的筛选	6
	ORDER BY 排序列表	8
	LIMIT 索引,条目数;	9

DML

插入

	方式一：
		INSERT INTO 表名[(列名)] 
		VALUES(值1,...),(值1,...);
		
	方式二：
		INSERT INTO 表名
		SET 列名=值,列名=值,...;

	方式三（方式一的子查询）：
		INSERT INTO 表名[(列名)]
		SELECT (值1,...),(值1,...), UNION
		SELECT (值2,...),(值2,...), UNION
		SELECT (值3,...),(值3,...),

特点：

方式一和方式三需要保证列与值一一对应，但是列的顺序可以调换
方式一和方式三可以省略(列名)，默认所有列
方式一和方式三列与值的个数必须一致，非空列也不除外
方式一支持一次插入多行，方式二不支持
方式一支持子查询，方式二不支持
方式一效果与方式三一样

修改

	一、修改单表数据
		语法：
			UPDATE 表名
			SET 列=新值,列=新值,...
			WHERE 条件;

	二、修改多表数据
		SQL92语法：
			UPDATE 表1 别名,表2 别名
			SET 列=新值,列=新值,...
			WHERE 条件
			AND 筛选条件;
			
		SQL99语法：
			UPDATE 表1 别名
			INNER|LEFT|RIGHT| JOIN 表2 别名
			ON 连接条件
			SET 列=值,...
			WHERE 筛选条件

删除

	一、单表的删除
		语法：
			DELETE FROM 表名 WHERE 筛选条件

	二、多表的删除
		SQL92语法：
			DELETE 表1的别名|表2的别名|表1的别名,表2的别名
			FROM 表1 别名,表2 别名
			WHERE 连接条件
			AND 筛选条件;

		SQL99语法：
			DELETE 表1的别名,表2的别名
			FROM 表1 别名
			INNER|LEFT|RIGHT JOIN 表2 别名 ON 连接条件
			WHERE 筛选条件; 

	三、删除整表
		语法：
			TRUNCATE TABLE 表名;

	四、对比
		1. DELETE可以加WHERE,TRUNCATE不可以
		2. TRUNCATE执行效率比DELETE高一些
		3. TRUNCATE删除表之后，再添加数据时，自增列会从0开始，DELETE则是接着断点开始
		4. TRUNCATE删除没有返回值，DELETE有
		5. TRUNCATE删除不能回滚，DELETE可以

DDL

库管理

	1.创建【判断数据库是否存在，不存在则创建】
		CREATE DATABASE [IF NOT EXISTS] 库名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
		
	2.更改库的字符集
		ALTER DATABASE 库名 CHARACTER SET 字符格式

	3.删除【判断数据库是否存在，存在则删除】
		DROP DATABASE [IF EXISTS] 库名
		
	4.创建数据库并指定字符编码utf-8
		CREATE DATABASE `库名` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

表管理

	1.创建
		CREATE TABLE IF NOT EXISTS 表名(
			列名 列的类型 [约束],
			...
		);

	2.修改
		修改列名
		ALTER TABLE 表名 CHANGE [COLUMN] 旧列名 新列名 新列名类型

		修改列的类型和约束
		ALTER TABLE 表名 MODIFY [COLUMN] 列名 新类型 新约束

		添加新列
		ALTER TABLE 表名 ADD [COLUMN] 列名 类型
	
		删除列
		ALTER TABLE 表名 DROP COLUMN 列名
		
		修改表名
		ALTER TABLE 表名 RENAME TO 新表名


	3.删除
		DROP TABLE IF EXISTS 表名

	4.复制
		复制表结构
		CREATE TABLE 新表名 LIKE 被复制的表名

		复制表结构加数据
		CREATE TABLE 新表名 SELECT * FROM 旧表

		只复制部分数据
		CREATE TABLE 新表名 SELECT 需要复制的列名 FROM 旧表

		仅复制某些字段
		CREATE TABLE 新表名 SELECT 需要复制的列名 FROM 旧表 WHERE 0

数据类型

整型

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

特点：
①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号
②如果超出了范围，会报out or range异常，插入临界值
③长度可以不指定，默认会有一个长度
长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型

小数

浮点型：

类型	字节
float(M,D)	4
double(M,D)	8

定点型：

类型	字节
decimal(M,D)	M+2

特点：
①M代表整数部位加小数部位，D代表小数部位，如果超出范围则插入临界值
②M和D都可以省略，如果是decimal，M默认为10，D默认为0，如果是float和double，则会根据插入的数值的精度来决定精度
③定点型精度较高，如果要求插入数值精度较高，类似货币运算时，最好使用定点型
④所选择的类型越简单越好，能保存数值的类型越小越好

字符

较短的文本：

	M的意思	特点	空间的耗费	效率
char(M)	最大的字符数，可以省略，默认为1	适合固定长度的字符	比较耗费	高
varchar(M)	最大的字符数，不可以省略	适合可变长度的字符	比较节省	低

binary和varbinary用于保存较短的二进制
enum保存枚举
set保存集合

日期

	字节	范围	时区等影响
year 年	1
date 日期	4
time 时间	3
datetime 日期+时间	8	1000-9999	不受
timestamp 日期+时间	4	1970-2038	受

常见约束

用于限制表中的数据，为了保证表中数据的准确和可靠性。

列级约束

约束类型	作用
NOT NULL	非空，用于保证该字段的值不能为空
DEFAULT	默认，用于保证该字段有默认值
PRIMARY KEY	主键，用于保证该字段的值具有唯一性，并且非空
UNIQUE	唯一，用于保证该字段的值具有唯一性，可以为空
CHECK	检查约束【MySQL不支持】
FOREIGN KEY	外键，用于限制两个表的关系，用于保证该字段的值必须来自于朱标的关联列的值【不支持】

语法：
直接在字段名和类型后面追加约束类型即可

主键、唯一的对比：

	保证唯一性	允许为空	一个表中可以有几个	允许组合
主键	是	否	一个	允许，PRIMARY KEY(列名,列名) ，不推荐
唯一	是	是	多个	允许，UNIQUE(列名,列名)，不推荐

外键：

要求在从表设置外键关系
从表的外键列需要和主表的关联列类型一致或兼容，名称无要求
主表中的关联列必须是一个key（一般是主键或唯一）
插入数据时，需要先插入主表，再插入从表
删除数据时，需要先删除从表，再删除主表，也可以通过以下两种方式直接删除主表的记录

	级联删除：
	删除主表时，将删除所有引用主表的从表记录
	ALTER TABLE 表名 
	ADD CONSTRAINT 自定义约束名 
	FOREIGN KEY(外键列) REFERENCES 主键表(主键列) 
	ON DELETE CASCADE

	级联置空：
	删除主表时，将对所有从表中的外键列设置为空
	ALTER TABLE 表名 
	ADD CONSTRAINT 自定义约束名 
	FOREIGN KEY(外键列) REFERENCES 主键表(主键列) 
	ON DELETE SET NULL

表级约束

	创建表级约束
	CREATE TABLE 表名(
		列名 类型 长度,
		...,
		CONSTRAINT 自定义约束名 约束类型(约束列),
		CONSTRAINT 自定义约束名 FOREIGN KEY(外键列) REFERENCES 主键表(主键列)
	);

	查询表内约束信息
	SHOW INDEX FROM 表名

特点：

只支持主键、唯一、外键
一个列可以有多个约束

修改表时操作约束

	添加列级约束
	ALTER TABLE 表名 MODIFY [COLUMN] 列名 类型 新约束

	添加表级约束
	ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(字段名)
	
	删除非空约束
	ALTER TABLE 表名 MODIFY [COLUMN] 列名 NULL

	删除默认约束
	ALTER TABLE 表名 MODIFY [COLUMN] 列名 类型

	删除主键约束
	ALTER TABLE 表名 DROP PRIMARY KEY
	
	删除唯一约束
	ALTER TABLE 表名 DROP INDEX 约束名
	
	删除外键约束
	ALTER TABLE 表名 DROP FOREIGN KEY 约束名
	
	因为MySQL创建外键时还会创建一个对应的索引，所以删除外键后，还需要删除外键对应的索引，才能彻底删除
	前缀 DROP INDEX 约束名

标识列

	创建标识列
	CREATE TABLE 表名(
		列名 类型 key约束 AUTO_INCREMENT
	)

	修改表时设置标识列
	ALTER TABLE 表名 MODIFY [COLUMN] 列名 类型 新约束 AUTO_INCREMENT

	删除标识列
	ALTER TABLE 表名 MODIFY [COLUMN] 列名 类型 约束

	设置步长
	SET AUTO_INCREMENT_INCREMENT = 3

特点：

标识列需要与key搭配
一个表只能有一个标识列
标识列只能是数值型
可以通过手动插入值设置起始值

事务

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

一、ACID特性

原子性：一个事务的整体操作不可拆分，要么都成功，要么都失败
一致性：一个事务执行会使数据从一个一致状态切换到另一个一致状态

以转账为例，转账之前 A 有 1000，B 有 1000，如果 A 给 B 转 200，成功了，那么 A 就是 800，B 就是 1200，业务前后

它们的总量都是 2000，不可能出现转完账之后，A 扣了 200，B 没加 200
隔离性：事务之间互相隔离

假设有 100 个人都在下单，一个人下单失败了，他的数据回滚，不会影响其他人
持久性：一个事务一旦提交，则会永久的改变数据库的数据

一旦事务保证了前3项特性，数据库通知事务提交成功了，那就一定会成功，就算数据库刚提示完成功，然后断电了，等再

次启动数据库时，也能在数据库中发现数据的变化

二、创建

1、隐式事务

没有明显的开启和结束的标记
比如insert、update、delete语句

2、显式事务
具有明显的开启和结束的标记

	步骤一：开启事务
	必须先设置自动提交功能为禁用
	set autucommit = 0;
	[START TRANSACTION]

	步骤二：
	编写事务中的SQL语句

	步骤三：结束事务
	COMMIT;提交事务
	ROLLBACK;回滚事务

3、隔离级别

	效果
脏读	A事务未提交却被B事务读取到
不可重复读	A事务做了修改提交了，同时开启的B事务读取到了修改后的结果
幻读	同时开启的两个事务中，A事务进行了查询，发现表内有两条数据，此时B事务向表中插入了一条数据并提交，A事务打算修改这两条数据，结果修改了三条数据

	脏读	不可重复读	幻读
READ UNCOMMITED（读未提交数据）	√	√	√
READ COMMITED（读已提交数据）	×	√	√
REPEATABLE READ（可重复读）	×	×	√
SERIALIZABLE（串行化，性能最差）	×	×	×

	MySQL默认REPEATABLE READ
	ORACLE默认READ COMMITED

	查看当前隔离级别
	SELECT @@TX_ISOLATION
	
	设置当前MySQL连接的隔离级别
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED

	设置数据库系统的全局的隔离级别
	SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED

4、回滚点

可以将事务回滚到SAVE POINT

	set autucommit = 0;
	[START TRANSACTION;]
	SQL语句1;
	SAVEPOINT 回滚点名称;
	SQL语句2;
	ROLLBACK 回滚点名称;

5、delete和truncate在事务使用时的区别
delete支持回滚，truncate不支持回滚

视图

一种虚拟存在的表，数据来自于查询时使用的表，当使用视图时会动态生成，只保存SQL逻辑，不保存查询结果

视图的好处

重用SQL语句
简化SQL，不必知道它的查询细节
与原始表相分离，提高了独立和安全性

	一、创建
		CREATE VIEW 视图名
		AS
		查询语句;

	二、修改
		方式
			CREATE OR REPLACE VIEW 视图名
			AS
			查询语句;
		
		方式2
			ALTER VIEW 视图名
			AS
			查询语句;

	三、删除
		DROP VIEW 视图名,视图名,...;

	四、查看
		DESC 视图名;
		SHOW CREATE VIEW 视图名;

视图的使用

视图一般只用于查询，但是也支持像普通表一样的增删改，语法与普通表的一样

具备以下特点的视图不允许更新

包含以下关键字的SQL语句：分组函数、group by、distinct、having、union、union all
常量视图
select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表

变量

系统变量

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

一、全局变量
作用域：服务器每次启动将为所有的全局变量赋初始值，针对所有的会话（连接）有效，但不能跨重启

二、会话变量
作用域：仅针对当前会话（连接）有效

	查询时，默认是会话级别SESSION，全局级别需要加GLOBAL

	1.查看所有系统变量
	SHOW GLOBAL|[SESSION] VARIABLES

	2.查看满足条件的部分系统变量
	SHOW GLOBAL|[SESSION] VARIABLES LIKE '%%'

	3.查看指定的某个系统变量的值
	SELECT @@GLOBAL|[SESSION].系统变量名

	4.为某个系统变量赋值
	方式一：
	SET GLOBAL|[SESSION] 系统变量名 = 值

	方式二：
	SET @@GLOBAL|[SESSION].系统变量名 = 值

自定义变量

一、用户变量
作用域：仅针对当前会话（连接）有效
定义和使用的位置：会话中的任何位置

	声明并初始化
	1) SET @用户变量名 = 值
	2) SET @用户变量名:= 值
	3) SELECT @用户变量名:= 值

	赋值
	1) 同声明并初始化
	2) SELECT 字段 INTO 变量名 FROM 表

	使用
	SELECT @用户变量名

二、局部变量
作用域：仅仅在定义它的begin end中有效
定义和使用的位置：只能用用在begin end中的第一句

	声明
	DECLARE 变量名 类型
	
	声明并初始化
	DECLARE 变量名 类型 DEFAULT 值

	赋值
	同自定义变量两种方式相同
	
	使用
	SELECT 局部变量名

存储过程

一组预先编译好的SQL语句集合，可以理解成批处理语句，适合做批量插入、更新、删除

好处：

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

创建：

	CREATE PROCEDURE 存储过程名(参数列表)
	BEGIN
		存储过程体
	END

注意：
一、参数列表包含三部分

参数模式参数名参数类型

	举例：
	IN STUNAME VARCHAR(20)

参数模式	效果
IN	输入参数，需要调用方传值
OUT	输出参数，会有返回值，一个存储过程中可以有多个
INOUT	既可输入也可输出的参数，需要传入值，并且有返回值

如果存储过程体仅仅只有一句话，BEGIND END可以省略
存储过程体中的每条SQL语句的结尾要求加分号
存储过程的结尾可以使用DELIMITER重新设置

DELIMITER

DELIMITER声明在Navicat客户端中被屏蔽了，不需要加，但是在别的环境下需要加，例如命令行客户端中。

默认情况下，DELIMITER是分号，在命令行客户端中，如果有一行命令以分号结束，那么回车后，MySQL就会执行该命令，但是在存储过程的BEGIN END中，我们不希望MySQL这么做，这时就可以将DELIMITER更改为别的符号，例如：“DELIMITER $”，这样只有当 $符出现后，MySQL解释器才会执行。

调用

	CALL 存储过程名(实参列表)

举例

	DELIMITER $

	#空参列表
	1、向表中插入数据
	CREATE PROCEDURE myp1()
	BEGIN
		INSERT INTO admin VALUES(3,'qwe','123'),(4,'qwe','123'),(5,'qwe','123'),
		(6,'qwe','123'),(7,'qwe','123');
	END $
	
	CALL myp1()$



	#创建带IN模式参数的存储过程
	1、创建存储过程实现用户是否登录成功
	DELIMITER $
	CREATE PROCEDURE myp3(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
	BEGIN
		DECLARE result INT MDEFAULT 0; #声明并初始化
		SELECT COUNT(*) INTO result	#赋值
		FROM admin
		WHERE username = username
		AND admin.`password` = PASSWORD;
		SELECT IF(result>0,'登录成功','登录失败');#使用
	END $
	
	CALL myp3('john','8888')$
	


	#创建带OUT模式的存储过程
	#1、根据女神名，返回对应的男神名和男神魅力值
	CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20),out userCP INT)
	BEGIN
		SELECT bo.boyName,bo.userCP INTO boyName,userCP
		FROM boys bo
		JOIN beauty b ON b.boyfriend_id = bo.id
		WHERE b.`name` = '小昭';
	END $
	
	CALL myp5('小昭',@bName,@userCP)$
	SELECT @bName,@buserCP$



	#创建INOUT参数的存储过程
	#1、传入a和b两个值，返回翻倍以后的a和b
	CREATE PROCEDURE myp6(INOUT a int, INOUT b INT)
	BEGIN
		SET a = a * 2;
		SET b = b * 2;
	END $
	
	SET @m = 10$
	SET @n = 20$
	CALL myp6(@m,@n)$
	SELECT @m,@n$

删除

	DROP PROCEDURE 存储过程名

查看存储过程的信息

	SHOW CREATE PROCEDURE 存储过程名

函数

含义同存储过程一样

创建

	CREATE FUNCTION 函数名(参数名 参数类型) RETURNS 返回类型
	BEGIN 
		函数体
		RETURN 值
	END

注意

函数体肯定会有return语句，如果没有会报错
return语句可以放到任何位置，但建议放到最后
每个函数只有一个返回值
函数体只有一句时，可以省略begin end

调用

	SELECT  函数名(参数列表)

举例

	#根据部门名，返回该部门的平均工资
	CREATE FUNCTION myf3(dName VARCHAR(20)) RETURNS DOUBLE
	BEGIN
		DECLARE avgSal DOUBLE; #定义局部变量
	
		SELECT AVG(salary) INTO avgSal
		FROM departments d
		JOIN employees e ON d.department_id = e.department_id
		WHERE department_name = dName;
	
		RETURN avgSal;
	END $
	
	SELECT myf3('Adm')$

查看

	SHOW CREATE FUNCTION 函数名

删除

	DROP FUNCTION 函数名

流程控制结构

分支结构

一、IF函数
见流程控制函数↑
二、CASE结构
见流程控制函数↑
三、IF结构

	IF 条件1 THEN 语句1;
	ELSEIF 条件2 THEN 语句2;
	...
	[ELSE 语句n;]
	END IF;

只能应用于begin end中

循环结构

分类
while、loop、repeat

循环控制
itereate类似于continue，结束本次继续下一次
leave类似于break，结束当前所在的循环

语法

	WHILE:
	[标签:]WHILE 循环条件 DO
		循环体
	END WHILE[标签]
	标签:循环名


	LOOP:
	[标签:] LOOP
		循环体
	END LOOP [标签]
	可以用来模拟简单的死循环

	REPEAT:
	[标签:] REPEAT
		循环体;
	UNTIL 结束循环的条件
	END REPEAT
	至少执行一次，类似于JAVA中的dowhile

案例

	#根据参数批量插入偶数列数据
	CREATE PROCEDURE myp10(IN insertCount INT)
	BEGIN
		DECLARE i INT DEFAULT -1;
		a:WHILE i<=insertCount DO
			SET i = i+1;
			IF i % 2 != 0 THEN ITERATE a;
			END IF;
			INSERT INTO admin VALUES(i,CONCAT('admini',i),'123132');
		END WHILE a;
	END $

	#根据参数批量向表中添加随机字母
	CREATE TABLE stringcontent(
	id INT PRIMARY KEY auto_increment,
	content VARCHAR(20)
	);


	CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
	BEGIN
		DECLARE i INT DEFAULT 1;#定义一个循环变量，表示插入次数
		DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
		DECLARE startIndex INT DEFAULT 1;#代表起始索引
		DECLARE len INT DEFAULT 1;#代表截取的字符的长度
		WHILE i<=insertCount DO
			SET len = FLOOR(RAND()*(20-startIndex+1)+1);#产生一个随机整数，代表截取长度1- (20-startIndex+1)
			SET startIndex = FLOOR(RAND()*20+1);#产生一个随机的整数，代表起始索引1-26
			INSERT INTO stringContent(content) VALUES(SUBSTR(str,startIndex,len));
			SET i=i+1;#循环变量更新
		END WHILE;
	END $
	
	CALL test_randstr_insert(10)$
	
	SELECT * FROM stringContent

AlvinUnity

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL

MySQL前言常见命令语法规范前言重新开始的MySQL常见命令查看所有的库 SHOW DATABASES打开指定库 USE 库名查看当前库的所有表 SHOW TABLES查看其它库的所有表 SHOW TABLES FROM 库名创建表 CREATE TABLES 表名( 列名，列类型; 列名，列类型 )...
复制链接

扫一扫

专栏目录