CAB TA 4기 20일차의 기록

Urban Jungle·2025년 4월 8일
CAB TA 4기

CAB TA 4기

목록 보기
20/28

MySQL 기본 실습

MySQL 접속

mysql -u root -p

  • -u root: root 사용자로 접속
  • -p: 비밀번호 입력을 위한 옵션

데이터베이스 목록 확인

SHOW DATABASES;

  • 현재 MySQL 서버에 존재하는 데이터베이스 목록을 출력함

특정 데이터베이스 사용

USE test;

  • test라는 데이터베이스를 사용하겠다는 의미
  • 이후의 모든 쿼리는 이 데이터베이스에 적용됨

현재 사용 중인 데이터베이스 안의 테이블 목록 확인

SHOW TABLES;

  • 현재 선택한 데이터베이스(USE 명령어로 선택한 DB) 안에 존재하는 테이블을 보여줌

시스템 데이터베이스 접근

USE mysql;

  • mysql 데이터베이스는 사용자 계정 및 권한 정보 등이 저장된 시스템 DB
  • 사용자 관련 설정을 변경하거나 확인할 때 사용됨

시스템 테이블 목록 확인

SHOW TABLES;

  • mysql 데이터베이스 내부의 모든 시스템 테이블을 나열함

사용자 정보 테이블 구조 확인

DESCRIBE user;

  • user 테이블의 컬럼(열) 정보 출력
  • 각 필드의 이름, 타입, NULL 허용 여부, 기본값 등을 확인할 수 있음

SELECT / WHERE 실습

SELECT 기본 구문

기본 형식

SELECT column1, column2, ... FROM table;

예제

SELECT host FROM user;
SELECT host, user, password FROM user;

WHERE 조건절

숫자 조건 검색

비교 연산자 사용

SELECT host, user, password, max_updates FROM user WHERE max_updates = 0;
SELECT host, user, password, max_updates FROM user WHERE max_updates > 0;
SELECT host, user, password, max_updates FROM user WHERE max_updates >= 0;
SELECT host, user, password, max_updates FROM user WHERE max_updates != 1;
SELECT host, user, password, max_updates FROM user WHERE max_updates <> 1;

범위 검색

SELECT host, user, password, max_updates FROM user WHERE max_updates BETWEEN -1 AND 1;
SELECT host, user, password, max_updates FROM user WHERE max_updates BETWEEN 0 AND 1;
SELECT host, user, password, max_updates FROM user WHERE max_updates BETWEEN 1 AND 2;

여러 값 중 하나

SELECT host, user, password, max_updates FROM user WHERE max_updates IN (1, 2, 3, 4);
SELECT host, user, password, max_updates FROM user WHERE max_updates IN (1, 2, 3, 0);

NULL 여부 검사

SELECT host, user, password, max_updates FROM user WHERE max_updates IS NULL;
SELECT host, user, password, max_updates FROM user WHERE max_updates IS NOT NULL;

문자열 조건 검색

같음 (=)

SELECT host, user FROM user WHERE host = 'localhost';

LIKE (패턴 매칭)

정확히 일치

SELECT host, user FROM user WHERE host LIKE 'localhost';

local 뒤에 한 글자

SELECT host, user FROM user WHERE host LIKE 'local_';

local로 시작하는 문자열

SELECT host, user FROM user WHERE host LIKE 'local_%';

두 번째 글자가 o인 패턴

SELECT host, user FROM user WHERE host LIKE '_ocal%';

ocal로 시작

SELECT host, user FROM user WHERE host LIKE 'ocal%';

localhost로 시작

SELECT host, user FROM user WHERE host LIKE 'localhost%';

앞에 글자 5개 + host로 시작

SELECT host, user FROM user WHERE host LIKE '_____host%';

특정 포맷 패턴

SELECT host, user FROM user WHERE host LIKE '_o___host%';

논리 연산자와 LIKE

SELECT host, user FROM user WHERE host LIKE '%local%' AND user LIKE 'root';
SELECT host, user FROM user WHERE host LIKE '%local%' OR user LIKE 'root';

DML 실습: INSERT / UPDATE / DELETE

준비 작업: 테이블 생성

USE test;

