1. SQL 문의종류 2 DDL (Data Definition Language) : 데이터와그구조를정의 SQL문 CREATE DROP ALTER 내용데이터베이스객체를생성데이터베이스객체를삭제기존에존재하는데이터베이스객체를다시정의하는역할 DML (Data Manipulati

Transcription

1 Oracle SQL 1

2 1. SQL 문의종류 2 DDL (Data Definition Language) : 데이터와그구조를정의 SQL문 CREATE DROP ALTER 내용데이터베이스객체를생성데이터베이스객체를삭제기존에존재하는데이터베이스객체를다시정의하는역할 DML (Data Manipulation Language) : 데이터의검색과수정등의처리 SQL 문 INSERT DELETE UPDATE SELECT 내용데이터베이스객체에데이터를입력데이터베이스객체에데이터를삭제기존에존재하는데이터베이스객체안의데이터수정데이터베이스객체로부터데이터를검색 DCL (Data Control Language) : 데이터베이스사용자의권한을제어 GRANT REVOKE SQL 문 내용 데이터베이스객체에권한을부여 이미부여된데이터베이스객체의권한을취소

3 2. 테이블의생성과수정그리고삭제 - 테이블의생성 3 테이블이란? 1. 테이블은오라클데이타베이스의기본적인데이타저장단위 2. 데이타베이스테이블은사용자가접근가능한모든데이타를보유하며레코드와컬럼으로구성 3. 테이블은시스템내에서독립적으로사용되길원하는엔티티를표현예를들면, 회사에서의고용자나제품에대한주문은테이블로표현가능 4. 테이블은두엔티티간의관계를표현즉테이블은고용자와그들의작업숙련도혹은제품과주문과의관계를표현하는데사용테이블내에있는외래키 (ForeIgn Key) 는두엔티티사이의관계를표현하는데사용 5. 비록 " 테이블 " 이라는말이더많이사용되지만테이블의형식어는 " 릴레이션 " 컬럼 : 테이블의각컬럼은엔티티의한속성을표현행 (ROW, 레코드 ) : 테이블의데이타는행에저장 테이블생성시제한사항과고려할점 - 테이블이름과컬럼은항상알파벳문자로시작해야하며 A~Z 까지의문자, 0~9 까지의숫자, 그리고 $,#,_(Under Bar) 를사용가능 ( 공백사용불가능 ) - 테이블의컬럼이름은 30 자를초과할수없고, 예약어를사용할수없음. - 오라클테이블한계정안에서테이블이름은다른테이블이름과달리유사해야함. - 한테이블안에서컬럼이름은같을수없으며다른테이블에서의컬럼이름과는같을수있음.

4 2. 테이블의생성과수정그리고삭제 - 테이블의생성 4 테이블생성문법 CREATE TABLE [schema.]table_name ( column datatype [, column datatype...] ) [TABLESPACE tablespace ] [ PCTFREE integer ] [ PCTUSED integer ] [ INITRANS integer ] [ MAXTRANS integer ] [ STORAGE storage-clause ] [ LOGGING NOLOGGING ] [ CACHE NOCACHE ] ; - schema : 테이블의소유자 - table_name: 테이블이름 - column: 컬럼의이름 - datatype: 컬럼의데이터유형 - TABLESPACE: 테이블이데이터를저장할테이블스페이스를지정 - PCTFREE : 블럭내에이미존재하고있는 Row에 Update가가능하도록예약시켜놓는블럭의퍼센트값을지정 - PCTUSED : 테이블데이터가저장될블록의행데이터부분의크기를퍼센트지로지정 PCTFREE에의해지정된크기만큼 Block이차면 PCTUSED 값보다작아져야새로운행삽입이가능 - INITRANS : 하나의데이터블록에지정될초기트랜잭션의값을지정 ( 기본값은 1) - MAXTRANS: 하나의데이터블록에지정될수있는트랜잭션최대수를지정 ( 기본값은 255) - STORAGE: 익스텐트스토리지에대한값을지정합니다. - LOGGING: 테이블에대해이후의모든작업이리두로그파일내에기록되도록지정 (default) - NOLOGGING: 리두로그파일에테이블의생성과특정유형의데이터로드를기록하지않도록지정 - CACHE : 전체테이블스캔 (full table scan) 이수행될때읽어들인블록이버퍼캐쉬내의 LRU 리스트의가장최근에사용된것의자리에위치하도록지정 - NOCACHE : 전체테이블스캔 (full table scan) 이수행될때읽어들인블록이버퍼캐쉬내의 LRU 리스트의가장최근에사용되지않은것의자리에위치하도록지정 * PCTFREE, PCTUSED 에대한자세한강좌는오라클어드민강좌를참고

5 2. 테이블의생성과수정그리고삭제 - 테이블의생성 5 테이블생성예제 DEPT2 테이블생성예제 SQL>CREATE TABLE DEPT2( DEPTNO NUMBER CONSTRAINT dept_pk_deptno PRIMARY KEY, - ( 컬럼 ) ( 데이터타입 ) ( 제약조건 ) DNAME VARCHAR2(40), LOC VARCHAR2(50)) PCTFREE 20 PCTUSED 50 ; 테이블이생성 테이블생성시주의사항 - 테이블이름을지정하고각컬럼들은괄호 "()" 로묶어지정 - 컬럼뒤에데이터타입은꼭지정되어야함. - 각컬럼들은콤마 "," 로구분되고, 항상끝은세미콜론 ";" 으로끝남. - 한테이블안에서컬럼이름은같을수없으며다른테이블에서의컬럼이름과는같을수있음. 유저가소유한모든테이블조회 USER_TABLES 데이터사전을조회하면유저가소유한테이블을확인할수있음. SQL>SELECT table_name FROM USER_TABLES; TABLE_NAME BONUS CRETABLE

6 2. 테이블의생성과수정그리고삭제 - 테이블의제약조건 6 제약조건이란? 제약조건이란테이블에부적절한자료가입력되는것을방지하기위해서여러가지규칙을적용해놓는것. 간단하게테이블안에서데이터의성격을정의하는것 - 데이터의무결성유지를위하여사용자가지정할수있는성질 - 모든 CONSTRAINT 는데이터사전 (DICTIONARY) 에저장 - 의미있는이름을부여했다면 CONSTRAINT 를쉽게참조할수있음. - 표준객체명명법을따르는것이좋다. - 제약조건은테이블을생성할당시에지정할수도있고, 테이블생성후구조변경 (ALTER) 명령어를통해서도추가가가능 - NOT NULL 제약조건은반드시컬럼레벨에서만정의가가능 제약조건 PRIMARY KEY(PK) FOREIGN KEY(FK) UNIQUE key(uk) NOT NULL(NN) CHECK(CK) 설 유일하게테이블의각행을식별 (NOT NULL 과 UNIQUE 조건을만족 ) 열과참조된열사이의외래키관계를적용하고설정합니다. 테이블의모든행을유일하게하는값을가진열 (NULL 을허용 ) 명 열은 NULL 값을포함할수없습니다. 참이어야하는조건을지정함 ( 대부분업무규칙을설정 )

7 2. 테이블의생성과수정그리고삭제 - 테이블의제약조건 7 NOT NULL 조건 : 컬럼을필수필드화시킬때사용 SQL> CREATE TABLE emp( ename VARCHAR2(20) CONSTRAINT emp_nn_ename NOT NULL ); 위와같이테이블을생성하면 ename 컬럼에는꼭데이터를입력해야만함. - 여기서 emp_nn_ename 은 ( 테이블이름 _ 제약조건이름 _ 컬럼이름 ) 형식으로 CONSTRAINT NAME 을정의 - CONSTRAINT NAME 은 USER_CONSTRAINTS 뷰 (VIEW) 를통해서확인 SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME ='EMP' ; CONSTRAINT_NAME emp_nn_ename à 이런식으로제약조건의이름을확인 UNIQUE 조건 UNIQUE 조건 : 데이터의유일성을보장 ( 중복되는데이터가존재할수없음.) 유니크조건을생성하면자동으로 index 가생성 SQL> ALTER TABLE emp ADD CONSTRAINT emp_uk_deptno UNIQUE (deptno) ; 테이블이변경 - 위와같이제약조건을생성하면 deptno 컬럼에중복된데이터가들어갈수없음. -- 제약조건의삭제 SQL>ALTER TABLE emp DROP CONSTRAINT emp_uk_deptno ; 테이블이변경

8 2. 테이블의생성과수정그리고삭제 - 테이블의제약조건 8 CHECK 조건 : 컬럼의값을어떤특정범위로제한 SQL>ALTER TABLE emp ADD CONSTRAINT emp_ck_comm CHECK (comm >= 10 AND comm <= ) ; 테이블이변경 - comm 컬럼은체크조건에서제한을하고있으므로 10 에서 까지의값만을가질수있음. 또체크조건에서는 IN 연산자를사용 SQL>ALTER TABLE emp DROP CONSTRAINT emp_ck_comm ; 테이블이변경 -- 제약조건의삭제 SQL> ALTER TABLE emp -- IN 연산자를이용해서 CHECK 제약조건을생성 ADD CONSTRAINT emp_ck_comm CHECK (comm IN (10000,20000,30000,40000,50000)) ; 테이블이변경 - comm 컬럼은 10000,20000,30000,40000,50000 의값만을가질수있음. DEFAULT( 컬럼기본값 ) 지정 데이터입력시에입력을하지않아도지정된값이디폴트로입력 SQL>CREATE TABLE emp( hiredate DATE DEFAULT SYSDATE ) ; 위와같이디폴트값을설정하면 hiredate 컬럼에 INSERT 를하지않아도오늘날짜가들어감.

9 2. 테이블의생성과수정그리고삭제 - 테이블의제약조건 9 PRIMARY KEY( 기본키 ) 지정 PRIMARY KEY : Primary Key( 기본키 ) 는 UNIQUE 와 NOT NULL 의결합과같다. - 기본키는그데이터행을대표하는컬럼으로서의역할을수행하여다른테이블에서외래키들이참조할수있는키로서의자격을가지고있으며이를참조무결성이라함. - UNIQUE 조건과마찬가지로기본키를정의하면자동으로인덱스를생성하며그이름은기본키제약조건의이름과같다. SQL>CREATE TABLE emp( empno NUMBER CONSTRAINT emp_pk_empno PRIMARY KEY ) ; 위와같이제약조건을설정하면 empno 컬럼에 UNIQUE 제약조건과 NOT NULL 제약조건을가지게된다. FOREIGN KEY( 외래키 ) 지정 FOREIGN KEY( 외래키 ) 지정 : 기본키를참조하는컬럼또는컬럼들의집합 - 외래키를가지는컬럼의데이터형은외뢰키가참조하는기본키의컬럼과데이터형이일치해야함. 이를어기면참조무결성제약에의해테이블을생성할수없음. - 외래키에의해참조되고있는기본키는삭제할수없음. - ON DELETE CASCADE 연산자와함께정의된외래키의데이터는그기본키가삭제될때같이삭제 SQL>ALTER TABLE emp ADD CONSTRAINT emp_fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) 테이블이변경 위와같이제약조건을생성하면 emp 테이블의 deptno 컬럼은 dept 테이블에 deptno 컬럼을참조하는외래키를가짐.

