Oracle基础综合案例

这是一篇基础练习题目，从0到1走一遍，有助于对Oracle的理解与运用。

前期环境准备：
---------------------管理员身份登录--------------------------------------------
–建立用户表空间
create smallfile tablespace ht
logging
datafile ‘F:\database\orcl\temp.dbf’ size 10M extent
management local segment space management auto;
–建立索引表空间
create smallfile tablespace htinidx
logging
datafile ‘F:\database\orcl\htinidx.dbf’ size 10M extent
management
local segment space management auto;
–建立角色
create role manager not identified;
–权限赋予角色
grant alter any procedure to manager;
–建立数据库用户
create user ht profile default
identified by orcl default tablespace ht
account unlock;
–查询所有角色
select * from dba_roles;
–查看所有用户：
select * from dba_users;
–角色赋予用户
grant connect to ht;
grant manager to ht;
grant resource to ht;
grant dba to ht;
–现在可以用ht用户登录系统了

场景：

字典表
OPT_TYPE VARCHAR2(32) 字典类型
EN_NAME VARCHAR2(64) 字典码
CN_NAME VARCHAR2(300) 字典值
ABVEN_NAME VARCHAR2(64) 父节点字典码
练习：
1，根据上述描述及表结构关系，创建上述表。

– Create table
create table TEMPS
(
opt_type VARCHAR2(32),
en_name VARCHAR2(64),
cn_name VARCHAR2(300),
abven_name VARCHAR2(64)
)
tablespace HT
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
– Add comments to the table
comment on table TEMPS
is ‘考试字典表’;
---------表重命名（不需要执行）--------------
alter table dictionary rename to dictionarys

select * from ht.temps;
–设置默认用户为ht
alter session set current_schema = ht;
–插入数据
select * from temps for update;
–物理插入
select count(*) from temps;
数据源：
链接：https://pan.baidu.com/s/1cAaIYvNH7svPIB_2Q8lbQQ
提取码：j9ot
2，导入国标行政区划数据,了解新增区划构成特性,加工父类字典码,剔除(已取消)数据
e.g.
OPT_TYPE EN_NAME CN_NAME ABVEN_NAME
AreaCode 330000 浙江省
AreaCode 330200 宁波市 330000
AreaCode 330205 江北区 330200

delete from temps where cn_name like ‘%已取消%’;

select * from temps where en_name like ‘00’;

– Created on 2021/8/30 by LINGCHEN
declare
– Local variables here
cursor cur is select * from temps t;
begin
– Test statements here
for vi in cur loop
if (vi.en_name like ‘00’) then
update temps set temps.abven_name = ‘’
where temps.en_name=vi.en_name;
ELSIF (vi.en_name like ‘____00’) then
update temps set temps.abven_name = substr(vi.en_name,1,2)||‘0000’
where temps.en_name=vi.en_name;
else
update temps set temps.abven_name = substr(vi.en_name,1,4)||‘00’
where temps.en_name=vi.en_name;
end if;
end loop;
end;
–谨记 like可以作为条件判断语句

3，统计展示浙江省,各地级区划下,县级区划数量
e.g.
地级区划 县级区数
南京市 11
无锡市 7
徐州市 10
… …

–地级区划
select cn_name as 地级区划
from temps
where en_name like ‘33__00’ and en_name != ‘330000’
–县级区数
select substr(t.abven_name,1,4) ,count( substr(t.abven_name,1,4)) as 县级区数 from
(select * from temps
where substr(abven_name,1,2)=‘33’ and not abven_name like ‘00’ order by en_name)t
group by substr(t.abven_name,1,4)

–创建联系字段 substr(en_name,1,4)
select a.地级区划，s.县级区数
from
(select cn_name as 地级区划 ,substr(en_name,1,4) as en_name from temps
where en_name like ‘33__00’ and en_name != ‘330000’)a，

