Bài giảng Cơ sở dữ liệu (Database) - Chương 3: Ngôn ngữ định nghĩa và thao tác dữ liệu - Lê Thị Lan

CHƯƠNG 3 NGÔN NGỮ ĐỊNH NGHĨA VÀ THAO TÁC DỮ LIỆU 1. Ngôn ngữ định nghĩa dữ liệu  Cho phép định nghĩa:  Sơ đồ đối với mỗi bảng  Kiểu dữ liệu hay miền giá trị  Các ràng buộc toàn vẹn  Tập các chỉ dẫn  Thông tin an toàn và ủy quyền đối với từng bảng  Cấu trúc lưu trữ vật lý Thi-Lan Le, MICA HUST 52 1. Ngôn ngữ định nghĩa dữ liệu  Kiểu dữ liệu:  Dữ liệu xâu ký tự: – Char(n) – Varchar (n) : Độ dài thay đổi  Dữ liệu số: – Int or Integer (từ -2,147,483,648 đến 2,147,483,647) – Smallint (từ -32,768 đến 32,767) – Numeric (p,s) – Real, double precision – Float (n)  Dữ liệu ngày tháng – Date: YYYY-MM-DD – Time: HH:MM.SS.MMMM Thi-Lan Le, MICA HUST 53 1. Ngôn ngữ định nghĩa dữ liệu  Tạo bảng trong CSDL: CREATE TABLE ( [NOT NULL], [CONSTRAINT <kiểu ràng buộc> ]) Thi-Lan Le, MICA HUST 54 1. Ngôn ngữ định nghĩa dữ liệu  Tạo bảng – Xác định khóa chính Thi-Lan Le, MICA HUST 55 1. Ngôn ngữ định nghĩa dữ liệu  Thêm - Xóa cột trong bảng Thi-Lan Le, MICA HUST 56 1. Ngôn ngữ định nghĩa dữ liệu Xóa bảng:  Cú pháp: DROP TABLE relation_name  Ví dụ: DROP TABLE branch Thi-Lan Le, MICA HUST 57 2. Ngôn ngữ thao tác dữ liệu  Một số từ khóa General SELECT, ALL / DISTINCT, *, AS, FROM, WHERE Structure Comparison IN, BETWEEN, LIKE "% _" Grouping GROUP BY, HAVING, COUNT( ), SUM( ), AVG( ), MAX( ), MIN( ) Display Order ORDER BY, ASC / DESC Logical AND, OR, NOT Operators Output INTO TABLE / CURSOR TO FILE [ADDITIVE], TO PRINTER, TO SCREEN Union UNION Thi-Lan Le, MICA HUST 58 2. Ngôn ngữ thao tác dữ liệu  Ví dụ: Bảng student lưu các thông tin về sinh viên Thuộc tính Kiểu dữ liệu Độ rộng Giải thích id numeric 4 Số hiệu sinh viên name character 10 Tên sinh viên dob date 8 Ngày tháng năm sinh sex character 1 Giới tính (M / F) class character 2 Tên lớp hcode character 1 Mã nhà (R, Y, B, G) dcode character 3 Mã quận remission logical 1 Quyền nhập học (T, F) mtest numeric 2 Điểm thi toán Thi-Lan Le, MICA HUST 59 2. Ngôn ngữ thao tác dữ liệu  Dữ liệu trong bảng student id name dob sex class mtest hcode dcode remission 9801 Peter 06/04/86 M 1A 70 R SSP .F. 9802 Mary 01/10/86 F 1A 92 Y HHM .F. 9803 Johnny 03/16/86 M 1A 91 G SSP .T. 9804 Wendy 07/09/86 F 1B 84 B YMT .F. 9805 Tobe 10/17/86 M 1B 88 R YMT .F. : : : : : : : : : Thi-Lan Le, MICA HUST 60 2. Ngôn ngữ thao tác dữ liệu  Thêm một bộ dữ liệu (~ 1 dòng) vào 1 bảng trong CSDL  Cách 1: INSERT INTO Table_name VALUES (value 1, value 2, , value n)  Cách 2: INSERT INTO Table_name (column 1, column 2, , column n) VALUES (value 1, value 2, , value n)  Câu hỏi: Đánh giá đặc điểm cũng như ưu, nhược điểm của từng cách Thi-Lan Le, MICA HUST 61 2. Ngôn ngữ thao tác dữ liệu SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2] ; FROM tablename WHERE condition  Truy vấn lựa chọn các dòng trên các bảng có tên tablename và trả kết quả dưới dạng bảng  Biểu thức expr1, expr2 có thể : • một cột hoặc • một biểu thức gồm các hàm và các trường  col1, col2 là tên các cột trong bảng kết quả  DISTINCT: bỏ hết các bộ lặp trong kết quả trong khi từ khóa ALL thì dữ lại toàn bộ các bộ lặp  condition có thể là : • Toán tử so sánh >, <, = hoặc • Toán tử so sánh xâu Thi-Lan Le,• MICAToán HUSTtừ logic AND, OR, NOT 62 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 1: Liệt kê tất cả sinh viên  Câu truy vấn: SELECT * FROM student id name dob sex class mtest hcode dcode remission Kết quả 9801 Peter 06/04/86 M 1A 70 R SSP .F. 9802 Mary 01/10/86 F 1A 92 Y HHM .F. 9803 Johnny 03/16/86 M 1A 91 G SSP .T. 9804 Wendy 07/09/86 F 1B 84 B YMT .F. 9805 Tobe 10/17/86 M 1B 88 R YMT .F. : : : : : : : : : Thi-Lan Le, MICA HUST 63 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 2: Liệt kê tên , mã nhà và lớp của sinh viên lớp 1A  Câu truy vấn: SELECT name, hcode, class FROM student WHERE class=‘1A’ name hcode class Peter R 1A Kết quả Mary Y 1A Johnny G 1A Luke G 1A Bobby B 1A Aaron R 1A : : : Thi-Lan Le, MICA HUST 64 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 3: Liệt kê mã quận của sinh viên có mã nhà là R (Red house)  Câu truy vấn: SELECT DISTINCT dcode FROM student WHERE hcode=‘R’ Nhận xét: Tại sao cần từ khóa DISTINCT dcode Kết quả HHM KWC MKK SSP TST YMT Thi-Lan Le, MICA HUST 65 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 4: Liệt kê tên của các nữ sinh lớp 1B  Câu truy vấn: SELECT name FROM student WHERE class=‘1B’ AND sex=‘F’ name Kết quả Janet Sandy Mimi Bài tập: 1) Liệt kê tên nữ sinh của lớp 1A và 1B 2) Liệt kê tên nữ sinh của lớp 1 A và nam sinh lớp 1 B Thi-Lan Le, MICA HUST 66 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 5: Liệt kê tên, id của sinh viên không được miễn phí đăng ký của lớp 1A  Câu truy vấn: SELECT name, id, class FROM student WHERE class=‘1A’ AND NOT remission name id class Peter 9801 1A Mary 9802 1A Kết quả Luke 9810 1A Bobby 9811 1A Aaron 9812 1A Ron 9813 1A Gigi 9824 1A : : : Thi-Lan Le, MICA HUST 67 2. Ngôn ngữ thao tác dữ liệu  Các tác tử so sánh:  expr IN ( value1, value2, value3)  expr BETWEEN value1 AND value2  expr LIKE "%_" Thi-Lan Le, MICA HUST 68 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 6: Liệt kê các sinh viên sinh vào thứ tư hoặc thứ bảy  Câu truy vấn: SELECT name, class, CDOW(dob) AS bdate FROM student WHERE DOW(dob) IN (4,7)  Trong đó:  CDOW (): trả về tên ngày trong tuần của 1 ngày  DOW (): trả về thứ của 1 ngày trong tuần dưới dạng số (Day of Week) name class bdate Peter 1A Wednesday Kết quả Wendy 1B Wednesday Kevin 1C Saturday Luke 1A Wednesday Aaron 1A Saturday Thi-Lan Le, MICA HUST : : : 69 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 7: Liệt kê các sinh viên không sinh vào tháng 1, 2, 5, 6 và 9  Câu truy vấn: SELECT name, class, dob FROM student WHERE MONTH(dob) NOT IN (1,3,6,9) name class dob Trong đó: Wendy 1B 07/09/86 MONTH (): trả về tháng Tobe 1B 10/17/86 Kết quả Eric 1C 05/05/87 Patty 1C 08/13/87 Bài tập: Kevin 1C 11/21/87 Bobby 1A 02/16/86 Viết lại câu truy vấn Aaron 1A 08/02/86 trên sử dụng toán tử IN : : : Thi-Lan Le, MICA HUST 70 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 8: Liệt kê sinh viên lớp 1A có điểm kiểm tra toán từ 80 đến 90  Câu truy vấn: SELECT name, mtest FROM student WHERE class=‘1A’ AND mtest BETWEEN 80 AND 90 name mtest Kết quả Luke 86 Aaron 83 Gigi 84 Thi-Lan Le, MICA HUST 71 2. Ngôn ngữ thao tác dữ liệu SELECT ...... FROM ...... WHERE condition ; GROUP BY groupexpr [HAVING requirement] Các hàm của nhóm: COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )  Nhóm dữ liệu:  groupexpr xác định các dòng liên quan sẽ được nhóm lại  WHERE condition xác định điều kiện cho từng dòng trước khi nhóm  HAVING requirement xác định điều kiện tương đương đến toàn bộ nhóm Thi-Lan Le, MICA HUST 72 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 9: Xác định tổng số sinh viên của mỗi lớp  Câu truy vấn: SELECT class, COUNT(*) as NumberofStudent FROM student GROUP BY class class NumberofStudent 1A 10 Kết quả 1B 9 1C 9 2A 8 2B 8 2C 6 Thi-Lan Le, MICA HUST 73 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 10: Liệt kê điểm toán trung bình của từng lớp  Câu truy vấn: SELECT class, AVG(mtest) FROM student GROUP BY class class avg_mtest 1A 85.90 1B 70.33 Kết quả 1C 37.89 2A 89.38 2B 53.13 2C 32.67 Thi-Lan Le, MICA HUST 74 2. Ngôn ngữ thao tác dữ liệu  Bài tập:  Tính điểm trung bình của nữ sinh của tất cả các lớp  Tính điểm trung bình của nữ sinh của từng lớp Thi-Lan Le, MICA HUST 75 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 11: Xác định số sinh viên nữ của từng quận  Câu truy vấn: SELECT dcode, COUNT(*) FROM student WHERE sex=‘F’ GROUP BY dcode dcode cnt Kết quả HHM 6 KWC 1 MKK 1 SSP 5 TST 4 YMT 8 Thi-Lan Le, MICA HUST 76 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 12: Xác định điểm kiểm tra cao nhất và thấp nhất của sinh viên ở từng quận  Câu truy vấn: SELECT MAX(mtest), MIN(mtest), dcode FROM student GROUP BY dcode max_mtest min_mtest dcode 92 36 HHM Kết quả 91 19 MKK 91 31 SSP 92 36 TST 75 75 TSW 88 38 YMT Thi-Lan Le, MICA HUST 77 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 13: Xác định điểm toán trung bình của nam sinh viên trong mỗi lớp với điều kiện lớp đó có ít nhất 3 nam sinh viên  Câu truy vấn: SELECT AVG(mtest), class FROM student WHERE sex=‘M’ GROUP BY class HAVING COUNT(*) >= 3 avg_mtest class Kết quả 86.00 1A 77.75 1B 35.60 1C 86.50 2A 56.50 2B Thi-Lan Le, MICA HUST 78  Đưa ra tên lớp, điểm toán trung bình của nam sinh viên trong mỗi lớp, số sinh viên nam của lớp với điều kiện lớp đó có ít nhất 3 nam sinh viên Thi-Lan Le, MICA HUST 79 2. Ngôn ngữ thao tác dữ liệu SELECT ...... FROM ...... WHERE ...... ORDER BY colname [ASC / DESC]  Sắp xếp kết quả trả về:  ASC: chiều tăng dần  DESC: chiều giảm dần  Mặc định: là ASC Thi-Lan Le, MICA HUST 80 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 14: Liệt kê nam sinh viên của lớp 1 A theo tên  Câu truy vấn: SELECT name, id FROM student WHERE sex=‘M’ AND class=‘1A’ ORDER BY name name id Kết quả name id Peter 9801 Aaron 9812 Johnny 9803 ORDER BY Bobby 9811 Luke 9810 Johnny 9803 name Bobby 9811 Luke 9810 Aaron 9812 Peter 9801 Ron 9813 Ron 9813 Thi-Lan Le, MICA HUST 81 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 15: Liệt kê sinh viên lớp 2A theo mã quận  Câu truy vấn: SELECT name, id, class, dcode FROM student WHERE class=‘2A’ ORDER BY dcode name id class dcode Jimmy 9712 2A HHM Kết quả Tim 9713 2A HHM Samual 9714 2A SHT Rosa 9703 2A SSP Helen 9702 2A TST Joseph 9715 2A TSW Paula 9701 2A YMT Susan 9704 2A YMT Thi-Lan Le, MICA HUST 82 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 16: Liệt kê số lượng sinh viên của mỗi quận theo chiều giảm dần  Câu truy vấn: SELECT COUNT(*) AS cnt, dcode FROM student GROUP BY dcode ORDER BY cnt DESC cnt docode 11 YMT Kết quả 10 HHM 10 SSP 9 MKK 5 TST 2 TSW 1 KWC 1 MMK 1 SHT Thi-Lan Le, MICA HUST 83  Yêu cầu: Liệt kê theo thứ tự giảm dần về điểm của sinh viên nữ có điểm thi lớn hơn điểm trung bình của các sinh viên nữ Thi-Lan Le, MICA HUST 84 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 17: Liệt kê nam sinh viên trong một nhà theo trật tự mã quận và tên lớp  Câu truy vấn: SELECT name, class, hcode FROM student WHERE sex=‘M’ ORDER BY hcode, class Thi-Lan Le, MICA HUST 85 2. Ngôn ngữ thao tác dữ liệu name hcode class Bobby B 1A Blue Trật tự House Teddy B 1B theo Joseph B 2A class Trậ tự Zion B 2B theo Leslie B 2C hcode Johnny G 1A Luke G 1A Kevin G 1C Green House George G 1C : : : Thi-Lan Le, MICA HUST 86 2. Ngôn ngữ thao tác dữ liệu  Hợp, giao và hiệu của các bảng Hợp của A và B (AB) A B Một bảng bao gồm tất cả các dòng của A và B. Thi-Lan Le, MICA HUST 87 2. Ngôn ngữ thao tác dữ liệu  Hợp, giao và hiệu của các bảng Giao của A và B (AB) A B Một bảng bao gồm các dòng vừa tồn tại trong A vừa tồn tại trong B Thi-Lan Le, MICA HUST 88 2. Ngôn ngữ thao tác dữ liệu  Hợp, giao và hiệu của các bảng Hiệu của A và B A B Bảng bao gồm các dòng ở trong A nhưng không ở trong B Thi-Lan Le, MICA HUST 89 2. Ngôn ngữ thao tác dữ liệu  Bảng lưu trữ các thành viên của Bridge và Chess có cùng cấu trúc như sau: Thuộc tính Kiểu dữ liệu Độ rộng Ý nghĩa id numeric 4 Mã hiệu sv name character 10 Tên sex character 1 Giới tính (M / F) class character 2 Lớp Bridge [A] Chess [B] id name sex class id name sex class 1 9812 Aaron M 1A 1 9802 Mary F 1A 2 9801 Peter M 1A 2 9801 Peter M 1A 3 9814 Kenny M 1B 3 9815 Eddy M 1B 4 9806 Kitty F 1B 4 9814 Kenny M 1B 5 9818 Edmond M 1C 5 9817 George M 1C : : : : : : : : Thi-Lan Le, MICA HUST 90 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 17: Xác định hợp của 2 câu lạc bộ  Câu truy vấn: SELECT * FROM bridge UNION SELECT * FROM chess ORDER BY class, name SELECT ...... FROM ...... WHERE ...... ; UNION ; SELECT ...... FROM ...... WHERE ...... Thi-Lan Le, MICA HUST 91 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 18: In danh sách các sinh viên tham gia cả 2 câu lạc bộ  Câu truy vấn: SELECT * FROM bridge WHERE id IN ( SELECT id FROM chess) SELECT ...... FROM table1 ; WHERE col IN ( SELECT col FROM table2 ) Thi-Lan Le, MICA HUST 92 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 19: Xác định danh sách các sinh viện tham gia câu lạc bộ Bridge nhưng không tham gia câu lạc bộ Chess  Câu truy vấn: SELECT * FROM bridge WHERE id NOT IN (SELECT id FROM chess ) SELECT ...... FROM table1 ; WHERE col NOT IN ( SELECT col FROM table2 ) Thi-Lan Le, MICA HUST 93 2. Ngôn ngữ thao tác dữ liệu  Bảng Music lưu trữ thông tin về nhạc cụ của sinh viên Thuộc tính Kiểu dữ liệu Độ rộng Ý nghĩa id numeric 4 mã hiệu sinh viên type character 10 kiểu nhạc cụ Thi-Lan Le, MICA HUST 94 2. Ngôn ngữ thao tác dữ liệu  Kết nối:  Kết nối tự nhiên (Natural Join) hay (Inner joint): là một thao tác kết nối 2 bảng bằng các cột chung.  Kết nối ngoài (outer join):  LEFT OUTER JOIN (thường viết LEFT JOIN) lựa chọn tất cả các dòng ở trong bảng đầu tiên sau FROM ngay cả khi nó không có trong bảng thứ hai.  RIGHT OUTER JOIN Thi-Lan Le, MICA HUST 95 2. Ngôn ngữ thao tác dữ liệu  Kết nối tự nhiên:  Cú pháp 1: SELECT a.comcol, a.col1, b.col2, expr1, expr2 FROM table1 a JOIN table2 b ON a.comcol = b.comcol  Cú pháp 2: SELECT a.comcol, a.col1, b.col2, expr1, expr2 FROM table1 a, table2 b WHERE a.comcol = b.comcol Thi-Lan Le, MICA HUST 96 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 20: Xác định danh sách các sinh viên và nhạc cụ sinh viên đó học  Câu truy vấn: SELECT s.class, s.name, s.id, m.type FROM student s, music m WHERE s.id=m.id ORDER BY class, name class name id type 1A Aaron 9812 Piano Kết quả 1A Bobby 9811 Flute 1A Gigi 9824 Recorder 1A Jill 9820 Piano 1A Johnny 9803 Violin 1A Luke 9810 Piano 1A Mary 9802 Flute Thi-Lan Le, MICA HUST : : : : 97 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 20: Xác định số sinh viên học piano trong mỗi lớp  Câu truy vấn: SELECT s.class, COUNT(*) FROM student s, music m WHERE s.id=m.id AND m.type=‘Piano’ GROUP BY class ORDER BY class Join Student Điều kiện Nhóm theo lớp m.type= "Piano" class cnt Kết quả 1A 4 1B 2 Music 1C 1 Thi-Lan Le, MICA HUST 98 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 21: Xác định danh sách sinh viên chưa lựa chọn nhạc cụ  Câu truy vấn: SELECT class, name, id FROM student WHERE id NOT IN ( SELECT id FROM music ) ORDER BY class, name class name id 1A Mandy 9821 Kết quả 1B Kenny 9814 1B Tobe 9805 1C Edmond 9818 1C George 9817 : : : Thi-Lan Le, MICA HUST 99 2. Ngôn ngữ thao tác dữ liệu  Yêu cầu 22: Tạo danh sách các sinh viên với danh sách nhạc cụ sinh viên đó học. Danh sách này phải bao gồm cả sinh viên chưa đăng ký nhạc cụ  Câu truy vấn: SELECT s.class, s.name, s.id, m.type FROM student s, music m WHERE s.id=m.id UNION SELECT class, name, id, "" FROM student WHERE id NOT IN ( SELECT id FROM music ) ORDER BY 1, 2 Sinh viên tự đề xuất các câu truy vấn khác để thực hiện yêu cầu 22 Thi-Lan Le, MICA HUST 100 2. Ngôn ngữ thao tác dữ liệu class name id type class name id type 1A Aaron 9812 Piano 1A Bobby 9811 Flute 1A Aaron 9812 Piano 1A Gigi 9824 Recorder 1A Bobby 9811 Flute 1A Jill 9820 Piano 1A Gigi 9824 Recorder 1A Johnny 9803 Violin 1A Jill 9820 Piano 1A Luke 9810 Piano 1A Johnny 9803 Violin 1A Mary 9802 Flute 1A Luke 9810 Piano : : : : 1A Mandy 9821 1A Mary 9802 Flute 1A Peter 9801 Piano class name id 1A Ron 9813 Guitar 1A Mandy 9821 1B Eddy 9815 Piano 1B Kenny 9814 1B Tobe 9805 1B Janet 9822 Guitar 1C Edmond 9818 1B Kenny 9814 1C George 9817 1B Kitty 9806 Recorder : : : : : : : Thi-Lan Le, MICA HUST 101 Bài tập  Cho bảng thông tin: Viết câu truy vấn liệt kê tên khách hàng và tổng số tiền mà họ đã sử dụng Thi-Lan Le, MICA HUST 102