本文主要梳理《对比Excel,轻松学习SQL数据分析》对于自己有用的sql知识。这本书中的所有代码和函数均适用于my sql 8.0版本。
SQL的基本功能 数据定义(DDL)--对数据库,表,视图,索引等对象进行的创建,删除,修改的操作。 数据操作(DML)--对表进行的增,删,查,改操作。 数据控制(DCL)--赋予或取消某个用户对表或列的DML权限。 SQL查询的处理步骤
- 查询分析分析sql语句的词法或语法是否正确。
- 查询检查检查sql语句中的数据库,表名,列名是否存在。
- 查询优化根据具体情况,选择效率最高的执行策略。
- 查询执行执行查询语句。
查询数据库信息
use information_schema;-- schemata表存储了my sql所有与库相关的信息select * from information_schema.`SCHEMATA`;
查询结果:
查询数据表信息
select* from information_schema.`TABLES`
查询结果:
查询列信息
select * from information_schema.`COLUMNS`
查询结果:
获取指定的行
limit x,y--表示获取第x行(不包括第x行)以后的y行数据。
select * from student;
原表:
select * from student limit 3,2;
取student表第3行(不包含第3行)之后的2行数据:
缺失值的处理
sql中缺失值有空格,空值,null三种形式,其中空值是没有值,空格和null是有值。在进行数学运算的时候,例如count,sum计算时,null不计数,而空格和空值是算入计数。
- 过滤掉缺失值
-- 缺失值为空值或空格时select * from student where class!="" or class!=" "-- 缺失值为null时select * from student where class is not null
2.填充缺失值
ifnull(列名,填充值);
coalesce(列名,填充名)
填充null值
select * ,ifnull(sbirthday,'if缺失值') ,coalesce(sbirthday,'coal缺失值')from student where sbirthday is null
运行结果:
数据类型转换
cast(value as type);
convert(value,type)
select * ,cast(sno as char(10)) as cast_type ,convert(sno,char(10)) as convert_typefrom student
sno列的数值型转换为了字符型。
字符串函数
替换--replace(列名c,A,a),表示将列名c中的A替换成a
select *,replace(ssex,'male','男') as male,replace(ssex,'female','女') as femalefrom student
合并--concat(A,a),表示将A列和a列合并
concat_ws(符号c,A,a),表示将A列与a列以符号c连接
select *,concat(sname,ssex),concat_ws('+',sname,ssex) from student
截取--left(列名,截取长度);
right(列名,截取长度);
substring(列名,a,b),表示从a位置开始截取b长度的字符
计数--char_length(),基于字符计数;
length(),基于字节计数(utf-8下,1字符=3字节,gbk下,1字符=2字节)
select char_length("自学sql"),length("自学sql")
uft-8环境下,length()函数中1各中文字符=3个字节,一个英文字符=1个字节,所以length("自学sql")最后运行的结果是9。
去字符空格--ltrim(),去除左边空格;
rtrim(),去除右边空格;
trim(),去除两边空格。
字符串重复--repeat(字符串,重复次数)
select repeat("study sql",3)
控制函数
case when
形式1
case 列名
when 条件1 then 返回值1
when 条件2 then 返回值2
when 条件3 then 返回值3
......
when 条件n then 返回值n
else 其他
end
select * ,case classwhen "class2" then "2班" when "class3" then "3班" when "class4" then "4班" when "class5" then "5班" else "" end as class_1from student
这种case when的形式后面的条件只能是具体的某个值,不能进行比较计算。
形式2
case when 满足条件1 then 返回值1
when 满足条件2 then 返回值2
when 满足条件3 then 返回值3
......
when 满足条件n then 返回值n
else 其他
end
select * ,casewhen `degree`>=60 then "及格" when `degree`<60 then "不及格" else "" end as scorefrom practice.score
日期和时间转换函数
date_format(列名,格式)
extract(unit from datetime)
select * ,date_format(sbirthday,"%d") as date1 ,extract(day from sbirthday) as date2from student
with 建立临时表
格式:
with 临时表名1 as (sql 语句)
建立多个临时表格式:
with 临时表名1 as (sql 语句)
临时表名2 as (sql语句)
......
临时表名n as (sql语句)
计算环比语句
select count(salesno) ,count(if(DATE_FORMAT(convert(dimDateID,datetime),"%y-%m-%d")="17-06-06",salesNo,null)) as last_dayfrom dw.fct_sales
- end -