/*
DML
C : insert / into 데이터추가
R : select / from 데이터 조회
U : update / set 데이터 수정
D : delete / from 데이터 삭제
*/
/*==============================<< insert >>================================*/
select * from student_mst;
insert into student_mst
(student_id, student_name, mentor_id)
values
(5, '이강용', 10),
(6, '김준경', 10),
(7, '이현수', 10),
(8, '정의현', 10);
/**/
insert into university_mst
values
(4, '강원대'),
(5, '경북대'),
(6, '전남대'),
(7, '전북대'),
(8, '제주대'),
(9, '충남대'),
(10, '충북대');
/*==============================<< update >>================================*/
select * from student_mst;
update student_mst
set
student_name = '김재영',
mentor_id = 15
where
student_id = 5;
/*멘토 아이디가 10인 학생들의 멘토 아이디를 1로 바꿔라*/
update student_mst
set
mentor_id = 1
where
mentor_id = 10;
/*==============================<< delete >>================================*/
delete
from
student_mst
where
student_id = 4
and student_name = '손지호';
select * from university_mst;
delete
from
university_mst
where
university_name = '제주대';
/*==============================<< select >>================================*/
select * from student_mst;
/* 전체 컬럼 조회 */
select
*
from
student_mst;
/* 지정 컬럼 조회 */
select
student_id,
student_name,
mentor_id
from
student_mst;
/* 임시 컬럼 추가*/
select
1 as num,
'김준일' as name;
select
student_id,
student_name,
'김준일' as instructor_name;
/* 컬럼명을 임시로 바꾸는 방법 as(alias) 알리아스 */
select
sm.student_id as studentId
from
student_mst sm;
/* 조회조건 where */
select
*
from
student_mst
where
mentor_id = (select
mentor_id
from
mentor_mst
where
mentor_name = '문자영');
select
student_id,
student_name,
mentor_id, (select
mentor_name
from
mentor_mst
where
mentor_id = student_mst.mentor_id)
from
student_mst;
/* 그룹으로 묶어서 조회하기*/
select
count(mentor_id),
min(student_id),
max(student_id),
avg(student_id),
sum(student_id),
mentor_id
from
student_mst
group by
mentor_id;
/* 중복 제거*/
select distinct
mentor_id
from
student_mst;
/* 그룹으로 조회한 결과에 조건주는 방법*/
select
count(mentor_id) as mentor_count,
min(student_id),
max(student_id),
avg(student_id),
sum(student_id),
mentor_id
from
student_mst
group by
mentor_id
having
mentor_count = 5;
/* 정렬 */
select
*
from
student_mst
order by
mentor_id,
student_id desc;
/* 전체 조합 실습*/
select
count(*) as student_count,
mentor_id
from
student_mst
where
student_id > 2
group by
mentor_id
having
student_count = 1
order by
mentor_id desc;
