玩转oracle学习第二天

1.第一天学习回顾

  美国Sybase公司的Sybase数据库是大型数据库，大型数据库有db2,oracle数据库
默认产生sys用户，system用户，这两个用户级别很高，另外还有scott默认普通用户

        conn 用户名/密码@实例名
创建用户：只有dba才可以创建用户

2. oracle的表管理(创建/维护)

       2.1表名和列名的规则
      （1）必须以字母开头
  （2）长度不能超过30字符
  （3）不能使用oracle的保留字
  （4）一般只能使用A-Z a-z,0-9,$ #等字符

       2.2oracle支持的数据类型

        （1）字符型
  char 最大2000字符 优点：查询速度极快 注：某个字段老被查询，并且定长，最好使用char类型
  例子：char(10) '小何'前四个字符放'小何'，后添加6个空格补全
  create table users(userName char(200),)
  varchar2(20) 变长 最大4000个字符 优点：节省空间
  clob 字符型大对象，最大4G
    （2）数字型
    number 范围 -10的38次方 -  10的38次方可以表示整数，也可以表示小数
number(5,2)
表示一个小数有5位有效数，2位小数
范围：-999.99-999.99
number(5)
表示一个五位整数
范围-99999 - 99999
(3)日期类型
date 包含年月日和时分秒
timestamp 这是oracle 9i对date数据类型的扩展
（4）图片类型
blob 二进制数据可以存放图片/声音 最大 4G

          2.3 建表，删除表，修改表

学生表
create table student
(
xh number(4),
xm varchar2(20),
sex char(2),
birthday date,
sal number(7,2)
);
drop table users;
表空间：一个表一个文件，存放在磁盘上的，oracle数据库可以创建很多表空间
可以指定将表放在某个指定的表空间中，表空间是管理表的
create table class
(
classId number(2),
cname   varchar2(40)
);
给表增加字段列
alter table student add(classId number(2));
修改表字段长度
alter tabele student modify (xm varchar(30));
修改表字段类型（谨慎）
alter table student modify (xm char(30));
删除表字段
alter table student drop column sal;
重命名表
rename student to stu;
删除
drop table student;
插入记录：date 默认格式 DD-MON-YYYY
insert into student values(2008120113,'小何','男','11-12月-1997',234.12);
修改 date的格式：
alter session set nls_date_format='yyyy-mm-dd';
插入空值：
insert into student(xh,xm,sex,birthday) values(123,'何世阳',‘男’,null);
查询为空：
select * from student where birthday is null;
改一个或者多个字段：
update student set sal = sal/2,classid = 3 where sex = '男';
修改含有null值的数据：条件是 is null
删除数据：
delete from student;
删除数据，但是表结构还在，通过日志可以恢复
例如恢复:
savepoint a; //做一个保存点
delete from student;
rollback to a;
oracle数据库保存点默认只有一个保存点，通过设置可以有多个保存点
删除表的结构和数据：
drop table student；
truncate table student;//类似delete，但是删除不写日志，所以删除速度极快
查看表结构
desc student;

       2.4 基本查询

多表查询，关联查询，嵌套查询；
  clear 清屏命令；
  emp表结构说明：
             empno（编号） ename（姓名） job（工作） mgr(上级编号) hiredate（雇佣时间） sal comm(奖金)  deptno(部门编号)
  
             dept表结构说明：部门表
           deptno(部门编号)  dname（部门名称） loc（部门所在地点）
          10
          20
         30
        40
  查看表结构：
  desc dept;
  查询所有列
  查询指定列
  注意：查询数据库切记使用*，查询所有列，尽量查询特定列，指定查询列速度较快
  疯狂自复制:
  insert into student (xh,xm,sex,birthday)  select * from student;
  如何取消重复行：
  select distinct job,deptno from emp;
  单表查询最简单
  数据区分大小写的，oracle关键字,表名，表字段名不区分大小写
  
  使用算术表达式:使用加减乘除运算符
  计算雇员的年工资：
  select enamel，sal*12 from emp;
  给列取别名
  select ename,sal*12+comm*13 "年工资" from emp;
  oracle数据库中表达式中有一个字段为空，则计算结果就为空；
  select ename,sal*12+nvl(comm,0)*13 "年工资" from emp;
  nvl处理null字段的问题
  nvl(comm,0):如果nvl为空，则结果用0代替，不为空就用comm本省
  如何连接字符串（||）
  select ename || 'is a ' || job from emp;
  使用where子句：
  查询1982年1月1日以后入职的员工
  select * from emp where hiredate > '1-1月-1982';  
select ename,sal from emp sal between 2000 and 2500;
或者
select ename,sal from emp sal >= 2000 and sal <= 2500;
like子句查询：
%：表示任意0到多个字符
_:单表任意单个字符
select ename,sal from emp where ename like 'S%';
  select ename,sal from emp where ename like '__O%';
  where条件中使用in
  select * from emp where empno=123 or empno = 234 or empno = 567;
  或者
  select * from emp where empno in(123,234,567);//此为批量处理方式，查询速度极快
  //查询上级为空的记录
  select ename from emp where mgr is null;
  使用逻辑操作符号
  select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';
  使用order by 子句，排序
  select * from emp order by sal;//薪水从低到高 asc 默认
  selecr * from emp order by desc;//薪水从高到低
  部门号升序，雇员工资降序排列
  select * from emp order by deptno asc , sal desc;
  部门号升序，雇员工入职时间降序
  order by hiredate desc;
  使用列的别名排序，as可要可不要
  select ename,(sal+nvl(comm,0))*12 as "年薪" from emp order by "年薪";
  分页查询：
  按雇员的ID号升序取出

