尚学堂马士兵oracle笔记53节完整版(23页).doc

《尚学堂马士兵oracle笔记53节完整版(23页).doc》由会员分享，可在线阅读，更多相关《尚学堂马士兵oracle笔记53节完整版(23页).doc（23页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、-尚学堂马士兵oracle笔记53节完整版-第 23 页尚学堂马士兵老师oracle笔记 (2008-10-30 10:17:39) 第一课：客户端 1. Sql Plus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。 2. 从开始程序运行:sqlplus，是图形版的sqlplus. 3. http:/localhost:5560/isqlplus Toad：管理， PlSql Developer:第二课：更改用户 1. sqlplus sys/密码 as sysdba 2. alter user scott account unlock;(解锁账号)第三课：tabl

2、e structure(系统自带的表有emp、salgrade、dept、bonus、dual) 1. 描述某一张表：desc 表名 2. select * from 表名第四课：select 语句： 1.计算数据可以用空表：比如：.select 2*3 from dual 2.select ename,sal*12 annual_sal from emp;与select ename,sal*12 annual sal from emp;区别：加双引号保持原大小写，不加全变大写。任何含有空值的数学表达式结果都为空值。 3. select ename | abcd | 用来连接两个字符串如果连接

3、字符串中含有单引号，用两个单引号代替一个单引号。第五课：distinct select deptno from emp; select distinct deptno from emp; select distinct deptno ,job from emp 去掉deptno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。第六课：Where select * from emp where deptno =10; select * from emp where deptno 10;不等于10 select * from emp where ename =bike; selec

4、t ename,sal from emp where sal between 800 and 1500 (=800 and 800 order by sal desc; select lower(ename) from emp; select ename from emp where lower(ename) like _a%;等同于 select ename from emp where ename like _a% or ename like _A%; select substr(ename,2,3) from emp;从第二个字符开始截,一共截三个字符. select chr(65) f

5、rom dual 结果为:A select ascii(a) from dual 结果为:65（第二个参数为指定四舍五入到哪位数） select round(23.652,-1) from dual; 20 select to_char(sal,$99，999，999) from emp;（用9就可以在没有数字的地方不显示，如果用0的话一定会用0填充满） select to_char(sal,L99_999_999) from emp; L:代表本地符号 这个需要掌握牢: select hiredate from emp; 显示为: BIRTHDATE - 17-12月-80 - 改为: se

6、lect to_char(hiredate,YYYY-MM-DD HH:MI:SS) from emp; 显示: BIRTHDATE - 1980-12-17 12:00:00 - select to_char(sysdate,YYYY-MM-DD HH24:MI:SS) from dual; /也可以改为:HH12 TO_CHAR(SYSDATE,YY - 2007-02-25 14:46:14 to_date函数: select ename,hiredate from emp where hiredate to_date(1981-2-20 12:34:56,YYYY-MM-DD HH24

7、:MI:SS); 如果直接写 birthdate1981-2-20 12:34:56会出现格式不匹配,因为表中的格式为: DD-MM月-YY. select sal from emp where sal888.88 无错.但 select sal from emp where sal$1,250,00; 会出现无效字符错误. 改为: select sal from emp where salto_number($1.250.00,$9,999,99); 把空值改为0 select ename,sal*12+nvl(comm,0) from emp; 作用：把comm为空的地方用0代替，这样可以

8、防止comm为空时,sal*12相加也为空的情况.第九课: Group function 组函数（即从多行中得到一个输出）牢记组函数：max(), min(), avg(), sum(), count() select to_char(avg(sal),99999999,99) from emp; select round(avg(sal),2) from emp; 结果:2073.21 select count(*) from emp where deptno=10; select count(ename) from emp where deptno=10; count某个字段,如果这个字段

9、不为空就算一个. select count(distinct deptno) from emp; select sum(sal) from emp;第十课: Group by语句注意：count() 是计数不是空值的数量 需求:现在想求每个部门的平均薪水. select avg(sal) from emp group by deptno; select deptno, avg(sal) from emp group by deptno; select deptno,job,max(sal) from emp group by deptno,job; 求薪水值最高的人的名字. select en

10、ame,max(sal) from emp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配. 应如下求: select ename from emp where sal=(select max(sal) from emp); Group by语句应注意, 出现在select中的字段,如果没出现在组函数中,必须出现在Group by语句中. 第十一课: Having 对分组结果筛选 Where是对单条纪录进行筛选,Having是对分组结果进行筛选. select avg(sal),deptno from emp group by deptno having avg(sal)2

11、000; 查询工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列. select avg(sal) from emp where sal1200 group by deptno having avg(sal)1500 order by avg(sal) desc; 第十二课:子查询 谁挣的钱最多(谁:这个人的名字, 钱最多) select 语句中嵌套select 语句,可以在where,from后. 问那些人工资,在平均工资之上. select ename,sal from emp where sal(select avg(sal) from emp); 查找

12、每个部门挣钱最多的那个人的名字. select ename ,deptno from emp where sal in (select max(sal) from ename group by deptno) 查询会多值. 应该如下: 把select max(sal),deptno from emp group by deptno;当成一个表.语句如下: select ename, sal from emp join(select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal=t.max_sal and emp.

13、deptno=t.deptno); 每个部门的平均薪水的等级. 分析:首先求平均薪水(当成表),把平均薪水和另外一张表连接.第十四课:self_table_connection 把某个人的名字以及他的经理人的名字求出来(经理人及这个人在表中同处一行) 分析:首先求出这个人的名字,取他的编号,然后从另一张表与其相对应编号,然后找到经理的名字. select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr= e2.empno. empno编号和MGR都是编号.第十15课: SQL1999_table_connectionsselect enam

14、e, dname,grade from emp e,dept d, salgrade swhere e.deptno = d.deptno and e.sal between s.losal and s.hisal andjob CLERK 有没有办法把过滤条件和连接条件分开来? 出于这样考虑,Sql1999标准推出来了.有许多人用的还是 旧的语法,所以得看懂这种语句. select ename,dname from emp,dept;(旧标准). select ename,dname from emp cross join dept;(1999标准) select ename,dname f

15、rom emp,dept (旧) select ename,dname from emp join dept on(emp.deptno = dept.deptno); 1999标准.没有Where语句. select ename,dname from emp join dept using(deptno);等同上句,但不推荐使用. select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal); join 连接语句, on过滤条件。连接，条件一眼分开。如果用Where语句较长时，连接语句和

16、过滤语句混在一起。 三张表连接： slect ename,dname, grade from emp e join dept d on(e.deptno=d.deptno) join salgrade s on(e.sal between s.losal and s.hisal) where ename not like _A%; 把每张表连接 条件不混在一起，然后数据过滤条件全部区分开来。读起来更清晰，更容易懂一点。 from emp e1 join emp e2 on(e1.mgr = e2.emptno); 左外连接：会把左边这张表多余数据显示出来。 select e1.ename,e2

17、,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer 右外连接：把右边这张表多余数据显示出来。 select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。 全外连接： 即把左边多余数据，也把右边多余数据拿出来， select ename,dname from emp e full join dept d on(e.deptno =d.deptno);PS：所谓的“外”连接，即把多余的数据显示

18、出来。Outer关键字可以省略16-23 求部门中哪些人的薪水最高：select ename, sal from empjoin (select max(sal) max_sal, deptno from emp group by deptno) ton (emp.sal = t.max_sal and emp.deptno = t.deptno) A.求部门平均薪水的等级。 select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno)t join salgrade s on

