미완성 블로그입니다

[TOC]

SQL이란 ?

SQL(Structured Query Language)은 관계형 데이터베이스에서 데이터를 관리하기 위한 표준 언어입니다. 데이터의 정의, 조작, 제어를 위한 다양한 명령어를 제공합니다.

SQL의 주요 특징:

  • 선언적 언어: 무엇을 원하는지만 명시하면 됨
  • 표준화된 언어: ANSI/ISO 표준을 따름
  • 대소문자를 구분하지 않음 (데이터는 구분할 수 있음)
  • 세미콜론(;)으로 문장을 구분

DBMS는 c++ 등으로 프로그래밍 되어있으며, 일반 사용자는 저장되어 있는 데이터에 사실상 직접 접근할 수 없습니다. 따라서 SQL 질의어를 사용해서 접근해야 합니다.


SQL 자료형

숫자형 자료형

  • INT 또는 INTEGER: 정수 (-2,147,483,648 ~ 2,147,483,647)

  • BIGINT: 큰 정수

  • DECIMAL(p,s): 고정 소수점 (p: 전체 자릿수, s: 소수점 자릿수)

  • FLOAT: 부동 소수점

  • DOUBLE: 배정도 부동 소수점

    자료형 size range description
    TINYINT 1 Bytes -128 ~ 127 (signed)
    0 ~ 255 (unsigned)    		 아주 작은 정수
    불린 값이나 상태 코드에 사용
    SMALLINT 2 Bytes -32,768 ~ 32,767 (signed)
    0 ~ 65,535 (unsigned)    		 작은 정수
    카운터나 순서 번호에 사용
    MEDIUMINT 3 Bytes -8,388,608 ~ 8,388,607 (signed)
    0 ~ 16,777,215 (unsigned)    		 중간 크기 정수
    MySQL 전용
    INT / INTEGER 4 Bytes -2,147,483,648 ~ 2,147,483,647 (signed)
    0 ~ 4,294,967,295 (unsigned)    		 표준 정수형
    가장 일반적으로 사용
    BIGINT 8 Bytes -9,223,372,036,854,775,808 ~
    9,223,372,036,854,775,807 (signed)    		 큰 정수
    타임스탬프, 큰 ID 값에 사용
    DECIMAL(p,s) /
    NUMERIC(p,s)    		 가변 정확한 고정 소수점 소수점 실수 표현
    (p: 전체 자릿수, s: 소수점 자릿수)
    FLOAT 4 Bytes ±3.402823466E+38 (근사값) 단정도 부동소수점
    과학적 계산에 사용
    DOUBLE / REAL 8 Bytes ±1.7976931348623157E+308 (근사값) 배정도 부동소수점
    높은 정밀도가 필요한 계산


문자형 자료형

  • CHAR(n): 고정 길이 문자열
  • VARCHAR(n): 가변 길이 문자열
  • TEXT: 긴 텍스트 데이터


날짜/시간형 자료형

  • DATE: 날짜 (YYYY-MM-DD)

  • TIME: 시간 (HH:MM:SS)

  • DATETIME: 초 단위까지의 시간 정보 (YYYY-MM-DD HH:MM:SS), 타임존 처리하지 않음

  • TIMESTAMP: 타임스탬프, TIMESTAMP는 타임존 자동 변환 처리를 지원합니다.

    자료형 size range description
    DATE 3 Bytes 1000-01-01 ~ 9999-12-31 연·월·일(YYYY-MM-DD) 저장
    DATETIME 5 Bytes
    (8 Bytes)    		 1000-01-01 00:00:00 ~
    9999-12-31 23:59:59    		 날짜와 시간을 함께 저장 (표준, 시간대 정보 없음)
    TIMESTAMP 4 Bytes 1970-01-01 00:00:01 UTC ~
    2038-01-19 03:14:07 UTC    		 날짜와 시간 저장
    (UTC 기반, 시스템 시간대에 따라 변환됨)
    TIME 3 Bytes -838:59:59 ~ 838:59:59 시:분:초 (HH:MM:SS), 경과 시간도 표현 가능
    YEAR 1 Bytes 1901 ~ 2155 연도(YYYY) 저장


기타 자료형

  • BOOLEAN: 참/거짓 값
  • BLOB: 이진 데이터 (이미지, 미디어 같은 Binary 데이터 저장)
  • SET: 다중 선택 타입
  • ENUM: 단일 선택 타입 (미리 선택한 리스트중 한가지 선택해서 저장)



