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 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
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
Giới thiệu
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
Trong một số DBMS, SQL được mở rộng để
Ngôn ngữ thao tác dữ liệu - DML
UPDATE, DELETE
DML bao gồm các lệnh : SELECT, INSERT,
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
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
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
(cid:0)
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
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
Lệnh Select - Mệnh đề Where
đượ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, …
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ẽ
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
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
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'
Lệnh Select - Mệnh đề Order By
giá trị của một / một số cột
Sắp xếp các bộ trong quan hệ kết quả theo
xếp (mặc định là ASC)
Sử dụng ASC hay DESC để chỉ ra chiều sắp
Vd Select * From Nhanvien Order by Ngaysinh ASC, Tennv DESC
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
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 ?
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
Lệnh Select – mệnh đề Group by
TONGTGÙ
MAÕ HÑ
MAÕ SP
SOÁ LÖÔÏNG
ÑÔN GIAÙ
MAÕ HÑ 10148
3
20.00
2.20
10148
803
10148
4
30.00
16.50
10148
9
20.00
13.20
10150
2
22.00
44.00
10150
1133
10150
4
10.00
16.50
10156
68.75
10156
8
20.00
68.75
Select Mahd, sum(soluong*dongia) From Chitiethoadon Group By Mahd
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
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
Truy vấn lồng - Subqueries
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
Một câu lệnh select (inner) được nhúng vào
lệnh khác như INSERT, DELETE, UPDATE
Câu subselect cũng được sử dụng trong một số
Truy vấn lồng - Subqueries
hay một table
Vd1: Select Masp, Tensp, Dongia From Sanpham Where Dongia > (Select Avg(Dongia) From
Câu subSelect có thể trả về một giá trị đơn,
Sanpham)
Truy vấn lồng - Subqueries
Xét lược đồ quan hệ: Nhanvien(Manv, Honv, Tennv, Phai, Diachi,
Chucvu, Luong)
So sánh với một tập hợp, sử dụng IN/NOT IN, >ALL, >ANY (hay SOME)
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
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’)
một nhân viên có chức vụ là ‘trưởng phòng’
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 )
Truy vấn lồng - Subqueries
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)
Tóan tử EXISTS (NOT EXISTS)
Lệnh Select – truy vấn từ nhiều bảng
gõ tên dài và làm truy vấn dễ đọc hơn
Tương tự phép kết trong ĐSQH Nên tạo bí danh (alias) cho tên bảng để tránh
bảng trước, sau đó kết nối nhóm này với bảng thứ ba…
Nếu số bảng kết nối nhiều hơn 2, thì kết nối 2
Hai loại kết: inner join, outer join
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’
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
Lệnh Select – truy vấn từ nhiều bảng
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
Vd4: Hiển thị tên nhân viên và chi tiết các
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 Noisinh From Nhanvien
Sử dụng toán tử Except (phép hiệu), Intersect (phép giao) : trong
SQLServer2005
Lệnh Insert
thứ tự các thuộc tính được nêu trong lệnh CREATE TABLE
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
Dùng [dscột] để chỉ rõ các cột được nhập liệu
Lệnh Insert
Sanpham(Tensp, Donvitinh, Dongia)
Values(‘Mì ăn liền’, ‘Thùng’, 100)
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
Lệnh Insert
Insert Into HoadonLuu Select * From Hoadon Where Year(Ngaylaphd) <2009
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 )
Lệnh Update
một hay nhiều bộ
Chỉnh sửa giá trị trong một/một số cột, của
Cú pháp: UPDATE Tênbảng SET col1 = giátri1 [, col2 = giátri2,… ] [WHERE điềukiện]
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
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’
Lệnh Delete
Vd2: Delete From Nhanvien
Vd3: Delete From Nhanvien Where Manv NOT IN
(Select Manv From Hoadon
Where Year(Ngaylaphd)=2009 )
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
