Giới Thiệu SQL Server 2000

Bài 2: Giới Thiệu Sơ Lược Về Transact SQL (T-SQL)
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 P-
SQL (Procedural-SQL) 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. 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
• Create
object_Name

• Alter
object_Name

• Drop
object_Name

Trong đó
object_Name
có thể là một table, view, stored procedure, indexes
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
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.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ụ:
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.3. 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)
Update
USE Northwind
UPDATE Territories
SET TerritoryDescription = 'Downtown Seattle'
WHERE TerritoryID = 98101
Delete
USE Northwind
DELETE FROM Territories
WHERE TerritoryID = 98101
Vì phần này khá căn bản nên chúng tôi thiết nghĩ không cần giải thích nhiều. Chú ý trong lệnh Delete bạn có
thể có chữ From hay không đều được.
Nhưng mà chúng ta sẽ chạy thử các ví dụ trên ở đâu?
Ðể chạy các câu lệnh thí dụ ở trên bạn cần sử dụng và làm quen với Query Analyser
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 TableX, KeyCol,
Description là những identifiers
CREATE TABLE TableX
(KeyCol 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 [].
Variables (Biến)
Biến trong T-SQL cũng có chức năng tương tự như trong các ngôn ngữ lập trình khác nghĩa là cần khai báo
trước loại dữ liệu trước khi sử dụng. Biến được bắt đầu bằng dấu @ (Ðối với các global variable thì có hai dấu
@@)
Ví dụ:
USE Northwind
DECLARE @EmpIDVar INT
SET @EmpIDVar = 3
SELECT * FROM Employees
WHERE EmployeeID = @EmpIDVar + 1
Functions (Hàm)
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.
• 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
Data Type (Loại Dữ Liệu)
Các loại dữ liệu trong SQL Server sẽ được bàn kỹ trong các bài sau
Expressions
Các Expressions có dạng Identifier + Operators (như +,-,*,/,= ) + Value
Các thành phần Control-Of Flow
Như BEGIN END, BREAK, CONTINUE, GOTO, IF ELSE, RETURN, WHILE Xin xem thêm Books Online để
biết thêm về các thành phần này.
Comments (Chú Thích)
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.
Bài tập 2 SQL Server 2000
Manipulate Data and Stored Procedure
Please follow those steps to practise:
1. Use bcp to export all data from Orders table of PracticeDB to c:\Orders.txt (or to c:\Orders.csv)
2. Change some data in the c:\Orders.txt and save. Then import to Orders table from the text file using
bcp
3. Import Orders.txt to Orders table using BULK INSERT
4. Create a Linked Server ‘LinkedPracticeDB’ which link to an Access database ‘PracticeDB.mdb’
(firstly you have to create an Access database similar to PracticeDB in SQL Server and input some
data). Then do a select data using four-part name and OPENQUERY
5. Using ad hoc computer name with OPENROWSET and OPENDATASOURCE functions to select
data from ‘PracticeDB.mdb’
6. Create the following Cursor
DECLARE @au_lname varchar(40), @au_fname varchar(20)
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor INTO @au_lname, @au_fname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Author:' + @au_fname + ' ' + @au_lname
FETCH NEXT FROM Employee_Cursor INTO @au_lname, @au_fname
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
7. Create the following stored procedure and try to execute with some values
CREATE PROCEDURE AddNewOrder
@OrderID smallint,
@ProductName varchar(50),
@CustomerName varchar(50),
@Result smallint=1 Output
AS
DECLARE @CustomerID smallint
BEGIN TRANSACTION
If not Exists(SELECT CustomerID FROM Customers WHERE [Name]=@CustomerName)
BEGIN
SET @CustomerID= (SELECT Max(CustomerID) FROM Customers)
SET @CustomerID=@CustomerID+1
INSERT INTO Customers VALUES(@CustomerID,@CustomerName)
If Exists(SELECT OrderID FROM [Orders] WHERE OrderID=@OrderID)
BEGIN
SELECT @Result=1
ROLLBACK TRANSACTION
END
Else
BEGIN
INSERT INTO [Orders](OrderID,ProductName,CustomerID)
VALUES(@OrderID,@ProductName,@CustomerID)
SELECT @Result=0
COMMIT TRANSACTION
END
END
Else
BEGIN
If Exists(SELECT OrderID FROM [Orders] WHERE OrderID=@OrderID)
BEGIN
SELECT @Result=1
ROLLBACK TRANSACTION
END
Else
BEGIN
INSERT INTO [Orders](OrderID,ProductName,CustomerID)
VALUES(@OrderID,@ProductName,@CustomerID)
SELECT @Result=0
COMMIT TRANSACTION
END
END
Print @Result
Return
9. Using VB 6 or VB.NET to execute the ‘AddNewOrder’ stored procedure
10. Using xp_cmdshell extended stored procedure to send a message (xp_cmdshell ‘net send Hello’)
Bài 3: Design and Implement a SQL Server Database
3.1. Cấu Trúc Của SQL Server
Như đã trình bày ở các bài trước một trong những đặc điểm của SQL Server 2000 là Multiple-Instance
nên khi nói đến một (SQL) Server nào đó là ta nói đến một Instance của SQL Server 2000, thông thường đó
là Default Instance. Một Instance của SQL Server 2000 có 4 system databases và một hay nhiều user
database. Các system databases bao gồm:
• Master : Chứa tất cả những thông tin cấp hệ thống (system-level information) bao gồm thông tin
về các database khác trong hệ thống như vị trí của các data files, các login account và các thiết đặt
cấu hình hệ thống của SQL Server (system configuration settings).
• Tempdb : Chứa tất cả những table hay stored procedure được tạm thời tạo ra trong quá trình làm
việc bởi user hay do bản thân SQL Server engine. Các table hay stored procedure này sẽ biến mất
khi khởi động lại SQL Server hay khi ta disconnect.
• Model : Database này đóng vai trò như một bảng kẻm (template) cho các database khác. Nghĩa là
khi một user database được tạo ra thì SQL Server sẽ copy toàn bộ các system objects (tables, stored
procedures ) từ Model database sang database mới vừa tạo.
• Msdb : Database này được SQL Server Agent sử dụng để hoạch định các báo động và các công việc
cần làm (schedule alerts and jobs).
3.2. Cấu Trúc Vật Lý Của Một SQL Server Database
Mỗi một database trong SQL Server đều chứa ít nhất một data file chính (primary), có thể có thêm một hay
nhiều data file phụ (Secondary) và một transaction log file.
• Primary data file (thường có phần mở rộng .mdf) : đây là file chính chứa data và những system
tables.
• Secondary data file (thường có phần mở rộng .ndf) : đây là file phụ thường chỉ sử dụng khi
database được phân chia để chứa trên nhiều dĩa.
• Transaction log file (thường có phần mở rộng .ldf) : đây là file ghi lại tất cả những thay đổi diễn
ra trong một database và chứa đầy đủ thông tin để có thể roll back hay roll forward khi cần.
Data trong SQL Server được chứa thành từng Page 8KB và 8 page liên tục tạo thành một Extent như hình
vẽ dưới đây:
Trước khi SQL Server muốn lưu data vào một table nó cần phải dành riêng một khoảng trống trong
data file cho table đó. Những khoảng trống đó chính là các extents. Có 2 loại Extents: Mixed Extents (loại
hỗn hợp) dùng để chứa data của nhiều tables trong cùng một Extent và Uniform Extent (loại thuần nhất)
dùng để chứa data của một table. Ðầu tiên SQL Server dành các Page trong Mixed Extent để chứa data cho
một table sau đó khi data tăng trưởng thì SQL dành hẳn một Uniform Extent cho table đó.
3.3. Nguyên Tắc Hoạt Ðộng Của Transaction Log Trong SQL Server
Transaction log file trong SQL Server dùng để ghi lại các thay đổi xảy ra trong database. Quá trình này diễn ra
như sau: đầu tiên khi có một sự thay đổi data như Insert, Update, Delete được yêu cầu từ các ứng dụng, SQL
Server sẽ tải (load) data page tương ứng lên memory (vùng bộ nhớ này gọi là data cache), sau đó data trong
data cache được thay đổi(những trang bị thay đổi còn gọi là
dirty-page
). Tiếp theo mọi sự thay đổi đều được
ghi vào transaction log file cho nên người ta gọi là
write-ahead
log. Cuối cùng thì một quá trình gọi là Check
Point Process sẽ kiểm tra và viết tất cả những transaction đã được commited (hoàn tất) vào dĩa cứng
(flushing the page).
Ngoài Check Point Process những dirty-page còn được đưa vào dĩa bởi một Lazy writer. Ðây là một
anh chàng làm việc âm thầm chỉ thức giấc và quét qua phần data cache theo một chu kỳ nhất định sau đó lại
ngủ yên chờ lần quét tới.
Xin giải thích thêm một chút về khái niệm transaction trong database. Một transaction hay một giao
dịch là một loạt các hoạt động xảy ra được xem như một công việc đơn (unit of work) nghĩa là hoặc thành
công toàn bộ hoặc không làm gì cả (all or nothing). Sau đây là một ví dụ cổ điển về transaction:
Chúng ta muốn chuyển một số tiền $500 từ account A sang
account B như vậy công việc này cần làm các bước sau:
1. Trừ $500 từ account A
2. Cộng $500 vào account B
Tuy nhiên việc chuyển tiền trên phải được thực hiện dưới dạng một transaction nghĩa là giao dịch
chỉ được xem là hoàn tất (commited) khi cả hai bước trên đều thực hiện thành công. Nếu vì một lý do nào đó
ta chỉ có thể thực hiện được bước 1 (chẳng hạn như vừa xong bước 1 thì điện cúp hay máy bị treo) thì xem
như giao dịch không hoàn tất và cần phải được phục hồi lại trạng thái ban đầu (roll back).
Thế thì Check Point Process hoạt động như thế nào để có thể đảm bảo một transaction được thực
thi mà không làm "dơ" database.

Xem chi tiết: Giới Thiệu SQL Server 2000