本文共 14268 字，大约阅读时间需要 47 分钟。
oracle安装完之后有3个默认用户： sys 超级管理员 权限最高 角色为dba 默认密码：chang_on_install system 系统管理员 权限很高 角色dbaoper 默认密码：manager (没有create databse 权限) scott 普通用户 默认密码：tiger ALTER USER scott ACCOUNT UNLOCK; 为用户解锁 ********************************************************************************************************************************************** oracle 对用户的操作 start d:\a.sql 或者@ d:\a.sql 运行脚本的命令 edit d:\a.sql 编辑脚本的命令 spool d:\a.sql ; spool off;将sqlplus 屏幕上得命令存储到a.sql 里面。 conn sys/admin as sysdba; 让sys以dba的角色登陆 create user admin identified by admin ;创建admin用户,密码为admin; password admin; 修改admin的密码 drop user admin; 删除用户，无法删除自己。当用户已经创建了表 需要带一个参数cascade;(所创建的表也删除) oracle默认的预定义有connect dba resource(可以再任何表空间建表) 自定义角色自己创建 grant dba to admin;把dba的角色权限给admin； grant select on help to admin ; 把system的 help表的查询权限给admin；(select update all ....) select * from system.help ;在admin下查询system的help表； set linesize 120; 设置一行的字符数量 revoke select on help from admin;在system下收回admin 对help表的查询的权限。 grant select on emp to admin with grant option;在scott下授权给admin emp表的查询权限 并且让admin 把这个权限传递下去、 grant select on scott.emp to sa; 在admin下 把scott的emp表的查询权限给sa; revoke select on emp from admin;在scott下收回admin对emp表的查询权限 同时admin所传递下去的权限也都默认收回了。 create profile rule limit failed_login_attempts 3 password_lock_time 2;创建名字为rule的规则 用户登录时最多输入的3次 登录错误3次后锁定2天 alter user admin profile rule;把rule规则赋给admin用户。 alter user admin account unlock; 解除admin的账号锁定 drop profile rule; 删除名字为rule的规则。 drop profile rule cascade ; 当rule规则指定给某用户时，删除名字为rule的规则需要加上 cadcade。 create profile rule_pass limit password_life_time 3 password_grace_time 2;rule_pass规则 用户密码使用3天后提示更改密码 宽限期为2天。 create profile rule_pas_his limit password_life_time 3 password_grace_time 2 password_reuse_life 3 ;更改密码后 历史密码3天后才能再次使用 ********************************************************************************************************************************************** oracle 对数据库表的操作 alter table user add(uuid number(10)); 对user表添加以一个user 的字段 alter table user modify(uuid number(5));把user表的uuid字段 长度改为5 alter table user drop column uuid;删除user表的uuid字段 rename user to use; 把user表改名字为use alter session set nls_date_format='yyyy-mm-dd';把日期型的格式改为年月日，默认是日月年 dd-mm-yyyy set timing on ; 打开查询时间。 insert into user(userid,username.password) select * from user;把当前表的数据复制到本身。 select distinct username from user; 消除重复行查询username select (sal+nvl(comm,0))*13 ,username from emp; nvl判断该字段是否为空。为空用0替代 不为空用本身的值。 select ename empno from emp where empno in(9875,4563,1546);查询emp中empno为9875 4563 1546的记录。 select enamel mgr from emp where mgr is null;查询mgr 为空的字段 select ename sal from emp where sal=(select max(sal) from emp); 查询出工资最高的员工 select min(sal) "最低工资",avg(sal) "平均工资", count(*) "总人数" from emp; select avg(sal) "平均工资",max(sal) "最高工资" ,deptno as "部门编号"from emp group by deptno; 按部门分组查询各部门的最高工资和平均工资 select avg(sal) "平均工资",max(sal) "最高工资" ,deptno as "部门编号" ,job "职位" from emp group by deptno, jOB ORDER BY DEPTNO；查询各部门各职位的工资 并按部门升序排列 SELECT avg(sal) "平均工资",deptno as "部门编号" FROM EMP GROUP BY deptno HAVING avg(sal)<2000; 按照部门编号分组查询平均工资《2000的部门 select A1.ename,A1.sal,A2.DNAME FROM emp a1 ,DEPT a2 where A1.DEPTNO=A2.DEPTNO; 利用emp和dept的主外键关系进行关联查询； SELECT EMP.ENAME "名字", EMP.SAL "工资", SALGRADE.GRADE "工资级别" from EMP,SALGRADE WHERE emp.SAL BETWEEN SALGRADE.LOSAL AND SALGRADE.HISAL; 利用 between .. and .. 查询 工资所在范围属于哪个级别。 SELECT a1.ename,A2.ENAME FROM EMP a1,EMP a2 WHERE A1.MGR=A2.EMPNO AND A1.ENAME='FORD'; 从EMP表查员工FORD的上司属于自连接 SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO from EMP WHERE ENAME='SMITH'); 单行子查询 作为条件的查询语句返回值只有一个 SELECT * FROM emp where JOB IN (SELECT distnct(消除重复数据) JOB from EMP where DEPTNO='10'); 多行子查询 作为条件的查询语句返回值有多个 SELECT * from EMP where SAL> ALL (SELECT SAL FROM EMP WHERE DEPTNO=20); 多条件查询 可以用关键词All 所有的 ，可以改为 SELECT * from EMP where SAL>(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=20); 直接和最大值比较 性能更好 SELECT * FROM EMP WHERE SAL> ANY(SELECT SAL FROM EMP WHERE DEPTNO=20); 多条件查询 可用关键词 any 任意一个 ，可改为 SELECT * FROM EMP WHERE SAL> ANY(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20);直接和最小值比较 SELECT * from EMP where DEPTNO=(SELECT DEPTNO FROM EMP where ENAME='SMITH') and JOB=(SELECT "JOB" FROM EMP where ENAME='SMITH'); 和SMITH一个部门一个岗位的员工信息 可改为 SELECT * FROM EMP where (DEPTNO,JOB)=(SELECT DEPTNO,JOB FROM EMP WHERE ENAME='SMITH'); CREATE TABLE mytable as SELECT* from EMP; 复制emp表名字为mytable; 合并查询 关键字 ：union union all intersect minus SELECT * from mytable WHERE sal>2000 UNION SELECT * from EMP WHERE DEPTNO=20; union 相同的记录不在显示 SELECT * from mytable WHERE sal>2000 UNION ALL SELECT * from EMP WHERE DEPTNO=20; union 相同的记录也显示 合集 SELECT * from mytable WHERE sal>2000 INTERSECT SELECT * from EMP WHERE DEPTNO=20; intersect 只显示两个查询结果相同的部分 交集 SELECT * from mytable WHERE sal>2000 MINUS SELECT * from EMP WHERE DEPTNO=20; minus 查询差集 前面的减去后面的 显示余下的 如果没有就为空 INSERT into EMP VALUES (7966,'ANGLE','MANAGER',7839,TO_DATE('1989-11-1', 'yyyy-mm-dd'),900,10,20); 日期型数据 插入函数 TO_date(str,formate) INSERT INTO mytest SELECT empno, ename,SAL from EMP WHERE DEPTNO=10;多条数据的添加； update mytest SET(ename,sal) =(SELECT ename,sal from EMP WHERE ename='SMITH') WHERE userid=7782; 多条数据的修改 ************************************************************************************************************************************************** oracle分页 SELECT * FROM(SELECT A1.*,ROWNUM rn from (SELECT * FROM EMP) a1 WHERE ROWNUM<=10) where rn>5; ************************************************************************************************************************************************** 事务的操作 dml事务（增删改） savepoint a1; 创建a1保存点 rollback a1; 返回到a1保存点 rollback; 返回到最初状态 commit; 事务提交后或者退出后，保存点都不存在。 只读事务（查） set stransaction read only; 设置只读事务，其他用户有对数据库进行dml操作时 是看不到的。 *********************************************************************************************************************************************** oracle的函数 lower:字符串换为小写 upper：字符串换为大写 length：字符串长度 substr：截取某段字符串 SELECT "LOWER"(ename) from EMP;小写显示姓名 SELECT "UPPER"(ename) from EMP;大写显示姓名 SELECT * from EMP WHERE "LENGTH"(ENAME)=5;显示姓名长度为5的员工 SELECT "SUBSTR"(ENAME, 1, 3) from EMP; 显示姓名的前3个字符 SELECT "UPPER"(SUBSTR(ENAME, 1, 1))||lower("SUBSTR"(ENAME, 2)) from EMP; 首字母大写 SELECT lower(SUBSTR(ENAME, 1, 1))||UPPER("SUBSTR"(ENAME, 2)) from EMP; 首字母小写 SELECT "REPLACE"(ename, 'A', 'XXX') FROM emp ;查询出的姓名中含有A的用XXX代替 SELECT "INSTR"(ename, 'A') from EMP; 查询字符A在字符串中的位置 SELECT "ROUND"(sal, 1) ,ename from EMP ; 四舍五入 取一位小数 SELECT "TRUNC"(sal, 1),ename from emp; 直接截取 取一位小数， SELECT "FLOOR"(sal),ename from emp;返回小于或等于sal的整数， SELECT "CEIL"(sal),ename from emp; 返回大于或等于sal的整数， SELECT "MOD"(sal, 1) FROM emp; sal和1取模 SYSDATE ：返回系统时间 add_mouths(d,n);在d的基础上加上n个月 last_day(d):返回指定月份的最后一天 SELECT * FROM EMP WHERE SYSDATE>"ADD_MONTHS"(HIREDATE, 350); 查找350个月以前入职的员工 SELECT ename ,TO_CHAR(hiredate,'yyyy-mm-dd hh24:mi:ss') FROM emp; 显示日期的时分秒； SELECT ename,hiredate from emp where "TO_CHAR"(hiredate,'yyyy')='1980';显示1980年入职的员工 SELECT ename,hiredate from emp where "TO_CHAR"(hiredate,'mm')='12'; 显示12月份入职的员工 SELECT sys_context('userenv','DB_name') from dual;返回数据库名 SELECT sys_context('userenv','language') from dual;返回当前语言 SELECT sys_context('userenv','session_user') from dual;返回当前用户 SELECT sys_context('userenv','current_schema') from dual;返回方案名一般和用户名一致 SELECT sys_context('userenv','host') from dual;返回主机名 ******************************************************************** 数据库的导出和备份 exp和import 在cmd进入oracle安装目录 运行exp命令 导出 exp userid=scott/admin@orcl tables=(emp，dept) file=d:\emp.bmp;用scott账号在orcl数据库里 导出emp,dept表 导到 d盘下面 exp userid=system/admin@orcl tables=(scott.emp，scott.dept) file=d:\emp.bmp;用system导出其他scott用户的表 exp userid=system/admin@orcl tables=(scott.emp，scott.dept) file=d:\emp.bmp rows=n;用system导出scott用户的表结构 exp userid=scott/admin@orcl tables=(emp，dept) file=d:\emp.bmp direct=y;快速导出 导出速度快 要求客户端字符集和数据库的字符集要一致 exp scott/admin@orcl owner=(scott) file=d:\scott.bmp; 导出方案 即scott用户的所有数据对象 exp userid=system/admin@orcl owner=(scott,system) file=d:\emp.bmp;用system导出scott的方案 用户导出别人的对象时要有exp_full_database权限 exp system/admin@orcl full=y inctype=complete file=d:\orcl.dmp; 导出orcl数据库 imp导入 imp userid=scott/admin@orcl tables=(emp，dept) file=d:\emp.bmp; 用法和导出一样 imp userid=system/admin@orcl file=d:\emp.bmp; 导入自己方案 imp userid=system/admin@orcl file=d:\system.bmp fromuser=system touser=scott; 导入别人的方案 imp system/admin full=y file=d:\orcl.dmp; 导出orcl数据库 ******************************************************************** 数据库字典视图 user_tables :当前用户所拥有的所有表 all_tables：当前用户可以访问的所有表 dba_tables： 只能是dba角色查询所有方案的所有表 select table_name from user_tables;select table_name from all_tables;select table_name from dba_tables; dba_users:数据库用户的详细信息 select username ,password from dba_users; dba_sys_privs:用户的系统权限视图 select * from dba_sys_privs; dba_tab_privs:用户的对象权限视图 select* from dba_tab_privs where owner='CONNECT'; dba_role_privs:用户具有的角色 selec * from dba_role_privs where grantee='SCOTT'; oracle中邮多少角色 select * from dba_roles; oracle中所有的系统权限 select * from system_privilege_map order by name; oracle中所有的对象权限 select distinct privilege from dba_tab_privs; select role from dba_roles; select * from dba_sys_privs where grantee ='SYSTEM' 有多少系统权限 select * from dba_tab_privs where grantee ='SYSTEM'; 有多少对象权限 select * from global_name; 当前用户使用的数据库 ********************************************************************************************************************************************* 约束关键字 not null：非空 unique:唯一 可为空 primary key:主键 foreign key:外键 check ：强制满足某条件 create TABLE goods( --商品表 goodsId char(8) primary key, --主键 goodsName VARCHAR2(30), untiprice number(10,2) check (untiprice>0), --约束价格大于0 categery VARCHAR2(8), provider VARCHAR2(30) ) CREATE TABLE customer( --客户表 customerId CHAR(8) primary key, cname VARCHAR2(20) not NULL, adress VARCHAR2(30), email VARCHAR2(20) 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(10) CHECK(nums between 1 and 30) ) --购物表 --添加约束 alter TABLE goods MODIFY goodsName not null; alter TABLE customer add constraint bbb UNIQUE(cardId);或者 alter TABLE customer modify cardId UNIQUE; alter table customer add constraint aaa check(adress in ('上海','南京','北京','武汉')); --删除约束 alter TABLE customer drop constraint aaa;--当表存在主从关系时 可以用 cascade 强制删除 alter TABLE customer drop constraint bbb;或者 ALTER TABLE customer MODIFY UNIQUE (cardId) DISABLE;--删除唯一约束 数据字典中有user_constraints视图 可查询所有的约束信息 表级定义 和列级定义 表级定义： create TABLE goods( --商品表 goodsId char(8), --主键 goodsName VARCHAR2(30), untiprice number(10,2) , --约束价格大于0 categery VARCHAR2(8), provider VARCHAR2(30), customerId CHAR(8) , constraint aaa_key primary key(goodsId), constraint bbb_check check(untiprice>0), constraint ccc_foreign foreign key (customerId) references customer(customerId) ) ----------------------------索引-- create INDEX nameindex on customer(cname);--单列索引 create INDEX nameindex on customer(cname,customerId);--符合索引 通过视图dba_indexs和user_indexs可以查询表的所有的索引 --------------------------角色-- connect resource dba create session :连接数据库 dba 可以自定义角色 create role 名字 not identified；角色不设置密码 就是修改角色时不用输入密码 create role myrole not identified; conn system/admin; grant create session to myrole; conn scott/admin; grant select,updata,delete on emp to myrole; SQL> create user testrole identified by admin; 用户已创建。 SQL> grant myrole to testrole; 授权成功。 SQL> conn testrole/admin; 已连接。 SQL> select * from scott.emp; dba 删除角色 或者有（drop anyrole）权限 drop role myrole; ************************************* 存储过程 ①创建 CREATE OR REPLACE PROCEDURE test_pro IS BEGIN INSERT into emp values (9782,'李四','MANAGER',7698,TO_CHAR('1988-01-01','yyyy-mm-dd'),4567.4,10,20); END; 出现错误时，show error; 显示错误信息。 ②调用 exec test_pro; call test_pro; 规范 --注释单行 /*....*/ 多行注释 定义变量 V_开头：V_sal 定义常量 C_开头：C_rate 定义游标 _cursor后缀：emp_cursor 定义例外 e_开头：e_error PL/sql 块分三部分：定义部分 执行部分 例外部分 declear :定义变量 数据类型 begin ：执行部分 exception：处理各种错误 ---例子1 CREATE OR REPLACE PROCEDURE test_pro IS BEGIN INSERT into emp values (9782,'李四','MANAGER',7698,TO_CHAR('1988-01-01','yyyy-mm-dd'),4567.4,10,20); END; ---例子1- BEGIN dbms_output.put_line('hello'); END --块1--- declare V_ename VARCHAR2(10); V_sal NUMBER(10); BEGIN select ename,SAL into V_ename ,V_sal from emp where EMPNO=&aa; dbms_output.put_line('名字是：'||v_ename||'工资是'||V_sal); exception when no_data_found THEN dbms_output.put_line('请输入正确的：'); END; ----------存储过程1 CREATE or replace procedure test_emp(aename VARCHAR2,asal NUMBER) IS BEGIN UPDATE emp set sal=asal where ename=aename; end; 在java中调用存储过程时由Connection得到一个 callableStatment 的prepareCall 方法调用 ---函数 create function qw_hans(hename VARCHAR2) return number is yearsal NUMBER(7,2); BEGIN select sal*12+NVL(COMM, 0)*12 into yearsal from emp where ename=hename; return yearsal; END; ************************************************************************** pl/sql的类型 ----复合类型 定义记录实例 相当于java中的类 set serveroutput on; declare type emp_record_type IS record (ename EMP.ENAME%TYPE ,sal EMP.SAL%TYPE, title EMP.JOB%TYPE); emp_record emp_record_type; BEGIN select ename,sal,job into emp_record from EMP WHERE EMPNO=7788; dbms_output.put_line('姓名：'||emp_record.ename||' 工资：'||emp_record.sal||' 工作：'||emp_record.title); END; ----复合类型 定义表实例 相当于java中的数组（区别pl/sql 中德下标可以为负数）当有多条记录返回时要使用游标变量 declare type emp_table_type is TABLE of EMP.ENAME%TYPE INDEX BY binary_integer; emp_table emp_table_type; BEGIN select ename into emp_table(0) from emp where EMPNO=7788; dbms_output.put_line('姓名：'||emp_table(0)); end; ----游标变量 declare type emp_cursor_type is ref cursor; emp_cursor emp_cursor_type; v_ename EMP.ENAME%TYPE; v_sal EMP.SAL%TYPE; begin open emp_cursor for SELECT ename,sal from emp where deptno=&noo; loop fetch emp_cursor into v_ename,v_sal; exit when emp_cursor%notfound; dbms_output.put_line('姓名：'||v_ename||' 工资：'||v_sal); end loop; end; ----游标变量 loop end loop if end if 创建过程 create or replace procedure empcur(deptn number) IS type emp_cursor_type is ref cursor; emp_cursor emp_cursor_type; v_ename EMP.ENAME%TYPE; v_sal EMP.SAL%TYPE; begin open emp_cursor for SELECT ename,sal from emp where deptno=deptn; loop fetch emp_cursor into v_ename,v_sal; exit when emp_cursor%notfound; if v_sal<200 THEN v_sal:=v_sal+100; end if; dbms_output.put_line('姓名：'||v_ename||' 工资：'||v_sal); end loop; end; ----结构控制语句 if.. then end if loop end loop while.. loop end loop转载地址：http://gfjdi.baihongyu.com/