Recording/멋쟁이사자처럼 BE 13기

[멋쟁이사자처럼 부트캠프 TIL 회고] BE 13기_14일차_'DML, DDL, TCL'

LEFT 2024. 12. 19. 18:03

🦁멋쟁이사자처럼 백엔드 부트캠프 13기 🦁
TIL 회고 - [14]일차

🚀14일차에는 DML (데이터 조작어)를 SELECT, INSERT, UPDATE, DELETE 로 많은 예제를 진행해보았고,

DDL (데이터 정의어)를 통해 CREATE, ALTER, DROP 등으로 테이블을 생성해보고 데이터베이스를 설계해볼 수 있었다.

DCL (데이터 제어어)는 GRANT, REVOKE 등으로 객체들에게 권한을 설정 및 해제하는 명령이다. 

TCL (트랜잭션 제어어)를 통해서는 수정했던 사항들을 데이터베이스에 적용할 것인지를 COMMIT / ROLLBACK으로 제어하는 방법을 배울 수 있었다. 개념적으로는 큰 범주에서 배운 것 같은데 INSERT를 활용하는 방법, CREATE를 활용하는 방법 등 다양한 예제를 통해 SQL의 문법을 익히게 되었다.💡

Set Operator

  • 두 질의의 결과를 가지고 집합 연산을 수행
  • UNION, UNION ALL, INTERSECT, MINUS 가 있음

UNION

  • 연습용 테이블 만들기 a, b

  • a테이블에는 1, 2, 3 —- b 테이블에는 2, 3, 4 값 넣기
-- a테이블에 값 넣기 (values()) 
insert into a values(1); 
insert into a values(2); 
insert into a values(3); 

-- b테이블에 값 넣기 (values()) 
insert into b values(2); 
insert into b values(3); 
insert into b values(4);
-- a, b테이블 값 확인
select * from a;
select * from b;
  • a테이블과 b테이블 union해보기 (합집합)

a테이블, b테이블 UNION 연산 결과

INTERSECT

  • MySQL에서 지원하지 않았지만, 최근 추가된 기능인것처럼 잘 동작한다.

  • 경고가 있어도 조회가 잘 되는 것을 확인할 수 있다.
  • 요약 : A INTERSECT B : A 테이블 안에 B 테이블의 요소들을 모두 삭제한 남은 A테이블의 값

MINUS

  • MySQL에서 지원되지 않는 기능

-- 실습. A가 가진 수 중에서 B에 포함 되지 않은 것들 조회
select A.name from A where A.name not in (select B.name from B);
-- 1출력 (1, 2, 3 <-> 2, 3, 4) 이므로 1이 출력
  • NOT IN을 사용하여 MINUS연산의 기능을 MySQL에서 대체할 수 있다.

DDL 데이터 정의어

 

CREATE

  • CREATE TABLE : 테이블 생성
  • ALTER TABLE : 테이블 관련 수정/변경
  • DROP TABLE : 테이블 삭제
  • RENAME : 이름 변경
  • TRUNCATE : 테이블의 모든 데이터 삭제
  • COMMENT : 테이블에 설명 추가
  • 💡CRAETE, ALTER, DROP이 자주 쓰이는 구문

▶️실습 - BOOK 테이블 생성

  • 필요한 컬럼 : id - int , title - 문자 (varchar(150)), author - varchar(100), published_date - date, price - int
-- 실습. Book 테이블 만들기
-- 필요한 컬럼 : id - int , title - 문자 (varchar(150)), 
-- author - varchar(100), published_date - date, price - int
create table Book(
    id int primary key auto_increment,
    title varchar(150) not null,
    author varchar(100) not null,
    published_date date,
    price int
);
  • SQL에서는 문자타입을 varchar로 사용
  • 출판일자는 date타입으로 할당, default를 활용하여 current_timestamp 의 옵션을 주어
    값을 주지 않을때는 현재의 시간을 표기할 수 있도록 할 수 있다.
  • 이처럼 default 예약어를 이용하여 값이 들어오지 않을 경우에는 기본값으로 어떠한 값을 주겠다를 선언 가능
  • 가격은 decimal(10, 2)로 소수점까지 표현하는데 전체 10자리 공간에 소수점은 2자리까지표현한다. 
  • Book 테이블의 id값은 primary key와 auto_increment옵션을 넣어줄 수 있음

❓기본키를 지정했을때와 지정하지 않았을때의 차이점

