MySQL———高阶语句（包括排列、中位数、累加、百分比、正则、存储过程等）

本文链接：https://blog.csdn.net/qq_44079072/article/details/120073205

本文详细介绍了如何在 MySQL 中进行数据处理，包括计算排名、中位数、累计总计、各行份额百分比等方法，并探讨了空值与无值的区别。此外，还讲解了正则表达式的常用模式及语法。最后，深入讨论了 MySQL 的存储过程，包括创建、调用、查看和删除存储过程，以及带参数和不带参数的存储过程实例。

摘要由CSDN通过智能技术生成

四、一些对数据的处理方法

create table Total_Sales (Name char(10),Sales int(5));
insert into Total_Sales values ('zhangsan',10);
insert into Total_Sales values ('lisi',15);
insert into Total_Sales values ('wangwu',20);
insert into Total_Sales values ('zhaoliu',40);
insert into Total_Sales values ('sunqi',50);
insert into Total_Sales values ('zhouba',20);
insert into Total_Sales values ('wujiu',30);

在这里插入图片描述

4.1、算排名

功能：表格自我连结（self join）,然后将结果依序列出，算出每一行之前（包含那一行本身）有多少行数

select A1.Name,A1.Sales, count(A2.Sales) Rank from Total_Sales A1,Total_Sales A2
where A1.Sales<A2.Sales or (A1.Sales=A2.Sales and A1.Name=A2.Name)
group by A1.Name,A1.Sales order by A1.Sales desc;
#统计Sales字段的值是比自己本身的值小的以及Sales字段和Name字段都相同的数量，比如zhangsan为6+1=7

在这里插入图片描述

4.2、算中位数

#每个派生表必须有自己的别名，所以A3必须要有
select Name,Sales middle from (select A1.Name,A1.Sales,count(A2.Sales) rank from Total_Sales A1,Total_Sales A2 where A1.Sales < A2.Sales or (A1.Sales=A2.Sales and A1.Name>=A2.Name) group by A1.Name,A1.Sales order by A1.Sales desc) A3
where A3.rank=(select (count(*)+1) div 2 from Total_Sales);
#div是mysql中算出商的方式

在这里插入图片描述

1.我们先把排名表创建一个视图
create view v_1 as (select A1.Name, A1.Sales,count(A2.Salees) Rank from Total_Sales A1, Total_Sales  A2 where A2.Sales > A1.Sales or (A2.Sales = A1.Sales and A2.Name >= A1.Name) GROUP BY  A1.Name,A1.Sales ORDER BY count(A1.Sales));
2.然后查询中位数的那行数据，这边我们要用到where判断语句结合count（*）得出行数+1之后除以2即可
select name,sales 'middle sales' from v_1 where rank = (select (count(*)+1) div 2 from v_1);
#div是mysql中算出商的方式

在这里插入图片描述

4.3、算累计总计

作用：表格自我连结（self join）,然后将结果依序列出，算出每一行之前（包含那一行本身）的总和

select A1.Name, A1.Sales, SUM(A2.Sales) Total_Sales ,count(A2.Sales) Rank from Total_Sales A1,Total_Sales A2 where A2.Sales > A1.Sales or (A2.Sales = A1.Sales and A2.Name >= A1.Name) GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales desc;
#count(A2.Sales) 或者A1.Sales desc相同的结果
#只要添加一个sum函数即可，就能到每行都能累加

在这里插入图片描述

4.4、算各行份额占总额的百分比

select A1.Name, A1.Sales, ROUND(A1.Sales/(select sum(Sales) from Total_Sales)*100,2) || '%'  Per_Sales,count(A2.Sales) Rank from Total_Sales A1, Total_Sales A2 where A2.Sales > A1.Sales or (A2.Sales = A1.Sales and A2.Name >= A1.Name) GROUP BY A1.Name,A1.Sales ORDER BY count(A2.Sales);
#添加一个round函数，函数中当前行的份额除以总份额

在这里插入图片描述

4.5、算各行份额占当前行累加总份额的百分比

select A1.Name, A1.Sales, ROUND(A1.Sales/SUM(A2.Sales)*100,2) || '%'  Per_Sales ,count(A2.Sales) Rank from Total_Sales A1, Total_Sales A2 where A2.Sales > A1.Sales or (A2.Sales = A1.Sales and A2.Name >= A1.Name) GROUP BY A1.Name,A1.Sales ORDER BY count(A2.Sales);
#把分母改为SUM(A2.Sales)即可

在这里插入图片描述

五：补充:空值（NULL）与无值（’’）的区别

区别：

无值长度为0，不占空间;而NULL值的长度为null，占用空间
is null 或者is not null 用来判断是不是空值，无法判断是不是无值
无值使用“=”或者“<>”来处理.<>代表不等于
count()计算时，NULL会被忽略，无值会加入计算

5.1、实验验证

select length(null),length(''),length('1');

①查看无值以及空值所占空间

在这里插入图片描述

② 判断空值以及null值

select * from City;
select * from City where name is null;
select * from City where name is not null;

在这里插入图片描述

③ 判断无值

select * from City where name = '';
select * from City where name <> '';

在这里插入图片描述

④通过查看行数查看空值以及无值

#查看行数包括null和无值
select count(*) from City ;
#查看行数，包括无值，但不包括空值
select count(name) from City ;

在这里插入图片描述

六、正则表达式

