🌵 SQL(Structured Query Language)

📌 테이블 생성해보기

Query : 데이터베이스에게 요청

google sesarch | create table in mysql cheat sheet

google sesarch | mysql datatype number
https://www.mysqltutorial.org/mysql-data-types.aspx

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

id INT(11) NOT NULL AUTO_INCREMENT : id 컬럼, int형으로 11자리까지 노출, 공백 허용 X, 자동으로 값 증가
title VARCHAR(100) NOT NULL : title 컬럼, VARCHAR형 100자리까지 생성, 공백 허용 X
PRIMARY KEY(id) : 메인키는 id, 중복 불가능

📌 CRUD

CREATE, READ, UPDATE, DELETE

📌 topic테이블 COL 구성요소 보기

테이블 열기

MariaDB [opentutorials]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| opentutorials      |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.014 sec)

MariaDB [(none)]> USE opentutorials;

MariaDB [opentutorials]> SHOW TABLES;
+-------------------------+
| Tables_in_opentutorials |
+-------------------------+
| topic                   |
+-------------------------+
1 row in set (0.001 sec)

topic 테이블 COL 구성요소

MariaDB [opentutorials]> DESC topic;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | 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.046 sec)

📌 ROW 추가하기, 읽기

ROW 추가

google sesarch | mysql create row

MariaDB [opentutorials]> INSERT INTO topic (title, description, created, author, profile) VALUES('SQL Server', 'SQL Server is ...', NOW(), 'duru', 'data administrator');
Query OK, 1 row affected (0.004 sec)

MariaDB [opentutorials]> INSERT INTO topic (title, description, created, author, profile) VALUES('PostgreSQL', 'PostgreSQL is ...', NOW(), 'taeho', 'data scientist, developer');
Query OK, 1 row affected (0.003 sec)

MariaDB [opentutorials]> INSERT INTO topic (title, description, created, author, profile) VALUES('MongoDB', 'MongoDB is ...', NOW(), 'egoing', 'developer');
Query OK, 1 row affected (0.003 sec)

INSERT INTO topic (title, description, created, author, profile) VALUES('MongoDB', 'MongoDB is ...', NOW(), 'egoing', 'developer'); Query OK, 1 row affected (0.003 sec) : COL순서에 맞게 ROW정보 추가

ROW 읽기

google sesarch | how to read row in mysql

MariaDB [opentutorials]> SELECT * FROM topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2022-02-28 22:05:35 | egoing | developer                 |
|  2 | ORACLE     | ORACLE is ...     | 2022-02-28 22:08:27 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2022-02-28 22:09:24 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ... | 2022-02-28 22:10:17 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2022-02-28 22:11:05 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+