oracle理论学习详解及各种简单 *** 作例子怎么解决

1 数据库的发展过程

层次模型 -->网状模型 -->关系模型 -->对象关系模型

2 关于数据库的概念

DB:数据库（存储信息的仓库）

DBMS：数据库管理系统（用于管理数据库的工具）

RDBMS:关系型数据库管理系统

ORDBMS:对象关系型的数据库管理系统

3 Oracle数据库的主要特点

1)支持多用户、大事务量的处理

2)数据库安全性和完整性控制

3)支持分布式数据处理

4)可移植性

4Oracle一些常见问题？

1)如果我只有一张表，为什么我还要创建数据库

SQL语言要求所有表都需放在数据库里。这项设计当然有它好的理由。SQL能控制多为用户同时访问表的行为。能够授予或撤销对整个数据库的访问权。这有时比控制每张表的权限要简单很多

2)创建库的命令的字母全是大写，一定要这样吗？

有些系统确实要求某些关键字采用大写形式。但SQL本身不区分大小写。也就是说，命令不大写也可以,但命令大写是良好的SQL编程惯例。

3)给数据库、表和列命名时有什么主意事项吗？

创建具有描述性的名称通常有不错的效果。有时候要多用几个单词来命名。所有名称都不能包含空格，所以使用下划线能够让你创建更具描述性的名称。命名时最好避免首字母大写，因为SQL不区分大小写。极有可能会搞错数据库。

4)为什么不能直接把BLOB当成所有文本值的类型

因为这样很浪费空间。VARCHAR或CHAR只会占用特定空间。不会多于256字符。但BLOB需要很大的存储空间。随着数据库的增长，占用存储空间就是冒着耗尽硬盘空间的风险。另外，有些重要的字符串运算无法 *** 作BLOB类型的数据。只能用于VARCHAR或CHAR。

5)为什么需要INT和DEC这类数值类型？

节省数据库存储空间和效率有关。为表的没列选择最合适的数据类型可以为表瘦身，还可以使数据 *** 作更为快速。

5Oracle关系数据库基础

1)主键：表中其中一列或几列的组合，其值能唯一标识表中每一行。

表中任何列都可以作为主键，但要满足如下条件：

任何两行都不具有相同的主键值

每个行都必须具有一个主键值（主键列不允许为null值）

主键列中的值不允许修改或更新

主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）

一般以id或uuid作为主键的名字

2)外键是什么：

在一个关系（参照表）中是主键，而另一个关系引用这个键。那么这个键在另一个关系中就是外键。

3)外建能干什么：

使两个关系（表）形成关联，外键只能引用参照表中的主键。保持数据一致性,完整性。

如图：

4)如何在数据库内表示一对一

5)如何在数据库内表示一对多

6)关系模型的完整性约束是什么？

是通过关系的某种约束条件对关系进行约束。也就是说关系的值随时间变化时应该满足一些约束条件。如年龄不能超过1000，性别必须是男或者女

7)关系模型的完整性约束能有什么

实体完整性、参照完整性、用户自定义完整性。

实体完整性：针对基本关系而言，也就是一个二维表,主键不能为NULL

参照完整性：表之间存在关系，自然就存在关系的引用（外键）,表和表之间的关系通过外键实现,外键可以为NULL或引用表的主键

用户自定义完整性：针对不同的需求定义自己的完整性约束,如不允许学生编号中出现非数字字符，性别必须是男或者女

6 Oracle自学笔记

1)数据库和表的名称不一定要大写。

2)列是存储在表中的一块数据,行是一组能够描述某个事物的列的集合。列和行构成了表。

3)创建oracle数据库。使用oracle自带的Database Configuration Assistant 来创建库

4)使用DBA身份 创建表空间。具体sql如下：

create tablespace pzw datafile 'C:/oracle/pzwdbf' SIZE 1024M REUSE AUTOEXTEND OFF extent management local segment space

management auto;

5)删除用户命令

drop user pzw cascade;

6)删除表空间命令

DROP TABLESPACE pzw INCLUDING CONTENTS AND DATAFILES;

7 )查看表空间命令。

链接地址：查看oracle表空间的两种方式

8)创建用户。

create user pzw identified by pzw;

9)将包空间分配给用户

alter user pzw default tablespace pzw;

10)给用户授权

grant create session, create table, unlimited tablespace to pzw;

11)创建表

CREATE TABLE doughnut_list

(

doughnut_name VARCHAR(10),

doughnut_type VARCHAR(6),

doughnut_birthday DATE

);

12)删除表

DROP TABLE

doughnut_list;

13)给表中增加一列

Alter table EMP add sale number;

14)数据库插入一条数据

insert into DEPT_EMP_TABLE (DEPT_EMP_NO,emp_no,Dept_No,Joined_Date) values (009,'00002',1,to_date('2011-2-28 15:42:56','yyyy-mm-dd hh24:mi:ss'));

commit;

15)数据库修改一条数据

update emp set emp_name='张惠妹',age=20,sex='女',profession='流行歌手' where emp_no = '00002';

commit;

16)数据库删除一条数据

delete emp where emp_no = '000013';

commit;

17)查询全部数据

select from emp;

18)创建视图

create view adress_view as select from pzwadress;

以下为oracle演示数据 *** 作及练习题（对菜鸟及有用。使用scott 登陆。默认密码tiger）

1查看演示数据的表。

selectfromtab

