Oracle数据库

数据库的历史

自从有了人类就有了信息,声音、光线、触觉、嗅觉、味觉等等这些五官能感知的东西中都带有信息。运动员听到枪响就起跑,枪响表示起跑的信息;长城上燃起的狼烟是敌人来犯的信息;婴儿在不了解这个世界时,他们用嘴巴来感知这个世界,这些都是生活中的信息。在当今社会中我们经常说的信息化,就是收集有用的信息加以分析和判断用来指导我们的生活和生产,这些信息可能在报纸上、互联网上等等,当这些信息保存在互联网上时,它们就变成了一个个的数据,而这些变为数据的信息就需要管理起来,于是就有了专业管理数据的软件,就是各种信息管理系统,而在这些信息管理系统的底层往往使用是数据库。
在远古时代,人们用结绳记事时,绳结就是信息就是数据,它们的载体是绳子,绳子可以说是最简单的数据库了。后来人们有了纸和笔时,纸就是数据库了,有了计算机后,人们把数据储存到文件中,文件成了数据库,直到后来有了专业的软件来管理这些文件中的数据,这些软件就是数据库软件,而安装这些软件的计算机就成了数据库服务器。我们有时说的数据库可能是指保存数据的这些文件,也可能是指管理这些数据的软件,也可能是指服务器,具体要看语境。总之,数据是信息的载体,数据库是数据的仓库,数据库软件是管理数据仓库的软件,安装了数据库软件的计算机就是数据库服务器。这里有几个单词和它们对应,data,database,databaseSoft,Database Management System,databaseService
数据库有多种数据模型,不同的模型中数据的结构和操作数据的方法都不相同,不同的数据库软件会采用其中一种数据模型。目前用的比较多的数据模型有关系数据模型和非关系型数据模型,它们的代表是:Oracle、MySQL、SQLServer使用关系数据模型,H2、Redis、MongoDB等NoSQL数据模型,以键值对形式、文档形式或其它对象形式储存数据。

关系型数据库

1970年E.F.Codd’s发表了论文 <<relational model of data for large shared data banks>>大型共享型数据库的关系模型,为关系型数据库提供了基础理论,为关系型数据库的诞生创造了条件。
关系模型中主要有关系模式和关系两个概念,关系就是关系模式在某一时刻的状态或内容,关系模式是静态的稳定的,关系是动态的,随着时间变化而变化的,因为关系操作在不断的更新着数据库中的数据。
在关系型数据库出现前有网状数据库和层次数据库,用户在操作数据前要明确指定数据的位置和路径,需要明确数据的结构。关系型数据库中,用户操作数据时是非过程化的,不需要指定路径,由DBMS自己优化完成(Database Management System)
关系模型中无论是实体还是实体间的联系均由单一的结构类型——关系来表示。在实际的关系数据库中的关系也称表。一个关系数据库就是由若干个表组成。现在给关系模型下一个定义:关系模型是指用二维表的形式表示实体和实体间联系的数据模型。

几个概念

实体:现实中的具体事物或关系,如:学校、院系、学生、成绩信息、选课表信息。
表: 实体在数据库中的表现,由一条条记录组成。
行: 表格中的一条记录。
列: 组成行的一个属性。
主键:能唯一区分表中的某一行。
外键:其它表的主键在当前表中的列。

school表:学校实体对应的表

编号名称地址
1北京大学北京市海淀区颐和园路5号
2清华大学北京市海淀区双清路30号

编号是其中一个列
编号能唯一确定一行

student表:学生实体对应的表

编号学号姓名年龄班级
12001赵一18202001
22002钱二18202001
32003孙三18202002

编号是表的主键,能唯一确定一条记录,学号列也可以,学号可以是表中的候选主键

course 课程表

编号名称
1Java
2C
3Oracle

score成绩表

学生编号课程编号成绩
11100
1290
1388
2190
2290
2395

成绩表中学生编号和课程编号共同作为主键(联合主键)

clazz 班级表

编号名称
202001软件一班
202002软件二班

编号在clazz表中是主键,在student表中班级列是student表的外键

三范式

数据在数据库中会占用大量空间,为了节省空间,同时也为了让数据更安全更合理,在设计表时要用三范式来规范一下。
为了简单理解一下三范式,记住以下几点,这在考试中是非常有用的。
一范式:字段不可再拆分
二范式:表中要有主键
三范式:表中不能用别的表的非主键
以上的描述是简要描述,按照一些教材的描述是比较复杂的,下面来详细介绍一下各个范式。
一范式(1NF):字段不可拆分
这个比较好理解,比如下表中:
student

编号姓名联系方式
1常熟阿庆嫂2826109xx,17788990011,leadxxx

上面的表不符合1NF,姓名列中的常熟阿庆嫂有两个信息,一个是地名常熟,一个是姓名阿庆嫂
联系方式列中,分别有QQ号,手机号,微信号,所以这个表可以设计成这样
student

编号姓名地址QQ电话微信
1阿庆嫂常熟2826109xx17788990011leadxxx

这样设计就符合一范式,因为每个列都不能再拆分了。
再来看一下第二范式:表中要有主键
表中要有主键,是为了让主键能唯一确定一条记录,如果表中的记录有重复
或者没有重复,但没有主键信息也不行,当然要符合二范式,首先要符合一范式

姓名地址性别年龄
小红下元10
小红下元20
小明小店8
小明小店8

以上的记录中有两个问题:1.小红的两条记录虽然没有重复,但是也没有主键,
就算是设置主键也是“姓名+地址+性别+年龄”共同组成主键,这样才能保证没有
重复记录。2.小明的那两条记录重复了,但现实中可能是两个人,只是信息巧合了
。所以要对该表进行整改,给这个表加一个“编号”列作为主键。

编号姓名地址性别年龄
1小红下元10
2小红下元20
3小明小店8
4小明小店8

这样增加“编号”列,作为主键,就满足了第二范式。
再来看一下第三范式:表中不能有别的表的非主键列
这句话在一些资料中讲的是:

第三范式(Third Normal Form,3rd NF)就是指表中的所有数据元素不但要能唯一地被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系。也就是说,对于一个满足2nd NF 的数据结构来说,表中有可能存在某些数据元素依赖于其他非关键字数据元素的现象,必须消除。

这样的描述可能不太好理解,其实就是别的表中的字段只允许它们的主键出现在当前表中,其它字段不能出现,如下表:

编号姓名性别班级编号班级名称
1赵一202001软件一班
2钱二202001软件一班

这个表当然是满足了一、二范式,但是不满足三范式,存在两个问题:1.“班级名称”这一列没有必要出现,因为会造成数据冗余,浪费了空间,只要有“班级编号”就能确定是哪个班了。2.如果在“班级”表中修改了班级名称“软件一班”改为“软件工程一班”后,还要修改上面这个表中的“班级名称”的这一列,造成性能低下,如果要是这个表中有上亿条记录的话,修改也会花费一定的时间。但是,如果不修改这个表,会造成“202001”这个班的名称在系统中不统一,造成数据不一致,这对一个系统来说是不允许的。
出现上述原因是因为表中出现了“班级”表不是主键的“班级名称”这一列,把这一列去掉就可以了,另外,“班级编号”在班级表中是主键,在当前表中就应该是外键了。外键的好处是:如果依赖的表中没有相应的主键记录,则当前表就无法添加记录,这就是外键依赖,当然要删除依赖表中的记录时,如果记录有被依赖的情况,则要么报错不能删除,要么级联删除当前表中的记录,保证了数据的安全,避免了依赖不存在的问题。
班级表

编号(PK)名称
202001软件一班

学生表

编号(PK)名称班级编号(FK)
1赵一202001
2钱二202002

上面的班级表中不存在记录“202002”这个班级,所以“2,钱二,202002”是无法保存的。如果“学生”表中的外键“班级编号”列设置了外键级联删除,则删除班级表中的“202001”记录时,会级联删除“学生”表中的所有“班级编号”是202001的记录。但是,如果外键列设置的不是级联删除,则删除班级表中的记录“202001”时,数据库会报错。
三范式也不只有三种范式,其实还有巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式),我们知道范式是为了解决数据冗余的问题,但是范式太高的话,又会影响数据查询的性能,所以,一般做到三范式就可以了。

DBA常用操作

管理员登录

sqlplus / as sysdba
这样登录是管理员登录,免密码,拥有最高权限,它采用的是操作系统的验证,所以只要能正常登录操作系统,就可以用这个命令登录Oracle数据库。但是,有时候我们登录操作系统的用户因为权限原因不能这样登录数据库,就要解决操作系统用户权限的问题。找到Oracle的安装目录,如:F:\app\DELL\product\11.2.0\dbhome_1\database这个目录下的一个文件oradba.exe,在文件上右键选择以管理员身份运行即可解决权限不足的问题。

数据库关闭和启动

shutdown immediate;立即关闭,自然关闭
startup; 启动数据

创建用户

create user user1 identified by user1;
user1是用户名,identified by user1是指定密码。
如果建错用户怎么办?可以删除
drop user user1;
如果user1下已经有用户新建的对象,这样删除会失败,要加一个参数
drop user user1 cascade;
删除用户级联删除其下的对象

权限设置

刚创建的用户没有任何权限,不能登录,不能操作数据库,权限可以是单独的权限,具体的权限,也可以角色,角色是若干权限的集合,Oracle有自带的权限,connect/resource/dba,如果需要定制权限集合,我们可以自定义角色,让角色拥有自己的权限。
grant connect to user1;
给user1指定connect的角色(可以登录)
grant resource to user1
resource可以创建数据库对象,但是没有登录权限,不能创建会话。
grant dba to user1;
dba权限是最高的
回收权限:
revoke dba from user1;
查看用户有哪些权限或角色:
select * from dba_role_privs where grantee=‘用户名’;
要用dba角色登录执行上面的SQL

导入导出

