Là tập hợp các chương trình dùng để quản lý cấu trúc và dữ liệu của cơ sở dữ liệu đồng thời điều khiển việc truy xuất dữ liệu trong cơ sở dữ liệu
Cung cấp cho người dùng và ứng dụng một môi trường thuận tiện và sử dụng hiệu quả tài nguyên dữ liệu

Ví dụ: Một số hệ quản trị CSDL thường gặp:

MS Access
MS SQL Server
MySQL
Oracle

Microsoft SQL Server:

Là hệ quản trị cơ sở dữ liệu do Microsoft phát triển.
Hoạt động theo mô hình Client/Server cho phép đồng thời cùng một lúc nhiều người dùng truy xuất đến cơ sở dữ liệu

SQL, viết tắt của Structured Query Language (ngôn ngữ hỏi có cấu trúc), là công cụ sử dụng để tổ chức, quản lý và truy xuất dữ liệu đuợc lưu trữ trong các cơ sở dữ liệu. SQL là một hệ thống ngôn ngữ bao gồm tập các câu lệnh sử dụng để tương tác với cơ sở dữ liệu quan hệ. Tên gọi ngôn ngữ hỏi có cấu trúc phần nào làm chúng ta liên tưởng đến một công cụ (ngôn ngữ) dùng để truy xuất dữ liệu trong các cơ sở dữ liệu. Thực sự mà nói, khả năng của SQL vượt xa so với một công cụ truy xuất dữ liệu, mặc dù đây là mục đích ban đầu khi SQL được xây dựng nên và truy xuất dữ liệu vẫn còn là một trong những chức năng quan trọng của nó. SQL được sử dụng để điều khiển tất cả các chức năng mà một hệ quản trị cơ sở dữ liệu cung cấp cho người dùng bao gồm:

Định nghĩa dữ liệu: SQL cung cấp khả năng định nghĩa các cơ sở dữ liệu, các cấu trúc lưu trữ và tổ chức dữ liệu cũng như mối quan hệ giữa các thành phần dữ liệu.
Truy xuất và thao tác dữ liệu: Với SQL, người dùng có thể dễ dàng thực hiện các thao tác truy xuất, bổ sung, cập nhật và loại bỏ dữ liệu trong các cơ sở dữ liệu.
Điều khiển truy cập: SQL có thể được sử dụng để cấp phát và kiểm soát các thao tác của người sử dụng trên dữ liệu, đảm bảo sự an toàn cho cơ sở dữ liệu.
Đảm bảo toàn vẹn dữ liệu: SQL định nghĩa các ràng buộc toàn vẹn trong cơ sở dữ liệu nhờ đó đảm bảo tính hợp lệ và chính xác của dữ liệu trước các thao tác cập nhật cũng như các lỗi của hệ thống.

Như vậy, có thể nói rằng SQL là một ngôn ngữ hoàn thiện được sử dụng trong các hệ thống cơ sở dữ liệu và là một thành phần không thể thiếu trong các hệ quản trị cơ sở dữ liệu. Mặc dù SQL không phải là một ngôn ngữ lập trình như C, C++, Java,... song các câu lệnh mà SQL cung cấp có thể được nhúng vào trong các ngôn ngữ lập trình nhằm xây dựng các ứng dụng tương tác với cơ sở dữ liệu. Khác với các ngôn ngữ lập trình quen thuộc như C, C++, Java,... SQL là ngôn ngữ có tính khai báo. Với SQL, người dùng chỉ cần mô tả các yêu cầu cần phải thực hiện trên cơ sở dữ liệu mà không cần phải chỉ ra cách thức thực hiện các yêu cầu như thế nào. Chính vì vậy, SQL là ngôn ngữ dễ tiếp cận và dễ sử dụng.

SQL Server sử dụng ngôn ngữ lập trình và truy vấn CSDL Transact-SQL (T-SQL), một phiên bản của Structured Query Language. Ngôn ngữ lập trình và truy vấn T-SQL cho phép truy xuất dữ liệu, cập nhật và quản lý hệ thống CSDL quan hệ. Mỗi máy chủ chỉ có một hệ quản trị CSDL SQL Server.

Cài đặt hệ quản trị CSDL MS SQL Server

- Bước 1 : Tại màn hình cài đặt SQL Server 2008 bạn chọn mục Installation sau đó chọn New SQL Server stand-alone installation or add features to an existing installation

- Bước 2 : Tại màn hình Setup Support Rules chọn OK

- Bước 3 : Tại màn hình "Product Key", chọn Next để tiếp tục

- Bước 4 : Tại màn hình License Term, đánh dấu chọn nút "I accept the licence terms", rồi chọn "Next"

- Bước 5 : Tại màn hình "setup support /files" nhấn nút "install" để tiếp tục.

- Bước 6 : tại màn hình "Setup Support Rules", nếu mỗi thứ suôn sẽ, thì nhấn nút "Next" để tiếp tục.

- Bước 7 : Trên màn hình "features selection", nhất nút "Sellect All" để chọn tất cả, sau đó nhấn nút "Next".

- Bước 8 : tại màn hình "instance Configuratio", có 2 lựa chọn : chọn như hình dưới, rồi nhấn nút Next để tiếp tục ....

- Bước 9 : Chọn Next để tiếp tục.

- Bước 10 : Chọn "Account Name" và chọn là NT AUTHORITY\NETWORK như trong hình sau đây, sau đó nhấn Next để tiếp tục.

- Bước 11 : Trên màn hình "Database Engine Configuration", trong phần Account Provisioning, chọn "Windows Authotication Mode" hoặc "Mixed Mode"

+ Chọn "Windows Authotication Mode" không cần nhập Password

+ Chọn "Mixed Mode" thì ta phải nhập Password.

+ Sau cùng ta nhấn vào nút "Add Current User", rồi nhấn nút Next để tiếp tục.

- Bước 12 : nhấn nút "Next" để tiếp tục

- Bước 13 : nhấn nút "Next" để tiếp tục

- Bước 14 : nhấn nút "Install" để tiến hành cài đặt, quá trình cài đặt khoảng 3 phút ...

- Bước 15 : Nhấn nút "Next" để hoàn thành quá trình cài đặt.

Đăng nhập vào MS SQL Server

Trong quá trình cài đặt MS SQL Server sẽ yêu cầu chọn hình thức đăng nhập MS SQL Server:
- Windows Authentication Mode
- Mixed Mode

