[모각코 6주차] 데이터베이스 SQL 고급

오랜만에 돌아온 모각코!

백엔드 공부하면서(?) sql을 복습하고자 

잠시 정리해보는 시간을 가졌다. 

---

 

내장 함수

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

숫자 함수

• ABS: 절댓값
• ROUND: 반올림
  • 고객별 평균 주문 금액을 백 원 단위로 반올림한 값
  • SELECT custid "고객번호", ROUND(SUM(saleprice)/COUNT(*), -2) "평균금액" FROM Orders GROUP BY custid;

문자함수

• REPLACE : 문자열 함수를 치환
  • 도서 제목에 야구가 포함된 도서를 농구로 변경한 후 도서 목록을 보여라
  • SELECT bookid, REPLACE(bookname, '야구', '농구') bookname, publisher, price FROM Book;
• LENGTH : 글자의 수를 세어주는함수
  • 굿스포츠에서 출판한 도서의 제목과 제목의 글자 수를 확인하기
  • SELECT bookname "제목", LENGTH(bookname) "글자수", LENGTH(bookname) "바이트 수" FROM Book WHERE publisher = '굿스포츠';
• SUBSTR : 지정한 길이만큼의 문자열을 반환
  • 마당서점의 고객 중에서 같은 성을 가진 사람이 몇 명이나 되는 지 성별 인원수 구하기 
  •  
  • SELECT SUBSTR(name, 1, 1) "성", COUNT(*) "인원" FROM Customer GROUP BY SUBSTR(name, 1, 1)
  • 근데 *이 정확히 뭐임???????

날짜 시간 함수

• 마당서점은 주문일로부터 10일 후 매출을 확정한다. 각 주문의 확정일자 구하기
• SELECT orderid "주문번호", orderdate "주문일", orderdate+10 "확정" FROM Orders;
• TO_DATE : 문자형으로 저장된 날짜를 날짜형으로 변환하는 함수
• TO_CHAR : 날짜형을 문자형으로 변환하는 함수
  • 마당서점이 2020년 7월 7일에 주문받은 도서의 주문번호, 주문일, 고객번호, 도서번호를 모두 보이라, 단 주문일은 yyyy-mm-dd요일 형태로 표시
  • SELECT orderid "주문번호", TO_CHAR(orderdate, 'yyyy-mm-dd dy') "주문일", custid "고객번호", bookid "도서번호" FROM Orders WHERE orderdate=TO_DATE('20200707', 'yyyymmdd');
• SYSDATETIME : 오라클의 현재 날짜와 시간을 반환하는 함수
• SYSRIMESTAMP : 현재 날짜, 시간과 함께 초 이하의 시간과 서버의 TIMEZNOE 까지 출력

NULL값 처리

• 아직 지정되지 않은 값, 비교 연산자로 비교 불가
• NULL 값의 산술 연산ㅇ르 수행하면 결과도 NULL
• 비교연산 → Unknown, 논리연산 → Ture, false
• 집계함수 계산 시 NULL이 포함된 행은 집계에서 빠짐.
• IS NULL, IS NOT NULL
• NVL : NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력
  • 이름, 전화번호가 포함된 고객 목록을 보이라, 전화번호가 없는 고객은 ‘연락처 없음’ 표시
  • SELECT name "이름", NVL(phone, '연락처없음') "전화번호" FROM Customer;

ROWNUM

• 내장함수는 아님,
• 오라클 내부적ㅇ로 생성되는 가상 컬럼
• SQL 조회 결과의 순번을 나타냄.
• 자료를 일부분만 확인하여 처리할 때 유용함
  • 고객 목록에서 고객번호, 이름, 전화번호를 앞의 두 명만 보이라
  • SELECT ROWNUM "순번", custid, name, phone FROM Customer WHERE ROWNUM <= 2;

subquery

