[생활코딩] MySQL (2)

opentutorials.org/course/3161/19538

MySQL의 CRUD - 생활코딩

8. MySQL의 CRUD (Create, Read, Update, Delete)

 

9. SQL의 INSERT 구문

mysql> SHOW TABLES;
+-----------------+
| Tables_in_first |
+-----------------+
| topic           |
+-----------------+
1 row in set (0.00 sec)

mysql> DESC topic;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| title       | varchar(100) | NO   |     | NULL    |                |
| description | text         | YES  |     | NULL    |                |
| created     | datetime     | NO   |     | NULL    |                |
| author      | varchar(15)  | YES  |     | NULL    |                |
| profile     | varchar(200) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> INSERT INTO topic (title,description,created,author,profile) 
				VALUES('MySQL', 'MySQL is ...', NOW(), 'egoing','developer');
Query OK, 1 row affected (0.00 sec) 
# 실수로 이 줄 두번 출력함 ^^;

mysql> INSERT INTO topic (title,description,created,author,profile) 
				VALUES('Oracle', 'Oracle is ...', NOW(), 'egoing','developer');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO topic (title,description,created,author,profile) 
				VALUES('SQL Server', 'SQL Server is ...', NOW(), 'duru','data administrator');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO topic (title,description,created,author,profile) 
				VALUES('PostgreSQL','PostgreSQL is ...', NOW(), 'taeho','data scientist, developer');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO topic (title,description,created,author,profile) 
				VALUES('MongoDB','MongoDB is ...', NOW(), 'egoing','developer');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2021-01-19 14:11:08 | egoing | developer                 |
|  2 | MySQL      | MySQL is ...      | 2021-01-19 14:11:22 | egoing | developer                 |
|  3 | Oracle     | Oracle is ...     | 2021-01-19 14:12:57 | egoing | developer                 |
|  4 | SQL Server | SQL Server is ... | 2021-01-19 14:14:32 | duru   | data administrator        |
|  5 | PostgreSQL | PostgreSQL is ... | 2021-01-19 14:15:19 | taeho  | data scientist, developer |
|  6 | MongoDB    | MongoDB is ...    | 2021-01-19 14:15:57 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
6 rows in set (0.00 sec)

 

10. SQL의 SELECT 구문

 - description, profile 숨겨 보기

mysql> SELECT id, title, created, author FROM topic;
+----+------------+---------------------+--------+
| id | title      | created             | author |
+----+------------+---------------------+--------+
|  1 | MySQL      | 2021-01-19 14:11:08 | egoing |
|  2 | MySQL      | 2021-01-19 14:11:22 | egoing |
|  3 | Oracle     | 2021-01-19 14:12:57 | egoing |
|  4 | SQL Server | 2021-01-19 14:14:32 | duru   |
|  5 | PostgreSQL | 2021-01-19 14:15:19 | taeho  |
|  6 | MongoDB    | 2021-01-19 14:15:57 | egoing |
+----+------------+---------------------+--------+
6 rows in set (0.00 sec)

- author가 'egoing'인 것만 보기 (WHERE 문) + id 내림차순으로 보기 (ORDER BY 문 : ASC, DESC) + 끝에서 2개만 출력하기 (LIMIT) 

mysql> SELECT * FROM topic WHERE author='egoing' ORDER BY id DESC;
+----+---------+----------------+---------------------+--------+-----------+
| id | title   | description    | created             | author | profile   |
+----+---------+----------------+---------------------+--------+-----------+
|  6 | MongoDB | MongoDB is ... | 2021-01-19 14:15:57 | egoing | developer |
|  3 | Oracle  | Oracle is ...  | 2021-01-19 14:12:57 | egoing | developer |
|  2 | MySQL   | MySQL is ...   | 2021-01-19 14:11:22 | egoing | developer |
|  1 | MySQL   | MySQL is ...   | 2021-01-19 14:11:08 | egoing | developer |
+----+---------+----------------+---------------------+--------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM topic WHERE author='egoing' ORDER BY id DESC LIMIT 2;
+----+---------+----------------+---------------------+--------+-----------+
| id | title   | description    | created             | author | profile   |
+----+---------+----------------+---------------------+--------+-----------+
|  6 | MongoDB | MongoDB is ... | 2021-01-19 14:15:57 | egoing | developer |
|  3 | Oracle  | Oracle is ...  | 2021-01-19 14:12:57 | egoing | developer |
+----+---------+----------------+---------------------+--------+-----------+
2 rows in set (0.00 sec)

 

11. SQL의 UPDATE 구문

 - id가 1인 값의 내용을 '' 공란으로 만들어주기 (내가 잘못 만든 값을 빈칸으로 만들어주려구..)

mysql> UPDATE topic SET title='', description='', created='', author='', profile=''
    -> WHERE id=1;
ERROR 1292 (22007): Incorrect datetime value: '' for column 'created' at row 1
# created 행은 타입이 datetime이라 공란('')은 안된다는 오류 발생.

mysql> UPDATE topic SET title='', description='', created=NOW(), author='', profile=''
    -> WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 |            |                   | 2021-01-19 14:34:19 |        |                           |
|  2 | MySQL      | MySQL is ...      | 2021-01-19 14:11:22 | egoing | developer                 |
|  3 | Oracle     | Oracle is ...     | 2021-01-19 14:12:57 | egoing | developer                 |
|  4 | SQL Server | SQL Server is ... | 2021-01-19 14:14:32 | duru   | data administrator        |
|  5 | PostgreSQL | PostgreSQL is ... | 2021-01-19 14:15:19 | taeho  | data scientist, developer |
|  6 | MongoDB    | MongoDB is ...    | 2021-01-19 14:15:57 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
6 rows in set (0.00 sec)

 

12. SQL의 DELETE 구문

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

mysql> SELECT * FROM topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  2 | MySQL      | MySQL is ...      | 2021-01-19 14:11:22 | egoing | developer                 |
|  3 | Oracle     | Oracle is ...     | 2021-01-19 14:12:57 | egoing | developer                 |
|  4 | SQL Server | SQL Server is ... | 2021-01-19 14:14:32 | duru   | data administrator        |
|  5 | PostgreSQL | PostgreSQL is ... | 2021-01-19 14:15:19 | taeho  | data scientist, developer |
|  6 | MongoDB    | MongoDB is ...    | 2021-01-19 14:15:57 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.00 sec)

 

13. 수업의 정상