数据分析师__SQL基础篇

SQL语言

 不管是初级还是中级数据分析师，sql都是工作的必备技能，而且还要懂如何手撕sql代码。避免边学边忘，将此技能作为我总结相关知识的第一步。

注释：[ ]中括号部分表示可有可无；

1. 数据定义语言（Data Definition Language, DDL）

 DDL包含创建、修改 、删除数据库中的各种对象（表、视图、索引等）,常见命令如create, alter, drop：

1.1 create

功能：用于创建数据库/表

语法：

create database/table name;

create table [if not exists] name (
	'字段名' 列的类型 [属性] [索引] [注释],
	...
	'字段名' 列的类型 [属性] [索引] [注释]
	)[表类型][字符集设置][注释];

例子：

##创建数据库school
create database [if not exists] school;
## 创建数据库表student
create table [if not exists] student(
	id int(4) not null auto_increment comment '学号',
	name varchar(30) not null default comment '学生姓名',
	gender varchar(2) not null default comment '性别',
	class int(4) not null default comment '班级',
	primary key(id)
)engine=innodb default charset=utf8;

数据类型

数据类型	描述
integer(size) int(size) smallint(size) tinyint(size)	仅容纳整数。在括号内规定数字的最大位数
decimal(size,d) numeric(size,d)	容纳带有小数的数字。 “size” 规定数字的最大位数。“d” 规定小数点右侧的最大位数。
char(size)	容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。 在括号中规定字符串的长度。
varchar(size)	容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。 在括号中规定字符串的最大长度。
date(yyyymmdd)	日期

约束（Constraints）

约束类型	描述
not null	指示某列不能存储 NULL 值
unique	保证某列的每行必须有唯一的值
primary key	唯一标识，NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录
foreign key	保证一个表中的数据匹配另一个表中的值的参照完整性 foreign key(stu_id) references student(id)
check	保证列中的值符合指定的条件 check(age>0)
default	规定没有给列赋值时的默认值。
auto_increment	自增函数，初始值1，步长1，也可设置初始值。

1.2 alter

功能：向已有表添加、修改或删除列
语法：

alter table table_name add/modify/change/rename/drop name;

--rename用于修改表名
alter table table_name rename [to] new table_name ;
alter table student rename to student2021;

--add 增加一列多多列
alter table table_name add column_name datatype
alter table student add(
	age int(2) not null default comment '年龄',
	address varchar(30) not null default comment '地址'
);

--modify 修改字段类型和约束
alter table table_name modify column_name datatype...;
alter table student modify (name varchar(10) default 'unknown')

--change 修改字段名
alter table table_name change old_name new_name datatype...; --新字段需要完整定义
alter table student change name stu_name char(4);

--drop 删除字段，删除表的结构及其所依赖的约束、索引等，执行后无法回滚
alter table table_name drop column column_name;
alter table student drop column age;	

1.3 truncate

功能：清空表中的所有行，但表结构及其约束、索引等保持不变，和drop一样执行后无法回滚
语法：

truncate [table] table_name;
truncate student;

2. 数据操作语言（Data Manipulation Language）

用来操作记录，常见命令如insert,update,delete，分别用来添加，修改和删除记录

2.1 insert

功能：向表格添加记录
语法：

insert into table_name values (values1, values2...)
insert into talbe_name (columns1, columns2, ...) values (values1, values2...)
insert into student (id, name) VALUES ('10', '张三');

2.2 update

功能：修改表格中的数据
语法

update table_name set columns_name = new_values where columns_name2 = old_values
update student set name = '小李', age = 12 where id = 10

2.3 delete

功能：删除表格中某行
语法

delete from table_name where columns_name = some_values
delete from student where id = 10

注释：
 delete可以删除表中部分数据，保留表结构，而truncate能删除表中全部数据，并保留表结构，但速度快

3. 数据查询语言（Data Query Language,DQL）

查询数据库表中的记录、基本结构

3.1 语法

select * from table where condition;
select id, name from student where id = 10;

3.2 常见函数说明

函数	描述
from	选中数据表格
where	符合某条件的表格记录
group by	根据一个或多个列对结果集进行分组，一般结合聚合函数使用
having	筛选分组后的各组数据，一般结合聚合函数使用，因为where无法与聚合函数一起使用
order by	指定列对结果集进行排序，默认升序asc，降序为desc
limit n[,m]	返回前n条记录，或者从第n条记录起，返回m条记录，不包含第n条
sum() avg() count() min() max()	常见聚合函数

3.3 高级函数说明

函数	描述
like	用于在 WHERE 子句中搜索列中的指定模式
in	允许在 WHERE 子句中规定多个值
_或%通配符	_任意一字符，%任意字符
between A and B	选取介于两个值之间的数据范围内的值。 这些值可以是数值、文本或者日期 包括a，不包括b
inner join on	满足条件的两表交集
left join on	满足条件的记录，以左表为准，右表没匹配到的为null
right join on	满足条件的记录，以右表为准，左表没匹配到的为null
full join on	满足条件的所有记录，左右表其中一个表有的都匹配出来
union	上下连接，合并两个或多个 SELECT 语句的结果，可进行列转换（高难度考点）
upper()	把字段的值转换为大写
Lower()	把字段的值转换为小写
mid()	提取中间值 MID(column_name,start[,length]) 包含起始位置
len()	返回字段长度
round()	把数值字段舍入为指定的小数位数。 round(column_name,decimals)
now()	返回当前系统环境
format()	对字段的显示进行格式化 format(column_name,format) format(Now(),‘YYYY-MM-DD’)
case when then end	条件表达式函数，也可用于行转列（高难度考点） case columns_name when condition then [else] end

3.4 开窗函数

函数类型	开窗函数	描述
序号函数	row_number()	有序号排序 row_number() over ( [partition by 分组列] order by 排序列 desc/asc)
	rank()	有序号排名，相同分数并列排 rank() over ([ partition by 分组列 ] order by 排序列 desc/asc)
	dense_rank()	始终返回连续的排名值 dense_rank() over ( [partition by 分组列 ] order by 排序列 desc/asc)
前后函数	lag()	返回columns_name当前行往上offset行的值 lag(columns_name [,offset, default_values ] ) over ([partition by 分组列 ] order by 排序列 desc/asc)
	lead()	返回columns_name当前行往下offset行的值 lead(columns_name [, offset, default_values ] ) over ([partition by 分组列 ] order by 排序列 desc/asc)
头尾函数	first_value()	返回columns_name有序行集第一行的值 first_value(columns_name) over ([partition by 分组列] order by 排序列 desc/asc)
	last_value()	返回columns_name有序行集最后一行的值 last_value(columns_name) <br>over ([ partition by 分组列 ] order by 排序列 desc/asc)
分布函数	percent_rank()	返回某列或某列组合后每行的百分比排序 初始值0，有重复值 percent_rank() over ([partition by 分组列 ] order by 排序列 desc/asc)
	cume_dist()	计算累积分布值，有重复值 表示值小于或等于行的值除以总行数的行数 cume_dist() over ([ partition by 分组列 ] order by 排序列 desc/asc)
其他函数	nth_value()	从结果集的第N行获取值 nth_value() over ([ partition by 分组列 ] order by 排序列 desc/asc)
	ntile()	按顺序分n组 ntile() over ([ partition by 分组列 ] order by 排序列 desc/asc)

3.5 select语句执行顺序：

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

4. 结束语

 此篇为基础总结篇，下一篇文章介绍相关实战演练