或者selecttable_namefromuser_tables;

2查看表结构(plsql *** 作无效。使用命名提示符 可以 *** 作)

desc dept;

3查看员工姓名

select ENAMEfrom emp;

4查询员工的编号和明星（sql语句不区分大小写）

select empno, enamefrom emp;

5查询所有的字段

selectfromemp;

一般建议不使用号，使用号不明确，建议将相关的字段写到select语句的后面,使用号的效率比较低

6列出员工的编号,姓名和年薪。

select empno, ename,sal12from emp;

select语句中可以使用运算符，以上存在一些问题，年薪的字段名称不太明确

7将查询出来的字段显示为中文

select empnoas 员工编号, ename as 员工姓名, sal12 as 年薪 from emp;

可以采用as命名别名，as可以省略

如：可以采用as命名别名，as可以省略

8查询薪水等于5000的员工

select empno, ename, sal from emp where sal=5000;

如果是字符类型的数据进行比较的时候，是区分大小写的。

9查询薪水不等于5000的员工

select empno, ename, sal from emp where sal<>5000;

10查询工作岗位不等于manager的员工

select empno,ename,sal,job from emp where job<>

'manager';

在sql语句中如果是字符串采用单引号，引起来，不同于Java中采用双引号，如果是数值型也可以引起来,只不过是数值类型数据当成字符串来处理

11查询薪水为1600到3000的员工（第一种方式，采用>=和<=）

select empno, ename, sal from emp where sal>=1600 and sal<=3000;

查询薪水为1600到3000的员工（第一种方式，采用between and）

select empno,ename,sal,job from emp where salbetween

1600and 3000;

between …and …，包含最大值和最小值

between …and …，不仅仅可以应用在数值类型的数据上，还可以应用在字符数据类型上

between …and …，对于两个参数的设定是有限制的，小的数在前，大的数在后

12查询津贴为空的员工

select from emp where commis null;

13查询津贴不为空的员工

select from emp where commis not null;

14工作岗位为MANAGER,薪水大于2500的员工。

select empno, ename, sal from emp where job='MANAGER'and sal>2500;

and表示并且的含义，表示所有的条件必须满足

15查询出job为manager和job为salesman的员工。

select from emp where job='MANAGER'or job='SALESMAN';

or，只要满足条件即可,相当于或者

16查询薪水大于1800，并且部门编号为20 或者 30的

select from emp where sal>1800and (deptno=20or deptno=30);

17查询出job为manager和job为salesman的员工

select from emp where jobin('MANAGER','SALESMAN');

18查询job不等于MANAGER并且不能与SALESMAN的员工（第一种写法）

select from emp where job<> 'MANAGER' and job <> 'SALESMAN';

19 查询job不等于MANAGER并且不能与SALESMAN的员工（第二种写法）

select from emp where jobnot in('MANAGER','SALESMAN');

20查询以M开头的所有员工

select from emp where ename like 'M %';

21查询以T结尾的所有员工

select from emp where ename like '%T';

22查询以O结尾的所有员工

select from emp where ename like '%O%';

23查询姓名中第一个字符为A的所有员工

select from emp where ename like '_A%';

Like可以实现模糊查询，like支持%和下划线匹配

Like中%和下划线的差别？

%匹配任意字符出现任意次数

下划线只匹配一个任意字符出现一次

Like语句是可以应用在数值类型的数据上的，但是如果没有使用引号括起来的话，那么不能使用%和下划线。类似于等号的 *** 作，如果使用引号括起来的话，那么可以使用%和下划线，将数值类型的数据转换为字符类型后进行处理。

24按照薪水由小到大排序

s elect from emporder by sal;

如果存在where子句那么order by必须放到where语句的后面

25手动指定按照薪水由小到大排序

select from emp order by saldesc;

26 按照薪水和姓名排序

select from emp order by sal desc ,ename desc;

如果采用多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序

select from emp order by sal asc;

26手动指定按照薪水由大到小排序

select from emporder by sal desc;

27按照薪水升序(使用字段的位置来排序)

select from emp order by 6;

不建议使用此种方式，采用数字含义不明确，程序不健壮

28查询员工将员工姓名全部转换成小写。

select lower(ename)from emp;

29查询job为manager的员工

select from emp where job=upper('manager');

30查询姓名以M开头所有的员工

select from emp wheresubstr(ename, 1,1)='M';

方法的第二个参数表示的是查询字符的位置，0,1都表示第一个字符，负数表示从结尾开始的位置，第三个参数表示截取字符串的长度。

31取得员工姓名的长度

select length(ename) from emp;

32取得工作岗位为MANAGER的所有员工

select from emp where job=trim('MANAGER ');

trim会去首尾空格，不会去除中间的空格

33查询1986-02-20入职的员工（第一种方法，与数据库的格式匹配上）

select from emp where HIREDATE='20-2月 -81';

查询1982-02-20入职的员工（第二种方法，将字符串转换成date类型）

