Mysql数据库—高阶语句三（排列、中位数、累加、百分比、正则、存储过程）

目录

• 一、对表格数据的处理
  • 1.1 算排名
  • 1.2 算中位数
  • 1.3 算累计总计
  • 1.4 算各行份额占总额的百分比
  • 1.5 算各行份额占当前行累加总份额的百分比
• 二、空值（NULL）与无值（''）
  • 2.1 区别
  • 2.2 实例
• 三、正则表达式
  • 3.1 常用正则
  • 3.2 语法
• 四、存储过程
  • 4.1 简介
  • 4.2 优点
  • 4.3 存储过程的格式
    • 4.3.1 不带参数
    • 4.3.2 带参数
    • 4.3.3 删除存储过程
  • 4.4 存储过程的控制语句
    • 4.4.1 条件语句if...end if
    • 4.4.2 循环语句while...end while

一、对表格数据的处理

1.1 算排名

#表格自我连接，然后将结果依次列出，算出每一行之前（包括哪一行本身）有多少行数
select A1.Name, A1.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);
#统计排名字段的值比本身的值小的以及排名字段和其他字段都相同的数量，比如aaa为6+1=7

1.2 算中位数

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中算出商的方式

1.3 算累计总计

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 count(A2.Sales);
#只要添加一个sum函数即可，就能到每行都能累加

1.4 算各行份额占总额的百分比

select A1.Name, A1.Sales, ROUND(A1.Sales/(select sum(Sales) from Total_Sales)*100,2) || '%'  Per_Sales l,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函数，函数中当前行的份额除以总份额

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

select A1.Name, A1.Sales, ROUND(A1.Sales/SUM(A2.Sales)*100,2) || '%'  Per_Sales l,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）与无值（''）

2.1 区别

1.空值长度为0，不占空间
2.NULL值的长度为null，占用空间
3.is null 无法判断空值
4.空值使用“=”或者“<>”来处理
5.count()计算时，NULL会被忽略，空值会加入计算

2.2 实例

三、正则表达式

3.1 常用正则

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

3.2 语法

select 字段 from 表格 where 字段 regexp '正则表达式';

四、存储过程

4.1 简介

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

4.2 优点

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

4.3 存储过程的格式

4.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           		       

4.3.2 带参数

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

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

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

4.3.3 删除存储过程

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

4.4 存储过程的控制语句

4.4.1 条件语句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具体值);

4.4.2 循环语句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;