范式"/>
ORACLE PIVOT 行列转换 DDL语句 数据库范式
一.行列转换
1.行转列
先来说这个pivot函数
pivot(fuc(c1) for c2 in(v1,v2,v3))
解释: pivot用于行转列,其中fuc是聚合function,c1即为column_name1,c2为被转换的字段,v1,v2,v3则是要根据被聚合的字段和输出列表数据的转移,即v1,v2,v3为输出结果.
行转列有两种方式 它们的本质都是分组
方式一:
select +case when +group by
方式二:
select+pivot
例:
图1
图2
将图1转到图2
法一
通过观察发现,是将rank进行 行转列 通过nation分组
SELECT NATION,MAX (CASE WHEN RANK = '第一' THEN CITY END) 第一,MAX (CASE WHEN RANK = '第二' THEN CITY END) 第二,MAX (CASE WHEN RANK = '第三' THEN CITY END) 第三,MAX (CASE WHEN RANK = '第四' THEN CITY END) 第四
FROM ADDR_1 GROUP BY NATION;
注意事项:
尽量使用pivot; 聚合函数在不同情况下选
择不同 max使用于大部分场景 min容易与else起冲突
avg和数值起冲突 sum和字符起冲突;搞清楚 按什么分组 对谁进行 行列转换
法二:
SELECT * FROM ADDR_1 PIVOT(MIN(CITY) FOR RANK IN ('第一' 第一,'第二'第二,'第三'第三,'第四'第四));
结果同上
注意事项:pivot() 的本质就是 C1 FOR C2
FOR循环所带来的遍历作用是指将C2不断地循环插入于一个范围中.所以C2就是被转换城新的字段的字段
而C1则是要插入的字段数据
需要对C1使用聚合函数是因为要分组
列转行:
法一:select nation,第一 as city,'第一' rank from addr_2
union all
select nation,第二 as city,'第二' rank from addr_2
union all
select nation,第三 as city,'第三' rank from addr_2
union all
select nation,第四 as city,'第四' rank from addr_2 order by nation;
法二:select *
from addr_2 unpivot(city for rank in(第一 as '第一',
第二 as '第二',
第三 as '第三',
第四 as '第四'));
二.数据库范式
1.E-R模型图(ENTITY - RELATIONSHIP) 实体-联系
实体:客观存在的事物
联系:实体之间的相互关系
属性:实体所具有的一种特性
2.数据库范式
数据库范式目前有六种:第一范式.第二范式.第三范式.第四范式.第五范式(完美范式).BNCF范式
范式等级越高,数据冗余度就会越低
但并不是越高越好,为了减少冗余度升级范式就要承受效率降低的代价
1-3.BNCF知道,4-5了解
一般实际工作中,以第三范式应用最多,即增加冗余度,降低范式等级,提升效率
第一范式
原子性:在1NF中的所有属性都满足原子性,原子性即不可再分,即所有的字段值都是不可分解的字段值
第二范式
满足1NF的基础上,保证数据库的每个字段都和主键相关
第二范式主要针对联合主键
第三范式
确保每列都和主键直接相关而不是简介相关
对于第二范式的联合主键来说
任何一列或多或少都存在关系,但不是直接关系
BCNF范式
在第三范式的基础上,消除主属性对码的函数依赖与传递函数依赖,即消除传递依赖
第四范式
要求把同一表的多对多关系删除
第五范式
略
三.DDL语句
1.表的创建.约束.注释
CREAT TABLE TABLE_NAME (
COLUMN_NAME1 DATA_TYPE[NOT NULL],[DEFAULT VALUE]
COLUMN_NAME2 DATA_TYPE[NOT NULL],[DEFAULT VALUE]
...
)
约束
什么是约束:约束是在表上强制执行的数据校验规则,被插入.修改.删除的数据必须符合相关字段上设置的这些检验条件.也就是约束条件
特点
约束时一个独立的数据库对象 必须建立在表上 没有表也就没有约束
约束可以构建在一个表的单个字段上.也可以建立在一个表的多个字段
除了主键约束之外,一个表可以存在多个表
约束可以在表创建的时候在CREAT语句就创建好 也可以在表已经创建好后通过ALTER TABLE来修改约束或者添加约束
oracle支持下列5种约束: NOT NULL . UNIQUE KEY . PRIMARY KEY . FOREIGN KEY . CHECK
非空约束时唯一一个可以定义在列级的约束,确保该字段的数据不能为NULL
唯一约束可以确保该字段的非空数据不重复
主键约束可以确保该字段既不重复也不为空
检查约束可以确保字段满足自己的检查要求
外键约束可以确保字段的取值范围取决于另一张表的某个字段
表注释
当我们需要对一张表添加注释时
我们可以对表做注释
也可以单独为字段添加注释
COMMENT ON TABLE TABLE_NAME IS ''
COMMENT ON COLUMN TABLE_NAME.COLUMN_NAME IS ''
2.表的复制.表的删除.表的更新
表复制
当我们需要备份数据来对数据库进行操作时 可以使用表复制来备份
复制表的本质也就是创建表
CREATE TABLE NEW_TABLE_NAME AS SELECT ANY_COLUMN_NAME FROM TABLE_NAME
或者
CREATE TABLE NEW_TABLE_NAME AS SELECT ANY_COLUMN_NAME FROM TABLE_NAME WHERE CONDITION JUDGE
在复制语句中添加条件可以满足一些特殊需求
当CONDITION JUDGE 永远判断为FALSE时将不会复制数据 只复制表的结构
表删除
对于不需要的表进行删除以释放空间
DROP
DROP TABLE TABLE_NAME[PURGE]
当添加PURGE关键字时 不会产生垃圾文件
当表中数据有相互依赖时 可以保护相关的数据不被删除,即会出现删除失败的情况
当用户只想删除表中的数据,而不删除表结构时,需要使用 TRUNCATE
TRUNCATE TABLE TABLE_NAME
修改表
举例:
修改表名:RENAME TO
ALTER TABLE TABLE_NAME RENAME TO NEW_TABLE_NAME
添加字段:ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATA_TYPE
修改字段.数据结构:ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DATA_TYPE
字段.名称:ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN_NAME TO NEW_COLUMN_NAME
删除字段:ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME
添加带默认值的列:ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATA_TYPE DEFAULT VAULE
修改列的默认值:ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DEFAULT VALUE
删除列的默认值:ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DEFAULT NULL
添加非空约束:ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME NOT NULL
去掉非空约束:ALTER TABLE TABLE_NAME MODIFY COLUM_NAME NULL
更多推荐
ORACLE PIVOT 行列转换 DDL语句 数据库范式
发布评论