10 2. 테이블의생성과수정그리고삭제 - 오라클데이터타입 10 오라클테이블을생성할때각컬럼에지정할수있는데이터타입 DATA TYPE 설 명 VARCHAR2(n) 가변길이문자데이터 (1~4000byte) CHAR(n) 고정길이문자데이터 (1~2000byte) NUMBER(p,s) 전체 p자리중소수점이하 s자리 (p:1~38, s:-84~127) DATE 7Byte(BC 4712년 1월 1일부터 AD 9999년 12월 31일 ) LONG 가변길이문자데이터 (1~2Gbyte) CLOB 단일바이트가변길이문자데이터 (1~4Gbyte) RAW(n) n Byte의원시이진데이터 (1~2000) LONG RAW 가변길이원시이진데이터 (1~2Gbyte) BLOB 가변길이이진데이터 (1~4Gbyte) BFILE 가변길이외부파일에저장된이진데이터 (1~4Gbyte) 숫자데이터 (Numeric Data) - 오라클데이터베이스에서숫자는항상가변길이데이터로저장되며유효자릿수 38 자리까지저장.

11 2. 테이블의생성과수정그리고삭제 - 오라클데이터타입 11 문자데이터 (Character Data) - 문자데이터는데이터베이스에고정길이, 또는가변길이문자열로저장 - CHAR 와 NCHAR 같은고정길이문자데이터유형은고정길이까지공백으로채워서저장 - NCHAR 는고정폭 (fixed-width), 또는가변폭 (variable-width) character set 의저장을가능하게하는 NLS 데이터유형최대크기는한문자를저장하는데필요한바이트수에따라결정되며한행당최대 2000 바이트기본값은 charater set 에따라 1 문자, 또는 1 바이트 - 가변길이문자데이터유형은실제컬럼값을저장하는데필요한바이트만큼만을사용하며각행에따라크기가다양. VARCHAR2와 NVARCHAR2 가있습니다. 날짜 (DATE) 데이터유형 - 오라클서버는날짜를 7 바이트, 고정길이필드 (field) 로저장 - 오라클 DATE 는항상시간을포함 RAW 데이터유형 - 크기가적은이진데이터의저장에사용

