MySQL 총 정리1: 명명 규칙, 테이블 생성ㆍ구조 변경ㆍ삭제

 

항상 공부하고 복습하지 않으면 잊어버리는 머리...

정리하며 기록해두자

---

[ 목차 ]
1. 테이블 생성 'CREATE'
1) 테이블 명명 규칙(Naming Conventions)
2) 기본 문법
※ 컬럼 명명 규칙
📝 Surrogate Key
3) 예시
📝 INT(N)의 N의 의미
📝 INT vs DECIMAL
📝 VARCHAR(N)의 N의 의미
📝 LENGTH() vs CHAR_LENGTH()

2. 테이블 구조 변경 'ALTER'
• ALTER 주요 기능

3. 테이블 삭제 'DELETE / TRUNCATE / DROP'
• DELETE, TRUNCATE, DROP의 차이

 

1. 테이블 생성 'CREATE'

1) 테이블 명명 규칙(Naming Conventions)

• 당연한 이야기지만, MySQL에서 예약어로 사용되는 단어(SELECT, INSERT 등)는 피한다.
• 소문자를 사용한다. (리눅스 운영체제의 경우 대/소문자를 구분하여 table1과 TABLE1은 다른 테이블로 인식함)
• 집합 명사를 사용, 이상적이지는 않으나 복수형 사용도 괜찮다.
• 예: staff(집합 명사), employees(복수형)
• 같은 데이터베이스 내에서 테이블 이름은 유일해야 한다.
• 여러 단어로 구성된 이름의 경우 Snake Case를 사용한다.  예: customer_orders
  ➯ Camel Case는 읽기가 좀 힘들다.  예: customerOrders
• 문자로 시작하되 _로 끝나지 않게 한다.
• 띄어쓰기 대신 _를 사용한다.
• 특수 문자 사용을 피한다. (애초에 _랑 $ 만 테이블명에 사용할 수 있긴 함)
• 헝가리안 표기법을 사용하지 않는다.
  ➯ 헝가리안 표기법: 변수 및 함수의 인자 이름 앞에 데이터 타입을 명시하는 규칙

 

2) 기본 문법

CREATE TABLE 테이블_이름 (
    열1_이름 열1_데이터타입 [제약조건],
    열2_이름 열2_데이터타입 [제약조건],
    ...
);

 

※ 컬럼 명명 규칙(Naming Conventions)

•  MySQL 예약어 피하기, 소문자 사용, Snake Case 사용 등 기본적인 규칙은 테이블 때와 같다.
• 컬럼 이름은 해당 컬럼이 어떤 데이터를 저장하는지를 명확히 나타내야 한다.
• 특별한 이유가 없다면 Auto Increment 속성의 Surrogate Key(대체키)를 Primary Key로 사용하며,
  이때 컬럼의 이름은 "테이블 이름의 단수형" + "_id"로 한다.
  ➯ employees 테이블의 PK 컬럼: employee_id
• 컬럼이 BIT 유형인 경우: "is" 와 "has" 같은 접두사를 붙인다.
  (BIT 유형: 0/1, 참/거짓, 활성화/비활성화 이렇게 두가지 상태로 표현할 수 있는 유형)
  ➯ is_completed, is_verified, has_started 등

 

📝Surrogate Key

• 데이터베이스에서 사용되는 대체 키로, 자연 키(Natural Key)와는 달리 실제 비지니스 의미가 없는 고유한 값

• 단순히 레코드를 식별하기 위한 용도로 사용되며, 이는 데이터베이스의 구조가 변경되거나 비지니스 로직이 변경되더라도 안정성을 제공한다.

employee_id (Suggorate Key)	employee_number (Natural Key)	name	department
1	E001	Alice	HR
2	E002	Charlie	IT
3	E003	Bob	Marketing
4	E004	David	IT

 

3) 예시

가장 간단한 예시부터 보자.

  CREATE TABLE products (

       제품번호 INT PRIMARY KEY,

       제품명 VARCHAR(100) NOT NULL

       제품가격 INT

  );

• 컬럼의 데이터 타입을 무조건 입력해주어야 한다.

• 데이터가 모두 숫자로 이뤄진 경우 VARCHAR로 지정해도 되나, 계산하는 로직들이 사용하게 될 것 같으며 숫자형으로 지정해주자.

• PRIMARY KEY: NULL일 수 없는 값으로 행을 고유하게 식별하는 컬럼

• NOT NULL: 해당 열에 대한 값이 NULL일 수 없는, 즉 비어있을 수 없음을 명시하는 제약 조건

 

  CREATE TABLE users (

       id INT(11) AUTO_INCREMENT PRIMARY KEY,

       username VARCHAR(10) NOT NULL,

       email VARCHAR(20) UNIQUE,

       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

  );

• AUTO_INCREMENT를 통해 고유값인 PRIMARY KEY를 자동으로 생성해 줄 수도 있다.

• 현재 id 열은 정수형으로 AUTO_INCREMENT로 인하여 users테이블에 대한 새 행이 추가될 때마다 id 값이 자동으로 1씩 증가하여 부여된다.

 

📝INT(11)의 11의 의미

• 11자리 숫자까지 저장할 수 있어요~ 하는 범위 내지 자릿수를 얘기하는 것이 아니다.

