SQL쿼리문 복습 - 'Database+Table' create/alter add, alter modify /drop, 생성, 삭제, 수정

멘토님이 보고 감탄하신, 내가 쓴 블로그 참고용 

[MySQL] MySQL 명령문 - DDL, DML, DQL, table/post 생성 수정 추가 삭제, Join

---

아래 내용은 

terminal에 직접 쳐보면서 연습해보았다. 

 

DELETE FROM 'table' WHERE '조건' 을 위해서,

alter ADD 로 칼럼 추가하고,

insert into로 값을 넣고,

삭제해 보기, 

DELETE FROM 'table name' WHERE 후에 select * from users; 하면 없다.   

 

 

> 여러 열 동시에 추가할 때

add column 을 매번 쳐줘야 하고, varchar 뒤에 (길이 제한) 써줘야 함, INT 뒤에는 길이 제한 없음 

 

> 이미 만들어진 posts 테이블에 'title, content, user_id' 열 추가할 때, user_id는 숫자 형식 

---

1. 데이터베이스 객체

관계형 데이터베이스(RDBMS, Relational Database Management System)에서는

데이터가 여러 개의 2차원 테이블에 저장되고 관리된다

 

- 스키마 설계

- 실제 물리적인 서버에 RDBMS software (ex. MySQL)를 설치

- Table, View, Index, Procedure 등의 데이터베이스 객체 작성 -> 데이터베이스(Schema) 구축

 

실제로 개발 과정에서 스키마를 구축하고 내부에 있는 테이블을 생성, 변경, 삭제하는 작업을 많이 하게되는데,

이때 필요한 데이터베이스 객체를 CRUD 방법

1-1. Database Object 데이터베이스 객체 

 2차원 테이블(Table), 뷰(View), 인덱스(Index) 등 데이터베이스 내에 정의하는 모든 것

 

데이터베이스 객체: 데이터베이스 내에 실체를 가지는 어떤 것

 

ex. 테이블

 

객체의 종류에 따라

객체 내에 저장되는 데이터도 달라짐

 

데이터베이스 객체는 이름을 갖고,

이름을 붙일 때는 다음과 같은 명명규칙에 맞게 지정해야 합니다.

• 중복하지 않는다.
• 숫자로 시작할 수 없다.
• 언더스코어(_) 만 사용 
• 한글: 더블쿼트(MySQL에서는 백쿼트)로 둘러싼다.
• 시스템이 허용하는 길이를 초과하지 않는다.
• 객체에 이름을 정할때는 객체가 담고 있는 정보를 명확하게 표현할 수 있는 이름을 선택하도록 한다!

1-2. Schema = 데이터베이스 

데이터베이스 내의 객체(table, view, index)는

스키마(Schema)라는 컨테이너 안에 만들어집니다.

 

아래 mysql(클라이언트 프로그램)으로

mysqld(서버 프로그램)에 접속한 후에

 

show databases; -> MySQL 서버 내의 데이터베이스 목록을 출력

 

 information_schema, performance_schema, mysql과 같이 MySQL 소프트웨어를 설치하면 기본적으로 생성되는 데이터베이스가 존재하는 것을 확인할 수 있습니다.

$ mysql -u root -p

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \\g.
Your MySQL connection id is 8
Server version: 8.0.28 Homebrew

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------------+
| Database                 |
+--------------------------+
| information_schema       |
| mysql                    |
| performance_schema       |
| sys                      |
+--------------------------+
5 rows in set (0.00 sec)

해당 스키마 내에는 mysql 서버 관리를 위해서 필요한 여러가지 테이블, 뷰, 인덱스와 같은 데이터베이스 객체들이 담겨있습니다.

 

USE  information_schmea를 사용하겠다고 MySQL 서버에 알려주고,

SHOW TABLES;  스키마 내에 테이블(table) 객체 목록을 출력해볼 수 있습니다.

mysql> USE information_schema;
mysql> SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| INNODB_BUFFER_PAGE                    |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_CACHED_INDEXES                 |
| INNODB_CMP                            |
| TABLES                                |
| TABLES_EXTENSIONS                     |
| TABLESPACES                           |
| TABLESPACES_EXTENSIONS                |
| TRIGGERS                              |
| USER_ATTRIBUTES                       |
| USER_PRIVILEGES                       |
| VIEW_ROUTINE_USAGE                    |
| VIEW_TABLE_USAGE                      |
| VIEWS                                 |
+---------------------------------------+
79 rows in set (0.00 sec)

 

CREATE DATABASE;라는 DDL이라는 SQL명령으로 작성한 데이터베이스(Database)가 스키마(Schema)가 됩니다.

mysql> CREATE DATABASE instagram;
mysql> SHOW DATABASES;
+--------------------------+
| Database                 |
+--------------------------+
| information_schema       |
| mysql                    |
| performance_schema       |
| **instagram**                |
| sys                      |
+--------------------------+
5 rows in set (0.00 sec)

 

