CRUD - (1) Create

본 포스팅은 MySQL 8.0.23 버전을 사용중이며, 툴은 Toad for MySQL 8.0 사용 중 입니다.

CRUD

✔ 데이터베이스 의 꽃 CRUD !

 

Create, Read, Update, Delete 를 간단하게 CRUD 라고 한다.

이 중 가장 핵심인것은 Create,  Read 이다. (update나 delete 는 경우에 따라 사용 하지 않아도 된다.)

 

SQL문 은 파고 들어갈수록 점점 더 심오해진다. 간단하게 이것만 알면 되겠지~ 하는데 더 알아야한다......

 

스프링 프로젝트 하면서 맨 처음에는 '아 뭐... 자바에서 해결 하자~ ' 했는데, 쿼리문으로 처리하면 정말 너무나도 완전많이 편해진다.... 한번 깨닳은 후 에는 쿼리문으로 쇼부 칠려고 노력했다. (ㅋㅋ)

여튼 잘 다룰 수 있도록 열심히 공부하자.

 

MySQL은 학원에서 다루었던 Oracle과는 다르게 자료형이 바뀐다.

Oracle과 다른 점

table type 변경

number  → int

varchar2  →  varchar

date  → datetime, timestamp

 (default로 현재일자를 줄 수 있으나, timestamp는 table당 하나만 가능)

 clob  → text

 

sequence 대신 auto_increment 사용

 

 

MySQL 에서 자주 쓰는 자료형

int : 4byte 이며, 정수형이다.

varchar : 최대 65535byte 이며, 가변길이이다. 짧은 데이터 입력시 나머지 공간은 채우지 않는다.

date : 3byte 이며 년, 월, 일 자료형이다.

time : 3byte 이며 시, 분, 초 자료형이다.

datetime : 8byte 이며 날짜와 시간(기간) 자료형이다.

시간과 관련된 자료형은 데이터를 추가할 때 NOW() 함수 혹은  CURRENT_TIMESTAMP 를 써주면 현재시간으로 데이터가 입력된다.

 

정수형 자료형

tinyint 1byte 이며, -128 ~ 127 또는 0 ~ 255 까지 표현 가능하다.

smallint : 2byte 이며, -32768 ~ 32767 또는 0 ~ 65536 까지 표현 가능하다.

mediumint : 3byte 이며, -8388608 ~ 8388607 또는 0 ~ 16777215 까지 표현 가능하다.

bigint : 8byte 이며, 무제한 수 표현 가능하다.

float : 4byte 이며, 고정 소수점 사용 형태이다.

decimal : 길이 + 1byte 이며, 소수점 사용 형태이다.

double : 8byte 이며, double을 문자열로 저장한다.

문자열 자료형

char : 최대 255byte 이며, 고정 길이이다. 지정된 길이보다 짧은 데이터 입력시 나머지 공간은 공백으로 채워진다.

tinytext : 최대 255byte 이다.

text : 최대 65535byte 이다.

mediumtext : 최대 16777215byte 이다.

longtext : 최대 4294967295byte 이다.

날짜 자료형

timestamp : 4byte 이며, 기간 자료형이다. 시스템 변경시 자동으로 그 날짜와 시간이 저장된다.

year : 1byte 이며, 년도 자료형이다.

 

 

✔ 짚고 넘어가자
SQL은 대/소문자 구분없이 사용
단, 데이터는 대/소문자를 구분함
숫자가 아닌 문자형 값을 넣을땐 ' ' 를 꼭 사용
Data Type 일치 해야함
SQL문을 종료할때는 ; (세미콜론) 을 반드시 사용

 

1. 테이블 생성

CRUD중 가장 먼저 해야할 일은 '생성' 하는 것이다.

테이블을 생성을 해야 Read도 하고 Update도 하고 Delete도 할 수 있으니까.. 당연한 얘기다.

 

데이터를 저장할 테이블을 생성하려면 어느 데이터베이스에서 테이블을 생성할지 정해줘야 한다.

데이터베이스 생성은 bbinya.tistory.com/8 을 참고하자.

 

테이블 생성 방법

create table 테이블명(
    컬럼명 데이터타입,
    컬럼명 데이터타입
PRIMARY KEY(컬럼명)
);

