oracle基础技术点全记录
oracle语句记录
版权说明
本文内容主要来自以下几个方面：
1、博得教育 oracle课程 （个人从腾讯课堂购入，讲的很细致 推荐学习）
2、《Oracle从入门到精通》、《MySQL必知必会》等书籍
3、相关技术博客
4、个人经验及总结
本文初衷是记录自己的学习过程，并作为速查笔记。现公开发布，希望对有需要的人有所帮助
简记
数据库的任何操作，谨慎删除。需要删除时，以一个列作为是否被删除的标记更合适。
万莫轻易删除数据！！！
对值操作
| 操作 | 语法 |
|---|---|
| 增 | INSERT INTO tablename[ (column1, column2…) ] VALUES () |
| 删(危) | DELETE FROM tablename [ WHERE XXX ] |
| 改 | UPDATE tablename SET column1 = xx, column2 = xx WHERE XXX |
| 查 | SELECT column1, column2 / * FROM tableName |
对表操作
| 操作 | 语法 |
|---|---|
| 创建新表 | CREATE TABLE tableName( column1 type [ DEFAULT defaultValue] , column2 xxx…) |
| 删除表 | DROP TABLE tableName |
| 修改表的某列 | ALTER TABLE tableName ADD / MODIFY / DROP ( column1 xxx, column2 xxx, … ) |
| 修改表名 | RENAME oldTableName TO newTableName |
| 清空表数据及空间 | TRUNCATE TABLE tableName |
PL/SQL数据类型
| 类型 | 说明 | 范围 |
|---|---|---|
| CHAR | 定长字符串 | 最长32767字节 |
| VARCHAR2 | 变长字符串 | 最大32767字节 |
| BINARY_INTEGER | 二进制有符号整型 | -214748 ~ 2147483647 |
| NUMBER | 数值型 | 1.0e-130 ~ 9.99e125 |
| LONG | 变长字符串 | 最大2147483647字节 |
| DATE | 日期 | |
| BOOLEAN | 布尔 | TRUE / FALSE |
| ROWID | 数据表行号 | 18个字节 |
| CLOB | 字符数据 | 最大4G |
易混点
DELETE 和 TRUNCATE都可以清空表，但是DELETE速度慢，因为DELETE会产生rollback，允许回滚（撤销）删除操作 所以速度慢。且在速度慢的同时会占用很多回滚段。
Oracle的一个语法糖：
-
ROWNUM —— oracle内置的一个自增序列，会根据现有的排序情况 从1开始顺序向下递增。但是oracle中的ROWNUM只能写 小于等于，不能写大于等于（查询不出来结果）。需要使用 >= 的时候 借助子查询，并且在子查询中将ROWNUM 取个别名，使用别名进行.
SELECT ROWNUM FROM …
取ROWNUM的区间值
SELECT *
FROM (
SELECT ROWNUM r, ename
FROM newemp
)
WHERE r >= 3 AND r <= 5;
Oracle数据类型
| 数据类型 | 描述 |
|---|---|
| VARCHAR2(size) | 变长字符串 |
| CHAR(size) | 定长字符串，字符长度上限为size，小于size被设置为size，不可大于size |
| NUMBER(p, s) | 变长数值，p 总长度， s 小数长度(精度) |
| DATE | 日期 |
| LONG | 变长字符，最大2G |
| CLOB | 字符数据，最大4G |
| RAW and LONG RAW | 裸二进制数据 |
| BLOB | 二进制数据，最大4G |
| BFILE | 存储外部文件的二进制数据，最大4G |
| ROWID | 行地址，数据库自行设置 不要认为干预 |
基础数据
基础数据
基础命令
求均值（avg）
-- 求取某列的均值
select avg(sal) from emp;
select job,avg(sal) from emp group by job;
注意：此命令对空值直接跳过，不纳入统计
若要使空值也被纳入统计可以借助NVL()，如：
-- 统计空值的均值
select avg(nvl(comm, 0)) from emp;
NVL(col, value)
作用：判断该col列中是否有空值，若有空值则用value来填充。
求和（sum）
-- 求取某列的和
select sum(sal) from emp;
select sum(sal),job from emp group by job;
注意：此命令对空值直接跳过，不纳入统计
求取某列有值的行数（count）
-- 求取某列的数量（根据所有列来求是差异性最大的）
select count(comm) from emp;
select count(*) from emp;
实例1：
-- 查询10号部门员工总数
select count(*) from emp where deptno = 10;
实例2：
– 查询部门总数
select count(distinct deptno) from emp;
注意：此命令对空值直接跳过，不纳入统计
求最大值（max）
作用：求取某列最大值
用法：
-- 查询每个部门的最大薪水
select max(sal) from emp group by deptno;
分组子句（group by）
作用：分组
注意：使用group by子句的时候，select中的所有非聚合列都要在group by中作为分组条件。
原因：同一组内若有多个值，则需要对多个值进行聚合 才能使结果唯一。
用法：
-- 获取每个部门的平均薪水
select deptno,avg(sal) sal_1 from emp group by deptno;
排序子句（order by）
作用：以某列正序排序
用法：
-- 获取每个部门的平均薪水
select deptno,avg(sal) sal_1 from emp group by deptno order by deptno;
分组后筛选子句（having）
作用：对分组后的数据进行筛选，要在group by后面使用。
注意：where要在group by前面使用，并且where后面不能跟聚合函数（如 sum()、max()等）；
但是having后面可以跟聚合函数
用法：
-- having子句
select deptno,max(sal)
from emp
group by deptno
having max(sal) > 3000
having前：
having后：
同时有两个having条件的时候
写法：
-- 查询部门编号大于10且最高薪水大于2900的部门编号和最高薪水
select deptno, max(sal)
from emp
group by deptno
having deptno > 10 and max(sal) > 2900
去除空值（is not null）
作用：排除空值行
注：空值在sql中是null
用法：
原始数据：
统计时，不排除空值的结果数据：
-- 统计时不排除空值
select avg(sal) from newemp group by job;
统计时，排除空值的结果数据：
-- 统计时排除空值
select avg(sal) from newemp where sal is not null group by job;
对表操作
创建新表
PLSQL窗口创建
1、在目标目录上右键——新建
2、在弹框中填写该表信息
3、必填项
1）、一般选项卡中——表名
2）、列选项卡中——填写列名、类型及是否可为空
4、保存该表
5、查看该表是否存在
SQL语句创建新表
-- Create table
create table LOC
(
locno NUMBER,
lname VARCHAR2(20)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255;
编辑某表
PLSQL中编辑
1、选中要编辑的表右键——编辑数据
2、编辑数据
1——保持锁开启
2——编辑数据
3——保存修改
3、保存修改表（F10提交当前事务）
SQL语句编辑
1、选择某表（一定要包括rowid，并且rowid要用"表名.rowid"的形式进行）
select t.*, t.rowid from LOC t
2、编辑数据
复制已存在的表至不存在的新表
-- 创建新表
create table newemp as select * from emp;
select * from newemp;
复制已存在的表至已存在的表
-- 复制某表的副本，副本表不可以为已存在的表
CREATE TABLE newemp2
AS SELECT * FROM newemp
-- 将某表的值复制到一个已存在的表
INSERT INTO newemp2
SELECT * FROM newemp
仅复制表结构，不复制表数据
删除表
-- 删除已经存在的表
DROP TABLE newemp2
删除表中某行
-- 从某表中删除一条数据
DELETE FROM newemp2
WHERE ename = 'newff'
创建和管理表
表名规范：
1、a~z
2、A~Z
3、0~9
4、_, $, #
5、非oracle保留字
语句：
CREATE TABLE tableName( column1 type [DEFAULT defaultValue], column2 type [DEFAULT defaultValue] ...)
/*
创建STUDENT学生表
表结构：
学号——stuno
姓名——sname
年龄——age
入学日期——enrollment_date
年级——grade
性别——sex
*/
CREATE TABLE T_STUDENT(
stuno NUMBER(4),
sname VARCHAR2(20),
age NUMBER(3),
enrollment_date DATE DEFAULT to_date('2008-08-08', 'yyyy-mm-dd'),
grade NUMBER(2),
sex CHAR(2)
)
INSERT INTO T_STUDENT(stuno, sname)
VALUES(0001, '小明')
SELECT * FROM T_STUDENT
简单的实例
对查询出来的表再次进行查询
作用：对某一个表进行查询，然后对查询结果 再次 进行查询。
用法：
-- 查询部门平均工资的最大值
select max(avgsal)
from (select deptno, avg(sal) avgsal
from emp
group by deptno)
第一次查询结果：
(select deptno, avg(sal) avgsal
from emp
group by deptno)
第二次查询结果
select max(avgsal)
from (select deptno, avg(sal) avgsal
from emp
group by deptno)
注意：此种写法不能再将deptno添加到被选择的列中
多表查询
笛卡尔积连接（见下方SQL数学）
等值（数值相等）连接（见下方SQL数学）
三表联合查询
注意：多表联合查询的时候，n个表至少要有n-1个关联条件，否则会出现笛卡尔积的情况
-- 三表联合查询
select *
from emp e, dept d, loc l
where e.deptno = d.deptno and d.locno = l.locno
非等值（值位于某个区间内）连接
作用：连接两表。连接条件：某表的某列值位于另一表某列的某个区间范围内时进行连接。
实例：
表一（emp）：
表二（salgrade）：
当表一的sal列的值位于表二区间[losal, hisal]时进行匹配连接
-- 非等值连接，查询每个员工的薪资等级
select *
from emp e, salgrade s
where sal between losal and hisal
order by empno
外连接
左外连接
作用：等值连接表，但是原始的等值连接的方式在匹配不上的行（值不一样或者为空时）会被过滤掉不显示。左外连接则会将所有值进行显示，但是未匹配的值在后表中无值。
注意：存在基准表的情况，以左表为基准表。
用法（在等值连接的后面那个表的后面加"(+)"）：
-- 外连接，查询部门所有员工的所在部门名称
-- 左外连接
select e.empno, e.ename, e.job, d.dname
from newemp e, dept d
where e.deptno = d.deptno(+)
结果：
对比项（等值连接）：
-- 外连接，查询部门所有员工的所在部门名称
-- 等值连接
select e.empno, e.ename, e.job, d.dname
from newemp e, dept d
where e.deptno = d.deptno
右外连接
作用：同左外连接，只是允许右边的表中未匹配项，存在于新表中
实例：
-- 右外连接
select e.empno, e.ename, e.job, d.dname
from newemp e, dept d
where d.deptno(+) = e.deptno
全外连接
自连接
作用：使用某表与该表自身进行连接
用途：如某表中既存在员工姓名、员工编号又存在领导的姓名及编号，需要在一行中展示
如emp表中的"empno"、“mgr”（上司编号）编号是同一体系的。
实例：
-- 查询所有员工及其领导姓名，一行展示
-- 自连接
select e1.ename, e1.empno, e1.mgr, e2.ename
from newemp e1,newemp e2
where e1.mgr = e2.empno(+)
SQL数学
注意：多表查询的时候，若有同名列要用"表名.列名"的形式
并/差
并集与其他语言中相同
差集略有不同，此差集不是两表平等的求两表共同的差。而是以一表作为基准，从该基准表中删除与另外一表相同的行。
笛卡尔积（无条件连接两表）
作用：将两表链接，链接规则为——第一个表的每一行与第二个表的所有行组合，组合成一个新表
行数关系：
R表（第一张表）—— 123行
S表（第二张表）—— 100行
RxS（新表）—— 123 * 100 = 12300行
实例：
--查询两表，笛卡尔积查询
select *
from emp,dept
order by empno
查询结果有56条（emp有14条，dept有4条。14*4=56）
两表连接（有条件连接两表）
作用：以其中一表为基准，以该基准表中的某列为连接点，与另一表的某列进行连接。若两表的两列有相同值则将第二表的内容加入第一表中
等值连接
实例1：
-- 查询编号为7369的员工的姓名、职位、部门名称
select empno, ename, job, dname
from emp, dept
where emp.deptno = dept.deptno
and empno = 7369
实例2：
-- 查询编号为7369的员工的姓名、职位、部门名称
select empno, ename, job, dname
from emp, dept
where emp.deptno = dept.deptno and empno = 7369
自然连接
SQL1999的连接类型
等值连接
作用：略，同oracle中一样 会将不匹配的行过滤掉
实例：
-- SQL99等值连接
select *
from emp e join dept d
此处写连接条件
连接条件——on子句
实例1（两表连接）：
-- SQL99等值连接
select *
from newemp e join dept d
on e.deptno = d.deptno
实例2（三表连接）：
select *
from newemp e join dept d
on e.deptno = d.deptno
join loc l
on d.locno = l.locno
连接条件——using()子句
实例：
-- using子句
select *
from newemp e join dept d
using(deptno)
左外连接
实例：
-- 左外连接
select *
from newemp e left join dept d
on e.deptno = d.deptno
或者
select *
from newemp e left outer join dept d
on e.deptno = d.deptno
其中的outer可写可不写
右外连接
实例：
-- 右外连接
select *
from dept d right join newemp e
on e.deptno = d.deptno
全外连接
实例：
-- 全外连接
select *
from newemp e full join dept d
on e.deptno = d.deptno
自然连接
作用：自动寻找相同的列进行等值连接
实例：
-- 自然连接
select *
from newemp e natural join dept d
交叉连接（笛卡尔积）
实例：
-- 交叉连接
select *
from newemp e cross join dept d
子查询
作用：在某项查询的基础上继续进行查询。先进行子查询再进行主查询（先执行括号内的语句，在执行括号外的语句）。
子查询类型：
子查询实例——单行单列
单行子查询可以使用的操作符（在主查询和子查询的连接处）
子查询实例1——查询薪水高于CLARK的员工
如：emp表中需要选择出sal大于CLARK的
此处注意在sql中如果选出的列只有一行，那这个列名可以看做是一个值（如var colName = colValue;）的值的形式
select *
from emp
where sal > (select sal
from emp
where ename = 'CLARK')
其中子语句
(select sal
from emp
where ename = 'CLARK')
结果为：
子查询实例2——获取薪水最高的那个人的所有信息
1、错误写法
-- 查询拿到最高薪水的员工信息
--- 错误写法，若加上group by empno, ename则获取的结果与期望的不同
select empno, ename, max(sal)
from emp
2、正确写法
-- 查询拿到最高薪水的员工信息
--- 正确写法，先获取最高薪水。再获取最高薪水的那个人的信息
select *
from emp
where sal = (select max(sal)
from emp)
子查询实例2——查询职位和ALLEN一样且薪水高于WARD的员工
-- 查询职位和ALLEN一样且薪水高于WARD的员工
select *
from emp
where job = (select job
from emp
where ename = 'ALLEN'
)
AND
sal > (select sal
from emp
where ename = 'WARD'
)
子查询实例3——查询部门员工最低工资比部门30中员工最低工资要高的部门编号及薪资
SELECT deptno, MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) > (SELECT MIN(sal)
FROM emp
WHERE deptno = 30)
子查询实例4——查询部门平均薪水最高的那个部门的部门编号和平均薪水
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal) = (SELECT MAX(AVG(sal))
FROM emp
GROUP BY deptno
)
子查询实例5——多表查询及子查询
-- 多表及子查询
-- 查询部门平均薪水最大的部门的部门编号、部门名称、平均薪资
-- 拆分一、查询平均薪资及部门编号
SELECT deptno, AVG(sal) avgsal
FROM emp
GROUP BY deptno
HAVING AVG(sal) = (SELECT MAX(AVG(sal))
FROM emp
GROUP BY deptno
)
-- 拆分二、用部门编号查询部门名称
SELECT e.deptno, d.dname, e.avgsal
FROM dept d, (SELECT deptno, AVG(sal) avgsal
FROM emp
GROUP BY deptno
HAVING AVG(sal) = (SELECT MAX(AVG(sal))
FROM emp
GROUP BY deptno
)
) e
WHERE e.deptno = d.deptno
子查询实例——多行单列
多行子查询的主要操作符有"IN"、“ANY”、“ALL”
- IN —— 父查询的某个条件在子查询（多行单列）的值列表中
- ANY —— 常配合 “>”、"<"等符号操作，父查询的值 大于/小于 子查询中任一值（小于最大或者大于最小即可，类似or只要在子查询中有一个条件成立即可）
- ALL —— 常配合 “>”、"<"等符号操作，父查询的值 大于/小于 子查询中所有值（小于最小或大于最大，类似and 要子查询中所有值都符合才可以）
-- 查询每个部门员工薪水最高的那个员工的信息
-- 拆分一、查询每个部门员工的最高薪水
SELECT MAX(sal) maxsal
FROM emp
GROUP BY deptno
-- 拆分二、用最大薪资查询对应信息
SELECT *
FROM emp e, (SELECT MAX(sal) maxsal
FROM emp
GROUP BY deptno) d
WHERE e.sal = d.maxsal
使用 “in” 来优化后
SELECT *
FROM emp
WHERE sal IN (
SELECT MAX(sal) maxsal
FROM emp
GROUP BY deptno
)
Oracle的增、删、改
增加一条记录
语法：
INSERT INTO tableName(column1[, column2, column3...]) VALUES(value1[, value2, value3])
COMMIT
1、前面几个列，后面几个值
2、INSERT INTO只是临时增加，需要通过COMMIT来使其永久生效
3、若不指定column则代表为所有列都赋值，VALUES()中要把所有列的值都给上，即使为空也要给NULL
4、若要插入日期，有以下两个当时
1）、插入当前系统日期：
INSERT INTO newemp(newdate) VALUES(SYSDATE)
COMMIT
2）、插入某个指定日期
INSERT INTO newemp(newdate) VALUES(TO_DATE('2008-08-08', 'yyyy-mm-dd'))
COMMIT
示例：
注意：提交完后要按F10进行commit 或者 shift + F10回滚
更新表
语法：
UPDATE tableName
SET column1 = value1, column2 = value2...
[WHERE 筛选条件]
注：如果不加WHERE条件，那么会对表中所有数据进行操作
实例：
给newff加薪200，并且职位变为manager
UPDATE newemp2
SET sal = sal + 200, job = 'manager'
WHERE ename = 'newff'
正确示例：
错误示例（将所有的值都更新了）：
示例2：
-- 将ALLEN的薪水更新为和WARD一样，职位和SMITH一样
UPDATE newemp
SET sal = (SELECT sal
FROM emp
WHERE ename = 'WARD'),
job = (SELECT job
FROM emp
WHERE ename = 'SMITH')
WHERE ename = 'ALLEN'
删除表中数据
语法：
-- 删除表中数据
DELETE FROM tableName
[WHERE condition]
注意：
若WHERE条件子句不存在，则删除所有的数据
实例：
原始表
执行语句
-- 删除newemp表中的ename为newdd的数据
DELETE FROM newemp
WHERE ename = 'newdd'
运行结果
数据库约束
基本概念
数据库约束是为了确保数据的修改是正确的，符合既定规则的。
数据库的完整性约束分为五种：
- NOT NULL —— 非空约束
- UNIQUE —— 唯一性约束
- PRIMARY KEY —— 主键约束，主键约束 = 非空约束 + 唯一性约束
- FOREIGN KEY —— 外键约束，指该列与某个表以此列相连接，所以更新该表的该列值的是否要与另外一表可以找到对应数据（保证依然能关联上）
- CHECK —— 检查约束，自定义约束，分为列级和表级
约束实例
列级约束是直接写在列后面的，用以控制该列的约束条件。
主键约束代表非空且唯一，主键可以为一个列或者多个列组合形成。
列级约束
列级约束实例：
CREATE TABLE STUDENT(
stuno NUMBER(4) PRIMARY KEY, -- 主键约束
sname VARCHAR2(20) NOT NULL, -- 非空约束
age NUMBER(3) CHECK( age BETWEEN 1 AND 100), -- CHECK 约束，自定义约束
stid VARCHAR(25) UNIQUE -- 唯一性约束
)
1）、向表中插入符合约束条件的数据
2）、插入数据，违背——主键约束
3）、插入数据，违背——非空约束
4）、插入数据，违背——CHECK约束
5）、插入数据，违背——主键约束
表级约束
1、先添加一个clazz表，并插入以下数据，表示班级。用以和学生表进行外键连接。
2、创建一个新的学生表student2
表级别约束的写法：
CONSTRAINT 约束条件名 约束条件类型
约束条件名的规范：
非外键：
2个字符的约束条件_作用的表名_作用的列
外键：
2个字符的约束条件_作用的表名_外键关联的表_作用的列
外键约束的写法：
CONSTRAINT 约束条件名 FOREIGN KEY( 列名 ) REFREENCES( 外键连接表名( 外键主键列名 ) )
3、查看student2表约束条件是否生效
4、插入数据
1）、插入一个符合所有条件的行
2）、插入一个不符合外键约束的行，插入的数据的clano在外键连接的表clazz中不存在，报错"未找到父项关键字"
关于外键的"联级删除"(“on delete”)
- No action —— 受限关联（拒绝删除），当被外键关联表中的值要被删除时，拒绝删除。注意，此处要删除的数据是被外键关联的表中的数据（即上面例子中的clazz），影响拒绝删除操作的是主动关联的那张（就是现在正在设置"联级删除"的这张表，对应上面例子中的student2）。若存在多张表关键关联一个表的情况，需要将所有表的"联级删除"都改为"Cascade "才可正常删除被关联表中的数据。 若删除了被外键外联表的某条数据，其他与该表外键关联的表中的对应数据也会被同时删除！！！
- Cascade —— 既联删除（同步删除，危险）当设置为此值的时候，可以删除被外键关联的表的数据。若存在多张表关键关联一个表的情况，需要将所有表的"联级删除"都改为"Cascade "才可正常删除被关联表中的数据。 若删除了被外键外联表的某条数据，其他与该表外键关联的表中的对应数据也会被同时删除，如上例中删除了clazz的1001班级，则student2表中clano为1001的所有记录全部同时删除。若有其他表也与此clazz表外键关联，则对应数据同步删除！！！
- Set Null —— 当被关联表(clazz)中的数据删除时，与其外键关联的表(student2)中的对应数据，不会被删除，只是将外键列设置为null
实例：
1、clazz表为
2、student2表为
3、两表关联结果如下
4、修改student2表的"On Delete"为"Cascade"
5、删除clazz的1001数据，student2表中与clazz表中关联的数据同步删除
6、设置student2的"On Delete"为"Set Null"，可以看到对应student2表中的数据并未删除 只是将班级编号设置为了null
视图
视图的作用：常用的应用场景是：
1、对不同用户所能查询的数据进行区分。在不修改表或创建新表的情况下，通过创建视图的方式对表中的数据进行一次筛选，用户仅可在此视图（被筛选过的数据）中进行查询，从而达到控制不同用户可以查看数据的目的
2、对多个表进行关联查询或者使用分组函数进行查询，从而在需要多次进行复杂查询的时候，仅查询视图即可
scott并不具有创建视图的权限，需要切换到sys账户 将此权限赋予scott账户
GRANT CREATE VIEW TO scott;
视图的几个特点
0、视图中不包含数据，数据依旧存储在基表当中
1、可以通过视图去修改基表，即视图可以执行DML操作
2、在视图包含 GROUP、GROUP BY、DISTINCT 关键字的时候不能删除数据行
3、with read only 创建的视图只能用于查询，如：... AS (...) WITH READ ONLY
创建视图
从emp表中创建一个sal>=1500的表视图
-- 创建视图
CREATE VIEW empvu
AS (
SELECT * FROM emp
WHERE sal >= 1500;
)
从视图中查询数据
查询方法和表完全相同
-- 从视图中查询数据
SELECT * FROM empvu
删除视图
-- 删除视图
DROP VIEW empvu
复杂的视图创建
创建所有员工及其所在部门的视图
-- 创建所有员工及其所在部门的视图
CREATE VIEW empdept2
AS (
SELECT e.ename, d.dname
FROM emp e LEFT JOIN dept d
ON e.deptno = d.deptno
);
通过视图修改基表数据
注意：对多表查询的视图，一次仅可修改其中的一个表，无法同时修改多个表
-- 通过表视图修改基表
UPDATE empdept3
SET empno = 4, ename = 'newgg'
WHERE empno IS NULL
初始视图
初始基表
修改后的视图
修改后的基表
序列
创建序列
-- 创建序列
CREATE SEQUENCE seqName
[ START WITH n ] -- 起始值
[ INCREMENT BY n ] -- 步长
[ {MAXVALUE n | NOMAXVALUE} ] -- 最大值
[ {MINVALUE n | NOMINVALUE} ] -- 最小值
[ {CYCLE | NOCYCLE} ] -- 当序列值超出最大值时，是否重头开始编号
[ {CACHE n | NOCACHE} ] -- 是否使用缓存
实例：
CREATE SEQUENCE mysqe
START WITH 100
INCREMENT BY 2
MAXVALUE 110
MINVALUE 100
NOCYCLE
NOCACHE;
使用序列
-- 使用序列
INSERT INTO emp(empno, ename)
VALUES(mysqe.nextval, 'seqtest')
再次调用
查询序列的当前值
-- 查询序列的当前值
SELECT mysqe.currval FROM dual;
删除序列
-- 删除序列
DROP SEQUENCE mysqe;
索引
索引的使用场景：
1、在主键上创建索引（oracle自动创建），为保证数据唯一性
2、在需要经常搜索的列上创建索引，以提高搜索速度
3、在表于表连接的外键上创建索引，以提高表连接速度
4、在经常需要排序的列上创建索引，以提高排序查询的速度
5、在经常需要where判断的列上创建索引，以提高条件判断速度
注意：
1、不要在类型为text、image和bit的列上添加索引
2、对频繁增删改的数据不宜创建索引，索引用于提高查询速度，但索引依旧需要维护 所以频繁修改会使维护索引的资源消耗提高。
创建索引
-- 创建索引
CREATE INDEX newemp_ename_index
ON newemp(ename, deptno)
PLSQL使用技巧
修改SQL代码字体大小及样式
1、工具——首选项
2、字体——编辑器——选择
3、设置即可
设置关键字自动大写
1、工具——首选项
2、编辑器——语法高亮——关键字大小写
Oracle数据库基本命令
账户相关（sqlplus中操作）
显示用户
SQL --> show user;
切换当前活动用户
SQL --> conn 用户/密码
如：conn scott/tiger
断开数据库连接
SQL --> disc
修改密码
SQL --> passw
PL/SQL
PL/SQL逻辑块的三部分：
1、声明部分
2、执行体部分
3、异常处理部分
-- PL/SQL逻辑块的结构
[ DECLARE ]
* 声明部分，用以声明变量、常量、游标、自定义异常
BEGIN
* 代码执行体
[ EXCEPTION ]
* 异常处理部分
END;
PL/SQL的变量定义及赋值。
1、定义格式： 变量名 数据类型;
2、赋值： 变量名 数据类型 := 值;
赋值使用 “:=” 而非 “=”
在一个逻辑块的BEGIN中可以嵌套另外一个逻辑块，其作用域与python、js的函数相同
简单实例
新建测试窗口
-- PL/SQL实例
DECLARE
-- 定义变量
v_name VARCHAR2(20);
-- 定义并赋值，赋值的时候 使用 := 而非 =
v_sex CHAR(3) := '男';
-- 定义变量时，没有赋值 则默认为NULL
v_age NUMBER(3);
-- 二进制整型，相较于十进制效率更好
v_counter BINARY_INTEGER;
v_result BOOLEAN;
-- 定义常量
v_pi CONSTANT NUMBER(3,2) NOT NULL := 3.14;
BEGIN
-- 执行中赋值
v_name := '张三';
-- PL/SQL中没有 ++ 也没有 +=
v_counter := v_counter + 1;
v_result := TRUE;
-- 输出
dbms_output.put_line(v_name);
dbms_output.put_line(v_counter);
dbms_output.put_line(v_PI);
END;
执行结果在DBMS输出中查看
简单实例 —— 向表中插入一条记录
DECLARE
v_empno NUMBER(4) := 1001;
v_name VARCHAR2(10) := 'hellen';
v_job VARCHAR2(10) := 'salesman';
v_sal NUMBER(4) := 3000;
BEGIN
-- 插入一条数据
INSERT INTO emp(empno, ename, job, sal)
VALUES(v_empno, v_name, v_job, v_sal);
COMMIT;
-- EXCEPTION
END;
简单实例 —— 查询并获取表中记录，并赋值给某个变量
DECLARE
-- 用于查询的变量
v_sel_empno NUMBER(4);
-- 用于存放记录值的变量
v_empno NUMBER(4);
v_ename VARCHAR2(10);
v_job VARCHAR2(10);
v_sal NUMBER(4);
BEGIN
v_sel_empno := 1002;
-- 查询
SELECT empno, ename, job, sal
-- 赋值
INTO v_empno, v_ename, v_job, v_sal
FROM emp
WHERE empno = v_sel_empno;
-- 输出显示
dbms_output.put_line('empno'|| ' | ' || 'ename'|| ' | ' || 'job'|| ' | ' || 'sal');
dbms_output.put_line(v_empno|| ' | ' || v_ename|| ' | ' || v_job|| ' | ' || v_sal);
END;
简单实例 —— 自定义记录类型
自定义记录类型是用于匹配表结构的一个数据类型，类似于JS中的Object，需要使用 TYPE self_record_name IS RECORD( columns… ) 的方式进行定义。 定义完成后需要进行实例化，然后将查询的结果直接插入实例化对象中（非标准叫法，方便记忆而已）。
DECLARE
v_sel_empno NUMBER(4);
-- 自定义记录类型，类似于js的object
TYPE emp_record_type IS RECORD(
v_empno NUMBER(4),
v_ename VARCHAR2(10),
v_job VARCHAR2(10),
v_sal NUMBER(4)
);
-- 实例化记录类型
emp_record emp_record_type;
BEGIN
v_sel_empno := 1002;
-- 将查询的结果存入实例化对象中
SELECT empno, ename, job, sal
INTO emp_record
FROM emp
WHERE empno = v_sel_empno;
-- 输出显示
dbms_output.put_line('empno'|| ' | ' || 'ename'|| ' | ' || 'job'|| ' | ' || 'sal');
dbms_output.put_line(emp_record.v_empno|| ' | ' || emp_record.v_ename|| ' | ' || emp_record.v_job|| ' | ' || emp_record.v_sal);
END;
实例综合
完全复制某表
-- 完全复制某表为新表
CREATE TABLE tableName
AS(SELECT *
FROM anotherTableName);
完全复制某表的表结构为新表
-- 完全复制某表的表结构
CREATE TABLE tableName
AS(SELECT *
FROM anotherTableName
WHERE 1 != 1
)
向已存在的表中插入某表的所有数据
-- 将某表的值复制到一个已存在的表
INSERT INTO newemp2
SELECT * FROM newemp
复制某表中的一行，表结构相同
-- 复制某表中的一行
INSERT INTO targetTable
SELECT * FROM originTable
WHERE xxx
targetTable —— 要将数据复制到的表
originTable —— 数据现在存在于的表
复制某表中的一行，表结构不同
-- 复制某表中的一行
INSERT INTO targetTable(column1, column2, column3)
SELECT column1, column2, column3 FROM originTable
WHERE xxx
targetTable和originTable的column1, column2, column3要对应（数据类型）
将表中newff的薪水更新为和WARD一样， 部门更新为和SMITH一样
-- 将newff的薪水更新为和WARD一样， 部门更新为和SMITH一样
UPDATE newemp2
SET sal = (
SELECT sal
FROM newemp2
WHERE ename = 'WARD'
), deptno = (
SELECT deptno
FROM newemp2
WHERE ename = 'SMITH'
)
WHERE ename = 'newff'
将表中newff的薪水增加200，并将职位设置为manager
UPDATE newemp2
SET sal = sal + 200, job = 'manager'
WHERE ename = 'newff'
给表增加一列
-- 给表增加一列
ALTER TABLE T_STUDENT
ADD (height NUMBER(3, 2), score NUMBER(5,2))
修改表的某列属性
-- 修改列属性，而非列值
ALTER TABLE T_STUDENT
MODIFY ( height NUMBER(3), score NUMBER(1))
TOP-N分析
原理：先按某个排序规则将表进行排序，然后使用子查询（子查询就是一个临时视图，非数据库对象），并且在查询中借助ROWNUM添加一个自增序列 通过该序列达到对数据提取TOP-N的目的
例1：选择出薪水最高的5个人（ 不通过薪水值进行过滤，因为实际项目中这个值一定会动态变化的 ）
-- 选择出薪水最高的5个人
SELECT ROWNUM, ename, sal
FROM (
SELECT ename, sal
FROM newemp
WHERE sal IS NOT NULL
ORDER BY sal DESC
)
WHERE ROWNUM <= 5;
注意，如果是筛选出子查询的结果视图中所有的列，则不可直接使用 * ， 而要将此视图重命名并使用 视图名.* 的方式查询
错误：
SELECT ROWNUM, *
FROM (
SELECT ename, sal
FROM newemp
WHERE sal IS NOT NULL
ORDER BY sal DESC
)
WHERE ROWNUM <= 5;
正确：
推荐使用这种方式进行查询，在需要修改的时候，直接修改子查询中的内容即可
SELECT ROWNUM, t.*
FROM (
SELECT ename, sal
FROM newemp
WHERE sal IS NOT NULL
ORDER BY sal DESC
) t
WHERE ROWNUM <= 5;
例2：查询薪水在4-6位的员工
方法一：给ROWNUM取个别名 —— 简单
SELECT *
FROM (
SELECT ROWNUM r, t.*
FROM(
SELECT ename, sal
FROM newemp
WHERE sal IS NOT NULL
ORDER BY sal DESC
) t
)
WHERE r >= 4 AND r <= 6;
方法二：使用ROWNUM查询别的可以直接使用 >= 进行筛选的字段 —— 麻烦
SELECT ROWNUM, t.*
FROM(
SELECT ename, sal
FROM newemp
WHERE sal IS NOT NULL
ORDER BY sal DESC
) t
WHERE
sal >= (
-- 获取6个人中薪水最低的
SELECT MIN(sal)
FROM(
SELECT ROWNUM, ename, sal
FROM (
SELECT ename, sal
FROM newemp
WHERE sal IS NOT NULL
ORDER BY sal DESC
)
WHERE ROWNUM <= 6
)
)
AND
sal <= (
SELECT MIN(sal)
FROM(
-- 获取4个人中薪水最低的
SELECT ROWNUM, ename, sal
FROM (
SELECT ename, sal
FROM newemp
WHERE sal IS NOT NULL
ORDER BY sal DESC
)
WHERE ROWNUM <= 4
)
);
原始数据如下：
