MySQL(变量 存储过程 触发器 函数)

变量

全局变量(内置变量):可以在多个会话中去访问他
-- 查看所有全局变量
show variables
-- 查看某个全局变量
select @@变量名
-- 修改全局变量
set 变量名=新值
-- mysql服务器的接收数据的编码
SET @@character_set_client=utf8; 设置数据库编码
-- mysql服务器输出数据的编码
set @@character_set_results=utf8;
-- 查看数据库编码
SELECT @@character_set_client;

会话变量:
	只存在于当前客户端与数据库服务器端的一次连接当中
	如果连接断开 那么会话变量全部丢失
-- 定义会话变量 
set @变量=值
-- 查看会话变量
select @变量

局部变量:
	在存储过程中使用的变量就叫局部变量 
	只要存储过程执行完毕 局部变量就丢失
-- 定义局部变量的语法
DECLARE i INT DEFAULT 1;  
-- 给变量设置值
set i=10;

定义一个变量，作为一个临时展示的字段
SELECT (@i:=@i+1) AS id,ename,job,sal 
	FROM emp,(SELECT @i:=0) AS init;
说明: SELECT @i:=0意思
    它的意思是为变量@i赋值(如：set @i=0;)
    在mysql中用户变量赋值有两种方式 一种是=另一种是:= 
    其中区别在于使用set赋值时两种方式都可以使用
    使用select赋值时只能使用:=
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

存储过程

概念

存储过程是数据库中的一个对象 
存储在服务端 用来封装多条SQL语句且带有逻辑性 可以实现一个功能
由于他在创建时 就已经对SQL进行了编译 所以执行效率高 
而且可以重复调用 类似与我们Java中的方法
1
2
3
4

语法

DELIMITER $$

CREATE

    PROCEDURE `数据库名`.`存储过程名`()
    
    BEGIN

    END$$

DELIMITER ;

注意:
	创建存储过程需要管理员分配权限 
补充:
	delimiter是mysql定义结束标记的 
	在mysql客户端中结束标记默认是分号;
	
	如果一次输入的语句较多 并且语句中间有分号 
	这时需要新指定一个特殊的结束符
	delimiter $$ 表示mysql用$$表示mysql语句结束
	过程结束后肯定会有一句delimiter 
	表示恢复成默认的
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

参数

in:输入参数
out：输出参数
inout：输入输出参数

DELIMITER $$ 
CREATE PROCEDURE `security`.`test2`(IN num INT,OUT res INT)
BEGIN
  SELECT * FROM sys_role WHERE id = num;
  SELECT COUNT(*) FROM sys_role INTO res;
END $$ 
DELIMITER ; 

CALL `security`.`test2`(1,@res);
SELECT @res;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

调用存储过程 call

语法: CALL `security`.`test2`(1,@res);
查询结果: SELECT @res;
1
2

删除存储过程 drop

 语法: DROP PROCEDURE test1;
1

查看存储过程 show

-- 查看所有的存储过程状态
SHOW PROCEDURE STATUS; 
-- 查看创建存储过程的语句
SHOW CREATE PROCEDURE 存储过程名; 
1
2
3
4

判断的存储过程 if

DELIMITER $$
CREATE PROCEDURE week_procedure(IN num INT, OUT str VARCHAR(32))
    BEGIN
        IF num=1 THEN
           SET str='星期一';
        -- 注意elseif  连在一块
        ELSEIF num=2 THEN 
           SET str='星期二';
        ELSE
           SET str='输入有误';
         -- 注意要结束if  后面有分号   
        END IF;   
    END$$
DELIMITER ;

CALL week_procedure(1,@str);
SELECT @str; #星期一
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

循环的存储过程 while do

DELIMITER $$
CREATE
    PROCEDURE `security`.`while_procedure`(IN num INT, OUT res INT)
    BEGIN
	-- 定义一个局部变量
	DECLARE i INT DEFAULT 1;
	DECLARE `sum` INT DEFAULT 0;
	WHILE i<=num DO
		SET `sum` = `sum`+i;
		SET i = i + 1;
	-- 结束循环	
	END WHILE;
	SET res = `sum`;	
    END$$
DELIMITER ;

CALL while_procedure(100,@res);
SELECT @res; #5050
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

其他循环

#REPEAT 循环 
DELIMITER $
 CREATE PROCEDURE proRepeat(OUT outnum INT)
     BEGIN
     DECLARE i INT DEFAULT 0;
     REPEAT
         SET i=i+1;
          -- 注意这里不要加分号,不然语法报错 UNTIL 结束条件
         UNTIL i>=5 
     -- 结束循环    
     END REPEAT; 
     SET outnum=i;
  END $
