A. Oracle 설치

Oracle For Mac

다운 받아야 할 Tool 2가지

oracle xe download
oracle sql developer

2번은 회원 가입 필요함.
SQL 명령어를 통해

OracleXE213_Win64 설치하기 - Window

해당 폴더 내 들어가서 setup.exe 실행
라이센스 계약 동의 후
설치 대상 폴더 선택
- 웬만하면 변경하지 마세요… 지울 때 골치아파지기 때문에 설치 폴더는 변경 X
- 폴더 경로에 한글이나 공백 포함되면 오류 가능성
DB 비밀번호 지정 - 비밀번호 잊어버리면 밀어버려야 하니까 우선 학원에서는 system 으로 통일하기
설치 확인 과정
- SQL 설치 후 CMD 접속하여, sqlplus / as sysdba 명령어 입력하여 다음과 같은 화면이 나오면 정상적으로 설치된 것
- as sysdba 로컬 DB에만 사용 가능한 접속 방식으로 ‘데이터 베이스 관리자’로서 접속한다는 의미
- C:\\Users\\ITWILL 하위에 Oracle 폴더 생성 여부 확인
Database 접속 유저 생성
- 다음 명령어로 C##을 제거하고 접속하도록 할 수 있다.
  1. ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
  2. create user scott identified by tiger;
- 21버전은 유저 name 설정 시 C##을 붙여야만 함 → 실행 X
  - create user c##scott identified by tiger;
  - 만약 user 생성 시 제거하고 싶을 때 drop user c##scott;
Database 유저에게 접속 권한 부여
- Admin으로 접속 sqlplus / as sysdba 명령
- GRANT dba TO scott;
  - 우리가 생성한 scott 유저에게 Database Admin 권한을 부여
Database 접속 - scott 유저
- sqlplus scott/tiger; : / 를 아이디와 비밀번호의 구분자로 이용

SQLDeveloper 관련 설정

설치하는 프로그램이 아니라 실행할 수 있는 파일이 바로 들어있음

sqldeveloper 폴더 내 들어가서 sqldeveloper.exe 파일의 바로가기를 바탕화면에 생성
- 🚨🚨🚨sqldeveloper.exe 파일 자체를 옮겨버리지 않게 주의
  - sqldeveloper 실행 시, 해당 exe 파일이 들어있는 폴더에 관련 폴더나 파일들이 있어야 하는데, 자체를 바탕화면으로 옮기게 되면 경로 문제 등이 발생함
C:\\Users\\ITWILL\\Downloads\\sqldeveloper-23.1.0.097.1607-x64 내에 있는 sqldeveloper를 로컬 디스크 하위에 Tools (eclipse 다운 시 생성했던 폴더)로 이동 (ctrl + x)
바탕화면에서 sqldeveloper 실행 - 첫 접속시 초기화 문제로 시간이 오래 걸림
- 아래 환경설정 임포트 확인은 아니오 선택
DB 생성 - 초록색 플러스 버튼 누르면 아래와 같은 창이 뜸
- Name - DB 이름 (scott)
- 사용자 이름 - scott
- 비밀번호 = tiger
- 우선 테스트 후 왼쪽 아래 상태에 성공
도구 > 환경설정에서 설정할 부분들
- 인코딩 > UTF-8
- 코드 편집기 > 글꼴 : 선택 사항
- 코드 편집기 > 행 여백 > 행 번호 표시 : 활성화 시 해당 행이 몇 번째 줄인지 확인 가능

<aside> 💡 oracle developer는 따로 설치가 필요 없음

oracle은 DB 서버 프로그램이고, devloper는 oracle 서버에 접속하여 명령들을 실행하여 결과들을 볼 수 있는 툴
학습용 버전이여서 좀 빠르게 설치되는 것임

SQLPLUS

DB에 접근할 수 있도록 해주는 연결 프로그램으로 자동으로 설치됨 </aside>

설정 후 scott.sql에서 쌤이 git에 올려준 파일 복사하여 실행하기

ORACLE 실행

sql 문장은 대소문자 구분 X

window 명령어 실행

cls	CMD 창을 깨끗하게 지우는 역할 clear

B. DQL

문장 하나 작성하고 바로바로 실행할 수 있는 언어 → SCRIPT 언어
- 자바스크립트도 개발자 도구에서 한줄씩 명령어를 실행할 수 있잖아! 그래서 스크립트 언어임
- 자바 같은 경우는,

SQL : Structured Query Language

