ĐẠI HỌC CÔNG NGHỆ THÔNG TIN KHOA HỆ THỐNG THÔNG TIN Chương 4 Ngôn ngữ truy vấn SQL GV: ThS. Nguyễn Đình Loan Phương Nội dung 1. Ngôn ngữ định nghĩa dữ liệu 3. Ngôn ngữ thao tác dữ liệu 4.
Ngôn ngữ truy vấn dữ liệu 2 1. Giới thiệu SQL (Structured Query Language) Là ngôn ngữ chuẩn cho truy vấn và thao tác trên CSDL quan hệ Ngôn ngữ truy vấn khai báo không thủ tục (non-procedure). Một truy vấn SQL mô tả điều mình cần (what), không cần nêu cách thức có được nó (how) Được phát triển bởi IBM (1970s) Ban đầu được gọi là SEQUEL Được ANSI công nhận và phát triển thành chuẩn • SQL-86 • SQL-2003 • SQL-89 • SQL-2006 • SQL-92 • SQL-2008 • SQL-99 • SQL-2011 3 1. Giới thiệu SQL gồm Định nghĩa dữ liệu (DDL) Thao tác dữ liệu (DML) Truy vấn dữ liệu SQL sử dụng thuật ngữ Bảng ~ quan hệ Cột ~ thuộc tính Dòng ~ bộ SQL mang ngữ nghĩa bag Các biểu thức SQL có thể trả về các bộ trùng, nếu ta không loại bỏ chúng một cách tường minh 4 Nội dung 1.
Ngôn ngữ định nghĩa dữ liệu Tạo bảng Khai báo RBTV Sửa cấu trúc bảng Xóa bảng 3. Ngôn ngữ thao tác dữ liệu 4. Ngôn ngữ truy vấn dữ liệu 5 2. Ngôn ngữ định nghĩa dữ liệu Là ngôn ngữ mô tả Lược đồ cho mỗi quan hệ Miền giá trị tương ứng của từng thuộc tính Ràng buộc toàn vẹn Chỉ mục trên mỗi quan hệ Gồm CREATE TABLE (tạo bảng) DROP TABLE (xóa bảng) ALTER TABLE (sửa bảng) CREATE DOMAIN (tạo miền giá trị) CREATE DATABASE (tạo cơ sở dữ liệu) .
6 Kiểu dữ liệu Số tinyint, smallint, int, numeric(m,n), decimal(m,n), float, real, smallmoney, money bit Chuỗi ký tự varchar(n), char(n), nvarchar(n), nchar(n) Ngày tháng smalldatetime, datetime Chuỗi nhị phân binary, varbinary … 7 2.1 Tạo bảng Để định nghĩa một bảng Tên bảng Các thuộc tính Tên thuộc tính Kiểu dữ liệu Các RBTV trên thuộc tính Cú pháp: CREATE TABLE <Tên_bảng> ( <Tên_cột> <Kiểu_dữ_liệu> [<RBTV>], <Tên_cột> <Kiểu_dữ_liệu> [<RBTV>], … [<RBTV>] ) 8 Ví dụ tạo bảng CREATE TABLE NHANVIEN ( MANV CHAR(9), HONV VARCHAR(10), TENLOT VARCHAR(20), TENNV VARCHAR(10), NGSINH DATETIME, DCHI VARCHAR(50), PHAI CHAR(3), LUONG INT, MA_NQL CHAR(9), PHG INT ) 9 2.2 Khai báo RBTV <RBTV> NOT NULL NULL UNIQUE tên thuộc tính DEFAULT (giá trị mặc định) PRIMARY KEY (các thuộc tính khóa chính) FOREIGN KEY / REFERENCES CHECK (tên thuộc tính điều kiện) Đặt tên cho RBTV CONSTRAINT <Ten_RBTV> <RBTV> 10 Ví dụ CREATE TABLE NHANVIEN ( HONV VARCHAR(10) NOT NULL, TENLOT VARCHAR(20) NOT NULL, TENNV VARCHAR(10) NOT NULL, MANV CHAR(9) PRIMARY KEY, NGSINH DATETIME, DCHI VARCHAR(50), PHAI CHAR(3) CHECK (PHAI IN (‘Nam’, ‘Nu’)), LUONG INT DEFAULT (10000), MA_NQL CHAR(9), PHG INT ) 11 Ví dụ CREATE TABLE PHONGBAN ( TENPB VARCHAR(20) UNIQUE, MAPHG INT NOT NULL, TRPHG CHAR(9), NG_NHANCHUC DATETIME DEFAULT(GETDATE()) ) CREATE TABLE PHANCONG ( MA_NVIEN CHAR(9) FOREIGN KEY (MA_NVIEN) REFERENCES NHANVIEN(MANV), SODA INT REFERENCES DEAN(MADA), THOIGIAN DECIMAL(3,1) ) 12 Ví dụ đặt tên RBTV CREATE TABLE NHANVIEN ( HONV VARCHAR(10) CONSTRAINT NN_NV_HONV NOT NULL, TENLOT VARCHAR(20) NOT NULL, TENNV VARCHAR(10) NOT NULL, MANV CHAR(9) CONSTRAINT PK_NV_MANV PRIMARY KEY, NGSINH DATETIME, DCHI VARCHAR(50), PHAI CHAR(3) CONSTRAINT CK_NV_PHAI CHECK (PHAI IN (‘Nam’, ‘Nu’)), LUONG INT CONSTRAINT DF_NV_LUONG DEFAULT (10000), MA_NQL CHAR(9), PHG INT ) 13 Ví dụ đặt tên RBTV CREATE TABLE PHANCONG ( MA_NVIEN CHAR(9), SODA INT, THOIGIAN DECIMAL(3,1), CONSTRAINT PK_PC_MANVIEN_SODA PRIMARY KEY (MA_NVIEN, SODA), CONSTRAINT FK_PC_MANVIEN FOREIGN KEY ( MA_NVIEN) REFERENCES NHANVIEN(MANV), CONSTRAINT FK_PC_SODA FOREIGN KEY (SODA) REFERENCES DEAN(MADA) ) 14 2.3 Sửa bảng Được dùng để Thay đổi cấu trúc bảng Thay đổi RBTV Thêm cột ALTER TABLE <Tên_bảng> ADD <Tên_cột> <Kiểu_dữ_liệu> [<RBTV>] Xóa cột ALTER TABLE <Tên_bảng> DROP COLUMN <Tên_cột> Sửa cột ALTER TABLE <Tên_bảng> ALTER COLUMN <Tên_cột> <Kiểu_dữ_liệu_mới> 15 2.4 Xóa bảng Được dùng để xóa cấu trúc bảng Tất cả dữ liệu của bảng cũng bị xóa Cú pháp DROP TABLE <Tên_bảng> Ví dụ DROP TABLE NHANVIEN DROP TABLE PHONGBAN DROP TABLE PHANCONG 19 2.4 Xóa bảng Khi muốn xóa một bảng phải xóa tất cả những khóa ngoại tham chiếu tới bảng đó trước. NHANVIEN HONV TENLOT TENNV MANV NGSINH DCHI PHAI LUONG MA_NQL PHG PHONGBAN TENPHG MAPHG TRPHG NG_NHANCHUC 20 Tạo miền giá trị Tạo ra một kiểu dữ liệu mới kế thừa những kiểu dữ liệu có sẳn Cú pháp CREATE DOMAIN <Tên_kdl_mới> AS <Kiểu_dữ_liệu> [DEFAULT defaultValue] [CHECK (condition) Ví dụ CREATE DOMAIN Kieu_Ten AS VARCHAR(30) CREATE DOMAIN t_cvu AS CHAR(2) DEFAULT ‘KT’ CHECK(VALUE IN(NULL,‘TK’,‘LT’,‘PT’,‘KT’)) 21 Nội dung 1. Ngôn ngữ định nghĩa dữ liệu 3. Ngôn ngữ thao tác dữ liệu Thêm dữ liệu Sửa dữ liệu Xóa dữ liệu 4.
Ngôn ngữ truy vấn dữ liệu 22 3.1 Thêm dữ liệu Cú pháp INSERT INTO <tên bảng>(<danh sách các thuộc tính>) VALUES (<danh sách các giá trị>) Ví dụ: insert into SANPHAM values('BC01','But chi', 'cay', 'Singapore', 3000) insert into SANPHAM(masp,tensp,dvt,nuocsx,gia) values ('BC01','But chi', 'cay', 'Singapore', 3000) 23 3.2 Xóa dữ liệu Cú pháp DELETE FROM <tên bảng> [WHERE <điều kiện>] Ví dụ: Xóa toàn bộ nhân viên DELETE FROM NHANVIEN Xóa những sản phẩm do Trung Quốc sản xuất có giá thấp hơn 10000 DELETE FROM SANPHAM WHERE (Gia<10000) and (Nuocsx=‘Trung Quoc’) 24 3.3 Sửa dữ liệu Cú pháp UPDATE <tên bảng> SET <tên thuộc tính>=<giá trị mới>, <tên thuộc tính>=<giá trị mới>, … [WHERE <điều kiện>] Ví dụ: Tăng giá 10% đối với những sản phẩm do “Trung Quoc” sản xuất UPDATE SANPHAM SET Gia = Gia*1.1 WHERE Nuocsx=‘Trung Quoc’ 25 Nội dung 1. Ngôn ngữ định nghĩa dữ liệu 3. Ngôn ngữ thao tác dữ liệu 4. Ngôn ngữ truy vấn dữ liệu 26 Truy vấn cơ bản Dạng tổng quát của một câu truy vấn đơn giản gồm 3 mệnh đề SELECT <danh sách các cột> FROM <danh sách các bảng> [WHERE <điều kiện>] <danh sách các cột> Tên các cột cần được hiển thị trong kết quả truy vấn <danh sách các bảng> Tên các bảng liên quan đến câu truy vấn <điều kiện> Biểu thức boolean xác định dòng nào sẽ được rút trích Nối các biểu thức: AND, OR, và NOT Phép toán: , , , , , , LIKE và BETWEEN 27 Truy vấn cơ bản SQL và ĐSQH SELECT <danh sách các cột> FROM <danh sách các bảng> WHERE <điều kiện> SELECT L FROM R L ( C (R)) WHERE C 28 Ví dụ Lấy tất cả các cột của quan hệ kết quả SELECT * FROM NHANVIEN WHERE PHG=5 MANV HONV TENLOT TENNV NGSINH DCHI PHAI LUONG MA_NQL PHG 333445555 Nguyen Thanh Tung 12/08/1955 638 NVC Q5 Nam 40000 888665555 5 987987987 Nguyen Manh Hung 09/15/1962 Ba Ria VT Nam 38000 333445555 5 PHG=5 (NHANVIEN) 29 Mệnh đề SELECT Loại bỏ các dòng trùng nhau Sử dụng DISTINCT trong mệnh đề SELECT SELECT DISTINCT LUONG LUONG FROM NHANVIEN WHERE PHG=5 LUONG 30000 25000 25000 38000 38000 30 Mệnh đề SELECT(tt) Tên bí danh SELECT MANV, HONV AS HO, TENLOT AS ‘TEN LOT’, TENNV AS TEN FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ MANV HO TEN LOT TEN 333445555 Nguyen Thanh Tung 987987987 Nguyen Manh Hung MANV,HO,TEN LOT,TEN ( MANV,HONV,TENLOT,TENNV( PHG=5PHAI=‘Nam’(NHANVIEN))) 31 Mệnh đề SELECT (tt) Có thể chứa các biểu thức toán học liên quan tới +, -, *, / và thực hiện trên các hằng số hay thuộc tính của bộ SELECT MANV, HONV + ‘ ’ + TENLOT + ‘ ’ + TENNV AS ‘HO TEN’ FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ MANV HO TEN 333445555 Nguyen Thanh Tung 987987987 Nguyen Manh Hung SELECT MANV, LUONG*1.1 AS ‘LUONG10%’ FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ 32 Mệnh đề WHERE Mô tả các điều kiện mà kết quả phải thỏa Tương ứng với điều kiện chọn của ĐSQH Điều kiện kết hợp với nhau bởi AND, OR hay NOT Các so sánh có thể được áp dụng trên kết quả của các biểu thức toán học 33 Ví dụ Tìm các nhân viên có chức vụ LT thuộc phòng P3 SELECT * FROM NHANVIEN WHERE CVU=‘LT’ AND PHG=‘P3’ 34 Mệnh đề WHERE (tt) BETWEEN … AND… Ví dụ: Tìm tất cả nhân viên có lương tối thiểu là 20000 và lương tối đa là 45000 SELECT * FROM NHANVIEN WHERE LUONG>=20000 AND LUONG<=45000 SELECT * FROM NHANVIEN WHERE LUONG BETWEEN 20000 AND 45000 NOT BETWEEN… AND… SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG NOT BETWEEN 20000 AND 45000 35 Mệnh đề WHERE(tt) Độ ưu tiên SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE (TENPHG=‘Nghien cuu’ OR TENPHG=‘Quan ly’) AND PHG=MAPHG 36 Mệnh đề WHERE (tt) So sánh chuỗi Sử dụng bất kỳ phép so sánh nào chẳng hạn <, >, =, … Để so sánh gần đúng sử dụng LIKE ‘%’ thay thế chuỗi ký tự bất kỳ ‘_’ thay thế 1 ký tự bất kỳ Ví dụ: Cho biết tên các nhân viên bắt đầu bằng chữ ‘A’ SELECT TENNV FROM NHANVIEN WHERE TENNV LIKE ‘A%’ Cho biết tên các nhân viên bắt đầu bằng chữ ‘N’ và có bốn ký tự SELECT TENNV FROM NHANVIEN WHERE TENNV LIKE ‘N_ _ _’ 37 Mệnh đề WHERE (tt) So sánh chuỗi Các ký tự đặc biệt sử dụng ESCAPE hoặc [ ] Ví dụ: SELECT MASP, TENSP FROM SANPHAM WHERE MASP LIKE ‘B/_%’ ESCAPE ‘/’ ‘B_’ SELECT MASP, TENSP FROM SANPHAM WHERE MASP LIKE ‘B[_]%’ 38 Mệnh đề WHERE (tt) Giá trị NULL Các bộ có thể có giá trị NULL ở một số thuộc tính IS NULL (IS NOT NULL) được dùng để kiểm tra giá trị null Ví dụ: SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NULL SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NOT NULL Kết quả của biểu thức toán học nào liên quan tới null là null Ví dụ: 9 + null trả về null Các hàm tính toán bỏ qua các giá trị null 39 Mệnh đề WHERE (tt) Sử dụng IN để kiểm tra một giá trị thuộc tính có thuộc một tập giá trị cho trước không Ví dụ: Cho biết tên các nhân viên thuộc một trong các phòng P1, P2, P3 SELECT TENNV FROM NHANVIEN WHERE PHG IN (‘P1’, ‘P2’, ‘P3’) 40 Mệnh đề FROM Nhiều bảng có thể được truy vấn trong một lệnh SQL bằng cách liệt kê chúng trong mệnh đề FROM Chỉ ra điều kiện kết trên các thuộc tính tương ứng Nếu không chỉ ra điều kiện kết liên quan giữa các bảng trong mệnh đề WHERE thì câu truy vấn tương đương phép tích trong ĐSQH Ví dụ SELECT * FROM PHONGBAN, DEAN WHERE PHONGBAN.PHONG 41 Mệnh đề FROM (tt) Tên bí danh SELECT TENPHG, DIADIEM FROM PHONGBAN, DDIEM_PHG WHERE MAPHG=MAPHG SELECT TENPHG, DIADIEM FROM PHONGBAN AS PB, DDIEM_PHG AS DD WHERE PB.MAPHG SELECT TENNV, NGSINH, TENTN, NGSINH FROM NHANVIEN, THANNHAN WHERE MANV=MA_NVIEN SELECT TENNV, NV.NGSINH, TENTN, TN.NGSINH FROM NHANVIEN NV, THANNHAN TN WHERE MANV=MA_NVIEN 42 Ví dụ 1.
Cho biết tên các dự án có ngân sách lớn hơn 250000.