Bài giảng Kỹ thuật phần mềm ứng dụng - Chương 5: Ngôn ngữ truy vấn cấu trúc SQL

Kꢀ thuꢁt phꢂn mꢃm

ꢄng dꢅng

Chương 5

Ngôn ngữ truy vꢀn cꢀu trꢁc SQL

9/7/2017

Nội dung

. 5.1. Giới thiệu SQL

. 5.2. Phân loại SQL

. 5.3. Các lệnh SQL

2

5.1 Giới thiệu SQL

. Ngôn ngữ truy xuất CSDL quan hệ

. Là một ngôn ngữ phi thủ tục

. Là phương tiện được sử dụng để trao đổi với DBMS

. Câu lệnh giống ngôn ngữ tiếng Anh (dễ đọc, dễ hiểu

hơn tiếng Anh).

. Những câu lệnh của SQL được sử dụng để trích rút

và cập nhật dữ liệu trong một hoặc nhiều bảng của

cơ sở dữ liệu.

. SQL làm việc với hầu hết các hệ quản trị cơ sở dữ

liệu như MS Access, DB2, Informix, MS SQL

Server, Oracle, Sybase ..

3

5.2. Phân loại SQL

. DDL – Data Definition Language

– Làm việc với cấu trúc CSDL

. DML – Data Manipulation Language

– Làm việc với dữ liệu thực sự được lưu trữ

. DCL – Data Control Language

4

5.3 Các lệnh SQL

. Tạo và hủy cơ sở dữ liệu

. Tạo, hủy và sửa bảng dữ liệu

. Thêm, xóa, truy xuất dữ liệu từ bảng

5

Tạo và hủy CSDL

1. Tạo CSDL: Cú pháp CREATE DATABASE <Tên CSDL>.

Ví dụ: create database QLSV

2. Hủy CSDL: Cú pháp DROP DATABASE <Tên CSDL>.

Ví dụ: drop database QLSV.

6

Tạo bảng dữ liệu(Table)

Cú pháp CREATE TABLE <Tên bảng> (<Column 1><Kiểu dữ

liệu>[<RBTV>], <Column 2><Kiểu dữ

liệu>[<RBTV>],……<Column n><Kiểu dữ liệu>[<RBTV>]).

Ràng buộc toàn vẹn – RBTV: NOT NULL, NULL, UNIQUE, DEFAULT,

PRIMARY KEY, FOREIGN KEY /REFERENCES, CHECK

Ví dụ:

CREATE TABLE DM_KHOA (MA_KHOA INT PRIMARY

KEY,TEN_KHOA NVARCHAR(50) NULL,GHI_CHU NVARCHAR(255)

NULL)

7

Hủy và sửa bảng dữ liệu(Table)

– Hủy Table: Cú pháp DROP TABLE <Tên bảng>.

Ví dụ:

DROP TABLE DM_KHOA

– Sửa Table:

Sửa cột của bảng

ALTER TABLE <Tên bảng> ALTER COLUMN <New_Data_Type>

[RBTV] .

Ví dụ:

ALTER TABLE DM_KHOA ALTER COLUMN TEN_KHOA NVARCHAR(100) NOT NULL

Thêm cột của bảng

ALTER TABLE <Tên bảng> ADD <Column_Name><Kiểu dữ liệu>

[RBTV] .

Ví dụ:

ALTER TABLE DM_KHOA ADD SO_NAM_HOC INT NULL

8

Thêm, xóa dữ liệu khỏi bảng

. Thêm dữ liệu vào bảng:

Cú pháp: INSERT INTO <Tên bảng>(Column1, Column2, ..,

Column n) VALUES (Giá trị 1, Giá trị 2, …., Giá trị n)

Ví dụ: Cho cꢂc lưꢃc đồ QH sau

- DM_KHOA (MA_KHOA,TEN_KHOA,GHI_CH)

- DM_SINHVIEN (MSSV, HO_TEN, NGAY_SINH, DIEN_THOAI)

Thêm khꢄa vꢅi vꢆo bảng DM_KHOA:

INSERT INTO DM_KHOA (MA_KHOA,TEN_KHOA,GHI_CHU)

VALUES (1,'Khóa 30','Dꢆi hạn')

. Xóa dữ liệu khỏi bảng:

Cú pháp: DELETE FROM <Tên bảng> [WHERE <Điều kiện>]

Ví dụ:

9

DEL

ETE FR

OM DM_KHOA WHERE M

A_KHOA

=1

Truy xuất dữ liệu

SELECT [DISTINCT] <Column1,.., ColumnN>