oracle11g数据库导入导出:

  • 传统方式——exp(导出)和(imp)导入:
  • 数据泵方式——expdp导出和(impdp)导入;
  • 第三方工具——PL/sql Develpoer;
    重点学1和3
    一、什么是数据库导入导出?
    oracle11g数据库的导入/导出,就是我们通常所说的oracle数据的还原/备份。
    数据库导入:把.dmp 格式文件从本地导入到数据库服务器中(本地oracle测试数据库中);
    数据库导出:把数据库服务器中的数据(本地oracle测试数据库中的数据),导出到本地生成.dmp格式文件。
    .dmp 格式文件:就是oracle数据的文件格式(比如视频是.mp4 格式,音乐是.mp3 格式);
    二、二者优缺点描述:(没有实测,从资料中得来)
    1.exp/imp:
    优点:代码书写简单易懂,从本地即可直接导入,不用在服务器中操作,降低难度,减少服务器上的操作也就 保证了服务器上数据文件的安全性。
    缺点:这种导入导出的速度相对较慢,合适数据库数据较少的时候。如果文件超过几个G,大众性能的电 脑,至少需要4~5个小时左右。
    2.expdp/impdp:
    优点:导入导出速度相对较快,几个G的数据文件一般在1~2小时左右。
    缺点:代码相对不易理解,要想实现导入导出的操作,必须在服务器上创建逻辑目录(不是真正的目录)。我们 都知道数据库服务器的重要性,所以在上面的操作必须慎重。所以这种方式一般由专业的程序人员来完 成(不一定是DBA(数据库管理员)来干,中小公司可能没有DBA)。
    3.PL/sql Develpoer:
    优点:封装了导入导出命令,无需每次都手动输入命令。方便快捷,提高效率。
    缺点:长时间应用会对其产生依赖,降低对代码执行原理的理解。
    三、特别强调:
    目标数据库:数据即将导入的数据库(一般是项目上正式数据库);
    源数据库:数据导出的数据库(一般是项目上的测试数据库);
    1.目标数据库要与源数据库有着名称相同的表空间。
    2.目标数据在进行导入时,用户名尽量相同(这样保证用户的权限级别相同)。
    3.目标数据库每次在进行数据导入前,应做好数据备份,以防数据丢失。
    4.使用数据泵时,一定要现在服务器端建立可用的逻辑目录,并检查是否可用。
    5.弄清是导入导出到相同版本还是不同版本(oracle10g版本与oracle11g版本)。
    6.目标数据导入前,弄清楚是数据覆盖(替换),还是仅插入新数据或替换部分数据表。
    7.确定目标数据库磁盘空间是否足够容纳新数据,是否需要扩充表空间。
    8.导入导出时注意字符集是否相同,一般Oracle数据库的字符集只有一个,并且固定,一般不改变。
    9.导出格式介绍:
       Dmp格式:.dmp是二进制文件,可跨平台,还能包含权限,效率好;
       Sql格式:.sql格式的文件,可用文本编辑器查看,通用性比较好,效率不如第一种,
    适合小数据量导入导出。尤其注意的是表中不能有大字段 (blob,clob,long),如果有,会报错;
       Pde格式:.pde格式的文件,.pde为PL/SQL Developer自有的文件格式,只能用PL/SQL Developer工具
    导入导出,不能用文本编辑器查看;
    10.确定操作者的账号权限。
    四、二者的导入导出方法:
    1、传统方法:
    通用命令:exp(imp) username/password@SERVICENAME:1521 file=“e:\temp.dmp” full = y;
    数据库导出举例:
    exp user1/user1@127.0.0.1:1521 file=“e:\temp.dmp” full = y;

exp:导出命令,导出时必写。
imp:导入命令,导入时必写,每次操作,二者只能选择一个执行。
username:导出数据的用户名,必写;
password:导出数据的密码,必写;
@:地址符号,必写;
SERVICENAME:Oracle的服务名,必写;
1521:端口号,1521是默认的可以不写,非默认要写;
file=“e:\temp.dmp” : 文件存放路径地址,必写;
full=y :表示全库导出。可以不写,则默认为no,则只导出用户下的对象;
方法细分:
1.完全导入导出:
exp(imp) username/password@SERVICENAME:1521 file=“e:\temp.dmp” full = y;
2.部分用户表table导入导出:
exp(imp) username/password@SERVICENAME:1521 file=“e:\temp.dmp” tabels= (table1,table2,table3,…);
3.表空间tablespaces导入导出:
//一个数据库实例可以有N个表空间(tablespace),一个表空间下可以有N张表(table)。
exp(imp) username/password@SERVICENAME:1521 file=“e:\temp.dmp” tablespaces= (tablespace1,tablespace2,tablespace3,…);
4.用户名username对象导入导出:
exp(imp) username/password@SERVICENAME:1521 file=“e:\temp.dmp” owner(username1,username2,username3);

OracleJOB

恢复数据

SQL

SQL (Structured Query Language:结构化查询语言) 用于管理关系型数据库,可以定义数据库对象,操作表中数据等。
SQL 在1986年成为 ANSI(American National Standards Institute 美国国家标准化组织)的一项标准,在 1987 年成为国际标准化组织(ISO)标准。
SQL是数据库比较重要的知识,可以分为:

DDL

Data Definition Language
所有的对数据库对象的操作语句
create database , alter database , create table ,alter table drop table,create index等

表空间

表空间是存储数据的地方,存储数据库对象的地方,在磁盘上对应有数据文件。

Oracle中默认有一个系统表空间,和临时表空间。如果创建用户不指定用户的表空间则默认把用户创建在系统表空间。系统表空间类似于Windows的C盘(系统盘),如果把所有软件安装在C盘是不合理的,Oracle中把用户分配在系统表空间也是不合理的。最理想的是一个用户一个表空间,在删除用户时可以删除表空间,这样管理比较好。

临时表空间是数据库操作时临时使用,如排序去重复,统计等大数据时使用,小规模可以在内存中完成 。
保证用户有创建表空间的权限:grant create tablespace to user1;
create tablespace tabsp1 datafile ‘data1.dbf’ size 1m;
执行完这条SQL,在数据库目录下创建一个data1.dbf文件
F:\app\DELL\product\11.2.0\dbhome_1\database\DATA1.DBF
如果表空间满了可以扩容,扩容表空间文件需要用户有相应的权限:
grant alter database to user1;(用sys用户登录分配权限)
alter database datafile ‘data1.dbf’ resize 2m; 可以观察data1.dbf已经变大了
以上是一种方法,还可以用下面的方法修改表空间:
grant alter tablespace to user1;先保证用户有相应权限
alter tablespace tabsp1 add datafile ‘data2.dbf’ size 1m;增加一个数据文件给表空间
以上是正常数据表空间,还可以创建临时表空间,正常数据存放正常数据表空间,临时表空间供数据库存放临时数据用。Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。
create temporary tablespace tmpsp1 tempfile ‘tmp1.dbf’ size 2m;创建表空间语句
创建用户时指定表空间,如果没有指定表空间时,创建的用户使用的是系统表空间,不建议使用系统表空间,如果用户使用自己的表空间时,可以在删除用户时把表空间也删除,这样比较彻底。当然,可以多个用户共用一个表空间,这个另当别论。
create user user2 identified by user2 default tablespace tabsp1 temporary tablespace tmpsp1;
以上语句是创建user2用户指定密码和默认表空间临时表空间。

删除表空间:

--删除空的表空间,但是不包含物理文件
drop tablespace tablespace_name;
--删除空表空间,包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间,但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除非空表空间,包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段,就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

建表前要先了解Oracle的数据类型,因为要为列指定合适的数据类型,数据类型有以下几种:

类型说明
number数字类型
integer整数(在程序中用)
char定长字符型
varchar变长字符型,最大4000
date日期类型
TIMESTAMP时间戳(可精确到毫秒)
blob二进制数据(Binary Large Object)保存音视频4G
clob字符型数据(Character Large Object)保存文档,支持定长和变长字符集
bfile二进制文件(文件在数据库外的磁盘上)

char和varchar:
关于varchar长度的说明,varchar和char的区别是char长度是固定的,内容不够列的宽度也会占用列的宽度,比如一个字段是char型,长度是20,username char(20),则无论username内容是多少,都会占用20个固定的宽度,就算username是一个字符也是如此,如果某些列的数据长度是比较固定某个长度的,则最好使用此类型。varchar的长度是变化的,如果实际数据内容不足列的宽度则只占用合适的空间来存放数据,会大大地节省数据库空间。但是,varchar更适合列比较大的,如果varchar(2)这样列比较小的就没有意义了,它反而还要花费额外的空间来记录该列的数据实际长度,数据库在处理varchar列时要进行额外的计算处理,并存储数据的实际长度,在效率上不如char性能高。
varchar最大4000。这个4000可能是4000字节也可能是4000字符,取决于参数NLS_LENGTH_SEMANTICS的设置,这个参数有两个选项,BYTE,CHAR,如果是BYTE则是4000字节,可以放4000个英文和数字,但汉字或其他字符就不一定了,肯定的是只会小于4000,如果是汉字占三个字节,则能放4000/3个字符。
alter session set NLS_LENGTH_SEMANTICS=‘CHAR’;可以修改这个参数 =‘CHAR’ 或 =‘BYTE’
number类型:
number可以指定精度,如果没有指定默认的是38位。number(5)是一个5位数据的整数,number(5,2)是一个总长为5位,小数位为2位,整数位是3位,如:123.23,123.2也可以,123.2345也可以,因为小数位会自动四舍五入为123.23,但是整数位不能随意写,如1234.1虽然整体长度是5位数字,但是除去小数位规定的2位,整数位只能是3位数,1234超出3位,所以是不符合规范的。number(5,-2)也是可以的,如果第2个参数是负数,则没有小数,并且整数位的后2位会忽略,数字的整体长度为第一个参数减去第二个参数5-(-2)=7,1234567是可以的。

结果
number(1,1)错误:1,2
正确0.1,0.11,0.15
number(3,2)错误:12,12.1
正确:1.12,1.15555
number(3,-2)错误:123456
正确:12345(结果:12300),12(结果:0)

定长和变长字符集,感兴趣的可以了解一下。

数据库的约束

数据库的约束主要是为了保证数据的有效性,从而保证了数据的安全性。约束有以下几点:

  • 主键约束
  • 唯一约束
  • 默认约束
  • 检查约束
  • 外键约束
    主键约束:保证数据的唯一性,并且主键列数据不能为空。
    唯一约束:保证数据的唯一性,唯一约束的列可以为空。表中有些列不是主键,但也是每条记录唯一的,比如学生表中的学
    号不重复,但是因为数据库设计时建议用非业务性列作主键,所以一般会用一个id作主键。
    默认约束:保证数据的完整性,如果没有填入信息时,会使用默认信息填入,保证数据的完整性。
    检查约束:保证数据的有效性,让值在有效范围内取值,如:性别[0,1],年龄[1-60]等。
    外键约束:保证数据的完整性,互相依赖的数据不能缺失。比如学生依赖班级表,班级表数据删除后,学生记录就没有了班
    级信息。外键的on delete no action这是默认的,删除父表记录时,子表有关联时会报错,set null 删除父表时会把子表相应外键列置空,cascade删除父表时级联删除子表记录。

