oracle快速入门笔记.docx

1、oracle快速入门笔记小型数据库：Access、Foxbase中型数据库：MySql、SqlServer、Informix大型数据库：Oracle、DB2、sybase数据库的选择考虑因素主要有以下3点：1. 负载量有多大，用户有多少2. 成本3. 安全性Sys 超级管理员权限高于system在命令行中输入：set ORACLE_SID = DAVE(实例名)SqlplusConn sys/admin as sysdba( as sysdba 不能少否则报错);也可使用conn system/adminSqlplus常用命令：创建用户-授权和我们普通项目中的权限思想基本一致，相当于oracl

2、e数据库已经建好了用户和角色类，我们可以创建新的用户，把角色或权限直接授予用户，我们也可以创建新的角色。自己不能删除自己的用户。新创建的用户连登录数据库的权限都没有需要授权。权限分系统权限（140多个）和对象权限（25个左右）。系统权限是指用户对数据库的相关权限如：建库、建表、建索引、建存储过程、建函数、登录数据库等。对象权限：用户对其他用户的数据对象（表、视图、存储过程、触发器、表空间、序列）操作的权限。角色是权限的集合。角色分：预定义角色和自定义角色。SQL conn system/admin;已连接。SQL grant resource to xiaoming;授权成功。SQL conn

