用户角色
连接
conn[ect] user/pass@服务 as sysdb/sysoper
断开连接 disc
修改密码自己passw
start/@ d:aa.sql 执行脚本
edit aa.sql 编辑文本
spool d:\bb.sql spool off; 将屏幕上的内容写入d盘
行宽set linesize
行数set pagesize
show user 显示当前用户
desc 表名 ，查看表结构
用户管理
（管理员操作，不允许自杀）
create user zhao identified by zhao;创建用户
password zhao修改密码
drop user zhao 【cascade】 删除用户,如果该用户已经建表则带参数
grant connect to zhao 赋权限 connect角色
revoke
权限：多个权限组成角色
系统权限：用户对数据库的相关权限
对象权限：用户对其它用户的数据对象操作的权限
角色
预定义角色：（自带的）connect,dba,resource(在任何一个表空间建表)
自定义角色：
口令管理：
create profile aa limit failed-login-attempts 3 password-lock-time 2;用户只能尝试3次登陆，锁定时间为2天。
alter user zhao profile aa;
管理权限和角色
所有角色信息select * from dba_roles;
所有权限信息select * from system_privilege_map order by name;
给用户授权限（系统权限）
grant create session,create table to 用户名 with admin option--被授权的用户还可以将该权限传递
grant create view to 用户名
回收系统权限 （不是级联回收）
revoke create session from 用户名
对象权限：有alter修改表结构 delete select insert update 修改数据 index索引 references 引用 execute 执行
授对象权：
with grant option 选项不能被授予角色，被授权者可以将该权限传递给其他用户，
grant select on emp to 用户名 --授查询权限
grant update on emp to 用户名
grant all on emp to 用户名
grant update on emp(sal) to 用户名 --授予列权限
grant select on emp(ename,sal) to 用户名 --只可查两个字段
回收对象权限：（是级联回收）
revoke select on emp from 用户名
角色：预定义，事先定好的角色，自定义
预定义角色:常用有connect ,resource,dba
connect角色有系统权限：alter session,create cluster
create database link,create session,create table,create view ,create sequence
resource 角色：
create cluster ,create indextype,create table,create sequence ,create type,create procedure,create trigger
自定义角色:
create role 角色名 not identified;--不验证
create role 角色名 identified by 密码--数据库验证
角色授权：和给用户授权没有太多的区别，系统权限的unlimited tablespace 和对象权限的with grant option选项是不能授予角色的。
grant create session to 角色名 with admin option
grant select on scott.emp to 角色名
把角色给用户：grant 角色名 to 用户名 with admin option
删除角色：（级联删除）
drop role 角色名；
查询角色信息
select * from dba_roles;显示所有角色
select privilege ,admin_option from role_sys_privs where role='角色名'；--显示角色具有的系统权限
select granted_role,default_role from dba_role_privs where grantee="用户名"；--显示用户具有的角色，及默认角色
表的管理：
字段类型
字符型
char 最大2000，定长，char(10),相当于 ‘小韩 ’，后面用空格补全，效率快。
varchar2(10) ，变长，最大4000字符。节省空间
clob 最大4G
数字型
number 可表整数，也可表小数 number(5,2) 范围-999。99 999.99 五位，2位是小数。
日期
date 年月日和时分秒
timestamp
图片
blob 图片/声音 4G
表字段管理
清表drop table users;
建表
create table student(
xh number(4),
xm varchar2(20),
sex char(2),
birthday date,
sal number(7,2)
);
create table classes(
classId number(2),
classname varchar(10)
);
添加一个字段
alter table student add(classid number(2));
修改字段的长度
alter table student modify (xm varchar2(30));
修改字段的类型或时名字
alter table student modify (xm char(30));
删除一个字段
alter table student drop column sal;
修改表的名字
rename student to stu;
删除表
drop table student ;
表记录管理
添加数据
insert into student values(1,'小赵','男','3-9月-11','999.99',2);
改日期的默认格式
alter session set nls-date-format='yyyy-mm-dd'
查为有字段为空的记录
select * from student where birthday is null
is not null
改
update student set sex='' where xh=''
删
delete from student ;删数据；
先savepoint aa 设置保存点。 删后 rollback to aa可以恢复
drop from student ;删表结构和数据。
truncate table student ;与delete相似，但无法恢复，速度快。不写日志。
查询：
取消重复 distinct
set timing on;显示执行时间
重复添加
insert into users (userid,username) select * from user;
排序
order by desc/asc asc默认从低到高 desc从高到低
分组
group by having
自连接：在同一张表的连接查询
select
from emp worker,emp boss
where worker.mgr=boss.empno
子查询：
添加：行迁移 将emp表内容复制到zzz
insert into zzz (myid ,myname,mydept) select empno,ename,deptno from emp
更新:scott的岗位，工资，补助与smith一样
update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT'
事物：
例如，银行账户转账，一边减，一边加，是同一件事，同时执行。
只读事物:如某个时间点之前的统计
set transaction read only
内置函数
字符函数
nvl(aa,''),如果aa为null，则用返回后面的值。
lower();将字符串转为小写
upper();为大写
length();字符串长度
substr(char,m,n);取子串m从开始 n取几个
数学函数
round(n,[m])四舍五入 m 保留小数位位数
trunc(n,[m]) 截取数字 m小数位，无m截取整数
mod（m,n） 取模m/n
floor(n) 小于或等于n的最大整数
ceil(n) 大于或等于n的最小整数
日期函数
sysdate
add_months(d,n) d+n个月
last_day(d) 指定日期所在月份的最后一天
转换函数
to_char('','l9999.99') l表本地货币符号 c国际
to_date
系统函数
sys_context('userenv','db_name')
db_name 库名
language 语言
session_user 当前会话客户所对应的库用户名
nls_date_format 日期格式
current_schema 方案名
数据库管理
show parameter 显示初始化参数
逻辑备份与恢复
导出：3种 exp命令
导出表
导出自个表 exp tables=(表名1，表2) file=d:\e1.dmp
direct=y 直接导出，数据量大时用
导出其它方案的表:需要dba权限或exp_full_database权限
system就可以导出scott的表 exp tables=(scott.emp) file=d:\e2.dmp
导出表结构
exp tables=(emp) file=d:\e3.dop rows=n
导出方案
导出自己的方案
exp owner=scott file=d:\scott.dmp
导出其它方案 需要dba权限或exp_full_database权限
exp owner=(system,scott) 多个file=d:\system.dmp
导出数据库 需dba权限或exp_full_database
exp full=y inctype=complete file=d:\x.dmp
导入
导入自己表
imp tables=(emp) file=d:\1.dmp
导入其它用户
imp tables=(emp) file=d:\2.dmp touser=scott
导入表结构
imp tables=(emp) file=d:\3.dmp
rows=n
导入数据，表已经存在，只倒数据
imp tables=(emp) file=d:\4.dmp ignore=y
导入方案
导入自己 imp file=d:\
导入其它方案 要求有dba权限
imp userid=system/manager file=d:\a.dmp fromuser=system touser=scott
导入数据库
imp userid=system/manager full=y file=
数据字典
查当前用户所拥有的所有表user_tables
select table_name from user_tables;
当前用户可以访问的所有表all_tables
select table_name from all_tables;
所有方案拥有的数据库表，需dba角色或select any table系统权限 dba_tables
select table_name from dba_tables;
dba_users显示所有数据库用户的详细信息
select username from dba_users
数据字典视图dba_sys_privs，可以显示用户所具有的系统权限
dba_tab_privs 用户具有的对象权限
dba_col_privs 用户具有的列权限
dba_role_privs用户具有的角色
查询oracle多少种角色select * from dba_roles;
一个角色有多少个权限（系统权限，对象权限）
系统权限
select * from dba_sys_privs where grantee='CONNECT'
select * from role_sys_privs wher role='DBA'
对象权限
select * from dba_tab_privs where grantee='CONNECT'
查oracle中所有的系统权限
select * from system_privilege_map order by name;
查所有对象权限
select distinct privilege from dba_tab_privs;
某个用户具有的角色
select * from dba_role_privs where grantee='用户名'
显示当前用户可以访问的所有数据字典视图
select * from dict where comments like '%grant%';
显示当前数据库的全称
select * from global_name;
表空间作用
控制数据库占用的磁盘空间
dba可以将不同数据类型部署到不同的位置，有利于提高i/o性能，同时利于备份和恢复等管理操作
创建表空间create tablespace 名字 datafile 'd:\data01.dbf'
size 20m uniform size 128k
改变表空间的状态
使表空间脱机
alter tablespace 表空间名 offline;
使表空间联机
alter tablespace 表空间名 online;
只读表空间
alter tablespace 表空间名read only/write
知道表空间名，显示该表空间包含的所有表
select * from all_tables where tablespace_name='表空间名'
知道表名，查看该表属于哪个表空间
select tablespace_name ,table_name from user_tables where table_name='emp'
删除表空间,同时删除该空间的所有数据库对象，datafilse表示将数据库文件也删除
drop tablespace '表空间' including contents and datafiles;
扩展表空间
1.增加数据文件
alter tablespace 表空间名 add datafile 'd:\test.dbf' size 20m
2 增加数据文件的大小
alter tablespace 表空间名 'd:test.dbf' resize 20m
3 设置文件的自动增长
alter tablespace 表空间名 'd:\test.dbf' autoextend on next 10m maxsize 500m
移动数据文件
1 确定数据文件所在的表空间
select tablespace_name from dba_data_files where file_name='d:\test.dbf';
2 使表空间脱机 确保数据文件的一致性，将表空间转变为offline状态
alter tablespace 表空间 offline;
3 使用命令移动数据文件到指定的目标位置
host move d:\test.dbf c:\test.dbf
4 物理上移动了数据文件后，还必须执行alter tablespace命令对数据库文件进行逻辑修改
alter tablespace 表空间名 rename datafile 'd:\test.dbf' to 'c:\test.dbf'
5 使表空间联机
alter tablespace 表空间 online;
数据的完整性
可以使用约束，触发器，应用程序（过程，函数）三种方法来实现。
约束
用于确保数据库数据满足特定的商业规则
包括：not null, unique唯一,可以为null,primary key不能重复不能为null,foreign key,check检查
例子：
create table goods (--商品表
goodsId char(8) primary key,--主键，定长字符8位
goodsName varchar2(30),
unitprice number(10,2) check (unitprice>0),--单价大于0
category varchar2(30),
provider varchar2(30)
);
create table customer(--客户表
customerId char(8) primary key,
name varchar2（30） not null,--不为空
address varchar2(50),
email varchar2(50) unique,--不能重复
sex char(2) default '男' check (sex in('男','女')),--默认是男
cardId char(18)
);
create table purchase(
customerId char(8) references customer(customerId),
goodsId char(8) references goods(goodsId),
nums number(5) check(nums between 1 and 30)--数量必须是1到30之间
)
添加约束 加notnull用modify 加其它约束用add
alter table goods modify goodsName not null;
alter table customer add constraint 名字 unique(cardid);--省份证也不重复
alter table customer add constraint addresscheck(约束名) check (address in ('东城','海淀','朝阳','西城'))
删除约束
alter table 表名 drop constraint 约束名称；
删除主键约束时，删除主表的主键是必须带上cascade
alter table 表名 drop primary key cascade;
显示约束信息
通过查询数据字典视图user_constranints
select constraint_name,constraint_type,status,validated from
user_constraints where table_name='表名'；
显示约束列
select column_name,position from user_cons_columns where constraint_name='约束名';
列级定义：定义列的同时定义约束
create table department(
dept_id number(2) constraint pk_deparment primary key,
name varchar2(2),
loc varchar2(12)
);
表级定义：定义了所有列后，在定义约束
create table employee(
emp_id number(4),
name varchar2(15),
dept_id number(2),
constraint pk_employee primary key(emp_id),
constraint fk_department foreign key(dept_id) references department(dept_id);
)
索引
单列索引
create index 索引名 on 表名（列名）；
复合索引
create index 索引名 on emp(ename,job);
使用原则：
1 在大表上建立索引才有意义
2 在where子句或是连接条件上经常引用的列上建立索引
3 索引的层次不要超过4层
索引缺点：
1 建立索引，系统要占用大约为表的1.2倍的硬盘盒内存空间来保存索引
2 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性；
查询索引信息
显示表的所有索引
select index_name,index_type from user_indexs where table_name='表名';
显示索引列
select table_name,column_name from user_ind_columns where idex_name='列名'
存储过程
pl/sql procedural language /sql 过程化语言
pl/sql编程技术：存储过程，函数，触发器……
创建:
create or replace procedure 名 is
begin
insert into mytest values('zhao','123');
end;
显示错误：show error
调用过程：1.exec 过程名（参数1，参数2）
2. call 过程名（参数1，参数2）
pl/sql块结构:
编写规范：
注释：--单行注释
/**/多行注释
标识符命名规范
变量v_为前缀
常量c_为前缀
游标_cursor为后缀
例外e_为前缀
declear定义部分，可选
begin 执行部分
exception 例外处理部分
end;
实例1
set serveroutput on/off 打开/关闭输出选项
begin
dbms_output.put_line('hello');
end;
实例2
declare
v_ename varchar2(5);--定义字符串变量
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;--把查出的ename 赋给v_name,&表示要接受从控制台输入的变量
dbms_output.put_line('雇员名：'||v_ename||'工资：'||v_sal);
exception--异常处理
when no_data_found then --预定义的例外异常
dbms_output.put_line('输入编号有误！');
end;
过程：
例1：可以输入雇员名，新工资，可修改雇员的工资
create procedure pro001 (name varchar2,newsal number) is
begin--执行部分,根据用户名修改工资
update emp set sal=newsal where ename=name;
end;
调用过程：exec pro001('SCOTT','5000');
java调用过程：
Connection ct=DriverManager.getConnection('url','user','password');
CallableStatement cs=ct.prepareCall("{call pro001(?,?)}");
cs.setString (1,"SCOTT");
cs.setInt(2,100);
函数
输入雇员的姓名，返回该雇员的年薪
create function fun001(name varchar) return number is yearSal number(7,2);
begin --执行
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=name;
return yearSal;
end;
显示错误：show error;
调用函数：var aaa number;--先定义一个变量
call fun001('SCOTT') into:aaa;
java调用：select fun001('scott') from dual;
用rs.getInt(1)得到返回值。
包：用于在逻辑上组合过程和函数，它由包规范和包体两部分组成
create package pack001 is--创建一个包，声明该包有一个过程，一个函数
procedure pro001(name varchar2,newsal number);
function fun001(name varchar2) return number;
end;
包体：包的规范只包含了过程和函数的声明，没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。
create or replace package body pack001 is--给包实001现包体
procedure pro001(name varchar2,newsal number) is
begin
update emp set sal=newsal where ename=name;
end;
function fun001(name varchar2) return number is
yearsal number;
begin
select sal*12+nvl(comm,0) into yearsal from emp where ename=name;
return yearsal;
end;
end;
调用包：exec pack001.pro001('scott','100');
触发器
隐含执行的存储过程。定义触发器是，必须要指定触发的事件和触发的操作，常用的触发事件包括：insert,update,delete
变量：
标量类型scalar
定义一个变长字符串 v_ename varchar2(10);
定义一个小数 v_sal number(6,2);
定义一个小数并给一个初始值为5.4 ：=是pl/sql的赋值号
v_sal2 number(6,2):=5.4
定义一个日期类型的数据 v_hiredate date;
定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false;
复合类型composite
参照类型reference
lob (large object)
例子:输入员工号，显示雇员名，工资，个人所得税，税率为0.03,。
declare
c_tax_rate number(3,2):=0.03;--常量
v_ename varchar2(5);--用户名，变量
v_sal number(7,2);
v_tax_sa number(7,2);--所得税
begin --执行
select ename,sal into v_ename,v_sal from emp where empno=&no;
--计算所得税
v_tax_sal:=v_sal*c_tax_rate;
--输出
dbms_output.put_line(''||v_name||''||v_sal||''||v_tax_sal);
end;
%type类型，属标量
v_ename emp.ename%type表示v_eanme 的类型与emp中ename大小一致
复合变量：存放多个值
pl/sql记录，类似于高级语言的类，结构体
--定义一个pl/sql记录类型 em_record_type
declare
type em_record_type is record(name emp.ename%type,
salary emp.sal%type,title emp.job%type);
--定义一个zk_record 变量，这个变量的类型em_record_type
zk_record em_record_type;
begin
select ename,sal,job into zk_record from emp where empno=7788;
dbms_output.put_line(zk_record.name);
end;
pl/sql表:相当于高级语言中的数组，下标可以为负数，
declare--定义一个表类型，zk_table_type，用于存放emp.ename%type数组，index by binary_integer表示下标是整数
type zk_table_type is table of emp.ename%type index by binary_integer;
--定义了一个变量zk_table，类型是zk_table_type
zk_table zk_table_type；
begin
select ename into zk_table(0) from emp where empno=7788;--把ename放到下标为0的位置
dbms_output.put_line(zk_table(0));
end;
参照变量
如果把上面select语句的where之后去掉，则用参照变量
游标变量
例子：
输入部门号，显示该部门的所有员工名和工资
declare
--定义游标类型
type zk_emp_cursor is ref cursor;
--再定义一个游标变量test_cursor
test_cursor zk_emp_cursor;
--定义变量
v_name emp.ename%type;
v_sal emp.sal%type;
begin
--把test_cursor和一个select 结和
open test_cursor for select ename ,sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断工资高低，决定是否更新
if
--退出条件，判断是否test_cursor为空
exit when test_cursor%notfound;
dbms_outpub.put_line(''||v_ename||''||v_sal);
end loop;
--关闭游标
close test_cursor;
end;
pl/sql的进阶,控制语句
条件分支语句
if--then,
if--then--else,二重条件分支
if--then--elsif--else多重条件分支
例：写一个过程，可以输入一个雇员名，该员工工资低于2000就增加10%
create or replace procedure zk_pro01(zkName varchar2) is
v_sal emp.sal%type;
begin
select sal into v_sal form emp where ename=zkName;
if v_sal<2000 then
update emp set sal=sal+sal*10% where ename=zkName;
end;/
调用： exec zk_pro01('SCOTT');
例子：如果该员工的补助不是0，就加100，如果补助为0，就加200；
create or replace procedure zk_pro01(zkName varchar2) is
v_comm emp.comm%type;
begin
select comm into v_comm form emp where ename=zkName;
if v_comm<>0 then
update emp set comm=comm+100 where ename=zkName;
else
update emp set comm=comm+200 where ename=zkName;
end if;
end;/
列子：
输入员工号，如果该员工是president 就工资加1000，如果是manager就加500，其它加200
create or replace procedure zk_pro01(zkName number) is
v_job emp.job%type;
begin
select job into v_job form emp where empno=zkName;
if v_job='president' then
update emp set sal=sal+1000 where empno=zkName;
elsif v_job='manager' then
update emp set sal=sal+500 where empno=zkName;
else
update emp set sal=sal+200 where empno=zkName;
end if;
end;/
循环语句 -loop--end loop 至少执行一次
例子：写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加
create or replace procedure zk_pro02(zkName varchar2) is
v_num number:=1;
begin
loop
insert into users values(v_num,zName);
--判断是否要退出循环
exit when v_num=10;
--自增
v_num:=v_num+1;
end loop;
end;
while循环:
例子：写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从11开始增加
create or replace procedure zk_pro02(zkName varchar2) is
v_num number:=11;
begin
while v_num<=20 loop
insert into users values(v_num,zkName);
v_num:=v_num+1;
end loop;
end;
for循环
begin
for i in reverse 1..10 loop
insert into users values(i,'zzz');
end loop;
end;
顺序控制语句，goto ,null
多层循环中，直接跳出最外边，可用goto,一般不用
goto lable lable是已经定义好的标号名
列子
declare
i int:=1; --int表示整数
begin
loop
dbms_output.put_line(''||i);
if i=10 then
goto end_loop;
end if;
i:=i+1;
end loop;
<<end_loop>>--标号
dbms_output.put_line('循环结束');
end;/
null 语句不会执行任何操作，会直接将控制传递到下一条语句
分页过程：
写一个过程，可以想book中添加数据，无返回值
--book表
create table book(bookid number,bookName varchar2(50),publishHouse varchar2(50));
--编写过程
create or replace procedure zk_pro03(zkBookId in number,zkBookName in varchar2,zkpub in varchar2)--in 表示输入变量，不写默认有in。out表示一个输出参数
is
begin
inset into book values(zkBookId,zkBookName,zkpub);
end;
例子：有返回值的存储过程
可输入员工编号，返回该员工的姓名
create or repalce procedure zk_pro04(no in number,name out varchar2) is
begin
select ename into name from where empno=no;
end;
java调用CallableStatement cs =ct.prepareCall("{call zk_pro(?,?)}");
cs.setInt(1,7788);
cs.registerOutParameter(2,oracle.jdbc.OrcleType.VARCHAR);
cs.execute();
String name=cs.getString(2);//取出返回值
System.out.println(""+name);
返回多个字段
create or repalce procedure zk_pro04(no in number,name out varchar2，zksal out number,zkjob out varchar2) is
begin
select ename,sal,job into name,zksal,zkjob from where empno=no;
end;
返回结果集的过程
--先创建一个包,定义了一个游标类型，test_cursor
create or replace package pack001 as typ test_cursor is ref cursor;
end pack001;
--建存储过程
create or replace procedure pro001(zkdeptno in number,p_cursor out pack001.test_cursor) is
begin
open p_cursor for select * from emp where deptno=zkdetpnao;
end;
--在java中调用
java调用CallableStatement cs =ct.prepareCall("{call pro001(?,?)}");
cs.setInt(1,7788);
cs.registerOutParameter(2,oracle.jdbc.OrcleType.CURSOR);
cs.execute();
ResultSet rs=(ResultSet)cs.getObject(2);
while(rs.next){
System.out.println(""+rs.getInt(1)+""+rs.getString(2));
}
分页：
select * from--分页语句
(select t1.* rownum rn from (select * from emp) t1 where rownum<=10)
where rn>=6;
--先建一个包,定义游标类型
create or replace package testpackage as type test_cursor is ref cursor;
end testpackage;
create or replace procedure fenye(tablename in varchar2,pagesize in number,--一页显示大小
pagenow in number,--当前页数
rowscount out number,--总记录数
pagescount out number，--总页数
p_cursor out testpackage.test_cursor--返回记录集) is
--定义sql语句 字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pagenow-1)*pagesize+1;
v_end number:=pagenow*pagesize;
begin
v_sql:='select * from
(select t1.* rownum rn from (select * from '||tablename||' order by sal) t1 where rownum<='||v_end||')
where rn>='||v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--计算总页数，总记录数
v_sql:='select count(*) from '||tablename;
--执行sql，并把返回值赋给rowscount
execute immediate v_sql into rowscount;
--计算pagescount
if mod(rowscount,pagesize)=0 then
pagescount:=rowscount/pagesize;
else
pagescount:=rowscount/pagesize+1;
end if;
--关闭游标
close p_cursor;
end;
原始记录地址： http://wenzhang.baidu.com/page/view?key=98e75c20ad5b0e14-1426452139
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 2459501893@qq.com
