MYSQL 高级SQL语句扩展

扩展

CASE#

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 2000
  ELSE Sales 
  END 
"New Sales",Date 
FROM Store_Info;

#"New Sales" 是用于 CASE 那个字段的字段名。

 

 

空值(null)和无值(' ')的区别#

1．无值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。
2．IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是无值的。
3．无值的判断使用=''或者<>''来处理。<> 代表不等于。 
4．在通过 count()指定字段统计有多少行数时，如果遇到 NULL 值会自动忽略掉，遇到无值会加入到记录中进行计算。

SELECT length(NULL), length(''), length('1');
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 <> '';
SELECT COUNT(*) FROM City;
SELECT COUNT(name) FROM City;

#空值

 

 

 #过滤空值，且过滤了null值

 

 

正则表达式#

 

匹配模式        描述                                    实例
^                 匹配文本的开始字符                         ‘^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 'os';
SELECT * FROM Store_Info WHERE Store_Name REGEXP '^[A-G]';
SELECT * FROM Store_Info WHERE Store_Name REGEXP 'Ho|Bo';

 

 

 

存储过程#

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

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

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

创建存储过程#

DELIMITER $$                            #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE PROCEDURE Proc()                 #创建存储过程，过程名为Proc，不带参数
-> BEGIN                                #过程体以关键字 BEGIN 开始
-> select * from Store_Info;            #过程体语句
-> END $$                               #过程体以关键字 END 结束
DELIMITER ;                             #将语句的结束符号恢复为分号

##调用存储过程##
CALL Proc;

 

查看存储过程#

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

SHOW CREATE PROCEDURE Proc;

SHOW PROCEDURE STATUS [LIKE '%Proc%'] \G

 #

删除存储过程#

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

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

 

存储过程的参数#

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

DELIMITER $$                
CREATE PROCEDURE Proc1(IN inname CHAR(16))        
-> BEGIN                    
-> SELECT * FROM Store_Info WHERE Store_Name = inname;
-> END $$                    
DELIMITER ;                    

CALL Proc1('Boston');


delimiter $$
mysql> create procedure proc3(in myname char(10), out outname int)
    -> begin
    -> select sales into outname from t1 where name = myname;
    -> end $$
delimiter ;
call proc3('yzh', @out_sales);
select @out_sales;


delimiter $$
mysql> create procedure proc4(inout insales int)
    -> begin
    -> select count(sales) into insales from t1 where sales < insales;
    -> end $$
delimiter ;
set @inout_sales=1000;
call proc4(@inout_sales);
select @inout_sales;







存储过程参数(只能用于字段的)
in #传入参数
out #传出参数
inout #传入传出参数

create procedure 存储过程名(in 传入参数名 参数数据类型,out 传出参数名 参数数据类型)
begin
 select 字段 into 传出参数名 from 表 where 字段 = 传入参数;
 end$$
 delimiter ;
 #将语句的结束符号恢复为分号
 
 call 存储过程名称(参数值|变量名; @变量名) #传入参数指定的值可以是纯良的值（字面值）,也可以是变量名; 传出参数指定的值只能是变量名
 
 create procedure 存储过程名(inout 参数名 参数数据类型)
 begin
 select 字段 into 参数名 from 表 where 字段 = 参数名;
 end$$
 delimiter ;
 #将语句的结束符号恢复为分号
 
 set @变量名 = 传入的值; #设置传入的值
 call 存储过程名(@变量名); #传入传出的参数指定的值只能是变量名
 select @变量名; #查看传出的值

 

存储过程的控制语句#

create table t (id int(10));
insert into t values(10);

（1）条件语句if-then-else ···· end if 
DELIMITER $$  
CREATE PROCEDURE proc2(IN pro int)  
-> begin 
-> declare var int;  
-> set var=pro*2;   
-> if var>=10 then 
-> update t set id=id+1;  
-> else 
-> update t set id=id-1;  
-> end if;  
-> end $$
 
DELIMITER ;

CALL Proc2(6);

（2）循环语句while ···· end while
DELIMITER $$  
CREATE PROCEDURE proc3()
-> begin 
-> declare var int(10);  
-> set var=0;  
-> while var<6 do  
-> insert into t values(var);  
-> set var=var+1;  
-> end while;  
-> end $$  

DELIMITER ;

CALL Proc3;



存储过程控制语句
条件语句 ：
if 条件表达式 then
  SQL语句序列1;
  slse
 SQL语句序列2;
 end if;

循环语句
while 条件表达式
do
SQL语句序列;
set 条件迭代;
end while;

 

脚本实现一次性创建10w个文件#

#！/bin/bash

a=0

while [ $a -le 100000 ]
do
    mysql -uroot -p123 -e "insert into 库.表 values (...)"
    let a++
done

DELIMITER $$

create procedure proc1()
begin
declare i int;
set i = 1;
create table test03 (id int primary key auto_increment,name varchar(20));
while i <= 100000
do insert into test03 (name) values (concat('studen', i));
set i = i + 1;
end while;
end$$

delimiter ;

call proc1

#大量批量插入数据，要么用if循环语句，或者用while