建表:
可以在建表时指定主键或其他各种约束,也可以在建好表后,修改表时增加这些约束。
create table table_name(colname datatype[,…])

-- 最简单的表
create table t1(id number);
-- 指定id最长3位数字,username可变长,最长20
create table t2(id number(3),username varchar(20),birthday date);

修改表:
alter table tablename …

-- 给表增加列
alter table t1 add c3 char(10);
-- 给表增加主键,主键没有指定名称
alter table t1 add primary key(c1);
-- 删除刚才增加的主键约束
alter table t1 drop primary key;
-- 给表增加主键,指定主键名称
alter table t1 add constraint t1_pk primary key(c1);
-- 删除指定名称的主键
alter table t1 drop constraint t1_pk;

如果t1表中的c3列是char型,长度是2
insert into t1(c3) values(‘1’);
c3列长度是2,插入的值是1个长度,但因为列类型是char型,所以实际长度变成了‘1 ’值中有空格。
修改列的宽度时,不能小于2。
alter table t1 modify c3 char(3);

-- 创建表时没有指定任何约束
create table t_user(id char(32),name char(10),password char(8),sex number(1),age number(2));
-- 添加主键约束
alter table t_user add constraint pk_user primary key(id);
-- 添加唯一约束
alter table t_user add constraint unq_user unique(name);
-- 添加列的默认值
alter table t_user modify password default '12345678';
-- 添加表的检查约束
alter table t_user add constraint chk_user_sex check(sex=0 or sex=1);

删除表:
drop table tablename;

drop table t1;

附加:truncate

1.truncate使用语法
truncate的作用是清空表或者说是截断表,只能作用于表。truncate的语法很简单,后面直接跟表名即可,例如: truncate table tbl_name 或者 truncate tbl_name 。

执行truncate语句需要拥有表的drop权限,从逻辑上讲,truncate table类似于delete删除所有行的语句或drop table然后再create table语句的组合。为了实现高性能,它绕过了删除数据的DML方法,因此,它不能回滚。尽管truncate table与delete相似,但它被分类为DDL语句而不是DML语句。

2.truncate与drop,delete的对比
上面说过truncate与delete,drop很相似,其实这三者还是与很大的不同的,下面简单对比下三者的异同。

truncate与drop是DDL语句,执行后无法回滚;delete是DML语句,可回滚。
truncate只能作用于表;delete,drop可作用于表、视图等。
truncate会清空表中的所有行,但表结构及其约束、索引等保持不变;drop会删除表的结构及其所依赖的约束、索引等。
truncate会重置表的自增值;delete不会。
truncate不会激活与表有关的删除触发器;delete可以。
truncate后会使表和索引所占用的空间会恢复到初始大小;delete操作不会减少表或索引所占用的空间,drop语句将表所占用的空间全释放掉。

每个oracle表包含一个指示器,叫High Water Mark,它存储在段头中,标志表曾存储的最大数据量。在数据装载到表以前,表为空,HWM指向表的开始。随着数据插入到表中,HWM跟着增长,但是当数据被删除时,HWK并不下降。在进行全表扫描时,Oracle将读取所有HWM之下的数据块,不管这些数据块中是否有数据。例如一个表中High Water Mark有1000个数据块,其中有500个数据块因为被删除而没有数据,但对表的全表扫描将读取1000个数据块,对数据库的查询性能有非常大的影响。

DML

Data Manipulation Language
对数据的操作,增删改
insert into table,update table,delete from table
insert into table_name [(c1[,…])] values(v1[,…])

-- 只写了id和name列,其他列没有指定值
insert into t_user(id,name) values('a1b1','admin');
-- 如果没有指定列,只有values,则values的顺序要按钮列的顺序来写
-- id,name,password,sex,age
insert into t_user values('a1b1','admin','12345678',0,10);

update table_name set col1=v1[,…] [where col=v]

-- 更新数据指定id为'a1b1'的记录
update t_user set name='admin_new',age=11 where id='a1b1';

delete from table_name [where col=v]

delete from t_user;

这里有一个truncate的操作,此操作也是针对表记录的,是清空表中的所有记录,而不是delete所有,它们的区别是:delete删除记录后不会释放表所占的空间,在给表中增加记录时,有一个数据指针会依次变化增高位置,delete后这个指针并没有回落,以前数据占用的空间还在,数据也在,只是做了逻辑删除,我们看不到数据而已,但是通过数据恢复操作还可以恢复这些数据,有点类似Windows操作系统的回收站情形。但是truncate操作表后,会立即释放表空间,就像清空回收站的操作一样,所以数据是不可再恢复的。

DQL

对数据的查询, 这个是SQL的主要内容,有单表查询,多表查询,嵌套查询,排序 ,分页,去重,结果合并等内容。
select col1[,…] from table_name [ where col1=xx,…]
SQL查询语句可以很简单也可以很复杂

单表查询

scott是Oracle一个测试账号,默认是锁定,要解锁,解锁完了要修改密码(强制)

scott下面有几张表供我们DQL使用

alter user scott account unlock;解锁账号

scott是Oracle一个职员的名称,它家的猫叫老虎tiger

scott的密码默认是tiger,但是这个账号默认是锁定的,需要解锁。

  • 简单查询
    select * from t_user;
    这是最简单的SQL语句,查询所有列时可以用 "*"来代替,但是,在规范上不建议使用*查询,这样会让数据库去数据仓库获取字段信息然后执行查询,在性能上会有所降低,不如下面的SQL效率高:
    select id,name,password,age,sex from t_user;
    SELECT * FROM USER_TABLES T 获取当前用户下的所有的表信息

SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ‘T_CLAZZ’;

所有当前 表的所有字段信息

  • 别名
    对一些字段比较长或是经过运算得出的查询结果可以用别名作为查询结果显示,如:
    select sal,sal+100 from emp;
    emp表是Oracle自带用户scott下的一张表,scott用户下的表和数据可以供我们学习DQL来使用,此处的这条SQL是查询emp(员工表)中的所有员工工资和加100元后的结果,第二列sal+100作为结果列的显示可以使用别名,别名是在列或表达后加as关键字或不加也可以
    select sal,sal+100 as sal2,sal+100 sal3 from emp;
    以上两种写法都可以。

  • 条件过滤
    如果只要查部分数据,可以使用where条件进行过滤
    select empno,ename,sal from emp where sal>=3000
    查询员工工资大于等于3000的员工的编号和姓名及工资信息

  • 去重
    查看emp表中部门共有几个
    select deptno from emp;
    得到的数据会有大量重复的,如果去重可以用distinct关键字
    select distinct deptno from emp;

    distinct关键字会对它后面跟着的所有字段进行过滤

    没有过滤之前有14条记录,其中标红色的是重复的,加上distinct后,变成了12条

  • 排序
    order by 字段名[,…] [ asc|desc] 默认是升序asc,desc是降序
    按照工资进行由高到低排序:
    select sal from emp order by sal desc;
    order by 可以同时使用多个字段,如:部门升序,sal降序

  • 分页
    有时表中记录过多,不想全部查询出来,如果是在网页上加载表中上千万条数据,会是一个非常不好的用户体验,此时可以使用分页技术,一次显示一页数据,然后逐次分页显示。Oracle中分页用到一个伪列(物理不存在的列,Oracle对结果进行计算得到的临时列)rownum来进行分页处理。如果一个查询结果是14条,则rownum从1到14显示。
    select empno,rownum from emp;

如果一页显示3条记录,则分页的SQL可以这样写:

SELECT * FROM (SELECT empno,ROWNUM NO FROM emp )t WHERE t.no>(4-1)*3
AND t.no<=4*3;
SELECT t2.* FROM (SELECT t1.*,ROWNUM rn FROM 
                    (SELECT empno FROM emp ORDER BY empno) t1
                 ) t2 WHERE t2.rn>9 AND t2.rn<=12;
SELECT empno,ROWNUM NO FROM emp

以上SQL中第三条是没有分页,可以看到empno在表中本身是升序的,rownum从1到14
以上第一条SQL是分页的,没有排序,结果也是正确的,第二条SQL是分页后的排序,结果和第一条SQL一样。
如果排序字段是sal,结只能用第二条SQL,因为只有这样才能保证是排序后的分页,第一条SQL不适用,排序sal后,rownum就乱了,分页的结果不正确。
&n &size是两个变量,SQL语句中&用为绑定变量

SELECT * FROM
(SELECT t.*,ROWNUM rn FROM (SELECT e.* FROM emp e ORDER BY sal ) t) o
WHERE o.rn>(&n-1)*&SIZE AND o.rn<=&n*&SIZE;

因为最里面的sql是排序的,没有编号,中间一层是给编号,最外一层是条件过滤。

  • group by分组
    一般是做统计时候用的比较多,可以按照group by后面的字段进行分组
-- 显示员工表中员工的部门信息,只显示统计后的部门,不重复显示部门,有种去重的效果
-- select后面映射的字段只能是group by后面的字段,不能写别的字段,可以写统计函数(后面讲)
select deptno from emp group by deptno;
-- 显示部门和工作,按照部门和工作进行分组
select deptno,job from emp group by deptno,job;

多表查询

查询员工编号、姓名、部门编号、部门名称信息,因为部门名称不在员工表中,部门表中又没有员工信息,所以需要两张表共同完成查询,为了让员工表中的部门编号和部门表中的部门名称一一对应,需要对这两张表中的deptno员工编号字段进行关联,这儿的关联有四种关联关系:1.内连接;2.左外连接;3.右外连接;4:全外连接。

  • 内连接
    select 表1.,表2. from 表1 inner join 表2 on 表1.列1=表2.列1 inner join (inner可以省略)
    on后面的表1中的列1和表2中的列1是两个表之间的桥梁列
    select empno,ename,d.deptno,dname from emp inner join dept d on emp.deptno=d.deptno;
    select empno,ename,d.deptno,dname from emp join dept d on emp.deptno=d.deptno;
    内连接可以写成以下这种形式,这也是普通使用的形式,反而inner join使用的少。
    select empno,ename,d.deptno,dname from emp,dept d where emp.deptno = d.deptno;
    这种写法是把多表写在from关键字后面,用逗号隔开,连接条件写在where子句中。
  • 左外连接
    select 表1.,表2. from 表1 left outer join 表2 on 表1.列1=表2.列1 left outer join (outer可以省略)
    on后面的表1中的列1和表2中的列1是两个表之间的桥梁列
    可以获得left表中所有的记录,即便右表中没有和它对应的记录左表也显示。
