Mục tiêu :
Kết thúc chương này, các bạn sẽ có thể:
-
Liệt kê các tính năng chính của Transact-SQL
-
Hiểu được biến, kiểu dữ liệu, chú thích
-
Tóm tắt các hàm, biểu thức trong Transact-SQL
-
Giải thích ngôn ngữ định nghĩa dữ liệu và các lệnh của ngôn ngữ định nghĩa dữ liệu
-
Giải thích ngôn ngữ thao tác dữ liệu và các lệnh của ngôn ngữ thao tác dữ liệu
-
Giải thích ngôn ngữ điều khiển dữ liệu và các lệnh của ngôn ngữ điều khiển dữ liệu
-
Giải thích cách thực thi câu lệnh Transact-SQL:
-
Một câu lệnh đơn lẽ
-
Tập lệnh
-
Scripts
-
Liệt kê và giải thích những tính năng nâng cao của Transact-SQL
-
Giới thiệu:
SQL Server 2005 được xây dựng dựa trên cú pháp của SQL ANSI chuẩn và bổ sung những phần mở rộng được gọi với tên Transact-SQL. Bài học này sẻ giải thích những khái niệm của Transact-SQL, đồng thời cũng sẽ giải thích chi tiết cách tạo và thực thi những câu lệnh này để quản lý dữ liệu cũng như CSDL. SQL Server hỗ trợ 3 loại câu lệnh Transact-SQL: Ngôn ngữ định nghĩa dữ liệu gọi tắt là DDL, Ngôn ngữ điều khiển dữ liệu gọi tắt là DCL, ngôn ngữ thao tác dữ liệu gọi tắt là DML. Bên cạnh đó bài học này cũng giới thiệu một số khái niệm cơ bản như biến, hàm và một số ví dụ về cách sử dụng chúng. Ngoài ra, cách thức thực hiện câu lệnh Transact-SQL dưới các hình thức: thực hiện từng câu lệnh đơn lẽ, thực hiện dưới dạng một tập lệnh và scripts.
-
Ngôn ngữ Transact-SQL
Ngôn ngữ vấn tin có cấu trúc (SQL) là một ngôn ngữ rất phổ dụng trong lĩnh vực CSDL. Hầu hết các hệ quản trị CSDL quan hệ (RDBMS) hiện đại đều mở rộng thêm cho ngôn ngữ truy vấn của mình, kể cả SQL Server 2005. SQL có thể dùng để định nghĩa các đối tượng của CSDL, chẳng hạn bảng và chèn dữ liệu vào bảng cũng như sử dụng để thao tác dữ liệu như: thay đổi, xóa hoặc rút trích một số dữ liệu nào đó. Microsoft xây dựng Transact-SQL dựa trên ngôn ngữ vấn tin có cấu trúc chuẩn (ANSI-SQL: là ngôn ngữ vấn tin có cấu trúc do học viện quốc gia Hoa Kỳ đưa ra), ANSI-SQL thường được gọi là SQL, ngôn ngữ này là cách thức chuẩn để giao tiếp với CSDL. Transact-SQL là phiên bản nâng cao của ANSI-SQL. Nó cung cấp một ngôn ngữ bao hàm toàn diện để định nghĩa bảng, chèn, xóa, thay đổi và truy cập dữ liệu trong bảng.
Transact-SQL là một ngôn ngữ mạnh, nó hỗ trợ các tính năng khác như: kiểu dữ liệu, đối tượng tạm thời, thủ tục lưu trữ và thủ tục hệ thống. Bên cạnh đó nó còn cho phép chúng ta định nghĩa đối tượng con trỏ, khai báo biến, cấu trúc rẽ nhánh, vòng lặp, bẫy lỗi và Transaction.
Ví dụ một dưới đây minh họa câu lệnh SELECT của ngôn ngữ Transact-SQL để rút trích tất cả các bản ghi từ bảng Employee.
Ví dụ 1:
SELECT * FROM Employee
Ngôn ngữ Transact-SQL gồm nhiều cú pháp, bao gồm: khai báo biến, việc sử dụng hàm, kiểu dữ liệu, biểu thức và chú thích.
-
Khai báo biến
Biến là vùng nhớ trong bộ nhớ được đặt tên để chứa giá trị dữ liệu. Dữ liệu có thể truyền cho câu lệnh SQL bằng biến cục bộ. Biến trong Transact-SQL được khai báo và sử dụng tạm thời trong khi thực thi lệnh. Tên biến cục bộ phải khai báo bắt đầu bằng ký hiệu @. Biến có thể phân thành 2 loại là biến cục bộ và biến toàn cục.
Trong Transact-SQL, biến cục bộ được khai báo và sử dụng tạm thời khi thực thi câu lệnh SQL.
Cú pháp:
DECLARE @local_variable [AS] data_type
Với:
- @local_variable: tên biến cục bộ, phải bắt đầu bằng ký hiệu @
- data_type: kiểu dữ liệu hệ thống hoặc kiểu dữ liệu người dùng
Câu lệnh SET hoặc SELECT dùng để gán giá trị cho biến
Cú pháp
SET @local_variable = value
OR
SELECT @local_variable = value
Hình 5.1 minh họa cách sử dụng biến cục bộ để rút trích dữ liệu từ bảng Employee.
Hình 5.1: Sử dụng biến cục bộ
Biến toàn cục là biến có sẵn và hệ thống quản lý. Biến toàn cục trong SQL Server được đặt tên bắt đầu bởi hai ký hiệu @. Giá trị của biến toàn cục có thể rút trích dễ dàng với câu lệnh SELECT như minh họa dưới đây:
Hình 5.2: Biến toàn cục
-
Kiểu dữ liệu trong Transact-SQL
Kiểu dữ liệu là thuộc tính định nghĩa loại dữ liệu mà đối tượng có thể chứa. Chẳng hạn như cột, tham số, biến, hàm trả về giá trị hoặc thủ tục lưu trữ trả về giá trị, thì tất cả đều được khai báo kiểu dữ liệu. Transact-SQL bao gồm nhiều kiểu dữ liệu chẳng hạn như: varchar, text, int,.... Tất cả dữ liệu lưu trữ trong SQL Server đều phải tương ứng với những kiểu dữ liệu đó.
Các đối tượng dưới đây sử dụng kiểu dữ liệu:
-
Cột trong bảng
-
Tham số trong thủ tục lưu trữ
-
Biến
-
Hàm trả về giá trị
-
Thủ tục lưu trữ trả về giá trị
Bảng dưới đây liệt kê tất cả các kiểu dữ liệu hỗ trợ trong SQL Server 2005.
Kiểu dữ liệu trong SQL Server 2005
|
|
Hình 5.3: Kiểu dữ liệu trong SQL Server 2005
-
Chú thích trong Transact-SQL
Chú thích là các văn bản diễn giải trong chương trình (đoạn lệnh) mà trình biên dịch sẽ bỏ qua. Chúng ta có thể sử dụng chú thích trong từng câu lệnh, tập lệnh hoặc dùng trong thủ tục lưu trữ. Mục đích của việc dùng chú thích là giải thích mã lệnh để bảo trì hoặc sửa đổi về sau dễ dàng.
Microsoft SQL Server hỗ trợ hai loại chú thích sau:
Toàn bộ dòng lệnh hoặc một phần của dòng lệnh được chú thích. Nếu -- được đặt ở đầu dòng thì toàn bộ dòng lệnh là chú thích.
Ví dụ dưới đây sử dụng loại chú thích này:
USE AdventureWorks
--Bảng Employee chứa thông tin chi tiết của nhân viên
--Câu lệnh này lấy tất cả các dòng trong bảng Employee
SELECT * FROM Employee
-
/* …*/ : chú thích nhiều dòng
Loại chú thích này bắt đầu bằng /* và kết thúc bằng */. Tất cả các dòng lệnh hay văn bản ở giữa cặp dấu này được xem là chú thích. Ví dụ dưới đây minh họa cách sử dụng loại chú thích này.
/*Bảng Employee chứa thông tin chi tiết của nhân viên
Câu lệnh này lấy tất cả các dòng trong bảng Employee */
SELECT * FROM Employee
-
Hàm, biểu thức trong Transact-SQL
-
Hàm là tập lệnh Transact-SQL để thực hiện công việc nào đó.
-
Biểu thức là kết hợp nhiều phần tử: toán tử, giá trị, định danh mà SQL Server có thể ước tính được để trả về một kết quả. Biểu thức có thể sử dụng trong nhiều trường hợp khi truy cập hoặc thay đổi dữ liệu
-
Hàm trong Transact-SQL
Transact-SQL hỗ trợ rất nhiều hàm. Những hàm này rất hữu dụng để tính toán hoặc thao tác dữ liệu. Trong SQL, hàm làm việc với dữ liệu, nhóm dữ liệu để trả về một kết quả mong đợi. Hàm cũng được sử dụng trong câu lệnh SELECT hoặc bất kỳ đâu trong biểu thức.
Transact-SQL có các hàm sau:
Transact-SQL hỗ trợ các hàm tập hợp để thống kê dữ liệu. Bảng 5.2 liệt kê các hàm tập hợp:
-
Hàm
|
Trả về giá trị
|
Ví dụ
|
SUM(col_name)
|
Hàm tính tổng, trả về tổng giá trị của col_name
|
SELECT SUM(OrderQty) AS Total FROM OrderDetail
|
AVG(col_name)
|
Trả tính giá trị trung bình
|
SELECT AVG(UnitPrice * OrderQty)
AS AveragePrice FROM
OrderDetail
|
COUNT(*)
COUNT(col_name)
|
Đếm số bản ghi trong bảng
|
SELECT COUNT(*) AS ‘Number of
Large Orders’ FROM Purchasing.
PurchaseOrderDetail WHERE
OrderQty > 100
|
MAX(col_name)
|
Trả về giá trị lớn nhất
|
SELECT MAX(OrderQty * UnitPrice)
AS ‘Largest Order’ FROM Purchasing.
PurchaseOrderDetail
|
MIN(col_name)
|
Trả về giá trị nhỏ nhất
|
SELECT MIN(OrderQty * UnitPrice) AS
‘Smallest Order’ FROM Purchasing.
PurchaseOrderDetail
|
Bảng 5.2 – Danh sách các hàm tập hợp
-
Hàm chuyển đổi kiểu dữ liệu
Hàm chuyển đổi CONVERT được dùng để chuyển đổi kiểu dữ liệu. Ngoài ra nó còn được dùng để hiển thị giá trị ngày tháng theo định dạng nào đó.
Cú pháp:
CONVERT(datatype[(length)],expression [,style])
Với:
-
datatype: là kiểu dữ liệu cần chuyển sang
-
Length : chỉ ra độ dài của dữ liệu
-
Expression: chỉ ra tên cột, hằng số, hàm, biến hoặc là câu truy vấn con
-
Style: chỉ ra kiểu hiển thị giá trị ngày tháng(dd/mm/yyyy hay mm/dd/yyyy)
Trong bảng dữ liệu Employee, chúng ta muốn hiển thị chuỗi “Employee ID” (ví dụ: Employee ID: 1 ) trước giá trị của trường EmployeeID. Nhưng do giá trị trường EmployeeID là trường số nên chúng ta không thể cộng (ghép) hai giá trị này được trong kết quả truy vấn. Chính vì vậy mà chúng ta cần chuyển đổi giá trị của trường EmployeeID thành kiểu ký tự và ghép với chuỗi “Employee ID”. Xem minh họa cách sử dụng hàm CONVERT dưới đây:
Hình 5.3 – Sử dụng hàm CONVERT
Hàm ngày tháng được dùng để thao tác giá trị ngày tháng. Hàm này dùng để tính toán dựa trên giá trị ngày tháng. Hàm ngày tháng cho phép chúng ta lấy một phần của giá trị ngày tháng như: lấy ngày, tháng hoặc năm.
DATEPART: là một phần giá trị ngày tháng, được kết hợp sử dụng trong hàm ngày tháng để lấy một phần nào đó của giá trị ngày tháng. Bảng dưới đây liệt kê tất cả các tùy chọn DATEPART được hỗ trợ trong Transact-SQL:
-
DATEPART
|
Abbreviation
|
Values
|
Hour
|
hh
|
0-23
|
Minute
|
Mi
|
0-59
|
Second
|
Ss
|
0-59
|
Millisecond
|
Ms
|
0-999
|
Day of Year
|
Dy
|
1-366
|
Day
|
Dd
|
1-31
|
Week
|
wk
|
1-53
|
Weekday
|
Dw
|
1-7
|
Month
|
Mm
|
1-12
|
Quarter
|
Qq
|
1-4
|
Year
|
Yy
|
1753-9999
|
Bảng 5.3 – Tùy chọn DatePart
Bảng 5.4 dưới đây liệt kê các hàm ngày tháng trong SQL Server 2005
-
Hàm
|
Trả về giá trị
|
Ví dụ
|
GETDATE()
|
Trả về ngày hiện tại của hệ thống
|
SELECT GETDATE()
|
DATEADD(datepart,number,date)
|
Cộng vào thêm number giá trị vào date
|
SELECT DATEADD(mm,4,’01/01/99’)
Kết quả trả về: 05/01/99
|
DATEDIFF(datepart,date1,date2)
|
So sánh giữa hai giá trị ngày tháng
|
SELECT DATEDIFF(mm,’01/
01/99’,’05/01/99’) – trả về 4
|
DATENAME(datepart,date)
|
Trả về giá trị ngày tháng dưới dạng chuỗi
|
SELECT DATENAME(dw,’01
/01/2000’)
Trả về: Saturday
|
DATEPART(datepart,date)
|
Trả về một phần giá trị ngày tháng
|
SELECT DATEPART(day,’01
/15/2000’)
Trả về: 5
|
Bảng 5.4: Các hàm ngày tháng
Hàm toán học thực hiện các phép tính đại số trên trường dữ liệu/giá trị số. Bảng dưới đây liệt kê tất cả các hàm toán học thông dụng trong SQL Server
-
Hàm
|
Trả về giá trị
|
Ví dụ
|
ABS(num_exp)
|
Trả về giá trị tuyệt đối
|
SELECT ABS(-43)
Trả về: 43
|
CEILING(num_expr)
|
Trả về giá trị nhỏ nhất lớn hơn hoặc bằng num_expr
|
SELECT CEILING(43.5)
Trả về: 44
|
FLOOR(num_expr)
|
Trả về giá trị lớn nhất nhỏ hơn hoặc bằng num_expr
|
SELECT FLOOR(43.5)
Trả về: 43
|
POWER(num_expr,y)
|
Hàm lũy thừa
|
SELECT POWER(5,2)
Trả về: 25
|
ROUND(num_expr,length)
|
Hàm làm tròn
|
SELECT ROUND(43.543,1)
Trả về: 43.5
|
SIGN(num_expr)
|
Trả về +1 nếu num_expr là số dương, -1 nếu num_expr là số âm, 0 nếu num_expr bằng 0
|
SELECT SIGN(-43)
Trả về: -1
|
SQRT(float_expr)
|
Hàm căn bậc hai
|
SELECT SQRT(9)
Trả về: 3
|
Bảng 5.5: Các hàm toán học
SQL Server cung cấp các hàm hệ thống để trả về dữ liệu hệ thống hay thông tin cấu hình của SQL Server. Bảng dưới đây liệt kê tất cả các hàm thông dụng.
-
Hàm
|
Trả về giá trị
|
DB_ID([‘database_name’])
|
Trả về số định danh của CSDL
|
DB_NAME([database_id])
|
Trả về tên CSDL
|
HOST_ID()
|
Trả về số định danh cho máy trạm
|
HOST_NAME()
|
Trả về tên máy trạm
|
ISNULL(expr,value)
|
Nếu expr là NULL thì được thay thế bằng giá trị VALUE
|
OBJECT_ID(‘obj_name’)
|
Số định danh đối tượng
|
OBJECT_NAME(object_id)
|
Trả về tên đối tượng
|
USER_ID([‘user_name’])
|
Số định danh người dùng
|
USER_NAME([user_id])
|
Tên người dùng
|
Bảng 5.6: Hàm hệ thống
Đây là các hàm mới được bổ sung trong SQL Server 2005. Một số công việc như tạo ra dãy số tuần tự, xếp hạng bây giờ dễ dàng thực hiện được trong SQL Server 2005. Đây là một tính năng nâng cao trong Transact-SQL. Bảng dưới đây liệt kê chi tiết các hàm xếp hạng thông dụng trong SQL Server.
-
Hàm
|
Trả về giá trị
|
ROW_NUMBER()
|
Hàm đánh số hàng, bắt đầu bằng 1 với hàng đầu tiên và 2, 3 với các hàng tiếp theo
|
DENSE_RANK()
|
Trả về giá trị xếp hạng giữa các hàng mà không có khoảng cách giữa các hạng
|
Bảng 5.7 – Danh sách các hàm xếp hạng
-
Biểu thức trong Transact-SQL
Biểu thức là kết hợp nhiều phần tử: toán tử, giá trị, định danh mà SQL Server có thể ước tính được để trả về một kết quả. Biểu thức có thể được sử dụng trong nhiều trường hợp khi truy cập hoặc thay đổi dữ liệu.
-
Sử dụng toán tử trong biểu thức
Toán tử được dùng để thực hiện tính toán, so sánh, ghép hoặc gán giá trị. Ví dụ, chúng ta kiểm tra xem trường COUNTRY có nhập dữ liệu hay không bằng toán tử ISNULL.
Bảng dưới đây liệt kê tất cả các toán tử được hỗ trợ trong SQL Server 2005.
-
Toán tử
|
Mô tả
|
So sánh
|
So sánh các giá trị dữ liệu
|
Logic
|
Toán tử luận lí, dùng kiểm tra điều kiện đúng hay sai. Các toán tử luận lí: AND, OR, NOT, LIKE, ANY, ALL hoặc IN
|
Số học
|
Toán tử thực hiện các phép tính số học như: +,-,*,/
|
Toán tử một ngôi
|
Toán tử thực hiện trên một toán hạng
|
Bitwise
|
Các toán tử thực hiện trên các bit dữ liệu
|
Hàm ghép chuỗi
|
Ghép hai chuỗi thành một chuỗi
|
Gán giá trị
|
Gán giá trị cho biến
|
Bảng 5.8- Các loại toán tử
-
Ngôn ngữ định nghĩa dữ liệu (DDL)
Ngôn ngữ định nghĩa dữ liệu là một trong số những loại của ngôn ngữ SQL. Ngôn ngữ DDL được dùng để định nghĩa (xây dựng), thay đổi hoặc xóa cấu trúc của các đối tượng CSDL, chẳng hạn: bảng, view, trigger, thủ tục lưu trữ, …
Với mỗi đối tượng đó chúng ta thường dùng các câu lệnh CREATE, ALTER, DROP để tạo, thay đổi và xóa cấu trúc đối tượng.
Cú pháp của ngôn ngữ định nghĩa dữ liệu (DDL) như dưới đây:
-
CREATE OBJECT_NAME
-
ALTER OBJECT_NAME
-
DROP OBJECT_NAME
Với OBJECT_NAME có thể là TABLE, VIEW, TRIGGER, STORED PROCEDURE… Trong bài này chúng ta sử dụng DDL để tạo, thay đổi, xóa bảng. Với các đối tượng khác, cú pháp có khác đi đôi chút tùy theo mỗi loại đối tượng và sẽ được giải thích chi tiết trong những bài tương ứng.
-
Lệnh CREATE TABLE
Lệnh CREATE TABLE dùng để tạo cấu trúc bảng trong CSDL hiện tại. Chỉ có những người dùng nào có quyền tạo thì mới được tạo.
Cú pháp:
Ví dụ:
Ví dụ trên tạo bảng Contacts trong CSDL hiện tại. Cấu trúc bảng bao gồm 3 trường: MailID, Name, và TelephoneNumber.
Bảng trong CSDL SQL Server có thể có nhiều loại thuộc tính khác nhau nhằm đảm bảo toàn vẹn dữ liệu. Các thuộc tính bao gồm: kiểu dữ liệu, NOT NULL, giá trị mặc định, giá trị tự động tăng, ràng buộc, trigger và chỉ mục.
Mỗi bảng thường có một cột hoặc nhiều cột có giá trị có thể phân biệt duy nhất mỗi hàng. Cột này hoặc nhóm cột này được gọi là khóa chính, mục đích để đảm bảo toàn vẹn thực thể. Mã dưới đây minh họa cách định nghĩa khóa chính:
Định nghĩa khóa chính:
Khóa chính là một trường hay nhiều trường kết hợp để phân biệt duy nhất mỗi hàng trong bảng.
Giá trị NULL có nghĩa là trường đó không chứa dữ liệu. NULL khác với giá trị 0 đối với kiểu dữ liệu số và chuỗi rỗng với kiểu dữ liệu ký tự. Thiết lập ràng buộc NOT NULL là đảm bảo rằng giá trị dữ liệu của trường đó phải được nhập. Dưới đây là cú pháp thiết lập ràng buộc NOT NULL:
-
Lệnh ALTER TABLE
Lệnh ALTER TABLE dùng để thay đổi cấu trúc bảng. Sử dụng ALTER TABLE để thêm, sửa hoặc xóa cột trong bảng cũng như các ràng buộc.
Cú pháp:
Với:
là tên bảng cần thay đổi
là thay đổi cột
là tên cột cần bổ sung, thay đổi hay xóa
là kiểu dữ liệu thay đổi cho cột
ADD là bổ sung cột vào bảng
DROP COLUMN là xóa cột từ bảng
Ví dụ dưới đây bổ sung thêm cột FaxNumber vào bảng Contacts
-
Lệnh DROP TABLE
Lệnh DROP TABLE dùng để xóa bảng khỏi CSDL, khi xóa bảng; tất cả các đối tượng thuộc bảng như: chỉ mục, trigger, ràng buộc, và quyền chỉ định cho bảng đó đều bị xóa.
Cú pháp
Với:
là tên bảng dữ liệu sẽ bị xóa
Ví dụ
-
Ngôn ngữ thao tác dữ liệu – (DML: Data Manipulation Language)
Ngôn ngữ thao tác dữ liệu bao gồm các câu lệnh: SELECT, INSERT, UPDATE, DELETE, dùng để thao tác với dữ liệu trong bảng.
-
Câu lệnh SELECT
Câu lệnh SELECT dùng để rút trích dữ liệu từ CSDL, lệnh này cho phép chúng ta rút trích một hàng hoặc nhiều hàng, một hoặc nhiều cột.
Cú pháp
Với
là tên của bảng dữ liệu
là tên của cột hoặc danh sách cột cần lấy dữ liệu
Ví dụ dưới đây lấy tất cả các hàng từ bảng Employee
-
Câu lệnh INSERT
Lệnh INSERT dùng để chèn dữ liệu vào bảng
Cú pháp như sau:
Với
là tên bảng mà dữ liệu được chèn vào
[INTO] là từ khóa (tùy chọn)
là các giá trị sẽ chèn vào các cột tương ứng trong bảng
Ví dụ dưới đây minh họa việc sử dụng câu lệnh INSERT để chèn dữ liệu vào bảng Contacts
-
Câu lệnh UPDATE
Lệnh UPDATE dùng để thay đổi dữ liệu trong bảng.
Cú pháp
Với
là tên bảng mà dữ liệu trong đó được thay đổi
là tên cột dữ liệu được thay đổi
là giá trị dữ liệu thay đổi
là điều kiện thỏa mãn mà các dữ liệu sẽ thay đổi
Ví dụ dưới đây minh họa cách sử dụng câu lệnh UPDATE để thay đổi dữ liệu của trường TelephoneNumber trong bảng Contacts.
-
Câu lệnh DELETE
Câu lệnh DELETE dùng để xóa các hàng dữ liệu trong bảng
Cú pháp
là tên bảng cần xóa
là điều kiện kiểm tra để xóa dữ liệu, dữ liệu thỏa mãn điều kiện sẽ bị xóa. Nếu không có điều kiện WHERE thì tất cả các hàng dữ liệu trong bảng đều bị xóa.
Ví dụ dưới đây xóa bản ghi trong bảng Contacts có TelephoneNumber là 5432679
-
Câu lệnh DML mới trong SQL Server 2005
Microsoft SQL Server 2005 cung cấp một số câu lệnh mới như sau:
CTE là đối tượng chứa kết quả tạm thời của câu lệnh SELECT và INSERT. Ví dụ dưới đây minh họa việc sử dụng CTE:
Ví dụ trên chèn một hàng dữ liệu vào bảng Employee từ bảng NewEmployees sử dụng bảng tạm EmployeeTemp.
Mệnh đề OUTPUT trả về các bản ghi thực hiện được của câu lệnh INSERT và UPDATE.
Hình 5.4: Sử dụng mệnh đề OUTPUT
Trong ví dụ trên, lệnh CREATE TABLE tạo bảng OutputTest và chèn vào hai bản ghi bằng câu lệnh INSERT. Dòng lệnh UPDATE thay đổi dữ liệu trong trường Name thành chữ hoa. Dòng lệnh OUTPUT trả về giá trị của trường Name trước và sau khi thay đổi (DELETED.Name, INSERTED.Name) sau đó chèn vào bảng @UpdatedTable. Cuối cùng câu lệnh SELECT hiển thị dữ liệu trong bảng @UpdatedTable.
Mệnh đề WRITE dùng trong câu lệnh UPDATE để thay thế dữ liệu với giá trị mới trong những trường kiểu dữ liệu kích thước lớn.
Cú pháp
Với
là chuỗi ký tự dùng thay thế giá trị trong cột dữ liệu
@offset là vị trí bắt đầu thay thế
@length là độ dài thay thế tính từ @offset
Hình 5.5: Sử dụng mệnh đề .WRITE
Trong ví dụ trên sử dụng mệnh đề .WRITE để thay đổi một phần dữ liệu trong trường Summary.
-
Ngôn ngữ điều khiển dữ liệu - DCL
Dữ liệu là một phần quan trọng của CSDL, do vậy vấn đề kiểm tra sự cấp phép của mỗi người dùng cũng cần được xem trọng. Chẳng hạn người dùng 1 thì được phép thay đổi dữ liệu trong bảng 1 nhưng với người dùng 2 thì chỉ được xem dữ liệu trong bảng 1 mà thôi. Ngôn ngữ định nghĩa dữ liệu dùng để thiết lập quyền truy cập trên các đối tượng CSDL. Quyền được quản lý bởi các lệnh GRANT, REVOKE, DENY. Các lệnh này dùng để thiết lập bảo mật CSDL.
Có 3 lệnh cơ bản của DCL như sau:
-
Lệnh GRANT
-
Lệnh REVOKE
-
Lệnh DENY
-
Lệnh GRANT
Dùng để gán quyền các đối tượng CSDL cho người dùng. Quyền được quản lý bởi các lệnh GRANT, REVOKE, DENY.
Cú pháp
Với
ALL: gán tất cả các quyền cho đối tượng với người dùng đó
Statement: các lệnh được gán cho người dùng đó
To Security_Account: quyền sẽ được gán cho người dùng này. Người dùng có thể là một trong 4 loại sau:
-
Người dùng SQL Server
-
Role của SQL Server
-
Tài khoản Windows
-
Nhóm tài khoản Windows
Table_Name là tên bảng mà quyền sẽ gán trên đó
Ví dụ dưới đây gán quyền SELECT cho người dùng JOHN trên bảng Employee
-
Lệnh REVOKE
Lệnh REVOKE dùng để xóa quyền đã gán trên các đối tượng của người dùng.
Cú pháp
Ví dụ dưới đây xóa quyền SELECT trên bảng Employee của người dùng JOHN
-
Lệnh DENY
Lệnh DENY dùng để ngăn quyền của người dùng.
Cú pháp
Ví dụ dưới đây ngăn quyền SELECT trên bảng Employee của người dùng JOHN
-
Thực thi lệnh Transact-SQL
SQL Server 2005 hỗ trợ nhiều cách thực thi câu lệnh Transact-SQL. Các câu lệnh có thể thực thi dưới dạng từng câu lệnh đơn lẻ hoặc tập lệnh(gồm nhiều câu lệnh Transact-SQL).
-
Thực thi từng lệnh đơn lẻ
Xử lý từng câu lệnh là cách thông thường hay dùng. Chúng ta thường thấy đó là câu lệnh SELECT.
Câu lệnh SELECT là câu lệnh phi thủ tục, nó không chỉ rõ các bước chính xác mà CSDL thực thi rút trích dữ liệu. Nói cách khác, server phải phân tích câu lệnh để xác định cách lấy dữ liệu hiệu quả nhất. Quá trình này được gọi là tối ưu câu lệnh SELECT và đối tượng thực hiện tối ưu này được gọi là bộ tối ưu truy vấn.
Ví dụ:
-
Xử lý lô
Xử lý lô là một hoặc nhiều câu lệnh Transact-SQL được gửi cùng một lần để yêu cầu SQL Server xử lý. Các câu lệnh này được biên dịch thành một đơn vị thực thi và được gọi là “execution plan”. Các câu lệnh trong “execution plan” được thực hiện cùng một lúc.
Quá trình thực thi tập câu lệnh cùng một lúc được gọi là xử lý lô.
Ưu điểm của xử lý lô là việc quản lý CSDL dễ dàng hơn. Xử lý lô trong SQL Server 2005 được cài đặt trong thủ tục lưu trữ (stored procedures). Thủ tục lưu trữ là nhóm các câu lệnh Transact-SQL được biên dịch thành một “execution plan”. Thủ tục lưu trữ giúp nhất quán trong việc lập trình ứng dụng. Những câu lệnh nào thực hiện công việc một cách lặp lại thì chúng ta nên xây dựng trong thủ tục lưu trữ để tăng tính hiệu quả thực thi. Mỗi khi đã xây dựng thủ tục lưu trữ, các ứng dụng khác cần sử dụng chỉ cần gọi tên thủ tục để thực thi. Ví dụ dưới đây minh họa việc xử lý lô:
USE AdvantureWorks
GO
SELECT * FROM Employee
GO
|
Trong ví dụ trên, mỗi câu lệnh được thi trong một “execution plan”. Lệnh GO là ký hiệu kết thúc lô.
-
Thực thi dưới dạng scripts
Các câu lệnh SQL có thể được thực thi dưới dạng tập tin được lưu trong file. Phần mở rộng của file thường lưu dưới dạng *.sql. Tập tin sẽ được đọc khi được yêu cầu để thực thi.
-
Các nâng cấp trong Transact-SQL
Các nâng cao đáng kể trong Transact-SQL như dưới đây:
Mệnh đề TOP trong SQL Server 2005 cho phép chúng ta chỉ ra số bản ghi, phần trăm cụ thể hoặc là kết quả của biểu thức. Mệnh đề TOP bây giờ còn dùng được trong cả mệnh đề INSERT, UPDATE, DELETE, SELECT.
Cú pháp
TOP (expression) [PERCENT] [WITH TIES]
|
Với
Expression là biểu thức/số hàng muốn trả về
PERCENT chỉ ra phần trăm số bản ghi muốn trả về
WITH TIES trả về tất cả các bản ghi mà có cùng giá trong bản ghi cuối có cùng giá trị
Hình 5.6 minh họa việc sử dụng mệnh đề TOP trong SQL Server 2005
Hình 5.6. Kết quả của việc sử dụng mện đề TOP
Trong ví dụ trên, chúng ta chỉ muốn lấy 6 bản ghi trong mệnh đề TOP, nhưng khi sử dụng WITH TIES thì kết quả sẽ trả về 7 bản ghi trong bảng kết quả.
Trả về ngẫu nhiên một số bản ghi trong bảng dữ liệu
Cú pháp
Với
SYSTEM chỉ ra thuật toán bóc mẫu theo cú pháp ANSI. Đó là thuật toán duy nhất có trong SQL Server 2005 và được thiết lập mặc định.
Sample_number [PERCENT | ROWS] là số bản ghi muốn trả về
REPEATABLE (repeat_seed) để chọn hàng trả về, REPEATABLE chỉ ra hàng lựa chọn có thể lặp lại.
Hình 5.7 dưới đây minh họa cách sử dụng mệnh đề TABLESAMPLE trong SQL Server 2005
Hình 5.7: sử dụng mệnh đề TABLESAMPLE
Mệnh đề OUTPUT dùng để trả về tất những bản ghi được thực hiện bởi các lệnh INSERT, UPDATE, DELETE.
Cú pháp
Với
@table_name là bảng tạm chứa kết quả thực hiện được
column_list là danh sách cột trên bảng đích mà các trường dữ liệu được chèn
scalar_expression là bất kỳ biểu thức trả về đơn trị
column_alias_identifier là bí danh cột
from_table_name là tên bảng dữ liệu
column_name là tên cột dữ liệu
DELETED là tên bảng dữ liệu chứa dữ liệu bị xóa, hoặc dữ liệu bị thay đổi
INSERTED là tên bảng chứa dữ liệu sẽ chèn vào hoặc sẽ thay đổi
Ví dụ trên đây cập nhật cột Price trong bảng Hotels với giá mới 3000 cho trường có HotelNumber bằng 101. Mệnh đề OUTPUT trả về giá cũ trước khi thay đổi và giá mới sau khi thay đổi. Và cuối cùng câu lệnh SELECT hiển thị tất cả các bản ghi trong bảng tạm @PriceChangeDetails.
Toán tử PIVOT và UNPIVOT dùng để thống kê dữ liệu. Pivot chuyển cột sang hàng và Unpivot chuyển hàng sang cột.
Cú pháp
Với
Table_source là bảng dữ liệu nguồn
Table_alias là bảng bí danh
Aggregrate_function là các hàm tập hợp
Value_column là cột chứa dữ liệu cần PIVOT
Pivot_column là cột chứa dữ liệu cần thống kê(sử dụng hàm tập hợp để thống kê)
là cột pivot sang bảng kết quả
Ví dụ dưới đây tính tổng doanh số bán hàng theo mỗi nhân viên trong năm 2002 và năm 2003.
Hình 5.8: sử dụng toán tử PIVOT
Cú pháp UNPIVOT
Hình 5.9: Sử dụng toán tử UNPIVOT
Trong CSDL AdventureWorks có bảng Teams. Các cột Player1, Player2, Player3 và Player4 trong bảng Teams được chuyển thành các hàng trong bảng dữ liệu kết quả. Hai cột Player và Goals là hai cột dùng để chuyển đổi; cột Player là cột dùng để chuyển đổi và cột Goals chứa giá trị để thống kê.
TÓM TẮT BÀI HỌC
-
Transact-SQL ngôn ngữ mở rộng của hai ngôn ngữ vấn tin chuẩn đó là: ANSI-SQL và ISO-SQL.
-
DDL(ngôn ngữ định nghĩa dữ liệu) là ngôn ngữ dùng để tạo, thay đổi và xóa cấu trúc đối tượng.
-
DML(ngôn ngữ thao tác dữ liệu) là ngôn ngữ dùng để thao tác với dữ liệu trong bảng. Các câu lệnh gồm có: INSERT, UPDATE, DELETE và SELECT.
-
DCL(ngôn ngữ điều khiển dữ liệu) là ngôn ngữ dùng để gán hoặc xóa quyền trên các đối tượng.
-
Một số câu lệnh mới trong SQL Server 2005 bao gồm:
-
Mệnh đề TOP
-
Mệnh đề OUTPUT
-
Mệnh đề TABLESAMPLE
-
Toán tử PIVOT và UNPIVOT
-
Trong Transact-SQL chúng ta có thể khai báo biến để chứa dữ liệu tạm thời trong khi thực thi lô.
-
Lệnh Transact-SQL có thể thực thi dưới nhiều hình thức:
-
Lệnh đơn
-
Lô (tập lệnh)
-
Scripts
|
Chia sẻ với bạn bè của bạn: |