6.1、常用的正则表达式：

正则表达式	说明	实例
^	匹配文本的开始字符
$	匹配文本的结束字符
.	匹配任何单个字符
*	匹配零个或多个在它前面的字符
+	匹配前面的字符1次或多次
字符串	匹配包含指定的字符串
p1\|p2	匹配 p1 或 p2
[…]	匹配字符集合中的任意一个字符
[^…]	匹配不在括号中的任何字符
{n}	匹配前面的字符串n次
{n,m}	匹配前面的字符串至少n次，至多m次

6.2、语法

语法说明：regexp

select 字段 from 表格 where 字段 regexp {模式};

select * from Store_Info where Store_Name regexp 'os';

在这里插入图片描述

select * from Store_Info where Store_Name regexp '^[A-G]';

在这里插入图片描述

select * from Store_Info where Store_Name regexp 'Ho|Bo';

在这里插入图片描述

七、存储过程

7.1、存储过程简介

MysQL数据库存储过程是一组为了完成特定功能的SQL语句的集合
存储过程这个功能是从5.0版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可
操作数据库的传统SQL语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高

7.2、存储过程优点

执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
SQL语句加上控制语句的集合，灵活性高
在服务器端存储，客户端调用时，降低网络负载
可多次重复被调用，可随时修改，不影响客户端调用
可完成所有的数据库操作，也可控制数据库的信息访问权限

7.3、存储过程的运用

7.3.1、不带参数

1.创建存储过程
delimiter $$    								#将语句的结束符号从分号；临时修改，以防出问题，可以自定义
create procedure proc()  	                    #创建存储过程，过程名proc，不带参数
begin   		    						    #过程体以关键字BEGIN开始
select * from 表格 where 条件语句;  	            #过程体语句
end $$    									    #过程体以关键字END结尾
delimiter ; 			    					#将语句的结束符号恢复为分号

2.调用存储过程
call proc;

3.查看存储过程
show create procedure [数据库.] 存储过程名;      #查看某个储存过程的具体信息	  							  
show create procedure proc\G  
show procedure status [like '%proc%'] \G

① 实例操作：创建存储过程,调用存储过程

mysql> select * from test;
+---------+------+----------+
| name    | age  | addr     |
+---------+------+----------+
| zs      |   10 | jiangsu  |
| zs      |   20 | nanjing  |
| lisi    |   30 | shanghai |
| wangwu  |   40 | shanghai |
| zhaoliu |   40 | huaian   |
+---------+------+----------+
mysql>delimiter $$
mysql> create procedure proc1()
    -> begin
    -> select * from test where name='zs';
    -> end 
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc1;
+------+------+---------+
| name | age  | addr    |
+------+------+---------+
| zs   |   10 | jiangsu |
| zs   |   20 | nanjing |
+------+------+---------+

在这里插入图片描述

②查看存储过程

show create procedure [数据库.] 存储过程名;      #查看某个储存过程的具体信息	  							  
show create procedure proc\G  ;
show procedure status [like '%proc%'] \G ;

在这里插入图片描述

③删除存储过程

drop procedure if exists 存储过程名;   #仅在存在删除，不加if exists ，如果不存在，则会报错

在这里插入图片描述

7.3.2、带参数

.输入参数：in 表示调用者向过程传入值（传入值可以是字面量或变量）
输出参数：out 表示过程向调用者传出值（可以返回多个值）（传出值只能是变量）
输入/输出参数：inout ，即表示调用者向过程传入值，又表示过程向调用者传入值（只能是变量）

delimiter $$  
create procedure proc(in 参数 字段类型)   #形参
begin
select * from 表格 where 条件语句;          #过程体语句
end $$
delimiter ; 

call proc('数据');                         #实参

mysql> delimiter $$
mysql> create procedure proc2(in new_age int)
    -> begin
    -> select * from test where age=new_age;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc2(40);
+---------+------+----------+
| name    | age  | addr     |
+---------+------+----------+
| wangwu  |   40 | shanghai |
| zhaoliu |   40 | huaian   |
+---------+------+----------+

在这里插入图片描述

7.4、存储控制语句

①条件语句if…end if

#条件语句
delimiter $$ 
create procedure proc3(in a int)     #参数a
begin
declare b int;                  #参数b与上面参数a类型一致
set b=a*2;                      #参数b与参数a之间的条件语句
if b>=25 then
update 表格 set 修改字段数据  where 条件语句;
else
update 表格 set 修改字段数据  where 条件语句;
end if ;
end $$
delimiter ;

call proc3 (参数a具体值);

操作：

create table test2 (id int(10));
insert into test2 values (10);
#条件语句
delimiter $$ 
create procedure proc3(in pro int)    
begin
declare var int;                  
set var=pro*2;                  
if var>=10 then
update test2 set id=id+1 ;
else
update test2 set id=id-1;
end if ;
end $$
delimiter ;

call proc3 (6);

在这里插入图片描述

②循环语句while…end while

delimiter $$
create procedure proc4()
begin
declare a int;
set a=0;
while a<6 do
update 表格 set 修改字段数据  where 条件语句;
set a=a+1;
end while;
end $$
delimiter ;

call proc4;

操作：

delimiter $$
create procedure proc4()
begin
declare var int(10);
set var=0;
while var<6 do
insert into test2 value(var);
set var=var+1;
end while;
end $$
delimiter ;

call proc4;

在这里插入图片描述