Sử dụng câu lệnh DDL

Sử dụng câu lệnh DDL để tạo và quản lý bảng

Một lược đồ là tập hợp của nhiều đối tượng cơ sở dữ liệu, được gọi là đối tượng lược đồ. Các đối tượng này có quyền truy cập trực tiếp bởi lược đồ chủ sở hữu của chúng. Bảng dưới đây liệt kê các đối tượng lược đồ.

Bảng - để lưu trữ dữ liệu
Xem - để chiếu dữ liệu ở định dạng mong muốn từ một hoặc nhiều bảng
Trình tự - để tạo giá trị số
Chỉ mục - để cải thiện hiệu suất của các truy vấn trên bảng
Từ đồng nghĩa - tên thay thế của một đối tượng

Một trong những bước đầu tiên trong việc tạo cơ sở dữ liệu là tạo các bảng lưu trữ dữ liệu của tổ chức. Thiết kế cơ sở dữ liệu liên quan đến việc xác định các yêu cầu của người dùng hệ thống đối với các hệ thống tổ chức khác nhau như nhập đơn đặt hàng, quản lý hàng tồn kho và các khoản phải thu. Bất kể kích thước và độ phức tạp của cơ sở dữ liệu, mỗi cơ sở dữ liệu bao gồm các bảng.

Tạo bảng

Để tạo bảng trong cơ sở dữ liệu, DBA phải có một số thông tin nhất định trong tay - tên bảng, tên cột, kiểu dữ liệu cột và kích thước cột. Tất cả thông tin này có thể được sửa đổi sau đó bằng cách sử dụng các lệnh DDL.

Quy ước đặt tên bảng -

Tên bạn chọn cho bảng phải tuân theo các quy tắc tiêu chuẩn sau:
Tên phải bắt đầu bằng chữ cái AZ hoặc az
Có thể chứa số và dấu gạch dưới
Có thể ở dạng UPPER của chữ thường
Có thể dài tối đa 30 ký tự
Không thể sử dụng cùng tên của một đối tượng hiện có khác trong giản đồ của bạn
Không được là một từ dành riêng cho SQL

Theo các hướng dẫn trên, 'EMP85' có thể là một tên bảng hợp lệ, nhưng 85EMP thì không. Tương tự như vậy, UPDATE không thể được chọn làm tên bảng vì nó là một từ khóa dành riêng cho SQL.

Câu lệnh CREATE TABLE

CREATE TABLE là một câu lệnh DDL được sử dụng để tạo bảng trong cơ sở dữ liệu. Bảng được tạo ngay sau khi tập lệnh CREATE TABLE được thực thi và sẵn sàng giữ dữ liệu trở đi. Người dùng phải có đặc quyền hệ thống CREATE TABLE để tạo bảng trong lược đồ của chính nó.Nhưng để tạo một bảng trong lược đồ của bất kỳ người dùng nào, người dùng phải có TẠO BẤT KỲ lược đồ BẢNG nào.

Đây là cú pháp của câu lệnh CREATE TABLE cơ bản. Có thể có nhiều mệnh đề bổ sung để cung cấp rõ ràng các thông số kỹ thuật lưu trữ hoặc giá trị phân đoạn.

CREATE TABLE [schema.]table 
          ( { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} 
         [, { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} ]...) 
         [AS subquery]

Trong cú pháp trên, DEFAULT chỉ định giá trị mặc định có thể được sử dụng trong câu lệnh INSERT nếu cột bị bỏ qua. Nó không thể chứa các tham chiếu đến các cột bảng hoặc cột giả khác (CURRVAL, NEXTVAL, LEVEL và ROWNUM) ngoại trừ SYSDATE và USER, hoặc các hằng số ngày không được chỉ định đầy đủ.

Ràng buộc là các quy tắc được xác định tùy chọn ở cấp cột hoặc cấp bảng (sẽ đề cập ở phần sau của chương này). Các quy tắc này được kiểm tra trong bất kỳ hành động dữ liệu nào (Chèn, cập nhật) trên bảng và nêu ra lỗi để hủy bỏ hành động khi vi phạm.

Ví dụ: câu lệnh CREATE TABLE bên dưới tạo một bảng EMP_TEST. Lưu ý các thông số kỹ thuật của cột, kiểu dữ liệu và độ chính xác.

