Oracle day9

------------------------------------------------------------------------------------
--创建用户
create user test1 identified by 123456;
create user ZJun identified by 888888;
--授予权限
grant create session to test1;
grant create  session to ZJun;
--删除用户
drop user test1;
drop user ZJun;

--修改用户
alter user test1 identified by 654321;
alter user ZJun identified by 666666;

alter user test1 identified by 123456;

------------------------------------------------------------------------------------
/*CREATE TABLE G1212(   
X INT,   
L VARCHAR2(10)) ; 
INSERT INTO G1212 VALUES(1, 'A');
INSERT INTO G1212 VALUES(2, 'A');
INSERT INTO G1212 VALUES(3, 'A');
INSERT INTO G1212 VALUES(4, 'A');
INSERT INTO G1212 VALUES(5, 'B');
INSERT INTO G1212 VALUES(6, 'B');
INSERT INTO G1212 VALUES(7, 'B');
INSERT INTO G1212 VALUES(8, 'A');
INSERT INTO G1212 VALUES(9, 'A');*/

select t.l,min(t.x)||'-'||max(t.x) r from
(select l,X,(X-row_number()over(partition by l order by x)) r from G1212) t
group by t.l,t.r order by t.l ;

------------------------------------------------------------------------------------

--系统权限
--查看当前用户系统权限列表
select * from user_sys_privs;
--授权系统权限
grant create session to test1;
--传递系统权限
grant create view to ZJun with admin option; 
--连接ZJun执行下面
grant create view to test1;
--回收系统权限
revoke create view from test1;

--对象权限
--授权对象权限
grant all on scott.emp to test1;
--连接到test1执行查询语句
select * from scott.emp;

--传递对象权限
grant all on scott.dept to ZJun with grant option;
--连接到ZJun
select * from scott.dept;
grant all on scott.dept to test1;
--连接到test1
select * from scott.dept;

--回收对象权限
revoke all on scott.dept from ZJun;
revoke all on scott.emp from test1;

--角色：一系列相关权限的集合
--查看当前登录用户所拥有的角色
select * from role_sys_privs;

-- connect: create sesion
grant create session to test1; --授予权限
grant connect to test1;  --授予角色
--DBA:包含所有系统权限
grant DBA to ZJun;
--resourse:一系列开发权限
grant RESOURCE to ZJun;

--自定义角色
create role role1;
grant create session,create table,create view to role1;
grant role1 to ZJun;

--回收角色
revoke DBA,RESOURCE from ZJun;
--删除角色:drop role 角色名
drop role role1;
------------------------------------------------------------------------------------

--创建表
/*create table cartoon(
name char(50) default '西游记',
role char(50) default '孙悟空',
role_age number(3) default 500
);*/
select * from cartoon;
/*insert into cartoon values('喜羊羊与灰太狼','懒羊羊',12);
insert into cartoon values('大耳朵图图','胡图图',6);
insert into cartoon values('大头儿子和小头爸爸','大头儿子',3);
insert into cartoon(name,role) values('西游记','孙悟空');*/

comment on table cartoon is '动画片';
comment on column cartoon.name is '名字';
comment on column cartoon.role is '主角';
comment on column cartoon.role_age is '主角年龄';

comment on table emp is '员工信息表';
comment on column emp.ename is '姓名';
comment on column emp.empno is '编号';
comment on column emp.job is '职位';
comment on column emp.hiredate is '入职日期';
comment on column emp.sal is '薪资';
comment on column emp.comm is '奖金';
comment on column emp.deptno is '所属部门';
comment on column emp.mgr is '上级编号';

comment on table dept is '部门表';
comment on column dept.dname is '部门名称';
comment on column dept.deptno is '部门编号';
comment on column dept.loc is '所在地区';

create table c_bak as select * from cartoon;
select * from c_bak;

------------------------------------------------------------------------------------

drop table reader;
create table book(
       no number primary key,
       title char(500),
       author char(50),
       publish char(500),
       pub_date date,
       price number
);
select * from book;
/*insert into book 
values(100001,'Oracle 9i 数据库系统管理','李代平',
'冶金工业出版社',to_date('2003-01-01','yyyy-MM-dd'),38);
insert into book 
values(100002,'Oracle 9i 中文版入门与提高','赵松涛',
'人民邮电出版社',to_date('2002-07-01','yyyy-MM-dd'),35);
insert into book 
values(100003,'Oracle 9i 开发指南：PL/SQL 程序设计','Joan Casteel',
'电子工业出版社',to_date('2004-04-03','yyyy-MM-dd'),49);
insert into book 
values(100004,'数据库原理辅助与提高','盛定宇',
'电子工业出版社',to_date('2004-03-01','yyyy-MM-dd'),34);
insert into book 
values(100005,'Oracle 9i 中文版实用培训教程','赵伯山',
'电子工业出版社',to_date('2002-01-01','yyyy-MM-dd'),21);
insert into book 
values(100006,'Oracle 8 实用教程','翁正科等',
'电子工业出版社',to_date('2003-07-08','yyyy-MM-dd'),38);*/