select from emp where hiredate=to_date('1981-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

to_date可以将字符串转换成日期，具体格式to_date(字符串，匹配格式)

34查询1981- 02-30以后入职的员工，将入职日期格式为yyyy-mm-dd hh:mm:ss

select empno, ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss')from emp where hiredate>to_date('1981-02-2000:00:00', 'YYYY-MM-DD HH24:MI:SS');

35查询员工薪水加入前分位

select empno, ename, to_char(sal, '$999,999') from emp;

36查询薪水加入千分位和保留两位小数

select empno, ename, to_char(sal, '$999,99900') fromemp;

将数字转换成字符串，格式

控制符

说明

9

表示一位数字

0

位数不够可以补零

$

美元符

L

本地货币符号

显示小数

,

显示千分位

37将字符串转换成数值

select from emp where sal>to_number('1,500','999,999');

38取得员工的全部薪水，薪水+津贴

select empno, ename, sal, comm, sal+nvl(comm,0) fromemp;

39如果job为MANAGER薪水上涨10%，如果job为SALESMAN工资上涨50%（case … when … then …end）

select empno, ename, job, sal, (casejobwhen 'MANAGER' thensal11when 'SALESMAN' thensal15end)as newsal from emp;

40如果job为MANAGER薪水上涨10%，如果job为SALESMAN工资上涨50%（decode）

select empno, ename, job, sal, decode(job,'MANAGER', SAL11, 'SALESMAN',sal15) as newsal from emp;

41四舍五入

select round(12345674567, 2) from dual;

Dual是oracle提供的，主要为了方便使用，因为select的时候需要用from

42聚合函数

count

取得记录数

sum

求和

Avg

取平均

Max

取最大的数

min

取最小的数

43取得所有员工人数

select count() from emp;

Count（）表示取得所有记录，忽略null，为null值也会取得

44取得津贴不为null的员工数

select count(comm) from emp;

采用count(字段名称)，不会取得为null的纪录

45取得工作岗位的个数

select count(distinctjob) from emp;

Distinct可以去除重复的纪录

46取得薪水的合计

select sum(sal) from emp;

47取得薪水的合计（sal+comm）

select sum(sal+nvl(comm, 0)) from emp;

48取得平均薪水

select avg(sal) from emp;

49取得最高薪水

select max(to_char(hiredate, 'yyyy-mm-dd')) from emp;

50取得最小薪水

select min(sal) from emp;

51取得最早入职的员工

select min(hiredate) from emp;

52可以将这些聚合函数都放到select中一起使用

select count(), sum(sal), avg(sal), max(sal),min(sal) from emp;

53取得每个岗位的工资合计，要求显示岗位名称和工资合计。

select job, sum(sal) from empgroupby job;

采用group by，非聚合函数所使用的字段必须参与分组，

Group by中不能使用聚合函数

如果使用了order by，order by必须放到group by后面

54。取得每个岗位的平均工资大于2000

select job, avg(sal) from emp group by job having avg(sal) >2000;

分组函数的执行顺序：

1、 根据条件查询数据

2、 分组

3、 采用having过滤，取得正确的数据

55 显示每个员工信息，并显示所属的部门名称

select ename ,dname from emp a ,dept b where adeptno = bdeptno;

以上查询也称为“内连接”，指查询相等的数据

56取得员工和所属的经理的姓名

select aename, bename from emp a, emp b whereamgr=bempno;

以上称为“自连接”，只有一张表连接，具体的查询方法

57（内连接）显示薪水大于2000的员工信息，并显示所属的部门名称

SQL99语法：

select ename,sal,dname from emp a join dept b on adeptno = bdeptno where sal>2000；

SQL92语法

select ename,sal,dname from emp a, dept b where adeptno=bdeptno and sal > 2000；

Sql92语法和sql99语法的区别：99语法可以做到表的连接和查询条件分离，特别是多个表进行连接的时候，会比sql92更清晰

58（外连接）显示薪水大于2000的员工信息，并显示所属的部门名称，如果某一个部门没有员工。那么该部门也必须显示出来

select dname,ename from emp a right join dept b on adeptno = bdeptno;

59查询员工名称和所属经历的名称，如果没有上级经理，也要查询出来

Select eename, mename mname from emp e, emp mwhere mempno(+) = emgr;

60查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

select ename,ename from emp where empno in (select distinct mgr from emp where mgr is not null);

61查询那些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水。

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

分析思路：首先根据文字描述找出被依赖的条件，逐次分析

62查询各个部门的平均薪水所属的等级，需要显示部门编号，平均薪水，等级编号

select adeptno, aavg_sal, bgrade from (select deptno, avg(sal) avg_sal fromemp group by deptno) a, salgrade b where aavg_sal between blosal and bhisal;

关键点：将子查询看作一张表

63查询员工信息以及部门名称

Select eempno, eename, edeptno, (select dname from dept where deptno = edeptno) as dname from emp e

64union可以合并集合（相加）

select from emp where job='MANAGER'

union

select from emp where job='SALESMAN'

65minus可以移出集合（相减）

查询部门编号为10和20的，取出薪水大于2000的。

select from emp where deptno in(10, 20)

minus

select from emp where sal>2000

66rownum隐含字段

select rownum, a from emp a;

67取得前5条数据

select from emp where rownum <=5;

68取得薪水最好的前5名

select empno, ename, sal from (select empno,ename, sal from emp order by sal desc)whererownum <=5

Oracle优化器在任何可能的时候都会对表达式进行评估 并且把特定的语法结构转换成等价的结构 这么做的原因是

·要么结果表达式能够比源表达式具有更快的速度

·要么源表达式只是结果表达式的一个等价语义结构

不同的SQL结构有时具有同样的 *** 作（例如 = ANY (subquery) and IN (subquery)） Oracle会把他们映射到一个单一的语义结构

下面将讨论优化器如何评估优化如下的情况和表达式

常量 LIKE *** 作符 IN *** 作符 ANY和SOME *** 作符 ALL *** 作符 BEEEN *** 作符 NOT *** 作符

传递（Transitivity） 确定性（DETERMINISTIC）函数

常量

常量的计算是在语句被优化时一次性完成 而不是在每次执行时 下面是检索月薪大于 的的表达式 · sal > / · sal > · sal >

如果SQL语句包括第一种情况 优化器会简单地把它转变成第二种

注意 优化器不会简化跨越比较符的表达式 例如第三条语句 鉴于此 应用程序开发者应该尽量写用常量跟字段比较检索的表达式 而不要将字段置于表达式当中

LIKE *** 作符

优化器把使用LIKE *** 作符和一个没有通配符的表达式组成的检索表达式转换为一个“=” *** 作符表达式

例如 优化器会把表达式ename LIKE SMITH 转换为ename = SMITH 优化器只能转换涉及到可变长数据类型的表达式 前一个例子中 如果ENAME字段的类型是CHAR( ) 那么优化器将不做任何转换

IN *** 作符

优化器把使用IN比较符的检索表达式替换为等价的使用“=”和“OR” *** 作符的检索表达式 例如 优化器会把表达式ename IN ( SMITH KING JONES )替换为: ename = SMITH OR ename = KING OR ename = JONES

ANY和SOME *** 作符

优化器将跟随（following）值列表的ANY和SOME检索条件用等价的同等 *** 作符和“OR”组成的表达式替换

例如 优化器将如下所示的第一条语句用第二条语句替换 · sal > ANY (:first_sal :second_sal) · sal > :first_sal OR sal > :second_sal

优化器将跟随子查询的ANY和SOME检索条件转换成由“EXISTS”和一个相应的子查询组成的检索表达式

例如 优化器将如下所示的第一条语句用第二条语句替换 · x > ANY (SELECT sal FROM emp WHERE job = ANALYST ) · EXISTS (SELECT sal FROM emp WHERE job = ANALYST AND x > sal)

ALL *** 作符

优化器将跟随值列表的ALL *** 作符用等价的“=”和“AND”组成的表达式替换

例如 sal > ALL (:first_sal :second_sal)表达式会被替换为 sal > :first_sal AND sal > :second_sal

对于跟随子查询的ALL表达式 优化器用ANY和另外一个合适的比较符组成的表达式替换 例如 优化器会把表达式 x > ALL (SELECT sal FROM emp WHERE deptno = ) 替换为 NOT (x <= ANY (SELECT sal FROM emp WHERE deptno = ))

接下来优化器会把第二个表达式适用ANY表达式的转换规则转换为下面的表达式 NOT EXISTS (SELECT sal FROM emp WHERE deptno = AND x <= sal)

BEEEN *** 作符

优化器总是用“>=”和“<=”比较符来等价的代替BEEEN *** 作符 例如 优化器会把表达式sal BEEEN AND 用sal >= AND sal <= 来代替

NOT *** 作符

优化器总是试图简化检索条件以消除“NOT”逻辑 *** 作符的影响 这将涉及到“NOT” *** 作符的消除以及代以相应的比较运算符

例如 优化器将下面的第一条语句用第二条语句代替 · NOT deptno = (SELECT deptno FROM emp WHERE ename = TAYLOR ) · deptno <> (SELECT deptno FROM emp WHERE ename = TAYLOR )

通常情况下一个含有NOT *** 作符的语句有很多不同的写法 优化器的转换原则是使“NOT” *** 作符后边的子句尽可能的简单 即使可能会使结果表达式包含了更多的“NOT” *** 作符 例如 优化器将如下所示的第一条语句用第二条语句代替 · NOT (sal < OR m IS NULL) · NOT sal < AND m IS NOT NULL sal >= AND m IS NOT NULL

传递（Transitivity）

如果“WHERE”子句的两个检索条件涉及了一个共同的字段 优化器有时会根据传递原理推断出第三个检索条件 随后可以根据这个推断出的条件对语句 进行优化 推断出的条件可能会激活一个原来的检索条件没有激活的潜在的接口路径（access path） 注意 传递仅仅被用在基于代价（cost based）的优化中

假设有一个这样的包含两个检索条件的“WHERE”子句 WHERE 字段 常量 AND字段 = 字段 在这个例子里 优化器会推断出新的检索条件 字段 常量 在这里 是比较运算符= != ^= <> > <= 或 >=之中的任何一个 常量是指任何一个涉及了 *** 作符 SQL函数 文字 绑定变量（bind variables）或者关联变量（correlation variables）的常量表达式

例如 考虑这样一个包含两个各自使用了字段EMP DEPTNO的检索条件的WHERE子句的查询 SELECT FROM emp dept WHERE emp deptno = AND emp deptno = dept deptno;

使用传递优化 优化器会推断出如下条件 dept deptno = 如果有索引存在于EMP DEPTNO字段上 这个条件会使调用这个索引的接口路径有效 注意 优化器只能对字段关联常量的表达式进行推断 而不是 字段关联字段的表达式 例如 包含这样条件的WHERE子句 字段 字段 AND 字段 = 字段 这种情况不能推断出表达式 字段 < p_oper> 字段

确定性（DETERMINISTIC）函数

在某些情况下 优化器能够使用先前的函数返回结果而不是重新执行用户定义的函数 这仅仅对那些以限制的方式来执行的函数来说是有效的 这些函数必须 对任何的输入都有同样的返回值 函数的结果必须不能因为包（PACKAGE）变量 数据库或会话（SESSION）的参数（例如NLS参数）不同而变化 如果函数在将来重新定义 返回值必须对任何参数来说仍然与以前的返回值相同 函数的创建者可以在以CREATE FUNCTION CREATE PACKAGE或者CREATE TYPE声明函数时根据以上的要求使用DETERMINISTIC关键字向数据库申明该函数为确定性函数 数据库不会对确定性函数的合法性进行校验 即使 一个函数明显的使用了包变量或 *** 作了数据库 仍然可以被定义为确定性函数 这就是说如何安全合法的使用和定义确定性函数是程序员的责任

lishixinzhi/Article/program/Oracle/201311/19040

(+)是外连接，表示当前条件等号左侧的表为主表，如果等号条件成立，查询中如果有等号右侧表中的字段，按照关联条件查询出数据，如果右侧没有条件符合，那么查询中补空。

举个例子，假设emp和dept表数据如下：

emp: emp_id, emp_name, dept_id

001 张三 10

002 李四 10

003 王五 20

004 赵六 30

dept: dept_id dept_name

10 部门1

20 部门2

查询语句：select emp_id, emp_name, dept_name

from emp, dept

where empdept_id = deptdept_id(+);

从上面两表能看出来，emp表中的最后一行数据，dept_id为30，在dept表没有对应的数据，使用直连（即不带加号）只能查询到前三行数据，可是使用外连，以emp为主表，那么emp表的数据就都可以查到。结果如下：

emp_id emp_name dept_name

001 张三 部门1

002 李四 部门1

003 王五 部门2

004 赵六 NULL(空，没有数据)

表（table）是数据库中用来存储数据的对象，是有结构的

数据库中的表

数据的集合，是整个数据库系统的基础。

sql数据库中用于存储数据的工具。

表是包含数据库中所有数据的数据库对象。

　

　表定义为列的集合。与电子表格相似，数据在表中式按行和列的格式组织排列的。

表中的每一列都设计为存储某种类型的信息（例如日期、名称、美元金额或数字）。

表上有几种控制（约束、规则、默认值和自定义用户数据类型）用于确保数据的有效性。

oracle数据库中所有的数据都存在于表空间内表空间是一个逻辑的结构;你无法通过观察 *** 作系统看到表空间

每一个表空间都由叫做数据文件的结构组成;每一个表空间必须包含一个或多个数据文件,每个数据文件仅属于一个表空间

在创建表时,可以在那个表空间中创建表接着,oracle会将这个表安放在组成表空间的一个数据文件中

就oracle数据库10g而言,oracle为所有类型表指定的默认表空间就是本地管理的表空间

一个用户可以把数据放在一个或者多个表空间里

,一个表空间里有很多表,一张表存放在一个表空间里用户可以访问表空间里的表

可以直接在SQL表达式里面写上加减运算符号。

比如update语句：

update table_a set column_a= column_a +4 where column_a<1000

比如select 语句:

select column_a+100 from table_a where column_a<1000

Oracle存储过程基本语法： CREATE OR REPLACE PROCEDURE 存储过程名 IS BEGIN NULL; END;解释： 行1: CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它; 行2: IS关键词表明后面将跟

Oracle数据库基本 *** 作

1概述

Oracle数据库客户端一般需要安装在服务器上，可以在服务器端 *** 作，一般我们可以用sql developer工具远程连接到数据库，先行建立数据库，然后对表进行增删改查。也可以使用MyEclispse的DB Broswer工具进行连接数据库并进行简单的增删改查。

2．SQL语句

Data Definition Language（DDL）：

主要用于建立、修改、删除数据库对象（表），不需要事务的参与CREATE:创建表

CREATE TABLE emp(

id NUMBER(10),

name VARCHAR2(20),

gender CHAR(1),

birth DATE,

salary NUMBER(6,2),

);

DESC :查询表结构

DESC emp;

RENAME:重命名表名

RENAME emp TO employee;

DROP：删除表

DROP TABLE emp;

ALTER:列（字段） *** 作

ADD：增加字段

ALTER TABLE employee ADD (birth DATE DEFAULT sysdate);MODITY：修改字段

ALTER TABLE employee MODIFY (name VARCHAR2(40) DEFAULT 'CLERK' );DROP：删除字段

ALTER TABLE employee DROP (birth);

Data Manipulation Language（DML）

用于对数据记录进行 *** 作，包括插入，删除，修改。需要commit才能真正确认 *** 作，如果需要撤销则rollback。

INSERT INTO：插入数据

INSERT INTO employee(id, name, salary) VALUES(1001, 'rose', 5500);UPDATE…SET：更新数据

UPDATE employee SET salary = 8500 WHERE name = 'ROSE';DELETE FROM：删除记录

DELETE FROM employee WHERE job is null;

Transaction Control Language（TCL）

事务控制语言，包括commit提交，rollback回滚，savepoint保存点（可以回退到指定保存点）。在sql developer中为图标。

Data Query Language（DQL）

SQL基础查询：

查询语句的执行顺序

FROM 子句：执行顺序为从后往前、从右到左。数据量较少的表尽量放在后面。

WHERE子句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE 子句的最右。

GROUP BY：执行顺序从左往右分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。

HAVING 子句：消耗资源。尽量避免使用，HAVING 会在检索出所有记录之后才对结果集进行过滤，需要排序等 *** 作。

SELECT子句：少用号，尽量取字段名称。ORACLE 在解析的过程中, 通过查询数据字典将号依次转换成所有的列名, 消耗时间。

ORDER BY子句：执行顺序为从左到右排序，消耗资源。

也就是从哪张表中以什么样的限制条件确定数据内容，再通过分组与进一步限制分组条件得到一个处理过的数据，最后选择出来，如有需要，则对最后选择的数据进行排序。

FROM子句：

From后接表名，from前可以可以是，但是不建议（查询效率低），一般接要查询的字段名。分组函数，字段和表名都可以使用别名，不加引号数据库中为大写，加引号别名可以用空格小写等。

WHERE子句：

当查询条件中和数字比较，可以使用单引号引起，也可以不用，当和字符及日期类型的数据比较，则必须用单引号引起。

使用条件判断：>，<，<=，<=，!=，<>，=。

SELECT ename, sal FROM emp WHERE sal< 2000;SELECT ename, sal, job FROM emp WHERE job = 'SALESMAN';SELECT ename, sal, hiredate FROM emp

WHERE hiredate>todate('2002-1-1','YYYY-MM-DD');使用AND/OR关键字

如果希望返回的结果必须满足多个条件，应该使用AND逻辑 *** 作符连接这些条件如果希望返回的结果满足多个条件之一即可，应该使用OR逻辑 *** 作符连接这些条件SELECT ename, sal, job FROM emp

WHERE sal> 1000 AND job = 'CLERK';

使用LIKE条件

SELECT ename, job FROM emp WHERE ename LIKE '_A%';（第二个字母为A）‘_A%’_表示占位一个字符，%表示0到多个字符。

使用IN/NOT IN

在WHERE子句中可以用比较 *** 作符IN(list)来取出符合列表范围中的数据。其中的参数list表示值列表，当列或表达式匹配于列表中的任何一个值时，条件为TRUE，该条记录则被显示出来。

SELECT ename, job FROM emp WHERE job IN ('MANAGER', 'CLERK');使用BETWEEN 1 AND 2

用来查询符合(1，2)值域范围之内的数据，通常查询数字和日期类型的数据范围。

SELECT ename, sal FROM emp

WHERE sal BETWEEN 1500 AND 3000;

使用IS NULL和IS NOT NULL

条件筛选值为空的记录，空值不能用‘=’来判断。

使用ANY和ALL条件

在比较运算符中，可以出现ALL和ANY，表示“全部”和“任一”，> ANY : 大于最小< ANY：小于最大> ALL：大于最大< ALL：小于最小SELECT empno, ename, job, sal, deptno

FROM emp WHERE

sal> ANY (SELECT sal FROM emp WHERE job = 'SALESMAN');查询条件中使用算术表达式

当查询需要对选出的字段进行进一步计算，可以在数字列上使用算术表达式(+、-、、/)。表达式符合四则运算的默认优先级，如果要改变优先级可以使用括号。

SELECT ename, sal, job FROMempWHERE ename = UPPER('rose');SELECT ename, sal, job FROM empWHERE sal 12 >100000;使用DISTINCT过滤重复

DISTINCT必须紧跟SELECT，后面可以有多个字段，表示过滤掉多个字段都重复的选项，第二例中不显示重复的deptno和job相同的记录。

SELECT DISTINCT deptno FROM emp;

SELECT DISTINCT deptno, job FROM emp;

使用ORDER BY子句

对查询数据按照一定的规则排序，则使用order by子句，order by子句必须为select子句的最后一个子句。默认升序排列，从小到大，ASC表示升序。DESC表示降序，从大到小。

SELECT ename, sal FROM emp

ORDER BY sal DESC;

Order by多个列，每个列需要单独指定排序规则，先按照第一个字段排序，当第一个字段的值相同时，再按照第二个字段的值排序。

聚合函数分组函数

当需要统计的数据并不能在表里直观列出，而是需要根据现有的数据计算得到结果，这种功能可以使用聚合函数来实现。

因为是多行数据参与运算返回一行结果，也称作分组函数、多行函数、集合函数。用到的关键字：GOURP BY 按什么分组，HAVING进一步限制分组结果MAX和MIN

用来取得列或者表达式的最大最小值，包括数字，字符和日期。

SELECT MAX(sal) max_sal, MIN(sal) min_sal FROM emp;AVG和SUM

用来统计列或者表达式的平均值和求和，只能处理数字类，并且平均值忽略NULL。

SELECT AVG(sal) avg_sal, SUM(sal) sum_sal FROM emp;COUNT

用来计算表中的记录条数，同样忽略NULL。

SELECT COUNT(job) total_num FROM emp;

空值的 *** 作

NVL(expr1, expr2)：将NULL转变为非NULL值。如果expr1为NULL，则取值expr2, expr2是非空值。

NVL2(expr1, expr2, expr3)：和NVL函数功能类似，都是将NULL转变为非空值。NVL2用来判断expr1是否为NULL，如果不是NULL，返回expr2，如果是NULL，返回expr3。

GROUP BY子句

Group by是表示对表中某个字段进行分组，值相同为一组，而分组函数的意思则是对这每一个组进行计算，平均值或是最大最小值。

HAVING子句

是对分组后的结果进行进一步的限制，HAVING子句必须紧跟在GROUP BY子句后，不能单独存在。限制分组条件不能放在WHERE子句中。

SELECT deptno, MAX(sal) max_sal FROM emp

GROUP BY deptno HAVING MAX(sal) >4000;

SQL关联查询：

概述，在实际应用中，往往我们所需要的数据是分布在不同的表上的，我们想要获取数据必须跨表格查询。

关联有两种方式：

SELECT table1column, table2column

FROM table1, table2

WHERE table1column1 = table2column2;

SELECT table1column, table2column

FROM table1JOIN table2

ON(table1column1 = table2column2);

关联查询有三种连接方式

内连接返回关联表中所有满足条件的记录（也称等值连接）外连接返回一些不满足条件的记录，外连接有左外连接，右外连接和全外连接三种形式其中左外连接返回左边所有记录而不必管右边是否匹配，如不匹配则为NULL值，右外连接返回右边所有记录而不必管左边的记录是否匹配，如不匹配则默认NULL，全外连接返回左边和右边所有的数据，左右边不匹配的数据，对应的其他字段值为NULL，格式如下

JOIN ON式

SELECT table1column, table2column

FROM table1 [LEFT | RIGHT | FULL] JOIN table2ON table1column1 = table2column2;

WHERE式

Select from dave a,bl b where aid=bid(+);+号的位置和意义

‘+’号加在哪个表，哪个表就是关联表，另一张表就是基表。基表全部显示，关联表匹配显示。+在左边就是右外连接，+在右边就是左外连接。

自连接：

表示数据的来源是同一张表的内容，即将一张表中的不同列进行连接，可以是等值或者不等值。实现方式是利用别名将一张表看作两张表。

SELECT workerempnow_empno,workerenamew_ename, managerempnom_empno, managerenamem_enameFROM emp worker join emp manager

ON workermgr = managerempno;

SQL高级查询：

子查询：

子查询用在WHERE里

在SELECT中，往往WHERE的限制条件并不是一个确定的值，而是来源于另一个查询结果，即需要在另一个查询结果的基础上进行查询，这个时候为另一个查询提供数据的查询就叫做子查询。

SELECT eename, ejob FROM emp e

WHERE ejob = (SELECT job FROM emp WHERE ename = 'SCOTT');可以与多行/单行比较 *** 作符混合使用。

子查询用在HAVING子句

SELECT deptno, MIN(sal) min_sal FROM emp GROUP BY deptnoHAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 30);表示分组条件需要满足的条件。可以把子查询当成一个结果。

