[DB] 기초공부 3

2025.02.14 ERD를 이용하여 테이블 만들기

ERD

ERD를 토대로 테이블은 생성해보겠다.

 

Student 테이블

create table student (
    std_num varchar(10),
    std_name varchar(20),
    std_major varchar(20),
    std_term int,
    std_point int,
    primary key (std_num)
);

 

Course 테이블

create table course (
    co_code varchar(10),
    co_name varchar(20),
    co_professor varchar(20),
    co_point int default 3,
    co_time int,
    co_titmetable varchar(40),
    primary key (co_code)
);

 

Attend 테이블

create table attend (
    at_num int auto_increment,
    at_std_num varchar(10),
    at_co_code varchar(10),
    at_year int,
    at_term int,
    at_mid int default 0,
    at_final int default 0,
    at_hw int default 0,
    at_attend int default 0,
    at_repetition varchar(1) default 'n',
    at_score varchar(4),
    primary key (at_num)
);

---

외래키

외래키 추가 방법

테이블 생성시 추가
create table 테이블명(
열명칭1 특성1... ,
열명칭2 특성2... ,
열명칭3 특성3... ,
primary key(열명칭),
foreign key(열명칭1) references 참조테이블명1(열명칭),
foreign key(열명칭2) references 참조테이블명2(열명칭)
);

Alter table 추가하는 방법
attend 테이블에 외래키 추가
alter table attend add foreign key(at_std_num) references student(std_num);
alter table attend add foreign key(at_co_code) references course(co_code);


외래키 규칙(참조무결성)
참조되는 테이블에 없는 값은 추가할 수 없다. NULL은 가능.

외래키 추가

MUL이 외래키라고 생각하면 된다. PRI는 기본키이다.

---

값 insert

insert into student values
('2019160123','전봉준','컴퓨터공학',2,64),
('2019456001','강길동','디자인',3,60),
('2020123001','강나래','화학공학',1,56),
('2020123020','박철수','화학공학',1,57),
('2020160001','강철수','컴퓨터공학',1,40),
('2020160002','나영희','컴퓨터공학',1,60),
('2022123001','강다운','화학공학',1,45),
('2022123002','김수진','디자인',1,50),
('2023160001','이영철','컴퓨터공학',1,55),
('2023160002','최수지','디자인',1,65);

insert into course values
('2020ipc001','컴퓨터개론','유관순',2,2,'화1A,1B,2A,2B'),
('2020ipc002','기초전기','이순신',3,4,'월1A,1B,2A목1A,1B,2A'),
('2020msc001','대학수학기초','홍길동',3,3,'월1A,1B,2A수1A,1B,2A'),
('2020msc002','프로그래밍일반','임꺽정',3,3,'월1A,1B,2A목1A,1B,2A'),
('2021deg001','디자인기초','황희',2,3,'목1A,1B,2A,2B'),
('2021deg002','색채이론','신사임당',3,2,'금1A,1B,2A,2B'),
('2022che001','화학이론','김길동',3,2,'월1A,1B,수2A,2B');

insert into attend(at_std_num, at_co_code) values
('2020160001','2020msc001'),
('2020160002','2020msc001'),
('2023160002','2021deg001'),
('2023160002','2021deg002'),
('2019160123','2020msc002'),
('2019456001','2020msc002'),
('2020123001','2020ipc001'),
('2020123020','2020ipc001'),
('2020123020','2022che001'),
('2020123001','2022che001'),
('2019456001','2020ipc002'),
('2019160123','2020ipc002'),
('2022123001','2020msc002'),
('2020160002','2020msc002'),
('2019160123','2020msc001'),
('2019456001','2020msc001'),
('2023160002','2022che001'),
('2022123001','2022che001'),
('2023160001','2020msc002'),
('2022123002','2021deg001'),
('2022123002','2021deg002'),
('2023160002','2020msc002');

---

정보 Update 및 Select

1. at_year을 2024년으로 업데이트
update attend set at_year = 2024;

2. at_term은 at_num 1~10까지 1로 적고 11~22 2로 업데이트
update attend set at_term = case
    when at_num <= 10 then 1
    else 2
    end;
    
다른방법
update attend set at_term = (
if(at_num <=10, 1, 2)
);

