查询语句速度入门
查询语句是考察重点,解决难题的关键是把他拆分成多个步骤,逐步思考完成
基础语句
顺序
select *
from table1
join table2 on ...... =........ 表连接
where ....... 筛选条件
group by ....... 按xxx分组
having ....... 常用聚合函数
order by ....... 按xxx排序
limit ...... 限制,前几个
distinct 去掉重复值
常用在变量前面,如:
select distinct user_id
from table;
会去掉重复出现的用户id
join 建立表连接
找两个表的相同列,标准格式:select * from table1 as t1 join table2 as t2 on t1.xxx=t2.xxx
如:表student有"id"、"name"列,表exam有"id","score"列
select *
from student as s
join exam as e on s.id = e.id
在这里as是起别名,用s代表student表,提高易读性简单一点
inner join 只返回两个表实现连接的行,不写inner、left、right这些的话就默认inner,如上面例子就是inner连接
left join 返回左表所有行及右表匹配行
right join 返回右表所有行及左表匹配行
full join 全连接
建议连接前先对要连接的表进行简化
where 条件筛选
筛选行数据,在分组前的操作,筛掉没用的行
不能用聚合函数,可用嵌套
假如我要查询1,2,3班的 可以
select *
from table
where class in (1,2,3);
group by 分组
使用group by时,select后面必须出现group by后面出现的所有列
例如筛选各科最高分的人:
select name,subject,max(score)
from student
group by subject;
having
筛选分组后的数据,常接在group by后
可用聚合函数
order by 排序
默认由低到高排序 desc由高到低 asc由高到低
可以接多个column,表示按第一个column相同按第二个column继续排
limit n 限制前几条
比如查询班级成绩前三的名字
select name
from table
order by score desc
limit 3 ;
聚合函数
count()统计行数
avg()平均值
sum()总和
max()/min()最大、最小值
在计数时,sum常和(0,1)配合,count常和(1,null)配合
union取并集(需要类型一样)
例如输出学校老师和学生的name和id名单
select id,name
from teacher
union
select id,name
from student;
默认去重,如果union all则不去重
case语句
case when 条件1 then A
when 条件2 then B
else C
如果只有两个条件就和if语句一样
if语句
if(条件,A,B),满足条件时执行A,不满足执行B
日期函数
year()取年份
month()取月
day()取日
date_add(date1,n) 给date1加n天,n可以是数字也可以是column(类型符合就行)
date_sub(date2,n) 给date2减n天
current_date() 获取当前时间
todate(n) 把n转换为日期字段
窗口函数
用于创建临时计算字段,进行许多复杂操作
格式: 窗口函数 over (paritition by 分组的列名 order by 排序的列名) 括号内的可省略,多数情况下要写
partition by 可以理解为group by
常用的窗口函数包括
排序函数
假如出现两个同分,这三种是这么排的
rank() 1,1,3
dense_rank() 1,1,2
row_number() 1,2,3
分析函数
lag(m,n) 查询同一分组内,m列比当前值低n名的值
lead(m,n) 查询同一分组内,m列比当前值高n名的值
如果没有就默认为null,可修改默认值,如下:
lag(m,n,k) 如果查询不到则默认为k,lead()同理
计算函数
round(m,n) 对m进行四舍五入,保留n位小数
coalesce(m,n) 返回第一个不为null的值
字符处理函数
截取
substring (字符串,起始位置,截取字符数)
substring_index(字符串,分隔符,截取字符数)
拼接
concat(字符串1,字符串2,字符串3,...) 连接字符串
concat_ws(分隔符,字符数组) 将字符数组连接在一起
大写
upper(字符串)
小写
lower(字符串)
字符数组函数
字符数组的格式:["a","b","c"]
explode()将字符数组拆分,每个字符作为一行
用法:select 表1.旧字段,视图名.新字段 from 表1 lateral view explode(是字符数组的那一列)视图名 as 新字段
split(m,'_')拆分函数,将字符串拆分成字符数组,m是要拆的字符串,_是分隔符
collect_list(列名)将该列所有元素组合起来并转换成字符数组
transform(旧字符数组, x → split(x,'_')[ 0 ] )
新元素 拆分后的第几个元素
array_sort()对字符数组内的元素进行排序
学习sql,当sql之神