2024 05 03 SQLD [고급문법]

김필립·2024년 5월 3일
sql

SQL

목록 보기
7/10

고급 SQLD

📚 뷰

  • FROM 문에 들어가는 서브쿼리 [복제된 릴레이션]

📚 인덱스

  • 목차 및 순서를 나타내는 번호

📙 내장함수

  • 내장함수는 잘 사용하지않음

📘 내장함수의 기본

  • 함수란 단어가 나오면믹서기를 생각하자
    • INPUT,OUTPUT이 모두 존재함

📗 SQL 내장함수

  • 모든 내장함수는 생성시 선언해주어야함

📙ABS

  • 절대값

📙ROUND

  • 반올림

📙SUBBSTING

  • ~부터 ~까지 [슬라이싱]

📙CONCAT

  • 연결해주다

📙SYSDATE,GETDATE

  • DATE를 받아올때 사용

📙CAST,CONVERT

  • 변환하다.

📚 숫자 함수의 종류 📌

  • ROUND(숫자)
    • ROUND 의 끝자리는 어디까지의 반올림 처리를 할건지 정해주는 것
  • CEILING(숫자)
  • 어떨 때 사용할까? 터널크기 등의 값을 계산할 때 사용
  • FLOOR (숫자)
  • 소수점을 떼고싶을 때 사용한다.

📃 숫자함수의 기본 예제들 [SELECT에서 사용 가능]

📘 문자함수

  • CHARINDEX(STR1,STR2)
    • STR 1이 STR2의 부분 문자열[포함되어 있어야함]
      그렇다면 어디까지 포함되어있는지 반환하는 친구
  • LEFT ,LIGHT
    • 오른쪽 또는 왼쪽의 결과값
  • TRIM(STR)
    • 공백제거 R,L이 붙으면 오른쪽 왼쪽
  • REPLACE (STR1,STR2,STR3)
    • 변환하고싶을때 ABCD , CD ,EF
      → ABEF로 바뀜 그래서 STR 3개들어감

📃 REPLACE : 문자열을 치환하는 함수

LENGTH : 글자를 세어주는 함수(문자의 단위)

  • 공백도 포함 됨

SUBSTRING : 지정한 길이만큼 문자열 반환

📚 날짜 및 시간 함수!

📚 날짜 및 시간함수

  • 날짜가 04 또는 4로 나오고싶다면 추가처리를 해야함 MM,M 등

📚날짜 및 시간함수의 DATE ADD 자동 주문확정 기능 만들기

📚 SYSDATETIME,DAYS함수

  • 함수 안에 함수 넣는거 가능

📚 NULL 값 처리

  • NULL은 >,=,< 비교 불가
  • 집계함수를 사용할때 주의
  • NULL이 하나라도 들어가면 NULL 반환

📚 NULL 값 처리 기본 예제

  • 집계함수는 NULL을 배제하기때문에 NULL이 들어가있어도 포함하지 않음

📚 NULL 값 처리 (1)

📚 NULL값을 확인하는 방법

  • IS NULL, IS NOT NULL을 사용해야함

📚IS NULL(): NULL 값을 다른값으로 대치하여 다른 값으로 출력

💨 TOP n 질의 [가장 많이 쓰이고 시험에 나옴]📌

  • TOP n와 LIMIT의 차이점
    • TOP n
      처음부터 ~개를 뽑아오는 조건을 검
    • LIMIT
      모든 값을 가져온 후 조건대로 표시함

💨집계함수에 NULL이 포함되어있으면 모두 NULL로뜸 [COUNT는 제외 0으로뜸]

📚 연습문제 풀이

  • (SELECT * FROM)b / INLINE VIEW 를 뜻함

부속질의

📂 부속질의 의 기본 뜻

🤔 부속질의란?

  • 다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 가공할때 사용
    • JOIN은 일단 합치는데 부속질의는 필요한 데이터만 찾아서 공급해줌

📂 부속질의 종류

  • 영문 및 동의어가 운이 안좋게 포함 될 수 있으니 알아둬야한다.
    • INLINE[FROM 테이블] ,SCALAR [SELECT], 나머지는 모두 중첩질의다. 생각하기

🤔상관관계,비상관관계를 나누는 조건?

  • 🤔 "이 에제는 비상관 관계이다. 왜?"
    • 부속질의로 인해서 메인이 변화되지 않기때문에
      [부속질의로 메인이 영향이있어야 상관관계]