(select substr(t.abven_name,1,4) as en_name ,count( substr(t.abven_name,1,4)) as 县级区数 from
(select * from temps
where substr(abven_name,1,2)=‘33’ and not abven_name like ‘00’ order by en_name)t
group by substr(t.abven_name,1,4))s
where a.en_name=s.en_name;
–存在group 的语句，查询结果的字段一定要出现在 group子句中 ；group不能用别名
–当查询条件需要两个表，两个表间的关联一定要用where语句，这样记录才能联系在一起
– 否定判断格式：not a=b not a like b

4，统计全国所有省级区划下,各地级区划,县级区划数量,按省级区划编号排序
e.g.
省级区划 地级区数 县级区数
北京市 0 19
天津市 0 17
河北省 12 169
… … …

–全国省级区划

select cn_name as 省级区划
from temps
where abven_name is null
order by en_name

–地级区划

select cn_name as 地级区划
from temps
where en_name like ‘____00’ and not abven_name is null
order by en_name

–地级区数
—巧妙运用数学思维，先统计省再给最终结果减一，就可以统计到结果为0的数据（天津没有地级市）

select substr(d.en_name,1,2) ,count( substr(d.en_name,1,2))-1 as 地级区数 from
(select * from temps
where en_name like ‘____00’)d
group by substr(d.en_name,1,2)
order by substr(d.en_name,1,2)

------省级区划对应地级区数

select ss.省级区划，ds.地级区数
from
(select cn_name as 省级区划, substr(en_name,1,2) as en_name
from temps
where abven_name is null
order by en_name
)ss，

(select substr(d.en_name,1,2) as en_name ,count( substr(d.en_name,1,2))-1 as 地级区数 from
(select * from temps
where en_name like ‘____00’)d
group by substr(d.en_name,1,2))ds
where ss.en_name=ds.en_name;

—删除三条无效数据

delete from temps where cn_name=‘县’

-----错误的删除

1.打开Flash存储的权限
ALTER TABLE temps ENABLE row movement ;
2.把表还原到指定时间点
flashback table temps to timestamp to_timestamp(‘2021-08-31 13:40:00’,‘yyyy-mm-dd hh24:mi:ss’);
后面的参数为要还原的时间点

---

select * from temps order by en_name;

–省级区划对应县级区数

select ss.省级区划，xs.县级区数
from
(select cn_name as 省级区划, substr(en_name,1,2) as en_name
from temps
where abven_name is null
order by en_name
)ss，

(select substr(t.abven_name,1,2) as en_name ,count( substr(t.abven_name,1,2)) as 县级区数 from
(select * from temps
where not en_name like ‘____00’)t
group by substr(t.abven_name,1,2))xs
where ss.en_name=xs.en_name;

–县级区数

select substr(t.abven_name,1,2) ,count( substr(t.abven_name,1,2)) as 县级区数 from
(select * from temps
where not en_name like ‘____00’)t
group by substr(t.abven_name,1,2)

–县级区划

select * from temps
where not en_name like ‘____00’ order by en_name

—最终结果

select ss.省级区划，ds.地级区数,xs.县级区数
from
(select cn_name as 省级区划, substr(en_name,1,2) as en_name
from temps
where abven_name is null
order by en_name
)ss，

(select substr(d.en_name,1,2) as en_name ,count( substr(d.en_name,1,2))-1 as 地级区数 from
(select * from temps
where en_name like ‘____00’)d
group by substr(d.en_name,1,2))ds，

(select substr(t.abven_name,1,2) as en_name ,count( substr(t.abven_name,1,2)) as 县级区数 from
(select * from temps
where not en_name like ‘____00’)t
group by substr(t.abven_name,1,2))xs
where ss.en_name=ds.en_name and ss.en_name=xs.en_name;

5，使用江北区行政区划查询,对应格式地区信息结果
e.g.条件:330205 ,结果:浙江省->宁波市->江北区