Hướng dẫn SQL & XQuery cho IBM DB2, Phần 6: Ngôn ngữ định nghĩa dữ liệu và ngôn ngữ kiểm soát dữ liệu

Tổng quan về các thành phần DML, DDL, và DCL của SQL

Hướng dẫn này, Phần 6 của loạt bài hướng dẫn SQL & XQuery cho IBM DB2 , phát triển theo ngôn ngữ xử lý dữ liệu (DML) và giới thiệu ngôn ngữ định nghĩa dữ liệu (DDL) và ngôn ngữ kiểm soát dữ liệu (DCL). Nó cũng bàn luận đến các lệnh SQL khác lệnh SELECT.

Pat Moffatt, Giám đốc Chương trình quản lý thông tin, Sáng kiến học đường, IBM

Pat Moffatt là giám đốc Chương trình quản lý thông tin thuộc Sáng kiến học đường IBM. Thông qua chương trình sáng kiến học đường này, bà muốn làm ra sẵn những nguồn tài nguyên thích hợp phục vụ giảng dạy về quản lý thông tin để giúp các giảng viên đại học tích hợp chủ đề phần mềm quản lý thông tin vào trong chương trình đào tạo của họ



Bruce Creighton, Chuyên viên lập kế hoạch các phần kỹ năng, IBM

Bruce Creighton là chuyên viên lập kế hoạch các phần kỹ năng trong khoa Phát triển và Lập kế hoạch đào tạo Quản lý thông tin. Với vai trò này, ông lập kế hoạch đầu tư về nội dung đào tạo và cân bằng đầu tư giữa các lĩnh vực mà IBM có thu lợi nhuận với các lĩnh vực mà tại đó yêu cầu về phát triển các kỹ năng là quan trọng, cần cung cấp đào tạo miễn phí



Jessica Cao, Chuyên viên phát triển các công cụ đào tạo, IBM

Jessica Cao là một sinh viên ngành Nghệ thuật - Khoa học và ngành Khoa học máy tính tại Trường đại học McMaster. Chị sẽ tốt nghiệp văn bằng kép hạng danh dự vào tháng Tư 2009. Jessica đang làm việc trong chương trình hỗ trợ và lập kế hoạch kênh kỹ năng quản lý thông tin DB2 của phòng thí nghiệm IBM Toronto, nhằm chuyên chú sâu hơn vào lập trình, biên tập và kỹ năng viết



20 05 2009

Trước khi bạn bắt đầu

Về loạt bài này

Loạt bài hướng dẫn này dạy các chủ đề SQL từ cơ bản đến nâng cao và các chủ đề XQuery cơ bản. Nó cũng chỉ ra cách diễn đạt các câu hỏi nghiệp vụ thường gặp bằng các truy vấn cơ sở dữ liệu sử dụng các câu truy vấn SQL hay các câu truy vấn XQuery. Những người phát triển ứng dụng và những người quản trị cơ sở dữ liệu có thể sử dụng hướng dẫn này để nâng cao các kỹ năng truy vấn cơ sở dữ liệu của mình. Các thành viên tham gia sáng kiến học đường IBM có thể sử dụng loạt hướng dẫn này như một phần trong chương trình giảng dạy cơ sở dữ liệu của họ.

Loạt bài hướng dẫn chỉ ra cách diễn đạt các câu hỏi kinh doanh thường gặp như các truy vấn cơ sở dữ liệu khi sử dụng SQL hay các XQuery.

Tất cả các ví dụ trong bài này là dựa trên Aroma, một cơ sở dữ liệu mẫu chứa các dữ liệu doanh thu về các sản phẩm cà phê và chè đã bán trong các cửa hàng trên khắp nước Mỹ. Mỗi ví dụ gồm có ba phần:

  • Một câu hỏi kinh doanh dưới dạng ngôn ngữ thường ngày
  • Một hoặc nhiều ví dụ truy vấn, được biểu diễn bằng SQL hay XQuery
  • Một bảng các kết quả trả về từ cơ sở dữ liệu

Hướng dẫn này được thiết kế để cho phép các học viên học tập ngôn ngữ SQL và XQuery. Cũng như học bất cứ cái gì khác, việc bổ sung thêm các bài tập thực hành là rất quan trọng. Dữ liệu và các định nghĩa bảng sẽ tạo điều kiện thuận lợi cho các bài tập thực hành này.

Đối với các sinh viên sử dụng tài liệu này như một phần của khoá học, hãy nhận từ thầy hướng dẫn của mình các chỉ dẫn để kết nối tới cơ sở dữ liệu Aroma và tìm hiểu mọi sự khác biệt giữa hướng dẫn và cài đặt tại máy cục bộ của mình.

Cảm ơn bạn lựa chọn IBM® DB2® để nghiên cứu về các máy chủ dữ liệu lai, phục vụ dữ liệu với cả hai cấu trúc: cấu trúc quan hệ thuần khiết lẫn cấu trúc pureXML.

Hướng dẫn này được viết cho Express-C 9 DB2 trên nền Linux®, UNIX®, và Windows®.

Về hướng dẫn này

Tất cả các phần trước của loạt bài này đã tập trung vào lệnh SELECT. Hướng dẫn này giới thiệu các thành phần và các lệnh khác SQL, bao gồm:

  • CREATE TABLE
  • INSERT
  • UPDATE
  • DELETE
  • ALTER TABLE
  • CREATE VIEW
  • CREATE INDEX
  • DROP TABLE
  • GRANT
  • REVOKE

