7주차 배운점 느낀점 - sql 문제, 내장 함수, view, index, 물리적 저장

7주차

 

아직까지 제대로된 mysql 안했고 sql만 했다고 하신다. 변하는것과 변하지않는것을 잘 구별해서 잘 익혀야 한다. sql을 탄탄히 해야한다.

 

코테같은거 보는 이유는 얼마나 깊이있게 생각하고 고민했느냐이다. 결과는 중요하지 않아. 어떤 차이점을 가지고 optimizer를했는지, 리스폰스타임이 어떻게 향상됐는지 등의 내용이 자소서 들어있으면 좋다고 하셨다.

 

아침부터 어제까지 배운 sql 문제를 풀었다. 2~3시간에 걸쳐 많은 양의 문제를 풀었다.
내장함수에 대해 배웠다. 숫자 계산과, 문자열을 다루고 날짜,시간 함수를 배우고 예제를 풀었다.
뷰 , 인덱스 에대해 배웠다.

 

배운점

 

1) 사원의 이름과 직위를 출력하시오. 단, 사원의 이름은 '사원이름', 직무는 '직무'로 출력한다.

select ename 사원이름, job 직무
from emp;

 

2) 30번 부서에 근무하는 모든 사원의 이름과 급여를 출력한다.

select ename, sal
from emp
where deptno = 30;

 

3) 사원번호와 이름, 현재 급여, 증가한 급여분('증가액'),10% 인상된 급여('인상된 급여＇)를
사원번호순으로 출력하세요.

select ename, sal, (sal*0.1) 증가액, (sal*1.1) '인상된 급여'
from emp
order by empno;

 

4) 'S'로 시작하는 모든 사원과 부서번호를 출력하세요

select ename, deptno
from emp
where ename like 'S%';

 

5) 모든 사원의 최대 및 최소급여, 합계 및 평균 급여를 출력하세요.열이름은 각각
MAX,MIN,SUM,AVG로 한다. 단 소수점 이하는 반올림하여 정수로 출력하시오.
반올림 함수: round( , )

select max(sal) MAX, min(sal) MIN, sum(sal) SUM, round(avg(sal),0) AVG
from emp;

 

6) 업무 이름과 업무별로 동일한 업무를 하는 사원의 수를 출력하세요.열이름은 '업무','업무별 사원수’
출력하세요

select job, count(*)
from emp
group by job;

 

7) 사원의 최대 급여와 최소 급여의 차액을 출력하세요

select max(sal)-min(sal)
from emp;

 

8) 30번 부서의 구성원 수오 ㅏ사원들의 급여의 합계와 평균을 출력하세요

select count(*), sum(sal), avg(sal)
from emp
where deptno = 30;

 

9) 평균 급여가 가장 높은 부서의 번호를 출력하세요

select deptno 
from emp 
group by deptno having avg(sal) >= all (select avg(sal) from emp group by deptno);

 

10) 세일즈맨(SALESMAN)을 제외하고 업무별 사원의 급여가 3,000이상인 각 업무에 대해, 업무명과
업무별 평균 급여를 출력하시오. 단 평균 급여는 내림차순으로 출력한다.

select job, avg(sal)
from emp
where sal >= 3000 and job not in ('SALESMAN')
group by job 
order by avg(sal) desc;

 

11) 전체 사원 가운데 직속상관이 있는 사원의 수를 출력하시오

select count(*)
from emp e1
where mgr = (select empno from emp e2 where e1.mgr = e2.empno); 

select count(empno) from emp where mgr is not null;

 

12) Emp테이블에서 이름, 급여, 커미션(comm),총액(sal+comm)을 구하여 총액이 많은 순서대로
출력하시오. 단, 커미션(comm)이 없는 사람은 제외한다.

select ename, sal, comm, sal+comm
from emp
where comm is not null and comm not in (0)
order by sal+comm desc;

 

13) 부서별로 같은 업무를 하는 사람의 인원 수를 구하여 부서번호, 업무이름, 인원수를 출력하시오
'부서별', '같은 업무' 두 번 그룹핑한다.

select deptno, job, count(*) from emp group by deptno, job order by deptno;

 

14) 사원이 한 명도 없는 부서의 이름을 출력하시오

select dname
from dept d left join emp e on d.deptno = e.deptno
where empno is null;

select dname from dept where deptno not in (select deptno from emp);

select dname from dept left join emp on dept.deptno = emp.deptno group by dept.deptno having count(empno) = 0;

 

15) 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수를 출력하시오

