目录
3.DQL语句(数据查询语言)(oracle数据库的DQL)
1.DDL语句(数据定义语言)
1)create用于创建新的数据库对象,如表,视图,索引等
2)alter用于修改现有数据库对象的结构,比如添加,删除或修改列
3)drop用于删除现有的数据库对象,如表,视图或索引
4)truncate用于快速删除表中所有记录,但是不删除表本身,且通常不能恢复
5)rename用于重命名现有的数据库对象
6)comment用于为数据库对象添加注释或者描述
2.DML语句(数据操纵语言)
1)insert用于向表中插入新记录
2)update用于更新已存在的记录
3)delete用于删除表中的记录
4)merge(upsert)用于在单个操作中执行插入或更新,具体取决于记录是否存在,存在更新,不存在插入
3.DQL语句(数据查询语言)(oracle数据库的DQL)
1)select用于从一个或多个表中检索数据
使用where字句查询特定条件
使用order by 对结果进行排序
使用rownum限制返回的行数(rownum是在查询后处理的)
使用聚合函数来获取统计信息,如计数,平均值等
使用group by 对数据进行分组,并结合聚合函数使用
having与group by一起使用,过滤分组后的结果
2)分析函数的种类与使用(我常用的)
排名类:(必须在over里面写order by)
row_number() 连续不重复 总共5个, 12345
rank() 重复不连续 总共5个, 12245
dense_rank() 重复且连续 总共5个, 12234
ntile(n) 将结果集分成n桶,并且为每一行分配桶编号
聚合类:
sum(),avg(),max(),min(),count()
偏移类:(必须在over里面写order by)
语法举例:LAG(column_name, offset, default_value) OVER (PARTITION BY partition_expression ORDER BY order_expression)
column_name: 要获取的列。
offset: 偏移量,表示要向前查看多少行(默认为1)。
default_value: 如果没有足够的前行,可以返回的默认值
lead() 向上偏移
lag() 向下偏移
first_value() 返回窗口内的第一个值
last_value() 返回窗口内的最后一个值
3)各个关键字后面可以放什么东西
select后面:列名,计算表达式,聚合函数,distinct,子查询,case语句,窗口函数
from后面:表名,多表join连接,子查询,视图,临时表
where后面:列名与运算符,逻辑运算符,in,exists,between,like,is null/is not null,case when语句,函数
group by后面:分组字段,函数计算字段
4)多表查询-join
inner join 内连接 两表中的数据完全匹配才会返回
left join 左外连接 左表全有,至少左表条数的数据返回
right join 右外连接 右表全有,至少右表条数的数据返回
full join全外连接 两表的数据全有,总条数等于,左表条数+(右表条数-匹配条数)
cross join 交叉连接 笛卡尔积 ,两表数据条数相乘条数的返回结果
5)谓词下推
谓词下推(predicate pushdown)是一种优化技术,它允许查询中的过滤条件(谓词)在数据检索的早起阶段就被应用,以减少需要处理的数据量,先筛选再关联,将这些谓词'推到'更接近数据源的层面,可以提高查询性能.
查看执行计划查看谓词下推是否生效
6)union和union all及join改写union all
union会对数据进行排序然后进行去重,union all直接向上粘
将join语句改写成为union all,因为多表join操作会非常耗费性能,因此常常需要把Join改写为union all的操作,先从简单的来举例,两张表根据关联字段进行join操作,然后select相关字段
进行union all操作之前先确定关联字段和select要查询的字段,因为一般一张表不一定有另外一张表的字段,这种情况下就是默认空值处理,然后把两张表要select的字段以及关联字段进行Union all的操作,这时候你会发现,如果是一对一的关系的话,会出现关联字段有两个重复的,这时候聚合函数就起作用了,根据关联字段进行聚合然后,对select后面的字段取最大值把空值去掉.
上面说的是表关系是1:1的情况,那么1:n这种情况怎么办呢?1:n根据关联字段进行union all之后会出现多个相同关联字段的情况,但是他们不是一一匹配的关系,所以不能直接进行聚合操作。
这种时候可以使用分析函数的max,求最大值,然后把空值给筛选掉
如果n:m的,目前我不太清楚,不过建议可以join成1:n或者1:1然后进行union all ...
4.SQL优化
1)什么时候创建索引
查询性能:如果数据库查询速度较慢,考虑为常用的查询条件或排序字段创建索引
数据量:当数据量增长到一定程度时,可能需要创建索引以提高查询效率
频繁的读取操作:如果某些表经常被读取而不常更新,可以考虑为这些表创建索引
2)索引分类(我常用)
unique-唯一索引: 给字段添加主键约束或者唯一约束时会自动创建唯一索引,要求字段的数据是唯一的
普通索引: 对数据没要求,针对一个字段
组合索引: 对数据没要求,针对多个字段(最左原则:作为条件筛选时一定要用最左边的字段,否则失效)
函数索引: 对数据没要求,针对使用函数的一个字段
位图索引: 针对枚举值少,大量重复值的字段
3)索引失效
对索引字段进行算数运算,索引失效
对索引字段进行函数处理,索引失效
对索引字段进行隐式转换,索引失效(隐式转换优先将字符串转换成数字,只有做字符串函数处理时,如拼接,截取才会将数字转换成字符串
对索引字段进行not in或者not eists,索引失效
组合索引没有遵循最左原则,索引失效
4)看执行计划
看操作类型,是全表扫描,索引扫描,连接类型(嵌套循环,哈希连接,排序合并)
看过滤谓词,看是否进行了谓词下推
看成本估算和操作时间,判断哪些地方效率比较低
5)sql优化(逻辑优化)
不要写select *,写select具体字段
有索引尽量走索引,避免索引失效
多表关联时,要判断关联字段的对应关系.避免数据发散
先筛选数据,再进行表关联或者分组聚合计算
内关联,默认左边表为驱动表,小表驱动大表,查询效率高
分批次提交
group by代替distinct去重,效率高
清空表用truncate代替delete
用exists代替in
当数据量大时建分区表
5.其他(伪列,分区)
1)伪列
rowid,用于存储数据的物理地址,是唯一的
可以通过rowid进行数据的去重,根据所有字段进行分组,然后取最大的rowid号
其中索引中记录有索引字段所对应数据的rowid,可以在回表的时候根据rowid快速查找对应的数据
rownum,序号,根据数据插入的顺序生成的,不排序,执行顺序在select那一层进行生成,在order by之后,因此如果在一个sql中进行order by之后rownum的值可能会乱序
注意:rownum直接在where后作为条件时,返回必须包含第一条数据,不包含不返回结果
如果想要不包含,那么就要套子查询,并且给rownum对应字段起别名
rownum可以作为分批次提交的累加判定值使用
2)分区
什么时候需要用到分区表:
表的数据量特别大
表中包含历史数据,新的数据被增加到新的分区中
表分区的几种类型:
范围分区:range针对数值,金额,年龄,时间等进行数据内容的划分,对相同范围的数据进行统一的管理
列表分区:list有大量重复信息的列,并且数据分布相对均匀,例如省份,商品分类,性别,部门等
散列分区:hash 如果表格的字段没有任何的规则,那么就创建散列分区,数据库内部通过数据的hash算法,将数据计算不同的哈希值,然后根据哈希值对数据进行分类
组合分区:范围+列表,范围+哈希,列表+哈希等