[SQL입문] MySQL_뷰(VIEW)와 MySQL 권한 부여, 실습 문제 풀이

---

 

 

데이터 분석을 위한 SQL 입문

 

✔️ VIEW(뷰)

✔️MySQL 서버에서 사용자 생성 및 권한 부여

✔️실습 : DB 생성부터 데이터 조회

 

 

---

 

 

✔️ VIEW(뷰)

 

 

VIEW(뷰)

SELECT로 조회한 내용을 테이블 생성처럼 저장하는 것

읽기 전용 -> VIEW에서 확인 가능

 

VIEW(뷰) 의 장점

단순화: 복잡한 쿼리를 뷰로 생성여 데이터 조회 가능

보안 강화: 사용자 권한을 뷰로 제한하여 데이터 노출 최소화

유지보수: 테이블보다 생성과 제거가 용이

 

 

VIEW(뷰) 의 단점

성능: 뷰가 복잡하면 쿼리 실행 시간이 길어질 수 있음

제한: 수정 불가 뷰가 많아서 데이터 조작에 용이하지 않음

의존성: 기반 테이블 삭제 시 뷰 사용 불가

 

-- 뷰 생성 기본 형식
CREATE VIEW 뷰이름 AS SELECT문

-- 뷰 제거 기본 형식
DEOP VIEW 뷰이름

-- 환율 데이터셋 활용 뷰 생성 위한 데이터 조회
select 
	통화,
    min(현찰_살때_환율) 살때최저환율,
    max(현찰_살때_환율) 살때최고환율,
    round(avg(현찰_살때_환율),2) 살때평균환율,
    round(min(현찰_살때_환율) - max(현찰_살때_환율),2) 살때환율변동량,
    min(현찰_팔때_환율) 팔때최저환율,
    max(현찰_팔때_환율) 팔때최고환율,
    round(avg(현찰_팔때_환율),2) 팔때평균환율,
    round(min(현찰_팔때_환율) - max(현찰_팔때_환율),2) 팔때환율변동량
from exchange_rate
where date between '1997-01-01' and '2001-12-31'
group by 통화;


--뷰 생성
create view exchange_rate_1997_2001 as (
	select 
		통화,
		min(현찰_살때_환율) 살때최저환율,
		max(현찰_살때_환율) 살때최고환율,
		round(avg(현찰_살때_환율),2) 살때평균환율,
		round(min(현찰_살때_환율) - max(현찰_살때_환율),2) 살때환율변동량,
		min(현찰_팔때_환율) 팔때최저환율,
		max(현찰_팔때_환율) 팔때최고환율,
		round(avg(현찰_팔때_환율),2) 팔때평균환율,
		round(min(현찰_팔때_환율) - max(현찰_팔때_환율),2) 팔때환율변동량
	from exchange_rate
	where date between '1997-01-01' and '2001-12-31'
	group by 통화
);

-- 생성된 뷰 확인
select * from exchange_rate_1997_2001;

-- 일반적으로 VIEW에서는 UPDATE 수행이 되지 않음
-- 예외적으로 VIEW가 단순하고, 기반 테이블에 직접 Mapping이 가능한 경우 UPDATE 가능
update exchange_rate_1997_2001 set 통화 = '미국' where 살떄최조환율 = 855.3;

 

 

 

---

 

 

✔️ MySQL 서버에서 사용자 생성 및 권한 부여

 

 

기존의 서버에서 사용자 생성

 

 

Adminstration -> Users and Privileges -> Add Acount 선택

 

 

 

로그인 이름 및 비번 생성 -> Account Limits 설정 X -> Adminstratine Roles에서 DBManager 선택 -> Add Entry

 

 

 

권한을 지정할 DB 선택 -> 권한의 범위 지정 

 

 

 

최종적으로 연결 설정하여 Test Connection이 오류 없이 진행 -> 새로운 Connection 생성됨

 

+

 

새로운 Connecion에 들어가서 데이터 조회 진행

권한이 있는 DB만 활용 가능한지 확인

(새로운 사용자를 지정하여 내 DB에서 읽기 권한만 주는 것도 가능)

 

 

---

 

 