Kết nối tới một cơ sở dữ liệu

Bạn cần kết nối tới một cơ sở dữ liệu trước khi có thể sử dụng các câu lệnh SQL để truy vấn hay xử lý dữ liệu. Lệnh CONNECT kết hợp một kết nối cơ sở dữ liệu với một tên người sử dụng.

Đối với loạt bài này, tên cơ sở dữ liệu là aromadb. (Nếu bạn là sinh viên khi sử dụng phần này như một phần của khoá học, hãy thông qua thầy hướng dẫn để tìm ra tên cơ sở dữ liệu bạn cần nối tới.)

Để kết nối tới cơ sở dữ liệu aromadb, gõ lệnh sau đây vào trong bộ xử lý dòng lệnh DB2:

CONNECT TO aromadb USER userid USING password

Đối với sinh viên khi sử dụng phần này như một phần của khoá học, thay thế "userid" và "password" bằng số ID và mật khẩu của người sử dụng mà bạn đã nhận được từ thầy hướng dẫn. Nếu máy không hỏi userid và password thì chỉ cần gõ vào lệnh sau đây:

CONNECT TO aromadb

Thông điệp sau báo cho bạn biết rằng đã tạo kết nối thành công:

Database Connection Information
Database server      = DB2/NT 9.0.0
SQL authorization ID = USERID
Local database alias = AROMADB

Khi đã kết nối được, bạn có thể bắt đầu sử dụng cơ sở dữ liệu.


Các thành phần SQL

SQL gồm có ba thành phần: ngôn ngữ xử lý dữ liệu (DML), ngôn ngữ định nghĩa dữ liệu (DDL) và ngôn ngữ kiểm soát dữ liệu (DCL). DML gồm các lệnh SELECT, UPDATE, INSERT, và DELETE; DDL gồm các lệnh CREATE, ALTER, và DROP ; ngôn ngữ kiểm soát gồm có các lệnh GRANTREVOKE.

Thành phần SQLLệnhMô tả
DMLSELECTThường dùng để hiển thị các kết quả truy vấn.
UPDATEThường dùng để thay đổi các giá trị hiện có trong các bảng. UPDATE ảnh hưởng đến dữ liệu tại mức cột.
INSERTThường dùng để thêm các hàng mới vào các bảng. INSERT ảnh hưởng đến dữ liệu tại mức hàng.
DELETEThường dùng để xóa các hàng khỏi các bảng. DELETE ảnh hưởng đến dữ liệu tại mức hàng.
DDLCREATE TABLEThường dùng để tạo ra các bảng mới.
ALTER TABLEThường dùng để thêm, điều chỉnh hay bỏ các cột.
CREATE VIEWThường dùng để tạo ra các khung nhìn mới.
CREATE INDEXThường dùng để tạo ra chỉ số mới.
DROP TABLEThường dùng để xóa các bảng.
DCLGRANTThường dùng để cấp quyền cho những người sử dụng cơ sở dữ liệu.
REVOKEThường dùng để hủy bỏ quyền của những người sử dụng cơ sở dữ liệu.

Các lưu ý về cách dùng

Các hướng dẫn trước đây trong loạt bài này đã bàn về lệnh SELECT. Đó là lệnh được những người dùng cuối sử dụng nhiều nhất.

Một số lệnh, như CREATE, GRANT, và REVOKE, có thể cần phải có quyền quản trị để thực hiện lệnh. Với tư cách là một người sử dụng, bạn có thể hay không thể được phép chạy các lệnh nào đó.


Lệnh CREATE TABLE

Câu hỏi

Bộ phận tiếp thị đã bắt đầu giới thiệu một chương trình thành viên mới để làm tăng doanh thu. Các khách hàng có thể mua một "Gold Card" (Thẻ Vàng) và được hưởng chiết khấu, các quà tặng miễn phí và thông tin qua email về các hoạt động khuyến mại khác nhau.

Mỗi cửa hàng được cung cấp các thẻ ghi số trước.

Tạo ra một bảng mới trong cơ sở dữ liệu Aroma được gọi là Gold để lưu giữ thông tin về các khách hàng Gold Card. Nó cần phải có các cột để chứa các giá trị như một số nhận dạng (ID) thẻ duy nhất, khóa cửa hàng, họ, tên, email, và trạng thái.

Ví dụ truy vấn

CREATE TABLE aroma.gold
   (card_id SMALLINT NOT NULL PRIMARY KEY,
    storekey INTEGER NOT NULL,
    fname VARCHAR(20) NOT NULL,
    lname VARCHAR(20) NOT NULL,
    email VARCHAR(30),
    status VARCHAR(10) NOT NULL,
    FOREIGN KEY (storekey) REFERENCES aroma.store (storekey));

Kết quả

Bạn có thể thực hiện một lệnh SELECT để xem kết quả của lệnh CREATE TABLE .

SELECT * FROM aroma.gold;
Card_idStorekeyFnameLnameEmailStatus

Cú pháp của lệnh CREATE TABLE

CREATE TABLE table_name
   (column_name1 column_descrpition,
    column_name2 column_descrpition,
    column_name3 column_descrpition,
    ... ,
    [FOREIGN KEY (column_name) REFERENCES table_name2 (column_name2)]
   );

