MySQL高级SQL语句（3）_存储过程

---- 算排名 ----

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

select A1.name,A1.sales,count(A2.sales) Rank from test A1,test 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;

解析：

当A1sales字段比A2sales字段小，或者两表字段名相等时，显示A1name字段和sales字段和A2sales字段别名rank非空的值，并为A1name字段和sales字段分组降序显示A1sales字段

##A1sales为10，A2sales可为10,15,25,30,30,40,60则count(A2.sales)值为7

##A1sales为15，A2sales可为15,25,30,30,40,60则count(A2.sales)值为6

##A1sales为25，A2sales可为25,30,30,40,60则count(A2.sales)值为5

。。。。。。。。。。。。。。

MySQL高级SQL语句（3）_自定义_02

MySQL高级SQL语句（3）_存储过程_03

---- 算中位数 ----

SELECT name,sales,rank FROM

(select A1.name,A1.sales,count(A2.sales) Rank from test A1,test 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 test);

解析：

#每个派生表必须有自己的别名，所以别名A3必须要有

#DIV是在MySQL中算出商的方式符号/也可以

#SELECT name,sales,rank FROM里面这些字段都没加表名默认用的是from后面的表名也就是A3

#先执行内查询的语句也就是排名，因为防止中位数的序号在并列排名中跳过所以就取消了并列排名

#将内查询的结果也就是排名后的表定义给派生表A3

#where语句A3表的rank字段值为内查询取(原表test的列数+1)/2为4即中间列，count(*)会显示所有列的行数，不论是否存在null即总行数，但是奇数行得出的商会是一个X.5，所以需要+1

#最后显示的就是rank=4字段的数据内容

MySQL高级SQL语句（3）_自定义_04

CREATE VIEW V_1 AS select A1.name,A1.sales,count(A2.sales) Rank from test A1,test A2 where A1.sales < A2.sales or (A1.sales=A2.sales and 1.name<=A2.name)

group by A1.name,A1.sales order by A1.sales desc;

 

SELECT Name, Sales Middle FROM V_1 WHERE Rank = (SELECT (CoUNT(*)+1) DIV 2 FROM V_1);

#也可以先将统计的排名值创建一个视图，再在视图进行进一步的操作

MySQL高级SQL语句（3）_字符串_05

---- 算累积总计 ----

表格自我连结（Self Join），然后将结果依序列出，算出每一行之前 （包含那一行本身）的总和

select A1.name,A1.sales,sum(A2.sales) Rank from test A1,test 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;

解析：

当A1sales字段比A2sales字段小，或者两表sales字段相对，name值不等时=时，显示A1name字段和sales字段和A2sales字段别名rank非空的值，并为A1name字段和sales字段分组降序显示A1sales字段

##A1sales为60，A2sales可为60则sum(A2.sales)值为60

##A1sales为40，A2sales可为40,60则sum(A2.sales)值为100

##A1sales为30，A2sales可为30,40,60则sum(A2.sales)值为130

。。。。。。。。。。。。。。

 MySQL高级SQL语句（3）_存储过程_06

---- 算总合百分比 ----

select A1.name,A1.sales,A1.sales/(select sum(sales) from test) Rank from test A1,test 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;

解析：

#select sum(sales) from test这一段子查询是用来算出总合

#总合算出后，我们就能够将每一行除以总合来求出每一行的总合百分比

MySQL高级SQL语句（3）_存储过程_07

####进一步优化显示

select A1.name,A1.sales,round(A1.sales/(select sum(sales) from test)*100,2)||'%' Rank from test A1,test 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;

解析：

#round(A1.sales/(select sum(sales) from test)*100,2)||'%'：里面子查询得出之前的值例如（0.2857）将这个值X100就说28.57

#ronnd（28.57,2）：取小数点后两位也就是28.57，可以根据需求设置

#28.57 || '%'：拼接符，将两个数据拼接在一起，因为%在SQL语句有特殊含义，所以要用单引号括起来

MySQL高级SQL语句（3）_存储过程_08

---- 空值（NULL）和无值（''）的区别 ----

1.无值的长度为 0，不占用空间的; 而NULL值的长度是 NULL，是占用空间的。

2. IS NULL或者IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是无值的。

3.无值的判断使用=''或者<>''来处理。<> 代表不等于，会自动会略NULL。

4.在通过 count()指定字段统计有多少行数时，如果遇到 NUL 值会自动忽略掉，遇到无值会加入到记录中进行计算。

 MySQL高级SQL语句（3）_mysql_09

SELECT length (NULL),length(''),length('1');      

 MySQL高级SQL语句（3）_字段_10

SELECT * FROM test2 WHERE name IS NULL;

SELECT * FROM test2 WHERE name IS NOT NUL; 

 MySQL高级SQL语句（3）_字符串_11

