국비 교육 34일차 - ORACLE

웬일로 수업 중에 아예 정리할 시간을 주셔서 지금 시간을 이용해서 정리를 해보고자 한다. 

 

오늘 배운 SELECT 함수들을 최종적으로 정리해보고자 한다. 

 

/*
    오라클 
     SQL (오라클을 실행하는 언어) = 검색에 기반을 둠 (SELECT) 
     ===
    DML (데이터 관리 (조작)) => 웹프로그래머
     = SELECT : 데이터 찾기
     = INSERT : 데이터 추가
     = UPDATE : 데이터 수정
     = DELETE : 데이터 삭제
    DDL : 생성 (테이블, 뷰...)
            CREATE, DROP, ALTER, RENAME, TRUNCATE
    DCL : REVOKE, GRANT
    TCL : COMMIT, ROLLBACK
    
    Table : 저장공간 (column, row) => 단위 row
*/

-- SELECT * FROM genie_music;

/*
    SELECT : 데이터 검색
    = 형식)
        ========================================================
        SELECT * (전체) | 원하는 데이터만 출력 (column1, column2..)
        FROM 테이블명 (뷰명, SELECT~~)
        ======================================================== 필수
        Column별로 (세로로) 가져오면 projection
        Row 별로 (가로로) 가져오면 selection 
        [
            1. WHERE 조건문 (형식 컬럼명 연산자 값) ==> 연산자
                                name = '홍길동'
            2. GROUP BY 컬럼명   
            3. HAVING 그룹조건 
            4. ORDER BY 컬럼명 ASC|DESC => ASC는 생략이 가능
                        ===== 대신 컬럼위치 번호 사용 가능
                        ===== , 이중 정렬
        ]
        
        2) 연산자
            = 산술 연산자 : +, -, *, / %(MOD-함수)
              ========== 형변환 1+'1'=2 (자동형변환)
                         변환 함수 => TO_CHAR, TO_NUMBER, TO_DATE 
            = 비교 연산자 : 결과값-BOOLEAN(TRUE/FALSE)
                = (같다), !=, <>, ^= (같지 않다)
                <, > , <=, >=
            = 논리 연산자 : 결과값-BOOLEAN(TRUE/FALSE)
                AND , OR
            = IN : 여러개의 OR를 대체
            = BETWEEN ~ AND : >= AND <= (기간, 범위)
            = NOT : 부정 => NOT BETWEEN, NOT LIKE, NOT IN
            = LIKE : %, _
                    REGEXP_LIKE() - LIKE를 보완한 함수
            = NULL : 모든 연산자는 NULL 값을 처리 할 수 없다
                    IS NULL
                    IS NOT NULL
        3) 내장 함수
            = 단일행 함수 (ROW단위로 변경)
                문자 함수
                    = LENGTH('') : 문자 갯수
                    = SUBSTR(문자열, 문자시작번호, 갯수) : 문자를 자를 경우 (문자번호: 오라클(1), 자바(0))
                        SUBSTR('ABCDEFG' 2,3) => BCD
                    = INSTR (문자열, 찾는 문자, 문자시작번호, 문자위치)
                    = RPAD (문자열, 문자출력갯수, '문자')
                                   ===========  =====
                      RPAD('ABC',2,'#') => AB
                      RPAD('ABC',7,'*') => ABC**** : ID찾기, PASSWORD 찾기
                
                숫자 함수 
                = ROUND() : 반올림  원형 : ROUND(실수,소수점)  ROUND(67.897,2) => 76.90
                = TRUNC() : 버림        : TRUNC(실수,소수점)  TRUNC(67.897,2) => 76.89
                = *** CEIL() : 올림         : CEIL(67.1) => 68 => 총페이지
                = MOD() : 나머지 (%)    : MOD(5,2) => 5%2 = 1
                날짜 함수 
                    = SYSDATE : 현재 날짜, 시간 => 등록일은 자동으로 처리시
                    = MONTHS_BETWEEN => 과거부터 현재까지의 총 개월수를 출력
                        MONTHS_BETWEEN(현재, 과거) = 게시물 (3~6개월이면 지운다)
                변환 함수
                    = TO_CHAR
                        = 년도 : YYYY,RRRR, YY,RR
                        = 월 : MM, M
                        = 일 : ㅇㅇ
                        = 시간 : HH24, HH , MI(분), SS(초)
                        = 숫자 표현 : 99,999
                일반 함수
                    = NVL : NULL값 대체
                    = NVL2 : NULL=>처리, NOT NULL => 처리
                       NVL2(컬럼명, NULL이 아닌 경우 처리, NULL일 경우 처리)
                            =====
                            NULL을 포함하고 있는 데이터 컬럼
                    = DECODE : 다중 건문
                    = CASE : 다중 선택문
                    = REGEXP_LIKE : 정규식을 이용한 함수
                    [],{}
                    [] => 한글자, 범위 지정이 가능 [A], [A-Z][a-z][0-9][기-힣]
                    {} => 글자 갯수 => {3} , {1,3}
                    . => 한글자를 나타냄
                    [^] => 제외 [^A-Z] => 알파벳 대문자는 제외
                    ^ => 시작 ^A ^[0-9] 
                    $ => 끝 A$ [0-9]$
                    | => 둘중에 하나 => A OR B
                    * => A* (뒤에 붙는 글자가 0개 이상)
                    + => A+ (뒤에 붙는 글자가 1개 이상)
                        맛있+
            = 다중형 함수 (집합함수) => 통계 (합, 평균,  RANK...)
        4) 여러개의 테이블 연결후 결과값 얻기 (JOIN)
        5) SQL문장 여러개를 합쳐서 한 개의 SQL문장으로 변환 (서브쿼리)
    ================================================================= DDL(제약조건)=> DML
        6) VIEW, INDEX, SEQUENCE, FUNCTION, PROCEDURE, TRIGGER
        
        
    
        
        
*/

