Chương 5: NGÔN NGỮ SQL

8/19/2019 Chương 5: NGÔN NGỮ SQL

1/115

CHƯƠNG 5

NGÔN NGỮ SQL


2/115

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


3/115

KNOWLEDGE FOR SHARE

Nội dung

1. Giới thiệu về SQL2. Kiểu dữ liệu

3. Định nghĩa dữ liệu4. Truy vấn dữ liệu

5. Cập nhật dữ liệu6. Một số vấn đề khác

3/22/2013 3


4/115

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 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/22/2013 4


5/115

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 5

Lý thuyếtChuẩn SQL-92,

SQL Server 2000

Minh họaSQL Server 2000


6/115

KNOWLEDGE FOR SHARE

1. Giới thiệu về SQL

3/22/2013 6

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).


7/115

KNOWLEDGE FOR SHARE

1. Giới thiệu về SQL

3/22/2013 7


8/115

KNOWLEDGE FOR SHARE

2. Kiểu dữ liệu

3/22/2013 8

Kiểu dữ liệu trong SQL Server


9/115

KNOWLEDGE FOR SHARE


3/22/2013 9



10/115

KNOWLEDGE FOR SHARE


3/22/2013 10



11/115

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 DOMAIN (tạo miền giá trị) – CREATE DATABASE – …

3/22/2013 11


12/115

KNOWLEDGE FOR SHARE


Để đị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 RBTV trên thuộc tính

Cú pháp

3/22/2013 12

CREATE TABLE

( [], [],…[]

)

Tạo bảng


13/115

KNOWLEDGE FOR SHARE


3/22/2013 13

CREATE TABLE NHANVIEN(

MANV CHAR (9),

HONV NVARCHAR (10),

TENLOT NVARCHAR (20),

TENNV NVARCHAR (10),

NGSINH DATETIME ,

DCHI NVARCHAR (50),

PHAI CHAR (3),

LUONG INT ,MA_NQL CHAR (9),

PHG INT

)

Tạo bảng


14/115

KNOWLEDGE FOR SHARE


3/22/2013 14

NOT NULL

NULL

UNIQUE (khóa chỉ định)

DEFAULTPRIMARY KEY

FOREIGN KEY / REFERENCES

CHECK (đk ràng buộc)

Đặt tên cho RBTVCONSTRAINT

Tạo bảng


15/115


16/115

KNOWLEDGE FOR SHARE


3/22/2013 16

CREATE TABLE PHONGBAN(

TENPB NVARCHAR (20) UNIQUE ,

MAPHG INT NOT NULL ,

TRPHG CHAR (9),

NG_NHANCHUC DATETIME DEFAULT (GETDATE()))

CREATE TABLE PHANCONG

(

MANV CHAR (9) FOREIGN KEY (MANV)

REFERENCES NHANVIEN(MANV),MADA INT REFERENCES DEAN(MADA),

THOIGIAN DECIMAL(3,1),

PRIMARY KEY (MANV,MADA)

)

Tạo bảng


17/115


18/115

KNOWLEDGE FOR SHARE


3/22/2013 18

CREATE TABLE PHANCONG

(

MANV CHAR (9),

MADA INT ,

THOIGIAN DECIMAL(3,1),CONSTRAINT PC_MANV_MADA_PK

PRIMARY KEY (MANV, MADA),

CONSTRAINT PC_MANV_FK FOREIGN KEY (MANV)

REFERENCES NHANVIEN(MANV),

CONSTRAINT PC_MADA_FK FOREIGN KEY (MADA)

REFERENCES DEAN(MADA)

)

Tạo bảng


19/115

KNOWLEDGE FOR SHARE


Thay đổi cấu trúc 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

Sửa bảng


20/115

KNOWLEDGE FOR SHARE


3/22/2013 20

S ử a b ả n

g

ALTER TABLE ADD COLUMN []

ALTER TABLE DROP COLUMN

ALTER TABLE ALTER COLUMN

ALTER TABLE ADD

CONSTRAINT ,

CONSTRAINT ,…

ALTER TABLE DROP


21/115

KNOWLEDGE FOR SHARE


3/22/2013 21

Sửa bảng

ALTER TABLE NHANVIEN ADD NGHENGHIEP CHAR (20)

ALTER TABLE NHANVIENDROP COLUMN NGHENGHIEP

