1 变量的使用

变量的分类

分类：变量分为系统变量和会话变量，其中系统变量包括会话变量和全局变量。
说明：系统变量由系统定义，不是用户定义，属于服务器层面。
文档参考

系统变量之全局变量

作用域：针对于所有会话（连接）有效，但不能跨重启。
注意：全局变量必须要添加global关键字，如果不写，则默认为会话级别

//1.0 查看所有全局变量
SHOW GLOBAL VARIABLES;
//2.0 查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
//3.0 查看指定的系统变量的值
SELECT @@global.autocommit;
//4.0 为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

系统变量之会话变量

作用域：针对于当前会话（连接）有效
注意：全局变量必须要添加global关键字，如果不写，则默认为会话级别

//1.0 查看所有会话变量
SHOW SESSION VARIABLES;
//2.0 查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
//3.0 查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.autocommit;
//4.0 为某个会话变量赋值
SET @@session.autocommit =1;
SET SESSION autocommit=1;
SET autocommit=1;

自定义变量用户变量

基本概念
定义：mysql用户变量不用提前申请，在用的时候直接用“@变量名”即可。
作用域：用户变量和连接有关，即一个客户端定义的变量不能被其他客户端看到或使用。当前连接退出时，该客户端连接的所有变量将自动释放。
使用位置：任何地方，包括begin/end里面或外面。
总结：

• 如果只是单纯的建立一个用户变量，则可使用set @param=value;
• 如果是要将数据表的值赋值给某个变量，可使用select column from table into @param;

使用Set关键字
使用set关键字进行声明、初始化和赋值：set @parma=value;
note：使用set关键字创建用户变量必须要初始化。

//1.0 初始化
//方式一初始化
set @nickname="fly";
//使用方式二初始化
set @nickname:="wu";

//2.0 修改变量值
同前面一样

//3.0 读取变量值
select @nickname as nick;

使用Select关键字
使用select into将表字段值赋值给某个变量：
select param1,param2 from table into @parma1,@parma2;
select userid,nickname from user where userid=2 into @userid,@nickname;

自定义变量之局部变量

declare param type default value;

1. 作用域

仅仅在定义它的begin end块中有效，应用在 begin end中的第一句话。

1. 声明

DECLARE 变量名类型;
DECLARE 变量名类型【DEFAULT 值】;

1. 赋值（更新变量的值）

• 简单赋值
  • (推荐)SET 局部变量名=值;
  • SET 局部变量名:=值;
  • (不推荐)SELECT 局部变量名:=值;
• 结合表赋值
  • (推荐)SELECT 字段 INTO 局部变量名FROM 表;

1. 使用(查看变量的值)

SELECT 局部变量名;
案例

drop FUNCTION if exists funlocalvar;
DELIMITER $
CREATE FUNCTION funlocalvar() RETURNS varchar(20)
BEGIN
    #set @nickname=10;note:declare param type必须放在begin/end中且是在第一句。
    declare nick varchar(20) DEFAULT '';
    declare gendervalue tinyint default if(RAND()<0.5,1,0);
    select nickname from USER where userid=2 into nick;
    RETURN nick;
END $
DELIMITER ;

案例：实现添加序号功能
https://www.begtut.com/mysql/mysql-row-number.html

(重要)变量使用总结

1. 带不带@

由变量类型决定：系统变量建议带@@(也可不带参数)，用户变量需要带@，局部变量不带。
作用域由大到小排列，此种方式也很好的区分了如果变量名都相同时其代表的不同的变量类型。

1. 带不带关键字(global/session)

全局变量需要带global；会话变量可以带session关键字，也可以不带。

1. 自定义变量初始化(set/declare)

用户变量用set p=value，局部变量用declare p default value;

1. 变量后续更改赋值(set/select into)

统一用set p=value或select c into p;

1. 查询变量

统一用select关键字

全局变量与会话变量

用户变量和局部变量的区别

2 单行函数

字符串函数

length和char_length：获取参数字节数
length()： 单位是字节，utf8编码下，一个汉字三个字节，一个数字或字母一个字节。gbk编码下，一个汉字两个字节，一个数字或字母一个字节。
使用SHOW VARIABLES LIKE '%char%'命令查看当前数据库的编码格式。
char_length()：单位为字符，不管汉字还是数字或者是字母都算是一个字符。

