Chương 3: Truy vấn SQL
I. Thao tác CSDL (tạo, sửa, xoá, thêm dữ liệu)
II. Các câu truy vấn: Select, Insert, Update,
Delete
III. Truy vấn lồng, gom nhóm, điều kiện gom
nhóm
IV. Các hàm xử lý dữ liệu
V. Bài tập thực hành
67 trang |
Chia sẻ: Thục Anh | Ngày: 12/05/2022 | Lượt xem: 469 | Lượt tải: 0
Bạn đang xem trước 20 trang nội dung tài liệu Bài giảng Hệ quản trị cơ sở dữ liệu (Oracle) - Chương 3: Truy vấn SQL - Nguyễn Thị Mỹ Dung, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
Bài giảng
HQT Cơ sở dữ liệu
(ORACLE)
Số tc: 3; LT: 25; Btập: 20
GV: Nguyễn Thị Mỹ Dung
Khối lớp: Đại học L2
1
NỘI DUNG MÔN HỌC
2
1
2
3
4
Chương 1: Tổng quan về Oracle (2)
Chương 2: Cơ bản về Oracle (2)
Chương 3: Truy vấn SQL (12)
Chương 4: Lập trình PL/SQL (9)
5
6
Chương 5: Procedure, Trigger, Function (12)
Chương 6: Quản trị người dùng (4)
7 Chương 7: Kết nối CSDL với Oracle (4)
Chương 3: Truy vấn SQL
I. Thao tác CSDL (tạo, sửa, xoá, thêm dữ liệu)
II. Các câu truy vấn: Select, Insert, Update,
Delete
III. Truy vấn lồng, gom nhóm, điều kiện gom
nhóm
IV. Các hàm xử lý dữ liệu
V. Bài tập thực hành
3
I. Thao tác CSDL
1. Cho CSDL
Ví dụ: Cho các lược đồ quan hệ sau
Sinhvien (MaSV, Hoten, Namsinh, QQ, Hocluc)
Detai (MaDT, TenDT, Chunhiem, Kinhphi)
SVDT (MaSV, MaDT, NoiAD, KQ)
Bảng dữ liệu mẫu như sau:
Sinhvien MaSv Hoten Namsinh QQ Hocluc
SV001 Thái Bình An 1983 Cần Thơ 7.5
SV002 Lê Văn Khang 1985 Vĩnh Long 7.1
SV003 Trần Đức Thịnh 1983 Đồng Tháp 8.1
SV004 Bùi Quốc Vượng 1984 Đồng Tháp 9.2
SV005 Lâm Nhân Nghĩa 1985 Tiền Giang 6.5
SV006 Hà Công Sự 1982 Cần Thơ 6.0
SV007 Hồ Phương Thanh 1985 An giang 8.2
SV008 Vũ Thị Hảo Ý 1986 Bạc Liêu 5.8 4
Bảng dữ liệu mẫu (tt)
Detai MaDT TenDT Chunhiem Kinhphi
DT001 Quản lý Nhân sự Nguyễn Thị Xuân 10
DT002 Web site Bán hàng qua mạng Hồ Văn Quang 15
DT003 Bảo mật trong thương mại điện tử Trần Hiếu Hạnh 20
DT004 Quản lý lịch công tác trên mạng Lê Đức Phúc 8
DT005 Bộ soạn thảo công thức toán học Trần Hiếu Hạnh 9
DT006 Web site đào tạo từ xa Lê Đức Phúc 25
SVDT MaSV MaDT Noi_AD KQ
SV001 DT001 Đồng Tháp 9.5
SV002 DT002 Vĩnh Long 9.0
SV003 DT001 Trà Vinh 9.0
SV004 DT002 Đồng Tháp 10.0
SV007 DT004 Trà Vinh 9.0
SV008 DT003 Bạc Liêu 10.0
SV001 DT004 Đồng Tháp 9.5
SV008 DT002 Trà Vinh 9.0
SV002 DT005 Cà Mau 8.5
SV001 DT005 Tiền Giang 8.0
SV006 DT003 Đồng Tháp 9.5 5
Thao tác CSDL (tt)
2. Tạo bảng
CREATE [GLOBAL TEMPORARY] TABLE
Table_name
( Column_name Datatype [CONSTRAINT constraint_def
DEFAULT default_exp]
[, column_name type [CONSTRAINT constraint_def
DEFAULT default_exp]...]
[,table_constraint [,]]
)
6
Tạo bảng (tt)
Trong đó:
– Global Temporary: nếu có từ khóa này
bảng được tạo sẽ là bảng tạm
– Table_name: tên bảng do người dùng
đặt
– Column_name: tên cột trong bảng
– Datatype: kiểu dữ liệu của cột
– Constraint: từ khóa cho phép tạo ràng
buộc trên cột
– Contraint_def: ràng buộc của cột
– Default: từ khóa cho phép xác định giá
trị mặc định cho cột
– Default_exp: dữ liệu của cột nhận giá trị
default_exp nếu người dùng không
nhập dữ liệu vào cột
– Table_constraint: ràng buộc của toàn
bảng dữ liệu
CREATE
[GLOBAL TEMPORARY]
TABLE
( column_name datatype
[CONSTRAINT
constraint_def
DEFAULT default_exp]
[, column_name type
[CONSTRAINT
constraint_def
DEFAULT default_exp]...]
[,table_constraint [,]]
)
7
Tạo bảng (tt)
CREATE TABLE SINHVIEN (
MASV CHAR(10) PRIMARY KEY,
HOTENSV VARCHAR2(40),
NAMSINH INT,
QUEQUAN VARCHAR2(40),
HOCLUC FLOAT
)
CREATE TABLE DETAI(
MADT CHAR(10) PRIMARY KEY,
TENDT VARCHAR2(40),
CHUNHIEM VARCHAR2(40),
KINHPHI NUMBER
)
8
Tạo bảng (tt)
CREATE TABLE SVDT (
MASV CHAR(10) NOT NULL,
MADT CHAR(10) NOT NULL,
NOIA_D CHAR(40),
KETQUA FLOAT,
PRIMARY KEY (MASV, MADT),
CONSTRAINT SVDT_SV FOREIGN KEY
(MASV) REFERENCES SINHVIEN (MASV),
CONSTRAINT SVDT_DT FOREIGN KEY
(MADT) REFERENCES DETAI(MADT)
)
9
Thao tác CSDL (tt)
Kiểu ràng buộc Diễn giải
NULL/NOT NULL Cho phép/không cho phép cột chứa giá
trị rỗng
CHECK (Criteria) Giá trị nhập vào cột phải thỏa Criteria
UNIQUE Giá trị nhập vào cột phải là duy nhất
PRIMARY KEY Ràng buộc khóa chính cho cột
REFERENCES
(Column_name1)
Ràng buộc khóa ngoại cho cột sẽ tham
chiếu tới Column_name đã tồn tại trong
bảng Table_name.
Mô tả:
CONSTRAINT []
10
Thao tác CSDL (tt)
3. Sửa bảng
Thêm cột: ALTER TABLE ADD
[]
Xóa cột: ALTER TABLE
DROP COLUMN
Thay đổi kiểu dữ liệu cột:
ALTER TABLE MODIFY
11
Sửa bảng (tt)
Thêm RBTV: ALTER TABLE ADD
CONSTRAINT ,
CONSTRAINT ,
Xóa RBTV:
ALTER TABLE DROP
Ví dụ:
CREATE TABLE HANGHOA(
MAHG CHAR(10),
TENHANG CHAR(50),
DVT CHAR(5)
)
12
Sửa bảng (tt)
Mở rộng kiểu dữ liệu cho thuộc tính bảng:
ALTER TABLE HANGHOA
MODIFY DONGIA LONG
Xóa thuộc tính bảng:
ALTER TABLE HANGHOA
DROP COLUMN DVT
Thêm thuộc tính bảng:
ALTER TABLE HANGHOA ADD
DONGIA FLOAT
13
Sửa bảng (tt)
Thay đổi RBTV:
ALTER TABLE SINHVIEN ADD
CONSTRAINT fk_sv FOREIGN KEY (MAKHOA)
REFERENCES KHOA(MAKHOA)
ALTER TABLE HANGHOA ADD CONSTRAINT fk_HH
PRIMIRY KEY (MAHG)
Xóa ràng buộc toàn vẹn trong bảng
ALTER TABLE SINHVIEN DROP CONSTRAINT fk_sv
14
Thao tác dữ liệu bảng (tt)
4. Xoá bảng
DROP TABLE
[CASCADE CONSTRAINT]
CASCADE CONSTRAINT sẽ xóa luôn tất cả các
ràng buộc toàn vẹn liên quan đến bảng cần xóa.
DROP TABLE HANGHOA
CASCADE CONSTRAINT
15
Thao tác dữ liệu bảng (tt)
5. Thêm dữ liệu
INSERT INTO Tênbảng [Cột1, Cột2, ]
VALUES (giátrị1, giátrị2, )
VD:
INSERT INTO SVDT (Masv, MaDT, KQ)
VALUES (‘SV001’, ‘DT08’, 8)
Thêm nhiều dòng dữ liệu
INSERT INTO [(ds_thuộc_tính)]
SELECT , ,
FROM
[WHERE ]
16
Thao tác dữ liệu bảng (tt)
Nhận xét:
- Thứ tự các giá trị phải trùng với thứ tự các
cột,
- Có thể thêm giá trị NULL ở những thuộc tính
không là khóa chính hoặc NOT NULL,
- Các giá trị thuộc tính khóa không được trùng,
- Câu lệnh INSERT sẽ gặp lỗi nếu vi phạm
RBTV (Khóa chính, tham chiếu, trùng tên,), các
thuộc tính có ràng buộc NOT NULL bắt buộc phải có
giá trị.
17
II. Truy vấn Select
1. Định danh
Tạo định danh (SYNONYM):Tạo một định danh (tên
gọi tắt) tham chiếu đến một đối tượng của một
schema
CREATE SYNONYM FOR
.
Xóa định danh:
DROP SYNONYM
VD: PHONG(MA_PHONG, TEN_PHONG)
CREATE SYNONYM P FOR PHONG;
INSERT INTO P(MA_PHONG, TEN_PHONG)
VALUES (1, 'Dao tao');
SELECT * FROM P; # SELECT * FROM PHONG;
18
Bài tập
Xác định khóa chính?
CONGNHAN (HOTEN_CN, NAMS_CN, NAM_VAO_N,
CH_MON)
THAMGIA (HOTEN_CN, STT_CTR, NGAY_TGIA, SO_NGAY)
KTRUCSU (HOTEN_KTS, NAMS_KTS, PHAI, NOI_TN,
DCHI_LL_KTS)
THIETKE (HOTEN_KTS, STT_CTR, THU_LAO)
CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR, TINH_THANH,
KINH_PHI, TEN_CHU, TEN_HAU, NGAY_BD)
CHUTHAU (TEN_THAU, TEL, DCHI_THAU)
CHUNHAN (TEN_CHU, DCHI_CHU)
19
Bài tập (tt)
Xác định khóa ngoại?
CONGNHAN (HOTEN_CN, NAMS_CN, NAM_VAO_N,
CH_MON)
THAMGIA (HOTEN_CN, STT_CTR, NGAY_TGIA, SO_NGAY)
KTRUCSU (HOTEN_KTS, NAMS_KTS, PHAI, NOI_TN,
DCHI_LL_KTS)
THIETKE (HOTEN_KTS, STT_CTR, THU_LAO)
CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR, TINH_THANH,
KINH_PHI, TEN_CHU, TEN_THAU, NGAY_BD)
CHUTHAU (TEN_THAU, TEL, DCHI_THAU)
CHUNHAN (TEN_CHU, DCHI_CHU)
20
Bài tập (tt)
Tạo Synonym
CREATE SYNONYM
FOR .
CONGNHAN => CN THAMGIA => TG
KTRUCSU => KTS THIETKE => TK
CGTRINH => CgTr CHUTHAU => ChThau
CHUNHAN => ChNhan
21
Truy vấn Select (tt)
2. Select
Select không có điều kiện
SELECT * |
FROM
Select có điều kiện
SELECT * |
FROM
WHERE [;]
[HAVING ]
22
Truy vấn Select (tt)
Select với Distinct | All
- Distinct: loại bỏ phần tử trùng nhau
- ALL: lấy tất cả các bộ kể cả phần tử trùng
SELECT DISTINCT | All
FROM
[WHERE ];
Các mệnh đề điều kiện:
AND, OR: kết hợp nhiều điều kiện
[NOT] LIKE: so sánh chuỗi
BETWEEN AND: so sánh trong khoảng
IS [NOT] NULL: tìm các bộ là (không) rỗng.
[NOT] IN: tìm trong/ ngoài danh sách
- Các ký tự so sánh đại diện đối với chuỗi: ‘%’ (nhiều
ký tự), ‘_’ (một ký tự).
23
Truy vấn Select (tt)
Select nhiều bảng
SELECT * |
FROM [bídanh1]
[bídanh2] ON ()
[WHERE[AND|OR]
Trong đó :
JOIN: Kết nối trong với cột chỉ định
LEFT OUTER JOIN: kết trái
RIGHT OUTER JOIN: kết phải
FULL OUTER JOIN: kết đầy đủ
NATURAL JOIN: kết tự nhiên
CROSS JOIN: kết liên bảng cho phép kết tất
cả các bộ có thể có (tương tự phép kết tích đề-các), khi
đó không cần bất kỳ điều kiện kết nối nào.
24
Truy vấn Select (tt)
Select trên tập hợp
UNION [ALL]() | INTERSECT () | MINUS(-)
SELECT
FROM
[WHERE ]
UNION | [Intersect] | [Minus]
SELECT
FROM
[WHERE ]
25
Bài tập select
1. In ra mã số, họ tên của tất cả sinh viên
2. In ra mã số, họ tên của tất cả sinh viên từ 30 tuổi trở
lên.
3. Tìm họ tên sinh viên thực hiện tên đề tài, kết quả từ
9 trở lên.
4. Tìm họ tên của tất cả sinh viên thực hiện đề tài (có
thể có sinh viên không thực hiện đề tài) gồm, họ tên
sinh viên, mã đề tài, kết quả.
5. Tìm tất cả họ tên chủ nhiệm và sinh viên
6. Tìm tất cả MASV có quê quán khác với nơi áp dụng
đề tài.
7. Xóa tất cả những sinh viên chưa đăng ký đề tài nào.
26
Truy vấn Select (tt)
3. Update
UPDATE
SET = ,,
= ,
[FROM ]
[WHERE ]
Ví dụ:
UPDATE SINHVIEN
SET HOCLUC = 6.2, NAMSINH = 1991
WHERE MASV = ‘SV006’
27
Truy vấn Select (tt)
4. Delete
Dạng 1: Xóa một số bộ từ bảng
DELETE FROM
-- Nếu đk có trong bảng khác
[FROM ]
[WHERE ]
Dạng 2: Xóa tất cả các bộ từ bảng
TRUNCATE TABLE
VD: Xóa những môn học dưới 20 tiết
DELETE FROM MON
WHERE SOTIET < 20
28
Delete (tt)
Lưu ý:
- Câu lệnh Delete sẽ xóa tất cả dữ liệu trên
bảng FROM gần nó nhất.
- Nếu có nhiều điều kiện kết hợp nhiều bảng
phải sử dụng truy vấn lồng.
VD: Xoá những sinh viên có học lực dưới 5 và kết
quả thực hiện đề tài < 5.
DELETE FROM SINHVIEN
WHERE EXISTS (SELECT MASV FROM SVDT
WHERE SINHVIEN.MASV = SVDT.MASV AND
HOCLUC < 5 AND KETQUA < 5);
29
Truy vấn Select (tt)
Nhận xét:
- Các lệnh UPDATE, DELETE có thể gây ra vi
phạm RBTV (không cho sửa, xóa, hoặc xóa luôn
các dòng dữ liệu tham chiếu hoặc dữ liệu tham
chiếu sẽ là Null).
- Điều kiện trong mệnh đề WHERE sẽ được
thực hiện cho các dòng thỏa điều kiện, nếu không
có mệnh đề WHERE toàn bộ bảng sẽ có ảnh hưởng.
- Tránh vi phạm RBTV khi xóa, nên xóa các dữ
liệu bảng nhiều trước, sau đó xóa bảng một.
30
Bài tập Select
Tìm tên và địa chỉ liên lạc các chủ thầu thi công
công trình ở Cần Thơ
CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR,
TINH_THANH, KINH_PHI, TEN_CHU, TEN_THAU,
NGAY_BD)
CHUTHAU (TEN_THAU, TEL, DCHI_THAU)
SQL:
Select TEN_THAU, DCHI_THAU
From CGTRINH CT , CHUTHAU T
Where CT.TEN_THAU = T.TEN_THAU and
TINH_THANH = ‘Cần Thơ ';
31
Bài tập Select (tt)
1. Tìm tên và địa chỉ liên lạc của các chủ thầu thi
công công trình ở Cần Thơ do kiến trúc sư Lê Kim
Dung thiết kế.
THIETKE (HOTEN_KTS, STT_CTR, THU_LAO)
CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR,
TINH_THANH, KINH_PHI, TEN_CHU,
TEN_THAU, NGAY_BD)
CHUTHAU (TEN_THAU, TEL, DCHI_THAU)
SQL: ???
32
Bài tập Select (tt)
2. Tìm nơi tốt nghiệp của kts thiết kế Khách sạn
Quốc tế Cần Thơ?
KTRUCSU (HOTEN_KTS, NAMS_KTS, PHAI,
NOI_TN, DCHI_LL_KTS)
THIETKE (HOTEN_KTS, STT_CTR, THU_LAO)
CGTRINH (STT_CTR, TEN_CTR,
DIACHI_CTR, TINH_THANH, KINH_PHI,
TEN_CHU, TEN_THAU, NGAY_BD)
SQL: ???
33
Bài tập Select (tt)
3. Tìm tên, năm sinh, năm vào nghề của công nhân
có chuyên môn hàn hoặc điện tham gia công trình của
chủ thầu Lê Văn Sơn?
CONGNHAN (HOTEN_CN, NAMS_CN,
NAM_VAO_N, CH_MON)
THAMGIA (HOTEN_CN, STT_CTR, NGAY_TGIA,
SO_NGAY)
CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR,
TINH_THANH, KINH_PHI, TEN_CHU, TEN_THAU,
NGAY_BD)
SQL???
34
Bài tập Select (tt)
4. Tìm tên công nhân tham gia công trình Khách
sạn Quốc tế Cần Thơ ở TP. Cần Thơ trong thời gian
từ 15/12/1994 đến 31/12/1994?
THAMGIA (HOTEN_CN, STT_CTR,
NGAY_TGIA, SO_NGAY)
CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR,
TINH_THANH, KINH_PHI, TEN_CHU, TEN_THAU,
NGAY_BD)
SQL???
35
Bài tập Select (tt)
5. Tìm tên và địa chỉ công trình mà công nhân
Nguyễn Hồng Vân tham gia vào ngày 18/12/1994?
THAMGIA (HOTEN_CN, STT_CTR,
NGAY_TGIA, SO_NGAY)
CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR,
TINH_THANH, KINH_PHI, TEN_CHU, TEN_THAU,
NGAY_BD)
SQL???
36
Bài tập Select (tt)
6. Tìm tên và năm sinh của kiến trúc sư tốt
nghiệp ở tp HCM và thiết kế ít nhất 1 công trình có
kinh phí >400?
KTRUCSU (HOTEN_KTS, NAMS_KTS, PHAI,
NOI_TN, DCHI_LL_KTS)
THIETKE (HOTEN_KTS, STT_CTR, THU_LAO)
CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR,
TINH_THANH, KINH_PHI, TEN_CHU, TEN_THAU,
NGAY_BD)
SQL???
37
III. Truy vấn lồng
1. Truy vấn lồng
Truy vấn con là một câu lệnh SELECT được lồng
vào bên trong một câu lệnh SELECT, INSERT,
UPDATE, DELETE hoặc bên trong một truy vấn khác.
Cú pháp:
SELECT [ALL | DISTINCT]
FROM
WHERE AND | OR
(SELECT FROM
[WHERE ])
38
Truy vấn lồng (tt)
Mục đích sử dụng:
–Điều kiện tìm kiếm lấy từ kết quả của
câu select khác
– Giúp trả lời các câu hỏi
• Tìm dự án thi công dài nhất?
• Tìm kiến trúc sư có thù lao cao nhất?
• Tìm các tác giả có nhiều bài viết nhất?
39
TRUY VẤN CON LỒNG NHAU (tt)
a. Sử dụng truy vấn con với toán tử IN
Khi cần thực hiện phép kiểm tra giá trị của một
biểu thức có xuất hiện (không xuất hiện) trong tập
các giá trị của truy vấn con, ta có thể sử dụng toán
tử IN (NOT IN) như sau:
WHERE [NOT] IN
()
Ví dụ: Tìm những sinh viên không thực hiện đề tài
SELECT HOTENSV, HOCLUC
FROM SINHVIEN
WHERE MASV NOT IN
(SELECT MASV FROM SV_DT)
40
TRUY VẤN CON LỒNG NHAU (tt)
b. Truy vấn con với EXISTS
Lượng từ EXISTS (NOT EXISTS) để kiểm tra xem
một truy vấn con có trả về dòng kết quả nào hay không
được sử dụng trong truy vấn con dưới dạng:
WHERE [NOT] EXISTS ()
Ví dụ: cho biết họ tên của những sinh viên hiện chưa
có đăng ký đề tài nào
SELECT hotensv
FROM SINHVIEN
WHERE NOT EXISTS (SELECT masv -- *
FROM SVDT
WHERE sinhvien.masv=svdt.masv)
41
TRUY VẤN CON LỒNG NHAU (tt)
c. Truy vấn con với mệnh đề HAVING
Một truy vấn con có thể được sử dụng trong mệnh
đề HAVING của một truy vấn khác. Kết quả của truy vấn
con được sử dụng để tạo điều kiện đối với các hàm gộp.
Ví dụ: Cho biết mã, tên và trung bình điểm thi của
các môn học có trung bình lớn hơn trung bình điểm của
tất cả các môn học.
SELECT KETQUA.MAMH,TENMH, AVG(DIEM)
FROM KETQUA,MONHOC
WHERE KETQUA.MAMH = MON.MAMH
GROUP BY KETQUA.MAMH,TENMH
HAVING AVG(DIEM) > --[All | Any| Some]
(SELECT AVG(DIEM) FROM KETQUA)
42
TRUY VẤN CON LỒNG NHAU (tt)
d. Thực hiện phép chia với truy vấn lồng nhau
Sử dụng toán tử NOT EXISTS để thực hiện:
Cú pháp:
SELECT *
FROM R
WHERE NOT EXISTS (SELECT *
FROM S
WHERE NOT EXISTS (SELECT *
FROM R_S
WHERE R_S.C1 = S.C1 AND
R_S.C2 = R.C2))
43
Phép chia với truy vấn lồng nhau (tt)
Ví dụ: Tìm thông tin sinh viên thực hiện tất cả các
đề tài
SELECT *
FROM SINHVIEN
WHERE NOT EXISTS (SELECT *
FROM DETAI
WHERE NOT EXISTS (SELECT *
FROM SVDT
WHERE SVDT.MADT = DETAI.MADT
AND
SVDT.MASV = SINHVIEN.MASV))
44
Truy vấn Select (tt)
2. Gom nhóm Group by
Tạo bảng tạm
WITH AS ()
[, AS () [,]]
SELECT
FROM
WHERE ;
Mục đích: đơn giản trong sử dụng lại nhiều lần cùng
một câu truy vấn trong select tổng hợp.
WITH t1 AS (select * from sinhvien)
Select * from t1
Lưu ý: các bảng tạm chỉ có ý nghĩa trong câu truy
vấn ngay sau đó
45
Truy vấn lồng
Các hàm thống kê
Cú pháp:
SELECT [,]
FROM
[WHERE ]
[GROUP BY ]
[HAVING ]
Các hàm kết tập bao gồm: SUM, MAX, MIN, AVG
và COUNT.
Đặt lại tên trường: Trong một số trường hợp tên
trường của kết quả truy vấn không phù hợp ta cần đặt
lại tên trường
Cú pháp: AS
46
Hàm thống kê (tt)
Cách sử dụng các hàm:
a. Hàm SUM (): Trả về tổng giá trị
của tất cả các dòng của một trường kiểu số
Ví dụ: Tính tổng kinh phí của tất cả các đề tài
SELECT SUM (Kinhphi) as Tong_KP
FROM Detai;
b. Hàm MAX (): Trả về giá trị lớn
nhất trong tất cả các dòng của một trường
Ví dụ: Tìm kinh phí lớn nhất của tất cả các đề tài
SELECT MAX(Kinhphi) AS KP_MAX
FROM Detai
47
Cách sử dụng hàm (tt)
c. Hàm MIN (): Trả về giá trị nhỏ
nhất trong tất cả các dòng của một trường
Ví dụ: Tìm kinh phí nhỏ nhất của tất cả các đề tài
SELECT MIN(Kinhphi) AS KP_MIN
FROM Detai
d. Hàm AVG (): Trả về giá trị
trung bình cộng của tất cả các dòng một trường kiểu
số
Ví dụ: Tính trung bình cộng kinh phí của tất cả
các đề tài
SELECT AVG(Kinhphi) AS TBC_KP
FROM Detai
48
Cách sử dụng hàm (tt)
e. Hàm COUNT (): Đếm các dòng
trong bảng theo một hoặc một số trường nào đó
Ví dụ 1: In ra số lượng đề tài mà thầy Lê Đức Phúc
làm chủ nhiệm
SELECT COUNT(MaDT) AS So_DT
FROM Detai
WHERE Chunhiem = ‘Lê Đức Phúc’
Ví dụ 2: In ra họ tên của các giáo viên chủ nhiệm từ
2 đề tài trở lên
SELECT Chunhiem, COUNT(MaDT) AS SoLG
FROM Detai
GROUP BY Chunhiem
HAVING COUNT(MaDT) >= 2
49
Gom nhóm (tt)
Group by trên nhóm
SELECT , <hàmkếttập
()>
FROM
[ WHERE ]
GROUP BY
HAVING
50
Bài tập Group by
1. Tìm tổng kinh phí của tất cả các công trình
theo từng chủ thầu
2. Họ tên KTS có tổng thù lao thiết kế công trình
lớn hơn 25 triệu
3. Số lượng KTS có tổng thù lao thiết kế công
trình lớn hơn 25 triệu
4. Tìm tên, địa chỉ công trình có nhiều công nhân
nhất
51
52
GROUP BY (tt)
1. Tìm tổng kinh phí của tất cả các công trình
theo từng chủ thầu
CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR,
TINH_THANH, KINH_PHI, TEN_CHU,
TEN_THAU, NGAY_BD)
Select TEN_THAU, sum (KINH_PHI) As
TongKP
From CGTRINH
Group by TEN_THAU;
53
GROUP BY (tt)
2. Họ tên KTS có tổng thù lao thiết kế công
trình lớn hơn 25 triệu
THIETKE (HOTEN_KTS, STT_CTR, THU_LAO)
Select HOTEN_KTS, sum (THU_LAO) As
TongTL
From THIETKE
Group by HOTEN_KTS
Having sum (THU_LAO) > 25;
54
GROUP BY (tt)
3. Số lượng KTS có tổng thù lao thiết kế công
trình lớn hơn 25 triệu
THIETKE (HOTEN_KTS, STT_CTR, THU_LAO)
Select count(*) As Solg_KTS
From (Select HOTEN_KTS, sum (THU_LAO)
As TongTL
From THIETKE Group by HOTEN_KTS
Having sum (THU_LAO) > 25);
55
GROUP BY (tt)
4. Tìm tên, địa chỉ công trình có nhiều công
nhân nhất
THAMGIA (HOTEN_CN, STT_CTR, NGAY_TGIA,
SO_NGAY)
CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR,
TINH_THANH, KINH_PHI, TEN_CHU,
TEN_THAU, NGAY_BD)
with t1 as (
Select STT_CTR, count(*) As TongSoCN
From THAMGIA
Group by STT_CTR)
56
GROUP BY (tt)
with t1 as (
Select STT_CTR, count (*) As TongSoCN
From THAMGIA
Group by STT_CTR)
Select TEN_CTR, DIACHI_CTR, TongSoCN
From t1 a, CGTRINH b
Where a.STT_CTR = b.STT_CTR and
TongSoCN = (Select Max (TongSoCN)
From t1 );
57
IV. Các hàm xử lý dữ liệu
1. LENGTH (): Trả về chiều dài chuỗi
Select LENGTH ('lop DHCNTT') LEN
From Dual -- len = 10
2. INSTR(, ,
,)
Tìm vị trí chuỗi b trong chuỗi a bắt đầu từ vị trí n,
lần xuất hiện thứ m.
n<0: tìm từ phải sang trái
Select INSTR ('CORORATE FLOOR','OR',3,2)
From Dual -- vị trí 13
Các hàm xử lý dữ liệu (tt)
3. SUBSTR (, ,
)
Cắt lấy chuỗi con của chuỗi a, lấy từ vị trí n về
phải m ký tự, nếu không chỉ m thì lấy cho đến
cuối chuỗi .
n<0: vị trí cắt đếm từ phải sang trái
Select SUBSTR('ORACLE',1,3) Substring
From Dual --ORA
4. CONCAT (,): Nối 2 chuỗi a, b
Select CONCAT ('Lop DHCNTT',' Khoa SP Toan-
Tin') Substring
From Dual -- Lop DHCNTT Khoa SP Toan-Tin
58
Các hàm xử lý dữ liệu (tt)
5. SUBOWER/UPPER/INITCAPSTR ()
Chuyển chuỗi thành chuỗi viết thường /hoa /ký
tự đầu các từ viết hoa.
6. LTRIM/RTRIM(, )
Loại bỏ chuỗi b ở bên trái/ phải chuỗi a
Select LTRIM(‘khoa su pham toan - tin’, ’khoa’)
From Dual; -- su pham toan - tin
Select RTRIM(‘LAST WORDx’, ’xy’)
FROM Dual; -- LAST WORD
59
Các hàm xử lý dữ liệu (tt)
7. REPLACE (chuỗi a, chuỗi b [,chuỗi c])
Thay tất cả các chuỗi b có trong chuỗi a bằng
chuỗi c
SELECT JOB, REPLACE (JOB, ‘SALESMAN’,
‘SALESPERSON’)
FROM EMP
WHERE DEPTNO =30 OR DEPTNO =20;
60
Các hàm xử lý dữ liệu (tt)
8. SYSDATE(chuỗi a, chuỗi b [,chuỗi c])
Hàm lấy ngày tháng hiện hành của hệ thống
SELECT SYSDATE FROM DUAL
9. EXTRACT (YEAR|MONTH|DAY FROM <chuỗi
ngày>) Tách lấy năm|tháng|ngày của
Select EXTRACT(Month FROM DATE
‘2001-05-07’)
FROM Dual; -- 5
Select EXTRACT(DAY FROM Sysdate)
FROM Dual;
61
Các hàm xử lý dữ liệu (tt)
10. MONTHS_BETWEEN(, )
Cho biết số tháng giữa và
Select MONTHS_BETWEEN
(DATE ‘1981-01-02’, DATE ‘1980-01-02’)
FROM Dual --12
11. ADD_MONTHS (, n) Thêm n tháng
vào
Select ADD_MONTHS (DATE ’1981-01-02’, 2)
FROM Dual; --3
62
Các hàm xử lý dữ liệu (tt)
12. NEXT_DAY(, )
Cho biết sau là ngày nào
Select NEXT_DAY(DATE ‘2015-04-10’, 'friday')
FROM Dual; --17-04-2015
13. LAST_DAY () Cho biết ngày cuối
cùng trong tháng chỉ bởi
Select LAST_DAY (DATE ‘2015-04-10’)
FROM Dual; --30-04-2015
63
Các hàm xử lý dữ liệu (tt)
14. TO_CHAR(): Chuyển số sang chuỗi
TO_CHAR(, ):Chuyển
ngày sang chuỗi theo định dạng
Select TO_CHAR(DATE'2011-09-20',
'DD-MM-YYYY')
FROM Dual; -- ‘20-09-2011’
16. TO_NUMBER(): Chuyển chuỗi
ký số sang dạng số
Select TO_NUMBER('123')+TO_NUMBER('34')
FROM Dual; => 157
64
Các hàm xử lý dữ liệu (tt)
17. TO_DATE(, <chuỗi
định dạng>)
Chuyển chuỗi ngày sang dạng ngày theo định
dạng
Select TO_DATE(’10-04-2015’,’DD-MM-YYYY’)
FROM Dual; -- 10-04-2015
65
Các hàm xử lý dữ liệu (tt)
CASE
WHEN THEN
[WHEN THEN
[ELSE ]
END
Đánh giá danh sách các điều kiện và trả về giá trị
phù hợp
Select CASE
WHEN extract(day from sysdate)>15
THEN ‘Cuoi thang’
ELSE ‘Dau thang’ END
From Dual;
66
TÓM TẮT NỘI DUNG
- Ngôn ngữ xử lý dữ liệu (Insert, Update, Delete)
- Truy vấn dữ liệu trên một và nhiều bảng (select
from where)
- Truy vấn dữ liệu tập hợp (UNION, INTERSECT,
MINUS), truy vấn lồng nhau
- Truy vấn sử dụng hàm kết tập (SUM, MAX, MIN,
AVG, COUNT)
- Truy vấn sắp xếp dữ liệu ORDER BY
- Truy vấn thống kê trên nhóm GROUP BY
- Các hàm xử lý dữ liệu (chuỗi, ngày, số, kiểu).
Biên soạn: Nguyễn Thị Mỹ Dung 67
Các file đính kèm theo tài liệu này:
- bai_giang_he_quan_tri_co_so_du_lieu_oracle_chuong_3_truy_van.pdf