-- 没有指定部门信息,如果用普通的内联则不会显示这条记录
insert into emp (empno,ename) values(8000,'newemp');
select empno,ename,d.deptno,dname from emp left outer join dept d on emp.deptno=d.deptno;

在上述SQL中,如果emp表中有一个员工没有设置部门信息,也会显示出来这个员工信息,只不过这个记录中只有员工信息没有部门信息。

  • 右外连接
    右外连接和左外连接原理是一样的,如果 A left outer join B,则A表是左表,B表是右表,会显示A中所有记录,B中有关联的记录,如果把B和A换一下位位置,则显示B表所有记录,A表显示关联记录,如果不换A和B的位置,还想显示B表所有记录,则把left关键字换成right就可以。
select empno,ename,d.deptno,dname from emp right outer join dept d on emp.deptno=d.deptno;
  • 全外连接
    如果说左外连接是集合1,右外连接是集合2,则全外连接就是两个集合的并集。
    关键字是full outer join,outer可以省略
select empno,ename,d.deptno,dname from emp full outer join dept d on emp.deptno=d.deptno;

– (+)在一个表后,另一个表是左表 Oracle特有的简单写法
SELECT s.id SID,s.name sname,sex,age,clazz,c.name cname
FROM t_student s ,t_clazz c WHERE s.clazz=c.id(+);

TCL

TCL(Transaction Control Language)事务控制语言
增加修改和删除数据后(DML语句)要进行commit或rollback才能使数据永久生效
commit是提交修改结果,rollback是撤销修改,恢复修改前的数据。
对事务的操作
INSERT INTO emp(empno) VALUES(5);
SAVEPOINT p1;-- 一个标志
INSERT INTO emp(empno) VALUES(6);
ROLLBACK TO p1;-- 回滚
COMMIT;

DCL

数据控制语言 (Data Control Language) 在SQL语言中,是一种可对数据访问权进行控制的指令,它可以控制用户对数据表、存储程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。
grant 角色/权限 to 用户; 分配角色或权限
revoke 角色/权限 from 用户; 回收角色或权限
对权限的操作,grant 和revoke语句
create role 角色名;
grant select on class to 角色名;

索引

索引的基本概念 [https://www.cnblogs.com/mwd-banbo/p/9022042.html]
索引是一种特殊类型的数据库对象,它与表有着密切的联系。
索引是为检索而存在的。如一些书籍的末尾就专门附有索引,指明了某个关键字在正文中的出现的页码位置,方便我们查找,但大多数的书籍只有目录,目录不是索引,只是书中内容的排序,并不提供真正的检索功能。可见建立索引要单独占用空间;索引也并不是必须要建立的,它们只是为更好、更快的检索和定位关键字而存在。
再进一步说,我们要在图书馆中查阅图书,该怎么办呢?图书馆的前台有很多叫做索引卡片柜的小柜子,里面分了若干的类别供我们检索图书,比如你可以用书名的笔画顺序或者拼音顺序作为查找的依据,你还可以从作者名的笔画顺序或拼音顺序去查询想要的图书,反正有许多检索方式,但有一点很明白,书库中的书并没有按照这些卡片柜中的顺序排列——虽然理论上可以这样做,事实上,所有图书的脊背上都人工的粘贴了一个特定的编号①,它们是以这个顺序在排列。索引卡片中并没有指明这本书摆放在书库中的第几个书架的第几本,仅仅指明了这个特定的编号。管理员则根据这一编号将请求的图书返回到读者手中。这是很形象的例子。

索引分类:聚集索引和非聚集索引 (注意:聚集索引会改变记录的物理位置)
从形式上而言,索引分为聚集索引(Clustered Indexes)和非聚集索引(NonClustered Indexes)。
聚集索引—相当于书籍脊背上那个特定的编号。如果对一张表建立了聚集索引,其索引页中就包含着建立索引的列的值(下称索引键值),那么表中的记录将按照该索引键值进行排序。比如,我们如果在“姓名”这一字段上建立了聚集索引,则表中的记录将按照姓名进行排列;如果建立了聚集索引的列是数值类型的,那么记录将按照该键值的数值大小来进行排列。
非聚集索引----用于指定数据的逻辑顺序,也就是说,表中的数据并没有按照索引键值指定的顺序排列,而仍然按照插入记录时的顺序存放。其索引页中包含着索引键值和它所指向该行记录在数据页中的物理位置,叫做行定位符(RID:Row ID)。好似书后面的的索引表,索引表中的顺序与实际的页码顺序也是不一致的。而且一本书也许有多个索引。比如主题索引和作者索引。

索引优点和不足

索引缺点:1:建立索引,系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。2:更新数据的时候,系统必须要有额外的时间来同时对索引进行更新,以维持数据和索引的一致性——这就如同图书馆要有专门的位置来摆放索引柜,并且每当库存图书发生变化时都需要有人将索引卡片重整以保持索引与库存的一致。

索引优点:在海量数据的情况下,如果合理的建立了索引,则会大大加强SQLS执行查询、对结果进行排序、分组的操作效率。
当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度
建立索引时一定要在“加快查询速度”与“降低修改速度”之间做好平衡,有得必有失,此消则彼长。

内置函数

在学习函数时有必要认识Oracle的一个特殊表dual(虚表),这个表不存在物理的表,只是方便用来测试或使SQL语句完整,通常不需要指定具体的业务表时,可以跟在from后面使SQL完整。
select 表达式 from dual;

单行函数

字符函数

函数说明
ascii(x)返回x的ascii值
select ascii(‘a’) from dual;-- 97
concat(x,y)返回x和y的拼起来的字符串
select concat(‘a’,‘b’) from dual;–ab
instr(strObj,strSearch[,start[,n]])在strObj中查找strSearch内容,从start位置开始找起,返回第n个结果的索引,start和n默认为1,可以不指定
select instr(‘a1a2a3a4’,‘a’) from dual;–1 索引从1开始
select instr(‘a1a2a3a4’,‘a’,2) from dual;-- 从索引2开始找a,结果是3
select instr(‘a1a2a3a4’,‘a’,2,2) from dual;-- 从索引2开始返回第二个结果,索引为5
length(x)x的长度
select length(‘abc’) from dual;-- 3
lower(x)x的小写形式
select lower(‘aBcD’) from dual;-- abcd
upper(x)x的大写形式
select upper(‘aBcD’) from dual;-- ABCD
ltrim(x[,str])去掉左边的字符串,默认是空格
select length(ltrim(’ abc’)) from dual;-- 3,因为结果是’abc’去掉了左边的空格
select length(ltrim(’***abc’,’*’)) from dual; – 去掉字符串中的* 结果为3
rtrim(x[,str])类似于ltrim,该函数是去掉右边的字符
trim(x[,str])去掉左右两边的字符
replace(strObj,old,new)替换字符中的old为new
select replace(‘a1a1a1’,‘1’,‘a’) from dual;-- aaaaaa
substr(strObj,star[,length])从strObj中截取字符串,star是开始位置,length可以省略,默认从star位置截取到strObj最后
select substr(‘abcdefg’,2) from dual;-- bcdefg
select substr(‘abcdefg’,2,4) from dual;-- bcde

数字函数

函数说明
abs(x)取x的绝对值
select abs(-1) from dual;-- 1
ceil(x)大于或等于x的最小值
select ceil(1.1) from dual;-- 2
select ceil(-1.9) from dual;-- -1
floor(x)返回小于或等于x的最大值
select floor(1.9) from dual;–1
select floor(-1.1) from dual;-- -2
round(x[,y])在y位上四舍五入,y可以省略,默认取值并四舍五入
select round(3.45),round(3.51),round(3.4444,2),round(3.5555,2) from dual;-- 3,4,3.44,3.56
trunc(x[,y])在y位上截断,y省略的话是取整,不会四舍五入,只是简单截断
mod(x,y)求x除以y的余数
select mod(5,2) from dual;-- 1

还有一些其它函数没有写出来,用到的话再说。

日期函数

对日期或时间的处理
sysdate在Oracle中是当前时间的关键字

select sysdate from dual;--20-12月-20 默认是这种格式,可以通过指定格式参数来显示指定格式的日期时间
函数说明
add_months(date,n)在date参数上增加一个n的月
select sysdate,add_months(sysdate,1) from dual;–20-12月-20 20-1月 -21
last_day(date)返回data参数所在月的最后一天的日期
select last_day(sysdate) from dual;–31-12月-20
round(date[,fmt])返回四舍五入的日期,fmt默认是’DDD’,月中的某一天,fmt取值可以是’YEAR’,‘MONTH’,‘DDD’,'DAY’
select sysdate,round(sysdate),round(sysdate,‘year’),round(sysdate,‘month’),round(sysdate,‘day’) from dual;这个函数相对比较复杂,后面再介绍
months_between(d1,d2)d1和d2之间的月份差
select months_between(sysdate,add_months(sysdate,2)) from dual;-- -2
next_day(date,‘星期一’)指定日期后的下一个星期一(也可以是星期二等等)
select next_day(sysdate,‘星期一’) from dual;-- 21-12月-20
extract(month from date)从指定日期中提取月份
select extract(month from sysdate) from dual;-- 12 month还可以是day,year提取日期和年
trunc(date,fmt)截断日期fmt,fmt可以是month/year,相当于只要月或年的信息
select trunc(sysdate,‘month’) from dual;-- 01-12月-20日期信息没有了,只保留到月,年也类似。

转换函数

转换数据类型或格式
to_date(datastr,fmt)
把字符串形式的日期信息按照fmt格式进行转换

-- 因为Oracle中date只显示日期,所以时间信息不显示
select to_date('2020-12-20 15:33:44','yyyy-mm-dd hh24:mi:ss') from dual;-- 20-12月-20

to_char(dataobj,fmt)
把日期对象显示为fmt指定的日期格式,还可以把数字转换为字符串形式

-- 正常情况下sysdate不显示时间信息,并且日期的格式也不是按照年月日展示
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;-- 2020-12-20 15:50:11

to_number(numberStr,fmt)
把字符形式的数字按照一定的格式转换为数字

select to_number('00123') from dual;-- 123
select to_number('12345.678', '99999.999') from dual;-- 12345.678,参数1和2要对应,参数2可以比参数1位数多,但不能反过来

统计函数

  • sum求和
-- 员工总工资
select sum(sal) from emp;
-- 员工按部门统计工资
select deptno,sum(sal) from emp group by deptno;
  • count统计
-- 每个部门的人数
select count(*) from emp group by deptno;
- 显示部门和工作及这个工作岗位的人数,按照部门和工作进行分组
select deptno,job,count(*) from emp group by deptno,job;
  • avg求平均值
-- 求每个部门员工平均工资
select avg(sal) from emp group by deptno;
  • max最大值min最小值
-- 求每个部门最高工资和最低工资
select max(sal),min(sal) from emp group by deptno;

having的过滤。

nvl和nvl2函数

-- 查询员工编号和奖金
select empno,comm from emp;

可以看到有些员工是没有奖金comm为空,但是如果只查询comm为空的记录,怎么查?

-- 查询不到任何记录
select empno,comm from emp where comm='';

为什么查不到记录呢?原因是comm为空并等于"",空是null而不是"",判断应该用is
where comm is null; 获取奖金为空的,where comm is not null;获取不为空的

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

null在Oracle中是一个特殊的值,是虚空,不存在,所以不能直接参数运算,如下面的结果是不正确的:

-- 计算所有员工的工资+奖金,结果是不正确的
select sal+comm from emp;

结果是一些没有奖金的员工连工资也不显示了。
正确的做法是当员工的comm列为空时,应该变为0参与运算,nvl函数就起这样的作用
nvl(col,value)当col为null时,返回value值,nvl(comm,0)当comm列为空时,返回0

select sal+nvl(comm,0) from emp;

NVL2(expr1,expr2,expr3)
Oracle在NVL()的功能上扩展,提供了NVL2函数。
功能:oracle中常用函数,如果参数表达式expr1值为NULL,则NVL2()函数返回参数表达式expr3的值;如果参数表达式expr1值不为NULL,则NVL2()函数返回参数表达式expr2的值。
即:NVL2(表达式,不为空值,为空值)。

select nvl2(null,1,0),nvl2('abc',1,0) from dual;-- 0  1

decode函数

decode函数是Oracle中功能非常强大的函数
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值),这个函数有switch和if的特征
成绩表