SELECT LENGTH('张三丰');//在utf-8编码下，长度为9
SELECT LENGTH(123); //在utf-8编码下，长度为3
SELECT CHAR_LENGTH('张三丰'); //长度为3
SELECT LENGTH(123); //长度为3

concat拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

upper、lower
示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

substr、substring
注意：索引从1开始

### 案例1：截取从指定索引处后面所有字符 ###
SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put;

### 案例2：截取从指定索引处指定字符长度的字符 ###
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;

### 案例3：姓名中首字符大写，其他字符小写然后用_拼接，显示出来 ###
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_put FROM employees;

instr
返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;
trim
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
SELECT TRIM('aa' FROM 'aaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaa') AS out_put;
lpad
用指定的字符实现左填充指定长度：SELECT LPAD('殷素素',2,'*') AS out_put;
rpad
用指定的字符实现右填充指定长度：SELECT RPAD('殷素素',12,'ab') AS out_put;
replace 替换
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

数学函数

round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);
ceil向上取整
向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02);
floor 向下取整
返回<=该参数的最大整数
SELECT FLOOR(-9.99);
truncate 截断
SELECT TRUNCATE(1.69999,1);
mod取余
其用法和%是一样的（余数是正是负跟被除数有关，即第一个参数）

SELECT MOD(10, 3);值为1
SELECT MOD(10,-3);值为1
SELECT MOD(-10, 3);值为-1
SELECT MOD(-10, -3);值为-1
SELECT MOD(10, 13);值为10
SELECT MOD(10, -13);值为10
SELECT MOD(-10, 13);值为-10
SELECT MOD(-10, -13);值为-10

日期函数

SELECT CURTIME();

可以获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) 年; SELECT YEAR(‘1998-1-1’) 年; SELECT YEAR(hiredate) 年 FROM employees; SELECT MONTH(NOW()) 月; SELECT MONTHNAME(NOW()) 月;

 |
| str_to_date | 将字符通过指定的格式转换成日期 |

SELECT STR_TO_DATE(‘1998-3-2’,’%Y-%c-%d’) AS out_put;

查询入职日期为1992-4-3的员工信息

SELECT FROM employees WHERE hiredate = ‘1992-4-3’; SELECT FROM employees WHERE hiredate = STR_TO_DATE(‘4-3 1992’,’%c-%d %Y’);

 |
| date_format  | 将日期转换成字符 |

SELECT DATE_FORMAT(NOW(),’%y年%m月%d日’) AS out_put;

查询有奖金的员工名和入职日期(xx月/xx日 xx年)

SELECT last_name,DATE_FORMAT(hiredate,’%m月/%d日 %y年’) 入职日期 FROM employees WHERE commission_pct IS NOT NULL;

 |

<a name="FilSb"></a>
## 流程控制函数
**if函数(三元运算符)**<br />使用if函数实现if else 的效果，等同于三元运算符。

SELECT IF(UserId>10,’成绩未进前10’,’成绩已经入前10’); SELECT last_name,commission_pct,IF(commission_pct IS NULL,’没奖金，呵呵’,’有奖金，嘻嘻’) 备注 FROM employees;

**case(c…wtF…ee)**<br />MySQL 的 case when 的语法有两种

CASE [col_name列] WHEN [value1值] THEN [result1结果] WHEN [value2值] THEN [result2] ELSE [default_value值] END 即 case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1 when 常量2 then 要显示的值2或语句2 … else 要显示的值n或语句n end 例子：SELECT CASE NAME WHEN ‘gz’ THEN ‘广州’ WHEN ‘sz’ THEN ‘深圳’ WHEN ‘hz’ THEN ‘杭州’ ELSE ‘无’ END ‘城市名’ FROM user_info;