✔ 프라이머리 키 (PRIMARY KEY)
테이블에서 레코드가 가지는 유일한 값을 지정하는 것. 특정 레코드를 검색하거나 레코드들을 정렬할 때 우선적으로 참조된다

 

위의 테이블 생성 방법은 기본 문법이다.

테이블을 처음 만들때에 PRIMARY KEY를 설정해준것 처럼 제약조건을 몇몇개 넣어 줄 수 있다.

 

✔ 제약조건? 왜 넣는건데?
무결성을 지키기 위해 제약조건들을 제공한다.
데이터를 삽입할때 조건을 만족했을 경우에만 데이터가 삽입되도록 제약을 할 수 있는 것.

 

🔎 무결성 제약조건 ( Integrity Constraints)

 

• NULL / NOT NULL

필수 입력 사항이 아닌것. 데이터 값이 비워져있어도 상관없다.

필수 입력 사항일땐 NOT NULL을 적어준다.

Null 값을 직접 넣기 위해서는 null 이나 ‟을 넣으면 됨
❗ 주의 : Null은 값이 없는 것으로 공백('  ')과는 다름

 

• UNIQUE

같은 테이블 내에서 중복이 안되는 제약조건. 고유한 데이터들의 제약조건이다.

Unique 키가 null을 허용하기는 하지만, not null 속성을 주는 것이 좋다
( null을 여러 번 입력했는데도 별다른 오류 없이 모두 저장된다.
null 은 값이 없음을 의미하므로 unique 키 비교 대상에서 제외되기 때문. 
  unique 키는 null을 무시하고 있는 것 )

• PRIMARY KEY

NOT NULL 과 UNIQUE 가 합쳐진 제약조건.

데이터들을 식별할때 쓰이는 제약조건으로 PK 라고도 불린다.
테이블 하나당 오직 1개만 생성 가능 

 

• FOREIGN KEY

외래키. FK라고 불린다. 다른 테이블을 참조한다. 참조하는 테이블에서 존재하는 값만 사용 가능하다.

부모 테이블의 PK가 자식 테이블의 FK

외래키를 만들기 전에 반드시 부모 테이블이 먼저 생성되어 있어야 한다.
단, 참조하는 부모 테이블의 컬럼은 반드시 기본 키 또는 unique키 여야 한다.
한 개 이상의 컬럼으로 외래 키 생성 가능(복합 외래키) .

자식 테이블에 존재하는 값을 부모 테이블에서 삭제할 수 없다 
insert 시 : 먼저 부모 테이블 데이터 입력 → 자식 테이블 데이터 입력 
delete (update) : 먼저 자식 테이블 데이터 삭제(수정) → 부모 테이블 데이터 삭제(수정) 

💡 ON DELETE CASCADE 옵션을 주면, 자식 테이블에 데이터가 있어도 부모 테이블에서 삭제 가능하며, 자식 테이블의 데이터도 같이 삭제해버림

• CHECK

주어진 조건에 해당하는 값만 입력 가능하다. 조건에 해당하지 않는 데이터가 들어오면 오류를 발생시킨다.

예) 성별(gender) 컬럼 => 남자, 여자만 입력되고 다른 값은 입력 될 수 없도록
gender  VARCHAR(10) NOT NULL CHECK ( gender IN ('남자', '여자')) 

• DEFAULT

기본값을 설정한다. 해당 컬럼에 값을 입력 할때 따로 입력하지 않으면 자동으로 DEFAULT 값이 들어간다.
컬럼 타입 다음에 'default 디폴트값' 을 명시 
반드시 데이터 타입 다음에, null이나 not null 앞에 위치시켜야 함 

 

테이블 생성 예시 

TOPIC 이라는 테이블을 만들어 보자

CREATE TABLE TOPIC(
  ID INT(11) NOT NULL AUTO_INCREMENT,
  TITLE VARCHAR(100) NOT NULL,
  DESCRIPTION TEXT NULL,
  CREATED DATETIME NOT NULL,
  AUTHOR VARCHAR(30) NULL,
  PROFILE VARCHAR(100) NULL,
  PRIMARY KEY(ID)
);

 

