2-1 SQL 기본

7. join

ORACLE 표준은 테이블 나열 순서 중요하지 X, ANSI 표준은 OUTER JOIN 시 순서 중요
ORACLE 표준과 ANSI 표준이 서로 다름

조인종류

• 조건 형태
  1) EQUI JOIN(등가 JOIN) : JOIN 조건이 동등 조건인 경우
  2) NON EQUI JOIN: JOIN 조건이 동등 조건이 아닌 경우

• 조건 결과
  1) INNER JOIN : JOIN 조건에 성립하는 데이터만 출력하는 경우
  2) OUTER JOIN: JOIN 조건에 성립하지 않는 데이터도 출력하는 경우
  (LEFT/RIGHT/FULL OUTER JOIN 으로 나뉨)

• NATURAL JOIN : 조인조건 생략 시 두 테이블에 같은 이름으로 자연 연결되는 조인
  이름 같은 컬럼으로 알아서 조인.
  컬럼 이름이 같고, 의미가 다른 경우에도 조인됨 → 엉뚱한 결과 나올 수 있음

student

student_id	name	major
1	Alice	CS
2	Bob	EE

enrollment

student_id	course	grade
1	DB	A
2	AI	B

SELECT *
FROM student
NATURAL JOIN enrollment;

student_id	name	major	course	grade
1	Alice	CS	DB	A
2	Bob	EE	AI	B

• CROSS JOIN : 조인조건 생략 시 두 테이블의 발생 가능한 모든 행을 출력하는 조인
• SELF JOIN: 하나의 테이블을 두 번 이상 참조하여 연결하는 조인

EQUI JOIN

조인 조건이 '='(equal) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 얻는 조인
FROM 절에 조인하고자 하는 테이블을 모두 명시

select 테이블1.컬럼, 테이블2.컬럼
from 테이블1, 테이블2
where 테이블1.컬럼 = 테이블2.컬럼;

join 없이 from에 여러개의 테이블명을 적는경우 where에다 on적듯이 적음!

NON-EQUI JOIN

조인 컬럼에 대한 비교 조건이 '<', BETWEEN A AND B 와 같이 '=' 조건이 아닌 연산자를 사용하는 경우의 조인조건

select 테이블1.컬럼, 테이블2.컬럼
from 테이블1, 테이블2
where 테이블1.컬럼 비교조건 테이블2.컬럼;

self join

한 테이블 내 각 행끼리 관계를 갖는 경우 사용하는 조인 기법
한 테이블을 참조할 때마다(필요할 때마다) 명시해야 함, 테이블명이 중복되므로 반드시 테이블 별칭 사용

8. 표준 조인

join

방식	설명	예시
✅ 전통적 조인 (Oracle 전통 스타일)	FROM A, B로 테이블 나열 + WHERE에 조건	sql SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
✅ ANSI 조인 (표준 SQL 스타일)	JOIN ~ ON 사용	sql SELECT * FROM emp JOIN dept ON emp.deptno = dept.deptno;

USING 조건절

• 조인할 컬럼명이 같을 경우 사용
• Alias 나 테이블 이름 같은 접두사 붙이기 불가
• 괄호 필수

항목	ON	USING
사용 방식	자유롭게 조건 지정 가능	컬럼명이 두 테이블에 같을 때만 사용 가능
조건 표현	table1.col1 = table2.col1 식	USING (col1) 이렇게 컬럼명만 씀
결과 컬럼 이름	table1.col1, table2.col1 둘 다 표시됨	col1 한 번만 나옴 (중복 제거)
가독성	복잡한 조인 조건 가능	간단하고 명확하지만 제약 있음

NATURAL JOIN

• 두 테이블 간의 동일한 이름을 가지는 모든 컬럼들에 대해 EQUI JOIN 을 수행
• USING, ON, WHERE 절에서 조건 정의 불가
• JOIN 에 사용된 컬럼들은 데이터 유형이 동일해야 하며 접두사를 사용불가

