[MySQL] CRUD - Create, Select, Insert, Update, Delete

728x90

데이터베이스의 본질인 CRUD

데이터를 생성하고, 읽고, 수정하고, 삭제하는 방법이다.

이 중, Create와 Select문이 가장 중요하다.

경우에 따라 Update와 Delete는 사용할 경우도 별로없고, 실무에서는 사용하지 않는 경우도 많기 때문이다.

예를 들어 금융 전산의 경우 데이터를 바꾸거나(Update) 지우는 것(Delete) 자체로 범죄가 되는 ... 💣

하지만 프로젝트를 진행하면 데이터를 조작 할 일이 아주 많으므로 SQL문을 배운다면 CRUD 모두를 알아두어야 한다.

DDL, DML, DCL 에 관련해서는 데이터베이스 글에 설명해두었으므로 이를 참고하자.

2021/03/02 - [DataBase] - 데이터베이스(Database, DB)

데이터베이스(Database, DB)

🔍 DB, DBMS, RDBMS 개념 정리 1. 데이터베이스(Database, DB) 중복을 최소화하고, 다수의 사용자가 공유할 수 있도록 데이터들을 모아둔 시스템 관련된 데이터를 모아 이를 통합하여 관리할 수 있도록

heestory217.tistory.com

MySQL 기본

SQL은 대/소문자 구분없이 사용
데이터는 대/소문자를 구분함
Data type은 일치 시켜야 한다
SQL문을 종료할때는 ; (세미콜론)을 반드시 사용;
SQL 주석 (오라클과 다르게 한줄주석은 -- 이 아닌 #을 사용한다)

# 한 줄 주석
/*
여러 줄 주석
*/

토드 SQL문 실행 단축키 : [F9]

# 데이터베이스 조회

SHOW DATABASES;

# 테이블 조회

SHOW TABLES;

# 테이블 구조 조회 (Describe)

DESC 테이블명;

1. Create (DDL)

# 테이블 생성하기

테이블 생성 문법은 다음과 같다.

Create table 테이블명 (
  컬럼명 자료형 널 여부 자동증가(시퀀스),
  컬럼명 INT(11) NOT NULL AUTO_INCREMENT,
  컬럼명 VARCHAR(100) NOT NULL,
  컬럼명 TEXT NULL,
  컬럼명 DATETIME NOT NULL,
  컬럼명 VARCHAR(30) NULL,
  컬럼명 VARCHAR(100) NULL,
  PRIMARY KEY(id)
);

#테이블 생성하기
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)
);

# 테이블 이름 변경

ALTER TABLE 기존 테이블명 RENAME 새 테이블명;

# 테이블 삭제

DROP 테이블명;

🔍 Null ? Not Null?

해당 컬럼의 데이터가 Null이어도 (비어있어도) 되는지 여부를 나타낸 것

예를 들어, Insert문을 작성할 때 컬럼이 Not Null 인 경우에는 반드시 입력해주어야 데이터가 입력된다.

Not Null인 경우, 데이터 필수 입력!

🔍 auto_increment?

Oracle의 시퀀스 개념으로 데이터가 입력될 때 마다 자동으로 +1씩 증가한다.
대부분 테이블의 기본키 (PRIMARY KEY : 식별자)에 쓰인다.

🔍 PRIMARY KEY (기본키, 주 식별자)

관계형 데이터베이스에서 모든 테이블은 반드시 하나의 기본키를 가져야 한다.

기본키는 Unique 하며(중복 불가), Not null (널값일 수 없음)이고, 테이블의 레코드를 고유하게 식별하기 위해 사용된다.

🔍 Oracle - MySQL 데이터 타입 차이

숫자 자료형 number 대신 int 사용
문자 자료형 varchar2 대신 varchar 사용
날짜 자료형 timstamp 대신 datetime 사용
Clob 대신 text 사용
sequence 대신 auto_increment 사용

💡 현재일자를 Default로 지정하는 방법

1. MySQL v5.6.5 이상일 경우 : NOW() 함수가 아닌 CURRENT_TIMESTAMP 함수를 사용

# created 테이블의 기본키를 현재날짜로 지정하여 테이블 생성
create table customer(
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  created DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id)
);

# oliver 라는 이름을 가진 데이터 입력
insert into customer(name)
values('OLIVER');

# 테이블 조회
select * from customer;

2. MySQL v5.6.5 미만일 경우 : 해당 테이블에 트리거를 작성해야 함

# created 테이블의 기본키를 현재날짜로 지정하여 테이블 생성
CREATE TABLE customer2(
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  created DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id)
);

# 테이블에 데이터가 insert 되기 전에 created 컬럼에 now()함수를 써서 오늘날짜 자동 입력되는 트리거 생성
CREATE
  TRIGGER customer2_On_Insert BEFORE INSERT
    ON customer2 FOR EACH ROW
  SET
    NEW.created = NOW();

