玩转oracle实战教程个人笔记Word文档下载推荐.docx

1、Drop profile lockma cascade;清屏操作：clearDisconnect： 断开与当前数据库的连接Password： 修改密码，如果要修改其他用户密码，需要用sys/system登录Show user： 查看当前连接用户Exit： 断开与数据库连接，同时会退出sql*plusStart和： 运行sql脚本，如： d:a.sql 或者start d:a.sqlEdit： 编辑指定的脚本 如：edit d:Spool：可以将sql*plus屏幕上的内容输出到知道的文件中去，如：sqlspool d:b.sql; 然后所有的操作及结果都会输出到文件，最后输入sqlspoolo

2、ff结束Oracle表的管理Oracle表名和列的命名规则：必须以字母开头长度不能超过30个字符不能使用oracle的保留字只能使用如下字符 A-Z，a-z，0-9，$，#等Oracle支持的数据类型：字符型char：定长，最大2000字符，对于长度变化不大的变量，查询速度很快。如身份证id。varchar2：变长，最大4000字符。节省空间。clob（character large object）：字符串大对象，最大4G数字型number 可以表示整数也可以表示小数number（5,2）表示有5位有效数字，2位小数，-999.99999.99number（5）表示一个5位整数，-999999

3、9999日期型date 包括年月日和时分秒timestamp oracle对date数据类型的扩展图片blob 二进制数据，可以存放图片/声音 4G创建表：DDL数据描述语言，包括create drop alterDML数据操作语言，包括select insert delete updateDCL数据控制语言，包括grant revokecreate table student（ 2 no number（10）, 3 name varchar2（10）, 4 sex char（2）, 5 birthday date, 6 sal number（7,2） 7 ）;添加一个字段：alter tab

4、le student add classid number（2）;修改字段长度：alter table student modify name varchar（30）;修改字段类型或字段名字（不能有数据）：alter table student modify name char（20）;删除一个字段：alter table student drop column sal;修改表的名字：rename student to stud;删除表：drop table student;插入数据：insert into stud values （1,马杰,男16-11月-1988,12）;其中日期的格式，

5、oracle默认的是：dd-mon-yy， 而且月份必须有月字。可以修改oracle的默认值：alter session set nls_date_format=yyyy-mm-dd;然后再插入数据：insert into stud values （2,张三1988-11-1SQL select * from stud; NO NAME SEX BIRTHDAY CLASSID- - - - - 1 马杰 男 1988-11-16 12 2 张三 男 1988-11-1 12修改no字段非空：alter table stud modify no not null;alter table stu

6、d modify name not null;alter table stud modify sex not null;alter table stud modify classid not null;insert into stud （no,name,sex,birthday,classid） values（3,李四,null,10）;查询空的记录：select * from stud where birthday is null; select * from stud where birthday is null; 3 李四 男 10修改多列：update stud set name=李斯

7、, sex=女,birthday=1988-11-05 where no=3 select * from stud where no = 3 李斯 女 1988-11-5 10还原点的作用，在还原点里进行的操作可以rollback回去：savepoint bb; savepoint bb;Savepoint created delete from stud;3 rows deleted NO NAME SEX BIRTHDAY CLASSID SAL- - - - - - rollback to bb;Rollback complete SQL 1 马杰 男 1988-11-16 12 100

8、.00 2 张三 男 1988-11-1 12 200.00 3 李四 男 10 300.00关于多个还原点： savepoint a1;Savepoint created update stud set sal = 110 where no =1;1 row updated savepoint a2; update stud set sal = 220 where no =2; 1 马杰 男 1988-11-16 12 110.00 2 张三 男 1988-11-1 12 220.00 rollback to a1; 1 马杰 男 1988-11-16 12 100.00如果rollback

9、 to a2的话，就只有第二条记录会被还原。如果还原点内任何位置进行了commit操作，则还原点被删除： update stud set sal = 110 where no =1; commit;Commit completerollback to a1ORA-01086: 从未创建保存点 A1 1 马杰 男 1988-11-16 12 110.00特殊的删除表数据：truncate table stud;删除表中的所有记录，表结构还在，删除操作不写日志，无法找回删除的记录，但是速度很快。可以用于数据表非常庞大的时候进行删除。Oracle表的查询简单查询：查询scott的emp表，计算年工资

10、（包括奖金）：select sal*12+comm*12 工资, ename from emp;结果为，没有奖金的员工就没有年工资了，这主要是由于comm存在null值的缘故。故修改语句为：select sal*12+nvl（comm,0）*12 工资,ename from emp;使用nvl（comm,0），如果comm为null，则让comm为0。使用like关键字：%代表任意0个或多个字符，_代表任意单个字符。 select ename from emp where ename like _O%ENAME-SCOTTS%SMITH升序（asc）和降序（desc）：Select ename