Về truy vấn

Truy vấn này sử dụng một lệnh CREATE TABLE để xây dựng một bảng có tên là Gold trong cơ sở dữ liệu Aroma. Lệnh CREATE phải bắt đầu với từ CREATE. Từ thứ hai nói cho DB2 biết sẽ tạo ra kiểu đối tượng nào (ở đây là bảng - table), còn từ thứ ba đặt tên đối tượng (Gold).

Phần nằm trong dấu ngoặc đơn là vùng mô tả cột để định nghĩa cột. Mọi cột phải được gán ba phần: tên cột, kiểu dữ liệu, và đặc tính NULL.

Mệnh đề PRIMARY KEY là tùy chọn. Nó bảo đảm chắc chắn rằng các giá trị trong cột card_id là duy nhất -- hai thẻ không thể có cùng số ID.

Mệnh đề FOREIGN KEY cũng là tùy chọn. Nó liên kết cột storekey của bảng Gold với cột storekey của bảng Store. Điều này bảo đảm chắc chắn rằng khóa cửa hàng (store key) tồn tại và hợp lệ và hai cột chứa các giá trị có cùng kiểu dữ liệu.

Để biết chi tiết hơn về lệnh CREATE TABLE , hãy tìm nó trong Trung tâm thông tin DB2 (xem Tài nguyên). Trung tâm được giới thiệu trong Phần 1 của loạt bài này.

Tên cột

Các bảng và các cột phải theo các quy ước đặt tên của DB2:

  • Ký tự đầu tiên của tên bảng phải là một ký tự chữ cái hay một trong các ký hiệu quốc gia. Chúng bao gồm các chữ cái A - Z, ký hiệu Đôla ($), ký hiệu (@), và ký hiệu số (#).
  • Các ký tự tiếp theo có thể chứa một tổ hợp các ký tự chữ cái, các chữ số 0-9, và dấu gạch chân (_). Trong hầu hết các trường hợp, dấu gạch chân được sử dụng như một dấu phân tách trực quan.
  • Chiều dài tên của bảng/ cột thay đổi tùy theo nền hệ thống. Xin vui lòng xem SQL Reference Guide (Hướng dẫn Tham khảo SQL) về các đặc thù riêng (xem Resources).

Kiểu dữ liệu

Một cột phải lưu trữ các giá trị của một kiểu dữ liệu cụ thể.

Numbers (Các số)Tất cả các số có một dấu và một độ chính xác. Độ chính xác là số các bit hay các chữ số, trừ dấu. Các số gồm có SMALLINT, INTEGER, BIGINT, REAL, DOUBLE và DECIMALS.
Character string (Chuỗi ký tự)Một chuỗi ký tự) Một chuỗi ký tự là một chuỗi các byte. Chiều dài của chuỗi là số lượng các byte trong chuỗi. Nếu chiều dài bằng không, giá trị này được gọi là chuỗi rỗng. Các chuỗi ký tự gồm có chuỗi ký tự có chiều dài cố định và chuỗi ký tự có chiều dài thay đổi (VARCHAR, LONG VARCHAR, và CLOB).
Graphic string (Chuỗi đồ họa)Một chuỗi đồ họa Một chuỗi đồ họa là một chuỗi của dữ liệu ký tự hai byte. Chuỗi đồ họa gồm có chuỗi đồ họa có chiều dài cố định và chuỗi đồ họa có chiều dài thay đổi.
(Chuỗi nhị phân)Một chuỗi nhị phân Một chuỗi nhị phân là một chuỗi các byte. Nó thường được dùng để chứa dữ liệu không truyền thống, như các hình ảnh. Đối tượng lớn nhị phân (BLOB) là một chuỗi nhị phân. Xem các đối tượng lớn (LOBs) để biết thêm thông tin.
Datetime values (Các giá trị ngày giờ)Các giá trị kiểu ngày giờ biểu diễn Date (ngày tháng), Time (thời giờ) và Timestamp (thời ấn) (là một chuỗi ký tự 14 ký số biểu diễn ngày tháng và thời giờ hợp lệ dưới dạng yyyyxxddhhmmss). Các giá trị kiểu ngày giờ có thể được sử dụng trong một số phép toán số học và phép toán chuỗi nhất định và tương thích với một số chuỗi ký tự nhất định; tuy nhiên, chúng không phải là chuỗi ký tự mà cũng không là các số. Các giá trị kiểu ngày giờ gồm có Date,Time, và Timestamp.
Null value (Giá trị null)Giá trị null là một giá trị đặc biệt, khác với tất cả các giá trị khác null. Nó có nghĩa là không có mặt bất kỳ một giá trị nào khác tại cột đó trên dòng đang xét. Giá trị null tồn tại với tất cả các kiểu dữ liệu.
XMLDB2 9 là máy chủ cơ sở dữ liệu lai đầu tiên trên thế giới cho phép quản lý dữ liệu theo mô hình quan hệ và mô hình pureXML. DB2 9 đã đưa ra cơ chế tối ưu để lưu tữ dữ liệu XML cùng với cơ chế quan hệ vốn có của nó.

Để biết các chi tiết về các kiểu dữ liệu, xem lại Phần1 của loạt bài này.

Đặc tính NULL

