Oracle学习笔记

连接Oracle工具
运行：sqlplusw、sqlplus、Oracle
sqlplus worksheet 开发用的
Enterprise Manager Console  图形操作

###########################################################远程连接
pl/sql developer连接远程数据库
开始-》所有程序-》Oracle-OraHome92-》Configuration and Migration Tools-》Net Manager
打开一个界面点  本地-》服务命名
再点左面的绿色的“+”，Net服务名填写你想要显示的名字例如（orcl_ip）,然后下一步
选 TCP/IP（Internet协议）  下一步
主机名： 远程数据库的IP地址，端口默认（远程oracle的端口）  下一步
服务名：windows看服务名，unix好像echo $ORACLE_HOME，我的机器orcl 下一步
测试，更改用户密码测试，如果成功显示如下
scott  tiger

###########################################################登录数据库
conn 用户名/密码@网络服务名 as sysdba|sysoper
connectscott/tiger@oracle
conn scott/tiger@oracle   远程访问，服务名oracle
conn scott/tiger   本地
conn system as sysdba
conn system/123456 as sysdba

show user
disc 断开连接
passw  修改密码

###########################################################文件操作命令
文件操作命令：
@ d:\a.sql  或 start d:\a.sql  执行.sql里德SQL命令
edit d:\a.sql  编写a.sql
spool d:\b.sql  …… spool off  将sql*plus屏幕上的内容……输出到指定文件中

###########################################################显示和设置环境变量
显示和设置环境变量
show linesize
set linesize 90  每行显示90个字符
set pagesize 5   每5行分页
###########################################################权限
创建用户（需要system权限）
create user 用户名 identified by my123;   创建用户时不能以数字开头
password 用户名;   修改用户密码，修改别人密码需dba或拥有alter user权限
alter user 用户名 identified by 密码
drop user  用户名     自己不能删除自己
drop user 用户名 cascade     在删除用户时，如果用户已经创建了表，连表一起删除
刚创建的用户，没有任何权限，连登录权限都没有。
权限分为：系统权限140多个、对象权限25个（select、insert、update、delete、all）
系统权限：create session有此权限才可登录到数据库
角色：具有一系列权限 connect、dba、resource（在任何一个表空间建表）
grant connect to 用户名    具备登录数据库权限
grant resource to 用户名   具备建表权限
grant select on emp to 用户名  把对emp表的select权限付给用户。
在scott用户执行grant select on emp to 用户名（把对scott的emp表的select权限付给用户）。登录用户名执行select * from scott.emp;
grant all on emp to 用户名;   把emp表操作的所有权限付给用户。

revoke select on emp from 用户名;   收回权限

grant select on emp to 用户名 with grant option   对象权限+with grant option可将获得的权限再次分给其他用户
grant   connect     to 用户名 with admin option   系统权限+with admin option可将获得的权限再次分给其他用户

注：如果用户A将权限分给B，B将权限跟给C，将B的权限收回后，C权限也将自动被收回。株连模式

########################################################### profile文件实现管理用户口令

create profile 文件名加锁 limit failed_login_attempts 3 password_lock_time 2;  密码输入3次，锁定2天
alter user 用户名 profile 文件名加锁;  给用户加锁
alter user 用户名 account unlock;   解锁

create profile 文件名 limit password_life_time 10 password_grace_time 2;   每10天修改密码，宽限期2天
create profile 文件名 limit password_life_time 10 password_grace_time 2 password_reuse_time 5; 指定口令可重用时间5天后可重用