子查询用在FROM部分

子查询用在FROM子句中，子查询可称为行内视图或者匿名试图，可以把它当成一张单独的表，用别名进行标识。

子查询用在SELECT子句中

可以认为是外连接的一张表现

分页查询：

ROWNUM

伪列，返回标识行数据顺序的数字，伪列并不是真正的列数据，只是用来显示行数，并不能单独作为一列进行分组 *** 作。

ROWNUM的结果从第一行数据之上开始，每查询到一条数据则指针下移一个，所以只有查询到数据后才能够出现伪列数字，因此不能直接用在where里，如果要利用ROWNUM截取结果集的部分数据，可以将含有ROWNUM伪列的SELECT子句放在FROM内，作为视图，供外部的SELECT语句使用，此时的伪列已形成顺序数据，可以进行分组函数 *** 作。

也就是将ROWNUM先作为行内视图的一个列，在主查询中就可以使用这个列值作为条件。

SELECT FROM (SELECT ROWNUMrn , e FROM emp e )WHERE rn BETWEEN 8 AND 10;

使用子查询和ROWNUM进行分页

分页 *** 作需要有一个作为分页标准的数据，该数据线进行排序，排序的结果作为视图被父查询用伪列标识顺序数字，然后在最外面的爷爷查询对伪列进行分页。

