Học Oracle cơ bản (Bài 1-SQL Basic)

ViÕt C©u LÖnh SQL C¬ b¶n Tæng Quan VÒ Bµi Häc Sau bµi häc nµy, b¹n cã thÓ: LiÖt kª kh¶ n¨ng cña c©u lÖnh SELECT Ch¹y mét c©u lÖnh SELECT c¬ b¶n Ph©n biÖt sù kh¸c nhau cña c©u lÖnh SQL vµ SQL*PLUS Login vµ T¹o c¸c b¶ng Login: Ch¹y SQL*Plus 8.0 HoÆc: hocvien1/hv123@orcl T¹o c¸c b¶ng T×m vÞ trÝ b¶ng: demobld.sql (oracle-home\dbs\demobld.sql) SQL> START C:\ORANT\DBS\DEMOBLD.SQL C¸c lÖnh cña SQL SELECT INSERT UPDATE DELETE CREATE ALTER DROP RENAME TRUNCATE COMMIT ROLLBACK SAVEPOINT GRANT REVOKE Data retrieval Data manipulation language (DML) Data definition language (DDL) Transaction control Data control language (DCL) Kh¶ N¨ng Cña C©u LÖnh SELECT Selection Projection Table 1 Table 2 Table 1 Table 1 Join C©u LÖnh SELECT C¬ B¶n SELECT [DISTINCT] {*, column [alias],...} FROM table; SELECT x¸c ®Þnh cét ®­îc lùa chän FROM X¸c ®Þnh d÷ liÖu lÊy tõ b¶ng nµo. Qui T¾c ViÕt C©u LÖnh SQL Kh«ng ph©n biÖt ch÷ hoa, ch÷ th­êng. Cã thÓ viÕt trªn nhiÒu dßng C¸c tõ kho¸ viÕt ®Çy ®ñ trªn mét dßng. C¸c mÖnh ®Ò th­êng ®­îc viÕt trªn nhiÒu dßng . Cã thÓ dïng Tab vµ Ident lµm c©u lÖnh râ rµng. Lùa Chän Toµn Bé C¸c Cét DEPTNO DNAME LOC --------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> SELECT * 2 FROM dept; Lùa chän mét sè cét DEPTNO LOC --------- ------------- 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTON SQL> SELECT deptno, loc 2 FROM dept; C¸c Tiªu ®Ò Cña Cét MÆc ®Þnh: S¾p xÕp cña cét Th¼ng bªn tr¸i: d÷ liÖu kiÓu ngµy vµ kiÓu kÝ tù Th¼ng bªn ph¶i: d÷ liÖu kiÓu sè HiÓn thÞ d­íi d¹ng ch÷ hoa PhÐp to¸n Sè Häc T¹o c¸c biÓu thøc trªn c¸c phÐp to¸n sè häc Operator + - * / Description Add Subtract Multiply Divide Sö Dông C¸c PhÐp To¸n Sè Häc SQL> SELECT ename, sal, sal+300 2 FROM emp; ENAME SAL SAL+300 ---------- --------- --------- KING 5000 5300 BLAKE 2850 3150 CLARK 2450 2750 JONES 2975 3275 MARTIN 1250 1550 ALLEN 1600 1900 ... 14 rows selected. ¦u Tiªn C¸c PhÐp To¸n Nh©n vµ Chia th× cã møc ­u tiªn h¬n Céng vµ trõ. C¸c phÐp to¸n cïng ®é ­u tiªn ®­îc ®¸nh gi¸ tõ tr¸i qua ph¶i. C¸c dÊu ngoÆc sö dông ®Ó b¾t buéc tÝnh ­u tiªn vµ lµm s¸ng sña c©u lÖnh. VÝ dô: TÝnh ¦u Tiªn Cña PhÐp To¸n. SQL> SELECT ename, sal, 12*sal+100 2 FROM emp; ENAME SAL 12*SAL+100 ---------- --------- ---------- KING 5000 60100 BLAKE 2850 34300 CLARK 2450 29500 JONES 2975 35800 MARTIN 1250 15100 ALLEN 1600 19300 ... 14 rows selected. Sö Dông DÊu NgoÆc SQL> SELECT ename, sal, 12*(sal+100) 2 FROM emp; ENAME SAL 12*(SAL+100) ---------- --------- ----------- KING 5000 61200 BLAKE 2850 35400 CLARK 2450 30600 JONES 2975 36900 MARTIN 1250 16200 ... 14 rows selected. §Þnh NghÜa Gi¸ TrÞ Null Null lµ gi¸ trÞ kh«ng cã s½n, kh«ng biÕt, kh«ng thÓ g¸n. Gi¸ trÞ Null kh¸c víi gi¸ trÞ kh«ng vµ kho¶ng trèng (space) SQL> SELECT ename, job, comm 2 FROM emp; ENAME JOB COMM ---------- --------- --------- KING PRESIDENT BLAKE MANAGER ... TURNER SALESMAN 0 ... 14 rows selected. Gi¸ TrÞ Null Trong C¸c PhÐp To¸n Sè Häc Mét phÐp to¸n sè häc cã chøa gi¸ trÞ Null th× ®­îc ®¸nh gi¸ lµ Null. SQL> select ename, 12*sal+comm 2 from emp 3 WHERE ename='KING'; ENAME 12*SAL+COMM ---------- ----------- KING §Þnh NghÜa BÝ Danh Cho Cét §æi tªn mét cét khi hiÓn thÞ Dïng trong viÖc tÝnh to¸n §Þnh nghÜa bÝ danh ë ngay sau tªn cét hay dïng tõ kho¸ AS CÇn cã dÊu nh¸y kÐp nªu cã kho¶ng trèng hay kÝ tù ®Æc biÖt trong bÝ danh. Sö Dông BÝ Danh Cét SQL> SELECT ename AS name, sal salary 2 FROM emp; NAME SALARY ------------- ---------... SQL> SELECT ename "Name", 2 sal*12 "Annual Salary" 3 FROM emp; To¸n H¹ng KÕt Nèi Chuçi Dïng cho viÖc kÕt nèi chuçi víi c¸c cét hay c¸c cét víi nhau. To¸n h¹ng lµ kÝ tù (||) T¹o ra kÕt qu¶ lµ mét cét d¹ng chuçi ký tù. Sö Dông To¸n H¹ng KÕt Nèi Chuçi SQL> SELECT ename||job AS "Employees" 2 FROM emp; Employees ------------------- KINGPRESIDENT BLAKEMANAGER CLARKMANAGER JONESMANAGER MARTINSALESMAN ALLENSALESMAN ... 14 rows selected. Chuçi Ký Tù D¹ng Ch÷ Mét LITERAL lµ mét chuçi kÝ tù, hay mét m« t¶, hay mét con sè ®­îc thªm vµo trong mét c©u lÖnh SELECT. NÕu mét litteral d¹ng Ngµy th¸ng hay chuçi kÝ tù cÇn cã dÊu ngoÆc ®¬n. Mçi chuçi kÝ tù nµy ®­îc tr¶ vÒ mét lÇn øng víi mçi hµng Sö Dông Chuçi KÝ Tù D¹ng Ch÷ Employee Details ------------------------- KING is a PRESIDENT BLAKE is a MANAGER CLARK is a MANAGER JONES is a MANAGER MARTIN is a SALESMAN ... 14 rows selected. SQL> SELECT ename ||' '||'is a'||' '||job 2 AS "Employee Details" 3 FROM emp; C¸c Hµng Tr¶ VÒ bÞ LÆp SQL mÆc ®Þnh c¸c hµng tr¶ vÒ lµ c¶ c¸c hµng cã gi¸ trÞ trïng lÆp. SQL> SELECT deptno 2 FROM emp; DEPTNO --------- 10 30 10 20 ... 14 rows selected. Loai Trõ c¸c Hµng trïng LÆp Lo¹i trõ c¸c hµng trïng lÆp b»ng tõ kho¸ DISTINCT trong c©u lÖnh SELECT SQL> SELECT DISTINCT deptno 2 FROM emp; DEPTNO --------- 10 20 30 SQL and SQL*Plus SQL*Plus Buffer SQL Statements vµ SQL*Plus Commands SQL statements SQL A language ANSI standard Keyword cannot be abbreviated Statements manipulate data and table definitions in the database SQL*Plus An environment Oracle proprietary Keywords can be abbreviated Commands do not allow manipulation of values in the database SQL buffer SQL*Plus commands SQL*Plus buffer §¨ng nhËp vµo SQL*PLUS M« t¶ cÊu tróc b¶ng So¹n th¶o c©u lÖnh SQL Thùc thi c©u lÖnh SQL trong SQL*PLUS CÊt c©u lÖnh SQL vµo File Ch¹y tÖp chøa c©u lÖnh SQL ChuyÓn c¸c c©u lÖnh tõ file vµo buffer Tæng Quan SQL*PLUS §¨ng NhËp Vµo SQL*PLUS Tõ m«i tr­êng Window: Tõ chÕ ®é dßng lÖnh: sqlplus [username[/password [@database]]] HiÓn ThÞ CÊu Tróc Mét B¶ng Sö dông c©u lÖnh SQL*Plus ®Ó m« t¶ cÊu tróc mét b¶ng DESC[RIBE] tablename HiÓn ThÞ CÊu Tróc Mét B¶ng SQL> DESCRIBE dept Name Null? Type ----------------- -------- ------------ DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) C¸c C©u LÖnh SQL*Plus §Ó Söa Ch÷a Mét C©u LÖnh SQL A[PPEND] text C[HANGE] / old / new C[HANGE] / text / CL[EAR] BUFF[ER] DEL DEL n DEL m n C¸c C©u LÖnh SQL*Plus §Ó Söa Ch÷a C©u LÖnh SQL I[NPUT] I[NPUT] text L[IST] L[IST] n L[IST] m n R[UN] n n text 0 text C¸c C©u LÖnh SQL*Plus Cho File SAVE filename GET filename START filename @ filename EDIT filename SPOOL filename Tãm t¾t Lµm quen víi c©u lÖnh SELECT Lµm quen víi SQL vµ SQL*Plus