Oracle基本语法

1 Oracle概述

1.1 常见数据库

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。数据库通常分为层次式数据库、网络式数据库和关系式数据库三种；不同的数据库是按不同的数据结构来联系和组织的。将反映和实现数据联系的方法称为数据模型。层次结构模型实质上是一种有根结点的定向有序树，按照层次模型建立的数据库系统称为层次模型数据库系统；按照网状数据结构建立的数据库系统称为网状数据库系统；关系式数据结构把一些复杂的数据结构归结为简单的二元关系(即二维表格形式)，由关系数据结构组成的数据库系统被称为关系数据库系统。

 

数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过DBMS访问数据库中的数据。数据库管理系统是数据库系统的核心，是管理数据库的软件。

 

常见的关系型数据库有：DB2,Sybase,Oracle,MySQL,Access,MS SQL Server...

1.2 Oracle简介

Oracle甲骨文公司是第一个跨整个产品线（数据库、业务应用软件和应用软件开发与决策支持工具）开发和部署100%基于互联网的企业软件的公司。Oracle是世界领先的信息管理软件供应商和世界第二大独立软件公司。其主要的有：

数据库服务器：oracle(9i,10g/11g,12c),MySQL

应用服务器：WegLogic，GlassFish

开发语言：Java

开发集成环境：NetBean

 

oracle 数据库是当前最主流的数据库之一。

2 Oracle安装与组成

2.1 安装oracle 11g数据库

详见《oracle 11g 32位安装.docx》

2.2 Oracle 11g数据库的组成

Oracle的整体架构：

 

上图示；一般Oracle数据库管理系统由：实例和数据库两部分组成。

 

1、数据库是一系列物理文件的集合（数据文件，控制文件，联机日志，参数文件等）；Oracle数据库由操作系统文件组成，这些文件也称为数据库文件，为数据库信息提供实际物理存储区。Oracle数据库包括逻辑结构和物理结构。数据库的物理结构包含数据库中的一组操作系统文件。数据库的逻辑结构是指数据库创建之后形成的逻辑概念之间的关系，如表、视图、索引等对象。

 

2、实例则是一组Oracle后台进程/线程以及在服务器分配的共享内存区。

 

Oracle可以创建多个oracle数据库，一个oracle数据库将又由实例和数据库构成。如默认安装时创建的orcl数据库外还可再创建其它数据库。创建的数据库将在$oracleHome/oradata/数据库名 目录下以一个个的*.DBF文件体现出来。

 

2.3 Oracle 11g数据库服务

Oracle * VSS Writer Service -- Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service）能够让存储基础设备（比如磁盘，阵列等）创建高保真的时间点映像，即映射拷贝（shadow copy）。它可以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。(非必须启动)

 

OracleDBConsole* -- Oracle数据库控制台服务；在运行Enterprise Manager(企业管理器EM)的时候，需要启动这个服务；此服务被默认设置为自动开机启动的(非必须启动)

 

OracleJobScheduler* -- Oracle作业调度服务。此服务被默认设置为禁用状态(非必须启动)

 

OracleMTSRecoveryService -- 服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。恢复、闪回需要开启该服务(非必须启动)

 

OracleOraDb11g_home1ClrAgent -- Oracle数据库.NET扩展服务的一部分。 (非必须启动)

 

OracleOraDb11g_home1TNSListener -- 监听器服务，服务只有在数据库需要远程访问或使用SQL Developer等工具的时候才需要，此服务被默认的设置为开机启动(非必须启动)

 

OracleService* -- 数据库服务，是Oracle核心服务该服务，是数据库启动的基础，只有该服务启动，Oracle数据库才能正常操作。此服务被默认的设置为开机启动。(必须启动)

3 连接Oracle

3.1 SQL Plus 连接

打开SQL Plus：

 

在上述界面中可以输入用户名，如在安装时解锁了的用户scott,口令为：tiger

 

输入语句查询该用户下的对象：

 

 

另外；也可以直接在命令行中输入sqlplus scott/tiger 进入并登录

3.2 SQL Developer 连接

打开SQL Developer ；在出现界面的左边右击鼠标，新建连接：

 

 

 

注意在上图中；

主机名：如果是本机的按照配置在网络管理中的服务的配置设置，可以为localhost；如果是连接其它机器的数据库则指定其ip；