ALTER TABLE NHANVIEN ALTER COLUMN NGHENGHIEP CHAR (50)


22/115

KNOWLEDGE FOR SHARE


3/22/2013 22

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)

Sửa bảng


23/115

KNOWLEDGE FOR SHARE


Đượ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ụ

3/22/2013 23

Xóa bảng

DROP TABLE

DROP TABLE NHANVIEN

DROP TABLE PHONGBAN

DROP TABLE PHANCONG


24/115

KNOWLEDGE FOR SHARE


3/22/2013 24

Xóa bảngNHANVIEN

TENNVHONV TENLOT MANV NGSINH DCHI PHAI LUONG MA_NQL PHG

PHONGBAN

TRPHGTENPHG MAPHG NG_NHANCHUC


25/115

KNOWLEDGE FOR SHARE


Tạo ra một kiểu dữ liệu mới kế thừanhững kiểu dữ liệu có sẳn Cú pháp

Ví dụ

3/22/2013 25

Tạo miền GT

CREATE DOMAIN AS

CREATE DOMAIN kieu_MANV AS CHAR(9)


26/115

KNOWLEDGE FOR SHARE

4. Truy vấn dữ liệu

1. Truy vấn cơ bản2. 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


27/115

KNOWLEDGE FOR SHARE


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

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à b ag (đa bộ) quan hệ là se t (tập hợp)

3/22/2013 27

Phép toán ĐSQH Một số bổ sung


28/115

KNOWLEDGE FOR SHARE


– Tên các cột cần đượ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

– 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: , ≤ , ≥ , ≠() , = , LIKE và BETWEEN

3/22/2013 28

Truy vấn cơ bảnSELECT

FROM

WHERE


29/115

KNOWLEDGE FOR SHARE


3/22/2013 29

Truy vấn cơ bản

SELECT

FROM WHERE

×

SELECT L

FROM R,S

WHERE C

L (C (RxS))


30/115

KNOWLEDGE FOR SHARE


3/22/2013 30

Truy vấn cơ bảnSELECT *

FROM NHANVIEN

WHERE PHG=5

Lấy tất cả các cột củaquan hệ kết quả

PHG=5 (NHANVIEN)

TENNVHONV NGSINH DCHI PHAI LUONG PHG

TungNguyen 12/08/1955 638 NVC Q5 Nam 40000 5

HungNguyen 09/15/1962 Ba Ria VT Nam 38000 5

333445555

987987987

MANV MA_NQL

888665555

333445555

TENLOT

Thanh

Manh


31/115


32/115

KNOWLEDGE FOR SHARE


3/22/2013 32


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(MANV,HONV,TENLOT,TENNV(PHG=5∧PHAI=‘Nam’ (NHANVIEN)))

TÊNHỌ

TungNguyen

HungNguyen

TÊN LÓT

Thanh

Manh

333445555

987987987

MANV

Tên bí danh


33/115

KNOWLEDGE FOR SHARE


3/22/2013 33


SELECT MANV, HONV + ‘ ’ + TENLOT + ‘ ’ + TENNV AS [HỌ TÊN]FROM NHANVIEN


HỌ TÊN

Nguyen Thanh Tung

Nguyen Manh Hung

333445555

987987987

MANV

Mở rộng


34/115

KNOWLEDGE FOR SHARE


3/22/2013 34

Truy vấn cơ bảnSELECT MANV, LUONG*1.1 AS [LUONG10%]

FROM NHANVIEN


MANV,LUONG10%(MANV,LUONG*1.1(PHG=5∧PHAI=‘Nam’ (NHANVIEN)))

LUONG10%

33000

27500

333445555

987987987

MANV

Mở rộng


35/115

KNOWLEDGE FOR SHARE


3/22/2013 35


SELECT LUONG

FROM NHANVIENWHERE PHG=5 AND PHAI= ‘Nam’

Loại bỏ các dòng trùng nhau

- Tốn chi phí

- Người dùng muốn thấy

LUONG

30000

25000

25000

38000

LUONG

30000

25000

38000

SELECT DISTINCT LUONG

FROM NHANVIENWHERE PHG=5 AND PHAI= ‘Nam’


36/115

KNOWLEDGE FOR SHARE


Cho biết MANV và TENNV làm việc ởphòng ‘Nghien cuu’

3/22/2013 36


