Oracle數據表如何管理

這篇文章主要介紹了Oracle數據表如何管理，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

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	在已有的表上增加約束

3、數據庫表的操作：創建表

a、創建表的語法結構

• 通過CREATE TABLE語句，如：CREATE TABLE table_name(column_name datatype...);

  b、數據類型（datatype）

• 在創建數據表時，涉及數據表的結構問題，也就是涉及確定數據表中各個列的數據類型，是數值，字符，日期還是圖像等其他類型。（因為只有設計好數據表結構，系統才會在磁盤中開辟相應的空間，用戶才能夠向表中填寫數據。）

• Oracle中常用的數據類型有：字符類型、數值類型、日期時間類型、LOB類型。

  字符類型	說明
  CHAR	1、表示固定長度的字符串；2、列長度可以使1到2000個字節。
  VARCHAR2	1、表示可變長度的字符串；2、最大長度為4000字節。

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中常用的數據字典

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用戶查看所有用戶的用戶信息：
  

感謝你能夠認真閱讀完這篇文章，希望小編分享的“Oracle數據表如何管理”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!