DELIMITER ;

CALL proRepeat(@num);
SELECT @num;  #5
  
  
#loop 循环 
DELIMITER $$
CREATE
    PROCEDURE proLoop(OUT outnum INT)
    BEGIN
	DECLARE i INT DEFAULT 0;
	 -- 这里的myloop 是我给循环起的一个标号或者说名字,这是语法要求,不起就会报错
	myloop:LOOP    
          SET i=i+1;
        IF i>=5 THEN
          -- 根据我的循环标号中断循环 leave是中断循环的意思
          LEAVE myloop;  
        END IF;
	END LOOP;
	SET outnum=i;
    END$$
DELIMITER ;
  
CALL proLoop(@num);
SELECT @num;
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

控制循环的两个关键字

leave 相当于java中的break
iterate相当于java中的continue
1
2

触发器

概念

触发器:
	数据库中的一个对象 相当于JS中的监听器 
	触发器可以监听 增 删 改 三个动作

比如说我想监听一张表,只要我增删改了这张表中的数据,
 		我就可以触发这个触发器,去往另外一张表中记录一下日志
1
2
3
4
5
6

语法

DELIMITER $$
CREATE
    TRIGGER `数据库名`.`触发器名` 
    BEFORE/AFTER INSERT/UPDATE/DELETE ON 监听的表名
    FOR EACH ROW 
    BEGIN

    END$$
DELIMITER ;

BEFORE 行为发生之前就触发
AFTER 行为发生之后触发
FOR EACH ROW 行级触发,每操作一行就触发
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 往一张表test中删除数据
-- 在日志表logger中添加一条记录
DELIMITER $$
CREATE TRIGGER tigger1 
    AFTER DELETE ON test
    FOR EACH ROW 
    BEGIN
	INSERT INTO logger VALUES(NULL,"你删除了一条数据",NOW());
    END$$
DELIMITER ;

DELETE FROM employee_enh WHERE emp_no = 25348;
#删除test表中的内容后 触发器启动
1
2
3
4
5
6
7
8
9
10
11
12
13

old new

old.字段 可以获取到被监听的表中的字段的旧值
new.字段 可以获取到被监听表中更新后的字段的新值 比如插入新值或者修改旧值
    
-- 例如:往一张表t1中添加一条数据
-- 另一张表t2也要添加一条同样的数据
DELIMITER $$
CREATE
    TRIGGER `mydb`.`myTri1` 
    AFTER INSERT ON `mydb`.`t1`
    FOR EACH ROW BEGIN
	INSERT INTO t2 VALUES(new.id,new.username,new.age);
    END$$
DELIMITER ;


-- 例如:我修改一张表t1中的数据
-- 另一张表t2中的数据也要修改
DELIMITER $$
CREATE
    TRIGGER `mytestdb`.`MyTri7` AFTER UPDATE
    ON `mytestdb`.`t1`
    FOR EACH ROW 
    BEGIN
	UPDATE t2 SET id=new.id,username=new.username,age=new.age WHERE id=old.id;
    END$$
DELIMITER ;
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

函数(方法)

函数包含了内置函数和自定义函数

函数调用
SELECT 函数名();

删除函数:
DROP FUNCTION 函数名; 

函数和存储过程的区别
1.存储过程没有返回值 函数必须要有返回值
  但是存储过程可以用out能实现返回值这个作用
2.存储过程有in out inout这几个参数类型 
  函数的参数全是用来收实参
1
2
3
4
5
6
7
8
9
10
11
12
13

自定义函数

自定义函数语法
DELIMITER $$
CREATE
    FUNCTION `数据库名`.`函数名`()
    RETURNS 返回类型
    BEGIN
       函数逻辑...
    END$$
DELIMITER ;
1
2
3
4
5
6
7
8
9

统计函数(count)

●使用Count语句 
	count 返回行的总数
	SELECT COUNT(*)|COUNT(列名) FROM table_name
		[WHERE where_definition]	
	count(*) 返回满足条件的记录的行数
	count(列名) 统计满足条件的某列有多少个 会排除为null情况
	
#统计一个班级有多少个学生
SELECT COUNT(*) FROM student;

#统计数学成绩大于80的学生个数
SELECT COUNT(*) FROM student
	WHERE math > 80;
1
2
3
4
5
6
7
8
9
10
11
12
13

