1.变量
系统变量和自定义变量
系统变量：根据作用范围不同分为：全局变量和会话变量
自定义变量：根据作用范围不同分为：用户变量和局部变量
查看系统变量：show
系统变量：
# 查看全局变量
show global variables ;
# 查看会话变量，默认是会话
show variables ;
show session variables ;
# 模糊条件
show global variables like '%char%' ;
# 查看某个系统变量（global）| 会话变量的值 （session）
# 语法：
# select @@session.系统变量名 ;
# select @@global.系统变量名 ;
select @@session.autocommit;
# +-------------------+
# |@@global.autocommit|
# +-------------------+
# |1 |
# +-------------------+
# select @@global.tx_isolation;
select @@session.character_set_client;
# +------------------------------+
# |@@session.character_set_client|
# +------------------------------+
# |utf8mb4 |
# +------------------------------+
# 为某个系统变量赋值
# 语法:
# 方式一 set global | session 系统变量名 = 值
# 方式二 set @@global.系统变量名 = 值 ;
# set @@session.系统变量名 = 值 ;自定义变量：
# 自定义变量
# 用户变量：作用域 session，一个session 有效
# 申明并初始化（也叫赋值）
# 方式一
# set @变量名 = 值;
# 方式二
# set @变量名 := 值;
# 方式三
# select @变量名 := 值;
# 赋值
# 方式一
# set @变量名 = 值;
# 方式二
# set @变量名 := 值;
# 方式三
# select @变量名 := 值;
# 方式四
# select 字段 into @变量名 from 表;
# 查看用户变量名
# select @用户变量名;
# demo
set @count = 100;
select @count ;
# +------+
# |@count|
# +------+
# |100 |
# +------+
select count(*) into @count from commodity_order;
select @count ;
# +------+
# |@count|
# +------+
# |1 |
# +------+
# 局部变量 仅仅在begin and 中有效,并且在第一的位置
# 声明
# declare 变量名 类型;
# # 声明 并赋值
# declare 变量名 类型 default 值;
# 赋值
# 方式一
# set 变量名 = 值;
# 方式二
# set 变量名 := 值;
# 方式三
# select 变量名 := 值;
# 方式四
# select 字段 into 变量名 from 表;
2.存储过程
存储过程和函数 类似java中的方法（好处：提高代码的复用性）
存储过程：一组预先编译好的sql语句的集合，减少和数据库服务器连接的次数
语法：
# 存储过程
# 创建语法
# 语法
# create procedure 存储过程名 (参数列表)
# begin
# 存储过程体(一组合法的sql语句)
#
# end ;
# 参数列表有三部分： 参数模式，参数名，参数类型
# eg： in name varchar(20)
# 参数模式 in(传入值) , out（传出值） , inout（既可以作为传入值，也可以作为传出值）
# 如果存储过程只有一句话 begin end 可以省略
# 存储过程体每条sql语句结尾必须加 ; (因为 存储过程结束开始是以begin 和 end 为标志)
# 存储过程的结尾 可以用 delimiter(翻译：定义符号;界定符号) 重新设置
# 存储过程一般情况下可以做增删改（比如插入上万条数据）
# 调用
# call 存储过程名(实参列表);
①空参的存储过程
# demo 往 commodity_order 插入5 条 记录
select id, name, custom_id, is_delete from commodity_order;
# +--+----+---------+---------+
# |id|name|custom_id|is_delete|
# +--+----+---------+---------+
# |1 |置办年货|1 |0 |
# +--+----+---------+---------+
delimiter $
create procedure batchInsert()
begin
insert into commodity_order (name, custom_id, is_delete)
values ('郭靖1', 1, 0),
('郭靖2', 1, 0),
('郭靖3', 1, 0),
('郭靖4', 1, 0),
# 每一条 sql 结束 用 ; 结尾
('郭靖5', 1, 0);
end $
# 调用(结束符 改成了le 所以不用 ; )
call batchInsert() ;
select id, name, custom_id, is_delete from commodity_order;
# +--+----+---------+---------+
# |id|name|custom_id|is_delete|
# +--+----+---------+---------+
# |1 |置办年货|1 |0 |
# |17|郭靖1 |1 |0 |
# |18|郭靖2 |1 |0 |
# |19|郭靖3 |1 |0 |
# |20|郭靖4 |1 |0 |
# |21|郭靖5 |1 |0 |
# +--+----+---------+---------+
②带 in的存储过程
#
select * from commodity_order;
# +--+----+---------+---------+
# |id|name|custom_id|is_delete|
# +--+----+---------+---------+
# |1 |置办年货|1 |0 |
# +--+----+---------+---------+
select * from commodity_order_detail;
# +--+--------+--------------+------------+---------------+---------+
# |id|order_id|commodity_name|commodity_id|commodity_count|is_delete|
# +--+--------+--------------+------------+---------------+---------+
# |1 |1 |鞭炮(edit) |1 |111111 |0 |
# |2 |1 |春联(edit) |2 |2222 |0 |
# |3 |1 |牛 |3 |1 |0 |
# +--+--------+--------------+------------+---------------+---------+
create procedure getOrderDetail(in orderId varchar(20))
begin
select *
from commodity_order o
inner join commodity_order_detail d
on o.id = d.order_id
where o.id = orderId;
end;
call getOrderDetail(1);
# +--+----+---------+---------+--+--------+--------------+------------+---------------+---------+
# |id|name|custom_id|is_delete|id|order_id|commodity_name|commodity_id|commodity_count|is_delete|
# +--+----+---------+---------+--+--------+--------------+------------+---------------+---------+
# |1 |置办年货|1 |0 |1 |1 |鞭炮(edit) |1 |111111 |0 |
# |1 |置办年货|1 |0 |2 |1 |春联(edit) |2 |2222 |0 |
# |1 |置办年货|1 |0 |3 |1 |牛 |3 |1 |0 |
# +--+----+---------+---------+--+--------+--------------+------------+---------------+---------+
# 根据 custom_id 判断订单是否存在
create procedure isExists(in customId int)
begin
# 声明并初始化
declare isExists int default 0;
# 赋值
select count(*) into isExists from commodity_order where custom_id = customId;
# 查看
select if(isExists >0,'成功','失败');
end;
call isExists(1);
# +-------------------------+
# |if(isExists >0,'成功','失败')|
# +-------------------------+
# |成功 |
# +-------------------------+
call isExists(2);
# +-------------------------+
# |if(isExists >0,'成功','失败')|
# +-------------------------+
# |失败 |
# +-------------------------+③带out 的存储过程
# 带out 模式的存储过程
# 带一个out
create procedure out_model(in id varchar(20),out name1 varchar(20))
begin
select name into name1 from commodity_order o where o.id = id;
end;
# 调用
call out_model(1,@boy_name);
select @boy_name;
# +---------+
# |@boy_name|
# +---------+
# |置办年货 |
# +---------+
# 带两个out
create procedure out_model_two(in id varchar(20),out name1 varchar(20),out isDelete int)
begin
select name ,is_delete into name1 ,isDelete from commodity_order o where o.id = id;
end;
# 调用
call out_model_two(1,@boy_name,@isDelete);
select @boy_name,@isDelete;
# +---------+---------+
# |@boy_name|@isDelete|
# +---------+---------+
# |置办年货 |0 |④带inout的存储过程
# 带有inout 模式的存储过程
create procedure inout_procedure(inout a int,inout b int)
begin
set a = 2*a;
set b = 2*b;
end;
# 定义两个用户变量接受返回的值（需要加 @ 否则就是语法错误）
set @A=1;
set @B=10;
call inout_procedure(@A ,@B);
select @A,@B;
# +--+--+
# |@A|@B|
# +--+--+
# |2 |20|
# +--+--+
# 删除存储过程
# 语法： drop procedure 存储过程名
# 查看存储过程（包括创建存储过程的语句）
# 语法：show create procedure 存储过程名字 不需要括号和括号里的形参列表
show create procedure isExists;
# +---------+---------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+------------------+
# |Procedure|sql_mode |Create Procedure |character_set_client|collation_connection|Database Collation|
# +---------+---------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+------------------+
# |isExists |ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION|CREATE DEFINER=`root`@`localhost` PROCEDURE `isExists`(in customId int) |utf8mb4 |utf8mb4_0900_ai_ci |utf8mb4_0900_ai_ci|
# | | |begin | | | |
# | | |# 声明并初始化 | | | |
# | | |declare isExists int default 0; | | | |
# | | |# 赋值 | | | |
# | | |select count(*) into isExists from commodity_order where custom_id = customId; | | | |
# | | |# 查看 | | | |
# | | |select if(isExists >0,'成功','失败'); | | | |
# | | |end | | | |
# +---------+---------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+------------------+
3.函数
存储过程:可以没有返回，可以有一个返回甚至多个返回，适合做批量插入，批量更新
函数：有且只有一个返回，适合做处理数据后返回 一个结果
# 函数 语法
create function 函数名(参数列表) returns 返回值类型
begin
函数体
end;
# 参数列表：参数名 + 参数类型
# 函数体 必须返回 return ： return 返回值
# 函数体只有一句话可以省略 begin end
# 调用语法
select 函数名(参数列表) ;
#
SET GLOBAL log_bin_trust_function_creators = 1;
# 无参数： 返回order 的总数量
create function getCount() returns int
begin
declare ct int default 0;
select count(*) into ct from commodity_order ;
# function 必须要有 return 语句
return ct ;
end;
select getCount();
# v+----------+
# |getCount()|
# +----------+
# |1 |
# +----------+
# 一个参数，有返回值
create function one_para(id int) returns int
begin
select count(*) into @ct from commodity_order where id = id ;
# function 必须要有 return 语句
return @ct ;
end;
select(1);
# +-+
# |1|
# +-+
# |1|
# +-+
# 函数的查看(查看函数的函数体etc)
# 语法：
# show create function 函数名
show create function one_para;
# +--------+---------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+------------------+
# |Function|sql_mode |Create Function |character_set_client|collation_connection|Database Collation|
# +--------+---------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+------------------+
# |one_para|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION|CREATE DEFINER=`root`@`localhost` FUNCTION `one_para`(id int) RETURNS int |utf8mb4 |utf8mb4_0900_ai_ci |utf8mb4_0900_ai_ci|
# | | |begin | | | |
# | | |# 定义用户变量(会话有效) | | | |
# | | |set @ct = 0; | | | |
# | | |select count(*) into @ct from commodity_order where id = id ; | | | |
# | | |# function 必须要有 return 语句 | | | |
# | | |return @ct ; | | | |
# | | |end | | | |
# +--------+---------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+------------------+
# 删除函数
drop function 函数名;
一.
流程控制结构：
顺序结构：
分支结构：if
循环结构：
循环：
2469
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
