ThS. Nguyễn Nghiệm 0913.745.789 nghiemn@fpt.edu.vn songlong2k@gmail.com
Tạo, xóa, sao lưu, phục hồi CSDL Thiết kế bảng Tìm hiểu CSDL Music Store Truy vấn dữ liệu
Sắp xếp Lọc dữ liệu Kết nối nhiều bảng Thống kê dữ liệu
Cơ sở lưu trữ và các tính năng quản lý
dữ liệu như thao tác, truy vấn… được các ứng dụng (viết bằng Java, C++, PHP, C#...) sử dụng. Các thành phần cơ bản trong CSDL gồm bảng (table), khung nhìn (view), thủ tục lưu (stored procedure), hàm (function), trigger. CSDL được quản lý bởi hệ quản trị CSDL quan hệ SQL Server MySQL Server Oracle Sysbase
Bảng (table) là đơn vị chứa dữ liệu duy nhất trong CSDL, được định nghĩa gồm nhiều cột (column), mỗi cột có kiểu dữ liệu (data type) và ràng buộc (constraints) riêng. Dữ liệu lưu trong bảng gồm nhiều hàng (row/record), mỗi hàng lưu thông tin của mỗi thực thể (entity) Khóa chính (primary key) là một hoặc nhiều cột dùng để xác định duy nhất mỗi thực thể trong bảng
Bảng: MonHoc Khóa chính: MaMonHoc
New Table: thêm mới Table Design: chỉnh sửa cấu trúc Table Select Top 1000 Rows: xem dữ
liệu Edit Top 200 Rows: xem và sửa dữ liệu Rename: đổi tên Table Delete: xóa table
Cấu trúc Table
Kiểu dữ liệu cột
Thông số cột đang làm việc
Cascade: Xóa Genre -> Xóa Albums No Action: không thể xóa Genre nếu có Album Set Null: Xóa Genre -> Album có giá trị null (con phải
cho phép null) Set Default: Xóa cha -> con có giá trị mặc định (con phải định nghĩa giá trị mặc định)
Albums: lưu thông tin
về album nhạc AlbumId: mã album GenreId: mã loại album Artist: nghệ sỹ Title: tựa Price: giá AlbumArtUrl: hình ảnh
Artists: lưu thông tin
về nghệ sỹ trình bày ArtistId: mã nghệ sỹ Name: tên nghệ sỹ Genres: lưu thông tin
về chủng loại album GenreId: mã loại Name: tên chủng loại Description: mô tả về chủng loại
Orders: lưu thông tin đơn đặt hàng OrderId: mã đơn hàng OrderDate: ngày đặt hàng Username: mã người đặt hàng FirstName: tên người nhận LastName: họ người nhận Address: địa chỉ nhận City: thành phố nhận State: tiểu bang nhận PostalCode: mã bưu điện Country: quốc gia nhận Phone: điện thoại liên hệ Email: email liên hệ Total: tổng tiền
Bảng OrderDetails được
sử dụng để lưu các mặt hàng được đặt trên mỗi đơn hàng. OrderDetailId: mã chi tiết OrderId: mã đơn hàng chứa chi tiết AlbumId: mã album Quantity: số lượng UnitPrice: đơn giá tại thời điểm bán
Carts được sử dụng để
lưu các mặt hàng đã chọn nhưng chưa mua. RecordId: mã bản ghi CartId: mã giỏ hàng – duy nhất cho mỗi người AlbumId: mặt hàng được chọn Count: số lượng album DateCreated: thời điểm tạo giỏ hàng
Cú pháp
SELECT <danh sách cột> FROM <tên bảng> <danh sách cột>
Liệt kê các cột C1, C2, C3 SELECT C1, C2, C3 FROM T
Đặt bí danh cho cột C2 là NAME: SELECT C1, C2 AS ‘Họ và Tên’, C3 FROM T Sử dụng DISTINCT để loại bỏ các hàng trùng dữ liệu SELECT DISTINCT C1, C2, C3 FROM T
Sử dụng TOP để hạn chế số lượng bản ghi SELECT TOP (10) C1, C2, C3 FROM T
Sử dụng dấu sao (*) để liệt kê tất cả các cột SELECT * FROM T
Sử dụng mệnh đề ORDER BY để sắp xếp tập kết quả
truy vấn. Sử dụng kèm với DESC và ASC để qui định hướng sắp xếp Sắp xếp giảm (DESC) SELECT * FROM Lop ORDER BY NgayKG DESC Sắp xếp tăng (ASC-mặc định)
SELECT * FROM Lop ORDER BY HocPhi ASC SELECT * FROM Lop ORDER BY NgayKG Sắp xếp nhiều cột
SELECT * FROM Lop ORDER BY NgayKG ASC, HocPhi DESC
Sử
dụng mệnh đề WHERE để lọc dữ liệu kết quả truy vấn theo điều kiện SELECT * FROM T WHERE <điều kiện>
Các tình huống sử dụng mệnh đề WHERE
… WHERE C1=‘ABC’ Các bản ghi có giá trị của C1 là ‘ABC’
… WHERE C2 > 70 Các bản ghi có giá trị của C2 lớn hơn 70
… WHERE C2 = ‘X’ AND C3 <= ’01/31/1995’ Các bản ghi có giá trị của C2 là ‘X’ và C3 trước 31-032010
Logic: AND, OR, NOT
Số học: +, -, *, /, mod
TOÁN TỬ
Đặc biệt: [NOT] IN, [NOT] LIKE, [NOT] BETWEEN, IS [NOT]NULL
So sánh: =, <>, >=, <=
Nhóm toán tử số học:
…WHERE 4 + C2 <= 300 Nhóm toán tử lôgic:
…WHERE NOT (C2 >= 10 AND C2 <= 20) Nhóm toán tử so sánh:
…WHERE (C2 <> 10 OR C3 <= ’12/31/2011’)
Nhóm toán tử đặc biệt:
LIKE <chuỗi mẫu> BETWEEN <min> AND <max> IN (tập hợp) IS NULL IS NOT NULL Ví dụ:
SELECT * FROM SinhVien WHERE HoTen LIKE ‘%Hoa’
[NOT] LIKE <mẫu tìm kiếm> dùng để tìm kiếm tương tự. Các ký tự đại diện sử dụng cùng với LIKE gồm %: đại diện nhóm ký tự bất kỳ _: đại diện 1 ký tự bất kỳ [xyz]: một ký tự thuộc nhóm x,y hoặc z [^xyz]: một ký tự không thuộc nhóm x, y và z
Các ví dụ khác Cột C1 có chứa chuỗi ‘ABC’ ? …WHERE C1 LIKE ‘%ABC%’
Cột C1 có kết thúc bởi chuỗi ‘ABC’ ? …WHERE C1 LIKE ‘%ABC’
Cột C1 có chứa một trong các ký tự ‘A’, ‘B’ hay ‘C’ ? …WHERE C1 LIKE ‘%[ABC]%’
Cột C1 có chứa chuỗi ‘ABxC’, với x là ký tự bất kỳ ? …WHERE C1 LIKE ‘%AB_C%’
[NOT] IN (tập hợp hoặc truy vấn con) dùng để tìm kiếm trong tập hợp. …WHERE C1 IN (‘SV001’, ‘SV009’, ‘SV075’) …WHERE C2 NOT IN (100, 55, 65, 18, 22, 54) …WHERE C3 IN (’12/31/2000’, ’12/31/2005’, ’12/31/2010’) …WHERE C4 IN (SELECT TOP 10 C FROM T2) [NOT] BETWEEN <ngày/số> AND <ngày/số> dùng để tìm theo phạm vi giá trị. …WHERE C2 BETWEEN ’12/31/2000’ AND ’12/31/2010’ ...WHERE C1 NOT BETWEEN 20 AND 3000 IS [NOT] NULL dùng để kiểm tra giá trị null …WHERE C1 IS NULL …WHERE C2 IS NOT NULL
Các câu lệnh SQL có thể lồng nhau. Câu lệnh SQL bên trong câu lệnh SQL khác được gọi là truy vấn con.
Một số trường hợp thường gặp SELECT * FROM SinhVien WHERE MaLH IN (SELECT MaLH FROM LopHoc WHERE Year(NgayKG)=2000) SELECT *, (SELECT COUNT(*) FROM SinhVien sv WHERE lh.MaLH=sv.MaLH) AS SoHV FROM LopHoc lh
SELECT * FROM SinhVien WHERE DiemTB > (SELECT AVG(DiemTB) FROM SinhVien) SELECT *, (SELECT COUNT(MaSV) FROM SinhVien sv WHERE sv.MaLH=lh.MaLH) AS SoLuong FROM LopHoc lh
HÀM THƯỜNG DÙNG
TỔNG HỢP
NGÀY THÁNG
XỬ LÝ CHUỖI
KHÁC
SUM (biểu thức) dùng để tính tổng SELECT SUM (C2) AS ‘Tong’ FROM T
MIN (biểu thức) dùng để lấy giá trị nhỏ nhất SELECT MIN (C2) AS ‘NhoNhat’ FROM T
MAX (biểu thức) dùng để lấy giá trị lớn nhất SELECT MAX (C2) AS ‘LonNhat’ FROM T
AVG (biểu thức) dùng để tính giá trị trung bình SELECT AVG (C2) AS ‘TrungBinh’ FROM T
COUNT (cột hoặc *) dùng để đếm số bản ghi SELECT COUNT (*) AS ‘SoLuong’ FROM T
UPPER (chuỗi) đổi sang chữ hoa SELECT UPPER (C1) FROM T
LOWER (chuỗi) đổi sang chữ thường SELECT C3 FROM T WHERE LOWER (C1)=‘xyz’
TRIM (chuỗi) cắt bỏ ký tự trắng 2 đầu chuỗi SELECT TRIM (C1) FROM T
CONCAT (‘chuỗi 1’, ‘chuỗi 2’) ghép 2 chuỗi SELECT CONCAT (‘SV:’, C1) AS MaSV FROM T
GETDATE () lấy ngày hiện tại SELECT * FROM T WHERE C3 < GetDate ()
Trích một phần của ngày-giờ
DATEPART (part, NgayGio) lấy năm-tháng-ngày Các hàm lấy ngày, tháng, năm, giờ, phút, giây
YEAR (ngày) lấy năm MONTH (ngày) lấy tháng HOUR (ngày) lấy giờ MINUTE (ngày) lấy phút SECOND (ngày) lấy giây SELECT SUM (C2) FROM T WHERE Year (C3)=2010
ISNULL (<biểu thức>, <giá trị thay thế>) dùng để
chuyển giá trị null sang một giá trị khác. SELECT C1, ISNULL (C2, 3) AS CC2 FROM T Lệnh CASE dùng để tạo giá trị phù hợp với các điều
kiện đặt ra. CASE WHEN <ĐK> THEN <GIA TRỊ>
WHEN <ĐK> THEN <GIA TRỊ> ELSE <GIA TRỊ> END
EXISTS (Câu lệnh SELECT) kiểm tra xem có bản ghi
nào được chọn hay không
SELECT NgaySinh AS ‘NGÀY SINH', Diem AS ‘’ĐIỂM, CASE WHEN Diem<5 THEN 'Rớt‘ ELSE ‘Đậu‘ END AS ‘KẾT QUẢ’ FROM SinhVien; SELECT TOP (30) CONCAT(MaSV, ' (', MaLH, ') ') AS 'MÃ SV', UPPER(HoTen) AS ‘HỌ VÀ TÊN', Diem AS ‘ĐIỂM', CASE WHEN Diem<5 THEN 'Yếu‘ WHEN Diem<7.5 THEN 'Trung Bình‘ ELSE 'Giỏi‘ END AS ‘XẾP LOẠI', NgaySinh AS 'NGÀY SINH' FROM SinhVien ORDER BY Diem DESC
Yêu cầu:
Hiển thị danh sách tên sinh viên và tên lớp. Giải pháp:
Kết nối bảng Lop và bảng SinhVien lại với nhau sau đó lấy lấy Lop.TenLop và SinhVien.Ten Ví dụ:
SELECT SinhVien.Ten, Lop.TenLop FROM SinhVien INNER JOIN Lop ON Lop.MaLop=SinhVien.MaLop Khái niệm
INNER JOIN là loại kết nối SinhVien gọi là bảng trái Lop gọi là bảng phải Lop.MaLop=SinhVien.MaLop là cơ sở kết nối
[INNER] JOIN: kết nối trong
Lấy các bản ghi của cả 2 bảng (Trái và Phải) có chung giá trị của cột kết nối. LEFT [OUTER] JOIN: kết nối ngoài, bên trái
Lấy tất cả các bản ghi của bảng trái kết. RIGHT [OUTER] JOIN: kết nối ngoài, bên phải
Lấy tất cả các bản ghi của bảng phải. FULL [OUTER] JOIN: kết nối ngoài, cả 2 bên
Kết hợp của LEFT JOIN và RIGHT JOIN
UNION được sử dụng để kết nối các tập dữ liệu
cùng cấu trúc.
Thống kê là tổng hợp số liệu theo từng nhóm bản
ghi. Nhóm Thông tin tổng hợp Ví dụ:
Đếm sinh viên theo lớp. Điểm thấp nhất, cao nhất, trung bình của mỗi lớp Doanh số bán hàng của các quí Doanh số bán hàng của từng nhà cung cấp
Sử dụng GROUP BY kết hợp với các hàm tổng
hợp số liệu (SUM, COUNT, MIN, MAX, AVG) để thống kê theo nhóm Tổng hợp số liệu theo nhóm
Nhóm
Kết hợp với HAVING để lọc nhóm Điều kiện này dùng để lọc các bản ghi trước khi phân nhóm
Điều kiện này dùng để lọc nhóm
Ví dụ sau đến sinh viên theo năm sinh
SUM(), COUNT(), MIN() ,MAX (), AVG()