Daily Reminder

SELECT 마무리, SQL 단일행 함수

• LIKE 연산자
  검색하고자 하는 문자열을 정확히 알 수 없는 경우에 사용
  
  

%	0글자 이상의 임의 문자를 대표
_	1글자의 임의문자를 대표

• WHERE 컬럼명 LIKE
  select * sal from emp whhere ename like 'J%'
  -> J 로 시작하는 이름찾기
  
  select * sal from emp whhere ename like '%S'
  -> S 로 끝나는 이름찾기
  
  select * sal from emp whhere ename like '%LL%'
  -> LL 이 중간에 들어가는 이름찾기
  
  select * sal from emp whhere ename like '_B%'
  -> 두번째 글자가 B로 시작하는 이름찾기
  
  select * sal from emp whhere ename like '_____S%'
  -> 다섯번째 글자가 S로 끝나는 이름찾기
  
  select * sal from emp whhere ename like '%$_%'
  -> _가 들어가는 이름찾기 그냥 _ 가 아니라
  
  
  
• 논리연산자 :
  
  
• AND 연산자
  두가지 조건을 모두 만족하는 데이터를 검색 
  SELECT * FROM emp WHERE job = 'SALESMAN' and sal>=1500;
• OR 연산
  둘중 하나 조건을 만족하는 데이터 검색 
  SELECT empno, ename, job, sal FROM emp WHERE job = 'SALESMAN' or sal>=1500;
  
  
• NOT 연산
  select *from emp where comm is not null;
  null 이 아닌 모든 항목을 출력.
  
  select * from emp where not sal >1000;
  sal이 1000 이하인 항목을 출력
  
  SELECT * FROM emp WHERE not ename in( 'SMITH' ,'ALLEN');
  SMITH 와 ALLEN 을 제외한 사람을 출력
  
  SELECT * FROM emp WHERE ENAME not LIKE 'J%';
  J로 시작하지 않는 사람들을 출력 → 10명
  
  SELECT * FROM emp WHERE sal not between 1000 and 2000;
  1000과 2000 사이가 아닌 사람을 추출
  
  
• 연산자 우선순위
  
  SELECT * FROM emp WHERE job = 'CLERK' OR job = 'ANALYST' AND
  comm is null and sal >=1000 and sal <=3000;
  앞에 조건먼저 찾은게 아니라 1000 과 3000 사이를 찾고 직업을 따라 찾음
  -> 잘못된 결과
  
  SELECT * FROM emp WHERE (job = 'CLERK' OR job = 'ANALYST') AND
  comm is null and sal >=1000 and sal <=3000;
  -> ()를 사용해 앞이 먼저 연산하게 함.
  
  
  
• ORDER BY
  
  오름차순 (asc) :위에서부터 아래로 증가
  내림차순(desc): 위에서부터 아래로 숫자가 감소.
  
  정렬방법은 기본이 오름차순(asc) 따라서 뒤에 아무것도 안적으면 오름차순이 된다.
  
  SELECT * FROM emp ORDER BY sal;
  -> sal 이 큰 작은순으로 정렬됨.
  
  SELECT * FROM emp ORDER BY 6 desc;
  앞에서부터 컬럼을 셋을 때 컬럼번호가 6인 sal 을 기준으로
  
  SELECT ename, sal as 월급 FROM emp order by 월급 desc;
  sal에 별칭 부여하면 그거대로 사용가능
  
  
  
• 다중정렬
  select * from emp order by sal desc, hiredate;
  같은 값의 sal을 hiredate 기준으로 오름차순으로 바꿈 (작_> 큰)
  
  =
  select* from emp order by 3 desc, 4;
  
  SELECT * FROM emp order by mgr desc;
  하면 null 값이 맨위에 나옴. null 값이 항상 맨 위에
  
  

SQL 함수

단일함수 : 모든행에 각각 적용되어 행의 개수와 동일한 개수를 반환. 
-> 레코드 개수만큼 값이 나옴.

1. INITCAP
   SELECT ENAME, INITCAP(ENAME) FROM EMP;
   첫글자만 대문자로
   
   
2. UPPER
   모든 글자를 대문자로
   select ename from emp where ename=upper('king');
   소문자로는 반환이 안되던 king이 대문자화 되어 KING으로 찾아짐
   
   
3. LOWER 
   모든글자를 소문자로
   select lower ('ORACLE SQL') from dual;
   -> oracle sql
   
   select ename from emp where lower(ename)= 'king';
   → KING 으로만 찾을 수 있던 것을 lower함수로 소문자 ‘king’ 으로 찾음
   
   
4. CONCAT
   두개의 문자열을 연결
   SELECT CONCAT ('ORACLE', 'Sql') FROM DUAL;
   →ORACLESql
   
   select concat(ename,'사원') as 사원명 from emp;
   ->SMITH사원
   
   
