Oracle入门教程与实战

前言

Oracle 数据库系统是美国 Oracle 公司(甲骨文)提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器 (Client/Server) 或 B/S 体系结构的数据库之一，比如 SilverStream 就是基于数据库的一种中间件。 Oracle 数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系型数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能，但它的所有知识，只要在一种机型上学习了 Oracle 知识，便能在各种类型的机器上使用它。

Oracle 的基本使用

连接命令

sqlplus /nolog

进入 sqlplus 环境。其中 /nolog 是不登陆到数据库服务器的意思，如果没有 /nolog 参数， sqlplus 会提示你输入用户名和密码。

C:\Users\Administrator>sqlplus

SQL*Plus: Release 11.1.0.7.0 - Production on 星期三 10月 16 10:30:42 2019

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

请输入用户名:

conn[etc]

用法: conn 用户名 / 密码 @网络服务名 [as sysdba/sysoper] 当用特权用户身份连接时，必须带上 as sysdba 或是 as sysoper

以系统管理员 (sysdba) 身份连接数据库

SQL> conn / as sysdba
已连接。

创建用户

SQL> create user huang identified by 123456;

用户已创建。

用户授权

SQL> grant create session,connect,resource to huang;

授权成功。

连接到数据库

SQL> conn huang/123456
已连接。

show user

显示当前用户名

SQL> show user
USER 为 "HUANG"

passw[ord]

用于修改用户的密码，如果要想修改其它用户的密码，需要用 sys/system 登录。

SQL> passw
更改 HUANG 的口令
旧口令:
新口令:
重新键入新口令:
口令已更改

disc[onnect]

用于断开与当前数据库的连接 (不退出 sqlplus )

SQL> disc
从 Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
SQL>

exit

用于断开与当前数据库的连接 (同时退出 sqlplus )

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

C:\Users\Administrator>

文件操作命令

start 和 @

运行 SQL 脚本

SQL> @ d:\c.sql

表已创建。

或是

SQL> start d:\c.sql

表已创建。

edit

该命令可以编辑指定的 SQL 脚本

SQL> edit d:\c.sql

这样会把 d:\c.sql 这个文件打开

spool

该命令可以将 sqlplus 屏幕上的(查询)内容输出到指定文件中去

SQL> spool d:\d.sql

SQL> select ASSETNUM 序号,DESCRIPTION 描述 from ASSET;

SQL> spool off

交互式命令

&

可以替代变量，而该变量在执行时，需要用户输入， Oracle 会提示用户输入值

SQL> select * from ACCOUNTDEFAULTS where ORGID='&ORGID';
输入 orgid 的值:

显示和设置环境变量

可以用来控制输出的各种格式，如果希望永久的保存相关设置，可以修改 glogin.sql 脚本

linesize

设置显示行的宽度，默认是 80 个字符

SQL> show linesize
linesize 80
SQL> set linesize 90
SQL> show linesize
linesize 90

pagelize

设置每页显示的行数目，默认是 14 ，用法和 linesize 一样

SQL> show pagesize
pagesize 14
SQL> set pagesize 20
SQL> show pagesize
pagesize 20

Oracle 用户管理

创建用户

在 Oracle 中要创建一个新的用户使用 create user 语句， 一般是具有 dba (数据库管理员)的权限才能使用。

create user 用户名 identified by 密码 ;

SQL> create user zhangsan identified by 123456;
create user zhangsan identified by 123456
                                   *
第 1 行出现错误:
ORA-01031: 权限不足

我们连接到 sysdba 创建用户

SQL> conn / as sysdba
已连接。
SQL> create user zhangsan identified by 123456;

用户已创建。

给用户修改密码

如果给自己修改密码可以直接使用

password 用户名

SQL> password huang
更改 huang 的口令
旧口令:
新口令:
重新键入新口令:
口令已更改

如果给别人修改密码则需要具有 dba 的权限，或是拥有 alter user 的系统权限

 alter user 用户名 identified by 新密码

SQL> alter user zhangsan identified by 12345678;

用户已更改。

删除用户

一般以 dba 的身份去删除某个用户， 如果用其它用户去删除用户则需要具有 drop user 的权限。

drop user 用户名 [cascade]

SQL> drop user zhangsan;

用户已删除。

如果要删除的用户，已经创建了表，那么就需要在删除的时候带一个参数 cascade ;

权限和角色

权限

创建的新用户是没有任何权限的， 甚至连登陆的数据库的权限都没有， 需要为其指定相应的权限；要使用有能力授权的用户，如 sys 、 system 。

权限包含系统权限和对象权限

• 系统权限：用户对数据库的相关权限
• 对象权限：用户对其他用户的数据对象操作的权限

角色

