2024.11.28 2일차 수업

1. where절

   - 행을 제한하는 절

   - 조건절 

   - 기준 컬럼이 문자열, 날짜열이면 비교값은 작은따옴표로 묶어야 한다.

   ex) select * from 소유자.테이블명

         where 기준컬럼 = 비교값;

    - 날짜 형식은 지역에 따라 기본 날짜 표시 형식이 다르다.

       ex) 한국(RR/MM/DD), 미국(DD-MON-RR)

 

※ 접속하는 지역에 따라 자동으로 바뀌는 지역 SQL 정보

select * from nls_session_parameters;

 

 

- 비교연산자 (=같다, >크다, >=크거나 같다, <작다, <=작거나 같다, (!=, ^=, <>)같지 않다)

ex1)

select * 
from hr.employees
where employee_id = 100;

출력

ex2)

select * 
from hr.employees
where last_name = 'king';

ex3)

 select *
 from hr.employees
 where hire_date = '04/01/30';

 - 한국에선 여러 날짜 표기가 가능하지만 미국 표시를 하면 에러남

 

# 조건절을 여러 개 쓸 경우

  - 논리연산자(AND, OR, NOT)도 필요하다.

  - AND : 두 조건이 참이면 true

  - OR : 두 조건 중에 하나라도 참이면 true

  - NOT : false인 경우 true, true인 경우 false

 

ex) department_id 50인 사람들이거나 급여가 5000보다 크거나 같은 사람만 출력

select *
from hr.employees
where department_id = 50
or salary >= 5000;

출력

 

2. between

   - and 비교를 좀 더 간편하게 하고 싶을 때 사용

   - between 하한값 and 상한값

   - 범위 조건을 사용하여 값의 범위에 따라 행을 추출

 

ex1) 둘 다 같은 출력값을 가진다.

 select *
 from hr.employees
 where salary >= 2500
 and salary <= 3500;

 ↕

select *
 from hr.employees
 where salary between 2500 and 3500;

 

ex2) 

 select *
 from hr.employees
 where last_name >= 'Abel'
 and last_name <= 'Austin';

↕

select *
 from hr.employees
 where last_name between 'Abel' and 'Austin';

 

3. in 연산자

 - OR 비교를 간편하게 하고 싶을 때 사용

 - 각 목록의 값과 일치하는 값을 추출할 때 사용

 

ex)

select *
from hr.employees
where employee_id = 100
or employee_id = 101
or employee_id = 102;

↕

select *
from hr.employees
where employee_id in(100,101,102);

 

4. 논리연산자 우선순위

  NOT > AND > OR

 

ex) 

select *
from hr.employees
where department_id = 30
or department_id = 50
or department_id = 60
and salary > 5000;

 - 이럴 경우 첫 번째로 department_id = 60 and salary > 5000; 이 조건이 먼저 돌어간다.

 

5. is null (널인 걸 체크), is not null (널이 아닌 걸 체크)

   - null값을 체크하는 연산자 (주의 : 앞에 is 꼭 붙여야 함!!)

 

ex1) 

select *
from hr.employees
where commission_pct is null;

출력

 

ex2)

select *
from hr.employees
where commossion_pct is not null;

출력

 

6. like 연산자 ★중요! 

   - 문자 패턴을 찾을 때만 쓰는 연산자 (다른 거에 쓰면 성능이 떨어지고 악성 프로그램의 원인이 될 수 있음)

   - % : 0개 이상의 문자를 찾을 때 사용

   - _: 1개 문자를 찾을 때 사용

 

 ex1)

select *
from hr.employees
where last_name like '_i%';

출력

- 숫자나 날짜 컬럼에는 절대 쓰지 말 것!! (실행은 되지만 성능 저하 원인이 된다.)

 

ex2)

select *
from hr.employees
where last_name like 'K%';

출력

 

7. escape 연산자 (원하는 걸로 선택 가능)

 

데이터 종류 (가정)

AC_MGR

AC%MGR -- 추출

AC_PROG

AC%PROG -- 추출

 

답 : 

select *
from hr.employees
where job_id like 'AC\%%' escape '\';

- 첫 번째 %앞에 \ 써줌으로 %가 들어간 데이터를 추출하고, 뒤에 한 번 더 % 써서 뒤에 패턴도 찾는 구절이다.

select *
from hr.employees
where job_id like 'AC^%%' escape'^';

- 위에랑 같은 문장이다.

ex)

select *
from hr.employees
where last_name like '__a%'
or last_name like '__e%';