Tất cả Card_id, storekey, fname, và lname được định nghĩa như giá trị không null (NOT NULL) do chúng là các thông tin quan trọng mà mỗi hàng phải có. Đối với bảng này, một hàng không có card_id là vô nghĩa đối với ngôn ngữ định nghĩa dữ liệu và ngôn ngữ kiểm soát dữ liệu.

Nếu một đặc tính null bị bỏ qua, nó sẽ bằng NULL theo mặc định. Email được cho phép là null do không phải tất cả khách hàng đều có e-mails. Họ vẫn có thể được hưởng các chiết khấu và các quà tặng miễn phí.

Sau khi tất cả các cột đều được định nghĩa, các khóa chính và khoá ngoài được định nghĩa. Bạn không cần chỉ rõ khóa chính mà khóa ngoài tham chiếu đến bởi vì DB2 biết về khóa chính từ thông tin danh mục.

Trước khi chuyển sang phần tiếp theo, bạn cần vào Trung tâm thông tin DB2 (xemTài nguyên) và đọc toàn bộ tài liệu trực tuyến về lệnh CREATE TABLE. Điều này sẽ giúp bạn trở nên quen thuộc với định dạng của tài liệu trợ giúp và xem lại những thứ bạn vừa mới học được.


Lệnh INSERT

Câu hỏi

Một khách hàng vừa mới mua Thẻ Vàng (Gold Card) đầu tiên từ cửa hàng có khóa cửa hàng (storekey) là 1. Tên của cô ấy là Ada Alexander, và e-mail của cô ấy là coffee_lover@hotmail.com.

Với mục đích của bài tập này, giả thiết rằng cửa hàng này được gán các thẻ đánh số từ 1 tới 100.

Nhập bản ghi của cô ấy vào trong bảng Gold với trạng thái được đặt là Active (hoạt động).

Ví dụ truy vấn

INSERT INTO aroma.gold
VALUES(1, 1, 'Ada', 'Alexander', 'coffee_lover@hotmail.com', 
'Active');

Kết quả

SELECT * FROM aroma.gold;
Card_idStorekeyFnameLnameEmailStatus
11AdaAlexandercoffee_lover@hotmail.comActive

Cú pháp của lệnh INSERT

INSERT INTO table_name
	VALUES(value1, value2, value3, ...);

Về truy vấn

Ví dụ truy vấn này minh họa một lệnh INSERT để thêm một hàng mới vào bảng Gold. Chú ý rằng chuỗi các giá trị trong lệnh INSERT được xếp cùng tuần tự giống như tuần tự các cột trong bảng. Cũng lưu ý rằng có một giá trị cho từng cột trong bảng.

Nếu giá trị là kiểu ký tự, nó phải được đặt trong các dấu nháy đơn.

Để biết thêm thông tin về lệnh INSERT tìm kiếm trong Trung tâm thông tin DB2 (xem Tài nguyên).

Bạn cũng có thể chèn nhiều hàng vào trong một bảng, chỉ sử dụng một lệnh INSERT.

Câu hỏi

Có nhiều khách hàng mua các Thẻ Vàng từ cửa hàng có storekey 1. Tên của họ là Ben Bowman - mmmcoffee@yahoo.com, Cynthia Chen - iheartcoffee@gmail.com, David Doyle - coffee_maniac@msn.com, Emily Enright - cupAday@hotmail.com and Frank Freeman - coffee_addict@gmail.com. Hãy nhập các bản ghi của họ vào trong bảng Gold bằng chỉ một lệnh.

Ví dụ truy vấn

INSERT INTO aroma.gold
VALUES(2, 1, 'Ben', 'Bowman', 'mmmcoffee@yahoo.com', 'Active'),
      (3, 1, 'Cynthia', 'Chen', 'iheartcoffee@gmail.com', 'Active'),
      (4, 1, 'David', 'Doyle', 'coffee_maniac@msn.com', 'Active'),
      (5, 1, 'Emily', 'Enright', 'cupAday@hotmail.com', 'Active'),
      (6, 1, 'Frank', 'Freeman', 'coffee_addict@gmail.com', 'Active');

Kết quả

SELECT * FROM aroma.gold;
Card_idStorekeyFnameLnameEmailStatus
11AdaAlexandercoffee_lover@hotmail.comActive
21BenBowmanmmmcoffee@yahoo.comActive
31CynthiaCheniheartcoffee@gmail.comActive
41DavidDoylecoffee_maniac@msn.comActive
51EmilyEnrightcupAday@hotmail.comActive
61FrankFreemancoffee_addict@gmail.comActive

Lệnh UPDATE

Câu hỏi

Emily Enright mới kết hôn với Frank Freeman và thay đổi tên họ của cô ấy thành 'Freeman'. Hãy cập nhật bảng Gold để phản ánh sự thay đổi này.

Ví dụ truy vấn

UPDATE aroma.gold
SET lname = 'Freeman'
WHERE card_id = 5;

Kết quả

SELECT * FROM aroma.gold;
Card_idStorekeyFnameLnameEmailStatus
11AdaAlexandercoffee_lover@hotmail.comActive
21BenBowmanmmmcoffee@yahoo.comActive
31CynthiaCheniheartcoffee@gmail.comActive
41DavidDoylecoffee_maniac@msn.comActive
51EmilyFreemancupAday@hotmail.comActive
61FrankFreemancoffee_addict@gmail.comActive

