D47-Oracle(多表查询，日期函数等)

一、oracle与mysql的区别：

oracle：

• 是大型的收费数据库
• 是一个多用户的数据库
• 一个新项目要为该项目创建一个新的用户

mysql：

• 一个新项目要为该项目创建一个新的数据库

二、oracle的基本操作

2.1 创建表空间

create tablespace zsl79
datafile 'c:\zsl79.dbf'
size 100m
autoextend on
next 10m;
---创建用户
create user zsl79
identified by zsl
default tablespace zsl79;

• 角色【身份】 用户【名称】，权限【操作的内容】

oracle数据库中本身就有三个角色
connect : 连接角色，基本角色
resource: 资源角色，开发角色
dba: 超级管理员

• 用户授权

grant dba to zsl79;

2.2 oracle中常用的数据类型

int: 数值类型，只能存放整数
number: 数值类型，不仅可以存放整数，也可以存放浮点型数据
char: 定长字符类型。char(2)
date: 日期类型，在oracle数据库中精确到年月日时分秒
oracle: 中的date可以用java的Date去接收，但是接收后只精确到日

• 创建一个person表

create table person (
       pid number(10),
       paname varchar2(10)
);

2.3 修改表结构

• 添加一列

alter table person add(gender char(1));

• 修改列类型

alter table person modify gender number(1);

• 修改列的名称

alter table person rename column gender to sex;

• 删除一列

alter table person drop column sex;

2.4 数据的增删改

• 添加数据

insert into person (pid,paname)
values (1,'小名');
commit;

• 修改数据

update person set paname='Tom' where pid =1;
commit;

• 在oracle数据库中除了别名之外都必须使用单引号

select * from person;

• 三种删除的区别

1. drop table person; 删除表结构
2. delete from person; 删除表中全部数据
3. truncate table person; 在数据量大的情况下效率比上面高
   先删除表， 再次创建表， 效果等于删除表中的全部数据。

2.4 SCOTT用户介绍

只要安装完oracle，就自带该用户，但默认该用户是锁定的

• 解锁soctt用户

alter user scott account unlock;

• 解锁scott用户的密码tiger（此句可以用来重置密码）

alter user scott identified by tiger;

• 切换到scott用户下

注意：

• 除了子查询，其余任何时候条件后面不能使用别名
• where 语句：where要先于select
• 查询工资高于2000的员工信息

select e.ename,e.sal s  from emp e where e.sal>2000;
select e.ename, e.sal s from emp e where e.sal > 2000;

三、查询

• 查询工资高于2000的员工信息

select e.ename, e.sal s from emp e where e.sal > 2000;

3.1 排序 order by

• 根据奖金倒序排列

select * from emp e order by e.comm desc nulls last;

• 在oracle中连接多个字符串用 ||

select e.ename||'的工资为：'||e.sal 工资 from emp e;

dual虚表： 虚表就是为了补全语法， 没有任何意义
– 因为在oracle中的查询必须有from关键字， 有from关键字后面就必须跟表
– 但是函数不属于任何一张表，每张表都可用
– 所有看函数的时候就可以from dual

3.2字符函数

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

• 数值函数

select round (56.52) from dual;-- 四舍五入  57
select trunc(56.45,1) from dual;-- 截取 -1 50；-2 0；1 56.4

• 日期函数
• 得到明天这个时候

select sysdate+1 from dual;--2018/12/24 23:36:30

• 算出emp表中所有员工入职距离现在多少天

select round (sysdate-e.hiredate) from emp e;

• 算出emp表中的所有员工入职距离现在有多少个礼拜

select round ((sysdate-e.hiredate)/7) from emp e;

• 算出emp表中的所有员工入职距离现在有多少个月

select round(months_between(sysdate,e.hiredate)) from emp e;

• 算出emp表中的所有员工入职距离现在有多少年

select round(months_between(sysdate,e.hiredate)/12) from emp e;

3.3 转换 函数

– to_char 把日期变成字符串

