Oracle专题3之Oracle数据表的

发布时间：2019-09-03 08:58:37编辑：auto阅读（1733）

1、SQLDeveloper工具的介绍

• Oracle SQL Developer是Oracle官方出品的免费图形化开发工具，相对于SQL * Plus来说，图形化的界面便于操作，不必记忆大量的命令，输出结果美观。它的基本功能有结果的格式化输出、编辑器自动提示、代码优化、显示SQL的执行计划、监控会话、编写以及调试存储过程等。

• SQLDeveloper工具的安装以及使用：
• SQLDeveloper工具可以在oracle官网中进行下载：（我下载的是win64，根据情况自行下载，下载后解压至硬盘中即可使用）
  
• 解压后运行sqldeveloper.exe可执行文件，新建连接，添加scott用户后连接即可：
• 
  
• 需要注意，SID是指数据库的唯一标识符，是建立一个数据库时系统自动赋予的一个初始ID，SID主要用于在一些DBA操作以及与操作系统交互，从操作系统的角度访问实例名，必须通过ORACLE_SID，且它在注册表中也是存在的。（如何查看SID，在WINDOWS平台中，可以到注册表中，查看HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE，有一个ORACLE_SID。）
  

  2、SQL和Oracle的SQL

• SQL结构化查询语言（Structured Query Language），简称为SQL。是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统的语言。

• Oracle的SQL：DDL（Data Definition language）数据定义语言、DML（Data Manipulation language）数据操作语言、DCL（Data Control Language）数据控制语言、TCL（Transaction Control Language）事务控制语言。

  DDL语句	说明
  create table	创建数据库表
  create index	创建数据库表的索引
  drop table	删除数据库表
  drop	删除数据库表的索引
  truncate	删除表中的所有行
  alter table	更改表结构，增加，修改，删除列
  alter table add constraint	在已有的表上增加约束

DML语句	说明
INSERT	添加数据到数据库中
UPDATE	修改数据库中的数据
DELETE	删除数据库中的数据
SELECT	选择（查询）数据

DCL语句	说明
grant	将权限或者角色授予用户或者其他角色（如授予访问权限）
revoke	从用户或数据库角色中收回权限（如撤销访问权限）
LOCK	对数据库的特定部分进行锁定

TCL语句	说明
COMMIT	提交事务处理
ROLLBACK	事务处理回退
SAVEPOINT	设置保存点

3、数据库表的操作：创建表

a、创建表的语法结构

• 通过CREATE TABLE语句，如：CREATE TABLE table_name(column_name datatype...);

  b、数据类型（datatype）

• 在创建数据表时，涉及数据表的结构问题，也就是涉及确定数据表中各个列的数据类型，是数值，字符，日期还是图像等其他类型。（因为只有设计好数据表结构，系统才会在磁盘中开辟相应的空间，用户才能够向表中填写数据。）

• Oracle中常用的数据类型有：字符类型、数值类型、日期时间类型、LOB类型。

  字符类型	说明
  CHAR	1、表示固定长度的字符串；2、列长度可以使1到2000个字节。
  VARCHAR2	1、表示可变长度的字符串；2、最大长度为4000字节。

数值类型	说明
NUMBER	声明语法为：NUMBER[(p[, s])]（p表示精度，s表示小数点的位数，可以存储整数、浮点数等数值类型，最高精度为38位）。例如：number(5,0)最多可存储五位整数；number(5,2)最大可存储999.99的浮点数。

日期类型	说明
日期类型	日期时间类型存储日期和时间值，包括年、月、日，小时、分钟、秒。主要的日期时间类型有：DATE。

LOB类型	说明
CLOB	CLOB即Character LOB（字符LOB）：他能够存储大量的字符数据
BLOB	BLOB即Binary LOB（二进制LOB）：可以存储较大的二进制对象，如推片、视频剪辑和声音文件等

c、创建学生信息表结构

• 学生信息表结构分析：

  学生信息表
  表名	学生信息表（student）
  字段名-学号（SID）	number(8,2)
  字段名-姓名（name）	varchar2(20)
  字段名-性别（sex）	char(2)
  字段名-出生日期（birthday）	date
  字段名-家庭住址（address）	varchar2(50)

  ---

• 创建学生信息表的SQL语句：

  SQL> conn scott/02000059
  已连接。
  SQL> create table student(sid number(8,2), name varchar2(20), sex char(2), birthday date, addresss varchar2(50));
  表已创建。
  SQL>

  4、Oracle的表操作：约束

  a、什么是约束？以及Oracle中的约束