姓名成绩
赵一A
钱二B
孙三C
李四D
王五
select decode(成绩,'A','优秀','B','良好','C','普通','D','不及格','无') from 成绩表;

其结果为:

姓名成绩
赵一优秀
钱二良好
孙三普通
李四不及格
王五

decode函数配置统计函数可以实现行转列的效果,下面有这样一张表:
成绩表:

姓名科目成绩
赵一Java100
赵一C90
赵一C++95
钱二Java90
钱二C100
钱二C++99

想要把这个表中的数据转换为下面的格式:

姓名JavaCC++
赵一1009095
钱二9010099

以上是把多行转为多列,如何做到呢?用到decode和sum两个函数

-- ++是特殊字符用2代替了++
SELECT 姓名,
       SUM(DECODE(科目, 'Java', 成绩)) JAVA,
       SUM(DECODE(科目, 'C', 成绩)) C,
       SUM(DECODE(科目, 'C++', 成绩)) C2
  FROM 成绩表 GROUP BY 姓名 ;

SQL的递归

如果有这样一个需求,查出一个奶牛厂某头奶牛的所有后代或父辈

create table cow(id number,parent_id number);
-- 第一代奶牛的父非为0
insert into cow values(1,0);
insert into cow values(100,1);
insert into cow values(101,1);
insert into cow values(800,100);
insert into cow values(902,101);
insert into cow values(2,0);
insert into cow values(302,2);

以上数据是没有规律的,只能通过parent_id来找数据。这里有一个固定结构的SQL实现递归

-- 记录从id=1的开始,当前记录的parent_id等于前一条记录的id
-- 得到id为1的奶牛的所有后代
SELECT * FROM cow START WITH ID=1 CONNECT BY PRIOR ID = parent_id;

嵌套查询

嵌套查询是DQL中的难点也是重点。
它可以在SQL中任何位置出现一个子查询,或若干子查询构成一个完整查询,使得SQL语句复杂度变高。
select (子查询),(子查询),(子查询) from (子查询),(子查询)where (子查询)and (子查询)
1.用一条SQL 语句 查询出每门课都大于80 分的学生姓名

name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
2. 学生表 如下:
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同, 其他都相同的学生冗余信息
3.一个叫 team 的表,里面只有一个字段name, 一共有4 条纪录,分别是a,b,c,d, 对应四个球队,现在四个球队进行比赛,用一条sql 语句显示所有可能的比赛组合.
5.面试题:怎么把这样一个表
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
拷贝表( 拷贝数据, 源表名:a目标表名:b)
复制表( 只复制结构, 源表名:a新表名:b)
显示文章、提交人和最后回复时间
SELECT title,NAME, MAX(adddate) FROM t1 GROUP BY title,NAME
两张关联表,删除主表中已经在副表中没有的信息
有两个表A 和B ,均有key 和value 两个字段,如果B 的key 在A 中也有,就把B 的value 换为A 中对应的value
原表:
courseid coursename score

1 Java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80

为了便于阅读, 查询此表后的结果显式如下( 及格分数为60):
courseid coursename score mark

1 Java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass

序列

有时候需要一个自增的数据,这个数据可以用序列来实现
create sequence seq1;
最简单的序列器,全部采用默认值来创建。
可以看一下seq1的脚本。

-- Create sequence 
create sequence SEQ1
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;

minvalue 1最小值从1开始,maxvalue是最大值,start with从1开始,increment by 1步长为1值会不断变大,如果为负的,则值会不断变小,cache 20序列器在内存中事先缓存20个值,调用时直接从缓存中取值,提高效率,cache的值要比1大,并且它的最大取值有一个公式:(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)要小于等于这个值

alter sequence SEQ1 cycle; -- 修改序列器,当序列器的值到最大值后返回最小值开始增长,循环使用。

取序列的值可以用:序列器.currval取当前值,序列器.nextval取下一个值,值会依次变化,currval不管取多少次,值都不会变。
SELECT seq1.currval FROM dual;值不会变
SELECT seq1.nextval FROM dual;值会变
使用场景:
序列可以使用在PL/SQL程序中,可以使用在SQL语句中

视图

视图view,也叫虚表,不是真实存在的表,视图只是一个SQL的存储,查看视图时,是这个SQL的执行。比如要执行一个SQL,而这个SQL比较复杂,每次都写就比较麻烦,现在把这个SQL命名为一个视图的名称,以后只要select * from 视图就可以,大大 减化查询 的过程,这是视图的一个优点;另一个优点是视图可以有效利用权限来达到访问数据的目的,比如系统中有三张表,这三张表比较重要不能向程序员开放,而程序员又需要从三个表中获取部分数据,此时就可以利用视图来完成这三张表的部分数据访问,同时对程序员开放的是视图的名称,而不是真实的表名,即保护了数据的安全,又提供了表中的数据,这就是视图的优点;
创建视图需要有相应的权限:create view
我们知道视图的数据实际显示的是构成它的表中的数据,这些构成视图的表就叫基表,比如视图v1是由t1和t2组成的,则t1和t2就是v1的基表,另外,视图也可以来自于另一个视图或几个视图,或者构成视图的不仅可以是基表也可以同时来自于基表和其它视图。
视图有简单视图和复杂视图之分,简单视图是由一个基表组成,并且构成视图的SQL只是表中的列,而不存在函数计算,过滤等操作的视图,这种视图可以进行CRUD(增删改查),而这些CRUD会作用在基表上。
复杂视图或是由多基表组成,或是由复杂SQL构成,此类视图不可以进行DML更新操作。
下面看一下视图的创建:
create [ or replace ] [ force ] view [schema.]view_name
[ (column1,column2,…) ]
as
select …
[ with check option ] [ constraint constraint_name ]
[ with read only ];
create or replace如果视图不存在则创建,如果存在则修改。
force是强制的意思,无论基表是否存在,是否有基表的访问权限都创建视图。
with check option是校验插入视图的数据是否符合构成视图数据的规则,默认是不检查的,如:视图只显示age大于等于18的数据,如果有此选项,则插入age=17时就会报错,插入不成功。
with read only视图只读,不能DML操作。

CREATE TABLE t1(c1 NUMBER);
CREATE OR REPLACE VIEW v1 AS SELECT  * FROM t1 WHERE c1>10 
WITH CHECK OPTION;
--INSERT INTO v1(c1) VALUES(3);-- 不成功因为WITH CHECK OPTION
INSERT INTO v1(c1) VALUES(11);-- 成功
CREATE OR REPLACE VIEW v1 AS SELECT  * FROM t1 WHERE c1>10 
WITH READ ONLY;
--INSERT INTO v1(c1) VALUES(11);-- 失败,只读

物化视图

这部分知识是拓展知识。
物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,不论什么对视图的查询,oracle都实际上转换为视图SQL语句的查询。这样对总体查询性能的提高。并没有实质上的优点。
1、物化视图的类型ON DEMAND、ON COMMIT。
二者的差别在于刷新方法的不同,ON DEMAND顾名思义。仅在该物化视图“须要”被刷新了,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性;而ON COMMIT是说,一旦基表有了COMMIT,即事务提交。则立马刷新,立马更新物化视图,使得数据和基表一致。

客户端工具

SQLPLUS,这个可以操作数据库,并且是命令行的行式,但实际开发器使用不多,因为效率较低;
PLSQL Developer,这个是是图形化操作数据库,需要安装和取得相应的使用授权。
在这里插入图片描述
在这里插入图片描述

PLSQL关键字转大写

PL/SQL

SQL可以编写程序PL,可以定义变量,可以使用语句流程,打印信息,处理异常等。
用PLSQL Developer开发程序
在这里插入图片描述

在PLSQL中,字符串不使用双引号,用单引号。
每行代码以分号结尾
dbms_output是数据库定义的程序包,类似于Java中的类,put_line是输出信息,还有一个函数put,也是输出,但是信息在缓存中不输出,当put_line时把缓存中的数据一起输出。
如果发现代码行窗口的代码行不显示,或显示的数字字体小,可以调整一下
在这里插入图片描述

BEGIN
  dbms_output.put(1);
  dbms_output.put(2);
  -- 注掉此行代码程序不会输出内容
  dbms_output.put_line('');
end;

定义变量

declare 
  -- declare和begin中间是定义变量的地方,其他地方不能
  -- 定义变量
  -- firstName是变量名,varchar是数据类型
  firstName VARCHAR(2);
  lastName VARCHAR(2);
  -- 汉字占两个字符,1个长度不够存储
  --firstName VARCHAR(1);