SELECT emp.ename, dept.dname
FROM emp
NATURAL JOIN dept;

-- dept앞에 테이블명 쓰지 말라는 뜻(접두사 사용불가)

outer join

테이블 기준 방향에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN 으로 구분
OUTER 생략 가능(LEFT OUTER JOIN -> LEFT JOIN)

• FULL OUTER JOIN
  두 테이블 전체 기준으로 결과를 생성하여 중복 데이터는 삭제 후 리턴
  LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과의 UNION 연산 리턴과 동일함
  ORACLE 표준에는 없음

ORACLE JOIN

SELECT ...
FROM A, B
WHERE A.id = B.id(+);


-- 예제 

SELECT e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id(+);


-- ANSI
SELECT e.name, d.dept_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.dept_id = d.dept_id;

☞ ORACLE 표준은 WHERE 절에 조인 조건을 작성하므로 LEFT OUTER JOIN 을 기술하지 X
☞ WHERE 절에서 기준이 되는 테이블(STUDENT) 반대 테이블 조건 컬럼 뒤에 (+)를 붙임

B 테이블에 (+) => A를 기준으로 LEFT OUTER JOIN 하겠다는 뜻
즉, 왼쪽(A)은 기준 테이블이고, 오른쪽(B)은 NULL로 채워질 수 있는 테이블

(+)는 NULL이 들어갈 수 있는 곳에 붙임

2-3 관리구문

17. DML

DML(Data Manipulation Language) 데이터 조작

• 데이터의 삽입(INSERT), 수정(UPDATE), 삭제(DELETE), 병합(MERGE)
• 저장(commit) 혹은 취소(rollback) 반드시 필요

INSERT

• 한 번에 한 행만 입력가능(SQL Server. 여러 행 동시 삽입 가능)
• INTO 절에 컬럼명을 명시하여 일부 컬럼만 입력 가능. 작성하지 않은 컬럼은 NULL 이 입력됨
  ☞ NOT NULL 컬럼의 경우 오류 발생

insert into 테이블명(컬럼1, 컬럼2, ...) values (값1, 값2 ,... ) 
-- 선택한 컬럼만 테이터 입력

• 전체 컬럼에 대한 데이터 입력시 테이블명 뒤의 컬럼명 생략 가능

Insert into 테이블명 values (값1,값2, ..); 
-- 전체 컬럼 값을 입력

• 컬럼 명시 생략으로 인한 오류
  테이블 구조
  
  insert into EMP3 values(2, 'B') 하면 오류
  테이블명 뒤에 insert 할 컬럼명 명시x -> 모든컬럼 insert 시도
  컬럼은 8개인데 입력한 값이 2개밖에 없어서 에러남
  insert into EMP3(EMPNO, ENAME)) values(2, 'B') 하면 해결

update

• 데이터 수정할 때 사용
• 컬럼 단위 수행

update 테이블명
set 수정컬럼명 = 수정값
where 조건

update 뒤에 테이블명오는거 기억~!

• 다중컬럼 수정 가능

-- 방법1.
update 테이블명
set 수정컬럼명1 = 수정값1, 수정컬럼명2 = 수정값2, ...
where 조건

-- 방법2.
update 테이블명
set (수정컬럼명1, 컬럼명2, ...) = (select 수정값1, 수정값2, ..)
where 조건

delete

• 행 단위 실행

delete from 테이블명
where 조건 ;

merge

• 데이터 병합
• 참조 테이블과 동일하게 맞추는 작업(참조테이블의 데이터 입력, 참조테이블의 값으로 수정 등)
  ☞ INSERT, UPDATE, DELETE 작업을 동시에 수행

merge into 테이블명
using 참조테이블
on (연결조건) -- 괄호필수

when matched then
  update -- 테이블명 생략
  set
  delete 조건
  
when not matched then
  insert values(값1, 값2, ...);