合计函数(sum avg max min)

●使用SUM语句
sum函数返回满足where条件的行的和
一般在数值列

    SELECT SUM(列名1), SUM(列名2)... FROM table_name
    	[WHERE where_definition]
    只对数值起作用 其他没有意义
    多列求和用,分开	

#统计一个班的数学总成绩
SELECT SUM(math) FROM student;	

#统计一个班语文 英语 数学各科的总成绩
SELECT SUM(chinese),SUM(english),SUM(math) 
FROM student;

#统计一个班语文 英语 数学的成绩总和
SELECT SUM(chinese+english+math) AS total_score 
FROM student;		

#统计一个班语文成绩平均分
SELECT (SUM(chinese) / COUNT(*)) AS aver_chinese
FROM student;

●使用AVG语句  
    SELECT AVG(列名1), AVG(列名2)... FROM table_name
    	[WHERE where_definition]

#求一个班级数学平均分
SELECT AVG(math) FROM student;

●使用MAX/MIN语句  
    SELECT MAX(列名1), MAX(列名2)... FROM table_name
    	[WHERE where_definition]

#求一个班级数学最低分
SELECT MIN(math) FROM student;    
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

字符串函数

●返回字串字符集
	SELECT CHARSET(ename) FROM emp;
	
●连接字串
	SELECT CONCAT(ename, " job is ", job) FROM emp;	
	
●返回substring在string中出现的位置 没有返回0	
	SELECT INSTR("helloworld", "world") FROM DUAL;	
	DUAL 亚元表 系统表 可以作为测试表使用	
	
●转换成大写	 
	SELECT UCASE(ename) FROM emp;
	
●转换成小写
	SELECT LCASE(ename) FROM emp;
    
●从string中的左边起取length个字符	 
	SELECT LEFT(ename, 2) FROM emp;
	
●从string中的右边起取length个字符 
	SELECT RIGHT(ename, 2) FROM emp;
	
●string长度[按照字节返回]	
	SELECT LENGTH(ename) FROM emp;
	
●在str中用replace_str替换search_str  
	SELECT ename, REPLACE(job, 'MANAGER', '经理') FROM emp;
	
●逐字符比较两字串的大小 
	SELECT STRCMP('jack','smith') FROM DUAL;
	
●从str的position开始[从1开始计算]，取length个字符 
	从enamel列的第1个位置开始取出2个字符
	SELECT SUBSTRING(ename,1,2) FROM emp;
	
●去除左端 右端 左右两端的空格
	SELECT TRIM('   jack  ') FROM DUAL;
	
●首字母小写 其他字母大写 显示员工的姓名
    获取第一个字符并且将其变小写 获取其他字符 将两个字符串连接
    SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)), SUBSTRING(ename,2))
        FROM emp
    获取第一个字符并将其转小写 用其替换之前的字符
    SELECT REPLACE(ename,SUBSTRING(ename,1,1),LCASE(SUBSTRING(ename,1,1)))
    	FROM emp;
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

数学函数

-- ABS(num)	绝对值
SELECT ABS(-10) FROM DUAL;  10

-- BIN (decimal_number )十进制转二进制
SELECT BIN(10) FROM DUAL;  1010

-- CONV(number2,from_base,to_base)	进制转换
-- 下面的含义是 8 是十进制的8, 转成 2进制输出
SELECT CONV(8, 10, 2) FROM DUAL;  1000
SELECT CONV(16, 16, 10) FROM DUAL;  22

-- HEX (DecimalNumber )	转十六进制

-- CEILING (number2 )	向上取整, 得到比num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL;  -1

-- FLOOR (number2 )	向下取整,得到比 num2 小的最大整数
SELECT FLOOR(-1.1) FROM DUAL;  -2

-- FORMAT (number,decimal_places )	保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL;  78.13

-- LEAST (number , number2  [,..])	求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL;
-- MOD (numerator ,denominator )	求余
SELECT MOD(10, 3) FROM DUAL;

-- RAND([seed])	RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果seed不变，
--    该随机数也不变了
SELECT RAND() FROM DUAL;

SELECT CURRENT_TIMESTAMP() FROM DUAL;
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

日期函数

-- CURRENT_DATE() 当前日期
SELECT CURRENT_DATE() FROM DUAL;
#2022-08-31
-- date(datetime) 返回datetime的日期部分(年月日)
SELECT DATE(NOW()) FROM DUAL;

-- CURRENT_TIME() 当前时间
SELECT CURRENT_TIME()  FROM DUAL;
#20:52:23

