[数据库]-oracle基础
资料
1、关于Mysql数据库中的数据类型
1、关于Mysql数据库中的数据类型：
* int 整数型
* bigint 长整型
* float 单精度浮点型
* double 双精度浮点型
* char 字符型【长度不可变】
* varchar 字符型【长度可变】
* date 日期类型
* blob 二进制大对象【Binary Large OBject，存储图片、声音、视频...】
* clob 字符大对象【Character Large OBject，可以存储4G+的字符串】
2、关于Oracle数据库中的数据类型：
* number
- number(3) 整数型：3表示长度，最大值是999
- number(3,2) 浮点型：3是有效数字的个数，2是小数位的个数
- 案例：
create table t_product(
price number(3,2)
);
insert into t_product(price) values(10); //ORA-01438: 值大于为此列指定的允许精度
insert into t_product(price) values(1); //可以
insert into t_product(price) values(1.2356); //可以
* char 字符型【长度不可变】
* varchar2 字符型【长度可变】
* date 日期类型
* blob 二进制大对象【Binary Large OBject，存储图片、声音、视频...】
* clob 字符大对象【Character Large OBject，可以存储4G+的字符串】
3、在实际开发中，日期类型一般都会采用char类型这种字符串的形式表示。因为每一个数据库
在处理日期类型方面都是不同的。使用date类型会导致将来的数据库很难移植。
4、关于blob类型：
create table t_movie(
id number(10) primary key,
name varchar2(32),
image blob
);
当一张表中的某个字段的数据类型是blob的时候，该字段插入数据的时候不能使用insert语句，
insert语法无法完成，必须使用编程语言中提供的IO流来完成插入操作。
但是，实际开发中一般情况下blob使用较少，当向一张表中存储图片的时候，可以考虑将图片存储
到“云盘”当中，然后云盘对外提供一个共享的连接地址，将这个共享的连接地址插入到数据库表中，
将来用户查询这张表的时候，直接获取到共享链接地址，通过链接地址访问。
5、char和varchar的区别？
* char是定长字符串
* varchar是可变长度字符串
* 假设在建表的时候，给name字段设置的数据类型是char(10)，将来向这个name字段中插入"jack"的时候，
底层实际上存储的是：“jack+6个空格"。使用不恰当的时候会浪费存储空间。
* 假设在建表的时候，给name字段设置的数据类型是varchar(10)，将来向这个name字段中插入"jack"的时候，
底层实际上存储的是：“jack"。它会根据数据的实际长度进行空间的分配。
* char和varchar怎么选择使用？
- 当某个字段中存储的数据长度是不变的，例如：性别、生日这样的字段还是采用char类型来定义，
效率高，也不会浪费空间。
- 当这个字段的长度不固定的时候，例如，这个字段是用来存储一个人的简介的时候，建议采用varchar类型。
6、varchar和varchar2的区别？
* varchar是SQL语句当中的标准，所有的数据库都支持。
* varchar2是varchar第二代，是Oracle数据库中特有的，其它数据库中没有。
* varchar采用一个字节编码
* varchar2采用两个字节编码，所以varchar2可以容纳更多国家的语言。varchar2的容量较大。
* 在实际开发中尽量使用标准，使用varchar，即使在程序中使用了varchar，到Oracle数据库中也会自动变成varchar2
2、单行函数
单行函数/数据处理函数：
1、什么是单行函数？
一个输入最终对应一个输出，这种函数不会一次处理多行，一行一行进行处理。
2、常用的单行函数：
* lower
- SQL> select lower(ename) as ename from emp;
ENAME
--------------------
smith
allen
ward
jones
martin
blake
clark
king
turner
james
ford
miller
以上由大写变成小写是查询结果集变成小写了。数据库表没有变，因为这只是一个DQL语句。
- 需求：找出员工smith的薪水，前提是不知道数据库表中存储的是大写的SMITH,还是小写的smith，
也有可能是大小写混合的Smith。
SQL> select ename,sal from emp where lower(ename) = 'smith';
ENAME SAL
-------------------- ----------
SMITH 800
* upper
SQL> select ename,sal from emp where upper(ename) = 'SMITH';
ENAME SAL
-------------------- ----------
SMITH 800
SQL> select lower('ABC') as ename from dual;
ENAME
------
abc
SQL> select upper('abc') as ename from dual;
ENAME
------
ABC
* substr
- SQL> select substr('abcdef',3,2) as ename from dual;
ENAM
----
cd
- 找出员工名字中第二个字母是A的
* 模糊查询
select ename from emp where ename like '_A%';
ENAME
---------
WARD
MARTIN
JAMES
* select ename from emp where substr(ename,2,1) = 'A';
ENAME
----------
WARD
MARTIN
JAMES
* length
SQL> select length('abc') as namelength from dual;
NAMELENGTH
----------
3
* trim
SQL> select trim(' a b c ') as ename from dual;
ENAME
--------------
a b c
SQL> select length(trim(' a b c ')) as enamelength from dual;
ENAMELENGTH
-----------
7
* concat
SQL> select concat('abc','def') as ename from dual;
ENAME
------------
abcdef
* ||
SQL> select 'abc'||'def'||'xx' as ename from dual;
ENAME
----------------
abcdefxx
* nvl
- 在mysql数据库当中是ifnull，在Oracle数据库当中是nvl
- 什么时候使用空值处理函数呢？
* 只要有空值参与的数学运算需要提前进行空值处理。要不然最终结果就是空。
- 计算年薪：
select ename,(sal + comm) * 12 as yearsal from emp;
ENAME YEARSAL
-------------------- ----------
SMITH
ALLEN 22800
WARD 21000
JONES
MARTIN 31800
BLAKE
CLARK
KING
TURNER 18000
JAMES
FORD
MILLER
select ename,(sal + nvl(comm,0)) * 12 as yearsal from emp;
ENAME YEARSAL
-------------------- ----------
SMITH 9600
ALLEN 22800
WARD 21000
JONES 35700
MARTIN 31800
BLAKE 34200
CLARK 29400
KING 60000
TURNER 18000
JAMES 11400
FORD 36000
MILLER 15600
* case..when..then..when..then..else..end
select
ename,sal,(case job when 'MANAGER' then sal * 1.1 when 'SALESMAN' then sal * 1.5 else sal end)newsal
from
emp;
ENAME SAL NEWSAL
-------------------- ---------- ----------
SMITH 800 800
ALLEN 1600 2400
WARD 1250 1875
JONES 2975 3272.5
MARTIN 1250 1875
BLAKE 2850 3135
CLARK 2450 2695
KING 5000 5000
TURNER 1500 2250
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
* decode【Oracle中特有的,和以上的case可以达到同等效果】
select
ename,sal,decode(job,'MANAGER',sal * 1.1,'SALESMAN',sal * 1.5,sal) newsal
from
emp;
这种方式别人写能看懂就行，不要求自己掌握。可读性差。
* round
SQL> select round(123.456) as result from dual;
RESULT
----------
123
SQL> select round(123.456,1) as result from dual;
RESULT
----------
123.5
SQL> select round(123.456,2) as result from dual;
RESULT
----------
123.46
SQL> select round(123.456,0) as result from dual;
RESULT
----------
123
SQL> select round(123.456,-1) as result from dual;
RESULT
----------
120
* to_date
* to_char
* to_number
3、关于DQL语句select关键字后面可以跟什么？
* 可以是一张表的某个字段名称
select ename from emp; //ename就是emp表中的一个字段
* 可以是一个字面值
select 123 from emp; //123是一个整数型字面值
select 'abc' from emp; //'abc'是一个字符型字面值
最终的查询结果是，那要看emp表中有多少条记录，有10条记录，最终的查询结果是10个123或者10个'abc'
* 关于Oracle数据库中自带的dual表
这张表只有一个字段，叫做：dummy
这张表中dummy字段只有一个值是：X【X表示未知】
dual表可以看做是Oracle数据库提供的一张测试表。
SQL> select 'abc' as xxx from dual;
XXX
------
abc
3、to_date,to_char,to_number函数
to_date
1、to_date函数的作用：将varchar类型转换成date类型
2、to_date函数的最终运算结果类型是：DATE类型
3、to_date函数的语法格式：to_date('字符串日期','日期格式')
4、关于日期格式：
* 回顾java中的日期格式：
yyyy-MM-dd HH:mm:ss SSS【全日期格式】
* 回顾java中：java.lang.String-->java.util.Date怎么转换？
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date time = sdf.parse("1980-10-11");
* Oracle数据库中的日期格式：
YYYY 年
MM/MON 月【假设月份是七月份，MM表示的话：07，MON表示的话：7月】
DD 日
HH 时
MI 分
SS 秒
* Oracle数据库默认的日期格式是：DD-MON-YY
5、to_date函数的使用案例：
* 创建一张学生表，学生有生日，生日用日期类型
-删除学生表
SQL> drop table t_student; //Oracle和MySQL都支持
SQL> drop table if exists t_student; //Oracle不支持，MySQL支持
-创建表的DDL：
create table t_student(
name varchar(32),
birth date
);
* 向t_student表中插入数据
//错误：'1980-10-11'日期字符串和Oracle默认的日期格式不同，而且'1980-10-11'是字符串类型，类型不匹配。
insert into t_student(name,birth) values('jack','1980-10-11');
//可以：birth是date类型，'11-10月-80'这个字符串日期正好和Oracle数据库默认的日期格式相同
//存在类型自动转换
insert into t_student(name,birth) values('jack','11-10月-80');
//可以：以下使用了手动转换
insert into t_student(name,birth) values('jack',to_date('1980-10-11','YYYY-MM-DD'));
6、to_date函数多数使用在insert操作当中，数据库表中某个字段是日期类型，不能直接将字符串给这个字段，除非
这个字符串的格式恰好和数据库默认的日期格式相同。要不然必须手动调用to_date函数进行类型转换。
------------------------------------------------------------------------------------------------------------------
to_char
1、to_char函数的作用：将DATE转换成具有特定格式的VARCHAR
2、to_char函数的运算结果是：VARCHAR类型
3、to_char函数的语法格式：to_char(日期数据,'日期格式')
4、回顾java中的日期格式化：
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date time = new Date();
String strTime = sdf.format(time);
5、案例：
查询所有学生的生日，要求学生生日显示格式是："2000-10-11"
select name,to_char(birth,'YYYY-MM-DD') as birth from t_student;
NAME BIRTH
---------------------------------------------------------------- -----------
jack 1980-10-11
jack 1980-10-11
SQL> select name,birth from t_student; //存在自动类型转换，按照Oracle默认的日期格式转换
NAME BIRTH
---------------------------------------------------------------- --------------
jack 11-10月-80
jack 11-10月-80
以上SQL语句还可以写成另外一种形式：
select name,to_char(birth,'DD-MON-YY') as birth from t_student;
NAME BIRTH
---------------------------------------------------------------- --------------
jack 11-10月-80
jack 11-10月-80
6、to_char函数多数使用在查询语句当中，以特定格式的字符串展示该日期的时候使用。
------------------------------------------------------------------------------------------------------------------
to_number【了解】
* VARCHAR ---> NUMBER
------------------------------------------------------------------------------------------------------------------
重点：
在Oracle数据库当中怎么获取Oracle数据库的系统时间？
SQL> select sysdate from dual;
SYSDATE
--------------
15-8月 -16
SQL> select to_char(sysdate,'YYYY-MON-DD HH:MI:SS') as nowTime from dual;
NOWTIME
--------------------------------------------------
2016-8月 -15 03:58:16
4、分组函数
分组函数/多行处理函数
1、多行处理函数？
这种函数可以一次处理多行记录，多行记录处理完成之后最终生成一行结果。
2、“分组函数”通常和group by联合使用，group by就是专门分组的。依靠某个字段或者某些字段进行联合分组，
分组之后想计算某个结果，必须使用分组函数。
3、当DQL语句中没有group by语句的话，整个表为一组。
4、重点：所有的分组函数自动忽略空值。
4、分组函数包括：
count 统计
sum 求和
avg 求平均值
max 求最大值
min 求最小值
5、关于count分组函数：
SQL> select count(ename) from emp;
COUNT(ENAME)
------------
12
SQL> select count(comm) from emp;
COUNT(COMM)
-----------
4
SQL> select count(*) from emp;
COUNT(*)
----------
12
面试题：
count(*)和count(具体某个字段)的区别？
* count(字段)表示：该字段中不为空的元素总数
* count(*)表示：和具体的某个字段无关，不是统计字段中不为空的元素总数，而是统计查询结果的总条数。
6、关于SUM函数：
SQL> select sum(comm) from emp;
SUM(COMM)
----------
2200
7、关于avg函数
select avg(sal) from emp;
AVG(SAL)
----------
2077.08333
8、关于max函数
select max(sal) from emp;
9、关于min函数
select min(sal) from emp;
10、重点：
SQL> select ename,sal from emp where sal > min(sal);
select ename,sal from emp where sal > min(sal)
*
第 1 行出现错误:
ORA-00934: 此处不允许使用分组函数
分组是在where条件执行之后才进行的，分组之后，分组函数才能用。
5、分组查询
分组查询
1、分组查询涉及到的语句：
group by
having
group by和having联合使用，没有group by就不能使用having
having是专门对分组之后的数据进行再次过滤的。
2、案例：
* 找出每个工作岗位的总人数【按照工作岗位分组，对每一组进行统计】
SQL> select job,count(*) from emp group by job;
JOB COUNT(*)
------------------ ----------
CLERK 3
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 1
* 找出每个工作岗位的总人数，除MANAGER岗位之外
SQL> select job,count(*) from emp group by job having job <> 'MANAGER';
JOB COUNT(*)
------------------ ----------
CLERK 3
SALESMAN 4
PRESIDENT 1
ANALYST 1
SQL> select job,count(*) from emp where job<>'MANAGER' group by job; //优先考虑此方案
JOB COUNT(*)
------------------ ----------
CLERK 3
SALESMAN 4
PRESIDENT 1
ANALYST 1
原则：能够使用where条件过滤，尽量使用where，除非where无法过滤，要求分组之后才能过滤，此时使用having。
* 找出每个工作岗位的平均薪水，要求显示平均薪水大于1500的数据
select job,avg(sal) from emp where avg(sal) > 1500 group by job; //该语句有语法错误，where后面不能直接跟分组函数
SQL> select job,avg(sal) from emp group by job having avg(sal) > 1500 ;
JOB AVG(SAL)
------------------ ----------
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000
SQL> select job,avg(sal) as avgsal from emp group by job having avgsal > 1500 ;
select job,avg(sal) as avgsal from emp group by job having avgsal > 1500
*
第 1 行出现错误:
ORA-00904: "AVGSAL": 标识符无效
结论：having语句在select语句执行之前执行，以上语句MYSQL可以执行，Oracle报错。Oracle语法严格。
* 找出每个部门的最高薪水？
select
deptno,max(sal)
from
emp
group by
deptno;
DEPTNO MAX(SAL)
---------- ----------
30 2850
20 3000
10 5000
select
ename,deptno,max(sal)
from
emp
group by
deptno;
以上语句在mysql中可以执行，但是执行结果没有意义
以上语句在Oracle数据库中执行报错。Oracle语法严格。
结论：当一个DQL语句中有group by的话，select关键字后面只能跟“分组函数”和“参与分组的字段”
* 可以通过多个字段分组吗？找出每个部门不同工作岗位的人数
select count(*),deptno,job from emp group by deptno,job;
COUNT(*) DEPTNO JOB
---------- ---------- ---------------
1 20 CLERK
4 30 SALESMAN
1 20 MANAGER
1 30 CLERK
1 10 PRESIDENT
1 30 MANAGER
1 10 CLERK
1 10 MANAGER
1 20 ANALYST
----------------------------------------------------------------------------------------------------------------
重点：DQL语句总结
select .. 5
from... 1
where... 2
group by ... 3
having ... 4
order by ... 6
SQL> select ename,sal as salary from emp order by salary;
ENAME SALARY
-------------------- ----------
SMITH 800
JAMES 950
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
FORD 3000
KING 5000
6、连接查询
连接查询
1、什么是连接查询?
2、连接查询根据语法年代分类？
* SQL92
- 表的连接条件和将来数据的过滤条件是在一起的，都在where后面。【结构不清晰】
* SQL99
- 表的连接条件和将来数据的过滤条件是分离的。【结构清晰】
3、连接查询根据连接方式分类？
* 内连接
- 等值连接
- 非等值连接
- 自连接
* 外连接
- 左(外)连接
- 右(外)连接
4、什么是笛卡尔积现象？怎么避免？
案例：找出每一个员工的部门名称，要求显示员工名和对应的部门名？
SQL> select ename,deptno from emp;
ENAME DEPTNO
-------------------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
KING 10
TURNER 30
JAMES 30
FORD 20
MILLER 10
SQL> select deptno,dname from dept;
DEPTNO DNAME
---------- --------------------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
SQL> select e.ename,d.dname from emp e , dept d;
....
48条记录
像以上的查询结果条数是emp表12条记录和dept表中的4条记录进行匹配，最终结果条数是：12*4=48【笛卡尔乘积现象】
避免：添加查询条件
select e.ename,d.dname from emp e , dept d where e.deptno=d.deptno;
ENAME DNAME
-------------------- ------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
KING ACCOUNTING
TURNER SALES
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
5、避免了笛卡尔积现象，连接匹配的次数变少了吗？什么变了？
没有变少，实际上次数还是乘积，只不过记录变少了，合法记录筛选出来了。
6、案例：查询出每一个员工的部门名称，要求显示员工名和部门名【别名】
SQL99语法：内连接中的等值连接
SQL> select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
SQL> select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;
SQL92语法：内连接中的等值连接
SQL> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
ENAME DNAME
-------------------- ------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
KING ACCOUNTING
TURNER SALES
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
7、案例：找出每个员工的薪水等级，要求显示员工名和对应的薪水等级
SQL> select ename,sal from emp;
ENAME SAL
-------------------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
KING 5000
TURNER 1500
JAMES 950
FORD 3000
MILLER 1300
SQL> select grade,losal,hisal from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL99语法：内连接中的非等值连接
SQL> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
SQL> select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
SQL92语法：内连接中的非等值连接
SQL> select e.ename,e.sal,s.grade from emp e , salgrade s where e.sal between s.losal and s.hisal;
ENAME SAL GRADE
-------------------- ---------- ----------
SMITH 800 1
JAMES 950 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
ALLEN 1600 3
TURNER 1500 3
JONES 2975 4
BLAKE 2850 4
CLARK 2450 4
FORD 3000 4
KING 5000 5
8、案例：找出每个员工的上级领导，要求显示员工名对应的领导名
emp a表 员工表
EMPNO ENAME MGR
---------- -------------------- ----------
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7839 KING
7844 TURNER 7698
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
emp b表 领导表
EMPNO ENAME
---------- --------------------
7566 JONES
7698 BLAKE
7782 CLARK
7839 KING
7902 FORD
连接条件：员工表的领导编号 = 领导表的员工编号
SQL99语法：内连接中的自连接
select a.ename empname,b.ename leadername from emp a join emp b on a.mgr = b.empno;
select a.ename empname,b.ename leadername from emp a inner join emp b on a.mgr = b.empno;
SQL92语法：内连接中的自连接
select a.ename empname,b.ename leadername from emp a , emp b where a.mgr = b.empno;
EMPNAME LEADERNAME
-------------------- ---------------
FORD JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
9、案例：找出每个员工的部门名称，要求部门全部显示。
内连接
SQL> select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
ENAME DNAME
-------------------- ----------------------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
KING ACCOUNTING
TURNER SALES
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
SQL92语法：外连接中的右(外)连接
select e.ename,d.dname from emp e , dept d where e.deptno(+) = d.deptno;
SQL92语法：外连接中的左(外)连接
select e.ename,d.dname from emp e , dept d where d.deptno = e.deptno(+);
SQL99语法：外连接中的右(外)连接
select e.ename,d.dname from emp e right outer join dept d on e.deptno=d.deptno;
select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;
SQL99语法：外连接中的左(外)连接
select e.ename,d.dname from dept d left outer join emp e on e.deptno=d.deptno;
select e.ename,d.dname from dept d left join emp e on e.deptno=d.deptno;
ENAME DNAME
-------------------- ------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
KING ACCOUNTING
TURNER SALES
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
OPERATIONS
10、外连接什么时候使用？
a和b两张表连接查询，想将其中一张表的数据全部显示，必须使用外连接。
对方表记录无法与之匹配，自动添加“空”与其匹配。
任何一个左连接都可以有右连接的写法。
任何一个右连接都可以有左连接的写法。
11、案例：找出哪个部门没有员工
select e.ename,d.dname from dept d left join emp e on e.deptno=d.deptno where e.ename is null;
ENAME DNAME
-------------------- -------------
OPERATIONS
12、案例：找出所有员工的上级领导，要求显示员工名与上级领导名。
select a.ename 员工,b.ename 领导 from emp a join emp b on a.mgr = b.empno;
员工 领导
-------------------- -------
FORD JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
select a.ename 员工,b.ename 领导 from emp a left join emp b on a.mgr = b.empno;
员工 领导
-------------------- ------
FORD JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
KING
13、三张表连接：找出每个员工的部门名称以及薪水等级，要求显示员工名、部门名、薪水、薪水等级.
emp e
dept d
salgrade s
select
e.ename,d.dname,e.sal,s.grade
from
emp e
join
dept d
on
e.deptno=d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal;
ENAME DNAME SAL GRADE
-------------------- ---------------------------- ---------- ----------
SMITH RESEARCH 800 1
JAMES SALES 950 1
WARD SALES 1250 2
MARTIN SALES 1250 2
MILLER ACCOUNTING 1300 2
ALLEN SALES 1600 3
TURNER SALES 1500 3
JONES RESEARCH 2975 4
BLAKE SALES 2850 4
CLARK ACCOUNTING 2450 4
FORD RESEARCH 3000 4
KING ACCOUNTING 5000 5
7、子查询
子查询
1、什么是子查询?
DQL语句嵌套到某个SQL语句当中。
2、子查询可以出现在哪里？
select .(select)..
from..(select).
where..(select).
3、案例：找出比平均薪水高的员工，要求显示员工名，薪水
第一步：找出平均薪水
SQL> select avg(sal) from emp;
AVG(SAL)
----------
2077.08333
第二步：找出薪水 > 2077.08333
SQL> select ename,sal from emp where sal > (select avg(sal) from emp);
ENAME SAL
-------------------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
KING 5000
FORD 3000
4、案例：找出每个部门的平均薪水的等级【先找出平均薪水，然后算平均薪水的等级】
第一步：找出每个部门的平均薪水
SQL> select avg(sal) as avgsal,deptno from emp group by deptno;
AVGSAL DEPTNO
---------- ----------
1566.66667 30
2258.33333 20
2916.66667 10
第二步：将以上查询结果看做临时表t，和salgrade s表进行连接，条件：t.AVGSAL between s.losal and s.hisal
select
t.*,s.grade
from
(select avg(sal) as avgsal,deptno from emp group by deptno) t
join
salgrade s
on
t.AVGSAL between s.losal and s.hisal;
AVGSAL DEPTNO GRADE
---------- ---------- ----------
1566.66667 30 3
2258.33333 20 4
2916.66667 10 4
5、案例：【了解select..(select).】
select e.ename, e.deptno, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;
ENAME DEPTNO DNAME
-------------------- ---------- ------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
MARTIN 30 SALES
BLAKE 30 SALES
CLARK 10 ACCOUNTING
KING 10 ACCOUNTING
TURNER 30 SALES
JAMES 30 SALES
FORD 20 RESEARCH
MILLER 10 ACCOUNTING
8、rownum
关于Oracle数据库当中的rownum
1、rownum是什么？
* Oracle数据库中特有的，其它数据库中没有这种机制。
* rownum是行号，行号从1开始，以1递增。
* rownum只在DQL语句当中存在
* rownum是专门为查询结果集生成的行号
* 查询结果集出来之后，再为查询结果集动态生成行号。
2、使用rownum
SQL> select ename,sal,rownum from emp;
ENAME SAL ROWNUM
-------------------- ---------- ----------
SMITH 800 1
ALLEN 1600 2
WARD 1250 3
JONES 2975 4
MARTIN 1250 5
BLAKE 2850 6
CLARK 2450 7
KING 5000 8
TURNER 1500 9
JAMES 950 10
FORD 3000 11
MILLER 1300 12
rownum其实是一个伪字段，在emp表中实际上是不存在的，
在查询结果集生成时rownum出现。
3、研究rownum和表中的记录有绑定关系吗？
SQL> select ename,sal,rownum from emp where ename='KING';
ENAME SAL ROWNUM
-------------------- ---------- ----------
KING 5000 1
rownum和表中的记录之间没有绑定关系。
4、rownum支持哪些操作？
* SQL> select ename,sal from emp where rownum=2; //无查询结果
* SQL> select ename,sal from emp where rownum=1; //可以查询到记录
* SQL> select ename,sal from emp where rownum<5; //可以查询到记录
* SQL> select ename,sal from emp where rownum<=5; //可以查询到记录
* SQL> select ename,sal from emp where rownum>2; //无查询结果
* SQL> select ename,sal from emp where rownum>1; //无查询结果
* SQL> select ename,sal from emp where rownum>=1; //可以查询到记录【没有意义，全部查询了】
结论：
rownum支持：
= 1 和 小于操作
5、找出员工表中前5条记录
select ename,sal from emp where rownum <= 5;
ENAME SAL
-------------------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
6、找出员工表中工资排名在前5名的员工
第一步：按照员工的工资降序排列
select ename,sal from emp order by sal desc;
ENAME SAL
-------------------- ----------
KING 5000
FORD 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
MARTIN 1250
WARD 1250
JAMES 950
SMITH 800
第二步：取出上面临时表中的前5条记录
select t.* from (select ename,sal from emp order by sal desc) t where rownum <= 5;
ENAME SAL
-------------------- ----------
KING 5000
FORD 3000
JONES 2975
BLAKE 2850
CLARK 2450
错误的写法：
select ename,sal from emp where rownum <= 5 order by sal desc;
MYSQL的编写方式：
select ename,sal from emp order by sal desc limit 0,5;
select ename,sal from emp order by sal desc limit 5;
7、找出工资排名在【3-9】名的员工
第一步：先按照工资降序排序
select ename,sal from emp order by sal desc;
ENAME SAL
-------------------- ----------
KING 5000
FORD 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
MARTIN 1250
WARD 1250
JAMES 950
SMITH 800
第二步：将上面的查询结果当做临时表t，然后取前9条记录
SQL> select t.* from (select ename,sal from emp order by sal desc) t where rownum <= 9;
ENAME SAL
-------------------- ----------
KING 5000
FORD 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
WARD 1250
select t.*,rownum as linenum from (select ename,sal from emp order by sal desc) t where rownum <= 9;
ENAME SAL LINENUM
-------------------- ---------- ----------
KING 5000 1
FORD 3000 2
JONES 2975 3
BLAKE 2850 4
CLARK 2450 5
ALLEN 1600 6
TURNER 1500 7
MILLER 1300 8
WARD 1250 9
第三步：将上面的查询结果当做临时表x，取x.linenum >= 3
select
x.*
from
(select t.*,rownum as linenum from (select ename,sal from emp order by sal desc) t where rownum <= 9) x
where
x.linenum >= 3;
ENAME SAL LINENUM
-------------------- ---------- ----------
JONES 2975 3
BLAKE 2850 4
CLARK 2450 5
ALLEN 1600 6
TURNER 1500 7
MILLER 1300 8
WARD 1250 9
有多种方式可以实现以上功能，常用的就是以上三层DQL语句的嵌套。
小窍门：别名【rownum as linenum】
8、mysql通用的分页SQL语句
找出工资排名在【3-9】名的员工
select ename,sal from emp order by sal desc limit 2,7;
每页显示3条记录
第1页：[0,3]
第2页：[3,3]
第3页：[6,3]
每页显示pageSize条记录
第pageNo页：[ (pageNo - 1) * pageSize , pageSize ]
select
*
from
tablename
order by
colmuname desc/asc
limit
(pageNo - 1) * pageSize , pageSize;
9、Oracle通用的分页SQL语句
找出工资排名在【3-9】名的员工
select
x.*
from
(select
t.*,rownum as linenum
from
(select
ename,sal
from
emp
order by
sal desc) t
where
rownum <= 9) x
where
x.linenum >= 3;
每页显示3条记录
第1页：(0-3]
第2页：(3-6]
第3页：(6-9]
每页显示pageSize条记录
第pageNo页：( (pageNo - 1) * pageSize ~ pageNo * pageSize ]
Oracle标准的通用分页SQL语句：
select
x.*
from
(select
t.*,rownum as linenum
from
(业务SQL语句) t
where
rownum <= pageNo * pageSize) x
where
x.linenum > (pageNo - 1) * pageSize ;
9、rowid
rowid
1、rowid是Oracle数据库中特有的，在其它数据库中没有。rowid【行的标识】
2、rowid是一张表中真正存在的一个字段。并且rowid是和表中记录存在绑定关系的。每一张表都有rowid字段。
3、rowid是：行的id，是一张表中某条记录的在硬盘上存储的真实物理地址。通过rowid查询表中的记录不需要
对表进行扫描，直接从硬盘上读取该数据，因为有硬盘的真实物理地址。
4、案例：
SQL> select ename,sal,rowid from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ROWID
------ -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ----------------
7369 SMITH CLERK 7902 17-12月-80 800 20 AAAE5SAABAAAK+hAAA
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 AAAE5SAABAAAK+hAAB
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 AAAE5SAABAAAK+hAAC
7566 JONES MANAGER 7839 02-4月 -81 2975 20 AAAE5SAABAAAK+hAAD
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 AAAE5SAABAAAK+hAAE
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 AAAE5SAABAAAK+hAAF
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 AAAE5SAABAAAK+hAAG
7839 KING PRESIDENT 17-11月-81 5000 10 AAAE5SAABAAAK+hAAH
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 AAAE5SAABAAAK+hAAI
7900 JAMES CLERK 7698 03-12月-81 950 30 AAAE5SAABAAAK+hAAJ
7902 FORD ANALYST 7566 03-12月-81 3000 20 AAAE5SAABAAAK+hAAK
7934 MILLER CLERK 7782 23-1月 -82 1300 10 AAAE5SAABAAAK+hAAL
分析以下两条SQL语句的执行效率：
select * from emp where ename = 'FORD'; //这条语句会对ename字段进行全表扫描。
select * from emp where rowid = 'AAAE5SAABAAAK+hAAK'; //这条语句不会进行表的全表扫描，直接通过物理地址定位。
5、rowid和以后要讲解的index【索引】有关系。
* 索引之所以会提高程序的执行效率，就是因为索引底层使用了rowid机制。
6、面试题：使用delete语句删除表中重复的记录。
SQL> select * from t_user;
NAME
------------------------------
jack
jack
jack
jack
jack
zhangsan
zhangsan
zhangsan
zhangsan
SQL> select name,rowid from t_user;
NAME ROWID
---------------------------------------------------------------- ------------------
jack AAAE6+AABAAALAJAAA
jack AAAE6+AABAAALAJAAB
jack AAAE6+AABAAALAJAAC
jack AAAE6+AABAAALAJAAD
jack AAAE6+AABAAALAJAAE
zhangsan AAAE6+AABAAALAJAAF
zhangsan AAAE6+AABAAALAJAAG
zhangsan AAAE6+AABAAALAJAAH
zhangsan AAAE6+AABAAALAJAAI
delete from t_user where rowid not in(select min(rowid) from t_user group by name);
10、union,minus
union【将查询结果相加】
1、查询工作岗位是MANAGER和SALESMAN的员工
SQL> select ename,job from emp where job='MANAGER' or job='SALESMAN';
SQL> select ename,job from emp where job in('MANAGER','SALESMAN');
ENAME JOB
-------------------- ------------------
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
TURNER SALESMAN
SQL> select ename,job from emp where job='MANAGER'
2 union
3 select ename,job from emp where job='SALESMAN';
ENAME JOB
-------------------- ------------------
ALLEN SALESMAN
BLAKE MANAGER
CLARK MANAGER
JONES MANAGER
MARTIN SALESMAN
TURNER SALESMAN
WARD SALESMAN
重点：调优策略
使用union可以将两张不同的表中的记录进行拼接/组合。达到表连接的效果。
这种方式还是基于单表查询，不是表的连接查询，比表的连接查询效率高一些。
union的使用注意事项：
要求两个查询结果集的字段数量相同，并且数据类型对应相同。【要求比较严格】
SQL> select ename,sal from emp
2 union
3 select dname from dept;
select ename,sal from emp
*
第 1 行出现错误:
ORA-01789: 查询块具有不正确的结果列数
SQL> select ename,sal from emp
2 union
3 select sal,ename from emp;
select ename,sal from emp
*
第 1 行出现错误:
ORA-01790: 表达式必须具有与对应表达式相同的数据类型
SQL> select ename,sal from emp
2 union
3 select dname,deptno from dept;
ENAME SAL
---------------------------- ----------
ACCOUNTING 10
ALLEN 1600
BLAKE 2850
CLARK 2450
FORD 3000
JAMES 950
JONES 2975
KING 5000
MARTIN 1250
MILLER 1300
OPERATIONS 40
RESEARCH 20
SALES 30
SMITH 800
TURNER 1500
WARD 1250
---------------------------------------------------------------------------------------------------------------
minus【将查询结果相减：mysql中没有】
注意：minus和union的语法完全相同，使用注意事项也是完全相同，只不过minus只负责相减
SQL> select ename,sal from emp where sal < 3000
2 minus
3 select ename,sal from emp where sal < 1500;
ENAME SAL
-------------------- ----------
ALLEN 1600
BLAKE 2850
CLARK 2450
JONES 2975
TURNER 1500
SQL> select ename,sal from emp
2 minus
3 select dname,deptno from dept;
ENAME SAL
---------------------------- ----------
ALLEN 1600
BLAKE 2850
CLARK 2450
FORD 3000
JAMES 950
JONES 2975
KING 5000
MARTIN 1250
MILLER 1300
SMITH 800
TURNER 1500
WARD 1250
A结果集 - B结果集 = 【减去的A结果集和B结果集的交集部分，并且最终剩余的记录是A结果集中的记录】
11、表的创建比较完整的示例
1、需求：
请设计数据库表，来描述学生选课信息。
2、分析：
一个学生可以选择多门课程
一门课程可以被多个学生选择
学生和课程之间的关系是一个多对多的关系
通常多对多的设计一般采用三张表进行存储和描述。
3、具体设计：
学生表
tbl_student
id(pk) name
---------------------
1 a
2 b
3 c
4 d
5 e
6 f
7 x
8 y
课程表
tbl_course
id(pk) name
----------------------
1 语文
2 数学
3 java
4 mysql
5 oracle
6 c
7 c++
8 .net
学生课程关系表
tbl_student_course_relation
id(pk) studentId(fk) courseId(fk) 【studentId,courseId需要联合添加唯一性约束】
-------------------------------------------------
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 1 6
7 1 7
8 2 3
9 2 4
10 2 5
11 2 6
12 2 7
13 2 8
14 2 1
4、以上的设计具体的SQL脚本
----------------------------------------------SQL文-----------------------------------------------------------------
//列级约束
create table tbl_student(
id number(10) primary key,
name varchar2(32)
);
//表级约束【同时可以给约束起名】
create table tbl_student(
id number(10),
name varchar2(32),
constraint tbl_student_pk_id primary key(id)
);
//系统表
//user_tables 【描述用户表信息的一张系统表】
//user_constraints【描述约束对象信息的一张系统表】
-----------------------------------------------------------------------------------------------------------------
drop table tbl_student_course_relation;
drop table tbl_student;
drop table tbl_course;
create table tbl_student(
id number(10),
name varchar2(32),
constraint tbl_student_pk_id primary key(id)
);
create table tbl_course(
id number(10),
name varchar2(32),
constraint tbl_course_pk_id primary key(id)
);
create table tbl_student_course_relation(
id number(10),
studentId number(10),
courseId number(10),
constraint a primary key(id),
constraint b foreign key(studentId) references tbl_student(id),
constraint c foreign key(courseId) references tbl_course(id),
constraint d unique(studentId,courseId)
);
insert into tbl_student(id,name) values(1,'a');
insert into tbl_student(id,name) values(2,'b');
insert into tbl_student(id,name) values(3,'c');
insert into tbl_student(id,name) values(4,'d');
insert into tbl_student(id,name) values(5,'e');
insert into tbl_student(id,name) values(6,'f');
insert into tbl_student(id,name) values(7,'x');
insert into tbl_student(id,name) values(8,'y');
insert into tbl_course(id,name) values(1,'java');
insert into tbl_course(id,name) values(2,'c');
insert into tbl_course(id,name) values(3,'php');
insert into tbl_course(id,name) values(4,'c++');
insert into tbl_course(id,name) values(5,'c#');
insert into tbl_course(id,name) values(6,'ASP');
insert into tbl_course(id,name) values(7,'JSP');
insert into tbl_course(id,name) values(8,'Servlet');
insert into tbl_student_course_relation(id,studentId,courseId) values(1,1,1);
insert into tbl_student_course_relation(id,studentId,courseId) values(2,1,2);
insert into tbl_student_course_relation(id,studentId,courseId) values(3,1,3);
insert into tbl_student_course_relation(id,studentId,courseId) values(4,1,4);
insert into tbl_student_course_relation(id,studentId,courseId) values(5,1,5);
insert into tbl_student_course_relation(id,studentId,courseId) values(6,1,6);
insert into tbl_student_course_relation(id,studentId,courseId) values(7,1,7);
insert into tbl_student_course_relation(id,studentId,courseId) values(8,2,1);
insert into tbl_student_course_relation(id,studentId,courseId) values(9,2,3);
insert into tbl_student_course_relation(id,studentId,courseId) values(10,2,4);
insert into tbl_student_course_relation(id,studentId,courseId) values(11,2,5);
insert into tbl_student_course_relation(id,studentId,courseId) values(12,2,6);
insert into tbl_student_course_relation(id,studentId,courseId) values(13,2,7);
insert into tbl_student_course_relation(id,studentId,courseId) values(14,2,8);
commit;
select * from tbl_student;
select * from tbl_course;
select * from tbl_student_course_relation;
----------------------------------------------SQL文-----------------------------------------------------------------
案例：知道学生的id=1，请找出该学生所选课程名称，要求显示学生名和课程名
tbl_student s
tbl_course c
tbl_student_course_relation sc
select
s.name,c.name
from
tbl_student_course_relation sc
join
tbl_student s
on
sc.studentId = s.id
join
tbl_course c
on
sc.courseId = c.id
where
s.id = 1;
NAME NAME
---------------------------------------------------------------- --------
a java
a c
a php
a c++
a c#
a ASP
a JSP
12、关于表中记录的删除
关于表中记录的删除
1、删除表中记录包括两种形式：
* 第一种方式：使用delete语句【DML】
使用delete语句删除表中的记录的时候，删除效率较低，
delete删除数据之后可以回滚，这说明delete语句删除的时候
只是将格子中的数据移除掉了，但是数据占用的空间并没有释放。
* 第二种方式：使用truncate语句【DDL】
使用truncate语句删除表中的记录，直接将表截断，并且
释放所有的控件，truncate语句删除的数据无法回滚再恢复，
但是这个语句删除效率很高。适合删除大表中的数据。
2、删除数据：
delete from tbl_student;
truncate table tbl_student; 表被截断
13、索引
索引【index】
1、什么是索引？什么作用？
* 索引对应的英语单词：index
* 索引相当于一本书的目录，为了提高检索效率而存在的一种机制。
2、怎么创建索引？
SQL> create index emp_ename_index on emp(ename); //给emp表中的ename字段添加一个索引，索引起名emp_ename_index
索引已创建。
3、怎么删除索引？
SQL> drop index emp_ename_index;
索引已删除。
4、索引也有一张系统表：user_indexes
user_tables 系统表描述用户表
user_constraints 系统表描述约束信息
user_indexes 系统表描述索引信息
5、索引也是存储在硬盘当中的，也是需要维护的，所以字段上不能随便添加索引。
6、什么时候字段上应该添加索引呢？
* 大数据量
* 该字段很少的DML操作
* 该字段经常出现在where条件当中
7、索引的实现原理？
7.1、只要知道rowid，通过rowid的检索效率是极高的
SQL> select * from emp where rowid='AAAE5SAABAAAK+hAAC';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7.2、当ename字段上没有添加索引的时候，以下的SQL语句是怎么执行的呢？
SQL> select * from emp where ename = 'FORD';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-12月-81 3000 20
以上SQL语句在执行的时候，进行全表扫描，扫描emp表的ename字段中的所有记录。
7.3、当ename字段上添加了索引，这个时候在硬盘的某个位置上有多个了一个索引对象。这个索引专门是为
ename字段服务的，相当于生成了ename字段的一个目录。
SQL> select empno,ename,sal,job,rowid from emp;
EMPNO ENAME SAL JOB ROWID
---------- -------------------- ---------- ------------------ ------------------
7369 SMITH 800 CLERK AAAE5SAABAAAK+hAAA
7499 ALLEN 1600 SALESMAN AAAE5SAABAAAK+hAAB
7521 WARD 1250 SALESMAN AAAE5SAABAAAK+hAAC
7566 JONES 2975 MANAGER AAAE5SAABAAAK+hAAD
7654 MARTIN 1250 SALESMAN AAAE5SAABAAAK+hAAE
7698 BLAKE 2850 MANAGER AAAE5SAABAAAK+hAAF
7782 CLARK 2450 MANAGER AAAE5SAABAAAK+hAAG
7839 KING 5000 PRESIDENT AAAE5SAABAAAK+hAAH
7844 TURNER 1500 SALESMAN AAAE5SAABAAAK+hAAI
7900 JAMES 950 CLERK AAAE5SAABAAAK+hAAJ
7902 FORD 3000 ANALYST AAAE5SAABAAAK+hAAK
EMPNO ENAME SAL JOB ROWID
---------- -------------------- ---------- ------------------ ------------------
7934 MILLER 1300 CLERK AAAE5SAABAAAK+hAAL
在硬盘的另一个空间上专门为ename字段维护了一个索引对象：
例如，执行这样的SQL语句：
create index emp_ename_index on emp(ename);
以下的记录就是这个索引对象：emp_ename_index
索引是自动排序的，并且索引底层会进行“分区”
ENAME ROWID
-------------------- ------------------
ALLEN AAAE5SAABAAAK+hAAB
BLAKE AAAE5SAABAAAK+hAAF
CLARK AAAE5SAABAAAK+hAAG
FORD AAAE5SAABAAAK+hAAK
JAMES AAAE5SAABAAAK+hAAJ
JONES AAAE5SAABAAAK+hAAD
KING AAAE5SAABAAAK+hAAH
MARTIN AAAE5SAABAAAK+hAAE
MILLER AAAE5SAABAAAK+hAAL
SMITH AAAE5SAABAAAK+hAAA
TURNER AAAE5SAABAAAK+hAAI
WARD AAAE5SAABAAAK+hAAC
当再执行以下SQL语句的时候，是怎么执行的呢？
发现ename字段有相关的索引，这个时候找到emp_ename_index索引对象。
发现ename='MILLER'，先在索引上找到'M'区进行扫描范围的缩小。快速
找到'MILLER'之后，获取'MILLER'后面的rowid是'AAAE5SAABAAAK+hAAL'
select * from emp where ename = 'MILLER';
找到rowid之后接下来以上的SQL语句就变成：
select * from emp where ename = 'AAAE5SAABAAAK+hAAL';
8、带有主键约束的字段，自动添加索引。尽量根据主键查询，效率很高。
14、视图
视图
1、视图对应的英语单词是view
2、视图也有一张系统表：user_views
3、创建视图
SQL> create view empview as select ename,sal from emp;
create view empview as select ename,sal from emp
*
第 1 行出现错误:
ORA-01031: 权限不足
怎么查看当前用户拥有的权限呢？
Oracle数据库当中提供了一张系统表：session_privs
session_privs表中描述了当前用户拥有哪些权限。
SQL> select * from session_privs;
PRIVILEGE
---------------------------------
CREATE SESSION 连接数据库的权限
UNLIMITED TABLESPACE 使用表空间的权限
CREATE TABLE 创建表的权限
CREATE CLUSTER
CREATE SEQUENCE 创建序列对象的权限
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE 创建索引的权限
已选择10行。
授权：grant ... to...【管理员才能授权】
SQL> grant CREATE VIEW to scott;
授权成功。
撤销权限：revoke ... from...【管理员才能撤销权限】
SQL> revoke create view from scott;
撤销成功。
SQL> create view empview as select ename,sal from emp;
视图已创建。
4、删除视图
SQL> drop view empview;
视图已删除。
5、视图有什么作用？
* 隐藏表的实现细则。
* 一般情况下都是客户的DBA创建视图对象，将视图对象提供给软件公司的程序员，
程序员只能面向视图进行CRUD操作，程序员根本不知道原表中的数据以及表结构信息。
原表得到保护。
6、例子：
create view sview as select id a , name b from tbl_student;
视图对象只能指向一条DQL语句。
但是通过对视图中的数据进行增删改，视图指向的那张表中的数据也会跟随着进行增删改。
15、序列-sequence
序列-sequence
1、序列是Oracle数据库当中特有的，专门用来维护一个自增的数字，默认情况下从1开始，以1递增。
类似于mysql当中的auto_increment
2、创建序列对象
create sequence myseq;
3、删除序列对象
drop sequence myseq;
4、序列对象也有一张系统表：user_sequences
规律：user_XXXs
5、序列对象都有两个属性：
* nextval：从序列当中获取下一个值
* currval：从序列对象当中获取当前值
6、案例：
create table t_customer(
id number(10) primary key,
name varchar2(32)
);
create sequence myseq;
insert into t_customer(id,name) values(myseq.nextval,'jack1');
insert into t_customer(id,name) values(myseq.nextval,'jack2');
insert into t_customer(id,name) values(myseq.nextval,'jack3');
insert into t_customer(id,name) values(myseq.nextval,'jack4');
insert into t_customer(id,name) values(myseq.nextval,'jacka');
insert into t_customer(id,name) values(myseq.nextval,'jackb');
insert into t_customer(id,name) values(myseq.nextval,'jackc');
commit;
select * from t_customer;
7、注意：Oracle中的sequence是一个共享的对象。所以有的时候会出现断号现象，
但是断号不是问题，因为主键只要不重复即可，没必要连续。
8、创建一个完整的序列对象：
create sequence my_seq_01
start with 100
minvalue 100
maxvalue 1000
increment by 10 #每次增加10
cycle
cache 10;
16、常用的DBA命令
常用的DBA命令：
1、系统表：
user_tables
user_indexes
user_views
user_sequences
user_constraints
user_objects
session_privs
2、显示当前用户
show user;
这不是一条SQL语句，只是Oracle数据库sqlplus当中的一个命令
3、切换用户：
conn system/bjpowernode@IP/全局数据库名
4、java语言连接Oracle数据库的信息：
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:XE
user=scott
password=tiger
5、查看所有的用户：
SQL> show user;
USER 为 "SCOTT"
SQL> select username from dba_users;
select username from dba_users
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
注意：scott是非管理员用户，是普通的Oracle数据库用户，没有dba_users表格，只有系统管理员才有
6、删除用户，以及该用户所有级联的对象：
SQL> drop user scott cascade;
7、给用户加锁
alter user scott account lock;
8、给用户解锁
alter user scott account unlock;
9、修改用户密码
alter user scott identified by tiger123;
10、使用imp和exp导入和导出数据：
* imp和exp必须在dos窗口中完成，不能登录上数据库去完成
* exp scott/tiger file=D:\EMP.DMF tables=emp,dept,salgrade
* imp scott/tiger file=D:\EMP.DMF
17、为epay-易支付项目准备数据库
【以下所有操作必须是管理员】
1、创建数据库【这一步省略了，直接使用已有的数据XE】
2、创建用户epay，密码bjpowernode
create user epay identified by bjpowernode;
3、创建表空间，给表空间起名epay_tablespace
create tablespace epay_tablespace datafile 'C:\oraclexe\app\oracle\oradata\XE\EPAY.DBF' size 50m;
4、让epay这个用户去使用epay_tablespace表空间
alter user epay default tablespace epay_tablespace;
5、授权
grant
create session,
create table,
create sequence,
create indextype,
unlimited tablespace
to
epay;
18、数据库设计三范式
数据库设计三范式
1、第一范式：每一张表都应该有主键，并且每一个字段都是原子性不可再分。
2、第二范式：所有的非主键字段应该完全依赖主键，不能产生部分依赖。
3、第三范式：所有的非主键字段应该直接依赖主键，不能产生传递依赖。
4、数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，
有时可能会拿冗余换速度，最终目的要满足客户需求。
5、关于数据库表的设计：
* 一对一
- 第一种方案：主键共享原则
t_husband
id(pk) name
--------------------
1 张三
t_wife
id(pk,fk) name
---------------------
1 李四
第二种方案：外键唯一原则【将一对一看做一对多，在多的那一方加unique约束】
t_husband
id(pk) name wid(fk+unqiue)
---------------------------------------
1 张三 1
2 王五 2
t_wife
id(pk) name
---------------------
1 李四
2 赵六
* 一对多
- 在多的一方添加外键约束，外键引用一的一方的主键值。
- 两张表
* 多对多
- 三张表
- 学生表、课程表、学生课程关系表
学习
1、oracle 简单sql语句、
-- Oracle中的建表语句
-- Oracle中的主要的数据类型
-- 1.字符型
-- varchar(10) 定长的字符型数据
-- char(2) 定长的字符型数据
-- varchar2(20) 变长的字符型数据
-- 2.数值型
-- numbers(4) 不带小数点的数值
-- number(8,2) 数据的总长度是8位,小数点后占两位
-- 3.日期型
-- date
-- 删除表的语句
-- create drop 都属于DDL语句(数据定义语言)
drop table users;
create table users(
id number(4) primary key,
name varchar2(10),
password varchar2(10),
sex char(2),
addr varchar2(20),
birthday date,
sql number(8,2)
);
-- 查询表的结构, 只能在命令行模式下使用
desc users;
-- 查询表中的数据
select * from users;
-- sql语句是不区分大小写,包括登录的用户名,密码都是不区分大小写的
SelEct * From users;
-- select 语句的使用
-- 从表中查询部分的字段
select empno,ename,sal,comm from scott.emp;
-- 在查询过程中,对于数值型的数据,可以执行+,-,*,/运算
-- 可以给查询字段起别名,有不同的方式 可以不用""，可以用""，可以用as，可以不用as
select empno "员工编号",ename 员工姓名,sal as "月薪",sal*12 as "年薪" from emp;
-- 在算术表达式中出现null, 得到的结果就是null, null不等于0.0
select empno "员工编号",ename "员工姓名", sal "月薪", sal*12 "年薪",comm "奖金", (sal*12 + comm) "年收入" ,(sal*(12 + comm)) "年收入1"
from emp;
-- 用 || 乐园把两列或多列查询结果合并到一起
select empno, ename, job,empno || ename || job "员工信息" from emp;
-- 在连接表达式中出现字符数据,字符型的数据必须要使用''
-- 在连接表达式中出现null,就是原来的字符型数据
select empno || ' 的经理是 ' || mgr "从属关系" from emp;
-- 任何的类型都支持null
select empno || ename || job || hiredate from emp;
select empno "EmpNo" from emp;
-- 对于日期型的数据可以使用 +, -运算符
-- 1.一个日期 +- 一个数值(就是+-一个天数),得到一个新的日期
-- 2.两个日期型的数据相减,得到的是两者之间相差的天数
-- 两个日期型的数据不能相加,日期型的数据不能进行乘除运算
select empno,ename,hiredate "雇佣日期", (hiredate + 90) "转正日期" from emp;
select empno,ename,hiredate "转正日期", (hiredate - 90) "雇佣日期" from emp;
-- sysdate 取得当前的日期
select empno,ename,(sysdate - hiredate)/365 "工作的年限" from emp;
-- 去除单列重复的数据
select distinct deptno from emp;
-- 去除多列重复的数据
select distinct job,deptno from emp;
--使用where子句对数据进行过滤
--对于字符型的数据和日期型的数据必须使用''
select * from emp where deptno = 10;
-- 字符型的数据对大小写是敏感的
select * from emp where ename = 'SMITH';
-- 对于日期型的数据,格式是敏感的,使用日期型数据的格式是DD-MM-YYYY (日-月-年)
select * from emp where hiredate = '17-2-1980';
--改变当前会话中的日期格式
alter session set nls_date_format= "YYYY-MM-DD HH:MI:SS";
select * from emp where hiredate = '1980-12-17';
-- 在查询条件中使用其他的比较运算符
select * from emp where sal >= 2000;
select * from emp where sal != 3000.00;
select * from emp where sal <> 3000.00;
-- 使用between ... and
select * from emp where sal between 2000 and 3000;
-- 在oracle中用and 来执行&&的功能
select * from emp where sal >= 2000 and sal <= 3000;
-- 使用in
select * from emp where job in('MANAGER','CLERK');
-- 在oracle中用or来执行 || 的功能
select * from emp where job = 'MANAGER' or job = 'CLERK';
-- like 用于字符型数据的查询,可以执行模糊查询
select * from emp where ename like '%A%';
-- %表示0个或多个任意的字符
select * from emp where ename like 'A%';
-- _表示一个字符
select * from emp where ename like '_A%';
-- escape 指定转义符为 ‘\’
select * from emp where ename like '%\%%' escape '\';
-- 对于空值,要使用is null进行比较
select * from emp where comm is null;
-- 使用not 运算符
select * from emp where sal not between 2000 and 3000;
select * from emp where sal <= 2000 or sal >= 3000;
select * from emp where job not in('MANAGER','CLERK');
select * from emp where job != 'MANAGER' and job != 'CLERK';
select * from emp where comm is not null;
-- not and or 的运算顺序
select * from users where name = 'admin' and password = '123456';
-- admin' OR 'X'='X'
-- sql注入攻击
-- 下面的执行顺序为: 'X' = 'X' and password = '45455' 再执行name = 'admin' or ('X' = 'X' and password = '45455')
select * from users where name = 'admin' or 'X' = 'X' and password = '45455';
--对查询后的数据进行排序,使用order by 子句,默认升序排列
select * from emp where sal > 2000 order by sal;
-- 用指定的字段显示的指定升序排序
select * from emp order by sal asc;
-- 用指定的字段显示的指定降序排序
select * from emp order by sal desc;
-- 可以按照多个字段进行排序
select * from emp order by deptno,sal;
-- 使用字段的别名来进行排序
select empno,ename,(sal * 12) "年薪" from emp order by "年薪";
2、oracle函数
-- Oracle函数
-- 单行函数 每次取一条记录,作为函数的参数,得到这条记录对应的单个结果
select ename, length(ename) from emp;
-- 多行函数: 一次性把多条记录当作参数输入给函数,得到多条记录对应的单个结果
select max(sal) from emp;
-- 字符函数
-- lower转换为小写
-- upper转换为大写
-- initcap转换为首字符大写
-- concat连接字符串
-- substr截取字符串 substr(ename,1,2),从第一个字符开始,截取2个
-- instr 字符在字段包含的位置
-- lpad(sal,10,'*') sal左边用*补全共10位，
-- rpad(sal,10,'#') sal右边用#补全共10位
-- replace(ename,'A','a') 把ename里面的A替换成a
-- trim 去除空格
select * from emp where lower(ename) = 'smith';
select * from emp where ename = upper('smith');
select ename,initcap(ename) from emp where initcap(ename) = 'Smith';
select empno || ename,concat(empno,ename) from emp;
select ename,substr(ename,1,2) from emp;
select ename,instr(ename,'A') from emp;
select ename,replace(ename,'A','a') from emp;
select sal,lpad(sal,10,'*'),rpad(sal,10,'#') from emp;
-- 数值函数
-- round四舍五入
-- trunc 截取
-- mod 求余
select round(45.943,2) "小数点后两位",
round(45.943,0) "个位",
round(45.942,-1) "十位"
from sys.dual;
select trunc(45.943,2) "小数点后两位",
trunc(45.943,0) "个位",
trunc(45.942,-1) "十位"
from sys.dual;
select ename,sal, mod(sal,300)
from emp where empno = 7369;
-- 日期函数
-- last_day(hiredate) 当前月的最后一天
select sysdate from sys.dual;
select * from emp where hiredate = '20-2月-1981';
select empno,sysdate,hiredate, round((sysdate - hiredate)/365,0) from emp;
select empno,sysdate,hiredate, (sysdate - hiredate)/30,months_between(sysdate,hiredate) from emp;
select empno,hiredate "雇佣日期", (hiredate + 90) "粗略的转正日期",add_months(hiredate,3) "精确的转正日期" from emp;
select sysdate 当时日期,
next_day(sysdate,'星期一') 下周星期一,
next_day(sysdate,'星期二') 下周星期二,
next_day(sysdate,'星期三') 下周星期三,
next_day(sysdate,'星期四') 下周星期四,
next_day(sysdate,'星期五') 下周星期五,
next_day(sysdate,'星期六') 下周星期六,
next_day(sysdate, '星期日') 下周星期日
from dual;
select ename,hiredate,last_day(hiredate) from emp;
-- 转换函数
-- 转换有两种方式,隐式转换,手动转换
-- to_char(hiredate,'YYYY-MM-DD') 日期转换为年月日字符串
-- to_char(hiredate,'YYYY-MM-DD HH24:MI:SS AM' 日期转换为年月日时分秒字符串
-- to_char(hiredate,'YYYY-MM-DD HH24:MI:SS AM DAY' 日期转换为年月日时分秒字符串 星期几
select * from emp where deptno = '30';
-- to_char 把日期转换为字符
select empno,ename,hiredate,to_char(hiredate,'YYYY-MM-DD HH24:MI:SS AM') from emp where to_char(hiredate,'YYYY-MM-DD') = '1981-02-22';
-- to_char把数值转换为字符
select sal,to_char(sal,'$999,999.00'),to_char(sal,'L000,000.00') from emp where empno = 7369;
-- to_number 把字符型的数据转为数值
select to_number('$800.00','$999,999.00') from sys.dual;
--to_date,把字符型的数据转为日期型的数据
select * from emp where hiredate = to_date('1981-2-20','YYYY-MM-DD');
--其他函数
-- 在算术表达式中出现null,得到的结果就是null
select empno,ename,sal,comm,(sal*12 + comm) "年收入1",(sal*12 + nvl(comm,0)) "年收入2" from emp;
select ename,job,hiredate,nvl(job,'还没有工作') from emp where empno = 7654;
select ename,job,hiredate,nvl(hiredate,'1-1月-1987') from emp where empno = 7654;
-- expr部位null，返回expr1,为null,返回expr2
select ename,job,nvl2(job,'有工作','没工作') from emp where empno = 7654;
-- nullif(expr1,rxpr2) 比较两个表达式,如果相等返回空值,如果不等返回第一个表达式
select ename,job,nullif(length(ename),length(ename)),nullif(length(ename),length(job)) from emp;
--case 实现 if..else if..的功能
select ename,job,sal,
case job
when 'CLERK' then
1.10 * sal
when 'MANAGER' then 1.3 * sal
when 'SALESMAN' then1.45 * sal
else sal
end as "修订工资数"
from emp
where ename = 'SMITH'
-- decode 实现if else
select ename,job,decode(job,
'CLERK',
sal * 1.10,
'MANAGER',
sal * 1.30,
'SALESMAN',
sal * 1.4,
sal
) as "修订工资数"
from emp where ename = 'SMITH';
3、oracle 组函数
-- 组函数,就是我们前面提到的多行函数
select max(sal) from emp;
-- AVG,SUM只能针对数值型的数据
-- MAX,MIN,COUNT可以针对任何类型的数据
select max(sal),min(sal),avg(sal),sum(sal) from emp;
select max(ename),min(ename) from emp;
select max(hiredate),min(hiredate) from emp;
-- count有两种用法
-- 1.count(*) 查询数据的总条数
-- 2.count(字段),这种清空下,忽略空值
select count(*) from emp;
select count(comm) from emp;
-- 所有的组函数都是忽略空值的
select sum(sal),avg(comm),count(comm),sum(comm)/count(comm) from emp;
--按照人数计算平均奖金
select sum(comm)/count(*),avg(nvl(comm,0)) from emp;
--对数据进行分组后,使用组函数
-- 1.出现在查询列表中字段,要么出现在组函数中,要么出现在group by子句中
-- 2.也可以只出现在group by中
select deptno, max(sal) from emp group by deptno;
-- 下面的这句无法执行,因为job没有出现在组函数或者group by子句中
select deptno,job,max(sal) from emp group by deptno;
--按照多个字段进行分组
select deptno,job,max(sal) from emp group by deptno,job order by deptno;
-- 下面这句也不行,没有deptno没有出现在组函数或者group by子句中
select deptno,max(sal) from emp;
-- 要对分组以后的数据进行过滤,过滤>=3000的记录,不能使用where子句,而是要使用having子句
-- 下面这句报错,分组后进行过滤 不能使用where
select max(sal) from emp
where max(sal) >= 3000
group by deptno;
-- 先分组,分组后再过滤
select deptno,max(sal) from emp
group by deptno having max(sal) >= 3000 order by deptno;
-- 首先用where对数据过滤,过滤后的数据用group by分组,分组后的数据用having再过滤,过滤后的数据用order by排序
select deptno,max(sal) from emp where deptno is not null group by deptno having max(sal) >= 3000 order by deptno;
-- 组函数也可以嵌套
-- 在组函数嵌套的时候,必须要使用group by
-- 组函数最多能嵌套两层
select max(sal) from emp where deptno is not null group by deptno;
select max(max(sal)) from emp where deptno is not null group by deptno;
-- 下面这句不能执行, 因为嵌套组函数中,没有使用group by
select max(max(sal)) from emp where deptno is not null;
4、oracle 多表查询
-- 多表查询: 用单个的select语句从多个表中查询相关的数据
-- 在多表查询的时候,如果没有加入等值条件,会产生笛卡尔乘积
select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e,dept d;
select e.empno,e.ename,e.deptno,d.deptno,d.dname,d.loc from emp e, dept d where e.deptno = d.deptno;
-- 多表查询分为不同的标准
-- ================sql 1992老标准================================
-- 1.等值查询,在父子表的关系上,用 = 来连接两个表的两个字段或多个表的多个字段
-- 等值查询只能查询两个表中 -- 对应的数据,该部门有员工,该员工有所属部门
-- N个表的等值查询,需要N-1个等值条件
select e.empno,e.ename,e.deptno,d.deptno,d.dname,d.loc from emp e, dept d where e.deptno = d.deptno;
select e.empno,e.ename,e.deptno,d.deptno,d.dname,d.loc from emp e, dept d where e.deptno = d.deptno and e.empno = 7369;
-- Create table
create table locations
(
locid number(4) not null,
locname varchar2(20)
)
;
-- Create/Recreate primary, unique and foreign key constraints
alter table locations
add constraint PK_LOC primary key (LOCID);
select * from locations;
select * from dept;
select e.*,d.*,l.* from emp e, dept d,locations l where e.deptno = d.deptno and d.loc_id = l.locid and e.empno = 7369;
-- 非等值查询,两个表之间没有父子关系,用!=来连接两个表
select e.empno,e.ename,e.sal,s.grade,s.losal,s.hisal
from emp e,salgrade s where e.sal between s.losal and s.hisal and e.empno = 7369;
-- 自连接(子链接)查询,通过别名,将一个表虚拟成两个表,然后在这两个表上做等值查询
select e.empno,e.ename,m.empno,m.ename from emp e, emp m where e.mgr = m.empno and e.empno = 7369;
-- 外连接: 在等值查询的基础上,可以查询不满足等值条件的数据
-- 左外连接,可以把右边表中不满足等值条件的数据查询出来
select e.*,e.ename,e.deptno,d.dname,d.loc_id
from emp e, dept d where e.deptno(+) = d.deptno;
select e.*,e.ename,e.deptno,d.dname,d.loc_id
from emp e, dept d where d.deptno(+) = e.deptno;
-- 右外连接,可以把左边表中不满足等值条件的数据查询出来
select e.*,e.ename,e.deptno,d.dname,d.loc_id
from emp e, dept d where e.deptno = d.deptno(+);
select e.*,e.ename,e.deptno,d.dname,d.loc_id
from emp e, dept d where d.deptno = e.deptno(+);
/**
-- + 不能同时出现 = 的两边, 下面这句不能执行
select e.*,e.ename,e.deptno,d.dname,d.loc_id
from emp e, dept d where d.deptno(+) = e.deptno(+);
**/
-- ===============sql 1999新标注=========================
-- 1.交叉链接, 就相等于sql 1992老标准等值查询的时候没有给出正确的等值条件,会产生笛卡尔乘积
select e.*,d.* from emp e
cross join dept d;
-- 2.自然链接,在父子表关系上,自动的匹配两个表中列名完整相同的字段(参照列),在这些相同名称的字段上做等值查询
-- 参照列上不能使用前缀,例如两表中都有deptno,就不能加前缀
-- 自然链接的缺陷: 1.会把所有的参照列都作为等值条件; 2.如果参照列的类型不同,查询会报错
select e.empno,e.ename,deptno,d.dname,d.loc_id from emp e natural join dept d;
-- 当两个表中没有参照列的时候,参照查询会产生笛卡尔乘积
select d.deptno,d.dname,l.locname from dept d natural join locations l;
-- 3.join .. using, 在自然连接的基础上,加以改进,使用指定的参照列来作为等值条件
select e.empno,e.ename,deptno,dname,d.loc_id from emp e join dept d using(deptno)
where e.empno = 7369;
-- 4.join .. on,使用on里面指定的条件作为查询条件(on里面的条件可以是任意的条件)
select e.*,d.* from emp e join dept d on (e.deptno = d.deptno);
-- 使用join .. on 做n个表的等值查询,需要n-1个join.. on子句
-- 为了和外连接有个对应,可以加个 inner,加不加对查询不影响
select e.*,d.*,l.*
from emp e
inner join dept d on (e.deptno = d.deptno)
inner join locations l on (d.loc_id = l.locid)
where e.empno = 7369;
-- 使用 join .. on 做非等值查询
select e.empno,e.ename,s.grade,s.hisal
from emp e
join salgrade s on (e.sal between s.losal and s.hisal)
where e.empno = 7369;
-- outer join .. on 外连接
-- left outer join .. on() 可以把左边表中不满足等值条件的数据查询出来
select e.empno,e.ename,e.deptno,d.deptno,d.dname,d.loc_id from emp e
left outer join dept d on (e.deptno = d.deptno);
select e.empno,e.ename,e.deptno,d.deptno,d.dname,d.loc_id from dept d
left outer join emp e on ( d.deptno = e.deptno);
-- right outer join .. on() 可以把右边表中不满足等值条件的数据查询出来
select e.empno,e.ename,e.deptno,d.deptno,d.dname,d.loc_id from emp e
right outer join dept d on (e.deptno = d.deptno);
select e.empno,e.ename,e.deptno,d.deptno,d.dname,d.loc_id from dept d
right outer join emp e on ( d.deptno = e.deptno);
-- full outer join .. on(),可以把左右两边表中不满足等值条件的数据都查询出来
select e.empno,e.ename,e.deptno,d.deptno,d.dname,d.loc_id from emp e
full outer join dept d on (e.deptno = d.deptno);
-- ==================================================================
-- 使用 union 把两个结果集合并成一个结果集
-- 使用查询语句来创建表(复制表及其数据)
create table dept_bak
as select * from dept;
5、oracle union 合并结果集
-- ==================== union 合并结果集==============================================
-- 使用 union 把两个结果集合并成一个结果集,注意两个查询语句的结果集必须要一致才能合并
-- 结果集必须要一致指的是查询字段的个数,字段的类型,字段的顺序 必须要一致
-- 使用查询语句来创建表(复制表及其数据,单十并没有复制主外键)
create table dept_bak
as select * from dept;
-- union, 会去除重复的数据
select * from dept_bak
union
select * from dept;
-- union all, 不去除重复的数据
select * from dept_bak
union all
select * from dept;
6、oracle 子查询
-- 查询 比7566员工的工资高的人的信息
select sal from emp where empno = 7566;
select * from emp where sal > 2975;
-- 为了给主查询提供条件,而首先执行的一个查询,被称为子查询(子查询是用来给著查询提供查询条件的)
-- 子查询首先被执行
-- 主查询使用子查询的查询条件
-- 子查询通常出现在比较运算符的右边,并且用()包围起来,便于理解
select * from emp where sal > (
select sal from emp where empno = 7566
);
-- 子查询的分类,就是根据子查询的返回结果来区分的
-- 单行单列子查询,要使用单行比较运算符
-- 单行单列子查询
select * from emp where sal < (
select mgr from emp where empno = 7566 );
-- 子查询中使用了组函数,但是子查询的结果依旧是单行单列,仍然可以使用单行比较操作符
select * from emp where sal <
(select avg(sal) from emp);
-- 子查询返回的结果是多行单列,就不能使用单行比较操作符
select * from emp where sal <
(select avg(sal) from emp group by deptno);
-- 子查询出现在 having 中
-- having对分组后的数据进行过滤
select deptno,max(sal) from emp group by deptno having max(sal) > (select max(sal) from emp where deptno=20);
-- 子查询没有结果,主查询也不会报错,就是没有查询结果而已
select * from emp where sal > (select sal from emp where empno = 8000);
-- 多行单列子查询,要使用多行比较运算符, in,any, all
select job from emp where sal > 3000;
-- 使用in运算符
select e.empno,e.ename,e.job,e.sal from emp e
where job in (select job from emp where sal > 2000);
-- 使用all运算符
-- > all 大于子查询的最大值
select e.* from emp e where e.sal > all (select sal from emp where deptno = 30);
-- < all 小于子查询的最小值
select e.* from emp e where e.sal < all (select sal from emp where deptno = 30);
-- any
-- > any 大于子查询的最小值
select e.* from emp e where e.sal > any (select sal from emp where deptno = 30);
-- < any 小于子查询的最大值
select e.* from emp e where e.sal < any (select sal from emp where deptno = 20);
-- 多行多列子查询,可以使用in比较运算符
select mgr,job from emp where empno = 7566 or empno = 7369;
-- 成对的比较
-- 查询和 7566,7366同经理同职位的员工的信息
select empno,ename,mgr,job from emp e where (mgr,job) in (select mgr,job from emp where empno = 7566 or empno = 7369)
and empno != 7566 and empno != 7369;
-- 非成对的比较,把多行多列的子查询拆分成两个多行单列的子查询,分别使用in运算符
select empno,ename,mgr,job from emp where mgr in
(select mgr from emp where empno = 7566 or empno = 7369)
and job in (select job from emp where empno = 7566 or empno = 7369)
and empno != 7566 and empno != 7369;
-- 子查询是出现在from后面的,用来提供数据源的,子查询被虚拟成一个表
select 部门编号,maxsal,minsal,avgsal,sumsal from
(
select deptno 部门编号, max(sal) maxsal,min(sal) minsal,avg(sal) avgsal,sum(sal) sumsal from emp
where deptno is not null
group by deptno
) where 部门编号 = 20;
select deptno, max(sal) maxsal,min(sal) minsal,avg(sal) avgsal,sum(sal) sumsal from emp
where deptno is not null
group by deptno;
7、oracle DML语句
-- DML语句,会引起数据库中发生一个事务
-- 第一个执行的DML语句,会引起事务
-- 在事务没有结束之前,只有当前的用户可以看到对数据库的修改操作,其他用户是看不到的
-- 事务可以以回滚的方式结束,所有的操作被放弃,回滚到事务开始之前的状态
-- 事务也可以以提交的方式结束,对数据库的修改被永久的保存,其他用户可以看到被修改的数据
-- 插入语句
insert into dept(deptno,dname,loc_id) values (50,'销售部',1000);
insert into dept(deptno,dname,loc_id) values (60,'开发部',1000);
-- 不指定插入的字段,则表示要插入全部的字段
insert into dept values(20,'销售部',1000);
insert into dept(deptno,dname) values(50,'销售部');
insert into dept(deptno,dname,loc_id) values(50,'销售部',null);
rollback;
commit;
insert into emp values(8000,'张1','工程师',9602,to_date('1987-12-9','YYYY-MM-DD'),3000,1000,20)
-- 一次性的插入多条语句,复制表中的数据,把查询结果当作数据插入到表中
insert into dept_bak
select * from dept;
-- update语句
-- 使用update语句的时候,在事务没有结束之前,该条数据会被锁住,其他的用户无法修改这条语句
update emp set ename = '张2',job = '经理',sal = 3500 where empno = 8001;
--删除语句
delete from dept where deptno > 40;
select * from dept;
-- 建表语句
create table student(
sid number(4) primary key,
sname varchar2(10),
grade varchar(20),
sex char(2) default '男',
birthday date
)
-- 插入数据
insert into student values(1000,'张1'，'java就业班','女','12-8月-1987');
insert into student(sid,sname,grade,birthday) values(1001,'张2'，'java就业班','女',to_date('1985-12-8','YYYY_MM_DD'));
-- 在不影响数据的情况下,对表做出修改,对表的修改主要是对字段的修改
-- 主要的操作: 1.添加字段;2.删除字段;3.修改字段(修改字段的类型,修改字段的长度)
-- 添加字段,总是可以成功,新添加的字段出现在表的最后面
alter table student
add tel varchar2(11);
-- 修改字段
-- 在该字段没有数据的时候,字段的类型,字段的长度都是可以修改的
alter table student
modify tel number(11);
alter table student
modify tel varchar2(11);
-- 对于缺省值的修改,不会影响已经存在的数据,只会对以后插入的数据产生影响
alter table student
modify sex char(2) default '女';
-- 当该字段有数据的时候,字段的类型是不能修改的,
-- 字段的长度是可以修改的,增大总是可以的,减少要看数据的实际长度
alter table student
modify grade varchar2(20)
alter table student
modify grade number(11);
-- 删除字段
alter table student
drop column addr;
-- 在建表的时候使用子查询
crate table dept_back
as
select * from dept;
drop table dept_back;
create table dept_back
as
select deptno,dname from dept;
-- 删除表的数据, truncate 比 delete的删除速度快,但是该命令一点要慎用,因为它不涉及事务
truncate table dept_back;
-- 改变对象的名称
rename student to students;
--=====================数据库约束============================
8、oracle 事务
--=====================事务===========================
--事务开始于第一个DML语句
--事务的结束点
-- 手动结束
-- 手动提交 commit
-- 手动回滚 rollback
-- 自动结束
-- 自动提交 DDL,DCL
-- 自动回滚
9、oracle 约束
-- 建表语句
create table student(
sid number(4) primary key,
sname varchar2(10),
grade varchar2（20),
sex char(2) default '男',
birthday date
)
-- 插入数据
insert into student values(1000,'张1','java就业班','女','12-8月-1987');
-- 在不影响数据的情况下,对表做出修改,对表的修改主要是对字段的修改
-- 主要的操作: 1.添加字段; 2.删除字段; 3.修改字段(修改字段的类型,修改字段的长度)
-- 添加字段,总是可以成功,新添加的字段出现在表的最后面
alter table student add tel varchar2(11);
--非空约束
-- 1.在创建表时设置非空约束
create table userinfo1
(id number(10,0),
Username varchar2(20) not null,
Userpwd varchar(30) not null);
-- 2.在修改表时添加非空约束
alter table userinfo
Modify username varchar2(20) not null;
-- 3.在修改表时去除非空约束
alter table userinfo
Modify userpwd varchar2(30) NULL;
-- 主键约束: 从功能上说,相当于非空且唯一
-- 在一个表中只能有一个主键约束
-- 通过主键可以唯一确定一条记录
-- 一个实体中什么样的数据可以被确定为主键? 不要用实体的业务数据来当作主键,因为业务数据是可以变化的
-- 应该用一个和实体无关的流水号来当作表的主键
-- 主键约束作用在单个字段上
drop table student;
create table student(
sid number(4),
sname varchar2(20),
constraints PK_STU primary key(sid)
)
insert into student values(1000,'张1')
insert into student values(1001,'张1')
insert into student values(1001,'张1')
insert into student values(null,'张1')
-- 主键约束同时作用在多格字段上,也被称为联合主键
drop table student;
create table student(
first_name varchar2(20),
last_name varchar2(20),
grade varchar2(20),
constraints PK_STU primary key(first_name,last_name)
);
insert into student values('张','一','java就业班');
insert into student values('张','二','java就业班');
insert into student values('二','张','java就业班');
-- 不可以,主键唯一性
insert into student values('张','一','java就业班');
-- 不可以,主键不能为空
insert into student values('张',null,'java就业班');
-- 在修改表时添加主键约束
ADD CONSTRAINT constraint_name
PRIMARY KEY(column_name1,…);
-- 删除主键约束
alter table table_name DISABLE|ENABLE CONSTRAINT constraint_name
-- 外键约束: 一对多(多对一)
-- 外键约束,外键约束可以重复,可以为null
-- 外键约束关系到两个表的两个字段之间的关系
-- 在外键约束下,在建表的时候,要先建立父表, 后建立子表
create table dept1(
deptno number(4) primary key,
dname varchar2(10)
);
create table emp1(
empno number(4) primary key,
ename varchar2(10),
job varchar2(10),
dept_no number(4),
constraints FK_EMP foreign key(dept_no) references dept1(deptno)
);
-- 在外键约束下,在添加数据的时候,要先添加父表数据, 后添加子表数据
insert into dept1 values(10,'销售部');
insert into dept1 values(20,'开发部');
insert into dept1 values(30,'生产部');
insert into emp1 values(1000,'张0','销售员',10);
insert into emp1 values(1001,'张1','销售员',10);
insert into emp1 values(1002,'张2','工程师',20);
insert into emp1 values(1003,'张3','工程师',20);
insert into emp1 values(1004,'张4','工程师',20);
-- 附表没这个值,插入不可以
insert into emp1 values(1005,'张4','工程师',40);
-- 可以插入,外键约束可以为空
insert into emp1 values(1006,'张4','工程师',null);
-- 在外键约束下,在删除数据的时候,要先删除子表数据,后删除父表数据
delete from dept1 where deptno = 10;
delete from emp1 where dept_no = 10;
-- 在外键约束下,在删除表的时候,要先删除子表,再删除父表
drop table emp1;
drop table dept1;
-- 父表中的什么样的字段可以被其他表引为外键?父表中的主键或唯一键可以被其他表引为外键
create table deptL1(
deptno number(4) primary key,
dname varchar2(10),
locid number(4),
constraints LOC_UIN unique(locid)
);
create table empL1(
empno number(4) primary key,
ename varchar2(10),
job varchar2(10),
dept_no number(4),
-- constraints FK_EMP foreign key(dept_no) references deptL1(deptno)
);
-- 在建立好表之后,通过alter table 再给表添加约束
alter table empL1
add constraints FK_EMPL foreign key(dept_no) references deptL1(deptno)
-- 一对一 如何用外键来实现, 在外键上同时设置唯一约束
create table card(
cid number(4) primary key,
cname varchar2(10)
)
create table persion(
pid number(4) primary key,
pname varchar2(20),
cid number(4),
constraints FK_PER_CARD foreign key(cid) references card(cid),
constraints CID_UNI unique(cid)
)
-- 多对多关系的实现,要引入关系表
create table student2(
sid number(4) primary key,
sname varchar2(20)
);
create table course2(
cid number(4) primary key,
cname varchar2(20)
);
-- 1.关系表中可以用两个表的主键当作外键,同时又把外键当作联合主键
drop table stu_cour;
create table stu_cour(
sid number(4),
cid number(4),
constraints FK1 foreign key(sid) references student2(sid),
constraints FK2 foreign key(cid) references course2(cid),
constraints PK_STU_COUR primary key(sid,cid)
)
-- 2.关系表中可以用两个表的主键当作外键,关系表有自己独立的主键
create table stu_cour(
scid number(4) primary key,
sid number(4),
cid number(4),
constraints FK1 foreign key(sid) references student2(sid),
constraints FK2 foreign key(cid) references course2(cid)
)
10、oracle 索引
-- 创建索引有两种方式
-- 1. 自动创建: oracle会自动为主键和唯一键创建索引
-- 自动创建的索引是无法手动删除的,但是在删除主键约束,唯一键约束的时候,对应的索引会被自动的删除
alter table emp
add constraints ENAME_UNI unique(ename);
-- 删除约束
alter table emp
drop constraints ENAME_UNI;
-- 2.可以手动的创建约束,表中什么样的字段可以添加索引,
-- 在查询的时候,经常被用来作为查询条件的字段,应该添加索引
create index ENAME_INDEX on emp(ename);
-- 删除索引
drop index ENAME_INDEX;
select * from emp where ename = 'SMITH';
11、oracle 视图
-- ===========================视图对象==============================
-- 视图,就是一个虚表,我们可以从这个表中查询数据
-- 视图,就是一个命名的查询语句
-- 任何有效的查询语句,无论多么复杂,都可以被创建成一个视图
/*
select * from emp or (子查询),视图
*/
-- 给人事经理使用的视图
create view HR_VIEW
as
select * from emp;
-- 创建给项目经理使用的视图
create view HR_MGR
as
select empno,ename,job,mgr,hiredate,deptno from emp;
desc emp;
desc HR_VIEW;
-- 我们可以在命令行窗口模式下查看表的结构,也可以查看视图的结构
select * from HR_VIEW where empno = 7369;
-- 创建或者重置, 没有这个视图创建,已经有了重置
create or replace view HR_MGR
as
select empno,ename,job,mgr,hiredate,deptno,comm from emp;
desc emp;
desc HR_VIEW;
-- 删除视图,会影响原来的数据吗? 不会影响原来的数据
drop view HR_MGR;
--
create or replace view VIEW_EMPINFO
as
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,(e.sal*12) as "年薪",e.comm,(e.sal*12 + nvl(e.comm,0)) "年收入",
e.deptno,d.dname,d.loc_id,l.locname
from emp e,dept d,locations l
where e.deptno = d.deptno and d.loc_id = l.locid;
select * from VIEW_EMPINFO;
select * from VIEW_EMPINFO
where 年收入>20000
create or replace view VIEW_EMP
(员工编号,员工姓名,薪水,佣金)
as
select empno,ename,sal,comm from emp;
select * from VIEW_EMP where 员工编号 = 7369;
-- 把使用组函数的查询创建成视图
create or replace view V_EMP_SAL
as
select deptno,max(sal) maxsal,min(sal) minsal,sum(sal) sumsal,avg(sal) avgsal
from emp
where deptno is not null
group by deptno
order by deptno;
select * from V_EMP_SAL where maxsal > 2000;
-- 视图就是一个(虚表),我们可以对表插入数据,也可以对视图插入数据
create or replace view V_EMPINFO
as
select * from emp where sal > 2000;
-- 对视图插入数据,数据会被插入到源表
insert info V_EMPINFO value(8000,'张1','工程师',7902,'12-8月-1987',1900,500,20);
-- 创建只读的视图,不能执行DML操作
create or replace view V_EMPINFO
as
select * from emp where sal > 2000
with read only;
-- 行内视图,就是出现在from后面的子查询,也就是一个视图,单十该视图没有命名,不会在数据库中保存
-- 查询工资最高的前三个人的信息,这种方式被称为 TOP-N 分析法
-- rownum只能适用于 <= 的情况
select rownum,e.*
from (select * from emp order by sal desc) e
where rownum<=3;
-- rownum不能适用于 >= 的情况
select rownum,e.*
from (select * from emp order by sal desc) e
where rownum>=12;
select rownum,e.*
from (select * from emp order by sal asc) e
where rownum<=4;
12、oracle 创建表空间
linux：
EM
emctl status dbconsole
emctl start dbconsole
windows：
服务开启：OracleDBCConsole + 数据库名称
https://localhost:1158/em/console/logon/logon
通过界面生成的语句
CREATE SMALLFILE TABLESPACE "CMDBTBS" DATAFILE 'D:\DATABASE\ORACLE\ORADATA\ETHEREAL\cmdbtbs01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
oracle 管理
用户名和安全
-- 查询用户的默认表空间,临时表空间
select username,password,default_tablespace,temporary_tablespace from dba_users;
-- 查询临时表空间
select username from dba_users where temporary_tablespace = 'SYSTEM';
-- 查询用户的oracle配置文件
select username,profile from dba_users where username = UPPER('&&username');
select profile,resource_name,resource_type,limit from dba_profiles order by profile,resource_type;
-- 查询系统中创建密码时间 和 更新密码时间相同的用户名
select name,to_char(ctime,'dd-mon-yy hh24:mi:ss'),
to_char(ptime,'dd-mon-yy hh24:mi:ss'),
length(password) from user$ where password is not null and password not in ('GLOBAL','EXTERNAL') and ctime = ptime;
select name,value from v$parameter where name = 'resource_limit';
-- 查询系统权限
select distinct privilege from dba_sys_privs;
-- 创建用户
create user zzq identified by zzq;
-- 更改密码
alter user zzq identified by zzq1;
--更改默认的永久表空间和临时表空间
alter user zzq default tablespace users temporary tablespace temp;
--锁定账户
alter user zzq account lock;
-- 更改zzq在USERS表空间的限额
alter user zzq quota 500m in users;
-- 解锁账户
alter user zzq account unlock;
-- 查看数据库中的锁定用户和锁定日期
select username,lock_date from dba_users;
-- 授予zzq系统权限
-- 连接数据库的权限
grant create session to zzq;
-- 授予autopriv.db_grant_sql对象的查询权限
grant select on autopriv.db_grant_sql to zzq;
commit;
-- 回收zzq查询autopriv.db_grant_sql的查询权限
revoke select on autopriv.db_grant_sql from zzq;
-- 未赋予插入权限,不可插入
insert into autopriv.db_grant_sql(SQL_ID,TASK_ID,SQL_TEXT,SQL_STATUS,EXEC_RESULT,SQL_ORDER,SQL_COMMENTS,SQL_CREATE_TIME,SQL_UPDATE_TIME) values('S333','T121','sqlText001','1','2',1,'dfdf',to_date('05-02-2021 15:51:39','dd-mm-yyyy hh24:mi:ss'),to_date('05-02-2021 15:52:39','dd-mm-yyyy hh24:mi:ss'));
-- 创建角色
create role jr_dba;
--把autopriv.db_grant_sql对象的查询权限赋给 角色 jr_dba;
grant select on autopriv.db_grant_sql to jr_dba;
--把jr_dba角色分配给zzq -- 授予后 zzq可查询
grant jr_dba to zzq;
commit;
-- 把autopriv.db_grant_sql对象的insert权限赋给 角色 jr_dba; -- 授予后zzq可插入
grant insert on autopriv.db_grant_sql to jr_dba;
-- 回收jr_dba的insert权限 -- 再次插入权限不足
revoke insert on autopriv.db_grant_sql from jr_dba;
-- 查询用户的角色
select grantee,granted_role from dba_role_privs order by 1;
-- 查询当前数据库用户的角色
select * from user_role_privs;
-- 创建自定义数据库配置文件
create profile user_profile_limit
limit
sessions_per_user 20 -- 并发会话数
cpu_per_session 210000 -- 每个会话使用cpu的时间限制(单位:厘秒)
logical_reads_per_session 1000000 -- 每次会话读入的块数
connect_time 480 -- 连接时间 (单位:秒)
idle_time 120; --空闲时间(单位：分钟)
-- 将自定义配置文件分配给zzq
alter user zzq profile user_profile_limit;
-- 创建密码验证函数
-- sql窗口执行
@?/rdbms/admin/utlpwdmg
-- 设置密码校验函数
--alter profile default limit PASSWORD_VERIFY_FUNCTION ora
-- 回收密码校验函数
alter profile default limit PASSWORD_VERIFY_FUNCTION null;
-- ==================设置表为只读模式===============================
-- 将表设置为只读模式
alter table inv read only;
--验证表是否处于只读模式
select table_name,read_only from user_tables where read_only = 'YES';
-- 将只读模式的表切换为 读写模式
alter table inv read write;
权限管理
Oracle 权限
权限允许用户访问属于其它用户的对象或执行程序，ORACLE系统提供三种权限：Object 对象级、System 系统级、Role 角色级。这些权限可以授予给用户、特殊用户public或角色，如果授予一个权限给特殊用户"Public"（用户public是oracle预定义的，每个用户享有这个用户享有的权限）,那么就意味作将该权限授予了该数据库的所有用户。
对管理权限而言，角色是一个工具，权限能够被授予给一个角色，角色也能被授予给另一个角色或用户。用户可以通过角色继承权限，除了管理权限外角色服务没有其它目的。权限可以被授予，也可以用同样的方式撤销。
权限分类
1、系统权限：系统规定用户使用数据库的权限。（系统权限是对用户而言)。
2、实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。
系统权限管理
系统权限分类：
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
对于普通用户：授予connect, resource权限。
对于DBA管理用户：授予connect，resource, dba权限。
系统权限授权命令：
系统权限只能由DBA用户授出：sys, system(最开始只能是这两个用户)
授权命令：SQL> grant connect, resource, dba to 用户名1 [,用户名2]...;
注:普通用户通过授权可以具有与system相同的用户权限，但永远不能达到与sys用户相同的权限，system用户的权限也可以被回收。
例：
SQL> connect system/manager
SQL> Create user user50 identified by user50;
SQL> grant connect, resource to user50;
查询用户拥有哪里权限：
SQL> select * from dba_role_privs;
SQL> select * from dba_sys_privs;
SQL> select * from role_sys_privs;
查自己拥有哪些系统权限
SQL> select * from session_privs;
删除用户
SQL> drop user 用户名 cascade; //加上cascade则将用户连同其创建的东西全部删除
系统权限传递：
增加WITH ADMIN OPTION选项，则得到的权限可以传递。
SQL> grant connect, resorce to user50 with admin option; //可以传递所获权限。
系统权限回收：系统权限只能由DBA用户回收
SQL> Revoke connect, resource from user50;
说明：
1）如果使用WITH ADMIN OPTION为某个用户授予系统权限，那么对于被这个用户授予相同权限的所有用户来说，取消该用户的系统权限并不会级联取消这些用户的相同权限。
2）系统权限无级联，即A授予B权限，B授予C权限，如果A收回B的权限，C的权限不受影响；系统权限可以跨用户回收，即A可以直接收回C用户的权限。
实体权限管理
实体权限分类
select, update, insert, alter, index, delete, all //all包括所有权限
execute //执行存储过程权限
user01:
SQL> grant select, update, insert on product to user02;
SQL> grant all on product to user02;
user02:
SQL> select * from user01.product;
// 此时user02查user_tables，不包括user01.product这个表，但如果查all_tables则可以查到，因为他可以访问。
将表的操作权限授予全体用户：
SQL> grant all on product to public; // public表示是所有的用户，这里的all权限不包括drop。
实体权限数据字典
SQL> select owner, table_name from all_tables; // 用户可以查询的表
SQL> select table_name from user_tables; // 用户创建的表
SQL> select grantor, table_schema, table_name, privilege from all_tab_privs; // 获权可以存取的表（被授权的）
SQL> select grantee, owner, table_name, privilege from user_tab_privs; // 授出权限的表(授出的权限)
DBA用户可以操作全体用户的任意基表(无需授权，包括删除)：
DBA用户：
SQL> Create table stud02.product(
id number(10),
name varchar2(20));
SQL> drop table stud02.emp;
SQL> create table stud02.employee
as
select * from scott.emp;
实体权限传递(with grant option)：
user01:
SQL> grant select, update on product to user02 with grant option; // user02得到权限，并可以传递。
实体权限回收：
user01:
SQL>Revoke select, update on product from user02; //传递的权限将全部丢失。
说明
1）如果取消某个用户的对象权限，那么对于这个用户使用WITH GRANT OPTION授予权限的用户来说，同样还会取消这些用户的相同权限，也就是说取消授权时级联的。
Oracle 角色管理
角色是一组权限的集合，将角色赋给一个用户，这个用户就拥有了这个角色中的所有权限。系统预定义角色是在数据库安装后，系统自动创建的一些常用的角色。下介简单的介绍一下这些预定角色。角色所包含的权限可以用以下语句查询：
sql>select * from role_sys_privs where role='角色名';
CONNECT, RESOURCE, DBA：这些预定义角色主要是为了向后兼容。其主要是用于数据库管理。oracle建议用户自己设计数据库管理和安全的权限规划，而不要简单的使用这些预定角色。将来的版本中这些角色可能不会作为预定义角色。
DELETE_CATALOG_ROLE， EXECUTE_CATALOG_ROLE， SELECT_CATALOG_ROLE：这些角色主要用于访问数据字典视图和包。
EXP_FULL_DATABASE， IMP_FULL_DATABASE：这两个角色用于数据导入导出工具的使用。
AQ_USER_ROLE， AQ_ADMINISTRATOR_ROLE：AQ:Advanced Query。这两个角色用于oracle高级查询功能。
SNMPAGENT：用于oracle enterprise manager和Intelligent Agent
RECOVERY_CATALOG_OWNER：用于创建拥有恢复库的用户。关于恢复库的信息，参考oracle文档《Oracle9i User-Managed Backup and Recovery Guide》
HS_ADMIN_ROLE：A DBA using Oracle's heterogeneous services feature needs this role to access appropriate tables in the data dictionary.
管理角色
建一个角色
sql>create role role1;
授权给角色
sql>grant create any table,create procedure to role1;
授予角色给用户
sql>grant role1 to user1;
查看角色所包含的权限
sql>select * from role_sys_privs;
创建带有口令以角色(在生效带有口令的角色时必须提供口令)
sql>create role role1 identified by password1;
修改角色：是否需要口令
sql>alter role role1 not identified;
sql>alter role role1 identified by password1;
设置当前用户要生效的角色
(注：角色的生效是一个什么概念呢？假设用户a有b1,b2,b3三个角色，那么如果b1未生效，则b1所包含的权限对于a来讲是不拥有的，只有角色生效了，角色内的权限才作用于用户，最大可生效角色数由参数MAX_ENABLED_ROLES设定；在用户登录后，oracle将所有直接赋给用户的权限和用户默认角色中的权限赋给用户。）
sql>set role role1; //使role1生效
sql>set role role,role2; //使role1,role2生效
sql>set role role1 identified by password1; //使用带有口令的role1生效
sql>set role all; //使用该用户的所有角色生效
sql>set role none; //设置所有角色失效
sql>set role all except role1; //除role1外的该用户的所有其它角色生效。
sql>select * from SESSION_ROLES; //查看当前用户的生效的角色。
修改指定用户，设置其默认角色
sql>alter user user1 default role role1;
sql>alter user user1 default role all except role1;
详见oracle参考文档
9.删除角色
sql>drop role role1;
角色删除后，原来拥用该角色的用户就不再拥有该角色了，相应的权限也就没有了。
说明:
1)无法使用WITH GRANT OPTION为角色授予对象权限
2)可以使用WITH ADMIN OPTION 为角色授予系统权限,取消时不是级联
与权限安全相关的数据字典表有:
ALL_TAB_PRIVS
ALL_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD
DBA_SYS_PRIVS
DBA_ROLES
DBA_ROLE_PRIVS
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
SESSION_PRIVS
SESSION_ROLES
USER_SYS_PRIVS
USER_TAB_PRIV
oracle的系统和对象权限列表
alter any cluster 修改任意簇的权限
alter any index 修改任意索引的权限
alter any role 修改任意角色的权限
alter any sequence 修改任意序列的权限
alter any snapshot 修改任意快照的权限
alter any table 修改任意表的权限
alter any trigger 修改任意触发器的权限
alter cluster 修改拥有簇的权限
alter database 修改数据库的权限
alter procedure 修改拥有的存储过程权限
alter profile 修改资源限制简表的权限
alter resource cost 设置佳话资源开销的权限
alter rollback segment 修改回滚段的权限
alter sequence 修改拥有的序列权限
alter session 修改数据库会话的权限
alter sytem 修改数据库服务器设置的权限
alter table 修改拥有的表权限
alter tablespace 修改表空间的权限
alter user 修改用户的权限
analyze 使用analyze命令分析数据库中任意的表、索引和簇
audit any 为任意的数据库对象设置审计选项
audit system 允许系统操作审计
backup any table 备份任意表的权限
become user 切换用户状态的权限
commit any table 提交表的权限
create any cluster 为任意用户创建簇的权限
create any index 为任意用户创建索引的权限
create any procedure 为任意用户创建存储过程的权限
create any sequence 为任意用户创建序列的权限
create any snapshot 为任意用户创建快照的权限
create any synonym 为任意用户创建同义名的权限
create any table 为任意用户创建表的权限
create any trigger 为任意用户创建触发器的权限
create any view 为任意用户创建视图的权限
create cluster 为用户创建簇的权限
create database link 为用户创建的权限
create procedure 为用户创建存储过程的权限
create profile 创建资源限制简表的权限
create public database link 创建公共数据库链路的权限
create public synonym 创建公共同义名的权限
create role 创建角色的权限
create rollback segment 创建回滚段的权限
create session 创建会话的权限
create sequence 为用户创建序列的权限
create snapshot 为用户创建快照的权限
create synonym 为用户创建同义名的权限
create table 为用户创建表的权限
create tablespace 创建表空间的权限
create user 创建用户的权限
create view 为用户创建视图的权限
delete any table 删除任意表行的权限
delete any view 删除任意视图行的权限
delete snapshot 删除快照中行的权限
delete table 为用户删除表行的权限
delete view 为用户删除视图行的权限
drop any cluster 删除任意簇的权限
drop any index 删除任意索引的权限
drop any procedure 删除任意存储过程的权限
drop any role 删除任意角色的权限
drop any sequence 删除任意序列的权限
drop any snapshot 删除任意快照的权限
drop any synonym 删除任意同义名的权限
drop any table 删除任意表的权限
drop any trigger 删除任意触发器的权限
drop any view 删除任意视图的权限
drop profile 删除资源限制简表的权限
drop public cluster 删除公共簇的权限
drop public database link 删除公共数据链路的权限
drop public synonym 删除公共同义名的权限
drop rollback segment 删除回滚段的权限
drop tablespace 删除表空间的权限
drop user 删除用户的权限
execute any procedure 执行任意存储过程的权限
execute function 执行存储函数的权限
execute package 执行存储包的权限
execute procedure 执行用户存储过程的权限
force any transaction 管理未提交的任意事务的输出权限
force transaction 管理未提交的用户事务的输出权限
grant any privilege 授予任意系统特权的权限
grant any role 授予任意角色的权限
index table 给表加索引的权限
insert any table 向任意表中插入行的权限
insert snapshot 向快照中插入行的权限
insert table 向用户表中插入行的权限
insert view 向用户视图中插行的权限
lock any table 给任意表加锁的权限
manager tablespace 管理（备份可用性）表空间的权限
references table 参考表的权限
restricted session 创建有限制的数据库会话的权限
select any sequence 使用任意序列的权限
select any table 使用任意表的权限
select snapshot 使用快照的权限
select sequence 使用用户序列的权限
select table 使用用户表的权限
select view 使用视图的权限
unlimited tablespace 对表空间大小不加限制的权限
update any table 修改任意表中行的权限
update snapshot 修改快照中行的权限
update table 修改用户表中的行的权限
update view 修改视图中行的权限
分类:
数据库 / oracle
标签:
oracle
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 上周热点回顾（2.24-3.2）
2020-03-07 selenium安装使用，selenium模拟浏览器抓取51job上的 python职位和工资