[原创]关于ORACLE的使用入门

Oracle
===============================
数据库:
Oracle------>甲骨文(Oracle) 49+%
DB2---------->IBM 49+%
SqlServer---->微软
My Sql------->(Oracle) 开源典型

Oracle:(神谕)
数据的隔离采用用户的方式

userName
passWord
一个数据库的概念:一组内存,一组进程

user
sys:超级管理员
system:管理员
scott:普通用户

用户:可以直接被授权,被指定角色
角色:可以自定义 role 角色包含一组权限
权限:系统中固定 create any table
管理员的角色，可以操作用户和角色

Oracle提供命令行的方式来访问系统
sqlplus

*修改用户密码

alter user system identified by aaa;

(规范的写法都是大写)

*创建用户:

create user luchong identified by aaa;

用户必须被授权才能访问ORACLE数据库

*授权:

grant create any table to luchong;

角色:权限的集合
创建角色:

create role userrole;

角色授权:

grant create any table to userrole;

将角色授权给用户:

grant userrole to luchong;

connect:角色(包含与数据连接等操作的权限)
resource:角色(包含创建对象等的权限)

grant connect,resource to luchong;
conn luchong/aaa;//连接用户

撤销权限:

revoke create any table from luchong;

物理结构:
管理系统
全局数据库:一组日志文件,一组控制文件
一组数据文件.

data space
tablespace:表空间,逻辑概念,对应一个数据文件

Oracle中,创建的user如果指定表空间,管理系统会默认指定
user的默认表空间为users--users.dbf

一个用户会有默认表空间和临时表空间
默认表空间存储数据
临时表空间存储临时数据

sql命令->发送Oracle管理系统->编译sql命令
->形成执行计划->执行

创建表空间:

create tablespace luchongts datafile('d:/luchongts.data' size 50M); 

创建用户指定表空间

create user luchong identified by aaa
default tablespace luchongts;

删除用户

drop user luchong;

SQL:Structured Query Language(结构化的查询语言)

SQL的分类:
DML(Data Manipulation Language)数据操作语言
insert update delete
DQL(Data Query Language)数据查询语言
select
CRUD:create Read Update Delete
DDL(Data Definition Language)数据定义语言
create drop alter
DCL(Data Control Language)数据控制语言
grant revoke
DTL(Data Transaction Language)数据事务语言
commit(提交) rollback(回滚)
savepoint(设置回滚点)

表的管理

create table tbname (
cname1 type(length)
......
);

1.Oracle中命名规则
*只能包含字母,数字,下划线,$和#
*长度限定在1-30个字符
*同一个数据库中的用户,不能有相同的用户名
*不能使用Oracle中的保留字和关键字
*Oracle中的名字不区分大小写
*见名知意
-user t_user
2.Oracle中常用的数据类型
*varchar2:变长字符类型 varchar2(10),
*char:不变长字符类型 char(18)
*number:数字 number(3) number(6,2) 六位数->其中两位是小数
*date:日期

创建一张表**

create table student(
id number(4),
name varchar2(10),
age number(2),
score number(4,1),
idcard char(18),
birth date
);

需求:创建商品表(编号,名称,价格,类别编号)
商品类别表(编号,类别名)

create table product(
product number(4),
productname varchar2(20),
price number(7,2),
categoryno number(2)
);
create table category(
categoryno number(2),
categoryname varchar2(10)
);

查看表结构
desc product;

约束:保证数据的完整性
数据完整性:业务数据的正确性和完备性,
包含实体完整性，域完整性,参照完整性

*主键约束:primary key
*外键约束:foreign key,references
*非空约束:not null
*唯一约束:unique
*检查约束:check

*主键约束:字段中的数据非空 唯一
主键,用来标识记录,开发中,表一定有主键
表只能有一个主键,可以有多个字段组成一个主键.

添加主键的语法:
字段级约束,将约束直接在字段中添加
表级约束,字段定义结束后添加
*以用户表为例
*第一种方式
自己命名

create table t_user1(
id number(4)
constraint t_user1_id_pk primary key,
username varchar2(20),
userpwd varchar2(16),
name varchar2(20)
);

*第二种方式
使用系统默认方式命名

