Giáo trình SQL

Giaïo trçnh thæûc haình SQL

7UDQJ

*,lv,7+,j€8

64/6WUXFWXUHG4XHU\/DQJXDJHODQJŒQQJŽŠŽ¤FVŽ•GX£QJFKRFDFK‹£TXD•Q

WU F V• GŽOL‹£X TXDQ K‹£ 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ˆ£FGXFRQKL‹žXQJŒQQJŽNKDFQKDXŠŽ¤FŠŽDUDFKRFDFK‹£TXD•QWU&6'/

TXDQK‹£64/ODQJŒQQJŽŠŽ¤FVŽ•GX£QJUŒ¤QJUDLKL‹£QQD\WURQJU‰¢WQKL‹žXK‹£WKŒ¢QJ

&6'/WKŽQJPD£LQKŽ2UDFOH64/6HUYHU'%0LFURVRIW$FFHVV7KŒQJTXD64/

QJŽL VŽ• GX£QJFRWK‹  G‹¡ GDQJ ŠQK QJK‘D ŠŽ¤F GŽOL‹£XWKDRWDF YL GŽOL‹£X0ˆ£W

NKDFŠ‰\ODQJŒQQJŽFRW’QKNKDLEDRQ‹QQRG‹¡VŽ•GX£QJYDFXQJYY‰£\PDWU•Q‹Q

SKŒ EL‹¢Q

*LDRWULQKQD\QKˆšPFXQJF‰¢SFKRED£QWDLOL‹£XWKDPNKD•RWŽQJŠŒ¢LŠ‰ž\ŠX•Y‹ž

FDF F‰X O‹£QK JLDR WDF 64/ VŽ• GX£QJ FKR K‹£ TXD•Q WU &6'/ 0LFURVRIW 64/ 6HUYHU

*LDRWUQKEDRJŒžPEŒ¢QFKŽQJ

&KŽQJ  JLLWKL‹£XPŒ¤W VŒ¢ F‰XO‹£QK VŽ• GX£QJWURQJ YL‹£F ŠQK QJK‘D FDF ŠŒ¢L

