SQL Server - 10

union

select sbd, hoten, ngaysinh, quequan from Thisinh

where quequan='Thai Binh'

6) Phép kết nối

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 kết 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. Phép kết nối là cơ 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 kết 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 trường đượ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 kết nối, cần phải xác định được những yếu tố sau:

- Những trường 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ì. Các loại phép nối

- Phép kết nối trong (inner join)

+ Phép nối bằng: Một phép nối bằng (equi-join) là một phép nối trong đó giá trị của các trường đượ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 trường trong các bảng tham gia nối đều được đưa ra trong kết quả. 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. Trong danh sách chọn của câu lệnh chỉ giữ lại một trường trong hai trường tham gia vào điều kiện của phép nối.

+ 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 trường 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.

Cú pháp

+ Cách 1: Đặt sau mệnh đề Where tên_bảng_1.tên_trường=tên_bảng_2.tên_trường

+ Cách 2: Đặt sau mệnh đề From

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

Ví du: Đưa ra các thông tin gồm: số báo danh, họ tên, ngày sinh, quê quán của những thí sinh có quê quán „Nam Định‟ hoặc „Thái Bình‟.

Cách 1: use qlts

select sbd, hoten, ngaysinh, quequan, tenn from Thisinh, Nganh

where Thisinh.man=Nganh.man Cách 2:

use qlts

select sbd, hoten, ngaysinh, quequan, tenn

from Thisinh inner join Nganh on Thisinh.man=Nganh.man

- Phép nối ngoài: Với phép kết nối trong, chỉ những dòng có giá trị trong các trường đượ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. Phép kết nối trong đã 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, đôi khi 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 Server cung cấp các loại phép nối ngoài sau:

+ 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ú 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ụ: Đưa ra thông tin các ngành không có thí sinh đăng kí dự thi use qlts

select Nganh.man,Nganh.tenn,Nganh.chitieu,sbd

from Nganh left outer join Thisinh on Nganh.man=Thisinh.man where thisinh.man is NULL

7) Mệnh đề GROUP BY

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 (aggregate functions) đượ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 trường

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:

STT

Hàm gộp	Chức năng
1	SUM([ALL| DISTINCT] biểu_thức)	Tính tổng các giá trị
2	AVG([ALL| DISTINCT] biểu_thức)	Tính trung bình của các giá trị
3	COUNT([ALL|DISTINCT] biểu_thức)	Đếm số các giá trị trong biểu thức
4	COUNT(*)	Đếm số các dòng được chọn
5	MAX(biểu_thức)	Tính giá trị lớn nhất
6	MIN(biểu_thức)	Tính giá trị nhỏ nhất

Có thể bạn quan tâm!

• SQL Server - 7
• SQL Server - 8
• Kết Quả Khi Xác Định Tiêu Đề Trường
• Cơ Sở Dữ Liệu Do Người Dùng Tạo Ra
• Khai Báo Tự Động Tăng Dung Lượng
• Kết Quả Sau Tạo Ràng Buộc Khóa Ngoại Và Khóa Ngoại

Xem toàn bộ 323 trang tài liệu này.

Chú ý:

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

- 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. Khi muốn loại bỏ bớt các giá trị trùng nhau (chỉ giữ lại một giá trị), ta thêm từ khoá DISTINCT ở trước biểu thức là đối số của hàm.

- Nếu sau mệ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ệ.

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 mệnh đề SELECT. Khi đó, ta không sử dụng bất kỳ một tên trường hay biểu thức nào ngoài các hàm gộp trong mệnh đề SELECT.

Ví dụ 1: Đưa số lượng các thí sinh đăng kí dự thi. use qlts

select COUNT(*) from thisinh

Thống kê trên nhóm: Khi 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ụ 2: Đưa ra các thông tin gồm: Mã ngành, tên ngành, số thí sinh đăng kí dự thi của mỗi ngành.

SELECT Nganh.man, Nganh.tenn, COUNT(*) AS sots

FROM Nganh INNER JOIN Thisinh ON Nganh.man=Thisinh.man GROUP BY Nganh.man, Nganh.tenn

Mệnh đề HAVING dùng để xác định điều kiện trong hàm gộp. 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 mệ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.

