MySQL 24번째수업

<연습문제> (데이터 변경문제라서 결과는 캡쳐안함)

​

16-1) 현재 학년이 3학년이면 2019년 1학기에 등록 테이블의 수업료(fee_price)에 100000원, 등록금총액(fee_total), 납부총액(fee_pay)에 각각 100000원을 가산하여 데이터 값을 변경하라.

A)

mysql> update fee

-> set fee_price = fee_price + 100000,

-> fee_total = fee_total + 100000,

-> fee_pay = fee_pay + 100000

-> where fee_year = 2019 and fee_term = 1 and stu_no in

-> (select stu_no from student where grade = 3);

​

​

16-2) 등록 테이블에서 입학금(fee_enter), 수업료(fee_price)를 5% 증가시키고 등록금총액(fee_total), 납부 총액(fee_pay)도 증가시켜 변경하라.

A)

mysql> update fee

-> set fee_enter = fee_enter * 1.05,

-> fee_price = fee_price * 1.05,

-> fee_total = ifnull(fee_enter, 0) + fee_price,

-> fee_pay = fee_total - ifnull(jang_total , 0);

​

​

16-3) 학번 20161001번이 '2016'년도에 수강 신청한 모든 데이터를 삭제하라.

A)

mysql> delete from attend

-> where stu_no = '20161001'

-> and att_year = '2016';

​

​

16-4) 동아리에 가입한 학생의 수강신청 데이터를 삭제하라.

A)

mysql> delete from attend

-> where stu_no in

-> (select stu_no from circle);

​

​

16-5) "유하나" 학생과 같은 도시에 살고 있는 모든 학생을 삭제하라. 그러나 "유하나" 학생은 제외시킨다.

A)

mysql> delete from student

-> where substring(address, 1, 2) =

-> (select * from (select substring(address, 1, 2) from student where stu_name = '유하나') as a)

-> and stu_name <> '유하나';

​

​

​​

▶ 인덱스의 사용

​

create index 인덱스이름 on 테이블이름 (열, ...);

​

drop index 인덱스이름 on 테이블이름

​​

​

​

<연습문제>

​

17-2) 학적 테이블(STUDENT)에서 생년월일을 유일하지 않은 인덱스를 생성하라.​

A)

mysql> create index stu_idx_birth on student(birthday);

​

​

17-3) 학적 테이블(STUDENT)에서 이름과 생년월일을 조합하여 유일한 인덱스를 생성하라.

A)

mysql> create unique index sidx_name_birth on student(stu_name, birthday);

​

​

17-4) 17-2와 17-3에서 생성한 인덱스를 삭제하라.

A)

mysql> drop index stu_idx_birth on student;

mysql> drop index sidx_name_birth on student;

​

​

​

▶ 뷰(VIEW)

​

*뷰의 생성

​

예제 18-1) STUDENT 테이블로부터 모든 학생의 학번과 학년, 반을 가지고 있는 뷰 테이블(V_CLASS)을 생성하라.

A)

mysql> create view v_class as

-> select stu_no, grade, class

-> from student;

​

​

예제 18-2) 등록한 학생의 학번과 등록년도에 대한 뷰 테이블을 생성하라.

A)

mysql> create view v_feeyear as

-> select stu_no, fee_year

-> from fee

-> where fee_year is not null;

​

​

예제 18-3) 재학생의 학번과 이름, 성별, 입학년도, 생년, 월, 일, 나이에 대한 뷰 테이블(V_AGES)을 생성하라.

A)

mysql> create view V_AGES as

-> select stu_no, stu_name, gender,

-> substring(stu_no, 1, 4) 'IBHAK_YEAR',

-> substring(birthday, 1, 4) 'B_YEAR',

-> substring(birthday, 5, 2) 'B_MONTH',

-> substring(birthday, 7, 2) 'B_DATE',

-> year(now()) - substring(birthday, 1, 4) + 1 'AGE'

-> from student;

​

​

예제 18-4) 재학생 중 20세 이상인 여학생의 학번, 이름, 성별, 출생년도, 나이를 출력하라.

A)

mysql> select stu_no, stu_name, gender, b_year, age

-> from V_AGES

-> where age > 19 and gender in (2, 4, 6);

​

​

예제 18-5) 재학생 중 21세에 해당하는 학생의 성년식 행사를 위한 명단을 출력하라. 단, 출력형식은 학과, 학년, 학번, 이름, 생년, 월, 일, 나이를 출력하라.

A)

mysql> select dept_code "학과코드", grade "학년", s.stu_no "학번", s.stu_name "이름",

-> b_year "년", b_month "월", b_date "일", age "나이"

-> from student s, V_AGES v

-> where s.stu_no = v.stu_no and age = 21

-> order by 1, 2, 3;