Tin Học Quản Trị - Hệ Cơ Sở Dữ Liệu phần 2

Chia sẻ: Qwdqwgferhrt Verbnrtjheth | Ngày: | Loại File: PDF | Số trang:12

Thêm vào BST

Báo xấu

87
lượt xem 9
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Cơ sở dữ liệu thường đủ lớn để lưu trên một thiết bị lưu trữ như đĩa hay băng. Dữ liệu này được duy trì dưới dạng một tập hợp các tập tin trong hệ điều hành hay được lưu trữ trong các hệ quản trị cơ sở dữ liệu.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Tin Học Quản Trị - Hệ Cơ Sở 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