1 / 40

Chương 3

Chương 3. SQL Structured Query Language Phần 1. Giới thiệu. SQL 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 1986 SQL là ngôn ngữ khai báo cấp cao

elewa
Download Presentation

Chương 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chương 3 SQLStructured Query LanguagePhần 1

  2. Giới thiệu • SQL 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 1986 • SQL 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 DBMS

  3. Giới thiệu • SQL 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ìn • DML (Data Manipulation Language) • Truy vấn dữ liệu • Các thao tác thêm, xóa , sửa dữ liệu • Kiểm soát các transaction • DCL (Data Control Language) • Phân quyền người dùng

  4. Giới thiệu • Trong một số DBMS, SQL được mở rộng để tăng khả năng lập trình thủ tục • T-SQL trong MS SQL Server cho phép • dùng cấu trúc điều khiển, biến cục bộ, cursor • Tạo các procedure, trigger

  5. Ngôn ngữ thao tác dữ liệu - DML • DML bao gồm các lệnh : SELECT, INSERT, UPDATE, DELETE • Lệnh Select là lệnh thường dùng nhất • Dù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ảng

  6. Lệnh Select • Cú 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ối

  7. Câu truy vấn đơn giản • Câu lệnh Select… From… Vd: Hiển thị Họ, tên , phái của các nhân viên Select Honv, Tennv, Phai From Nhanvien Tương đương với phép chiếu trong ĐSQH • Honv, 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ên Select * From Nhanvien

  8. Câu truy vấn đơn giản • Sử dụng Distinct để loại bỏ các bộ trùng nhau Vd: Danh sách nơi sinh của các nhân viên Select DISTINCT noisinh From Nhanvien

  9. Câu truy vấn đơn giản • Hiển thị các cột tính toán (Calculated fields) • Sử dụng hàm trong cột tính toán • Các cột có thể đặt tên mới – alias Vd1: hiển thị chi tiết hóa đơn gồm mahd, masp, soluong, dongia và thành tiền Select mahd, masp, soluong,dongia, soluong*dongia AS thanhtien From chitiethoadon Vd2: hiển thị họ tên, và năm sinh của nhân viên Select Honv+Tennv as HT , year(ngaysinh) as NS From Nhanvien

  10. Lệnh Select - Mệnh đề Where • Sử 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 đề From • Các toán tử so sánh, toán tử logic And, Or, Not, tóan tử Between • Các toán tử khác : Like, in, …

  11. Lệnh Select - Mệnh đề Where • Vd1: Hiển thị các mặt hàng có đơn giá >100 Select Tensp , dongia From Sanpham Where dongia > 100 • Vd2: Hiển thị các mặt hàng có đơn giá >100 hoặc đơn giá <50 Select Tensp , dongia From Sanpham Where dongia > 100 or dongia < 50 • Vd3: Hiển thị các mặt hàng có đơn giá trong khoảng từ 50 đến 100 Select Tensp , dongia From Sanpham Where dongia between 50 and 100

  12. Lệnh Select - Mệnh đề Where • Sử dụng toán tử Like/ Not Like trong biểu thức so sánh với chuỗi • Like khác với toán tử = • Sử dụng các ký tự thay thế %, _ Vd4: Hiển thị danh sách nhân viên có họ “Nguyễn” Select * From Nhanvien Where Honv like “Nguyễn%” • Vd5: hiển thị danh sách các nhân viên có địa chỉ ở đường Nguyễn Trãi

  13. Lệnh Select - Mệnh đề Where • Sử dụng IS NULL / IS NOT NULL để so sánh với giá trị NULL Vd: Hiển thị danh sách các Nhân viên chưa biết số điện thoại Select * From Nhanvien Where dienthoai IS NULL Vd: mệnh đề Where có thể chứa biểu thức cột select Honv, Tennv From Nhanvien where left(Honv,1) like ‘N'

  14. Lệnh Select - Mệnh đề Order By • Sắp xếp các bộ trong quan hệ kết quả theo giá trị của một / một số cột • Sử dụng ASC hay DESC để chỉ ra chiều sắp xếp (mặc định là ASC) • Vd Select * From Nhanvien Order by Ngaysinh ASC, Tennv DESC

  15. Lệnh Select – sử dụng các hàm thống kê • Còn gọi là Aggregate Functions • Hàm Count, Sum, Avg, Min, Max Vd: hiển thị tổng trị giá tất cả các hóa đơn Select sum(soluong*dongia) From Chitiethoadon Vd: Hiển thị đơn giá lớn nhất, nhỏ nhất và trung bình của các mặt hàng Select Max(dongia) , Min(dongia), AVG(dongia) From Sanpham

  16. Lệnh Select – sử dụng các hàm thống kê • Hàm count(tencot), count(*) và count(distinct) Vd1: cho biết số lượng nhân viên Select Count(Noisinh) From Nhanvien Select Count(Distinct Noisinh) From Nhanvien Select Count(*) From Nhanvien Vd2: Hiển thị tổng số hóa đơn , tổng trị giá các hóa đơn ?

  17. Lệnh Select – mệnh đề Group by • Gom nhóm các dòng dữ liệu và tạo ra một dòng chung cho mỗi nhóm • Hiển thị các tổng phụ (subtotal) Vd1: Hiển thị tổng trị giá của tất cả các hóa đơn Select sum(soluong*dongia) From Chitiethoadon Vd2: Hiển thị tổng trị giá của từng hóa đơn Select Mahd, maSp, sum(soluong*dongia) As TongTg From Chitiethoadon Group By Mahd

  18. Lệnh Select – mệnh đề Group by Select Mahd, sum(soluong*dongia) From Chitiethoadon Group By Mahd

  19. Lệnh Select – mệnh đề Group by • Chú ý : các cột xuất hiện trong mệnh đề Select phải có trong mệnh đề Group By, ngoại trừ các cột được tạo từ hàm thống kê. • Vd3: Select Manv, LoaiHD, count(Mahd) From Hoadon Group by Manv, LoaiHD

  20. Lệnh Select – mệnh đề Having • Đi sau mệnh đề Group by, được sử dụng để lọc các nhóm • Giống như mệnh đề Where : Chứa các biểu thức điều kiện Select Mahd, sum(soluong*dongia) From Chitiethoadon Group By Mahd Having sum(soluong*dongia) > 1000

  21. Truy vấn lồng - Subqueries • Mộ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 select • Câu subselect cũng được sử dụng trong một số lệnh khác như INSERT, DELETE, UPDATE

  22. Truy vấn lồng - Subqueries • Câu subSelect có thể trả về một giá trị đơn, hay một table Vd1: Select Masp, Tensp, Dongia From Sanpham Where Dongia > (Select Avg(Dongia) From Sanpham)

  23. Truy vấn lồng - Subqueries • So 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)

  24. Truy vấn lồng - Subqueries Vd2: 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.Chucvu From Nhanvien As T, Nhanvien As S Where T.Luong > S.Luong And S.Chucvu like ‘trưởng phòng’ Dùng subQuery: Select Manv From Nhanvien Where Luong >Any (Select Luong From Nhanvien Where Chucvu like ‘trưởng phòng’)

  25. Truy vấn lồng - Subqueries Vd3: 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 Manv From Nhanvien Where 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 5 Select Manv, Honv, Tennv From Nhanvien Where Manv IN (Select Manv From Hoadon Where Month(NgaylapHD) = 5 )

  26. Truy vấn lồng - Subqueries • Tóan tử EXISTS (NOT EXISTS) • Kiểm tra kết quả của câu truy vấn con có rỗng hay không Vd1 : hiển thị danh sách các nhân viên đã từng bán hàng Select Manv, Honv, Tennv From Nhanvien t Where Exists (Select * From Hoadon Where Manv = t.Manv)

  27. Lệnh Select – truy vấn từ nhiều bảng • Tương tự phép kết trong ĐSQH • Nê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ơn • Nế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ứ ba… • Hai loại kết: inner join, outer join

  28. Lệnh Select – truy vấn từ nhiều bảng • Vd1: Hiển thị Mahd, TenKH, NgaylapHD của hóa đơn 10148 Select Mahd, Tenkh, NgaylapHD From Hoadon As H INNER JOIN Khachhang As K ON H.Makh = K.Makh Where Mahd = ‘10148’ Hoặc : Select Mahd, Tenkh, NgaylapHD From Hoadon As H , Khachhang As K Where H.Makh = K.Makh and Mahd = ‘10148’

  29. Lệnh Select – truy vấn từ nhiều bảng • Vd2: Hiển thị tên khách hàng mua hàng trong tháng 2 Select K.Makh, Tenkh From Khachhang As K INNER JOIN Hoadon As H ON K.Makh = H.Makh Where Month(ngaylapHD) =2 • Vd3: Hiển thị tên khách hàng chưa từng mua hàng Select K.Makh, Tenkh From Khachhang As K LEFT JOIN Hoadon As H ON K.Makh = H.Makh Where Mahd IS NULL

  30. Lệnh Select – truy vấn từ nhiều bảng • Vd4: Hiển thị tên nhân viên và chi tiết các hóa đơn do nhân viên đã lập Select Tennv, Mahd, Masp, Soluong, Dongia From (Nhanvien As N INNER JOIN Hoadon As H ON N.Manv= H.Manv ) INNER JOIN Chitiethoadon As C ON H.Mahd=C.Mahd

  31. Lệnh Select – toán tử Union • Sử 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à Nhanvien Select Thanhpho From Khachhang UNION Select Thanhpho From Nhanvien • Sử dụng toán tử Except (phép hiệu), Intersect (phép giao) : trong SQLServer2005

  32. Lệnh Insert • Nhập dữ liệu vào bảng • Cú 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 TABLE • Dùng [dscột] để chỉ rõ các cột được nhập liệu

  33. Lệnh Insert • Vd1: nhập một bộ vào bảng Sanpham Insert into Sanpham Values(12, ‘Sữa bột’, ‘Kg’, 200, NULL) • Vd2: nhập một bộ vào bảng Sanpham Insert into Sanpham(Tensp, Donvitinh, Dongia) Values(‘Mì ăn liền’, ‘Thùng’, 100)

  34. Lệnh Insert • Cú pháp 2: Insert Into tênbảng [dscột] Select … From …Where… Vd: giả sử có bảng HOADONLUU( MAHD,LOAIHD, MAKH, MANV, NGAYLAPHD, NGAYGNHANG, DIENGIAI ) Insert Into HoadonLuu Select * From Hoadon Where Year(Ngaylaphd) <2009

  35. Lệnh Update • Chỉ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ảng SET col1 = giátri1 [, col2 = giátri2,… ] [WHERE điềukiện]

  36. Lệnh Update • Vd1: tăng giá của tất cả cácmặt hàng lên 10% Update Sanpham Set Dongia = Dongia*1.1 • Vd2: nhân viên có mã 101 được bổ nhiệm làm ‘quản lý’ và lương mới là 310 Update Nhanvien Set Chucvu = ‘quảnlý’ , Luong = 310 Where Manv = 101

  37. Lệnh Delete • Xóa một hay nhiều bộ trong một bảng • Cú pháp: DELETE [FROM] tênbảng [WHERE điềukiện] • Vd1: Xóa nhân viên có mã là 101 Delete From Nhanvien Where Manv = ‘101’

  38. Lệnh Delete • Vd2: Delete From Nhanvien • Vd3: Delete From Nhanvien Where Manv NOT IN (Select Manv From Hoadon Where Year(Ngaylaphd)=2009 )

  39. Cascade Update & Cascade Delete • Cá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 đặt • Bị từ chối thực hiện • Lệ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 cha • Vd

  40. DB quản lý bán hàng

More Related