BEGIN
  -- 赋值不能用等号,等号用来判断,:=才是赋值
  firstName:='张';
  lastName:='三';
  -- 两个内容拼接时用||
  dbms_output.put_line(firstName||lastName);
end;
declare 
  -- 定义变量赋初始值 用:=赋值
  username VARCHAR(10)  :='张三';
  -- 定义变量赋初始值用default赋值
  age INTEGER DEFAULT 18;
BEGIN
  dbms_output.put_line(username||'-'||age);--张三-18
end;
declare 
  -- 用户emp表的记录作为类型定义变量
  -- 通过变量可以获取变量中的各字段信息
  v_emp emp%ROWTYPE;
  -- 用表中的字段来定义变量,变量的数据类型和该字段保持一致
  -- 这样的好处是:字段数据类型改变时,变量不用修改,变量
  -- 的长度和精度随着字段来定义
  v_empno emp.empno%TYPE;
BEGIN
  v_empno:=1;
  -- 把查询结果存储到变量v_emp中
  SELECT * INTO v_emp FROM emp WHERE empno=7369;
  -- 通过.字段可以获取记录中相应字段的信息 7369-SMITH
  dbms_output.put_line(v_emp.empno||'-'||v_emp.ename);
end;
declare 
  v_deptno emp.deptno%TYPE;-- 部门编号列
BEGIN
  /*执行SQL查询,并把结果储存到变量v_deptno中,这样有一个问题要
  注意,当下面的SQL语句执行后没有结果,或结果多于一条都会抛异常,
  也就是说当且仅当结果为一条时才正确*/
  SELECT deptno INTO v_deptno FROM emp WHERE empno=7782;
  dbms_output.put_line('deptno='||v_deptno); 
end;

if语句

declare 
  -- Local variables here
  i integer;
begin
  -- if判断要写then和end if,某个变量是否为null不能用等号,要用is
  -- 不等于要用is not
  IF i IS NULL THEN
    dbms_output.put_line('i is null');-- i is null
  END IF;
end;

else if的使用

declare 
  -- Local variables here
  i integer;
begin
  i:=65;
  IF i >= 90 THEN
    dbms_output.put_line('优秀');
  ELSIF i >= 80 THEN
    -- elsif是else if的缩写
    dbms_output.put_line('良好');
  ELSIF i >= 60 THEN
    dbms_output.put_line('及格');  -- 及格
  ELSE
    dbms_output.put_line('不及格');  
  END IF;  
end;

for循环

求100以内的质数,以下代码中有用到嵌套循环

-- Created on 2021/1/4 by DELL 
declare 
  -- i和j是循环变量
  i integer;
  j INTEGER;
  flag INTEGER;-- 是否质数的标记
  prime CONSTANT INTEGER :=1;-- 定义常量,表示是质数
BEGIN
  -- 质数是大于1的自然数 
  FOR i IN 2..100 LOOP
    flag:=prime;-- 初始值为prime,默认是质数
    -- 质数是除1和自身外,不能被其它数整除,排除1后,其它一但能整除就不是质数
    -- in是关键字,说明变量是在此范围内
    FOR j IN 2..sqrt(i) LOOP
      -- MOD是数学函数,求余 
      IF MOD(i,j)=0 THEN
        flag := 0;-- 能整除,不是质数,对于一个范围内的判断一定要用一个标志量来标记结果,不能用一个判断就定结果,比如判断i=9时
                  -- 虽然9除以2不能整除,但不代表后面的数不能整除,所以不能因为9除以2不能整除就说它是质数,要所有数都不能整除才能确定
                  -- 就像在一个房间找一个叫张三的人,要问我所有人都不是张三,才能确定,不能第一个人不是张三,就说张三不在房间 
        EXIT;
      END IF;
    END LOOP;
    IF flag = prime THEN
      dbms_output.put_line(i||'是质数');
    END IF;
  END LOOP; 
end;

while循环

declare 
  -- Local variables here
  i INTEGER DEFAULT 0;
  random_num INTEGER; 
BEGIN
  -- DBMS_RANDOM程序包可以生成随机数,这个包中的value函数
  -- 可以生成[0-1)之间的小数
  random_num:=DBMS_RANDOM.value()*10;
  -- while加循环条件
  WHILE i < random_num LOOP
    dbms_output.put_line(i);
    i:=i+1; 
  END LOOP; 
end;

loop…end loop

loop…end loop格式的循环条件是在循环体中进行判断,当达到某一条件时退出循环。

declare 
  -- Local variables here
  i INTEGER DEFAULT 0;
  random_num INTEGER; 
BEGIN
  -- DBMS_RANDOM程序包可以生成随机数,这个包中的value函数
  -- 可以生成[0-1)之间的小数
  random_num:=DBMS_RANDOM.value()*10;
  LOOP
    IF i > random_num THEN
      EXIT;-- 退出循环
    END IF;
    dbms_output.put_line(i);
    i:= i +1;
  END LOOP;  
end;

总结:循环体有三种:for,while,loop

异常处理

异常的分类
系统自带异常
用户自定异常
异常的抛出
PLSQL引擎自动处理
Raise
Raise_Application_Error
异常的处理
异常的SQLCode 和 SQL Errm

RAISE exception_name;
异常的SQLCode 和 SQL Errm
NO_DATA_FOUND 使用 select into 未返回行
TOO_MANY_ROWS 执行 select into 时,结果集超过一行
ZERO_DIVIDE 除数为 0

处理

EXCEPTION
WHEN NO_DATA_FOUND THEN …

自定义异常

e_deptno_remaining exception ;
   PRAGMA EXCEPTION_INIT (e_deptno_remaining, - 22292 );
-- Created on 2021/1/24 by DELL 
declare 
  -- Local variables here
  i integer;
  myex EXCEPTION;-- 定义异常对象
   PRAGMA EXCEPTION_INIT(myex,-20000);-- 给异常对象赋值错误号标识
   myex2 EXCEPTION;
begin
 dbms_output.put_line(1);
 RAISE myex;-- 抛出异常
 -- 定义加抛出,指定错误号和错误提示信息
 --raise_application_error (-20000/*-20000——-20999*/, '自定义错误信息');
 EXCEPTION
   WHEN myex OR myex2 THEN -- 处理多个异常
      dbms_output.put_line('eore');
   --WHEN myex THEN myex只能处理一次,和上面的二选一
   --  dbms_output.put_line(2||'-'||SQLCODE||'-'||SQLERRM);
   WHEN OTHERS THEN -- 其他未处理的异常打印错误号和错误信息
       dbms_output.put_line(3||'-'||SQLCODE||'-'||SQLERRM);
end;

注:声明中的异常必定无法被当前块捕获

DECLARE

Abc number(3):='abc';

游标

游标可以看作是数据的集合,可以通过游标获取其中的值。游标也可以看作是数据集合的指针,通过游标可以获取集合中的数据。
Oracle中的游标可以分为用户自定义游标和系统游标(隐式游标),用户游标根据定义的方式不同可以分为动态游标和静态游标。
有的资料上讲游标是一个工作区,这个工作区中有存储的数据,可以通过游标的操作来获取这些数据。

名称说明
系统游标系统游标又叫隐式游标,是系统定义好的游标,一般在做DML操作时自动触发,单条的SELECT COL INTO V1 FROM TABLE也会产生一个系统游标
用户游标用户创建的游标,动态的游标和静态游标
静态游标创建游标的SQL语句是固定的
动态游标创建游标的SQL语句是动态的,带有参数或变量的

游标的主要操作有:1.打开游标;2.遍历游标;3.关闭游标;
如果游标是显示打开的,就要显示关闭,有一种简单操作是隐式打开游标,自动遍历,就不用显示关闭了。
如果是第一种方式,显示打开游标的话,需要写游标指针移动的语句,才能遍历。

 -- 以下是常用的游标操作语句
 CURSOR emp_cs is SELECT * FROM emp WHERE deptno=10;-- 定义一个静态游标
 OPEN emp_cs;-- 显示打开游标
  -- 游标指针向前移动一条记录,并把记录存储到变量中
 FETCH emp_cs INTO v_emp_row;
 -- 游标%NOTFOUND判断游标是否已经到底的属性
  IF emp_cs%NOTFOUND THEN
      EXIT;
  END IF;  
-- 如果游标的状态是打开状态  游标%ISOPEN判断游标打开的状态
  IF emp_cs%ISOPEN THEN
    -- 关闭游标  CLOSE 游标,用来关闭打开的游标
    CLOSE emp_cs;  
  END IF;    

游标一般会在程序中使用,比如存储过程、函数、触发器等。因为这些还没有介绍,此处的游标只简单的做游标使用的讲解。

静态游标

比如在PLSQL中定义一个游标,游标是emp表中10部门员工的信息,可以用以下代码实现:

declare 
  -- 定义一个静态游标,因为SQL语句是固定的,没有变数的
  CURSOR emp_cs is SELECT * FROM emp WHERE deptno=10;
  v_emp_row emp%ROWTYPE;-- emp表行对象类型
begin
  -- 显示打开游标,此时游标指针指向的是游标的头部
  OPEN emp_cs;
  LOOP
    -- 游标指针向前移动一条记录,并把记录存储到变量中
    FETCH emp_cs INTO v_emp_row;
    -- 如果游标指针已经到了游标的底部,则退出循环
    IF emp_cs%NOTFOUND THEN
      EXIT;
    END IF; 
    dbms_output.put_line(v_emp_row.empno||'-'||v_emp_row.ename);
  END LOOP;
  -- 如果游标的状态是打开状态
  IF emp_cs%ISOPEN THEN
    -- 关闭游标
    CLOSE emp_cs;
  END IF;
end;

游标的简单操作

declare 
  -- 定义一个静态游标,因为SQL语句是固定的,没有变数的
  CURSOR emp_cs is SELECT * FROM emp WHERE deptno=10;
  v_emp_row emp%ROWTYPE;-- emp表行对象类型
BEGIN
  -- 隐式打开游标,自动遍历,自动关闭
  FOR v_emp_row IN emp_cs LOOP
    dbms_output.put_line(v_emp_row.empno||'-'||v_emp_row.ename);
  END LOOP;
  IF emp_cs%ISOPEN THEN
    -- 实际上这句话并不打印
    dbms_output.put_line('游标为打开状态');
  END IF;  
end;

动态游标