스키마 는 데이터베이스 내의 객체(table, view, index)를 담는 컨테이너라고 정의할 수 있습니다. 테이블과 스키마는 무엇인가를 담는 그릇 역할을 한다는 점에서 비슷합니다. 테이블 안에는 열을 정의할 수 있고 스키마 안에는 테이블을 정의할 수 있습니다.

 

2. 테이블 생성, 삭제, 수정(CREATE, ALTER, DROP ) ; DDL 

대표적인 데이터베이스 객체인 2차원 테이블을 생성, 삭제, 변경하는 방법에 대해서 알아보겠습니다.

 

2-1. DDL이란?

DDL은 Data Define Language의 약자로써

 

Schema 내의 객체를 정의하고 관리할 때 사용되는 쿼리문을 의미합니다. CREATE, ALTER, DROP 이 대표적인 DDL입니다.

 

DDL에 속하는 쿼리문의 이름에서도 알 수 있듯이 데이터를 구성하는 논리적인 구조(데이터베이스 또는 테이블)을 추가하고 수정하고 삭제할 수 있습니다. 또한 메타데이터(데이터에 대한 데이터)를 다루는 쿼리문이라고 할 수 있습니다.

[그림 2-1] SQL statements 종류

 

2-2. 스키마 생성

Instagram의 미니어쳐 버전인 Westagram System을 만든다고 가정하고 이때, Westagram System에서 사용되는 스키마, 테이블을 관리하는 방법에 대해서 알아보도록 하겠습니다. 서비스에 필요한 데이터를 저장할 수 있는 데이터베이스 시스템을 구축할 때, 가장 먼저 테이블을 정의하기 전에 테이블 객체를 담을 수 있는 스키마를 생성합니다.

**$ mysql -u root -p #** 명령어로 mysql 서버에 접속해 프롬프트 창이 나타난 상태로 있어야합니다.

mysql> CREATE DATABASE westagram; # Database 를 생성하는 명령어
mysql> SHOW DATABASES; # 현재 MySQL 에 저장되어있는 데이터베이스를 보는 명령어
+--------------------------+
| Database                 |
+--------------------------+
| **westagram**                |
| information_schema       |
| mysql                    |
| performance_schema       |
| sys                      |
+--------------------------+

아래와 같이 명령 프롬프트에서 instagram 스키마를 사용하겠다고 MySQL 서버에 알려주고, SHOW TABLES; 명령을 전달하면 해당 스키마 내에 테이블(table) 객체 목록을 출력해볼 수 있습니다. 아직까지는 테이블을 생성하지 않았으니 테이블의 정보가 없습니다.

mysql> USE westagram;
mysql> SHOW TABLES;

Empty set (0.00 sec)

여기까지 SQL의 DDL 문법 중에 하나인 CREATE DATABASE를 통해 instagram 스키마를 생성했습니다. 이어서 DDL을 활용해서 instagram schema 내에 필요한 테이블 객체를 생성, 수정, 삭제하는 방법을 살펴보겠습니다.

 

2-3. Table 생성 Create 

RDBMS에서 스키마 내에서 가장 먼저 만드는 객체중에 하나가 바로 테이블입니다.

 

> CREATE TABLE 'table 이름' ( id INT [NOT NULL], column1 VARCHAR(255) [NULL], column2 DATE [PRIMARY KEY], column3 DATE [PRIMARY KEY], column4 DECIMAL [PRIMARY KEY]); 

> CREATE TABLE 'table 이름' ( id INT); 

// 귀찮으니까 id INT까지만 하고, 나중에 column 추가함 

DECIMAL (전체자릿수, 소수점이하자릿수)
: DECIMAL(10, 2)는 소수점 이하 2자리를 포함한 총 10자리의 숫자를 저장
// 금액을 나타내는 열을 만들 때 

열(column)에는 컬럼명과 함께 자료형과 제약 조건을 지정해줄 수 있습니다.

자료형으로는 INTEGER, VARCHAR, DATETIME 등이 있는데, 특히 자주 사용되는 VARCHAR나 CHAR와 같은 문자열형으로 지정할 때는 최대길이를 정해줘야 합니다.

 

제약 조건으로는 자료형 뒤에 오는 NULL, NOT NULL, PRIMARY KEY 등이 있습니다.

문자 그대로 테이블 혹은 컬럼에 제약을 설정함으로써 저장될 데이터를 제한할 수 있습니다. 

