MySQL 명령어

📌 목차

• 1. 데이터베이스 관리
• 2. 테이블 관리
• 3. 데이터 삽입 (INSERT)
• 4. 데이터 조회 (SELECT)
• 5. 조인 (JOIN)
• 6. 데이터 수정 (UPDATE)
• 7. 데이터 삭제 (DELETE)
• 8. 인덱스 (INDEX)
• 9. 그룹화 & 집계
• 10. 뷰 (VIEW)
• 11. 저장 프로시저 (Stored Procedure)
• 12. 트랜잭션 (Transaction)
• 13. 백업 & 복원
• 14. 사용자 관리 & 권한
• 15. 상태 확인 & 시스템

1. 데이터베이스 관리

-- 현재 서버에 존재하는 모든 데이터베이스 목록 조회
SHOW DATABASES;

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

-- 사용할 데이터베이스 선택 (이후 쿼리는 해당 DB에서 실행됨)
USE mydb;

-- 데이터베이스 삭제 (IF EXISTS: 존재하지 않아도 에러 발생하지 않음)
DROP DATABASE IF EXISTS mydb;

💡 Tip: CREATE DATABASE 시 문자셋 명시 : 한글 등 다국어 데이터 안전하게 저장 가능

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

⬆ 목차로 돌아가기

2. 테이블 관리

DDL(Data Definition Language) : 테이블 생성, 구조 확인, 수정, 삭제

• CREATE : 테이블 생성
• DESC (DESCRIBE) : 테이블 구조 확인
• ALTER : 테이블 구조 수정
• DROP : 테이블 삭제

2-1. 테이블 생성 및 조회

-- 현재 데이터베이스의 모든 테이블 목록 조회
SHOW TABLES;

-- 테이블 생성
CREATE TABLE users (
    id         INT AUTO_INCREMENT PRIMARY KEY,        -- 자동 증가 기본키
    name       VARCHAR(50) NOT NULL,                  -- 이름 (필수)
    email      VARCHAR(100) UNIQUE NOT NULL,           -- 이메일 (유니크 + 필수)
    age        INT DEFAULT 0,                          -- 나이 (기본값 0)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    -- 생성 시각 (자동 기록)
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
               ON UPDATE CURRENT_TIMESTAMP             -- 수정 시각 (자동 갱신)
);

2-2. 테이블 구조 확인

-- 테이블의 컬럼 정보(타입, NULL 여부, 키 등) 간단히 조회
DESCRIBE users;

-- 테이블 생성 쿼리 전체를 확인 (엔진, 문자셋 등 포함)
SHOW CREATE TABLE users;

2-3. 테이블 구조 변경 (ALTER TABLE)

-- 컬럼 추가
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 컬럼 삭제
ALTER TABLE users DROP COLUMN phone;

-- 컬럼 타입 변경 (이름은 유지)
ALTER TABLE users MODIFY age SMALLINT;

-- 컬럼 이름 + 타입 동시 변경
ALTER TABLE users CHANGE name full_name VARCHAR(100);

-- 테이블 이름 변경
ALTER TABLE users RENAME TO members;

2-4. 테이블 삭제

-- 테이블 삭제 (IF EXISTS: 존재하지 않아도 에러 없음)
DROP TABLE IF EXISTS members;

⚠️ DROP TABLE : 테이블 구조와 데이터 모두 삭제

⬆ 목차로 돌아가기

3. 데이터 삽입 (INSERT)

DML(Data Manipulation Language) : 데이터 삽입, 조회, 수정, 삭제

• INSERT : 데이터 삽입
• SELECT : 데이터 조회
• UPDATE : 데이터 수정
• DELETE : 데이터 삭제
  
  

-- 여러 행 한 번에 삽입 (권장: 단건 INSERT보다 성능이 좋음)
INSERT INTO users (name, email, age) VALUES 
('홍길동', 'hong@example.com', 30),
('김철수', 'kim@example.com', 25),
('이영희', 'lee@example.com', 28);

-- SET 구문을 사용한 단건 삽입 (컬럼=값 형태로 직관적)
INSERT INTO users SET name='박민수', email='park@example.com', age=35;

💡 대량 데이터 삽입 시 LOAD DATA INFILE이나 INSERT INTO ... SELECT 구문 활용 (빠름)

⬆ 목차로 돌아가기