5. LENGTH
   
   select ename , length(ename) from emp;
   -> 이름을 정렬하고 그 옆에 이름의 길이를 정렬
   
   select ename , length(ename) from emp order by 2 desc;
   -> 이름을 정렬하고 그 옆에 이름의 길이를 정렬할 때 이름의 길이를 기준으로 위에서 아래로 작아지게
   
   
6. INSTR
   INSTR(컬럼명|표현식, 검색값, [m,n])
   INSTR('DOLLAR','L' 1,2)
   
   m은 문자를 검색하기 위한 시작 위치값
   n은 몇 번째로 나오는 문자를 검색할건지
   
   select INSTR('STILL', 'L',1,2),INSTR ('STILL', 'X',1,2)FROM DUAL;
   L은 4로 나오는데 X는 존재하지않아 0으로 나옴.
   
   
7. SUBSTR 
   select substr('900303-1234567',8,1) from dual;
   ->1
   
   select substr(hiredate,1,2)입사년도 from emp order by 입사년도 asc;
   → 80 81 …
   
   select substr('900303-1234567',-8) from dual;
   → -1234567
   
   select substr('900303-1234567',8) from dual;
   → 1234567
   
   
   
8. REPLACE
   
   select replace ('JACK and JUE', 'J', 'BL') from dual;
   →black and blue
   select replace (ENAME, 'J', 'G') from emp;
   → ename에는 ‘ ‘ 붙일 필요없음.
   
   
   
9. LPAD 
   문자열을 오른쪽 정렬 후에 특정 문자를 왼쪽부터 디덩한 문자로 채우는 함수
   select LPAD('dollar', 10, '*') from dual;
   -> dollar****
   
   
   
10. RPAD
    문자열을 왼쪽 정렬후에 특정문자를 오른쪽부터 지정한 문자로 채우는 함수
    
    select RPAD('dollar', 10, '*') from dual;
    → dollar****
    
    시험출제!~
    select substr('900303-1234567',1,8)||'******' 주민번호 from dual;
    -> 900303-1******
    1번째 자리부터 8번째 자리까지 뽑고 뒤에 * 6개 붙이기
    
    select rpad(substr('900303-1234567',1,8)14,'*') 주민번호 from dual;
    ->900303-1******
    1번째 자리부터 8번째 자리까지 뽑고 왼쪽 정렬하여 오른쪽부터 총 14자리에서 남은 자리 6개 *로 채우기
    
    select replace ('900303-1234567,substr(900303-1234567,9),'******') 주민번호 from dual;
    -> 주민번호에서 필요한 값을 SUBSTR 함수로 추출하고 REPLACE 함수를 사용하여 '*'로 치환.
    
    
    
11. TRIM 
    select length(trim('  hello  ')) from dual;
    앞공백 2자리 뒤공백 2자리 제거한 5만 추출  
    select length(trim('0'from'0001234567000'))from dual;
    ->0을제거 1234567만 추출
    select length(trim(leading'0'from'0001234567000'))from dual;
    -> 왼쪽 0만제거 1234567000→10자리
    select length(trim(trailing'0'from'0001234567000'))from dual;
    -> 오른쪽 0만 제거 0001234567→10자
    
    -  select ename, substr(hiredate,1,2)입사년도, substr(hiredate,4,2)입사월 from emp order by 입사년도, 입사월 asc;
    = select ename, substr(hiredate,1,2)입사년도, substr(hiredate,4,2)입사월 from emp order by 2, 3 asc;
    입사년도는 앞 2자리만 뽑고 입사월을 4번째 자리부터 2개 뽑아 오름차순으로 배열
    
    
    

숫자처리함수

1. ROUND
   지정한 자리에서 반올림한 결과를 반환하는 함수
   소수점을 기준으로   -2-  1 0 . 1 2 3
   
   select round(456.789,2)from dual;
   -> 456.79 : 2의 자리를 남겨야 하니 3에서 반올림
   
   select round (456.789) from dual;
   -> 소수점자리서 반올림하여 정수로 출력
   
   
   
2. TRUNC
   
   select trunc (456.78,2) from dual;
   절삭하여 456.78
   
   select trunc (456.789,-1) from dual;
   → 450
   
   select trunc (456.789) from dual;
   → 456
   
   
3. MOD
   
   select mod(10,3), mod(10,0) from dual;
   -> 1 | 10
   
   select empno, ename from emp where mod(empno,2) = 1;
   -> 사원번호가 홀수 인 사람
   
   =0이면 짝수인사람
   
   not mod (empno,2) =1 이면 짝수 인 사람
   
   
   
4. CEIL
   
   소수값을 가진 실수값을 정수값으로 반환하는 함수
   select ceil (10.6), ceil(-10.6) from dual;
   -> 11 | 10
   
   
   
5. FLOOR 
   
   주어진 숫자보다 작거나 같음
   
   select floor(10.6), floor(-10.6) from dual;
   -> 10 | 11
   
   
   
6. SIGN
   
   select sign(100), sign(-20), sign(0) from dual;
   -> 1 | -1 | 0 반환
   
   select * from emp where sign (sal - 1500) = 1;
   -> 1500 보다 많은 월급을 가진 사람을 반환.
   
   
   

날짜 함수

1. SYSDATE 중요!!
   
   select sysdate, sysdate +1 from dual;
   -> 23/11/15 | 23/11/16
   
   select sysdate 오늘, syssdate +1 내일 , sysdate -1 어제 from 어제 dual;
   
   select ename , hiredate, TRUNC (( stysdate - hiredate ) / 365) "년" from emp order by 3 desc;
   -> 현재 날짜에서 입사날짜를 빼고 소수점을 절삭한뒤 내밀차순으로 정렬
   
   
2. RR 과 YY 타입
   
   YY 타입은 현재 세기를 고려 따라서 99/11/16 쓰면 2099년이 된다.
   RR 타입은 현재연도가 00~49 고 명시년도도 그러하다면
   현재세기 현재연도가 00-49 고 명시년도가 50-99 년도면 이전세기를 표기한다
   
   select * from NLS_SESSION_PARAMETERS;
   YY 인지RR인지 확인가능
   
   
3. MONTHS_BETWEEN
   
   날짜와날짜 사이의 개월 수를 반환하는 함수
   
   select ename, hiredate, TRUNC(MONTHS_BETWEEN(sysdate, hiredate)) "근무 월수" from emp order by 3 desc;
   실행결과는 소수점을 제외한 근무개월 수 출력
   
   
4. ADD_MONTHS
   
   지정된 날짜에 특정 개월수를 더하거나 뺀 날짜를 반환
   
   select sysdate 현재 ,add_months (sysdate,1) 다음달,
   add_months(sysdate, -1) 이전달 from dual;
   
   
   
5. NEXT_DAY
   
   특정 날짜를 기준으로 돌아오는 가장 빠른 요일에 해당하는 날을 보여줌
   
   한글인 경우 월/화 또는 월요일/화요일 , n값은 문자 요일 대신에 일요일은 1 월요일은 2 형식으로 기술
   
   select next_dat (sysdate, '금 )from dual;
   -> 돌아오는 가장 른 금요일
   
   
   
6. LAST_DAY
   
   해당 날이 속하는 달의 마지막 날 반환
   
   select last_day(sysdate) from dual;
   → 23/11/30
   
   select next_day(ADD_MONTHS(sysdate,1), 1) from dual;
   → 현재 날짜에서 한 달이 지나고 가장 가까운 일요일
   
   
7. ROUND 
   
   가장 가까운 년도 또는 월로 반올림
   
   select sysdate, round(sysdate, 'YEAR'), round(sysdate,'ONTH') from dual;
   -> 24/1/1| 23/11/1 30일의 절반인 15일 때는 다음달로 넘어가지 않음
   
8. TRUNC
   
   가장 가까운 년도 또는 월로 절삭
   
   select sysdate, trunc(sysdate, 'YEAR'), trunc(sysdate, 'MONTH') from dual;
   → 23/01/01 23/11/01
   
   

변환 함수 중요!!!!!!!!!!!! 

묵시적= 자동 , 변환, 묵시적 변환, auto

ex) expno = '1000' 문자 ('1000') 인데 자동으로 숫자로 변환하여 에러가 안남
학과목 번호 = 001 은 숫자인데 없는 숫자로 문자 변환

명시적 = 자동변환이 아닌 수동변화 ( 내가 직접 코딩해야) ,menual
ex) expno= to_number ('1000')

