13 Oracle数据库开发与设计入门篇

2022-05-26 17:39:32

Oracle数据库SQL语言基础

SQL（Structured Query Language，结构化查询语言）语言是一种在关系数据库中定义和操纵数据的标准语言，是用户与数据库之间进行交流的接口。SQL语言已经被大多数关系数据库管理系统采用。
Oracle采用ANSI的SQL标准，并且对它进行了扩充，以便包含更多的附加功能。
Oracle数据库提供的许多有用而强大的功能都需要通过SQL语言来体现。因此要使用Oracle数据库，一定要掌握SQL语言

SQL是1974年由Boyee和Chamberlin提出，并在IBM公司研制的关系数据库原型系统System R上实现的。
1986年10月，美国国家标准局（ANSI）的数据库委员会批准了SQL作为关系数据库语言的美国标准，同年，公布了标准SQL文本。
现在，SQL已成为关系数据库领域中的一种主流语言

SQL语言集多种功能于一体，是一个综合的、通用的、功能极强的，同时又简洁易学的语言。其主要特点如下：
⑴SQL是一种一体化的语言。
⑵SQL语言是一种高度非过程化的语言。
⑶SQL语言非常简洁。虽然SQL语言功能很强，但它只有为数不多的9条命令。
⑷SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用

SQL语言分类

数据操纵语言(DML)

查询数据(SELECT)也可以分细分到DQL数据查询语言

数据操纵语言(DML)：用于查询、生成、修改、删除数据库中的数据。
l DML语言包含用于查询数据(SELECT)
l 添加新行数据(INSERT)
l 修改现有行数据(UPDATE)
l 删除现在行数据(DELETE)

数据定义语言(DDL)

数据定义语言(DDL)：用于定义、修改、删除数据库模式对象，进行权限管理等。
l DDL语言包括创建、修改、删除或者重命名模式对象(CREATE、ALTER、
DROP、RENAME)的语句
l 删除表中所有行但不删除表(TRUNCATE)
l 使用DDL，定义保存在数据字典。
l DDL语言是自动提交的

数据库控制语言(DCL)

oracle 特有的

数据库控制语言(DCL)：是用来设置或更改数据库用户或角色权限的语句。
l GRANT 授权
l REVOKE 取消授权

事务控制语言(TCL)：用于数据库的事务管理。
l COMMIT 提交
l SAVEPOINT 设置保存点
l ROLLBACK 回滚
l SET TRANSACTION 设置事务属性

l 会话控制(Session Control)
用于控制一个会话(SESSION，指从与数据库连接开始到断开之间的时间过程)的属
性。包括用于控制会话属性(ALTER SESSION)的语句，还包括切换角色(SET ROLE)的语句。

l 系统控制(System Control)
用于管理数据库的属性。只有一条语句，即ALTER SYSTEM

l SQL语句的编写规则
SQL关键字不区分大小写，也就是大小写不敏感
对象名和列名不区分大小写
字符值和日期值区分大小写

Oracle数据库对象

对象与模式
• 模式是一种命名的对象集合
• 一个用户被创建，对应的模式（schema）就被创建
• 一个用户仅仅对应一个模式
• 用户名其实就等同于模式名

模式包涵一下内容：

创建了一个用户，就创建了一个表空间或者模式

SQL> create user fadewalk identified by fadewalk;
User created.

创建一个用户，可以看到随即创建了一个表空间[类似MySQL的数据库]

Oracle数据类型

查看数据库视图所包含的数据类型:
SELECT * FROM DBA_TYPES WHERE OWNER IS NULL

重要:ORACLE数据库汉字占用几个字节，要根据ORACLE中字符集编码决定，一般情况，数据库的NLS_CHARACTERSET 为AL32UTF8或UTF8，即一个汉字占三到四个字节。如果NLS_CHARACTERSET为ZHS16GBK，则一个字符占两个字节.

Oracle PL/SQL语言介绍

l PL/SQL是ORACLE对标准数据库语言的扩展，扩展部分：包、数据类型、异常处理等，近几年中更多的开发人员和DBA开始使用PL/SQL。

l PL/SQL用途：该语言专门用于在各种环境下对Oracle数据库进行访问

PL/SQL的优点如下：
1、PL/SQL是一种高性能的基于事务处理的语言，能运行在任何ORACLE环境中，支持所有数据处理命令。通过使用PL/SQL程序单元处理SQL的数据定义和数据控制元素。
2、PL/SQL支持所有SQL数据类型和所有SQL函数，同时支持所有ORACLE对象类型。
3、PL/SQL块可以被命名和存储在ORACLE服务器中，同时也能被其他的PL/SQL程序或SQL命令调用，任何客户/服务器工具都能访问PL/SQL程序，具有很好的可重用性

4、可以使用ORACLE数据工具管理存储在服务器中的PL/SQL程序的安全性。可以授权或撤销数据库其他用户访问PL/SQL程序的能力。
5、PL/SQL代码可以使用任何ASCII文本编辑器编写，所以对任何ORACLE能够运行的操作系统都是非常便利的
6、对于SQL，ORACLE必须在同一时间处理每一条SQL语句，在网络环境下这就意味作每一个独立的调用都必须被oracle服务器处理，这就占用大量的服务器时间，同时导致网络拥挤。而PL/SQL是以整个语句块发给服务器，这就降低了网络拥挤

PL/SQL块不同部分的作用：
声明部分(Declaration section) ：

声明部分包含了变量和常量的数据类型和初始值。这个部分是由关键字DECLARE开始，如果不需要声明变量或常量，那么可以忽略这一部分；需要说明的是游标的声明也在这一部分。
执行部分(Executable section) ：
执行部分是PL/SQL块中的指令部分，由关键字BEGIN开始，所有的可执行语句都放在这一部分，其他的PL/SQL块也可以放在这一部分

异常处理部分(Exception section) ：
这一部分是可选的，在这一部分中处理异常或错误，对异常处理的详细讨论我们在后面进行。
例：显示问候语的简单PL/SQL块

PL/SQL程序块可被独立编译并存储在数据库中，任何与数据库相连接的应用程序都可以访问这些存储的PL/SQL程序块。

ORACLE提供了四种类型的可存储的程序：
函数
过程
包
触发器

我们创建一个lewen表空间，lewen用户，用于后续环境中的练习：
我的环境是：Oracle 12c/19c for Linux x86_64，在操作系统用root登录：

#su - oracle
sqlplus "/as sysdba"
create tablespace lewen datafile '/data/oracle/oradata/orcl/lewen.dbf' size 100m autoextend off;
create user lewen identified by lewen default tablespace lewen;
grant connect,dba to lewen;
conn lewen/lewen;


#删除
drop tablespace lewen;

[root@MongoDB-01 ~]# ls /data/oracle/oradata/orcl/lewen.dbf -lh
-rw-r----- 1 oracle oinstall 101M Mar 20 20:42 /data/oracle/oradata/orcl/lewen.dbf

将数据导入

imp lewen/lewen file=/tmp/scott_v1.dmp log=/tmp/scott.log ignore=y full=y
imp lewen/lewen file=/tmp/lewen.dmp log=/tmp/lewen.log ignore=y full=y

码农公寓