Giao trinh thuc hanh sql

  • Published on
    21-May-2015

  • View
    47

  • Download
    0

Embed Size (px)

Transcript

  • 1. Giao trnh thc hanh SQL

2. Trn Nguyn PhongChng 1: NGN NG NH NGHA D LIUBinaryMoneySmallmoneyCharNcharTextDatetimeNtextTinyintDecimalNvarcharVarbinaryFloatRealVarcharImageSmallintBitIntSmalldatetime 3. Giao trnh thc hanh SQL CREATE TABLEtable_name( {colname_1col_1_properties[,{colname_2 col_2_properties[constraints_1 ] [constraints_2 ]]... [,{colname_N col_N_properties [table_constraints] )- table_name:- colname_i:[constraints_N ]] 4. Trn Nguyn Phong- col_i_properties:- constraints_i:- table_constraint:CREATE TABLE nhanvien ( manvchar(10) not null,hotenchar(30) not null,ngaysinhdatetime null,diachichar(50) null,dienthoaichar(6)null)[CONSTRAINT constraint_name] CHECK (expression)CREATE TABLE nhanvien ( 5. Giao trnh thc hanh SQL manvchar(10) not null,hotenchar(30) not null,ngaysinhdatetime null,diachichar(50) null,dienthoaichar(6)nullconstraint check_dienthoai check (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 hotenchar(30) not null,ngaysinhdatetime null,diachichar(50) default 'khng bit',dienthoai )char(10) not null,char(6)null 6. Trn Nguyn Phong[CONSTRAINT constraint_name ] PRIMARY KEY [CLUSTERED|NONCLUSTERED] [( colname [,colname2 [...,colname16]])]CREATE TABLE nhanvien ( manvchar(10) primary key,hotenchar(30) not null,ngaysinhdatetime null,diachichar(50) null,dienthoaichar(6)null)CREATE TABLE nhanvien ( manvchar(10) not null,hotenchar(30) not null,ngaysinhdatetime null,diachichar(50) null,dienthoaichar(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]])] REFERENCESreference_table [(ref_colname [,ref_colname2 [...,ref_colname 16]])]CREATE TABLE donvi ( 8. Trn Nguyn Phong madvchar(2)primary key,tendvchar(20) not null)CREATE TABLE nhanvien ( manvchar(10) primary key,hotenchar(20) not null,ngaysinhdatetime null,diachichar(50) default 'khong biet',dienthoai char(6)check(dienthoai like '[0-9][0-9][0-9] [0-9][0-9][0-9]'),madvchar(2)foreign key(madv) references donvi(madv))ALTER TABLE table_name [ADD {col_namecolumn_properties[column_constraints] [[,]table_constraint ] } [,{next_col_name|next_table_constraint}]...] [DROP [CONSTRAINT] constraint_name1 [, constraint_name2]...]ALTER TABLE ADDdonviCONSTRAINT check_madv CHECK (madv LIKE [0-9][0-9]) 9. Giao trnh thc hanh SQLCREATE [CLUSTEREDNONCLUSTERED] INDEX index_name ONtable_name(column_name [, column_name]...)CREATE NONCLUSTERED INDEX idx_nhanvien_madv ON nhanvien(madv) 10. Trn Nguyn Phong CREATE VIEW view_name[(column_name [, column_name]...)] AS select_statement CREATE VIEW thongtin_nv AS SELECT manv,hoten,datediff(year,ngaysinh,getdate()),tendv FROM nhanvien,donvi WHERE nhanvien.madv=donvi.madvCREATE VIEW thongtin_nv(manv,hoten,tuoi,donvi) 11. Giao trnh thc hanh SQL AS SELECT manv,hoten,datediff(year,ngaysinh,getdate()),tendv FROM nhanvien,donvi WHERE nhanvien.madv=donvi.madv 12. Trn Nguyn PhongChng 2: NGN NG THAO TAC D LIU 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 l truy xut mt tp con cc dng trong mt hay nhiu bng), php chiu (tc l truy xut mt tp con cc ct trong mt hay nhiu bng) v php ni (tc l lin kt cc dng trong hai hay nhiu bng truy xut d liu). C php chung ca cu lnh SELECT c dng nh sau: SELECT[ ALL | DISTINCT ][ INTO FROMselect_list[ newtable_name ]]{ table_name | view_name } ................ [,{table_name | view_name }][WHEREclause ][GROUP BYclause ][HAVING BY clause ] [ORDER BYclause ][COMPUTEclause ]Ch : Cc thnh phn trong mt cu lnh SELECT phi c s dng theo th t c nu trn. 1.1 Xc nh bng bng mnh FROM Mnh FROM trong cu lnh SELECT c s dng nhm ch nh cc bng v khung nhn cn truy xut d liu. Sau mnh FROM l danh sch tn cc bng v khung nhn tham gia vo truy vn (tn ca cc bng v khung nhn c phn cch nhau bi du phy). SELECT FROMselect_list{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 hay khung nhn. B danh c gn trong mnh FROM bng cch ch nh b danh sau tn bng. V d cu lnh sau gn b danh n1 cho bng nhanvien. SELECTten, diachi FROMnhanvienn11.2 Mnh WHERE Mnh WHERE trong cu lnh SELECT xc nh cc iu kin i vi vic truy xut d liu. Sau mnh WHERE l mt biu thc logic v ch nhng dng d liu no tho mn biu thc sau WHERE mi c hin th trong kt qu truy vn. Trong mnh 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=Ln hn hoc bngKhng ln hn!1.92Gii hn (BETWEEN v NOT BETWEEN) T kho BETWEEN v NOT BETWEEN c s dng nhm ch nh khong gi tr tm kim i vi cu lnh SELECT. Cu lnh di y cho bit tn v ia ch ca nhng nhn vin c h s lng nm trong khong 1.92 n 3.11 SELECTten, tuoi, diachiFROM nhanvien WHEREhsluong BETWEEN1.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 cu lnh SELECT l mt danh sch cc gi tr. Sau IN (hoc NOT IN) c th l mt danh sch 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.92 hoc 2.11, thay v s dng cu lnh: SELECT * FROM nhanvien WHEREhsluong=1.86 OR hsluong=1.92 OR hsluong=2.11Ta c th s dng cu lnh sau: SELECT * FROM nhanvien WHEREhsluong IN (1.86, 1.92, 2.11)Cc k t i din v mnh LIKE T kho LIKE (NOT LIKE) s dng trong cu lnh SELECT nhm m t khun dng ca d liu cn tm kim. Chng thng c kt hp vi cc k t i din sau y: 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 Nam SELECT * WHEREFROM nhanvienhoten LIKE '% Nam'IS NULL v NOT IS NULL Gi tr NULL c th c nhp vo mt ct cho php chp nhn gi tr NULL theo mt trong ba cch sau: Nu khng c d liu c a vo v khng c mc nh cho ct hay kiu d 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 ch nh gy trn s. Trong mnh WHERE, ta s dng IS NULL hoc IS NOT NULL nh sau: WHERE col_name IS [NOT] NULL Cc ton t logic Cc 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 bng Khi mun truy xut tt c cc ct trong bng, ta s dng cu lnh SELECT c c php sau: SELECT*FROMtable_nameKhi s dng cu lnh ny, cc ct trong kt qu s c hin th theo th t m chng 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 c php sau: SELECT FROMtn_ct [,...,tn_ct_n] 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 s xc nh th t ca cc ct c hin th ra trong kt qu. * i tn cc ct trong cc kt qu Khi kt qu c hin th, tiu ca cc ct mc nh s l tn ca cc ct khi n c to ra trong cu lnh CREATE TABLE. Tuy nhin, cc tiu tr nn thn thin hn, ta c th i tn cc tiu ca cc ct. lm c vic ny, ta c th s dng mt trong hai cch vit sau: tiu__ct = tn_ct hoctn_ct tiu__ctV d 2.4: Hai cu lnh sau s t tiu H v tn cho l hoten v a ch cho ct diachi khi kt qu c hin th cho ngi s dng: SELECT 'H v tn'=hoten, 'a ch '= diachi FROMnhanvienHoc: SELECT hoten 'H v tn',diachi 'ia ch ' FROMnhanvien* S dng cu trc CASE thay i d liu trong kt qu Trong cu lnh SELECT, ta c th s dng cu trc CASE thay i cch hin th kt qu ra mn hnh. V d 2.5: Cu lnh sau cho bit h tn, h s lng v xp loi lng ca nhn vin theo h s lng: SELECT'H v tn' = ten,'H s lng' = hsluong,'Xp loi lng' = CASE WHENlsluong=NULL THEN'Khng xc nh' 16. Trn Nguyn Phong WHENhsluong=Ln hn hoc bngKhc 21. Giao trnh thc hanh SQL!>Khng ln hn!=2.11* Php t ni v cc b danh Php t ni l php ni m trong ta so snh cc gi tr bn trong mt ct ca cng mt bng. V d 2.16: Tm nhng nhn vin c cng a ch vi nhn vin 'Trn Nguyn Phong' SELECT n1.hoten FROM nhanvien n1, nhanvien n2 WHERE n2.hoten='Trn Nguyn Phong' AND n1.diachi = n2.diachi * Php ni khng da trn tiu chun bng Trong php ni ny, cc ct c s dng kt ni c so snh vi nhau khng da trn iu kin bng. * Php ni ngoi (outer join) Trong cc php ni cp trn, ch nhng dng hp l (tc l nhng dng c gi tr trong cc ct c ch nh tho m iu kin kt ni) mi c a ra trong kt qu. Theo mt ngha no , nhng php ni ny loi b thng tin cha trong nhng dng khng hp l. Tuy nhin, i khi ta cng cn gi li nhng thng tin khng 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 cp hai php ni ngoi: Php ni ngoi tri (*=) : Php ni ny cho php ly tt c cc t bng c tn u tin.Php ni ngoi phi (=*) : Php ni ny cho php ly tt c cc dng t bng c tn th hai.V d 2.17: Gi s ta c hai bng R v S c ni dung nh sau ABCD----- ----------- ----------- ----aaa24aaaafff84f2ggg27g4xxx212gdfggg212khfsss450k3hBng R EFG----- ----------- ----------aaa3(null)xxx2326abc36(null)12(null)sss320Bng S Khi cu lnh: SELECT * FROM R,S WHERE R.A = S.ECho kt qu l: ABC--------- -----DEFG----- ----------- --------aaa24aaaaaaa3xxx212gdfxxx23260k3hsss203sss 45 Cn cu lnh:(null) 23. Giao trnh thc hanh SQL SELECT * FROM R,S WHERE R.A *= S.ECho kt qu l: ABC------- -------------- ------------aaa24aaaaaaa(null)fff84f2(null) (null)(null)ggg27g4(null) (null)(null)xxx212gdfggg212khf0k3hsss 45DEFG3xxx2326(null) (null) sss(null)203V cu lnh SELECT * FROM R,S WHERE R.A =* S.ECho kt qu l: ABCDEFG------------------- ----------- ---------aaa24aaaaaaa3(null)xxx212gdfxxx232636(null)(null) (null)(null) abc(null)(null) (null)(null) (null)12sss450k3hsss(null)203Cc gii hn ca php ni ngoi Giao tc SQL khng cho php hai php ni ngoi lng nhau v php ni trong lng vo trong php ni