oracle的常用命令 第一章
(1) 请使用 scott 用户登录 oracle 数据库实例，然后，切换身份为 system 用户
登录后，使用：
简单使用：conn 用户名/密码
该命令经常用于切换当前用户。因为我们建议大家在登录的时候用普通用户，scott，如果确实需要 system
用户，测可以使用该命令切换为高级用户。
一、连接命令
(1)conn[ect]用户名/密码@网络[as sysdba/as sysoper]
(2)show user 命令可以用来显示当前用户名
(3)disc[onnect] 用户 该命令用于断开和oracle连接，但是不退出sqlplus窗口
(4)exit 退出sqlplus窗口
(5)passw[ord] 该命令用于修改用户密码
基本用发：password 用户名：
如果给自己修改密码， 则可以不带用户名，如果给别人修改密码(前提是 system，
或者说一声用户来修改),则需要带用户名。
-------------------------------------------------------------------------------------
二、交互命令
(1)& :可以替代变量，而该变量在执行时，需要用户输入。
基本用法： select* form table where coumnName=&***;
slect * from emp job=&job;
(2)edit :用于编辑脚本
基本用法： edit d:\aa.sql
(3)spool :把屏幕中上显示的记录，保存到文件中。
(1)linesize: 每行显示的字数。默认是80个。
基本用法： set linesize 字符数
(2) pagesize 每页显示的行数
基本用法： set pagesize 100;
-------------------------------------------------------------------------------------
三、oracle的用户管理
① 创建用户（简单版）
基本用法： create uer 用户名 identified by 密码；
eg: 创建一个普通用户xiaoming
create user xiaomig identified by m124;[oracle 要求用户密码不能用数字开始。]
创建用户（细节）
eg：create user ljf identified by m123
default tablespace users
temporary tablespace temp quota 3m on users;
identified by 表明用户ljf将用数据库方式验证default tablespace users//用户的表空间在uers上。
temporary tablespace temp //用户ljf的临时表建在temp空间
quata 3m on users//表明用户ljf建立的数据对象（表、索引、pl/sql块。。）最大只能是3m。
刚刚创建的用户是没有任何权限的，需要dba给该用户权限。
grant connect to ljf；
如果你希望该用户没有空间的权限
grant resource to ljf
如果你希望该用户成为dba
grant dba to ljf
----------------------------------------------------------------------------------------------
②oracle 管理用户的原理
权限：从大类来分：系统权限和对象权限
系统权限有128中，常用的有：create session 登录数据库实例
create table 建表权限 create index 、create view
create sequence create trriger...
对象权限： unpdate、insert、delete、select
角色分两种：预定义角色、自定义角色。
预定义角色：把常用
的权限集中起来，形成的角色。dba、connect、resource这几个角色是常用的。
自定义角色：自己定义的角色。
授予或回收权限
基本语法：授予 grant 权限/角色 to 用户
回收 revoke 权限/角色 from 用户
②修改密码
password 用户名
如果给别人修改密码则需要具有dba的权限，或是拥有alter user 的系统权限，也可以使用password用户名
alter user 用户名 identified by 新密码。
④方案（schema）
当一个用户创建好后，如果该用户创建了任意一个数据对象，这时，我们的dbms就会创建一个对应的方案与该用户对应，并且该方案的名字和用户名一致。
小技巧：如果希望看到某个用户的方案究竟有什么数据对象，我们可以用pl/sql developer;
要求：请完成一个功能：让xiaoming 用户可以去查询 scott 的 emp 表
步骤1.先用scott 登录
conn scott/tiger;
步骤2.赋权限
grant select[update][delete][insert][all] on emp to xiaoming;
小明要查询scott 的emp 表时，要这样写：select * from scott.emp;
----------------------------------------------------------------------
四、使用profile（规则）文件对口令进行管理
基本语法：
创建profile文件
create profile 文件名 limit failed_login_attempts 3 password_lock_time 2;
指定给用户：alter user 用户名 profile profile文件名;
需求，只允许某用户，最多尝试登录三次，如果三次没有成功，则锁定两天，两天后才能重新登录。
步骤1 conn system/cclljf
2 create profile myprofile1 limit failed_login_attempts 3 password_lock_time 2;
3 alter user scott profile myprofile1;
给账户（用户）解锁
alter user 用户名 account unlock;
终止口令
需求：一个用户的密码做多只能用10天，宽限期为2天，到时必须设置重新的密码。
基本语法：
create profile 文件名 limit password_life_time 10 password_grace_time 2;
分配文件
alter user 用户名 profile 文件名；
删除profile文件
drop profile 文件名；
-------------------------------------------------------------------------
五、oracle数据库的启动流程
①可以在控制台（dos控制台下运行） 查看window系统信息指令：systeminfo；
lsnrctl start；用于启动监听
②启动数据实例
oradim -startup -sid 数据库实例名
-------------------------------------------------------------------------
六、oracle 登录认证方式
oracle认证分为普通用户和特权用户
- 普通用户登录，默认是以数据库方式验证，如：conn scott/tiger;
- 特权用户登录(sys）
-- 只要当前这个系统用户是在ora_dba组中，则可以通过)，默认是以操
作系统方式验证。如：conn system/ccljf as sysdba
dbms一看到as sysdba则认为要以特权用户登录，前面的用户名和密码口不看，登陆后，
自动切换成 sys用
户 等价于conn sys/cclljf;
-- 如果当前用户不在ora_dba组中，这时候再使用数据库验证。
-- 可以通过修改sqlnet.ora文件，可以修改oracle登录验证方式：
SQLNET AUTHENTICATION_SERVICES=(NTS) 是基于操作系统验证
SQLNET AUTHENTICATION_SERVICES=(NONE) 是基于oracle验证
SQLNET AUTHENTICATION_SERVICES=(NONE,NTS) 两者共存。
----------------------------------------------------------
如何找回管理员密码
① 搜索名为PWD 数据库实例名.ora 文件
* 数据库实例名 是根据实际情况定，比如：orclljf
②备份一份密码文件
③生成一份新的密码文件，在doc控制台下输入命令:
orapwd file=xxx password=xxxx entries=xx
file:密码文件的全路径（和原来的一样）
password：表示新的密码
entries：表示登录同时可以登录多少个sys用户
注意：修改后要重启数据库实例，这样修改的密码文件才能生效。
课堂练习：
给scott用户分配一个profile，要求如下：
1. 尝试登录的登录次数4次
2. 如果4次登录错误，则锁定该用户2天
3. 密码每隔5天修改一次，宽限期为2天
conn system/cclljf
create profile myprofile1 limit failed_login_attempts 4
password_lock_time 2 password_life_time 5 password_grace_time 2;
4. 练习如何给用户解锁
conn system/cclljf
alter user scott account unlock;
5. 联系如何删除profile文件
drop profile myprofile1 cascade;
oracle 第二章 笔记
创建表
create table table_name(
列名 劣的数据类型，
....
)
oracle 数据类型
文本、二进制类型
① char(size)
存放字符串，最大 2000个字符，是定长。
② varchar2(size)
变长，最大可以存放4000个字符。是变长
注意:如果确定数据的长度是固定的，比如商品编号（8位），则应当使用char来存放，
以提高存取速度，如存放的数据的是变化的。则使用varchar2.
③ nchar
定长，unicode编码，最大2000个字符。
④nvarchar
变长，unicode编码，最大4000个字符
⑤ clob(character large object) 字符型大对象
变长，最大8tb 1t = 1024g，一般存放字符类文件
⑥ blob
变长，最大8tb，存储二进制文件，如：音乐文件等
*说明：我们在实际开发中很少把文件存放在数据库（效率问题），实际上我们一
般记录文件的有一个路径(https://www.doczj.com/doc/b73115488.html,/..)
-----------------------------------------------------------------------
数值类型
① number
可以存放整数，也可以存放小数。number(p, s)
p为整数位，s为小数位，范围： 1<=p <=38, -84<= s <=127
保存数据范围：-1.0e-130 <= nmber value <1.0e+126;
*-10e-130(科学计数法):就是 -1.0 乘以 10 的-130次方。
保存在机器内部的范围： 1--22bytes；是变长。
② date 日期类型
用于表示时间，（年/月/日/时/分/秒)
oracle 日期的默认格式是 ‘dd-mm-yyyy’;如果
我们希望使用自己习惯的日期添加，
也可以但是需要借助oracle的相关函数。
- timestamp(n) 是邮戳，n的值0--9，是可以选择。时间是会联动的。
表管理-修改表
//添加一个字段
alter table 表名 add (新的列名 列的类型);
//修改一个字段
alter table 表名 modify (culumnname datatype);
//删除一个字段
alter table tablename drop column columnname;
//修改表的名字
rename 旧表的名字 to 新的名字;
实际使用：
练习：
1 给学生表添加班级编号
atler table student add (classId number(10));
2 学生姓名 变成 varchar2(30)
alter table student modify (sname varchar2(30));
3 学生姓名 变成 char(30)
alter table student modify (sname char(30));
4 删除学生表的 fellowship 字段
alter table student drop column fellowship;
5 把学生表名 student 修改成 stu；
rename student to stu;
6 删除学生表
drop table stu;
----------------------------------------------------
oracle 的crud操作
crud 操作就是：c(create) r(retrieve/read) u(update) d(delete)
① insert 添加操作
insert into tablename [(column[,column...])] values(value[,value...]);
*插入的数据应与字段的数据类型相同。
*数据的大小应在列规定范围内。
*在alues中列出的数据位置必须与被加入的列的排列位置相对应。
*字符和日期数据应包含在单引号中。
*插入空值，不指定或insert into tablename alues(null); ''等同null
-------------------------------------------------------------------
update 更新数据
update tablename set columnname=value [columnname=value....] [条件];
update 语句的实际运用
练习题
* 将所有学生薪水修改为5000元。
update stu set fellowship=5000;
* 将姓名为‘zs’的学生薪水改为3000元。
update stu set fellowship where name='zs';
* 将‘ls’的薪水在原来基础上增加1000元。
update stu set fellowship=fellowship + 1000 where name='ls';
* 将没有奖学金的同学的奖金改为10元。
update stu set fellowship=10 where fellowship is null;
-------------------------------------------------------------------
delete 语句的使用
delete from tablename where condition.
① 同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，
在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。
② 删除数据有两种方式
* delete 表名
回滚：savepoint 回滚点；
只有设置了回滚点才能回滚，通过命令：rollback to 回滚点；
* truncate table 表名；//速度快，但是不能回滚。
-------------------------------------------------------
select 查询
基本语法：
select [distinct] *|{列名1，列名2...} from tableName [where {condition}];
* oracle的 sql 不区分大小写，但是内容是区分大小写。
* nvl(0,com)函数：表示假如com的只等于null的话，那么就让它等于
0。
||的使用
在查询的时候，如果希望把多列拼接起来，作为一列返回，可以使用。
面试题：
我们希望删除用户，同时保留该用户的数据对象，怎么处理
(1)锁定该用户
alter user scott account lock;
(2) 这时该用户已经不能登录到数据库，但是system用于依然可以使用它的数据对象。
(3) 解锁用户
alter user scott account unlock;
------------------------------------------------------------
where 字句的用法
*to_char(日期，‘格式’) 转换日期的格式。
?查询1982.1.1后入职的员工
select * from emp where to_char(hiredate,'yyyy-mm-dd') > '1982-1-1';
*between 是两头都是闭区间。
* like字符的使用
是做模糊查询的一个关键字
% 表示0到多个字符。
_ 表示一个任意字符。
？如何显示首字符为S的员工姓名和工资
select ename,sal from emp where ename like 'S%';
?如何显示第三个字符为大写O的所有员工的姓名和工资。
select ename,sal from emp where ename like '__O%';
* where 条件中使用 in
？如何显示empno为123，345，800的员工
select * from emp where enameno in(123,345,800);
?如何显示没有上司的员工
select * from emp where mgr is null;
-----------------------------------------------
oracle中使用逻辑运算符
？查询工资高于500或者岗位为MANAGER的员工，同时还要满足他们的姓名首字符
为大写的J
select * from emp where (sal > 500 or job='MANAGER') and ename = 'J%';
* order by 排序
？如何按照工资的从低到高的顺序显示员工的信息
select * from emp where order by sal;
说明：默认的情况下是升序的排列asc，如果想降序desc(descend),order by后面除了可以加列名外，也可以跟数字，但是不能是0或超出查询的列数。如：
select ename,job from emp order by 数字;//这里只是查询了ename 和job两列，假如在后跟3的话，就越界了，因为只有两列。
?按照部门号升序而员工的入职时间降序排序
select * from emp order by deptno ,hiredate desc;
* 使用别名排列
select ename,sal*13+nvl(comm,0) "年薪" from emp order by "年薪";
-----------------------------------------------------------------
oracle的复杂查询
数据分组-max，min,avg,sum,count
* max
?显示年工资最大的员工
select max(sal*13+nvl(comm,0) 年工资 from emp;
?显示所有员工的平均工资和工资总和
select avg(sal),sum(sal) from emp;
细节问题：avg(sal)不会把sal为null的进行统计，因此我们要注意，如果，你希望为空值也考虑，则可以这样做：
select sum(sal)/count(*) from emp;
* count(*) 统计行数
？计算有多少员工
select count(*) from emp;
细节：count(*)可以对一个字段进行统计。它也是不统计空值的行。
扩展要求：
？请显示工资最高员工的名字，工作岗位。
思路：我们可以使用子查询
select ename,job
from emp where sal=(select max(sal) from emp);
select 语句的执行顺序
① 我们写sql语句是从左到右
② sql执行在默认情况下是从右到左执行。
？请显示比平均工资大的员工
select * from emp where sal>(select avg(sal) from emp);
* group by 和having字句
group by用于对查询的结果分组统计；
having字句用于限制分组显示结果。后面可以加分组函数，但是不能永别名
?如何显示每个部门的平均工资和最高工资
select deptno,avg(sal) 平均工资,max(sal) 最高工资 from emp group by deptno;
?显示每个部门的每种岗位的平均工资和最低工资
select deptno,job,avg(sal),min(sal) from emp group by deptno,job;
?显示部门平均工资低于2000的部门号和它的平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
***对数据分组的总结
1 分组函数(avg...)只能出现在选择列表、having、order by字句中
2 如果在select语句中同时包含group by，having，order by那么他们
的顺序是group by，having，order by
3 在选择列中如果有列、表达式、和分组函数，那么这些列和表达式必须有一个
出现在group by字句中，否则就会出错。
------------------------------------------------------------------------
oracle多表查询
需求：因为在实际开发中，我们不可避免的需要对两张或以上的表进行联合查询：如：显示员工名，工资及所在部门的名字
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;
* 笛卡尔集，在多表查询的时候，如果不带任何条件，则会出现笛卡尔集，怎么避免笛卡尔集。多表查询的条件是至少不能少于 表的个数-1.
?如何显示部门号为10的部门名。员工名和工资。
select dept.dname, emp.ename,emp.sal from emp, dept where emp.deptno=dept.deptno and emp.deptno=10;
?显示各个员工的姓名，工资，及其工资的级别
select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal;
注意：在多表查询的时候，如果两个表的列同名，则需要要加表名区分，否则可以不加，我们建议大家都加表名，以增加阅读性。也可以用别名减少sql语句的长度。
?显示员工名，员工工作及所在部门的名字并按部门排序
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno and order by d.dname;
----------------------------------------------------------------------
oracle 自连接
自连接是指同一张表的连接查询
?显示FORD的上司的名字
select enmae form emp where empno=(select mrg from emp where ename='FORD');
?显示所有员工的上司的姓名
select w.ename,b.ename form emp w,emp b where w.mgr=b.empno;
疑惑：这里我们看到KING没有显示，因为KING没有上级，如果我们希望把上级的员工也显示出来，则需要
使用外联结(回头说....)。
------------------------------------------------------------------------
oracle子查询
是指嵌入式在其他sql语句中的select语句，也叫嵌套查询。根据返回的结果不一样
分单行子查询和多行子查询
① 单行子查询
指只返回一行数据的子查询语句
?如何显示与SMITH同一部门的所有员工
select * from emp where deptno=(select deptno from emp where ename='SMITH') and ename!='SMITH';
② 多行子查询
指返回多行数据的子查询
?如何查询和部门10的工作相同的员工的名字、岗位、工资、部门号
1 先查询出10号部门有哪些岗位
select distinct job from emp where deptno=10;
2 显示和它的岗位有相同的员工
select ename,job,deptno from emp where job in(select distinct job from emp where deptno=10);
③ 在子查询中使用all
?如何显示工作比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal> all(select sal from emp where deptno=30);
也等同于：
select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);
④ 在子查询中使用any
?如何显示工资比部门30的任意一个员工的工资的员工的姓名、工资、部门号
select ename,sal,deptno from emp where sal> any(select sal from emp where deptno=30);
也等同于：
select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30);
⑤ 多列子查询(oracle特有)
单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句。
?如何查询与SMITH的部门和岗位完全相同的所有员工。
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
⑥ 在from字句中使用子查询
?如何显示高于自己部门平均工资的信息
思考：各个部门的平均工资是多少?
select avg(sal),deptno,from emp group by deptno;
把上面的查询当成临时表来对待
select t2.ename,t2.sal,t1.myavg,t2.deptno from emp t2,(select avg(sal) myavg,deptno from emp group by deptno)t1 where t2.deptno=t1.deptno and t2.sal>t1.myavg;
?查找每个部门工资最高的人的详细资料
思路：先查询出各个部门的最高工资
select max(sal),deptno from emp group by deptno;
以上面的表做临时表联合查询
select * from emp,(select max(sal) hisal,deptno from emp group by deptno) t1 where emp.sal=t1.hisal and t1.deptno=emp.deptno;
?显示每个部门的信息(编号，名称)和人员数量。
思路：先查询每个部门有多少人
select count(*),deptno from emp group by deptno;
和dept表做联合查询
select d.deptno,d.dname,t1.total from dept d,(select count(*) total, deptno from emp group by deptno) t1 where d.deptno=t1.deptno(+);
!!!这个知识点必须掌握。(+
)表示外联。
------------------------------------------------------------------------
分页查询
分页查询是我们学习任何数据库，必须掌握的一个要点。
mysql：
select * from tableName where condition limit 从第几条取，取几条；
sqlserver:
select top 4 * from tableName where id not in(select top 4 id from tableName where condition);
排除前四条，再取四条，这个案例实际上是取出id号为 5-8条；
oracle分页查询
select t2.* from (select t1.*,rownum rn from(select * from emp) t1 where rownum<=6) t2 where rn>=4;
说明：上面的这个sql是oracle数据库效率比较高的查询方法；在百万级别都可以及时响应。
oracle 使用三层过滤机制
第一层：select * from emp
第二层：select t1.*,rownum rn from(select * from emp) t1 where rownum<=6
第三层：select t2.* from (select t1.*,rownum rn from(select * from emp) t1 where rownum<=6) t2 where rn>=4;
* 6：表示取到第几条
* 4：从第几条开始取
如果我们需要针对不同的情况，分页，请在最内层进行处理，包括多表查询。
? 请按照入职时间的先后顺序，查询从第7到第10个人是谁？
select t2.* from (select t1.*,rownum rn from(select * from emp order by hiredate) t1 where rownum<=10) t2 where rn>=7;
看看分页查询的效率：
模拟10w的一个表：测试
create table test as select empno,ename,sal,comm,deptno from emp;
自我复制：
insert into test(empno,ename,sal,comm,deptno) select empno,eanme,sal,comm,deptno from test;
----------------------------------------------------------------
合并查询
① union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='MANAGER';
② union all
该操作符与union相似，但是不会去掉重复的行。而且不会排序。
select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job='manager';
③ intersect 取交集
④ minus 取差集
使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据。
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job='MANAGER';
------------------------------------------------------------------------
oracle 的内连接和外连接
① 内连接
是我们用的最多的一种连接，前面我们讲的都是内连接。
eg：比如我们要显示员工的信息和部门名称。
select emp.ename,dept.dname from emp ,dept where emp.deptno=dept.deptno;
基本语法：
select 列名... from 表1，inner join 表2 on 条件；
select emp.ename,dept.danme from emp inner join dept on emp.deptno=dept.deptno;
② 外连接
外连接分三种：左外联、右外连、完全外连接。
我们创建两张表做测
试：
stu；exam
create table stu(
id number,
name varchar2(32)
);
insert into stu values(1,'jack');
insert into stu values(2,'tom');
insert into stu values(3,'kity');
insert into stu values(4,'nono');
create table exam (
id number,
grade number);
insert into exam values(1,56);
insert into exam values(2,67);
insert into exam values(3,78);
insert into exam values(14,56);
实际运用：
显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号成绩显示出来为null；
selec https://www.doczj.com/doc/b73115488.html,,stu.id,exam.grade from stu,exam where stu.id=exam.id;
上面我们使用的是内连接：它的特点是只有两张表同时匹配，才被选择。
select https://www.doczj.com/doc/b73115488.html,,stu.id,exam.grade from stu left join exam on stu.id=exam.id;
使用左外连：left join
如果stu(左表)的记录没有和 exam任何一条记录匹配，也要被选中而显示。
注意：怎么判断一张表是左表，如果在left join左边，就是左表。
左外连另外一种写法：
select https://www.doczj.com/doc/b73115488.html,, stu.id,exam.grade from stu,exam where stu.id=exam.id(+);
右外连 right join
显示所有的成绩，如果没有名字匹配，显示空
select https://www.doczj.com/doc/b73115488.html,,stu.id,exam.grade from stu right join exam on stu.id=exam.id;
说明：右外连，指的是右边的表如果没有和左边任何一条记录匹配，也要被选中。
另外一种写法：
select https://www.doczj.com/doc/b73115488.html,,stu.id,exam.grade from stu,exam where stu.id(+)=exam.id;
把(+)写在左边，表示右外连。
小结：实际上左外联和右外连是可以互相转换的。
完全外联 full outer join
是指两个表查询，不管有没有匹配，都显示出来，即有匹配的就显示匹配的，没有匹配就显示null。
？显示所有成绩和学生的姓名，如果没有相应的匹配值，则显示空。
select https://www.doczj.com/doc/b73115488.html,,stu.id,exam.grade from stu full outer join exam on stu.id=exam.id;
一个小练习
列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。用四种方法
① select dept.dname,emp.* from emp right join dept on dept.deptno = emp.deptno order by dept.dname;
② select dept.dname,emp.* from emp,dept where dept.deptno = emp.deptno(+) order by dept.dname;
③ select dept.dname,emp.* from dept left join emp on dept.deptno = emp.deptno order by dept.dname;
② select dept.dname,emp.* from emp,dept where dept.deptno = emp.deptno(+) order by dept.dname;
---------------------------------------------------------------------------
创建数据库实例
① 用向导工具 Database Configuration Assistant
② 手工创建数据库（文档）
* 当我们创建完一个新的数据库实例后，在服务中就会有两个新的服务创建，这是，你根据实际需要去启动响应的数据库实例。
* 在同一台机，可以同时启动多个数据库实例，我们在登录或连接的时候。需要指定主机字符串。
java如何连接和操作oracle数据库
我们
在eclipse编写一个java程序，完成对emp表的crud操作
------------------------------------------------------------------------
java连接oracle有两种方式
①使用jdbc直连（用的比较多）
连接：Class.forName(oracle.jdbc.driver.OracleDriver);
URL:jdbc:oracle:thin:@127.0.0.1:1521:orclljf;
sql语句的分类
dml语句（数据操作语句）[insert,update,delete]
ddl语句（数据定义语言）[create,drop]
dql语句（数据查询语句）[select]
dtl语句（数据控制语言）[commit rollback]
② jdbc-odbc桥连接（用的比较少）
oracle也提供jdbc-odbc桥连
首先要先配置数据源
控制面板->管理工具->odbc数据源
连接：Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
URL:jdbc:odbc:ljforc
什么时候使用jdbc，jdbc-odbc
原则：如果java程序和db不在同一机器上，我们一般用jdbc，如果java程序和db在同一机器上，则两个都可以用。
---------------------------------------------------------------------------
to_date(String,'format')的使用
我们看看，在插入达特列时候，我们发现，必须以默认格式添加（日-月-年），实际上我们可以希望以自己习惯的方式，比如：
insert into emp (empno,hiredate) values(2222,'1892-2-12');会发生错误
insert into emp (empno,hiredate) values(12,to_date('1985-11-12','yyyy-mm-dd'));
使用子查询完成行迁移的需求
使用sql建表
create table temp# as select empno,ename from emp where ename like 's%';
使用子查询完成更新
希望员工SCOTT的岗位、工资、补助与SMITH员工一样。
update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';
------------------------------------------------------------------------
sql函数
oracle数据库的强大，体现在对用户管理，pl/sql编程，函数丰富
oracle函数分类
从大方向，分为 单行函数比如：length
和多行函数 如：max，min
单行函数分类
① 字符函数
用于处理字符相关的业务
------------------------------------------------------------------
oracle提供了一套系统函数，可以用于查询系统信息
① sys_context('userenv','db_name') from dual;
1)terminal :当前回话客户所在的终端的标识符
2)language: 语言
3)db_name ：当前数据库名称
4)nls_date_format：当前回话客户所对应的数据库用户名
5)session_user: 当前回话客户所对应的数据库用户名
6)current_schema: 当前回话客户所对应的默认方案名称
--------------------------------------------------------------------------
oracle事务处理
先给大家看一个现象
现象：控制台1在操作数据库的时候，如果没有提交（commit）动作，那么我们控制2看到的信息就会和控制台1看到的结果不一样。
数据库把一系列的对数据库的操作看做成一个整体，要么全部成功，要么全部
失败，这样的现象就是事务现象、事务具有有原子性。
基本概念：
事务是把对数据库的一系列操作看作一个整体，要么全部成功，要么全部失败，利用事务我们可以保证数据的完整性，事务具有原子性。
事务的提交
java代码
//设置事务成不自动提交
//事务开始
update emp set-?...
insert ...
update....
//事务结束
//事务提交
commit;
保存点
基本语法
① savepont 保存点名称
② 在一个事务中可以保存多个保存点
③ 我们可以使用 rollback to 保存点
回退到指定的保存点。
④ 一旦回退了，就不能再次回退。
⑤ 设置保存点，是有资源开销
⑥ 一旦提交了事务，则不能回退任何的保存点。
在java程序中如何去控制事务的提交和回滚
事务（transaction)
如果一个事务中，只有select，那么事务控制可以忽略，如果一个事务有多个（update，delete，insert）语则需要考虑事务。
-------------------------------------------------------------------------
事务隔离级别
定义：事务的隔离级别用于指定事务的隔离程度。
脏读：当一个事务读取另一个书屋尚未提交的修改时，产生脏读。oracle数据库默认是不会出现脏读的。
不可重复度：在同一个事务中的同一个查询，如果因为别的事务修改或删除造成本事务查询的结果不一致，这种现象成为 不可重复读。
幻读：在同一个事务中的同一个查询，如果因为别的的事务添加，造成本事务查询的结果不一致，这种现象称为幻读。
oracle有两个事务隔离级别：SQL92标准中的read committed 和serializable，同时提供了非SOL92标准的read-only。
oracle的read committed说明：
① 这是oracle默认的事务隔离级别
② 保证不会脏读；但可能出现非重复读和幻读。
oracle的serializable说明：
保证不会出现脏读、不可重复读和幻读
read only
可以保证不会出现脏读、不可重复读和幻读。
serializable 和 read only的区别
serializable 可以执行 dml语句
read only 只能读取。
设置事务的隔离级别
① 设置一个事务的隔离级别
set transaction isolation level read committed;(默认的隔离级别)
set transaction isolation level serializable;
set transaction read only;
② 设置整个回话的隔离级别
alter session set isolation_level serializable;
alter session set isolation_level read committed;
java中如何设置隔离级别
conn.setTransactionIsoLation(Conneciton.xxxx);
一般我们来讲，我们java程序员不需要设置隔离级别。
维护数据的完整性
有三种方法：约束、触发器、应用程序（过程、函数）
① 约束
1）not null 非空
用于指定某列的值不能是空
2) unique 唯一
用于指定某列的值不能重复，但是可以为空
create table user(
id number,name varchar2(32) unique);
3) primary key 主键
用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null
create table uer2(id number primary key,name varchar2(32));
primary key 和unique区别
* 一个表可以有多个unique，但是只能有一个主键
* 我们每张表都应该有一个主键
* unique列的值可以为null，但是主键不行
* primary key 的所在列，会自动创建索引，但是unique不会自动创建索引
4) foreign key 外键
用于定义主表和从表之间的关系
外键细节：
* 外键指向主键列
* 外键可以指向unique列
* 外键列和主键列的数据类型要一致
* 建表时，先建主表，在建从表，删除表的时候，先删除从表，再删除主表
* 外键列可以为null
5) check 检查
用于强制行数据必须满足的条件
check(约束条件)
综合案例：
现在有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：
商品goods（商品号 goodsId，商品名 goodsName，单价 unitprice，闪屏别列 category，供应商 provider）；
客户 custom（客户号 customerId，姓名 name，住址：address 邮件：email
性别 sex 身份 cardId）；
购买purchase（客户号customerId，商品号 goodsId，购买数量nums）；
请用SQL语言完成下列功能：
1 建表，在定义中要求声明
* 每个表的主外建
* 客户的姓名不能为null
* 单价必须大于0，购买数量必须在1到30之间
* 邮件不能重复；
* 客户的性别必须是男或者女 默认是男。
create table goods(
goodsId number primary key,
goodsName varchar2(32),
unitprice number check(unitprice >0),
category varchar2(64),
provider varchar2(64)
)
create table customer(
customerId number primary key,
name varchar2(32) not null,
address varchar2(64),
email varchar2(64) unique,
sex char(2)default '男' check(sex in('男','女')),
cardId varchar2(20)
)
create table purchase(
customerId number references customer(customerId),
goodsId number references goods(goodsId),
nums number check(nums>0 and nums<=30)
)
* 商店售货系统表设计案例修改
如果在建表是忘记建立必要的约束，则可以在建表后使用
alter table命令为表增加约束，但是要注意：增加not null约束时，需要使用modify
选项，而增加其他四种约束使用add选项。
语法：
alter table 表名 add constraint 约束名 约束种类(字段);
alter talbe 表名 modify 字段名 not null;
1）每个表的主外码；
2）客户的姓名不能为null；增加商品名也能为null
alter table goods modify goodsName no null;
3）单价必须大于0，购买数量必须在1到30之间
4）邮件不能够重复；增加身份证也不重复
alter table customer add constraint uni_id unique(cardId);
5）增加客户的住址只能是“海定”、“朝阳”、“东城”、西城、通
州、崇文
alter table customer add constraint ch_addr check(address in('海定','朝阳'));
如果一张表建好后，需要对约束修改，我们可以使用alter table命令
删除约束
基本语法：
alter table 表名 drop contraint 约束名;
约束名：一个表的每一个约束，都对应一个名称
特别说明：因为一张表只能有一个主键：因此在删除主键约束的时候，可以直接使用primary key。
alter table tablename drop constraint primary key;
如果有外键值，指向该主键的某个值，则不能删除成功，需要使用 cascade【级联】
alter table tablename drop constraint primary key cascade;
---------------------------------------------------------------------------
列级定义和表级定义
在定义表后，直接在列后定义约束，我们称为列级定义
表级定义：把各个列都定义完毕后，在分别说明约束，不如：
create table user4(id number,
name varchar2(32),
contraint pk_id primary key id);
一般情况下，我们使用列级定义即可，但是如果遇到定义复合主键（两列一起作为主键）
create table user5(
id number,
idgoods number,
nums number,
constraint pk_uer_goods primary key(id,idgoods)
)
不推荐大家使用复合主键。
-----------------------------------------------------------------------
序列
① 需求：在oracle的一张表中，我们希望有一列，假设该列是整形，我们希望我们添加一条记录的时候，该列值能够自动的增长（比如从1开始增长，每次增长1）
在sql server和mysql中都是可以在定义表的时候，直接给指定自增长
sql server
create table tmp1(
id int primary key indentity(1,1),
name varchar(32));
mysql
create table tmep1(
id int primary key auto_increment,
name varchar(32));
② 解决方式：oracle是利用 序列来完成（sequence）
③ 快速入门案例：
//创建一个序列
create sequence seq
start with 1 //从1开始自增
increment by 1 //每次增加1
minvalue 1 //最小值
maxvalue 3000 //最大值 nomaxval 没有最大值
cycle //表示当序列增加到3000，从新从1开始。不想循环 nocycle
nocache //不缓存 [cache 10 : 表示一次产生10个号供你使用，使用缓存可以提高效率，缺点是可能产生跳好]
//建表
create table test(id number,name varchar2(32));
//使用
insert into test values(seq.nextval,'ccc');
说明：seq：表示序列名字，nextval:这是一个关键字
④ 序列的细节
* 可以为表中的列自动产生值
* 由用户创建数据库对象，并可以由多个用户共享
system可以使用scott创建的序列
* 一般用于主键或唯一列
* 可以使用 序列名.c urrval来看当前的序列号。
select seq.currval from dual;
* 要查看序列的当前值，必须先nextval初始化。
---------------------------------------------------------------------
索引
索引分
为单列索引和复合索引
创建索引
① 单列索引
create index 索引名 on 表名（列名）；
② 复合索引
create index 索引名 on 表名（列名1，列名2）；
细节:
* 在大表中创建索引才有意义
* 在where字句或是连接条件上经常引用的列上建索引
* 索引的层次不要超过4层
* 在逻辑型类型字段上，或者值是固定几种的列上也不要建立索引。
缺点：
* 索引的创建会增加表大小 ，是原来的1.2倍
* 创建会影响数据插入，删除，修改的效率。
-----------------------------------------------------------------
权限和角色
权限分系统权限（166种）、对象权限
① 系统权限
指对数据库管理的操作以及对数据对象的操作（创建、删除、修改）
② 对象权限
对数据对象的操作（select,update,insert,delete)
角色（本质就是多个权限的集合，用处是简化对权限的管理）分预定义角色和自定义角色
select * from dab_roles;//查看所有角色
select * from dba_sys_privs where grantee='DBA';//查看角色有什么系统权限
select * from dba_tab_privs where grantee='角色名';//查看角色有什么对象权限
select * from dba_role_privs where grantee='SCOTT'//查看某个用户拥有那些角色。
* 注意：无论是角色名或用户名都要大写
① 预定义角色
oracle提供了33预定义角色，常用的是（connect，dba，resource）
② 自定义角色
oracle设计者认为，33中预定义角色可能不能满足所有的需要，所有，可以使用自定义角色来解决，这样可以让管理员更加灵活。
基本语法：
创建自定义角色有两种方式：
不带验证（常用）
create role 角色名 not identified;
待验证
create role 角色名 identified by 密码;
练习：
假定有用户1，2，3为了让他们都拥有权限
① 连接数据库
② 在scott.emp 表上select，insert，update
使用自定义角色来完成
create role myrole not identified;
给角色赋权限
grant create session to myrole;
grant select on scott.emp to myrole;
grant update on scott.emp to myrole;
grant insert on scott.emp to myrole;
赋给指定的用户
create user ljf identified by m123;
grant myrole to ljf;
删除角色
drop role 角色名；
* 角色可以包含系统权限，也可以包含对象权限
oracle 对用户和权限的管理
① 系统权限
查询系统权限：select * from system_privilege_map order by name;
常用的有：
create session 连接数据库 create table 建表
create view 建视图 create public synonym 键同义词
create procedure 建过程。函数。包 create trigger 建触发器
create cluster 建簇
系统权限使用管理的案例
1 创建两个用户ken，tom。初始阶段他们没有任何权限，如果登录就会给出错误的信息。
1.1 创建两个用户，并指定密码。
create user ken identified
by m123;
create user tom identified by m123;
2 给用户ken授权
2.1 授予create session和create table权限时，带with admin option
grant create session to ken with admin option//带with admin option就表示ken可以把他得到的权限授予给其他用户。
回收系统权限
使用system回收ken的create session权限
revoke create session from ken;
对象权限
概念：指访问其他方案对象的权限
需要dab查询对象权限：
select distinct privilege from dba_tab_privs;
基本语法：
grant 对象权限 on 方案.数据对象 to 用户[角色][with grant option]
特别说明：可以把权限直接赋给角色
回收对象权限
revoke 对象权限 on 方案.数据对象 from 用户
对象的权限是级联回收
oracle的pl/sql编程
pl(procedural language)/sql
基本概念：pl/sql是在标准sql语句基础上扩张的一种oracle数据库进行编程的语句。可以定义常量和变量，而且可以使用条件语句和循环语句。
sql语句的缺陷：
1 不能模块化编程。为了完成下订单，可能我们要发出几条sql
2 执行速度很慢。
3 安全性问题
4 浪费带宽
解决之道
使用pl/sql来编写过程，以提高效率
快速入门案例
开发一个简单的存储过程，可以完成想某表添加一条记录
create procedure pro1 is
begin
insert into emp (empno,ename) values(3333,'4444');
end;
/
1 如何在控制台调用
exec 过程名（参数1，参数2）；
2 java程序执行
pl/sql可以做什么
利用pl/sql可以开发 过程、函数、包（包体）、触发器
他们的基本编程单元 是块。
pl/sql编程的规范
① 注释
单行注释 --
多行注释 /*....*/来划分
② 标示符号的命名规范
1）当定义变量时，建议用v_作为前缀 v_sal
2）当定义常量时，建议用c_作为前缀 c_rate
3）当定义游标时，建议用_cursor作为后缀emp_cursor
4）当定义例外时，建议用e_作为前缀 e_error
块（block）的开发
块的基本结构
declare
--定义变量的部分
begin
--执行部分
exception
--捕获例外
end;
/
* declare 和 exception 部门是可选的，并不是必须的。
① 只包含执行部分的案例
输出helloworld
--开发一个只包括执行部分的块
begin
dbms_output.put_line('helloworld');
end;
特别注意：要设置 set serveroutput on 才能在控制台中输出helloworld。
declare
v_ename varchar2(32);
begin
select ename into v_ename from emp where empno=&empno;
dbms_output.put_line(v_ename);
end;
/
ORA-01403: no data found
ORA-06512: at line 5
从上面的案例可以看出，如果我们输入的用户编号不存在，则系统会提示异常，为了更加明确的指出错误，oracle提供了 异常（exception）处理机制
快速入门：
declare
v_ename varchar2(32);
begin
select ename into v_ename from emp where empno=&empno;
dbms_outp
ut.put_line(v_ename);
exception
when no_data_found then
dbms_output.put_line('朋友，您输入的编号有误！');
end;
/
对该案例的细节说明
这里我们涉及到异常处理：
异常的基本语法：
exception
when 异常的名称 then
//对异常进行处理的代码。。
when 异常的名称 then
//对异常进行处理的代码。。
end;
异常处理
1 可以给出捕获异常，并给出明确提示
2 有时可以利用异常，来进行业务处理
过程的进一步讲解
oracle过程，可以指定参数是输入的参数，还是输出的参数，基本语法如下：
create procedure 过程名(变量名 in 变量类型...变量名 out 变量类型...)is
//定义变量
begin
//执行代码
end;
考虑编写一个过程，可以输入员工名，新工资，可修改员工的工资
create or replace procedure pro3(in_ename in varchar2, in_newsal in number) is
begin
update emp set sal=in_newsal where ename=in_ename;
end;
/
* 当我们编写过程时，可以输入 show error 来显示具体的错误。
如何在java程序中调用过程
需求说明:使用java调用刚才的过程。
create or replace procedure pro4(in_empno in number,in_newsal in number) is
v_ename varchar2(32);
begin
--下面的这句语句没有实际的作用，是为了出发异常处理而写
select ename into v_ename from emp where empno=in_empno;
update emp set sal=in_newsal where empno=in_empno;
exception
when no_data_found then
dbms_output.put_line('输入的号码无效！');
end;
函数
基本语法：
create function 函数名（参数1...)
return 返回的数据类型 is
--定义变量
begin
//execute
end;
案例：编写一个函数，完成输入用户姓名，返回年工资。
create function fun1(in_v_ename varchar2)
return number is
--定义一个变量来接收年薪
v_annual_sal number;
begin
select (sal+nvl(comm,0))*13 into v_annual_sal from emp where ename=in_v_ename;
return v_annual_sal;
end;
如何调用函数
1 在控制台调用 select 函数名（实际参数） from dual；
2 java中调用
包
为什么需要包：使用包可以更好的管理 自己写的函数、过程
包的基本语法：
create package 报名 is
--声明函数
funtion 函数名（参数1...）return 返回类型；
--声明过程
procedure 过程名（参数1...)；
end;
包体
触发器
触发器是一个隐含执行的过程，他不是由程序员或则dba来显示调用，而是因为某个操作引发执行的。
pl/sql 语法
pl/sql 的数据类型分为
① 标量类型(scalar)
和oracle的数据类型相似
注意：pl/sql 在定义一个变量的时候，如果要赋初值要用 := ;
%type
为了让我们在pl/sql 编程中，让变量的类型和大小与表的列的大小和类型一致。
② 复合类型
* pl/sql记录
基本语法：
type 名称 is record(
变量 变量的类型
，
变量 变量的类型
)；