create table t_user2(
id number(4) primary key,
username varchar2(20),
userpwd varchar2(16),
name varchar2(20)
);

表级约束:

create table t_user3(
id number(4),
username varchar2(20),
userpwd varchar2(16),
name varchar2(20),
constraint t_user3_id_pk primary key(id)---使用默认名的表级约束
);

create table t_user4(
id number(4),
username varchar2(20),
userpwd varchar2(16),
name varchar2(20)
);

通过修改表添加主键:

alter table t_user4 add primary key(id);

*外键约束:
字段必须参照某个表的主键的值
foreign key,references
部门表:(deptno,dname,loc)
deptnop primary key
员工表:(empno,ename,sal,job,deptno)
empno primary key
deptno foreign key

*添加外键约束
*方式一

create table dept1(
deptno number(2) primary key,
dname varchar2(10),
loc varchar2(20)
);
create table emp1(
empno number(4) primary key,
ename varchar2(20),
sal number(7,2),
job varchar2(20),
deptno number(2) references dept1(deptno)
);

*方式二

create table emp2(
empno number(4) primary key,
ename varchar2(20),
sal number(7,2),
job varchar2(20),
deptno number(2),
foreign key(deptno) references dept1(deptno)
);

create table emp3(
empno number(4) primary key,
ename varchar2(20),
sal number(7,2),
job varchar2(20),
deptno number(2)
);

***开放中比较常用

alter table emp3 add foreign key(deptno)
references dept1(deptno);

*********

create table emp4(
empno number(4) primary key,
ename varchar2(20) not null,
sal number(7,2) check(sal between 800 and 20000),
idcard char(18) unique,
job varchar2(20),
deptno number(2)
);

修改表 alter table ....
添加字段:

alter table emp4 add comm number(6,2);

修改字段类型

alter table emp4 modify comm number(7,2);

删除字段

alter table emp4 drop (comm);

删除表:
drop table emp4;删除表结构
truncate table emp4;截断表(截断所有的数据,数据无法恢复)
delete from emp4;删除所有的数据(数据可以恢复,开发使用)
DML(核心)
*insert 添加记录
dept(deptno,name,loc)
emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
-----创建表

create table emp(
empno number(4) primary key,
ename varchar2(12),
job varchar2(20),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(6,2),
deptno number(2)
);
create table dept(
deptno number(2) primary key,
dname varchar2(12),
loc varchar2(10)
);

添加外键

alter table emp add foreign key(mgr)
references emp(empno);
alter table emp add foreign key(deptno)
references dept(deptno);

insert into dept values(10,'财务部','海淀区');
insert into dept(deptno,dname,loc)values(20,'研发部','西城区');
commit;
select * from dept;
insert into dept(deptno,dname,loc)values(30,'市场部','东城区');
insert into dept(deptno,dname,loc)values(40,'审计部','朝阳区');

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values
(7000,'KING','总裁',null,'13-1月-2013',25000,null,10);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values
(7369,'刘德华','经理',7000,'13-3月-2013',18000,200,10);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values
(7469,'李宇春','经理',7000,'15-3月-2013',22000,200,20);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values
(7470,'詹姆斯','经理',7000,'1-4月-2014',21000,200,20);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values
(7370,'SMITH','普通员工',7000,'10-1月-2014',8000,500,10);

复制数据

create table emp_copy
as select * from emp;

复制表数据

insert into emp_copy
select * from emp_copy;

*修改数据
update

update emp set job='数据分析员' where ename='詹姆斯';

---多个一起修改

update emp set job='数据分析员' where empno=7469 or empno=7370;

*删除数据
delete

delete from emp where empno =7370;
delete from emp where empno in(1000,2000,3000,4000);

*查询数据

select cn1,cn2,cn3......tablename;
select cn1 from tablename;
select * from tablename;

*条件查询
select ...from ....where条件
比较:>,>=,<,<=,=,!=,<>(不等)
需求:职位不是经理的员工信息

select empno,ename,job,sal,comm,deptno,hiredate,mgr
from emp where job<>'经理';

需求:查询所有普通员工的信息

select empno,ename,job,sal,comm,deptno,hiredate,mgr
from emp where job='普通员工';

