Ebook giao trinh thuc hanh sql

  • Published on
    24-May-2015

  • View
    301

  • Download
    2

Embed Size (px)

Transcript

  • 1. Giao trnh thc hanh SQL

2. Trn Nguyn PhongCChhnngg 11:: NNGGNN NNGG NNHH NNGGHHAA DD LLIIUUBinaryBitCharDatetimeDecimalFloatImageIntMoneyNcharNtextNvarcharRealSmalldatetimeSmallintSmallmoneyTextTinyintVarbinaryVarchar 3. Giao trnh thc hanh SQLCREATE 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])- table_name:- colname_i: 4. Trn Nguyn Phong- col_i_properties:- constraints_i:- table_constraint: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 constraint_name]CHECK (expression)CREATE TABLE nhanvien( 5. Giao trnh thc hanh SQLmanv char(10) not null,hoten char(30) not null,ngaysinh datetime null,diachi char(50) null,dienthoai char(6) nullconstraint check_dienthoaicheck (dienthoai like [0-9][0-9][0-9][0-9][0-9] [0-9]))[CONSTRAINT constraint_name]DEFAULT {const_expression nonarguments_function NULL}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) 6. Trn Nguyn Phong[CONSTRAINT constraint_name ]PRIMARY KEY [CLUSTERED|NONCLUSTERED][( colname [,colname2 [...,colname16]])]CREATE TABLE nhanvien(manv char(10) primary key,hoten char(30) not null,ngaysinh datetime null,diachi char(50) null,dienthoai char(6) null)CREATE TABLE nhanvien(manv char(10) not null,hoten char(30) not null,ngaysinh datetime null,diachi char(50) null,dienthoai char(6) nullconstraint pk_nv primary key(manv)) 7. Giao trnh thc hanh SQL[CONSTRAINT constraint_name]UNIQUE [CLUSTERED | NONCLUSTERED][colname1 [,colname2 [...,colname16]])][CONSTRAINT constraint_name ][FOREIGN KEY (colname [,colname2 [...,colname16]])]REFERENCES reference_table [(ref_colname[,ref_colname2[...,ref_colname 16]])]CREATE TABLE donvi( 8. Trn Nguyn Phongmadv 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][0-9]),madv char(2) foreign key(madv)references donvi(madv))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]...]ALTER TABLE donviADD CONSTRAINT check_madvCHECK (madv LIKE [0-9][0-9]) 9. Giao trnh thc hanh SQLCREATE [CLUSTEREDNONCLUSTERED] INDEX index_nameON table_name(column_name [, column_name]...)CREATE NONCLUSTERED INDEX idx_nhanvien_madvON nhanvien(madv) 10. Trn Nguyn PhongCREATE VIEW view_name[(column_name [, column_name]...)]AS select_statementCREATE VIEW thongtin_nvASSELECT manv,hoten,datediff(year,ngaysinh,getdate()),tendvFROM nhanvien,donviWHERE nhanvien.madv=donvi.madvCREATE VIEW thongtin_nv(manv,hoten,tuoi,donvi) 11. Giao trnh thc hanh SQLASSELECT manv,hoten,datediff(year,ngaysinh,getdate()),tendvFROM nhanvien,donviWHERE nhanvien.madv=donvi.madv 12. Trn Nguyn PhongCChhnngg 22:: NNGGNN NNGG TTHHAAOO TTAACC DD LLIIUU truy xut d liu t cc dng v cc ct ca mt hay nhiu bng, khung nhn,ta s dng cu lnh SELECT. Cu lnh ny c th dng thc hin php chn (tc ltruy xut mt tp con cc dng trong mt hay nhiu bng), php chiu (tc l truyxut mt tp con cc ct trong mt hay nhiu bng) v php ni (tc l lin kt ccdng trong hai hay nhiu bng truy xut d liu).C php chung ca cu lnh SELECT c dng nh sau:SELECT [ ALL | DISTINCT ] select_list[ INTO [ newtable_name ]]FROM { table_name | view_name }................[,{table_name | view_name }][WHERE clause ][GROUP BY clause ][HAVING BY clause ][ORDER BY clause ][COMPUTE clause ]Ch : Cc thnh phn trong mt cu lnh SELECT phi c s dng theo th tc nu trn.1.1 Xc nh bng bng mnh FROMMnh FROM trong cu lnh SELECT c s dng nhm ch nh cc bngv khung nhn cn truy xut d liu. Sau mnh FROM l danh sch tn cc bngv khung nhn tham gia vo truy vn (tn ca cc bng v khung nhn c phn cchnhau bi du phy).SELECT select_listFROM {table_nameview_name list} 13. Giao trnh thc hanh SQL n gin ho cu hi, ta c th s dng cc b danh (alias) cho cc bng haykhung nhn. B danh c gn trong mnh FROM bng cch ch nh b danh sautn bng. V d cu lnh sau gn b danh n1 cho bng nhanvien.SELECT ten, diachi FROM nhanvien n11.2 Mnh WHEREMnh WHERE trong cu lnh SELECT xc nh cc iu kin i vi vic truyxut d liu. Sau mnh WHERE l mt biu thc logic v ch nhng dng d liuno tho mn biu thc sau WHERE mi c hin th trong kt qu truy vn. Trongmnh WHERE thng s dng: Cc ton t so snh Gii hn ( BETWEEN v NOT BETWEEN). Danh sch (IN, NOT IN) Khun dng (LIKE v NOT LIKE). Cc gi tr cha bit (IS NULL v IS NOT NULL). Kt hp cc iu kin (AND, OR).Cc ton t so snh:Ton t ngha= Bng> Ln hn< Nh hn>= Ln hn hoc bng Khng ln hn!< Khng nh hnV d 2.1: Truy vn sau y cho bit tn, a ch v in thoi ca nhng nhn vinc h s lng ln hn 1.92:SELECT ten, diachi, dienthoaiFROM nhanvienWHERE hsluong>1.92Gii hn (BETWEEN v NOT BETWEEN)T kho BETWEEN v NOT BETWEEN c s dng nhm ch nh khonggi tr tm kim i vi cu lnh SELECT. Cu lnh di y cho bit tn v ia chca nhng nhn vin c h s lng nm trong khong 1.92 n 3.11SELECT ten, tuoi, diachiFROM nhanvienWHERE hsluong BETWEEN 1.92 AND 3.11 14. Trn Nguyn PhongDanh sch (IN v NOT IN)T kho IN c s dng khi ta cn ch nh iu kin tm kim d liu cho culnh SELECT l mt danh sch cc gi tr. Sau IN (hoc NOT IN) c th l mt danhsch cc gi tr hoc l mt cu lnh SELECT khc.V d 2.2: hin th thng tin v cc nhn vin c h s lng l 1.86, 1.92hoc 2.11, thay v s dng cu lnh:SELECT * FROM nhanvienWHERE hsluong=1.86 OR hsluong=1.92 OR hsluong=2.11Ta c th s dng cu lnh sau:SELECT * FROM nhanvienWHERE hsluong IN (1.86, 1.92, 2.11)Cc k t i din v mnh LIKET kho LIKE (NOT LIKE) s dng trong cu lnh SELECT nhm m t khundng ca d liu cn tm kim. Chng thng c kt hp vi cc k t i din sauy:K t i din ngha% Chui k t bt k gm khng hoc nhiu k t- K t n bt k[] K t n bt k trong gii hn c ch nh (v d [a-f])hay mt tp (v d [abcdef])[^] K t n bt k khng nm trong gii hn c ch nh (v d [^a-f] hay mt tp (v d [^abcdef]).V d 2.3: Cu lnh di y hin th thng tin v cc nhn vin c tn l NamSELECT * FROM nhanvienWHERE hoten LIKE % NamIS NULL v NOT IS NULLGi tr NULL c th c nhp vo mt ct cho php chp nhn gi tr NULLtheo mt trong ba cch sau: Nu khng c d liu c a vo v khng c mc nh cho ct hay kiud liu trn ct . Ngi s dng trc tip a gi tr NULL vo cho ct . Mt ct c kiu d liu l kiu s s cha gi tr NULL nu gi tr c chnh gy trn s.Trong mnh WHERE, ta s dng IS NULL hoc IS NOT NULL nh sau:WHERE col_name IS [NOT] NULLCc ton t logicCc ton t logic s dng trong mnh WHERE bao gm AND, OR, NOT.AND v OR c s dng kt hp nhiu iu kin trong WHERE.1.3 Danh sch chn trong cu lnh SELECT 15. Giao trnh thc hanh SQLn tt c cc ct trong bngKhi mun truy xut tt c cc ct trong bng, ta s dng cu lnh SELECT c cphp sau:SELECT * FROM table_nameKhi s dng cu lnh ny, cc ct trong kt qu s c hin th theo th t mchng c to ra trong cu lnh CREATE TABLE.* Chn cc ct c ch nh chn ra mt s ct no trong bng, ta s dng cu lnh SELECT c cphp sau:SELECT tn_ct [,...,tn_ct_n]FROM tn_bng | khung_nhnCc tn ct trong cu lnh phi c phn cch nhau bng du phy.Ch : Trong cu lnh SELECT, th t ca cc ct c nu ra trong cu lnh sxc nh th t ca cc ct c hin th ra trong kt qu.* i tn cc ct trong cc kt quKhi kt qu c hin th, tiu ca cc ct mc nh s l tn ca cc ct khin c to ra trong cu lnh CREATE TABLE. Tuy nhin, cc tiu tr nnthn thin hn, ta c th i tn cc tiu ca cc ct. lm c vic ny, ta cth s dng mt trong hai cch vit sau:tiu__ct = tn_cthoc tn_ct tiu__ctV d 2.4: Hai cu lnh sau s t tiu H v tn cho l hoten v a chcho ct diachi khi kt qu c hin th cho ngi s dng:SELECT H v tn=hoten,a ch = diachiFROM nhanvienHoc:SELECT hoten H v tn,diachi ia ch FROM nhanvien* S dng cu trc CASE thay i d liu trong kt quTrong cu lnh SELECT, ta c th s dng cu trc CASE thay i cch hinth kt qu ra mn hnh.V d 2.5: Cu lnh sau cho bit h tn, h s lng v xp loi lng ca nhnvin theo h s lng:SELECT H v tn = ten, H s lng = hsluong,Xp loi lng =CASEWHEN lsluong=NULL THEN Khng xc nh 16. Trn Nguyn PhongWHEN hsluong= Ln hn hoc bng< Nh hn Khng ln hn!< Khng nh hn.1.9.2 Cc loi php ni* Php ni bng v php ni t nhinMt php ni bng (equijoin) l mt php ni trong gi tr ca cc ct cs dng ni c so snh vi nhau da trn tiu chun bng v tt c cc ct trongcc bng tham gia ni u c a ra trong kt qu.V d 2.14:SELECT * FROM nhanvien, donviWHERE nhanvien.madonvi = donvi.madonviTrong kt qu ca cu lnh trn, ct madonvi v tendonvi xut hin hai ln trongkt qu php ni v nh vy l khng cn thit. loi b iu ny, ta c th s dngphp ni t nhin (natural join) bng cch loi b i cc ct trng tn vi nhau.* Php ni vi cc iu kin b sungTrong mnh WHERE ca cu lnh ni, ta c th b sung cc iu kin tmkim khc.V d 2.15:SELECT hoten, diachi, tendonviFROM nhanvien, donviWHERE nhanvien.madonvi = donvi.madonvi ANDNhanvien.hsluong>=2.11* Php t ni v cc b danhPhp t ni l php ni m trong ta so snh cc gi tr bn trong mt ct cacng mt bng.V d 2.16: Tm nhng nhn vin c cng a ch vi nhn vin Trn NguynPhongSELECT n1.hotenFROM nhanvien n1, nhanvien n2WHERE n2.hoten=Trn Nguyn Phong ANDn1.diachi = n2.diachi* Php ni khng da trn tiu chun bngTrong php ni ny, cc ct c s dng kt ni c so snh vi nhaukhng da trn iu kin bng.* Php ni ngoi (outer join)Trong cc php ni cp trn, ch nhng dng hp l (tc l nhng dngc gi tr trong cc ct c ch nh tho m iu kin kt ni) mi c a ra trongkt qu. Theo mt ngha no , nhng php ni ny loi b thng tin cha trongnhng dng khng hp l. Tuy nhin, i khi ta cng cn gi li nhng thng tinkhng hp l bng cch cho php nhng dng khng hp l c mt trong kt qu ca 22. Trn Nguyn Phongphp ni. lm iu ny, ta c th s dng php ni ngoi. Giao tc SQL cung cphai php ni ngoi: Php ni ngoi tri (*=) : Php ni ny cho php ly tt c cc t bng ctn u tin. Php ni ngoi phi (=*) : Php ni ny cho php ly tt c cc dng tbng c tn th hai.V d 2.17: Gi s ta c hai bng R v S c ni dung nh sauA B C D----- ----------- ----------- -----aaa 2 4 aaaafff 8 4 f2ggg 2 7 g4xxx 2 12 gdfggg 2 12 khfsss 45 0 k3hBng RE F G----- ----------- ---------