二、Oracle基本介绍和用法

1、Oracle介绍

ORACLE 数据库系统是美国 ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或 B/S 体系结构的数据库之一。

2、Oracle体系结构

2.1数据库

Oracle 数据库是数据的物理存储。这就包括（数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件）。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是 Oracle 就只有一个大数据库。

2.2实例

一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有 n 个实例。

2.3用户

用户是在实例下建立的。不同实例可以建相同名字的用户。

2.4 表空间

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为 system 表空间)。
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

2.5数据文件（dbf、ora）

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

3、表操作

3.1表空间

---创建表空间
create tablespace testSpace
datafile 'E:\App\oracle\oradata\testSpace.dbf'
size 100m
autoextend on
next 10m;

--testSpace为表空间名称 
--datafile 指定表空间对应的数据文件
--size 后定义的是表空间的初始大小 
--autoextend on 自动增长 ，当表空间存储都占满时，自动增长 
--next 后指定的是一次自动增长的大小。

---删除表空间
drop tablespace testSpace;

3 .2用户

---创建用户
create user test
identified by lyt123456
default tablespace testSpace;

---identified by 后边是用户的密码 
---default tablespace 后边是表空间名称
---oracle 数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。

---给用户授权
--oracle数据库中常用角色
connect--连接角色，基本角色
resource--开发者角色
dba--超级管理员角色

---赋予test用户dba权限
grant dba to test;

3.3Oracle的数据类型

varchar和varchar2的区别：varchar(10)：代表数据定长10；varchar2(10)：数据最大长度10，当数据实际长度小于10，数值的长度是实际长度，节约空间。

3.4表的管理

--------------切换到test用户下--------------
create table person(
       pid number(20),
       pname varchar2(10)
);
---修改表结构
---添加一列
alter table person add (age number(3));
---修改列类型
alter table person modify age number(5);
---修改列名称
alter table person rename column age to agenum;
---删除一列
alter table person drop column agenum;

4、表数据操作

4.1数据的增删改查

---查询表中记录
select rowNum,person.* from person;
----添加一条记录
insert into person(pid,pname)values(12,'张三');

---此处新增用到的是person_sequence.nextval序列，后面会讲到
insert into person(pid,pname)values(person_sequence.nextval,'张三');
commit;
----修改一条记录
update person set pname ='zhangsan' where pid =1;
commit;

---删除表
drop table person;
---清除表数据
delete from person;
--先删除表，再次创建表。效果等同于删除表中全部记录。
--在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高。
--索引可以提供查询效率，但是会影响增删改效率。
truncate table person;

4.2序列

在很多数据库中都存在一个自动增长的列,如果现在要想在 oracle 中完成自动增长的功能, 则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理.

-----根据上面的新增，新增的时候给PID赋值，显然这样是不合适的，
---因为手工填写的话不知道pid是否唯一
-----我们使用序列解决上面问题：
----序列不真的属于任何一张表，但是可以逻辑和表做绑定。
----序列：默认从1开始，依次递增，主要用来给主键赋值使用。
----dual：虚表，只是为了补全语法，没有任何意义。
create sequence person_sequence;
select  person_sequence.nextval from dual;

insert into person (pid ,pname ) values(person_sequence.nextval,'lisi');

4.3scott用户

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

------------------切换到scott用户下------------------

5、函数

1、单行函数：
● 字符函数：upper()、lower()
● 数值函数：round()、trunc()、mod()
● 日期函数：sysdate、months_between()
● 通用函数
○ 日期转字符串：to_char()
○ 字符串转日期：to_date()
2、多行函数：
● count()、sum()、avg()、max()、min()

-----1.单行函数：作用于一行，返回一个值。
---字符函数（大小写）
select upper('yes') from dual; ---大写
select lower('HELLO') from dual; ---小写
----数值函数
select round(114.03123,3) from dual;    ---四舍五入，后面的参数表示保留的位数,小数
select trunc(10.159,1) from dual;       ---直接截取，后面的参数表示保留的位数,小数
select mod(10,3) from dual;      ---求余数
----日期函数
select * from emp;
----查询出emp表中所有员工入职距离现在几天。
select trunc(sysdate  - emp.hiredate,-1) from emp;
----算出明天此刻
select sysdate+3 from dual;
----查询出emp表中所有员工入职距离现在几月。
select months_between(sysdate,emp.hiredate) from emp;
----查询出emp表中所有员工入职距离现在几年。
select months_between(sysdate,emp.hiredate)/12 from emp;
----查询出emp表中所有员工入职距离现在几周。
select trunc(sysdate-emp.hiredate,-1)/7 from emp;

----转换函数
---日期转字符串
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;
---字符串转日期
select to_date('2022-08-18 21:15:23','fm yyyy-mm-dd hh24:mi:ss') from dual;

----查询现在到1970-01-01 00:00:00经过的秒数
SELECT (CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - TO_DATE('01/01/1970 00:00:00','dd/mm/yyyy hh24:mi:ss')) * 24 * 60 * 60 FROM DUAL


----通用函数
---算出emp表中所有员工的年薪
select emp.sal*12 from emp;              
select emp.sal*12 + emp.comm from emp; 
---null.奖金里面有null值，如果null值和任意数字做算术运算，结果都是null。
---可以使用nvl()获取到对应的数值
select emp.sal*12 + nvl(emp.comm,0) from emp; 

