定义变量
SET @username = ‘yly’;
定义函数
DROP FUNCTION IF EXISTS select_by;
DELIMITER //
CREATE FUNCTION select_by(INT(10) user_id)
RETURNS VARCHAR(25)
DETERMINISTIC # 是否具有确定性->多次执行结果是否相同
CONTAINS SQL # 是否包含sql
READS SQL DATA # 只是读取sql数据,不做修改
# 不加上面这些限定会报错
BEGIN
RETURN (SELECT username FROM sys_user su WHERE su.user_id=user_id);
END //
DELIMITER ;
调用函数
SELECT select_by(2022);
定义存储过程
-- in
DROP PROCEDURE IF EXISTS select_user_by_username;
DELIMITER $$
CREATE PROCEDURE select_user_by_username(IN username VARCHAR(20)) -- IN 入参 OUT 出参 INOUT 都行
BEGIN
SELECT * FROM sys_user su
WHERE su.username=username;
END $$
DELIMITER ;
-- out
DROP PROCEDURE IF EXISTS select_user_by_username;
DELIMITER $$
CREATE PROCEDURE select_user_by_username(IN username VARCHAR(20),OUT userdata VARCHAR(40)) -- IN 入参 OUT 出参 INOUT 都行
BEGIN
SELECT su.username INTO userdata
FROM sys_user su
WHERE su.username=username;
END $$
DELIMITER ;
调用存储过程
-- 1
CALL select_user_by_username('yly');
-- 2
SET @username = '';
CALL select_user_by_username('yly',@username);
SELECT @username;
查看存储过程
-- 1
-- 创建信息
SHOW CREATE PROCEDURE select_user_by_username;
SHOW CREATE FUNCTION select_by;
-- 状态信息
SHOW PROCEDURE STATUS LIKE '';
SHOW FUNCTION STATUS LIKE '';
--2 从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='select_user_by_username' AND ROUTINE_TYPE='PROCEDURE';
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='select_by' AND ROUTINE_TYPE='FUNCTION';
-- 修改
ALTER PROCEDURE select_user_by_username
COMMENT '通过username查询用户';
-- 删除
DROP PROCEDURE IF EXISTS select_user_by_username;
## mysql变量
mysql系统变量
-- 全局 会话
-- 查看系统变量
SHOW GLOBAL VARIABLES LIKE 'admin_%';
SHOW SESSTION VARIABLES LIKE 'character_%';
-- 查看指定系统变量
SELECT @@global.max_connections;
-- 修改
SET @@global.max_connections = 124;
SET GLOBAL max_connections = 123;
-- 全局系统变量是针对于当前数据库实例有效, 一旦重启mysql服务,就会失效;
mysql用户变量
-- 定义变量
-- 1
SET @username = '';
SET @username := '';
-- 2
SELECT @username := FROM sys_user;
SELECT username INTO @username := FROM sys_user;
-- 使用
SELECT @username;
-- 定义局部变量
-- 局部变量 使用DECLARE声明
-- 只能在 PROCEDURE, FUNCTION中使用;
-- BEGIN 首行
-- 声明格式
DECLARE username VARCHAR(30) '';
DELIMITER $$
CREATE PROCEDURE `test_var`()
BEGIN
DECLARE username VARCHAR(20); -- 不指定则为空
SELECT username INTO username FROM sys_user;
SELECT username;
END;
$$
DELIMITER ;
定义错误与处理程序
定义错误条件
DECLARE '' /*error*/ CONDITION FOR ''/*error_code*/;
DECLARE Field_Not_Be_NULL CONDITION FRO 1048;
DECLARE Field_Not_Be_NULL CONDITION FRO SQLSTATE '1048';
定义处理程序
DECLARE EXIT/**/ HANDLER FRO Field_Not_Be_NULL SET @INFO = 'ERROR';
if
-- IF(a,b,c) 相当于 a ? b : c
DROP PROCEDURE IF EXISTS test_var;
DELIMITER $$
BEGIN
DECLARE username VARCHAR(20);
SELECT su.username INTO username FROM sys_user su WHERE su.user_id=2022;
IF username = 'yly'
THEN SET username='';
ELSEIF username='hello'
THEN SET username='123456';
ELSE SET username='hhhhh';
END IF;
SELECT username;
END;
$$
DELIMITER ;
case
DROP PROCEDURE IF EXISTS test_var;
DELIMITER $$
CREATE PROCEDURE `test_var`()
BEGIN
DECLARE username VARCHAR(20);
SELECT su.username INTO username FROM sys_user su WHERE su.user_id=2022;
-- CASE username
-- when 'yly3' THEN SET username='';
-- When 'hello' THEN SET username='123456';
-- Else SET username='else';
-- end case;
CASE
WHEN LENGTH(username)>4 THEN SET username='';
WHEN LENGTH(username)>1 THEN SET username='123456';
ELSE SET username='else';
END CASE;
SELECT username;
END;
$$
DELIMITER ;
loop while repeat
loop 感觉就像 for 一样
DROP PROCEDURE IF EXISTS test_loop;
DELIMITER $$
CREATE PROCEDURE `test_loop`()
BEGIN
DECLARE username VARCHAR(20);
DECLARE `count` INT(10) DEFAULT 0;
SELECT su.username INTO username FROM sys_user su WHERE su.user_id=2022;
loop_label:LOOP
SET `count` = `count`+1;
IF `count` >= 10 THEN LEAVE /*跳出循环*/ loop_label;
END IF;
END LOOP loop_label;
SELECT username,`count`;
END;
$$
DELIMITER ;
while 感觉就像 while 前面也可以加label
DROP PROCEDURE IF EXISTS test_while;
DELIMITER $$
CREATE PROCEDURE `test_while`()
BEGIN
DECLARE username VARCHAR(20);
DECLARE `count` INT(10) DEFAULT 0;
SELECT su.username INTO username FROM sys_user su WHERE su.user_id=2022;
WHILE `count` < 10 DO
SET `count` = `count`+1;
END WHILE
SELECT username,`count`;
END;
$$
DELIMITER ;
repeat 感觉就像 do while
DROP PROCEDURE IF EXISTS test_while;
DELIMITER $$
CREATE PROCEDURE `test_while`()
BEGIN
DECLARE username VARCHAR(20);
DECLARE `count` INT(10) DEFAULT 0;
SELECT su.username INTO username FROM sys_user su WHERE su.user_id=2022;
REPEAT
SET `count` = `count`+1;
UNTIL `count` >= 10
END REPEAT;
SELECT username,`count`;
END;
$$
DELIMITER ;
LEAVE 和 ITERATE 像 continue
-- LEAVE
DROP PROCEDURE IF EXISTS test_while;
DELIMITER $$
CREATE PROCEDURE `test_while`()
test:BEGIN
DECLARE username VARCHAR(20);
DECLARE `count` INT(10) DEFAULT 0;
SELECT su.username INTO username FROM sys_user su WHERE su.user_id=2022;
REPEAT
SET `count` = `count`+1;
UNTIL `count` >= 10
END REPEAT;
LEAVE test; -- 到这就会停止
SELECT username,`count`;
END;
$$
DELIMITER ;
-- ITERATE
DROP PROCEDURE IF EXISTS test_loop;
DELIMITER $$
CREATE PROCEDURE `test_loop`()
BEGIN
DECLARE username VARCHAR(20);
DECLARE `count` INT(10) DEFAULT 0;
SELECT su.username INTO username FROM sys_user su WHERE su.user_id=2022;
loop_label:LOOP
SELECT username,`count`;
SET `count` = `count`+1;
IF `count` >= 10 THEN LEAVE /*跳出循环*/ loop_label;
END IF;
IF `count` < 3 THEN SELECT 100; ITERATE loop_label;
END IF;
SET `count` = `count`+100;
END LOOP loop_label;
SELECT username,`count`;
END;
$$
DELIMITER ;
CALL test_loop();