본문 바로가기
cs

03. SQL

by 왜안돼요 2025. 4. 4.
728x90

데이터 정의 언어 (DDL)

종류 설명
CREATE 데이터베이스 혹은 데이터베이스 객체 생성
ALTER 데이터베이스 객체 갱신
예) 테이블 필드 및 제약 조건을 추가/삭제
DROP 데이터베이스 객체 삭제
예) 데이블이나 데이터베이스를 삭제
TRUNCATE 테이블 구조를 유지한 채 모든 레코드 삭제

 

CREATE

데이터베이스를 생성하는 명령

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

CREATE DATABASE mydb;

SQL문에서는 끝을 표기하기 위해 세미콜론 기호를 사용한다.

 

데이터베이스 조회 및 사용

SHOW DATABASES

이렇게 되면 성공이다. 입니다

 

create table 문을 이용한 테이블 생성은 아래와 같이 작성한다

 

CREATE TABLE 테이블이름 (
    컬럼이름1 데이터타입 제약조건,
    컬럼이름2 데이터타입 제약조건,
    ...
    PRIMARY KEY (기본키컬럼)
);

 

유의할 점은 데이터 타입 우측 혹은 create table 하단에 아래와 같은 키워드를 명시함으로써 특정 필드가 지켜야할 제약 조건을 명시할 수 있다는 점이다.

 

키워드 제약 조건
PRIMARY KEY 특정 필드를 기본키로 지정
UNIQUE 특정 필드가 고유한 값을 갖도록 설정
FOREIGN KEY 특정 필드를 외래 키로 지정
DEFAULT 기본값 기본값 지정
NULL/ NOT NULL 특정 필드에 NULL 값을 허용/허용하지않음

 

예제

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 기본키, 자동 증가
    username VARCHAR(50) NOT NULL,     -- 유저 이름 (50자 제한, 필수)
    email VARCHAR(100) UNIQUE,         -- 이메일 (100자 제한, 중복 불가)
    password VARCHAR(255) NOT NULL,    -- 비밀번호 (암호화 저장해야 함)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 가입 날짜 (자동 입력)
);
  1. id -> 고유한 회원 번호(자동 증가)
  2. username -> 회원이름 (50자 제한, 필수)
  3. email -> 회원 이메일( 100자 제한, 중복 불가)
  4. password -> 비밀번호 (255자, 암호화 저장, 필수)
  5. created_at -> 가입 날짜 (자동입력)
CREATE TABLE posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,  -- 게시글 ID (기본키, 자동 증가)
    user_id INT,                             -- 작성자 ID (users 테이블 참조)
    title VARCHAR(255) NOT NULL,             -- 게시글 제목
    content TEXT NOT NULL,                   -- 게시글 내용
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 작성 시간 자동 입력
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE  -- 외래키 설정 (회원 삭제 시 게시글도 삭제)
);

 

ON DELETE CASCADE -> 만약 해당 유저가 탈퇴하면 그 사람이 쓴 게시글도 자동으로 삭제한다.

 

 

그림과 글에 작성한 테이블 내용이 좀 다를 수 있지만 대충 저렇게 참조한다는 뜻

 

 

PRIMARY KEY, UNIQUE, FOREIGN KEY 제약 조건은 CREATE TABLE문 하단에 추가될 수도 있고, 선택적으로 제약 조건에 이름을 붙일 수도 있다.

 

예제

CREATE TABLE users (
    id INT AUTO_INCREMENT, 
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_users PRIMARY KEY (id),  -- 기본 키 (PK)
    CONSTRAINT uq_users_username UNIQUE (username), -- 고유 키 (UNIQUE)
    CONSTRAINT uq_users_email UNIQUE (email) -- 고유 키 (UNIQUE)
);

 

ALTER

CREATE TABLE문을 통해 생성된 테이블에 새로운 필드를 추가하거나 기존의 필드를 수정/삭제 할 수 있고 제약 조건 또한 새롭게 추가하거나 수정/삭제할 수 있다.

 