12 2. 테이블의생성과수정그리고삭제 - 오라클데이터타입 12 크기가큰오브젝트를저장하기위한데이터유형 1 크기가큰오브젝트를저장하기위한데이터유형에는 LONG 과 LONG RAW, LOB 데이터유형이있다. 2 LONG 데이터유형은 2GB 의문자열데이터를저장 3 오라클은 LOB 을저장하기위한여섯가지데이터유형을제공 - 큰고정폭 (fixed-width) 문자데이터를위한 CLOB과 LONG - 큰고정폭국가 character set 데이터를위한 NCLOB - 구조화되지않은데이터를저장하기위한 BLOB과 LONG RAW - 구조화되지않은데이터를운영체제파일에저장하기위한 BFILE LONG 과 LOB 데이터유형비교 LONG, LONG RAW 테이블에컬럼하나만생성할수있음 2GB SELECT결과로데이터를리턴데이터를직접저장오브젝트유형을지원하지않음 LOB 테이블에여러개의컬럼생성이가능 4GB SELECT결과로위치를리턴데이터를직접또는간접저장오브젝트유형지원

13 2. 테이블의생성과수정그리고삭제 - 테이블의관리 13 테이블컬럼의관리 테이블의컬럼은 ADD, MODIFY, DROP 연산자를통해서관리할수있음. ADD 연산자 ADD 연산자 : 테이블에새로운컬럼을추가할때사용 SQL>ALTER TABLE emp ADD (addr VARCHAR2(50)); VARCHAR2 의데이터형을가지는 addr 컬럼이 emp 테이블에추가 MODIFY 연산자 MODIFY 연산자 : 테이블의컬럼을수정하거나 NOT NULL 컬럼으로변경할수있음. SQL>ALTER TABLE emp MODIFY (ename VARCHAR2(50) NOT NULL) ; -> ename 컬럼이 VARCHAR2 50 자리로수정 컬럼이이미테이터를가지고있을경우다른데이터형으로변경불가능 DROP 연산자 컬럼의삭제예제 SQL>ALTER TABLE table_name DROP COLUMN column_name -- 컬럼의삭제는오라클 8i 버전부터지원 제약조건의삭제예제 -- CASCADE 연산자와함께사용하면외래키에의해참조되는기본키도삭제될수있음 SQL>ALTER TABLE emp DROP CONSTRAINT emp_pk_empno CASCADE;

14 2. 테이블의생성과수정그리고삭제 - 테이블의관리 14 테이블정보의관리 기존테이블의복사 - 기존테이블을부분, 또는완전히복사할때에서브쿼리를가진 CREATE TABLE 명령어를사용해서쉽게테이블을복사가능 - 하지만제약조건, 트리거, 그리고테이블권한은새로운테이블로복사되지않는다. - 제약조건은 NOT NULL 제약조건만복사 [ 문법 ] [ 예제 ] CREATE TABLE [schema.]table_name SQL>CREATE TABLE emp2 [ LOGGING NOLOGGING ] AS [... ] SELECT * FROM emp; AS 테이블이생성 subquery 테이블의테이블스페이스변경 Oracle8i 이전버전에서는 export 를해서다시 import 를해야지만테이블스페이스를변경할수있었지만오라클 8i 부터는 ALTER TABLE ~ MOVE TABLESPACE 명령어로쉽게테이블의테이블스페이스를변경할수있음. ALTER TABLE table_name MOVE TABLESPACE tablespace_name; SQL>ALTER TABLE emp MOVE TABLESPACE test; 테이블이변경 àemp 테이블의테이블스페이스를 test 로변경

15 2. 테이블의생성과수정그리고삭제 - 테이블의관리 15 테이블정보의변경 - 보통테이블의정보를변경하는이유는스토리지파라미터와블록활용파라미터를변경하기위해서사용 - 테이블정보의변경시 INITIAL 의값은변경할수없음. ALTER TABLE [schema.]table_name [ STORAGE storage-clause ] [ PCTFREE integer ] [ PCTUSED integer ] [ INITRANS integer ] [ MAXTRANS integer ] STORAGE-CLAUSE 에들어올수있는스토리지파라미터 - NEXT 다음번생성될익스텐트의크기를 Byte 단위로지정합니다. 이후, 익스텐트크기는 PCTINCREASE 만큼씩증가 - PCTINCREASE 마지막생성된익스텐트의바로다음에생성될익스텐트의증가율을퍼센트지로지정 - MINEXTENTS 최초생성되는익스텐트의수를지정 - MAXEXTENTS 생성될수있는최대익스텐트의수를지정 기타블록관련파라미터는테이블의생성강좌를참고

16 2. 테이블의생성과수정그리고삭제 - 테이블의관리 16 테이블의 TRUNCATE - 테이블을 Truncate 하면테이블의모든행이삭제되고사용된공간이해제 - TRUNCATE TABLE 은 DDL 명령이므로롤백데이터가생성되지않음. DELETE 명령으로데이터를지우면롤백명렁어로복구할수있지만 TRUNCATE 로데이터를삭제하면롤백을할수가없음. - 행의인덱스도같이잘려나감. - 외래키가참조중인테이블은 TRUNCATE 할수없음. - TRUNCATE 명령을사용하면삭제트리거가실행되지않음. TRUNCATE TABLE [schema.]table_name ; DROP TABLE ( 테이블을삭제할때사용 ) DROP TABLE [schema.]table_name [CASCADE CONSTRAINTS] ; SQL>DROP TABLE emp ; SQL>DROP TABLE emp CASCADE CONSTRAINT; CASCADE CONSTRAINT è 외래키에의해참조되는기본키를포함한테이블일경우기본키를참조하던외래키조건도같이삭제

17 3. 데이터조작어 (DML) - 데이터의삽입, 수정, 삭제 17 INSERT INSERT 명령어는테이블안에데이터를삽입하는역할 INSERT INTO table_name(column1, column2, ) VALUES ( 데이터, 데이터, ) ; - 실제데이터는 VALUES 괄호 () 안에입력하고문자열은단일따옴표 (' ') 로둘러싼다. - 각각의테이터는 "," 로구분 - 테이블이름옆에 () 생략시에는모든컬럼을 VALUES() 안에입력 모든데이터를입력할경우 SQL>INSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902, TO_DATE('80/12/17'), 800, NULL, 20); 원하는데이터만입력할경우 SQL>INSERT INTO DEPT (DEPTNO, DNAME) VALUES(10, 'ACCOUNTING' ); SELECT 문장을이용한 INSERT INSERT INTO table_name(column1, column2, ) SELECT column1, column2, FROM table_name WHERE 조건 ; SQL>INSERT INTO DEPT SELECT * FROM SCOTT.DEPT ;

18 3. 데이터조작어 (DML) - 데이터의삽입, 수정, 삭제 18 UPDATE UPDATE 명령어는테이블안의데이터를수정 UPDATE table_name SET column1 = 값 ( 고칠내용 ), column2 = 값, WHERE 조건 SQL>UPDATE EMP SET DEPTNO = 30 WHERE EMPNO = 7902 ; è 사원번호가 7902 번인사람의부서번호가 30 번으로수정됨 SQL>UPDATE EMP SET SAL = SAL * 1.1 WHERE DEPTNO = 20 ; è 20 부서의사원들의급여가 10% 인상됨 SQL>UPDATE EMP SET HIREDATE = SYSDATE è 모든사원의입사일이오늘로수정됨 DELETE DELETE 명령어는테이블안의데이터를삭제 DELETE FROM table_name WHERE 조건 ; SQL>DELETE FROM EMP WHERE EMPNO = 7902 ; è 사원번호가 7902 번인사람의데이터가삭제 SQL>DELETE FROM EMP WHERE SAL < (SELECT AVG(SAL) FROM EMP) ; è 평균급여보다적게받는사원삭제 SQL>DELETE FROM EMP ; è 테이블의모든행이삭제