R1 NHANVIEN PHG=MAPHG PHONGBANKQ MANV, TENNV (TENPHG=‘Nghien cuu’ (R1))

SELECT MANV, TENNV

FROM NHANVIEN, PHONGBANWHERE TENPHG= ‘Nghien cuu’ AND PHG=MAPHG

Biểu thức luận lý

TRUE TRUE


37/115

KNOWLEDGE FOR SHARE


3/22/201337


SELECT MANV, TENNV

FROM NHANVIEN, PHONGBAN

WHERE (TENPHG= ‘Nghien cuu’ OR TENPHG= ‘Quan ly’ ) AND PHG=MAPHG

Độ ưu tiên


38/115

KNOWLEDGE FOR SHARE


3/22/201338

Truy vấn cơ bảnSELECT MANV, TENNV

FROM NHANVIEN

WHERE LUONG>=20000 ANDLUONG


39/115

KNOWLEDGE FOR SHARE


3/22/201339


FROM NHANVIEN

WHERE DCHI LIKE ‘Nguyen _ _ _ _’

LIKE

SELECT MANV, TENNV

FROM NHANVIENWHERE DCHI LIKE ‘Nguyen %’

Chuỗi bất kỳ

Ký tự bất kỳ

SELECT MANV, TENNV

FROM NHANVIEN

WHERE HONV NOT NOT LIKE ‘Nguyen’


40/115

KNOWLEDGE FOR SHARE


3/22/201340


FROM NHANVIEN

WHERE DCHI LIKE ‘% Nguyens _%’ ESCAPE ‘s’

ESCAPE

‘Nguyen_’


41/115

KNOWLEDGE FOR SHARE


3/22/201341

Truy vấn cơ bảnNgày giờ

SELECT MANV, TENNVFROM NHANVIEN

WHERE NGSINH BETWEEN ‘1955-12-08’ AND ‘1966-07-19’

YYYY-MM-DD

MM/DD/YYYY

‘1955-12-08’

’12/08/1955’

‘December 8, 1955’

HH:MI:SS’17:30:00’

’05:30 PM’

‘1955-12-08 17:30:00’

SELECT MANV, TENNV

FROM NHANVIEN

WHERE NGSINH = ‘1955-12-08’


42/115

KNOWLEDGE FOR SHARE


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ứcsang 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 sangkiểu chuỗi nhằm quy định khuôn dạng dữ liệu đượchiển thị.

3/22/201342

Truy vấn cơ bảnHàm CONVERT


43/115

KNOWLEDGE FOR SHARE


3/22/201343