📚 중첩질의 -WHERE 부속질의

  • 기억해야하는 것
    • Scalar ,inline
  • 한정에서 ALL,SOME(ANY)는 [아무거나 한가지만 맞으면 가져온다 라는 뜻]

📂 비교연산자

  • 둘다 단일행,단일 열이어야 처리 할 수 있다.

🤔 IN,NOT IN

  • 하나라도 있거나,없으면 참

📂IN 예제

🤔ALL,SOME(ANY) 란?📌

  • 📂 질의 4-14 예제코드
  • MAX대신 ALL사용가능 saleprice가
    [내가 산 모든것중에 다 크면 표시해]

📂EXISTS,NOT EXISTS

  • EXISTS [LOW가 완벽히 존재한다면]
  • IN은 하나라도 존재한다면.
  • EXISTS 그곳에 존재한다면(부속질의) SUM 합치겠다.

📂스칼라 부속질의 -SELECT 부속질의

  • 상수 = 스칼라[SELECT] =변하지 않는 값
  • 연관이 되든 안되든 모두 사용가능

📂스칼라 부속질의 코드예제

  • 보통 WHERE 절에 많이 넣는데 셀렉트에서도 넣을 수 있다. [알고만 가자]

📂 인라인뷰-FROM 부속질의

  • 인라인뷰를 사용할땐 임의로 이름을 지정해줘야한다.

📚 인라인뷰 연습문제 풀이

뷰(VIEW)

📂 뷰[생성,수정,삭제]

  • 뷰란?
    하나 이상의 테이블을 합하여 만든 가상의 테이블
  • 보안성의 장점 = 캡슐화와 유사
    클라이언트는 뷰만 봄

📂 뷰의 작동원리 그림표

  • 뷰?
    • 바로가기 아이콘 [원본이 삭제되면 작동X]
  • 뷰를 언제 만드는 걸까?
    • 수정,삽입,삭제가 빈번할때
    • 여러 테이블에 묶어서 쿼리를 작성해야할때
    • (검색) 읽기전용을 만들 때

📂 뷰 생성방법

  • VIEW 를 생성할때 AS를 써주고 뒤에 생성될수있는 SELECT FROM문을 생성을
    해주어야 된다

📜 뷰 생성 예제

📜 뷰 생성 예제 (2)

📜 뷰의 수정

  • 뷰를 수정할때는 VIEW에 대응되는 친구들이 모두 동일해야함

🔑 뷰의 삭제

인덱스

🤔 인덱스란?

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

  • DBMS = 데이터를 고유한 방법으로 저장
    CD

📜 데이터베이스의 실제 물리적저장

  • 가장 많이 사용되는 장치는 "하드디스크"

  • 엑세스 [접근타입]

    • 또는 탐색시간이라고도 부름

🤔 하드디스크 그림표 📌

  • 섹터 = 트랙

📜 메모리 풀

  • 데이터를 검색했을 경우에 버퍼 [안에없으면] Memory Pool 안에 있는 정보를 검다시불러옴

🔑 데이터 주요파일📌

  • 트렌젝션
    • 살기어ㅣ해
  • 로그란?
    • 작업을 기록,검사 기능을 가짐 [체크용]

📜 인덱스와 B-tree📌

  • B-tree는 뭔가요? [Blance tree]
    • 대량의 데이터를 효율적으로 저장하고 탐색학 위해 사용

📜 B-TREE 인덱스📌 [키와 포인터]

  • 키는 오름차순으로 저장
  • 키는 오른쪽 왼쪽 모두 포인터가 지정되어있어 거맥에 용이함
  • 단점: 데이터 설정 삭제 변경이 발생하면 데이트

📜 클러스터드(CLUSTERED)인덱스

  • 연속된 키 값의 레코드를 묶어서 같은 블록에 저장
    • 하나의 인덱스 안에 한개만 생성 가능하다.
      [물리적,논리적 위치가 동일하다 = 한개밖에 못만든다]

📜 비클러스터드(NON CLUSTERED) 인덱스

  • 사용이유:
    • 내가 원하는만큼 여러개를 만들 수 있어서[인덱스만큼 만들어서 정렬하겠다.]

🔑 클러스터 인덱스는?

  • 클러스터 인덱스
    • 직관적으로 바로 찾을 수 있다.[검색속도빠름]
  • 보조인덱스
    • 기본키 속성값을 찾고 클러스터 인덱스에서 해당 레코드를 찾는다 [메모리낭비] 많아봤자 2개이다.

