Tham khảo tài liệu 'tin học hệ quản trị - toán tin dữ liệu phần 2', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả
AMBIENT/
Chủ đề:
Nội dung Text: Tin Học Hệ Quản Trị - Toán Tin Dữ Liệu phần 2
- 11
Bài gi ng tóm t t H qu n tr cơ s d li u
Alter table table_name
Drop { [constraint ] constraint_name } [,…n]
Ví d :
Alter table SINHVIEN
drop constraint u_CMND, chk_Nam
e. Rule
− Rule là m t qui nh chung ư c t o ra trong m t CSDL.
− M t rule có th ư c áp d ng cho nhi u thu c tính c a nhi u b ng khác nhau, ho c
cho các ki u d li u ngư i dùng nh nghĩa trong database.
T o rule
Cú pháp:
Create rule rule_name
as logical_expression
(trong ó “logical_expression” ph i ch a m t bi n. Bi n này tương ng v i i tư ng s
ư c áp d ng rule).
Ví d :
create rule r_SoDuong
as @value >0
K t bu c/ g k t bu c rule
K t bu c rule: dùng th t c:
sp_bindrule ‘rule_name’, ‘object’, [ ‘futureonly’ ]
trong ó:
Tùy ch n futureonly ch dùng khi k t bu c rule v i ki u d li u ngư i dùng
nh nghĩa, có nghĩa các c t thu c ki u d li u này trư c ó không b nh
hư ng b i rule.
Ví d : sp_bindrule ‘r_SoDuong’, ‘SinhVien.Nam’
Rule m i k t bu c s ng m g rule cũ trên i tư ng.
G k t bu c
sp_unbindrule ‘object’, [ ‘futureonly’ ]
Ví d : sp_unbindrule ‘SinhVien.Nam’
Xoá rule
Cú pháp: Drop rule {rule_name} [,…n]
Lưu ý: Ch xóa ư c rule khi nó không còn k t bu c v i i tư ng nào.
V. Các thao tác trên d li u
Chú ý khi thêm/ xóa/ c p nh t d li u:
- 12
Bài gi ng tóm t t H qu n tr cơ s d li u
– D li u nh p ph i phù h p v i ki u d li u.
m b o các ràng bu c toàn v n.
–
nh d ng giá tr ki u chu i unicode, ki u datetime.
–
– Nh p giá tr r ng (Null).
1. Các d ng l nh insert
− Thêm t ng dòng d li u vào b ng
Insert [into] Table_name[ (column_name[,…n] )]
values ( value [,…n] )
− Thêm 0-n dòng d li u t b ng khác/ t k t qu c a m t câu truy v n
Insert [into] Table_name
Select_statement
Lưu ý: trong câu select, ta có th c d li u t các b ng trong database khác.
Khi ó, tên b ng ư c vi t y như sau:
Database_name.Owner.Table_name
Ví d : select * from QLSinhVien.dbo.SinhVien
2. L nh c p nh t d li u
update table_name
set column_name_1= value1,…, column_name_m= value_m
[where conditional_expression]
3. L nh xoá d li u
delete [from] table_name
[where conditional_expression]
VI. Truy v n d li u
1. Câu truy v n t ng quát
Cú pháp t ng quát c a câu truy v n d li u:
SELECT [tính ch t]
FROM
[WHERE < i u ki n_1>]
[GROUP BY ]
[HAVING < i u ki n_2>]
[ORDER BY [ASC | DESC]]
trong ó:
− Tính ch t là m t trong các t khóa: ALL (ch n ra t t c các dòng trong b ng),
DISTINCT (lo i b các dòng trùng l p thông tin), TOP (ch n n dòng u
tiên th a mãn i u ki n).
- 13
Bài gi ng tóm t t H qu n tr cơ s d li u
− Danh sách các thu c tính_1: tên các thu c tính cho bi t thông tin c n l y.
Chú ý:
Các thu c tính cách nhau b i d u ‘,’.
N u l y t t c các thu c tính c a 1 b ng R thì dùng: R.*
N u sau FROM ch có 1 b ng và l y t t c các c t c a b ng ó thì dùng
select *.
N u t n t i 1 thu c tính sau select xu t hi n 2 b ng sau FROM thì ph i
ch nh rõ thu c tính ó thu c b ng nào.
− Danh sách các table/query/view: các b ng, câu truy v n, ho c khung nhìn ch a
thông tin c n l y. Khi tìm ki m thông tin trên nhi u hơn 2 b ng/truy v n thì
ph i k t các b ng l i v i nhau (có th t i u ki n k t t sau where ho c t
From… join/ left join/ right join/full join … on …).
trong m nh
− Alias: bí danh (tên t t) c a b ng dùng cho các b ng có tên quá dài, ho c m t
b ng ư c dùng nhi u l n trong m nh from c a câu truy v n.
i u ki n_1: là i u ki n l c d li u (ch n các b tho i u ki n).
−
− danh sách các thu c tính_2: d li u s ư c gom nhóm theo các c t này,
ưu tiên tính t trái sang.
i u ki n_2: i u ki n l c các nhóm theo m t tiêu chí i di n cho c nhóm.
−
− danh sách các thu c tính_3:s p x p d li u theo c t nào, th t là tăng (ASC)
ho c gi m (DESC). M c nh là d li u ư c s p theo th t tăng d n. Vi c
s p x p ư c th c hi n theo th t ưu tiên t trái qua ph i.
Lưu ý:
N u câu truy v n không có m nh Group By thì cũng không có m nh
Having.
N u câu truy v n có ch a m nh Group By thì Danh sách các thu c tính_1
ch ch a các thu c tính ho c bi u th c liên quan n các thu c tính trong
danh sách các thu c tính_2 và các hàm g p (max, min, avg, sum, count).
2. Các hàm thư ng dùng
− Các hàm g p (Aggregate functions): max, min, sum, avg, count
− Các hàm th i gian.
− Các hàm toán h c.
− Các hàm x lý chu i.
− ….
(Sinh viên có th tra c u theo t khóa trong Books Online).
- 14
Bài gi ng tóm t t H qu n tr cơ s d li u
VII. T o và s d ng khung nhìn (View)
1. Khái ni m khung nhìn:
Khung nhìn (View) là m t b ng o, có c u trúc như m t b ng, khung nhìn không
lưu tr d li u mà d li u c a nó ư c t o ra khi s d ng, khung nhìn là i tư ng
thu c CSDL. Khung nhìn ư c t o ra t câu l nh truy v n d li u (l nh Select), truy
v n t m t ho c nhi u b ng d li u.
2. S d ng khung nhìn
o Khung nhìn ư c s d ng khai thác d li u như m t b ng d li u, có th ư c chia
s b i nhi u ngư i dùng, an toàn trong khai thác.
o Có th th c hi n truy v n d li u trên c u trúc c a khung nhìn.
o Các khung nhìn ư c t o t nhi u b ng ho c trong khung nhìn có ch a t khóa
group by u không cho phép c p nh t d li u t
DISTINCT, hàm g p, m nh
khung nhìn vào các b ng g c trong cơ s d li u.
Cú pháp t o khung nhìn:
Create View view_name
As Select_statement
VIII. T o và s d ng ch m c (Index)
Ch m c (Index) là m t ph n quan tr ng i v i CSDL, c bi t là cơ s d li u l n.
Ch m c ư c thi t l p t m t ho c nhi u c t d li u c a b ng d li u. Các giá tr c a Ch
m c s ư c s p x p và lưu tr theo m t danh sách (b ng khác). M i giá tr ch m c là
duy nh t trong danh sách và nó s liên k t n giá tr trong b ng d li u (liên k t d ng con
tr ). Vi c lưu tr d li u c a b ng có khóa ch m c ư c th c hi n theo c u trúc B-Cây
nh m tăng t c truy xu t d li u i v i ĩa (thi t b th c p).
Khi tìm ki m m t giá tr trong c t d li u, mà c t này tham gia t o Ch m c, u tiên
câu l nh xác nh v trí c a giá tr n m trong Ch m c b ng phép duy t cây, sau ó th c
hi n tìm theo liên k t n b n ghi ch a giá tr tương ng v i khóa trong b ng.
1. L a ch n ch m c
• Không có ch m c, h qu n tr CSDL th c hi n truy v n b ng cách duy t qua t ng
dòng trong b ng.
• Cài t các ch m c cho b ng giúp truy v n thông tin nhanh hơn (tìm ki m trên
B-Cây).
• Khóa chính và các ràng bu c unique hi n nhiên là các ch m c c a b ng.
• Cơ s ch n cài t ch m c: d a vào các nhu c u truy v n th c hi n thư ng xuyên
trên CSDL.
- 15
Bài gi ng tóm t t H qu n tr cơ s d li u
• Nên cài t ch m c cho các trư ng h p sau:
– Trư ng h p 1: Có nhu c u truy v n thư ng xuyên các b c a b ng Q theo m t s
(t p) thu c tính nào ó.
Ví d : GiaoDich(MãGD, …,NgàyGD): Có nhu c u truy xu t thư ng xuyên các b
c a giao d ch trong m t ngày ho c trong m t kho ng th i gian nh t nh: cài t
ch m c trên thu c tính NgayGD c a quan h GiaoDich.
– Trư ng h p 2: t p thu c tính tham gia vào phép k t c a m t câu truy v n x y ra
thư ng xuyên.
Ví d : cho 2 lư c quan h :
HocSinh(STT, Lop, HoTen,…)
KetQua(STT, Lop, Mon, Diem)
Thư ng xuyên có nhu c u truy v n: cho bi t k t qu h c t p c a m t h c sinh.
Câu l nh truy v n như sau:
select hs.STT, hs.Lop, hs.HoTen, kq.Mon, kq.Diem
from HocSinh hs join KetQua kq on hs.STT = kq.STT
and hs.Lop = kq.Lop
Cài t ch m c (STT, Lop) cho quan h KetQua
T ng quát: trên mô hình quan h , xác nh các con ư ng truy xu t thư ng
xuyên:
T m t b c a Q1(m t giá tr c th a c a A) có nhu c u truy xu t thư ng
xuyên các b c a Q2 tương ng (tìm ki m các b c a Q2 v i A = a): khai báo ch
m c (A) cho Q2.
Lưu ý: m t ch m c (AB) khác v i hai ch m c (A) và (B).
2. Các lo i ch m c
Có hai lo i ch m c:
– Clustered index
– Nonclustered index
Clustered index:
• D li u th t s ư c s p x p v t lý theo ch m c (th t s n m nút lá c a cây).
• M i b ng ch có th có m t clustered ch m c, thư ng là khóa chính.
- 16
Bài gi ng tóm t t H qu n tr cơ s d li u
Nonclustered index:
• Ch m c logic, d li u th t s không ư c s p x p v t lý theo ch m c.
• Nút lá là con tr tr n v trí c a b d li u, ho c tr n giá tr c a clustered ch m c
(trong trư ng h p b ng có clustered index).
– Không có clustered index:
– Có clustered index
- 17
Bài gi ng tóm t t H qu n tr cơ s d li u
• M t s cân nh c khi ch n ch m c:
– S d ng nhi u ch m c tăng t c truy v n, nhưng làm gi m hi u qu c a các thao
tác thêm/xoá/c p nh t d li u.
– Không nên t o ch m c trên các b ng quá nh (vài trăm dòng).
– Ch nên ch n ch m c mà m i giá tr c a nó tương ng v i m t s ít b . N u m i
giá tr ch m c ng v i trên 20% s lư ng b trong b ng, th c hi n truy v n bình
thư ng b ng cách duy t qua các dòng trong b ng s hi u qu hơn.
– Các giá tr ch m c ph i phân b u các b trong b ng.
– C g ng dùng các ch m c v i s thu c tính ít (chi m ít không gian và c n ít chi phí
duy trì hơn ch m c v i s thu c tính l n).
– Clustered index ph i nh (s thu c tính ít, kích thư c nh ), vì các ch m c
nonclustered u ph i g n k t t i nó.
3. Cài t ch m c v i SQL Server
M t s qui nh:
1. M t b ng có t i a 249 nonclustered ch m c (bao g m c nh ng ch m c ng m
nh khi khai báo khóa chính và ch m c).
2. Kích thư c t i a c a m t ch m c (t ng kích thư c các thu c tính tham gia vào
ch m c) không quá 900 bytes.
3. M c nh: ch m c clustered ư c khai báo ng m nh cùng v i khai báo khóa
chính, các trư ng h p khác là nonclustered (t t nhiên có th ch nh khác i).
Cú pháp khai báo ch m c:
Create [ Unique ][ Cluster| Nonclustered] Ch m c ch m c_name
- 18
Bài gi ng tóm t t H qu n tr cơ s d li u
On {table | view } (column [ Asc | Desc] [ ,...n ])
Ví d :
Create nonclustered ch m c idx_STTHS_Lop
On KETQUA (STTHS, Lop)
Cú pháp xóa ch m c:
Drop Ch m c table_name (ch m c_name)
Ví d :
Drop Ch m c KETQUA(idx_STTHS_Lop)
IX. Chuy n i d li u v i các ng d ng khác
(xem các tài li u hư ng d n th c hành SQL Server kèm theo)
- 19
Bài gi ng tóm t t H qu n tr cơ s d li u
Chương 3
T-SQL NÂNG CAO
I. Khai báo và s d ng bi n
1. Bi n c c b
− Là m t i tư ng có th ch a giá tr thu c m t ki u d li u nh t nh, tên bi n b t
u b ng m t ký t @.
– Bi n c c b có giá tr trong m t query batch ho c trong m t th t c thư ng trú
(stored procedure) ho c hàm (function).
– Khai báo bi n c c b b ng l nh declare: cung c p tên bi n và ki u d li u:
Declare tên_bi n Ki u_d _li u
Ví d :
Declare @MaSinhVien char(10)
Declare @HoTen nvarchar(30)
Declare @Sum float, @Count int
gán giá tr cho m t bi n c c b dùng l nh set. Giá tr gán cho bi n ph i phù h p
–
v i ki u d li u c a bi n.
Set tên_bi n = giá_tr
Set tên_bi n = tên_bi n
Set tên_bi n = bi u_th c
Set tên_bi n = k t_qu _truy_v n
Ví d :
Set @MaLop = ‘TH2001’
Set @SoSV = (select count (*) from SinhVien)
Set @MaLop = ‘TH’+Year(@NgayTuyenSinh)
ưa k t qu truy v n vào bi n:
SV(MaSV: int; HoTen: nvarchar(30), Tuoi int)
Select @Var1 = HoTen, @Var1 = Tuoi from SV
where MaSV = 1
Lưu ý: n u câu truy v n tr v nhi u dòng, các bi n ch nh n giá tr tương ng c a dòng
u tiên.
2. Bi n toàn c c
– Là các bi n h th ng do SQL Server cung c p, tên bi n b t u b ng 2 ký t @
– SQL t c p nh t giá tr cho các bi n này, ngư i s d ng không th gán giá tr tr c
ti p.
− M t s bi n h th ng thu ng dùng
o @@error: thông báo mã l i, n u @@error = 0: thao tác th c hi n thành công.
- 20
Bài gi ng tóm t t H qu n tr cơ s d li u
o @@rowcount: cho bi t s dòng b nh hư ng b i l nh cu i (insert, update,
delete).
o @@trancount: cho bi t s giao d ch ang ho t ng trên k t n i hi n t i.
o @fetch_status: cho bi t thao tác l y d li u t cursor có thành công không.
II. C u trúc i u khi n
1. L nh If…else
− Ch c năng: xét i u ki n quy t nh nh ng l nh T-SQL nào s ư c th c hi n
− Cú pháp:
If bi u_th c_ i u ki n
L nh| Kh i_l nh
[Else L nh| Kh i_l nh]
Kh i l nh là m t ho c nhi u l nh n m trong c p t khóa begin…end
Ví d : xét 2 lư c quan h (L QH)
HocPhan(MaHP, TenHP, SiSo)
DangKy(MaSV, MaHP)
Vi t l nh thêm m t ăng ký m i cho sinh viên có mã s 001 vào h c ph n HP01
(gi s h c ph n này ã t n t i trong b ng HocPhan). L i gi i như sau:
Declare @SiSo int
select @SiSo = SiSo from HocPhan where MaHP= ‘HP01’
if @SiSo < 50
Begin
insert into DANG_KY(MaSV, MaHP)
values(‘001’, ’HP01’)
print N’ ăng ký thành công’
End
Else
print N’H c ph n ã SV’
2. L nh While
− Ch c năng: th c hi n l p l i m t o n l nh T-SQL khi i u ki n còn úng.
− Cú pháp:
- 21
Bài gi ng tóm t t H qu n tr cơ s d li u
While bi u_th c_ i u_ki n
L nh| Kh i l nh
– Có th s d ng Break và Continue trong kh i l nh c a while
Break: thoát kh i vòng while hi n hành.
Continue : tr l i u vòng while, b qua các l nh sau ó.
Ví d : xét lư c quan h SinhVien(MaSV: int, HoTen: nvarchar(30))
Vi t l nh xác nh m t mã sinh viên m i theo qui nh: mã sinh viên tăng d n, n u
có ch tr ng thì mã m i xác nh s chèn vào ch tr ng ó. Ch ng h n, n u trong
b ng sinhvien ã có các mã sinh viên 1, 2, 3, 7 mã sinh viên m i là 4.
Gi i:
Declare @STT int
Set @STT = 1
While exists(select * from SV where MaSV = @STT)
set @STT = @STT+1
Insert into SV(MaSV, HoTen) values(@STT, ‘Nguyen Van A’)
3. L nh Case
− Ch c năng: ki m tra m t dãy các i u ki n và tr v k t qu phù h p v i i u ki n
úng. L nh case ư c s d ng như m t hàm trong câu select.
− Cú pháp: Có hai d ng:
D ng 1 (simple case):
Case Bi u_th c_ u_vào
When Giá_tr then k t_qu
[...n]
[ Else k t_qu _khác]
End
D ng 2 (searched case):
Case
When bi u_th c_ i u ki n then k t_qu
[...n]
[ Else k t_qu _khác]
End
Ví d : xét L QH NHAN_VIEN(MaNV, HoTen, NgaySinh, CapBac,Phai)
- 22
Bài gi ng tóm t t H qu n tr cơ s d li u
Cho bi t nh ng nhân viên n tu i ngh hưu bi t r ng tu i v hưu c a nam là 60, c a
n là 55).
Gi i:
select * from NHAN_VIEN
where datediff(yy, NgaySinh, getdate()) > =
Case Phai
when ‘Nam’ then 60
when ‘Nu’ then 55
End
Cho bi t mã NV, h tên và lo i nhân viên (c p b c
Thêm vào BST
Báo xấu
Download
Vui lòng tải xuống để xem tài liệu đầy đủ
