SQL | MySQL | 테이블 생성,수정,삭제 - CREATE TABLE, ALTER TABLE, DROP TABLE

 
 
 
MySQL에서는 다음과 같은 CREATE 문을 사용하여 데이터 베이스와 테이블을 만들 수 있다. !

 
① CREATE DATABASE
② CREATE TABLE 
 
 
참고로  DB 관리툴은 DBeaver를 사용할 것이다.
https://dbeaver.io/ 

DBeaver Community | Free Universal Database Tool

 

---

 

00. 데이터베이스 생성 

 

CREATE DATABASE 데이터베이스이름

 
· 'BORI'라는 데이터베이스를 생성 
     
     - CHARACTER SET : 문자들과 그 문자들을 코드화인 인코딩들의 조합. 
     - COLLATE : 문자열을 비교, 정렬하기 위해 정의된 규칙들의 집합.
 

        문자셋과 정렬 참고 ☞ https://dev.mysql.com/doc/refman/8.0/en/charset-mysql.html

CREATE DATABASE BORI  DEFAULT CHARACTER SET utf8mb4 COLLATE  utf8mb4_general_ci;

 
· 생성된 데이터베이스 목록 확인

SHOW DATABASES ;

 

 
 
- 유닉스 환경의 MySQL에서는 데이터베이스 이름의 대소문자를 구분하지만
  윈도우 환경의 MySQL에서는 대소문자를 구분하지 않는다. 
 
 

---

 

01. 테이블 생성 - CREATE TABLE

 

1)  테이블 생성 구문 

 
· 열 이름과 자료형을 지정 

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]

 
· SELECT 결과를 저장(구조+자료)

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement

 
· 기존 테이블 구조를 복사 

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }

 
 

2) 테이블 생성 실습 

 
- 테이블을 생성할 때, 데이터베이스를 지정해줘야 하는데 dbeaver 툴의 상단바에서 데이터베이스를 미리 지정하면 편하다. 
 

 

· 테이블 생성 

CREATE TABLE 테이블명 (
     열이름1 열타입1 , 
     열이름2 열타입2 , ...
     );

CREATE TABLE people (
     person_id int , 
     person_name varchar(10),
     age TINYINT,
     birthday date
     );

 

 
· 테이블 정보 보기 

DESCRIBE people;

DESC people;

 

 

• 테이블 구조 복사
  : 기존에 있는 테이블 구조를 그대로 복사함. 

 -- CREATE TABLE 복사할테이블명 LIKE 기존테이블명
 CREATE TABLE `PEOPLE2` LIKE `PEOPLE`;

 
 
 

2)  제약조건 (constraint) 

 
: 데이터의 무결성을 지키기 위해 데이터를 입력받을 때 실행되는 검사규칙
: CREATE문으로 테이블을 생성하거나 ALTER 문으로 열을 추가할 때 설정 가능
 
 
CREATE TABLE문에 사용되는 제약 조건 
 

• NOT NULL : 해당 열에 NULL(빈 값) 입력 불가 
• UNIQUE: 해당 열의 값들은 서로 다른 값을 가져야 함, 중복 불가 
• PRIMARY KEY: NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가짐/ NULL불가, 중복 불가
• FOREIGN KEY: 하나의 테이블을 다른 테이블에 의존하게 만듦
• DEFAULT : 값 입력이 없을 시 기본값 설정 
• UNSIGNED : 숫자일 경우 양수만 입력 가능 
• AUTO_INCREMENT : 새로운 행 생성시마다 자동으로 1씩 증가

 
 
PRIMARY KEY
 - 테이블 한 개에 하나만 가능 
 - 기본적으로 인덱스를 생성함(기본키 행 기준으로 빠른 검색 가능) 
 - 보통 AUTO_INCREMENT와 함께 사용
 - 각 행을 고유하게 식별 가능 (UNIQUE) 
 
 
· 제약 생성 

