DAY 5 DQL문

NA YE SOM·2023년 7월 4일
0
post-custom-banner

<복습>
1. DML(Date Manipulation Language)
COMMIT - 실행했던 쿼리문도 취소가능하다(COMMIT이전 데이터으로)
ROLLBACK

-DATE(날짜데이터) : ' '(작은따움표) /(슬래시) -(하이픈) 이용하여 날짜 구분

-ON DELETE SET NULL DEPARTMENT 에 부서번호가 지워지면 부서정보만 NULL 값이 된다 ( 부서가 없어지면 부서 정보만 사라질 수 있게)

-NOODER (제공되는 번호가 굳이 순서대로 제공하지 않아도 된다)
->번호를 순서대로 사용하기 위해서 ORDER옵션(Sequence)

-CREATE SEQUENCE DEPT_SEQ ORDER;
(INCREMENT, START, NOMAXVALUE, NOMINAVLUE, NOCYCLE, CACHE 다 사용하고 마지막에 NOORDER를 ORDER로 사용한다)

  1. 수정
    UPDATE 테이블
    SET 업데이트할내용, 업데이트할내용(회원가입 후 비밀번호를 바꿈, 유투브 조회수 증가)
    WHERE 조건식(누구의 비번을 수정?, 어떤 유투브의 게시물 조회수 증가?)

지역이 저장된 칼럼 이름 (LOCATION)

-'인천'이라고 하는곳으로 변경(SET LOCATION = '인천')

  • (대입연산자 = 뒤에 있는 값을 앞으로 보내주세요)

  • 숫자는 ''(작은따움표)로 묶지 X
    --WHERE절의 등호(=)는 동등비교연산자 (정말 같은지 아닌지 비교하는것)



-줄맞춤 : SET 절앞 (띄어쓰기 3개), WHERE 절앞(띄어쓰기 1개)

  • 0행이 업데이트되었습니다 - 실패(1행이 업데이트되었습니다 - 성공)

  • COMMIT전이니 취소가능 ('인천' -> '대구' 변경)

  • 등호의 오른쪽을 먼저 작업 -> 왼쪽으로 보내줌 (대입연산자 사용)


    -> 2개 행 이(가) 업데이트되었습니다(2개가 업데이트됨)

<삭제>

-줄맞춤 : FROM절(띄어쓰기2개), WHERE절(띄어쓰기1개)

  • 부서가 사라지면 사원이 NULL값으로 처리된다
    (대구만 남음)
  • 사원테이블 확인(서울에 근무하는 2명의 사원 -> NULL처리됨)(실제로는 써있지 않고 빈칸임) -> ★이렇게 동작하는 이유는 "외래키 옵션"때문이다(외래키 삭제 옵션이 없으면 삭제가 되지 않음)

♥ 기능구현 할때 자주 사용(외래키 만들기)

  • ON DELETE SET NULL 없이 TALBE 재 진행


삭제 옵션이 사라지면 거부함
(무결성 제약조건이 위배되었습니다 - 자식 레코드가 발견되었습니다)

- ON DELETE CASCADE
(서울이 없어지면 서울에 근무하는 사원도 싹 날라감)

언제 접속했는지 여부를 저장하는 테이블 따로 있음
-외래키 -> 접속에 있음(FK)
-삭제옵션이 없으면 : 탈퇴가 안됨(접속기록이 없으면 탈퇴가 안되는건 말이 안됨)
★-회원이 나갔을때 접속 기록 날릴 것인가(함께 삭제)- 요즘 추세_
"ON DELETE CASCADE"로 서비스 구현할 것
-회원이 나갔을때 접속 기록 남길 것인가

DDL : CREATE,ALTER,DROP, TRUCATE
DML : INSERT, UPDATE, DELETE
TCL(CONTROL LANGUAGE) : COMMIT, ROLLBACK
DCL : GRANT(권한줄때),REVOKE
DQL : SELECT

  1. DQL
    -핵심이 되는 언어 : 조회를 가장 많이 함 -> 어떤 데이터가 있는 찾는것
    -SELECT문을 이용해 테이블에서 원하는 내용만 조회할 수 있음

