Bài giảng SQL - Chương 5: Ngôn ngữ SQL

́nh Ngôn ngữ định nghĩa dữ liệu: tạo bảng, sửa cấu trúc bảng̣, xóa bảng Ngôn ngữ thao tác dữ liệu: thêm, xóa, sửa dữ liệu, và truy vấn dữ liệu. Ngôn ngữ điều khiển dữ liệu: cấp quyền và thu hồi quyền sử dụng trên cơ sở dữ liệu. Tương quan giữa SQL và ngôn ngữ ĐSQH Ngôn ngữ định nghĩa dữ liệu Ngôn ngữ định nghĩa dữ liệu (DDL– Data Definition Language) Bao gồm: Lệnh tạo bảng (CREATE...) Lệnh sửa cấu trúc bảng (ALTER...) Lệnh xóa bảng (DROP...) Ngôn ngữ thao tác dữ liệu Ngôn ngữ thao tác dữ liệu (DML – Data Manipulation Language) Bao gồm: Lệnh thêm dữ liệu (INSERT...) Lệnh sửa dữ liệu (UPDATE...) Lệnh xóa dữ liệu (DELETE...) Truy vấn dữ liệu (SELECT...) Ngôn ngữ điều khiển dữ liệu Ngôn ngữ điều khiển dữ liệu (DCL – Data Control Language) Bao gồm: Lệnh cấp quyền cho người sử dụng cơ sở dữ liệu (GRANT...) Lệnh thu hồi quyền hạn của người sử dụng cơ sở dữ liệu (REVOKE...) Ngôn ngữ định nghĩa dữ liệu Tạo bảng (1) Cú pháp 	CREATE TABLE tên_bảng 	( 	tên_cột1 kiểu_dữ_liệu [not null], 	tên_cột2 kiểu_dữ_liệu [not null], 	… 	tên_cộtn kiểu_dữ_liệu [not null], 	khai báo khóa chính, khóa ngoại, ràng buộc toàn vẹn 	) Ngôn ngữ định nghĩa dữ liệu  Tạo bảng (2) Ngôn ngữ định nghĩa dữ liệu Tạo bảng (3) Cho lược đồ CSDL “quản lý đề án cty” như sau NHANVIEN (MaNV, HoNV, TenLot, TenNV, Phai, Luong, Phong, NgaySinh, DiaChi, Ma_NQL) PHONGBAN (MaPHG, TenPHG, TrPHG, NG_NhanChuc) DEAN (MaDA, TenDA, DDIEM_DA, Phong) PHANCONG (MaNV, MaDA, ThoiGian) DIADIEM_PHG (MaPHG, DIADIEM) THANNHAN (MaNV, TenTN, Phai, NGSinh,QuanHe) Ngôn ngữ định nghĩa dữ liệu Tạo bảng (4) Ví dụ: câu lệnh để tạo một bảng nhân viên 	CREATE TABLE NHANVIEN (	MANV varchar(10) NOT NULL, 	HONV varchar(50) NOT NULL, TENLOT varchar(50) NOT NULL, 	TENNV varchar(50) NOT NULL, NGAYSINH datetime,	 	PHAI varchar(3) NOTNULL, DIACHI varchar(100), 	MA_NQL varchar(10), 	PHONG varchar(10), 	LUONG numeric, 	CONSTRAINT PK_NV PRIMARY KEY (MANV), 	CONSTRAINT FK_NV_PB FOREIGN KEY (PHONG) REFERENCES PHONGBAN (MAPHG) ) Ngôn ngữ định nghĩa dữ liệu Sửa cấu trúc bảng (1) Thêm thuộc tính ALTER TABLE tên_bảng ADD tên_cột kiểu_dữ_liệu Ví dụ: thêm cột Ghi_chú vào bảng nhân viên ALTER TABLE NHANVIEN ADD GHI_CHU varchar(20) Sửa kiểu dữ liệu thuộc tính ALTER TABLE tênbảng ALTER COLUMN tên_cột kiểu_dữ_liệu_mới Ngôn ngữ định nghĩa dữ liệu Sửa cấu trúc bảng (2) Ví dụ: sửa kiểu dữ liệu của cột Ngày sinh ALTER TABLE NHANVIEN ALTER COLUMN NGAYSINH SMALLDATETIME Xóa thuộc tính ALTER TABLE tên_bảng DROP COLUMN tên_cột Ví dụ: xóa cột Ghi_chú từ bảng nhân viênALTER TABLE NHANVIEN DROP COLUMN GHI_CHU Ngôn ngữ định nghĩa dữ liệu Sửa cấu trúc bảng (3) Thêm ràng buộc toàn vẹn ALTER TABLE ADD CONSTRAINT UNIQUE tên_cột PRIMARY KEY tên_cột FOREIGN KEY tên_cột REFERENCES tên_bảng (cột_là_khóa_chính) [ON DELETE CASCADE] [ON UPDATE CASCADE] CHECK (tên_cột điều_kiện) Ngôn ngữ định nghĩa dữ liệu Sửa cấu trúc bảng (4) Ví dụ ALTER TABLE NHANVIEN ADD CONSTRAINT PK_NV PRIMARY KEY (MANV) ALTER TABLE NHANVIEN ADD CONSTRAINT FK_NV_PB FOREIGN KEY (PHONG) REFERENCES PHONGBAN(MAPHG) ALTER TABLE NHANVIEN ADD CONSTRAINT CHK CHECK ( PHAI IN (‘Nam') OR (‘Nu’)) ALTER TABLE NHANKHAU ADD CONSTRAINT UQ_NK UNIQUE (CMND) Ngôn ngữ định nghĩa dữ liệu Sửa cấu trúc bảng (5) Xóa ràng buộc toàn vẹn ALTER TABLE tên_bảng DROP CONSTRAINT tên_ràng_buộc Ví dụ ALTER TABLE NHANVIEN DROP CONSTRAINT FK_NV_PB Ngôn ngữ định nghĩa dữ liệu Xóa bảng Cú pháp DROP TABLE tên_bảng Ví dụ: xóa bảng (table) nhân viên DROP TABLE NHANVIEN Ví dụ: xóa bảng (table) phân công DROP TABLE PHANCONG Ngôn ngữ thao tác dữ liệu Thêm dữ liệu vào bảng Cú pháp INSERT INTO tên_bảng VALUES (giá_trị_1, giá_trị_2,…, giá_trị_n) INSERT INTO tên_bảng (cột1, cột2) VALUES (giá_trị_1, giá_trị_2) Ví dụ INSERT INTO NHANVIEN VALUES (‘001’, ‘Vuong’, ‘Ngoc’, ‘Quyen’, ’01/01/1977’, ‘Nu’, ’450 Trung Vuong, Ha Hoi’, ‘12345’, ‘QL’, 2000) Ngôn ngữ thao tác dữ liệu Sửa dữ liệu của bảng Cú pháp UPDATE tên_bảng SET cột_1 = giá_trị_1, cột_2 = giá_trị_2 [WHERE điều_kiện] Ví dụ: Sửa họ nhân viên có mã số ‘001’ thành ‘Nguyen’ 	UPDATE NHANVIEN SET HONV = ‘Nguyen’ WHERE MANV=‘001’ Ngôn ngữ thao tác dữ liệu Sửa dữ liệu của bảng Cú pháp Ví dụ: Sửa họ tên của nhân viên có mã số ‘001’ thành ‘Nguyen Thanh Tung’ và ngày sinh mới là 1/1/1978 UPDATE NHANVIEN SET HONV = ‘Nguyen’, TENLOT = ‘Thanh’, TENNV = Tung’, NGAYSINH=‘1/1/1978’ WHERE MANV=‘001’ Ngôn ngữ thao tác dữ liệu Xóa dữ liệu trong bảng Cú pháp DELETE FROM tên_bảng [WHERE điều_kiện] Ví dụ: xóa nhân viên có mã số ‘001’ DELETE FROM NHANVIEN WHERE MANV=‘001’ Ví dụ: xóa toàn bộ nhân viên 	DELETE FROM NHANVIEN Ngôn ngữ thao tác dữ liệu Câu truy vấn SELECT Câu truy vấn tổng quát SELECT [DISTINCT] tên_cột | hàm FROM bảng [WHERE điều_kiện] [GROUP BY cột] [HAVING điều_kiện] [ORDER BY cột ASC | DESC] Ngôn ngữ thao tác dữ liệu Toán tử truy vấn (1) Toán tử so sánh = > = Toán tử logic:	AND, OR, NOT Phép toán: +, - ,* , / Ngôn ngữ thao tác dữ liệu Toán tử truy vấn (2) Các toán tử so sánh khác BETWEEN - định nghĩa một đoạn giá trị liên tục IS NULL - kiểm tra giá trị thuộc tính có null hay không LIKE – kiểm tra chuỗi ký tự tương tự IN – kiểm tra giá trị thuộc tính có thuộc tập hợp các giá trị đã định nghĩa hay không EXISTS – mang giá trị TRUE nếu mệnh đề so sánh trả về ít nhất một bộ (record), FALSE nếu ngược lại Ngôn ngữ thao tác dữ liệu Toán tử truy vấn (3) Toán tử BETWEEN Ví dụ SELECT * FROM NHANVIEN WHERE year(NGAYSINH) BETWEEN 1965 AND 1977 	SELECT n.MANV, n.TENNV, p.TENPHG 	FROM NHANVIEN n, PHONGBAN p 	WHERE n.PHONG=p.MAPHG 	AND n.LUONG NOT BETWEEN 1000 AND 3000 Ngôn ngữ thao tác dữ liệu Toán tử truy vấn (4) Toán tử IS NULL Ví dụ a. SELECT * FROM NHANVIEN 	 WHERE MA_NQL IS NOT NULL b. SELECT h.MAHV,h.HOTEN,h.DIACHI 	FROM HOCVIEN h, BIENLAI b WHERE h.MAHV=b.MAHV AND b.TIENNOP IS NULL Ngôn ngữ thao tác dữ liệu Toán tử truy vấn (5) Toán tử LIKE So sánh chuỗi tương đối Cú pháp: s LIKE p, p có thể chứa % hoặc _ % : thay thế một chuỗi ký tự bất kỳ _ : thay thế một ký tự bất kỳ Ví dụ	 SELECT * FROM NHANVIEN 	 WHERE HONV LIKE ‘Nguyen%’ Ngôn ngữ thao tác dữ liệu Toán tử truy vấn (6) Toán tử IN Ví dụ a. SELECT * FROM NHANVIEN 	 WHERE PHONG IN (‘NC’,’QL’,’DH’) b. SELECT MANV, TENNV, DIACHI 	 FROM NHANVIEN WHERE MANV NOT IN (SELECT MANV 	FROM PHANCONG) Ngôn ngữ thao tác dữ liệu Toán tử truy vấn (7) Toán tử EXISTS Ví dụ a. SELECT d.TENDA, p.TENPHG FROM PHONGBAN p, DEAN d WHERE p.MAPHG = d.PHONG 	 AND EXISTS (SELECT pc.MADA FROM PHANCONG pc 	 WHERE d.MADA=pc.MADA) b. SELECT * FROM NHANVIEN n WHERE NOT EXISTS 	(SELECT * FROM DEAN d WHERE NOT EXISTS 	(SELECT * FROM PHANCONG p WHERE 	n.MANV=p.MANV AND d.MADA=p.MADA)) Ngôn ngữ thao tác dữ liệu Mệnh đề GROUP BY (1) Mệnh đề GROUP BY Chia các dòng thành các nhóm dựa trên tập con của các thuộc tính Tất cả các thành viên của nhóm đều thỏa các thuộc tính này. Mỗi nhóm được mô tả bằng một dòng các thuộc tính, được giới hạn bởi: Các thuộc tính chung của tất cả thành viên thuộc nhóm (được liệt kê trong mệnh đề GROUP BY). Các phép toán trên nhóm. Ngôn ngữ thao tác dữ liệu Mệnh đề GROUP BY (2) nhóm Các thuộc tính GROUP BY a a b b c c c c c d d d Chia các dòng thành các nhóm dựa trên tập con của các thuộc tính Ngôn ngữ thao tác dữ liệu Mệnh đề GROUP BY (3) Các hàm SQL cơ bản COUNT: Đếm số bộ dữ liệu của thuộc tính MIN: Tính giá trị nhỏ nhất MAX: Tính giá trị lớn nhất AVG: Tính giá trị trung bình SUM: Tính tổng giá trị các bộ dữ liệu Ngôn ngữ thao tác dữ liệu Mệnh đề GROUP BY (4) Ví dụ: tìm tổng lương, lương lớn nhất, lương ít nhất và lương trung bình của các nhân viên SELECT SUM(LUONG), MAX(LUONG), MIN(LUONG), AVG(LUONG) FROM NHANVIEN ; Ví dụ: tìm tổng lương, lương lớn nhất, lương ít nhất và lương trung bình của các nhân viên phòng “Nghiên cứu” SELECT SUM(LUONG), MAX(LUONG), MIN(LUONG), AVG(LUONG) from NHANVIEN , PHONGBAN WHERE MAPHG=PHONG AND TENPHG='Nghien cuu'; Ví dụ: cho biết số lượng nhân viên 	SELECT COUNT(*) FROM NHANVIEN; Ngôn ngữ thao tác dữ liệu Mệnh đề GROUP BY (5) Ví dụ 	SELECT n.MANV, n.TENNV, n.PHONG, MIN(p.THOIGIAN) thap_nhat, 	MAX (p.THOIGIAN) cao_nhat, AVG(p.THOIGIAN) trung_binh, 	SUM (p.THOIGIAN) tong_so_gio 	FROM NHANVIEN n, PHANCONG p 	WHERE n.MANV=p.MANV 	GROUP BY n.MANV, n.HOTEN, n.PHONG Ngôn ngữ thao tác dữ liệu Mệnh đề HAVING Mệnh đề HAVING Lọc kết quả theo điều kiện, sau khi đã gom nhóm Điều kiện của HAVING là các thuộc tính trong danh sách GROUP BY và các phép tính toán khác. Ngôn ngữ thao tác dữ liệu Mệnh đề HAVING Ví dụ: cho biết tên từng phòng ban và tổng số nhân viên, mức lương trung bình của các phòng có mức lương trung bình trên 2000 SELECT p.TENPHG, COUNT(*) so_luong_nv, AVG(n.LUONG) luong_tb FROM NHANVIEN n, PHONGBAN p WHERE n.PHONG = p.MAPHG GROUP BY p.TENPHB HAVING AVG(n.LUONG) > 2000 Ngôn ngữ thao tác dữ liệu Truy vấn con (Subquery) Subquery Các mệnh đề SELECT được lồng vào nhau Kết quả của câu SELECT này là điều kiện của câu SELECT khác. Các mệnh đề SELECT được nối với nhau bằng các phép so sánh ,,=,>=,= ALL (SELECT SUM(THOIGIAN) FROM PHANCONG GROUP BY (MANV) Phép chọn: () tương ứng mệnh đề WHERE trong SQL Phép chiếu:  () tương ứng mệnh đề SELECT trong SQL Các phép toán tập hợp: Hợp tương ứng mệnh đề UNION trong SQL Tích Descartes (R  S: tên khác nhau ) tương ứng mệnh đề FROM trong SQL Kết (theta): chỉ là các liên từ trong điều kiện Equi-join: Tất cả đều là phép so sánh bằng Natural Join: Kết trên các thuộc tính có cùng tên Outer Join: 1) kết o fr and s, 2) r, not s, 3) s, not r Tương quan giữa SQL và ĐSQH Phép chia: R/S, tìm các thể hiện trong R thỏa tất cả các bộ trong S Tương quan giữa SQL và ĐSQH SELECT C.TENNV FROM NHANVIEN C WHERE C.MANV = ‘001’ Ví dụ: Cách tiếp cận: Mệnh đề FROM tạo tích Descartes của các bảng được liệt kê. Tương quan giữa SQL và ĐSQH Mệnh đề WHERE gán các dòng vào trong C theo trình tự và tạo bảng chỉ chứa các dòng thỏa điều kiện. Mệnh đề SELECT chỉ lưu lại các cột được liệt kê Tương đương với: TenNVMaNV=‘001’(NhanVien)