19 3. 데이터조작어 (DML) - SELECT 문및연산자 19 SELECT 문 SELECT 문은데이터베이스로부터저장되어있는데이터를검색하는데사용 SELECT [DISTINCT] {*, column [alias],...} FROM table_name [WHERE condition] [ORDER BY {column, expression} [ASC DESC]]; - DISTINCT : 중복되는행을제거하는옵션 - * : 테이블의모든 column을출력 - alias : 해당 column에대해서다른이름을부여할때사용 - table_name : 질의대상테이블명 - WHERE : 조건을만족하는행들만검색 - condition : column, 표현식, 상수및비교연산자 - ORDER BY : 질의결과정렬을위한옵션 (ASC: 오름차순 (Default),DESC내림차순) SQL 문의작성방법 - SQL 문장은대소문자를구별하지않음. - SQL 문장은한줄또는여러줄에입력 - 일반적으로키워드는대문자로입력. 다른모든단어, 즉테이블이름, 열이름은소문자로입력 ( 권장 ) - 가장최근의명령어가 1 개가 SQL buffer 에저장 - SQL 문마지막절의끝에 ";" 를기술하여명령의끝을표시 [ 예제 ] SQL>SELECT empno 사번, ename 성명 FROM emp WHERE deptno = 10

20 3. 데이터조작어 (DML) - SELECT 문및연산자 20 WHERE 절에올수있는연산자들. 연산자설명 BETWEEN a AND b a 와 b 사이의데이터를출력 (a, b 값포함 ) IN (list) list 의값중어느하나와일치하는데이터를출력 LIKE 문자형태로일치하는데이터를출력 (%, _ 사용 ) IS NULL NULL 값을가진데이터를출력 NOT BETWEEN a AND b a 와 b 사이에있지않은데이터를출력 (a, b 값포함하지않음 ) NOT IN (list) NOT LIKE NOT IS NULL IN 과 NOT IN 연산자 IN 연산자 list 의값과일치하지않는데이터를출력 문자형태와일치하지않는데이터를출력 NULL 값을갖지않는데이터를출력 SQL> SELECT empno, ename FROM emp WHERE empno IN (7900, 7934) ; --> 사번이 7900, 7934 번인사원의사번과성명출력 NOT IN 연산자 SQL> SELECT empno, ename FROM emp WHERE empno NOT IN (7900, 7934); --> 사번이 7900, 7934 번이아닌사원의사번과성명출력

21 3. 데이터조작어 (DML) - SELECT 문및연산자 21 BETWEEN 연산자 SQL> SELECT empno, ename FROM emp WHERE sal BETWEEN 3000 AND 5000 ; --> 급여가 3000 에서 5000 사이인사원만보여줌. BETWEEN 연산자 (AND 를이용해두조건을결합한검색과같은결과값을보여줌.) LIKE 연산자 - 검색 STRING 값에대한와일드카드검색을위해서 LIKE연산자를사용 - % : 여러개의문자열을나타내는와일드카드 - _ : 단하나의문자를나타내는와일드카드 - ESCAPE : 와일드카드문자를일반문자처럼사용하고싶은경우에사용 WHERE name LIKE '%a\_y%' ESCAPE '\' ; - LIKE 연산자는대소문자를구분 - Upper() 함수를이용해대소문자구분없이출력 [ 예제 ] SQL>SELECT empno, ename FROM emp WHERE UPPER(ename) like '%K%'; 'K' 문자가들어있는사원정보를보여줌. upper() 라는함수는 k 가들어가있는것도대문자 'K' 로인식하기때문에데이터들을보여줌. '_' 를이용한 LIKE 검색 SQL>SELECT empno, ename FROM emp WHERE UPPER(ename) like '_I%' '_' 는한문자를나타냄. 'I' 문자가두번째문자에위치한사원들의정보를보여줌.

22 3. 데이터조작어 (DML) - 예명 (Alias) 22 테이블예명 (Alias) 테이블예명 (Alias) - 테이블 alias 로 column 을단순, 명확히할수있음. - 현재의 SELECT 문장에대해서만유효 - 테이블 alias 는길이가 30 자까지가능하나짧을수록더욱좋다. - 테이블 alias 는의미가있어야함. - FROM 절에테이블 alias 설정시해당테이블 alias 는 SELECT 문장에서테이블이름대신에사용 SQL> SELECT a.dname, b.cnt FROM dept a, (SELECT deptno, COUNT(empno) cnt FROM emp GROUP BY deptno) b WHERE a.deptno = b.deptno AND b.cnt > 3 DNAME CNT RESEARCH 6 SALES 6 사원수가 3 명이넘는부서의부서명과사원수를보여줌. 위쿼리에선총 3 개의 Alias 가사용첫번째로 DEPT 테이블을 a 라는예명을, 두번째로부서의사원수인 COUNT(empno) 를 cnt 라는예명을세번째로부서별사원수를가져오는쿼리를 b 라는예명을줌. 위예제와같이예명은컬럼에만주는것이아니라. 쿼리문및테이블에도사용할수있음.

23 3. 데이터조작어 (DML) - 조인 (Join) 23 조인 (Join)? 조인 (Join) 이란 - 둘이상의테이블을연결하여데이터를검색하는방법 - 보통둘이상의행들의공통된값 Primary Key 및 Foreign Key 값을사용하여조인 - 그러므로두개의테이블을 SELECT 문장안에서조인하려면적어도하나의컬럼이그두테이블사이에서공유되어야함. 조인방법 - Equijoin( 동등조인, 내부조인 ) - Non-equijoin - Outer join - Self join Cartesian Product( 카티션곱 ) 검색하고자했던데이터뿐아니라조인에사용된테이블들의모든데이터가 Retrun 되는현상 Cartesian product 는다음과같은경우에발생 - 조인조건을정의하지않았을경우 - 조인조건이잘못된경우 - 첫번째테이블의모든행들이두번째테이블의모든행과조인이되는경우 - 테이블의개수가 N 이라면 Cartesian product 를피하기위해서는적어도 N-1 개의등가조건을 SELECT 문안에포함시켜서다른테이블안에있는각테이블의컬럼이적어도한번은참조되도록해야함.

