[데이터베이스] MySQL 기초 문법 정리(데이터베이스 객체, 테이블 생성/삭제 변경)

데이터베이스를 다루는 오픈소스인 MySQL에 대해 알아보고공부한 내용을 정리

 

목차

 

---

데이터베이스(Database)

---

1. 데이터베이스 객체

데이터베이스 객체 : View, Index, Table, Procedure … DB내에 존재하는 모든 실체

데이터베이스 객체는 유일한 이름을 갖음

ex.  SQL은 데이터베이스 객체? X → 이름을 지정해서 procedure로 저장하면 그것도 객체가 될 수는 있음
       select 구에서 컬럼의 alias는 객체? X

1.1. 데이터 베이스 객체의 명명 규칙

• 1. 기존 이름이나 예약어를 사용할 수 없다.
  2. 알파벳과 숫자로 구성되지만 숫자로 시작할 수 없다.
  3. _이외의 문장 기호는 사용 X
  4. 한글 사용 지양, 필요 시 "" 로 둘러싼다.
  5. 시스템이 허용하는 길이 초과하지 않기
  6. 의미있는 이름 부여
  7. 동일한 스키마 내에서 모든 객체는 유일한 이름이 되도록 한다.

 

1.2. 스키마

• 데이터베이스 객체는 스키마라는 그릇 안에 만들어짐
• 객체의 이름은 스키마 내에서만 유일하게 사용하면 됨
• DDL을 이용하여 정의
• MySQL/MariaDB에서는 create database를 이용하여 스키마 생성(데이터베이스 생성)
• 중복 이슈를 제거하기 위하여 namespace 개념을 적용
  • 스키마, 테이블이 하나의 namespace임

---

◆DML

• Data Manipulation Language
• 데이터베이스에 데이터를 추가 또는 삭제하거나 내용을 갱신하는 등 데이터를 조작할 때 사용
• 기본

◆ DDL

• Data Definition Language
• 스키마, 테이블, 뷰 등의 "데이터베이스 객체(Object)"를 만들거나 삭제하는 명령어

◆ DCL

• Data Control Language
• Transcaction을 제어하는 명령과 데이터베이스 객체의 접근권한 등을 제어하는 명령어 

 

 

---

2. 테이블의 작성과 삭제

2.1. create table

create table sample 62(
	no int not null,
    a varchar(30),
    b date
);

1. create table 명령어로 작성
2. 테이블명과 소괄호를 작성
3. 각 컬럼명(명명규칙을 따르는 것을 주의)과 그 옆에 해당 컬럼의 자료형(int, char, varchar 등)을 작성
4. 기본값, Null을 허용할지 안할지에 대해서 선언
5. 작성한 후 desc 명령어를 통해 객체 확인!

 

2.2. drop table

drop table sample62;

• 테이블 자체를 삭제하는 명령어
• 재확인이 어려우니 사용에 주의
• 데이터도 같이 삭제
• 데이터만 삭제하려면 delete from명령와와 where 구를 사용

 

※ 행 전체 삭제 명령어 truncate

 

---

3. 테이블 변경

alter table sample62 변경명령;

• alter table로 테이블을 변경할 수 있음
• 필요에 따라 컬럼 추가, 최대 길이 변경 등이 가능

 

◆ alter table로 가능한 작업

• 컬럼의 추가, 삭제, 변경
• 제약의 추가와 삭제

3.1. 컬럼 추가

alter table sample62 add val1 int;

• add 구를 통해 컬럼을 추가할 수 있음.
• 컬럼이 추가되면 모든 값이 Null 또는 default로 지정된 기본값으로 입력.

 

3.2. 컬럼 속성 변경

alter table sample62 modify val1 varchar(10);

• modify 구를 통해 컬럼의 속성을 변경할 수 있음
• 자료형, 기본값, null 제약 조건 변경 가능
• 컬럼의 자료형을 변경하면 원래 존재하던 데이터의 자료형도 변경되니 주의

 

3.3. 컬럼 이름 변경

alter table sample62 change val1 c;

• change 구를 통해 컬럼명 변경 가능
• 자료형 변경까지 가능

 

3.4. 컬럼 삭제

alter table sample64 drop c;

• drop 구 이용
• 주의하여 사용할 것

◆ Alter table을 이용해서 주로 관리하는 것