문자를 넘버로
select * from emp where sal= '1500'; — 자동형변환
select * from emp where sal= to_number ('1500'); —- 수동

 

1.  TO_CHAR
   
   select ename, to_char (hiredate, 'DD' )입사일,
   to_char(hiredate, 'MM') 입사월,
   to_char(hiredate, 'YY') 입사연도 from emp;
   
   select hiredate from emp where '81' = to_char(hiredate, 'YY');
   -> 81년도 입사날짜 열개 추출
   
   select TO_CHAR(SYSDATE, 'YYYY "년" MM"월" DD"일" ') 날짜 from dual;
   → 2023년 11월 15일
   
   $랑 L 중요!!!!!!!
   
   select ename , sal, TO_CHAR(sal, '$999,999') 달러,
   TO_CHAR(sal, 'L999,999') 원화 from emp;
   ->$는 달라기호 앞에 표현 L은 지역통화
   
   
2. TO_NUMBER
   
   select '123'+100 from dual; —— 자동
   select TO_NUMBER('123')+100 from dual; —— 수동
   
   
3. TO_DATE
   
   select TO_DATE('20170802', 'YYYYMMDD') from dual;
   → 17/08/02 로 나옴
   
   ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
   -> RR 타입을 YY로 변경. 사용시YYYY/MM/DD HH24:MI:SS 로 나옴
   
   select TO_DATE('20170802181030', 'YYYYMMDDHH24MISS') from dual;
   → 이제야 2017/08/02 18:10:30 으로 나옴.
   
   select sysdate, sysdate-to_date('20230101', 'YYYYMMDD') from dual;
   230101 부터 318일 지난것으로 나옴.