角色是指由系统权限集合。通常给某个用户授予权限时如果没有角色存在的话，那么需要一条一条的操作，角色的存在就是使得授权变得很方便。通常一个角色由多个系统权限组成。常用的角色有三个 connect (7种权限)、 dba 、 resource (在任何表空间建表)。

connect 角色：是授予最终用户的典型权利，最基本的

• alter session 修改会话
• create cluster 建立聚簇
• create database link 建立数据库链接
• create sequence 建立序列
• create session 建立会话
• create synonym 建立同义词
• create view 建立视图

resource 角色： 是授予开发人员的

• create cluster 建立聚簇
• create procedure 建立过程
• create sequence 建立序列
• create table 建表
• cteate trigger 建立触发器
• create type 建立类型

dba 角色：拥有系统所有系统级权限

使用 grant 命令给用户分配权限：

grant 【权限名】 to 【用户名】

• 分配角色

grant 【角色名】 to 【用户名】

• 收回权限

revoke 【权限名】 from 【用户名】

用户管理的综合案例

SQL> create user zhangsan identified by 123456; /*创建用户*/

用户已创建。

SQL> conn zhangsan/123456; /*新建用户没有 session (登陆)权限*/
ERROR:
ORA-01045: 用户 ZHANGSAN 没有 CREATE SESSION 权限; 登录被拒绝


警告: 您不再连接到 ORACLE。
SQL> show user
USER 为 ""
SQL> conn / as sysdba;
已连接。
SQL> grant create session to zhangsan; /*使 zhangsan 能够被连接*/

授权成功。

SQL> conn zhangsan/123456;
已连接。
SQL> show user;
USER 为 "ZHANGSAN"
SQL> conn / as sysdba;
已连接。
SQL> grant resource to zhangsan; /*让zhangsan 能够在任何表空间下建表*/

授权成功。

SQL> create table users(name varchar(10),age number(3)); /*在 sys 角色下创建一个简单的表 users */

表已创建。

SQL> insert into users values('张三',22); /*插入数据*/

已创建 1 行。

SQL> insert into users values('李四',24);

已创建 1 行。

SQL> select * from users; /*查询*/

NAME                        AGE
-------------------- ----------
张三                         22
李四                         24

SQL> conn zhangsan/123456;
已连接。
SQL> select * from sys.users; /*新建的用户没有查询 sys 表的权限*/
select * from sys.users
                  *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> conn / as sysdba;
已连接。
SQL> grant select on users to zhangsan; /*登录到 sys 给 zhangsan 授权让 zhangsan 可以查看 sys 下的 users 表*/

授权成功。

SQL> conn zhangsan/123456;
已连接。
SQL> select * from sys.users; /* 登录到 zhangsan 下查看 users 表*/

NAME                        AGE
-------------------- ----------
张三                         22
王五                         24

SQL> update sys.users set name='张小三' where name='张三'; /*这时如果想要更新 sys.users 中的数据，会提示 "ORA-01031: 权限不足 " 。因为 sys 只给了 zhangsan 查看的权利，如果仍然想更新，要到 sys 下进行授权*/
update sys.users set name='张小三' where name='张三'
           *
第 1 行出现错误:
ORA-01031: 权限不足


SQL> conn / as sysdba
已连接。
SQL> grant update on users to zhangsan; /*登录到 sys 给 zhangsan 授权让 zhangsan 可以更新 sys 下的 users 表*/

授权成功。

SQL> conn zhangsan/123456;
已连接。
SQL> update sys.users set name='张小三' where name='张三';

已更新 1 行。

SQL> select * from sys.users;

NAME                        AGE
-------------------- ----------
张小三                       22
王五                         24

SQL> revoke resource from zhangsan; /*登陆到 sys 下回收 resource 权限*/

撤销成功。

SQL> revoke select on users from zhangsan; /*登陆到 sys 下回收 select 权限*/

撤销成功。

SQL> conn zhangsan/123456;
已连接。
SQL> select * from sys.users; /* 这时 sys 就不能再查询 sys.users 的数据了*/
select * from sys.users
                  *
第 1 行出现错误:
ORA-00942: 表或视图不存在

使用 profile 管理用户口令

profile 是口令限制，资源限制的命令集合。当建立数据库时， Oracle 会自动建立名称为 default 的 profile。当建立用户没有制定 profile 选项，那 Oracle 就会将 default 分配给用户。

帐号锁定

指定登录时最多可以输入密码的次数，也可以指定用户锁定的时间，以天为单位。一般用 dba 的身份去执行命令。

指定用户 huang 最多只能尝试三次登录，锁定时间为 2 天。

SQL> conn / as sysdba;
已连接。
SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

配置文件已创建

SQL> alter user huang profile lock_account;