• 约束是Oracle提供的自动保持数据库完整性的一种方法，它通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性。

• 完整性约束的基本语法格式为：[CONSTRAINT constraint_name (约束名）]<约束类型>（说明：约束不指定名称时，系统会给定一个名称。）

• Oracle中的约束：主键约束（primary key constraint）、唯一性约束（unique constraint）、默认约束（default constraint）、非空约束（not null constraint）、检查约束（check constraint）、外部键约束（foreign key constraint）。

  b、Oracle的表操作：主键约束

• PRIMARY KEY约束用于定义基本表的主键，它是唯一确定表中每一条记录的标识符，其值不能为NULL，与不能重复，以此来保证实体的完整性。表中主键只能有一个，但是可以由多个列构成。如：primary key（学号、科目编号）
• 创建表时添加主键约束：

  SQL> conn scott/02000059
  已连接。
  SQL> create table student(sid number(8,2),name varchar2(20), sex char(2), birthday date, address varchar2(50), constraint sid_pk primary key(sid));
  表已创建。

  ---

• 修改表时添加主键约束：

  SQL> create table student(sid number(8,2),name varchar2(20), sex char(2), birthday date, address varchar2(50));
  表已创建。
  SQL> alter table student add constraint sid_pk primary key(sid);
  表已更改。

  c、Oracle的表操作：非空约束

• 非空约束（not null）：用于确保列不能为NULL，如果在列上定义了NOT NULL约束，那么当插入数据时，必须为该列提供数据；当更新列数据时，不能将其值设置为NULL。（非空（not null）约束是列级约束）

---

• 附加说明：列级约束与表级约束定义上的区别？
• 列级约束语法格式：column [CONSTRAINT constraint_name] constraint_type，说明：列级约束必须跟着列的定义的后面。

• 表级约束：column, ... [CONSTRAINT constraint_name] constraint_type (column, ...)，说明：表级约束不与列一起，而是单独定义的。

• 创建表时添加非空（not null）约束：（自定义约束名称便于后期维护）

  SQL> conn scott/02000059
  已连接。
  SQL> create table student(sid number(8,0), name varchar2(20) not null, sex char(2) constraint
  2  nn_sex not null,birthday date, address date, constraint sid_pk primary key(sid));
  表已创建。

• 修改表时添加非空约束：添加非空（not null）约束要使用MODIFY语句：ALTER TABLE table_name MODIFY(column datatype NOT NULL);

• 删除非空（not null）约束的语法格式：ALTER TABLE table_name MODIFY column_name datatpe NULL;
• 命令行中的示例：

  SQL> alter table student modify (name varchar2(20) NULL);
  表已更改。
  SQL>

• 附加说明：删除约束的方式有两种：将约束激活或者无效化、将约束彻底删除
• 将约束无效化或者激活：ALTER TABLE table_name DISABLE | ENABLE CONSTRAINT constraint_name;；将约束彻底删除：ALTER TABLE table_name DROP CONSTRAINT constraint_name;。
• 删除主键约束的格式：ALTER TABLE table_name DROP PRIMARY KEY;；
• 命令行中的示例：（使主键约束无效化和删除主键约束）

  SQL> alter table student enable constraint sid_pk;
  表已更改。
  SQL> alter table student drop constraint sid_pk;
  表已更改。
  SQL>

  ---

  d、Oracle的表操作：唯一约束

• 唯一性（unique）约束：唯一性约束用于指定一个或者多个列的组合值具有唯一性，以防止在列中输入重复的值。

• 唯一性约束的注意事项：使用唯一性约束的列允许为空值；一个表中可以允许有多个唯一性约束；可以把唯一性约束定义在多个列上。（当唯一性约束定义在一个列时，可以是表级约束，也可以是列级约束；当唯一性约束定义在多个列时，必须为表级约束。）

• 创建表时添加唯一约束的示例：

  C:\Users\Administrator>sqlplus /nolog
  SQL*Plus: Release 11.2.0.1.0 Production on 星期二 11月 28 21:40:44 2017
  Copyright (c) 1982, 2010, Oracle.  All rights reserved.
  SQL> conn scott/02000059
  已连接。
  SQL> create table student(sid number(8,0), name varchar2(20), sex char(2), birthday date, address varchar2(50), email varchar2(50) unique, cardid varchar2(18), constraint uk_cardid unique(cardid));
  表已创建。
  SQL>

• 修改表时添加唯一性约束：

  SQL> create table student(sid number(8,0), name varchar2(20), sex char(2), birthday date, address varchar2(50), email varchar2(50) unique, cardid varc
  har2(18));
  表已创建。
  SQL> alter table student add constraint uk_cardid unique(cardid);
  表已更改。

• 删除唯一性约束：有禁用约束或者是彻底删除两种方式

  SQL> alter table student disable constraint uk_cardid;
  表已更改。
  SQL> alter table student drop constraint uk_cardid;
  表已更改。
  SQL>

  e、Oracle的表操作：检查约束

• 检查约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据库数据的完整性。

• 创建表时添加检查约束的示例：（可以使用表级约束，也可以使用列级约束）

  SQL> conn scott/02000059
  已连接。
  SQL> create table student(sid number(8,0), name varchar2(20), sex char(2) check(sex='男' or sex = '女'), birthday date, address varchar2(50));
  表已创建。
  SQL>

  SQL> conn scott/02000059
  已连接。
  SQL> create table student(sid number(8,0), name varchar2(20), sex char(2), birthday date, address varchar2(50), constraint ck_sex check(sex='男' or se
  x='女'));
  表已创建。
  SQL>

• 修改表时添加检查（check）约束：

  SQL> alter table student add constraint ck_sex check(sex='男' or sex='女');
  表已更改。

• 删除检查约束：有禁用约束或者是彻底删除两种方式

  SQL> alter table student disable constraint ck_sex;
  表已更改。
  SQL> alter table student drop constraint ck_sex;
  表已更改。

  f、Oracle的表操作：外键约束

• 外键（FOREIGN KEY）是用于建立和加强两个表数据之间的链接的一列或者多列。外键约束是唯一涉及两个表关系的约束。
  

• 需要注意的是主表先建立，从表后建立。（删除时先删除从表，后删除主表）

• 设置外键约束的语法格式：
• 列级约束：CRAETE TABLE 从表 (column_name datatype REFERENCES 主表(column_name) [ON DELETE CASCADE],....);（ON DELETE CASCADE表示的是级联删除）
• 表级约束：CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES 主表(column_name) [ON DELETE CASCADE];
• 创建表时设置列级外键约束示例：

  SQL> create table department(depid varchar2(10) primary key, depname varchar2(30));
  表已创建。
  SQL> create table student(sid number(8,0), name varchar2(20), sex char(2), birthday date, address varchar2(50), depid varchar2(10) references department(depid));
  表已创建。

• 创建表时设置表级外键约束示例：

  C:\Users\Administrator>sqlplus /nolog
  SQL*Plus: Release 11.2.0.1.0 Production on 星期三 11月 29 16:53:36 2017
  Copyright (c) 1982, 2010, Oracle.  All rights reserved.
  SQL> conn scott/02000059
  已连接。
  SQL> create table department(depid varchar2(10) primary key, depname varchar2(30));
  表已创建。
  SQL> create table student(sid number(8,0), name varchar2(20), sex char(2), birthday date, address varchar2(50), depid varchar2(10), constraint fk_depid foreign key(depid) references department(depid) on delete cascade);
  表已创建。
  SQL>

• 外键约束的注意事项：设置外键约束时主表的字段必须是主键列（或者唯一列）；主从表中相应字段必须是同一数据类型；从表中外键字段的值必须来自主表中的相应字段的值，或者为NULL值。

• 修改表时添加外键约束：alter table student add constraint fk_depid foreign key(depid) references department(depid) on delete cascade;

  SQL> alter table student add constraint fk_depid foreign key(depid) references department(depid) on delete cascade;
  表已更改。
  SQL>

  ---

• 删除外键约束：有禁用约束和彻底删除两种方式

  SQL> alter table student disable constraint fk_dep
  表已更改。
  SQL> alter table student drop constraint fk_depid;
  表已更改。
  SQL>

  5、Oracle的表操作：修改和删除表

  a、修改表

• 添加列语法结构：ALTER TABLE 表名 ADD 新增列名 数据类型;
• 举例：ALTER TABLE student ADD tel VARCHAR2(11);

  SQL> ALTER TABLE student ADD tel VARCHAR2(11);
  表已更改。
  SQL>

• 修改列语法结构：ALTER TABLE 表名 MODIFY 列名 新数据类型;
• 举例：ALTER TABLE student MODIFY tel NUMBER(11,0);

  SQL> ALTER TABLE student MODIFY tel NUMBER(11,0);
  表已更改。
  SQL>

• 删除列的语法结构：ALTER TABLE 表名 DROP COLUMN 列名;
• 举例：ALTER TABLE student DROP COLUMN tel;

  SQL> ALTER TABLE student DROP COLUMN tel;
  表已更改。
  SQL>

• 修改列名的语法结构：ALTER TABLE 表名 RENAME COLUMN 列名 TO 新列名
• 举例：ALTER TABLE student RENAME COLUMN sex TO gender;

  SQL> ALTER TABLE student RENAME COLUMN sex TO gender;
  表已更改。
  SQL>

• 修改表名的语法结构：RENAME 表名 TO 新表名;
• 举例：RENAME student TO studnetifo;

  SQL> RENAME student TO studnetifo;
  表已重命名。
  SQL>

  b、删除表

• 删除表有两种方式：TRUNCATE TABLE 表名：用于删除表中的全部数据，并不是把表删除掉，这种删除方式要比DELETE方式删除数据的速度要快，也叫做截断表；DROP TABLE 表名：删除表结构。
• 示例：

  SQL> truncate table emp_bak;
  表被截断。
  SQL> drop table emp_bak;
  表已删除。
  SQL>

  6、Oracel的表操作：增删改查

• 添加信息的语法结构：INSERT INTO 表名[(列1,列2, ... ,)] VALUES(值1,值2, ...,值N);
• 查询信息的语法格式：SELECT * | column[,...] from 表名;
• 修改信息的语法格式：UPDATE table SET column = value [, column = value, ...] [WHERE condition];
• 删除信息的语法格式：DELETE FROM table [WHERE condition];

  7、Oracle的表操作：事务

• 什么是事务：事务可以看做是由对数据库的若干操作组成的一个单元，这些操作要么都完成，要么都取消，从而保证数据满足一致性的要求。
• 事务的组成：一条或者多条DML、一条DLL或者一条DCL语句。（DML语句需要使用COMMIT提交事务或者使用ROLLBACK回滚事务，而DDL和DCL是自动提交事务的。）

• 为什么要使用事务：使用事务的原因是为了保证数据的安全有效。（如当执行事务操作（DML语句）时，Oracle会在被作用表上加表锁，以防止其他用户改变表结构；同时会在被作用行上加行锁，以防止其他事务在相应行上执行DML操作。）

• 事务的控制命令：
• 提交事务（COMMIT）：通过COMMIT语句可以提交事务，当执行了COMMIT语句后，会确认事务的变化、结束事务、删除保存点、释放锁。当使用COMMIT语句结束事务之后，其他会话可以查看到事务变化后的新数据。
• 回滚事务（ROLLBACK）：ROLLBACK只能对未提交的数据撤销，已经commit的数据时无法撤销的，因为commit之后已经持久化到数据库中。
• 保存点（SAVEPOINT）：是事务中的一点，用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点。当执行ROLLBACK时，通过指定保存点可以回退到指定的点。（设置保存点：SAVEPOINT a;， 回滚部分事务：ROLLBACK TO a;，回滚全部事务：ROLLBACK;）

  8、Oracle数据字典

• 什么是数据字典：是Oracle存放有关数据库信息的地方，其用途是用来描述数据的。数据库数据字典是一组表和视图结构。数据字典中的表时不能直接被访问的，但是可以访问数据字典中的视图。

• 数据字典的作用：通过数据字典，使我们了解数据库内部的信息。当用户在对数据库中的数据进行操作时遇到困难就可以访问数据字典来查看详细的信息。

• Oracle中常用的数据字典：Oracle中常用的数据字典分为三类，以三种前缀开头：user*、all、dba_。
• user_*：该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象）
• all*：该视图存储了当前用户能够访问的对象的信息。（与user相比，all_并不需要拥有该对象，只需要具有访问该对象的权限即可。）
• dba_*：该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限。）

  a、Oracle中常用的数据字典

数据字典的名称	说明
user_users	关于用户的信息
user_tablespaces	关于表空间的信息
user_tables	关于数据库表的信息
user_views	关于视图的信息
user_sequences	关于用户序列信息
user_constraints	关于用户表的约束信息
user_triggers	关于用户的触发器信息
user_source	关于用户存储过程信息

b、应用数据字典查看相应的数据库信息（例）

• 查看当前用户下的用户信息：SELECT * FROM user_users;

• 查看scott用户下的当前用户的用户信息：
  

• 查看用户有权访问的所有用户的基本信息：SELECT * FROM all_users;

• 查看scott用户下的所有用户的基本信息：
  

• 查看数据库所有用户的用户信息：SELECT *FROM dba_users;
• 使用scott用户无法查看所有用户的用户信息：

  SQL> SELECT *FROM dba_users;
  SELECT * FROM dba_users
  第 1 行出现错误:
  ORA-00942: 表或视图不存在
  SQL>

• 使用sys用户查看所有用户的用户信息：
  

关键字：