动态游标要比静态游标功能强大,同时也复杂一些,它的优点是游标可以是灵活的,可以使用变量和参数,甚至SQL语句都是一个变量,动态游标和静态游标的定义方式不同。
动态游标需要先定义一个游标类型,然后通过游标类型定义一个动态游标变量,在打开游标的同时指定游标的实际SQL语句。

 declare 
  v_deptno emp.deptno%TYPE;-- 部门编号列
   -- 先定义一个动态游标类型
  TYPE dync IS REF CURSOR;
  -- 用动态游标类型定义一个动态游标变量
  emp_cs dync;
  v_emp_row emp%ROWTYPE;
BEGIN
  SELECT deptno INTO v_deptno FROM emp WHERE empno=7782;
  dbms_output.put_line('deptno='||v_deptno);
  -- 动态游标的SQL语句是动态的,不确定的 
  OPEN emp_cs FOR SELECT * FROM emp WHERE deptno=v_deptno;
  LOOP 
    FETCH emp_cs INTO v_emp_row;
    IF emp_cs%NOTFOUND THEN
      EXIT;
    END IF;  
    dbms_output.put_line(v_emp_row.empno||'-'||v_emp_row.ename);
  END LOOP;
  IF emp_cs%ISOPEN THEN
    dbms_output.put_line('游标关闭');
    CLOSE emp_cs;
  END IF;   
end;

隐式游标

隐式游标是系统定义好的,在做DML操作时会触发,一般需要得到的是影响到的行数信息。

以上图片中可以看到插入一条记录,对于emp表来说,影响的行数就是1,这个1其实就是隐式游标的结果,这个是通过界面操作看到的,如果是在程序中对表进行插入记录的操作,如何来获取操作的结果呢?

以上两个图中的操作不要提交事务,而是要回滚事务,因为只是单纯的测试,并不想影响emp表的数据,对于不提交的数据一定要回滚,否则会对表进行锁定,影响其它操作。
declare 
  -- 定义一个带参数的游标,SQL语句中用到该参数
  CURSOR emp_c(v_deptno emp.deptno%TYPE) IS SELECT * FROM emp
         WHERE deptno=v_deptno; 
  -- emp表的行类型           
  v_emp_row emp%ROWTYPE;
BEGIN
  -- 打开游标时需要指定游标的参数
  OPEN emp_c(10);
  LOOP
    FETCH emp_c INTO v_emp_row;
    -- 当游标到底后,退出循环
    EXIT WHEN emp_c%NOTFOUND;
    dbms_output.put_line(v_emp_row.empno);
  END LOOP;  
end;

存储过程

上面我们了解了程序,这些程序单独运行还可以,但是不能集成起来使用,就是程序互相调用,因为它们只是单纯的一段代码,没有存储起来,没有名称,现在我们把程序储存起来,给它起一个名称,就是存储过程,所以说,存储过程就是一段有名称的代码。

定义简单存储过程

打开一个SQL窗口
file->new->SQL window

create or replace procedure p_emp is
BEGIN
  -- 存储过程中至少要有一行有效代码
  dbms_output.put_line('hello world');
end p_emp;

以上的代码执行,即使代码有问题也可能不会报错,所以要看存储过程创建是否成功,要打开存储过程编程窗口,可以"ctrl+单击p_emp"
按住ctrl键,再单击p_emp打开编辑窗口,此时可以查看是否有报错。
还可以在对象浏览窗口查看:
在这里插入图片描述
如果P_EMP前面有红色的叉号,则说明存储过程有错误,需要排除才可以使用。

测试存储过程

在存储过程上点右键:

在弹出的菜单上点test,打开测试窗口。


可以点左上角的执行按钮来测试存储过程,通过DBMS Output来查看存储过程输出的结果
在这里插入图片描述

带参数的存储过程

/*带参数的存储过程在定义参数时要指定参数名称 参数类型in/out
入参还是出参,in可以不写,默认是入参,参数的数据类型
*/
create or replace procedure p_emp(v_deptno IN emp.deptno%TYPE) is
CURSOR emp_c IS SELECT * FROM emp WHERE deptno=v_deptno;
v_emp_row emp%ROWTYPE;
BEGIN
  FOR v_emp_row IN emp_c LOOP
    dbms_output.put_line(v_emp_row.empno);
  END LOOP;   
end p_emp;

测试窗口,因为存储过程有参数,所以在测试时要提供参数

DBMS Output查看结果

带出参的存储过程

/*可以给存储过程增加出参,出参要指定是out类型*/
create or replace procedure p_emp(
v_deptno IN emp.deptno%TYPE,
v_num OUT INTEGER) is
BEGIN
  SELECT COUNT(*) INTO v_num FROM emp WHERE deptno=v_deptno;   
end p_emp;

可以看到黄色部分的参数是存储过程的出参,通过这个出参可以看到存储过程执行的结果。 ## 程序调用存储过程 ```sql -- Created on 2021/1/5 by DELL declare -- 存储过程出参使用 v_num integer; BEGIN -- 调用存储过程,直接写存储过程名称就可以了,出参需要定义变量 p_emp(10,v_num); dbms_output.put_line('10部门人数:'||v_num); end; ```

存储过程中不建议处理事务,提交和回滚都不建议操作,如果存储过程有嵌套调用的话,让最外层程序来决定是否提交事务
比如:p_a调用p_b,p_c两个存储过程,p_b要操作数据,p_c也要操作数据,两个应该是同步的,比如:银行转账,假如p_b调用成功了,在p_c调用过程中出现了异常,则p_b中的数据不能更新到数据库中,否则会让数据不一致。而p_a可以通过p_b和p_c返回的出参来判断是否成功,如果都成功了则发让事务提交,否则回滚。
异常
exception when others then dbms_out.put_line(sqlcode);
rollback;
end delete_table;

自定义函数

自定义函数和存储过程非常类似,区别是,存储过程注重于处理某件事情,多和DML有关,而函数注重于获取某些数据,多和DQL有关。
如获取某个部门的人数:

CREATE OR REPLACE FUNCTION 函数名(参数 数据类型) 
RETURN 返回的数据类型 IS
变量名  返回的数据类型;-- 存储返回的数据
BEGIN
 -- 略 ,此处给变量赋值
  RETURN  变量名;-- 上面定义的变量
end f_num;
CREATE OR REPLACE FUNCTION f_num(v_deptno INTEGER) 
RETURN INTEGER IS
v_num INTEGER;
BEGIN
  SELECT COUNT(*) INTO v_num FROM emp WHERE deptno=v_deptno;
  RETURN v_num;
end f_num;

调用函数就像调用普通函数一样,一个SQL语句执行就可以:

函数的出参

/*v_sal_avg是函数的出参,参数类型是out
另外函数还有一个返回值*/
CREATE OR REPLACE FUNCTION f_num(
v_deptno INTEGER,-- 部门编号
v_sal_avg OUT INTEGER-- 部门平均工资
) 
RETURN INTEGER IS
v_num INTEGER;
BEGIN
  SELECT AVG(sal) INTO v_sal_avg FROM emp 
   WHERE deptno = v_deptno;
  SELECT COUNT(*) INTO v_num FROM emp WHERE deptno=v_deptno;
  RETURN v_num;
end f_num;

函数的调用,因为有出参,所以要用程序来处理,普通SQL无法处理,因为要定义变量。

declare 
  -- 如果要用程序调用函数就要有变量存储结果数据
  v_num integer;
  v_sal_avg INTEGER;
begin
  v_num:=f_num(10,v_sal_avg);
  dbms_output.put_line('10部门有'||v_num||'人,平均工资是'||v_sal_avg);
end;

触发器

触发器是附加在数据库对象上的一段代码,它指定了触发的时机和事件,如:before/after/instead of和insert,update,delete。一般在操作这些有附加触发器的对象时,用触发器记录操作日志,或进行数据备份和数据校验。
触发器会给数据库带来不稳定,因为它可能在执行时抛出异常而影响正常的数据操作,有时数据库对象上附加的触发器个数多,或流程复杂,势必会影响数据库性能,所以,一般不建议使用,除非特别清楚触发器的风险和隐患。想达到某一种目的,往往有多个替代方案。
触发器一般建立在表或视图上,也可以建立在用户或数据库层面上。触发器不能主动调用,只有在对相应对象操作时自动触发。
根据触发器作用的对象分为三种:1.DML触发器;2.替代触发器;3.系统触发器。

DML触发器

DML触发器有语句级和行级两种

语句级

语句级触发器对一个SQL操作只做一次触发,不能访问具体的行、列数据,适合记录操作日志。

  • 新建序列
    序列采用默认值,只提供名称就可以创建一个,用它来生成一个数据给一些主键赋值
    create sequence seq_id;
  • 创建操作日志表
create table T_LOGS
(
  id        NUMBER,-- 主键
  op_time   DATE, -- 操作时间
  op_user   VARCHAR2(20), -- 操作用户
  op_event  CHAR(6), -- 操作事件类型insert/update/delete
  op_target VARCHAR2(50)-- 操作的目标对象
)
  • 创建语句级触发器
-- 关键字trigger
create or replace trigger trg_emp1
  before insert or update or DELETE -- 时机和事件,事件可以是它们的组合
  on emp -- 作用的对象
declare
  v_use VARCHAR2(20);-- 操作用户
  v_event CHAR(6);-- 操作事件
BEGIN
  -- INSERTING  UPDATING DELETING USER这些都是Oracle的常量,可以直接使用
  IF INSERTING THEN
    v_event:='增加';
  ELSIF UPDATING THEN
    v_event:='修改';
  ELSIF DELETING THEN
    v_event:='删除';
  END IF;      
  INSERT INTO t_logs(id,op_time,op_user,op_event,op_target)
  VALUES(seq_id.nextval,SYSDATE,USER,v_event,'emp');
end trg_emp1;

创建完触发器可以在Objects窗口,对象浏览窗口中Triggers节点看到创建的触发器

给em表中增加一条记录,提交事务。观察日志表

行级

创建触发器

create or replace trigger trg_emp2
  before insert or update or DELETE -- 时机和事件
  on emp -- 作用的对象
  for each ROW --行级
declare
BEGIN
  -- :new是代表了新加入emp表中的数据行,可以得到每一列的值
  IF INSERTING THEN     
     INSERT INTO emp2(empno,ename,job,mgr,hiredate,sal
     ,comm,deptno) VALUES(:new.empno,:new.ename,:new.job,
     :new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno);
  END IF;   
end trg_emp1;

给emp表中增加一条记录,查看emp2表,发现成功插入新记录。
再来看一下更新操作的触发器,在上述触发trg_emp2的基础之上进行修改,增加修改和删除的处理。

create or replace trigger trg_emp2
  before insert or update or DELETE -- 时机和事件
  on emp -- 作用的对象
  for each ROW --行级
