SQL 기초

SQL

• Structured Query Language
• 데이터를 데이터베이스에서 조작하고 관리하기 위해 사용하는 언어

---

DDL

• Data Definition Language
• 테이블이나 관계의 구조를 생성하는데 사용

CREATE

• 새로운 데이터베이스 및 테이블을 생성

CREATE DATABASE 데이터베이스이름;
CREATE TABLE 테이블이름
(
필드이름1 필드타입1,
필드이름2 필드타입2,
...
);

ALTER

• 데이터베이스와 테이블의 내용을 수정

ALTER TABLE 테이블이름 ADD 필드이름 필드타입;
ALTER TABLE 테이블이름 DROP 필드이름;
ALTER TABLE 테이블이름 MODIFY COLUMN 필드이름 필드타입;

DROP

• 데이터베이스와 테이블을 삭제. 데이터 및 테이블 자체를 삭제

DROP DATABASE 데이터베이스이름;
DROP TABLE 테이블이름;

TRUNCATE

• 데이터베이스와 테이블 삭제. 최초 테이블이 만들어졌던 상태(컬럼값만)만 남김

TRUNCATE DATABASE 데이터베이스이름;
TRUNCATE TABLE 테이블이름;

---

DCL

• Data Control Language
• 데이터의 사용 권한을 관리하는데 사용

GRANT

• 사용자 또는 ROLE에 대한 권한 부여

GRANT [객체권한명] (컬럼)
ON [객체명]
TO { 유저명 | 롤명 | PUBLC} [WITH GRANT OPTION];

//ex
GRANT SELECT ,INSERT
ON mp
TO scott WITH GRANT OPTION;

REVOKE

• 사용자 또는 ROLE에 부여한 권한을 회수

REVOKE { 권한명 [, 권한명...] ALL}
ON 객체명
FROM {유저명 [, 유저명...] | 롤명(ROLE) | PUBLIC}
[CASCADE CONSTRAINTS];
//ex
REVOKE SELECT , INSERT
ON emp
FROM scott
[CASCADE CONSTRAINTS];

---

DML

• Data Manipulation Language
• 테이블의 데이터를 검색, 삽입, 수정, 삭제 (CRUD) 수행

INSERT

• 테이블에 새로운 row ( record, tuple ) 추가

INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...) VALUES(데이터값1, 데이터값2, 데이터값3, ...);
INSERT INTO 테이블이름 VALUES(데이터값1, 데이터값2, 데이터값3, ...);

SELECT

• 테이블의 row를 선택 (조건부도 가능) 합니다.

SELECT 필드이름 FROM 테이블이름 [WHERE 조건];

UPDATE

• 테이블의 row의 내용을 수정합니다.

UPDATE 테이블이름 SET 필드이름1=데이터값1, 필드이름2=데이터값2, ... WHERE 필드이름=데이터값;

DELETE

• 테이블의 row를 삭제합니다.

DELETE FROM 테이블이름 WHERE 필드이름=데이터값;

DELETE FROM 테이블이름; // 테이블 row 전체삭제

---

KEY

Primary Key

• 해당 필드는 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가지게 됨
• NOT NULL : 값이 없음을 허용하지 않음
• UNIQUE : 중복값을 허용하지 않음 (유일성)
• PK (Primary Key)는 테이블당 1개
• PK는 자동 인덱싱
• 기본키의 존재 이유는 '데이터의 무결성 확보'

CREATE TABLE 테이블이름
(
필드이름 필드타입 PRIMARY KEY,
...
);

Foreign Key (외래키)

• 외래키라고도 함
• 하나의 테이블을 다른 테이블에 의존하게 만들며 데이터의 무결성을 보장해줌
• FK (Foreign Key)를 가지는 테이블이 참조하는 기준 테이블의 열은 반드시 PK, UNIQUE 제약조건(Constraint)이 설정되어있어야함

CREATE TABLE 테이블이름
(
필드이름 필드타입,
...
FOREIGN KEY(필드이름)
REFERENCES 테이블이름(필드이름)
);

• Key와 관게에 대해선 추후 정규화에 대한 포스팅으로 다룰 예정

Composite Key (복합키)

• 복수의 컬럼을 묶어서 하나의 Primary Key로 사용

// 단일 PK의 경우 -
CREATE TABLE STUDENT (
    student_code VARCHAR(10) PRIMARY KEY  -- 1개 컬럼
);