DBMS에서 데이터를 읽고 쓰고 삭제하는 등 데이터를 관리하기 위한 일종의 프로그램 언어
IBM 연구소에서 개발한 SEQUEL(Structured English Query Language)에서 유래
국제 표준기구와 미국국립 표준협회에서 RDBMS의 표준 언어로 SQL을 채택

SQL의 종류

DDL : Data Definition Language
- Create, drop, alter truncate
- 테이블 정의하는 언어
DQL : data query language
- select
DML : Data Manipulation language
- insert update delete
- 테이블 내 값들을 조작할 때 사용하는 언어
TCL : Transaction Control Language
- Commit Rollback

PL/SQL: Procedural Language Extension to SQL

SQL을 이용한 절차적 프로그래밍 언어
변수 선언과 사용, 제어문(IF, CASE, Loop) 함수, 프로시저, 예외처리 등 → 학원에서는 사용하지 않아요.
Oracle에서만 사용하는 PL/SQL

Table → 행과 열로 이루어져 있음

열, variables, fields, attributes
행, observation (관측), records, examples
- 한 인스턴스의 속성들을 관찰하는 것이기 때문에 observation이라고 하는 것.
Java에서 DBMS를 사용하게 될 때, 컬럼은 멤버 변수가 되게 된다. (즉, 한 테이블이 한 클래스를 구성할 것)

/**/  블락주석

-- inline comment
-- SQL 문장은 세미콜론으로 끝남

Ctrl + Enter
1. 현재 커서가 있는 위치의 한 문장을 실행
2. 만약 여러 문장을 드래그로 한 번에 선택하고 있다면 복수의 명령어가 실행됨
SQL 문장에서 명령어, 테이블 명, 컬럼명은 대소문자를 구분하지 않음
하지만 Oracle에서 저장하는 테이블 명들을 저장하는 테이블을 조회할 때는 대문자로 사용해야함.
- 오라클은 테이블 명을 대문자로 저장
Cntl + S : 다른 이름으로 저장 ⇒ workspace > lab_sql에 저장
Alt + F10 : 새로운 sql 워크시트 만들기

Q. 직원 테이블에서 사번, 이름 검색하는 문장 작성하기

emp : 직원 테이블, dept : 부서 테이블
SELECT EMPNO, ENAME FROM EMP;

Q. 부서 테이블에서 모든 컬럼을 검색 : 와일드 카드 사용 OR 모든 컬럼 나열

*은 모든 컬럼을 의미하고 와일드 카드라고 칭하기도 함
SELECT * FROM DEPT;

Q. 컬럼 별칭 주기 (alias) : as “별명”

SELECT DEPTNO AS “부서 번호”, DNAME AS “부서 이름” FROM DEPT;

별명을 줄 때에는 반드시 큰 따옴표 (””)만 사용
별명 이외의 문자열 데이터를 표현할 때는 작은 따옴표만 사용해야 함.

Q. 부서 테이블을 검색해서 '... 부서는 ...에 있습니다.' 형식으로 결과 출력

SELECT dname || ' 부서는 ' || loc|| '에 있습니다.' AS location FRom dept;

B-2. 검색

projection : 테이블에서 원하는 컬럼들을 선택 (투영, 사상)
selection : 테이블에서 조건을 만족하는 행(record)들을 검색

SELECT [COLUMN_NAME], [COLUMN_NAME], ... FROM [TABLE_NAME] WHERE [CONDITION] ORDER BY [COLUMN_NAME][ORDER_CONDITION], [COLUMN_NAME][ORDER_CONDITION];

조건식에서 사용되는 연산자들
1. 비교 연산자 : = , !=, >, >= , <=, <, IS NULL, IS NOT NULL
2. 논리 연산자 : AND, OR, NOT

직원 테이블에서 10번 부서에서 근무하는 직원들의 부서번호, 사번, 이름 출력
- 정렬 순서 : (1) 부서번호, (2) 사번
직원 테이블에서 수당(COMM)이 NULL이 아닌 직원들의 사원, 부서번호, 이름, 수당 출력
- 정렬 순서 : 사번
직원 테이블에서 급여가 2000 이상인 직원들의 이름, 직무, 급여를 출력하세요.
직원 테이블에서 10 번 부서 또는 20번 부서에서 근무하는 부서 번호,, 이름, 급여 검색
- 정렬 순서 : 부서 번호, 오름차순 , (2) 급여 내림차

암시적 형 변환

오라클은 HIREDATE 컬럼의 값을 문자열 타입으로 변환해서 ‘1987-01-01’ 문자열과 크기 비교를 수행 - 암시적 타입 변환

