Contents

DDL 기초 / TCL / 사용자 관리

   Apr 11, 2023     7 min read

DDL

  • Data Definition Language
  • 데이터를 관리하는 컨테이너를 생성, 수정, 삭제하는 등의 데이터의 전체의 골격을 결정하는 역할을 한다.

1. CREATE

  • 데이터베이스 생성
create database testdb; -- testdb 데이터베이스를 생성한다.

use testdb;

create table t_user (
       c_ID int primary key auto_increment comment '유저 고유 아이디',
       c_name char(20) not null comment '유저 이름'
) comment '유저 테이블';



  • 데이블 코멘트 보기
SELECT  
    table_name, table_comment
FROM
    information_schema.tables
WHERE
    table_schema = 'testdb' AND table_name = 't_user';



Untitled

  • 칼럼 코멘트 보기
SELECT
    table_name, column_name, column_comment
FROM
    information_schema.columns
WHERE
    table_schema = 'testdb' AND table_name = 't_user';



Untitled (2)

  • select into (테이블 복사)
create table t1 (id int, name varchar(30));
insert into t1 values(1, '홍길동');
insert into t1 values(2, '서길동');
insert into t1 values(3, '남길동');
insert into t1 values(4, '북길동');

create table t2 as (select * from t1);
select * from t2;



Untitled (1)

  • MySQL은 지원하지 않음
    SELECT * INTO B FROM A;
    위 구문을 실행하기 전까지는 B 란 테이블은 존재하지 않지만 실행을 하게 되면 A라는 테이블의 칼럼과 데이터를 가지는 B 라는 테이블이 생성된다.

뷰(view) 생성

  • Table -> VIEW -> VIEW …

create view v1
as
select * from t_user;

create view v2
as
select c_name from t_user;

create view v3
as
select c_ID from t_user where c_ID > 3;

select * from v1;
select * from v2;
select * from v3;



2. DROP

  • 완전히 지우는 것

drop view v1;
drop view v2;
drop view v3;

drop table t_user;

drop database test_db;



3. ALTER

  • 정의를 변경할 때

create database test_db;

use test_db;

create table t_user (
        c_ID int primary key auto_increment comment '유저 고유 아이디',
        c_name char(20) not null comment '유저 이름'
) comment '유저 테이블';



  • 테이블 컬럼 추가
alter table t_user
  add column test_column int not null;

desc t_user;



  • 열이름 및 데이터 형식 변경
alter table t_user
 change column c_name n_name varchar(30) null;

desc t_user;



  • 테이블 컬럼 삭제
alter table t_user
 drop column test_column;

desc t_user;



4. TRUNCATE

  • 테이블의 모든 데이터를 삭제할 때 사용

truncate t1;

delete from t1;



  • DELETE와 차이점
    1. TRUNCATE는 테이블을 DROP했다가 다시 생성함. 그래서 DELETE의 경우 한 줄씩 삭제하기 때문에 느리지만 TRUNCATE는 매우 빠름
    2. 자동 COMMIT을 수행하기 때문에 롤백이 불가능함.
    3. DELETE는 조건을 걸어서 원하는 데이터를 삭제할 수 있지만 TRUNCATE는 모든 데이터를 삭제한다.
    4. TRUNCATE는 DELETE와 달리 디스크 상에 할당된 공간도 모두 제거함. 그냥 완벽한 초기화.

5. RENAME

create table t1(id int);
create table t2(id int);
create table t3(id int);

rename table t1 to t11;

rename table t2 to t22, t3 to t33;



6. 임시 테이블

  • 임시테이블은 현재 접속한 세션내에서만 존재할 수 있는 테이블.
    세션이 닫히면 자동으로 삭제 됨.

create database db01;
use db01;

create temporary table if not exists t1(id int, name varchar(30));  -- 임시 테이블 생성

describe t1;

insert into t1 values(1, 'This');
select * from t1;

drop table t1;



TCL

  • Transaction Control Language
  • DCL(Data Control Language)의 Commit과 Rollback을 따로 분리하여 TCL이라고 함
  • 하나의 작업 단위 : 전부 성공 또는 전부 취소
  • ex. A은행 -> B은행 : 10만원 이체
  • REDO 로그를 사용해서 과거의 데이터를 최신 데이터 쪽으로 흐르게 하는 것을 롤 포워드(roll-forward)라고 함

  • 반대로 UNDO 정보를 사용해서 변경을 취소(과거의 상태로 되돌린다)하는 것을 롤백(rollback)이라고 함

  • 트랜잭션의 특징(ACID)