select job, count(*)
from emp
group by job having count(*) >= 4;

 

16) 사원번호가 7400이상 7600이하인 사원의 이름을 출력하시오

select ename from emp where empno between 7400 and 7600;

 

17) 사원의 이름과 사원의 부서를 출력하시오

select ename, dname
from emp e join dept d on e.deptno=d.deptno;

 

18) 사원의 이름과 팀장(mgr)의 이름을 출력하시오
셀프조인 이용

select e1.ename, e2.ename 
from emp e1 join emp e2 on e1.mgr = e2.empno;

 

19) 사원 SCOTT보다 급여를 많이 받는 사람의 이름을 출력하시오

select ename
from emp
where sal > (select sal from emp where ename = 'SCOTT');

 

20) 사원 SCOTT이 일하는 부서번호 혹은 DALLAS에 있는 부서번호를 출력하시오.

select distinct e.deptno
from emp e join dept d on e.deptno = d.deptno
where e.ename = 'SCOTT' or d.loc = 'DALLAS';

select emp.ename,dept.deptno from emp join dept on emp.deptno = dept.deptno where dept.deptno = (select deptno from emp where ename = 'scott') 
or loc = (select loc from dept where loc = 'dallas');

select deptno 부서번호 from emp where ename='scott'
union
(select deptno from dept 부서번호 where loc='dallas');

 

auto increment 선언

~ addrid integer primary key auto_increment,

 

참조키 삭제조건 선언

constraint PK_EMP foreign key(deptno) references DEPT(deptno) on delete cascade

 

1) Cust_addr테이블을 생성하고 제공한 스크립트를 이용하여 데이터를 입력하세요

2) 고객번호 1번의 주소 변경 내역을 모두 나타내세요

select custid,address from cust_addr where custid=1;

 

3) 고객번호 1번의 전화번호 변경 내역을 모두 나타내세요

select phone from cust_addr where custid = 1;

select custid,phone from cust_addr where custid=1;

 

4) 고객번호 1번의 가입 당시 전화번호를 나타내시오. 단, 가입당시 전화번호는 주소이력(history)중 가장 오래된 것을 찾습니다. 주소변경 이력이 없으면 현재 주소를 반환합니다.

select phone from cust_addr order by changeday limit 1;

select custid,phone from cust_addr where addrid <= all(select addrid from cust_addr where custid =1);

 

5) 고객번호 1번의 '2024년 01월01일' 당시 전화번호를 나타내세요. 단, 주소 이력 중 changeday
속성값이 '2024년 02월 01일'보다 오래된 첫번째 값을 찾습니다.
날짜 데이터 ' ' 해서 비교

select phone, changeday from cust_addr where changeday < '2024-01-01'
order by changeday desc limit 1;

select custid,phone from cust_addr where changeday >= (select changeday from cust_addr where addrid >= all(select addrid from cust_addr where custid =1));
select custid,phone from cust_addr where changeday <= '2024-01-01' limit 1;

 

6) Cart 테이블을 생성하고 제공한 데이터를 입력합니다.

 

7) 고객번호 1번의 cart에 저장된 도서 중 주문한 도서를 구하세요

cart c join orders o on c.custid = o.custid and c.bookid = o.bookid
를 해줘서 cart 와 orders의 on 조건을 custid, bookid 둘 다 줬다.
-> custid, bookid 둘 다 같아야 하므로 cart에 있는 orders만 join이 된다.

 

select * from cart join orders on cart.custid = orders.custid and cart.bookid = orders.bookid
join book on orders.bookid = book.bookid where cart.custid=1;

select * from cart natural join orders
join book on orders.bookid = book.bookid where cart.custid=1;

select bookname from cart c left join orders o on c.bookid = o.bookid join book b on c.bookid = b.bookid where c.bookid not in (select bookid from orders where custid =1); 

 

8) 고객번호 1번의 cart에 저장된 도서 중 주문하지 않는 도서를 구하세요

cart 목록에 따른 orders를 bookid 와 custid가 같다는 조건으로 left foin한다.
그 후 ordersid 가 null 인 도서를 출력한다.

SELECT * -- Cart.bookid
FROM Cart
LEFT JOIN Orders ON Cart.custid = Orders.custid AND Cart.bookid = Orders.bookid
join book on book.bookid =  cart.bookid
WHERE Cart.custid = 1
   AND Orders.orderid IS NULL;

 

9) 고객번호 1번의 cart에 저장된 도서의 정가의 합을 구하세요.

select sum(price) from cart c join book b on c.bookid = b.bookid where custid =1;

