MySql学习笔记

最新推荐文章于 2023-06-18 11:25:03 发布

0o勿忘初心o0

最新推荐文章于 2023-06-18 11:25:03 发布

阅读量272

点赞数

CC 4.0 BY-SA版权

分类专栏：学习实践文章标签： MySQL MySQL存储过程 MySQL基本函数 sql函数存储工程

本文链接：https://blog.youkuaiyun.com/Xg_95303084/article/details/81945092

学习实践专栏收录该内容

13 篇文章

订阅专栏

>>简介

1 什么是存储过程?

存储过程(Stored Procedure) 是一组为完成特定功能的Sql语句集,经变异后存在数据库中,可以通过名字开来调用.

2 存储过程有什么作用?

完成特定的功能,大致理解为就像java中的方法

3 存储过程有什么优点?

3.1 增强了sql语言的功能和灵活性

3.2 标准组件式编程

3.3 较快执行速度

3.4 减少网络流量

3.5 作为一种安全机制来充分利用

>>存储过程的创建

1 语法

create procedure 过程名([in|out|inout] 变量名 数据类型,[IN|OUT|INOUT] 参数名 数据类型…]])[特性] 过程体

2 示例

-- 删除存储过程

DROP PROCEDURE IF EXISTS `myproc_01`;

DELIMITER //

CREATE PROCEDURE `myproc_01`(OUT s INT)

BEGIN -- {

-- 查询表acc_sort的记录总数,存放在输出参数s中

SELECT COUNT(*) INTO s FROM acc_sort;

END -- }

DELIMITER;

-- 调用

CALL myproc_01(@s);

SELECT @s AS res;

3 说明

3.1 为什么要用DELIMITER //

MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。

3.2 参数说明

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:

IN 参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 该值可在存储过程内部被改变，并可返回

INOUT 调用时指定，并且可被改变和返回

3.3 过程体

begin ...... end 理解参考java { ...... }

3.4 in参数的使用示例

-- 需求:输入一个数88,然后在存储过程中修改为22,然后执行函数

DROP PROCEDURE IF EXISTS `myproc_02`;

DELIMITER //

CREATE PROCEDURE `myproc_02`(IN s INT)

BEGIN

SELECT s;

SET s = 22;

SELECT s;

END

DELIMITER;

-- 调用

SET @s = 88;

CALL myproc_02(@s);

SELECT @s;

-- 结论 : @s存储过程中被修改，但是原来的@s值还是没改变

3.5 out参数使用示例

-- 需求:定义一个参数值为88作为输出参数,然后定义一个存储过程修改其值,最后执行查看结果

DROP PROCEDURE IF EXISTS `myproc_03`;

DELIMITER //

CREATE PROCEDURE `myproc_03`(OUT s INT)

BEGIN

SELECT s;

SET s = 22;

SELECT s;

END

DELIMITER;

-- 调用

SET @s = 88;

CALL myproc_03(@s);

SELECT @s;

-- 结论:@s单独设置生效的,当作为输出参数传入存储过程的时候,会初始化为null,函数执行结束后,@s的值为存储过程中设置的值

3.6 inout参数使用示例

-- 需求:定义一个参数值为88作为输入输出参数,然后定义一个存储过程修改其值,最后执行查看结果

DROP PROCEDURE IF EXISTS `myproc_04`;

DELIMITER //

CREATE PROCEDURE `myproc_04`(INOUT _inout INT)

BEGIN

SELECT _inout;

SET _inout = 22;

SELECT _inout;

END;

DELIMITER;

-- 调用

SET @_inout = 88;

CALL myproc_04(@_inout);

SELECT @_inout;

-- 结论 inout参数能带值进入存储过程,但是如果存储过程中进行了改变,再次调用它 得到的值是存储过程需要输出的值

4 过程体中声明变量

语法 declare 变量名1[,变量名2...] 数据类型[默认值]

5 变量赋值

语法：SET 变量名 = 变量值 [,变量名= 变量值 ...]

6 用户变量

用户变量以@开头

6.1 在mysql客户端中使用用户变量

SELECT 'Hello World' into @x;

SELECT @x;

SET @y='Goodbye Cruel World';

SELECT @y;

SET @z=1+2+3;

SELECT @z;

6.2 #在存储过程中使用用户变量

CREATE PROCEDURE GreetWorld() SELECT CONCAT(@greeting,' World');

SET @greeting='Hello';

CALL GreetWorld();

6.3 -- #在存储过程间传递全局范围的用户变量