8) Truy vấn con (Subquery)

Truy vấn con là một câu lệnh SELECT được lồng vào bên trong một câu lệnh SELECT, INSERT, UPDATE, DELETE hoặc bên trong một truy vấn con khác. Loại truy vấn này được sử dụng để biểu diễn cho những truy vấn trong đó điều kiện truy vấn dữ liệu cần phải sử dụng đến kết quả của một truy vấn khác.

Cú pháp:

(SELECT [ALL | DISTINCT] danh_sách_trường FROM danh_sách_bảng

[WHERE điều_kiện]

[GROUP BY danh_sách_trường] [HAVING điều_kiện])

Nguyên tắc khi sử dụng truy vấn con:

- Một truy vấn con phải được viết trong cặp dấu ngoặc. Trong hầu hết các trường hợp, một truy vấn con thường phải có kết quả là một trường (tức là chỉ có duy nhất một trường trong danh sách chọn).

- Mệnh đề COMPUTE và ORDER BY không được phép sử dụng trong truy vấn con.

- Các tên trường xuất hiện trong truy vấn con có thể là các trường của các bảng trong truy vấn ngoài.

Một truy vấn con thường được sử dụng làm điều kiện trong mệnh đề WHERE hoặc HAVING của một truy vấn khác.

Nếu truy vấn con trả về đúng một giá trị, nó có thể sử dụng như là một thành phần bên trong một biểu thức (chẳng hạn xuất hiện trong một phép so sánh bằng).

Các lượng từ dùng với truy vấn con:

- Lượng từ (ANY|ALL): Cú pháp:

WHERE biểu_thức  [ANY|ALL] (truy_vấn_con) Trong đó ={=, >, <, >=, <=, <>, !=}

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

Ví dụ 1: Đưa ra các thông tin gồm: Số báo danh, họ tên, ngày sinh của những thí sinh có tổng điểm 3 môn lớn nhất.

use qlts

select sbd,hoten,ngaysinh from Thisinh

where dtoan+dly+dhoa>=

all (select dtoan+dly+dhoa from Thisinh)

+ 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: Đưa ra các thông tin gồm: Mã ngành, tên ngành, chỉ tiêu tuyển sinh của tất cả các ngành có thí sinh đăng kí dự thi.

use qlts

select man, tenn, chitieu from Nganh

where man=any (select man from Thisinh)

- Lượng từ IN (NOT IN): Cú pháp

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

+ Lượng từ IN dùng để kiểm tra giá trị của một biểu thức có xuất hiện trong tập các giá trị của truy vấn con hay không.

Ví dụ 3: Đưa ra các thông tin gồm: Mã ưu tiên, tên ưu tiên, điểm cộng của tất cả các loại ưu tiên mà có thí sinh được hưởng.

use qlts

select maut, tenut, dc from uutien

where maut in (select maut from Thisinh)

+ Lượng từ NOT IN dùng để kiểm tra giá trị của một biểu thức không xuất hiện trong tập các giá trị của truy vấn con hay không.

Ví dụ 4: Đưa ra các thông tin gồm: Mã ngành, tên ngành, chỉ tiêu tuyển sinh của tất cả các ngành không có thí sinh đăng kí dự thi.

use qlts

select man, tenn, chitieu

from Nganh

where man not in (select man from Thisinh)

- Lượng từ EXISTS| NOT EXISTS:

Cú pháp:

WHERE [NOT] EXISTS (truy_vấn_con)

+ Lượng từ EXISTS trả về giá trị True nếu kết quả của truy vấn con có ít nhất một dòng.

Ví dụ: Kiểm tra xem có thí sinh nào quê „Nam Dinh‟ không. use qlts

if exists (select * from Thisinh$ where quequan='Nam Dinh') print 'Co thi sinh que Nam Dinh'

else print 'Khong co thi sinh que Nam Dinh'

+ Lượng từ NOT EXISTS trả về giá trị False nếu kết quả của truy vấn con không có dòng nào.

Ví dụ 5: Kiểm tra xem trường có đào tạo ngành „CNTT‟ hoặc „KHMT‟ không. use qlts

