MySQL數(shù)據庫——內置函數(shù)

• 建表并插入數(shù)據
• 內置函數(shù)
• 單行函數(shù)
• 字符串函數(shù)
• length(column_name|str)
• char_length(column_name|str)
• concat(column_name1|str1, column_name2|str2,......)
• concat_ws(separator,column_name1|str1, column_name2|str2,......)
• trim(str)
• substr(str,pos[,len])
• replace(str, from_str, to_str)
• reverse(str)
• strcmp(expr1,expr2)
• 數(shù)值函數(shù)
• mod(x,y)
• round(x[,y])
• truncate(x,y)
• 日期函數(shù)
• now()
• date_format(date,format)
• datediff(date1,date2)
• timediff(time1,time2)
• 轉換函數(shù)
• convert(value,type)
• 其他函數(shù)
• if(expr1,expr2,expr3)
• ifnull(expr1,expr2)
• 多行函數(shù)
• 常用 組函數(shù)(多行函數(shù))
• avg(input)
• max(input)
• min(input)
• sum(input)
• count(*|input)

建表并插入數(shù)據

create table student(
	id char(36) primary key,
	name varchar(8) not null,
	age int(3) default 0,
	mobile char(11),
	address varchar(150)
)

插入數(shù)據

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','北京朝陽區(qū)');
insert into student 
values ('0055d61c-eb51-4696-b2da-506e81c3f566','王_五%%',11,'13856901237','吉林省長春市寬平區(qū)');

內置函數(shù)

在MySQL中已經定義過的函數(shù)，具有特定的功能和作用,。
將SQL函數(shù)分為單行函數(shù)和多行函數(shù)：

• 單行函數(shù)：單行函數(shù)僅對單條數(shù)據中的列進行操作并且返回一個結果
• 多行函數(shù)：多行函數(shù)可以操作成組的多條數(shù)據,，每組返回一個結果,，所以多行函數(shù)又稱之為組函數(shù)

單行函數(shù)

字符串函數(shù)

length(column_name|str)

• 返回字符串存儲長度

select name, length(name) from student;

注意：一個中文字符占三個長度

char_length(column_name|str)

• 返回字符串中字符個數(shù)

select name,char_length(name) from student;

這里需要注意和length函數(shù)的區(qū)別

concat(column_name1|str1, column_name2|str2,…)

• 將多個字符串首尾相連后返回

select concat(id,':',name) "個人信息" from student;

concat_ws(separator,column_name1|str1, column_name2|str2,…)

• 將多個字符串按照執(zhí)行separator進行首尾相連；

select concat_ws(':',id,name,age,address) from student;

trim(str)

• 返回去掉str源字符串兩端,、前綴或后綴字符串

select trim(name) from student;

因為這里插入的數(shù)據沒有全面面是空格的數(shù)據,，所以效果不太明顯。

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;

注意：當插入的數(shù)據前面相同，后面有空格的時候也是返回0的
比如這里的數(shù)據

數(shù)值函數(shù)

mod(x,y)

• 取x與y的余數(shù)
  
  這里的張李三后面有空格

select name,strcmp(name,'張李三') from student;

可看到張李三對應的返回值為0

round(x[,y])

• 返回參數(shù)x的四舍五入值,，該值有y位小數(shù),；不指定第二個參數(shù)，則默認為0

select round(1.234);

select round(1.534);

select round(1.234,1);

select round(1.255,2),；

truncate(x,y)

• 返回數(shù)字x截斷后的結果,，該值有y位小數(shù)

select truncate(1.9999,2) from dual;

select truncate(1.9999,0) from dual;

日期函數(shù)

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);

• 注意：函數(shù)的兩個參數(shù)類型必須相同

轉換函數(shù)

convert(value,type)

• 將value轉換為type類型，type可以是char（字符型）,、date（日期型）,、time（時間型）、datetime（日期時間型）,、 signed（整型） 和decimal（浮點型）類型中的一個

select convert('111',signed);

其他函數(shù)

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;

多行函數(shù)

多行函數(shù)又稱組函數(shù)，這類函數(shù)用于對多行數(shù)據進行操作,，在使用時需要注意一下幾點：

1. 組函數(shù)忽略空值——可以通過ifnull函數(shù)或if(expr1, expr1, expr1)用一個值代替空值,；
2. 組函數(shù)默認考慮重復值——可以通過distinct關鍵字使組函數(shù)不考慮重復值；

常用 組函數(shù)(多行函數(shù))

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)

• 求行數(shù)，如果使用*則不會忽略空值的行,，

select count(address) from student;

使用*就不會忽略null

select count(*) from student;