SQL常用函数（MySQL）

概述

这里介绍SQL的常用函数，以及自定义函数的创建。SQL函数感觉还行，不是很好用，只能返回一个值，结果集不能返回，这点很不方便。但是函数结合参数方便查询某一个值。

---

准备

-- SQL函数基本调用方式select function(column or null) from table_name;--创建操作表，并插入一些操作数据create table student(id tinyint unsigned auto_increment primary key,name varchar(30) not null,age tinyint unsigned,gender enum('男','女') default null,weight_kg tinyint unsigned,height_cm tinyint unsigned,school varchar(30))engine=InnoDB charset=utf8;-- 插入数据insert into student(name,age,gender,weight_kg,height_cm,school)values('浩辰',18,'男',55,170,'丐帮'),('珊雅',16,'女',48,168,'天下会'),('坤振',17,'男',57,172,'圣火教'),('铭嘉',18,'男',52,171,'丐帮'),('茜岚',19,'女',45,174,'明教'),('盛梓',21,'男',59,175,'幽界'),('泽然',17,'男',54,169,'圣火教'),('婧玥',16,'女',55,166,'明教'),('璐琛',19,'女',48,172,'武当'),('寅家',17,'男',55,168,'少林'),('娜涵',19,'女',43,171,'幽界'),('晓晨',20,'男',54,170,'武当');

字符操作函数

-- concat(column,...) 拼接字符串select concat(name,'来自',school) from student;-- concat_ws('?',columns,...) '?'中内容作为连接符select concat_ws('来自',name,school) from student;-- lower('CONTENT') 将CONTENT变成小写select lower('CONTENT');-- upper('content') 将content变成大写select lower('content');-- left('MySQL',num) 截取MySQl左num个字符select left('MySQL',2);-- right('MySQL',3) 截取MySQl右num个字符select right('MySQL',3);-- length('content') 得到content的长度 select length('content');-- [trim|ltrim|rtrim('   content  ') 去除content[左右两边|左|右]的空格(字符中间空格无效)select trim('   content  ');select ltrim('   content   ');select rtrim('   content  ');-- 附加select trim(leading '?' from '??content???');select trim(trailing '?' from '??content???')select trim(both '?' from '??My?SQL???');-- replace('??AB???CDE??','?','!') 将'??AB???CDE??'中的'?'换成'!'select replace('??AB???CDE??','?','!');select replace('??AB??CDE??','??','!');-- substring('MySQL',1,2) 截取字符select substring('MySQL',1,2);select substring('MySQL',3);select substring('MySQL',-1);-- format(num1,num2)格式化(对于数字,四舍五入)select format(502.6,2);select format('10561.75',1);

时间操作

-- 获取当前时间select now();select date(now());-- 仅获取年份和日期select curdate();-- 获取当前时分秒select curtime();-- 分开获取年月日select EXTRACT(year from now()) as only_year;select EXTRACT(month from now()) as only_month;select EXTRACT(day from now()) as only_day;-- 向指定时间添加时间间隔select date_add(now(),interval 45 day) as someyear;select date_add(now(),interval 45 year) as someyear;select date_add(now(),interval 45 month) as someyear;-- 向指定时间减去时间间隔select date_sub(now(),interval 5 day) as someday;select date_sub(now(),interval 5 year) as someday;select date_sub(now(),interval 5 month) as someday;--获取两个指定时间的间隔(天数)select datediff('2016-11-30',now()) as times;-- 时间格式化select DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');select DATE_FORMAT(NOW(),'%m-%d-%Y');select DATE_FORMAT(NOW(),'%d %b %y');select DATE_FORMAT(NOW(),'%d %b %Y %T:%f');select date_format(now(),'%Y年%m月%d日 %H点%i分%s秒');

聚合函数

-- avg(column)　求平均值select avg(age) as avg_age from student;-- count(column) 求单列行数select count(name) from student;select count(*) from student;-- max(column) 求单列最大值select max(age) from studentselect * from student where age=(select max(age) from student);-- max(column) 求单列最最小值select min(weight_kg) from student;-- sum(column) 求和select sum(age) from student;

数学函数

-- 好久没复习了 三角函数、幂运算、角度值很多的,罗列如下select abs(-1);select acos(-1);select acos(0);select acos(1);select asin(1);select asin(0);select asin(-1);select atan(0);select atan(1);select ceil(-1.2);select conv(105,16,2);select conv('5E',10,2);select cos(pi());select cos(0.5*pi());select radians(180);select pi();select mod(5,3);select log(2,8);select ln(10);select rand();select pow(2,3);

综合运用

-- 列出年龄在平均值之上的学生并指出来自哪个门派select name,school from student where age>(select avg(age) from student);

创建自定义函数

--自定义函数的创建-- MySQL WorkBench中操作-- 函数名 参数 返回值 （不能返回一个结果集） 以下end后面‘;’ 自己添加-- 不带参数create function `new_function` ()returns integerbeginreturn (select avg(weight_kg) from student);end;--调用select new_function();-- 带参数create function `new_function` (s_id tinyint)returns integerbeginRETURN (select name from student where id = s_id);end;--调用select new_function(10);

附加

--多表更新create table school(school_id tinyint unsigned auto_increment primary key)select school from student group by school;--二次更新update student as a inner join school as b on a.school=b.school set a.school=b.school_id;--三次更新alter table student change school school_id tinyint unsigned;-- 查看表结构show columns from student;-- OK