Oracle數(shù)據(jù)表如何管理

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

1、SQLDeveloper工具的介紹

• Oracle SQL Developer是Oracle官方出品的免費(fèi)圖形化開發(fā)工具，相對于SQL * Plus來說，圖形化的界面便于操作，不必記憶大量的命令，輸出結(jié)果美觀。它的基本功能有結(jié)果的格式化輸出、編輯器自動(dòng)提示、代碼優(yōu)化、顯示SQL的執(zhí)行計(jì)劃、監(jiān)控會(huì)話、編寫以及調(diào)試存儲過程等。

• SQLDeveloper工具的安裝以及使用：

• SQLDeveloper工具可以在oracle官網(wǎng)中進(jìn)行下載：（我下載的是win64，根據(jù)情況自行下載，下載后解壓至硬盤中即可使用）
  

• 解壓后運(yùn)行sqldeveloper.exe可執(zhí)行文件，新建連接，添加scott用戶后連接即可：

• 
  

• 需要注意，SID是指數(shù)據(jù)庫的唯一標(biāo)識符，是建立一個(gè)數(shù)據(jù)庫時(shí)系統(tǒng)自動(dòng)賦予的一個(gè)初始ID，SID主要用于在一些DBA操作以及與操作系統(tǒng)交互，從操作系統(tǒng)的角度訪問實(shí)例名，必須通過ORACLE_SID，且它在注冊表中也是存在的。（如何查看SID，在WINDOWS平臺中，可以到注冊表中，查看HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE，有一個(gè)ORACLE_SID。）
  

  2、SQL和Oracle的SQL

• SQL結(jié)構(gòu)化查詢語言（Structured Query Language），簡稱為SQL。是一種特殊目的的編程語言，是一種數(shù)據(jù)庫查詢和程序設(shè)計(jì)語言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng)的語言。

• Oracle的SQL：DDL（Data Definition language）數(shù)據(jù)定義語言、DML（Data Manipulation language）數(shù)據(jù)操作語言、DCL（Data Control Language）數(shù)據(jù)控制語言、TCL（Transaction Control Language）事務(wù)控制語言。

  DDL語句	說明
  create table	創(chuàng)建數(shù)據(jù)庫表
  create index	創(chuàng)建數(shù)據(jù)庫表的索引
  drop table	刪除數(shù)據(jù)庫表
  drop	刪除數(shù)據(jù)庫表的索引
  truncate	刪除表中的所有行
  alter table	更改表結(jié)構(gòu)，增加，修改，刪除列
  alter table add constraint	在已有的表上增加約束

3、數(shù)據(jù)庫表的操作：創(chuàng)建表

a、創(chuàng)建表的語法結(jié)構(gòu)

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

  b、數(shù)據(jù)類型（datatype）

• 在創(chuàng)建數(shù)據(jù)表時(shí)，涉及數(shù)據(jù)表的結(jié)構(gòu)問題，也就是涉及確定數(shù)據(jù)表中各個(gè)列的數(shù)據(jù)類型，是數(shù)值，字符，日期還是圖像等其他類型。（因?yàn)橹挥性O(shè)計(jì)好數(shù)據(jù)表結(jié)構(gòu)，系統(tǒng)才會(huì)在磁盤中開辟相應(yīng)的空間，用戶才能夠向表中填寫數(shù)據(jù)。）

• Oracle中常用的數(shù)據(jù)類型有：字符類型、數(shù)值類型、日期時(shí)間類型、LOB類型。

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

c、創(chuàng)建學(xué)生信息表結(jié)構(gòu)

• 學(xué)生信息表結(jié)構(gòu)分析：

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

  ---

• 創(chuàng)建學(xué)生信息表的SQL語句：

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

  4、Oracle的表操作：約束

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

• 約束是Oracle提供的自動(dòng)保持?jǐn)?shù)據(jù)庫完整性的一種方法，它通過限制字段中數(shù)據(jù)、記錄中數(shù)據(jù)和表之間的數(shù)據(jù)來保證數(shù)據(jù)的完整性。

• 完整性約束的基本語法格式為：[CONSTRAINT constraint_name (約束名）]<約束類型>（說明：約束不指定名稱時(shí)，系統(tǒng)會(huì)給定一個(gè)名稱。）

• Oracle中的約束：主鍵約束（primary key constraint）、唯一性約束（unique constraint）、默認(rèn)約束（default constraint）、非空約束（not null constraint）、檢查約束（check constraint）、外部鍵約束（foreign key constraint）。

  b、Oracle的表操作：主鍵約束

• PRIMARY KEY約束用于定義基本表的主鍵，它是唯一確定表中每一條記錄的標(biāo)識符，其值不能為NULL，與不能重復(fù)，以此來保證實(shí)體的完整性。表中主鍵只能有一個(gè)，但是可以由多個(gè)列構(gòu)成。如：primary key（學(xué)號、科目編號）

• 創(chuàng)建表時(shí)添加主鍵約束：

  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));
  表已創(chuàng)建。

  ---

• 修改表時(shí)添加主鍵約束：

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

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