CREATE TABLE SCOTT.EMP_TEST
(EMPID NUMBER,
ENAME VARCHAR2(100),
DEPARTMENT_ID NUMBER,
SALARY NUMBER,
JOB_ID VARCHAR2(3),
HIREDATE DATE,
COMM NUMBER);

Người dùng có thể tham chiếu các bảng từ lược đồ của người dùng khác bằng cách thêm tên người dùng hoặc lược đồ vào trước tên bảng. Ví dụ: người dùng GUEST muốn truy vấn tên nhân viên và mức lương từ bảng EMP_TEST thuộc sở hữu của SCOTT. Anh ta có thể đưa ra truy vấn dưới đây -

SELECT  ENAME, SALARY,
FROM 	GUEST.EMP_TEST;

Một cột có thể giữ một giá trị mặc định trong thời gian tạo bảng, nó giúp hạn chế các giá trị NULL đi vào cột. Giá trị mặc định có thể được suy ra từ một chữ, biểu thức hoặc hàm SQL phải trả về kiểu dữ liệu tương thích cho cột. Trong câu lệnh CREATE TABLE bên dưới, lưu ý rằng cột LOCATION_ID có giá trị mặc định là 100.

CREATE TABLE SCOTT.DEPARTMENT
(DEPARTMENT_ID NUMBER,
   DNAME VARCHAR2 (100),
   LOCATION_ID NUMBER DEFAULT 100);

CTAS - Tạo bảng bằng truy vấn con

Một bảng có thể được tạo từ một bảng hiện có trong cơ sở dữ liệu bằng cách sử dụng tùy chọn truy vấn con. Nó sao chép cấu trúc bảng cũng như dữ liệu từ bảng. Dữ liệu cũng có thể được sao chép dựa trên các điều kiện. Các định nghĩa kiểu dữ liệu cột bao gồm các ràng buộc NOT NULL được áp đặt rõ ràng được sao chép vào bảng mới.

Tập lệnh CTAS bên dưới tạo một bảng mới EMP_BACKUP. Dữ liệu nhân viên của bộ phận 20 được sao chép vào bảng mới.

CREATE TABLE EMP_BACKUP
AS
SELECT * FROM EMP_TEST
WHERE department_id=20;

Loại dữ liệu

Các kiểu dữ liệu được sử dụng để chỉ định hành vi cơ bản của một cột trong bảng. Trên cơ sở rộng hơn, hành vi của cột có thể thuộc về số, ký tự hoặc họ ngày tháng. Có nhiều kiểu con khác thuộc các họ này.

Kiểu dữ liệu số

Kiểu dữ liệu NUMBER bao gồm cả giá trị số nguyên, dấu phẩy động và dấu phẩy động. Các phiên bản ban đầu của Oracle đã xác định các kiểu dữ liệu khác nhau cho từng loại số khác nhau này, nhưng giờ đây, kiểu dữ liệu NUMBER phục vụ tất cả các mục đích này. Chọn kiểu dữ liệu NUMBER khi một cột phải lưu trữ dữ liệu số có thể được sử dụng trong các phép tính toán học. Thông thường, kiểu dữ liệu NUMBER được sử dụng để lưu trữ các số nhận dạng trong đó các số đó được DBMS tạo ra dưới dạng số tuần tự.

NUMBER (p, s), trong đó p là độ chính xác lên đến 38 chữ số và s là tỷ lệ (số chữ số ở bên phải dấu thập phân). Tỷ lệ có thể nằm trong khoảng từ -84 đến 127.

NUMBER (p), là một số điểm cố định với thang điểm bằng 0 và độ chính xác là p.

FLOAT [(p)], trong đó p là độ chính xác nhị phân có thể nằm trong khoảng từ 1 đến 126. Nếu p không được chỉ định thì giá trị mặc định là nhị phân 126.

Kiểu dữ liệu ngày tháng

Đối với mỗi kiểu dữ liệu DATE, Thế kỷ, Năm, Tháng, Ngày, Giờ, Phút, Giây được lưu trữ trong cơ sở dữ liệu. Mọi hệ thống cơ sở dữ liệu đều có một định dạng ngày mặc định được xác định bởi tham số khởi tạo NLS_DATE_FORMAT. Thông số này thường được đặt thành DD-MON-YY, nếu bạn không chỉ định thời gian, thời gian mặc định là 12:00:00 sáng

Kiểu dữ liệu ký tự