----值区分大小写
需求:列出入职日期在2005年后的员工名,入职日期

select ename,hiredate from emp
where hiredate>='1-1月-2015';

Oracle续=========================
查询
null值查询 is null ,is not null
查询没有奖金的员工的姓名,工资,奖金

select ename,sal,comm from emp
where comm is null or comm=0;

布尔连接
and,与的运算
or,或的运算
需求:列出05年后入职,工资大于8000的员工的姓名
入职日期,工资.

select ename,hiredate,sal from emp
where hiredate>'1-1月-2015' and sal >8000;

---给表起别名

select e.ename,e.hiredate,sal
from emp e where ......

--双引号可以用来给字段起别名

select e.ename,e.hiredate as "薪水",sal
from emp e where ......

String sql="select ename,hiredate,sal where 1=1";

//使用append追加(多条件查询)
=======================================
or,或的运算
需求:涨薪，职位是普通员工
或没有奖金的员工的工资涨薪500,
列出，涨薪后的员工姓名，工资

select ename,sal+500 as nsal from emp
where job='普通员工' or comm is null;

==================================
模糊查询
like
%:若干位若干字符 J%(J开头后面不管)
_(下划线):一位若干字符_I __I%(表示第三位是I后面不管)
需求:列出员工姓名，工资，入职日期
姓名中必须包含用户输入的内容

select ename,sal,hiredate from emp
where ename like '%KI%';(名字中只要有KI)

==================================
范围
in,not in
需求:列出10,20号部门的员工姓名，工资
部门号.

select ename,sal,hiredate from emp
where deptno in(10,20);
between....and,not between....and

需求：列出13年1月份到14年1月份入职员工姓名，
入职日期.

select ename,hiredate from emp
where hiredate between '1-1月-2013' and
'1-1月-2014';

***排序查询***
order by:指定排序字段
asc:升序(默认)
desc:降序
数字从小到大
日期从早到晚
字符根据字母序
排序可以有多个参考字段,参考顺序从前到后
需求:根据查询20号部门的员工姓名，工资，入职日期
根据工资降序排序.

select ename,sal,hiredate from emp
where deptno=20 order by sal desc;

需求:列出工资大于10000的员工姓名，工资，入职日期，
根据入职日期升序，工资降序排序

select ename,sal,hiredate from emp
where sal>10000 order by sal desc and hiredate asc;

*null值所在值中排序最大
需求:列出员工姓名，工资，奖金
根据奖金降序显示数据。

select ename,sal,comm from emp
order by comm desc;

分组查询;
分组:group by
运算:组函数 sum avg max min count
需求:统计公司内部有多少个员工?
---- 查询表的记录数

select count(empno) from emp;

需求:列出公司内部每种职位的员工的工资总和.

select job,sum(sal) from emp
group by job;

需求:列出公司内部每个部门的员工工资平均值

select deptno,avg(sal) avg_sal,
max(sal) max_sal,min(sal) min_sal
from emp
group by deptno;

注意:分组查询只能查询分组字段,和组函数运算的结果.
需求:列出平均工资大于10000的部门号
和平均工资
having:筛选分组后的结果

select deptno,avg(sal) from emp
group by deptno having avg(sal)>10000;

需求:列出部门号大于10号部门,平均工资
大于10000的部门号和平均工资.

select deptno,avg(sal) from emp
where deptno>10 group by deptno
having avg(sal)>10000;

需求:列出部门号大于10号部门,平均工资大于
大于10000的部门号和平均工资.根据平均工资降序排序

select deptno,avg(sal) from emp
where deptno>10 group by deptno
having avg(sal)>10000 order by avg(sal) desc;

=================================================
关联查询(多表查询)
连接条件
KING 财务部
需求:列出员工名，部门名,部门号,部门地址

select ename,deptno,dname,loc from
emp,dept;

笛卡尔乘积现象(避免)

select ename,e.deptno,dname,loc from
emp e,dept d where e.deptno=d.deptno;

*内连接查询 都是满足连接条件的数据
select..from t1 inner join t2
on 连接条件
需求:列出员工名，部门名,部门号,部门地址
------内连接-----