24 3. 데이터조작어 (DML) - 조인 (Join) 24 Equi Join Equi Join - 조건절 Equality Condition(=) 에의하여조인이이루어짐. - Equi join 의성능을높이기위해서는 Index 기능을사용하는것이좋다. SQL>SELECT e.ename, d.dname è WHERE 절에조인조건을작성하고 column 명앞에테이블명을적음. FROM emp e, dept d WHERE e.deptno = d.deptno; Non-Equijoin Non-Equijoin - Non-equijoin 은테이블의어떤 column 도 join 할테이블의 column 에일치하지않을때사용하고조인조건은동등 ( = ) 이외의연산자를갖는다. (BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN) SQL>SELECT e.ename, d.dname FROM emp e, dept d WHERE e.sal BETWEEN 3000 AND 4000; Self Join Self Join - Equi Join 과같으나하나의테이블에서조인이일어나는것이다름. - 같은테이블에대해두개의 alias 를작성함으로 FROM 절에두개의테이블을사용 SQL> SELECT concat(a.ename,' ') ' : \' b.sal 급여 FROM emp a, emp b WHERE a.empno = b.empno

25 3. 데이터조작어 (DML) - 조인 (Join) 25 Out( 외부 ) Join Out( 외부 ) Join - equijoin 문장들의한가지제약점은그것들이조인을생성하려하는두개의테이블의두개컬럼에서공통된값이없다면테이블로부터테이터를 Return 하지않는다. - 정상적으로조인조건을만족하지못하는행들을보기위해 outer join 을사용 Outer join 연산자 "( + )" - 조인시킬값이없는조인측에 "(+)" 를위치시킨다. - Outer join 연산자는표현식의한편에만올수있다. - Outer join 은 IN 연산자를사용할수없고 OR 연산자에의해다른하나의조건에연결될수없다. 예제 1) 일반조인의경우 SQL> SELECT DISTINCT(a.deptno), b.deptno FROM emp a, dept b WHERE a.deptno = b.deptno DEPTNO DEPTNO 예제 2) out join 을했을경우 SQL> SELECT DISTINCT(a.deptno), b.deptno FROM emp a, dept b WHERE a.deptno(+) = b.deptno DEPTNO DEPTNO

26 3. 데이터조작어 (DML) - 트랜잭션 (commit 과 rollback) 26 데이터베이스 TRANSACTION 데이터베이스 TRANSACTION 트랜잭션은데이터처리의한단위 오라클서버에서발생하는 SQL 문들을하나의논리적인작업단위로써성공하거나실패하는일련의 SQL 문 ORACLE SERVER 는 TRANSACTION 을근거로데이터의일관성을보증 TRANSACTION 은데이터를일관되게변경하는 DML 문장으로구성 (COMMIT, ROLLBACK, SAVEPOINT) 1 TRANSACTION 의시작 - 실행가능한 SQL 문장이제일처음실행될때 2 TRANSACTION 의종료 - COMMIT 이나 ROLLBACK - DDL 이나 DCL 문장의실행 ( 자동 COMMIT) - 기계장애또는시스템충돌 (crash) - deadlock 발생 - 사용자가정상종료 3 자동 COMMIT 은다음의경우발생 - DDL,DCL 문장이완료될때 - 명시적인 COMMIT 이나 ROLLBACK 없이 SQL*Plus 를정상종료했을경우 4 자동 ROLLBACK 은다음의경우발생 - SQL*Plus 를비정상종료했을경우 - 비정상적인종료, system failure

27 3. 데이터조작어 (DML) - 트랜잭션 (commit 과 rollback) 27 COMMIT 과 ROLLBACK COMMIT 과 ROLLBACK COMMIT : 변경사항저장 ROLLBACK : 변경사항취소 1 COMMIT 과 ROLLBACK 의장점 - 데이터의일관성을제공 - 데이터를영구적으로변경하기전에데이터변경을확인 - 관련된작업을논리적으로그룹화 - COMMIT, SAVEPOINT,ROLLBACK 문장으로 TRANSACTION 의논리를제어 2 COMMIT 이나 ROLLBACK 이전의데이터상태 - 데이터이전의상태로북구가가능 - 현재사용자는 SELECT 문장으로 DML 작업의결과를확인 - 다른사용자는 SELECT 문장으로현재사용자사용한 DML 문장의결과를확인할수없음. - 변경된행은 LOCK 이설정되어서다른사용자가변경할수없음. 3 COMMIT 이후의데이터상태 - 데이터베이스에데이터를영구적으로변경 - 데이터의이전상태는완전히상실 - 모든사용자가결과를볼수있다. - 변경된행의 LOCK 이해제되고다른사용자가변경할수있다. - 모든 SAVEPOINT 는제거

28 4. 내장함수 (Sing-Row Functions) - 숫자함수 (Number Functions) 28 ABS(n) : ABS 함수는절대값을계산하는함수 CEIL(n) : CEIL 함수는주어진값보다는크지만가장근접하는최소값을구하는함수 EXP(n) : EXP 함수는주어진값의 e 의승수. e = FLOOR(n) : FLOOR 함수는주어진값보다작거나같은최대정수값을구하는함수. (CEIL 함수와비교 ) LN(n) : LN 함수는주어진값의자연로그값을반환 MOD(m, n) : MOD 함수는 m 을 n 으로나누어남은값을반환. n 이 0 일경우 m 을반환 POWER(m, n) : POWER 함수는 m 의 n 승값을계산 ROUND(n, [m]) : ROUND 함수는 n 값의반올림을하는함수로 m 은소숫점아래자릿수를나타냄. SQL>SELECT ROUND( , 1) TEST FROM dual ; SQL>SELECT ROUND( , -1) TEST FROM dual ; TEST TEST SIGN(n) : SIGN 함수는 n<0 일경우 1 을 N=0 일경우 0 을 N>0 일경우 1 을반환 SQRT(n) : SQRT 함수는 n 값의루트값을계산. n 은양수 TRUNC(n, m) : TRUNC 함수는 n 값을 m 소숫점자리로반내림한값을반환. (ROUND 함수와비교 ) SQL>SELECT TRUNC(6.5597, 2) TEST FROM dual ; SQL>SELECT TRUNC ( , -2 ) TEST FROM dual ; TEST TEST

29 4. 내장함수 (Sing-Row Functions) - 문자열처리함수 (Character Functions) 29 CONCAT(char1, char2) CONCAT 함수는 Concatenation 의약자로두문자를결합하는역할. " " 연산자와같은역할을합니다. SQL>SELECT CONCAT('Oracle', ' Korea') NAME FROM dual ; NAME Oracle Korea INITCAP(char) : 주어진문자열의첫번째문자를대문자로변환 LOWER(char) : 문자열을소문자로변환 UPPER(char) : 문자열을대문자로변환 LPAD(char1, n [,char2]) : 왼쪽에문자열을끼어놓는역할 n 은반환되는문자열의전체길이를나타내며, char1 의문자열이 n 보다클경우 char1 을 n 개문자열만큼반환 SQL>SELECT LPAD('JUNG-SICK', 10, '*') NAME FROM dual ; NAME *JUNG-SICK RPAD(char1, n [,char2]) : LPAD 와반대로오른쪽에문자열을끼어놓는역할 SQL>SELECT RPAD('JUNG-SICK', 10, '*') NAME FROM dual ; NAME JUNG-SICK*

