[oracle] SQL의 함수, DDL, 트랜잭션, 무결성 제약조건

select * from deptno where deptno = 10;

 

핵심은 where절

- 어떻게 조건은 다느냐에 따라서 출력되는 결과물이 달라진다.

between and ~에서 ~사이까지

in 해당하는 요소만 출력(or 연산자의 줄임) - 비일비재하게 많이씀

 

평균, 합계 -> 연산 ->  함수가 지원된다.

 

많이 사용하는 기준으로 공부..

 

SQL 함수

dual 테이블

- 산술 연산이나 가상 컬럼 등의 값을 한 번만 출력하고 싶을 때

 

문자함수

LOWER

-> SELECT 'DataBase', LOWER('DataBase') FROM DUAL;

UPPER

-> SELECT 'DataBase', UPPER('DataBase') FROM DUAL;

INITCAP 

- 첫 글자만 대문자 나머지는 소문자

-> SELECT 'DATABASE PROGRAM', INITCAP('DATABASE PROGRAM') FROM DUAL;

CONCAT 

- 문자열 연결

-> SELECT CONCAT('Data', 'Base') FROM DUAL;

LENGTH/LENGTHB(바이트수)

- 문자길이 구하기

->SELECT LENGTH('DataBase'), LENGTHB('데이터베이스') FROM DUAL;

SUBSTR/SUBSTRB(바이트수만큼 잘라냄) 

- 문자열 일부만 추출

//첫번째 SUBSTR은 츨력, 두번째 SUBSTR은 조건

->SELECT HIREDATE, SUBSTR(HIREDATE, 1, 2) FROM EMP WHERE DEPTNO=20 AND SUBSTR(HIREDATE, 1, 2)=87;

INSTR/INSTRB

- 대상 문자열이나 컬럼에서 특정 문자가 나타나는 위치

->SELECT INSTR('데이터베이스', '이', 3, 1), INSTRB('데이터베이스', '이', 3, 1) FROM DUAL;

LPAD/RPAD

- 컬럼이나 대상 문자열을 명시된 자릿수에서 오른 쪽에 나타내고, 왼쪽자리에 특징기호로 채운다

->SELECT LPAD('DataBase', 20, '$'), RPAD('DataBase', 20, '$') FROM DUAL;

TRIM

- 컬럼이나 대상 문자열에서 특정 문자가 첫 번째 글자이거나 마지막 글자면 잘라내고 남은 문자열만 반환

->SELECT ENAME, TRIM('S' FROM ENAME), TRIM('H' FROM ENAME) FROM EMP WHERE ENAME='SMITH';

 

숫자함수

ABS

- 절대값을 반환

ROUND

- 특정 자릿수에서 반올림한다(두 번째 자리까지 표시가능)

->ROUND(35.12, 1) : 소수점이하 둘째자리에서 반올림

ROUND(21.125, 2) : 소수점이하 셋째 자리에서 반올림

ROUND(834.12, -1) : 두 번째 인자 값이 음수라서 일 단위에서 반올림

ROUND(834.12, -2) : 두 번째 인자 값이 음수라서 십 단위에서 반올림

->SELECT ROUND(35.12, 1), ROUND(21.125, 2), ROUND(834.12, -1), ROUND(834.12, -2) FROM DUAL;

<<자바의 숫자 함수>>

round - 반올림(3.5->4)

floor - 내림(3.9->3)

ceil - 올림(3.1->4)

TRUNC

- 지정한 자릿수 이하를 버린 결과를 구해주는 함수

MOD

- 나머지를 반환

 

날짜함수

SYSDATE

- 현재 날짜를 반환

->SELECT SYSDATE-1 어제, SYSDATE 오늘, SYSDATE+1 내일  FROM DUAL;

MONTH-BETWEEN

- 두 날짜 사이 간격을 계산

-> MONTHS_BETWEEN(DATE1, DATE2)

NEXT_DAY

- 해당 요일에 가장 가까운 날짜를 반환

-> NEXT_DAY(SYSDATE, '월요일')

LAST_DAY

- 다이어리 만들 때 사용

ROUND,TRUNC

 

변환함수

TO_CHAR()

- 하나의 형변환, DATE형->문자형, 숫자형->문자형

//TO_CHAR(날짜데이터, '출력할 형식')

->SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD, HH24:MI:SS') FROM DUAL;

->SELECT SYSDATE, TO_CHAR(HIREDATE, 'YY/MON/DD DY') FROM EMP WHERE DEPTNO=10;

//숫자를 문자형태로 출력($->$, L->\, S->+) 

