MySQL - CRUD

1. CREATE

• INSERT 문 : 데이터(row) 추가

INSERT INTO table_name (column1, column2,..)
VALUES (value1, value2,..);

 

2. READ

• SELECT 문 : 데이터가 추가된 것을 확인 - 자주 사용됨

<SELECT syntax>

SELECT
    [ALL | DISTINCT | DISTINCTROW ] --3개 중 하나 입력
    [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_name --생략가능
      [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] ...
}

 

• 모든 데이터 읽기

SELECT * FROM table_name;

 

• 특정 칼럼만 읽기 

SELECT column1, column2,.. FROM table_name;

 

• 특정 데이터(행)만 읽기

SELECT column1, column2,... FROM table_name WHERE column_name = data;

• 데이터 정렬

SELECT column1, column2, .. FROM table_name WHERE column_name='data' ORDER BY column_name DESC;

 

• 데이터 개수 제한

SELECT column1, column2,.. FROM table_name WHERE column_name = 'data' 
ORDER BY column_name DESC LIMIT 2;

 

 

3. UPDATE

<UPDATE syntax>

UPDATE [LOW_PRIORITY] [IGNORE] table_name
    SET assignment_list
    [WHERE where_condition] -- 어느 부분에서 업데이트를 진행할 것인지(식별자)
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

 

• 데이터 수정하기 - WHERE 문 빠뜨리지 않게 주의!

UPDATE table_name SET column1='..', column2='..',.. WHERE primarykey1='..';

 

 

4. DELETE 

<DELETE syntax>

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

 

• 데이터 삭제하기 - WHERE 문 빠뜨리지 않게 주의! (모든 데이터가 삭제될 수 있음)

DELETE FROM table_name WHERE primarykey1='..';