기본구문

  • 수행순서가 나중에 중요해짐
    SELECT 칼럼1, 칼럼2..
    FROM 테이블이름
    [WHERE 조회    조건]
    [GROUP BY 그룹_칼럼][HAVING 그룹_조건]
    [ORDER BY 정렬_칼럼]

SELECT CODE, MODEL(내가 조회하고 싶은 칼럼 이름을 적음 : COLUMN과 MODEL 칼럼만)/
FROM PRODUCT_T(조회하고 싶은 테이블)

조회할때 칼럼의 이름을 바꿀수 있음
SELECT CODE AS(alias 앨리어스,별명 약어) 코드, MODEL AS 모델(한글 코드로 바꾸고, 한글 모델로 바꾸기)

SELECT * FROM PRODUCT_TBL;(모든 칼럼을 다 보여줘라)

*(에스터리스크 : 모든칼럼) -> 실무에서는 금지!(성능이 떨어짐,감사에 걸림)

수행순서 : (1) FROM 절 먼저 해석 -> (2)SELECT 절 해석
★FROM PRODUCT_T (공백,스페이스) P (PRODUCT_TBL 별명은 공백으로 줌)

★
SELECT P.CODE, P.MODEL, P.CATEGORY, P.PRICE, P.AMOUNT, P.MANUFACTURED FROM PRODUCT_TBL P;

  • .(마침표) : 주인을 명시 (EX) P.CODE(칼럼은 누구꺼다)
  • 필연적으로 테이블의 별명을 주게 되어있다(그렇지 않으면 테이블이 너무 길어짐)

DISTINCT(중복제거)
SELECT DISTINCT CATEGORY FROM PRODUCT_TBL;

  • DISTNICT 위치 : 중복을 제거하고자 하는 칼럼(COLUMN)이름 "앞"에

FROM 절도 안적는 DB도 많음(특별한 테이블을 조회하지 않는다 -> 계산식을 보고싶을때)
(오라클은 테이블까지 필수임)

----------------------------------------------------------------------------실습
HR 계정 소개
NUMBER(6,0) : 전체 6자리 , 소수점 없다
NUMBER(2,2) : 전체 2자리, 소수점 2자리(1이 넘는 숫자 없음) ~0,99(최대)
COMMISSION_PCT : 100(백분율) 나타낼때 사용

7번,
10번(MANAGER_ID), (Foreign key to employee_id column) -> 상사의 사원번호
11번 외래키(2개) : 같은 업무를 보는 사원이 여럿 있을 수 있음

//(1) 상사의 사원번호도 EMPLOYEE_ID 안에 있음
(조인 : 2개 이상을 한번에 조회하는 것)

107명 데이터
조회를 위한 계정

-DEPARTMENTS 칼럼

1:M 관계(하나의 지역에 여러 부서가 있을 수 있음)

FROM(띄어쓰기2칸)

*SELECT문의 조회결과 : TABLE(테이블)

  1. 칼럼 이름에 별명 주기 (한글, 영어 상관없음)
  1. 사원 테이블에서 DEPARTMENT_ID의 중복을 제거하고 조회하기
    부서가 없는 사원도 있음(NULL)
    사원들이 근무하는 부서번호를 보겠음

SELECT문을 많이 씀(실제 서비스 기획할때)
(EX) 웹툰 창 클릭 -> 조회해서 온것(SELECT하는것)(목록, 내용) -> SELECT 2번
만화 내용 -> SELECT , 댓글 -> SELECT



  • NULL 비교 연산자
    IS NULL
    IS NOT NULL

  • 대입연산자 (대입연산자 또는 동등비교연산자 : 위치에 따라서 달라짐)

  • NULL

  • BETWEEN A AND B : A와 B모두 포함된다(해당 번호를 클릭해서 -> 목록화 PAGING 페이징 처리할때 -> BETWEEN AND 사용함) (번호 만드는 것 어려움)

  • OR 쓰거나 IN 써서 식 만들 수 있었음(WEHRE절 식과 동일함)

  • LIKE 패턴 -> 패턴(PATTERN)과 일부 일치함
    ( 등호 대신 LIKE 씀, =은 같은걸 찾는것, 일반적으로 검색어와 똑같은 걸 찾는것 X, (EX) '김'이 포함된 걸 찾는다 -> '김부장')