->SELECT ENAME, SAL, TO_CHAR(SAL, 'L999,999') FROM EMP WHERE DEPTNO=10;

TO_DATE()

- 하나의 형변환, 문자형->DATE형

//TO_DATE(날짜, '출력할형식')

->SELECT EAME, HIREDATE FROM EMP WHERE HIRDATE=TO_DATE(19810220, 'YYYYMMDD');

//TO_DATE함수를 사용하여 날짜 빼기

->SELECT TRUNC(SYSDATE-TO
_DATE('2008/01/01', 'YYYY/MM/DD')) FROM DUAL;

TO_NUMBER()

- 특정 데이터를 숫자형으로 변환

//산술 연산은 문자형을 숫자형으로 변환한 후 실행

->SELECT TO_NUMBER('10,000', '999,999,999') + TO_NUMBER('20,000', '999,999,999') FROM DUAL;

 

일반함수

NVL

- NULL을 0 또는 다른 값으로 변환

->SELECT ENAME, COMM, NVL(COMM, 0), JOB FROM EMP ORDER BY JOB;

->SELECT ENAME, COMM, SAL*12+NVL(COMM, 0), JOB FROM EMP ORDER BY JOB;

DECODE

- SWITCH-CASE, 첫 번째 인자로 받은 값을 조건에 맞춰 처리

DECODE(표현식, 조건1, 결과1, 조건2, 결과2, 기본 결과N)

CASE

- IF-ELSE-IF, 조건에 따른 처리

CASE 표현식 WHEN 조건1 THEN 결과1 WHEN 조건2 THEN 결과2

ELSE 결과N END 테이블이름

 

그룹함수

SUM

- 해당 컬럼 값의 총 합을 구하는 함수

//SAL의 총 합을 구한다

->SELEC SUM(SAL) FROM EMP;

AVG

- 해당 컬럼 값의 평균을 구하는 함수

//SAL의 평균을 구한다

->SELEC AVG(SAL) FROM EMP;

MAX & MIN

- 최대값과 최소값 구하는 함수

//SELECT문에 그룹 함수를 사용하는 경우, 그룹 함수를 적용하지 않은 단순 컬럼은 올 수 없다는 사실을 명심

->SELECT ENAME, MIN(SAL) FROM EMP; =>ERROR

COUNT

- 테이블에서 조건을 만족하는 행의 갯수

->SELECT COUNT(*), COUNT(COMM) FROM EMP;

GROUP BY절

- 특정 컬럼 기준으로 그룹별로 구분하기 위한 절

//아무 컬럼이나 하면 안된다.

//GROUP BY절에서 사용한 컬럼만 사용가능하다.

->SELECT DEPTNO, SUM(SAL), AVG(SAL) FROM EMP GROUP BY DEPTNO;

HAVING절

- GROUP BY절에 특정 조건을 기술

->SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) >=2000;

//전체 연산한 후에 30, 40을 제거시키는 것이므로 불필요한 연산도 하게 된다. <<전체스캔 VS 부분스캔>> 비용에 따른 차이가 있다.

->SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO IN(10, 20);

== SELECT DEPTNO, MAX(SAL) FROM EMP WHERE DEPTNO IN(10, 20) GROUP BY DEPTNO;

 

오라클에서의 객체

1. table

2. view

3. index

4. sequence

 

DDL - 테이블 구조의 결정

테이블 만들기

CREATE TABLE EMP01(

 EMPNO NUMBER(4),

 ENAME VARCHAR(20),

 SAL NUMBER(7,2)

);

테이블 복사해서 생성하기

CREATE TABLE EMP02 AS SELECT * FROM EMP;

새로운 컬럼 추가

ALTER TABLE EMP02 ADD(JOB2 VARCHAR2(9));

컬럼 삭제

ALTER TABLE EMP02 DROP COLUMN JOB2;

테이블 삭제

DROP TABLE EMP01;

테이블 이름 변경

RENAME EMP02 TO EMP03;

테이블의 모든 로우 제거

TRUNCATE TABLE EMP03;

 

truncate는 rollback 불가능 하므로 신중하게,,

delete는 rollback 가능

 

INSERT INTO 테이블 이름(컬럼들) VALUES 컬럼값들

 

CREATE TABLE DEPT01(

 DEPTNO NUMBER(2),

 DNAME VARCHAR2(14),

 LOC VARCHAR2(13)

);

DESC DEPT01;

SELECT * FROM DEPT01; 

 

행 생성

->INSERT INTO DEPT01(DEPTNO, DNAME, LOC)

VALUES(10, 'ACCOUNTING', 'SEOUL');

->INSERT INTO DEPT01(DEPTNO, DNAME, LOC)

