[ORACLE 3일차]테이블 변경,수정/like/(문자함수upper/lower/initcap/substr/instr/reverse/lpad/rpad/trim/rtrim/ltrim/translate/replace/length

테이블 수정

ALTER TABLE 을 이용하여 수정한다. 

ALTER TABLE을 사용할 때는 ADD(컬럼 추가), MODIFY(컬럼 수정), DROP(컬럼 제거)를 이용한다.

 

1.컬럼추가 

 

alter table employees
   add jubun varchar2(13);

 

   select *
   from employees
   where department_id = 30;
   
   select *
   from employees
   where department_id like 30;

 

like 연산자와 함께 사용되어지는 % 와 _ 를 wild character 라고 부른다.

like 연산자와 함께 사용되어지는 % 의 뜻은 글자수와는 관계없이 글자가 있든지 글자가 없든지 관계없다라는 말이다.
like 연산자와 함께 사용되어지는 _ 의 뜻은 반드시 아무글자 1개만을 뜻하는 것이다.

 

<예시 >

employees 테이블에서 여자 1990년생과 남자 1991년생의 사원들만 사원번호, 사원명, 주민번호를 나타내세요.

 

 

   select employee_id AS 사원번호
        , first_name || ' ' || last_name AS 사원명
        , jubun AS 주민번호
   from employees
   where jubun like '90____2%' OR jubun like '91____1%';

 

<다양한 예시들>

 

 

  employees 테이블에서 first_name 컬럼의 값이 'J'로 시작하는 사원들만 
  사원번호, 이름, 성, 기본급여를 나타내세요.
 
 
   select employee_id, first_name, last_name, salary
   from employees
   where first_name like 'J%';
 
   <select from 은생략하겠다>
 
   employees 테이블에서 first_name 컬럼의 값이 's'로 끝나는 사원들만 
   사원번호, 이름, 성, 기본급여를 나타내세요.
   where first_name like '%s';
 
 
  -- employees 테이블에서 first_name 컬럼의 값중에 'ee' 라는 글자가 들어있는 사원들만 
   -- 사원번호, 이름, 성, 기본급여를 나타내세요.
   where first_name like '%ee%';
 
  -- employees 테이블에서 first_name 컬럼의 값중에 'e' 가 2개 이상 들어있는 사원들만 
   -- 사원번호, 이름, 성, 기본급여를 나타내세요.
   where first_name like '%e%e%';
 
   -- employees 테이블에서 last_name 컬럼의 값이 첫글자는 'F' 이고 두번째 글자는 아무거나 이고
   -- 세번째 글자는 소문자 'e' 이며 4번째 부터는 글자가 있든지 없든지 상관없는 사원들만 
   -- 사원번호, 이름, 성, 기본급여를 나타내세요.
   where last_name like 'F_e%';
   
 
 

 

 <like 연산자와 함께 사용되어지는 % 와 _ 인 wild character 의 기능을 escape(탈출) 시키기 >

 

-- tbl_watch 테이블에서 bigo 컬럼에 99.99% 라는 글자가 들어있는 행만 나타내세요.
   select * 
   from tbl_watch
   where bigo like '%99.99%%';

 

결과값 : 99.99% 와 99.99점이 모두 도출된다. 

목표는 99.99%만 출력되게 하는 것

 

  select * 
   from tbl_watch
   where bigo like '%99.99\%%' escape '\';
   --  escape 문자로 '\' 을 주었으므로 '\' 다음에 나오는 wild character(% 또는 _) 문자 1개만 wild character 기능에서 탈출시켜버린다.
   
   select * 
   from tbl_watch
   where bigo like '%99.992%%' escape '2';
   --  escape 문자로 '2' 을 주었으므로 '2' 다음에 나오는 wild character(% 또는 _) 문자 1개만 wild character 기능에서 탈출시켜버린다.
   
   select * 
   from tbl_watch
   where bigo like '%99.99a%%' escape 'a';
   --  escape 문자로 'a' 을 주었으므로 'a' 다음에 나오는 wild character(% 또는 _) 문자 1개만 wild character 기능에서 탈출시켜버린다.

 

이러면 99.99%만 도출되게 된다.

 

 

단일행 함수

 

        ※ 단일행 함수의 종류
    
        1. 문자 함수
        2. 숫자 함수
        3. 날짜 함수
        4. 변환 함수

 

1. 문자 함수

 

1.1 upper

1.2 lower

1.3 initcap

 

-- 1.1  upper('문자열')  ==> '문자열' 을 모두 대문자로 변환시켜주는 것.
    select 'kOreA SEoul', upper('kOreA SEoul')
    from dual;
    
    -- 1.2  lower('문자열')  ==> '문자열' 을 모두 소문자로 변환시켜주는 것.
    select 'kOreA SEoul', lower('kOreA SEoul')
    from dual;
    
    -- 1.3  initcap('문자열')  ==> '문자열' 을 단어별(구분자 공백) 첫글자만 대문자, 나머지는 소문자로 변환시켜주는 것.
    select 'kOreA SEoul', initcap('kOreA SEoul')
    from dual;

 

<사용법 >

 

1.select 할때 : 이대로 뽑아라

 select last_name, upper(last_name), lower(last_name), initcap(last_name)
 from employees;

 

 

2.where 절에서 쓸때 : 각각 대,소문자로 변환후 뽑는거라  대,소문자 상관없이 뽑히게 된다

 

    select * 
    from employees
    where lower(last_name) = lower('kIng');
 
    select * 
    from employees
    where upper(last_name) = upper('kIng');
 
    select * 
    from employees
    where  initcap(last_name)= initcap('kIng');

 

1.4 substr

 

substr('문자열', 시작글자번호, 뽑아야할글자길이) 
: 문자열' 중에 문자열의 특정 일부분을 선택해올 때 사용한다.

 

 select '쌍용교육센터'
         , substr('쌍용교육센터', 2, 3)  -- '쌍용교육센터' 에서 2번째 글자인 '용' 부터 3글자만 뽑아온다.
         , substr('쌍용교육센터', 2)     -- '쌍용교육센터' 에서 2번째 글자인 '용' 부터 끝까지 뽑아온다.
    from dual;
    -- 쌍용교육센터    용교육    용교육센터

 

<예시>

 

 

-- *** substr() 함수를 사용하여 employees 테이블에서 성별이 '여자'인 사원들만
    --     사원번호, 사원명, 주민번호를 나타내세요. ***
    
    select employee_id AS 사원번호
         , first_name || ' ' || last_name AS 사원명
         , jubun AS 주민번호
    from employees
    where substr(jubun, 7, 1) in('2','4');

 

 

 1.5   instr 

 

: 어떤 문자열에서 명명된 문자열의 위치를 알려주는 것 

 

 select '쌍용교육센터 서울교육대학교 교육문화원'
         , instr('쌍용교육센터 서울교육대학교 교육문화원', '교육', 1, 1)  -- 3
         -- '쌍용교육센터 서울교육대학교 교육문화원' 에서 '교육' 이 나오는 위치를 찾는데
         -- 출발점이 1 번째 부터 검색하여 1 번째 나오는 '교육'의 위치를 알려달라는 말이다.
         
         , instr('쌍용교육센터 서울교육대학교 교육문화원', '교육', 1, 2)  -- 10
         -- '쌍용교육센터 서울교육대학교 교육문화원' 에서 '교육' 이 나오는 위치를 찾는데
         -- 출발점이 1 번째 부터 검색하여 2 번째 나오는 '교육'의 위치를 알려달라는 말이다.
         
         , instr('쌍용교육센터 서울교육대학교 교육문화원', '교육', 1, 3)  -- 16
         -- '쌍용교육센터 서울교육대학교 교육문화원' 에서 '교육' 이 나오는 위치를 찾는데
         -- 출발점이 1 번째 부터 검색하여 3 번째 나오는 '교육'의 위치를 알려달라는 말이다.
         
         , instr('쌍용교육센터 서울교육대학교 교육문화원', '교육', 1, 4)  -- 0
         -- '쌍용교육센터 서울교육대학교 교육문화원' 에서 '교육' 이 나오는 위치를 찾는데
         -- 출발점이 1 번째 부터 검색하여 4 번째 나오는 '교육'의 위치를 알려달라는 말이다.
         -- 찾는 값이 없을 시에는 0 이 나온다.
         
         , instr('쌍용교육센터 서울교육대학교 교육문화원', '교육', 4, 1)  -- 10
         -- '쌍용교육센터 서울교육대학교 교육문화원' 에서 '교육' 이 나오는 위치를 찾는데
         -- 출발점이 4 번째 부터 검색하여 1 번째 나오는 '교육'의 위치를 알려달라는 말이다.
         
         , instr('쌍용교육센터 서울교육대학교 교육문화원', '교육', 1)  -- 3
         -- '쌍용교육센터 서울교육대학교 교육문화원' 에서 '교육' 이 나오는 위치를 찾는데
         -- 출발점이 1 번째 부터 검색하는데 몇번째인지 값이 없으면 무조건 1 번째 나오는 '교육'의 위치를 알려달라는 말이다.
         
         , instr('쌍용교육센터 서울교육대학교 교육문화원', '교육', 4)  -- 10 
         -- '쌍용교육센터 서울교육대학교 교육문화원' 에서 '교육' 이 나오는 위치를 찾는데
         -- 출발점이 4 번째 부터 검색하는데 몇번째인지 값이 없으면 무조건 1 번째 나오는 '교육'의 위치를 알려달라는 말이다.
    from dual;

 

 

1.6  reverse

 

 : 어떤 문자열을 거꾸로 보여주는 것이다.

 

 

    -- 1.6  reverse : 어떤 문자열을 거꾸로 보여주는 것이다. --
    select 'ORACLE', reverse('ORACLE')
         , '대한민국', reverse('대한민국'), reverse(reverse('대한민국'))
    from dual;

reverse(reverse('대한민국')) : 는 원래대로 대한민국이 나온다

 

 

<종합 응용>

 

 

 -- 아래와 같이 나오도록 하세요 --
    /*
       ---------------------------
        파일번호      파일명
       --------------------------- 
          1          resume.hwp
          2          mymusic.mp3
          3          face.jpg
    */

 

select fileno, filepath, reverse(filepath),
      --   substr(reverse(filepath),1, 글자길이)
      --   substr(reverse(filepath),1, reverse(filepath)에서 최초로 '\'가 나오는 위치 - 1 )
      --   substr(reverse(filepath),1, instr(reverse(filepath),'\',1)-1)
           reverse( substr(reverse(filepath),1, instr(reverse(filepath),'\',1)-1)  )   
    from tbl_files;

 

 

<최종 코딩>

 select fileno AS 파일번호 
         , reverse( substr(reverse(filepath),1, instr(reverse(filepath),'\',1)-1)  ) AS 파일명  
    from tbl_files;

 

<또 다른 방법>

 

select '쌍용교육센터 서울교육대학교 교육문화원'
         , instr('쌍용교육센터 서울교육대학교 교육문화원', '교육', -1, 1)  -- 16
         -- '쌍용교육센터 서울교육대학교 교육문화원' 에서 '교육' 이 나오는 위치를 찾는데
         -- 출발점이 역순으로 1 번째 부터 검색하여 1 번째 나오는 '교육'의 위치를 알려달라는 말이다.
         
         , instr('쌍용교육센터 서울교육대학교 교육문화원', '교육', -6, 2)  -- 3
    from dual;   
    
    select fileno, filepath,
       --  substr(filepath, filepath에서 마지막으로 나오는 '\' 위치값 그 다음 부터 끝까지 뽑는다)
       --  substr(filepath, filepath에서 마지막으로 나오는 '\' 위치값+1 )
           substr(filepath, instr(filepath, '\', -1)+1 )
    from tbl_files;
    
    select fileno AS 파일번호
         , substr(filepath, instr(filepath, '\', -1)+1 ) AS 파일명
    from tbl_files;

 

1.7  lpad

 

: 왼쪽부터 문자를 자리채움  

1.8  rpad

 

: 오른쪽부터 문자를 자리채움  

 

    -- 1.7  lpad : 왼쪽부터 문자를 자리채움  --
    -- 1.8  rpad : 오른쪽부터 문자를 자리채움  --
    select lpad('여름여행', 20, '*')    -- 20 byte 를 확보해서 '여름여행'를 넣습니다. 넣은 후 빈공간(12byte)이 있으면 왼쪽부터 '*' 로 채워라.
         , rpad('여름여행', 20, '*')    -- 20 byte 를 확보해서 '여름여행'를 넣습니다. 넣은 후 빈공간(12byte)이 있으면 오른쪽부터 '*' 로 채워라.
    from dual;
    --************여름여행 / 여름여행************
    
    select lpad('*',10,'*'), rpad('*',10,'*')
    from dual;
    -- **********

 

1.9   ltrim : 왼쪽부터 문자를 제거한다.

 

1.10  rtrim : 오른쪽부터 문자를 제거한다.

1.11  trim :  왼쪽, 오른쪽부터 공백문자를 제거한다.

 

 

    select ltrim('aabcdabcdaaaccdddabcdTabdddcccaaaSSS','abcd'), --TabdddcccaaaSSS
           rtrim('aabcdabcdaaaccdddabcdTabdddcccaaa','abcd'),-- aabcdabcdaaaccdddabcdT
           rtrim(ltrim('aabcdabcdaaaccdddabcdTabdddcccaaa', 'abcd'), 'abcd')--T
    from dual;
   
    select '쌍용' || '                     교육                 센터'        --쌍용                     교육                 센터
         , '쌍용' || ltrim('                     교육                 센터') --쌍용교육                 센터
    from dual;
    
    select '쌍용                    ' || '교육                 센터'        --쌍용                    교육                 센터
         , rtrim('쌍용                    ') || '교육                 센터' --쌍용교육                 센터
    from dual;
    
    
    select '쌍용' || '          교육              ' || '센터'       --쌍용          교육              센터
         , '쌍용' || trim('          교육              ') || '센터' -- 쌍용교육센터
    from dual;

 

1.12  translate 

 

    select translate('010-3456-6943'
                    ,'0123456789'
                    ,'영일이삼사오육칠팔구')
    from dual;
    -- 영일영-삼사오육-육구사삼

 

 1.13  replace

 

 

 select replace('쌍용교육센터 서울교육대학교 교육문화원'
                  ,'교육'
                  ,'education')
    from dual;
    -- 쌍용education센터 서울education대학교 education문화원

 

 1.14  length  : 문자열의 길이를 알려주는 것 

 

select length('쌍용center')  -- 8
    from dual;

 

<예시 >

 

 

    -- employees 테이블에서 last_name 의 길이가 8 이상인 행만 추출하세요.
    select *
    from employees
    where length(last_name) >= 8;
    
    -- employees 테이블에서 last_name 의 길이가 8 이상 10 이하인 행만 추출하세요.
    select *
    from employees
    where length(last_name) between 8 and 10;