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时间戳

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

# 使用日期格式化函数
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

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

任务二:提取时间中的年,月,日

SELECT NOW()
			,EXTRACT(YEAR FROM NOW()) #提取当前时间的年份
			,EXTRACT(MONTH FROM NOW()) #提取当前时间的月份
			,EXTRACT(DAY FROM NOW()) #提取当前时间的日期

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

常用的字符串函数

函数名 说明
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

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

# 查询学习人数大于2000的课程标题,学习人数和课程id
with cte as (
	select title,study_cnt,class_id
	from imc_course
	where study_cnt>2000
)
select *
from cte

窗口函数

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

函数名 说明

在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

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

# 当使用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

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

# 正确过滤姿势
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

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

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

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

# 正确姿势1 指定表名
# 正确姿势2 使用Join关联代替子查询

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

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

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

上一篇:[MySQL练习] 查询两个表的差集(两种方法)


下一篇:python项目_mysql数据库的使用