Cú pháp của lệnh UPDATE

UPDATE table_name
SET column_name = new_value
[WHERE search_condition];
search_condition
Điều kiện này sẽ được đánh giá là đúng hay sai.

Mệnh đề SET chỉ rõ các cột được cập nhật và các giá trị mới của chúng.

Mệnh đề WHERE là quan trọng. Nếu nó bị bỏ sót, tất cả các hàng trong bảng sẽ được gán giá trị mới new_value tại vị trí cột column_name.

Về truy vấn

Trong ví dụ này, một mệnh đề WHERE được dùng để định hướng DB2 tới các hàng bạn muốn cập nhật. Đây là chỗ mà mã nhận biết duy nhất trở nên rất quan trọng. Bạn muốn chỉ UPDATE cho riêng một Emily Enright. Mặc dù bảng Gold hiện tại rất nhỏ bé và chỉ chứa một Emily Enright, nhưng nó có thể phát triển mạnh và chứa nhiều bản ghi có các tên và họ giống nhau. Vì vậy luôn luôn sử dụng các mã nhận biết duy nhất là một thói quen an toàn.

Để biết thêm thông tin về lệnh UPDATE tìm kiếm nó trong Trung tâm thông tin DB2 (xem Tài nguyên).


Lệnh DELETE

Câu hỏi

Frank Freeman đã huỷ bỏ tư cách thành viên của mình. Hãy xóa bản ghi của anh ấy khỏi cơ sở dữ liệu.

Ví dụ truy vấn

DELETE FROM aroma.gold
WHERE card_id = 6;

Kết quả

SELECT * FROM aroma.gold;
Card_idStorekeyFnameLnameEmailStatus
11AdaAlexandercoffee_lover@hotmail.comActive
21BenBowmanmmmcoffee@yahoo.comActive
31CynthiaCheniheartcoffee@gmail.comActive
41DavidDoylecoffee_maniac@msn.comActive
51EmilyFreemancupAday@hotmail.comActive

Cú pháp của lệnh DELETE

DELETE FROM table_name
[WHERE search_condition];
search_condition
Điều kiện này sẽ được đánh giá là đúng hay sai.

Mệnh đề WHERE là quan trọng. Nếu nó bị bỏ qua, tất cả các hàng trong bảng sẽ bị xóa.

Về truy vấn

Ở đây lệnh DELETE loại bỏ Frank Freeman ra khỏi bảng Gold. Xin nhắc lại, việc nêu ra một điều kiện chỉ rõ mã nhận diện duy nhất là rất quan trọng. Nếu điều kiện chỉ rõ là "WHERE lname = 'Freeman'" thì cả hai bản ghi của Frank and Emily Freeman sẽ bị xóa.

Để biết thêm thông tin về lệnh DELETE, tìm kiếm nó trong Trung tâm thông tin DB2 ( xem Tài nguyên).


Lệnh ALTER TABLE

Câu hỏi

Bộ phận tiếp thị có đề xuất ý tưởng khác. Những người trong số các khách hàng Thẻ Vàng giới thiệu được năm người trở thành thành viên Thẻ Vàng sẽ giành được một phiếu quà tặng miễn phí $20. Hãy thêm một cột có tiêu đề referrals vào bảng Gold.

Ví dụ truy vấn

ALTER TABLE aroma.gold
ADD referrals SMALLINT;

Kết quả

SELECT * FROM aroma.gold;
Card_idStorekeyFnameLnameEmailStatusReferrals
11AdaAlexandercoffee_lover@hotmail.comActive 
21BenBowmanmmmcoffee@yahoo.comActive 
31CynthiaCheniheartcoffee@gmail.comActive 
41DavidDoylecoffee_maniac@msn.comActive 
51EmilyFreemancupAday@hotmail.comActive 

Cú pháp của lệnh ALTER TABLE

ALTER TABLE table_name
ADD column_name column_definition;

Về truy vấn

Truy vấn thêm một cột mới có tên referrals, chứa các số nguyên dài nhất đến năm chữ số.

Tất cả các trường của cột mới chứa các giá trị NULL.

Câu hỏi

Cột card_id ban đầu được tạo ra có kiểu là SMALLINT, hỗ trợ các số nguyên lên đến năm chữ số. Ta đã nhận thấy rằng khi bảng Gold lớn lên, cột này có thể đạt tới các số id lớn hơn. Hãy thay đổi cột card_id để chứa các giá trị kiểu INTEGER.

Kết quả -- Trước ALTER

Trước khi bạn thay đổi kiểu dữ liệu của card_id, dùng lệnh SELECT bảng các cột hệ thống để xem loại dữ liệu hiện tại.

SELECT * FROM sysibm.syscolumns
WHERE tbname='GOLD'
AND tbcreator='AROMA';
NAMETBNAMETBCREATORREMARKSCOLTYPENULLS...
CARD_IDGOLDAROMA SMALLINTN...
EMAILGOLDAROMA VARCHARY...
FNAMEGOLDAROMA VARCHARN...
LNAMEGOLDAROMA VARCHARN...
REFERRALSGOLDAROMA SMALLINTY...
STATUSGOLDAROMA VARCHARN...
STOREKEYGOLDAROMA INTEGERN...

Ví dụ truy vấn

ALTER TABLE aroma.gold
ALTER card_id SET data type INTEGER;

