›
›

Bài giảng Hệ quản trị cơ sở dữ liệu (Phần II) - Nguyễn Vương Thịnh

pdf 89 trang ngocly 3440
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Hệ quản trị cơ sở dữ liệu (Phần II) - Nguyễn Vương Thịnh", để 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:

  • pdfbai_giang_he_quan_tri_co_so_du_lieu_phan_ii_nguyen_vuong_thi.pdf

Nội dung text: Bài giảng Hệ quản trị cơ sở dữ liệu (Phần II) - Nguyễn Vương Thịnh

  1. Phần 2. CÂU LỆNH T-SQL Trong phần này sẽ giới thiệu cấu trúc, kỹ thuật soạn kịch bản lệnh T-SQL, đối với các hệ quản trị CSDL Foxfro, Access thì câu lệnh thực hiện truy vấn, khai thác CSDL là ngôn ngữ truy vấn SQL (Structure Query Language), các lệnh được thực hiện theo từng câu lệnh mà không thực hiện theo kịch bản hoặc theo tập hợp nhiều câu lệnh với nhau. Đối với hệ quản trị CSDL Oracle thì ngôn ngữ truy vấn dữ liệu là SQL/PL (SQL Plus), còn SQL Server ngôn ngữ có tên Transact-SQL viết tắt là T-SQL. ĐỊNH NGHĨA DỮ LIỆU (DATA DEFINITION LAGUAGE - DDL). Phần này sẽ xem xét các lệnh liên quan đến tạo mới, sửa đổi, xóa các đối tượng liên quan đến Table, View và các đối tượng khác. Tạo kiểu dữ liệu mới. Tạo kiểu dữ liệu dạng user-defined. Cú pháp: sp_addtype [ @typename = ] type, [ @phystype = ] system_data_type [ , [ @nulltype = ] 'null_type' ] [ , [ @owner = ] 'owner_name' ] Ví dụ: sp_addtype ssn, ‘varchar(11)’, ‘NOT NULL’ Xóa kiểu dữ liệu đã tạo. Cú pháp: sp_droptype [ @typename = ] 'type' Ví dụ: Sp_droptype ssn Tạo ràng buộc (Constraint). Tạo ràng buộc được thực hiện trong 2 câu lệnh Create Table hoặc Alter Table: Check, Default, Foreign Key, Primary Key, Unique. 148
  2. Xét một số ví dụ sau: + Tạo một Check. trong bảng authors. ALTER TABLE authors ADD CONSTRAINT chau_id CHECK(au_id LIKE ‘[0-9][0-9][0-9]-[0-9][0-9]- [0-9][0-9] [0-9][0-9]’) + Tạo Check trong bảng Publishers. ALTER TABLE publishers ADD chpub_id CHECK(pub_id IN (‘1389’, ‘0736’, ‘0877’, ‘1622’, ‘1756’) OR pub_id LIKE ‘99[0-9][0-9]’) + Tạo ràng buộc Default. ALTER TABLE authors ADD DEFAULT ‘UNKNOWN’ for au_lname + Tạo ràng buộc Foreign Key. ALTER TABLE titles ADD CONSTRAINT FK_pub_id FOREIGN KEY(pub_id) REFERENCES publishers(pub_id) + Tạo ràng buộc Primary Key. ALTER TABLE authors ADD CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED (au_id) + Tạo ràng buộc Unique. ALTER TABLE stores ADD CONSTRAINT UNC_name_city UNIQUE NONCLUSTERED(store_name, city) Xóa ràng buộc. Sử dụng Drop trong các câu lệnh Create Table hoặc Alter Table. + Ví dụ xóa Constraint sử dụng câu lệnh Alter Table. ALTER TABLE authors DROP CONSTRAINT UPKCL_auidind Hiển thị ràng buộc. sp_helpconstraint titltes Tạo bảng. Để tạo bảng dữ liệu có thể sử dụng 2 câu lệnh Create Table hoặc Select Into. + Tạo bảng tạm thời local (là bảng chỉ hiện với phiên hiện thời, tên bảng được bắt đầu bằng một dấu #). CREATE TABLE #MyTempTable (cola INT PRIMARY KEY) INSERT INTO #MyTempTable VALUES (1) + Tạo bảng tạm thời global (hiện với tất cả các phiên, tên bảng được bắt đầu bằng 2 dấu #). CREATE TABLE ##MyTempTable (cola INT PRIMARY KEY) 149
  3. INSERT INTO ##MyTempTable VALUES (1) + Tạo bảng dữ liệu. /* jobs table */ CREATE TABLE jobs ( job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, job_desc varchar(50) NOT NULL DEFAULT 'New Position - title not formalized yet', min_lvl tinyint NOT NULL CHECK (min_lvl >= 10), max_lvl tinyint NOT NULL CHECK (max_lvl <= 250) ) /* employee table */ CREATE TABLE employee ( emp_id empid CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED CONSTRAINT CK_emp_id CHECK (emp_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0- 9][0-9][FM]'), fname varchar(20) NOT NULL, minit char(1) NULL, lname varchar(30) NOT NULL, job_id smallint NOT NULL DEFAULT 1 REFERENCES jobs(job_id), job_lvl tinyint DEFAULT 10, pub_id char(4) NOT NULL DEFAULT ('9952') REFERENCES publishers(pub_id), hire_date datetime NOT NULL DEFAULT (getdate()) ) /* publishers table */ 150
  4. CREATE TABLE publishers ( pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756') OR pub_id LIKE '99[0-9][0-9]'), pub_name varchar(40) NULL, city varchar(20) NULL, state char(2) NULL, country varchar(30) NULL DEFAULT('USA') ) Xóa bảng. Sử dụng lệnh Drop Table. + Xóa bảng trong CSDL hiện thời: Drop Table MyTable + Xóa bảng trong CSDL khác. DROP TABLE pubs.dbo.authors2 Đổi tên bảng. Sử dụng thủ tục sp_rename + Đổi tên bảng: Sp_rename titltes, books Sửa cấu trúc bảng. Sử dụng lệnh Alter Table. + Thêm một cột vào bảng. CREATE TABLE doc_exa ( column_a INT) GO ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL GO EXEC sp_help doc_exa GO DROP TABLE doc_exa GO + Xóa một cột khỏi bảng. CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL) 151
  5. GO ALTER TABLE doc_exb DROP COLUMN column_b GO EXEC sp_help doc_exb GO DROP TABLE doc_exb GO Tạo Index. Sử dụng lệnh Create Index. + Tạo Index. SET NOCOUNT OFF USE pubs IF EXISTS (SELECT name FROM sysindexes WHERE name = 'au_id_ind') DROP INDEX authors.au_id_ind GO USE pubs CREATE UNIQUE CLUSTERED INDEX au_id_ind ON authors (au_id) GO Xem thông tin Index. Sử dụng thủ tục sp_helpindex + Xem Index của bảng authors. sp_helpindex authors Xóa Index. Sử dụng lệnh Drop Index. + Xóa Index của bảng authors. DROP INDEX authors.au_id_ind Tạo khung nhìn. Sử dụng lệnh Create View. + Tạo View. USE pubs IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'titles_view') 152
  6. DROP VIEW titles_view GO CREATE VIEW titles_view AS SELECT title, type, price, pubdate FROM titles GO Xóa khung nhìn. Sử dụng lệnh Drop View. + Xóa khung nhìn. USE pubs IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'titles_view') DROP VIEW titles_view GO Đổi tên khung nhìn. Sử dụng lệnh thủ tục sp_rename. + Đổi tên view. sp_rename titles_view, view_titles THAO TÁC VỚI DỮ LIỆU (DATA MANIPULATION LANGUAGE - DML). Phần này sẽ xem xét các câu lệnh thao tác với dữ liệu như Insert, Select, Delete. Lệnh Insert - Chèn dữ liệu vào bảng. Sử dụng câu lệnh Insert. + Chèn dữ liệu vào tất cả các cột, theo thứ tự của trong bảng. IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T1') DROP TABLE T1 GO CREATE TABLE T1 ( column_1 int, column_2 varchar(30)) INSERT T1 VALUES (1, 'Row #1') 153
  7. + Chèn dữ liệu vào các cột không theo thứ tự. IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T1') DROP TABLE T1 GO CREATE TABLE T1 ( column_1 int, column_2 varchar(30)) INSERT T1 (column_2, column_1) VALUES ('Row #1',1) + Chèn dữ liệu số giá trị ít hơn số cột. IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T1') DROP TABLE T1 GO CREATE TABLE T1 ( column_1 int identity, column_2 varchar(30) CONSTRAINT default_name DEFAULT ('column default'), column_3 int NULL, column_4 varchar(40) ) INSERT INTO T1 (column_4) VALUES ('Explicit value') INSERT INTO T1 (column_2,column_4) VALUES ('Explicit value', 'Explicit value') INSERT INTO T1 (column_2,column_3,column_4) VALUES ('Explicit value',-44,'Explicit value') SELECT * FROM T1 + Chèn dữ liệu với bảng có cột dữ liệu IDENTITY. Ví dụ sau sẽ thực hiện chèn dữ liệu vào bảng có cột kiểu IDENITY, cột có kiểu IDENTITY sẽ tự động gán giá trị khi hàng mới được tạo, nên người nhập sẽ không nhập và sửa đổi. Tuy nhiên vó thể sử dụng câu lệnh SET IDENTITY_INSERT để nhập giá trị. 154
  8. IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T1') DROP TABLE T1 GO CREATE TABLE T1 ( column_1 int IDENTITY, column_2 varchar(30)) INSERT T1 VALUES ('Row #1') INSERT T1 (column_2) VALUES ('Row #2') SET IDENTITY_INSERT T1 ON INSERT INTO T1 (column_1,column_2) VALUES (-99,'Explicit identity value') SELECT * FROM T1 Lệnh Insert - Chèn dữ liệu vào bảng kết hợp lệnh SELECT. Câu lệnh này được thực hiện gần tương tự như câu lệnh chèn dữ liệu sử dụng từ khóa VALUES, nhưng giá trị chèn vào được truy vấn từ câu lệnh SELECT. Đối với các cột dữ liệu có kiểu Nchar, Nvarchar hỗ trợ Unicode thì khi chèn dữ liệu trực tiếp phải gán thêm tiền tố N, ví dụ Lname=N’John Smith’. + Chèn dữ liệu được truy vấn từ các cột trong lệnh SELECT. USE pubs INSERT INTO MyBooks SELECT * FROM titles WHERE type = 'mod_cook' + Chèn dữ liệu được truy vấn từ một số cột. USE pubs INSERT INTO MyBooks SELECT title_id, title, type FROM titles WHERE type = 'mod_cook' Lệnh Update – Sửa dữ liệu. 155
  9. Lệnh Update sử dụng sửa dữ liệu trong bảng hoặc View, xem xét cu pháp thông qua các ví dụ sau. + Sửa dữ liệu sử dụng lệnh Update sử dụng mệnh đề SET. UPDATE Northwind.dbo.Products SET UnitPrice = UnitPrice * 1.1 WHERE CategoryID = 2 Ho iá tr ặc gán g ị trực tiếp: UPDATE authors SET authors.au_fname = 'Annie' WHERE au_fname = 'Anne' Ho gán giá tr ặc ị NULL cho một cột. UPDATE publishers SET pub_name = NULL + Sửa dữ liệu sử dụng mệnh đề Where xác định hàng được sửa dữ liệu. UPDATE authors SET state = 'PC', city = 'Bay City' WHERE state = 'CA' AND city = 'Oakland' + Sửa dữ liệu sử dụng mệnh đề From, sử dụng thông tin từ một bảng khác. UPDATE titles SET ytd_sales = t.ytd_sales + s.qty FROM titles t, sales s WHERE t.title_id = s.title_id AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales) Hoặc ví dụ giá trị xác định là tổng từ bảng khác. UPDATE titles SET ytd_sales = (SELECT SUM(qty) 156
  10. FROM sales WHERE sales.title_id = titles.title_id AND sales.ord_date IN (SELECT MAX(ord_date) FROM sales)) FROM titles, sales + Sửa dữ liệu sử dụng mệnh đề Top, xác định số lượng hàng đầu tiên được sửa dữ liệu. UPDATE authors SET state = 'ZZ' FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1 WHERE authors.au_id = t1.au_id Lệnh WriteText – Sửa dữ liệu Text, Image. Lệnh WriteText được sử dụng cập nhật cột có kiểu Text hoặc Image. Dữ liệu kiểu Text và Image thường có kích thước lớn, có thể đến Gyga byte, nên làm việc với kiểu dữ liệu này phải sử dụng con trỏ. Để sử dụng được lệnh này trước hết người quản trị (Administrator) phải đặt thuộc tính select into/bulk copy là true, thực hiện đặt như sau: USE master EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'TRUE' Với cột dữ liệu kiểu Text, Image ta có thể gán giá trị NULL hoặc sử dụng các lệnh WriteText, UpdateText để gán giá trị, khi sử dụng các lệnh trên, hàng dữ liệu có cột cần chèn đã tồn tại (không đồng thời với câu lệnh Insert). Riêng đối với cột dữ liệu kiểu Text bạn có thể sử dụng lệnh thêm dữ liệu như các cột kiểu chuỗi khác nhưng kích thước của dữ liệu tối đa chỉ được 4096 ký tự. + Thực hiện chèn đoạn văn bản vào cột dữ liệu kiểu Text. DECLARE @ptrval binary(16) SELECT @ptrval = TEXTPTR(pr_info) FROM pub_info pr, publishers p WHERE p.pub_id = pr.pub_id AND p.pub_name = 'New Moon Books' 157
  11. WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!' GO Xem ví dụ trên ta thấy, để chèn dữ liệu vào cột Text hoặc Image ta phải sử dụng con trỏ kiểu binary hoặc varbinarry, con trỏ sẽ được xác định vào cột text, image và hàng tương ứng đã có trong bảng dữ liệu, sau đó sử dụng lệnh WriteText để gán giá trị. Trong thực tế khi thực hiện lệnh này ta thường thực hiện thông qua thủ tục lưu trữ của CSDL, giá trị được gán qua biến. Lệnh WriteText thường được sử dụng khi cột dữ liệu đó là NULL hoặc đè toàn bộ dữ liệu đã có (không chèn thêm). Lệnh UpdateText – Sửa dữ liệu Text, Image. Lệnh UpdateText có chức năng thực hiện sửa dữ liệu kiểu Text, Image, tuy nhiên UpdateText khác WriteTex, UpdateText có thể sửa., xóa dữ liệu theo từng đoạn hoặc thêm dữ liệu vào phần dữ liệu đã có của cột dữ liệu. + Cú pháp chung: UPDATETEXT { table_name.dest_column_name dest_text_ptr } { NULL | insert_offset } { NULL | delete_length } [ WITH LOG ] [ inserted_data | { table_name.src_column_name src_text_ptr } ] Trong đó: - Insert_offset: Xác định vị trí theo byte dữ liệu sẽ được đặt vào hoặc bắt đầu xóa. - Delete_length: Xác định độ dài dữ liệu được xóa tính từ vị trí insert_offset. Việc chèn, xóa, sửa dữ liệu được điều khiển thông qua các tham số insert_offset, delete_offset, ví dụ muốn sửa dữ liệu, đầu tiên phải xác định vị trí bắt đầu cần sửa dữ liệu (insert_offset) và độ dài dữ liệu cần sửa, bắt đầu từ vị trí cần xóa dữ liệu mới sẽ được chèn vào. + Ví dụ sửa nội dung cột kiểu Text. 158
  12. USE pubs GO EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true' GO DECLARE @ptrval binary(16) SELECT @ptrval = TEXTPTR(pr_info) FROM pub_info pr, publishers p WHERE p.pub_id = pr.pub_id AND p.pub_name = 'New Moon Books' UPDATETEXT pub_info.pr_info @ptrval 88 1 'b' GO EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false' GO Cursor - Điều khiển con trỏ. Cursor là kiểu biến xác định con trỏ cho một tập dữ liệu, là kết quả của câu lệnh Select. Cursor được kết hợp cùng lệnh Fetch để xác định vị trí hàng trong tập dữ liệu. Cursor có 2 kiểu Cursor thông thường và Scroll Cursor. Các thao tác thực hiện với Cursor: + Declare: Khai báo. + Open: Mở con trỏ để làm việc với tập dữ liệu. + Fetch: Dịch chuyển vị trí hàng trong tập dữ liệu. + Close: Đóng con trỏ. + DeAllocate: Giải phóng con trỏ. + Ví dụ sử dụng Curcor, liệt kê danh sách các hàng của bảng Authors. USE pubs GO DECLARE authors_cursor CURSOR FOR SELECT au_lname FROM authors WHERE au_lname LIKE "B%" ORDER BY au_lname OPEN authors_cursor Perform the first fetch. FETCH NEXT FROM authors_cursor 159
  13. Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN This is executed as long as the previous fetch succeeds. FETCH NEXT FROM authors_cursor END CLOSE authors_cursor DEALLOCATE authors_cursor GO + Ví dụ sử dụng Cursor, giá trị cột được đưa vào biến. USE pubs GO Declare the variables to store the values returned by FETCH. DECLARE @au_lname varchar(40), @au_fname varchar(20) DECLARE authors_cursor CURSOR FOR SELECT au_lname, au_fname FROM authors WHERE au_lname LIKE "B%" ORDER BY au_lname, au_fname OPEN authors_cursor Perform the first fetch and store the values in variables. Note: The variables are in the same order as the columns in the SELECT statement. FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname Check @@FETCH_STATUS to see if there are any more rows to fetch. 160
  14. WHILE @@FETCH_STATUS = 0 BEGIN Concatenate and display the current values in the variables. PRINT "Author: " + @au_fname + " " + @au_lname This is executed as long as the previous fetch succeeds. FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname END CLOSE authors_cursor DEALLOCATE authors_cursor GO + Ví dụ sử dụng Scroll Cursor, con trỏ cho phép sử dụng các phương thức: LAST, PRIOR, RELATIVE, ABSOLUTE. USE pubs GO Execute the SELECT statement alone to show the full result set that is used by the cursor. SELECT au_lname, au_fname FROM authors ORDER BY au_lname, au_fname Declare the cursor. DECLARE authors_cursor SCROLL CURSOR FOR SELECT au_lname, au_fname FROM authors ORDER BY au_lname, au_fname OPEN authors_cursor Fetch the last row in the cursor. FETCH LAST FROM authors_cursor Fetch the row immediately prior to the current row in the cursor. FETCH PRIOR FROM authors_cursor 161
  15. Fetch the second row in the cursor. FETCH ABSOLUTE 2 FROM authors_cursor Fetch the row that is three rows after the current row. FETCH RELATIVE 3 FROM authors_cursor Fetch the row that is two rows prior to the current row. FETCH RELATIVE -2 FROM authors_cursor CLOSE authors_cursor DEALLOCATE authors_cursor GO Lệnh Delete – Xóa dữ liệu. Sử dụng lệnh Delete để xóa dữ liệu, kết hợp cùng điều kiện để xóa một hay nhiều hàng dữ liệu trong bảng. + Xóa tất cả các hàng của bảng. USE pubs DELETE authors + Xóa một tập các hàng. USE pubs DELETE FROM authors WHERE au_lname = 'McBadden' + Xóa một hàng tạ vị trí con trỏ. USE pubs DELETE FROM authors WHERE CURRENT OF complex_join_cursor Trong ví dụ trên con trỏ đã được mở có tên complex_join_curcor. 162
  16. + Xóa các hàng dựa vào lệnh truy vấn khác hoặc liên kết các bảng. /* SQL-92-Standard subquery */ USE pubs DELETE FROM titleauthor WHERE title_id IN (SELECT title_id FROM titles WHERE title LIKE '%computers%') /* Transact-SQL extension */ USE pubs DELETE titleauthor FROM titleauthor INNER JOIN titles ON titleauthor.title_id = titles.title_id WHERE titles.title LIKE '%computers%' + Xóa dữ liệu sử dụng từ khóa Top. DELETE authors FROM (SELECT TOP 10 * FROM authors) AS t1 WHERE authors.au_id = t1.au_id Lệnh Truncate Table – Xóa dữ liệu toàn bảng. Tương tự như câu lệnh Delete, lệnh Truncate Table sử dụng xóa dữ liệu toàn bảng, thao tác này giống lệnh Delete khi không có điều kiện Where nhưng lệnh Truncate Table thực hiện nhanh hơn. TRUNCATE TABLE authors Lệnh Go – Nhóm lệnh. Lệnh Go không tham gia thao tác với CSDL, lệnh Go xác định nhóm các lệnh với nhau, nhóm lệnh được xác định từ vị trí đầu tiên hoặc từ từ lệnh Go trước đó đến lệnh Go tiếp theo. Khi gặp lệnh Go nhóm lệnh sẽ được gửi ngay đến SQL Server để thực hiện. 163
  17. USE pubs GO DECLARE @MyMsg VARCHAR(50) SELECT @MyMsg = 'Hello, World.' GO @MyMsg is not valid after this GO ends the batch. Yields an error because @MyMsg not declared in this batch. PRINT @MyMsg GO SELECT @@VERSION; Yields an error: Must be EXEC sp_who if not first statement in batch. sp_who GO Contrrol-of-Flow - Điều khiển luồng. Tương tự như các ngôn ngữ lập trình thiết kế ứng dụng, T-SQL cho phép thiết lập kịch bản câu lệnh, cho phép sử dụng các lệnh điều khiển khối, luồng, vòng lặp, điều kiện, rẽ nhánh, Sau đây là bảng các lệnh: Từ khóa Mô tả BEGIN END Khối lệnh GOTO Lệnh nhảy IF ELSE Lệnh điều kiện RETURN Thoát WAITFOR Chờ thực hiện lệnh WHILE BREAK CONTINUE Vòng lặp, thoát khỏi vòng lặp, quay lại lặp CASE Rẽ nhánh DECLARE Khai báo PRINT In thông báo RAISEERROR Trả lại mã lỗi EXECUTE (EXEC) Thực hiện lệnh 164
  18. TRUY VẤN DỮ LỆU. Trong trước ta đã xem xét những câu lệnh thao tác với dữ liệu như Insert, Update, Delete, phần này ta sẽ xem xét các câu lệnh khai thác truy vấn dữ liệu như Select, các phép Join, Lệnh Use - Chọn Cơ sở dữ liệu. Sử dung lệnh Use để chọn CSDL trong kịch bản câu lệnh. Use Pubs Select - Truy vấn tất cả các cột từ một bảng. Lệnh Select được sử dụng truy vấn dữ liệu từ một hoặc nhiều bảng, từ khung nhìn, kết quả đưa lại một tập dữ liệu gồm các hàng, cột. USE Northwind GO SELECT * FROM Shippers GO Order by - Truy vấn sắp xếp danh sách theo thứ tự. ASC là sắp xếp tăng, DESC là sắp xếp giảm, khi xác định sắp xếp tăng bạn có thể không cần đặt từ khóa ASC mà hệ thống tự xác định là ASC. USE Northwind GO SELECT * FROM Shippers ORDER BY CompanyName DESC GO Truy vấn một số cột, xác định thứ tự các cột. 165
  19. USE Northwind GO SELECT OrderID, ProductID, UnitPrice, Quantity, Discount FROM [Order Details] ORDER BY OrderID ASC GO Đổi tên các cột khi truy vấn. USE Northwind GO SELECT OrderID as [Order ID], ProductID as [Product ID], UnitPrice as [Unit Price], Quantity, Discount FROM [Order Details] ORDER BY OrderID ASC GO Lệnh Case - Phân lớp dữ liệu. Case là câu lệnh rẽ nhánh, thường được sử dụng phân lớp dữ liệu trong câu lệnh Select. Ví dụ sử dụng lệnh Case đơn giản: USE pubs GO SELECT Category = CASE type WHEN 'popular_comp' THEN 'Popular Computing' WHEN 'mod_cook' THEN 'Modern Cooking' WHEN 'business' THEN 'Business' WHEN 'psychology' THEN 'Psychology' WHEN 'trad_cook' THEN 'Traditional Cooking' ELSE 'Not yet categorized' END, CAST(title AS varchar(25)) AS 'Shortened Title', price AS Price FROM titles WHERE price IS NOT NULL 166
  20. ORDER BY type, price COMPUTE AVG(price) BY type GO Ví dụ sử dụng lệnh Case tìm kiếm: USE pubs GO SELECT 'Price Category' = CASE WHEN price IS NULL THEN 'Not yet priced' WHEN price = 10 and price < 20 THEN 'Coffee Table Title' ELSE 'Expensive book!' END, CAST(title AS varchar(20)) AS 'Shortened Title' FROM titles ORDER BY price GO Kết quả thực hiện như sau: Price Category Shortened Title Not yet priced Net Etiquette Not yet priced The Psychology of Co Very Reasonable Title The Gourmet Microwav Very Reasonable Title You Can Combat Compu Very Reasonable Title Life Without Fear Very Reasonable Title Emotional Security: Coffee Table Title Is Anger the Enemy? Coffee Table Title Cooking with Compute Coffee Table Title Fifty Years in Bucki Coffee Table Title Sushi, Anyone? Coffee Table Title Prolonged Data Depri Coffee Table Title Silicon Valley Gastr Coffee Table Title Straight Talk About Coffee Table Title The Busy Executive's 167
  21. Expensive book! Secrets of Silicon V Expensive book! Onions, Leeks, and G Expensive book! Computer Phobic And Expensive book! But Is It User Frien (18 row(s) affected) Đặt tên cho cột. Sử dụng dấu phẩy xác định tên cột, tối đa là 30 ký tự. (ytd_sales) FROM titles SELECT ‘sum’= SUM Khi cần thể hiện dấu phẩy trên giá trị hoặc tên cột ta cần sử dụng 2 dấu liền nhau. Ví dụ ‘I don’’t understand.’ Chuỗi ký tự trong kết quả truy vấn. Sử dụng dấu phẩy trong chuỗi ký tự. er=pub_name SELECT ‘The publisher’’s name is’, publish FROM publishers Các giá trị tính toán được. Đối các kiểu dữ liệu tính toán được sử dụng các phép toán +, -, *, /, %. SELECT title_id, ytd_sales*2 FROM titles Truy vấn kiểu dữ liệu Text, Image. Để truy vấn dữ liệu Text, Image có thể sử dụng 2 lệnh Select hoặc ReadText. Khi sử dụng lệnh Select để truy vấn kiểu dữ liệu này thì chỉ truy vấn được dữ liệu có độ dài xác định trước bằng câu lệnh SET TEXTSIZE. SET TEXTSIZE 25 168
  22. SELECT pub_id, pr_info FROM pub_info Ngầm định kích thước sử dụng cho truy vấn là 4096 (4K). Từ khóa Distinct – Truy vấn các hàng khác nhau theo cột. Để truy vấn các hàng dữ liệu khác nhau theo cột ta sử dụng từ khóa Distinct. USE pubs SELECT DISTINCT au_id FROM titleauthor Xác định bảng trong mệnh đề From. USE pubs SELECT p.pub_id, p.pub_name FROM publishers p Mệnh đề Where. Mệnh đề Where xác định điều kiện các hàng được truy vấn, biểu thức trong mệnh đề Where xác định theo biểu thức logic. Các phép toán, câu lệnh xác định gồm: - Các phép toán so sánh: =, , ! . - Từ khóa xác định phạm vi: Between, Not Between. - Danh sách: In, Not In. - Theo mẫu đinh dạng: Like, Not Like. - Giá trị NULL: Is Null, Is Not Null. - Các phép toán logic: And, Or. + Từ khóa Between: SELECT UnitsInStock, ProductID, ProductName 169
  23. FROM Northwind.dbo.Products WHERE UnitsInStock BETWEEN 15 AND 25 ORDER BY UnitsInStock + Từ khóa Not Between. SELECT UnitsInStock, ProductID, ProductName FROM Northwind.dbo.Products WHERE UnitsInStock NOT BETWEEN 15 AND 25 ORDER BY UnitsInStock + Từ khóa In, Not In. USE pubs SELECT au_lname, state FROM authors WHERE state IN ('CA', 'IN', 'MD') USE pubs SELECT au_lname, au_fname FROM authors WHERE au_id IN (SELECT au_id FROM titleauthor WHERE royaltyper < 50) USE pubs SELECT au_lname, au_fname FROM authors WHERE au_id NOT IN (SELECT au_id FROM titleauthor WHERE royaltyper < 50) + Từ khóa Like. 170
  24. Từ khóa Like được sử dụng tương tự như phép so sánh, phép Like được thực hiện cho dữ liệu kiểu chuỗi, phép Like được xem là phép so sánh theo định dạng của chuỗi, việc định dạng xác định theo một số từ khóa sau: % Xác định bất kỳ chuỗi ký tự nào hoặc không có ký tự nào tại vị trí. _ Một ký tự bất kỳ nào đó. [] Một ký tự nào đó nằm trong phạm vi, ví dụ [a-f]. [^] Xác định một ký tự không thuộc phạm vi nào đó, ví dụ [^a-f]. Ví dụ sử dụng từ khóa Like với %: USE pubs GO SELECT phone FROM authors WHERE phone LIKE '415%' ORDER by au_lname GO Ví dụ từ khóa Not Like với %: USE pubs GO SELECT phone FROM authors WHERE phone NOT LIKE '415%' ORDER BY au_lname GO Ví dụ sử dụng từ khóa Like với mệnh đề Escape: Escape được sử dụng loại bỏ một ký tự hoặc chuỗi khỏi phép so sánh. USE pubs GO 171
  25. IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'mytbl2') DROP TABLE mytbl2 GO USE pubs GO CREATE TABLE mytbl2 ( c1 sysname ) GO INSERT mytbl2 VALUES ('Discount is 10-15% off') INSERT mytbl2 VALUES ('Discount is .10-.15 off') GO SELECT c1 FROM mytbl2 WHERE c1 LIKE '%10-15!% off%' ESCAPE '!' GO Ví dụ sử dụng từ khóa Like với []: USE pubs GO SELECT au_lname, au_fname, phone FROM authors WHERE au_lname LIKE '[CK]ars[eo]n' ORDER BY au_lname ASC, au_fname ASC GO + Giá trị NULL. Giá trị NULL được nhập bằng cách đặt ngầm định hoặc gán theo câu lệnh. Để tìm giá trị NULL trong bảng sử dụng từ khóa Is Null hoặc Is Not Null. SELECT title_id, type, advance FROM pubs.dbo.titles WHERE advance IS NULL 172
  26. TẠO BẢNG BẰNG LỆNH SELECT INTO. Lệnh Select Into truy vấn dữ liệu, dữ liệu được đưa vào một bảng mới. Nếu thuộc tính select into/bulkcopy được đặt có thể tạo bảng cố định, nếu thuộc tính không được đặt ta có thể tạo bảng tạm thời. SELECT Shippers.*, Link.Address, Link.City, Link.Region, Link.PostalCode INTO NewShippers FROM Shippers JOIN LinkServer.DB.dbo.Shippers AS Link ON (Shippers.ShipperID = Link.ShipperID) LỆNH COMPUTE BY. Khi thực hiện với các hàm tính toán SUM, AVG, MIN, MAX, COUNT thường được sử dụng với các mệnh đề GROUP BY, COMPUTE BY (không áp dụng các hàm tính toán với dữ liệu kiểu Text, Image). + Sử dụng Group By: Từ khóa Group By được sử dụng nhóm theo cột, có thể kết hợp các hàm tính toán. USE Northwind SELECT OrdD.ProductID AS ProdID, SUM(OrdD.Quantity) AS AmountSold FROM [Order Details] AS OrdD JOIN Products as Prd ON OrdD.ProductID = Prd.ProductID AND Prd.CategoryID = 2 GROUP BY OrdD.ProductID + Sử dụng mệnh đề Compute: Tính toán toàn bộ giá trị. USE pubs SELECT type, price, advance FROM titles ORDER BY type COMPUTE SUM(price), SUM(advance) 173
  27. + Sử dụng mệnh đề Compute By: Tính toán theo nhóm (tương tự Group By). USE pubs SELECT type, price, advance FROM titles ORDER BY type COMPUTE SUM(price), SUM(advance) BY type TOÁN TỬ UNION. Toán tử Union thực hiện hợp 2 tập với nhau, phép toán này thực hiện chỉ lấy đại diện khi có hai hàng của hai tập trùng nhau. Giả sử có 2 bảng dữ liệu như sau: Table1 Table2 ColumnA ColumnB ColumnC ColumnD char(4) int char(4) int abc 1 ghi 3 def 2 jkl 4 ghi 3 mno 5 Thực hiện toán tử Union: SELECT * FROM Table1 UNION SELECT * FROM Table2 Kết quả thực hiện: ColumnA ColumnB abc 1 def 2 ghi 3 jkl 4 174
  28. mno 5 Khi sử dụng từ khóa ALL toàn bộ các hàng của hai tập dữ liệu sẽ được hợp lại, không loại bỏ những hàng trùng nhau. SELECT * FROM TableA UNION ALL ( SELECT * FROM TableB UNION SELECT * FROM TableC ) GO Sử dụng toán tử Union với Select Into: USE Northwind IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CustomerResults') DROP TABLE CustomerResults GO USE Northwind SELECT ContactName, CompanyName, City, Phone INTO CustomerResults FROM Customers WHERE Country IN ('USA', 'Canada') UNION SELECT ContactName, CompanyName, City, Phone FROM SouthAmericanCustomers ORDER BY CompanyName, ContactName ASC GO TRUY VẤN DỮ LIỆU TỪ NHIỀU BẢNG. Truy vấn dữ liệu từ nhiều bảng được xác định theo quan hệ giữa các cột của các bảng với nhau. Có thể truy vấn thông qua điều kiện liên kết trong mệnh đề Where hoặc từ khóa Join. 175
  29. Theo điều kiện liên kết. Sử dụng điều kiện liên kết theo cột giữa các bảng, thông tin cần truy vấn được đặt ở nhiều bảng khác nhau, để truy vấn được các thông tin như trên phải xác định điều kiện liên kết giữa các bảng. + Liên kết bằng nhau. SELECT P.ProductID, S.SupplierID, S.CompanyName FROM Suppliers AS S, Products AS P WHERE S.SupplierID = P.SupplierID AND P.UnitPrice > $10 AND S.CompanyName LIKE N'F%' Đối với câu lệnh truy vấn theo điều kiện liên kết nói trên, các hàng chứa giá trị Null của cột tham gia liên kết sẽ không được liệt kê, câu lệnh này tương đương với lệnh Inner Join (sẽ xem trong phần sau). + Liên kết không bằng nhau. Liên kết dạng này sử dụng các phép toán so sánh >, >=, , !>, ! p.state and p.pub_name = 'New Moon Books' ORDER BY au_lname ASC, au_fname ASC + Tự liên kết bằng nhau. Tự liên kết trong một bảng, câu lệnh dạng này thường được sử dụng trong việc xác định những cặp giá trị nào các cột trong bảng có quan hệ với nhau theo liên kết. 176
  30. USE pubs SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname FROM authors au1, authors au2 WHERE au1.zip = au2.zip and au1.city = 'Oakland' ORDER BY au1.au_fname ASC, au1.au_lname ASC + Tự liên kết không bằng nhau. USE pubs SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname FROM authors au1, authors au2 WHERE au1.zip = au2.zip AND au1.city = 'Oakland' AND au1.state = 'CA' AND au1.au_id < au2.au_id ORDER BY au1.au_lname ASC, au1.au_fname ASC + Truy vấn dữ liệu từ nhiều hơn 2 bảng dữ liệu. Truy vấn dạng này thực hiện điều kiện liên kết theo từng cặp các bảng với nhau. USE pubs SELECT a.au_lname, a.au_fname, t.title FROM authors a, titleauthor ta, titles t WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id AND t.type = 'trad_cook' ORDER BY t.title ASC + Liên kết ngoài trái. Như những điều kiện liên kết nói trên, những hàng có cột là Null sẽ không được đưa ra tập kết quả, câu lệnh liên kết ngoài sẽ đưa ra những hàng chứa giá trị Null nói trên. Xác định liên kết ngoài bằng toán tử *. USE pubs SELECT a.au_fname, a.au_lname, p.pub_name 177
  31. FROM authors a, publishers p WHERE a.city *= p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC Bảng authors sẽ đưa ra cả những hàng có cột có cột city là Null, khi đó chưa có pub_name, kết quả như sau: au_fname au_lname pub_name Reginald Blotchet-Halls NULL Michel DeFrance NULL Innes del Castillo NULL Ann Dull NULL Marjorie Green NULL Morningstar Greene NULL Burt Gringlesby NULL Sheryl Hunter NULL Livia Karsen NULL Charlene Locksley NULL Stearns MacFeather NULL Heather McBadden NULL Michael O'Leary NULL Sylvia Panteley NULL Albert Ringer NULL Anne Ringer NULL Meander Smith NULL Dean Straight NULL Dirk Stringer NULL Johnson White NULL Akiko Yokomoto NULL Abraham Bennet Algodata Infosystems Cheryl Carson Algodata Infosystems (23 row(s) affected) + Liên kết ngoài phải. USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors AS a, publishers AS p WHERE a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC 178
  32. Kết quả thực hiện: au_fname au_lname pub_name Abraham Bennet Algodata Infosystems Cheryl Carson Algodata Infosystems NULL NULL Binnet & Hardley NULL NULL Five Lakes Publishing NULL NULL GGG&G NULL NULL Lucerne Publishing NULL NULL New Moon Books NULL NULL Ramona Publishers NULL NULL Scootney Books (9 row(s) affected) Lệnh Join – Truy vấn từ nhiều bảng. Phần trên ta đã xem xét kỹ thuật truy vấn dữ liệu từ nhiều bảng sử dụng điều kiện liên kết, tương tự như các phép toán so sánh, *=, =* SQL Server cung cấp câu lệnh Join thay thế các phép toán nói trên. + Inner Join – Liên kết trong. Thay vì xác định điều kiện liên kết trong mệnh đề Where thì ở đây ta chỉ cần xác định liên kết trong mệnh đề From. Liên kết bằng: USE pubs SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city = p.city ORDER BY a.au_lname DESC Liên kết không bằng: USE pubs 179
  33. SELECT p.pub_name, p.state, a.au_lname, a.au_fname, a.state FROM publishers p INNER JOIN authors a ON a.state > p.state WHERE p.pub_name = 'New Moon Books' ORDER BY au_lname ASC, au_fname ASC + Tự liên kết trong bảng. Tự liên kết bằng: USE pubs SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname FROM authors au1 INNER JOIN authors au2 ON au1.zip = au2.zip WHERE au1.city = 'Oakland' ORDER BY au1.au_fname ASC, au1.au_lname ASC Tự liên kết không bằng: USE pubs SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname FROM authors au1 INNER JOIN authors au2 ON au1.zip = au2.zip WHERE au1.city = 'Oakland' AND au1.state = 'CA' AND au1.au_id < au2.au_id ORDER BY au1.au_lname ASC, au1.au_fname ASC + Liên kết nhiều hơn 2 bảng. USE pubs SELECT a.au_lname, a.au_fname, t.title FROM authors a INNER JOIN titleauthor ta 180
  34. ON a.au_id = ta.au_id JOIN titles t ON ta.title_id = t.title_id WHERE t.type = 'trad_cook' ORDER BY t.title ASC + Liên kết ngoài trái - LEFT OUTER JOIN. Liên kết ngoài trái tương tự như phép toán *=. USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC + Liên kết ngoài phảii – RIGHT OUTER JOIN. Liên kết ngoài phải tương tự như phép toán =*. USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a RIGHT OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC + Liên kết ngoài 2 phía – FULL OUTER JOIN. Là phép liên kết trái hoặc phải. USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a FULL OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC Kết quả như sau: 181
  35. au_fname au_lname pub_name Reginald Blotchet-Halls NULL Michel DeFrance NULL Innes del Castillo NULL Ann Dull NULL Marjorie Green NULL Morningstar Greene NULL Burt Gringlesby NULL Sheryl Hunter NULL Livia Karsen NULL Charlene Locksley NULL Stearns MacFeather NULL Heather McBadden NULL Michael O'Leary NULL Sylvia Panteley NULL Albert Ringer NULL Anne Ringer NULL Meander Smith NULL Dean Straight NULL Dirk Stringer NULL Johnson White NULL Akiko Yokomoto NULL Abraham Bennet Algodata Infosystems Cheryl Carson Algodata Infosystems NULL NULL Binnet & Hardley NULL NULL Five Lakes Publishing NULL NULL GGG&G NULL NULL Lucerne Publishing NULL NULL New Moon Books NULL NULL Ramona Publishers NULL NULL Scootney Books (30 row(s) affected) + Giá trị Null và phép Join. Giá trị Null không xác định trong phép so sánh của mệnh đề Where (chỉ sử dụng với các phép so sánh Is Null hoặc Is Not Null), trong phép Join ta có thể xác định giống nhau giữa 2 giá trị Null. Xét ví dụ sau: Giả sử có 2 bảng dữ liệu có giá trị như sau: 182
  36. table1 table2 a b c d 1 one NULL two NULL three 4 four 4 join4 Thực hiện phép Join như sau: SELECT * FROM table1 t1 JOIN table2 t2 ON t1.a = t2.c ORDER BY t1.a Kết quả thực hiện: a b c d 4 join4 4 four (1 row(s) affected) TRUY VẤN TỔNG HỢP. Việc sử dụng các hàm tính toán như SUM, AVG, thường được thực hiện theo các mệnh đề WHERE, GROUP BY, HAVING. Khi xác định điều kiện có sử dụng các hàm tính toán thì phải sử dụng mệnh đề HAVING mà không được sử dụng trong mệnh đề WHERE. Các hàm tính toán có thể tóm tắt như sau: SUM([ALL | DISTINCT]) Tính tổng tất cả hoặc những hàng khác nhau. AVG([ALL | DISTINCT]) Tính trung bình tất cả hoặc những hàng khác nhau. COUNT([ALL | DISTINCT]) Đếm số hàng tất cả hoặc những hàng khác nhau. COUNT(*) Đếm các hàng được lựa chọn. MAX() Tính giá trị lớn nhất. 183
  37. MIN() Tính giá trị nhỏ nhất. Các hàm SUM, AVG chỉ làm việc với dữ liệu dạng số, các hàm SUM, AVG, COUNT, MAX, MIN bỏ qua giá trị Null, hàm COUNT(*) đếm cả hàng có giá trị Null. Sử dụng hàm tính toán. + Tính tổng toàn bộ. USE pubs SELECT SUM(ytd_sales) FROM titles + Tính tổng, trung bình có điều kiện. USE pubs SELECT AVG(advance), SUM(ytd_sales) FROM titles WHERE type = 'business' Mệnh đề Group By. Group by được thực hiện nhóm các hàng theo giá trị cột xác định, các hàm tính toán sẽ được thực hiện theo nhóm nói trên. USE Northwind SELECT OrdD.ProductID AS ProdID, SUM(OrdD.Quantity) AS AmountSold FROM [Order Details] AS OrdD JOIN Products as Prd ON OrdD.ProductID = Prd.ProductID AND Prd.CategoryID = 2 GROUP BY OrdD.ProductID Kết quả thực hiện như sau: 184
  38. ProdID AmountSold 3 328 4 453 5 298 6 301 8 372 15 122 44 601 61 603 63 445 65 745 66 239 77 791 (12 row(s) affected) Mệnh đề Having. Having được sử dụng cùng với các hàm tính toán xác định điều kiện lọc các hàng, thường được kết hợp cùng mệnh đề Group By để thực hiện các hàm tính toán theo nhóm. + Having với hàm SUM. USE pubs SELECT pub_id, total = SUM(ytd_sales) FROM titles GROUP BY pub_id HAVING SUM(ytd_sales) > 40000 + Having với hàm Count. USE pubs SELECT pub_id, total = SUM(ytd_sales) FROM titles GROUP BY pub_id HAVING COUNT(*) > 5 185
  39. + Having với mệnh đề Where. SELECT pub_id, SUM(advance) AS AmountAdvanced, AVG(price) AS AveragePrice FROM pubs.dbo.titles WHERE pub_id > '0800' AND price >= $5 GROUP BY pub_id HAVING SUM(advance) > $15000 AND AVG(price) < $20 ORDER BY pub_id DESC + Having thay cho mệnh đề Where. SELECT titles.pub_id, AVG(titles.price) FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id GROUP BY titles.pub_id HAVING publishers.state = 'CA' TRUY VẤN LỒNG NHAU. Phần này sẽ xem xét các câu lệnh truy vấn lồng nhau, trong câu lệnh truy vấn Select có câu lệnh truy vấn Select khác trong điều kiện xác định của lệnh Select ngoài. Thông thường các câu lệnh dạng này đi cùng các từ lhóa IN, NOT IN, EXITST, NOT EXIST, ANY, ALL. Truy vấn lồng nhau với phép bằng. USE pubs SELECT title, price FROM titles WHERE price = (SELECT price FROM titles WHERE title = 'Straight Talk About Computers') 186
  40. Đầu tiên câu lệnh sẽ xác định hàng trong lệnh Select trong, lệnh truy vấn này phải đưa ra kết quả duy nhất. Truy vấn với từ khóa IN. Kiểm tra nằm trong tập các giá trị truy vấn được. USE pubs SELECT distinct pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business') Hằng số nằm trong khoảng: USE pubs SELECT DISTINCT au_lname, au_fname FROM authors WHERE 100 IN (SELECT royaltyper FROM titleauthor WHERE titleauthor.au_id = authors.au_id) Truy vấn với từ khóa Exist. Kiểm tra tồn tại hàng dữ liệu truy vấn được. USE pubs SELECT DISTINCT pub_name FROM publishers WHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'business') 187
  41. Truy vấn với hàm All. Kiểm tra với tất cả các hàng. USE pubs SELECT t1.type FROM titles t1 GROUP BY t1.type HAVING MAX(t1.advance) >= ALL (SELECT 2 * AVG(t2.advance) FROM titles t2 WHERE t1.type = t2.type) Truy vấn với hàm Any. Kiểm tra thỏa mãn với bất kỳ hàng nào. USE pubs SELECT title FROM titles WHERE advance > ANY (SELECT advance FROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id AND pub_name = 'Algodata Infosystems') Truy vấn với hàm Some. Kiểm tra với ít nhất một hàng. USE pubs SELECT t1.type FROM titles t1 GROUP BY t1.type HAVING MAX(t1.advance) >= SOME (SELECT 2 * AVG(t2.advance) FROM titles t2 WHERE t1.type = t2.type) 188
  42. Nhiều lệnh Select lồng nhau. USE pubs SELECT au_lname, au_fname FROM authors WHERE au_id IN (SELECT au_id FROM titleauthor WHERE title_id IN (SELECT title_id FROM titles WHERE type = 'popular_comp')) UPDATE, DELETE, INSERT VỚI LỆNH TRUY VẤN LỒNG NHAU. Việc thực hiện các lệnh thao tác với dữ liệu có thể kết hợp điều kiện truy vấn lồng nhau để xác định phạm vi dữ liệu được thao tác. Kết hợp với lenẹh Select. UPDATE titles SET price = price * 2 WHERE pub_id IN (SELECT pub_id FROM publishers WHERE pub_name = 'New Moon Books') Kết hợp với lệnh Join. UPDATE titles SET price = price * 2 FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id AND pub_name = 'New Moon Books' 189
  43. Xóa dữ liệu kết hợp với lệnh Select. DELETE sales WHERE title_id IN (SELECT title_id FROM titles WHERE type = 'business') Xóa dữ liệu với phép Join. DELETE sales FROM sales INNER JOIN titles ON sales.title_id = titles.title_id AND type = 'business' LỆNH READTEXT – ĐỌC TEXT, IMAGE. Lệnh ReadText được thực hiện đọc dữ liệu kiểu Text, Image và chuyển vào một biến. USE pubs GO DECLARE @ptrval varbinary(16) SELECT @ptrval = TEXTPTR(pr_info) FROM pub_info pr INNER JOIN publishers p ON pr.pub_id = p.pub_id AND p.pub_name = 'New Moon Books' READTEXT pub_info.pr_info @ptrval 1 25 GO Ví dụ trên thực hiện đọc dữ liệu từ cột pr_info bắt đầu từ vị trí 1, độ dài 25 byte. THAO TÁC DỮ LIỆU NGOÀI. Nội dung phần này sẽ giới thiệu câu lệnh, kỹ thuật truy vấn dữ liệu của hệ quản trị CSDL khác hoặc Instance khác. Lệnh OpenRowSet. 190
  44. Lệnh OpenRowSet sử dụng truy nhập dữ liệu xa với nguồn dữ liệu là OLE DB, kết nối kiểu này có thể thực hiện các lệnh Insert, Update, Delete, Select với bảng dữ liệu. Quyền thực hiện trong câu lệnh thực hiện theo user kết nối trong câu lệnh. + OPENROWSET với lệnh SELECT và Microsoft OLE DB Provider for SQL Server. USE pubs GO SELECT a.* FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass', 'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a GO Ví dụ trên thực hiện kết nối đến Instance có tên seattle1, user có tên sa, mật khẩu MyPass. + OPENROWSET với OLE DB Provider for ODBC. USE pubs GO SELECT a.* FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass', pubs.dbo.authors) AS a ORDER BY a.au_lname, a.au_fname GO + Microsoft OLE DB Provider for Jet. Lệnh dạng này được này được thực hiện kết nối đến Access. USE pubs GO SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 191
  45. 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'myp wd', Orders) AS a GO + OPENROWSET với INNER JOIN một bảng khác. USE pubs GO SELECT c.*, o.* FROM Northwind.dbo.Customers AS c INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'myp wd', Orders) AS o ON c.CustomerID = o.CustomerID GO Lệnh OpenDataSource. Lệnh OpenDataSource thực hiện mở dữ liệu ngoài Instance, không cần đến linked_server. + Kết nối đến Instance khác. SELECT * FROM OPENDATASOURCE( 'SQLOLEDB', 'Data Source=ServerName;User ID=MyUID;Password=MyPass' ).Northwind.dbo.Categories + Kết nối đến Excel. 192
  46. SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0') xactions Lệnh OpenQuery. Lệnh OpenQuery thực hiện thao tác với dữ liệu ngoài thông qua LinkedServer. EXEC sp_addlinkedserver 'OracleSvr', 'Oracle 7.3', 'MSDAORA', 'ORCLDB' GO SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles') GO MỘT SỐ HÀM CƠ BẢN. Hàm hệ thống. DB_ID Trả về ID của CSDL khi biết tên. DB_NAME Trả về tên CSDL khi biết ID. HOST_ID Trả về ID của máy chủ. HOST_NAME Trả về tên máy chủ SUSER_ID Trả về ID User của Server khi biết tên SUSER_NAME Trả về tên User của Server khi biết ID. USER_ID Trả về ID User khi biết tên USER_NAME Trả về tên User khi biết ID 193
  47. Hàm thao tác với chuỗi. + SUBSTRING - Lấy chuỗi nhỏ trong chuỗi. SUBSTRING ( expression , start , length ) Sử dụng với chuỗi ký tự: USE pubs SELECT au_lname, SUBSTRING(au_fname, 1, 1) FROM authors ORDER BY au_lname Sử dụng với text, ntext, image: USE pubs SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo, SUBSTRING(pr_info, 1, 10) AS pr_info FROM pub_info WHERE pub_id = '1756' + CHARINDEX – Trả về vị trí bắt đầu một mẫu trong chuỗi. CHARINDEX ( expression1 , expression2 [ , start_location ] ) – Tìm vị trí xuất hiện chuỗi expression1 trong expression2. Ví dụ tìm chuỗi ‘wonderful’ trong cột notes của bảng titles: USE pubs GO SELECT CHARINDEX('wonderful', notes) FROM titles WHERE title_id = 'TC3218' GO 194
  48. + PATINDEX – Trả về vị trí xuất hiện của mẫu trong chuỗi. PATINDEX ( '%pattern%' , expression ) Ví dụ trìm vị trí xuất hiện mẫu ‘%wonderful%’: USE pubs GO SELECT PATINDEX('%wonderful%', notes) FROM titles WHERE title_id = 'TC3218' GO Ví dụ trìm vị trí xuất hiện mẫu ‘%won_erful%’: USE pubs GO SELECT PATINDEX('%won_erful%', notes) FROM titles WHERE title_id = 'TC3218' GO + STR – Chuyển dữ liệu kiểu số sáng chuỗi. STR ( float_expression [ , length [ , decimal ] ] ) Ví dụ chuyển số sang chuỗi có độ dài 6, làm tròn sau dấu phảy 1 số. SELECT STR(123.45, 6, 1) GO Kết quả là chuỗi ‘123.5’ Ví dụ sử dụng với hàm Floor lấy giá trị nguyên nhỏ hơn của một số thực: 195
  49. SELECT STR (FLOOR (123.45), 8, 3) GO Kết quả là ‘123.000’ + STUFF – Chèn một chuỗi vào một chuỗi khác. Hàm Stuff thực hiện xóa chuỗi nhỏ trong một chuỗi sau đó thực hiện chèn một chuỗi mới vào vị trí bắt đầu. STUFF ( character_expression , start , length , character_expression ) Ví dụ: SELECT STUFF('abcdef', 2, 3, 'ijklmn') GO Kết quả thực hiện: aijklmnef + SOUNDEX – Trả về hàm phát âm. Hàm Soundex sử dụng so sánh phát âm giữa 2 chuỗi, ví dụ sau sẽ cho 2 mã Soundex như nhau: SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') Kết quả thực hiện: S530 S530 + Defference – So sánh giá trị hàm Soundex giữa 2 chuỗi: Giá trị trả về từ 0 đến 4, 4 là giá trị giống nhau nhất. Ví dụ sau so sánh giữa 2 chuỗi: SELECT DIFFERENCE('Smithers', 'Smythers') GO Kết quả thực hiện: 4 196
  50. SELECT DIFFERENCE('Anothers', 'Brothers') GO Kết quả thực hiện: 2 + UNICODE – Lấy mã unicode ký tự đầu tiên trong chuỗi. + NCHAR – Chuyển mã unicode thành ký tự. Các hàm DateTime. + GETDATE: Trả về ngày, giờ hiện tại. + DATEPART: Trả về giá trị ngay hoặc tháng hoặc năm của một biểu thức ngày. DATEPART ( datepart , date ) Giá trị datepart theo bảng sau: Datepart Dạng rút gọn year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw hour hh minute mi, n second ss, s millisecond ms SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0) + SET DATFIRST: Đặt ngày đầu tiên trong tuần. + SET DATEFORMAT: Đặt định dạng kiểu DateTime để nhập dữ liệu. 197
  51. SET DATEFORMAT mdy GO DECLARE @datevar datetime SET @datevar = '12/31/98' SELECT @datevar GO + DAY, MONTH, YEAR: Lấy giá trị ngày, tháng, năm. + ISDATE: Kiểm tra xem dữ liệu có hợp lệ DateTime không. + DATEDIFF: Xác định độ lệch giữa 2 giá trị DateTime. DATEDIFF ( datepart , startdate , enddate ) Ví dụ: Xác định số ngày đã phát hành sách. USE pubs GO SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days FROM titles GO + DATEADD – Xác định giá DateTime mới khi thay đổi một khoảng thời gian. DATEADD ( datepart , number, date ) USE pubs GO SELECT DATEADD(day, 21, pubdate) AS timeframe FROM titles GO Các hàm chuyển đổi. + CONVERT + CAST 198
  52. TRANSACTION – PHIÊN GIAO DỊCH. Transaction là một đơn vị công việc trong nó bao gồm nhiều việc nhỏ, các việc này được thực hiện thành công thì Transaction thành công, dữ liệu thay đổi trong quá trình thực hiện của Transaction sẽ được cập nhật. Nếu trong quá trình có phát sinh lỗi thì Transaction sẽ lặp lại (Roll Back hoặc Cancel), dữ liệu không được cập nhật. Một phiên giao dịch có 4 đặc tính ACID (Atomicity, Consistency, Isolation, Durability). Atomicity – Nguyên tố: Một phiên giao dịch là một đơn vị công việc nhỏ nhất, tất cả dữ liệu thay đổi trong phiên giao dịch được thực hiện hoặc tất cả không được thực hiện. Consistency- Nhất quán: Giao dịch sẽ không thực hiện nếu có một thao tác xung khắc về mặt logic hoặc quan hệ. Tính nhất quán rất quan trọng với mô hình ứng dụng client/server, với mô hình dạng này tại một thời điểm có thể có nhiều giao dịch thực hiện đồng thời, nếu một giao dịch nào đó không nhất quán thì tất cả các giao dịch khác sẽ thực hiện sai, dẫn đến sự vi phạm toàn vẹn dữ liệu. Isolation – Tách biệt: Tại một thời điểm có nhiều phiên giao dịch đồng thời, các phiên giao dịch chỉ tác động với nhau khi dữ liệu được cập nhật (kết thúc phiên). Giả sử có 2 phiên giao dịch có tác động Durability - Bền vững: Sau khi giao dịch hoàn tất, dữ liệu ở trạng thái bền vững. Một phiên giao dịch được xác định bắt đầu, kết thúc: Bắt đầu phiên giao dịch. Phiên giao dịch có 3 loại: explicit transaction, implicit transaction, autocommit transaction. Explicit transaction: Là kiểu phiên giao dịch rõ, được bắt đầu bằng lệnh BEGIN TRANSACTION, đối với phiên giao dịch phân tán thì được bắt đầu bằng lệnh BEGIN DISTRIBUTED TRAN [ transaction_name | @tran_name_variable ] Đặt tên giao dịch: 199
  53. DECLARE @TranName VARCHAR(20) SELECT @TranName = 'MyTransaction' BEGIN TRANSACTION @TranName GO USE pubs GO UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE 'Pc%' GO COMMIT TRANSACTION MyTransaction GO Đánh dấu trong giao dịch: BEGIN TRANSACTION RoyaltyUpdate WITH MARK 'Update royalty values' GO USE pubs GO UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE 'Pc%' GO COMMIT TRANSACTION RoyaltyUpdate GO Autocommit transaction: Mỗi câu lệnh tự cập nhật dư liệu khi nó kết thúc, không cần câu lệnh điều khiển phiên giao dịch. Implicit transaction: Là phiên giao dịch ẩn, đặt chế độ này thông qua hàm API hoặc lệnh SET IMPLICIT_TRANSACTIONS ON. Khi phiên giao dịch kết thúc, câu lệnh T-SQL tiếp theo sẽ khởi động phiên giao dịch mới. SET IMPLICIT_TRANSACTIONS { ON | OFF } 200
  54. Sử dụng kết hợp với Implicit transaction: USE pubs GO CREATE table t1 (a int) GO INSERT INTO t1 VALUES (1) GO PRINT 'Use explicit transaction' BEGIN TRAN INSERT INTO t1 VALUES (2) SELECT 'Tran count in transaction'= @@TRANCOUNT COMMIT TRAN SELECT 'Tran count outside transaction'= @@TRANCOUNT GO PRINT 'Setting IMPLICIT_TRANSACTIONS ON' GO SET IMPLICIT_TRANSACTIONS ON GO PRINT 'Use implicit transactions' GO No BEGIN TRAN needed here. INSERT INTO t1 VALUES (4) SELECT 'Tran count in transaction'= @@TRANCOUNT COMMIT TRAN SELECT 'Tran count outside transaction'= @@TRANCOUNT GO PRINT 'Use explicit transactions with IMPLICIT_TRANSACTIONS ON' GO BEGIN TRAN INSERT INTO t1 VALUES (5) SELECT 'Tran count in transaction'= @@TRANCOUNT COMMIT TRAN SELECT 'Tran count outside transaction'= @@TRANCOUNT GO 201
  55. SELECT * FROM t1 GO Need to commit this tran too! DROP TABLE t1 COMMIT TRAN GO Kết thúc phiên giao dịch. Sử dụng lệnh Commit trong phiên giao dịch. COMMIT [ TRAN [ SACTION ] [ transaction_name | @tran_name_variable ] ] + Commit một phiên giao dịch. BEGIN TRANSACTION USE pubs GO UPDATE titles SET advance = advance * 1.25 WHERE ytd_sales > 8000 GO COMMIT GO + Commit nhiều phiên giao dịch lồng nhau. CREATE TABLE TestTran (Cola INT PRIMARY KEY, Colb CHAR(3)) GO BEGIN TRANSACTION OuterTran @@TRANCOUNT set to 1. GO INSERT INTO TestTran VALUES (1, 'aaa') GO BEGIN TRANSACTION Inner1 @@TRANCOUNT set to 2. GO INSERT INTO TestTran VALUES (2, 'bbb') GO BEGIN TRANSACTION Inner2 @@TRANCOUNT set to 3. GO 202
  56. INSERT INTO TestTran VALUES (3, 'ccc') GO COMMIT TRANSACTION Inner2 Decrements @@TRANCOUNT to 2. Nothing committed. GO COMMIT TRANSACTION Inner1 Decrements @@TRANCOUNT to 1. Nothing committed. GO COMMIT TRANSACTION OuterTran Decrements @@TRANCOUNT to 0. Commits outer transaction OuterTran. GO Hủy bỏ và quay lại phiên giao dịch. Sử dụng lệnh RollBack Transaction hủy bỏ những thực hiện và quay lại phiên giao dịch. ROLLBACK [ TRAN [ SACTION ] [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ] ] LOCK – KHÓA. Khi 2 hay nhiều người cùng truy nhập đồng thời một CSDL, SQL Server sử dụng khoá để xác định hoạt động cho một người và không xác didnhj cho người khác. Khoá là việc ngăn không cho những người đọc dữ liệu mà không bị người khác sử đổi. Hầu hết SQL Server đều khoá tự động, bạn có thể thiết tiết CSDL một cách có hiệu quả hơn bằng việc tìm hiểu về khoá và chọn khoá cho ứng dụng của bạn. Tìm hiểu về khoá. Khoá gồm các loại sau: Kiểu khoá Mô tả Shared Là khoá không làm thay đổi, ghi dữ liệu, dùng cho lệnh Select Update Khoá hoặc cho phép sửa đổi dữ liệu Exclusive Khoá với các thao tác Update, Insert, Delete Một số phạm vi khoá như sau: 203
  57. Tên Mô tả Page Trang dữ liệu 2K hoặc trang chỉ mục Index, thường được dùng Extent Nhóm các trang có kích thước 8k, chỉ dùng với trường hợp xác định Table Cả bảng dữ liệu, gồm dữ liệu và index Intent Là kiểu đặc biệt để đặt kiểu khoá của trang hiện tại trên bảng Bảng xác định hiệu lực của các kiểu khoá Shared Update Exclusive Shared Yes Yes No Update Yes No No Exclusive No No No Ví dụ: Khi đặt chế độ khoá là Exclusive thì những phiên giao dịch khác không thể yêu cầu bất cứ loại khoá nào đến khi hoá Exclusive bị bỏ. Xem thông tin về khoá. Để xem thông tin về khoá đạng sử dụng trong SQL Server ta làm như sau: Chọn đối tượng cần xem khoá Thực hiện thủ tục sp_lock Chọn kiểu khoá. Khoá đựoc đặt trong các câu lệnh như: SELECT, INSERT, UPDATE, và DELETE , sau đây là bảng mô tả các kiểu khoá đối với phương thức nói trên Tên Mô tả NOLOCK Được sử dụng với câ lệnh Select, người đoc có thể đọc dữ liệu khi dữ liệu gốc khi chưa được ghi dữ liệu mới trong giao dịch đang sử dụng HOLDLOCK Khoa Shared được giữ đến khi phiên giao dịch được hoàn tất khi khoá chưa được giải phóng UPDLOCK Dùng để cập nhật dữ liệu của kiểu khoá Shared trong quá trình đọc bảng dữ liệu và được giữ đến khi kết thúc lệnh của phiên giao dịch. Khoa này dùng khi cập nhật dữ liệu, ngăn không cho người khác đọc đến khi phiên giao dịch cập nhật được hoàn tất TABLOCK Dùng khoá Shared trên một bảng dữ liệu, cho phép những người khác đọc dữ liệu nhưng ngăn không cho cập nhật PAGELOCK sử dụng kiểu khoá Shared phạm vi trang dữ liệu (Page), đây là 204
  58. loại khoá ngầm định TABLOCKX Dùng kiểu khoá Exlusive trên một bảng dữ liệu, ngăn người khác đọc và cập nhật dữ liệu từ bảng và giữ đến khi kết thúc lệnh, phiên giao dịch Cách đặt khoá như sau: Dùng lệnh SET Ví dụ 1: USE pubs GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION SELECT au_lname FROM authors WITH (NOLOCK) GO Vớ d? 2: Select * from authors(UPDLOCK) Đặt mức khoá. Dùng đặt, điều khiển khoá trong các giao dịch của SQL Server Cú pháp: SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE } Trong đó: o Read Committed: Dùng kiểu khoá Shared trong quá trình đọc dữ liệu o Read Uncommitted: Không đặt khoá Shared và khoá Exclusive, có thể đọc dữ liệu gốc khi đang có phiên giao địch sửa đổi dữ liệu o RepeateTable Read: Khoá tất cả dữ liệu đang được sử dụng trong truy vấn, ngăn những người khác sửa dữ liệu nhưng người khác có thể chèn thêm dữ liệu mới vào bảng (hàng mới) o Serializable: Đặt khoá trong một tập dữ liệu (khoá phạm vi) ngăn không cho người khác có thể sửa, thêm hàng mới vào tập dữ liệu đến khi giao dịch kết thúc, tương ự như HoldLock trong lệnh Select Ví dụ: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 205
  59. GO BEGIN TRANSACTION SELECT * FROM publishers SELECT * FROM authors COMMIT TRANSACTION Khoá chết (DeadLock). Trong hệ quản trị CSDL quan hệ nói rieneg và các hệ quản trị khác nói chung, việc xuất hiện nhiều luồng dữ liệu đồng thời trong CSDL là thường xuyên xảy ra, một giao dịch có thể lấy dữ liệu từ nhiều nguồn dữ liệu khác nhau, hai giao dịch trong cùng CSDL có thể cùng chung một nguồn dữ liệu nào đó nên việc các giao dịch này đặt các mức khoá khác nhau cho các nguồn dữ liệu mà no năm giữ là không thể tránh khỏi, ví dụ tên sơ đồ sau mô tả sự giao chéo về nguồn dữ liệu trong giao dịch Trong giao dịch 1 và 2 đều đặt các bảng dữ liệu ở mức khoá Exlusive, như vậy giao dịch 1 chỉ thực hiện được khi giao dịch 2 thực hiện xong hoặc quay lại trạng thái ban đầu, ngược lại giao dịch 2 cũng chừo giao dịch 1 thực hiện xong hoặc quay lại trạng thai ban đầu. Cứ như vây thì cả 2 giao dịch sẽ không bao giơ kết thúc được phiên giao dịch của mình. Phần chung của khoá nói trên gọi là khoá chết, và được khoá theo khối (block). GRAND – GÁN QUYỀN. Lệnh Grand thực hiện gán quyền cho user hoặc role của SQL Server. Người thực hiện Grand phải có quyền được thực hiện phân quyền cho user. Có 2 hình thức gán quyền: gán quyền thực hiện câu lệnh, gán quyền thao tác với đối tượng. Gán quyền thao tác câu lệnh. 206
  60. GRANT { ALL | statement [ , n ] } TO security_account [ , n ] Các câu lệnh: CREATE DATABASE CREATE DEFAULT CREATE FUNCTION CREATE PROCEDURE CREATE RULE CREATE TABLE CREATE VIEW BACKUP DATABASE BACKUP LOG SQL Server ngầm định một số nhóm có quyền thực hiện câu lệnh như sau: dbcreator processadmin securityadmin serveradmin bulkadmin ALTER X DATABASE CREATE X DATABASE BULK INSERT X DBCC X (1) DENY X (2) GRANT X (2) KILL X RECONFIGURE X RESTORE X REVOKE X (2) SHUTDOWN X 207
  61. db_ db_ db_ db_ db_ db_ backup security owner datareader datawriter ddladmin operator admin ALTER DATABASE X X ALTER FUNCTION X X ALTER PROCEDURE X X ALTER TABLE X (1) X ALTER TRIGGER X X ALTER VIEW X (1) X BACKUP X X CHECKPOINT X X CREATE DEFAULT X X CREATE FUNCTION X X CREATE INDEX X (1) X CREATE PROCEDURE X X CREATE RULE X X CREATE TABLE X X CREATE TRIGGER X (1) X CREATE VIEW X X DBCC X X (2) DELETE X (1) X DENY X X DENY on object X DROP X (1) X EXECUTE X (1) GRANT X X GRANT on object X (1) INSERT X (1) X READTEXT X (1) X REFERENCES X (1) X RESTORE X REVOKE X X REVOKE on object X (1) 208
  62. SELECT X (1) X SETUSER X TRUNCATE TABLE X (1) X UPDATE X (1) X UPDATE STATISTICS X (1) UPDATETEXT X (1) X WRITETEXT X (1) X Các user được gán quyền có thể là user của SQL Server hoặc user của Windows NT. Ví dụ gán quyền thao tác câu lệnh cho 3 user (trong đó có 2 user của SQL Server và 1 user của Windows NT): GRANT CREATE DATABASE, CREATE TABLE TO Mary, John, [Corporate\BobJ] Ví dụ gán quyền thao tác cho role và user: USE pubs GO GRANT SELECT ON authors TO public GO GRANT INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom GO Gán quyền thao tác đối tượng. Là việc gán quyền cho các user hoặc role có quyền thao tác với các đối tượng của SQL Server. Ví dụ gán quyền thao tác cho Role: 209
  63. GRANT CREATE TABLE TO Accounting Ví dụ gán quyền để gán quyền thao tác cho user khác: Ví dụ Jean là dbo của bảng Plan_data, Jean thực hiện gán quyền với chức năng GRAND_OPTION cho role accounting, Jill thuộc role nói trên và Jill gán quyền được Select cho Jack, Jack không là thành viên của Accounting. /* User Jean */ GRANT SELECT ON Plan_Data TO Accounting WITH GRANT OPTION /* User Jill */ GRANT SELECT ON Plan_Data TO Jack AS Accounting Thủ cục sp_grandlogin. Là thủ tục thực hiện gán quyền truy nhập cho user của Windows NT hoặc nhóm user của Windows NT. sp_grantlogin [@loginame =] 'login' Ví dụ gán quyền truy nhập SQL Server cho BobJ. EXEC sp_grantlogin 'Corporate\BobJ' Thủ tục sp_grandaccess. Gán quyền khai thác cho user của SQL Server hoặc Windows NT. sp_grantdbaccess [@loginame =] 'login' [,[@name_in_db =] 'name_in_db' [OUTPUT]] Ví dụ gán quyền khai thác cho user của Windows và lấy theo tên mới. 210
  64. EXEC sp_grantdbaccess 'Corporate\GeorgeW', 'Georgie' REVOKE – TƯỚC QUYỀN. Revoke là câu lệnh tước quyền khai thác của user. Tước quyền được thực hiện câu lệnh. REVOKE { ALL | statement [ , n ] } FROM security_account [ , n ] Ví dụ tước quyền khai thác với 2 user: REVOKE CREATE TABLE FROM Joe, [Corporate\BobJ] Ví dụ tước quyền khai thác 2 câu lệnh với các user: REVOKE CREATE TABLE, CREATE DEFAULT FROM Mary, John Tước quyền khai thác của user với đối tượng. Ví dụ tước quyền thực hiện lệnh Select trong role Budget_data đối với Mary: REVOKE SELECT ON Budget_Data TO Mary DENY – TỪ CHỐI QUYỀN. Là câu lệnh từ chối quyền đối với user, user chỉ thực hiện được quyền khi có chỉ định rõ ràng. Ví dụ từ chối quyền thực hiện lệnh với các user: DENY CREATE DATABASE, CREATE TABLE TO Mary, John, [Corporate\BobJ] 211
  65. Ví dụ gán quyền khai thác cho role, sau đó thực hiện từ chối thực hiện của các user trong role: USE pubs GO GRANT SELECT ON authors TO public GO DENY SELECT, INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom Ví dụ từ chối quyền của role: DENY CREATE TABLE TO Accounting TRỢ GIÚP. Trong quá trình thực hiện soạn lệnh T-SQL bạn có thể thực hiện tra cứu lệnh trong Book Online. 212
  66. Phần 3. PHÁT TRIỂN ỨNG DỤNG VỚI SQL SERVER Trong phần này ta sẽ xem xét kỹ thuật phát triển ứng dụng với SQL Server từ các ngôn ngữ lập trình (Visual Basic, C++, VBScript, ). Các ứng dụng khai thác CSDL của SQL Server thực hiện các bước sau: + Kết nối từ ứng dụng đến SQL Server. + Xây dựng cơ sở dữ liệu. + Thực hiện các lệnh khai thác hoặc thủ tục của SQL Server. + Khai thác dữ liệu thông qua công cụ có sẵn. + Ngắt kết nối. GIỚI THIỆU. Thiết kế ứng dụng là việc thực hiện tạo giao diện (API – Application Program Interface) giao tiếp với SQL Server, việc thực hiện kết nối thực hiện thông qua các công cụ ADO, URL, OLE DB, ODBC, Embedded SQL for C, DB-Library. Khi sử dụng các công cụ kết nối dữ liệu thao tác dưới dạng bảng hoặc dạng tài liệu XML. + Dữ liệu dưới dạng bảng được thực hiện thông qua các công cụ kết nối ADO, OLE DB, ODBC, Embedded SQL for C, DB-Library. + Dữ liệu thực hiện thông qua tài liệu XML thông qua các công cụ ADO, URL, OLE DB. KẾT NỐI VỚI SQL SERVER BẰNG ADO. ADO viết tắt của cụm từ ActiveX Data Object là công cụ giao tiếp với dữ liệu của nhiều hệ quản trị CSDL khác nhau, SQL Server là một ví dụ cho việc giao tiếp. ADO sử dụng với CSDL quan hệ hoặc sử dụng với CSDL đa chiều, khi đó gọi là ADO MD (ADO Multi Dimention). ADO sử dụng kết nối kiểu OLE DB hoặc các thư viện kết nối COM (Component Object Model). 213
  67. OLE DB sử dụng 2 phương thức Microsoft OLE DB Provider for SQL Server (SQLOLEDB) và Microsoft OLE DB Provider for ODBC (MSDASQL). ADO có thể thực hiện từ các ngôn ngữ lập trình Visual Basic, ASP, C++. Cấu trúc ứng dụng sử dụng ADO. ADO gồm các thành phần cơ bản sau: Application, ADO, OLE DB Provider, Data Source. Thành phần Chức năng Application Gọi các đối tượng, thành phần, phương thức và các thuộc tính của ADO. Thông qua các thành phần này ứng dụng sẽ gửi các câu lệnh SQL và nhận kết quả xử lý. ADO Quản lý việc trao đổi dữ liệu giữa ứng dụng và OLE DB OLE DB Xử lý các lệnh gọi từ ứng dụng qua ADO, kết nối với Data provider Source. Processes all ADO calls from the application, connects to a data source, passes SQL statements from the application to the data source, and returns results to the application. Data source Contains the information used by a provider to access a specific instance of data in a DBMS. Khi thực hiện lập trình ứng dụng với SQL Server sử dụng ADO, người lập trình phải thực hiện các thao tác sau: + Kết nối đến nguồn dữ liệu (data source). + Gửi câu lệnh SQL đến nguồn dữ liệu. + Xử lý kết quả nhận được từ câu lệnh đã gửi. + Xử lý các lỗi và thông báo. + Ngắt kết nối đến nguồn dữ liệu. Đối với một số ứng dụng phức tạp sử dụng ADO có thể sử dụng một số thao tác sau: + Sử dụng con trỏ (cursor) để điều khiển vị trí trong tập kết quả. + Thực hiện thủ tục lưu trữ trên Server. + Thực hiện hàm tự định nghĩa trên Server. + Quản lý các phép truy vấn mà có nhiều tập kết quả. 214
  68. + Yêu cầu kết thúc hoặc lặp lại một phiên giao dịch. + Quản lý các thao tác với kiểu dữ liệu lớn (text, image). + Thực hiện các thao tác với XML sử dụng phép truy vấn XPath. Kết nối đến SQL Server. Để kết nối đến SQL Server, các công việc cơ bản cần thực hiện như sau: + Cấu hình kết nối. + Thiết lập hoặc ngắt kết nối đến nguồn dữ liệu. + Xác định OLE DB provider. + Thực hiện truy vấn. + Quản lý các phiên làm việc trên kết nối. Khi sử dụng SQLOLEDB ta phải thực hiện đặt các thuộc tính sau cho kết nối: + Initial Catalog: Xác định CSDL. + Data Source: Xác định tên Server. + Integrated Security: Xác định chế độ xác thực, nếu là SSPI chế độ xác thực là Windows Authentication, hoặc xác định User ID, Password của chế độ xác thực SQL Server Authentication. Ví dụ thực hiện kết nối đến SQL Server đặt từng thuộc tính tiêng biệt từ Visual Basic: ' Initialize variables. Dim cn As New ADODB.Connection . . . Dim ServerName As String, DatabaseName As String, _ UserName As String, Password As String ' Put text box values into connection variables. ServerName = txtServerName.Text DatabaseName = txtDatabaseName.Text 215
  69. UserName = txtUserName.Text Password = txtPassword.Text ' Specify the OLE DB provider. cn.Provider = "sqloledb" ' Set SQLOLEDB connection properties. cn.Properties("Data Source").Value = ServerName cn.Properties("Initial Catalog").Value = DatabaseName ' Decision code for login authorization type: ' Windows NT or SQL Server authentication. If optWinNTAuth.Value = True Then cn.Properties("Integrated Security").Value = "SSPI" Else cn.Properties("User ID").Value = UserName cn.Properties("Password").Value = Password End If ' Open the database. cn.Open Ví dụ kết nối đến SQL Server sử dụng chuỗi kết nối: ' Initialize variables. Dim cn As New ADODB.Connection Dim provStr As String ' Specify the OLE DB provider. cn.Provider = "sqloledb" ' Specify connection string on Open method. ProvStr = "Server=MyServer;Database=northwind;Trusted_Connection= yes" cn.Open provStr Ví dụ kết nối sử dụng ODBC: 216
  70. Dim cn As New ADODB.Connection cn.ConnectionTimeout = 100 ' DSN connection. You can use variables for the parameters. cn.Open "MyDataSource", "sa", "MyPassword" ' Alternative syntax follows: ' cn.Open "DSN=DataSourceName;UID=sa;PWD=Password;" cn.Close Ví dụ kết nối xác định Driver của SQL Server: Dim cn As New ADODB.Connection ' Connection to SQL Server without using ODBC data source. cn.Open "Driver={SQL Server};Server=Server1;Uid=SA;Pwd=;Database=northwind" cn.Close Thực hiện truy vấn. Thực hiện truy vấn sử dụng đối tượng Command. cmd.Execute(NumRecords, Parameters, Options) Đối tượng Command có thể thực hiện nhiều kiểu câu lệnh (Select, Update, Insert, Delete, Create, Drop), đối với lệnh Select kết quả thực hiện là một recordset. Set rs = cmd.Execute(NumRecords, Parameters, Options) Kiểu lệnh thực hiện trong Command được xác định theo option của lệnh, gồm một số kiểu sau: Tên kiểu Mô tả adCmdFile Tên file chứa đối tượng recordset 217
  71. adCmdStoreProc Stored procedure adCmdTable Tên bảng adCmdTableDirect Tên bảng mà các cột được truy vấn adCmdText Câu lệnh SQL adCmdUnknown Chưa xác định adCmdUnspecified Chưa xác định tham số cho lệnh Thực hiện truy vấn thông qua đối tượng connection. Dim cn As New ADODB.Connection . . . Dim rs As New ADODB.Recordset cmd1 = txtQuery.Text Set rs = cn.Execute(cmd1) Thực hiện truy vấn có sử dụng tham số. Khi thực hiện các thủ tục có tham số truyền vào các ứng dụng phải truyền tham số, trong phần này sẽ giới thiệu một ví dụ sử dụng đối tượng parameter. Tạo thủ tục: USE NORTHWIND GO drop proc myADOParaProc GO CREATE PROC myADOParaProc @categoryid int(4) AS SELECT * FROM products WHERE categoryid = @categoryid GO Sử dụng đối tượng parameter truyền tham số là số nguyên xác định categoryID: Dim cn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset Dim prm As ADODB.Parameter 218
  72. Dim fld As ADODB.Field Dim provStr As String ' Connect using the SQLOLEDB provider. cn.Provider = "sqloledb" ' Specify connection string on Open method. provStr = "Server=MyServer;Database=northwind;Trusted_Connection=yes" cn.Open provStr ' Set up a command object for the stored procedure. Set cmd.ActiveConnection = cn cmd.CommandText = "myADOParaProc" cmd.CommandType = adCmdStoredProc cmd.CommandTimeout = 15 ' Set up a new parameter for the stored procedure. Set prm = Cmd.CreateParameter("CategoryID", adInteger, adParamInput, 4, 7) Cmd.Parameters.Append prm ' Create a recordset by executing the command. Set rs = cmd.Execute Set Flds = rs.Fields ' Print the values for all rows in the result set. While (Not rs.EOF) For Each fld in Flds Debug.Print fld.Value Next Debug.Print "" rs.MoveNext Wend ' Close recordset and connection. rs.Close cn.Close Đối tượng Recordset. Sử dụng đối tượng Recordset lưu trữ kết quả của lệnh Select. 219
  73. Dim cn As New ADODB.Connection Dim rs As ADODB.Recordset . . . cmd1 = txtQuery.Text Set rs = New ADODB.Recordset rs.Open cmd1, cn rs.MoveFirst . . . ' Code to loop through result set(s) Đối tượng Field. Sử dụng đối tượng field là các cột của Recordset, thông qua nó ta có thể láy giá trị, thuộc tính của cột. Dim rs As New ADODB.Recordset Dim fld As ADODB.Field Dim cn As ADODB.Connection Dim cmdText As String cn.Provider = "sqloledb" cn.Properties("Data Source").Value = "MyServerName" cn.Properties("Initial Catalog").Value = "northwind" cn.Properties("Integrated Security").Value = "SSPI" cn.Open cmdText = "select * from authors" rs.Open cmdText, cn Set Flds = rs.Fields Dim TotalCount As Integer TotalCount = Flds.Count For Each fld In Flds Debug.Print fld.Name Debug.Print fld.Type Debug.Print fld.Value Next rs.Close Sử dụng con trỏ. 220
  74. Khi sử dụng đối tượng Recordset của ADO, ta có thể sử dụng nhiều kiểu con trỏ khác nhau xác định kiểu khóa, điều khiển vị trí, Dim rs As New ADODB.Recordset . . . rs.Open "SELECT * FROM titles", , adOpenDynamic, adLockOptimistic rs.Close COn trỏ nói trên gồm những thuộc tính cơ bản sau: CursorType, CursorLocation, LockType, CacheSize. Thuộc tính Mô tả CursorType - adOpenForwardOnly: Ngầm định Xác định kiểu con trỏ được sử dụng: - adOpenForwardOnly: Chỉ đọc, chỉ có thể cập nhật dữ liệu trên hàng dữ liệu hiện thời. - adOpenStatic: Trạng thái tĩnh, khi mở kiểu này hệ thống sẽ cung cấp một ảnh dữ liệu (snapshot), dữ liệu thay đổi trên bảng cơ sở sẽ không được thể hiện trên snapshot dạng này. - adOpenKeyset: Theo vị trí tùy chọn, khi di chuyển hàng cập nhật con trỏ kiểu này sẽ chiếu đến hàng dữ liệu cơ sở, hàng dữ liệu được khó và bạn có thể cập nhật, lấy dữ liệu từ hàng cơ sở. - adOpenDynamic: Động, con trỏ kiểu này gần giống keyset cursor , nhưng con trỏ kiểu này phản ảnh những thay đổi trên bảng cơ sở. CursorLocation -adUseServer: Ngầm định. - adUseClient: Nếu đặt là ta chỉ có thể mở ở trạng thái tĩnh. LockType -adLockReadOnly: Ngầm định. Xác định kiểu khóa trong quá trình cập nhật dữ liệu (adLockPessimistic, adLockOptimistic, adLockBatchOptimistic). CacheSize Ngầm định: 1 Xác định số hàng đặt trong bộ đệm hoặc đọc trong một thời điểm. Các phương thức dịch chuyển hàng dữ liệu. 221
  75. Khi sử dụng đối tượng Recordset bạn có thể dịch chuyển vị trí của hàng dữ liệu bằng các phương thức MoveFirst, MoveLast, MoveNext, MovePrevious. Đánh dấu vị trí theo phương thức Bookmark, phương thức clone để tạo một bản sao recordset. Quản lý phiên làm việc. Trong phần câu lệnh T-SQL ta đã xem xét việc điều khiển một hpiên làm việc (transaction), tuy nhiên ta có thể sử dụng đối tượng connection của ADO để điều khiển trực tiếp phiên làm việc như trong kịch bản lệnh nói trên bằng việc sử dụng các phương thức BeginTrans, CommitTrans, RollbackTrans. Xét ví dụ sau: Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset . . . ' Open connection. cn.Open ' Open titles table. rs.Open "SELECT * FROM titles", Cn, adOpenDynamic, adLockPessimistic . . . ' Begin the transaction. rs.MoveFirst cn.BeginTrans ' User loops through the recordset making changes. . . . ' Ask if the user wants to commit all the changes made. If MsgBox("Save all changes?", vbYesNo) = vbYes Then cn.CommitTrans Else cn.RollbackTrans End If Thực hiện các lệnh DDL. Để thực hiện các lệnh DDL như CREATE TABLE, DROP TABLE, ALTER TABLE. Bạn có thể sử dụng đối tượng command của ADO, xét ví dụ sau: Dim Cn As New ADODB.Connection 222
  76. Dim Cmd As New ADODB.Command ' If the ADOTestTable does not exist, go to AdoError. On Error GoTo AdoError ' Connect using the SQLOLEDB provider. cn.Provider = "sqloledb" cn.Properties("Data Source").Value = "MyServerName" cn.Properties("Initial Catalog").Value = "northwind" cn.Properties("Integrated Security").Value = "SSPI" cn.Open ' Set up command object. Set Cmd.ActiveConnection = Cn Cmd.CommandText = "DROP TABLE ADOTestTable" Cmd.CommandType = adCmdText Cmd.Execute Done: Cmd.CommandText = "SET NOCOUNT ON" Cmd.Execute Cmd.CommandText = "CREATE TABLE ADOTestTable (id int, name char(100))" Cmd.Execute Cmd.CommandText = "INSERT INTO ADOTestTable values(1, 'Jane Doe')" Cmd.Execute Cn.Close Exit Sub AdoError: Dim errLoop As Error Dim strError As String ' Enumerate Errors collection and display properties of ' each Error object. Set Errs1 = Cn.Errors For Each errLoop In Errs1 Debug.Print errLoop.SQLState Debug.Print errLoop.NativeError Debug.Print errLoop.Description 223
  77. Next GoTo Done End Sub Quản lý dữ liệu kiểu lớn – Text, image. Dữ liệu kiểu text, ntext, image là kiểu dữ liệu phức tạp, việc quản lý, khai thác không được thực hiện thông thường, ADO hỗ trợ các phương thức riêng để thực hiện. Thay vì độc, cập nhật dữ liệu trực tiếp thì dữ liệu kiểu này được thao tác theo đoạn (chunk) bằng cách sử dụng các phương thức AppendChunk, GetChunk. Trước khi thực hiện bạn phải đặt tham số bằng cách thực hiện lệnh sau: EXEC sp_dboption 'pubs', 'Select into/bulkcopy', 'True' Xét ví dụ sau trên CSDL Pubs: - Copy bảng pub_info sang bảng mới USE pubs SELECT * INTO pub_info_x FROM pub_info GO - Thực hiện chèn dữ liệu vào bảng: Public Sub AppendChunkX() Dim cn As ADODB.Connection Dim rstPubInfo As ADODB.Recordset Dim strCn As String Dim strPubID As String Dim strPRInfo As String Dim lngOffset As Long Dim lngLogoSize As Long Dim varLogo As Variant Dim varChunk As Variant Const conChunkSize = 100 224
  78. ' Open a connection. Set cn = New ADODB.Connection strCn = "Server=srv;Database=pubs;UID=sa;Pwd=;" cn.Provider = "sqloledb" cn.Open strCn 'Open the pub_info_x table. Set rstPubInfo = New ADODB.Recordset rstPubInfo.CursorType = adOpenDynamic rstPubInfo.LockType = adLockOptimistic rstPubInfo.Open "pub_info_x", cn, , , adCmdTable 'Prompt for a logo to copy. strMsg = "Available logos are : " & vbCr & vbCr Do While Not rstPubInfo.EOF strMsg = strMsg & rstPubInfo!pub_id & vbCr & _ Left(rstPubInfo!pr_info, InStr(rstPubInfo!pr_info, ",") - 1) & vbCr & vbCr rstPubInfo.MoveNext Loop strMsg = strMsg & "Enter the ID of a logo to copy:" strPubID = InputBox(strMsg) ' Copy the logo to a variable in chunks. rstPubInfo.Filter = "pub_id = '" & strPubID & "'" lngLogoSize = rstPubInfo!logo.ActualSize Do While lngOffset < lngLogoSize varChunk = rstPubInfo!logo.GetChunk(conChunkSize) varLogo = varLogo & varChunk lngOffset = lngOffset + conChunkSize Loop ' Get data from the user. strPubID = Trim(InputBox("Enter a new pub ID:")) strPRInfo = Trim(InputBox("Enter descriptive text:")) 225
  79. ' Add a new record, copying the logo in chunks. rstPubInfo.AddNew rstPubInfo!pub_id = strPubID rstPubInfo!pr_info = strPRInfo lngOffset = 0 ' Reset offset. Do While lngOffset Login SQL Server example <! 226
  80. .style1 { font-size: 24px; font-weight: bold; } > Login SQL Server   User name Password Server name    sub cmdLogin_Onclick 227
  81. frmlogin.method="post" frmlogin.action="connect.asp" frmlogin.submit end sub Tập tin connect.asp: Là tập tin được gọi từ form login.asp, thực hiện nhận tham số của form login.asp, kết nối đến SQL Server. <% dim username, password, servername, txt username=request.Form("txtUser") password = request.Form("txtPassword") servername=request.Form("txtServer") txt= "Provider=SQLOLEDB; " txt=txt & " Data Source=" & servername & ";" txt=txt & " Initial Catalog=pubs; " txt=txt & " User ID=" & username & ";" txt=txt & " PWD=" & password 228
  82. Set cn=Server.CreateObject("ADODB.Connection") cn.Open txt %> Kết nối thành công Liệt kê danh sách. Để liệt kê danh sách (có thể lấy dữ liệu bằng cách truy vấn trực tiếp hoặc thông qua khung nhìn - view) trước hết phải tạo một recordset lưu trữ kết quả truy vấn, từ recordset ta có thể lấy dữ liệu và đặt vào vị trí tương ứng cần thiết. + Khai báo Recordset: Set rs=Server.CreateObject("ADODB.Recordset") rs.ActiveConnection =cn 229
  83. rs.Source ="Select * from Authors" rs.Open + Lấy giá trị: Rs.fields(“au_id”) + Đóng Recordset: Rs.close + Ví dụ liệt kê danh sách bằng cách truy vấn trực tiếp: Danh sach <% dim username, password, servername, txt username="sa" 230
  84. password = "" servername="TDCong" txt= "Provider=SQLOLEDB; " txt=txt & " Data Source=" & servername & ";" txt=txt & " Initial Catalog=pubs; " txt=txt & " User ID=" & username & ";" txt=txt & " PWD=" & password Set cn=Server.CreateObject("ADODB.Connection") cn.Open txt Set rs=Server.CreateObject("ADODB.Recordset") rs.ActiveConnection =cn rs.Source ="Select * from Authors" rs.Open %> LIST OF AUTHORS   No au_id au_lname au_fname phone address city 0 then %> 231
  85. KẾT NỐI VỚI SQL SERVER BẰNG SQL-DMO. SQL DMO viết tắt của cụm từ SQL Distributed Management Objects, sử dụng thư viện liên kết động (dll) để kết nối đến SQL Server. SQL DMO thực hiện liên kết nhúng (OLE Automation ), các đối tượng SQL Server được thực hiện nhúng các đối tượng của SQL Server vào ứng dụng, khai thác các đối tượng thông qua thuộc tính, sự kiện và các phương thức làm việc của nó. 233
  86. SQL DMO hỗ trợ phát triển ứng dụng từ ngôn ngữ lập trình Visual Basic, C++, khi đóng gói các thư viện liên kết động sẽ được đóng gói cùng, cài dặt ứng dụng thư viện sẽ được cài đặt trong Windows, nên khi chạy ứng dụng bạn không cần thiết lập môi trường Client Connectivity. Các tập tin cơ bản cho SQL DMO: sqldmo.dll, sqldmo80.hlp, sqldmo.rll, sqldmo.h (C++), sqldmoid.h (C++), sqldmo.sql. Trong phần này sẽ giới thiệu kỹ thuật thiết kế ứng dụng từ Visual Basic 6.0. Khai báo thư viện trong project. - Vào menu Project -> References - Chọn Microsoft SQL DMO Object Library -> Ok Khai báo đối tượng. Sau khi thực hiện khai báo thư viện trong project, ta có thể khai báo biến kiểu đối tượng (object) hoặc kiểu đối tượng của SQL DMO. Ví dụ khai báo biến kiểu SQL Server: Dim oSQLServer As SQLDMO.SQLServer Kết nối đến SQL Server. 234
  87. Để kết nối đến SQL Server ta sử dụng phương thức kết nối của đối tượng SQL Server, có 3 tham số Servername, LoginName, Pasword. Dim oSQLServer As SQLDMO.SQLServer Set oSQLServer = New SQLDMO.SQLServer oSQLServer.Connect "ServerName", "LoginName", "Pasword" Thực hiện lại kết nối: Trong nhiều trường hợp bạn muốn ngắt kết nối hiện tại và thực hiện lại kết nối để lấy trạng thái SQL Server hiện thời (tương tự động tác làm tươi – Reresh). oSQLServer.DisConnect oSQLServer.ReConnect Làm việc với các đối tượng. SQL DMO tạo đối tượng kế thừa từ những đối tượng con của nó, ví dụ SQL Server kế thừa từ các đối tượng Database <- Table <- Column, Xác định biến với CSDL: Dim oDatabase as new SQLDMO.Database Set oDatabase = oSQLServer.Databases("Northwind") Lấy danh sách tên các CSDL vào hộp thoại: Dim nDatabase as Integer For nDatabase = 1 to oSQLServer.Databases.Count Combo1.AddItem oSQLServer.Databases(nDatabase).Name Next nDatabase Các đối tượng đều được kế thà từ các đối tượng con, các đối tượng con tạo thành một tập hợp, tập hợp nói trên có thể thực hiện các phương thức Add, Remove, với từng đối tượng. Ví dụ remove bảng khỏi CSDL: oServer.Databases("Northwind").Tables.Remove("Orders", "anne") Thực hiện lệnh SQL: 235
  88. Các đối tượng (SQL Server, Database) có thể thực hiện các lệnh SQL thông qua các phương thức ExecuteImmediate và ExecuteWithResults. Ví dụ thực hiện lệnh thao tác: oSQLServer.ExecuteImmediate “Create Database Example” Ví dụ thực hiện lệnh truy vấn: Dim rs As QueryResults Set rs = oDatabase.ExecuteWithResults("Select * from Authors") Ví dụ lấy dữ liệu từ một truy vấn: For i = 1 To rs.Rows For j = 1 To rs.Columns MsgBox rs.GetColumnString(i, j) Next j Next i Các phương thức thực hiện kết nối có thể hỗ trợ theo từng ngôn ngữ lập trình, hỗ trợ nhiều trong việc lập trình từ Visual Basic, ASP, C, C++. Bạn có thể tham khảo các ví dụ trong thư mục Sample của SQL Server. Các ví dụ sẽ đề cập nhiều ngôn ngữ lập trình, nhiều sự kiện khác nhau. 236