spool
例子：spool e:\aa.sql
从上面的语句开始打印查询到的文字到aa.sql 到 spool off结束
使用oracle ：
在运行栏中输入 ：sqlplusw
直接运行
进入dos中运行 ：在运行栏中输入sqlplus
CREATE USER XIAOMING IDENTIFIED BY M123
建立 用户小明同时设置密码 M123 密码必须以字母开头
删除用户 drop user 用户名
修改别人的密码（必须是管理员）：
①alter user xiaoming identified by m123
② password 用户名
修改自己的密码：直接输入 passw
连接系统用户conn system/root password xiaoming
断开连接：disc
新创建的用户是不能登录数据库的 需要为其指定相应的权限
GRANT CONNECT TO XIAO
start和@都可以运行sql脚本(运行sql脚本里面写的sql语句)
例如：@d:\a.sql或者start d:\a.sql
edit可以编辑指定的sql脚本
edit d:\a.sql
&(交互式命令)可以替代变量
select * from emp where job=’&job’;
系统权限>>是指用户对数据库的相关权限，建库，建表等，登陆数据库，修改密码
对象权限>>是指用户对其他用户的数据对象操作的权限。SELECT,INSERT,UPDATE,DELETE, ALL,CREATE ,INDEX
数据对象>>用户创建的表，存储过程，触发器，序列，同义词等都是数据对象

sys用户： 超级管理员 权限最高 他的角色 dba
system用户： 是系统管理员 权限也很高 他的角色dbaoper
scott用户： 普通用户
sys与system
sys有 create database 的权限，而 system没有，其他类似
sys用户必须以as sysdba或as sysoper形式登录，不能以normal方式登录数据库

连接数据库
conn 用户名/密码 [as sysdba]

在删除用户时，如果要删除的用户已经创建了表，那么就需要在删除时
带一个参数CASCADE 比如 DROP USER 用户名 CASCADE

读写事物处理
set transaction read write
只读事物处理
set transaction read only设置只读事物后在他之后发生的事物都只能查询

给新建用户登录权限
grant create session to xiaoming
希望新创建的用户可以查询已经创建用户的东西
GRANT select on emp to xiaoming
修改权限
GRANT update on emp to xiaoming
只修改列权限 例如(sal)
grant update on emp(sal) to monkey
把所有的权限给xiaoming
grant all on emp to xiaoming
收回权限
REVOKE SELECT ON EMP FROM XIAOMING
希望小明可以把这个权限给别人
GRANT SELECT ON EMP TO XIAOMING WITH GRANT OPTION

查询用户名 去除重复 distinct
select distinct dname from scott.dept

系统权限不级联回收，对象权限级联回收
如果是系统权限往下面派发是用admin
如果是对象权限往下面派发是用grant
with admin option ,with grant option

ascii(‘a’)把字符转换成Ascii码

exists : 强调的是是否返回结果集，不要求知道返回什么, 比如：SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)

and 与 or或 not 非

不等于操作符 <>

打开显示操作时间的开关
Set timing on

select * from emp where comm is null
select * from emp where comm is not null

LIKE 语句
% 表示任意0到多个字符 _ 表示任意单个字符
例：如何查询首字符为S的员工的姓名和工资
SELECT SAL,ENAME FROM EMP WHERE ENAME like ‘S%’
名字当中不含有R的
select * from emp where ename not like’%R%’

使用profile管理用户口令
Profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名称为default的profile。当建立用户没有指定profile选项时，那oracle就会将default分配给用户。一个案例，最多允许用户输出3次错密码，输错后锁定2天
使用：首先创建profile文件（规则），
Create profile 规则名称limit failed_login_attempts 3 password_lock_time 2;
然后把这个规则指定给某一个用户
Alter user 用户名 profile 规则名称

终止口令>>为了让用户定期修改密码可以使用终止口令的指令来完成。
例如：要求用户每隔十天就修改一次密码，宽限期为2天。
Create profile 名字 limit password_life_time 10 password_grace_time 2;
Alter user 用户名 profile 名字

口令历史>>如果希望用户在修改密码时，不能使用以前使用过得密码就可以使用口令历史，这样oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。
一个例子：先建立 profile
Create profile 规则名称 limit password_life_time 10 password_grace_time 2 password_reuse_time 10
Alter user 用户名 profile 规则名
Password_reuse_time 10指定口令可重用的限制时间是10天，10天后可重用。
删除profile
Drop profile 规则名 [cascade]