✔️  실습 : DB 생성부터 데이터 조회

 

 

 

ERD를 활용한 univ_db 생성

 

 

univ_db 생성 조건

학과와 학생은 1 : N 관계
(학과는 많은 학생이 소속,  학생은 하나의 학과 소속)

학과와 교수는 1 : N 관계
(학과는 많은 교수가 소속, 교수는 하나의 학과 소속)

교수와 개설과목은 1 : N 관계
(교수는 많은 과목 가르칠 수 있음, 개설과목은 한 명의 교수만 소속)

개설과목과 수강은 1 : N 관계
(한 개설과목은 여러 명이 수강 가능, 수강은 한 개설과목만 가능) 

학생과 수강은 1 : N 관계
(학생은 여러 과목을 수강할 수 있음, 수강은 한 명의 학생과 가능)

 

 

테이블 생성 및 데이터 타입 설정

학생(Student) 테이블
학번(student_id)_PK, 성명(student_name), 키(height), 학과코드(department_id)_FK

학과(Department) 테이블

학과코드(department_id)_PK, 학과명(department_name) 

교수(Professor) 테이블

교수코드(professor_id)_PK, 교수명(professor_name), 학과코드(department_id)_FK

개설과목(Course) 테이블

과목코드(course_id)_PK, 과목명(course_name), 교수코드(professor_id), 

시작일(start_date), 종료일(end_date) 

수강(Student_Course) 테이블

학번(student_id)_FK, 과목코드(course_id)_FK

 

 

MySQL워크벤치를 통해 ERD 구성

ERD 생성하는 법은 ERD 생성에 대해 작성한 블로그 글 참조

위 조건을 참조하여 ERD 생성하여 univ_db 저장하고

forward Engineering 수행하여 스키마에 DB 저장

[SQL입문] MySQL_ERD 다이어그램 생성과 DB 백업 및 불러오기

 

 

---

 

 

univ_db에 데이터 생성(입력)

 

 

각 테이블에 해당하는 데이터  생성 

use univ_db;

-- 학과 데이터 생성
insert into department values(1,'수학');
insert into department values(2,'국문학');
insert into department values(3,'정보통신공학');
insert into department values(4,'모바일공학');

-- 학과 테이블 확인
select * from department;
desc department;

-- 학생 데이터 생성
insert into student values (1, '가길동', 177,1);
insert into student values (2, '나길동', 178,1);
insert into student values (3, '다길동', 179,1);
insert into student values (4, '라길동', 180,2);
insert into student values (5, '마길동', 170,2);
insert into student values (6, '바길동', 172,3);
insert into student values (7, '사길동', 166,4);
insert into student values (8, '아길동', 192,4);

-- 학생 테이블 확인
select * from student;
desc student;


-- 교수 데이터 생성
insert into professor values (1, '가교수', 1);
insert into professor values (2, '나교수', 2);
insert into professor values (3, '다교수', 3);
insert into professor values (4, '빌게이츠', 4);
insert into professor values (5,'스티브잡스', 3);

-- 교수 테이블 확인
select * from professor;
desc professor;

-- 개설과목 데이터 생성
insert into course values (1,'교양영어',1,'2016/09/02','2016/11/30');
insert into course values (2,'데이터베이스 입문',3,'2016/08/20','2016/10/30');
insert into course values (3,'회로이론',2,'2016/10/20','2016/12/30');
insert into course values (4,'공업수학',4,'2016/11/02','2017/01/28');
insert into course values (5,'객체지향프로그래밍',3,'2016/11/01','2017/01/30');

-- 개설과목 테이블 확인
select * from course;
desc course;

-- 수강 데이터 생성
insert into student_course values(1,1);
insert into student_course values(2,1);
insert into student_course values(3,2);
insert into student_course values(4,3);
insert into student_course values(5,4);
insert into student_course values(6,5);
insert into student_course values(7,5);

-- 수강 테이블 확인
select * from student_course;
desc student_course;

 

 

 

데이터 조회 문제 풀이

 

문제1

학생번호, 학생명, 학과번호, 학과명 정보 조회

select 
	student_id,
    student_name,
    height,
    s.department_id,
    department_name
