1.windows10安装Oracle 19

Database Software Downloads | Oracle

中间安装过程省略，可查看其他博客。

2.安装PL/SQL

Registered download PL/SQL Developer - Allround Automations

当Oracle是最新的，那么相应的配套软件一定要安装最新，最新的软件不用使用繁琐的配置，软件里自带配置，安装就可以使用。

3.登录Oracle

打开PL\SQL软件，密码就是你安装时设置的口令，而连接有三种方式，选择默认normal，因为开发不需要更改配置，而sysdata是用于更改配置的，所以选择normal。

新建SQL窗口

4.创建表空间

表空间，相当于一个划分给Oracle的内存，专门用于存储数据的区域。
create tablespace 表空间名
datafile 存储路径 文件路径必须存在，并且后缀文件名必须是 *.dbf
size 表空间的初始大小
autoextend on 自动增长 ，当表空间存储都占满时，自动增长
next 指定的是一次自动增长的大小

-- 创建表空间
create tablespace hikktn
datafile 'E:\oracle_tablespaces\hikktn.dbf' size 100M
autoextend on -- 自动增长
next 10M; -- 自动增长大小

5. 删除表空间

-- 删除表空间
drop tablespace hikktn;

6.创建用户

Oracle 中已存在三个重要的角色：connect 角色，resource 角色，dba 角色。
CONNECT 角色： —是授予最终用户的典型权利，最基本的

• ALTER SESSION —修改会话
• CREATE CLUSTER —建立聚簇
• CREATE DATABASE LINK —建立数据库链接
• CREATE SEQUENCE —建立序列
• CREATE SESSION —建立会话
• CREATE SYNONYM —建立同义词
• CREATE VIEW —建立视图

RESOURCE 角色： —是授予开发人员的

• CREATE CLUSTER —建立聚簇
• CREATE PROCEDURE —建立过程
• CREATE SEQUENCE —建立序列
• CREATE TABLE —建表
• CREATE TRIGGER —建立触发器
• CREATE TYPE —建立类型

DBA 角色：拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构，并且系统
权限也需要 DBA 授出，且 DBA 用户可以操作全体用户的任意基表，包括删除

-- 创建用户【必须给用户授权，否则无法访问表空间】
create user c##hikktn -- 用户名
identified by hikktn -- 密码
default tablespace hikktn; -- 表空间

7.授权用户

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

8.创建一个person表

-- 创建一个person表
create table person(
       id number(20),
       name varchar2(10)
);

9.数据库表结构定义

-- 修改表结构
-- 添加列
alter table person add ( sex number(1), age number(5) );
-- 修改列类型
alter table person modify sex char(1);
-- 修改列名称
alter table person rename column sex to gender;
-- 删除列
alter table person drop column age;

10.CRUD

-- CRUD
-- 查询
select * from person;
-- 添加一条数据
insert into person (id,name,gender) values (1,'王三','0');
-- 提交
commit;
-- 修改
update person set name = '李四' where id =1;
commit;
-- 三种删除方式
-- 删除表中全部数据
delete from person;
-- 删除表结构
drop table person;
-- 先删除表，再创建表。效果等同于删除表中全部数据。
-- 数据量大，先删除了索引，在删除全部数据，效率高
truncate table person;

11.创建序列

-- 序列不属于任何一张表，但是可以逻辑和表做绑定
-- 序列：默认从1开始，依次递增，用于主键赋值
-- dual:虚拟表，只是用来补全语法，没有任何存在意义
create sequence s_person;
select s_person.nextval from dual;

12.准备测试sql

如果你的版本还是12以前，那么可以执行下面命令

-- 作为初学者，Oracle提供了测试案例，可完成大量学习
-- 使用Scott用户，密码tiger
-- 想要使用scott用户，需要在超级管理员解锁后才能使用
-- scott用户不存在
-- 创建scott用户
CREATE USER c##scott IDENTIFIED BY tiger;
-- 为用户授权
grant connect,resource to c##scott;
-- 设置用户使用的表空间
ALTER USER c##scott DEFAULT TABLESPACE hikktn;
ALTER USER c##scott TEMPORARY TABLESPACE TEMP;
-- 解锁scott用户命令
alter user c##scott account unlock;
-- 解锁Scott用户的密码，该命令也可以用于重置密码
alter user c##scott identified by tiger;
-- 删除用户
drop user c##scott cascade;