如：

SELECT FROM

(SELECT ROWNUMrn , t FROM

(SELECT empno,ename,sal FROM emp

ORDER BY sal DESC) t

)

WHERE rn BETWEEN 8 AND 10;

DECODE函数

DECODE (expr, search1, result1[, search2, result2…][, default])它用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果，可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值。default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。

SELECT ename, job, sal,

DECODE(job, 'MANAGER', sal 12,

'ANALYST', sal 11,

'SALESMAN', sal 105,

sal) bonus

FROM emp;

SELECT deptno, dname, loc

FROM dept

ORDER BY

DECODE(dname, '研发部',1,'市场部',2,'销售部',3), loc;分组函数

ROW_NUMBER

ROW_NUMBER()

OVER (PARTITION BY deptno ORDER BY empno)根据deptno分组，在分组内根据empno内排序，比ROWNUM功能更强。可以直接从结果集中取出子集RANK

RANK() OVER( PARTITION BY col1 ORDER BY col2)功能与上相同，不同在于存在并列，并列第二跳过第三直接第四的规则。

DENSE_RANK

如果有并列第二，下一个排序将是三。

高级分组函数

ROLLUP

如果对两个字段rollup，那么第一个字段相同的值会当成一组，如果有分组函数求和，就会对第一字段所有的数据求和，单独成行。此时第二字段值为空。可以用于统计年度销售（工资）和。

