[MySQL] ERD 대학수강프로그램 - 4. 테이블 업데이트 (update, case, if, modify)

1. at_mid, at_final, at_attend, at_homework 값 업데이트 (40/40/10/10)

update attend set at_homework = 8 where at_num=1;
update attend set at_homework = 5 where at_num=2;
update attend set at_homework = 2 where at_num=3;
update attend set at_homework = 10 where at_num=4;
update attend set at_homework = 7 where at_num=5;
update attend set at_homework = 5 where at_num=6;
update attend set at_homework = 7 where at_num=7;
update attend set at_homework = 10 where at_num=8;
update attend set at_homework = 4 where at_num=9;
update attend set at_homework = 10 where at_num=10;
update attend set at_homework = 4 where at_num=11;
update attend set at_homework = 4 where at_num=12;
update attend set at_homework = 6 where at_num=13;
update attend set at_homework =4 where at_num=14;
update attend set at_homework =4 where at_num=15;
update attend set at_homework =4 where at_num=16;
update attend set at_homework =2 where at_num=17;
update attend set at_homework =8 where at_num=18;
update attend set at_homework =7 where at_num=19;
update attend set at_homework =10 where at_num=20;
update attend set at_homework =5 where at_num=21;
update attend set at_homework =5 where at_num=22;
update attend set at_homework =10 where at_num=23;
update attend set at_homework =5 where at_num=24;
update attend set at_homework =6 where at_num=25;
update attend set at_homework =5 where at_num=26;
update attend set at_homework =5 where at_num=27;

//update attend set at_homework =5 where at_num in(1,3,8,12,17); 로도 적용이 가능

2. at_score을  at_mid+at_final+at_attend+at_homework 로 업데이트

>=90 A, >=80 B, >=70 C, >=60 D, F

mysql> update attend set at_score = (
    -> case
    -> when (at_mid+at_final+at_attend+at_homework) >= 90 then 'A'
    -> when (at_mid+at_final+at_attend+at_homework) >= 80 then 'B'
    -> when (at_mid+at_final+at_attend+at_homework) >= 70 then 'C'
    -> when (at_mid+at_final+at_attend+at_homework) >= 60 then 'D'
    -> else 'F'
    -> end);
Query OK, 27 rows affected (0.00 sec)
Rows matched: 27  Changed: 27  Warnings: 0

3. at_pass의 기본값을 'f'로 변경

mysql> alter table attend modify column at_pass varchar(1) default 'f';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc attend;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| at_num        | int         | NO   | PRI | NULL    | auto_increment |
| at_mid        | int         | NO   |     | 0       |                |
| at_final      | int         | NO   |     | 0       |                |
| at_attend     | int         | NO   |     | 0       |                |
| at_homework   | int         | NO   |     | 0       |                |
| at_score      | varchar(45) | YES  |     | NULL    |                |
| at_pass       | varchar(1)  | YES  |     | f       |                |
| at_repetition | varchar(1)  | YES  |     | n       |                |
| at_st_num     | int         | NO   | MUL | NULL    |                |
| at_co_num     | int         | NO   | MUL | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

4. at_pass값 업데이트 

at_score가 A 또는 B이면 p, 아니면 f

mysql> update attend set at_pass =
    -> if(at_score = 'A' or at_score = 'B', 'p' , 'f' );
Query OK, 6 rows affected (0.00 sec)
Rows matched: 27  Changed: 6  Warnings: 0

5. at_repetition 업데이트

at_score가 f 이거나 at_attend가 3이하인 자료는 y, 아니면 n

mysql> update attend set at_repetition =
    -> if(at_score = 'f'  or at_attend <= 3 , 'y' , 'n' );
Query OK, 12 rows affected (0.00 sec)
Rows matched: 27  Changed: 12  Warnings: 0