数据库常用函数的使用案例

1.replace函数：案例将表中密码字段的o变为0，将i变为1

 create table game(
 id int,
 password varchar(20)
 );
 insert into game
 values(1,'ABPojojaio'),
			 (2,'ujd9ija1oiojoi'),
			 (3,'ijdafgyuafi');
			 select * from game;
 #将o替换为零0，i换为1
 update game set password=replace(password,'o','0' );
 update game set password=replace(password,'i','1');	 
 
 #用一条语句：
 update game set password=replace(replace(password,'o','O'),'i','1')

2.将数据库表NumTable中的num列中的数据先按减号前半部分排序，再按减号后面部分排序（instr获取某个字符串下标，substring截取字符串,cast … as 类型将字符串转化为指定类型内容）

 #11-101,,11-11,12-1,11-110,12-104,101-1
 create table numtable
 (
 no varchar(20)
 )
 insert into numtable
 values('11-101'),
			('11-11'),
			('12-1'),
			('11-101'),
			('12-104'),
			('101-1');
#获取减号位置
 select INSTR(no,'-')	from numtable
#获取-前半部分
select substring(no,1,INSTR(no,'-')-1) from numtable; 
#根据减号前半部分内容排序
 select cast(SUBSTRING(no,1,INSTR(no,'-')-1) as signed) as sort from numtable order by sort;
 #同理对减号后半部分排序
 select cast(substring(no,instr(no,'-')+1) as signed) as sort from numtable order by sort;
 #合并：多列排序
 select * from numtable order by cast(SUBSTRING(no,1,INSTR(no,'-')-1) as signed),cast(substring(no,instr(no,'-')+1) as signed)

结果：

3.sum 、avg、max、min、count函数

如sum(money)
age(grade)
max(grade)
min(grade)
count(1)、count(*）、count(列名)的区别

这几个函数一般与group by语句连用（可以按照多列分组，group by后面可以有多个分组条件）having（分组后筛选）条件筛选是在group by之后

4.limit函数与分页查询

如：

select * from results limit 0,8

左开右闭这句话筛选的是第1-8条数据