지피티야 도와줘 ~
회원정보가 저장된 users 테이블이 있고,
외부 시스템에서 가져온 새 회원/수정된 회원 정보는 users_staging 테이블에 있어.
우리는 최신화된 데이터를 users 테이블에 업데이트, 없으면 삽입, 일부 조건은 삭제해야 해.

1. users (기존 회원 테이블)

id	name	status
1	Alice	active
2	Bob	active
3	Charlie	inactive

2. users_staging (새로운/수정된 회원 정보)

id	name	status
2	Bob	inactive
4	Daisy	active

MERGE INTO users u
USING users_staging s
ON (u.id = s.id)

WHEN MATCHED THEN
    UPDATE SET u.name = s.name,
               u.status = s.status
WHEN NOT MATCHED THEN
    INSERT VALUES (s.id, s.name, s.status);

match는 on을 기준으로 match판단.
id = 2인 Bob은 매치됨 → status update
id = 4인 Daisy는 매치 안 됨 → 신규 삽입
id = 1, id = 3은 users_staging에 없음 → 변화 없음

18. TCL

TCL(Transaction Control Language)

• 트랜잭션 제어어, COMMIT, ROLLBACK
• DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어
• DML 수행 후 트랜잭션을 정상 종료하지 않는 경우 LOCK 발생할 수 있음
  ※ 잠금(LOCK)
  트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한
  잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만이 접근 및 해제 가능(관리자 권한 계정 제외)

트랜잭션

• 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함
• 분할 할 수 없는 최소의 단위
• ALL OR NOTHING 개념(모두 COMMIT 하거나 ROLLBACK 처리 해야 함)

※ 트랜잭션의 특성 (ACID)

• 원자성(atomicity) : 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은상태
• 일관성(consistency)
• 고립성(isolation) : 트랜잭션 실행도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨
• 지속성(durability) : 데이터베이스 내용이 영구적으로 저장

COMMIT

• 데이터를 저장하는 명령어
• 한 번 COMMIT 을 수행하면 COMMIT 이전에 수행된 DML은 모두 저장되며 되돌릴 수 없음
• ORACLE은 DDL시 AUTO COMMIT(23c 버전부터 비활성화 가능)이지만 SQL Server 는
  AUTO COMMIT 비활성화 설정 가능

ROLLBACK

SAVEPOINT SAVEPOINT_NAME

• 변경을 취소하는 명령어
• 데이터베이스에 저장되지 않고 최종 COMMIT 지점/변경 전/특정 SAVEPOINT 지점으로 원복
• 최종 COMMIT 시점 이전까지 ROLLBACK 가능
• SAVEPOINT 를 설정하여 최종 COMMIT 시점이 아닌, 그 이후의 원하는 시점으로의 원복 가능
  ※ SAVEPOINT
  트랜잭션 내에서 롤백을 부분적으로 수행하기 위해 사용되는 지점을 지정
  사용자가 원하는 위치에 원하는 이름으로 설정 가능
  ROLLBACK TO SAVEPOINT_NAME 으로 원하는 지점으로 원복 가능(단, COMMIT 이전으로는 원복 불가)

19.DDL

DDL(Data Definition Language)

• 데이터 구조 정의(객체 생성, 삭제, 변경) 언어
• CREATE(객체 생성), ALTER(객체 변경), DROP(객체 삭제), TRUNCATE(데이터 삭제)
• AUTO COMMIT(명령어 수행하면 즉시 저장, 원복 불가)

CREATE

• 테이블이나 인덱스와 같은 객체를 생성하는 명령어
• 테이블 생성 시 테이블명, 컬럼명, 컬럼순서, 컬럼크기, 컬럼의 데이터타입 정의 필수
  각 컬럼의 제약조건 및 기본값 생략 가능, 소유자 명시 가능(생략 시 명령어 수행 계정 소유)은 생략 가능
