Ngôn ngữ truy vấn CSDL SQL (tiếp theo)

Bổ sung (các) bộ giá trị mới.

Có 2 cách bổ sung bộ giá trị mới cho bảng. Cách 1, bổ sung trực tiếp một bộ bởi một lệnh SQL và cách 2, bổ sung nhiều bộ giá trị lấy từ (các) bộ giá trị của các bảng của CSDL.

Tạo mới một bảng với các bộ giá trị lấy từ CSDL.

        Các câu truy vấn dữ liệu để tìm kiếm thông tin tạo ra một bảng trung gian với những mối liên hệ sao cho có thể xem và, nếu được phép, có thể sửa chữa dữ liệu hoặc xóa bỏ chúng. Các QUERY đó đã tạo ra những khung nhìn (VIEW). Trong nhiều trường hợp chúng ta muốn các bảng này trở thành những bảng vật lý, được lưu trữ lâu dài trong CSDL. Điều này có thể được thực hiện nhờ 1 lệnh truy vấn hành động (Action Query) gọi là truy vấn tạo bảng mới (Make Table Query).

   -Cú pháp:

SELECT <biểu thức 1>, <biểu thức 2> , ....

FROM <danh sách các bảng nguồn>

INTO TABLE <tên bảng>

[WHERE <điều kiện>]

[GROUP BY <danh sách cột phân nhóm>]

