oracle 学习 2

==============================06 oracle表的管理1 ===============================29分钟
内容介绍


1 oracle的表的管理
2 基本查询
3 复杂查询
4 oracle数据库的创建


查询，修改，插入，删除——查询比较重要。


存储过程，触发器。查询——oracle3大重点。


目标——
1 掌握oracle表的管理-创建和维护
2 掌握对oracle表的各种查询技巧
3 学会创建新的oracle数据库


表名，字母开头，小于30字符，不能使用oracle的保留字，只能使用字母，数字# $...


oracle的数据类型——
1  char 定长 最大2000字符
占用空间，但是查询速度快。
如：char(10),不够补空格

2  varchar2 变长  最大4000字符
节省空间，查询慢


3  clob（character large object）字符型大对象  最大4G


4  数字类型  number  范围-10的38次方 到 10的38次方。
可以表示整数，也可以表示小数
number（5,2）-999.99-999.99
number（5） -99999-99999






====================================07 oracle 表的管理 2===============================51分钟


5 日期类型
date 包含年月日和时分秒
timestamp oracle9i 对date数据类型的扩展，精确到毫秒。
6 图片
blob  二进制数据  可以存放图片/声音 4G
真实的项目里面，一般存放路径而已。
但是如果保密性要求高的话，放到数据库里面。


例如：学生表


create table student(
sid number(4),
sname varchar2(20),
sex char(2),
birthday date,
sal number(7,2)
);


create table class(
classid number(2),
cname varchar2(20)
);


修改表


1 添加一个字段
例如：学生表里 忘记班号了。。。
alter table student add(classid number(2));
2 修改字段的长度
例如：学生cname 改为30字符
alter table student modify(cname varchar2(30));
3 修改字段的类型、名字（不能有数据）
alter table student modify(cname char(30));
4 删除一个字段——一般不用
alter table student drop column sal;
5 修改表的名字——也不用一般
rename student to stu;
6 删除表
drop table stu;




7 添加数据
insert into stu values(1,'hzl','女','8-11月-1990',1);
oracle默认的格式——日-月-年
例如8-11月-1990
修改的默认的格式
alter session set nls_date_format='yyyy-mm-dd';


8 添加部分字段
9 插入空值
insert into stu values(1,'hzl','女',null,1);


——查询生日为空的人
select * from stu where birthday is null;
——查询生日不为空的人
select * from stu where birthday is not null;


10 修改一个字段
update stu set sex='男' where sid='1';


11 修改多个字段
update stu set sex='男',birthday='1990-11-08' where sid=1;


12 修改含有null的数据
is null
——把男同鞋的工资减到之前的一半。
update stu set sal=sal*0.5 where sex='男';


13 删除数据
delete from stu;——删除数据；表结构还在。写日志，可以找回数据。
truncate table stu;——删除数据；不写日志，不可以找回数据，速度快。
drop table stu;——表结构也被删除，表就不存在了。



savepoint aa;
rollback to aa;


操作的时候，保存点，写日志，可恢复。
有经验的人，先做保存点。
保存点，不做处理的话，默认一个保存点？？？？？
也可以多个保存点。


scott 不能建表？？？？






====================================08 oracle 表查询1 ==============================47分钟


clear——清屏命令


1 查看表结构
desc dept;
2 查询所有列
select * from dept;
3 查询指定列
select ename,sal,job,deptno from emp;
4 如何取消重复行
select distinct deptno,job from emp;


——查询smith 的薪水，工作 以及所在部门
select sal,job,deptno from emp where ename='SMITH';


set timing on;——打开操作的时间。
--从自己复制，加大数据量 大概几万行就可以了  可以用来测试sql语句执行效率
insert into users(userid,uname) select * from users;


5 使用算术表达式
——如何显示年工资
select ename,sal*12 as salY from scott.emp;
select ename "姓名",sal*12 "年工资" from scott.emp;
select ename "姓名",sal*12+comm*12 "年工资" from scott.emp;


 select ename "姓名",sal*12+nvl(comm,0)*12 "年工资" from scott.emp ;


comm 为空
oracle中，如果运算时，有个字段是null值，则整个表达式都是空。
用nvl(x,0)——如果x的值为空，则变为0，否则取x的值。




6 使用列的别名
select ename "姓名",sal*12+nvl(comm,0)*12 "年工资" from scott.emp ;


7 如何处理null值
使用nvl函数
select ename "姓名",sal*12+nvl(comm,0)*12 "年工资" from scott.emp ;


8 如何连接字符串——||
select ename || ' is a ' || job from emp;


