Notice
Recent Posts
Recent Comments
Link
«   2024/10   »
일 월 화 수 목 금 토
1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31
Tags
more
Archives
Today
Total
관리 메뉴

공hannah부

데이터 베이스 - #4.CRUD 본문

공부/백엔드

데이터 베이스 - #4.CRUD

Hannah0226 2023. 3. 19. 00:19

CRUD란?

Create

Read

Update

Delete

- 이 중 Create,Read가 가장 중요 ( Update와 Delete는 없을 수도 있기 때문 ex. 회계록, 역사)

 

테이블 구조 확인하기

  • DESC 테이블 이름;
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)

 

CREATE

Create란? 테이블에 데이터를 추가하는 것

  • INSERT INTO 테이블 이름 (Field1이름, Field2이름, ...) VALUES('Field1에 넣을 내용','Field2에 넣을 내용', ...);
  • NOW() : 현재 시간
mysql> INSERT INTO topic (title,description,created,author,profile) VALUES('MySQL','MySQL is ...',NOW(),'egoing','developer');
Query OK, 1 row affected (0.01 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)

 

READ

READ란? 테이블에 저장된 데이터를 출력하는 것

 

- SELECT

  • 한 테이블 안의 모든 데이터 출력하기: SELECT * FROM 테이블 이름;
mysql> SELECT * FROM topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2023-03-18 23:19:57 | egoing | developer                 |
|  2 | ORACLE     | ORACLE is ...     | 2023-03-18 23:32:45 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2023-03-18 23:36:09 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ... | 2023-03-18 23:37:11 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2023-03-18 23:38:15 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.00 sec)

 

  • Field 몇 개만 출력: SELECT Field1이름, Field3이름, ... FROM 테이블 이름;
mysql> SELECT id,title,created,author FROM topic;
+----+------------+---------------------+--------+
| id | title      | created             | author |
+----+------------+---------------------+--------+
|  1 | MySQL      | 2023-03-18 23:19:57 | egoing |
|  2 | ORACLE     | 2023-03-18 23:32:45 | egoing |
|  3 | SQL Server | 2023-03-18 23:36:09 | duru   |
|  4 | PostgreSQL | 2023-03-18 23:37:11 | taeho  |
|  5 | MongoDB    | 2023-03-18 23:38:15 | egoing |
+----+------------+---------------------+--------+
5 rows in set (0.00 sec)

 

  • 특정 단어가 포함된 Field만 출력: SELECT FROM 테이블 이름  WHERE Field 이름='단어 ';
mysql> SELECT * FROM topic WHERE author='egoing';
+----+---------+----------------+---------------------+--------+-----------+
| id | title   | description    | created             | author | profile   |
+----+---------+----------------+---------------------+--------+-----------+
|  1 | MySQL   | MySQL is ...   | 2023-03-18 23:19:57 | egoing | developer |
|  2 | ORACLE  | ORACLE is ...  | 2023-03-18 23:32:45 | egoing | developer |
|  5 | MongoDB | MongoDB is ... | 2023-03-18 23:38:15 | egoing | developer |
+----+---------+----------------+---------------------+--------+-----------+
3 rows in set (0.00 sec)

 

  • 큰 순으로 정렬: SELECT FROM 테이블 이름  ORDER BY Field이름 DESC;
mysql> SELECT * FROM topic ORDER BY id DESC;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  5 | MongoDB    | MongoDB is ...    | 2023-03-18 23:38:15 | egoing | developer                 |
|  4 | PostgreSQL | PostgreSQL is ... | 2023-03-18 23:37:11 | taeho  | data scientist, developer |
|  3 | SQL Server | SQL Server is ... | 2023-03-18 23:36:09 | duru   | data administrator        |
|  2 | ORACLE     | ORACLE is ...     | 2023-03-18 23:32:45 | egoing | developer                 |
|  1 | MySQL      | MySQL is ...      | 2023-03-18 23:19:57 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM topic WHERE author='egoing' ORDER BY id DESC;
+----+---------+----------------+---------------------+--------+-----------+
| id | title   | description    | created             | author | profile   |
+----+---------+----------------+---------------------+--------+-----------+
|  5 | MongoDB | MongoDB is ... | 2023-03-18 23:38:15 | egoing | developer |
|  2 | ORACLE  | ORACLE is ...  | 2023-03-18 23:32:45 | egoing | developer |
|  1 | MySQL   | MySQL is ...   | 2023-03-18 23:19:57 | egoing | developer |
+----+---------+----------------+---------------------+--------+-----------+
3 rows in set (0.00 sec)

 

  • 출력하는 데이터 제한: SELECT FROM 테이블 이름 LIMIT 제한할 양(숫자);