CREATE TABLE user (
  name VARCHAR(10),
  uid INT,
  locate VARCHAR(10),
  email VARCHAR(20)
);
  • VARCHAR(n) : 실제 입력된 글자 수만큼 저장.
  • CHAR(n) : 항상 고정된 길이(n)만큼 공간 차지.
  • INT : 정수 타입
DESCRIBE user;
SELECT * FROM user;

INSERT 구문

컬럼 순서를 지정하여 삽입

INSERT INTO user (name, uid, locate, email)
VALUES ('Lee', 1, 'seoul', 'lee@test.example.com');
INSERT INTO user (uid, name, email, locate)
VALUES (2, 'KIM', 'kim@test.example.com', 'daegu');

컬럼 순서 생략 (모든 컬럼 순서대로 입력)

INSERT INTO user
VALUES ('Lim', 3, 'kyunggi', 'lim@test.example.com');

컬럼 순서 생략 시, 순서가 다르면 오류 발생 가능

INSERT INTO user
VALUES (4, 'kyunggi', 'park@test.example.com', 'Park');

입력 결과 확인

SELECT * FROM user;

UPDATE 구문

특정 조건의 행 데이터 변경

UPDATE user
SET name = 'Park'
WHERE uid = 0;

DELETE 구문

특정 조건의 행 삭제

DELETE FROM user
WHERE uid = 0;

WHERE 절 누락 주의

UPDATE 구문 – 전체 레코드 변경됨

UPDATE user
SET locate = 'busan';
  • WHERE 조건이 없기 때문에 user 테이블의 모든 행의 locate 컬럼이 'busan'으로 변경됨

결과

SELECT * FROM user;
  • 모든 사용자 데이터의 locate가 'busan' 으로 동일하게 설정됨

DELETE 구문 – 전체 레코드 삭제됨

DELETE FROM user;
  • WHERE 조건이 없으면 user 테이블의 모든 행이 삭제됨

결과

SELECT * FROM user;
  • 결과 없음 (빈 테이블)

실무에서 반드시 기억해야 할 안전 수칙

  1. UPDATE / DELETE 사용 시 항상 WHERE 조건부터 작성하는 습관을 들이기

  2. 실수로 실행하지 않도록 SELECT로 먼저 조건 테스트하고, 조건이 맞는지 확인한 후에 UPDATE/DELETE 실행

  3. 트랜잭션 사용이 가능한 환경이라면 꼭 BEGIN, ROLLBACK, COMMIT 구조로 안전하게 작업

트랜잭션(Transaction) 설정 및 확인 실습

현재 트랜잭션 자동 커밋 설정 확인

SHOW VARIABLES LIKE 'auto%';
  • autocommit = ON 이면 각 SQL 실행 시 자동으로 커밋됨
  • autocommit = OFF 이면 명시적인 COMMIT 없이는 반영되지 않음

트랜잭션 수동 커밋 모드 설정

SET autocommit = OFF;
  • 이 설정은 현재 세션에만 적용됨
  • 여러 쿼리를 하나의 트랜잭션처럼 묶어서 실행할 수 있음

INSERT 실습

INSERT INTO user
VALUES ('Lim',3,'kyunggi','lim@test.example.com');
SELECT * FROM user;
  • 입력된 데이터 확인 가능
  • 하지만 아직 COMMIT 하지 않았으므로 확정된 것은 아님

ROLLBACK 수행

ROLLBACK;
SELECT * FROM user;
  • 방금 넣은 데이터는 되돌려짐 (롤백됨)

세션 종료 및 재접속 후 확인

exit

mysql -u root -p

SHOW VARIABLES LIKE 'autocommit';
  • autocommit = ON (기본값으로 복귀됨)

TIP

autocommit = OFF 상태에서 실수 없이 작업하려면 다음 흐름 추천

START TRANSACTION;
-- SQL 작업들 실행
COMMIT; -- 저장
-- 또는 ROLLBACK; -- 취소

MySQL autocommit 영구 OFF 설정

설정 파일 편집

vi /etc/my.cnf

[mysqld]
autocommit = 0
  • [mysqld] 섹션 아래에 autocommit = 0 추가
  • 이는 MySQL 서버가 시작될 때 autocommit을 OFF 상태로 유지하게 만듦

MySQL 서비스 재시작

