系统函数

常用的时间函数

# 使用日期格式化函数
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()) #提取当前时间的日期

常用的字符串函数

出于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的字符

其他函数

根据返回的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值

MySQL系统函数及SQL开发中易犯错的点