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

CHƯƠNG 3 NGÔN NGỮ ĐỊNH NGHĨA VÀ THAO TÁC DỮ LIỆU 52Thi-Lan Le, MICA HUST 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ý 53Thi-Lan Le, MICA HUST 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 54Thi-Lan Le, MICA HUST 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> ]) 55Thi-Lan Le, MICA HUST 1. Ngôn ngữ định nghĩa dữ liệu  Tạo bảng – Xác định khóa chính 56Thi-Lan Le, MICA HUST 1. Ngôn ngữ định nghĩa dữ liệu  Thêm - Xóa cột trong bảng 57Thi-Lan Le, MICA HUST 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 58Thi-Lan Le, MICA HUST 2. Ngôn ngữ thao tác dữ liệu  Một số từ khóa General Structure SELECT, ALL / DISTINCT, *, AS, FROM, WHERE Comparison IN, BETWEEN, LIKE "% _" Grouping GROUP BY, HAVING, COUNT( ), SUM( ), AVG( ), MAX( ), MIN( ) Display Order ORDER BY, ASC / DESC Logical Operators AND, OR, NOT Output INTO TABLE / CURSOR TO FILE [ADDITIVE], TO PRINTER, TO SCREEN Union UNION 59Thi-Lan Le, MICA HUST 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 60Thi-Lan Le, MICA HUST 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. : : : : : : : : : 61Thi-Lan Le, MICA HUST 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 62Thi-Lan Le, MICA HUST 2. Ngôn ngữ thao tác dữ liệu  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 • Toán từ logic AND, OR, NOT SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2] ; FROM tablename WHERE condition 63Thi-Lan Le, MICA HUST 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 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. : : : : : : : : : Kết quả 64Thi-Lan Le, MICA HUST 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 Mary Y 1A Johnny G 1A Luke G 1A Bobby B 1A Aaron R 1A : : : Kết quả 65Thi-Lan Le, MICA HUST 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 HHM KWC MKK SSP TST YMT Kết quả 66Thi-Lan Le, MICA HUST 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 Janet Sandy Mimi Kết quả 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 67Thi-Lan Le, MICA HUST 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 Luke 9810 1A Bobby 9811 1A Aaron 9812 1A Ron 9813 1A Gigi 9824 1A : : : Kết quả 68Thi-Lan Le, MICA HUST 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 "%_" 69Thi-Lan Le, MICA HUST 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 Wendy 1B Wednesday Kevin 1C Saturday Luke 1A Wednesday Aaron 1A Saturday : : : Kết quả 70Thi-Lan Le, MICA HUST 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) Trong đó: MONTH (): trả về tháng Bài tập: Viết lại câu truy vấn trên sử dụng toán tử IN name class dob Wendy 1B 07/09/86 Tobe 1B 10/17/86 Eric 1C 05/05/87 Patty 1C 08/13/87 Kevin 1C 11/21/87 Bobby 1A 02/16/86 Aaron 1A 08/02/86 : : : Kết quả 71Thi-Lan Le, MICA HUST 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 Luke 86 Aaron 83 Gigi 84 Kết quả 72Thi-Lan Le, MICA HUST 2. Ngôn ngữ thao tác dữ liệu  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 SELECT ...... FROM ...... WHERE condition ; GROUP BY groupexpr [HAVING requirement] Các hàm của nhóm: COUNT( ), SUM( ), AVG( ), MAX( ), MIN( ) 73Thi-Lan Le, MICA HUST 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 1B 9 1C 9 2A 8 2B 8 2C 6 Kết quả 74Thi-Lan Le, MICA HUST 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 1C 37.89 2A 89.38 2B 53.13 2C 32.67 Kết quả 75Thi-Lan Le, MICA HUST 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 76Thi-Lan Le, MICA HUST 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 HHM 6 KWC 1 MKK 1 SSP 5 TST 4 YMT 8 Kết quả 77Thi-Lan Le, MICA HUST 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 91 19 MKK 91 31 SSP 92 36 TST 75 75 TSW 88 38 YMT Kết quả 78Thi-Lan Le, MICA HUST 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 86.00 1A 77.75 1B 35.60 1C 86.50 2A 56.50 2B Kết quả 79Thi-Lan Le, MICA HUST  Đư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 80Thi-Lan Le, MICA HUST 2. Ngôn ngữ thao tác dữ liệu  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 SELECT ...... FROM ...... WHERE ...... ORDER BY colname [ASC / DESC] 81Thi-Lan Le, MICA HUST 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 Peter 9801 Johnny 9803 Luke 9810 Bobby 9811 Aaron 9812 Ron 9813 ORDER BY name name id Aaron 9812 Bobby 9811 Johnny 9803 Luke 9810 Peter 9801 Ron 9813 Kết quả 82Thi-Lan Le, MICA HUST 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 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 Kết quả 83Thi-Lan Le, MICA HUST 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 10 HHM 10 SSP 9 MKK 5 TST 2 TSW 1 KWC 1 MMK 1 SHT Kết quả 84Thi-Lan Le, MICA HUST  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ữ 85Thi-Lan Le, MICA HUST 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 86Thi-Lan Le, MICA HUST 2. Ngôn ngữ thao tác dữ liệu name hcode class Bobby B 1A Teddy B 1B Joseph B 2A Zion B 2B Leslie B 2C Johnny G 1A Luke G 1A Kevin G 1C George G 1C : : : Trật tự theo class Blue House Green House Trậ tự theo hcode 87Thi-Lan Le, MICA HUST 2. Ngôn ngữ thao tác dữ liệu  Hợp, giao và hiệu của các bảng A B Hợp của A và B (AB) Một bảng bao gồm tất cả các dòng của A và B. 88Thi-Lan Le, MICA HUST 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) 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 A B 89Thi-Lan Le, MICA HUST 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 Bảng bao gồm các dòng ở trong A nhưng không ở trong B A B 90Thi-Lan Le, MICA HUST 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 : : : : : : : : 91Thi-Lan Le, MICA HUST 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 ...... 92Thi-Lan Le, MICA HUST 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 ) 93Thi-Lan Le, MICA HUST 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 ) 94Thi-Lan Le, MICA HUST 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ụ 95Thi-Lan Le, MICA HUST 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 96Thi-Lan Le, MICA HUST 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 97Thi-Lan Le, MICA HUST 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 1A Bobby 9811 Flute 1A Gigi 9824 Recorder 1A Jill 9820 Piano 1A Johnny 9803 Violin 1A Luke 9810 Piano 1A Mary 9802 Flute : : : : Kết quả 98Thi-Lan Le, MICA HUST 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 Music Student Kết quả Join Điều kiện m.type= "Piano" Nhóm theo lớp class cnt 1A 4 1B 2 1C 1 99Thi-Lan Le, MICA HUST 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 1B Kenny 9814 1B Tobe 9805 1C Edmond 9818 1C George 9817 : : : Kết quả 100Thi-Lan Le, MICA HUST 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 101Thi-Lan Le, MICA HUST class name id 1A Mandy 9821 1B Kenny 9814 1B Tobe 9805 1C Edmond 9818 1C George 9817 : : : class name id type 1A Aaron 9812 Piano 1A Bobby 9811 Flute 1A Gigi 9824 Recorder 1A Jill 9820 Piano 1A Johnny 9803 Violin 1A Luke 9810 Piano 1A Mary 9802 Flute : : : : class name id type 1A Aaron 9812 Piano 1A Bobby 9811 Flute 1A Gigi 9824 Recorder 1A Jill 9820 Piano 1A Johnny 9803 Violin 1A Luke 9810 Piano 1A Mandy 9821 1A Mary 9802 Flute 1A Peter 9801 Piano 1A Ron 9813 Guitar 1B Eddy 9815 Piano 1B Janet 9822 Guitar 1B Kenny 9814 1B Kitty 9806 Recorder : : : : 2. Ngôn ngữ thao tác dữ liệu 102Thi-Lan Le, MICA HUST 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