➡️ 값이 중복이 될수 있느냐 없느냐를 판단

  • 이처럼 기본키를 지정하지 않으면 id값이 중복되어 데이터를 구분 불가능
  • 이럴때 기본키를 지정할 수 있다.
-- 2. 기본키를 지정했을 경우
create table PrimaryTest(
    id int primary key,
    name varchar(100)
);

insert into PrimaryTest values(1, 'JAVA');

  • 이처럼 중복된 값을 다시 insert하려고하면 에러코드가 발생한다.

 

insert into PrimaryTest values(1, 'JAVA');
insert into PrimaryTest values(2, 'JAVA');
  • 이처럼 기본키에 해당하는 값을 바꿔주어 넣어주어야 에러코드가 발생하지 않는다.
  • INSERT INTO 테이블명 VALUES (값1, 값2 …) = DML 데이터 조작어

기본키 관련 - auto_increment 옵션

  • 지정된 키 값 중(ex. 기본키) 가장 큰 값의 키를 기준으로 증가연산을 수행한다.
    → id = 1이 아니더라도 id = 40을 넣어주게되면
  • 그 이후로 이전에 넣었던 가장 큰 값의 키를 찾아 41이 들어가게된다.

  • 사용자가 직접 id값을 지정하지 않아도 auto_increment옵션을 지정하면
    자동으로 id값이 증가하게된다.
테이블명 (추가할컬럼) values (name값);
  • INSERT를 수행할때 추가할 컬럼을 명시하여 그 값을 넣어줄 수도 있다.
insert into auto_exam (name) values ('JAVA'); -- (O) 컬럼 명시 후 해당 컬럼 값 넣기

insert into auto_exam values ('JAVA'); -- (X) 에러 발생

-- insert into 테이블명 [(컬럼들...)] values(값들...)
-- 여기서 테이블명 뒤에오는 컬럼들은 생략이 가능한 것이다.
  • ‘JAVA’를 넣고자하지만, 컬럼값은 id와 name 2개이므로 values 명령이 수행되지 않고 에러코드가 발생된다.
    따라서 테이블명 [컬럼들… ] 부분을 생략할 경우에는 values 안에 테이블의 모든 컬럼의 값들이 나열되어야한다.
  • 반면 테이블명 [컬럼들...] 부분을 명시하면, 그 명시한 컬럼의 값만 넣어주면 된다.

  • 테이블명 뒤에오는 컬럼들의 순서는 중요하지 않지만,
    values에 값을 넣을때는 컬럼을 명시한 순서대로 넣어주어야한다.

ALTER

  • 테이블에 대한 정보를 수정 가능
-- 수정 ALTER
alter table user_accounts
add constraint fk_user_roles
foreign key (role_id) references roles(id)
-- on delete cascade : 이 옵션은 삭제 시 같이 삭제 하게 함
on delete set null; -- 이 옵션은 삭제될때 null로 바꾸게 할 것
  • 이 쿼리는 alter table로 user_accounts테이블에 제약조건을 추가한다. (= 외래키를 만드는 쿼리)
  • on조건으로
    외래키가 지워질때의 조건을 줄 수 있다.
  • cascade 옵션으로 외래키가 지워질때 > 그 외래키를 지닌 값들도 같이 삭제하게할 것
  • set null 옵션으로 삭제될떄 그 외래키를 지녔던 값들은 외래키 값이 null로 바뀌게 할 것

  • on조건은 이처럼 테이블 생성할때 바로 부여해줄 수도 있다.

CONSTRAINT 제약조건 

  • 데이터의 무결성을 지키기 위해 데이터를 입력받을때 실행되는 검사규칙
  • CRAETE문으로 생성할때나 ALTER문으로 필드를 추가할때도 설정 가능
  • NOT NULL : NULL허용하지 않음
  • UNIQUE : 해당 필드는 “서로 다른 값”을 가져야함 / 같은 값을 허용하지 않음
  • PRIMARY KEY : 해당 필드가 NOT NUL과 UNIQUE 제약조건의 특징을 “모두 가진다”
  • FOREIGN KEY : 특정 테이블의 컬럼이 특정 테이블의 컬럼을 참조
  • DEFAULT : 해당 필드의 기본값을 설정
  • 이러한 제약조건들은 DESC 테이블명; 을 통해서 컬럼들에 어떤 제약조건들이 걸려있는지 확인가능
  • 테이블을 생성할때 제약조건이 잘 들어있어야 데이터가 테이블에 실제로 입력이 될때,
    올바른 값인지 판단을 해주기때문에 데이터의 무결성을 보장

