Thuc Hanh SQL Server

  • Published on
    08-Jul-2015

  • View
    2.737

  • Download
    0

Embed Size (px)

Transcript

<p>I HC THI NGUYN TRNG I HC K THUT CNG NGHIP</p> <p>TI LIU THC HNH H QUN TR C S D LIU</p> <p>Thi Nguyn, nm 2011</p> <p>THS. NGUYN TUN ANH</p> <p>TI LIU THC HNHHC PHN: H QUN TR C S D LIU M S HC PHN: S TN CH: 3</p> <p>Trng B mn (K v ghi r h tn)</p> <p>Trng khoa (K v ghi r h tn)</p> <p>Ts. Nguyn Duy Cng</p> <p>2</p> <p>MC LCBI 1. NGN NG NH NGHA D LIU.......................................................................4 1.1. To bng d liu............................................................................................................4 1.1.1. Cc thuc tnh lin quan n bng..........................................................................4 1.1.2. To bng bng truy vn SQL..................................................................................5 1.1.3. Sa i bng.........................................................................................................10 1.2. Ch mc (Index)...........................................................................................................10 1.3. Khung nhn (view).......................................................................................................11 BI 2. NGN NG THAO TC D LIU........................................................................13 2.1. Truy xut d liu.........................................................................................................13 2.1.1. Xc nh bng bng mnh FROM...................................................................13 2.1.2. Mnh WHERE.................................................................................................14 2.1.3. Danh sch chn trong danh sch SELECT ..........................................................16 2.1.4. Tnh ton cc gi tr trong cu lnh SELECT ......................................................18 2.1.5. T kha DISTINCT..............................................................................................18 2.1.6. To bng mi bng cu lnh SELECT INTO..................................................18 2.1.7. Sp xp kt qu truy vn bng ORDER BY.........................................................18 2.1.8. Php hp v ton t UNION................................................................................19 2.1.9. Php ni................................................................................................................21 2.1.10. To cc dng thng k d liu vi COMPUTE BY......................................23 2.1.11. Thng k d liu vi GROUP BY v HAVING................................................25 2.1.12. Truy cn con.......................................................................................................26 2.2. B sung, cp nht v xa d liu................................................................................27 2.2.1. B sung d liu.....................................................................................................27 2.2.2. Cc nht d liu....................................................................................................28 2.2.3. Xa d liu............................................................................................................29 BI 3. BO MT TRONG SQL...........................................................................................30 3.1. Cu lnh GRAN...........................................................................................................30 3.2. Cu lnh REVOKE.....................................................................................................31 BI 4. TH TC LU TR V TRIGGER......................................................................33 4.1. S dng th tc lu tr (store procedure)................................................................33 4.1.1. To cc th tc lu tr..........................................................................................33 4.1.2. Thng tin tr v t cc th tc lu tr..................................................................35 4.1.3. Cc quy tc s dng cho Store Procedure ...........................................................36 4.1.4. Xc nh tn bn trong cc th tc.......................................................................38 4.1.5. i tn cc th tc:...............................................................................................39 4.1.6. Xo th tc:...........................................................................................................39 4.2. S dng cc Trigger....................................................................................................39 4.2.1. To cc trigger......................................................................................................40 4.2.2. Cc gi tr null ngm nh v hin........................................................................41 4.2.3. Vic i tn v cc trigger....................................................................................42 4.2.4. Hin th thng tin v cc trigger...........................................................................42 4.2.5. Xo trigger............................................................................................................42 PH LC................................................................................................................................44 4.3. Cu trc v d liu bng NhanVien v bng DonVi................................................44 4.4. Mt s hm thng s dng trong SQL Server.......................................................45 4.4.1. Cc hm trn d liu kiu ngy gi......................................................................45 4.4.2. Cc hm v chui..................................................................................................46</p> <p>3</p> <p>BI 1.</p> <p>NGN NG NH NGHA D LIUS TIT: 01 TIT</p> <p>Mc tiu: Ngn ng nh ngha d liu bao gm cc cu lnh cho php ngi s dng nh ngha CSDL v cc i tng trong CSDL nh cc bng, cc khung nhn, ch mc, 1.1. To bng d liu D liu bn trong mt CSDL c t chc lu tr trong cc bng. Bn trong cc bng, d liu c t chc di dng cc dng v cc ct. Mi mt dng biu din mt bng ghi duy nht v mi mt ct biu din mt trng. 1.1.1. Cc thuc tnh lin quan n bng Khi to v lm vic vi cc bng d liu, ta cn phi n cc thuc tnh khc trn bng nh: kiu d liu, cc rng buc, cc kha, cc quy tc,.. Cc thuc tnh ny c s dng nhm to ra cc rng buc ton vn trn cc ct (trng), trn bng cng nh to ra cc ton vn tham chiu gia cc bng d liu trong CSDL a) Kiu d liu Mi mt ct (trng) ca mt bng u phi thuc vo mt kiu d liu nht nh c nh ngha t trc. Mi kiu d liu quy nh cc gi tr d liu c php i vi ct . Cc h qun tr CSDL thng cung cp cc kiu d liu chun, ngoi ra cn c th cho php ngi s dng nh ngha cc kiu d liu khc da trn cc kiu d liu c. Di y l mt s kiu d liu thng s dng trong SQL: Binary Bit Char Datetime Decimal Float Image b) Cc rng buc (Constraints) Trn cc bng d liu, cc rng buc c s dng nhm cc mc ch sau: Quy nh cc gi tr d liu hay khun dng d liu c cho php chp nhn trn cc ct ca bng (rng buc Check) Int Money Nchar Ntext Nvarchar Real Smalldatetime Smallint Smallmoney Text Tinyint Varbinary Varchar</p> <p>4</p> <p>-</p> <p>Quy nh gi tr mc nh cho cc ct (rng buc Default) To nn tnh ton vn thc th trong mt bng d liu v ton vn tham chiu cc bng d liu trong CSDL (rng buc Primary key, Unique, foreign key).</p> <p>Chng ta s tm hiu chi tit hn v cc rng buc ny phn trnh by v cu lnh CREATE TABLE. 1.1.2. To bng bng truy vn SQL To cc bng l mt khau quan tng trong qu tnh thit k v ci t cc CSDL. Bn trong cc CSDL, mi bng thng c s dng nhm biu din thng tin v cc i trng trong th gii thc v/hoc biu din mi quan h gia cc i tng. c th t chc tt mt bng d liu, bn t nht cn phi xc nh c cc yu cu sau: Bng c s dng nhm mc nh g v c vai tr nh th no bn trong CSDL? Bng s bao gm nhng mt no v kiu d liu cho cc ct l g? Nhng ct no cho php chp nhn gi tr NULL. C s dng cc rng buc, cc mc nh hay khng v nu c th s dng u v nh th no? Nhng ct no s ng vai tr l kha chnh, kha ngoi, kha duy nht? Nhng dng ch mc no l cn thit v cn u a) To bng d liu: to mt bng d liu tng CSDL, bn s dng cu lnh CREATE TABLE c c php nh sau: CREATE TABLE table_name ( {colname_1 col_1_properties [constraints_1 ] [,{colname_2 col_2_properties [constraints_2 ]] ... [,{colname_N col_N_properties [constraints_N ]] [table_constraints] ) Trong : table name Tn bng cn to, tn bng phi duy nht trong mi CSDL v phi tun theo cc quy tc v nh danh.</p> <p>5</p> <p>colname_i</p> <p>Tn ca ct th i trong bng. Cc ct trong mi bng phi c tn khc nhau v phi tun theo cc quy tc v nh danh. Mi mt bng phi c t nht mt ct. Cc thuc tnh ca ct th i quy nh kiu d liu ca ct v ch nh ct c cho php chp nhn gi tr NULL hay khng Cc rng buc (nu c) trn ct th i nh cc rng buc v kha, cc mc nh, cc quy nh v khun dng d liu. Cc rng buc trn bng d liu</p> <p>col_i_properties</p> <p>constraints_itable_constraint</p> <p>V d 1.1: Cu lnh di u thc hin vic tao bng NhanVien bao gm cc ct: MaNV, HoTen, NgaySinh, DiaChi, DienThoai: CREATE TABLE nhanvien ( manv char(10) NOT NULL, hoten char(30) NOT NULL, ngaysinh datetime NULL, diachi char(50) NULL, dienthoai char(6) NULL ) b) S dng cc rng buc trong bng d liu Rng buc Check: Rng buc Check c s dng ch nh cc gi tr hay khun dng d liu c th c chp nhn i vi mt ct. Trn mt ct c th c nhiu rng buc Check. khai bo mt rng buc Check i vi mt ct no , ta s dng c php nh sau: [CONSTRAINT constraint_name] CHECK (expression) Trong expression, l mt biu thc logic quy nh gi tr hay khun dng ca d liu c cho php. Khi , ch nhng gi tr d liu no lm cho expression nhn gi tr ng mi c chp nhn. V d 1.2: quy nh in thoi ca nhn vin phi c dng ###### (chng hn 826767), cu lnh v d c vit nh sau: CREATE TABLE nhanvien ( manv char(10) NOT NULL, hoten char(30) NOT NULL,</p> <p>6</p> <p>ngaysinh datetime NULL, diachi char(50) NULL, dienthoai char(6) NULL constraint check_dienthoai check (dienthoai LIKE '[0-9][0-9][0-9][0-9][0-9] [09]') ) Rng buc Default</p> <p>Rng buc Default c s dng quy ddihj gi tr mc nh cho mt ct. Gi tr ny s t ng c gn cho ct ny khi ngi s dng b dung mt bng ghi m khng ch nh gi tr cho ct. Trn mi c ch c th c nhiu nht mt rng buc Default (tc l ch c th c ti a mt gi tr mc nh). khai bo mt gi tr mc nh cho mt ct, ta ch nh mt rng buc Default cho ct bng cch s dng c php sau: [CONSTRAINT constraint_name] DEFAULT {const_expression | nonarguments_function | NULL} V d 1.3: Cu lnh di y ch nh gi tr mc nh l khng bit cho ct DiaChi trong bng NhanVien v d 1.1. CREATE TABLE nhanvien ( manv char(10) NOT NULL, hoten char(30) NOT NULL, ngaysinh datetime NULL, diachi char(50) default 'Khng bit', dienthoai char(6) NULL ) Rng buc Primary Key</p> <p>Rng buc Primary key c s dng nh ngha kha chnh ca bng. Mt rng buc Primary key m bo rng khng c cc gi t trng lp c a vo trn cc ct. Hay ni cch khc, gi tr ca kho chnh s gip cho ta xc nh c duy nht mt dng (bng ghi) trong bng d liu. Mi mt bng ch c th c duy nht mt kh chnh v bn thn kha chnh khng chp nhn gi tr NULL. Rng buc Primary key l c s cho vic m bo tnh ton vn thc th cng nh ton vn tham chiu. khai bo mt rng buc Primary key, bn s dng c php sau: [CONSTRAINT constraint_name ]</p> <p>7</p> <p>PRIMARY KEY [CLUSTERED|NONCLUSTERED] [( colname [,colname2 [...,colname16]])] Nu kha chnh ca mt bng ch l mt ct, khi bn khng cn thit phi nh danh sch cc ct (s dng rng buc mc ct). Trong trng hp kh chnh l mt tp hp t hai ct tr ln, bn phi ch nh sanh sch cc ct (s dng rng buc mc bng). V d 1.4: to bng NhanVien vi kha chnh l MaNV. CREATE TABLE nhanvien ( manv char(10) primary key, hoten char(30) NOT NULL, ngaysinh datetime NULL, diachi char(50) NULL, dienthoai char(6) NULL ) Cu lnh trn c th vit nh sau: CREATE TABLE nhanvien ( manv char(10) NOT NULL, hoten char(30) NOT NULL, ngaysinh datetime NULL, diachi char(50) NULL, dienthoai char(6) NULL constraint pk_nv primary key(manv) ) Rng buc Unique</p> <p>Thay v s dng kha chnh, bn c th s dng rng buc Unique m bo tnh ton vn thc th. S dng rng buc Unique trn mt (hay nhiu) ct bt buc cc gi tr d liu trn mt (hay nhiu) ct ny khng c trng lp nhau. khai bo mt rng buc Unique, bn s dng c php lnh sau y: [CONSTRAINT constraint_name] UNIQUE [CLUSTERED | NONCLUSTERED] [colname1 [,colname2 [...,colname16]])] Rng buc Foreugn Key</p> <p>Cc bng bn trong mt CSDL thng c mi quan h vi nhau. Cc mi quan h ny c xc nh da trn tnh bng nhau gia mt hay nhiu trng ca bng ny</p> <p>8</p> <p>vi mt hay nhiu trng ca bng khc. Nu mt (hay nhiu) ct no ca mt bng c gi t c xc nh t mt (hay nhiu) trng kha ca bng khc th cc ct c gi l c rng buc kha ngoi. Cc rng buc Foreign Key c s dng kt hp vi cc rng buc Primary Key v Unikey nhm m bo tnh ton vn tham chiu gia cc bng c ch nh. khai bo kha ngoi, bn s dng c php lnh nh sau: [CONSTRAINT constraint_name ] [FOREIGN KEY (colname [,colname2 [...,colname16]])] REFERENCES reference_TABLE [(ref_colname [,ref_colname2 [...,ref_colname 16]])] V d 1.5: To hai bng NhanVien(MaNV, Hoten, NgaySinh, DiaChi, DienThoai, MaDV) v DonVi(MaDV, TenDV) theo s di y:</p> <p>Hnh 1.1 CREATE TABLE donvi ( madv char(2) primary key, tendv char(20) NOT NULL ) CREATE TABLE nhanvien ( manv char(10) primary key, hoten char(20) NOT NULL, ngaysinh datetime NULL, diachi char(50) default 'khong biet', dienthoai char(6) check(dienthoai LIKE '[0-9][0-9][0-9][0-9][0-9][09]'), madv char(2) foreign key(madv) references donvi(madv)</p> <p>9</p> <p>) 1.1.3. Sa i bng Sau khi to bng, bn c th tin hnh sa i cu trc hay thuc tnh ca bng nh b sng ct, b sung kha, thay i cc rng buc, c th sa i bng, bn s dng cu lnh Alter c c php nh sau: ALTER TABLE TABLE_name [ADD {col_name column_properties [column_constraints] |[[,]TABLE_constraint ] } [,{next_col_name|next_TABLE_constraint}]...] |[DROP [CONSTRAINT] constraint_name1 [, constraint_name2]...] V d 1.6: To mt rng buc cho bng DonVi trn ct MaDV quy nh m n v phi c dng hai ch s (v d 01, 02,) ALTER TABLE donvi ADD CONSTRAINT check_madv CHECK (madv LIKE [0-9][0-9]) 1.2. Ch mc (Index) Cc ch mc c s dng nhm h t vic truy cp n cc dng d liu c nhanh chng trn cc gi t ca mt hay nhiu ct. Ch mc c chia ra lm hai loi: ch mc t nhm (clustered index) v ch mc khng t nhm (nonclustered index). Mt ch mc t nhm l mt ch mc m trong th t logic ca cc kha tng t nh th t vt l ca cc dng tng ng tn ti trong bng. Mt bng ch c th c ti a mt ch t nhm. Mt ch mc khng t nhm l mt ch mc m trong th t logic ca cc kha khng nh th t vt l ca cc dng trong bng. Cc ch mc t nhm h tr vic truy cp n cc dng d liu nhanh hn nhiu so vi cc ch mc khng t nhm.</p> <p>-</p> <p>Khi ta khai bo mt kha chnh hay kha Unique trn mt hay nhiu ct no ca bng, h qun t CSDL s t ng to ch mc trn cc ct . Bn c th to thm cc ch mc khc bng cch s dng cu lnh c c php nh sau: CREATE [CLUSTERED | NONCLUSTERED] INDEX index_name ON TABLE_name(column_name [, column_name]...)</p> <p>10</p> <p>V d 1.7: Cu lnh di y s to mt ch mc khng t nhm trn ct MaDV ca bng NhanVienCREATE NONCLUSTERED INDEX idx_nhanvien_madv ON nhanvien(madv)</p> <p>1.3. Khung nhn (view) Mt khung nhn c th coi nh l mt bng o c ni dung c xc nh t mt truy vn. Mt truy vn l mt tp cc ch dn nhm truy xut v hin th d liu t cc bng trong CSDL. Cc truy vn c thc hin bng cch s dng cu lnh SELECT. Mt khung nhn ging nh mt bng vi mt tp cc tn ct v cc dng d liu. Tuy nhin, khung nhn khng tn ti nh l mt cu trc lu tr d liu trong CSDL. D liu bn trong khung nhn thc cht l d liu c xc nh t mt hay nhiu bng c s v do ph thuc vo cc bng c s. Cc khung nhn thng c s dng bn tng CSDL nhm cc mc ch sau: S dng khung...</p>