目录
2.6.1 条件控制语句 if-then-else … end if
一、数据库函数
1.1 数学函数
数据库内存储的记录，经常要进行一系列的算术操作，所以 MySQL 支持很多数学函数。
| 函数 | 含义 |
|---|---|
| abs(x) | 返回x 的 绝对值 |
| rand() | 返回0到1的随机数 |
| mod(x,y) | 返回x除以y以后的余数 |
| power(x,y) | 返回x的y次方 |
| round(x) | 返回离x最近的整数 |
| round(x,y） | 保留x的y位小数四舍五入后的值 |
| sqrt(x) | 返回x的平方根 |
| truncate(x,y) | 返回数字 x 截断为 y 位小数的值 |
| ceil(x) | 返回大于或等于 x 的最小整数 |
| floor(x) | 返回小于或等于 x 的最大整数 |
| greatest(x1,x2.....) | 返回返回集合中最大的值 |
| least(x1,x2..........) | 返回返回集合中最小的值 |
示例：
返回-2的绝对值
select abs(-2);
0-1的随机数（0<=x<1） 返回 0 到 1 的随机数
#示例3：随机生成排序
select * from info order by rand();
#示例4：返回7除以2以后的余数
select mod(7,2);
#示例5：返回2的3次方
select power(2,3);
#示例6：返回离2.6最近的数
select round(2.6);
#返回离2.4最近的数
#示例7：保留2.335321的3位小数四舍五入后的值
select round(2.335321,2);
#示例8：返回数字 2.335321 截断为2位小数的值
select truncate(2.335321,2);
#示例9：返回大于或等于2.335321 的最小整数
select ceil(2.335321);
#示例10：返回集合中最大的值
select greatest(1,4,3,9,20);
1.2 聚合函数
MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的，这些函数被称作聚合函数。
| 函数 | 含义 |
|---|---|
| avg() | 返回指定列的平均值 |
| count() | 返回指定列中非 NULL 值的个数 |
| min() | 返回指定列的最小值 |
| max() | 返回指定列的最大值 |
| sum(x) | 返回指定列的所有值之和 |
示例：
#示例1：求表中分数的平均值
select avg(score) from info;
#示例2：求表中分数的总和
select sum(score) from info;
#示例3：求表中有多少条记录
select count(*) from info;
count（*）包含空字段，会忽略空记录
1.3 字符串函数
| 函数 | 描述 |
| trim() | 返回去除指定格式的值 |
| concat(x,y) | 将提供的参数 x 和 y 拼接成一个字符串 |
| substr(x,y) | 获取从字符串 x 中的第 y 个位置开始的字符串，跟substring（）函数作用相同 |
| substr(x,y,z) | 获取从字符串 x 中的第 y 个位置开始长度为z 的字符串 |
| length(x) | 返回字符串 x 的长度 |
| replace(x,y,z) | 将字符串 z 替代字符串 x 中的字符串 y |
| upper(x) | 将字符串 x 的所有字母变成大写字母 |
| lower(x) | 将字符串 x 的所有字母变成小写字母 |
| left(x,y) | 返回字符串 x 的前 y 个字符 |
| right(x,y) | 返回字符串 x 的后 y 个字符 |
| repeat(x,y) | 将字符串 x 重复 y 次 |
| space(x) | 返回 x 个空格 |
| strcmp(x,y) | 比较 x 和 y，返回的值可以为-1,0,1 |
| reverse(x) | 将字符串 x 反转 |
1）trim
语法：
select trim (位置 要移除的字符串 from 字符串)
其中位置的值可以是
leading（开始）
trailing(结尾)
both(起头及结尾)
#区分大小写
要移除的字符串:从字符串的起头、结尾或起头及结尾移除的字符串，缺省时为空格。#示例1：从名字开头的开始，移除南京中的南显示
select trim(leading '南' from '南京');
#示例2：去除空格
select trim(both from ' liuxing');
2）length
#语法：
select length（字段） from 表名；
#示例：计算出字段中记录的字符长度
select name,length(name) from info;
3）replace
#语法：
select replace(字段，'原字符''替换字符') from 表名；
示例：
select replace(name,'ua','hh') from info03;
4）concat
#语法：
select concat(字段1，字段2）from 表名
#示例1：将name，age字段拼接成一个字符串
select concat(name,age) from info03;
#示例2：中间加制表符
select concat(name,'\t',age) from info03 where id=3;
5）substr
#语法：
select substr(字段，开始截取字符，截取的长度) where 字段='截取的字符串'
#示例1：截取第3个字符往后
select substr(name,3) from info03 where name='zhangsan';
#示例2：截取第3个字符往后的三个字符
select substr(name,3，3) from info03 where name='zhangsan';
strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1
比较17和18，小于返回-1，等于返回0，大于返回1，只会返回这3个值，它是比较第一位不同的数字
select strcmp(17,18);
1.4 日期时间函数
字符串函数 描述
curdate() 返回当前时间的年月日
curtime() 返回当前时间的时分秒
now() 返回当前时间的日期和时间
month(x) 返回日期 x 中的月份值
week(x) 返回日期 x 是年度第几个星期
hour(x) 返回 x 中的小时值
minute(x) 返回 x 中的分钟值
second(x) 返回 x 中的秒钟值
dayofweek(x) 返回 x 是星期几，1 星期日，2 星期一
dayofmonth(x) 计算日期 x 是本月的第几天
dayofyear(x) 计算日期 x 是本年的第几天示例：返回年月日
示例:返回当前时间
示例：返回当前完整时间
示例：当前日期是本月的第几天
二、存储过程
2.1 概述
-
存储过程是组为了完成特定功能的SQL语句集合
-
存储过程这个功能是从5.0版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性
-
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。
存储过程在数据库中创建并保存，它不仅仅是 SQL 语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。存储过程的应用范围很广，例如封装特定的功能、 在不同的应用程序或平台上执行相同的函数等等。
2.2 存储过程的优点
（1）执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
（2）SQL语句加上控制语句的集合，灵活性高
（3）在服务器端存储，客户端调用时，降低网络负载
（4）可多次重复被调用，可随时修改，不影响客户端调用
（5）可完成所有的数据库操作，也可控制数据库的信息访问权限
2.3 创建、调用和查看存储的过程
2.3.1 创建存储过程
#语法：
CREATE PROCEDURE <存储过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[ IN | OUT | INOUT ] <参数名><类型>
1) 过程名 存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。 需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。
2) 过程参数 存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。 MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。
3) 过程体 存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束 在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。
为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下： delimiter $ $
语法说明如下：
$$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符
成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个??
mysql > DELIMITER ??
若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可
mysql > DELIMITER ;
注意：DELIMITER 和分号“;”之间一定要有一个空格
示例：（不带参数的创建）
##创建存储过程##
DELIMITER $$ #将语句的结束符号从分号;临时改为两个$$(可以自定义)
CREATE PROCEDURE Proc() #创建存储过程，过程名为Proc，不带参数
-> BEGIN #过程体以关键字 BEGIN 开始
-> create table mk (id int (10), name char(10),score int (10));
-> insert into mk values (1, 'wang',13);
-> select * from mk; 过程体语句
-> END $$ #过程体以关键字 END 结束
DELIMITER ; #将语句的结束符号恢复为分号
2.3.2 调用存储过程
#语法
call 过程名
2.3.3 查看存储过程
#查看某个存储过程的具体信息（如果在指定库中，库名可以省略）
SHOW CREATE PROCEDURE [数据库.] 存储过程名;
##示例1：
show create procedure hellodb.proc\G;
##示例2：
show create procedure proc\G;
查看指定存储过程信息
SHOW PROCEDURE STATUS like '%proc%'\G
2.3.4 删除存储过程
存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。
#语法
DROP PROCEDURE IF EXISTS 过程名;
2.4 存储过程的参数
-
IN输入参数: 表示调用者向过程传入值(传入值可以是字面量或变量)
-
OUT输出参数: 表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
-
INOUT输入输出参数: 既表示调用者向过程传入值，又表示过程向调用者传出值(值只能是变量)
示例：
mysql> delimiter @@
mysql> create procedure proc1 (in inname varchar(40)) #行参
-> begin
-> select * from info where name=inname;
-> end @@
mysql> delimiter ;
mysql> call proc1('小明'); #实参
2.5 修改存储过程
ALTER PROCEDURE <过程名>[<特征>... ]
ALTER PROCEDURE GetRole MODIFIES SQL DATA SQL SECURITY INVOKER;
MODIFIES sQLDATA:表明子程序包含写数据的语句
SECURITY:安全等级
invoker:当定义为INVOKER时，只要执行者有执行权限，就可以成功执行。
2.6 存储过程的控制语句
2.6.1 条件控制语句 if-then-else … end if
delimiter $$ #修改默认结束符为$$
create procedure proc12(in pro int) #创建存储过程proc2，参数为pro，类型为int
-> begin #过程体以关键字begin开始
-> declare var int; #定义变量var为int类型
-> set var=pro*3; #设置变量var等于传入参数的3倍
-> if var>=10 then #如果变量var大于10，则执行下面过程体
-> update students set age=age+10; #设置表students中的age加10
-> else #如果变量var不大于10，则执行下面过程体
-> update students set age=age-10; #设置表students中的age减10
-> end if; #结束if语句
-> end $$ #创建存储过程结束
delimiter ; #重新修改默认结束符为原始的;
CALL proc12(4); #调用proc2存储过程，并传入参数4
2.6.2 循环语句 while … end while
#创建表
create table nametest(id int auto_increment primary key,name char(40),age int default 20);
DELIMITER $$ #修改默认结束符为$$
-> create procedure yxp() #创建存储过程yxp
-> begin #过程体以关键字begin开始
-> declare i int; #定义变量i为int类型（长度最大为10）
-> set i = 1; #设置i = 1;
-> while i <= 100 #使用while循环，i要小于100
-> do insert into nametest(name,age) values #满足条件则进行添加数据，内容为变量i
-> set i=i+1; #变量i每次循环后加1
-> end while; #结束while循环
-> end $$ #创建存储过程结束
delimiter ; #重新修改默认结束符为原始的;
CALL proc6; #调用yxp存储过程
三、运算符
MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别 是：算术运算符、比较运算符、逻辑运算符和位运算符
3.1 算术运算符
3.2 比较运算符
1. 等于
① 如果两者都是整数，则按照整数值进行比较。
②如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。（在程序中，一般是不会吧这两者进行相比较的）
③ 如果两者都是字符串，则按照字符串进行比较。
④ 如果两者中至少有一个值是 NULL，则比较的结果是 NULL。
2、不等于
不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。 如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL
3、判断一个值为/不为null ( IS NULL、IS NOT NULL )
4、BETWEEN AND
5、Least and greater
5.1、LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL。
5.2、GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为NULL， 则返回结果就为 NULL。
5.3、若要判断一组数字或字母中哪个最小、哪个最大，可以通过使用 LEAST 和 GREATEST 来实现
6、IN判断值是否在对应的列表中，是则1，否0
3.3 逻辑运算符
3.3.1 逻辑非
不是0 就是1 不是1就是0；
3.3.2 逻辑与
使用相乘的方式，11=1，10=0，0与null=0
3.3.3 逻辑或
1+1=1；1+0=1；0+0=0；2+null=1;0+null=null
3.3.4 逻辑异或（xor）
异为相反，则1+1=0；1+0=1；0+null=0
3.4 位运算符
位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式（1010 1111），然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看。MySQL 支持 6 种位运算符
3.5 总结
1.逻辑运算
①逻辑非（not)：有0返回1，没0返回0，有null返回null
②逻辑与（and)：有0返回0，没0返回1，有null返回null
③逻辑或（or): 前后都是0返回0，否则为1，前后有一个null为1，前后都是null(和0 or null）返回null
④逻辑异或（xor）：前后相同为0，前后不同为1，有null返回null
2.位运算符
①按位与（ & ）：1 1 得 1，否则为0
②按位或（ | ）：有1 得1，否则为0
③按位异或（ ^ )： 不同得1，否则为0
④按位取反（ ~ ）：取反计算，1 1得1，否则为0
616
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