锁定账号 ALTER USER 用户名 ACCOUNT LOCK
解锁 ALTER USER 用户名 ACCOUNT UNLOCK

char 查询速度快
varchar2节省空间
。。。。。。。。。。
复制一个表
Create table emp2 as select * from emp

修改一个表
ALTER TABLE SCOTT.TEST RENAME TO TEST1–修改表名
ALTER TABLE emp RENAME COLUMN NAME TO NAME1 –修改表列名
ALTER TABLE emp MODIFY NAME1 NUMBER(20) –修改字段类型
ALTER TABLE emp ADD ADDRESS VARCHAR2(40) –添加表列

alter table student modify ( xm varchar2(20))
修改字段的类型或者名字，不能有数据哦

alter table student drop column sal
删除一个字段

用insert into 插入多行数据
insert into kkk(myid,myname,mydept) select empno,ename,deptno from emp where deptno=10

用update语句更新多个数据
UPdate emp set(job,sal,comm)=(select job,sal,comm from where ename=’SMITH’)
WHERE ENAME=’SCOTT’

修改日期格式
alter session set nls_date_format=’yyyy-mm-dd’;

drop table 删除表的结构和数据
truncate table 删除表的数据，结构还在，不写日志，无法找回数据，删除速度快

select count(*) from table 可以查看一共有多少条记录

ORDER BY
SELECT * FROM EMP ORDER BY SAL (asc) 按照工资由低到高的顺序显示
SELECT * FROM EMP ORDER BY SAL DESC 从高到低
默认为由低到高

GROUP BY
分组查询 分组函数只能出现在选择列表，having,order by 里面
SELECT AVG(SAL) ,MAX(SAL),DEPTNO FROM EMP GROUP BY DEPTNO
select avg(sal),min(sal) ,deptno,job from emp group by deptno,job
having分组查询中的条件查询
如果在select语句中同时包含有group by ,having,order by 那么他们的顺序是 group by ,having,order by
显示每个部门的平均工资，和最高工资，并且最高工资大于2000
select deptno ,avg(sal) from emp group by deptno having avg(sal)>2000;

函数
数字函数
ABS(N)绝对值函数
CEIL(N)用于返回大于等于数字N的最小整数
FLOOR(n) 小于等于数字N的最小整数
MOD(M,N) 取模 取余数m/n
round(n)四舍五入
round(n,m)精确到小数点后面m位 m是负数四舍五入到小数点前m位
sign(n)判断当前的值是正负 n<0 返回-1 n>0 返回1 0 返回0
trunc(n)截取n到整数位
trunc(n,m)截取到小数点后m位
如果m<0，-2则截取n的个位和十位为0同时让n为整数

Clear 清屏命令
字符函数
instr(c1,c2)搜索字符函数 c1为被搜索的字符集，c2为被搜索的字符
chr(n)将数字转换成字符串
concat(a,s)用于连接字符串等价于||
lower(‘n’),将字符转换成小写
upper(‘n’)将字符转换成大写
length(‘dddd’)返回字符串的长度
replace(char1,search_string,replace_string)用来替换字符的函数
substr(char,m,n)截取字符串的字串 从第m个字符开始截取，截取n个
initcap(‘ddd’) 使字符串的首字符大写
LPAD(A,N,D)往A前添加D到长度为N个字符RPAD同上，往右面添，如果A本身大于N则删除对应方 向的字符到N的长度
LTRIM(‘FDDD’,F’) 删掉左端F
RTRIM() 同上，删右端的

日期函数
Oracle中默认添加日期的格式为日月年例如：11-11月-1988
insert into emp(hiredate) values(to_date(‘1988-12-12’,’yyyy-mm-dd’))
sysdate 当前日期
CURRENT_DATE 返回当前会话区的日期
CURRENT_TIMESTAMP 同上
add_months(d1,n)返回在日期d1基础上再加上n个月后的新日期
select sysdate，add_months(sysdate,3) from dual
返回：2008-11-5,2009-2-5
EXTRACT(YEAR FROM SYSDATE) 提取当前的年份
last_day(sysdate) 返回当前月最后一天
MONTHS_BETWEEN(日期1，日期2) 两个日期相差月数
next_day(sysdate，’星期五’) 返回下一个星期五是多少号
sysdate-hiredate 可以查到SYSDATE距离HIREDATE的天数
to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’)设置指定的日期格式也可以只写YYYY或者MM或者DD只查出来对应的年或者月或者日
to_char(sal,’L999,999.99’) 查看薪水并在前面加￥