새로운 필드 추가

-- ALTER TABLE 테이블_이름 ADD COLUMN 필드_이름 필드_타입 [제약 조건];
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20) NOT NULL;

 

기존 필드 수정

-- ALTER TABLE 테이블_이름 MODIFY 필드_이름 필드_타입 [제약 조건];
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;

-- ALTER TABLE 테이블_이름 CHANGE COLUMN 기존_필드 새_필드 필드_타입 [제약 조건];
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(100) NOT NULL;

 

기존 필드 삭제

-- ALTER TABLE 테이블_이름 DROP COLUMN 필드_이름;
ALTER TABLE users DROP COLUMN phone_number;

 

외래 키 제약 조건 추가

-- ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약_조건_이름 FOREIGN KEY (필드_이름) REFERENCES 참조_테이블(참조_필드);
ALTER TABLE posts ADD CONSTRAINT fk_posts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

 

UNIQUE 제약 조건 추가

-- ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약_조건_이름 UNIQUE (필드_이름);
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

 

NOT NULL 제약 조건 추가

-- ALTER TABLE 테이블_이름 MODIFY 필드_이름 필드_타입 NOT NULL;
ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL;

 

기본 키 추가 (PRIMARY KEY)

-- ALTER TABLE 테이블_이름 ADD PRIMARY KEY (필드_이름);
ALTER TABLE posts ADD PRIMARY KEY (post_id);

 

제약 조건 제거

-- 기본 키 삭제
ALTER TABLE users DROP PRIMARY KEY;

-- 외래 키 삭제
ALTER TABLE posts DROP FOREIGN KEY fk_posts_user;

-- UNIQUE 삭제
ALTER TABLE users DROP INDEX uq_users_email;

 

DROP

테이블이나 데이터베이스를 삭제할 수 있는 명령어

 

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

 

지워졌다!

 

TRUNCATE

테이블의 구조를 유지한 채로 테이블의 모든 레코드를 삭제하는 명령어

TRUNCATE TABLE 테이블_이름;

 

테이블 조작 언어 (DML)

종류 설명
SELETE 테이블의 레코드 조회
INSERT 테이블에 레코드 삽입
UPDATE 테이블의 레코드 수정
DELETE 테이블의 레코드 삭제

 

 

INSERT

테이블에 레코드를 삽입하는 명령어

테이블_이름 이라는 테이블에 필드에 맞는 값들을 삽입하느 ㄴ명령어다.

INSERT INTO 테이블_이름(필드1, 필드2) VALUES (값1, 값2)

 

 

여러 레코드를 한번에 삽입하고 싶을떄

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

 

예제

-- INSERT INTO 테이블_이름 (필드1, 필드2, ...) VALUES (값1, 값2, ...);
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john@example.com', 'hashed_password123');
INSERT INTO users (username, email, password) 
VALUES 
    ('alice', 'alice@example.com', 'password1'),
    ('bob', 'bob@example.com', 'password2'),
    ('charlie', 'charlie@example.com', 'password3');

 

  • SELETE * FROM users: users 테이블(FROM users)의 모든 레코드(*)조회하겠다. 라는 뜻

레코드 삽입시 유의할 점: 무결성 제약 조건을 지켜야한다.

존재하지 않는 레코드를 참조하는 경우에는 무결성 제약 조건에 위배되어 INSERT문의 실행이 거부된다.

 

예제

존재하지 않는 외래 키 값 삽입 시 오류 발생

INSERT INTO posts (user_id, title, content, created_at) 
VALUES (99, '존재하지 않는 유저의 게시글', '이 내용은 저장되지 않습니다.', NOW());
오류발생
Cannot add or update a child row: a foreign key constraint fails
(user_id = 99는 users 테이블에 존재하지 않기 때문)

 

UPDATE와 DELETE

각각 레코드를 수정하고 삭제하는 명령어

 