✔ 여기서 잠깐 AUTO_INCREMENT 에 대해서 짚고 넘어가자.

특정 데이터는 구분자가 되기위해 PK(Primary key) 설정을 한다.
PK의 특징은 고유성(유니크)을 가져야하기 때문에 똑같은 데이터를 넣을 수 없다.

그래서 보통 PK를 int 타입으로 겹치지않게 인덱스(정수값)를 계속 올린다.
그때 그 인덱스(정수값)를 체크해 자동으로 올려주는 옵션이 바로 AUTO_INCREMENT이다.

테이블 컬럼에 AUTO_INCREMENT 를 지정하기 위해선 기본키(PK) 여야 적용 된다

참고 : InnoDB일때 auto_increment 사용 시 발생될 수 있는 에러들
 http://gywn.net/2013/02/mysql-innodb-auto-increment/

 

이미 만들어진 테이블에 AUTO_INCREMENT 타입으로 변경
  ALTER TABLE [테이블명] MODIFY [컬럼명] [데이터타입:int] AUTO_INCREMENT;

AUTO_INCREMENT 값 초기화 하기
  ALTER TABLE [테이블명] AUTO_INCREMENT=[시작할려는 순서]
주의 : 테이블에 새로 시작할 값보다 높은 값이 있으면 안된다.

AUTO_INCREMENT가 적용된 컬럼값 재정렬하기
  SET @COUNT = 0;
  UPDATE [테이블명] SET [컬럼명] = @COUNT:=@COUNT+1;

 

 

2. INSERT 

자, 테이블 생성 까지 따라왔으면, 빈 테이블 안에 이제 자료들을 넣어주자.

 

INSERT 구문

INSERT INTO 테이블이름(컬럼1, 컬럼2, 컬럼3, ...)
VALUES (데이터값1, 데이터값2, 데이터값3, ...);

INSERT INTO 테이블이름
VALUES (데이터값1, 데이터값2, 데이터값3, ...);

위 아래 두가지 방법 다 가능하다.

컬럼1, 2, 3 즉 필드라고 불리기도 하는데, 이 필드의 이름은 두번째 처럼 생략이 가능하다.

이 경우에는 데이터베이스의 스키마 ( 테이블의 필드(컬럼) ) 와 같은 순서대로 값이 자동 대입 된다.

 

 

첫번째 방법에서 생략할 수 있는 컬럼은 다음과 같다.

1.  NULL을 저장할 수 있도록 설정된 필드
2.  DEFAULT 제약 조건이 설정된 필드
3.  AUTO_INCREMENT 키워드가 설정된 필드

 

나는 topic 이란 테이블에 title, description, created, author, profile 컬럼에 직접 데이터를 넣고 싶어!

INSERT INTO topic(title, description, created, author, profile) 
VALUES ('BIN', 'Bin IS...',  CURRENT_TIMESTAMP, 'Bin', 'developer');

INSERT INTO topic(title, description, created, author, profile) 
VALUES ('HEE', 'Hee IS...',  CURRENT_TIMESTAMP, 'Hee', 'developer');

INSERT INTO topic(title, description, created, author, profile) 
VALUES ('JUNG', 'Jung IS...',  CURRENT_TIMESTAMP, 'Jung', 'developer');

INSERT INTO topic(title, description, created, author, profile) 
VALUES ('MySQL', 'MySQL IS...',  CURRENT_TIMESTAMP, 'egoing', 'developer');

INSERT INTO topic(title, description, created, author, profile) 
VALUES ('Oracle', 'Oracle IS...',  CURRENT_TIMESTAMP, 'egoing', 'developer');

 

다음과 같이 Insert 가 가능하다.

-현재시간을 나타내는것은  NOW() 함수 혹은 CURRENT_TIMESTAMP 사용한다.
-물론 테이블을 정의할때도 DEFAULT NOW() , DEFAULT CURRENT_TIMESTAMP 사용 가능.

 

DESC 테이블명;

 

해당 테이블의 컬럼명과 자료형이 나온다. INSERT 할때 참고하면 좋다.

 

 

 

---

참고 사이트

velog.io/@bungouk6829/mysql-%EC%9E%90%EB%A3%8C%ED%98%95

blog.naver.com/imf4/220762181574