3、 xiaoming/m123;已连接。SQL create table test(userid varchar2(12);表已创建。授权：Grant select on emp to xiaoming;(将emp表的查询权限授予小明)Grant all on emp to zhangsan;（将emp表的所有权限增删改查等授予张三）此时如果小明的表空间中也有emp表如何验证？select * from scott.emp;删除权限：revoke select on emp from xiaoming;对权限的维护：grant select on emp to xiaoming with gra

4、nt option;with admin option VS with grant option不同点：- with admin option 只能在赋予 system privilege 的时使用- with grant option 只能在赋予 object privilege 的时使用- 撤消带有admin option 的system privileges 时，连带的权限将保留例如：1. DBA 给了CREATE TABLE 系统权限给张三 WITH ADMIN OPTION2. 张三 建了若干张表。3. 张三 将建表系统权限给 李四4. 李四 建了若干张表5. DBA 将张三的建表权

5、限收回结果：张三S TABLE 依然存在，但不能创建新的TABLE 了李四S TABLE 依然存在，他还保留着CREATE TABLE 系统权限- 撤消带有grant option 的object privileges 时，连带的权限也将撤消例如：1. JEFF 给了SELECT object privileges 在EMP 上 WITH ADMIN OPTION2. JEFF 给了SELECT 权限在EMP 上 TO EMI3. 后来，撤消JEFF的SELECT 权限结果：EMI 的权限也被撤消了建表添加一个字段：alter table 表名 add (classid number(4);修

6、改多个字段SQL alter table stu modify (stuno number(4),stuname char(2);删除表的某个字段：SQL alter table t_stu drop column classid;Table altered修改表的名字：SQL rename stu to t_stu;Table renamed删除表：drop talbe t_stu;insert into t_stu values(1,10001, 小明, 1999-09-09, 12300, 15)ORA-01861: 文字与格式字符串不匹配SQL alter session set nl

7、s_date_format = yyyy-mm-dd;Session alteredSQL insert into t_stu values(1,10001, 小明, 1999-09-09, 12300, 15);1 row insertedSQL select * from t_stu; ID STUNO STUNAME BIRTHDAY SALARY CLASSID- - - - - - 1 10001 小明 1999-9-9 12300 15SQL设置保存点：savepoint aa;恢复至保存点：rollback to aa;删除表：truncate table t_class;删除表

8、的所有数据，不写日志，速度很快，但无法找回删除的记录。显示查询时间：set timing on;24 rows selecte;Executed in 0.438 seconds对表已有数据的复制：insert into t_class(classid, classname) select * from t_class;select sal*12+nvl(comm,0)*12 “年薪” from emp;模糊查询：% 表示匹配任意多个字符 _表示匹配一个字符使用列的别名排序：order by 1显示每个部门的每种岗位的平均工资和最低工资。其实就是按照多个字段分组group by deptno,

9、 job;多表查询的条件：不能少于表的个数-1 如4张表的关联至少需要3个条件才能避免出现笛卡尔积。多列子查询：多列子查询则是指返回多列数据的子查询语句。当多列子查询返回单行数据时，在where子句中可以使用单行比较符。而返回多行数据时，在where字句中必须使用多行比较符。（in，all，any）select * from emp where (deptno,job)=(select deptno,job from emp where ename = Smith);使用子查询比较多个列的数据时，既可以使用成对比较，也可以使用非成对比较。其中，成对比较要求多个列的数据必须同时匹配，而非成对比较

10、则不要求多个列的数据必须同时匹配。1. 成对比较示例SELECT ename, sal, comm, deptno FROM empWHERE (sal, nvl(comm, -1) IN (SELECT sal, nvl(comm, -1) FROM emp WHERE deptno=30);2. 非成对比较示例SELECT ename, sal, comm, deptno FROM empWHERE sal IN (SELECT sal FROM emp WHERE deptno=30) AND nvl(comm, -1) IN (SELECT nvl(comm, -1) FROM emp

11、 WHERE deptno=30);oracle 表取别名时不要加as 否则报错oracle 分页：共有3种方式：select * from (select e.*, rownum rn from (select * from emp) e where rownum = 最小值2.如果只查询几列，只需要修改最里层视图即可select * from (select e.*, rownum rn from (select empno, ename from emp) e where rownum = 93.如果想在分页中按某些字段排序也只修改最里层视图即可。select * from (selec

12、t e.*, rownum rn from (select empno, ename, sal from emp order by sal desc) e where rownum = 9用查询结果直接创建表create table emp_bak (id, name, sal) as select empno, ename, sal from emp;union: 该操作符用于取得两个结果集的幷集，当使用该操作符时会自动去掉结果集中重复行。过滤掉重复的记录这点我之前不知道。union all： 两个结果集直接相加不去除重复行intersect取交集minus： 从集合A中排除集合B中的元素。

13、A可以比B小，不是数学意义上的减法，而是只要是这个元素在集合B中也存在就过滤掉。事务一旦提交，之前设置的保存点将失效（消失）。只读事务如18点时统计本日销售量，之后新增或删除的记录都不在统计之列1.首先以system/admin账户登录，查看emp_bak表数据并设置开启只读事务。2.以proxy/proxy登录系统插入2条数据proxy看到17条数据4. proxy看到的还是14条数据函数：substr(列名，从第几个字符开始， 截取几个字符)；将每个人名首字母大写，其余字母小写：replace(列名，要替换的字符，替换后的字符)函数：round(col,n) 四舍五入到小数点后几位，如果不

14、写n默认为整数trunc(sal，n)：截取到小数点后第几位，默认为整数，砍掉的位置0;floor(n): 返回=n的最小整数mod(m,n)取模函数 10%3=1add_months函数：时间向前，或向后几个月后的时间入职30年以上的员工：select floor(sysdate - hiredate) 入职天数, trunc(sysdate - hiredate) from emp ;last_day(日期) 该日期月份的最后一天转换函数：to_char(hiredate, yyyy/MM/dd hh24:mi:ss)to_char(sal,$99,999.99)显示工资时前面带$: $2

15、,975.00to_char(sal,L99,999.99)本地货币: ￥1,600.00to_char(sal,C99,999.99)国际货币：CNY2,850.00查询1980年入职的员工select empno, ename, hiredate from emp where to_char(hiredate,yyyy)= 1980 ;*：表连接条件为多个时可以用on(.and.and.)如：每个部门薪水最高的员工以前：select ename, e.sal, e.deptno, t.ms from emp e inner join (select deptno, max(sal) ms

16、from emp group by deptno)t on e.deptno = t.deptno where e.sal = t.ms;select ename, e.sal, e.deptno from emp e inner join (select deptno, max(sal) ms from emp group by deptno) t on (e.deptno = t.deptno and e.sal = t.ms);*查询出每个部门中薪水高于其部门平均薪水的职员信息。SQL select ename,e.sal,t.avg_sal from emp e inner join

17、(select deptno, avg(sal) avg_sal from emp group by deptno) t on (e.deptno = t.deptno and e.sal t.avg_sal);*显示每个部门的名称和平均薪水等级SQL select t.deptno, d.dname, s.grade from (select deptno, avg(sal) avg_sal from emp group by deptno)t 2 inner join dept d on t.deptno = d.deptno 3 inner join salgrade s on (t.a

18、vg_sal between s.losal and s.hisal) 4 order by 1 asc;*查询工资1200的员工，按部门编号分组后平均薪水1500的按倒序排列（不是最初想象的那么简单）。SQL select ename,sal, e.deptno from emp e inner join (select deptno from emp where sal1200 group by deptno having avg(sal) 1500) t on e.deptno = t.deptnowhere e.sal 1200 order by e.deptno asc, sal a

19、sc;*显示每个员工的姓名，工作，和经理人姓名select e1.ename, e1.job, e2.ename mgr from emp e1 left outer join emp e2 on e1.mgr = e2.empno order by e1.empno asc;*求部门平均的薪水等级*不用组函数求最高薪水：select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal e2.sal);(不知道是谁想的)我的：select rownum

20、, e.* from (select * from emp order by sal desc) e where rownum =1;*按薪水排序，查询第4-9高的员工信息select * from (select rownum rn, e.* from (select * from emp order by sal desc) e where rownum =4;*求平均薪水最高的部门编号select t.* from (select deptno, avg(sal) avg_sal from emp group by deptno order by 2 desc) t where rown

21、um = 1;(每个部门平均薪水，排序后取第一个)下面的写法很不常用：部门平均薪水= 最高的平均薪水利用了函数的嵌套select deptno, avg(sal) as max from emp group by deptno having avg(sal) = (select max(avg(sal) from emp group by deptno);*平均薪水最高的部门名称select d.* from dept d inner join ( select deptno, avg(sal) as max from emp group by deptno having avg(sal) =

22、 (select max(avg(sal) from emp group by deptno) t on d.deptno = t.deptno;*平均薪水 的等级 最低的部门名称,select * from dept where deptno in ( select t.deptno from ( select deptno, avg(sal) avg from emp group by deptno )t inner join salgrade g on (t.avg between g.losal and g.hisal) where g.grade = (select min(g.gr

23、ade) from (select deptno, avg(sal) avg from emp group by deptno) t join salgrade g on (t.avg between g.losal and g.hisal);如果还要显示平均薪水、平均薪水等级【我的】select d.dname, t.avg, t.grade from dept d inner join ( select t.deptno, avg, grade from ( select deptno, avg(sal) avg from emp group by deptno )t inner join

24、 salgrade g on (t.avg between g.losal and g.hisal) where grade = (select min(g.grade) from (select deptno, avg(sal) avg from emp group by deptno) t join salgrade g on (t.avg between g.losal and g.hisal) )t on d.deptno = t.deptno;函数：sys_context select sys_context(userenv, db_name) 数据库名, sys_context(u

25、serenv, terminal) 客户端机器名称,sys_context(userenv, language) 语言,sys_context(userenv, session_user) 数据库用户名,sys_context(userenv, current_schema) 方案名from dual;数据库管理员的职责：从下图可以看出:system 不具有sysoper的权限通过对比看出：sysoper比sysdba不具备的权限有：创建数据库、删除数据库，改变字符集、恢复数据库等重要权限。show parameter;显示参数oracle导出表、方案、数据库前提：打开命令行，切换到进入ora

26、cle_homebin目录下(直接在sqlplush命令行中无效)D:oracleproduct10.2.0db_1BIN1.导出某些表:*示例：exp userid=proxy/proxycslgs file=d:db_bak.dmp tables=(emp,dept,salgrade) rows=n direct=y可选补充：只导出表结构不导数据 只需后面加rows=n，当数据量很大时可以增加direct=y直接导出方式来加快速度。 2.导出用户所有的表|导出方案*示例：exp userid=proxy/proxycslgs file=d:expAll.dmpowner=proxy(以pr

27、oxy身份导出该方案的所有表)【以system身份导出所有表】：*示例：exp userid=system/admincslgsfile=d:expSys.dmpowner=(system,proxy)3.导出整个数据库要求该用户必须具备dba或exp_full_database的权限*示例：exp userid=system/admincslgsfull=y 关键是这句话file=d:expAllDb.dmporacle入表、方案、数据库1.导入某些表*示例：imp userid=proxy/proxycslgs file=d:db_bak.dmp tables=(emp,dept,salg

28、rade)2.导入方案*示例：【导入自己的方案】imp userid=proxy/proxycslgs file=d:fangan.dmpfromuser=proxy touser=proxy或者full=y(写一个即可)3.导入数据库*示例：imp userid=proxy/proxy 实例名不用写oracle会自动创建full=y file=d:db.dmp从数据字典中查询所有的账号，密码：SQL select username, password from dba_users;查看proxy具有的角色：select * from dba_role_privs where grantee=

29、 PROXY;创建表空间：create tablespace lobom_tbl datafile D:oracleproduct10.2.0db_1database data01.dbf size 100m uniform size 128k指定区的大小 autoextend on; 创建完成后就可以使用表空间了，在其上创建表create table test_tbl_space (id int, name varchar2(10) tablespace ds_weisy;改为只读后无法插入数据，脱机后无法查询，如何消除只读呢？alter tablespace 空间名 read write如果输入的empno不存在呢？函数：输入雇员姓名返回雇员年薪在pl/sql中如何调用？在java程序中如何调用？语法如下:create or replace function function_name ( argu1 mode1 datatype1, argu2 mode2 datatype2, . ) return datatype is begin end; 执行 var v1 varchar2(100) exec :v1:=function_na包体：类似于java的类，调用其中的函数或过程时