CHƯ Ơ NG 5
NGÔN NGỮ SQL
KNOWLEDGE FOR SHARE
Tài liệ u tham khả o
[ 1] Đỗ Phúc, Nguyễ n Đăng Tỵ .
Giáo trình cơ sở dữ liệ u.
Đạ i họ c Quố c gia Tp.HCM.
[ 2] Đồ ng Thị Bích Thủ y.
Giáo trình cơ sở dữ liệ u.
Đạ i họ c Quố c gia Tp.HCM.
[ 3] Trầ n Ngọ c Bả o.
Slide bài giả ng CSDL
Đạ i họ c Sư Phạ m TP.HCM
[ 4] Lê Minh Triế t.
Slide bài giả ng CSDL
Đạ i họ c Sư Phạ m TP.HCM
3/ 22/ 2013
2
KNOWLEDGE FOR SHARE
Nộ i dung
1. Giớ i thiệ u về SQL
2. Kiể u dữ liệ u
3. Đị nh nghĩa dữ liệ u
4. Truy vấ n dữ liệ u
5. Cậ p nhậ t dữ liệ u
6. Mộ t số vấ n đề khác
3/ 22/ 2013
3
KNOWLEDGE FOR SHARE
1. Giớ i thiệ u
Ngôn ngữ
ĐSQH
– Cách thứ c truy vấ n dữ liệ u
– Khó khăn cho ngư ờ i sử dụ ng
SQL ( Structured Query Language)
– Ngôn ngữ cấ p cao
– Ngư ờ i sử dụ ng chỉ cầ n đư a ra nộ i dung cầ n truy
vấ n
– Đư ợ c phát triể n bở i I BM ( 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/ 22/ 2013
4
KNOWLEDGE FOR SHARE
1. Giớ i thiệ u
SQL gồ m
– Đị 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ộ
3/ 22/ 2013
Lý thuyế t
Chuẩ n SQL-92,
SQL Server 2000
Minh họ a
SQL Server 2000
5
KNOWLEDGE FOR SHARE
1. Giớ i thiệ u về SQL
Ngôn ngữ
đị nh nghĩa dữ
liệ u
DDL - Data Definition Language
Các lệ nh dùng để đị nh nghĩa CSDL: tạ o lậ p (create),
thay đổ i (alter) và hủ y bỏ (drop) các đố i tư ợ ng dữ liệ u,
thiế t lậ p các ràng buộ c.
Ngôn ngữ
thao tác dữ
liệ u
DML - Data Manipulation Language
Các lệ nh dùng để bả o trì và truy vấ n CSDL: thêm
(insert), sử a (update), xóa (delete) dữ liệ u củ a bả ng,
truy vấ n (select).
Ngôn ngữ điề u khiể n dữ liệ u
DCL - Data Control Language
Các lệ nh dùng để điề u khiể n CSDL: quả n trị các quyề n
(grant, revoke).
3/ 22/ 2013
6
KNOWLEDGE FOR SHARE
1. Giớ i thiệ u về SQL
3/ 22/ 2013
7
KNOWLEDGE FOR SHARE
2. Kiể u dữ liệ u
Kiể u dữ liệ u trong SQL Server
3/ 22/ 2013
8
KNOWLEDGE FOR SHARE
2. Kiể u dữ liệ u
Kiể u dữ liệ u trong SQL Server
3/ 22/ 2013
9
KNOWLEDGE FOR SHARE
2. Kiể u dữ liệ u
Kiể u dữ liệ u trong SQL Server
3/ 22/ 2013
10
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
Là ngôn ngữ
–
–
–
–
mô tả
Lư ợ c đồ cho mỗ i quan hệ
Miề n giá trị tư ơ ng ứ ng củ a từ ng thuộ c tính
Ràng buộ c toàn vẹ n
Chỉ mụ c trên mỗ i quan hệ
Các lệ nh thông dụ ng
–
–
–
–
–
–
CREATE TABLE ( tạ o bả ng)
ALTER TABLE ( sử a bả ng)
DROP TABLE ( xóa bả ng)
CREATE DOMAI N ( tạ o miề n giá trị )
CREATE DATABASE
…
3/ 22/ 2013
11
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
Để đị nh nghĩa mộ t bả ng
– Tên bả ng
– Các thuộ c tính
Tạ o bả ng
• Tên thuộ c tính
• Kiể u dữ liệ u
• Các RBTV trên thuộ c tính
Cú pháp
CREATE TABLE < Tên_bả ng>
(
< Tên_cộ t> < Kiể u_dữ _liệ u> [ < RBTV> ] ,
< Tên_cộ t> < Kiể u_dữ _liệ u> [ < RBTV> ] ,
…
[ < RBTV> ]
)
3/ 22/ 2013
12
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
CREATE TABLE NHANVI EN
(
MANV
CHAR( 9) ,
HONV
NVARCHAR( 10) ,
TENLOT
NVARCHAR( 20) ,
TENNV
NVARCHAR( 10) ,
NGSI NH
DATETI ME,
DCHI
NVARCHAR( 50) ,
PHAI
CHAR( 3) ,
LUONG
I NT,
MA_NQL
CHAR( 9) ,
PHG
I NT
Tạ o bả ng
)
3/ 22/ 2013
13
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
<RBTV>
NOT NULL
NULL
UNIQUE (khóa chỉ đị nh)
DEFAULT
PRIMARY KEY
FOREIGN KEY / REFERENCES
CHECK (đk ràng buộ c)
Tạ o bả ng
Đặ t tên cho RBTV
CONSTRAINT <Ten_RBTV> <RBTV>
3/ 22/ 2013
14
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
Tạ o bả ng
CREATE TABLE NHANVIEN
(
NVARCHAR(10) NOT NULL,
HONV
TENLOT NVARCHAR(20) NOT NULL,
TENNV
NVARCHAR(10) NOT NULL,
MANV
CHAR(9) PRIMARY KEY,
NGSINH DATETIME,
DCHI
NVARCHAR(50),
PHAI
NCHAR(4) CHECK (PHAI IN (N‘Nam’, N‘Nữ ’)),
LUONG INT DEFAULT (10000),
MA_NQL CHAR(9),
INT
PHG
)
3/ 22/ 2013
15
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
CREATE TABLE PHONGBAN
(
Tạ o bả ng
TENPB NVARCHAR( 20) UNI QUE,
MAPHG I NT NOT NULL,
TRPHG CHAR( 9) ,
NG_NHANCHUC DATETI ME DEFAULT ( GETDATE( ) )
)
CREATE TABLE PHANCONG
(
MANV CHAR( 9) FOREI GN KEY ( MANV)
REFERENCES NHANVI EN( MANV) ,
MADA I NT REFERENCES DEAN( MADA) ,
THOI GI AN DECI MAL( 3,1) ,
PRI MARY KEY( MANV,MADA)
)
3/ 22/ 2013
16
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
CREATE TABLE NHANVI EN
(
Tạ o bả ng
HONV NVARCHAR( 10) CONSTRAI NT NV_HONV_NN NOT NULL,
TENLOT NVARCHAR( 20) NOT NULL,
TENNV NVARCHAR( 10) NOT NULL,
MANV CHAR( 9) CONSTRAI NT NV_MANV_PK PRI MARY KEY,
NGSI NH
DATETI ME,
DCHI NVARCHAR( 50) ,
PHAI NCHAR( 3) CONSTRAI NT NV_PHAI _CHK
CHECK ( PHAI I N ( N‘ Nam’, N ‘ Nữ )’ ) ,
LUONG I NT CONSTRAI NT NV_LUONG_DF DEFAULT ( 10000) ,
MA_NQL CHAR( 9) ,
PHG I NT
)
3/ 22/ 2013
17
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
CREATE TABLE PHANCONG
(
Tạ o bả ng
MANV CHAR( 9) ,
MADA I NT,
THOI GI AN DECI MAL( 3,1) ,
CONSTRAI NT PC_MANV_MADA_PK
PRI MARY KEY ( MANV, MADA) ,
CONSTRAI NT PC_MANV_FK FOREI GN KEY ( MANV)
REFERENCES NHANVI EN( MANV) ,
CONSTRAI NT PC_MADA_FK FOREI GN KEY ( MADA)
REFERENCES DEAN( MADA)
)
3/ 22/ 2013
18
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
Thay đổ i cấ u trúc bả ng
Sử a bả ng
– Thêm cộ t
– Xóa cộ t
– Mở rộ ng cộ t
Thay đổ i ràng buộ c toàn vẹ n ( RBTV)
– Thêm RBTV
– Xóa RBTV
3/ 22/ 2013
19
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
ALTER TABLE < Tên_bả ng> ADD COLUMN
< Tên_cộ t> < Kiể u_dữ _liệ u> [ < RBTV> ]
ALTER TABLE < Tên_bả ng> DROP COLUMN < Tên_cộ t>
ALTER TABLE < Tên_bả ng> ADD
CONSTRAI NT < Ten_RBTV> < RBTV> ,
CONSTRAI NT < Ten_RBTV> < RBTV> ,
Sử a bả ng
ALTER TABLE < Tên_bả ng> ALTER COLUMN
< Tên_cộ t> < Kiể u_dữ _liệ u_mớ i>
…
ALTER TABLE < Tên_bả ng> DROP < Tên_RBTV>
3/ 22/ 2013
20
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
Sử a bả ng
ALTER TABLE NHANVIEN
ADD NGHENGHIEP CHAR(20)
ALTER TABLE NHANVIEN
DROP COLUMN NGHENGHIEP
ALTER TABLE NHANVIEN
ALTER COLUMN NGHENGHIEP CHAR(50)
3/ 22/ 2013
21
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
CREATE TABLE PHONGBAN
(
TENPB
VARCHAR(20),
MAPHG
INT NOT NULL,
TRPHG
CHAR(9),
Sử a bả ng
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)
3/ 22/ 2013
22
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
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
DROP TABLE < Tên_bả ng>
Ví dụ
DROP TABLE NHANVI EN
DROP TABLE PHONGBAN
DROP TABLE PHANCONG
3/ 22/ 2013
23
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
Xóa bả ng
NHANVI EN
HONV
TENLOT
TENNV
MANV
NGSINH
DCHI
PHAI
LUONG
MA_NQL
PHG
PHONGBAN
TENPHG
3/ 22/ 2013
MAPHG
TRPHG
NG_NHANCHUC
24
KNOWLEDGE FOR SHARE
3. Đị nh nghĩa dữ liệ u
Tạ o ra mộ t kiể u dữ liệ u mớ i kế
nhữ ng kiể u dữ liệ u có sẳ n
Cú pháp
thừ a
CREATE DOMAI N < Tên_kdl_mớ i> AS < Kiể u_dữ _liệ u>
Ví dụ
Tạ o miề n GT
CREATE DOMAIN kieu_MANV AS CHAR(9)
3/ 22/ 2013
25
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
1. Truy vấ n cơ bả n
2. Tậ p hợ p, so sánh tậ p hợ p
3. Truy vấ n lồ ng
4. Hàm kế t hợ p, gom nhóm
5. Mộ t số kiể u truy vấ n khác
3/ 22/ 2013
26
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Là ngôn ngữ rút trích dữ
điề u kiệ n nào đó
Dự a trên
Phép toán ĐSQH
liệ u thỏ a mộ t số
Mộ t số bổ sung
Lệ nh cơ bả n để rút trích thông tin: SELECT
Cho phép 1 bả ng có nhiề u dòng trùng nhau
Bả ng là bag ( đa bộ ) quan hệ là set ( tậ p hợ p)
3/ 22/ 2013
27
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
SELECT < danh sách các cộ t>
FROM < danh sách các bả ng>
WHERE < điề u kiệ n>
< danh sách các cộ t>
– Tên các cộ t cầ n đư ợ c hiể n thị trong kế t quả truy vấ n
< danh sách các bả ng>
– Tên các bả ng liên quan đế n câu truy vấ n
< điề u kiệ n>
– Biể u thứ c boolean xác đị nh dòng nào sẽ đư ợ c rút
trích
– Nố i các biể u thứ c: AND, OR, và NOT
– Phép toán: < , > , ≤ , ≥ , ≠( < > ) , =, LI KE và BETWEEN
3/ 22/ 2013
28
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
SELECT < danh sách các cộ t>
×
FROM < danh sách các bả ng>
WHERE < điề u kiệ n>
SELECT L
FROM R,S
L(
C (RxS))
WHERE C
3/ 22/ 2013
29
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
Lấ y tấ t cả các cộ t củ a
quan hệ kế t quả
SELECT *
FROM NHANVIEN
WHERE PHG= 5
MANV
HONV
TENLOT
TENNV
NGSINH
DCHI
PHAI
LUONG
MA_NQL
PHG
333445555 Nguyen
Thanh
Tung
12/ 08/ 1955
638 NVC Q5
Nam
40000
888665555
5
987987987 Nguyen
Manh
Hung
09/ 15/ 1962
Ba Ria VT
Nam
38000
333445555
5
PHG= 5
3/ 22/ 2013
(NHANVIEN)
30
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
SELECT MANV, HONV, TENLOT, TENNV
FROM NHANVIEN
WHERE PHG= 5 AND PHAI= ‘ Nam’
MANV
HONV
TENLOT
TENNV
333445555
Nguyen
Thanh
Tung
987987987
Nguyen
Manh
Hung
MANV,HONV,TENLOT,TENNV(
3/ 22/ 2013
PHG= 5 ∧ PHAI= ‘ Nam’ (NHANVIEN))
31
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
Tên bí danh
SELECT MANV, HONV AS [ HỌ ] , TENLOT AS [ TÊN LÓT] , TENNV AS [ TÊN]
FROM NHANVIEN
WHERE PHG= 5 AND PHAI= ‘ Nam’
(
MANV,HỌ ,TÊN LÓT,TÊN
3/ 22/ 2013
MANV
HỌ
TÊN LÓT
TÊN
333445555
Nguyen
Thanh
Tung
987987987
Nguyen
Manh
Hung
MANV,HONV,TENLOT,TENNV(
PHG= 5∧PHAI= ‘ Nam’(NHANVIEN)))
32
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
Mở rộ ng
SELECT MANV, HONV + ‘ ’+ TENLOT + ‘ ’+ TENNV AS [ HỌ
TÊN]
FROM NHANVIEN
WHERE PHG= 5 AND PHAI= ‘ Nam’
MANV
3/ 22/ 2013
HỌ
TÊN
333445555
Nguyen Thanh Tung
987987987
Nguyen Manh Hung
33
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
Mở rộ ng
SELECT MANV, LUONG* 1.1 AS [ LUONG10%]
FROM NHANVIEN
WHERE PHG= 5 AND PHAI= ‘ Nam’
MANV
LUONG10%
333445555
33000
987987987
27500
MANV,LUONG10% (
3/ 22/ 2013
MANV,LUONG* 1.1(
PHG= 5∧PHAI= ‘ Nam’(NHANVIEN)))
34
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
Loạ i bỏ các dòng trùng nhau
SELECT DISTINCT LUONG
FROM NHANVIEN
WHERE PHG= 5 AND PHAI= ‘ Nam’
LUONG
30000
- Tố n chi phí
25000
- Ngư ờ i dùng muố n thấ y
25000
38000
38000
3/ 22/ 2013
35
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
Cho biế t MANV và TENNV làm việ c ở
phòng ‘ Nghien cuu’
R1
KQ
NHANVIEN
MANV, TENNV (
PHG= MAPHG
PHONGBAN
TENPHG= ‘ Nghien cuu’(R1))
SELECT MANV, TENNV
Biể u thứ c luậ n lý
FROM NHANVIEN, PHONGBAN
WHERE TENPHG= ‘ Nghien cuu’AND PHG= MAPHG
TRUE
3/ 22/ 2013
TRUE
36
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
Độ ư u tiên
SELECT MANV, TENNV
FROM NHANVIEN, PHONGBAN
WHERE (TENPHG= ‘ Nghien cuu’OR TENPHG= ‘ Quan ly’) AND PHG= MAPHG
3/ 22/ 2013
37
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
BETWEEN
SELECT MANV, TENNV
SELECT MANV, TENNV
FROM NHANVIEN
FROM NHANVIEN
WHERE LUONG> = 20000 AND
WHERE LUONG BETWEEN 20000 AND 30000
LUONG< = 30000
NOT BETWEEN
SELECT MANV, TENNV
FROM NHANVIEN
WHERE LUONG NOT BETWEEN 20000 AND 30000
3/ 22/ 2013
38
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
LI KE
SELECT MANV, TENNV
SELECT MANV, TENNV
FROM NHANVIEN
FROM NHANVIEN
WHERE DCHI LIKE ‘ Nguyen _ _ _ _’
WHERE HONV NOT NOT LIKE ‘ Nguyen’
Ký tự bấ t kỳ
SELECT MANV, TENNV
FROM NHANVIEN
WHERE DCHI LIKE ‘ Nguyen %’
Chuỗ i bấ t kỳ
3/ 22/ 2013
39
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
ESCAPE
SELECT MANV, TENNV
FROM NHANVIEN
WHERE DCHI LIKE ‘ % Nguyens_%’ ESCAPE ‘ s’
‘ Nguyen_’
3/ 22/ 2013
40
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
SELECT MANV, TENNV
Ngày giờ
FROM NHANVIEN
WHERE NGSINH = ‘ 1955-12-08’
SELECT MANV, TENNV
FROM NHANVIEN
WHERE NGSINH BETWEEN ‘ 1955-12-08’AND ‘ 1966-07-19’
‘ 1955-12-08’
YYYY-MM-DD
’17:30:00’
’12/ 08/ 1955’
MM/ DD/ YYYY
’05:30 PM’
HH:MI:SS
‘ December 8, 1955’
‘ 1955-12-08 17:30:00’
3/ 22/ 2013
41
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
Hàm CONVERT
Convert( kiể u_dữ _liệ u, biể u_thứ c [ ,kiể u_chuyể n_đổ i] )
Hàm có chứ c năng chuyể n đổ i giá trị củ a biể u thứ c
sang kiể u_dữ _liệ u.
Tham số kiể u_chuyể n_đổ i là mộ t giá trị số thư ờ ng
đư ợ c sử dụ ng khi chuyể n đổ i giá trị kiể u ngày sang
kiể u chuỗ i nhằ m quy đị nh khuôn dạ ng dữ liệ u đư ợ c
hiể n thị .
3/ 22/ 2013
42
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
HOADON(SoHD, MaKH, ngaylap, trigia)
Ví dụ : đị nh dạ ng ngày theo dạ ng dd/mm/yyyy
SELECT SoHD, MaKH, CONVERT( varchar( 10) ,ngaylap,103) as ngaylap
FROM HOADON
3/ 22/ 2013
43
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
3/ 22/ 2013
44
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
Sử
dụ ng trong trư ờ ng hợ p
– Không biế t ( value unknow n)
– Không thể áp dụ ng ( value inapplicable)
– Không tồ n tạ i ( value w ithheld)
NULL
Nhữ ng biể u thứ c tính toán có liên quan đế n giá trị
NULL sẽ cho ra kế t quả là NULL
– 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ệ trong SQL
Nhữ ng biể u thứ c so sánh có liên quan đế n giá trị
NULL 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ệ trong SQL
3/ 22/ 2013
45
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
SELECT MANV, TENNV
NULL
FROM NHANVIEN
WHERE MA_NQL IS NULL
SELECT MANV, TENNV
FROM NHANVIEN
WHERE MA_NQL IS NOT NULL
3/ 22/ 2013
46
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
Không sử dụ ng
mệ nh đề WHERE
( Tích chéo RxS)
MANV
3/ 22/ 2013
SELECT MANV, MAPHG
FROM NHANVIEN, PHONGBAN
WHERE TRUE
MAPHG
333445555
1
333445555
4
333445555
5
987987987
1
987987987
4
987987987
5
…
…
47
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n
Tên bí danh
SELECT TENPHG, DIADIEM
SELECT TENPHG, DIADIEM
FROM PHONGBAN, DDIEM_PHG
FROM PHONGBAN PB, DDIEM_PHG DD
WHERE MAPHG= MAPHG
WHERE PB.MAPHG= DD.MAPHG
SELECT TENNV, NGSINH, TENTN, NGSINH
FROM NHANVIEN, THANNHAN
WHERE MANV= MA_NVIEN
SELECT TENNV, NV.NGSINH, TENTN, TN.NGSINH
FROM NHANVIEN NV, THANNHAN TN
WHERE MANV= MA_NVIEN
3/ 22/ 2013
48
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n – Bài tậ p
1. Vớ i nhữ ng đề án ở ‘ Ha Noi’, cho biế t mã đề án, mã
phòng ban chủ trì đề án, họ tên trư ở ng phòng cùng
vớ i ngày sinh và đị a chỉ củ a ngư ờ i ấ y
2. Tìm họ tên củ a nhân viên phòng số 5 có tham gia vào
đề án “Sả n phẩ m X” vớ i số giờ làm việ c trên 10 giờ
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 đó
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
3/ 22/ 2013
49
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n – Sắ p xế p
Dùng để hiể n thị kế t quả câu truy vấ n
theo mộ t thứ tự nào đó
ORDER BY
Cú pháp
SELECT < danh sách các cộ t>
FROM < danh sách các bả ng>
WHERE < điề u kiệ n>
ORDER BY < danh sách các cộ t>
– ASC: tăng ( mặ c đị nh)
– DESC: giả m
3/ 22/ 2013
50
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n – Sắ p xế p
SELECT MA_NVIEN, SODA
ORDER BY
FROM PHANCONG
ORDER BY MA_NVIEN DESC, SODA
MA_NVIEN
3/ 22/ 2013
SODA
999887777
10
999887777
30
987987987
10
987987987
30
987654321
10
987654321
20
987654321
30
51
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n cơ bả n – Tổ ng kế t
DI STI NCT
BETWEEN
LI KE
ESCAPE
NGÀY GI Ờ
CONVERT
NULL
TÍ CH CHÉO
ĐẶ T TÊN
ORDER BY
3/ 22/ 2013
52
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Phép toán tậ p hợ p trong SQL
SQL có cài đặ t các phép toán
– Hộ i ( UNI ON)
– Giao ( I NTERSECT)
– Trừ ( EXCEPT)
Kế t quả trả về là tậ p hợ p
– Loạ i bỏ các bộ trùng nhau
– Để giữ lạ i các bộ trùng nhau
• UNI ON ALL
• I NTERSECT ALL
• EXCEPT ALL
3/ 22/ 2013
53
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Phép toán tậ p hợ p trong SQL
SELECT < ds cộ t> FROM < ds bả ng> WHERE < điề u kiệ n>
UNI ON [ ALL]
SELECT < ds cộ t> FROM < ds bả ng> WHERE < điề u kiệ n>
SELECT < ds cộ t> FROM < ds bả ng> WHERE < điề u kiệ n>
I NTERSECT [ ALL]
SELECT < ds cộ t> FROM < ds bả ng> WHERE < điề u kiệ n>
SELECT < ds cộ t> FROM < ds bả ng> WHERE < điề u kiệ n>
EXCEPT [ ALL]
SELECT < ds cộ t> FROM < ds bả ng> WHERE < điề u kiệ n>
3/ 22/ 2013
54
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Phép toán tậ p hợ p trong SQL
Cho biế t các mã đề án có
– Nhân viên vớ i họ là ‘ Nguyen’ tham gia đề án
hoặ c
– Trư ở ng phòng chủ trì đề án vớ i họ là ‘ Nguyen’
SELECT MADA
FROM NHANVIEN, PHANCONG
Phép hợ p
WHERE MANV= MA_NVIEN AND HONV= ‘ Nguyen’
UNI ON
SELECT MADA
FROM NHANVIEN, PHONGBAN, DEAN
WHERE MANV= TRPHG AND MAPHG= PHONG AND HONV= ‘ Nguyen’
3/ 22/ 2013
55
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Phép toán tậ p hợ p trong SQL
Tìm nhân viên có ngư ờ i thân cùng
tên và cùng giớ i tính
Phép giao
SELECT TENNV, PHAI, MANV FROM NHANVIEN
INTERSECT
SELECT TENTN, PHAI, MA_NVIEN FROM THANNHAN
SELECT NV.*
FROM NHANVIEN NV, THANNHAN TN
WHERE NV.MANV= TN.MA_NVIEN
AND NV.TENNV= TN.TENTN AND NV.PHAI= TN.PHAI
3/ 22/ 2013
56
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Phép toán tậ p hợ p trong SQL
Tìm nhữ ng nhân viên không có thân
nhân nào
Phép trừ
SELECT MANV FROM NHANVIEN
EXCEPT
SELECT MA_NVIEN AS MANV FROM THANNHAN
SELECT MANV
FROM NHANVIEN
WHERE MANV NOT IN
(SELECT MANV FROM THANNHAN)
3/ 22/ 2013
57
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n lồ ng
Tìm nhân viên có ngư ờ i thân
SELECT DISTINCT NV.MANV,HONV + ‘ ’ + TENNV [HỌ
TÊN]
FROM NHANVIEN NV, THANNHAN TN
WHERE NV.MANV= TN.MANV
SELECT MANV
FROM NHANVIEN
WHERE MANV IN
(SELECT MANV FROM THANNHAN)
3/ 22/ 2013
58
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n lồ ng
Câu truy vấ n cha
(Outer query)
SELECT < danh sách các cộ t>
FROM < danh sách các bả ng>
WHERE < so sánh tậ p hợ p> (
SELECT < danh sách các cộ t>
FROM < danh sách các bả ng>
Câu truy vấ n con
(Subquery)
WHERE < điề u kiệ n> )
3/ 22/ 2013
59
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Các lệ nh SELECT có thể lồ ng nhau ở nhiề u mứ c
Các câu truy vấ n con trong cùng mộ t mệ nh đề WHERE
đư ợ c kế t hợ p bằ ng phép nố i logic
Câu truy vấ n con thư ờ ng trả về mộ t tậ p các giá trị
Mệ nh đề WHERE củ a câu truy vấ n cha
– < biể u thứ c> < so sánh tậ p hợ p> < truy vấ n con>
– So sánh tậ p hợ p thư ờ ng đi cùng vớ i mộ t số toán tử
• I N, NOT I N
• ALL
• ANY hoặ c SOME
– Kiể m tra sự tồ n tạ i
• EXI STS
• NOT EXI STS
Truy vấ n lồ ng
3/ 22/ 2013
60
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n lồ ng
SELECT *
FROM NHANVIEN
WHERE MLUONG > = ALL
(SELECT MLUONG FROM NHANVIEN)
SELECT *
FROM NHANVIEN
WHERE MLUONG > = ANY/ SOME
(SELECT MLUONG FROM NHANVIEN)
3/ 22/ 2013
61
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
1. Lồ ng phân cấ p
Truy vấ n lồ ng
–
Mệ nh đề WHERE củ a truy vấ n con không tham chiế u
đế n thuộ c tính củ a các quan hệ trong mệ nh đề FROM
ở truy vấ n cha
–
Khi thự c hiệ n, câu truy vấ n con sẽ
trư ớ c
đư ợ c thự c hiệ n
2. Lồ ng tư ơ ng quan
–
Mệ nh đề WHERE củ a truy vấ n con tham chiế u ít nhấ t
mộ t thuộ c tính củ a các quan hệ trong mệ nh đề FROM
ở truy vấ n cha
–
Khi thự c hiệ n, câu truy vấ n con sẽ đư ợ c thự c hiệ n
nhiề u lầ n, mỗ i lầ n tư ơ ng ứ ng vớ i mộ t bộ củ a truy vấ n
cha
3/ 22/ 2013
62
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n lồ ng – Phân cấ p
SELECT MADA
FROM NHANVIEN NV, PHANCONG PC
WHERE NV.MANV= PC.MA_NVIEN AND NV.HONV= ‘ Nguyen’
UNION
SELECT MADA
FROM NHANVIEN NV, PHONGBAN PB, DEAN DA
WHERE NV.MANV= PB.TRPHG AND PB.MAPHG= DA.PHONG
AND NV.HONV= ‘ Nguyen’
Hãy tìm
Đề án mà nhân viên tham gia đề án có họ ‘Nguyen’
HOẶ C
Đề án có trư ở ng phòng chủ trì đề án có họ ‘Nguyen’
3/ 22/ 2013
63
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n lồ ng – Phân cấ p
SELECT DISTINCT TENDA
FROM DEAN
WHERE MADA IN (
SELECT MADA
FROM NHANVIEN, PHANCONG
WHERE MANV= MA_NVIEN AND HONV= ‘ Nguyen’)
OR
MADA IN (
SELECT MADA
FROM NHANVIEN, PHONGBAN PB, DEAN DA
WHERE MANV= TRPHG AND PB.MAPHG= DA.PHONG
AND HONV= ‘ Nguyen’)
3/ 22/ 2013
64
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n lồ ng – Phân cấ p
SELECT *
Hãy tìm
nhữ ng nhân viên
không có thân nhân
FROM NHANVIEN
WHERE MANV NOT IN (
SELECT MA_NVIEN
FROM THANNHAN )
SELECT *
FROM NHANVIEN
WHERE MANV < > ALL (
SELECT MA_NVIEN
FROM THANNHAN )
3/ 22/ 2013
65
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n lồ ng – Phân cấ p
1. Tìm nhữ ng nhân viên có lư ơ ng lớ n hơ n
lư ơ ng củ a ít nhấ t mộ t nhân viên
phòng 4
2. Tìm nhữ ng nhân viên có lư ơ ng lớ n hơ n
lư ơ ng củ a tấ t cả nhân viên phòng 4
3. Tìm nhữ ng trư ở ng phòng có tố i thiể u
mộ t thân nhân
3/ 22/ 2013
66
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n lồ ng – Phân cấ p
SELECT *
SELECT NV1.*
FROM NHANVI EN
FROM NHANVI EN NV1, NHANVI EN NV2
WHERE LUONG > ANY (
WHERE NV1.LUONG > NV2.LUONG AND
NV2.PHG= 4
SELECT LUONG
FROM NHANVI EN
WHERE PHG= 4 )
SELECT *
FROM NHANVIEN
WHERE LUONG > ALL (
SELECT *
SELECT LUONG
FROM NHANVIEN
FROM NHANVIEN
WHERE MANV IN (SELECT MA_NVIEN FROM THANNHAN)
WHERE PHG= 4 )
AND MANV IN (SELECT TRPHG FROM PHONGBAN)
3/ 22/ 2013
67
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n lồ ng – Tư ơ ng quan
SELECT MANV, TENNV
FROM NHANVIEN, PHONGBAN
WHERE TENPHG= ‘ Nghien cuu’AND PHONG= MAPHG
SELECT MANV, TENNV
FROM NHANVIEN
Tìm nhân viên
Phòng nghiên cứ u
WHERE EXISTS (
SELECT *
FROM PHONGBAN
WHERE TENPHG= ‘ Nghien cuu’AND PHONG= MAPHG )
3/ 22/ 2013
68
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n lồ ng – Tư ơ ng quan
Tìm nhân viên
có thân nhân
cùng tên, cùng phái
SELECT *
FROM NHANVIEN NV
WHERE EXISTS (
SELECT *
FROM THANNHAN TN
WHERE NV.MANV= TN.MA_NVIEN
AND NV.TENNV= TN.TENTN
AND NV.PHAI= TN.PHAI )
3/ 22/ 2013
69
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n lồ ng – Tư ơ ng quan
1. Tìm nhữ ng nhân viên không có thân
nhân nào
2. Tìm nhữ ng nhân viên có lư ơ ng lớ n hơ n
lư ơ ng củ a ít nhấ t mộ t nhân viên phòng
4
3. Tìm nhữ ng trư ở ng phòng có tố i thiể u
mộ t thân nhân
3/ 22/ 2013
70
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Truy vấ n lồ ng – Tư ơ ng quan
IN
– < tên cộ t> I N < câu truy vấ n con>
– Thuộ c tính ở mệ nh đề SELECT củ a truy vấ n con
phả i có cùng kiể u dữ liệ u vớ i thuộ c tính ở mệ nh
đề WHERE củ a truy vấ n cha
EXI STS
– Không cầ n có thuộ c tính, hằ ng số hay biể u thứ c
nào khác đứ ng trư ớ c
– Không nhấ t thiế t liệ t kê tên thuộ c tính ở mệ nh
đề SELECT củ a truy vấ n con
– Nhữ ng câu truy vấ n có = ANY hay I N đề u có thể
chuyể n thành câu truy vấ n có EXI STS
3/ 22/ 2013
71
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Phép chia trong SQL
R
A
B
C
D
E
a
a
1
a
a
1
a
b
1
a
a
1
a
b
3
a
a
1
a
b
1
a
b
1
S
bi
D
E
R÷S
a
1
ai
b
1
A
B
C
a
a
R÷S là tậ p các giá trị ai trong R sao cho
không có giá trị bi nào trong S làm cho bộ
( ai, bi) không tồ n tạ i trong R
3/ 22/ 2013
72
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
SELECT R1.A, R1.B, R1.C
FROM R R1
Phép chia trong SQL
WHERE NOT EXISTS (
SELECT *
FROM S
WHERE NOT EXISTS (
Sử dụ ng
NOT EXISTS
để biể u diễ n
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
)
)
3/ 22/ 2013
73
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Phép chia trong SQL
Sử dụ ng truy vấ n lồ ng
để biể u diễ n phép chia
SELECT R.A,R.B,R.C
FROM R
WHERE R.A+R.B+R.C NOT IN
(
SELECT Q2.A+Q2.B+Q2.C
FROM (SELECT R.A,R.B,R.C,S.* FROM R,S) Q2
WHERE Q2.A+Q2.B+Q2.C+Q2.D+Q2.E NOT IN
(SELECT R.A+R.B+R.C+R.D+R.E FROM R)
) -- Q3 LÀ A,B,C KHÔNG THAM GIA ĐẦ Y ĐỦ VÀO S
3/ 22/ 2013
74
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Phép chia trong SQL
Sử dụ ng hàm count
SELECT MANV,COUNT(MADA)
FROM PHANCONG
GROUP BY MANV
HAVING COUNT(MADA)=(SELECT COUNT(MADA) FROM DEAN)
3/ 22/ 2013
75
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Phép chia trong SQL
Tìm tên các nhân viên đư ợ c phân công làm tấ t
cả các đề án
– Tìm tên các nhân viên mà không có đề
không đư ợ c phân công làm
án nào là
– Tậ p bị chia: PHANCONG( MA_NVI EN, MADA)
– Tậ p chia: DEAN( MADA)
– Tậ p kế t quả : KQ( MA_NVI EN)
– Kế t KQ vớ i NHANVI EN để lấ y ra TENNV
3/ 22/ 2013
76
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Phép chia trong SQL
SELECT NV.TENNV
FROM NHANVIEN NV, PHANCONG PC1
WHERE NV.MANV= PC1.MANV
AND NOT EXISTS (
SELECT *
FROM DEAN DA
WHERE NOT EXISTS (
SELECT *
FROM PHANCONG PC2
WHERE PC2.MADA= DA.MADA
AND PC1.MANV= PC2.MANV ))
3/ 22/ 2013
77
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Hàm kế t hợ p
COUNT
1. COUNT( * ) đế m số dòng
2. COUNT( < tên thuộ c tính> ) đế m số giá trị khác NULL củ a
thuộ c tính
3. COUNT( DI STI NCT < tên thuộ c tính> ) đế m số giá trị khác
nhau và khác NULL củ a thuộ c tính
MI N
MAX
SUM
AVG
Các hàm kế t hợ p đư ợ c đặ t ở mệ nh đề SELECT
3/ 22/ 2013
78
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Hàm kế t hợ p
SELECT SUM(LUONG), MAX(LUONG), MIN(LUONG), AVG(LUONG)
FROM NHANVIEN
SELECT COUNT(* ) AS SL_NV
FROM NHANVIEN, PHONGBAN
WHERE PHG= MAPHG AND TENPHG= ‘ Nghien cuu’
3/ 22/ 2013
79
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Gom nhóm
Cú pháp
SELECT < danh sách các cộ t>
FROM < danh sách các bả ng>
WHERE < điề u kiệ n>
GROUP BY < danh sách các cộ t gom nhóm>
Sau khi gom nhóm: mỗ i nhóm các bộ
sẽ có cùng giá trị tạ i các thuộ c tính
gom nhóm
3/ 22/ 2013
80
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Gom nhóm
SELECT PHG, COUNT(* ) AS SL_NV
FROM NHANVIEN
GROUP BY PHG
SELECT TENPHG, COUNT(* ) AS SL_NV
FROM NHANVIEN, PHONGBAN
WHERE PHG= MAPHG
SELECT
cộ t nào
GROUP BY
cộ t đó
(ngoạ
i trừ hàm kế t hợ p
)
GROUP BY TENPHG
3/ 22/ 2013
81
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Gom nhóm
1. Vớ i mỗ i nhân viên
cho biế t mã số , họ
tên, số lư ợ ng đề án
và tổ ng thờ i gian
mà họ tham gia
2. Cho biế t nhữ ng
nhân viên tham gia
từ 2 đề án trở lên
3/ 22/ 2013
MA_NVIEN
SODA
THOIGIAN
123456789
1
32.5
123456789
2
7.5
333445555
2
10.0
333445555
3
10.0
333445555
10
10.0
888665555
20
20.0
987987987
10
35.0
987987987
30
5.0
987654321
30
20.0
987654321
20
15.0
453453453
1
20.0
453453453
2
20.0
82
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Gom nhóm
SELECT < danh sách các cộ t>
FROM < danh sách các bả ng>
WHERE < điề u kiệ n>
GROUP BY < danh sách các cộ t gom nhóm>
HAVI NG < điề u kiệ n trên nhóm>
SELECT MANV
FROM PHANCONG
GROUP BY MANV
HAVI NG COUNT( * ) > = 2
3/ 22/ 2013
83
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Gom nhóm
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 lơ n 20000
SELECT PHONG, AVG(LUONG) AS LUONG_TB
FROM NHANVIEN
GROUP BY PHONG
SELECT TENPHG, AVG(LUONG) AS LUONG_TB
HAVING AVG(LUONG) > 20000
FROM NHANVIEN, PHONGBAN
WHERE PHG= MAPHG
GROUP BY TENPHG
HAVING AVG(LUONG) > 20000
3/ 22/ 2013
84
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Gom nhóm
Mệ nh đề GROUP BY
– 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 đề HAVI NG
– Sử dụ ng các hàm kế t hợ p trong mệ nh đề SELECT
để kiể m tra mộ t số điề u kiệ n nào đó
– Chỉ kiể m tra điề u kiệ n trên nhóm, không là điề u
kiệ n lọ c 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
3/ 22/ 2013
85
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Gom nhóm – Thứ
tự
câu lệ nh
Chọ n ra nhữ ng dòng thỏ a điề u kiệ n trong
mệ nh đề WHERE
Nhữ ng dòng này sẽ đư ợ c gom thành nhiề u
nhóm tư ơ ng ứ ng vớ i mệ nh đề GROUP BY
Áp dụ ng các hàm kế t hợ p cho mỗ i nhóm
Bỏ qua nhữ ng nhóm không thỏ a điề u kiệ n
trong mệ nh đề HAVI NG
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
3/ 22/ 2013
86
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Mộ t số dạ ng truy vấ n khác
3/ 22/ 2013
87
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Mộ t số dạ ng truy vấ n khác
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 < danh sách các cộ t>
FROM R1, R2, ( < truy vấ n con> ) AS tên_bả ng
WHERE < điề u kiệ n>
3/ 22/ 2013
88
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Mộ t số dạ ng truy vấ n khác
Kế t bằ ng
SELECT < danh sách các cộ t>
FROM R1 [ I NNER] JOI N R2 ON < biể u thứ c>
WHERE < điề u kiệ n>
Kế t ngoài
FULL JOIN
SELECT < danh sách các cộ t>
FROM R1 LEFT| RI GHT [ OUTER] JOI N R2 ON < biể u thứ c>
FULL
WHERE < điề u kiệ n>
3/ 22/ 2013
89
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Cấ u trúc CASE
Cho phép kiể m tra điề u kiệ n và xuấ t
thông tin theo từ ng trư ờ ng hợ p
Cú pháp
CASE < tên cộ t>
WHEN < giá trị > THEN < biể u thứ c>
WHEN < giá trị > THEN < biể u thứ c>
…
[ ELSE < biể u thứ c> ]
END
3/ 22/ 2013
90
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
SELECT HONV, TENNV
FROM NHANVIEN
WHERE YEAR(GETDATE()) – YEAR(NGSINH) > = (CASE PHAI
WHEN ’Nam’ THEN 60
WHEN ’Nu’ THEN 55
END )
SELECT HONV, TENNV,
( CASE PHAI
CASE
WHEN ’Nam’ THEN YEAR(NGSINH) + 60
WHEN ’Nu‘ THEN YEAR(NGSINH) + 55
END ) AS NAMVEHUU
FROM NHANVIEN
3/ 22/ 2013
91
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
QUY TRÌNH XỬ
3/ 22/ 2013
LÝ CÂU TRUY VẤ N
92
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
3/ 22/ 2013
93
KNOWLEDGE FOR SHARE
4. Truy vấ n dữ liệ u
Cross-Tab
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Pivot
GROUP BY Year
3/ 22/ 2013
94
KNOWLEDGE FOR SHARE
5. Cậ p nhậ t dữ liệ u
THÊM DỮ
LIỆ U VÀO BẢ NG
Sử dụ ng để thêm 1 hay nhiề u dòng vào bả ng
Cú pháp:
I NSERT I NTO < tên bả ng> (< danh sách các thuộ c tính> )
VALUES (< danh sách các giá trị > )
Ví dụ :
3/ 22/ 2013
95
KNOWLEDGE FOR SHARE
5. Cậ p nhậ t dữ liệ u
THÊM DỮ
LIỆ U VÀO BẢ NG
INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV)
VALUES (N‘ Lê’, N‘ Văn’, N‘ Tuyể n’, ‘ 635635635’)
INSERT INTO NHANVIEN(HONV, TENLOT, 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)
3/ 22/ 2013
96
KNOWLEDGE FOR SHARE
5. Cậ p nhậ t dữ liệ u
THÊM DỮ
LIỆ U VÀO BẢ NG
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 và NOT NULL
Câu lệ nh I NSERT 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ị
3/ 22/ 2013
97
KNOWLEDGE FOR SHARE
5. Cậ p nhậ t dữ liệ u
THÊM NHIỀ U DÒNG DL VÀO BẢ NG
I NSERT I NTO < tên bả ng> (< danh sách các thuộ c tính> )
< câu truy vấ n con>
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
3/ 22/ 2013
98
KNOWLEDGE FOR SHARE
5. Cậ p nhậ t dữ liệ u
XÓA DỮ
LIỆ U TRONG BẢ NG
DELETE FROM < tên bả ng>
[ WHERE < điề u kiệ n> ]
DELETE FROM NHANVIEN
DELETE FROM NHANVIEN
DELETE FROM NHANVIEN
WHERE MANV= ‘ 345345345’
WHERE HONV= ‘ Tran’
3/ 22/ 2013
99
KNOWLEDGE FOR SHARE
5. Cậ p nhậ t dữ liệ u
XÓA DỮ
LIỆ U TRONG BẢ NG
Xóa nhân viên
ở phòng nghiên cứ u
DELETE FROM NHANVIEN
WHERE PHG IN (
SELECT MAPHG
FROM PHONGBAN
WHERE TENPHG= ‘ Nghien cuu’)
3/ 22/ 2013
100
KNOWLEDGE FOR SHARE
5. Cậ p nhậ t dữ liệ u
XÓA DỮ
LIỆ U TRONG BẢ NG
Số lư ợ ng số dòng bị xóa phụ
mệ nh đề WHERE
thuộ c vào điề u kiệ n ở
Nế u không chỉ đị nh điề u kiệ n ở mệ nh đề WHERE, tấ t
cả 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
3/ 22/ 2013
101
KNOWLEDGE FOR SHARE
5. Cậ p nhậ t dữ liệ u
XÓA DỮ
MANV
HONV
LIỆ U TRONG BẢ NG
TENLOT
TENNV
NGSINH
DCHI
PHAI
LUONG
MA_NQL
PHG
333445555 Nguyen
Thanh
Tung
12/ 08/ 1955
638 NVC Q5
Nam
40000
888665555
5
987987987 Nguyen
Manh
Hung
09/ 15/ 1962
Ba Ria VT
Nam
38000
333445555
5
453453453
Tran
Thanh
Tam
07/ 31/ 1972
543 MTL Q1
Nu
25000
333445555
5
999887777
Bui
Ngoc
Hang
07/ 19/ 1968
33 NTH Q1
Nu
38000
987654321
4
987654321
Le
Quynh
Nhu
07620/ 1951
219 TD Q3
Nu
43000
888665555
4
987987987
Tran
Hong
Quang
04/ 08/ 1969
980 LHP Q5
Nam
25000
987654321
4
888665555
Pham
Van
Vinh
11/ 10/ 1945
450 TV HN
Nam
55000
NULL
1
MA_NVIEN
SODA
333445555
10
10.0
888665555
20
20.0
987987987
10
35.0
987987987
30
5.0
987654321
30
20.0
453453453
1
20.0
3/ 22/ 2013
THOIGIAN
102
KNOWLEDGE FOR SHARE
5. Cậ p nhậ t dữ liệ u
CẬ P NHẬ T DỮ
LIỆ U TRONG BẢ NG
UPDATE < tên bả ng>
UPDATE NHANVIEN
SET < tên thuộ c tính> = < giá trị mớ i> ,
SET NGSINH= ’08/ 12/ 1965’
< tên thuộ c tính> = < giá trị mớ i> ,
…
[ WHERE < điề u kiệ n> ]
WHERE MANV= ‘ 333445555’
UPDATE NHANVIEN
SET LUONG= LUONG* 1.1
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
3/ 22/ 2013
103
KNOWLEDGE FOR SHARE
5. Cậ p nhậ t dữ liệ u
CẬ P NHẬ T DỮ
LIỆ U TRONG BẢ NG
Nhữ ng dòng thỏ a điề u kiệ n tạ i mệ nh đề WHERE
sẽ đư ợ c cậ p nhậ t giá trị mớ i
Nế u không chỉ đị nh điề u kiệ n ở mệ nh đề WHERE,
tấ t cả 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
3/ 22/ 2013
104
KNOWLEDGE FOR SHARE
6. Mộ t số vấ n đề khác
KHUNG NHÌN - VIEW
Bả ng là mộ t quan hệ đư ợ c tổ chứ c lư u
trữ vậ t lý trong CSDL
Khung nhìn cũng là mộ t quan hệ
– Không đư ợ c lư u trữ vậ t lý ( bả ng ả o)
– Không chứ a dữ liệ u
– Đư ợ c đị nh nghĩa từ nhữ ng bả ng khác
– Có thể truy vấ n hay cậ p nhậ t thông qua
khung nhìn
3/ 22/ 2013
105
KNOWLEDGE FOR SHARE
6. Mộ t số vấ n đề khác
KHUNG NHÌN - VIEW
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ư ớ i dạ ng tiệ n dụ ng nhấ t
– An toàn dữ liệ u
3/ 22/ 2013
106
KNOWLEDGE FOR SHARE
6. Mộ t số vấ n đề khác
KHUNG NHÌN - VIEW
Cú pháp
CREATE VI EW < tên khung nhìn> AS
< câu truy vấ n>
DROP VI EW < tên khung nhìn>
Bả ng ả o này có
– Danh sách thuộ c tính trùng vớ i các thuộ c tính
trong mệ nh đề SELECT
– Số dòng phụ thuộ c vào điề u kiệ n ở mệ nh đề
WHERE
– Dữ liệ u đư ợ c lấ y từ các bả ng ở mệ nh đề FROM
3/ 22/ 2013
107
KNOWLEDGE FOR SHARE
6. Mộ t số vấ n đề khác
KHUNG NHÌN - VIEW
CREATE VIEW NV_P5 AS
SELECT MANV, HONV, TENLOT, TENVN
FROM NHANVIEN
WHERE PHG= 5
CREATE VIEW TONGLNG_SLNV_PB AS
SELECT MAPHG, TENPHG, COUNT(* ) AS SLNV,
SUM(LUONG) AS TONGLNG
FROM NHANVIEN, PHONGBAN
WHERE PHG= MAPHG
GROUP BY TENPHG, MAPHG
3/ 22/ 2013
108
KNOWLEDGE FOR SHARE
6. Mộ t số vấ n đề khác
TRUY VẤ N TRÊN KHUNG NHÌN
SELECT TENNV
SELECT HONV, TENVN, TENDA, THOIGIAN
FROM NV_P5
FROM NV_P5, PHANCONG, DEAN
WHERE HONV LIKE ‘ Nguyen’
WHERE MANV= MA_NVIEN AND SODA= MADA
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
Có thể viế t câu truy vấ n dữ
từ khung nhìn và bả ng
3/ 22/ 2013
liệ u
109
KNOWLEDGE FOR SHARE
6. Mộ t số vấ n đề khác
CẬ P NHẬ T TRÊN KHUNG NHÌN
Có thể dùng các câu lệ nh I NSERT, DELETE
và UPDATE cho các khung nhìn đơ n giả n
– Khung nhìn đư ợ c xây dự ng trên 1 bả ng và có
khóa chính củ a bả ng
Không thể cậ p nhậ t dữ
–
–
–
–
Khung
Khung
Khung
Khung
cộ t
– Khung
3/ 22/ 2013
nhìn có
nhìn có
nhìn có
nhìn đư
liệ u nế u
dùng từ khóa DI STI NCT
sử dụ ng các hàm kế t hợ p
mệ nh đề SELECT mở rộ ng
ợ c xây dự ng từ bả ng có RB trên
nhìn đư ợ c xây dự ng từ
nhiề u bả ng
110
KNOWLEDGE FOR SHARE
6. Mộ t số vấ n đề khác
CẬ P NHẬ T TRÊN KHUNG NHÌN
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’
3/ 22/ 2013
111
KNOWLEDGE FOR SHARE
6. Mộ t số vấ n đề khác
INDEX – 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 *
FROM NHANVI EN
WHERE PHG= 5 AND PHAI = ‘ Nu’
3/ 22/ 2013
112
KNOWLEDGE FOR SHARE
6. Mộ t số vấ n đề khác
INDEX – CHỈ MỤ C
Cú pháp
CREATE I NDEX < tên chỉ mụ c> ON < tên bả ng> (< tên cộ t> )
DROP I NDEX < tên chỉ mụ c>
Ví dụ
CREATE INDEX PHG_IND ON NHANVIEN(PHG)
CREATE INDEX PHG_PHAI_IND ON NHANVIEN(PHG, PHAI)
3/ 22/ 2013
113
KNOWLEDGE FOR SHARE
6. Mộ t số vấ n đề khác
INDEX – CHỈ MỤ C
Nhậ n xét
– Tìm kiế m nhanh trong trư ờ 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ý???
3/ 22/ 2013
114
HẾ T