此SQL是从SCOTT用户里拷贝过来的，一些不明原因，无法使用scott用户，那么就直接使用自己创建的用户，运行一下下面的SQL，创建好一样的数据就行。

拷贝的路径 ：

E:\software\WINDOWS.X64_193000_db_home\rdbms\admin\scott.sql

DROP TABLE DEPT;
CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME VARCHAR2(14) ,
    LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
    (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
    (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
    (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'C##SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
    (
    ENAME VARCHAR2(10)    ,
    JOB VARCHAR2(9)  ,
    SAL NUMBER,
    COMM NUMBER
    ) ;
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
      ( GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;

13.单行函数

作用域一行，返回一个值

字符函数

转大写 upper

-- 转大写
select upper('yes') from dual;

转小写 lower

-- 转小写
select lower('YES') from dual;

首字符大写 initcap

-- 首字符大写
select initcap('hello') from dual;

左剪切 ltrim

-- 左剪切
select ltrim('xyzadmins','xyz') from dual;

右剪切 rtrim

-- 右剪切
select rtrim('xyzadmins','admins') from dual;

字符替换 translate

-- 字符替换
-- 返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string
-- a 替换为1，c替换为3
select translate ('jack', 'abcd', '1234') from dual;

字符串替换 replace

-- 字符串替换
select replace ('jack and jue', 'j', 'bl') from dual;

字符串索引 instr

-- 查找子串位置，返回该字段索引位置
select instr ('worldwide', 'd') from dual;

截取字符串 substr

-- 截取字符串，从第三个字符截取两个字符
select substr ('abcdefg',3,2) from dual;

拼接字符串 concat

-- 拼接字符串
select concat ('hello','world') from dual;

数值函数

绝对值 abs

-- 绝对值
select abs(-15) from dual;

向上取整 ceil

-- 向上取整
select ceil(44.2) from dual;

正弦 sin

-- 正弦
select sin(1.34) from dual;

余弦 cos

-- 余弦
select cos(0) from dual;

向下取整 floor

-- 向下取整
select floor(100.2) from dual;

取余数 mod

-- 取余数
select mod(10,3) from dual;

四舍五入 round

-- 四舍五入
select round(1000.333,1) from dual;

数值截取 trunc

-- 数值截取，不看后面位数的数字
select trunc(100.256,2) from dual;

日期函数

当前时间 sysdate

select sysdate from dual;

返回两个日期间的月份 months_between

-- 返回两个日期间的月份
-- 查询出emp表中所有员工入职距离现在几月
select months_between (sysdate,e.hiredate) from emp e;
-- 查询出emp表中所有员工入职距离现在几年
select months_between (sysdate,e.hiredate)/12 from emp e;

转换函数

日期转字符串 to_char

-- 日期转字符串
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;

字符串转日期 to_date

-- 字符串转日期
select to_date(' 2021-4-29 1:50:57','fm yyyy-mm-dd hh24:mi:ss') from dual;

通用函数 nvl

-- 计算 null 使用nvl函数，返回0进行运算
select e.sal +nvl(e.comm,0) from emp e;

通用函数 nvl2

-- 判断nvl2函数里，如果第一参数为null，则返回第二个值，相反返回第三个值
select e.sal +nvl2(e.comm,null,1) from emp e;

条件表达式

等值判断 mysql和oracle通用

-- 等值判断
select e.ename,
       case e.ename
         when 'SMITH' then '大笨蛋'
           when 'ALLEN' then '大聪明'
             else '无名'
               end
from emp e;

范围判断 mysql和oracle通用

-- 范围判断
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;

14.多行函数

作用于多行，返回一个值

统计函数

总数 count

-- 总数
select count(1) from EMP e where e.empno > 7500;

最大 max

-- 最大
select max(COMM) FROM EMP;

最小 min

-- 最小
select min(COMM) FROM EMP;

平均 avg

-- 平均
select avg(COMM) FROM EMP;

总和 sum

-- 总和
select sum(COMM) FROM EMP;

15.别名

-- 别名
select e.sal as 工资 from emp e;
select e.sal 工资 from emp e;
select e.sal "工资" from emp e;

16.字符串连接 ||

-- 字符串连接 ||
select e.ename || ',' || e.empno "雇主姓名和编号" from emp e;

17.消除重复行 distinct

-- 消除重复行
select distinct e.deptno from emp e;

18.条件限定

区间查询 between … and …

-- 区间查询
-- 类似于查询工资1000元到2000元，没有符合条件时，默认返回1000
select e.sal 工资 from emp e where e.sal between 1000 and 2000;

多条件查询 in ()

-- 多条件查询
-- 类似于 where sal = 800 or sal = 2000 or sal = 500
select e.sal 工资 from emp e where e.sal in( 800 , 2000 , 500 );

模糊查询 like

-- 模糊查询
select * from emp e where e.ename like '%A%';

不为空查询 is not null

-- 不为空查询
select * from emp e where e.comm is null;

19.分组查询

-- 查询每个部门平均工资
select e.deptno , avg(e.sal) asal
from emp e
group by e.deptno;
-- 分组查询 过滤查询
select e.deptno , avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal) < 2500;
-- 查询每个部门平均工资高于500员工
select e.deptno , avg(e.sal)
from emp e
where e.sal > 500
group by e.deptno;
-- 查询每个部门平均工资高于500员工
-- 查询平均工资低于2500的部门
select e.deptno , avg(e.sal)
from emp e
where e.sal > 500
group by e.deptno
having avg(e.sal) < 2500;
-- where 和 having 区别
-- where在group by 之前过滤， having 在 group by 之后过滤

20.笛卡尔积

-- 笛卡尔积
select * from emp e, dept d;

21.等值连接

-- 等值连接 (推荐)
select * from emp e,dept d
where e.deptno = d.deptno;

22.内连接

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

23.简化等值连接 using

-- 简化连接，必须是等值连接，且两表的列字段名称和类型相同
select * from emp e inner join dept d
 using(deptno);

24.外连接

右连接

-- 右连接
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 * from emp e,dept d
where e.deptno(+) = d.deptno;

子连接

-- 子连接
-- 查询出雇员姓名，上级领导姓名
select e1.ename , e2.ename
from emp e1,emp e2
where e1.empno = e2.mgr;
-- 查询出雇员姓名，上级领导姓名，雇员的部门名称，领导的部门名称
select e1.ename , e2.ename , d1.dname ,d2.dname
from emp e1,emp e2, dept d1,dept d2
where e1.empno = e2.mgr
and d1.deptno = e1.deptno 
and d2.deptno = e2.deptno;
-- 查询出雇员编号，雇员姓名，上级领导姓名，雇员的部门名称，领导的部门名称，工资等级
select
 e1.empno, e1.ename,  d1.dname,
 decode(s1.grade,
        1,'一级',
        2,'二级',
        3,'三级',
        4,'四级',
        5,'五级') grade ,
 e2.empno,e2.ename,d2.dname,
 decode(s2.grade,
        1,'一级',
        2,'二级',
        3,'三级',
        4,'四级',
        5,'五级') grade        
from emp e1,emp e2, dept d1 , dept d2, salgrade s1 ,salgrade s2
where e1.mgr = e2.empno
and d1.deptno = e1.deptno 
and d2.deptno = e2.deptno 
and e1.sal between s1.losal and s1.hisal
and e2.sal between s2.losal and s2.hisal;

单行子查询

-- 单行子查询，返回一条记录
select * from emp where sal =
(select sal from emp where ename = 'CLARK');

多行子查询

-- 多行子查询，返回一个集合
select * from emp where sal in 
(select sal from emp where deptno = 10);

例子：

-- 查询每个部门最低工资，最低工资的员工姓名，以及该员工所在的部门名
select t.deptno , t.msal,e.ename,d.dname
from 
( select deptno , min(sal) msal from emp group by deptno ) t, emp e, dept d
where t.deptno = e.deptno
and t.msal = e.sal
and e.deptno = d.deptno;
select e2.ename,e2.job,
   round(
       (select avg(e.sal)
       from emp e
       where e.empno = e2.empno),
       2
   ) asal
from emp e2
order by e2.ename;

25.分页查询

-- 和mysql中的limit一样的功能，Oracle里面没有limit关键字
select * from emp e inner join dept d
 using(deptno) order by empno desc fetch next 5 rows only;
-- 返回结果集前5行
select * from emp where rownum <= 5;
-- 返回结果集第10行到100行
-- 排序操作会造成顺序乱掉
select * from (select rownum rn,t.* from emp t) where rn<=100 and rn >=10; 
-- 多层嵌套rownum
select rownum,t.* from (select rownum rn,e.* from emp e order by e.sal desc) t where rn<=100 and rn >=10; 
-- rownum 不能跳行
-- 推荐使用
select * from (
       select rownum rn, e.* from (
              select * from emp order by sal desc
       ) e where rownum < 11
) where rn > 5;