11、,sal,deptno from emp order by deptno, sal desc;可以按照字段别名排序：select ename, sal*12+nvl（comm,0）*12 年薪 from emp order by 年薪;复杂查询：子查询查询最高工资的员工的姓名和工资：select ename,sal from emp where sal in （select max（sal） from emp）;不能写成：select ename,sal from emp where sal=max（sal）; select ename, max（sal） from emp;查询比平均工资高的

12、员工的信息：select * from emp where sal（select avg（sal） from emp）;查询每个部门每种岗位的平均工资：select avg（sal）,max（sal） ,deptno,job from emp group by deptno,job; select avg（sal）,max（sal） ,deptno,job from emp group by deptno,job; AVG（SAL） MAX（SAL） DEPTNO JOB- - - - 950 1100 20 CLERK 1400 1600 30 SALESMAN 2975 2975 20 M

13、ANAGER 950 950 30 CLERK 5000 5000 10 PRESIDENT 2850 2850 30 MANAGER 1300 1300 10 CLERK 2450 2450 10 MANAGER 3000 3000 20 ANALYST9 rows selected显示平均工资大于2000的的部门号和它的平均工资：select avg（sal）,max（sal） ,deptno from emp group by deptno having avg（sal）2000;总结：1、 分组函数只能出现在选择列表、having、order by子句中2、 如果select语句中同时

14、包含有group by、having、order by，那么它们的顺序是group by、having、order by。3、 在选择列中，如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则就会出错。多表查询：多表查询的条件是：连接条件至少不能少于 “表的个数-1”查询部门为10的部门名称，员工姓名，员工工作：select d.dname,e.ename,e.sal from emp e,dept d where e.deptno=d.deptno and d.deptno=10;查询员工的姓名，工资，员工的工资级别：select e.ename, e.

15、sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;自连接：查询员工的领导编号和名字：select e1.ename worker,e1.mgr bossid,e2.ename boss from emp e1,emp e2 where e1.mgr=e2.empno; select e1.ename worker,e1.mgr bossid,e2.ename boss from emp e1,emp e2 where e1.mgr=e2.empno;WORKER BOSSID BOSS- - -F

16、ORD 7566 JONESSCOTT 7566 JONESJAMES 7698 BLAKETURNER 7698 BLAKEMARTIN 7698 BLAKEWARD 7698 BLAKEALLEN 7698 BLAKEMILLER 7782 CLARKADAMS 7788 SCOTTCLARK 7839 KINGBLAKE 7839 KINGJONES 7839 KINGSMITH 7902 FORD单行子查询：查询和SMITH一个部门的员工：select e.*,d.dname from emp e,dept d where e.deptno=（select deptno from em

17、p where ename=SMITH） and e.deptno=d.deptno;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME- - - - - - - - - 7369 SMITH CLERK 7902 1980-12-17 800.00 20 RESEARCH 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 RESEARCH 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 RESEARCH 7876 ADAMS CLERK 7788 1987-5-2

18、3 1100.00 20 RESEARCH 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 RESEARCH多行子查询：查询大于部门30的任意员工工资的员工信息：select ename,sal,deptno from emp where sal any（select sal from emp where deptno=30）;或者是： （select min（sal） from emp where deptno=30）;多列子查询：查询和SMITH相同部门并且工作相同的员工信息：select * from emp where （deptno,job）

19、=（select deptno,job from emp where ename=）;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20查询高于自己部门平均工资的员工的信息：第1步，查询各个部门的平均工资和部门号：select deptno,avg（sal） mysal from emp group by deptno;DEPTNO MYSAL- - 30

20、1566.66666 20 2175 10 2916.66666第2步，把上面的查询看做为一张子表temp：select e.ename,e.sal,e.deptno,temp.mysal from emp e,（select deptno,avg（sal） mysal from emp group by deptno） temp where e.deptno=temp.deptno and e.saltemp.mysal;ENAME SAL DEPTNO MYSAL- - - -ALLEN 1600.00 30 1566.66666JONES 2975.00 20 2175BLAKE 285

21、0.00 30 1566.66666SCOTT 3000.00 20 2175KING 5000.00 10 2916.66666FORD 3000.00 20 2175Oralce分页一个有三种方式，下面是rownum分页方式：1rownum分页（select * from emp）2.显示rownumoracle分配的select a1.*,rownum rn from （select * from emp） a1;3然后筛选rownum=10的：select a1.*,rownum rn from （select * from emp） a1 where rownum=6;嵌套如下：select * from （select a1.*,rownum rn from （select * from emp） a1 where rownumEMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN- - - - - - - - - 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 6 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7 7788