[MySQL] student table - 테이블의 값을 다른 테이블로 옮기기 (4학년 친구들을 졸업)

< 4학년 친구들을 졸업 >

- 새로운 테이블을 생성해서!

1.  student_4를 student 테이블과 같은 구조로 생성

• Create table new_table(복사본) like table(원본);
• 원본 테이블의 구조를 복사하여 복사 테이블을 생성
• create table student_4 like student;

2. Grade가 4인 학생만 student_4로 이동

•  insert into문 활용해서 한번에 값 이동
• values(조건) = select * from student where grade=4;
• insert into student_4(num, name, age, address, major, score, grade)       
  • -> select * from student where grade = 4;

3. student grade 4인 학생은 삭제

• delete from student where grade=4;

4. Grade 1씩 증가

• update student set grade = grade+1;

/* 실행 */
mysql> create table student_4 like student;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_ezen |
+----------------+
| student        |
| student_4      |
| test3          |
+----------------+
3 rows in set (0.00 sec)

mysql> desc student_4;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| num     | int         | NO   | PRI | NULL    |       |
| name    | varchar(30) | NO   |     | NULL    |       |
| age     | int         | YES  |     | 20      |       |
| address | varchar(45) | YES  |     | NULL    |       |
| major   | varchar(45) | YES  |     | NULL    |       |
| score   | int         | YES  |     | NULL    |       |
| grade   | int         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

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

mysql> select * from student where grade=4;
+------+--------+------+---------+---------+-------+-------+
| num  | name   | age  | address | major   | score | grade |
+------+--------+------+---------+---------+-------+-------+
| 2222 | 강길순 |   25 | seoul   | English |    87 |     4 |
+------+--------+------+---------+---------+-------+-------+
1 row in set (0.00 sec)

mysql> insert into student_4(num, name, age, address, major, score, grade)
    -> select * from student where grade = 4;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from student_4;
+------+--------+------+---------+---------+-------+-------+
| num  | name   | age  | address | major   | score | grade |
+------+--------+------+---------+---------+-------+-------+
| 2222 | 강길순 |   25 | seoul   | English |    87 |     4 |
+------+--------+------+---------+---------+-------+-------+
1 row in set (0.00 sec)

mysql> delete from student where grade=4;
Query OK, 1 row affected (0.00 sec)

mysql> update student set grade = grade+1;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 7  Changed: 5  Warnings: 0

mysql> select * from student;
+------+--------+------+---------+----------+-------+-------+
| num  | name   | age  | address | major    | score | grade |
+------+--------+------+---------+----------+-------+-------+
| 1111 | 홍길동 |   24 | seoul   | computer |    89 |     4 |
| 3333 | 이순신 |   23 | Inchen  | English  |    57 |     3 |
| 5555 | 유관순 |   22 | suwon   | Computer |    97 |     2 |
| 6666 | 다래냥 |    2 | Inchen  | computer |   100 |  NULL |
| 7777 | 다래옹 |   24 | Inchen  | English  |    20 |     4 |
| 8888 | 다래잉 |   27 | Inchen  | computer |    80 |  NULL |
| 9999 | 다래뇽 |   23 | seoul   | computer |    70 |     3 |
+------+--------+------+---------+----------+-------+-------+
7 rows in set (0.00 sec)

---

< student테이블의 4학년을 student_4테이블로 이동 >

insert into student_4(num, name, age, address, major, score, grade)
 -> select * from student where grade = 4; (student_4에 4학년 데이터 복사)

delete from student where grade=4; (student테이블의 4학년 데이터 삭제)