Oracle 实战

1 Oralce体系结构

oracle体系结构如下

• 数据库
• 实例
• 表空间
• 用户
• 表

以上,上对象对待下对象都是1:n的关系,但是表空间对用户又是n:1的关系,实际是n:n的关系

2 Oracle 对用户及表结构操作

创建表空间

   create tablespace itheima47    //表空间名称
   datafile 'c:/itheima47.dbf'    //对应的物理地址
   size 100m                      //初始化大小
   autoextend on                  //自动拓展  
   next 10m                       //每次拓展空间

2.1删除表空间

    drop tablespace 表空间名称

2.2创建用户

   create user tencent          --用户名
   identified by tencent        --密码
   default tablespace tencent   --默认使用的表空间

2.3 授权 connect resource dba

   grant dba to itheima47         --给用户授权DBA

2.4 Oracle库

No	数据类型	描述
1	Varchar， varchar2	表示一个字符串
2	NUMBER	NUMBER(n)表示一个整数，长度是n NUMBER(m,n):表示一个小数，总长度是 m，小 数是 n，整数是 m-n
3	DATA	表示日期类型

2.5 修改表结构

--添加一列
   alter table user1 add (phoneNum number)
   --修改列的类型或长度
   alter table user1 modify  (phoneNum varchar2(20))
   --修改列名
   alter table user1 rename column phoneNum to address
   --删除一列
   alter table user1 drop column address
   
  --删除表
   drop table user1

2.6 表数据的增删改

oracle涉及到表变化的由于事务管理等级不同,必须要提交事务才可以发生表变化

--添加 
   insert into 表(字段1，字段2) values(值1，值2);
   commit;
   --更新
   update 表 set 字段=新值 where 条件;
   commit;
   --删除
   delete from 表;          --delete和truncate删除的区别：delete删除的数据可以被回滚，会产生碎片，而truncate不会
   commit;                  --原理：delete删除是逐条删除  truncate直接把表摧毁再创建
   
   truncate table 表    

2.7 序列

--概念：连续增长的数字
     --作用：用来生成主键
     --dual: 伪表/虚表：为了补全语法
     --注意问题：序列第一次使用必须是nextval ****
     --语法：   
      create sequence 序列名
           
      create sequence id;
      
      select id.nextval from dual;   --取到序列的下个值
      select id.currval from dual;   --取到序列的当前值
           
      
       create sequence id --序列键
       increment by 5    --步长
       start with  3 --开始值
       
       
       select id.nextval from dual;

       ----添加一条记录
        insert into person (pid, pname) values (s_person.nextval, '小明');
        commit;

2.8 scott用户

----scott用户，密码tiger。
--解锁scott用户
alter user scott account unlock;
--解锁scott用户的密码【此句也可以用来重置密码】
alter user scott identified by tiger;
--切换到scott用户下

3 单行函数

作用于一行,返回一个值

      --字符函数
      upper(): 转大写
      lower(): 转小写

select upper('yes') from dual;--YES
select lower('YES') from dual;--yes

select length('abcd') from dual;     --计算长度
select concat('abc','d') from dual;  --拼接
      
select ('公司名称:'||'传智播客') from dual;   --oracle独有拼接

      --数值函数
      round(): 四舍五入
      trunc(): 截取
      mod():   取余

select round(56.16, -2) from dual;---四舍五入，后面的参数表示保留的位数
select trunc(56.16, -1) from dual;---直接截取，不在看后面位数的数字是否大于5.
select mod(10, 3) from dual;---求余数
     --日期函数
      sysdate : 当前时间
      months_between():计算两个时间段间隔了多个个月
     --转换函数
      to_char(): 转换成字符串
      to_date()：转换成日期
     --通用函数
      select 1+nvl(null,3)  from dual;  --nvl：空值处理
      select 1+nvl(3,6) from dual;
      -- 数据库遇到空值会一直视为null,此刻需要这样处理

---条件表达式
---条件表达式的通用写法，mysql和oracle通用
---给emp表中员工起中文名
select e.ename, 
       case e.ename  --对哪行取别名
         when 'SMITH' then '史密斯'
           when 'ALLEN' then '艾伦'
             when 'WARD' then '沃德'
               --else '无名'
                 end
from emp e;
---判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，
-----其余显示低收入
select e.sal, 
       case e.sal
         when e.sal>3000 then '高收入'
           when e.sal>1500 then '中等收入'
               else '低收入'
                 end
from emp e;
----oracle中除了起别名，都用单引号。
----oracle专用条件表达式
select e.ename, 
        decode(e.ename,
          'SMITH',  '史密斯',
            'ALLEN',  '艾伦',
              'WARD',  '沃德',
                '无名') "中文名"             
from emp e;

--多行函数【聚合函数】：作用于多行，返回一个值。
select count(1) from emp;---查询总数量
select sum(sal) from emp;---工资总和
select max(sal) from emp;---最大工资
select min(sal) from emp;---最低工资
select avg(sal) from emp;---平均工资

4 分组查询

--分组查询
     --语法：
     select * from 表  where 分组前条件 group by 分组条件 having 分组后条件 
     --注意问题:
        --1.select后面出现的普通字段必须要在group by后面出现
        --2.where和having后面不能使用别名
        --sql执行顺序：
           from  where  group by    having  select  order by
     
     ---查询出每个部门资高于800的员工的平均工资
     ---然后再查询出平均工资高于2000的部门
     select e.deptno 部门编号,avg(e.sal) 平均工资
     from emp e
     where  e.sal>800
     group by e.deptno
     having avg(e.sal)>2000

多表查询

   -- 查询员工姓名 员工所在部门  领导姓名 领导所在部门
     select e1.ename 员工姓名,d1.dname 员工所在部门名,e2.ename 领导姓名,d2.dname 领导所在部门
     from emp e1,emp e2 ,dept d1,dept d2
     where e1.mgr = e2.empno
     and e1.deptno = d1.deptno
     and e2.deptno = d2.deptno

(非常重点)分页查询

 --分页查询 **
    -- rownum : 行号，每查询一行记录，就会添加一个行号
            --：行号只能从1开始，不能跳过  *****
            
    --需求1： 查询emp表中前5条记录
       select rownum,e.* from emp e where rownum<6
    --需求2： 查询emp表中5条之后的记录
       select rownum,e.* from emp e where rownum > 5
       --上面查询没有结果
       --原因：rownum不能使用> = >=   原理：rownum生成时机
       --解决：改变rownum的时机 （先生成rownum,再取值）
       
       select * from 
       (select rownum r,e.* from emp e) e1 where r>5 and r<11
    --需求3： 查询emp表工资排名最高的第6到第10号员工
       select * from emp e order by e.sal desc
    
       select * from 
       (select rownum r,e.* from emp e order by e.sal desc) e1 where r>5 and r<11 
       --上面的查询结果是错误的
       --原因：先生成了rownum   再排序   再取值
       --解决: 先排序  再生成rownum  再取值
       select * from 
       (select rownum r,e1.* from 
       (select * from emp e order by e.sal desc) e1) e2 where r>5 and r<11 

rownum不能使用> = >=
行号只能从1开始，不能跳过
因此将行号取成别名

oracle 最重要区别于mysql就是序列以及分页查询