• 숫자컬럼의 경우 컬럼 사이즈 생략 가능(날짜 컬럼은 사이즈 명시 X)

create table [소유자] 테이블명 (
컬럼1 데이터타입 [defalut 기본값] [제약조건],
컬럼2 데이터타입,
...
);


-- 복제할 경우
create 테이블명
as 
select *
from 복제테이블명;

-- 구조만 복제할 때
Create table test
as 
select *
from emp
where 1 = 2

• 복제테이블의 컬럼명과 컬럼의 데이터 타입이 복제됨
  where에 항상 거짓인 조건을 전달 -> 출력x
  컬럼 정보들은 출력됨 => 구조만 복제시 사용
• SELECT 문에서 컬럼별칭 사용 시 컬럼별칭 이름으로 생성
• CREATE 문에서 컬럼명 변경 가능

CREATE TABLE new_table (col1, col2)
AS
SELECT column1, column2
FROM old_table;

• NULL 속성도 복제되지만 테이블에 있는 제약조건, INDEX 등은 복제되지 X

alter

• 테이블 구조 변경(컬럼명, 컬럼 데이터타입, 컬럼 사이즈, DEFAULT 값, 컬럼 삭제, 컬럼 추가, 제약조건)
• 컬럼 순서 변경 불가(재생성으로 해결)

1. 컬럼 추가

2. 컬럼(속성) 변경

3. 컬럼 이름 변경

4. 컬럼 삭제

drop

객체(테이블, 인덱스 등) 삭제, DROP 후에는 조회 불가

drop table 테이블명 

truncate

구조 남기고 데이터 즉시삭제, 즉시반영

truncate table 테이블명;

DELETE / DROP / TRUNCATE 차이

• DELETE : 데이터 일부 또는 전체 삭제, 롤백 가능
• TRUNCATE : 데이터 전체 삭제만 가능(일부 삭제 불가), 즉시 반영(롤백 불가)
• DROP : 데이터와 구조를 동시 삭제, 즉시 반영(롤백 불가)

제약조건(CONSTRAINT)

1. PRIMARY KEY
   constraint 제약조건명 제약조건종류

-- 기본구문
컬럼명 데이터타입 CONSTRAINT 제약조건명 PRIMARY KEY
PRIMARY KEY (컬럼명)

CREATE TABLE member (
    member_id INT CONSTRAINT pk_member PRIMARY KEY,
    name VARCHAR(100)
);

2.UNIQUE
중복허용 x, null 가능

컬럼명 데이터타입 CONSTRAINT 제약조건명 UNIQUE
UNIQUE (컬럼명)


CREATE TABLE user_account (
    user_id INT PRIMARY KEY,
    email VARCHAR(100) CONSTRAINT unq_email UNIQUE
);

3. NOT NULL

컬럼명 데이터타입 NOT NULL

CREATE TABLE product (
    product_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

4. FOREIGN KEY

• 참조테이블의 참조 컬럼에 있는 데이터를 확인하면서 본 테이블 데이터를 관리할 목적으로 생성
• 반드시 참조(부모)테이블의 참조 컬럼(REFERENCE KEY)이 사전에 PK 혹은 UNIQUE KEY 를 가져야 함!
  외래키는 반드시 다른테이블의 유일한 값을 참조해야 함
  유일 = PK OR UNIQUE조건.

컬럼명 데이터타입,
CONSTRAINT 제약조건명 FOREIGN KEY (컬럼명) 
REFERENCES 참조테이블(참조컬럼)



CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    member_id INT,
    CONSTRAINT fk_member FOREIGN KEY (member_id)
        REFERENCES member(member_id)
);

• FOREIGN KEY 옵션(생성 시 정의, 변경 불가 -> 재생성)

1. ON DELETE CASCADE : 부모 데이터 삭제 시 자식 데이터 함께 삭제
2. ON DELETE SET NULL : 부모 데이터 삭제 시 자식 데이터의 참조값은 NULL 로 수정

