Oracle入门4-JZTXT

case when

在查询的时候可以对数据进行判断，可以指定不同情况下判定的值

-- 按照各科平均成绩从低到高和及格率的百分数从高到底排序
select avg(score) as av, 
sum (case when score >= 60 then 1 else 0 end)/count(*) as jigelv 
from sc group by cno order by av, jigelv desc;
-- case when 在查询的时候会对score进行判断
-- when代表 比较的条件
-- end代表 case when的结束符号

top-n问题

排序之后取前n个数据，要使用一个特别的函数（分区函数）（over）

分组函数（group by）:会将数据分成若干个组，并且将行数据转换为组数据。

分区函数（over）:会按照指定字段，将相同字段值的数据分在同一个区内，被分去的数据，不会变成组数据。

-- 查询各科成绩前三名的数据
select * from 
(select sno, cno, score, row_number() over(partition by cno order by score desc)rn from sc)
where rn<4;

-- over----分区函数名称
-- partition by----决定数据分区的一句
-- order by----各个分区中数据的排序方式
-- rn----代表分区函数的运行结果，每一条数据再被分区之后都会产生一个运行结果，产生的数字值类似于rownum，代表是是该数据再分区中所对应的排名
-- over函数的前缀有三个：
-- row_number()：不考虑并列情况，每一个排名只会有一个人
-- rank()：考虑并列情况，后续的排名会受到并列人数的影响
-- dense_rank()：考虑并列情况，后续排名不会受到并列人数的影响
-- sum()：

Oracle语言分类

1.数据定义语言（DDL）create, alter, drop, truncate

2.数据操纵语言（DML）insert, update, delete

3.数据查询语言（DQL）select, order by, where

4.事务控制语言（TCL）commit, rollback, savepoint

5.数据控制语言（DCL）grant, revoke

Oracle数据类型详解

数据类型	定义
char	定长字符串类型（需要定义长度，2000最大）
nvarchar2	可变长度是字符类型（所有字符无论编码和中英文一个字符占据两个字节，解决了中英文存储长度不一致的情况，2000最大）
varchar2	可变长度的字符串类型（大小以数据本身大小为准，4000最大）
number(a,b)	a：包括小数在内的总共有效位数（1_{38）。b：精确到小数点后的位数（-84}127）当b区负数的时候将小数点左边的a位置置零，并四舍五入。（需要22个字符的存储空间）
integer/int	等价于number(38)
smallint	等价于number(38)
decimal(a,b)	如果a，b没有指定，等价于number(38)
float(b)	a：表示的是二进制的位数（1~126）默认为126
real	是float(a)的子类型，等价于float(63)
date	世纪 - 年 - 月 - 日 - 时 - 分 - 秒（占用七个字节，每一个部分占用一个字节）
timestamp(a)	事件戳类型 a：（0~9）默认为6
long	长文本类型（弃用）最多可以存储2GB大小的数据
blob	支持存储二进制大型对象比如：图片，音乐，视频存储最大4GB
clob	字符大型对象，存储文章，较长文字，存储最大4GB

阿里云的oss接口

企业开发设计的流程

需求分析（产品经理）：产品经理会书写prd（需求文档）和甲方对接需求，一般prd会和原型进行结合，帮助甲方去确认需求。产品经理在确定需求后，需要和乙方的程序员沟通，将功能转换为程序员能够读懂的业务，进行完成

常用的原型工具（sketch,axure）
数据库建模：根据业务，定义表结构以及表的关联性
正式开发：

传统开发：前后端不分离，所有代码由后端开发者自行完成

前后端分离开发：前端编写前端代码，后端编写和业务接口，并行开发
code review（cr）：代码复查，在项目上线之前解决问题
功能上线前测试
上线测试

发现bug-->项目维护（同时更新项目的功能）
10%的时间在写代码，70%的时间在修改bug，20%在摸鱼

数据库建模和三大范式

设计范式是为了规范程序员更好更合理的去定义表结构和表关系

