2025-09-29(SQL-4 DDL, 제약조건, VIEW)

1. DDL (Data Definition Language)

DDL은 데이터베이스 구조(스키마) 를 정의·변경·삭제하는 명령어입니다.
대표적인 명령어:

• CREATE → 테이블 생성
• ALTER → 테이블 구조 변경
• DROP → 테이블 삭제

 

1) 테이블 생성

CREATE TABLE emp (
    empno INT AUTO_INCREMENT PRIMARY KEY,
    ename VARCHAR(20),
    deptno INT,
    mgr INT,
    sal DECIMAL(10,2)
);

• AUTO_INCREMENT → 자동 증가 숫자
• PRIMARY KEY → 기본키 (중복, NULL 불가)

작업문법 예시

컬럼 추가	ALTER TABLE mymember ADD COLUMN email CHAR(15);
컬럼 타입 변경	ALTER TABLE mymember MODIFY COLUMN email VARCHAR(20);
컬럼명 변경	ALTER TABLE mymember CHANGE COLUMN email myemail VARCHAR(30);
컬럼 삭제	ALTER TABLE mymember DROP COLUMN myemail;
테이블명 변경	ALTER TABLE mymember RENAME TO member1;

3) 테이블 삭제 (DROP)

DROP TABLE emp;

주의: DROP은 복구 불가 ❗ 실제 데이터까지 완전히 삭제됨.

 

2. UPDATE 예제

미션 1

deptno와 mgr이 모두 NULL인 레코드의 deptno를 20번으로 수정

UPDATE emp
SET deptno = 20
WHERE deptno IS NULL
  AND mgr IS NULL;

mgr이 NULL인 사원의 mgr을 SMITH의 mgr로 수정 (단, KING 제외)

UPDATE emp
SET mgr = (
    SELECT mgr
    FROM (SELECT * FROM emp) e
    WHERE e.ename = 'SMITH'
)
WHERE mgr IS NULL
  AND ename <> 'KING';

3. AUTO_INCREMENT

• 숫자가 자동으로 1부터 순차 증가
• PRIMARY KEY 와 함께 많이 사용
• 반드시 정수형 컬럼에만 사용 가능 (INT, BIGINT 등)
  
  

4. 제약조건 (Constraints)

테이블 데이터의 무결성(Integrity) 을 보장하기 위해 사용.

① Primary Key

• 중복 X, NULL X

CREATE TABLE mydept (
    deptno INT PRIMARY KEY,
    dept_name VARCHAR(20)
);
-- 테이블 생성 후 추가 가능
ALTER TABLE mydept
ADD CONSTRAINT pk_mydept_deptno PRIMARY KEY(deptno);

② Not Null

• NULL 값 저장 불가

ALTER TABLE member1
MODIFY COLUMN name VARCHAR(20) NOT NULL;

 

③ Check

• 컬럼에 저장 가능한 값 제한

ALTER TABLE member1
ADD CONSTRAINT CHECK (point >= 1000 AND point <= 5000);

ALTER TABLE member1
ADD CONSTRAINT CHECK (addr IN ('서울','광주','부산','대구'));

④ Unique

• 중복된 값 입력 불가

ALTER TABLE mydept
ADD CONSTRAINT unique_dept_name UNIQUE(dept_name);

⑤ Foreign Key

• 다른 테이블의 PK를 참조

ALTER TABLE member1
ADD CONSTRAINT fk_member1_deptno
FOREIGN KEY(deptno) REFERENCES mydept(deptno);

주의: PK 테이블이 먼저 생성되어 있어야 하며,
참조할 PK 값이 존재해야 INSERT 가능

⑥ 제약조건 확인

MySQL은 모든 제약조건 정보를 information_schema 데이터베이스에 저장함

SELECT *
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'mydept';

5. FROM 절 서브쿼리 활용 (팁)

복잡한 쿼리에서 가상의 테이블을 만들어 사용하는 방법

SELECT e.empno, e.ename, e.sal, d.avg_sal
FROM emp e
JOIN (
    SELECT deptno, AVG(sal) AS avg_sal
    FROM emp
    GROUP BY deptno
) d ON e.deptno = d.deptno
WHERE e.sal > d.avg_sal;

➡ 서브쿼리 결과를 d라는 테이블처럼 JOIN
➡ 부서별 평균 급여보다 높은 직원만 조회 가능

 

• DDL은 테이블·스키마 구조를 정의·변경하는 명령어
• ALTER 로 컬럼 추가·삭제·수정 가능
• 제약조건(PRIMARY KEY, NOT NULL, CHECK, UNIQUE, FOREIGN KEY)으로 데이터 무결성 보장
• UPDATE 시 서브쿼리를 이용해 다른 레코드 값으로 수정 가능
• FROM 절 서브쿼리는 성능 개선·가독성 향상에 효과적

 

이 명령어는 부서별 평균 급여 를 조회하는 쿼리를 avgdeptsal 이라는 뷰(view) 로 만들어둔 것

• emp 테이블 : 사원 정보 (사번, 이름, 급여, 부서번호 등)
• dept 테이블 : 부서 정보 (부서번호, 부서명 등)
• where e.deptno = d.deptno : 사원의 부서번호와 부서 테이블을 조인
• group by d.dname : 부서별로 그룹화
• avg(sal) : 부서별 평균 급여 계산

즉, avgdeptsal 이라는 가상 테이블

 

• View란?
  실제 데이터를 복제하지 않고, 자주 사용하는 쿼리를 재사용할 수 있도록 만들어 놓은 가상의 테이블
• 장점
  1. 복잡한 쿼리를 단순화 (→ select * from avgdeptsal; 처럼 간단하게 조회 가능)
  2. 재사용성 ↑
  3. 보안성 ↑ (필요한 컬럼만 노출 가능)

 

View 사용하기