출력

- last_name에 3번째 문자가 a로 시작하거나 e로 시작하는 사원을 추출하라는 결과이다.

---

 

문제 1) employees 테이블에 있는 데이터 중에 job_id가 SA로 시작되고 salary 값은 10000 이상 받고 2005년도에 입사(hire_date) 사원들의 정보를 출력해라

 

 select *
 from hr.employees
where job_id like 'SA%'
and salary >= 10000
and hire_date between '2005-01-01' and '2005-12-31';

출력

 

문제 2) employees 테이블에서 job_id가 SA_REP 또는 AD_PRES 사원 중에 salary값이 10000 초과한 사원들의 정보를 출력해라

select *
from hr.employees
where job_id in('SA_REP','AD_PRES')
and salary > 10000;

select *
from hr.employees
where (job_id = 'SA_REP'
or job_id = 'AD_PRES')
and salary > 10000;

- 둘 다 같은 정답이다.

출력

---

8. sort (정렬)

   - order by절을 이용해서 정렬한다.

   - 기본값은 오름차순 정렬(asc)

   - 내림차순 정렬(desc)

   - order by절은 select문의 가장 마지막 절에 기술해야 한다.

   - 절들의 순서는 ANSI 표준이기 때문에 순서를 꼭 지켜야 한다.

1. select
2. from
3. where
4. group by
5. having
6. start with
7. connect by
8. order by

# 여러 예시를 봐보자

 

ex1)

select employee_id, salary
from hr.employees
order by salary;

- 따로 차순을 정의하지 않아도 기본값이 오름차순이기 때문에 결과가 이렇게 나오는 것이다.

 

ex2) 내림차순 정렬

select employee_id, salary
from hr.employees
order by salary desc;

 

ex3) 별칭 사용

select employee_id, salary*12 annual_salary
from hr.employees
order by annual_salary desc;

select employee_id, salary*12 annual_salary
from hr.employees
order by salary*12 desc;

출력

- 둘 다 출력값은 같다.

 

ex4) 대소문자 구분하려고 열별칭을 쓸 땐 order by절에도 같이 표현해야 한다!!

select employee_id, salary*12 "annual_salary"
from hr.employees
order by "annual_salary" desc;

출력

- 출력값에 볼 수 있듯이 위 컬럼에 소문자로 바뀌는 걸 볼 수 있다.

 

# select 뒤에 컬럼 위치에 따라 쉽게 표현도 가능 (현장에서도 많이 쓰는 방법이다.)

 

ex1) salary*12 이 컬럼을 내림차순으로 간편하게 표기하고 싶을 때

select employee_id, salary*12 "annual_salary"
from hr.employees
order by 2 desc;

- 추출하려는 칼럼의 위치를 표기해주는 방법이다.

출력

 

ex2) 2번째 컬럼은 오름차순으로 3번째 컬럼은 내림차순으로 정렬하고 싶을 때

select employee_id, department_id, salary*12 "annual_salary"
from hr.employees
order by 2 asc, 3 desc;

출력

- 출력값을 보면 내림차순엔 정렬이 좀 뒤죽박죽 되어있는 걸 볼 수 있다. 그 이유는 order by절에 가장 먼저 2번째 컬럼 오름차순을 우선순위로 적었기 때문에 우선적으로 결과가 보이는 것이다.

 

9. 함수 (function)

   - 기능의 프로그램

   - 단일행 함수

     1) 행당 조작하는 함수, 입력값으로 한 행에 필드값이 들어오면 출력값은 하나를 리턴한다.

     2) 문자함수, 숫자함수, 날짜함수, 형변환함수, 일반함수

         ● upper : 대문자로 변환하는 함수

         ● lower : 소문자로 변환하는 함수

         ● initcap : 첫 글자만 대문자 나머지 글자는 소문자로 변환하는 함수

        ex)

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

출력

          ● concat : 연결연산자와 동일한 기능의 함수 (무조건 2개만 입력 가능하다.)

            ex) 

select last_name || first_name, concat(last_name, first_name)
from hr.employees;

출력

- 연결연산자와 쓰임새가 같은 걸 볼 수 있다.

 

10. length

    - 문자의 길이를 리턴하는 함수

select last_name, length(last_name)
from hr.employees;

출력

 

11. lengthb

    - 문자의 바이트 값을 리턴하는 함수 (영문자는 길이랑 값이 같다.)

   

select last_name, length(last_name), lengthb(last_name)
from hr.employees;

출력

ex) 한글일 경우