▶️실습 - 기존 emp테이블에 값들을 넣어보기

  • foreign key를 다룰때는 null값을 넣거나 테이블에 해당하는 값들로만 넣을 수 있다.

  • ex. deptno → 10, 20, 30, 40 번호만 존재하므로 이 안에서만 지정이 가능하다.
  • empno는 auto_increment로 지정되어있어서 7936으로 자동 증가되어 추가되었다.

데이터베이스 설계

ER Diagram

  • [user_role]은 유저 권한을 의미 유저 한명[user]이 유저 권한[user_role]을 여러개 가질 수 있으므로 '1 : N 관계'이다.
  • 그림에서 보면 user → user_role = '1 대 다' 의 관계
  • 유저 권한[user_role]은 유저[user]와 권한[role] 테이블 둘과 함께 관계를 갖고 있는 데이터
  • 그림에서 유저[user]와 권한[role]은 'N : N (다 대 다)관계'인데 SQL관계에서는 표현이 되지 않으므로
    중간에 테이블을 하나두어 연결한 것이다.

  • 게시글[board]은 유저[user]가 게시글을 여러 개 쓸 수 있기때문에 유저[user]와 게시글[board]은 '1 : N 관계'
  • 여기서 게시글[board]은 user_id를 통해서 어느 유저[user]가 게시글을 작성했는지 접근할 수 있다.

MySQL Workbench - ER Diagram 만들기

▶️user_accounts 테이블 만들기

  • Workbench에서 왼쪽 탭 모델을 눌러서 그리기 가능
  • Database의 이름을 설정하고 (ex. bank), Add Diagram버튼을 클릭하여 bank DB에 대한 Diagram을 그릴 수 있음
  • 첫번째 사각형을 누르고 격자판을 누르면 테이블이 생성되고
  • 두번째 관계선을 클릭 후 A테이블, B테이블을 순차적으로 클릭하면 A, B테이블의 관계선이 만들어짐
  • Column Name : PK, NN 체크 = NN은 NOT NULL을 의미
  • user_name을 설정할때는 동명이인이있을 수 있으므로 UNIQUE를 해제하고, 
    user의 ID나 닉네임을 지정할 경우에는 중복되지 않아야하므로 UNIQUE를 체크
-- 1. user_accounts 테이블 (id, username, email, password, created_at, role_id)
create table user_accounts(
	id int primary key auto_increment,
    user_id varchar(50) not null unique, -- 중복되는 권한이면 안되므로 unique옵션 부여
    user_name varchar(45) not null,
    email varchar(100) not null unique,
    password varchar(200) not null,
    create_at date default current_timestamp,
    role_id int,
    foreign key (role_id) references roles(id)
);
  • foreign key : role_id를 외래키로 지정
  • references : 외래키는 roles테이블의 id를 참조

  • 외래키의 이름을 위 쿼리처럼 명시할 수도 있지만 생략도 가능

 

▶️roles 테이블 만들기

  • roles테이블을 만든 후에는 user_accounts테이블을 더블클릭하여 해당 테이블에 들어가고,
  • 밑의 탭에서 Foreign Keys를 클릭하여 테이블 참조 설정이 가능
  • id를 user_account의 어떤값에 참조하게 할 것이냐는 role_id 체크처럼 “체크”

DCL 데이터 제어어

  • 객체들, 데이터베이스 사용자에게 권한을 설정 및 해제하는 명령어
  • GRANT, REVOKE
-- 연습용 계정에 hr, scott 샘플 DB 권한 부여
grant All privileges on hr.* to 'exam'@'%';
grant All privileges on hr.* to 'exam'@'localhost';

grant All privileges on scott.* to 'exam'@'%';
grant All privileges on scott.* to 'exam'@'localhost';
flush privileges;

DML 데이터 조작어

 

INSERT

▶️실습 - user_accounts 테이블에 샘플데이터 5개 추가

UPDATE

  • UPDATE 구조
update 테이블명 set 업데이트할컬럼명 = "업데이트할값", 업데이트할 컬럼명 = "업데이트할값"
where 업데이트할조건

▶️실습 - 샘플데이터의 role_id값 변경

