Oracle总结（二）

1.创建空间、用户

创建表空间

create tablespace dynamic
datafile 'E:\dynamic.dbf'
size 100m
autoextend on
next 10m;

删除表空间

drop tablespace dynamic;

2.创建用户

创建用户

create user dynamic
identified by dynamic
default tablespace dynamic;

oracle数据库中常用角色

connect --连接角色，基本角色
resource--开发者角色
dba--超级管理员角色

给用户授权
给dynamic用户授予dba角色

grant dba to dynamic;

切换到dynamic用户下
创建一个person表

create table person(
       pid number(20),
       pname varchar(10)
)

3.数据库的增删改查（ 修改表结构）

添加一列

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

修改列类型

alter table person modify gender char(1);

修改列类型

alter table person rename column gender to sex;

删除一列

alter table person drop column sex;

4.数据库的增删改查（数据）

select * from person;
--添加一条记录（提交事务,增删改都有事务）
insert into person(pid,pname) values (1,'小袁');
commit;

修改一条记录

update person set pname = '小马' where pid=1;
commit;

三个删除
–删除表中全部记录

delete from person;

删除表结构

drop table person;

先删除表，再次创建表，效果等同于删除表中全部记录
在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高
索引可以调高查询效率，但是会影响增删改效率.

truncate table person;

5.序列

序列不真的属于任何一张表，但是可以逻辑和表做绑定
序列：默认从1开始，依次递增，主要用来给主键赋值使用
dual:虚表，只是为了补全语法，没有任何意义

create sequence s_person
select s_person.nextval from dual;

—添加一条记录

insert into person(pid,pname) values (s_person.nextval,'小王');
commit;
select *  from person;

6.解锁并切换用户

scott用户，密码tiger
解锁Scott用户

alter user scott account unlock;

解锁Scott用户的密码【此具也可用来重置密码】

alter user scott identified by tiger;

7.单行函数

单行函数：作用于一行，返回一个值

（1）字符函数

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

（2）数值函数

select round(26.18,2) from dual;  --四舍五入，后面的参数标识保留的位数
select trunc(26.18,-1) from dual;  --直接截取，不再看后面的位数是否>5
select mod(10,3) from dual; --求余函数

（3）日期函数

查询emp表中所有员工入职距离现在几天。

select sysdate-e.hiredate from emp e; --日期可以直接加减，单位是天

算出明天此刻

select sysdate+1 from dual;

查询emp表中所有员工入职距离现在几月。

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

查询emp表中所有员工入职距离现在几年

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

查询emp表中所有员工入职距离现在几周。

select round((sysdate-e.hiredate)/7) from emp e; --round是四舍五入

（4）转换函数

日期转字符串

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

字符串转日期

select to_date('2020-6-2 9:52:48','fm yyyy-mm-dd hh24:mi:ss') from dual;

8.通用函数

（1）通用函数

算出emo表中所有员工的年薪
奖金里面有null值，如果null和任意数字做运算，结果都是null

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

（2）条件表达式

条件表达式的通用写法（mysql,oracle都可用）
给emp表中员工起中文名

select e.ename,
       case e.ename
           when 'SMITH' then '二多'
             when 'ALLEN' then '二喵'
               --else '多多'
                 end
from emp e;

判断emp表中员工工资，高于3000显示高收入，高于1500低于3000显示中等收入，其余显示低收入

select e.sal,
       case
           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',  '二喵',
               '多多') "中文名"  
from emp e;

9.聚合函数

多行函数【聚合函数】：作用域多行，返回一个值

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; --查询平均工资

分组查询
分组查询中，出现在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

所有条件都不能使用别名来判断(先看where，再看select)
查询出每个部门工资高于800的员工的平均工资

select e.deptno, avg(e.sal)
from emp e
where e.sal>800
group by e.deptno

查询出每个部门平均工资高于800的员工的平均工资
然后再查询出平均工资高于2000的部门

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

【注释】
where是过滤分组前的数据，having是过滤分组后的数据
表现形式:where必须再group by之前，having在group by之后

10.多表查询

多表查询中的一些概念

（1）笛卡儿积

select * from emp e,dept d;

（2）等值连接

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

（3）内连接

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

（4）外连接

查询所有的部门，以及部门下的员工信息【外连接】

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

查询所有员工信息,以及员工所属部门【外连接】

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

oracle专用外连接(+放左边：右外连接；+放右边：左外连接)

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.empno;

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

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;

11.分页查询

oracle中的分页
rownum行号：当我们做select操作的时候
每查询一行记录，就会在该行上加上一个行号
行号从1开始，依次递增，不能跳着走
排序操作会影响rownum的顺序

emp表工资倒叙排列后，每页五条记录，查询第二页

select * from emp e where rownum < 4 order by e.sal desc
select rownum,e.* from emp e where rownum < 10 order by e.sal desc

如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询

select rownum,t.* from(select rownum,e.* from emp e  order by e.sal  desc) t;

emp表工资倒叙排列后，每页五条记录，查询第二页
rownum行号不能写上大于一个正数

select * from(
  select rownum rn ,e.* from(
         select * from emp order by sal desc
  ) e where rownum <11 )
where rn >5