- 원자성(Atomicity) : The entire transation takes place at once or doesn't happen at all.                  
- 일관성(Consistency) : The database must be consistent before and after the transation.
- 독립성(Isolation) : Mutiple Transation occur independently without interference.
- 영구성(Durability) : The changes of a successful transation occurs even if the system failure occurs.



  • 처음 MySQL을 설치하면 AUTOCOMMIT이 1로 설정되어 있어서 ROLLBACK이 안 된다.
  • 그래서 AUTOCOMMIT을 0으로 설정해줘야 실행한 쿼리를 ROLLBACK하여 원상복구가 가능하다.

Ex. 연습용 테이블 생성

create table t1(id int, name varchar(30));

insert into t1 values(1, '홍길동');
insert into t1 values(2, '서길동');
insert into t1 values(3, '남길동');
insert into t1 values(4, '북길동');
select * from t1;



1. 롤백이 가능하도록 autocommit을 0으로 설정

SET autocommit = 0;
SET SQL_SAFE_UPDATES=0;



2. ROLLBACK & COMMIT 연습

  • ROLLBACK
    데이터베이스의 상태를 트랜잭션 수행하기 이전 상태로 되돌릴 수 있다. 단, 트랜잭션이 COmmit 되지 않은 상태여야 한다.

  • COMMIT
    트랜잭션의 결과를 확정시킨다. 이 명령을 실행하고 나서는 로백으로 이전 시점으로 복구할 수 없다.

start transaction;
delete from t1 where id = 1;
delete from t1 where id = 2;
delete from t1 where id = 3;
select * from t1;

rollback;   -- rollback 성공
select * from t1;




delete from t1 where id = 1;
delete from t1 where id = 2;
delete from t1 where id = 3;
commit;

rollback;   -- rollback 실패
select * from t1;



03. SAVEPOINT

  • 트랜잭션 중에 포인트를 지정하고 해당 상태로 되돌아 갈 수 있는 기능을 제공

drop table t1
create table t1(id int, name varchar(30));

insert into t1 values(1, '홍길동');
insert into t1 values(2, '서길동');
insert into t1 values(3, '남길동');
insert into t1 values(4, '북길동');
select * from t1;
commit;  --- 실행 여러번 하기

start transaction;

savepoint a;
delete from t1 where id = 1;
select * from t1;

savepoint b;
delete from t1 where id = 2;
select * from t1;

rollback to b;
select * from t1;

rollback to a;
select * from t1;

commit;  -- 실행 여러번 하기



사용자 관리

  • 데이터를 보호하고 관리하기 위한 목적으로 사용되며 무결성, 보안 및 권한 제어, 복구 등을 하기 위한 언어.

1. GRANT

  • 데이터베이스 사용자에게 권한을 부여함

1) 권한 부여

GRANT [권한] ON [DB].[TABLE] TO [유저_ID]@[호스트];



2) 권한 확인

SHOW GRANTS FOR [유저_ID]@[호스트];  -- 다른 사용자 권한 확인
SHOW GRANTS;  -- 내게 부여된 권한 확인
SHOW GRANTS FOR current_user;  -- 내게 부여된 권한 확인
SHOW GRANTS FOR current_user();  -- 내게 부여된 권한 확인



2. REVOKE

  • 데이터베이스 사용자에게 권한을 취소함
REVOKE [권한] ON [DB].[TABLE] FROM [유저_ID]@[호스트]



Ex.

  • 데이터베이스, 테이블, 사용자 정보 확인

SQL> show databases;

SQL> use mysql;
SQL> show tables;
SQL> describe user;
SQL> explain user;
SQL> select * from user;



  • 관리자 이름 변경
SQL> use mysql;
SQL> update user set user='admin' where user='root';
SQL> flush privileges;
SQL> exit
> mysql -u admin -p



  • 사용자 계정 생성 및 권한 주기
SQL> create user user1@'192.168.1.134' identified by '1234';
SQL> create user user11@'192.168.1.%' identified by '1234';
SQL> create user user3@'%' identified by '1234';
SQL> create user user4@'localhost' identifiedy by '1234';

SQL> grant all privileges on *.* to user1@'192.168.1.134';
SQL> grant all privileges on DB01.* to user11@'192.168.1.%';
SQL> grant all privileges on DB01.t1 to user3@'%';
SQL> grant select,insert on *.* to user4@'localhost';



Ex. 문제

  • HostPC에서 > mysql -h 192.168.1.x -u user7 -p
    > t1 테이블 생성 -> 데이터 입력 -> select 까지