MySQL数据库——内置函数
建表并插入数据
create table student(
id char(36) primary key,
name varchar(8) not null,
age int(3) default 0,
mobile char(11),
address varchar(150)
)
插入数据
insert into student
values ('9b4435ec-372c-456a-b287-e3c5aa23dff4','张三',24,'12345678901','北京海淀');
insert into student
values ('a273ea66-0a42-48d2-a17b-388a2feea244','李%四',10,'98765432130',null);
insert into student
values ('eb0a220a-60ae-47b6-9e6d-a901da9fe355','张李三',11,'18338945560','安徽六安');
insert into student
values ('6ab71673-9502-44ba-8db0-7f625f17a67d','王_五',28,'98765432130','北京朝阳区');
insert into student
values ('0055d61c-eb51-4696-b2da-506e81c3f566','王_五%%',11,'13856901237','吉林省长春市宽平区');
内置函数
在MySQL中已经定义过的函数，具有特定的功能和作用。
将SQL函数分为单行函数和多行函数：
单行函数：单行函数仅对单条数据中的列进行操作并且返回一个结果
多行函数：多行函数可以操作成组的多条数据，每组返回一个结果，所以多行函数又称之为组函数
单行函数
字符串函数
length(column_name|str)
返回字符串存储长度
select name, length(name) from student;
注意：一个中文字符占三个长度
char_length(column_name|str)
返回字符串中字符个数
select name,char_length(name) from student;
这里需要注意和length函数的区别
concat(column_name1|str1, column_name2|str2,…)
将多个字符串首尾相连后返回
select concat(id,':',name) "个人信息" from student;
concat_ws(separator,column_name1|str1, column_name2|str2,…)
将多个字符串按照执行separator进行首尾相连；
select concat_ws(':',id,name,age,address) from student;
trim(str)
返回去掉str源字符串两端、前缀或后缀字符串
select trim(name) from student;
因为这里插入的数据没有全面面是空格的数据，所以效果不太明显。
substr(str,pos[,len])
从源字符串str中的指定位置pos开始取一个字串并返回
select substr('abcdefg',2) from dual;
select substr('abcdefg',2,4) from dual;
replace(str, from_str, to_str)
将源字符串str中所有子串form_str(大小写敏感替代成字符串to_str并返回替换后的字符串
select replace(name,'%','@') from student;
这里可看出所有的%符号都被替换成了@符号
reverse(str)
返回字符串str反转结果
select reverse(name) from student;
strcmp(expr1,expr2)
两个字符串相同则返回0；第一个小于第二个返回-1，否则返回1
select name,strcmp(name,'王五') from student;
select name,strcmp(name,'张三') from student;
注意：当插入的数据前面相同，后面有空格的时候也是返回0的
比如这里的数据
数值函数
mod(x,y)
取x与y的余数
这里的张李三后面有空格
select name,strcmp(name,'张李三') from student;
可看到张李三对应的返回值为0
round(x[,y])
返回参数x的四舍五入值，该值有y位小数；不指定第二个参数，则默认为0
select round(1.234);
select round(1.534);
select round(1.234,1);
select round(1.255,2)；
truncate(x,y)
返回数字x截断后的结果，该值有y位小数
select truncate(1.9999,2) from dual;
select truncate(1.9999,0) from dual;
日期函数
now()
获得当前日期 时间
select now();
date_format(date,format)
获取指定格式的日期
select date_format(now(),'%Y-%m-%d %H:%i:%s');
datediff(date1,date2)
返回(date1-date2)天
select datediff(now(),20190501);
timediff(time1,time2)
返回time1-time2
select timediff(now(),20190501000000);
注意：函数的两个参数类型必须相同
转换函数
convert(value,type)
将value转换为type类型，type可以是char(字符型)、date(日期型)、time(时间型)、datetime(日期时间型)、 signed(整型) 和decimal(浮点型)类型中的一个
select convert('111',signed);
其他函数
if(expr1,expr2,expr3)
expr1为TRUE，返回expr2，否则返回expr3
select if(address is not null,mobile,"未知") from student;
ifnull(expr1,expr2)
expr1不是NULL，返回expr1，否则返回expr2
select ifnull(address,"未知") from student;
多行函数
多行函数又称组函数，这类函数用于对多行数据进行操作，在使用时需要注意一下几点：
组函数忽略空值——可以通过ifnull函数或if(expr1, expr1, expr1)用一个值代替空值；
组函数默认考虑重复值——可以通过distinct关键字使组函数不考虑重复值；
常用 组函数(多行函数)
avg(input)
求平均值，例如：select avg(age) from student;#计算学生平均年龄，包括重复的年龄
select avg(age) from student;
max(input)
求最大值，例如：select max(age) from student——获取学生表中最大年龄
select max(age) from student;
min(input)
求最小值，例如：select min(age) from student——获取学生表中最小年龄
select min(age) from student;
sum(input)
求和，例如：select sum(age) from student——计算学生表中年龄之和
select sum(age) from student;
count(*|input)
求行数，如果使用*则不会忽略空值的行，
select count(address) from student;
使用*就不会忽略null
select count(*) from student;
