CRUD

Create(데이터 추가)

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

mysql> INSERT INTO topic (title,description,created_at,author,profile) VALUES ('MYSQL','MYSQL is....', NOW(),'dahye','developer');
Query OK, 1 row affected (0.01 sec)

Read(읽기)

• SELECT syntax

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

• 테이블의 모든 데이터 읽기

SELECT * FROM tableName;

mysql> SELECT * FROM topic;
+----+-------+--------------+---------------------+--------+-----------+
| id | title | description  | created_at          | author | profile   |
+----+-------+--------------+---------------------+--------+-----------+
|  1 | MYSQL | MYSQL is.... | 2018-05-22 01:09:21 | dahye  | developer |
+----+-------+--------------+---------------------+--------+-----------+
1 row in set (0.00 sec)

• 특정 column 가져오기

mysql> SELECT id, title, created_at, author FROM topic;
+----+------------+---------------------+--------+
| id | title      | created_at          | author |
+----+------------+---------------------+--------+
|  1 | MYSQL      | 2018-05-22 01:09:21 | dahye  |
|  2 | ORACLE     | 2018-05-22 01:10:38 | dahye  |
|  3 | SQL Server | 2018-05-22 01:11:35 | dahye  |
|  4 | PostgreSQL | 2018-05-22 01:11:55 | dahye  |
|  5 | MongoDB    | 2018-05-22 01:12:13 | dahye  |
|  6 | SQLite3    | 2018-05-22 01:12:28 | dahye  |
+----+------------+---------------------+--------+
6 rows in set (0.00 sec)

• WHERE문을 이용해 Filter기능!

mysql> SELECT id, title, created_at, author FROM topic WHERE author='dahye';
+----+------------+---------------------+--------+
| id | title      | created_at          | author |
+----+------------+---------------------+--------+
|  1 | MYSQL      | 2018-05-22 01:09:21 | dahye  |
|  2 | ORACLE     | 2018-05-22 01:10:38 | dahye  |
|  3 | SQL Server | 2018-05-22 01:11:35 | dahye  |
|  4 | PostgreSQL | 2018-05-22 01:11:55 | dahye  |
|  5 | MongoDB    | 2018-05-22 01:12:13 | dahye  |
|  6 | SQLite3    | 2018-05-22 01:12:28 | dahye  |
+----+------------+---------------------+--------+
6 rows in set (0.00 sec)

• 정렬하기

mysql> SELECT id, title, created_at, author FROM topic WHERE author='dahye' ORDER BY id DESC;
+----+------------+---------------------+--------+
| id | title      | created_at          | author |
+----+------------+---------------------+--------+
|  6 | SQLite3    | 2018-05-22 01:12:28 | dahye  |
|  5 | MongoDB    | 2018-05-22 01:12:13 | dahye  |
|  4 | PostgreSQL | 2018-05-22 01:11:55 | dahye  |
|  3 | SQL Server | 2018-05-22 01:11:35 | dahye  |
|  2 | ORACLE     | 2018-05-22 01:10:38 | dahye  |
|  1 | MYSQL      | 2018-05-22 01:09:21 | dahye  |
+----+------------+---------------------+--------+
6 rows in set (0.00 sec)

• LIMIT

mysql> SELECT id, title, created_at, author FROM topic WHERE author='dahye' ORDER ESC LIMIT 2;
+----+---------+---------------------+--------+
| id | title   | created_at          | author |
+----+---------+---------------------+--------+
|  6 | SQLite3 | 2018-05-22 01:12:28 | dahye  |
|  5 | MongoDB | 2018-05-22 01:12:13 | dahye  |
+----+---------+---------------------+--------+
2 rows in set (0.00 sec)

Update

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

mysql> UPDATE topic SET author='mirea' WHERE id=3;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

WHERE문을 빠트리면 테이블 전체가 변경되므로 주의해야한다.

Delete

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

mysql> DELETE FROM topic WHERE id=5;
Query OK, 1 row affected (0.03 sec)