Select
SQL 1. select
select
데이터를 조회할 때 사용하는 sql의 종류이다.
** 예제는 hr table
**select~ from~ **
-
select*from tab; : 테이블 목록 조회
-
select * from 컬럼명; : 모든 컬럼을 조회
-
select 조회컬럼 from 테이블명; : 테이블명에 있는 ‘‘조회컬럼’’ 조회
- select 조회컬럼1, 조회컬럼2 from 테이블명; : 복수 조회가 가능하다.
select first_name, salary from employee;
-
연산 조회: 데이터 타입만 같다면 column끼리 연산도 가능하다.
- 날짜(date)끼리도 연산이 가능.
select first_name, salary*12 from employers; salary + commission_pct;
oracle 연산자
산술연산자 | + - * / |
---|---|
비교연산자 | > >= < <= != (<>) = |
논리연산자 | not and or |
목록연산자 | in(….) |
유사연산자 | like ‘ %’ % : 모든 문자, 문자 개수 상관 x(0개 이상) _ : 모든 문자, 한 개만 와야한다. (ex. ___: 세문자 올 수 있음) |
범위연산자 | between and |
null처리 연산자 | is null is not null |
alias (as) column 조회시 임시로 이름을 변경
select first_name as name, salary as month, salary*12 as year from employees;
distinct** : 조회시 중복되는 것은 하나만 출력
select distinct job_id from employees;
where (select ~ frome ~ where ?)
비교연산자의 결과가 true인 것만 걸러서 조회한다. 참인지 거짓인지를 파악하고 걸러내는 표현
where가 없다면, 조회시 모든 record가 조회된다.
- where 에서는 연산자가 자주 사용된다.
* select first_name, salary from employees where salary >= 10000;
* select first_name, salary from employees where salary >= 10000 and salary <=11000;
-
비교 목록이 다수일 경우 in()
select employee_id, first_name from employees where employee_id in(50, 100, 150, 200, 250, 300);
-
문자타입은 ’ ‘안에 넣어준다.
- 오라클은 문자가 하나도, 여러개도 문자타입이라고 함. 그리고 문자타입은 ‘ ‘안에 넣어준다.
select first_name from employees where first_name = 'jennifer'
-
와일드카드, %
select first_name from employees where first_name like 'J%'; : J 뒤에 아무 문자나 와도 된다. select first_name from employees where first_name like '%r'; : r 앞에 아무 문자나 와도 된다. select first_name from employees where first_name like '%er%' : er 앞 뒤로 아무 문자나 와도 된다.
-
_(언더바)
- _ 언더바는 글자의 숫자를 정해준다.
- 언더바를 특수기호 자체로 쓰겠다면, \를 앞에 붙인다. escape ‘\역슬레쉬’; 는 조회 조건에 \빼도 된다는 소리.
select job_id from employees where job_id like '___MAN'; : 언더바의 수에 따라 글자수가 정해진다.
between and
between A and B 를 해주게 되면 A와 B사이의 값을 가져온다.
select first_name, hire_date from employees where hire_date >= '05-JAN-01' and hire_date > '05-SEP-31';
: 위의 식은 아래의 between and로 바꿀 수 있다.
select first_name, hire_date from employees where hire_date between '05-JAN-01' and '05-SEP-31';
order by
데이터 정렬에 쓰인다. 주의할 점: select ~ from ~ where ? order by? 순서를 지켜야 한다.
- asc : 오름차순
- asc는 생략이 가능하다.
select first_name from employees order by first_name asc;
: first_name을 오름차순으로 정렬한다. (알파벳 순)
- desc : 내림차순
select first_name from employees order by first_name desc;
:first_name을 내림차순으로 정렬한다. (알파벳 역순)
-
**order by column or index or alias [asc 생략] desc** - index: 1은 조회하겠다고 쓴 컬럼 순서 중 첫번째 컬럼을 의미. 오라클은 첫 번째 데이터는 0이 아니라 1이다.
select hire_date, first_name from employees order by 1;
- alias: 별명으로 대신 언급이 가능
select hire_date as HireDate from employees order by HireDate;
-
null
오라클은 모든 타입의 데이터가 값이 없으면 null로 처리하는데, 이것은 공백으로 표현된다.
column에 null값이 있을 경우, null(공백)은 다음과 같이 위치된다.
order by 1 asc; --> null 마지막 order by 1 desc; --> null 처음
조회시 값이 있는가를 조회해볼 때는 is null or is not null
select commission_pct from employees where commission_pct is not null;
oracle 함수
-
upper() : 문자를 대문자로 바꿔준다
select first_name, upper(first_name) from employees;
-
oracle date: 오라클의 날짜 내장 포멧 ( 변경 가능 )
select sysdate from dual; : 현재 시스템 날짜 시각 정보를 보여준다. (기본 포맷을 확인할 수 있음)
-
날짜도 대소비교와 연산이 가능하다. + - 는 1일 단위로 이루어진다.
rr/mm/dd - rr/mm/dd 의 연산도 가능하다.
-
rr : 0-49 값: 2000년대, 50-99 값 : 1900년대
select first_name, hire_date from employees where hire_date >= '05-JAN-01' and hire_date > '05-SEP-31'; : 날짜는 최근에 가까울수록 큰 값을 갖는다.
-
-
rownum : 조회하는 행번호를 생성해주는 함수
select rownum, hire_date from employees; : hire_date 앞에 1. 2. 3.. 의 행번호가 생성되어 출력된다.
subquery
select…(select,,, ) 식으로 메인 select 안에 먼저 실행되어야 할 select가 위치하는 구조가 subquery이다.
메인쿼리 안에 괄호를 써서 서브쿼리형태를 써주는 것, 메인쿼리 전에 먼저 실행해야 할 쿼리가 있다면 서브쿼리를 사용하면 된다.
-
top-n Query
from(select)
순서
작성순서 실행순서 select
from
where
order byfrom
where
select
order by- from이 가장 먼저 실행되기 때문에, 프롬절 안에 select을 넣으면 select가 1번으로 실행된다. ( )는 테이블처럼 생각하면 된다.
: subquery 전 select rownum, hire_date from employees where rownum <= 5 order by hire_date desc; ROWNUM HIRE_DATE ---------- --------- 5 21-MAY-07 4 03-JAN-06 2 21-SEP-05 1 17-JUN-03 3 13-JAN-01 : subquery 후 select rownum, hire_date from(select*from employees order by hire_date desc) where rownum <=5; ROWNUM HIRE_DATE ---------- --------- 1 21-APR-08 2 21-APR-08 3 24-MAR-08 4 08-MAR-08 5 23-FEB-08
-
where(select..; )
조회를 우선 해보고, 그 조회가 또 다른 쿼리에 사용되어야 할 때의 subquery
select first_name from employees where department_id = (select department_id from departments where department_name = 'Sales'); :employee에 있는 department_id는 department_id의 column을 갖고 있지 않고 department 테이블을 호출 한다. 따라서 우선적으로 department에서 department_id를 조회한 뒤에 그 값을 employee에 있는 column과 비 교해 조회해야 한다.
** subquery에서 비교연산자를 쓸 때 같은 데이터타입인지 반드시 확인한다.
** = 연산자는 하나의 값을 비교한다. in 연산자는 여러개의 목록을 가질 수 있다. subquery가 단 하나만 리턴할 것이라는 확신이 없다면 in을 사용하도록 한다.
단일행 리턴 | = / > / >= < <= != |
---|---|
다중행 리턴 | in not in > all > any |
-
다중행 리턴 all / any
모든 William의 급여와 같거나 많을 때 (예 6500, 1000, 3000)
6500보다 같거나 많을 때 (>= all)
select employee_id, first_name, salary from employees where salary > =all(select salary from employees where first_name = 'William');
1000보다 같거나 많을 때 (>=any)
select employee_id, first_name, salary from employees where salary > =any(select salary from employees where first_name = 'William');
그룹함수
:여러개의 데이터를 가지고 단 하나의 값만 리턴해준다.
- 그룹함수 조회 SELECT절에 다른 컬럼 기술은 불가능하다. ( 개수가 다르기 때문 )
- 단 GROUP BY 뒤 기술컬럼은 제외
sum() | 총합계 (숫자타입만 가능) |
---|---|
avg() | 평균 (숫자타입만 가능) |
count() | 개수 세어오기 (숫자, 문자, 날짜 가능) |
max() | 최대값 (숫자, 문자, 날짜 가능) |
min() | 최소값 (숫자, 문자, 날짜 가능) |
stdev() | 표준편차 (숫자타입만 가능) |
variance() | 분산 (숫자타입만 가능) |
employees table 총 합계 / 평균 / 개수 / 최대,최소 급여값 조회
select sum(salary) from employees;
select avg(salary) from employees;
select count(salary) from employees;
select count(salary) as SALARY, count(department_id) as COUNT from employees;
: null값은 빼고 출력
select count(salary), count(department_id), 2 count(*) from employees;
: null값을 표현할 때
select max(first_name), min(first_name) from employees;
select max(hire_date), min(hire_date) from employees;
사원이름, 최대급여 조회:
SELECT first_name, MAX(salary) FROM employees;
>> 오류. 그룹함수는 하나의 값만 보여주기 때문에, 개수가 맞지 않을 때 문제.
WHERE절 조건 사용하기:
SELECT first_name, salary from employees WHERE salary = (SELECT MAX(salary) FROM employees);
SELECT first_name, salary from employees WHERE salary = (SELECT MAX(salary) FROM employees) or salary = (SELECT min(salary) FROM employees);
:
GROUP BY
전체를 묶는 게 아닌, 특정 파트별 그룹을 구할 때
사원 부서별 급여 총합 조회 :
SELECT department_id, SUM(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id ;
부서별 안에 직종별 조회:
SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id IS NOT NULL GROUP BYjob_id ORDER BY department_id;
HAVING
그룹함수 조건식 : group by의 조건을 만들어준다.
부서별 급여 총합 조회하되 부서별 급여 총합이 10000미만인 부서의 결과만 조회:
SELECT department_id, SUM(salary) FROM employees WHERE SUM(salary) < 10000 GROUP BY department_id;
(FROM > WHERE > GROUP BY > SELECT)
:SUM(salary)는 SELECT에서 실행, 따라서 순서가 맞지 않음. 없는 값의 조건(WHERE)이 먼저 발생하기 때문에 오류
SELECT department_id, SUM(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id HAVING SUM(salary) < 10000;
(FROM > WHERE > GROUP BY > HAVING > SELECT)
부서별로 급여 총합 조회하되 사원의 급여가 5000미만은 제외하고, 부서별 ㄱ브여 총합이 50000이상인 부서의 결과만 조회 + order by
SELECT department_id, SUM(salary) FROM employees WHERE salary>=5000 GROUP BY department_id HAVING SUM(salary) >= 50000 ORDER BY SUM(salary) desc, department_id;
ROLLUP
부분합: 총합을 보여주고, 그 안에서 파트별 총합을 한 번 더 구해준다.
기준이 되어진 파트의 초압
SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY ROLLUP(department_id, job_id);
: 부서, 직종을 나누고 부서의 총합을 한 번 더 보여줌.
CUBE
SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY CUBE(department_id, job_id);
:직종별 합계를 보여주고, 모든 부서의 총합이 한 번 더 보여진다.