Bài giảng SQL Server - Chương 4: Truy vấn dữ liệu - Phạm Mạnh Cương

CHƯƠNG IVTRUY VẤN DỮ LIỆUGiảng viên: Phạm Mạnh Cương1NỘI DUNGI- Một số hàm T-SQL:II- Phát biểu truy vấn dữ liệuIII- các hàm xếp loại (ranking)2I- Một số hàm T-SQL:1. Hàm toán học2. Hàm xử lý chuỗi ký tự3. Hàm xử lý ngày tháng và thời gian4. Hàm chuyển đổi kiểu giá trị5. Hàm hệ thống6. Hàm CASE31- Hàm toán học41. ABS(x) : Trị tuyệt đối của x2. SQRT(x) : Căn bậc hai của x3. SQUARE( x) : tính x24. POWER( y, x ) : tính yx5. SIGN( x ) : Lấy dấu của số x (-1: x0)6. ROUND( x, n ) : Làm tròn tới n số lẻ.7. CEILING( x ) : Số nguyên nhỏ nhất >= x8. FLOOR( x ) : Số nguyên lớn nhất 63- Hàm xử lý ngày tháng và thời gian (tt)6. DATEPART(Mã_TP, date) : trả về thành phần chỉ định trong date, kiểu số.7. DATENAME(Mã_TP, date) : Trả về thành phần chỉ định trong ngày, kiểu chuỗi.8. DATEADD(Mã_TP, n, date): Trả về một ngày giờ (date + n), với n là số nguyên thuộc thành phần chỉ định9. DATEDIFF(Mã_TP, date_1, date_2):Khoảng cách (date_2 - date_1) theo thành phần chỉ định7select * from HoaDonwhere Datepart(yy,NgayHD)='1994'81.Hôm nay là ngày 27/08/2010. Ta có:SELECT DATEPART(month,GETDATE())-> Kết quả trả về là một số nguyên là 8 (tức tháng 8 )SELECT DATENAME(month,GETDATE())-> Kết quả trả về là một chuỗi là August (tức tháng 8 )2. Hôm nay là ngày 28 tháng 8 năm 2010, bạn muốn thăng lên 1 ngày và 1 tháng. Tức giá trị bạn mong  muốn là ngày 29 tháng 9 năm 2010, được viết như  sau:Ví dụSELECT DATEADD(month,1,DATEADD(day,1,'8/28/2010'))3.Use PubsSELECT DATEDIFF (day, pubdate, getdate ()) AS TongSoNgay From titles4- Hàm chuyển đổi kiểu giá trịThường dùng chuyển dữ liệu kiểu số, ngày giờ sang kiểuchuỗi để hiển thị ra màn hình1. STR( x [, len [, dec] ]): Chuyển số x sang kiểu chuỗi có tổngchiều dài len (mặc định 10) với phần lẻ dec (mặc định 0).Select MaMH, Str(DonGia, 10, 2) + N' đồng' From MatHang2. CAST(exp AS new_data_type)Select MaMH, Cast(DonGia As nVarchar(10)) + N' đồng'From MatHang3. CONVERT (new_data_type, exp [, style])Select MaMH, Convert(nVarchar(10), DonGia) + N' đồng'From MatHang9Style cho kiểu Float hoặc Real• 0 Tối đa 6 chữ số; nếu vượt quá sẽ hiển thị dạng mũ• 1 Hiện dạng mũ có 8 chữ số.• 2 Hiện dạng mũ có 16 chữ số.Ví dụ: Declare @a FloatSet @a = 58947.5589745SELECT Convert(Varchar,@a ,1)Kết quả: 5.8947559e+00410Style cho kiểu Money và SmallMoney• 0 Có 2 chữ số thập phân, không dấu phân cách hàng ngàn• 1 Có 2 chữ số thập phân, có dấu phân cách hàng ngàn• 2 Có 4 chữ số thập phân; không dấu phẩy phân cách hàng ngàn.Ví dụ:Declare @a moneySet @a = 12345.67895Print Convert(VarChar, @a, 0)• Kết quả: 12345.68Print Convert(VarChar, @a, 1)• Kết quả: 12,345.68Print Convert(VarChar, @a, 2)• Kết quả: 12345.689011Style cho kiểu Datetime và SmallDatetime125- Một số hàm hệ thống• ISDATE( exp ): Kiểm tra dạng ngày hợp lệ. Trả về 1 nếu hợp lệ và 0 nếu không hợp lệ.• ISNUMERIC( exp ): Kiểm tra dạng số hợp lệ. Trả về 1 nếu hợp lệ và 0 nếu không hợp lệ.• ISNULL(exp, value) : Trả về giá trị value nếu exp có giá trị NULL, ngược lại trả về giá trị của exp. Giá trị trả về cùng kiểu với exp.• @@ROWCOUNT: Trả về số dòng thực hiện bởi phát biểu cuối cùng.• @@ERROR : Trả về mã lỗi (integer) nếu có của phát biểu TSQL cuối cùng. Trả về số 0 nếu không có lỗi.136- Hàm CASE: Chọn lựa giá trịCú pháp 1: So sánh giá trị biểu thức với giá trị sau WHEN để xác định kết quả.14Hàm CASE : Chọn lựa giá trịCú pháp 2: Kiểm tra từng biểu thức điều kiện để xác định kết quả.15II- Phát biểu truy vấn dữ liệuSELECT select_list [ INTO new_table ][ FROM table_source ][ WHERE search_condition ][ GROUP BY group_by_expression ][ HAVING search_condition ][ORDER BY order_expression [ASC | DESC ]][COMPUTE aggregate_function_list [ BY expression]Thứ tự thực hiện:From>Where>Group By > Having>Select>Order>Compute16171- Mệnh đề FROM– Chỉ định bảng chứa dữ liệu cần truy vấn.Cú pháp: FROM table_source [ [ AS ] label ]– Bảng dữ liệu có thể là: Table, View, SubQuery, Function.– Kết nối mấu tin trên nhiều bảng bằng các phép toán:1. Phép tích Cartesian:FROM B1, B2,FROM B1 CROSS JOIN B22. Phép kết:FROM B1 INNER JOIN B2 ON FROM B1 LEFT JOIN B2 ON FROM B1 RIGHT JOIN B2 ON FROM B1 FULL JOIN B2 ON 18PHÉP TÍCH CARTESIAN:19PHÉP KẾT INNER JOIN20PHÉP KẾT TRÁI (LEFT JOIN)21PHÉP KẾT PHẢI (RIGHT JOIN)22PHÉP KẾT ĐẦY ĐỦ (FULL JOIN)23Ví dụ:242- Mệnh đề WHERE:WHERE • Dùng thực hiện phép chọn trên bảng kết xuất từ mệnh đề FROM • được thiết lập bởi các phép toán so sánh, phép toán luận lý trên các dữ liệu lưu trữ trên các cột. 25Ngoài ra còn có các các toán tử sau:26• Kiểm tra gía trị rỗng: IS [NOT] NULL• Thuộc miền giá trị: [NOT] BETWEEN AND • Thuộc tập giá trị: [NOT] IN (, , )• Kiểm tra chuỗi thuộc mẫu dữ liệu: [NOT] LIKE ““3- Mệnh đề SELECT27SELECT { | }[AS ], • Dùng thực hiện phép chiếu, chỉ định các cột cần có trên bảng kết quả truy vấn.• Đối với các thuộc tính trùng tên trên 2 bảng thì phải chỉ rõ muốn lấy thuộc tính của bảng nào bằng cách ghi : {|}..• Dấu * : chọn hết các cột của các bảng nguồn• .* : chọn hết các cột thuộc • Cột : [ [AS] ] hay = 28Giới hạn các dòng cần hiển thị:29SELECT [DISTINCT] [TOP( n ) [ PERCENT ] • DISTINCT : Chỉ hiện những dòng có dữ liệu phân biệt.Câu hỏi: Cho biết mã số và tên các mặt hàng đã bán ?• TOP () : chỉ hiện n dòng đầu tiên.Câu hỏi: Danh sách 3 mặt hàng đầu tiên trong table MatHang ?Tạo Table chứa dữ liệu truy vấn30Tạo Table chứa dữ liệu truy vấnSELECT INTO Yêu cầu: Tạo bảng ‘HoaDonLuu’ chứa danh sách các hóa đơn của năm cũ.SELECT * INTO HoaDonLuu from HoaDon4- Mệnh đề ORDER BY31ORDER BY { [ASC | DESC] [ , ...n] }• Sắp xếp các bộ theo thứ tự tăng hay giảm theo các giá trị của Ví dụ: Tính tiền bán từng mặt hàng trong CTHD sắp thứ tự theo SoHD, và Tiền bán• Kết hợp ORDER BY với TOP ( ) [PERCENT] WITH TIESĐể lấy thêm các dòng kế tiếp có cùng giá trị sắp xếp.Ví dụ: Liệt kê mặt hàng có đơn giá lớn nhất5- Mệnh đề GROUP BY:32GROUP BY • Dùng gom các mẫu tin thành từng nhóm và kết hợp với các hàm tổng hợp để tổng hợp dữ liệu trên từng nhóm.Các hàm tổng hợp theo nhóm:• SUM([DISTINCT] ): Tính tổng giá trị biểu thức số trên các dòng• AVG([DISTINCT] ): Tính trung bình cộng trên các dòng• MAX(): Chọn ra giá trị lớn nhất trên các dòng thuộc nhóm• MIN(): Chọn ra giá trị thấp nhất trên các dòng thuộc nhóm• COUNT([DISTINCT] ): Đếm số dòng trong từng nhóm mà có giá trị khác NULL.• COUNT(*): Đếm số dòng trong từng nhóm, kể cả những dòng có tất cả các thuộc tính đều NULLCâu hỏi331. Cho biết trong từng loại hàng có bao nhiêu mặt hàng, đơn giá lớn nhất và nhỏ nhất?SELECT MaLH, COUNT(MaMH) AS SoMatHang, MAX(DonGia) AS DonGiaLN, MIN(DonGia) AS DonGiaNNFROM MatHangGROUP BY MaLH2. Cho biết nhà cung cấp cung ứng các loại hàng nào, số mặt hàng trong từng loại là bao nhiêu ? SELECT MaNCC, MaLH, COUNT(MaMH) AS SoMatHangFROM MatHangGROUP BY MaNCC, MaLH3. Cho biết trong bảng MatHang có bao nhiêu loại hàng, bao nhiêu mặt hàng, đơn giá lớn nhất là bao nhiêu ? SELECT MaLH, COUNT(MaLH) AS SoLoaiHang, COUNT(MaMH) AS SoMatHang, MAX(DonGia) AS DonGiaLNFROM MatHangGROUP BY MaLHChú ý34• Mệnh đề GROUP BY phải chứa tất cả các cột không sử dụng trong các hàm tổng hợp có trong mệnh đề SELECT.Câu hỏi: Tính tiền hóa đơn theo đơn giá gốc của mặt hàngselect b2.SoHD, sum(b1.DonGia*b2.SL) as ThanhTienFrom CTHD as b2 inner join MatHang as b1 on b2.MaMH = b1.MaMHGroup by b2.SoHDGROUP BY ALL 35• Trả về tất cả các nhóm, kể cả những nhóm không có mẫu tin nào thỏa mãn điều kiện của mệnh đề WHERE.Câu hỏi: Cho biết số mặt hàng có đơn giá> 20 trong từng loại hàngselect MaLH,count(MaMH) as SLHfrom MatHangwhere DonGia>20group by MaLH6- Mệnh đề HAVING36HAVING • Dùng chọn các nhóm thỏa mãn Câu hỏi: Cho biết các hóa đơn có tiền hóa đơn > 5007- Mệnh đề COMPUTE:37COMPUTE HàmTổngHợp( ) [ ,...n ]• Tạo thêm bảng thứ 2 có một dòng chứa các giá trị tổng hợp trên các mẫu tin của các hàm trong COMPUTE.• Các trong mệnh đề COMPUTE phải xuất hiện trong mệnh đề Select.Ví dụ: Liệt kê danh sách nhân viên, đồng thời thêm bảngchứa số nhân viên và lương trung bình của nhân viên.Select MaKV, MaNV, Ho, Ten, LuongCB From NhanVien Compute Count(MaNV), AVG(LuongCB)388- Mệnh đề COMPUTE ... BY:39ORDER BY [ ASC | DESC ] [ ,...n ]COMPUTE ( ) [ ,...n ]BY [ ,...n ]• Kết hợp với mệnh đề ORDER BY, tạo thêm các bảng chứa dòng tổng hợp của từng nhóm mẫu tin dựa vào các gom nhóm trong mệnh đề BY.• Các trong BY là tập con của các trong ORDER BY, theo thứ tự từ trái qua phải, bắt đầu từ cột đầu tiên và không bỏ qua bất kỳ cột nào.Ví dụ: Cho biết Mã khu vực, họ tên, lương cơ bản của nhânviên, đồng thời tạo thêm các bảng chứa số nhân viên và lương trung bình của các nhân viên trong từng khu vực40Select MaKV, Ho, Ten, LuongCB From NhanVienOrder By MaKVCompute Count(MaKV), AVG(LuongCB) By MaKV9- Các câu truy vấn lồng nhau41• Trong nhiều trường hợp chúng ta cần phải tìm kiếm thông tin qua nhiều bước truy vấn: kết quả truy vấn của bước trước được sử dụng trong phát biểu truy vấn tiếp theo• Trường hợp này có thể được giải quyết bằng các cách sau:– Cách 1: Sử dụng trực tiếp phát biểu truy vấn của bước trước bên trong phát biểu truy vấn của bước sau. Phát biểu truy vấn trung gian đó được gọi là Subquery. Câu hỏi con, phải được bao trong cặp dấu ngoặc tròn.– Cách 2: Gán tên kết quả truy vấn của bước trước và sử dụng tên này trong phát biểu truy vấn của bước sau.Sử dụng Subquery trong mệnh đề FROM:42FROM () AS Câu hỏi: Liệt kê số hóa đơn có ghi các mặt hàng có đơn giá lớn nhất?SELECT SoHDFROM (SELECT TOP(1) MaMH FROM MatHangORDER BY DonGia DESC) as MH_Max, CTHD WHERE CTHD.MaMH = MH_Max.MaMHSử dụng Subquery trong mệnh đề WHERE43• Các phép toán trên Subquery:1. [NOT] IN ()Câu hỏi: Mã số, tên các nhân viên không là nhân viên quản lý2. ALL ()Câu hỏi: Mã số và tên các mặt hàng có đơn giá lớn nhấtSELECT MaMH, TenMH, DonGiaFROM MatHangWHERE DonGia >= ALL (SELECT DonGia FROM MatHang AS MatHang )3. ANY ()Câu hỏi: Mã số và tên các mặt hàng có đơn giá không là đơn giá lớn nhấtSELECT MaMH, TenMH, DonGiaFROM MatHangWHERE DonGia ) : Trả về True nếu subquery có mẫu tin.Câu hỏi: Mã số và tên các mặt hàng bán trong thángSELECT * FROM MATHANG AS aWHERE EXISTS(SELECT DISTINCT MAMH FROM CTHD as b where a.MAMH=b.MAMH)44Biểu thức bảng (Common table expression)45WITH expression_name [ ( column_name [ ,...n ] ) ]AS ( SELECT statement )• Gán tên cho bảng kết quả của một phát biểu Select để sử dụng trongphát biểu SELECT, INSERT, UPDATE, hay DELETE kế tiếp.• expression_name: Tên của biểu thức bảng• column_name: tên các cột trong biểu thức bảng.• Phát biểu Select không chứa các phát biểu:– COMPUTE hoặc COMPUTE BY– ORDER BY (ngoại trừ có sử dụng mệnh đề TOP)– INTOSử dụng biểu thức bảng46Câu hỏi: Liệt kê số hóa đơn có ghi các mặt hàng có đơn giá lớn nhất?WITH MH_MaxAS (SELECT TOP(1) MaMH FROM MatHang ORDER BY DonGia DESC)SELECT SoHDFROM CTHD INNER JOIN MH_MaxON CTHD.MaMH = MH_Max.MaMHIII- CÁC HÀM XẾP LOẠI (RANKING)47• Tạo cột chứa giá trị xếp loại (Ranking) cho mỗi dòng trongtừng nhóm mẫu tin, dựa trên mệnh đề OVER. ranking_functionOVER( [ ] ) AS column_label • ::= PARTITION BY [, .. n] Dùng phân chia bảng dữ liệu trả về từ mệnh đề FROM thành nhiều nhóm mẫu tin dựa trên giá trị các . • : Xác định thứ tự các dòng trong từng nhóm để nhận giá trị hàm Ranking.1- Hàm ROW_NUMBER()48Trả về số thứ tự, kiểu BigInt, của mỗi dòng trong mỗi nhómmẫu tin. Bắt đầu từ 1 cho mỗi nhóm.Xếp thứ tự mặt hàng theo đơn giá trongtừng loại hàng và thêm cột đánh số thứ tựcho các mặt hàng trong từng loại hàngSELECT MatHang.MaMH, TenMH,DONGIA,MALH,ROW_NUMBER() OVER(PARTITION BY MALH ORDER BY DONGIA ASC) AS STTFROM MatHangVí dụ: không dùng 49SELECT MatHang.MaMH, TenMH,DONGIA,MALH,ROW_NUMBER() OVER(ORDER BY MALH ASC) AS STTFROM MatHang2- Hàm RANK()50• Trả về giá trị xếp hạng, kiểu BigInt của mỗi dòng trong mỗi nhóm mẫu tin.• Với mỗi nhóm, giá trị xếp hạng bắt đầu từ 1, giá trị xếp hạng của dòng sau sẽbằng giá trị của dòng trước nếu cùng giá trị sắp xếp ngược lại thì bằng số thứtự mẫu tin trong nhómVí dụ: Xếp hạng các mặt hàng theo đơngiá trong từng loại hàngSELECT MatHang.MaMH, TenMH,DONGIA,MALH,RANK() OVER(partition by MALH ORDER BY DONGIA ASC) AS HANGFROM MatHangCẢM ƠN51