|| 파이프, 역슬래쉬 - 2개를 붙이는 연산

  • A||B(파이프 (역슬래쉬 위) - 앞에것 2개를 붙이는 연산) : A와 B를 연결

  1. EMPLOYEES 테이블에서 전체 사원의 근무 개월 수를 정수로 조회하시오. 1개월 1일을 근무했다면 2개월을 근무한 것으로 처리해서 조회하시오.

    SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
    , CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) || '개월' AS 근무개월수
    FROM EMPLOYEES;

%(와일드카드) : 만능문자

9.EMPLOYEES 테이블에서 FIRST_NAME이 'J'로 시작하는 사원을 조회하시오.

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'J%';

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'J' || '%' ;

  1. DEPARTMENTS 테이블에서 DEPARTMENT_NAME의 첫 2글자로 그룹화하여 각 그룹의 부서수를 조회하시오. 'IT'와 'Co'인 부서만 조회하시오.

SELECT SUBSTR(DEPARTMENT_NAME, 1,2)
, COUNT(*) AS 부서수
FROM DEPARTMENTS
WHERE SUBSTR(DEPARTMENT_NAME, 1,2) IN ('IT', 'Co')
GROUP BY SUBSTR(DEPARTMENT_NAME, 1,2);

SELECT SUBSTR(DEPARTMENT_NAME, 1,2)
, COUNT(*) AS 부서수
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME LIKE 'IT%'
OR DEPARTMENT_NAME LIKE 'Co%'
GROUP BY SUBSTR(DEPARTMENT_NAME, 1,2);

  • % 모든 문자 만능 문자 -> 와일드카드
    (ex) %김% : 힘주면 김빠짐(웹툰 조회시)

(실습 - 같은값을 가진 테이블 찾기)

SELECT *(모든 칼럼을 조회하겠다)

★ WHERE SALARY BETWEEN 10000 AND 20000; -- 추천(사잇값)
★ WHERE DEPARTMENT_ID IN(30,40,50); -- 추천

-커미션 퍼센트 없으면 : 안받음, 있으면 : 받음

-NULL
-NOT NULL

select * from employees; - 전체 사원을 조회하겠다

(PHONE_NUMBER가 515로 시작하는 번호)

-와일드카드(%만능문자)

  1. '515%' : 515로 시작한다
  • 만능문자 %연산에서는 '등호'가 사용될 수 없다 -> 등호 대신 LIKE사용
  • LIKE '515' || '%'
    (사용자가 검색하기 위해서 전달하는 내용 || 뒤에 어떤 글자가 오든지 상관없다)
    (EX) A라고 치면 자동완성 되서 가지고 올 수 있음

10.-사원 테이블에서 전화번호가 '515'로 시작하는 전화번호의 중복을 제거하여 조회하기
SELECT DISTINCT PHONE_NUMBER (보고싶은건 전화번호만)
-|| 연결연산자(홈케트에이트?)

WHERE

  • WHERE MODEL = 'A120'
    WHERE MODEL = 'a120' 대,소문자가 구분

(ex) last name = King(그대로)
조회하겠다 (데이터베이스에 저장되어있는 데이터들은 대,소문자 구분하기!)

★<실행순서>
PRICE가 300이상인 제품의 MODEL과 PRICE조회하기
가장 먼저 실행 FROM -> WHERE -> SELECT

ORDER BY : 조회결과를 정렬하고자 할 때 사용하는 절

