Giáo trình Hệ quản trị cơ sở dữ liệu MSSQL 2005 - Chương 3: Ngôn ngữ thao tác dữ liệu - Hồ Thị Anh Đào

Chương 3NGÔN NGỮ THAO TÁC DỮ LIỆUKHOA CÔNG NGHỆ THÔNG TINI. CÂU LỆNH SELECT 1. Ứng dụng:Câu lệnh Select dùng để truy xuất dữ liệu từ một hay nhiều bảng. 2. Cú pháp:SELECT [ALL | DISTINCT][TOP n] [INTO tên_bảng_mới]FROM [WHERE điều_kiện][GROUP BY ds cột phân nhóm] [HAVING điều_kiện nhóm][ORDER BY cột_sắp_xếp][DESC | ASC] Hiển thị danh sách cột từ các bảng dữ liệu gồm các dong tin thỏa mãn điều kiện.2. Danh sách cột trong câu lệnh SELECT Danh sách cột: là dãy các cột/ biểu thức cột cách nhau bởi dấu phẩy.Ví dụ: 1. Hiển thị một số cột trong một bảng SELECT MaLop,TenLop,NamNhapHoc,Khoahoc FROM DMLOP 2. Hiển thị tất cả các cột trong một bảng SELECT * FROM DMLOP 3. Hiển thị một số cột trong nhiều bảng quan hệ SELECT MaLop, TenLop, DMLOP.MaKhoa,TenKhoa FROM DMLOP, DMKHOA WHERE DMLOP.MaKhoa = DMKHOA.MaKhoaKhi thực hiện lệnh sẽ kiểm tra điều kiện từng dòng, nếu thỏa mãn thì hiển thị kết quả. Tham chiếu đến cột KHÓA của bảng DMLOP4. Thay đổi tiêu đề cộtSELECT MaLop AS 'Mã lớp', TenLop AS 'Tên lớp’, Khoahoc AS 'Khóa học’.FROM DMLOPHoặcSELECT 'Mã lớp'= MaLop, 'Tên lớp‘=TenLop,'Khóa học‘=KhoahocFROM DMLOP 5. Hằng và Biểu thức cộtSELECT TenMonhoc, Sodvht*15 AS ‘Số tiêt’FROM DMMOHOCVí dụ: Hiển thị Mã sinh viên, Họ, Tên, Giới tính 0: Nữ; 1: Nam; Null không có.Cấu trúc rẽ nhánh CASECASE WHEN THEN [WHEN THEN ELSE ENDSELECT Masv, Ho+Ten,CASE GioitinhWHEN 1 THEN 'Nam’WHEN 0 THEN N'Nữ'ELSE N‘Không có’END AS ‘Giới tính’FROM SINHVIENVí dụ: Hiển thị danh sách gồm Họ Tên, Ngày sinh dạng ‘dd/mm/yyyy’ Sử dụng hàm ConvertCONVERT(kiểu_dữ_liệu, biểu_thức [,kiểu_chuyển_đổi])Hàm có chức năng chuyển đổi giá trị của biểu thức sang kiểu dữ liệu được chỉ định. Tham số là một giá trị số nhằm qui định định dạng dữ liệu hiển thị và được qui định như sau:Kiểu ngày Định dạng dữ liệu hiển thị0 (100) Mặc định 101 (1) mm/dd/yyyy (mm/dd/yy)102 (2) yyyy-mm-dd (yy-mm-dd)103 (3) dd/mm/yyyy (dd/mm/yy) SELECT HoTen,CONVERT(char(10),NgaySinh,101) AS 'Ngày Sinh'FROM SINHVIENVí dụ: Cho biết tổng số sinh viên hiện có trong bảng SINHVIENSử dụng Hàm Sum, Count, AVG Select Count(MaSV) From SINHVIEN6. Giới hạn số lượng dòng trong kết quả truy vấn TOP n: truy vấn n mẫu tin đầu tiên TOP N%: Truy vấn n% mẫu tin đầu tiên. DISTINT: Loại bỏ những giá trị trùng nhau trong danh sách.Ví dụ:SELECT TOP 5 Ho,Ten,NgaysinhFROM SINHVIENVí du: Hiển thị các Khoa có lớp họcSELECT DISTINCT MaKhoa FROM DMLOP2. Điều kiện trong câu lệnh SELECTWHERE : Điều kiện nằm sau từ khóa WHERE, là một biểu thức Logic gồm các phép toán sau:Các toán tử kết hợp điều kiện:AND, ORCác toán tử so sánh: >,=,,!, =Kiểm tra giới hạn của dữ liệu: BETWEEN/NOT BETWEENToán tử thuộc tập hợp, không thuộc tập hợp: IN (dãy giá trị | truy vấn Select|), NOT IN ()Kiểm tra khuôn dạng dữ liệu: LIKE /NOT LIKE Với ký tự đại diện: %: đại diện cho một nhóm ký tự _: đại diện cho một ký tự [dãy ký tự]: ký tự đơn nằm trong dãy ký tự chỉ định như [0-9], [ABC] [^dãy ký tự]: ký tự đơn KHÔNG nằm trong dãy ký tự chỉ định Ví dụ: Hiển thị danh sách gồm MaSV, HoTen, Malop, của những sinh viên lớp ‘CT11’SELECT MaSV, Ho, Ten, MaLop FROM DMLOPWHERE MaLop=‘CT11’ Ví dụ: Hiển thị danh sách gồm MaSV, HoTen, Malop của những sinh viên lớp ‘CT11’, CT12’SELECT MaSV, Ho, Ten, MaLop FROM DMLOPWHERE MaLop=‘CT11’ OR MaLop=‘CT12’ SELECT MaSV, Ho, Ten, MaLop FROM DMLOPWHERE MaLop IN (‘CT11’, ‘CT12’ )Vẫn câu hỏi đó nhưng hiển thị thêm cột TenLopVí dụ: Hiển thị danh sách sinh viên có họ là LêSELECT Ho,Ten FROM SINHVIENWHERE Ho LIKE 'Lê%‘Bài tập:Danh sách sinh viên gồm: MaSV, Ho, Ten, GioiTinh (Nam,Nữ), Ngày sinh (dd/mm/yyyy)Cho biết những sinh viên có họ bắt đầu bằng N,L,THiển thị danh sách sinh viên nữ lớp CT11 và CT12.Hiển thị danh sách sinh viên không ở lớp CT11.Hiển thị danh sách 5 sinh viên có tuổi cao nhất.Hiển thị số lượng sinh viên học lớp CT11.Hiển thị danh sách sinh viên nữ có sinh trước năm 1995Hiển thị danh sách sinh viên có DiemMH Ví dụ: Đưa ra danh sách những lớp có tổng số sinh viên >10 SELECT DMLOP.MaLop,TenLop,COUNT(MaSV) AS Siso FROM DMLOP, SINHVIEN WHERE DMLOP.MaLop=SINHVIEN.MaLop GROUP BY DMLOP.MaLop,TenLop HAVING COUNT(Masv)>10Ví dụ: Tính Điểm Trung bình chung học kỳ theo từng sinh viên. Xuất dữ liệu ra bảng mới có tên DIEMTBC Sử dụng bảng DIEMTHI Vùng phân nhóm là: MaSV,Hocky Biểu thức tính tổng là (DiemMH*Sodvht)/(Sodvht) SELECT MaSV, Hocky,Sum(DiemMH*Sodvht)/Sum(Sodvht) AS DTBC INTO DIEMTBC FROM DIEMTHI,DMMON WHERE DMMON.MaMon=DIEMTHI.MaMonhoc GROUP BY Masv,HọckyVí dụ: Cho biết những sinh viên có ít nhất 2 môn học có DiemMH =2Sử dụng Bảng DIEMTHISử dựng Từ khóa WHERE Sử dụng GROUP BY có HAVINGSelect MaSV, Count(MaMonhoc) AS SoluongFrom DIEMTHIWhere DTB=2Thêm vùng: Ho, Ten, Lop.Bài tậpTính Điểm trung bình chung của các học kỳ cho từng sinh viên.Cho biết những sinh viên có tổng số đơn vị học trình của các môn thiếu điểm trên 25 (sinh viên ở lại lớp)Cho biết những sinh viên có Điểm TK các môn học [NOT] IN (Câu lệnh Select_con) Select MalopFrom SINHVIENWhere Ten=N’Hoa’)Select TenLopFrom DMLOPWhere MaLop IN (Ví dụ:Cho biết Tên lớp có sinh viên tên HoaSelect TenMonhoc From DMMONHOCWhere MaMonhoc NOT IN (Select Mamonhoc From DIEMTHI Where DiemMH=Đưa lượng từ trước tập hợp có nhiều hơn MỘT giá trịLượng từ ALL All (Câu lệnh Select) (Select DiemMH From DIEMTHI)Ví dụ: Cho biết Tên sinh viên có DiemMH lớn nhấtSelect Ho, TenFrom DIEMTHI,SINHVIENWhere (DiemMH >=ALL(Select DiemMH from DIEMTHI) AND SINHVIEN.MaSV=DIEMTHI.MasvSelect MaSVFrom DIEMTHIWhere DiemMH >=ALLVí dụ: Cho biết Mã sinh viên có DiemMH lớn nhấtNghĩa là lớn hơn hoặc bằng tất cả các giá trị điểm của tập DiemMHLượng từ Any Any (Câu lệnh Select)Ví dụ:Cho biết MaSV, Mamon có DiemMH lớn hơn bất kỳ Điểm môn học của sinh viên có Mã là ‘001’ ANY (Select DiemMonhoc From DIEMTHI Where MaSV=‘001’)Select MaSV, MamonhocFrom DIEMTHIWhere DiemMH >=Lượng Từ EXISTS[NOT] EXISTS (truy_vấn_con) Lượng từ EXISTS trả về giá trị True nếu kết quả của truy vấn con có ít nhất một dòng (khác rỗng), ngược lại trở về giá trị FalseTương tự NOT EXISTSVí dụ: Cho biết thông tin môn học có sinh viên họcSelect * From DMMONHOCWhere Exists (Select * From DIEMTHI Where DMONHOC.MaMon=DIEMTHI.MaMon)Ví dụ: Cho biết họ tên của những sinh viên hiện chưa có điểmthi của bất kỳ một môn học nào?SELECT Ho, TenFROM SINHVIENWHERE NOT EXISTS(SELECT Masv FROM DIEMTHI WHERE DIEMTHI.Masv=SINHVIEN.Masv)5. PHÉP TOÁN TẬP HỢPUnion (hợp)Except (Hiệu)Intersect (Giao)Đặc điểm:Các dòng giống nhau trong tập kết quả sẽ bị loại bỏCác tập hợp tham gia trong phép toán phải khả hợp nghĩa là phải cùng tập các cột.Giữ lại những dòng giống nhau thêm từ khóa ALLUnion All, Except All, Intersect AllCú pháp Câu_lệnh_1 {UNION | EXCEPT | INTERSET} [ALL] Câu_lệnh_2 [{UNION | EXCEPT | INTERSET} [ALL] Câu_lệnh_3] ... [{UNION | EXCEPT | INTERSET} [ALL] Câu lệnh_n]5.1. Phép hợp (Union)Hợp của 2 hay nhiều truy vấn Select là một tập tất cả các bộ giá trị của các truy vấn đó.Vidu: Cho biết mã sinh viên đã học ít nhất một môn có mã là “001”, “002”Select Masv From DIEMTHI Where Mamon=‘001’Union(Select Masv From DIEMTHI Where Mamon=‘002’)5.2 Phép hiệu (Except)Hiệu của 2 truy vấn A và B là một tập tất cả các bộ giá trị thuộc truy vấn A nhưng không thuộc truy vấn B.Ví dụ: Cho biết Mã sinh viên chưa học môn nàoLà những sinh viên có trong bảng SINHVIEN mà không ở bảng DIEMTHI Select MaSV from SINHVIEN Except (select MaSV from DIEMTHI)5.3 Phép giao (Intersect)Giao của 2 hay nhiều truy vấn Select là một tập gồm các bộ giá trị thuộc các truy vấn đó.Ví dụ: Cho biết Mã sinh viên học cả hai môn có mã ‘001’ và ‘002’ Select Masv from DIEMTHI Where MAMONHOC='001' Intersect (Select Masv from DIEMTHI where MAMONHOC='002')Bài tậpCho biết Mã môn học mà chưa được họcCho biết Tên môn học mà chưa được học.Cho biết Mã giáo viên dạy cả hai môn có mã ‘001’ và ‘002’Cho biết Tên giáo viên dạy cả hai môn có mã ‘001’ và ‘002’.Cho biết Mã giáo viên dạy môn học có số đơn vị học trình >=5.Cho biết Masv học ít nhất một học do giáo viên mã ‘001’.6. BỔ SUNG, CẬP NHẬT, XÓA DỮ LIỆU Lệnh INSERTLệnh UPDATE Lệnh DELETE6.1 Lệnh INSERT Dùng để bổ sung thêm các dòng dữ liệu vào một bảng Bổ sung từng dòng dữ liệu Cú pháp lệnh INSERT INTO tên_bảng[(danh_sách_cột)] VALUES(danh_sách_trị) Bổ sung nhiều dòng dữ liệu bằng cách truy xuất dữ liệu từ các bảng dữ liệu khác. Cú pháp lệnh INSERT INTO tên_bảng[(danh_sách_cột)] Câu lệnh Select Ví dụ: Bổ sung một dòng vào bảng DMKHOA với bộ giá trị sau (‘KT’, ‘Kế toán’) INSERT INTO KHOA(Makhoa, TenKhoa) WITH (‘KT’, ‘Kế toán’)Bổ sung tất cả các cột trong bảng INSERT INTO KHOA WITH (‘KT’, ‘Kế toán’,NULL)6.2 Lệnh UPDATE Dùng để cập nhật dữ liệu cho nhiều cột trong bảng Cú pháp UPDATE tên_bảng SET Tên_cột 1 = biểu_thức 1, Tên_ cột 2 = biểu thức 2 [, ..., Tên_cột_k = biểu_thức_k] [FROM Danh dách bảng] [WHERE điều_kiện] Where: chỉ cập nhật cột có dòng thỏa mãn điều kiện From: Dữ liệu cập nhật liên qua tới nhiều bảng.Ví dụ: Thêm cột Xếp loại trong bảng DIEMTBC, Cập nhật dữ liệu cho cột đó theo yêu cầu sau: Nếu DiemTBC >=8 thì xếp loại Giỏi, ngược lại Nếu DiemTBC >=7 thì xếp loại Khá, ngược lại Nếu DiemTBC >=5 thì xếp loại Trung bình Ngược lại là yếu Alter Table DIEMTBC ADD Xeploai nvarchar(10) NULL Update DIEMTBC Set Xeploai = Case When DTBC>=8 then N'Giỏi' When DTBC>=7 then N'Khá' When DTBC>=5 then N'Trung bình' Else N'Yếu' END 6.3 Lệnh DELETEĐể xoá các dòng dữ liệu trong một bảng Cú pháp DELETE FROM tên_bảng [FROM danh_sách_bảng] [WHERE điều_kiện]Nếu không có Where thì xóa tất cả các dòngVí dụ: Xoá khỏi bảng SINHVIEN những sinh viên có nơi sinh tại HuếDELETE FROM sinhvienWHERE noisinh LIKE ‘%Huế%’Ví dụ: Xóa khỏi bảng DMLOP những lớp không có sinh viên nàoDELETE FROM DMLOPWHERE Malop NOT IN (SELECT DISTINCT Malop FROM sinhvien)7. CÁC LOẠI PHÉP NỐIINNER JOIN: Là phép kết nối gồm những dòng có giá trị so khớp nhau trên cả hai bảng.FULL JOIN: Phép kết nối gồm tất cả các dòng của hai bảng.RIGHT JION: Phép kết nối gồm các dòng có giá trị so khớp nhau trên cả hai bảng và các dòng thuộc bảng bên PHẢI của liên kếtLEFT JION: Phép kết nối gồm các dòng có giá trị so khớp nhau trên cả hai bảng và các dòng thuộc bảng bên TRÁI của liên kết. Cú pháp lệnh: SELECT FROM {INNER| FULL| RIGHT| FULL} JOIN ON (Tham khảo trên cửa sổ View)Ví dụ:Hiển thị danh sách sinh viên gốm: MaSV, Ho, Ten, Malop, Tenlop, Gioitinh. (INNER JOIN)Hiến thị MaLop, TenLop chưa có sinh viên nào (chứa giá trị NULL) (LEFT/RIGHT JOIN)Hiển thị tất cả các thông tin trên 2 bảng SINHVIEN và DMLOP.II . KHUNG NHÌN (WIEW)Một khung nhìn (view) có thể được xem như là một bảng “ảo” trong cơ sở dữ liệu có nội dung được định nghĩa thông qua một truy vấn (câu lệnh SELECT) Cách tạo ViewC1: Thao tác bằng chuộtClick phải tại đối tượng ViewChọn New ViewĐẩy các bảng1. Chứa các bảng liên kết2. Xây dựng các tham số cho view3. Câu lệnh SQL tương ứng4. Kết quả Chọn cộtTiêu đề cộtHiển thị cột hay khôngSắp xếpThứ tự sx ưu tiênGhi điều kiện lọcVí dụ:Hiển thị danh sách sinh viên gồm MaSV, Ho,Ten, Ngaysinh, Gioitinh TenLop.Ví dụ: Hiển thị danh sách sinh viên gồm MaSV, Ho,Ten, Ngaysinh, Gioitinh TenLop của sinh viên nữ học lớp ‘CT11’,’CT12’Ví dụ: Đếm số lượng sinh viên mỗi lớpVí du: Đếm số sinh viên nữ mỗi lớpVí dụ: Cho biết Lớp nào chưa có sinh viênCâu hỏi kiểm tra 15 phút:Câu số 1:Phép toán NOT IN () trong cấu trúc lồng nhau để trả lời các dạng câu hỏi nào cho ví dụHiển thị danh sách sinh viên gồm Masv, Họ Tên, giới tính (nam,nữ), Ngày sinh (dd/mm/yyyy), Malop của những sinh viên nữ lớp CT11 và CT12.Câu số 2: Từ khóa Group by sẽ trả lời cho các dạng câu hỏi nào, cho ví dụHiển thị danh sách sinh viên gồm Masv, Họ Tên, giới tính (nam,nữ), Ngày sinh (dd/mm/yyyy), Malop của những sinh viên có tên bắt đầu bằng chữ L,N,HTóm tắt chương 3:Dùng câu trúc truy vấn Select để trả lời các dạng câu hỏi sau:Từ khóa Where trả lời câu hỏi có điều kiện lọcCâu hỏi lấy dữ liệu từ 2 bảng thì thêm điều kiện kết nối (SINHVIEN.Malop=DMLOP.Malop, DIEMTHI.MaMH=DMMON.MaMH, ) khi đó để tham chiếu đến cột liên kết thì phải thêm đường dẫn tới bảng của nó (SINHVIEN.Malop, DIEMTHI.Malop)Từ khóa Group by sẽ trả lời cho các dạng câu hỏi như: tính tổng theo từng nhóm, điều kiện thỏa mãn ít nhất 2,3, giá trị hay tất cả (thiếu điểm ít nhất 2 môn học, 3 môn học, , tất cả môn học). Tuy nhiên ta có thể dùng phép toán tập hợp Intersect (phức tạp)Phép toán tập hợp Except trả lời những dạng câu hỏi có từ “chưa ” (Những lớp CHƯA có sv nào, sinh viên CHƯA học môn nào)Dùng lượng từ Exists để kiểm tra kết quả truy vấn có giá trị trị hay không (thường dùng trong lập trình)Phép toán NOT IN () trong cấu trúc lồng nhau để trả lời các dạng câu hỏi phủ định (KHÔNG thiếu môn học nào, KHÔNG )Lượng từ ALL thường trả lời cho các câu hỏi dạng lớn NHẤT, nhỏ NHẤT. (>= ALL() hoặc 7Cho biết những sinh viên có ít nhất 2 môn học có DiemMH <5Cho biết những sinh viên có tổng số đơn vị học trình của các môn thiếu điểm trên 25 (sinh viên ở lại lớp)Cho biết những sinh viên có Điểm TK các môn học <3 (ở lại lớp).Cho biết tên sinh viên KHÔNG thiếu môn học nàoCho biết tên sinh viên học TẤT CẢ các môn họcCho biết tên sinh viên học ít nhất hai môn có mã ‘001’ và ‘002’Cho biết tên sinh viên có điểm môn ‘001’ cao nhất.Cho biết Tên môn học mà chưa có sinh viên nào học