1. DB 접속
mysql -u [username] -p -h [hostname] -p [port] [database_name]
2. DB 생성
CREATE DATABASE [database 명];
USE [database 명];
3. Table 생성
예시
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100)
);
테이블 안에 넣을 컬럼에 대해서는
[컬럼명] 타입 추가설정(기본키, 외래키, 디폴트, null 여부 등)의 순서로 작성해주면 된다.
기본키: [column name] [type] AUTO_INCREMENT PRIMARY KEY
*** (AUTO_INCREMENT는 필수는 아니며, 기본키 자동 증가 설정을 해주는 요소이다.)Null 불가: [column name] [type] NOT NULLNull 허용: [column name] [type] DEFAULT NULL기본값: [column name] [type] DEFAULT [기본값]외래키:
[외래키 column name] [type],
CONSTRAINT [외래키 설정 명] FOREIGN KEY ([외래키 column name]) REFERENCES [기본키 table]([기본키 column name])
*** ([외래키 설정 명]은 주로 fk_[기본키 table]의 형식으로 많이 설정하는 것 같다!)
4. 기본 CRUD
1) INSERT
명령어
INSERT INTO [테이블명] ([컬럼1], [컬럼2], ...) VALUES ([컬럼1 넣을 값], [컬럼2 넣을 값], ...);
예시
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
2) SELECT
명령어
전체 컬럼 => SELECT * FROM [테이블명] WHERE [조건];
일부 컬럼 => SELECT [컬럼1], [컬럼2], [컬럼3], ... FROM [테이블명] WHERE [조건];
예시
SELECT * FROM users WHERE email = 'alice@example.com';
SELECT name FROM users WHERE email = 'alice@example.com';
3) UPDATE
명령어
UPDATE [테이블명] SET [변경할 컬럼1] = [변경할 값], [변경할 컬럼2] = [변경할 값], ... WHERE [조건];
예시
UPDATE users SET name = 'Alicia' WHERE id = 1;
4) DELETE
명령어
DELETE FROM [테이블명] WHERE [조건];
예시
DELETE FROM users WHERE id = 1;
5. Table 구조 조회 및 관리
1) 테이블 목록 조회
SHOW TABLES;
2) 테이블 스키마 조회
DESCRIBE users;
6. Transaction 관리
1) Transaction이란?
- 여러 쿼리의 집합을 하나의 작업으로 묶어 실행한다.
- COMMIT : 트랜잭션이 성공적으로 완료되었을 때, 변경된 데이터를 영구적으로 반영
- ROLLBACK : 트랜잭션 중 오류가 발생했을 때, 작업 취소 후 이전 상태로 돌림
2) 주요 특징
- Atomicity(원자성) : 트랜잭션 내의 모든 작업이 모두 완료되거나 모두 실패해야 한다.
- Consistemcy(일관성) : 트랜잭션이 완료되면 데이터베이스는 일관된 상태를 유지해야 한다.
- Isolation(고립성) : 트랜잭션 중에 수행되는 작업은 다른 트랜잭션에 영향을 받지 않아야 한다.
- Durability(지속성) : 트랜잭션이 성공적으로 완료되면, 그 변경 내용은 영구적으로 저장된다.
3) 관련 명령어
START TRANSACTION;
INSERT INTO employees (employee_id, name) VALUES (101, 'John');
SAVEPOINT savepoint1;
INSERT INTO employees (employee_id, name) VALUES (102, 'Jane');
ROLLBACK TO savepoint1;
INSERT INTO employees (employee_id, name) VALUES (103, 'Mike');
COMMIT;
- 위의 예시로 설명해보면,
START TRANSACTION으로 트랜잭션을 시작했다. INSERT작업을 진행한 뒤,COMMIT이 아닌SAVEPOINT를 지정해주었다. 이를 통해저장점을 설정한 것이다.- 다시
INSERT시행 후 SAVEPOINT1로 돌아가는ROLLBACK을 실행했다. - 그러면 두 번째 'Jane'에 대한
INSERT는 취소되고, 'John'을 저장한 상태로 돌아간다. - 세 번째로 'Mike'에 대해 INSERT 진행 후,
COMMIT으로 데이터를 영구 저정했다. - 결론적으로 employees라는 테이블에는 'John'과 'Mike'만 저장된 것이다.
4) 격리 수준(Isolation Levels)
- 동시에 실행되는 여러 트랜잭션 간의 간섭 정도를 설정하는 것이다. 고립성에 따라 동시성 제어가 이루어진다.
READ UNCOMMITTED: 커밋되지 않은 데이터도 읽을 수 있는 가장 낮은 고립성 수준
READ COMMITTED: 다른 트랜잭션에서 커밋된 데이터만 읽는 수준으로 Oracle 기본값
REPEATABLE READ: 트랜잭션이 완료될 때까지 같은 데이터를 반복해서 읽을 수 있도록 보장
SERIALIZABLE: 가장 엄격한 고립성 수준으로, 트랜잭션 간 데이터베이스가 완전히 고립되도록 보장
설정 예시
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
7. DB 및 USER 관리
1) 사용자 생성
명령어
CREATE USER '[사용자명]'@'[호스트]' IDENTIFIED BY '[비밀번호]';
GRANT [부여할 권한] ON [데이터베이스명]. TO '[사용자명]'@'[호스트]';
FLUSH PRIVILEGES;
예시
CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON my_database. TO 'my_user'@'localhost';
FLUSH PRIVILEGES;
FLUSH PRIVILEGES; 는 변경된 권한을 적용한다는 의미!
2) 사용자 권한 부여
명령어
GRANT [부여권한1], [부여권한2], ... ON [데이터베이스명].[테이블명] TO '[사용자명]'@'[호스트]';
예시
GRANT SELECT, INSERT, UPDATE, DELETE ON my_database.users TO 'my_user'@'localhost';
8. INDEX 생성
명령어
CREATE INDEX [인덱스명] ON [테이블명]([대상 컬럼명]);
예시
CREATE INDEX idx_users_email ON users(email);
9. JOIN
1) INNER JOIN
예시
SELECT u.user_id, u.name, p.profile_id, p.age
FROM Users u
INNER JOIN Profiles p
ON u.user_id = p.user_id;
- 두 테이블에서 공통된 값을 기준으로 매칭된 데이터를 조회한다
=> Users 테이블과 Profiles 테이블에서 user_id가 일치하는 값들을 출력
2) LEFT (OUTER) JOIN
예시
SELECT u.user_id, u.name, p.profile_id, p.age
FROM Users u
LEFT JOIN Profiles p
ON u.user_id = p.user_id;
- 왼쪽 테이블을 기준으로 데이터를 매칭한다.
- 오른쪽 테이블에 일치하는 데이터가 없는 경우, 해당 부분은 Null로 표시한다.
=> Users 테이블의 모든 데이터를 가져오고, Profiles와 일치하지 않는 경우에는 Null로 표시
3) RIGTH (OUTER) JOIN
예시
SELECT u.user_id, u.name, p.profile_id, p.age
FROM Users u
RIGHT JOIN Profiles p
ON u.user_id = p.user_id;
- 오른쪽 테이블을 기준으로 데이터를 매칭한다.
- 왼쪽 테이블에 일치하는 데이터가 없는 경우, 해당 부분은 Null로 표시한다.
=> Profiles 테이블의 모든 데이터를 가져오고, Users와 일치하지 않는 경우에는 Null로 표시
4) FULL (OUTER) JOIN
예시
SELECT u.user_id, u.name, p.profile_id, p.age
FROM Users u
LEFT JOIN Profiles p
ON u.user_id = p.user_id
UNION
SELECT u.user_id, u.name, p.profile_id, p.age
FROM Users u
RIGHT JOIN Profiles p
ON u.user_id = p.user_id;
- 양쪽 테이블의 모든 행을 가져오며, 일치하는 않는 부분은 Null로 표시
- MySQL은 지원하지 않으므로
UNION을 통해 LEFT JOIN, RIGHT JOIN 값을 합치면 된다.
=> 양쪽 테이블에서 모든 데이터를 가져오고, 일치하지 않는 데이터는 NULL로 표시
5) CROSS JOIN
예시
SELECT u.user_id, p.profile_id
FROM Users u
CROSS JOIN Profiles p;
- 두 테이블의 모든 행을 조합하여
카티전 곱(Cartesian Product)을 생성한다.
=> Users 테이블의 각 행과 Profiles 테이블의 각 행을 조합하여 결과 조회
6) SELF JOIN
예시
SELECT e1.employee_id, e1.name, e2.employee_id, e2.name
FROM Employees e1
INNER JOIN Employees e2
ON e1.manager_id = e2.employee_id;
- 동일한 테이블을 두 번 사용해서 테이블 안의 데이터끼리 조인할 때 사용한다.
=> Employees 테이블에서 관리자의 employee_id와 직원의 manager_id를 매칭하여 결과 조회
10. DB 백업 및 복구
1) 백업
mysqldump -u root -p [backup_DBname] > [backup_filename]
[backup_DBname]: 백업 대상인 DB의 명을 넣어주면 된다.[backup_filename]: 백업 데이터가 저장될 파일의 이름을 지정하는 것으로, 원하는 명칭.sql 형식이나 경로를 포함하여 /path/to/backup/my_backup.sql로도 지을 수 있다.
2) 복구
mysql -u root -p [backup_DBname] > [backup_filename]
