一、数据定义语言DDL
对于数据库、数据表的操作,比如创建表、修改表结构、删除表。
二、数据查询语句DQL
对于数据库的查询。
1 基础语法
查询全表
select * from 表名
查询字段
select 字段 from 表名
查询字段,给字段设置别名as
select 原字段 as 新字段 from 表名
常量和运算
可查询运算字段,在语句中对数据加减乘除、连接文本等
select 字段1*2 as 字段2 from 表名
条件查询where
select 字段 from 表名 where 条件
条件查询,运算符(=、<、>、!=、between)
“!=” 筛选不符合, “BETWEEN” 筛选范围内
条件查询,空值
“IS NULL” 筛选空值, “IS NOT NULL” 筛选非空值
条件查询,模糊查询,通用符(%、_)
百分号(%):表示任意长度的任意字符序列。
下划线(_):表示任意单个字符。
like筛选包含某关键词,not like筛选不包含某关键字
条件查询,逻辑运算(and、or、not)
AND:表示逻辑与,同时满足返回 true。
OR:表示逻辑或,满足任一返回 true。
NOT:表示逻辑非,否定条件( true用后转 false)
去重,distinct
select distinct 字段 from 表名
排序,order by,升序(ASC)或降序(DESC)排,默认是ASC
select distinct 字段 from 表名
order by 字段 [ASC/DESC]
截断和偏移,limit
LIMIT 后跟1个整数,表示截取几条
select 字段 from 表名 limit 5 #截取5条
LIMIT 后跟2个整数,表示从第几条开始、截取几条
select 字段 from 表名 limit 5,5 #下标为5(第6条)截取5条
条件判断,case when
case when 条件1 then '结果1'
when 条件2 then '结果2'
……
else '其他结果' end
分组聚合-单字段分组,group by
select distinct 字段 from 表名
group by 字段
分组聚合-多字段,group by
select distinct 字段 from 表名
group by 字段1 , 字段2
分组聚合-分组后筛选having子句
WHERE用于在分组前过滤,HAVING用于分组后过滤
select distinct 字段 from 表名
group by 字段
having 条件
2 函数
一般结构:function({参数})
时间函数(date、time、datetime)
DATE:获取当前日期
DATETIME:获取当前日期时间
TIME:获取当前时间
select date() as 当前日期 from 表名
字符串处理(upper、lower、length)
upper:转换成大写。lower:转换成小写
length:计算长度
聚合函数(count、sum、max、min)
COUNT:计算行数、非空值数量。count(distinct({参数}))——去重统计
SUM:计算指定列的数值之和。
AVG:计算指定列的数值平均值。
MAX:找出指定列的最大值。
MIN:找出指定列的最小值。
3 查询进阶
关联查询-cross join
cross join:左表每行与右表每行组合,返回两个表的笛卡尔积
可以给表起别名来简化语句,比如{表名} a
select a.字段,b.字段
from 表名1 a
cross join 表名2 b
关联查询-inner join
inner join 返回两表交集部分
select a.字段,b.字段
from 表名1 a
join 表名2 b on a.字段= b.字段
关联查询-outer join
包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 两种类型
分别表示查询左表和右表的所有行(没有匹配上为null),再加上满足条件的交集部分。
select a.字段,b.字段
from 表名1 a
left join 表名2 b on a.字段= b.字段
子查询(嵌套查询)
子查询可以用于获取更复杂的查询结果或者用于过滤数据。
--主查询
select 字段1 from 表名1
where 字段1 IN(
--子查询
select 字段2 from 表名2
where 条件)
子查询-exists
exists:用于检查主查询的结果集是否存在满足条件的记录,它返回布尔值(True 或 False)。
exists:查找存在的记录。not exists:查找不存在的记录
--主查询,遍历查询是否存在
select 字段1 from 表名1
where exists(
--子查询,先获取共有的记录
select 字段
from 表名2
where 表名1.字段=表名2.字段)
合并查询-UNION 和 UNION ALL
union:去重合并。union all 不去重合并。
select 字段 from 表名1
union select 字段 from 表名2
##4 窗口函数
窗口函数-分组统计-sum over
窗口函数:能够对数据集的子集进行计算
sum(计算字段) over (partition by 分组字段)
窗口函数-累计求和-sum over order by
sum(计算列名) over (
partition by 分组字段 order by 排序字段 排序规则)
窗口函数-统计排名(保留相同排名)-rank
并列时,rank会保留相同的排名,跳过后续排名
rank() over(
partition by 分组字段 order by 排序字段 排序规则)
窗口函数-统计排名(排名唯一连续)-row_number
并列时,row_number会分配唯一连续的排名
row_number() over(
partition by 分组字段 order by 排序字段 排序规则)
窗口函数-lag/lead
lag:查看上一行数据
lag(字段,,偏移函数,null) over (partition 分组列名 order by 排序列名)
lead:查看下一行数据
lead(字段,,偏移函数,null) over (partition 分组列名 order by 排序列名)
三、数据操作语言(DML)
用于数据表中的操作,比如表中新增数据、表中删除数据、修改表中的数据。
【增】-插入insert into
无需指定列名插入新记录
insert into 表名
values(值A1,值B1,……),
values(值A2,值B2,……)
……
指定列名插入新记录
insert into 表名(字段1,字段2,……)
values(值A1,值B1,……),
values(值A2,值B2,……)
……
【删】-删除delete
delete from 表名
where 删除条件
【改】-更新update
update 表名
set 字段1=值1,字段2=值2,……
where 修改条件