Oracle hỗ trợ ba kiểu dữ liệu ký tự được xác định trước bao gồm CHAR, VARCHAR, VARCHAR2 và LONG.VARCHAR và VARCHAR2 thực sự đồng nghĩa và Oracle khuyến nghị sử dụng VARCHAR2 thay vì VARCHAR. Sử dụng kiểu dữ liệu CHAR khi cột sẽ lưu trữ các giá trị ký tự có độ dài cố định. ví dụ: số An sinh xã hội (SSN) ở Hoa Kỳ được chỉ định cho mọi công dân và luôn có kích thước 9 ký tự (mặc dù SSN bao gồm đúng các chữ số, các chữ số được coi là ký tự) và sẽ được chỉ định là CHAR (9). Sử dụng kiểu dữ liệu VARCHAR2 để lưu trữ dữ liệu chữ và số có độ dài thay đổi. Ví dụ: tên hoặc địa chỉ khách hàng sẽ thay đổi đáng kể về số lượng ký tự được lưu trữ. Kích thước tối đa của cột VARCHAR2 là 4.000 ký tự.

Kiểu dữ liệu LOB

Oracle cung cấp một số kiểu dữ liệu LOB khác nhau, bao gồm CLOB (đối tượng lớn ký tự) và BLOB (đối tượng lớn nhị phân). Các cột của các kiểu dữ liệu này có thể lưu trữ dữ liệu phi cấu trúc bao gồm văn bản, hình ảnh, video và dữ liệu không gian. Kiểu dữ liệu CLOB có thể lưu trữ lên đến tám terabyte dữ liệu ký tự bằng cách sử dụng bộ ký tự cơ sở dữ liệu CHAR. Loại dữ liệu BLOB được sử dụng để lưu trữ các đối tượng lớn nhị phân không có cấu trúc, chẳng hạn như các đối tượng được liên kết với dữ liệu hình ảnh và video trong đó dữ liệu chỉ đơn giản là một dòng giá trị "bit". Loại dữ liệu BLOB có thể lưu trữ tối đa tám terabyte dữ liệu nhị phân. Kiểu dữ liệu NCLOB có thể lưu trữ các đối tượng lớn ký tự ở dạng ký tự quốc gia nhiều byte được thiết lập lên đến 8TB đến 128TB. Giá trị kiểu dữ liệu BFILE hoạt động như một bộ định vị tệp hoặc con trỏ đến tệp trên hệ thống tệp của máy chủ. Kích thước tệp tối đa được hỗ trợ là 8TB đến 128TB.

Ràng buộc

Ràng buộc là tập hợp các quy tắc được xác định trong bảng Oracle để đảm bảo tính toàn vẹn của dữ liệu. Các quy tắc này được thực thi cho từng cột hoặc tập hợp các cột. Bất cứ khi nào bảng tham gia vào hành động dữ liệu, các quy tắc này sẽ được xác thực và đưa ra ngoại lệ khi vi phạm. Các loại ràng buộc có sẵn KHÔNG phải là NULL, Khóa chính, Duy nhất, Kiểm tra và Khóa ngoại.

Cú pháp dưới đây có thể được sử dụng để áp đặt ràng buộc ở cấp cột.

Cú pháp:

column [data type] [CONSTRAINT constraint_name] constraint_type

Tất cả các ràng buộc ngoại trừ NOT NULL, cũng có thể được định nghĩa ở cấp bảng. Các ràng buộc tổng hợp chỉ có thể được chỉ định ở cấp bảng.

Ràng buộc KHÔNG ĐẦY ĐỦ

Ràng buộc NOT NULL có nghĩa là một hàng dữ liệu phải có giá trị cho cột được chỉ định là NOT NULL. Nếu một cột được chỉ định là NOT NULL, Oracle RDBMS sẽ không cho phép các hàng được lưu trữ vào bảng nhân viên vi phạm ràng buộc này. chỉ có thể được xác định ở cấp cột chứ không phải ở cấp bảng.

Cú pháp:

COLUMN [data type] [NOT NULL]

Hạn chế duy nhất

Đôi khi cần phải thực thi tính duy nhất cho giá trị cột không phải là cột khóa chính. Ràng buộc UNIQUE có thể được sử dụng để thực thi quy tắc này và Oracle sẽ từ chối bất kỳ hàng nào vi phạm ràng buộc duy nhất. Ràng buộc Unique đảm bảo rằng các giá trị cột là khác biệt , không có bất kỳ bản sao.

