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

2024-01-03 00:01:33

一、高级SQL语句

（一）case语句

1.语法定义

2.示例

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

一、高级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的值*2
  when 'Boston' then 2000             #查询到Boston字段时，Sales的值为2000
  else 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 条件表达式 then
 SQL语句序列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 条件表达式
do
    SQL语句序列;
	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脚本实现上万数据插入

文章来源:https://blog.csdn.net/byq002488/article/details/135270584
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！

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脚本实现上万数据插入

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