▶️실습 - UPDATE 명령에서 where절을 생략했을 경우

set autocommit = 0; -- 오토커밋 해제

update a set name = 10; -- where가 없으면 a테이블의 1, 2, 3이 모두 10으로 수정됨 (주의)
select * from a;

update a set name = 20;
select * from a;
rollback; -- 다시 10으로 돌아간것을 확인가능
  • where 절이 생략되면 모든 데이터가 수정된다.

DELETE

-- 삭제
-- 예제. 연관된 데이터가 있는 테이블은 지워지지 않음 (외래키관련)
delete from roles where id = 1;
select * from user_accounts;
  • 단순히 지울 수 없고 먼저 연결된 데이터를 삭제한 후 삭제해야한다.
  • 외래키로 연관되는 데이터들은 A테이블에서 데이터가 삭제되더라도 B테이블에서는
    그 연관된 데이터가 삭제 되지 않음을 의미
  • ex. 외래키가 지정되어있거나하면 지워지지 않음을 확인
    > ALTER를 통해 테이블에 있는 외래키를 DROP으로 지워줄 수 있음
delete from user_accounts;
rollback;
  • 테이블의 모든 정보를 없앨 수도 있다. > rollback으로 다시 되돌린다.
delete from user_accounts where id = 1;
  • = id가 1인 값만 삭제할 것
  • delete할때는 where절을 이용해야 모든 데이터가 삭제되는 위험을 감수할수 있다.

ALTER와 UPDATE의 차이점

  • ALTER 명령어는 데이터베이스에서 테이블의 구조를 변경하는 목적으로 사용
  • UPDATE 명령어는 레코드(행)에 대한 수정을, ALTER는 스키마(테이블, 컬럼 등)에 대한 수정을 진행하는 것이다.

SESSION 세션

  • MySQL에서는 데이터베이스를 나갔다 들어오면 접속이 “새롭게 일어난다”
  • 이 접속을 “세션”이라고 하며, 또 다른 사용자가 프롬프트 등에서
    같은 데이터베이스에 접속했을때는 또 다른 “세션”이 발생

  • 데이터베이스와 접속한 사용자 간의 세션이 연결되어진 것이다. (= 사용자와 데이터베이스의 각각의 접속이 “세션”)
  • ⭐정리하자면 “새로 열린 세션에서는 다른 세션에서 바꾼 값을 모른다”

▶️실습 - time_zone 설정

  • scott데이터베이스를 사용하는 A사용자가 A세션을 가지고 B사용자가 B세션을 가졌다고 가정
SET time_zone = "Asia/Seoul";
  • A사용자가 A세션에서 이렇게 설정하더라도 B세션에서는 time_zone이 바뀌지 않음
  • 이 명령은 현재 있는 세션에서만 time_zone을 바꾸는 것
  • ⭐세션에서 값을 바꾸어도 데이터베이스에 곧장 변경사항을 업데이트하지 않는다.
  • 데이터베이스의 무결성을 유지하기위해 "적용"하는 명령이 있어야 데이터베이스에 반영됨
SET GLOBAL time_zone = "Asia/Seoul";
  • GLOBAL옵션으로 지정하여 root계정에서 처리하면 데이터베이스를 이용하는 모든 세션에 적용가능 

time_zone 변경 전 -> 변경 후

  • 변경 후에 세션을 나갔다가 다시 root로 접속하면 한국시각으로 잘 바뀌는 것을 확인 가능

AUTOCOMMIT 오토커밋

  • 각각의 SQL문을 자동으로 TRANSACTION 처리해줌
  • SQL문이 성공적으로 실행되면 "자동으로 COMMIT"
  • 실행 중 문제 발생 시 "알아서 ROLLBACK"
set autocommit = 1; -- 오토커밋 설정,사용
  • MySQL은 기본적으로 오토커밋을 지원 (기본값 = 1 = 오토커밋 ON)
SELECT @@AUTOCOMMIT; -- 현재 오토커밋 값 확인
set autocommit = 0; -- 오토커밋 해제
  • 오토커밋을 해제하게 되면 A세션에서의 변경값이 B세션에서 적용이 되지 않음
  • 쿼리로 설정하는 방법을 제외하고 다른 방법은
    Workbench에서 Preferences → SQL Editor → SQL Execution →
    New connections use auto commit mode를 체크 설정/해제 가 가능하다.

