1.count(*)
计入空值
count(字段)
不计入空值
2.两个表做连接时要使用最细维度
Select a.*, b.price from discount_info a
left join
(select product_id, cast(avg(price) as decimal(6,2)) as price from
product_id group by product_id) b
on a.product_id=b.product_id;
3.LIKE 与regexp的区别
Like 'ga'
返回完整ga
Regexp 'ga'
返回包含ga
4.正则表达式
^
不在方括号时意味着开头,方括号内"不包含"
5.提取时间
Year(current_date)...Month(current_Date)..week ...day
注意 week是当前日期是本年的第几周 day是当前日期是本年的第几天
6.时间平移与时间间隔
往前推两天date_Add(current_Date,interval-2day)
如果+2就是往后推
本月的第一天 date_add(current_date,interval-day(current_date)+1 day)
本月的最后一天 last_day(current_Date)
两个日期的间隔天数 datediff(date1,date2)
日期与时间戳的相互转化
时间戳转化为日期From_unixtime(时间戳)
日期转化为时间戳 unix_timestamp(current_date)
7.连接表时 用on与where的区别
使用 where统一都是连接后的内容
Left join 使用on 对右表限制,先返回左边表,连接的右表仅保留符合on条件部分
Right join 连接的左表内容仅保留符合on条件部分
Inner join 仅保留右边符合on条件部分,之后与左边求交集
8.sql执行步骤
select之前的不能使用别名,select 之后的可以使用别名
From, join, on, where, group by, having, select, disctinct, order by, limit
9.With rollup 可以分层总结 首先底层 后来上一级别
select
coalesce(year,'TTL') as year,#因为汇总那栏行会变空的,所以取代一下
coalesce(product,'TTL') AS PRODUCT,
SUM(sales) as sales
from
product_sales
group by year,product with rollup
10.Coalesce 、ifnull、Nullif
select year,sales,plan_sales,
ifnull(sales,10) as filled_sales,
coalesce(sales,plan_sales,10) as filled_sales_2
from sales
#coalesce 先选择Sales 如果都有空值再填充10
Nullif(expression1,expression2)
如果expression1=expression2则返回null,否则Expression1
11.数值处理
Truncate(x,d)
x是数值,d是保留小数位数,不是四舍五入,是截断
D=0将小数部分去掉,当d小于是将数值的小数部分去除,并将整数部分按照d指定位数,用0代替
Round
四舍五入
Ceil,ceiling
向上取整
floor
向下取整
Mod(x,y)
与%
都是求余
12.字符串数值转换
Cast(value as type)
Convert(value,type)
浮点数 decimal(p,q) 整数 signed 无符号整数 unsigned
13.生成自增标识的列(即类似行号)
@是用户变量
@@是系统变量
:=与=的区别
= 1.判断符号前后两者的逻辑运算符2.赋值(只有set和update时才和:=用法一样)
:= 赋值
@row:=@row+1
select a.*,(@row:=case when gender='male' then @row+1 else 0 end) rownumber
from student_info a,(select @row:=0) b
14.case表达式各个分支返回的数据类型一致
15.如果日期具体到了时间,再等于小于某个日期,是不包含该日期0.00后的
例如<=2019-11-08不包括 2019-11-08 8.00
16.行转列
每位学生成绩不重复
select student_id,
sum(case when subject='数学' then score end )as '数学',
sum( case when subject='英语' then score end )as '英语',
sum(case when subject='语文' then score end )as '语文'
from student_score
group by student_id
select student_id,
sum(if(subject='数学',score,0 ) )as '数学',
sum( if( subject='英语', score,0 ))as '英语',
sum(if(subject='语文' , score ,0) )as '语文'
from student_score
group by student_id