Oracle 기본
오라클 기본 - 기본 내용 정리
오라클에서의 기본 문법, 조인 등 오라클 DB 관련해서 기본적인 내용을 정리해보려고 한다
데이터의 다양한 타입
기본적으로 테이블을 생성할 때, 테이블에 들어가는 칼럼에 대해서 데이터타입과 사이즈를 지정하고 데이터타입에는 다양한 종류가 존재
숫자형 데이터타입
숫자열 데이터 타입은 NUMBER(p, s) 이고 p = 전체 자리수이고 최대 38까지 설정 가능, s = 소수점 이하 자릿수 또는 유효 숫자이고 -84 ~ 127까지의 범위를 가지고 있다 NUMBER 데이터 타입은 정수 또는 실수의 숫자형 데이터를 저장하기 위해 사용한다 NUMBER(p) 이렇게 작성하게 되면 정수만 저장이 되고 NUMBER(6, 2) 이렇게 작성하게 되면 전체 자리수가 6자리에 정수자리는 4자리, 소숫점 자리는 2자리 이렇게 생각하면 된다
예시를 봐보자 실수를 저장하는 경우, NUMBER(6,2) 이렇게 설정해두고 1234.56 -> 정상적으로 1234.56이 저장됨 1234.567 -> 소숫점은 2자리로 제한되어있기 때문에 반올림되서 1234.57이 저장됨 12345.6 -> 소숫점은 2자리를 채워야 하지만 채우지 않았기 때문에 오류 발생 정수를 저장하는 경우, NUMBER(3) 이렇게 설정해두고 123 -> 정상적으로 123이 저장 123.4 -> 하나의 자릿수는 반올림되어 123이 저장 1234 -> 자릿수가 맞기 않기 떄문에 오류 발생 실수든 정수든 저장하는 경우, NUMBER 라고만 설정했을 때는 최대자릿수인 38까지 그대로 저장된다
문자형 데이터타입
문자형 데이터타입은 크게 CHAR(s), NCHAR(s), VARCHAR2(s), NVARCHAR2(s) 이러한 종류가 있다 모든 데이터타입에 들어가는 파라미터 (s)는 문자열의 자릿수를 의미하고 여기 들어갈 수 있는 최대 길이는 2000 bytes임 가장 유명한 2가지를 알아보자 우선 CHAR라는 데이터타입은 고정길이 문자열을 저장할 떄 사용되고, 최대 2000 바이트까지 가능하다 고정길이 문자열이라고 함은 만약 10개로 지정해두고 test를 집어넣으면 왼쪽부터 test를 집어넣고 나머지는 공백으로 저장 만약 해당 긻이보다 더 긴 문자열을 집어넣으려고 하면 에러난다 다음으로는 VARCHAR2 이다 문자열을 지정하는데 있어서 가장 많이 사용되는 데이터 타입이고 가변길이 문자열을 저장할 때 사용한다 가변길이 문자열이라는 이름답게 입력한 데이터에 따라 저장되는 자릿수가 변하게 된다 항상 어떤 데이터가 들어올지 모르기 때문에 데이터 효율성을 생각해서도 varchar2를 사용하자
날자형 데이터타입
날짜형 데이터타입으로는 DATE, TIMESTAMP, INTERVAL이 존재한다
첫 번째로 DATE 타입은 고정된 7 바이트를 할당하며 크기는 따로 지정하지 않는다 DATE은 날짜 + 시간 을 저장하기 위해서 사용하고 오라클 사용환경에 따라 다르지만 코리안 기준으로는 'YY/MM/DD HH24:MI' 이런 형식이다
두 번째는 TIMESTAMP 데이터 타입이고 DATE 의 확장판이라고 생각하면 편하다 그래서 TIMESTAMP 에는 원하면 자릿수를 최소(생략) 6자리부터 9자리까지 지정하는 것이 가능하다 데이터 타입을 비교해서 확인해보면
현재를 기준으로 DATE : 22/03/19 TIMESTAMP : 22/03/19 12:44:34.000000000 TIMESTAMP WITH TIME ZONE : 22/03/19 12:44:34.000000000 ASIA/SEOUL TIMESTAMP WITH LOCAL TIME ZONE : 22/03/19 12:44:34.000000000
세 번째는 INTERVAL 타입이고 여기에는 INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND 데이터 타입이 존재한다
INTERVAL YEAR (y) TO MONTH (m) : y은 연, m은 개월수의 자릿수이고 기본값은 2로 지정되어있음
INTERVAL DAY (d) TO SECOND (s) : d는 일의 자릿수로 기본값은 2, s는 초의 자릿수로 기본값은 6
BLOB, LOB
BLOB(Binary Large OBject) : 이진 파일로 표현되는 정보를 의미하고 우리가 아는 데이터로는 그래픽, 오디오, 동영상과 같은 멀티미디어 정보이다 특징으로는 하나의 테이블에 한 컬럼만 사용할 수 있으며 이진 파일을 위한 RAW, LONG RAW, LONG 타입이 존재한다 LOB(Large OBject) : LOB란 2GB를 초과하는 큰 대용량의 데이터를 저장하기 위해서 존재하는 타입이고 이 것은 크기를 지정하지 않고 가변길이로 저장됨 세부 타입들은 이렇게 존재함
BLOB : 구조화되지 않은 이진 파일을 저장하며 최대 4GB까지 저장
CLOB : 구조화되지 않은 문자 데이터를 저장하며 최대 4GB까지 저장
NCLOB : 저장된 문자열에 대해 다양한 언어를 지원하고 문자열을 저장하기도하며 최대 4GB까지 저장
BFILE : 구조화되지 않은 이진 파일을 디비 외부에 저장하고 최대 4GB까지 저장
오라클 디비에서의 제한
하나의 테이블에서 넣을 수 있는 최대 컬럼수는 1000개이고 테이블과 열에 대한 insert는 제한이 없다]
하나의 컬럼의 최대 길이는 4000 바이트, 숫자의 최대 길이는 38, 날짜의 사용 범위는 BC 4712/1/1 ~ AD 9999/12/31
한 행의 최대 길이는 4000000000바이트, 서브 쿼리의 하위 단계는 255개, 사용자정의명의 최대 문자수는 30바이트이다
오라클 디비에서의 데이터 무결성
데이터 무결성이란 모든 개체는 그 객체에 있는 각 행을 유일하게 식별하는 속성을 가져야 하고 테이블에서 중복된 행을 존재할 수 없다 이것이 데이터 무결성이고 오라클에서는 테이블에 대해서 기본키, 외부키, 고유키, Null/NOT Null, check 이렇게 5 가지의 제약조건을 제공해준다
기본키 제약조건
기본키란 개체의 인스턴스를 유일하게 정의하는 속성이다. 유일하기 떄문에 중복된 데이터를 허용하지 않고, 널 값을 허용하지 않는다는 점을 기억
외부키 제약조건
외부키는 하나 이상의 컬럼이 다른 테이블과의 기본 키와 관계를 정의하는 컬럼이고 이것으로 인해서 참조 무결성을 보장한다 참조 무결성이란 외부키의 컬럼에 값이 입력될 때 관계가 정의된 참조하는 테이블의 기본 키가 존재하지 않으면 이 값이 거부되는 것이다
Null, NOT Null 제약조건
이건 컬럼에 null값을 허용하는지 허용하지 않는지에 대한 의미이다
고유키 제약조건
컬럼에 입력되는 값이 고유한 값을 요구하는 것으로 고유 키가 지정된 컬럼에는 중복된 데이터가 들어갈 수 없다
체크 제약 조건
이건 각 칼럼에 대한 입력 값의 범위나 조건을 지정할 때 사용되고 입력하는 컬럼의 값이 입력하고 있는 다른 컬럼의 값을 참조할 때 체크 제약조건을 지정할 수 있음
테이블 설계
테이블의 정의는 DDL(Data Definition Language)를 사용하고 그 종류로는 CREATE, ALTER, DROP 이 있다
CREATE
테이블을 생성하는데는 CREATE 이라는 예약어를 사용하고
해당 테이블을 구성할 컬럼과 데이터타입 그리고 nullable을 처리해준다 추가적으로 UNIQUE을 걸거나 CHECK 제약조건을 컬럼에 걸어서 insert값에 대한 제약조건을 걸거나 DEFAULT키워드를 사용해서 값이 안들어왔을 때 기본값을 설정해 줄 수 있음 그리고 constraint을 통해서 pk나 fk를 설정해준다 fk는 일단 테이블간의 관계를 정의할때 사용되며 관계를 정의하는 생성할 테이블의 하나 이상의 칼럼이 외부 키가 되고, 이 외부 키는 참조할 테이블의 pk과 관계가 성립 외부키와 기본키와의 관계는
생성할 테이블의 관계를 정의하는 컬럼이 외부 키가 됨
참조하는 테이블의 기본 키와 관계가 성립된
외부 키 컬럼의 수와 참조하는 테이블의 기본 키 컬럼의 수가 동일해야함
외부 키 컬럼의 데이터타입과 참조하는 테이블의 기본 키 컬럼의 데이터 타입과 동일해야함
외부 키 컬럼의 데이터타입을 생략하면 참조하는 테이블의 기본 키 컬럼의 데이터타입을 참조
테이블 생성 시, 참조할 테이블의 기본 키 컬럼은 생략할 수 있음
테이블을 생성하는 것 뿐만이라 테이블을 복사해주는 것도 가능하다 오라클에서 테이블의 구조를 복사하고 싶을 땐 (단일 테이블만! 만약 제약조건까지 변경하고 싶다면 제약조건이 같이 걸려있는 모든 테이블을 다 복사 다시해야함) CREATE TABLE 테이블명 AS SELECT * FROM 변경하려는테이블명 WHERE 1=2;
ALTER
테이블을 생성하고 난 뒤에는 ALTER를 통해서 테이블을 조작하는 것이 가능하다 ALTER를 해주면 내부에 들어가있는 데이터인 '열'에 해당하는 데이터들은 그대로 존재하고 테이블의 구조만 변경된다 변경할 수 있는 것들은 이런게 있다
기존 테이블에 새로운 컬럼 추가
ALTER TABLE 테이블명 ADD(컬럼명 데이터타입 [추가속성], 컬럼명2 데이터타입 [추가속성]...)
기존 테이블에 컬럼의 크기를 늘리거나 줄이기
ALTER TABLE 테이블명 MODIFY (컬럼명 데이터타입(바꾸고싶은 크기))
기존 컬럼에 Null을 Not Null으로, Not Null을 Null로 제약조건 변경
ALTER TABLE 테이블명 MODIFY (컬럼명 데이터타입 [NOT NULL/NULL])
기존 컬럼 삭제
ALTER TABLE 테이블명 DROP COLUMN 컬럼명
기존 컬럼에 기본 값 지정
ALTER TABLE 테이블명 ADD PRIMARY KEY(조합할 컬럼, 조합할 컬럼)
삭제는 단순하게 ALTER TABLE 테이블 DROP PRIMARY KEY
기존 컬럼에 다른 제약조건 지정 또는 삭제
외래키의 추가는 ALTER TABLE 테이블 ADD CONSTRAINT fk이름 FOREIGN KEY 외부테이블pk이름 REFERENCES 외부테
외래키의 삭제는 조건없이 ALTER TABLE 테이블 DROP CONSTRAINT fk이름
DROP
테이블을 삭제하는건 복잡하지 않게 DROP TABLE 테이블명 이렇게 삭제할 수 있는데, 만약에 해당 테이블에 연결되어 있는 외부키가 없을 때이다 즉, 다른 테이블에서 삭제하려는 테이블을 외래키로 참조하고 있으면 삭제할 수 없다는 것
데이터베이스 검색
DML(Data Manipulation Language)라는 데이터 조작어를 사용해서 진행한다 크게 5가지 종류가 있음
SELECT : 행 검색
INSERT : 행 삽입
UPDATE : 행 수정
DELETE : 행 삭제
MERGE : 두 개 이상의 테이블 데이터를 하나의 테이블로 병합
DUAL 테이블
오라클에서 제공해주는 테이블이고, 원하는 함수의 계산 결과값을 확인할 때 사용 어떠한 것들 테스트해보고 싶을 때, 예시로는 함수를 테스트하기 위해서 테이블을 만들어서 테스트하는 것이 아니라 dual테이블을 사용해서 함수 값을 리턴받는 것이 가능하다! 말 그대로 테스트용 더미 테이블이라고 생각하면 된다
SELECT
SELECT는 테이블에게 필요한 데이터를 질의(query)해서 검색하는 명령어이다 문법으로는 SELECT [DISTINCT] 컬럼 FROM 테이블 이 명령어는 select와 from 이 2 가지 예약어가 필수로 들어가야 한다 select의 특징이라고 하면
from 절의 테이블으로부터 검색하여 출력할 컬럼명이나 *(와일드카드)를 사용해서 기술하고 여러 가지의 컬럼을 입력할 때는 ,을 사용
컬럼 앞에는 distinct이라는 예약어를 통해서 컬럼중에서 겹치는게 없이 나오게 할 수 있다
컬럼 뒤에는 한 칸 이상의 공백을 두고 as을 통해서 별명을 주는 것이 가능하다
계산식이나 함수 등을 사용할 수 있음
SELECT문의 각 절에는 무엇이 들어갈까
SELECT : 컬럼명, *, 함수, 수식 등이 들어갈 수 있음
출력할 컬럼명 등을 넣고 최종 결과 테이블을 생성
FROM : 테이블명, 뷰명등이 들어갈 수 있음
검색할 테이블을 적으면 된다
WHERE : 검색조건을 적으면 된다
조건을 작성하는데 있어서 산술연산자(+,-,*,/)가 들어갈 수 있고 관계연산자(>,>=,<,<=,=,<>,!=)도 들어갈 수 있으며 논리연산자(AND, OR, NOT)가 들어갈 수 있음
연결연산자로 '||' 을 지원해주는데 이건 리눅스의 파이프라인과 비슷하게 문자를 잇고 싶을 때 사이에 ||을 넣어줄 수 있다
LIKE 연산자가 있는데 요놈은 문자열의 내부에서 '포함'하는지를 확인하는 과정이고 검색하고 싶은 문자열의 앞뒤에 %을 넣어서 나머지 처리를 할 수 있다 / NOT LIKE도 있음
IN 연산자는 가능한 값들의 목록을 입력해주고 해당 목록과 비교하여 만약 제시한 목록에 있는 값이 실제 데이터에도 존재한다면 true를 뱉는 방식이다
BETWEEN ~ AND 연산자는 지정된 범위의 최소값부터 최대값까지의 범위를 계산하는 연산자이고 그 대상으로는 숫자, 문자, 날짜 모두 가능하다
GROUP BY : 컬럼명을 묶어서 조회하는 방식
HAVING : 검색조건을 넣어주고 where와 다른점은 group by의 검색조건에 해당하는 값이다
ORDER BY : 컬럼을 적고 그 뒤에는 ASC/DESC을 적어서 정렬하는 방식을 정하면 된다
서브쿼리
서브 쿼리란 SQL 명령문을 사용하는데 그 내부에서 한번 더 SELECT 하는 방식이다 SQL 명령문 내부에 서브 쿼리가 존재한다면 일단 서브 쿼리를 먼저 실행해서 그 결과 값을 메인 쿼리로 보내고 그 결과를 사용해서 쿼리를 진행한다 주의사항
서브 쿼리는 괄호()안에서 작성
서브 쿼리가 뱉는 행의 수는 연산자가 기대하는 값의 수와 같아야하며 서브쿼리에서 뱉는 컬럼의 수는 기대하는 컬럼의 수와 같아야함
서브 쿼리에서는 order by를 사용하지 않음
메인 쿼리의 from 절에 있는 테이블의 컬럼명은 서브 쿼리 내에서 사용할 수 있지만 서브 쿼리의 from에 있는 테이블 컬럼명을 메인 쿼리에서는 사용 불가능
서브 쿼리는 최대 255개까지 가능
단일 행 서브 쿼리
말 그대로 서브 쿼리의 결과 값이 단일 행이고 단일 컬럼이나 다중 컬럼으로 뱉는 쿼리이다 책의 실습을 한번 보자 SG_Score테이블의 'L1031' 'SQL' 과목의 행들을 출력하고 'L1031' 과목의 평균 점수보다 높은 점수를 출력하라
이 쿼리문에서는 일단 L1031에 대한 평균점수를 알아야 where절에 조건으로 넣어주는데 이것을 굳이 쿼리문 2개로 나눠서 평균을 먼저 구하고 그 결과를 가지고 값을 집어넣어서 조회하는 것이 아니라 서브 쿼리를 사용해서 내부에서 먼저 구해와서 그것으로 또 쿼리에 사용하는 방식이다
다중 행 서브 쿼리
이 서브 쿼리를 사용하게 되면 다중 행으로 반환되고 다중 행 연산자로는 ANY, SOME, ALL, EXISTS 가 있음
IN : 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 하나라도 일치하면 참을 뱉는 연산자
ANY, SOME : 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에 하나 이상 일치하면 참을 뱉는 연산자
ALL : 메인 쿼리의 비교 저건이 서브 쿼리의 결과와 모든 값이 일치하면 참을 뱉는 연산자
EXISTS : 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 하나라도 반환하면 참을 뱉는 연산자
서브 쿼리를 이용한 테이블 생성
서브 쿼리에서 CREATE TABLE문을 통해서 테이블의 데이터를 전체 혹은 일부분을 복사해서 새로운 테이블을 만들 수 있다 일단은 뭐 알고나 있자
INSERT
insert문을 한 번 실행할 때마다 한 행에 데이터가 저장된다. 그래도 이것의 사용은 이렇게 한다 INSERT INTO 테이블[(컬럼, 컬럼2, ...)] VALUES (값, 값 ...); 주의사항은 이러하다
컬럼명과 입력하는 값의 수와 데이터타입은 반드시 동일해야 함
기본 키와 NotNull으로 설정되어 있는 컬럼은 반드시 넣어줘야 한다
컬럼명이 생략되면 테이블에 존재하는 모든 컬럼을 작성해줘야 한다
입력하지 않은 컬럼의 값은 기본적으로는 null이 들어가지만 default값이 있으면 default값이 들어감
만약 insert 시, 오류가 발생하면 이하의 항목들을 확인해보자
컬럼명과 입력하는 값의 수가 동일하지 않을 때
컬럼명과 데이터 타입이 동일하지 않을 때
기본 키와 not null컬럼의 값이 입력되지 않았을 때
기본키가 중복된 데이터나 null값을 입력할 때
외부 키 컬럼의 값이 차조하는 테이블의 기본 키 값이 아닐 때
고유 키 제약조건이 지정 컬럼에 중복된 데이터가 들어갈 때
입력 값이 컬럼의 크기를 초과할 때
오라클에서는 ""(빈문자열)을 null로 처리..! 빈값을 "'로 넘겨줄 때 null로 나오는거 주의
서브쿼리가 있는 INSERT 문
서브 쿼리가 있는 insert문은 기존 테이블의 모든 데이터 또는 일부분을 다른 테이블에 동일하게 복사하는 경우에 유용하다 주의사항으로는
insert 문의 컬럼 수와 select 절의 컬럼 수가 반드시 동일
insert 문의 컬럼과 select 절의 컬럼의 데이터 타입은 반드시 동일
select 절에 기술된 컬럼이 insert에 기술된 컬럼의 순서대로 값에 저장
UPDATE
이것은 테이블에 저장된 각 행들의 컬럼들을 변경하는데 사용 UPDATE 테이블 SET 컬럼=바꿀값 [WHERE 조건];
DELETE
테이블에 저장된 각 행을 삭제하는 방법이고 단일로도 삭제 가능하고 전체 행을 삭제하는 것도 가능 DELETE FROM 테이블 [WHERE 조건]; 만약 WHERE절을 따로 언급해주지 않는다면 모든 행을 삭제하고 WHERE절이 있다면 해당 절에 만족하는 행만 삭제함 이외에도 TRUNCATE TABLE을 사용해서 테이블에 존재하는 모든 행을 삭제할 수 있다 테이블을 싹 비우는데에는 DELETE보다는 좋지만 TRUNCATE을 통해서 삭제된 행(데이터)들은 복구될 수 없다는 단점이 존재한다
MERGE
MERGE문은 테이블의 구조가 동일한 두 개 이상의 테이블이나 뷰, 서브 쿼리로부터 데이터를 비교하여 하나의 데이터를 병합하는 방식으로, 오라클에서만 제공해주는 명령어다 이 명령어는 결국 입력과 수정을 동시에 작업할 수 있는 명령어이다
테이블A : 대상 테이블명
USING : 원본 테이블이나 뷰 또는 서브 쿼리를 적음
조건 : 원본 테이블이나 대상 테이블에 대한 조건을 비교하여 만족하면 UPDATE/DELETE, 만족하지 않으면 INSERT
WHEN MATCHED : 조건이 만족하는 경우 실행할 UPDATE문이나 DELETE문을 작성
WHEN NOT MATCHED : 조건이 만족하지 않을 경우 실행할 INSERT문을 작성
일단은 이렇게 사용한다~ 라고 알아두고 실제로 사용할 때 보충해서 적어두자
트랜잭션 제어 명령
오라클 디비에서는 데이터 변경 작업이 즉각적으로 반영되지 않는다 시간을 두고 변경 작업을 디비에 적용하는데, 이를 관리하는 단위가 트랜잭션이다 트랜잭션이란 테이블에 대해서 행을 추가하거나 컬럼 값 수정 또는 행의 삭제에 의해서 변경되는 작업의 단위 / 변경작업을 할 수 있도록 사용자에게 할당된 기간 여기서 나오는 명령어는 2개가 있다
COMMIT : 테이블에 대한 트랜잭션 영역의 변경 작업을 영구히 저장
ROLLBACK : 테이블에 대한 트랜잭션 영억의 변경 작업을 삭제
SQL 함수
SQL 에는 데이터 조작을 위한 다양한 SQL 함수를 제공하고 있다
그 종류로는 크게 단일행 함수가 있고 복수행 함수가 있다
단일행 함수(문자, 숫자, 날짜, 데이터변환 등) : 행의 수만큼 반환, SELECT-WHERE-GROUPBY, 인수가 사용, SQL함수가 많음
복수행 함수(변환, 분석) : 그룹에 대하여 하나의 값만 반환, SELECT-HAVING, GROUPBY절에 그룹화
문자형 함수
LOWER(str) : 소문자로 변환
UPPER(str) : 대문자로 변환
INITCAP(str) : 단어의 첫 문자만 대문자로 변환
LENGTH(str) : 문자열의 길이 계산
LENGTHB(str) : 문자열의 길이를 바이트로 계산
INSTR(str, c, p, n) : 특정 문자의 위치 계산
SUBSTR(str, p, n) : 해당 위치에서 특정 문자열을 추출
CONCAT(str1, str2) : str1과 str2를 결합
TRIM(str, 'set') : 문자열에서 특정 문자 제거
PAD(str, n, 'set') : 문자열에 특정 문자 추가
REPLACE(str, 'c1', 'c2') : 문자열의 일부분을 다른 문자열로 대치
숫자형 함수
ROUND(M, N) : M 값을 소숫점 N 자릿수로 반올림하여 실수로 리턴
TRUNC(M, N) : M 값을 소숫점 N 자릿수로 내림하여 실수로 리턴
CEIL(M) : M 값의 소숫점 이하를 올림하여 정수로 리턴
FLOOR(M) : M 값의 소숫점 이하를 내림하여 정수로 리턴
MOD(M1, M2) : M1을 M2로 나눈 나머지 값을 리턴
날짜형 함수
CURRENT_DATE : 지역 시간대의 현재 날짜를 리턴
CURRENT_TIMESTAMP : 지역 시간대의 현재 날짜와 시간을 리턴
SYSDATE : 시스템의 현재 날짜와 시간을 리턴
ADD_MONTHS(d, n) : 날짜(d)에 n개월 수를 더해서 리턴
MONTHS_BETWEEN(d1, d2) : 두 날짜 d1, d2의 월차를 계산해서 리턴
LAST_DAY(d) : 그 달(d)의 마지막 날짜를 리턴
NEXT_DAY(d, '요일') : 날짜(d)로부터 다음에 나오는 요일을 반환
ROUND(d) : 날짜(d)를 반올림
TRUNC(D) : 날짜(d)를 내림
데이터 변환함수
날짜형으로 변환하는 TO_DATE()
날짜형 데이터의 기본은 YY/MM/DD 인데, 우리가 원하는 데이터 형식은 제각각이기 때문에
TO_DATE(문자형 데이터, '날짜형 변환형식')로 사용하고
가장 풀로 사용하는건 'YYYY-MM-DD HH24:MI:SS' 이 형식에서 수정하고 더 찾아서 사용하자
문자형으로 변환하는 TO_CHAR()
이건 숫자나 날짜를 문자로 변환하는 케이스이다
TO_CHAR(날짜형 데이터, '날짜형 변환형식') 이렇게 사용
추가로 TO_CHAR(숫자형 데이터, '숫자형 변환형식') 이렇게 숫자형 데이터를 문자형 데이터로 수정하는 것이 가능하다
숫자형으로 변환하는 TO_NUMBER()
이건 문자열 데이터를 숫자형 데이터로 수정하는 방식이다
NULL 관련 함수
NVL(expr, '값') : expr가 null이면 값을 리턴
NVL2(expr, '값1', '값2') : expr가 null이면 값1, not null이면 값2 리턴
NULLIF(expr1, expr2) : expr1==expr2 ? null : expr1
LANVL(조건) : 조건이 참이면 거짓을 리턴, 거짓이면 참 리턴
COALESCE(expr1, expr2, expr3, ...) : expr1이 null이 아니면 expr1을 리턴, null이면 expr2이 null인지 비교해서 쭉쭉 뒤로 나감
기타 함수
DECODE(expr, 비교값1, 리턴값1, 비교값2, 리턴값2, 비교값N, 리턴값N) : expr이 비교값이면 리턴값을 리턴
CASE-WHEN
함수를 사용해서 데이터를 원하는 형태로 변환하는 경우도 있지만
여러가지 조건에 따라서 데이터를 다양하게 변환할 때 사용하고 2가지 방법으로 작성해볼 수 있음
고급 SQL 함수
GROUP BY절과 그룹 함수
테이블의 전체 데이터를 특정 데이터 기준으로 그룹화하는 것이 GROUP BY GROUP BY절은 FROM절에 기술된 테이블의 컬럼들을 이용해서 그룹화할 컬럼명을 기술하고 컬럼의 데이터를 그룹화해서 중간 결과 테이블을 생성한다 HAVING 절을 사용해서 GROUP BY절에 대해서 검색 조건을 지정한다
그룹함수
그룹함수는 복수 행을 처리해서 오직 한 행의 결과 값을 반환한다
count() : 테이블의 행의 수를 리턴
MAX() : 컬럼 값에서 최대 수를 리턴
MIN() : 컬럼 값에서 최소 수를 리턴
SUM() : 컬럼 값의 합계를 리턴
AVG() : 컬럼 값의 평균 리턴
STDDEV() : 컬럼 값의 표준편차를 리턴
VARIANCE() : 컬럼 값의 분산을 리턴
고려사항으로는
컬럼의 null값은 계산에서 제외
행의 수와 관계없이 단 하나의 행만을 반환
그룹 함수는 단일 컬럼과 함께 사용할 수 없음
WHERE절을 사용하면 검색조건이 참이 되는 행에 대해서 계산
ROLLUP, CUBE 를 사용하면 그룹 조건에 따라 그룹별 합계를 계산해서 출력할 수 있음 ROLLUP : 그룹핑 컬럼의 그룹별 합계 정보를 출력하는데 그 순서는 a,b,c가 있다면 -> abc, ab, a, 전체 순으로 조합결과를 반환 CUBE : 그룹핑된 컬럼의 가능한 모든 조합에 대한 합계정보를 출력
그룹핑 함수 : 이 함수들은 연산기능은 없고, 출력 결과에 대한 사용자의 이해를 위하여 사용된다 GROUPING(expr) : expr이 ROLLUP 또는 CUBE 연산에 사용이 되었는지를 보여주는 함수
expr은 GROUP BY절에 기술된 컬럼의 하나와 반드시 일치되어야 한다
expr은 컬럼의 값이 모든 조합의 집합에서 null로 나타나면 1을 반환하고 아니면 0을 반환
GROUPING_ID(expr, ...) : ROLLUP, CUBE, GROUPING() 함수와 함께 사용하여 expr이 연산의 사용 유무에 따라 0, 1을 반환
GROUP_ID() : 지정된 GROUP BY 결과로부터 중복된 그룹을 구별하며, 질의 결과에서 중복된 GROUPING을 필터링하는데 유용하며 유일한 중복 그룹을 식별하기 위해서 오라클 숫자를 반환한다
조인
조인이란 어떤 값의 공통된 집합을 공유하는 둘 이상의 테이블로부터 행을 검색하는 것 조인을 하기 위해서는 SELECT문의 FROM절에 적어도 두 개 이상의 테이블이 존재해야하고, WHERE절에 하나 이상의 조인 조건이 있어야 한다 SELECT문의 FROM절에 테이블이나 뷰 등이 두 개 이상이 기술되고, 조인조건이나 출력할 컬럼 등을 기술하는 WHERE절이나 SELECT절 등에서 각 테이블에 사용된 동일한 컬럼명이 구분되어야 함
컬럼을 구분하기 위해서 테이블.컬럼 이렇게 기술하고 테이블명에 별명을 사용하면 더욱 쉽게 단순화할 수 있다 각 테이블의 공통된 행일 때 부속 질의어는 서브 쿼리에 사영하는 테이블의 컬럼을 출력하지 못하지만 조인은 FROM절에 기술한 테이블의 컬럼들은 언제든 출력가능 하지만 조인하는데 있어서 2개 이상의 테이블을 정의하는데, 각 테이블이나 뷰에서 같은 이름의 컬럼이 존재하고 있다면 테이블명.컬럼명 이렇게 지정해주지 않으면 'ORA-00918: 열의 정의가 애매합니다' 라는 에러가 나오기 떄문에 컬럼명에 주의하자 조인의 종류로는
카티션 프로덕트(Cartesian Product)
등가 조인(equi join)
외부 조인(outer join)
자가 조인(self join)
비등가 조인(non-equi join)
안티 조인(anti join)
세미 조인(semi join)
카티션 프로덕트
카티션 프로덕트는 크로스 조인, 교차조인이라고도 불리우고 SELECT문의 WHERE절에 조인조건이 생략된 것이다 크로스 조인이라는 것의 의미는 조인하려는 테이블의 모든 행을 곱한만큼의 수를 출력하는 것이다 즉, 조인하는 것의 모든 경우 수를 만드는 것이다 사용하는 방법으로는 CROSS JOIN이라는 키워드를 사용하면 된다
등가조인
등가조인이란 두 개 이상의 테이블로부터 행을 검색할 때, 한 테이블에 있는 하나 이상의 컬럼 값이 다른 테이블에 있는 하나 이상의 컬럼 값과 같을 때 참이 되고, 참이 될 때 행의 컬럼 값을 조합하여 출력하는 조인이다 등가조인은 내부조인(inner join)이나 단순조인(simple join)이라고도 불리우고 가장 많이(?) 사용되는 조인 방식이고 방법으로는
WHERE절을 이용한 방법
NATURAL JOIN 키워드를 이용한 방법
JOIN-USING 키워드를 이용한 방법
이렇게 있다 WHERE를 이용 등가조인은 SELECT문을 WHERE절에서 조인조건으로 '=' 관계연산자를 사용한다
조인조건은 단순하게 WHERE절에서 테이블1.컬럼 = 테이블2.컬럼 이렇게 작성해주면 두 테이블의 컬럼값이 공통된 행에 대해서 출력해준다
NATURAL JOIN를 이용 표준문법으로 등가조인은 WHERE절을 생략하고 FROM절에 NATURAL JOIN 키워드를 사용할 수 있다 FROM절에서 NATURAL JOIN키워드를 사용하면 자동으로 테이블간의 모든 컬럼을 대상으로 공통 컬럼을 조사해서 내부적으로 조인을 생성 단, SELECT 절에 공통 컬럼은 테이블의 별명을 붙이지 않으며 NATURAL JOIN 키워드에 사용된 컬럼은 식별자를 가질 수 없다
여기서 두 테이블간의 공통 컬럼이 기술되지 않더라도 Course 테이블의 Professor_ID와 Professor테이블의 Professor_ID가 된다
JOIN~USING를 이용 NATURAL JOIN은 모든 컬럼을 대상으로 공통컬럼을 찾아서 그 공통 컬럼으로 조인을 진행하기 때문에 속도가 느리다는 단점이 존재 JOIN~USING 키워드는 두 테이블간의 조인조건에 해당하는 공통 컬럼명을 ()안에 넣어줘서 바로 조인을 진행해서 속도가 빨라진다
외부조인
외부조인은 테이블간의 공통된 행과 공통되지 않은 테이블의 행들을 모두 출력한다. 외부조인은 어디 테이블에서 공통되지 않은 테이블을 출력할 것인지를 정하는데 있어서 left outer join, right outer join으로 나누어지게 된다 그리고 외부조인을 작성하는 방법은
WHERE절의 등가조인 조건에 (+)을 기술한다
left outer join은 등가조인 WHERE절 조인조건의 오른쪽에 (+) 작성
right outer join은 등가조인 WHERE절 조인조건의 왼쪽에 (+) 작성
OUTER JOIN ~ ON ~ 키워드로 기술한다
left outer join은 LEFT OUTER JOIN ~ ON ~
right outer join은 RIGHT OUTER JOIN ~ ON ~
전체 outer join은 FULL OTER JOIN ~ ON ~
조인조건은 공통 컬럼의 테이블1.컬럼 = 테이블2.컬럼 이렇게 작성
WHERE절에 LEFT OUTER JOIN
left outer join은 등가조인의 WHERE절 조인조건에서 오른쪽 위치에 (+)을 작성하면되고 예시로 테이블1, 테이블2를 조인하게 되면 왼쪽에 해당하는 테이블1의 모든 행 + 테이블1,2의 공통된 행 이렇게를 뽑아온다
WHERE절에 RIGHT OUTER JOIN
right outer join은 등가조인의 WHERE절 조인조건에서 왼쪽 위치에 (+)을 넣어서 공통된 행과 테이블2의 공통되지 않은 행들을 출력 위의 left outer join과는 반대로 오른쪽에 해당하는 테이블2의 모든 행 + 테이블1, 2의 공통된 행 이렇게를 추출
OUTER JOIN 키워드를 사용한 left outer join
left outer join은 FROM절에 '테이블1 LEFT OUTER JOIN 테이블2 ON 조인조건' 이렇게 작성하고 만약에 값이 null을 가지고 있어도 다 출력
OUTER JOIN 키워드를 사용한 right outer join
위에서 이야기했던 outer join 키워드로 거는 것과 동일하다
OUTER JOIN 키워드를 사용한 전체 외부 조인
FULL OUTER JOIN은 표준문법에서만 지원하고 FROM절에 '테이블1 FULL OUTER JOIN 테이블2 ON 조인조건' 키워드로 기술하고 조인조건에 만족하는 공통 행과 조인조건에 만족하지 않은 테이블1과 테이블2의컬럼 값이 null인 경우에도 출력 결국 테이블1만 있는거 + 테이블1, 2 공통으로 있는거 + 테이블2만 있는거 이렇게 다 가져온다는 의미
자기조인(SELF JOIN)
SELF JOIN이란 순환적 관계 모델링에 의해 표현되는 테이블이고 그 의미는 테이블이 자신의 기본 키를 참조하는 외부 키를 가지고 있을 때 사용된다는 것이다 그리고 자기 조인은 FROM 절에 종일한 테이블을 두 번 적고 별명으로 구분해서 조인한다 그 방법으로는
FROM에 '테이블명 별명1, 테이블명 별명2'을 적어주고, WHERE에 조인조건을 기술
FROM에 '테이블명 별명1 JOIN 테이블명 별명2 ON 조인조건'
FROM에 '테이블명 별명1 INNER JOIN 테이블명 별명2 ON 조인조건'
WHERE절을 이용한 자기조인
JOINON 을 이용한 자기조인
INNER JOIN 을 이용한 자기조인
기타 조인
비등가 조인
비등가 조인이랑 조인 조건에서 =(등호)가 아닌 <, >와 같은 관계 연산자나 BETWEENAND등과 같은 연산자를 사용한 조인 방법이다
서브 쿼리를 이용한 조인
서브 쿼리를 이용한 조인 방법으로는 세미조인과 안티조인이 존재한다
세미 조인(semi join)은 EXISTS와 IN 연산자의 효율적인 처리를 위한 조인
안티 조인(anti join)은 메인 쿼리의 컬럼 값이 서브 쿼리의 반환 값을 만족하지 않는 행들을 반환
집합연산자
각 쿼리 결과 행에 대한 집합 연산을 할때 사용하는 집합 연산자
UNION, UNION ALL : 각 집합의 합을 반환하는 합집합이고 UNION ALL은 모든 원소를 포함한 합집합
INTERSECT : 각 집합의 공통된 행을 반환하는 교집합
MINUS : 한 집합에서 다른 집합을 뺀 행을 반환하는 차집합
주의사항으로는
각 SELECT문은 ORDER BY 절을 포함하지 않기 때문에 전체 결과 행에 대한 ORDER BY 절을 포함할수는 없다
첫 번째 SELECT문의 컬럼 수와 두 번째 SELECT문의 컬럼 수가 반드시 같아야 함
첫 번째 SELECT문의 컬럼과 두 번째 SELECT문의 컬럼의 데이터타입이 같아야 함
전체 결과 행에 대한 ORDER BY 절은 컬럼명보다 순서번호를 사용
예시로 보면 A = {1, 3, 5, 7, 9} B = {1, 2, 3, 4} UNION = {1, 2, 3, 4, 5, 7, 9} UNION ALL = {1, 1, 2, 3, 3, 4, 5, 7, 9} INTERSECT = {1, 3} A MINUS B = {5, 7, 9} B MINUS A = {2, 4}
Last updated
Was this helpful?