注意：null和任意数字进行算术运算，结果都是null，可以使用nvl()函数获取数值，nvl(emp.comm,0)：取值emp.comm，如果emp.comm是null则取值0

------------------切换到scott用户下------------------
-----2.多行函数【聚合函数】：作用于多行，返回一个值。
select * from emp;
select count(*) from emp; ---查询总行数，count(*)和count(1)是一样的，count(*)本质上是调用count(1)
select sum(sal) from emp; ---查询工资总和,
select sum(comm) from emp; --请注意，null和其他值运算得到的是null
select sum(nvl(comm,0)) from emp; ---nvl()可以获取值

select max(sal) from emp;    ---最大工资
select min(sal) from emp;    ---最小工资

select avg(sal) from emp;    ---平均工资

6、表达式

6.1条件表达式

------------------切换到scott用户下------------------
-----条件表达式
---条件表达式
---条件表达式的通用写法，mysql和oracle通用
---给emp表中员工起中文名
select * from emp;
select emp.ename,
      case emp.ename
        when 'SMITH' then '斯密斯1'
          when 'ALLEN' then '艾伦2'
            when 'JONES' then '俊斯3'
              else '其他4'
                end 
 from emp;

---判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，
-----其余显示低收入
select emp.sal,
       case
         when emp.sal>3000 then '高收入'
           when emp.sal>1500 then '中等收入'
             else '中等收入'
               end
from emp;

----oracle中除了起别名，都用单引号。
----oracle专用条件表达式
select emp.ename ,decode(emp.ename,
'SMITH','11111',
'ALLEN','22222',
'WARD','33333',
'QITA')"CHINESENAME"
from emp;

7、查询

7.1分组查询

------------------切换到scott用户下------------------

---分组查询
---查询出每个部门的平均工资
---分组查询中，出现在group by后面的原始列，才能出现在select后面
---没有出现在group by后面的列，想在select后面，必须加上聚合函数。
---聚合函数有一个特性，可以把多行记录变成一个值。
select * from emp;
select deptno,avg(sal) 
from emp
group by deptno;

---查询出平均工资高于2000的部门信息
select deptno,avg(sal) avgsal
from emp
group by deptno
having avg(sal) >2000;
-----------此处不能使用avg(sal)的别名avgsal 来当作条件
select deptno,avg(sal) avgsal from emp group by deptno having avgsal >2000;
---注意：：：所有条件都不能使用别名来判断！！！
--比如下面的条件语句也不能使用别名当条件
select ename, sal s from emp where sal>1500;

---查询出每个部门工资高于800的员工的平均工资
select deptno,avg(sal)
from emp
where sal>800
group by deptno ;
----where是过滤分组前的数据，having是过滤分组后的数据。
---表现形式：where必须在group by之前，having是在group by之后。
---查询出每个部门工资高于800的员工的平均工资
---然后再查询出平均工资高于2000的部门
select deptno,avg(sal)
from emp
where sal>800
group by deptno 
having avg(sal)>2000;

7.2多表查询中的一些概念

---多表查询中的一些概念
select * from emp;  ----14条记录
select * from dept; ---4条记录
---1.笛卡尔积,A表和B表的所有组合
select * from emp,dept;  ---笛卡尔积，14*4 = 56条记录

---2.内连接
select * from emp 
inner join dept on emp.deptno=dept.deptno;
---3.查询出所有部门，以及部门下的员工信息。【外连接】
select *from emp
right join dept on emp.deptno=dept.deptno;

select * from dept
left join emp on dept.deptno =emp.deptno;

---查询所有员工信息，以及员工所属部门
select * from emp 
left join dept on emp.deptno=dept.deptno;
---oracle中专用外连接
select * from emp,dept 
where emp.deptno(+)=dept.deptno;
---查询出员工姓名，员工领导姓名
---4.自连接：自连接其实就是站在不同的角度把一张表看成多张表。
select e1.* ,e2.ename as leader from emp e1,emp e2 where e1.mgr = e2.empno;

------查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
select e1.ename,d1.dname ,e2.ename as leader,d2.dname   from emp e1
left join emp e2 on e1.mgr=e2.empno
left join dept d1 on e1.deptno = d1.deptno
left join dept d2 on e2.deptno =d2.deptno;

7.3子查询

---子查询
---子查询返回一个值
---查询出工资和SCOTT一样的员工信息
select * from emp e1
where e1.sal=(select sal from emp e2 where ename ='SCOTT');


---子查询返回一个集合
---查询出工资和10号部门任意员工一样的员工信息
select * from emp 
where emp.sal in (select sal from emp where deptno ='10');

---子查询返回一张表
---查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
---1，先查询出每个部门最低工资

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


---2，三表联查，得到最终结果。
select t.deptno,t.minsal,e1.ename,d1.dname from emp e1 , (select min(sal) minsal,deptno from emp  group by deptno) t ,dept d1
where e1.sal=t.minsal
and e1.deptno=t.deptno
and e1.deptno =d1.deptno;

7.4分页查询

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

----排序操作会影响rownum的顺序
select rownum,emp.* from emp order by emp.empno desc;
select rownum, e.* from emp e order by e.sal desc

----如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询。
select rownum ,e1.* from (
       select * from emp order by sal desc
) e1;


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

select e2.* from (
  select rownum as e2rowNum, e1.* from 
         (select rownum as e1rowNum,emp.* from emp order by sal desc) e1  where rownum <11 
)e2 
where e2.e2rowNum>5



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