```
CASE WHEN [expr表达式] THEN [result1结果] WHEN [expr 表达式] THEN [RESULT2结果]…ELSE [default_value值] END
即：
case 
  when 条件1 then 要显示的值1或语句1
  when 条件2 then 要显示的值2或语句2
  ...
  else 要显示的值n或语句n
end

例子: select case when NAME='gz' then '广州' when NAME='sz' then '深圳' else '杭州' END as AreaSchool  from idnameky;
CASE的多层嵌套：
CASE WHEN linkurl='' then case when ifnull(con.ispaper,0)=0 then 'a' else 'b' end else 'c' end as url

聚合函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数。

• sum、avg一般用于处理数值型
• max、min、count可以处理任何类型

特点：是否忽略null情况（如下在MySQL和SQLServer均一样）

• null数据也将统计的情况：count(*)
• null数据会被过滤的情况：count(列名)、count(distinct 列名) 、Max(列表)、Min(列名)、Sum(列名)、Avg(列名)，其中Avg(列名)为：Sum(列名)/Count(列名)，分子分母均会过滤掉null值。

Count函数效率分析

有如下表数据，总共6条数据，其中两条数据的所有值均为null。 select COUNT(*) from idname; //不会过滤null值，计算得到值为6 select COUNT(1) from idname; //不会过滤null值，计算得到值为6 select COUNT(Id) from idname; //会过滤null值，计算得到值为4 //补充 select Max(Id) from idname; //会过滤null值，计算得到值为4 select Min(Id) from idname; //会过滤null值，计算得到值为1 select Sum(Id) from idname; //会过滤null值，计算得到值为11 select AVG(Id) from idname; //分子分母均会过滤null值，计算值为11/4=2.75

前提：如果要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果一样才可以。

1. MYISAM存储引擎下 ：每张MyISAM的数据表都有一个meta信息存储了row_count值，其一致性则由表级锁来保证，使用COUNT(*)统计表的行数只需要O(1)的复杂度。
   • COUNT()≈COUNT(1)：COUNT() 和COUNT(1) 都是对所有结果进行COUNT统计，COUNT(*)和COUNT(1)本质上并没有区别。
   • COUNT(*)在MyISAM下效率比InnoDB下更高。
2. INNODB存储引擎下：InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，维护一个row_count变量，因此需要采用扫描全表O(n)，进行循环＋计数的方式来完成统计。
   • COUNT(*)≈COUNT(1)>COUNT(非聚集字段)>COUNT(主键字段)
     • COUNT()≈COUNT(1)：COUNT() 和COUNT(1) 都是对所有结果进行COUNT统计，COUNT( *)和COUNT(1)本质上并没有区别。
     • 在InnoDB引擎中，如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引（非聚集索引）。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引(非聚簇索引)。对于COUNT(*)和COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。如果有多个二级索引，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

总结：MyISAM的COUNT()≈MyISAM的COUNT(1)>InnoDB的COUNT()≈InnoDB的COUNT(1)>InnoDB(非聚集索引)>InnoDB(主键索引Id)。

其他常见函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

3 流程控制结构

分支结构

if函数
应用范围：任何地方，包括begin/end里或外面
语法：if(条件,值1，值2)，实现双分支

#查询user表，如果userid=1则输出超级管理员，否则为普通用户
select if(userid=1,'adminuser','commonuser') as result from `user`;

case结构
应用范围：任何地方，包括begin/end里面或外面

#查询user表，如果userid=1则输出level1user，如果为2则输出level2user，否则输出commonuser
case写法1：表达式写法，类似于if..else
select case when userid=1 then 'lever1user' when userid=2 then 'level2user' else 'commonuser' end as resutl from user;
case写法2：值判断写法，类似于switch..case
select case userid when 1 then 'lever1user' when 2 then 'level2user' else 'commonuser' end as result from user;

if结构(开始if中间then结束endif)
note：elseif不要写成了else if
应用范围：只能应用在begin end 中

if(条件) then 语句1;语句2;
elseif(条件) then语句1;语句2;
....
else 语句1;语句2;
end if;

或
if 条件 then 语句1;语句2;
elseif 条件 then 语句1;语句2;
....
else 语句1;语句2;
end if;

//根据传入的职员姓名和职员薪资，存储过程内部判断对应值，进行后续的一系列操作
delimiter $
DROP PROCEDURE if exists handleEmploeeSalary;
CREATE PROCEDURE handleEmploeeSalary(IN sal DOUBLE,In username varchar(10))
BEGIN
    if(sal<=2000) THEN 
            insert into `user`(nickname,salary,rank) values(username,sal,1);
            insert into `employee`(username,salary) values(username,sal);
    elseif(sal>2000 and sal<=5000) THEN
            insert into `user`(nickname,salary,rank) values(username,sal,2);
            insert into `employee`(username,salary) values(username,sal);
    else
            insert into `user`(nickname,salary,rank) values(username,sal,3);
            insert into `employee`(username,salary) values(username,sal);
    END IF;
END $

循环结构

开始while中间do结束endwhile
基本语法和概念
循环语句包括while/loop/repeat

循环控制关键字：
iterate类似于continue。继续，结束本次循环，继续下一次循环。
leave 类似于break。跳出，直接结束当前整个循环。