CREATE PROCEDURE p1() SET @last_proc='p1';

CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);

CALL p1();

CALL p2();

7 注释

MySQL存储过程可使用两种风格的注释：

双杠：--，该风格一般用于单行注释

C风格：一般用于多行注释

8 调用 call 函数名([参数])

9 存储过程查询

9.1 #查询存储过程

-- 方式一

SELECT NAME FROM mysql.proc WHERE db = 'bookkeeping';

-- 方式二 需要information_schema存在,否则会报错

SELECT routine_name FROM information_schema WHERE db = 'bookkeeping';

-- 方式三

SHOW PROCEDURE STATUS WHERE db = 'bookkeeping' ;

9.2 #查看存储过程详细信息

SHOW CREATE PROCEDURE 数据库.存储过程名;

10 存储过程的修改

ALTER PROCEDURE 更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

10.1 语法

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

characteristic:

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

10.2 参数说明

sp_name参数表示存储过程或函数的名称；

characteristic参数指定存储函数的特性。

CONTAINS SQL 表示子程序包含SQL语句，但不包含读或写数据的语句；

NO SQL 表示子程序中不包含SQL语句；

READS SQL DATA 表示子程序中包含读数据的语句；

MODIFIES SQL DATA 表示子程序中包含写数据的语句。

SQL SECURITY { DEFINER | INVOKER } 指明谁有权限来执行，DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。

COMMENT 'string' 是注释信息。

10.3 使用案例

-- 案例1 #将存储过程读myproc_01写权限改为MODIFIES SQL DATA，并指明调用者可以执行。

ALTER PROCEDURE myproc_01 MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT

-- 调用

SET @s = 88;

CALL myproc_01(@s);

SELECT @s;

-- 案例2 #将读写权限改为READS SQL DATA，并加上注释信息'FIND NAME'。

ALTER PROCEDURE myproc_01 READS SQL DATA COMMENT 'FIND NAME' ;

-- 调用

SET @s = 88;

CALL myproc_01(@s);

SELECT @s;

11 mysql存储过程的删除

DROP PROCEDURE [过程1[,过程2…]]

从MySQL的表格中删除一个或多个存储过程。

>>MySql存储过程的控制语句

1 变量作用域

内部变量在其作用范围内享有更高的优先权,当执行到end的时候,背部变量消失,不可再见,在存储过程外也找不到这个内部变量了,但是可以通过out参数或者将其值指派给会话变量来保存其值。

>>使用示例

DELIMITER //

CREATE PROCEDURE myproc_05()

BEGIN

DECLARE x1 VARCHAR(20) DEFAULT '测试变量作用域1';

BEGIN

DECLARE x1 VARCHAR(20) DEFAULT '测试变量作用域2';

SELECT x1;

END;

SELECT x1;

END;

DELIMITER;

2 条件语句

2.1 if-then-else 语句

说明:

如果满足if条件,就执行then后的语句,否则执行else后的语句,类似java中的if-else语句

DROP PROCEDURE IF EXISTS proc_if_then_else;

DELIMITER //

CREATE PROCEDURE proc_if_then_else(IN param INT,OUT print INT)

BEGIN

DECLARE var INT;

SET var = param +1;

IF var = 0 THEN

SET print = 666;

SELECT print;

END IF;

IF param = 0 THEN

SET print = 999;

SELECT print;

ELSE

SET print = 555;

SELECT print;

END IF;

END;

DELIMITER;

CALL proc_if_then_else(0,@res);

SELECT @res AS 输出结果;

2.2 case-when-then-else语句

说明:

case后是到时可能出现的情况,

case 条件变量

when 条件变量值1 then 结果1;

when 条件变量值2 then 结果2;

when 条件变量值n then 结果n;

else

不属于条件变量的结果

-- 示例:输入一个数,输入1 输出星期一,输入二 输出星期二 ... 大于7 或者小于0 输出不是有效的天数

DROP PROCEDURE IF EXISTS procCaseWhenThenElse;

DELIMITER //

CREATE PROCEDURE procCaseWhenThenElse(IN num INT,OUT weeks VARCHAR(10))

BEGIN

CASE num

WHEN 1 THEN SET weeks = '星期一';

WHEN 2 THEN SET weeks = '星期二';

WHEN 3 THEN SET weeks = '星期三';

WHEN 4 THEN SET weeks = '星期四';

WHEN 5 THEN SET weeks = '星期五';

WHEN 6 THEN SET weeks = '星期六';

WHEN 7 THEN SET weeks = '星期天';