SID：是指定数据库服务器上的全局数据库名称，默认安装的话一般是orcl

 

 

 

3.3 PLSQL Developer 连接

安装PLSQL Develper；见《PLSQL Developer安装及注册.docx》

 

 

 

3.4 Jdbc 连接

1、 在安装目录下找到oracle的驱动包；

如下路径可以找到oracle的驱动包：

C:\oracle11g\product\11.2.0\dbhome_1\jdbc\lib 复制ojdbc6.jar到项目中进行连接测试；

2、 新建java项目测试连接；

 

 

4 SQL Plus 设置与常用命令

4.1 显示设置

 

-- 设置每行显示的最长字符数

set linesize 120

 

-- 设置一页显示的行数

set pagesize 20

 

-- 设置是否显示一页的记录数

set feedback on/off

 

-- 打开或取消oracle自带的输出方法dbms_output，并输出内容

set serveroutput on/off

 

-- 格式化列的内容：将列名对应的列的值格式化为四位数值长度

col 表中对应的列名 for 9999 

column 表中对应的列名 format 9999

 

【示例】

-- 表明将empno列名对应的列值格式为4位长度的数值型

col empno for 9999  

 

 

-- 格式化列的内容：将列名对应的列的值格式化为10位字母长度

col 表中对应的列名 for a10

 

【示例】

-- 表明将ename列名对应的列值格式为10位长度的字符型

col ename for a10

 

4.2 常用命令

命令

说明

show all

查看系统所有变量值

show user

显示当前连接用户

show error

显示错误

desc 表名

显示表的结构；如：desc emp

--

多行注释

单行注释

/

执行缓冲区中的语句

ed

打开默认编辑器，Windows系统中默认是notepad.exe，把缓冲区中最后一条SQL语句调入afiedt.buf文件中进行编辑（如果提示没有afiedt.buf请使用管理员身份打开SLQ Plus）；常用于语句比较长需要修改时。

spool 文件地址

spool 文件地址 append

spool off

假脱机命令；将命令行的内容（从设置后开始的命令行内容）记录到文本。添加append的意思是在原有的文本内容上追加后续的命令行的内容；需要注意的是所有的这些内容都将在spool off之后才记录。如：

spool d:\itcast\itcast.txt

spool d:\itcast\test.sql append

spool off

clear screen 或者 host cls

清屏

exit

退出SQL Plus

 

5 表空间

表空间是数据库中最大的逻辑单位，Oracle数据库采用表空间将相关的逻辑组件组合在一起，一个Oracle数据库至少包含一个表空间。每个表空间由一个或多个数据文件组成，一个数据文件只能与一个表空间相联系。

在每一个数据库中都有一个名为SYSTEM的表空间，即系统表空间，该表空间是在创建数据库或数据库安装时自动创建的，用于存储系统的数据字典表、程序单元、过程、函数、包和触发器等。

 

5.1 表空间类型

永久性表空间：一般保存表、视图、过程和索引等的数据

临时性表空间：只用于保存系统中短期活动的数据

撤销表空间：用来帮助回退未提交的事务数据

5.2 操作与运用

创建表空间

【语法】

CREATE TABLESPACE 表空间名

   DATAFILE '数据文件路径' SIZE 大小

   [AUTOEXTEND ON] [NEXT 大小]

   [MAXSIZE 大小];

 

【说明】[]里面内容可选项；数据文件路径中若包含目录需要先创建

SIZE为初始表空间大小，单位为K或者M

AUTOEXTEND ON 是否自动扩展

NEXT为文件满了后扩展大小

MAXSIZE为文件最大大小，值为数值或UNLIMITED（表示不限大小）

 

【示例】

CREATE TABLESPACE itcast_ts

   DATAFILE 'd:\oracle_data\itcast01.dbf' SIZE 10M

   AUTOEXTEND ON;

 

查询表空间

--管理员角色查看表空间

SELECT file_name,tablespace_name,bytes,autoextensible

FROM dba_data_files

WHERE tablespace_name='ITCAST_TS';

 

修改表空间

【语法】

ALTER TABLESPACE 表空间名

   ADD DATAFILE '文件路径' SIZE 大小

   [AUTOEXTEND ON] [NEXT 大小]

   [MAXSIZE 大小];

 

【示例】

