MySQL 기초

// 서버 시작
mysql.server start

// 설정
mysql_secure_installation

// 접속
mysql -uroot -p

// 데이터베이스 생성
reate database node242 default character set utf8;

// 스키마 선택
use node242

// 테이블 추가
mysql> create table topic(
    -> id int(11) not null auto_increment, // 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))

// 테이블 목록
show tables

// 필드 출력
desc topic;

// 내용 추가(C)
insert into topic(title, description, created, author, profile) values('javascript','javascript is...', now(), 'KIS','developer');
insert into topic(title, description, created, author, profile) values('C++','C++ is...', now(), 'KIS','developer');
insert into topic(title, description, created, author, profile) values('Python','Python is...', now(), 'Lee','teacher');
insert into topic(title, description, created, author, profile) values('Nodejs','Nodejs is...', now(), 'Kim','developer');
insert into topic(title, description, created, author, profile) values('Mysql','Mysql is...', now(), 'Park','DBmanager');

// 내용 확인
select * from topic;

+----+------------+------------------+---------------------+--------+-----------+
| id | title      | description      | created             | author | profile   |
+----+------------+------------------+---------------------+--------+-----------+
|  1 | javascript | javascript is... | 2022-05-20 16:52:30 | KIS    | developer |
|  2 | C++        | C++ is...        | 2022-05-20 16:56:54 | KIS    | developer |
|  3 | Python     | Python is...     | 2022-05-20 16:57:39 | Lee    | teacher   |
|  4 | Nodejs     | Nodejs is...     | 2022-05-20 16:58:44 | Kim    | developer |
|  5 | Mysql      | Mysql is...      | 2022-05-20 16:59:32 | Park   | DBmanager |
+----+------------+------------------+---------------------+--------+-----------+

// 조건문
select * from topic where profile="developer";
+----+------------+------------------+---------------------+--------+-----------+
| id | title      | description      | created             | author | profile   |
+----+------------+------------------+---------------------+--------+-----------+
|  1 | javascript | javascript is... | 2022-05-20 16:52:30 | KIS    | developer |
|  2 | C++        | C++ is...        | 2022-05-20 16:56:54 | KIS    | developer |
|  4 | Nodejs     | Nodejs is...     | 2022-05-20 16:58:44 | Kim    | developer |
+----+------------+------------------+---------------------+--------+-----------+

// 정렬
// 내림차순 
select * from topic order by id desc
+----+------------+------------------+---------------------+--------+-----------+
| id | title      | description      | created             | author | profile   |
+----+------------+------------------+---------------------+--------+-----------+
|  5 | Mysql      | Mysql is...      | 2022-05-20 16:59:32 | Park   | DBmanager |
|  4 | Nodejs     | Nodejs is...     | 2022-05-20 16:58:44 | Kim    | developer |
|  3 | Python     | Python is...     | 2022-05-20 16:57:39 | Lee    | teacher   |
|  2 | C++        | C++ is...        | 2022-05-20 16:56:54 | KIS    | developer |
|  1 | javascript | javascript is... | 2022-05-20 16:52:30 | KIS    | developer |
+----+------------+------------------+---------------------+--------+-----------+

select * from topic order by title;
+----+------------+------------------+---------------------+--------+-----------+
| id | title      | description      | created             | author | profile   |
+----+------------+------------------+---------------------+--------+-----------+
|  2 | C++        | C++ is...        | 2022-05-20 16:56:54 | KIS    | developer |
|  1 | javascript | javascript is... | 2022-05-20 16:52:30 | KIS    | developer |
|  5 | Mysql      | Mysql is...      | 2022-05-20 16:59:32 | Park   | DBmanager |
|  4 | Nodejs     | Nodejs is...     | 2022-05-20 16:58:44 | Kim    | developer |
|  3 | Python     | Python is...     | 2022-05-20 16:57:39 | Lee    | teacher   |
+----+------------+------------------+---------------------+--------+-----------+

// 출력하는 데이터 제한
select * from topic order by id asc limit 2; // 2개만 보여주겠다
+----+------------+------------------+---------------------+--------+-----------+
| id | title      | description      | created             | author | profile   |
+----+------------+------------------+---------------------+--------+-----------+
|  1 | javascript | javascript is... | 2022-05-20 16:52:30 | KIS    | developer |
|  2 | C++        | C++ is...        | 2022-05-20 16:56:54 | KIS    | developer |
+----+------------+------------------+---------------------+--------+-----------+

// 수정 update
update topic set description ='database is...' where id=5;
+----+------------+------------------+---------------------+--------+-----------+
| id | title      | description      | created             | author | profile   |
+----+------------+------------------+---------------------+--------+-----------+
|  1 | javascript | javascript is... | 2022-05-20 16:52:30 | KIS    | developer |
|  2 | C++        | C++ is...        | 2022-05-20 16:56:54 | KIS    | developer |
|  3 | Python     | Python is...     | 2022-05-20 16:57:39 | Lee    | teacher   |
|  4 | Nodejs     | Nodejs is...     | 2022-05-20 16:58:44 | Kim    | developer |
|  5 | Mysql      | database is...   | 2022-05-20 16:59:32 | Park   | DBmanager |
+----+------------+------------------+---------------------+--------+-----------+

update topic set title ='Database' where id=5;
+----+------------+------------------+---------------------+--------+-----------+
| id | title      | description      | created             | author | profile   |
+----+------------+------------------+---------------------+--------+-----------+
|  1 | javascript | javascript is... | 2022-05-20 16:52:30 | KIS    | developer |
|  2 | C++        | C++ is...        | 2022-05-20 16:56:54 | KIS    | developer |
|  3 | Python     | Python is...     | 2022-05-20 16:57:39 | Lee    | teacher   |
|  4 | Nodejs     | Nodejs is...     | 2022-05-20 16:58:44 | Kim    | developer |
|  5 | Database   | database is...   | 2022-05-20 16:59:32 | Park   | DBmanager |
+----+------------+------------------+---------------------+--------+-----------+

//삭제 delete
delete from topic where id=5;
+----+------------+------------------+---------------------+--------+-----------+
| id | title      | description      | created             | author | profile   |
+----+------------+------------------+---------------------+--------+-----------+
|  1 | javascript | javascript is... | 2022-05-20 16:52:30 | KIS    | developer |
|  2 | C++        | C++ is...        | 2022-05-20 16:56:54 | KIS    | developer |
|  3 | Python     | Python is...     | 2022-05-20 16:57:39 | Lee    | teacher   |
|  4 | Nodejs     | Nodejs is...     | 2022-05-20 16:58:44 | Kim    | developer |
+----+------------+------------------+---------------------+--------+-----------+