9 使用where子句
——如何显示员工高于3000的员工
select * from scott.emp where sal>3000;
——如何查找1982,1,1后入职的员工
select * from scott.emp where hiredate>'1-1月-1982';
——如何显示工资在2000到2500的员工情况
select * from scott.emp where sal between 2000 and 2500;
select * from scott.emp where sal>=2000 and sal<=2500;


10 如何使用like操作符
%——表示0到多个字符
_——表示单个字符


——如何显示首字母为S的员工姓名和工资
select ename,sal from scott.emp where ename like'S%';
——如何显示第三个字符为大写o的所有员工的姓名和工资
select ename,sal from scott.emp where ename like'__O%';


11 在where条件中使用in
——如何显示empno为 123,7844,800。。。的雇员情况
select empno,ename from scott.emp where empno in(123,7844,800);


12 使用is null的操作符
——如何显示没有上级的雇员的情况
select ename from scott.emp where mgr is null;




================================09 表查询2 ============================================59分钟


13 使用逻辑操作符号
——查询工资高于500 或是岗位 为manager的雇员，同时还要满足他们的姓名首字母为大写的J。
select * from scott.emp where (sal>500 or job='MANAGER') and ename like 'J%';




14 使用order by 字句
——如何按照工资的从低到高的顺序显示雇员的信息
select ename,sal from scott.emp order by sal desc;
——如何按照部门号升序而雇员的工资降序排列
select ename,deptno,sal from scott.emp order by deptno asc,sal desc;
——如何按照部门号升序而雇员的入职降序排列
select ename,deptno,hiredate from scott.emp order by deptno asc,hiredate desc;




15 使用列的别名排序
——根据年薪排序
select ename,(sal+nvl(comm,0))*12 "年薪" from scott.emp order by "年薪" desc;


16 分页查询
按雇员的id号升序取出——学完子查询


oracle表复杂查询


17 oracle 表复杂查询
执行复杂的数据统计，显示多张表的数据。


18 数据分组——max，min，avg，sum，count
——如何显示所有员工中最高工资和最低工资
select max(sal),min(sal) from scott.emp;
——如何显示所有员工中最高工资以及姓名
select ename,sal from scott.emp  where sal = (select max(sal) from scott.emp); 
——如何显示所有员工的平均工资和工资总和
select avg(sal),sum(sal) from scott.emp;
——计算有多少名员工
select count(*) from scott.emp;
——显示工资最高的员工的名字，工作岗位
select ename,job from scott.emp where sal = (select max(sal) from scott.emp);
——显示工资高于平均工资的员工信息
select ename,sal from scott.emp where sal> (select avg(sal) from scott.emp);
——显示工资高低于平均工资且入职时间早于1985-1-1号员工加薪10%；
select ename,sal "原工资", sal*1.1 "加薪后" from scott.emp where sal<(select avg(sal) from scott.emp) and hiredate<'1-1月-1985';




19 group by 和 having子句
having子句用于限制分组显示结果


——如何显示每个部门的平均工资和最高工资
select deptno, avg(sal),max(sal) from scott.emp group by deptno;


——显示每个部门的每种岗位的平均工资和最低工资
select deptno,job, avg(sal),max(sal) from scott.emp group by deptno,job;？？？？？？？？？？？


——显示平均工资低于2000的部门号以及她的平均工资
select deptno,avg(sal) from scott.emp group by deptno having avg(sal)<2000;


数据分组总结
1） 分组函数只能出现在选择列表，having，order by 子句中
2） group by,having,order by 先后顺序
3） 选择列如果有列，表达式，分组函数，这些列和表达式必须有一个出现在group by子句子。？？？？？？




20 多表查询
——显示雇员名，雇员工资以及所在部门的名字（笛卡尔集）
select ename,sal,(select dname from scott.dept where deptno = scott.emp.deptno) dname from scott.emp;
select a1.ename,a1.sal,a2.dname from scott.emp a1,scott.dept a2 where a1.deptno = a2.deptno;




======================================10 多表查询3 =================================42分钟




——显示部门编号为10的部门名，员工名和工资
select d.dname,e.ename,e.sal from scott.emp e,scott.dept d where e.deptno=d.deptno and d.deptno=10;


——显示各个员工的姓名，工资，以及工资的级别 salgrade 表
select e.ename,e.sal,s.grade from scott.emp e,scott.salgrade s where e.sal between s.losal and s.hisal;


——显示雇员名，雇员工资以及所在部门的名字，并按部门排序
select e.ename,e.sal,d.dname from scott.emp e,scott.dept d where e.deptno=d.deptno order by e.deptno;


