P4 SQL从入门到实战

语法结构

select-from-where-group by-having-order by-limit

运行顺序

from-where-group by-having-order by-limit- select

distinct去重

1.在select核心语句中加distinct关键字，可以对最后显示的数据去重
2.在select distinct后加多个字段时，是对重复的行数据进行去重
3.distinct本质是加在select后，而不是字段前，多个字段时distinct无法对单一字段去重

排序

· 升序asc 降序desc
· order by subject in（‘字段值1’,‘字段值2’)
符合条件的数据——字段值1，字段值2置为1，其他字段值置为0，升序排序，所以括号里的字段会排在后面，字段值1在字段值2前

limit 限制查询结果集显示的行数

· limit n 返回查询结果的前几行
· limit x,n 从x+1开始返回n行
返回第4到7行：limit 3，4

group by

· 使用group by子句时，select只能使用聚合函数【max(),min(),avg(),count()】和group by引用过的字段，否则会报错

having

· 聚合前用where筛选，聚合后用having筛选
· having 子句中只能使用聚合函数和group by作为分组依据的字段

常见函数

四舍五入

round(x,y)
· 对x进行四舍五入，精确到小数点y位
· y为负值，保留小数点左边相应的位数为0，不四舍五入
· round(3.15,1)返回3.2
· round(14.15,-1)返回10

连接字符串

concat(s1,s2,…)
· 返回连接s1，s2等产生的字符串
· 任一参数为null时，返回null
· concat(‘My’,’ ',‘SQL’)返回My SQL
· concat(‘My’,null,‘SQL’)返回null

替换函数

replace(s,s1,s2)
· 使用字符串s2代替s中所有s1
· replace（‘MySQLMySQL’，‘SQL’,‘sql’)返回MysqlMysql

截取字符串一部分

left(s,n)
· 返回s做左边n个字符
· left(‘abcdefg’,3)返回abc

right(s,n)
· 返回s做右边n个字符
· right(‘abcdefg’,3)返回efg

substring(s,n,len)
· 返回字符串s从第n个字符起长度为len的子字符串
· n为负，倒数第n个字符起取长度为len的子字符串
· 没有len值则取从第n个字符起到最后一位
· substring(‘abcdefg’，-2,3)返回fg

数据类型转换

cast(x as type)
· 将一个类型的x值转换成另一个类型
· type参数可以填写char(n)、date、time、datetime、decimal等转换为对应的数据类型

获取年月日

year(date)、month(date)、day(date)
· date可以是年月日 也可以是年月日时分秒
· date = ‘2021-08-03’
year(date) = 2021、month(date) = 8、day(date) =3

对指定起始时间进行加减操作

date_add(date,interval expr type)
date_sub(date,interval expr type)
· date起始时间，可以是年月日，也可以是年月日时分秒
· expr从起始日期加或减的时间间隔
· type指示expr被解释的方式，可以是second/minute/hour/day/week/month/quarter/year

计算两个日期之间间隔的天数

datediff(date1,date2)
· 只有date的日期部分参与计算，时间不参与
· datediff(‘2021-06-08’,‘2021-06-01’)返回7
· datediff(‘2021-06-08 23:59:59’,‘2021-06-01 21:00:00’)返回7
· datediff(‘2021-06-01’,‘2021-06-08’)返回-7

将日期和时间格式化

date_format(date,format)

条件判断函数

if(expr,v1,v2)
· 如果表达式expr是true返回v1，否则返回v2

case when
· case expr when v1 then r1 [when v2 then r2]…[else rn] end
· case when v1 then r1[when v2 then r2]…[else rn] end
· case when 1<0 then ‘T’ else ‘F’ end 返回F

窗口函数

over([partition by 字段名][order by 字段名 asc|desc])
·partition by指定分区依据，order by指定排序依据

【排序窗口函数】
rank()over()
· 跳跃式排序
· 99，99，90，89 ——> 1，1，3，4
dense_rank()over()
· 并列连续型排序
· 99，99，90，89 ——> 1，1，2，3
row_number()over()
· 连续型排序
· 99，99，90，89 ——> 1，2，3，4
【便宜分析函数】
lag(字段名,偏移量[,默认值])over()
向上取偏移量行
lead(字段名,偏移量[,默认值])over()
向下取偏移量行

· 窗口函数只能写在select子句中
· partition by子句可以指定数据的分区，和group by不同，partition by只分区不去重
· 窗口函数没有partition by子句时，整个表为一个区
· 排序窗口函数中order by子句是必选项

表连接

inner join
笛卡尔积去除左右表null
left join
笛卡尔积去除左表null
right join
笛卡尔积去除右表null

子查询

· 子查询本身就是一段完整的查询语句，用英文括号()包裹嵌套在主查询语句中，可多层嵌套
· 最常用：子查询在from和where语句中
· 子查询优于主查询运行

where子查询

· 带比较运算符的子查询，结果为一个单元格
· 带in关键字的子查询，结果为一列多行
· where子句中的子查询适用于查询条件无法一步到位，需要先进行一步查询得到结果，基于这个查询结果再进行条件判断

from子查询

· from子句的子查询本质上是通过查询一段查询语句得出的查询结果作为主查询的数据来源
· from子查询结果可以是多行多列
· from子查询必须使用别名，可以省略as
from (select …) a