TCL 트랜잭션 제어어

  • 데이터가 직접적으로 바뀌는 것 (= 데이터 자체가 변경되는 것)
  • 데이터 조작어(DML)의 명령어 실행, 취소, 임시저장 시 사용
  • COMMIT : 지금까지의 변경사항을 저장
  • ROLLBACK : 취소 명령
  • SAVEPOINT : 취소로 돌아갈 포인트를 지정

Transaction 트랜잭션

  • 데이터베이스에서 하나의 작업으로 처리되는 논리적 작업단위이자,나눌 수 없는 하나의 업무 단위
  • 여러 SQL문들을 "단일 작업"으로 묶어서 나눠질 수 없게 만든 것
    ex. 입금/출금 로직은 각 사용자에게 UPDATE문이 2개 작성되어야한다.
    1. A 출금 TO B : UPDATE 잔액차감
    2. B 입금 FROM A : UPDATE 잔액증가
    이 두 로직은 단일로 수행되면 안되고, 두 작업이 모두 성공되어야 성공
  • 👀트랜잭션의 중요한 속성으로 A C I D가 있다.

트랜잭션 과정

1. 시작

  • 다른 DBMS와는 달리 명시적인 시작 명령이 없다.
  • 일반적으로 첫 DML시작 시 트랜잭션이 시작됨
START TRANSACTION;
  • 이 명령으로 인해 트랜잭션 실행과 동시에 "오토커밋"은 해제됨
  • 따라서 COMMIT 명령을 수행해야만 SQL문이 적용된다.
  • COMMIT/ROLLBACK 명령으로 트랜잭션이 종료되었을때에 원래의 "오토커밋"상태로 돌아감
setAutoCommit(false);
  • 자바 JDBC에서는 이 [START TRANSACTION] 명령이 "오토커밋을 해제하는 메소드"와 같은 기능을 함

2. 종료

  • 명시적 종료 : COMMIT / ROLLBACK
  • 묵시적 종료 : DDL, DCL등이 수행될때 (AutoCommit), 시스템 오류 (AutoRollback)

  • 트랜잭션은 DML의 집합이다.
    INSERT : 값을 새롭게 추가
    UPDATE : 값을 변경
    DELETE : 값을 삭제
    이 3개가 DML이라고 할 수 있다.

  • DDL이나 DCL은 한 문장이 트랜잭션으로 처리됨

▶️실습 - TCL 사용 : COMMIT, ROLLBACK

  1. INSERT 수행을 할때 자동으로 “트랜잭션을 시작”한다.
  2. 데이터의 추가 및 수정을 끝냈으면 COMMIT

COMMIT

commit;
  • 커밋을 하게되면 "지금까지 작업한 내용을 DB에 영구적으로 저장"
  • TRANSACTION을 종료하는 기능도 포함 (명시적 종료)
  • 오토커밋을 설정이 해제되어있을때는 commit을 명시해주어 커밋 가능
  • 이렇게 되면 다른 세션에서도 데이터베이스에 적용된 변경 값으로 보이게 된다.

ROLLBACK

  • 변경된 사항들이 있을때 지금까지 했던 변경사항들을 “실행취소”
  • 트랜잭션의 "이전 상태"로 되돌림
  • TRANSACTION을 종료하는 기능도 포함 (명시적 종료)
  • 오토커밋이 해제된 상태에서 수행한 SQL문들을 ROLLBACK을 통해 이전 상태로 되돌릴 수 있음
insert into roles(name) values(”test4”);
insert into roles(name) values(”test5”);
commit; // 데이터 추가 사항 업데이트

insert into roles(name) values(”test6”);
insert into roles(name) values(”test7”);
rollback; // 실행취소
  • 끝까지 잘 수행되었으면 commit, 중간에 변경사항이나 잘못추가한 사항이 있으면 rollback

💡DML, DDL, TCL을 실습을 통해 배울 수 있었다. 배우면서 INSERT, COMMIT, UPDATE, ALTER 등을 한번에 배우다보니 회고시간에 각각의 범주에 맞게 정리해야할 필요성을 많이 느꼈다.

정리하면서 INSERT, COMMIT, UPDATE, DELETE 등의 명령어가 각각 어디에 속하는지 등을 알 수 있었다.

개념을 많이 익히게 되면서 해야하는 관련 실습이 많아졌는데 자바 실습을 먼저 마무리하고 SQL 실습도 진행해야겠다!🚀

 

저작자표시 (새창열림)