1. 第一范式：所有的列都不可以再分（列的原子性）
1. 第二范式：所有的字段都和主键直接相关
1. 第三范式：表和表之间只存关系，不存数据（两个表之间只需要由一个关联字段即可，并且这个字段大概率是另一张表的主键值）
1. 一对多关系中，表的关联字段放在多的那张表中，可以酌情对关联字段进行外键约束的建立
1. 多对多关系中，表的关联，体现在第三个关联表中，关联表中的字段，也可以添加外键约束
1. 在某些场景下，可以适当的违背三大范式，在情况允许的情况下（某些跨表的字段在项目运行的时候频繁被使用，就可以适当的冗余）
1. 冗余字段本身是违背三大范式的，但是如果冗余字段经常被查询，适当冗余可以减少笛卡尔积（将夺标关联查询简化为单表查询，提高查询效率，空间换时间）

布置数据库项目的要求，用包和存储过程做

基于天猫，淘宝等购物完成实现数据库建模并最终通过存储过程和包实现功能。

项目需求

1. 前台，后台
1. 商家可以对商品进行管理
1. 商家可以对分类进行管理
1. 商家可以对商品的具体属性和属性值进行设置
1. 用户登录，注册，修改用户信息
1. 用户可以查看商品信息，查看分类信息，查看指定分类下的商品信息
1. 用户可以实现购物行为（生成订单和订单详情）
1. 用户可以评价商品
1. 用户可以对订单进行操作

大致要求

数据库建模，提供表结构模型
同一个小组内，表名字段名统一，并且设计需要符合规范，不能出现拼音和中文名
给表中的每一个字段添加你认为合适的约束，包括但不限于主键，非空，唯一，检查，外键等等
每张表至少提供10条数据用于后续测试
后续的功能等待我们后面的内容学习完成之后再继续

数据库对象

表，序列，同义词，视图，索引，表空间，游标，触发器， plsql存储过程，存储函数，包

序列

创建所有的数据库对象都是需要对应权限的，为了方便同意，后续所有操作全部在scott用于下完成

-- 给用户赋予创建序列的权限
grant create sequence to 用户名;

-- 什么是序列
-- 序列是生成有序数字的数据库对象，序列所生成的序号叫做序列号，一般用户数据库表主键的生成
-- 同一用户下数据库对象的名称不可以重复

-- 序列语法如下
create sequence 序列名称 -- 创建序列并指定序列的名称
start with XX -- 序列的初始值，从哪个数字开始
increment by XX -- 序列所生成的序列号的增长间隔，默认为1
maxvalue XX | nomaxvalue -- 如果指定了最大值，序列号生成到最大值之后，在没有循环的情况下，就不会再生成序列号，默认最大值1027，也可以不设置最大值
minvalue XX | nominvalue -- 如果指定了序列的最小值，在有循环的情况下，默认最小值为-1026，也可以不设置最小值
cycle |nocycle -- 表示当序列生成到最大值之后，下一个序列号会会到最小值，如果设置nocycle，序列生成到最大值之后，继续获取序列号就会报错
cache | nocache -- 代表是否在缓存中提前生成序列号，Oracle默认会生成20个序列号存放在内存中，如果使用的是nocache，则表示oracle不会提前生成序列号，当用户尝试从序列获取序列号的时候，再计算生成
-- 如果序列设置了循环，就必须设置最大值
-- 如果没设置循环，可以不设置最大值
-- 一般情况下，不需要担心序列号不够用的情况
-- Oracle单表最多支持一亿条数据，序列生成的序列号足够使用
-- 序列取值顺序(cycle)：
-- startwith-->maxvalue-->minvalue-->maxvalue....

-- 创建一个从1开始，最大值为40，每次增长间隔为1，不使用循环，使用缓存30个
create sequence test01
start with 1
increment by 1
maxvalue 40
nocycle
cache 30;

-- 删除序列
drop sequence 序列名;

-- 序列对象有两个属性
-- nextval 复测从序列中取出下一个序列号
-- currval 从序列中取出上次取出的序列号，注意，如果序列没有调用过nextval 使用currval会报错
select test01.nextval from dual;
select test01.currval from dual;

-- 序列一般是为表的主键提供不重复的序列号所服务的，会结合触发器实现主键的自动生成
-- 在大数据量场景下，序列生成的自然数主键无法满足数据唯一性的需求，企业一般会使用uuid(随机字符串)
-- uuid使用的是32位的十六进制数（实际存储的时候以字符串形式进行存储）
-- uuid在任何情况下，都能保证数据主键的唯一性