**Create, Read, Update, Delete
*Create
- 데이터를 생성해거 데이터 베이스에 넣는 작업.
- INSERT INTO [테이블명] ([컬럼1], [컬럼2],...) VALUES ([값1], [값2],...);
mysql> INSERT INTO users (name, age, married, comment) VALUES ('zero',24,0,'자기소개');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO users (name, age, married, comment) VALUES ('nero',31,1,'자기소개2');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO nodejs.comments (commenter, comment) VALUES (1,'안녕하세요. zero의 댓글입니다.');
Query OK, 1 row affected (0.01 sec)
*Read
- 데이터베이스에 있는 데이터를 조회하는 작업
- SELECT * FROM [테이블명]; : 테이블의 모든 데이터 조회.
mysql> SELECT * FROM nodejs.users;
+----+------+-----+---------+-------------------------------------------------------+---------------------+
| id | name | age | married | comment | created_at |
+----+------+-----+---------+-------------------------------------------------------+---------------------+
| 1 | zero | 24 | 0 | 안녕하세요. 간단히 MySQL 사용 방법에 대해 알아봅시다. | 2021-09-17 17:14:40 |
| 2 | nero | 31 | 1 | 안녕하세요. zero친구 nero입니다. | 2021-09-17 17:15:03 |
+----+------+-----+---------+-------------------------------------------------------+---------------------+
2 rows in set (0.00 sec)
//특정 컬럼만 조회
-SELECT [컬럼명],[컬럼명],.. FROM [테이블명];
mysql> SELECT name, married FROM nodejs.users;
+------+---------+
| name | married |
+------+---------+
| zero | 0 |
| nero | 1 |
+------+---------+
2 rows in set (0.00 sec)
//특정 조건을 가진 데이터만 조회하기
-WHERE절을 사용 : AND, OR 등으로 조건부여
mysql> SELECT name, married FROM nodejs.users WHERE married = 1 AND age > 30 ;
+------+---------+
| name | married |
+------+---------+
| nero | 1 |
+------+---------+
1 row in set (0.00 sec)
mysql> SELECT name, married FROM nodejs.users WHERE married = 0 OR age > 30 ;
+------+---------+
| name | married |
+------+---------+
| zero | 0 |
| nero | 1 |
+------+---------+
2 rows in set (0.00 sec)
//정렬하기
-ORDER BY [칼럼명] [DESC\ASC]
-DESC : 내림차순
-ASC : 오름차순
mysql> SELECT id, name FROM nodejs.users ORDER BY age DESC;
+----+------+
| id | name |
+----+------+
| 2 | nero |
| 1 | zero |
+----+------+
2 rows in set (0.00 sec)
//조회할 로우 개수 설정
-LIMIT [숫자]
mysql> SELECT id, name FROM nodejs.users ORDER BY age LIMIT 1;
+----+------+
| id | name |
+----+------+
| 1 | zero |
+----+------+
1 row in set (0.00 sec)
//로우 중간부터 조회하기
-OFFSET [건너뛸 숫자]
-페이지네이션기능 구현시 유용
mysql> SELECT id, name FROM nodejs.users ORDER BY age LIMIT 1 OFFSET 1;
+----+------+
| id | name |
+----+------+
| 2 | nero |
+----+------+
1 row in set (0.00 sec)
*Update
- 데이터베이스에 있는 데이터를 수정하는 작업.
- UPDATE [테이블명] SET [컬럼명=바꿀 값] WHERE [조건];
mysql> UPDATE nodejs.users SET comment ='바꿀내용' WHERE id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
*Delete
- 데이터베이스에 있는 데이터를 삭제하는 작업
- DELETE FROM [테이블명] WHERE [조건];
mysql> DELETE FROM nodejs.users WHERE id = 2;
Query OK, 1 row affected (0.01 sec)
