Giáo trình thực hành SQL

[r]

7UDQJ

*,lv,7+,j8

64/6WUXFWXUHG4XHU\/DQJXDJHODQJQQJÔFVGXÊQJFKRFDFKÊTXDQ WU F V G OLÊX TXDQ KÊ 1JQ QJ 64/ FKXQ Ô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 SKLQ EDQ 64/ SKLQ EDQ ÔF D UD QP 0ÊFGXFRQKLXQJQQJNKDFQKDXÔFDUDFKRFDFKÊTXDQWU&6'/ TXDQKÊ64/ODQJQQJÔFVGXÊQJUÔQJUDLKLÊQQD\WURQJUÂWQKLXKÊWKÂQJ &6'/WKQJPDÊLQK2UDFOH64/6HUYHU'%0LFURVRIW$FFHVV7KQJTXD64/ QJL V GXÊQJ FR WK GĂ GDQJ QK QJKD ÔF G OLÊX WKDR WDF YL G OLÊX 0ˆ£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 *LDRWUQKEDRJPEÂQFKQJ

&KQJ JLL WKLÊX PÔW VÂ FX OÊQK V GXÊQJ WURQJ YLÊF QK QJKD FDF ÂL WÔQJGOLÊXQKEDQJGOLÊXNKXQJQKQYDFKPXÊ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 QKQJ ÂL WÔQJ &6'/ÔFVGXÊQJQKPWQJKLÊXQQJNKLVGXÊQJ&6'/

7URQJFKQJSKXÊOXÊFFKXQJWLJLLWKLÊXFÂXWUXFYDGOLÊXFXDFDFEDQJV GXÊQJWURQJFDFYGXÊFKQJEDÊQWLÊQWUDFXYDÂLFKLÂXYLFDFYGXÊDQX 1JRDLUDWURQJFKQJQD\FRQFRFDFKDPWKQJVGXÊQJWURQJ64/6HUYHUFDF EDÊQWKDPNKDRWURQJWKÊFKDQK

C

Chhổổồồnngg11::NNGGNNNNGGặặẻẻậậNNHHNNGGHHẫẫAA DDặặẻẻLLIIUU

1JQQJQKQJKDGOLÊXEDRJPFDFFXOÊQKFKRSKHSQJLVGXÊQJQK QJKD&6'/YDFDFÂLWÔQJWURQJ&6'/QKFDFEDQJFDFNKXQJQKQFKPXÊF 7DREDQJGOLX

'OLÊXEQWURQJPÔW&6'/ÔFWFKFOXWUWURQJFDFEDQJ%QWURQJFDF EDQJGOLÊXÔFWFKFGLGDÊQJFDFGRQJYDFDFFÔW0ĂLPÔW GRQJ ELX GLĂQ PÔWEDQJKLGX\QKÂWYDPĂLPÔWFÔWELXGLĂQFKRPÔWWUQJ

&DFWKXFWQKOLQTXDQQEDQJ

.KL WDÊR YD ODP YLÊF YL FD—F ED•QJ GŽ– OL‹£X WD F‰žQ SKD•L Š‹  \— Š‹¢Q FDF WKXÔF WQK NKDFWUQEDQJQKNLX G OLÊX FDF UDQJ EXÔF FDF NKRD FDF TXL WF &DF WKXÔF WQKQD\ÔFVGXÊQJQKPWDÊRUDFDFUDQJEXÔFWRDQYHÊQWUQFDFFÔWWUQJWUQ EDQJFXQJQKWDÊRUDFDFWRDQYHÊQWKDPFKLÂXJLDFDFEDQJGOLÊXWURQJ&6'/ D.LXGOLX

0ĂL PÔW FÔW WUQJ FXD PÔW EDQJ X SKDL WKXÔF YDR PÔW NLX G OLÊX QKÂW QK D ÔF QK QJKD W WUF 0ĂL PÔW NLX G OLÊX TXL QK FDF JLD WU G OLÊX ÔFFKRSKHSÂLYLFÔWR&DFKÊTXDQWU&6'/WKQJFXQJFÂSFDFNLXGOLÊX FKXQQJRDLUDFRQFRWKFKRSKHSQJLVGXÊQJQKQJKDFDFNLXGOLÊXNKDF GÊDWUQFDFNLXGOLÊXDFR

'L\ODPÔWVÂNLXGOLÊXWKQJÔFVGXÊQJWURQJJLDRWDF64/ Binary

Bit Char Datetime Decimal Float Image

Int Money Nchar Ntext Nvarchar Real

Smalldatetime

Smallint Smallmoney Text

Tinyint Varbinary Varchar

