1 数据库

1.1 什么是数据库？

  • 车库：放汽车的仓库，实体化
  • 米库：放大米的仓库，实体化
  • 数据库：放数据的仓库，数据化

我们口中的数据库：

  • 是数据库管理系统和数据库组成
  • 仓库管理系统和仓库
1. 1.1 数据的发展历史

生活中的存放数据的方式

  • 笔记本、手机、竹筒、计算机文档、表格、数据库
  1. 手工管理：容易丢、修改困难、数据量多不好处理、不方便查找
  2. 文档文件：使用方便、什么都要面面俱到、电脑不能坏、查找不方便
  3. 数据库：弥补了大部分缺点，对使用人群有限制

优势：持久化，数据共享，数据一致性，数据安全性，高效性（SQL）

1.2 数据库的特性

  1. 原子性：原子性确保一个事务被视为一个不可分割的工作单元。这意味着要么事务中的所有更改都提交到数据库，要么都不提交。如果事务的任何部分失败，整个事务都将回滚，数据库恢复到之前的状态。
  2. 一致性：一致性确保事务将数据库从一个有效状态转换为另一个有效状态。它强制执行对数据库定义的完整性约束和规则，以使数据库在事务之前和之后保持一致的状态。
  3. 隔离性：隔离性确保并发事务不会相互干扰。每个事务都像在隔离环境中执行一样，即使可能有多个事务同时执行。隔离性防止出现脏读、不可重复读和幻读等问题。
  4. 持久性：持久性保证一旦事务提交并将更改保存到数据库中，即使系统发生故障（如断电或崩溃），更改也会持久存在。更改变得永久且无法撤销。
1.2.1 数据库表之间的关系
  1. 一对一：每个员工只有一个工作时间
  2. 一对多：一个顾客ID可以对应很多个订单ID，但是一个订单只能有一个顾客
  3. 多对多：很多菜可以对应一个顾客，很多顾客可以对应很多菜

1.2.2 数据库的分类

关系型数据库：采用关系模型来组织数据的，处理简单的文字数据，可以用表来存储并且可以直观表达的

常见的：Oracle、MySQL、SQL serve…

非关系型数据库：主要针对的是高可用、高扩展、性能，处理这种高并发的数据、表内存储不了的，视频、音频、图片…

常见的：Mong DB、DB2、Redius…

关系型数据库相当于非关系型数据库的优势：

  • 数据一致性：使用ACID来管理数据，保证了数据的完整性
  • 结构化：采用的是表格化的方式来管理数据，结构清晰
  • 可靠性：有更成熟的查询语句(SQL)
1.2.3 什么是SQL？

是一个编程语言，可以用来增删改查数据库，是一种标准化语言

分类：

  1. DQL：数据查询语句，查询数据，关键词：select
  2. DML：数据操作语句，增删改数据，关键词：insert、update、delete
  3. DDL：数据定义语句，创建数据库的表、索引、视图、游标、
  4. 序列，关键词：create、define、alter、drop
  5. DCL：数据控制语句，用于对用户的授权，关键词：grant、alter、revoke
  6. TCL：事务控制语句，用于控制事务的提交、回滚等等，关键词：commit、rollback
1.2.4 Oracle
1.2.4.1 什么是Oracle

甲骨文，是一个数据库

版本：“”

  • Oracle 7：支持分布式数据库和存储过程
  • Oracle 8：支持Java、物理化视图、分区表
  • Oracle 9i（Internet）：支持XML、RAC
  • Oracle 10g（grocery）：新增手动调优
  • Oracle 11g：新增自动调优
  • Oracle 12c（cloud）：新增存储序列，并且支持云端

1.3 数据库安装

1.3.1 上传解压工具7z

上传完安装

1.3.2 上传Oracle10ge安装文件

上传完成利用7z解压

1.3.3 找到安装文件开始安装

1.3. 4 开始安装

  • 不用管这个页面

1.3.5 在2003里cmd测试连接

sqlplus / as sysdba

1.3.6查看数据库服务名

select instance_name from v$instance

  • 解压远程连接工具，路径不能有中文和特殊符号

在instantclient的路径输入cmd打开cmd窗口输入下方命令测试连接

sqlplus sys/密码@//数据库IP:1521/服务名 as sysdba

数据库IP就是你安装数据库的虚拟机的IP

查询用户

连接成功

配置环境变量，两个地方，一个外部一个path内部

路径选择你自己存放的路径，不能有中文和特殊符号

配置TNS协议

使用别名测试登录

sqlplus sys/密码@别名 as sysdba

中文配置的环境变量

NLS_LANG

AMERICAN_AMERICA.ZHS16GBK

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

解压PLSQL

打开登录软件

查询全部用户

select * from all_users;

修改密码并解锁scott用户

alter user scott identified by “123456”;

alter user scott account unlock;

使用scott用户登录

永久注册

测试

— 查询emp表的内容

select * from emp;

— 查询scott用户拥有的表

select * from user_tables;

什么是单表查询？

在一张表内的查询操作，就叫单表查询

PLSQL软件

默认表EMP表介绍

2 查询（select）

要求：

  1. 必须是英文符号
  2. 养成习惯分号结尾
  3. 表名，字段名不区分大小写，内容区分大小写

2.1 基本查询

select * from 表名;

select * from emp;

select * from salgrade;

2.1.1指定字段查询

字段：每个表内容的标题

*代表全部字段，多个字段查询是，字段之间使用逗号隔开

select 字段 from 表名;

select ename,job,sal from emp;

2.1.2 条件查询（where）

select 字段 from 表名 where 条件;

— 条件：字符用单引号包裹，数字不需要

select ename,job from emp where job = ‘SALESMAN’;

select ename,sal from emp where sal > 2000;

— 工作是CLERK的人的工资

select sal,ename from emp where job = ‘CLERK’;

2.1.3 多个条件查询（and）

select 字段 from 表名 where 条件1 and 条件2;

— 工作是 CLERK 并且工资大于 1000 的人

select ename,sal,job from emp where job = ‘CLERK’ and sal > 1000;

— 有工资大于2000并且是部门10的人

— 有工资大于2000并且是部门10的人

2.1.4 Null值的判断

is null ： 值是空值

is not null ： 不是空值，0不是空值

select 字段 from 表名 where 字段 判断空值;

— 查询有奖金的人

select comm,ename from emp where comm is not null;

— 查询没有上级编号的人

select ename,mgr from emp where mgr is null;

2.1.5 别名（as）

给表或者字段取一个别名，方便我们记忆，as可以省略

select 字段 as 别名 from 表名 别名 …;

注意事项

给表取了别名，使用字段时要用别名来声明字段

select e.ename from emp e;

2.1.6 去重（distinct）

作用：去除重复值

select distinct(字段) from emp …; — 查询有哪些部门

2.1.7 排序（order by）

作用：对查询出来的数据进行排序

asc：升序，一般不加，默认升序

desc：降序

select 字段 from 表名 order by 字段 asc/desc …;

— 排序工资

select sal,ename from emp order by sal asc;

2.1.8 面对空值

默认是最大值，可以使用 nulls first 和 nulls last 调整空值的顺序

select 字段 from 表名 order by 字段 asc/desc nulls first/last …;

— 排序奖金

select comm,ename from emp order by comm asc nulls first;

2.1.9 模糊查询（like）

作用：部分匹配

_：一个下划线代表一个字符

%：代表全部字符

select 字段 from 表名 where 字段 like ‘值’ …;

— 查询名字是S开头的

select ename from emp where ename like ‘S____’;

select ename from emp where ename like ‘S%’;

2.1.10 算数运算符 （+-*/）

通常使用在字段中

select 字段+-*/ from 表名 …;

— 给每个员工涨 500 块钱工资 select 500 + sal 新工资,sal 旧工资 from emp;

2.1.11 比较运算符 （> = <= !=)

通常使用在条件里

!=：不等于

— 查询不是部门 10 的人

select ename,deptno from emp where deptno != 10;

2.1.12 逻辑运算符 （and,or,not）

and：与的关系，表并列，连接两个表达式，只有二者都成立菜返回结果

or：或的关系，连接两个表达式，只有一个成立就可以返回结果

not：非的关系，用在表达式之前，表示取反

— 查询来自部门 10 或者部门 20 的人的姓名

select ename,deptno from emp where deptno = 10 or deptno = 20;

— 查询不是来自部门 10 或者部门 20 的人的姓名

select ename,deptno from emp where not(deptno = 10 or deptno = 20);

select ename,deptno from emp where not deptno in (10,20); — 使用 in + or

2.1.13 区域条件（between and）

select 字段 from 表名 where 字段 between 条件1 and 条件2 …;

— 查询工资在1000~2000之间的员工

select ename,sal from emp where sal between 1000 and 2000;

2.2 函数

  • 通过函数名来使用一些特定的功能 sum：求和函数，函数是可以套用的，函数的变量位置不是单纯的只能放字段、字符串，函数之间是可以套用的
2.2.1 字符串函数
2.2.1.1大小写控制（upper、lower）

upper：转大写

lower：转小写

select lower(ename) 小写名字 from emp;

select upper(‘aaaa’) 大写字母 from dual;

2.2.1.2首字母大写（initcap）

select initcap(lower(ename)) 首字母大写 from emp;

2.2.1.3 字符串拼接（concat，||）

select concat(‘Dear’,ename) DearName from emp;

select ‘Dear’ || lower(ename) from emp;

2.2.1.4 字符串提取（sunstr）

select ename,substr(ename,3,2) from emp;

2.2.1.5 字符串查找（instr）

select ename,instr(ename,’I’,2) from emp;

— 截取字符串 Hello world！当中的 wo