系统函数
decode(expr,search1,result1，search2,result2default)如果search1匹配与expr则返回result1 如果search2匹配expr则返回sesult2 依此类推 如果都不满足则返回default
截取第一位的字符(K)方法,查找第一位为k的值
substr(ename,1,1)
select instr(ename,’k’) from emp
select ascii(‘k’) from emp
select lpad(ename,1) from emp

判断是否是null值操作的函数
Oracle中进行操作（加减乘除）只要有一个是null值，操作的结果就是null了。
Nvl(comm,0) 如果comm为null就返回0，否则返回comm
查询约束
desc user_constraints
select constraint_name ,constraint_type from user_constraints where table_name=’EMP’
显示约束列
select column_name,position from user_cons_columns where constraint=’约束名’
内嵌视图 把子查询当成一个表来使用
当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名。并且不能加AS

分页
rownum
select * from emp
显示 rownum
select a1., rownum rn from (select from emp) a1
挑选出前十条

select a1.*, rownum rn from (select * from emp) a1 where rownum<=10

在挑选出后5条

select * from(select a1.*, rownum rn from (select * from emp) a1 where rownum<=10)where rn>=6

在分页中不能使用and 和between and, rownum只能用一次

几个查询的变化
指定查询列只需修改最里层的select查询
排序也只修改最里层的
二、根据ROWID来分页
三、按分析函数来分页
用查询的结果来创建一张新表
Create table myemp(id,ename) as select empno,ename from emp;

合并查询
有时候在实际应用中，为了合并多个select 语句的结果，可以使用集合操作符号union,union all, intersect,minus
union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行
select ename,sal,job from emp
where sal>2500 union
select ename,sal,job from emp
where job=’MANAGER’
union all
该操作赋予union 相似，但是它不会取消重复行，而且不会排序
intersect
使用该操作符用于取得两个结果集的交集
minus
使用该操作符用于取得两个结果集的差集，它只会显示存 在第一个集合中，而不存在第二个集合中的数据

表的自连接查询
例如：显示emp表中雇员名和他的上级名
select clerk.ename,boss.ename from emp clerk,emp boss where clerk.mgr=boss.empno;

创建数据库使用工具向导
Oracle10G_home>>Application Development>>Configuration and Migration Tools>>database configuration assistant

用odbc方式连接oracle数据库 不需要扎包
首先我们要先配置一个数据源
Class.forname(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection ct=DriverManager.getConnection(“jdbc:odbc:shujuyuan”,”scott”,”root”);
注意：shujuyuan为您的数据源名称
用odbc方式连接数据库，你的java文件要和数据库在一个服务器上，因为它没有写IP地址。

用jdbc的方式连接oracle数据库 需要扎包
加载驱动
Class.forname(“oracle.jdbc.driver.OracleDriver”);
Connection ct=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:myorcl”,”scott”,”root”);
注意上面myorcl为你要连接的那个数据库名字

子查询>>多个select 语句在一个select语句里面
单行子查询>>只返回一行数据的子查询
多行子查询>>返回多行数据的子查询
查询工资比30号部门所有员工的工资高的员工的姓名(all的用法)
Select ename from emp where sal>all(select sal from emp where deptno=30);
查询工资比30部门的任意一个员工的工资高的员工的姓名(any的用法)
Select ename from emp where sal>any(select sal from emp where deptno=30);
返回多列的子查询
查询出与smith部门号和职位都相同的员工的信息，下面的代码都可以查出，但是效率不同，下面的代码效率要高一些，因为它只查询了2次，上面的查询了3次
select * from emp where deptno=(select deptno from emp where ename=’SMITH’) and job=(select job from emp where ename=’SMITH’);
select * from emp empp,(select emp.deptno sdeptno,emp.job sjob from emp where emp.ename=’SMITH’) semp where empp.deptno=semp.sdeptno and empp.job=semp.sjob;
第三种方法 貌似也没有第二种方法效率高
select * from emp where (deptno,job)=(select deptno,job from emp where ename=’SMITH’);
事务：
提交事务：commit 会删除保存点 当退出数据库的时候就会自动提交
回退事务：rollback to a1
取消全部事务：rollback
保存点：savepoint a1
Java中加入事务处理：
Connection ct=null;
ct.setAutoCommit(false); //设置不能默认提交；
然后在你想提交的地方设置提交 ct.commit();
如果发生了异常就回滚 ct.rollback();

查看数据库
desc v$database
%type 一个变量
%rowtype 一行变量 rowtype 前面可以接表明也可以接游标名