Cú pháp:

Column Level:

COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]

Table Level: CONSTRAINT [tên ràng buộc] UNIQUE (tên cột)

Lưu ý: Nội bộ Oracle tạo chỉ mục duy nhất để ngăn chặn sự trùng lặp trong các giá trị cột. Chỉ mục sẽ được thảo luận sau trong PL / SQL.

CREATE TABLE TEST
( ... ,
  NAME VARCHAR2(20) 
          CONSTRAINT TEST_NAME_UK UNIQUE,
  ... );

Trong trường hợp khóa duy nhất tổng hợp, nó phải được xác định ở cấp bảng như dưới đây.

CREATE TABLE TEST
( ... ,
  NAME VARCHAR2(20),
  STD VARCHAR2(20) ,
      CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD)
 );

Khóa chính

Mỗi bảng thường phải chứa một cột hoặc tập hợp các cột xác định duy nhất các hàng dữ liệu được lưu trữ trong bảng. Cột hoặc tập hợp cột này được gọi là khóa chính. Hầu hết các bảng đều có một cột duy nhất làm khóa chính. các cột chính bị hạn chế đối với NULL và các giá trị trùng lặp.

Những điểm cần lưu ý -

Một bảng chỉ có thể có một khóa chính.
Nhiều cột có thể được xếp thành câu lạc bộ dưới một khóa chính tổng hợp.
Nội bộ Oracle tạo chỉ mục duy nhất để ngăn chặn sự trùng lặp trong các giá trị cột. Các chỉ mục sẽ được thảo luận sau trong PL / SQL.

Cú pháp:

Column level:

COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]

Table level:

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

Ví dụ sau đây cho thấy cách sử dụng ràng buộc CHÍNH CHÍNH ở cấp cột.

CREATE TABLE TEST
( ID  NUMBER CONSTRAINT TEST_PK PRIMARY KEY,
  ...  );

Ví dụ sau đây cho thấy cách xác định khóa chính tổng hợp bằng cách sử dụng ràng buộc PRIMARY KEY ở cấp bảng.

CREATE TABLE TEST
 ( ...,
   CONSTRAINT TEST_PK PRIMARY KEY (ID) 
 );

Khóa ngoại

Khi hai bảng chia sẻ mối quan hệ cha mẹ dựa trên cột cụ thể, cột tham gia trong bảng con được gọi là Khóa ngoại. Thuộc tính này của cột tương ứng trong bảng mẹ được gọi là Tính toàn vẹn tham chiếu. Giá trị cột bổ sung khóa trong bảng con có thể hoặc là null hoặc phải là các giá trị hiện có của bảng mẹ. Xin lưu ý rằng chỉ các cột khóa chính của bảng được tham chiếu mới đủ điều kiện để thực thi tính toàn vẹn của tham chiếu.

Nếu một khóa ngoại được xác định trên cột trong bảng con thì Oracle không cho phép xóa hàng mẹ, nếu nó chứa bất kỳ hàng con nào. Tuy nhiên, nếu tùy chọn ON DELETE CASCADE được đưa ra tại thời điểm xác định khóa ngoại, Oracle sẽ xóa tất cả các hàng con trong khi hàng mẹ đang bị xóa. Tương tự, ON DELETE SET NULL chỉ ra rằng khi một hàng trong bảng mẹ bị xóa, các giá trị khóa ngoại được đặt thành null.

Cú pháp:

Column Level:

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]

Table level:

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

Ví dụ sau đây cho thấy cách sử dụng ràng buộc NGOẠI KHÓA ở cấp cột.

CREATE TABLE TEST
(ccode varchar2(5) 
     CONSTRAINT TEST_FK REFERENCES PARENT_TEST(ccode),
   ...
);

Usage of ON DELETE CASCADE clause

CREATE TABLE TEST
(ccode varchar2(5) 
   CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode)
   ON DELETE CASCADE,
   ...
);

Kiểm tra ràng buộc

Đôi khi các giá trị dữ liệu được lưu trữ trong một cột cụ thể phải nằm trong một số phạm vi giá trị có thể chấp nhận được. Ràng buộc CHECK yêu cầu điều kiện kiểm tra được chỉ định là đúng hoặc không xác định đối với mỗi hàng được lưu trữ trong bảng. Ràng buộc kiểm tra cho phép áp đặt quy tắc có điều kiện cho một , phải được xác thực trước khi dữ liệu được chèn vào cột. Điều kiện không được chứa truy vấn phụ hoặc cột giả CURRVAL NEXTVAL, LEVEL, ROWNUM hoặc SYSDATE.

