유지연

유지연

I’m a junior web developer.
Let’s code with me!

MySQL03 Data Definition Language 2

업데이트:
9 분 소요

비트캠프 서초본원 엄진영 강사님의 수업을 듣고 정리했습니다.

DDL(Data Definition Language)

DB 객체(테이블, 뷰, 함수, 트리거 등)를 생성, 변경, 삭제하는 SQL 명령

  • 데이터베이스(database) = 스키마(schema)
  • 테이블(table)
  • 뷰(view)
  • 트리거(trigger)
    • 특정 조건에서 자동으로 호출되는 함수
    • 특정 조건? SQL 실행 전/후 등
    • OOP 디자인 패턴에서 옵저버에 해당한다.
  • 함수(function)
  • 프로시저(procedure)
  • 인덱스(index)

5. 키 컬럼 지정

테이블 :

  • no, name, email, id, pwd, jumin, tel, postno, basic_addr, gender

Key vs Candidate Key

  • key
    • 데이터를 구분할 때 사용하는 컬럼들의 집합
    • 예) {email}, {jumin}, {id}, {name, tel}, {name, tel, basic_addr, gender}, {name, jumin}, {email, id}, {id, name, email} …
  • candidate key (후보키 = 최소키)
    • key들 중에서 최소 항목으로 줄인 키
    • 예) {jumin}, {email}, {id}, {name, tel}

Alternate Key vs Primary Key

  • primary key (주 키)
    • candidate key 중에서 DBMS 관리자가 사용하기로 결정한 key
    • 예) DBMS 관리자가 id 컬럼의 값을 데이터를 구분하는 키로 사용하기로 결정했다면, 주 키는 {id}가 된다.
    • 주 키로 선택되지 않은 모든 candidate key는 alternate key가 된다.
  • alternate key (대안 키)
    • 비록 primary key는 아니지만, primary key처럼 데이터를 구분하는 용도로 대신 사용할 수 있다고 해서 대안 키(alternate key)라 부른다.

Artificial Key (인공 키)

  • Primary key로 사용하기에 적절한 컬럼을 찾을 수 없다면, 이런 경우 key로 사용할 컬럼을 추가한다.
    • 예) 게시글 : 제목, 내용, 작성자, 등록일, 조회수
  • 보통 일련번호를 저장할 정수 타입의 컬럼을 추가한다.
    • 예) 게시글 : 게시글 번호
  • 대부분의 SNS 서비스들은 일련의 번호를 primary key로 사용한다.
    • 왜?
      • 회원 탈퇴
        • 회원 탈퇴할 때 아이디도 제거한다.
        • 아이디를 지우면 그 아이디와 연결된 게시글을 지워야 한다.
        • 그런데 회원 아이디 대신 일련 번호를 사용하면, 그 회원이 쓴 게시글은 일련 번호와 묶인다.
        • 따라서 아이디가 삭제되더라도 해당 글은 계속 유효하게 처리할 수 있다.
      • 이메일 변경
        • 게시글을 저장할 때 회원 이메일을 저장한다고 가정하자.
        • primary key 값은 다른 데이터에서 사용하기 때문에, PK 값을 변경하면 그 값을 사용한 모든 데이터에 영향을 끼친다.
        • 그래서 PK 값을 다른 데이터에서 사용한 경우, DBMS는 PK 값을 변경하지 못하도록 통제한다.
        • 이렇게 값이 변경될 수 있는 경우에는 PK로 사용하지 말라.
        • 대신 회원 번호와 같은 임의의 key(인공 키)를 만들어 사용하는 것이 좋다.

Primary Key

  • 테이블의 데이터를 구분할 때 사용하는 컬럼들이다.
  • 줄여서 PK라고 표시한다.
  • PK 컬럼을 지정하지 않으면 데이터가 중복될 수 있다.

****

  • 테이블 생성
create table test1(
  name varchar(20),
  kor int,
  eng int,
  math int
);
  • 데이터 입력 테스트
insert into test1(name,kor,eng,math) values('aaa', 100, 100, 100);
insert into test1(name,kor,eng,math) values('bbb', 90, 90, 90);
insert into test1(name,kor,eng,math) values('aaa', 100, 100, 100); /* 중복 허용*/

****

컬럼명 타입 primary key
  • 테이블 생성
create table test1(
  name varchar(20) primary key,
  kor int,
  eng int,
  math int
);
  • 데이터 입력 테스트
insert into test1(name,kor,eng,math) values('aaa', 100, 100, 100);
insert into test1(name,kor,eng,math) values('bbb', 90, 90, 90);
insert into test1(name,kor,eng,math) values('aaa', 100, 100, 100); /* 중복 오류! */
insert into test1(kor,eng,math) values(100, 100, 100); /* PK는 기본이 not null 이다. */

