MySQL數(shù)據(jù)庫——內(nèi)置函數(shù)

• 建表并插入數(shù)據(jù)
• 內(nèi)置函數(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)
• 轉(zhuǎn)換函數(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ù)據(jù)

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ù)據(jù)

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','吉林省長(zhǎng)春市寬平區(qū)');

內(nèi)置函數(shù)

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

• 單行函數(shù)：?jiǎn)涡泻瘮?shù)僅對(duì)單條數(shù)據(jù)中的列進(jìn)行操作并且返回一個(gè)結(jié)果
• 多行函數(shù)：多行函數(shù)可以操作成組的多條數(shù)據(jù)，每組返回一個(gè)結(jié)果，所以多行函數(shù)又稱之為組函數(shù)

單行函數(shù)

字符串函數(shù)

length(column_name|str)

• 返回字符串存儲(chǔ)長(zhǎng)度

select name, length(name) from student;

注意：一個(gè)中文字符占三個(gè)長(zhǎng)度

char_length(column_name|str)

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

select name,char_length(name) from student;

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

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

• 將多個(gè)字符串首尾相連后返回

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

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

• 將多個(gè)字符串按照?qǐng)?zhí)行separator進(jìn)行首尾相連；

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

trim(str)

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

select trim(name) from student;

因?yàn)檫@里插入的數(shù)據(jù)沒有全面面是空格的數(shù)據(jù)，所以效果不太明顯。

substr(str,pos[,len])

• 從源字符串str中的指定位置pos開始取一個(gè)字串并返回

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;

這里可看出所有的%符號(hào)都被替換成了@符號(hào)

reverse(str)

• 返回字符串str反轉(zhuǎn)結(jié)果

select reverse(name) from student;

strcmp(expr1,expr2)

• 兩個(gè)字符串相同則返回0；第一個(gè)小于第二個(gè)返回-1，否則返回1

select name,strcmp(name,'王五') from student;

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

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

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

mod(x,y)

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

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

可看到張李三對(duì)應(yīng)的返回值為0

round(x[,y])

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

select round(1.234);

select round(1.534);

select round(1.234,1);

select round(1.255,2)；

truncate(x,y)

• 返回?cái)?shù)字x截?cái)嗪蟮慕Y(jié)果，該值有y位小數(shù)

select truncate(1.9999,2) from dual;

select truncate(1.9999,0) from dual;

日期函數(shù)

now()

• 獲得當(dāng)前日期 時(shí)間

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ù)的兩個(gè)參數(shù)類型必須相同

轉(zhuǎn)換函數(shù)

convert(value,type)

• 將value轉(zhuǎn)換為type類型，type可以是char（字符型）、date（日期型）、time（時(shí)間型）、datetime（日期時(shí)間型）、 signed（整型） 和decimal（浮點(diǎn)型）類型中的一個(gè)

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ù)用于對(duì)多行數(shù)據(jù)進(jìn)行操作，在使用時(shí)需要注意一下幾點(diǎn)：

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

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

avg(input)

• 求平均值，例如：select avg(age) from student;#計(jì)算學(xué)生平均年齡，包括重復(fù)的年齡

select avg(age) from student;

max(input)

• 求最大值，例如：select max(age) from student——獲取學(xué)生表中最大年齡

select max(age) from student;

min(input)

• 求最小值，例如：select min(age) from student——獲取學(xué)生表中最小年齡

select min(age) from student;

sum(input)

• 求和，例如：select sum(age) from student——計(jì)算學(xué)生表中年齡之和

select sum(age) from student;

count(*|input)

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

select count(address) from student;

使用*就不會(huì)忽略null

select count(*) from student;