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. 结束语
此篇为基础总结篇,下一篇文章介绍相关实战演练