09_MySQL_第八课_内置函数、预处理、存储过程

一、内置函数

• 数字类
• 字符串类
• 时间类
• 加密函数
• 判断函数

  1、数字类

select rand() from dual;

select * from stuInfo order by rand() limit 2;  # 随机抽两个学生

select round(3.5);     # 四舍五入  4

select ceil(2.1);      # 向上取整  3

select floor(2.9);     # 向下取整  2

select truncate(3.1415926,3)  # 直接截取小数点后几位

2、字符串类

select ucase('i am b boy!');      # 转成大写

select lcase('I AM A BOY!');      # 转成小写

select left('abcd',3);            # 从左边截取 abc

select right('abcd',3);           # 从左边截取 bcd

select substring('abcde',2,3)     # 从1开始，第二个开始，取3个

select concate('中国','-','上海');  # 字符串联接

select stuName,coalesce (writtenExam,'缺考'),coalesce (labExam,'缺考'） from stuInfo natural left join stuMarks;     # coalesce(str1,st2); # 如果str1不为空，显示str1，如果str1为空，显示str2

select length('锄禾日当午');  # 字节长度

select char_length('锄禾日当午'); # 字符个数

3、时间类

select unix_timestamp();                # 获取时间戳

select from_unixtime(unix_timestamp()); # 将时间戳转成年月时分秒的格式

select now();                           # 获取当前日期时间

select year(now())'年',month(now())'月',day(now())'日',hour(now())'小时',minute(now()）'分钟',second(now()）'秒');

select dayName(now());                    # 星期几

select monthName(now()),dayofyear(now()); # 本年的第几个月,第几天

select datediff(now(),'2008-8-8');        # 两个时间差多少天

• **cast() **函数进行数据类型转换（两个表达式可以隐性转换才可以转换）
• **convert()**函数是把日期转换为新数据类型的通用函数

select cast(now() as time);

select convert(now(),date),convert (now(),time);   # 将now()转成日期2018-09-16 和 时间16:08:03

4、加密函数

• md5() 单向加密
  • 数据字典实现解密
  • 穷举法
• sha()

select md5('aa'),sha('aa');

create table users(
id int,
name varchar(20),
pwd char(32)
);

5、判断函数

语法：

if(表达式，值1,值2)

select if(10%2,'偶数','奇数');

select stuName,ch,math,if(ch>=60 && math>=60,'通过','不通过') '是否通过' from stu;

二、预处理 prepare

• 预编译一次，可以多次执行。
  • 用来解决一条SQL语句频繁执行的问题。
  • 只要结构一样就可以。
• ？位置占位符
• 变量@开头，通过set给变量赋值

预处理语句：prepare 预处理名字 from 'sql语句'
执行预处理：execute 预处理名字[using 变量]

案例：

prepare stmt from 'select * from stuInfo';  # 创建预处理            # stmt (statement)
execute stmt;                               # 执行预处理多次执行情况下，速度会提升。

delimiter //

prepare stmt from 'select * from stuInfo where stuNo=?'; # ?为位置占位符   @id全局变量  ------------------这个很重要！！！
set @id='s25301';                                        # 通过set给变量赋值
execute stmt using @id //                                # 全局变量替换占位符,传递参数

prepare stmt from 'select * from stuInfo where stuSex=? and stuAddress=?';
set @sex='男';
set @addr='北京';
execute stmt using @sex,@addr//

三、存储过程 procedure

存储过程——函数。

• **没有return **的叫做过程
• **有return **的叫做函数

1、存储过程的优点

• 存储过程可以减少网络流量
• 允许模块化设计
• 支持事务

2、创建存储过程

• 由于过程中有很多sql语句，每个语句的结束都要用（ ；）表示。
• 默认情况下，分号即表示语句结束，又表示向服务器发送SQL语句。
• 我们希望分号仅表示语句的结束，不要将SQL语句发送到服务器执行，通过delimiter 来更改结束符。

语法：

create procedure 存储过程名（参数）
begin
    // sql语句
end;

案例：

delimiter //
create procedure proc()
begin
  select *from stuInfo;
end //

3、调用存储过程

语句：

call 存储过程名()  # 创建存储过程

案例：

call proc()        # 可以多次调用存储过程

4、删除存储过程

语法：

drop procedure [if exists] 存储过程名

案例：

drop procedure proc//    # 删除存储过程

5、查看存储过程信息

语法:

show create procedure 存储过程名 \G

案例：

show create procedure proc \G

show procedure status \G          # 显示所有存储过程

6、存储过程的参数

• 存储过程的参数分为：
  • 输入参数（in）【默认】
  • 输出参数（out）
  • 输入输出参数（inout）
• 存储过程不能使用return返回值，要返回值只能通过“输出参数”来向外传递值。

create procedure proc(in num1,out num2,inout num3);  # num1,num2,num3既可以是输入参数也是输出参数

6.1 存储过程的输入参数

案例：传递学号，获取对应的信息

create procedure proc(in param varchar(10))  # 输入参数
begin
  select * from stuInfo where stuNo=param;
end //

call proc('s25301') //
call proc('s25302') //

局部变量 declare,不用写@符号）

create procedure proc(name varcahr(10))
begin
  declare seat tinyint;  # 声明局部变量
  select stuSeat into seat from stuInfo where stuName=name; # into seat 给局部变量赋值方法二
  select * from stuInfo where stuSeat=seat+1 or stuSeat-1;
end //

• 注意事项⚠️：
  • 通过 decalre 关键字声明局部变量。
  • 给局部变量赋值有两种方法
    • 方法一：set 变量名=值
    • 方法二： select 字段 into 变量 from 表 where 条件
  • 声明的变量不能与列名同名

6.2 存储过程的输出参数

create procedure proc(num int,out result int)
begin
  set result=num*num;
end //

call proc(10,@result)

6.3存储过程的输入输出参数

create procedure proc(inout num int)     # inout 输入输出参数
begin
  set num=num*num;
end //

set @num=10;
call proc(@num);
select @num # 100

---

赏

谢谢你请我吃糖果

支付宝

微信

• 本文作者： 梁俊可
• 本文链接： http://ljk3d.com/2021/10/19/mySqlNote/09_MySQL_第八课_内置函数、预处理、存储过程/
• 版权声明： 梁俊可工作室