Truy vấn cơ bảnHOADON(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


44/115

KNOWLEDGE FOR SHARE


3/22/201344


45/115

KNOWLEDGE FOR SHARE


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ại (value withheld)

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/201345

Truy vấn cơ bảnNULL


46/115

KNOWLEDGE FOR SHARE


3/22/201346

Truy vấn cơ bảnNULLSELECT MANV, TENNV

FROM NHANVIEN

WHERE MA_NQL IS NULL

SELECT MANV, TENNV

FROM NHANVIEN

WHERE MA_NQL IS NOT NULL


47/115

KNOWLEDGE FOR SHARE


3/22/201347


WHERE TRUE

SELECT MANV, MAPHG


Không sử dụngmệnh đề WHERE(Tích chéo RxS)

MAPHG

1

4

333445555

333445555

MANV

5

1987987987

987987987

333445555

4

5987987987

… …


48/115

KNOWLEDGE FOR SHARE


3/22/201348


SELECT TENPHG, DIADIEM

FROM PHONGBAN, DDIEM_PHG

WHERE MAPHG=MAPHG

Tên bí danh

SELECT TENPHG, DIADIEM

FROM PHONGBAN PB, DDIEM_PHG DD

WHERE PB.MAPHG=DD.MAPHG

SELECT TENNV, NGSINH, TENTN, NGSINHFROM NHANVIEN, THANNHAN

WHERE MANV=MA_NVIEN SELECT TENNV, NV.NGSINH, TENTN, TN.NGSINH

FROM NHANVIEN NV, THANNHAN TN

WHERE MANV=MA_NVIEN


49/115

KNOWLEDGE FOR SHARE


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 ấy2. 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 ThanhTung” phụ trách trực tiếp

3/22/201349

Truy vấn cơ bản – Bài tập


50/115

KNOWLEDGE FOR SHARE


Dùng để hiển thị kết quả câu truy vấntheo một thứ tự nào đó

Cú pháp

– ASC: tăng (mặc định) – DESC: giảm

3/22/2013 50

Truy vấn cơ bản – Sắp xếp

SELECT

FROM

WHERE

ORDER BY

ORDER BY


51/115

KNOWLEDGE FOR SHARE


3/22/2013 51

Truy vấn cơ bản – Sắp xếpORDER BY SELECT MA_NVIEN, SODA

FROM PHANCONG

ORDER BY MA_NVIEN DESC, SODA

SODA

10

30

999887777

999887777

MA_NVIEN

10

30987987987

987654321

987987987

10

20987654321

30987654321


52/115

KNOWLEDGE FOR SHARE


DISTINCT BETWEEN LIKE ESCAPE NGÀY GIỜ CONVERT

NULL TÍCH CHÉO ĐẶT TÊN ORDER BY

3/22/2013 52

Truy vấn cơ bản – Tổng kết


53/115

KNOWLEDGE FOR SHARE


SQL có cài đặt các phép toán – Hội (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ữ lại các bộ trùng nhau

• UNION ALL• INTERSECT ALL• EXCEPT ALL

3/22/2013 53

Phép toán tập hợp trong SQL


54/115

KNOWLEDGE FOR SHARE


3/22/2013 54

Phép toán tập hợp trong SQLSELECT FROM WHERE

UNION [ALL]

SELECT FROM WHERE

SELECT FROM WHERE

INTERSECT [ALL]

SELECT FROM WHERE

SELECT FROM WHERE

EXCEPT [ALL]

SELECT FROM WHERE


55/115

KNOWLEDGE FOR SHARE


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’

3/22/2013 55


SELECT MADA

FROM NHANVIEN, PHANCONG

WHERE MANV=MA_NVIEN AND HONV= ‘Nguyen’

UNIONSELECT MADA

FROM NHANVIEN, PHONGBAN, DEAN

WHERE MANV=TRPHG AND MAPHG=PHONG AND HONV= ‘Nguyen’

Phép hợp


56/115

KNOWLEDGE FOR SHARE


Tìm nhân viên có người thân cùngtên và cùng giới tính

3/22/2013 56


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

Phép giao

KNOWLEDGE FOR SHARE


57/115

KNOWLEDGE FOR SHARE


Tìm những nhân viên không có thânnhân nào

3/22/2013 57


Phép trừ

SELECT MANV FROM NHANVIEN

EXCEPT

SELECT MA_NVIEN AS MANV FROM THANNHAN

SELECT MANVFROM NHANVIEN

WHERE MANV NOT IN

(SELECT MANV FROM THANNHAN)

KNOWLEDGE FOR SHARE


58/115

KNOWLEDGE FOR SHARE


Tìm nhân viên có người thân

3/22/201358

Truy vấn lồng

SELECT MANV

FROM NHANVIENWHERE MANV IN

(SELECT MANV FROM THANNHAN)

SELECT DISTINCT NV.MANV,HONV + ‘ ’ + TENNV [HỌ TÊN]

FROM NHANVIEN NV, THANNHAN TNWHERE NV.MANV=TN.MANV

KNOWLEDGE FOR SHARE


59/115

KNOWLEDGE FOR SHARE


3/22/201359

Truy vấn lồng

SELECT

FROM WHERE (

SELECT

FROM

WHERE )

Câu truy vấn cha

(Outer query)

Câu truy vấn con(Subquery)

KNOWLEDGE FOR SHARE


60/115

KNOWLEDGE FOR SHARE


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

– – So sánh tập hợp thường đi cùng với một số toán tử

• IN, NOT IN• ALL

• ANY hoặc SOME – Kiểm tra sự tồn tại

• EXISTS• NOT EXISTS

3/22/201360

Truy vấn lồng

KNOWLEDGE FOR SHARE


61/115

KNOWLEDGE FOR SHARE


3/22/201361

SELECT *

FROM NHANVIEN

WHERE MLUONG >= ALL

(SELECT MLUONG FROM NHANVIEN)

SELECT *

FROM NHANVIEN

WHERE MLUONG >= ANY/SOME

(SELECT MLUONG FROM NHANVIEN)

Truy vấn lồng

KNOWLEDGE FOR SHARE


62/115

KNOWLEDGE FOR SHARE


1. Lồng phân cấp – 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ẽ được thực hiệntrước

2. Lồng tương quan

– Mệnh đề WHERE của truy vấn con tham chiếu ít nhấtmộ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ệnnhiều lần , mỗi lần tương ứng với một bộ của truy vấncha

3/22/2013

62

Truy vấn lồng

KNOWLEDGE FOR SHARE


63/115

KNOWLEDGE FOR SHARE


3/22/2013

63

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’

Truy vấn lồng – Phân cấpSELECT MADAFROM 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’

KNOWLEDGE FOR SHARE


64/115

KNOWLEDGE FOR SHARE


3/22/2013

64

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 DAWHERE MANV=TRPHG AND PB.MAPHG=DA.PHONG

AND HONV= ‘Nguyen’ )

Truy vấn lồng – Phân cấp

KNOWLEDGE FOR SHARE


65/115


3/22/2013 65

Truy vấn lồng – Phân cấpSELECT *

FROM NHANVIEN

WHERE MANV NOT IN (

SELECT MA_NVIEN

FROM THANNHAN )

SELECT *

FROM NHANVIENWHERE MANV ALL (

SELECT MA_NVIEN

FROM THANNHAN )

Hãy tìmnhững nhân viên

không có thân nhân

KNOWLEDGE FOR SHARE


66/115


1. Tìm những nhân viên có lương lớn hơnlươ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ơnlươ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ểumột thân nhân

3/22/2013

66

Truy vấn lồng – Phân cấp

KNOWLEDGE FOR SHARE


67/115


3/22/2013

67

Truy vấn lồng – Phân cấpSELECT *

FROM NHANVIEN

WHERE LUONG > ANY (

SELECT LUONG

FROM NHANVIEN

WHERE PHG=4 )

SELECT NV1.*

FROM NHANVIEN NV1, NHANVIEN NV2

WHERE NV1.LUONG > NV2.LUONG AND

NV2.PHG=4

SELECT *

FROM NHANVIEN

WHERE LUONG > ALL (

SELECT LUONG

FROM NHANVIEN

WHERE PHG=4 )

SELECT *FROM NHANVIEN

WHERE MANV IN (SELECT MA_NVIEN FROM THANNHAN)

AND MANV IN (SELECT TRPHG FROM PHONGBAN)

KNOWLEDGE FOR SHARE


68/115


3/22/2013

68

SELECT MANV, TENNV

FROM NHANVIEN

WHERE EXISTS (

SELECT *FROM PHONGBAN

WHERE TENPHG= ‘Nghien cuu’ AND PHONG=MAPHG )

Truy vấn lồng – Tương quanSELECT MANV, TENNVFROM NHANVIEN, PHONGBAN

WHERE TENPHG= ‘Nghien cuu’ AND PHONG=MAPHG

Tìm nhân viênPhòng nghiên cứu

KNOWLEDGE FOR SHARE


69/115


3/22/2013

69

Truy vấn lồng – Tương quanTìm nhân viêncó 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 )