declare
BEGIN
  IF INSERTING THEN     
     INSERT INTO emp2(empno,ename,job,mgr,hiredate,sal
     ,comm,deptno) VALUES(:new.empno,:new.ename,:new.job,
     :new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno);
  ELSIF UPDATING THEN
    -- :new是新数据行
    UPDATE emp2 SET ename=:new.ename WHERE empno=:new.empno;
  ELSE 
    -- 对于没有更新操作来说,:old就是原数据行
    -- 需要通过:old来获取数据,:new在此处不适合
    DELETE emp2 WHERE empno=:old.empno;  
  END IF;   
end trg_emp1;

对emp表做更新和删除操作,观察emp2表的数据变化

DDL触发器

在scott用户下操作数据库对象时可以记录操作日志。

CREATE OR REPLACE TRIGGER trg_scott1
AFTER DDL ON scott.schema -- 对scott用户进行DDL的监控
BEGIN
  /*ora_dict_obj_nameDDL操作的对象名
  ora_dict_obj_typeDDL操作的对象类型table、view、db_name*/
  INSERT INTO t_logs(ID,op_time,op_user,op_event,op_target)
  VALUES(seq_id.nextval,SYSDATE,'scott',ora_sysevent
  ,ora_dict_obj_name||'-'||ora_dict_obj_type);
END;

建立了触发器后,不能再对t_logs表进行修改或删除,因为这也是DDL操作,而这个表正是记录这些操作的日志表,会报错。如果要对t_logs表进行修改或删除,则需要对trg_scott1的触发器进行失效操作,或删除。

恢复触发器有效,需要做如下操作:

当我在scott用户下打开trg_emp2的源代码,重新执行后,在t_logs表中增加一条记录,但是记录的事件类型是CREATE它不能识别是修改
图中是因为执行了两次,产生两条记录。

顺便说一下,对trg_scott1这个DDL触发器进行修改时不会触发DDL触发器。
测试创建一个简单的表t1,查看日志表中增加了一条日志记录。

create table t1(id integer);

日志表中产生下面的日志
30 2021/1/6 17:21:26 scott CREATE T1-TABLE

数据库级触发器

以DBA角色登录数据库,创建如下触发器,记录用户登录的日志

-- 因为是DBA角色登录,所以代码中的数据对象要有用户schema
--scott就是scott的用户schema
CREATE OR REPLACE TRIGGER trg_logon
AFTER LOGON ON DATABASE -- 对数据库登录监控  BEFORE LOGOFF可以在用户登出时记录日志
BEGIN
  -- sys_context('USERENV', 'SESSIONID')是获取用户的会话标识
  INSERT INTO scott.t_logs(ID,op_time,op_user,op_event,op_target)
  VALUES(scott.seq_id.nextval,SYSDATE,user,'LOGIN'
  ,sys_context('USERENV', 'SESSIONID'));
END;

用scott账号登录数据库,查看t_logs表可以看到刚才scott登录的日志。
33 2021/1/6 17:49:43 SCOTT LOGIN 220541

替代触发器

替代视图完成表数据的维护
instead of和before、after是三选一,所以替代触发器没有before和after选项。
create or replace trigger trg_myvw
instead of insert or update or delete
on my_vw
for each row
declare
– local variables here
begin
IF INSERTING THEN
INSERT INTO emp(empno) VALUES(:new.empno);
ELSIF UPDATING THEN
– :new是新数据行
UPDATE emp SET ename=:new.ename WHERE empno=:new.empno;
ELSE
– 对于没有更新操作来说,:old就是原数据行
– 需要通过:old来获取数据,:new在此处不适合
DELETE emp WHERE empno=:old.empno;
END IF;
end trg_myvw;

事务

首先说明,如果每次业务操作都只是针对一条SQL的话,那么事务就简单了,只要保证这条SQL成功或失败就可以,但现实时比这个复杂。
我们在对数据库数据进行更新时,为了完成一个业务操作,有时需要先后操作多条SQL,只有这些SQL都完成后,一个业务操作才算完成 ,这个过程就叫事务,它是一个逻辑单元,有开始,有结束,如果中间任何一个SQL出错,则整体都要回退,之前修改过的数据要恢复(你只需要告诉Oracle放弃修改就行),这叫事务回滚,如果所有SQL都如期完成,则为了让数据永远有效,则需要提交此次操作,这叫事务提交。事务开始后,所做的修改都只是在缓存中(这是Oracle的机制),所以回滚了不影响原来的数据;没有提交,缓存中的数据也不会真正保存到表中。事务的操作语句用到的是TCL(事务控制语句)

事务的特点

四大特点:ACID,四大特点的英文首字母的缩写

特点说明
原子性(Atomicity)事务的不可分割性。事务中的操作要么都做,要么都不做。简称:要么,要么。 假如转账是一个事务的话,给A转出给B,是一个完整的操作,不能少任何一个操作,不能只从A转出,不给B 也不能只给B,不从A转出,这样做的话,银行的钱就会多出来,或少掉。理论上,不管多少人转账多少次,银行的钱 总数是不变的。
一致性(Consistency)事务操作前和操作后,数据是有效的,一致的。事务操作前,数据库是平衡的,事务操作中是打破平衡的,事务操作后,就恢复平衡。这样数据是一致的,有效的,如果事务半途意外中断,这样数据是不一致的。
隔离性(Isolation)当多个事务并发(同时)对同一数据进行操作时,它们会有序操作,互不影响。假设事务1要修改emp表中的empno=7788记录的ename为Tom,事务2也要修改该记录的ename为Jerry,则事务2会在事务1操作完成后再执行,它们是不见面的,对于它们而言感受不到对方的存在。
持久性(Durability)事务一但提交,数据会持久保存起来,断电或重启都不会影响。

事务和锁是不分家的,没有锁就没有了事务的管理,事务的底层是依赖锁来完成的。一个开放的数据库同时有多个客户端访问同一数据,每个客户端都要修改和读取数据,没有一个很好的锁的机制,会导致数据极不安全。有了锁,事务就可以轻松实现数据的一致性。
锁的工作大致是这样的,当一个事务要对某一数据进行处理时,它向Oracle系统请求保护数据,Oracle系统把该数据用锁保护起来,该事务进行操作数据,在此期间,其它事务不能对该保护的数据进行更新。Oracle是怎么做到数据上锁呢?我们的数据在数据库中以数据块形式存在,每个数据块都有一个属性用来标识当前数据是否需要上锁,这个标识一般在数据的最前面。 当一个事务在此属性上标识它的存在时,后面来的事务观察到此信息,就知道当前有事务在工作。锁不会上升到某一个表或某几行数据上,而是保留在数据块的层面上。
锁的分类有好几种办法,以下是按照不同角度对锁的分类:
限制程序上:锁可以分为排他锁和共享锁
排他锁:exclusive lock也叫x锁,当一个事务对指定的数据库资源进行x锁的设置时,其他事务不能再对相应资源设置排他锁或共享锁。
共享锁:share lock也叫s锁,当一个事务对指定的数据库资源进行s锁的设置时, 其他事务可以对相应资源设置共享锁。
产生的时机上:锁也分为自动锁和显示锁
自动锁:当进行DML和DDL操作时系统会自动为要操作的资源获取所有需要的锁。
显示锁:用户可以通过命令或语句显示的对数据库一些资源进行锁定。
产生的操作上:DML锁DDL锁。当进行DML操作时为了保护数据的一致性,产生了DML锁。DML操作能够自动获得表级锁(TM)行级锁(TX)行级锁也叫事务锁,DML操作时先获得TM锁,然后再获得TX锁,这样有一个好处,后面来的事务如果相对某行进行x锁设置时,只要检查到TM锁就可以了,假设没有TM锁,就需要对每一行数据进行x锁的判断,效率就低了。
在数据行上只能设置x锁。如果其他事务也要更新x锁的数据时,就需要挂起等待前面的事务提交或回滚,时间长了会导致系统不稳定或死锁,这样会导致业务操作失败。
当进行DML和DDL操作时,会对表进行TM锁的设定,这样可以防止一个事务没有完成,表却不存在了或结构发生了变化,或数据被清空的异常。
悲观锁和乐观锁:
悲观锁:select … from 表 for update 会让返回的数据集锁定,无法更新。如果对上述数据集修改将会使事务阻塞,对该表DDL操作时,会报错。

在这里插入图片描述
可以看到事务被阻塞。直到for update语句中的事务提交或回滚此update才得以执行,这样会让系统效率低下。
如果不明原因发现系统繁忙,或业务无法正常提交,要检查是否有事务长期没有提交,通过以下办法找到影响系统的会话,可以考虑kill掉该会话,或找到原因。
首页以DBA角色登录Oracle

-- v$locked_object中是挂起的信息,获取会话id,再根据会话id查看挂起的用户或状态客户端机器,程序等信息
  select username,lockwait,status,machine,program from v$session where sid in
 (select session_id from v$locked_object);
 -- 查看挂起的sql语句
   select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object));
-- 查看挂起的sql的会话和序列
 SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
alter system kill session 'sid,serial#'; (其中sid=L.session_id)
-- 杀掉相应会话和序列
alter system kill session '67,2053';

分区表

SQL优化

1.写出具体的列名,不写*,减少SQL的分析时间,不用再去数据字典中找列的信息。
2.创建索引
3.对索引列的过滤时,不用函数,不要隐匿转换数据类型,不用模糊匹配开头查询
4.创建合理的表结构,可以适当对数据进行冗余,减少子查询

NoSQL

Not Only SQL不仅仅是SQL在当下成了热门,向传统的关系型数据库发起了挑战。

H2数据库

H2数据库用Java开发的产品,它的优点是小巧,可以作用程序的嵌入式数据库,相当于启动一个程序,而这个程序自带数据库,并且程序停止后,数据库消失(当然也可以让它持久化到磁盘上,这儿不做讨论),因为它是Java开发的,可以跨平台使用。

可能的问题

ALTER TABLE t1 ADD c1 NUMBER NOT NULL DEFAULT 0;-- 缺少directory关键字
ALTER TABLE t1 ADD c1 NUMBER DEFAULT 0 NOT NULL;-- 把not null放后面

补充

拷贝表( 拷贝数据, 源表名:a目标表名:b)
复制表( 只复制结构, 源表名:a新表名:b)
in not in/exists not exists
union

SQL UNION 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL

between
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 5000

pd

在这里插入图片描述
在这里插入图片描述

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页

打赏作者

会说话的java

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值