2.5 复杂查询

实际应用中经常需要执行复杂的数据统计，经常需要显示多张表查询

  复杂的select语句
  数据分组函数 max min avg sum count
  ?如何显示所有员工中最高工资和最低工资
  select max(sal),min(sal) from emp;
  ?查询最高工资的员工名字和工资
  select  ename,sal from emp where sal = (select max(sal) from emp);
  查询列中有一个分组函数 
  请显示高于平均工资的员工
  select ename,sal from emp where sal > (slect avg(sal) from emp);
  group by 和having子句
  group by 用于查询结果的分组统计
  having子句用于限制分组显示结果  
  如何显示每个部门的平均工资和最高工资
  select avg(sal),max(sal),deptno from emp group by deptno;
  显示每个部门的每种岗位的平均工资和最高工资
    select avg(sal),max(sal),deptno,jobfrom emp group by deptno,job;
  显示平均工资低于2000的部门号和它的平均工资
  select avg(sal),max(sal),deptno from emp group by deptno having avg(sal) < 2000;  
  显示每个部门的平均工资大于2000，最高工资，平均工资升序排列
   select avg(sal),max(sal),deptno from emp group by deptno having avg(sal) > 2000 order by avg(sal); 
  对数据分组统计的总结
  (1)分组函数只能出现在选择列中或者出现在having里面，或者出现在order by子句中
  (2)如果在select语句中同时包含grou by ,having,order by三个子句，
  他们的顺序一定是group by ,having,order by
  (3)在选择列中如果有列，表达式，和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则就会出错
   例如：
   select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal) < 2000;
   这里deptno就一定要出现在group by中

（1）多表查询：

 基于两个或者两个以上的表或是视图的查询，在实际应用中，多表查询是很常见的
    例如dept表和emp表
    ？显示雇员名，雇员工资以及所在部门的名字
   select ename,sal,deptname from emp a1 ,dept a2 where a1.deptno = a2.deptno;
   此处要用到笛卡尔积，排除笛卡尔积，多表查询的条件一定不能小于表的个数减1，否则就会出现笛卡尔积 
    ?显示部门的的部门号为10的部门名，员工，工资
    select a1.deptname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno = a2.deptno and a1.deptno = 10;
    ?显示各个员工的姓名，工资，工资的级别
   salgrade //工资界别表
   grader(级别) losal（最低工资） hisal（最高工资）
    select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and hisal;
   ?显示雇员名，雇员工资及所在部门，按部门排序
  select ename,sal from emp a1,dept a2 where a1.deptno = a2.deptno order by a1.deptno;
    多表查询：自连接
   子查询：嵌入在其它sql中的select语句，也称嵌套查询 
    select * from emp where deptno = 
   (select deptno from emp where ename='SMITH');
   数据库中在执行sql是从左到右执行
    单行子查询：返回一行数据的子查询语句
   多行子查询：
    select distinct job from emp where deptnop=10;
    多行子查询中使用all操作符
   select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=30);
   或者
    select ename,sal,deptno from emp where sal > (select max(sal) from emp);
   以上两个语句的查询结果是相同的，但是第二个语句要比第一句执行效率高哦
    多行子查询中使用any操作符
    多列子查询
    select * from emp where (deptno,job) = (select deptno,job from where ename='SMITH');
   查询出比各个部门平均工资高的员工(显示高于自己部门平均工资的员工的信息)
    步骤1：查询出各个部门的平均工资和部门号：
    select deptno,avg(sal) mysal from emp group by deptno;
    步骤2：把上面的查询可看做一张子表
    select  a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,( select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno = a1.deptno a nd a2.sal > a1.mysal;  
    from子句中使用子查询时候，该子查询被作为一个视图来对待，因此也称内嵌视图，当在from子句中使用子查询的时候必须给子查询
    指定别名给表取别名不能加as，给列取别名可以加as，
  oracle的分页查询：
  oracle分页一共有三种方式：
  1.子查询
  子查询：select * from emp；
  2.rownum分页
  select a1.*,rownum rn from (select * from emp) a1;
  注：rn是oracle给每行分配的行号
  3.   
    select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum <= 10) where rn >= 6;
  指定查询列，只需要修改最里层的子查询
  select * from (select a1.*,rownum rn from (select ename,sal from emp) a1 where rownum <= 10) where rn >= 6;
  如何排序，修改最里层的子查询即可
    select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal desc) a1 where rownum <= 10) where rn >= 6;
  显示4-9的记录
   select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal desc) a1 where rownum <= 9) where rn >= 4;
  select count(*) from emp;//查询总共数据条数
  oracle分页的三种方式
  1.根据rowid来分
  2.按照分析函数来分
  3.按rownum来分（此处用这种方法）
  oracle表复杂查询 -子查询
  用查询结果创建新表
  create table myemp2 (id,ename,sal) as select empno,ename,sal from emp;
  合并查询：为了合并多个select语句的结果，可以使用集合操作符号union,union all,intersect,minus
  1)union
     该操作符号合并相同的数据记录
      select ename,sal,job,from emp where sal>2500 union 
      select ename,sal,job from emp where job='manager';    
  2)union all
    该操作不会合并相同的记录    
  3)intersect
    该操作取两个集合的交集   
  4）minus
    该操作用来取两个集合的差集，前面减去后面的

2.6 oracle数据库的创建管理
  （1)通过oracle向导工具创建
  dbca创建[数据库配置助手]
创建新的数据库
  （2)使用命令手动创建