4. 데이터 조회 (SELECT)

-- 전체 데이터 조회
SELEC * ROM users;

-- 특정 컬럼만 조회 + 조건(WHERE) + 정렬(ORDER BY)
-- age가 25 이상인 사용자를 나이 내림차순으로 조회
SELECT name, email, age 
FROM users 
WHERE age >= 25 
ORDER BY age DESC;

-- 패턴 검색 (LIKE) + 다중 조건 (OR)
-- 이름에 '길'이 포함되거나 이메일에 'example'이 포함된 사용자
SELEC * ROM users 
WHERE name LIKE '%길%' OR email LIKE '%example%';

-- 집계 함수: 전체 행 수
SELECT COUNT(*) AS total FROM users;

-- 집계 함수: 평균 나이
SELECT AVG(age) AS avg_age FROM users;

-- 페이지네이션: 1페이지 (0번째부터 10개)
SELEC * ROM users LIMIT 10 OFFSET 0;

-- 중복 제거: 고유한 나이 값만 조회
SELECT DISTINCT age FROM users;

💡 OFFSET : 0부터 시작, N페이지 조회 시 OFFSET = (N - 1 * IMIT 으로 계산

⬆ 목차로 돌아가기

5. 조인 (JOIN)

JOIN (데이터 결합) : 다수 테이블 연결 및 조회

5-1. 조인용 테이블 생성

CREATE TABLE orders (
    order_id   INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT,                              -- users 테이블의 id를 참조
    amount     DECIMAL(10,2),                    -- 주문 금액
    order_date DATE,                             -- 주문 일자
    FOREIGN KEY (user_id) REFERENCES users(id)   -- 외래키 설정
);

-- 주문 데이터 삽입
INSERT INTO orders (user_id, amount, order_date) 
VALUES (1, 100000, '2025-12-01');

5-2. INNER JOIN 실행

-- users와 orders를 user_id로 연결하여 조회
SELECT u.name, o.amount, o.order_date 
FROM users u 
JOIN orders o ON u.id = o.user_id;

💡 JOIN 종류 한눈에 보기

종류	설명
INNER JOIN	양쪽 테이블에 모두 일치하는 데이터만 반환
LEFT JOIN	왼쪽 테이블 전체 + 오른쪽 일치 데이터 (없으면 NULL)
RIGHT JOIN	오른쪽 테이블 전체 + 왼쪽 일치 데이터 (없으면 NULL)
CROSS JOIN	양쪽 테이블의 모든 조합 (카테시안 곱)

⬆ 목차로 돌아가기

6. 데이터 수정 (UPDATE)

-- 모든 사용자의 나이를 1 증가 (WHERE 조건 필수, 안전 모드 우회를 위해 id > 0 사용)
UPDATE users SET age = age + 1 WHERE id > 0;

-- 이메일이 NULL인 사용자에게 이름 기반 이메일 자동 설정
UPDATE users SET email = CONCAT(name, '@example.com') WHERE email IS NULL;

⚠️ MySQL Safe Updates 모드 (sql_safe_updates) 주의
- 해당 모드 활성화 시, WHERE 절에 키(Key) 컬럼이 없는 UPDATE 및 DELETE 명령어 실행 불가
- 의도적으로 전체 데이터를 수정하거나 삭제해야 할 경우 WHERE id > 0 등의 우회 조건 추가 필요

⬆ 목차로 돌아가기

7. 데이터 삭제 (DELETE)

-- 특정 행 삭제
DELETE FROM users WHERE id = 1;

-- 조건에 맞는 행 삭제
DELETE FROM users WHERE age < 20;

-- 테이블의 모든 데이터 초기화 (구조는 유지, AUTO_INCREMENT도 초기화)
TRUNCATE TABLE users;

💡 DELETE vs TRUNCATE 차이점

구분	DELETE	TRUNCATE
롤백 가능 여부	✅ 가능 (트랜잭션 내)	❌ 불가능
WHERE 조건	✅ 사용 가능	❌ 사용 불가
속도	상대적으로 느림	매우 빠름
AUTO_INCREMENT	유지됨	초기화됨

⬆ 목차로 돌아가기

8. 인덱스 (INDEX)

INDEX : 조회 성능 향상을 위한 데이터 색인 생성, 수정, 삭제

• CREATE INDEX : 인덱스 생성
• SHOW INDEX : 테이블의 인덱스 정보 확인
• ALTER INDEX : 인덱스 이름 변경 및 구조 수정
• DROP INDEX : 인덱스 삭제
  
  

-- 일반 인덱스 생성 (중복 허용)
CREATE INDEX idx_email ON users(email);

-- 유니크 인덱스 생성 (중복 불허)
CREATE UNIQUE INDEX idx_name ON users(name);

-- 테이블에 설정된 인덱스 목록 조회
SHOW INDEX FROM users;

-- 인덱스 삭제
DROP INDEX idx_email ON users;

💡 인덱스 (Index)
- SELECT 조회 성능 향상, 단 DML(INSERT/UPDATE/DELETE) 실행 시 갱신 비용 발생
- 자주 조회하는 컬럼에 한해 선별적 적용

⬆ 목차로 돌아가기

9. 그룹화 & 집계

GROUP BY / HAVING : 데이터 그룹화 및 조건 필터링

• GROUP BY : 특정 컬럼을 기준으로 데이터 그룹화 (주로 COUNT, SUM, AVG 등 집계 함수와 함께 사용)
• HAVING : 그룹화된 데이터 결과에 대한 조건 필터링 (WHERE은 데이터 그룹화 전, HAVING은 그룹화 후 적용)

-- 나이별 그룹화 후, 인원이 2명 이상인 그룹만 조회
SELECT age, 
       COUNT(*) AS count, 
       AVG(age) AS avg 
FROM users 
GROUP BY age 
HAVING count > 1;

💡 WHERE vs HAVING
WHERE: 그룹화 이전에 개별 행을 필터링
HAVING: 그룹화 이후에 그룹 결과를 필터링

⬆ 목차로 돌아가기

10. 뷰 (VIEW)

VIEW : 자주 사용하는 쿼리를 가상 테이블로 저장 및 재사용

• CREATE VIEW : 뷰 생성
• DROP VIEW : 뷰 삭제

-- 25세 이상 사용자만 보여주는 뷰 생성
CREATE VIEW user_summary AS 
SELECT name, age FROM users WHERE age >= 25;

-- 뷰 조회 (일반 테이블처럼 사용)
SELEC * ROM user_summary;

-- 뷰 삭제
DROP VIEW IF EXISTS user_summary;

💡 뷰 (View)
- 실제 데이터 저장 없이 쿼리 결과만 보여주는 가상 테이블
- 원본 테이블 데이터 변경 시 뷰 결과 자동 반영

⬆ 목차로 돌아가기

11. 저장 프로시저 (Stored Procedure)

STORED PROCEDURE : 자주 사용하는 SQL 로직 서버 저장 및 호출

• CREATE PROCEDURE : 프로시저 생성
• CALL : 프로시저 호출 및 실행
• DROP PROCEDURE : 프로시저 삭제
  
  

-- 프로시저 생성: 입력받은 나이 이상의 사용자 조회
DELIMITER //
CREATE PROCEDURE GetUsersByAge(IN min_age INT)
BEGIN
    SELEC * ROM users WHERE age >= min_age;
END //
DELIMITER ;

-- 프로시저 호출
CALL GetUsersByAge(25);

-- 프로시저 삭제
DROP PROCEDURE IF EXISTS GetUsersByAge;

💡 DELIMITER // :  프로시저 본문 내 세미콜론(;) 오인식 방지용 구분자 임시 변경
- DELIMITER ; : 프로시저 정의 완료 후 기본 구분자 복원

⬆ 목차로 돌아가기

12. 트랜잭션 (Transaction)

TRANSACTION : 다수 SQL 문 단일 작업 단위 결합 및 무결성 보장

• START TRANSACTION : 트랜잭션 시작
• COMMIT : 작업 결과 영구 반영
• ROLLBACK : 작업 취소 및 이전 상태 복구
  
  

-- 트랜잭션 시작
START TRANSACTION;

-- 작업 1: 데이터 삽입
INSERT INTO users (name, email, age) VALUES ('테스트', 'test@example.com', 20);

-- 작업 2: 데이터 수정
UPDATE users SET age = 21 WHERE name = '테스트';

-- 모든 작업이 정상이면 확정
COMMIT;

-- 문제가 발생했다면 모든 작업을 되돌림
-- ROLLBACK;

💡 ACID 원칙  : 트랜잭션 정합성 보장 필수 속성

• 원자성(Atomicity) : 작업 단위 전체 성공 또는 전체 실패 처리
• 일관성(Consistency) : 트랜잭션 전후 데이터 규칙 및 상태 유지
• 격리성(Isolation) : 트랜잭션 상호 간 간섭 및 영향 배제
• 지속성(Durability) : 성공한 결과의 영구적 저장 및 보존
• 활용 : 금융 거래, 재고 처리 등 데이터 무결성 중요 작업

⬆ 목차로 돌아가기

13. 백업 & 복원

BACKUP & RESTORE : 데이터 유실 대비 터미널(CLI) 기반 백업 및 복원

# 데이터베이스 백업 (SQL 파일로 내보내기)
mysqldump -u root -p mydb > backup.sql

# 데이터베이스 복원 (SQL 파일 가져오기)
mysql -u root -p mydb < backup.sql

• 백업 실행 : mysqldump -u root -p [DB명] > [파일명].sql
• 복원 실행 : mysql -u root -p [DB명] < [파일명].sql
• 전체 DB 백업 : mysqldump -u root -p --all-databases > all_backup.sql
• 특정 테이블 백업 : mysqldump -u root -p mydb users > users_backup.sql
• 자동화 : 운영 환경 내 crontab 활용 백업 스케줄링 적용 권장

⬆ 목차로 돌아가기

14. 사용자 관리 & 권한

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

-- 특정 권한만 부여 (SELECT, INSERT, UPDATE)
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'dev'@'localhost';

-- 모든 권한 부여
GRANT ALL PRIVILEGES ON mydb.* TO 'dev'@'localhost';

-- 권한 변경 사항을 즉시 반영
FLUSH PRIVILEGES;

-- 사용자에게 부여된 권한 확인
SHOW GRANTS FOR 'dev'@'localhost';

-- 모든 권한 회수
REVOKE ALL PRIVILEGES ON mydb.* FROM 'dev'@'localhost';

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

⚠️ DCL(Data Control Language) : 사용자 생성 및 권한 관리

• CREATE USER : 사용자 생성
• GRANT : 권한 부여
• REVOKE : 권한 회수
• DROP USER : 사용자 삭제
• 보안 팁 : 최소 권한 원칙(Principle of Least Privilege) 준수 및 운영 환경 내 ALL PRIVILEGES 남용 금지

⬆ 목차로 돌아가기

15. 상태 확인 & 시스템

SERVER MONITORING : 서버 상태 및 시스템 정보 확인

• top / htop : 실시간 CPU 및 메모리 점유율 모니터링
• df -h : 디스크 여유 공간 및 사용 현황 확인
• du -sh : 디렉토리별 디스크 사용량 합계 조회
• free -m : 물리 메모리 및 스왑 메모리 상태 확인
• uptime : 시스템 가동 시간 및 평균 부하 확인
• ps aux : 전체 프로세스 실행 현황 및 자원 점유 조회
• netstat -ntlp / ss -ntlp : 네트워크 포트 활성화 및 연결 상태 확인
• lscpu : CPU 아키텍처 및 상세 정보 조회
• uname -a : 운영체제 버전 및 커널 정보 확인

-- 현재까지의 연결 수 등 상태 변수 조회
SHOW STATUS LIKE 'Connections%';

-- MySQL 버전 관련 시스템 변수 조회
SHOW VARIABLES LIKE 'version%';

-- MySQL 버전 확인
SELECT VERSION();

-- 현재 사용 중인 데이터베이스 확인
SELECT DATABASE();

-- 서버 상태 요약 정보 (연결 정보, 버전, 스레드 등)
STATUS;

-- MySQL 종료
EXIT;

⬆ 목차로 돌아가기

주요 카테고리별 요약

카테고리	핵심 키워드
데이터베이스/테이블 관리	CREATE, ALTER, DROP, SHOW
데이터 조작 (CRUD)	INSERT, SELECT, UPDATE, DELETE
조회 심화	JOIN, GROUP BY, HAVING, LIKE, LIMIT
성능 & 재사용	INDEX, VIEW, Stored Procedure
안정성 & 보안	TRANSACTION, GRANT, REVOKE, mysqldump

⬆ 목차로 돌아가기