SQL)2일차

UPDATE 사용하기

MariaDB [opentutorials]> select * from topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MYSQL      | mysql is ...      | 2022-02-20 16:59:56 | egoing | developer                 |
|  2 | oracle     | oracle is         | 2022-02-20 17:28:03 | egoing | developer                 |
|  3 | sql server | sql server is ... | 2022-02-20 17:29:14 | duru   | data adminnistrator       |
|  4 | postgreSQL | postgreSQL is ... | 2022-02-20 17:30:04 | taeho  | data scientist, developer |
|  5 | mongoDB    | mongoDB is ...    | 2022-02-20 17:31:23 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.704 sec)

MariaDB [opentutorials]> UPDATE topic SET description='oracle is...', title='oracle' where id=2;

MariaDB [opentutorials]> select * from topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MYSQL      | mysql is ...      | 2022-02-20 16:59:56 | egoing | developer                 |
|  2 | oracle     | oracle is...      | 2022-02-20 17:28:03 | egoing | developer                 |
|  3 | sql server | sql server is ... | 2022-02-20 17:29:14 | duru   | data adminnistrator       |
|  4 | postgreSQL | postgreSQL is ... | 2022-02-20 17:30:04 | taeho  | data scientist, developer |
|  5 | mongoDB    | mongoDB is ...    | 2022-02-20 17:31:23 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.000 sec)

 

 

DELETE

MariaDB [opentutorials]> select * from topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MYSQL      | mysql is ...      | 2022-02-20 16:59:56 | egoing | developer                 |
|  2 | oracle     | oracle is...      | 2022-02-20 17:28:03 | egoing | developer                 |
|  3 | sql server | sql server is ... | 2022-02-20 17:29:14 | duru   | data adminnistrator       |
|  4 | postgreSQL | postgreSQL is ... | 2022-02-20 17:30:04 | taeho  | data scientist, developer |
|  5 | mongoDB    | mongoDB is ...    | 2022-02-20 17:31:23 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.001 sec)

MariaDB [opentutorials]> DELETE FROM topic WHERE id=5;
Query OK, 1 row affected (0.005 sec)

MariaDB [opentutorials]> select * from topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MYSQL      | mysql is ...      | 2022-02-20 16:59:56 | egoing | developer                 |
|  2 | oracle     | oracle is...      | 2022-02-20 17:28:03 | egoing | developer                 |
|  3 | sql server | sql server is ... | 2022-02-20 17:29:14 | duru   | data adminnistrator       |
|  4 | postgreSQL | postgreSQL is ... | 2022-02-20 17:30:04 | taeho  | data scientist, developer |
+----+------------+-------------------+---------------------+--------+---------------------------+
4 rows in set (0.000 sec)

 

1. 관계형 데이터베이스

 

1) 관계형데이터베이스가 왜 필요한가.?

    데이터는 분산되더라도 , 볼때는 직관적으로 볼수있게하.

    중복된 내용이 있을경우 여러개 문제점을 야기함.

    (복잡한 데이터가 1000만번 중복이라면 경재적손해 , 수정조차 어려움 ,같은데이터 확신 어려움 , 같은이름이 여러명       일수도 있다. )

   *해결방안

    TOPIC 테이블을 아래와같이 나눠준다. 

    AUTOR 값을 변경하면 TOPIC 까지 영향을준다.

    단점 : 직관적으로 볼수가 없음.

 

2)데이터 분리하기

 

테이블 이름 바꾸기.

ariaDB [opentutorials]> show TABLES;
+-------------------------+
| Tables_in_opentutorials |
+-------------------------+
| author                  |
| topic1                  |
| topic_backup            |
+-------------------------+
3 rows in set (0.001 sec)

MariaDB [opentutorials]> RENAME TABLE topic1 TO topic;
Query OK, 0 rows affected (0.022 sec)

MariaDB [opentutorials]> show TABLES;
+-------------------------+
| Tables_in_opentutorials |
+-------------------------+
| author                  |
| topic                   |
| topic_backup            |
+-------------------------+
3 rows in set (0.001 sec)

join

 

방법1

MariaDB [opentutorials]> SELECT*FROM topic LEFT JOIN author ON topic.author_id = author.id;

방법2 : 원하는 칼럼만 가져올수있음

ariaDB [opentutorials]> SELECT topic.id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id = author.id;

MariaDB [opentutorials]> SELECT*FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
| id | title      | description       | created             | author_id | id   | name   | profile                   |
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
|  1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 |         1 |    1 | egoing | developer                 |
|  2 | Oracle     | Oracle is ...     | 2018-01-03 13:01:10 |         1 |    1 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 |         2 |    2 | duru   | database administrator    |
|  4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 |         3 |    3 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2018-01-30 12:31:03 |         1 |    1 | egoing | developer                 |
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
5 rows in set (0.009 sec)

MariaDB [opentutorials]> SELECT topic.id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | name   | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 | egoing | developer                 |
|  2 | Oracle     | Oracle is ...     | 2018-01-03 13:01:10 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 | duru   | database administrator    |
|  4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2018-01-30 12:31:03 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.004 sec)

 

DATABASE SERVER