数据分析师__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)
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. 结束语

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

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页