select 'oracle', length('oracle'), lengthb('oracle'), length('오라클'), lengthb('오라클')
 from dual;

출력

- 출력값에 영어는 한 문자당 1byte이기 때문에 값이 같지만 한글은 한 글자당 3byte이기 때문에 값이 다르게 나온다.

 

---

※ 읽으면 좋은 책

 1. 데이터 품질 관련 책

 2. OLTP 

---

※ 데이터베이스에 설정된 문자 charactersect이 뭔가요 물어본다면?

  - AL32UTF8 : 유니코드(unicode), 전 세계 출판되는 글자는 char, varchar2 타입의 컬럼에 입력할 수 있다. 영어 1byte, 한글 3byte
  - KO16MSWIN49 : 한글, 한자, 영어, 일어글자는 char, varchar2 타입의 컬럼에 입력할 수 있다. 2byte로 저장된다.
  - select * from nls_database_parameters; 먼저 조회 (무조건 외우기) 

조회할 경우 출력값

- 답은 출력값에 NLS_CHARACTERSET    AL32UTF8이라고 대답하면 된다.

 

# national character set
  - 전 세계 출판되는 글자는 nchar, nvarchar2 타입의 컬럼에 입력할 수 있다. 유니코드
  - 저장공간이 1~4 byte 사용된다.
NLS_NCHAR_CHARACTERSET   AL16UTF16

 

12. instr : 문자의 처음 나오는 위치를 리턴하는 함수

    - instr(컬럼, 찾는 문자열, 시작위치, 몇 번째로 찾는 위치

 

ex1) 두 번째로 나오는 a의 위치가 궁금한 경우

select last_name, instr(last_name, 'a'), instr(last_name, 'a',1,2)
from hr.employees;

출력

 

ex2) last_name의 'a'가 6번째 위치부터 있는 것만 출력

select *
from hr.employees
where instr(last_name,'a',6,1) >= 1;

출력

 

13. substr : 문자를 추출하는 함수

     - substr(컬럼(문자열), 시작점, 추출개수)

     - 시작점

        1 2 3 4 5

      -5-4-3-2-1

 

ex1) 가장 앞 2글자만 추출하고 싶을 경우

select last_name,
        substr(last_name,1,2)
from hr.employees;

출력

ex2) 3번째 글자부터 다 출력하고 싶을 경우 (뒤에는 생략 가능)

select last_name,
        substr(last_name,3)
from hr.employees;

출력

ex3) 젤 마지막 글자만 뽑고 싶을 때

select last_name,
        substr(last_name, length(last_name),1)
from hr.employees;

출력

- last_name의 length가 마지막 글자 위치이기 때문에 적은 것

 

ex4) 젤 뒤에 두 글자만 뽑고 싶을 때

select last_name,
        substr(last_name, length(last_name) -1)
from hr.employees;

출력

- last_name의 length -1을 하게 되면 마지막 전 글자 위치가 되고 뒤에 생략함으로써 2글자만 출력이 된다.

 

14. substrb 

    - 문자를 바이트 수만큼 추출하는 함수

 

select
         substr('abcd',1,2),
         substrb('abcd',1,2),
         substr('가나다라',1,2),
         substrb('가나다라',1,2)
from hr.employees;

출력

- 출력하면 한글은 3바이트이기 때문에 출력이 안 나오고 2를 6으로 바꿔야 두 글자가 나온다. 

                                                                                     ↓

select
         substr('abcd',1,2),
         substrb('abcd',1,2),
         substr('가나다라',1,2),
         substrb('가나다라',1,6)
 from hr.employees;

출력

 

15. trim, ltrim, rtrim

    - trim : 왼쪽(접두), 오른쪽(접미) 부분에 연속되는 문자를 제거하는 함수, 앞에 from 키워드가 나와야 함, 공백만 제거하고 싶을 땐 ex) trim(' aabbcaa ')써도 된다.

- ltrim :  왼쪽(접두)부분에 연속되는 문자를 제거하는 함수

 - rtrim : 오른쪽(접미) 부분에 연속되는 문자를 제거하는 함수

 

ex1) 

select
       trim('a' from 'aabbcaa'),
       ltrim('aabbcaa','a'),
       rtrim('aabbcaa','a')
 from dual;

출력

 

ex2)

 select
    ' aabbcaa ',
    length(' aabbcaa '),
       trim(' ' from ' aabbcaa '),
       length(trim(' 'from ' aabbcaa ')),
       trim(' aabbcaa ')
 from dual;

출력