5. CHECK
   직접적으로 데이터의 값 제한

컬럼명 데이터타입 CHECK (조건식)
CONSTRAINT 제약조건명 CHECK (조건식)


CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT CHECK (age >= 18)
);

기타 오브젝트

• 뷰

1. 종류: 단순뷰(테이블1) vs 복합뷰(테이블 2이상)
2. 장점: 논리적 독립성을 제공함(뷰는 "가상의 테이블" 이고, CREATE VIEW 할 때 작성한 SQL 쿼리 결과만 보여주는 창문 같은 것. 즉, 뷰를 만들 당시 기준으로 "이 컬럼만 보겠다" 라고 정해두면,
   그 이후 원본 테이블에 컬럼을 추가해도 뷰에는 반영 x)

create view 뷰이름
as
select * ...

CREATE VIEW HIGH_SAL_VIEW 
AS
SELECT EMPNO, ENAME, SAL 
FROM EMP 
WHERE SAL > 3000;


drop view 뷰이름

• 시퀀스(SEQUENCE)
  자동으로 연속적인 숫자를 부여해주는 객체

CREATE SEQUENCE 시퀀스명
START WITH 시작값    -- default 1
INCREMENT BY 증가값  -- default 1
MINVALUE 최소값
MAXVALUE 최대값
CYCLE | NOCYCLE
CACHE n;

CREATE SEQUENCE EMP_SEQ
START WITH 1001       -- 1001부터 시작
INCREMENT BY 1        -- 1씩 증가
NOCYCLE               -- 최대값 넘으면 끝냄
CACHE 20;             -- 성능 향상 위해 미리 20개 캐싱

• 시노님(SYNONYM)
  테이블 별칭 생성
  다른 사용자 소유의 객체를 쉽게 접근하거나, 긴 이름을 줄일 때 사용.

CREATE [PUBLIC] SYNONYM 별칭명 FOR 원래객체명;


-- 내 계정에서만 사용
CREATE SYNONYM emp_view FOR scott.employee_view;

-- 전체 계정에서 사용
CREATE PUBLIC SYNONYM emp FOR scott.employee;


-- 원래는 이렇게 써야 함
SELECT * FROM scott.employee;

-- 시노님 만들면 이렇게 짧게 가능
SELECT * FROM emp;

☞ OR REPLACE : 기존에 같은 이름으로 시노님이 생성되어 있는 경우 대체
☞ PUBLIC : 시노님을 생성한 유저만 사용 가능한 PRIVATE SYNONYM 의 반대(누구나 사용가능)
☞ PUBLIC 으로 생성한 시노님은 반드시 PUBLIC 으로 삭제

SCOTT은 Oracle에서 기본으로 제공하는 계정

scott: 테이블을 만든 사용자 (소유자 계정)
emp: 그 계정이 만든 테이블

20. DCL

DCL(Data Control Language)

객체에 대한 권한을 부여(GRANT)하거나 회수(REVOKE)하는 기능
테이블 소유자는 타계정에 테이블 조회 및 수정 권한 부여 및 회수 가능

권한

본인(접속한 계정) 소유가 아닌 테이블은 원칙적으로 조회 불가(권한 통제)
업무적으로 필요시 테이블 소유자가 아닌 계정에 테이블 조회, 수정 권한 부여 가능

• 오브젝트 권한
  테이블에 대한 SELECT, INSERT, UPDATE, DELETE, MERGE 권한

• 시스템 권한
  시스템 작업(테이블 생성 등)등을 제어
  ex) 테이블 생성 권한, 인덱스 삭제 권한

GRANT

권한 예시: SELECT, INSERT, UPDATE, DELETE, ALL
객체명: 테이블, 뷰, 시퀀스 등
TO: 누구한테 줄 건지

동시에 여러 유저에 대한 권한 부여 가능
동시 여러 객체 권한 부여 불가
= on 뒤에 하나만 가능하다는 뜻

