韩顺平oracle学习笔记
第0讲：如何学习oracle
一、如何学习oracle
Oracle目前最流行的数据库之一，功能强大，性能卓越。学习oracle需要具备一定基础：
1.学习过一门编程语言（如：java ，c）
2.最好学习过一门别的数据库（sql server，mysql ， access）
教程推荐：oracle使用教程， 深入浅出oracle
记住：欲速则不达，做任何事情要遵循他的规律，循序渐进，信心很重要
成为一个oracle高手过程：理解小知识点->做小练习->把小的只是点连成线->做oracle项目->形成只是面->深刻理解
Oracle基础部分：oracle基础使用； oracle用户管理； oracle表管理
Oracle高级部分：oracle表的查询； oracle的函数； oracle数据库管理；oracle的权角色； pl/sql 编程； 索引，约束和事物。
期望目标：
1 学会安装、启动、卸载oracle
2 使用sql *plus工具
3 掌握oracle用户管理
4 学会在oracle中编写简单的select语句
第1讲：基础语法
内容介绍：
1.为什么学习oracle
2.介绍oracle及其公司的背景
3.学会安装、启动、卸载oracle
4.oracle开发工具
5.Sql*plus的常用命令
6.oracle用户管理
一、主流数据库包括：
●微软：sql server 和 access
●瑞典：mysql AB公司
●IBM公司：DB2
●美国sybase公司：sybase
●IBM公司：infromix
●美国oracle公司：oracle(目前最流行的之一)
数据库分类：
|
小型数据库 |
中型数据库 |
大型数据库 |
|
access |
mysql sql server informix |
Sybase Oracle DB2 |
|
负载量在100人内，比如板，信息系信息留言统。 |
比如在负载日访问量5000-15000 |
负载可以处理海量数据库 sybase<oracle<db2 |
|
成本低 安全性要求不高 |
成本在万元内 比如商务网站 |
大型数据库的安全性能高，价格也很昂贵！万元以上 |
二、oracle安装，启动及卸载
1.系统要求：
操作系统最好为windows2000
内存最好在256M以上
硬盘空间需要2G以上
2.oracle安装会自动的生成sys和system两个用户
说明：
1Sys用户 是 超级用户，具有最高权限，具有sysdba角色，create database的权限，默认密码是manager
2System 用户 是 管理操作员，权限也很大，具有sysoper角色，没有create database权限，默认密码是 change_on_install
3一般讲，对数据库维护，使用system用户登录就可以了
3.启动oracle
右键单击我的电脑->服务和应用程序：服务->启动OracleServiceMYORA1(MYORA1是安装oracle时起的名字各有不同)和OracleOracleHome90TNSLlistener
4.卸载oracle
1、先关掉oralce，net stop OracleServiceORCL(ORCL是我的实例名字，换成你的)，或者去我的电脑服务中关闭
2、开始－＞程序－＞Oracle - oracle的版本号，我的是10ghome－＞Oracle Installation Products－＞ Universal Installer 卸载oracle
3、进注册表，regedit，删除选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE下所有的key。HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services、HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application这个里面所有有oracle这个字眼的，删除
4、删除c盘\Program Files\Oracle目录的东西以及oracle安装目录下所有的文件以及文件夹
5、环境变量中删除有关oracle的classpath和path
三、oracle开发工具
Sqlplus是Oracle自带的工具软件，主要用于执行sql语句，pl/sql块。
如何使用：
1)在开始à程序àoracle orachome90à
application developmentàsql*plus(这个是在dos下操作)
2)在运行栏中输入：sqlplusw或者sqlplus即可
3)在开始à程序àoracle orahome90à
Application developmentàsql*plus worksheet
4)pl/sql developer 属于第三方软件，主要用于开发，测试，优化oracle pl/sql 的存储过程.如：触发器，此软件oracle不带，需要单独安装。
5)oracle的企业管理器(oem oracle enterprise manager)
位置在 开始à程序àoracleàoracle oraclehome90àenterprise manager console
即可启动oracle的企业管理器，是一个图形界面环境
四、Sql*plus的常用命令
1.连接命令
(1) conn[ect]
用法：conn 用户名/密码 @ 网络服务名[as sysdba/sysoper](角色登陆)当用特权用户身份连接时，必须带上as sysdba 或是 as sysoper
例：conn scott/tiger;
show user; 显示当前用户
2.断开连接命令
(2) disc[onnect]
说明：该命令用来断开与当前数据库的连接
(3)passw[ord] 说明：该命令用于修改用户名的密码。如果要想修改其他用户的密码，需要用sys/system登陆。
(4) show user
说明：显示当前用户名
(5) exit
说明：该命令会断开与数据库的连接，同时会退出sql*plus
3.文件操作命令
(1) start和@
说明：运行sql脚本
案例：sql>@ d:\a.sql 或者 sql>start d:a.sql
(2) edit
说明：该命令可以编辑指定的sql脚本。
案例：sql>edit d:\a.sql
(3) spool
说明：该命令可以将sql*plus屏幕上的内容输出到指定文件中去。
案例：sql>spool d:\b.sql 并输入 sql>spool off
4．交互式命令
(1)＆
说明：可以替代变量，而该变量在执行时，需要用户输入。如：sql>select * from emp where job=‘&job’ 输入job是注意大小写
(2) edit
说明：该命令可以编辑指定的sql脚本
案例： sql>edit d:\a.sql
5.显示和设置环境变量
概述：可以用来控制输出的各种格式，set show 如果希望永久的保存相关的设置，可以去修改 glogin.sql脚本
(1)linesize
说明：设置显示行的宽度，默认是80个字符
sql>show linesize
sql>set linesize 90
(2)pagesize
说明：设置每页显示的行数目，默认是14，用法和linesize一样，至于其他环境参数的使用也是大同小异
sql>set pagesize 8 实际得到的不是14/8=2页 而是：14/（8-3）=5页
五Oracle用户的管理
1.创建用户
概述：在oracle中要创建一个新的用户使用create user语句，一般是具有dba（数据库管理员）的权限才能使用。
create user 用户名 identified by 密码；
2.给用户修改密码
概述：如果给自己修改密码可以直接使用 sql>password 用户名
如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限
sql>alter user 用户名 identified by 新密码
3.删除用户
概述：一般以dba的身份去删除某个用户，如果用其他用户去删除用户则需要具有 drop user的权限。
比如 drop user 用户名【cascade】
注意：在删除用户时，如果要删除的用户已经创建了表，就需要在删除的时候带一个参数cascade
4.用户管理的综合案例
概述：创建的新用户是没有任何权限的，甚至连登陆的数据库的权限都没有，需要为其指定相应的权限。给一个用户赋权限（connect登陆，dba管理员,resource建表）使用命令grant,回收权限使用命令revoke.
为了给讲清楚用户管理，给大家举一个案例。
案例：
新建一个用户lady 并给该用户赋可登陆可创建表
Sql>create user lady identified by lady123(密码)
受登陆权限:àgrant connect to lady
受可创建表：grant resource to lady
可以多个同时授权：grant connect，resource to lady;
注意授权用户级别应该为（dba以上sys/system）
还可以使用PL/SQL工具创建：文件->新建->用户
现在我要把scott用户里面的emp表的权限赋给lady用户但lady只能有对emp查询权限：
grant select on emp to lady;(授权用户级别应该：授权本身，或者sys/system)
lady 查询emp的方式：select * from scott.emp；
案例：同上我想把update的权限也赋给leng à grant update on emp to leng;
2)我想把emp所有的(增，删，改，查/insert,delete,update,select这四个一起可以用all代替)
操作权限赋给leng
grant all on emp to leng;(此时登陆用户为scott)
案例2：这时我想把权限收回来怎么办呢？
这时用关键字revoke（撤回）à
revoke select on emp from lady; 收回lady的查权限
案例：
现在scott希望吧查询emp表权限通过lady用户继续给别的用户？
Scott > grant select on emp to lady with grant option;
Leng > grant select on scott.emp to 新建用户
案例：
scott想把emp表的权限从leng用户赋权给新用户yoyo;
Scott> grant select on emp to leng with grant option;
Leng> grant select on scott.emp to yoyo;
Yoyo> select * from scott.emp;
如果是对象权限，就加入with grant option
例：grant select on emp to xiaoming with grant option
如果是系统管理权限，system 给xiaoming权限时：
例：grant connect to xiaoming with admin option
如果scott把xiaoming 对emp表的查询权限回收，那么xiaohong会怎么样
Scott->xiaoming->xiaohong ，结果就是xiaoming,xiaohong,都不能查询emp表
5.oracle用户的管理
使用profile管理用户口令
概述：profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名称为default的profile.当建立用户没有指定profile选项，那oracle就会将default分配给用户。
(1)账户锁定
概述：指定该用户锁定登陆时最多多可以输入密码的次数，也可以指定用户锁定的时间一般用dba的身份去执行该命令
例子：指定scott这个用户最多只能尝试3次登陆
使用profile管理用户口令
(1)账户锁定
概述：指定该用户锁定登陆时对多可以输入密码的次数，也可以指定用户锁定的时间一般用dba的身份去执行该命令
例子：指定scott这个用户最多只能尝试3次登陆,锁定时间为2天，让我们看看怎么实现。
创建profile文件（相当于创建规则）
sql>create profile aaa1 limit
(aaa1创建规则的名称)
failed_login_attempts 3 password_lock_time 2;
(设置登陆的次数为3次及锁定时间为：2天)
结果：create profile aaa1 limit failed_login_attempts 3 password_lock_time 2;
Sql>alter user leng profile aaa1
2)给账户（用户）解锁
sql> alter user leng account unlock;
(3)终止口令
为了让用户定期修改密码可以使用终止口令的指令来完成同样这个命令也需要dba身份来操作。
例子：给前面创建的用户lady创建一个profile文件，要求该用户每隔10天要修改自家的登陆密码，宽限期为2天。看看怎么做。
sql> create profile myprofile limit password_life_time 10 password_grace_time 2;
sql> alter user leng profile myprofile;
sql> alter user leng account unlock;
6．口令历史
概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样oralce就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。
例子：
1)建立profile
sql>create profile password_history limit
password_life_time 10 paaword_grace_time 2
password_reuse_time 10;
create profile //创建的文件名。 Password_life_time//使用期为10天
passwrod_grace_time//宽限期为两天
password_reuse_time//指定口令可重用时间即10天后就可以重用：
修改密码在10天内不能与旧密码一样，10天就可以重用
2)分配给某个用户。
删除profile
概述:当不需要某个profile文件时，可以删除该文件。
sql>drop profile password_history 【cascade】
第2讲：表
内容介绍：
1、 oracle的表的管理（创建和维护）
2、 基本查询
3、 复杂查询
4、 Oracle数据库的创建
一．表名和列的命名规则：
表名必须以字母开头
长度不能超过30字符
不能使用oracle的保留字
只能使用如下字符 A-Z,a-z,0-9,$,#等
二oracle支持的数据类型
1.字符型
char 定长 最大2000字符
例子：char(10) ‘小韩’ 前四个字符放’小韩’ ，后添6 个空格补全。 ‘小韩 ’ char型查询速度极快
varchar2(20) 变长 最大4000字符。
例子：varchar2(20) ‘小寒’ oracle 分配四个字符。
这样可以节省空间 varchar2 会自动去匹配
clob 字符型大对象最大4G
2.数字型
number 范围 负 (10 的38次方) 到10的38次方可以表示
整数，也可以表示小数。
number(5,2)：括号里面的数据可改变
表示一个小数有5位有效数，2位小数范围：-999.99~999.99
范围 负999.99 到 999.99
number(5)
表示一个五位整数
范围 负99999 到 99999
3．日期类型
date 包括年月日和时分秒（一般项目足够用）
timestamp 这是oracle9i对date数据类型的扩展(精度更高)
4、二进制(可存放图片，声音，视频文件)
blob 二进制数据 可以存放图片/声音 4G
一般来讲不会把图片，声音，视频文件网数据库里面存放，一般存放他们的路径；
如果这些数据保密性高，可以考虑存在数据库里面。
三、表的管理
1. 建表
----班级表
sql> create table class(---表名
classId number(2),---班级编号
cname varchar2(40),----班级名称
);
班级表：sql> create table class(classId number(2),cname varchar2(40))；
----学生表
sql> create table student (----表名
xh number(4),---学号
xm varchar2(20),----姓名
sex char(2),----性别
birthday date,----出生日期
sal number(7,2)----奖金
);
方法2: 利用pl/sql developer 工具建
在左边的工具栏的对象栏-->选中Tables文件夹右键-->新建à填写内容单击应用
分析存储栏：表示你要把所建的表存放在哪里，默认放在system这个表空间。
表空间：表存放在一个文件里，存放在一个磁盘里，不会在内存了，最终会放在数据库某一空间里。所以表空间就是存放表的一个空间
列：
索引:主要用于提高速度
2.修改表
添加一个字段
sql> alter table student add(classid number(2));
查看表结构desc student；
修改字段的长度
sql> alter table student modify (xm varchar2(30));
修改字段的类型 (不能有数据)
sql> alter table student modify (xm char(30));
删除一个字段
sql> alter table student drop column sal;
修改表的名字
sql> rename student to stu;
删除表
sql> drop table student；
3.添加数据
所有字段都插入
insert into student values(‘A001’,’刘权’,’男’,’07-5月-89,10);
ORACLE中默认的日期格式’DD-MON-YY’ dd 日子(天)
mon 月份 yy 2位的年 ？1996年6月9号
改日期的默认格式
alter session set nls_date_format =‘yyyy-mm-dd’;
修改后，可以用我们熟悉的格式添加日期类型
insert into student values(‘A002’,’李四’,’男’,’1990-05-28’,10);
插入部分字段
Insert into student(xh,xm,sex) values(‘A002’,’john’,’女’);
插入空值
Insert into student (xh,xm,sex,birthday)
values(‘A003’,’MARTIN’,’男’,null);
查出没有生日的人
Select * from student where birthday is null;
查看有生日的人
Select * from student where birthday is not null;
4.修改数据
改一个字段
update student set sex=‘女’ where xh=‘A001’;
修改多个字段
update student set sex=‘男’,birthday=‘1996-11-12’
where xh=‘A001’;
修改含有null值的数据
注意：select时查询null时，要用 is null
5.删除数据
删除数据
delete from student;(只删数据不删除表，表结构还存在)
数据回滚
rollback;(恢复数据)
在rollback 之前要先设置savapoint才能恢复数据
例：delete from student;
Savapoint aa;
Rollback to aa;
有经验的dba工作之前一般会做一个savapoint
删除所有记录，表结构还在，写日志，可以恢复的，速度慢
drop table student;删除表的结构和数据
delete from student where xh=‘A001’; 删除一条记录
truncate table student;
删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快
四、表的查询
介绍
在我们讲解的过程中我们利用scott用户存在的几张表(emp,dept)为大家演示如何使用 select语句，select语句在软件编程中非常的有用，希望大家好好的掌握。
select * from emp；查询结果如下：
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT CLERK 7566 1987-4-19 800.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
14 rows selected
select * from dept；结果为：
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
1、简单的查询语句
查看表结构
sql> desc dept;
查询所有列
select * from dept; 不要经常用* 查询，因为速度慢
查询指定列
select ename,sal,job,deptno from emp;
如何取消重复行
select distinct deptno,job from emp;(消除部门重复工作或清楚不重复地显示所有工作种类)
?查询SMITH的薪水，工作，所在部门
Select sal,job,deptno from where ename=‘SMITH’
删掉表中的某一列：
alter table tablename drop column columnname; ---------column：列
Set timing on 打开执行命令时间
自我复制表内容：insert into student（xuehao,xingming,sex）select * from student;
Select count(*) from student; 查看student表一共有多少行
2、使用算数表达式
？显示每个雇员的年工资
SQL> select sal*12,ename from emp;
SAL*12 ENAME
---------- ----------
9600 SMITH
19200 ALLEN
15000 WARD
35700 JONES
15000 MARTIN
34200 BLAKE
29400 CLARK
9600 SCOTT
60000 KING
18000 TURNER
13200 ADAMS
11400 JAMES
36000 FORD
15600 MILLER
14 rows selected
12个月工资加奖金：
SQL> select ename,sal*12+comm*12from emp;
ENAME SAL*12+COMM*12
---------- --------------
SMITH
ALLEN 22800
WARD 21000
JONES
MARTIN 31800
BLAKE
CLARK
SCOTT
KING
TURNER 18000
ADAMS
JAMES
FORD
MILLER
14 rows selected
对比发现comm为空的员工都没有工资了。Oracle如果一个值为null参见运输整个之为空
正确写法为：
select sal*12+nvl(comm,0)*13,ename from emp;
AL*12+NVL(COMM,0)*13 ENAME
--------------------- ----------
9600 SMITH
23100 ALLEN
21500 WARD
35700 JONES
33200 MARTIN
34200 BLAKE
29400 CLARK
9600 SCOTT
60000 KING
18000 TURNER
13200 ADAMS
11400 JAMES
36000 FORD
15600 MILLER
14 rows selected
说明：nvl(comm,0) 如果comm为null则用0代替；
使用列的别名
select ename “姓名”,sal*12 as “年收入” from emp;
oracle 中字符如果是英文的用 ‘‘裹起来，如果是中文的则用“”括起来
如何处理null值
使用nvl函数处理
select ename “姓名”,sal*12+nvl(comm,0)*13 as “年收入” from emp
如何连接字符串（||）
Select ename || ‘is a’ || job from emp;
3、使用where子句
？如何显示工资高于3000的员工
答案：Select ename，sal from emp where sal>3000;
？如何查找1982.1.1后入职的员工 注意：‘1-1月-1982’
答案：select ename ，hiredate from emp where hiredate>’1-1月-1982’;
？如何显示工资在2000到2500的员工情况
答案：select ename，sal from emp where sal>=2000 and sal<=2500;
如何使用like操作符
%：表示任意0到多个字符 ：表示单个字符
？： 如何显示首字符为S的员工姓名和工资 注意：like ‘S%’
Select ename ,sal from emp where ename like ‘S%’;
? :如何显示第三个字符为大写O的所有员工的姓名及工资
注意： ename like ‘__O%’;
在where条件中使用in
？如何显示empno为123，345，800…的员工情况
注意：empno in(123,345,800);
Select * from emp where empno in(123,345,800);
使用is null的操作符
？如何显示没有上级的雇员的情况 注意：where mgr is null
4.使用逻辑操作符号
？查询工资高于500或是为MANAGER的雇员，同时还要满足他们的姓名首写字母大写的J
注意：(sal>500 or job='manager') and ename like 'J%'
使用order by 子句(desc从高到低，默认是从低到高asc)
？如何按照工资的从低到高的顺序显示雇员的信息
select ename,sal from emp order by sal; （从高到低后面加desc）
？按照部门号升序而雇员的工资降序排列
(order by deptno,sal desc)
答案：select * from emp order by deptno asc,sal desc;
使用列的别名排序
select ename,(sal+nvl(comm,0))*12 as "年薪" from emp order by
"年薪" asc;
列名需要使用“”号圈中
5.复杂查询
说明：
在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较复杂的select语句
数据分组—max,min,avg,sum,count
? 如何显示所有员工中最高工资和最低工资
select max(sal),min(sal) from emp;
？显示所有员工的平均工资和工资总和
select avg(sal),sum(sal) from emp;
？计算共有多少员工
扩展要求：
？请显示工资最高的员工的名字，工作岗位
select ename,job from emp where sal=(select max(sal) from emp);
？请显示工资高于平均工资的员工信息
select ename,sal from emp where sal>(select avg(sal) from emp);
6. group by 和 having 子句
group by 用于对查询的结果分组统计，
having 子句用于限制分组显示结果。
?如何显示每个部门的平均工资和最高工资
select avg(sal),max(sal),deptno from emp group by deptno;
说明：前后必须有deptno这个词
？显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal), deptno,job from emp group by deptno,job;
？显示平均工资低于2000的部门号和他的平均工资
select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)<2000;
扩展要求：对数据分组的总结
1 分组函数中能出现在选择列表(select …选择列…from),having,order by子句中
2 如果在select 语句中同时包含有group by ,having, order by 那么他们的顺序是group by >having>order by
如
select deptno , avg(sal) , max(sal) from emp group by deptno having avg(sal)<2000 order by avg(sal);
3 在选择列中如果有列，表达式，和分组函数，那么这些列和表达式有一个出现在group by 子句中
如
select deptno , avg(sal) , max(sal) from emp group by deptno having avg(sal)<2000);
*这里deptno就一定要出现在group by中
7.多表查询
说明
多表查询是指基于两个和两个以上的表或视图的查询，在实际应用中
查询部门号 和其员工的姓名)，这种情况下需要使用到(dept表和emp表)
规定：多表查询条件是 至少不能小于 表的个数-1;
?显示雇员名，雇员工资及所在部门的名字
select ename,sal,dept.dname from emp,dept where emp.deptno=dept.deptno;
如果没有：where emp.deptno=dept.deptno，则会出现14*4行记录
？如何显示部门号为10的部门名，员工名和工资
select dept.dname,emp.ename,emp.sal from emp,dept where emp.deptno=dept.deptno and dept.deptno=10;
？显示各个员工的姓名，工资，及其工资的级别(级别在SALGRADE)
select a2.grade,a1.ename,a1.sal from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
扩展要求：
？显示雇员名雇员工资及所在部门的名字，并按部门排序
select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;
8.自连接
自连接：自连接是指在同一张表的连接查询；
？显示某个员工的上级领导和姓名
解析：将emp看成完全一样的两张表，一张是职工表，一张是老板表
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;
？显示FORD的上级
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename=’FORD’;
9.子查询
*子查询：是指嵌入在其他sql语句中的select 语句，也叫嵌套查询
如：select * from class where ? =(select…select（）)
*单行子查询：是指返回一行数据的子查询语句
请思考：如何显示与SMITH同一部门的所有员工？
SQL> select * from emp where deptno = (select deptno from emp where ename = 'SMITH');
数据库在执行sql时，是从左到右执行的，但我们在写的时候一般是从右到左。
*多行子查询
多行子查询只返回多行数据的子查询
请思考：如何查询和部门10的工作相同的雇员的名字，岗位，工资，部门号
select ename,job,sal,deptno from emp where job in(select job from emp where deptno=10);
*在多行子查询中使用all操作符
请思考：如何显示工资比部门30的所有员工的工资高的员工的姓名，工资和部门号
select ename,sal,deptno from emp where sal>all (select sal from emp where deptno=30);
扩展要求：
大家想想还有没有其他的查询方法？
SQL> select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);
这两个方法中max()比all 效率高
*在多行子查询中使用any 操作符
请思考：如何显示工资比部门30的任意一个员工的工资高的员工的姓名，工资和部门号。
select ename,sal,deptno from emp where sal>any (select sal from emp where deptno=30);
扩展要求：
大家想想还有没有其他的查询方法
select * from emp where sal>(select min(sal) from emp where deptno=30);
*多列子查询
单行子查询是指子查询只返回单列，单行数据，多行子查询是指返回单列多行的数据，都是正对单列而言的，而多列子查询则是只查询返回多个列数据的子查询语句。
请思考：如何查询与smith的部门和岗位完全相同的所有雇员
SQL> select * from emp where deptno=(select deptno from emp where ename='SMITH') and job=(select job from emp where ename='SMITH');
或者：
SQL> select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
*在from子句中使用子查询
请思考：如何显示高于自己部门平均工资的员工的信息
step1：先查出各个部门的平均工资
SQL> select deptno,avg(sal) from emp group by deptno;
结果：
step2:将上面的查询看做是一张 子表
SQL>select a2.* from emp a2, (select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
总结：在from 子句中使用子查询
这里需要说明的是挡在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，挡在from子句中使用子查询时，必须给子查询指定别名。表取别名是不能用as，列可以
10.分页查询
按雇员的id号升序取出
oracle分页一共有 三种方式（这里讲rownum）
1. rownum分页
例： (select * from emp);
2. 显示rownum[oracle的分配]
SQL> select a1.*,rownum rn from (select * from emp)a1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7369 SMITH CLERK 7902 1980-12-17 800.00 20 1
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 2
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 3
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 4
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 5
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 6
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7
7788 SCOTT CLERK 7566 1987-4-19 800.00 20 8
7839 KING PRESIDENT 1981-11-17 5000.00 10 9
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 10
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 11
7900 JAMES CLERK 7698 1981-12-3 950.00 30 12
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 13
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 14
14 rows selected
rownum rn 作用是生存rn行号
3. 例如我们要选6-10的rn
如果写成：select a1.*,rownum rn from (select * from emp)a1 where rownum<=10 and rownum>5;
oracle 数据库不能显示任何信息。
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
正确操作方法：
1select a1.*,rownum rn from (select * from emp)a1 where rownum<=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7369 SMITH CLERK 7902 1980-12-17 800.00 20 1
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 2
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 3
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 4
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 5
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 6
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7
7788 SCOTT CLERK 7566 1987-4-19 800.00 20 8
7839 KING PRESIDENT 1981-11-17 5000.00 10 9
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 10
10 rows selected
2再将上面的查询结果当作一个表
select * from(select a1.*,rownum rn from (select * from emp)a1 where rownum<=10)where rn>5;
结果如下：
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 6
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7
7788 SCOTT CLERK 7566 1987-4-19 800.00 20 8
7839 KING PRESIDENT 1981-11-17 5000.00 10 9
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 10
4几个查询变化
a. 指定查询列只需改变（select * from emp）这里的*
b. 如何排序，如：工资从高到低排序。也只需改（select * from emp order by sal desc）
c. 反正所有的改动只需改最里层的（select * from emp）
其他的分页方式有：根据rowid和按分享函数来分
执行查询效率：rowid > rownum > 分析函数
补：查看emp表一共有多少行：select count（*） from emp;
11.用查询结果创建新表
这个命令是一种快捷的建表方法。
create table mytable (id,name,sal,job,deptno) as select empno,sal,job,deptno from emp;
12.合并查询
优势在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号
union union all intersect minus(减)
1） union（联合）
该操作符用于取得两个结果集的并集表，当使用该操作符时，会自动去掉结果集中重复行。比如：表A有5个元素，B表有3个元素，A,B有2个相同的元素 那么 A union B 有 5+3-2=6个元素
select ename,sal,job from emp where sal >2500 union
select ename,sal,job from emp where job=’MANAGER’;
结果分析：
SQL> select ename,sal,job from emp where sal >2500;
ENAME SAL JOB
---------- --------- ---------
JONES 2975.00 MANAGER
BLAKE 2850.00 MANAGER
KING 5000.00 PRESIDENT
FORD 3000.00 ANALYST
SQL> select ename,sal,job from emp where job='MANAGER';
ENAME SAL JOB
---------- --------- ---------
JONES 2975.00 MANAGER
BLAKE 2850.00 MANAGER
CLARK 2450.00 MANAGER
SQL>select ename,sal,job from emp where sal >2500 union select ename,sal,job from emp where job=’MANAGER’;
ENAME SAL JOB
---------- ---------- ---------
BLAKE 2850 MANAGER
CLARK 2450 MANAGER
FORD 3000 ANALYST
JONES 2975 MANAGER
KING 5000 PRESIDENT
2） union all
与union相似，但它不会取消重复行，而且不会排序。所以上面的例子应该有5+3=8行
3） intersect（相交）
取得两个结果的交集，上面例子应该有2个像个的元素。
4） minus（减）
取得两个结果的差集，
注意：这种方法比and 之类的速度快
五 创建新的数据库
有两个方法：
1） 通过oracle提供的向导工具（重要）
步骤：开始—所有程序---Oracle-oraHome92—Configuration and Migration Tools---Database Configuration Assistant
创建时一般选择 New Database
2） 手工步骤字节创建
第3讲：插入补充和事务
一．插入更新
1，使用 to_date 函数
请思考：如何插入列带有日期的表，并按照 年-月-日 的格式插入？
例如：
SQL>inset into emp values(7001,’XIAOHONG’,’CLERK’,7369, to_date(‘1990-12-10’ ,’yyyy-mm-dd’,700.00,100.00,10);
2.使用子查询插入数据
介绍：当使用values子句时，一次只能插入一行数据，当使用子查询插入数据时，一条insert语句可以插入大量的数据。当处理行迁移或者装在外部表的数据到数据库时，可以使用子查询来插入数据
例：
SQL> insert into kkk(myid,myname,mydept) select empno,ename,deptno from emp where deptno=10;
4. 使用子查询更新数据
介绍：使用update语句更新数据时，既可以使用表达式或者数值直接修改数据，也可以使用盖子查询修改数据。
思考：希望员工scott的岗位，工资，补助与smith员工一样
SQL> update emp set (job,sal,comm) =(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';
二．oracle中事务处理
1.什么是事务：事务用于保证数据的一致性，他由一组相关的dml（数据操作语言：增删改）语句组成，该组的dml语句要么全部成功，要么全部失败。
如：网上转账就是典型的药用事务来处理，用以保证数据的一致性。
要保持事务的一致性，不然就撤销一切事务。
.事务和锁：
当执行实务操作时（dml语句），oracle会在被作用的表上加锁，防止其他用户该表的结构，这里我们对用户来讲是非常重要的。（类似于上单间厕所排队）
2.提交事务
SQL>commit; (提交完成,此时的所有保存点将不存在)所有应该先commit
当执行使用commit语句可以提交事务，当执行了commit语句子后，会确认事务的变化，结束事务，删除保存点，释放锁，当使用commit语句结束事务子后，其他回话将可以看到变化后的新数据。
保存点的演示：
先提交事务（COMMIT）,创建一个保存点a1.
现在删除emp表中的一条数据，然后呢，我们
在rollback to a1.--------回退事务
3.回退事务
在介绍回退事务前，我们先介绍一下保存点（savepoint）的概念和作用。保存点是事务中的一点，用于取消部分事务，当结束事务时，会自动的删除该事物所定义的所有保存点。当执行rollback时，通过制定保存点可以回退到指定的点。
SQL>commit; (提交完成,此时的所有保存点将不存在)所有应该：
先commit
再：savepoint a1; （创建保存点a1）
再：delete
最后：rollback to a1; (回滚到保存点a1)
4.事务的几个重要操作
a. 设置保存点：savepoint a;
b. 取消部分事务：rollback to a;
c. 取消全部事务：rollback；
5.java程序中如何使用事务
在java操作数据库时，为了保证数据的一致性，比如转账操作
1） 从一个账户减掉100
2） 在另一个账户上加100
我们看看如何是有事务？（第13讲，40分钟）
6.只读事务
只读事务是指允许执行查询的操作，而不允许执行任何其他dml操作的事务，使用只读事务可以确保用户只能取得某时间点的数据。假定机票代售点每天18点开始统计今天的销售情况，这是可以使用只读事务，在设置了只读事务后，尽管其它会话可能会提交新的事务，但是只读事务讲不会取得最新数据的变化，从而可以保证取得特定时间点的数据信息。
7.设置只读事务
set transaction read only;
第4讲：（sql函数的使用）
1.字符函数
介绍：字符函数是oracle中最常用的函数，我们来看看有哪些字符函数：
* lower（char）：将字符串转化为小写的格式
* upper（char）：将字符串转化为大写的格式
* length（char）：返回字符串的长度
* substr（char,m,n）：取字符串的子串
* replace(char1,search_string,replace_string)
* instr(char1,char2,[,n[,m]])取子串在字符串的位置
将所有员工的名字按小写的方式显示？
SQL> select lower(ename) from emp;
将所有员工的名字安大写的方式显示？
显示正好为5个字符的员工的姓名？
SQL> select * from emp where length(ename)=5;
显示所有员工姓名的前三个字符？
SQL> select substr(ename,2,3) from emp;表示从第二位开始取，取三个字符。
以首字母大写的方式显示所有员工的姓名？
分析：
1完成首字母大写
select upper(substr(ename,1,1)) from emp;
2完成后面字母小写
select lower(substr(ename,2,length(ename)-1)) f