from student s -- 기준이 되는 테이블을 왼쪽에 두기(대부분 데이터가 많음)
join department d on s.department_id = d.department_id;

 

 

문제2

가교수의 교수 아이디 조회

select
	professor_id
from professor
where professor_name = '가교수';

 

 

문제3

학과이름별 교수의 수를 조회

select
	department_name,
    count(professor_id)
from department d
join professor p on d.department_id = p.department_id
group by department_name;

-- 다른 풀이

select 
	department_name,
    count(professor_id)
from professor p 
left join department d on p.department_id = d.department_id
group by department_name;

 

 

문제4

'정보통신공학'과의 학생정보 조회

select 
	student_id,
    student_name,
    height,
    s.department_id,
    department_name
from student s
join department d on s.department_id = d.department_id
where department_name = '정보통신공학';

-- 다른 풀이

select 
	student_id,
    student_name,
    height,
    s.department_id,
    department_name
from student s
left join department d on s.department_id = d.department_id
where department_name = '정보통신공학';

 

 

문제5

'정보통신공학'과의 교수명을 조회

select 
	professor_id,
    professor_name,
    p.department_id,
    department_name
from professor p
join department d on p.department_id = d.department_id
where department_name = '정보통신공학';

-- 다른 풀이

select 
	professor_id,
    professor_name,
    p.department_id
    department_name
from professor p
left join department d on p.department_id = d.department_id
where department_name = '정보통신공학';

 

 

문제6

학생 중 성이 '아'인 학생이 속한 학과명과 학생명을 조회

select 
	student_name,
    department_name
from student s
join department d on s.department_id = d.department_id
where student_name like '아%';

-- 다른 풀이

select 
	student_name,
    department_name
from student s
left join department d on s.department_id = d.department_id
where student_name like '아%';

 

 

문제7

키가 180~190 사이에 속하는 학생의 수 조회

select
	count(student_id) -- count(*)
from student
where height between 180 and 190;

 

 

문제8

학과이름별 키의 최고값, 평균값 조회

select
	department_name,
    max(height),
    round(avg(height))
from department d
join student s on d.department_id = s.department_id
group by department_name;

-- 다른 풀이

select
	department_name,
    max(height),
    round(avg(height))
from department d
left join student s on d.department_id = s.department_id
group by department_name;

 

 

문제9

'다길동' 학생과 같은 학과에 속한 학생의 이름 조회

-- 서브쿼리 활용

select 
	student_name
from student
where department_id in (select department_id from student where student_name='다길동');
-- where department_id = (select department_id from student where student_name='다길동')

 

 

문제10

2016년 11월에 시작하는 과목을 수강하는 학생의 이름과 수강과목 조회

select 
	student_name,
    course_name
from student s
left join student_course sc on s.student_id = sc.student_id
left join course c on sc.course_id = c.course_id
where date_format(start_date, '%Y-%m') = '2016-11';

 

 

문제11

'데이터베이스 입문' 과목을 수강신청한 학생의 이름 조회

select 
	student_name
from student s
left join student_course sc on s.student_id = sc.student_id
left join course c on sc.course_id = c.course_id
where course_name = '데이터베이스 입문';

 

 

문제12

'빌게이츠' 교수의 과목을 수강신청한 학생 수 조회

select 
	count(s.student_id)
from student s
left join student_course sc on s.student_id = sc.student_id
left join course c on sc.course_id = c.course_id
left join professor p on c.professor_id = p.professor_id
where professor_name = '빌게이츠';

-- 서브 쿼리 사용

select 
	count(s.student_id)
from student s
left join student_course sc on s.student_id = sc.student_id
where course_id in (
select 
	course_id
from course c
left join professor p on c.professor_id = p.professor_id
where professor_name = '빌게이츠');

 

 

---

 

 

데이터 분석을 위한 SQL 입문_4

뷰와 사용자 생성 및 권한 부여, ERD 생성부터 데이터 조회 실습까지 진행하였습니다. 

자세한 내용은 혼자 공부하는 SQL 블로그 글도 참고해주시기 바랍니다.

 

'SQL/혼자 공부하는 SQL' 카테고리의 글 목록