用户已更改。

按 CTRL + C 退出来验证账号锁定

C:\Users\Administrator>sqlplus

SQL*Plus: Release 11.1.0.7.0 - Production on 星期三 10月 30 11:13:48 2019

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

请输入用户名:  huang
输入口令:
ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝


请输入用户名:  huang
输入口令:
ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝


请输入用户名:  huang
输入口令:
ERROR:
ORA-28000: 帐户已被锁定


SP2-0157: 在 3 次尝试之后无法连接到 ORACLE, 退出 SQL*Plus

账户解锁

SQL> alter user huang account unlock;

用户已更改。

SQL> conn huang;
输入口令:
已连接。

终止口令

为了让用户定期修改密码，可以使用终止口令的指令完成，同样这个命令也要 dba 身份来操作。

给 huang 创建一个 profile 文件，要求该用户每隔 10 天要修改登录密码，宽限期 2 天

SQL> create profile huang limit password_life_time 10 password_grace_time 2;

配置文件已创建

SQL> alter user huang profile huang;

用户已更改。

解锁方式同上

口令历史

如果希望用户在修改密码时，不能使用以前用过的密码，可以使用口令历史，这样 Oracle 就会将口令修改的信息存放在数据字典中，这样当用户修改密码时， Oracle 就会对新密码与就得进行对比，如果一样提示用户重新输入。

SQL> create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10;

配置文件已创建

SQL> alter user huang profile password_history;

用户已更改。

SQL> alter user huang identified by 12345678;

用户已更改。

SQL> alter user huang identified by 12345678;
alter user huang identified by 12345678
*
第 1 行出现错误:
ORA-28007: 无法重新使用口令

password_reuse_time 10 表示 10 天后口令可重复使用。

删除 profile

SQL> drop profile password_history cascade;

配置文件已删除。

cascade 表示如果已经将 profile分 配给某个用户时，仍要删除 profile，就要加上 cascade。

Oracle 表的管理

表名和列的命名规则

• 必须以字母开头
• 长度不能超过 30 个字符
• 不能使用 Oracle 的保留字
• 只能使用如下字符 A-Z ， a-z ， 0-9 ， $ , # 等

Oracle 支持的数据类型

字符型

char 定长，最长2000字符

例如： char(10) 存储内容为“小黄”时，前4个字符放‘小黄’，后六位由空格补齐

优点是：效率高，查询速率快。如身份证的字段可以设置成 char(18)

varchar2 变长最大 4000 字符( Oracle 推荐使用)

varchar2(10) 存储内容为“小黄”时， Oracle 分配 4 个字符

clob(character large object) 字符型大对象，最大 4G

数字类型

number 范围 -10 的 38 次方到 10 的 38 次方，可以是整数，也可以是小数

number(5，2) 表示一个小数有5位有效数字， 2 位是小数

例如：定义一个范围在 -999.99-999.99 的数字可以用 number(5，2)，定义一个范围在 -99999-99999 可以用 number(5)

日期类型

date 包含年月日和时分秒

timestamp Oracle 对 date 类型的扩展，可以精确到毫秒。

图片类型

blob 二进制数据，可以存放图片，音频，视频最大 4G ，这个类型允许我们将大文件存储进数据库，但是一般在数据库里，存放的应该是这些文件的路径，如果对安全性有要求，可以将文件放入数据库(一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放的)。

建表语句

建表

• 学生表

create table student( /*表名*/
    id number(4), /*学号*/
    name varchar2(20), /*姓名*/
    sex char(2), /*性别*/
    birthday date, /*出生日期*/
    sal number(6,2) /*奖学金*/
    );


SQL> desc student; /*查看表结构*/
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(4)
 NAME                                               VARCHAR2(20 CHAR)
 SEX                                                CHAR(2 CHAR)
 BIRTHDAY                                           DATE
 SAL                                                NUMBER(6,2)

• 班级表

create table class(
    c_id number(2),
    c_name varchar2(40)
    );


SQL> desc class;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 C_ID                                               NUMBER(2)
 C_NAME                                             VARCHAR2(40 CHAR)

修改表

SQL> alter table student add (c_id number(2)); /*添加字段*/

表已更改。

SQL> alter table student modify (name varchar2(50)); /*修改字段的长度*/

表已更改。

SQL> alter table student modify (name char(20)); /*修改字段的类型(表中不能有数据)*/

表已更改。

SQL> alter table student rename column name to s_name; /*修改字段的名字(表中不能有数据)*/

表已更改。

SQL> alter table student drop column sex; /*删除一个字段(慎重使用)*/

表已更改。

SQL> rename student to stu; /*修改表的名字*/

表已重命名。

SQL> desc stu;
 名称                                      是否为空? 类型