Oracle数据库教程

最新推荐文章于 2024-06-30 14:00:11 发布

NSJim

最新推荐文章于 2024-06-30 14:00:11 发布

阅读量890

点赞数

分类专栏： Database 文章标签： Oracle 数据库教程

本文链接：https://blog.csdn.net/NSJim/article/details/89602672

版权

Database 专栏收录该内容

7 篇文章 1 订阅

订阅专栏

Oracle教程目录

第一章数据库启动与连接
第二章数据库操作
第三章 SQL基础
第四章视图和同义词
第五章序列
第六章用户管理
第七章权限管理
第八章角色与权限管理
第九章 PL/SQL

第一章数据库启动与连接

启动数据库
1. cmd方式进行操作
  sqlplus 用户名/密码超级管理员权限[as sysdba]
  账号：
  超级管理员sys, change on install
  普通管理员system manager
  用户：scott tiger
2. 图形化的方式sqlpuls
命令行与图形化界面可以相互转化
sqlplusw 用户名/密码 [as sysdba]
查看当前身份
show user
账号的加解锁
alter user 用户名 account lock|unlock;
连接(conn是connect的简写)
conn 用户名/密码 [as sysdba]
conn 用户名/密码@数据库实例的名字 [as sysdba]
断开
disconnect
简写：disc
修改密码
alter user 用户名 IDENTIFIED BY 新密码;
password 用户名;
密码失效
alter user 用户名 PASSWORD EXPIRE;

第二章数据库操作

查询
a. 查询所有表：select * from tab;
b. 查看表结构：desc 表名;
c. 查看表中的所有数据：select * from 表名
d. 设置页面宽度：set linesize 整数
e. 设置每页显示的行数：set pagesize 整数

示例
Emp表雇员表
EMPNO 雇员编号
ENAME 雇员姓名
JOB 雇员岗位
MGR 上司编号
HIREDATE 受雇日期
SAL 基本工资
COMM 补助
DEPTNO 部门编号

查询语句写法
a. 查询
select empno,ename,sal from emp;
b. 修改：ed
c. 执行：r 或 /
关于缓冲区的操作
a. 查看缓冲区的内容：list 或 l
b. 编辑缓冲区的内容：
i. ed 或 edit: 修改
ii. a: 追加
iii. del: 删除
c. 执行缓冲区内容：r 或 /
d. 清除缓冲区的内容：clear buffer
文件操作指令
a. 创建脚本
save 文件
save 文件 replace
save 文件 append
b. 脚本文件的载入
get 文件
c. 脚本文件的执行
start 文件
@文件
脚本文件注释
a. 单行注释：–注释内容
b. 多行注释：/**/
c. remark：单行注释，只能用在首行
常用命令
a. 显示表结构
desc 表名
b. 将屏幕内容保存到文件中
spool 文件地址
……
spool off
c. 清屏：清空屏幕和屏幕缓冲区
clear screen
shift + delete
补充：clear scr 或 cle scr
d. 帮助命令
help 命令
环境变量的设置与显示
命令更改只改变当前窗口，永久更改可以在sqlplus界面的环境菜单更改
a. show命令就是显示的意思。
show linesize
show all
b. set命令为设置，on为显示，off为关闭
i. autocommit：是否自动提交DML语句
ii. colsep：表示列之间的间隔符
iii. feedback：反馈信息行的最低数，默认是6
iv. heading：是否显示列标题，set heading on
v. time：是否在命令行前添加时间
vi. timing：是否显示sql语句的执行时间
vii. 保存环境变量：store set 文件位置，如store set d:\ddd.sql

第三章 SQL基础

3.1 查询

查询
SELECT 列名1，列名2 …… FROM 表名;
范例：查询所有列
SELECT * FROM emp;
多表查询
SELECT 列名1，列名2 …… FROM 表名1，表名2 WHERE 关联条件 AND 过滤条件;
范例：
SELECT * FROM emp e, dept d WHERE e.deptno=d.deptno;
SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
SELECT e.empno, e.ename, e.mgr, m.ename FROM emp e, emp m WHERE e.mgr=m.empno

3.2 运算符

