oracle基础技术点全记录

最新推荐文章于 2021-11-19 11:03:27 发布
最新推荐文章于 2021-11-19 11:03:27 发布
阅读量474 收藏 2
点赞数
分类专栏： oracle 文章标签： oracle 1024程序员节
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/alun550/article/details/104377269
版权

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
       )
     );

在这里插入图片描述

原始数据如下：
在这里插入图片描述

alun550
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle学习记录Oracle-master.zip
01-31
学习SQL语句的编写，如SELECT用于查询，INSERT用于插入数据，UPDATE用于更新数据，DELETE用于删除数据，以及DDL（数据定义语言）和DML（数据操纵语言）的基本语法，是Oracle学习的基础。 数据库管理包括用户管理、...
Oracle技术简介
03-28
orcle选修课的论文，介绍了oracle的基本特性和使用方法
ORACLE数据库中HAVING用法笔记
weixin_44999258的博客
10-15 6558
对数据聚合后计数 以自建的一张成绩表CSDN_HAVING作为示例，有三个字段NAME、SUBJECT、SCORE，分别表示”姓名“、”学科“、”成绩“，表中共10行数据，包含张三、李四、王五、赵六四人的学科成绩，其中赵六只有英语成绩，另外三人有语文、数学、英语三门学科的成绩。 NAME|SUBJECT|SCORE| ----+-------+-----+ 张三 |数学 | 80| 张三 |英语 | 90| 张三 |语文 | 70| 李四 |数学 |
oracle中having子句的使用
Golden的专栏
05-29 1479
先了解一下聚合函数，例如 SUM，COUNT，MAX，AVG等。 这些函数和其他函数的根本区别就是他们一般作用在多条记录上 select count(unit_id) from sys_user; 这里的COUNT作用在所有返回记录的unit_id字段上，结果就是该查询只返回一个结果，即总数。 通过使用GROUP BY子句，可以让SUM和COUNT这些函数对属于一组的数据起作用。当你指定BR...
ORACLE 知识汇总
qq_41968327的博客
08-17 1325
oracle知识汇总 一、数据库三大范式： 第一范式：每个列都不可再拆分； 第二范式：在第一范式的基础上，非主键字段完全依赖于主键字段，而不是依赖部分主键字段； 第三范式：在第二范式的基础上，非主键字段只依赖于主键字段，不依赖其他非主键字段。 二、数据库索引 1、索引：建立在表一列或多列的辅助对象，目的是加快访问表的数据。 2、索引的优点： (1)、创建唯一性索引，可以确保数据的唯一性； (2)、大大加快数据检索速度； (3)、加速表与表之间的连接； (4)、在查询过程中，使用优化
Oracle PL/SQL语句基础学习笔记(上)
Rosf笔记
06-26 1万+
PL/SQL语句基础学习笔记(上)
oracle中的having子句
热门推荐
fly_zxy的专栏
12-24 7万+
oracle中的having子句 having子句 having子句和group by一起使用，紧跟在group by的后边，having子句可以使用结果集中的列，也可以使用聚合函数(max、min、count、sum、svg)。having的作用是对分组后的结果进行过滤。这里在提一下where，where是对每一行进行过滤的，查询出符合where条件的每一行。having是对查询出结果集分组
PostgreSQL PG&Oracle-备份技术完美攻略
05-29
PostgreSQL 和 Oracle 都提供了闪回技术，PostgreSQL 使用 WAL 记录，而 Oracle 使用闪回日志。 PG 闪回技术 PostgreSQL 的闪回技术基于 WAL 记录，WAL 记录是 PostgreSQL 数据库中的一个日志文件，记录了所有的...
Oracle 架构全图 Oracle 架构全图
最新发布
03-09
### Oracle架构全图详解 #### 一、Oracle服务器架构概览 Oracle数据库系统以其高度可扩展性和可靠性在企业级应用中占据着重要地位。本文旨在深入解析Oracle数据库的架构组成，帮助读者理解其核心组件及工作原理。 ...
Oracle权威资料EBS基础设置全手册.docx
05-19
### Oracle EBS基础设置全手册知识点总结 #### 一、安全性管理 - **安全性概念**：安全性管理在Oracle EBS中扮演着至关重要的角色，它不仅关乎用户权限管理，还包括了对企业数据和信息的安全保护。安全性管理的...
oracle数据分发技术.pdf
05-27
Oracle 数据分发技术是数据库管理中的一种重要策略，它旨在解决数据集中带来的问题，如数据库负载、逻辑容灾、灾难恢复和个性化分析需求。Oracle 提供了多种数据分发方法，包括 exp/imp 导入导出技术、物化视图和 ...
一个国内Oracle技术大牛整理常见问题
07-21
一个国内Oracle技术大牛整理常见问题
Oracle技术整理
qq_45641500的博客
11-19 917
sql的常用方法 1.（+）表关联一般不要用 2.order by 可以分组降序 3.row_number row_number() over (partition by col1 order by col2) 表示根据col1分组，在分组内部根据 col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的) 4.rank rank() over (partition by col1 order by col2) 相同的话返回相同的序号 下一名 跳跃 5.dense_rank dens
Oracle中having函数和in函数的用法
曹晗的博客
08-08 8940
1.在数据库中having函数在实际开发中可能用的几率不太大，但是它能大大节约我们的开发，优化sql脚本。  首先介绍一下having是什么，它的实际开发场景有哪些？ 答：having是常跟在group by分组函数后面进行使用的，它出现的原因就是WHERE 关键字无法与合计函数一起使用。 having是与分组函数一起来使用的。对由sum或其它集合函数运算结果的输出进行限制。它是为了进一步强
建表时表空间的一些参数pctfree initrans maxtrans storage的含义
攻城记
04-15 1万+
create table X_SMALL_AREA  (    idx_id             NUMBER(20) not null,   pss_idx_id NUMBER(20),   update_log    VARCHAR2(512),   update_date    DATE,   constraint PK_PSS_LOG primary key (idx_id),   c...
spring事务&综合小实例
a18792721831的博客
02-25 775
spring事务+综合小实例1.事务管理的核心接口2.事务相关接口2.1PlatformTransactionManager2.2TransactionDefinition2.3TransactionStatus3.基于注解方式配置3.1配置元素3.2例子3.2.1创建一个spring项目3.2.2创建Java文件3.2.3xml文件3.2.4运行结果4.总结 1.事务管理的核心接口 spring...
Oracle 的常用技术
weixin_33976072的博客
03-27 57
1) windows / linux / Unix 平台下的安装，卸载。2）冷备与冷备的还原3）逻辑备份与还原4）RMAN 备份与还原5）数据搬迁：可用逻辑备份或者RMAN来。　6）添加表空间7）高可用之 Data Guard 安装与维护8）高可用之RAC 安装与维护9） 数据库性能诊断与调优 转载于:https://blog.51cto.com/linuxpyt...
oracle initrans 参数
一名数据库爱好者的专栏
12-25 1万+
os: centos 7.4 db: oracle 11.2.0.4 oracle 查看表或者索引时都会看到 initrans 参数，那么这个参数代表什么含义了？ INITRANS： INITRANS 指的是一个 BLOCK 上初始预分配给并行交易控制的空间 (ITLs) ( 当 BLOCK 上某笔 ROW 被交易更新锁定时，会在 BLOCK header ITL allocate 一个锁，当下一...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值