create table reader(
    rno number primary key,
    rname char(50)
);
select * from reader;
/*insert into reader values(200001,'张三');
insert into reader values(200002,'李凤');
insert into reader values(200003,'孟欣');
insert into reader values(200004,'谢非');
insert into reader values(200005,'刘英');*/
drop table borrow;
create table borrow(
       no number references book(no),
       rno number references reader(rno),
       borrow_date date 
);
select * from borrow;
/*insert into borrow values(100001,200001,
to_date('2004-04-08 10:06:14','yyyy-MM-dd hh24:mi:ss'));
insert into borrow values(100002,200002,
to_date('2004-04-08 10:06:27','yyyy-MM-dd hh24:mi:ss'));
insert into borrow values(100003,200003,
to_date('2004-04-08 10:06:36','yyyy-MM-dd hh24:mi:ss'));
insert into borrow values(100004,200004,
to_date('2004-04-08 10:06:48','yyyy-MM-dd hh24:mi:ss'));
insert into borrow values(100005,200005,
to_date('2004-04-08 10:06:58','yyyy-MM-dd hh24:mi:ss'));*/
select * from book,reader,borrow where book.no = borrow.no
and reader.rno = borrow.rno;

------------------------------------------------------------------------------------

drop table "Employee";
create table "Employee"(
       "EmplID" varchar(4) primary key not null,
       "EmplName" varchar(12) not null,
       "Sex" varchar(3) not null,
       "Birthday" date not null,
       "Address" varchar(30),
       "Wages" number not null,
       "DeptID" varchar(4) references "Department"("DeptID")
);

select * from "Employee";

comment on table "Employee" is 'Employee表';
comment on column "Employee"."EmplID" is '员工号';
comment on column "Employee"."EmplName" is '姓名';
comment on column "Employee"."Sex" is '性别';
comment on column "Employee"."Birthday" is '出生日期';
comment on column "Employee"."Address" is '地址';
comment on column "Employee"."Wages" is '工资';
comment on column "Employee"."DeptID" is '部门号';

------------------------------------------------------------------------------------

drop table "Department";
create table "Department"(
       "DeptID" varchar(4) primary key not null,
       "DeptName" varchar(30) not null
);
select * from "Department";

comment on table "Department" is 'Department表';
comment on column "Department"."DeptID" is '部门号';
comment on column "Department"."DeptName" is '部门名称';

------------------------------------------------------------------------------------

CREATE TABLE F801(
    日期 DATE,
    类型 VARCHAR2(10),
    金额 number
);
/*INSERT INTO F801 VALUES (to_date('2021-01-01','yyyy_mm_dd'),'借款',100);
INSERT INTO F801 VALUES (to_date('2021-01-31','yyyy_mm_dd'),'借款',-50);
INSERT INTO F801 VALUES (to_date('2021-01-20','yyyy_mm_dd'),'还款',50);
INSERT INTO F801 VALUES (to_date('2021-01-23','yyyy_mm_dd'),'还款',-20);
INSERT INTO F801 VALUES (to_date('2021-02-01','yyyy_mm_dd'),'借款',100);
INSERT INTO F801 VALUES (to_date('2021-02-28','yyyy_mm_dd'),'借款',-50);
INSERT INTO F801 VALUES (to_date('2021-02-20','yyyy_mm_dd'),'还款',50);
INSERT INTO F801 VALUES (to_date('2021-02-23','yyyy_mm_dd'),'还款',-20);
INSERT INTO F801 VALUES (to_date('2021-03-01','yyyy_mm_dd'),'借款',100);
INSERT INTO F801 VALUES (to_date('2021-03-31','yyyy_mm_dd'),'借款',-50);
INSERT INTO F801 VALUES (to_date('2021-03-20','yyyy_mm_dd'),'还款',50);
INSERT INTO F801 VALUES (to_date('2021-03-23','yyyy_mm_dd'),'还款',-20);*/

select * from F801;
---错解
/*
with tt as (select * from (select t.月份,t.类型,sum(t.金额） from(
select to_char(f.日期,'yyyy')||'-'||to_char(f.日期,'MM') as 月份,f.类型,f.金额，
sum(金额)over(partition by extract(month from 日期),f.类型 order by 金额 ) 
from F801 f )t group by t.月份,t.类型 order by t.月份))

select tt.月份,max(decode(tt.类型,借款,tt.借款金额,0)) from 
(select * from (select t.月份,t.类型,sum(t.金额） from(
select to_char(f.日期,'yyyy')||'-'||to_char(f.日期,'MM') as 月份,f.类型,f.金额，
sum(金额)over(partition by extract(month from 日期),f.类型 order by 金额 ) 
from F801 f )t group by t.月份,t.类型 order by t.月份)) tt;
select* from f801;
*/

select t.月份,t.借款 as 借款金额, t.还款 as 还款金额,sum(t.借款)over(order by t.月份) as 累计借款,
sum(t.还款)over(order by t.月份) as 累计还款 from(
select to_char(f.日期,'yyyy-MM') as 月份,
sum(decode(f.类型,'借款',f.金额,0)) 借款,sum(decode(f.类型,'还款',f.金额,0)) 还款
from F801 f group by to_char(f.日期,'yyyy-MM')) t group by t.月份,t.借款,t.还款;

select t.月份,t.借款 as 借款金额,t.还款 as 还款金额,sum(t.借款)over(order by t.月份) as 累计借款,
sum(t.还款)over(order by t.月份) as 累计还款 
from (select to_char(日期,'yyyy-MM') as 月份,sum(借款) as 借款,sum(还款) as 还款 from f801
pivot(sum(金额) for 类型 in ('借款' as 借款,'还款' as 还款))group by to_char(日期,'yyyy-MM') ) t;