사실 어제도 정리한 부분이라서.. 일단 책을 보면서 내가 모르는 부분이 있는지 확인해봐야겠다. 

(P.26~)

 

* DISTINCT - 중복된 값을 제외하고 출력해주는 것 

-- 상태 (유지, 상승, 하강, new) => 중복 없이 출력 => DISTINCT (장르, 직위, 부서...)
SELECT DISTINCT state
FROM genie_music;

SELECT DISTINCT state

FROM geine_music;

 

-- 응용 
/*
   1|쇼생크 탈출|드라마|
   https://movie-phinf.pstatic.net/20160119_278/14531650465287bcuk_JPEG/movie_image.jpg?type=m77_110_2
   |팀 로빈스(앤디 듀프레인), 모건 프리먼(엘리스 보이드 레드 레딩)
   |2016 .02.24 재개봉, 1995 .01.28 개봉
   |15세 관람가
   |프랭크 다라본트

   1. 오라클에 저장할 공간 : 테이블
*/
/*DROP TABLE movie;
CREATE TABLE movie(
   mno NUMBER(4),
   title VARCHAR2(100),
   genre VARCHAR2(100),
   poster VARCHAR2(200),
   actor VARCHAR2(300),
   regdate VARCHAR2(100),
   grade VARCHAR2(50),
   director VARCHAR2(100)
);
select count(*) from movie;
SELECT * FROM movie;
*/
-- 장르 => 액션  => 함수  => substr()
SELECT *
FROM movie
WHERE SUBSTR(genre,1,2)='액션';

SELECT *
FROM movie
WHERE genre LIKE '%액션%';

-- 송강호가 출연한 영화 제목을 출력 
SELECT title,poster
FROM movie
WHERE actor LIKE '%송강호%';

-- 영화가 두번이상 상영된 영화 찾기 
SELECT title,regdate
FROM movie
WHERE regdate LIKE '%재개봉%';

-- 영화제목중에 영어가 포함된 영화제목 출력 
SELECT title
FROM movie
WHERE REGEXP_LIKE(title,'[A-Za-z]');
/*
    영문  : 대문자 [A-Z], 소문자 [a-z] , 대문자소문자 : [A-Za-z]
    숫자  : [0-9]
    한글 : [가-힣]
*/
-- 영화 제목중에 숫자가 들어가 있는 모든 영화 제목 출력
SELECT title
FROM movie
WHERE REGEXP_LIKE(title,'[0-9]');

-- emp에서 in,en이 포함된 이름을 가진 사원의 이름을 출력 
SELECT * FROM emp;
SELECT ename
FROM emp
WHERE ename LIKE '%IN' OR ename LIKE '%EN';
/*
    $ => 끝나는 문자열 
    | => OR
*/
SELECT ename
FROM emp
WHERE REGEXP_LIKE(ename,'(IN|EN)$');