CUBE

CUBE函数对字段进行排列统计，比如三个字段的CUBE运算，将三个字段排列成6种情况具体需要使用的时候再参考百度。

GROUPING（）

GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。案例情况复杂，具体使用时再说。

集合 *** 作

UNION、UNION ALL、INTERSECT、MINUS

Union表示取多次SELECT结果的并集，如果去掉重复的数据。

Union All与union功能一样，只是它不会去掉重复的数据，会全部显示。

Intersect表示相交，多次查询后去相同的数据，即同时满足两个查询条件的数据Minus表示差集，即将第一个结果集中的数据，减去第二个结果集的数据。即满足第一个查询条件，而不满足第二查询条件的数据。

Data Control Language（DCL）:

用于执行权限的授予和收回 *** 作、创建用户等，包括授予(GRANT)语句，收回(REVOKE)语句，CREATE USER语句，其中GRANT用于给用户或角色授予权限， REVOKE用于收回用户或角色已有的权限。DCL语句也不需要事务的参与，是自动提交的。

GRANT CREATE VIEW TO tarena;

3视图、索引、序列、约束

视图

视图本质上是一条SELECT语句，当SELECT子查询在from子句中，可以把SELECT子句的结果当作一个视图。视图本身只是基表的映射，只是把基表中的数据显示出来，可以把视图当成表看待，所有 *** 作都与表 *** 作极其相似。