select instr(‘Hello world!’,’wo’) from dual;

如果不加最后一个截取个数，返回的值是截取的是第几位

2.2.1.6 返回字符的长度（length、lengthb）

英文的字符等于字节数，中文的1：2

select length(sname) 字符数,lengthb(sname) 字节数 from student;

小练习

— 查找员工姓名是五个字母的员工 select length(ename) 长度,ename from emp where length(ename) = 5;

2.2.1.7 左右填充函数（ipad，rpad）

ipad：左填充

rpad：右填充

select lpad(ename,10,’*’) from emp; — 给 SMITH 右边填充 3 个星号 select rpad(ename,8,’*’) from emp;

2.2.1.8去除字符串前后的字符（trim）

使用from连接

select trim(‘*’ from rpad(ename,8,’*’)) 去除星号 from emp;

2.2.1.9 字符串替换（replace）

select replace(concat(‘Dear’,ename),’Dear’,’-‘) from emp;

2.2.2 数学函数
2.2.2.1 四舍五入（round）

select round(123.456) from dual;

截断（trunc）

select trunc(123.456) from dual; — 123

求余（mod）

mod(除数，被除数)

select mod(100,3) from dual; — 3

取整（ceil、floor）

ceil：向上取整，去除小数位整数位+1

floor：向下取整，去除小数位

select ceil(123.123),floor(456.789) from dual; — 124 456

2.2.2.2 日期函数

当前所用时间是 2023-07-25-13:50~

显示当前日期和时间（sysdate、systimestamp）

select sysdate,systimestamp from dual;

2.2.2.3 返回当前时间和日期

current_date、current_timestamp、localtimestamp

select current_date,current_timestamp,localtimestamp from dual;

2.2.2.4 给指定的日期添加月份（add_months）

select hiredate,add_months(hiredate,2) from emp;

2.2.2.5 当前月的最后一天（last_day）

select hiredate,last_day(hiredate) from emp;

2.2.2.6 抽取日期的单位（extract）

select extract(year from hiredate) year from emp;

select extract(month from hiredate) month from emp;

select extract(day from hiredate) day from emp;

select extract(hour from systimestamp) hour from dual;

select extract(minute from systimestamp) minute from dual;

select extract(second from systimestamp) second from dual;

2.2.2.7返回两个日期之间的月份（months_between）

select ceil(months_between(sysdate,hiredate)) months from emp;

2.2.2.8 返回下一个周几（next_day）

不是下一周是下一个

select next_day(sysdate,’星期五’) from dual;

2.2.3 转换函数
2.2.3.1 字符串转日期（to_date）

select to_date(‘2023-07-25′,’yyyy-mm-dd’) from dual;

2.2.3.2. 日期转字符串（to_char）

select to_char(sysdate,’yyyy’) year from dual; — 23

select to_char(sysdate,’mm’) months from dual; — 07

select to_char(sysdate,’dd’) day from dual; — 25

select to_char(sysdate,’day’) week from dual; — 返回当前星期几 星期二

2.2.3.3 字符串转数字（to_numbner）

select to_number(‘20230725’) from dual;

— 转有效数字

select to_number(‘$123.456′,’$999.9999’) from dual;

— 16进制转10进制

select to_number(’19f’,’xxx’) from dual;

2.2.3.4 通用函数

nvl

nvl(值1，值2) 如果值1为空返回值2

— 如果奖金为空，给他500奖金

select nvl(comm,500) from emp;

nvl2

nvl2(值1，值2，值3) 如果值1为空返回值3，否则返回值2

select nvl2(comm,comm+500,1500) 涨奖金,comm from emp;

nullif

nullif(值1，值2)，如果值1等于值2，返回null，反之返回值1

select nullif(500,null) from dual;

coalesce

coalesce(值1，值2，值3，….)返回第一个不为空的值

select coalesce(comm,deptno) from emp;

2.2.4 条件函数

case when

case when 值1 then 返回值1

when 值2 then 返回值2

…

else 其他值

end

— 判断员工的部门名称。如果是 10 我们叫 财务部

— 如果是 20 我们叫 研发部

— 如果是 30 我们叫 销售部

select emp.*,

case when deptno = 10 then ‘财务部’

when deptno = 20 then ‘研发部’

when deptno = 30 then ‘销售部’

else ‘其他部门’

end 部门名称

from emp;

decode

decode (条件，值1，返回值1，

值2，返回值2

…,

值n，返回值n，

其他值)

如果 条件 = 值1，输出返回值1

如果 条件 = 值2，输出返回值2

都不满足 返回其他值

— 判断员工的部门名称。如果是 10 我们叫 财务部

— 如果是 20 我们叫 研发部

— 如果是 30 我们叫 销售部

select emp.*,

decode(deptno,10,’财务部’,

20,’研发部’,

30,’销售部’,

‘其他部门’) 部门名称

from emp;

2.2.5 聚合函数（分组函数）

括号里面放字段

max() — 最大值

min() — 最小值

avg() — 平均值

count() — 数量

sum() — 求和

select max(sal),min(sal),avg(sal),count(sal),sum(sal) from emp;

count(*),count(1) 都代表总内容数，多少行数据

select count(*) from emp;

2.2.5.1 分组语句 （group by）

对指定的字段分组，比如部门有10，20，30，如果是 group by deptno，就是对部门分组，分成10一组，20一组，30一组

select 字段 from 表名 where 条件 group by 字段 …;

— 求每个部门的平均工资

select deptno,trunc(avg(sal)) 平均工资 from emp group by deptno;

注意事项

1. 出现在 select 后面的字段，如果不是在分组函数中，那么他必须同时出现在 group by 语句当中

2. 出现在 group by 后面的字段不一定出现在 select 后面

3. where 语句中不允许出现 group by

分组语句的条件（having）

用法与 where 一样，having 条件

区别：

1. having 服务对象是 group by，where 服务对象是字段

2. where 不能用分组函数，having 通过条件过滤分组函数

3. having 是在分组完成后执行，where 是在分组前执行，这也是为什么 where 不能服务 group by 的原因

— 分部门和职业统计员工的工资和，并且工资和 > 3000

1.group by deptno,job

2.having sum(sal) > 3000

3.deptno,job,sum(sal)

4.select deptno,job,sum(sal) from emp group by deptno,job having sum(sal) > 3000;

2.2.6 查询顺序

1. from table

2. where example

3. group by title

4. having

5. select

6. order by answer

3 连续查询

3.1 笛卡尔积

两个表的一种关联方式，将第一张表中的每一行都与第二张表中的每一行组合，生成一个新的表

举例：两个表A和表B

表A

表B

笛卡尔积后—> 表C

3.2 等值连接查询

我们从多张表中查询数据的时候，我们根据表与表之间的关联性来寻找对应的数据

— 我们查找 SMITH 的工作岗位的详细信息

— 先找到 SMITH

select ename from emp where ename = ‘SMITH’

— 找到 SMITH 的工作部门

select ename,deptno from emp where ename = ‘SMITH’

— 找到部门 20 的详细信息

select * from dept where deptno = 20;

— 使用笛卡尔积将两张表拼接

select * from emp,dept;

— 选择有效的字段，emp表的 ename字段 ，deptno字段，dept表的全部字段

select emp.ename,emp.deptno,dept.* from emp,dept;

— 只需要部门20，名字是SMITH

select e.ename,d.* from emp e,dept d where e.ename = ‘SMITH’ and d.deptno = 20; — 笛卡尔积 select e.ename,d.* from emp e,dept d where e.ename = ‘SMITH’ and d.deptno = e.deptno; — 等值连接

3.2 非等值连接查询

连接两个表时使用不等于运算符来比较两个表的列

— 查找成绩在60分以上，这门课的学分可以获得，计算每个学生总学分和统计姓名

select s.*,m.*,c.* from student s,mark m,course c;

select * from student; select * from mark; select * from course;

— 得到关联字段

select s.*,m.*,c.* from student s,mark m,course c where s.sid = m.sid and m.cid = c.cid;

— 不等值的条件

select s.*,m.*,c.* from student s,mark m,course c where s.sid = m.sid and m.cid = c.cid and cmark >= 60;

— 去除重复数据

select sname,sum(cval) from student s,mark m,course c

where s.sid = m.sid and m.cid = c.cid and cmark >= 60

group by sname,s.sid ;

3.3 自连接

一张表当多张表用

select a.*,b.* from dept a,dept b;

— 查询 emp 表中所有工资比部门平均工资高的员工信息

select * from emp;

select sal,ceil(avg(sal)) from emp group by deptno,sal having sal > avg(sal); — 错误 800 > 800/1

— 连接两张 emp 表

select e1.*,e2.* from emp e1,emp e2;

— 拿到需要的字段 select e1.sal,e1.ename from emp e1,emp e2;

— 等值连接

select e1.sal,e1.ename from emp e1,emp e2 where e1.deptno = e2.deptno;

— 去除重复数据

select e1.sal,e1.ename,ceil(avg(e2.sal)) from emp e1,emp e2

where e1.deptno = e2.deptno

group by e1.deptno,e1.sal,e1.ename;

— 添加条件

select e1.sal,e1.ename,ceil(avg(e2.sal)) from emp e1,emp e2

where e1.deptno = e2.deptno

group by e1.deptno,e1.sal,e1.ename having e1.sal > avg(e2.sal);

3.4 左外连接右外连接（left join，right join）

以左边或者右边的表为基准表查询数据，如果没有数据补null值，判断时添加on使用等值连接

create table testA (

tid number,

A_name varchar2(10)

);

insert into testA values(1,’A’);

insert into testA values(2,’B’);

insert into testA values(3,’C’);

create table testB (

tid number,

B_name varchar2(10)

);