• 非空約束（not null）：用于確保列不能為NULL，如果在列上定義了NOT NULL約束，那么當(dāng)插入數(shù)據(jù)時(shí)，必須為該列提供數(shù)據(jù)；當(dāng)更新列數(shù)據(jù)時(shí)，不能將其值設(shè)置為NULL。（非空（not null）約束是列級約束）

• 附加說明：列級約束與表級約束定義上的區(qū)別？

• 列級約束語法格式：column [CONSTRAINT constraint_name] constraint_type，說明：列級約束必須跟著列的定義的后面。

• 表級約束：column, ... [CONSTRAINT constraint_name] constraint_type (column, ...)，說明：表級約束不與列一起，而是單獨(dú)定義的。

• 創(chuàng)建表時(shí)添加非空（not null）約束：（自定義約束名稱便于后期維護(hù)）

  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));
  表已創(chuàng)建。

• 修改表時(shí)添加非空約束：添加非空（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）約束：唯一性約束用于指定一個(gè)或者多個(gè)列的組合值具有唯一性，以防止在列中輸入重復(fù)的值。

• 唯一性約束的注意事項(xiàng)：使用唯一性約束的列允許為空值；一個(gè)表中可以允許有多個(gè)唯一性約束；可以把唯一性約束定義在多個(gè)列上。（當(dāng)唯一性約束定義在一個(gè)列時(shí)，可以是表級約束，也可以是列級約束；當(dāng)唯一性約束定義在多個(gè)列時(shí)，必須為表級約束。）

• 創(chuàng)建表時(shí)添加唯一約束的示例：

  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));
  表已創(chuàng)建。
  SQL>

• 修改表時(shí)添加唯一性約束：

  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));
  表已創(chuàng)建。
  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的表操作：檢查約束

• 檢查約束對輸入列或者整個(gè)表中的值設(shè)置檢查條件，以限制輸入值，保證數(shù)據(jù)庫數(shù)據(jù)的完整性。

• 創(chuàng)建表時(shí)添加檢查約束的示例：（可以使用表級約束，也可以使用列級約束）

  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));
  表已創(chuàng)建。
  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='女'));
  表已創(chuàng)建。
  SQL>

• 修改表時(shí)添加檢查（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）是用于建立和加強(qiáng)兩個(gè)表數(shù)據(jù)之間的鏈接的一列或者多列。外鍵約束是唯一涉及兩個(gè)表關(guān)系的約束。
  

• 需要注意的是主表先建立，從表后建立。（刪除時(shí)先刪除從表，后刪除主表）

• 設(shè)置外鍵約束的語法格式：

• 列級約束：CRAETE TABLE 從表 (column_name datatype REFERENCES 主表(column_name) [ON DELETE CASCADE],....);（ON DELETE CASCADE表示的是級聯(lián)刪除）

• 表級約束：CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES 主表(column_name) [ON DELETE CASCADE];

• 創(chuàng)建表時(shí)設(shè)置列級外鍵約束示例：

  SQL> create table department(depid varchar2(10) primary key, depname varchar2(30));
  表已創(chuàng)建。
  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));
  表已創(chuàng)建。

• 創(chuàng)建表時(shí)設(shè)置表級外鍵約束示例：

  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));
  表已創(chuàng)建。
  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);
  表已創(chuàng)建。
  SQL>

• 外鍵約束的注意事項(xiàng)：設(shè)置外鍵約束時(shí)主表的字段必須是主鍵列（或者唯一列）；主從表中相應(yīng)字段必須是同一數(shù)據(jù)類型；從表中外鍵字段的值必須來自主表中的相應(yīng)字段的值，或者為NULL值。

• 修改表時(shí)添加外鍵約束：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、修改表

• 添加列語法結(jié)構(gòu)：ALTER TABLE 表名 ADD 新增列名 數(shù)據(jù)類型;

• 舉例：ALTER TABLE student ADD tel VARCHAR2(11);

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

• 修改列語法結(jié)構(gòu)：ALTER TABLE 表名 MODIFY 列名 新數(shù)據(jù)類型;

• 舉例：ALTER TABLE student MODIFY tel NUMBER(11,0);

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

• 刪除列的語法結(jié)構(gòu)：ALTER TABLE 表名 DROP COLUMN 列名;

• 舉例：ALTER TABLE student DROP COLUMN tel;

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

• 修改列名的語法結(jié)構(gòu)：ALTER TABLE 表名 RENAME COLUMN 列名 TO 新列名

• 舉例：ALTER TABLE student RENAME COLUMN sex TO gender;

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

• 修改表名的語法結(jié)構(gòu)：RENAME 表名 TO 新表名;

• 舉例：RENAME student TO studnetifo;

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

  b、刪除表

• 刪除表有兩種方式：TRUNCATE TABLE 表名：用于刪除表中的全部數(shù)據(jù)，并不是把表刪除掉，這種刪除方式要比DELETE方式刪除數(shù)據(jù)的速度要快，也叫做截?cái)啾?；DROP TABLE 表名：刪除表結(jié)構(gòu)。