ELSE SET weeks = '输入的不是有效的星期数';

END CASE;

END;

DELIMITER;

-- 调用

SET @in = 6;

CALL procCaseWhenThenElse(@in,@week);

SELECT @week AS 结果;

3 循环语句

3.1 while-do...end-while循环

语法 while 条件 do 循环体 end while

只要满足该条件就会一直执行

-- 此循环是先判断条件,如果满足,就循环,不满足,就不执行循环,理解参考java的while(条件){循环体}循环

DROP PROCEDURE IF EXISTS proc_whileDoEndIf;

DELIMITER ;;

CREATE PROCEDURE proc_whileDoEndIf()

BEGIN

DECLARE var INT;

DECLARE mes VARCHAR(20);

SET mes = '今天是星期:';

SET var =1;

WHILE var <= 7 DO

SET mes = CONCAT(mes,var);

SET var = var +1;

SELECT mes;

SET mes = '今天是星期:';

END WHILE;

END;

;;

DELIMITER;

CALL proc_whileDoEndIf();

3.2 repeat ... end repeat循环

此语句的特点是执行操作后检查结果

-- 此语句的特点是执行操作后检查结果

-- 次循环的作用是先执行循环体,然后在看条件,如果条件满足,就继续循环,如果不满足,就不再循环,理解可以参考java的do...while();循环

DROP PROCEDURE IF EXISTS proc_repeatEndRepeat;

DELIMITER //

CREATE PROCEDURE proc_repeatEndRepeat ()

BEGIN

DECLARE var INT;

DECLARE mes VARCHAR(50);

SET var=0;

SET mes = '无论如何这条消息我都要输出到控制台的';

REPEAT

SELECT mes;

SET var = var+1;

UNTIL var > 2 -- 这里不能加分号

END REPEAT;

END;

DELIMITER ;

-- 执行

CALL proc_repeatEndRepeat();

3.3 loop ... end loop 循环

可以理解为就是死循环,使用的时候最好加上跳出标记,不然你懂的.

小知识:LABLES标号

标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

-- 这里使用了标记:loop_lable

-- 标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

DROP PROCEDURE IF EXISTS proc_loopEndLoop;

DELIMITER //

CREATE PROCEDURE proc_loopEndLoop ()

BEGIN

DECLARE v INT;

DECLARE mes VARCHAR(40);

SET v=0,mes='每循环一次,就会有一个执行结果';

LOOP_LABLE:LOOP -- 这里使用了循环标记

SELECT mes;

SET v=v+2;

IF v >=5 THEN

SET mes = '当前循环次数大于判断条件了,本循环要终止了';

SELECT mes;

-- LEAVE LOOP_LABLE; -- 当循环次数大于 5次的时候,跳出本循环

END IF;

END LOOP;

END;

DELIMITER ;

CALL proc_loopEndLoop ();

3.4 ITERATE迭代

通过引用复合语句的标号,来从新开始复合语句

-- 通过引用复合语句的标号,来从新开始复合语句

DROP PROCEDURE IF EXISTS proc_iterate;

DELIMITER //

CREATE PROCEDURE proc_iterate()

BEGIN

DECLARE v INT;

DECLARE mes VARCHAR(50);

SET v=0,mes = '我是测试iterate迭代循环的测试语句';

LOOP_LABLE:LOOP -- 给loop循环打上一个标记

IF v=3 THEN -- 如果循环变量 = 3

SET v=v+1; -- 循环变量+1

ITERATE LOOP_LABLE; -- 迭代当前标签

END IF;

SELECT mes;

SET v=v+1;

IF v>=5 THEN -- 当循环变量>=5的时候,跳出循环

LEAVE LOOP_LABLE;

END IF;

END LOOP;

END;

DELIMITER ;

-- 调用

CALL proc_iterate();

>>mysql存储过程的基本函数

1 字符串类

-- charset('字符串'); //返回字符串字符集

SELECT CHARSET('你好,mysql');

-- concat('字符串1'[,'字符串2,...']) //连接字符串

SET @date = '08月21日';

SET @week = '星期二';

SET @temp = 36;

SELECT CONCAT('今天的日期是:',@date,',具体是:',@week,',今天温度是:',@temp);

-- INSTR (string ,substring ); //判断字符串string中是否包含substring,如果包含,返回

-- 第一次出现的位置,如果不包含,返回0

SET @str = '你好,java,现在是在学习mysql的存储过程';

SELECT INSTR(@str,'在学');

-- LOCATE (substring , string [,start_position ] ) //同INSTR,但可指定开始位置