视图有三种，简单视图不包括函数，复杂视图包含了分组函数等附加的内容，连接视图是基于多个表的。

视图的优劣点：

如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可，简化复杂查询；视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，对基表中的其它列起到安全和保密的作用，可以限制数据访问。

同时因为视图本质是基表的数据，所以对视图的 *** 作会影响到基表从而不安全。这里可以使用WITH READ ONLY来限制对视图的DML语言。

CREATE OR REPLACE VIEW v_emp_10

AS

SELECT empno, ename, sal, deptno FROM empWHERE deptno = 10

WITH READ ONLY;

使用DCL语句可以授权用户创建视图的权限，

GRANT CREATE VIEW TO tarena;

创建简单视图

CREATE VIEW v_emp_10

AS

SELECT empno, ename, sal, deptno

FROM emp

WHERE deptno = 10;

查询视图

SELECT id, name, salary FROM v_emp_10;

对视图的DML *** 作

简单视图可以进行DML *** 作，但是 *** 作的对象必须是基表里视图包含的字段，即对视图可见。而且简单视图的DML *** 作会对影响基表数据。

WITH CHECK OPTION短语表示，通过视图所做的修改，必须在视图的可见范围内，无论是INSERT UPDATE DELETE *** 作都必须在视图范围内，超过视图范围不可用。