KNOWLEDGE FOR SHARE


70/115


1. Tìm những nhân viên không có thânnhân nào

2. Tìm những nhân viên có lương lớn hơnlương của ít nhất một nhân viên phòng4

3. Tìm những trưởng phòng có tối thiểumột thân nhân

3/22/2013

70

Truy vấn lồng – Tương quan

KNOWLEDGE FOR SHARE


71/115


IN – IN – 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 EXISTS

– Không cần có thuộc tính, hằng số hay biểu thứcnà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 IN đều có thể

chuyển thành câu truy vấn có EXISTS

3/22/2013

71

Truy vấn lồng – Tương quan

KNOWLEDGE FOR SHARE


72/115


R ÷S là tập các giá trị ai trong R sao chokhô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

Phép chia trong SQL A B

a

a

a

a

a

a

a

a

C D

a

b

a

a

b

a

b

b

E

1

3

1

1

1

1

1

1

R D E

a

S

b

1

1

A B C

a

a

R ÷S

aibi

KNOWLEDGE FOR SHARE


73/115


3/22/2013

73

SELECT R1.A, R1.B, R1.CFROM R R1

WHERE NOT EXISTS (

SELECT *

FROM SWHERE 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

)

)

Sử dụng

NOT EXISTSđể biểu diễn

