show user conn system/¾ÏÈ£ ==>system°èÁ¤À¸·Î DB¿¡ Á¢¼Ó conn /as sysdba ==>sys°èÁ¤À¸·Î DB¿¡ Á¢¼Ó (DB½ÃÀÛ,Á¾·á ÀÛ¾÷°¡´É) show user select status from v$instance; ==>DBÀÇ »óÅ ȮÀÎ -open (»ç¿ëÀÚµéÀÌ Á¢¼ÓÇؼ­ ÀÛ¾÷°¡´ÉÇÑ ½ÃÀÛ»óÅÂ) -mounted (mount»óÅ·μ­ DBÀÇ º¹±¸,»óź¯°æµîÀÌ °¡´ÉÇÑ »óÅÂ) -started (nomount»óÅ·μ­ ÀνºÅϽº¸¸ ½ÃÀÛµÈ »óÅÂ) select * from all_users; ==>ÇöÀç DB¿¡ Á¢¼Ó°¡´ÉÇÑ °èÁ¤Á¤º¸ È®ÀÎ @D:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\scott.sql conn scott/tiger select * from tab; ==>scott°èÁ¤ ¼ÒÀ¯ÀÇ ¸ðµç Å×À̺íµéÀÇ À̸§ È®ÀÎ CREATE TABLE employee( emp_num NUMBER(4) CONSTRAINT employee_emp_num_pk PRIMARY KEY, first_name VARCHAR2(10), larst_name VARCHAR2(10), soc_sec_num VARCHAR2(14), address VARCHAR2(30), tel VARCHAR2(15), hire_date DATE, store_code NUMBER(2)); select * from tab; ALTER TABLE employee RENAME COLUMN larst_name TO last_name; desc employee ==>employeeÅ×À̺íÀÇ ±¸Á¶ È®ÀÎ select * from employee ==>employeeÅ×ÀÌºí¿¡ ÀúÀåµÈ µ¥ÀÌÅ͸¦ ¸ðµÎ Á¶È¸ INSERT INTO employee VALUES(1,'±æµ¿','È«','800101-1000000','¼­Ãʱ¸ ¾çÀ絿','100-111',SYSDATE,10); ==>»õ·Î¿î ÇÑÇà »ðÀÔ col first_name format a5 col last_name format a5 SELECT * FROM employee; ** employeeÅ×À̺íÀÌ »ý¼ºµÈ ¹°¸®ÀûÀÎ ÆÄÀÏ È®ÀÎ conn /as sysdba select table_name,tablespace_name from dba_tables where owner='SCOTT'; select file_name from dba_data_files where tablespace_name='USERS'; ** glogin.sql D:\oraclexe\app\oracle\product\10.2.0\server\sqlplus\admin\glogin.sal SET PAGESIZE 100 set linesize 100 col ename format a6 col job format a9 col sal format 99999 ** SQL 1)µ¥ÀÌÅÍ Á¶È¸:select SELECT Ä÷³¸í/* distinct , " " ==>5 FROM Å×À̺í¸í ==>1 WHERE ÇàÁ¶°ÇÀý ==>2 GROUP BY ±×·ìÁ¶°Ç ==>3 HAVING ±×·ìÁ¦ÇÑÁ¶°ÇÀý ==>4 ORDER BY Á¤·ÄÁ¶°Ç; ==>6 2)°´Ã¼(Å×À̺íµî)»ý¼º :create ¼öÁ¤ :alter »èÁ¦:drop,truncate ==>DDL(Data Definition Language) 3)µ¥ÀÌÅÍÀÔ·Â:insert ¼öÁ¤ : update »èÁ¦:delete ==>DML(Data Manipulation Language) 4)Æ®·£Àè¼Ç Á¦¾î:commit,rollback,savepoint ==>TCL(Transaction Control Language) 5)User¿¡°Ô ±ÇÇѺο©:Grante ±ÇÇÑ Ãë¼Ò:Revoke ==>DCL(Data Control Language) *** Select ¿¹Á¦ SELECT Ä÷³¸í/Ä÷³¸í(+,-,*,/ )¼ýÀÚµîÀÇ Ç¥Çö½Ä/distinct Ä÷³¸í/*/Ä÷³¸í "º°Äª"/¿¬°á¿¬»êÀÚ(||) FROM Å×À̺í¸í; ¹®Á¦1) SCOTTÀÌ ¼ÒÀ¯ÇÏ°í ÀÖ´Â EMP Table¿¡¼­ »ç¿ø ¹øÈ£, À̸§, ±Þ¿©, ´ã´ç¾÷¹«¸¦ Ãâ·ÂÇϽÿÀ. SELEECT empno,ename,sal,job FROM emp; ¹®Á¦2) ¸ðµç »ç¿øÀÇ À̸§ ¹× ±Þ¿©,±Þ¿©¿¡ 300À» ´õÇÑ°ªÀ» Ãâ·ÂÇϽÿÀ. SELECT ename,sal,sal+300 FROM emp; ¹®Á¦3) EMP Å×ÀÌºí¿¡¼­ À̸§, ±Þ¿©, º¸³Ê½º, ¿¬ºÀÀ» Ãâ·ÂÇϽÿÀ. SELECT ename,sal,comm,sal*12+comm,sal*12+nvl(comm,0) FROM emp; ¹®Á¦4) EMP Å×ÀÌºí¿¡¼­ ENAME¸¦ NAME·Î SALÀ» SALARY·Î Ãâ·ÂÇϽÿÀ. SELECT ename as name,sal salary FROM emp; ¹®Á¦5) EMP Å×ÀÌºí¿¡¼­ ENAME¸¦ ¡®¼º ¸í¡¯À¸·Î SAL*12¸¦ ¡®±Þ ¿©¡¯·Î Ãâ·ÂÇϽÿÀ. SELECT ename as "¼º ¸í",sal*12 "±Þ ¿©" FROM emp; ¹®Á¦6)EMP Å×ÀÌºí¿¡¼­ À̸§°ú ¾÷¹«¸¦ ¡°KING is a PRESIDENT¡± Çü½ÄÀ¸·Î Ãâ·ÂÇϽÿÀ. SELECT ename||' is a '||job from emp; ¹®Á¦7) EMP Å×ÀÌºí¿¡¼­ À̸§°ú ¿¬ºÀÀ» ¡°KING: 1 Year salary = 60000¡± Çü½ÄÀ¸·Î Ãâ·ÂÇϽÿÀ. SELECT ename||': 1 Year Salary ='||(sal*12+nvl(comm,0)) "¿¬ºÀ" FROM emp; ¹®Á¦8) EMP Å×ÀÌºí¿¡¼­ ´ã´çÇÏ°í ÀÖ´Â ¾÷¹«ÀÇ Á¾·ù¸¦ Ãâ·ÂÇϽÿÀ. SELECT distinct job FROM emp; ¹®Á¦9) EMP Å×ÀÌºí¿¡¼­ Áߺ¹µÇÁö ¾Ê´Â ºÎ¼­¹øÈ£¸¦ Ãâ·ÂÇϽÿÀ. SELECT distinct deptno FROM emp; SELECT Ä÷³¸í/Ä÷³¸í(+,-,*,/ )¼ýÀÚµîÀÇ Ç¥Çö½Ä/distinct Ä÷³¸í/*/Ä÷³¸í "º°Äª"/¿¬°á¿¬»êÀÚ(||) ==>3 FROM Å×À̺í¸í ==>1 WHERE Ä÷³Ç¥Çö½Ä ¿¬»êÀÚ °ª ; ==>ÇàÀ» Á¦ÇÑÇÏ´Â Á¶°ÇÀý ==>2 **¿¬»êÀÚ 1)ºñ±³¿¬»êÀÚ : =, >, <,>=, <=, !=,<> 2)SQL¿¬»êÀÚ - between °ª1 and °ª2 - in (°ª1,°ª2,°ª3...) - like '...%..._ ' : ¹®ÀÚ¿­ÀÇ ÆÐÅÏ % :¸ðµç, _ : ¹Ýµå½Ã 1 - is null :³Î°ª ¹®Á¦10) EMP Å×ÀÌºí¿¡¼­ ±Þ¿©°¡ 3000ÀÌ»óÀÎ »ç¿øÀÇ »ç¿ø¹øÈ£, À̸§, ´ã´ç¾÷¹«, ±Þ¿©¸¦ Ãâ·ÂÇ϶ó SELECT empno,ename,job,sal FROM emp WHERE sal>=3000; ¹®Á¦11) EMP Å×ÀÌºí¿¡¼­ ´ã´ç¾÷¹«°¡ ManagerÀÎ »ç¿øÀÇ Á¤º¸¸¦ »ç¿ø¹øÈ£, ¼º¸í, ´ã´ç¾÷¹«, ±Þ¿©, ºÎ¼­¹øÈ£¸¦ Ãâ·ÂÇ϶ó. SELECT empno,ename,job,sal,deptno FROM emp WHERE job='MANAGER'; ¹®Á¦12) EMP Å×ÀÌºí¿¡¼­ 1982³â 1¿ù 1ÀÏ ÀÌÈÄ¿¡ ÀÔ»çÇÑ »ç¿øÀÇ »ç¿ø¹øÈ£, ¼º¸í, ´ã´ç¾÷¹«, ±Þ¿©, ÀÔ»çÀÏÀÚ, ºÎ¼­¹øÈ£¸¦ Ãâ·Â select * from nls_session_parameters; SELECT empno,ename,job,sal,hiredate,deptno FROM emp WHERE hiredate > '1982/01/01'; ¹®Á¦13) EMP Å×ÀÌºí¿¡¼­ ±Þ¿©°¡ 1300¿¡¼­ 1500»çÀÌÀÇ »ç¿øÀÇ ¼º¸í, ´ã´ç¾÷¹«, ±Þ¿©, ºÎ¼­¹øÈ£¸¦ Ãâ·ÂÇÏ¿©¶ó SELECT ename,job,sal,deptno FROM emp WHERE sal between 1300 and 1500; WHRE sal >=1300 and sal <=1500; ¹®Á¦14) EMP Å×ÀÌºí¿¡¼­ »ç¿ø¹øÈ£°¡ 7902,7788,7566ÀÎ »ç¿øÀÇ »ç¿ø¹øÈ£, ¼º¸í, ´ã´ç¾÷¹«, ±Þ¿©, ÀÔ»çÀÏÀÚ¸¦ Ãâ·ÂÇÏ¿©¶ó SELECT empno,ename,job,sal,hiredate FROM emp WHERE empno in (7902,7788,7566) where empno=7902 or empno=7788 or empno=7566; ¹®Á¦15) EMP Å×ÀÌºí¿¡¼­ ÀÔ»çÀÏÀÚ°¡ 1982³âµµ¿¡ ÀÔ»çÇÑ »ç¿øÀÇ »ç¹ø, ¼º¸í, ´ã´ç¾÷¹«, ±Þ¿©, ÀÔ»çÀÏÀÚ, ºÎ¼­¹øÈ£¸¦ Ãâ·ÂÇÏ¿©¶ó SELECT empno,ename,job,sal,hiredate,deptno FROM emp WHERE hiredate like '%82%'; WHERE hiredate between '82/01/01' and '82/12/31'; SELECT empno,ename,job,sal,hiredate,deptno FROM emp WHERE to_char(hiredate,'rrrr')='1982'; ¹®Á¦16) EMP Å×ÀÌºí¿¡¼­ º¸³Ê½º°¡ NULLÀÎ »ç¿øÀÇ »ç¿ø¹øÈ£, ¼º¸í, ´ã´ç¾÷¹«, ±Þ¿©, ÀÔ»çÀÏÀÚ, ºÎ¼­¹øÈ£¸¦ Ãâ·ÂÇÏ¿©¶ó SELECT empno,ename,job,sal,hiredate,deptno FROM emp WHERE comm is null; ¹®Á¦17) EMP Å×ÀÌºí¿¡¼­ ±Þ¿©°¡ 1100ÀÌ»óÀÌ°í JOBÀÌ ManagerÀÎ »ç¿øÀÇ »ç¿ø¹øÈ£, ¼º¸í, ´ã´ç¾÷¹«, ±Þ¿©, ÀÔ»çÀÏÀÚ, ºÎ¼­¹øÈ£¸¦ Ãâ·ÂÇÏ¿©¶ó. SELECT empno,ename,job,sal,hiredate,deptno FROM emp WHERE sal >=1100 and job='MANAGER'; ¹®Á¦18) EMP Å×ÀÌºí¿¡¼­ JOBÀÌ Manager,Clerk,Analyst°¡ ¾Æ´Ñ »ç¿øÀÇ »ç¿ø¹øÈ£, ¼º¸í, ´ã´ç¾÷¹«, ±Þ¿©, ºÎ¼­¹øÈ£¸¦ Ãâ·ÂÇÏ¿©¶ó SELECT empno,ename,job,sal,deptno FROM emp WHERE job not in ('MANAGER','CLERK','ANALYST') WHERE job <>'MANAGER' and job<>'CLERK' and job<>'ANALYST'; ¹®Á¦19) ¾÷¹«°¡ PRESIDENTÀÌ°í ±Þ¿©°¡ 1500ÀÌ»óÀ̰ųª,¾÷¹«°¡ SALESMANÀÎ »ç¿øÀÇ »ç¿ø¹øÈ£, À̸§, ¾÷¹«, ±Þ¿©¸¦ Ãâ·ÂÇÏ¿©¶ó. SELECT empno,ename,job,sal FROM emp WHERE (job='PRESIDENT' and sal >=1500) or job='SALESMAN'; ¹®Á¦20) EMP Å×ÀÌºí¿¡¼­ À̸§¿¡ LÀÌ µÎ ÀÚÀÌ»óÀÌ°í, ºÎ¼­°¡ 30À̰ųª ¶Ç´Â °ü¸®ÀÚ°¡ 7782ÀÎ »ç¿øÀÇ ¸ðµç Á¤º¸¸¦ Ãâ·ÂÇÏ´Â SELECT ¹®À» ÀÛ¼ºÇÏ¿©¶ó. SELECT * FROM emp WHERE ename like '%L%L%' and (deptno=30 or mgr=7782); ¹®Á¦21)EMP Å×ÀÌºí¿¡¼­ °¡Àå ÃÖ±Ù¿¡ ÀÔ»çÇÑ ¼øÀ¸·Î »ç¿ø¹øÈ£, À̸§, ¾÷¹«, ±Þ¿©, ÀÔ»çÀÏÀÚ,ºÎ¼­¹øÈ£¸¦ Ãâ·ÂÇÏ¿©¶ó. select empno,ename,job,sal,hiredate "ÀÔ»çÀÏ",deptno from emp order by "ÀÔ»çÀÏ" desc; ** ÇÔ¼ö :Á»´õ °­·ÂÇÑ ÁúÀǹ®À» ÀÛ¼ºÇϱâ À§Çؼ­ 1)¿À¶óŬÁ¦°ø ³»ÀåÇÔ¼ö - ´ÜÀÏÇàÇÔ¼ö - ¹®ÀÚÇüÇÔ¼ö:lower,upper,initcap,substr,instr, lpad,rpad,ltrim,rtrimµî - ¼ýÀÚÇüÇÔ¼ö :round,trunc,modµî - ³¯Â¥ÇüÇÔ¼ö :add_months,months_between, next_day,last_day,sysdateµî - Çüº¯È¯ÇÔ¼ö : to_char,to_number,to_dateµî - ±âŸ ÇÔ¼ö :nvl,decode,caseµî - ±×·ìÇàÇÔ¼ö :sum,count,max,min,avgµî 2)»ç¿ëÀÚ ÇÔ¼ö : pl/sql·Î ÀÛ¼º ** ´ÜÀÏÇàÇÔ¼öÀÇ »ç¿ë¿¹ 1.select¹®Àå¿¡¼­ select round(72.15,1),round(72.15,-1),round(72.15) from dual; select ename,sal,round(sal,-1), from emp; select * from emp where round(sal,-1)>=2000; 2.±×¿Ü¹®Àå¿¡¼­ update emp set sal=round(sal,-1) where ename='SCOTT'; ¹®Á¦1) EMP Å×ÀÌºí¿¡¼­ scottÀÇ »ç¿ø¹øÈ£,¼º¸í,´ã´ç¾÷¹«(¼Ò¹®ÀÚ·Î),ºÎ¼­¹øÈ£¸¦ Ãâ·ÂÇÏ¿©¶ó. SELECT empno,ename,LOWER(job),deptno FROM emp WHERE UPPER(ename)='SCOTT'; ¹®Á¦2) DEPT Å×ÀÌºí¿¡¼­ ¸ðµç ºÎ¼­ÀÇ ºÎ¼­¸í,À§Ä¡¸¦ ù ±ÛÀڵ鸸 ´ë¹®ÀÚ·Î º¯È¯ÇÏ¿© Ãâ·ÂÇÏ¿©¶ó. select initcap(dname),initcap(loc) from dept; ¹®Á¦3) EMP Å×ÀÌºí¿¡¼­ À̸§ÀÇ Ã¹±ÛÀÚ°¡ ¡®K¡¯ º¸´Ù Å©°í ¡®Y¡¯º¸´Ù ÀÛÀº »ç¿øÀÇ »ç¿ø¹øÈ£, À̸§, ¾÷¹«, ±Þ¿©, ºÎ¼­¹øÈ£¸¦ Ãâ·ÂÇÏ¿©¶ó. ´Ü À̸§¼øÀ¸·Î Á¤·ÄÇÏ¿©¶ó. select empno,ename,job,sal,deptno,substr(ename,1,1) from emp where substr(ename,1,1) >'K' and substr(ename,1,1)<'Y' order by ename ; ¹®Á¦4) EMP Å×ÀÌºí¿¡¼­ ºÎ¼­°¡ 20¹øÀÎ »ç¿øÀÇ »ç¿ø¹øÈ£, À̸§, À̸§ÀÇ ÀÚ¸´¼ö, ±Þ¿©, ±Þ¿©ÀÇ ÀÚ¸´¼ö¸¦ Ãâ·ÂÇÏ¿©¶ó. select empno,ename,length(ename),sal,length(sal) from emp where deptno=20 ¹®Á¦5) EMP Å×ÀÌºí¿¡¼­ ±Þ¿©¸¦ 30À¸·Î ³ª´« ³ª¸ÓÁö¸¦ ±¸ÇÏ¿© Ãâ·ÂÇÏ¿©¶ó. select ename,sal,mod(sal,30) from emp; ¹®Á¦6) EMP Å×ÀÌºí¿¡¼­ ÇöÀç±îÁöÀÇ ±Ù¹«Àϼö°¡ ¸î ÁÖ ¸î ÀÏÀΰ¡¸¦ Ãâ·ÂÇÏ¿©¶ó. ´Ü ±Ù¹« Àϼö°¡ ¸¹Àº »ç¶÷¼øÀ¸·Î Ãâ·ÂÇÏ¿©¶ó. select ename,trunc((sysdate-hiredate)) "Ãѱٹ«Àϼö",trunc(trunc((sysdate-hiredate))/7 )"Ãѱٹ«ÁÖ¼ö",trunc(mod(trunc(sysdate-hiredate),7)) "³ª¸ÓÁöÀϼö" from emp order by "Ãѱٹ«Àϼö" desc ¹®Á¦7) EMP Å×ÀÌºí¿¡¼­ 10¹ø ºÎ¼­¿øÀÇ ÇöÀç±îÁöÀÇ ±Ù¹« ¿ù¼ö¸¦ °è»êÇÏ¿© Ãâ·ÂÇÏ¿©¶ó. select ename,deptno,months_between(sysdate,hiredate) "±Ù¹«°³¿ù¼ö" from emp where deptno=10; ¹®Á¦8) EMP Å×ÀÌºí¿¡¼­ 10¹ø ºÎ¼­¿øÀÇ ÀÔ»ç ÀÏÀڷκÎÅÍ 5°³¿ùÀÌ Áö³­ ÈÄ ³¯Â¥¸¦ °è»êÇÏ¿© Ãâ·ÂÇÏ¿©¶ó. select ename,add_months(hiredate,5) from emp where deptno=10; ¹®Á¦9) EMP Å×ÀÌºí¿¡¼­ 10¹ø ºÎ¼­¿øÀÇ ÀÔ»ç ÀÏÀڷκÎÅÍ µ¹¾Æ¿À´Â ±Ý¿äÀÏÀ» °è»êÇÏ¿© Ãâ·ÂÇÏ¿©¶ó. select ename,next_day(hiredate,'±Ý') from emp where deptno=10; ¹®Á¦10) EMP Å×ÀÌºí¿¡¼­ ÀÔ»çÇÑ ´ÞÀÇ ±Ù¹« Àϼö¸¦ °è»êÇÏ¿© Ãâ·ÂÇÏ¿©¶ó. ´Ü Åä¿äÀÏ°ú ÀÏ¿äÀϵµ ±Ù¹« Àϼö¿¡ Æ÷ÇÔÇÑ´Ù. select ename,last_day(hiredate)-hiredate from emp;