Thuc Hanh SQL Server

  • View
    2.737

  • Download
    0

Embed Size (px)

Transcript

I HC THI NGUYN TRNG I HC K THUT CNG NGHIP

TI LIU THC HNH H QUN TR C S D LIU

Thi Nguyn, nm 2011

THS. NGUYN TUN ANH

TI LIU THC HNHHC PHN: H QUN TR C S D LIU M S HC PHN: S TN CH: 3

Trng B mn (K v ghi r h tn)

Trng khoa (K v ghi r h tn)

Ts. Nguyn Duy Cng

2

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

3

BI 1.

NGN NG NH NGHA D LIUS TIT: 01 TIT

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

4

-

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).

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.

5

colname_i

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

col_i_properties

constraints_itable_constraint

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,

6

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

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). kh