ALTER TABLESPACE itcast_ts

   ADD DATAFILE 'd:\oracle_data\itcast02.DBF' SIZE 5M

   AUTOEXTEND ON;

 

 

删除表空间

【语法】

DROP TABLESPACE 表空间名;

DROP TABLESPACE 表空间名 INCLUDING CONTENTS AND DATAFILES;

 

【说明】

第一个删除语句只删除表空间；第二个删除语句则删除表空间及数据文件

 

【示例】

DROP TABLESPACE itcast_ts; 

DROP TABLESPACE itcast_ts INCLUDING CONTENTS AND DATAFILES;  

 

6 数据库用户

6.1 系统常见用户

 

用户

说明

sys

超级用户，主要用来维护系统信息和管理实例，以SYSDBA或SYSOPER角色登录。密码为在安装时设置的管理口令，如一般设置为：orcl

system

默认的系统管理员，拥有DBA权限，通常用来管理Oracle数据库的用户、权限和存储，以Normal方式登录。密码为在安装时设置的管理口令，如一般设置为：orcl

scott

示范用户，使用users表空间。一般该用户默认密码为tiger

 

6.2 用户管理

Oracle中有个模式（schema）的概念，它是用户的所有数据库对象的集合；一般在创建用户的同时会自动创建一个这样的模式，名称和用户名称一样。

6.2.1 查询系统用户

select * from all_users;

或

select * from dba_users; --更详细的用户信息

 

6.2.2 解锁用户

【语法】

ALTER USER 用户名 ACCOUNT UNLOCK;

 

【示例】解锁hr用户

alter user hr account unlock;

 

6.2.3 创建用户

【语法】

CREATE USER 用户名 IDENTIFIED BY 密码

   DEFAULT TABLESPACE 表空间;

 

【示例】

CREATE USER itcast IDENTIFIED BY itcast

   DEFAULT TABLESPACE itcast_ts

   TEMPORARY TABLESPACE temp;

 

6.2.4 修改用户密码

【语法】

ALTER USER 用户名 identified by 密码

 

【示例】

ALTER USER itcast identified by it;

 

6.2.5 删除用户

【语法】

DROP USER 用户名 CASCADE;

 

【示例】

DROP USER itcast CASCADE;

 

7 DCL数据控制语言

7.1 授予

【语法1】

GRANT 角色权限（角色）[,角色权限] TO 用户;

 

【示例1】

--授予CONNECT和RESOURCE两个角色

GRANT connect, resource TO itcast;

 

【备注】使用如下语句可以查看resource角色下的权限

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE'

 

 

【语法2】

GRANT 操作 ON 模式.对象  TO 用户;

 

【示例2】

--允许用户查看、更新 EMP 表中的记录

GRANT select,update ON SCOTT.emp TO  itcast;

 

 

--查看当前用户的系统权限

select * from user_sys_privs;

 

--查看当前用户的对象权限

select * from user_tab_privs;

 

--查看当前用户的所有角色

select * from user_role_privs;

 

 

7.2 撤销

 

【语法1】

REVOKE 角色权限（角色）[,角色权限] FROM 用户;

 

【示例1】

--撤销CONNECT和RESOURCE两个角色

REVOKE connect, resource FROM itcast;

 

 

【语法2】

REVOKE 操作 ON 模式.对象  FROM 用户;

 

【示例2】

--撤销用户查看、更新 EMP 表中的记录的操作

REVOKE select,update ON SCOTT.emp FROM  itcast;

 

 

8 DDL数据定义语言

8.1 创建表

 

【语法】

CREATE TABLE (

column1 DATATYPE [NOT NULL] [PRIMARY KEY],

column2 DATATYPE [NOT NULL],

...

[constraint <约束名> 约束类型 (要约束的字段)

... ] );

 

【说明】

DATATYPE --是Oracle的数据类型

NUT NULL --可不可以允许资料有空的（尚未有资料填入）

PRIMARY KEY --是本表的主键

constraint --是对表里的字段添加约束.(约束类型有

            Check,Unique,Primary key,not null,Foreign key);

 

【示例】

create table t_student(

s_id number(8) PRIMARY KEY,

s_name varchar2(20) not null,

s_sex varchar2(8),

clsid number(8),

constraint u_1 unique(s_name),

constraint c_1 check (s_sex in ('MALE','FEMALE'))

);

 

--从现有的表创建表及复制其数据

【语法】

CREATE TABLE as