[MariaDB] chapter3

CREATE: 데이터베이스 객체(테이블, 뷰, 인덱스 등)를 생성

CREATE TABLE 테이블이름 (
    열1_이름 데이터_유형 [열1_제약조건],
    열2_이름 데이터_유형 [열2_제약조건],
    ...
    [PRIMARY KEY (기본키_열)],
    [UNIQUE KEY (고유키_열)],
    [FOREIGN KEY (외래키_열) REFERENCES 참조테이블(참조열)],
    [CHECK (조건)],
    [열_명령어],
    ...
);

열 데이터 유형 (Data Type): 각 열의 데이터 유형을 정의.
예를 들어, INT, VARCHAR(50), DATE 등이 가능.

열 제약조건 (Column Constraint): 각 열에 대한 제약조건을 정의.
예를 들어, NOT NULL, AUTO_INCREMENT, DEFAULT 등의 제약조건

열 명령어 (Column Option): 열의 속성을 지정하는 옵션을 정의. 
- AUTO_INCREMENT
주로 정수 열에 사용 열에 값을 삽입할 때 자동으로 증가하는 고유한 값을 생성
> 만일 delete로 데이터를 지우고 다시 추가하면 번호가 이어서 생성
(1,2,3 지우고 다시 추가하면 4번부터 시작)
> 아에 번호를 새롭게 부여하려면 delete 가 아니라 truncate를 활용
> alter table 테이블명 auto_increment=100; 으로 하면 100부터 시작
>set @@auto_increment_increment = 3; 으로하면 3씩 증가함.

- NOT NULL
해당 열에 NULL 값을 허용하지 않음을 나타냄. 열에 항상 값을 포함해야함.

CREATE TABLE MEMBER
(
mem_id CHAR(8) NOT NULL PRIMARY KEY,  
-> null 허용 x primary key 로 제약걸기  
-> PK로 제약을 걸면 null값 허용이 안되어 not null안써도 됨
mem_name VARCHAR(15) NOT NULL,
height TINYINT NULL null 허용

);

CREATE TABLE MEMBER( 
	mem_id CHAR(8) NOT NULL,
	mem_name VARCHAR(15) NOT NULL,
	height TINYINT NULL,
	PRIMARY KEY(mem_id)
);

제약조건

기본 키 (Primary Key) 제약조건

기본 키는 테이블의 각 행을 고유하게 식별. null값 넣을 수 없음
기본 키는 테이블에 포함해야 하며, 주로 정수 ID 또는 고유한 식별자 열로 사용

CREATE TABLE MEMBER
(
mem_id CHAR(8) NOT NULL PRIMARY KEY, 
-> null 허용 x primary key 로 제약걸기  
-> PK로 제약을 걸면 null값 허용이 안되어 not null안써도 됨
mem_name VARCHAR(15) NOT NULL,
height TINYINT NULL 
-> null 허용

);

CREATE TABLE MEMBER
(
mem_id CHAR(8) NOT NULL 
mem_name VARCHAR(15) NOT NULL,
height TINYINT NULL 
constraint primary key [키이름](mem_id)

);

pk의 이름을 직접 지어줄 수 있다.
이름을 부여할 때에는 위처럼 이름을 넣어주면 됨
하지만 pk 이름은 항상 primary로 표시되어 많은 의미는 없지만
fk는 하나의 테이블에 여러 개 생성될 수 있으니 이름을 지정해
관리하는게 좋긴 함.

고유 제약조건 (Unique Constraint)

고유 제약조건은 열의 값이 중복되지 않도록 하며 null값을 넣을 수 있음
예를 들어, 이메일 주소나 사용자 이름이 중복되지 않도록 할 때 사용

CREATE TABLE MEMBER
(
mem_id CHAR(8) NOT NULL PRIMARY key,
mem_name VARCHAR(15) NOT NULL,
height TINYINT UNSIGNED NULL,
email CHAR(30) NULL UNIQUE
) ;

외래 키 제약조건 (Foreign Key Constraint)

외래 키 제약조건은 두 테이블 간의 관계를 유지.
부모 테이블의 기본 키와 관련된 데이터를 자식 테이블에서 참조하는 데 사용.
기준테이블이 pk나 unique가 아니라면 외래 키 관계는 설정되지 않음.
이것은 데이터의 무결성을 유지하고 데이터 간의 일관성을 보장하는 데 중요.

CREATE TABLE buy
(
num int IN AUTO_INCREMENT PRIMARY KEY,
mem_id CHAR(8) NOT NULL,
prod_name CHAR(8) NOT NULL,
CONSTRAINT fk이름 FOREIGN KEY(mem_id) REFERENCES 기준테이블(mem_id)
);

[옵션] - 별도로지정하지 않으면 no action
on delete cascade
부모 테이블에서 레코드가 삭제될 때 
해당 레코드와 관련된 자식 테이블의 레코드도 자동으로 삭제