select sum(price) from book join cart on cart.bookid = book.bookid  group by cart.custid having custid =1;

 

SQL 내장 함수

SQL에서는 함수의 개념을 사용
수학의 함수와 마찬가지로 특정 값이나 열의 값을 입력 받아 그 값을 계산하여 결과 값을 돌려줌

SQL의 함수는 DBMS가 제공하는 내장 함수(built-in function), 사용자가 필요에 따라 직접 만드는 사용자 정의 함수(user-defined function)로 나뉨

 

MySQL Built-In Function 문서:
책살필요없이 여기 보면됨
https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

 

SQL 내장 함수는 상수나 속성 이름을 입력 값으로 받아 단일 값을 결과로 반환함
모든 내장 함수는 최초에 선언될 때 유효한 입력 값을 받아야 함

 

숫자 함수

ABS

select abs(-4.5), abs(4.5) from dual;

select abs(-4.5), abs(4.5) // mysql 에서 생략가능

 

첫째짜리까지 반올림

select round(4.875,1);

 

고객별 평균 주문 금액을 백 원 단위로 반올림한 값을 구하시오.
100원단위라 round 안에 -2 해줬다.

select custid 고객번호, round(sum(saleprice)/count(*),-2) 평균금액
from orders
group by custid; 

SUBSTR 3'번째'부터 4개 , 인덱스 0 부터 아님.

 

도서제목에 야구가 포함된 도서를 농구로 변경한 후 도서 목록을 보이시오.

select replace(bookname,'야구','농구')
from book
where bookname like '%야구%';

 

LENGTH : 글자의 수를 세어주는 함수 (단위가 바이트(byte)가 아닌 문자 단위)

 

굿스포츠에서 출판한 도서의 제목과 제목의 글자 수를 확인하시오.
(한글은 2바이트 혹은 UNICODE 경우는 3바이트를 차지함)

select bookname 제목, char_length(bookname) 문자수, length(bookname)
from book
where publisher = '굿스포츠'

 

SUBSTR : 지정한 길이만큼의 문자열을 반환하는 함수

 

서점의 고객 중에서 같은 성(姓)을 가진 사람이 몇 명이나 되는지 성별 인원수를 구하시오.

select substr(name,1,1) '성', count(*) '인원'
from customer
group by substr(name, 1, 1);

 

날짜·시간 함수

 

select date(sysdate);

현재시간

select sysdate();

 

adddate

select adddate('2024-01-19',interval -5 day) before5,
	   adddate('2024-01-19',interval +5 day) after5;

 

서점은 주문일로부터 10일 후 매출을 확정한다. 각 주문의 확정일자를 구하시오

select orderid '주문번호' , orderdate '주문일', adddate(orderdate, interval 10 day)
from orders;

 

STR_TO_DATE : 문자형으로 저장된 날짜를 날짜형으로 변환하는 함수
DATE_FORMAT : 날짜형을 문자형으로 변환하는 함수

 

서점이 2014년 7월 7일에 주문 받은 도서의 주문번호, 주문일, 고객번호, 도서번호를 모두 보이시오. 단, 주문일은 '%Y-%m-%d' 형태로 표시한다.

select orderid '주문번호', date_format(orderdate,'%Y-%m-%d') '주문일'
,custid '고객번호', bookid '도서번호'
from orders
where orderdate = str_to_date('2024-07-07','%Y-%m-%d');

 

SYSDATE : MySQL의 현재 날짜와 시간을 반환하는 함수

 

DBMS 서버에 설정된 현재 날짜와 시간, 요일을 확인 하시오.

 

select sysdate(), date_format(sysdate(),'%Y/%m/%d %a %h:%i') 'SYSDATE_1';

 

NULL 값이란?
아직 지정되지 않은 값
NULL 값은 ‘0’, ‘’ (빈 문자), ‘ ’ (공백) 등과 다른 특별한 값
NULL 값은 비교 연산자로 비교가 불가능함
NULL 값의 연산을 수행하면 결과 역시 NULL 값으로 반환됨

 

집계 함수를 사용할 때 주의할 점
outer join해서 null체크 먼저해줘야함!! 누수 발생한다.
‘NULL+숫자’ 연산의 결과는 NULL
집계 함수 계산 시 NULL이 포함된 행은 집계에서 빠짐
해당되는 행이 하나도 없을 경우 SUM, AVG 함수의 결과는 NULL이 되며,
COUNT 함수의 결과는 0

 

