SQL 기초

🔖 데이터베이스 생성

CREATE DATABASE opentutorials;

🔖 데이터베이스 사용

USE opentutorials;

🔖 테이블 생성

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));

🔖 테이블 구성요소 살펴보기

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    |                |
+-------------+--------------+------+-----+---------+----------------+

🔖 테이블에 데이터 추가

INSERT INTO topic (title,description,created,author,profile) VALUES ('MySQL','MySQL is ...',NOW(),'egoing','developer');

🔖 테이블 모든 데이터 읽기

SELECT * FROM topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2024-04-09 06:22:04 | egoing | developer                 |
|  2 | ORACLE     | ORACLE is ...     | 2024-04-09 06:23:50 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2024-04-09 06:24:44 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ... | 2024-04-09 06:25:27 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2024-04-09 06:25:57 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.00 sec)

🔖 원하는 컬럼만 읽기

SELECT id,title,author FROM topic;
+----+------------+--------+
| id | title      | author |
+----+------------+--------+
|  1 | MySQL      | egoing |
|  2 | ORACLE     | egoing |
|  3 | SQL Server | duru   |
|  4 | PostgreSQL | taeho  |
|  5 | MongoDB    | egoing |
+----+------------+--------+

🔖 기본 조건절 사용해서 읽기

SELECT id,title,author FROM topic WHERE author='egoing';
+----+---------+--------+
| id | title   | author |
+----+---------+--------+
|  1 | MySQL   | egoing |
|  2 | ORACLE  | egoing |
|  5 | MongoDB | egoing |
+----+---------+--------+

🔖 정렬해서 읽기 (기본은 ASC=오름차순)

SELECT id,title,author FROM topic WHERE author='egoing' ORDER BY id DESC;
+----+---------+--------+
| id | title   | author |
+----+---------+--------+
|  5 | MongoDB | egoing |
|  2 | ORACLE  | egoing |
|  1 | MySQL   | egoing |
+----+---------+--------+

🔖 갯수 제한해서 읽기

 SELECT id,title,author FROM topic WHERE author='egoing'ORDER BY id DESC LIMIT 2;
+----+---------+--------+
| id | title   | author |
+----+---------+--------+
|  5 | MongoDB | egoing |
|  2 | ORACLE  | egoing |
+----+---------+--------+

🔖 데이터 수정하기(업데이트하기)

UPDATE topic SET description='Oracle is ...', title='Oracle' WHERE id=2

SELECT * FROM topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2024-04-09 06:22:04 | egoing | developer                 |
|  2 | Oracle     | Oracle is ...     | 2024-04-09 06:23:50 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2024-04-09 06:24:44 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ... | 2024-04-09 06:25:27 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2024-04-09 06:25:57 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+

🔖 데이터 삭제하기

DELETE FROM topic WHERE id=5;

SELECT * FROM topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2024-04-09 06:22:04 | egoing | developer                 |
|  2 | Oracle     | Oracle is ...     | 2024-04-09 06:23:50 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2024-04-09 06:24:44 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ... | 2024-04-09 06:25:27 | taeho  | data scientist, developer |
+----+------------+-------------------+---------------------+--------+---------------------------+