VALUES(10, 'ACCOUNTING', NULL);

 

INSERT구문의 오류 발생에 대한 주의할 점

1. 데이터 갯수

2. 작은 따옴표

3. 데이터형

 

테이블 수정

UPDATE 테이블 이름 SET 컬럼 이름 = 컬럼값 WHERE 조건;

->UPDATE EMP02 SET DEPTNO=30;

 

행 삭제 DELETE

->DELETE FROM DEPT01;

->DELETE FROM DEPT01 WHERE DEPTNO=30;

 

트랜잭션

- 일처리 단위

- 데이터베이스에서 데이터를 처리하는 하나의 논리적인 작업 단위

 

COMMIT

- 데이터를 삭제한 결과를 물리적으로 영구히 저장

 

ROLLBACK

- 트랜잭션 작업 내용을 취소

- 이전 COMMIT한 곳까지만 복구

 

자동 COMMIT명령과 자동 ROLLBACK명령이 되는 경우

- 정전 발생이나 컴퓨터 다운 시 자동으로 ROLLBACK

 

SAVEPOINT

- 차후 롤백과 함께 사용해서 현재 트랜잭션 내의 특정 세이브 포인트까지 롤백할 수 있게 된다

 

->DROP TABLE DEPT01;

CREATE TABLE DEPT01 AS SELECT * FROM DEPT;

SELECT * FROM DEPT01;

 

->DELETE FROM DEPT01 WHERE DEPTNO=40;
COMMIT;
SELECT * FROM DEPT01;

 

->DELETE FROM DEPT01 WHERE DEPTNO=30;
SELECT * FROM DEPT01;

 

->SAVEPOINT C1;
DELETE FROM DEPT01 WHERE DEPTNO=20;
SELECT * FROM DEPT01;

 

->SAVEPOINT C2;

DELETE FROM DEPT01 WHERE DEPTNO=10;

SELECT * FROM DEPT01;

 

//SAVEPOINT C2까지 살아난다.

->ROLLBACK TO C2;

SELECT * FROM DEPT01;

//SAVEPOINT C1까지 살아난다.

->ROLLBACK TO C1;

SELECT * FROM DEPT01;
//COMMIT한 곳까지 살아난다.

ROLLBACK;

SELECT * FROM DEPT01;

 

무결성 제약조건

무결성 : 데이터베이스 내에 데이터의 정확성을 유지

제약조건 : 바람직하지 않는 데이터가 저장되는 것을 방지

- 데이터를 추가, 수정, 삭제하는 과정에서 무결성을 유지할 수 있도록 제약을 주는 것

 

NOT NULL

- NULL을 허용하지 않는다.

UNIQUE

- 데이터의 중복을 막는다.

->CREATE TABLE EMP03(
 EMPNO NUMBER(4) UNIQUE,
 ENAME VARCHAR2(10) NOT NULL,
 JOB VARCHAR2(9),
 DEPTNO NUMBER(4)
);

PRIMARY KEY(식별자)

- NULL과 중복된 값을 허용하지 않는다.(NOT NULL+UNIQUE)

FOREIGN KEY

- 참조되는 테이블의 컬럼 값이 존재하면 허용

->CREATE TABLE EMP05(
 EMPNO NUMBER(4) PRIMARY KEY,
 EMPNAME VARCHAR2(10) NOT NULL,
 JOB VARCHAR2(9),
 DEPTNO NUMBER(4) REFERENCES DEPT(DEPTNO)
);

//무결성 제약조건에 위배된다. 부모키가 없다

INSERT INTO EMP05 VALUES(7566,'JONES','MANAGER',50);

CHECK

- 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만 허용

DEFAULT

- 컬럼에 아무런 데이터도 입력하지 않았을 때 DEFAULT값이 들언간다.

데이터 딕셔너리

- API, 데이터 딕셔너리 뷰는 기본 테이블의 정보를 더 유용하게 보여주기 위해서 기본 테이블을 요약한 형태

DESC USER_TABLES
//사용자 이름

SHOW USER;

//사용자 안에 있는 테이블들 출력
SELECT TABLE_NAME

FROM USER_TABLES

ORDER BY TABLE_NAME DESC;

//제약조건을 알려주는 TABLE

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME

FROM USER_CONSTRAINTS 
WHERE TABLE_NAME='EMP03';

CONSTRAINT -> P(PRIMARY KEY or R(FOREIGN KEY)이 중요

//어느 컬럼에 어느 제약조건이 걸려있는 지 확인

SELECT * FROM USER_CONS_COLUMNS
WHERE TABLE_NAME='EMP03';