Bài giảng Cơ sở dữ liệu (Database) - Chương 5: SQL (Structured Query Language)

Chương 5 SQL (Structured( Query Language) Nội dung chi tiết ƒ Giới thiệu ƒ Định nghĩa dữ liệu ƒ Truy vấn dữ liệu ƒ Cập nhật dữ liệu ƒ Khung nhìn (view) ƒ Chỉ mục (index) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 2 1 Giới thiệu ƒ Ngôn ngữ ĐSQH - Cách thức truy vấn dữ liệu - Khó khăn cho người sử dụng ƒ SQL (Structured Query Language) - Ngôn ngữ cấp cao - Người sử dụng chỉ cần đưa ra nội dung cần truy vấn - Được phát triển bởi IBM (1970s) - Được gọi là SEQUEL - Được ANSI công nhận và phát triển thành chuẩn y SQL-86 y SQL-92 y SQL-99 Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 3 Giới thiệu (tt) ƒ SQL gồm - Định nghĩa dữ liệu (DDL) - Thao tác dữ liệu (DML) - Định nghĩa khung nhìn Lý thuyết : Chuẩn SQL-92 - Ràng buộc toàn vẹn Ví dụ : SQL Server - Phân quyền và bảo mật - Điều khiển giao tác ƒ SQL sử dụng thuật ngữ - Bảng ~ quan hệ - Cột ~ thuộc tính - Dòng ~ bộ Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 4 2 Nội dung chi tiết ƒ Giới thiệu ƒ Định nghĩa dữ liệu - Kiểu dữ liệu - Các lệnh định nghĩa dữ liệu ƒ Truy vấn dữ liệu ƒ Cập nhật dữ liệu ƒ Khung nhìn (view) ƒ Chỉ mục (index) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 5 Đị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 Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 6 3 Kiểu dữ liệu ƒ Số (numeric) - INTEGER - SMALLINT - NUMERIC, NUMERIC(p), NUMERIC(p,s) - DECIMAL, DECIMAL(p), DECIMAL(p,s) - REAL - DOUBLE PRECISION - FLOAT, FLOAT(p) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 7 Kiểu dữ liệu (tt) ƒ Chuỗi ký tự (character string) - CHARACTER, CHARACTER(n) - CHARACTER VARYING(x) ƒ Chuỗi bit (bit string) - BIT, BIT(x) - BIT VARYING(x) ƒ Ngày giờ (datetime) - DATE gồm ngày, tháng và năm - TIME gồm giờ, phút và giây - TIMESTAMP gồm ngày và giờ Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 8 4 Lệnh tạo Database - bảng ƒ CREATE DATABASE ƒ Để định nghĩa một bảng - Tên bảng - Các thuộc tính y Tên thuộc tính y Kiểu dữ liệu y Các RBTV trên thuộc tính ƒ Cú pháp CREATE TABLE ( [], [], [] ) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 9 Ví dụ -Tạo bảng CREATE TABLE NHANVIEN ( MANV CHAR(9), HONV VARCHAR(50), TENNV VARCHAR(10), NGSINH DATETIME, DCHI VARCHAR(50), PHAI CHAR(3), LUONG INT, MA_NQL CHAR(9), PHG INT ) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 10 5 Lệnh tạo bảng (tt) ƒ - NOT NULL - NULL - UNIQUE - DEFAULT - PRIMARY KEY - FOREIGN KEY / REFERENCES - CHECK ƒ Đặt tên cho RBTV CONSTRAINT Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 11 Ví dụ -RBTV CREATE TABLE NHANVIEN ( HONV VARCHAR(50) 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 ) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 12 6 Ví dụ -RBTV 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) ) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 13 Ví dụ - Đặt tên cho RBTV CREATE TABLE NHANVIEN ( HONV VARCHAR(50) CONSTRAINT NV_HONV_NN NOT NULL, TENNV VARCHAR(10) NOT NULL, MANV CHAR(9) CONSTRAINT NV_MANV_PK PRIMARY KEY, NGSINH DATETIME, DCHI VARCHAR(50), PHAI CHAR(3) CONSTRAINT NV_PHAI_CHK CHECK (PHAI IN (‘Nam’, ‘Nu’)), LUONG INT CONSTRAINT NV_LUONG_DF DEFAULT (10000), MA_NQL CHAR(9), PHG INT ) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 14 7 Ví dụ - Đặt tên cho RBTV CREATE TABLE PHANCONG ( MA_NVIEN CHAR(9), SODA INT, THOIGIAN DECIMAL(3,1), CONSTRAINT PC_MANVIEN_SODA_PK PRIMARY KEY (MA_NVIEN, SODA), CONSTRAINT PC_MANVIEN_FK FOREIGN KEY (MA_NVIEN) REFERENCES NHANVIEN(MANV), CONSTRAINT PC_SODA_FK FOREIGN KEY (SODA) REFERENCES DEAN(MADA) ) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 15 Lệnh sửa bảng ƒ Được dùng để - Thay đổi cấu trúc bảng - Thay đổi RBTV ƒ Thêm cột ALTER TABLE ADD COLUMN [] ƒ Xóa cột ALTER TABLE DROP COLUMN ƒ Mở rộng cột ALTER TABLE ALTER COLUMN Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 16 8 Ví dụ - Thay đổi cấu trúc bảng ALTER TABLE NHANVIEN ADD NGHENGHIEP CHAR(20) ALTER TABLE NHANVIEN DROP COLUMN NGHENGHIEP ALTER TABLE NHANVIEN ALTER COLUMN NGHENGHIEP CHAR(50) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 17 Lệnh sửa bảng (tt) ƒ Thêm RBTV ALTER TABLE ADD CONSTRAINT , CONSTRAINT , ƒ Xóa RBTV ALTER TABLE DROP Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 18 9 Ví dụ - Thay đổi RBTV CREATE TABLE PHONGBAN ( TENPB VARCHAR(20), MAPHG INT NOT NULL, TRPHG CHAR(9), NG_NHANCHUC DATETIME ) ALTER TABLE PHONGBAN ADD CONSTRAINT PB_MAPHG_PK PRIMARY KEY (MAPHG), CONSTRAINT PB_TRPHG FOREIGN KEY (TRPHG) REFERENCES NHANVIEN(MANV), CONSTRAINT PB_NGNHANCHUC_DF DEFAULT (GETDATE()) FOR (NG_NHANCHUC), CONSTRAINT PB_TENPB_UNI UNIQUE (TENPB) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 19 Lệnh 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 ƒ Ví dụ DROP TABLE PHONGBAN DROP TABLE NHANVIEN DROP TABLE PHANCONG Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 20 10 Lệnh xóa bảng (tt) NHANVIEN HONV TENLOTTENNV MANV NGSINH DCHI PHAI LUONG MA_NQL PHG PHONGBAN TENPHG MAPHG TRPHG NG_NHANCHUC Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 21 Lệnh tạo và xóa 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 AS DROP DOMAIN AS ƒ Ví dụ CREATE DOMAIN Chuoi AS VARCHAR(50) DROP DOMAIN Chuoi Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 22 11 Nội dung chi tiết ƒ Giới thiệu ƒ Định nghĩa dữ liệu ƒ Truy vấn dữ liệu - Truy vấn cơ bản - Tập hợp, so sánh tập hợp và truy vấn lồng - Hàm kết hợp và gom nhóm - Một số kiểu truy vấn khác ƒ Cập nhật dữ liệu ƒ Khung nhìn (view) ƒ Chỉ mục (index) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 23 Truy vấn dữ liệu ƒ Là ngôn ngữ rút trích dữ liệu thỏa một số điều kiện nào đó ƒ Dựa trên Phép toán ĐSQH + Một số bổ sung - Cho phép 1 bảng có nhiều dòng trùng nhau Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 24 12 Truy vấn cơ bản (SELECT) ƒ Cú pháp tổng quát SELECT FROM WHERE GROUP BY HAVING ORDER BY Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 25 Truy vấn cơ bản ƒ - Tên các cột cần được hiển thị trong kết quả truy vấn ƒ - Tên các bảng liên quan đến câu truy vấ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, BETWEEN, EXISTS, ANY/SOME, ALL, IN Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 26 13 Truy vấn cơ bản (tt) ƒ SQL và ĐSQH πSELECT ×FROM σ WHERE πSELECTL (σC (R))L FROM R WHERE C Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 27 Ví dụ Lấy tất cả các cột của SELECT * quan hệ kết quả FROM NHANVIEN WHERE PHG=5 MANV HONVTENLOT TENNV NGSINH DCHI PHAI LUONGMA_NQL PHG 333445555 NguyenThanh Tung 12/08/1955 638 NVC Q5 Nam 40000888665555 5 987987987 NguyenManh Hung 09/15/1962 Ba Ria VT Nam 38000333445555 5 σPHG=5 (NHANVIEN) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 28 14 Mệnh đề SELECT SELECT MANV, HONV, TENLOT, TENNV FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ MANV HONV TENLOT TENNV 333445555 Nguyen Thanh Tung 987987987 Nguyen Manh Hung πMANV,HONV,TENLOT,TENNV(σPHG=5 ∧ PHAI=‘Nam’ (NHANVIEN)) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 29 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 33344555 Nguyen Thanh Tung 987987985 Nguyen Manh Hung 7 ρMANV,HO,TEN LOT,TEN(πMANV,HONV,TENLOT,TENNV(σPHG=5∧PHAI=‘Nam’(NHANVIEN))) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 30 15 Mệnh đề SELECT (tt) Mở rộng 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 ρMANV,HO TEN(πMANV,HONV+TENLOT+TENNV(σPHG=5∧PHAI=‘Nam’(NHANVIEN))) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 31 Mệnh đề SELECT (tt) Mở rộng SELECT MANV, LUONG*1.1 AS ‘LUONG10%’ FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ MANV LUONG10% 333445555 33000 987987987 27500 ρMANV,LUONG10%(πMANV,LUONG*1.1(σPHG=5∧PHAI=‘Nam’(NHANVIEN))) Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 32 16 Mệnh đề SELECT (tt) Loại bỏ các dòng trùng nhau SELECT LUONGDISTINCT LUONG FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ LUONG 30000 25000 -Tốn chi phí 2500038000 38000 - Người dùng muốn thấy Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 33 Ví dụ ƒ Cho biết MANV và TENNV làm việc ở phòng ‘Nghien cuu’ R1 ← NHANVIEN PHG=MAPHG PHONGBAN KQ ← πMANV, TENNV (σTENPHG=‘Nghien cuu’(R1)) SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 34 17 Mệnh đề WHERE SELECT MANV, TENNV Biểu thức luận lý FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG TRUE TRUE Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 35 Mệnh đề WHERE (tt) Độ ưu tiên SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE (TENPHG=‘K Toan’ OR TENPHG=‘Quan ly’) AND PHG=MAPHG Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 36 18 Mệnh đề WHERE (tt) BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG>20000 AND LUONG<30000 SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG BETWEEN 20000 AND 30000 Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 37 Mệnh đề WHERE (tt) NOT BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG NOT BETWEEN 20000 AND 30000 Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 38 19 Mệnh đề WHERE (tt) LIKE SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE ‘Nguyen _ _ _ _’ Ký tự bất kỳ SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE ‘Nguyen %’ Chuỗi bất kỳ Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 39 Mệnh đề WHERE (tt) NOT LIKE SELECT MANV, TENNV FROM NHANVIEN WHERE HONV LIKE ‘Nguyen’ SELECT MANV, TENNV FROM NHANVIEN WHERE HONV NOT NOT LIKE ‘Nguyen’ Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 40 20 Mệnh đề WHERE (tt) ESCAPE SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE ‘% Nguyens_%’ ESCAPE ‘s’ ‘Nguyen_’ Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 41 Mệnh đề WHERE (tt) Ngày giờ SELECT MANV, TENNV FROM NHANVIEN WHERE NGSINH BETWEEN ‘1955-12-08’ AND ‘1966-07-19’ ‘1955-12-08’ YYYY-MM-DD ’17:30:00’ HH:MI:SS ’12/08/1955’ MM/DD/YYYY ’05:30 PM’ ‘December 8, 1955’ ‘1955-12-08 17:30:00’ Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 42 21 Mệnh đề WHERE (tt) NULL ƒ Sử dụng trong trường hợp - Không biết (value unknown) - Không thể áp dụng (value inapplicable) - Không tồn tại (value withheld) ƒ Những biểu thức tính toán có liên quan đến giá trị NULL sẽ cho ra kết quả là NULL - x có giá trị là NULL - x + 3 cho ra kết quả là NULL - x + 3 là một biểu thức không hợp lệ trong SQL ƒ Những biểu thức so sánh có liên quan đến giá trị NULL sẽ cho ra kết quả là UNKNOWN - x = 3 cho ra kết quả là UNKNOWN - x = 3 là một so sánh không hợp lệ trong SQL Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 43 Mệnh đề WHERE (tt) NULL SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NULL SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NOT NULL Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 44 22 Mệnh đề FROM Không sử dụng mệnh đề WHERE SELECT MANV, MAPHG FROM NHANVIEN, PHONGBAN WHERE TRUE MANV MAPHG 333445555 1 333445555 4 333445555 5 987987987 1 987987987 4 987987987 5 Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 45 Mệnh đề FROM (tt) Tên bí danh (Alias) SELECT TENPHG, DIADIEM FROM PHONGBANPHONGBAN, ASDDIEM_PHGPB, DDIEM_PHG AS DD WHERE PB.MAPHG=DD.MAPHGMAPHG=MAPHG SELECT TENNV, NV.NGSINH,NGSINH, TENTN, TENTN, NGSINH TN.NGSINH FROM NHANVIENNHANVIEN, NV,THANNHAN THANNHAN TN WHERE MANV=MA_NVIEN Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 46 23 Mệnh đề ORDER BY ƒ Dùng để hiển thị kết quả câu truy vấn theo một thứ tự nào đó ƒ Cú pháp SELECT FROM WHERE ORDER BY - ASC: tăng (mặc định) - DESC: giảm Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 47 Mệnh đề ORDER BY (tt) ƒ Ví dụ SELECT MA_NVIEN, SODA FROM PHANCONG ORDER BY MA_NVIEN DESC, SODA MA_NVIEN SODA 999887777 10 999887777 30 987987987 10 987987987 30 987654321 10 987654321 20 987654321 30 Cơ sở dữ liệu - Khoa ĐT-TH - CĐ KTCN-ĐN 48 24