Oracle cho phép một cột có nhiều hơn một ràng buộc KIỂM TRA. Trên thực tế, không có giới hạn thực tế nào đối với số lượng ràng buộc KIỂM TRA có thể được xác định cho một cột.

Cú pháp:

Column level:

COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]

Table level:

CONSTRAINT [name] CHECK (condition)

Ví dụ sau đây cho thấy cách sử dụng ràng buộc CHECK ở cấp cột.

CREATE TABLE TEST
( ...,
   GRADE char (1) CONSTRAINT TEST_CHK
   CHECK (upper (GRADE) in ('A','B','C')),
   ...
);

Ví dụ sau đây cho thấy cách sử dụng ràng buộc CHECK ở cấp bảng.

CREATE TABLE TEST
( ...,
   CONSTRAINT TEST_CHK
   CHECK (stdate < = enddate),
);

Câu lệnh ALTER TABLE

Một DBA có thể thực hiện các thay đổi đối với cấu trúc bảng hoặc định nghĩa cột sau khi bảng đã được tạo trong cơ sở dữ liệu. Lệnh DDL ALTER TABLE được sử dụng để thực hiện các hành động đó. Lệnh ALTER TABLE cung cấp nhiều tiện ích dành riêng cho các đối tượng lược đồ. để thêm, bớt, đổi tên và sửa đổi một cột trong bảng.

Câu lệnh ALTER TABLE bên dưới đổi tên bảng EMP thành EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

Câu lệnh ALTER TABLE bên dưới thêm cột mới TESTCOL vào bảng EMP_NEW

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

Câu lệnh ALTER TABLE bên dưới đổi tên cột TESTCOL thành TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

Câu lệnh ALTER TABLE bên dưới loại bỏ cột TESTNEW khỏi bảng EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

Câu lệnh ALTER TABLE bên dưới thêm khóa chính trên cột EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

Câu lệnh ALTER TABLE dưới đây loại bỏ khóa chính.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

Câu lệnh ALTER TABLE bên dưới chuyển chế độ bảng thành chỉ đọc.

ALTER TABLE EMP_NEW READ ONLY;

Chỉ đọc bảng

Bảng chỉ đọc là một cải tiến trong Oracle 11g, cho phép các bảng được sử dụng cho mục đích chỉ đọc. Trong các phiên bản oracle trước đó, các bảng chỉ được đọc bằng cách cấp đặc quyền SELECT cho người dùng khác, nhưng chủ sở hữu vẫn có đặc quyền đọc ghi. Tuy nhiên, hiện tại, nếu một bảng được đặt là Chỉ đọc, ngay cả chủ sở hữu cũng không có quyền truy cập vào thao tác dữ liệu .

Cú pháp:

ALTER TALE [TABLE NAME] READ ONLY

ALTER TALE [TABLE NAME] READ WRITE

Hình minh họa

SQL>CREATE TABLE ORATEST (id NUMBER)

SQL>INSERT INTO ORATEST VALUES (1);

SQL>ALTER TABLE ORATEST READ ONLY;

SQL> INSERT INTO ORATEST VALUES (2);
INSERT INTO ORATEST VALUES (2)
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> UPDATE ORATEST SET id = 2;
UPDATE ORATEST SET id = 2
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> DELETE FROM ORATEST;
DELETE FROM ORATEST
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> TRUNCATE TABLE ORATEST;
TRUNCATE TABLE ORATEST
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> ALTER TABLE ORATEST ADD (description VARCHAR2 (50));
ALTER TABLE ORATEST ADD (description VARCHAR2 (50))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> ALTER TABLE ORATEST READ WRITE;

Table altered.

SQL> DELETE FROM ORATEST;

1 row deleted.

Câu lệnh DROP TABLE

Câu lệnh DROP TABLE được sử dụng để xóa một bảng khỏi cơ sở dữ liệu. Bảng bị loại bỏ và dữ liệu của nó không còn có sẵn để lựa chọn. Bảng đã bị cắt có thể được khôi phục bằng tiện ích FLASHBACK, nếu có trong thùng rác. Việc cắt bảng sẽ làm giảm chỉ mục và các kích hoạt liên quan đến nó.