UPDATE 데이블_이름
	SET 필드1 = 값1, 필드2 = 값2 ...
    WHERE = 조건식
  1. 테이름_이름은 갱신하고자하는 테이블의 이름을 나타내고
  2. 필드1 = 값1, 필드2 = 값2는 각 필드에 대한 대입 연산이다.

where 조건식은 생략이 가능하지만 일반덕으로 대부분의 update 문에서 사용된다

특정 조건에 부합하는 레코드만 선별하기 위한 일종의 필터임

 

set에 명시되는 '=' 대입 연산자이지만 where절에 명시되는 '='는 비교연산자임

 

 

예제

-- 특정 사용자의 이메일 주소 변경
UPDATE users 
SET email = 'new_email@example.com' 
WHERE id = 1;

아이디가 1인 사용자의 email을 new_eamil 어쩌고로 변경

 

여러 필드 동시에 업데이트

-- 특정 사용자의 이름과 비밀번호 변경
UPDATE users 
SET username = 'new_username', password = 'new_hashed_password'
WHERE id = 2;

아이디 2인 사용자의 username과 password를 동시에 변경

 

DELETE문도 UPDATE문과 비슷함.

WHERE절을 통해 삭제하고자 하는 레코드를 식별할 수 있음

WHERE절을 명시하지 않을 경우 테이블의 모든데이터를 삭제하는 명령어가 됨

 

DELETE FROM posts
	WHERE title = 'hi';

 

SELECT

삽입된 레코드를 조회하는 명령어

테이블 내 레코드를 다양하게 정렬하거나 필터링하여 조회도 가능

SELECT 필드1, 필드2, ...  
FROM 테이블_이름  
WHERE 조건식  
GROUP BY 그룹화할_필드  
HAVING 필터_조건  
ORDER BY 정렬할_필드  
LIMIT 레코드_제한;

SELECT 뒤에는 하나 이상의 필드 이름이 명시 될 슁ㅆ음 FROM 절에는 조회하고자 하는 테이블 이름을 명시

 

-- students 테이블 생성
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    major VARCHAR(50),
    gpa DECIMAL(3, 2),
    enrollment_date DATE
);

-- 데이터 삽입
INSERT INTO students (first_name, last_name, age, major, gpa, enrollment_date) VALUES
('Alice', 'Johnson', 20, 'Computer Science', 3.8, '2022-09-01'),
('Bob', 'Smith', 22, 'Mathematics', 3.5, '2020-09-01'),
('Charlie', 'Brown', 21, 'Physics', 3.9, '2021-09-01'),
('David', 'Williams', 23, 'Chemistry', 3.2, '2019-09-01'),
('Eve', 'Davis', 19, 'Biology', 3.6, '2023-09-01');

 

조건 추가

SELECT * FROM students WHERE age >= 21;

 

 

GROUP BY

특정 필드를 기준으로 필드를 그룹화

연산/집계 함수와 함께 사용되는 경우가 많음

SELECT major, COUNT(*)  
FROM students  
GROUP BY major;

전공별 학생 수 조회

 

HAVING

그룹화된 결과에 조건을 적용하기 위해 사용

WHERE절에 명시되는 조건식이 그룹화 되기 전 개별 레코드에 대한 조건식이라면

HAVING절에 명시되는 조건식은 그룹화된 레코드에 대한 조건식이라는 점.

-- HAVING 절을 사용하여 평균 GPA가 3.6 이상인 전공 조회
SELECT major, AVG(gpa)
FROM students
GROUP BY major
HAVING AVG(gpa) >= 3.6;

 

 

ORDER BY

특정 필드를 기준으로 데이터를 정렬하는 데 사용

-- 내림차순
SELECT first_name, last_name, gpa
FROM students
ORDER BY gpa DESC;

-- 오름차순
SELECT first_name, last_name, gpa
FROM students
ORDER BY gpa ASC;

 

LIMIT

조회할 레코드 수를 제한하기 위해 사용

SELECT * FROM students ORDER BY enrollment_date DESC LIMIT 3;

