[SQL] DDL(Data Definition Language)

Backend/SQL

[SQL] DDL(Data Definition Language)

해로몬 2024. 10. 28. 16:42

DDL

DDL (Data Definition Language): 데이터베이스 내의 구조를 정의하고 변경할 때 사용되는 언어입니다. 주로 테이블, 인덱스, 뷰, 스키마 등을 생성하고 관리하는 명령어로 구성되어 있습니다.

데이터베이스와 테이블, 뷰, 인덱스 등의 데이터 구조를 설계하고 설정하는 것입니다.
데이터베이스의 설계 단계에서 주로 사용되며, 데이터 저장소의 틀을 정의하는 데 중점을 둡니다.
데이터를 직접 변경하는 것이 아닌, 데이터를 담을 구조를 생성하고 관리하는 작업에 사용됩니다.

데이터베이스의 두가지 의미

데이터베이스 시스템 (Database System)
데이터베이스 (Database)

1. 데이터베이스 생성 및 삭제

데이터베이스 생성 (CREATE DATABASE)

데이터베이스를 생성하려면 CREATE DATABASE 명령어를 사용합니다. 데이터베이스 이름은 식별자 역할을 하며, 사용하려는 이름이 기존에 있는지 확인하고 고유하게 설정합니다.

CREATE DATABASE 데이터베이스_이름;

CREATE DATABASE IF NOT EXISTS
데이터베이스를 생성할 때, 이미 존재하는 경우에는 새로 생성하지 않고 넘어가도록 할 수 있습니다. 이를 통해 동일한 이름의 데이터베이스가 있을 경우 오류를 방지합니다.

CREATE DATABASE IF NOT EXISTS 데이터베이스_이름;

데이터베이스 삭제 (DROP DATABASE)

DROP DATABASE 명령어는 지정된 데이터베이스와 해당 데이터베이스에 포함된 모든 테이블, 뷰, 데이터를 삭제합니다. 이 명령어는 데이터 복구가 불가능하기 때문에 주의해서 사용해야 합니다.

DROP DATABASE 데이터베이스_이름;

데이터베이스를 삭제할 때, 존재하지 않는 경우에는 삭제 명령을 무시하고 오류 없이 넘어가도록 설정할 수 있습니다. 이를 통해 삭제할 데이터베이스가 없을 때 발생할 수 있는 오류를 방지합니다.

DROP DATABASE IF EXISTS 데이터베이스_이름;

2. 테이블 생성 및 삭제

1)CREATE TABLE

새로운 테이블을 생성할 때 사용됩니다. 이 명령어를 통해 테이블의 열 이름, 데이터 타입, 제약조건 등을 정의할 수 있으며, 테이블 생성 시 초기 구조를 설정할 수 있습니다.

CREATE TABLE 테이블_이름 (
    열_이름 데이터_타입 [제약조건],
    열_이름 데이터_타입 [제약조건],
    ...
);

테이블_이름: 생성할 테이블의 이름을 지정합니다.
열_이름: 테이블 내 열(컬럼)의 이름을 지정합니다.
데이터_타입: 열에 저장될 데이터의 유형을 지정합니다. 예: INT, VARCHAR(50), DATE 등
제약조건: 열에 설정할 추가 조건 (예: PRIMARY KEY, NOT NULL, UNIQUE 등)

*MariaDB에서 주로 사용되는 데이터 타입*

데이터 유형	자료형 예시
정수	INT, BIGINT
실수	FLOAT, DOUBLE, DECIMAL
고정 길이 문자	CHAR
가변 길이 문자	VARCHAR, TEXT
날짜 및 시간	DATE, TIME, DATETIME, TIMESTAMP
목록 선택	ENUM, SET
이진 데이터	BINARY, VARBINARY

CREATE TABLE IF NOT EXISTS employees (
    num INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    job VARCHAR(50),
    sal DECIMAL(10, 2) DEFAULT 0.00,
    day DATE
);

2)DROP TABLE

테이블을 삭제할 때 사용됩니다. 이 명령어를 실행하면 테이블과 그 안에 있는 모든 데이터가 완전히 제거되므로, 데이터 복구가 불가능하다는 점에서 신중하게 사용해야 합니다.

DROP TABLE 테이블명;

[IF EXISTS 조건 추가]

IF EXISTS 조건을 추가하면, 지정한 테이블이 존재하지 않는 경우에도 오류가 발생하지 않고 명령을 무시하게 됩니다. 이를 통해 테이블이 없는 경우에도 안전하게 명령을 실행할 수 있습니다.

DROP TABLE IF EXISTS employees;