(Windows Authentication and SQL Server Authentication

Sau khi cài đặt thành công, việc đăng nhập sẽ được thực hiện qua các bước sau:
- Khởi động chương trình
  - Start / All Programs / Microsoft SQL Server 2008 / SQL Server Management Studio
  - Hoặc Double Click vào biểu tượng

Nhập/chọn các thông tin trong cửa sổ Connect to Server

Server type (Kiểu server)
Server name (Tên server thường là tên máy)
Authentication (Có 2 chế độ: Windows Authentication and SQL Server Authentication)

Click vào nút Connect

Các công cụ của MS SQL Server

SQL Server Configuration Manager

Khởi động công cụ: Start / All Programs / Microsoft SQL Server 2008 / Configuration Tools / SQL Server Configuration Manager

SQL Server Configuration Manager là bộ công cụ cung cấp giao diện cho người quản trị các công việc sau:

Starting and stopping services: Khởi động và dừng các dịch vụ
Changing service accounts: Thay đổi các tài khoản dịch vụ
Changing service start modes: Thay đổi chế độ khởi động dịch vụ
Modifying networking settings: Sửa đổi các thiết lập mạng

SQL Server Management Studio

Khởi động công cụ: Start / All Programs / Microsoft SQL Server 2008 / SQL Server Management Studio

SQL Server Management Studio bao gồm:

Object Explorer: là môi trường trực quan sử dụng hệ thống thực đơn (menu)

Transact-SQL queries: cho phép thực hiện những công việc đối với cơ sở dữ liệu thông qua các lệnh T-SQL

SQL Server Books Online

Khởi động công cụ:

Start / All Programs / Microsoft SQL Server 2008 / Documentation and Tutorials / SQL Server Books Online

Hoặc nhấn phím F1 tại màn hình SQL Server Management Studio

Sử dụng để tra cứu nhanh các thông tin về SQL và SQL Server

Các kiểu dữ liệu trong MS SQL Server

Bảng dưới đây liệt kê một số kiểu dữ liệu thông dụng được sử dụng trong SQL.

Bài 2. Định nghĩa dữ liệu trong MS SQL Server

Tạo, sử dụng, xóa cơ sở dữ liệu

1.1. Tạo Cơ sở dữ liệu

Cú pháp:

CREATE DATABASE <Tên cơ sở dữ liệu>

Ví dụ:

CREATE DATABASE QuanLyDiem

1.2. Sử dụng Cơ sở dữ liệu

Cú pháp:

USE <Tên cơ sở dữ liệu>

Ví dụ:

USE QuanLyDiem

1.3. Xóa Cơ sở dữ liệu

Cú pháp:

DROP DATABASE <Tên cơ sở dữ liệu>

Ví dụ:

DROP DATABASE QuanLyDiem

Tạo bảng dữ liệu

Cú pháp tạo bảng:

CREATE TABLE Tên_bảng

(

Tên_cột_1 Kiểu_dữ_liệu_của_cột NULL/NOT NULL,

Tên_cột_2 Kiểu_dữ_liệu_của_cột NULL/NOT NULL,

............

Tên_cột_n Kiểu_dữ_liệu_của_cột NULL/NOT NULL

)

Trong đó:

Tên_bảng: Tên của bảng cần tạo. Quy tắc định danh: là xâu ký tự bất kỳ, không chứa ký tự đặc biệt.
Tên_cột_1, Tên_cột_2, ... , Tên_cột_n: Tên cột cần định nghĩa. Tuân theo quy tắc định danh. Trong một bảng tên cột là duy nhất. Thứ tự các cột không quan trọng
Kiểu_dữ_liệu_của_cột: Đây là thuộc tính bắt buộc phải có đối với mỗi cột. Các kiểu dữ liệu có thể sử dụng đã được giới thiệu tại bài 1.
NULL/NOT NULL: Cột có chấp nhận giá trị NULL hay không? Mặc định nếu không khai báo thì SQL Server sẽ gán cho cột giá trị NULL.

Giá trị NULL:

Những giá trị không xác định được biểu diễn trong CSDL quan hệ bởi các giá trị NULL. Đây là giá trị đặc biệt và không nên nhầm lẫn với chuỗi rỗng (đối với dữ liệu kiểu chuỗi) hay giá trị không (đối với giá trị kiểu số).
Giá trị NULL đóng một vai trò quan trọng trong các CSDL và hầu hết các hệ quản trị CSDL quan hệ hiện nay đều hỗ trợ việc sử dụng giá trị này.

Ví dụ:

Tạo bảng Sinh viên:

Create table SinhVien

(

MaSV nvarchar(10) NOT NULL ,

TenSV nvarchar(50) NOT NULL ,

GioiTinh bit NOT NULL ,

NgaySinh datetime NULL

)

Tạo các loại ràng buộc dữ liệu
1. Tạo ràng buộc khóa chính Primary key

Ràng buộc PRIMARY KEY được sử dụng để định nghĩa khoá chính của bảng. Khoá chính của một bảng là một hoặc một tập nhiều cột mà giá trị của chúng là duy nhất trong bảng. Hay nói cách khác, giá trị của khoá chính sẽ giúp cho ta xác định được duy nhất một dòng (bản ghi) trong bảng dữ liệu. Mỗi một bảng chỉ có thể có duy nhất một khoá chính và bản thân khoá chính không chấp nhận giá trị NULL. Ràng buộc PRIMARY KEY là cơ sở cho việc đảm bảo tính toàn vẹn thực thể cũng như toàn vẹn tham chiếu.

Để khai báo một ràng buộc PRIMARY KEY, ta sử dụng cú pháp như sau:

[CONSTRAINT tên_ràng_buộc]

PRIMARY KEY [(danh_sách_cột)]

Nếu khoá chính của bảng chỉ bao gồm đúng một cột và ràng buộc PRIMARY KEY được chỉ định ở mức cột, ta không cần thiết phải chỉ định danh sách cột sau từ khoá PRIMARY KEY. Tuy nhiên, nếu việc khai báo khoá chính được tiến hành ở mức bảng (sử dụng khi số lượng các cột tham gia vào khoá là từ hai trở lên) thì bắt buộc phải chỉ định danh sách cột ngay sau từ khóa PRIMARY KEY và tên các cột được phân cách nhau bởi dấu phẩy.

Ví dụ: Câu lệnh dưới đây định nghĩa bảng SINHVIEN với khoá chính là MASV CREATE TABLE sinhvien

(

Masv NVARCHAR(10) CONSTRAINT pk_sinhvien_masv PRIMARY KEY,

hodem NVARCHAR(25) NOT NULL ,

ten NVARCHAR(10) NOT NULL ,

ngaysinh DATETIME,

gioitinh BIT,

noisinh NVARCHAR(255),

malop NVARCHAR(10)

)

Với bảng vừa được tạo bởi câu lệnh ở trên, nếu ta thực hiện câu lệnh:

INSERT INTO sinhvien(masv,hodem,ten,gioitinh,malop)

VALUES('0261010001','Lê Hoàng Phương','Anh',0,'C26101')

một bản ghi mới sẽ được bổ sung vào bảng này. Nhưng nếu ta thực hiện tiếp câu lệnh:

INSERT INTO sinhvien(masv,hodem,ten,gioitinh,malop)

VALUES('0261010001','Lê Huy','Đan',1,'C26101')

thì câu lệnh này sẽ bị lỗi do trùng giá trị khoá với bản ghi đã có.

Ví dụ: Câu lệnh dưới đây tạo bảng DIEMTHI với khoá chính là tập bao gồm hai cột MAMONHOC và MASV

CREATE TABLE diemthi

(

Mamonhoc NVARCHAR(10) NOT NULL ,

masv NVARCHAR(10) NOT NULL ,

diemlan1 NUMERIC(4, 2),

diemlan2 NUMERIC(4, 2),

CONSTRAINT pk_diemthi PRIMARY KEY(mamonhoc,masv)

)

Lưu ý:

Mỗi một bảng chỉ có thể có nhiều nhất một ràng buộc PRIMARY KEY.
Một khoá chính có thể bao gồm nhiều cột nhưng không vượt quá 16 cột.
1. Tạo ràng buộc khóa ngoại Foreign key

Các bảng trong một cơ sở dữ liệu có mối quan hệ với nhau. Những mối quan hệ này biểu diễn cho sự quan hệ giữa các đối tượng trong thế giới thực. Về mặt dữ liệu, những mối quan hệ được đảm bảo thông qua việc đòi hỏi sự có mặt của một giá trị dữ

liệu trong bảng này phải phụ thuộc vào sự tồn tại của giá trị dữ liệu đó ở trong một bảng khác.

Ràng buộc FOREIGN KEY được sử dụng trong định nghĩa bảng dữ liệu nhằm tạo nên mối quan hệ giữa các bảng trong một cơ sở dữ liệu. Một hay một tập các cột trong một bảng được gọi là khoá ngoại, tức là có ràng buộc FOREIGN KEY, nếu giá trị của nó được xác định từ khoá chính (PRIMARY KEY) hoặc khoá phụ (UNIQUE) của một bảng dữ liệu khác.

Hình dưới đây cho ta thấy được mối quan hệ giữa 3 bảng DIEMTHI, SINHVIEN và MONHOC. Trong bảng DIEMTHI, MASV là khoá ngoài tham chiếu đến cột MASV của bảng SINHVIEN và MAMONHOC là khoá ngoài tham chiếu đến cột MAMONHOC của bảng MONHOC.

Với mối quan hệ được tạo ra như hình trên, hệ quản trị cơ sở dữ liệu sẽ kiểm tra tính hợp lệ của mỗi bản ghi trong bảng DIEMTHI mỗi khi được bổ sung hay cập nhật. Một bản ghi bất kỳ trong bảng DIEMTHI chỉ hợp lệ (đảm bảo ràng buộc FOREIGN KEY) nếu giá trị của cột MASV phải tồn tại trong một bản ghi nào đó của bảng SINHVIEN và giá trị của cột MAMONHOC phải tồn tại trong một bản ghi nào đó của bảng MONHOC.

Ràng buộc FOREIGN KEY được định nghĩa theo cú pháp dưới đây:

[CONSTRAINT tên_ràng_buộc]

FOREIGN KEY [(danh_sách_cột)]

REFERENCES tên_bảng_tham_chiếu(danh_sách_cột_tham_chiếu)

[ON DELETE CASCADE | NO ACTION | SET NULL | SET DEFAULT]

[ON UPDATE CASCADE | NO ACTION | SET NULL | SET DEFAULT]

Việc định nghĩa một ràng buộc FOREIGN KEY bao gồm các yếu tố sau:

Tên cột hoặc danh sách cột của bảng được định nghĩa tham gia vào khoá ngoài.
Tên của bảng được tham chiếu bởi khoá ngoài và danh sách các cột được tham chiếu đến trong bảng tham chiếu.
Cách thức xử lý đối với các bản ghi trong bảng được định nghĩa trong trường hợp các bản ghi được tham chiếu trong bảng tham chiếu bị xoá (ON DELETE) hay cập nhật (ON UPDATE). SQL chuẩn đưa ra 4 cách xử lý:

CASCADE: Tự động xoá (cập nhật) nếu bản ghi được tham chiếu bị xoá (cập nhật).

NO ACTION: (Mặc định) Nếu bản ghi trong bảng tham chiếu đang được tham chiếu bởi một bản ghi bất kỳ trong bảng được định nghĩa thì bàn ghi đó không được phép xoá hoặc cập nhật (đối với cột được tham chiếu).

SET NULL: Cập nhật lại khoá ngoài của bản ghi thành giá trị NULL (nếu cột cho phép nhận giá trị NULL).

SET DEFAULT: Cập nhật lại khoá ngoài của bản ghi nhận giá trị mặc định (nếu cột có qui định giá trị mặc định).

Ví dụ: Câu lệnh dưới đây định nghĩa bảng DIEMTHI với hai khoá ngoài trên cột MASV và cột MAMONHOC (giả sử hai bảng SINHVIEN và MONHOC đã được định nghĩa)

CREATE TABLE diemthi

(

mamonhoc NVARCHAR(10) NOT NULL ,

masv NVARCHAR(10) NOT NULL ,

diemlan1 NUMERIC(4, 2),

diemlan2 NUMERIC(4, 2),

CONSTRAINT pk_diemthi PRIMARY KEY(mamonhoc,masv),

CONSTRAINT fk_diemthi_mamonhoc

FOREIGN KEY(mamonhoc)

REFERENCES monhoc(mamonhoc)

ON DELETE CASCADE

ON UPDATE CASCADE,

CONSTRAINT fk_diemthi_masv

FOREIGN KEY(masv)

REFERENCES sinhvien(masv)

ON DELETE CASCADE

ON UPDATE CASCADE

)

Lưu ý:

Cột được tham chiếu trong bảng tham chiếu phải là khoá chính (hoặc là khoá phụ).
Cột được tham chiếu phải có cùng kiểu dữ liệu và độ dài với cột tương ứng trong khóa ngoài.

Bảng tham chiếu phải được định nghĩa trước. Do đó, nếu các bảng có mối quan hệ vòng, ta có thể không thể định nghĩa ràng buộc FOREIGN KEY ngay trong câu lệnh CREATE TABLE mà phải định nghĩa thông qua lệnh ALTER

Tạo ràng buộc Default

Câu lệnh dưới đây định nghĩa bảng NHANVIEN với các trường MANV (mã nhân viên), HOTEN (họ và tên), NGAYSINH (ngày sinh của nhân viên), DIENTHOAI (điện thoại) và HSLUONG (hệ số lương)

CREATE TABLE nhanvien

(

manv NVARCHAR(10) NOT NULL,

hoten NVARCHAR(50) NOT NULL,

ngaysinh DATETIME NULL

dienthoai NVARCHAR(10) NULL,

hsluong DECIMAL(3,2) DEFAULT (1.92)

)

Trong câu lệnh trên, trường MANV và HOTEN của bảng NHANVIEN không được NULL (tức là bắt buộc phải có dữ liệu), trường NGAYSINH và DIENTHOAI sẽ nhận giá trị NULL nếu ta không nhập dữ liệu cho chúng còn trường HSLUONG sẽ nhận giá trị mặc định là 1.92 nếu không được nhập dữ liệu.

Nếu ta thực hiện các câu lệnh dưới đây sau khi thực hiện câu lệnh trên để bổ sung dữ liệu cho bảng NHANVIEN

INSERT INTO nhanvien

VALUES('NV01','Le Van A','2/4/75','886963',2.14)

INSERT INTO nhanvien(manv,hoten)

VALUES('NV02','Mai Thi B')

INSERT INTO nhanvien(manv,hoten,dienthoai)

VALUES('NV03','Tran Thi C','849290')

Ta sẽ có được dữ liệu trong bảng NHANVIEN như sau:

Tạo ràng buộc Unique

Trên một bảng chỉ có thể có nhiều nhất một khóa chính nhưng có thể có nhiều cột hoặc tập các cột có tính chất như khoá chính, tức là giá trị của chúng là duy nhất trong bảng. Tập một hoặc nhiều cột có giá trị duy nhất và không được chọn làm khoá chính được gọi là khoá phụ (khoá dự tuyển) của bảng. Như vậy, một bảng chỉ có nhiều nhất một khoá chính nhưng có thể có nhiều khoá phụ.

Ràng buộc UNIQUE được sử dụng trong câu lệnh CREATE TABLE để định nghĩa khoá phụ cho bảng và được khai báo theo cú pháp sau đây:

[CONSTRAINT tên_ràng_buộc]

UNIQUE [(danh_sách_cột)]

Ví dụ: Giả sử ta cần định nghĩa bảng LOP với khoá chính là cột MALOP nhưng đồng thời lại không cho phép các lớp khác nhau được trùng tên lớp với nhau, ta sử dụng câu lệnh như sau:

CREATE TABLE lop

(

malop NVARCHAR(10) NOT NULL,

tenlop NVARCHAR(30) NOT NULL,

khoa SMALLINT NULL,

hedaotao NVARCHAR(25) NULL,

namnhaphoc INT

makhoa NVARCHAR(5),

CONSTRAINT pk_lop PRIMARY KEY (malop),

CONSTRAINT unique_lop_tenlop UNIQUE(tenlop)

)

Tạo ràng buộc Check

Ràng buộc CHECK được sử dụng nhằm chỉ định điều kiện hợp lệ đối với dữ liệu. Mỗi khi có sự thay đổi dữ liệu trên bảng (INSERT, UPDATE), những ràng buộc này sẽ được sử dụng nhằm kiểm tra xem dữ liệu mới có hợp lệ hay không.

Ràng buộc CHECK được khai báo theo cú pháp như sau:

[CONSTRAINT tên_ràng_buộc]

CHECK (điều_kiện)

Trong đó, điều_kiện là một biểu thức logic tác động lên cột nhằm qui định giá trị hoặc khuôn dạng dữ liệu được cho phép. Trên mỗi một bảng cũng như trên mỗi một cột có thể có nhiều ràng buộc CHECK.

Ví dụ: Câu lệnh dưới đây tạo bảng DIEMTOTNGHIEP trong đó qui định giá trị của cột DIEMVAN và DIEMTOAN phải lớn hơn hoặc bằng 0 và nhỏ hơn hoặc bằng 10.

CREATE TABLE diemtotnghiep

(

hoten NVARCHAR(30) NOT NULL,

ngaysinh DATETIME,

diemvan DECIMAL(4,2)

CONSTRAINT chk_diemvan

CHECK(diemvan>=0 AND diemvan<=10),

diemtoan DECIMAL(4,2)

CONSTRAINT chk_diemtoan

CHECK(diemtoan>=0 AND diemtoan<=10),

)

Như vậy, với định nghĩa như trên của bảng DIEMTOTNGHIEP.

các câu lệnh dưới đây là hợp lệ:

INSERT INTO diemtotnghiep(hoten,diemvan,diemtoan)

VALUES('Le Thanh Hoang',9.5,2.5)

INSERT INTO diemtotnghiep(hoten,diemvan)

VALUES('Hoang Thi Mai',2.5)

Còn câu lệnh dưới đây là không hợp lệ:

INSERT INTO diemtotnghiep(hoten,diemvan,diemtoan)

VALUES('Tran Van Hanh',6,10.5)

do cột DIEMTOAN nhận giá trị 10.5 không thoả mãn điều kiện của ràng buộc

Trong ví dụ trên, các ràng buôc được chỉ định ở phần khai báo của mỗi cột. Thay vì chỉ định ràng buộc trên mỗi cột, ta có thể chỉ định các ràng buộc ở mức bảng bằng cách khai báo các ràng buộc sau khi đã khai báo xong các cột trong bảng.

Ví dụ: Câu lệnh CREATE TABLE lop

(

malop NVARCHAR(10) NOT NULL ,

tenlop NVARCHAR(30) NOT NULL ,

khoa SMALLINT NULL ,

hedaotao NVARCHAR(25) NULL

CONSTRAINT chk_lop_hedaotao

CHECK (hedaotao IN ('chính quy','tại chức')),

namnhaphoc INT NULL

CONSTRAINT chk_lop_namnhaphoc

CHECK (namnhaphoc<=YEAR(GETDATE())),

makhoa NVARCHAR(5)

)

có thể được viết lại như sau:

CREATE TABLE lop

(

malop NVARCHAR(10) NOT NULL ,

tenlop NVARCHAR(30) NOT NULL ,

khoa SMALLINT NULL ,

hedaotao NVARCHAR(25) NULL

namnhaphoc INT

makhoa NVARCHAR(5),

CONSTRAINT chk_lop

CHECK (namnhaphoc<=YEAR(GETDATE()) AND

hedaotao IN ('chính quy','tại chức'))

)

Sửa, xóa bảng dữ liệu

4.1. Sửa bảng dữ liệu

Một bảng sau khi đã được định nghĩa bằng câu lệnh CREATE TABLE có thể được sửa đổi thông qua câu lệnh ALTER TABLE. Câu lệnh này cho phép chúng ta thực hiện được các thao tác sau:

Bổ sung một cột vào bảng.
Xoá một cột khỏi bảng.
Thay đổi định nghĩa của một cột trong bảng.
Xoá bỏ hoặc bổ sung các ràng buộc cho bảng

Cú pháp của câu lệnh ALTER TABLE như sau:

ALTER TABLE tên_bảng

ADD định_nghĩa_cột |

ALTER COLUMN tên_cột kiểu_dữ_liêu [NULL | NOT NULL] |

DROP COLUMN tên_cột |

ADD CONSTRAINT tên_ràng_buộc định_nghĩa_ràng_buộc |

DROP CONSTRAINT tên_ràng_buộc

Ví dụ: Các ví dụ dưới đây minh hoạ cho ta cách sử dụng câu lệnh ALTER TABLE trong các trường hợp.

Giả sử ta có hai bảng DONVI và NHANVIEN với định nghĩa như sau:

CREATE TABLE donvi

(

madv INT NOT NULL PRIMARY KEY,

tendv NVARCHAR(30) NOT NULL

)

CREATE TABLE nhanvien

(

manv NVARCHAR(10) NOT NULL,

hoten NVARCHAR(30) NOT NULL,

ngaysinh DATETIME,

diachi CHAR(30) NOT NULL

)

Bổ sung vào bảng NHANVIEN cột DIENTHOAI với ràng buộc CHECK nhằm qui định điện thoại của nhân viên là một chuỗi 6 chữ số:

ALTER TABLE nhanvien

ADD

dienthoai NVARCHAR(6)

CONSTRAINT chk_nhanvien_dienthoai

CHECK (dienthoai LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')

Bổ sung thêm cột MADV vào bảng NHANVIEN:

ALTER TABLE nhanvien

ADD

madv INT NULL

Định nghĩa lại kiểu dữ liệu của cột DIACHI trong bảng NHANVIEN và cho phép cột

này chấp nhận giá trị NULL:

ALTER TABLE nhanvien

ALTER COLUMN diachi NVARCHAR(100) NULL

Xoá cột ngày sinh khỏi bảng NHANVIEN:

ALTER TABLE nhanvien

DROP COLUMN ngaysinh

Định nghĩa khoá chính (ràng buộc PRIMARY KEY) cho bảng NHANVIEN là cột MANV:

ALTER TABLE nhanvien

ADD

CONSTRAINT pk_nhanvien PRIMARY KEY(manv)

Định nghĩa khoá ngoài cho bảng NHANVIEN trên cột MADV tham chiếu đến cột MADV của bảng DONVI:

ALTER TABLE nhanvien

ADD

CONSTRAINT fk_nhanvien_madv

FOREIGN KEY(madv) REFERENCES donvi(madv)

ON DELETE CASCADE

ON UPDATE CASCADE

Xoá bỏ ràng buộc kiểm tra số điện thoại của nhân viên

ALTER TABLE nhanvien

DROP CONSTRAINT CHK_NHANVIEN_DIENTHOAI

Lưu ý:

Nếu bổ sung thêm một cột vào bảng và trong bảng đã có ít nhất một bản ghi thì cột mới cần bổ sung phải cho phép chấp nhận giá trị NULL hoặc phải có giá trị mặc định.
Muốn xoá một cột đang được ràng buộc bởi một ràng buộc hoặc đang được tham chiếu bởi một khoá ngoài, ta phải xoá ràng buộc hoặc khoá ngoài trước sao cho trên cột không còn bất kỳ một ràng buộc và không còn được tham chiếu bởi bất kỳ khoá ngoài nào.
Nếu bổ sung thêm ràng buộc cho một bảng đã có dữ liệu và ràng buộc cần bổ sung không được thoả mãn bởi các bản ghi đã có trong bảng thì câu lệnh ALTER TABLE không thực hiện được.

4.2. Xóa bảng dữ liệu

Khi một bảng không còn cần thiết , ta có thể xoá nó ra khỏi cơ sở dữ liệu bằng câu lệnh DROP TABLE. Câu lệnh này cũng đồng thời xoá tất cả những ràng buộc, chỉ mục, trigger liên quan đến bảng đó.

Câu lệnh có cú pháp như sau:

DROP TABLE tên_bảng

Trong các hệ quản trị cơ sở dữ liệu, khi đã xoá một bảng bằng lệnh DROP TABLE, ta không thể khôi phục lại bảng cũng như dữ liệu của nó. Do đó, cần phải cẩn thận khi sử dụng câu lệnh này.

Câu lệnh DROP TABLE không thể thực hiện được nếu bảng cần xoá đang được tham chiếu bởi một ràng buộc FOREIGN KEY. Trong trường hợp này, ràng buộc FOREIGN KEY đang tham chiếu hoặc bảng đang tham chiếu đến bảng cần xoá phải được xoá trước.

Khi một bảng bị xoá, tất cả các ràng buộc, chỉ mục và trigger liên quan đến bảng cũng đồng thời bị xóa theo. Do đó, nếu ta tạo lại bảng thì cũng phải tạo lại các đối tượng này.

Ví dụ: Giả sử cột MADV trong bảng DONVI đang được tham chiếu bởi khoá ngoài fk_nhanvien_madv trong bảng NHANVIEN. Để xoá bảng DONVI ra khỏi cơ sở dữ liệu, ta thực hiện hai câu lệnh sau:

Xoá bỏ ràng buộc fk_nhanvien_madv khỏi bảng NHANVIEN:

ALTER TABLE nhanvien

DROP CONSTRAINT fk_nhanvien_madv

Xoá bảng DONVI:

DROP TABLE donvi

Bài 3: Thao tác dữ liệu trong MS SQL Server

Thêm mới một dòng dữ liệu

Dữ liệu trong các bảng được thể hiện dưới dạng các dòng (bản ghi). Để bổ sung thêm các dòng dữ liệu vào một bảng, ta sử dụng câu lệnh INSERT. Hầu hết các hệ quản trị CSDL dựa trên SQL cung cấp các cách dưới đây để thực hiện thao tác bổ sung dữ liệu cho bảng:

Bổ sung từng dòng dữ liệu với mỗi câu lệnh INSERT. Đây là các sử dụng thường gặp nhất trong giao tác SQL.
Bổ sung nhiều dòng dữ liệu bằng cách truy xuất dữ liệu từ các bảng dữ liệu khác.

Bổ sung từng dòng dữ liệu với lệnh INSERT

Để bổ sung một dòng dữ liệu mới vào bảng, ta sử dụng câu lệnh INSERT với cú pháp như sau:

INSERT INTO tên_bảng[(danh_sách_cột)]

VALUES(danh_sách_trị)

Trong câu lệnh INSERT, danh sách cột ngay sau tên bảng không cần thiết phải chỉ định nếu giá trị các trường của bản ghi mới được chỉ định đầy đủ trong danh sách trị.

Trong trường hợp này, thứ tự các giá trị trong danh sách trị phải bằng với số lượng các trường của bảng cần bổ sung dữ liệu cũng như phải tuân theo đúng thứ tự của các trường như khi bảng được định nghĩa.

Ví dụ: Câu lệnh dưới đây bổ sung thêm một dòng dữ liệu vào bảng KHOA INSERT INTO khoa

VALUES(‘DHT10’,’Khoa Luật’,’054821135’)

Trong trường hợp chỉ nhập giá trị cho một số cột trong bảng, ta phải chỉ định danh sách các cột cần nhập dữ liệu ngay sau tên bảng. Khi đó, các cột không được nhập dữ liệu sẽ nhận giá trị mặc định (nếu có) hoặc nhận giá trị NULL (nếu cột cho phép chấp nhận giá trị NULL). Nếu một cột không có giá trị mặc định và không chấp nhận giá trị NULL mà không đuợc nhập dữ liệu, câu lệnh sẽ bị lỗi.

Ví dụ: Câu lệnh dưới đây bổ sung một bản ghi mới cho bảng SINHVIEN

INSERT INTO sinhvien(masv,hodem,ten,gioitinh,malop)

VALUES(‘0241020008’,‘Nguyễn Công’,’Chính’,1,’C24102’)

câu lệnh trên còn có thể được viết như sau:

INSERT INTO sinhvien

VALUES(‘0241020008’,‘Nguyễn Công’,’Chính’,

NULL,1,NULL,’C24102’)

Bổ sung nhiều dòng dữ liệu từ bảng khác

Một cách sử dụng khác của câu lệnh INSERT được sử dụng để bổ sung nhiều dòng dữ liệu vào một bảng, các dòng dữ liệu này được lấy từ một bảng khác thông qua câu lệnh SELECT. Ở cách này, các giá trị dữ liệu được bổ sung vào bảng không được chỉ định tường minh mà thay vào đó là một câu lệnh SELECT truy vấn dữ liệu từ bảng khác.

Cú pháp câu lệnh INSERT có dạng như sau:

INSERT INTO tên_bảng[(danh_sách_cột)] câu_lệnh_SELECT

Ví dụ: Giả sử ta có bảng LUUSINHVIEN bao gồm các trường HODEM, TEN, NGAYSINH. Câu lệnh dưới đây bổ sung vào bảng LUUSINHVIEN các dòng dữ liệu có được từ câu truy vấn SELECT:

INSERT INTO luusinhvien

SELECT hodem,ten,ngaysinh

FROM sinhvien

WHERE noisinh like ‘%Huế%’

Khi bổ sung dữ liệu theo cách này cần lưu ý một số điểm sau: Kết quả của câu lệnh SELECT phải có số cột bằng với số cột được chỉ định trong bảng đích và phải tương thích về kiểu dữ liệu.

Cập nhật dữ liệu

Câu lệnh UPDATE trong SQL được sử dụng để cập nhật dữ liệu trong các bảng.

Câu lệnh này có cú pháp như sau:

UPDATE tên_bảng

SET tên_cột = biểu_thức

[, ..., tên_cột_k = biểu_thức_k]

[FROM danh_sách_bảng]

[WHERE điều_kiện]

Sau UPDATE là tên của bảng cần cập nhật dữ liệu. Một câu lệnh UPDATE có thể cập nhật dữ liệu cho nhiều cột bằng cách chỉ định các danh sách tên cột và biểu thức tương

ứng sau từ khoá SET. Mệnh đề WHERE trong câu lệnh UPDATE thường được sử dụng để chỉ định các dòng dữ liệu chịu tác động của câu lệnh (nếu không chỉ định, phạm vi tác động của câu lệnh được hiểu là toàn bộ các dòng trong bảng)

Ví dụ: Câu lệnh dưới đây cập nhật lại số đơn vị học trình của các môn học có số đơn vị học trình nhỏ hơn 2

UPDATE monhoc

SET sodvht = 3

WHERE sodvht = 2

Sử dụng cấu trúc CASE trong câu lệnh UPDATE

Cấu trúc CASE có thể được sử dụng trong biểu thức khi cần phải đưa ra các quyết định khác nhau về giá trị của biểu thức

Ví dụ: Giả sử ta có bảng NHATKYPHONG sau đây

Sau khi thực hiện câu lệnh:

UPDATE nhatkyphong

SET tienphong=songay*CASE WHEN loaiphong='A' THEN 100

WHEN loaiphong='B' THEN 70

ELSE 50

END

Dữ liệu trong bảng sẽ là:

Điều kiện cập nhật dữ liệu liên quan đến nhiều bảng

Mệnh đề FROM trong câu lệnh UPDATE được sử dụng khi cần chỉ định các điều kiện liên quan đến các bảng khác với bảng cần cập nhật dữ liệu. Trong truờng hợp này, trong mệnh đề WHERE thường có điều kiện nối giữa các bảng.

Ví dụ: Giả sử ta có hai bảng MATHANG và NHATKYBANHANG như sau:

Câu lệnh dưới đây sẽ cập nhật giá trị trường THANHTIEN của bảng NHATKYBANHANG theo công thức THANHTIEN = SOLUONG × GIA

UPDATE nhatkybanhang

SET thanhtien = soluong*gia

FROM mathang

WHERE nhatkybanhang.mahang = mathang.mahang

Câu lệnh UPDATE với truy vấn con

Tương tự như trong câu lệnh SELECT, truy vấn con có thể được sử dụng trong mệnh đề WHERE của câu lệnh UPDATE nhằm chỉ định điều kiện đối với các dòng dữ liệu cần cập nhật dữ liệu.

Ví dụ: Câu lệnh ở trên có thể được viết như sau:

UPDATE nhatkybanhang

SET thanhtien = soluong*gia

FROM mathang

WHERE mathang.mahang =(SELECT mathang.mahang

FROM mathang

WHERE mathang.mahang=nhatkybanhang.mahang)

Xóa các dòng dữ liệu

Để xoá dữ liệu trong một bảng, ta sử dụng câu lệnh DELETE. Cú pháp của câu lệnh này như sau:

DELETE FROM tên_bảng

[FROM danh_sách_bảng]

[WHERE điều_kiện]

Trong câu lệnh này, tên của bảng cần xoá dữ liệu được chỉ định sau DELETE FROM.

Mệnh đề WHERE trong câu lệnh được sử dụng để chỉ định điều kiện đối với các dòng dữ liệu cần xoá. Nếu câu lệnh DELETE không có mệnh đề WHERE thì toàn bộ các dòng dữ liệu trong bảng đều bị xoá.

Ví dụ: Câu lệnh dưới đây xoá khỏi bảng SINHVIEN những sinh viên sinh tại Huế

DELETE FROM sinhvien

WHERE noisinh LIKE ‘%Huế%’

Xoá dữ liệu khi điều kiện liên quan đến nhiều bảng

Nếu điều kiện trong câu lệnh DELETE liên quan đến các bảng không phải là bảng cần xóa dữ liệu, ta phải sử dụng thêm mệnh đề FROM và sau đó là danh sách tên các bảng đó. Trong trường hợp này, trong mệnh đề WHERE ta chỉ định thêm điều kiện nối giữa các bảng

Ví dụ: Câu lệnh dưới đây xoá ra khỏi bảng SINHVIEN những sinh viên lớp Tin K24

DELETE FROM sinhvien

FROM lop

WHERE lop.malop=sinhvien.malop AND tenlop='Tin K24'

Sử dụng truy vấn con trong câu lệnh DELETE

Một câu lệnh SELECT có thể được lồng vào trong mệnh đề WHERE trong câu lệnh DELETE để làm điều kiện cho câu lệnh tương tự như câu lệnh UPDATE.

Ví dụ: Câu lệnh dưới đây xoá khỏi bảng LOP những lớp không có sinh viên nào học

DELETE FROM lop

WHERE malop NOT IN (SELECT DISTINCT malop

FROM sinhvien)

Xoá toàn bộ dữ liệu trong bảng

Câu lệnh DELETE không chỉ định điều kiện đối với các dòng dữ liệu cần xoá trong mệnh đề WHERE sẽ xoá toàn bộ dữ liệu trong bảng. Thay vì sử dụng câu lệnh DELETE trong trường hợp này, ta có thể sử dụng câu lệnh TRUNCATE có cú pháp như sau:

TRUNCATE TABLE tên_bảng

Ví dụ: Câu lệnh sau xoá toàn bộ dữ liệu trong bảng diemthi:

DELETE FROM diemthi

có tác dụng tương tự với câu lệnh

TRUNCATE TABLE diemthi

Truy xuất dữ liệu
1. Lấy thông tin từ các cột của bảng bằng mệnh đề SELECT

Câu lệnh SELECT được sử dụng để truy xuất dữ liệu từ các dòng và các cột của một hay nhiều bảng, khung nhìn. Câu lệnh này có thể dùng để thực hiện phép chọn (tức là truy xuất một tập con các dòng trong một hay nhiều bảng), phép chiếu (tức là truy xuất một tập con các cột trong một hay nhiều bảng) và phép nối (tức là liên kết các dòng trong hai hay nhiều bảng để truy xuất dữ liệu). Ngoài ra, câu lệnh này còn cung cấp khả năng thực hiện các thao tác truy vấn và thống kê dữ liệu phức tạp khác.

Cú pháp chung của câu lệnh SELECT có dạng:

SELECT [ALL | DISTINCT][TOP n] danh_sách_chọn

[INTO tên_bảng_mới]

FROM danh_sách_bảng/khung_nhìn

[WHEREđiều_kiện]

[GROUP BY danh_sách_cột]

[HAVING điều_kiện]

[ORDER BY cột_sắp_xếp]

[COMPUTE danh_sách_hàm_gộp [BY danh_sách_cột]]

Điều cần lưu ý đầu tiên đối với câu lệnh này là các thành phần trong câu lệnh SELECT nếu được sử dụng phải tuân theo đúng thứ tự như trong cú pháp. Nếu không, câu lệnh sẽ được xem là không hợp lệ.

Câu lệnh SELECT được sử dụng để tác động lên các bảng dữ liệu và kết quả của câu lệnh cũng được hiển thị dưới dạng bảng, tức là một tập hợp các dòng và các cột (ngoại trừ trường hợp sử dụng câu lệnh SELECT với mệnh đề COMPUTE).

Ví dụ: Kết quả của câu lệnh sau đây cho biết mã lớp, tên lớp và hệ đào tạo của các lớp hiện có

SELECT malop,tenlop,hedaotao

FROM lop

Mệnh đề FROM

Mệnh đề FROM trong câu lệnh SELECT được sử dung nhằm chỉ định các bảng và khung nhìn cần truy xuất dữ liệu. Sau FROM là danh sách tên của các bảng và khung nhìn tham gia vào truy vấn, tên của các bảng và khung nhìn được phân cách nhau bởi dấu phẩy.

Ví dụ: Câu lệnh dưới đây hiển thị danh sách các khoa trong trường

SELECT * FROM khoa

kết quả câu lệnh như sau:

Ta có thể sử dụng các bí danh cho các bảng hay khung nhìn trong câu lệnh SELECT. Bí danh được gán trong mệnh đề FROM bằng cách chỉ định bí danh ngay sau tên bảng.

Ví dụ: câu lệnh sau gán bí danh là a cho bảng khoa

SELECT * FROM khoa a

Danh sách chọn trong câu lệnh SELECT

Danh sách chọn trong câu lệnh SELECT được sử dụng để chỉ định các trường, các biểu thức cần hiển thị trong các cột của kết quả truy vấn. Các trường, các biểu thức được chỉ định ngay sau từ khoá SELECT và phân cách nhau bởi dấu phẩy. Sử dụng danh sách chọn trong câu lệnh SELECT bao gồm các trường hợp sau:

Chọn tất cả các cột trong bảng

Khi cần hiển thị tất cả các

Trường trong các bảng, sử dụng ký tự * trong danh sách chọn thay vì phải liệt kê danh sách tất cả các cột. Trong trường hợp này, các cột được hiển thị trong kết quả truy vấn sẽ tuân theo thứ tự mà chúng đã được tạo ra khi bảng được định nghĩa.

Ví dụ: Câu lệnh

SELECT * FROM lop

cho kết quả bao như sau:

Tên cột trong danh sách chọn

Trong trường hợp cần chỉ định cụ thể các cột cần hiển thị trong kết quả truy vấn, ta chỉ định danh sách các tên cột trong danh sách chọn. Thứ tự của các cột trong kết quả truy vấn tuân theo thứ tự của các trường trong danh sách chọn.

Ví dụ: Câu lệnh

SELECT malop,tenlop,namnhaphoc,khoa

FROM lop

cho biết mã lớp, tên lớp, năm nhập học và khoá của các lớp và có kết quả như sau:

Lưu ý: Nếu truy vấn được thực hiện trên nhiều bảng/khung nhìn và trong các bảng/khung nhìn có các trường trùng tên thì tên của những trường này nếu xuất hiện trong danh sách chọn phải được viết dưới dạng: tên_bảng.tên_trường

Ví dụ:

SELECT malop, tenlop, lop.makhoa, tenkhoa

FROM lop, khoa

WHERE lop.malop = khoa.makhoa

Thay đổi tiêu đề các cột

Trong kết quả truy vấn, tiêu đề của các cột mặc định sẽ là tên của các trường tương ứng trong bảng. Tuy nhiên, để các tiêu đề trở nên thân thiện hơn, ta có thể đổi tên các tiêu đề của các cột. Để đặt tiêu đề cho một cột nào đó, ta sử dụng cách viết:

tiêu_đề_cột = tên_trường

hoặc tên_trường AS tiêu_đề_cột

hoặc tên_trường tiêu_đề_cột

Ví dụ: Câu lệnh dưới đây:

SELECT 'Mã lớp'= malop,tenlop 'Tên lớp',khoa AS 'Khoá'

FROM lop

cho biết mã lớp, tên lớp và khoá học của các lớp trong trường. Kết quả của câu lệnh

như sau:

Sử dụng cấu trúc CASE trong danh sách chọn

Cấu trúc CASE được sử dụng trong danh sách chọn nhằm thay đổi kết quả của truy vấn tuỳ thuộc vào các trường hợp khác nhau. Cấu trúc này có cú pháp như sau:

CASE biểu_thức

WHEN biểu_thức_kiểm_tra THEN kết_quả

[ ... ]

[ELSE kết_quả_của_else]

END

hoặc:

CASE

WHEN điều_kiện THEN kết_quả

[ ... ]

[ELSE kết_quả_của_else]

END

Ví dụ: Để hiển thị mã, họ tên và giới tính (nam hoặc nữ) của các sinh viên, ta sử

dụng câu lệnh

SELECT masv,hodem,ten,

CASE gioitinh

WHEN 1 THEN 'Nam'

ELSE 'Nữ'

END AS gioitinh

FROM sinhvien

hoặc:

SELECT masv,hodem,ten,

CASE

WHEN gioitinh=1 THEN 'Nam'

ELSE 'Nữ'

END AS gioitinh

FROM sinhvien

Kết quả của hai câu lệnh trên đều có dạng như sau:

Hằng và biểu thức trong danh sách chọn

Ngoài danh sách trường, trong danh sách chọn của câu lệnh SELECT còn có thể sử dụng các biểu thức. Mỗi một biểu thức trong danh sách chọn trở thành một cột trong kết quả truy vấn.

Ví dụ: câu lệnh dưới đây cho biết tên và số tiết của các môn học

SELECT tenmonhoc,sodvht*15 AS sotiet

FROM monhoc

Nếu trong danh sách chọn có sự xuất hiện của giá trị hằng thì giá trị này sẽ xuất hiện trong một cột của kết quả truy vấn ở tất cả các dòng

Ví dụ: Câu lệnh

SELECT tenmonhoc,'Số tiết: ',sodvht*15 AS sotiet

FROM monhoc

cho kết quả như sau:

Loại bỏ các dòng dữ liệu trùng nhau trong kết quả truy vấn

Trong kết quả của truy vấn có thể xuất hiện các dòng dữ liệu trùng nhau. Để loại bỏ bớt các dòng này, ta chỉ định thêm từ khóa DISTINCT ngay sau từ khoá SELECT.

Ví dụ: Hai câu lệnh dưới đây

SELECT khoa FROM lop

và:

SELECT DISTINCT khoa FROM lop

có kết quả lần lượt như sau:

Giới hạn số lượng dòng trong kết quả truy vấn

Kết quả của truy vấn được hiển thị thường sẽ là tất cả các dòng dữ liệu truy vấn được. Trong trường hợp cần hạn chế số lượng các dòng xuất hiện trong kết quả truy vấn, ta chỉ định thêm mệnh đề TOP ngay trước danh sách chọn của câu lệnh SELECT.

Ví dụ: Câu lệnh dưới đây hiển thị họ tên và ngày sinh của 5 sinh viên đầu tiên

trong danh sách

SELECT TOP 5 hodem,ten,ngaysinh

FROM sinhvien

Ngoài cách chỉ định cụ số lượng dòng cần hiển thị trong kết quả truy vấn, ta có thể chỉ định số lượng các dòng cần hiển thị theo tỷ lệ phần trăm bằng cách sử dụng thêm từ khoá PERCENT như ở ví dụ dưới đây.

Ví dụ: Câu lệnh dưới đây hiển thị họ tên và ngày sinh của 10% số lượng sinh viên hiện có trong bảng SINHVIEN

SELECT TOP 10 PERCENT hodem,ten,ngaysinh

FROM sinhvien

Chọn các dòng của bảng bằng mệnh đề WHERE

Mệnh đề WHERE trong câu lệnh SELECT được sử dụng nhằm xác định các điều kiện đối với việc truy xuất dữ liệu. Sau mệnh đề WHERE là một biểu thức logic và chỉ những dòng dữ liệu nào thoả mãn điều kiện được chỉ định mới được hiển thị trong kết quả truy vấn.

Ví dụ: Câu lệnh dưới đây hiển thị danh sách các môn học có số đơn vị học trình

lớn hơn 3

SELECT * FROM monhoc

WHERE sodvht>3

Kết quả của câu lệnh này như sau:

Trong mệnh đề WHERE thường sử dụng:

Các toán tử kết hợp điều kiện (AND, OR)
Các toán tử so sánh
Kiểm tra giới hạn của dữ liệu (BETWEEN/ NOT BETWEEN)
Danh sách
Kiểm tra khuôn dạng dữ liệu.
Các giá trị NULL

Các toán tử so sánh

Ví dụ: Câu lệnh

SELECT masv,hodem,ten,ngaysinh

FROM sinhvien

WHERE (ten='Anh')

AND (YEAR(GETDATE())-YEAR(ngaysinh)<=20)

cho biết mã, họ tên và ngày sinh của các sinh viên có tên là Anh và có tuổi nhỏ hơn

hoặc bằng 20.

Kiểm tra giới hạn của dữ liệu

Để kiểm tra xem giá trị dữ liệu nằm trong (ngoài) một khoảng nào đó, ta sử dụng toán tử BETWEEN (NOT BETWEEN) như sau:

Ví dụ: Câu lệnh dưới đây cho biết họ tên và tuổi của các sinh viên có tên là Bình và có tuổi nằm trong khoảng từ 20 đến 22

SELECT hodem,ten,year(getdate())-year(ngaysinh) AS tuoi

FROM sinhvien

WHERE ten='Bình' AND YEAR(GETDATE())-YEAR(ngaysinh) BETWEEN 20 AND 22

Danh sách (IN và NOT IN)

Từ khoá IN được sử dụng khi ta cần chỉ định điều kiện tìm kiếm dữ liệu cho câu lệnh SELECT là một danh sách các giá trị. Sau IN (hoặc NOT IN) có thể là một danh sách các giá trị hoặc là một câu lệnh SELECT khác.

Ví dụ: Để biết danh sách các môn học có số đơn vị học trình là 2, 4 hoặc 5, thay vì sử dụng câu lệnh

SELECT * FROM monhoc

WHERE sodvht=2 OR sodvht=4 OR sodvht=5

ta có thể sử dụng câu lệnh

SELECT * FROM monhoc

WHERE sodvht IN (2,4,5)

Toán tử LIKE và các ký tự đại diện

Từ khoá LIKE (NOT LIKE) sử dụng trong câu lệnh SELECT nhằm mô tả khuôn dạng của dữ liệu cần tìm kiếm. Chúng thường được kết hợp với các ký tự đại diện sau đây:

Ví dụ: Câu lệnh dưới đây

SELECT hodem,ten FROM sinhvien

WHERE hodem LIKE 'Lê%'

cho biết họ tên của các sinh viên có họ là Lê và có kết quả như sau

Câu lệnh:

SELECT hodem,ten FROM sinhvien

WHERE hodem LIKE 'Lê%' AND ten LIKE '[AB]%'

Có kết quả là:

Giá trị NULL

Dữ liệu trong một cột cho phép NULL sẽ nhận giá trị NULL trong các trường hợp sau:

Nếu không có dữ liệu được nhập cho cột và không có mặc định cho cột hay kiểu dữ liệu trên cột đó.
Người sử dụng trực tiếp đưa giá trị NULL vào cho cột đó.
Một cột có kiểu dữ liệu là kiểu số sẽ chứa giá trị NULL nếu giá trị được chỉ định gây tràn số.

Trong mệnh đề WHERE, để kiểm tra giá trị của một cột có giá trị NULL hay không, ta sử dụng cách viết:

WHERE tên_cột IS NULL

hoặc:

WHERE tên_cột IS NOT NULL

Truy vấn thông tin từ nhiều bảng

Khi cần thực hiện một yêu cầu truy vấn dữ liệu từ hai hay nhiều bảng, ta phải sử dụng đến phép nối. Một câu lệnh nối kết hợp các dòng dữ liệu trong các bảng khác nhau lại theo một hoặc nhiều điều kiện nào đó và hiển thị chúng trong kết quả truy vấn.

Xét hai bảng sau đây:

Giả sử ta cần biết mã lớp và tên lớp của các lớp thuộc Khoa Công nghệ Thông tin, ta phải làm như sau:

Chọn ra dòng trong bảng KHOA có tên khoa là Khoa Công nghệ Thông tin, từ đó xác định được mã khoa (MAKHOA) là DHT02.
Tìm kiếm trong bảng LOP những dòng có giá trị trường MAKHOA là DHT02 (tức là bằng MAKHOA tương ứng trong bảng KHOA) và đưa những dòng này vào kết quả truy vấn

Như vậy, để thực hiện được yêu cầu truy vấn dữ liệu trên, ta phải thực hiện phép nối giữa hai bảng KHOA và LOP với điều kiện nối là MAKHOA của KHOA bằng với MAKHOA của LOP. Câu lệnh sẽ được viết như sau:

SELECT malop,tenlop

FROM khoa,lop

WHERE khoa.makhoa = lop.makhoa AND tenkhoa='Khoa Công nghệ Thông tin'

Sử dụng phép nối

Phép ơ sở để thực hiện các yêu cầu truy vấn dữ liệu liên quan đến nhiều bảng. Một câu lệnh nối thực hiện lấy các dòng dữ liệu trong các bảng tham gia truy vấn, so sánh giá trị của các dòng này trên một hoặc nhiều cột được chỉ định trong điều kiện nối và kết hợp các dòng thoả mãn điều kiện thành những dòng trong kết quả truy vấn.

Để thực hiện được một phép nối, cần phải xác định được những yếu tố sau:

Những cột nào cần hiển thị trong kết quả truy vấn
Những bảng nào có tham gia vào truy vấn.
Điều kiện để thực hiện phép nối giữa các bảng dữ liệu là gì

Trong các yếu tố kể trên, việc xác định chính xác điều kiện để thực hiện phép nối giữa các bảng đóng vai trò quan trọng nhất. Trong đa số các trường hợp, điều kiện của phép nối được xác định nhờ vào mối quan hệ giữa các bảng cần phải truy xuất dữ liệu. Thông thường, đó là điều kiện bằng nhau giữa khoá chính và khoá ngoài của hai bảng có mối quan hệ với nhau. Như vậy, để có thể đưa ra một câu lệnh nối thực hiện chính xác yêu cầu truy vấn dữ liệu đòi hỏi phải hiểu được mối quan hệ cũng như ý nghĩa của chúng giữa các bảng dữ liệu.

Danh sách chọn trong phép nối

Một câu lệnh nối cũng được bắt đầu với từ khóa SELECT. Các cột được chỉ định tên sau từ khoá SELECT là các cột được hiển thị trong kết quả truy vấn. Việc sử dụng tên các cột trong danh sách chọn có thể là:

Tên của một số cột nào đó trong các bảng có tham gia vào truy vấn. Nếu tên cột trong các bảng trùng tên nhau thì tên cột phải được viết dưới dạng tên_bảng.tên_cột
Dấu sao (*) được sử dụng trong danh sách chọn khi cần hiển thị tất cả các cột của các bảng tham gia truy vấn.
Trong trường hợp cần hiển thị tất cả các cột của một bảng nào đó, ta sử dụng cách viết: tên_bảng.*

Mệnh đề FROM trong phép nối

Sau mệnh đề FROM của câu lệnh nối là danh sách tên các bảng (hay khung nhìn) tham gia vào truy vấn. Nếu ta sử dụng dấu * trong danh sách chọn thì thứ tự củacác bảng liệt kê sau FROM sẽ ảnh hưởng đến thứ tự các cột được hiển thị trong kết quả truy vấn.

Mệnh đề WHERE trong phép nối

Khi hai hay nhiều bảng được nối với nhau, ta phải chỉ định điều kiện để thực hiện phép nối ngay sau mệnh đề WHERE. Điều kiện nối được biểu diễn dưới dạng biểu thức logic so sánh giá trị dữ liệu giữa các cột của các bảng tham gia truy vấn.

Các toán tử so sánh dưới đây được sử dụng để xác định điều kiện nối

Ví dụ: Câu lệnh dưới đây hiển thị danh sách các sinh viên với các thông tin: mã sinh viên, họ và tên, mã lớp, tên lớp và tên khoa

SELECT masv,hodem,ten,sinhvien.malop,tenlop,tenkhoa

FROM sinhvien,lop,khoa

WHERE sinhvien.malop = lop.malop AND lop.makhoa=khoa.makhoa

Trong câu lệnh trên, các bảng tham gia vào truy vấn bao gồm SINHVIEN, LOP và KHOA. Điều kiện để thực hiện phép nối giữa các bảng bao gồm hai điều kiện:

sinhvien.malop = lop.malop

và

lop.malop = khoa.malop

Điều kiện nối giữa các bảng trong câu lệnh trên là điều kiện bằng giữa khoá ngoài và khoá chính của các bảng có mối quan hệ với nhau. Hay nói cách khác, điều kiện của phép nối được xác định dựa vào mối quan hệ giữa các bảng trong cơ sở dữ liệu.

Các loại phép nối
- Phép nối bằng và phép nối tự nhiên

Một phép nối bằng (equi-join) là một phép nối trong đó giá trị của các cột được sử dụng để nối được so sánh với nhau dựa trên tiêu chuẩn bằng và tất cả các cột trong các bảng tham gia nối đều được đưa ra trong kết quả.

Ví dụ: Câu lệnh dưới đây thực hiện phép nối bằng giữa hai bảng LOP và KHOA

SELECT *

FROM lop,khoa

WHERE lop.makhoa=khoa.makhoa

Trong kết quả của câu lệnh trên, cột makhoa (mã khoa) xuất hiện hai lần trong kết quả phép nối (cột makhoa của bảng khoa và cột makhoa của bảng lop) và như vậy là không cần thiết. Ta có thể loại bỏ bớt đi những cột trùng tên trong kết quả truy vấn bằng cách chỉ định danh sách cột cần được hiển thị trong danh sách chọn của câu lệnh.

Một dạng đặc biệt của phép nối bằng được sử dụng nhiều là phép nối tự nhiên (natural-join). Trong phép nối tự nhiên, điều kiện nối giữa hai bảng chính là điều kiện bằng giữa khoá ngoài và khoá chính của hai bảng; Và trong danh sách chọn của câu lệnh chỉ giữ lại một cột trong hai cột tham gia vào điều kiện của phép nối.

Ví dụ: Để thực hiện phép nối tự nhiên, câu lệnh trong ví dụ trước được viết lại như sau:

SELECT malop,tenlop,khoa,hedaotao,namnhaphoc, siso,lop.makhoa,tenkhoa,dienthoai

FROM lop,khoa

WHERE lop.makhoa=khoa.makhoa

hoặc viết dưới dạng ngắn gọn hơn:

SELECT lop.*,tenkhoa,dienthoai

FROM lop,khoa

WHERE lop.makhoa=khoa.makhoa

Phép nối với các điều kiện bổ sung

Trong các câu lệnh nối, ngoài điều kiện của phép nối được chỉ định trong mệnh đề WHERE còn có thể chỉ định các điều kiện tìm kiếm dữ liệu khác (điều kiện chọn).

Thông thường, các điều kiện này được kết hợp với điều kiện nối thông qua toán tử AND.

Ví dụ: Câu lệnh dưới đây hiển thị họ tên và ngày sinh của các sinh viên Khoa Công nghệ Thông tin

SELECT hodem,ten,ngaysinh

FROM sinhvien,lop,khoa

WHERE tenkhoa='Khoa Công nghệ Thông tin' AND sinhvien.malop = lop.malop AND lop.makhoa = khoa.makhoa

Phép tự nối và các bí danh

Phép tự nối là phép nối mà trong đó điều kiện nối được chỉ định liên quan đến các cột của cùng một bảng. Trong trường hợp này, sẽ có sự xuất hiện tên của cùng một bảng nhiều lần trong mệnh đề FROM và do đó các bảng cần phải được đặt bí danh.

Ví dụ: Để biết được họ tên và ngày sinh của các sinh viên có cùng ngày sinh với sinh viên Trần Thị Kim Anh, ta phải thực hiện phép tự nối ngay trên chính bảng sinhvien. Trong câu lệnh nối, bảng sinhvien xuất hiện trong mệnh đề FROM với bí danh là a và b. Bảng sinhvien với bí danh là a sử dụng để chọn ra sinh viên có họ tên là Trần Thị Kim Anh và bảng sinhvien với bí danh là b sử dụng để xác định các sinh viên trùng ngày sinh với sinh viên Trần Thị Kim Anh. Câu lệnh được viết như sau:

SELECT b.hodem,b.ten,b.ngaysinh

FROM sinhvien a, sinhvien b

WHERE a.hodem='Trần Thị Kim' AND a.ten='Anh' AND

a.ngaysinh=b.ngaysinh AND a.masv<>b.masv

Phép nối không dựa trên tiêu chuẩn bằng

Trong phép nối này, điều kiện để thực hiện phép nối giữa các bảng dữ liệu không phải là điều kiện so sành bằng giữa các cột. Loại phép nối này trong thực tế thường ít được sử dụng.

Phép nối ngoài (outer-join)

Trong các phép nối đã đề cập ở trên, chỉ những dòng có giá trị trong các cột được chỉ định thoả mãn điều kiện kết nối mới được hiển thị trong kết quả truy vấn, và được gọi là phép nối trong (inner join) Theo một nghĩa nào đó, những phép nối này loại bỏ thông tin chứa trong những dòng không thoả mãn điều kiện nối. Tuy nhiên, đôikhi ta cũng cần giữ lại những thông tin này bằng cách cho phép những dòng không thoả mãn điều kiện nối có mặt trong kết quả của phép nối. Để làm điều này, ta có thểsử dụng phép nối ngoài.

SQL cung cấp các loại phép nối ngoài sau đây:

+ Phép nối ngoài trái (ký hiệu: *=): Phép nối này hiển thị trong kết quả truy vấn tất cả các dòng dữ liệu của bảng nằm bên trái trong điều kiện nối cho dù những dòng này không thoả mãn điều kiện của phép nối.

+ Phép nối ngoài phải (ký hiệu: =*): Phép nối này hiển thị trong kết quả truy vấn tất cả các dòng dữ liệu của bảng nằm bên phải trong điều kiện nối cho dù những dòng này không thoả điều kiện của phép nối.

Ví dụ: Giả sử ta có hai bảng DONVI và NHANVIEN như sau:

Câu lệnh:

SELECT *

FROM nhanvien,donvi

WHERE nhanvien.madv=donvi.madv

có kết quả là:

Nếu thực hiện phép nối ngoài trái giữa bảng NHANVIEN và bảng DONVI:

SELECT *

FROM nhanvien,donvi

WHERE nhanvien.madv*=donvi.madv

kết quả của câu lệnh sẽ là:

Và kết quả của phép nối ngoài phải:

select *

from nhanvien,donvi

where nhanvien.madv=*donvi.madv

như sau:

Phép nối và các giá trị NULL

Nếu trong các cột của các bảng tham gia vào điều kiện của phép nối có các giá trị NULL thì các giá trị NULL được xem như là không bằng nhau.

Ví dụ: Giả sử ta có hai bảng TABLE1 và TABLE2 như sau:

Câu lệnh:

SELECT *

FROM table1, table2

WHERE A *= C

Có kết quả là:

Sử dụng phép nối trong SQL

Ở phần trước đã đề cập đến phương pháp sử dụng phép nối trong và phép nối ngoài trong truy vấn SQL. Như đã trình bày, điều kiện của phép nối trong câu lệnh được chỉ định trong mệnh đề WHERE thông qua các biểu thức so sánh giữa các bảng tham gia truy vấn.

Chuẩn SQL đưa ra một cách khác để biểu diễn cho phép nối, trong cách biểu diễn này, điều kiện của phép nối không được chỉ định trong mệnh đề WHERE mà được chỉ định ngay trong mệnh đề FROM của câu lệnh. Cách sử dụng phép nối này cho phép ta biểu diễn phép nối cũng như điều kiện nối được rõ ràng, đặc biệt là trong trường hợp phép nối được thực hiện trên ba bảng trở lên.

Phép nối trong

Điều kiện để thực hiện phép nối trong được chỉ định trong mệnh đề FROM theo cú pháp như sau:

tên_bảng_1 [INNER] JOIN tên_bảng_2 ON điều_kiện_nối

Ví dụ: Để hiển thị họ tên và ngày sinh của các sinh viên lớp Tin K24, thay vì sử dụng câu lệnh:

SELECT hodem,ten,ngaysinh

FROM sinhvien,lop

WHERE tenlop='Tin K24' AND sinhvien.malop=lop.malop

ta có thể sử dụng câu lệnh như sau:

SELECT hodem,ten,ngaysinh

FROM sinhvien INNER JOIN lop

ON sinhvien.malop=lop.malop

WHERE tenlop='Tin K24'

Phép nối ngoài

SQL cung cấp các phép nối ngoài sau đây:

Phép nối ngoài trái (LEFT OUTER JOIN)
Phép nối ngoài phải (RIGHT OUTER JOIN)
Phép nối ngoài đầy đủ (FULL OUTER JOIN)

Cũng tương tự như phép nối trong, điều kiện của phép nối ngoài cũng được chỉ định ngay trong mệnh đề FROM theo cú pháp:

tên_bảng_1 LEFT|RIGHT|FULL [OUTER] JOIN tên_bảng_2

ON điều_kiện_nối

Ví dụ: Giả sử ta có hai bảng DONVI, NHANVIEN dữ liệu như sau:

Phép nối ngoài trái giữa hai bảng NHANVIEN và DONVI được biểu diễn bởi câu lệnh:

SELECT *

FROM nhanvien LEFT OUTER JOIN donvi

ON nhanvien.madv=donvi.madv

có kết quả là:

Câu lệnh:

SELECT *

FROM nhanvien RIGHT OUTER JOIN donvi

ON nhanvien.madv=donvi.madv

thực hiện phép nối ngoài phải giữa hai bảng NHANVIEN và DONVI, và có kết quả là:

Nếu phép nối ngoài trái (tương ứng phải) hiển thị trong kết quả truy vấn cả những dòng dữ liệu không thoả điều kiện nối của bảng bên trái (tương ứng phải) trong phép nối thì phép nối ngoài đầy đủ hiển thị trong kết quả truy vấn cả những dòng dữ liệu không thoả điều kiện nối của cả hai bảng tham gia vào phép nối.

Ví dụ: Với hai bảng NHANVIEN và DONVI như ở trên, câu lệnh

SELECT *

FROM nhanvien FULL OUTER JOIN donvi

ON nhanvien.madv=donvi.madv

cho kết quả là:

Thực hiện phép nối trên nhiều bảng

Một đặc điểm nổi bật của SQL là cho phép biểu diễn phép nối trên nhiều bảng dữ liệu một cách rõ ràng. Thứ tự thực hiện phép nối giữa các bảng được xác định theo nghĩa kết quả của phép nối này được sử dụng trong một phép nối khác.

Ví dụ: Câu lệnh dưới đây hiển thị họ tên và ngày sinh của các sinh viên thuộcKhoa Công nghệ Thông tin

SELECT hodem,ten,ngaysinh

FROM (sinhvien INNER JOIN lop

ON sinhvien.malop=lop.malop)

INNER JOIN khoa ON lop.makhoa=khoa.makhoa

WHERE tenkhoa=N'Khoa công nghệ thông tin'

Trong câu lệnh trên, thứ tự thực hiện phép nối giữa các bảng được chỉ định rõ ràng: phép nối giữa hai bảng sinhvien và lop được thực hiện trước và kết quả của phép nối này lại tiếp tục được nối với bảng khoa.

Phân nhóm dữ liệu bằng mệnh đề GROUP BY

Ngoài khả năng thực hiện các yêu cầu truy vấn dữ liệu thông thường (chiếu, chọn, nối,…) như đã đề cập như ở các phần trước, câu lệnh SELECT còn cho phép thực hiện các thao tác truy vấn và tính toán thống kê trên dữ liệu như: cho biết tổng số tiết dạy của mỗi giáo viên, điểm trung bình các môn học của mỗi sinh viên,…

Mệnh đề GROUP BY sử dụng trong câu lệnh SELECT nhằm phân hoạch các dòng dữ liệu trong bảng thành các nhóm dữ liệu, và trên mỗi nhóm dữ liệu thực hiện tính toán các giá trị thống kê như tính tổng, tính giá trị trung bình,...

Các hàm gộp được sử dụng để tính giá trị thống kê cho toàn bảng hoặc trên mỗi nhóm dữ liệu. Chúng có thể được sử dụng như là các cột trong danh sách chọn của câu lệnh SELECT hoặc xuất hiện trong mệnh đề HAVING, nhưng không được phép xuất hiện trong mệnh đề WHERE

SQL cung cấp các hàm gộp dưới đây:

Hàm gộp Chức năng:

Trong đó: Hàm SUM và AVG chỉ làm việc với các biểu thức số.

Hàm SUM, AVG, COUNT, MIN và MAX bỏ qua các giá trị NULL khi tính toán.
Hàm COUNT(*) không bỏ qua các giá trị NULL. Mặc định, các hàm gộp thực hiện tính toán thống kê trên toàn bộ dữ liệu. Trong trường hợp cần loại bỏ bớt các giá trị trùng nhau (chỉ giữ lại một giá trị), ta chỉ định thêm từ khoá DISTINCT ở trước biểu thức là đối số của hàm

Thống kê trên toàn bộ dữ liệu

Khi cần tính toán giá trị thống kê trên toàn bộ dữ liệu, ta sử dụng các hàm gộp trong danh sách chọn của câu lệnh SELECT. Trong trường hợp này, trong danh sách chọn không được sử dụng bất kỳ một tên cột hay biểu thức nào ngoài các hàm gộp.

Ví dụ: Để thống kê trung bình điểm lần 1 của tất cả các môn học, ta sử dụng câu lệnh như sau:

SELECT AVG(diemlan1)

FROM diemthi

còn câu lệnh dưới đây cho biết tuổi lớn nhất, tuổi nhỏ nhất và độ tuổi trung bình của tất cả các sinh viên sinh tại Huế:

SELECT MAX(YEAR(GETDATE())-YEAR(ngaysinh)),

MIN(YEAR(GETDATE())-YEAR(ngaysinh)),

AVG(YEAR(GETDATE())-YEAR(ngaysinh))

FROM sinhvien

WHERE noisinh=’Huế’

Thống kê dữ liệu trên các nhóm

Trong trường hợp cần thực hiện tính toán các giá trị thống kê trên các nhóm dữ liệu, ta sử dụng mệnh đề GROUP BY để phân hoạch dữ liệu vào trong các nhóm. Các hàm gộp được sử dụng sẽ thực hiện thao tác tính toán trên mỗi nhóm và cho biết giá trị thống kê theo các nhóm dữ liệu.

Ví dụ: Câu lệnh dưới đây cho biết sĩ số (số lượng sinh viên) của mỗi lớp SELECT lop.malop,tenlop,COUNT(masv) AS siso

FROM lop,sinhvien

WHERE lop.malop=sinhvien.malop

GROUP BY lop.malop,tenlop

và có kết quả là

SELECT sinhvien.masv,hodem,ten,

sum(diemlan1*sodvht)/sum(sodvht)

FROM sinhvien,diemthi,monhoc

WHERE sinhvien.masv=diemthi.masv AND

diemthi.mamonhoc=monhoc.mamonhoc

GROUP BY sinhvien.masv,hodem,ten

cho biết trung bình điểm thi lần 1 các môn học của các sinh viên

Lưu ý: Trong trường hợp danh sách chọn của câu lệnh SELECT có cả các hàm gộp và những biểu thức không phải là hàm gộp thì những biểu thức này phải có mặt đầy đủ trong mệnh đề GROUP BY, nếu không câu lệnh sẽ không hợp lệ.

Ví dụ: Dưới đây là một câu lệnh sai

SELECT lop.malop,tenlop,COUNT(masv)

FROM lop,sinhvien

WHERE lop.malop=sinhvien.malop

GROUP BY lop.malop

do thiếu trường TENLOP sau mệnh đề GROUP BY

Lọc nhóm kết quả truy vấn bằng mệnh đề HAVING

Mệnh đề HAVING được sử dụng nhằm chỉ định điều kiện đối với các giá trị thống kê được sản sinh từ các hàm gộp tương tự như cách thức mệnh đề WHERE thiết lập các điều kiện cho câu lệnh SELECT. Mệnh đề HAVING thường không thực sự có nghĩa nếu như không sử dụng kết hợp với mệnh đề GROUP BY. Một điểm khác biệt giữa HAVING và WHERE là trong điều kiện của WHERE không được có các hàm gộp trong khi HAVING lại cho phép sử dụng các hàm gộp trong điều kiện của mình.

Ví dụ: Để biết trung bình điểm thi lần 1 của các sinh viên có điểm trung bình lớn

hơn hoặc bằng 5, ta sử dụng câu lệnh như sau:

SELECT sinhvien.masv,hodem,ten,

SUM(diemlan1*sodvht)/sum(sodvht)

FROM sinhvien,diemthi,monhoc

WHERE sinhvien.masv=diemthi.masv AND

diemthi.mamonhoc=monhoc.mamonhoc

GROUP BY sinhvien.masv,hodem,ten

HAVING sum(diemlan1*sodvht)/sum(sodvht)>=5

Sắp xếp kết quả truy vấn bằng mệnh đề ORDER BY

Mặc định, các dòng dữ liệu trong kết quả của câu truy vấn tuân theo thứ tự của chúng trong bảng dữ liệu hoặc được sắp xếp theo chỉ mục (nếu trên bảng có chỉ mục).

Trong trường hợp muốn dữ liệu được sắp xếp theo chiều tăng hoặc giảm của giá trị của một hoặc nhiều trường, ta sử dụng thêm mệnh đề ORDER BY trong câu lệnh SELECT; Sau ORDER BY là danh sách các cột cần sắp xếp (tối đa là 16 cột). Dữ liệu

được sắp xếp có thể theo chiều tăng (ASC) hoặc giảm (DESC), mặc định là sắp xếp theo chiều tăng.

Ví dụ: Câu lệnh dưới đây hiển thị danh sách các môn học và sắp xếp theo chiều giảm dần của số đơn vị học trình

SELECT * FROM monhoc

ORDER BY sodvht DESC

Nếu sau ORDER BY có nhiều cột thì việc sắp xếp dữ liệu sẽ được ưu tiên theo thứ tự từ trái qua phải.

Ví dụ: Câu lệnh

SELECT hodem,ten,gioitinh, YEAR(GETDATE())-YEAR(ngaysinh) AS tuoi

FROM sinhvien

WHERE ten='Bình'

ORDER BY gioitinh,tuoi

có kết quả là:

Thay vì chỉ định tên cột sau ORDER BY, ta có thể chỉ định số thứ tự của cột cấn được sắp xếp. Câu lệnh ở ví dụ trên có thể được viết lại như sau:

SELECT hodem,ten,gioitinh, YEAR(GETDATE())-YEAR(ngaysinh) AS tuoi

FROM sinhvien

WHERE ten='Bình'

ORDER BY 3, 4

Truy vấn lồng nhau

Kết quả của truy vấn con có thể được sử dụng đề thực hiện phép so sánh số học với một biểu thức của truy vấn cha. Trong trường hợp này, truy vấn con được sử dụng dưới dạng:

WHERE biểu_thức phép_toán_số_học [ANY|ALL]

(truy_vấn_con)

Trong đó phép toán số học có thể sử dụng bao gồm: =, <>, >, <, >=, <=; Và truy vấn con phải có kết quả bao gồm đúng một cột.

Ví dụ: Câu lệnh dưới đây cho biết danh sách các môn học có số đơn vị học trình lớn hơn hoặc bằng số đơn vị học trình của môn học có mã là TI-001

SELECT *

FROM monhoc

WHERE sodvht>=(SELECT sodvht FROM monhoc WHERE mamonhoc='TI-001')

Nếu truy vấn con trả về nhiều hơn một giá trị, việc sử dụng phép so sánh như trên sẽ không hợp lệ. Trong trường hợp này, sau phép toán so sánh phải sử dụng thêm lượng từ ALL hoặc ANY. Lượng từ ALL được sử dụng khi cần so sánh giá trị của biểu thức với tất cả các giá trị trả về trong kết quả của truy vấn con; ngược lai, phép so sánh với lượng từ ANY có kết quả đúng khi chỉ cần một giá trị bất kỳ nào đó trong kết quả của truy vấn con thoả mãn điều kiện. Ví dụ 2.44: Câu lệnh dưới đây cho biết họ tên của những sinh viên lớp Tin K25 sinh trước tất cả các sinh viên của lớp Toán K25

SELECT hodem,ten

FROM sinhvien JOIN lop ON sinhvien.malop=lop.malop

WHERE tenlop='Tin K25' AND

ngaysinh<ALL(SELECT ngaysinh

FROM sinhvien JOIN lop

ON sinhvien.malop=lop.malop

WHERE lop.tenlop='Toán K25')

và câu lệnh:

SELECT hodem,ten

FROM sinhvien JOIN lop on sinhvien.malop=lop.malop

WHERE tenlop='Tin K25' AND

year(ngaysinh)= ANY(SELECT year(ngaysinh)

FROM sinhvien JOIN lop

ON sinhvien.malop=lop.malop

WHERE lop.tenlop='Toán K25')

cho biết họ tên của những sinh viên lớp Tin K25 có năm sinh trùng với năm sinh của

bất kỳ một sinh viên nào đó của lớp Toán K25.

Sử dụng truy vấn con với toán tử IN

Khi cần thực hiện phép kiểm tra giá trị của một biểu thức có xuất hiện (không xuất hiện) trong tập các giá trị của truy vấn con hay không, ta có thể sử dụng toán tử IN (NOT IN) như sau:

WHERE biểu_thức [NOT] IN (truy_vấn_con)

Ví dụ: Để hiển thị họ tên của những sinh viên lớp Tin K25 có năm sinh bằng với năm sinh của một sinh viên nào đó của lớp Toán K25, thay vì sử dụng câu lệnh như ở ví dụ trên, ta có thể sử dụng câu lệnh như sau:

SELECT hodem,ten

FROM sinhvien JOIN lop on sinhvien.malop=lop.malop

WHERE tenlop='Tin K25' AND

year(ngaysinh)IN(SELECT year(ngaysinh)

FROM sinhvien JOIN lop

ON sinhvien.malop=lop.malop

WHERE lop.tenlop='Toán K25')

Sử dụng lượng từ EXISTS với truy vấn con

Lượng từ EXISTS được sử dụng kết hợp với truy vấn con dưới dạng:

WHERE [NOT] EXISTS (truy_vấn_con)

để kiểm tra xem một truy vấn con có trả về dòng kết quả nào hay không. Lượng từ EXISTS (tương ứng NOT EXISTS) trả về giá trị True (tương ứng False) nếu kết quả của truy vấn con có ít nhất một dòng (tương ứng không có dòng nào). Điều khác biệt của việc sử dụng EXISTS với hai cách đã nêu ở trên là trong danh sách chọn của truy vấn con có thể có nhiều hơn hai cột.

Ví dụ: Câu lệnh dưới đây cho biết họ tên của những sinh viên hiện chưa có điểm thi của bất kỳ một môn học nào

SELECT hodem,ten

FROM sinhvien

WHERE NOT EXISTS(SELECT masv FROM diemthi

WHERE diemthi.masv=sinhvien.masv)

Sử dụng truy vấn con với mệnh đề HAVING

Một truy vấn con có thể được sử dụng trong mệnh đề HAVING của một truy vấn khác. Trong trường hơp này, kết quả của truy vấn con được sử dụng để tạo nên điều kiện đối với các hàm gộp.

Ví dụ: Câu lệnh dưới đây cho biết mã, tên và trung bình điểm lần 1 của các mônhọc có trung bình lớn hơn trung bình điểm lần 1 của tất cả các môn học

SELECT diemthi.mamonhoc,tenmonhoc,AVG(diemlan1)

FROM diemthi,monhoc

WHERE diemthi.mamonhoc=monhoc.mamonhoc

GROUP BY diemthi.mamonhoc,tenmonhoc

HAVING AVG(diemlan1)>

(SELECT AVG(diemlan1) FROM diemthi)

Bài 4: Khung nhìn (View)

Các bảng trong cơ sở dữ liệu đóng vai trò là các đối tượng tổ chức và lưu trữ dữ liệu. Như vậy, ta có thể quan sát được dữ liệu trong cơ sở dữ liệu bằng cách thực hiện các truy vấn trên bảng dữ liệu. Ngoài ra, SQL còn cho phép chúng ta quan sát được dữ liệu thông qua việc định nghĩa các khung nhìn.

Một khung nhìn (view) có thể được xem như là một bảng “ảo” trong cơ sở dữ liệu có nội dung được định nghĩa thông qua một truy vấn (câu lệnh SELECT). Như vậy, một khung nhìn trông giống như một bảng với một tên khung nhìn và là một tập bao gồm các dòng và các cột. Điểm khác biệt giữa khung nhìn và bảng là khung nhìn không được xem là một cấu trúc lưu trữ dữ liệu tồn tại trong cơ sở dữ liệu. Thực chất dữ liệu quan sát được trong khung nhìn được lấy từ các bảng thông qua câu lệnh truy vấn dữ liệu.

Hình 1: Khung nhìn DSSV với dữ liệu được lấy từ bảng SINHVIEN và LOP

Hình 1 minh hoạ cho ta thấy khung nhìn có tên DSSV được định nghĩa thông qua câu lệnh SELECT truy vấn dữ liệu trên hai bảng SINHVIEN và LOP:

SELECT masv,hodem,ten, DATEDIFF(YY,ngaysinh,GETDATE())

AS tuoi,tenlop

FROM sinhvien,lop

WHERE sinhvien.malop=lop.malop

Khi khung nhìn DSSV đã được định nghĩa, ta có thế sử dụng câu lệnh SELECT để truy vấn dữ liệu từ khung nhìn như đối với các bảng. Khi trong câu truy vấn xuất hiện khung nhìn, hệ quản trị cơ sở dữ liệu sẽ dựa vào đinh nghĩa của khung nhìn để chuyển yêu cầu truy vấn dữ liệu liên quan đến khung nhìn thành yêu cầu tương tự trên các bảng cơ sở và việc truy vấn dữ liệu được thực hiện bởi yêu cầu tương đương trên các bảng.

Việc sử dụng khung nhìn trong cơ sở dữ liệu đem lại các lợi ích sau đây:

Bảo mật dữ liệu: Người sử dụng được cấp phát quyền trên các khung nhìn với những phần dữ liệu mà người sử dụng được phép. Điều này hạn chế được phần nào việc người sử dụng truy cập trực tiếp dữ liệu.
Đơn giản hoá các thao tác truy vấn dữ liệu: Một khung nhìn đóng vai trò như là một đối tượng tập hợp dữ liệu từ nhiều bảng khác nhau vào trong một “bảng”. Nhờ vào đó, người sử dụng có thể thực hiện các yêu cầu truy vấn dữ liệu một cách đơn giản từ khung nhìn thay vì phải đưa ra những câu truy vấn phức tạp.
Tập trung và đơn giản hoà dữ liệu: Thông qua khung nhìn ta có thể cung cấp cho người sử dụng những cấu trúc đơn giản, dễ hiểu hơn về dữ liệu trong cơ sở dữ liệu đồng thời giúp cho người sử dụng tập trung hơn trên những phần dữ liệu cần thiết.
Độc lập dữ liệu: Một khung nhìn có thể cho phép người sử dụng có được cái nhìn về dữ liệu độc lập với cấu trúc của các bảng trong cơ sở dữ liệu cho dù các bảng cơ sở có bị thay đổi phần nào về cấu trúc
Tuy nhiên, việc sử dụng khung nhìn cũng tồn tại một số nhược điểm sau:
Do hệ quản trị cơ sở dữ liệu thực hiện việc chuyển đổi các truy vấn trên khung nhìn thành những truy vấn trên các bảng cơ sở nên nếu một khung nhìn được định nghĩa bởi một truy vấn phức tạp thì sẽ dẫn đến chi phí về mặt thời gian khi thực hiện truy vấn liên quan đến khung nhìn sẽ lớn.
Mặc dù thông qua khung nhìn có thể thực hiện được thao tác bổ sung và cập nhật dữ liệu cho bảng cơ sở nhưng chỉ hạn chế đối với những khung nhìn đơn giản. Đối với những khung nhìn phức tạp thì thường không thực hiện được; hay nói cách khác là dữ liệu trong khung nhìn là chỉ đọc.

Tạo khung nhìn

Câu lệnh CREATE VIEW được sử dụng để tạo ra khung nhìn và có cú pháp như sau:

CREATE VIEW tên_khung_nhìn[(danh_sách_tên_cột)]

AS

câu_lệnh_SELECT

Ví dụ: Câu lệnh dưới đây tạo khung nhìn có tên DSSV từ câu lệnh SELECT truy vấn dữ liệu từ hai bảng SINHVIEN và LOP

CREATE VIEW dssv

AS

SELECT masv,hodem,ten,

DATEDIFF(YY,ngaysinh,GETDATE()) AS tuoi,tenlop

FROM sinhvien,lop

WHERE sinhvien.malop=lop.malop

và nếu thực hiện câu lệnh:

SELECT * FROM dssv

ta có được kết quả như sau:

Nếu trong câu lệnh CREATE VIEW, ta không chỉ định danh sách các tên cột cho khung nhìn, tên các cột trong khung nhìn sẽ chính là tiêu đề các cột trong kết quả của câu lệnh SELECT. Trong trường hợp tên các cột của khung nhìn đươc chỉ định, chúng phải có cùng số lượng với số lượng cột trong kết quả của câu truy vấn.

Ví dụ: Câu lệnh dưới đây tạo khung nhìn từ câu truy vấn tương tự như ví dụ trên nhưng có đặt tên cho các cột trong khung nhìn:

CREATE VIEW dssv(ma,ho,ten,tuoi,lop)

AS

SELECT masv,hodem,ten,

DATEDIFF(YY,ngaysinh,GETDATE()), tenlop

FROM sinhvien,lop

WHERE sinhvien.malop=lop.malop

và câu lệnh:

SELECT * FROM dssv

Khi tạo khung nhìn với câu lệnh CREATE VIEW, ta cần phải lưu ý một số nguyên tắc sau:

Tên khung nhìn và tên cột trong khung nhìn, cũng giống như bảng, phải tuân theo qui tắc định danh
Không thể qui định ràng buộc và tạo chỉ mục cho khung nhìn
Câu lệnh SELECT với mệnh đề COMPUTE ... BY không được sử dụng để định nghĩa khung nhìn.
Phải đặt tên cho các cột của khung nhìn trong các trường hợp sau đây:
Trong kết quả của câu lệnh SELECT có ít nhất một cột được sinh ra bởi một biểu thức (tức là không phải là một tên cột trong bảng cơ sở) và cột đó không được đặt tiêu đề.
Tồn tại hai cột trong kết quả của câu lệnh SELECT có cùng tiêu đề cột.

Ví dụ: Câu lệnh dưới đây là câu lệnh sai do cột thứ 4 không xác định được tên cột

CREATE VIEW tuoisinhvien

AS

SELECT masv,hodem,ten,DATEDIFF(YY,ngaysinh,GETDATE())

FROM sinhvien

Cập nhật, bổ sung và xóa dữ liệu thông qua khung nhìn

Đối với một số khung nhìn, ta có thể tiến hành thực hiện các thao tác cập nhập, bổ sung và xoá dữ liệu. Thực chất, những thao tác này sẽ được chuyển thành những thao tác tương tự trên các bảng cơ sở và có tác động đến những bảng cơ sở.

Về mặt lý thuyết, để có thể thực hiện thao tác bổ sung, cập nhật và xoá, một khung nhìn trước tiên phải thoả mãn các điều kiện sau đây:

Trong câu lệnh SELECT định nghĩa khung nhìn không được sử dụng từ khoá DISTINCT, TOP, GROUP BY và UNION.
Các thành phần xuất hiện trong danh sách chọn của câu lệnh SELECT phải là các cột trong các bảng cơ sở. Trong danh sách chọn không được chứa các biểu thức tính toán, các hàm gộp.

Ngoài những điều kiện trên, các thao tác thay đổi đến dữ liệu thông qua khung nhìn còn phải đảm bảo thoả mãn các ràng buộc trên các bảng cơ sở, tức là vẫn đảm bảo tính toàn vẹn dữ liệu. Ví dụ dưới đây sẽ minh hoạ cho ta thấy việc thực hiện các thao tác bổ sung, cập nhật và xoá dữ liệu thông qua khung nhìn.

Ví dụ: Xét định nghĩa hai bảng DONVI và NHANVIEN như sau:

CREATE TABLE donvi

(

madv INT PRIMARY KEY,

endv NVARCHAR(30) NOT NULL,

dienthoai NVARCHAR(10) NULL

)

CREATE TABLE nhanvien

(

manv NVARCHAR(10) PRIMARY KEY,

hoten NVARCHAR(30) NOT NULL,

ngaysinh DATETIME NULL,

diachi NVARCHAR(50) NULL,

madv INT FOREIGN KEY

ON DELETE CASCADE

ON UPDATE CASCADE

REFERENCES donvi(madv)

)

Giả sử trong hai bảng này đã có dữ liệu như sau:

Câu lệnh dưới đây định nghĩa khung nhìn NV1 cung cấp các thông tin về mã nhân viên, họ tên và mã đơn vị nhân viên làm việc:

CREATE VIEW nv1

AS

SELECT manv,hoten,madv FROM nhanvien

Nếu ta thực hiện câu lệnh

INSERT INTO nv1 VALUES('NV04','Le Thi D',1)

Một bản ghi mới sẽ được bổ sung vào bảng NHANVIEN và dữ liệu trong bảng này sẽ là:

Thông qua khung nhìn này, ta cũng có thể thực hiện thao tác cập nhật và xoá dữ liệu.

Chẳng hạn, nếu ta thực hiện câu lệnh:

DELETE FROM nv1 WHERE manv='NV04'

Thì bản ghi tương ứng với nhân viên có mã NV04 sẽ bị xoá khỏi bảng NHANVIEN

Nếu trong danh sách chọn của câu lệnh SELECT có sự xuất hiện của biểu thức tính toán đơn giản, thao tác bổ sung dữ liệu thông qua khung nhìn không thể thực hiện được. Tuy nhiên, trong trường hợp này thao tác cập nhật và xoá dữ liệu vấn có thể có khả năng thực hiện được (hiển nhiên không thể cập nhật dữ liệu đối với một cột có được từ một biểu thức tính toán).

Ví dụ: Xét khung nhìn NV2 được định nghĩa như sau:

CREATE VIEW nv2

AS

SELECT manv,hoten,YEAR(ngaysinh) AS namsinh,madv

FROM nhanvien

Đối với khung nhìn NV2, ta không thể thực hiện thao tác bổ sung dữ liệu nhưng có thể cập nhật hoặc xoá dữ liệu trên bảng thông qua khung nhìn này. Câu lệnh dưới đây là không thể thực hiện được trên khung nhìn NV2

INSERT INTO nv2(manv,hoten,madv)

VALUES('NV05','Le Van E',1)

Nhưng câu lệnh:

UPDATE nv2 SET hoten='Le Thi X' WHERE manv='NV04'

hoặc câu lệnh

DELETE FROM nv2 WHERE manv='NV04'

lại có thể thực hiện được và có tác động đối với dữ liệu trong bảng NHANVIEN Trong trường hợp khung nhìn được tạo ra từ một phép nối (trong hoặc ngoài) trên nhiều bảng, ta có thể thực hiện được thao tác bổ sung hoặc cập nhật dữ liệu nếu thao tác này chỉ có tác động đến đúng một bảng cơ sở (câu lệnh DELETE không thể thực hiện được trong trường hợp này).

Ví dụ: Với khung nhìn được định nghĩa như sau:

CREATE VIEW nv3

AS

SELECT manv,hoten,ngaysinh, diachi,nhanvien.madv

AS noilamviec, donvi.madv, tendv, dienthoai

FROM nhanvien FULL OUTER JOIN donvi

ON nhanvien.madv=donvi.madv

Câu lệnh:

INSERT INTO nv3(manv,hoten,noilamviec)

VALUES('NV05','Le Van E',1)

sẽ bổ sung thêm vào bảng NHANVIEN một bản ghi mới.

Hoặc câu lệnh:

INSERT INTO nv3(madv,tendv) VALUES(3,'P. Ke toan')

bổ sung thêm vào bảng DONVI một bản ghi do cả hai câu lệnh này chỉ có tác động đến đúng một bảng cơ sở.

Câu lệnh dưới đây không thể thực hiện được do có tác động một lúc đến hai bảng cơ sở.

INSERT INTO nv3(manv,hoten,noilamviec,madv,tendv)

VALUES('NV05','Le Van E',1,3,'P. Ke toan')

Sửa, xóa khung nhìn

Sửa đổi khung nhìn

Câu lệnh ALTER VIEW được sử dụng để định nghĩa lại khung nhìn hiện có nhưng không làm thay đổi các quyền đã được cấp phát cho người sử dụng trước đó.

Câu lệnh này sử dụng tương tự như câu lệnh CREATE VIEW và có cú pháp như sau:

ALTER VIEW tên_khung_nhìn [(danh_sách_tên_cột)]

AS

Câu_lệnh_SELECT

Ví dụ: Ta định nghĩa khung nhìn như sau:

CREATE VIEW viewlop

AS

SELECT malop,tenlop,tenkhoa

FROM lop INNER JOIN khoa ON lop.makhoa=khoa.makhoa

WHERE tenkhoa='Khoa Vật lý’

và có thể định nghĩa lại khung nhìn trên bằng câu lệnh:

ALTER VIEW view_lop

AS

SELECT malop,tenlop,hedaotao

FROM lop INNER JOIN khoa ON lop.makhoa=khoa.makhoa

WHERE tenkhoa='Khoa

Xóa khung nhìn

Khi một khung nhìn không còn sử dụng, ta có thể xoá nó ra khỏi cơ sở dữ liệu thông qua câu lệnh:

DROP VIEW tên_khung_nhìn

Nếu một khung nhìn bị xoá, toàn bộ những quyền đã cấp phát cho người sử dụng trên khung nhìn cũng đồng thời bị xoá. Do đó, nếu ta tạo lại khung nhìn thì phải tiến hành cấp phát lại quyền cho người sử dụng.

Ví dụ: Câu lệnh dưới đây xoá khung nhìn VIEW_LOP ra khỏi cơ sở dữ liệu

DROP VIEW view_lop

Bài 5: Thủ tục lưu trữ (Stored Procedure)

Thủ tục lưu trữ (Stored Procedure):

Là một nhóm câu lệnh Transact-SQL đã được compiled (biên dịch)
Được lưu trữ trong CSDL dưới một tên phân biệt
Được xử lý như một đơn vị (chứ không phải nhiều câu lệnh SQL riêng lẻ)
Có tham số Input và Output
Cho phép gọi thực hiện từ các ngôn ngữ lập trình

Thủ tục lưu trữ thường dùng thực hiện truy vấn hay cập nhật dữ liệu theo yêu cầu của người dùng.

Khi một thủ tục lưu trữ đã được định nghĩa, nó có thể được gọi thông qua tên thủ tục, nhận các tham số truyền vào, thực thi các câu lệnh SQL bên trong thủ tục và có thể trả về các giá trị sau khi thực hiện xong.

Thủ tục lưu trữ có thể có các thành phần sau:

Các cấu trúc điều khiển (IF, WHILE, FOR)
Các biến như trong ngôn ngữ lập trình nhằm lưu giữ các giá trị tính toán được, các giá trị được truy xuất được từ cơ sở dữ liệu.
Một tập các câu lệnh SQL được kết hợp lại với nhau thành một khối lệnh bên trong một thủ tục.
Một thủ tục có thể nhận các tham số truyền vào, có thể trả về các giá trị thông qua các tham số (như trong các ngôn ngữ lập trình).

Tạo, gọi thủ tục lưu trữ

1.1. Cú pháp tạo thủ tục lưu trữ

CREATE PROC[EDURE] tên_thủ_tục [(các_tham_số_hình_thức)]

[WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION]

AS

[BEGIN]

các_câu_lệnh_của_thủ_tục

[END]

Trong đó:

tên_thủ_tục: Tên của thủ tục cần tạo. Tên phải tuân theo qui tắc định danh và không được vượt quá 128 ký tự.
các_câu_lệnh_của_thủ_tục: Tập hợp các câu lệnh sử dụng trong nội dung thủ tục.
các_tham_số_hình_thức: Nếu nhiều tham số thì các khai báo phân cách nhau bởi dấu phẩy. Khai báo của mỗi một tham số tối thiểu phải bao gồm hai phần:
- tên tham số được bắt đầu bởi dấu @.
- kiểu dữ liệu của tham số
- Ví dụ: @MaMonHoc nvarchar(10)

RECOMPILE : Thông thường, thủ tục sẽ được phân tích, tối ưu và dịch sẵn ở lần gọi đầu tiên. Nếu tuỳ chọn WITH RECOMPILE được chỉ định, thủ tục sẽ được dịch lại mỗi khi được gọi.
ENCRYPTION: Thủ tục sẽ được mã hoá nếu tuỳ chọn WITH ENCRYPTION được chỉ định. Nếu thủ tục đã được mã hoá, ta không thể xem được nội dung của thủ tục.

Ví dụ:

Xây dựng thủ tục liệt kê tên các môn học có mã bắt đầu bằng "MD"

Create Proc SP_MonHocLaMoDun

As

Select MaMH, TenMH

From MonHoc

Where MaMH like 'MD%'

1.2. Lời gọi thủ tục lưu trữ

Tên_thủ_tục [Danh_sách_đối_số]

Hoặc EXEC Tên_thủ_tục [Danh_sách_đối_số]

(Trong trường hợp lời gọi thủ tục được thực hiện bên trong một thủ tục khác)

Ví dụ 1: SP_MonHocLaMoDun

Hoặc Exec SP_MonHocLaMoDun

Ví dụ 2: SP_DSLopCuaKhoa Hoặc Exec SP_DSLopCuaKhoa

→ Hiện danh sách lớp của khoa có mã CNTT

Exec SP_DSLopCuaKhoa ‘KT’

→ Hiện danh sách lớp của khoa có mã KT

Khai báo tham số, biến trong thủ tục

@tên_tham_số kiểu_dữ_liệu [= giá_trị_mặc_định] [OUTPUT]

Tham số hình thức có 2 loại:

Input: Nhận giá trị từ người dùng gửi vào cho thủ tục xử lý

@tên_tham_số kiểu_dữ_liệu [= giá_trị_mặc_định]

Khi có khai báo giá trị mặc định: tham số hình thức sẽ nhận giá trị mặc định nếu không truyền giá trị cho nó trong lời gọi thủ tục

Output: Nhận kết quả trả về từ thủ tục

Trong trường hợp cần giữ lại sự thay đổi giá trị của tham số sau khi kết thúc thủ tục, ta sử dụng OUTPUT

@tên_tham_số kiểu_dữ_liệu OUTPUT

Ví dụ:

Xây dựng thủ tục cho biết danh sách các lớp của một mã khoa cho trước

Create Proc SP_DSLopCuaKhoa (@MaKhoa nvarchar(10) = 'CNTT')

AS

Select TenLop

From Lop inner join Khoa

on Lop.MaKhoa = Khoa.MaKhoa

Where Lop.MaKhoa = @MaKhoa

Khai báo và sử dụng biến trong thủ tục

3.1. Biến cục bộ

Biến cục bộ là một đối tượng có thể chứa giá trị thuộc một kiểu dữ liệu nhất định, tên biến bắt đầu bằng một ký tự @

Khai báo biến cục bộ:

Declare Tên_biến Kiểu_dữ_liệu

Ví dụ:

Declare @MaSV nvarchar(10)
Declare @NgaySinh datetime
Declare @SoTiet int
Declare @Diem float

Gán giá trị cho biến cục bộ:

Set Tên_biến = Giá_trị
Set Tên_biến = Tên_biến
Set Tên_biến = Biểu_thức
Set Tên_biến = Kết_quả_truy_vấn

Giá trị gán cho biến phải phù hợp với kiểu dữ liệu của biến.

Set @MaSV = ‘SV0101’
Set @SoTiet = 60
Set @SoSV = (Select count(*) From SinhVien)
Set @Diem = 7.5

Đưa kết quả truy vấn vào biến

Select @Ho = HoDem, @Ten = Ten

From SinhVien

Where MaSV = ‘SV0101’

3.2. Biến toàn cục

Biến toàn cục là các biến hệ thống do SQL Server cung cấp, tên biến bắt đầu bằng 2 ký tự @. Người sử dụng không thể gán giá trị trực tiếp cho các biến này.

Một số biến hệ thống:

@@error: thông báo mã lỗi, nếu @@error = 0: thao tác thực hiện thành công.
@@rowcount: cho biết số dòng bị ảnh hưởng bởi lệnh cuối (insert, update, delete)

Sử dụng cấu trúc điều khiển trong thủ tục
1. Sử dụng cấu trúc If…Else…

Cấu trúc If…Else… xét điều kiện để quyết định những lệnh T-SQL nào sẽ được thực hiện

Cú pháp:

If Biểu_thức_điều_kiện

Khối_lệnh

[ Else Khối_lệnh ]

Khối lệnh là một hoặc nhiều lệnh nằm trong cặp từ khóa Begin.....End

Ví dụ:

Cho 2 mã SV bất kỳ, kiểm tra xem 2 SV đó có cùng ngày sinh hay không?

Create Proc SP_SVCungNgaySinh

(@MaSV1 nvarchar(10), @MaSV2 nvarchar(10))

As

Begin

declare @NS1 smalldatetime

Select @MaKhoa1 = MaKhoa

From SinhVien sv inner join Lop l on sv.MaLop = l.MaLop

Where MaSV = @MaSV1

declare @NS2 smalldatetime

Select @MaKhoa2 = MaKhoa

From SinhVien sv inner join Lop l on sv.MaLop = l.MaLop

Where MaSV = @MaSV2

If @NS1 = @NS2

print N'2 SV có cùng ngày sinh'

Else

print N'2 SV khác ngày sinh với nhau'

End

Exec SP_SVCungNgaySinh 'SV0101','SV0102'

Sử dụng cấu trúc While

Thực hiện lặp lại một đoạn lệnh T-SQL khi điều kiện đúng

Cú pháp:

While Biểu_thức_điều_kiện

[Begin]

Khối_lệnh

[Break]

[Continue]

[End]

Trong đó:

Break: thoát khỏi vòng while hiện hành
Continue: trở lại đầu vòng while, bỏ qua các lệnh sau đó

Ví dụ:

Xét lược đồ quan hệ: SV(MaSV: int, HoTen: nvarchar(40))

Viết lệnh xác định 1 mã SV mới theo quy định: Mã SV tăng dần, nếu có chỗ trống thì mã mới sẽ chèn vào chỗ trống đó. Chẳng hạn nếu trong bảng SV đã có các mã SV 1,2,3,6 thì mã SV mới là 4

Declare @STT int

Set @STT = 1

While exists(select * from SV where MaSV = @STT)

set @STT = @STT+1

Insert into SV(MaSV, HoTen) values(@STT, N 'Nguyễn Văn Nam')

Sử dụng cấu trúc Case

Kiểm tra một dãy các điều kiện và trả về kết quả phù hợp với điều kiện đúng.

Lệnh Case được sử dụng như một hàm trong câu lệnh Select

Cú pháp:

Dạng 1 (simple case):

Case Biểu_thức_đầu_vào

When Giá_trị then Kết_quả

[...n]

[ Else Kết_quả_khác]

End

Dạng 2 (searched case):

Case

When Biểu_thức_đk then Kết_quả

[...n]

[ Else Kết_quả_khác]

End

Ví dụ:

Xét lược đồ quan hệ:

NhanVien(MaNV: int,

HoTen: nvarchar(40),

GioiTinh: nvarchar(3),

NgaySinh: datetime,

CapBac: smallint )

Cho biết những nhân viên đến tuổi nghỉ hưu biết rằng tuổi về hưu của nam là 60, của nữ là 55

select *

from NhanVien

where year(getdate()) – year(NgaySinh) > = Case GioiTinh

when ‘Nam’ then 60

when ‘Nu’ then 55

End

Sửa, xóa thủ tục lưu trữ

5.1. Sửa thủ tục lưu trữ

Khi một thủ tục đã được tạo ra, ta có thể tiến hành định nghĩa lại thủ tục đó bằng câu lệnh ALTER PROCEDURE có cú pháp như sau:

ALTER PROCEDURE tên_thủ_tục [(danh_sách_tham_số)]

[WITH RECOMPILE | ENCRYPTION

| RECOMPILE,ENCRYPTION]

AS

Các_câu_lệnh_Của_thủ_tục

5.2. Xóa thủ tục lưu trữ

Drop procedure tên_thủ_tục

Hỏi đáp

MÔN HỌC

Quản trị SQL Server

Nội dung tài liệu

Các tài liệu liên quan

Có thể bạn quan tâm

Thông tin tài liệu

MỤC LỤC

Bài 1. Tổng quan về hệ quản trị Cơ sở dữ liệu

Bài 2. Định nghĩa dữ liệu trong MS SQL Server

Bài 3: Thao tác dữ liệu trong MS SQL Server

Bài 4: Khung nhìn (View)