NULL 값을 확인하는 방법 – IS NULL, IS NOT NULL
NULL 값을 찾을 때는 ‘=’ 연산자가 아닌 ‘IS NULL’을 사용,  NULL이 아닌 값을 찾을 때는 ‘＜＞’ 연산자가 아닌 ‘IS NOT NULL’을 사용함

 

IFNULL : NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력
IFNULL(속성, 값) / 속성 값이 NULL이면 '값'으로 대치한다 /

 

이름, 전화번호가 포함된 고객목록을 보이시오. 단, 전화번호가 없는 고객은 ‘연락처없음’으로 표시한다.

select name, ifnull(phone, '연락처 없음') '전화번호' from customer;

 

행번호 출력
변수 0 으로 초기화

set @seq:=0;
select (@seq:=@seq+1) '순번', custid, name, phone
from customer
where @seq<2;

 

뷰(view) : 하나 이상의 테이블을 합하여 만든 가상의 테이블
select 용! 한 번 만들어놓으면 재사용이 가능
성능평가에 좋은 객체, 미리 만들어 놓으니 dbms가 부담이 덜하다.
일반 테이블처럼 사용가능, 실제 데이터가 저장되는 데이터가 아님

 

뷰의 장점
편리성 및 재사용성 : 자주 사용되는 복잡한 질의를 뷰로 미리 정의해 놓을 수 있음
→ 복잡한 질의를 간단히 작성
보안성 : 사용자별로 필요한 데이터만 선별하여 보여줄 수 있고, 중요한 질의의 경우 질의 내용을 암호화할 수 있음
→ 개인정보(주민번호)나 급여, 건강 같은 민감한 정보를 제외한 테이블을 만들어 사용
독립성 : 미리 정의된 뷰를 일반 테이블처럼 사용할 수 있기 때문에 편리하고, 사용자가 필요
한 정보만 요구에 맞게 가공하여 뷰로 만들어 쓸 수 있음
→ 원본 테이블의 구조가 변해도 응용에 영향을 주지 않도록 하는 논리적 독립성 제공

 

뷰의 특징
원본 데이터 값에 따라 같이 변함
독립적인 인덱스 생성이 어려움
삽입, 삭제, 갱신 연산에 많은 제약이 따름

 

기본 문법
CREATE VIEW 뷰이름 [(열이름 [ ,...n ])]
AS SELECT 문

 

Book 테이블에서 ‘축구’라는 문구가 포함된 자료만 보여주는 뷰

drop view vs_Book1;
create view vs_Book1
as
select * from book where bookname like '%축구%';

select * from vs_Book1; 

 

Orders 테이블에 고객이름과 도서이름을 바로 확인할 수 있는 뷰를 생성한 후, ‘김연아’ 고
객이 구입한 도서의 주문번호, 도서이름, 주문액을 보이시오.

 

create view vw_Order(orderid,custid,name,bookid,bookname,saleprice,orderdate)
as
select od.orderid, od.custid, cs.name, od.bookid, bk.bookname, od.saleprice,od.orderdate
from orders od, customer cs, book bk
where od.custid = cs.custid and od.bookid = bk.bookid;

select orderid, bookname, saleprice from vw_order where name = '김연아';

 

create or replace view 로 선언하면 수정이 가능

CREATE OF REPLACE VIEW 뷰이름 [(열이름 [ ,...n ])]
AS SELECT 문

 

[20]에서 생성한 뷰 vw_Customer는 주소가 대한민국인 고객을 보여준다. 이 뷰를 영국을
주소로 가진 고객으로 변경하시오. phone 속성은 필요 없으므로 포함시키지 마시오.
(괄호) 넣어주면 별명으로 출력이됨

create or replace view vw_customer(custid, name, address)
as select custid, name, address
from customer
where address like '%영국%';

 

(1) 판매가격이 20,000원 이상인 도서의 도서번호, 도서이름, 고객이름, 출판사, 판매가
격을 보여주는 highorders 뷰를 생성하시오.
(2) 생성한 뷰를 이용하여 판매된 도서의 이름과 고객의 이름을 출력하는 SQL 문을 작
성하시오.
(3) highorders 뷰를 변경하고자 한다. 판매가격 속성을 삭제하는 명령을 수행하시오.
삭제 후 (2)번 SQL 문을 다시 수행하시오.

create or replace  view highorders
as
select b.bookid, b.bookname, b.publisher, c.name
from book b, orders o, customer c
where b.bookid = o.bookid and o.custid = c.custid;

select bookname, name from highorders;

 

