MySQL数据库高级SQL语句及存储过程

目录

一、高级SQL语句

（一）case语句

1.语法定义

2.示例

（二）空值(NULL) 和 无值(' ') 

1.区别

2.示例

（1）字符长度

（2）判断方法

① 空值(NULL)

② 无值(' ') 

（3）count()统计

（三）正则表达式

1.语法

2.示例

二、存储过程

（一）定义

（二）使用过程

（三）优点

（四）创建存储过程

1.修改SQL语句结束符

2.创建存储过程

3.把结束符改回分号

4.调用存储过程

5.示例

（五）查看存储过程

1.语法

2.示例

（六）存储过程的参数

1.in 输入参数

（1）语法

（2）示例

2.out 输出参数

（1）语法

（2）示例

​编辑

3.inout输入输出参数

（1）语法

（2）示例

（七）删除存储过程

1.语法

2.示例

（八）存储过程的控制语句

1. if 条件语句

（1）语法

（2）示例

① 准备一个表

② 创建

③查看结果

2. while 循环语句

（1）语法

（2）示例

三、实操一次性往数据库中插入上万条数据

（一）存储过程实现上万数据插入

（二）shell脚本实现上万数据插入

---

一、高级SQL语句

（一）case语句

1.语法定义

根据一个字段的条件设置一个新的字段的值

select case ("字段名")when "条件1" then "结果1"when "条件2" then "结果2"...[else "结果N"]end
from "表名";
# "条件" 可以是一个数值或是公式。 else 子句则并不是必须的。

2.示例

先准备location和store_info两个表格

select Store_Name, case Store_Name when 'Los Angeles' then Sales * 2   #查询到Los Angeles字段时，Sales的值*2when 'Boston' then 2000             #查询到Boston字段时，Sales的值为2000else Sales                          #查询到其余字段时，Sales的值不变end 
"New Sales",Date                      #将查询结果取别名为New Sales并和Date字段一起输出
from Store_Info;

（二）空值(NULL) 和 无值(' ') 

1.区别

	空值(NULL)	无值(' ')
长度	NULL	0
判断方法	IS NULL 或者 IS NOT NULL	=''或者<>''
count()统计	自动忽略	不会忽略

2.示例

（1）字符长度

select length(NULL), length(''), length('1');
#查询空值、无值、1的字符长度

（2）判断方法

① 空值(NULL)

select Store_Name from location where Store_Name IS NULL;
#查询location表中Store_Name字段为空值的记录select Store_Name from location where Store_Name IS NOT NULL;
#查询location表中Store_Name字段中非空值的记录

② 无值(' ') 

select Store_Name from location where Store_Name = '';
#查询location表中Store_Name字段为无值的记录select Store_Name from location where Store_Name <> '';
select Store_Name from location where Store_Name ！='';
#查询location表中Store_Name字段中非无值的记录

（3）count()统计

会忽略空值行，统计时不计算在内

select count(*) from location;
#统计location表中所有值的个数select count(Store_Name) from location;
#统计location表中Store_Name字段值的个数

（三）正则表达式

1.语法

select查询中的where查询可以匹配正则表达式

select "字段" from "表名" WHERE "字段" regexp {模式};

匹配模式	描述	实例
^	匹配文本的开始字符	‘^bd’ 匹配以 bd 开头的字符串
$	匹配文本的结束字符	‘qn$’ 匹配以 qn 结尾的字符串
.	匹配任何单个字符	‘s.t’ 匹配任何 s 和 t 之间有一个字符的字符串
*	匹配零个或多个在它前面的字符	‘fo*t’ 匹配 t 前面有任意个 o
+	匹配前面的字符 1 次或多次	‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串
字符串	匹配包含指定的字符串	‘clo’ 匹配含有 clo 的字符串
p1|p2	匹配 p1 或 p2	‘bg|fg’ 匹配 bg 或者 fg
[...]	匹配字符集合中的任意一个字符	‘[abc]’ 匹配 a 或者 b 或者 c
[^...]	匹配不在括号中的任何字符	‘[^ab]’ 匹配不包含 a 或者 b 的字符串
{n}	匹配前面的字符串 n 次	‘g{2}’ 匹配含有 2 个 g 的字符串
{n,m}	匹配前面的字符串至少 n 次，至多m 次	‘f{1,3}’ 匹配 f 最少 1 次，最多 3 次

2.示例

select * from store_info where Store_Name regexp 'os';
#查询store_info表中Store_Name字段中包含os的记录
select * from store_info where Store_Name regexp '^[A-G]';
#查询store_info表中Store_Name字段中除了包含A-G的记录
select * from store_info where Store_Name regexp 'Ho|Bo';
#查询store_info表中Store_Name字段中包含Ho或者Bo的记录

二、存储过程

（一）定义

       存储过程是一组为了完成特定功能的SQL语句集合，即数据库脚本。

（二）使用过程

       存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。

（三）优点

1.执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率

2.SQL语句加上控制语句的集合，灵活性高

3.在服务器端存储，客户端调用时，降低网络负载

