Giáo trình thực hành SQL (ĐH Huế)

Giaïo trçnh thæûc haình SQL 7UDQJ *,lv,7+,j€8 64/6WUXFWXUHG4XHU\/DQJXDJHOD”QJŒQQJŽ–ŠŽ¤FVŽ•GX£QJFKRFD—FK‹£TXD•Q WU“ F V• GŽ– OL‹£X TXDQK‹£ 1JŒQ QJŽ– 64/ FKX‰ Q ŠŽ¤F ŠŽD UD E•L $16, $PHULFDQ 1DWLRQDO 6WDQGDUGV ,QVWLWXGH YD”  ,62 ,QWHUQDWLRQDO 6WDQGDUGV 2UJDQL]DWLRQ Y—L SKL‹Q ED•Q P—L QK‰¢W KL‹£Q QD\ OD” SKL‹Q ED•Q 64/ SKL‹Q ED•Q ŠŽ¤F ŠŽD UD QˆP 0ˆ£FGX”FR—QKL‹žXQJŒQQJŽ–NKD—FQKDXŠŽ¤FŠŽDUDFKRFD—FK‹£TXD•QWU“&6'/ TXDQK‹£64/OD”QJŒQQJŽ–ŠŽ¤FVŽ•GX£QJUŒ¤QJUD–LKL‹£QQD\WURQJU‰¢WQKL‹žXK‹£WKŒ¢QJ &6'/WKŽQJPD£LQKŽ2UDFOH64/6HUYHU'%0LFURVRIW$FFHVV7KŒQJTXD64/ QJŽ”L VŽ•GX£QJFR— WK‹  G‹¡ GD”QJŠ“QKQJK‘DŠŽ¤FGŽ– OL‹£X WKDR WD—F Y—LGŽ– OL‹£X0ˆ£W NKD—FŠ‰\OD”QJŒQQJŽ–FR—W’QKNKDLED—RQ‹QQR—G‹¡VŽ•GX£QJYD”FX–QJYY‰£\PD”WU•Q‹Q SKŒ EL‹¢Q *LD—RWULQKQD”\QKˆšPFXQJF‰¢SFKRED£QWD”LOL‹£XWKDPNKD•RWŽQJŠŒ¢LŠ‰ž\ŠX•Y‹ž FD—F F‰X O‹£QK JLDR WD—F 64/ VŽ• GX£QJ FKR K‹£ TXD•Q WU“ &6'/0LFURVRIW 64/ 6HUYHU *LD—RWUQKEDRJŒžPEŒ¢QFKŽQJ &KŽQJJL—L WKL‹£XPŒ¤W VŒ¢ F‰X O‹£QK VŽ• GX£QJ WURQJYL‹£FŠ“QKQJK‘DFD—F ŠŒ¢L WŽ¤QJGŽ–OL‹£XQKŽED•QJGŽ–OL‹£XNKXQJQKQYD”FKPX£F  &KŽQJ  WUQK ED”\ EŒ¢Q F‰X O‹£QK WKDR WD—F GŽ– OL‹£X OD” 6(/(&7 ,16(57 83'$7(YD”'(/(7(WURQJŠR—W‰£SWUXQJFKX•\‹¢X•F‰XO‹£QK6(/(&7 &KŽQJŠ‹žF‰£SŠ‹¢QKDLF‰XO‹£QKŠL‹žXNKL‹ QOD”*5$17YD”5(92.(VŽ•GX£QJ WURQJYL‹£FF‰¢SSKD—WYD”KX\•ER•TX\‹žQFX•DQJŽ”LVŽ•GX£QJ&6'/  &KŽQJ  JL—L WKL‹£X Y‹ž WKX• WX£F OŽX WUŽ– YD” WULJJHU j‰\ OD” QKŽ–QJ ŠŒ¢L WŽ¤QJ &6'/ŠŽ¤FVŽ•GX£QJQKˆšPWˆQJKL‹£XQˆQJNKLVŽ•GX£QJ&6'/ 7URQJFKŽQJSKX£OX£FFKX—QJWŒLJL—LWKL‹£XF‰¢XWUX—FYD”GŽ–OL‹£XFX•DFD—FED•QJVŽ• GX£QJWURQJFD—FY’GX£•FKŽQJŠ‹ ED£QWL‹£QWUDFŽ—XYD”ŠŒ¢LFKL‹¢XY—LFD—FY’GX£ŠD–Q‹X 1JRD”LUDWURQJFKŽQJQD”\FR”QFR—FD—FKD”PWKŽ”QJVŽ•GX£QJWURQJ64/6HUYHUŠ‹žFD—F ED£QWKDPNKD•RWURQJWKŽ£FKD”QK 0ˆ£FGX”ŠD–U‰¢WFŒ¢JˆQJQKŽQJJLD—RWUQKNKŒQJWK‹ WUD—QKŠX¤FFD—FVDLVR—W5‰¢W PRQJQK‰£QŠŽ¤FVŽ£JR—S\—FX•DFD—FED£QŠ‹ JLD—RWUQKQJD”\FD”QJKRD”QWKL‹£QKQ Tráön Nguyãn Phong Chæång 1: NGÄN NGÆÎÎ ÂËNH NGHÉA DÆÎÎ LIÃÛÛU 1JŒQQJŽ–Š“QKQJK‘DGŽ–OL‹£XEDRJŒžPFD—FF‰XO‹£QKFKRSKH—SQJŽ”LVŽ•GX£QJŠ“QK QJK‘D&6'/YD”FD—FŠŒ¢LWŽ¤QJWURQJ&6'/QKŽFD—FED•QJFD—FNKXQJQKQFKPX£F 7D›REDQJGˆOL…›X 'Ž–OL‹£XE‹QWURQJPŒ¤W&6'/ŠŽ¤FWŒ FKŽ—FOŽXWUŽ–WURQJFD—FED•QJ%‹QWURQJFD—F ED•QJGŽ– OL‹£XŠŽ¤FWŒ FKŽ—FGŽ—LGD£QJFD—FGR”QJYD”FD—FFŒ¤W0Œ¡LPŒ¤WGR”QJEL‹ XGL‹¡Q PŒ¤WED•QJKLGX\QK‰¢WYD”PŒ¡LPŒ¤WFŒ¤WEL‹ XGL‹¡QFKRPŒ¤WWUŽ”QJ &D‘FWKX†œFWŒQKOL…QTXDQ„…šQEDQJ .KLWD£RYD” OD”PYL‹£FY—LFD—FED•QJGŽ– OL‹£X WDF‰žQSKD•LŠ‹ \—Š‹¢QFD—F WKXŒ¤F W’QK NKD—FWU‹QED•QJQKŽNL‹ XGŽ– OL‹£XFD—FUD”QJEXŒ¤FFD—FNKRD—FD—FTXL WˆF&D—F WKXŒ¤F W’QKQD”\ŠŽ¤FVŽ•GX£QJQKˆšPWD£RUDFD—FUD”QJEXŒ¤FWRD”QYH£QWU‹QFD—FFŒ¤WWUŽ”QJWU‹Q ED•QJFX–QJQKŽWD£RUDFD—FWRD”QYH£QWKDPFKL‹¢XJLŽ–DFD—FED•QJGŽ–OL‹£XWURQJ&6'/ D.L…˜XGˆOL…›X 0Œ¡LPŒ¤WFŒ¤W WUŽ”QJFX•DPŒ¤WED•QJŠ‹žXSKD•L WKXŒ¤FYD”RPŒ¤WNL‹ XGŽ– OL‹£XQK‰¢W Š“QKŠD–ŠŽ¤FŠ“QKQJK‘D WŽ” WUŽ—F0Œ¡LPŒ¤WNL‹ XGŽ– OL‹£XTXLŠ“QKFD—FJLD— WU“GŽ– OL‹£X ŠŽ¤FFKRSKH—SŠŒ¢LY—LFŒ¤WŠR—&D—FK‹£TXD•QWU“&6'/WKŽ”QJFXQJF‰¢SFD—FNL‹ XGŽ–OL‹£X FKX‰ QQJRD”LUDFR”QFR—WK‹ FKRSKH—SQJŽ”LVŽ•GX£QJŠ“QKQJK‘DFD—FNL‹ XGŽ–OL‹£XNKD—F GŽ£DWU‹QFD—FNL‹ XGŽ–OL‹£XŠD–FR— 'Ž—LŠ‰\OD”PŒ¤WVŒ¢NL‹ XGŽ–OL‹£XWKŽ”QJŠŽ¤FVŽ•GX£QJWURQJJLDRWD—F64/ Binary Bit Char Datetime Decimal Float Image Int Money Nchar Ntext Nvarchar Real Smalldatetime Smallint Smallmoney Text Tinyint Varbinary Varchar E&D‘FUDŽQJEX†œF&21675$,176 7U‹QFD—FED•QJGŽ–OL‹£XFD—FUD”QJEXŒ¤FŠŽ¤FVŽ•GX£QJQKˆšPFD—FPX£FŠ’FKVDX • 4XLŠ“QKFD—FJLD—WU“GŽ–OL‹£XKD\NKXŒQGD£QJGŽ–OL‹£XŠŽ¤FFKRSKH—SFK‰¢SQK‰£QWU‹Q FD—FFŒ¤WFX•DED•QJUD”QJEXŒ¤F&+(&. • 4XLŠ“QKJLD—WU“Pˆ£FŠ“QKFKRFD—FFŒ¤WUD”QJEXŒ¤F'()$8/7 Giaïo trçnh thæûc haình SQL 7UDQJ • 7D£RQ‹QW’QKWRD”QYH£QWKŽ£FWK‹ WURQJPŒ¤WED•QJGŽ–OL‹£XYD”WRD”QYH£QWKDPFKL‹¢XJLŽ–D FD—F ED•QJ GŽ– OL‹£X WURQJ &6'/ UD”QJ EXŒ¤F 35,0$5< .(< 81,48( YD” )25(,*1.(< &KX—QJWDVH–WPKL‹ XFKLWL‹¢WKQY‹žFD—FUD”QJEXŒ¤FQD”\•SK‰žQWUQKED”\Y‹žF‰XO‹£QK &5($7(7$%/( 7D›REDQJE‚“QJWUX\YƒšQ64/ 7D£RFD—FED•QJ OD”PŒ¤WNK‰XTXDQ WURQJ WUR¤QJ WURQJTXD— WUQK WKL‹¢WN‹¢YD” FD”LGˆ£W FD—F&6'/%‹QWURQJFD—F&6'/PŒ¡LPŒ¤WED•QJWKŽ”QJŠŽ¤FVŽ•GX£QJQKˆšPEL‹ XGL‹¡Q WKŒQJWKLY‹žFD—FŠŒ¢LWŽ¤QJWURQJWK‹¢JL—LWKŽ£FYD”KRˆ£FEL‹ XGL‹¡QPŒ¢LTXDQK‹£JLŽ–DFD—F ŠŒ¢L WŽ¤QJŠR—j‹ FR— WK‹  WŒ FKŽ—FWŒ¢WPŒ¤WED•QJGŽ– OL‹£XED£Q’WQK‰¢WF‰žQSKD•L[D—FŠ“QK ŠŽ¤FFD—F\‹XF‰žXVDX • %D•QJŠŽ¤FVŽ•GX£QJQKˆšPPX£FŠ’FKJYD”FR—YDLWUR”QKŽWK‹¢QD”RE‹QWURQJ&6'/" • %D•QJVH–EDRJŒžPQKŽ–QJFŒ¤WQD”RYD”NL‹ XGŽ–OL‹£XFKRFD—FFŒ¤WŠR—OD”J" • 1KŽ–QJFŒ¤WQD”RFKRSKH—SFK‰¢SQK‰£QJLD—WU“18// • &R—VŽ•GX£QJFD—FUD”QJEXŒ¤FFD—FPˆ£FŠ“QKKD\NKŒQJYD”Q‹¢XFR—WKVŽ•GX£QJ•Š‰XYD” QKXWK‹¢QD”R" • 1KŽ–QJFŒ¤WQD”RVH–ŠR—QJYDLWUR”OD”NKRD—FK’QKNKRD—QJRD”LNKRD—GX\QK‰¢W"1KŽ–QJ GD£QJFKPX£FQD”ROD”F‰žQWKL‹¢WYD”F‰žQ•Š‰X D7D›REDQJGˆOL…›X j‹  WD£RPŒ¤WED•QJ WURQJ&6'/ED£Q VŽ• GX£QJF‰X O‹£QK&5($7(7$%/(FR— FX— SKD—SQKŽVDX 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] ) 7URQJ„R‘ - table_name: 7‹Q ED•QJ F‰žQ WD£R 7‹Q FX•D ED•QJ SKD•L GX\ QK‰¢W WURQJPŒ¡L&6'/YD”SKD•LWX‰QWKHRFD—FTXLWˆFY‹ž Š“QKGDQK - colname_i: 7‹QFX•DFŒ¤W WKŽ— L WURQJED•QJ&D—FFŒ¤W WURQJPŒ¡L Tráön Nguyãn Phong ED•QJSKD•LFR—W‹QNKD—FQKDXYD”SKD•LWX‰QWKHRFD—F TXL WˆF Y‹ž Š“QK GDQK0Œ¡L PŒ¤W ED•QJ SKD•L FR— ’W QK‰¢WPŒ¤WFŒ¤W - col_i_properties: &D—FWKXŒ¤FW’QKFX•DFŒ¤WWKŽ—LTXLŠ“QKNL‹ XGŽ–OL‹£X FX•D FŒ¤W YD” FK Š“QK FŒ¤W FR— FKR SKH—S FK‰¢S QK‰£Q JLD—WU“18//KD\NKŒQJ - constraints_i: &D—F UD”QJ EXŒ¤F Q‹¢X FR— WU‹Q FŒ¤W WKŽ— L QKŽ FD—F UD”QJEXŒ¤FY‹žNKRD—FD—FPˆ£FŠ“QKFD—FTXLŠ“QKY‹ž NKXŒQGD£QJGŽ–OL‹£X - table_constraint: &D—FUD”QJEXŒ¤FWU‹QED•QJGŽ–OL‹£X 9ŒGX›&‰XO‹£QKGŽ—LŠ‰\WKŽ£FKL‹£QKYL‹£FWD£RED•QJ1+$19,(1EDRJŒžPFD—FFŒ¤W 0$19+27(11*$<6,1+',$&+,',(17+2$, CREATE TABLE nhanvien ( manv char(10) not null, hoten char(30) not null, ngaysinh datetime null, diachi char(50) null, dienthoai char(6) null ) E6ˆGX›QJFD‘FUDŽQJEX†œFWURQJEDQJGˆOL…›X D5DŽQJEX†œF&+(&. 5D”QJEXŒ¤F&+(&.ŠŽ¤FVŽ•GX£QJŠ‹ FKŠ“QKFD—FJLD—WU“KD\NKXŒQGD£QJGŽ–OL‹£X FR— WK‹  ŠŽ¤F FK‰¢S QK‰£Q ŠŒ¢L Y—L PŒ¤W FŒ¤W 7U‹Q PŒ¤W FŒ¤W FR— WK‹  FR— QKL‹žX UD”QJ EXŒ¤F &+(&.j‹ NKDLED—RPŒ¤WUD”QJEXŒ¤F&+(&.ŠŒ¢LY—LPŒ¤WFŒ¤WQD”RŠR—WDVŽ•GX£QJFX— SKD—SQKŽVDX [CONSTRAINT constraint_name] CHECK (expression) 7URQJŠR—H[SUHVVLRQ OD”PŒ¤WEL‹ X WKŽ—F ORJLFTXLŠ“QKJLD— WU“KD\NKXŒQGD£QJFX•DGŽ– OL‹£XŠŽ¤FFKRSKH—S.KLŠR—FKQKŽ–QJJLD—WU“GŽ–OL‹£XQD”ROD”PFKRH[SUHVVLRQQK‰£QJLD— WU“ŠX—QJP—LŠŽ¤FFK‰¢SQK‰£Q 9ŒGX›j‹ TXLŠ“QKŠL‹£Q WKRD£LFX•DQK‰QYL‹QSKD•LFR—GD£QJ   FKˆ›QJKD£Q F‰XO‹£QK•Y’GX£ŠŽ¤FYL‹¢WQKŽVDX CREATE TABLE nhanvien ( Giaïo trçnh thæûc haình SQL 7UDQJ manv char(10) not null, hoten char(30) not null, 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] [0-9]') ) D5DŽQJEX†œF'()$8/7 5D”QJEXŒ¤F'()$8/7ŠŽ¤FVŽ•GX£QJŠ‹ TXLŠ“QKJLD—WU“Pˆ£FŠ“QKFKRPŒ¤WFŒ¤W *LD—WU“QD”\VH–WŽ£ŠŒ¤QJŠŽ¤FJD—QFKRFŒ¤WQD”\NKLQJŽ”LVŽ•GX£QJEŒ VXQJPŒ¤WED•QJKL PD”NKŒQJFKŠ“QKJLD— WU“FKRFŒ¤W7U‹QPŒ¡L FŒ¤W FKFR— WK‹  FR— QKL‹žXQK‰¢WPŒ¤W UD”QJ EXŒ¤F'()$8/7WŽ—FOD”FKFR—WK‹ FR—WŒ¢LŠDPŒ¤WJLD—WU“Pˆ£FŠ“QK j‹  NKDL ED—R PŒ¤W JLD— WU“ Pˆ£F Š“QK FKR PŒ¤W FŒ¤W WD FK Š“QK PŒ¤W UD”QJ EXŒ¤F '()$8/7FKRFŒ¤WEˆšQJFD—FKVŽ•GX£QJFX—SKD—SVDX [CONSTRAINT constraint_name] DEFAULT {const_expression  nonarguments_function  NULL} 9Œ GX›  &‰X O‹£QK GŽ—L Š‰\ FK Š“QK JLD— WU“ Pˆ£F Š“QK OD” NKŒQJ EL‹¢W  FKR FŒ¤W ',$&+,WURQJED•QJ1+$19,(1•Y’GX£  CREATE TABLE nhanvien ( manv char(10) not null, hoten char(30) not null, ngaysinh datetime null, diachi char(50) default 'khäng biãút', dienthoai char(6) null ) D5DŽQJEX†œF35,0$5<.(<   muY’GX£QD”\FKX—QJWŒLNKŒQJŠˆ£WW‹QFKRUD”QJEXŒ¤F'()$8/7.KLŠR—K‹£TXD•QWU“&6'/VH–WŽ£ŠŒ¤QJŠˆ£WW‹Q FKRUD”QJEXŒ¤FQD”\7X\QKL‹QŠ‹ G‹¡GD”QJFKRYL‹£FTXD•QWU“ED£QQ‹QŠˆ£WW‹QFKRFD—FUD”QJEXŒ¤F Tráön Nguyãn Phong 5D”QJEXŒ¤F35,0$5<.(<ŠŽ¤FVŽ•GX£QJŠ‹ Š“QKQJK‘DNKRD—FK’QKFX•DED•QJ 0Œ¤W UD”QJEXŒ¤F35,0$5<.(<ŠD•PED•R UˆšQJNKŒQJFR— FD—FJLD— WU“ WUX”QJ Oˆ£SŠŽ¤F ŠŽDYD”R WU‹QFD—F FŒ¤W+D\QR—L FD—FKNKD—FJLD— WU“ FX•DNKRD— FK’QKVH– JLX—SFKR WD[D—F Š“QKŠŽ¤FGX\QK‰¢WPŒ¤WGR”QJED•QJKLWURQJED•QJGŽ–OL‹£X0Œ¡LPŒ¤WED•QJFKFR—WK‹  FR—GX\QK‰¢WPŒ¤WNKRD—FK’QKYD”ED•QWK‰QNKRD—FK’QKNKŒQJFK‰¢SQK‰£QJLD—WU“18// 5D”QJEXŒ¤F35,0$5<.(<OD”FV•FKRYL‹£FŠD•PED•RW’QKWRD”QYH£QWKŽ£F WK‹ FX–QJ QKŽWRD”QYH£QWKDPFKL‹¢X j‹ NKDLED—RPŒ¤WUD”QJEXŒ¤F35,0$5<.(<ED£QVŽ•GX£QJFX—SKD—SVDX [CONSTRAINT constraint_name ] PRIMARY KEY [CLUSTERED|NONCLUSTERED] [( colname [,colname2 [...,colname16]])] 1‹¢XNKRD—FK’QKFX•DPŒ¤WED•QJFKOD”PŒ¤WFŒ¤WNKLŠR—ED£QNKŒQJF‰žQWKL‹¢WSKD•LFK Š“QKGDQKVD—FKFD—FFŒ¤WVŽ•GX£QJUD”QJEXŒ¤F•PŽ—FFŒ¤W7URQJWUŽ”QJK¤SNKRD—FK’QK OD”PŒ¤WW‰£SK¤SWŽ”KDLFŒ¤WWU•O‹QED£QSKD•LFKŠ“QKGDQKVD—FKFD—FFŒ¤WVŽ•GX£QJUD”QJ EXŒ¤F•PŽ—FED•QJ 9ŒGX›WD£RED•QJ1+$19,(1Y—LNKRD—FK’QKOD”0$19 CREATE TABLE nhanvien ( manv char(10) primary key, hoten char(30) not null, ngaysinh datetime null, diachi char(50) null, dienthoai char(6) null ) F‰XO‹£QKWU‹QFR—WK‹ YL‹¢WQKŽVDX 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) ) Giaïo trçnh thæûc haình SQL 7UDQJ D 5DŽQJEX†œF81,48( 7KD\Y VŽ• GX£QJNKRD— FK’QKED£QFR— WK‹  VŽ• GX£QJ UD”QJEXŒ¤F81,48(Š‹  ŠD•P ED•RW’QKWRD”QYH£QWKŽ£FWK‹ 6Ž•GX£QJUD”QJEXŒ¤F81,48(WU‹QPŒ¤WKD\QKL‹žXFŒ¤WEˆW EXŒ¤FFD—FJLD—WU“GŽ–OL‹£XWU‹QPŒ¤WKD\QKL‹žXFŒ¤WQD”\NKŒQJŠŽ¤FWUX”QJOˆ£SQKDXj‹  NKDLED—RPŒ¤WUD”QJEXŒ¤F81,48(ED£QVŽ•GX£QJFX—SKD—SO‹£QKVDXŠ‰\ [CONSTRAINT constraint_name] UNIQUE [CLUSTERED | NONCLUSTERED] [colname1 [,colname2 [...,colname16]])] D 5DŽQJEX†œF)25(,*1.(< &D—FED•QJE‹QWURQJPŒ¤W&6'/WKŽ”QJFR—PŒ¢LTXDQK‹£Y—LQKDX&D—FPŒ¢LTXDQ K‹£QD”\ŠŽ¤F[D—FŠ“QKGŽ£D WU‹Q W’QKEˆšQJQKDXJLŽ–DPŒ¤WKD\QKL‹žX WUŽ”QJFX•DED•QJ QD”\Y—LPŒ¤WKD\QKL‹ž”XWUŽ”QJFX•DED•QJNKD—F1‹¢XPŒ¤WKD\QKL‹žXFŒ¤WQD”RŠR—FX•DPŒ¤W ED•QJFR—JLD—WU“ŠŽ¤F[D—FŠ“QKWŽ”PŒ¤WKD\QKL‹žXWUŽ”QJNKRD—FX•DED•QJNKD—FWKFD—F FŒ¤WŠR—ŠŽ¤FJR¤LOD”FR—UD”QJEXŒ¤FNKRD—QJRD£LIRUHLJQNH\&D—FUD”QJEXŒ¤F)25(,*1 .(<ŠŽ¤FVŽ•GX£QJN‹¢WK¤SY—LFD—F UD”QJEXŒ¤F35,0$5<.(<YD”81,48(QKˆšP ŠD•PED•RW’QKWRD”QYH£QWKDPFKL‹¢XJLŽ–DFD—FED•QJŠŽ¤FFKŠ“QK j‹ NKDLED—RNKRD—QJRD£LED£QVŽ•GX£QJFX—SKD—SO‹£QKQKŽVDX [CONSTRAINT constraint_name ] [FOREIGN KEY (colname [,colname2 [...,colname16]])] REFERENCES reference_table [(ref_colname [,ref_colname2 [...,ref_colname 16]])] 9Œ GX›  7D£R KDL ED•QJ 1+$19,(10$19 +27(1 1*$<6,1+ ',$&+, ',(17+2$,0$'9YD”'219,0$'97(1'9WKHRŠŒžKQKGŽ—LŠ‰\ CREATE TABLE donvi ( 1+$19,(1 0$19 +27(1 1*$<6,1+ ',$&+, ',(17+2$, 0$'9 '219, 0$'9 7(1'9  ∞ +QK  Tráön Nguyãn Phong 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][0-9]'), madv char(2) foreign key(madv) references donvi(madv) ) 6ˆD„†˜LEDQJ 6DXNKLŠD– WD£RED•QJED£QFR— WK‹  WL‹¢QKD”QKVŽ•DŠŒ LF‰¢X WUX—FKD\ WKXŒ¤F W’QKFX•D ED•QJ QKŽEŒ  VXQJ FŒ¤W EŒ  VXQJ NKRD— WKD\ ŠŒ L FD—F UD”QJ EXŒ¤Fj‹  FR— WK‹  VX•D ŠŒ L ED•QJED£QVŽ•GX£QJF‰XO‹£QK$/7(5FR—FX—SKD—SQKŽVDX 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]...] 9ŒGX›7D£RPŒ¤WUD”QJEXŒ¤FFKRED•QJ'219,WU‹QFŒ¤W0$'9TXLŠ“QKPD–ŠQY“ SKD•LFR—GD£QJKDLFKŽ–VŒ¢Y’GX£ ALTER TABLE donvi ADD CONSTRAINT check_madv CHECK (madv LIKE ‘[0-9][0-9]’) Giaïo trçnh thæûc haình SQL 7UDQJ &KŠPX›FLQGH[ &D—FFKPX£FŠŽ¤FVŽ•GX£QJQKˆšPKŒ¡WU¤YL‹£FWUX\F‰£SŠ‹¢QFD—FGR”QJGŽ–OL‹£XŠŽ¤F QKDQKFKR—QJGŽ£DWU‹QFD—FJLD—WU“FX•DPŒ¤WKD\QKL‹žXFŒ¤W&KPX£FŠŽ¤FFKLDUDOD”PKDL ORD£L FKPX£F WX£ QKR—P FOXVWHUHG LQGH[ YD” FKPX£F NKŒQJ WX£ QKR—P QRQFOXVWHUHG LQGH[ • 0Œ¤WFKPX£FWX£QKR—POD”PŒ¤WFKPX£FPD”WURQJŠR—WKŽ—WŽ£ORJLFFX•DFD—FNKRD—WŽQJ WŽ£QKŽWKŽ—WŽ£Y‰£WO\—FX•DFD—FGR”QJWŽQJŽ—QJWŒžQWD£L WURQJED•QJ0Œ¤WED•QJFKFR— WK‹ FR—WŒ¢LŠDPŒ¤WFKPX£FWX£QKR—P • 0Œ¤WFKPX£FNKŒQJWX£KR—POD”PŒ¤WFKPX£FPD”WURQJŠR—WKŽ—WŽ£ORJLFFX•DFD—FNKRD— NKŒQJQKŽWKŽ—WŽ£Y‰£WO\—FX•DFD—FGR”QJWURQJED•QJ&D—FFKPX£FWX£QKR—PKŒ¡WU¤YL‹£F WUX\F‰£SŠ‹¢QFD—FGR”QJGŽ–OL‹£XQKDQKKQQKL‹žXVRY—LFD—FFKPX£FNKŒQJWX£QKR—P .KLWDNKDLED—RPŒ¤WNKRD—FK’QKKD\NKRD—81,48(WU‹QPŒ¤WKD\QKL‹žXFŒ¤WQD”RŠR— FX•DED•QJK‹£TXD•QWU“&6'/VH– WŽ£ŠŒ¤QJWD£RFKPX£F WU‹QFD—FFŒ¤WŠR—%D£QFR— WK‹  WD£R WK‹PFD—FFKPX£FNKD—FEˆšQJFD—FKVŽ•GX£QJF‰XO‹£QKFR—FX—SKD—SQKŽVDX CREATE [CLUSTEREDNONCLUSTERED] INDEX index_name ON table_name(column_name [, column_name]...) 9ŒGX›&‰XO‹£QKGŽ—LŠ‰\VH–WD£RPŒ¤WFKPX£FNKŒQJWX£QKR—PWU‹QFŒ¤W0$'9FX•D ED•QJ1+$19,(1 CREATE NONCLUSTERED INDEX idx_nhanvien_madv ON nhanvien(madv) .KXQJQK‰Q9LHZ 0Œ¤WNKXQJQKQFR—WK‹ FRLQKŽOD”PŒ¤WeED•QJD•RfFR—QŒ¤LGXQJŠŽ¤F[D—FŠ“QKWŽ” PŒ¤W WUX\Y‰¢Q0Œ¤W WUX\ Y‰¢Q TXHU\ OD”PŒ¤W W‰£S FD—F FK G‰¡Q LQVWUXFWLRQ QKˆšP WUX\ [X‰¢WYD”KL‹ QWK“GŽ–OL‹£XWŽ”FD—FED•QJWURQJ&6'/&D—FWUX\Y‰¢QŠŽ¤FWKŽ£FKL‹£QEˆšQJ FD—FKVŽ•GX£QJF‰XO‹£QK6(/(&7ŠŽ¤FŠ‹žF‰£SŠ‹¢QWURQJFKŽQJ 0Œ¤WNKXQJQKQWURQJJLŒ¢QJQKŽPŒ¤WED•QJY—LPŒ¤WW‰£RFD—FW‹QFŒ¤WYD”FD—FGR”QJ GŽ–OL‹£X7X\QKL‹QNKXQJQKQNKŒQJWŒžQWD£LQKŽOD”PŒ¤WF‰¢XWUX—FOŽXWUŽ–GŽ–OL‹£XWURQJ &6'/'Ž–OL‹£XE‹QWURQJNKXQJQKQWKŽ£FFK‰¢W OD”GŽ– OL‹£XŠŽ¤F[D—FŠ“QKWŽ”PŒ¤WKD\ QKL‹žXED•QJFV•YD”GRŠR—SKX£WKŒ¤FYD”RFD—FED•QJFV• &D—FNKXQJQKQWKŽ”QJŠŽ¤FVŽ•GX£QJE‹QWURQJ&6'/QKˆšPFD—FPX£FŠ’FKVDX Š‰\ • 6Ž•GX£QJNKXQJQKQŠ‹ W‰£SWUXQJWU‹QGŽ–OL‹£XŠŽ¤F[D—FŠ“QK Tráön Nguyãn Phong • 6Ž•GX£QJNKXQJQKQŠ‹ ŠQJLD•QKRD—WKDRWD—FGŽ–OL‹£X • 6Ž•GX£QJNKXQJQKQŠ‹ WX\”EL‹¢QGŽ–OL‹£X • 6Ž•GX£QJNKXQJQKQŠ‹ [X‰¢WNK‰ XH[SRUWGŽ–OL‹£X • 6Ž•GX£QJNKXQJQKQŠ‹ ED•RP‰£WGŽ–OL‹£X j‹ WD£RNKXQJQKQED£QVŽ•GX£QJF‰XO‹£QKFR—FX—SKD—SQKŽVDX CREATE VIEW view_name[(column_name [, column_name]...)] AS select_statement 7URQJŠR—VHOHFWBVWDWHPHQWOD”PŒ¤WF‰XO‹£QK6(/(&7GX”QJŠ‹ WUX\[X‰¢WGŽ–OL‹£XWŽ” PŒ¤WKD\QKL‹žXED•QJ .KLWD£RNKXQJQKQF‰žQOŽX\—PŒ¤WVŒ¢ŠL‹ PVDX • 7‹QNKXQJQKQSKD•LWX‰QWKHRFD—FTXLWˆFY‹žŠ“QKGDQKYD”SKD•LGX\QK‰¢WŠŒ¢LY—L PŒ¡LQJŽ”LVŽ•GX£QJ • .KŒQJWK‹ UD”QJEXŒ¤FFD—FPˆ£FŠ“QKFD—FTXLWˆFFKRNKXQJQKQ • .KŒQJWK‹ [‰\GŽ£QJFKPX£FFKRNKXQJQKQ • 7URQJF‰X O‹£QK&5($7(9,(:NKŒQJF‰žQ WKL‹¢WSKD•L FKŠ“QK W‹QFŒ¤W7‹QFX•D FD—FFŒ¤WFX–QJQKŽNL‹ XGŽ–OL‹£XFX•DFKX—QJVH–WŽQJŽ—QJY—LFD—FFŒ¤WWURQJGDQKVD—FK FKR¤QFX•DF‰XO‹£QK6(/(&7 • %D£QSKD•L[D—FŠ“QKW‹QFŒ¤W WURQJF‰X O‹£QK&5($7(9,(:WURQJFD—F WUŽ”QJK¤S VDX − 0Œ¡LFŒ¤WFX•DNKXQJQKQŠŽ¤FSKD—WVLQKWŽ”PŒ¤WEL‹ XWKŽ—FVŒ¢KR¤FPŒ¤WKD”PFD”L VˆœQKD\PŒ¤WKˆšQJ − +DLKD\QKL‹žXFŒ¤WFX•DNKXQJQKQFR—WUX”QJW‹Q − %D£QPXŒ¢QWKD\ŠŒ LW‹QFŒ¤WWURQJNKXQJQKQNKD—FY—LW‹QFŒ¤WFX•DED•QJFV• 9ŒGX›&‰XO‹£QKGŽ—LŠ‰\VH–E“OŒ¡LGRW‹QFX•DFŒ¤WWKŽ—NKŒQJ[D—FŠ“QKŠŽ¤F CREATE VIEW thongtin_nv AS SELECT manv,hoten,datediff(year,ngaysinh,getdate()),tendv FROM nhanvien,donvi WHERE nhanvien.madv=donvi.madv j‹ F‰XO‹£QKWU‹QFR—WK‹ WKŽ£FKL‹£QŠŽ¤FED£QSKD•LŠˆ£W W‹QFKRFD—FFŒ¤WFX•DNKXQJQKQ QKŽVDX CREATE VIEW thongtin_nv(manv,hoten,tuoi,donvi) Giaïo trçnh thæûc haình SQL 7UDQJ AS SELECT manv,hoten,datediff(year,ngaysinh,getdate()),tendv FROM nhanvien,donvi WHERE nhanvien.madv=donvi.madv Z Y Tráön Nguyãn Phong Chæång 2: NGÄN NGÆÎÎ THAO TAÏÏC DÆÎÎ LIÃÛÛU 1JŒQQJŽ–WKDRWD—FGŽ–OL‹£XFXQJF‰¢SFKRQJŽ”LVŽ•GX£QJNKD•QˆQJWL‹¢QKD”QKFD—F WKDRWD—FWUX\[X‰¢WEŒ VXQJF‰£SQK‰£WYD”[RD—GŽ–OL‹£X1JŒQQJŽ–WKDRWD—FGŽ–OL‹£XEDR JŒžPFD—FF‰XO‹£QK6(/(&7,16(5783'$7(YD”'(/(7( 7UX\[XƒšWGˆOL…›X §Ó truy xuÊt d÷ liÖu tõ c¸c dßng vµ c¸c cét cña mét hay nhiÒu b¶ng, khung nh×n, ta sö dông c©u lÖnh SELECT. C©u lÖnh nµy cã thÓ dïng ®Ó thùc hiÖn phÐp chän (tøc lµ truy xuÊt mét tËp con c¸c dßng trong mét hay nhiÒu b¶ng), phÐp chiÕu (tøc lµ truy xuÊt mét tËp con c¸c cét trong mét hay nhiÒu b¶ng) vµ phÐp nèi (tøc lµ liªn kÕt c¸c dßng trong hai hay nhiÒu b¶ng ®Ó truy xuÊt d÷ liÖu). Có ph¸p chung cña c©u lÖnh SELECT cã d¹ng 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ó ý: C¸c thµnh phÇn trong mét c©u lÖnh SELECT ph¶i ®­îc sö dông theo thø tù ®­îc nªu trªn. 1.1 X¸c ®Þnh b¶ng b»ng mÖnh ®Ò FROM MÖnh ®Ò FROM trong c©u lÖnh SELECT ®­îc sö dông nh»m chØ ®Þnh c¸c b¶ng vµ khung nh×n cÇn truy xuÊt d÷ liÖu. Sau mÖnh ®Ò FROM lµ danh s¸ch tªn c¸c b¶ng vµ khung nh×n tham gia vµo truy vÊn (tªn cña c¸c b¶ng vµ khung nh×n ®­îc ph©n c¸ch nhau bëi dÊu phÈy). SELECT select_list FROM {table_nameview_name list} Giaïo trçnh thæûc haình SQL 7UDQJ §Ó ®¬n gi¶n ho¸ c©u hái, ta cã thÓ sö dông c¸c bÝ danh (alias) cho c¸c b¶ng hay khung nh×n. BÝ danh ®­îc g¸n trong mÖnh ®Ò FROM b»ng c¸ch chØ ®Þnh bÝ danh sau tªn b¶ng. VÝ dô c©u lÖnh sau g¸n bÝ danh n1 cho b¶ng nhanvien. SELECT ten, diachi FROM nhanvien n1 1.2 MÖnh ®Ò WHERE MÖnh ®Ò WHERE trong c©u lÖnh SELECT x¸c ®Þnh c¸c ®iÒu kiÖn ®èi víi viÖc truy xuÊt d÷ liÖu. Sau mÖnh ®Ò WHERE lµ mét biÓu thøc logic vµ chØ nh÷ng dßng d÷ liÖu nµo tho¶ m∙n biÓu thøc sau WHERE míi ®­îc hiÓn thÞ