가장 최근 입학한 3명의 학생 조회

 

SELECT문이 작성 순서와 실제 실행 순서에 차이가 있다.

SELECT문은 순차적으로 실행되지 않고

  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • ORDER BY
  • LIMIT

순으로 실행된다.

 

트랜잭션 제어 언어 (TCL)

종류 설명
COMMIT 데이터베이스에 작업 반영
ROLLBACK 작업 이전 상태로 되돌림
SAVEPOINT 롤백의 기준점 설정

 

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_name VARCHAR(50),
    balance INT
);

INSERT INTO accounts (account_id, account_name, balance) VALUES (1, 'Kim', 1000);
INSERT INTO accounts (account_id, account_name, balance) VALUES (2, 'Lee', 500);
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

 

두개의 UPDATE문이 반드시 함께 실행되어야 할 경우 두 UPDATE문을 하나의 트랜잭션으로 구성될 수 있다

여러 작업을 포함하는 트랜잭션을 나타낼 때는 START TRANSACTION 혹은 BEGIN 명령을 사용하여 트랜잭션이 시작됨을 알리는 명령이라고 볼 수 잇음

START TRANSACTION

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

 

 

  • COMMIT: 트랜잭션이 성공적으로 완료되어 트랜잭션에서 수행된 모든 변경 사항을 데이터베이스에 영구적으로 반영한다는 의미
  • ROLLBACK: 트랜잭션에서 수행된 변경 사항을 취소하고, 데이터베이스를 트랜잭션 시작 이전의 상태로 되돌리겠다는 의미

 

ROLLBACK

트랜잭션 이후에 

  1. 업데이트문을 한 번 실행했고
  2. 커밋하지 않고 롤백을 했을 경우
  3. 의 accounts 테이블은 업데이트가 이루어지기전 1번과 같아진다

COMMIT

트랜잭션 이후에

  1. 두개의 업데이트문이 실행되고 
  2. 커밋을 통해 작업을 데이터베이스에 영구적으로 반영
  3. 의 accounts 테이블은 시점 2와 같아짐

SAVEPOINT

ROLLBACK으로 되돌아갈 시점을 지정하는 기능

-- 되돌아갈 시점 지정
SAVEPOINT 세이브포인트_이름

-- 세이브포인트_이름으로 되돌아가는 명령
ROLLBACK TO SAVEPOINT 세이브포인트_이름

 

예제

START TRANSACTION;

-- 세이브포인트 생성 ①
SAVEPOINT sp1;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 세이브포인트 생성 ②
SAVEPOINT sp2;

UPDATE accounts SET account_name = 'new_Kim' WHERE account_id = 1;
UPDATE accounts SET account_name = 'new_Lee' WHERE account_id = 2;

-- 세이브포인트 생성 ③
SAVEPOINT sp3;

SELECT * FROM accounts;

-- 특정 세이브포인트로 롤백
ROLLBACK TO SAVEPOINT sp2;
ROLLBACK TO SAVEPOINT sp1;

 

추가!!! 데이터 제어언어 ~

DCL 대표적인 명령어에는 GRANT와 REVOKE가 있다.

종류 설명
GRANT 사용자에게 권한 부여
REVOKE 사용자로부터 권한 회수

 

두개 다 사용자와 관련된 명령어이다.

RDBMS에서도 접속 가능한 사용자 계정을 생성하거나 삭제할 수 있고, 사용자마다 사용가능한 SQL 명령을 제한하는 등 권한 관리을 관리할 수 있다.

'cs' 카테고리의 다른 글

01-2. 프로그래밍 패러다임  (1) 2025.04.22
01. 디자인 패턴  (0) 2025.04.22
02. RDBMS의 기본  (0) 2025.04.04
01. 데이터베이스  (0) 2025.04.04
04. 전송계층 - TCP와 UDP  (0) 2025.03.19

관련글

최근댓글

최근글

skin by © 2024 ttuttak

티스토리툴바