数据库基础教程(Oracle)

04-11 757阅读

1 数据库

1.1 什么是数据库？

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

数据库：放数据的仓库，数据化

我们口中的数据库：

是数据库管理系统和数据库组成

仓库管理系统和仓库

1. 1.1 数据的发展历史

生活中的存放数据的方式

笔记本、手机、竹筒、计算机文档、表格、数据库

手工管理：容易丢、修改困难、数据量多不好处理、不方便查找
文档文件：使用方便、什么都要面面俱到、电脑不能坏、查找不方便
数据库：弥补了大部分缺点，对使用人群有限制

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

1.2 数据库的特性

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

1.2.1 数据库表之间的关系

一对一：每个员工只有一个工作时间
一对多：一个顾客ID可以对应很多个订单ID，但是一个订单只能有一个顾客
多对多：很多菜可以对应一个顾客，很多顾客可以对应很多菜

1.2.2 数据库的分类

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

常见的：Oracle、MySQL、SQL serve...

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

常见的：Mong DB、DB2、Redius...

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

数据一致性：使用ACID来管理数据，保证了数据的完整性
结构化：采用的是表格化的方式来管理数据，结构清晰

可靠性：有更成熟的查询语句(SQL)

1.2.3 什么是SQL？

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

分类：

DQL：数据查询语句，查询数据，关键词：select
DML：数据操作语句，增删改数据，关键词：insert、update、delete
DDL：数据定义语句，创建数据库的表、索引、视图、游标、
序列，关键词：create、define、alter、drop
DCL：数据控制语句，用于对用户的授权，关键词：grant、alter、revoke
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）

要求：

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

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的人

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 比较运算符（> = 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 -- 内连接

比较笛卡尔积和内连接：

内连接：只返回满足连接条件的结果集，可以过滤数据

笛卡尔积：只是单纯的连接两个表的行，不会过滤出数据

优势

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

举例

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 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 常用关键字

row_number：用于为结果集中的每一行分配唯一的序号。这个序号是根据 order by 子句定义的排序顺序来分配的。
rank：用于为结果集中的每一行分配一个排名。如果有多个行具有相同的值，则被分配相同的排名，下一个排名将被跳过
dense_rank：与rank相似，但是具有多行相同值，被分配相同的排名，并且下一个排名不会被跳过
lead：用于获取结果中当前行之后的第n行值。可以使用 partition by 子句将结果集分成多个分区
sum：用于计算指定列的总和。可以使用 group by 子句将结果集分组
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 = 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 - m

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

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

-- m = 5,n = 3

select * from (

select rownum r,s.* from student s

where rownum 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

-- 查询前十行的数据

select rownum r,student.* from student

where rownum 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 表与视图的区别

表：

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

视图

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

主要区别

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

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 常见约束

主键约束 - primary key：用于唯一标识表中每一行记录的列，保证了该列的数据不重复并且不能为空
唯一约束 - unique：保证该列的值不重复，但是可以为空
外键约束 - foreign key ：将一个表中的列与另一个表中的列建立关联，保证了数据的一致性和完整性
选择约束 - check：在插入或者更新记录时，检查指定列的值是否符合指定条件。插入的数据，必须选择范围里面的
非空约束 - 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 什么是索引？

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

为什么要用索引？

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

6.2 访问数据库的方式

6.2.1 Oracle

顺序访问：全表扫描，在没有索引或者不满足创建索引的条件时，回对整个表进行扫描，逐个获取数据
索引访问：是通过遍历索引来快速定位和查找符合条件的数据
覆盖索引：是一种特殊的索引访问方式，当查询只需要从索引中获取数据时，可以直接利用覆盖索引，无需访问实际的行数

6.2.2 索引的分类

B-Tree：B树索引，使用一种多层次的平衡树结构
唯一索引：确保索引列中的值是唯一的，可以通过主键约束来自动创建，也可以手动创建
聚簇索引：按照表的物理顺序来存储数据的索引，按照索引的键值排序，并且与索引的结构相关联，一个表只能有一个聚簇索引
非聚簇索引：独立于表的物理排序的索引，通过表中对应行的逻辑指针来实现数据的快速访问

6.2.3 索引的优缺点

缺点

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

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 创建索引时需要注意什么

非空字段应该指定列为NOT NULL，空值很难查询，除非你想存储NULL，所以应该用0、一个特殊的值或者一个空串代替空值；
取值离散大的字段（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次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 = 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 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;

函数的参数模式只能是 in 模式，可以省略 in
函数声明的时候，必须使用 return 加返回值类型
return 的返回值类型只需要告诉类型，不需要定义长度 return varchar2 变量: 数据类型 varchar2(30)
函数的结果必须通过 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

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

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

avg_sal number;

begin

select avg(sal) into avg_sal from emp where hiredate

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用户分为三种身份

normal：普通用户
sysoper：数据库操作员，可以打开关闭数据库服务器、备份数据库、恢复数据、日志归档...
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代码块

存储过程的优点

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