30 4. 내장함수 (Sing-Row Functions) - 문자열처리함수 (Character Functions) 30 SUBSTR(char, m,[n]) : SUBSTR 함수를이용하여 m 번째자리부터길이가 n 개인문자열을반환 m 이음수일경우에는뒤에서 M 번째문자부터반대방향으로 n 개의문자를반환 -- 뒤에서부터자를 SQL>SELECT SUBSTR('JUNG-SICK', 3, 3) NAME FROM dual ; SQL>SELECT SUBSTR('JUNG-SICK', -3, 3) NAME FROM dual ; NAME NAME NG- ICK LENGTH(char1) : 문자열의길이를리턴 SQL>SELECT LENGTH('JUNG-SICK') TEST FROM dual ; TEST REPLACE(char1, str1, str2) : REPLACE 는문자열의특정문자를다른문자로변환 SQL> SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL; Changes BLACK and BLUE -- 대소문자를구분한다는것을알수있다. SQL>SELECT REPLACE('JACK and JUE','j','BL') "Changes" FROM DUAL Changes JACK and JUE

31 4. 내장함수 (Sing-Row Functions) - 문자열처리함수 (Character Functions) 31 INSTR : 문자열이포함되어있는지를조사하여문자열의위치를반환지정한문자열이발견되지않으면 0 이반환 -- 지정한문자 OK 가발견되지않아서 0 이반환 SQL>SELECT INSTR('CORPORATE FLOOR','OK') "Instring" FROM DUAL Instring OR 이있는위치 2 를반환. 왼쪽부터비교를한다는것을알수있다. SQL>SELECT INSTR('CORPORATE FLOOR','OR') "Instring" FROM DUAL Instring TRIM : 특정한문자를제거제거할문자를입력하지않으면기본적으로공백이제거. 리턴값의데이터타입은 VARCHAR 을제거 SQL>SELECT TRIM(0 FROM ) "TRIM Example" FROM DUAL; TRIM Example 어떤문자도입력하지않으면기본적으로공백이제거 -- TRIM 을사용한위에예제와사용하지않은아래예제의결과값이다르게나오는것을알수있다. SQL>SELECT NVL(TRIM (' '),' 공백 ') "TRIM Example" FROM DUAL TRIM Example 공백