-- A,B 로 시작하는 이름 출력 
SELECT ename
FROM emp
WHERE ename LIKE 'A%' OR ename LIKE 'B%';
/*
   시작하는 문자열  '^문자열'  => '문자열%'
   끝나는 문자열    '문자열$' => '%문자열'
*/
SELECT ename
FROM emp
WHERE REGEXP_LIKE(ename,'^(A|B)');
-- 211.238.142.181 , 127.0.0.1 
-- [0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}
-- 영화 제목중에 .을 포함하는 제목 
SELECT title
FROM movie
WHERE title LIKE '%...%';
/*
    . 임의의 한글자  => _  S___T , S...T
    . 자체를 찾는 경우 => \.
    [] 글자,범위
    {} 갯수 
*/
SELECT title
FROM movie
WHERE REGEXP_LIKE(title,'[.]{3}');

-- .이 3개 있는 제목 
/*
    순위:100
    제목:Square (2017)
    가수:백예린 (Yerin Baek)
    앨범:Every letter I sent you.
    포스터://image.genie.co.kr/Y/IMAGE/IMG_ALBUM/081/329/064/81329064_1575961779895_1_140x140.JPG/dims/resize/Q_80,0
    상태:하강
    등폭:4
*/
-- 뮤직 genie_music  , melon_music
SELECT LENGTHB('홍길동') FROM DUAL;
/*DROP TABLE genie_music;
CREATE TABLE genie_music(
   no NUMBER(3),
   cno NUMBER(1),
   title VARCHAR2(300),
   singer VARCHAR2(200),
   album VARCHAR2(200),
   poster VARCHAR2(260),
   state CHAR(6),
   idcrement NUMBER(3)
);
DESC genie_music;

SELECT * FROM emp;

SELECT COUNT(*) FROM genie_music;
SELECT * FROM genie_music;


DELETE FROM genie_music
WHERE no>200;
COMMIT;
*/

DROP TABLE 테이블명;

CREATE TABLE 테이블명(

 변수명 (데이터형)

 변수명 (데이터형)

 변수명 (데이터형)

 변수명 (데이터형)

);

 

REGEXP_LIKE() => 정규식으로 데이터 찾아주는 방식

 = REGEXP_LIKE : 정규식을 이용한 함수
                    [],{}
                    [] => 한글자, 범위 지정이 가능 [A], [A-Z][a-z][0-9][기-힣]
                    {} => 글자 갯수 => {3} , {1,3}
                    . => 한글자를 나타냄
                    [^] => 제외 [^A-Z] => 알파벳 대문자는 제외
                    ^ => 시작 ^A ^[0-9] 
                    $ => 끝 A$ [0-9]$
                    | => 둘중에 하나 => A OR B
                    * => A* (뒤에 붙는 글자가 0개 이상)
                    + => A+ (뒤에 붙는 글자가 1개 이상)
                        맛있+

-- 제목에 영어가 들어가는 노래의 모든 정보 출력

SELECT *
FROM genie_music
WHERE REGEXP_LIKE(title, '[A-Za-z]');  

-- 제목이 e로 끝나는 노래의 모든 정보 출력

SELECT *
FROM genie_music
WHERE REGEXP_LIKE(title, 'e$');

-- 가수명이 nct인 노래의 모든 정보 출력 (없을 것으로 예상)

SELECT *
FROM genie_music
WHERE singer LIKE '%NCT%';

역시나는 역시나

대중 선생님들의 귀는 정확했다... 

 

 

-- .이 3개 있는 제목 출력하기 

 SELECT title

FROM movie

WHERE REGEXP_LIKE(title, '[.]{3}');

SELECT title
FROM movie
WHERE REGEXP_LIKE(title,'[.]{3}');

** 번외 전세계 ip 찾기

WHERE REGEXP_LIKE(IP, '[0-9]{3}.[0-9]{3}.[0-9]{3}.[0-9]{3}.');  

 

함수는 어느정도 알것 같아서 출력하는 방법을 다시 보겠다. 

 

-- ㅇㅇ 의 ㅇㅇ앨범에 있는 곡 ㅇㅇ => 이 형식으로 정보 출력하기

SELECT singer|| '의 ' || album || '에 있는' || ' 노래:' || title "가수명 앨범명 노래명"
FROM genie_music;

오늘의 복습은 끝!!!

 

즐 설날 ^^