/*
ORACLE FUNCTION
    (1) 단일 행 함수 : 
        행 하나씩 함수의 아규먼트로 전달되고, 행 마다 하나씩 결과가 리턴되는 함수
        EX) TO_DATE, TO_CHAR, LOWER, UPPER, ...
    (2) 다중 행 함수 : 
        여래 개의 행이 함수의 아규먼트로 전달되고, 하나의 결과가 리턴되는 함수
        EX) 통계 관련 함수 : COUNT, SUM, AVG, MIN, VARIANCE (분산), STDDEV (표준편차)
*/

-- 단일 행 함수 예 1) - 문자열 대소문자 변환: 
SELECT ENAME, LOWER(ENAME), JOB , LOWER(JOB) FROM EMP; -- 대문자 -> 소문자 변환 : LOWER(값)

SELECT ENAME, LOWER(ENAME), UPPER(LOWER(ENAME)),JOB , LOWER(JOB), UPPER(LOWER(JOB)) FROM EMP; -- 소문자 -> 대문자 변환 : UPPER(값)

SELECT ENAME, INITCAP(ENAME), JOB, INITCAP(JOB) FROM EMP; -- 첫 문자가 대문자, 이후 문자 소문자인 문자열로 변환 : INITCAP(값)

-- 단일 행 함수 예 2) - TO_CHAR(값) : 다른 타입의 값을 문자열로 변환. 날짜 -> 문자열
SELECT HIREDATE, TO_CHAR(HIREDATE,  'YY/mm/DD HH24:mm:SS'), TO_CHAR(HIREDATE,  'YY/MM/DD HH24:MI:SS')  FROM EMP;
--MM/mm의 경우, 날짜 형식 내에서 '월'을 의미하고 분을 의미할 때는 `MI`로 푠현하여야 한다.

-- RR은 무조건 현재와 가까운 년도. 81 이면 1981년이 2081년보다 가깝기 때문에 1981년으로 인식
-- YY는 무조건 현재 세기
SELECT TO_CHAR(TO_DATE('81-01-01', 'RR-MM-DD'), 'RRRR-MM-DD'), TO_CHAR(TO_DATE('81-01-01', 'YY-MM-DD'), 'YYYY-MM-DD')  FROM DUAL;

-- 단일 행 함수 예 3) - NVL (A, B) : A가 NULL이면 B를 리턴하고, NULL이 아니면 원래 값을 리턴함.              // NULL_VALUE의 약어
--                         - NVL2(A, B, C) : A가 NULL이 아니면 B을 리턴하고, NULL이면 C를 리턴 

SELECT COMM, NVL(COMM, -1) FROM EMP;
SELECT COMM, NVL(COMM, -1), NVL2(COMM, COMM, -1) FROM EMP;

--COUNT(컬럼) : NULL이 아닌 행의 개수를 리턴
SELECT COUNT(EMPNO), COUNT (MGR), COUNT(COMM)
FROM EMP;

SELECT COUNT(*) FROM EMP; --테이블의 행의 개수

**-- 통계함수 : 합계, 평균, 최댓값, 최솟값, 분산, 표준편차**
SELECT 
    SUM (SAL), 
    AVG(SAL), 
    MAX(SAL), 
    MIN(SAL), 
    VARIANCE (SAL) , 
    STDDEV(SAL)
FROM EMP;

**-- 분산 표준편차 : 평균에서 해당 값이 얼마나 떨어져 있느냐**

-- 단일행 함수와 다중행 함수는 함께 SELECT 할 수 없음
-- SELECT NVL(sal, 0), SUM(SAL) FROM EMP;

**/*
GROUP BY 쿼리 
    EX ) 부서별 급여 평균, 부서별 인원수
    
    - 문법
    SELECT 
        [컬럼명], 
        [함수 호출]....
    FROM [테이블]
    WHERE [조건식](1)
    GROUP BY 그룹 별로 묶어줄 변수 (컬럼), ...
    HAVING 조건식(2) // 그룹 안에서 취사선택 할 때의 조건식 : HAVING 은 GROUP BY가 있어야 사용할 수 있다. - GROUP BY의 WHERE 절인 셈이지.
    ORDER BY 정렬 기준 변수 (컬럼), ... ;
*/**

SELECT DEPTNO, ROUND(AVG(SAL), 2) 급여평균 FROM EMP
WHERE COMM IS NULL
GROUP BY DEPTNO
ORDER BY DEPTNO;

