DB 생성 및 TABLE CRUD

* SELECT 가 가장 많이 사용되어 중요하다.

** DB의 명령어는 대소문자 구분 필수적이다.

---

DB 생성

//데이터베이스 생성
CREATE DATABASE opentutorials;

//데이터베이스 삭제
DROP DATABASE opentutorials;

//데이터베이스 사용
USE opentutorials;

---

CREATE

 

//AUTO_INCREMENT : id 자동증가 (값 구분을 위해)
//PRIMARY KEY(id) : id는 값의 identity 보장
CREATE TABLE topic(
    -> id INT(11) NOT NULL AUTO_INCREMENT, title VARCHAR(100) NOT NULL, description TEXT NULL, created DATETIME NOT NULL, author VARCHAR(30) NULL, profile VARCHAR(100) NULL, PRIMARY KEY(id));
>> 
Query OK, 0 rows affected, 1 warning (0.12 sec)

---

READ 

 

//데이터베이스 보기
SHOW DATABASE;
>>
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| opentutorials      |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
8 rows in set (0.01 sec)


//테이블 보기
SHOW TABLES;
>>
+-------------------------+
| Tables_in_opentutorials |
+-------------------------+
| topic                   |
+-------------------------+
1 row in set (0.01 sec)


//topic 테이블 값 보기
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(30)  | YES  |     | NULL    |                |
| profile     | varchar(100) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

 

// topic 테이블에 데이터 추가하기 (CREATE)
INSERT INTO topic (title,description,created,author,profile) VALUES('MySQL','MySQL is ...', now(), 'egoing','developer');
>>
Query OK, 1 row affected (0.01 sec)


//topic 에서 가져오기
SELECT * FROM topic; 
>>아래와 같이 정상적으로 추가되었음을 알 수 있다.
+----+-------+--------------+---------------------+--------+-----------+
| id | title | description  | created             | author | profile   |
+----+-------+--------------+---------------------+--------+-----------+
|  1 | MySQL | MySQL is ... | 2022-03-04 17:24:26 | egoing | developer |
+----+-------+--------------+---------------------+--------+-----------+
1 row in set (0.00 sec)


//topic에서 특정 값만 가져오기
SELECT id, title, created, author FROM topic;
>>
+----+------------+---------------------+--------+
| id | title      | created             | author |
+----+------------+---------------------+--------+
|  1 | MySQL      | 2022-03-04 17:24:26 | egoing |
|  2 | Oracle     | 2022-03-04 17:31:37 | egoing |
|  3 | SQL Server | 2022-03-04 17:32:55 | duru   |
|  4 | PostgreSQL | 2022-03-04 17:33:53 | taeho  |
|  5 | MongoDB    | 2022-03-04 17:34:31 | egoing |
+----+------------+---------------------+--------+
5 rows in set (0.00 sec)


//author 가 egoing인 값들만 가져오기 (위치조건 WHERE a = b)
SELECT id, title, created, author FROM topic WHERE author='egoing';
>>
+----+---------+---------------------+--------+
| id | title   | created             | author |
+----+---------+---------------------+--------+
|  1 | MySQL   | 2022-03-04 17:24:26 | egoing |
|  2 | Oracle  | 2022-03-04 17:31:37 | egoing |
|  5 | MongoDB | 2022-03-04 17:34:31 | egoing |
+----+---------+---------------------+--------+
3 rows in set (0.00 sec)


//정렬 순서 (ORDER BY 정렬기준 순서(정, 역))
SELECT id, title, created, author FROM topic WHERE author='egoing' ORDER BY id DESC;
>> id DESC : 
+----+---------+---------------------+--------+
| id | title   | created             | author |
+----+---------+---------------------+--------+
|  5 | MongoDB | 2022-03-04 17:34:31 | egoing |
|  2 | Oracle  | 2022-03-04 17:31:37 | egoing |
|  1 | MySQL   | 2022-03-04 17:24:26 | egoing |
+----+---------+---------------------+--------+
3 rows in set (0.00 sec)


//제한 걸기
SELECT id, title, created, author FROM topic WHERE author='egoing' ORDER BY id DESC LIMIT 2;
>>
+----+---------+---------------------+--------+
| id | title   | created             | author |
+----+---------+---------------------+--------+
|  5 | MongoDB | 2022-03-04 17:34:31 | egoing |
|  2 | Oracle  | 2022-03-04 17:31:37 | egoing |
+----+---------+---------------------+--------+
2 rows in set (0.00 sec)

---

UPDATE 

- WHERE가 빠지면 혼란이... 무조건 체크

//값 수정하기
UPDATE topic SET description = 'ORACLE is ...', title = 'ORACLE' WHERE id=2;
>> description과 title 업데이트, 조건 : id = 2
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT * FROM topic;
>>id가 2인 항목이 바뀌어져 있음을 알 수 있다.
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2022-03-04 17:24:26 | egoing | developer                 |
|  2 | ORACLE     | ORACLE is ...     | 2022-03-04 17:31:37 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2022-03-04 17:32:55 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ... | 2022-03-04 17:33:53 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2022-03-04 17:34:31 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.00 sec)

---

DELETE 

 

- WHERE가 빠지면 혼란이... 무조건 체크

//값 삭제하기
delete FROM topic WHERE id = 5;
>> 
Query OK, 1 row affected (0.01 sec)

SELECT * FROM topic;
>> id가 5번인 항목 삭제되어 있음을 알 수 있다.
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2022-03-04 17:24:26 | egoing | developer                 |
|  2 | ORACLE     | ORACLE is ...     | 2022-03-04 17:31:37 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2022-03-04 17:32:55 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ... | 2022-03-04 17:33:53 | taeho  | data scientist, developer |
+----+------------+-------------------+---------------------+--------+---------------------------+
4 rows in set (0.00 sec)