Các ngôn ngữ thao tác trên quan...
Transcript of Các ngôn ngữ thao tác trên quan...
CHƢƠNG 4
Các ngôn ngữ thao tác trên
quan hệ
Tiến sĩ: Lê Thị Tú Kiên
Mục tiêu
Biểu diễn một câu hỏi (truy vấn) trên CSDL quan hệ
dưới dạng
Biểu thức ngôn ngữ đại số quan hệ
Biểu thức ngôn ngữ tân từ biến bộ hoặc biến miền
Câu lệnh truy vấn trong ngôn ngữ SQL
Nhận biết được sự tương đương giữa hai ngôn ngữ
hình thức đại số quan hệ và ngôn ngữ tân từ
Hai ngôn ngữ hình thức là cơ sở của ngôn ngữ
SQL Dept. of IS - FIT - HNUE
Ngôn ngữ đại số quan hệ
Dept. of IS - FIT - HNUE
Giới thiệu
Là một trong hai ngôn ngữ hình thức của mô hình
dữ liệu quan hệ
Là ngôn ngữ có tính thủ tục
Mỗi câu hỏi được biểu diễn thông qua việc áp dụng có thứ
tự một tập các phép toán
Các phép toán
Tập hợp: phép hợp, phép giao, phép hiệu và phép tích đề
các
Quan hệ: phép toán quan hệ như phép chọn, phép chiếu,
phép kết nối, phép chia Dept. of IS - FIT - HNUE
Hai quan hệ khả hợp
Hai quan hệ là khả hợp nếu chúng cùng xác
định trên một tập thuộc tính.
STT
Ho
Ten
GioiTinh
1
Trần
A
Nam
2
Trần
B
Nam
3
Trần
C
Nam
r1
STT
Ho
Ten
GioiTinh
1
Trần
D
Nữ
2
Trần
E
Nữ
r2
Dept. of IS - FIT - HNUE
Phép hợp
r1 r2 = { t | t r1 hoặc t r2}
STT
Ho
Ten
GioiTinh
1
Trần
A
Nam
2
Trần
B
Nam
3
Trần
C
Nam
STT
Ho Ten
GioiTinh
1
Trần
D
Nữ
2
Trần
E
Nữ
r2
r1
STT
Ho
Ten
GioiTinh
1
Trần
A
Nam
2
Trần
B
Nam
3
Trần
C
Nam
1
Trần
D
Nữ
2
Trần
E
Nữ
r1 r2
Dept. of IS - FIT - HNUE
Phép giao
r1 r2 = { t | t r1 và t r2}
Ho
Ten
GioiTinh
Trần
E
Nữ
Trần
B
Nam
Trần
C
Nam
r1 Ho Ten
GioiTinh
Trần
B
Nam
Trần
E
Nữ
Trần
C
Nữ
r2
Ho
Ten
GioiTinh
Trần
B
Nam
Trần
E
Nữ
r1 r2
Dept. of IS - FIT - HNUE
Phép hiệu
r1 - r2 = { t | t r1 và t r2}
Ho
Ten
GioiTinh
Trần
E
Nữ
Trần
B
Nam
Trần
C
Nam
r1 Ho
Ten
GioiTinh
Trần
B
Nam
Trần
E
Nữ
Trần
C
Nữ
r2
Ho
Ten
GioiTinh
Trần
C
Nam
r1 - r2
Ho
Ten
GioiTinh
Trần
C
Nữ
r2 – r1
Dept. of IS - FIT - HNUE
Phép tích đề các
Bộ ghép nối
r1 xác định trên U1 và r2 xác định trên U2
t1= (a1, a2, ..., an) r1 và t2 = (b1, b2, …, bm) r2
(t1,t2) = (a1, a2, ..., an, b1, b2, …, bm)
Ví dụ: U1= {Ho, Ten, GioiTinh}, U2 = {SBD, DiaChi}
r1/U1 và r2/U2
t1 = (Trần, A, Nam) và t2 = (SF001, Hà nội)
(t1, t2) = (Trần, A, Nam, SF001, Hà nội) Dept. of IS - FIT - HNUE
Phép tích đề các
Định nghĩa phép tích đề các
r1 x r2 = {t= (a1, a2, ..., an, b1, b2, …, bm) |
(a1, a2, ..., an) r1 và (b1, b2, …, bm) r2 }
Dept. of IS - FIT - HNUE
Ví dụ phép tích đề các
Ho
Trần
Đỗ
Nguyễn
r1
Ten
GioiTinh
A
Nữ
B
Nam
r2
Ho
Ten
GioiTinh
Trần
A
Nữ
Trần
B
Nam
Đỗ
A
Nữ
Đỗ
B
Nam
Nguyễn
A
Nữ
Nguyễn
B
Nam
r1 x r2
Dept. of IS - FIT - HNUE
Phép chia
r1r2 = {t | t= (am+1, am+2, ..., an):
(a1, a2, ..., am) r2,
(a1, a2, ..., am, am+1, am+2, ..., an) r1}
Dept. of IS - FIT - HNUE
Ví dụ phép chia
r1
MaSV
MaMonHoc
Diem
SP1
HP1
5.0
SP1
HP2
7.5
SP2
HP3
4.5
SP1
HP3
8.0
SP2
HP2
7.5
r2
MaMonHoc
HP1
HP2
HP3
Cho biết những mã sinh
viên đã có điểm tất cả
các học phần? Dept. of IS - FIT - HNUE
Ví dụ phép chia
r1
MaSV
MaMonHoc
Diem
SP1
HP1
5.0
SP1
HP2
7.5
SP2
HP3
4.5
SP1
HP3
8.0
SP2
HP2
7.5
MaSV
MaMonHoc
SP1
HP1
SP1
HP2
SP2
HP3
SP1
HP3
SP2
HP2
r3 MaSV, MaMonHoc(r1)
Dept. of IS - FIT - HNUE
Ví dụ phép chia
r2
MaMonHoc
HP1
HP2
HP3
MaSV
SP1
r3 r2
r3
MaSV
MaMonHoc
SP1
HP1
SP1
HP2
SP2
HP3
SP1
HP3
SP2
HP2
Dept. of IS - FIT - HNUE
Phép chiếu
Bộ thu gọn
r: xác định trên tập thuộc tính U
X: một tập con thuộc tính của tập U
t r
t[X]: chỉ một thu gọn của bộ t trên tập thuộc tính X
Ví dụ
U={Ho, Ten, GioiTinh}
X={Ho, Ten}
t=(Trần, A, Nam)
t[X]=(Trần, A)
Dept. of IS - FIT - HNUE
Phép chiếu
Định nghĩa phép chiếu
X(r) = {t[X] | t r}
Ho
Ten
GioiTinh
Trần
E
Nữ
Trần
B
Nam
Trần
C
Nam
r
Ho
Ten
Trần
E
Trần
B
Trần
C
X(r) X={Ho, Ten}
Dept. of IS - FIT - HNUE
Phép chọn
F(r) = {t | t r và F(t) đúng}
F: biểu thức logic xác định trên
miền giá trị của các thuộc tính
thuộc tập U
F: GioiTinh = „Nam‟
Ho
Ten
GioiTinh
Trần
E
Nữ
Trần
B
Nam
Trần
C
Nam
r
Ho
Ten
GioiTinh
Trần
B
Nam
Trần
C
Nam
F(r)
Dept. of IS - FIT - HNUE
Phép kết nối
r1ABr2 = {(t,q) | t r1, q r2
và t[A] q[B] đúng}
Trong đó
r1/U1 và r2/U2
: <, , =, , >,
A U1 và B U2
Các giá trị của A và B có thể so sánh được với nhau
A B: biểu thức lôgic xây dựng trên phép so sánh
được gọi là toán tử kết nối
Dept. of IS - FIT - HNUE
Ví dụ phép kết nối
Ho
Ten
NamLenLuong
Trần
E
2001
Trần
B
2000
Trần
C
2004
r1
MocTangLuong
GhiChu
2001
r2
Ho
Ten
NamLenLuong
MocTangLuong
GhiChu
Trần
E
2001
2001
Trần
B
2000
2001
21 rr ngMocTangLuogNamLenLuon
Dept. of IS - FIT - HNUE
Phép kết nối bằng và tự nhiên
Phép kết nối bằng
Nếu là toán tử kết nối bằng “=”
Phép kết nối tự nhiên
Nếu hai quan hệ r1 và r2 kết nối bằng tại hai thuộc tính
cùng tên và một trong 2 thuộc tính đó được bỏ đi
trong kết quả
Kí hiệu: r1*r2 hoặc r1 r2
Dept. of IS - FIT - HNUE
Ví dụ phép kết nối bằng
Ho
Ten
NamLenLuong
Trần
E
2001
Trần
B
2000
Trần
C
2004
r1
MocTangLuong
GhiChu
2001
r2
Ho
Ten
NamLenLuong
MocTangLuong
GhiChu
Trần
E
2001
2001
21 rr ngMocTangLuogNamLenLuon
Dept. of IS - FIT - HNUE
Ví dụ phép kết nối tự nhiên
SBD
Ho
Ten
GioiTinh
SP1
Trần
A
Nam
SP2
Trần
B
Nam
SP3
Trần
C
Nam
r1
SBD
Diem
SP1
4.5
SP2
7.5
SP3
6.0
r2
SBD
Ho
Ten
GioiTinh
Diem
SP1
Trần
A
Nam
4.5
SP2
Trần
B
Nam
7.5
SP3
Trần
C
Nam
6.0
r1 *r2
Dept. of IS - FIT - HNUE
Phép kết nối ngoài trái
Ho
Ten
NamLenLuong
Trần
E
2001
Trần
B
2000
Trần
C
2004
r1
MocTangLuong
2001
2005
r2
Ho
Ten NamLenLuong MocTangLuong
Trần
E
2001
2001
Trần
Trần
B
C
2000
2004
2001
null
21 rr ngMocTangLuogNamLenLuon
r1<ABr2 = {(t,q) ABr2 hoặc ((t, null, … , null)qr2, t[A] q[B] sai)} Dept. of IS - FIT - HNUE
Phép kết nối ngoài phải
Ho
Ten
NamLenLuong
Trần
E
2001
Trần
B
2000
Trần
C
2004
r1
MocTangLuong
2001
2005
r2
Ho
Ten NamLenLuong MocTangLuong
Trần
E
2001
2001
Trần
null
B
null
2000
null
2001
2005
21 rr ngMocTangLuogNamLenLuon
r1>ABr2 = {(t,q) r1ABr2 hoặc ((null, … , null, q)tr1, t[A] q[B] sai)}
Dept. of IS - FIT - HNUE
Phép đổi tên quan hệ
SBD
Ho
Ten
GioiTinh
SP1
Trần
A
Nam
SP2
Trần
B
Nam
SP3
Trần
C
Nam
r1 SBD
Diem
SP1
4.5
SP2
7.5
SP3
6.0
r2
SBD
Ho
Ten
GioiTinh
Diem
SP1
Trần
A
Nam
4.5
SP2
Trần
B
Nam
7.5
SP3
Trần
C
Nam
6.0
r1 *r2
Tên_mới[(danh_sách_thuộc tính)] Biểu_thức_đại_số_quan_hệ
KQTG r1*r2
KQ(SBD, Họ, Tên) (SBD, Ho, Ten)KQTG SBD
Họ
Tên
SP1
Trần
A
SP2
Trần
B
SP3
Trần
C
KQ
KKQTG
Dept. of IS - FIT - HNUE
Sử dụng các biểu thức đại số quan hệ biểu
diễn câu hỏi
Ví dụ minh họa
Dept. of IS - FIT - HNUE
Cơ sở dữ liệu quan hệ mẫu
NHAN_VIEN
DU_AN
PHONG
CHAM_CONG
Dept. of IS - FIT - HNUE
Ví dụ 1
TEN_DA((MA_DA=”D4” )(DU_AN))
(MA_DA=”D4” )(DU_AN)
Cho biết tên của dự án có mã là D4?
Dept. of IS - FIT - HNUE
Ví dụ 2
Cho biết họ tên và lương của các nhân viên làm việc ở
phòng “Nghiên cứu và phát triển”?
NHAN_VIEN * PHONG
Dept. of IS - FIT - HNUE
Ví dụ 2
(TEN-PHONG=”Nghiên cứu và phát triển )(NHAN_VIEN * PHONG)
HOTEN, LUONG((TEN-PHONG=‘Nghiên cứu và phát triển’ )(NHAN_VIEN * PHONG))
Dept. of IS - FIT - HNUE
Ví dụ 2
Cách 2:
HOTEN, LUONG(NHAN_VIEN*
(MA-DV((TEN-PHONG=”Nghiên cứu và phát triển )(PHONG))))
Dept. of IS - FIT - HNUE
Ví dụ 2
(TEN-PHONG=”Nghiên cứu và phát triển )(PHONG)
HOTEN, LUONG(NHAN_VIEN * (TEN-PHONG=”Nghiên cứu và phát triển )(PHONG))
NHAN_VIEN * (MA_DV((TEN-PHONG=”Nghiên cứu và phát triển (PHONG)))
MA_DV( (TEN-PHONG=”Nghiên cứu và phát triển (PHONG))
Dept. of IS - FIT - HNUE
Ví dụ 3
Với mỗi dự án thực hiện ở “TT khí tượng thủy văn HN”,
hãy cho biết mã số dự án đồng thời cho biết họ tên,
ngày sinh của trưởng phòng quản lý dự án này?
Dept. of IS - FIT - HNUE
Ví dụ 3
Cách 1
KQTG MA-DA, MA-DV(DIA-DIEM-DA=„TT Khí tượng thủy văn HN‟ (DU_AN))
KETQUA MA-DA, HO-TEN, NG-SINH(NHAN_VIEN⋈MA-NV=MA-TP (PHONG*KQTG))
Cách 2
KQTG NHAN_VIEN⋈MA-NV=MA-TP (DU_AN * PHONG)
KETQUA MA-DA, HO-TEN, NG-SINH( DIA-DIEM-DA=„TT Khí tượng thủy văn HN‟ (KQTG))
Dept. of IS - FIT - HNUE
Ví dụ 3
KQTG MA-DA, MA-DV((DIA-DIEM-DA=”TT Khí tượng thủy văn HN’ )(DU_AN)
PHONG*(KQTG)
MA-DA, HO-TEN, NG-SINH(NHAN_VIEN⋈MA-NV=MA-TP (PHONG*KQTG))
Dept. of IS - FIT - HNUE
Phép gộp nhóm
Dept. of IS - FIT - HNUE
Định nghĩa
[<các thuộc tính cơ sở để gộp nhóm>]F<danh sách hàm kết tập>(r)
F là kí hiệu của phép gộp nhóm
r là một quan hệ
Các thuộc tính cơ sở để gộp nhóm
Là những thuộc tính thuộc quan hệ r
Những bộ có cùng giá trị trên các thuộc tính này sẽ
được gộp vào cùng một nhóm.
Dept. of IS - FIT - HNUE
Định nghĩa
[<các thuộc tính cơ sở để gộp nhóm>]F<danh sách hàm kết tập>(r)
Các hàm kết tập
<hàm>(<thuộc tính>)
SUM, AVERAGE, MAX, MIN, COUNT
Thuộc tính thuộc quan hệ r
Dept. of IS - FIT - HNUE
Định nghĩa
[<các thuộc tính cơ sở để gộp nhóm>]F<danh sách hàm kết tập>(r)
Quan hệ kết quả
Các thuộc tính cơ sở để gộp nhóm
Các thuộc tính tương ứng với các phần tử trong danh
sách hàm kết tập
Dept. of IS - FIT - HNUE
Ví dụ 1
FAVERAGE(LUONG)(NHAN_VIEN)
Cho biết trung bình lương của công ty X?
Dept. of IS - FIT - HNUE
Ví dụ 2
MA_DVFAVERAGE(LUONG)(NHAN_VIEN)
Cho biết trung bình lương của từng phòng?
Dept. of IS - FIT - HNUE
Ví dụ 3
MA_DV, GIOI_TINHFAVERAGE(LUONG)(NHAN_VIEN)
Cho biết trung bình lương của các nhân viên
nam và trung bình lương của các nhân viên nữ
trong từng phòng?
Dept. of IS - FIT - HNUE
Ngôn ngữ tân từ
Dept. of IS - FIT - HNUE
Giới thiệu
Là ngôn ngữ có tính phi thủ tục
Cho phép mô tả các câu hỏi mà không cần quan
tâm chúng thực hiện như thế nào
Là ngôn ngữ có ảnh hưởng lớn đến các ngôn
ngữ thương mại nhự SQL (Structure Query
Language), QBE (Query By Example)
Hai loại ngôn ngữ tân từ
Ngôn ngữ tân từ biến bộ
Ngôn ngữ tân từ biến miền
Dept. of IS - FIT - HNUE
Ngôn ngữ tân từ biến bộ
Dept. of IS - FIT - HNUE
Phép tính biến bộ đơn giản
{x | ĐK(x)}
x là một biến bộ và có miền giá trị là một quan hệ
của CSDL
ĐK(x) là một biểu thức lôgic có chứa biến bộ x
Quan hệ kết quả:
các bộ t của quan hệ là miền giá trị cho biến x và làm cho
biểu thức ĐK(x) đúng
Dept. of IS - FIT - HNUE
Ví dụ
Ví dụ 1
{x | NHAN_VIEN(x) AND x.LUONG >=3000000}
Ví dụ 2
{x.HO_TEN, x.MA_DV | NHAN_VIEN(x) AND
x.LUONG >=3000000}
Dept. of IS - FIT - HNUE
Phép tính biến bộ tổng quát
{xi.A1, xj.A2, …, xk.An | ĐK(x1, x2, …, xn, xn+1, …, xn+m)}
Trong đó:
x1, x2, …, xn, xn+1, …, xn+m : các biến bộ
Aq: một thuộc tính của quan hệ (q = 1…n)
ĐK(): công phức phép tính bộ
Dept. of IS - FIT - HNUE
Các công thức nguyên tố của ĐK()
r(x): miền giá trị của biến bộ x là tập tất cả các
bộ trên quan hệ r
xi.A xj.B với : <, <=,>, >=,, =
xi.A c hoặc c xj.B với c là hằng số
Dept. of IS - FIT - HNUE
Các luật tạo nên ĐK()
Mỗi công thức nguyên tố là một công thức.
Nếu F1, F2 là công thức thì (F1 AND F2), (F1
OR F2), NOT(F1), NOT(F2) cũng là công thức.
Nếu F là công thức thì (x)(F) cũng là công
thức.
Nếu F là công thức thì (x)(F) cũng là công
thức.
Dept. of IS - FIT - HNUE
Ví dụ 1
Cho biết tên của dự án có mã là D4?
{x.TEN_DA DU_AN(x) AND x.MA_DA=„D4‟}
Dept. of IS - FIT - HNUE
Ví dụ 2
Cho biết họ tên và lương của các nhân viên làm
việc ở phòng „Nghiên cứu và phát triển‟?
{x.HO_TEN, x.LUONG y (NHAN_VIEN(x) AND PHONG(y)
AND x.MA_DV = y.MA_DV
AND y.TEN_PHONG = „Nghiên cứu và phát triển‟ ) }
Dept. of IS - FIT - HNUE
Ví dụ 3
Với mỗi dự án thực hiện ở „TT khí tượng thủy văn
HN‟, hãy cho biết mã số dự án đồng thời cho biết
họ tên, ngày sinh của trưởng phòng quản lý dự án
này?
{x.MA_DA, z.HO_TENy(DU_AN(x) AND PHONG(y) AND NHAN_VIEN(z)
AND x.MA_DV = y.MA_DV AND y.MA_TP = z.MANV
AND x.DIA_DIEM_DA = „TT Khí tượng thủy văn HN‟)}
Dept. of IS - FIT - HNUE
Ngôn ngữ tân từ biến miền
Dept. of IS - FIT - HNUE
Ngôn ngữ tân từ biến miền
Dựa trên lý thuyết logic tân từ cấp 1.
Sử dụng biến miền, nhận giá trị là một phần
tử của một miền thuộc tính trong quan hệ,
trong công thức phép tính biến miền.
Phải sử dụng n biến miền trong công thức
phép tính miền để tạo ra quan hệ kết quả có
n thành phần.
Dept. of IS - FIT - HNUE
Biểu thức tổng quát phép tính miền
{(X1,X2, ... ,Xn ) | ĐK(X1,X2, ... ,Xn,Xn+1,…Xn+m)}
Xi là biến miền, có miền giá trị là tập giá trị của
một thuộc tính trong một quan hệ
ĐK(X1,X2, ... ,Xn, Xn+1,…Xn+m) là công thức
phép tính miền
Dept. of IS - FIT - HNUE
Ví dụ 1
Cho biết tên của dự án có mã là D4?
{tenX ma_daX diadiemX ma_dvX
(DU_AN(ma_daX, tenX, diadiemX, ma_dvX)
AND ma_daX=„D4‟)}
{tenX diadiemX ma_dvX
(DU_AN(„D4‟,tenX, diadiemX, ma_dvX))}
Dept. of IS - FIT - HNUE
Ví dụ 2
Cho biết họ tên và lương của các nhân viên làm việc ở
phòng “Nghiên cứu và phát triển”?
{ho_tenX, luongXma_nvX ngay_sinhX gioi_tinhX ma_dvX ma_tpX
(PHONG (ma_dvX, “Nghiên cứu và phát triển”, ma_tpX)
AND NHAN_VIEN(ma_nvX, ho_tenX, ngay_sinhX, gioi_tinhX,
ma_dvX, luongX))}
Dept. of IS - FIT - HNUE
Ví dụ 3
Với mỗi dự án thực hiện ở “TT khí tượng thủy văn
HN”, hãy cho biết mã số dự án đồng thời cho biết
họ tên, ngày sinh của trưởng phòng quản lý dự án
này?
{ma_daX, ho_tenX ten_daX ma_dvX ten_phongX ma_tpX
ngay_sinhX gioi_tinhX luongX
(DU_AN (ma_daX, ten_daX, “TT khí tượng thủy văn HN”, ma_dvX)
AND PHONG (ma_dvX, ten_phongX, ma_tpX)
AND NHAN_VIEN (ma_tpX, ho_tenX, ngay_sinhX, gioi_tinhX,
ma_dvX, luongX)) }
Dept. of IS - FIT - HNUE
So sánh đại số quan hệ và tân từ
Tính phi thủ tục ở ngôn ngữ tân từ cao hơn
ngôn ngữ đại số quan hệ.
Khả năng biểu thị câu hỏi của ngôn ngữ đại số
quan hệ và ngôn ngữ tân từ là tương đương
nhau.
Hầu hết các ngôn ngữ hỏi của CSDL quan hệ
ngoài các phép toán cơ bản còn có thêm một
số phép toán mở rộng như hàm kết tập, phép
gộp nhóm. Dept. of IS - FIT - HNUE
Structured Query Language
SQL
Dept. of IS - FIT - HNUE
Giới thiệu về SQL
Dept. of IS - FIT - HNUE
Lịch sử ra đời và phát triển của SQL
SQL được xem là ngôn ngữ chuẩn cho các hệ
quản trị CSDL quan hệ
Năm 1976: phiên bản gốc của SQL với tên là
SEQUEL (Structured English QUEry
Language) ra đời tại phòng nghiên cứu San
Jose của hãng IBM
Dept. of IS - FIT - HNUE
Lịch sử ra đời và phát triển của SQL
Năm 1986: Viện tiêu chuẩn quốc gia Mỹ (ANSI)
và tổ chức tiêu chuẩn quốc tế (ISO) đã công bố
chuẩn SQL-86.
SQL chuẩn đã trải qua nhiều phiên bản khác
nhau: SQL-86, SQL-89, SQL-92, SQL:1999,
SQL:2003, SQL:2006, SQL:2008, SQL:2011,
SQL:2016.
Dept. of IS - FIT - HNUE
Các thành phần cơ bản của SQL
Ngôn ngữ định nghĩa dữ liệu (DDL-Data Definition
Language)
Cho phép định nghĩa các đối tượng dữ liệu
Các câu lệnh: CREATE, ALTER, DROP
Ngôn ngữ thao tác dữ liệu (DML–Data Manual
Languague)
Cho phép cập nhật và tìm kiếm dữ liệu
Các câu lệnh: INSERT, UPDATE, DELETE, SELECT,
MERGE, COMMIT, ROLLBACK, SAVEPOINT
Dept. of IS - FIT - HNUE
Các thành phần cơ bản của SQL
Ngôn ngữ điều khiển dữ liệu (DCL-Data Control
Language)
Cho phép kiểm soát quyền truy cập dữ liệu
Các câu lệnh: GRANT, REVOKE
Dept. of IS - FIT - HNUE
Ngôn ngữ định nghĩa dữ liệu
Dept. of IS - FIT - HNUE
Các kiểu miền dữ liệu
CHAR(n): xâu kí tự có độ dài cố định
VARCHAR(n): xâu kí tự có độ dài thay đổi
INT: số nguyên
SMALLINT: kiểu số nhỏ
NUMERIC(p,d): số thập phân cố định. Ví dụ numeric(3,1): 12.3
REAL, DOUBLE PRECISION: số thập phân dấu phẩy động và số
thập phân dấu phảy động độ chính xác gấp đôi
FLOAT(n): số dấu phẩy động với độ chính xác ít nhất n chữ số
DATE: Ngày-tháng-năm
TIME giờ trong ngày theo giờ, phút, giây Dept. of IS - FIT - HNUE
Câu lệnh tạo bảng
CREATE TABLE <Tên bảng> (
<tên cột 1> <kiểu dữ liệu 1> [(<kích thước 1>)],
<tên cột 2> <kiểu dữ liệu 2> [(<kích thước 2>)],
. . .
<tên cột n> <kiểu dữ liệu n> [(<kích thước n>)]
);
Dept. of IS - FIT - HNUE
Cơ sở dữ liệu mẫu
NHAN_VIEN
DU_AN
PHONG
CHAM_CONG
Dept. of IS - FIT - HNUE
Ví dụ tạo bảng PHONG
CREATE TABLE PHONG(
MA_DV CHAR(2),
TEN_PHONG VARCHAR(30),
MA_TP SMALLINT
);
Dept. of IS - FIT - HNUE
Tạo bảng với ràng buộc toàn vẹn
CREATE TABLE <Tên bảng> (
<tên cột 1> <kiểu dữ liệu 1> [(<kích thước 1>)] [CONSTRAINT <tên 1>],
<tên cột 2> <kiểu dữ liệu 2> [(<kích thước 2>)] [CONSTRAINT <tên 2>],
. . .
<tên cột n> <kiểu dữ liệu n> [(<kích thước n>)] [CONSTRAINT <tên n>]
[,CONSTRAINT <tên 1>]
[,CONSTRAINT <tên 2>]
…
[,CONSTRAINT <tên n>]
);
Dept. of IS - FIT - HNUE
Cú pháp các ràng buộc toàn vẹn
[CONSTRAINT <tên ràng buộc toàn vẹn>] |
NULL |
NOT NULL |
UNIQUE [(<tên cột i>, <tên cột j> …)] |
PRIMARY KEY [(<tên cột i>, <tên cột j> …)] |
[FOREIGN KEY [(<tên cột i>, <tên cột j> …)]]
REFERENCES <tên bảng> (<tên cột i>, <tên cột j> …) |
CHECK (<điều kiện>) |
DEFAULT <giá trị>
Dept. of IS - FIT - HNUE
Ví dụ tạo bảng PHONG với ràng buộc
CREATE TABLE PHONG(
MA_DV CHAR(2) PRIMARY KEY,
TEN_PHONG VARCHAR(30) NOT NULL UNIQUE,
MA_ TP SMALLINT
);
Dept. of IS - FIT - HNUE
Ví dụ tạo bảng NHAN_VIEN với ràng buộc
Ví dụ tạo bảng NHAN_VIEN với ràng buộc toàn vẹn
CREATE TABLE NHAN_VIEN(
MANV SMALLINT PRIMARY KEY,
HO_TEN VARCHAR(30) NOT NULL ,
NG_SINH DATE,
GIOI_TINH VARCHAR(3) DEFAULT „Nam‟,
MA_DV CHAR(2) REFERENCES PHONG(MA_DV),
LUONG INT
);
Dept. of IS - FIT - HNUE
Ví dụ tạo bảng CHAM_CONG với ràng buộc
Ví dụ tạo bảng CHAM_CONG
CREATE TABLE CHAM_CONG(
MANV SMALLINT REFERENCES NHAN_VIEN(MANV),
MA_DA CHAR(2) REFERENCES DU_AN(MA_DA),
SO_GIO INT
CONSTRAINT PK PRIMARY KEY(MANV, MA_DA)
);
Dept. of IS - FIT - HNUE
Câu lệnh xóa bảng
DROP TABLE <tên bảng>;
Ví dụ
DROP TABLE CHAM_CONG;
Dept. of IS - FIT - HNUE
Câu lệnh thêm/xóa cột
Thêm cột
ALTER TABLE <tên bảng> ADD <định nghĩa cột mới>;
Xóa cột
ALTER TABLE <tên bảng> DROP <tên cột>;
Dept. of IS - FIT - HNUE
Tạo lập chỉ mục (Index)
Tạo ra một bảng lưu trữ vị trí các bản ghi dựa
trên giá trị tăng dần của một cột nào đó
Làm tăng tốc độ tìm kiếm thông tin trong CSDL
Chỉ mục không làm thay đổi thứ tự vật lí của các
bản ghi trong bảng.
Dept. of IS - FIT - HNUE
Tạo lập chỉ mục (Index)
Tạo chỉ mục
CREATE INDEX <tên chỉ mục>
ON <tên bảng> (<tên cột 1>) [(<tên cột 2>), …];
Ví dụ
CREATE INDEX idx_HoTen
ON NHAN_VIEN(HO_TEN)
Xóa chỉ mục
DROP INDEX <tên chỉ mục>;
Dept. of IS - FIT - HNUE
Ngôn ngữ thao tác dữ liệu
Dept. of IS - FIT - HNUE
Câu lệnh SELECT – Truy vấn thông tin
SELECT [DISTINCT | ALL] {* | [<biểu thức cột> AS [<tên mới>]] [,…]}
FROM <tên bảng> [<bí danh>] [,…]
[WHERE <Điều kiện>]
[[GROUP BY <Danh sách tên cột>] [HAVING <điều kiện>]]
[ORDER BY <Danh sách tên cột> [ASC|DESC]]
Dept. of IS - FIT - HNUE
VD1 Cho biết tên của dự án có mã là D4?
SELECT TEN_DA
FROM DU_AN
WHERE MA_DA = „D4‟;
Ví dụ câu lệnh SELECT
Dept. of IS - FIT - HNUE
VD2 Cho biết họ tên và lương của các nhân viên làm
việc ở phòng “Nghiên cứu và phát triển”?
SELECT HO_TEN, LUONG
FROM NHAN_VIEN N, PHONG P
WHERE N.MA_DV= P.MA_DV
AND TEN_PHONG = „Nghiên cứu và phát triển‟;
Ví dụ câu lệnh SELECT
Dept. of IS - FIT - HNUE
VD3 Với mỗi dự án thực hiện ở “TT khí tượng thủy văn
HN”, hãy cho biết mã số dự án đồng thời cho biết họ tên,
ngày sinh của trưởng phòng quản lý dự án này?
SELECT MA_DA, HO_TEN, NG_SINH
FROM DU_AN D, PHONG P, NHANVIEN N
WHERE D.MA_DV = P.MA_DV AND P.MA_TP = N.MANV
AND MA_DA =„TT khí tượng thủy văn HN‟;
Ví dụ câu lệnh SELECT
Dept. of IS - FIT - HNUE
VD4 Đưa ra tên phòng và trung bình lương của
các phòng có trung bình lương lớn hơn 3500000
đồng?
SELECT TEN_PHONG, AVG(LUONG) AS TB_LUONG
FROM PHONG P, NHANVIEN N
WHERE P.MA_DV = N.MA_DV
GROUP BY TEN_PHONG
HAVING AVG(LUONG) > 3500000;
Ví dụ câu lệnh SELECT
Dept. of IS - FIT - HNUE
VD4 Đưa ra tên, trung bình lương của các phòng
có trung bình lương lớn hơn 3 500 000 đồng?
SELECT TEN_PHONG, TB_LUONG
FROM (SELECT TEN_PHONG, AVG(LUONG)
FROM PHONG P, NHANVIEN N
WHERE P.MA_DV = N.MA_DV
GROUP BY TEN_PHONG)
AS TG(TEN_PHONG, TB_LUONG)
WHERE TB_LUONG > 3500000;
Ví dụ câu lệnh SELECT
Dept. of IS - FIT - HNUE
Cú pháp câu lệnh
CREATE VIEW <tên khung nhìn> AS <câu lệnh SELECT>
Ví dụ:
CREATE VIEW TG AS
SELECT TEN_PHONG, AVG(LUONG) AS TB_LUONG
FROM PHONG P, NHANVIEN N
WHERE P.MA_DV = N.MA_DV
GROUP BY TEN_PHONG
SELECT TEN_PHONG, TB_LUONG FROM TG
WHERE TB_LUONG > 3500000;
Khung nhìn (View)
Dept. of IS - FIT - HNUE
Câu lệnh INSERT- thêm bản ghi
Cú pháp câu lệnh
INSERT INTO <tên bảng> [(<Danh sách cột>)]
VALUES (<Danh sách giá trị>)
INSERT INTO <tên bảng> [(<Danh sách cột>)]
SELECT …
Dept. of IS - FIT - HNUE
Câu lệnh UPDATE- sửa đổi thông tin
Cú pháp câu lệnh
UPDATE <tên bảng>
SET <tên cột 1> = <giá trị 1> [<tên cột 2> = <giá trị 2>...]
[WHERE <điều kiện>]
Dept. of IS - FIT - HNUE
Câu lệnh DELETE- xóa bản ghi
Cú pháp câu lệnh
DELETE FROM<tên bảng>
[WHERE <điều kiện>]
Dept. of IS - FIT - HNUE
Ngôn ngữ điều khiển dữ liệu
Dept. of IS - FIT - HNUE
Tạo tài khoản NSD CSDL
Cú pháp câu lệnh
CREATE USER <tên NSD>
IDENTIFIED BY <mật khẩu>
Ví dụ
CREATE USER giamdoc IDENTIFIED BY 123abc;
Dept. of IS - FIT - HNUE
Các dạng đặc quyền NSD
SELECT
INSERT
DELETE
UPDATE
REFERENCES
USASE
Dept. of IS - FIT - HNUE
Câu lệnh gán quyền cho NSD
Cú pháp câu lệnh
GRANT <danh sách đặc quyền>
ON <phần tử dữ liệu>
TO <danh sách NSD>
[WITH GRANT OPTION]
Ví dụ
GRANT SELECT, INSERT
ON NHAN_VIEN
TO giamdoc;
Dept. of IS - FIT - HNUE
Câu lệnh thu hồi quyền của NSD
Câu lệnh thu hồi quyền của NSD
REVOKE <danh sách đặc quyền>
ON <phần tử dữ liệu>
FROM <danh sách NSD>
Ví dụ
REVOKE SELECT, INSERT
ON NHAN_VIEN
FROM giamdoc;
Dept. of IS - FIT - HNUE
Kết chƣơng
Ngôn ngữ đai số quan hệ
Ngôn ngữ tân từ
Ngôn ngữ SQL
Dept. of IS - FIT - HNUE
Ví dụ 2
Trong hai biểu thức đại số dưới đây biểu diễn cho câu hỏi
ở ví dụ 2, biểu thức nào khi thực hiện sẽ tối ưu hơn trong
việc sử dụng bộ nhớ để lưu các kết quả trung gian?
1.
HOTEN, LUONG((TEN-PHONG=”Nghiên cứu và phát triển )(NHAN_VIEN * PHONG))
2.
HOTEN, LUONG(NHAN_VIEN*
(MA-DV((TEN-PHONG=”Nghiên cứu và phát triển )(PHONG))))
Dept. of IS - FIT - HNUE
Ví dụ 3
Theo các bạn, trong hai biểu thức đại số sau biểu
diễn câu hỏi trong ví dụ 3, câu hỏi nào tối ưu hơn?
Cách 1
KQTG MA-DA, MA-DV(DIA-DIEM-DA=„TT Khí tượng thủy văn HN‟ (DU_AN)
KETQUA MA-DA, HO-TEN, NG-SINH(NHAN_VIEN⋈MA-NV=MA-TP (PHONG*KQTG))
Cách 2
KQTG NHAN_VIEN⋈MA-NV=MA-TP (DU_AN * PHONG)
KETQUA MA-DA, HO-TEN, NG-SINH( DIA-DIEM-DA=„TT Khí tượng thủy văn HN‟ (KQTG))
Dept. of IS - FIT - HNUE
Ví dụ 3
MA_DV, GIOI_TINHFAVERAGE(LUONG)(NHAN_VIEN)
Theo các bạn, biểu thức
biểu diễn cho câu hỏi nào sau đây:
A. Cho biết trung bình lương của các nhân viên nam và trung
bình lương của các nhân viên nữ trong công ty ?
B. Cho biết trung bình lương của các nhân viên nam và bình
lương của các nhân viên nữ ở từng phòng?
C. Cho biết trung bình lương của các nhân viên nam và nữ ?
Dept. of IS - FIT - HNUE
Ví dụ 2
Theo các bạn, biểu thức biến bộ biểu diễn cho câu
hỏi trong ví dụ 2 cần dùng bao nhiêu biến bộ?
A. 1
B. 2
C. 3
Dept. of IS - FIT - HNUE
Ngôn ngữ định nghĩa dữ liệu
Theo các bạn, ràng buộc:
CONSTRAINT PK PRIMARY KEY(MANV, MA_DA)
Trong câu lệnh tạo bảng CHAM_CONG có nghĩa là
gì? (Chọn câu trả lời đúng)
A. Ràng buộc được đặt tên là PK và hai cột MANV,
MA_DA làm khóa chính của bảng.
B. Ràng buộc được đặt tên là PK. Trong đó, cột
MANV làm khóa chính thứ nhất và cột MA_DA
làm khóa chính thứ hai của bảng
Dept. of IS - FIT - HNUE