Phép chia trong SQL

KNOWLEDGE FOR SHARE


74/115


3/22/2013

74


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) Q2WHERE 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

Sử dụng truy vấn lồng

để biểu diễn phép chia

KNOWLEDGE FOR SHARE


75/115


3/22/2013

75


SELECT MANV,COUNT(MADA)FROM PHANCONG

GROUP BY MANVHAVING COUNT(MADA)=(SELECT COUNT(MADA) FROM DEAN)

Sử dụng hàm count

KNOWLEDGE FOR SHARE

ấ


76/115


Tìm tên các nhân viên được phân công làm tấtcả 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, MADA)

– Tập chia: DEAN(MADA)

– Tập kết quả: KQ(MA_NVIEN)

– Kết KQ với NHANVIEN để lấy ra TENNV

3/22/2013

76


KNOWLEDGE FOR SHARE

ấ


77/115


3/22/2013

77

Phép chia trong SQLSELECT 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 ))

KNOWLEDGE FOR SHARE

ấ


78/115


COUNT1. COUNT(*) đếm số dòng2. COUNT() đếm số giá trị khác NULL của

thuộc tính3. COUNT(DISTINCT ) đếm số giá trị khác

nhau và khác NULL của thuộc tính

MIN

MAX SUM AVG Các hàm kết hợp được đặt ở mệnh đề SELECT

3/22/2013

78

Hàm kết hợp

KNOWLEDGE FOR SHARE

ấ


79/115


3/22/2013

79

Hàm kết hợpSELECT SUM(LUONG), MAX(LUONG), MIN(LUONG), AVG(LUONG)

FROM NHANVIEN

SELECT COUNT(*) AS SL_NV


WHERE PHG=MAPHG AND TENPHG= ‘Nghien cuu’

KNOWLEDGE FOR SHARE

ấ


80/115


Cú pháp

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ínhgom nhóm

3/22/2013

80

Gom nhóm

SELECT

FROM WHERE

GROUP BY

KNOWLEDGE FOR SHARE

ấ


81/115


3/22/2013

81

Gom nhómSELECT PHG, COUNT(*) AS SL_NV

FROM NHANVIEN

GROUP BY PHG

SELECT TENPHG, COUNT(*) AS SL_NV


WHERE PHG=MAPHGGROUP BY TENPHG

SELECT

cột nàoGROUP BY

cột đó

(ngoại trừ hàm kết hợp)

KNOWLEDGE FOR SHARE

ấ


82/115


1. Với mỗi nhân viêncho biết mã số, họtên, số lượng đề án

và tổng thời gianmà họ tham gia

2. Cho biết những

nhân viên tham giatừ 2 đề án trở lên

3/22/2013 82

Gom nhóm SODA THOIGIAN1 32.5

2 7.5

123456789

123456789

MA_NVIEN

2 10.0

3 10.0

333445555

33344555510 10.0333445555

20 20.0

10 35.0

888665555

987987987

30 5.0987987987

30 20.0987654321

20 15.0987654321

1 20.0453453453

2 20.0453453453

KNOWLEDGE FOR SHARE

ấ


83/115


3/22/2013 83

Gom nhómSELECT

FROM

WHERE

GROUP BY HAVING

SELECT MANV

FROM PHANCONG

GROUP BY MANV

HAVING COUNT(*) >= 2

KNOWLEDGE FOR SHARE

ấ


84/115


3/22/2013 84

Gom nhóm

SELECT PHONG, AVG(LUONG) AS LUONG_TB

FROM NHANVIEN

GROUP BY PHONG

HAVING AVG(LUONG) > 20000

SELECT TENPHG, AVG(LUONG) AS LUONG_TB


WHERE PHG=MAPHGGROUP BY TENPHG

HAVING AVG(LUONG) > 20000

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

KNOWLEDGE FOR SHARE

ấ ệ


85/115


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 đề HAVING

– 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ềukiệ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

Gom nhóm

KNOWLEDGE FOR SHARE

4 ấ ữ iệ


86/115


Chọn ra những dòng thỏa điều kiện trongmệnh đề WHERE

Những dòng này sẽ được gom thành nhiềunhó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 đề HAVING 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

Gom nhóm – Thứ tự câu lệnh

