MySQL(8) - 트랜잭션, TCL, DDL, DCL, 새로운 스키마 예제

<목차>

1) 트랜잭션

   1-1) 개념

2) TCL

   2-1) 개념

3) DDL

   3-1) 개념

   3-2) 예제

4) DCL

   4-1) 개념

   4-2) 예제

5) 새로운 스키마(데이터베이스) 예제

 

 

 

 

 

1) 트랜잭션

1-1) 개념

 

/*
	TCL
	- commit, rollback
	
	트랜잭션(Transaction)
	- 데이터베이스의 상태를 바꾸는 일종의 "작업 단위"
	
	트랜잭션 특징
	- 트랜잭션이 데이터베이스에 모두 반영되던가,
	  아니면 전혀 반영되지 않아야 한다.
	- 트랜잭션의 작업 처리 결과가 항상 일관성이 있어야 한다.
	- 어떤 하나의 트랜잭션이라도, 다른 트랜잭션의 연산에 끼어들 수 없다.
	- 트랜잭션이 성공적으로 완료되었을 경우, 결과는 영구적 반영
*/

 

 

 

2) TCL : COMMIT, ROLLBACK

2-1) 개념

 

 

 

3) DDL : CREATE, ALTER, DROP

3-1) 개념

 

 

 

 

 

3-2) 예제

/*
	DDL
	- CREATE
*/

-- 현재 접속한 사용자 확인
select user();

-- user 생성
create user 'scit45'@'localhost' identified by '1234';

-- 특정 ip만 접근을 허용하는 사용자 생성
create user 'scit45'@'127.0.0.1' identified by '1234';
-- 외부 접근을 허용하는 사용자 생성, '%' : 원격접속 허용
create user 'scit45'@'%' identified by '1234';
-- 내부 접근을 허용하는 사용자 생성
create user 'scit45'@'localhost' identified by '1234';

-- 커맨드창(명령 프롬프트)에서 ipconfig > ip주소 확인
-- 커맨드창(명령 프롬프트)에서 netstat -a > 사용 중인 port 번호 확인

 

-- 사용자 계정 수정
-- 비밀번호 변경
-- alter user '계정ID'@'localhost' identified by '변경할 비밀번호';
alter user 'scit45'@'localhost' identified by '1234';

-- 사용자 계정 삭제
-- drop user '계정ID'@'localhost';
drop user 'scit45'@'localhost';

 

 

 

4) DCL

4-1) 개념

 

 

4-2) 예제

/*
	DCL
	- GRANT, REVOKE
*/

-- 권한 부여
grant all privileges on *.* to 'scit45'@'localhost';
-- 권한 즉시 부여
flush privileges;
-- 권한 회수
revoke all on *.* from 'scit45'@'localhost';

 

 

 

5) 새로운 스키마(데이터베이스) 예제

-- user 확인
select user();

/*
	MySQL 구조
	- 테이블 : 실제 데이터가 저장되는 부분
	- 데이터베이스(Database) : 스키마(schema)라고 표현하며,
	  서로 연관된 테이블들을 그룹핑한 일종의 폴더
	- 데이터베이스 서버(Database Server) : 여러 스키마들이 저장된 곳
*/

-- 데이터베이스 삭제
drop database if exists test;
-- 데이터베이스 생성
create database test;
-- 데이터베이스 확인
show databases;
-- 데이터베이스 사용
use hr;
select * from employees;

use test;

 

 

-- [문제] members 테이블을 생성한 후,
-- insert, update, delete 연습 쿼리 작성
/*
	-- 컬럼명        -- 데이터 타입        -- 제약조건
	seqno          정수                pk(primary key)
	usrid          가변문자열 30자리       unique, not null
	usrname        가변문자열 30자리       not null
	birth          날짜
	joindate       날짜                default 현재날짜
	gender         고정길이 1자리         check F, M
*/

 

create table members (
    -- 컬럼명         -- 데이터 타입        -- 제약조건
	seqno           int               primary key,
	usrid           varchar(30)       unique not null,
	usrname         varchar(30)       not null,
	birth           date,
	joindate        date              default(current_date),
	gender          char(1)           default 'M' check(gender in ('F', 'M'))
);

drop table members;
select * from members;
desc members;   -- desc : 테이블 구조를 파악할 수 있는 명령어

 

 

 

 

insert into members
values(
	1, 'user1', '홍길동', '91-12-27', '24-05-23', 'M'
);

insert into members(
	seqno, usrid, usrname, birth)
values(
	2, 'user2', '이순신', '1995-05-12'
);

insert into members
values(
	3, 'user3', '유관순', '1999-10-10', current_date(), 'F'
);

 

 

 

update members
set usrname = '성춘향', birth = '1850-12-18'
where usrid = 'user3';

 

 

 

delete from members where seqno = '1';

select * from members;

 

 

 

/*
	DDL
	- alter, drop
*/

 

-- 테이블에 컬럼 추가
-- alter table [테이블명]
-- add column [추가할 컬럼명] [데이터 타입] [제약조건]
alter table members add column test1 varchar(20);
alter table members add column test2 varchar(20) not null;
desc members;

 

 

 

-- 컬럼명 변경
-- alter table [테이블명]
-- change column [컬럼명] [변경할 컬럼명] [데이터 타입] [제약조건]
alter table members change column test1 test1_change int;

 

 

 

-- 컬럼 삭제
-- drop table [테이블명] drop column [컬럼명]
alter table members drop column test2;
desc members;

 

 

 

-- 데이터 타입 변경 및 제약조건 추가
-- alter table [테이블명]
-- modify [컬럼명] [변경할 데이터 타입] [제약조건]
alter table members modify test1_change int unique;

 

 

 

-- 테이블 삭제
drop table members;

 

 

/*
	뷰(VIEW)
	- MySQL에서 뷰(VIEW)는 하나 이상의 테이블에서 선택한
	  열과 행을 기반으로 생성된 가상 테이블
	- 뷰는 실제 테이블처럼 행과 열을 가지고 있지만, 실제로
	  데이터를 저장하는 것이 아니라 본래 데이터베이스 객체로
	  등록할 수 없는 SELECT 명령을 객체로서 이름을 붙여
	  관리할 수 있도록 한 것
*/

 

create view v_testtbl as
	select * from members;
select * from v_testtbl;

 

 

 

insert into v_testtbl values(
	4, 'user4', '뷰', current_date(), '00-10-25', 'M', 0
);

 

 

 

update v_testtbl set usrname = '뷰 수정' where seqno = 4;