E&DFUDQJEXF&21675$,176

7UQFDFEDQJGOLÊXFDFUDQJEXÔFÔFVGXÊQJQKPFDFPXÊFFKVDX ã 4XLQKFDFJLDWUGOLÊXKD\NKXQGDÊQJGOLÊXÔFFKRSKHSFKÂSQKÊQWUQ

FDFFÔWFXDEDQJUDQJEXÔF&+(&

7UDQJ

ã 7DÊRQQWQKWRDQYHÊQWKÊFWKWURQJPÔWEDQJGOLÊXYDWRDQYHÊQWKDPFKLÂXJLD FDF EDQJ G OLÊX WURQJ &6'/ UDQJ EXÔF 35,0$5< (< 81,48( YD )25(,*1.(<

&KXQJWDVHWPKLXFKLWLÂWKQYFDFUDQJEXÔFQD\SKQWUQKED\YFXOÊQK &5($7(7$%/(

7DREDQJEQJWUX\YQ64/

7DÊR FDF EDQJ OD PÔW NKX TXDQ WURQJ WURÔQJ WURQJ TXD WUQK WKLÂW NÂ YD FDL GÊW FDF&6'/%QWURQJFDF&6'/PĂLPÔWEDQJWKQJÔFVGXÊQJQKPELXGLĂQ WKQJWKLYFDFÂLWÔQJWURQJWKÂJLLWKÊFYDKRÊFELXGLĂQPÂLTXDQKÊJLDFDF ÂLWÔQJRjFRWKWFKFWÂWPÔWEDQJGOLÊXEDÊQWQKÂWFQ SKDL [DF QK ÔFFDF\XFXVDX

ã %DQJÔFVGXÊQJQKPPXÊFFKJYDFRYDLWURQKWKÂQDREQWURQJ&6'/" ã %DQJVHEDRJPQKQJFÔWQDRYDNLXGOLÊXFKRFDFFÔWRODJ"

ã 1KQJFÔWQDRFKRSKHSFKÂSQKÊQJLDWU18//

ã &RVGXÊQJFDFUDQJEXÔFFDFPÊFQKKD\NKQJYDQÂXFRWKVGXÊQJXYD QKXWKÂQDR"

ã 1KQJFÔWQDRVHRQJYDLWURODNKRDFKQKNKRDQJRDLNKRDGX\QKÂW"1KQJ GDÊQJFKPXÊFQDRODFQWKLÂWYDFQX

D7DREDQJGOLX

j WDÊR PÔW EDQJ WURQJ &6'/ EDÊQ VŽ• GX£QJ F‰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

EDQJSKDLFRWQNKDFQKDXYDSKDLWXQWKHRFDF TXL WF Y QK GDQK 0ĂL PÔW EDQJ SKDL FR W QKÂWPÔWFÔW

- col_i_properties: &DFWKXÔFWQKFXDFÔWWKLTXLQKNLXGOLÊX FXD FÔW YD FK QK FÔW FR FKR SKHS FKÂS QKÊQ JLDWU18//KD\NKQJ

- constraints_i: &DF UDQJ EXÔF QÂX FR WUQ FÔW WK L QK FDF UDQJEXÔFYNKRDFDFPÊFQKFDFTXLQKY NKXQGDÊQJGOLÊX

- table_constraint: &DFUDQJEXÔFWUQEDQJGOLÊX

9GX&XOÊQKGL\WKÊFKLÊQKYLÊFWDÊREDQJ1+$19,(1EDRJPFDFFÔ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

D D D

D5DQJEXF&+(&.

5DQJEXÔF&+(&.ÔFVGXÊQJFKQKFDFJLDWUKD\NKXQGDÊQJGOLÊX FR WK ÔF FKÂS QKÊQ ÂL YL PÔW FÔW 7UQ PÔW FÔW FR WK FR QKLX UDQJ EXÔF &+(&.jNKDLEDRPÔWUDQJEXÔF&+(&.ÂLYLPÔWFÔWQDRRWDVGXÊQJFX SKDSQKVDX

[CONSTRAINT constraint_name] CHECK (expression)

7URQJ RH[SUHVVLRQ OD PÔW EL‹ X WKŽ—F ORJLF TXL Š“QK JLD— WU“ KD\ NKXŒQ GD£QJ FXD G OLÊXÔFFKRSKHS.KLRFKQKQJJLDWUGOLÊXQDRODPFKRH[SUHVVLRQQKÊQJLD WUXQJPLÔFFKÂSQKÊQ

9 GX j TXL Š“QK ŠL‹£Q WKRD£L FX•D QK‰Q YL‹Q SKD•L FR— GD£QJ FKˆ›QJ KDÊQ FXOÊQKYGXÊÔFYLÂWQKVDX

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]') )

D DD

D5DQJEXF'()$8/7

5DQJEXÔF'()$8/7ÔFVGXÊQJTXLQKJLDWUPÊFQKFKRPÔWFÔW *LDWUQD\VHWÊÔQJÔFJDQFKRFÔWQD\NKLQJLVGXÊQJEVXQJPÔWEDQJKL PD NKQJ FK QK JLD WU FKR FÔW 7UQ PĂL FÔW FK FR WK FR QKLX QKÂW PÔW UDQJ EXÔF'()$8/7WFODFKFRWKFRWÂLDPÔWJLDWUPÊFQK

j NKDL EDR PÔW JLD WU PÊF QK FKR PÔW FÔW WD FK QK PÔW UDQJ EXÔF '()$8/7FKRFÔWEQJFDFKVGXÊQJFXSKDSVDX

[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 ',$&+,WURQJEDQJ1+$19,(1YGXÊ

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

)

D DD

D5DŽQJEX†œF35,0$5<.(<

D+DŽP/()7

&X—SKD—S/()7VWULQJQ

&KŽ—FQˆQJ+D”PWU’FKUDWŽ”FKXŒ¡LVWULQJQN\—WŽ£W’QKWŽ”E‹QWUD—L E+DŽP5,*+7

&X‘SKD‘S/()7VWULQJQ

&Kˆ‘FQ‚QJ+D”PWU’FKUDWŽ”FKXŒ¡LVWULQJQN\—WŽ£W’QKWŽ”E‹QSKD•L F+DŽP68%675,1*

&X‘SKD‘S68%675,1*VWULQJPQ

&Kˆ‘FQ‚QJ+D”PWU’FKUDWŽ”FKXŒ¡LVWULQJQN\—WŽ£W’QKWŽ”N\—WŽ£WKŽ—P G+DŽP/75,0575,0

&X‘SKD‘S/75,0VWULQJ575,0VWULQJ

&Kˆ‘FQ‚QJ+D”PFˆWER•FD—FNKRD•QJWUˆQJWKŽ”DE‹QWUD—LE‹QSKD•LFKXŒ¡LVWULQJ H+DŽP/(1

&X‘SKD‘S/(1VWULQJ

M

MUUÛCÛCLLUUÛCÛC

&+ml1*1*k11*muir1+1*+p$'mu/,j8

7$2%$u1*'nv/,k8

&DFWKXÔFWQKOLQTXDQÂQEDQJ 7DÊREDQJEQJWUX\YÂQ64/ 6DLEDQJ &+p08&,1'(;

.+81*1+o19,(:

&+ml1*1*k11*mu7+$27$v&'mu/,j€8

758<;8i‚7'nv/,kƒ8

1.1 Xác định bảng mệnh đề FROM 12

1.2 Mệnh đề WHERE 13

1.3 Danh sách chọn câu lệnh SELECT 14

1.4 Tính toán giá trị câu lệnh SELECT 16

1.5 Từ khoá DISTINCT 16

1.6 Tạo bảng câu lệnh SELECT INTO 17

1.7 Sắp xếp kết truy vấn ORDER BY 17

1.8 Phép hợp toán tö UNION 18

1.9 PhÐp nèi 20

1.10 Tạo dòng thống kê liệu với COMPUTE BY 24

1.11 Thống kê liệu với GROUP BY vµ HAVING 26

1.12 Truy vấn (subquery) 27

%l€681*&iƒ31+iƒ79$t;2$w'nv/,kƒ8 %Œ VXQJGŽ–OL‹£X &‰£SQK‰£WGŽ–OL‹£X ;RD—GŽ–OL‹£X &+ml1*1*k11*mui,j|8.+,j}1 &i8/k1+*5$17 &i8/k1+5(92.( &+ml1*7+8t78&/m875mu9$s75,**(5 ,6nu'81*7+8u78&/n875nv6725('352&('85( I.1 Tạo thủ tục lưu trữ 35

I.2 Thông tin trả từ thủ tục lưu trữ 36

I.3 Các qui tắc sư dơng cho sp 38

I.4 Xác định tên bờn cỏc th tc 38

I.5 Đổi tên c¸c thđ tơc: 39

I.6 Xo¸ thđ tơc: 39

II Sệ DễNG CáC TRIGGER II.1 Tạo trigger 40

II.2 Các giá trị null ngầm định hiển (implicit and explicit null values) 40

II.3 Việc đổi tên trigger 41

II.4 HiĨn thÞ thông tin trigger 41

II.5 Xoá trigger 42