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