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

尚学堂马士兵老师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;(解锁账号)第三课：table 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 | 用来连接两个字符串如果连接字符串中含有单引号，用两个单引号代替一个单引号。第五课：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; select 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) from dual 结果为:A select ascii(a) from dual 结果为:65 select round(23.652,1) from dual; 结果为: 23.7（第二个参数为指定四舍五入到哪位数） 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 - 改为: select 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: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代替，这样可以防止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某个字段,如果这个字段不为空就算一个. 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 ename,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)2000; 查询工资大于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); 查找每个部门挣钱最多的那个人的名字. 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.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 ename, 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 from emp,dept where emp.deptno=dept.deptno (旧) 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语句较长时，连接语句和过滤语句混在一起。 三张表连接： 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%; 把每张表连接 条件不混在一起，然后数据过滤条件全部区分开来。读起来更清晰，更容易懂一点。 select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno); 左外连接：会把左边这张表多余数据显示出来。 select e1.ename,e2,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：所谓的“外”连接，即把多余的数据显示出来。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(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 empno 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可能会自动对代码优化，所以不见得后一句就会慢）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 on 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); -只对某个字段插入数据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 rownum =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 where 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 date, grade number(2) default 1, class number(4), email varchar2(50) unique);28、五种约束条件：非空约束、唯一约束、主键约束、外键约束、检查约束（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) default 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), sdate date, grade number(2) default 1, class number(4), email varchar2(50)，constraint stu_name_email_uni unique(email, name);29、-主键约束- 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)，constraint stu_id_pk primary key (id),constraint stu_name_email_uni unique(email, 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) default 1, class number(4) references class(id), email varchar2(50)，constraint stu_class_fk foreign key (class) references class(id),constraint stu_id_pk primary key (id),constraint stu_name_email_uni unique(email, name);红色为字段约束的写法，蓝色为表级约束的写法create table class (id number(4) primary key,name varchar2(20) not null);31、修改表结构：-添加字段-alter table stu add(addr varchar2(29);-删除字段-alter table stu drop (addr);-修改表字段的长度-alter table stu modify (addr varchar2(50);/更改后的长度必须要能容纳原先的数据-删除约束条件-alter table stu drop constraint 约束名-修改表结构添加约束条件-alter table stu add constraint stu_class_fk foreign key (class) references class (id);32、-数据字典表（有user_tables、user_views、user_constraints等等）-查看当前用户下面所有的表、视图、约束-数据字典表user_tables-select table_name from user_tables;select view_name from user_views;select constraint_name from user_constraints;存储数据字典表的信息的表：dictionary; /该表共有两个字段 table_name comments /table_name主要存放数据字典表的名字 /comments主要是对这张数据字典表的描述 33、-索引(能优化查询效率)-create index idx_stu_email on stu (email);/ 在stu这张表的email字段上建立一个索引：idx_stu_email- 删除索引 -drop index index_stu_email;-查看所有的索引-select index_name from user_indexes;-创建视图-create view v$stu as selesct id,name,age from stu;视图的作用: 简化查询，保护我们的一些隐私数据，通过视图也可以用来更新数据，但是我们一般不这么用 缺点：要对视图进行维护34、-创建序列（sequence，oracle特有的东西，一般用来做主键）-create sequence seq;/创建序列select seq.nextval from dual;/ 查看seq序列的下一个值drop sequence seq;/删除序列35、-数据库的三范式-（1）、要有主键，列不可分（2）、不能存在部分依赖：当有多个字段联合起来作为主键的时候，不是主键的字段不能部分依赖于主键中的某个字段（3）、不能存在传递依赖=PL_SQL(过程化SQL语言)=38、-在客户端输出helloworld-set serveroutput on;/默认是off，设成on是让Oracle可以在客户端输出数据begindbms_output.put_line(helloworld);end;/-pl/sql变量的赋值与输出-declarev_name varchar2(20);/声明变量v_name变量的声明以v_开头beginv_name := myname;dbms_output.put_line(v_name);end;/39、-pl/sql对于异常的处理(除数为0)-declarev_num number := 0;beg