技术圈高级认证，MySQL高级SQL语句，别藏了，就是你应该知晓的好文 下

---- CASE ---- 是 SQL 用来做为 IF-THEN-ELSE 之类逻辑的关键字
语法:
SELECT CASE ("栏位名")
  WHEN "条件1” THEN "结果1"
  WHEN "条件2" THEN "结果2"
  ...
  [ELSE "结果N"]
  END
FROM "表名";
#"条件"可以是一个数值或是公式。ELSE子句则并不是必须的。
SELECT store_Name, CASE Store_Name
  WHEN 'Los Angeles' THEN Sales * 2
  WHEN 'Boston' THEN Sales * 1.5
  ELSE Sales
  END
"New Sales",Date
FROM Store_Info;
#"New Sales"是用于CASE 那个栏位的栏位名。
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);

----算排名----表格自我连结(Self Join)，然后将结果依序列出，算出每一行之前〈包含那一行本身)有多少行数
SELECT A1.Name, A1.sales,COUNT(A2.sales) Rank FROM Total_sales A1,Total_sales A2
WHERE A1.sales < A2.sales 0R (A1.sales = A2.sales AND A1.Name = A2.Name)
GROUP BY A1.Name, A1.sales ORDER BY A1.sales DESC;
例如：
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 order by A1.sales desc;
解释：
当A1的sales字段值小于A2的sales字段值、或者两表sales字段值相等并且Name字段值相等时，
从A1和A2表中查询A1的Name字段值、A1的sales字段值、和A2的sales字段的非空值rank是别名，并为A1的Name字段分组，A1的sales字段降序排序

---- 算中位数 ----
SELECT Sales Middle FROM (SELECT A1.Name,A1.Sales,COUNT(A2.Sales) Rank FROM Total_Sales A1,Total_Sales A2
WHERE A1.Sales < A2.Sales 0R (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);
例如：
select * 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 order by A1.sales desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from Total_Sales);
select sales mid 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 order by A1.sales desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from Total_Sales);
#每个派生表必须有自己的别名，所以别名A3必须要有
#DIV 是在MySQL中算出商的方式

---- 算累积总计 ---- 表格自我连结(Self Join)，然后将结果依序列出,算出每一行之前(包含那一行本身)的总合
SELECT A1.Name, A1.Sales, SUM(A2.Sales) Sum_Total 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;
例如：
select A1.*,sum(A2.sales) sum_soales 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 order by A1.sales desc;

例如：
select A1.*,A1.sales/(select sum(sales) from Total_Sales) z_sum 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;
#select sum(sales) from Total_Sales 是为了算出字段值总合，然后每一行一一除以总合，算出每行的总合百分比。

例如：
select A1.Name,A1.sales,sum(A2.sales),sum(A2.sales)/(select sum(sales) from Total_Sales) Z 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 order by A1.sales desc;
select A1.Name,A1.sales,sum(A2.sales),TRUNCATE(sum(A2.sales)/(select sum(sales) from Total_Sales),2) ||'%' Z 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 order by A1.sales desc;

例如：
create table SITE(site varchar(20));
insert into SITE values('nanjing');
insert into SITE values('beijing');
insert into SITE values('');
insert into SITE values('taijin');
insert into SITE values();
insert into SITE values('');
select * from SITE; 

select length(site) from SITE;
select * from SITE where site is NULL;
select * from SITE where site is not NULL;
select * from SITE where site ='';
select * from SITE where site <> '';

匹配模式      描述                  实例
^         匹配文本的开始字符             ‘^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 Total_Sales where Name regexp '^[n]';
select * from Total_Sales where Name regexp '[n]';
select * from Total_Sales where Name regexp 'Ho|Bo';

语法：
DELIMITER !!            #将语句的结束符号从分号；临时修改，以防出问题，可以自定义
CREATE PROCEDURE XXX()    #创建存储过程，过程名自定义,（）可带参数
  BEGIN             #过程体以关键字BEGIN开始
  select * from xxx;    #过程体语句
  END!!           #过程体以关键字END结尾
DELIMITER ;             #将语句的结束符号恢复为分号
call XXX;           #调用存储过程
====查看存储过程====
show create procedure [数据库.]储存过程名;    #查看某个储存过程的具体信息
show create procedure XXX;
show procedure status [like '%XXX%'] \G

例如：
DELIMITER !!
CREATE PROCEDURE KIND1()
BEGIN
  select * from Total_Sales;
END!!
DELIMITER ;
CALL KIND1;
show create procedure KIND1\G
show procedure status like '%KIND1%'\G

例如：
DELIMITER !!
CREATE PROCEDURE KIND2(IN people char(20))
BEGIN
  select * from Total_Sales where Name=people;                                                    
END!!         
DELIMITER ;
CALL KIND2('lisi');

例如：
DELIMITER !!
CREATE PROCEDURE KIND7(IN num int(10))
BEGIN
declare var int;
set var=num*2;
if var>=10 then
  update Total_Sales set sales=sales+1;
else 
  update Total_Sales set sales=sales-1;
end if;
END!!
DELIMITER ;
CALL KIND7(5);
CALL KIND7(4);

例如：
create table akg(id int);
DELIMITER !!
CREATE PROCEDURE KIND6()
BEGIN
declare var int;
set var=0;
while var<5 do
  insert into akg values(var);
  set var=var+1;
end while;
END!!
DELIMITER ;
CALL KIND6;
select * from akg;