Oracle数据库概述
-
数据库基本概念
1.数据（Data）是数据库的基本对象，是描述事物的符号记 2.数据库（Database）就是存放数据的仓库。 3.关系型数据库（Relational Database，简称RDB）就是指基于关系模型的数据库 4.数据库管理系统（Database Management System ，简称DBMS）是科学组织和储存数据，对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。 5.关系型数据库管理系统（Relational Database Management System ，简称RDBMS），它通过数据、关系和对数据的约束三者组成的数据模型来存放和管理数据。1、概念设计：概念设计的主要任务是根据需求分析的结果抽象出实际应用中的实体及联系，然后画出ER图。 2、逻辑设计：逻辑设计的主要任务是把概念设计得到的ER图转化为表的结构，用到的知识主要是ER图中的联系转化为表的基本规则及范式理论。 3、物理设计：物理设计的主要任务是在具体的DBMS上把逻辑设计得到的表实现，包括物理存储规划和创建合适的索引等任务。映射约束的种类有以下三种： 1、一对一 2、一对多 3、多对多第一范式（1NF）: 1、它应该只有单个（原子）值的属性/列。 解释:表的每一列都应该是单值的，这意味着它们不应包含多个值。 2、存储在列中的值应该属于同一个值域。 解释:在每列中，存储的值必须是相同类型或类型。（对应列的属性和类型应该是相同的） 3、属性/列的唯一名称。此规则要求表中的每个列都应具有唯一的名称。 解释:这是为了避免在检索数据或对存储的数据执行任何其他操作时的混淆。 4、数据的存储顺序无关紧要。 第二范式（2NF）: 1、应该满足第一范式。 2、应该没有部分依赖。 解释:表中的属性仅依赖于主键的一部分而不是整个组合主键，这就是部分依赖关系。要删除部分依赖关系，可以重新划分表，删除导致部分依赖的属性，并将其放到适合的其他表中。 第三范式（3NF）: 1、它应该是满足第二范式。 2、它不应该具有传递依赖性。 解释:当非主键属性依赖于其他非主键属性，而不是依赖于主键，这是传递依赖。要删除传递依赖关系，可以重新划分表，删除导致传递依赖的属性，并将其放到适合的其他表中。 -
数据库分类
关系型数据库： oracle 大型 oracle公司 mysql 免费的中型数据库 原为瑞典MYSQL AB公司，被Sun公司收购，后Sun公司又被oracle公司收购 sqlserver 大型关系型数据库系统 微软 db2 sybase 金融领域 informix 通信领域 非关系型数据库 NoSQL MongoDB -
oracle数据库系统特点（美国甲骨文公司的一款关系数据库管理系统）
1.数据的大量性 2.数据的保存的持久性 3.数据的共享性 4.数据的可靠性1.信息准则---关系型DBMS的所有信息都应在逻辑上用一种方法，即表中的值显式地表示； 2.保证访问的准则 3.视图更新准则---只要形成视图的表中的数据变化了，相应的视图中的数据同时变化 4.数据物理性和逻辑性独立准则1.Oracle数据库自第5版起就提供了分布式处理能力，到第7版就有比较完善的分布式数据库功能了，一个Oracl分布式数据库由Oracle rdbms、sql*Net、SQL*CONNECT和其他非Oracle的关系型产品构成。Oracle数据库有很好的跨平台性能，可以在Windows、Linux等多个操作系统平台上使用。 -
oracle默认监听端口和启动的服务
#默认端口 1.oracle:1521 2.sql:3306 #Oracle安装完成后，会在windows系统中注册服务。主要有以下2个服务: 1.OracleOraDB12Home1TNSListener：表示监听服务，如果客户端要想连接到数据库，此服务必须启动。 2.OracleServiceORCL：表示数据库的主服务，命名规则：OracleService数据库名称。此服务必须启动，否则Oracle根本无法使用。 -
oracle的角色：connect,resource,dba
1.CONNECT角色:是授予最终用户的典型权利，最基本的权力，能够连接到ORACLE数据库中，并在对其他用户的表 有访问权限时，做SELECT、UPDATE、INSERTT等操作。 2.RESOURCE角色:是授予开发人员的，能在自己的方案中创建表、序列、视图等。 3.DBA角色:是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限 sys;//系统管理员，拥有最高权限 system;//本地管理员，次高权限 scott;//普通用户，密码默认为tiger,默认未解锁 -
基本原理介绍
物理结构:数据库中的一组操作系统文件（数据持久化) 1.数据文件：dbf文件 2.控制文件：ctl文件 3.日志文件：log文件 逻辑结构:数据库创建之后形成的逻辑概念之间的关系（图片位于下方）
-
逻辑组件:数据库==>表空间==>段==>区==>数据块==>模式:
1.表空间是数据库中最大的逻辑单位。 2.一个 Oracle 数据库至少包含一个表空间，就是名为SYSTEM的系统表空间。 3.每个表空间是由一个或多个数据文件组成的，一个数据文件只能与一个表空间相关联。 4.表空间的大小等于构成该表空间的所有数据文件大小之和。 #查看当前数据库的所有的表空间 SQL> select * from dba_tablespaces; #查看某个表空间下的所有表的信息 SQL> select * from dba_tables where tablespace_name='USERS'; #创建表空间： SQL> create tablespace ts1 datafile 'C:\oraclexe\oradata\XE\ts1.dbf' size 30m uniform size 128k; #补充:Oracle 数据库中的典型表空间是：SYSTEM 表空间 、DATA 表空间、USER 表空间、TOOLS 表空间、TEMP 表空间1.段是构成表空间的逻辑存储结构，段由一组区组成。 2.按照段所存储数据的特征，将段分为四种类型，即数据段、索引段、回退段和临时段。1.区为段分配空间，它由连续的数据块组成。 2.当段中的所有空间已完全使用时，系统自动为该段分配一个新区。 3.区不能跨数据文件存在，只能存在于一个数据文件中。1.数据块是Oracle服务器所能分配、读取或写入的最小存储单元。 2.Oracle服务器以数据块为单位管理数据文件的存储空间。1.模式是对用户所创建的数据库对象的总称。 2.模式对象包括表、视图、索引、同义词、序列、过程和程序包等。 -
数据类型：字符串类型、数值类型、日期类型、二进制类型
char(size):定长字符或字符串,固定长度的，并且当位数不够时，在其右边添加空格来补满。 size:1-2000字节，默认为1个字节 varchar2(size):变长字符，系统自动回收没使用的字节 size:1-4000字节 nchar(size):包含unicode格式数据的定长字符串。 size:1-2000字节 long:可变长字符列，最大长度限制是2GB clob:用来保存较大的图形文件或带格式的文本文件,如文本文档，以及音频、视频等非文本文件,最多放4G字节number(p,s):可以存放数据范围为10130~10126（不包含此值)，需要1~22字节不等的存储空间 P(Precison):精度缩写，表示有效数字的位数，最多不能超过38个有效数字 S(Scale):可以使用的范围为-84~127。 S正数时，表示从小数点到最低有效数字的位数 S负数时，表示从最大有效数字到小数点的位数 number:整数小数自适应,效率偏低 create table emp(salary number); number(p):整数位不超过p位,小数位四舍五入 create table emp(salary number(3)); numeric:用法等同于number带size int:用法等同于number不带sizedate:世纪、年、月、日期、小时、分钟和秒。一般占用7个字节的存储空间 #dual虚表，查看当前日期 SQL> select sysdate from dual; SQL> create table emp(name varchar2(30),birthday date); #to_date("要转换的字符串","转换的格式"),两个参数的格式必须匹配，否则会报错。 SQL> insert into emp values('tom',to_date('2018-03-05','yyyy-mm-dd')); #to_char(日期,"转换格式" ) 即把给定的日期按照“转换格式”转换。 SQL> select name, to_char(birthday,'mm-dd-yy') from emp; SQL> select to_char(birthday,'yyyy-mm-dd hh:mi:ss')from emp; timestamp:时间戳 #注:timestamp日期类型如果与数值进行加减运算会自动转换为date型，也就是说小数秒会自动去除。 SQL> select systimestamp from dual;#查看当前系统的时间戳raw(size):最大2000字节 long raw:能存储2GB 的原始二进制数据（不用进行字符集转换的数据） blob:存储非结构化的二进制数据大对象(如图形、视频剪辑和声音文件)编码格式：ORACLE XE默认编码格式是utf-8 #查看数据库的编码格式（dual:虚表） SQL> select userenv('language') from dual; 编码:把字符（或其他数据）编译成字节码，根据ASCII表转换 解码:把字节码解释为字符（或其他数据） 编解码常用格式： iso-8859-1:西欧编解码 一个英文占1个字节 GBK(国家标准，包括繁体中文)和GB2312(国家标准第2312条，只简体中文): 一个数字和英文占1个字节,一个中文占2个字节 UTF-8: 一个中文站3个字节,一个英文1个字节 有关大小写: 字段值:区分大小写 SQL> insert into emp values('TOM'); SQL> insert into emp values('tom'); #二者不同 关键词,表名,列名不区分大小写 -
SQL(structure query language)结构化查询语言
DDL(数据定义语言) - create、alter、drop 创建、更改、删除，自动提交，无需用commit提交。 DML(数据操纵语言) - insert、update、delete 这些语句需要commit才能提交。 DQL(数据查询语言) - select 查询语句不存在提交问题。 TCL(事务控制语言) - commit、rollback、savepoint 事务提交、回滚、设置保存点。 DCL(数据控制语言) - grant、revoke、lock 授予权限与回收权限语句。#1、create table:创建数据库表 #创建表:create table 表明(变量名1 类型,变量名2 类型,变量名3 类型,...); SQL> create table emp(id number(5)); SQL> create table emp2(id number(5)) tablespace ts1; #指定表空间 #2、create index:创建数据库表的索引 #SQL> create index 索引名 on 表名(列名); SQL> create index name_index on emp(name) #3、drop table:删除一个表之后,表及它所包含的所有数据都将删除,这个表所创建的任何索引也将删除 SQL> drop table employee; #4、drop index:删除数据库索引 #SQL> drop index 索引名 on 表名(列名); SQL> drop index index_name on emp; #5、truncate:截断一个表,表中包含的所有行都将被删除,但表本身将会保留 SQL> truncate table emp #补充:truncate table 在功能上与不带 where 子句的 delete 语句相同：二者均删除表中的全部行。但 truncate table 比 delete 速度快，且使用的系统和事务日志资源少。 #6、alter table:更改表结构，增加、删除列 #1)添加字段： SQL> alter table emp add(name varchar2(30)); #2)修改字段名: SQL> alter table emp rename column name to empName; #3)修改字段类型 SQL> alter table emp modify empName varchar2(100); #4)删除字段： SQL> alter table emp drop column empName; #补充sql常用语句、 #1)重命名表: SQL> rename emp to employee; #2)desc 作为降序排序的关键字,desc + 表名：显示表的详细字段 SQL> desc emp;#1、insert:添加数据到数据库中 #insert into 表名(列名1,列名2,...)values(值1,值2,...); SQL> create table emp(id number(7),name varchar2(30),sex char(1)); SQL> insert into emp values(101,'tom','m'); SQL> insert into emp values(101,'张三'，null);#null：空，代表没有值 SQL> insert into emp(id,name)values(201,'jerry');#给指定的字段添加记录值 #2、update:修改数据库中的数据 #update 表名 set 列名1=值1,列名2=值2,... SQL> update emp set sex='f'; SQL> update emp set sex='m' where name='tom';#where:引导条件过滤 练习：把员工201的，姓名改成李四，性别改成m SQL> update emp set name='lisi',sex='m' where id=201; 练习：把员工号201或101或301的性别改成f SQL> update emp set sex='f' where id=201 or id=101 or id=301;#or是或，and是且 #3、delete:删除数据库中的数据 #delete from 表名 emp SQL> delete from emp;#删库跑路大法 SQL> delete from emp2 where id=201;-
DQL(Data Query Language,数据查询语言) — select选择查询语句
#select 列名 from 表名 SQL> select * from stu; SQL> select id,age from stu; #where条件查询sql SQL> select name from stu where id=101;SQL> select name from stu where id=101; SQL> select name from stu where id>101; #orale中数据为空值不参与运算符得比较，查不到结果，所以用is null查询 SQL> select name,id from stu where age is null; SQL> select name,id from stu where age is not null; SQL> select * from stu where score between 80 and 90;#查不到score为空的 SQL> select * from stu where score>80 and socre<90; SQL> select * from users where name='tom' and password='123'; #利用or运算的sql的注入漏洞(黑客中的sql注入) SQL> select * from users where name='tom' and password='xxx'or '1'='1';SQL> select * from stu where id=100+1; SQL> select score+10*2/4 from stu;1、in:包含 SQL> select * from stu where id in(101,102,103); 2、like: 模糊查询 % :表示任意个字符，包括零个; _ :表示一个任意字符 SQL> select * from stu where name like '张%'; SQL> select * from stu where name like '_佳'; 3、distinct:过滤重复记录 SQL> select distinct name from stu; #补充说明:distinct这个关键字来过滤掉多余的重复记录只保留一条，但往往只用 它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。其原因是distinct只有用二重循环查询来解决，而这样对于一个数据量非常大的站来说，无疑是会直接影响到效率的。 4、as:别名 SQL> select name,score as point from stu; 5、order by:排序(默认为升序。空值作为排序时，按无穷大处理。) #asc:升序,desc:降序 SQL> select * from stu order by id; SQL> select * from stu order by id asc; SQL> select * from stu order by id desc; #含多个排序参数，依次排序，where与order by连用（判断score不是空值后，先按照score降序,再按照id升序） SQL> select * from stu where score is not null order by score desc,id asc;#分组查询:在带有group by子句的查询语句中，在select 列表中指定的列要么是group by子句中指定的列，要么包含聚合函数，否则就会报错 SQL> select salary from emp group by sex;#报错 SQL> select max(salary)from emp where age>20 group by sex; SQL> select max(salary),sex from emp group by sex; #分组后的排序:据男女分组，按分组后的工资排序升序 SQL> select max(salary) as s,sex from emp group by sex order by s; #group by子句中使用having 子句时，查询结果中只返回满足having条件的组。分组前可以使用where语句,分组后的条件过滤，用having，不能用where SQL> select addr,avg(salary) from emp where age>20 group by addr having avg(salary)>=6000; #关于分组的补充: 1、分组函数(聚合函数): max() min() avg() sum() count() 2、having与where子句类似，均用于设置限定条件。其区别如下: 1)where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚合函数，使用where条件显示特定的行。 2)having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数，使用having条件显示特定的组，也可以使用多个分组标准进行分组。1、行子查询（子查询的结果作为值）子查询不能包括ORDER BY子句 SQL> select name,age from emp where salary=(select max(salary) from emp); 2、行子查询 SQL> select name,age from emp where salary in(select min(salary) from emp group by addr); 3、表子查询(子查询的结果作为临时表) SQL> select * from(select name,age,salary from emp);#隐藏列:rownum(只能进行<运算) emp.*是该表全部列 SQL> select rownum,emp.* from emp where rownum <3; #分页思想:使用临时表和列的别名,把隐藏列转换为普通的列 rn SQL> select tempTable.* from (select rownum as rn,emp.* from emp) tempTable where rn>=3 and rn<=4; 分页算法: 每页显示条数:pageSize 要显示的页数:pageNum rn>=(pageNum-1)*pageSize+1 and rn<=pageNum*pageSize eg:p每页显示5条,显示第3页 ---> rn>=(3-1)*5+1 and rn<=5*3 有排序的分页算法: #eg:按id排序，分页查询，每页3条记录，查询第2页 SQL> select tempTable.* from (select rownum as rn,emp.* from emp order by id asc) tempTable where rn between (2-1)*3+1 and 2*3;1、交叉连接：表与表之间做笛卡尔积查询！根据笛卡尔积进行完全链接，会有垃圾数据 SQL> select * from emp cross join dept; SQL> select * from emp,dept; 2、内连接(inner可以省略): SQL> select * from emp inner join dept on emp.deptno=dept.deptno; 3、外连接 1)左外链接(左边的表是主表):查询结果除了返回包含连接条件的行，还包含左表a中不满足连接条件的行，其中不满足连接条件的行中b表的字段值将被置为空 #left outer join ... on SQL> select * from emp left outer join dept on emp.deptno=dept.deptno; 2)右外链接(右边的表是主表):与左外连接相反 #right outer join ... on SQL> select * from emp right outer join dept on emp.deptno=dept.deptno; 3)全连接:左右两表都会有数据 #full outer join ... on SQL> select * from emp full outer join dept on emp.deptno=dept.deptno; 4、自然连接:使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。自连接的本意就是将一张表看成多张表来做连接 #例：查询出学生和其所在班级的班长的一个 SQL> SELECT t1.s_id, t1.s_name,t2.s_id,t2.s_name FROM t_student t1 INNER JOIN t_student t2 ON t1.s_pid = t2.s_id;- 合并查询:
1、union all:使用UNION ALL,表示取A、B的合集,不过滤重复的数据行 SQL> select name from emp union all select addr from dept; 2、union:取查询结果的并集（同时会过滤掉同一个表中的重复字段）会将结果集A和结果集B进行union all运算,然后取两者交集的余集作为结果集 SQL> select name from emp union select addr from dept; 3、intersect将结果集A和结果集B进行union all运算,然后两者之间的集交集作为结果集 SQL> select name from emp intersect select addr from dept; 4、minus:取结果集A减去结果集B留下的差集,注:如果结果集A小于等于结果集B,返回空结果集. SQL> select name from emp minus select addr from dept; 如果你看不懂，请看下方合并查询的图解
-
-
TCL(Transaction Control Language,事务控制语言)
1、commit:用于提交前面所做的所有操作。 有一些操作需要手动添加commit才能够将修改提交给库，而有一些操作只需要执行系统会自动commit。 2、rollback:撤销在上一次执行COMMIT命令之后执行的所有DML操作。 3、savepoint:设置回滚点 一组DML操作指令，正常完成时一起进行提交，出现异常时一起回退 -
DCL(Data Control Language,数据控制语言) — grant、revoke、lock
#切换连接用户指令： SQL> conn sys; #查看用户： SQL> show user; #断开用户连接： SQL> disconn system; #退出： SQL> quit; #查看当前数据库（系统）的数据库名（实例） SQL> select name from v$database; #查看当前数据库下有用户 SQL> select * from dba_users; #创建新用户（最基本规则：1.一般都是字母开头2.不能有空格）： SQL> create user zhangsan identified by abc; #更改用户密码： SQL> alter user system identified by 123456;#授予zhangsan用户创建session的权限，即登陆权限，允许用户登录数据库 SQL> grant create session to zhangsan; #授予zhangsan用户使用表空间的权限 SQL> grant unlimited tablespace to zhangsan; #授予创建表的权限 SQL> grant create table to zhangsan; #授予删除表的权限 SQL> grant drop table to zhangsan; #授插入表的权限 SQL> grant insert table to zhangsan; #授予修改表的权限 SQL> grant update table to zhangsan; #授予所有权限(all)给所有用户(public) SQL> grant all to public;#撤销zhangsan用户创建session的权限，即登陆权限，允许用户登录数据库 SQL> revoke create session from zhangsan; #撤销zhangsan用户使用表空间的权限 SQL> revoke unlimited tablespace from zhangsan; #撤销创建表的权限 SQL> revoke create table from zhangsan; #撤销删除表的权限 SQL> revoke drop table from zhangsan; #撤销插入表的权限 SQL> revoke insert table from zhangsan; #撤销修改表的权限 SQL> revoke update table from zhangsan; #撤销所有权限(all)给所有用户(public) SQL> revoke all from public;#用户锁定： SQL> alter user zhangsan account lock; #用户解锁： SQL> alter user zhangsan account unlock; #共享锁:有共享锁的事务只能读数据，不能修改数据。 如果事务T对数据A加上共享锁后,则其他事务只能对A再加共享锁,不能加排他锁。 注：共享锁是表级的 #排他锁:有排他锁的事务既能读数据，又能修改数据。 如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的锁。 注：排他锁一般是行级的 #select...for update命令在将要更改的记录上放置了一个共享锁，防止其他任何用户获得同样记录上的锁。 SQL> select * from emp where id=101 for update; -
数据对象
数据对象: 1、table表 2、view视图 3、constraint约束 4、sequence序列 5、index索引 下面依次介绍表的结构: 列(column):字段（属性） 行(row): 记录的数据 伪列：隐藏列(如:rownum) 表的命名规则: 1、最好以字母开头 2、表名不能重复 3、中间不能有空格 4、oracle中的预设定表明，经常用下划线，$-
视图(view)
1、提供了另外一种级别的表安全性 2、隐藏的数据的复杂性 3、简化的用户的SQL命令 4、隔离基表结构的改变 5、通过重命名列，从另一个角度提供数据1、只能修改一个底层的基表 2、如果修改违反了基表的约束条件，则无法更新视图 3、如果视图包含连接操作符、 distinct 关键字、集合操作符、聚合函数或 group by子句，则将无法更新视图 4、如果视图包含伪列或表达式，则将无法更新视图#查看当前用户下的所有视图 SQL> select * from user_views; #创建视图（视图与表的关联关系） SQL> create view a_view as select id,name from emp; #使用视图 SQL> select * from a_view; #重命名 SQL> rename a_view to b_view; #删除视图 SQL> drop view b_view; -
索引(index)
1、索引是与表相关的一个可选结构 2、用以提高 SQL 语句执行的性能 3、减少磁盘I/O 4、使用 CREATE INDEX 语句创建索引 5、在逻辑上和物理上都独立于表的数据 6、Oracle 自动维护索引1、标准索引 #查看当前用户下的索引 SQL> select * from user_indexes; #创建索引（根据某个表的某个列） SQL> create index a_index on emp(id); #重建索引 SQL> alter index a_index rebuild; #删除索引 SQL> drop index a_index; 2、唯一索引：唯一索引确保在定义索引的列中没有重复值，Oracle 自动在表的主键列上创建唯一索引 #使用CREATE UNIQUE INDEX语句创建唯一索引 SQL> CREATE UNIQUE INDEX x_id ON itemfile (f_id); 3、组合索引：在表的多个列上创建的索引，索引中列的顺序是任意的 #如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度 SQL> CREATE INDEX x_comp ON t_student(f_name, f_birth); 4、反向键索引 5、位图索引 6、基于函数的索引 -
序列(sequence)
-
概念：存放一组连续的数值（整数）序列是用于生成唯一、连续序号的对象，序列可以是升序的，也可以是降序的，使用create sequence语句创建序列
-
优势：在oracle数据库中主要用序列来实现主键自增的功能。
-
操作：
#通过序列的伪列来访问序列的值 nextval: 返回序列的下一个值 currval: 返回序列的当前值 #查看序列 SQL> select * from user_sequences; #创建序列 SQL> create sequence a_seq 2 start with 1 #启动项（必须大于等于最小值） 3 increment by 1 #步幅 4 maxvalue 7 #最大值 5 minvalue 1 #最小值 6 nocycle #不要循环 7 nocache; #不要缓存 #使用alter sequence语句修改序列，不能更改序列的start with参数 SQL> alter sequence seq_toys maxvalue 5000 CYCLE; #使用DROP SEQUENCE语句删除序列 SQL> drop sequence seq_toys; #使用序列 SQL> select a_seq.nextval from dual; SQL> select a_seq.currval from dual; SQL> insert into emp values(id_emp_seq.nextval,'王五',20,'m',5600,'济南'); -
-
约束(constraint)
查看所有约束 SQL> select * from user_constraints;-
主键约束(primary key):将非 NULL 约束与单个声明中的唯一约束结合在一起。它禁止多行在同一列或列的组合中具有相同的值, 并禁止值为 null。一个表或视图有且只有一个主键
-
外键约束(foreign key):当两个表包含一个或多个公共列时， Oracle数据库可以通过外键约束 强制两个表之间的关系。外键约束要求对于定义约束的列中的每个值， 必须与引用表中指定的列的值匹配。
-
唯一约束(unique):禁止多行在同一列或列的组合中具有相同的值, 但允许某些值为 null。
-
检查约束(check):要求数据库中的值符合指定条件。
-
非空约束(not null):禁止将数据库中的值为空（null）。
各约束详解
1.限制:(可以使用 check 约束替代，用 is null作为条件。) 1)不能在视图中使用 2)不能在数据库对象的属性中使用。 2.操作: #创建非空约束 SQL> create table stu01(id number(5) not null); #此时STU01作为记录值，必须大写 SQL> select * from user_constraints where TABLE_NAME='STU01'; #自定义约束名 SQL> create table stu03(id number(5)not null, name varchar2(30)constraint name_con not null); #修改非空约束 SQL> alter table stu03 modify name null; SQL> alter table stu03 modify name not null;#此时要确保表中name列 没有null的#创建唯一性约束 SQL> create table stu04(id number(5) unique, name varchar2(30) unique); #修改唯一性约束 SQL> alter table stu04 drop unique(name); SQL> alter table stu04 add unique(name); null不参与唯一性比较#创建检查性约束 SQL> create table stu05(id number(5),sex char(1) check(sex in('f','m'))); #建议使用自定义约束名的方式创建check约束，方便修改 SQL> create table stu06(id number(5),sex char(10) constraint sex_che check(sex in('f','m')); #删除检查性约束 SQL> alter table stu06 drop constraint sex_che; #添加检查性约束 SQL> alter table stu06 add constraint sex_che_add check(sex in('男','女'));#创建主键约束：一个表只能有一个主键 SQL> create table stu2(id number(5)primary key,name varchar2(30)not null); #删除主键约束： SQL> alter table stu5 drop primary key; #添加主键约束: SQL> alter table stu5 add primary key(id);-
外键约束：foreign key
外键：外键约束定义中的引用了其他表的的列或列集，一般是其他表的主键列。 引用键：外键引用的其他表的唯一键或主键。 从表或子表：包含外键的表。此表的值依赖于引用表的唯一或主键中存在的值。 引用表或父表：由子表的外键引用的表。此表的引用键确定子表中是否允许特定的插入或更新。- 操作
#创建外键约束 #先创建主表，被关联的字段必须具有唯一性，再创建从表 SQL> create table cla(id number(5)primary key,name varchar2(100)) SQL> create table stu(id number(5)primary key,name varchar2(30)not null,claId number(5) constraint claId_fk_cons references cla(id)); #删除外键约束 SQL> alter table stu drop constraint claId_fk_cons; 添加外键约束 SQL> alter table stu add constraints claId_fk_cons foreign key(claId) references cla(id); #创建外键约束，default 默认值 SQL> create table stu6(id number(5),name varchar2(30) default 'tom'); #修改外键约束 SQL> alter table stu6 modify name default null;
-
-
练习
1、创建学生表 stu: 字段名 字段类型 描述内容 默认值 约束 id number(5) 学号 主键，序列生成 name varcahr2(30) 学生姓名 非空 sex char(1) 性别 m f,m age number(3) 年龄 dateBegin date 入学日期 collegeId number(5) 所在院校 外键 2、给学生表创建序列：stu_seq: 实现学号从10001到99999自增长，递增幅度为1 3、给学生表添加字段: 字段名 字段类型 描述内容 默认值 约束 callNum number(5) 提问次数 0 callOkNum number(5) 答对次数 0 score number(3,2) 分数
-
-
PL/SQL语言（Procedural Language/SQL）：是结合了结构化查询与 Oracle 自身过程控制为一体的强大语言
-
优势：
1、支持面向对象的编程 2、获得更好的程序性能 3、使程序模块化 4、良好的移植性 5、安全性 6、丰富的逻辑关系、顺序关系、错误信息的处理 -
块语法结构
declare 声明语句... begin 执行语句... exception 异常处理语句... end; 语法解析: 1、声明部分是可选部分，由declare开始，声明执行部分所需的变量或者常量。假如，没有用到变量或者常量可以省略。 2、执行部分是由begin开始，end结束。是PL/SQL块语句的核心部分，所有的可执行的PL/SQL语句和操作变量都放在该部分。是必选的部分，不能省略。 3、异常部分是由exception开始。主要处理执行部分过程中的执行语句或者赋值操作出现错误时，就会进入该部分。是PL/SQL程序的异常处理部分，不是必须的。 eg:输出学生信息表中的某一位学生的基本信息 declare -- 声明变量 stuinfo varchar2(50); begin select '姓名:' || stuname || ' 学号:' || stuid || ' 年龄：' || age into stuinfo from stu where t.stuname = '张三'; dbms_output.put_line(stuinfo); exception when no_data_found then dbms_output.put_line('该学生在学生信息表中找不到'); end; 解释: 1、|| 是字符串连接运算 -- 是注释 2、select ... into是PL/SQL程序中对SQL查询语句给变量赋值方法。是PL/SQL程序特有的赋值语句，该赋值语句只能要求SQL语句查询出来的值只有一个，假如多个或者一个都没有回抛出异常。 3、dbms_output.put_line是Oracle系统自带的包中的过程，用来做输出流打印，经常可以用来开发PL/SQL程序时做测试用。 4、no_data_found:如果没有找到查询结果则触发 -
数据类型
1、数值类型: number(size),pls_integer,binary_integer和simple_integer 2、字符类型: char(size),varchar2(size),long 3、时间类型: date 4、布尔类型: true,false,null1、自适应列类型:%type(引用数据库中表的某列的类型作为某变量的数据类型，也可以直接引用PL/SQL程序中某个变量作为新变量的数据类型。) eg: declare ls_stuname stu.name%type; -- 通过学生姓名字段声明ls_stuname begin select stu.name into ls_stuname from stu where stu.stuid = '101'; dbms_output.put_line(ls_stuname); exception when no_data_found then dbms_output.put_line('该学生在学生信息表中找不到'); end; 2、自适应行类型:%rowtype(PL/SQL程序引用数据库表中的一行作为数据类型，即 record 类型（记录类型）表示一条数据记录。) eg: declare ls_stuinfo stu%rowtype; stuinfo varchar2(50); begin select * into ls_stuinfo from stu where id='101'; -- := 赋值运算符 stuinfo:='姓名:' ||ls_stuinfo.stuname || ' 学号:' ||ls_stuinfo.stuid || '年龄：' || ls_stuinfo.age; dbms_output.put_line(stuinfo); exception when no_data_found then dbms_output.put_line('该学生在学生信息表中找不到'); end;1、比较运算符 = 2、赋值运算符 := 3、字符串连接运算符 || -
流程控制
顺序结构我们经常使用到GOTO的关键字进行程序的跳转，goto 不能跳转到 if 语句、 case 语句、 loop 语句、或者子块中，因此，不在非不得已的情况下，不使用GOTO语句。 总结:花里胡哨没啥用！ eg: declare ls_stuinfo stuinfo%rowtype; msg varchar2(50); begin select t.* into ls_stuinfo from stuinfo t where t.stuid='SC201801006'; msg:='姓名:' ||ls_stuinfo.stuname || ' 学号:' ||ls_stuinfo.stuid || ' 年龄：' ||ls_stuinfo.age; dbms_output.put_line(msg); if ls_stuinfo.age>25 then goto flag1; else goto flag2; end if; <<flag1>> dbms_output.put_line(msg||' 年龄大于25岁'); << flag2>> null; exception when no_data_found then dbms_output.put_line('该学生在学生信息表中找不到'); end; 解释说明： 1、其中通过判断学生“张三丰”的年龄是否大于25岁，大于就跳转到flag1标志位开始继续顺序执行程序。 2、NULL在PL/SQL程序中是顺序结构当中的一种，表示不执行任何内容，直接跳过，因此，当年龄小于等于25岁，该学生年龄大于25岁的信息将不会被打印出来。if 条件1 then -- 条件1成立执行体; elsif 条件2 then -- 条件1不成立,条件2成立执行体; else -- 条件都不成立执行体; end if; -- eg:判断变量num_in范围 declare num_in number(5) begin num_in:=20 if num_in<5 then dbms_output.put_line('输入的值'||num_in||'小于5'); elsif num_in>=5 and num_in<10 then dbms_output.put_line('输入的值'||num_in||'大于等于5,小于10'); else dbms_output.put_line('输入的值'||num_in||'大于等于10'); end if; end;case 选择体 when 表达式1 then 执行体; when 表达式2 then 执行体; when 表达式3 then 执行体; ... else 表达式n then 执行体; end case; #eg:与上面类似，懒得举例了-- 通过循环体直接进行loop循环 for 循环体别名 in (SELECT 条件查询数据) loop -- 循环执行体； end loop; # 通过选择体case when区分出学生信息表中学生的各个年龄的人数 declare ls_stuinfo stuinfo%rowtype;-- 学生信息表 ls_number_26 number:=0;-- 26岁计数器 ls_number_27 number:=0;-- 27岁计数器 ls_number number:=0;-- 其它 begin -- 对学生信息表进行全表循环 for ls_stuinfo in ( select t.* from stuinfo t ) loop case ls_stuinfo.age when 26 then ls_number_26:=ls_number_26+1; when 27 then ls_number_27:=ls_number_27+1; else ls_number:= ls_number+1; end case; end loop; dbms_output.put_line('26岁：'||ls_number_26||'人，27岁：'||ls_number_27||'人,其它岁数：'||ls_number||'人'); end;-- 通过循环变量进行循环 for 循环变量 in 循环下限...循环上限 loop end loop; #eg: declare type stuinfo_type is table of stuinfo%rowtype;-- 学生信息表 ls_stuinfo stuinfo_type;-- 声明一个集合变量(学生集合变量) ls_number number:=0;-- 计数器 begin -- 赋值给学生集合变量 select * BULK COLLECT into ls_stuinfo from stuinfo; -- 对集合变量进行for循环，通过下标取值 for i in 1.. ls_stuinfo.count loop if ls_stuinfo(i).sex='1' then--性别编码为1的是男生 ls_number:=ls_number+1;--计数器加1 end if; end loop; dbms_output.put_line('男生的数量是：'||ls_number); end;-- while循环 while 条件 loop -- 循环执行体 end loop; #eg:一个简单1加到n（n=4）的代码的while循环代码 declare ls_number number:=0;--结果值 i number:=1;--计数器 begin while i<=4 loop ls_number:=ls_number+i; i:=i+1; end loop; dbms_output.put_line(ls_number); end; -
函数
PL/SQL函数主要有下面几部分组成： 1、输入部分：PL/SQL函数可以有输入参数，在调用函数时，必须给输入参数赋值。 2、逻辑计算部分：逻辑计算部分是由PL/SQL块组成业务逻辑计算部分。这部分主要是通过输入参数、表数据、SQL计算函数等进行逻辑计算得到想要的结果。 3、输出部分：通过逻辑计算部分，我们会得到一个函数的唯一返回值进行返回（函数必须要有返回值）。1、创建函数 create [or replace] function 函数名 ([p1,p2...pn]) return datatype is|as -- 声明部分 begin -- PL/SQL程序块 end 语法解析： 1、function 是创建函数的关键字。 2、p1,p2...pn是函数的入参，Oracle创建的函数也可以不需要入参。 3、return datatype:是函数的返回值的类型 4、通过is承接着PL/SQL程序块。这部分是函数的计算内容。 #eg: create or replace function a_fun return varchar2 is name_out varchar2(30); begin select name into name_out from emp where name='mary'; return name_out; end; 使用： SQL> select a_fun from dual 2、修改函数:通过 or replace 关键词对原有的函数进行编辑覆盖 3、删除函数:通过drop命令进行删除的 drop function 函数名; -
存储过程（注：触发器是一种特殊的存储过程）
一段存储在数据库中执行某块业务功能的程序模块。它是由一段或者多段的PL/SQL代码块或者SQL语句组成的一系列代码块。1、降低总体开发成本。 2、增加数据的独立性。 3、提高性能。create [or replace] procedure 过程名 ( p1 in|out datatype, p2 in|out datatype, ... pn in|out datatype ) is .... -- 声明部分 begin .... -- 过程体 end; 语法解析： 1、procedure 关键字是创建存储过程的命令。 2、create [or replace] :如果存储过程已经存在则覆盖替代原有的过程。 3、in|out ：存储过程具有入参和出参两种参数选择，in表示的是入参，out表示的是出参，在使用过程的时候，入参必须得有对应的变量传入，出参得有对应的变量接收。 4、datatype表示出入参变量对应的数据类型。 5、is后面跟着的是过程当中使用到的声明变量。 6、begin...end 中间编写的就是存储过程的具体操作。 #eg:创建一个存储过程计算学生某一个课程中成绩在班中的排名，使用存储过程进行计算，返回对应的排名 create or replace procedure sp_score_pm ( p_in_stuid in varchar2,-- 学号 p_in_courseid in varchar2, -- 课程 ID p_out_pm out number -- 排名 ) is ls_score number:=0; ls_pm number:=0; begin -- 获取该学生的成绩 select t.score into ls_score from score t where t.stuid = p_in_stuid and t.courseid = p_in_courseid; -- 获取成绩比该学生高的人数 select count(1) into ls_pm from score t where t.courseid = p_in_courseid and t.score>ls_score; -- 得到该学生的成绩排名 p_out_pm:=ls_pm+1; exception when no_data_found then dbms_output.put_line('该学生的课程：'||p_in_courseid|| '的成绩在成绩表中找不到'); end; #执行 SQL> exec sp_score_pm('SC201801001','R20180101',ls_pm);
-
SQL> drop procedure sp_score_pm;
-
游标
通过关键字CURSOR的来定义一组Oracle查询出来的数据集，类似数组一样，把查询的数据集存储在内存当中，然后通过游标指向其中一条记录，通过循环游标达到循环数据集的目的。-
游标的种类
-
显式游标：
显式游标是游标使用之前必须得先声明定义，一般是对查询语句的结果事进行定义游标，然后通过打开游标循环获取结果集内的记录，或者可以根据业务需求跳出循环结束游标的获取。循环完成后，可以通过关闭游标，结果集就不能再获取了。全部操作完全由开发者自己编写完成，自己控制1、声明游标： #声明游标是给游标命名并给游标关联一个查询结果集，具体声明语法如下： declare cursor cursor_name(游标名) is select＿statement(查询语句); 2、打开游标： #游标声明完，可以通过打开游标打开命令，初始化游标指针，游标一旦打开后，游标对应的结果集就是静态不会再变了，不管查询的表的基础数据发生了变化。打开游标的命令如下： open cursor_name; 3、读取游标中数据： #读取游标中的数据是通过fetch into语句完成，把当前游标指针指向的数据行读取到对应的变量中（record 变量）。游标读取一般和循环LOOP一起使用，用于循环获取数据集中的记录。 fetch cursor_name into record变量 4、关闭游标： #游标使用完，一定要关闭游标释放资源。关闭后，该游标关联的结果集就释放了，不能够再操作了，命令如下： close cursor_name;常用属性： 1、%notfound。表示游标获取数据的时候是否有数据提取出来，没有数据返回TRUE，有数据返回false。经常用来判断游标是否全部循环完毕 2、%found。正好和%notfound，当游标提取数据值时有值，返回TRUE，否则返回FALSE。 3、%isopen。用来判断游标是否打开。 4、%rowcount。表示当前游标fetch into获取了多少行的记录值，用来做计数用的。#eg1:创建一个游标循环打印学生信息表中学生基本信息 declare -- 定义游标 cursor cur_msg is select * from stuinfo order by stuid; -- 定义记录变量 ls_curinfo cur_msg%rowtype; begin open cur_msg;-- 打开游标 loop fetch cur_msg into ls_curinfo;-- 获取记录值 exit when cur_msg%notfound; dbms_output.put_line('学号：' || ls_curinfo.stuid || ',姓名：' ||ls_curinfo.name); end loop; close cur_msg;-- 关闭游标 end; 代码解析： 通常我们使用显式游标都是用来循环取数据集的，因此会经常如eg1一样，使用loop控制结果来搭配使用，通过游标的属性变量%notfound来获取游标的结束，跳出loop循环。#eg2:我们经常用到%rowcount作为游标的计数器，我们修改一下eg1的代码，用上%rowcount declare -- 定义游标 cursor cur_msg is select * from stuinfo order by stuid; -- 定义记录变量 ls_curinfo cur_msg%rowtype; begin open cur_msg;-- 打开游标 loop fetch cur_msg into ls_curinfo;--获取记录值 exit when cur_msg%notfound; -- 利用游标计数器打印学生个数 dbms_output.put('%rowcount计数器，第'||cur_msg%rowcount||'位学生，'); dbms_output.put_line('学号：' || ls_curinfo.stuid || ',姓名：' ||ls_curinfo.name); end loop; close cur_msg;-- 关闭游标 end; -
隐式游标：
隐式游标指的是PL/SQL自己管理的游标，开发者不能自己控制操作，只能获得它的属性信息。eg3: declare ls_msg stuinfo%rowtype; begin -- 查询学生信息 select * into ls_msg from stuinfo t where t.stuid = 'SC201801001'; if sql%found then dbms_output.put_line('学号：' || ls_msg.stuid || ',姓名：' ||ls_msg.stuname); end if; -- 查询学生信息(不存在的学生) select * into ls_msg from stuinfo t where t.stuid = 'SC201901001'; if sql%found then dbms_output.put_line('学号：' || ls_msg.stuid || ',姓名：' ||ls_msg.stuname); end if; exception when no_data_found then dbms_output.put_line('该学生SC201901001不存在'); end; 代码解析： oracle隐式游标没有像显式游标一样声明游标名，直接采用SQL名称作为隐式游标的名称。然后可以利用游标的属性，做一些逻辑判断，隐式游标的属性值和显式的一样，有%notfound、%found、%rowcount、%isopen。显式游标表示的属性值都是对结果集行数的一些判断，而隐式游标对应的就是DML语句影响的行数 -
ref游标
动态关联结果集的临时对象。即在运行的时候动态决定执行查询静态游标和REF 游标的区别是什么？ 1、静态游标是静态定义，REF 游标是动态关联； 2、使用REF 游标需REF 游标变量。 3、REF 游标能做为参数进行传递，而静态游标是不可能的。
-
-
-
触发器
-
概念
Oracle触发器是使用者对Oracle数据库的对象做特定的操作时，触发的一段PL/SQL程序代码，叫做触发器。触发的事件包括对表的DML操作，用户的DDL操作以及数据库事件等。 -
优势
Oracle触发器可以根据不同的数据库事件进行特定的调用触发器程序块，因此，它可以帮助开发者完成一些PL/SQL存储过程完成不了的问题，比如操作日志的记录、防止一些无效的操作、校验数据的正确性、限制一些对数据库对象的操作、提供数据同步的可行性。但是不推荐在触发器当中写业务逻辑程序，因为这样对后期数据的维护将大大提高成本。 -
组成
1.触发事件：引发触发器被触发的事件 DML语句(insert, update, delete语句对表或视图执行数据处理操作)、DDL语句（如create、alter、drop语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。 2.触发时间：即该触发器是在触发事件发生之前（before）还是之后(after)触发 3.触发操作：触发器触发后要完成的事情 4.触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，触发器才会执行触发操作。 5.触发条件：由when子句指定一个逻辑表达式。只有当该表达式的值为true时，遇到触发事件才会自动执行触发操作。 6.触发频率：说明触发器内定义的动作被执行的次数。即语句级(statement)触发器和行级(row)触发器。（比如delete多条数据时，行级触发器可能会执行多次，语句级触发器只会触发一次） -
类型（按照用户具体的操作事件的类型）
1、数据操作（DML）触发器 2、数据定义操作（DDL）触发器 3、用户和系统事件触发器 4、instead of 触发器 5、复合触发器 -
操作方法：
eg:创建一个简单的触发器来校验学生基本信息的正确性 create or replace trigger tr_msg_insert before insert on stuinfo for each row begin -- 对性别的数据进行校验 if :new.sex not in ('m', 'f') then raise_application_error(-20001, '性别错误,请正确选择。'); end if; end; #:new --为一个引用最新的列值; #:old --为一个引用以前的列值; #这两个变量只有在使用了关键字 "for each row"时才存在.且update语句两个都有,而insert只有:new ,delect 只有:old; 代码解析：这是一个DML触发器，是对学生信息表（stuinfo）学生数据插入（insert）之前做的一个性别的校验，当性别的值不符合规范的时候报数据错误。 禁用/启用触发器: SQL> alter trigger a_trig disable; SQL> alter trigger a_trig enable; 删除触发器: SQL> drop trigger a_trig;
-
-
包和包体（package and package body）
-
概念
将相关对象存储到一起的一种结构形式。通过包封装过程和函数，方便调用。包名和包体名必须一致，先创建包，在包体中创建过程和函数 相关对象：变量、游标、异常、存储过程、函数。 -
优点
1、模块化 2、更轻松的应用程序设计 3、信息隐藏 4、新增功能 5、性能更佳 -
创建包
create or replace procedure b_pro(name_in varchar2) is name_out varchar2(30); begin select name into name_out from emp where name=name_in; dbms_output.put_line(name_out); end;
- ##### 创建包体 ```sql create or replace package body a_pack is procedure temp_pro(name_in varchar2) #定义一个过程 is name_out varchar2(30); begin select name into name_out from emp where name=name_in; dbms_output.put_line(name_out); end; function temp_fun return varchar2 #定义一个函数 is name_out varchar2(30); begin select name into name_out from emp where name='mary'; return name_out; end; end;#执行包中的函数或过程 SQL> select a_pack.temp_fun from dual; SQL> exec a_pack.temp_pro('mary'); -
-
数据库备份和恢复
1、Oracle数据库备份与恢复：Oracle备份与恢复是数据库必备的功能，同时也是DBA必备技能。备份通常分为物理备份和逻辑备份之分，恢复又分为完全恢复和部分恢复之分。 2、物理备份和恢复数据库：介绍Oracle物理备份和恢复数据库，其中物理冷备份和热备份是如何进行的，又有何区别以及如何开启归档模式。 3、Oracle逻辑备份就是使用EXP/EXPDP工具对数据进行备份成DMP文件，Oracle逻辑恢复就是使用IMP/IMPDP工具把DMP文件中的数据导入到数据库进行恢复。主要内容包括： （1）、Oracle EXP导出：介绍使用EXP工具导出数据库对象，分别介绍是如何导出用户数据，表数据、表空间和其它数据库对象。 （2）、Oracle EXPDP导出：介绍数据泵技术EXPDP是如何导出数据库对象的。 （3）、Oracle imp导入：介绍使用IMP工具导入DMP文件进行数据恢复。 （4）、Oracle IMPDP导入：介绍使用IMPDP工具导入数据泵导出的数据文件，进行数据恢复。 4、Oracle RMAN备份和恢复：Oracle RMAN是Oracle数据库提供的一种恢复和备份的数据库工具。学习的主要内容有： （1）、Oracle RMAN介绍：介绍oracle RMAN的特点和相关数据库备份与恢复的知识概念。 （2）、RMAN恢复目录：RMAN恢复目录是使用RMAN工具进行数据库备份时要存储备份信息的数据库。 （3）、RMAN通道分配：通道分配就是确定连接数据库备份的设置个数，了解RMAN是如何利用通道分配进行数据库备份的。 （4）、RMAN备份集：备份集是用来存储备份信息，可以选择使用备份集来进行数据备份。一个备份集可以存储一个或者多个数据文件。 （5）、RMAN还原和恢复。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
