1.常用函数：
官方文档：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
数学函数，字符串函数，日期和时间函数，系统信息函数等。
# 数学函数 (这里只列出一些常用的) SELECT ABS(-8); /*绝对值*/ SELECT CEILING(9.4); /*向上取整*/ SELECT FLOOR(9.4); /*向下取整*/ SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/ SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/ #字符串函数 SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/ SELECT CONCAT('我','爱','程序'); /*合并字符串,参数可以有多个*/ SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); /*替换字符串,从某个位置开始替换某个长度*/ SELECT LOWER('KuangShen'); /*小写*/ SELECT UPPER('KuangShen'); /*大写*/ SELECT LEFT('hello,world',5); /*从左边截取*/ SELECT RIGHT('hello,world',5); /*从右边截取*/ SELECT REPLACE('狂神说坚持就能成功','坚持','努力'); /*替换字符串*/ SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/ SELECT REVERSE('狂神说坚持就能成功'); /*反转*/ #日期和时间函数 SELECT CURRENT_DATE(); /*获取当前日期*/ SELECT CURDATE(); /*获取当前日期*/ SELECT NOW(); /*获取当前日期和时间*/ SELECT LOCALTIME(); /*获取当前日期和时间*/ SELECT SYSDATE(); /*获取当前日期和时间*/ /*获取年月日,时分秒*/ SELECT YEAR(NOW()); SELECT MONTH(NOW()); SELECT DAY(NOW()); SELECT HOUR(NOW()); SELECT MINUTE(NOW()); SELECT SECOND(NOW()); #系统信息函数 SELECT VERSION(); /*版本*/ SELECT USER(); /*用户*/ # 查询姓李的同学,改成立 SELECT REPLACE(studentname,'李','历') AS 新名字 FROM student WHERE studentname LIKE '李%';
2.聚合函数：
#统计非空的记录 SELECT COUNT(StudentName) FROM student; SELECT * FROM student; SELECT 1 FROM student; /*推荐*/ SELECT AVG(StudentResult) FROM result; #查询平均值 SELECT MAX(StudentResult) FROM result; #查最大值 SELECT MIN(StudentResult) FROM result; #查最小值 SELECT SUM(StudentResult) AS '总分' FROM result; #计算总分 # 查询不同课程的平均分,最高分,最低分 # 前提:根据不同的课程进行分组 SELECT sb.SubjectName,AVG(r.StudentResult) AS '平均分',MAX(r.StudentResult) AS '最高分',MIN(r.StudentResult) AS '最低分' FROM result r INNER JOIN `subject` sb ON r.`SubjectNo`=sb.SubjectNo GROUP BY sb.SubjectNo ; /* where写在分组的前面 要是要在分组的后面筛选 用 Having */
3.Mysql之自定义函数
函数存储着一系列SQL语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。
但注意的是函数注重返回值，不注重执行过程，所以一些语句无法执行。所以函数并不是单纯的SQL语句集合。
函数与存储过程的区别：函数只会返回一个值，不允许返回一个结果集。函数强调返回值，所以函数不允许返回多个值的情况，即使是查询语句。
/*语法*/ create function 函数名([参数列表]) returns 数据类型 begin sql语句; return 值; end; /*参数列表的格式是： 变量名 数据类型*/ -- 最简单的仅有一条sql的函数 create function myselect2() returns int return 666; select myselect2(); -- 调用函数 -- create function myselect3() returns int begin declare c int; select id from class where cname="python" into c; return c; end; select myselect3(); -- 带传参的函数 create function myselect5(name varchar(15)) returns int begin declare c int; select id from class where cname=name into c; return c; end; select myselect5("python");
4.sql编程
/* SQL编程 */ ------------------ --// 局部变量 ---------- -- 变量声明 declare var_name[,...] type [default value] 这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个default子句。值可以被指定为一个表达式，不需要为一个常数。如果没有default子句，初始值为null。 -- 赋值 使用 set 和 select into 语句为变量赋值。 - 注意：在函数内是可以使用全局变量（用户自定义的变量） --// 全局变量 ---------- -- 定义、赋值 set 语句可以定义并为变量赋值。 set @var = value; 也可以使用select into语句为变量初始化并赋值。这样要求select语句只能返回一行，但是可以是多个字段，就意味着同时为多个变量进行赋值，变量的数量需要与查询的列数一致。 还可以把赋值语句看作一个表达式，通过select执行完成。此时为了避免=被当作关系运算符看待，使用:=代替。（set语句可以使用= 和 :=）。 select @var:=20; select @v1:=id, @v2=name from t1 limit 1; select * from tbl_name where @var:=30; select into 可以将表中查询获得的数据赋给变量。 -| select max(height) into @max_height from tb; -- 自定义变量名 为了避免select语句中，用户自定义的变量与系统标识符（通常是字段名）冲突，用户自定义变量在变量名前使用@作为开始符号。 @var=10; - 变量被定义后，在整个会话周期都有效（登录到退出） --// 控制结构 ---------- -- if语句 if search_condition then statement_list [elseif search_condition then statement_list] ... [else statement_list] end if; -- case语句 CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END -- while循环 [begin_label:] while search_condition do statement_list end while [end_label]; - 如果需要在循环内提前终止 while循环，则需要使用标签；标签需要成对出现。 -- 退出循环 退出整个循环 leave 退出当前循环 iterate 通过退出的标签决定退出哪个循环 --// 内置函数 ---------- -- 数值函数 abs(x) -- 绝对值 abs(-10.9) = 10 format(x, d) -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46 ceil(x) -- 向上取整 ceil(10.1) = 11 floor(x) -- 向下取整 floor (10.1) = 10 round(x) -- 四舍五入去整 mod(m, n) -- m%n m mod n 求余 10%3=1 pi() -- 获得圆周率 pow(m, n) -- m^n sqrt(x) -- 算术平方根 rand() -- 随机数 truncate(x, d) -- 截取d位小数 -- 时间日期函数 now(), current_timestamp(); -- 当前日期时间 current_date(); -- 当前日期 current_time(); -- 当前时间 date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分 time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间 unix_timestamp(); -- 获得unix时间戳 from_unixtime(); -- 从时间戳获得时间 -- 字符串函数 length(string) -- string长度，字节 char_length(string) -- string的字符个数 substring(str, position [,length]) -- 从str的position开始,取length个字符 replace(str ,search_str ,replace_str) -- 在str中用replace_str替换search_str instr(string ,substring) -- 返回substring首次在string中出现的位置 concat(string [,...]) -- 连接字串 charset(str) -- 返回字串字符集 lcase(string) -- 转换成小写 left(string, length) -- 从string2中的左边起取length个字符 load_file(file_name) -- 从文件读取内容 locate(substring, string [,start_position]) -- 同instr,但可指定开始位置 lpad(string, length, pad) -- 重复用pad加在string开头,直到字串长度为length ltrim(string) -- 去除前端空格 repeat(string, count) -- 重复count次 rpad(string, length, pad) --在str后用pad补充,直到长度为length rtrim(string) -- 去除后端空格 strcmp(string1 ,string2) -- 逐字符比较两字串大小 -- 流程函数 case when [condition] then result [when [condition] then result ...] [else result] end 多分支 if(expr1,expr2,expr3) 双分支。 -- 聚合函数 count() sum(); max(); min(); avg(); group_concat() -- 其他常用函数 md5(); default(); --// 存储函数，自定义函数 ---------- -- 新建 CREATE FUNCTION function_name (参数列表) RETURNS 返回值类型 函数体 - 函数名，应该合法的标识符，并且不应该与已有的关键字冲突。 - 一个函数应该属于某个数据库，可以使用db_name.funciton_name的形式执行当前函数所属数据库，否则为当前数据库。 - 参数部分，由"参数名"和"参数类型"组成。多个参数用逗号隔开。 - 函数体由多条可用的mysql语句，流程控制，变量声明等语句构成。 - 多条语句应该使用 begin...end 语句块包含。 - 一定要有 return 返回值语句。 -- 删除 DROP FUNCTION [IF EXISTS] function_name; -- 查看 SHOW FUNCTION STATUS LIKE 'partten' SHOW CREATE FUNCTION function_name; -- 修改 ALTER FUNCTION function_name 函数选项 --// 存储过程，自定义功能 ---------- -- 定义 存储存储过程 是一段代码（过程），存储在数据库中的sql组成。 一个存储过程通常用于完成一段业务逻辑，例如报名，交班费，订单入库等。 而一个函数通常专注与某个功能，视为其他程序服务的，需要在其他语句中调用函数才可以，而存储过程不能被其他调用，是自己执行 通过call执行。 -- 创建 CREATE PROCEDURE sp_name (参数列表) 过程体 参数列表：不同于函数的参数列表，需要指明参数类型 IN，表示输入型 OUT，表示输出型 INOUT，表示混合型 注意，没有返回值。 /* 存储过程 */ ------------------ 存储过程是一段可执行性代码的集合。相比函数，更偏向于业务逻辑。 调用：CALL 过程名 -- 注意 - 没有返回值。 - 只能单独调用，不可夹杂在其他语句中 -- 参数 IN|OUT|INOUT 参数名 数据类型 IN 输入：在调用过程中，将数据输入到过程体内部的参数 OUT 输出：在调用过程中，将过程体处理完的结果返回到客户端 INOUT 输入输出：既可输入，也可输出 -- 语法 CREATE PROCEDURE 过程名 (参数列表) BEGIN 过程体 END