WŽ¤QJGŽOL‹£XQKŽED•QJGŽOL‹£XNKXQJQKQYDFKPX£F

 &KŽQJ  WUQK ED\ EŒ¢Q F‰X O‹£QK WKDR WDF GŽ OL‹£X OD 6(/(&7 ,16(57

83'$7(YD'(/(7(WURQJŠRW‰£SWUXQJFKX•\‹¢X•F‰XO‹£QK6(/(&7

&KŽQJŠ‹žF‰£SŠ‹¢QKDLF‰XO‹£QKŠL‹žXNKL‹ QOD*5$17YD5(92.(VŽ•GX£QJ

WURQJYL‹£FF‰¢SSKDWYDKX\•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£FFKXQJWŒLJLLWKL‹£XF‰¢XWUXFYDGŽOL‹£XFX•DFDFED•QJVŽ•

GX£QJWURQJFDFY’GX£•FKŽQJŠ‹ ED£QWL‹£QWUDFŽXYDŠŒ¢LFKL‹¢XYLFDFY’GX£ŠDQ‹X

1JRDLUDWURQJFKŽQJQD\FRQFRFDFKDPWKŽQJVŽ•GX£QJWURQJ64/6HUYHUŠ‹žFDF

ED£QWKDPNKD•RWURQJWKŽ£FKDQK

0ˆ£FGXŠDU‰¢WFŒ¢JˆQJQKŽQJJLDRWUQKNKŒQJWK‹ WUDQKŠX¤FFDFVDLVRW5‰¢W

PRQJQK‰£QŠŽ¤FVŽ£JRS\FX•DFDFED£QŠ‹ JLDRWUQKQJD\FDQJKRDQWKL‹£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ŒžPFDFF‰XO‹£QKFKRSKHSQJŽLVŽ•GX£QJŠQK

QJK‘D&6'/YDFDFŠŒ¢LWŽ¤QJWURQJ&6'/QKŽFDFED•QJFDFNKXQJQKQFKPX£F

7D›REDQJGˆOL…›X

'ŽOL‹£XE‹QWURQJPŒ¤W&6'/ŠŽ¤FWŒ FKŽFOŽXWUŽWURQJFDFED•QJ%‹QWURQJFDF

ED•QJGŽOL‹£XŠŽ¤FWŒ FKŽFGŽLGD£QJFDFGRQJYDFDFFŒ¤W0Œ¡LPŒ¤WGRQJEL‹ XGL‹¡Q

PŒ¤WED•QJKLGX\QK‰¢WYDPŒ¡LPŒ¤WFŒ¤WEL‹ XGL‹¡QFKRPŒ¤WWUŽQJ

&D‘FWKX†œFWŒQKOL…QTXDQ„…šQEDQJ

.KLWD£R YDODP YL‹£F YLFDF ED•QJ GŽOL‹£XWDF‰žQ SKD•L Š‹  \ Š‹¢QFDFWKXŒ¤FW’QK

NKDFWU‹QED•QJQKŽNL‹ XGŽOL‹£XFDF UDQJEXŒ¤FFDFNKRDFDFTXLWˆF&DFWKXŒ¤F

W’QKQD\ŠŽ¤FVŽ•GX£QJQKˆšPWD£RUDFDFUDQJEXŒ¤FWRDQYH£QWU‹QFDFFŒ¤WWUŽQJWU‹Q

ED•QJFXQJQKŽWD£RUDFDFWRDQYH£QWKDPFKL‹¢XJLŽDFDFED•QJGŽOL‹£XWURQJ&6'/

D.L…XGˆOL…›X

0Œ¡LPŒ¤WFŒ¤W WUŽQJFX•DPŒ¤W ED•QJ Š‹žX SKD•LWKXŒ¤F YDRPŒ¤W NL‹ X GŽOL‹£X QK‰¢W

ŠQKŠDŠŽ¤FŠQKQJK‘DWŽWUŽF0Œ¡LPŒ¤W NL‹ X GŽOL‹£X TXL ŠQK FDF JLDWU GŽOL‹£X

ŠŽ¤FFKRSKHSŠŒ¢LYLFŒ¤WŠR&DFK‹£TXD•QWU&6'/WKŽQJFXQJF‰¢SFDFNL‹ XGŽOL‹£X

FKX‰ QQJRDLUDFRQFRWK‹ FKRSKHSQJŽLVŽ•GX£QJŠQKQJK‘DFDFNL‹ XGŽOL‹£XNKDF

GŽ£DWU‹QFDFNL‹ XGŽOL‹£XŠDFR

'ŽLŠ‰\ODPŒ¤WVŒ¢NL‹ XGŽOL‹£XWKŽQJŠŽ¤FVŽ•GX£QJWURQJJLDRWDF64/

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‹QFDFED•QJGŽOL‹£XFDFUDQJEXŒ¤FŠŽ¤FVŽ•GX£QJQKˆšPFDFPX£FŠ’FKVDX

• 4XLŠQKFDFJLDWUGŽOL‹£XKD\NKXŒQGD£QJGŽOL‹£XŠŽ¤FFKRSKHSFK‰¢SQK‰£QWU‹Q

FDFFŒ¤WFX•DED•QJUDQJEXŒ¤F&+(&.

• 4XLŠQKJLDWUPˆ£FŠQKFKRFDFFŒ¤WUDQJEXŒ¤F'()$8/7

Giaïo trçnh thæûc haình SQL

7UDQJ

• 7D£RQ‹QW’QKWRDQYH£QWKŽ£FWK‹ WURQJPŒ¤WED•QJGŽOL‹£XYDWRDQYH£QWKDPFKL‹¢XJLŽD

FDF ED•QJ GŽ OL‹£X WURQJ &6'/ UDQJ EXŒ¤F 35,0$5< .(< 81,48( YD

)25(,*1.(<

&KXQJWDVHWPKL‹ XFKLWL‹¢WKQY‹žFDFUDQJEXŒ¤FQD\•SK‰žQWUQKED\Y‹žF‰XO‹£QK

&5($7(7$%/(

7D›REDQJE‚QJWUX\YƒšQ64/

7D£RFDFED•QJODPŒ¤WNK‰XTXDQWURQJWUR¤QJWURQJ TXDWUQKWKL‹¢W N‹¢ YDFDL Gˆ£W

FDF&6'/%‹QWURQJFDF&6'/PŒ¡LPŒ¤WED•QJWKŽQJŠŽ¤FVŽ•GX£QJQKˆšPEL‹ XGL‹¡Q

WKŒQJWKLY‹žFDFŠŒ¢LWŽ¤QJWURQJWK‹¢JLLWKŽ£FYDKRˆ£FEL‹ XGL‹¡QPŒ¢LTXDQK‹£JLŽDFDF

ŠŒ¢LWŽ¤QJŠRj‹ FRWK‹ WŒ FKŽFWŒ¢WPŒ¤WED•QJGŽOL‹£XED£Q’WQK‰¢WF‰žQSKD•L[DFŠQK

ŠŽ¤FFDF\‹XF‰žXVDX

• %D•QJŠŽ¤FVŽ•GX£QJQKˆšPPX£FŠ’FKJYDFRYDLWURQKŽWK‹¢QDRE‹QWURQJ&6'/"

• %D•QJVHEDRJŒžPQKŽQJFŒ¤WQDRYDNL‹ XGŽOL‹£XFKRFDFFŒ¤WŠRODJ"

• 1KŽQJFŒ¤WQDRFKRSKHSFK‰¢SQK‰£QJLDWU18//

• &RVŽ•GX£QJFDFUDQJEXŒ¤FFDFPˆ£FŠQKKD\NKŒQJYDQ‹¢XFRWKVŽ•GX£QJ•Š‰XYD

QKXWK‹¢QDR"

• 1KŽQJFŒ¤WQDRVHŠRQJYDLWURODNKRDFK’QKNKRDQJRDLNKRDGX\QK‰¢W"1KŽQJ

GD£QJFKPX£FQDRODF‰žQWKL‹¢WYDF‰žQ•Š‰X

D7D›REDQJGˆOL…›X

j‹ WD£RPŒ¤W ED•QJWURQJ&6'/ ED£Q VŽ• GX£QJ F‰XO‹£QK&5($7(7$%/( FR FX

SKDSQKŽ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'/YDSKD•LWX‰QWKHRFDFTXLWˆFY‹ž

ŠQKGDQK

- colname_i: 7‹QFX•DFŒ¤WWKŽLWURQJED•QJ&DFFŒ¤WWURQJPŒ¡L

Tráön Nguyãn Phong

ED•QJSKD•LFRW‹QNKDFQKDXYDSKD•LWX‰QWKHRFDF

TXL WˆF Y‹ž ŠQK GDQK 0Œ¡L PŒ¤W ED•QJ SKD•L FR ’W

QK‰¢WPŒ¤WFŒ¤W

- col_i_properties: &DFWKXŒ¤FW’QKFX•DFŒ¤WWKŽLTXLŠQKNL‹ XGŽOL‹£X

FX•D FŒ¤W YD FK ŠQK FŒ¤W FR FKR SKHS FK‰¢S QK‰£Q

JLDWU18//KD\NKŒQJ

- constraints_i: &DF UDQJ EXŒ¤F Q‹¢X FR WU‹Q FŒ¤W WKŽ L QKŽ FDF

UDQJEXŒ¤FY‹žNKRDFDFPˆ£FŠQKFDFTXLŠQKY‹ž

NKXŒQGD£QJGŽOL‹£X

- table_constraint: &DFUDQJEXŒ¤FWU‹QED•QJGŽOL‹£X

9ŒGX›&‰XO‹£QKGŽLŠ‰\WKŽ£FKL‹£QKYL‹£FWD£RED•QJ1+$19,(1EDRJŒžPFDFFŒ¤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&+(&.

5DQJEXŒ¤F&+(&.ŠŽ¤FVŽ•GX£QJŠ‹ FKŠQKFDFJLDWUKD\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 UDQJ EXŒ¤F

&+(&.j‹ NKDLEDRPŒ¤WUDQJEXŒ¤F&+(&.ŠŒ¢LYLPŒ¤WFŒ¤WQDRŠRWDVŽ•GX£QJFX

SKDSQKŽVDX

[CONSTRAINT constraint_name]

CHECK (expression)

7URQJŠRH[SUHVVLRQODPŒ¤WEL‹ XWKŽFORJLF TXL ŠQK JLDWU KD\ NKXŒQ GD£QJFX•D GŽ

OL‹£XŠŽ¤FFKRSKHS.KLŠRFKQKŽQJJLDWUGŽOL‹£XQDRODPFKRH[SUHVVLRQQK‰£QJLD

WUŠXQJPLŠŽ¤FFK‰¢SQK‰£Q

9ŒGX› j‹  TXL ŠQK ŠL‹£QWKRD£LFX•D QK‰Q YL‹Q SKD•LFR GD£QJ



FKˆ›QJ KD£Q

F‰XO‹£QK•Y’GX£ŠŽ¤FYL‹¢WQKŽVDX

CREATE TABLE nhanvien

(