Tài liệu Bài giảng Giới thiệu SQL Server 2008: Trang 1
Chương 1 Giới Thiệu SQL Server 2008
Trong một thế giới dữ liệu ngày nay, dữ liệu và các hệ thống quản lý dữ liệu đó cần
phải luôn luôn được bảo đảm và ở trạng thái có sẵn. SQL Server 2008 cho phép các
nhà phát triển giảm được sự phức tạp của cơ sở hạ tầng trong khi đó vẫn bảo đảm
cung cấp một nền tảng dữ liệu doanh nghiệp có khả năng bảo mật, khả năng mở rộng
và quản lý tốt hơn, cùng với thời gian chết của ứng dụng giảm.
Những điểm mới của SQL server 2008:
Nền tảng cho các nhiệm vụ then chốt - SQL Server 2008 cho phép các tổ chức
có thể chạy hầu hết các ứng dụng phức tạp của họ trên một nền tảng an toàn, tin
cậy và có khả năng mở rộng. Bên cạnh đó còn giảm được sự phức tạp trong việc
quản lý cơ sở hạ tầng dữ liệu. SQL Server 2008 cung cấp một nền tảng tin cậy và an
toàn bằng cách bảo đảm những thông tin có giá trị trong các ứng dụng đang tồn tại
và nâng cao khả năng sẵn có của dữ liệu. SQL Server 2008 giới thiệu một cơ chế
quản lý cách tân dựa trê...
161 trang |
Chia sẻ: hunglv | Lượt xem: 1755 | Lượt tải: 1
Bạn đang xem trước 20 trang mẫu tài liệu Bài giảng Giới thiệu SQL Server 2008, để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên
Trang 1
Chương 1 Giới Thiệu SQL Server 2008
Trong một thế giới dữ liệu ngày nay, dữ liệu và các hệ thống quản lý dữ liệu đó cần
phải luôn luôn được bảo đảm và ở trạng thái có sẵn. SQL Server 2008 cho phép các
nhà phát triển giảm được sự phức tạp của cơ sở hạ tầng trong khi đó vẫn bảo đảm
cung cấp một nền tảng dữ liệu doanh nghiệp có khả năng bảo mật, khả năng mở rộng
và quản lý tốt hơn, cùng với thời gian chết của ứng dụng giảm.
Những điểm mới của SQL server 2008:
Nền tảng cho các nhiệm vụ then chốt - SQL Server 2008 cho phép các tổ chức
có thể chạy hầu hết các ứng dụng phức tạp của họ trên một nền tảng an toàn, tin
cậy và có khả năng mở rộng. Bên cạnh đó còn giảm được sự phức tạp trong việc
quản lý cơ sở hạ tầng dữ liệu. SQL Server 2008 cung cấp một nền tảng tin cậy và an
toàn bằng cách bảo đảm những thông tin có giá trị trong các ứng dụng đang tồn tại
và nâng cao khả năng sẵn có của dữ liệu. SQL Server 2008 giới thiệu một cơ chế
quản lý cách tân dựa trên chính sách, cơ chế này cho phép các chính sách có thể
được định nghĩa quản trị tự động cho các thực thể máy chủ trên một hoặc nhiều
máy chủ. Thêm vào đó, SQL Server 2008 cho phép thi hành truy vấn dự báo với
một nền tảng tối ưu.
Sự phát triển động - SQL Server 2008 cùng với .NET Framework đã giảm được sự
phức tạp trong việc phát triển các ứng dụng mới. ADO.NET Entity Framework cho
Kết thúc chương này các bạn có thể :
Trình bày được các khái niệm cơ bản SQL Server 2008
Mô tả được các thành phần và kiến trúc SQL Server 2008.
Thực hành được cách tạo cơ sở dữ liệu, tạo bảng, tạo kết nối giữa các bảng…
Thực hành được sao lưu và phục hồi cơ sở dữ liệu.
1.1 Tổng quan về SQL Server 2008
- Free Download
Trang 2
phép các chuyên gia phát triển phần mềm có thể nâng cao năng suất bằng làm việc
với các thực thể dữ liệu logic đáp ứng được các yêu cầu của doanh nghiệp thay vì
lập trình trực tiếp với các bảng và cột. Các mở rộng của ngôn ngữ truy vấn tích hợp
(LINQ) mới trong .NET Framework đã cách mạng hóa cách các chuyên gia phát triển
truy vấn dữ liệu bằng việc mở rộng Visual C#® và Visual Basic® .NET để hỗ trợ cú
pháp truy vấn giống SQL vốn đã có. Hỗ trợ cho các hệ thống kết nối cho phép
chuyên gia phát triển xây dựng các ứng dụng cho phép người dùng mang dữ liệu
cùng với ứng dụng này vào các thiết bị và sau đó đồng bộ dữ liệu của chúng với
máy chủ trung tâm.
Dữ liệu quan hệ mở rộng - SQL Server 2008 cho phép các chuyên gia phát triển
khai thác triệt để và quản lý bất kỳ kiểu dữ liệu nào từ các kiểu dữ liệu truyền thống
đến dữ liệu không gian địa lý mới.
Thông tin trong toàn bộ doanh nghiệp - SQL Server 2008 cung cấp một cơ sở
hạ tầng có thể mở rộng, cho phép quản lý các báo cáo, phân tích với bất kỳ kích
thước và sự phức tạp nào, bên cạnh đó nó cho phép người dùng dễ dàng hơn trong
việc truy cập thông tin thông qua sự tích hợp sâu hơn với Microsoft Office. Điều này
cho phép CNTT đưa được thông tin của doanh nghiệp rộng khắp trong tổ chức. SQL
Server 2008 tạo những bước đi tuyệt vời trong việc lưu trữ dữ liệu, cho phép người
dùng hợp nhất các trung tâm dữ liệu vào một nơi lưu trữ dữ liệu tập trung của toàn
doanh nghiệp.
- Free Download
Trang 3
Hình 1.1. Toàn cảnh nền tảng dữ liệu của Microsoft
Các bước cài đặt SQL Server 2008
Bước 1: Chạy file setup.exe để cài đặt, chọn Installation -> New SQL Server stand-alone ...
Hình 1.2. Giao diện SQL Server Installation Center
- Free Download
Trang 4
Bước 2: Chọn Ok -> Next .
Hình 1.3. Giao diện Setup Support Files
Bước 3: chọn kiểu cài đặt mới
- Free Download
Trang 5
Hình 1.4. Giao diện Installation Type
Bước 4: Nhập product key
- Free Download
Trang 6
Hình 1.5. Giao diện Product Key
Bước 5: Sau khi đồng ý License Terms, chọn các thành phần cài đặt
- Free Download
Trang 7
Hình 1.6. Giao diện Feature Selection
Bước 6: Thiết lập cài đặt chọn Default instance
- Free Download
Trang 8
Hình 1.7. Giao diện Instance Configuration
Bước 7: Cấu hình server
- Free Download
Trang 9
Hình 1.8. Giao diện Server Configuration
Bước 8: Cấu hình dữ liệu như sau chọn Window Authentication và Add current User
- Free Download
Trang 10
Hình 1.9. Giao diện Database Engine Configuration
Bước 9: Cấu hình analysis services Add Current User
Bước 10: Cấu hình report chọn option như hình nhấn Next, Next … Cho đến khi hoàn tất
- Free Download
Trang 11
Hình 1.10. Giao diện Reporting Services Configuration
Mở SQL Server Management Studio ta làm như sau: Vào start -> chọn program -> chọn
Microsoft SQL Server 2008 -> chọn SQL Server Management Studio
1.2 SQL Server Management Studio
- Free Download
Trang 12
Hình 1.11. Kết nối vào SQL Server
Chú ý những thành phần trên hộp thoại sau:
Server Type: Như ví dụ của cuốn sách này, cho phép server type là Database Engine.
Các tùy chọn khác là kiểu dữ liệu khác nhau của servers nó sẽ hiển thị kết nối.
Server Name: Hộp combo thứ 2 chứa 1 danh sách của SQL Server cài đặt mà chọn.
Trong hộp thoại hình 12, bạn sẽ thầy tên của máy tính được cài đặt trên local. Nếu bạn
mở hộp Server name bạn có thể tìm kiếm nhiều server local hoặc network connection
bằng cách chọn .
Authentication: Combobox cuối cùng xác định các loại hình kết nối bạn muốn sử
dụng.Trong giáo trình này chúng ta kết nối đến SQL Server sử dụng Windowns
Authentication. Nếu bạn cài đặt SQL Server với chế độ hỗn hợp(mix mode), thì bạn có
thể thay đổi chọn lựa SQL Server authentication, thì nó sẽ mở hai hộp thoại và cho
phép nhập username và password.
Sau khi nhấn nút Connect sẽ xuất hiện màn hình sau:
- Free Download
Trang 13
Hình 1.12. SQL Server Management Studio
Tạo cơ sở dữ liệu (database)
Chọn database -> Click phải -> Chọn New Database…
Hình 1.13. Hộp thoại Object Explorer
Trong hộp thoại New Database đặt tên cho database name -> Chọn OK
- Free Download
Trang 14
Hình 1.14. Giao diện New Database
Tạo bảng (table)
Vào database quản lý bán hàng chọn table. Sau đó click phải lên table -> Chọn New
Table
- Free Download
Trang 15
Hình 1.15.
Khi chọn New Table sẽ xuất hiện bên phải màn hình bên dưới
Sau đó ta nhập Column Name, Data Type… Nhấn Enter để nhập cột kết tiếp.
Hình 1.16.
- Free Download
Trang 16
Lưu table trên thanh Standard toolbar -> chọn Save
Hình 1.17.
Tạo quan hệ kết nối giữa các bảng (relatetionship)
1. Tạo khóa chính (Primary key) cho table trong SQL Server Management Studio,
tạo cột và kiểu dữ liệu. Sau đó trên thanh toolbar, chọn nút Set Primary Key .
Bạn cũng có thể click phải lên column chọn Set Primary Key.
Hình 1.18.
2. Tạo khóa ngoại (Foreign key) trong cửa sổ thiết kế table. Foreign được
dùng để liên kết các table lại với nhau.
Cần lưu ý khi tạo foreign key là tên cột, kiểu dữ liệu giống tên cột của khóa
chính mà table nó đại diện.
Ví dụ sau MaDMSP làm foreign key
- Free Download
Trang 17
Hình 1.19.
3. Tạo sơ đồ (Diagrams)
Diagrams là 1 cửa sổ hiển thị mối quan hệ giữa các table của 1 database. Tạo
diagram ta thực hiện như sau:
Trong cửa sổ Object Explorer chọn tên database cần tạo -> Click phải
vào Database Diagrams -> Chọn New Database Diagram
Hình 1.20.
- Free Download
Trang 18
Sau khi chọn New Database Diagram sẽ xuất hiện hộp thoại để Add
các table, sau khi add xong chọn Close.
Hình 1.21.
Để thiết lập mối quan hệ giữa các table ta chọn cột dữ liệu của cột làm khóa
chính trong bảng cha (parent table) và kéo nó đến khóa ngoại trong bảng con
(child table)
Hình 1.22.
Sau khi kéo mối quan hệ cho 2 table sẽ xuất hiện hộp thoại như hình 1.21.
- Free Download
Trang 19
Hình 1.23.
Khi ta chọn OK giữa 2 table sẽ xuất hiện một kết nối giữa 2 table
Hình 1.24.
Trong cùng một cách, bạn có thể tạo mối quan hệ khác. Khi bạn đã hoàn tất,
bạn có thể lưu và đóng diagram.
4. Back up và Restore dữ liệu
4.1. Back up
Click phải vào database cần back up -> Chọn Tasks -> Chọn Back up…
Thực hiện các thao tác theo thứ tự các hình bên dưới
- Free Download
Trang 20
Hình 1.25.
- Free Download
Trang 21
Hình 1.26.
- Free Download
Trang 22
Hình 1.27.
Hình 1.29.
- Free Download
Trang 23
Hình 1.30.
- Free Download
Trang 24
Hình 1.31.
Hình 1.32.
4.2. Restore
Click phải vào database cần Restore -> Chọn Tasks -> Chọn Restore ->
Database…
Thực hiện các thao tác theo thứ tự các hình bên dưới
- Free Download
Trang 25
Hình 1.33.
- Free Download
Trang 26
Hình 1.34.
Hình 1.35
- Free Download
Trang 27
Hình 1.36
- Free Download
Trang 28
Hình 1.37
Hình 1.38
- Free Download
Trang 29
Hình 1.39
- Free Download
Trang 30
Hình 1.40
- Free Download
Trang 31
Chương 2 Truy Vấn Dữ Liệu Cơ Bản
Transact-SQL là ngôn ngữ SQL mở rộng dựa trên SQL chuẩn của ISO (International
Organization for Standardization) và ANSI (American National Standards Institute) được sử
dụng trong SQL Server khác với PL-SQL ((Procedural Language/Structured Query Language)
dùng trong Oracle.
Trong bài này chúng ta sẽ tìm hiểu sơ qua về T-SQL. Chúng được chia làm
3 nhóm:
2.1.1. Data Definition Language (DDL):
Ðây là những lệnh dùng để quản lý các thuộc tính của một database như định nghĩa các
hàng hoặc cột của một table, hay vị trí data file của một database..., thường có dạng như
sau :
Create object_Name
Alter object_Name
Drop object_Name
Trong đó object_Name có thể là một table, view, stored procedure, indexes...
Kết thúc chương này các bạn có thể :
Trình bày được các lệnh T-SQL : biến, if…else, case…when,…
Thực hiện được thao tác truy vấn dữ liệu
Trình bày và vận dụng được các mệnh đề trong truy vấn
Trình bày và sử dụng được các hàm cơ bản của SQL Server
2.1 Khái niệm cơ bản về T-SQL
- Free Download
Trang 32
Ví dụ:
Lệnh Create sau sẽ tạo ra một table tên Importers với 3 cột
CompanyID,CompanyName,Contact
USE Northwind - - sử dụng cơ sở dữ liệu Northwind
CREATE TABLE Importers(
CompanyID int NOT NULL,
CompanyName varchar(40) NOT NULL,
Contact varchar(40) NOT NULL
)
Lệnh Alter sau đây cho phép ta thay đổi định nghĩa của một table như thêm(hay bớt)
một cột hay một Constraint...Trong ví dụ này ta sẽ thêm cột ContactTitle vào table
Importers
USE Northwind
ALTER TABLE Importers
ADD ContactTitle varchar(20) NULL
Lệnh Drop sau đây sẽ hoàn toàn xóa table khỏi database nghĩa là cả định nghĩa
của table và data bên trong table đều biến mất (khác với lệnh Delete chỉ xóa
data nhưng table vẫn tồn tại).
USE Northwind
DROP TABLE Importers
2.1.2. Data Control Language (DCL):
Ðây là những lệnh quản lý các quyền truy cập lên từng object (table, view, stored
procedure...). Thường có dạng sau:
Grant
Revoke
Deny
Ví dụ:
- Free Download
Trang 33
Lệnh sau sẽ cho phép user trong Public Role được quyền Select đối với table
Customer trong database Northwind (Role là một khái niệm giống như Windows
Group sẽ được bàn kỹ trong phần Security)
USE Northwind
GRANT SELECT
ON Customers
TO PUBLIC
Lệnh sau sẽ từ chối quyền Select đối với table Customer trong database Northwind
của các user trong Public Role
USE Northwind
DENY SELECT
ON Customers
TO PUBLIC
Lệnh sau sẽ xóa bỏ tác dụng của các quyền được cho phép hay từ chối trước đó
USE Northwind
REVOKE SELECT
ON Customers
TO PUBLIC
2.1.3. Data Manipulation Language (DML):
Ðây là những lệnh phổ biến dùng để xử lý data như Select, Update, Insert, Delete
Ví dụ:
Select
USE Northwind
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE (CustomerID = 'alfki' OR CustomerID = 'anatr')
ORDER BY ContactName
Insert
USE Northwind
INSERT INTO Territories VALUES (98101, 'Seattle', 2)
- Free Download
Trang 34
Update
USE Northwind
UPDATE Territories
SET TerritoryDescription = 'Downtown Seattle'
WHERE TerritoryID = 98101
Delete
USE Northwind
DELETE FROM Territories
WHERE TerritoryID = 98101
Chú ý : trong lệnh Delete bạn có thể có chữ From hay không đều được.
Để kiểm tra các ví dụ trên ta làm như sau:
Trên thanh toolbar của màn hình SQL Server Mangement Studio -> Chọn New Query và gõ
các câu lệnh như trên. Sau đây là 1 ví dụ tạo table bằng câu lệnh T-SQL.
Hình 2.1
- Free Download
Trang 35
Cú pháp của T-SQL
Phần này chúng ta sẽ bàn về các thành phần tạo nên cú pháp của T-SQL
Identifiers
Ðây chính là tên của các database object. Nó dùng để xác định một object. (Chú ý khi nói
đến Object trong SQL Server là chúng ta muốn đề cập đến table, view, stored procedure,
index.....Vì hầu như mọi thứ trong SQL Server đều được thiết kế theo kiểu hướng đối tượng
(object-oriented)). Trong ví dụ sau TableName, KeyName, Description là những identifiers
CREATE TABLE TableName
(KeyName INT PRIMARY KEY, Description NVARCHAR(80))
Có hai loại Identifiers một loại thông thường (Regular Identifier) và một loại gọi là
Delimited Identifier, loại này cần có dấu "" hay dấu [] để ngăn cách. Loại Delimited
được dùng đối với các chữ trùng với từ khóa của SQL Server (reserved keyword) hay các
chữ có khoảng trống.
Ví dụ:
SELECT * FROM [My Table]
WHERE [Order] = 10
Trong ví dụ trên chữ Order trùng với keyword Order nên cần đặt trong dấu ngoặc vuông [].
Hàm (Functions)
Có 2 loại hàm một loại là built-in và một loại user-defined
Các hàm Built-In được chia làm 3 nhóm:
Rowset Functions : Loại này thường trả về một object và được đối xử như một
table. Ví dụ như hàm OPENQUERY sẽ trả về một recordset và có thể đứng vị trí của
một table trong câu lệnh Select.
Aggregate Functions : Loại này làm việc trên một số giá trị và trả về một giá trị
đơn hay là các giá trị tổng. Ví dụ như hàm AVG sẽ trả về giá trị trung bình của một
cột.
- Free Download
Trang 36
Scalar Functions : Loại này làm việc trên một giá trị đơn và trả về một giá trị đơn.
Trong loại này lại chia làm nhiều loại nhỏ như các hàm về toán học, về thời gian, xử
lý kiểu dữ liệu String....Ví dụ như hàm MONTH('2002-09-30') sẽ trả về tháng 9.
Các hàm User-Defined (được tạo ra bởi câu lệnh CREATE FUNCTION và phần body
thường được gói trong cặp lệnh BEGIN...END) cũng được chia làm các nhóm như sau:
Scalar Functions : Loại này cũng trả về một giá trị đơn bằng câu lệnh RETURNS.
Table Functions : Loại này trả về một table
Chú Thích (Comments)
T-SQL dùng dấu -- để đánh dấu phần chú thích cho câu lệnh đơn và dùng /*...*/
để chú thích cho một nhóm
Thực Thi Các Câu Lệnh SQL
Thực thi một câu lệnh đơn:
Một câu lệnh SQL được phân ra thành các thành phần cú pháp như trên bởi một parser,
sau đó SQL Optimizer (một bộ phận quan trọng của SQL Server) sẽ phân tích và tìm cách
thực thi (Execute Plan) tối ưu nhất ví dụ như cách nào nhanh và tốn ít tài nguyên của máy
nhất... và sau đó SQL Server Engine sẽ thực thi và trả về kết quả.
Thực Thi một nhóm lệnh (Batches)
Khi thực thi một nhóm lệnh SQL Server sẽ phân tích và tìm biện pháp tối ưu cho các câu
lệnh như một câu lệnh đơn và chứa execution plan đã được biên dịch (compiled) trong bộ
nhớ sau đó nếu nhóm lệnh trên được gọi lại lần nữa thì SQL Server không cần biên dịch mà
có thể thực thi ngay điều này giúp cho một batch chạy nhanh hơn.
Lệnh GO
Lệnh này chỉ dùng để gởi một tín hiệu cho SQL Server biết đã kết thúc một batch job và
yêu cầu thực thi. Nó vốn không phải là một lệnh trong T-SQL.
2.2 Cách sử dụng biến, toán tử, biểu thức, điều kiện
- Free Download
Trang 37
Biến (Variable)
SQL Server cung cấp 2 loại biến trong T-SQL đó là: biến toàn cục global variable được
bắt đầu bằng @@ và local variable (biến cục bộ )được bắt đầu bằng @
Global variable
SELECT @@VERSION AS SQL_SERVER_VERSION_DETAILS
Một số globle variable trong SQL: @@CONNECTIONS, @@CPU_BUSY,
@@CURSOR_ROWS, @@ERROR, @@FETCH_STATUS…
Local variable
USE Northwind
DECLARE @EmpIDVar INT
SET @EmpIDVar = 3
SELECT EmployeeID, LastName, FirstName FROM Employees
WHERE EmployeeID = @EmpIDVar + 1
Lưu ý: Khi khai báo biến local variable ta dùng từ khóa DECLARE
Toán tử (Operator)
Toán tử bao gồm các phép tính: +, -, *, /
- Free Download
Trang 38
Ví dụ:
DECLARE @A INT, @B INT, @KetQua fLOAT
SET @A=5
SET @B=2
SET @KetQua= @A + @B
PRINT N'Cộng: ' + convert(nvarchar(40),@KetQua)
SET @KetQua= @A - @B
PRINT N'Trừ: ' + convert(nvarchar(40),@KetQua)
SET @KetQua= @A * @B
PRINT N'Nhân: ' + convert(nvarchar(40),@KetQua)
SET @KetQua= @A / @B
PRINT N'Chia: ' + convert(nvarchar(40),@KetQua)
Kết quả:
Cộng: 7
Trừ: 3
Nhân: 10
Chia: 2
Cấu trúc điều khiển
IF…ELSE
Cú pháp:
IF (Biểu_thức)
- Free Download
Trang 39
{ Câu lệnh hoặc nhóm lệnh được thực thi }
ELSE
{ Câu lệnh hoặc nhóm lệnh được thực thi }
Lưu ý: Trong SQL nếu ta muốn thực thi 1 nhóm lệnh thì nhóm lệnh đó phải nằm
trong từ khóa BEGIN…END
Ví dụ:
DECLARE @CharGender Char(1),
@Gender Varchar(20);
SET @CharGender = 'F';
IF (@CharGender'F')
SET @Gender='Male'
ELSE
SET @Gender='Female'
SELECT @Gender AS [Giới Tính]
CASE…WHEN
Khi chúng ta sử dụng nhiều If..else thì có thể dung Case..When để thay thế.
Cú pháp:
CASE Biểu thức
WHEN Giá trị 1 THEN kết quả
WHEN Giá trị 2 THEN kết quả
WHEN Giá trị n THEN kết quả
END
- Free Download
Trang 40
Ví dụ:
DECLARE @CharGender Char(1),
@Gender Varchar(20);
SET @CharGender = 'F';
SET @Gender =
CASE @CharGender
WHEN 'm' THEN 'Male'
WHEN 'M' THEN 'Male'
WHEN 'f' THEN 'Female'
WHEN 'F' THEN 'Female'
END;
SELECT @Gender AS [Giới Tính]
Khối lệnh : BEGIN…END
Cú pháp:
BEGIN
{ Câu lệnh hoặc nhóm lệnh được thực thi}
END
Vòng lặp : WHILE
Cú pháp:
WHILE Biểu thức
{
Câu lệnh hoặc nhóm lệnh được thực thi
}
Ví dụ:
DECLARE @Number As int
- Free Download
Trang 41
SET @Number = 1
WHILE @Number < 5
BEGIN
SELECT @Number AS Number
SET @Number = @Number + 1
END
FOR
Mệnh đề For được dùng để chỉ định chọn lựa BROWSE hoặc XML. BROWSE và XML
không liên quan trong cấu trúc lặp.
BEARK
Thoát khỏi vòng lặp WHILE hoặc mệnh đề IF… ELSE được lồng bên trong vòng lặp
WHILE. Các câu lệnh thực thi sau từ END được thực thi.
CONTINOUS
Chạy lại vòng lặp WHILE. Các câu lệnh thực thi sau từ khóa CONTIOUS điều được bỏ
qua.
GOTO
- Free Download
Trang 42
Dùng GOTO để nhảy đến tên label trong khối lệnh đang được thực thi. Không có phát
biểu nào ở giữa GOTO và lable được thực thi.
Cú pháp:
GOTO Tên_lable
RETURN
Chúng ta có thể dùng RETURN bất kỳ thời điểm nào để thoát khỏi khối lệnh, thủ tục.
Các phát biểu sau RETURN sẽ không được thực thi.
Cú pháp:
RETURN [ integer_expression ]
WAITFOR
Thực thi một khối lệnh của một đợt, stored procedure hoặc transaction đến thời gian chỉ
định hoặc thời gian được kích hoạt hoặc sửa một câu lệnh được chỉ định hoặc trả về ít
nhất một dòng.
Cú Pháp
WAITFOR
{
DELAY
| TIME
}
Ví dụ
USE msdb;
EXECUTE sp_add_job @job_name = 'TestJob';
BEGIN
WAITFOR TIME '10:00';
- Free Download
Trang 43
EXECUTE sp_update_job @job_name = 'TestJob',
@new_name = 'UpdatedJob';
END;
GO
Câu lệnh SELECT... FROM dùng để cho phép bạn có thể chọn lựa các dữ liệu cần thiết từ một
hoặc nhiều bảng có quan hệ bên trong một cơ sở dữ liệu.
Cú pháp:
SELECT
FROM
hay
SELECT * FROM tên_table
(*) dùng để lấy tất cả các cột trong table
Ví dụ:
USE Northwind
SELECT RegionID, RegionDescription
FROM Region
Hoặc bạn có thể viết theo cách sau:
USE Northwind
SELECT * FROM Region
2.3 Truy vấn dữ liệu sử dụng SELECT…FROM
- Free Download
Trang 44
Với cú pháp SELECT... FROM bên dưới kết hợp mệnh đề WHERE cho phép bạn có thể lọc các
dòng dữ liệu bên trong một bảng phải thỏa điều kiện đưa ra trong mệnh đề WHERE.
Cú pháp:
SELECT [DISTINCT ][TOP số dòng[PERCENT]]<tên_column_1,
tên_column_2,..., tên_column_n>
FROM
WHERE
Từ khóa DISTINCT : dùng để chỉ định truy vấn chỉ chọn ra các dòng dữ liệu duy nhất,
không trùng lắp dữ liệu.
Từ khóa TOP : dùng để chỉ định truy vấn chỉ chọn ra chính xác bao nhiêu dòng dữ liệu
đầu tiên. Nếu có thêm từ khóa PERCENT đi kèm theo thì truy vấn chỉ chọn ra bao nhiêu
phần trăm mẫu tin đầu tiên, lúc bấy giờ con số mà bạn chỉ định phải nằm trong phạm vi
từ 0 đến 100. Thông thường khi sử dụng từ khóa TOP thì bạn sẽ kết hợp mệnh đề ORDER
BY để sắp xếp lại dữ liệu theo một thứ tự nào đó.
Điều kiện lọc : là điều kiện chỉ định việc lọc ra các mẫu tin bên trong bảng. Thông
thường là một biểu thức luận lý.
Ví dụ:
USE Northwind
SELECT RegionID, RegionDescription
FROM Region
WHERE RegionDescription='Southern'
2.3.1 Truy vấn dữ liệu sử dụng mệnh đề WHERE
- Free Download
Trang 45
Với cú pháp SELECT...FROM bên dưới kết hợp mệnh đề ORDER BY cho phép bạn có thể lấy
dữ liệu của các cột bên trong một bảng, sau đó sắp xếp lại dữ liệu theo thứ tự chỉ định là
tăng hoặc giảm.
Cú pháp:
SELECT
FROM
[ WHERE ]
ORDER BY ASC/ DESC
Ví dụ:
USE Northwind
SELECT ProductID,ProductName,UnitPrice
FROM Products
WHERE UnitPrice>=34
ORDER BY UnitPrice ASC
2.3.2 Truy vấn và sắp xếp dữ liệu sử dụng mệnh đề ORDER BY
- Free Download
Trang 46
Hàm MAX() : Hàm này sẽ trả về giá trị lớn nhất trong biểu thức. Nó có thể dùng với
các kiểu dữ liệu số, chuỗi hay ngày tháng. Max trả về giá trị lớn nhất trong toàn bộ giá
trị sau khi đã đối chiếu.
Lưu ý: Hàm MAX bỏ qua các giá trị NULL.
Ví dụ:
USE Northwind
SELECT MAX(UnitPrice) AS MaxPrice
FROM Products
Hàm MIN() : Ngược lại với hàm MAX. Hàm MIN trả về giá trị nhỏ nhất trong biểu
thức. Hàm này có thể dùng với các trường số, chuỗi, ngày tháng. Ngoài ra hàm này bỏ
qua giá trị NULL:
Ví dụ:
USE Northwind
SELECT MIN(UnitPrice) AS MinPrice FROM Products
2.3.3 Sử dụng hàm của T-SQL trong truy vấn dữ liệu
- Free Download
Trang 47
Hàm AGV() : Hàm này trả về giá trị trung bình của các giá trị trong các trường dữ liệu
được chỉ ra trong biểu thức.
Lưu ý: Hàm AVG chỉ được dùng với các trường có kiểu dữ liệu là số. Ngoài ra nó có khả
năng loại bỏ giá trị NULL
Ví dụ:
USE Northwind
SELECT AVG(UnitPrice) AS AvgPrice
FROM Products
Hàm SUM() : Hàm này trả về tổng của tất cả các giá trị của trường dữ liệu trong biểu
thức. Ngoài ra, bạn có thể dùng tới DISTINCT với SUM để tính tổng cho các giá trị duy
nhất của trường dữ liệu trong biểu thức. Các giá trị NULL sẽ bị bỏ qua.
Lưu ý : SUM chỉ dùng cho các trường dữ liệu là kiểu số.
Ví dụ:
USE Northwind
SELECT SUM(UnitPrice) AS [Tổng Sản Phẩm]
FROM Products
Hàm COUNT() : Hàm COUNT được sử dụng đếm các bản ghi được select trong chuỗi
truy vấn. Hàm này có thể đếm được các giá trị NULL trong biểu thức. Nếu ta dùng nó
với từ khóa DISTINCT, COUNT đếm được các giá trị duy nhất. Ngoài ra nó có thể được
dùng với các trường số và ký tự.
Lưu ý: Các bạn có thể dùng ký tự * thay cho biểu thức trong hàm COUNT. Bằng cách
này chúng ta có thể đếm được tất cả các bản ghi mà không cần quan tâm đến các
trường dữ liệu.
Ví dụ: USE Northwind
SELECT COUNT(ProductID) FROM Products
- Free Download
Trang 48
Hàm SQUARE() : tính bình phương
Ví dụ:
DECLARE @A INT
SET @A=5
SELECT SQUARE(@A)AS [@A Bình Phương]
Hàm ROUND() :
ROUND luôn luôn trả về một giá trị. Nếu chiều dài lớn hơn số lượng các chữ số trước
dấu thập phân, ROUND trả về 0.
Round trả về một biểu thức số được làm tròn, bất kể loại dữ liệu, khi chiều dài là một số
âm.
Ví dụ Kết quả
ROUND(748.58, -1) 750.00
ROUND(748.58, -2) 700.00
ROUND(748.58, -3) 1000.00
Ví dụ sau minh họa cách sử dụng Round
SELECT ROUND(123.9994, 3), ROUND(123.9995, 3)
GO
Ví dụ sau minh họa làm tròn và xấp xỉ
SELECT ROUND(123.4545, 2);
GO
- Free Download
Trang 49
SELECT ROUND(123.45, -2);
GO
Ví dụ sau sử dụng hai câu SELECT để chứng minh sự khác biệt giữa làm tròn và
cắt xén. Câu lệnh đầu tiên có kết quả làm tròn. Câu lệnh thứ hai có kết quả cắt
xén.
Hàm CHAR()
Ví dụ:
USE Northwind;
GO
SELECT FirstName + ' ' + LastName, + CHAR(13) + [Address] + CHAR(13) +
HomePhone
FROM Employees
WHERE EmployeeID = 1;
GO
- Free Download
Trang 50
Hàm UPPER() , LOWER()
Với cú pháp chung bên dưới của các hàm UPPER, LOWER có kết quả trả về là một chuỗi
sau khi đã được chuyển đổi các ký tự bên trong chuỗi thành chữ in (upper), hoặc chữ
thường (lower).
Ví dụ : hàm UPPER
USE Northwind;
GO
SELECT UPPER(FirstName) + ' ' + UPPER(LastName) AS Fullname
FROM Employees
WHERE EmployeeID=1
Ví dụ : hàm LOWER
USE Northwind;
GO
SELECT LOWER(UPPER(FirstName)) + ' ' +
LOWER(UPPER(LastName)) AS Fullname
FROM Employees
WHERE EmployeeID=1
Hàm LEN()
Với cú pháp đơn giản của hàm LEN bên dưới có kết quả trả về là một số nguyên dương
dùng để chỉ định chiều dài của một chuỗi chứa bao nhiêu ký tự.
- Free Download
Trang 51
Ví dụ:
USE Northwind;
GO
SELECT LEN(FirstName) AS [Length],FirstName
FROM Employees
WHERE EmployeeID=1
Hàm LTRIM(), RTRIM()
Với cú pháp chung bên dưới của các hàm LTRIM, RTRIM có kết quả trả về là một chuỗi đã được cắt bỏ
các khoảng trắng ở đầu chuỗi (ltrim) hoặc các khoảng trắng ở cuối chuỗi (rtrim).
Cú pháp :
LTRIM (chuổi)
RTRIM (Chuổi)
Ví dụ : hàm LTRIM
DECLARE @string_to_trim varchar(60)
SET @string_to_trim = ' Five spaces are at the beginning of
this string.'
SELECT 'Here is the string without the leading spaces: ' +
LTRIM(@string_to_trim)
GO
- Free Download
Trang 52
Ví dụ: hàm RTRIM()
DECLARE @string_to_trim varchar(60);
SET @string_to_trim = 'Four spaces are after the period in this sentence.
';
SELECT @string_to_trim + ' Next string.';
SELECT RTRIM(@string_to_trim) + ' Next string.';
GO
Hàm LEFT(),RIGHT(),SUBSTRING
Với cú pháp chung bên dưới của các hàm LEFT, RIGHT, SUBSTRING có kết quả trả về là
một chuỗi con được trích ra từ chuỗi nguồn. Chuỗi con được trích ra tại vị trí bắt đầu từ
bên trái (left), bên phải (right) hoặc tại bất kỳ vị trí nào (substring) và lấy ra bao nhiêu ký
tự.
Cú pháp :
LEFT (chuổi nguồn , số ký tự )
RIGHT (chuổi nguồn , số ký tự )
SUBSTRING (chuổi nguồn ,vị trí, số ký tự )
Trong đó :
• Chuỗi nguồn : là chuỗi ký tự nguồn chứa các ký tự muốn được chọn lựa để trích ra.
• Số ký tự : là một số nguyên dương chỉ định số ký tự bên trong chuỗi nguồn sẽ được
trích ra.
- Free Download
Trang 53
• Vị trí : là số nguyên dương chỉ định tại vị trí bắt đầu trích được áp dụng cho hàm
SUBSTRING.
• Chuỗi con : là chuỗi kết quả trả về sau khi thực hiện việc trích các ký tự đã chỉ định
trong các tham số trên.
Ví dụ: hàm LEFT
SELECT LEFT('abcdefg',2) -> Kết quả : ab
Ví dụ: hàm RIGHT
SELECT RIGHT('abcdefg',2)-> Kết quả fg
Ví dụ: hàm SUBSTRING
SELECT SUBSTRING ('abcdefg',4,3)-> Kết quả def
Hàm GETDATE() : lấy ngày hiện hành
Ví dụ:
SELECT GETDATE() AS [Ngày giờ hiện tại]
, CONVERT (date, GETDATE()) AS [Ngày hiện tại]
, CONVERT (time, GETDATE()) AS [Giờ hiện tại]
Hàm DATEPART(YY,getdate()) : lấy 1 phần ( ngày , tháng hoặc năm,… ) của ngày
Ví dụ:
SELECT DATEPART(year, '12:10:30.123') AS [Năm]
,DATEPART(month, '12:10:30.123') AS [Tháng]
,DATEPART(day, '12:10:30.123') AS [Ngày]
,DATEPART(dayofyear, '12:10:30.123') AS [Ngày trong năm]
- Free Download
Trang 54
,DATEPART(weekday, '12:10:30.123') AS [Thứ]
Hàm DATEDIFF(X,Y,Z) : tính khoảng cách giửa hai ngày
Ví dụ:
DECLARE @startdate datetime2 = '2007-05-05 12:10:09.3312722';
DECLARE @enddate datetime2 = '2007-05-07 12:10:09.3312722';
SELECT DATEDIFF(day, @startdate, @enddate) AS [Số ngày]
Hàm DAY() : lấy ngày
Ví dụ:
SELECT DAY(GETDATE()) AS 'Ngày'
Hàm MONTH() : lấy tháng
Ví dụ:
SELECT MONTH(GETDATE()) AS 'Tháng'
Hàm YEAR() : lấy năm
Ví dụ:
SELECT YEAR(GETDATE()) AS 'Năm'
Hàm CAST() : chuyển đổi kiểu
Ví dụ:
DECLARE @NgaySinh datetime, @Tuoi int
SET @NgaySinh='1986-12-19'
- Free Download
Trang 55
SET @Tuoi = YEAR(GETDATE())- YEAR(@NgaySinh)
SELECT N'Bạn được: ' + CAST(@Tuoi AS varchar(10)) + N' tuổi'
Hàm CONVERT() : chuyển đổi kiểu có định dạng
Ví dụ:
DECLARE @NgaySinh datetime, @Tuoi int
SET @NgaySinh='1986-12-19'
SET @Tuoi = YEAR(GETDATE())- YEAR(@NgaySinh)
SELECT N'Bạn được: ' + CONVERT(varchar(10), @Tuoi) + N' tuổi'
- Free Download
Trang 56
Chương 3: Truy Vấn Dữ Liệu Trên Nhiều Bảng
Trong chương này chúng ta sẽ tìm hiểu về cách truy vấn dữ liệu trên nhiều bảng sử dụng các loại
mệnh đề JOIN trong SQL Server.
Với mệnh đề SELECT… FROM kết hợp mệnh đề JOIN cho phép bạn liên kết hai bảng có quan hệ
với nhau để lấy ra các dữ liệu chung. Điểm quan trọng giữa những bảng này phải có các cột quan
hệ chung nhau va thứ tự quan hệ khi bạn chỉ định giữa các bảng cũng sẽ làm ảnh hưởng đến kết
quả của truy vấn.
Bằng cách sử dụng JOIN bạn có thể lấy dữ liệu từ nhiều table dựa trên mối quan hệ logic giữa các
table (logical relationship). Có những loại JOIN như sau:
Thứ tự từ trái sang phải: Inner Join, Left Outer Join, Right Outer Join, Full Outer Join
Kết thúc chương này các bạn có thể :
Trình bày và thực hành được truy vấn trên nhiều bảng với Inner Join
Trình bày và thực hành được truy vấn trên nhiều bảng với Outer Join
Trình bày và thực hành được truy vấn trên nhiều bảng với Cross Join
Trình bày và thực hành được truy vấn kết hợp dữ liệu sử dụng Union
Trình bày và thực hành được truy vấn tạo bảng với Select…Into
Trình bày và thực hành được truy vấn sử dụng mệnh đề : Group By, Having,
Merge, Intersect...
- Free Download
Trang 57
Dùng Inner Join để select data từ 2 hay nhiều tables trong đó giá trị của các cột được join
phải xuất hiện ở cả 2 tables tức là phần gạch chéo trên hình và
dùng để chỉ định việc so sánh giá trị trong các cột của các bảng là tương đương (dữ liệu đều
có ở cả hai bảng). Hệ thống sẽ trả về các mẫu tin thỏa điều kiện quan hệ ở cả hai bảng.
Ví dụ:
SELECT TOP 10 ProductName, CategoryName
FROM Products AS P INNER JOIN Categories AS C
ON P.CategoryID=C.CategoryID
ORDER BY ProductName
Dùng Left Outer Join để select data từ 2 hay nhiều tables trong đó tất cả cột bên table thứ
nhất và không tồn tại bên table thứ hai sẽ được select cộng với các giá trị của các cột được
inner join. Số cột select được sẽ bằng với số cột của table thứ nhất. Tức là phần tô màu đỏ
trên hình.
Ví dụ:
SELECT ProductName, CategoryName
FROM Products AS P LEFT JOIN Categories AS C
ON P.CategoryID=C.CategoryID
ORDER BY ProductName
3.1 Truy vấn dữ liệu sử dụng INNER JOIN
3.2 Truy vấn dữ liệu sử dụng LEFT JOIN
- Free Download
Trang 58
Dùng Right Outer Join để select data từ 2 hay nhiều tables trong đó tất cả cột bên table thứ
hai và không tồn tại bên table thứ nhất sẽ được select cộng với các giá trị của các cột được
inner join. Số cột select được sẽ bằng với số cột của table thứ hai. Tức là phần tô màu đỏ
trên hình.
Ví dụ:
SELECT ProductName, CategoryName
FROM Products AS P RIGHT JOIN Categories AS C
ON P.CategoryID=C.CategoryID
ORDER BY ProductName
Dùng Cross Join ghép data từ hai table trong đó số dòng thu được bằng với số dòng của
table thứ nhất nhân với số dòng của table thứ hai.
Ví dụ:
SELECT TOP 5 * FROM dbo.Products
CROSS JOIN dbo.Categories
Lưu ý : trong câu lệnh này không có keyword "On".
3.3 Truy vấn dữ liệu sử dụng RIGHT OUTER JOIN
3.4 Truy vấn dữ liệu sử dụng CROSS JOIN
3.5 Truy vấn dữ liệu và kết hợp dữ liệu sử dụng UNION
- Free Download
Trang 59
Việc kết hợp dữ liệu của hai truy vấn SELECT FROM bằng mệnh đề UNION cho phép bạn có thể
tạo ra một tập hợp các mẫu tin từ các mẫu tin có trong câu lệnh SELECT FROM thứ nhất và các
mẫu tin có trong câu lệnh SELECT FROM thứ hai. Khác với việc liên kết dữ liệu bằng mệnh đề
JOIN, mệnh đề UNION thực ra chỉ thực hiện việc thêm vào các dòng dữ liệu trong câu lệnh
SELECT FROM thứ nhất vào cuối các dòng dữ liệu trong câu lệnh SELECT FROM thứ hai.
Thông thường bạn sử dụng mệnh đề UNION dùng để nối dữ liệu từ các bảng khác nhau trong
cơ sở dữ liệu thành một bộ các mẫu tin liên tục nhau. Các cột chỉ định trong hai câu lệnh
SELECT FROM phải có cùng kiểu dữ liệu tương thích thứ tự như nhau, tổng số các cột phải
bằng nhau. Việc định dạng tiêu đề của các cột tính toán chỉ cần thực hiện trong câu lệnh truy
vấn đầu tiên.
Kết hợp các kết quả của hai hay nhiều truy vấn thành một tập kết quả duy nhất mà bao gồm
tất cả các dòng thuộc về tất cả các truy vấn trong union. Các hoạt động UNION khác nhau từ
cách sử dụng join để kết hợp cột từ hai table.
- Free Download
Trang 60
Ví dụ:
CREATE TABLE UnionTest1
(
idcol int IDENTITY,
col2 char(3),
);
CREATE TABLE UnionTest2
(
idcol int IDENTITY,
col4 char(3),
);
INSERT INTO UnionTest1 VALUES ('AAA');
INSERT INTO UnionTest1 VALUES ('BBB');
INSERT INTO UnionTest1 VALUES ('CCC');
SELECT * FROM UnionTest1;
INSERT INTO UnionTest2 VALUES ('CCC');
INSERT INTO UnionTest2 VALUES ('DDD');
INSERT INTO UnionTest2 VALUES ('EEE');
Regular UNION
- Free Download
Trang 61
SELECT col2 FROM UnionTest1
UNION
SELECT col4 FROM UnionTest2;
UNION ALL
SELECT col2 FROM UnionTest1
UNION ALL
SELECT col4 FROM UnionTest2;
Với cú pháp SELECT…FROM bên dưới kết hợp mệnh đề INTO cho phép bạn sao chép
dữ liệu và cấu trúc từ kết quả của một truy vấn cho ra một bảng dữ liệu mới bên trong
cơ sở dữ liệu hiện hành hoặc các bảng dữ liệu tạm thời dùng để tính toán các xử lý
phức tạp. Trong trường hợp nếu bạn muốn tạo ra bảng dữ liệu mới thì bắt buộc tên
của bảng phải duy nhất trong cơ sở dữ liệu.
3.6 Truy vấn tạo table sử dụng SELECT … INTO
- Free Download
Trang 62
Bạn có thể chỉ định các ký tự dấu thăng (#) hoặc hai ký tự dấu thăng (##) phía trước tên
bảng được tạo trong câu lệnh SELECT INTO dùng để tạo ra các bảng tạm cục bộ (local) hoặc
các bảng tạm toàn cục (global). Bảng tạm cục bộ chỉ được sử dụng bởi người tao ra nó và hệ
thống sẽ tự động hủy bỏ bảng tạm cục bộ khi người tạo ra bảng ngưng nối kết vào Microsoft
SQL Server. Ngược lại bảng tạm toàn cục được sử dụng cho nhiều người khác nhau và hệ
thống tự động hủy bảng tạm toàn cục khi không còn người sử dụng nào nối kết vào Microsoft
SQL Server.
SELECT ... INTO tạo ra một table mới trong filegroup mặc định và chèn các kết quả dòng từ
truy vấn vào table mới.
Cú pháp :
Trong đó :
• Tên bảng mới : là tên của bảng mới sẽ được tạo lập có cấu trúc và dữ liệu từ truy vấn.
• Tên bảng dữ liệu : là tên của bảng chứa dữ liệu nguồn cho việc sao chép.
Ví dụ:
SELECT TOP 10 [CustomerID] ,[Address],[City] ,[Region],[PostalCode]
,[Country],[Phone] ,[Fax]
INTO KhachHang
FROM [Northwind].[dbo].[Customers]
- Free Download
Trang 63
Xác định một kết quả được đặt tên tạm thời, được biết đến như là một bảng biểu chung.
Ví dụ:
USE Northwind
WITH Emp(EmployeeID, FirstName, LastName)
AS
(
SELECT EmployeeID, FirstName, LastName
FROM dbo.Employees
)
3.7 Truy vấn dữ liệu sử dụng mệnh đề WITH
- Free Download
Trang 64
SELECT EmployeeID, FirstName, LastName
FROM Emp
Bạn có thể sắp xếp kết quả theo các ký tự đầu tiên bằng cách sử dụng mệnh đề Order by.
Ví dụ:
USE Northwind
SELECT FirstName, LastName, [Address]
FROM dbo.Employees
ORDER BY FirstName ASC
3.8 Truy vấn dữ liệu sử dụng mệnh đề ORDER BY
- Free Download
Trang 65
Một điểm mới trong SQL Server 2008 là mệnh đề MERGE. Bạn có thể kết hợp 2 hay nhiều
table với nhau.
Ví dụ:
USE Northwind
SELECT EM.FirstName, E.LastName, E.[Address]
FROM dbo.Employees E
INNER MERGE JOIN dbo.Employees EM
ON E.EmployeeID=EM.EmployeeID
ORDER BY EM.FirstName ASC
Với cú pháp SELECT FROM bên dưới kết hợp mệnh đề GROUP BY cho phép bạn có thể nhóm
dữ liệu của các dòng bên trong một bảng và được phép sử dụng các hàm thống kê đi kèm theo
để tính toán các dữ liệu có tính chất thống kê tổng hợp. Thông thường, sau khi nhóm dữ liệu,
bạn nên sắp xếp lại dữ liệu để hiển thị theo một thứ tự nào đó. Do vậy bạn sẽ sử dụng mệnh
đề ORDER BY sau mệnh đề GROUP BY. Mệnh đề group by dùng để gom nhóm khi tính toán.
Cú pháp :
3.9 Truy vấn dữ liệu sử dụng mệnh đề MERGE
3.10 Truy vấn dữ liệu sử dụng mệnh đề GROUP BY
- Free Download
Trang 66
Ví dụ:
USE Northwind
SELECT Country, COUNT(EmployeeID) AS 'Tổng'
FROM dbo.Employees
GROUP BY Country
Với mệnh đề SELECT… FROM bên dưới kết hợp mệnh đề HAVING cho phép bạn có thể lọc lại
dữ liệu sau khi đã nhóm dữ liệu của các dòng bên trong một bảng. Khác với mệnh đề WHERE
dùng để lọc các dòng dữ liệu hiện đang có bên trong bảng, mệnh đề HAVING chỉ được phép sử
dụng đi kèm theo mệnh đề GROUP BY dùng để lọc lại dữ liệu sau khi đã nhóm. Điều này có
nghĩa là mệnh đề HAVING chỉ được dùng kèm với mệnh đề GROUP BY.
Mệnh đề HAVING dùng để xác định một điều kiện tìm kiếm cho một nhóm hoặc một tập hợp.
HAVING thường được sử dụng trong một mệnh đề GROUP BY.
Cú pháp :
3.11 Truy vấn dữ liệu sử dụng mệnh đề HAVING
- Free Download
Trang 67
Trong đó :
• Hàm thống kê : là tên của các hàm thống kê và các tham số tương ứng dùng để tính
tổng (SUM), tính giá trị thấp nhất (MIN), tính giá trị cao nhất (MAX), đếm các mẫu tin
(COUNT), tính giá trị trung bình (AVG) của các dữ liệu bên trong bảng.
• Bí danh : là tiêu đề mới của các cột tính toán. Các tiêu đề này chỉ có hiệu lực lúc hiển
thị dữ liệu trong câu lệnh truy vấn mà không làm ảnh hưởng đến cấu trúc bên trong của
bảng.
• Danh sách cột nhóm dữ liệu : là danh sách tên các cột được nhóm dữ liệu để tính
toán.
Ví dụ: Lấy danh sách các hóa đơn có trị giá > 10000 và kết quả được sắp xếp
tăng dần theo trị giá
SELECT OD.OrderID, SUM(UnitPrice*Quantity)AS Subtotal
FROM [Order Details] OD JOIN dbo.Orders O
ON OD.OrderID=O.OrderID
GROUP BY OD.OrderID
HAVING SUM(UnitPrice*Quantity)>10000
ORDER BY SUM(UnitPrice*Quantity) ASC
Kết quả :
- Free Download
Trang 68
INTERESCT tự động loại bỏ các dữ liệu trùng từ 2 câu lệnh truy vấn.
Ví dụ:
USE Northwind
SELECT RegionID, RegionDescription
FROM dbo.Region
INTERSECT
SELECT RegionID, RegionDescription
FROM dbo.Region
3.12 Truy vấn dữ liệu sử dụng mệnh đề INTERSECT
- Free Download
Trang 69
USE Northwind
SELECT OrderID FROM dbo.Orders
INTERSECT
SELECT OrderID FROM [Order Details]
EXCEPT tự động loại bỏ các dữ liệu trùng từ 2 câu lệnh truy vấn.
Ví dụ:
USE Northwind
SELECT OrderID FROM dbo.Orders
EXCEPT
SELECT OrderID FROM [Order Details]
Với cú pháp SELECT FROM bên dưới kết hợp mệnh đề COMPUTE cho phép bạn có thể tạo ra
dòng thống kê dữ liệu ở bên cuối kết quả truy vấn. Tuy nhiên nếu bạn sử dụng thêm mệnh
đề COMPUTE BY tiếp theo thì hệ thống sẽ thống kê dữ liệu theo từng nhóm dữ liệu.
Cú pháp :
3.13 Truy vấn dữ liệu sử dụng mệnh đề EXCEPT
3.14 Truy vấn dữ liệu sử dụng mệnh đề COMPUTE BY
- Free Download
Trang 70
Trong đó :
• Count, Min, Max, Sum, Avg : là các hàm thống kê tính toán dữ liệu mà kết quả sẽ xuất
hiện ở cuối kết quả truy vấn hoặc từng nhóm dữ liệu.
• Tên cột : tên các cột hoặc biểu thức được tính toán kèm với các hàm thống kê chỉ định
trước đó.
Ví dụ:
USE Northwind
SELECT * FROM Suppliers COMPUTE count(SupplierID)
USE Northwind
SELECT * FROM Suppliers ORDER BY SupplierID COMPUTE count(SupplierID) BY
SupplierID
Mệnh đề FOR được sử dụng chỉ định trong sự chọn lựa BROWSE hay XML. BROWSE hay XML
không liên quan trong cấu trúc lặp.
Ví dụ:
USE Northwind
SELECT EmployeeID, FirstName, LastName, City, Country
FROM dbo.Employees
ORDER BY FirstName, LastName
FOR XML AUTO
Kết quả :
3.15 Truy vấn dữ liệu sử dụng mệnh đề FOR
- Free Download
Trang 71
Chương 4 Truy Vấn Dữ Liệu Nâng Cao
Chúng ta có thể sử dụng một câu lệnh SELECT để trả về các bản ghi mà sẽ được sử dụng bởi
câu SELECT khác. Câu lệnh bao ở bên ngoài gọi là parent query và câu lệnh bên trong gọi là
subquery .
Select from Where
Trong khi lập trình bên trong Transaction-SQL, có đôi lúc bạn sẽ sử dụng đến truy vấn con đề tính
toán dữ liệu. Truy vấn con chỉ là một câu lệnh truy vấn chọn lựa (SELECT) được lồng vào các câu lệnh
truy vấn khác nhằm thực hiện các truy vấn tính toán phức tạp.
Khi sử dụng đến truy vấn con, bạn cần lưu tâm đến một vài yếu tố sau :
• Cần mở và đóng ngoặc đơn cho câu lệnh truy vấn con.
• Bạn chỉ được phép tham chiếu đến tên một cột hoặc một biểu thức sẽ trả về giá trị trong truy vấn
con.
• Kết quả của truy vấn con có thể trả về là một giá trị đơn lẻ hoặc một danh sách các giá trị.
• Cấp độ lồng nhau của các truy vấn con bên trong Microsoft SQL Server là 32 mức.
Kết thúc chương này các bạn có thể :
Trình bày được khái niệm cơ bản SubQuery ( truy vấn con )
Thực hành được truy vấn dữ liệu sử dụng SubQueries như Table (bảng)
Thực hành được truy vấn dữ liệu sử dụng SubQueries như Expression (biểu thức )
Thực hành được truy vấn dữ liệu sử dụng mệnh đề EXISTS và NOT EXIITS
Thực hành được truy vấn dữ liệu Sử dụng từ khóa DISTINCT
4.1 Sử dụng SubQueries như Table
- Free Download
Trang 72
Ví dụ: giả sử chúng muốn biết các sản phẩm mà đã được đặt hàng, chúng ta có thể sử dụng
câu lệnh , trong ví dụ này chúng ta sử dụng cơ sở dự liệu Northwind.
H4.1 sử dụng subqueries
Chúng ta có thể sử dụng một subquery để thay thế cho một giá trị trong mệnh đề thay thế cho
một giá trị trong mệnh đề SELECT, như là một phần của mệnh đề WHERE. Điều này sẽ được
chỉ ra ví dụ sau đây.
Khi sử dụng các toán tử so sánh với sub query có một số giới hạn với việc trả lại số lượng dòng
và cột của sub-query. Các giới hạn được liệt kê như bảng dưới.
4.2 Sử dụng SubQueries như mệnh đề
- Free Download
Trang 73
Một cột Nhiều cột
Một dòng Sử dụng =,>,< và các toán tử
so sánh khác.
Sử dụng EXISTS
Nhiều dòng Sử dụng ANY, ALL, IN và
EXISTS
Sử dụng EXISTS
Ta có thể sử dụng kết quả trả về của SubQuery như là 1 biểu thức
Ví dụ: Lấy số lượng mặt hàng thuộc loại 1
Select distinct CategoryID , (Select count(ProductID)
from Products
where CategoryID = 1
group by CategoryID ) as ProductQuantity
From Products
where CategoryID = 1
Một số nguyên tắc của SubQuery:
Theo sao một toán tử so sánh có thể bao gồm một biểu thức hoặc cột (ngoại trừ
EXISTS…IN trong câu lệnh SELECT).
Nếu mệnh đề WHERE trong câu lệnh bao gồm tên các cột, sẽ phải phù hợp với các cột
trong danh sách SubQuery.
Kiểu dữ liệu ntext, image, text không thể sử dụng trong SubQuery.
Bởi vì phải trả về một giá trị đơn, subquery không bao gồm mệnh đề GROUP BY và
HAVING, từ khóa ANY hay ALL.
Từ khóa DISTINCT không thể sử dụng với subquery.
Mệnh đề COMPUTE và INTO không thể được dùng trong câu lệnh.
Mệnh đề ORDER BY chỉ có thể dùng khi từ khóa TOP được nêu trong câu lệnh.
View được tạo ra bởi Subquery không thể được cập nhật.
4.3 Sử dụng SubQueries như biểu thức
- Free Download
Trang 74
Subquery với từ khóa EXISTS, theo quy ước, dấu * thay cho tên cột, subquery được
tạo ra nhằm tạo sự tồn tại để kiểm tra sự tồn tại và trả về giá trị TRUE hay FALSE thay
vì trả về dữ liệu.
VÍ dụ : Liệt kê sản phẩm có ProductID=1, trong đó subquery dùng để tính tổng số lượng sản
phẩm đó.
4. 2 Dùng Subquery tính tổng số lượng
- Free Download
Trang 75
VÍ dụ : Liệt kê danh sách các nhà phân phối nhiều mặt hàng nhất
H 4.3 Liệt kê nhà phân phối
Ví dụ : Tính tổng số lượng các mặt hàng đã bán theo từng nhà cung cấp
H 4.4 Tổng hàng đã bán theo nhà cung cấp
- Free Download
Trang 76
Khi một subquery có từ EXISTS, nó có chức năng kiểm tra sự tồn tại. Từ khóa EXISTS được sử
dụng để kiểm tra sự tồn tại của các dòng trả về bởi subquery. Subquery lúc này không thực sự
trả lại dữ liệu, mà nó trả về một giá trị TRUE hoặc FALSE.
Một subquery bao gồm từ EXISTS có cú pháp như sau:
WHERE (NOT) EXISTS (Subquery)
Ví dụ: giả sử chúng ta muốn biết chỉ những sản phẩm nào có mã 1 đã được đặt hàng. Chúng ta
có thể sử dụng từ khóa EXISTS để kiểm tra nếu thông tin về sản phảm tồn tại trong bảng Order
Details.
Câu lệnh và kết quả của nó được chỉ ra trong hình 4.5
4.4 Sử dụng từ khóa EXISTS và NOT EXISTS
- Free Download
Trang 77
H 4.5 Sử dụng mệnh đề EXISTS
Ví dụ: nếu chúng ta muốn chỉ ra những sản phẩm loại 1 mà chưa bao giờ đặt hàng. Chúng ta
sẽ sử dụng từ khóa NOT EXISTS để truy vấn.
- Free Download
Trang 78
H 4.6 sử dụng mệnh đề NOT EXISTS
Một subqueries có thể lồng nhiều subqueries (có thể lên tới 32 mức) nhưng khi đó sẽ
không có hiệu suất thi hành như mong muốn.
Ví dụ: giả sử rằng chúng ta muốn tìm chi tiết những hóa đơn mà bao gồm sản phẩm được
cung cấp từ thành phố London.(H4.7)
- Free Download
Trang 79
H 4.7 Sử dụng nested subqueries
Từ khóa DISTINCT có tác dụng khử các dòng trùng nhau được trả về từ tập kết quả của một
chuỗi Select. Trong trường hợp câu lệnh có từ khóa DISTINCT thí tất cả các dòng trùng nhau
cũng hiển thị.
Ví dụ, chúng ta muốn hiện thị các ProductID không trùng nhau từ bản Order Details với
Discount là 0. Câu lệnh và kết quả như trong hình 4.5.
4.5 Sử dụng từ khóa DISTINCT
- Free Download
Trang 80
H 4.8 Sử dụng mệnh đề DISTINCT.
- Free Download
Trang 81
Chương 5 Modifying Data
Đây là câu lệnh đơn giản nhất để thêm một dòng dữ liệu mới vào table sử dụng câu lệnh
Insert Into .. Values, với câu lệnh này bạn phải chắc chắn rằng tất cả các giá trị thêm vào phải
tương ứng với các cột trong bản cần thêm dữ liệu
Cú pháp
Insert Into [table_name] Values (“value 1”,”value 2”, “value 3”,...)
Ví dụ: chúng ta muốn thêm một giá trị mới vào bảng Region trong cơ sở dữ liệu Northwind ,
Kết quả như trong hình 5.1
Insert into [Region] Values (6,”NewRegion”)
Kết thúc chương này các bạn có thể :
Thực hành được thêm dữ liệu vào bảng sử dụng lệnh INSERT
Thực hành được thêm dữ liệu vào bảng sử dụng lệnh INSERT…SELECT
Thực hành được thêm dữ liệu vào bảng với từ khóa DEFAULT
Thực hành được xóa dữ liệu trên bảng liệu sử dụng lệnh DELETE
Thưc hành được cập nhật dữ liệu trong bảng sử dụng lệnh UPDATE
Trình bày khái niệm cơ bản về TRANSACTION
5.1 Thêm dữ liệu sử dụng INSERT
- Free Download
Trang 82
H 5.1 Thêm Mới
Với câu lệnh Insert into .. values chúng ta chỉ thêm được một dòng dữ liệu tại một thời điểm
thêm mới dữ liệu. Để có thể thêm nhiều dòng dữ liệu tại cùng một thời điểm insert chúng ta có
thể sử dụng câu lệnh Insert ... select
Cú pháp
INSERT INTO [table_name] ( [column1], [column2], [column3], …)
SELECT statement.
Ví dụ: chúng ta tạo mới một bảng Nams có hai cột NameID ,FullName và lấy tất cả tên của
các nhân viên trong bảng Employees thêm vào bản Names, chúng ta sẽ dùng câu lệnh Insert
… Select như hình bên dưới (H 5.2)
5.2 Thêm dữ liệu sử dụng INSERT…SELECT
- Free Download
Trang 83
H 5.2 Insert…Select
Để không phải nhập cùng một giá trị cho nhiều cột dữ liệu, chúng ta có thề gán cho một hay
nhiều cột trong bảng với một giá trị mặc định là DEFAULT value.
Cú pháp:
INSERT INTO [table_name] DEFAULT VALUES
Ví dụ:
Chúng ta tạo một bảng tên History, gồm 3 trường ID, CreateDate, CreateName. Trong đó ta
gán thuộc tính DEFAULT cho CreateDate
5.3 Thêm dữ liệu với từ khóa DEFAULT
- Free Download
Trang 84
CREATE TABLE History
(
ID int IDENTITY,
CreateDate DateTime DEFAULT (GETDATE()),
CreateName varchar(20) NULL
)
H 5.3 Bảng History
Chúng ta Insert vào bảng vừa tạo một dòng dữ liệu theo cách thông thường. Ta sẽ dùng hàm
GETDATE() để lấy ngày giờ hệ thống
- Free Download
Trang 85
H 5.4 Insert dữ liệu không dùng DEFAULT VALUES
Bây giờ ta thêm vào một dòng dữ liệu và sử dụng DEFAULT VALUES, các bạn vẫn có thể lấy giờ
hệ thống.
H 5.5 Insert dữ liệu dùng DEFAULT VALUES
- Free Download
Trang 86
Câu lệnh Delete dùng để xóa tất cả các dòng ra khỏi bảng.
Cú pháp
DELETE [table_name] WHERE [column]=[value]
Lưu ý : Khi sử dụng lệnh Delete mà không có mệnh đề Where thì SQL Server sẽ xóa toàn bộ
các dòng trong bảng .
Ví dụ : trong bảng Employee chúng ta muốn xóa những nhân viên nào ở thành phố LonDon thì
ta có thể sử dụng câu lệnh như sau (H 5.6)
H 5.6 Xóa nhân viên
Ví dụ 2: xóa tất cả các loại sản phẩm chưa có sản phẩm nào (H 5.7)
5.4 Xóa dữ liệu sử dụng câu lệnh DELETE
- Free Download
Trang 87
H 5.7 Xóa loại sản phẩm
Câu lệnh UPDATE được sử dụng để cập nhập/sửa đổi dữ liệu đã có trong bảng
Cú pháp
Update [table_name] SET [column]=[new value]
WHERE [column]=[value]
Lưu ý : Khi sử dụng lệnh Update mà không có mệnh đề Where thì SQL Server sẽ cập nhật toàn
bộ các dòng trong bảng có cùng giá trị của cột được update
Ví dụ : nếu như bạn muôn cập nhập lai tất cả các sản phẩm có cùng một nhà cung cấp , bạn
có thể thưc hiện như sau (H 5.8)
5.5 Cập nhật dữ liệu sử dụng UPDATE
- Free Download
Trang 88
H 5.8 Câp nhập nhà cung cấp
Ví dụ : nếu như bạn muốn cập nhập lại tất cả các sản phẩm có cùng một nhà cung cấp và
những sản phẩn này có số lượng hàng trong kho lớn hơn 0 , bạn có thể thưc hiện như ví dụ
trên và thêm vào mệnh đề Where như sau (H 5.9)
H 5.9 Cập nhập sản phẩm với mệnh đề WHERE
- Free Download
Trang 89
Để áp dụng các câu lệnh trên chúng ta thực hành bài tập sau đây :
Đầu tiên , chúng ta tạo một database BanHang gổm bảng như sau: SanPham, KhachHang,
DonHang.
H 5.10 Tạo CSDL BanHang
Thêm dữ liệu vào các bảng dùng câu lệnh INSERT
H 5.12 Thêm dữ liệu bằng INSERT
- Free Download
Trang 90
H 5.13 Dữ liệu sau khi thêm
Dùng UPDATE để thay đổi giá trị các cột MaSP, SoLuong của đơn hàng thứ nhất.
H 5.14 Cập nhật dữ liệu của MaDonHang=1
- Free Download
Trang 91
Ta xóa thông tin khách hàng có mã là 3 bằng lệnh DELETE
Để xóa dữ liệu của các bảng có quan hệ với nhau, chúng ta phải xóa từ bảng con trước
(DonHang), sau đó mới xóa trong bảng cha (KhachHang).
H 5.15 Xóa dữ liệu 2 bảng liên quan
Transaction là một tập hợp các câu lệnh được kết hợp lại để thực một công việc. Transaction
được dùng để đảo bảo rằng các câu lệnh được thực thi thành công hoặc thất bại.
Có 3 phần chính trong một Transaction:
BEGIN TRANSACTION: bắt đầu một Transaction. Dữ liệu sẽ không được cập nhật đến CSDL
cho đến khi COMMIT TRANSACTION được gọi.
COMMIT TRANSACTION: được gọi khi tất cà các câu lệnh ngay sau BEGIN TRANSACTION
thực hiện thành công, dữ liệu sẽ được ghi xuống CSDL.
ROLLBACK TRANSACTION: trả tất cà dữ liệu về trạng thái ban đầu trước khi BEGIN
TRANSACTION được gọi.
Cú Pháp:
BEGIN TRANSACTION
SQL Statements
COMMIT | ROLLBACK TRANSACTION
5.6 Sử dụng TRANSACTION
- Free Download
Trang 92
Chúng ta có thể sử dụng TRY…CACTH hoặc IF cùng với TRANSACTION
BEGIN TRY
BEGIN TRAN
-- Code for your transaction
COMMIT TRAN
END TRY
BEGIN CATCH
-- output an error message
ROLLBACK TRAN
END CATCH
Ví dụ: Chúng ta sử dụng bảng Categories trong CSDL Northwind để tạo 1 Transaction Update
đơn giản.
H 5.16 TRANSACTION UPDATE CategoryName
Sau khi Update, sản phẩm Beverages đã được đổi thành Coffee , kết quả như hình sau .
- Free Download
Trang 93
H 5.17 Truy vấn bảng Categories
- Free Download
Trang 94
Chương 6 Giới Thiệu Các Thành Phần Khác Trong
SQL Server 2008
STORE PROCEDURE
Là một nhóm các câu lệnh T-SQL đã được biên dịch từ trước( pre-compiled). Một Store
Procedure có thể không chứa hoặc chứa nhiều tham số truyền vào; đồng thời có thề trả về một
giá trị, một bảng hoặc không trả về 1 giá trị nào đó. Sử dụng Store Procedure độ thực thi
nhanh hơn , dễ thay đổi , nâng cấp và bảo mật,….
Có 2 dạng Store Procedure:
System Store Procedure
User-defined Store Procedure
Các System Store Procedure có sẵn khi chúng ta cài đặt SQL Server. Tất cả các System Store
Procedure đều bắt đầu bằng tiền tố sp_
6.1.1. Một số nhóm System Store Procedure:
Database Engine stored procedures: bao gồm các câu lệnh queries lấy
thông tin của SQL Server và CSDL.
VÍ dụ: sp_helpdb lấy thông tin của tất cả các CSDL hiện có trên Server
6.1 Giới thiệu về STORE PROCEDURE
Kết thúc chương này các bạn có thể :
Mô tả được khái niệm và sử dụng được Trigger để ràng buộc dữ liệu
Mô tả được khái niệm và sử dụng được Store Procedure
Trình bày khái niệm cơ bản và sử dụng Function và User-Defined Function
Trình bày khái niệm cơ bản và sử dụng View
- Free Download
Trang 95
Database Mail stored procedures: dùng cho các thao tác về e-mail trong CSDL
(sp_send_dbmail)
Security stored procedures: dùng cho mục đích bảo mật như thêm/ xóa User, đăng
nhập… ( sp_addlogin)
XML stored procedures: dùng quản lý các tài liệu XML (sp_xml_preparedocument).
6.1.2 User-defined Store Procedure
Cú pháp:
CREATE PROC | PROCEDURE
@variablename datatype
@variablename datatype
…
AS
SQL Statement
VÍ dụ: chúng ta tạo một Store Procedure tên GetEmployees để lấy thông tin các nhân viên theo
thành phố từ bảng Employees theo tham số truyền vào là @City.
Sau đó, để gọi Store Procedure vừa tạo, ta dùng lệnh EXEC
- Free Download
Trang 96
H 6.2 Gọi SP GetEmployees
Đề thay đổi tên hay nội dung một Store Procedure ta dùng ALTER thay cho CREATE
Các ví dụ minh họa thực hành về Store Procedure: Tạo các store procedure sử dụng CSDL
Northwind
Tạo store procedure lấy danh sách tất cả các mặt hàng
- Free Download
Trang 97
Tạo store procedure lấy danh sách các mặt hàng có UnitPrice >= giá trị bất kỳ
Tạo store procedure cập nhật UnitPirce với ProductID và UnitPrice do người dùng nhập
vào
Function được dùng tương tự như Store Procedure giúp tối ưu hoạt động của CSDL; giảm thời
gian viết lại các lệnh SQL thường dùng.Ta có thể truyền vào các tham số cho Function.
Tuy nhiên, Function có những đặc điểm khác với Store Procedure:
Function luôn trả về một giá trị
Function phải có tham số kèm theo khi gọi, ngoại trừ 1 số function như GETDATE(),
PI()…
Function có thể được gọi bên trong câu lệnh SELECT
Những Function sẵn có khi ta cài SQL Server gọi là Built-in Function.
Một số ví dụ về Built-in Function:
6.2 Giới thiệu về Function và User-Defined Function
- Free Download
Trang 98
VÍ dụ 1
Tính tổng số lượng từng mặt hàng (Quantity) có mã đơn hàng (OrderID) là 10248.
H 6.7 Tính tổng bằng Sum()
VÍ Dụ 2: Đôi khi, chúng ta muốn có kết quả truy vấn mà dữ liệu trả về được kết hợp từ nhiều
cột trong bảng; khi đó ta có thể dùng CONCAT(). Mỗi hệ CSDL cung cấp 1 cách khác nhau
MySQL: CONCAT()
Oracle: CONCAT(), ||
SQL Server: +
Trong ví dụ này chúng ta chỉ áp dụng CONCAT cho SQL Server. Chú ý : Ta chỉ có thể kết hợp
các cột có cùng kiểu dữ liệu.
- Free Download
Trang 99
H 6.8 Truy vấn không dùng CONCAT
H 6.9 Truy vấn dùng CONCAT
- Free Download
Trang 100
VÍ dụ 3: Hàm Built-in STUFF()
Cú pháp:
STUFF ( character_expression , start , length ,character_expression )
Chúng ta tạo 2 bảng tên ContactPersons và MyCustomers có dữ liệu như hình bên dưới:
H 6.10 Bảng ContactPersons
H 6.11 Bảng MyCustomers
Đề thu được kết quả như hình sau
H 6.12 Kết quả truy vấn
- Free Download
Trang 101
Chúng ta sử dụng hàm STUFF
H 6.13 Gọi hàm STUFF()
VÍ dụ 4: Hàm UNPIVOT()
Chúng ta tạo một bảng tên Clients
create table Clients
(
clientID int primary key,
clientName varchar(100),
contact1 int,
contact2 int,
contact3 int,
contact4 int
)
Kết quả :
H 6.14 Dữ liệu Bảng Clients
- Free Download
Trang 102
Với cách tạo bảng như trên, chúng ta khó có thể đếm tất cả các lần liên hệ của mỗi khách hàng
theo một cột với cách truy vấn thông thường. Chúng ta có nhiều cách để thực hiện
Truy vấn dùng UNION ALL
select clientID, contact1 as ContactID
from clients
where contact1 is not null
union all
select clientID, contact2 as ContactID
from clients
where contact2 is not null
union all
select clientID, contact3 as ContactID
from clients
where contact3 is not null
union all
select clientID, contact4 as ContactID
from clients
where contact4 is not null
H 6.15 Kết quả truy vấn dùng Union All
- Free Download
Trang 103
Tuy nhiên cách làm trên tương đối dài và khó hiểu, do đó SQL Server 2008 cung cấp cho ta
một hàm Built-in đơn giản và dễ sử dụng là UNPIVOT().
H 6.16 Gọi hàm Unpivot()
Lưu ý:
Trước khi sử dụng hàm Unpivot(), bắt buộc ta phải gọi 1 Store hệ thống là sp_dbcmptlevel
EXEC sp_dbcmptlevel Northwind, 90
Với cú pháp:
Sp_dbcmptlevel [@dbname] [ @new_cmptlevel]
@dbname: tên CSDL
@new_cmptlevel = 80 (SQL Server 2000)
= 90 (SQL Server 2005)
= 100 (SQL Server 2008)
- Free Download
Trang 104
User Defined Function: ta có thể tự viết các Function cho riêng mình nếu như các Built-
in Function không phù hợp với yêu cầu.
Có 2 dạng User Defined Function: Scalar Function và Table-valued Function
Scalar Function: luôn trả về một giá trị cụ thể.
Cú pháp:
CREATE FUNCTION FunctionName
(@parameter datatype)
RETURNS type
AS
BEGIN
SQL Statements
RETURN Scalar expression
END
VÍ dụ: chúng ta sẽ tạo 1 hàm dùng thay thế các giá trị NULL thành NOT APPLICABLE
H 6.17 Tạo hàm NewRegion
- Free Download
Trang 105
Sau đó ta gọi hàm NewRegion vừa tạo trong câu lệnh SELECT từ bảng Employees.
H 6.18 Gọi hàm NewRegion
Table-valued Function: trả về kiểu giá trị kiểu bảng dữ liệu giống như View (sẽ được
trình bày bên dưới).
Cú pháp:
CREATE FUNCTION FunctionName
(@parameter datatype)
RETURNS Table
AS
RETURN Statement
VÍ dụ: tạo 1 function liệt kê các Nhân viên có City=@City
- Free Download
Trang 106
H 6.19 Tạo function GetEmployeeByCity
Gọi hàm GetEmployeeByCity vừa tạo với tham số @City=‟London‟
H 6.20 Gọi hàm GetEmployeeByCity
- Free Download
Trang 107
Tuy nhiên ta có thể thu được kết quả tương tự bằng cách kết hợp giữa 1 hàm Built-in là
COALESCE() và 1 hàm User Defined.
Tạo hàm User Defined tên MySubString()
H 6.21 Hàm MySubString
Sau khi gọi hàm MySubString trong câu câu lệnh SELECT ta cũng thu được kết quả tương
tự ví dụ ở phần hàm Built-in
H 6.22 Gọi hàm MySubString()
- Free Download
Trang 108
Các ví dụ minh họa
Viết hàm trả về tổng UnitPrice cùa các mặt hàng thuộc nhà cung cấp có mã số
@SupplierID
H6.23 fn_GetSumUnitPrice
Gọi hàm và xem kết quả:
Viết hàm tính tổng tiền của một khách hàng
- Free Download
Trang 109
H 6.24 Hàm fn_GetTotalAmount
Kết quả
H 6.24 gọi hàm fn_GetTotalAmount
- Free Download
Trang 110
View là một đối tượng cho phép ta có thể xem chính xác những dữ liệu cần thiết, không chỉ
một vài trường trong 1 bảng mà còn có thể từ nhiều trường từ nhiều bảng khác nhau.
Có 2 cách để tạo View:
Cách 1: tạo View trong View Designer
Khởi động SQL Server Management Studio (SSMS)
Kết nối với Server
Chọn CSDL cần làm việc
Click phải vào Views Container -> chọn New View
Trong hộp thoại Add Table -> chọn các bảng cần thiết -> Add
Click chọn các trường trong bảng vừa thêm vào
Chọn Execute SQL để xem kêt quả
Ctrl + S để lưu View
Cách 2: tạo View bằng T-SQL
Cú Pháp:
CREAE VIEW
AS
SQL Satements
Để chỉnh sửa hoặc xóa View ta dung ALTER/ DROP
VÍ dụ: tạo 1 View tên ViewOrders_Products với dữ liệu lấy từ 2 bảng Order Details và
Products
6.3 Giới thiệu về VIEW
- Free Download
Trang 111
H 6.25 Tạo ViewOrders_Details
Gọi View vừa tạo với điều kiện ProductID=1
H 6.26 Gọi view ViewOrders_Products
- Free Download
Trang 112
Các ví dụ minh họa
Tạo View thống kê số lượng mặt hàng theo từng nhà cung cấp
H 6.27 View vGetProductBySupplier
Tạo view lấy danh sách các hóa đơn có giá trị >= 10000
H 6.28 View vGetOrder
- Free Download
Trang 113
Trigger là đối tượng gắng liền với một bảng, tự động thực hiện khi xảy ra sự thay đổi dữ liệu
trong bảng như Update, Insert hay Delete. Trigger được dùng đề đảm bảo Data Integrity (toàn
vẹn dữ liệu) hay thực hiện các Business Rule(ràng buộc dữ liệu) nào đó.
Định nghĩa Trigger cần chú ý:
Trigger được tạo trong bảng nào?
Trigger được kích hoạt khi câu lệnh nào được thực thi (Insert ,Update hay Delete).
Cú pháp
CREATE TRIGGER
ON
As
Begin
End
Sử dụng trigger khi chúng ta cập nhật dữ liệu trên bảng
Cú pháp
CREATE TRIGGER
ON
FOR UPDATE
AS
BEGIN
Statements
END
6.4 Giới thiệu TRIGGER
- Free Download
Trang 114
Giả sử bạn muốn tạo ra một Trigger tự động thông báo tới người quản lý khi có một đơn hàng
mới được cập nhập.
H 6.29 Tạo trigger send mail
Chú ý: để thực hiện được câu lệnh trên chúng ta phải Enable chức năng “Database Mail XPs”
Sau khi bạn tạo thành công Trigger sẽ tự đông giám sát và thông báo khi có sự thay đổi trong
bản Orders.
Ví dụ 1: Để giám sát các hoạt động thêm ,chỉnh sử và cập nhập hàng trong table Products
chúng ta tạo ra một table Audit , với các cột AuditID, AuditType,DateTimeAudit,ProductID.
Trong đó AuditType có các giá trị „I‟,‟U‟,‟D‟ tương ứng với các hoạt động Insert, Update hay
Delete.
- Free Download
Trang 115
H 6.30 Bảng Audit
Sau đó chúng ta tạo ra Trigger dùng cho việc audit như sau
- Free Download
Trang 116
H 6.31 Trigger Insert Audit
Các ví dụ minh họa :
Chúng ta tạo một database như bên dưới, sau đó tạo các Trigger để quản lý việc thay đổi dữ
liệu
H 6.32 Tạo Database
- Free Download
Trang 117
Tạo Trigger tg_UpdateOrder
Tạo trigger tg_DeleteOrderDetail
- Free Download
Trang 118
Tạo trigger tg_UpdateCustomer
- Free Download
Trang 119
Chương 7: SQL SERVER 2008 và XML
Khái niệm về XML
XML (Extendsible Markup Language) được W3C tạo ra để trở thành một dạng dữ liệu đơn giản,
linh hoạt dựa trên StandardGeneralized Markup Language (SGML). Ngôn ngữ XML được
W3C đề xuất vào năm 1996 và công bố vào năm 1998.
W3C XML 1.0 đưa ra một tập hợp các quy tắc cho việc thêm cấu trúc và nội dung dữ liệu bằng
cách đánh dấu, tiêu chuẩn hóa việc truyền và chia sẻ dữ liệu giữa các ứng dụng.
Một số ưu điểm của XML:
- Đơn giản cho việc xử lý: dữ liệu dạng XML có thể được xử lý bởi các chương trình
chuyển đổi.
- Được truyển trực tiếp trên Internet: XML được tạo bởi các tập hợp ký tự được định
nghĩa như UTF-8, UTF-16… Được thiết kế để dễ dàng đi qua tường lửa, sử dụng các chuẩn
giao thức trên Internet như HTTP
- Đơn giản với người đọc: do được tạo bởi dạng ký tự và cấu trúc của tài liệu XML rõ ràng,
nên con người có thể đọc và hiểu nội dung bên trong dễ dàng, giúp cho việc kiểm tra lỗi
đơn giản hơn kiểu dữ liệu dạng nhị phân.
- Được tạo dễ dàng: không giống dạng dữ liệu nhị phân, XML có thể được tạo bởi một
trình soạn thảo đơn giản.
Cấu trúc của một tài liệu XML:
Kết thúc chương này các bạn có thể :
Trình bày được các khái niệm cơ bản về XML và SQL Server 2008
Trình bày và thực hiện được việc lưu trữ dữ liệu dạng XML trong bảng
Thực hiện được truy vấn dữ liệu XML
Thực hiện được Thêm , xóa , sửa dữ liệu XML trong bảng
7.1 Giới thiệu về XML và SQL Server 2008
- Free Download
Trang 120
…
…
…
Ví dụ :
1
coffee
10
20
2
sugar
10
3
XML trong SQL Server 2008
Một số đặc điểm chính mà SQL Server 2008 hỗ trợ XML:
o Kiểu dữ liệu XML (XML data type)
o Lượt đồ XML (XML Schema collections)
o Chỉ mục XML (XML indexes)
o XQuery và XML DML
Kiểu dữ liệu XML (XML data type)
Đây là một trong những điểm quan trọng trong SQL Server 2008. XML data type hỗ trợ
lưu trữ các tài liệu có định dạng XML, đồng thời được dung để khai báo các cột trong
một bảng, các biến T-SQL, tham số, kiểu tra về của 1 hàm. Hơn nữa, XML data type
mang đến một tập hợp các phương thức dùng truy vấn dữ liệu.
Giới hạn:
- Free Download
Trang 121
o Dữ liệu dạng Xml không thể vượt quá 2GB
o Không hỗ trợ chuyển đồi sang kiểu dữ liệu dạng text hoặc ntext. Có thể
dùng varchar(max) hoặc nvarchar(max) thay thế.
o Không thể so sánh hoặc sắp xếp, không thể dùng GROUP BY.
Lượt đồ XML (XML Schema)
SQL Server 2008 hỗ trợ tạo lược đổ XML phía server để có thể kiểm tra cấu trúc của tài
liệu XML.
Chỉ mục XML (XML index)
Trong SQL Server, khi ta truy vấn dữ liệu XML, trước hết dữ liệu sẽ được chuyển sang
một định dạng khác, quá trình đó gọi là shredding. Qúa trình này có thễ mất nhiều
thời gian khi truy vấn một số lượng lớn dữ liệu dạng Xml. SQL Server 2008 hỗ trợ tạo
chỉ mục cho các cột có kiểu dữ liệu XML trong bảng, giúp tối ưu hóa và nâng cao khả
năng truy vấn dữ liệu.
XQuery và XML DML
Kiểu dữ liệu XML cung cấp một số phương thức cho phép ta truy vấn hoặc chỉnh sửa dữ
liệu dạng Xml. Nhưng phương thức như query(), value(), exist(), nodes(),
modify() hỗ trợ truy vấn XQuery và XML DML..
Tạo Bảng: Để tạo một cột dạng Xml trong bảng ta dùng lệnh CREATE TABLE
Cú pháp:
CREATE TABLE
(
Col1 ,
Col2
)
Ví dụ: Chúng ta tạo 1 bảng tên XmlProducts trong CSDL Northwind gồm 2 cột ID và xmlCol;
trong đó cột xmlCol có kiểu dữ liệu XML
7.2 Cách xây dựng bảng, lưu dữ liệu dạng XML
trong bảng
- Free Download
Trang 122
Thêm dữ liệu dạng XML vào bảng
Để thêm dữ liệu vào các cột có kiểu dữ liệu XML trong bảng, ta dùng lệnh INSERT INTO
tương tự như cách thêm dữ liệu thông thường
Cú pháp:
INSERT INTO
(
…
)
VALUE
(
…
)
Chúng ta cũng có thể thêm dữ liệu dạng 1 tài liệu XML :
Cú pháp:
INSERT INTO
(
…
)
VALUE
(
7.3 Thêm, Xóa, Sửa dữ liệu XML trong bảng
- Free Download
Trang 123
…
)
Ví dụ: Thêm dữ liệu vào bảng XmlProducts vừa tạo
H 7.2 Thêm dữ liệu vào bảng XmlProducts
Thêm dữ liệu vào bảng dùng DECLARE
Chúng ta có thể dùng DECLARE để khai báo biến nhằm giúp cho câu lệnh INSERT được đơn
giản rõ ràng hơn
Cú pháp:
DECLARE [AS] xml
SET = [xml Document]
VÍ cụ: dùng DECLARE khai báo 1 biến tên @xmlVar thay thế cho cột xmlCol
- Free Download
Trang 124
H 7.3 Dùng DECLARE khai báo biến @xmlVar
Sau khi khai báo biến chúng ta thực hiện câu lệnh INSERT, khi đó dùng biến @xmlVar thay cho
cột xmlCol
- Free Download
Trang 125
H 7.5 Kết quả sau khi thêm dữ liệu
Cập nhật, Xóa dữ liệu
Đối với các thao tác chỉnh sửa nội dung vào xóa dữ liệu, chúng ta cũng thực hiện với câu lệnh
UPDATE và DELETE
Ví dụ: Thay đổi tên sản phẩm có ID=3.
- Free Download
Trang 126
H 7.7 Kết quà sau khi cập nhật
Ví dụ: Xóa dữ liệu vừa cập nhật
- Free Download
Trang 127
H 7.8 Lệnh DELETE và kết quả
Ngoài câu lệnh SELECT đơn giản đã được giới thiệu, SQL Server 2008 còn hỗ trợ một số cách
truy vấn cho kiểu dữ lệu XML
FOR XML: trả về dữ liệu dạng một tài liệu XML
Cú pháp:
SELECT
,…
FROM
FOR XML
Với mode: RAW, AUTO, EXPLICIT, PATH
VÍ Dụ: truy vấn dữ liệu của bảng xmlProducts
7.4 Truy vấn dữ liệu XML
- Free Download
Trang 128
H 7.9 Lệnh SELECT…FOR XML
Sau đó, chúng ta click vào dòng dữ liệu trả về, SQL Server sẽ cho thấy kết quả
- Free Download
Trang 129
H 7.10 Kết quả truy vấn dạng XML
Tuy nhiên, khi dùng FOR XML, dữ liệu dạng XML không đúng cấu trúc vì thiếu một tag mà ta
thường gọi là Root. Để thêm Root vào ta có thể dùng FOR XML PATH
Cú pháp:
SELECT
,…
FROM
FOR XML PATH, ROOT
VÍ Dụ: Ta thêm một root ProductList vào tài liệu XML khi truy vấn FOR XML
- Free Download
Trang 130
H 7.11 Truy vấn dùng PATH
Kết quả:
H 7.12 ProductList được thêm vào kết quả truy vấn
- Free Download
Trang 131
Ngoài việc truy vấn các bảng có dữ liệu dạng XML, ta có thể dùng FOR XML để truy vấn và xem
dữ liệu từ các bảng thông thường dưới dạng 1 tài liệu XML.
Ví dụ: truy vấn dữ liệu các khách hàng trong bảng Customers của CSDL Northwind có
CustomerID bắt đầu bằng „A‟
H 7.13 Truy vấn từ Customers
- Free Download
Trang 132
Kết quả:
H 7.14 Danh sách Khách hàng
Để tìm hiểu rõ hơn, các bạn có thể tham khảo tại
us/library/ms191268.aspx
Một số phương thức dùng cho kiểu dữ liệu XML
SQL Server cung cấp cho chúng ta một số phương thức dùng truy vấn XML, các phương thức
này được xem tương tự như Subquery, do đó không thể trong các câu lệnh PRINT hay trong
các mệnh đề GROUP BY.
Cách gọi các phương thức này tương tự khi ta gọi phương thức/hàm trong lập trình hướng đối
tượng
- Free Download
Trang 133
Hàm QUERY()
Cú pháp:
xml_obj.query ( xquery ) : Với XQuery là node trong tài liệu xml
Ví dụ: Dùng Query() để lấy tên các sản phẩm trong bảng xmlProducts
H
7.15 Liệt kê ProductName dùng Query()
Hàm Value(): trả về giá trị của các node.
Cú pháp:
xml_obj.value ( xquery, data_type )
VÍ Dụ: Lấy giá trị ProductID, ProductName trong bảng xmlProducts
- Free Download
Trang 134
H 7.16 Liệt kê các sản phẩm
Hàm Exist(): kiểm tra sự tồn tại giá trị của các node
Cú pháp:
xml_obj.exist ( xquery)
Các giá trị trả về:
0: không tồn tại giá trị bên trong node
1: tồn tại giá trị bên trong node
NULL: xml_obj không tồn tại
Ví dụ: Kiểm tra giá trị ProductName có ID=1
- Free Download
Trang 135
H 7.17 Kiểm tra tồn tại giá trị ProductName
Hàm Nodes()
Cú pháp:
xml_obj.nodes ( xquery ) AS table ( column )
Table( column ): 1 bảng tạm được tạo và chứa cột dạng xml mà dữ liệu có được là giá trị
trả về của nodes()
Ví dụ: lấy tên sản phẩm trong bảng xmlProducts truyền vào bảng myTable với cột myCol dạng
xml
- Free Download
Trang 136
H 7.18 Sử dụng Nodes()
Chỉ mục XML (XML Index)
Chỉ mục được tạo trên các cột có kiểu dữ liệu xml, giúp nâng cao hiệu quả truy vấn khi ta có
một bảng chứa một lượng lớn các dữ liệu dạng xml.
Có 2 loại chỉ mục: Primary XML Index và Secondary XML Index
Cú pháp:
CREATE [ PRIMARY ] XML INDEX index_name
ON ( xml_column_name )
[ USING XML INDEX xml_index_name
[ FOR { VALUE | PATH | PROPERTY } ]
- Free Download
Trang 137
Ví dụ: Tạo Index cho cột xmlCol trong bảng xmlProducts
H 7.19 Tạo Primary Index tên xmlColIndex
- Free Download
Trang 138
H 7.20 Tạo Secondary Index tên xmlColValueIndex
Xóa, Chỉnh sửa Index
Để xóa Index, ta dung lệnh DROP INDEX
Cú pháp:
DROP INDEX { index_name ON [ ,...n ] }
Ví dụ: xóa Secondary Index vừa tạo
- Free Download
Trang 139
H 7.21 Xóa Index
Để chỉnh sửa ta dùng ALTER INDEX
Cú pháp:
ALTER INDEX
ON REBUILD | REORGANIZE | DISABLE
VÍ Dụ: chỉnh sửa Index dung Rebuild hoặc Disable
- Free Download
Trang 140
H 7.22 Chỉnh sửa Index
Lược đồ XML (XML Schema)
XML Schema mô tả cấu trúc của tài liệu dạng XML; sử dụng cú pháp của XML
Định nghĩa các thành phần, thuộc tính có trong tài liệu.
Định nghĩa kiểu dữ liệu của các thành phần bên trong.
Xác định và sắp xếp các thuộc tính con.
Xác định thành phần nào rỗng hoặc có thể thêm dữ liệu vào đó.
Cú pháp:
Tạo Schema:
CREATE XML SCHEMA COLLECTION
AS Expression
- Free Download
Trang 141
Chỉnh sửa Schema:
ALTER XML SCHEMA COLLECTION
Xóa Schema:
DROP XML SCHEMA COLLECTION
Ví dụ: Tạo 1 schema tên ContactSchemaCollection, sau đó tạo bảng Contacts và them dữ liệu
vào bảng theo cấu trúc Schema vửa tạo
H 7.23 Tạo Schema ContactSchemaCollection
- Free Download
Trang 142
Tạo bảng Contacts gồm 1 cột Contact
Sau khi đã tạo bảng, chúng ta có thể thêm dữ liệu
Do ta đã định nghĩa trước các thành phần của XML trong Schema, nên khi ta thêm dữ liệu
không phù hợp, SQL Server sẽ kiểm tra với Schema và báo lỗi
Để tìm hiểu rõ hơn về XML Schema cũng như các cách sử dụng khác của Schema, các bạn có
thể tham khảo tại:
- Free Download
Trang 143
Chương 8 .NET Integration & SQL Server
Common Language Runtime (CLR) cung cấp các phương thức quản lý mã nguồn như
tích hợp các ngôn ngữ, bảo mật bằng mã truy cập, quản lý vòng đời các đối tượng, gỡ
lỗi… Đối với người dùng SQL Server và các nhà phát triển ứng dụng, tích hợp CLR (CLR
Integration) trong .Net nghĩa là ta có thể viết và lưu trữ Store Procedure, triggers,
user-defined types, user-defined function bằng cách sử dụng bất kỳ .Net Framework.
Lưu ý rằng, CLR Integration không dùng trong Visual Studio 2003 (Framework 1.0) .
Những lợi ích chính của CLR Integration:
Mô hình lập trình tốt hơn: các nhà phát triển có thể tận dụng sức mạnh của thư
viện .Net Framework, trong đó cung cấp một tập hợp rộng rãi các class để sử dụng
nhanh chóng và hiệu quả nhằm giải quyết các vấn đề lập trình.
Cải thiện tính an toàn và bảo mật: quản lý các mã trong môi trường CLR đượ tổ
chức trong cơ sở dữ liệu. SQL Server cung cấp một sự thay thế an toàn và bảo mật
hơn các phiên bản trước.
Khả năng xác định kiểu dữ liệu: User-defined type và user-defined aggregates
là 2 đối tượng quản lý dữ liệu mới, mở rộng khả năng lưu trữ và truy vấn SQL
Server.
Kết thúc chương này các bạn có thể :
Trình bày được các khái niệm về .NET Integration với SQL Server 20008
Trình bày và xây dựng được CLR Store Procedure
Trình bày và xây dựng được CLR User-Defined Function
Trình bày và xây dựng CLR User-Defined Type
8.1 Giới thiệu về .NET Integration & SQL Server 2008
- Free Download
Trang 144
CLR Store Procedure được sử dụng như các phương thức dạng public static. Các
phương thức static có thể khai báo dạng void hay trả về một giá trị kiểu interger.
Nếu khai báo dạng void, giá trị trả về là 0.
Giá trị trả về của Store Procedure có thể là tham số, bảng kết quả hoặc một thông
báo.
Màn hình làm việc với CLR Store Procedure trong Visual Studio 2008
H 8.1 Giao diện viết Store Procedure
8.2 Xây dựng CLR Store Procedure
- Free Download
Trang 145
Để tìm hiểu rõ hơn về cách tạo một CLR Store Procedure Integration, chúng ta sẽ
tạo môt store procedure tên GetProducts trong CSDL Northwind theo các bước sau :
Bước 1: mở VS 2008 chọn File -> New Project.
Chọn Database -> SQL Server Project
Đặt tên Project: SampleCLRStoreProcedure
H 8.2 Tạo Project mới
- Free Download
Trang 146
Sau khi tạo Project, ta chọn Database cần làm việc; trong ví dụ này ta chọn
Northwind, VS 2008 mở cửa sổ chính của project.
H 8.3 Cửa sổ chính Project
- Free Download
Trang 147
Bước 2: Thêm vào project 1 class dùng để tạo store procedure
Right click tên Project -> Add -> Store Procedure
H 8.3 Thêm Store Procedure
- Free Download
Trang 148
Bươc 4: Đặt tên Store Procedure là GetProducts
H 8.4 Tạo store GetProducts
Bước 5: Chúng ta tạo store GetProducts có 1 tham số truyền vào kiểu int để nhận
giá trị CategoryID do người dùng nhập vào
- Free Download
Trang 149
H 8.5 Viết code Store GetProducts
Chú ý: kiểu dữ liệu dùng cho @CategoryID là SqlInt32
Bước 6: Deploy Store GetProducts
Right click Project -> Deploy
- Free Download
Trang 150
H 8.6 Deploy store
Nếu kết nối được với Server, Visual Studio sẽ thông báo Deploy Succeeded. Nếu
thất bại sẽ xuất thông báo trong mục Output: Deploy Failed.
Bước 7 : Để thực thi store procedure vừa tạo chúng ta sẽ thực hiện trong SQL
Server 2008.
Chúng ta mở SQL Server Management Studio -> chọn New Query
- Free Download
Trang 151
H 8.7 Thực thi Store
User-Defined Function có thể nhận tham số, thực hiện tính toán, các thao tác khác và
trả về một giá trị nào đó.
Tương tự T-SQL Function, CLR User-Defined Function có 2 loại Function:
Scalar function trả về một giá trị đơn.
Table Valued function: trả về một tập hợp các dòng dữ liệu.
8.3 Xây dựng CLR User-Defined Function
- Free Download
Trang 152
Ví dụ: Chúng ta sẽ tạo một User-defined Function dùng kiển tra Email có hợp lệ hay
không để hiểu rõ hơn về cách tạo và sử dụng Function.
Bước 1: Add ->User-Defined Function
H 8.8 Thêm mới Function
Bước 2 : Viết code cho hàm kiểm tra Email tên ValidateEmail
- Free Download
Trang 153
H 8.9 Nội dung hàm ValidateEmail
Bước 3 :Nhấn phải chuột | Deploy thì VS.Net sẽ cài đặt hàm này vào SQL Server tự
động .
Lưu ý : Khi deploy thì VS.Net sẽ tạo một tập tin *.dll trong thư mục Bin\Debug để
chúng ta có thể cài đặt trên cơ cở dữ liệu khác bằng cách sử dụng các lệnh T-SQL .
- Free Download
Trang 154
H 8.10 Deploy hàm vừa tạo
Bước 4 : Đăng ký thư viện .dll để sử dụng trong SQL Server 2008 ( đăng ký bằng
tay sử dụng lệnh T-SQL ). Nếu chúng ta đã chọn Deploy ở bước 3 thì có thể sang
bước 5.
H 8.11 Đăng ký dll
Bước 5 : Trong trường hợp ta không sử dụng tên hàm có sẳn trong .Net , ta có thể tạo
hàm mới tên RegEx trong SQL Server 2008 dựa trên hàm đã tạo trong .Net.
Sau khi tạo hàm, chúng ta có thể gọi hàm này trong lệnh SELECT
- Free Download
Trang 155
H 8.12 Tạo và gọi hàm RegEx
SQL Server 2008 cho phép ta tạo những đối tượng cơ sở dữ liệu trong .NET Framework
CLR. Chúng ta có thể dùng User-Defined Tpyes (UDTs) để tạo ra các kiểu dữ liệu mới
để lưu trữ các đối tượng của CLR trong SQL Server. UDTs có thể chứa nhiều thành
phần và có nhiều thuộc tính khác với những kiểu dữ liệu sẵn có trong SQL Server.
Để xây dựng UDTs, ta thực hiện tương tự như CLR User-defined Function:
Viết code và biên dịch thư viện tạo UDTs trong .NET.
Đăng ký thư viện vừa tạo trong SQL Server (CREATE ASSEMBLY)
Tạo UDTs trong SQL Server
Tạo bảng, tham số sử dụng UDTs.
8.4 Xây dựng CLR User-Defined Types
- Free Download
Trang 156
Ví dụ : Tạo một User-defined Types dùng để kiểm tra Mật khẩu nhập vào trong CSDL
Bước 1: Thêm class dạng User-defined Type vào project ( tên project là ThuVien)
H 8.13 Thêm class User-defined Type
Bước 2 : Viết code cho User-defined vừa thêm vào
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
- Free Download
Trang 157
using System.IO;
[Serializable]
[SqlUserDefinedType(Format.UserDefined,
IsByteOrdered=true, MaxByteSize=8000)]
public struct MatKhau : INullable, IBinarySerialize
{
private bool m_Null;
public string m_ChuoiMK;
public override string ToString()
{
return m_ChuoiMK;
}
public bool IsNull
{
get
{
return m_Null;
}
}
public static MatKhau Null
{
get
{
MatKhau matKhau = new MatKhau();
matKhau.m_Null = true;
return matKhau;
}
}
public static MatKhau Parse(SqlString chuoi)
{
if (chuoi.IsNull)
{
return Null;
}
- Free Download
Trang 158
MatKhau matKhau = new MatKhau();
bool hopLe = Regex.IsMatch(chuoi.ToString(), "[a-zA-Z]{4,}[0-9]");
if (hopLe == true)
{
matKhau.m_ChuoiMK = chuoi.ToString();
return matKhau;
}
throw new SqlTypeException("Mat khau khong hop le.");
}
public void Write(BinaryWriter writer)
{
writer.Write(m_ChuoiMK);
}
public void Read(BinaryReader reader)
{
m_ChuoiMK = reader.ReadString();
}
}
Bước 3 : Build và Deploy User-defined Type tương tự như phần CLR User-defined
Function.
Bước 4: Đăng ký .dll vào SQL Server
- Free Download
Trang 159
H 8.14 Đăng ký dll
Bước 5 : Tạo bản Users và thêm 3 dòng dữ liệu, trong đó có dòng dữ liệu đầu tiên
không đúng với kiểu MAT_KHAU mà ta đã định nghĩa là phải bao gồm cả chữ lẫn số.
Trong ví dụ này , chúng ta tạo ra một kiểu dữ liệu tên MAT_KHAU có dạng 4 ký tự
đầu là chữ cái sau đó là các số nguyên từ 0-9
- Free Download
Trang 160
H 8.15 Thông báo lỗi sau khi nhập dữ liệu
Theo như thông báo lỗi ở trên thì chỉ có 2 dòng dữ liệu 2 và 3 được thêm vào bảng
Users, dòng đầu tiên không đúng với kiểu dữ liệu.
H 8.16 Liệt kê dữ liệu vừa thêm
- Free Download
Trang 161
Dữ liệu trong cột MyPassword đã dược chuyển sang dạng Binary trong câu lệnh
public void Write(BinaryWriter writer)
{
writer.Write(m_ChuoiMK);
}
Để có thể thấy được mật khẩu ta có thể sử dụng hàm Cast trong câu lệnh Select
Select UserName,Cast(MyPassword as varchar) as MyPassword from Users
Kết quả
Trên đây là 2 ví dụ đơn giản về User-defined Function và User-defined Type, để tìm
hiểu chi tiết hơn về CLR Integration trong SQL Server 2008. Chúng ta sử dụng kỹ
thuật này để viết các hàm phức tạp bằng cách sử dụng các ngôn ngữ .Net ( C#,
VB.Net,.. ) thay vì phải viết bằng các lệnh T-SQL .
Các bạn có thể tham khảo theo Book Online 2008 và MSDN của Microsoft.
- Free Download
Các file đính kèm theo tài liệu này:
- Giao_Trinh_SQL_Server_2008.pdf