DROP TABLE 명령어는 테이블을 삭제하는 데 사용되며, 모든 데이터가 함께 삭제됩니다.
IF EXISTS 조건을 사용하여 테이블이 없는 경우 오류 없이 명령을 수행할 수 있습니다.
여러 테이블을 한 번에 삭제할 수도 있으며, 테이블 삭제 시 데이터 손실에 주의가 필요합니다.

3. 테이블 복제

1) 테이블 구조와 데이터 모두 복제

CREATE TABLE 새_테이블명 AS
SELECT * FROM 기존_테이블명;

기존_테이블명의 구조와 데이터를 그대로 새_테이블명에 복제합니다.
새로 생성된 테이블에는 원본 테이블의 데이터도 함께 저장됩니다.
주의: AS SELECT 구문을 사용할 경우, 원본 테이블의 제약조건(PRIMARY KEY, INDEX 등)은 복제되지 않습니다.
SELECT 문에 맞춰 복제가 가능합니다.

[문제] 사원번호, 사원이름, 급여, 연봉, 입사일자가 복제되어 있는 emp_year1 테이블 생성

-- 사원번호(empno), 사원이름(ename), 급여(sal), 연봉(annual_salary), 입사일자(hiredate) 컬럼이 있는 emp_year1 테이블 생성
-- 연봉(annual_salary)은 급여(sal) * 12로 계산

create table emp_year1 as
select 
    num,                  -- 사원번호
    name,                  -- 사원이름
    sal,                    -- 급여
    sal * 12 as annual_salary,  -- 연봉 (급여 * 12)
    day                -- 입사일자
from sample.emp;

2) 테이블 구조만 복제

CREATE TABLE 새_테이블명 LIKE 기존_테이블명;

기존_테이블명의 구조와 제약조건을 그대로 복제하되, 데이터는 복사하지 않습니다.
LIKE 구문은 PRIMARY KEY, INDEX, DEFAULT 값 등 모든 제약조건을 그대로 유지한 채 테이블을 복제할 수 있습니다.

create table empty_dept2 like sample.dept;

WHERE 1 != 1 조건을 사용하면, 테이블의 구조만 복제하고 데이터는 복제하지 않는 효과를 얻을 수 있습니다. 이 방법은 CREATE TABLE ... AS SELECT 구문을 사용할 때 유용합니다. WHERE 1 != 1 조건은 항상 FALSE이므로, 선택된 데이터는 없지만 테이블의 구조는 동일하게 복사됩니다.

CREATE TABLE 새_테이블명 AS
SELECT * FROM 기존_테이블명
WHERE 1 != 1;

SELECT * FROM 기존_테이블명 구문에서 WHERE 1 != 1 조건을 사용하면 데이터가 선택되지 않지만, 구조는 복제됩니다.
원본 테이블의 열 이름과 데이터 타입을 그대로 복사하여 새로운 테이블을 생성합니다.
주의: 제약조건(PRIMARY KEY, INDEX 등)은 복제되지 않습니다. 제약조건이 필요한 경우에는 추가 설정이 필요합니다.

 [문제] 사원번호, 사원이름, 부서번호, 부서이름, 부서위치를 저장할 수 있는 emp_dept 테이블 생성 스크립트 
 
create table emp_dept
as select e.empno, e.ename, d.deptno, d.dname, d.loc
from sample.emp e inner join sample.dept d
on (e.deptno = d.deptno)
where 1 != 1;

4. ALTER TABLE

ALTER TABLE의 주요 기능

열 추가: 테이블에 새로운 열을 추가합니다.
열 삭제: 기존의 열을 삭제합니다.
열 수정: 기존 열의 데이터 타입이나 제약조건을 변경합니다.
제약조건 추가 및 삭제: PRIMARY KEY, FOREIGN KEY 등의 제약조건을 추가하거나 제거합니다.

create table emp_alter1
 as select empno, ename, sal, hiredate
 from sample.emp
 where 1 != 1;

1)열 추가 (ADD COLUMN)

테이블에 새로운 열을 추가할 때는 ADD COLUMN을 사용한다.

//열 추가
ALTER TABLE 테이블명
ADD COLUMN 열_이름 데이터_타입 [제약조건];

ALTER TABLE emp_alter1
ADD COLUMN work VARCHAR(20);

2) 열 삭제 (DROP COLUMN)

기존의 열을 삭제할 때는 DROP COLUMN을 사용한다. 열을 삭제하면 해당 열의 데이터도 함께 삭제.

//열 삭제
ALTER TABLE 테이블명
DROP COLUMN 열_이름;

ALTER TABLE emp_alter1
DROP COLUMN work;

3) 열 수정 (MODIFY COLUMN 또는 CHANGE COLUMN)