// 복합키의 경우
CREATE TABLE EXAM (
    student_code VARCHAR(100),
    exam_seq INT,
    PRIMARY KEY (student_code, exam_seq)  -- 2개 컬럼 조합
);

복합키는 왜 필요한가?

//문제 상황
// 학생 s1의 시험 결과가 3개
// -> student_code만으로 구분이 불가능

s1, 점수 85 <- 두 시험이 어떤 시험인지 구분 불가
s1, 점수 90 <- 두 시험이 어떤 시험인지 구분 불가

//해결
(학생이름, 시험주차)로 구분
(s1, 1) -> 첫번째 시험
(s2, 2) -> 두번째 시험

복합키의 조건

• 개별 컬럼은 중복 가능
  student_code : s1,s1,s1 (가능)
  exam_sql:1,2,1,2(가능)
• 조합은 반드시 유일해야함
  (s1,1), (s1,2), (s2,1) -> 가능
  (s1,1), (s1,1) -> 불가능
• null 불가 (not null)

CASCADE

• Foreign Key로 연관된 데이터를 삭제, 변경

// CASCADE가 없는 경우
-- MAJOR 삭제 시도
DELETE FROM MAJOR WHERE major_code = 'm1';
-- ❌ 에러! STUDENT가 'm1' 참조 중

// CASCADE가 있는 경우
-- MAJOR 삭제 시
DELETE FROM MAJOR WHERE major_code = 'm1';
-- ✅ 성공! 해당 전공 학생들도 자동 삭제

// 사용 방법
// 1. ON DELETE CASCADE
CREATE TABLE STUDENT (
    student_code VARCHAR(10) PRIMARY KEY,
    name VARCHAR(50),
    major_code VARCHAR(100),
    
    CONSTRAINT fk_student_major
        FOREIGN KEY (major_code)
        REFERENCES MAJOR(major_code)
        ON DELETE CASCADE  -- 전공 삭제 시 학생도 삭제
);

// 2. ON UPDATE CASCADE
CREATE TABLE STUDENT (
    student_code VARCHAR(10) PRIMARY KEY,
    major_code VARCHAR(100),
    
    CONSTRAINT fk_student_major
        FOREIGN KEY (major_code)
        REFERENCES MAJOR(major_code)
        ON UPDATE CASCADE  -- 전공코드 변경 시 학생도 변경
);

//3. 둘 다 사용
CREATE TABLE MANAGER (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    student_code VARCHAR(10),
    
    CONSTRAINT manager_fk_student_code
        FOREIGN KEY (student_code)
        REFERENCES STUDENT(student_code)
        ON DELETE CASCADE  -- 학생 삭제 시 매니저도 삭제
        ON UPDATE CASCADE  -- 학생코드 변경 시 매니저도 변경
);

---

JOIN

• 분할된 테이블을 하나로 합치기 위해 데이터베이스가 제공하는 기능
• ON 키워드를 통해 기준 컬럼을 선택, 2개의 테이블을 합쳐줌
• JOIN을 위해서는 적어도 하나의 column을 공유해야 함

// SELECT
SELECT s.name, m.major_name 
FROM STUDENT s 
JOIN MAJOR m ON s.major_code = m.major_code;

//UPDATE
-- 스프링 전공 학생들의 전화번호 일괄 변경
UPDATE STUDENT s
JOIN MAJOR m ON s.major_code = m.major_code
SET s.phone = '01099999999'
WHERE m.major_name = '스프링';

//DELETE
-- 특정 전공 학생 삭제
DELETE s
FROM STUDENT s
JOIN MAJOR m ON s.major_code = m.major_code
WHERE m.major_name = '노드';

//INSERT (서브쿼리)
-- 직접 JOIN은 안 되지만 SELECT-JOIN 결과 삽입 가능
INSERT INTO STUDENT_BACKUP 
SELECT s.* 
FROM STUDENT s
JOIN MAJOR m ON s.major_code = m.major_code
WHERE m.major_name = '스프링';

---

INDEX

• 검색 (SELECT) 속도 향상을 위해 필요
• 인덱스 없을 시 기본적으로 순차 탐색 (1번부터 나올때까지 조회, 완료후 처음부터 반복) -> O(log2)
• 인덱스 있을 시 B-Tree 자료구조 사용 (Binary Search) -> O(log n)
• 데이터가 적거나, INSERT/UPDATE가 많은 테이블이거나, 거의 조회하지 않는 컬럼에는 만들지 않는 편