19、(t.avg_sal between s.losal and s.hisal) B. 求部门平均薪水的等级select deptno, avg(grade) from(select deptno, ename, grade from emp join salgrade s on(emp.sal between s.losal and s.hisal) tgroup by deptno C.那些人是经理 select ename from emp where empno in(select mgr from emp); 更高效率的写法：select ename from emp where em

20、pno in(select distinct mgr from emp); D.不准用组函数，求薪水的最高值（面试题） select distinct sal from emp where sal not in( select distinct e1.sal from emp e1 join emp e2 on (e1.sal select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null)25、-面试题：比较效率（理论上前一句效率高，但实际上可能Oracle可能会自

21、动对代码优化，所以不见得后一句就会慢）select * from emp where deptno = 10 and ename like %A%; /效率高，因为将过滤力度大的放在前面 select * from emp where ename like %A% and deptno = 10;-以上为select 语句的内容-创建新用户-1、backup scott/备份exp/导出2、create usercreate user guohailong identified(认证) by guohailong default tablespace users quota(配额) 10M o

22、n usersgrant create session(给它登录到服务器的权限),create table, create view to guohailong3、import dataImp25、-取消操作-rollback-事务确认语句-commit;/此时再执行rollback无效当正常断开连接的时候例如exit,事务自动提交。 当非正常断开连接，例如直接关闭dos窗口或关机，事务自动提交-表的备份 create table dept2 as select * from dept;-插入数据 insert into dept2 values(50,game,beijing); -只对某个

23、字段插入数据insert into dept2(deptno,dname) values(60,game2);-将一个表中的数据完全插入另一个表中（表结构必须一样）insert into dept2 select * from dept;-求前五名员工的编号和名称（使用伪字段rownum 只能使用 必须使用子查询）select empno,ename from emp where rownum 10;-求薪水最高的前5个人的薪水和名字-select ename, sal from (select ename, sal from emp order by sal desc) where rown

24、um =6 and r=10-面试题： 有3个表S，C，SC S（SNO，SNAME）代表（学号，姓名） C（CNO，CNAME，CTEACHER）代表（课号，课名，教师） SC（SNO，CNO，SCGRADE）代表（学号，课号成绩） 问题： 1，找出没选过“黎明”老师的所有学生姓名。 2，列出2门以上（含2门）不及格学生姓名及平均成绩。 3，即学过1号课程有学过2号课所有学生的姓名。答案：1、 select sname from s join sc on(s.sno = sc.sno) join c on (o = o) where cteacher 黎明;2、 select sname w

25、here sno in (select sno from sc where scgrade =2);3、 select sname from s where sno in (select sno, from sc where cno=1 and cno in(select distinct sno from sc where cno = 2);27、-创建表- create table stu id number(6), name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3), sdate

26、date, grade number(2) default 1, class number(4), email varchar2(50) unique28、五种约束条件：非空约束、唯一约束、主键约束、外键约束、检查约束（check）-给name字段加入 非空 约束，并给约束一个名字，若不取，系统默认取一个- create table stu id number(6), name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3), sdate date, grade number(2) defau

27、lt 1, class number(4), email varchar2(50)-给nameemail字段加入 唯一 约束 两个 null值 不为重复- create table stu id number(6), name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50) unique-两个字段的组合不能重复 约束：表级约束- create table stu id number(6), name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3