insert into testB values(1,’A’);

insert into testB values(2,’B’);

insert into testB values(3,’C’);

insert into testB values(4,’D’);

— 删除表

DROP TABLE testA;

DROP TABLE testB;

select * from testA;

select * from testB;

— 左外连接

select * from testA left join testB on testA.tid = testB.tid

— 右外连接

select * from testA right join testB on testA.tid = testB.tid

3.5 内连接（inner join）

当我们想要将两个或者多个表中的数据进行连接时可以使用内连接，通过一个或者多个关联条件，它会返回两个表中匹配到的行，也就是说在连接表中存在匹配的行时才会返回结果

inner join 可以简写成 join

create table emp_test (

emp_no number,

emp_name varchar2(10),

dept_no number

);

insert into emp_test values(1,’张三’,1);

insert into emp_test values(2,’李四’,2);

insert into emp_test values(3,’王五’,2);

insert into emp_test values(4,’赵六’,3);

create table dept_test (

dept_no number,

dept_name varchar2(10)

);

insert into dept_test values(1,’人事部’);

insert into dept_test values(2,’技术部’);

insert into dept_test values(3,’财务部’);

select * from emp_test; select * from dept_test;

— 查询员工的姓名和所在部门的名称

select e.emp_name,d.dept_name from emp_test e,dept_test d

where d.dept_no = e.dept_no; — 笛卡尔积

select e.emp_name,d.dept_name from emp_test e inner join dept_test d on d.dept_no = e.dept_no; — 内连接

— 查询每个学生的姓名和平均分

select s.sid,ceil(avg(cmark)) from student s, mark m

where s.sid=m.sid group by s.sid — 笛卡尔积

select s.sid ,ceil(avg(cmark)) from student s inner join mark m on m.sid=s.sid group by s.sid — 内连接

比较笛卡尔积和内连接：

  • 内连接：只返回满足连接条件的结果集，可以过滤数据
  • 笛卡尔积：只是单纯的连接两个表的行，不会过滤出数据

优势

  1. 数据过滤：内连接会根据关联条件来过滤数据，只返回相关的行，减少了数据的查询速度
  2. 查询效率：内连接的查询效率高于笛卡尔积
  3. 资源占用：内连接只返回有效数据，所以资源占用小

举例

create table customers (

cid number,

cname varchar2(10),

clocal varchar(100)

);

insert into customers values(1,’张三’,’南京’);

insert into customers values(2,’李四’,’扬州’);

insert into customers values(3,’王五’,’徐州’);

insert into customers values(4,’赵六’,’苏州’);

create table orders (

oid number,

cid number,

odate varchar2(10)

);

insert into orders values(101,1,’07-01′);

insert into orders values(202,2,’07-02′);

insert into orders values(303,3,’07-03′);

insert into orders values(404,4,’07-04′);

select * from customers; select * from orders;

— 返回匹配数据

select cname,clocal,oid,odate from customers inner join orders on customers.cid = orders.cid; select cname,clocal,oid,odate from customers inner join orders就是笛卡尔积的

select cname,clocal,oid,odate from customers,orders

所以相对于笛卡尔积来说，少了一步，所以查询时间肯定是比笛卡尔积块

select cname,clocal,oid,odate from customers,orders where customers.cid = orders.cid;

4 高级查询、开窗函数、分页查询、TopN查询

4.1 随机查询

dbms_random.value()

— 产生 0 ~ 1 之间的随机数，可以为0，不能为1

select dbms_random.value() from dual;

— 产生 1 ~ 11 之间的随机数

select dbms_random.value(1,11) from dual;

— 产生 1 ~ 10 之间的随机整数，含有10

select trunc(dbms_random.value(1,11),0) from dual;

— 生成一个随机小写字母97 ~ 122

select chr(trunc(dbms_random.value(97,123),0)) from dual;

— 随机返回学生表的五条数据 — 得到一张顺序被打乱的新表

select * from student order by dbms_random.value();

— 使用 rownum 返回五条数据

select rownum r,s.* from (select * from student order by dbms_random.value()) s where rownum <= 5

4.2 子查询

4.2.1 什么是子查询？
  • 从一个表中查出来的部分数据当作另一个表的查询条件，分为单行子查询和多行子查询
4.2.2 单行子查询

查询出来的结果只有一行，精确的匹配某个值

— 查询工资最高的员工 — 找到最高的工资（5000）

select max(sal) from emp;

— 拿最高的工资找人，最高的工资（5000）当作条件

select ename,sal from emp where sal = (select max(sal) from emp)

— 查询学生，年龄比学号10005号学生大的其他学生的姓名

— 找到 10005 号学生的年龄（21）

select sage from student where sid = 10005;

— 比 21 大的其他学生的姓名

select sname from student where sage > 21;

select sname from student where sage > ( select sage from student where sid = 10005);

4.2.3 多行子查询

使用 in any all 三种运算符

4.2.3.1 in 运算符

用于判断某个值是否在子查询返回的结果集中，在子查询中返回的结果只要有一个值等于外层查询中的某个值，就会返回结果

1. 找到对应的值

2. 作为条件，里层和外层的值要对应

— 找出所有男生的成绩

— 先找到所有男生

select sid from student where ssex = ‘男’;

— 找出所有男生的成绩

select * from mark; select sid,cmark from mark where sid in (select sid from student where ssex = ‘男’);

4.2.3.2 多行子查询是可以无限嵌套的

— 找出所有男生的成绩

select sid,cmark from mark where sid in (select sid from student where ssex = ‘男’);

— 找出所有男生的成绩所对应的科目

select * from course;

select * from course

where cid in (

select cid from mark

where sid in (

select sid from student

where ssex = ‘男’))

— 找出科目所对应的老师

select * from teacher

where tid in (

select tid from course

where cid in (

select cid from mark

where sid in (

select sid from student where ssex = ‘男’)))

4.2.3.3 any运算符
  • 用于比较外层查询中的某个值与子查询返回的结果集中的任意一个值是否相等，在子查询中返回的结果只要有一个值与外层查询中的某个值相等，返回结果

— 查询年龄大于江苏任意一个学生年龄的其他地区的学生信息

— 查找江苏学生的年龄

select sage from student where snativeplace = ‘江苏’;

— 查询比江苏地区最小年龄大的学生的其他地区的学生信息

select sname,snativeplace,sage from student

where sage > any (select sage from student where snativeplace = ‘江苏’)

4.2.3.4 all运算符

用于比较外层查询中的某个值与子查询返回的结果集中的全部值是否相等，在子查询中返回的结果集中的所有值都与外层查询中的某个值相等，才会返回结果

— 查询年龄大于江苏地区所有学生年龄的其他地区的学生信息

select sname,snativeplace,sage from student

where sage > all (select sage from student where snativeplace = ‘江苏’) and snativeplace != ‘江苏’;

4.3 开窗函数

4.3.1 开窗函数over()
  • 语法：over(值1 order by 值2)，根据值1，进行区分，再分区内按照值2进行排序
  • 注意：over函数一般不单独使用，会和关键字进行配合
4.3.2 常用关键字
  1. row_number： 用于为结果集中的每一行分配唯一的序号。这个序号是根据 order by 子句定义的排序顺序来分配的。
  2. rank：用于为结果集中的每一行分配一个排名。如果有多个行具有相同的值，则被分配相同的排名，下一个排名将被跳过
  3. dense_rank：与rank相似，但是具有多行相同值，被分配相同的排名，并且下一个排名不会被跳过
  4. lead：用于获取结果中当前行之后的第n行值。可以使用 partition by 子句将结果集分成多个分区
  5. sum：用于计算指定列的总和。可以使用 group by 子句将结果集分组
  6. lag：用于获取结果中当前行之前的第n行值。
4.3.2.1 row_numbe关键字的使用

— 查询学生分数信息，并按照每个人的分数进行排序

select m.*,row_number()over(partition by m.sid order by m.cmark desc) 排名 from mark m

— 每个学生 前三名 的成绩

select * from ( select m.*,row_number()over(partition by m.sid

order by m.cmark desc) paiming from mark m) where paiming <= 3;

— 查询学生表，跳过表中的偶数行

— 根据 sid 分配

select row_number()over(order by sid) num,s.* from student s

— 跳过偶数行

select * from (

select row_number()over(order by sid) num,s.* from student s)

where mod(num,2) = 1;

4.3.2.2 rank关键字的使用

— 查询每个学生的成绩，并且为每个学生计算排名

select m.*,rank()over(partition by m.sid order by m.cmark desc) paiming from mark m

— 查询每个学生的成绩，计算每个学生的排名，并列排名并且合成一个位置

select m.*,dense_rank()over(partition by m.sid order by m.cmark desc) paiming from mark m

4.3.2.3 max和min的使用

— 查询最大年龄和最小年龄

select distinct(max(sage)over()),min(sage)over() from student;

4.3.2.4 sum的使用

— 求所有学生的年龄的连续求和、总合

— 一共18组，总合 366

select sum(sage) over(order by sid) 连续求和,sum(sage) over() 总和 from student;

— 分班级给学生的年龄求和、连续求和、总和，20岁以上的参与计算

select sclass,sname,sage, sum(sage)over(partition by sclass order by sid) 连续求和, sum(sage)over(partition by sclass) 总和

from student

where sage >= 20;

4.3.2.5 lag和lead关键字的使用

select sname,sage,

lead(sage) over(order by sage) 下一行,

lag(sage) over(order by sage) 上一行

from student

— 查询第三行以下的数据 row_number,rownum

select sname,sage,

row_number()over(order by sage desc) row_num,

lead(sage) over(order by sage desc) 下一行

from student

— 子查询

select *