4.可多次重复被调用，可随时修改，不影响客户端调用

5.可完成所有的数据库操作，也可控制数据库的信息访问权限

（四）创建存储过程

1.修改SQL语句结束符

delimiter $$							
#将语句的结束符号从分号;临时改为两个$$(可以是自定义)

2.创建存储过程

use 库名$$
#切换库     
create procedure 存储过程名()
#创建存储过程，过程名为Proc，不带参数       
-> begin  
#过程体以关键字 begin 开始
-> SQL语句序列; 
#过程体语句 
-> end$$
#过程体以关键字 end 结束

3.把结束符改回分号

delimiter ;
#将语句的结束符号恢复为分号

4.调用存储过程

call 存储过程名;
#调用存储过程

5.示例

delimiter $$
#将语句的结束符号从分号;临时改为两个$$							       
create procedure Proc()	
#创建存储过程，过程名为Proc，不带参数				                
-> begin	
#过程体以关键字 begin 开始							
-> select * from store_info;
#显示store_info表的表内容			                        
-> end $$	
#过程体以关键字 end 结束							
delimiter ;
#将语句的结束符号恢复为分号
call Proc;
#调用存储过程

（五）查看存储过程

1.语法

show create procedure [数据库.]存储过程名;		
#查看某个存储过程的具体信息

2.示例

show create procedure Proc;
#查看Proc存储过程
show procedure status [like '%Proc%'] \G
#查看存储过程的状态以竖列显示

（六）存储过程的参数

1.in 输入参数

表示调用者向过程传入值（传入值可以是字面量或变量）

（1）语法

create procedure Proc1(in 传入参数名 参数数据类型)；

（2）示例

delimiter $$				
create procedure Proc1(in inname char(16))	
#创建存储过程Proc1，添加传入参数inname 参数数据类型char(16)	
-> begin					
-> select * from store_info where Store_Name = inname;
#查看store_info表中新添加的传入参数
-> end $$					
delimiter ;					call Proc1('Boston');
#调用存储过程

2.out 输出参数

表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

（1）语法

create procedure Proc1(out 传出参数名 参数数据类型)；

（2）示例

delimiter $$
CREATE PROCEDURE proc3(in in_Sales INT,OUT out_name varchar(20))
BEGIN
SELECT Store_Name INTO out_name FROM store_info where Sales<in_Sales;
END$$
delimiter ;
CALL proc3(400,@dest);
SELECT @dest;
#当输入参数大于Sales字段的值时，显示Store_Name字段##select 字段或聚合函数 into +输出参数名称 from 表名 where 条件
##输出参数的值一般是一个确定的值，多个会报错

3.inout输入输出参数

既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

（1）语法

create procedure Proc1(inout 传入传出参数名 参数数据类型)；

（2）示例

delimiter $$
CREATE PROCEDURE proc4(INOUT i INT)
BEGIN
SELECT COUNT(Store_Name) INTO i FROM store_info where Sales>i;
END$$
delimiter ;
set @a=400;
CALL proc4(@a);
SELECT @a;
#统计Sales字段的值大于400的个数

（七）删除存储过程

1.语法

drop procedure if exists Proc;		
#仅当存在时删除，不添加 if exists 时，如果指定的过程不存在，则产生一个错误

2.示例

drop procedure if exists Proc2;		
#删除Proc2存储过程

（八）存储过程的控制语句

1. if 条件语句

（1）语法

if 条件表达式 thenSQL语句序列1
else SQL语句序列2
end if;

（2）示例

① 准备一个表

② 创建

delimiter $$  
create procedure proc2(in pro int) 
#创建 proc2存储过程，并添加传入参数pro ，参数类型为int
-> begin 
-> declare var int;  
-> set var=pro*2; #设置变量名  
-> if var>=500 then #if条件判断，当var>=500时，则
-> update store_info set Sales=Sales+100; #Sales字段的值+100
-> else #否则就执行下一条命令
-> update store_info set Sales=Sales-100;  #Sales字段的值-100
-> end if;  
-> end $$delimiter ;call Proc2(6);

③查看结果

2. while 循环语句

（1）语法

while 条件表达式
doSQL语句序列;set 条件迭代;
end while;

（2）示例

DELIMITER $$  
CREATE PROCEDURE proc5()
begin 
declare var int(10);  
set var=1;
create table t5(id int primary key auto_increment,name varchar(20));  
while var<6 do  
insert into t5(name) values(concat('student',var)); 
set var=var+1;  
end while;  
end $$  
#循环插入6条数据，student后缀从0开始
DELIMITER ;
CALL proc5;
select * from t5;

三、实操一次性往数据库中插入上万条数据

（一）存储过程实现上万数据插入

create procedure proc7 ()
begin
declare i int;
set i = 1;
create table test03 (id int primary key auto_increment, name varchar(20));
while i <= 1000000;
do insert into test03 (name) values (concat('student', i));
set i = i + 1;
end while;
end $$delimiter ;
call proc7;
select count(*) from test03;

（二）shell脚本实现上万数据插入