调用存储过程
exec 存储过程名
call 存储过程名
begin 存储过程名 end;

查看已经存储的东西
desc user_source
select text from user_source
where name=’存储过程名’


关闭数据库 shutdown 启动数据库 startup
初始化参数
初始化参数用于设置实例或是数据库的特征
显示初始化参数 show parameter

在导入和导出的时候要到ORACLE的主目录bin目录下
运行cmd 到ORACLE的BIN目录下在运行下面的代码

数据库的备份和恢复
逻辑备份是指使用工具export将数据对象的结构和数据导出到文件的过程，逻辑恢复是指当数据库对象被误操作而损坏后使用工具import利用备份的文件把数据对象导入到数据库的过程。物理备份即可在数据库open的状态下进行也可以在关闭数据库进行，但是逻辑备份和恢复只能在open的状态下。
导出自己的表
exp userid=scott/root@orcl tables=(emp,dept) file=d:\e1.dmp
导出其它方案的表
如果要导出其它方案的表，需要DBA的权限
exp userid=system/root@orcl tables=(scott.emp) file=d:\e1.dmp
导出表的结构
exp userid=scott/root@orcl tables=(emp,dept) file=d:\e1.dmp rows=n
使用直接导出的方式导出表结构
exp userid=scott/root@orcl tables=(emp,dept) file=d:\e1.dmp direct=y
这种方式比默认的常规方式速度要快，当数据量大时，可以考虑使用这样的方法。

导入自己的表
imp userid=scott/root@orcl tables=(emp) file=d:\ss.dmp
导入表到其他用户
要求该用户具有dba的权限，或是imp_full_database
imp userid=system/root@orcl tables=(emp) file=d:\xx.dmp touser=scott
导入表结构，只导入表的结构而不导入数据
imp userid=scott/root@orcl tables=(emp) file=d:\xx.dmp rows=n
导入数据，如果对象(此表)已经存在可以也导入表的数据
imp userid=scott/root@orcl tables=(emp) file=d:\xx.dmp ignore=y

导出数据库（备份）
导出数据库是指利用export导出所有数据库中的对象及数据，要求该用户具有dba的权限或是exp_full_database权限
exp userid=system/root@orcl full=y inctype=complete file=d:\x.dmp
导入数据库（恢复）
在默认情况下，当导入数据库时，会导入所有对象结构和数据，案例如下
imp userid=system/root full=y file=d:\xx.dmp

导出方案
导出自己的方案
exp userid=scott/root@orcl owner=scott file=d:\e1.dmp
导出其它方案 需要dba的权限
exp userid=system/root@orcl owner=scott file=d:\e1.dmp

user_tables
显示当前用户所拥有的表
all_tables
显示当前用户可以访问的表
dba_tables
当前数据库所有的表

select * from dba_users
查询数据库的所有用户
dba_sys_privs
显示用户所具有的系统权限
dba_tab_privs
显示用户具有的对象权限
dba_col_privs
显示用户具有的列权限
dba_role_privs
显示用户所具有的角色
SELECT * FROM DBA_ROLES
查询ORALCE中有多少个角色

查询ORACLE中所有的系统权限，一般是DBA
select * from system_privilege_map order by name;
查询ORACLE中所有的对象权限，一般是DBA
select distinct privilege from dba_tab_privs
查询数据库的表空间
select tablespace_name from dba_tablespaces

查询一个角色包含的权限
一个角色包含的系统权限，对象权限
系统权限
用户针对数据库的权限 建库，建表，登录数据库。。。
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=’角色名(dba,connect)’
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE=’角色名(connect,dba)’

查看某个用户具有多少角色
SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE=’角色名(system,scott)’
对象权限
如果用户要访问其他对象的方案必须赋给对象权限
SELECT * FROM DBA_TAB_PRIVS WHERE
GRANTEE=’角色名(dba,connect)’
角色
例如 DBA ，connect,resource

授予权限
授予权限一般是由DBA来完成的，如果用其他用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限。
在授予系统权限时，可以带有with adim option选项，这样被授予权限的用户或角色还可以将该系统权限授予其他
用户或角色
grant session ,create table to ken with adminoption

约束
not null
如果在列上定义了，那么当插入数据时，必须为列提供数据
unique
当定义了唯一约束后，该列值是不能重复的，但是可以为
null
primary key
用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null
需要说明的是：一张表最多只能有一个主键，但是可以有
多个unique约束
foreign key
用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具有主键约束或unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null
check用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间，如果不在1000-2000之间就会提示出错。

