记几个sql函数的用法
一、IF 条件语句用法
用法:
IF( expr1 , T_expr2 , F_expr3 )
解释:
expr1 的值为 TRUE, 则返回值为 expr2
expr1 的值为 FALSE,则返回值为 expr3
例子:
例子一:计算各学科成绩合格率
SC是成绩表,cno是科目字段
SELECT cno,SUM(IF(score >=60,1,0))/COUNT(score) FROM SC GROUP BY cno
例子二:计算各班优秀+良好,及格,不及格的人数
select
classid,
sum(if(score>=80,1,0)),
sum(if(score>=60 and score<80,1,0)),
sum(if(score<60,1,0))
from
stu
group by classid;
二、FORMAT 函数
用法:
FORMAT(column_name,format)
例子:
计算各学科的平均分
(1)如果不用FORMAT函数
SELECT cno,AVG(score) avgscore FROM beicaiSC GROUP BY cno
(2)使用FORMAT函数
SELECT cno,FORMAT(AVG(score),2) avgscore FROM beicaiSC GROUP BY cno
WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。
三、DATE_FORMAT()函数
DATE_FORMAT()函数用于以不同的格式显示日期/时间数据
用法:
DATE_FORMAT(date,format)
解释:
date参数:合法的日期
format参数:规定日期/时间的输出格式
例子:
要年月日
date_format(get_time,‘yyyy-MM-dd’)
只要年和月
date_format(get_time,‘yyyy-MM’)
格式 | 描述 |
---|---|
%a | 缩写星期名 |
%b | 缩写月名 |
%c | 月,数值 |
%D | 带有英文前缀的月中的天 |
%d | 月的天,数值(00-31) |
%e | 月的天,数值(0-31) |
%f | 微秒 |
%H | 小时 (00-23) |
%h | 小时 (01-12) |
%I | 小时 (01-12) |
%i | 分钟,数值(00-59) |
%j | 年的天 (001-366) |
%k | 小时 (0-23) |
%l | 小时 (1-12) |
%M | 月名 |
%m | 月,数值(00-12) |
%p | AM 或 PM |
%r | 时间,12-小时(hh:mm:ss AM 或 PM) |
%S | 秒(00-59) |
%s | 秒(00-59) |
%T | 时间, 24-小时 (hh:mm:ss) |
%U | 周 (00-53) 星期日是一周的第一天 |
%u | 周 (00-53) 星期一是一周的第一天 |
%V | 周 (01-53) 星期日是一周的第一天,与 %X 使用 |
%v | 周 (01-53) 星期一是一周的第一天,与 %x 使用 |
%W | 星期名 |
%w | 周的天 (0=星期日, 6=星期六) |
%X | 年,其中的星期日是周的第一天,4 位,与 %V 使用 |
%x | 年,其中的星期一是周的第一天,4 位,与 %v 使用 |
%Y | 年,4 位 |
%y | 年,2 位 |
四、NVL函数
NVL函数是一个空值转换函数
用法:
NVL(表达式1,表达式2)
解释:
如果表达式1为空值,NVL返回值为表达式2的值,否则返回表达式1的值。
该函数的目的是把一个空值(null)转换成一个实际的值。
其表达式的值可以是数字型、字符型和日期型。
但是表达式1和表达式2的数据类型必须为同一个类型。
五、LIMIT函数
用法:
limit offset,num
解释:
offset:第一个返回记录行的偏移量,设置为n,表示不包括n,从n+1开始检索,相当于是跳过n条
num:返回记录行的最大数目,设置为m,表示检索m行
例子:
limit 1,1 ---- 检索记录行 2
limit 5,10 ---- 检索记录行 6-15
特殊值:
offset设置为0,表示从第一行开始
limit 1 等同于 limit 0,1 ---- 检索第一行
limit 5 等同于 limit 0,5 ---- 检索前5行
num 设置为-1,则说明是从偏移量开始到记录的结尾
limit 95,-1 ---- 检索记录行 96-结尾last
例子:来自牛客— 用limit进行分页查询
分页查询 employees表,每 5 行一页,返回第 2 页的数据
-- limit X,Y
-- X:从第几条数据开始返回(起始值0)(表示不包括X,从X+1开始检索) Y:返回几条数据
select * from employees limit 5,5
六、CONCAT 函数
用法:
concat(字段1,‘字符串’, ’ ',字段2)
解释:
concat用于拼接字符串
-------- 可以拼接字段,字符串
注意:如果有任何一个参数为null,则返回值为null
例子:
select concat(last_name," ",first_name) as Name from employees
七、CONCAT_WS()函数
用法:
concat_ws(separator分隔符, str1, str2, …)
解释:
separator:分隔符,在str1,str2中间加上分隔符
注意:分隔符不能为null,如果为null,则返回结果为null
例子:
select concat_ws(" ",last_name,first_name) as Name from employees
八、group_concat函数
用法:
group_concat(要合并的字段,连接字符)
PS:一般和group by 一起使用。
解释:
把要 合并的字段的值 通过设置的 连接字符 连接起来
– 连接字符默认 逗号,
– 如果是, 就不用设置了
例子:
题源自牛客:
按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
select dept_no,group_concat(emp_no) as employees
from dept_emp
group by dept_no
其他的暂待添加