前言
本文包括Oracle数据库的绝大部分的知识的概要汇总,包含代码部分,全篇超过3w字,如果读者在学习相关内容有不理解的地方,可以将本文内的笔记结合百度上的定义,理解各种知识点(我感觉笔记对照百度的话,我自己理解起来没什么难度),也可以直接将本文作为Oracle数据库的学习笔记(不作商用就行),全文内容仅供学习参考。
此外,如果在学习过程中发现问题且无法解决,可以在留言区留言,(万一有好心大佬愿意帮忙呢?)毕竟数据库这玩意一出问题不是表就是数据库连接,我一个初学者也不一定会。
学完Oracle数据库,笔记前前后后记了有几万字,电脑配置也不足了,担心哪天更新的时候弄丢了,就把自己做的思维导图的大纲导出来做了一份笔记汇总,以防万一。
下面这玩意是思维导图,每个框体内的笔记在文中都有具体写出来。
目录
Oracle数据库
概念
概念
Oracle是一种关系型数据库
关系型:
1.以二维表格的形式存储数据
2.表和表之间可以建立关系
常见版本
8i ; 9i internet 走向网络
10g ; 11g grid 网格
12c cloud 云
主要结构
库——表空间——用户——表——数据
只有一个库
常见关系型数据库
Oracle数据库:特点:性能好;价格贵;安全性高;一般大公司喜欢用
MySQL数据库: 特点:性能比不上oracle;开源(开放源代码)、免费(6.0之前);一般中小型公司使用
SQL server数据库: .net(C#)语言无缝衔接
DB2:一般只使用在银行系统中
用户相关操作
1.oracle数据库的安装
(1)解决本地电脑和虚拟机之间不能相互传文件的问题 安装VMwareTools
(2)安装过程见视频
(3)验证是否安装成功
用户名:system
密码:123456
主机字符串:orcl
2.oracle数据库的登录和登出
(1)登录
cmd—>sqlplus system(用户名)/123456(密码)
登录成功:开头变成SQL>
(2)查找表格:select * from tabs;
(3)退出登录:
//了解
①右上角点×(不推荐)
②使用quit命令
③使用exit命令
3.使用远程工具连接oracle
(1)需要安装jdk1.8
(2)使用远程工具连接
起名规则:oracle + 用户名
端口:1521
服务名:orcl
(3)远程连接工具的配置
显示行号、修改字体大小:首选项—>代码编辑器—>行装订线/字体
用户相关说明
sys:超级管理员 oracle工程师
system:系统管理员
dba数据库管理员
scott:普通用户
密码一般默认为:tiger ——我们使用的用户
操作时使用自己创建的数据库
Oracle相关的服务
1.服务的概念
运行在电脑上的程序
2.怎么查看电脑的服务
(1)此电脑—>右键选择管理—>服务与应用程序—>服务
(2)cmd—>services.msc
3.oracle相关服务
查找方式:单击任意一个服务,在键盘中敲入服务首字母,系统自动索引至列表中首字母相同的第一个服务
OracleDBSonsoleord 和控制台相关的服务(不重要,可以不启动)
OracleJobSchedulerORCL 和定时器相关的服务(不重要,可以不启动)
OracleOraDb10g_home1iSQL*Plus 和sqlplus相关的服务(不重要,可以不启动)
OracleOraDb10g_home1TNSListener 监听器的服务(重要,需要启动)
OracleServiceORCL 数据库主服务(重要,需要启动)
SQL语言
概念
1.sql语言:
所有的关系型数据库的通用语法(规则)
2.方言:
每一种关系型数据库特有的规则
3.sql语言的规则
(1)sql语言多个单词之间使用空格隔开
(2)sql语句可以是单行的,也可以是多行的
(3)sql语言不区分大小写
4.sql语言的注释
(1)单行注释
~ 注释的内容
(2)多行注释
/* 注释的内容 */
分类
C —> D —> M —> Q
库 —> 表 —> 数据(增删改) —> 数据(查)
-
DCL
管理数据库的访问权限
要求:必须使用system或者sys账户来操作 选中需要运行的代码,再点击运行(需要运行什么选择什么)
-
创建表空间
--创建一个表空间 iweb501tp create tablespace iweb501tp datafile 'C:\work\0426\iweb501tp.dbf' size 100m autoextend on --自动扩容 next 10m;
-
创建一个用户
格式:
-
CREATE USER <user_name> [profile "DEFAULT"] identified by "<password>" [default tablespace "USERS"]
--创建一个名为lisi的用户 create user lisi identified by "1234"--设置密码为1234 default tablespace iweb501tp;--挂在iweb501tp的表空间内
创建完用户,使用工具连接,发现问题:没有权限登录
-
给用户授权
3.给用户授权 格式:
-
GRANT <权限列表> to <user_name>;
--创建一个名为lisi的用户 create user lisi identified by "1234"--设置密码为1234 default tablespace iweb501tp;--挂在iweb501tp的表空间内
-
-
DDL
用来操作表的 操作:crud 增删改查
-
create创建新增
-
格式:
CREATE TABLE <table_name> ( column1 DATATYPE [NOT NULL] [PRIMARY KEY], column2 DATATYPE [NOT NULL], ... [constraint <约束名> 约束类型 (要约束的字段) ... ] )
--创建一个学生表 create table stud1( id number, name varchar2(20) ); --创建一个学生比啊,no、name、score、birthdady create table stu2( no number(5), name varchar(20), score number(3,1), birthday date );
复制表: 格式:CREATE TABLE <table_name> as <SELECT 语句>
--复制表stu2 包括表结构和表数据 create table stu3 as select * from stu2; --复制表stu2 只复制表结构,不复制表数据 create table stu4 as select * from stu2 where 1=2;
where 1=2指永远不成立的等式,使数据不被复制。
-
retrieve查询
-
--查看表的结构 desc stu1;
-
update修改
(1)添加表字段 ALTER TABLE <table_name> ADD (字段1 类型 [NOT NULL], 字段2 类型 [NOT NULL] .... );
-- 在stu1中添加一个sex性别列,age年龄列 alter table stu1 add( sex varchar2(4), age number )
(2)修改表中字段 ALTER TABLE <table_name> modify(字段1 类型, 字段2 类型 .... );
-- 修改stu1表中的age类型为number(4),把sex改成varchar2(6) alter table stu1 modify( sex varchar2(6),age number(4) );
(3)修改表中字段名 ALTER TABLE <table_name> rename(字段1, 字段2 .... );
-- 修改字段名称sex——>gender alter table stu1 rename column sex to gender;
(4)删除表中字段 ALTER TABLE <table_name> drop(字段1, 字段2 .... );
-- 删除stu1表中的gender字段和age字段 alter table stu1 drop (gender,age);
(5)修改表的名称 RENAME <table_name> to <new table_name>;
-- 修改表的名称 rename stu1 to stu;
-
delete删除
删除表 DROP TABLE <table_name>;
-- 删除表stu3 drop table stu3;
-
总结 create drop alter desc
-
-
-
DML
操作数据(增删改)
-
create增加
insert语句
-
添加单条数据:
-
格式:
-
INSERT INTO table_name (column1,column2,...) values ( value1,value2, ...);
-
注意事项:
-
1.给values后的字符串类赋值时,只能使用单引号 ‘ ’
-
2.修改时,values的数量要对应,字段属性要对应
-
3.date类赋值时需要符合相应格式
insert into stu2(no,name,birthday) values(104,'李四','26-4月 -24');-- 不推荐 insert into stu2(no,name,birthday) values(105,'王五',sysdate);-- 推荐 sysdate-系统当前时间 --向stu2表中添加数据(给所有字段) insert into stu2(no,name,score,birthday) values(106,'赵六',96.6,sysdate); insert into stu2 values(106,'赵六',96.6,sysdate); --若表名后没有小括号,则默认给所有字段赋值
添加多条数据: 格式: INSERT INTO <table_name> <SELECT 语句>
-- 复制表数据(注意表的结构一致) create table stu3( no number(5), name varchar(32), score number(3,1), birthday date ); -- 将stu2表中的数据插入到stu3中 insert into stu3 select * from stu2; -- 将stu2表中no号小于3的插入到stu3中 insert into stu3 select * from stu2 where no<3;
另:复制表也可以完成相关要求
-
-
-
-
update修改
格式: UPDATE table_name set column1=new valuecolumn2=new value,... WHERE <条件>
-- 修改stu2表,将no=103的学生分数改为93.3 update stu2 set score=93.3 where no=103; -- 修改no=104的学生 分数=94.4 birthday=当前时间 update stu2 set score=94.4,birthday=sysdate where no=104; -- 不加where的修改——直接修改所有数据 -- ##!!!高危操作!!!## update stu2 set score=88.8; -- 修改score=91.3的李四的no=5 update stu2 set no=5 where name='李四' and score=91.3;
-
delete删除
DELETE FROM <table_name> WHERE <条件>
-- 删除stu2表中no=104的学生信息 delete from stu2 where no=104;
-
与truncate的区别
删除表中所有数据的两种方式的区别
-- 删除表中所有数据 truncate table stu3; delete from stu2;
-
delete:一条一条删,数据可以恢复(支持回滚)
-
truncate:把整张表直接删除,然后再创建一张和以前结构一模一样的新表(不支持回滚)
-
-
总结
DDL: create drop alter desc
-
DML: insert delete update select
-
-
DQL(最重要)
操作数据(查询)(单表查询)
-
格式: SELECT [DISTINCT] <column1 [as new name] ,columns2,...> FROM <table1> [WHERE <条件>] [[GROUP BY ] [[HAVING <条件>] [[ORDER BY [ASC|DESC]]
-
分析:[]表示可选
-
简单查询
前提:解锁scott用户,使用scott用户下面的表,作为查询表 1.查询单个字段
-- 查询emp表所有员工的姓名 select ENAME from emp;
2.查询多个字段
-- 查询emp表中所有员工的编号,姓名,领导编号 select EMPNO,ENAME,MGR from emp; -- 多个字段之间逗号隔开
3.查询所有字段
-- 查询emp表中所有员工的所有信息 select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from emp; select * from emp; -- * 表示所有 但凡使用 * 效率都偏低(因为*底层是全表扫描,不走索引)
-
去重查询
关键字:distinct
-- 查询emp表中的部门标号,去重 select distinct deptno from emp; select distinct mgr,deptno from emp; -- distinct后面跟多个字段,把多个字段作为一个整体
-
计算查询
计算方式:四则运算(加减乘除) 注意:null参与四则运算,结果为null 解决方案:使用nvl函数
-- 计算员工表中的所有员工的总收入(sal+comm) select ename,sal,comm,sal+nvl(comm,0) from emp;
注意:做四则运算时,注意数据类型 例:
select ename,ename+100 from emp;-- 报错,数据类型错误
-
起别名
关键字 as
1.给字段起别名
-- 查询emp表中的员工编号和薪资 select empno as 部门编号,sal as 薪资 from emp;-- 标准写法 select empno 部门编号,sal 薪资 from emp;-- 省略写法 select sal,comm from emp;-- 查询的是两个字段 select sal comm from emp;-- 查询的是一个字段,comm是sal的别名
2.给表起别名
-- 给emp表起别名 select t.* from emp t;
-
where查询
-
运算符
= > >= < <= and or is(null) is not(null) not in between...and...
-
实例
-- 查询emp表中sal大于1100的员工信息 select * from emp where sal > 1100; -- 查询emp表中sal大于等于1100的员工信息 select * from emp where sal >= 1100; -- 查询emp表中sal等于1600的员工信息 select * from emp where sal = 1600;--Oracle中只有=,没有== -- 查询emp表中sal等于1100或者sal等于1600的员工信息 select * from emp where sal = 1100 or sal = 1600;-- 效率高 select * from emp where sal in(1100,1600);-- in的效率偏低 sql优化 -- 查询emp表中sal=1600 并且 部门编号为30的员工信息 select * from emp where sal = 1600 and deptno = 30; -- 查询emp表中sal不等于1100的员工信息 select * from emp where sal != 1100; select * from emp where sal <> 1100; select * from emp where sal not in(1100); -- 查询emp表中sal大于等于1000,并且为小于等于2000的员工信息 select * from emp where sal >= 1000 and sal <= 2000; select * from emp where sal between 1000 and 2000; -- between后面跟较小的值,and后面跟较大的值 -- 查询emp表中有comm的员工信息 select * from emp where comm is not null; -- null只能和is或者is not连用,不能和=连用 -- 查询emp表中有comm的员工信息 select * from emp where comm is null;
-
-
模糊查询
-
两个概念
-
精确查询
查询的条件是具体的、精确的,一般用 = 连接
-
例:查询员工的 名字为张三 的员工
-
模糊查询
查询的条件是不具体的、模糊的,通常是用部分来查询整体
-
例:查询员工名字中带有“三”字的员工
-
-
关键字
like 像
-
占位符
_ :下划线 表示任意单个字符 % :百分号 表示任意多个字符
-
实例
-- 查询员工表中 名字为WARD 的员工信息 select * from emp where ename = 'WARD';-- 精确查询 -- 查询emp表中名字中以T开头的员工信息 select * from emp where ename like 'T%'; -- 查询emp表中以S结尾的员工信息 select * from emp where ename like '%S'; -- 查询emp表中名字中包含L的员工信息 select * from emp where ename like '%L%'; -- 查询emp表中名字中第二个字符为D的员工信息 select * from emp where ename like '_D%'; -- 查询emp表中名字由四个字符组成的员工信息 select * from emp where ename like '____';
-
-
-
排序查询
-
语法
语法: order by <排序字段1> <排序方式> , <排序字段2> <排序方式> , ......
-
排序方式
排序方式: (1)升序:从小到大,ASC 默认值(缺省值) (2)降序:从大到小,DESC
-- 查询emp表中的员工信息,按照sal从小到大排序 select * from emp order by sal asc;-- 标准写法 select * from emp order by sal;-- 简化写法,省略了asc
-
实例
-- 查询emp表中的员工信息,按照comm从小到大排序 select * from emp order by comm asc; -- oracle数据库中,null值参与排序,按照最大值计算 -- 查询emp表中的员工信息,按照总收入(sal+comm)的从大到小排序 select t.*,sal+nvl(comm,0) total from emp t order by total desc; -- 查询emp表中的员工信息,先按照sal从大到小排序,如果sal相同,再按照comm从大到小排序 select * from emp order by sal desc,comm desc;-- 双重排序
-
-
聚合函数
函数:就是方法,一般情况下都带有() 作用:对某一列进行纵向计算 max:求最大值 min:求最小值 count:计数 avg:求平均值 sum:求和
-
实例一
-- 求emp表中员工的最大工资,最小工资,平均工资,总工资,员工数量 select max(sal) from emp; select min(sal) from emp; select avg(sal) from emp; select sum(sal) from emp; select count(sal) from emp;
-
实例二
-- 求emp表中员工最大奖金,最小奖金,平均奖金,总奖金,员工数量 select max(comm) from emp; select count(nvl(comm,0)) from emp; -- null值不参与聚合函数计算 select avg(nvl(comm,0)) from emp; -- oracle数据库中处理null值使用nvl函数
-
-
分组查询
-
格式
group by 分组字段
-
实例
-- 查询emp表中所有的员工的sal总和 select sum(sal) from emp; -- 查询emp表中部门编号为10的员工的sal总和 select sum(sal) from emp where deptno=10; -- 查询emp表中每个部门的员工的sal总和 select sum(sal) from emp where deptno=10; select sum(sal) from emp where deptno=20; select sum(sal) from emp where deptno=30; -- 或: select deptno,sum(sal) from emp group by deptno; -- 一旦分组之后,那么select之后只能跟分组字段和聚合函数 -- 查询emp表中每个部门的员工的sal总和,sal低于1000的不参与计算 select deptno,sum(sal) from emp where sal>=1000 group by deptno; -- 分组前进行过滤 -- 查询emp表中每个部门的员工的sal总和,分组后sal总和小于9000,则不显示 select deptno,sum(sal) from emp group by deptno having sum(sal)>=9000; -- 分组之后进行过滤
-
where与having的区别
-
where:分组之前进行过滤,where后面不能跟聚合函数
-
having:分组之后进行过滤,having后面可以跟聚合函数
-
-
伪列查询
-
伪列的概念
在建表的时候,没有定义该列的,Oracle自动帮我们创建的列,伪列中的数据也是Oracle帮我们自动生成的。
-
伪列的特点
(1)每个表都有 (2)只能查询,不能修改
-
Oracle中的两个伪列
-
(1)rowid 该行的物理地址,是唯一的 例:AAAMfPAAEAAAAAgAAA
-
(2)rownum 查询结果中,给每一行标识的行号 后期做分页查询
dual 伪表:单行的表格,用于测试结果。
-
-
数据类型
-
字符型
-
(1) CHAR : 固定长度的字符类型,最多存储 2000 个字节
-
(2) VARCHAR2 :可变长度的字符类型,最多存储 4000 个字节
-
(3) LONG : 大文本类型。 最大可以存储 2 个 G
-
数值型
NUMBER : 数值类型,默认为number(5) 例如: NUMBER(5) 最大可以存的数为 99999 NUMBER(5,2) 最大可以存的数为 999.99
-
日期型
-
(1) DATE:日期时间型,精确到秒
-
(2) TIMESTAMP:精确到秒的小数点后 9 位
函数
字符串函数
-
upper(c1)
【功能】将字符串全部转为大写
select upper('helLO') from dual;--dual 伪表
-
lower(c1)
【功能】:将字符串全部转为小写
select lower('helLO') from dual;--dual 伪表
-
initcap(c1)
【功能】返回字符串并将字符串的第一个字母变为大写,其它字母小写;
-- 将首字母变大写 select initcap('helLO') from dual; select initcap(ename) from emp;
-
concat(c1,c2)
【功能】连接两个字符串
-- 字符串拼接 select concat('hello','oracle') from dual; select 'hello'||'oracle'||'nanjing'||'仙林' from dual;
-
substr(c1,n1[,n2])
【功能】取子字符串
-- 截取字符串 select substr('hellooracle',3,4) from dual; -- 与Java不同,3:从第3个开始,4:截取4个 select substr('hellooracle',3) from dual;-- 默认截取到结尾
-
instr(c1,c2[,I[,J]])
【功能】在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
-- 找出字符串中,指定字符的位置 select instr('hellooracle','ll') from dual; -- 返回指定字符第一次出现的位置,若无,则返回0
-
length(c1)
LENGTH(c1) 【功能】返回字符串的长度;
lengthB(c1) 【功能】返回字符串的字节数;
-- 获取字符串的长度 select length('hello'),lengthb('hello') from dual; --oracle中每一个英文字母占一个字节 select length('南京'),lengthb('南京') from dual; --根据oracle数据库底层的字符编码决定中文字节数
-
lpad(c1,n[,c2])
【功能】在字符串c1的左边用字符串c2填充,直到长度为n时为止 RPAD(c1,n[,c2]) 【功能】在字符串c1的右边用字符串c2填充,直到长度为n时为止
-- 左右填充 select lpad('njxq',10,'#'),rpad('njxq',10,'#') from dual;
-
TRIM(c1 from c2)
【功能】删除左边和右边出现的字符串
-- 去除左边和右边的字符串 select trim('*' from '*****njxq') from dual; -- 只能去除左右,不能去除中间 select trim(' nj xq ') from dual; -- 默认值就是空格
-
REPLACE(c1,c2[,c3])
【功能】将字符表达式值中,部分相同字符串,替换成新的字符串
-- 字符串的替换功能 select replace ('hello','l','$') from dual;
实例
-- 获取所有员工姓名的第一个字母 select substr(ename,1,1) from emp; -- 在所有员工的名字前面加上 南京 select concat('南京',ename) from emp; -- 找出所有员工中,名字为四个字符的员工 select * from emp where length(ename)=4; select * from emp where ename like '____';-- 模糊查询方法解答
数值函数
round四舍五入
-- 四舍五入
select round(100.456,2) from dual;-- 取小数点后两位
select round(100.456) from dual;-- 取整数
cell向上取整
-- 向上取整
select ceil(3.5678) from dual;-- 取大于其的最小整数
floor向下取整
-- 向下取整
select floor(3.5678) from dual;-- 取小于其的最大整数
trunc截取
-- 直接截取
select trunc(3.5678,3) from dual;-- 直接取小数点后3位
select trunc(3.5678,3) from dual;-- 取整数位
mod取余数
-- 取余数
select mod(10,3) from dual;
日期函数
-
sysdate
【功能】:返回当前日期。
-- 获取当前时间 select sysdate from dual;
-
扩展
-- 获取昨天和明天的日期 select sysdate-1,sysdate+1 from dual; -- 时间可以直接计算 -- 计算员工入职的年数 select (sysdate-hiredate)/365 from emp; select trunc((sysdate-hiredate)/365) from emp;-- 取整
-
-
months_between(d1,d2)
【功能】:返回日期d1到日期d2之间的月数。
-- 计算员工入职的月数 select months_between(sysdate,hiredate) from emp; select trunc(months_between(sysdate,hiredate)) from emp; -- 取整
-
add_months(d1,n1)
【功能】:返回在日期d1基础上再加n1个月后新的日期。
-- 在当月的基础上,加上6个月 select add_months(sysdate,6) from dual;
-
last_day(d1)
【功能】:返回日期d1所在月份最后一天的日期。
-- 获取当前月的最后一天日期 select last_day(sysdate) from dual;
-
next_day(d1[,c1])
【功能】:返回日期d1在下周,星期几(参数c1)的日期
-- 获取下一个星期三的日期 select next_day(sysdate,'星期三') from dual;
-
实例
-- 获取员工表中,哪些员工在入职月的月最后三天入职 select * from emp where hiredate between (last_day(hiredate)-2) and last_day(hiredate); select * from emp where last_day(hiredate)-hiredate<3 ;
转换函数
-
to_char(x[[,c2],C3])
将日期或数值转换为字符串数据类型 日期------->字符串 数值--------->字符串
-- 将数值转换为字符串 select to_char(1024) from dual; -- 将日期转换为字符串 select to_char(sysdate) from dual; select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day') from dual;-- 2024-04-28 09:04:27 星期日
转换为16进制
-- 字符串转换为16进制 select TO_CHAR(100,'XX') from dual; -- 数字型(数字)可以不加单引号,字符型必须要加单引号
to_date(X[,c2[,c3]])
-
将字符串X转化为日期型 Oracle数据库中采取用mi代替分钟,mm表示月
-- 将字符串转换为日期 select to_date('2024-04-21','yyyy-mm-dd') from dual; -- 'yyyy-mm-dd'是为了解析前面输入的字符串
-
to_number(X[[,c2],c3])
将字符串X转化为数字型
-- 将字符串转换为数字型 select to_number('456'),to_number ('12.345') from dual; -- 字符串16进制表示出10进制含义 select to_number('f','XX') from dual;-- xx为能表示的位数
其他函数
-
NVL (expr1, expr2)
NVL (expr1, expr2) 【功能】若expr1为NULL,返回expr2;expr1不为NULL,返回expr1。 NVL2 (expr1, expr2, expr3) 【功能】expr1不为NULL,返回expr2;expr1为NULL,返回expr3。
-- 求emp表中的员工的总收入 select ename,sal+nvl(comm,0) from emp; select ename,sal+nvl2(comm,comm,0) from emp;
COALESCE(c1, c2, ...,cn)
【功能】返回列表中第一个非空的表达式,如果所有表达式都为空值则返回1个空值 select coalesce(null,null,null,'南京') from dual;
-- 求emp表中的员工的总收入 select ename,sal+coalesce(comm,0) from emp; -- 若comm不为空,则返回它自己;若为空,则返回0
-
case...when...end
case [<表达式>] when <表达式条件值1> then <满足条件时返回值1> [when <表达式条件值2> then <满足条件时返回值2> …… [else <不满足上述条件时返回值>]] end
-- 查询emp表中数据,姓名,所在部门的名称 -- 10----开发部 20----销售部 30----财务部 select ename, case(deptno) when 10 then '开发部' when 20 then '销售部' when 30 then '财务部' else '未知部门' end as 部门 from emp; -- case对deptno进行一个判断 -- 开发人员通常使用case语句
-
decode(条件,值1,翻译值1,...值n,翻译值n,缺省值)
【功能】根据条件返回相应值
-- 使用decode实现查询部门名称 select ename, decode(deptno, 10,'开发部', 20,'销售部', 30,'财务部' ) 部门 from emp;
------------------ decode判断方法: --------------------- -- if 条件=值1,then return 翻译值1 -- elseif 条件=值2,then return 翻译值2 -- elseif 条件=值3,then return 翻译值3 -- ...... -- else return 缺省值 -- end if
约束
概念
就是限制,用来限制数据的
非空约束(针对单表)
1.关键字 not null
2.作用 限制表中的数据不能为null
-
创建表时添加非空约束
-- 创建学生表时,添加非空约束 create table stu4( id number not null, -- 在列的数据类型后加上not null就是非空约束 name varchar2(20) );
-
删除表中的非空约束
alter table stu4 modify (id null);
-
创建完表后,再添加非空约束
alter table stu4 modify (id not null);
唯一约束(针对单表)
1.关键字 unique
2.作用
保证该字段各值各不相同
唯一约束是无法约束null值的。
创建表时添加唯一约束
-- 创建表时添加唯一约束
create table stu5(
id number unique, -- 在列的数据类型后加上unique就是唯一约束
name varchar2(20)
);
删除表中的唯一约束
alter table stu5 drop constraint SYS_C005423;
创建完表之后,再添加唯一约束
alter table STU5
add constraint STU5_UK1 unique
(
id
)
enable;
给一个字段同时添加非空和唯一约束
-- 创建表时,同时添加非空和唯一约束
create table stu6(
id number not null unique, -- 给id字段同时添加非空和唯一约束
name varchar2(20)
);
选择约束(针对单表)
1.关键字 check
2.作用
限制数字的取值范围
选择约束是无法约束null值的
-
创建表时添加选择约束
-- 创建表时添加选择约束 create table stu7( id number, name varchar2(20), gender varchar2(5) check(gender in('男','女'))-- 给gender字段添加选择约束 );
-
删除表中的选择约束
ALTER TABLE STU7 DROP CONSTRAINT SYS_C005424;
-
创建表后,再添加选择约束
ALTER TABLE STU7 ADD CONSTRAINT STU7_CHK1 CHECK (gender in('男','女')) ENABLE;
默认约束(针对单表)
1.关键字 default
2.作用 当不给指令的字段赋值时时,会有默认值 给指定字段赋值后,以给定值为准
-
创建表时添加默认约束
-- 创建表时添加默认约束 create table stu9( id number, name varchar2(20), gender varchar2(5), age number default 13 -- 给age添加了默认元素(和类型对应) );
-
删除表中的默认约束
ALTER TABLE STU9 MODIFY (AGE DEFAULT NULL );
-
创建表后,再添加默认约束
ALTER TABLE STU9 MODIFY (AGE DEFAULT 13 );
主键约束(针对单表)
1.关键字 primary key
2.作用 作为表中记录的唯一标识
3.特点 非空且唯一
-
创建表时添加主键约束
-
方法一
-- 创建表时添加主键约束 create table stu10( id number primary key,-- 给id添加主键约束 name varchar2(20), );
-
方法二
-- 创建表时添加主键约束 create table stu10( id number, name varchar2(20), primary key(id)-- 给id添加主键约束 );
-
-
删除表中的主键约束
ALTER TABLE STU10 DROP CONSTRAINT SYS_C005431;
-
创建表后,再添加主键约束
ALTER TABLE STU10 MODIFY (ID NOT NULL); ALTER TABLE STU10 ADD CONSTRAINT STU10_PK PRIMARY KEY ( ID ) ENABLE;
-
一张表能不能有多个主键?不能
create table stu10( id number, name varchar2(20), primary key(id,name)-- 联合主键 );
-
主键约束和非空且唯一约束有什么区别?
-
主键约束:一张表只能有一个
-
非空且唯一约束:一张表可以有多个
外键约束(针对多表)
1.关键字 foreign key
2.作用
让多表之间的数据产生关联,从而使得我们的数据更加准确和有效
3.单表存在的问题
(1)存在大量的冗余数据
(2)删除员工的时候,删除了部门
(例) 解决方案:分成两张表
但是,新问题:
(1)添加员工时,可以添加不存在的部门
(2)删除部门时,该部门还留有员工
最终,使用外键解决。
-
创建表时添加外键
constraint <主表_外表_fk> foreign key(主表关联外表的键) reference <外表(外表外键)>
-
删除外键关系
alter table employee drop constraint employee_department_fk;
-
创建表之后,添加外键——推荐使用这种方法,好用
alter table employee add constraint employee_department_fk foreign key(dedp_id) reference department(id);
-
级联删除(扩展)
删除主表,附表相关的数据会被同时删除 on delete cascade
表设计
表关系
-
一对一关系
举例:人与身份证 一个人只有一个身份证,一个身份证也只能对应一个人
-
一对一要求:外键唯一
-
结论:oracle中基本见不到,通常会把一对一的表设计成一张表
-
一对多关系
举例:部门和员工 一个部门可以有多个员工,一个员工只能在一个部门
-
结论:在多的一方建立外键指向一的一方的主键
-
多对多关系
举例:学生和课程 一个学生可以选择多门课程,一门课程也能被多个学生选择
-
结论:oracle数据库中无法直接表示多对多关系,必须借助中间表(没有共同字段/关联字段)
-
设计方案
学生表和学生选课表时一对多关系:
-
一:学生表、多:学生选课表 设计方案:在 多 的一方建立外键指向 一 的一方的主键
课程表和学生选课表时一对多关系:
-
一:课程表、多:学生选课表 设计方案:在 多 的一方建立外键指向 一 的一方的主键
-
-
三大范式
-
第一范式
每一列都是不可再分的原子数据项
-
第二范式
消除部分依赖,从而消除冗余数据【将部分依赖(可推导关系)提取成另外的表】
-
第三范式
消除传递依赖(将间接关系提取成另外的表)
备份和还原
原因
防止数据被误删
按照库导入和导出
导出:exp system/123456 file=a.dmp full=yes
exp 管理员/密码 file=输出文件夹名.dmp full=yes(把整个库导出)
导入:imp system/123456 file=a.dmp full=yes
按照用户导入和导出
导出:exp system/123456 owner=lisi file=a.dmp
导入:imp system/123456 fromuser=lisi file=a.dmp
按照表导入和导出
导出:exp lisi/1234 file=a.dmp tables=stu7,stu8 (导出表stu7,stu8)
导入:imp lisi/1234 file=a.dmp tables=stu7,stu8
查询
多表查询(重点)
-
原因
单表查询不够满足需求
-
原理
使用表之间的拼接 使用笛卡尔积,将两个集合进行拼接
笛卡尔积: 集合1:{a,b} 集合2:{1,2,3} 对以上两个集合进行笛卡尔积:{a1,a2,a3,b1,b2,b3}
- 为什么使用笛卡尔积进行拼接?
- 为了在一行数据中查询出所有我要的信息
-
使用笛卡尔积的弊端?
-
会产生大量的无效数据
-
怎么解决?
-
使用条件过滤
-
-
结论:但凡使用多表查询,必须过滤无效数据
-
例:
-
select t1.* , t2.* from employee t1,department t2 where t1.dept_id=t2.id;
-
-
分类
-
内连接
-
隐式内连接(自然连接)
--------------------------隐式内连接-------------------------------- -- 查询emp表中所有的员工信息,对应的部门信息 select t1.*,t2.* from emp t1,dept t2 where t1.deptno=t2.deptno; -- 查询emp表中所有的员工信息,对应的工资等级 select t1.*,t2.* from emp t1,salgrade t2 where t1.sal between t2.losal and t2.hisal; -- 查询emp表中所有的员工信息,对应的部门信息,对应的工资等级 select t1.*,t2.*,t3.* from emp t1,dept t2,salgrade t3 where t1.deptno=t2.deptno and t1.sal between t3.losal and t3.hisal;
-
显式内连接(公式连接)(推荐使用)
-
语法
select 字段列表 from 表名1 [inner] join 表名2 on 限制条件(去除无效的笛卡尔积数据)
-
实例
------------------------显式内连接-------------------------- -- 查询emp表中所有的员工信息,对应的部门信息 select t1.*,t2.* from emp t1 inner join dept t2 on t1.deptno=t2.deptno; -- 查询emp表中所有的员工信息,对应的工资等级 select t1.*,t2.* from emp t1 join salgrade t2 on t1.sal between t2.losal and t2.hisal; -- 查询emp表中所有的员工信息,对应的部门信息,对应的工资等级 select t1.*,t2.*,t3.* from emp t1 join dept t2 on t1.deptno=t2.deptno join salgrade t3 on t1.sal between t3.losal and t3.hisal;
-
-
弊端
无法查询出两张表中不是公共数据的部分
-
-
外连接
左表、右表:相对于(参照物)的左/右表
-
左外连接:左表的所有 + 公共部分
-
右外连接:右表的所有 + 公共部分
-
满外连接(全连接):左右表的所有 + 公共部分
-
左外连接
语法: select 字段列表 from 表名1 left [outer] join 表名2 on 去除无效的笛卡尔积数据
参照物:left [outer] join 左表:表名1 右表:表名2
-
实例
-- 使用左外连接来修改需求 select t1.*,t2.* from dept t1 left outer join emp t2 on t1.deptno=t2.deptno;
-
-
右外连接
语法: select 字段列表 from 表名1 right [outer] join 表名2 on 去除无效的笛卡尔积数据
参照物:right [outer] join 左表:表名1 右表:表名2
-
实例
-- 使用右外连接来查询emp表中所有的部门信息,对应的员工信息 select t1.*,t2.* from emp t1 right join dept t2 on t1.deptno=t2.deptno;
-
左外连接与右外连接的相互转换
-- 查询所有的员工信息和对应的领导信息 -- 自身连接 select t1.*,t2.* from emp t1 join emp t2 on t1.mgr=t2.empno; -- 内连接,缺少部分数据 -- 左外连接 select t1.*,t2.* from emp t1 left join emp t2 on t1.mgr=t2.empno; -- 右外连接 select t1.*,t2.* from emp t1 right join emp t2 on t2.mgr=t1.empno;
-
-
满外连接/全连接
语法: select 字段列表 from 表名1 full [outer] join 表名2 on 去除无效的笛卡尔积数据
-
实例
-- 全连接 -- 查询emp表所有的员工信息,所有的部门信息 select t1.*,t2.* from emp t1 full join dept t2 on t1.deptno=t2.deptno;
-
-
-
子查询
-
定义
在一个查询中嵌套另一个查询
-
分类
-
where子句子查询
-
单行单列
使用语句:> < <= <> = >=
-- where子句子查询 select * from emp where sal=(select max(sal) from emp); -- 子查询先执行子查询语句,在执行外层的查询 -- 查询emp表中sal大于平均工资的员工信息 select * from emp where sal>(select avg(sal) from emp);
-
多行多列
使用语句:in exists any all
-- where子句子查询 多行单列 -- 查询ACCOUNTING部门和RESEARCH部门中所有的员工信息 select deptno from dept where dname in('ACCOUNTING','RESEARCH');-- 根据部门名称查询部门编号 select * from emp where deptno in(select deptno from dept where dname in('ACCOUNTING','RESEARCH')); -- 使用exists转换in select * from emp where exists(select deptno from dept where dname in('ACCOUNTING','RESEARCH')and emp.deptno=dept.deptno); -- 查询emp表中低于其中任意一个职位为CLERK的员工工资的信息 select sal from emp where job='CLERK';-- 查询出CLERK职位的所有员工的工资 select * from emp where sal<any(select sal from emp where job='CLERK') and job<>'CLERK'; -- 查询工资比所有的职位为SALESMAN员工的工资都高的员工信息 select sal from emp where job='SALESMAN'; select * from emp where sal>all(select sal from emp where job='SALESMAN');
-
in与exists的异同
同:都有select子语句 异:in先执行内部子语句;exists先执行外部语句,再代入子语句进行判断。
-
-
-
from子句子查询
-------------------------from子句子查询---------------------------- -- 查询工资低于3000的员工信息,以及对应的部门信息 -- 不适用from语句,多表联查 select t1.*,t2.* from emp t1 join dept t2 on t1.deptno=t2.deptno where t1.sal<3000; -- 使用from子句 select t1.*,t2.* from (select * from emp where sal <3000) t1 join dept t2 on t1.deptno=t2.deptno;-- 子查询 + 多表联查
-
select子句子查询
-------------------------select子查询-------------------------- -- 查询emp表中的员工编号,员工姓名,部门名称 -- 多表联查 select t1.empno,t1.ename,t2.dname from emp t1 join dept t2 on t1.deptno=t2.deptno; -- 使用select子句 select empno,ename,(select dname from dept where deptno=emp.deptno)from emp;
-
-
-
分页查询
什么是分页 分成多页
为什么要分页 每次只返回一部分数据
-
如何实现分页
-
回顾伪列
rowid rownum
-- 回顾伪列 rowid rownum select rowid,rownum,t.* from emp t;
-
-
简单查询
-- 简单查询 -- 查询员工信息,取前五条作为第一页 select rownum,t.* from emp t where rownum<=5;-- 第一页 -- select rownum,t.* from emp t where rownum=5; -- rownum只能是小于等于,不能是=或>(具体看rownum编码原理) select rownum rn,t.* from emp t;-- 子语句,为了拿到所有的rownum select * from (select rownum rn,t.* from emp t) where rn>5 and rn<=10;-- 第二页 -- 若rownum不取别名,则where后rownum会根据查询表自动生成,与from子句中rownum不一致 select * from (select rownum rn,t.* from emp t) where rn>10 and rn<=15;-- 第三页
-
排序查询
-- 查询员工信息,按照sal从大到小的顺序排序,取前五条作为第一页 select rownum,t.* from emp t where rownum<=5 order by sal desc; -- order by最后执行,导致结果出错 select rownum,t.* from ( select * from emp order by sal desc ) t where rownum<=5;-- 第一页 select * from ( select rownum rn,t.* from ( select * from emp order by sal desc ) t ) where rn>5 and rn<=10;-- 第二页
集合运算
定义: 把两个或者两个以上的表(集合)的结果,组合成一个新的结果。
并集
-
关键字
全部 union all:取两个集合的所有记录,包含重复值 union:取两个集合的所有记录,不包含重复值
-
实例
-- 并集 select * from emp where sal >=1000 union all select * from emp where sal <=2450;-- 共有21条,有重复数据 select * from emp where sal >=1000 union-- 没有重复数据 select * from emp where sal <=2450;-- 有7条重复数据
交集
-
关键字
公共部分 intersect
-
实例
-- 交集运算 select * from emp where sal >=1100-- 12 intersect select * from emp where sal <=2450;-- 9 -- 7
差集
-
关键字
左边取右边差集:属于左边但不属于右边的部分 minus
-
实例
-- 差集运算 select * from emp where sal >=1100-- 12 minus select * from emp where sal <=2450;-- 9 -- 5
-
使用差集计算分页
-- 使用差集计算分页 select rownum,t.* from emp t where rownum<=5;-- 第一页 -- 第二页 select rownum,t.* from emp t where rownum<=10 minus select rownum,t.* from emp t where rownum<=5;
视图
概念
就是一个查询语句的执行结果,被保存下来的对象
称视图为:虚表
为什么使用视图?安全
视图的相关操作
-
创建视图
create [or replace] view <view_name> as <select语句>;
-- 创建视图 create view emp_view as select * from emp where id>3;
-
使用视图
-- 查询emp表中id大于3的员工信息 select * from emp where id>3; select * from emp_view;
-
修改视图
-- 修改视图 create or replace view emp_view as select * from emp where id>10;
-
删除视图
-- 删除视图 drop view emp_view;
-
根据视图来创建视图
-- 根据一个视图创建另一个视图 create view test_view as select * from emp_view where id>9;
-
改变视图会改变原表
-- 改变视图,原表也会跟着改变 insert into saltab_view values(38,1004,2000,201612,3); update saltab_view set salary=2500 where eid=38; delete from saltab_view where eid=38;
-
应用
银行数据库程序员修改表数据时不接触原表,而是修改视图
-
序列
是Oracle数据库用来自动产生唯一数字的数据对象
创建默认序列
-
语法
CREATE SEQUENCE <sequencen_name> INCREMENT BY n START WITH n MAXVALUE n [CYCLE|NOCYCLE] [CACHE n|NOCACHE];
-
解释
-
INCREMENT BY n --表示序列每次增长的幅度;默认值为1.
-
START WITH n --表示序列开始时的序列号。
-
默认值为1. MAXVALUE n --表示序列可以生成的最大值(升序).
-
MINVALUE n --表示序列可以生成的最小值(降序).
-
CYCLE --表示序列到达最大值后,在重新开始生成序列.默认值为 NOCYCLE。
-
CACHE --允许更快的生成序列.
-
实例
-- 创建一个 默认 的序列 create sequence squ1;
序列中的两个伪列
-
currval
返回序列的当前值. 注意在刚建立序列后,序列的CURRVAL值为NULL,所以不能直接使用。
-
nextval
返回序列下一个值
-
使用方法
-- 使用序列的两个伪列 select squ1.currval from dual; select squ1.nextval from dual;
创建复杂序列
-- 创建复杂序列
create sequence squ2
increment by 2 -- 每次增加2
start with 10086; -- 从10086开始增加
-- 使用序列向stu3表中添加数据
insert into stu3(no,name) values(squ2.nextval,'王小二');
insert into stu3(no,name) values(squ2.nextval,'王小三');
insert into stu3(no,name) values(squ2.nextval,'王小四');
insert into stu3(no,name) values(squ2.nextval,'王小五');
修改序列
-- 修改序列
alter sequence squ2
--start with 1000; -- start with参数不能修改
increment by -1;
删除序列
-- 删除序列
drop sequence squ2;
事务(重点)
事务其实就是生活中的业务,比如:银行转账(张三给李四转500元)
在Oracle数据库中指的是多条增删改语句组成的整体
提交方式
-
自动提交
-- 查看Oracle数据库库的提交方式 show autocommit; -- autocommit OFF表示手动提交 -- 修改Oracle数据库的提交方式为自动提交 set autocommit on;
-
手动提交
Oracle数据库默认的提交方式
----------------事务的提交方式---------------------- -- 使用insert向stu4中添加数据 insert into stu4 values (1,'王小二') insert into stu4 values (2,'王小三') insert into stu4 values (3,'王小四') commit;
作用
事务内部增删改语句要么同时成功,要么同时失败
事务的三个操作
(1)SAVEPOINT < savepoint > 保存位置 标记事务的开始位置,为了回滚操作做铺垫
(2)COMMIT 提交 把事务操作中产生的临时值,真正的更新到数据库中 为了保证事务的同时成功/失败,必须使用手动提交
(3)ROLLBACK [TO savepoint] 回滚 把事务操作中产生的临时值,取消
四大特征
-
原子性
会把事务当成一个不可再分的整体,要么同时成功,要么同时失败
-
持久性
持久化(序列化)
-
数据的状态:游离状态(在内存中游离) 持久态数据(保存在硬盘中)
-
执行commit操作之后,会将数据保存到硬盘中
-
-
一致性
事务执行前后,数据的总和不变
-
隔离性
多个事务之间,是相互隔离的
事务的隔离级别
-
可能出现的问题
-
脏读
一个事务读取到了另一个事务还未提交的数据
-
不可重复读(虚读)
一个事物读取到了另一个事务已经提交的update语句 //数量一样 内容会变
-
幻读
一个事物读取到了另一个事务已经提交的insert语句 //数量会变
-
-
隔离级别
-
Read Uncommited
读未提交
可能出现脏读、不可重复读(虚读)、幻读
-
Read commited
读已提交
可能出现不可重复读(虚读)、幻读,是Oracle数据库的默认隔离级别
-
Repeatable Read
可重复读
可能常出现幻读 ,是MySQL数据库的默认隔离级别
-
Serializable
串行化
不会出现问题
-
安全性从上向下递增,效率从上向下递减
-
索引
定义: 是一种提升查询效率的数据库对象。 缺点:增删改的效率会变低
需要使用索引的时候
数据量比较大的时候,查询效率就会偏低,需要使用索引来提升查询效率
测试索引的查询效率
-
创建一张表
-- 创建一张表 create table person( id number, name varchar2(32), age number(3) );
-
向表中添加大量数据——利用循环直接导入500w条数据
-- 向person表中添加500w条数据 begin for x in 1..5000000 loop insert into person values(x,'NJ'||x,23); end loop; commit; end;
-
测试使用索引和不适用索引的区别
-------- 测试使用索引和不使用索引的区别 -------- -- 根据name进行条件查询 -- 不使用索引 0.248s 使用索引之后 0.004s -- 查询一次后产生缓存,以后索引查询速度增快 select * from person where name='NJ431219'; -- 给person表的name字段加一个索引 create index person_name on person(name);
判断查询语句是否走了索引
使用慢查询: explain plan for 一定要加在select前,不能加在增删改语句前。
explain plan for select * from person where name='NJ431219';-- 只会更新计划
-- 查询计划
select * from table(dbms_xplan.display);
-
走了索引
在查询计划中,会出现index range scan的显示
-
未走索引
在查询计划中,不显示index range scan 而是显示table access full 说明进行的是全表扫描
判断单个字段索引的执行条件
1.必须有where
2.where后面必须使用索引字段进行条件判断
-
实例
explain plan for select * from person where name='NJ431219';-- 只会更新计划 explain plan for select * from person where id = 134;-- 不走索引
-
关于模糊查询是否走索引
占位符在末尾,走索引
占位符在开头,不走索引(全表扫描)
联合索引的执行条件
1.必须有where
2.where后面必须使用索引字段进行条件判断
3.最左匹配原则(最左前缀原则) 最左边的字段必须要提供,不提供左边字段查询时不走索引
-
实例
-- 给person表添加一个联合索引 create index person_name_id on person(name,id);
索引的底层结构
数据结构图示化网站 Data Structure Visualization
-
数据库中索引的底层结构
B+树
-
B树和B+树有什么区别
(1)B+树的叶子节点之间是有指针相互指向的
(2)B+树中在叶子节点中冗余了非叶子节点的数据
索引的底层工作原理
-
索引的底层逻辑
1.对索引字段进行排序
2.提取“目录”——倒排表
在Oracle中的分类
-
普通索引
-
一个字段的索引
-- 给person表的name字段加一个索引 create index person_name on person(name);
-
-
联合索引
-- 给person表添加一个联合索引 create index person_name_id on person(name,id);
-
唯一索引
-
添加唯一索引
-- 给stu13表的id字段加唯一索引 create unique index stu13_id on stu13(id);
可以提供唯一约束条件
-
-
位图索引
底层是个图片,适用低基数列
PL/SQL
process language
SQL中的过程语言
语法结构
DECLARE --声明部分 声明语句 BEGIN --执行部分 执行语句 EXCEPTION --异常处理部分 执行语句 END;
基本语法
-
对变量的声明与赋值
声明: <变量名> 类型;
赋值: <变量名> :=初始值;
特殊类型 字段%type
-
实例
-------------------变量的声明和赋值--------------------- -- 定义变量 declare -- 声明,后面用来声明变量 v_id number;-- 定义变量不是字段,每次定义后需要使用分号 v_name varchar2(32); v_score number(3,1); v_date date;-- 定义格式与定义表相似,但未创建表 begin v_id := 10086; v_name := '张三'; v_score := 96.6; v_date := to_date('2000-09-10','yyyy-mm-dd'); dbms_output.put_line(v_id);-- 输出v_id的值 dbms_output.put_line(v_name);-- 输出v_name的值 dbms_output.put_line(v_score);-- 输出v_score的值 dbms_output.put_line(v_date);-- 输出v_date的值 end;
-
练习
-- 练习:定义两个number类型的值,输出两个数的和 declare m number; n number; begin m := 10086; n := to_number('100', 'xxx'); dbms_output.put_line(m+n); end;
-
重点
1.声明变量中后面使用分号
2.赋值变量时使用 ‘ := ‘
-
-
用户输入(&)
-- 用户输入两个数字,求两个数字的和 declare num1 number; num2 number; begin num1 := &请输入第一个数字; -- 获取用户输入的值(数字类型) num2 := &请输入第二个数字; dbms_output.put_line((num1+num2)); end;
-
select into赋值
-
实例一
-- 输入员工的编号,输出对应的员工的姓名 declare v_id number; v_name varchar2(32); begin v_id := &请输入员工编号; select ename into v_name from emp where empno = v_id;-- select into 赋值 dbms_output.put_line(v_name); end;
-
实例二
-- 输入员工的编号,输出对应的员工的姓名,对应的部门名称 declare v_id number;-- 接受用户输入的值 v_name varchar2(32); v_dept varchar2(32); begin v_id := &请输入员工编号; select ename,deptno into v_name,v_dept from emp where empno = v_id;-- into前后字段数量相同 dbms_output.put_line(v_name||','||v_dept);-- 使用 ‘ || ’ 拼接 end;
-
-
属性类型
-
%type
-- 属性的类型引用 declare v_id number;-- 接受用户输入的值 v_name emp.ename%type;-- 引用表中 某一字段 的类型 v_dept emp.deptno%type; begin v_id := &请输入员工编号; select ename,deptno into v_name,v_dept from emp where empno = v_id;-- select into 赋值 dbms_output.put_line(v_name||','||v_dept); end;
-
%rowtype
-- 查询emp表中empno=7369的员工信息 declare v_emp emp%rowtype;-- 将一整行的数据直接赋值给v_emp begin select * into v_emp from emp where empno =7369; dbms_output.put_line(v_emp.empno || ',' ||v_emp.ename); -- 查询编号和姓名 end;
-
-
异常处理
-------------------------异常-------------------------- declare v_id number; v_name varchar2(32); begin v_id := &请输入员工编号; select ename into v_name from emp where empno = v_id;-- select into 赋值 dbms_output.put_line(v_name); exception when TOO_MANY_ROWS then dbms_output.put_line('查询结果异常,返回了多条信息'); when NO_DATA_FOUND then dbms_output.put_line('查询结果为空'); end;
-
条件判断
-
if
IF <条件1> THEN 语句
end if;
-
实例
-------------------- 条件判断 -------------------- -- if then 结构 -- 输入一个学生的年龄,如果大于18,则输出已成年 declare v_age number; begin v_age :=&请输入一个年龄; if v_age >= 18 then dbms_output.put_line('已成年'); end if; end;
-
-
if else
IF <条件1> THEN 语句
ELSE 语句
end if;
-
实例
-- if......else结构 -- 输入一个用户名和密码,判断是否登录成功 -- lisi 1234 declare v_uname varchar2(32); v_password varchar2(32); begin v_uname :='&请输入用户名'; -- 只能接受数字,不能接受字符串,需要增加'' v_password :='&请输入密码'; if v_uname ='lisi' and v_password='1234' then dbms_output.put_line('登录成功'); else dbms_output.put_line('登录失败'); end if; end;
-
-
if...else if ...else if...else
IF <条件1> THEN 语句 [ELSIF <条件2> THEN 语句 . . . ELSIF <条件n> THEN 语句] [ELSE 语句] END IF;
注意是elsif,与以往不同
-
实例
-- if多选结构 -- 输入一个学生成绩,如果大于等于90,输出优秀,大于等于70,输出良好,大于等于60,输出及格,否则输出不及格 declare v_score number; begin v_score := &请输入一个成绩; if v_score >=90 then dbms_output.put_line('优秀'); elsif v_score >=70 then dbms_output.put_line('良好'); elsif v_score >=60 then dbms_output.put_line('及格'); else dbms_output.put_line('不及格'); end if; end;
-
-
-
循环
-
loop(do...while)
-- 语法结构 LOOP 语句; EXIT WHEN <条件> END LOOP;
-
实例
------------------------循环结构--------------------------- -- loop 循环(do...while) -- 输出1---100 declare num number; begin num :=1; loop dbms_output.put_line (num); num := num + 1; exit when num > 100; end loop; end;
-
-
while
-- 语法结构 WHILE <条件> LOOP 语句; END LOOP;
-
-
实例
-- 使用while loop实现输出1---100 declare num number; begin num :=1; while num<=100 loop dbms_output.put_line (num); num := num + 1; end loop; end;
-
-
for
FOR <循环变量> IN 下限..上限 LOOP 语句; END LOOP;
-
实例
-- 使用for实现输出1---100 declare num number; begin num :=1; for num in 1..100 loop dbms_output.put_line (num); end loop; end;
-
-
游标
用来临时存储从数据库中查询出来的数据块
-
相关操作
-
创建和声明一个游标
CURSOR <游标名> IS <SELECT 语句>
-
使用游标
-
open 打开游标
创建出一个光标,光标的初始位置在第一行数据之前
-
fetch 抓取一行数据
判断是否有下一行数据 没有下一行数据,不移动,notfound=true 有下一行数据 光标向下移动一位 获取当前指向行的数据
-
close 关闭游标
光标删除
-
-
带参数的游标
-
实例
-- 带参数的游标 -- 根据员工的no查询对应的员工信息 declare v_stu2 stu2%rowtype; cursor c_stu2(c_no number) is select * from stu2 where no = c_no; begin open c_stu2(2); fetch c_stu2 into v_stu2; dbms_output.put_line(v_stu2.no||','||v_stu2.name||',' ||v_stu2.score||','||v_stu2.birthday); close c_stu2; end;
-
-
for循环游标
-
实例
-- for循环游标(隐式游标) -- for在逻辑上替代了游标的遍历 declare cursor c_stu2(c_no number) is select * from stu2 where no = c_no; begin for v_stu2 in c_stu2(106) loop dbms_output.put_line(v_stu2.no||','||v_stu2.name||',' ||v_stu2.score||','||v_stu2.birthday); end loop; end;
-
-
存储函数(函数)
函数:返回结果,做查询操作 Oracle自带的函数(预定义):字符串函数、日期函数、转换函数.etc 自己定义的函数(自定义):存储函数
-
实例
---------------------存储函数----------------------- -- 定义一个根据员工id查询员工姓名的存储函数 create or replace function fu_stu2_1(v_no number) return varchar2 -- return 后的数据类型不要写长度 is -- 就相当于declare,可以定义变量 v_name varchar2(32); begin select name into v_name from stu2 where no = v_no; return v_name;-- 返回值 end; -- 使用存储函数 select sysdate from dual; select fu_stu2_1(2) from dual;
-
练习
-- 练习:传入员工id,返回对应的部门名称 -- employee表和department表 create or replace function fu_emp_dept(v_id number) return varchar2 is v_deptname varchar2(32); begin select t2.dep_name into v_deptname from employee t1 join department t2 on t1.dep_id=t2.id where t1.id=v_id; return v_deptname; end; select fu_emp_dept(2) from dual;
存储过程
存储函数:包含了一个查询语句的
存储过程:包含了一个事务
-
相关操作
-
创建存储过程(传入参数)
-
实例
--------------- 存储过程 ------------------- -- 声明一个传入参数的存储过程 -- 先创建一个序列 create sequence seq1 start with 10; create or replace procedure pro_employee( v_name varchar2, v_age number, v_dep_id number) is begin insert into employee values(seq1.nextval,v_name,v_age,v_dep_id); commit; end; -- 调用传入参数存储过程 -- 有两种方式 -- 方式一:使用call call pro_employee('陈九',19,1); -- 方式二:使用begin end begin pro_employee('黄十',20,2); end;
-
-
创建存储过程并调用(传出参数)
-
实例
-- 定义一个传出参数的存储过程 create or replace procedure pro_employee2( v_name varchar2, v_age number, dep_id number, v_id out number -- out表示输出参数 ) is begin select seq1.nextval into v_id from dual; insert into employee values(v_id,v_name,v_age,v_dep_id); commit; end; -- 调用传出参数的存储过程 -- 只有一种调用方式 declare v_id number; begin pro_employee2('陆十一',21,2); dbms_output.put_line(v_id); end;
-
-
触发器(很少使用)
概念:
生活中的触发器:灯的开关——按下开关——灯亮
Oracle中的触发器:执行增删改语句的时候——触发另一个逻辑
注意点: 触发器内不能有commit,会产生问题
-
分类
-
前置触发器
-
实例
-- 创建一个前置触发器 create or replace trigger tri_emp_depname before update of dep_name on department for each row declare begin :new.dep_location :='未知'; end; -------调用update语句触发的触发器 update emp set depname ='财务部' where id =6;
-
-
后置触发器
-
实例
-- 创建一个后置触发器 create or replace trigger tri_emp_depname1 after update of dep_name on emp for each row declare begin insert into department values(seq1.nextval,:old.dep_name,:old.dep_location); end; -------调用update语句触发的触发器 update emp set dep_name ='财务部' where id =3;
-
-