-- now() 当前时间戳
SELECT NOW() FROM DUAL;
-- CURRENT_TIMESTAMP() 当前时间戳
SELECT CURRENT_TIMESTAMP()  FROM DUAL;
#2022-08-31 20:52:23

-- date_add(date,interval d_value [年月日 时分秒]) 在date中加上日期或时间
SELECT DATE_ADD(NOW(), INTERVAL 10 MINUTE) FROM DUAL;

-- date_sub(date,interval d_value d_type) 在date中减上一个时间
SELECT DATE_SUB(NOW(), INTERVAL 10 MINUTE) FROM DUAL;

-- TIMESTAMPDIFF(类型,开始时间,结束时间)
-- second秒 minute分钟 hour小时 day天 week周 
-- quarter季度 month月 year年
SELECT TIMESTAMPDIFF(DAY,'2020-12-31 00:00:00','2020-12-22 00:00:00')

-- datediff(date1,date2) 两个日期时间差(结果是天数)
SELECT DATEDIFF('2020-12-31 00:00:00','2020-12-22 00:00:00') 
	FROM DUAL;
	
-- timediff(date1,date2) 两个时间差[时分秒]
SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL;	

-- year|month|day|date(datetime) 年月日
SELECT DATE(NOW()) FROM DUAL;

-- unix_timestamp() 返回的是1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;

-- from_unixtime() 可以把一个unix_timestamp秒数[时间戳] 转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过FROM_UNIXTIME转换
SELECT FROM_UNIXTIME(1535621, '%Y-%m-%d %H:%i:%s') FROM DUAL;

-- DATE_FORMAT()格式化
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
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

加密函数

-- USER() 查询用户
-- 可以查看登录到mysql的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL; 
-- root@localhost 用户@IP地址

-- DATABASE() 查询当前使用数据库名称
SELECT DATABASE();

-- MD5(str)	为字符串算出一个 MD5 32的字符串，常用(用户密码)加密
-- root 密码是 123456 -> 加密md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('123456') FROM DUAL;
SELECT LENGTH(MD5('123456')) FROM DUAL;

-- 演示用户表，存放密码时，是md5
CREATE TABLE jies_user
	(id INT , 
	`name` VARCHAR(32) NOT NULL DEFAULT '', 
	pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO jies_user 
	VALUES(100, 'jies', MD5('123456'));
SELECT * FROM jies_user; 

SELECT * FROM jies_user  -- SQL注入问题
	WHERE `name`='jies' AND pwd = MD5('123456')  

-- PASSWORD(str) -- 加密函数, MySQL数据库的用户密码就是 PASSWORD函数加密

SELECT PASSWORD('123456') FROM DUAL; 
-- *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 


-- select * from mysql.user \G 	从原文密码str 计算并返回密码字符串
-- 通常用于对mysql数据库的用户密码加密
-- mysql.user 表示 数据库.表 
SELECT * FROM mysql.user
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

流程控制函数

# IF(expr1,expr2,expr3)	
# 如果expr1为True, 则返回expr2, 否则返回expr3
SELECT IF(TRUE, 'jack', 'tom') FROM DUAL;
jack

# IFNULL(expr1,expr2)
# 如果expr1不为空NULL, 则返回expr1, 否则返回expr2
SELECT IFNULL( NULL, 'jack') FROM DUAL;
jack

# SELECT CASE 
#	WHEN expr1 THEN expr2 
#	WHEN expr3 THEN expr4 
#	ELSE expr5 END; [类似多重分支]
# 如果expr1为true, 则返回expr2,
# 如果expr3为true, 返回 expr4, 
# 否则返回expr5
SELECT CASE 
	WHEN TRUE THEN 'jack'  -- jack
	WHEN FALSE THEN 'tom' 
	ELSE 'mary' END
jack

# 查询emp表, 如果comm是null, 则显示0.0
SELECT ename,IF(comm IS NULL, 0.0, comm) FROM emp;
SELECT ename,IFNULL(comm, 0.0) FROM emp;

# 如果emp表的job是CLERK则显示职员，如果是MANAGER则显示经理
# 如果是SALESMAN则显示销售人员，其它正常显示
SELECT ename,(SELECT CASE 
		WHEN job='CLERK' THEN '职员' 
		WHEN job='MANAGER' THEN '经理' 
		WHEN job='SALESMAN' THEN '销售人员'
		ELSE job END
	) AS job 
	FROM emp;
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