DROP TABLE  if exists PEOPLE2; -- 테이블이 있다면 삭제 

CREATE TABLE PEOPLE2(
    person_id int AUTO_INCREMENT PRIMARY KEY,
    person_name varchar(10) NOT NULL,
    nickname varchar(10) UNIQUE NOT NULL,
    age TINYINT UNSIGNED);

 

 
 

3) 데이터 삽입 -- INSERT INTO

 
·컬럼명, 값입력

INSERT INTO people
  (person_id, person_name, age, birthday)
  VALUES (1, '김보리', 21, '1999-01-01');

 
·모든 컬럼에 값을 넣을 때는 컬럼명 생략 가능 

INSERT INTO people
  VALUES (2, '감보리', 18, '2003-05-12');

 
· 일부 컬럼에만 값 넣기 가능 

INSERT INTO people
  (person_id, person_name, birthday)
  VALUES (3, '이보리', '1999-11-04');

 
· 여러행 입력 가능 

INSERT INTO people
  (person_id, person_name, age, birthday)
  VALUES 
    (4, '이성민', 27, '1997-01-01'),
    (5, '이성만', 28, '1997-12-07');

 

 
 

---

 
 

02. 테이블 변경 - ALTER TABLE

 

1) 테이블명 변경 

ALTER TABLE people RENAME TO  student,

 

2) 컬럼 자료형 변경 

ALTER TABLE student MODIFY COLUMN person_id TINYINT;

 

3) 컬럼명 변경 

: ALTER TABLE 테이블명 CHANGE 기존컬럼명 변경할컬럼명 컬럼타입;

ALTER TABLE student CHANGE person_name student_name VARCHAR(10);

 

4) 컬럼 추가 

: ALTER TABLE 테이블명 ADD COLUMN 추가할컬럼명 컬럼타입 [DEFALUT 디폴트값] [AFTER 컬럼위치]
: ALTER TABLE 테이블명 ADD COLUMN 추가할컬럼명 컬럼타입 [DEFALUT 디폴트값] [FIRST] -> 가장 맨앞에

-- AGE 컬럼 앞에 IS_MARRIED 컬럼 생성
ALTER TABLE STUDENT ADD COLUMN is_married INT DEFAULT 0 AFTER age;

-- 맨 앞에 MARRIED 컬럼 생성
ALTER TABLE STUDENT ADD COLUMN married INT DEFAULT 0 FIRST;

 
 

5) 컬럼 삭제 

: ALTER TABLE 테이블명 DROP COLUMN 컬럼명
 

ALTER TABLE STUDENT DROP COLUMN is_married;
ALTER TABLE STUDENT DROP COLUMN married;

 

 
 

6) PRIMARY KEY 추가

ALTER TABLE student ADD PRIMARY KEY(person_id);

 

 

7) PRIMARY KEY 삭제  

ALTER TABLE student DROP PRIMARY KEY;

 

 
 
 

---

 

03. 테이블 삭제  - DROP TABLE 

 

1) DROP TABLE  테이블명 

DROP TABLE PEOPLE2 ;

 

2) DROP TABLE  if exists 테이블명

  : 삭제하려는 테이블명이 존재할 경우 삭제 

DROP TABLE if exists STUDENT;

 
· 현재 데이터베이스에 존재하는 테이블 조회 

SHOW TABLES;

 

테이블이 존재하지 않음. 잘 DROP 됨

 
 
※ 참고로 외래키(FOREIGN KEY) 가 있을 때는 참조하는 테이블을 먼저 삭제해야 함. 
 
 

---

출처 및 참고 문헌(reference)

https://dev.mysql.com/doc/refman/8.0/en/preface.html

MySQL :: MySQL 8.0 Reference Manual :: Preface and Legal Notices

http://www.tcpschool.com/mysql/mysql_basic_create

코딩교육 티씨피스쿨

https://www.yalco.kr/@sql/3-2/

테이블 만들고 데이터 입력하기