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)
Last updated