drop profile 文件名      删除profile文件,被profile约束的用户可释放
###########################################################表管理，数据类型
以字母开头
长度30字符
不能用oracle保留字
A-Z,a-z,0-9,$,#等
字符型:
char 最大2000字符
例： char(10) '小韩'前4个字符放'小韩'，后添6个空格不全。查询速度快，例如身份证，全字符比较。
varchar2(20) 变长 最大4000字符
例：varchar2(10) '小韩' 只分配4个字符。节省空间，查询速度慢，一个一个字符比较。
clob(character large objient) 字符型大对象 最大4G
数字型:
number 10-38次方-10+38次方
number(5,2)  表示一个小数有5位有效数，2位小数，范围-999.99-999.99
number(5) 表示一个5位整数，范围-99999-99999
日期：
date 年月秒
datestamp 毫秒级，银行项目
图片：
blob 二进制数据，可存放图片、声音 4G ， 一般保密性高的图片、声音可以放入数据库，普通只存放图片路径
###########################################################表管理，表操作
学生表
create table student(
xh number(4),--学号
xm varchar2(20),--姓名
sex char(2),--性别
birthday date,--出生日期
sal number(7,2) --奖学金
);
desc student  --查看表结构
alter table student add (classid number(2)); --添加字段
alter table student modify (xm varchar2(30)); --修改字段
alter table student drop column sal; --删除字段 （一般不用）
rename student to stu; --修改表名
drop table student; 删除表
###########################################################表管理，数据操作
insert into student values ('A001','张三','男','01-5月-05',10);   oracle默认日期格式'DD-MON-YY'
alter session set nls_date_format='yyyy-mm-dd'; --修改默认日期格式
insert into student(xh,xm,sex) values('A003','John','女'); --插入字段
insert into student(xh,xm,sex) select * from student;  --复制自身插入数据，速度2的N次方。
insert into student(xh,xm,sex,birthday) values(''A003','John','女',null); --插入空数据
select * from student where birthday is null/not null;  --查询空数据
update student set sex='女',birthday='1997-12-11' where xh='A001'; --修改数据
delete from student;  删除所有记录，表结构还在，写日志，可以恢复，删除速度慢
savepoint aa; 保存点
roolback to aa; --回滚
drop table student; --删除表的结构和数据
delete from student where xh='A001'; --删除一条记录
truncate table student; --删除表中所有记录，表结构还在，不写日志，无法找回删除记录，删除速度快
###########################################################表管理，select操作
set timing on;  显示sql语句执行完成时间
如何处理null值
select sal*13+nvl(comm,0)*13 "年工资",ename,comm from emp; --nvl(comm,0) 如果nvl为空，就用0计算，如果不为空就用本身值。
日期大于1982-1-1要写成  date>'1-1月-1982'  不能写成 date>'1982-1-1'  此为Oracle默认的日期格式
like % _  0到多个字符，1个字符
is null / is not null
select * from emp by deptno,sal desc;   --desc降序
select ename,(sal+nvl(comm,0))*12 as "年薪" from emp order by "年薪"
###########################################################Oracle复杂查询、子查询
max,min,avg,sum,count分组函数
select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;
1、分组函数只能出现在选择列，或having、order by子句中；
2、如果在select语句中同时包含group by,having,order by顺序为group by,having,order by；
3、选择列中如果有列、表达式、分组函数，则这些列和表达式必须有一个出现在group by子句中。
多表查询条件，至少为表个数-1。笛卡尔集。
between  1 and 2；
自连接：
select worker.ename,boss.ename from emp worker,emp boss where worker.ename=FROD;
子查询：
把能排除最多查询数据的条件放在子查询的左边，SQL语句扫描 左->右。
in
select * from emp where sal> all(select sal from emp where deptno=30); 
select * from emp where sal> (select max(sal) from emp where deptno=30); --效率高
select * 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); --效率高
多列子查询：
select * from emp where (deptno,job)=(select deptno,job from emp where ename="SMITH";
不能给表取别名。
###########################################################Oracle分页 三种方式
1、根据rowid分来
select * from emp where rowid in (select rid from (select rownum rn,rid
from(select rowid rid,cid from emp drder by cid desc) where rownum<=10) where rn>=6 order by cid desc;
执行时间0.03秒
2、按分析函数来分
select * from (select a1.*,row_number() over (order by cid desc) rk from emp a1) where rk<10000 and rk>9980;
执行时间1.01秒
3、按rownum来分
select a1.*,rownum rn from (select * from emp) a1 where rownum<=10;
select * from (select a1.*,rownum rn from (select * from emp order by cid desc) a1 where rownum<=10) where rn>=6; 二分法，查询速度快
修改列显示，只需修改select * from emp。
执行时间0.1秒
emp表明，cid关键字段，取第6-第10记录，7万条记录。

###########################################################Oracle合并查询
M union N where  合并排重   --M、N为表
M union all N where  合并
M minus N   M减N
M intersect N 取M、N交集
###########################################################Oracle 创建数据库
Configuration and Migration Tools——Database Configuration Assistant
Data Warehouse  数据仓库
General Purpose 普通用途
New Database 
Transaction Processing 事物处理
###########################################################Oracle 事务和锁
create table myemp (id,ename,sal) as select empno,ename,sql from emp; --用查询结果创建一个新表
insert into myemp (Myid,myname,mydept) select empno,ename,deptno from emp where deptno=10; --批量插入数据
update emp set (job,sal,comm) = (select job,sal,comm from emp where ename='SMITH' where ename='SCOTT';

commit; --提交事务，执行后，会确认事务的变化、结束事务、删除保存点、释放锁
事务几个重要操作：
1、设置保存点  savepoint a
2、取消部分事务 rollback to a
3、取消全部事务 rollback