Kết quả -- Sau ALTER

Bảng các cột hệ thống bây giờ cho thấy kiểu dữ liệu sau lệnh ALTER.

SELECT * FROM sysibm.syscolumns
WHERE tbname='GOLD'
AND tbcreator='AROMA';
NAMETBNAMETBCREATORREMARKSCOLTYPENULLS...
CARD_IDGOLDAROMA INTEGERN...
EMAILGOLDAROMA VARCHARY...
FNAMEGOLDAROMA VARCHARN...
LNAMEGOLDAROMA VARCHARN...
REFERRALSGOLDAROMA SMALLINTY...
STATUSGOLDAROMA VARCHARN...
STOREKEYGOLDAROMA INTEGERN...

Chú ý: Một số cột đã bị cắt đi cho vừa với trang.

Cú pháp của lệnh ALTER TABLE

ALTER TABLE table_name
ALTER column_name SET data type new_definition;

Về truy vấn

Truy vấn thay đổi kiểu dữ liệu card_id thành INTEGER, để hỗ trợ các số nguyên lên đến 10 chữ số.

Ngoài ra, chú ý rằng các giá trị NULL cho cột referrals mới được thêm vào là Y, với ý nghĩa là có thể có các giá trị null. Chúng ta không chỉ rõ đặc tính null cho cột khi tạo ra cột này.

Để biết chi tiết hơn về các bảng hệ thống, xem Phần 1 của loạt bài này: "Metadata."

Câu hỏi

Hệ thống referral không mang lại những kết quả hợp lý hay ý kiến ủng hộ của khách hàng. Công ty đã quyết định kết thúc chương trình. Hãy loại bỏ cột referrals.

Ví dụ truy vấn

ALTER TABLE aroma.gold
DROP COLUMN referrals;

Kết quả

SELECT * FROM aroma.gold;
Card_idStorekeyFnameLnameEmailStatus
11AdaAlexandercoffee_lover@hotmail.comActive
21BenBowmanmmmcoffee@yahoo.comActive
31CynthiaCheniheartcoffee@gmail.comActive
41DavidDoylecoffee_maniac@msn.comActive
51EmilyFreemancupAday@hotmail.comActive

Cú pháp của lệnh ALTER TABLE

ALTER TABLE table_name
DROP COLUMN colum_name;

Về truy vấn

Truy vấn loại bỏ cột có tên referrals.

Để biết thêm thông tin về lệnh ALTER TABLE, tìm kiếm nó trong Trung tâm thông tin DB2 ( xem Resources).


Lệnh CREATE VIEW

Câu hỏi

Để bảo vệ sự riêng tư của các khách hàng, những người sử dụng cơ sở dữ liệu phải không xem được các cột fname, lname và email. Hãy tạo ra một khung nhìn (view) của bảng Gold chỉ chứa các cột card_id, storekey, và trạng thái.

Ví dụ truy vấn

CREATE VIEW aroma.gold_public(card_id, storekey, status) AS
SELECT card_id, storekey, status
FROM aroma.gold;

Kết quả

SELECT * FROM aroma.gold_public;
Card_idStorekeyStatus
11Active
21Active
31Active
41Active
51Active

Cú pháp của lệnh CREATE VIEW

CREATE VIEW view_name(view_column_list) AS
SELECT table_column_list
FROM table_name;
view_column_list
Một danh sách các tên cột cho khung nhìn. Các tên cột áp dụng tương ứng một - một với table_column_list của lệnh SELECT. Nếu các tên cột không được chỉ rõ, vùng nhìn thấy sử dụng các tên giống như các cột của bảng kết quả của lệnh SELECT.
table_column_list
Một danh sách các cột để chọn từ bảng có tên là table_name.

Về truy vấn

Một khung nhìn công khai của bảng Gold được tạo ra để hiển thị các thông tin card_id, storekey, và trạng thái của các khách hàng.

Dòng đầu tiên của truy vấn đặt tên cho khung nhìn (gold_public) và định nghĩa các cột của nó (card_id, storekey, status). Tên gold_public phải là duy nhất bên trong lược đồ của nó. Tên khung nhìn xuất hiện như một tên bảng mặc dầu nó không chứa dữ liệu. Khung nhìn sẽ có ba cột gọi là card_id, storekey, và status, tương ứng với các cột card_id, storekey, và status từ bảng Gold.

Dòng thứ hai hàng là một lệnh SELECT để mô tả những giá trị nào được chọn từ bảng Gold. Nó có thể chứa các mệnh đề ALL, DISTINCT, FROM, WHERE, GROUP BY, và HAVING.

Để biết thêm thông tin về lệnh CREATE VIEW , tìm kiếm nó trong Trung tâm thông tin DB2 ( xem Tài nguyên).


Lệnh CREATE INDEX

Câu hỏi

Lệnh SELECT mất quá nhiều thời gian để tìm kiếm storekey từ Gold. Hãy tạo ra một chỉ mục trên store key để giải quyết vấn đề này.

Kế hoạch truy nhập- Trước

Sử dụng các chỉ mục có thể cải thiện việc thực hiện truy vấn trên các bảng dữ liệu. Bạn có thể so sánh việc thực hiện một truy vấn không có chỉ mục và có dùng chỉ mục khi xem xét kế hoạch truy nhập.

