Tài liệu 3.giao_trinh_sql_va_pl_sql

www.nhipsongcongnghe.net C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - Fpt Hµ Néi, th¸ng 11 n¨m 2002. §µo t¹o Oracle c¬ b¶n Gi¸o tr×nh SQL vµ PL/SQL §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 2 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL Môc lôc Môc lôc .................................................................................................................................................3 1 Giíi thiÖu.......................................................................................................................................6 1.1 1.2 Môc tiªu kho¸ häc ..............................................................................................................................6 Khëi ®éng vµ tho¸t khái Oracle.........................................................................................................6 1.2.1 1.2.2 1.3 Giíi thiÖu ng«n ng÷ SQL ...................................................................................................................7 1.3.1 1.3.2 1.4 1.5 1.6 1.7 LÖnh truy vÊn c¬ b¶n........................................................................................................................10 C¸c thµnh phÇn kh¸c cña mÖnh ®Ò SELECT..................................................................................10 Gi¸ trÞ Null..........................................................................................................................................11 Läc d÷ liÖu tõ c¸c row cã cïng gi¸ trÞ.............................................................................................11 HiÓn thÞ cÊu tróc b¶ng ......................................................................................................................12 C¸c lÖnh cña c«ng cô SQL*Plus......................................................................................................12 2.6.1 2.6.2 2.6.3 2.7 C¸c hµm sè........................................................................................................................................20 C¸c hµm ký tù ...................................................................................................................................22 C¸c hµm ngµy ...................................................................................................................................26 C¸c hµm chuyÓn ®æi kiÓu.................................................................................................................28 Bµi tËp................................................................................................................................................29 Bµi tËp................................................................................................................................................32 C¸c hµm nhãm ¸p dông cho lín h¬n hoÆc b»ng 1 dßng d÷ liÖu ........................32 6.1 6.2 6.3 7 MÖnh ®Ò ORDER BY .........................................................................................................................16 MÖnh ®Ò WHERE...............................................................................................................................16 C¸c to¸n tö ........................................................................................................................................17 Bµi tËp................................................................................................................................................19 BiÕn runtime..............................................................................................................................31 5.1 6 Bµi tËp................................................................................................................................................14 C¸c hµm ¸p dông cho 1 dßng d÷ liÖu..............................................................................20 4.1 4.2 4.3 4.4 4.5 5 C¸c lÖnh so¹n th¶o ...............................................................................................12 C¸c lÖnh vÒ file......................................................................................................13 C¸c lÖnh vÒ column...............................................................................................13 Truy vÊn d÷ liÖu cã ®iÒu kiÖn ............................................................................................16 3.1 3.2 3.3 3.4 4 M« h×nh quan hÖ d÷ liÖu..........................................................................................9 M« t¶ d÷ liÖu............................................................................................................9 LÖnh truy vÊn c¬ b¶n ...........................................................................................................10 2.1 2.2 2.3 2.4 2.5 2.6 3 LÞch sö ph¸t triÓn cña ng«n ng÷ SQL .....................................................................7 ChuÈn SQL .............................................................................................................7 C¸c kh¸i niÖm trong CSDL.................................................................................................................7 Danh s¸ch rót gän c¸c ®èi t−îng CSDL ...........................................................................................8 C¸c lÖnh SQL ......................................................................................................................................8 Giíi thiÖu vÒ vÝ dô thùc hµnh.............................................................................................................9 1.7.1 1.7.2 2 T¹i Server (Window NT) .........................................................................................6 T¹i Client (Window 9x) ............................................................................................6 C¸c hµm t¸c ®éng trªn nhãm ..........................................................................................................32 MÖnh ®Ò GROUP BY .........................................................................................................................34 Bµi tËp................................................................................................................................................35 HiÓn thÞ néi dung d÷ liÖu tõ nhiÒu b¶ng ........................................................................35 7.1 Mèi liªn kÕt t−¬ng ®−¬ng .................................................................................................................35 §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 3 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 7.2 7.3 7.4 7.5 7.6 8 C¸c lÖnh truy vÊn lång nhau...........................................................................................39 8.1 8.2 9 Mèi liªn kÕt kh«ng t−¬ng ®−¬ng......................................................................................................35 Mèi liªn kÕt céng...............................................................................................................................36 Liªn kÕt cña b¶ng víi chÝnh nã .......................................................................................................36 C¸c to¸n tö tËp hîp ..........................................................................................................................36 Bµi tËp................................................................................................................................................37 C©u lÖnh SELECT lång nhau. ..........................................................................................................39 Bµi tËp................................................................................................................................................40 CÊu tróc h×nh c©y...................................................................................................................40 9.1 9.2 9.3 CÊu tróc h×nh c©y trong 1 table .......................................................................................................40 Kü thuËt thùc hiÖn ............................................................................................................................41 Bµi tËp................................................................................................................................................42 10 Tæng kÕt vÒ lÖnh select .....................................................................................................44 11 T¹o table ...................................................................................................................................44 11.1 11.2 11.3 11.4 LÖnh t¹o b¶ng ...................................................................................................................................44 C¸c quy t¾c ®Æt tªn object ...............................................................................................................46 C¸c quy t¾c khi tham chiÕu ®Õn object...........................................................................................47 KiÓu d÷ liÖu vµ ®iÒu kiÖn ..................................................................................................................47 11.4.1 11.4.2 11.4.3 11.4.4 11.4.5 11.4.6 11.4.7 11.4.8 11.4.9 11.4.10 11.4.11 CHAR ....................................................................................................................47 VARCHAR2...........................................................................................................48 VARCHAR.............................................................................................................48 NUMBER...............................................................................................................48 FLOAT...................................................................................................................48 LONG ....................................................................................................................49 DATE.....................................................................................................................49 RAW vµ LONG RAW ............................................................................................50 ROWID ..................................................................................................................50 MLSLABEL............................................................................................................50 ChuyÓn ®æi kiÓu ....................................................................................................50 11.5 Constraint..........................................................................................................................................51 11.6 Bµi tËp................................................................................................................................................52 12 c¸c lÖnh DDL kh¸c vµ d÷ liÖu trong tõ ®iÓn d÷ liÖu...............................................52 12.1 ChØnh söa cÊu tróc table ..................................................................................................................52 12.2 C¸c lÖnh DDL kh¸c ...........................................................................................................................53 12.2.1 12.2.2 12.2.3 12.2.4 Xãa table ...............................................................................................................53 Gi¶i thÝch b¶ng ......................................................................................................53 Thay ®æi tªn object................................................................................................53 Xãa d÷ liÖu cña table.............................................................................................53 12.3 D÷ liÖu trong tõ ®iÓn d÷ liÖu ............................................................................................................54 12.4 Bµi tËp................................................................................................................................................54 13 C¸c lÖnh Thao t¸c d÷ liÖu kh¸c ......................................................................................55 13.1 13.2 13.3 13.4 13.5 13.6 14 ChÌn mét row vµo table ...................................................................................................................55 ChØnh söa d÷ liÖu..............................................................................................................................55 Xãa dßng ...........................................................................................................................................55 Lçi rµng buéc d÷ liÖu .......................................................................................................................56 LÖnh ®iÒu khiÓn giao dÞch................................................................................................................56 Bµi tËp................................................................................................................................................57 Sequence vµ index..................................................................................................................57 §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 4 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 14.1 Sequence...........................................................................................................................................57 14.1.1 14.1.2 T¹o Sequence.......................................................................................................57 Xo¸ vµ söa sequence ...........................................................................................58 14.2 Index ..................................................................................................................................................58 14.3 Bµi tËp................................................................................................................................................59 15 T¹o view ......................................................................................................................................59 15.1 View....................................................................................................................................................59 15.2 Bµi tËp................................................................................................................................................61 16 QuyÒn vµ b¶o mËt ...................................................................................................................61 16.1 QuyÒn - PRIVILEGE ..........................................................................................................................61 16.2 ROLE..................................................................................................................................................62 16.3 Synonym............................................................................................................................................63 17 tæng quan vÒ pl/sql vµ procedure builder...............................................................63 17.1 Có ph¸p lÖnh PL/SQL .......................................................................................................................63 17.2 PL/SQL block ....................................................................................................................................63 17.3 Giíi thiÖu Procedure builder............................................................................................................64 18 có ph¸p lËp tr×nh ...................................................................................................................66 18.1 18.2 18.3 18.4 18.5 19 cursor ........................................................................................................................................68 19.1 19.2 19.3 19.4 19.5 20 IF ........................................................................................................................................................66 LOOP vµ EXIT....................................................................................................................................66 FOR ....................................................................................................................................................67 WHILE ................................................................................................................................................67 GOTO .................................................................................................................................................67 §Þnh nghÜa .........................................................................................................................................68 KiÓu d÷ liÖu Table vµ Record...........................................................................................................69 Sao kiÓu d÷ liÖu ................................................................................................................................70 C©u lÖnh SELECT... INTO... trong PL/SQL......................................................................................70 Bµi tËp................................................................................................................................................70 procedure vµ funtion..........................................................................................................71 20.1 Procedure ..........................................................................................................................................71 20.2 Function.............................................................................................................................................72 20.3 Bµi tËp................................................................................................................................................73 21 pakage.........................................................................................................................................73 21.1 Package .............................................................................................................................................73 22 database trigger ..................................................................................................................74 22.1 Database Trigger...............................................................................................................................74 22.2 Bµi tËp................................................................................................................................................75 23 error handing .........................................................................................................................76 23.1 Bµi tËp................................................................................................................................................78 §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 5 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 1 Giíi thiÖu 1.1 Môc tiªu kho¸ häc KÕt thóc kho¸ häc häc viªn ph¶i n¾m ®−îc • HiÓu ®−îc ph−¬ng ph¸p, c¸c thµnh phÇn, thuËt ng÷ vµ thao t¸c trong CSDL quan hÖ • T¹o ®−îc c¸c cÊu tróc d÷ liÖu nh− table, view dïng SQL • Ghi, ®äc, vµ cËp nhËt d÷ liÖu trong CSDL • X©y dùng c¸c PL/SQL block dïng Procedure Builder 1.2 Khëi ®éng vµ tho¸t khái Oracle 1.2.1 T¹i Server (Window NT) SQLDBA cung cÊp c¸c dÞch vô qu¶n trÞ hÖ thèng, nh−: t¹o lËp CSDL, më - ®ãng CSDL, t¹o vµ qu¶n lý c¸c USER ... C¸c b−íc ®Ó khëi ®éng t¹i Server nh− sau: • Khëi ®éng m¸y chñ • BËt dÞch vô OracleServiceXXX (trong ®ã XXX lµ tªn cña CSDL) b»ng c¸ch nhÊn vµo Start -> Program > Service -> OracleServiceXXX -> NhÊn chuét ph¶i -> NhÊn Start. Chó ý chØ bËt dÞch vô nµy khi ng−êi cµi ®Æt kh«ng ®Ó chÕ ®é tù ®éng hay khi dÞch vô nµy ch−a ®−îc bËt. • BËt dÞch vô OracleXXXTNSLÝstener (trong ®ã XXX lµ tªn cña Database Home) b»ng c¸ch nhÊn vµo Start -> Program -> Service -> OracleXXXTNSLÝstener -> NhÊn chuét ph¶i -> NhÊn Start. Chó ý chØ bËt dÞch vô nµy khi ng−êi cµi ®Æt kh«ng ®Ó chÕ ®é tù ®éng hay khi dÞch vô nµy ch−a ®−îc bËt. • Khi bËt xong CSDL ®· s½n sµng ®Ó lµm viÖc §Ó ®ãng CSDL cÇn lµm theo c¸c b−íc ng−îc l¹i: • T¾t dÞch vô OracleXXXTNSLÝstener (trong ®ã XXX lµ tªn cña Database Home) b»ng c¸ch nhÊn vµo Start -> Program -> Service -> OracleXXXTNSLÝstener -> NhÊn chuét ph¶i -> NhÊn Stop. • T¾t dÞch vô OracleServiceXXX (trong ®ã XXX lµ tªn cña CSDL) b»ng c¸ch nhÊn vµo Start -> Program > Service -> OracleServiceXXX -> NhÊn chuét ph¶i -> NhÊn Stop. • Shutdown m¸y chñ. 1.2.2 T¹i Client (Window 9x) C¸c øng dông cña oracle ch¹y trong m«i tr−êng Windows víi giao diÖn graphic, c¸c øng dông th−êng dïng cã SQL*Plus, Oracle Form, Oracle Report, Oracle Designer ... ViÖc ch¹y c¸c øng dông nµy hoµn toµn gièng nh− viÖc ch¹y c¸c øng dông th«ng th−êng trong m«i tr−êng windows. §Ó lµm viÖc víi c¸c øng dông truy cËp CSDL Oracle, ng−êi sö dông (NSD) ph¶i connect vµo CSDL. Cã hai c¸ch ®Ó connect. Connect NSD/password, vÝ dô NSD tªn Scott cã password lµ tiger th× Connect Scott/tiger Ph¸t lÖnh connect víi tªn NSD, khi ®ã Oracle sÏ hái password Connect Scott Enter password: ***** §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 6 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL NSD cã thÓ lµm viÖc trong ph¹m vi cho phÐp cña m×nh mµ Oracle gäi lµ "khung c¶nh" (Schema) cña NSD. Mçi khung c¶nh chøa nhiÒu ®èi t−îng c¸c lo¹i, NSD chØ cã thÓ t¸c ®éng lªn c¸c ®èi t−îng trong khung c¶nh cña m×nh. Trong c¸c øng dông ®Òu cã chøc n¨ng tho¸t vµ tù ®éng disconnect. §Ó thùc hµnh phÇn SQL vµ PL/SQL gäi øng dông SQL* Plus. 1.3 Giíi thiÖu ng«n ng÷ SQL 1.3.1 LÞch sö ph¸t triÓn cña ng«n ng÷ SQL M« h×nh c¬ së d÷ liÖu (CSDL) quan hÖ do E.F Codd ®−a ra vµo ®Çu thËp kû 70, tõ ®ã ®Õn nay nã liªn tôc ph¸t triÓn trë thµnh m« h×nh CSDL phæ biÕn bËc nhÊt (RDBMS). M« h×nh quan hÖ gåm c¸c thµnh phÇn sau: • TËp hîp c¸c ®èi t−îng vµ/hoÆc c¸c mèi quan hÖ • TËp hîp c¸c xö lý t¸c ®éng tíi c¸c quan hÖ • Rµng buéc d÷ liÖu ®¶m b¶o tÝnh chÝnh x¸c vµ nhÊt qu¸n. SQL (Structured Query Language, ®äc lµ "sequel") lµ tËp lÖnh truy xuÊt CSDL quan hÖ. Ng«n ng÷ SQL ®−îc IBM sö dông ®Çu tiªn trong hÖ qu¶n trÞ CSDL System R vµo gi÷a nh÷ng n¨m 70, hÖ ng«n ng÷ SQL ®Çu tiªn (SEQUEL2) ®−îc IBM c«ng bè vµo th¸ng 11 n¨m 1976. N¨m 1979, tËp ®oµn ORACLE giíi thiÖu th−¬ng phÈm ®Çu tiªn cña SQL, SQL còng ®−îc cµi ®Æt trong c¸c hÖ qu¶n trÞ CSDL nh− DB2 cña IBM vµ SQL/DS. Ngµy nay, SQL ®−îc sö dông réng r·i vµ ®uîc xem lµ ng«n ng÷ chuÈn ®Ó truy cËp CSDL quan hÖ. 1.3.2 ChuÈn SQL N¨m 1989, viÖn tiªu chuÈn quèc gia Hoa kú (ANSI) c«ng nhËn SQL lµ ng«n ng÷ chuÈn ®Ó truy cËp CSDL quan hÖ trong v¨n b¶n ANSI SQL89. N¨m 1989, tæ chøc tiªu chuÈn quèc tÕ (ISO) c«ng nhËn SQL ng«n ng÷ chuÈn ®Ó truy cËp CSDL quan hÖ trong v¨n b¶n ISO 9075-1989. TÊt c¶ c¸c hÖ qu¶n trÞ CSDL lín trªn thÕ giíi cho phÐp truy cËp b»ng SQL vµ hÇu hÕt theo chuÈn ANSI. 1.4 C¸c kh¸i niÖm trong CSDL Table lµ cÊu tróc l−u tr÷ c¬ b¶n nhÊt trong CSDL quan hÖ (RDBMS), nã bao gåm 1 hoÆc nhiÒu column vµ 0 hoÆc nhiÒu row. Row lµ tæ hîp nh÷ng gi¸ trÞ cña Column trong b¶ng. Mét row cßn cã thÓ ®−îc gäi lµ 1 record. Column hiÓn thÞ mét lo¹i d÷ liÖu trong b¶ng, vÝ dô tªn phßng ban trong b¶ng phßng ban. Ng−êi ta thÓ hiÖn nã th«ng qua tªn column vµ gi÷ sè liÖu d−íi c¸c kiÓu vµ kÝch cì nhÊt ®Þnh. Field lµ giao cña column vµ row. Field chÝnh lµ n¬i chøa d÷ liÖu. NÕu kh«ng cã d÷ liÖu trong field ng−êi ta nãi field cã gia trÞ lµ null. Primary Key lµ mét column hoÆc mét tËp c¸c column x¸c ®Þnh tÝnh duy nhÊt cña c¸c row ë trong b¶ng. VÝ dô m· phßng ban. Primary Key nhÊt thiÕt ph¶i cã sè liÖu. Foreign Key lµ mét column hoÆc mét tËp c¸c column tham chiÕu tíi chÝnh b¶ng ®ã hoÆc mét b¶ng kh¸c. Foreign Key x¸c ®Þnh mèi quan hÖ gi÷a c¸c b¶ng. Constraint lµ c¸c rµng buéc d÷ liÖu, vÝ dô Foreign Key, Primary Key... §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 7 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL VÝ dô: EMP EMPNO DEPT ENAME DEPTNO EMP 7369 DEPTNO DNAME DEPT 20 10 ACCOUNTING 7499 ALLEN 30 20 RESEARCH 7521 WARD 30 7566 JONES 20 30 SALES 7654 MARTIN 30 40 OPERATIONS 7698 BLAKE 30 7782 Row SMITH CLARK 10 Foreign key Primary key Column 1.5 Danh s¸ch rót gän c¸c ®èi t−îng CSDL Table lµ cÊu tróc l−u tr÷ c¬ b¶n nhÊt trong CSDL quan hÖ (RDBMS), gåm row vµ column View lµ cÊu tróc logic hiÓn thÞ d÷ liÖu tõ 1 hoÆc nhiÒu b¶ng Sequence kÕt sinh gi¸ trÞ cho c¸c primary key Index t¨ng tÝnh thùc thi cña c¸u truy vÊn Synonym tªn t−¬ng ®−¬ng cña ®èi t−îng Program unit gåm Procedure, function, package... 1.6 C¸c lÖnh SQL LÖnh SELECT INSERT UPDATE DELETE CREATE ALTER DROP RENAME TRUNCATE COMMIT ROLLBACK SAVE POINT GRANT REVOKE M« t¶ Lµ lÖnh th«ng dông nhÊt, dïng ®Ó lÊy, xem d÷ liÖu trong CSDL. Lµ 3 lÖnh dïng ®Ó nhËp thªm nh÷ng row míi, thay ®æi néi dung d÷ liÖu trªn c¸c row hay xo¸ c¸c row trong table. Nh÷ng lÖnh nµy ®−îc gäi lµ c¸c lÖnh thao t¸c d÷ liÖu DML (Data Manipulation Language) Lµ 3 lÖnh dïng ®Ó thiÕt lËp, thay ®æi hay xo¸ bá cÊu tróc d÷ liÖu nh− lµ table, view, index. Nh÷ng lÖnh nµy ®−îc gäi lµ c¸c lÖnh ®Þnh nghÜa d÷ liÖu DDL (Data Definition Language) Qu¶n lý viÖc thay ®æi d÷ liÖu b»ng c¸c lÖnh DML. ViÖc thay ®æi d÷ liÖu cã thÓ ®−îc nhãm l¹i thµnh c¸c transaction. 2 lÖnh nµy dïng ®Ó g¸n hoÆc huû c¸c quyÒn truy nhËp vµo CSDL Oracle vµ c¸c cÊu tróc bªn trong nã. Nh÷ng lÖnh nµy ®−îc gäi lµ c¸c lÖnh ®iÒu khiÓn d÷ liÖu DCL (Data §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 8 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL Control Language) 1.7 1.7.1 Giíi thiÖu vÒ vÝ dô thùc hµnh M« h×nh quan hÖ d÷ liÖu DEPT EMP SALGRADE DUMMY 1.7.2 BONUS M« t¶ d÷ liÖu Tªn KiÓu Kho¸ Gi¶i thÝch DEPTNO NUMBER(2) NOT NULL PK M· phßng ban DNAME CHAR(14) Tªn phßng ban LOC CHAR(13) §Þa chØ DEPT SALGRADE GRADE NUMBER LOSAL NUMBER PK Møc l¬ng Gi¸ trÞ thÊp HISAL NUMBER Gi¸ trÞ cao EMP EMPNO NUMBER(4) NOT NULL, ENAME CHAR(10), JOB CHAR(9), MGR NUMBER(4) HIREDATE DATE Ngµy gia nhËp c«ng ty SAL NUMBER(7,2) L−¬ng COMM NUMBER(2) NOT NULL, §µo t¹o c¬ b¶n: SQL vµ PL/SQL M· nh©n viªn Tªn nh©n viªn NghÒ nghiÖp FK (EMP.EMPNO) NUMBER(7,2) DEPTNO PK M· ng−êi qu¶n lý Th−ëng FK (DEPT.DEPTNO) M· phßng ban Trang 9 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 2 LÖnh truy vÊn c¬ b¶n 2.1 LÖnh truy vÊn c¬ b¶n SELECT [DISTINCT ] {*, column [alias],...} FROM table; • SELECT 1 column. tr¶ lêi c©u hái lÊy d÷ liÖu nµo? (column, biÓu thøc...), trong mÖnh ®Ò SELECT cÇn cã Ýt nhÊt • FROM tr¶ lêi c©u hái lÊy d÷ liÖu ë ®Çu? (table, view...) • DISTINCT chØ ®Þnh hiÓn thÞ 1 lÇn c¸c d÷ liÖu trïng nhau. • * thay cho viÖc chØ tªn tÊt c¶ c¸c column • alias ®−a ra nh·n cña column hiÓn thÞ d÷ liÖu. Vd: SELECT * FROM emp; SELECT empno, ename, deptno, mgr FROM emp; SELECT DISTINCT job nghenghiep FROM emp; 2.2 C¸c thµnh phÇn kh¸c cña mÖnh ®Ò SELECT Trong mÖnh ®Ò SELECT cßn cã thÓ ®−a vµo c¸c thµnh phÇn kh¸c: • BiÓu thøc to¸n häc • Column alias • C¸c column ®−îc ghÐp chuçi • Literal BiÓu thøc to¸n häc Trong mÖnh ®Ò SELECT biÓu thøc to¸n häc cã thÓ c¸c gi¸ trÞ (column hoÆc hµng sè), c¸c to¸n tö, c¸c hµm. C¸c to¸n tö ®−îc dïng lµ (+), (-), (*), (/). §é −u tiªn cña c¸c to¸n tö gièng trong phÇn sè häc. Vd: SELECT ename, sal *12, comm FROM emp; SELECT ename, (sal+250)*12 FROM emp; Column alias Trong mÖnh ®Ò SELECT, column alias lµ phÇn nh·n hiÓn thÞ cña column khi lÊy sè liÖu ra. Trong column alias kh«ng ®−îc cã dÊu c¸ch vµ viÕt c¸ch sau tªn column mét dÊu c¸ch. Column alias ®−îc chÊp nhËn cã dÊu c¸ch khi ®−îc ®Æt trong dÊu nh¸y kÐp (“ “). Vd: (ANUAL chÝnh lµ column alias) SELECT ename, SAL*12 ANUAL, comm FROM emp; C¸c column ®−îc ghÐp chuçi To¸n tö ghÐp chuçi (||) cho phÐp c¸c column ®−îc nèi víi nhau thµnh d¹ng chuçi. Cã thÓ cã nhiÒu to¸n tö ghÐp chuçi trong cïng mét column alias. Vd: SELECT empno||ename EMPLOYEE FROM emp; SELECT ename || ' co luong la ' || (sal+250)*12 as "mieu ta" FROM emp; Chuçi ®Æt trong nh¸y ®¬n, bÝ danh ®Æt trong nh¸y kÐp §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 10 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL Literal Trong mÖnh ®Ò SELECT, literal lµ bÊt kú ký tù nµo, biÓu thøc, hay sè nµp mµ kh«ng ph¶i lµ column hoÆc column alias. Vd: SELECT empno||ename EMPLOYEE, ‘WORK IN DEPARTMENT’, deptno FROM emp; 2.3 Gi¸ trÞ Null Cét cã gi¸ trÞ rçng (NULL) lµ cét ch−a ®−îc g¸n gi¸ trÞ, nãi c¸ch kh¸c nã ch−a ®−îc khëi t¹o gi¸ trÞ. C¸c cét víi bÊt cø kiÓu d÷ liÖu nµo còng cã thÓ cã trÞ NULL, trõ khi ®−îc nã lµ khãa hay cã rµng buéc toµn vÑn NOT NULL. Trong biÓu thøc cã bÊt kú gi¸ trÞ NULL nµo kÕt qu¶ còng lµ NULL. Vd: SELECT ename, sal*12 + comm ANUAL_SAL FROM emp; NULL trong c¸c hµm cña SQL - Trong c¸c hµm lµm viÖc víi tõng cét hay hµm v« h−íng (scalar function) C¸c hµm lo¹i nµy tr¶ vÒ trÞ null khi cã tham sè null, trõ hµm NVL vµ TRANSLATE cã thÓ tr¶ vÒ gi¸ trÞ thùc. Có ph¸p cña hµm NVL NVL (DATECOLUMN,’01-01-2001’) NVL(NUMBERCOLUMN, 9) NVL(CHARCOLUMN,’STRING’) VÝ dô: NVL(comm,0) tr¶ vÒ trÞ 0 khi comm lµ null SELECT ename, sal*12 + NVL(comm,0) ANUAL_SAL FROM emp; - Trong c¸c hµm lµm viÖc víi nhãm c¸c cét (group function) HÇu hÕt c¸c hµm lµm viÖc trªn nhãm bá qua trÞ null, vÝ dô nh− khi sö dông hµm AVG ®Ó tÝnh trung b×nh cho mét cét cã c¸c gi¸ trÞ 1000, null, null, null, 2000 khi ®ã trung b×nh ®−îc tÝnh lµ (1000+2000)/2=1500, nh− vËy trÞ null bÞ bá qua chø kh«ng ph¶i xem lµ trÞ 0. NULL trong c¸c biÓu thøc so s¸nh, ®iÒu kiÖn §Ó kiÓm tra cã ph¶i null hay kh«ng dïng c¸c to¸n tö IS NULL hoÆc IS NOT NULL. NÕu trong biÓu thøc so s¸nh cã trÞ null tham gia vµ kÕt qu¶ cña biÓu thøc phô thuéc vµo trÞ null th× kÕt qu¶ lµ kh«ng x¸c ®Þnh, tuy nhiªn trong biÓu thøc DECODE, hai gi¸ trÞ null ®−îc xem lµ b»ng nhau trong phÐp so s¸nh. ORACLE xem c¸c biÓu thøc víi kÕt qu¶ kh«ng x¸c ®Þnh t−¬ng ®−¬ng víi FALSE, vÝ dô comm = NULL cã kÕt qu¶ kh«ng x¸c ®Þnh vµ do ®ã biÓu thøc so s¸nh xem nh− cho kÕt qu¶ FALSE. Trong c©u lÖnh sau kh«ng cã mÉu tin nµo ®−îc chän SELECT * FROM emp WHERE comm=NULL; NÕu muèn chän c¸c nh©n viªn cã comm lµ NULL th× ph¶i dïng to¸n tö IS NULL SELECT * FROM emp WHERE comm IS NULL; 2.4 Läc d÷ liÖu tõ c¸c row cã cïng gi¸ trÞ Mét c©u lÖnh truy vÊn cã thÓ tr¶ vÒ c¸c row cã cïng gÝa trÞ. Vd: SELECT JOB FROM EMP; SELECT DEPTNO FROM EMP; SELECT JOB, DEPTNO FROM EMP; §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 11 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL §Ó läc lÊy mét gi¸ trÞ duy nhÊt ng−êi ta dïng mÖnh ®Ò DISTINCT. MÖnh ®Ò nµy ph¶i ®−îc ®Æt tr−íc tÊt c¶ c¸c column, sau mÖnh ®Ò SELECT. Vd: SELECT DISTINCT JOB FROM EMP; SELECT DISTINCT DEPTNO FROM EMP; SELECT DISTINCT JOB, DEPTNO FROM EMP; 2.5 HiÓn thÞ cÊu tróc b¶ng Có ph¸p DESC[RIBE] table_name (lÖnh nµy chØ ch¹y ®−îc trªn sqlplus, kh«ng ch¹y ®−îc trªn PL/SQL Develop) VÝ dô: DESC emp; Name Null? Type ------------------------------- -------- ---EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) KiÓu d÷ liÖu NOT NULL nghÜa lµ column nhÊt ®Þnh ph¶i cã gi¸ trÞ. 2.6 C¸c lÖnh cña c«ng cô SQL*Plus 2.6.1 C¸c lÖnh so¹n th¶o LÖnh M« t¶ A[PPEND] text §−a thªm ®o¹n text vµo dßng hiÖn t¹i C[HANGE] /old/new ChuyÓn ®o¹n text cò thµnh ®o¹n text míi trong dßng hiÖn t¹i C[HANGE] /text/ Xo¸ ®o¹n text trong dßng hiÖn t¹i CL[EAR] BUFF[ER] Xo¸ tÊt c¶ c¸c dßng trong SQL buffer DEL Xo¸ dßng hiÖn t¹i DEL n Xo¸ dßng n DEL m n Xo¸ dßng tõ m ®Õn n I[NPUT] Thªm mét sè dßng nhÊt ®Þnh I[NPUT] text Thªm dßng cã chøa text L[IST] LiÖt kª toµn bé c¸c dßng trong SQL buffer L[IST] n LiÖt kª dßng n L[IST] m n LiÖt kª dßng m ®Õn n §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 12 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL R[UN] HiÓn thÞ vµ ch¹y lÖnh trong buffer N Nhµy ®Õn dßng n N text Thay dßng n bëi ®o¹n text 0 text ChÌn 1 dßng tr−íc dßng 1 2.6.2 C¸c lÖnh vÒ file LÖnh M« t¶ SAVE filename [.ext] [REP[LACE]|APP[END]] Ghi néi dung bufer thµnh file. APPEND ®Ó ghi thªm vµo file. REPLACE ®Ó chÌn lªn néi dung file cò. GET filename [.ext] Ghi néi dung file vµo buffer. MÆc ®Þnh phÇn ®u«i lµ .sql STA[RT] filename [.ext] Ch¹y c¸c lÖnh trong file @ filename [.ext] Gièng lÖnh Start ED[IT] So¹n th¶o néi dung bufffer cã tªn lµ afiedt.buf §Ó ch¹y néi dung buffer dïng lÖnh / ED[IT] filename [.ext ] So¹n th¶o néi dung file SPO[OL] filename [.ext ] [OFF|OUT] CÊt kÕt qu¶ hiÓn thÞ trªn mµn h×nh ra file. Vd: SPOOL result.sql .... SPOOL OFF EXIT 2.6.3 Tho¸t khái SQL*Plus C¸c lÖnh vÒ column Có ph¸p COLUMN [{column | alias} [option]] LÖnh M« t¶ CLE[AR] Xo¸ ®Þnh d¹ng cña column FOR[MAT] format ChuyÓn ®Þnh d¹ng cña cét d÷ liÖu HEA[DING] text §Æt nh·n co column JUS[TIFY] align C¸n tr¸i “ left , ph¶i - right, gi÷a - center cho nh·n NOPRI[NT] Èn column NUL[L] text HiÓn thÞ text nÕu gi¸ trÞ cña column lµ NULL PRI[NT} HiÓn thÞ column §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 13 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL TRU[NCATED] Xo¸ chuçi t¹i cuèi dßng ®Çu tiªn khi hiÓn thÞ WRA[PPED] Phñ cuèi chuçi cña dßng tiÕp theo WOR[D_WAPPED] Gièng WAP nh−ng tõ kh«ng bÞ c¾t L−u ý: c¸c ®Þnh d¹ng hiÓn thÞ nµy ®−îc l−u vµo bé nhí vµ ¸p dông cho mäi cét cã tªn nh− vËy, dï chóng ë trong b¶ng nµo Vd: ChØnh ®Þnh d¹ng vµ nh·n cña column COLUMN ename HEADING ‘Employee|Name’ FORMAT A15 COLUMN sal JUSTIFY LEFT FORMAT $ 99,990.00 COLUMN hiredate FORMAT A9 NULL ‘ Not hired’ Vd: HiÓn thÞ ®Þnh d¹ng hiÖn t¹i cña column COLUMN COLUMN ename Vd: Xo¸ ®Þnh d¹ng hiÖn t¹i cña column COLUMN ename CLEAR CLEAR COLUMN C¸c lo¹i ®Þnh d¹ng §Þnh d¹ng M« t¶ VÝ dô KÕt qu¶ An HiÓn thÞ dµi nhÊt n ký tù dïng cho c¸c column d¹ng ký tù hoÆc d¹ng ngµy 9 HiÓn thÞ sè, kh«ng bao gåm sè 0 999999 1234 0 HiÓn thÞ c¶ sè 0 099999 01234 $ HiÓn thi $ $9999 $1234 L HiÓn thÞ ký tù L L9999 L1234 . HiÓn thÞ dÊu thËp ph©n 9999.99 1234.00 , HiÓn thÞ dÊu ph©n chia hµng ngh×n 9,999 1,234 2.7 Bµi tËp 1. Chän toµn bé th«ng tin trong b¶ng SALGRADE GRADE LOSAL HISAL ---------- ---------- ---------1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 2. Chän toµn bé th«ng tin trong b¶ng EMP EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ----- --------- ----- ------ ---------7839 KING PRESIDENT 17-11-1981 5000 10 7698 BLAKE MANAGER 7839 01-05-1981 2850 30 7782 CLARK MANAGER 7839 09-06-1981 2450 10 7566 JONES MANAGER 7839 02-04-1981 2975 20 §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 14 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 7654 MARTIN SALESMAN 7499 ALLEN SALESMAN 7844 TURNER SALESMAN 7900 JAMES CLERK 7521 WARD SALESMAN 7902 FORD ANALYST 7369 SMITH CLERK 7788 SCOTT ANALYST 7876 ADAMS CLERK 7934 MILLER CLERK 3. HiÓn thÞ mäi lo¹i nghÒ nghiÖp 7698 7698 7698 7698 7698 7566 7902 7566 7788 7782 28-09-1981 20-02-1981 08-09-1981 03-12-1981 22-02-1981 03-12-1981 17-12-1980 09-12-1982 12-01-1983 23-01-1982 1250 1600 1500 950 1250 3000 800 3000 1100 1300 1400 300 0 500 30 30 30 30 30 20 20 20 20 10 JOB --------ANALYST CLERK MANAGER PRESIDENT SALESMAN 4. HiÓn thÞ tªn nh©n viªn vµ thu nhËp trong mét n¨m (REMUNERATION) ENAME REMUNERATION ---------- -----------KING 60000 BLAKE 34200 CLARK 29400 JONES 35700 MARTIN 16400 ALLEN 19500 TURNER 18000 JAMES 11400 WARD 15500 FORD 36000 SMITH 9600 SCOTT 36000 ADAMS 13200 MILLER 15600 14 rows selected. 5. HiÓn thÞ theo néi dung d−íi ®©y Who, what and when ---------------------------------------------------------------------KING HAS HELP THE POSITION OF PRESIDENT IN DEPT 10 SINCE 17-11-1981 BLAKE HAS HELP THE POSITION OF MANAGER IN DEPT 30 SINCE 01-05-1981 CLARK HAS HELP THE POSITION OF MANAGER IN DEPT 10 SINCE 09-06-1981 JONES HAS HELP THE POSITION OF MANAGER IN DEPT 20 SINCE 02-04-1981 MARTIN HAS HELP THE POSITION OF SALESMAN IN DEPT 30 SINCE 28-09-1981 ALLEN HAS HELP THE POSITION OF SALESMAN IN DEPT 30 SINCE 20-02-1981 TURNER HAS HELP THE POSITION OF SALESMAN IN DEPT 30 SINCE 08-09-1981 JAMES HAS HELP THE POSITION OF CLERK IN DEPT 30 SINCE 03-12-1981 WARD HAS HELP THE POSITION OF SALESMAN IN DEPT 30 SINCE 22-02-1981 FORD HAS HELP THE POSITION OF ANALYST IN DEPT 20 SINCE 03-12-1981 SMITH HAS HELP THE POSITION OF CLERK IN DEPT 20 SINCE 17-12-1980 SCOTT HAS HELP THE POSITION OF ANALYST IN DEPT 20 SINCE 09-12-1982 ADAMS HAS HELP THE POSITION OF CLERK IN DEPT 20 SINCE 12-01-1983 MILLER HAS HELP THE POSITION OF CLERK IN DEPT 10 SINCE 23-01-1982 14 rows selected. 6. HiÓn thÞ cÊu tróc b¶ng emp; 7. Thay ®æi nh·n vµ ®Þnh d¹ng hiÓn thÞ cña cét sal vµ hiredate trong b¶ng emp; §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 15 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 3 Truy vÊn d÷ liÖu cã ®iÒu kiÖn 3.1 MÖnh ®Ò ORDER BY Có ph¸p SELECT [DISTINCT ] {*, column [alias],...} FROM table; [WHERE condition] [ORDER BY expr/position [DESC/ASC]] MÖnh ®Ò ORDER BY dïng ®Ó s¾p xÕp sè liÖu ®−îc hiÓn thÞ vµ ph¶i ®Æt ë vÞ trÝ sau cïng cña c©u lÖnh truy vÊn, VÝ dô: SELECT ENAME, JOB, SAL*12, DEPTNO FROM EMP ORDER BY ENAME; MÖnh ®Ó ORDER BY mÆc ®Þnh s¾p xÕp theo thø tù t¨ng dÇn ASC[ENDING] • Sè thÊp tr−íc • Ngµy nhá tr−íc • Ký tù theo b¶ng ch÷ c¸i §Ó s¾p xÕp theo thø tù ng−îc l¹i (gi¶m dÇn) ®Æt tõ kho¸ DESC[ENDING] sau column cÇn s¾p thø tù. Vi dô: SELECT ENAME, JOB, HIREDATE FROM EMP ORDER BY HIREDATE DESC ; Order nhiÒu column MÖnh ®Ò Order cßn cã thÓ s¾p xÕp nhiÒu column. C¸c column cÇn s¾p xÕp ®−îc viÕt thø tù sau mÖnh ®Ò ORDER BY vµ c¸ch bëi dÊu phÈy (,). Column nµo gÇn mÖnh ®Ó ORDER BY h¬n cã møc ®é −u tiªn khi s¾p xÕp cao h¬n. ChØ ®Þnh c¸ch thøc s¾p xÕp ASC/DESC ®−îc viÕt sau column c¸ch bëi mét dÊu c¸ch. VÝ dô: SELECT DEPTNO, JOB, ENAME, SAL FROM EMP ORDER BY DEPTNO, SAL DESC ; Order gi¸ trÞ NULL Riªng ®èi víi gi¸ trÞ NULL, nÕu s¾p xÕp theo thø tù ASCENDING sÏ n»m ë c¸c vÞ trÝ cuèi cïng. Chó ý Cã thÓ chØ ®Þnh s¾p xÕp theo thø tù c¸c column trong mÖnh ®Ò SELECT. VÝ dô: SELECT DEPTNO, JOB, ENAME, SAL FROM EMP ORDER BY 2; 3.2 MÖnh ®Ò WHERE Có ph¸p SELECT [DISTINCT ] {*, column [alias],...} FROM table; [WHERE condition (s)] [ORDER BY expr/position [DESC/ASC]] §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 16 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL MÖnh ®Ò WHERE dïng ®Ó ®Æt ®iÒu kiÖn cho toµn bé c©u lÖnh truy vÊn. Trong mÖnh ®Ò WHERE cã thÓ cã c¸c thµnh phÇn: • Tªn column • To¸n tö so s¸nh • Tªn column, h»ng sè hoÆc danh s¸ch c¸c gi¸ trÞ VÝ dô: SELECT DEPTNO, JOB, ENAME, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000 ; Truy vÊn d÷ liÖu víi nhiÒu ®iÒu kiÖn MÖnh ®Ò WHERE cho phÐp ghÐp ®−îc nhiÒu ®iÒu kiÖn th«ng qua c¸c to¸n tö logic AND/OR. To¸n tö AND yªu cÇu d÷ liÖu ph¶i tho¶ m·n c¶ 2 ®iÒu kiÖn. To¸n tö OR cho phÐp d÷ liÖu tho¶ m·n 1 trong 2 ®iÒu kiÖn. VÝ dô: SELECT DEPTNO, JOB, ENAME, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000 AND JOB = ‘MANAGER’; SELECT DEPTNO, JOB, ENAME, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000 OR JOB = ‘MANAGER’; SELECT DEPTNO, JOB, EMPNO, ENAME, SAL FROM EMP WHERE SAL > 1500 AND JOB = ‘MANAGER’ OR JOB =’SALESMAN’; SELECT DEPTNO, JOB, EMPNO, ENAME, SAL FROM EMP WHERE SAL > 1500 AND (JOB = ‘MANAGER’ OR JOB =’SALESMAN’); 3.3 C¸c to¸n tö To¸n tö so s¸nh • = : To¸n tö b»ng hay t−¬ng ®−¬ng • !=, ^=, '+, <\> : To¸n tö kh¸c hay kh«ng t−¬ng ®−¬ng • > : To¸n tö lín h¬n • < : To¸n tö nhá h¬n • >= : To¸n tö lín h¬n hoÆc b»ng • <= : To¸n tö nhá h¬n hoÆc b»ng C¸c to¸n tö logic • NOT : Phñ ®Þnh mÖnh ®Ò • AND : yªu cÇu d÷ liÖu ph¶i tho¶ m·n c¶ 2 ®iÒu kiÖn • OR : cho phÐp d÷ liÖu tho¶ m·n 1 trong 2 ®iÒu kiÖn §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 17 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL C¸c to¸n tö cña SQL • [NOT] BETWEEN x AND y : [Kh«ng] lín h¬n hoÆc b»ng x vµ nhá h¬n hoÆc b»ng y. • IN (danh s¸ch) • x [NOT] LIKE y : §óng nÕu x [kh«ng] gièng khung mÉu y. C¸c ký tù dïng trong khu«n mÉu: DÊu g¹ch d−íi ( _ ) : ChØ mét ký tù bÊt kú DÊu phÇn tr¨m ( % ) : ChØ mét nhãm ký tù bÊt kú • IS [NOT] NULL : kiÓm tra gi¸ trÞ rçng • EXISTS : Tr¶ vÒ TRUE nÕu cã tån t¹i. : thuéc bÊt kú gi¸ trÞ nµo trong danh s¸ch [NOT] BETWEEN x AND y VÝ dô chän nh©n viªn cã l−¬ng n»m trong kho¶ng 2000 vµ 3000 SELECT * FROM emp WHERE sal BETEEN 2000 AND 3000; IN (danh s¸ch) Chän nh©n viªn cã l−¬ng b»ng mét trong 2 gi¸ trÞ 1400 hoÆc 3000 SELECT * FROM emp WHERE sal IN (1400, 3000); T×m tªn phßng ban nÕu phßng ®ã cã nh©n viªn lµm viÖc. SELECT dname FROM dept WHERE EXISTS (SELECT * FROM emp WHERE dept.deptno = emp.deptno); x [NOT] LIKE y T×m nh©n viªn cã tªn b¾t ®Çu b»ng chuçi SMITH SELECT * FROM emp WHERE ename LIKE 'SMITH_'; §Ó chän nh÷ng nh©n viªn cã tªn b¾t ®Çu b»ng 'SM' SELECT * FROM emp WHERE ename LIKE 'SM%'; §Ó t×m nh÷ng nh©n viªn cã tªn cã chuçi 'A_B' SELECT ename FROM emp WHERE ename LIKE '%A\_B%'; ESCAPE '\' V× ký hiÖu "_" dïng ®Ó ®¹i diÖn cho mét ký tù bÊt kú nªn nÕu kh«ng cã mÖnh ®Ò ESCAPE, c©u lÖnh trªn sÏ t×m tÊt c¶ c¸c nh©n viªn tªn AAB, ABB, ACB, v.v... (nÕu kh«ng cã mÖnh ®Ò ESCAPE '\') NÕu muèn ký hiÖu "_" mang ý nghÜa nguyªn thñy, tøc lµ kh«ng cßn ®¹i diÖn cho ký tù bÊt kú n÷a, ta ®Æt dÊu "\" tr−íc ký hiÖu. §ång thêi khai b¸o thªm mÖnh ®Ò ESCAPE "\" Ta còng cã thÓ dïng mét ký tù bÊt kú thay cho "\". Ch¼ng h¹n mÖnh ®Ò sau cã cïng kÕt qu¶ víi mÖnh ®Ò trªn SELECT ename FROM emp WHERE ename LIKE '%A^_B%'; ESCAPE '^' Ta gäi c¸c ký tù nh− "\" hay "^" nãi trªn lµ c¸c ký tù ESCAPE. IS [NOT] NULL VÝ du SELECT * FROM emp WHERE comm IS NULL ; §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 18 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 3.4 Bµi tËp 1. Chän nh©n viªn trong b¶ng EMP cã møc l−¬ng tõ 1000 ®Õn 2000 (chän c¸c tr−êng ENAME, DEPTNO, SAL). ENAME DEPTNO SAL ---------- ---------- ---------ALLEN 30 1600 WARD 30 1250 MARTIN 30 1250 TURNER 30 1500 ADAMS 20 1100 MILLER 10 1300 Sal Between 1000 to 2000 2.HiÓn thÞ m· phßng ban, tªn phßng ban, s¾p xÕp theo thø tù tªn phßng ban. DEPTNO -----10 40 20 30 DNAME ----------ACCOUNTING OPERATIONS RESEARCH SALES Order by dname 3. HiÓn thÞ danh s¸ch nh÷ng nh©n viªn lµm t¹i phßng 10 vµ 20 theo thø tù A,B,C EMPNO ---------7876 7782 7902 7566 7839 7934 7788 7369 ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----------------- ----- ---------- ---------- ----- ----ADAMS CLERK 7788 12-01-1983 1100 20 CLARK MANAGER 7839 09-06-1981 2450 10 FORD ANALYST 7566 03-12-1981 3000 20 JONES MANAGER 7839 02-04-1981 2975 20 KING PRESIDENT 17-11-1981 5000 10 MILLER CLERK 7782 23-01-1982 1300 10 SCOTT ANALYST 7566 09-12-1982 3000 20 SMITH CLERK 7902 17-12-1980 800 20 Where deptno in (10,20) order by ename asc 4. HiÓn thÞ tªn vµ nghÒ nghiÖp nh÷ng nh©n viªn lµm nghÒ th− ký (clerk) t¹i phßng 20. ENAME ---------SMITH ADAMS JOB --------CLERK CLERK Where upper(job)= upper('clerk') and deptno = '20'; (l−u ý vÊn ®Ò ch÷ Hoa-th−êng) 5. HiÓn thÞ tÊt c¶ nh÷ng nh©n viªn mµ tªn cã c¸c ký tù TH vµ LL. ENAME ---------SMITH ALLEN MILLER Where ename like “%TH%“ or ename like “%LL%“ 6. HiÓn thÞ tªn nh©n viªn, nghÒ nghiÖp, l−¬ng cña nh÷ng nh©n viªn cã gi¸m ®èc qu¶n lý. ENAME ---------SMITH ALLEN WARD JONES JOB SAL --------- ---------CLERK 800 SALESMAN 1600 SALESMAN 1250 MANAGER 2975 §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 19 C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL MARTIN BLAKE CLARK SCOTT TURNER ADAMS JAMES FORD MILLER SALESMAN MANAGER MANAGER ANALYST SALESMAN CLERK CLERK ANALYST CLERK 1250 2850 2450 3000 1500 1100 950 3000 1300 13 rows selected. 7. HiÓn thÞ tªn nh©n viªn, m· phßng ban, ngµy gia nhËp c«ng ty sao cho gia nhËp c«ng ty trong n¨m 1983. ENAME DEPTNO HIREDATE ---------- ---------- --------ADAMS 20 12-JAN-83 Where to_char(hiredate) like “%83“ Where hiredate like “%83“ 8. HiÓn thÞ tªn nh©n viªn, l−¬ng mét n¨m (ANUAL_SAL ), th−ëng sao cho l−¬ng lín h¬n th−ëng vµ nghÒ nghiÖp lµ SALEMAN, s¾p theo thø tù l−¬ng gi¶m dÇn vµ tªn t¨ng dÇn. ANUAL_SAL COMM ---------- ---------19200 300 18000 0 15000 500 4 4.1 C¸c hµm ¸p dông cho 1 dßng d÷ liÖu C¸c hµm sè §Çu vµo vµ ®Çu ra lµ c¸c gi¸ trÞ kiÓu sè ROUND(n[,m]) cho gi¸ trÞ lµm trßn cña n (®Õn cÊp m, mÆc nhiªn m=0) TRUNC(n[,m]) cho gi¸ trÞ n lÊy m ch÷ sè tÝnh tõ chÊm thËp ph©n = Format(dl, “99999,00“) CEIL(n) cho sè nguyªn nhá nhÊt lín h¬n hoÆc b»ng n = FLOOR(n) cho sè nguyªn lín nhÊt b»ng hoÆc nhá h¬n n = POWER(m,n) cho lòy thõa bËc n cña m = EXP(n) cho gi¸ trÞ cña en = SQRT(n) cho c¨n bËc 2 cña n, n>=0 = SIGN(n) cho dÊu cña n. = n<0 cã SIGN(n)= -1 n=0 cã SIGN(n)= 0 n>0 cã SIGN(n)= 1 ABS(n) cho gi¸ trÞ tuyÖt ®èi = MOD(m,n) cho phÇn d− cña phÐp chia m cho n = Mét sè hµm kiÓu sè tham kh¶o kh¸c: LOG(m,n) cho logarit c¬ sè m cña n §µo t¹o c¬ b¶n: SQL vµ PL/SQL = Trang 20