Giao trinh thuc hanh sql

  • Published on
    29-Jun-2015

  • View
    84

  • Download
    1

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 bng1.92 28. Trn Nguyn Phong(SELECT [ALL|DISTINCT] subquery_select_list [FROM{table_name|view_name} [optimizer_hints] [[,table_name2|view_bame2} [optimizer_hints] [...,table_name16|view_name16}[optimizer_hints]]][WHERE clause] [GROUP BY clause] [HAVING clause])(1)WHERE expression [NOT]IN (subquery)(2)WHERE expression comparison_operator [ANY|ALL](subquery)(3)WHERE [NOT]EXISTS (subquery)SELECT * FROM nhanvien WHERE madonvi NOT IN ( SELECT madonvi FROM donvi WHERE dienthoai like '82%') 29. Giao trnh thc hanh SQLINSERT [INTO]table_nameVALUES(value1, value2, ...)INSERT INTO nhanvien VALUES('NV02003','L Th Mai','23/5/72', NULL,'523312',1.92,'02')INSERT [INTO]table_name(col1,col2,..., colN)VALUES(value1, value2, ...,valueN)INSERT INTO nhanvien(manv,hoten,diachi) VALUES('NV03002','Nguyn Th Hanh Dung','56 Trn Phu')INSERT INTO luong_nhanvien SELECThoten,hsluong*210000 FROM nhanvien 30. Trn Nguyn Phong UPDATE SETupdated_table_namecolname = expression [, ..., colname = expression ][FROM [WHEREtable_name [,..., table_name]] conditions ]UPDATE nhanvien SET hsluong = hsluong+0.2 WHERE madonvi ='04' UPDATE nhatkybanhang SETthanhtien = soluong*mathang.dongiaFROM mathang WHERE nhatkybanhang.mahang = mathang.mahangDELETE[FROM]delete_table_name[FROM table_name [,..., table_name]] [WHERE conditions]DELETE FROM nhanvien 31. Giao trnh thc hanh SQL FROM donvi WHERE nhanvien.madonvi = donvi.madonvi AND donvi.dienthoai ='848484' 32. Trn Nguyn PhongChng 3: NGN NG IU KHINGRANT ALL | statement [,...,statementN ] TO account [,...,accountN]GRANT ALL | permission [,...,permissionN] ON table_name |view_name [(column1 [,...,columnN])] |ON stored_procedure TO account [,...,accountN] 33. Giao trnh thc hanh SQL GRANT CREATE TABLE, CREATE VIEW TO db_userGRANTSELECT, UPDATEON nhanvien(hoten,diachi,dienthoai,hsluong) TO db_user1, db_user2REVOKE ALL | statement [,...,statementN] FROM account [,...,accountN]REVOKE ALL | permission [,...,permissionN]} ON table_name | view_name [(column [,...,columnN])] | stored_procedure FROMaccount [,.