MariaDB 기본실습(입력/수정/삭제)

1. 데이터베이스 생성

MariaDB [(none)]> create database kmj;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> use kmj;
Database changed
MariaDB [kmj]> create table member(no int, name varchar(20), class varchar(10)); 
Query OK, 0 rows affected (0.005 sec)

MariaDB [kmj]> show databases;
+--------------------+
| Database           |
+--------------------+
| WebTest            |
| information_schema |
| khacademy          |
| kmj                |
| mysql              |
| naver_db           |
| performance_schema |
+--------------------+
7 rows in set (0.001 sec)

 

2. 테이블 생성 (테이블명 : member, column : no, name, class)

MariaDB [kmj]> create table member(no int, name varchar(20), class varchar(10)); 
Query OK, 0 rows affected (0.005 sec)

MariaDB [kmj]> desc member;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| class | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

 

3. 테이블에 데이터 추가

MariaDB [kmj]> insert into member values(1,'nenjin','G');
Query OK, 1 row affected (0.001 sec)

MariaDB [kmj]> insert into member values(2,'yang','F');
Query OK, 1 row affected (0.001 sec)

MariaDB [kmj]> insert into member values(2,'baebae','E');
Query OK, 1 row affected (0.001 sec)

MariaDB [kmj]> select * from member;
+------+--------+-------+
| no   | name   | class |
+------+--------+-------+
|    1 | nenjin | G     |
|    2 | yang   | F     |
|    2 | baebae | E     |
+------+--------+-------+
3 rows in set (0.000 sec)

 

4. age column 추가하고 값 넣기

MariaDB [kmj]> alter table member add age int after name;
Query OK, 0 rows affected (0.004 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [kmj]> select * from member;
+------+--------+------+-------+
| no   | name   | age  | class |
+------+--------+------+-------+
|    1 | nenjin | NULL | G     |
|    2 | yang   | NULL | F     |
|    2 | baebae | NULL | E     |
+------+--------+------+-------+
3 rows in set (0.001 sec)

MariaDB [kmj]> update member set age=15 where name='nenjin';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [kmj]> update member set age=35 where name='yang';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [kmj]> update member set age=20 where name='baebae';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [kmj]> select * from member;
+------+--------+------+-------+
| no   | name   | age  | class |
+------+--------+------+-------+
|    1 | nenjin |   15 | G     |
|    2 | yang   |   35 | F     |
|    2 | baebae |   20 | E     |
+------+--------+------+-------+
3 rows in set (0.000 sec)

 

5. baebae의 no 값 수정하기

MariaDB [kmj]> update member set no=3 where class='E';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [kmj]> select * from member;
+------+--------+------+-------+
| no   | name   | age  | class |
+------+--------+------+-------+
|    1 | nenjin |   15 | G     |
|    2 | yang   |   35 | F     |
|    3 | baebae |   20 | E     |
+------+--------+------+-------+
3 rows in set (0.000 sec)

 

6. no.3의 행 삭제

MariaDB [kmj]> delete from member where no=3;
Query OK, 1 row affected (0.001 sec)

MariaDB [kmj]> select * from member;
+------+--------+------+-------+
| no   | name   | age  | class |
+------+--------+------+-------+
|    1 | nenjin |   15 | G     |
|    2 | yang   |   35 | F     |
+------+--------+------+-------+
2 rows in set (0.000 sec)

 

7. no 열 삭제

MariaDB [kmj]> alter table member drop no;
Query OK, 0 rows affected (0.002 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [kmj]> select * from member;
+--------+------+-------+
| name   | age  | class |
+--------+------+-------+
| nenjin |   15 | G     |
| yang   |   35 | F     |
+--------+------+-------+
2 rows in set (0.001 sec)

 

8. 나이 15살만 이름 출력

MariaDB [kmj]> select name from member where age=15;
+--------+
| name   |
+--------+
| nenjin |
+--------+
1 row in set (0.001 sec)

 

9. 다른 database에서 kmj database의 member 테이블 확인

MariaDB [kmj]> use khacademy;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [khacademy]> select * from kmj.member;
+--------+------+-------+
| name   | age  | class |
+--------+------+-------+
| nenjin |   15 | G     |
| yang   |   35 | F     |
+--------+------+-------+
2 rows in set (0.000 sec)