select to_char (sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

– to_date 把字符串变成日期

select to_date ('2018-12-22 17:55:55','yyyy-mm-dd hh24:mi:ss')from dual;

• 常用日期转换

select to_char(sysdate,'yyyy') from dual;--2018
select to_char(sysdate,'year') from dual;--twenty eighteen
select to_char(sysdate,'mm') from dual; --12
select to_char(sysdate,'dd')from dual;--23

3.4 通用函数(重要)

3.4.1 null值做任何算术运算结果都是null

• 算出每个 员工的年薪

select e.sal*12+nvl(e.comm,0)from emp e;

– 条件表达式：是根据条件来处理数据

• 给emp表中所有的员工起中文名字
• 条件表达式等值方式

select e.ename,
       case e.ename
         when 'SMITH' then '司徒武痴'
           when 'ALLEN' then '诸葛琴魔'
             when 'WARD' then '曹操'
               else '梅叶'
                 end 中文名 from emp e;

– 当else省略后，其余不满足条件的都是null值
– 一般情况下，如果我们想要去掉某些值的时候
– 可以采用表达式不带else的方式

3.5条件表达式范围方式

• 工资高于3000显示有钱人
  – 工资在1500和3000之间，显示中产阶级
  – 低于1500 ，穷

select e.ename,
      case 
        when e.sal>3000 then '有钱人'
          when e.sal >1500 then '中产阶级'
            else '穷人'
              end from emp e;

• 以上条件表达式是oracle和mysql通用的方式
• • 下面是oracle专用条件表达式
• 给emp表中所有的员工起中文名

select e.ename,
       decode (e.ename,
       'SMITH',  '曹贼',
            'ALLEN',  '司马老贼',
              'WARD',  '诸葛小儿',
                '大耳贼')
                中文名 from emp e;

– 多行函数：其实就是聚合函数。如果用在分组内，族内聚合
– 多行函数有一个特点，可以把多行记录变成一个值。

select count(1) from emp;
select sum(sal) from emp;

3.6 分组查询：会改变表结构

• 只要在group by 后面出现的字段，才能出现在select后面
• 如果没有在group by后面出现的 字段，想出现在select后面，需要加聚合函数

• 查询出每个部门的平均工资

select e.deptno,avg(e.sal)
from emp e 
group by e.deptno;

• 查询出平均工资高于2000的部门

– 除了子查询，任何条件都不能使用别名

select e.deptno,avg(e.sal) asal
from emp e
group by e.deptno
having avg(e.sal)>2000;

• 查询出每个部门工资高于1000的员工的平均工资

select e.deptno,avg(e.sal)
from emp e
where e.sal>1000
group by e.deptno;
-- where 和having的区别：
-- where 是过滤分组前原表的数据，
--having 是过滤分组后新表的数据

3.7 子查询

– 子查询的结果可以当条件

• 查询出和scott工资一样的员工信息

select * from emp where sal in
(select sal from emp where ename='SCOTT');

–子查询的结果也可以当表

• 查询出每个部门最低工资员工姓名，工资，员工所在部门名称
• 转换成每个部门最低工资表，emp表和dept表，三表联查

select e.ename,e.sal,d.dname
from emp e,(
     select  deptno,min(sal) msal
     from emp
     group by deptno
)t,dept d
where e.deptno=t.deptno
and e.sal=t.msal
and e.deptno = d.deptno;

3.8 多表查询的一些概念

• 笛卡尔积

select * from emp,dept;

3.8.1 内连接

select * from emp e inner join dept d on e.deptno=d.deptno;

• 等值连接（内连接 的新写法）

select * from emp e,dept d where e.deptno= d.deptno;

• 查询出所有部门以及部门下员工信息

select * from emp e right join dept d on e.deptno=d.deptno;

• oracle专用外连接，查询出所有部门以及部门下的员工信息

select * from emp e,dept d where e.deptno = d.deptno;

select * from emp e, dept d where e.deptno(+)=d.deptno;

– 自连接，站在不同的角度，把一张表看成多张表

• 查询出员工姓名， 员工的领导姓名

select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr=e2.mgr;

• 查询出员工姓名，员工部门名称，员工的领导姓名，领导部门名称

select e1.ename,e2.ename,d1.dname,d2.dname
from emp e1,emp e2,dept d1,dept d2
where e1.mgr=e2.mgr
and e1.deptno=d1.deptno
and e2.deptno=d2.deptno;