FROM <Tên bảng 1>,[<Tên bảng 2>],…

[WHERE <Expression>]

[GROUP BY gColumn1, gColumn2,..

[HAVING <gExpression>] ]

[ORDER BY oColumn1, oColumn2 [DESC/ASC], …]

10

Truy xuất dữ liệu

– Truy xuất một cột trong bảng:

Cú pháp SELECT <Column_Name> FROM <Tên bảng>.

Ví dụ:

SELECT TEN_KHOA FROM DM_KHOA

– Truy xuất nhiều cột trong bảng:

Cú pháp: SELECT <Column_Name1,.., Column i> FROM <Tên bảng>.

Ví dụ:

SELECT TEN_KHOA,GHI_CHU FROM DM_KHOA

– Truy xuất tất cả các cột trong bảng:

Cú pháp SELECT * FROM <Tên bảng>.

Ví dụ:

SELECT * FROM DM_KHOA

Chú ý: Mệnh đề điều kiện WHERE <Điều kiện> thường được sử dụng trong câu lệnh truy xuất

để tìm được một kết quả thỏa mãn điều kiện nào đó, ví dụ :

SELECT * FROM DM_KHOA WHERE TEN_KHÓA=‘Dài hạn’

11

Mệnh đề Order By

. Mục đích: sắp xếp lại dữ liệu theo một trật tự nhất định.

. Phải được đặt ở cuối của dòng lệnh.

. Có thể phát triển rộng ra khi thực hiện sắp xếp theo nhiều cột,

– Ví dụ:

SELECT * FROM DM_SINHVIEN ORDER BY HO_TEN,NGAY_SINH

. Ta có thể dùng chỉ số thứ tự của cột trong bảng để thay cho tên cột,

– Ví du: thay cho câu lênh trên ta có thể sử dụng câu lệnh sau: SELECT

* FROM DM_SINHVIEN ORDER BY 2,3

. Chú ý: Mệnh đề ORDER BY sẽ mặc định sắp xếp theo chiều tăng dần, khi

đó nếu muốn sắp xếp theo chiều giảm dần phải thêm cú pháp DESC sau

mệnh đề ORDER BY,

– ví dụ:

SELECT * FROM DM_SINHVIEN ORDER BY HO_TEN DESC

12

Mệnh đề WHERE

. Mục đích: lọc dữ liệu theo một điều kiện nào đó. Khi đó SQL cung cấp

cho người sử dụng một tập các toán tử để lọc dữ liệu theo miền mong

muốn,

Chú ý: Nếu trong câu lệnh SELECT cùng sử dụng mệnh đề WHERE và

mệnh đề ORDER BY thì mện đề WHERE phải nằm trước mệnh đề

ORDER BY

Toán tử LIKE

Đây là toán tử được sử dụng trong trình lọc thông minh của SQL, với việc sử

dụng kết hợp với các ký tự đại diện toán tử LILE cung cấp rất nhiều các khả

năng lọc giữ liệu một cách thuận tiện và phong phú.

. Ký tự đại diện % (SQL Server):

- Tìm các cụm từ có ký tự đầu là một ký tự nào đó

cú pháp LIKE ‘<Ký tự cần tim>%’.

Ví dụ:

Giả sử ta muốn tìm tất cả các sinh viên có họ bắt đầu bằng ký tự ‘n’, Khi đó

ta se có cú pháp như sau:

SELECT * FROM DM_SINHVIEN WHERE HO_TEN LIKE 'n%‘

- Tìm các cụm từ có ký tự cuối là một ký tự nào đó

cú pháp LIKE ‘%<Ký tự cần tim>’

Ví dụ, ta muốn tìm tất cả các sinh viên có tên kết thúc là chữ i, khi đó cú

pháp như sau:

SELECT * FROM DM_SINHVIEN WHERE HO_TEN LIKE '%i‘

14

Toán tử LIKE

- Còn nếu khi ta không biết rõ cụm ký tự cần tìm ở nằm ở đâu

trong các cụm ký tự thì ta dùng cú pháp như sau: LIKE

‘%<Cụm ký tự cần tim>%’.

Ví dụ, ta muốn tìm tất cả các sinh viên mà họ tên có cụm từ

‘nam’, khi đó ta có cú pháp như sau:

SELECT * FROM DM_SINHVIEN WHERE HO_TEN LIKE

'%nam%‘

Chú ý: Ký tự đại diện % được sử dụng rất đa dạng và hiệu quả, tùy

thuộc vào cách đặt vị trí của nó.

. Ký tự đại diện (_) – Chỉ dùng cho SQL Server: Ký tự đại diện

