MySQL- 5. MySQL 테이블 정보 추가 및 삭제

MySQL- 5. MySQL 테이블 정보 추가 및 삭제

 

 

1. 'insert into' 명령어

 

mysql> insert into ccna values('ccna1', 'ccna1111', 'kim jung woo', '23');
Query OK, 1 row affected (0.00 sec)

 

 

mysql> insert into ccna (UserID, UserPass, UserNAME, age) values('ccna2', 'ccna2222', 'kim jung woo', 26);
Query OK, 1 row affected (0.00 sec)

 

 

mysql> insert into ccna (UserNAME, UserID, UserPass, age) values('kim jung woo', 'ccna3', 'ccna3333', 29);
Query OK, 1 row affected (0.00 sec)

 

 

mysql> select * from ccna;
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna1  | ccna1111 | kim jung woo |   23 |
| ccna2  | ccna2222 | kim jung woo |   26 |
| ccna3  | ccna3333 | kim jung woo |   29 |
+--------+----------+--------------+------+
3 rows in set (0.00 sec)

 

 

[참고] 패스워드 MD5, AES 암호화 입력

 

select select md5('ccna4444');

insert into ccna (UserNAME, UserID, UserPass, age) values('kim jung woo', 'ccna4', md5('ccna4444'),  29));

 

insert into ccna (UserNAME, UserID, UserPass, age) values('kim jung woo', 'ccna5', hex(aes_encrypt('ccna5555', '5555', 29)));

 

select * from ccna;
select aes_decrypt(unhex(UserPass), '5555') from ccna;

 

2. 한방에 정보 추가하기

 

mysql> insert into ccnp (UserID, UserPass, UserNAME, age) values
    -> ('ccnp1', 'ccnp1111', 'kim jung woo', 33),
    -> ('ccnp2', 'ccnp2222', 'kim jung woo', 36),
    -> ('ccnp3', 'ccnp3333', 'kim jung woo', 39)
    -> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

 

 

mysql> select * from ccnp;
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccnp1  | ccnp1111 | kim jung woo |   33 |
| ccnp2  | ccnp2222 | kim jung woo |   36 |
| ccnp3  | ccnp3333 | kim jung woo |   39 |
+--------+----------+--------------+------+
3 rows in set (0.00 sec)

 

 

mysql> select userid,userpass from ccnp;
+--------+----------+
| userid | userpass |
+--------+----------+
| ccnp1  | ccnp1111 |
| ccnp2  | ccnp2222 |
| ccnp3  | ccnp3333 |
+--------+----------+
3 rows in set (0.00 sec)

 

 

3. 테이블 정보 복사

 

mysql> create table ccna_bak select * from ccna;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

 

 

mysql> show tables;
+-----------------+
| Tables_in_cisco |
+-----------------+
| ccie            |
| ccna            |
| ccna_bak       |
| ccnp            |
+-----------------+
4 rows in set (0.00 sec)

 

 

mysql> select * from ccna_bak;
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna1  | ccna1111 | kim jung woo |   23 |
| ccna2  | ccna2222 | kim jung woo |   26 |
| ccna3  | ccna3333 | kim jung woo |   29 |
+--------+----------+--------------+------+
3 rows in set (0.00 sec)

 

 

4. 테이블 정보 수정

 

mysql> update ccna_bak set age=43 where userid='ccna1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

 

mysql> select * from ccna_bak;
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna1  | ccna1111 | kim jung woo |   43 |
| ccna2  | ccna2222 | kim jung woo |   26 |
| ccna3  | ccna3333 | kim jung woo |   29 |
+--------+----------+--------------+------+

 

 

mysql> update ccna_bak set username='park chan ho' where userid='ccna2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

 

mysql> select * from ccna_bak;
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna1  | ccna1111 | kim jung woo |   43 |
| ccna2  | ccna2222 | park chan ho |   26 |
| ccna3  | ccna3333 | kim jung woo |   29 |
+--------+----------+--------------+------+
3 rows in set (0.00 sec)

 

 

5. 테이블 정보 삭제

 

mysql> delete from ccna_bak;
Query OK, 3 rows affected (0.00 sec)

 

mysql> select * from ccna_bak;
Empty set (0.00 sec)

 

 

mysql> show tables;
+-----------------+
| Tables_in_cisco |
+-----------------+
| ccie            |
| ccna            |
| ccna_bak        |
| ccnp            |
+-----------------+
4 rows in set (0.00 sec)

 

 

mysql> drop tables ccna_bak;
Query OK, 0 rows affected (0.00 sec)

 

 

mysql> show tables;
+-----------------+
| Tables_in_cisco |
+-----------------+
| ccie            |
| ccna            |
| ccnp            |
+-----------------+
3 rows in set (0.00 sec)