• 컬럼 추가
  • 응용시스템에 영향을 줄 수 있으니 사용에 주의
• 데이터 최대길이 연장
  • modify 구 적용

4. 제약조건

테이블 생성 또는 변경 시 많은 제약조건이 설정 가능

제약 조건의 종륲

• null / not null
• primary key
• foreign key
• unique

컬럼 제약 조건

• null / not null
• unique 등

테이블 제약 조건

• primary key 등

---

• 제약 조건에 이름을 부여하면 향후 제약조건 관리에 유리

 

4.1. 제약조건 추가/삭제

컬럼 제약 조건 추가

alter table sample631 modify c varchar(30) not null;

테이블 제약 조건 추가

alter table sample631 add constraint pri_key primary key(a);

---

컬럼의 제약조건 삭제 (alter table)

alter table sample631 modify c varchar(30);

제약 조건이 있었을 때 저렇게 해버리면 사라짐.

테이블 제약조건의 삭제 alter table 명령어의 drop 구 사용

alter table sample631 drop primary key;

 

4.2. 기본키

• 기본키를 갖는 컬럼은 not null, unique 제약조건을 갖음
•  데이터 추가, 수정시 제약조건을 위반하면 오류 발생

→ 여러 테이블을 연결해서 사용할 때 중요한 역할을 함.

복수의 컬럼을 하나의 기본키로 구성될 수 있음

 

4.3. 인덱스

• 인덱스는 테이블에 붙여진 색인으로 검색 속도를 향상
•  select 명령에서 where 구로 조건을 지정한 경우의 검색 (where 구가 빠지면 full scan과 동일)
•  인덱스는 테이블과 다른 별개의 데이터베이스 객체
•  보통 테이블이 삭제되면 인텍스 객체도 삭제

 

4.3.1. 검색 알고리즘

• full table scan : 인덱스가 설정되지 않는 테이블은 처음부터 차례로 모든 항목 검색
• 이진 검색 (binary search) : 데이터셋을 반으로 나누어 검색
• 이진 트리 (binary tree) : 이진 트리로 구성된 구조로 작은 값은 왼쪽, 큰 값은 오른쪽 가지로 분리

 

4.3.2. 인덱스 작성/삭제

create index

• 테이블 내 객체이므로 중복 주의
• 필요시 복수의 컬럼 설정 가능

create index idx_sample64 on sample62(no);

drop index 인덱스명 on 테이블명

• 인덱스 삭제

 

4.3.3. Explain

• 인덱스 활용시 검색 성능을 향상
• explain 명령은 인덱스를 활용하는지 확인가능 (SQL명령이 실제 수행되지 않음)

◆select 명령에 대하여 인 인덱스 사용 여부의 선택은 DBMS의 최적화 프로세스

explain은 이러한 실행계획을 확인하는 명령어

 

---

5. 뷰(view)

데이터 베이스 객체 중 하나

• 서브쿼리의 from 구의 select 명령어와 같은 개념으로 뷰 사용가능
• 뷰를 참조하면 그에 정의된 select 명령어의 결과값을 반환
• 복잡한 select 명령을 뷰를 사용하여 간략하게 사용가능
• 뷰는 테이블과 같이 실제로 존재하는 것이 아닌 ‘가상 테이블’
• 뷰는 물리적 데이터를 갖고 있지 않으므로 되도록 insert, update, delete 명령문의 사용은 지양

---

◆ 뷰의 약점

• 뷰는 데이터베이스 객체로 select 명령어가 저장되어 있음
• 뷰는 테이블의 공간을 갖고 있지 않고 select 명령어를 수행하는 CPU 의 컴퓨터 자원을 사용
• 뷰를 참조할 때마다 select 명령이 실행
• 테이블에 데이터가 많은 경우, 집계 처리에 사용되는 경우, 뷰를 중첩해서 사용하는 경우 뷰의 처리 속도가 떨어짐
• 이러한 속도 저하를 방지하기 위하여 뷰 결과를 임시적으로 저장해 놓는 뷰를 Materialized View가 있으나 MySQL에서는 지원하지 못함

---

 

마무리

MySQL을 통해 테이블을 생성하고 삭제하고 변경하는 법을 배우고

각각 어떤 제약조건을 줘야되는지에 주의하면서 생성을 할 것!

 

인덱스는 검색 속도에 도움을 주는 객체

 

 

---