运算符
算数运算符：+ - * / （数字和日期）
范例：
SELECT hireate+1 FROM emp;
空值：无效或未指定的值，不可以进行算数运算
nvl(空值列，指定值)
连接符 ||
列与列的连接，列与字符串的连接，字符串用单引号
SELECT 'id: ’ || empno || ', name is ’ || ename FROM emp;
去掉重复行DISTINCT，只能放在第一列前面
SELECT DISTINCT ename FROM emp;
修改列名（as可写可不写）
SELECT 列名1 (as) 列别名1，列名2 (as) 列别名2, …… FROM 表名;
列别名为关键字时可以加双引号
如：SELECT sal+1 “from” FROM emp;
排序，默认是升序
SELECT 列名1 (as) 列别名1，列名2 (as) 列别名2, …… FROM 表名 ORDER BY 排序列名1, 排序列名2 ASC | DESC;
过滤，WHERE 条件，在排序前面
比较运算符：>, <, =, <=, >=, !=,<>
BETWEEN AND
LIKE
%：代表任意数量的任意字符
_：表示一个字符
IN：某个范围内中的任意一个值
IS NULL：是空
逻辑运算符：
AND
OR
NOT
范例：查询工资大于800，并且小于1000的员工信息
SELECT * FROM emp WHERE sal>800 AND sal<1000;
范例：查询工资在800到1500之间的雇员的所有信息
SELECT * FROM emp WHERE sal BETWEEN 800 AND 1500;
范例：查询姓名中没有A的员工信息
SELECT * FROM emp WHERE ename not llike ‘%A%’;

3.3 函数

单行函数：字符，数值，日期，转换，通用函数
多行函数
a. 字符函数：
i. 大小写控制函数（lower，upper，initcap-首字母大写，其他小写）
ii. 字符控制函数（concat，substr，length，instr-查找，lpad-左填充，rpad，trim-截取左右2端）
SELECT lpad(sal, 10, ‘’) FROM emp; 不够10位的，用填充
SELECT TRIM(‘H’ FROM ‘Hello Hello world’) FROM dual;
b. 数字函数：round-小数点后几位，trunc-截取，不四舍五入，mod-余数
SELECT round(45.926, 2) FROM dual; 为45.93
SELECT trunc(45.926, 2) FROM dual; 为45.92
c. 日期函数：
i. sysdate：显示当前系统时间
日期可以进行算数运算
日期+数字=日期
日期-日期=天数
ii. months between
iii. add_months
iv. next_day
v. last_day：所在月份的最后一天
vi. extract：提取日期 extract(year from sysdate)
d. 转换函数
to_char：把其他类型转换成字符类型
select to_char(hiredate, ‘yyyy-mm-dd’) from emp;
select to_char(hiredate, ‘FMyyyy-mm-dd HH12:MI:SS am’) from emp; FM-去掉前导零，HH12代表12时制，am表示上午下午
9：数字
S：美元
e. 通用函数
i. nvl
ii. nbl2
iii. decode
decode (列，条件，结果，
条件，结果，
……
[结果]
)
f. 填充函数
SELECT lpad(’ ', 5level-1)||empno EMPNO,LPAD(’ '5LEVEL-1)\ename ENAME

3.4 连接

自然连接
左右连接

3.5 表的操作