3. 1학기의 점수만 채우기
at_mid(최대) : 40 / at_final(최대) : 40 / at_attend(최대) : 10 / at_hw(최대) : 10
update attend set at_mid=40, at_final=37, at_attend=9, at_hw=10 where at_num=1;
update attend set at_mid=35, at_final=38, at_attend=8, at_hw=9 where at_num=2;
update attend set at_mid=13, at_final=7, at_attend=1, at_hw=10 where at_num=3;
update attend set at_mid=27, at_final=28, at_attend=5, at_hw=4 where at_num=4;
update attend set at_mid=37, at_final=35, at_attend=7, at_hw=9 where at_num=5;
update attend set at_mid=25, at_final=40, at_attend=8, at_hw=10 where at_num=6;
update attend set at_mid=10, at_final=8, at_attend=4, at_hw=3 where at_num=7;
update attend set at_mid=34, at_final=36, at_attend=9, at_hw=8 where at_num=8;
update attend set at_mid=19, at_final=7, at_attend=5, at_hw=10 where at_num=9;
update attend set at_mid=27, at_final=40, at_attend=10, at_hw=8 where at_num=10;

4. at_score 채우기
at_mid + at_final + at_hw + at_attend
>=90 'A'
>=80 'B'
>=70 'C'
>=60 'D'
나머지 'F'
update attend set at_score = case
    when (at_mid + at_final + at_hw + at_attend) >= 90 then 'A'
    when (at_mid + at_final + at_hw + at_attend) >= 80 then 'B'
    when (at_mid + at_final + at_hw + at_attend) >= 70 then 'C'
    when (at_mid + at_final + at_hw + at_attend) >= 60 then 'D'
    else 'F'
    end where at_term = 1;

5. at_repetition 재수강 여부 채우기 (1학기만 채우기)
at_score가 'F'이거나, at_attend가 2이하이면 'y'
update attend set at_repetition = case
    when (at_score = 'F' OR at_attend <= 2) then 'Y'
    else 'N'
    end where at_term = 1;
    
6. score별 인원수 집계
select at_score as 학점, count(at_score) as 인원수 from attend group by at_score

7. 재수강 인원 집계
select count(at_score) as 재수강할인원수 from attend where at_score = 'f';

다른 방법
select at_repetition as 재수강자, count(at_repetition) as 인원
from attend
group by at_repetition
having at_repetition = 'y';

---

Join

join 쉽게 이해하기

join
- 여러개의 테이블을 묶어 하나의 테이블로 사용하는 것
- 원하는 데이터가 다른 테이블에 흩어져 있을 경우 join 사용
- 내부조인(inner join), 외부조인(outer join)
- 일반적인 조인은 내부조인 (inner join / join)

select 열목록 from 테이블명
(inner) join 참조테이블명
on 조인조건(fk 연결)
[where 조건]
[group by 열]
[having 조건]
[order by 열]

 

실습 예시 코드

1. F를 받은 학생 명단 (학번, 이름, 학과)
select s.std_num, s.std_name, s.std_major from student s
inner join attend a
on s.std_num = a.at_std_num
where a.at_score = 'F';

2. A학점을 받은 학생명단 (학번, 이름, 과목, 교수명)
select s.std_num, s.std_name, s.std_major, c.co_professor from student s
inner join attend a
on s.std_num = a.at_std_num
inner join course c
on a.at_co_code = c.co_code
where a.at_score = 'A';

3. A학점 학생들의 학번, 이름, 과목, (중간+기말+출석+과제) as 성적
select s.std_num, s.std_name, s.std_major, (a.at_mid + a.at_final + a.at_attend + a.at_hw) as 성적 from student s
inner join attend a
on s.std_num = a.at_std_num
where a.at_score = 'A';

4. 과목별 (co_name) 별 중간, 기말 출석 과제 합계
select c.co_name, sum(a.at_mid) as 중간, sum(a.at_final) as 기말, sum(a.at_attend) as 출석, sum(a.at_hw) as 과제
from course c
inner join attend a
on c.co_code = a.at_co_code
group by c.co_name;

5. 학점별 중간, 기말, 출석, 과제 평균 (null은 제외, 학점별 오름차순)
select a.at_score, avg(a.at_mid) as 중간평균, avg(a.at_final) as 기말평균, avg(a.at_attend) as 출석평균, avg(a.at_hw) as 과제평균
from attend a
where a.at_score is not null
group by a.at_score order by a.at_score asc;

6. 강철수가 수강하고 있는 과목명과 교수명 출력
select s.std_name, c.co_name, c.co_professor from student s
inner join attend a
on s.std_num = a.at_std_num
inner join course c
on a.at_co_code = c.co_code
where s.std_name = '강철수';