• 하나의 sql 문 안에 다른 sql 문이 중첩된 질의
• 다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할 때
• 조인보다 필요한 데이터만 찾는 부속질의가 성능이 좋다
• 주질의 와 부속질의로 구성됨
• 종류
  • 중첩질의 / WHERE 절
    • 비교연산자: =, <, > → 반드시 단일 행, 단일 열, 상관 가능
    • 평균 주문 금액 이하의 주문에 대해서 주문번호와 금액을 보이라
    • SELECT orderid, slaeprice FROM Orders WHERE saleprice <= (SELECT AVG(saleprice) FROM Orders);
    • 각 고객의 평균 주문 금액보다 큰 금액의 주문 내역에 대해서 주문번호, 고객번호, 금액을 보이라

      SELECT orderid, custid, saleprice
      FROM Orders, Customers 
      WHERE saleprice > (SELECT AVG(saleprice)
      									 FROM Orders
      									 WHERE custid = orderid);
      

    • 이 봐 .. 이거 어찌해 od랑 so는 내가 만드는거냐고…. 그리고 Customers는 왜 FROM에 안 적어?
    • 집합 연산자: IN, NOT IN → 다중 행, 단일 열, 상관 가능주질의는 WHERE 절에 사용되는 속성 값을 부속질의의 결과 집합과 비교해 하나라도 있으면 참이 된다.
      • 대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오

      SELECT SUM(price) "total"
      FROM Orders
      WHERE custid IN (SELECT custid 
      								 FROM Customer 
      								 WHERE address LIKE %대한민국%);
      

    • IN은 주질의 속성 값이 부속질의에서 제공한 결과 집합에 있는 지 확인, 다중 행을 가질 수 있음.
    • 한정 연산자: ALL, SOME(ANY) → 단중 행, 단일 열, 상관 가능
      • 3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 금액을 보여

      SELECT orderid, saleprice 
      FROM Orders
      WHERE saleprice > ALL (SELECT saleprice 
      											 FROM Orders
      											 WHERE custid='3');
      

    • ALL은 모두, SOME은 어떠한 이라는 의미를 가짐.
    • 존재 연산자: EXISTS, NOT EXISTS → 다중 행, 다중 열, 상관 필수!주질의에서 부속질의로 제공된 속성의 값을 가지고 부속 질의에 조건에 만족하여 값이 존재하면 참, 주질의는 해당 행의 데이터를 출력함.
      • EXISTS 연산자로 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액을 구하시오

      SELECT SUM(saleprice) "total"
      FROM Orders od
      WHERE EXISTS (SELECT *
      							FROM Customer cs
      							WHERE cs.address LIKE %대한민국% AND cs.custid=od.custid);
      

    • 데이터의 존재 유무를 확인하는 연산자
  • 스칼라 부속질의 / SELECT
    • SELECT 절에서 사용되는 부속질의
    • 부속질의의 결과 값을 단일 행, 단일 열의 스칼라 값으로 반환
    • 스칼라 부속질의는 원칙적으로 스칼라 값이 들어갈 수 있는 모든 곳에서 사용 가능
    • 일반적으로 SELECT 문과 UPDATE SET 절에 사용됨
    • 주질의와 부속질의와의 관계는 상관/비상관 모두 가능
      • 마당 서점의 고객별 판매액을 보이시오(결과는 고객이름과 고객별 판매액 출력)

      SELECT (SELECT name 
      				FROM Customer cs
      				WHERE cs.custid = od.custid) "name", SUM(saleprie) "total 
      FROM Orders od
      GROUP BY od.custid;
      

      • Orders 테이블에 각 주문에 맞는 도서 이름을 입력하시오

      ALTER TABLE Orders ADD bookname VARCHAR2(40);;
      
      UPDATE Orders
      SET bookname = (SELECT bookname
      								FROM Book
      								WHERE Book.bookid = Orders.bookid);
      

  • 인라인 뷰 / FROM 부속질의
    • FROM 절에서 사용되는 부속질의
    • 테이블 이름 대신 인라인 뷰 부속질의를 사용하면 보통의 테이블과 같은 형태로 사용
    • 부속질의 결과 반환되는 데이터는 다중 행, 다중 열이어도 상관없다
    • 다만 가상의 테이블인 뷰 형태로 제공되어 상관 부속질의로 사용될 수는 없다
      • 고객번호가 2이하인 고객의 판매액을 보여라 (결과는 고객이름과 고객별 판매액 출력)

      SELECT name, saleprice "total"
      FROM (SELECT custid, name
      			FROM Customer 
      			WHERE custid <= 2) cs, 
      			Orders od 
      WHERE cs.custid = od.custid
      GROUP BY cs.name;
      

뷰(view)

