《数据分析师的自我修炼》第二篇学习文档
一、查询语句相关
最核心的技能
1、select
查询,从一个或多个表中检索信息:select...from...
2、where
过滤数据:在select语句中,数据根据where子句中指定的搜索条件进行过滤;
where子句在From之后给出;
操作符:=,>,<,>=,<=,!=,between,is null(null 不等同于0、空字符串或仅包含空格);
操作符and和or,要注意计算次序,通常要用()明确;
操作符in,where子句中用来指定条件范围,功能与or相当,但执行更快;
操作符not,where子句中用来否定后跟条件的关键字;
通配符like,用来匹配值的一部分的特殊字符,只能用于匹配文本,%能够匹配0-n个字符
3、order by
排序,取一个或多个列的名字,据此对输出进行排序,只能写在最后一行;
默认升序排序,如需降序使用desc,desc只能应用到直接位于其前面的列名,如果要对多个列降序排序,需在每个字段前加desc
4、函数
聚集函数:sum()、count()、avg()、max()、min(),用法同Excel;
时间、日期处理函数:now()获取当前日期时间、date_format()日期格式化、date_add()增加日期、datediff()计算两个时间天数差;
文本处理函数:concat()拼接字段,substr()截取一段字符,length()计算字符长度、trim()剔除空格、replace()替换文本、left()、right(),用法同Excel;
逻辑函数:if,用法同excel
行转列:case when
case when 条件1 then 结果1 when 条件2 then 结果2 else 结果3 end
举例如下:
5、group by
数据分组
使用聚合函数时必须要进行分组;
group by 子句必须出现在where子句之后,order by子句之前;
除聚集计算语句外,select语句中每个列都必须在group by 子句中给出;(针对这一点mysql不会报错,但SqlServer等会报错)
having 子句用于group by分组后的过滤;与where的区别:where是在group by之前筛选,未通过筛选的数据不会进入分组,having是在group by之后进行过滤,只留下满足过滤条件的分组
6、join
数据联结,当需要查询的字段来自多个有关联的表时;
一定要提供联结条件,否则会出现笛卡尔积;
内联结:inner join,只出现被连接的表共同拥有的记录;
左联结:left join,返回左表的全部记录,及右表能匹配到的记录;
右联结:right join,同左联结,返回右表的全部记录,及左边能匹配到的记录;
全联结:full join,返回被联结表的全部记录
7、union
组合查询,将多个查询结果作为单个查询结果集返回;
被组合的多个查询必须包含相同的列、表达式或聚集函数;
默认剔除重复数据,如果需要包含重复数据,需使用union all;
order by 只能用于最后一条select的最后一行,排序对整个组合查询有效
二、数据表操作相关
基本技能
1、表操作
create table,创建表
#1-建表
Create Table test_hly_20181018(
`id` bigint(20) NOT NULL AUTO_INCREMENT,#主键自增
`StudentID` varchar(20) NOT NULL,
`Subjects` varchar(20) NOT NULL,
`Score` int(10) ,
PRIMARY KEY (`id`)
)ENGINE=InnoDB CHARSET=utf8 COMMENT='学生成绩表';
alter table,更新表结构
修改表备注,举例:在test_hly_20181018表中,修改表备注为"学生成绩表-练习"
alter table test_hly_20181018 comment '学生成绩表-练习';
修改字段备注,举例:在test_hly_20181018表中,给字段增加备注,并修改Score 的备注
alter table test_hly_20181018 modify column id bigint(20) NOT NULL AUTO_INCREMENT comment '自增ID';
alter table test_hly_20181018 modify column StudentID varchar(20) NOT NULL comment '学生ID';
alter table test_hly_20181018 modify column Subjects varchar(20) NOT NULL comment '科目';
alter table test_hly_20181018 modify column Score int(10) comment '分数';
alter table test_hly_20181018 modify column Score int(10) comment '科目分数';
修改字段长度(改长不改短),举例:在test_hly_20181018表中,修改Subjects 字段长度
alter table test_hly_20181018 modify column Subjects varchar(50) NOT NULL ;
增加一个字段,举例:在test_hly_20181018表中,将增加文本类型字段 sex
alter table test_hly_20181018 add sex varchar(2) comment '性别';
alter table test_hly_20181018 add name varchar(20) comment '姓名' after id;#在指定的列后面增加字段
删除一个字段,举例:在test_hly_20181018表中,删除字段 sex和name
alter table test_hly_20181018 drop column sex;
alter table test_hly_20181018 drop column name;
drop table,删除表
drop table test_hly_20181018;
2、增删改记录
insert,插入行(记录)到数据库表;
插入具体数据,只能插入一行个,如需插入多行需执行多次;
insert into 表名 (字段1、字段2...) values (数据1、数据2...);
#插入数据
insert into test_hly_20181018 (StudentID,Subjects,Score) values (1,'Chiness',90);
insert into test_hly_20181018 (StudentID,Subjects,Score) values (2,'Chiness',90);
insert into test_hly_20181018 (StudentID,Subjects,Score) values (3,'Chiness',89);
insert into test_hly_20181018 (StudentID,Subjects,Score) values (4,'Chiness',80);
insert into test_hly_20181018 (StudentID,Subjects,Score) values (5,'Chiness',85);
插入查询结构:可以一次性插入多行,将select的全部查询结果插入;
insert into 表名 (字段1、字段2...) select...from...;
Insert into test_hly_20181018(StudentID,Subjects,Score)
Select 1 ,'Math',86 Union all
Select 2 ,'Math',86 Union all
Select 3 ,'Math',99 Union all
Select 4 ,'Math',80 Union all
Select 5 ,'Math',60 Union all
Select 1 ,'English',70 Union all
Select 2 ,'English',88 Union all
Select 3 ,'English',96 Union all
Select 4 ,'English',81 Union all
Select 5 ,'English',79 ;
update,更新、修改表中的记录;
举例:在test_hly_20181018表中,将studentID为5的学生的英语成绩改为100
update test_hly_20181018 set Score=100 where StudentID='5' and Subjects='English';
一定要用where限定需要被修改的记录,否则将更新表中所有记录
delete,从表中删除记录;
用法类似update,一定要用where限定需要被删除的记录,否则将删除表中所有记录
举例:在test_hly_20181018表中,将studentID为5的学生的英语成绩该条记录删除
DELETE from test_hly_20181018 WHERE StudentID ='5' and Subjects='English';
三、视图
增加效率
1、什么是视图
视图是虚拟的表;用于查看存放在原始表的数据的一种途径;
视图的使用功能与表类似,可以用于查询、筛选、分组、排序、联结等;
视图不包含数据,只包含一个动态变化的查询结果;
2、何时使用视图
简化复杂的查询,将重复使用的sql语句包装为一个视图,再次使用时仅联结该视图即可;
过滤掉不需要的数据;
3、如何使用视图
1)像建立表一样建立视图,该视图仅包含学号为4的学生成绩信息;
CREATE VIEW test_view as
SELECT * from test_hly_20181018 WHERE StudentID='4';
2)像使用表一样使用视图
SELECT * from test_view;
四、存储过程
增加效率
(内容待补充)