mysql.00001_MySQL系统函数及SQL开发中易犯错的点

系统函数

常用的时间函数

函数名

说明

CURDATE()/CURTIME()

返回当前日期/返回当前时间

NOW()

返回当前的日期和时间

DATE_FORMAT(date,fmt)

按照fmt的格式，对日期date进行格式化

SEC_TO_TIME(senconds)

把秒数转换为(小时:分:秒)

TIME_TO_SEC(time)

把小时(小时:分:秒)转换为秒数

DATEDIFF(date1,date2)

返回data1和data2两个日期相差的天数

DATE_ADD(date,INTERVAL )

对给定的日期增加或减少指定的时间单元(unit:DAY天/HOUR小时/Minutes分钟/SECOND秒)

EXTRACT(unit FROM DATE)

返回日期date的指定部分

UNIX_TIMESTAMP()

返回unix时间戳

# 使用日期格式化函数

SELECT DATE_FORMAT(NOW(),'%Y%m%d %H-%i-%s')

# 秒数转换小时 小时转换秒数

SELECT SEC_TO_TIME(60),TIME_TO_SEC('1:00:00') 返回结果:00:01:00 3600

任务一：计算每门课程，上线时间距当前时间的天数

select title,DATEDIFF(NOW(),online_time) from imc_course

order by 2 desc

# 对当前时间进行加减操作

SELECT NOW()

,DATE_ADD(NOW(),INTERVAL 1 DAY) #当前时间加1天

,DATE_ADD(NOW(),INTERVAL 1 YEAR) #当前时间加1年

,DATE_ADD(NOW(),INTERVAL -1 YEAR) #前时间减1年

,DATE_ADD(NOW(),INTERVAL '-1:30' HOUR_MINUTE) #当前时减1：30

任务二：提取时间中的年，月，日

SELECT NOW()

,EXTRACT(YEAR FROM NOW()) #提取当前时间的年份

,EXTRACT(MONTH FROM NOW()) #提取当前时间的月份

,EXTRACT(DAY FROM NOW()) #提取当前时间的日期

常用的字符串函数

函数名

说明

CONCAT(str1,str2)

把字符串str1,str2连接成一个字符串

CONCAT_WS(sep,str1,str2)

用指定的分隔符sep连接字符串

char_length(str)

返回字符串中字符个数

LENGTH(str)

返回字符串字节个数

FORMAT(X,D[,local])

将数字n格式化为格式，如“#，###，###”,并舍入到D位小数

LEFT(str,len)/RIGHT(str,len)

从字符串的左/右边起返回len长度的子字符串

SUBSTRING(str,pos,[len])

从字符串str的pos位置起返回长度为len的子串

SUBSTRING(str,delim,count)

返回字符串str按delim分割的前count个子字符串

locate(substr,str)

在字符串str中返回子串substr第一次出现的位置

TRIM([remstr FROM] str)

从字符串str两端删除不需要的字符remstr

出于seo优化的目的，需要合并显示课程分类名称和课程标题

select CONCAT_WS('||',class_name,title)

from imc_course a

join imc_class b on b.class_id = a.class_id

将数字舍入到4位小数

SELECT FORMAT(123456.789,4) # 执行结果 123,456.7890

从www.imooc.com分别从左起/右起截取三位

SELECT LEFT('www.imooc.com',3) # 返回www

,RIGHT('www.imooc.com',3) # 返回com

对于ip地址，取C段和D段地址

SELECT SUBSTRING_INDEX('192.168.0.100','.',-2); #返回0.100

截取课程标题里中横线之前的部分 如MYSQL课程-00001

SELECT SUBSTRING('MYSQL-00001',1,LOCATE('-','MYSQL-00001')-1);

SELECT TRIM( ' imoooc '),# 删除空格

TRIM('x' FROM 'xxxxxxxximoocxxxxxxxx'); # 删除字符串前后x的字符

其他函数

函数名

说明

ROUND(X,D)

对数值X进行四舍五入保留D位小数

RAND()

返回一个在0和1之间的随机数

CASE WHEN[condition] THEN result

用于实现其他语言中的case...when..

MD5(str)

返回str的MD5值

根据返回的0和1返回男女

select user_nick

,case when sex=1 then '男'

when sex=2 then '女'

else '未知'

END AS '性别'

from imc_user

SQL高级特性

公共表表达式CTE(Common Table Expressions)

MySQL8.0之后的版本才可以使用

CTE生成一个命名临时表，并且只在查询期间有效

CTE临时表在一个查询中可以多次引用及自引用

CTE基础语法

# CTE 递归生成序列

with RECURSIVE test as(

select 1 as n

union all

select 1+n from test where n<10

)

select *

from test

# 查询学习人数大于2000的课程标题，学习人数和课程id

with cte as (

select title,study_cnt,class_id

from imc_course

where study_cnt>2000

)

select *

from cte

窗口函数

函数名

说明

在SQL开发中易犯的错误

使用count(*)判断是否存在符合条件的数据 使用 select ... limit 1

在执行一条更新语句后，使用查询方式判断更新语句是否执行成功。 使用ROW_COUNT()函数判断修改行数

试图在on条件中过滤不满足条件的数据 使用在where 条件中进行过滤

举个栗子

# 查询出分类ID为5的课程名称和分类名称

select a.`class_name`,b.`title`

from imc_class a

join imc_course b

on b.`class_id`=a.`class_id` and a.`class_id`=5

# 当使用left join的时候，并没有起到过滤作用，原因是left join会列出表中所有的数据，故过滤无效

select a.`class_name`,b.`title`

from imc_class a

left join imc_course b

on b.`class_id`=a.`class_id` and a.`class_id`=5

# 正确过滤姿势

select a.`class_name`,b.`title`

from imc_class a

left join imc_course b

on b.`class_id`=a.`class_id`

where a.`class_id`=5

在使用In进行子查询的判断时，在列中未指定正确的表名，如Select A1 from A where A1 in(select A1 from B),这时尽管B中并不存在A1列数据库也不会报错，而是列出A表中的所有数据。

# 正确姿势1 指定表名

# 正确姿势2 使用Join关联代替子查询

对于表中定义的具有Not null 和default值的列，在插入数据时直接插入null值