Chương 5 Chương 5
Ngôn ngữ SQL Ngôn ngữ SQL
Nội dung chi tiết
i thi u
ớ
ậ
Gi ệ Đ nh nghĩa d li u ữ ệ ị Truy v n d li u ữ ệ ấ C p nh t d li u ậ ữ ệ Khung nhìn (view) Ch m c (index)
ỉ ụ
2 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Giới thiệu
ữ ệ
i s d ng
Ngôn ng Đ i s quan h ệ ữ ạ ố - Cách th c truy v n d li u ấ ứ - Khó khăn cho ng ườ ử ụ
SQL (Structured Query Language)
i s d ng ch c n đ a ra n i dung c n truy v n
ư
ầ
ấ
ộ
ỉ ầ ở
ọ
- Ngôn ng c p cao ữ ấ - Ng ườ ử ụ - Đ c phát tri n b i IBM (1970s) ể - Đ c g i là SEQUEL - Đ c ANSI công nh n và phát tri n thành chu n ậ
ượ ượ ượ
ể
ẩ
SQL-86 SQL-92 SQL-99
3 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Giới thiệu (tt)
SQL g mồ ị
ữ ệ
ữ ệ
Lý thuyết : Chuẩn SQL92
ị
Ví dụ : SQL Server
ề
ữ
ả ộ
- Đ nh nghĩa d li u (DDL) - Thao tác d li u (DML) - Đ nh nghĩa khung nhìn - Ràng bu c toàn v n ẹ ộ - Phân quy n và b o m t ề ậ ả - Đi u khi n giao tác ể SQL s d ng thu t ng ậ ử ụ - B ng ~ quan h ệ - C t ~ thu c tính ộ - Dòng ~ bộ
4 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Nội dung chi tiết
i thi u
ữ ệ
ể
ữ ệ
ậ
Gi ệ ớ Đ nh nghĩa d li u ị - Ki u d li u ữ ệ - Các l nh đ nh nghĩa d li u ị ệ Truy v n d li u ữ ệ ấ C p nh t d li u ậ ữ ệ Khung nhìn (view) Ch m c (index)
ỉ ụ
5 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Định nghĩa dữ liệu
ả
ữ
c đ cho m i quan h
ồ
Là ngôn ng mô t ỗ ng ng c a t ng thu c tính
ượ ề
ệ ủ ừ
ị ươ
ộ
- L - Mi n giá tr t ứ - Ràng bu c toàn v n ẹ ộ - Ch m c trên m i quan h ệ
ỉ ụ
ỗ
G mồ
ạ
ử
ả
- CREATE TABLE (t o b ng) ả - DROP TABLE (xóa b ng)ả - ALTER TABLE (s a b ng) - CREATE DOMAIN (t o mi n giá tr ) ị ề ạ - CREATE DATABASE - … ậ
6 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ
Kiểu dữ liệu
S (numeric)
-
ố INTEGER - SMALLINT - NUMERIC, NUMERIC(p), NUMERIC(p,s) - DECIMAL, DECIMAL(p), DECIMAL(p,s) - REAL - DOUBLE PRECISION - FLOAT, FLOAT(p)
7 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Kiểu dữ liệu (tt)
ự
Chu i ký t ỗ
(character string) - CHARACTER, CHARACTER(n) - CHARACTER VARYING(x)
Chu i bit (bit string)
ỗ - BIT, BIT(x) - BIT VARYING(x)
Ngày gi
ờ
(datetime) - DATE g m ngày, tháng và năm ồ - TIME g m gi ờ ồ - TIMESTAMP g m ngày và gi
, phút và giây ồ
ờ
8 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh tạo bảng
Đ đ nh nghĩa m t b ng
ộ ả
ể ị
ộ ữ ệ
ể
- Tên b ngả - Các thu c tính ộ Tên thu c tính Ki u d li u Các ràng bu c toàn v n trên thu c tính (RBTV) ẹ
ộ
ộ
Cú pháp
CREATE TABLE
)
9 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ Tạo bảng
CREATE TABLE NHANVIEN (
MANV CHAR(9),
HONV VARCHAR(10),
TENDEM VARCHAR(20),
TENNV VARCHAR(10),
NS DATETIME,
DCHI VARCHAR(50),
GT CHAR(3),
LUONG INT,
MA_NQL CHAR(9),
PHG INT
)
10 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh tạo bảng (tt)
- NOT NULL - NULL - UNIQUE - DEFAULT - PRIMARY KEY - FOREIGN KEY / REFERENCES - CHECK
Đ t tên cho RBTV
ặ
CONSTRAINT
11 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ RBTV
CREATE TABLE NHANVIEN (
HONV VARCHAR(10) NOT NULL,
TENDEM VARCHAR(20) NOT NULL,
TENNV VARCHAR(10) NOT NULL,
MANV CHAR(9) PRIMARY KEY,
NS DATETIME,
DCHI VARCHAR(50),
GT CHAR(3) CHECK (GT IN (‘Nam’, ‘Nu’)),
LUONG INT DEFAULT (10000),
MA_NQL CHAR(9),
PHG INT
)
12 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ RBTV
CREATE TABLE PHONGBAN (
TENPB VARCHAR(20) UNIQUE,
MAPHG INT NOT NULL,
TRPHG CHAR(9),
NG_NHANCHUC DATETIME DEFAULT (GETDATE())
)
CREATE TABLE PHANCONG (
MA_NVIEN CHAR(9) FOREIGN KEY (MA_NVIEN)
REFERENCES NHANVIEN(MANV),
SODA INT REFERENCES DEAN(MADA),
THOIGIAN DECIMAL(3,1)
)
13 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ Đặt tên cho RBTV
CREATE TABLE NHANVIEN (
HONV VARCHAR(10) CONSTRAINT NV_HONV_NN NOT NULL,
TENDEM VARCHAR(20) NOT NULL,
TENNV VARCHAR(10) NOT NULL,
MANV CHAR(9) CONSTRAINT NV_MANV_PK PRIMARY KEY,
NS DATETIME,
DCHI VARCHAR(50),
GT CHAR(3) CONSTRAINT NV_GT_CHK
CHECK (GT IN (‘Nam’, ‘Nu’)),
LUONG INT CONSTRAINT NV_LUONG_DF DEFAULT (1000000),
MA_NQL CHAR(9),
PHG INT
14 ) Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ Đặt tên cho RBTV
CREATE TABLE PHANCONG (
MA_NVIEN CHAR(9),
SODA INT,
THOIGIAN DECIMAL(3,1),
CONSTRAINT PC_MANVIEN_SODA_PK PRIMARY KEY (MA_NVIEN,
SODA),
CONSTRAINT PC_MANVIEN_FK FOREIGN KEY (MA_NVIEN)
REFERENCES NHANVIEN(MANV),
CONSTRAINT PC_SODA_FK FOREIGN KEY (SODA)
REFERENCES DEAN(MADA)
)
15 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh sửa bảng
Đ c dùng đ ể
ượ
ả
- Thay đ i c u trúc b ng - Thay đ i RBTV
ổ ấ ổ
Thêm c tộ
ALTER TABLE
Xóa c tộ
M r ng c t ộ ở ộ
ALTER TABLE
ALTER TABLE
16 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh sửa bảng (tt)
Thêm RBTV
ALTER TABLE
CONSTRAINT
CONSTRAINT
Xóa RBTV
…
ALTER TABLE
17 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ Thay đổi cấu trúc bảng
ALTER TABLE NHANVIEN ADD
NGHENGHIEP CHAR(20)
ALTER TABLE NHANVIEN DROP COLUMN NGHENGHIEP
ALTER TABLE NHANVIEN ALTER COLUMN
NGHENGHIEP CHAR(50)
18 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ Thay đổi RBTV
CREATE TABLE PHONGBAN (
TENPB VARCHAR(20),
MAPHG INT NOT NULL,
TRPHG CHAR(9),
NG_NHANCHUC DATETIME
)
ALTER TABLE PHONGBAN ADD
CONSTRAINT PB_MAPHG_PK PRIMARY KEY (MAPHG),
CONSTRAINT PB_TRPHG FOREIGN KEY (TRPHG)
REFERENCES NHANVIEN(MANV),
CONSTRAINT PB_NGNHANCHUC_DF DEFAULT
(GETDATE())
FOR (NG_NHANCHUC),
CONSTRAINT PB_TENPB_UNI UNIQUE (TENPB) 19 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh xóa bảng
ượ
Đ c dùng đ xóa c u trúc b ng - T t c d li u c a b ng cũng b xóa ủ
ể ấ ả ữ ệ
ấ ả
ả ị
Cú pháp
Ví dụ
DROP TABLE
DROP TABLE NHANVIEN
DROP TABLE PHONGBAN
DROP TABLE PHANCONG
20 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh xóa bảng (tt)
NHANVIEN
TENDEM
NS
GT
MA_NQL PHG
HON V
TENN V
MAN V
DCH I
LUON G
PHONGBAN
MAPHG
TENPH G
TRPH G
NG_NHANCHU C
21 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh tạo miền giá trị
T o ra m t ki u d li u m i k th a nh ng ki u d ữ
ớ ế ừ
ữ ệ
ữ
ể
ể
ộ ạ li u có s n ẳ ệ
Cú pháp
Ví dụ
CREATE DOMAIN
CREATE DOMAIN Kieu_Ten AS VARCHAR(30)
22 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Nội dung chi tiết
i thi u
ớ
ậ
ấ ồ
ợ
ấ
ậ
Gi ệ Đ nh nghĩa d li u ữ ệ ị Truy v n d li u ữ ệ ấ - Truy v n c b n ơ ả ấ - T p h p, so sánh t p h p và truy v n l ng ậ ợ - Hàm k t h p và gom nhóm ế ợ - M t s ki u truy v n khác ộ ố ể C p nh t d li u ậ ữ ệ Khung nhìn (view) Ch m c (index)
ỉ ụ
23 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Truy vấn dữ liệu
Là ngôn ng rút trích d li u th a m t s đi u ki n ữ ệ
ộ ố ề
ữ
ệ
ỏ
nào đó D a trên ự
nhi u dòng trùng nhau
ả
ề
set
- Cho phép 1 b ng có bag „ - B ng là ả
quan h là ệ
+ Phép toán ĐSQH Một số bổ sung
24 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Truy vấn cơ bản
G m 3 m nh đ
ệ
ồ
ề
SELECT
FROM
c hi n th trong k t qu truy v n
ể
ế
ấ
ả
ị
ộ ầ
-
Tên các b ng liên quan đ n câu truy v n
ế
ấ
c rút trích
ệ ứ
ị
ẽ ượ
ả - <đi u ki n> ề ể ố
ứ
ể
, ‡
, = , LIKE và BETWEEN
WHERE <điều kiện>
Bi u th c boolean xác đ nh dòng nào s đ N i các bi u th c: AND, OR, và NOT , „ Phép toán: < , > , £ Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ
25 ậ
Truy vấn cơ bản (tt)
SQL và ĐSQH
p SELECT
· FROM
s WHERE <điều kiện>
SELECT L
L (s
C (R))
p FROM R
WHERE C
26 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ
Lấy tất cả các cột của quan hệ kết quả
SELECT *
FROM NHANVIEN
TENDEM
MANV
HONV
TENNV
NS
DCHI
GT
LUONG
MA_NQL
PHG
Nguyen
Thanh
Tung
12/08/1955
Nam
40000
5
Nguyen
Manh
Hung
09/15/1962
638 NVC Q5 Ba Ria VT
Nam
38000
5
33344555 5 98798798 7
88866555 5 33344555 5
WHERE PHG=5
27 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề SELECT
SELECT MANV, HONV, TENDEM, TENNV
FROM NHANVIEN
TENDEM
MANV
HONV
TENNV
Nguyen
Thanh
Tung
Nguyen
Manh
Hung
33344555 5 98798798 7
WHERE PHG=5 AND GT=‘Nam’
28 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề SELECT (tt)
Tên bí danh
SELECT MANV, HONV AS HO, TENDEM AS ‘TEN DEM’, TENNV AS TEN
FROM NHANVIEN
MANV
HO
TEN DEM
TEN
Nguyen
Thanh
Tung
Nguyen
Manh
Hung
33344555 5 98798798 7
WHERE PHG=5 AND GT=‘Nam’
29 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề SELECT (tt)
Mở rộng
SELECT MANV, HONV + ‘ ’ + TENDEM + ‘ ’ + TENNV AS ‘HO TEN’
FROM NHANVIEN
MANV
HO TEN
Nguyen Thanh Tung
Nguyen Manh Hung
33344555 5 98798798 7
WHERE PHG=5 AND GT=‘Nam’
30 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề SELECT (tt)
Mở rộng
SELECT MANV, LUONG*1.1 AS ‘LUONG10%’
FROM NHANVIEN
MANV
LUONG10%
33000
27500
33344555 5 98798798 7
WHERE PHG=5 AND GT=‘Nam’
31 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề SELECT (tt)
Loại bỏ các dòng trùng nhau
SELECT DISTINCT LUONG SELECT LUONG
FROM NHANVIEN FROM NHANVIEN
LUONG LUONG
30000 30000
WHERE PHG=5 AND GT=‘Nam’ WHERE PHG=5 AND GT=‘Nam’
25000 25000
38000 25000
Tốn chi phí
38000
Người dùng muốn thấy
32 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ
Cho bi
t MANV và TENNV làm vi c
phòng
ế
ệ ở
‘Nghien cuu’
33 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề WHERE
Biểu thức logic
SELECT MANV, TENNV
FROM NHANVIEN, PHONGBAN
WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG
TRUE TRUE
34 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề WHERE (tt)
Độ ưu tiên
SELECT MANV, TENNV
FROM NHANVIEN, PHONGBAN
WHERE (TENPHG=‘Nghien cuu’ OR TENPHG=‘Quan ly’) AND PHG=MAPHG
35 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề WHERE (tt)
BETWEEN
SELECT MANV, TENNV
FROM NHANVIEN
WHERE LUONG>20000 AND LUONG<30000
SELECT MANV, TENNV
FROM NHANVIEN
WHERE LUONG BETWEEN 20000 AND 30000
36 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề WHERE (tt)
NOT BETWEEN
SELECT MANV, TENNV
FROM NHANVIEN
WHERE LUONG NOT BETWEEN 20000 AND 30000
37 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề WHERE (tt)
LIKE
SELECT MANV, TENNV
FROM NHANVIEN
Ký tự bất kỳ
WHERE DCHI LIKE ‘Nguyen _ _ _ _’
SELECT MANV, TENNV
FROM NHANVIEN
Chuỗi bất kỳ
WHERE DCHI LIKE ‘Nguyen %’
38 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề WHERE (tt)
NOT LIKE
SELECT MANV, TENNV
FROM NHANVIEN
WHERE HONV LIKE ‘Nguyen’
SELECT MANV, TENNV
FROM NHANVIEN
WHERE HONV NOT NOT LIKE ‘Nguyen’
39 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề WHERE (tt)
ESCAPE
SELECT MANV, TENNV
FROM NHANVIEN
WHERE DCHI LIKE ‘% Nguyens_%’ ESCAPE ‘s’
‘Nguyen_’
40 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề WHERE (tt)
Ngày giờ
SELECT MANV, TENNV
FROM NHANVIEN
WHERE NGSINH BETWEEN ‘19551208’ AND ‘19660719’
HH:MI:SS
YYYYMMDD
’17:30:00’ ‘19551208’
MM/DD/YYYY
’12/08/1955’ ’05:30 PM’
‘December 8, 1955’
‘19551208 17:30:00’
41 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề WHERE (tt)
NULL
- S d ng trong tr
ng h p
ử ụ
ườ
ợ
ụ
Không bi t (value unknown) Không th áp d ng (value inapplicable) Không t n t
- Nh ng bi u th c tính toán có liên quan đ n giá tr NULL
ữ
ế
ị
i (value withheld) ứ
ế ể ồ ạ ể
ả
s cho ra k t qu là NULL ẽ
ộ
ợ ệ
- Nh ng bi u th c so sánh có liên quan đ n giá tr NULL
ể
trong SQL ế
ị
ế
ế
ả
trong SQL
ế x có giá tr là NULL ị x + 3 cho ra k t qu là NULL ả ế x + 3 là m t bi u th c không h p l ể ứ ứ ữ s cho ra k t qu là UNKNOWN ả ẽ x = 3 cho ra k t qu là UNKNOWN x = 3 là m t so sánh không h p l ợ ệ
ộ
42 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề WHERE (tt)
NULL
SELECT MANV, TENNV
FROM NHANVIEN
WHERE MA_NQL IS NULL
SELECT MANV, TENNV
FROM NHANVIEN
WHERE MA_NQL IS NOT NULL
43 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề FROM
Không sử dụng mệnh đề WHERE
SELECT MANV, MAPHG
FROM NHANVIEN, PHONGBAN
MANV
MAPHG
1
4
5
1
4
5
33344555 5 33344555 5 33344555 5 98798798 7 98798798 7 98798798 7 …
…
WHERE TRUE
44 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề FROM (tt)
Tên bí danh
SELECT TENPHG, DIADIEM SELECT TENPHG, DIADIEM
FROM PHONGBAN, DDIEM_PHG FROM PHONGBAN AS PB, DDIEM_PHG AS DD WHERE MAPHG=MAPHG WHERE PB.MAPHG=DD.MAPHG
SELECT TENNV, NV.NGSINH, TENTN, TN.NGSINH SELECT TENNV, NGSINH, TENTN, NGSINH FROM NHANVIEN NV, THANNHAN TN FROM NHANVIEN, THANNHAN WHERE MANV=MA_NVIEN WHERE MANV=MA_NVIEN
45 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 1
ớ
ề
V i nh ng đ án ữ
ở
t mã đ án, mã ề ng phòng cùng
‘Ha Noi’, cho bi phòng ban ch trì đ án, h tên tr ọ ề ủ v i ngày sinh và đ a ch c a ng ớ
ế ưở i y ườ ấ
ỉ ủ
ị
46 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 2
Tìm h tên c a nhân viên phòng s 5 có tham gia ố làm vi c trên 10
ọ ề
ớ ố ờ
ủ ả
ệ
ẩ
vào đ án “S n ph m X” v i s gi giờ
47 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 3
Tìm h tên c a t ng nhân viên và ng
i ph trách
ủ ừ
ườ
ụ
tr c ti p nhân viên đó
ọ ế
ự
48 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 4
Tìm h tên c a nh ng nhân viên đ
c “Nguyen
ữ
ọ
ượ
ủ Thanh Tung” ph trách tr c ti p ụ
ự
ế
49 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề ORDER BY
Dùng đ hi n th k t qu câu truy v n theo m t th ứ ả
ể ể
ị ế
ấ
ộ
t ự
nào đó Cú pháp
SELECT
FROM
WHERE <điều kiện>
ặ
ị
- ASC: tăng (m c đ nh) - DESC: gi mả
ORDER BY
50 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Mệnh đề ORDER BY (tt)
Ví dụ
SELECT MA_NVIEN, SODA
FROM PHANCONG
MA_NVIEN
SODA
10
30
10
30
10
20
30
99988777 7 99988777 7 98798798 7 98798798 7 98765432 1 98765432 1 98765432 1
ORDER BY MA_NVIEN DESC, SODA
51 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Nội dung chi tiết
i thi u
ớ
ấ ồ
ậ
Gi ệ Đ nh nghĩa d li u ữ ệ ị Truy v n d li u ữ ệ ấ - Truy v n c b n ơ ả ấ - T p h p, so sánh t p h p và truy v n l ng ợ ậ ợ - Hàm k t h p và gom nhóm ế ợ - M t s d ng truy v n khác
ấ
ậ
ậ ữ ệ
ộ ố ạ C p nh t d li u Khung nhìn (view) Ch m c (index)
ỉ ụ
52 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Phép toán tập hợp trong SQL
ợ
SQL có cài đ t các phép toán ặ - H p (UNION) - Giao (INTERSECT) - Tr (EXCEPT)
ừ
ậ
ế
ợ
K t qu tr v là t p h p ả ả ề - Lo i b các b trùng nhau ộ - Đ gi
i các b trùng nhau
ộ
ạ ỏ l ể ữ ạ UNION ALL
INTERSECT ALL
EXCEPT ALL
53 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Phép toán tập hợp trong SQL (tt)
Cú pháp
SELECT
UNION [ALL]
SELECT
INTERSECT [ALL]
SELECT
EXCEPT [ALL]
SELECT
54 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 5
Cho bi
t các mã đ án có
ế
ề
ớ ọ
ặ
- Nhân viên v i h là ‘Nguyen’ tham gia ho c, - Tr
ng phòng ch trì đ án đó v i h là ‘Nguyen’
ớ ọ
ưở
ủ
ề
55 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 6
Tìm nhân viên có ng
i thân cùng tên và cùng gi
ườ
i ớ
tính
56 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 7
Tìm nh ng nhân viên không có thân nhân nào
ữ
57 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Truy vấn lồng
SELECT MANV, TENNV
FROM NHANVIEN, PHONGBAN
WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG
SELECT
FROM
WHERE
SELECT
FROM
WHERE <điều kiện>)
58 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Truy vấn lồng (tt)
ể ồ
nhi u m c ứ
ề
ở
ệ ấ
ng tr v m t t p các giá tr ị
ả ề ộ ậ
ườ
ệ
ộ c k t h p b ng phép n i logic
WHERE đ
Các câu l nh SELECT có th l ng nhau Câu truy v n con th Các câu truy v n trong trong cùng m t m nh đ ề ằ
ượ
ố
ấ ế ợ M nh đ WHERE c a câu truy v n ngoài ủ
ề
ệ
ấ
ể
ợ
ậ ng đi cùng v i m t s toán t ớ
ấ ộ ố
ườ
ậ
ợ
ử
-
- Ki m tra s t n t
ALL ANY ho c SOME ặ i ự ồ ạ
ể
EXISTS NOT EXISTS
59 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Truy vấn lồng (tt)
Có 2 lo i truy v n l ng
ấ ồ
ạ - L ng phân c p
ấ
ồ
ề
ệ
ủ
ế
M nh đ WHERE c a truy v n trong không tham chi u đ n ấ truy v n
ế ấ
ủ
ệ
ệ
ề
ở
thu c tính c a các quan h trong m nh đ FROM ộ ngoài
Khi th c hi n, câu truy v n trong s đ
c th c hi n tr
ẽ ượ
ấ
ự
ệ
c ướ
ự ươ
- L ng t ồ ệ
ệ ng quan ề
ủ
ế
ấ
M nh đ WHERE c a truy v n trong tham chi u ít nh t m t ấ truy v n
ộ ấ
ủ
ệ
ệ
ề
ở
thu c tính c a các quan h trong m nh đ FROM ộ ngoài
ẽ ượ
ự
ề
ấ
c th c hi n nhi u ệ ng ng v i m t b c a truy v n ngoài
Khi th c hi n, câu truy v n trong s đ ứ
ệ ự l n, m i l n t ỗ ầ ươ ầ
ộ ộ ủ
ấ
ớ
60 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ Lồng phân cấp
SELECT MANV, TENNV
FROM NHANVIEN, DIADIEM_PHG
WHERE DIADIEM=‘TP HCM’ AND PHG=MAPHG
SELECT MANV, TENNV
FROM NHANVIEN
(1, 5) WHERE PHG IN(
SELECT MAPHG
FROM DIADIEM_PHG
WHERE DIADIEM=‘TP HCM’
)
61 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 7
Tìm nh ng nhân viên không có thân nhân nào
ữ
62 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 8
Tìm nh ng nhân viên có l
ng l n h n l
ng c a
ữ
ơ ươ
ớ
ủ
ươ ộ nhân viên phòng 4
ít nh t m t ấ
63 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 9
Tìm nh ng nhân viên có l
ng l n h n l
ng c a
ữ
ươ
ơ ươ
ớ
ủ
t c nhân viên phòng 4
t ấ ả
64 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 10
Tìm nh ng tr
ng phòng có
i thi u m t
ữ
ưở
t ố
ộ thân nhân
ể
65 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ Lồng tương quan
SELECT MANV, TENNV
FROM NHANVIEN, PHONGBAN
WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG
SELECT MANV, TENNV
FROM NHANVIEN
WHERE EXISTS (
SELECT *
FROM PHONGBAN
WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG )
66 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 6
Tìm nhân viên có ng
i thân cùng tên và cùng gi
ườ
i ớ
tính
67 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 7
Tìm nh ng nhân viên không có thân nhân nào
ữ
68 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 8
Tìm nh ng nhân viên có l
ng l n h n l
ng c a
ữ
ơ ươ
ớ
ủ
ươ ộ nhân viên phòng 4
ít nh t m t ấ
69 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 10
Tìm nh ng tr
ng phòng có
i thi u m t
ữ
ưở
t ố
ộ thân nhân
ể
70 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Nhận xét IN và EXISTS
IN
ộ
ấ
ề
ấ
ở ệ
ớ
ộ
-
m nh đ SELECT c a truy v n trong ph i ả ủ m nh đ WHERE có cùng ki u d li u v i thu c tính ề ữ ệ c a truy v n ngoài ủ EXISTS
- Không c n có thu c tính, h ng s hay bi u th c nào
ứ
ể
ằ
ầ
ộ
ố
t kê tên thu c tính
ệ
ộ
ở
m nh đ ề
ệ
SELECT c a truy v n trong
khác đ ng tr c ướ ứ - Không nh t thi ấ ế ủ
t li ấ
- Nh ng câu truy v n có = ANY hay IN đ u có th chuy n
ữ
ề
ể
ể
ấ
thành câu truy v n có EXISTS
ấ
71 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Phép chia trong SQL
A
B
C
D
E
D
E
A
C
B
R
S
R‚ S
a
1
a
a
a
1
b
1
a
a
a
1
a a a g bi ai a g g g
a
b
1
a g
a
a
1
b g
a
b
3
b g
a
a
1
g g
a
b
1
g g
a
b
1
R‚ S là t p các giá tr a
ậ
ị
i trong R sao cho không có ồ ạ i
i nào trong S làm cho b (aộ i, bi) không t n t
giá tr bị trong R
g b
72 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Phép chia trong SQL (tt)
S d ng NOT EXISTS đ bi u di n
ể ể
ử ụ
ễ
SELECT R1.A, R1.B, R1.C
FROM R, R1
WHERE NOT EXISTS (
SELECT *
FROM S
WHERE NOT EXISTS (
SELECT *
FROM R R2
WHERE R2.D=S.D AND R2.E=S.E
AND R1.A=R2.A AND R1.B=R2.B AND R1.C=R2.C ))
73 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 11
Tìm tên các nhân viên đ
c phân công làm
ượ
t cấ ả t
các đ ánồ
- Tìm tên các nhân viên mà không có đ án nào là
không
ề
đ
c phân công làm
ị
ế
- T p b chia: PHANCONG(MA_NVIEN, SODA) - T p chia: DEAN(MADA) - T p k t qu : KQ(MA_NVIEN) ả - K t KQ v i NHANVIEN đ l y ra TENNV ể ấ
ớ
ượ ậ ậ ậ ế
74 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 11 (tt)
Tìm tên các nhân viên đ
c phân công làm
ượ
t cấ ả t
các đ ánồ
75 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Nội dung chi tiết
i thi u
ớ
ấ ồ
ậ
ợ
Gi ệ Đ nh nghĩa d li u ữ ệ ị Truy v n d li u ữ ệ ấ - Truy v n c b n ơ ả ấ - T p h p, so sánh t p h p và truy v n l ng ậ ợ - Hàm k t h p và gom nhóm ế ợ - M t s d ng truy v n khác
ấ
ậ
ậ ữ ệ
ộ ố ạ C p nh t d li u Khung nhìn (view) Ch m c (index)
ỉ ụ
76 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Hàm kết hợp
COUNT
ế
- COUNT(*) đ m s dòng
- COUNT(
ố ộ
ủ
ố
ị
thu c tính
ộ
ế
ố
ị
nhau và khác NULL c a thu c tính
- COUNT(DISTINCT
ủ
MIN MAX SUM AVG
Các hàm k t h p đ
c đ t
ế ợ
ượ
ặ ở ệ
m nh đ SELECT ề
77 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 12
Tìm t ng l
ng cao nh t, l
ng, l
ng th p nh t và
ổ
ươ
ấ ươ
ấ
ấ
l
ươ ng trung bình c a các nhân viên ủ
ươ
78 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 13
t s l
ế
ố ượ
ng nhân viên c a phòng ‘Nghien ủ
Cho bi cuu’
79 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 14
Cho bi
t s l ế ố ượ
ng nhân viên c a t ng phòng ban ủ ừ
PHG
SL_NV
5
3
4
3
1
1
MANV
HONV
TENNV
NGSINH
DCHI
PHAI
LUONG
MA_NQL
PHG
TENLO T Thanh
Nguyen
Tung
12/08/1955
Nam
5
40000
Nguyen
Manh
Hung
09/15/1962
638 NVC Q5 Ba Ria VT
Nam
5
38000
Tran
Thanh
Tam
07/31/1972
Nu
5
25000
Ngoc
Hang
07/19/1968
Bui
543 MTL Q1 33 NTH Q1
Nu
4
38000
Le
Nu
Quynh
Nhu
07620/1951
219 TD Q3
4
43000
Tran
Hong
Quang
04/08/1969
Nam
4
25000
Pham
Van
Vinh
11/10/1945
980 LHP Q5 450 TV HN
Nam
88866555 5 33344555 5 33344555 5 98765432 1 88866555 5 98765432 1 NULL
1
55000
33344555 5 98798798 7 45345345 3 99988777 7 98765432 1 98798798 7 88866555 5
80 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Gom nhóm
Cú pháp
SELECT
FROM
WHERE <điều kiện>
Sau khi gom nhóm
i các thu c tính
- M i nhóm các b s có cùng giá tr t ộ ẽ
ị ạ
ỗ
ộ
gom nhóm
GROUP BY
81 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 14
Cho bi
t s l ế ố ượ
ng nhân viên c a t ng phòng ban ủ ừ
82 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 15
t mã s , h tên, s l
ng
ố ọ
ế
ỗ
ố ượ
đ án và t ng th i gian mà h tham gia
V i m i nhân viên cho bi ổ
ớ ề
ờ
ọ
SODA THOIGIAN
1
32.5
2
7.5
2
10.0
3
10.0
10
10.0
20
20.0
10
35.0
30
5.0
30
20.0
20
15.0
1
20.0
2
20.0
MA_NVIE N 12345678 9 12345678 9 33344555 5 33344555 5 33344555 5 88866555 5 98798798 7 98798798 7 98765432 1 98765432 1 45345345 3 45345345 3 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ
83 ậ
Ví dụ 16
t nh ng nhân viên tham gia t
ế
ữ
ừ
2 đ án tr ở
ề
Cho bi lên
SODA THOIGIAN
1
32.5
2
7.5
2
10.0
3
10.0
10
10.0
20
20.0
bị loại ra
10
35.0
30
5.0
30
20.0
20
15.0
1
20.0
2
20.0
MA_NVIE N 12345678 9 12345678 9 33344555 5 33344555 5 33344555 5 88866555 5 98798798 7 98798798 7 98765432 1 98765432 1 45345345 3 45345345 3
84 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Điều kiện trên nhóm
Cú pháp
SELECT
FROM
WHERE <điều kiện>
GROUP BY
HAVING <điều kiện trên nhóm>
85 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 16
t nh ng nhân viên tham gia t
ế
ữ
ừ
2 đ án tr ở
ề
Cho bi lên
86 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 17
Cho bi
t nh ng phòng ban (TENPHG) có l
ng
ế
ữ
ươ
trung bình c a các nhân viên l n h n 2tr
ủ
ơ
ớ
87 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Nhận xét
ệ
ề
M nh đ GROUP BY ộ
ữ
ộ
ệ ế ợ
ừ ệ
ệ
ấ
ả
ề
- S d ng các hàm k t h p trong m nh đ SELECT đ ể
ề
ế
ệ
ợ
- Các thu c tính trong m nh đ SELECT (tr nh ng thu c ề tính trong các hàm k t h p) ph i xu t hi n trong m nh đ GROUP BY M nh đ HAVING ề ệ ử ụ ể
ệ
- Ch ki m tra đi u ki n trên nhóm, không là đi u ki n l c
ki m tra m t s đi u ki n nào đó ộ ố ề ề
ệ
ệ ọ
ề
ỉ ể trên t ng b ừ
ộ
- Sau khi gom nhóm đi u ki n trên nhóm m i đ ề
ớ ượ
ệ
c th c ự
hi n ệ
88 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Nhận xét (tt)
Th t
th c hi n câu truy v n có m nh đ GROUP
ấ
ệ
ề
ứ ự ự
-
ề
ệ
ữ
ệ
-
c gom thành nhi u nhóm
ẽ ượ
ề
ệ
-
ng ng v i m nh đ GROUP BY ỗ
ụ
-
ệ
ỏ
ỏ
ệ
-
ế ợ
ị ủ
ộ
ệ BY và HAVING (1) Ch n ra nh ng dòng th a đi u ki n trong m nh đ ề ỏ ọ WHERE (2) Nh ng dòng này s đ ữ t ề ớ ứ ươ (3) Áp d ng các hàm k t h p cho m i nhóm ế ợ (4) B qua nh ng nhóm không th a đi u ki n trong ề ữ m nh đ HAVING ề (5) Rút trích các giá tr c a các c t và hàm k t h p trong m nh đ SELECT ề
ệ
89 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 18
Tìm nh ng phòng ban có l
ữ
ươ
ng trung bình cao nh t ấ
90 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 19
Tìm 3 nhân viên có l
ươ
ng cao nh t ấ
91 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 12
Tìm tên các nhân viên đ
c phân công làm
ượ
t cấ ả t
các đ ánồ
92 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Nội dung chi tiết
i thi u
ớ
ậ
ấ ồ
ợ
ấ
ậ ữ ệ
ậ
Gi ệ Đ nh nghĩa d li u ữ ệ ị Truy v n d li u ữ ệ ấ - Truy v n c b n ơ ả ấ - T p h p, so sánh t p h p và truy v n l ng ậ ợ - Hàm k t h p và gom nhóm ế ợ - M t s d ng truy v n khác ộ ố ạ C p nh t d li u Khung nhìn (view) Ch m c (index)
ỉ ụ
93 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Một số dạng truy vấn khác
Truy v n con
ấ
ở ệ
m nh đ FROM ề
ề
ệ
m nh đ FROM ề
ế ở ệ nhiên
Đi u ki n k t - Phép k t t - Phép k t ngoàI
ế ự ế
C u trúc CASE
ấ
94 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Truy vấn con ở mệnh đề FROM
K t qu tr v c a m t câu truy v n ph là m t
ụ
ấ
ộ
ộ
ả ả ề ủ
ấ
ế b ngả - B ng trung gian trong quá trình truy v n ả - Không có l u tr th t s ư
ữ ậ ự
Cú pháp
SELECT
FROM R1, R2, (
WHERE <điều kiện>
95 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Điều kiện kết ở mệnh đề FROM
K t b ng ế ằ
SELECT
FROM R1 [INNER] JOIN R2 ON
K t ngoài
ế
WHERE <điều kiện>
SELECT
FROM R1 LEFT|RIGHT [OUTER] JOIN R2 ON
WHERE <điều kiện>
96 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 20
Tìm mã và tên các nhân viên làm vi c t
i phòng
ệ
ạ
‘Nghien cuu’
97 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 21
Tìm h tên các nhân viên và tên các đ án nhân
ề
ọ
viên tham gia n u có
ế
98 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Cấu trúc CASE
Cho phép ki m tra đi u ki n và xu t thông tin theo
ệ
ề
ấ
ể ng h p
t ng tr ừ
ườ
ợ
Cú pháp
CASE
WHEN
WHEN
…
[ELSE
END
99 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 22
Cho bi
t h tên các nhân viên đã đ n tu i v h u
ổ ề ư
ế
ế ọ (nam 60 tu i, n 55 tu i) ổ
ữ
ổ
100 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 23
Cho bi
t h tên các nhân viên và năm v h u
ế ọ
ề ư
101 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Kết luận
SELECT
FROM
[WHERE <điều kiện>]
[GROUP BY
[HAVING <điều kiện trên nhóm>]
[ORDER BY
102 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Nội dung chi tiết
i thi u
ớ
Gi ệ Đ nh nghĩa d li u ữ ệ ị Truy v n d li u ấ ữ ệ ậ ữ ệ C p nh t d li u
ậ
ử
- Thêm (insert) - Xóa (delete) - S a (update) Khung nhìn (view) Ch m c (index)
ỉ ụ
103 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh INSERT
Dùng đ thêm 1 hay nhi u dòng vào b ng
ề
ể
ả
ể
ữ ệ
ữ ệ
Đ thêm d li u - Tên quan hệ - Danh sách các thu c tính c n thêm d li u ộ - Danh sách các giá tr t
ầ ng ng ứ
ị ươ
104 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh INSERT (tt)
Cú pháp (thêm 1 dòng)
INSERT INTO
VALUES (
105 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ
INSERT INTO NHANVIEN(HONV, TENDEM, TENNV, MANV)
VALUES (‘Le’, ‘Van’, ‘Tuyen’, ‘635635635’)
INSERT INTO NHANVIEN(HONV, TENDEM, TENNV, MANV, DCHI)
VALUES (‘Le’, ‘Van’, ‘Tuyen’, ‘635635635’, NULL)
INSERT INTO NHANVIEN
VALUES (‘Le’, ‘Van’, ‘Tuyen’, ‘635635635’, ’12/30/1952’, ’98 HV’, ‘Nam’, ‘37000’, 4)
106 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh INSERT (tt)
Nh n xét ậ - Th t ứ ự
các giá tr ph i trùng v i th t ả
ứ ự
ớ
ị
các c t ộ
nh ng thu c tính không là
ể
ị
ở
ữ
ộ
- Có th thêm giá tr NULL khóa chính và NOT NULL
- Câu l nh INSERT s g p l
ẽ ặ ỗ ế
i n u vi ph m RBTV ạ
ệ Khóa chính Tham chi uế NOT NULL - các thu c tính có ràng bu c NOT NULL b t
ắ
ộ
ộ
bu c ph i có giá tr ả
ộ
ị
107 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh INSERT (tt)
Cú pháp (thêm nhi u dòng)
ề
INSERT INTO
108 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ
CREATE TABLE THONGKE_PB (
TENPHG VARCHAR(20),
SL_NV INT,
LUONG_TC INT
)
INSERT INTO THONGKE_PB(TENPHG, SL_NV, LUONG_TC)
SELECT TENPHG, COUNT(MANV), SUM(LUONG)
FROM NHANVIEN, PHONGBAN
WHERE PHG=MAPHG
GROUP BY TENPHG
109 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh DELETE
Dùng đ xóa các dòng c a b ng
ủ
ể
ả
Cú pháp
DELETE FROM
[WHERE <điều kiện>]
110 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ
DELETE FROM NHANVIEN
WHERE HONV=‘Tran’
DELETE FROM NHANVIEN
WHERE MANV=‘345345345’
DELETE FROM NHANVIEN
111 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh DELETE (tt)
ề
ộ
ố
ị
ệ ở
Nh n xét ậ - S l ố ượ ệ
ng s dòng b xóa ph thu c vào đi u ki n ụ m nh đ WHERE ề
m nh đ WHERE, t
ế
ệ ở ệ
ề
t c ấ ả
- N u không ch đ nh đi u ki n ỉ các dòng trong b ng s b xóa
ề ẽ ị
ị ả
- L nh DELETE có th gây ra vi ph m RB tham chi u
ệ
ể
ế
ạ
Không cho xóa Xóa luôn nh ng dòng có giá tr đang tham chi u đ n
ữ
ế
ế
ị
CASCADE
Đ t NULL cho nh ng giá tr tham chi u ữ
ế
ặ
ị
*
112 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh DELETE (tt)
MANV
HONV
TENNV
NGSINH
DCHI
PHAI
LUONG
MA_NQL
PHG
TENLO T Thanh
Nguyen
Tung
12/08/1955
Nam
40000
5
Nguyen
Manh
Hung
09/15/1962
638 NVC Q5 Ba Ria VT
Nam
38000
5
Tran
Thanh
Tam
07/31/1972
Nu
25000
5
Ngoc
Hang
07/19/1968
Bui
543 MTL Q1 33 NTH Q1
Nu
38000
4
Le
Nu
Quynh
Nhu
07620/1951
219 TD Q3
43000
4
Tran
Hong
Quang
04/08/1969
Nam
25000
4
Pham
Van
Vinh
11/10/1945
980 LHP Q5 450 TV HN
Nam
55000
88866555 5 33344555 5 33344555 5 98765432 1 88866555 5 98765432 1 NULL
1
33344555 5 98798798 7 45345345 3 99988777 7 98765432 1 98798798 7 88866555 5
SODA THOIGIAN
10
10.0
20
20.0
10
35.0
30
5.0
30
20.0
1
20.0
MA_NVIE N 33344555 5 88866555 5 98798798 7 98798798 7 98765432 1 45345345 3
113 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh DELETE (tt)
TENPHG MAPHG
MA_NVIEN
Nghien cuu
5
NG_NHANCHU C 05/22/1988
333445555
Dieu hanh
4
987987987
01/01/1995
Quan ly
1
888665555
06/19/1981
MANV
HONV
TENNV
NGSINH
DCHI
PHAI
LUONG
MA_NQL
PHG
TENLO T Thanh
12/08/1955
Tung
Nguyen
Nam
40000
09/15/1962
Hung
Manh
Nguyen
638 NVC Q5 Ba Ria VT
Nam
38000
Tam
Tran
Thanh
07/31/1972
Nu
25000
Bui
Hang
Ngoc
07/19/1968
543 MTL Q1 33 NTH Q1
Nu
38000
5 NUL L NUL 5 L NUL 5 L 4
Quynh
Nhu
Le
Nu
43000
4
07620/1951
219 TD Q3
Quang
Hong
Tran
04/08/1969
Nam
25000
4
Pham
Vinh
Van
11/10/1945
980 LHP Q5 450 TV HN
Nam
55000
88866555 5 33344555 5 33344555 5 98765432 1 88866555 5 98765432 1 NULL
1
33344555 5 98798798 7 45345345 3 99988777 7 98765432 1 98798798 7 88866555 5 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ
114 ậ
Lệnh UPDATE
Dùng đ thay đ i giá tr c a thu c tính cho các
ị ủ
ộ
ổ
ể dòng c a b ng ủ
ả
Cú pháp
UPDATE
SET
…
[WHERE <điều kiện>]
115 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ
UPDATE NHANVIEN
SET NGSINH=’08/12/1965’
WHERE MANV=‘333445555’
DELETE NHANVIEN
SET LUONG=LUONG*1.1
116 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ 25
ổ ơ
ự
ệ
ố
ớ ề ề
ụ
V i đ án có mã s 10, hãy thay đ i n i th c hi n đ án thành ‘Vung Tau’ và phòng ban ph trách là phòng 5
UPDATE DEAN
SET DIADIEM_DA=’Vung Tau’, PHONG=5
WHERE MADA=10
117 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Lệnh UPDATE
Nh n xét ậ
ệ
ỏ
ạ
i m nh đ WHERE s ẽ
ệ
ề
đ
- Nh ng dòng th a đi u ki n t ề c c p nh t giá tr m i ị ớ ậ
ữ ượ
ậ
m nh đ WHERE, t
ế
ề
t c ấ ả
- N u không ch đ nh đi u ki n ề ỉ ẽ ị ậ
ệ ở ệ ị các dòng trong b ng s b c p nh t ậ ả
- L nh UPDATE có th gây ra vi ph m RB tham chi u
ệ
ế
ể
ạ
Không cho s aử S a luôn nh ng dòng có giá tr đang tham chi u đ n
ữ
ử
ế
ế
ị
CASCADE
*
118 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Nội dung chi tiết
i thi u
ớ
ậ
Gi ệ Đ nh nghĩa d li u ữ ệ ị Truy v n d li u ữ ệ ấ C p nh t d li u ậ ữ ệ Khung nhìn (view)
ị
Ch m c (index)
- Đ nh nghĩa - Truy v nấ - C p nh t ậ ậ ỉ ụ
119 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Khung nhìn
ả
ệ ượ t c
ổ
ch c l u tr ứ ư
ữ v t lý ậ
B ng là m t quan h đ ộ trong CSDL
Khung nhìn cũng là m t quan h ệ ộ c l u tr v t lý (b ng o) ả
ữ ậ
ả
ượ ư ứ
ượ
- Không đ - Không ch a d li u ữ ệ - Đ c đ nh nghĩa t ữ ừ ị - Có th truy v n hay c p nh t thông qua khung nhìn
nh ng b ng khác ả ậ
ể
ậ
ấ
120 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Khung nhìn (tt)
ạ
ả ử ụ
ứ ạ
ủ
ữ ệ ấ ệ
ướ ạ
ơ ể
ụ
i d ng ti n d ng nh t ấ
T i sao ph i s d ng khung nhìn? - Che d u tính ph c t p c a d li u ấ - Đ n gi n hóa các câu truy v n ả - Hi n th d li u d ị ữ ệ - An toàn d li u ữ ệ
121 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Định nghĩa khung nhìn
Cú pháp
CREATE VIEW
ả
- Danh sách thu c tính trùng v i các thu c tính trong m nh
B ng o này có ả ộ
ệ
ớ
ộ
m nh đ WHERE ề
các b ng
đ SELECT ề - S dòng ph thu c vào đi u ki n ố ộ ụ c l y t - D li u đ ượ ấ ừ ữ ệ
ề ả
ệ ở ệ m nh đ FROM ề ở ệ
DROP VIEW
122 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ
CREATE VIEW NV_P5 AS
SELECT MANV, HONV, TENDEM,
TENVN
FROM NHANVIEN
WHERE PHG=5
CREATE VIEW TONGLNG_SLNV_PB AS
SELECT MAPHG, TENPB, COUNT(*) AS SLNV, SUM(LUONG) AS TONGLNG
FROM NHANVIEN, PHONGBAN
WHERE PHG=MAPHG
GROUP BY TENPHG
123 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Truy vấn trên khung nhìn
Tuy không ch a d li u nh ng có th th c hi n các
ể ự
ệ
ữ ệ
ư ứ câu truy v n trên khung nhìn
ấ
SELECT TENNV
FROM NV_P5
WHERE HONV LIKE ‘Nguyen’
NV_P5 ‹
PHG=5 (NHANVIEN))
p
MANV,HONV, TENDEM, TENNV (s TENNV (s
HONV=‘Nguyen’ (NV_P5))
p
124 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Truy vấn trên khung nhìn (tt)
khung nhìn và
ể ế
t câu truy v n d li u t ấ
ữ ệ ừ
Có th vi b ngả
SELECT HONV, TENVN, TENDA, THOIGIAN
FROM NV_P5, PHANCONG, DEAN
WHERE MANV=MA_NVIEN AND SODA=MADA
NV_P5 ‹
MANV,HONV, TENDEM, TENNV (s
PHG=5 (NHANVIEN))
TMP ‹
p
NV_P5 MANV=MA_NVIEN PHONGBAN SODA=MADADEAN
TENNV,TENDA,THOIGIAN(TMP)
p
125 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Cập nhật trên khung nhìn
Có th dùng các câu l nh INSERT, DELETE và ệ
ể
ả
ơ
ượ
c xây d ng trên 1 b ng và có khóa chính ả
ự
UPDATE cho các khung nhìn đ n gi n - Khung nhìn đ c a b ng ả ủ
Không th c p nh t d li u n u
ậ ữ ệ ừ
ế khóa DISTINCT ế ợ ở ộ
b ng có RB trên c t ộ nhi u b ng
ể ậ - Khung nhìn có dùng t - Khung nhìn có s d ng các hàm k t h p ử ụ - Khung nhìn có m nh đ SELECT m r ng ề ệ c xây d ng t - Khung nhìn đ ự c xây d ng t - Khung nhìn đ ự
ừ ả ừ
ượ ượ
ề
ả
126 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Cập nhật trên khung nhìn (tt)
S a l
i h cho nhân viên mã ‘123456789’
phòng 5
ử ạ ọ
ở
là ‘Pham’
UPDATE NV_P5
SET HONV=‘Pham’
WHERE MANV= ‘123456789’
127 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Nội dung chi tiết
i thi u
ớ
ậ
Gi ệ Đ nh nghĩa d li u ữ ệ ị Truy v n d li u ữ ệ ấ C p nh t d li u ậ ữ ệ Khung nhìn (view) Ch m c (index)
ỉ ụ - Đ nh nghĩa ị - Ch n l a ch m c
ọ ự
ỉ ụ
128 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Chỉ mục
ỉ ụ
ữ ệ
ộ
ộ ấ ứ
ệ
ệ
ế
Ch m c trên thu c tính A là m t c u trúc d li u làm cho vi c tìm ki m m u tin có ch a A hi u qu ả ẫ h nơ
SELECT *
Đọc 10.000 bộ FROM NHANVIEN
WHERE PHG=5 AND GT=‘Nu’
Đọc 200 bộ
Bảng NHANVIEN có 10.000 bộ
Đọc 70 bộ Có 200 nhân viên làm việc cho phòng 5
129 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Chỉ mục (tt)
Cú pháp
CREATE INDEX
Ví dụ
DROP INDEX
CREATE INDEX PHG_IND ON NHANVIEN(PHG)
CREATE INDEX PHG_GT_IND ON NHANVIEN(PHG, GT)
130 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Chỉ mục (tt)
- Tìm ki m nhanh trong tr
Nh n xét ậ ế
ườ
ng h p so sánh v i h ng s ố
ớ ằ
ợ
và phép k tế
ậ
ử
ố
- Làm ch m đi các thao tác thêm, xóa và s a - T n chi phí ư
ữ
L u tr ch m c ỉ ụ Truy xu t đĩa nhi u ấ
ề
Ch n l a cài đ t ch m c h p lý???
ọ ự
ỉ ụ
ặ
ợ
131 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ
Xét quan h ệ
- PHANCONG(MA_NVIEN, SODA, THOIGIAN)
Gi
sả ử
c l u tr trong 10 block
ượ ư
ủ
ữ Chi phí đ đ c toàn b d li u c a PHANCONG là 10 ộ ữ ệ
- Trung bình m t nhân viên tham gia 3 đ án và m t đ án
ộ ề
ề
c tr i đ u trong 10 block
- PHANCONG đ ể ọ ộ có kho ng 3 nhân viên làm ả D li u đ ữ ệ Chi phí đ tìm m t nhân viên hay m t đ án là 3
ượ ể
ộ ề
- Khi s d ng ch m c
ử ụ
ậ
ọ
ả ề ộ ỉ ụ Chi phí đ c hay c p nh t ch m c ỉ ụ ậ - Thao tác thêm c n 2 l n truy xu t đĩa ấ ầ
ầ
132 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ (tt)
Gi
s có 3 thao tác đ
c th c hi n th
ng xuyên
ả ử
ượ
ự
ệ
ườ
- Q1
SELECT SODA, THOIGIAN
FROM PHANCONG
- Q2
WHERE MA_NVIEN=‘123456789’
SELECT MANV
FROM PHANCONG
- Q3
WHERE SODA=1 AND THOIGIAN=20.5
INSERT INTO PHANCONG
VALUES ( 123456789’, 1, 20.5)
133 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
Ví dụ (tt)
B ng so sánh chi phí
ả
Thao tác
Không có chỉ mục
Chỉ mục trên MA_NVIEN
Chỉ mục trên SODA
Chỉ mục trên cả 2 thuộc tính
Q1
10
4
10
4
Q2
10
10
4
4
Q3
2
4
4
6
2 + 8p1 + 8p2
4 + 6p2
4 + 6p1
6 2p1 – 2p2
Chí phí TB
Khoảng thời gian thực hiện Q1 là p1
Khoảng thời gian thực hiện Q2 là p2
Khoảng thời gian thực hiện Q3 là 1 p1 p2
134 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
135 Nh p môn C s d li u - Khoa CNTT ơ ở ữ ệ ậ