[ORDER BY <cột 1> [ASC | DESC], <cột 1> [, <cột 1> [ASC| DESC],... ]

[,... ]

[HAVING <điều kiện>];

F Ghi chú: Trong Oracle, câu lệnh này là CREATE TABLE có cú pháp như sau:

CREATE TABLE <tên bảng> AS

SELECT <biểu thức 1>, <biểu thức 2> , ....

FROM <danh sách các bảng nguồn> INTO TABLE <tên bảng>

[WHERE <điều kiện>]

[GROUP BY <danh sách cột phân nhóm>]

[ORDER BY <cột 1> [ASC | DESC], <cột 1> [, <cột 1> [ASC| DESC],... ]

[,... ]

[HAVING <điều kiện>];

   -Ví dụ 6.3.3.

    Tạo bảng mới tên là MANAGER bao gồm chỉ những nhân viên phụ trách các phòng ban.

SELECT * FROM EMPLOYEE INTO TABLE MANAGER

WHERE Job = ‘Mngr’ ORDER BY DeptNo;

    Kết quả ta có một bảng mới MANAGER với các bản ghi sau:

Sửa nội dung các bản ghi:

        Thông thường có thể sửa nội dung của (các) bản ghi bằng cách cho hiện nội dung của bảng (gọi là Browse hoặc Open bảng), di chuyển con trỏ (Cursor) đến bản ghi cần sửa và thực hiện việc sửa đổi giá trị của các field của bản ghi thể theo yêu cầu. Tuy nhiên cách làm này là phù hợp với các bảng của CSDL nhỏ, và số lượng bản ghi cần sửa cũng rất ít. Đối với các bảng lớn nhiều ngàn - thậm chí hàng chục ngàn như bảng CCVC trong CSDL quản lý công chức viên chức thành phố, hay trăm ngàn như bảng các đối tượng chính sách trong CSDL quản lý các đối tượng chính sách, có công Cách mạng; hoặc nhiều triệu bản ghi như bảng thông tin chi tiết về một cư dân trong CSDL quản lý dân số TP.Hồ Chí Minh v.v... thì việc sửa (cập nhật) thông tin biến động chỉ 1 bản ghi thôi cũng đã là khó khăn.

Ngôn ngữ quản trị CSDL cung cấp một lệnh cho phép sửa đổi nội dung các bản ghi trong CSDL một cách dễ dàng, chính xác và nhanh chóng.

   -Cú pháp lệnh như sau:

UPDATE <tên bảng>

SET <tên cột 1> = <biểu thức 1>,

<tên cột 2> = <biểu thức 2>, ...

<tên cột n> = <biểu thức n>

[WHERE <điều kiện>];

    -Ngữ nghĩa: Giá trị của các field có tên trong danh sách <tên cột 1>, <tên cột 2> ... của những bản ghi thoả mãn điều kiện sau WHERE sẽ được sửa đổi thành giá trị của các <biểu thức 1>, <biểu thức 2> ... tương ứng. Nếu không có mệnh đề điều kiện WHERE, thì tất cả các bản ghi của bảng sẽ được sửa đổi.

F Ghi chú: SQL/Plus của Oracle mở rộng cú pháp, cho phép liệt kê danh sách các tên cột của bảng cần sửa đổi giá trị trong cặp dấu ngoặc tròn sau từ khóa SET và danh sách các giá trị mới của chúng trong cặp dấu ngoặc tròn theo sau dấu bằng ( = ), đồng thời chấp nhận nhiều danh sách như thế trong câu lệnh UDATE. Cú pháp như sau:

UPDATE <tên bảng>

SET <d/sách tên cột 1> = <d/sách biểu thức 1>,

< d/sách tên cột 2> = < d/sách biểu thức 2>, ...

< d/sách tên cột n> = < d/sách biểu thức n>

[WHERE <điều kiện>];

    -Ví dụ 6.3.4:

    Tăng lương thêm 10% cho các nhân viên phụ trách các phòng ban.

UPDATE EMPLOYEE

SET Salary = Salary * 1.1

WHERE Job = ‘Mngr’;

    Kết quả là, lương mới của các nhân viên phụ trách các phòng ban được thể hiện như trong bảng dưới đây:

Xóa bỏ các bản ghi khỏi một bảng:

    Việc loại bỏ một bản ghi khỏi một bảng trong CSDL là một trong những thao tác cập nhật dữ kiệu được tiến hành một cách thường xuyên nhằm đản bảo phản ánh tình trạng mới nhất của CSDL.

    -Cú pháp:

DELETE FROM <tên bảng>

[WHERE <điều kiện>];

    -Ngữ nghĩa: Các bản ghi thoả mãn điều kiện sau WHERE sẽ bị xoá khỏi bảng. Nếu không có mệnh đề WHERE thì tất cả các bản ghi của bảng sẽ bị xóa khỏi bảng.

F Lưu ý: Nếu thao tác trên View, tức là một bảng trung gian của một câu lệnh truy vấn SQL, và người sử dụng được quyền cập nhật xóa dữ liệu, thì khi thực hiện lệnh này các bản ghi trong các bảng vật lý của CSDL cũng sẽ bị xóa.

    -Ví dụ 6.3.5:

    Xóa tất cả các nhân viên phụ trách các phòng ban khỏi bảng nhân viên EMPLOYEE. Câu lệnh SQL như sau:

DELETE FROM EMPLOYEE

WHERE Job = ‘Mngr’;

    Kết quả là, bảng EMPLOYEE chỉ còn các bản ghi sau:

Cách đặt tên đối tượng:

        SQL chuẩn hóa (86, 89, 92, 96) đều quy định cách đặt tên các đối tượng như tên bảng, tên cột của bảng, tên View, tên RBTV v.v... (gọi chung là định danh - Identifier) như sau:

        Tên gọi gồm tối đa 32 ký tự chữ cái Latinh, chữ số Arập và dấu gạch chân (Underscore) và phải bắt đầu bằng một chữ cái Latinh hoặc dấu gạch chân. Tuyệt đối không chứa khoảng trắng hay ký tự chữ cái không phải Latinh như tiếng Việt chẳng hạn. Chữ in hoa hay chữ thường đều được xem là như nhau. Tên bảng phải là duy nhất trong CSDL và tên bảng trung gian, và không trùng với bất cứ từ khóa nào trong ngôn ngữ quản trị CSDL.

        Tên cột của một bảng phải là khác nhau, nhưng chúng có thể giống nhau nếu chúng nằm trong các bảng khác nhau. Người ta đề nghị rằng những tên mang ngữ nghĩa giống nhau và mô tả cùng một thực thể thi nên đặt tên giống nhau. Chẳng hạn, tên cột số hiệu phòng ban nên đặt là DEPTNO trong cả hai bảng EMPLOYEE và DEPARTMENT, hay tên cột mã ngạch công chức viên chức (CCVC) nên đặt là MA_NGACH trong cả 3 bảng CCVC, danh mục ngạch CCVC: NGACH_LG và bảng ngạch - bậc - hệ số lương: NGACH_BAC_HESO.

   -Ví dụ 6.4.1:

EMP_85 : là tên hợp lệ

85EMPL : không hợp lệ, vì tự đầu tiên là chữ số.

BẢNG_NV : không hợp lệ vì có ký tự tiếng Việt

BANG-N/V : không hợp lệ vì có dấu trừ (-) và dấu chia (/)

DANH SACH : không hợp lệ vì có khoảng trắng

UPDATE : không hợp lệ vì là từ dành riêng của ngôn ngữ .

SQL Tạo bảng CSDL:

    Một trong những dạng đơn giản nhất của câu lệnh tạo bảng CSDL quan hệ có cú pháp như sau:

CREATE TABLE <tên bảng> (

<tên cột 1> <kiểu dữ liệu 1>(<kích thước 1>),

<tên cột 2> <kiểu dữ liệu 2>(<kích thước 2>),

...

<tên cột n> <kiểu dữ liệu n>(<kích thước n>)

);

    Kiểu dữ liệu có thể là:

-CHAR (w) : Kiểu ký tự với kích thước cố định. Chiều dài của giá trị dữ liệu luôn luôn là w ký tự. Nếu cột được gán giá trị là một chuỗi ký tự có chiều dài nhỏ hơn w thì các ký tự khoảng cách (space) sẽ được điền vào bên phải chuỗi (Right Padded) để cho đủ w ký tự. Kích thước tối thiểu là 1 và tối đa là 255 ký tự.

-VARCHAR(w) : Kiểu ký tự với kích thước thay đổi từ 0 đến w ký tự. Giá trị lớn nhất của w là 2000.

-NUMBER (w, s): Kiểu dữ liệu số có kích thước tối đa w ký tự (kể chứa dấu chấm thập phân), trong đó có s chữ số sau dấu chấm thể hiện phần số lẻ.

-DATE : Kiểu dữ liệu ngày tháng năm

-LOGICAL : Kiểu dữ liệu lôgic 1 byte có giá trị hoặc đúng (True), hoặc sai (False).

    -Ví dụ 6.4.2:

    Lệnh SQL để tạo bảng DEPARTMENT như đã nêu trong Chương V, mục 5.3, bài 7 như sau:

CREATE TABLE DEPARTMENT (

Deptno NUMBER (2),

Deptname CHAR (15),

Loc CHAR (15),

Mgr NUMBER (3),

Exp_budg NUMBER (7),

Rev_budg NUMBER (7)

);

        Trong bảng này chúng ta đã xác định DeptNo là thuộc tính khóa; một phòng ban phải có một tên gọi, địa điểm, và kinh phí hoạt động xác định, nghĩa là DeptName, Loc, Exp_Budg phải khác NULL; đồng thời chúng ta có thể muốn địa điểm mặc định của các phòng ban là ‘Houston’. Cách khai báo các RBTV kiểu này được hỗ trợ bởi các từ khóa PRIMARY KEY, DEFAULT, UNIQUE và NOT NULL như sau:

CREATE TABLE DEPARTMENT (

DeptNo NUMBER (2) PRIMARY KEY,

DeptName CHAR (15) NOT NULL,

Loc CHAR (15) NOT NULL DEFAULT ‘Houston’,

Mgr NUMBER (3),

Exp_Budg NUMBER (7) NOT NULL,

Rev_Budg NUMBER (7)

);

    -Ví dụ 6.4.3:

    Tạo cấu trúc bảng các chức danh (công việc) JOBS có thể có của công ty:

CREATE TABLE JOBS (

Job CHAR (5) CONSTRAINT PK PRIMARY KEY,

Jobname CHAR(15) NOT NULL UNIQUE,

Minsalary NUMBER(4),

Maxsalary NUMBER(4),

Mgrflag CHAR(1)

);

        Nhưng chỉ với cách khai báo như trên thì chúng ta vẫn còn quá nhiều việc phải làm trong việc định nghĩa các RBTV trên bảng và các bảng: RBTV về miền giá trị của một thuộc tính, RBTV định nghĩa trên nhiều thuộc tính của một bảng, RBTV phụ thuộc tồn tại, RBTV liên thuộc tính, liên quan hệ v.v... Ngôn ngữ quản trị CSDL cho phép khai báo các RBTV này trong khi tạo cấu trúc các bảng bởi mệnh đề CONSTRAINT mà chúng ta sẽ được làm quen trong mục 6.5 dưới đây.

RBTV về miền giá trị:

    -Cú pháp:

[CONSTRAINT <tên RBTV>] CHECK (<điều kiện>)

        Ràng buộc toàn vẹn về miền giá trị này có thể định nghĩa trên một cột của bảng, có thể được viết ngay sau tên thuộc tính mà không cần phải viết tên RBTV trong cụm CONSTRAINT <tên RBTV>. Nếu định nghĩa trên nhiều cột của bảng thì nên sử dụng cụm này và được viết sau khi đã khai báo xong các thuộc tính của bảng.

        <Điều kiện> là một biểu thức lôgic bất kỳ như đã trình bày trong phần câu lệnh truy vấn thông tin, tuy nhiên trong biểu thức không được chứa các câu hỏi con (SubQueries).

    -Ví dụ 6.5.4:

    Viết lại câu lệnh khai báo cấu trúc bảng DEPARTMENT với RBTV về miền giá trị cho cột DeptNo:

CREATE TABLE DEPARTMENT (

DeptNo NUMBER (2) PRIMARY KEY CHECK (DeptNo MOD 10 = 0),

DeptName CHAR (15) NOT NULL,

Loc CHAR (15) NOT NULL DEFAULT "Houston",

Mgr NUMBER (3),

Exp_Budg NUMBER (7) NOT NULL,

Rev_Budg NUMBER (7)

);

    Phép toán MOD dùng để lấy phần dư của một phép chia hai số nguyên. Mệnh đề CHECK ở trên cho phép chỉ nhận những bản ghi có giá trị ở cột DeptNo là bội của 10: 10, 20, 30, ... và 90. Câu lệnh trên có thể được viết cách khác tương đương như sau:

CREATE TABLE DEPARTMENT (

DeptNo NUMBER (2) PRIMARY KEY,

DeptName CHAR (15) NOT NULL,

Loc CHAR (15) NOT NULL DEFAULT ‘Houston’,

Mgr NUMBER (3),

Exp_Budg NUMBER (7) NOT NULL,

Rev_Budg NUMBER (7)

CONSTRAINT PK_VAL CHECK (DeptNo MOD 10 = 0)

);

RBTV về khóa ngoại hay phụ thuộc tồn tại:

    -Cú pháp: Định nghĩa cho bảng trên nhiều cột của bảng

,[CONSTRAINT <tên RBTV>]

FOREIGN KEY (<các thuộc tính khóa ngoại>)

REFERENCES <tên bảng> (<các cột khóa chính>)

    Và định nghĩa cho một cột khóa ngoại của bảng:

[CONSTRAINT <tên RBTV>]

REFERENCES <tên bảng> (<các cột khóa chính>)

F Lưu ý: trong cú pháp định nghĩa trên một cột của bảng không có các từ khóa FOREIGN KEY và không có dấu phảy ở trước CONSTRAINT. Các RBTV định nghĩa trên cột được viết ngay trong dòng mô tả cột.

    -Ví dụ 6.5.5:

    Định nghĩa cấu trúc các bảng EMPLOYEE và EMPLHIST:

CREATE TABLE EMPLOYEE (

EmpNo

EmpNo NUMBER(3) PRIMARY KEY,

Name CHAR (10) NOT NULL,

Job CHAR (5) REFERENCES JOBS (Job),

Salary NUMBER (5) NOT NULL,

Comm NUMBER (5),

DeptNo NUMBER (2) REFERENCES DEPARTMENT (DeptNo),

Sex CHAR (1) CHECK (Sex = ‘F’ OR Sex = ‘M’)

);

    Và:

CREATE TABLE EMPLHIST (

EmpNo

EmpNo NUMBER(3),

Seq NUMBER(3) NOT NULL,

Date_Beg DATE NOT NULL,

Date_End DATE,

Salary NUMBER (5) NOT NULL,

FrJob CHAR (5) REFERENCES JOBS (Job),

Tojob CHAR (5),

Promo LOGICAL NOT NULL,

FrDept NUMBER (2),

ToDept NUMBER (2) REFERENCES DEPARTMENT (DeptNo),

CONSTRAINT P_KEY PRIMARY KEY (EmpNo, Seq)

);

        Với RBTV về khóa ngoại còn có thể sử dụng tùy chọn (Option) ON DELETE CASCADE để thực hiện việc xóa một bộ của bảng chứa khóa chính thì sẽ xóa tất cả các bản ghi của bảng chứa khóa ngoại. Chẳng hạn, khi xóa đi một phòng ban khỏi bảng DEPARTMENT, thì tất cả các nhân viên thuộc phòng đó cũng sẽ bị xóa bỏ khỏi bảng EMPLOYEE. Mệnh đề xóa ON DELETE CASCADE mang tính "lan tỏa" này được đặt sau mệnh đề REFERENCES.

    -Ví dụ 6.5.6:

        Định nghĩa cấu trúc các bảng EMPLOYEE và EMPLHIST được viết lại với RBTV về khóa ngoại có sự xóa "lan tỏa" như sau:

CREATE TABLE EMPLOYEE (

EmpNo

EmpNo NUMBER(3) PRIMARY KEY,

Name CHAR (10) NOT NULL,

Job CHAR (5) REFERENCES JOBS (Job),

Salary NUMBER (5) NOT NULL,

Comm NUMBER (5),

DeptNo NUMBER (2),

Sex CHAR (1) CHECK (Sex = ‘F’ OR Sex = ‘M’),

CONSTRAINT DEPT_CASC REFERENCES DEPARTMENT (DeptNo)

ON DELETE CASCADE

);

    Và:

CREATE TABLE EMPLHIST (

EmpNo

EmpNo NUMBER(3),

Seq NUMBER(3) NOT NULL,

Date_Beg DATE NOT NULL,

Date_End DATE,

Salary NUMBER (5) NOT NULL,

FrJob CHAR (5) REFERENCES JOBS (Job),

ToJob CHAR (5),

Promo LOGICAL NOT NULL,

FrDept NUMBER (2),

ToDept NUMBER (2) REFERENCES DEPARTMENT (DeptNo),

CONSTRAINT PKEY PRIMARY KEY (EmpNo, Seq),

CONSTRAINT FKEY FOREIGN KEY (EmpNo)

REFERENCES EMPLOYEE (EmpNo, Seq)

ON DELETE CASCADE

);

Một số tùy chọn