-- 내가 생각한 늑낌...
**-- 1. WHERE 조건을 기준으로 SELECT문의 대상의 컬럼들을 포함하여 레코드들을 불려온다.
-- 2. (1)을 통해 불러온 레코드들 중 HAVING에 따라 또 걸러서 최종 사용할 데이터들을 추려낸다.
-- 3. GROUP BY 를 통해 그룹으로 나누고, 해당 그룹 별 집계함수 결과를 도출한다.
-- 4. ORDER BY 로 정렬이 됨 : 이 때만 별칭을 사용할 수 있다.** 

--SELECT ENAME, SAL, SAL -AVG(SAL) FROM EMP
--GROUP BY ENAME, SAL;

-- 모든 문제에서 소수점은 반올림해서 소수점 이하 2자리까지 표시
-- EX1 ) 부서별 급여 평균, 표준편차를 부서번호 오름차순으로 출력
SELECT DEPTNO, ROUND(AVG(SAL),2) 급여평균,  ROUND(STDDEV(SAL), 2) 급여표준편차 FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;
-- EX2 ) 직무별 직무, 직원수, 급여 최댓값, 최솟값, 평균을 직무 오름차순으로 출력
SELECT JOB, COUNT(JOB) 직원수, MAX(SAL) 급여최댓값,  MIN(SAL) 급여최솟값, ROUND(AVG(SAL), 2) 급여평균
FROM EMP GROUP BY JOB ORDER BY JOB;
-- EX3 ) 부서별, 직무별 부서 번호, 직무, 직원수, 급여 평균 검색 +  정렬 : 부서 번호, 직무 순
-- 10번 부서 안에 직무별로 묶어서 또 
SELECT DEPTNO, JOB, COUNT(EMPNO), ROUND(AVG(SAL), 2) FROM EMP GROUP BY DEPTNO, JOB ORDER BY DEPTNO, JOB;
-- EX4 ) 입사연도별 사원수를 검색

SELECT HIREDATE FROM EMP;

SELECT TO_CHAR(HIREDATE, 'YY') AS 입사년도, COUNT(EMPNO) AS 당해_입사한_사원수 FROM EMP 
GROUP BY TO_CHAR(HIREDATE, 'YY')
ORDER BY 입사년도;

--엘리야스는 GROUP BY 에서는 사용하지 못한다.
--헤맸던 이유 : ALIAS 별칭 사용 범위
--group by 입사연도;

--WHERE 절은 테이블에서 조건에 맞는 행들을 선택하 때
-- HAVING 절은 WHERE 절 실행까지 한 후 걸러진 레코드들에서 조건에 맞는 그룹을 선택할 때

-- 부서별 급여 평균
SELECT DEPTNO, AVG(SAL) AS SALSAL
FROM EMP
GROUP BY DEPTNO
;
--
--
-- 1 MGR 컬럼이 NULL이 아닌 직원 들 중에서 부서별 급여 평균을 검색
--   정렬 부서 번호 오름차
SELECT 
    DEPTNO, ROUND(AVG(SAL),2) 급여평균
FROM EMP 
WHERE MGR IS NOT NULL
GROUP BY DEPTNO
ORDER BY DEPTNO;
--2. 직무별 사원 수 검색. PRESIDENT 제외 : 직무별 사우너수가 3명 이상인 직무만 검색
-- 정렬 순서 : 직무 오름차
SELECT JOB, COUNT(EMPNO) 사원수 FROM EMP
WHERE JOB <> 'PRESIDENT'
GROUP BY JOB
HAVING COUNT(EMPNO) >= 3
ORDER BY JOB;

-- 3. 입사연도 부서번호 입사연도별 부서별 사원 수 검색
-- 1980년은 검색에서 제외
-- 연도별 부서별 사ㅇ원수가 2명 이상인 경우만 선택 / 연도별 부서별 오름차순 출력
SELECT TO_CHAR(HIREDATE, 'YYYY') 입사연도, DEPTNO, COUNT(EMPNO) 사원수
FROM EMP
WHERE TO_CHAR(HIREDATE, 'YYYY') != '1980'
GROUP BY TO_CHAR(HIREDATE, 'YYYY') , DEPTNO
ORDER BY TO_CHAR(HIREDATE, 'YYYY') , DEPTNO;

https://im-first-rate.tistory.com/130

B-3. 서브쿼리

UPDATE나 DELETE 같은 조건문 내 혹은 값에서도 사용 가능

SUBQUERY : SQL문장의 일부로 다른 SQL 문장이 포함되는 것

단일행 서브쿼리 VS 다중행 서브쿼리