기존 열의 데이터 타입이나 제약조건을 변경할 때는 MODIFY COLUMN 또는 CHANGE COLUMN을 사용한다.

//열 크기 변경
ALTER TABLE 테이블명
MODIFY COLUMN 열_이름 새로운_데이터_타입 [새로운_제약조건];

ALTER TABLE emp_alter1
MODIFY COLUMN sal DECIMAL(12, 2);

//열 이름 변경
ALTER TABLE 테이블명
RENAME COLUMN 기존_열_이름 TO 새_열_이름;

//예제
ALTER TABLE emp_alter1
RENAME COLUMN ename TO employee_name;

5.INSERT

INSERT 문은 MariaDB에서 테이블에 새로운 데이터를 추가하는 명령어입니다. 이 명령어는 주로 단일 행 또는 여러 행의 데이터를 삽입할 때 사용하며, 필요한 경우 특정 열에만 데이터를 추가하거나 다른 테이블에서 데이터를 복사해 삽입하는 등 다양한 방식으로 사용할 수 있습니다.

[기본 문법]

INSERT INTO 테이블명 VALUES (값1, 값2, ...);

구문	설명
INSERT INTO 테이블 VALUES (값1, 값2, ...);	테이블의 모든 열에 데이터를 삽입합니다.
INSERT INTO 테이블 (열1, 열2) VALUES (값1, 값2);	특정 열에만 데이터를 삽입합니다.
INSERT INTO 테이블 VALUES (값1, 값2), (값3, 값4);	여러 행을 한 번에 삽입합니다.
INSERT INTO 테이블 SELECT * FROM 다른_테이블 WHERE 조건;	다른 테이블에서 데이터를 선택하여 삽입합니다.

연습용 테이블 dept1 생성: sample.dept 테이블의 구조를 복사하여 데이터는 없이 dept1 테이블을 만듭니다.

-- 연습용 테이블 생성
create table dept1
as select * from sample.dept
where 1 != 1;

[중복 데이터 삽입 및 유효성 검토]

insert into dept1 values (10, '개발부', '서울'); 

insert into dept1 values (10, '개발부', '서울');

insert into dept1 values (10, '개발부', '서울');

insert into dept1 values (10, '개발부', '서울서울서울서울서울서울서울서울서울');

insert into dept1 values (20, '연구부', '부산');

데이터 삽입: dept1에 모든 열을 대상으로 데이터를 삽입합니다.
중복 데이터 삽입 시도: deptno 값이 동일한 데이터(중복)를 여러 번 삽입할 수 있습니다.
VARCHAR 길이를 초과하는 데이터 삽입 시 오류가 발생할 수 있습니다.

특정 열 삽입

//deptno와 dname 열에만 데이터를 삽입
insert into dept1 (deptno, dname, loc) values (30, '총무부', '서울');

insert into dept1 (deptno, loc, dname) values (40, '서울', '기획부');

열 순서 변경하여 데이터 삽입: 열의 순서를 지정하고 데이터 삽입을 진행할 수 있습니다.

여러 행을 동시에 삽입 (Bulk Insert)

-- bulky insert

insert into dept1 values ( 11, '개발부', '서울' ), ( 12, '개발부', '서울' ), ( 13, '개발부', '서울' );

여러 행 동시에 삽입: 한 번에 여러 행을 삽입할 때는 VALUES 절에 여러 개의 데이터 세트를 추가합니다.

테이블 구조 복제 후 데이터 삽입 (CREATE TABLE LIKE 및 INSERT INTO ... SELECT)

-- 연습용 테이블2 생성
create table dept2 like sample.dept;

desc dept2;
select * from dept2;

insert into dept2 select * from sample.dept;

테이블 간 데이터 복사: INSERT INTO ... SELECT 문을 사용하여 다른 테이블의 데이터를 복사해 삽입할 수 있습니다.

주의사항

데이터 타입: 각 열에 맞는 데이터 타입을 제공해야 합니다. 예를 들어, 숫자형 열에 문자열을 넣으면 오류가 발생할 수 있습니다.
NULL 값: 특정 열에 데이터를 입력하지 않으면 기본값이나 NULL 값이 자동으로 삽입됩니다. 테이블 구조에 따라 NOT NULL 제약조건이 설정된 열은 반드시 값을 제공해야 합니다.
중복 데이터: 기본 키 또는 유니크 키가 설정된 열에는 중복 값이 삽입되지 않으므로, 중복 데이터 삽입 시 오류가 발생할 수 있습니다.

[NOT NULL 제약조건]

NULL 값 허용 안 함: NOT NULL 제약조건이 설정된 열에는 NULL 값이 들어갈 수 없습니다. 데이터를 삽입할 때 해당 열에 반드시 유효한 값이 입력되어야 합니다.

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(2)      | NO   |     | NULL    |       | <- Not Null
| dname  | varchar(14) | YES  |     | NULL    |       |
| loc    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

