详谈SQL函数

数据库中的函数与Java中的函数类似，都是完成一定功能的代码的集合。根据函数操作数据行数可将SQL函数分为单行函数和多行函数：

单行函数：单行函数仅对单条数据中的列进行操作并且返回一个结果；      

 例如：select length('Tom') from dual;#获取字符串字符个数 多行函数：

多行函数：可以操作成组的多条数据，每组返回一个结果，所以多行函数又称之为组函数；      

 例如：select sex,count(id) from student group by sex ;#获取student表中男女人数

 单行函数 

字符串函数

length字符串长度

1.length(column_name|str)：返回字符串存储长度 （一个汉字三个字节的长度）

select name ,length(name)from student

2..char_length(column_name|str)：返回字符串中字符个数

select name ,length(name),char_length(name) from student

concat 拼接

1.concat(column_name1|str1, column_name2|str2,......)：将多个字符串首尾相连后返回；

2.concat_ws(separator,column_name1|str1, column_name2|str2,......)：将多个字符串按照执行separator进行首尾相连；

select concat(id,' , ',name,' , ',age)from student
select concat_ws (' , ',id,name,age)from student

 

 reverse 反转结果

select reverse(name)from student

 trim 删除字符串前后的空格

select trim(name) from student

trim([{both | leading | trailing} [remstr] from] str)：返回去掉str源字符串两端、前缀或后缀字符串

remstr；不指定both、leading、trailing ，则默认为 both，此时不能使用from关键字；不指定remstr，则去掉str两端的空格； 

replace替换

将 student表中的name字符串中的%替换成*,代码如下

select replace (name,'%',"*") from student

substr字符串截取

substr(str,pos[,len])：从源字符串str中的指定位置pos开始取一个字串并返回；        

注意：

pos：从1开始，下标1开始，没有下标0，区别于数组的索引；

len指定子串的长度，如果省略则一直取到字符串的末尾；

len为负值表示从源字符串的尾部开始取起。

函数substr()是函数substring()的同义词

select substr("acfshahqhkfsd",2,2)

strcmp字符串比较

strcmp(expr1,expr2)：两个字符串相同则返回0；第一个小于第二个返回-1，否则返回1；

select name,strcmp(name,"张三")from student

数学函数

mod取余

MySQL数据库中的算术运算符包括+、-、*和/，其中/运算符的结果为浮点类型的数值        例如：select age + 1 age from student;

MySQL数据库中没有取余运算符，如果要对数据进行取余运算只能借助数据库mod(x,y)来实现，其中x为被除数，y为除数      

select mod(1,3) as result from dual;

round四舍五入

round(x[,y])：返回参数x的四舍五入值，该值有y位小数；不指定第二个参数，则默认为0；

select round(1.45,1),round(1.58,0),round(1.58),round(1.2,1);

truncate截断

truncate(x,y)：返回数字x截断后的结果，该值有y位小数；y不能去掉，否则报错

select truncate(1.45,1),truncate (14.243,2),truncate (14.243,1),truncate (14.243,0)

日期函数 

now()：获得当前日期+时间

select now();

date_format(date,format):获取指定格式的日期

select date_format(now(),'%Y-%m-%d')

convert转换

    convert(value,type)：将value转换为type类型，type可以是char（字符型）、date（日期型）、time（时间型）、datetime（日期时间型）、 signed（整型） 和decimal（浮点型）类型中的一个;

三目运算符 

if(expr1,expr2,expr3)： expr1为TRUE，返回expr2，否则返回expr3

ifnull(expr1,expr2)：expr1不是NULL，返回expr1，否则返回expr2。

ifnull 必须连续，中间不能有空格，否则会报错

order by 

 对查询结果进行排序，必须置于SQL语句的最后，

 语法：order by {column_name1, column_name2, column_name3, ......column_namen} [asc|desc]

1.{column_name1, column_name2, column_name3, ......column_namen}：指定需要排列的字段

2.[asc|desc]：指定排列策略，asc以升序排列，desc以降序排列，默认以升序排列。

select * from student order by age 
select * from student order by age asc

           

select * from student order by age desc

 

不同数据类型，升序的含义如下：

数字类型：小值在前面显示；

日期类型：早的日期在前面显示；

字符类型：依据字母顺序显示，a在前，z最后；

3.在order by 子句中，不仅可以使用字段，还可以使用字段别名进行排序

select id,name,age as stu_age,mobile,address from student order by stu_age;

4.多重排序：也称多列排序，order by 不仅可以跟一个字段，还可以跟多个字段，此时先排第一个字段，只有当重复的时候再排下一个字段。即先以一个字段对查询结果进行排序，然后在这个排序的基础上再对另一个字段进行排序，最终显示经多次排序后的查询结果；

select * from student order by age,mobile

distinct：去除相同的行

（“相同的行”指不同行之间的相同列中的数值相同）

select  distinct age from student

该关键字必须紧跟select关键字的后面，即如下写法是错误的： select age,distinct name from student

多行函数 

多行函数又称组函数，这类函数用于对多行数据进行操作，

在使用时需要注意一下几点：

1、组函数忽略空值——可以通过ifnull函数或if(expr1, expr1, expr1)用一个值代替空值；

2、组函数默认考虑重复值——可以通过distinct关键字使组函数不考虑重复值；

常用组函数：

avg(input)：求平均值，例如：select avg(age) from student-------计算学生平均年龄，包括重复的年龄

max(input)：求最大值，例如：select max(age) from student——获取学生表中最大年龄

min(input)：求最小值，例如：select min(age) from student——获取学生表中最小年龄

sum(input)：求和，例如：select sum(age) from student——计算学生表中年龄之和

count(* | input)：求行数，如果使用*或1则不会忽略空值的行，

select count(name) from student;#1，student表中有一条数据中的name为空  
select count(distinct name) from student;#2，student表中有一条数据中的name为空，有两条数据name的值重复
select count(ifnull(name,' ')) from student;#3， 将name为空的值置为1个空格
select count(*) from student;#4，*不会忽略空值的行
select count(1) from student

stddev(input)：求标准差

variance(input)：求方差

注意：count、max和min组函数操作的数据的数据类型可以是char、varchar、int或date，但不能为clob；avg、sum、stddev和varlance仅能用在数字类型的数据上。

group by分组

group by用于将表中数据划分为若干个组，group by后面用于指定分组的依据

操作数据库，代码如下：

create table student(
id char(1) primary key,
name varchar(8),
sex char(2) default '男' ,
age int(3) default 0
)
 
insert into student values ('1','王明','男',18);
insert into student values ('2','孙丽','女',17);
insert into student values ('3','王明','男',27);
insert into student (id,sex,age) values ('4','男',27);

将student表学生按照sex分组，然后统计每组中的人数 

select sex,count(sex) from student group by sex

 

 group by查询列表只能是分组依据列或多行函数

# 此表没有任何存在的价值
select name,sex,count(sex) from student group by sex

这样的表没有什么作用，如果是Oracle数据库直接会报错