systemctl restart mysql

  • 변경된 설정이 반영되도록 MySQL 서비스를 재시작함

MySQL 접속 후 확인

mysql -u root -p

SHOW VARIABLES LIKE 'autocommit';
  • Value가 OFF로 표시되면 성공적으로 영구 설정 완료

참고

  • SET autocommit = OFF; 는 현재 세션에만 일시적 적용
  • my.cnf의 autocommit = 0 설정은 모든 사용자의 기본값 변경

MySQL 테이블 수정 실습 정리

테이블에 컬럼 추가

age 정수형 컬럼 추가

ALTER TABLE user ADD age INT;

level 숫자 자리수 제한(표현) 포함 컬럼 추가

ALTER TABLE user ADD level INT(5);

컬럼 삭제

ALTER TABLE user DROP level;
  • level 컬럼 삭제

컬럼 타입 수정

ALTER TABLE user MODIFY email INT;
  • email을 INT로 수정 (기존 값이 문자열이라면 데이터 변환 주의)

기본 키 지정

ALTER TABLE user ADD PRIMARY KEY(uid);
  • uid를 기본 키로 지정

NOT NULL 제약 조건 추가

ALTER TABLE user MODIFY name VARCHAR(10) NOT NULL;
  • name에 NOT NULL 제약조건 추가

UNIQUE 제약 조건 추가 시도

ALTER TABLE user MODIFY locate VARCHAR(10) UNIQUE;
  • 에러 발생: 기존 데이터에 중복된 값이 존재함

해결 팁

  • 중복 데이터 제거 또는 정리 후 다시 시도해야 UNIQUE 제약 조건을 적용할 수 있음
SELECT locate, COUNT(*) FROM user GROUP BY locate HAVING COUNT(*) > 1;
  • 위 쿼리로 중복된 locate 확인 가능

MySQL 사용자 계정 및 권한 관리 실습

mysql 시스템 DB 사용

USE mysql;
SHOW TABLES;
DESCRIBE user;
SELECT host, user FROM user;
  • mysql.user 테이블 구조 확인
  • 현재 등록된 사용자 계정 리스트 확인

사용자 계정 생성

CREATE USER user01@localhost IDENTIFIED BY '123';
  • user01 이라는 사용자 계정 생성 (접속 호스트는 localhost)
  • 호스트는 IP 형식, %, 127.0.0.1 등으로도 지정 가능

권한 확인

DESCRIBE user;
SELECT Select_priv, user FROM user;
  • 사용자의 권한 필드 확인 가능 (Select_priv, Create_priv 등)

사용자 권한 확인

SHOW GRANTS FOR user01@localhost;
SHOW GRANTS FOR root@localhost;
  • 특정 사용자가 가진 권한 리스트 확인

권한 부여

  • test DB의 user 테이블에 대해 SELECT, CREATE, DROP 권한 부여
GRANT SELECT, CREATE, DROP ON test.user TO user01@localhost;
SHOW GRANTS FOR user01@localhost;

권한 회수 (REVOKE)

지정한 권한만 회수 가능 (전체 회수 아님)

REVOKE CREATE, DROP ON test.user FROM user01@localhost;

SELECT 권한만 남았는지 확인

SHOW GRANTS FOR user01@localhost;

MySQL 데이터베이스 백업 & 복원

백업 (Dump)

mysqldump -u root -p test > test.dump

  • test 데이터베이스를 백업하여 test.dump 파일로 저장

  • -u root -p : root 사용자로 비밀번호 입력 후 접속

테이블 삭제 테스트

mysql -u root -p

USE test;
SHOW TABLES;

DROP TABLE user;

SHOW TABLES;
exit;
  • 백업 테스트를 위해 user 테이블 삭제

백업 파일로 복원

mysql -u root -p test < test.dump

  • test.dump 파일을 test 데이터베이스에 덮어쓰기
  • 삭제되었던 user 테이블이 복구됨

복원 확인

mysql -u root -p

USE test;
SHOW TABLES;
  • user 테이블이 다시 존재하는지 확인

profile
Urban Jungle
똑똑해지고 싶은 공학도
이전 포스트

CAB TA 4기 19일차의 기록

다음 포스트

CAB TA 4기 21일차의 기록

0개의 댓글