from ( select sname,sage,

row_number()over(order by sage desc) row_num,

lead(sage) over(order by sage desc) 下一行

from student)

where row_num > 3;

4.4 分页查询

思考：如果表数据量特别大，想要一次性展现给用户，页面加载数据很多，导致查询速度很慢，体验很差，如何解决

解决：使用分页查询进行分页展示

— 语法 — 每页展示 m 条数据 查询第 n 页的数据

select * from (

select rownum r,t1.* from table1 t1(需要分页的表)

where rownum <= m * n)t2

where r > m * n – m

— 查询学生表的 10 ~ 15 行的数据

— 每页分 5 条数据，查询 第 3 页的数据

— m = 5,n = 3

select * from (

select rownum r,s.* from student s

where rownum <= 15)t

where r > 10;

4.5 TopN查询

是一种查询语句，可以返回指定数据集中的前N个符合条件的记录，通常，TopN用在数据分析和业务决策很有效

比如：mark表存储了学生的分数，我们可以使用TopN来返回前N名分数最高的学生记录

4.5.1 rownum

这是Oracle当中的一个“伪列”，它按照查询结果集中的行号为每一行分配一个唯一的值，我们可以使用rownum实现

注意：在使用rownum的时候，我们必须放在子查询中，并在外部查询中进行限制，否则会出现错误的结果

4.5.1.1.rownum对于等于某个值的查询条件

我们想使用rownum查询第一行数据，rownum = 1，查询第二行不能使用 rownum = 2，没有结果，rownum无法使用 = 连接大于 1 的数，如果你想实现 rownum = 2 这种效果，需要使用子查询，rownum要取别名

–查询学生表的第一条数据

select rownum r,student.* from student

where rownum = 1;

–查询学生表的第六条数据

select * from (select rownum r,s.* from student s)

where r =6;

4.5.1.2.rownum对于大于某个值的查询条件

查询大于某值的记录的时候，rownum > n（n是自然数）一般这种情况也是不行的，依然使用子查询

— 查询第一行以后的数据

select rownum r,student.* from student

where rownum > 1;

— 没数据 select * from (

select rownum r,s.* from student s)

where r > 1;

4.5.1.3.rownum对于小于某个值的查询条件

可以直接使用 rownum < n，用来查询前几行的数据

— 查询前十行的数据

select rownum r,student.* from student

where rownum <= 10;

4.5.1.4.rownum查询某个区间的数据

使用子查询

— 查询年龄从高到低，第 5 ~ 8 行的数据 — 按照年龄排序

select * from student order by sage desc

— 获取每一行

select rownum r,s.* from (

select * from student order by sage desc) s

— 查找 5 ~ 8 的数据

select * from (

select rownum r,s.* from (

select * from student order by sage desc) s)

where r between 5 and 8;

4.5.1.5.rownum和order by
  1. rownum 是获取数据的时候产生的序列号使用 order by 会打乱排序
  2. rownum 想获取排序后的内容，也要使用子查询，将排好的查询，作为子查询重新查询
  3. order by 是用来排序查询结果，只能升序或者降序，按照一个或者多个排序，不能指定区间（where）
  4. rownum 是一个伪列，只是用来限制查询结果的行数

5 事务、视图、数据类型、约束

5.1 什么是事务？

数据库操作的一个逻辑单位，一般由一个或者多个数据库操作组成。事务中的操作要么全部执行，要么都不执行，要么全部成功提交，要么全部失败并回滚，保证数据的一致性和完整性

5.2 事务控制

只要是DML（增删改）操作会自动启动事务。一旦开始了事务，所有的增删改操作都会被纳为是一个事务，直到事务被提交或回滚

特点：

  1. 一个事务内的所有操作一起被提交或回滚
  2. 提交或回滚后的其他事务与之前的事务没有任何联系
  3. 当事务被提交后，其他人才能看到事务提交后的结果

注意：

如果事务没有提交或回滚，其他人再次尝试执行相同的事务操作会导致锁定现象，无法完成事务。这时一旦事务上了锁，必须等待锁释放才能继续执行相同的操作

5.3 事务控制的操作

5.3.1 永久保存 commit

— 插入数据

insert into cour2 values(105,’历史’,64,1005);

— 更新数据

update cour2 set cid = 1001 where cname = ‘科学’;

— 插入数据

insert into cour2 values(106,’物理’,64,1006);

— 提交操作，并永久保存

commit;

5.3.2 回滚 rollback

— 可以回滚到事务开始前的样式

rollback;

— 创建保存点

savepoint a;

— 执行事务操作

delete from cour where tid = 1001

— 回滚到保存点

rollback to a

5.4 事务的特性

ACID，db-01

5.4.1 原子性

— 开启事务

BEGIN;

— 插入数据

insert into customertype (cid,ctype,explain) values(4,’铜卡会员’,’消费在30000元以上’);

–更新数据

update customertype set ctype = ‘一般客户’ where cid = 1;

— 事务结束

commit;

5.4.2 一致性

— 开启事务

BEGIN

— 插入数据

insert into customertype (cid,ctype,explain) values(5,’黑卡会员’,’消费在70000元以上’);

— 事务结束

commit;

END;

— 查询数据

select * from customertype;

5.4.3 隔离性

— 开启事务A

BEGIN TRANSACTION

— 在事务A 中修改数据

UPDATE customertype SET ctype = ‘yiban客户’ WHERE cid = 1;

commit;

END；

— 在事务B 中 修改数据

BEGIN TRANSACTION

update customertype set ctype = ‘普通客户’ where cid = 1;

commit;

END；

— 在事务A中查询数据

select * from customertype;

commit;

— 在事务B中查询数据

select * from customertype;

commit;

5.4.4 持久性

— 开启事务

BEGIN

— 插入数据

insert into customertype (cid,ctype,explain) values(6,’至尊会员’,’消费在100000元以上’);

— 事务结束

commit;

END;

— 插入完 数据一直存在

select * from customertype;

5.5 数据共享

脏读(Drity Read)：一个事务读取到另一个事务尚未提交的数据。 事务 A 读取事务 B 更新的数据，然后 B 回滚（RollBack）操作，那么 A 读取到的数据是脏数据。

不可重复读(Non-repeatable read)：一个事务中两次读取的数据的内容不一致。 事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。

幻读(Durability )：一个事务中两次读取的数据量不一致。 系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。 解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

事务的隔离级别

SQL 标准定义了四个隔离级别：

  • READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更
  • READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据
  • REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改
  • SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰

隔离级别

脏读

不可重复读

幻读

READ-UNCOMMITTED(读取未提交)

√

√

√

READ-COMMITTED(读取已提交)

×

√

√

REPEATABLE-READ(可重复读)

×

×

√

SERIALIZABLE(可串行化)

×

×

×

5.6 视图

5.6.1 什么是视图？

是数据库中一种虚拟的表，它由一个人或者多个的查询结果定义出来的，视图并不存储实际的数据，而是在访问时动态生成结果。

优点：

  • 可以封装查询操作，提供简化、重用、和安全性等优势
  • 使用视图可以将复杂的逻辑包裹起来，以便从视图里直接检索数据，就不用再次书写查询语句
5.6.2 创建视图

— 语法

create view 视图名 as

条件查询语句

— 创建视图 保存高工资的员工数据

create view highsal as

select empno,ename,job,sal

from emp where sal > 2500;

— 如果没有权限，切换到 sys 用户，授权

grant create view to scott;

— 查看视图

select * from highsal;

— 更新视图

update highsal set sal = sal * 1.1

— 插入视图

insert into highsal(empno,ename,job,sal)

values (7788,’Jack’,’SALESMAN’,3000)

— 删除视图

drop view highsal

5.6.3 表与视图的区别

表：

  1. 数据库中实际存储的数据对象，具有固定的结构和命名
  2. 表由行和列组成，可以直接存储数据
  3. 表可以包含索引、约束、触发器等数据库对象
  4. 对表可以执行数据的增删改查

视图

  1. 视图是数据库中虚拟的表，是基于查询结果出现的
  2. 不存储实际的数据，仅在动态生成结果
  3. 视图可以简化查询的操作，方便我们更快找到查询结果
  4. 可以做到和表一样的操作

主要区别

  1. 存储方式：表存储实际的数据，视图不是
  2. 结构定义：表是行和列的定义，视图是查询结果的定义
  3. 数据保持：表一旦数据完成增删改查永久保存，视图只有在访问时菜生成数据
  4. 数据修改：表的修改是永久的，视图只读的

5.7 数据类型

5.7.1 按照类型分类

1.字符串类型：char、varchar2、clob，用于存储文本数据，char和varchar2分别是定长和变长的字符串类型，clob是用于存储大量文本数据的类型

char：定长，10，helloxxxxx

varchar2：变长，10，hello

2.数值类型：number，用于存储数据类型，包括整数、小数等。可以指定精度和范围

3.日期类型：date、timestamp，用于存储日期和时间。date可以精确到秒，timestamp可以精确到毫秒

4.布尔类型：boolean，只有两个数据类型，一个是true一个是false

5.二进制类型：blob，存储图片、音频

6.不常用的：rowid、raw、long

5.7.2 数据类型注意事项

1.char 类型是固定长度，处理速度比varchar2快，字符填充不满的时候，需要使用 trim 把两边的空格去掉

2.varchar2一般用于英文和数字，nvarchar2一般用于中文和其他字符，我们通常还是使用varchar2

3.varchar2是可变长度，放入几个字符就是几个字符，不能超过设定的字符

4.number（4，2）整数占4位小数占两位，如果小数位数多，会自动四舍五入截取指定的位数

5.number默认是38位，放入 99.994 可以，99.995 不行