**<한 개="" 이상의="" 컬럼을="" PK로="" 지정하기="">**

  • 두 개 이상의 컬럼을 묶어서 PK로 선언하고 싶다면 각 컬럼에 대해서 개별적으로 PK를 지정해서는 안된다.
  • 여러 개의 컬럼을 묶어서 PK로 지정하려면 별도의 문법을 사용해야 한다.
    • constraint 제약조건이름 primary key (컬럼명, 컬럼명, …)
  • 테이블 생성
create table test1(
  name varchar(20) primary key,
  age int primary key,
  kor int,
  eng int,
  math int
);
/* ERROR 1068 (42000): Multiple primary key defined */
  • 테이블 생성
create table test1(
  name varchar(20),
  age int,
  kor int,
  eng int,
  math int,
  constraint test1_pk primary key(name, age)
);
  • 데이터 입력 테스트
insert into test1(name, age, kor, eng, math) values('aa', 10, 100, 100, 100);
insert into test1(name, age, kor, eng, math) values('bb', 20, 90, 90, 90);
insert into test1(name, age, kor, eng, math) values('aa', 11, 88, 88, 88);
insert into test1(name, age, kor, eng, math) values('ab', 10, 88, 88, 88);

/* 이름과 나이가 같으면 중복되기 때문에 입력 거절이다. */
insert into test1(name, age, kor, eng, math) values('aa', 10, 88, 88, 88);
/* ERROR 1062 (23000): Duplicate entry 'aa-10' for key 'PRIMARY' */

**<임의의 값을="" PK로="" 만들기="">**

  • 여러 개의 컬럼을 묶어서 PK로 사용하면 데이터를 다루기가 불편하다.

    즉 데이터를 찾을 때 마다 name과 age 값을 지정해야 한다.

  • 그래서 실무에서는 이런 경우 ‘학번’처럼 임의의 값을 저장하는 컬럼을 만들어 PK로 사용한다.

  • 테이블 생성

create table test1(
  no int primary key, /* 학번 */
  name varchar(20),
  age int,
  kor int,
  eng int,
  math int
 );
  • 데이터 입력 테스트
insert into test1(no,name,age,kor,eng,math) values(1,'a',10,90,90,90);
insert into test1(no,name,age,kor,eng,math) values(2,'a',11,91,91,91);
insert into test1(no,name,age,kor,eng,math) values(3,'b',11,81,81,81);
insert into test1(no,name,age,kor,eng,math) values(4,'c',20,81,81,81);

/* 번호가 중복되었기 때문에 입력 거절 */
insert into test1(no,name,age,kor,eng,math) values(4,'d',21,81,81,81);
/* ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY' */

/* 번호는 중복되지 않았지만, name과 age값이 중복되는 경우를 막을 수 없다*/
insert into test1(no,name,age,kor,eng,math) values(5,'c',20,81,81,81);
  • 위와 같은 경우를 대비해 준비된 문법이 unique이다.
  • PK는 아니지만 PK처럼 중복을 허락하지 않는 컬럼을 지정할 때 사용한다.
  • 그래서 PK를 대신해서 사용할 수 있는 key라고 해서 “대안키(alternate key)“라고 부른다.

unique = alternate key(대안키)

  • 테이블 생성
create table test1(
  no int primary key,
  name varchar(20),
  age int,
  kor int,
  eng int,
  math int,
  constraint test1_uk unique (name, age)
 );
  • 다음과 같이 제약 조건을 모든 컬럼 선언 뒤에 놓을 수 있다.
create table test1(
  no int,
  name varchar(20),
  age int,
  kor int,
  eng int,
  math int,
  constraint test1_pk primary key(no),
  constraint test1_uk unique (name, age)
 );
  • 데이터 입력 테스트
insert into test1(no,name,age,kor,eng,math) values(1,'a',10,90,90,90);
insert into test1(no,name,age,kor,eng,math) values(2,'a',11,91,91,91);
insert into test1(no,name,age,kor,eng,math) values(3,'b',11,81,81,81);
insert into test1(no,name,age,kor,eng,math) values(4,'c',20,81,81,81);

/* 번호가 중복되었기 때문에 입력 거절 */
insert into test1(no,name,age,kor,eng,math) values(4,'d',21,81,81,81);

/* 비록 번호가 중복되지 않더라도 name, age가 unique 컬럼으로 지정되었기 
   때문에 중복저장될 수 없다.*/
insert into test1(no,name,age,kor,eng,math) values(5,'c',20,81,81,81);

/* name이 중복되더라도 age가 다르면 저장할 수 있다. */
insert into test1(no, name, age, kor, eng, math) values(6, 'c',50,81,81,81);