if not exists (select * from nganh where tenn='CNTT' or tenn='KHMT') print 'Khong dao tao nganh CNTT và KHMT'

else

print 'Co dao tao nganh CNTT và KHMT'

+ Khi sử dụng EXISTS, trong danh sách các thông tin đưa ra của truy vấn con có thể có nhiều hơn hai trường.

2.3.2. Câu lệnh INSERT

Tác dụng: Dùng để đưa các giá trị của trường vào bảng dữ liệu Cú pháp:

- Nhập dữ liệu trực tiếp:

INSERT INTO <Tên bảng > [( <Danh sách các trường> )] VALUES ( <Bản ghi tương ứng cần nhập> )

- Nhập dữ liệu từ bảng khác:

INSERT INTO <tên_bảng> [( <danh_sách_tên_trường>)]

<câu lệnh Select...From...>

Ví dụ: Thêm một bản ghi vào bảng mặt hàng.

INSERT INTO Mathang

VALUES („MH010‟,‟TIVI‟,‟Trang‟,‟Cai‟)

2.3.3. Câu lệnh UPDATE

Tác dụng: Lệnh UPDATE dùng để chỉnh sửa dữ liệu trong bảng

Cú pháp:

Trong đó:

UPDATE <Table_name>

SET <Column_Name = Value> [WHERE <Search condition>]

Table_Name: là tên bảng mà dữ liệu trong đó được thay đổi. Column_name: là tên trường dữ liệu được thay đổi.

Value: là giá trị dữ liệu thay đổi.

Search_condition: là điều kiện thỏa mãn mà các dữ liệu sẽ thay đổi.

Ví dụ: Tăng đơn giá của các mặt hàng đã được mua có đơn giá nhỏ hơn 50 lên 1%. use qlmbh

UPDATE muaban

SET dongia=dongia+dongia*0.1 WHERE dongia<50 and muaban=0

2.3.4. Câu lệnh DELETE

Tác dụng: Câu lệnh DELETE dùng để xóa các hàng dữ liệu trong bảng Cú pháp:

DELETE FROM <Table_name> [WHERE <Search condition>]

Trong đó:

Table_Name: Xác định tên bảng có dữ liệu cần xóa. Mệnh đề WHERE chỉ định điều kiện.

Ví dụ 1: Xóa những mặt hàng có màu xanh. DELETE

FROM mathang WHERE mau=‟xanh‟

Nếu mệnh đề WHERE không có trong câu lệnh DELETE, tất cả các bản ghi trong bảng sẽ bị xóa. 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ú pháp:

TRUNCATE TABLE tên_bảng

Ví dụ 1: Xóa tất cả các bản ghi trong bảng mặt hàng. TRUNCATE TABLE mathang

2.4. Ngôn ngữ điều khiển dữ liệu

Ngôn ngữ điều khiển dữ liệu dùng để thiết lập quyền truy cập trên các đối tượng cơ sở dữ liệu. Ngôn ngữ điều khiển dữ liệu được sử dụng để bảo mật cơ sở dữ liệu. Các quyền được điều khiển bằng cách sử dụng các câu lệnh GRANT, REVOKE và DENY.

2.4.1. Câu lệnh GRANT

Tác dụng: Đặc quyền Grant được sử dụng khi cơ sở dữ liệu được chia sẻ với các người dùng khác.

Cú pháp:

GRANT

{ALL | statement[,...]} ON Table_Name

TO Security_Account [,...] Trong đó:

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 Table_Name : tên của bảng mà các đặc quyền được đưa ra

Ví dụ: Gán quyền SELECT cho người dùng JOHN trên bảng Employee trong cơ sở dữ liệu AdventureWorks

GRANT SELECT

ON Employee TO JOHN

2.4.2. Câu lệnh REVOKE

Tác dụng: Lệnh REVOKE dùng để xóa các quyền đã gán trên các đối tượng của người dùng trong cơ sở dữ liệu hiện hành

Cú pháp:

REVOKE {ALL | statement[,...]} ON Table_Name FROM Security_Account [,...]

Ví dụ: Xóa quyền SELECT trên bảng Employee của người dùng JOHN trong cơ sở dữ liệu AdventureWorks

REVOKE SELECT

ON Employee FROM JOHN