KNOWLEDGE FOR SHARE

4 T ấ dữ liệ


87/115


3/22/2013 87

Một số dạng truy vấn khác

KNOWLEDGE FOR SHARE

4 T ấ dữ liệ


88/115


Kết quả trả về của một câu truy vấnphụ 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

3/22/2013 88


SELECT FROM R1, R2, () AS tên_bảng

WHERE

KNOWLEDGE FOR SHARE

4 T ấ dữ liệ


89/115


Kết bằng

Kết ngoài

3/22/2013 89


SELECT

FROM R1 [INNER ] JOIN R2 ON

WHERE

SELECT

FROM R1 LEFT|RIGHT [OUTER ] JOIN R2 ON

WHERE

FULL JOIN

FULL

KNOWLEDGE FOR SHARE

4 T ấ dữ liệ


90/115


Cho phép kiểm tra điều kiện và xuấtthông tin theo từng trường hợp

Cú pháp

3/22/2013 90

Cấu trúc CASE

CASE

WHEN THEN

WHEN THEN

…

[ELSE ]

END


91/115

KNOWLEDGE FOR SHARE

4 T ấ dữ liệ


92/115


3/22/2013 92

QUY TRÌNH XỬ LÝ CÂU TRUY VẤN

KNOWLEDGE FOR SHARE

4 T ấ dữ liệ


93/115


3/22/2013 93

KNOWLEDGE FOR SHARE

4 T ấ dữ liệ


94/115


3/22/2013 94

SELECT Year,

SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,



SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4

FROM Pivot

GROUP BY Year

Cross-Tab

KNOWLEDGE FOR SHARE

5 Cập nhật dữ liệ


95/115

5. Cập nhật dữ liệu

Sử dụng để thêm 1 hay nhiều dòng vào bảng Cú pháp:

Ví dụ:

3/22/2013

95

THÊM DỮ LIỆU VÀO BẢNG

INSERT INTO ()

VALUES ()

KNOWLEDGE FOR SHARE

5 Cập nhật dữ liệu


96/115


3/22/2013

96

THÊM DỮ LIỆU VÀO BẢNGINSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV)

VALUES (N ‘Lê’ , N ‘Văn’ , N ‘Tuyển’ , ‘635635635’ )

INSERT INTO NHANVIEN

VALUES ( ‘Le’ , ‘Van’ , ‘Tuyen’ , ‘635635635’ , ’12/30/1952’ , ’98 HV’ , ‘Nam’ , ‘37000’ , 4)

INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV, DCHI)

VALUES ( ‘Le’ , ‘Van’ , ‘Tuyen’ , ‘635635635’ , NULL)

KNOWLEDGE FOR SHARE



97/115


Thứ tự các giá trị phải trùng với thứ tự cáccột

Có thể thêm giá trị NULL ở những thuộc tínhkhông là 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ị

3/22/2013

97

THÊM DỮ LIỆU VÀO BẢNG

KNOWLEDGE FOR SHARE



98/115


3/22/2013

98

THÊM NHIỀU DÒNG DL VÀO BẢNGINSERT INTO ()

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, PHONGBANWHERE PHG=MAPHGGROUP BY TENPHG

KNOWLEDGE FOR SHARE



99/115


3/22/2013

99

XÓA DỮ LIỆU TRONG BẢNG

DELETE FROM

[WHERE ]

DELETE FROM NHANVIEN

WHERE HONV= ‘Tran’


WHERE MANV= ‘345345345’


KNOWLEDGE FOR SHARE



100/115


3/22/2013

100


WHERE PHG IN (

SELECT MAPHGFROM PHONGBAN

WHERE TENPHG= ‘Nghien cuu’ )


Xóa nhân viênở phòng nghiên cứu

KNOWLEDGE FOR SHARE



101/115


Số lượng số dòng bị xóa phụ thuộc vào điều kiện ởmệnh đề WHERE

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



102/115


3/22/2013

102

XÓA DỮ LIỆU TRONG BẢNGTENNVHONV NGSINH DCHI PHAI LUONG PHG

TungNguyen 12/08/1955 638 NVC Q5 Nam 40000 5

HungNguyen 09/15/1962 Ba Ria VT Nam 38000 5

333445555

987987987

MANV MA_NQL

888665555

333445555

TENLOT

Thanh

Manh

