玩转oracle学习第二天

1.第一天学习回顾

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

        conn 用户名/密码&＃64;实例名
创建用户&＃xff1a;只有dba才可以创建用户

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

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

       2.2oracle支持的数据类型

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

          2.3 建表&＃xff0c;删除表&＃xff0c;修改表

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

       2.4 基本查询

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

2.5 复杂查询

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

  复杂的select语句
  数据分组函数 max min avg sum count
  ?如何显示所有员工中最高工资和最低工资
  select max(sal),min(sal) from emp;
  ?查询最高工资的员工名字和工资
  select  ename,sal from emp where sal &＃61; (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&＃xff0c;最高工资&＃xff0c;平均工资升序排列
   select avg(sal),max(sal),deptno from emp group by deptno having avg(sal) > 2000 order by avg(sal); 
  对数据分组统计的总结
  (1)分组函数只能出现在选择列中或者出现在having里面&＃xff0c;或者出现在order by子句中
  (2)如果在select语句中同时包含grou by ,having,order by三个子句&＃xff0c;
  他们的顺序一定是group by ,having,order by
  (3)在选择列中如果有列&＃xff0c;表达式&＃xff0c;和分组函数&＃xff0c;那么这些列和表达式必须有一个出现在group by子句中&＃xff0c;否则就会出错
   例如&＃xff1a;
   select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal) <2000;
   这里deptno就一定要出现在group by中

&＃xff08;1&＃xff09;多表查询&＃xff1a;

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

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