Oracle实验总结

近日做了基于ORACLE的数据库安全、对象——sql数据库，XML数据库的实验，其中遇到很多问题，先总结如下。

1、安装Oracle的过程中可以‘选择建立数据库’，也可以选择‘只安装软件’，后者可在安装好软件后，建立数据库，可参考网上的很多安装步骤。

2、Oracle的默认用户有sys和system,在命令行或者sQl*plus中登陆时，写法如下：

Connect sys/123456 as sysdba; 其中123456是密码，在创建数据库的时候设置的。

普通用户登录，connect zhangsan/123456;

3、grant select不支持对表中选定的几列授权，只能将整个表的select权限授权出去。所以实验中先建立了基于选课信息表的视图SelectiveInfo1，视图中包含成绩列，学号列以及班级列（不包含其他的列）,再将对视图SelectiveInfo1的select权限授予角色teacher。

4、创建用户的时候，一定要说明该用户对表空间的权限即（quota unlimited on tablespace或者具体限定大小的句子），否则该用户即使被授予了建表权限，也不能建表。

创建用户的时候必须给用户授予create session的权限，这是最基本的权限。

5、不同的用户可以创建相同的表格，例如，teacher用户创建了table1,student用户也创建了table1,且这两个表格式完全相同的，那么其他用户需要用这两个表格时，可以用teacher. table1和student. table1加以区分。

系统用户创建的表，和用户自己创建的表，本用户引用是可不加前缀。

6、oracle有自带的密码验证函数，默认情况下是不启用的，可以用下面的语句启用，注意必须以Connect sys/123456 as sysdba的身份登陆(其他DBA身份的用户可不可以，本人没有试过)才能启用密码函数，启用方式如下

7、将日期装换为字符串可以to_char()函数，将字符串转换为日期用to_date()函数。网上有很多这两个函数的资料。

8、Xml数据库使用的简单举例（具体路劲写法好像不对，只要将改改‘//’为‘/’并把路径写全就可以了，路径的用法其实是xpath技术）

建立含xmltype数据的表

create table abc

(id number,

xmldoc sys.xmltype);

向表中插入数据

insert into abc (id,xmldoc)

values

(1 ,

sys.xmlType.createXML

(''<name>

</name>'')

);

查询表中的数据：

得到id=1的value变脸的值

select