DDL (Data Definition Language)

DDL은 데이터베이스 구조를 정의하고 수정하는 언어입니다.

CREATE

테이블, 뷰, 데이터베이스(스키마) 등을 생성합니다.

데이터베이스(스키마 생성)

CREATE DATABASE database_name; -- create database(scheme)

USE database_name;  -- set as default scheme


CREATE TABLE 예시: 데이터베이스를 선택한 후, 해당 데이터베이스에 테이블을 생성합니다.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


참고 테이블 구조 확인하기

DESC table_name;  -- describe field, type, null, key, default, extra 등 확인


ALTER

테이블 구조를 수정하는 명령어입니다.


컬럼 추가:

ALTER TABLE students ADD COLUMN phone VARCHAR(20);


컬럼 수정:

ALTER TABLE students MODIFY COLUMN age SMALLINT;
ALTER TABLE students CHANGE COLUMN age student_age INT;


컬럼 삭제:

ALTER TABLE students DROP COLUMN phone;


제약조건 추가/삭제:

-- 제약조건 추가
ALTER TABLE students ADD CONSTRAINT fk_student_class 
FOREIGN KEY (class_id) REFERENCES classes(id);

-- 제약조건 삭제
ALTER TABLE students DROP CONSTRAINT fk_student_class;


DROP, TRUNCATE

  • DROP: 테이블 자체를 완전히 삭제합니다 (구조와 데이터 모두 삭제)
  • TRUNCATE: 테이블 구조는 유지하고 모든 데이터만 삭제합니다
-- 테이블 완전 삭제
DROP TABLE students;

-- 테이블 내용만 삭제 (구조 유지)
TRUNCATE TABLE students;

참고로, DELETE 는 데이터 내 튜플을 삭제하는 것이며, DML 입니다.



외래 키 제약 조건 해제/설정

외래 키 제약 조건을 일시적으로 해제하여 데이터 조작을 수행할 때 사용합니다.

보통 외래 키 제약 조건 때문에 삭제되지 않는 테이블을 삭제할 때 많이 사용됩니다.

-- 외래 키 제약 조건 해제
SET FOREIGN_KEY_CHECKS = 0;

-- 테이블 삭제 또는 데이터 조작
DROP TABLE students;

-- 외래 키 제약 조건 재설정
SET FOREIGN_KEY_CHECKS = 1;



DML (Data Manipulation Language)

DML은 데이터를 조작하는 언어입니다.

SELECT (데이터 조회)

-- 기본 조회
SELECT * FROM students;

-- 특정 컬럼 조회
SELECT name, age FROM students;

-- 조건부 조회
SELECT * FROM students WHERE age >= 20;

-- 정렬
SELECT * FROM students ORDER BY age DESC;

-- 그룹화 및 집계
SELECT age, COUNT(*) FROM students GROUP BY age;

-- 조인
SELECT s.name, c.class_name 
FROM students s 
JOIN classes c ON s.class_id = c.id;


INSERT (데이터 삽입)

-- 단일 행 삽입
INSERT INTO students (name, age, email) 
VALUES ('김철수', 22, 'kim@email.com');

-- 여러 행 삽입
INSERT INTO students (name, age) VALUES 
('이영희', 23),
('박민수', 21),
('최지원', 24);

들어갈 행 목록의 순서와 형식 모두 일치해야 한다.


UPDATE (데이터 수정)

-- 조건부 수정
UPDATE students SET age = 25 WHERE name = '김철수';

-- 여러 컬럼 수정
UPDATE students 
SET age = 26, email = 'newemail@email.com' 
WHERE id = 1;

DELETE

특정 조건에 맞는 행을 삭제하는 명령어입니다.

-- 모든 데이터 삭제
DELETE FROM students;

-- 조건부 삭제
DELETE FROM students WHERE age < 18;

-- 여러 조건으로 삭제
DELETE FROM students WHERE age > 25 AND name LIKE 'Kim%';

DELETE vs TRUNCATE, DROP

  • DROP : DDL, 테이블 완전 삭제, 롤백 불가
  • TRUNCATE : DDL, 테이블 구조 유지, 빠른 삭제, 롤백 불가, AUTO_INCREMENT 초기화
  • DELETE : DML, 선택한 데이터만 삭제하는 것, 트랜젝션 내 롤백 가능, 상대적으로 느림