# oliver 라는 이름을 가진 데이터 입력
insert into customer2(name)
values('OLIVER');

# 테이블 조회
select * from customer2;

➡ 두 결과 모두, 테이블에는 자동으로 증가한 id (기본키) 컬럼, 입력한 이름(OLIVER) 그리고 created (현재날짜 자동입력) 컬럼 데이터가 입력됨

💡 필드(컬럼) 추가/삭제/이름 변경

# 필드(컬럼) 추가

 ALTER TABLE 테이블명 ADD 새로 추가할 필드명과 타입 (위치 설정[AFTER 필드명]);

# 필드(컬럼) 삭제

ALTER TABLE 테이블명 DROP 삭제할 필드명;

# 필드명(컬럼명) 변경

ALTER TABLE 테이블명 CHANGE 기존 필드명 새로운 필드명 타입;

# 필드(컬럼)의 타입만 변경

ALTER TABLE 테이블명 MODIFY 기존필드명 새로운 타입;

2. Insert (DML)

# 데이터 입력하기

데이터 입력 Insert 문법은 다음과 같다.

이 때, value 값은 컬럼의 자료형과 일치해야하며 입력한 컬럼 순서대로 값을 입력해야 한다.

#MYSQL CREATE ROW - INSERT
INSERT INTO topic(컬럼1, 컬럼2, 컬럼3, 컬럼4, 컬럼5) 
VALUES (값1, 값2, 값3, 값4, 값5);

INSERT INTO topic(title, description, created, author, profile) 
VALUES ('Oracle', 'Oracle is...', NOW(), 'Hee', 'developer');

예 :

숫자 자료형(int)인 경우 숫자 입력

문자 자료형(varchar)인 경우 따옴표('' 홑따옴표 or "" 쌍따옴표)로 감싸서 문자로 입력

날짜컬럼에 오늘 날짜를 입력하고 싶은 경우, NOW() 함수를 사용한다.

3. Select (DML)

# 데이터 조회하기

#SELECT * FROM 테이블명 => 테이블의 모든 레코드 조회 : *
SELECT * FROM topic;

#SELECT (조회하고 싶은 컬럼 목록) FROM 테이블;
SELECT id, title, created, author FROM topic;

# 조건에 맞는 데이터 조회하기 (WHERE 조건문)

title 컬럼의 값이 MySQL인 레코드만 조회하기

# SELECT (조회하고 싶은 컬럼 목록) FROM 테이블 WHERE 조건문
SELECT id, title, created, author FROM topic WHERE title="MySQL";

# ORDER BY : 데이터 정렬하여 조회하기

생략 또는 ASC : 오름차순 1 2 3 4 , DESC : 내림차순 4 3 2 1

# ORDER BY : 정렬
# 생략 또는 ASC : 오름차순 DESC:내림차순
SELECT id, title, created, author FROM topic
ORDER BY id DESC;

# LIMIT : 원하는 갯수만큼만 데이터 조회

LIMIT 개수
게시판에서 페이징 처리 시 사용된다.

SELECT id, title, created, author FROM topic
ORDER BY id DESC LIMIT 1;

📚 MySQL SELECT syntax : SELECT 공식 문법

dev.mysql.com/doc/refman/8.0/en/select.html
[ ] : 대괄호 부분은 생략 가능함

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

여기서 Oracle을 사용하던 사람들은 깜짝 놀랄 부분이 있다.

바로 FROM을 안써도 레코드를 조회할 수 있다는 것..!

오라클에서는 FROM 테이블명을 명시해야만 데이터를 조회할 수 있었다.

그래서 테이블이 없는 시퀀스 값 같은 경우는 가상 테이블 dual 을 써서 조회했었는데

MySQL 에서는 하단 SQL문처럼 FROM 없이 레코드를 조회할 수 있다.

#title 레코드 조회
SELECT "title"; 
SELECT "title", 1+1;

4. Update (DML)

UPDATE 테이블명 SET 변결컬럼=변경값 WHERE 조건문;

UPDATE topic SET author="Kim" WHERE id=3;

조건을 지정하지 않으면, 모든행이 수정되므로 유의하자.

dev.mysql.com/doc/refman/8.0/en/update.html

5. Delete (DML)

DELETE FROM 테이블명 WHERE 조건문;

DELETE FROM topic WHERE id=3;

조건을 지정하지 않으면, 모든행이 삭제되므로 유의하자.

dev.mysql.com/doc/refman/8.0/en/delete.html

이 포스팅에서는 CRUD 기본만 다뤘지만, 엄청나게 다양한 방식으로 데이터를 조회하고 작업할 수 있기 때문에 SQL문을 잘 다루는 건 아주 중요합니다. (프로젝트에서 뼈저리게 느낀 부분.. )