i.xmldoc.extract(''//name/a[@id=1]/@value'').getStringVal() as ennames, id

from abc i

得到a节点的值

select id, i.xmldoc.extract('/name/a/text()').getStringVal() as truename

from abc i

得到节点id属性的值

Select hd.Data_t.extract(''/root/name/@id'').getStringVal() As Name

FROM sehr_house_data hd

第二篇：oracle总结

一、查看表结构

desc 表名

例如：desc emp；

二、查询所有列

例如：select * from emp;

三、查询指定列

例如：select ename,sal,job,deptno from emp;

四、取消重复的行，使用distinct关键字

例如：select distinct deptno,job from emp;

五、处理null值,使用nvl函数来处理

例如：select sal*13+nvl(comm,0)*13,ename,comm from emp;

nvl(comm,0)的意思是如果comm的值不为null，就用comm的值，如果为null，则用0代替null。

六、如何连接字符串，使用||

例如：select ename || 'is a'||job from emp;

七，使用where关键字作为条件来实行查询

例如select empno,sal from emp where ename='SMITH';这句的意思是从表emp中找出名字叫SMITH的人的雇员号和薪水。

八、使用like关键字进行模糊查询

%表示多个字符，_代表单个字符，最后一个字符必须是%

例如select * from emp where ename like '%A%'；这句话的意思是找出emp表中名字中间包含A的所有人的信息

九、在where条件中使用in关键字

例如select * from emp where empno in(123,456,789);这句话的意思是找出emp表中empno在（123,456,789）中的人的信息.补充一句，select语句和java语句一样，要以分号结尾，不区分大小写。

十、使用is not null

例如；select * from emp where emmpno is not null;

十一、使用order by子句进行查询(order by用来排序)

例如；select * from emp order by deptno asc,sal desc;这句话的意思是将emp表中的deptno升序排列，sal降序排列

十二、使用列的别名排序

例如：select ename,sal*12 '年薪' from emp order by '年薪' asc；

十三、group by子句和having子句

group by 子句用于对查询的结果分组统计

having 子句用于限制分组显示结果

如果在select语句中同时包含有group by,having,order by,那么他们的顺序是group by,having,order by。

十四、多表连接查询（重点）

多表连接查询是指基于两个或两个以上表或是视图的查询。

例如：slect a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;这句话的意思是显示所有雇员的姓名，薪水和部门名称。因为这三个条件是分别位于emp和dept表

中的，所以需要使用多表连接查询，因为两张表都有deptno，于是便可以使用deptno将两张表连接起来进行查询。

1.查询的时候,可以from多个表

SELECT * FROM EMP,DEPT

这样会产生一个笛卡尔集

表示:两个表任何组合的结果

2.需求:取关联数据

我的部门20号,我想要20号部门的名称====连接条件

select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno

3.语法格式:

select 字段 from 表1,表2 where 表1.列=表2.列

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

当然,你可以为表定义一个别名,但是不能使用as(字段重命名可以使用)

不同的连接条件可以使用and或or

select empno,ename,d.deptno,dname,e.sal from emp e,dept d where e.deptno=d.deptno And sal>1600

4.使用不等值连接

连接条件:一个表中的记录,在另一个表中能够找到匹配的记录即可

Select e.*,g.grade From emp e,salgrade g Where e.sal Between g.losal And g.hisal

5.外连接:把不满足条件的数据显示出来

Select * From EMP E,DEPT D Where E.DEPTNO=D.DEPTNO(+)

外连接可以在左表,也可以在右表

6.自连接

从同一个表中联合查询

Select E.ENAME,P.ENAME From EMP E,EMP P Where E.MGR=P.EMPNO

-----sql1999:

7.交叉连接:

其结果是笛卡尔集,是没加任何条件所产生出来的数据集合

8.自然连接:默认情况下,以两个表中有相同名称的列来进行连接查询

Select * From EMP Natural Join DEPT

其效果相当于

select * from emp,dept where emp.deptno=dept.deptno

9.使用using子句来查询

select * from emp join dept using(deptno)

10.使用on子句

使用on来指定连接具体条件

Select * From EMP Join DEPT On emp.deptno=dept.deptno And EMP.DEPTNO=20

多个表连接查询

Select E.ENAME,E.SAL,D.DNAME,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

11.外连接:

与内连接的区别:

内连接========查询条件相等的记录(能够匹配的)

外连接========条件相等的+没匹配的

Select * From EMP E Left Outer Join DEPT D On E.DEPTNO=D.DEPTNO Select * From EMP E Right Outer Join DEPT D On E.DEPTNO=D.DEPTNO Select * From EMP E Full Outer Join DEPT D On E.DEPTNO=D.DEPTNO 十五、子查询

子查询:

语句内部的子句,

Select * From EMP Where SAL>(

Select SAL From EMP Where ENAME='ALLEN'

)

1.注意:

A.子查询总是先于主语句的运行

B.必须有(),表示是一个整体

C.习惯上,把子查询放在条件的右边

D.单行操作符只能接受一个值

单行:> < >= <=等

2子查询的分类:

A.单行

B.多行

3.单行的语法

select 字段 from 表 where 字段 >[单行] (子查询)

4.多行

in ,all,any

in:表示在一个区间内

select * from emp where ename in (

select ename from emp where sal>2000

)

any:

> any:比子查询中最小的大

< any:比子查询中最大的小

all

>all :比子查询中最大的那个还要大

<all:比子查询中最小的那个还要小

十六、视图

视图:逻辑上的数据集合

逻辑上:区别于物理上

物理上:表示真实的,现实存在

逻辑上:虚的数据,来源于真实的表

比如,我们可以为emp创建一个视图:

CREATE OR REPLACE VIEW EMP_VIEW AS SELECT EMPNO,ENAME,JOB,HIREDATE FROM EMP；

以后如果想要EMPNO,ENAME,JOB,HIREDATE这些数据,就可以不必访问emp了,直接从视图中来查找。

作用:

控制数据访问

简化查询

数据独立性

避免重复访问相同的数据

1.创建格式:

create or replace view 视图名词 as 数据集合(通常是一个查询语句)

2.在创建视图的时候,可以使用别名

CREATE OR REPLACE VIEW EMP_VIEW AS

SELECT empno employee_number,ename employee_name,job type,hiredate birthday FROM EMP

3.修改视图:

replace

create or replace view myview as select...

4.视图创建的规则

视图的内容可以是任意的集合

CREATE OR REPLACE VIEW EMP_VIEW AS

SELECT JOB,MAX(SAL) AS MAXSAL,MIN(SAL) AS MINSAL,

(MAX(SAL)-MIN(SAL)) AS SAL,COUNT(*) NUM,AVG(SAL) AVGSAL

FROM EMP GROUP BY JOB

HAVING JOB IS NOT NULL

5.区别:

A语句:

CREATE OR REPLACE VIEW JOB_VIEW AS

SELECT DISTINCT(JOB) FROM EMP WHERE JOB IS NOT NULL

B语句:

CREATE TABLE JOB_TEMP AS SELECT DISTINCT(JOB) FROM EMP WHERE JOB IS NOT NULL

主要区别:

共同点:都做到了:数据被独立出来

最大不同:JOB_TEMP是一个物理的表,是要占用很多的数据库空间的,

相当于硬盘上的资料被拷贝了一份

JOB_VIEW:数据还是同一份,不会多占空间,JOB_VIEW相当于是一个"快捷方式"

视图数据的来源===基础表

对基础表的数据的改变将会影响到视图,视图本身并不保存数据,只是对相关数据集合的一个引用而已

简单视图操作的数据和基础表中的数据是"同一份"

简单视图可以使用DML，但是不提倡使用这个功能。

复杂视图不能使用DML:

原因:数据已经被整合了,如果被改变,无法和基础表的数据对应了

6.删除视图:

drop view 视图名称

删除视图是对基础表不会产生任何的影响

7.TOP-N分析（分页查询）:

使用到一个关键的列名:rownum

rownum:行编号

伪列:虚拟的列,你并没有在表中人为的添加这个列,

但是当你使用rownum关键词,oracle服务器能够解析

使用的方式

rownum<或<=

因为它总是从第一行开始

例外:

ROWNUM=1不会为null

分页查询的变通方式

SELECT * FROM (

SELECT T.*,ROWNUM AS RN FROM EMP T

) WHERE RN BETWEEN 3 AND 8

分页查询范例：

select * from(select table_name,rownum rn from user_tables) where rn<=10;

十七、约束（主要用来限定内容）

1.分类：A.非空

B.唯一

C.主键:非空+唯一

D.外键:值是外来的

E.检查:值是符合条件

2.注意事项

A.可以给约束起个名字,也可以是使用默认的名字

B.创建的两种时机

创建表的同时/修改表的定义的时候

C.约束的定义位置

表级/列级

D.在字典表中可以查看到这些约束信息

表的定义:user_tables

约束的定义:user_constraints

使用SELECT * FROM USER_CONSTRAINTS语句可以查看到所有的约束信息

3.not null约束

用来修饰字段的

表示该列的值不能为null

定义位置:not null只能在列级

列级:区别于表级

表级:定义的时候,与字段用逗号分开,和字段是同一语句级别

列级:定义的时候,紧跟在字段的后面

CREATE TABLE employees(

employee_id NUMBER(6),

first_name VARCHAR2(20),

...

job_id VARCHAR2(10) NOT NULL,

CONSTRAINT emp_emp_id_pk

PRIMARY KEY (EMPLOYEE_ID));

上面的例子中

not null=======列级

CONSTRAINT emp_emp_id_pk

PRIMARY KEY (EMPLOYEE_ID)代表一个表级的定义

4.unique唯一约束

是用来修饰一个字段的,内容的

unique要求字段内容不能有重复

unique定义位置

A.表级(自定义名称)

B.列级(使用系统默认名称)

基本语法:

列级:字段名 unique

表级:,constraints uni_名称 unique(字段名)

例子:

CREATE TABLE T4(

ID NUMBER(4) UNIQUE,--column

NAME VARCHAR2(20),

AGE NUMBER(3),

CONSTRAINTS UNI_NAME UNIQUE(NAME)--table

)

5.主键约束

也是用来约束字段的,要求其内容必须不能为null,也不能重复

PRIMARY KEY:既不能为null,也不能重复

定义位置:

A.表级

B.列级

定义方式

列级:字段名 primary key

表级:,constraints pk_id primary key(id)

主键==not null+unique

一般情况下,我们在创建表的同时,都会为表指定一个主键,用来唯一标识一条记录,以便在程序中实现修改,删除等业务逻辑操作时,根据该主键标识来准确定位到要操作的记录

联合主键:

把多个字段合起来作为一个主键

要求多个字段每一项都不能为null,

并且合起来的值不能重复

6.外键约束

外来的键值

EMP表的deptno字段引用了dept表中的deptno字段

我们认为:

emp表中的deptno是一个外键,emp.deptno不能任意指定,只能从dept.deptno的值中任意选一个

外键约束定义的位置:

A.表级

B.列级

语法定义格式:

constraints 自定义名称 FOREIGN KEY (字段名)

references 表名(字段名)

使用外键约束的条件

如果A表引用B表中的字段id

条件有两个

B表中的id必须有主键约束

或

唯一性约束

删除表的方式:

两种:A.drop table

B.工具中drop

列级外键定义:

直接在列名的后面

references dept(deptno)

7.关于外键的删除级联设置

联动的效果

我们可以在父项进行删除的时候,让子项联动

两种方式

A.on delete cascade:级联删除

B.on delete set null(不引用)

外键引用时的值的选择

A.要么为null

B.要么从父表中取一个值

8.CHECK约束

check:对某个字段的值的约束,要求该字段的每一行都必须满足的条件它以一种很直观的方式,来限定你的行值

CREATE TABLE STAFF(

STAFFID NUMBER(5),

NAME VARCHAR2(20) CHECK(NAME IN ('A','B','C'))

)

定义位置:

可以表级

也可以是列级

9,约束的添加

语法:

alter table 表名 add primary key (字段名称)

实例:

ALTER TABLE STAFF ADD PRIMARY KEY (NAME)

10.删除已存在的约束

ALTER TABLE 表名 drop constraints 约束名

比如:

ALTER TABLE STAFF DROP CONSTRAINTS SYS_C003042

11.级联删除约束

在删除约束的同时,也删除跟该字段有关系的约束设置

ALTER TABLE DEPT DROP CONSTRAINT PK_DEPT CASCADE 该代码执行后:

emp表中的外键引用就不存在了

12.有效化和无效化约束

使用disable和enable关键词

ALTER TABLE EMP DISABLE CONSTRAINT PK_EMPNO

ALTER TABLE EMP ENABLE CONSTRAINT PK_EMPNO

13.从数据字典中查询

select * from user_constraint:查询对应的表

SELECT * FROM USER_CONS_COLUMNS:查询对应的列

Oracle常见的练习

1.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。 select empno,ename,sal from emp where sal >= 3000;

2.查询出emp表中所有员工一年的薪水，显示形式如下：

编号为XX的员工，姓名为XX，年薪XX。（提示：使用||将多个列合并）

select '编号为'||empno||'的员工，姓名为'||ename||',年薪为'||(sal*12)||'。' from emp;

3.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号员工名字薪水

select empno as "员工编号",ename as "员工名字",sal as "薪水" from emp where deptno = 20 and sal > 2000;

4.查询出emp表中所有的工作种类（无重复）

select distinct(job) from emp;

5.查询出所有奖金（comm）字段不为空的人员的所有信息。

select * from emp where comm is not null;

6.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）

select * from emp where sal >= 800 and sal <= 2500;

select * from emp where sal between 800 and 2500;

7.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）

select * from emp where empno = 7521 or empno = 7900 or empno = 7782;

select * from emp where empno in (7521,7900,7782);

8.查询出名字中有“A”字符，并且薪水在1000以上（不包括1000）的所有员工信息。 select * from emp where ename like '%A%' and sal > 1000;

9.查询出名字第三个字母是“M”的所有员工信息。

select * from emp where ename like '__M%';

10.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。

select * from emp order by sal asc,hiredate desc;

11.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。

select * from emp order by ename asc,sal desc;

12.显示所有员工名字的小写形式、薪水及部门编号。

select lower(ename),sal,deptno from emp;

13.查询出所有在30号部门的员工的员工名字、薪水和所在部门编号，其中名字按照首字母大写，其余小写的形式显示。

select initcap(ename),sal,deptno from emp where deptno = 30;

14.将所有人的名字截掉首字母后显示。

select substr(ename,2) from emp;

15.查询出emp表中所有名字长度在5以上（不包括5）人的名字、员工编号、薪水以及名字的长度。

select ename,empno,sal,length(ename) from emp where length(ename) > 5;

16.查询出emp表中不在10号部门所有人的名字、薪水和部门编号，将名字中所有的字符?E?替换成?%?显示。

select replace(ename,'E','%') as ename,sal,deptno from emp where deptno != 10;

17.查询出所有薪水在1500以上（不包括1500）的所有人的名字、薪水、入职时间，使用四舍五入的方式将所有薪水保留一位小数。

select ename,round(sal,1),hiredate from emp where sal > 1500;

18.查询出所有薪水在1800以下（包括1800）的所有人的名字、薪水、入职时间，使截取的方式将所有薪水小数截掉。

select ename,trunc(sal),hiredate from emp where sal <= 1800;

19.查询出最早工作的那个人的名字、入职时间和薪水。

select ename,hiredate,sal from emp where hiredate = (select min(hiredate) from emp);

20.查询出最晚工作的那个人的名字、入职时间。

select ename,hiredate from emp where hiredate = (select max(hiredate) from emp);

21.计算出最早入职的员工和最晚入职的员工的入职年份之差。

select max(TO_CHAR(hiredate,'yyyy'))-min(TO_CHAR(hiredate,'yyyy')) from emp;

22.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.

select ename,sal,NVL(comm,100) from emp;

23.使用nullif函数求出所有名字长度为6的员工的名字和部门编号。

select ename,deptno from emp where nullif(length(ename),6) is null;

24.显示出薪水最高人的职位。

select job from emp where sal = (select max(sal) from emp);

25.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。 select max(sal),min(sal),deptno from emp where deptno != 10 group by deptno;

26.查询出所有薪水在'ALLEN'之上的所有人员信息。

select * from emp where sal > (select sal from emp where ename = 'ALLEN');

27.查询出员工名字、薪水、入职年份、部门编号和部门名称。

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

28.查询出所有员工的名字以及其上司的名字。

select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;

29.查出所有员工的个人信息和部门信息（多表连接），暂时没有部门的也要查出来。 select * from emp e,dept d where e.deptno = d.deptno;

30.查询所有员工的信息和其薪水对应的级别。（salgrade：薪水分级表）

select e.*,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

31.在emp表中新添加一个员工：ename:ZHANGSAN empno:1234 job:manager

hiredate:2009-01-23

Insert Into emp(empno,ename,hiredate) Values(1234,'ZHANGSAN',to_date('2009-01-23','yyyy-mm-dd'));

32.将新添加的员工工作改为：ANALYST,薪水改为3000，入职时间加一年。

33.将新添加的员工的奖金改为和编号为7499一样高。（7499若没有奖金先为7499添加奖金100）

34.更新表中数据如下：Update emp Set deptno=55 Where empno=7499

能否更新成功，为什么？

35.将新添加的员工删除。

36.删除10号部门薪水最高的员工。

delete from emp where empno in (select empno from emp where sal =(select max(sal) from emp where deptno = 10));

37.将薪水最高的员工的薪水降30%。

update emp set sal = 0.7 * sal where sal = (select max(sal) from emp);

38.创建一个测试表，表名：test，其中有如下字段：id：Number类型4位，name：varchar2类型20位，birthday：Date类型，默认值为20xx年08月08号。

39.删除测试表中的birthday字段。

Alter Table test Drop Column birthday。

40.什么是数据库中的事务？

41.char和varchar2的区别？

char---定长字符串

varchar2----变长字符串

char(20) abcd---------20

varchar(20) abcd--------4

42.NUMBER(N)和number(m,n)的区别？

NUMBER(N):表示整数位不能大于n个,小数位可以任意

number(m,n):表示总长度为m位,其中小数位为n,整数为为m-n

43.delete和truncate的作用以及区别？

两种方式

A.delete删除

B.truncate清空表的内容

区别：

delete：从数据库的缓存区清除该数据

truncate：把数据删除了，然后清空所占用的空间

delete可以撤销

truncate不能撤销

truncate===delete+commit

TRUNCATE 语法

TRUNCATE TABLE EMP;

44.TRUNCATE和drop 区别？

drop：删除表的定义，整个对象删掉，删除的是对象的本身，全部

truncate：删除表的内容，只是删除数据，表的结果会保留

45.什么是主键，主键的作用是什么？可以举例阐述

46.什么是外键，外键的作用是什么？可以举例阐述

外来的键值,如EMP表的deptno字段引用了dept表中的deptno字段

我们认为:emp表中的deptno是一个外键,emp.deptno不能任意指定,只能从dept.deptno的值中任意选一个

47.什么是联合主键？

把多个字段合起来作为一个主键,要求多个字段每一项都不能为null,并且合起来的值不能重复.

相关推荐

Oracle实验总结

第二篇：oracle总结

专栏推荐