GRANT 권한 [, 권한2, ...]
ON 객체명
TO 사용자 [, 사용자2, ...];

-- SCOTT이 자신의 EMP 테이블에 여러 권한 부여
GRANT SELECT, INSERT
ON emp
TO james;

REVOKE

• 동시 여러 권한 회수 가능
• 이미 회수된 권한 재회수 불가
• 동시 여러 유저로부터의 권한 회수 가능
  from 뒤에 두명

REVOKE 권한 [, 권한2, ...]
ON 객체명
FROM 사용자 [, 사용자2, ...];


-- JAMES에게서 EMP 테이블 SELECT 권한 회수
REVOKE SELECT
ON emp
FROM james;

ROLE

여러 권한을 그룹으로 묶어서 사용자에게 한 번에 부여할 수 있는 기능!

CREATE ROLE 역할명;
GRANT 권한 ON 객체명 TO 역할명;
GRANT 역할명 TO 사용자;

CREATE ROLE emp_role;
GRANT SELECT, INSERT ON emp TO emp_role;
GRANT emp_role TO james;

-- 1. 역할 생성
CREATE ROLE data_editor_role;

-- 2. 여러 권한 부여 (SELECT, INSERT, UPDATE)
GRANT SELECT, INSERT, UPDATE ON student TO data_editor_role;

-- 3. 역할을 사용자(admin)에게 부여
GRANT data_editor_role TO admin;

data_editor_role은 student 테이블을 조회, 삽입, 수정할 수 있어.
admin에게 이 역할을 주면 세 가지 권한을 한 번에 주는 효과!

• 권한회수

-- 역할 생성
CREATE ROLE data_analyst_role;

-- 역할에 권한 부여
GRANT SELECT, INSERT ON sales_data TO data_analyst_role;

-- 사용자 alice에게 역할 부여
GRANT data_analyst_role TO alice;

-- 방법 1: 롤에서 권한 회수 (롤 자체 수정)
REVOKE SELECT, INSERT ON sales_data FROM data_analyst_role;

--방법 2: 사용자에게서 롤 자체를 회수

REVOKE data_analyst_role FROM alice;

권한부여 옵션(중간관리자 권한)

👑 admin: 최고 관리자
👤 manager: 중간 관리자 역할 (시스템 권한 위임 가능)
🙋 user1: 일반 사용자

• 1. WITH GRANT OPTION
     오브젝트 권한 부여 / 회수

GRANT SELECT ON professor TO manager WITH GRANT OPTION;

-- 중간관리자(manager)가 user1에게 권한 부여
GRANT SELECT ON professor TO user1;

-- admin이 manager 권한 회수
REVOKE SELECT ON professor FROM manager;

manager는 professor 테이블에 대해 SELECT 가능 + 다른 사람(user1 등)에게도 SELECT 권한을 줄 수 있음

이제 user1도 professor를 조회할 수 있음.
하지만 WITH GRANT OPTION 없이 받았기 때문에, user1은 다른 사람에게 권한을 넘길 수는 없음.

manager의 권한 사라짐-> manager가 부여했던 user1의 권한도 자동 회수됨!

• 2. WITH ADMIN OPTION
     시스템 권환 /회수

GRANT CREATE SESSION TO manager WITH ADMIN OPTION;
GRANT CREATE SESSION TO user1;
REVOKE CREATE SESSION FROM manager;

manager는 CREATE SESSION 권한을 가질 뿐 아니라,
다른 사용자에게도 이 권한을 줄 수 있음.

user1은 DB에 로그인할 수 있게 됨.

manager는 이제 로그인 못 함
하지만! user1은 여전히 CREATE SESSION 가능함
→ 왜? WITH ADMIN OPTION은 중간관리자를 거치지 않고 직접 부여한 걸로 간주됨
→ 회수해도 manager가 부여한 권한은 살아있음