1. Oracle概述
ORACLE数据库系统是美国ORACLE公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只要在一种机型上学习了ORACLE知识，便能在各种类型的机器上使用它。
1.1 Oracle发展史
•1978年，Oracle 2诞生了，它是使用汇编语言开发的，但它的出现并没有引起太多的关注
•1982年，Oracle公司推出了Oracle 3，这是第一个能够运行在大型和小型机上的关系型数据库
•1997年,Oracle公司又推出了基于Java语言的Oracle 8
•1999年，Oracle正式提供世界上第一个Internet数据库Oracle8i
•2001年6月，Oracle公司发布了Oracle 9i
•2007年7月12日，Oracle公司推出Oracle 11g，Oracle 11g中的“g”代表“网格”
1.2 Oracle 数据库系统的特点
•支持大数据库、多用户、高性能的事务处理
•安全性好
•提供了新的分布式数据库能力
•具有可移植性、可兼容性
1.3 Oracle的安装和卸载
1.3.1 Oracle安装步骤
- 下载Oracle文件
大家可以根据自己的操作系统是多少位（32位或64位）的，到官网下载相应的安装程序，如下图所示。
有一点需要注意，Oracle的安装程序分成2个文件，下载后将2个文件解压到同一目录即可。
- 下载完成后，将2个压缩文件一起选中，鼠标右键—>解压文件，如下图所示。两个压缩文件解压到同一目录下。需要注意的是，路径名称中最好不要出现中文、空格等不规则字符。
3）打开相应的解压路径，找到安装文件“setup.exe”，双击进行安装，如下图所示：
4） 配置安全更新。电子邮件可写可不写，取消下面的“我希望通过My Oracle Support接受安全更新(W)”，如下图所示，单击下一步。
5） 安装选项。直接选择默认的“创建和配置数据库”，如下图所示，单击下一步。
6） 系统类。由于咱们安装的是服务器，所以选择“服务器类”，如下图所示，单击下一步。
7） 网格安装选项。选择“单实例数据库安装”，如下图所示，单击下一步。
8）安装类型。选择“高级安装”，如下图所示，单击下一步。
9） 产品语言。直接默认即可（简体中文、英语），如下图所示，单击下一步。
10） 数据库版本。选择“企业版”，如下图所示，单击下一步。
11） 安装位置。填入安装路径（只需要填“Oracle基目录”即可，“软件位置”会自动生成），如下图所示，单击下一步。
12） 配置类型。选择“一般用途/事务处理”，如下图所示，单击下一步。
13）数据库标识符。填入全局数据库名和SID，如下图所示，单击下一步。
14） 配置选项。切换到“字符集”选项卡，选择“使用Unicode（AL32UTF8）”，如下图所示，单击下一步。
15） 管理选项。直接单击下一步，如下图所示。
16） 数据库存储。直接单击下一步，如下图所示。
17） 备份和恢复。如果需要对数据进行备份，就启用自动备份，小编这里选择“不启用自动备份”，如下图所示，单击下一步。
18） 方案口令。为了便于测试，这里使用了相同的密码，实际部署时可根据实际情况自行决定。
19） 概要。完成先决条件检查后，单击完成就可以正式开始安装了，如下图所示，单击下一步。
20） 安装产品。安装完成后，会列出相关数据库配置清单，这个最好截图保存，如下图所示，单击确定。
21） 完成。这时安装已完成，单击关闭即可。
22） 测试一下。打开Oracle自带的SQL PLUS，如下图所示。
23） 输入用户名、密码（就是第18步设置的密码），测试成功！可以直接输入SQL语句了！需要注意的是，这里Oracle输入的口令是不显示的。
1.3.2 Windows下Oracle卸载步骤
1)关闭正在运行的Oracle所有服务
开始－＞程序－＞Oracle_OraDB11g_Home1－＞OracleInstallation Products－＞Universal Installer，单击“卸载产品”-“全部展开”，选中除“OraDb11g_home1”外的全部目录，删除。
2)删除对应注册表信息
- 删除oracle注册表信息.运行regedit,删除注册表项
HKEY_LOCAL_MACHINE\SOFTWARE\Oracle - 删除oracle服务.oracle服务在注册表中的位置是:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Service
删除所有以oracle字符打头的服务. - 删除事件日志.注册表中的位置是
HKEY_LOCAL_MACHINE\SYSTEM\CurrentContrilSet\Services\Eventlog\Application
删除所有以oracle字符打头的键.
3)删除环境变量中oracle的环境变量
删除ORACLE环境变量,如"JSERV"变量和Path变量中的oracle路径
4)删除oracle相关菜单以及目录
- 删除oracle菜单.
- 删除"program files\oracle"目录
- 重启WINDOWS.
- 删除oracle主目录.
1.4 Oracle 11g实例的启动与关闭
Windows操作系统下的Oracle服务以后台服务进程的方式进行管理
• OracleOraDb11g_homeTNSListener服务：Oracle服务器的监听程序
•OracleServiceSID：Oracle的核心服务，要启动Oracle实例，必须启动该服务
•OracleJobSchedulerSID： 任务调度服务，负责对用户创建的作业按预先设置的时间周期性地进行调度执行，从而实现Oracle服务器的自动管理功能
•OracleDBConsoleSID：负责在Windows平台下启动Oracle企业管理器。Oracle 11g企业管理器是一个功能完善的 Oracle数据库管理工具，可以管理本地数据库环境和网格环境
开始---->运行---->services.msc
1.5 Oracle数据库默认账户
新建一个数据库时，Oracle将默认创建几个用户
- SYS用户：SYS用户是Oracle中的超级用户，主要用于维护系统信息和管理实例，数据库中数据字典的所有表和视图都存储在SYS模式中
- SYSTEM用户：SYSTEM用户是Oracle中默认的管理员，它拥有DBA权限。该用户拥有Oracle管理工具使用的内部表和视图，通常通过SYSTEM用户管理Oracle数据库的用户、权限和存储等
- SCOTT用户：SCOTT是Oracle数据库的一个示范账号。SCOTT用户模式包含4张示范表，SCOTT用户的默认口令为tiger
对于日常的管理任务，建议使用SYSTEM用户登录Oracle数据库服务器。
如果需要执行备份、恢复、更改数据库的任务，就必须以SYS用户登录Oracle数据库服务器
1.6 Oracle逻辑结构和表空间
-
数据块（Data Block）
数据块是最小的数据管理单位，即Oracle中所有的I/O操作都以块为单位。数据块的大小是操作系统块大小的整数倍，常见大小为2KB或4KB
-
数据区间（Data Extent）
数据区间由物理上连续存放的块所构成，它是Oracle存储分配的最小单位，一个或多个块组成一个数据区间
-
数据段（Data Segment）
若干个数据区间构成数据段
-
表空间（Table Space）
- 为了提高数据库服务器管理和运行的效率，Oracle 11g使用“表空间”这个虚拟的概念来管理逻辑对象，用户可以将不同性质的逻辑对象存放在不同的表空间下
- 在Windows操作系统中，使用文件夹来分类管理各种文件，表空间相当于Oracle数据库的文件夹。
- 每个表空间由一个或多个数据文件组成，一个数据文件只能与一个表空间相联系
数据库相当于一个柜子， 柜子中的抽屉相当于表空间， 抽屉中的文件夹相当于表， 文件夹中的纸相当于数据区间， 写在纸上的信息相当于数据。
表空间的作用
- 可以使用表空间限制数据库文件的大小
- 利用表空间将数据文件存放到不同的磁盘上,提高IO性能，利于数据的备份和恢复
创建表空间语法：
CREATE TABLESPACE tablespacename
DATAFILE ‘filename’ [SIZE integer [K|M]]
[AUTOEXTEND [OFF|ON]];
create tablespace myspace
datafile
'e:/my01.dbf' size 5 M,
'd:/my02.dbf' size 10 M
-- 创建表时,为表指定表空间
create table person
(
pid number ,
pname varchar2(20) ,
page number(3),
birthday date,
address varchar2(20) default '不详'
)tablespace myspace;
创建建表空间，必须以dba身份登录。
conn system/admin
2.用户和权限
2.1 创建Oracle用户
同一数据库中可以同时有多个用户，每个用户管理自己的数据库对象．比如数据库表、索引、视图等。。
Oracle中的CREATE USER命令用于创建新用户。每个用户都有一个默认表空间和一个临时表空间。如果没有指定，Oracle就将SYSTEM设为默认表空间，将TEMP设为临时表空间。
创建用户语法：create user 用户名 identified by 密码 default tablespace 表空间
CREATE USER xiaoming
IDENTIFIED BY 123
DEFAULT TABLESPACE myspace
默认情况下用户处于锁定状态
解除锁定语法： alter user [username] account unlock;
alter user scott account unlock;
2.2 权限和角色
Oracle中的常用系统预定义角色如下。
- CONNECT：临时用户，特别是那些不需要创建表的用户，通常赋予该角色。
- RESOURCE：更为可靠和正式的数据库用户可以授予该角色，可以创建表、触发器、过程等。
- DBA：数据库管理员角色，拥有管理数据库的最高权限。一个具有DBA角色的用户可以撤销任何别的用户甚至别的DBA权限，这是很危险的，所以不要把该角色轻易授予一些不是很重要的用户。
给用户分配权限或角色
GRANT命令用于为用户分配权限或角色，而REVOKE命令用于为用户撤销权限和角色
分配权限或角色语法：GRANT [<权限> | <角色>] TO <用户>;
grant create session,create table to xiaoming;--为用户分配权限
grant connect to xiaoming;--为用户分配角色
grant resource to xiaoming;
--grant connect,resource to xiaoming;--为用户分配角色
--为xiaoming分配查询scott账号下的emp表的权限
-- grant select|insert|update|delete on 表 to 用户
grant select on scott.emp to xiaoming
--以xiaoming账号登录查询
select * from scott.emp
撤销权限和角色语法：REVOKE [<权限> | <角色>] FROM <用户>;
revoke create session, create table from xiaoming;
revoke select on scott.emp from xiaoming;
3. 数据类型和SQL语句
3.1 Oracle常用的数据类型
-
字符类型
-
CHAR类型：CHAR表示固定长度字符串，长度不够的用空格补充，最多可以存储2000字节
CHAR类型区分中英文，中文在CHAR中占两个字节，而英文只占一个字节
-
VARCHAR2类型：VARCHAR2表示可变长度字符串，最多可以存储4000字节
在定义该数据类型时，应该指定其大小。与CHAR类型相比，使用VARCHAR2可以节省磁盘空间
-
-
数值类型
- NUMBER类型：可以存储正数、负数、零、定点数和精度为38位的浮点数
-
NUMBER(M,N)。其中，M表示精度，代表数字的总位数；N表示小数点右边数字的位数
number(10,2): 10位有效数字精确到小数点后2位
-
日期类型
- DATE类型：用于存储表中的日期和时间数据，取值范围是公元前4712年1月1日至公元9999年12月31日，长度是7，7个字节分别表示世纪、年、月、日、时、分和秒
- TIMESTAMP类型：用于存储日期的年、月、日以及时间的小时、分和秒值，其中，秒值精确到小数点后6位，该数据类型同时包含时区信息
-
大对象类型
- CLOB大字符串对象类型 ：CLOB（Character Large Object）数据类型用于存储可变长度的字符数据，最多可存储4GB数据，用于存储VARCHAR2类型不能存储的长文本信息
- BLOB大二进制类型：BLOB（Binary Large Object）数据类型用于存储较大的二进制对象，如图形、视频剪辑和声音剪辑等，最多可以存储4GB数据
3.2 DDL语句
3.2.1 CREATE TABLE 语句
用于创建表。在创建表时，经常会创建该表的主键、外键、唯一约束、Check约束等
---创建表: create table 表名(列名1 数据类型 [约束])
create table dept
(
deptno number(6) primary key,
deptname varchar2(50) not null,
deptnum number(5) check (deptnum>=0),
loc varchar(100) default '郑州'
)
create table employee
(
empId number(10) primary key,
empName varchar2(50) not null,
empAge number(3) check(empAge>=18 and empAge<=60),
deptno number(6) references dept(deptno)
)
Oracle没有主键自增的概念，如果需要主键自增可以通过序列解决
3.2.2 ALTER TABLE语句
-
向已经创建的表中增加一个新列
语法: alter table tableName add columnName dataType;
alter table employee add mobile number(11)
-
修改表中指定列的数据类型和类型长度
语法: alter table tableName modify columnName dataType;
alter table employee modify mobile number(12)
-
删除表中指定的列
语法: alter table tableName drop column columnName;
alter table employee drop column mobile
-
给表添加约束
语法:•alter table tableName add constraint constraintName constraintType(columnName)
alter table employee add constraint UN_empName unique(empName)
- 从视图USER_CONS_COLUMNS中查看约束
select constraint_name,column_name from user_cons_columns
小练习:
-
创建表空间:zhangsan
-
创建用户:zhangsan并为该用户指定zhangsan的表空间
-
为用户zhangsan分配connect和resource角色
-
创建班级表和学生表
班级表(班级编号，班级名称) ---->班级名称添加唯一约束
学生表(学生编号,学生姓名,学生年龄,毕业院校,班级编号)—>
学生名称非空,年龄在10~30之间, 班级编号引用班级表的主键
3.2.3 序列
序列（SEQUENCE）是一个命名的顺序编号生成器，它能以串行的方式生成一系列顺序整数
序列的主要用途：
- 主键、外键值应用需求
- 流水号应用需求
- 序列的生成与定义的内容
序列语法：
CREATE SEQUENCE sequnce_name
[START WITH n1] //指定要生成的第一个序列号 (从n1 开始)
[INCREMENT BY n2] //用于指定序列号之间的间隔，默认值为1
[{MAXVALUE n3 | NOMAXVALUE}] //指定序列可以生成的最大值
[{MINVALUE n4 | NOMINVALUE}] //指定序列可以生成的最小值
[{CACHE n5 | NOCACHE}] //用于指定在高速缓存中可以预分配的序列号个数，默认为20
[{CYCLE | NOCYCLE}] //用于指定在达到序列的最大值或最小值后是否循环
[ORDER]; //用于指定按顺序生成序列号 ，确保序列唯一和有序
create sequence my_seq
start with 1
increment by 1
maxvalue 100000
minvalue 1
nocycle
cache 10
通过nextval获取下一个序列的值
select my_seq.nextval from dual
通过currval获取当前序列的值
select my_seq.currval from dual;
--创建序列
create sequence my_seq
start with 1 --从1开始
increment by 1 --序列的间隔(每次变化的数值)
maxvalue 10000000 --最大值
minvalue 1 --最小值
nocycle --不循环
cache 10
insert into dept(deptno,deptname,deptnum)values(20,'test2',2)
select * from dept
select * from dual
select my_seq.nextval from dual
select my_seq.currval from dual
select * from employee
--Oracle中没有主键自增的概念,但可以通过自定义序列来实现
insert into employee(empId,empName,empAge,deptno) values(my_seq.nextval,'zhangsan',20,10)
insert into employee(empId,empName,empAge,deptno) values(my_seq.nextval,'zhangsan2',22,10)
更改和删除序列:
#更改序列
ALTER SEQUENCE my_seq MAXVALUE 5000 CYCLE;
#删除序列
DROP SEQUENCE my_seq;
3.3 基本SQL语句
3.3.1 新增语句
insert into 表名(列名1,列名2,列名3…)values(值1, 值2 ,值3…)
3.3.2 修改语句
update 表名 set 列名1=值1, 列名2=值2 … [where 条件]
3.3.3 删除语句
delete from 表名 [where 条件]
3.3.4 基本查询语句练习
--使用scott账号练习基本查询语句
--1 查询当前用户下的所有表
select * from tab;
--2 查询雇员表中所有信息
select * from emp;
--3 查询雇员编号,姓名,工作,工资
select empno,ename,job,sal from emp
--4 查询雇员编号,姓名,工作,工资,并显示中文(为列起别名)
select empno as 编号,ename as 姓名,job as 工作,sal as 工资 from emp
--5 消除重复列,查询雇员工作种类
select distinct job from emp
--6 字符串连接操作(||)
--查询雇员编号,姓名,工作.按以下格工显示:编号:7369,姓名:Smith,工作:Clerk
select '编号:'||empno,'姓名:'||ename,'工作:'||job from emp
--7 查询列支持四则运算(年薪=(工资+奖金)*12)
--查询雇员编号,姓名,工作,年薪
select empno,ename,job,(sal+nvl(comm,0))*12 from emp
nvl(comm,0)==>如果comm值为空，取值0
--8 Where条件查询
-- 查询工资大于1500的所有雇员
select * from emp where sal>1500
--查询可以得到奖金的所有雇员
select * from emp where comm is not null
--查询工资大于1500或可以得到奖金的雇员
select * from emp where sal>1500 or comm is not null
--查询工资大于1500并且可以领取奖金的雇员
select * from emp where sal>1500 and comm is not null
--查询工资不大于1500或者不可以领取奖金的雇员
select * from emp where sal<=1500 or comm is null
--查询工资在1500到3000的所有雇员信息
select * from emp where sal>=1500 and sal<=3000
select * from emp where sal between 1500 and 3000
--查询在1981年雇用的员工信息
select * from emp where hiredate like '%81%'
--查询雇员姓名中第二个字母为"M"的雇员
select * from emp where ename like '_M%'
--查询雇员工资中带8这个数字的
select * from emp where sal like '%8%'
--查询编号是7369,7499,7521,7799的雇员信息
select * from emp where empno=7369 or empno=7499 or empno=7521 or empno=7799
select * from emp where empno in(7369,7499,7521,7799)
--查询雇员编号不是7369,7499,7521,7799的所有雇员信息
select * from emp where empno not in(7369,7499,7521,7799)
--查询雇员编号为7369的雇员信息
select * from emp where empno =7369
--查询雇员编号不为7369的雇员信息
select * from emp where empno !=7369
select * from emp where empno <>7369
--查询雇员信息,按工资由低到高排序
select * from emp order by sal asc
--查询雇员信息,按工资由高到低排序
select * from emp order by sal desc
--操作集合:
--union:将两个记录合并,去掉重复项
select distinct deptno from emp union select deptno from dept;
--union:将两个记录合并,不去掉重复项
select distinct deptno from emp union all select deptno from dept;
--intersect:取两个集合的交集
select distinct deptno from emp intersect select deptno from dept;
--minus:去掉交集(集合A-(集合A和集合B的交集))
select deptno from dept minus select distinct deptno from emp ;
4. 常用函数
4.1 字符函数
字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。
常用的字符函数：
--字符函数
--initcap(char):首字母转大写
select initcap('hello') from dual;
select initcap(ename) from emp;
--lower(char):转小写 upper(char):转大写
select lower('HELLO') from dual;
select lower(ename) from emp;
select upper('hello') from dual;
select upper(ename) from emp;
--ltrim(char,set):左剪裁 rtrim(char,set):右剪裁
select ltrim('hello,string','hello') from dual;
select rtrim('hello,string','string') from dual;
--按照字符翻译:translate
select translate('jack','abcd','1234') from dual;
--字符串替换:replace
select replace('jack and jue','j','bl') from dual;
4.2 日期函数
注意：oracle不识别字符串日期，需要转换为日期类型操作
--1.返回两个日期间的月份：月份在前，否则就负值
select months_between(hiredate,sysdate) from emp;
--2.返回月份数添加到日期对应的新日期:参数2：相当于加减月份
select add_months(sysdate,1) from dual;--2018/9/1 星期六
select add_months(sysdate,-2) from dual;--2018/6/1 星期五
--3.返回指定日期后的星期对应的新日期
select next_day(sysdate,'MON') from dual;--查询最近星期的日期。2018/10/8 星期一
--4.返回指定日期所在月份的最后一天
select last_day(sysdate) from dual;--2018/2/28 星期三
--5.按指定格式对日期进行四舍五入【挣扎了好久，外国的日期跟中国的习惯不一样，不必要纠结日期这段，跳过去】
--指定年份：年的四舍五入按月份算
select round(to_date(sysdate),'YEAR') from dual;--2003/1/1 星期三
--指定月份：月的四舍五入按日份算
select round(to_date(sysdate),'MONTH') from dual;--2003/8/1 星期五
--指定日:按星期的中间天数
select round(to_date(sysdate),'DAY') from dual;
4.3 转换函数
Oracle的类型转换分为自动类型转换和强制类型转换。数据类型之间可以进行自动转换，仍建议使用显示转换函数，以保持良好的设计风格。
常用类型转换函数有：
- to_number(数值类型的字符)：将字符转换为数值
- to_char(数值或者是日期)：将数值或者日期转换为字 符
- 使用to_date(‘要转的字符’,‘日期格式’)函数将字符转换为日期
--数字转换字符number --> cahr
--指定显示格式：
--9表示位置的占位：eg:999,999,999=>三位一组使用逗号隔开
--可以占位分组，但若真实数据位数不足，会使用0进行补位
select to_char(987654321,'￥999,999,999')from dual;
select to_char(123456789,'000,000,000,000.000')from dual;
--字符转换日期 cahr-->date
--使用to_date('要转的字符','日期格式')函数将字符转换为日期
select * from emp where to_char(hiredate,'yyyy-mm-dd')>'1982-01-01';
select * from emp where hiredate>to_date('1982-01-01','yyyy-mm-dd');
--日期转换字符 date-->char
--使用to_char('要转的日期','转换格式')
select to_char(hiredate,'yyyy-mm-dd') from emp;
4.4 数字函数
数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。
--ceil(数字):向上取整--->不小于指定数字的最小整数
select ceil(3.14) from dual;
--floor(数字):向下取整--->不大于指定数字的最大整数
select floor(3.14) from dual;
--round(数字):四色五入
select round(3.14) from dual;
--mod(除数,被除数)取余:
select mod(10,3) from dual;
--power(x,y):计算x的y次方
select power(3,3) from dual;
--sqrt(x):计算x的平方根
select sqrt(9) from dual;
5. 高级查询
5.1 连接查询
内连接: inner join 只展示两个表向关联的数据
--等值连接
select e.empno,e.ename,e.sal,d.dname from emp e, dept d where e.deptno=d.deptno;
--内连接
select e.empno,e.ename,e.sal,d.dname from emp e join dept d on(e.deptno=d.deptno)
左连接： left join 左表展示所有数据，右表只展现向关联的数据
select e.empno,e.ename,e.sal,d.dname from emp e left join dept d on(e.deptno=d.deptno)
select e.empno,e.ename,e.sal,d.dname from emp e ,dept d where e.deptno=d.deptno(+);
右连接: right join 右表展示所有数据，左表只展现向关联的数据
select e.empno,e.ename,e.sal,d.dname from emp e right join dept d on(e.deptno=d.deptno)
select e.empno,e.ename,e.sal,d.dname from emp e ,dept d where e.deptno(+)=d.deptno;
交叉连接: cross join 笛卡尔乘积
select * from emp,dept
select e.empno,e.ename,e.sal,d.dname from emp e cross join dept d
全连接: full join
select e.empno,e.ename,e.sal,d.dname from emp e full join dept d on(e.deptno=d.deptno)
5.2 子查询
子查询是指将一个查询语句的结果作为另个查询语句的条件。
--查询平均工资>2000,的部门编号,部门名称，平均工资
--平均工资>2000的部门编号
--查询平均工资>2000的部门编号的部门编号和平均工资
select deptno,avg(sal)from emp group by deptno having avg(sal)>2000
select d1.deptno,d1.dname,d2.savg from dept d1 inner join
(select deptno,avg(sal) savg from emp group by deptno having avg(sal)>2000) d2 --将查询的结果看成一张表进行连接查询
on (d1.deptno=d2.deptno)
--查询平均工资>2000,的部门编号,部门名称
--查询平均工资>2000的部门编号
select deptno from emp group by deptno having avg(sal)>2000
select deptno,dname from dept where deptno in(select deptno from emp group by deptno having avg(sal)>2000)
--查询平均工资>2000,的部门编号,部门名称，平均工资
select d.deptno,d.dname,avg(e.sal) from emp e,dept d where e.deptno=d.deptno
group by d.deptno,d.dname having avg(e.sal)>2000
5.3 Oracle分页
分页的原因如下：
-
提高性能，一次查20个，比一次查20000个性能肯定更好；另外如果数据量很大，一次性将内容都查询出来，查询出来的结果是放在内存里面的，内存没有这么大
-
不需要这么多数据，如新闻，一般人可能只看最近前20条；如果我们将后面的也都查询出来了，就是浪费
-
展现层面的考虑：如果一次展现太多的数据，不管是排版，还是美观上都不好
rownum：是一个伪列，它会根据返回的记录生成一个序列化的数字。可以使用ROWNUM列返回查询结果集中前N条记录
--Oracle分页: 通过rownum:伪列
---MySQL分页: 使用limit 起始位置,分页单位 SQLServer:使用top关键字
select * from emp;
--分页单位:5, 第一页:前5条
select rownum rn, e.* from emp e;
delete from emp where empno=8000
--将查询的结果看成一张表进行连接查询
select e2.* from (select rownum rn, e.* from emp e) e2 where e2.rn<=5
select e2.* from (select rownum rn, e.* from emp e) e2 where e2.rn>0 and e2.rn<=5
--分页单位:5, 第二页: rn>5 and rn<=10
select e2.* from (select rownum rn, e.* from emp e) e2 where e2.rn>5 and e2.rn<=10
--分页单位:5, 第二页: rn>10 and rn<=15
select e2.* from (select rownum rn, e.* from emp e) e2 where e2.rn>10 and e2.rn<=15
--select e2.* from (select rownum rn, e.* from emp e) e2 where e2.rn>其实位置 and e2.rn<=结束位置
--起始位置:(当前页-1)*分页单位、
--结束位置:当前页*分页单位
注意:Oracle中为列起别名可以加as,但为表起别名不能加as
-总页数:总记录数%分页单位==0?总记录数/分页单位:总记录数/分页单位+1
6. 锁
Oracle中的锁主要用于解决并发问题，保证在并发操作的过程中的数据一致性和完整性的需求。
Oracle锁的分类
- 行级锁
- 表级锁
6.1 行级锁
行级锁是一种排他锁，防止其他事务修改此行，在使用以下语句时，Oracle会自动应用行级锁：
-
INSERT语句
-
UPDATE语句
-
DELETE语句
-
SELECT … FOR UPDATE [wait second] [no wait]]
-
SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新
使用COMMIT或ROLLBACK语句释放锁
--用户A:
select * from emp where empno=7934 for update
--用户B:
update scott.emp set sal=sal+10 where empno=7934 --不能更改
update scott.emp set sal=sal+10 where empno=7902 --可以更改
6.2 表级锁
表级锁:锁定整个表，限制其他用户对表的访问。
LOCK TABLE table_name IN mode MODE
--用户A:
lock table emp in share mode;
--用户B:
select * from scott.emp --可以
update scott.emp set sal=sal+10 where empno=7902 --不可以
6.3 死锁
死锁:当两个事务相互等待对方释放资源时，就会形成死锁。
Oracle会自动检测死锁，并通过结束其中的一个事务来解决死锁。
--用户A:
lock table emp in share mode;
update emp set sal=sal+10 where empno=7902
--用户B:
lock table emp in share mode;
update scott.emp set sal=sal+10 where empno=7902
7. 视图
视图以经过定制的方式显示来自一个或多个表的数据，视图可以视为“虚拟表”或“存储的查询”，创建视图所依据的表称为“基表”。
视图的优点
- 提供了另外一种级别的表安全性
- 隐藏的数据的复杂性
- 简化的用户的SQL命令
- 隔离基表结构的改变
- 通过重命名列，从另一个角度提供数据
语法:
create [or replace] view 视图名称
as select 语句;
--查询员工编号，姓名，工作,薪水，部门名称和位置
select * from emp;
select * from dept;
select e.empno,e.ename,e.job,e.sal,d.dname,d.loc from emp e inner join dept d on(e.deptno=d.deptno)
--使用DBA身份登录为scott用户授权
grant create view to scott
--创建视图
create or replace view myview
as select e.empno,e.ename,e.job,e.sal,d.dname,d.loc from emp e inner join dept d on(e.deptno=d.deptno)
--查询视图
select * from myview;
删除视图: drop view 视图名称;
--删除视图:
drop view myview;
8.索引
索引类似于字典中的索引目录，为表添加合适的索引可以提高检索效率。
索引优点:
- 用以提高 SQL 语句执行的性能
- 减少磁盘I/O访问次数
添加索引原则:
- 只有在大表上创建索引才有意义
- 在where子句中的条件(列)经常建立索引
- 主键会自动添加唯一索引。
- 建立索引会带来一些额外的开销:
- 写的速度会变慢:需要额外维护索引信息
- 加大磁盘和内存空间的占用量(1.2倍)
创建标准索引语法:
CREATE INDEX 索引名称 ON 表名 (列名)
TABLESPACE 表空间
--语法: create index 索引名 on 表名(列名)
create index myIndex on emp(ename);
select * from emp where ename='test'
--组合索引:create index 索引名 on 表名(列名1,列名2...)
create index myIndex2 on emp(ename,sal)
select * from emp where ename='test'and sal=1300
删除索引: drop index 索引名称;
drop index myIndex;
创建唯一索引语法:
CREATE UNIQUE INDEX 索引名
ON 表名 (列名);
--创建唯一索引: create unique index 索引名 on 表名(列名)
create unique index myIndex3 on emp(ename);
9. PL/SQL语言
9.1 PL/SQL简介
PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。
- PL/SQL是Oracle在标准SQL语言上的过程性扩展
- 允许嵌入SQL语句，定义变量和常量，
- 允许过程语言结构（条件分支语句和循环语句），
- 允许使用异常来处理Oracle错误
- 可以用于创建存储过程、触发器和程序包等，
- 也可以用于处理业务 规则、数据库事件或给SQL命令的执行添加程序逻辑
所有的PL/SQL程序都以块作为基本单位，块中包含过程化语句和SQL的DML语句。这些块可以按顺序出现，也可以相互嵌套（一个块在另一个块的内部）。
块的分类：
- 匿名块
- 匿名块是出现在应用程序中的没有名字且不存储到数据库中的块
- 匿名块出现在SQL语句可以出现的地方，它们可以调用其他程序，却不能被其他程序调用命名块
命名块是一种带有标签的匿名块，标签为块指定了一个名称
- 子程序
- 子程序是存储在数据库中的过程（procedure）、函数（function），生成之后可以被多次执行
- 程序包
- 程序包是存储在数据库中的一组子程序、变量定义，程序包中的子程序可以被其他程序包或者子程序调用
- 触发器
- 触发器是一种存储在数据库中的命名块，生成之后可以被多次执行
在相应的触发器事件发生之前或之后就会被执行一次或多次
- 触发器是一种存储在数据库中的命名块，生成之后可以被多次执行
9.2 程序结构
9.2.1 PL/SQL块的基本结构
组成部分
- 定义部分 ：用于定义常量、变量、游标、异常和复杂数据类型
- 执行部分：用于实现应用模块功能，该部分包含需要执行的PL/SQL语句和SQL语句
- 异常处理部分：用于处理执行部分可能出现的运行错误
语法:
DECLARE
定义部分
BEGIN
执行部分
EXCEPTION
异常处理部分
END;
注意：DECLAREE、BEGIN和EXCEPTION后面没有“；”（分号），而END后则必须要带“；”（分号）。
9.2.2 变量和常量
在声明部分中可以声明需要使用的常量、变量、函数、游标、异常处理名称 。
声明变量语法：变量名 类型:=值;
为变量赋值语法：变量名:=值;
在sqlplus中首先设置，否则执行没有输出结果
set serveroutput on;
--声明变量(建议变量名以v_开头)
--变量名 类型:=值;
--赋值 变量名:=值;
declare
v_hello varchar2(20):='hello';
v_world varchar2(20);
v_cons constant number(4):=123;--声明常量:不能重新赋值
begin
v_world:=',oralce!';
--v_cons:=345;
dbms_output.put_line(v_hello||v_world);--类似于System.out.println();
dbms_output.put_line('v_cons='||v_cons);
end;
--使用select ..into语句为变量赋值
declare
v_name varchar2(20);
v_sal number(10);
v_empno number(10);
begin
v_empno:='&empno';--获取控制台输入的编号:&(交互式命令)
select ename,sal into v_name,v_sal from emp where empno=v_empno;
dbms_output.put_line('姓名:'||v_name);
dbms_output.put_line('薪水:'||v_sal);
end;
--%type:引用变量和数据库列的数据类型
--%rowtype:提供表示表中一行的记录类型
declare
v_name emp.ename%type;-- 与emp表中ename列的类型一致
v_sal emp.sal%type;
v_empno emp.empno%type;
v_record emp%rowtype;--保持一条记录
begin
v_empno:='&empno';--获取控制台输入的编号:&(交互式命令)
select ename,sal into v_name,v_sal from emp where empno=v_empno;
dbms_output.put_line('姓名:'||v_name);
dbms_output.put_line('薪水:'||v_sal);
dbms_output.put_line('**************************');
select * into v_record from emp where empno=v_empno;--将查询的记录赋值给v_record
dbms_output.put_line('工作:'||v_record.job);
end;
9.2.2 流程控制
条件结构：
- if…end if 结构
- if…else… end if 结构
- if…elsif…else…end if结构
- case结构
--条件结构
--if.... end if;
--语法 if 条件 then 语句 end if;
declare
v_age number(3);
begin
v_age:='&age';
if v_age>=18 then
dbms_output.put_line('成年!');
end if;
if v_age<18 then
dbms_output.put_line('未成年!');
end if;
end;
--if....else....end if;
declare
v_age number(3);
begin
v_age:='&age';
if v_age >= 18 then
dbms_output.put_line('成年!');
else
dbms_output.put_line('未成年!');
end if;
end;
---if...elsif...else....end if
declare
v_age number(3);
begin
v_age:='&age';
if v_age >= 50 then
dbms_output.put_line('老年!');
elsif v_age>=30 then
dbms_output.put_line('状年!');
elsif v_age>=18 then
dbms_output.put_line('青年!');
else
dbms_output.put_line('未成年!');
end if;
end;
--case子句:类似于switch
declare
v_score varchar2(2);
begin
v_score:='&score';
case v_score --类似switch语句中的switch
when 'A' then --类似于switch语句中case
dbms_output.put_line('优秀!');
when 'B' then
dbms_output.put_line('良好!');
when 'C' then
dbms_output.put_line('一般!');
when 'D' then
dbms_output.put_line('不及格!');
else --类似于swtich中的default
dbms_output.put_line('输入不正确!');
end case;
end;
循环结构：
- loop循环结构: 类似于do…while循环
- while循环结构: 类似于while循环
- for循环结构: 类似于for循环
--loop循环结构: do...while循环
--loop
--循环体;
-- exit when 条件
--end loop;
declare
i binary_integer:=1;
begin
loop
dbms_output.put_line('i='||i);
i:=i+1;
exit when i>10; --满足条件推出循环
end loop;
end;
--while循环结构:类似于while循环
--while 条件
--loop
--循环体
--end loop;
declare
i binary_integer:=1;
begin
while i<=10
loop
dbms_output.put_line('i='||i);
i:=i+1;
end loop;
end;
--for 循环变量 in [REVERSE] 初值表达式..终值表达式
--loop
-- 循环体;
--end loop;
declare
v_i binary_integer;
begin
for v_i in 1..10
loop
dbms_output.put_line('i='||v_i);
end loop;
end;
declare
v_i binary_integer;
begin
for v_i in reverse 1..10
loop
dbms_output.put_line('i='||v_i);
end loop;
end;
10. 游标
当在PL/SQL块中执行查询语句（SELECT）和数据操纵语句（DML）时，Oracle会为其分配一个上下文区（Context Area） ，游标是指向上下文区的指针，它为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法
10.1 游标概述
游标分为显式游标和隐含游标两种
- 隐含游标用于处理SELECT INTO和DML语句
- 显式游标则用于处理SELECT语句返回的多行数据
使用显式游标 的步骤
-
定义游标：CURSOR cursor_name IS select_statement;
-
打开游标：OPEN cursor_name;
-
提取数据：FETCH cursor_name INTO variable1,variable2,…;
-
关闭游标：CLOSE cursor_name;
10.2 显示游标
显示游标属性
显式游标属性用于返回显式游标的执行信息
游标属性使用格式为：游标名 + 属性名
| 属性名 | 含义 |
|---|---|
| %ISOPEN | 用于确定游标是否已经打开。如果游标已经打开，则返回值为TRUE；否则返回FALSE |
| %FOUND | 检查是否从结果集中提取到数据。如果提取到数据，则返回值为TRUE；否则返回FALSE |
| %NOTFOUND | 与%FOUND属性恰好相反，如果提取到数据，则返回值为FALSE；否则返回TRUE |
| %ROWCOUNT | 返回到当前行为止已经提取到的实际行数 |
/*
显示游标
显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行
*/
--使用loop循环遍历游标
declare
v_emp_record emp%rowtype; --声明变量:保持员工表中一行的记录信息
cursor emp_cur is select * from emp;--定义游标:该游标保持了所有的员工信息
begin
open emp_cur; --打开游标
loop
fetch emp_cur into v_emp_record; --从游标中提取数据
exit when emp_cur%NOTFOUND; --判断游标中是否没有数据
dbms_output.put_line('编号:'||v_emp_record.empno||'姓名:'||v_emp_record.ename);
end loop;
close emp_cur;
end;
10.3 带有参数的游标
在定义了参数游标之后，当使用了不同参数值多次打开游标时，可以生成不同的结果集
语法: CURSOR cursor_name(param_name datatype)
IS select_statement;
定义参数游标时，游标参数只能指定数据类型，而不能指定长度。
当使用游标for循环时，oracle会隐含地打开游标，提取数据并关闭游标
--带参数的游标
declare
v_sal emp.sal%type;
v_emp_record emp%rowtype;
cursor emp_cur(salPara number) is select * from emp where sal < salPara; --定义带参数的游标
begin
v_sal:='&请输入薪酬';
for v_emp_record in emp_cur(v_sal)
loop
dbms_output.put_line('姓名:'||v_emp_record.ename||'薪酬:'||v_emp_record.sal);
end loop;
end;
11.存储过程
过程一般用于执行一个指定的操作，可以将常用的特定操作封装成过程
存储过程的优点:
- 模块化:将程序分解为逻辑模块
- 可重用性:可以被任意数目的程序调用
- 可维护性:简化维护操作
- 安全性:通过设置权限，使数据更安全
- 执行效率较高
存储过程语法:
CREATE [OR REPLACE] PROCEDURE
[()]
IS|AS --变量的声明部分
BEGIN
--业务
[EXCEPTION --异常处理
]
END;
在sqlplus中首先设置，否则执行没有输出结果
set serveroutput on;
create or replace procedure pro1
is
v_hello varchar2(20):='hello,procedure!';
begin
dbms_output.put_line(v_hello);
end;
调用存储过程的方式:
-
execute 存储过程名称(sqlplus中)
execute pro1(); -
call 存储过程名称(sqlplus/PLSQLDev中)
call pro1(); -
使用程序块调用
begin
存储过程名称
end;begin pro1(); end;
--根据部门编号,为员工加薪(部门编号=10,加薪100,部门编号=20,加薪200,部门编号=30,加薪300)
create or replace procedure pro2
is
cursor c is select * from emp for update;
begin
for v_emp_rec in c
loop
if(v_emp_rec.deptno=10) then
update emp set sal=sal+100 where current of c;--current of c 当前游标指向的记录
elsif v_emp_rec.deptno=20 then
update emp set sal=sal+200 where current of c;
elsif v_emp_rec.deptno=30 then
update emp set sal=sal+300 where current of c;
end if;
end loop;
commit; --提交事务
end;
select * from emp
begin
pro2();
end;
带参数的存储过程
- IN:用于接受调用程序的值 (输入参数)
- OUT:用于向调用程序返回值 (输出参数)
- IN OUT：用于接受调用程序的值，并向调用程序返回更新的值(即可以充当输入参数也可以充当输出参数)
create or replace procedure pro3(emp_no number)
is
v_name varchar2(20);
begin
select ename into v_name from emp where empno=emp_no;
dbms_output.put_line('名字:'||v_name);
exception when NO_DATA_FOUND then
dbms_output.put_line('没有找到雇员信息..');
end;
--调用带参的存储过程
begin
pro3('793422');
end;
--既有输入参数又有输出参数(接受返回值)
create or replace procedure pro4(emp_no in number,emp_sal out number)
is
v_name varchar2(20);
begin
select ename,sal into v_name,emp_sal from emp where empno=emp_no;
dbms_output.put_line('名称:'||v_name);
exception when NO_DATA_FOUND then
dbms_output.put_line('没有找到雇员信息..');
end;
-- 调用
declare
emp_no number:=7934;
emp_sal number;--接收返回值
begin
pro4(emp_no,emp_sal);
dbms_output.put_line('out:....'||emp_sal);
end;
--输入/输出参数: in out
create or replace procedure pro5(p_empno in out number,p_sal in out number)
is
v_name varchar2(20);
begin
select ename,sal into v_name ,p_sal from emp where empno=p_empno;
dbms_output.put_line('雇员姓名:'||v_name);
exception when NO_DATA_FOUND then
dbms_output.put_line('没有找到雇员信息..');
end;
--调用
declare
p_empno number:=7369;
p_sal number; --接受输出参数的值
begin
pro5(p_empno,p_sal);
dbms_output.put_line('输出参数的值:'||p_sal);
end;
12. 触发器
触发器是指被隐含执行的存储过程，它可以使用PL/SQL进行开发
当发生特定事件（如修改表、创建对象、登录到数据库）时，Oracle会自动执行触发器的相应代码
触发器是当特定事件出现时自动执行的存储过程
触发器类型
-
DML触发器 ：在对数据库表进行DML操作时触发，并且可以对每行或者语句操作上进行触发。
-
替代触发器：是oracle8专门为进行视图操作的一种触发器
-
系统触发器 ：对数据库系统事件进行触发，如启动、关闭等
触发器组成
- 触发事件 ：DML或DDL语句。
- 触发时间：是在触发事件发生之前(before)还是之后(after)触发
- 触发操作：使用PL/SQL块进行相应的数据库操作
- 触发对象：表、视图、模式、数据库
- 触发频率 ：触发器内定义的动作被执行的次数。
编写触发器执行代码时，需要注意以下限制
- 触发器代码的大小不能超过32K。如果确实需要使用大量的代码创建触发器，则应该首先创建过程，然后在触发器中使用CALL语句调用过程
- 触发器代码只能包含SELECT、INSERT、UPDATE和DELETE语句，
- 不能包含DDL语句（CREATE、ALTER和DROP）和事务控制语句（COMMIT、ROLLBACK和SAVEPOINT）
触发器语法
CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]]
[[OR] DELETE]
ON table_name
[FOR EACH ROW]
begin
pl/sql_block;
end;
--------------------------触发器-------------------------------
create table tbl_dept
(
deptId number(4) primary key,
deptName varchar2(20),
deptNum number(4)
)
create table tbl_emp
(
empId number(4) primary key,
empName varchar2(20),
deptId number(4) references tbl_dept(deptId)
)
insert into tbl_dept(deptId,deptName,deptNum) values(1,'开发部',1);
insert into tbl_dept(deptId,deptName,deptNum) values(2,'测试部',2);
insert into tbl_emp(empId,empName,deptId) values(1,'zhangsan',1);
insert into tbl_emp(empId,empName,deptId) values(2,'zhangsan2',2);
insert into tbl_emp(empId,empName,deptId) values(3,'zhangsan3',2);
--利用触发器,维护部门人数
--（如果新添加员工,让部门人数+1;
--如果删除员工,让部门人数-1;
--如果为员工更改部门:原有部门人数-1，现有部门人数+1）
create or replace trigger tri1
after insert or delete or update
on tbl_emp
for each row
begin
if inserting then
update tbl_dept set deptNum=deptNum+1 where deptId=:new.deptId;--:new代表新数据
elsif deleting then
update tbl_dept set deptNum=deptNum-1 where deptId=:old.deptId;--:old代表旧数据
elsif updating then
update tbl_dept set deptNum=deptNum-1 where deptId=:old.deptId;--:old代表旧数据
update tbl_dept set deptNum=deptNum+1 where deptId=:new.deptId;--:new代表新数据
end if;
end;
insert into tbl_emp values(4,'zhaoliu',1);
delete from tbl_emp where empId=4
update tbl_emp set deptId = 1 where empId=2
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