5.7.3 DDL
数据库定义语句
5.7.4 常用关键词
  • create：创建表、视图、索引
  • alter：修改表、列、约束
  • drop：删除表、视图、索引
  • revoke：撤销其他角色对某个对象的访问权限
5.7.5 创建表create table …

create table 表名(

字段1 类型（长度）,

字段2 类型（长度）,

….

);

— 语法建表

create table stu(

id number(5),

name varchar2(50),

gender char(3),

tel number(11)

);

— 子查询建表

create table 表1 as select 字段 from 表2

— 表1 根据 表2 创建的，两个表一模一样

create table stu1 as select * from student;

5.7.6 增加字段 alter … add

alter table 表名 add 字段 类型;

— 给 stu 表增加一个 age 字段

alter table stu add age number(3);

5.7.7 修改字段 alter modify / rename column

modify：修改数据类型

rename column：修改字段名

— 修改 number 为 varchar2

alter table stu modify id varchar2(10)

— 修改 id 为 sid

alter table stu rename column id to sid

5.7.7.1 删除字段 alter drop

— 删除 stu 表的 tel 字段

alter table stu drop column tel;

5.7.7.2 删除表 drop

— 删除 stu1 表

drop table stu1;

5.7.7.3 重命名表 rename

— 修改 stu 表名为 sttu

rename stu to sttu

练习

创建商品表，商品表中含有商品编号、商品名称、商品价格、商品说明这几个字段，创建完成以后，需要增加商品数量这个字段

表名：shop

商品编号：sp_id

商品名称：sp_name

商品价格：sp_price

商品说明：sp_text

商品数量：sp_num

create table shop (

sp_id number(10),

sp_name varchar2(50),

sp_price number(8,2),

sp_text varchar2(200)

);

alter table shop add sp_num number(20)

select * from shop

5.7.8 DML

数据库操作语言

常用关键字

select：查询

insert：插入

update：更新

delete：删除

merge：合并

upsert：更新数据，如果数据存在则更新，不存在则插入

replace：替换

truncate（trunce）：清空

5.7.8.1 插入

值要与字段对应，并且符合字段的属性

— 可以缺少值，值与字段相对应

insert into 表名(字段1，字段2，…) values (值1，值2，…)

— 不可以缺少值，有多少字段，就有多少值

insert into 表名 values(值1，值2，…)

— 子查询插入数据

insert into 表1 select 字段 from 表2 — 表2的数据插入给表1

— 举例

— 全字段插入

insert into shop(sp_id,sp_name,sp_price,sp_text,sp_num)

values(10000004,’男士运动鞋’,199.99,’男士网面夏季鞋’,9999)

— 不使用字段名插入

insert into shop values(10000002,’女士运动鞋’,199.99,’女士透气夏季运动鞋’) — 报错，因为缺少值 insert into shop values(10000002,’女士运动鞋’,199.99,’女士透气夏季运动鞋’,9999)

— 全字段插入时可以选择性插入数据，没有选择的字段为 null 值

insert into shop(sp_id,sp_name,sp_price,sp_text)

values(10000003,’男士运动鞋’,199.99,’男士网面夏季鞋’)

— 子查询插入数据

insert into sttu select sid,sname,ssex,sage from student;

5.7.8.2 更新

— 指定条件修改数据

update 表名 set 字段 = 数据 where 条件 — 如果不指定条件，默认全修改

— 修改张三的年龄为 22 岁

update sttu set age = 22 where name = ‘萧瑾’;

— 修改全部人的性别为 女

update sttu set gender = ‘女’

5.7.8.3 删除

— 指定条件删除数据

delete from 表名 where 条件

— 删除李四的数据

delete from sttu where name = ‘李四’

— 不加条件 默认全删除，和 truncate 一样的效果

delete from sttu truncate table sttu

5.8 约束

对插入的数据进行限制，比如：在姓名这个字段内不能为空、性别只能设置为男或者女、手机号码必须是11位

5.8.1 常见约束
  1. 主键约束 – primary key：用于唯一标识表中每一行记录的列，保证了该列的数据不重复并且不能为空
  2. 唯一约束 – unique：保证该列的值不重复，但是可以为空
  3. 外键约束 – foreign key ：将一个表中的列与另一个表中的列建立关联，保证了数据的一致性和完整性
  4. 选择约束 – check：在插入或者更新记录时，检查指定列的值是否符合指定条件。插入的数据，必须选择范围里面的
  5. 非空约束 – not null：保证该列的值不能为空
5.8.2 添加约束
5.8.2.1 建表时添加约束

create table employees(

employee_id number(5) primary key,

first_name varchar2(50) not null,

last_name varchar2(50) not null,

hire_date date not null,

salary number(10,2)

);

5.8.2.2 alter添加约束

— 给 sid 添加约束

alter table student add primary key(sid)

— 修改表字段时添加约束

alter table sttu modify name char(50) not null

— 使用 constraint 添加唯一约束

alter table sttu add constraint sttu_uq unique(name)

— 修改表数据的时候使用 constraint 添加选择约束

alter table student add constraint ssex_check check(ssex in(‘男’,’女’))

— 使用 alter 添加外键约束 和 级联删除（on delete cascade）

alter table mark

add constraint mk_su foreign key(sid) references student(sid) on delete cascade

alter table 目标表

add constraint 外键名 foreign key(目标字段) references 参考表(参考字段) on delete cascade

5.8.2.3 外键约束

在两个表之间建立连接，可以是一个列或者是多个列，一个表可以有一个或者多个外键

a，b两张表，a是主表，b是副表，b表的外键指向a表

如果要添加信息，必须先在a中添加，再去b添加，删除是相反的，先删除b再删除a

create table teach(

tid number primary key,

tname varchar2(50) not null,

tsex char(3) not null check(tsex in (‘男’,’女’)),

tphone number(11) not null unique

);

insert into teach values(1001,’张三’,’男’,12345678910);

insert into teach values(1002,’李四’,’女’,12345678911);

insert into teach values(1003,’王五’,’男’,12345678912);

insert into teach values(1004,’赵六’,’女’,12345678913);

select * from teach;

create table cour(

cid number primary key,

cname varchar2(50),

ctime number,

tid number,

constraint fkey_tid foreign key (tid) references teach(tid)

);

select * from cour

— 如果关联的主表中没有该 1005 tid 则数据插入失败

insert into cour values(101,’语文’,64,1005) — 失败

insert into cour values(101,’语文’,64,1001) — 成功

— 外键删除 — 先删除主表，报错

delete from teach where tid = 1001

— 应该先删除副表

delete from cour where tid = 1001

— 再删除主表

delete from teach where tid = 1001

5.8.2.4 级联删除

当一个表的数据被删除时，与之相关联的其他表中的记录也会被自动删除的操作，这种删除操作可以通过外键约束中的 on delete cascade 来实现

通常用于确保数据的一致性和完整性，可以同时删除多个相关记录

一般都是创建外键的时候创建级联删除，很少单独指定

create table cour1(

cid number primary key,

cname varchar2(50),

ctime number,

tid number,

constraint fkey_tid1 foreign key (tid) references teach(tid) on delete cascade

);

insert into cour1 values(101,’语文’,64,1001);

insert into cour1 values(102,’数学’,64,1002);

insert into cour1 values(103,’英语’,64,1003);

select * from cour1

— 可以直接删除主表内容，会连带着副表的内容也删除

delete from teach where tid = 1001

5.8.2.5 删除约束

— 删除主键约束

alter table sttu drop primary key

— 按照约束名删除

alter table sttu drop constraint sttu_uq

— 如果要删除唯一、检查、外键，需要添加 constraint 约束名

alter table sttu drop constraint SSEX_CHK

— 去除非空

alter table sttu modify name varchar2(50) null

6 索引、序列、PLSQL

6.1 什么是索引？

索引是在数据库中用于加快检索速度的数据结构。类似于书本的目录

为什么要用索引？

  1. 加快检索的时间：数据库允许系统直接定位到包含特定值的行，无需扫描整个表
  2. 提升查询的性能：因为有了索引在查询某些特定值时可以通过索引快速查询
  3. 支持唯一约束：通过唯一列上创建唯一索引，可以却表表中的值是唯一的
  4. 优化连接操作：作表之间的连接查询时，索引可以加快连接的速度，在连接字段上创建索引即可
  5. 加速分组和排序：索引可以在执行聚合函数和排序时提供更快的结果
  6. 获取内容的速度：数据库可以按照索引的顺序来读取数据，避免临时表的创建和额外的排序操作

6.2 访问数据库的方式

6.2.1 Oracle
  1. 顺序访问：全表扫描，在没有索引或者不满足创建索引的条件时，回对整个表进行扫描，逐个获取数据
  2. 索引访问：是通过遍历索引来快速定位和查找符合条件的数据
  3. 覆盖索引：是一种特殊的索引访问方式，当查询只需要从索引中获取数据时，可以直接利用覆盖索引，无需访问实际的行数
6.2.2 索引的分类
  1. B-Tree：B树索引，使用一种多层次的平衡树结构
  2. 唯一索引：确保索引列中的值是唯一的，可以通过主键约束来自动创建，也可以手动创建
  3. 聚簇索引：按照表的物理顺序来存储数据的索引，按照索引的键值排序，并且与索引的结构相关联，一个表只能有一个聚簇索引
  4. 非聚簇索引：独立于表的物理排序的索引，通过表中对应行的逻辑指针来实现数据的快速访问
6.2.3 索引的优缺点

缺点

  1. 占用存储的空间：需要额外的空间来存储索引，一旦包含多个索引时占有的空间会很多
  2. 增加写操作的成本：索引的创建需要你手动创建，当然有些约束会自动创建索引
  3. 更新频繁的表性能下降：对于经常修改数据的表，索引的更新会影响表的性能
  4. 索引选择不当：过多或者不必要的索引会降低查询的性能，增加了数据库的管理和维护的成本
  5. 索引的维护：面对多个索引，维护起来需要消耗大量成本
