Ví dụ: lấy ra thông tin của các lớp thuộc khoa công nghệ thông tin (mã CNTT), nông học (mã NH) và chăn nuôi thú y (mã CNTY). 0 5. Truy vấn dữ liệu từ nhiều bảng
1
Ví dụ: Hiển thị thông tin về các sinh viên với các kết quả học tập của họ. Khi hiển thị cột KetQua đổi tên thành cột DiemTB Các hệ quản trị cơ sở dữ liệu quan hệ thương mại hiện có như Oracle, SQL Server, Informix, DB2,... đều chọn SQL làm ngôn ngữ cho sản phẩm của mình. SQL cài đặt trong các hệ quản trị CSDL thương mại có một số khác biệt so với SQL do ANSI/ISO đề xuất. Các câu lệnh SQL cung cấp có thể được nhúng vào trong các ngôn ngữ lập trình nhằm xây dựng các ứng dụng tương tác với cơ sở dữ liệu.4 a. Các thành phần Ngôn ngữ định nghĩa dữ liệu (DDL) Định nghĩa/thay đổi/ xóa lược đồ quan hệ Định nghĩa khung nhìn Đặc tả quyền truy nhập Đặc tả ràng buộc toàn vẹn Ngôn ngữ thao tác dữ liệu (DML) Thêm/xóa/sửa/truy vấn các bộ giá trị trong quan hệ Ngôn ngữ điều khiển giao dịch Đặc tả sự bắt đầu và kết thúc giao dịch Điều khiển tương tranh 5 b. Các dạng quan hệ Bảng cơ sở Kết quả truy vấn Khung nhìn Quan hệ được lưu trữ vật lý trong csdl Kết quả truy vấn Kết quả truy vấn trên các quan hệ là một quan hệ. Khung nhìn Quan hệ ảo được định nghĩa bởi một biểu thức truy vấn 6 2. Lệnh định nghĩa dữ liệu 2.1. Tạo csdl, xóa csdl 2.2. Tạo bảng 2.3. Sửa đổi cấu trúc bảng 2.4. Xoá bảng 2.5. Tạo khung nhìn 7 2.1. Tạo CSDL, xóa CSDL Tạo csdl CREATE DATABASE <tên cơ sở dữ liệu> Xóa csdl DROP DATABASE <tên cơ sở dữ liệu> 8 2.2. Tạo bảng Cú pháp CREATE TABLE <Tên bảng> (<Tên cột> <Kiểu dữ liệu> [NOT NULL],… [CONSTRAINT <Tên ràng buộc> <Kiểu ràng buộc>]) Ví dụ: Tạo bảng về khách hàng có các trường là mã khách hàng, tên khách hàng, địa chỉ , số điện thoại và khóa chính là mã khách hàng. CREATE TABLE KHACH_HANG (MaKH char(5) NOT NULL, TenCongTy varchar(40), DiaChi varchar (60), DienThoaiCD char(7), CONSTRAINT khoa_chinh PRIMARY KEY (MaKH)) 9 2.3. Sửa đổi cấu trúc của bảng 10 2.3. Sửa đổi cấu trúc của bảng(t) 11 2.4. Xóa bảng Cú pháp DROP TABLE <Tên bảng> Ví dụ: Xóa bảng KHACH_HANG DROP TABLE KHACH_HANG 12 2.5. Tạo khung nhìn Cú pháp: CREAT VIEW <tên khung nhìn> [(danh sách tên cột)] AS <Câu truy vấn> Ví dụ Bảng cơ sở: NHAN_VIEN(MaNV, TenNV, DiaChi, MaPhong, Luong, NamLenLuong, DanhGia) PHONG_BAN(MaPhong, TenPhong, NguoiQuanLy) 13 2.5. Tạo khung nhìn (tiếp) Khung nhìn: phục vụ cho đối tượng là trưởng phòng của phòng kỹ thuật CREAT VIEW KT(TênNV, Đchỉ, Lương, NămLênlương, Đánh giá) AS (SELECT TenNV, DiaChi, MaPhong, Luong, NamLenLuong, DanhGia FROM NHAN_VIEN WHERE MaPhong IN (SELECT MaPhong FROM PHONG_BAN WHERE TenPhong = ‘Kỹ thuật’ )) 14 3. Lệnh cập nhật dữ liệu 3.1. Thêm một bộ giá trị 3.2. Xóa bộ giá trị 3.3. Thay đổi thuộc tính các bộ 15 Thêm bộ giá trị Cú pháp: INSERT INTO <Tên bảng> [(danh sách tên cột)] { VALUES (các giá trị)| <Câu truy vấn>} Ví dụ: Thêm một bộ giá trị vào bảng NHA_CUNG_CAP. INSERT INTO NHA_CUNG_CAP (MaNCC, TenCongTy, DiaChi) VALUES ('S1', 'Hải Hà', 'Hà Nội') 16 Xóa bộ giá trị Cú pháp: DELETE FROM {<Tên bảng| Tên khung nhìn>} [WHERE <Biểu thức điều kiện>] Ví dụ: Xóa các bộ có mã nhà cung cấp = S1 tại hai bảng CUNG_UNG và NHA_CUNG_CAP DELETE * FROM CUNG_UNG WHERE MaNCC = 'S1' DELETE * FROM NHA_CUNG_CAP 17 Thay đổi giá trị thuộc tính các bộ Cú pháp: UPDATE <Tên bảng> SET <Tên cột > = <biểu thức> [WHERE <Biểu thức điều kiện>] Ví dụ: Thay đổi thuộc tính tên của bộ giá trị có MaHang là = P1 trong bảng HANG_HOA. UPDATE Items SET TenHang = 'Bánh ngọt' WHERE MaHang= 'P1' 18 4. Lệnh truy vấn dữ liệu 4.1. Mệnh đề truy vấn tổng quát 4.2. Câu truy vấn lồng nhau 19 Mệnh đề truy vấn tổng quát SELECT[DISTINCT]<danh sách cột>|*|<biểu thức số học> FROM<danh sách tên bảng>|<danh sách các Khung nhìn> [WHERE <biểu thức điều kiện>] [GROUP BY <danh sách tên cột>] [HAVING <biểu thức điều kiện>] [ORDER BY <danh sách tên cột>|<biểu thức> [ASC|DESC]] [UNION|INTERSECT|MINUS<Câu truy vấn>] 20 a. Mệnh đề SELECT Mệnh đề SELECT tương ứng với phép chiếu trong đại số quan hệ, được sử dụng để liệt kê các thuộc tính mong muốn Mênh đề SELECT cho phép: Lựa chọn một/nhiều/tất cả (*) các thuộc tính Ví dụ: Hiển thị tất cả thông tin của nhân viên trong bảng NHAN_VIEN SELECT * FROM NHAN_VIEN Lấy các bộ giá trị không trùng nhau (DISTINCT) Ví dụ: Cho biết họ của các nhân viên SELECT DISTINCT Ho FROM NHAN_VIEN 21 Mệnh đề SELECT(t) Lấy kết quả từ các biểu thức số học (phép tính +,-,*,/) của hằng hoặc các thuộc tính Ví dụ: Cho biết danh mục mặt hàng và giá của mặt hàng đó (tính theo việt nam đồng): SELECT MAT_HANG.TenHang,CUNG_UNG.Gia *16.02 FROM MAT_HANG, CUNG_UNG WHERE MAT_HANG.MaHang = CUNG_UNG.MaHang Kết quả của biểu thức số học là NULL nếu một giá trị đầu vào là NULL. 22 SQL sử dụng cách viết <tên quan hệ> SQL sử dụng cách viết <tên quan hệ>.<tên thuộc tính> để che dấu tính lập lờ trong trường hợp tên các thuộc tính trong các quan hệ trùng nhau. SQL sử dụng các phép nối logic NOT, AND, OR. Toán hạng của các phép nối logic có thể là các biểu thức chứa các toán tử so sánh >, >=, <>, <, <=. Toán tử BETWEEN được dùng để chỉ các giá trị nằm giữa các khoảng giá trị. 23 b. Mệnh đề FROM Mệnh đề FROM tương ứng với phép tích Đề các của các quan hệ được xét. Ví dụ:Tìm giá mua vào của các mặt hàng thuộc loại máy tính xách tay: SELECT MAT_HANG.TenHang, UNG_UNG.Gia*16.02 FROM CUNG_UNG, NHOM_HANG, MAT_HANG WHERE (MAT_HANG.MaHang=CUNG_UNG.MaHang) AND (NHOM_HANG.MaNhom = AT_HANG.MaNhom) AND (TenNhom='laptop')) 24 c. Mệnh đề WHERE Mệnh đề WHERE tương ứng chọn dựa trên các thuộc tính của các quan hệ xuất hiện sau FROM. Ví dụ: Tìm các mặt hàng có số lượng đặt hàng lớn hơn 1 trăm? SELECT MAT_HANG.TenHang, CHI_TIET_DH.SoLuong FROM CHI_TIET_DH, MAT_HANG WHERE (MAT_HANG.MaHang = CHI_TIET_DH.MaHang) AND (SoLuong>100) 25 Mệnh đề WHERE (t) Kết quả của biểu thức điều kiện là TRUE hoặc FALSE Nếu 1 trong những đầu vào của biểu thức điều kiện có giá trị NULL thì kết quả trả về là FALSE WHERE[NOT]<biểu thức> <phép so sánh> <biểuthức> WHERE[NOT]<biểu thức logic> WHERE[NOT]<biểu thức logic> {AND|OR}[NOT]<biểu thức logic> WHERE[NOT]<tên cột> [NOT] LIKE <xâu ký tự mẫu> 26 Mệnh đề WHERE(t) WHERE[NOT]<biểu thức> [NOT] IN ({danh sách | câu truy vấn}) WHERE[NOT] EXISTS (<câu truy vấn con>) WHERE[NOT]<biểu thức><phép so sánh> {SOME|ANY|ALL (Câu truy vấn con)} WHERE <cột> IS NULL 27 Các phép đổi tên Mệnh đề AS cho phép đổi tên các bảng hoặc các cột <tên cũ> AS <tên mới> Đổi tên cột: Các bảng trong mệnh đề FROM cùng tên Sử dụng biểu thức số học trong câu lệnh SELECT Thay đổi tên cột trong bảng kết quả Ví dụ: Đổi tên cột dữ liệu hiển thị SELECT MAT_HANG.TenHang, CUNG_UNG.Gia*16.02 AS GiaSanPham FROM CUNG_UNG, NHOM_HANG, MAT_HANG WHERE ((MAT_HANG.MaHang=CUNG_UNG.MaHang) AND (NHOM_HANG.MaNhom = MAT_HANG.MaNhom) AND (TenNhom='laptop')) 28 Phép đổi tên (t) Đổi tên bảng So sánh các bộ (dòng) trong cùng một bảng Ví dụ: Lấy tên của các hãng cung ứng có cung ứng các sản phẩm màn hình LCD + TV (mã MH013) rẻ hơn hãng hula (mã là CHULA). SELECT T.MaNCC FROM CUNG_UNG AS T, CUNG_UNG AS S WHERE T.MaHang='MH013' AND T.Gia < S.Gia AND S.MaNCC = 'CHULA’ AND S.MaHang = ‘MH013’ 29 Các phép toán trên chuỗi Toán tử LIKE và NOT LIKE trong mệnh đề WHERE cho phép đối sánh giá trị của một cột với một mẫu khi tìm kiếm. Mẫu có thể được biểu diễn bởi các ký tự thay thế: Ký tự (%): Tương ứng với một chuỗi con bất kỳ. Ký tự (_): Tương ứng với một ký tự bất kỳ. Nếu trong xâu mẫu có chứa các ký tự thay thế (%, _, \) thì đặt ký tự \ trước các ký tự đó 30 Các phép toán trên chuỗi (t) Ví dụ: Mẫu ‘% Nam An %’: Tương ứng với các xâu ký tự có chứa xâu con là ‘Nam An’. ‘__%’: Tương ứng với các xâu ký tự có ít nhất hai ký tự. ‘20\%%’: Tương ứng với các xâu ký tự bắt đầu bằng 20% Ví dụ: Tìm tên chính xác của nhân viên tiếp xúc phía khách hàng có chứa xâu ‘Hoa’. SELECT NguoiLL FROM KHACH_HANG WHERE NguoiLL LIKE ‘%Hoa%’ 31 Các hàm thư viện Hàm thư viện lấy một tập các giá trị làm đầu vào và trả kết quả là một giá trị đơn. Các hàm tính gộp: Tính trung bình: AVG([DISTINCT]<cột có kiểu dữ liệu số>) Tính tổng SUM([DISTINCT]<cột có kiểu dữ liệu số>) Tính Min, Max Min(<[DISTINCT]><cột > Đếm COUNT([DISTINCT]<cột>|*) 32 Các hàm thư viện khác(t) Tất cả các hàm tính gộp (trừ Count(*) bỏ qua các giá trị đầu vào NULL COUNT() = 0 Ví dụ: Kiểm kê lượng hàng hoá theo loại mặt hàng còn trong kho SELECT MaNhom, Sum(TonKho) FROM MAT_HANG GROUP BY MaNhom 33 d. Mệnh đề GROUP BY và HAVING Mệnh đề GROUP BY nhóm các bộ có cùng giá trị trên các thuộc tính nào đó Ví dụ: In danh sách loại hàng hóa cùng lượng hàng hoá theo loại mặt hàng còn trong kho với điều kiện lượng hàng lớn hơn 100. SELECT MaNhom, Sum(TonKho) FROM MAT_HANG GROUP BY MaNhom HAVING Sum(TonKho) > 100 Mệnh đề HAVING xuất hiện sau khi tạo nhóm, đưa ra điều kiện cho nhóm. 34 d.Mệnh dề GROUP BY và HAVING(t) Nếu WHERE và HAVING cùng ở trong một câu truy vấn thì: Biểu thức điều kiện trong WHERE sẽ được thực hiện trước. Các bộ thỏa mãn điều kiện trong WHERE sẽ được nhóm vào bởi GROUPBY. Mệnh đề HAVING (nếu có) sẽ được áp dụng trên mỗi nhóm. Các nhóm không thỏa mãn mệnh đề HAVING sẽ bị xóa bỏ. 35 e. Mệnh đề ORDER BY Mệnh đề ORDER BY cho phép trình bày kết quả câu truy vấn theo thứ tự. Mặc định liệt kê theo thứ tự tăng Ví dụ: Đưa ra danh sách họ tên nhân viên với mức lương từ cao tới thấp. SELECT Ho, Ten, Luong FROM NHAN_VIEN ORDER BY Luong DESC 36 f. Tìm kiếm nhờ các phép toán tập hợp Phép UNION tương ứng với phéphợp trong đại số quan hệ. Ví dụ: Tìm các hãng là đối tác của siêu thị (khách hàng hoặc nhà cung cấp) (SELECT TenCongTy FROM KHACH_HANG) UNION FROM NHA_CUNG_CAP) 37 Tìm kiếm nhờ các phép toán tập hợp(t) Phép INTERSECT tương ứng với phép giao trong đại số quan hệ Ví dụ:Tìm các hãng vừa là khách hàng vừa là nhà cung cấp cho siêu thị. (SELECT TenCongTy FROM KHACH_HANG) INTERSECT FROM NHA_CUNG_CAP) 38 Tìm kiếm nhờ phép toán tập hợp(t) Phép MINUS tương ứng với phép trừ trong đại số quan hệ Ví dụ:Tìm các hãng chỉ là khách hàng (không là nhà cung cấp) của siêu thị. (SELECT MaKH FROM KHACH_HANG) MINUS FROM CUNG_UNG 39 4.2. Các câu truy vấn lồng nhau Câu truy vấn lồng là một câu truy vấn có chứa câu truy vấn con. Câu truy vấn con là một biểu thức truy vấn (SELECT-FROM-WHERE) lồng trong một truy vấn khác như: SELECT INSERT..INTO UPDATE DELETE Câu truy vấn con khác 40 Các câu truy vấn lồng nhau(t) Cú pháp tạo truy vấn con i) <biểu thức> [NOT] IN (<câu truy vấn con>) ii) [NOT] EXISTS (<câu truy vấn con>) iii) <biểu thức><phép so sánh> {SOME|ANY|ALL} (<câu truy vấn con>) 41 Mệnh đề WHERE WHERE[NOT]<biểu thức> [NOT] IN ({danh sách | câu truy vấn con}) WHERE[NOT] EXISTS (<câu truy vấn con>) WHERE[NOT]<biểu thức><phép so sánh> {SOME|ANY|ALL}(<câu truy vấn con>) 42 a. <biểu thức> [NOT] IN(<Câu truy vấn con>) . Xác định kết quả trong truy vấn cha (không) phải thuộc tập hợp các bộ là kết quả của truy vấn con. 43 Ví dụ IN Ví dụ: Tìm những MaHang từ bảng CUNG_UNG mà mặt hàng đó có lượng lưu kho >0. SELECT MaHang FROM CUNG_UNG WHERE MaHang IN (Select MaHang from HANG_HOA where TonKho >0) NOT IN Ví dụ: Tìm những MaHang từ bảng CUNG_UNG mà mặt hàng đó có lượng lưu kho <0. WHERE MaHang NOT IN (Select MaHang from HANG_HOA where TonKho >0) 44 b. [NOT] EXISTS (<Câu truy vấn con>) Kiểm tra quan hệ là rỗng hay không. Nếu kết quả trả về của truy vấn con chứa ít nhất một dòng thì điều kiện tồn tại thoả mãn. 45 Ví dụ trong câu lệnh SELECT Hiển thị thông tin của những nhà cung cấp cung cấp ít nhất một sản phẩm SELECT * FROM NHA_CUNG_CAP WHERE EXISTS (select * from CUNG_UNG where NHA_CUNG_CAP.MaNCC = CUNG_UNG.MaNCC 46 Hiển thị thông tin của những khách hàng đã có yêu cầu về hàng hoá SELECT * FROM KHACH_HANG WHERE EXISTS (Select MaKH from DON_DAT_HANG Where KHACH_HANG.MaKH= DONG_DAT_HANG.MaKH) 47 Ví dụ trong câu lệnh DELETE Xóa trong bảng Sự cung ứng (nếu có) tất cả những nhà cung cấp vừa là nhà cung cấp vừa là là khách hàng. DELETE * FROM NHA_CUNG_CAP WHERE EXISTS (select * from KHACH_HANG where KHACH_HANG.MaKH = NHA_CUNG_CAP.MaNCC) 48 Ví dụ trong câu lệnh INSERT Thêm một bộ giá trị vào bàng Customer và kiểm tra xem mã của khách hàng = 'CQTHG' đã tồn tại hay chưa. INSERT INTO KHACH_HANG(MaKH, TenCongty, ThanhPho ) SELECT DISTINCT 'CQTHG', 'Công ty Quốc tế Hoàng Gia', 'Hà Nội' FROM KHACH_HANG WHERE NOT EXISTS (select * from KHACH_HANG where MaKH = 'CQTHG') 49 c. <biểu thức><phép so sánh> {SOME|ANY|ALL} (<Câu truy vấn con>) So sánh kết quả với truy vấn con 50 Ví dụ SOME/ANY {<| <=| >=| >| <>| =} SOME <>SOME NOT IN \=SOME IN Ví dụ: Hiện thông tin về việc cung ứng các mặt hàng có mã MH013 có giá nhỏ hơn giá của nhà cung cấp có mã CHULA cung ứng. SELECT * FROM CUNG_UNG WHERE MaHang='MH013' and Gia < SOME (select CUNG_UNG.Gia from CUNG_UNG where MaNCC = 'CHULA' ); 51 Ví dụ ALL {<| <=| >=| >| <>| =} ALL <>ALL NOT IN Ví dụ: Tìm nhà cung ứng cung ứng mặt hàng laptop với giá trung bình thấp nhất SELECT MaNCC FROM CUNG_UNG where MaNhom =12 GROUP BY MaNCC HAVING avg (Gia) < ALL (select avg(Gia) from CUNG_UNG inner join MAT_HANG on CUNG_UNG.MaHang = MAT_HANG.MaHang group by MaNCC); 52 Phụ lục Kiểu dữ liệu: char(n): Xâu ký tự có độ dài cố định varchar(n): Xâu ký tự có độ dài thay đổi với độ dài tối đa là n int: Số nguyên smallint: Số nguyên nhỏ numeric (p,d): Số thập phân: phần thập phân p, prhần nguyên d real, double precision: Số thập phân dấu phẩy động và số thập phân dấu phẩy động với độ chính xác kép |
