Chương 3: SQL - Structured Query Language Phần 1

Chương 3SQLStructured Query LanguagePhần 1Giới thiệuSQL là ngôn ngữ CSDL Quan hệ chuẩnĐược sử dụng trong các DBMS thương mại Được phát triển từ IBM (vào đầu 70s, Sequel)Phiên bản chuẩn được ANSI công bố đầu tiên vào 1986SQL là ngôn ngữ khai báo cấp cao Người dùng chỉ ra kết quả cần đạt là gìTối ưu hóa và quyết định thi hành ntn là do DBMSGiới thiệuSQL bao gồm các phần :DDL (Data Definition Language)Định nghĩa DatabaseĐịnh nghĩa cấu trúc BảngĐịnh nghĩa ràng buộc toàn vẹnĐịnh nghĩa khung nhìnDML (Data Manipulation Language)Truy vấn dữ liệuCác thao tác thêm, xóa , sửa dữ liệuKiểm soát các transactionDCL (Data Control Language)Phân quyền người dùngGiới thiệuTrong một số DBMS, SQL được mở rộng để tăng khả năng lập trình thủ tụcT-SQL trong MS SQL Server cho phép dùng cấu trúc điều khiển, biến cục bộ, cursorTạo các procedure, triggerNgôn ngữ thao tác dữ liệu - DMLDML bao gồm các lệnh : SELECT, INSERT, UPDATE, DELETELệnh Select là lệnh thường dùng nhấtDùng để truy vấn dữ liệu (query data)Lấy và hiển thị dữ liệu từ một hay nhiều bảngLệnh SelectCú pháp :SELECT [ DISTINCT | ALL] [ * | têncột [AS tên mới] [,]FROM tênbảng [alias] [,][WHERE điềukiện ][GROUP BY danhsáchcột ] [HAVING điềukiện ][ORDER BY danhsáchcột ](*) Các ví dụ trong chương này sử dụng CSDL Quản lý Bán hàng , xem slide cuốiCâu truy vấn đơn giảnCâu lệnh Select FromVd: Hiển thị Họ, tên , phái của các nhân viênSelect Honv, Tennv, PhaiFrom NhanvienTương đương với phép chiếu trong ĐSQHHonv, Tennv, Phai (NHANVIEN)Hiển thị tất cả các cột , bằng sử dụng *Vd: Hiển thị danh sách các nhân viênSelect * From NhanvienCâu truy vấn đơn giảnSử dụng Distinct để loại bỏ các bộ trùng nhauVd: Danh sách nơi sinh của các nhân viênSelect DISTINCT noisinhFrom NhanvienCâu truy vấn đơn giảnHiển thị các cột tính toán (Calculated fields)Sử dụng hàm trong cột tính toánCác cột có thể đặt tên mới – aliasVd1: hiển thị chi tiết hóa đơn gồm mahd, masp, soluong, dongia và thành tiềnSelect mahd, masp, soluong,dongia, soluong*dongia AS thanhtien From chitiethoadonVd2: hiển thị họ tên, và năm sinh của nhân viênSelect Honv+Tennv as HT , year(ngaysinh) as NSFrom NhanvienLệnh Select - Mệnh đề WhereSử dụng để chọn một số bộChứa các biểu thức logic, xác định các bộ sẽ được chọn :Các cột nằm trong bảng có trong mệnh đề FromCác toán tử so sánh, toán tử logic And, Or, Not, tóan tử BetweenCác toán tử khác : Like, in, Lệnh Select - Mệnh đề WhereVd1: Hiển thị các mặt hàng có đơn giá >100Select Tensp , dongiaFrom SanphamWhere dongia > 100Vd2: Hiển thị các mặt hàng có đơn giá >100 hoặc đơn giá 100 or dongia 1000Truy vấn lồng - SubqueriesMột câu lệnh select (inner) được nhúng vào trong một câu lệnh select khác (outer)Kết quả của lệnh inner select, hay subselect được sử dụng trong lệnh outer select để xác định tập kết quảThường đặt trong mệnh đề Where, Having của lệnh outer selectCâu subselect cũng được sử dụng trong một số lệnh khác như INSERT, DELETE, UPDATETruy vấn lồng - SubqueriesCâu subSelect có thể trả về một giá trị đơn, hay một tableVd1: Select Masp, Tensp, DongiaFrom SanphamWhere Dongia > (Select Avg(Dongia) From Sanpham)Truy vấn lồng - SubqueriesSo sánh với một tập hợp, sử dụng IN/NOT IN, >ALL, >ANY (hay SOME)Xét lược đồ quan hệ:Nhanvien(Manv, Honv, Tennv, Phai, Diachi, Chucvu, Luong) Truy vấn lồng - SubqueriesVd2: hiển thị những nhân viên có lương lớn hơn lương của ít nhất một nhân viên có chức vụ là ‘trưởng phòng’Select T.Manv, T.ChucvuFrom Nhanvien As T, Nhanvien As SWhere T.Luong > S.Luong And S.Chucvu like ‘trưởng phòng’Dùng subQuery:Select ManvFrom NhanvienWhere Luong >Any (Select Luong From Nhanvien Where Chucvu like ‘trưởng phòng’)Truy vấn lồng - SubqueriesVd3: hiển thị những nhân viên có lương lớn hơn lương của tất cả các nhân viên có chức vụ là ‘trưởng phòng’Select ManvFrom NhanvienWhere Luong >All (Select Luong From Nhanvien Where Chucvu like ‘trưởng phòng’ )Vd4: hiển thị những nhân viên có bán hàng trong tháng 5Select Manv, Honv, TennvFrom NhanvienWhere Manv IN (Select Manv From Hoadon Where Month(NgaylapHD) = 5 )Truy vấn lồng - SubqueriesTóan tử EXISTS (NOT EXISTS)Kiểm tra kết quả của câu truy vấn con có rỗng hay khôngVd1 : hiển thị danh sách các nhân viên đã từng bán hàngSelect Manv, Honv, TennvFrom Nhanvien tWhere Exists (Select * From Hoadon Where Manv = t.Manv)Lệnh Select – truy vấn từ nhiều bảngTương tự phép kết trong ĐSQHNên tạo bí danh (alias) cho tên bảng để tránh gõ tên dài và làm truy vấn dễ đọc hơnNếu số bảng kết nối nhiều hơn 2, thì kết nối 2 bảng trước, sau đó kết nối nhóm này với bảng thứ baHai loại kết: inner join, outer joinLệnh Select – truy vấn từ nhiều bảngVd1: Hiển thị Mahd, TenKH, NgaylapHD của hóa đơn 10148Select Mahd, Tenkh, NgaylapHDFrom Hoadon As H INNER JOIN Khachhang As K ON H.Makh = K.MakhWhere Mahd = ‘10148’Hoặc :Select Mahd, Tenkh, NgaylapHDFrom Hoadon As H , Khachhang As K Where H.Makh = K.Makh and Mahd = ‘10148’Lệnh Select – truy vấn từ nhiều bảngVd2: Hiển thị tên khách hàng mua hàng trong tháng 2Select K.Makh, TenkhFrom Khachhang As K INNER JOIN Hoadon As H ON K.Makh = H.Makh Where Month(ngaylapHD) =2Vd3: Hiển thị tên khách hàng chưa từng mua hàngSelect K.Makh, TenkhFrom Khachhang As K LEFT JOIN Hoadon As H ON K.Makh = H.Makh Where Mahd IS NULLLệnh Select – truy vấn từ nhiều bảngVd4: Hiển thị tên nhân viên và chi tiết các hóa đơn do nhân viên đã lậpSelect Tennv, Mahd, Masp, Soluong, DongiaFrom (Nhanvien As N INNER JOIN Hoadon As H ON N.Manv= H.Manv ) INNER JOIN Chitiethoadon As C ON H.Mahd=C.MahdLệnh Select – toán tử UnionSử dụng toán tử Union để hợp tập dữ liệu của các câu lệnh Select vào quan hệ kết quảVd: hiển thị các thành phố có trong cả hai bảng Khachhang và NhanvienSelect Thanhpho From KhachhangUNIONSelect Noisinh From NhanvienSử dụng toán tử Except (phép hiệu), Intersect (phép giao) : trong SQLServer2005Lệnh InsertNhập dữ liệu vào bảngCú pháp 1 :INSERT INTO tênbảng [dscột]VALUES (danhsách giá trị)Thứ tự giá trị trong mệnh đề VALUES trùng với thứ tự các thuộc tính được nêu trong lệnh CREATE TABLEDùng [dscột] để chỉ rõ các cột được nhập liệuLệnh InsertVd1: nhập một bộ vào bảng SanphamInsert into SanphamValues(12, ‘Sữa bột’, ‘Kg’, 200, NULL)Vd2: nhập một bộ vào bảng SanphamInsert into Sanpham(Tensp, Donvitinh, Dongia)Values(‘Mì ăn liền’, ‘Thùng’, 100)Lệnh InsertCú pháp 2:Insert Into tênbảng [dscột]Select From WhereVd: giả sử có bảng HOADONLUU( MAHD,LOAIHD, MAKH, MANV, NGAYLAPHD, NGAYGNHANG, DIENGIAI )Insert Into HoadonLuuSelect * From HoadonWhere Year(Ngaylaphd) <2009Lệnh UpdateChỉnh sửa giá trị trong một/một số cột, của một hay nhiều bộCú pháp:UPDATE TênbảngSET col1 = giátri1 [, col2 = giátri2, ][WHERE điềukiện]Lệnh UpdateVd1: tăng giá của tất cả cácmặt hàng lên 10%Update SanphamSet Dongia = Dongia*1.1Vd2: nhân viên có mã 101 được bổ nhiệm làm ‘quản lý’ và lương mới là 310Update NhanvienSet Chucvu = ‘quảnlý’ , Luong = 310Where Manv = 101Lệnh DeleteXóa một hay nhiều bộ trong một bảngCú pháp:DELETE [FROM] tênbảng[WHERE điềukiện]Vd1: Xóa nhân viên có mã là 101Delete From NhanvienWhere Manv = ‘101’Lệnh DeleteVd2: Delete From NhanvienVd3:Delete From NhanvienWhere Manv NOT IN (Select Manv From Hoadon Where Year(Ngaylaphd)=2009 )Cascade Update & Cascade DeleteCác lệnh Insert, Update, Delete sẽ bị lỗi nếu vi phạm các Ràng buộc toàn vẹn đã cài đặtBị từ chối thực hiệnLệnh Update, Delete có thể dẫn đến sửa, xóa những bộ tham chiếu đến (cascade update, cascade delete)Khi thực hiện thao tác trên bảng chaVdDB quản lý bán hàng