Giáo trình thực hành SQL (rất hay)

Giáo trình này nhằm cung cấp cho bạn tài liệu tham khảo tương đối đầy đủ về các câu lệnh giao tác SQL sử dụng cho hệ quản trị cCSDL Microsoft SQL Server. Giáo trình bao gòm 4 chương: Chương 1: Giới thiệu một số câu lệnh sử dụng trong việc định nghĩa các đối tượng dữ liệu như bảng dữ liệu, khung nhìn và chỉ mục Chương 2: Trình bày bốn câu lệnh thao tác dữ liệu và SELECT, INSERT, UPDATE, và DELETE; trong đó tập trung chủ yếu ở câu lệnh SELECT Chương 3: Đề cập tới hai câu lệnh điều khiển là GRANT và REVOKE sử dụng trong việc cấp phát và hủy bỏ quyền của người sử dụng CSDL Chương 4: Giới thiệu về thủ tục lưu trữ là trigger. Đây là những đối tượng CSDL được sử dụng nhằm tăng hiệu năng khi sử dụng CSDL.

pdf46 trang | Chia sẻ: diunt88 | Ngày: 17/08/2012 | Lượt xem: 1048 | Lượt tải: 63download
Tóm tắt tài liệu Giáo trình thực hành SQL (rất hay), để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
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Œ¤W WUŽ”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•QJ UD”QJEXŒ¤F&+(&. • 4XLŠ“QKJLD—WU“Pˆ£FŠ“QKFKRFD—FFŒ¤W UD”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/7 WŽ—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”QJ ED•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Œ¤W VŽ•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Œ¤W VŽ•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Œ¤W KD\QKL‹žX FŒ¤WEˆW EXŒ¤FFD—FJLD—WU“GŽ–OL‹£XWU‹QPŒ¤W KD\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Œ¤W KD\QKL‹žX FŒ¤WQD”RŠR—FX•DPŒ¤W ED•QJFR—JLD—WU“ŠŽ¤F[D—FŠ“QKWŽ”PŒ¤W KD\QKL‹žX WUŽ”QJNKRD—FX•DED•QJNKD—FWKFD—F FŒ¤WŠR—ŠŽ¤FJR¤LOD”FR—UD”QJEXŒ¤FNKRD—QJRD£L IRUHLJQNH\ &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,(1 0$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›F LQGH[ &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‰Q 9LHZ 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‰ X H[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Þ
Tài liệu liên quan