on update cascade
부모 테이블에서 기본 키 값을 변경할 때, 자식 테이블의 외래 키 열도 자동으로 변경

체크 제약조건 (Check Constraint)

체크 제약조건은 열의 값이 특정 조건을 충족해야 함을 정의.
예를 들어, 나이가 양수여야 하거나 특정 범위 내에 있어야 하는 경우에 사용.

CREATE TABLE MEMBER
(
mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(15) NOT NULL,
height TINYINT UNSIGNED NULL CHECK(height <=100),  
-> 100 이하의 값만 넣을 수 있다.
phone1 CHAR(3) NULL);

기본값 제약조건 (Default Constraint)

기본값 제약조건은 열에 값을 지정하지 않을 때 사용될 기본값을 정의.
예를 들어, 날짜 열에 현재 날짜 및 시간을 자동으로 기본값으로 설정.

CREATE TABLE MEMBER
(
mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(15) NOT NULL,
height TINYINT UNSIGNED NULL DEFAULT 160, 
-> 사용자가 기재하지 않으면 자동으로 160 기입
phone1 CHAR(3) NULL);

ALTER: 데이터베이스 객체의 구조를 변경하는데 사용

ALTER TABLE 테이블이름 동작_유형 [동작_옵션];

- 테이블이름: ALTER할 대상 테이블의 이름
- 동작_유형: 어떤 종류의 ALTER 동작을 수행할 것인지를 지정 
추가, 수정 또는 삭제와 관련된 동작
- 동작_옵션: ALTER 동작의 옵션을 추가로 지정 
새로운 열을 추가하거나 열의 데이터 유형을 변경하는 등의 동작을 구체적으로 설명

1. 열 추가 (Add Column)
ALTER TABLE employees
ADD COLUMN phone_number VARCHAR(20);

2. 열 수정 (Modify Column)
ALTER TABLE products
MODIFY COLUMN price DECIMAL(10, 2);
ALTER table student MODIFY COLUMN weight FLOAT; 
-> 데이터 타입 변환

3. 열 삭제 (Drop Column)
ALTER TABLE customers
DROP COLUMN old_column;

4. PRIMARY KEY 추가 (Add Primary Key)
ALTER TABLE orders
ADD PRIMARY KEY (order_id);
Alter tabel 테이블명 add constraint primary key(필드명, 필드명); 

5. INDEX 추가 (Add Index)
ALTER TABLE products
ADD INDEX idx_product_name (product_name);

6. FOREIGN KEY 추가 (Add Foreign Key)
ALTER TABLE order_details
ADD constraint 제약조건 이름 
FOREIGN KEY (product_id) 
REFERENCES products(product_id);
ALTER TABLE buy ADD CONSTRAINT foreign KEY(mem_id)
REFERENCES MEMBER(mem_id); 로 추가 가능
ALTER table buy DROP CONSTRAINT fk_mem_id; 
제약조건 지우기, 제약조건에 별칭(이름)을 넣어줘야 삭제도 가능

다른 제약조건을 추가할 떄에도 alter를 사용
ALTER TABLE 테이블이름
ADD CONSTRAINT 필드이름
CHECK (조건);

CONSTRAINT는 생략 가능.
Alter로 cascase를 하려면  fk제약을 걸지 않고 테이블을 만들어야 함.
Alter로 cascade를 넣으려면 앞에 제약조건도 추가로 넣어줘야 함.

DROP: 데이터베이스 객체를 삭제하는데 사용

DROP OBJECT_TYPE [IF EXISTS] OBJECT_NAME;
- OBJECT_TYPE: 삭제할 데이터베이스 객체의 유형을 나타냄. 
예를 들어, 테이블(TABLE), 뷰(VIEW), 인덱스(INDEX) 등
- [IF EXISTS]: 선택적으로 사용, 
해당 객체가 이미 존재하지 않을 때 오류를 방지하기 위해 사용.
- OBJECT_NAME: 삭제할 데이터베이스 객체의 이름을 지정.

1. 테이블 삭제
DROP TABLE employees;

2. 뷰 삭제
DROP VIEW sales_report;

3. 인덱스 삭제
DROP INDEX idx_employee_name ON employees;

4. 데이터베이스 삭제
DROP DATABASE mydb;

5. if exists 사용
DROP TABLE IF EXISTS non_existent_table;

만일 fk로 연결되어 있으면 fk쪽 테이블 지운 뒤 pk쪽 테이블 지워야 함.
테이블을 만들 때 제약조건만 넣고 cascade를 넣지 못하였으면,
Drop으로 제약조건을 지운 뒤 다시 제약조건을 걸면서 cascade를 넣어야 함.

TRUNCATE: 테이블의 모든 데이터를 삭제하는데 사용

TRUNCATE TABLE table_name;
- table_name: 데이터를 삭제할 대상 테이블의 이름.

TRUNCATE는 DELETE와는 다르게 트랜잭션 로그를 작성하지 않기 때문에 롤백 불가.