最近在实习经常写SQL语句,在力扣上也写了一些题目,今天整理一下本人写SQL的一些心得。文章内容只包含书写SQL语句的部分知识点,方便同学们查漏补缺。
1、筛选条件
筛选条件也就是where后跟着的内容
1、null
首先判断一个字段是否为空要用 is null 而不是 = null
现在有这样一张学生表,可见第四条数据的score字段为空
2、正则表达式
SQL语句也是可以用正则表达式的,REGEXP
这里写个最简单的例子,以zh开头的name:
SELECT * FROM `student` WHERE name REGEXP '^zh'
2、函数
1、case
case用于条件判断,它在行转列情况下非常好用。
假如现在有这样一张成绩表,包含名字,月份,科目,成绩:
想要得到每位同学的每科历次最高分,这时候用case就很容易实现,
SELECT NAME
,
max( CASE course WHEN course = '语文' THEN score END ) AS 语文,
max( CASE course WHEN course = '数学' THEN score END ) AS 数学,
max( CASE course WHEN course = '英语' THEN score END ) AS 英语
FROM
scores
GROUP BY
NAME
结果如下:
2、字符串操作函数
CONCAT() 函数
CONCAT 可以将多个字符串拼接在一起
LEFT(str, length) 函数
从左开始截取字符串,length 是截取的长度
UPPER(str) 与 LOWER(str)
UPPER(str) 将字符串中所有字符转为大写,LOWER(str) 将字符串中所有字符转为小写 SUBSTRING(str, begin, end)
截取字符串,end 不写默认为空,SUBSTRING(name, 2) 从第二个截取到末尾,注意并不是下标,就是第二个。
综合例子:
编写一个 SQL 查询来修复名字,使得只有第一个字符是大写的,其余都是小写的。
SELECT
CONCAT(UPPER(left(name,1)),LOWER(SUBSTRING(name,2))) AS name
from user
GROUP_CONCAT
这个函数用于将相同的行组合起来,可以指定分隔符,排序顺序
SELECT NAME
,
GROUP_CONCAT( DISTINCT score ORDER BY score SEPARATOR ';' ) sco
FROM
scores
GROUP BY
NAME
结果如下:
这里order by要在separator前面,不然会报错
3、DATEDIFF
DATEDIFF是比较日期的函数,如果用+1 / -1 会出现2000-01-00这种不存在的日期
datediff(日期1, 日期2): 得到的结果是日期1与日期2相差的天数。 如果日期1比日期2大,结果为正;如果日期1比日期2小,结果为负。
例子:
编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的数据 :
SELECT
*
FROM
weather w1,
weather w2
WHERE
w1.temperature > w2.temperature
AND datediff( w1.recordDate, w2.recordDate )= 1;
像获取日期信息的函数还有很多,如YEAR(),MONTH()等等,实在忘了用字符串操作函数也一样可以达到效果,在下面目录其他里有对获取时间在指定范围的介绍。
4、avg
获取平均数
为什么我会提这个呢,因为我在算平均数的时候,用 sum/count ,然后感觉很奇怪哈哈哈
5、max
获取最大值
如果说让你获取一个最大值,然后不存在返回null,你会怎么写?order by 然后 limit 1 还是 max ?
NA和Null还是不同的,NA表示数据集中的该数据遗失、不存在。 NULL表示未知的状态。
6、limit
limit分为1个参数和两个参数,大家平时基本都是用的1个参数,limit n 表示获取前n条记录
limit n, m 表示获取第 n + 1 到 n + m 条记录(分页)
3、其他
1、删除相同元素
DELETE a from Student a, Student b
where a.name = b.name
and a.id > b.id
记得 a.id > b.id 不可省略,不然会删除全部元素
2、获取时间在指定范围
between and,like,DATE FORMAT,LEFT
例子:如时间指定在2020年2月的方法
dateTest between '2020-02-01' and '2020-02-29
dateTest like '2020-02%
DATE FORMAT(dateTest,"%Y-%m") = "2020-024
LEFT(dateTest ,7) 或 substring(dateTest,1,7)
这几天遇到差不多就这些了,其他的后续慢慢补充