SQL 명령어 분류

• DDL (데이터 정의 언어): 데이터베이스와 테이블 구조를 생성하거나 변경하는 명령어
• DML (데이터 조작 언어): 데이터를 삽입, 수정, 삭제하는 명령어
• DQL (데이터 조회 언어): 데이터를 검색하는 SELECT 관련 명령어
• DCL (데이터 제어 언어): 사용자 권한 관리 및 보안 관련 명령어

DDL - 데이터베이스 및 테이블 정의

데이터베이스 작업

-- 새 데이터베이스 생성
CREATE DATABASE mydb;

-- 존재하지 않을 경우에만 생성
CREATE DATABASE IF NOT EXISTS mydb;

-- 모든 데이터베이스 목록 보기
SHOW DATABASES;

-- 특정 데이터베이스 사용
USE mydb;

-- 현재 선택된 데이터베이스의 테이블 목록 확인
SHOW TABLES;

-- 데이터베이스 구조 정보 보기
SHOW CREATE DATABASE mydb;

-- 데이터베이스 삭제
DROP DATABASE mydb;

-- 문자셋 변경
ALTER DATABASE mydb CHARACTER SET utf8;

테이블 작업

-- 테이블 생성
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(30),
    salary DECIMAL(10,2)
);

-- 테이블 구조 확인
DESC employees;

-- 테이블 생성문 확인
SHOW CREATE TABLE employees;

-- 테이블 이름 변경
ALTER TABLE employees RENAME TO staff;

-- 컬럼 추가
ALTER TABLE staff ADD department VARCHAR(20);

-- 컬럼명 변경
ALTER TABLE staff RENAME COLUMN name TO full_name;

-- 컬럼 타입 수정
ALTER TABLE staff MODIFY COLUMN salary DOUBLE;

-- 컬럼 삭제
ALTER TABLE staff DROP department;

-- 테이블 삭제
DROP TABLE staff;

-- 테이블 존재 여부 확인 후 삭제
DROP TABLE IF EXISTS staff;

DML - 데이터 조작

데이터 삽입

-- 모든 컬럼에 값 삽입
INSERT INTO employees VALUES(1, '홍길동', '개발자', 5000000);

-- 특정 컬럼에 값 삽입
INSERT INTO employees(emp_id, name, position) VALUES(2, '김철수', '디자이너');

데이터 삭제

-- 모든 레코드 삭제
DELETE FROM employees;

-- 조건에 맞는 레코드 삭제
DELETE FROM employees WHERE emp_id = 1;

-- 테이블 초기화 (더 효율적인 방식)
TRUNCATE TABLE employees;

데이터 수정

-- 모든 레코드 수정
UPDATE employees SET salary = 5500000;

-- 조건에 맞는 레코드만 수정
UPDATE employees SET salary = 6000000 WHERE position = '팀장';

DQL - 데이터 조회

기본 조회

-- 전체 데이터 조회
SELECT * FROM employees;

범위 조건 조회

-- 급여가 400만원에서 700만원 사이인 직원 조회
SELECT * FROM employees WHERE salary BETWEEN 4000000 AND 7000000;

-- 동일한 조건을 다른 방식으로 표현
SELECT * FROM employees WHERE salary >= 4000000 AND salary <= 7000000;

조건별 조회

단일 조건

-- 특정 직급인 직원 조회
SELECT * FROM employees WHERE position IN ('개발자', '디자이너', '기획자');

복합 조건

-- 여러 조건을 모두 만족하는 직원 조회
SELECT * FROM employees WHERE position = '개발자' AND salary > 5000000 AND name LIKE '김%';

NULL 값 처리

-- NULL이 아닌 값 조회
SELECT * FROM employees WHERE department IS NOT NULL;

-- NULL 값 조회
SELECT * FROM employees WHERE bonus IS NULL;

패턴 매칭

-- 이름이 '이'로 시작하는 직원
SELECT * FROM employees WHERE name LIKE '이%';

-- 두 번째 글자가 '민'인 직원
SELECT * FROM employees WHERE name LIKE '_민%';

-- 이름에 '수'가 포함된 직원
SELECT * FROM employees WHERE name LIKE '%수%';

-- 두 글자 이름인 직원
SELECT * FROM employees WHERE name LIKE '__';

중복 제거

-- 부서 정보 중복 제거하여 조회
SELECT DISTINCT department FROM employees;

정렬

-- 단일 기준 정렬 (오름차순)
SELECT * FROM employees ORDER BY salary;

-- 내림차순 정렬
SELECT * FROM employees ORDER BY hire_date DESC;

-- 다중 기준 정렬
SELECT * FROM employees ORDER BY department ASC, salary DESC;

집계 함수

-- 총 합계
SELECT SUM(salary) AS total_salary FROM employees;

-- 최대값
SELECT MAX(salary) AS max_salary FROM employees;

-- 최소값
SELECT MIN(salary) AS min_salary FROM employees;

-- 평균값
SELECT AVG(salary) AS avg_salary FROM employees;

-- 개수
SELECT COUNT(*) AS employee_count FROM employees;

그룹화

-- 부서별 평균 급여
SELECT department, AVG(salary) FROM employees GROUP BY department;

-- 부서별 평균 급여 및 인원 수
SELECT department, AVG(salary), COUNT(*) FROM employees GROUP BY department;

-- 특정 조건을 만족하는 그룹만 조회
SELECT department, AVG(salary), COUNT(*) 
FROM employees 
WHERE salary > 4000000 
GROUP BY department;

-- 그룹 결과에 조건 적용
SELECT department, AVG(salary), COUNT(*) 
FROM employees 
WHERE salary > 4000000 
GROUP BY department 
HAVING COUNT(*) >= 2;

페이지네이션

-- 상위 5개 레코드
SELECT * FROM employees LIMIT 5;

-- 6번째부터 10개 레코드
SELECT * FROM employees LIMIT 5, 10;

-- 11번째부터 15개 레코드
SELECT * FROM employees LIMIT 10, 5;

조인

-- 내부 조인 (암시적)
SELECT e.name, d.dept_name 
FROM employees e, departments d 
WHERE e.dept_id = d.id;

-- 내부 조인 (명시적)
SELECT e.name, d.dept_name 
FROM employees e INNER JOIN departments d ON e.dept_id = d.id;

-- 왼쪽 외부 조인
SELECT e.name, d.dept_name 
FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;

-- 오른쪽 외부 조인
SELECT e.name, d.dept_name 
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;

서브쿼리

-- 가장 높은 급여를 받는 직원 조회
SELECT * FROM employees 
WHERE salary = (SELECT MAX(salary) FROM employees);

DCL - 권한 및 사용자 관리

사용자 관리

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

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

-- 비밀번호 변경
USE mysql;
UPDATE USER SET PASSWORD=PASSWORD('newpass') WHERE User='newuser' AND Host='localhost';
FLUSH PRIVILEGES;

-- 모든 사용자 조회
USE mysql;
SELECT * FROM USER;

권한 관리

-- 사용자 권한 확인
SHOW GRANTS FOR 'newuser'@'localhost';

-- 권한 부여
GRANT ALL ON company.* TO 'newuser'@'localhost';

-- 특정 권한 회수
REVOKE UPDATE ON company.employees FROM 'newuser'@'localhost';