시스템 카탈로그에 저장됩니다.
sinsaegebookdb 스키마가 갖고있는 테이블과 정보 볼 수있다
basetable = 원본테이블
view = 뷰

select * from information_schema.tables where table_schema like 'sinsaegebookdb';

 

데이터베이스의 물리적 저장

 

dbms도 자기만의 방식으로 데이터를 저장한다.
운영체제에서 실행되는 응용 어플리케이션의 일종

 

실제 데이터가 저장되는 곳은 보조기억장치
하드디스크, SSD, USB 메모리 등

 

가장 많이 사용되는 장치는 하드디스크
하드디스크는 원형의 플레이트(plate)로 구성되어 있고, 이 플레이트는 논리적으로 트랙으로
나뉘며 트랙은 다시 몇 개의 섹터로 나뉨
원형의 플레이트는 초당 빠른 속도로 회전하고, 회전하는 플레이트를 하드디스크의 액세스 암
(arm)과 헤더(header)가 접근하여 원하는 섹터에서 데이터를 가져옴
하드디스크에 저장된 데이터를 읽어 오는 데 걸리는 시간은 모터(motor)에 의해서 분당 회전
하는 속도(RPM, Revolutions Per Minute), 데이터를 읽을 때 액세스 암이 이동하는 시간
(latency time), 주기억장치로 읽어오는 시간(transfer time)에 영향을 받음

 

하드디스크는 느림 버퍼에 읽어들이면서 일을 계속 시킴
LRU 최근에 가장 적게 사용된 거 교체하는 알고리즘 적용해서 dBMS는 엑세스 시간 속도를 조정한다.
데이터 검색할때 dbms 버퍼 풀에 저장되서 작업한다.

 

액세스 시간

 

탐색시간(seek time, 액세스 헤드를 트랙에 이동시키는 시간)

• 회전지연시간(rotational latency time, 섹터가 액세스 헤드에 접근하는 시간)
• 데이터 전송시간(data transfer time, 데이터를 주기억장치로 읽어오는 시간)

인덱스부여하고 검색해서 뽑아옴, 트리구조사용

 

우린 SQL INterface 부분 계속 배우는중
각테이블은 인덱스를 가지고있다. primary key 도 인덱스를 가지고있다.
확장자 .idb
폼파일 : 데이터, 스키마 구조 따로 분류

 

MySQL InnoDB 엔진 데이터베이스의 파일

 

인덱스와 B-tree
balanced tree
인덱스(index, 색인) : 도서의 색인이나 사전과 같이 데이터를 쉽고 빠르게 찾을 수
있도록 만든 데이터 구조

 

각 노드들이 같은 레벨에 전제한다. 균형에 맞춰있다.
루트: 스키마 (데이타베이스) 밑에-> 테이블 밑에-> 속성 밑에-> 도메인

 

인덱스의 특징
인덱스는 테이블에서 한 개 이상의 속성을 이용하여 생성함
빠른 검색과 함께 효율적인 레코드 접근이 가능함
순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지함
저장된 값들은 테이블의 부분집합이 됨
일반적으로 B-tree 형태의 구조를 가짐
데이터의 수정, 삭제 등의 변경이 발생하면 인덱스의 재구성이 필요함

 

인덱스 설명 영상:
https://www.youtube.com/watch?v=edpYzFgHbqs&t=833s

MySQL 인덱스의 종류

 

 

인덱스의 생성 문법

 

Book 테이블의 bookname 열을 대상으로 비 클러스터 인덱스 ix_Book을 생성하라.

create index ix_Book on book(bookname);

 

Book 테이블의 publisher, price 열을 대상으로 인덱스 ix_Book2를 생성하시오

create index ix_book2 on book(publisher,price);

 

확인

show index from book;

 

인덱스 동작확인
select * from book where publisher = '대한미디어' and price >= 30000;
이거 실행하고 Query -> explain current statement

회고

 

생각보다 외울 게 많다. 어제 배운내용을 자세히복습하지 않고 OS 공부에 시간을 썼다. 이정도면 충분하다고 생각했다. 오늘 아침부터 푼 문제들을 보니 더 배움이 필요했다. join, group by, order by 각각을 사용할 수는 있지만 복합적으로 되면 난 잘 못 풀었다.
문제 문해도 잘 못해서 틀린것도 있다. 각각을 충분히 할 줄 안다고 생각했기에 일어난 일이다.

 

오늘 금요일이다. 이번 주말에 이번 주 풀었던 sql 문제를 다시 풀어 볼 생각이다. 복습의 시간이 꼭 필요하다.
반성하자.