6.2.4 索引的创建

create index 索引名 on 表名 (字段1,字段2….) create index sid_uq on sttu (sid); — 定义主键约束或者唯一约束会自动创建索引 create table idex_table ( did number(4), name varchar2(50), constraint index_uq unique (did), constraint index_prim primary key (name) );

6.3 创建索引的原则（重中之重）

1）最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列

5）尽量的扩展索引，不要新建索引

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

6.3.1 创建索引时需要注意什么
  1. 非空字段应该指定列为NOT NULL，空值很难查询，除非你想存储NULL，所以应该用0、一个特殊的值或者一个空串代替空值；
  2. 取值离散大的字段（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
  3. 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

6.4 什么是序列？

是数据库中用于生成唯一数值序列的对象。他是一个独立的数据库对象，序列生成的数值通常用于为表的主键提供唯一的标识符，或者作用于其他唯一递增数值的列。序列生成的值不依赖于表中的数据，而是由数据库自身维护和生成。

6.5 序列的操作

6.5.1 创建序列

— 创建了一个序列，从 1 开始每次递增 1，nocache 参数表示不缓存序列的值，而是直接从数据库中获取

create sequence SequenceName

start with 1

increment by 1

nocache

— 删除序列

drop sequence SequenceName

6.5.2 举例

— 子查询建表

create table testseq as select sid,sname,sage,ssex from student;

select * from testseq delete from testseq

— 创建序列

create sequence test_sid;

— 使用序列添加数据

insert into testseq(sid,sname,sage,ssex)

values(test_sid.nextval,’张三’,20,’男’);

insert into testseq(sid,sname,sage,ssex)

values(test_sid.nextval,’李四’,19,’女’);

insert into testseq(sid,sname,sage,ssex)

values(test_sid.nextval,’王五’,21,’男’);

6.5.3 自定义序列

— 自定义序列

test_sid1 create sequence test_sid1

minvalue 10019 — 定义最小值

start with 10019 — 开始值

maxvalue 99999 — 序列最大值

increment by 1; — 规定自增值

6.5.4 查看序列

— 查看模当前序列值

select test_sid1.currval from dual;

6.6 什么是PL/SQL？

集中处理一些可以复制、反复执行的SQL操做

set serveroutput on

declare

声明部分

begin

主体内容

exception

捕获异常

end;

6.6.1 基本输出

begin

dbms_output.put_line(‘Hello World!’);

end;

/

6.6.2 变量输出

declare

i number;

— 定义变量 i 是 number 类型

begin

i := 30;

— 给 i 赋值 dbms_output.put_line(i); — 输出 i

end;

6.7 PL/SQL异常

异常是执行期间的错误，PL/SQL支持程序员在程序中使用 EXPECTION 块来捕获此类情况，并对错误情况采取适当的措施

6.7.1 异常处理的语法

when 异常名 then 处理代码块

6.7.2 异常列表

1. ACCESS_INTO_NULL：访问未定义的对象。

2. CASE_NOT_FOUND：CASE语句中没有匹配的WHEN条件，并且没有设置ELSE。

3. COLLECTION_IS_NULL：集合元素未初始化。

4. CURSOR_ALREADY_OPEN：游标已经打开。

5. DUP_VAL_ON_INDEX：唯一索引列上存在重复的值。

6. INVALID_CURSOR：对非法的游标进行操作。

7. INVALID_NUMBER：内嵌的SQL语句无法将字符转换为数字。

8. NO_DATA_FOUND：SELECT INTO语句未返回任何行，或者访问未初始化索引表的元素。

9. TOO_MANY_ROWS：执行SELECT INTO语句时，结果集超过一行。

10. ZERO_DIVIDE：除数为0。

11. SUBSCRIPT_BEYOND_COUNT：下标超过嵌套表或VARRAY的最大值。

12. SUBSCRIPT_OUTSIDE_LIMIT：使用嵌套表或VARRAY时，下标指定为负数。

13. VALUE_ERROR：赋值时，变量长度不足以容纳实际数据。

14. LOGIN_DENIED：当PL/SQL应用程序连接到Oracle数据库时，提供了不正确的用户名或密码。

15. NOT_LOGGED_ON：在没有连接到Oracle数据库的情况下尝试访问数据。

16. PROGRAM_ERROR：PL/SQL内部问题，可能需要重新安装数据字典和PL/SQL系统包。 17. ROWTYPE_MISMATCH：宿主游标变量与PL/SQL游标变量的返回类型不兼容。

18. SELF_IS_NULL：在null对象上调用对象方法时使用了对象类型。

19. STORAGE_ERROR：运行PL/SQL时，超出了内存空间。

20. SYS_INVALID_ID：无效的ROWID字符串。

21. TIMEOUT_ON_RESOURCE：Oracle在等待资源时超时。

6.7.3 处理异常

— 除数为 0 的异常

declare

i number;

begin

i:=1/0;

exception

when zero_divide then

dbms_output.put_line(‘不能除0’);

end;

— 唯一索引列上存在重复的值

declare

v_empno number := 7369; — 定义变量 v_empno 并赋值

begin

insert into emp (empno) values (v_empno); — 插入重复值

exception

when DUP_VAL_ON_INDEX then

dbms_output.put_line(‘存在相同的编号！’);

end; /

— CASE语句中没有匹配的WHEN条件，并且没有设置ELSE。

declare

v_grade char(1) := ‘D’;

begin

case v_grade

when ‘A’ then

dbms_output.put_line(‘优秀’);

when ‘B’ then

dbms_output.put_line(‘良好’);

end case;

exception

when CASE_NOT_FOUND then

dbms_output.put_line(‘没有匹配项’);

end;

/

6.7.4 用户输入

— 让用户输入 sid 查找学生

declare — 定义变量

v_sid number;

v_sname varchar2(30);

begin

v_sid :=&请输入sid; — & 提示输出，输入的 sid 存入进 v_sid 内

— 把 v_sid 当做条件放进查询语句中，where 实际上判断的是输入的 sid

— 将找到的 sname 通过 into 存入变量 v_sname 内

select sname into v_sname from student where sid = v_sid;

— 输出变量 v_sname

dbms_output.put_line(v_sname);

exception

— 捕获异常

when NO_DATA_FOUND then

dbms_output.put_line(‘没有该学生！’);

end;

6.8 循环

6.8.1 loop循环

loop

循环语句;

exit when 终止的条件;

循环条件必须更改;

end loop;

— 循环输出 1 ~ 指定值，用户数指定值

declare

v_end number;

v_start number;

begin

v_end :=&请输入结束的数字;

v_start := 1;

loop

dbms_output.put_line(v_start);

v_start := v_start + 1;

exit when v_start > v_end;

end loop;

end;

6.8.2 while循环

while(循环条件) loop

循环语句;

循环条件的改变;

end loop;

— 循环输出 1 ~ 指定值，用户数指定值

declare

v_end number;

v_start number;

begin

v_end :=&请输入结束的数字; v_start := 1;

— 判断开始的条件 输入的数字 大于 开始的数字 才开始循环，如果不满足条件结束循环 while(v_start <= v_end) loop

dbms_output.put_line(v_start);

v_start := v_start + 1;

end loop;

end;

6.8.3 for循环

for 变量名称 in 变量的初始值..结束值 loop

循环语句;

end loop;

— 循环输出 1 ~ 指定值，用户数指定值

declare

v_end number;

begin

v_end :=&请输入结束的数字;

for v_start in 1..v_end loop

dbms_output.put_line(v_start);

end loop;

end;

6.8.4 分支语句

6.8.4.1 if语句

if 条件 then

满足条件，执行这里;

end if;

— 判断能否谈对象

declare

age number;

begin age :=&请输入年龄;

if age >= 15 then

dbms_output.put_line(‘可以谈恋爱了’);

end if;

end;

6.8.4.2 if…else

declare

age number;

begin

age :=&请输入年龄;

if age >= 15 then

dbms_output.put_line(‘可以谈恋爱了’);

else

dbms_output.put_line(‘不可以谈恋爱’);

end if;

end;

6.8.4.3 if..elsif..else

declare

age number;

begin

age :=&请输入年龄;

if age >= 15 then

dbms_output.put_line(‘可以谈恋爱了’);

elsif

age <= 6 then — 不要忘了then

dbms_output.put_line(‘好好读书！’);

else

dbms_output.put_line(‘输入范围内数字’);

end if;

exception

when VALUE_ERROR then

dbms_output.put_line(‘数据不合法’);

end;

6.8.4.4 goto 标记点的用法

— 打印偶数

declare

i number;

j number; begin i :=0;

— 循环开始的默认值

loop

<> — 标记点

i := i+1; j :=&请输入;

if mod(i,2)=1 then — 通过求余函数判断是奇数还是偶数

goto fo; — 如果不是奇数就往下执行，如果是奇数重来

end if;

exit

when i > j; — 退出goto的条件

dbms_output.put_line(i);

end loop;

end;

7 函数、用户命令、游标、存储过程

7.1 什么是函数

作为编程范式的一个组成部分，一段可重复使用的代码块，接收参数，并返回一个具体的值

7.1.1 函数使用

函数定义

create [or replace] function 函数名 (参数 类型1,参数2 类型2,…)

return 返回值类型

is/as

[定义变量]

begin

代码块

return 结果;

exception

…