• 하나 이상의 테이블을 합하여 만든 가상의 테이블
• 장점
  • 편리성 및 재사용성 → 자주 사용되는 복잡한 질의 미리 정의, 일반 테이블처럼 사용하여 편리
  • 보안성 → 필요한 데이터만 선별하여 보여줄 수 있어 중요한 질의 암호화 가능
  • 논리적 데이터 독립성 제공 → 개념 스카의 디비 구조가 변하여도 외부 스키마에 영향을 주지 않도록 함.
• 특징
  • 원본 데이터 값에 따라 같이 변함
  • 독립적인 인덱스 생성이 어려움
  • 삽입, 삭제, 갱신 연산에 많은 제약이 따름
  • 뷰의 SELECT 문은 Data Dictionary에 저장되며, 뷰의 조회 시 읽어와서 실행됨.

!! AS SELECT !!

• Book 테이블에서 ‘축구’라는 문구가 포함된 자료만 보여주는 뷰
• SELECT * FROM Book WHERE bookname LIKE '%축구%';
• 위 SELECT 문을 이용해 작성한 뷰 정의문
• CREATE VIEW vw_Book AS SELECT * FROM Book WHERE bookname LIKE '%축구%'
• 주소에 ‘대한민국’을 포함하는 고객들로 구성된 뷰를 만들고 조회하기 단, 뷰의 이름은 vw_Customer
• CREATE VIEW vm_Customer AS SELECT * FROM Customer WHERE address LIKE '%대한민국%';

인덱스

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

• 하드디스크
  • 원형의 플레이트로 구성, 이 플레이트는 논리적인 트랙으로 나뉨, 트랙은 다시 섹터로 나뉨
  • 플레이트는 초당 빠른 속도로 회전,
  • 회전하는 플레이트를 하드디스크의 액세스 암과 헤더가 접근하여 원하는 섹터에서 데이터 가져옴
  • HDD에서 저장된 데이터를 읽는 데는 시간이 오래걸려 모터에 의해 분당 회전하는 속도로
  • 액세스 암이 이동하는 시간, 주기억장치로 읽어오는 시간에 영향을 받는다.
• 액세스 시간
  • 탐색시간 + 회전지연시간 + 데이터 전송시간
• 오라클의 주요 파일
  • 데이터파일
    • 운영체제상 물리적 존재
    • 사용자 데이터와 개체를 저장
    • 테이블과 인덱스로 구성
  • 온라인 리두 로그
    • 데이터의 모든 변경사항 기록
    • 데이터베이스 복구에 사용되는 로그 정보를 저장한다
    • 최소 2개
  • 컨트롤파일
    • 데이터 파일, 로그파일의 위치정보를 저장한다
    • 디비 변경사항이 있을 때 자동으로 업데이트
    • DB의 마운트, 오픈의 필수파일
    • 복기 시 동기화 정보 저장
• 오라클 저장 영역
  • Tablespace > Segment > Extents > Block
• 인덱스의 필요성
  • 질의 검색 시 data block을 읽는 횟수의 최소화가 필요함
  • Disk에 있는 데이터는 memory에 있는 데이터에 비해 읽는 속도 1000배 소요
• 인덱스의 특징
  • 한 개 이상의 속성
  • 빠른 검색, 효율적인 레코드 접근
  • 순서대로 정렬된 속성, 데이터의 위치만 보유 → 테이블보다 작은 공간 차지
  • 저장된 값들은 테이블의 부분집합
  • 일반적으로 B-tree의 형태 구조
  • 데이터 수정, 삭제 → 인덱스 재구성 필요
• 인덱스 종류
  • B-Tree 가장 기볹거인 거
  • IOT
  • Bitmap Index
  • Function-base index
• 인덱스 생성 시 고려사항
  • WHERE 절과 join에 자주 사용되는 속성이어야 함
  • 단일 테이블에 인덱스가 많으면 속도가 느려질수도 4~5개 권장
  • 속성이 가공되는 경우 사용x
  • 속성의 선택도가 낮을 때 유리 → 속성의 모든 값이 다른 경우
    • selectivity = 1 / (서로 다른 값의 개수)
• 인덱스 생성
• CREATE INDEX 인덱스 이름 ON 테이블(대상);
• 인덱스 재구성과 삭제
  • 단편화 제거를 위해 REBUILD 사용

  ALTER INDEX 인덱스이름  REBUILD;