select ename,e.deptno,dname,loc from
emp e inner join dept d on e.deptno=d.deptno;

---等值查询，与内连接返回的数据相同--

select ename,e.deptno,dname,loc from
emp e,dept d where e.deptno=d.deptno;

*外连接:保证数据的不缺失
左外连接:保证左表数据不缺失
右外连接:保证右表数据不缺失
全外连接:保证所有表数据不缺失
需求:列出员工名，部门名,部门号,部门地址

左外连接来实现:(一般常用)--1

select ename,e.deptno,dname,loc
from emp e left outer join dept d
on e.deptno=d.deptno;

左外方式二(Oracle中有的)

select ename,e.deptno,dname,loc
from emp e,dept d
where e.deptno=d.deptno(+);

select ename,e.deptno,dname,loc
from emp e left join dept d
on e.deptno=d.deptno;

---------结果是一样的------
右外连接实现

select ename,e.deptno,dname,loc
from dept d right outer join emp e
on e.deptno=d.deptno;

------全外连接

select ename,e.deptno,dname,loc
from emp e full outer join dept d
on e.deptno=d.deptno;

需求:列出员工号，员工姓名,经理工号，
经理的姓名
自连接

select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1 left join emp e2 on
e1.mgr=e2.empno;

需求:列出员工号，员工姓名，经理工号，
经理的姓名，员工的部门地址，
部门号
------超过两张表的左外连接-----

select e1.empno,e1.ename,e1.mgr,e2.ename,dname,loc,d.deptno
from emp e1 left join emp e2 on
e1.mgr=e2.empno left join dept d
on e1.deptno=d.deptno;

=================================================
SQL中常用的函数
*字符函数

lower,upper
select ename,job,sal from emp
where lower(ename)='king';
concat(连接字符串)
select concat('Hello','Oracle!')
from dual;

------ ||(表示拼接)