6. index

  • 검색 조건으로 사용되는 컬럼인 경우 따로 정렬해두면 데이터를 찾을 때 빨리 찾을 수 있다.
  • 특정 컬럼의 값을 A-Z 또는 Z-A로 정렬시키는 문법이 인덱스이다.
  • DBMS는 해당 컬럼의 값으로 정렬한 데이터 정보를 별도의 파일로 생성한다.
  • 보통 책 맨 뒤에 붙어있는 색인표와 같다.
  • 인덱스로 지정된 컬럼의 값이 추가/변경/삭제 될 때 인덱스 정보도 갱신한다.
  • 따라서 입력/변경/삭제가 자주 발생하는 테이블에 대해 인덱스 컬럼을 지정하면, 입력/변경/삭제 시 인덱스 정보를 갱신해야 하기 때문에 입력/변경/삭제 속도가 느려지는 문제가 있다.
  • 대신 조회 속도는 빠르다.

인덱스 생성

  • 새 테이블에 인덱스 생성
create table 테이블명 ( 
  ... 
  index 인덱스명 (컬럼명)
); 

create table 테이블명 ( 
  ... 
); 
create index 인덱스명 on 테이블명(컬럼명1, 컬럼명2);
  • 기존 테이블에 인덱스 추가
alter table 테이블명 add index (컬럼명1, 컬럼명2);

인덱스 조회

show index from 테이블명;

인덱스 삭제

drop index 인덱스명 on 테이블명;
  • 테이블 생성
create table test1(
  no int primary key,
  name varchar(20),
  age int,
  kor int,
  eng int,
  math int,
  constraint test1_uk unique (name, age),
  fulltext index test1_name_idx (name)
);
  • 데이터 입력 테스트
insert into test1(no,name,age,kor,eng,math) values(1,'aaa',20,80,80,80);
insert into test1(no,name,age,kor,eng,math) values(2,'bbb',21,90,80,80);
insert into test1(no,name,age,kor,eng,math) values(3,'ccc',20,80,80,80);
insert into test1(no,name,age,kor,eng,math) values(4,'ddd',22,90,80,80);
insert into test1(no,name,age,kor,eng,math) values(5,'eee',20,80,80,80);
  • name 컬럼은 인덱스 컬럼으로 지정되었기 때문에 DBMS는 데이터가 추가되거나 삭제되거나 name 컬럼 값이 변경될 때마다 색인표를 갱신한다.
  • 단점, 이런 이유로 이름으로 검색할 때 찾기 속도는 빠르지만, 입력,변경,삭제 속도는 느리게 된다.

fulltext index

  • fulltext index에 대해서는 조금 더 알아본 후 내용 보충하기!!!!

인덱스 컬럼의 활용

  • 검색할 때 사용한다.
select * from test1 where name = 'bbb';

7. 테이블 변경

기존에 있는 테이블을 변경할 수 있다.

  • 테이블 생성
create table test1 (
  name varchar(3),
  kor int,
  eng int,
  math int,
  sum int,
  aver int
);

테이블에 컬럼 추가

alter table test1
  add column no int;

alter table test1
  add column age int;

alter table test1
  add column no2 int,
  add column age2 int;

테이블에서 컬럼 삭제

alter table test1
  drop column no;

alter table test1
  drop column age;

alter table test1
  drop column no2,
  drop column age2;

Primary Key 컬럼 지정

alter table test1
  add constraint test1_pk primary key (no);

Unique 컬럼 지정

alter table test1
  add constraint test1_uk unique (name, age);

Index 컬럼 지정

alter table test1
  add fulltext index test1_name_idx (name);
  • primary key, unique, index 컬럼을 한 번에 지정할 수도 있다.
alter table test1
  add constraint test1_uk unique (name, age),
  add fulltext index test1_name_idx (name);

컬럼에 옵션 추가

alter table test1
  modify column name varchar(20) not null,
  modify column age int not null,
  modify column kor int not null,
  modify column eng int not null,
  modify column math int not null,
  modify column sum int not null,
  modify column aver float not null;
  • 데이터 입력 테스트
insert into test1(no,name,age,kor,eng,math,sum,aver)
  values(1,'aaa',20,100,100,100,300,100);
  
insert into test1(no,name,age,kor,eng,math,sum,aver)
  values(2,'bbb',21,100,100,100,300,100);

/* 다음은 name과 age의 값이 중복되기 때문에 입력 거절된다.*/  
insert into test1(no,name,age,kor,eng,math,sum,aver)
  values(3,'bbb',21,100,100,100,300,100);
/* ERROR 1062 (23000): Duplicate entry 'bbb-21' for key 'test1_uk' */