end;

  1. 函数的参数模式只能是 in 模式，可以省略 in
  2. 函数声明的时候，必须使用 return 加返回值类型
  3. return 的返回值类型只需要告诉类型，不需要定义长度 return varchar2 变量: 数据类型 varchar2(30)
  4. 函数的结果必须通过 return 返回出去，函数要有返回值

举例

— 无参函数

create or replace function sayHello

return varchar2

is begin return ‘Hello World!’;

end; select sayHello() from dual;

— 有参函数

create or replace function sayHy(sname in varchar2)

return varchar2

is

rec varchar2(50);

begin

if sname is null or sname = ” then

rec := ‘Hello!’;

else

rec := ‘Hello,’||sname;

end if;

return rec;

end;

select sayHy(”) from dual;

练习

— 根据员工的编号，计算员工的年收入（工资 + 奖金）* 12

create or replace function calculateyearlyIncome(eno in emp.empno%type)

return number

is

v_sal emp.sal%type;

v_comm emp.comm%type;

begin

select sal,comm into v_sal,v_comm from emp where empno = eno;

if v_sal is null then

v_sal :=0;

end if;

if v_comm is null then

v_comm :=0;

end if;

return (v_sal + v_comm) * 12;

end;

select e.*,calculateyearlyIncome(e.empno) as 年收入 from emp e;

— 传入时间，返回入职时间比这个时间早的所有员工的平均工资

create or replace function calculateAvgSal(hdate in emp.hiredate%type)

return number

is

avg_sal number;

begin

select avg(sal) into avg_sal from emp where hiredate < hdate;

return avg_sal;

end;

select calculateAvgSal(TO_DATE(‘2000-01-01′,’YYYY-MM-DD’)) as 平均工资 from dual;

7.2 查看所有用户

select * from all_users;

7.2.1 创建用户

需要管理员用户完成，Oracle用户分为三种身份

  1. normal：普通用户
  2. sysoper：数据库操作员，可以打开关闭数据库服务器、备份数据库、恢复数据、日志归档…
  3. sysdba：数据库管理员，管理数据库、管理用户

create user 用户名 identified by “密码”;

7.2.2 删除用户

drop user 用户名 cascade;

7.2.3 解锁用户

unlock：解锁

lock：锁定

alter user 用户名 account unlock;

7.2.4 修改密码

alter user 用户名 identified by “密码”;

7.2.5 权限

数据库管理权限

create session：登录权限

create table：创建表的权限

create index：创建索引的权限

create view：创建视图的权限

create sequence：创建序列的权限

7.2.6 数据操作权限

insert：插入数据的权限

delete：删除数据的权限

update：修改数据的权限

select：查询数据的权限

举例

— 赋予权限

grant 权限名 to 用户名;

— 收回权限

revoke 权限名 from 用户名;

7.2.7 角色

把很多权限组合成一个角色，然后将该角色赋给某个用户，那么这个用户就会拥有这个角色的权限

— 授权

grant 权限名 on 表名 to 用户1,用户2….;

—

grant select,insert,update on CCOL$ to jack,yxy;

— 收回权限

revoke 权限名 on 表名 from 用户1,用户2….;

有一些内置的角色，connect、dba可以将权限赋给用户

grant 内置角色名 to 用户名;

7.3 什么是游标

游标是SQL的内存工作区，由系统或用户以变量的形式进行定义

作用

用于临时存储从数据库中提取的数据块

7.3.1 分类

显示游标、隐式游标

举例

select into 一次只能从数据库 提取一行数据

declare

v_ename varchar2(50);

v_deptno number(10);

begin

v_deptno :=&plaseput;

select into v_ename from emp where deptno = v_deptno;

dbms_output.put_line(v_ename);

end;

/

7.3.1.1 隐式游标

以下内容会触发隐式游标

— 插入数据

insert into testseq(sid,sname,sage,ssex)

values(test_sid.nextval,’王五’,21,’男’);

— 更新数据

update testseq set sname = ‘王wu’ where sname = ‘王五’

— 删除数据

delete from testseq where sname = ‘王wu’

— into 单行查询

declare

v_ename varchar2(50);

v_empno number(10);

begin

v_empno :=&plaseput;

select ename into v_ename from emp where empno = v_empno; dbms_output.put_line(v_ename);

end;

/

属性

属性

返回值类型

含义

sql%rowcount

整形

代表DML语句成功执行的数据行数

sql%found

布尔型

值为true表是插入、删除、更新、单行查询等成功

sql%notfound

布尔型

值为true表是插入、删除、更新、单行查询等未找到所匹配的行

sdql%isopen

布尔型

在DML语句执行过程为真执行结束为假

用于判断DML语句的执行结果以及控制程序的流程

举例

sql%rowcount 获取 插入、更新、删除操作影响的行数

— 删除 student 表中年龄大于等于 20 的人

— 设置回滚点

savepoint a;

declare

row_count number;

begin

delete from student where sage >= 20;

— 设置隐式游标

row_count := sql%rowcount;

dbms_output.put_line(‘删除成功’||row_count||’行数！’);

end;

— 回滚

rollback to a;

sql%found 判断 DML 操作是否成功

— 查询 1005 号学生的姓名，判断是否查询成功

declare

s_sname varchar2(50);

begin

select sname into s_sname from student where sid = 10005;

if sql%found then

dbms_output.put_line(‘找到了：’||s_sname||’!’);

else

dbms_output.put_line(‘没找到！’);

end if;

end;

/

sql%notfound 判断 DML 操作是否找到任何一个匹配的行

— 查询 10018 号学生的姓名，判断是否查询成功

declare

s_sname varchar2(50);

begin

select sname into s_sname from student where sid = 10005;

if sql%notfound then

dbms_output.put_line(‘没找到！’);

else

dbms_output.put_line(‘找到了：’||s_sname||’!’);

end if;

end;

/

— 异常

declare

s_sname varchar2(50);

begin

select sname into s_sname from student where sid = 10019;

dbms_output.put_line(‘找到了：’||s_sname||’!’);

exception

when no_data_found then

dbms_output.put_line(‘没找到！’);

end;

/

sdql%isopen 检查隐式游标在 DML 操作时的状态

— 假设游标 c_student 查询了学生的姓名

— fetch 语句 into 子句 与查询的列数量匹配

— 将结果赋值给变量 s_sname ，并输出学生的姓名

— 不使用 sql%isopen 来判断游标是否处于打开状态

— 而是直接进入循环并使用 c_student%notfound 作为循环退出的条件

— 使用显示游标

declare

cursor c_student is

select sname from student;

s_sname varchar2(50);

begin

open c_student;

loop

fetch c_student into s_sname;

— 当没有值可以获取的时候退出循环

exit when c_student%notfound;

dbms_output.put_line(‘学生姓名：’||s_sname);

end loop;

dbms_output.put_line(‘未找到’);

close c_student;

end;

/

— 使用隐式游标

declare

cursor c_student is

select sname from student;

s_sname varchar2(50);

begin

open c_student;

loop

fetch c_student into s_sname;

— 当没有值可以获取的时候退出循环

exit when c_student%notfound;

dbms_output.put_line(‘学生姓名：’||s_sname);

end loop;

dbms_output.put_line(‘游标已关闭’);

if c_student%isopen then

close c_student;

end if;

end;

/

7.3.1.2显示游标

需要我们自己手动声明、打开、提取数据、关闭

— 声明游标

cursor 游标名 is select …

— 打开游标

open 游标名

— 提取数据

fetch 游标名 into [v1,v2….]

fetch 游标名 into v1

— 关闭游标

close 游标名

— 提取 SMITH 的数据

— 提取 student 表中的 10001 的姓名和籍贯

declare

v_sname varchar2(10);

v_snativeplace varchar(10);

— 声明游标

cursor stu_var is

select sname,snativeplace from student;

begin

— 打开游标

open stu_var;

— 提取数据

fetch stu_var into v_sname,v_snativeplace;

dbms_output.put_line(v_sname||’,’||v_snativeplace);

close stu_var;

end;

/

7.3.2 for循环游标

— 输出 empno、job、ename

declare

cursor e_emp is

select * from emp;

— 声明和emp表中字段、类型相同的变连

empInfo emp%rowtype;

cou number;

begin

— 循环游标e_emp，把值给empInfo

for empInfo in e_emp loop

— 值在传给cou

cou := e_emp%rowcount;

dbms_output.put_line(cou||’雇员编号：’||empInfo.empno||’，雇员工作：’||empInfo.job||’,雇员名字：’||empInfo.ename);

end loop;

end;

7.3.3 while循环游标

— 输出 empno、ename

declare

cursor mycur is select * from emp;

empInfo emp%rowtype;

begin

open mycur;

— 使游标指向下一行

fetch mycur into empInfo;

— 判断游标的这一行是否有数据

while(mycur%found) loop

dbms_output.put_line(‘雇员编号：’||empInfo.empno||’,雇员名字：’||empInfo.ename); fetch mycur into empInfo;

end loop;

end;

7.3.4 loop循环游标

— 输出 empno、ename

declare

cursor mycur is select * from emp;

empInfo emp%rowtype;

begin

open mycur;

loop

fetch mycur into empInfo;

exit when mycur%notfound;

dbms_output.put_line(‘雇员编号：’||empInfo.empno||’,雇员名字：’||empInfo.ename);

end loop;

end;

练习

— 一次性上涨全部人的工资。根据他所在的部门上涨工资

— 10 ：上涨 10%

— 20 ：上涨 20%

— 30 ：上涨 30%

— 虽然上涨工资，但是最高不能超过 5000，如果工资超过 5000 那么工资就是5000

— 通过查询判断是否成功 select * from emp;

declare

cursor c_emp is select empno,sal,deptno from emp;

v_empno emp.empno%type;

v_deptno emp.deptno%type;

v_sal emp.sal%type;