단일행 서브쿼리 : 서브쿼리의 결과 행이 1개 이하인 경우
단일행 서브 쿼리는 단순 비교 (=, !=, >, >=, <, <=)를 할 수 있음.
다중행 서브쿼리 : 서브 쿼리의 결과행이 2개 이상인 경우.
다중행 서브쿼리는 단순 비교를 할 수 없다.
다중행 서브쿼리에서는 in, any, all 과 같은 키워드 함께 사용

단일행 서브쿼리

Q. 급여 평균보다 더 많은 급여를 받는 직원들
SELECT * FROM EMP, (SELECT ROUND(AVG(SAL), 2) MEAN FROM EMP) AVG_SAL WHERE SAL >= MEAN; SELECT * FROM EMP WHERE SAL >= (SELECT AVG (SAL) MEAN FROM EMP); -- 컬럼을 노출하지 않는 경우라면, 아래 SQL문과 같이 조건 절에 직접 사용할 수 있다. SELECT ENAME, SAL, SAL - ROUND((SELECT AVG(SAL) FROM EMP)) AS 차이 FROM EMP;
Q. ALLEN 보다 적은 급여를 받는 직원들의 사번, 이름, 급여를 검색
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL < (SELECT SAL FROM EMP WHERE ENAME = 'ALLEN');
Q. WARD의 연봉보다 더 많은 연봉을 받는 직원들의 이름, 급여, 수당, 연봉을 검색ALLEN과 같은 직무의 직원들의 사번, 이름, 부서 번호, 직무, 급여를 검색
SELECT ENAME, SAL, COMM, (SAL * 12 + NVL(COMM, 0)) FROM EMP WHERE SAL * 12 + NVL(COMM, 0) > (SELECT SAL * 12 + NVL(COMM, 0) FROM EMP WHERE ENAME = 'WARD');

-- 각 부서에서 급여를 가장 많이 받는 직원의 레코드(모든 컬럼) 검색
--SELECT 
--    * 
--FROM EMP
--WHERE (DEPTNO, SAL) IN (부서별 최댓값);

SELECT * FROM EMP
WHERE (DEPTNO, SAL) IN (
 SELECT DEPTNO,  MAX(SAL) FROM EMP GROUP BY DEPTNO
);

SELECT * FROM EMP
WHERE (DEPTNO, SAL) IN (
    SELECT DEPTNO, MIN(SAL) FROM EMP GROUP BY DEPTNO
);

-- =, <, ... 등의 단순비교는 불가능함.

-- 다중행 서브 쿼리인 경우, 쿼리에서 ANY VS ALL :
-- (1) ANY : 여러 개 중에서 하나
-- (2) ALL : 여러 개 모두 (전부)
--
SELECT  * FROM EMP
WHERE SAL < (
    SELECT SAL FROM EMP WHERE DEPTNO = 10
); -- COMPILE ERROR가 나지는 않지만 실행 시 ERROR가 남
-- 열이 여러 개 나오기 때문
 

SELECT * FROM EMP
WHERE SAL < ANY (
    SELECT SAL FROM EMP WHERE DEPTNO = 10
); -- 5000이하

SELECT * FROM EMP
WHERE SAL < ALL (
    SELECT SAL FROM EMP WHERE DEPTNO = 10
); -- 1300 이하여야 해, SUBQUERY에서 도출된 어떤 결과보다도 작아야 함.

SELECT * FROM EMP
WHERE SAL = ANY (
    SELECT SAL FROM EMP WHERE DEPTNO = 10
);

SELECT * FROM EMP
WHERE (DEPTNO, SAL) IN (
    SELECT DEPTNO, MIN(SAL) FROM EMP GROUP BY DEPTNO
);

C. DML

C-1. INSERT

새로운 값으로 행 새로 생성하기

INSERT INTO TABLE (COL, ...) VALUES (VAL, ...);

컬럼들 나열 시, 나열한 컬럼 순서와 일치하게 값들을 나열하여 VALUES 뒤에 명시해야 함
컬럼 나열하지 않을 시 해당 테이블 컬럼 나열 순서와 동일한 값을 모두 넣어줘야 함.

다른 테이블의 값으로 행 새로 생성하기

INSERT INTO {값을 삽입할 테이블 이름}
SELECT {컬럼명 나열} FROM {값을 가져올 테이블 이름}
[WHERE 조건절];

Q. EMP 테이블에서 COMM이 NULL이 아닌 레코드를 BONUS 테이블에 삽입

INSERT INTO BONUS SELECT ENAME, JOB, SAL, COMM FROM EMP WHERE COMM IS NOT NULL;

C-2. UPDATE : 테이블 테이터 업데이트