select 'Hello'|| 'oracle'from dual;(效果同上)
My sal is 1000,I hope 2000!;
select ename || '''s sal is' || sal ||',I hope' || sal*2 || '!'
from emp;

length,substr
select ename,length(ename) 字符数,
hiredate,substr(hiredate,0,2) from // 0:表示第0个位置 2:表示第二个位置
emp;

*数学函数
round:四舍五入
trunc:截断
mod:求余

select round(45.54,2) from dual;
select trunc(45.562) from dual;
select mod(10,2) from dual;

*日期函数
sysdate(返回当前系统的时间)

select sysdate from dual;

日期可以运算
select hiredate ,hiredate+1 from emp;
*months_between(两个日期之间的间隔天数)
需求:列出员工姓名,入职月数

select ename,
round(months_between(sysdate,hiredate))
from emp where hiredate is not null;

add_months

select add_months(sysdate,3) from dual;

next_day 下个星期对应的日期
select next_day(sysdate,'星期三') from dual;
last_day 某月最后一天的日期

select last_day(sysdate) from dual;

round,(<=15舍掉日 >15，加一日)

select round(sysdate,'MONTH') from dual;

trunc

select trunc(sysdate,'DAY') from dual;

extract:返回指定的年或月或日

select extract(month from sysdate) from dual;
select extract(year from sysdate) from dual;

===========================================================================
*转换函数
to_char:日期转换特定格式的字符串(***************)

select ename,to_char(hiredate,'yyyy-MM-dd') from emp;

yyyy:4位数字表示年
mm:2位数字表示月份
dd:2位数字表示日
year:用英文单词表示年
dy:星期的单词前3位
2016年06月06号

select ename,to_char(hiredate,'yyyy"年"MM"月"dd"日"')
from emp;

timestamp类型,常用的日期类型,包含时分秒

create table t_usersys(
id number(4) primary key,
registtime timestamp default systimestamp
);
select id,to_char(registtime,'YYYY-MM-DD HH24:MI:SS')---时 分 秒
from t_usersys;

to_date:将字符串转换为日期(******************)
2016-6-6 06:06:06

insert into t_usersys(id,registtime)values(1002,to_date('2016-6-6 06:06:06','YYYY-MM-DD HH24:MI:SS'));

to_char:将数字转换到特殊格式的字符串
9:表示任意一位的数字
$:美元符号
L:本地货币符号

select ename,sal,to_char(sal,'L999,999,999,99')
from emp;

空值计算:
nvl:nvl(comm,0)(当comm为null时就把它换成是0)

select ename,sal,comm,(sal+nvl(comm,0))*12 年薪 from emp;

去重:
select distinct job from emp;
======================================================================
子查询
需求:列出20号部门中，比30号部门平均工资大的员工姓名，工资，职位

select ename,sal,job from emp
where deptno=20 and
sal>(select avg(sal)
from emp where deptno=30);

(先执行子查询)
需求:列出SMITH的同部门同事的姓名.工资,部门号..

select ename,sal,deptno from emp where deptno=
(select deptno from emp where lower(ename)='smith')
and lower (ename)<>'smith';

in:在范围之内
需求:列出包含20号部门中的职位的员工的姓名，职位，工资。

select ename,job,sal from emp where job in(
select distinct job from emp where deptno=20);

all:所有
any:任意一个
需求:列出员工姓名，工资，职位工资大于20号部门所有员工工资
的姓名，工资，职位.(>max)

select ename,sal,job from emp where sal > all(select sal from
emp where deptno=20
);

需求:列出员工姓名，工资，职位工资大于20号部门任意一个员工工资
的姓名，工资，职位.(>min)

select ename,sal,job from emp where sal > any(select sal from
emp where deptno=20
);

需求:将员工工资低于20号部门平均工资的员工工资涨薪500

update emp set sal=sal+500 where sal <(select avg(sal) from emp where deptno=20);

子查询也可以用在from子句中

select e.* from (select * from emp where deptno=20)e;

(关联子查询)
需求:有下属的员工姓名，工资
exists:是否存在

select empno,ename,sal from emp e where exists(
select mgr from emp where mgr=e.empno
);

需求:列出工资大于本部门平均工资的员工姓名，工资，奖金,部门号.

select ename,sal,comm,deptno
from emp e where sal >(
select avg(sal) from emp where deptno=e.deptno
);

==========================================================
集合操作
union,union all(合并集合)
需求;查询20号部门，工资大于10000,
将两个集合合并

select ename,sal,deptno from emp
where deptno=20;
-----union去重合并---------
-----union all---不去重合并------
select ename,sal,deptno from emp
where sal>10000;

select ename,sal,deptno from emp where deptno=20
union
select ename,sal,deptno from emp where sal>10000;

intersect 获取两个结果集的(交集)
查询职位是经理，工资大于10000

select ename,job,sal from emp
where job='经理' intersect
select ename,job,sal from emp
where sal >10000;
minus:(差集)
rownum:行号,伪列
select empno,ename,rownum from emp where
rownum<=10 minus
select empno,ename,rownum from emp where
rownum<=5;

============================================
Oracle中的分页查询(*****)

page:当前页数
pageSize(perPage):每页条目

begin=(page-1)*pageSize;
end=page*pageSize;

显示任意一段记录
4-6

select ename,sal,r from (
select ename,sal,rownum r from emp where rownum<=6
)where r>=4;
---效果同上---(********开发常用*****)
select ename,sal,r from(
select e.ename,e.sal,rownum r from
(select ename,sal from emp)e)
where r>=4 and r<=6;
----比较好理解的一种(*****开发常用*****)
select ename,sal,r from(
select ename,sal ,rownum r from emp)
where r between 4 and 6;

mysql分页:limit 限定
select ename,sal from emp
limit 10,5;//第一个数字是开始位置，第二个数字是每页显示的最大数

==================================================================
序列(sequence):是一种用来生成唯一数字值
的数据库对象(重要)
序列的值由Oracle程序按递增或递减的顺序自动生成的
是一种高效的获取唯一键值的途径.
序列是独立的字段
通常情况下，一个序列为一个表提供主键值。(也可以为多个表
提供主键值。
)

------序列举例
create table t_user_hjf(
id number(4),
username varchar2(20),
password varchar2(20)
);
----创建一个简单的序列---
create sequence userseq;

1 递增1
序列的应用:
nextval:获取序列的下一个值
currval:获取序列当前值
*必须访问nextval之后才能访问currval*
insert into t_user_hjf(id,username,password)values(userseq.nextval,'aaa','aaa');
create sequence lcseq start with 1000 ---起始值
increment by 10---步长
maxvalue 9990---最大值
cycle---自动循环
--nocycle---不自动循环
cache 30----缓存中存储值的个数
--nocache----不缓存
====================================================
视图(view):命名的查询,虚表
虚拟的表，是一组数据的逻辑表示
本身并没有数据，它只包含映射到
基表的一个查询语句.
用户创建视图，需要用户具备创建视图的权限
create view或create any view
授权:grant create any view to hjf2;
create or replace view empview as
select ename,sal,comm,job from emp;
应用视图:
select * from empview;

简单视图:基于单表，没有使用函数，组函数，group by的字句
复杂视图:基于单表，可能包含单行函数，表达式，分组函数，group by字句等
不能实现DML操作
连接视图:基于多表
需求:创建视图，列出每个部门的员工的平均工资，最大工资，最小工资
工资总和。

create view empsalview
as
select avg(sal) avg_sal ---在使用函数时必须起别名
,deptno,max(sal) max_sal, ---在使用函数时必须起别名
min(sal) min_sal ---在使用函数时必须起别名
,sum(sal) sum_sal ---在使用函数时必须起别名
from emp group by deptno;
应用视图:select * from empsalview;

通过视图来做DML操作

create or replace view empview as---创建视图
select empno,ename,sal,comm,job from emp;

insert into empview values(lcseq.nextval,'黄剑飞',20000,1000,'数据分析员');--插入数据

delete from empview where ename='黄剑飞';---删除数据

带有约束视图
*with check option
DML操作只能

create or replace view empview
as
select empno,ename,deptno
from emp
where deptno=10
with check option; 

---添加数据失败 部门号只能是10---(加了with check option以后)

insert into empview values(
lcseq.nextval,'李四',20
);

 -----正确的方式---------
insert into empview values(
lcseq.nextval,'李四',10
);

*with read only:只读，不支持DML
create or replace view empview
as
select empno,ename,deptno
from emp
where deptno=10
with read only; ---只读视图---
----添加数据失败 只读--------
insert into empview values(
lcseq.nextval,'李四',10
);

数据字典 也是表
user_tables
select * from user_tables;
user_views
select * from user_views;
user_sequences
select * from user_sequences;

=====================================================
索引(index):
create index emp_copy_empno_index
on
emp_copy(empno);

create index emp_copy_ename_job_index
on
emp_copy(ename,job);
Oracle中主键字段，unique字段(自动添加索引)
删除索引:
drop index myindex;

======================================================
表设计的三大范式
第一范式:原子性
第二范式:完全依赖
第三范式:依赖不能传递

id ename sal comm deptno(pk)
1001 张三

id deptno loc
=============================
事务:一组SQL语句操作的逻辑单元
事务的机制:可以保证一组操作，完全成功
执行，完成整个工作单元
特点:(ACID)
Atomicity(原子性)*
事务的原子性是指包含的操作要么全成功，要么全失败.可以保证
数据的一致性。
Consistency(一致性)*
事务前后，会保证数据的一致性的业务规则。
Isolation(隔离性)*
隔离级别:
read nocommit;读不提交
read commit;读提交
serialize;串行化
数据库支持多个事务并发对数据进行读写，修改的操作
不同的事务之间，不会相互影响.
避免交叉操作数据带来数据的不一致性
Durability(持久性)
事务结束对数据的修改应该是持久的.

commit;提交
rollback;回滚
savepoint s;

rollback s;

===================================================
数据库项目:(管理员管理,角色管理,权限)
*表的创建 设计
*数据的初始化
*功能的SQL语句

数据的对应关系
一对多的对应关系
关系模型:主表 从表(FK)
多对多的对应关系
关系模型: 表A(aid) 表B(bid) 表AB(aid,bid)
primary key(aid,bid)


admin
aid username pwd rid
1001 张飞 aaa 30
1002 刘备 aaa 30
admin_role(中间表)
aid rid
1001 10
1001 20
1001 30
1002 20
role
rid rolename
10 超级管理员
20 普通管理员
30 角色管理员
role_privi(中间表)
rid pid
10 1
10 2
10 3
10 4
20 3
privi
pid pname
1 管理员管理
2 角色管理
3 资费管理
4 账务账号管理

t_privilege(权限表)
pid(pk) name
privilege_role(权限角色表)
rid(fk) pid(fk)
pk(rid,pid)
t_role(角色表)
rid(pk) name

 

-----创建权限表---
CREATE TABLE t_privilege(
pid NUMBER(2) PRIMARY KEY,
name VARCHAR2(20)
);
-----创建角色表----
CREATE TABLE t_role(
rid NUMBER(2) PRIMARY KEY,
name VARCHAR2(20)
);
----创建权限角色表---(中间表)
CREATE TABLE privilege_role(
rid NUMBER(2),
pid NUMBER(2),
PRIMARY KEY(rid,pid)
);
---给中间表添加一个外键----
ALTER TABLE privilege_role ADD
FOREIGN KEY(rid) REFERENCES t_role(rid);
---给中间表添加一个外键----
ALTER TABLE privilege_role ADD
FOREIGN KEY(pid) REFERENCES t_privilege(pid);
-----角色表创建序列-----
CREATE SEQUENCE roleseq;
----------初始化权限表的数据---
INSERT INTO t_privilege VALUES(
1,'角色管理'
);
INSERT INTO t_privilege VALUES(
2,'管理员管理'
);
INSERT INTO t_privilege VALUES(
3,'资费管理'
);
INSERT INTO t_privilege VALUES(
4,'账务账号管理'
);
INSERT INTO t_privilege VALUES(
5,'业务账号管理'
);
INSERT INTO t_privilege VALUES(
6,'账单管理'
);
INSERT INTO t_privilege VALUES(
7,'报表管理'
);
-----初始化角色表的数据--------
INSERT INTO t_role VALUES(
roleseq.NEXTVAL,'超级管理员'
);
INSERT INTO t_role VALUES(
roleseq.NEXTVAL,'管理员'
);
INSERT INTO t_role VALUES(
roleseq.NEXTVAL,'账号管理员'
);
--初始化角色权限表数据----------
INSERT INTO privilege_role VALUES(
1,1
);
INSERT INTO privilege_role VALUES(
1,2
);
INSERT INTO privilege_role VALUES(
1,3
);
INSERT INTO privilege_role VALUES(
1,4
);
INSERT INTO privilege_role VALUES(
1,5
);
INSERT INTO privilege_role VALUES(
1,6
);
INSERT INTO privilege_role VALUES(
1,7
);
INSERT INTO privilege_role VALUES(
2,1
);
INSERT INTO privilege_role VALUES(
2,2
);
INSERT INTO privilege_role VALUES(
2,3
);
INSERT INTO privilege_role VALUES(
3,5
);
INSERT INTO privilege_role VALUES(
3,6
);
INSERT INTO privilege_role VALUES(
3,7
);
------创建管理员表------
CREATE TABLE t_admin(
aid number(4) PRIMARY KEY,
admin_code VARCHAR2(30),
password VARCHAR2(8),
name VARCHAR2(20),
telephone VARCHAR2(15),
email VARCHAR2(50),
enrolldate DATE
);
-----创建管理员序列---
CREATE SEQUENCE adminseq
START WITH 1000
INCREMENT BY 10;
-----初始化管理员表数据---
INSERT INTO t_admin VALUES(
adminseq.NEXTVAL,'admin','aaa',
'刘备','13333333333','liubei@tedu.cn',
SYSDATE
);
INSERT INTO t_admin VALUES(
adminseq.NEXTVAL,'guanyu','aaa',
'关羽','13444444444','guanyu@tedu.cn',
SYSDATE
);
INSERT INTO t_admin VALUES(
adminseq.NEXTVAL,'zhangfei','aaa',
'张飞','13555555555','zhangfei@tedu.cn',
SYSDATE
);
INSERT INTO t_admin VALUES(
adminseq.NEXTVAL,'lvbu','aaa',
'吕布','13666666666','lvbu@tedu.cn',
SYSDATE
);
------创建管理员角色表---
CREATE TABLE admin_role(
aid NUMBER(4),
rid NUMBER(4),
PRIMARY KEY(aid,rid)
);
-----给管理员角色表添加外键---
ALTER TABLE admin_role ADD
FOREIGN KEY(aid) REFERENCES t_admin(aid);
ALTER TABLE admin_role ADD
FOREIGN KEY(rid) REFERENCES t_role(rid);
-----初始化管理员角色表的数据---
INSERT INTO admin_role VALUES(
1010,2
);
INSERT INTO admin_role VALUES(
1020,3
);
INSERT INTO admin_role VALUES(
1030,2
);
INSERT INTO admin_role VALUES(
1030,3
);
INSERT INTO admin_role VALUES(
1030,1
);

==============================================
实现项目中角色管理的功能
1,分页显示角色

SELECT rid,name FROM
(SELECT rid,name,rownum r
FROM t_role
WHERE rownum<=2)
WHERE r>=1;

2:根据已知角色的id,显示对应的
权限的名字

SELECT name FROM
t_privilege p LEFT JOIN privilege_role pr
ON p.pid=pr.pid
WHERE
pr.rid=1;

3:添加角色并授权

INSERT INTO t_role(rid,name)VALUES(
roleseq.NEXTVAL,'资费管理员'
);
---授权---
INSERT INTO privilege_role(rid,pid)VALUES(
4,1
);
INSERT INTO privilege_role(rid,pid)VALUES(
4,2
);

4:点击去修改的链接，根据角色ID，查询角色信息以及权限ID

SELECT rid,name FROM t_role WHERE
rid=1;

SELECT pid FROM privilege_role WHERE
rid=1;

SELECT r.rid,name,pr.pid FROM t_role r LEFT JOIN
privilege_role pr ON
r.rid=pr.rid where
r.rid=1;

5:点击确认修改按钮
*修改角色的名字

UPDATE t_role SET name='超级管理员'
WHERE rid=4;

*修改角色权限
先清空角色权限，在添加新权限

DELETE FROM privilege_role WHERE rid=1;
INSERT INTO privilege_role(rid,pid)
VALUES(4,1);
INSERT INTO privilege_role(rid,pid)
VALUES(4,2);
INSERT INTO privilege_role(rid,pid)
VALUES(4,3);

6:删除角色
先删除中间表角色与权限的对应关系

DELETE FROM privilege_role
WHERE rid=4;
DELETE FROM t_role
WHERE rid=4;

实现项目中的管理员模块查询功能
动态条件分页查询管路员功能

aid name
1001 张三
1002 李四

rid name
1 超级管理员
2 管理员

pid name
1 角色管理
2 管理员管理

SELECT aid,name,email FROM t_admin
WHERE aid IN(
SELECT DISTINCT aid FROM admin_role
WHERE rid IN(
SELECT DISTINCT rid FROM privilege_role
WHERE pid=1
)
);

----------------------------------------------
*查询权限ID为1对应的管理员的ID和NAME

SELECT DISTINCT a.aid,name FROM t_admin a
JOIN admin_role ar ON a.aid=ar.aid
JOIN privilege_role pr ON ar.rid=pr.rid
WHERE pr.pid=1;

------分页---------------------------------

SELECT aid,name FROM(
SELECT aid,name,ROWNUM r FROM(
SELECT DISTINCT a.aid,name FROM t_admin a
JOIN admin_role ar ON a.aid=ar.aid
JOIN privilege_role pr ON ar.rid=pr.rid
WHERE 1=1
AND pr.pid=1
AND ar.aid=2
)WHERE ROWNUM<=4)
WHERE r>=3;

--------------------------------------------
登录:
*查询登录用户的详细信息(此处默认是管理员)

SELECT aid,name,admin_code,password
FROM t_admin
WHERE admin_code='admin'
AND password='aaa';

*查询登录用户的具备的权限(假设当前的用户为1010号管理员)

SELECT DISTINCT pid FROM admin_role ar
JOIN privilege_role pr ON
ar.rid=pr.rid
WHERE ar.aid=1010;

 作者:little飞 出处:http://www.cnblogs.com/little-fly/ 欢迎转载 也请保留这段声明 谢谢!