ORDER BY(보고싶은 순서대로 정렬하겠다) (언제나 마지막)
-문자 정렬 : 가나다순 : 오름차순 , 다나가순(역순) : 내림차순
-숫자 정렬 : 작은숫자 - 큰숫자 :** 오름차순**, 큰숫자 - 작은숫자(역순) : 내림차순
-날짜 정렬 : 옛날날짜 -> 최신날짜 : 오름차순, 최신날짜 -> 옛날날짜(역순) : 내림차순

1.EMPLOYEES 테이블에서 JOB_ID를 조회하시오. 동일한 JOB_ID는 한 번만 출력하고 알파벳 순으로 정렬하여 조회하시오.

SELECT(조회) DISTNICT(중복제거) JOB_ID
FREOM EMPLOYEES
ORDER BY JOB_ID;

11.EMPLOYEES 테이블에서 전체 사원을 DEPARTMENT_ID의 오름차순으로 조회하되, 동일한 DEPARTMENT_ID 내에서는 HIRE_DATE의 오름차순으로 조회하시오.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID ASC, TO_DATE(HIRE_DATE, 'YY/MM/DD') ASC;

WHERE절 생략 가능

  • (띄어쓰기 1칸) ORDER BY
  • 오름차순인지 내림차순이지 정해지지 X -> 오름차순으로

*A(오름차순) - 에펠탑 모양

*날짜도 정렬이 됨

-동일한 부서별로 가진 사원들 먼저 조회
-연봉순으로 조회
-,로 2차 정렬 기준 적기 (ORDER BY에 , 나오면 -> 하나씩 끊어서 해석)
1. DEPARTMENT_ID 오름차순으로 정렬
2. SALARY 내림차순으로 정렬
(EX) 인기순, 정렬순 - ORDER BY
☆실무: 5~6차까지 나옴

(오후)

FROM 절이 없음 -> 오라클은 FROM절이 필수임

DUAL(아무것도 가지고 있지 않고 이름만 빌려주는 TABLE)

CREATE SEQUENCE TEST_SEQ ORDER
(순서대로 번호를 써라)

SELECT TEST_SEQ.NEXTVAL FROM DUAL; - SELECT문만 계속 실행시) 새 번호표 뽑아줌

DUAL 테이블
1. DUMMY 칼럼 1개를 가지고 있는 테이블이다.
2. 'X'값을 가지고 있다
3. FROM절이 필요없는 SELECT문을 사용할 때 DUAL 테이블을 이용한다.

--함수 수업방식(종류별로)

  • 데이터타입변환 NULL 수학 문자열 날짜시간 ...
    -'123'(문자) 을 숫자로 변환해 주세요

-> TO_NUMBER('123)의 숫자 결과는 : 123

띄어쓰기 5개 , 띄어쓰기 1개

(공백)(공백)1234
'999999' 9가 6개 -> 6자리로 나타내라 (많이 쓰지X)

  • 함수는 외우지 말자(때 되면 외워짐) -> references용으로 만들어놓기


-> 세자리 쉼표 붙음


-> 숫자, 형식 자릿수 안맞음

24시각제(공공기관) : HH24-> 자정 0시, 밤 11시-> 23시
오후 2시 , 14시 (오전, 오후 같이 표기할건지? 시간만 표기할 건지?)

-> 고용일 DATE에 없음, 오전 12시 = 0시

☆날짜 변화과정 할 줄 알아야 함

TO_DATE
TO_TIMESTAMP
거의 같음


-> SYSDATE 는 시간도 함께 처리하는 함수이다(기본 디폴트값으로 사용할때는 날짜만 사용된다)


-> 접속만 : GD로 수정


-> DT1 DATE타입, DT2 TIMESTAMP타입
SYSDATE SYSTIMESTAMP
DATE TIMESTAMP 차이 : 소수점 차이 (.327000000 있으면(초 밑에 단위까지 필요시) TIMESTAMP)

  • 안되는 쿼리!
  • '23/07/04' 숫자도 작은 따움표 가능
    -> 해결하기 위해서 필요한 것?


(23/07/04 년/월/일 이미 있는것)

  • 날짜는 오라클 디폴트 값도 있고 아닌 것도 있음

TO_DATE : 날짜의 해석법 적어주는 것 -> 날짜를 해석해서 해당날짜로 만들어줌


(SYS 데이터는 YY/MM/DD 년/월/일이 전부가 아님 - 시간 비교가 되지 않아서 비교가 안됨)
(SYS 시간 비교할 수 없음)
★ 날짜 비교시 ) TO_DATE 다 넣기 !