Nhập truy vấn sau đây vào trong bộ soạn thảo lệnh và nhấp chuột vào nút Execute and Access plan. Truy vấn sẽ được thực hiện và tạo ra một đồ thị biểu diễn kế hoạch truy nhập của truy vấn.

SELECT * from aroma.gold;

Nhấp chuột vào phiếu Access Plan bên trong bộ soạn thảo lệnh để xem kế hoạch truy nhập DB2. Chú ý rằng một việc quét bảng (TBSCAN) được thực hiện trên bảng aroma.gold .

Hình 1. Kế hoạch truy nhập trước khi đánh chỉ số
Kế hoạch truy nhập trước khi đánh chỉ số

Ví dụ truy vấn

CREATE INDEX aroma.gold_index ON aroma.gold (storekey);
RUNSTATS ON TABLE aroma.gold ON ALL COLUMNS;
COMMIT WORK;

Kết quả

Để nhận thấy sự cải tiến trong việc thực hiện truy vấn, nhập lệnh SELECT giống như vậy vào trong trình soạn thảo lệnh , và nhấp chuột vào nút Execute and Access plan.

Hình 2. Kế hoạch truy nhập sau khi đánh chỉ số
Kế hoạch truy nhập sau khi đánh chỉ số

Cú pháp của lệnh CREATE INDEX

CREATE INDEX index_name ON table (column_list);
RUNSTATS ON TABLE table ON ALL COLUMNS;
COMMIT WORK;
column_list
Một danh sách các cột để tạo chỉ số trên đó.

Hai dòng sau cùng bảo đảm chắc chắn rằng kết quả tạo chỉ mục được phản ánh trong các truy vấn tiếp theo.

Về truy vấn

Truy vấn đầu tiên tạo ra một chỉ mục trên nút storekey của bảng aroma.gold để cải tiến hiệu năng truy vấn, sau đó thực hiện phép toán trên bảng aroma.gold để bảo đảm rằng chỉ mục mới sẽ được DB2 xem xét.

Do kích thước bảng nhỏ, nên không thể nhận thấy được sự thay đổi hiệu năng truy vấn trong bài tập này. Mục đích đơn giản là trình diễn cách tạo ra một chỉ mục, để có thể cải thiện đáng kể hiệu năng truy vấn cho các bảng có số lượng dữ liệu rất lớn.

Để biết thêm thông tin về lệnh CREATE INDEX, tìm kiếm nó trong Trung tâm thông tin DB2 (xem Tài nguyên).


Lệnh DROP TABLE

Câu hỏi

Dự án Gold Card là một thành công to lớn. Bộ phận tiếp thị đã quyết định bắt đầu một phiên bản mới và có cải tiến gọi là Platinum Card (Thẻ Bạch kim). Sau một thời gian, dự án Gold Card được hủy bỏ dần từng bước. Hãy loại bỏ bảng Gold .

Ví dụ truy vấn

DROP TABLE aroma.gold;

Kết quả

SELECT * FROM sysibm.systables
WHERE creator='AROMA';
NAMECREATOR...
CLASSAROMA...
DEALAROMA...
LINE_ITEMSAROMA...
MARKETAROMA...
ORDERSAROMA...
PERIODAROMA...
PRODUCTAROMA...
PROMOTIONAROMA...
SALESAROMA...
STOREAROMA...
SUPPLIERAROMA...

Về truy vấn

Truy vấn loại bỏ bảng Gold khỏi cơ sở dữ liệu Aroma. Đây là một hành động không thể đảo ngược được. Tất cả dữ liệu được cất giữ trong bảng bây giờ sẽ bị mất.

Để biết chi tiết hơn về các bảng hệ thống, xem Phần 1 của loạt bài này: "Metadata"

Để biết thêm thông tin về lệnh DROP TABLE, tìm kiếm nó trong Trung tâm thông tin DB2 (xem Tài nguyên).


Ngôn ngữ kiểm soát dữ liệu

Thành phần thứ ba của SQL là ngôn ngữ kiểm soát dữ liệu (DCL).

Trong DCL có các lệnh quan trọng như GRANTREVOKE. Các lệnh này kiểm soát người sử dụng có những quyền gì với cơ sở dữ liệu, ví dụ như việc chọn các hàng, cập nhật các bảng, xóa dữ liệu, thay đổi các cấu trúc bảng, và vân vân.

Với các vai trò khác nhau, những người sử dụng thực hiện các nhiệm vụ khác nhau và cần phải được cấp các quyền khác nhau.

Lệnh GRANT

Câu hỏi

Một người quản trị cơ sở dữ liệu mới được thuê để quản lý các bảng Sales và cần được cấp các quyền SELECT, INSERT, UPDATE, và DELETE.

Ví dụ truy vấn

GRANT select, insert, update, delete ON aroma.sales TO user02;

Cú pháp của lệnh GRANT

GRANT privilege_list ON table_name TO user_name;
privilege_listMột danh sách các quyền được cấp cho người sử dụng. Các giá trị có thể gồm SELECT, INSERT, UPDATE, DELETE và ALL PRIVILEGES.

Về truy vấn

Lệnh GRANT được dùng để cấp các quyền cho nhưng người sử dụng cơ sở dữ liệu.

Truy vấn cấp cho người sử dụng có tên là user02 các quyền chọn, chèn, cập nhật và xóa bảng Sales trong cơ sở dữ liệu Aroma.