SELECT LOCATE('你好,java,现在是在学习mysql的存储过程','mysql',2)

-- LCASE (string2 ) //转换成小写

SELECT LCASE('HELLO,Mysql');

-- UCASE (string2 ) //转换成大写

SELECT UCASE('HELLO,Mysql');

-- LEFT (string2 ,length ) //从string2中的左边起取length个字符

SELECT LEFT('你好,java,现在是在学习mysql的存储过程',6);

-- RIGHT(string2,LENGTH) //取string2最后length个字符

SELECT RIGHT('你好,java,现在是在学习mysql的存储过程',6);

-- LENGTH (string ) //string长度一个汉字在utf8 占三个长度

SELECT LENGTH('你好');

-- LOAD_FILE (file_name ) //从文件读取内容

-- LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length

SELECT LPAD('hello',20,'--//');

-- RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length

SELECT RPAD('hello',20,'--//');

-- LTRIM (string2 ) //去除前端空格

SELECT LTRIM(' 这是我的用户名 .. ');

-- RTRIM (string2 ) //去除后端空格

SELECT RTRIM(' 这是我的用户名 ') AS res;

-- REPEAT (string2 ,count ) //重复count次

SELECT REPEAT('java',5);

-- REPLACE (str ,search_str ,replace_str ) //在str中使用replace_str替换search_str,search_str不存在就不替换

SELECT REPLACE('这是一个测试使用的字符串,本来就是字符串','字','哈哈哈');

-- STRCMP (string1 ,string2 ) //比较两个字符串，如果这两个字符串相等返回0，如果第一个参数是根据当前的排序小于第二个参数顺序返回-1，否则返回1。

-- 如要比较两个数字型字符串,比较前,先保证位数一样,

SELECT STRCMP('879','088');

-- SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,

-- 注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

SELECT SUBSTRING('这是一个测试使用的字符串',3,2);

-- TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符

SELECT TRIM(' nidasjlkj sda '); -- 默认删除前后空格

SELECT TRIM(LEADING '，' FROM '，，barxxx'); -- //删除指定首字符 如'，‘

SELECT TRIM(BOTH '，' FROM '，7，bar，sd，，'); -- //删除指定首尾字符

SELECT TRIM(TRAILING ', ' FROM '，7，bar，sd, , '); -- // 删除指定尾字符

-- SPACE(count) //生成count个空格

SELECT SPACE(5);

2 数字类

-- ABS (number2 ) //绝对值

SELECT ABS(-9.99);

-- BIN (decimal_number ) //十进制转二进制

SELECT BIN(100);

-- HEX (DecimalNumber ) //转十六进制,针对整数有效

注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143

也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19

SELECT HEX(25);

SELECT HEX('DEF');

-- 浮点数取整 *.** 向上取整就是*+1 向下取整就是*

-- CEILING (number2 ) //向上取整

-- FLOOR (number2 ) //向下取整

SELECT CEILING(5.01);

SELECT FLOOR(5.01);

-- CONV(number2,from_base,to_base) //进制转换将number2从from_base进制转换为to_base进制

SELECT CONV(10,10,16);

-- FORMAT (number,decimal_places ) //保留小数位数,最后一位四舍五入

SELECT FORMAT('3.12592654',4);

-- ROUND (number [,decimals ]) //decimals没有的时候,返回number的整数并四舍五入,当decimals存在的时候,返回指定的decimals位小数,最后一位四射五入

SELECT ROUND(3.14158322); -- 3

SELECT ROUND(3.14158322,3); -- 3.142

SELECT ROUND(3,3); -- 3

SELECT ROUND(6.5,3); -- 3

-- LEAST (number , number2 [,..]) //求最小值,如果参数有字符串,返回0

SELECT LEAST('1',2,34,54,'ss'); -- 0

SELECT LEAST(99,2,34,54,88); -- 2

-- MOD (numerator ,denominator ) //求余

SELECT MOD(10,3);

-- POWER (number ,power ) //求number的power次方的结果

-- SQRT(number2) //开平方

SELECT POWER(2,64);

SELECT SQRT(3); -- 1.7320508075688772

-- RAND([seed]) //随机数

SELECT RAND(); -- 返回一个大于0小于1的17位小数的随机数

SELECT RAND(1); -- 返回一个指定seed的随机数

3 日期时间类

-- ADDTIME (expr1 ,添加到expr2 ) //添加到expr2 到 expr1 并返回结果。 expr1 是一个时间或日期时间表达式，expr2是一个时间表达式。