UPDATE {테이블 이름} SET {변수(컬럼)=값}, {변수(컬럼)=값}, ... [WHERE 조건식];

C-3. DELETE : 테이블에서 행을 삭제하는 DML

DELETE FROM {테이블명} [WHERE 조건식];

조건식을 작성하지 않으면 해당 테이블에 모든 데이터가 삭제된다.

D. DDL (Data Definition Language)

D-1. ALTER TABLE

이름 변경 : ALTER TABLE ... RENAME …
- 테이블, 컬럼, 제약조건의 이름 등을 변경할 수 있음
- 오라클에서 테이블들을 관리하기 위한 테이블 : USER_TABLES
- SELECT TABLE_NAME FROM USER_TABLES;
1. 테이블의 이름 변경
2. ALTER TABLE {원래 테이블 이름} RENAME TO {변경할 테이블 이름};
3. 컬럼 이름 변경 : STUNAME → NAME
4. ALTER TABLE {테이블 이름} RENAME COLUMN {원래 컬럼 이름} TO {변경할 제약조 이름};
5. 컬럼 이름 변경 : STUNAME → NAME
6. ALTER TABLE {테이블 이름} RENAME CONSTRAINT {원래 제약조건 이름} TO {변경할 제약조건 이름};
추가 : ALTER TABLE ... ADD …
- 컬럼, 제약 조건 추가
1. 제약조건 (CONSTRAINT) 추가 : STUNO 컬럼에 PK 제약 조건 설정
2. ALTER TABLE {테이블 이름} ADD CONSTRAINT {제약조건 이름} PRIMARY KEY ({컬럼명});
3. 컬럼 추가 🚨주의 : ADD COLUMN이라고 안함 그냥 ADD
4. ALTER TABLE {테이블 이름} ADD {컬럼 이름} {데이터 타입};
삭제 : ALTER TABLE ... DROP …
- 컬럼, 제약 조건 삭제
1. 컬럼 삭제
2. ALTER TABLE {테이블 이름} DROP COLUMN {컬럼 이름};
3. 제약조건 삭제1
4. ALTER TABLE {테이블 이름} DROP CONSTRAINT {제약 조건 이름};
수정 : ALTER TABLE ... MODIFY …
- 컬럼의 데이터 타입 변경, 제약 조건 수정
1. 컬럼 데이터 타입 수정
2. ALTER TABLE {테이블 이름} MODIFY COLUMN {컬럼명} {변경할 데이터 타입};

D-2. [추가] CREATE TABLE … AS SELECT … FROM …

E. CREATE TABLE 시 제약조건 와 연관

테이블을 생성하고 데이터를 넣은 상태에서 테이블을 쪼개야 할 때 사용한다.

CREATE TABLE ... AS SELECT ...

테이블의 모양(컬럼 이름, 데이터 타입) 과 데이터를 복사

EMP 테이블의 전체 데이터를 EX_EMP3 테이블을 생성하면서 복사하기

CREATE TABLE EX_EMP3 AS SELECT * FROM EMP;

EMP 테이블의 모양(컬럼, 데이터 타입)만 똑같은 테이블 EX_EMP4을 생성

CREATE TABLE {새로 생성할 테이블명} AS SELECT {복사할 컬럼명 나열} FROM {복사할 테이블명}
WHERE {절대로 만족하지 못할 조건절};

CREATE SEQUENCE .. : AUTO INCREMENT 같은 자동 증분값 만들어주는 듯?

D-3. TRUNCATE TABLE : 테이블의 모든 행을 삭제하는 DDL

TRUNCATE TABLE {TRUNCATE를 수행할 테이블};

TRUNCATE 수행 시 노트를 찢어버리는 것과 동일.
- 테이블 자체는 남아있음.

D-4. DROP TABLE : 테이블을 삭제하는 DDL

DROP TABLE {DROP을 수행할 테이블};

아무런 데이터나 메모리가 남지 않고 테이블 자체를 삭제
이 DDL을 수행한 후 SELECT를 이용해서 해당 테이블에 접근하면 오류가 발생

Q. 1987년에 입사한 직원 삭제하기

TO_DATE 함수 사용

DELETE FROM EMP WHERE HIREDATE BETWEEN TO_DATE ('1987/01/01') AND TO_DATE('1987/12/31');

DELETE FROM EMP WHERE '1987' = TO_CHAR(HIREDATE, 'YYYY'); SELECT * FROM EMP WHERE '1987' = TO_CHAR(HIREDATE, 'YYYY');

E. CREATE TABLE 시 제약조건

교재 104-108

제약조건 목록