• 실제 저장 가능한 정수의 범위는 INT 타입의 기본 범위인, (-2,147,483,648 ~ 2,147,483,647)와 동일하다.

• 해당 열의 값이 몇자리로 표시되도록 할 것인지에 대한 디스플레이 형식을 뜻한다.

• 디폴트 값이 11이다.

• INT의 범위는 10자리수 ~ 10자리수인데 왜 11인가?:  음수 표현을 위해

• INT(숫자)를 쓰는 건 Zerofill 옵션을 추가할 때나 의미가 있다.

• Zerofiil: 숫자가 지정된 디스플레이 길이보다 짧을 경우, 앞에 0을 채워 출력하는 옵션

  # Zerofiill 예시

  CREATE TABLE example (

       id INT(3) ZEROFILL

  );

• id값이 1이면 001로 데이터베이스에 기록되며, 12면 012,

• id값이 123이면 123으로, id값이 1234면 1234로 기록된다.

 

  CREATE TABLE animal_shelter (

       공고번호 VARCHAR(10) PRIMARY KEY,

       시군명 VARCHAR(10) NOT NULL,

       발견장소 VARCHAR(25) NOT NULL,

        공고시작일자 DATE NOT NULL,

       공고종료일자 DATE NOT NULL,

       품종 VARCHAR(25) NOT NULL,

       색상 VARCHAR(10) NOT NULL,

       나이 INT NOT NULL,

       체중 DECIMAL(4,2) NOT NULL

  );

📝 INT vs DECIMAL

• INT

➯ 정수값 저장

• DECIMAL

➯ 고정 소수점 숫자 저장

➯ 형식: DECIMAL(M,D)

➯ M: 총 자릿수 (정수 부분 + 소수 부분) , D: 소수 부분의 자릿수

➯예: DECIMAK(4,2) → 12.99, 3.20

 

📝 VARCHAR(숫자)의 숫자의 의미

• 넣을 수 있는 값의 자릿수를 의미한다.

• MySQL 4.1 이전에는 자릿수가가인 바이트를 의미했었다.

• 그래서 한글일 경우 한 글자당 몇 바이트인지 찾아보고 계산해서 값을 지정해 줬었던 기억이...

 

📝 LENGTH() vs CHAR_ LENGTH()

•  LENGTH()

➯ 바이트 수 반환

• CHAR _ LENGTH()

➯ 문자 수 반환

  SELECT CHAR_LENGTH(1) FROM DUAL;

  # → 1 반환

  SELECT CHAR_LENGTH('A') FROM DUAL;

  # → 1 반환

  SELECT CHAR_LENGTH('가') FROM DUAL;

  # → 1 반환

 

  SELECT LENGTH(1) FROM DUAL;

  # → 1 반환

  SELECT LENGTH('A') FROM DUAL;

  # → 1 반환

  SELECT LENGTH('가') FROM DUAL;

  # → 3 반환

 

 

---

2. 테이블 구조 변경 'ALTER'

1) 테이블에 새 열 추가

ALTER TABLE animal_shelter ADD 특징 VARCHAR(50);

 

2) 기존 열 수정

# 최대 자릿수 변경
ALTER TABLE animal_shelter MODIFY COLUMN 특징 VARCHAR(100);
# 데이터 타입 변경
ALTER TABLE animal_shelter MODIFY COLUMN 특징 TEXT;

 

3) 열 이름 변경

ALTER TABLE animal_shelter CHANGE COLUMN 특징 특징란 VARCHAR(100);

•  열 이름을 변경할 때도 데이터 타입을 적어줘야한다.

 

4) 열 삭제

ALTER TABLE animal_shelter DROP 특징란;

 

5) 디폴트 값 설정ㆍ변경

ALTER TABLE animal_shelter ALTER 시군명 SET DEFAULT '경기도 용인';

 

6) 디폴트 값 삭제

ALTER TABLE animal_shelter ALTER 시군명 DROP DEFAULT;

 

7) 테이블 이름 변경

ALTER TABLE animal_shelter RENAME animal_haven;

 

 

---

3. 테이블 삭제 'DELETE / TRUNCATE / DROP'

1) DELETE

DELETE FROM animal_shelter;

• 테이블에 저장된 일부(조건을 주었을 때) / 모든 데이터를 삭제한다.

• 테이블 구조는 유지된다. (테이블은 삭제되지 않는다.)

• 각 행을 하나씩 삭제하기 때문에 대량의 데이터를 삭제할 때 속도가 느리다.

• COMMIT 수행 이전이라면, 복구가 가능하다.

 

2) TRUNCATE

TRUNCATE TABLE animal_shelter;

• 테이블에 저장된 모든 데이터 삭제한다.

• 삭제된 데이터는 복구가 불가능하다.

• 테이블 구조는 유지된다. (테이블은 삭제되지 않는다.)

• 모든 행을 한 번에 삭제하기 때문에 속도가 빠르다.

 

3) DROP

DROP TABLE animal_shelter;

• 테이블 자체를 삭제한다.

• 즉 해당 테이블과 관련된 모든 데이터 및 인덱스, 제약 조건 등이 완전히 사라진다는 것.

• 삭제된 테이블은 복구가 불가능하다.

 

 

---

참고:

https://www.sqlstyle.guide/#tables

https://purumae.tistory.com/200