Lệnh REVOKE

Câu hỏi

Đã quyết định rằng ngay khi các bản ghi được vào trong bảng Sales, người quản trị cơ sở dữ liệu mới không thể thay đổi hay xóa các bản ghi này. Hãy hủy bỏ các quyền UPDATEDELETE của user02.

Ví dụ truy vấn

REVOKE update, delete ON aroma.sales FROM user02;

Cú pháp của lệnh REVOKE

REVOKE privilege_list ON table_name FROM user_name;
privilege_listMột danh sách các quyền của người sử dụng sẽ bị hủy bỏ. Các giá trị có thể sử dụng bao gồm SELECT, INSERT, UPDATE, DELETE, và ALL PRIVILEGES.

Về truy vấn

Lệnh REVOKE được dùng để hủy bỏ các quyền của người sử dụng cơ sở dữ liệu.

Truy vấn hủy bỏ các quyền UPDATEDELETE khỏi người sử dụng có tên user02 cho bảng Sales trong cơ sở dữ liệu Aroma.

Để biết thêm chi tiết về lệnh DCL, tìm kiếm lệnh này trong Trung tâm thông tin DB2 (xem Tài nguyên.)


Tóm tắt

Hướng dẫn này giúp bạn tạo ra một bảng mới và thực hành các lệnh trong ngôn ngữ xử lý dữ liệu (DML), ngôn ngữ định nghĩa dữ liệu (DDL), và ngôn ngữ kiểm soát.

Lệnh CREATE TABLE đã được dùng để tạo ra bảng Gold.

Lệnh INSERT đã được dùng để thêm hàng mới vào trong bảng Gold.

Lệnh UPDATE đã được dùng để sửa đổi các hàng trong bảng Gold.

Lệnh DELETE đã được sử dụng xóa các dòng khỏi bảng Gold.

Lệnh ALTER TABLE đã được sử dụng để thêm một cột, thay đổi kiểu dữ liệu của một cột và xóa một cột trong bảng Gold.

Lệnh CREATE VIEW đã được dùng để tạo ra một khung nhìn công khai của bảng Gold, ở đây có thể nhìn thấy các cột card_id, storekey, và status.

Lệnh CREATE INDEX đã được dùng để cải thiện việc thực hiện truy vấn của bảng Gold.

Lệnh DROP TABLE đã được dùng để loại bỏ bảng Gold.

Lệnh GRANT đã được dùng để cấp các quyền cho người sử dụng bảng Sales.

Lệnh REVOKE đã được dùng để hủy bỏ các quyền khỏi người sử dụng bảng Sales.


Tải về

Mô tảTênKích thước
Cơ sở dữ liệu AromaAroma_Data.zip1MB

Tài nguyên

Học tập

Lấy sản phẩm và công nghệ

  • Hiện nay bạn có thể sử dụng DB2 miễn phí. Tải xuống DB2 Express-C, một phiên bản DB2 Express miễn phí dành cho cộng đồng nhưng vẫn có đủ các đặc tính dữ liệu cốt lõi giống như phiên bản DB2 Express và cung cấp một cơ sở vững chắc để xây dựng và triển khai các ứng dụng.

Thảo luận

Bình luận

developerWorks: Đăng nhập

Các trường được đánh dấu hoa thị là bắt buộc (*).


Bạn cần một ID của IBM?
Bạn quên định danh?


Bạn quên mật khẩu?
Đổi mật khẩu

Bằng việc nhấn Gửi, bạn đã đồng ý với các điều khoản sử dụng developerWorks Điều khoản sử dụng.

 


Ở lần bạn đăng nhập đầu tiên vào trang developerWorks, một hồ sơ cá nhân của bạn được tạo ra. Thông tin trong bản hồ sơ này (tên bạn, nước/vùng lãnh thổ, và tên cơ quan) sẽ được trưng ra cho mọi người và sẽ đi cùng các nội dung mà bạn đăng, trừ khi bạn chọn việc ẩn tên cơ quan của bạn. Bạn có thể cập nhật tài khoản trên trang IBM bất cứ khi nào.

Thông tin gửi đi được đảm bảo an toàn.

Chọn tên hiển thị của bạn



Lần đầu tiên bạn đăng nhập vào trang developerWorks, một bản trích ngang được tạo ra cho bạn, bạn cần phải chọn một tên để hiển thị. Tên hiển thị của bạn sẽ đi kèm theo các nội dung mà bạn đăng tải trên developerWorks.

Tên hiển thị cần có từ 3 đến 30 ký tự. Tên xuất hiện của bạn phải là duy nhất trên trang Cộng đồng developerWorks và vì lí do an ninh nó không phải là địa chỉ email của bạn.

Các trường được đánh dấu hoa thị là bắt buộc (*).

(Tên hiển thị cần có từ 3 đến 30 ký tự)

Bằng việc nhấn Gửi, bạn đã đồng ý với các điều khoản sử dụng developerWorks Điều khoản sử dụng.

 


Thông tin gửi đi được đảm bảo an toàn.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=70
Zone=Information Management, SOA và dịch vụ Web
ArticleID=972927
ArticleTitle=Hướng dẫn SQL & XQuery cho IBM DB2, Phần 6: Ngôn ngữ định nghĩa dữ liệu và ngôn ngữ kiểm soát dữ liệu
publish-date=05202009