32 4. 내장함수 (Sing-Row Functions) - 날짜처리함수 (Date Functions) 32 LAST_DAY(d) : LAST_DAY 함수는달의마지막날의날짜를반환 SQL>SELECT SYSDATE TODAY, LAST_DAY(SYSDATE) LASTDAY FROM dual ; TODAY LASTDAY JUN JUN-2000 ADD_MONTHS(a, b) : ADD_MONTHS 함수는 a 의날짜에 b 의달을더한값을반환 SQL>SELECT TO_CAHR(ADD_MONTHS(SYSDATE,3),'RRRR/MM/DD' LASTDAY) "date" FROM dual ; date /09/05 MONTH_BETWEEN(a1, a2) : MONTH_BETWEEN 은 a1 과 a2 사이의달의수를 NUMBER 형타입으로반환 SQL>SELECT MONTHS_BETWEEN(TO_DATE('2000/06/05'), TO_DATE('2000/09/23')) "Date" FROM dual ; Date ROUND(d[,F]) : ROUND 함수는 F 에지정된단위로반올림합니다, F 가연도라면연도단위로반올림 SQL>SELECT ROUND(TO_DATE('1998/09/11'), 'YEAR') FROM dual ; ROUND(TO_

33 4. 내장함수 (Sing-Row Functions) - 변환함수 (Conversion Functions) 33 TO_CHAR : TO_CHAR 함수는 DATE 형, NUMBER 형을 VARCHAR2 형으로변환 SQL>SELECT TO_CHAR(SYSDATE, 'MONTH') CHARTEST FROM dual ; CHARTEST JUNE 오늘이 6 월 10 일이라면,.. 오늘의달인 6 월이찍힘 TO_DATE : TO_DATE 함수는 CHAR, VARCHAR2 형을 DATE 타입으로변환 SQL>SELECT TO_DATE('2000/06/16','RRRR/MM/DD') FROM dual ; TO_DATE( /06/16 '2000/06/16' 문자열이날짜형으로변환 TO_NUMBER : TO_NUMBER 함수는 CHAR, VARCHAR2 의데이터타입을숫자형식으로변환 SQL>SELECT TO_NUMBER(' ') FROM dual ; TO_NUMBER( ' ' 문자열이숫자형으로변환 * SQL Function 에대하여아래사이트참조.

34 4. 내장함수 (Sing-Row Functions) - 기타함수 (Miscellaneous Single-Row Functions) 34 NVL - NVL 함수는 NULL 값을다른값으로바꿀때쓰임. - 모든데이터타입에적용가능 - 전환되는값의데이터타입을일치시켜야함. SQL>SELECT empno, NVL(comm, 0) FROM emp WHERE deptno = 30; EMPNO NVL(COMM,0) Commsion 이없는사원에대해 0 으로바꾸어서출력 DECODE - DECODE 함수는데이터들을다른값으로바꾸어줌 - 형식 DECODE(VALUE, IF1, THEN1, IF2, THEN2...) - VALUE 값이 IF1 일경우에 THEN1 값으로바꾸어주고 VALUE 값이 IF2 일경우에는 THEN2 값으로바꾸어줌 SQL> SELECT deptno, DECODE(deptno, 10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'SALES', 40, 'OPERATIONS') FROM emp ; DEPTNO DECODE(DEP RESEARCH 30 SALES 10 ACCOUNTING 부서가 10 번이면 'ACCOUNTING' 를 20 번이면 'RESEARCH' 를 30 번이면 'SALES' 를 40 번이면 'OPERATIONS' 를출력하는예제

35 4. 내장함수 (Sing-Row Functions) - 기타함수 (Miscellaneous Single-Row Functions) 35 DUMP : DUMP 는바이트크기와해당데이터타입코드를반환 SQL>SELECT ename, DUMP(ename, 16) "16 진수 " FROM emp WHERE ename = 'ALLEN' ename 16진수 ALLEN Typ=1 Len=5: 41,4c,4c,45,4e 만약에 16 대신 8 을넣으면 8 진수로, 10 를넣으면 10 진수로변환 16, 10, 8, 17 이올수있는데, 17 은단일문자열을반환함. Len 은 ename 의해당 byte 수 GREATEST : GREATEST 함수는검색값중에서가장큰값을반환 SQL>SELECT GREATEST(10, 100, 5, -7) FROM DUAL; GREATEST(10,100,5,-7) 가장큰수 : 100 LEAST : LEAST 함수는 GREATEST 함수와반대로가장작은값을반환 SQL>SELECT LEAST(10, 100, 5, -7) FROM DUAL; LEAST(10,100,5,-7)

36 4. 내장함수 (Sing-Row Functions) - 기타함수 (Miscellaneous Single-Row Functions) 36 UID : 현재사용자의유일한 ID 번호를반환 USER : 현재오라클을사용하는사용자를 VARCHAR2 형식으로반환 SQL> SELECT USER, UID FROM DUAL; USER UID SCOTT 32 USERENV : USERENV 함수는현재세션의환경정보를반환 - ENTRYID : 사용가능한 Auditing entry Identifier를반환 - LABEL : 현재세션의 Label을반환 - LANGUAGE : 현재세션에서사용중인언어와테리토리값을반환 - SESSIONID : Auditing( 감사 ) Session ID 를반환 - TERMINAL : 현재세션터미널의 OS ID를반환 SQL> SELECT USERENV('LANGUAGE') FROM DUAL; USERENV('LANGUAGE') KOREAN_KOREA.KO16KSC5601 VSIZE : 해당문자의 BYTE 수를반환합니다. 해당문자가 NULL 이면 NULL 값이반환 SQL> SELECT VSIZE(ename), ename FROM emp WHERE deptno = 30; VSIZE(ENAME) ENAME ALLEN 4 WARD 6 MARTIN

37 5. 그룹함수 (Group Functions) - Group Function 의종류 37 그룹함수란? - 그룹함수란여러행또는테이블전체의행에대해함수가적용되어하나의결과값을가져오는함수 - GROUP BY 절을이용하여그룹당하나의결과가주어지도록그룹화할수있다. - HAVING 절을사용하여그룹함수를가지고조건비교를할수있다. - COUNT(*) 를제외한모든그룹함수는 NULL 값을고려하지않는다. - MIN, MAX 그룹함수는모든자료형에대해서사용할수있다. 그룹함수의종류 COUNT : COUNT 함수는검색된행의수를반환 SQL>SELECT COUNT(deptno) FROM DEPT ; COUNT(DEPTNO) ==> 검색된행의총수 4 개를반환. 즉 4 개의부서가존재 MAX : MAX 함수는컬럼중의최대값을반환 SQL>SELECT MAX(sal) salary FROM emp ; SALARY ==> sal 컬럼중에서제일큰값을반환. 즉가장큰급여를반환

38 5. 그룹함수 (Group Functions) - Group Function 의종류 38 MIN : MIN 함수는컬럼중의최소값을반환 SQL>SELECT MIN(sal) salary FROM emp ; SALARY ==> sal 컬럼중에서가장작은값반환. 즉가장적은급여를반환 AVG : AVG 함수는평균값을반환 SQL>SELECT ROUND(AVG(sal),1) salary FROM emp WHERE deptno = 30 SALARY ==> 30 부서사원의평균급여를소수점 1 자리이하에서반올림 SUM : SUM 함수는검색된컬럼의합을반환 SQL>SELECT SUM(sal) salary FROM emp WHERE deptno = 30; SALARY ==> 30 부서사원의급여합계를보여줌 STDDEV : STDDEV 함수는표준편차를반환 SQL> SELECT ROUND(STDDEV(sal),3) salary FROM emp WHERE deptno = 30 ; SALARY ==> 30 부서사원의급여표준편차를반환

39 5. 그룹함수 (Group Functions) - Group By 절과 Having 절 39 GROUP BY? - 특정한컬럼의테이터들을다른데이터들과비교해유일한값에따라무리를지음 - GROUP BY 절을사용하여한테이블의행들을원하는그룹으로나눔 - Column 명을 GROUP 함수와 SELECT 절에사용하고자하는경우 GROUP BY 뒤에 Column 명을추가 GROUP BY 예제 예제1) 부서별로그룹을지은검색결과값이며부서별로사원수를보여줌 SQL>SELECT b.deptno, COUNT(a.empno) FROM emp a, dept b WHERE a.deptno = b.deptno GROUP BY b.deptno DEPTNO COUNT(*) 예제 2) 업무별로그룹하여업무, 인원수, 평균급여액, 최고급여액, 최저급여액및합계를출력하라. SQL>SELECT job, COUNT(empno) " 인원수 ", AVG(sal) " 평균급여액 ", MAX(sal) " 최고급여액 ", MIN(sal) " 최저급여액 ", SUM(sal) " 급여합계 " FROM emp GROUP BY job JOB 인원수평균급여액최고급여액최저급여액급여합계 ANALYST CLERK MANAGER PRESIDENT SALESMAN

40 5. 그룹함수 (Group Functions) - Group By 절과 Having 절 40 GROUP BY 의 HAVING 절 - WHERE 절에 GROUP Function 을사용할수없음 - HAVING 절은 GRUOP 함수를가지고조건비교를할때사용 - WHERE GRUOP BY HAVING ORDER BY 순으로쿼리문이와야됨. 예제 1) 사원수가 5 명이넘는부서의부서명과사원수를출력해라 SQL>SELECT b.dname, COUNT(a.empno) FROM emp a, dept b WHERE a.deptno = b.deptno GROUP BY dname HAVING COUNT(a.empno) > 5 DNAME COUNT(A.EMPNO) RESEARCH 6 SALES 6 예제2) 전체월급이 5000을초과하는각업무에대해서업무와월급여합계를출력하여라. 단판매원은제외하고월급여합계로내림차순정렬하여라. SQL>SELECT job, SUM(sal) " 급여합계 " FROM emp WHERE job NOT IN ('SALES') -- 판매원은제외 GROUP BY job -- 업무별로 Group By HAVING SUM(sal) > 전체월급이 5000을초과하는 ORDER BY SUM(sal) DESC; -- 월급여합계로내림차순정렬 JOB 급여합계 MANAGER 8520 ANALYST 6000 SALESMAN 5600

41 6. 서브쿼리 (Subquery) - 서브쿼리 (Subquery) 란? 41 Subquery란? SUBQUERY는다른하나의 SQL 문장의절에 NESTEDED된 SELECT 문장 SELECT, UPDATE, DELETE, INSERT와같은 DML문과 CREATE TABLE 또는 VIEW에서이용될수있음 알려지지않은조건에근거한값들을검색하는 SELECT 문장을작성하는데유용 SUBQUERY는 MAIN QUERY가실행되기이전에한번실행 Guidelines SUBQUERY는괄호로묶어야함. SUBQUERY는연산자의오른쪽에나타냄 두종류의비교연산자들이 SUBQUERY에사용 - 단일행연산자 (=,>, >=, <, <=, <>,!=) - 복수행연산자 (IN, NOT IN, ANY, ALL, EXISTS) Guidelines 단일행 (Sing-Row) 서브쿼리 : SELECT문장으로부터오직하나의행만을검색하는질의 다중행 (Multiple-Row) 서브쿼리 : SELECT문장으로부터하나이상의행을검색하는질의 다중열 (Multiple-Column) 서브쿼리 : SELECT문장으로부터하나이상의컬럼을검색하는질의 FROM절상의서브쿼리 (INLINE VIEW) : FROM절상에오는서브쿼리로 VIEW처럼작용 상관관계서브쿼리 : 바깥쪽쿼리의컬럼중의하나가안쪽서브쿼리의조건에이용되는처리방식