DCL (Data Control Language)

DCL은 데이터에 대한 접근 권한을 제어하는 언어입니다.

GRANT (권한 부여)

-- 특정 테이블에 대한 SELECT 권한 부여
GRANT SELECT ON students TO user1;

-- 모든 권한 부여
GRANT ALL PRIVILEGES ON database_name.* TO user1;

-- 특정 권한 부여
GRANT SELECT, INSERT, UPDATE ON students TO user1;


REVOKE (권한 해제)

-- 권한 해제
REVOKE SELECT ON students FROM user1;

-- 모든 권한 해제
REVOKE ALL PRIVILEGES ON database_name.* FROM user1;


사용자 관리

-- 사용자 생성
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

-- 사용자 삭제
DROP USER 'username'@'localhost';



TCL (Transaction Control Language)

TCL은 트랜잭션을 제어하는 언어입니다.

트랜잭션의 ACID 속성

  • Atomicity(원자성): 트랜잭션은 모두 실행되거나 모두 실행되지 않음
  • Consistency(일관성): 트랜잭션 실행 후에도 데이터 무결성 유지
  • Isolation(격리성): 동시 실행 트랜잭션들이 서로 영향을 주지 않음
  • Durability(지속성): 완료된 트랜잭션의 결과는 영구적으로 저장


트랜잭션 명령어

-- 트랜잭션 시작
START TRANSACTION;
-- 또는
BEGIN;

-- 작업 수행
INSERT INTO students (name, age) VALUES ('홍길동', 20);
UPDATE students SET age = 21 WHERE name = '김철수';

-- 트랜잭션 확정 (커밋)
COMMIT;

-- 트랜잭션 취소 (롤백)
ROLLBACK;


SAVEPOINT (저장점)

START TRANSACTION;

INSERT INTO students (name, age) VALUES ('user1', 20);

-- 저장점 설정
SAVEPOINT sp1;

INSERT INTO students (name, age) VALUES ('user2', 21);

-- 저장점으로 롤백
ROLLBACK TO SAVEPOINT sp1;

COMMIT;


AUTO COMMIT 설정

-- 자동 커밋 해제
SET autocommit = 0;

-- 자동 커밋 설정
SET autocommit = 1;



제약 조건 (NOT NULL, UNIQUE, PRIMARY KEY)

  • NOT NULL : 해당 필드의 값은 null이 될 수 없다.
  • UNIQUE : 해당 필드의 값은 테이블 내에서 유일해야 한다.
  • PRIMARY KEY : 해당 필드는 기본키(PK)이다.
    기본키는 테이블에서 해당 튜플을 유일하게 식별할 수 있어야 하며, NOT NULL과 UNIQUE를 모두 만족해야 한다.


CONSTRAINT 로 제약조건 설정하기 (예시: 외래 키)

-- constraint example: subject_student (FK) refer student.student.id (PK)

CONSTRAINT fk_subject_student FOREIGN KEY (subject_student) 
REFERENCES student(student_id)

student(student_id) 는 student.student_id 로 바꿔 쓸 수 있으나,
table.fieldname 은 모든 RDBMS 문법에 에 적용되지 않기 때문에 student(student_id) 로 적는 것을 권장합니다.




안전 모드(MySQL)

UPDATE my_table SET price = 1000

만약 실수로 WHERE 와 같은 조건을 지정하지 않고 바로 쿼리를 실행해 버리면 큰일이 날 수 있다.

그래서 MySQL 같은 DBMS에서는 안전 모드를 제공합니다.

SELECT @@sql_safe_updates;

위 쿼리를 실행했을 때 결과가 1이 나오면 지정, 0이 나오면 지정되지 않은 상태입니다.

만약 0인 상태에서는 DELETE 와 UPDATE의 조건(WHERE)를 PK 가 아닌 것으로 지정했을 시, 오류를 발생시킵니다.

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
  • MySQL 기준으로, workbench를 통해 쿼리를 실행할 경우 위의 안전 모드가 기본적으로 적용(1) 되어 있습니다.
  • workbench의 workbench preference 또는 SET SQL_SAFE_UPDATE = 1; 와 같은 명령으로 설정 / 해제 가능합니다.

실제로는 데이터 변경 전 데이터를 미리 조회하는 습관이 중요하다.

참고