PRIMARY KEY (고유키) : NOT NULL 제약조건과 UNIQUE 제약조건 모두 충족
NOT NULL
UNIQUE
CEHCK : INSERT하려는 값이 어떤 조건을 만족해야 한다.
FOREIGN KEY (외래키)

E-1. 제약 조건 추가

방식 1. 컬럼 정의 시 제약 조건 함께 명시

CREATE TABLE EX1 (
    ID NUMBER(2) PRIMARY KEY,
    NAME VARCHAR2(10 CHAR) NOT NULL, 
    PHONE VARCHAR2 (13 CHAR) UNIQUE,
    AGE NUMBER(3) CHECK (AGE >= 0),
    MEMO VARCHAR2 (1000 CHAR)
);

INSERT INTO EX1
VALUES (1, '오쌤', '010-7252-7493', 16, '안녕하세');

INSERT INTO EX1 (ID, NAME) VALUES (1, '홍길동');
--> 고유키 (PK)  제약조건 위배 :  PK는 유일해야 함

INSERT INTO EX1 (NAME) VALUES ('홍길동');
-- >  PK 제약 조건 위배 : PK는 NULL이 될 수 없음.

SELECT * FROM EX1;

방식 2. 컬럼 정의 시 제약조건 함께 명시

(+ 제약 조건 이름 붙이기)

CREATE TABLE EX2 (
    ID NUMBER(4)
        CONSTRAINT EX2_ID_PK PRIMARY KEY,
    NAME VARCHAR2(10 CHAR)
        CONSTRAINT EX3_NAME_NN NOT NULL,
    PHONE VARCHAR2(13 CHAR)
        CONSTRAINT EX3_PHONE_UQ UNIQUE,
    GENDER VARCHAR2(1 CHAR)
        CONSTRAINT EX2_GENDER_CHCK CHECK (GENDER IN ('M','F'))
);

방식 3. 컬럼 정의 후 제약 조건 추가

CREATE TABLE EX3 (
 -- 컬럼 정의 (이름, 데이터 타입)
    ID NUMBER(4),
    NAME VARCHAR2(10 CHAR),
    PHONE VARCHAR2(13 CHAR),
    GENDER VARCHAR2(1 CHAR),
--  제약조건 정의 (제약 조건 이름 & 내용)
    CONSTRAINT  EX3_ID_PK PRIMARY KEY (ID),
    CONSTRAINT EX3_NAME_NN **CHECK (NAME IS NOT NULL)**,
    CONSTRAINT EX3_PHONE_UQ UNIQUE(PHONE),
    CONSTRAINT EX3_GENDER_CECHK CHECK (GENDER IN ('M','F'))
);

🚨🚨🚨NOT NULL의 경우에는 위와 같이 CHECK 제약 조건의 조건으로 추가하여야 한다.
- 만약 컬럼 정의시에 제약조건 명시와 같이 다음과 같이 쓴다면 에러 발생 CONSTRAINT EX3_NAME_NN **NOT NULL**

E-2. FOREIGN KEY

FOREIGN KEY는 실무에서도 제일 마지막에 넣는 경우가 많다.
FOREIGN KEY 제약 조건을 먼저 주게 되면 테이블 생성 순서에 영향을 주기 때문이다.
다른 테이블의 고유키 (PK)를 참조하는 제약조건
- 데이터를 INSERT할 때 다른 테이블의 PK에 없는 값이 INSERT 되지 않도록 하기 위함
- 테이블을 생성할 때 FK를 설정하려면, PK가 있는 다른 테이블이 먼저 생성되어 있어야 함.
FOREIGN KEY 컬럼 정의 내에서 설정하기
- DATA TYPE은 참조하는 PK 값과 동일하게 설정되면 됨.
CREATE TABLE EX_EMP1 ( EMPNO NUMBER(4) CONSTRAINT EX_EMP1_EMPNO_PK PRIMARY KEY, ENAME VARCHAR2(100 CHAR) NOT NULL, **DEPTNO NUMBER(2) CONSTRAINT EX_EMP1_DEPTNO_FK REFERENCES EX_DEPT (DEPTNO)** );
FOREIGN KEY 생성 후 데이터 삽입 순서 주의
- 만약 EX_DEPT 테이블에 데이터를 삽입하지 않고 EX_EMP1 에 다음의 삽입 시도 시 에러 발생 : INSERT INTO EX_EMP1 VALUES (1000, '오쌤', 10);
- 에러가 발생하지 않고 실행되는 SQL 순서
- INSERT INTO EX_DEPT VALUES (10, '아이티윌'); INSERT INTO EX_EMP1 VALUES (1000, '오쌤', 10);
- 🚨🚨DB 만들 시, FOREIGN KEY 제약 조건을 추가하기 전에 INSERT나 검색 등의 SEQUENCE를 먼저 충분히 테스트해보는 것을 추천

