Các khái niệm về hệ quản trị csdl



tải về 1.1 Mb.
trang7/15
Chuyển đổi dữ liệu17.08.2016
Kích1.1 Mb.
1   2   3   4   5   6   7   8   9   10   ...   15

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

    1. 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.

    1. 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.


      1. 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.

  • Biến cục bộ

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

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

      1. 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

      1. 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:



  • -- (hai dấu trừ)

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



    1. 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

      1. 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:



  • Các hàm tập hợp

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

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

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

  • Các hàm hệ thống

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

  • Hàm xếp hạ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

      1. 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ử

    1. 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.

      1. 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.


  • Ràng buộc khóa chính

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.



  • Ràng buộc NOT NULL

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:



      1. 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





      1. 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

DROP TABLE

Với:

là tên bảng dữ liệu sẽ bị xóa

Ví dụ

DROP TABLE Contacts

    1. 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.

      1. 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





      1. 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





      1. 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.





      1. 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





      1. 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

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

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.



    1. 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

      1. 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





      1. 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





      1. 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





    1. 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).

      1. 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ụ:

SELECT * FROM Employee


      1. 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ô.


      1. 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.

    1. 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

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ả.



  • Mệnh đề TABLESAMPLE

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

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

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

: files
files -> Sạt-đe rằng: Nầy là lời phán của Đấng có bảy vì thần Đức Chúa Trời và bảy ngôi sao
files -> HƯỚng dẫn càI ĐẶt và SỬ DỤng phần mềm tạo bài giảng e-learning
files -> VIỆn chăn nuôi trịnh hồng sơn khả NĂng sản xuất và giá trị giống của dòng lợN ĐỰc vcn03 luậN Án tiến sĩ NÔng nghiệp hà NỘI 2014
files -> ĐẠi học quốc gia hà NỘi trưỜng đẠi học khoa học tự nhiên nguyễn Thị Hương XÂy dựng quy trình quản lý CÁc công trìNH
files -> PHỤ LỤC 2 TỔng hợp danh mục tài liệu tham khảO
files -> BÁo cáo quy hoạch vùng sản xuất vải an toàn tỉnh bắc giang đẾn năM 2020 (Thuộc dự án nâng cao chất lượng, an toàn sản phẩm nông nghiệp và phát triển chương trình khí sinh học ) Cơ quan chủ trì
files -> BỘ TÀi nguyên và MÔi trưỜng
files -> 1. Mục tiêu đào tạo: Mục tiêu chung


1   2   3   4   5   6   7   8   9   10   ...   15


Cơ sở dữ liệu được bảo vệ bởi bản quyền ©hocday.com 2019
được sử dụng cho việc quản lý

    Quê hương