MariaDB [test1]> INSERT INTO dept1 (dname, loc) VALUES ('총무부', '서울');
ERROR 1364 (HY000): Field 'deptno' doesn't have a default value

deptno 열에 값을 제공하지 않았기 때문에 NULL 값이 들어가게 되며, 오류가 발생합니다. NOT NULL 제약조건이 설정된 열은 반드시 값을 가져야 합니다.

6. UPDATE

테이블 내의 특정 열 값을 수정하거나 여러 열을 동시에 변경할 수 있는 SQL 명령어입니다.

전체 행 수정: WHERE 절을 생략하면 테이블의 모든 행이 업데이트됩니다.
부분 행 수정: WHERE 절을 사용하면 특정 조건을 만족하는 행만 선택적으로 업데이트할 수 있습니다.

UPDATE 테이블명 
SET 컬럼명1 = 값1, 컬럼명2 = 값2, ... 
WHERE 조건;

(1) 모든 행의 특정 열을 동일한 값으로 수정

UPDATE 문에서 WHERE 절을 생략하면, 테이블의 모든 행이 수정됩니다.

//dept1 테이블의 loc 열 값을 모두 '대전'으로 변경
UPDATE dept1 
SET loc = '대전';

(2) 특정 조건에 맞는 행만 수정

특정 조건을 설정하여 일부 행만 선택적으로 수정할 수 있습니다. 예를 들어, deptno가 21인 행의 loc 값을 '부산'으로 변경하려면 다음과 같이 작성합니다.

//deptno가 30인 행에서 dname을 '총무부'로, loc를 '서울'로 변경
DATE dept1 
SET loc = '부산' 
WHERE deptno = 21;

(3) 여러 열 동시에 수정하기

SET 절에 여러 열을 지정하여 여러 열의 값을 동시에 수정할 수 있습니다.

//deptno가 30인 행에서 dname을 '총무부'로, loc를 '서울'로 변경
UPDATE dept1 
SET dname = '총무부', loc = '서울' 
WHERE deptno = 30;

[문제] 급여가 1000에서 2000 사이인 사원들의 부서번호를 30으로 변경.
 update emp1 set deptno=30 where sal between 1000 and 2000;

7. DELETE

DELETE 문은 테이블의 특정 데이터를 삭제하는 데 사용되며, WHERE 절을 통해 조건을 설정할 수 있습니다. WHERE 절을 사용하지 않을 경우 테이블의 모든 데이터가 삭제되므로, 선택적인 데이터 삭제가 필요할 때는 반드시 WHERE 조건을 지정하는 것이 좋습니다.

DELETE FROM 테이블명
WHERE 조건;

DELETE 문 사용 예제

(1) 모든 행 삭제
WHERE 절을 생략하면 테이블의 모든 행을 삭제합니다.

DELETE FROM emp1;

emp1 테이블의 모든 데이터가 삭제됩니다. 테이블 구조는 유지되며, 행 데이터만 제거

(2) 특정 조건을 만족하는 행 삭제
WHERE 절을 사용하여 특정 조건을 만족하는 일부 행만 삭제할 수 있습니다.

//-- deptno가 30인 행을 삭제
DELETE FROM emp1
WHERE deptno = 30;

(3) 특정 범위의 데이터 삭제
WHERE 절에 범위를 지정하여 특정 범위에 해당하는 데이터를 삭제할 수 있습니다.

//--급여가 1000에서 2000 사이인 사원의 데이터를 삭제
DELETE FROM emp1
WHERE sal BETWEEN 1000 AND 2000;

저작자표시 비영리 변경금지 (새창열림)

'Backend > SQL' 카테고리의 다른 글

[SQL] 제약조건(Constraints) (3)	2024.10.29
[SQL] 참조키 : 기본키, 외래키 (0)	2024.10.29
[SQL]JOIN (0)	2024.10.28
[SQL]서브쿼리(Subquery) (0)	2024.10.28
[SQL] GROUP BY (집계함수, HAVING) (0)	2024.10.28

현재글[SQL] DDL(Data Definition Language)

VIVA LA VIDA

조금씩 나아가는 🐣

SQLD 자격증, 오블완, 기록, nullable, 오토언박싱, SQL, java, 실전 자바, 데이터베이스 백업, wrapper클래스, RBF, 운영체제, SQL개발자, 티스토리챌린지, 김영한, sql 개발자, 데이터베이스 복원, Til, 김영한의 실전자바, 2024,

Today :
Yesterday :

VIVA LA VIDA