8. 컬럼 값 자동 증가(auto_increment)

  • 숫자 타입의 PK 컬럼인 경우 값을 1씩 자동 증가시킬 수 있다.
  • 즉 데이터를 입력할 때 해당 컬럼의 값을 넣지 않아도 자동으로 증가된다.
  • 단 삭제를 통해 중간에 비어있는 번호는 다시 채우지 않는다. 즉 증가된 번호는 계속 앞으로 증가할 뿐이다.
    • 예) 게시판의 게시글 번호 - 중간에 글을 삭제해도 번호가 재조정되지 않는다.
  • 테이블 생성
create table test1(
  no int not null,
  name varchar(20) not null
);
  • 특정 컬럼의 값을 자동으로 증가하게 선언한다.
  • 단 반드시 primary key 나 unique여야 한다.
/* 아직 no가 pk가 아니기 때문에 오류*/
alter table test1
  modify column no int not null auto_increment; 

/* 일단 no를 pk로 지정한다.*/  
alter table test1
  add constraint primary key (no); 

/* no를 unique로 지정해도 된다.*/
alter table test1
  add constraint unique (no); 

/* 그런 후 auto_increment를 지정한다.*/
alter table test1
  modify column no int not null auto_increment;

  • Oracle은 auto_increment라는 키워드가 없다.

  • 데이터 입력 테스트

/* auto-increment 컬럼의 값을 직접 지정할 수 있다.*/
insert into test1(no, name) values(1, 'xxx');

/* auto-increment 컬럼의 값을 생략하면 마지막 값을 증가시켜서 입력한다.*/
insert into test1(name) values('aaa');

c
insert into test1(name) values('bbb'); /* no는 101이 입력된다.*/

insert into test1(name) values('ccc'); /* no=102 */
insert into test1(name) values('ddd'); /* no=103 */

/* 값을 삭제하더라도 auto-increment는 계속 앞으로 증가한다.*/
delete from test1 where no=103;

insert into test1(name) values('eee'); /* no=104 */

insert into test1(name) values('123456789012345678901234');

/* 다른 DBMS의 경우 입력 오류가 발생하더라도 번호는 자동 증가하기 때문에 
 * 다음 값을 입력할 때는 증가된 값이 들어간다.
 * 그러나 MySQL(MariaDB)는 증가되지 않는다.
 */
insert into test1(name) values('fff'); /* no=? */

9. 뷰(view)

  • 조회 결과를 테이블처럼 사용하는 문법

  • select 문장이 복잡할 때 뷰로 정의 해놓고 사용하면 편리하다.

  • 테이블 생성
create table test1 (
  no int primary key auto_increment,
  name varchar(20) not null,
  class varchar(10) not null,
  working char(1) not null,
  tel varchar(20)
);
  • 데이터 입력
insert into test1(name,class,working) values('aaa','java100','Y');
insert into test1(name,class,working) values('bbb','java100','N');
insert into test1(name,class,working) values('ccc','java100','Y');
insert into test1(name,class,working) values('ddd','java100','N');
insert into test1(name,class,working) values('eee','java100','Y');
insert into test1(name,class,working) values('kkk','java101','N');
insert into test1(name,class,working) values('lll','java101','Y');
insert into test1(name,class,working) values('mmm','java101','N');
insert into test1(name,class,working) values('nnn','java101','Y');
insert into test1(name,class,working) values('ooo','java101','N');
  • 직장인만 조회 (working = ‘Y’)
select no, name, class from test1 where working = 'Y';

뷰 생성

  • 직장인만 조회한 결과를 가상 테이블로 만들기
    • show tables;를 하면 하면 worker 가 조회된다.
create view worker
  as select no, name, class from test1 where working = 'Y';
  • java100만 조회
select no, name, working from test1 where class = 'java100';
  • java100만 조회한 결과를 가상 테이블로 만들기
create view java100
	as select no, name, working from test1 where class = 'java100';

  • view가 참조하는 테이블에 데이터를 입력한 후 view를 조회하면?

    → 새로 추가된 컬럼이 함께 조회된다.

  • 뷰를 조회할 때 마다 매번 select 문장을 실행한다.

    → 미리 결과를 만들어 놓는 것이 아니다. 일종의 조회 함수 역할을 한다.

  • 목적은 복잡한 조회를 가상의 테이블로 표현할 수 있어 SQL문이 간결해진다.

insert into test1(name,class,working) values('ppp','java101','Y');
select * from worker;

뷰 삭제

drop view worker;

태그:

카테고리:

업데이트:

참고

2022-02-22 TIL(at work)

업데이트:
5 분 소요

업무 배경 이 업무가 할당된 이유 회사에서 맡고 있는 서비스 중, 회원이 구매를 신청하면 정부24에서 그 컨텐츠를 구매하여 제공을 하는 서비스가 있다. (참고로, 구매를 한다고 바로 완료되는게 아니라 구매 로직을 태운 후 실제로 컨텐츠를 가져오기까지 어느정도 시간이 소요된다....