42 6. 서브쿼리 (Subquery) - 단일행 (Sing-Row) 서브쿼리 42 단일행 (Sing-Row) 서브쿼리란? 오직한개의행 ( 값 ) 을반환 (Return only one row) 단일행연산자 (=,>, >=, <, <=, <>,!=) 만사용 SQL> SELECT ename,job FROM emp WHERE job = (SELECT job FROM emp WHERE empno = 7369); 위의서브쿼리를실행시키면. 오른쪽의그림처럼. empno 가 7369 인 job 을먼저검색하고.. job 이 'CLERK' 인사원의이름과직업을반환

43 6. 서브쿼리 (Subquery) - 다중행 (Multiple-Row) 서브쿼리 43 다중행 (Multiple-Row) 서브쿼리란? 하나이상의행을 RETURN 하는 SUBQUERY 를다중행 SUBQUERY 복수행연산자 (IN, NOT IN, ANY, ALL, EXISTS) 를사용 IN 연산자의사용예제 예제 ) 부서별로가장급여를많이받는사원의정보를출력하는예제 SQL>SELECT empno,ename,sal,deptno FROM emp WHERE sal IN (SELECT MAX(sal) FROM emp GROUP BY deptno); EMPNO ENAME SAL DEPTNO BLAKE SCOTT FORD KING ANY 연산자의사용예제 - ANY 연산자는서브쿼리의결과값중어느하나의값이라도만족이되면결과값을반환 SQL>SELECT ename, sal FROM emp WHERE deptno!= 20 AND sal > ANY(SELECT sal FROM emp WHERE job='salesman'); ENAME SAL ALLEN 1600 BLAKE 2850 CLARK 2450 KING 5000 TURNER 1500 MILLER 개의행이선택

44 6. 서브쿼리 (Subquery) - 다중행 (Multiple-Row) 서브쿼리 44 ALL 연산자의사용예제 - ALL 연산자는서브쿼리의결과값중모든결과값이만족되야만결과값을반환 SQL>SELECT ename, sal FROM emp WHERE deptno!= 20 AND sal > ALL(SELECT sal FROM emp WHERE job='salesman'); ENAME SAL BLAKE 2850 CLARK 2450 KING 개의행이선택 EXISTS 연산자의사용예제 - EXISTS 연산자를사용하면서브쿼리의데이터가존재하는가의여부를먼저따져존재하는값들만을결과로반환 - SUBQUERY 에서적어도 1 개의행을 RETURN 하면논리식은참이고그렇지않으면거짓 예제 ) 사원을관리할수있는사원의정보를보여줌 SELECT empno, ename, sal FROM emp e WHERE EXISTS (SELECT empno FROM emp WHERE e.empno = mgr) EMPNO ENAME SAL JONES BLAKE CLARK SCOTT KING FORD 개의행이선택

45 6. 서브쿼리 (Subquery) - 다중열 (Multiple-Column) 서브쿼리 45 다중열 (Multiple-Column) 서브쿼리란? 다중열서브쿼리란서브쿼리의결과값이두개이상의컬럼을반환하는서브쿼리 Pairwise( 쌍비교 ) Subquery 서브쿼리가한번실행되면서모든조건을검색해서주쿼리로넘겨줌 ex) SELECT empno, sal, deptno FROM emp WHERE (sal, deptno) IN ( SELECT sal, deptno FROM emp WHERE deptno = 30 AND comm is NOT NULL ); EMPNO SAL DEPTNO Null Values in a Subquery 서브쿼리에서 null 값이반환되면주쿼리에서는어떠한행도반환되지않음. Nonpairwise( 비쌍비교 ) Subquery 서브쿼리가여러조건별로사용되어서결과값을주쿼리로넘겨줌 ex) SELECT empno, sal, deptno FROM emp WHERE sal IN ( SELECT sal FROM emp WHERE deptno = 30 AND comm is NOT NULL ) AND deptno IN ( SELECT deptno FROM emp WHERE deptno = 30 AND comm is NOT NULL ); EMPNO SAL DEPTNO

46 6. 서브쿼리 (Subquery) - FROM 절상의서브쿼리 (INLINE VIEW) 와상관관계서브쿼리 46 FROM 절상의서브쿼리 (INLINE VIEW) 란? SUBQUERY 는 FROM 절에서도사용이가능 INLINE VIEW 란 FROM 절상에오는서브쿼리로 VIEW 처럼작용 예제 ) 급여가 20 부서의평균급여보다크고사원을관리하는사원으로서 20 부서에속하지않은사원의정보를보여주는 SQL 문 SELECT b.empno,b.ename,b.job,b.sal, b.deptno FROM (SELECT empno FROM emp WHERE sal >(SELECT AVG(sal) FROM emp WHERE deptno = 20)) a, emp b WHERE a.empno = b.empno AND b.mgr is NOT NULL AND b.deptno!= 20 EMPNO ENAME JOB SAL DEPTNO BLAKE MANAGER CLARK MANAGER 상관관계서브쿼리 상관관계서브쿼리란바깥쪽쿼리의컬럼중하나가안쪽서브쿼리의조건에이용되는처리방식 이는주쿼리에서서브쿼리를참조하고이값을다시주쿼리로반환 예제 ) 사원을관리할수있는사원의평균급여보다급여를많이받는사원의정보를출력 SELECT empno, ename, sal FROM emp e WHERE sal > (SELECT AVG(sal) sal FROM emp WHERE e.empno = mgr) EMPNO ENAME SAL BLAKE CLARK SCOTT KING FORD 3000

47 6. 서브쿼리 (Subquery) - 집합쿼리 (UNION, INTERSECT, MINUS) 47 집합쿼리 (UNION, INTERSECT, MINUS) 집합연산자를사용시집합을구성할컬러의데이터타입이동일해야함 UNION : 합집합 UNION ALL: 공통원소두번씩다포함한합집합 INTERSECT: 교집합 MINUS: 차집합 UNION UNION 은두테이블의결합을나타내며, 결합시키는두테이블의중복되지않은값들을반환 SQL>SELECT deptno FROM emp UNION SELECT deptno FROM dept; DEPTNO UNION ALL UNION 과같으나두테이블의중복되는값까지반환 SQL>SELECT deptno FROM emp UNION ALL SELECT deptno FROM dept; DEPTNO

48 6. 서브쿼리 (Subquery) - 집합쿼리 (UNION, INTERSECT, MINUS) 48 INTERSECT INTERSECT 는두행의집합중공통된행을반환 SQL>SELECT deptno FROM emp INTERSECT SELECT deptno FROM dept; DEPTNO MINUS MINUS 는첫번째 SELECT 문에의해반환되는행중에서두번째 SELECT 문에의해반환되는행에존재하지않는행들을보여줌 SQL>SELECT deptno FROM dept MINUS SELECT deptno FROM emp; DEPTNO