HangBui 07/19/1968 33 NTH Q1 Nu 38000 4999887777 987654321Ngoc

NhuLe 07620/1951 219 TD Q3 Nu 43000 4987654321 888665555Quynh

VinhPham 11/10/1945 450 TV HN Nam 55000 1888665555 NULL Van

TamTran 07/31/1972 543 MTL Q1 Nu 25000 5453453453 333445555Thanh

QuangTran 04/08/1969 980 LHP Q5 Nam 25000 4987987987 987654321Hong

SODA THOIGIANMA_NVIEN

10 10.0333445555

20 20.0888665555

30 20.0987654321

1 20.0453453453

10 35.0987987987

30 5.0987987987

KNOWLEDGE FOR SHARE



103/115


3/22/2013103

CẬP NHẬT DỮ LIỆU TRONG BẢNGUPDATE

SET =,

=,

…

[WHERE ]

UPDATE NHANVIEN

SET NGSINH=’08/12/1965’

WHERE MANV= ‘333445555’

UPDATE NHANVIEN

SET LUONG=LUONG*1.1

Với đề án có mã số 10, hãy thay đổinơi thực hiện đề án thành ‘Vung Tau’ vàphòng ban phụ trách là phòng 5


104/115

KNOWLEDGE FOR SHARE

6 Một số vấn đề khác


105/115

6. Một số vấn đề khác

Bảng là một quan hệ được tổ chức lưutrữ 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/2013105

KHUNG NHÌN - VIEW

KNOWLEDGE FOR SHARE



106/115


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/2013106

KHUNG NHÌN - VIEW

KNOWLEDGE FOR SHARE



107/115


Cú pháp

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/2013107

KHUNG NHÌN - VIEW

CREATE VIEW AS

DROP VIEW

KNOWLEDGE FOR SHARE



108/115


3/22/2013108

KHUNG NHÌN - VIEWCREATE VIEW NV_P5 ASSELECT 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, PHONGBANWHERE PHG=MAPHG

GROUP BY TENPHG, MAPHG

KNOWLEDGE FOR SHARE



109/115


3/22/2013109

TRUY VẤN TRÊN KHUNG NHÌNSELECT TENNV

FROM NV_P5

WHERE HONV LIKE ‘Nguyen’

Tuy không chứa dữ liệu nhưngcó thể thực hiện các câu truy vấntrên khung nhìn

Có thể viết câu truy vấn dữ liệutừ khung nhìn và bảng

SELECT HONV, TENVN, TENDA, THOIGIAN

FROM NV_P5, PHANCONG, DEAN

WHERE MANV=MA_NVIEN AND SODA=MADA

KNOWLEDGE FOR SHARE



110/115


Có thể dùng các câu lệnh INSERT, DELETEvà 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ữ liệu nếu – Khung nhìn có dùng từ khóa DISTINCT – 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 – Khung nhìn được xây dựng từ bảng có RB trên

cột – Khung nhìn được xây dựng từ nhiều bảng

3/22/2013110

CẬP NHẬT TRÊN KHUNG NHÌN

KNOWLEDGE FOR SHARE



111/115


Sửa lại họ cho nhân viên mã ‘123456789’ ở phòng 5 là ‘Pham’

3/22/2013111

CẬP NHẬT TRÊN KHUNG NHÌN

UPDATE NV_P5

SET HONV=‘Pham’

WHERE MANV= ‘123456789’

KNOWLEDGE FOR SHARE



112/115


Chỉ mục trên thuộc tính A là một cấutrúc dữ liệu làm cho việc tìm kiếm mẫutin có chứa A hiệu quả hơn

3/22/2013112

INDEX – CHỈ MỤC

SELECT *

FROM NHANVIEN

WHERE PHG=5 AND PHAI= ‘Nu’

KNOWLEDGE FOR SHARE



113/115


Cú pháp

Ví dụ

3/22/2013113


CREATE INDEX ON ()

CREATE INDEX PHG_IND ON NHANVIEN(PHG)

CREATE INDEX PHG_PHAI_IND ON NHANVIEN(PHG, PHAI)

DROP INDEX

KNOWLEDGE FOR SHARE



114/115


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/2013114



115/115

HẾT

Chương 5: NGÔN NGỮ SQL

Documents

Transcript of Chương 5: NGÔN NGỮ SQL