Ngôn ngữ SQL

1NGÔN NGỮ SQL 1. Ngôn ngữ mô tả dữ liệu 2. Ngôn ngữ thao tác dữ liệu 3. Ngôn ngữ truy vấn dữ liệu 2CƠ SỞ DỮ LIỆU VÍ DỤ Cho CSDL gồm các Bảng sau: SINHVIEN (MSSV, HOLOT, TEN, PHAI, NGAYSINH, QUEQUAN, DIACHI, MALOP) DSLOP (MALOP, TENLOP, SISO, MAKHOA) DSKHOA (MAKHOA, TENKHOA) MONHOC (MAMH, TENMH, SOTC) KETQUA (MSSV, MAMH, DIEM) 3CƠ SỞ DỮ LIỆU VÍ DỤ MSSV HOLOT TEN PHAI NGAY SINH QUEQUAN DIACHI MALO P T001 LE VAN TU NAM 13/5/81 TPHCM 12 LELOI-Q1 – TPHCM T1N D002 TRAN THI DIEM NU 3/12/82 HANOI 3/1A BAHAT - Q10 D1N H003 NGUYEN VU LINH NAM 1/6/80 DONGTHAP 25 VOTHISAU - Q3 H1N K004 CAO VAN HUNG NAM 31/7/81 VINHLONG 33/5 HONG HA – TB K1N T005 LE TIEN DUNG NAM 11/3/84 DONGNAI 77/8 LE LOI - Q1 T2N T006 DAO VAN DUNG NAM 6/6/79 HUE 38/4 TAM DAO – Q10 T1N H007 TRAN MINH HANH NU 4/8/81 HANOI 9 BINHGIA – TB H1N D008 DINH THI TUYET NU 23/4/82 TPHCM 22 CC MIEUNOI D2N D009 LY HAI TRIEU NAM 18/2/81 CANTHO F7 NGUYEN OANH D1N K010 HA NGUYEN XUAN NU 16/3/80 ANGIANG 365 CAO THANG K1N SINHVIEN 4CƠ SỞ DỮ LIỆU VÍ DỤ MALOP TENLOP SISO MAKHOA T1N TIN HOC 1 75 TH T2N TIN HOC 2 68 TH D1N DIEN TU 1 57 DD D2N DIEN TU 2 61 DD H1N HOA HOC 1 123 HH K1N KINH TE 1 84 KT DSLOP MAKHOA TENKHOA TH CONG NGHE THONG TIN DD DIEN – DIEN TU HH CONG NGHE HOA KT KINH TE DSKHOA 5CƠ SỞ DỮ LIỆU VÍ DỤ MAMH TENMH SOTC THCB TIN HOC CAN BAN 4 THVP TIN HOC VAN PHONG 3 CSDL CO SO DU LIEU 4 LTCB LAP TRINH CAN BAN 5 CTDL CAU TRUC DU LIEU 4 MANG MANG MAY TINH 3 MONHOC MSSV MAMH DIEM T001 THVP 6 T001 CSDL 7 D002 THCB 8 D002 THVP 5 D002 LTCB 3 H003 LTCB 9 H003 CTDL 6 K004 THCB 5 K004 THVP 7 K004 CSDL 9 T005 THCB 1 T005 MANG 5 KETQUA 61. Ngôn ngữ mô tả dữ liệu 1. Lệnh Tạo Bảng 2. Lệnh Xóa Bảng 3. Lệnh Thêm Cột 4. Lệnh Xóa Cột 5. Lệnh Sửa Cột 6. Lệnh Tạo Khóa chính 7. Lệnh Tạo Khóa ngoại 8. Lệnh Tạo ràng buộc về miền giá trị 9. Lệnh Tạo ràng buộc duy nhất 10. Lệnh Tạo chỉ mục 11. Lệnh Xóa chỉ mục 71. Ngôn ngữ mô tả dữ liệu 1.Lệnh Tạo Bảng CREATE TABLE ( [NOT NULL], [NOT NULL], [NOT NULL]); NOT NULL : Cột không được phép rỗng 81. Ngôn ngữ mô tả dữ liệu 1.Lệnh Tạo Bảng – Ví dụ CREATE TABLE SINHVIEN ( MSSV char(4) not null, HOLOT varchar(20) not null, TEN varchar(7) not null, PHAI bit, NGAYSINH datetime, QUEQUAN varchar(20), DIACHI varchar(50), MALOP char(3) not null); 91. Ngôn ngữ mô tả dữ liệu 2.Lệnh Xóa Bảng DROP TABLE ; Ví dụ: DROP TABLE SINHVIEN; 10 1. Ngôn ngữ mô tả dữ liệu 3.Lệnh Thêm Cột ALTER TABLE ADD [NOT NULL]; Ví dụ: ALTER TABLE SINHVIEN ADD DOANVIEN bit; 11 1. Ngôn ngữ mô tả dữ liệu 4.Lệnh Xóa Cột ALTER TABLE DROP COLUMN ; Ví dụ: ALTER TABLE SINHVIEN DROP COLUMN DOANVIEN; 12 1. Ngôn ngữ mô tả dữ liệu 5.Lệnh Sửa Cột ALTER TABLE ALTER COLUMN ; Ví dụ: ALTER TABLE SINHVIEN ALTER COLUMN Ngaysinh SmallDatetime; 13 1. Ngôn ngữ mô tả dữ liệu 6.Lệnh Tạo Khóa chính ALTER TABLE ADD CONSTRAINT PRIMARY KEY (DS tên cột); Lưu ý: -Tên ràng buộc là duy nhất -Các cột trong DS tên cột phải có thuộc tính NOT NULL 14 1. Ngôn ngữ mô tả dữ liệu 6.Lệnh Tạo Khóa chính - Ví dụ: ALTER TABLE SINHVIEN ADD CONSTRAINT Kc_Sv PRIMARY KEY (mssv); ALTER TABLE KETQUA ADD CONSTRAINT Kc_Kq PRIMARY KEY (mssv,mamh); 15 1. Ngôn ngữ mô tả dữ liệu 7.Lệnh Tạo Khóa ngoại ALTER TABLE ADD CONSTRAINT FOREIGN KEY (DS tên cột) REFERENCES (DS tên cột); Lưu ý: -Tên ràng buộc là duy nhất 16 1. Ngôn ngữ mô tả dữ liệu 7.Lệnh Tạo Khóa ngoại - Ví dụ: ALTER TABLE SINHVIEN ADD CONSTRAINT Kn_Sv FOREIGN KEY(malop) REFERENCES DSLOP (malop); ALTER TABLE KETQUA ADD CONSTRAINT Kn_Kq1 FOREIGN KEY (mssv) REFERENCES SINHVIEN (mssv); 17 1. Ngôn ngữ mô tả dữ liệu 8.Lệnh Tạo ràng buộc về miền giá trị ALTER TABLE ADD CONSTRAINT CHECK (Điều kiện); Lưu ý: -Tên ràng buộc là duy nhất 18 1. Ngôn ngữ mô tả dữ liệu 8.Lệnh Tạo ràng buộc miền giá trị - Ví dụ: ALTER TABLE KETQUA ADD CONSTRAINT Kt_Kq CHECK (Diem between 0 AND 10); ALTER TABLE DSLOP ADD CONSTRAINT Kt_Dslop CHECK (Siso >= 50); 19 1. Ngôn ngữ mô tả dữ liệu 9.Lệnh Tạo ràng buộc duy nhất ALTER TABLE ADD CONSTRAINT UNIQUE (DS Tên cột); Lưu ý: -Tên ràng buộc là duy nhất 20 1. Ngôn ngữ mô tả dữ liệu 9.Lệnh Tạo ràng buộc duy nhất – Ví dụ Tên môn học không được trùng nhau: ALTER TABLE MONHOC ADD CONSTRAINT Dn_Mh UNIQUE (TenMH); 21 1. Ngôn ngữ mô tả dữ liệu 10.Lệnh Tạo chỉ mục CREATE INDEX ON (, , ...) ; Ngữ nghĩa: Tạo chỉ mục để lưu thứ tự sắp xếp các bản ghi theo giá trị tăng dần của các cột được cho trong danh sách. Ghi chú: Chỉ nên tạo chỉ mục cho các bảng có nhiều bản ghi và ít được cập nhật, và chỉ nên tạo trên các cột mà thông tin của nó thường xuyên được tham khảo tới. 22 1. Ngôn ngữ mô tả dữ liệu 10.Lệnh Tạo chỉ mục – Ví dụ: CREATE INDEX Cm_sv1 ON SINHVIEN (Ten); CREATE INDEX Cm_mh1 ON MONHOC (TenMH); 23 1. Ngôn ngữ mô tả dữ liệu 11.Lệnh Xóa chỉ mục DROP INDEX ; Ví dụ: DROP INDEX Cm_sv1 DROP INDEX Cm_mh1 24 2. Ngôn ngữ thao tác dữ liệu 1. Lệnh Thêm mẫu tin 2. Lệnh Xóa mẫu tin 3. Lệnh cập nhật mẫu tin 25 2. Ngôn ngữ thao tác dữ liệu 1.Lệnh thêm mẫu tin (1) INSERT INTO (, ...) VALUES (, , ...); Lưu ý: -Kiểu dữ liệu -Chiều dài -Các ràng buộc toàn vẹn: khóa chính, khóa ngoại, kiểm tra, duy nhất 26 2. Ngôn ngữ thao tác dữ liệu 1.Lệnh thêm mẫu tin (1) – Ví dụ Thêm 1 Sinh viên mới: INSERT INTO SINHVIEN (MSSV, HOLOT, TEN, PHAI, NGAYSINH, QUEQUAN, DIACHI, MALOP) VALUES (‘T005’,’LE TIEN’,’DUNG’,1,’3/18/84’, ’DONGNAI’,’77/8 LE LOI – Q1’,’T2N’); Thêm 1 Môn học mới: INSERT INTO MONHOC (MAMH, TENMH, SOTC) VALUES (‘CSDL’,’CO SO DU LIEU’,4); 27 2. Ngôn ngữ thao tác dữ liệu 1.Lệnh thêm mẫu tin (2) Nếu các biểu thức sau từ khoá VALUES hoàn toàn phù hợp về thứ tự với các cột trong bảng  danh sách các cột sau từ khóa INTO có thể được bỏ qua INSERT INTO VALUES (, , ...); 28 2. Ngôn ngữ thao tác dữ liệu 1.Lệnh thêm mẫu tin (2) – Ví dụ Thêm 1 Sinh viên mới: INSERT INTO SINHVIEN VALUES (‘T005’,’LE TIEN’,’DUNG’,1,’3/18/84’, ’DONGNAI’,’77/8 LE LOI – Q1’,’T2N’); Thêm 1 Môn học mới: INSERT INTO MONHOC VALUES (‘CSDL’,’CO SO DU LIEU’,4); 29 2. Ngôn ngữ thao tác dữ liệu 1.Lệnh thêm mẫu tin (3) Ngoài ra ta có thể thêm (các) mẫu tin vào bảng từ câu lệnh truy vấn (Select) INSERT INTO (, ...) hoặc INSERT INTO 30 2. Ngôn ngữ thao tác dữ liệu 1.Lệnh thêm mẫu tin (3) – Ví dụ Thêm Sinh viên mới từ 1 danh sách: INSERT INTO SINHVIEN SELECT * FROM DSSV_NEW ; Lưu ý: -DSSV_New có cùng cấu trúc với SINHVIEN 31 2. Ngôn ngữ thao tác dữ liệu 2.Lệnh Xóa mẫu tin  Việc loại bỏ mẫu tin khỏi 1 bảng trong CSDL là 1 trong những thao tác cập nhật dữ liệu được tiến hành một cách thường xuyên  Cú pháp: DELETE FROM [WHERE ];  Ngữ nghĩa: Các mẫu tin thoả mãn điều kiện sau WHERE sẽ bị xoá khỏi bảng. Nếu không có mệnh đề WHERE thì tất cả các mẫu tin của bảng sẽ bị xóa khỏi bảng. 32 2. Ngôn ngữ thao tác dữ liệu 2.Lệnh Xóa mẫu tin – Ví dụ: Xóa Sinh viên có Mã số là T005 DELETE FROM SINHVIEN WHERE MSSV=‘T005’; Xóa tất cả các môn học có 5 tín chỉ DELETE FROM MONHOC WHERE SOTC = 5; 33 2. Ngôn ngữ thao tác dữ liệu 3.Lệnh cập nhật mẫu tin Cú pháp UPDATE SET = , = , ... = [WHERE ]; 34 2. Ngôn ngữ thao tác dữ liệu 3.Lệnh cập nhật mẫu tin Ngữ nghĩa: Giá trị của các field có tên trong danh sách , ... của những mẫu tin thoả mãn điều kiện sau WHERE sẽ được sửa đổi thành giá trị của các , ... tương ứng. Nếu không có mệnh đề WHERE thì tất cả các mẫu tin của bảng sẽ được sửa đổi. 35 2. Ngôn ngữ thao tác dữ liệu 3.Lệnh cập nhật mẫu tin Ví dụ: Tăng cho tất cả sinh viên 1 điểm môn THCB UPDATE KETQUA SET DIEM = DIEM +1 WHERE MAMH = ‘THCB’; Cập nhật sĩ số lớp T2N thành 90 UPDATE DSLOP SET SISO = 90 WHERE MALOP = ‘T2N’; 36 3. Ngôn ngữ truy vấn dữ liệu 1. Cú pháp tổng quát 2. Dạng đơn giản 3. Dấu * 4. Mệnh đề WHERE 5. Mệnh đề ORDER BY 6. Mệnh đề GROUP BY 7. Mệnh đề HAVING 8. Phát biểu Select với AS 9. Phát biểu Select với TOP N 10. Phát biểu Select với DISTINCT 11. Truy vấn từ nhiều Bảng 12. Truy vấn con 37 3. Ngôn ngữ truy vấn dữ liệu 1.Cú pháp tổng quát SELECT FROM WHERE GROUP BY HAVING ORDER BY 38 3. Ngôn ngữ truy vấn dữ liệu 2.Dạng đơn giản SELECT FROM  Trích ra một số cột trong 1 Bảng nào đó Ví dụ: Lấy ra MAMH, TENMH từ bảng MONHOC SELECT MAMH, TENMH FROM MONHOC; 39 3. Ngôn ngữ truy vấn dữ liệu 3.Dấu * Dấu * đại diện cho tất cả các cột SELECT * FROM Ví dụ: Lấy ra danh sách các môn học SELECT * FROM MONHOC; 40 3. Ngôn ngữ truy vấn dữ liệu 4.Mệnh đề WHERE Dùng để đặt điều kiện trích dữ liệu SELECT FROM WHERE Ví dụ: Lấy ra MSSV, HOTEN của Sinh viên quê quán ‘HA NOI’ SELECT MSSV, HOLOT, TEN FROM SINHVIEN WHERE QUEQUAN = ‘HA NOI’; 41 3. Ngôn ngữ truy vấn dữ liệu 4.Mệnh đề WHERE Các phép toán trong mệnh đề WHERE -So sánh: >, =, -Logic: And, Or, Not Ví dụ: Lấy ra MSSV, HOTEN của Sinh viên lớp T2N quê quán ‘HA NOI’ SELECT MSSV, HOLOT, TEN FROM SINHVIEN WHERE QUEQUAN = ‘HA NOI’ AND MALOP= ‘T2N’; 42 3. Ngôn ngữ truy vấn dữ liệu 4.Mệnh đề WHERE Toán tử Between: nằm trong 1 miền Ví dụ: Trích ra danh sách Sinh viên sinh năm 1982 SELECT * FROM SINHVIEN WHERE NGAYSINH Between ‘1/1/1982’ And ’12/31/1982’; 43 3. Ngôn ngữ truy vấn dữ liệu 4.Mệnh đề WHERE Toán tử LIKE: phép toán so sánh gần giống, sử dụng dấu các ký tự đại diện như _ (1 ký tự), % (1 chuỗi bất kỳ) Ví dụ: Trích ra danh sách Sinh viên Họ ‘Phan’ SELECT * FROM SINHVIEN WHERE HOLOT LIKE ‘Phan%’; 44 3. Ngôn ngữ truy vấn dữ liệu 4.Mệnh đề WHERE Toán tử IN: phép toán so sánh trong 1 tập hợp, 1 danh sách Ví dụ: Trích ra danh sách Sinh viên quê quán ở các tỉnh ‘Cần Thơ’, ‘An Giang’, ‘Kiên Giang’ SELECT * FROM SINHVIEN WHERE QUEQUAN IN (‘Cần Thơ’, ‘An Giang’, ‘Kiên Giang’); 45 3. Ngôn ngữ truy vấn dữ liệu 4.Mệnh đề WHERE IS NULL ( IS NOT NULL ): kiểm tra 1 giá trị có rỗng hay không (kết quả: TRUE – FALSE) Ví dụ: Trích ra danh sách Sinh viên chưa có địa chỉ SELECT * FROM SINHVIEN WHERE DIACHI IS NULL; 46 3. Ngôn ngữ truy vấn dữ liệu 4.Mệnh đề WHERE Toán tử Exists: trả về TRUE nếu có ít nhất 1 mẫu tin tồn tại Ví dụ: Cho biết có sinh viên nào Quê quán ‘Nha Trang’ không? If Exists (SELECT * FROM SINHVIEN WHERE QUEQUAN = ‘Nha Trang’) Print ‘Co sinh vien’; 47 3. Ngôn ngữ truy vấn dữ liệu 5.Mệnh đề ORDER BY Sắp xếp kết quả theo thứ tự mong muốn ORDER BY [ASC | DESC] Ví dụ: Trích ra danh sách sinh viên nữ, sắp theo tên tăng dần? SELECT MSSV, HOLOT, TEN, MALOP FROM SINHVIEN WHERE PHAI= 0 ORDER BY TEN ASC 48 3. Ngôn ngữ truy vấn dữ liệu 6.Mệnh đề GROUP BY Nhóm dữ liệu lại theo từng nhóm để thực hiện các phép toán thống kê GROUP BY Ví dụ: Cho biết số lượng sinh viên từng lớp SELECT MALOP, COUNT(*) AS SOSV FROM SINHVIEN GROUP BY MALOP 49 3. Ngôn ngữ truy vấn dữ liệu 6.Mệnh đề GROUP BY Một số Hàm thông dụng: -AVG: giá trị trung bình -MIN: giá trị nhỏ nhất -MAX: giá trị lớn nhất -COUNT: đếm số phần tử -SUM : Tổng các phần tử 50 3. Ngôn ngữ truy vấn dữ liệu 7.Mệnh đề HAVING Đặt điều kiện chọn sau khi đã nhóm dữ liệu bằng mệnh đề GROUP BY Ví dụ: Trích ra Danh sách các lớp có trên 20 sinh viên SELECT MALOP, COUNT(*) AS SOSV FROM SINHVIEN GROUP BY MALOP HAVING COUNT(*) > 20 ; 51 3. Ngôn ngữ truy vấn dữ liệu 8.Phát biểu Select với AS Đặt lại tên Field khi hiển thị kết quả Ví dụ: Trích ra Danh sách các lớp có trên 20 sinh viên SELECT MALOP AS ML, COUNT(*) AS SOSV FROM SINHVIEN GROUP BY MALOP HAVING COUNT(*) > 20 ; 52 3. Ngôn ngữ truy vấn dữ liệu 9.Phát biểu Select với TOP N Cho phép lấy ra chỉ một số mẫu tin nào đó theo 1 tiêu chuẩn nào đó. Ví dụ: Trích ra Danh sách 10 sinh viên đầu tiên. SELECT TOP 10 * FROM SINHVIEN; 53 3. Ngôn ngữ truy vấn dữ liệu 10.Phát biểu Select với DISTINCT Nếu kết quả truy vấn có nhiều mẫu tin trùng nhau, để chỉ lấy 1 mẫu tin ta dùng DISTINCT Ví dụ: Trong Danh sách Sinh viên, cho biết có những lớp nào SELECT DISTINCT MALOP FROM SINHVIEN; 54 3. Ngôn ngữ truy vấn dữ liệu 11.Truy vấn từ nhiều bảng Khi thông tin cần lấy ra có từ nhiều bảng khác nhau, cần thực hiện truy vấn từ nhiều bảng SELECT FROM WHERE 55 3. Ngôn ngữ truy vấn dữ liệu 11.Truy vấn từ nhiều bảng Lưu ý:  Cần kết các bảng lại với nhau. Với n bảng cần có n-1 điều kiện kết.  Các tên cột cùng có ở nhiều bảng cần ghi rõ theo dạng [Tên Bảng].[Tên cột] 56 3. Ngôn ngữ truy vấn dữ liệu 11.Truy vấn từ nhiều bảng Ví dụ: Trích ra MSSV, HOTEN, MALOP,TENLOP của những sinh viên tên ‘NAM’ SELECT MSSV, HOLOT, TEN, DSLOP.MALOP, TENLOP FROM SINHVIEN, DSLOP WHERE SINHVIEN.MALOP = DSLOP.MALOP AND TEN=‘NAM’; 57 3. Ngôn ngữ truy vấn dữ liệu 11.Truy vấn từ nhiều bảng -Có thể sử dụng tên tắt của các Bảng Ví dụ: Trích ra MSSV, HOTEN, MALOP, TENLOP của những sinh viên khoa CNTT SELECT MSSV, HOLOT, TEN, L.MALOP, TENLOP FROM SINHVIEN S, DSLOP L, DSKHOA K WHERE S.MALOP = L.MALOP AND L.MAKHOA = K.MAKHOA AND TENKHOA=‘Cong nghe thong tin’; 58 3. Ngôn ngữ truy vấn dữ liệu 12.Truy vấn con -Đôi khi ta cần sử dụng kết quả của 1 câu truy vấn để làm điều kiện cho 1 câu truy vấn khác, khi đó ta gọi là truy vấn con. -Khi thực hiện, truy vấn con sẽ được thực hiện trước, rồi lấy kết quả để thực hiện truy vấn lớn 59 3. Ngôn ngữ truy vấn dữ liệu 12.Truy vấn con Dạng tổng quát: SELECT FROM WHERE IN ( NOT IN, =, , ) ( SELECT FROM WHERE ) 60 3. Ngôn ngữ truy vấn dữ liệu 12.Truy vấn con Ví dụ: Trích ra DS những SV có điểm môn THVP cao nhất SELECT S.MSSV,HOLOT,TEN,MAMH,DIEM FROM SINHVIEN S, KETQUA K WHERE S.MSSV = K.MSSV AND MAMH=‘THVP’ AND DIEM = ( SELECT MAX(DIEM) FROM KETQUA WHERE MAMH=‘THVP’) 61  From --> Where --> Group By --> Having --> Select --> Order