묵시적 타입 변환
(오라클이 알아서 함)

  • 정확한 연산을 위해 오라클에서 데이터 타입을 내부적으로 스스로 변환하는 것

  • SELECT *
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = '150';(숫자 VS 문자 비교 -> 오라클에서 알아서 수행해줌)

    (오라클 내부 변환과정) WHERE EMPLOYEE_ID = TO_NUMBER('150');(150으로 숫자 바꿈)

    (X) WHERE TO_CHAR(EMPLOYEE_ID) = '150'(문자로 바꾸는 것 하지는 않음)

    ★ 1.PK(기본키)로 검색을 시킬 수 있으면 가장 빠름
    -> PK 값 그대로 쓰면 빠름 : EMPLOYEE_ID(값 건드리지 않음) = TO_NUMBER('150;)

    1. PK값을 다른 것으로 감싸면 느려짐 : TO_CHAR(EMPLOYEE_ID) = '150';
  • NUL 처리 함수
  1. NVL(표현식, 표현식의 결과가 NULL인 경우에 사용할 값)
    2/.

  • 값이 없는 경우가 섞여있으면 ) 문제 발생할 수 있음
    (클릭시 부서페이지로 넘어갈때 ) 부서번호가 넘어가도록 코드를 짜놔도 값이 오지 않아서 -> 예외(문제 발생)
    :NULL값 -> 특정 값으로 바꿔서 가져가는 것이 좋음(NULL -> 0으로 바꿔서 가져가기)

NVL(DEPARTMENT_ID,0)
애초에 부서번호가 없다면 0으로 조회해라

  • NVL(NULL VALUE) : NULL값을 그대로 가져가는건 위험할 수 있음

기본연산자에 NULL이 포함되면 -> 결과 ) NULL

SALARY * COMMISSION_PCT(표현식)

NULL보다 값으로 가지고 오기(서비스 구현 측에서 좋음)

(집계함수)
통계

표준편차, 분산 몰라도 됨
모든 함수에서 NULL값은 뺀다

-> 아무처리 하지 않아도 모든 함수들은 NULL값을 제외한 상태임

WHERE COMMISSION_PCT IS NOT NULL; -> 없어도 됨


-> 아무처리 하지 않아도 모든 함수들은 NULL값을 제외한 상태임

오름차순 (예전 -> 최신) : 모든 날짜는 '숫자'이다

  • TIMESTAMP : 1970년 1월 1일 0시부터 1000분의 1씩 1이 늘어남, 1초마다 1000씩 증가시키는 숫자 (1/1000)
    -> 최근 날짜일수록 숫자가 큼(쌓이고 있어서)
    -> 최근 날짜는 크다 , 예전 날짜는 작다
    가장 나중에 입사한 사원 -> 큰날짜

  • 날짜 빼기 가능 (숫자로 바꿔서 빼기) - 차이 계산 : 얼마의 간격이 생기는지 확인가능
    (날짜 = 숫자)

사원번호 = PK값이라 (NULL값이 존재하지 않아서)

(대체 가능)

★★6. 전체 사원 수 조회하기 : 전체 갯수 구하기 - 많이 사용함!
SELECT COUNT(*) AS 전체사원수
FROM EMPLOYEES;

동일한 부서 - 중복제거

KIMBERY(부서 없던 아이) 빼고 11군데 부서 다니고 있음

★ COUNT -> MAX 순으로 많이 씀 (SUM, AVG 거의 안씀)

profile
NA YE SOM
개발자 velog
이전 포스트

4일차 DDL, DML

다음 포스트

SEQUENCE 실습

post-custom-banner

0개의 댓글