🔑 인덱스 생성시 고려사항

  • 🤔 "인덱스를 왜만드나요?"
    • 검색속도를 높히기 위해서 ↑
  • 인덱스는 보통 1~2개 정도밖에 사용하지 않음
  • 유일키 존재 기본값 = 비 클러스터드 인덱스

🤔 " 속성의 선택도? "

  • PK [유일키]선택도가 낮음 사용성 ↑
  • 내가 선택할 폭이 넓음
  • 인덱스는 왠만하면 UNIQU[유일키]에 적용된다.

🤔 인덱스를 너무많이 사용하게되면?

  • 의미없는 인덱스가 많아지면 검색속도 ↓
    • 찾는 시간도 길어지고 메모리사용률 ↑

📂 인덱스 생성 예제


_ 사용자에게 서비스 제공의 이유임

📂 인덱스의 재구성과 삭제

REBUILE

  • 재생성하겠다

DISABLE

  • 활성화를 비활성화로 바꾸겠다.
  • VIEW 처럼 수정보단 삭제 후 생성이 더 나은방향이다.

REORGANIZE

  • 최신화 시키겠다 [성능적으로 조금이라도 상향시킬때는 사용함[
    보통 대용량 데이터에서 사용하는 듯

🧭프로그램에서 시간이란?

  • 시간은 곧 비용이다. 시간이 늘어날 수록 비용이 상승함

중간정리 [VIEW , INDEX는 문법적인 부분을 중점으로 이해해야할 것 같다]

SQL 기본 활용 및 활용 2

SQL PART 2 [계층형 쿼리 ,윈도우 함수]

🧭 관계형 데이터 베이스

  • RDDNS [MY SQL ORACLE등 ]
  • 계층형 ,네트워크형 데이터베이스 ,관계형 데이터베이스
    계층형 , 네트워크형 [한물 감]

📂 관계형 데이터 베이스 특징

  • 릴레이션을 사용하여 집합연산,관계연산 가능
  • 출제자들은 곱집합을 좋아한다.

📂 테이블의 구조

스키마: 속성이름과 도메인, 유니크 , 이즈 널

  • DATA (레코드) 속성값
  • 튜플 갯수 : 카디날리티
  • 열의 갯수 : 디그리
  • PK : 클러스터드 인덱스가 붙음[정렬하지 않아도 정렬해줌]
  • 외래키 : 다른 테이블의 기본키를 참조

📂SQL 구조

  • 데이터 구조 정의,데이터조작,데이터제어 등 절차형언어
  • 관계형 데이터 베이스는 데이터베이스를 연결 SQL문을 사용하여 누구나 쉽게 사용할 수 있게함
  • SQL = ANSI / SIO [ANSI나오면 맞았다고 생각하기]
    • DBMS가 제공하는내장함수때문에 관리시스템이 바뀌어도 그대로사용 가능

📂 TCL (트랜젝션 컨트롤 랭귀지)[지금은 DCL로 병합[]

  • COMMIT , ROLLBACK ,SAVEPOINT[TCL]

📂 SQL 종류

📂 트랜잭션의 종류 📌

  • 영어로도 나오니 영어기억하기

🧊원자성(ATOMICITY)

  • ALL OR NOTHING [전부 실행되거나 전혀 실행X]

🧊일관성(CONSISTENCY)

  • 모순되지 않아야한다.

🧊고립성(ISOLATION)

  • 다른 트랜잭션이 접근할 수 없다. [동기화]

🧊영속성(DURABLILTY)

  • 트랜잭션 실행성공 → 다른작업을 할때까지 영구적 보장

🧊DDL

🧊 DDL 테이블 생성 [제약조건사용]

🧊참조무결성 = CASCADE [연쇄작용]

🧊 RENAME[이름변경하겠다.]

  • 칼럼추가 ADD
  • 칼럼변경 MODIFY

🧊 DDL

테이블삭제

🧊 DML

  • INSERT INTO (짝꿍)
  • NOlogging
    • log를 그냥 두면

🧊 UPDATE문

  • UPDATE SET [짝궁]
  • DELETE FROM WHERE[짝꿍]

🧊 SELECT 문

  • FROM → WHERE → SELECT
    업로드중..업로드중..
  • Order by 사용한 정렬

업로드중..

🧊 Order by를 사용한 정렬

🧊Index를 사용한 정렬 회피

Distinct [중복제거]

Alias [AS 별명]

📁 WHERE 문 사용

  • WHERE문이 사용하는 연산자

📁 Between 문

Between문은 지정된 범위의 값을 조회 [>=<]

업로드중..

IN문 "OR"의 의미를 가지고 있음 [하나의 조건만 만족 = 출력]

[사진받기]

📁 IS NULL [NULL이거나 NULL이 아니라면]

📁 GROUP BY 연산

  • GROUP BY = HAVING[조건식]
  • COUNT 함수

📁SELECT문 실행순서 📌

FROM →WHERE→ GROUP→ HAVING →SELECT →ODERBY

📁 명시적 형변환과 암시적 형변환

  • 암시적 형변환은 개발자가 형변환 안해줬을때 DBMS가 자동으로해주는걸 말함

🍱 내장형 함수

Dual 테이블

  • Oracle 데이터 베이스의 기본 더미테이블이다.

💨문자열 함수

💨숫자형 함수

💨 날짜형 함수

💨 DECODE

  • 삽합연산자 같다 ? TURE , FALSE

💨 CASE

  • IF문과 상당히 유사함
    첫번째 WHEN이후엔 ELSE IF 와 같음

🧨RowNum (행번호)

  • oracle 데이터 베이스의 select문 결과에 논리적인 일련번호 부여

🧨 ROWID

  • ROWID 데이터 베이스 내에서 데이터를 구분할 수 이는 유일 한 값
    + 동일 한 값이어도 ROWID 가 다르면 다른 친구이다. [ORACLE기준]

🧨 WITH 구문

[WITH안에서만 임시테이블을 사용할 수있음 종속적][CREAT VIEW 는 한번 생성하면 삭제 X **영구적**]

  • 임시테이블이나 뷰처럼 사용 할 수 있는 구문

❎ DCL

  • GRANT
    • 사용자 데이터 권한 부여
      ON [테이블이름][부여할 권한]
  • REVOKE
    • 사용자 권한 회수
      [있어야함 권한이]

🎃 TCL

  • COMIT [영속성]
  • 실제로 반영되는 데이터 / 트렌젠션이 끝나면 저장해주는 친구

📚ROLLBACK [원자성]

📚SAVEPOINT

  • 트랜잭션을 작게 분활하여 관리 지정된 위치 이후 트랜잭션에서만 ROLLBACK가능

🎇 JOIN [조인]

EQUI(등가)조인(교집합)

  • 새로운 릴레이션을 만드는과정
    기본은 교집합

🎇INNER조인

  • 보통 WHERE 존에 쓰는경우가 많다.

🎇 INTERSECT연산

  • NON EQUI(비등가 조인)

🎇OUTER JOIN

  • LEFT , RIGHT OUTER 조인
  • CROSS 조인?📌
  • JOIN조건 구 없이 2개의 테이블을 하나로 조인
  • 조인구가 없으니 카티션 프로덕트를 사용

♻ UNION을 사용한 합집합

  • 중복 및 정렬을 해줌

♻ UNION ALL

  • 중복제거 X 정렬 유발 X
    • 투표 선거 같은 [중복을 하면 안되는 것]

♻ 차집합을 만드는 MINUS[ORACLE]

  • 교집합 부분이 날라가는 차집합

📗 계층형 조회 (Connect by)

📗서브쿼리 (Subquery)

📗 IN,ALL,EXSTS

📗그룹함수 (Group Function)

  • 두개가 바뀌어도 결과는 변하지 않는다
    • 부서별로 영업이익,직업별 영업이익 등의 수익을 합칠때 사용하기 편하다.

💱 CUBE 함수 [부서별 조건을 넣어주면 합계를 구해줌]

  • CUBE는 제시한 칼럼에 대해서 결합 가능한 모든 집계를 계산한다.

💱 윈도우 함수 (Window Function)

  • 순위 함수
  • 집계 함수

💱 테이블 파티션

[range 범위]

💱list Partition

💱Hash Partition

[hash는 함수사용]

💱Partition Index

  • 연습문제 PASING -> EX -> PATH순이 맞음
profile
김필립
기억에 남는 사람입니다
이전 포스트

2024 04 29 SQL [데이터 정의어]

다음 포스트

2024 05 07 SQL [데이터 모델링]

0개의 댓글