FOREIGN KEY 방법 2. 컬럼 선언부와 제약조건 명시부를 분리하기

constraint 제약 조건 이름 설정할 때
CREATE TABLE EX_EMP2 ( -- 컬럼 선언부 EMPNO NUMBER(4), ENAME VARCHAR2(100 CHAR), DEPTNO NUMBER(2), -- 컬럼 선언부 종료 CONSTRAINT EX_EMP2_EMPNO_PK PRIMARY KEY (EMPNO), CONSTRAINT EX_EMP2_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES EX_DEPT (DEPTNO) );
단순히 primary key 와 foreign key를 설정할 때
CREATE TABLE EX_EMP2 ( -- 컬럼 선언부 EMPNO NUMBER(4), ENAME VARCHAR2(100 CHAR), DEPTNO NUMBER(2), -- 컬럼 선언부 종료 PRIMARY KEY (EMPNO), FOREIGN KEY (DEPTNO) REFERENCES EX_DEPT (DEPTNO) );

→ 2번의 경우,

DELETE VS TRUNCATE VS DROP

DELETE FROM {테이블 이름}

테이블의 모든 행의 데이터를 삭제, 테이블의 크기가 줄어들지는 않음
- 테DEDLETE 명령어 실행 시 데이터가 들어가있던 메모리 크기는 동일하고 내부 데이터만 삭제
- 비유하시길 지우개로 지우는 것

TRUNCATE TABLE {테이블 이름}

테이블의 모든 행의 데이터를 삭제하고, 행 자쳊도 삭제
- 테이블은 남아있으나 컬럼만 남아있는 상태
- 비유하시길 노트를 찢어버리는 것

DROP TABLE {테이블 이름}

테이블 자체를 DBMS에서 삭제
- 그냥 테이블 자체를 삭제

만약을 대비하여 백업 디비를 생성해놓음 (DB 이중화라고 함)

어떤 주기로 백업을 받을 거냐를 DBA가 설정하여 최소한의 피해를 보장할 수 있도록 한다.

F. 세션과 읽기 일관성

F-1. 세션

데이터베이스 접속을 시작으로 접속을 종료하기까지 전체 기간
하나의 세션에는 여러개의 트랜젝션이 존재

F-2. 하나의 단위로 데이터를 처리하는 트랜잭션

트랜잭션
- 더 이상 분할할 수 없는 최소 수행 단위
- INSERT, UPDATE, DELETE 등의 데이터 조작문 1줄이라고 생각하면 됨

TCL (TCL)

트랜젝션을 제어하기 위해 사용하는 명령어
- COMMIT
- ROLLBACK
- 바로 이전에 COMMIT된 상태까지로 되돌아감
한 개 이상의 데이터 조작 명령어로 이루어짐
DDL이 실행되면 자동으로 COMMIT 되기 때문에 이전에 수행한 DML이 영구히 데이터베이스에 반영됨
- DDL이 실행되면 ROLLBACK을 사용한 실행 취소가 불가능

G. (내기준)신가한 SQL문법

WITH {가상 테이블명} AS (서브쿼리, 생성할 가상의 테이블)

WITH T AS ( 
    SELECT 
        DEPARTMENT_ID, AVG(SALARY) AS AVG_SAL
        FROM EMPLOYEES
        GROUP BY DEPARTMENT_ID
)
SELECT DEPARTMENT_ID, ROUND (AVG_SAL,1)
FROM T
WHERE AVG_SAL = (
    SELECT MAX(AVG_SAL) FROM T
);

G-1. 시퀀스 → 자동증분수

SEQUENCE : 오라클은 MYSQL 과 다르게 SEQUENCE를 만들어 ID값이 자동으로 증가되게 할 수 있음
- TRIGGER/SEQUENCE ⇒ 어떤 상황에서 어떤 과정이 수행될 것인지를 만들어줘야 함.
SQL DEVELOPER 가 만들어준 SEQUENCE 문장은 다음과 같음.
CREATE TRIGGER BLOGS_TRG BEFORE INSERT ON BLOGS FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF INSERTING AND :NEW.ID IS NULL THEN SELECT BLOGS_SEQ.NEXTVAL INTO :NEW.ID FROM SYS.DUAL; END IF; END COLUMN_SEQUENCES; END;