CREATE [OR REPLACE] VIEW view_name[(alias[, alias…])]

AS subquery

[WITH CHECK OPTION];

删除视图

DROP VIEW v_emp_10;

对视图的删除不会导致基表数据的丢失，不会影响基表数据。

序列

序列(SEQUENCE)是一种用来生成唯一数字值的数据库对象。序列的值由Oracle程序按递增或递减顺序自动生成，通常用来自动产生表的主键值，是一种高效率获得唯一键值的途径。

序列是独立的数据库对象，和表是独立的对象，序列并不依附于表。

通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值。

CREATE SEQUENCE [schema]sequence_name

[ START WITH i ] [ INCREMENT BY j ]

[ MAXVALUE m | NOMAXVALUE ]

[ MINVALUE n | NOMINVALUE ]

[ CYCLE | NOCYCLE ][ CACHE p | NOCACHE ]

创建一个序列，起始数据是100，步进是10：

CREATE SEQUENCE emp_seq

START WITH 100

INCREMENT BY 10;

NEXTVAL：获取序列的下个值

CURRVAL：获取序列的当前值

当序列创建以后，必须先执行一次NEXTVAL，之后才能使用CURRVAL。

获取序列的第一个值，并且使用序列值为EMP表插入新的记录：

删除序列

DROP SEQUENCE emp_seq;

索引

创建索引

CREATE [UNIQUE] INDEX index_name

ON table(column[, column…]);

index_name表示索引名称

table表示表名

column表示列名，可以建立单列索引或复合索引UNIQUE表示唯一索引

CREATE INDEX idx_emp_job_sal ON emp(job, sal);可以增加函数

CREATE INDEX emp_ename_upper_idx

ON emp(UPPER(ename));

重建索引，提高索引空间利用率

ALTER INDEX index_name REBUILD;

删除索引

DROP INDEX idx_emp_ename;

为提升查询效率，创建和使用索引的原则：

1为经常出现在WHERE子句中的列创建索引

2为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致3为经常作为表的连接条件的列上创建索引

4不要在经常做DML *** 作的表上建立索引

5不要在小表上建立索引

6限制表上的索引数目，索引并不是越多越好

7删除很少被使用的、不合理的索引

约束

CONSTRAINT

非空约束(Not Null)，简称NN

创建表时添加约束

CONSTRAINT employees_hiredate_nn NOT NULL修改表时增加非空约束

ALTER TABLE employees

MODIFY (eid NUMBER(6) NOT NULL);

修改表时取消非空约束

ALTER TABLE employees

MODIFY (eid NUMBER(6));

唯一性约束(Unique)，简称UK

以上就是关于oracle理论学习详解及各种简单 *** 作例子怎么解决全部的内容，包括:oracle理论学习详解及各种简单 *** 作例子怎么解决、如何对Oracle中的优化器进行评估优化、oracle语句的级联问题，这个语句e.mgr=m.empno(+) 谁可以给我讲讲这个是什么意思还带有（+）详细解答哦！等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！