›
›

Hệ quản trị cơ sở dữ liệu - Những hỗ trợ tiên tiến khác của SQL

pdf 57 trang vanle 2040
Download
Bạn đang xem 20 trang mẫu của tài liệu "Hệ quản trị cơ sở dữ liệu - Những hỗ trợ tiên tiến khác của SQL", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfhe_quan_tri_co_so_du_lieu_nhung_ho_tro_tien_tien_khac_cua_sq.pdf

Nội dung text: Hệ quản trị cơ sở dữ liệu - Những hỗ trợ tiên tiến khác của SQL

  1. Khoa Công Nghệ Thông Tin Trường Đại Học Cần Thơ Những hỗ trợ tiên tiến khác của SQL Đỗ Thanh Nghị dtnghi@cit.ctu.edu.vn Cần Thơ 24-04-2005
  2. Nội dung  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 2
  3.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 3
  4.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Định nghĩa kế thừa  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  CREATE TABLE  Hỗ trợ cho định nghĩa kế thừa (INHERITES)  Phép truy vấn cũng làm việc trên dữ liệu thừa kế  SELECT (ONLY) 4
  5.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 1  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 5
  6.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 2  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 6
  7.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 3  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 7
  8.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 4 với kế thừa  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 8
  9.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 5 với kế thừa  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 9
  10.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 6 với kế thừa  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 10
  11.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 11
  12.  Kế thừa Định nghĩa kiểu  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu  Định nghĩa trigger  Tạo View  Cú pháp:  Truy cập cạnh tranh CREATE TYPE typename ( INPUT = input_function, OUTPUT = output_function, INTERNALLENGTH = { internallength | VARIABLE } [, DEFAULT = default ] [, ELEMENT = element ] [, DELIMITER = delimiter ] [, PASSEDBYVALUE ] [, ALIGNMENT = alignment ] [, STORAGE = storage ]) CREATE TYPE typename AS (column_name data_type [, ]) 12
  13.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 7  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 13
  14.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 7 (cont.)  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 14
  15.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 15
  16.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Định nghĩa hàm  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Cú pháp: CREATE FUNCTION name ( [ ftype [, ] ] ) RETURNS rtype AS definition LANGUAGE 'langname ' [ WITH ( attribute [, ] ) ] CREATE FUNCTION name ( [ ftype [, ] ] ) RETURNS rtype AS obj_file , link_symbol LANGUAGE 'C' [ WITH ( attribute [, ] ) ] 16
  17.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 8  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 17
  18.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 9  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 18
  19.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 10  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 19
  20.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 11  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 20
  21.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 11(cont.)  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 21
  22.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 22
  23.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Định nghĩa ràng buộc dữ liệu  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  CREATE TABLE hỗ trợ cho định nghĩa những ràng buộc:  NOT NULL  UNIQUE  CHECK  PRIMARY KEY (UNIQUE và NOT NULL)  REFERENCES (Khóa ngoài) 23
  24.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 12  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh test=# CREATE TABLE cities ( test(# nc smallint PRIMARY KEY, test(# name varchar(30), test(# pop bigint NOT NULL, test(# nb_museums smallint DEFAULT 0); test=# CREATE TABLE hotels ( test(# nh integer PRIMARY KEY, test(# nc smallint REFERENCES cities, test(# name varchar(30), test(# category varchar(12) test(# CHECK (category IN ('modeste','confortable','luxe'))); 24
  25.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 13  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh test=# CREATE TABLE tourists ( test(# nt integer PRIMARY KEY, test(# name varchar(20), test(# last_name varchar(20), test(# age smallint CHECK (age > 0), test(# type varchar(14) test(# CHECK (type IN ('sportif','intellectuel','ecologiste'))); test=# CREATE TABLE museums ( test(# nm integer, test(# nc smallint REFERENCES cities, test(# name varchar(30) NOT NULL, test(# PRIMARY KEY (nm,nc)); 25
  26.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 14  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh test=# CREATE TABLE bookings ( test(# nt integer REFERENCES tourists, test(# nh integer REFERENCES hotels, test(# begin_date date NOT NULL, test(# nb_days smallint NOT NULL, test(# PRIMARY KEY (nt,nh,begin_date)); test=# CREATE TABLE ut ( test(# id integer PRIMARY KEY, test(# acc_no integer UNIQUE); 26
  27.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 15  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 27
  28.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 16  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 28
  29.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm Kiểm tra ràng buộc:  Định nghĩa ràng buộc dữ liệu  Định nghĩa trigger primary key, not null, references  Tạo View  Truy cập cạnh tranh test=# INSERT INTO cities(nc, name, pop) VALUES (3, 'Waterloo', 150000); ERROR: duplicate key violates unique constraint "cities_pkey" test=# INSERT INTO museums VALUES (2, 2, NULL); ERROR: null value in column "name" violates not-null constraint test=# INSERT INTO museums VALUES (1, 10, 'CULTURE'); ERROR: insert or update on table "museums" violates foreign key constraint "museums_nc_fkey" DETAIL: Key (nc)=(10) is not present in table "cities". 29
  30.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm Kiểm tra ràng buộc:  Định nghĩa ràng buộc dữ liệu  Định nghĩa trigger check, unique  Tạo View  Truy cập cạnh tranh test=# INSERT INTO tourists VALUES (8, 'Dinh', 'Gia Bao', 34, 'foo'); ERROR: new row for relation "tourists" violates check constraint "tourists_type_check" test=# INSERT INTO tourists VALUES (8, 'Dinh', 'Gia Bao', 0, 'sportif'); ERROR: new row for relation "tourists" violates check constraint "tourists_age_check" test=# INSERT INTO ut VALUES (1,1072); test=# INSERT INTO ut VALUES (2,1072); ERROR: duplicate key violates unique constraint "ut_acc_no_key" 30
  31.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 31
  32.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Định nghĩa Trigger  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Cú pháp: CREATE TRIGGER name { BEFORE | AFTER } { event [OR ] } ON table FOR EACH { ROW | STATEMENT } EXECUTE PROCEDURE func ( arguments ) 32
  33.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 17  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Tạo trigger để đảm bảo khi cập nhật dữ liệu trong bảng museums, cột nb_museums trong bảng cities là tổng số museum có trong thành phố 33
  34.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 18  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 34
  35.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 19  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 35
  36.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 20  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Tạo trigger để đảm bảo khi cập nhật dữ liệu trong bảng bookings, một người du lịch thuộc típ intellectuel chỉ ở trong khách sạn của một thành phố có ít nhất 1 bảo tàng 36
  37.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 21  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 37
  38.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 22  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Tạo trigger để đảm bảo khi cập nhật dữ liệu trong bảng bookings, một người du lịch thuộc típ ecologiste chỉ ở trong khách sạn của một thành phố có dân số ít hơn 1000 38
  39.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ 23  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 39
  40.  Kế thừa Kiểm tra những triggers:  Định nghĩa kiểu  Định nghĩa hàm trigger_insert_update_bookings1 &  Định nghĩa ràng buộc dữ liệu  Định nghĩa trigger  Tạo View trigger_insert_update_bookings2  Truy cập cạnh tranh test=# INSERT INTO bookings VALUES (3, 3, '1999-04-30', 2); ERROR: A tourist of the ecologiste type only books the hotel in a city having less 1000 populations test=# INSERT INTO bookings VALUES (2, 3, '1999-04-30', 2); ERROR: A tourist of the intellectuel type only books the hotel in a city having at least one museum 40
  41.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 41
  42.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Định nghĩa View  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Cú pháp: CREATE VIEW view_name AS SELECT query  Từ ver. 9.2 trở về trước postgres không cho phép insert, update, delete trên view. Cần kết hợp với CREATE RULE để thực hiện insert, update, delete  Nhưng từ ver. 9.3 postgres cho phép insert, update, delete trên view đơn giản (không có mệnh đề WITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, etc.) 42
  43.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 43
  44.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 44
  45.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 45
  46.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Ví dụ  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 46
  47.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh 47
  48.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Truy cập cạnh tranh  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Nhiều người dùng chia sẻ cơ sở dữ liệu  Cập nhật, truy vấn đồng thời  Làm thế nào để đảm bảo được tính nhất quán của dữ liệu từ phía các người dùng ?  Khái niệm giao dịch (TRANSACTION)  ACID (Atomic, Consistent, Isolated, Durable)  MVCC (MultiVersion Concurrency Control)  BEGIN, ABORD, END, COMMIT, ROLLBACK, SAVEPOINT, CHECKPOINT, SET TRANSACTION, START TRANSACTION, SET CONSTRAINTS, LOCK 48
  49.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Truy cập cạnh tranh  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Tính nguyên tử  Giao dịch kết thúc làm « all-or-nothing » trên cơ sở dữ liệu  Giao dịch bắt đầu bằng BEGIN  Lệnh trong giao dịch chỉ được thực hiện khi gặp « COMMIT »  Lệnh trong giao dịch sẽ bị hủy bỏ khi gặp « ROLLBACK »  Mọi câu SQL đều nên thực thi trong một giao dịch 49
  50.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Truy cập cạnh tranh  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Tính nguyên tử  Một giao dịch nhóm nhiều phép toán thành phép toán nguyên tử  Kết quả của giao dịch là « all-or-nothing » 50
  51.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Truy cập cạnh tranh  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Tính cô lập  Có 4 mức độ cô lập: SERIALIZABLE, REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED  3 hiện tượng thường gặp: dirty read, nonrepeatable read, phantom read 51
  52.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Truy cập cạnh tranh  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Tính cô lập  Mặc định PostgreSQL sử dụng READ COMMITTED 52
  53.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Truy cập cạnh tranh  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Tính cô lập: READ COMMITTED => nonrepeatable read 53
  54.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Truy cập cạnh tranh  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Tính cô lập  Sử dụng SERIALIZABLE  Cho phép chỉ thấy dữ liệu đã được committed trước thời điểm bắt đầu của giao dịch  Cho phép thấy sự thay đổi bởi các lệnh trước đó trong cùng giao dịch mặc dù chưa được committed 54
  55.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Truy cập cạnh tranh  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Công cụ LOCK  Sử dụng LOCK tường minh hay SELECT FOR UPDATE 55
  56.  Kế thừa  Định nghĩa kiểu  Định nghĩa hàm  Định nghĩa ràng buộc dữ liệu Savepoints  Định nghĩa trigger  Tạo View  Truy cập cạnh tranh  Đánh dấu bên trong một giao dịch  Giúp cho việc hủy bỏ những phép toán từ vị trí Savepoint đến lệnh Rollback trong một giao dịch  Một giao dịch có thể có nhiều Savepoints 56