Toán - Tin Học Hệ Quản Trị phần 2

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

Thêm vào BST

Báo xấu

81
lượt xem 6
download

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

Tham khảo tài liệu 'toán - tin học hệ quản trị 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ả

Chủ đề:

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

Lưu

Nội dung Text: Toán - Tin Học Hệ Quản Trị 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