21 自连接——在同一张表的连接查询
——显示员工ford的上级领导的名字
select ename,(select ename from scott.emp e2 where e2.empno=e1.mgr) from scott.emp e1;
select ename,(select ename from scott.emp e2 where e2.empno=e1.mgr) from scott.emp e1 where e1.ename='FORD';


select e1.ename,e2.ename from scott.emp e1,scott.emp e2 where e2.empno=e1.mgr;
select e1.ename,e2.ename from scott.emp e1,scott.emp e2 where e2.empno=e1.mgr and e1.ename='FORD';


22 子查询——嵌入在其他sql语句中的select语句，也叫嵌套查询。


1)单行子查询——只返回一行数据的查询
——如何显示与smith同在一个部门的员工。
select ename from scott.emp where deptno=(select deptno from scott.emp where ename='SMITH');




2)多行子查询——返回多行数据的查询
——如何查询和部门10的工作相同的雇员的名字，岗位，工资，部门号
select ename,job,sal,deptno from scott.emp where job in (select distinct job from scott.emp where deptno=10);


3)多行子查询中，使用all操作符
——如何显示工资比部门30的所有员工的工资高的员工的姓名，工资和部门号
select ename,sal,deptno from scott.emp where sal>(select max(sal) from scott.emp where deptno=30);效率较高。
select ename,sal,deptno from scott.emp where sal>all(select sal from scott.emp where deptno=30);


4)多行子查询，使用any操作符
——如何显示工资比部门30的任意一个员工的工资高的员工的姓名，工资和部门号
select ename,sal,deptno from scott.emp where sal>any(select sal from scott.emp where deptno=30);
select ename,sal,deptno from scott.emp where sal>(select min(sal) from scott.emp where deptno=30);


5)多列子查询
——如何查询与smith的部门和岗位完全相同的所有雇员
select ename from scott.emp where (deptno,job)=(select deptno,job from scott.emp where ename='SMITH');




6)在from子句中使用子查询
——如何显示高于自己部门平均工资的员工的信息
select ename,deptno  from scott.emp e1 where sal>(select avg(sal) from scott.emp e2 where e1.deptno=e2.deptno);


——把查询的结果看成是一个视图，也叫内嵌视图。使用子查询时，要给内嵌视图起一个别名。给表起别名的时候，不能加as。给列可以加as。
select a2.ename,a2.sal,a2.deptno,a1.avgsal from scott.emp a2,(select deptno,avg(sal) avgsal 
from scott.emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.avgsal;




======================================11 表查询4 =======================================50分




23 分页查询


——按雇员的id升序取出。


oracle分页有三种。


一种——rownum分页,rownum 只能用一次，oracle分配的。一般用这个就好。
step1 先查询出前10条。
select a1.*,rownum rn from (select * from scott.emp) a1 where rownum<=10;


step2 再查出第六条——第十条，可当做一个公式记忆。
select * from (select a1.*,rownum rn from (select * from scott.emp) a1 where rownum<=10) where rn>=6;


注意——不管是排序，或者查询某些列，只需要改最里面的查询，即select * from scott.emp 就可以了。




二种——按分析函数来分，速度慢
select * from (select a1.*,row_number() over(order by deptno desc) rk from scott.emp a1) where rk<10 and rk>6;




三种——根据rowid来分   速度最快，太复杂
select * from scott.emp where rowid in( select rid from (select rownum rn,rid from 


(select rowid rid,deptno from scott.emp order by dept desc) 


where rownum<10) where rn>6) order by dept desc;




24 用查询结果创建一个新表
这个命令是一种快捷的建表方法。
create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from scott.emp;


25 合并查询——公司一般很少使用
union，union all，intersect，minus
1）union——自动 去掉结果集中的重复行。
select ename,sal,job from scott emp where sal>2500 union 
select ename,sal,job from scott emp where job='MANAGER';


2)union all ——union类似，但是不去掉重复行


select ename,sal,job from scott emp where sal>2500 union  all
select ename,sal,job from scott emp where job='MANAGER';


3）intersect——合并查询，取交集


select ename,sal,job from scott emp where sal>2500  intersect
select ename,sal,job from scott emp where job='MANAGER';


4）minus ——合并查询，差集，存在于第一个集合，而不存在于第二个集合的集合。


select ename,sal,job from scott emp where sal>2500  minus
select ename,sal,job from scott emp where job='MANAGER';




1 创建数据库的两种方法
1）通过oracle提供的向导工具——dbca数据库配置助手
2）手工步骤直接创建