• 示例：

  SQL> truncate table emp_bak;
  表被截?cái)唷?
  SQL> drop table emp_bak;
  表已刪除。
  SQL>

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

• 添加信息的語法結(jié)構(gòu)：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的表操作：事務(wù)

• 什么是事務(wù)：事務(wù)可以看做是由對數(shù)據(jù)庫的若干操作組成的一個(gè)單元，這些操作要么都完成，要么都取消，從而保證數(shù)據(jù)滿足一致性的要求。

• 事務(wù)的組成：一條或者多條DML、一條DLL或者一條DCL語句。（DML語句需要使用COMMIT提交事務(wù)或者使用ROLLBACK回滾事務(wù)，而DDL和DCL是自動(dòng)提交事務(wù)的。）

• 為什么要使用事務(wù)：使用事務(wù)的原因是為了保證數(shù)據(jù)的安全有效。（如當(dāng)執(zhí)行事務(wù)操作（DML語句）時(shí)，Oracle會(huì)在被作用表上加表鎖，以防止其他用戶改變表結(jié)構(gòu)；同時(shí)會(huì)在被作用行上加行鎖，以防止其他事務(wù)在相應(yīng)行上執(zhí)行DML操作。）

• 事務(wù)的控制命令：

• 提交事務(wù)（COMMIT）：通過COMMIT語句可以提交事務(wù)，當(dāng)執(zhí)行了COMMIT語句后，會(huì)確認(rèn)事務(wù)的變化、結(jié)束事務(wù)、刪除保存點(diǎn)、釋放鎖。當(dāng)使用COMMIT語句結(jié)束事務(wù)之后，其他會(huì)話可以查看到事務(wù)變化后的新數(shù)據(jù)。

• 回滾事務(wù)（ROLLBACK）：ROLLBACK只能對未提交的數(shù)據(jù)撤銷，已經(jīng)commit的數(shù)據(jù)時(shí)無法撤銷的，因?yàn)閏ommit之后已經(jīng)持久化到數(shù)據(jù)庫中。

• 保存點(diǎn)（SAVEPOINT）：是事務(wù)中的一點(diǎn)，用于取消部分事務(wù)，當(dāng)結(jié)束事務(wù)時(shí)，會(huì)自動(dòng)的刪除該事務(wù)所定義的所有保存點(diǎn)。當(dāng)執(zhí)行ROLLBACK時(shí)，通過指定保存點(diǎn)可以回退到指定的點(diǎn)。（設(shè)置保存點(diǎn)：SAVEPOINT a;， 回滾部分事務(wù)：ROLLBACK TO a;，回滾全部事務(wù)：ROLLBACK;）

  8、Oracle數(shù)據(jù)字典

• 什么是數(shù)據(jù)字典：是Oracle存放有關(guān)數(shù)據(jù)庫信息的地方，其用途是用來描述數(shù)據(jù)的。數(shù)據(jù)庫數(shù)據(jù)字典是一組表和視圖結(jié)構(gòu)。數(shù)據(jù)字典中的表時(shí)不能直接被訪問的，但是可以訪問數(shù)據(jù)字典中的視圖。

• 數(shù)據(jù)字典的作用：通過數(shù)據(jù)字典，使我們了解數(shù)據(jù)庫內(nèi)部的信息。當(dāng)用戶在對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行操作時(shí)遇到困難就可以訪問數(shù)據(jù)字典來查看詳細(xì)的信息。

• Oracle中常用的數(shù)據(jù)字典：Oracle中常用的數(shù)據(jù)字典分為三類，以三種前綴開頭：user*、all、dba_。

• user_*：該視圖存儲了關(guān)于當(dāng)前用戶所擁有的對象的信息。（即所有在該用戶模式下的對象）

• all*：該視圖存儲了當(dāng)前用戶能夠訪問的對象的信息。（與user相比，all_并不需要擁有該對象，只需要具有訪問該對象的權(quán)限即可。）

• dba_*：該視圖存儲了數(shù)據(jù)庫中所有對象的信息。（前提是當(dāng)前用戶具有訪問這些數(shù)據(jù)庫的權(quán)限，一般來說必須具有管理員權(quán)限。）

  a、Oracle中常用的數(shù)據(jù)字典

b、應(yīng)用數(shù)據(jù)字典查看相應(yīng)的數(shù)據(jù)庫信息（例）

• 查看當(dāng)前用戶下的用戶信息：SELECT * FROM user_users;

• 查看scott用戶下的當(dāng)前用戶的用戶信息：
  

• 查看用戶有權(quán)訪問的所有用戶的基本信息：SELECT * FROM all_users;

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

• 查看數(shù)據(jù)庫所有用戶的用戶信息：SELECT *FROM dba_users;

• 使用scott用戶無法查看所有用戶的用戶信息：

  SQL> SELECT *FROM dba_users;
  SELECT * FROM dba_users
  第 1 行出現(xiàn)錯(cuò)誤:
  ORA-00942: 表或視圖不存在
  SQL>

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

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