mysql> CREATE TABLE users 
(
  id INT NOT NULL AUTO_INCREMENT,                        -- id 칼럼을 정수, NULL을 허락하지 않으며 값을 자동증가하게 설정합니다.
  name VARCHAR(50) NOT NULL,                             -- name 칼럼을 50 bytes의 크기를 가지는 문자열, NULL을 허락하지 않습니다.
  email VARCHAR(200) NOT NULL,
  profile_image VARCHAR(1000) NULL,    
  password VARCHAR(200) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),           -- 데이터의 이력관리를 위해서 생성 시간을 기록합니다.
  updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP, -- 데이터의 이력관리를 위해서 변경 시간을 기록합니다.
  PRIMARY KEY (id)                                       -- 이 테이블의 Primary Key를 id 로 설정합니다.
);                                                       -- 쿼리문은 ; 로 구분되기 때문에 엔터를 쳐서 쿼리문을 멀티라인으로 작성할 수 있습니다.

DESC 'table 이름' ; 테이블을 생성한 후에 원하는 대로 잘 생성 되었는지 확인 합니다.

 

 

[create posts table]

게시물 테이블(posts)을 생성합니다.

 

사용자와 게시물이 1:M 관계이므로,

post 테이블: 어떤 사용자가 작성한 게시글인지 테이블 간에 부모 자식 관계를 지정(데이터의 정합성을 위함)

-> 외부키(FOREIGN KEY) 지정, 부모 테이블을 참조하도록

 

이때, 부모 테이블에서 참조될 열:  반드시 유일성을 가져야 하므로 보통 PRIMARY KEY를 지정합니다.

mysql> CREATE TABLE posts
(
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL,
  content VARCHAR(2000) NULL,
  user_id INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES users (id) -- 외래키를 걸어줄 칼럼과 참조할 테이블과 칼럼을 순서대로 적어줍니다.
);

auto_increment를 하려면, primary key(id)를 해야 함 

 

테이블을 생성한 후에 항상 DESC [table name] 원하는 대로 잘 생성 되었는지 확인 

출력된 테이블을 확인해보면, user_id field의 Key column을 보면 MUL 값이 들어가 있는 것을 확인할 수 있습니다. 다른 테이블의 PK를 참조하는 FK는 이처럼 MUL로 표현되고 MULTIPLE 이라는 뜻 입니다. posts 테이블의 여러 행(row)이 동일한 값을 가질 수 있음을 의미합니다. 예를 들어서, 다수의 게시물(post)가 동일한 user_id를 가질 수 있기 때문입니다.

 

---

2-4. 데이터베이스 변경- Table 변경: 칼럼, 내용 

이번에는 이미 만들어진 혹은 기존에서비스에서 사용중이던 테이블을 변경하는 방법에 대해서 알아보도록 하겠습니다.

 

• users 테이블에 age 열을 추가합니다. (자료형 → INT, 제약 조건 → NOT NULL)
• users 테이블의 profile_image 열을 삭제합니다.
• posts 테이블의 content 열의 자료형을 VARCHAR에서 TEXT로 변경합니다.

 DROP table [TABLE 이름]

-> 테이블을 완전히 삭제하고  CREATE TABLE 명령으로 다시 생성

 

 

> ALTER TABLE '변경하고자하는 table 이름'  'ADD'  'column 이름'  '자료형' '제약 조건'  // 열  추가

desc 'table 이름' 확인

 

 

여러 개의 열 동시에/한번에 추가

- ALTER TABLE 'table 이름'

   ADD COLUMN 'colunm1 INT', ADD COLUMN 'colunm2 varchar', ADD COLUMN 'colunm name3 INT'

- CREATE TABLE 'table 이름' (column1 INT AUTO_INCREMENT PRIMARY KEY,  column2 VARCHAR(50),

column3 VARCHAR(50)); 

 

1) 이미 만들어진 테이블에서 동시에 여러 열 추가

add column 을 매번 쳐줘야 하고, varchar 뒤에 (길이 제한) 써줘야 함, INT 뒤에는 길이 제한 없음 

 

2) 새로 테이블 만들면서 여러 열 추가할 때

> ALTER TABLE '변경하고자하는 table ' DROP 'column 이름' // 열 삭제 

desc 'table 이름' 로 확인

 

> ALTER TABLE '변경하고자하는 table명' MODIFY 'column 이름' '자료형' '제약 조건' //열 수정

mysql> ALTER TABLE posts MODIFY content TEXT NULL;

기존에 테이블 객체 내에 데이터(row)가 존재하는 경우, 자료형(Type) 변경에 따라 데이터의 자료형 역시도 변환됩니다. 다만 처리과정에서 에러가 발생하면 ALTER TABLE 명령이 적용되지 않을 수 있습니다. 지금 예제에서는 테이블의 자료형 변경에 대한 내용만 다루었지만, MODIFY가 아닌 CHANGE 명령을 통해서 열 이름도 변경할 수 있습니다. 자세한 내용은 MySQL 공식문서를 참고해주시기를 바랍니다.

2-5. Table 삭제

 DROP TABLE [table 이름]  // 테이블 +  테이블 안에 포함된 모든 데이터  삭제

 

DELETE FROM [table name] WHERE  // 테이블 내의 데이터만 삭제

TRUNCATE TABLE [table name]  // 모든 행을 삭제