SELECT ADDTIME('2018-01-01','2018-01-02');

-- CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换fromTZ时区到toTZ时区

SELECT CONVERT_TZ('2014-02-24 00:00:00','+08:00','+09:00');

-- CURRENT_DATE ( ) //当前日期

-- CURRENT_TIME ( ) //当前时间

-- CURRENT_TIMESTAMP ( ) //当前时间戳

-- NOW ( ) //当前时间

SELECT CURRENT_DATE(); -- 2018-08-22 一般用于数据创建时候自动生成

SELECT CURRENT_TIME(); -- 14:06:08

SELECT CURRENT_TIMESTAMP(); -- 2018-08-22 14:06:08

SELECT NOW();-- 2018-08-22 14:06:08

-- DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间

-- DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间

/* 参数说明:

　　date:起始日期或者起始时间

　　expr:指定的是一个间隔值,在起始时间中增加或者减少,注意:expr是一个字符串.对于负值间隔,可以以"-"开头

　　unit:表示的是一个单位(年月日时分秒任意一个),比如,加上的是1天还是一个小时.

SELECT DATE_ADD('2018-08-22',INTERVAL 8 YEAR);

SELECT DATE_SUB('2018-08-22',INTERVAL 8 YEAR);

-- DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime

/*date 参数是合法的日期。format 规定日期/时间的输出格式*/

SELECT DATE_FORMAT(CURRENT_TIMESTAMP(),'%y年%m月%d日 %h时%m分');

-- DATEDIFF (date1 ,date2 ) //返回date1日期-date2日期的天数

-- TIMEDIFF (datetime1 ,datetime2 ) //两个时间差两个时间差的有效值最大是838:59:59

SELECT DATEDIFF(NOW(),'2017-06-05');

SELECT TIMEDIFF(NOW(),'2017-06-05 14:48:00');

-- EXTRACT (interval_name FROM date ) //从date中提取interval_name的指定部分,interval_name可以是时分秒

SELECT EXTRACT(YEAR FROM '2008-12-29 16:25:46.635');

-- MAKEDATE (year ,day ) //将day天数转换为指定year的日期形式

-- MAKETIME (hour ,minute ,second ) //返回 'hour:minute:second'形式的字符串

SELECT MAKEDATE(2018,45); -- 2018-02-14

SELECT MAKETIME(28,22,12);

-- SEC_TO_TIME (seconds ) //将指定的seconds秒转换为 '时:分:秒' 的字符串形式,能计算的最大返回值是838:59:59

-- STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示不好用

-- TIME_TO_SEC (time ) //将当天当前时间转换为过了多少秒

SELECT SEC_TO_TIME(2312312321); -- 838:59:59

SELECT STR_TO_DATE('2017-10-19 16:47:00', '%Y-%m-%d %H:%i:%s') AS TIME

SELECT TIME_TO_SEC('16:47:00');

-- DAY (date ) //返回日期的天

-- YEAR (datetime ) //返回datetime是****年

-- WEEK (date_time [,start_of_week ]) //第几周

-- DAYOFMONTH(datetime) //返回datetime是月的第几天

-- HOUR(datetime) //返回datetime的第*个小时

-- MONTH(datetime) //返回datetime的第*个月

-- MINUTE(datetime) //返回datetime的第*分钟

-- DATE (datetime ) //返回datetime的日期部分

-- MONTHNAME (date ) //返回日期date的月份的英文名称

-- DAYNAME (date ) //英文星期

-- DAYOFYEAR (date ) //一年中的第几天

-- DAYOFWEEK (date ) //星期(1-7) ,1为星期天

SELECT DAY(NOW());

SELECT YEAR(NOW());

SELECT WEEK('2017-12-28 16:47:00'); -- 返回date_time是当年的第几周

SELECT DAYOFMONTH(NOW());

SELECT HOUR(NOW());

SELECT MONTH(NOW());

SELECT MINUTE(NOW());

SELECT DATE('2018-08-22 14:06:08'); -- 2018-08-22

SELECT MONTHNAME(NOW());

SELECT DAYNAME(NOW());

SELECT DAYOFWEEK('2018-08-20');

SELECT DAYOFYEAR(NOW());

-- LAST_DAY(date) //返回date当月的最大号数

SELECT LAST_DAY(NOW());

-- MICROSECOND(expr) //返回时间或日期时间表达式expr的微秒，这个数字范围为 0 到 999999。

SELECT MICROSECOND('12:00:00.123456'); -- 123456