SELECT * FROM test2 WHERE name = '';

SELECT * FROM test2 WHERE name <> '';     

 MySQL高级SQL语句（3）_字段_12

SELECT COUNT(*) FROM test2;

SELECT COUNT(name) FROM test2;

 MySQL高级SQL语句（3）_mysql_13

---- 正则表达式 ----


匹配模式



描述



实例



^



匹配文本的开始字符



`^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次

 


 

语法∶ SELECT "字段" FROM "表名"WHERE "字段" REGEXP '模式';

SELECT* FROM Store Info WHERE Store Name REGEXP '.*';

SELECT * FROM Store Info WHERE Store Name REGEXP '^[A-B]'; 

 MySQL高级SQL语句（3）_存储过程_14

 ---- 存储过程 ----

存储过程是一组为了完成特定功能的SOL语句集合。

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

存储过程的优点∶

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

2、SOL语句加上控制语句的集合，灵活性高

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

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

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

##创建存储过程##

DELIMITER ##                                       #将语句的结束符号从分号;临时改为两个##（可以是自定义）

CREATE PROCEDURE NO1()              #创建存储过程，过程名为NO1，不带参数

-> BEGIN                                                 #过程体以关键字BEGIN开始

-> select * from test2;                            #过程体语句     

-> END $$                                                #过程体以关键字END结束

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

##调用存储过程##

CALL NO1;                     

 MySQL高级SQL语句（3）_mysql_15

##查看存储过程排##

SHOW CREATE PROCEDURE [数据库.]存储过程名;            #查看某个存储过程的具体信息

SHOW CREATE PROCEDURE TEST2\G;   

show procedure status like '%NO1%'\G;                             #like查询存储过程的选项信息，%代表任意字符

MySQL高级SQL语句（3）_字符串_16

 MySQL高级SQL语句（3）_mysql_17

##存储过程的参数##

IN 输入参数∶表示调用者向过程传入值（传入值可以是字面量或变量，但必须是自定义的数据类型）

OUT 输出参数∶表示过程向调用者传出值（可以返回多个值）（传出值只能是变量）（调试使用，传入时需要加上@符号）

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

#传入参数

DELIMITER ##

create procedure NO2(in Aname char(10),in Bname char(5))   #char数据类型，Aname为自定义的参数名，这里同时设置传入两个参数

-> begin 

-> select * from test2 where name=Aname;

-> select * from test2 where name=Bname;

-> end ##

DELIMITER ;

CALL NO2('AAA','BBB ');                                               #执行存储过程同时传入两个参数

 MySQL高级SQL语句（3）_mysql_18

#输出参数

mysql> DELIMITER ##

mysql> DELIMITER ##

mysql> create procedure NO4(in a int,in b int,out c int)

    -> begin

    -> set c=a+b;                               

-> end ##

mysql> delimiter ;

mysql> call NO4(1,2,@CC);                                  #变量传入需要加上@符号

mysql> select @CC

 MySQL高级SQL语句（3）_字符串_19

##删除存储过程##

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。

DROP PROCEDURE IF EXISTS NO4;                             #仅当存在时删除，不添加IF EXISTS 时，如果指定的过程不存在，则产生一个错误

 MySQL高级SQL语句（3）_字符串_20

##存储过程的控制语句##

create table test(name char(10),id int(10));

insert into test values('zhangsan',10);

insert into test values('zhangsan',5);

 MySQL高级SQL语句（3）_mysql_21

（1）条件语句if-then-else...end if 



DELIMITER ##

CREATE PROCEDURE NO1(IN aaa int)
-> begin
-> declare bb int;                                                           #declare定义一个bb的变量类型为int
-> set bb=aaa*2;                                                             #设置变量bb的值为变量aaaX2
-> if bb>=10 then                                                            #如果bb值大于10
-> update test set id=id+1 where name='zhangsan';                            #则将zhangsan的id+1
-> else                                        
-> update test set id=id-1 where name='lisi';                                #否则则将lisi的id-1
->end if;
-> end ##
DELIMITER ;
CALL NO1(6);


 MySQL高级SQL语句（3）_自定义_22

 MySQL高级SQL语句（3）_存储过程_23

（2）循环语句while...end while 



mysql> delimiter ##           
mysql> CREATE PROCEDURE NO2()
    -> begin
    -> declare aaa int(10);                      #定义一个aaa的变量
    -> set aaa=0;                                #变量aaa赋值为0
    -> while aaa<5 do                            #如果变量aaa小于5则执行while循环语句
    -> insert into test values('ceshi',aaa);
    -> set aaa=aaa+1;                            #aaa每次+1，相当于函数的a++，为了防止死循环
    -> end while;                                #结束循环
    -> end ##
mysql> delimiter ;
mysql> call NO2;


MySQL高级SQL语句（3）_自定义_24