mariadb를 이용한 DBMS 구축

table 생성

MariaDB [khacademy]> create table kh_mem_const(
    -> no int not null primary key,
    -> id varchar(20) unique,
    -> pass varchar(50) default '1234',
    -> name varchar(20),
    -> age int,
    -> reg_date datetime not null
    -> );

 

table 생성 확인 및 내용 확인

MariaDB [khacademy]> show tables;
+---------------------+
| Tables_in_khacademy |
+---------------------+
| dept                |
| emp                 |
| freeboard           |
| kh_mem              |
| kh_mem_const        |
| student             |
| t1                  |
| test                |
+---------------------+
8 rows in set (0.000 sec)

MariaDB [khacademy]> desc kh_mem_const;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| no       | int(11)     | NO   | PRI | NULL    |       |
| id       | varchar(20) | YES  | UNI | NULL    |       |
| pass     | varchar(50) | YES  |     | 1234    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
| reg_date | datetime    | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.001 sec)

--제약조건 auto_increment 추가
MariaDB [khacademy]> alter table kh_mem_const modify no int auto_increment;
Query OK, 0 rows affected (0.010 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [khacademy]> desc kh_mem_const;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| no       | int(11)     | NO   | PRI | NULL    | auto_increment |
| id       | varchar(20) | YES  | UNI | NULL    |                |
| pass     | varchar(50) | YES  |     | 1234    |                |
| name     | varchar(20) | YES  |     | NULL    |                |
| age      | int(11)     | YES  |     | NULL    |                |
| reg_date | datetime    | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)

 

table insert

MariaDB [khacademy]> insert into kh_mem_const values(
    -> NULL,'jiwon','qwer1234','배지원',24,now());
Query OK, 1 row affected (0.001 sec)
-- 모든 row 추가
MariaDB [khacademy]> select * from kh_mem_const;
+----+-------+----------+-----------+------+---------------------+
| no | id    | pass     | name      | age  | reg_date            |
+----+-------+----------+-----------+------+---------------------+
|  1 | jiwon | qwer1234 | 배지원    |   24 | 2024-03-18 17:36:02 |
+----+-------+----------+-----------+------+---------------------+
1 row in set (0.000 sec)
-- password 없이 추가
MariaDB [khacademy]> insert into kh_mem_const(no,id,name,age,reg_date) 
    -> values(NULL,'jiwon1','배지원',24,now());
Query OK, 1 row affected (0.001 sec)
MariaDB [khacademy]> select * from kh_mem_const;
+----+--------+----------+-----------+------+---------------------+
| no | id     | pass     | name      | age  | reg_date            |
+----+--------+----------+-----------+------+---------------------+
|  1 | jiwon  | qwer1234 | 배지원    |   24 | 2024-03-18 17:36:02 |
|  2 | jiwon1 | 1234     | 배지원    |   24 | 2024-03-18 17:39:19 | -- default 값이 들어감
+----+--------+----------+-----------+------+---------------------+
2 rows in set (0.000 sec)

table update

--id 변경
MariaDB [khacademy]> update kh_mem_const set id='JW' where no=1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [khacademy]> select * from kh_mem_const;
+----+--------+----------+-----------+------+---------------------+
| no | id     | pass     | name      | age  | reg_date            |
+----+--------+----------+-----------+------+---------------------+
|  1 | JW     | qwer1234 | 배지원    |   24 | 2024-03-18 17:36:02 |
|  2 | jiwon1 | 1234     | 배지원    |   24 | 2024-03-18 17:39:19 |
+----+--------+----------+-----------+------+---------------------+
2 rows in set (0.000 sec)

--name 변경
MariaDB [khacademy]> update kh_mem_const set name='홍길동' where no=2;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [khacademy]> select * from kh_mem_const;
+----+--------+----------+-----------+------+---------------------+
| no | id     | pass     | name      | age  | reg_date            |
+----+--------+----------+-----------+------+---------------------+
|  1 | JW     | qwer1234 | 배지원    |   24 | 2024-03-18 17:36:02 |
|  2 | jiwon1 | 1234     | 홍길동    |   24 | 2024-03-18 17:39:19 |
+----+--------+----------+-----------+------+---------------------+
2 rows in set (0.000 sec)

table delete

--배지원 Column 삭제
MariaDB [khacademy]> delete from kh_mem_const where name='배지원';
Query OK, 1 row affected (0.005 sec)

MariaDB [khacademy]> select * from kh_mem_const
    -> ;
+----+--------+------+-----------+------+---------------------+
| no | id     | pass | name      | age  | reg_date            |
+----+--------+------+-----------+------+---------------------+
|  2 | jiwon1 | 1234 | 홍길동    |   24 | 2024-03-18 17:39:19 |
+----+--------+------+-----------+------+---------------------+
1 row in set (0.000 sec)

MariaDB [khacademy]> 

--홍길동 Column 삭제
MariaDB [khacademy]> delete from kh_mem_const where name='홍길동';
Query OK, 1 row affected (0.001 sec)

MariaDB [khacademy]> select * from kh_mem_const;
Empty set (0.000 sec)

Auto_Increment

--제약조건 auto_increment가 있기 존재하므로 새로 row를 추가할 때 숫자가 초기화 안됌
MariaDB [khacademy]> insert into kh_mem_const values(NULL,'test','qwer1234','테스터',20,now());
Query OK, 1 row affected (0.002 sec)

MariaDB [khacademy]> insert into kh_mem_const values(NULL,'test02','qwer1234','테스터2',21,now());
Query OK, 1 row affected (0.001 sec)

MariaDB [khacademy]> select * from kh_mem_const;
+----+--------+----------+------------+------+---------------------+
| no | id     | pass     | name       | age  | reg_date            |
+----+--------+----------+------------+------+---------------------+
|  3 | test   | qwer1234 | 테스터     |   20 | 2024-03-19 08:54:57 |
|  4 | test02 | qwer1234 | 테스터2    |   21 | 2024-03-19 08:55:14 |
+----+--------+----------+------------+------+---------------------+
2 rows in set (0.000 sec)

--내용 초기화 후 명령어 입력
MariaDB [khacademy]> delete from kh_mem_const;
Query OK, 2 rows affected (0.001 sec)

MariaDB [khacademy]> select * from kh_mem_const;
Empty set (0.000 sec)

MariaDB [khacademy]> truncate table kh_mem_const;
Query OK, 0 rows affected (0.006 sec)

--다시 내용 입력하여 초기화 됐는지 확인
MariaDB [khacademy]> insert into kh_mem_const values(NULL,'test03','qwer1234','테스터3',22,now());
Query OK, 1 row affected (0.001 sec)

MariaDB [khacademy]> insert into kh_mem_const values(NULL,'test04','qwer1234','테스터4',23,now());
Query OK, 1 row affected (0.001 sec)

MariaDB [khacademy]> select * from kh_mem_const;
+----+--------+----------+------------+------+---------------------+
| no | id     | pass     | name       | age  | reg_date            |
+----+--------+----------+------------+------+---------------------+
|  1 | test03 | qwer1234 | 테스터3    |   22 | 2024-03-19 08:58:06 |
|  2 | test04 | qwer1234 | 테스터4    |   23 | 2024-03-19 08:58:22 |
+----+--------+----------+------------+------+---------------------+
2 rows in set (0.000 sec)