Giáo trình SQL và PL SQL part 2

Chia sẻ: Mr Yukogaru | Ngày: | Loại File: PDF | Số trang:7

Thêm vào BST

Báo xấu

170
lượt xem 69
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

một số câu lệnh truy vấn cơ bản: SELECT From distinct alias .....

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Giáo trình SQL và PL SQL part 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 §Ó 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 Tho¸t khái SQL*Plus 2.6.3 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 M« t¶ VÝ dô KÕt qu¶ d¹ng 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 7698 28-09-1981 1250 1400 30 7499 ALLEN SALESMAN 7698 20-02-1981 1600 300 30 7844 TURNER SALESMAN 7698 08-09-1981 1500 0 30 7900 JAMES CLERK 7698 03-12-1981 950 30 7521 WARD SALESMAN 7698 22-02-1981 1250 500 30 7902 FORD ANALYST 7566 03-12-1981 3000 20 7369 SMITH CLERK 7902 17-12-1980 800 20 7788 SCOTT ANALYST 7566 09-12-1982 3000 20 7876 ADAMS CLERK 7788 12-01-1983 1100 20 7934 MILLER CLERK 7782 23-01-1982 1300 10 3. HiÓn thÞ mäi lo¹i nghÒ nghiÖp 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 •
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) : thuéc bÊt kú gi¸ trÞ nµo trong 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. [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