mysql> SELECT * FROM topic LIMIT 2;
+----+--------+---------------+---------------------+--------+-----------+
| id | title  | description   | created             | author | profile   |
+----+--------+---------------+---------------------+--------+-----------+
|  1 | MySQL  | MySQL is ...  | 2023-03-18 23:19:57 | egoing | developer |
|  2 | ORACLE | ORACLE is ... | 2023-03-18 23:32:45 | egoing | developer |
+----+--------+---------------+---------------------+--------+-----------+
2 rows in set (0.00 sec)

 

UPDATE

  • 수정하기: UDATE 테이블이름 SET Field이름='수정할 내용'  WHERE Field이름 = 순서;
#수정 전
mysql> SELECT * FROM topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2023-03-18 23:19:57 | egoing | developer                 |
|  2 | ORACLE     | ORACLE is ...     | 2023-03-18 23:32:45 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2023-03-18 23:36:09 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ... | 2023-03-18 23:37:11 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2023-03-18 23:38:15 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.00 sec)

#수정
mysql> UPDATE topic SET description='Oracle is ~~',title='Oracle' WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#수정 후
mysql> SELECT * FROM topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2023-03-18 23:19:57 | egoing | developer                 |
|  2 | Oracle     | Oracle is ~~      | 2023-03-18 23:32:45 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2023-03-18 23:36:09 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ... | 2023-03-18 23:37:11 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2023-03-18 23:38:15 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.00 sec)

 

DELETE

  • 삭제하기: DELETE FROM 테이블이름 WHRER Field이름 = 순서;
#삭제 전
mysql> SELECT * FROM topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2023-03-18 23:19:57 | egoing | developer                 |
|  2 | Oracle     | Oracle is ~~~     | 2023-03-18 23:32:45 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2023-03-18 23:36:09 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ... | 2023-03-18 23:37:11 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2023-03-18 23:38:15 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.00 sec)

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

#삭제 후
mysql> SELECT * FROM topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2023-03-18 23:19:57 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2023-03-18 23:36:09 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ... | 2023-03-18 23:37:11 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2023-03-18 23:38:15 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
4 rows in set (0.00 sec)

리뷰

오늘은 데이터 다루기의 기본인 CRUD에 대해 배웠다.

데이터를 다루는 명령어들은 무궁무진하기 때문에 오늘은 기본적인 것들만 실습해 보았다. 나머지는 필요할 때마다 검색해 사용하면 되니 필요한 정보를 검색하고 활용하는 능력이 필수적임을 느꼈다. 또한 Update나 Delete를 할 경우 변경할 위치를 지정해주는 WHERE을 해주지 않으면 모두 변경 또는 삭제되기 때문에 꼭꼭 주의해 사용해야한다는 것을 알게되었다. 앞으로 웹개발을 함에 있어서 오늘 배운 내용들을 잘 활용해 보고 싶다!

2주차 수강 진도

 

'공부 > 백엔드' 카테고리의 다른 글

AWS에 데이터베이스 환경 만들기-AWS RDS  (0) 2023.05.05
AWS 서버 환경 만들기-AWS EC  (0) 2023.05.04
데이터 베이스 - #3.MySQL 테이블 생성  (0) 2023.03.17
데이터 베이스 - #2.MySQL  (0) 2023.03.11
데이터 베이스 - #0~1.DataBase 소개 및 MySQL 설치  (1) 2023.03.10
'공부/백엔드' Related Articles more