begin

for rec in c_emp loop

v_empno := rec.empno;

v_deptno := rec.deptno;

v_sal := rec.sal;

— 调整工资

CASE v_deptno

when 10 then

v_sal := least(v_sal + (v_sal * 0.1),5000);

when 20 then

v_sal := least(v_sal + (v_sal * 0.2),5000);

when 30 then

v_sal := least(v_sal + (v_sal * 0.3),5000);

else v_sal := v_sal * 1;

end case;

update emp set sal = v_sal where empno = v_empno;

end loop;

commit;

end;

7.4 什么是存储过程

封装了一段或者多段sql语句的pl/sql代码块

存储过程的优点

  1. 简化复杂操作：将很多条的sql语句封装为一个独立的单元，使用时只需要去调用该单元即可
  2. 增加数据的独立性：将数据库基础数据和程序隔离开，基础数据结构变化时，只需要存储过程不需要修改原代码
  3. 提高安全性：减少了执行多条sql语句报错
  4. 提高性能：只需要编译一次即可运行多条sql语句

存储过程的参数

  • in：定义输入参数，用于传递参数给存储过程
  • out：定义输出参数，用于从存储过程获取参数
  • in out：定义输入输出参数
7.4.1没有参数的存储过程

— 给指定的编号年龄添加2岁

— stu_pro 是存储过程名

create or replace procedure stu_pro

is

begin

update student set sage = sage + 2 where sid = 10001;

commit;

dbms_output.put_line(‘修改成功！’);

end;

— 调用存储过程

begin

stu_pro;

end;

— 查询结果

select * from student;

7.4.2 有参数的存储过程

— 指定人添加指定年龄 — 定义存储过程并且传入两个参数 var_1 和 var_2

create or replace procedure pro_stu(var_1 in varchar2,var_2 in number)

is

begin

update student set sage = sage + var_2 where sname = var_1;

commit;

dbms_output.put_line(var_1||’年龄增加了’||var_2||’岁！’);

end;

declare

begin

pro_stu(‘李四’,2);

end;

7.4.3 out 和 in out参数的存储过程

create or replace procedure pro_out

(var_1 in out number,var_2 out student.sname%type,var_3 out student.sage%type)

is

begin

select sname,sage into var_2,var_3 from student where sid = var_1;

end;

declare

ex_var_1 number;

ex_var_2 student.sname%type;

ex_var_3 student.sage%type;

begin

ex_var_1 :=&请输入sid;

pro_out(ex_var_1,ex_var_2,ex_var_3);

dbms_output.put_line(‘学号为：’||ex_var_1||’，姓名是：’||ex_var_2||’,年龄是：’||ex_var_3);

end;

7.4.4 删除存储过程

drop procedure 存储过程名

函数和存储过程的区别

  1. 函数主要用于返回计算结果，存储过程执行sql语句
  2. 关键字不一样，函数function，存储过程是procedure
  3. 存储过程可以由输入输出的参数，函数只有输入的参数
  4. 函数有返回值，存储过程没有
  5. 存储过程调用在plsql的代码块里，只能在sql语句

8 数据字典、数据库设计、触发器、in和existi

8.1 什么是数据字典？

提供了对数据结构和对象元数据信息的查询和访问，帮助你了解和管理数据库的各个方面

举例

— 查询 scott 用户下所有表

select TABLE_NAME from all_tables where owner = ‘SCOTT’;

— 查询 emp 表中所有的字段

select * from all_tab_columns where TABLE_NAME = ‘EMP’;

— 列出 emp 表的 索引列

select * from all_ind_columns where TABLE_NAME = ‘EMP’

— 列出 emp 表的 约束

select * from all_constraints where TABLE_NAME = ‘EMP’

8.2 什么是数据库三大范式？

第一范式：属性不可再分

第二范式：在第一范式的基础上，属性完全依赖于主键

第三范式：在第二范式的基础上，属性不依赖于其他非主键属性

举例

第一范式

以上内容就不符合，联系方式字段还可以再分

修改

第二范式

互相之间都可以作为主键并且互相依赖，这是违背第二范式

修改

保证每张表只有一个主键依赖

第三范式

其他的属性不可以当主键，只有一个属性可以当作主键

举例

— 建表 — 建一个订单表，含有订单内容的信息

CREATE TABLE orders1(

order_id NUMBER PRIMARY KEY,

customer_id NUMBER,

order_date DATE,

total_amount NUMBER,

CONSTRAINT fk_orders_customer

FOREIGN KEY(customer_id)

REFERENCES customers1(customer_id)

);

— 建一个客户表，含有客户信息

CREATE TABLE customers1(

customer_id NUMBER PRIMARY KEY,

customer_name VARCHAR2(100),

customer_email VARCHAR2(100),

customer_phone VARCHAR2(100)

);

修改

查看是否存在能够单独当主键的列或者会造成多个数据冗杂的列，需要单独拎出来建表

— 建一个订单表，含有订单内容的信息

CREATE TABLE orders1(

order_id NUMBER PRIMARY KEY,

customer_id NUMBER,

order_date DATE,

CONSTRAINT fk_orders_customer

FOREIGN KEY(customer_id)

REFERENCES customers1(customer_id)

);

— 建一个客户表，含有客户信息

CREATE TABLE customers1(

customer_id NUMBER PRIMARY KEY,

customer_name VARCHAR2(100)

);

— detail

CREATE TABLE detail1(

order_id NUMBER,

item_id NUMBER,

item_quantity NUMBER,

item_price NUMBER,

CONSTRAINT pk_order_detail

PRIMARY KEY (order_id,item_id),

CONSTRAINT fk_order_detail

FOREIGN KEY(order_id)

REFERENCES order1(order_id),

CONSTRAINT fk_order_detail

FOREIGN KEY(item_id)

REFERENCES item1(item_id)

);

— item

CREATE TABLE item1(

item_id NUMBER PRIMARY KEY,

item_name VARCHAR2(100),

item_description VARCHAR2(100),

item_price NUMBER

);

8.3 什么是触发器？

本身是一种数据库的对象，用于在指定的事件发生的时候会自动执行一段plsql代码

功能

  1. 允许/限制对表的修改
  2. 自动生成派生列，自增字段
  3. 强制保证数据的统一性
  4. 提供审计和日志记录
  5. 防止无效的事务处理，为了避免锁的发生
  6. 启用复杂的业务逻辑

语法

create or replace trigger 触发器名

{before | after} {insert | update | delete} o

n 表名

[for each row]

begin

plsql代码

end;

触发时间：{before | after}

指明触发器何时执行

before：在数据库操作之前

after：在数据库操作之后

触发事件：{insert | update | delete}

指明哪些数据库的操作会触发此触发器

for each row：表示触发器为每一行数据执行一次，如果省略此选项触发器只会执行一次

举例

— 更新学生表之前触发，限制不允许在周末修改表

create or replace trigger auth_stu

before insert or update or delete

on student

begin

— 这里添加的是判断条件

if (to_char(sysdate,’DY’) = ‘星期二’) then

raise_application_error(-20600,’不能修改’);

end if;

end;

— 在下午两点前不允许插入数据

create or replace trigger time_stu

before insert on test_log

for each row

declare

current_time timestamp;

begin

current_time := systimestamp;

if current_time < timestamp '2023-08-01 14:00:00' then

raise_application_error(-20001,’不能插入’);

end if;

end;

我们对 test1 执行操作，将记录保存在 test_long内

create table test1(

t_id number(4),

t_name varchar2(20),

t_age number(2),

t_sex char

);

create table test_log(

l_user varchar2(15),

l_type varchar2(15),

l_date varchar2(30)

);

— 对test1 进行操作，将操作日志保存在 test_log 内

create or replace trigger test1_log

after delete or insert or update on test1

declare

v_type varchar2(15);

begin

if inserting then

v_type := ‘insert’;

dbms_output.put_line(‘已记录’);

elsif updating then

v_type := ‘update’;

dbms_output.put_line(‘已记录’);

elsif deleting then

v_type := ‘delete’;

dbms_output.put_line(‘已记录’);

end if;

insert into test_log

values(user,v_type,to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’));

end;

select * from test1

select * from test_log

insert into test1 values(103,’杨芯叶’,19,’男’);

update test1 set t_age = 20 where t_age = 19;

delete test1 where t_id = 103

8.3 exists

逻辑计算符，exists 用于判断子查询返回的结果是否为空，如果不为空 exists 成立，主sql语句会执行，反之不执行

举例

— 如果部门名称中含有字母A，则查询所有员工的信息

select * from emp where exists (

select * from dept where ename like ‘%A%’ and deptno = emp.deptno)

not exists

— 如果平均工资不小于 1500 的部门信息，则查询所有部门信息(not exists)

select * from dept

where not exists (

select deptno from emp

where deptno = dept.deptno

group by deptno

having avg(sal) < 1500)

8.5 In

逻辑运算符，用于判断一个值是否存在与子查询的结果集中，如果存在条件成立，主SQL语句执行

如何选择in和existi

假设B表做子查询

  1. A表有10000条记录，B表有1000000条记录，那么会遍历10000 * 1000000次，效率很差，exists
  2. A表有10000条记录，B表有100条，in比较快

结论

  1. 如果 子表 比 父表的数据多，用 exists，反之用 in
  2. 此时主查询有索引，in比较快
  3. not in 和 not exists，not exists 最快

练习

— 返回无论是经理还是非经理，都没有下属的员工信息(not in)

select * from emp e

where e.empno not in (

select distinct(mgr) from emp)

— 返回至少在一个职位上有员工的部门名称和地点

select d.dname,d.loc from dept d

where exists (

select 1 from emp e where e.deptno = d.deptno)