若强行把正在被别的表引用的主键删除可在primary key 后添加cascade

列级定义
列级定义是在定义列的同时定义约束、
create table department(
deptid number(2) constraint pk_department primary key,
loc varchar2(12))
表级定义
表级定义是在定义了所有列后，在定义约束，这里要注意：not null 约束只能在列级上定义
create table employee2(
emp_id number(4),name varchar2(15),
dept_id number(2),
constraint pk_employee primary key (emp_id),
constraint pk_department foreign key (dept_id) references deparyment4(dept_id));

索引的缺点
建立索引系统要占用大约为表的1.2倍的硬盘空间和内存来保存索引
更新数据时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性

显示表的所有索引
dba_indexs ,user_indexs 可以显示索引信息。其中dba_indexs用于显示数据库所有的索引信息，而user_indexs用于显示当前用户的索引信息。
select index_name,index_type from user_indexswhere table_name=’表名’>>显示索引对应的列信息
通过查询数据字段视图user_ind_columns 可以显示索引对应的列的信息
select talbe_name,column_name from user_ind_columns where index_name=’索引名’

表空间：
ORACLE 的逻辑结构包括：表空间，段，区，块
表空间的作用：
控制数据库占用的磁盘空间
Dba可以将不同数据类型部署到不同位置，这样有利于提高i/o性能，同时利于备份和恢复等管理操作

建立表空间：
一般情况下是又dba来完成的，普通用户要建立表空间要具有create tablespace的系统权限
Create tablespace data01 datafile ’ d:\data01.dbf’ size 20m uniform size 128k
Uniform size 128k为区的大小

使用表空间：
Create table mypart( deptno number(4),dname varchar(14)) tablespace data001
改变表空间的状态：
使表空间脱机：alter tablespace 表空间名 offline
使表空间联机：alter tablespace 表空间名 online

只读表空间：
当建立表空间时，表空间可以读写，如果不希望在该表空间上操作，那么可以修改为只读
Alter tablespace 表空间名 read only
修改为读写表空间
Alter tablespace 表空间名 read write 假如写成 write read 就不好使了，这点有点死板啊
显示表空间里面所有的表：
Select * from all_tables where tablespace_name=’表空间名’
知道表名，查看属于哪个表空间：
Select tablespace_name,table_name from user_tables where table_name=’表名’
删除表空间
Drop tablespace ‘表空间名’ including contents and datafiles
说明：including contents 表示删除表空间时，删除该空间的所有数据库对象，而datafiles表示将数据库文件也删除

修改表空间名称：
Alter tablespace data001 rename to data002
查看每个用户的默认表空间：
Select user_id,username,default_tablespace from dba_tables
修改数据库默认表空间：
Alter database default tablespace 表空间名

扩展表空间：
(1) 增加数据文件
Alter tablespace data001(表空间名) add datafile ’d:\data002.dbf’ size 20m
(2) 增加数据文件的大小
Alter datebase datafile ‘d:\data001.dbf ’ resize 30m
这里需要注意的是数据文件的大小不要超过500m
(3) 设置文件的自动增长
Alter database datafile ‘d:\data001.dbf’ autoextend on next 10m maxsize 500m

移动数据文件
(1) 确定数据文件所在的表空间
Select tablespace_name from dba_data_files where file_name=’d:\ORACLE 练习记录\data001.dbf
(2) 使表空间脱机
确保数据文件的一致性，将表空间转变为offline的状态
(3) 使用命令移动数据文件到指定的位置
Host move d:\ORACLE 练习记录\data002.dbf e:\临时文档\data002.dbf
(4)执行alter tablespace 命令
Alter tablespace data001 rename datafile ‘:\ORACLE 练习记录\data002.dbf’ to ‘e:\临时文档\data002.dbf ‘
(5)是表空间联机
Alter tablespace data001 online

在MOUNT状态下移动数据文件
SYSTEM表空间和SYSAUX表空间不能被脱机，为了移动这两种表空间的数据文件，必须在MOUNT状态下使用alter database 命令来完成。需要注意，这种方法可用于移动任何表空间的数据文件。
(1)关闭并装载数据库
Sqlplus sys/dd as sysdba
Shutdown immediate
Startup mount
(2)移动数据文件到目标位置
Host move d:\ORACLE 练习记录\data002.dbf e:\临时文档\data002.dbf
执行ALTER DATABASE 命令
Alter database rename file ‘d:\ORACLE 练习记录\data002.dbf’ to ‘e:\临时文档\data002.dbf’
(3) 打开数据库
Alter database open