_ trên nguyên tắc được sử dụng giống như ký tự đại diện %, song

có một điều khác biệt là ký tự đại diện _ chỉ đại diện cho một

một ký tự duy nhất

Ví dụ: SELECT * FROM DM_KHOA WHERE TEN_KHOA

LIKE 'a_a‘

15

. Ký tự đại diện khoảnh trống ([ ])- SQL Server : Ký tự đại diện

khoảng trống [ ] được sử dụng kết hợp với toán tử LIKE để tìm ra các

đối tượng với một đặc điểm nào đó mà đối tượng đó thỏa mãn.

Ví dụ: Ta có bảng danh mục khóa như sau:

Khi đó nếu ta muốn tìm tất cả các bản ghi mà phần ghi chú của nó bắt

đầu bằng chữ D hoặc H, thì ta sử dụng cú pháp lọc như sau: SELECT *

FROM DM_KHOA WHERE GHI_CHU LIKE '[DH]%‘, kết quả nhận

được như sau:

16

Còn nếu ta sử dụng cú pháp SELECT * FROM DM_KHOA WHERE

GHI_CHU LIKE '[^DH]%‘, ta sẽ nhận được kết quả như sau:

Chú ý: Không nên quá lạm dụng vào các ký tự đại diện, chỉ sử dụng chúng

khi thật sự cần thiết.

17

Một số hàm tính toán

. Hàm trung bình – AVG(): Dùng để tính giá trị trung bình cho

một cột nào đó

. Hàm đếm số bản ghi – Count():

Đếm số lần xuất hiện của giá trị hoặc số dòng dữ liệu của bảng.

Count(*) sẽ cho ra tất cả các các bản ghi kể cả các bản ghi có chứa

giá trị Null,

Count(Column_name) sẽ đưa chỉ đưa ra các cột có giá trị

. Hàm lấy giá trị lớn nhất – MAX(): Dùng để lấy giá trị lớn nhất

trong một cột nào đó.

. Hàm lấy giá trị nỏ nhất – MIN(): Dùng để lấy giá trị nhỏ nhất trong

một cột nào đó. Cách sử dụng tương tự như đối với hàm MAX().

. Hàm lấy giá trị tổng – SUM(): Dùng để lấy giá trị tổng của một cột

nào đó.

. Chú ý: Thường các hàm lấy giá trị tổng được sử dụng kèm với các

mệnh đề GROUP BY và HAVING.

18

Mệnh đꢃ GROUP BY và HAVING

. Mệnh đề GROUP BY dùng để nhóm các bản ghi có một số tính chất

giống nhau, khi đó các tính chất đó sẽ có mặt trong mệnh đề SELECT và

mệnh đề GROUP BY.

Ví dụ: Ở phần trước ta dùng hàm SUM() để tình tổng các giá trị trong

cột SO_LUONG, bây giờ ta lại muốn tính tổng số lượng cho từng loại vật

tư, khi đó ta sẽ có cu pháp như sau:

SELECT

TEN_VT,CHUNG_LOAI,DVT,SUM(SO_LUONG)

AS

SO_LUONG FROM DM_VT

GROUP BY TEN_VT,CHUNG_LOAI,DVT

Mệnh đề HAVING dùng để lọc các bản ghi vừa được nhóm bằng mệnh đề

GROUP BY.

Ví du: Ta cần tính tổng của từng loại vật tư, tuy nhiên chỉ đối với các vật

tư mà số lượng tổng của nó lơn hơn 50, khi đó ta sẽ sử dụng cú pháp sau:

SELECT TEN_VT,CHUNG_LOAI,DVT,SUM(SO_LUONG) AS

SO_LUONG FROM DM_VT

GROUP BY TEN_VT,CHUNG_LOAI,DVT

HAVING SUM(SO_LUONG)>50

19

Mệnh đꢃ GROUP BY và HAVING

Chú ý: Nếu khi trong câu lựa chọn SELECT có dử dụng cả mệnh

đề WHERE và HAVING thì mệnh đề WHERE sẽ được thực hiện

trước khi nhóm, còn mệnh đề HAVING sẽ được thực hiện sau khi

nhóm.

Ví dụ: Ta có cú pháp sau:

SELECT TEN_VT,CHUNG_LOAI,DVT,SUM(SO_LUONG) AS

SO_LUONG FROM DM_VT

WHERE CHUNG_LOAI='Xe nguyen chiec'

GROUP BY TEN_VT,CHUNG_LOAI,DVT

HAVING SUM(SO_LUONG)>50

20