1. attend 테이블의 수강년도(at_year) 을 2023으로 변경
mysql> update attend set at_year = 2023;
Query OK, 16 rows affected (0.00 sec)
Rows matched: 16 Changed: 16 Warnings: 0
mysql> select * from attend;
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| at_num | at_std_num | at_co_code | at_year | at_term | at_mid | at_final | at_attend | at_hw | at_repetition | at_score |
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| 81 | 2020160001 | 20201msc001 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 82 | 2020160002 | 20201msc001 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 83 | 2019160123 | 20202msc002 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 84 | 2019456001 | 20202msc002 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 85 | 2020123001 | 20201ipc001 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 86 | 2020123020 | 20201ipc001 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 87 | 2019456001 | 20202ipc002 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 88 | 2019160123 | 20202ipc002 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 89 | 2020160001 | 20202msc002 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 90 | 2020160002 | 20202msc002 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 91 | 2019160123 | 20201msc001 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 92 | 2019456001 | 20201msc001 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 93 | 2020123001 | 20201msc001 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 94 | 2020123020 | 20201msc001 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 95 | 2019456001 | 20202msc002 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
| 96 | 2019160123 | 20202msc002 | 2023 | NULL | 0 | 0 | 0 | 0 | n | NULL |
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
16 rows in set (0.00 sec)2. 수강학기(at_term) : 1~8은 1학기, 나머지는 9~16 2학기로 적용
mysql> update attend set at_term = 1 where at_num between 81 and 88;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql> update attend set at_term = 2 where at_num between 89 and 96;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql> select * from attend;
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| at_num | at_std_num | at_co_code | at_year | at_term | at_mid | at_final | at_attend | at_hw | at_repetition | at_score |
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| 81 | 2020160001 | 20201msc001 | 2023 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 82 | 2020160002 | 20201msc001 | 2023 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 83 | 2019160123 | 20202msc002 | 2023 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 84 | 2019456001 | 20202msc002 | 2023 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 85 | 2020123001 | 20201ipc001 | 2023 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 86 | 2020123020 | 20201ipc001 | 2023 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 87 | 2019456001 | 20202ipc002 | 2023 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 88 | 2019160123 | 20202ipc002 | 2023 | 1 | 0 | 0 | 0 | 0 | n | NULL |
| 89 | 2020160001 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 90 | 2020160002 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 91 | 2019160123 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 92 | 2019456001 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 93 | 2020123001 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 94 | 2020123020 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 95 | 2019456001 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 96 | 2019160123 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
16 rows in set (0.00 sec)3. 1학기 학생들의 점수 입력
mysql> update attend set at_mid = 38, at_final = 35, at_attend = 8, at_hw = 9
-> where at_num = 81;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update attend set at_mid = 30, at_final =40, at_attend = 1, at_hw =5
-> where at_num = 82;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update attend set at_mid = 22, at_final = 10, at_attend = 8, at_hw = 4
-> where at_num = 83;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update attend set at_mid = 35, at_final = 22, at_attend = 8, at_hw = 9
-> where at_num = 84;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update attend set at_mid = 25, at_final = 12, at_attend = 1, at_hw = 6
-> where at_num = 85;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update attend set at_mid = 40, at_final = 40, at_attend = 8, at_hw = 3
-> where at_num = 86;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update attend set at_mid = 15, at_final = 35, at_attend = 8, at_hw = 2
-> where at_num = 87;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update attend set at_mid = 22, at_final = 33, at_attend = 5, at_hw = 5
-> where at_num = 88;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from attend;
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| at_num | at_std_num | at_co_code | at_year | at_term | at_mid | at_final | at_attend | at_hw | at_repetition | at_score |
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| 81 | 2020160001 | 20201msc001 | 2023 | 1 | 38 | 35 | 8 | 9 | n | NULL |
| 82 | 2020160002 | 20201msc001 | 2023 | 1 | 30 | 40 | 1 | 5 | n | NULL |
| 83 | 2019160123 | 20202msc002 | 2023 | 1 | 22 | 10 | 8 | 4 | n | NULL |
| 84 | 2019456001 | 20202msc002 | 2023 | 1 | 35 | 22 | 8 | 9 | n | NULL |
| 85 | 2020123001 | 20201ipc001 | 2023 | 1 | 25 | 12 | 1 | 6 | n | NULL |
| 86 | 2020123020 | 20201ipc001 | 2023 | 1 | 40 | 40 | 8 | 3 | n | NULL |
| 87 | 2019456001 | 20202ipc002 | 2023 | 1 | 15 | 35 | 8 | 2 | n | NULL |
| 88 | 2019160123 | 20202ipc002 | 2023 | 1 | 22 | 33 | 5 | 5 | n | NULL |
| 89 | 2020160001 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 90 | 2020160002 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 91 | 2019160123 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 92 | 2019456001 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 93 | 2020123001 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 94 | 2020123020 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 95 | 2019456001 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 96 | 2019160123 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
16 rows in set (0.00 sec)4. at_score = at_mid+at_final+at_attend+at_hw 로 등급 나누기
mysql> update attend set at_score = (
-> case
-> when (at_mid+at_final+at_attend+at_hw) >= 90 then 'A'
-> when (at_mid+at_final+at_attend+at_hw) >= 80 then 'B'
-> when (at_mid+at_final+at_attend+at_hw) >= 70 then 'C'
-> when (at_mid+at_final+at_attend+at_hw) >= 60 then 'D'
-> else 'F'
-> end)
-> where at_term = 1;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql> select * from attend;
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| at_num | at_std_num | at_co_code | at_year | at_term | at_mid | at_final | at_attend | at_hw | at_repetition | at_score |
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| 81 | 2020160001 | 20201msc001 | 2023 | 1 | 38 | 35 | 8 | 9 | n | A |
| 82 | 2020160002 | 20201msc001 | 2023 | 1 | 30 | 40 | 1 | 5 | n | C |
| 83 | 2019160123 | 20202msc002 | 2023 | 1 | 22 | 10 | 8 | 4 | n | F |
| 84 | 2019456001 | 20202msc002 | 2023 | 1 | 35 | 22 | 8 | 9 | n | C |
| 85 | 2020123001 | 20201ipc001 | 2023 | 1 | 25 | 12 | 1 | 6 | n | F |
| 86 | 2020123020 | 20201ipc001 | 2023 | 1 | 40 | 40 | 8 | 3 | n | A |
| 87 | 2019456001 | 20202ipc002 | 2023 | 1 | 15 | 35 | 8 | 2 | n | D |
| 88 | 2019160123 | 20202ipc002 | 2023 | 1 | 22 | 33 | 5 | 5 | n | D |
| 89 | 2020160001 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 90 | 2020160002 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 91 | 2019160123 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 92 | 2019456001 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 93 | 2020123001 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 94 | 2020123020 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 95 | 2019456001 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 96 | 2019160123 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
16 rows in set (0.00 sec)5. at_repitition 재수강여부를 y/n으로 입력
at_score = f or at_attrnd <= 3 : 재수강 y
mysql> update attend set at_repetition =
-> if(at_score = 'F' and at_attend <=3, 'y', 'n')
-> where at_term = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 8 Changed: 1 Warnings: 0
mysql> select * from attend;
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| at_num | at_std_num | at_co_code | at_year | at_term | at_mid | at_final | at_attend | at_hw | at_repetition | at_score |
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
| 81 | 2020160001 | 20201msc001 | 2023 | 1 | 38 | 35 | 8 | 9 | n | A |
| 82 | 2020160002 | 20201msc001 | 2023 | 1 | 30 | 40 | 8 | 5 | n | B |
| 83 | 2019160123 | 20202msc002 | 2023 | 1 | 32 | 20 | 8 | 8 | n | D |
| 84 | 2019456001 | 20202msc002 | 2023 | 1 | 35 | 22 | 8 | 9 | n | C |
| 85 | 2020123001 | 20201ipc001 | 2023 | 1 | 25 | 12 | 1 | 6 | y | F |
| 86 | 2020123020 | 20201ipc001 | 2023 | 1 | 40 | 40 | 8 | 3 | n | A |
| 87 | 2019456001 | 20202ipc002 | 2023 | 1 | 15 | 35 | 8 | 2 | n | D |
| 88 | 2019160123 | 20202ipc002 | 2023 | 1 | 22 | 33 | 5 | 5 | n | D |
| 89 | 2020160001 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 90 | 2020160002 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 91 | 2019160123 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 92 | 2019456001 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 93 | 2020123001 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 94 | 2020123020 | 20201msc001 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 95 | 2019456001 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
| 96 | 2019160123 | 20202msc002 | 2023 | 2 | 0 | 0 | 0 | 0 | n | NULL |
+--------+------------+-------------+---------+---------+--------+----------+-----------+-------+---------------+----------+
16 rows in set (0.00 sec)
'DB > 명령프롬프트 - mysql 예시' 카테고리의 다른 글
| [MySQL] product table - 이전 테이블의 값 중 하나만 가져와서 현재 테이블에 삽입 (0) | 2023.03.28 |
|---|---|
| [MySQL] ERD 수강관리 프로그램 4. join을 이용한 검색 (0) | 2023.03.28 |
| [MySQL] ERD 수강관리프로그램 2. 내부 데이터 삽입 (0) | 2023.03.28 |
| [MySQL] ERD 수강관리 프로그램 1. 생성 (속성) (0) | 2023.03.28 |
| [MySQL] student table - 모든 학생의 age를 +1 업데이트 (0) | 2023.03.27 |