插入
a. 正常插入
i. 标准（推荐）
INSERT INTO 表名（列名1，列名2，……） VALUES（值1，值2，……）
ii. 省略写法
iii. INSERT INTO (SELECT empno,ename,job FROM myemp) VALUES(9000,‘张三’,‘农民’)
b. 利用子查询插入数据（批量操作）
INSERT INTO 表名（列名1，列名2，……） SELECT语句
范例：
向myemp表中插入一个员工信息，员工号为1122，员工姓名为“Tom22”，其他信息与员工7369的员工信息一样
INSERT INTO myemp SELECT 1122, ‘Tom22’, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE empno = 7369
若要进行大量数据的插入（装载）操作，可以利用“/+APPEND/”关键字来实现
INSERT /+APPEND/ INTO myenp(empno,ename) SELECT empno,ename FROM emp
c. 向多个表插入数据
i. 无条件多表插入（多用于数据备份）
INSERT ALL
INTO 表1 VALUES（值1，值2，……）
INTO 表2 VALUES（值1，值2，……）
……
子查询
范例：
INSERT ALL
INTO myemp1 VALUES(empno,hiredate,sal)
INTO myemp2 VALUES(empno,hiredate,sal)
SELECT empno,hiredae,sal FROM emp WHERE sal>800
ii. 有条件的多表插入
INSERT ALL | FIRST
WHEN 条件1 THEN INTO 表1（列1，列2，……）
WHEN 条件2 THEN INTO 表2（列1，列2，……）
……
ELSE INTO 表n（列1，列2，……）
子查询
iii. 多表插入的应用
修改
a. 增加新的列
ALTER TABLE 表名 add (列名数据类型 default 默认值, 列名数据类型 ……）
b. 更改列名
ALTER TABLE 表名 rename column 现列名 to 新名;
c. 删除列
ALTER TABLE 表名 drop column 列名;
d. 修改列的状态
ALTER TABLE 表名 set unused column 列名;
e. 删除无用的列
ALTER TABLE 表名 drop unused columns;
f. 表的重命名
rename 旧表名 to 新表名;
g. 为表添加注释
建表
CREATE TABLE 表名(
列名类型 [DEFAULT ‘值’],
列名类型,
……);
约束：对表的强制规定，优先建表级别
约束的分类：
○ 主键约束：表示唯一的，不能为空
○ 唯一约束：表示唯一的
○ 非空约束：表示不能为空的
○ 检查约束：检查一个列的内容是否合法
○ 外键约束：
根据约束的位置进行分类：
○ 列级别约束
○ 表级别约束
范例：
○ 建立主键约束（列级别）
DROP TABLE person;
CREATE TABLE person(
pid number(5) primary key,
pname varchar(2),
page number(3),
psex varchar2(4)
);
DROP TABLE person;
CREATE TABLE person(
pid number(5) CONSTRAINT person_pid_pk PEIMARY KEY(pid),
pname varchar(2),
page number(3),
psex varchar2(4)
);
○ 建立主键约束（表级别）
DROP TABLE person;
CREATE TABLE person(
pid number(5),
pname varchar(2),
page number(3),
psex varchar2(4),
CONSTRAINT person_pid_pk PEIMARY KEY(pid,pname)
);
○ 唯一约束
DROP TABLE person;
CREATE TABLE person(
pid number(5),
pname varchar(2) unique,
page number(3),
psex varchar2(4)
);
DROP TABLE person;
CREATE TABLE person(
pid number(5),
pname varchar(2) unique,
page number(3),
psex varchar2(4),
CONSTRAINT person_pname_uk UNIQUE(pname)
);
○ 非空约束（只有列级别）
DROP TABLE person;
CREATE TABLE person(
pid number(5),
pname varchar(2) unique not null,
page number(3) not null,
psex varchar2(4)
);
○ 检查约束
DROP TABLE person;
CREATE TABLE person(
pid number(5),
pname varchar(2) unique,
page number(3) not null check(page BETWEEN 0 AND 150),
psex varchar2(4) CHECK(psex IN(‘男’, ‘女’))
};
DROP TABLE person;
CREATE TABLE person(
pid number(5),
pname varchar(2) unique,
page number(3) not null ,
psex varchar2(4) CHECK(psex IN(‘男’, ‘女’)),
CONSTRAINT person_page_ck CHECK(page BETWEEN 0 AND 150)
);
○ 外键约束
DROP TABLE department;
CREATE TABLE department(
deptno number(5) primary key,
dname varchar2(10)
);
DROP TABLE employee;
CREATE TABLE employee(
empno number(5) primary key,
ename varchar2(10),
deptno number(5) references department(deptno)
);
DROP TABLE department;
CREATE TABLE department(
deptno number(5) primary key,
dname varchar2(10)
);
DROP TABLE employee;
CREATE TABLE employee(
empno number(5) primary key,
ename varchar2(10),
deptno number(5),
CONSTRAINT employee_deptno_fk FOREIGN KEY(deptno) REFERENCES department(deptno)
);
○ 追加约束
ALTER TABLE 表名 ADD 约束
DROP TABLE person;
CREATE TABLE person(
pid number(5),
pname varchar(2),
page number(3),
psex varchar2(4)
);
ALTER TABLE person add CONSTRAINT person_pid_pk PRIMARY KEY(pid,pname);
○ 删除约束
ALTER TABLE 表名 DROP 约束
ALTER TABLE person DROP CONSTARINT person_pid_pk;
ALTER TABLE person DROP primary key;
○ 查看约束
SELECT * FROM user_constraints WHERE table_name=‘EMP’
○ 约束的禁用和启动
ALTER TABLE 表名 DISABLE CONSTAINT 约束名;
ALTER TABLE 表名 ENABLE CONSTAINT 约束名;
删除数据（安全，不经过回收站）
全部删除：DETELE FROM 表名
局部删除：DETELE FROM 表名 WHERE 条件
子查询删除
删除表（删除到回收站，可恢复）
DROP TABLE 表名
从回收站里恢复表
FLASHBACK TABLE 表名
从回收站中删除表
PURGE TABLE 表名;
清空回收站：purge recyclebin
层次查询
CREATE TABLE bicycle(
part_id number(5)
constraint pk_bicycle_part_id primary key,
parent_id number(5)
constraint fk_bicycle_pid
references bicycle(part_id),
part_name varchar2(30) not null,
mp_cost number(9, 2),
describe varchar2(30)
);
SELECT level, column_name, expression, …
FROM table_name
[WHERE where_condition]
START WITH start_condition
CONNECT BY PRIOR prior_condition;

第四章视图和同义词

视图：获取一个或多个表中的数据集合
创建视图：
CREATE [OR REPLACE] [force unforce] VIEW 视图名 AS
select语句
[with check option 约束]
[with READ ONLY]
删除视图：
DROP VIEW 视图名
创建同义词
CREATE [PUBLIC] SYNONYM synonym_name FOR object_name;
删除同义词
DROP [PUBLIC] SYNONYM synonym_name;

第五章序列

序列：按照一定规则自动增加/减少数字的这一种数据库对象
创建序列：
CREATE SEQUENCE 序列名
[INCREMENT BY n]
[start with n]
[maxvalue n]
[minvalue n]
[cycle|nocycle]
[cache n|nochche]
修改序列：
ALTER SEQUENCE 序列名 [INCREMENT BY n] [maxvalue n] [minvalue n] [cycle|nocycle] [cache n|nochche]
删除序列：
DROP SQUENCE 序列名
伪列：
ROWID：伪列，系统自动生成。其表示的每个数据库中的记录的物理地址，这个地址是唯一的，好处是可以快速
索引（INDEX）：用于定位
单列索引
复合索引
a. 创建索引：
自动创建：在建表时，使用了PRIMARY KEY或unique约束时，数据库会自动创建一个索引
手工创建：CREATE INDEX 索引名 ON 表名(列, ……)
说明：关于索引名的命名规范：idx_表名_列名
例：CREATE INDEX idx_emp_ename ON emp(ename)
CREATE INDEX idx_emp_deptnojob ON emp(deptno,job)
b. 删除索引：
DROP INDEX 索引名

第六章用户管理

Oracle数据库的初始用户
SYS
SYSTEM
SCOTT：用于测试网络连接的用户
PUBLIC：实质上一个用户组，数据库中任何一个用户都属于该组。要为该组授予某个权限，只需把权限授予PUBLIC就可以了。
用户属性
a. 安全属性：
身份认证：数据库身份认证、外部身份认证、全局身份属性
默认表空间
临时表空间
表空间配额
概要文件
账号状态
创建账号
CREATE USER 用户名
IDENTIFIED [BY 密码|EXTERNALLY|GLOBALLY AS ‘external_name’]
[DEFAULT TABLESPACE tablespace_name]
[TEMPORARY TABLESPACE temp_tablespace_name]
[QUOTA n K|M|UNLIMITED ON tablespace_name]
[PROFILE profile_name]
[PASSWORD EXPIRE]
[ACCOUNT LOCK|UNLOCK];

范例：创建一个用户user3，口令为user3，默认表空间为USERS，在该表空间的配额为10mb，初始状态为锁定。
DROP USER user3；
CREATE USER user3 IDENTIFIED BY user3
DEFAULT TABLESPACE USERS
QUOTA 10M ON USERS
ACCOUNT LOCK;
修改用户
ALTER USER 用户名 IDENTIFIED [BY 密码|EXTERNALLY|GLOBALLY AS ‘external_name’]
[DEAFULT TABLESPACE tablespace_name] [TEMPORARY TABLESPACE temp_tablespace_name]
[QUOTA n K|M|UNLIMITED ON tablespace_name] [PROFILE profile_name]
[DEFAULT role_list|ALL[EXCEPT role_list]|NONE]
[PASSWORD EXPIRE] [ACCOUNT LOCK|UNLOCK];

范例：将账号为user3的密码修改为newuser3，同时将该用户解锁
ALTER USER user3 IDENTIFIED BY newuser3 ACCOUNT UNLOCK;
删除用户
DROP USER 用户名 [cascade];
查看用户
a. ALL_USERS：包含数据库所用用户的用户名，用户id和用户创建时间
b. DBA_USERS：包含数据库所用用户的所有信息
c. USER_users：包含当前用户的详细信息
d. DBA_TS_QUOTAS：包含所用用户的表空间配额信息
e. USER_TS_QUOTAS：包含当前用户的表空间配额信息
f. V $S E S S I O N ：包含用户的会话信息 g . V$ OPEN_CURSOR：包含用户执行的SQL语句信息

第七章权限管理

系统权限：不是控制对数据库对象的访问，而是用来许可对各种特性的访问。
a. 系统权限的授予和撤销
GRANT 系统权限1, 系统权限2 TO 用户1，用户2 [WITH ADMIN OPTION];
REVOKE 系统权限1, 系统权限2 FROM 用户1，用户2;
b. 系统权限
SESSION：登录数据库
CREATE TABLE：创建表
DROP ANY TABLE：删除表
c. 注意
只有DBA才应该拥有 ALTER DATABASE 系统权限。
对象权限：是由用户赋予的访问或操作数据库对象的权限。
a. 对象权限的授予和撤销
GRANT 对象权限1，对象权限2 ON 对象(.属性) TO 用户1，用户2
[WITH GRANT OPTION] [WITH HIERARCHY];
REVOKE 对象权限1，对象权限2 ON 对象(.属性) FROM 用户1，用户2
[CASCADE CONSTRANTS] [FORCE];
b. 对象权限
SELECT，UPDATE，DELETE，等等
c. 示例
以 Emi 的身份使 Bob 能够访问 Scott 的 DEPT 表。
以 Scott 的身份撤消 Emi 读取 Scott 的 DEPT 表的权限。
以 Bob 的身份查询 Scott 的 DEPT 表。
grant select on Scott.DEPT to Bob;
revoke select on DEPT from Emi;
select * from Scott.DEPT t;
以 Scott 的身份重新连接，并赋予 Emi 从 Scott 的 DEPT 表中进行选择的能力。此外，使 Emi 能向其他用户赋予选择权限。
grant select on DEPT to Emi with grant option;

第八章角色与权限管理

角色：数据库角色就是权限的命名集合。
作用：使用角色可以大大降低用户权限的维护负担。角色可以是对象权限或系统权限的命名集合。数据库管理员只需创建特定的数据库角色，使其反映组织或应用的安全权限，就可以将这些角色赋予用户。
创建角色
CREATE ROLE 角色名;
删除角色
DROP ROLE 角色名;
将权限授予角色
方法同将权限赋予用户，把用户改成角色即可
将角色授予用户或其他角色
GRANT role1,role2 TO user1,user2,role1 [WITH ADMIN OPTION];
撤销授予用户或其他角色的角色
REVOKE role1,role2 FROM user1,user2,role1;

第九章 PL/SQL

NoSQL（非关系型数据库）：
键值(Key-Value)存储数据库
列存储数据库
文档型数据库
图形数据库
PL/SQL (Procedural Language/SQL)
是基于Ada编程语言的结构化编程语言，是由Oracle公司从版本6开始提供的专用于Oracle产品的数据库编程语言。用户可以使用PL/SQL语言编写过程、函数、程序包、触发器等代码，并把这些代码存起来，以便由具有适当权限的数据库用户重新使用。
PL/SQL语言基本的程序单元就是PL/SQL代码块，简称块。块是指令的集合，这些指令包括：
Oracle要执行的指令
向屏幕显示信息的指令
将数据写入文件的指令
调用其他程序的指令
操作数据的指令
……
PL/SQL程序至少会包含一个代码块
PL/SQL代码块实现形式：
只执行一次且永不存储的PL/SQL程序
存储在数据库中以备之后使用的块
代码块支持所有的DML语句，并可以通过使用本地动态SQL（Native Dynamic SQL，NDS）或内置的DBMS_SQL包运行DDL语句。