-1- select 검색문
- 테이블에서 모든 행(row) 및 열 반환
- 테이블에서 특정 열 반환
- 열 별칭을 사용하여 구체적인 열 머리글 부여
날짜, 문자열 상수값: ' '
컬럼 별칭에서 대소문자 구분 / 스페이스 포함하는 경우: " "
select 컬럼명, 컬럼명
from 테이블명;
-- 전체 출력
select *
from 테이블명;
-- 중복 제거 컬럼
select 컬럼명, distinct 컬럼명
from 테이블명;
select*
from 테이블명
where 조건; -- 원하는 줄을 골라냄
order by 컬럼명; -- 정렬
1. 산술 연산자 사용
* / + -
select last_name, salary, salary + 300
from employees;
2. 널 값
: 알 수 없는 값, 사용할 수 없는 값, 할당할 수 없는 값, 적용할 수 없는 값
0과 공백과는 다름.
select last_name, job_id, salary, commission_pct
from employees;-- 영업과장과 영업사원만 커미션을 받을 수 있고 다른 사원들은 커미션을 받을 수 없음
>> 이 때 널값 사용.
select last_name, 12*salary*commission_pct
from employees;-- 산술식에서 열의 값이 널이면 결과는 널임.
3. 열 별칭 정의 -- 열 머리글 이름 변경
1) 열 이름 바로 뒤에 사용. 열 이름과 별칭 사이에 AS 키워드 사용 (생략 가능)
select last_name AS name, commission_pct comm
from employees;2) 공백, 특수문자, 대소문자 구분할 경우 "" 사용
select last_name "Name", salary*12 "Annual Salary"
from employees;
4. 연결 연산자 (||)
- 열을 다른 열, 산술식, 상수값에 연결하여 문자식 생성.
- 연산자(||) 좌우에 있는 열이 결합되어 하나의 열로 출력
select last_name||job_id AS "Employees"
from employees;
5. 리터럴 문자열
- select 목록에 포함된 문자, 숫자, 날짜 ==> 각 행이 반환될 때마다 출력됨.
-- 반드시 ' '로 묶어야 함. (숫자 리터럴은 묶지 않아도 됨)
select last_name ||' is a '||job_id AS "Employee Details"
from employees;
6. 중복 행(row) 제거
select distinct department_id
from employees;
-2- 데이터 제한 및 정렬
- 질의로 검색되는 행 제한(where) & 행 정렬(order by)
1. 선택되는 행 제한 --where절
- where 절은 만족해야 할 조건 포함. from 절 다음에 옴.
- 조건이 참일 경우, 조건을 만족하는 행 반환.
select employee_id, last_name, job_id, department_id
from employees
where department_id = 90;
- 문자열 및 날짜는 ' '로 묶음. (숫자 상수는 묶지 않음)
- 문자 검색은 대소문자 구분.
- 기본 날짜 표시 형식은 DD-MON-RR
select last_name, job_id, department_id
from employees
where last_name = 'Whalen';
2. 비교조건 -- between, in(set), like, escape, is (not) null
=, >, >=, <, <=, <>(같지않음)
select last_name, salary
from employees
where salary <= 3000;
between ... and ... (범위 조건)
(두 값 사이(지정 값 포함))
select last_nema, salary
from employees
where salary between 2500 and 3500;in(set) (멤버 조건)
(값 목록 중의값과 일치)
select employee_id, last_name, salary, manager_id
from employees
where manager_id in (100, 101, 201);select employee_id, manager_id, department_id
from employees
where last_name in ('Hartstein', 'Vergas');
like (문자 검색)
- 유효한 검색 문자열 값인 대체 문자 사용하여 검색
- % (0개 이상의 일련 문자. 문자가 오지 않거나 여러개 올 수 있음)
- _ (문자 하나만 올 수 있음)
select last_name, hire_date
from employees
where hire_date like '%95';
-- 1995년 1월 ~ 1995년 12월 사이에 입사한 모든 사원의 이름, 입사일 표시select last_name
from employees
where last_name like '_o%';
-- 이름의 두번째 문자가 o인 모든 사원의 이름 표시escape 옵션
-- 검색할 문자에 실제로 %및 _문자가 포함된 경우 \ 를 이스케이프 문자로 식별.
select employee_id, last_name, job_id
from employees
where job_id like '%SA\_%' escape '\';
-- 'SA_를 포함하는 문자열 검색
is null (널값)
- 널 여부 테스트
- null 조건 : is null / is not null
select last_name, manager_id
from employees
where manager_id is null;
2. 논리 조건
and (모두 true일 때 true 반환)
select employee_id, last_name, job_id, salary
from employees
where salary >=10000
and job_id like '%MAN%';
-- 업무 id에 문자열 MAN이 포함되고 급여가 $10000이상인 사원만 선택or (하나라도 true면 true 반환)
not (뒤따르는 조건이 false면 true 반환)
select last_name, job_id
from employees
where job_id not in ('IT_PROG', 'ST_CLERK', 'SA_REP');
-- 업무 id가 IT_PROG, ST_CLERK, SA_REP가 아닌 모든 사원의 이름과 업무id 표시
where salary not between 10000 and 15000
where last_name not like '%A%'
where commission_pct is not null
-- 등 다른 sql 연산자와 함께 사용할 수 있음
3. 우선 순위 규칙
※ SQL 우선 순위 규칙
1. 산술 연산자 ( * / + - )
2. 연결 연산자( || )
3. 비교조건 ( =, >, >=, <, <=, <>(같지않음) )
4. is(not) null, like, (not) in
5. (not) between
6. not 논리 조건
7. and 논리 조건
8. or 논리 조건
select last_name, job_id, salary
from employees
where job_id = 'SA_REP'
or job_id = 'AD_PRES'
and salary > 15000;
-- 사장이면서 급여가 15000 넘는 사원 또는 영업사원의 행 선택- 괄호를 사용하여 우선순위를 강제로 지정할 수 있음
select last_name, job_id, salary
from employees
where (job_id = 'SA_REP'
or job_id = 'AD_PRES')
and salary > 15000;
-- 사장 또는 영업사원이면서 급여가 15000 넘는 사원 행 선택
4. order by 절
- 행정렬 - select 문 가장 끝에 둠.
- asc : 오름차순, 기본값
- desc : 내림차순
(desc table; -- table 구조 출력)
select last_name, job_id, department_id, hire_date
from employees
order by hire_date desc;
- 열 별칭 기준으로 정렬
select last_name, job_id, department_id, hire_date
from employees
order by hire_date;
- 여러 열 기준으로 정렬
(select 절에 포함되지 않은 열을 기준으로 정렬할 수도 있음)
select last_name, department_id, salary
from employees
order by department_id, salary desc;
-- 모든 사원의 이름&급여 표시
-- 부서 번호를 기준으로 정렬한 후 급여를 기준으로 내림차 순으로 결과 정렬
-3- 단일 행 함수
- sql에서 사용 가능한 여러 함수 유형
- select 문에서 문자, 숫자 및 날짜 함수 사용
- 변환 함수
여러 행 함수: 행 그룹을 조작하여 행 그룹 당 하나의 결과를 제공하도록 하는 함수. (그룹 함수)
단일 행 함수: 단일 행만 여산. 행 당 하나의 결과 반환. (문자, 숫자, 날짜, 변환)
- 데이터 항목 조작
- 인수 사용, 값 하나 반환
- 반환되는 각 행에 대해 작업
- 행 당 하나의 결과 반환
- 데이터 유형 수정
- 중첩 가능
- 열 또는 표현식을 인수로 사용
(인수: 사용자 지원 상수, 변수값, 열 이름, 표현식)
1. 문자 함수
1-1. 대소문자 조작 함수
lower (소문자로 변환)
upper (대문자로 변환)
initcap (첫 문자만 대문자)
| 함수 | 결과 |
| lower('SQL Course') | sql course |
| upper('SQL Course') | SQL COURSE |
| initcap('SQL Course') | Sql Course |
select employee_id, last_name, department_id
from employees
where lower(last_name) = 'higgins';
-- 비교하기 전에 last_name열을 소문자로 변환한 뒤 ' ' 값과 비교. true1-2. 문자 조작 함수
concat (첫번째 문자 값을 두번째 문자값에 연결 (||))
substr (문자값 위치 m에서 n까지 지정된 문자 반환, n 생략시 문자열의 끝까지 모든 문자 반환)
length (표현식의 문자 수 반환)
instr (지정된 문자열의 위치를 숫자로 반환)(검색시작위치, 발생횟수)(기본값은 1)
lpad / rpad (전체 폭이 n이 되도록 문자값을 오른쪽/왼쪽 정렬, 빈 곳을 지정한 string으로 채움)
trim (문자열에서 접두어나 접미어 또는 모두 자름.)
repalce (텍스트 표현식에서 문자열을 검색하여 해당 문자열을 발견한 경우 지정된 대체 문자열로 바꿈)
| concat('Hello', 'World') | HelloWorld |
| substr('HelloWorld', 1, 5) | Hello |
| length('HelloWorld') | 10 |
| instr('HelloWorld', 'w') | 6 |
| lpad(salary, 10, '*') | *****24000 |
| rpad(salary, 10, '*') | 24000***** |
| trim('H' from 'HelloWorld') | elloWorld |
| replace(컬럼명, '문자X', '문자Y') | X -> Y |
select employee_id, concat(first_name, last_name) name,
job_id, length (last_name), instr(last_name, 'a') "Contains 'a'?"
from employees
where substr(job_id, 4) = 'REP';
-- 업무 id의 4번째 문자부터 REP 문자열이 포함된 모든 사원에 대해
-- 성과 이름을 합친 전체이름, 성의 길이, 성에서 문자 a의 위치를 표시select employee_id, concat(first_name, last_name) name,
length (last_name), instr(last_name, 'a') "Contains 'a'?"
from employees
where substr (last_name, 1-, 1) = 'n';
-- 성이 n으로 끝나는 사원에 대해 데이터 표시하도록 수정
2. 숫자 함수
round (지정한 소수점 자리로 값 반올림)
trunc (지정한 소수점 자리까지 남기고 값 버림)
mod (나눗셈의 나머지 반환) (홀수 짝수 확인할 때 많이 사용)
| round (45.926, 2) | 45.93 |
| trunc (45.926, 2) | 45.92 |
| mod (1600, 300) | 100 |
-- dual: 사용자 데이터가 들어있는 테이블에서 파생되지 않는 표현식, 의사 열, 상수값 등을 한 번만 반환
select round(45.923, 2), round(45.923, 0), round(45.923, -1)
from dual;
-- 45.92 / 46 / 50select trunc(45.923, 2), trunc(45.923), trunc(45.923, -2)
from dual;
-- 45.92 / 45 / 0select last_name, salary, mod(salary, 5000)
from employees
where job_id = 'SA_REP';
-- 업무id가 SA_REP인 모든 사원에 대해 급여를 5000으로 나눈 나머지를 계산
3. 날짜 사용
- 세기, 연도, 월, 일, 시, 분, 초
- 기본 날짜 표시 형식: DD-MON-RR
- 연도의 마지막 두자리만 지정하여 20세기에 21세기 날짜를 저장할 수 있음 (동일하게 21->20 가능)
- sysdate: 데이터베이스 서버의 현재 날짜 및 시간 반환
select sysdate
from dual;3-1. 날짜 계산
- 시간을 24로 나누어 날짜에 시간을 더함.
- 날짜 뿐 아니라 숫자 상수와도 + - 가능.
| date + number | 날짜에 일 수 더함 |
| date - number | 날짜에서 일 수 뺌 |
| date - date | 한 날짜에서 다른 날짜를 뺌 |
| date + number/24 | 날짜에 시간 수를 더함. |
select last_name, (sysdate-hire_date) /7 as WEEKS
from employees
where department_id = 90;
-- 부서 90에 있는 모든 사원의 이름과 근무한 주 수를 표시.3-2. 날짜 함수
months_between (두 날짜 간의 달 수)
add_months (날짜에 달 수 더하기)
next_day (지정한 날짜의 다음날)
last_day (해당 달의 마지막 날)
round (날짜 반올림)
trunc (날짜 버림)
| months_between ('01-SEP-95', '11-JAN-94') | 19.6774194 |
| add_months ('11-JAN-94', 6) | '11-JUL-94' |
| next_day ('01-SEP-95', 'FRIDAY') | '08-SEP-95' |
| last_day ('01-FEB-95') | '28-FEB-95' |
select employee_id, hire_date,
months_between(sysdate, hire_date) TENURE,
add_months (hire_date, 6) REVIEW,
next_day (hire_date, 'FRIDAY'), last_day(hire_date)
from employees
where months_between (sysdate, hire_date) < 36;
-- 36개월보다 적게 근무한 모든 사원의 사원 번호, 입사일, 근무한 달 수, 6개월 검토일,
-- 입사 후 첫 금요일 및 입사한 달의 마지막 날을 표시| sysdate = '25-JUL-95' | |
| round(sysdate, 'MONTH') | 01-AUG-95 |
| round(sysdate, 'YEAR') | 01-JAN-96 |
| trunc(sysdate, 'MONTH') | 01-JUL-95 |
| trunc(sysdate, 'YEAR') | 01-JAN-95 |
4. 변환 함수
4-1. 암시적(implicit) 데이터 유형 변환
: oracle server가 할당문에 사용되는 값의 데이터 유형을 할당 대상의 데이터 유형으로 변환할 수 있는 경우
=> 할당문 변환 규칙 적용
| 원본 | ▶ | 대상 |
| varchar2 / char | number | |
| varchar2 / char | date | |
| number | varchar2 | |
| date | varchar2 |
: 할당문 변환 규칙이 적용되지 않을 때 데이터 유형을 변환해야 하는 경우
=> 일반적으로 표현식 변환 규칙 적용
- char -> number 시 문자열이 유효한 숫자를 나타내는 경우에만 성공
| 원본 | ▶ | 대상 |
| varchar2 / char | number | |
| varchar2 / char | date |
4-2. 명시적(explicit) 데이터 유형 변환
to_char
- number -> character (숫자 변환)
- 지원하는 자릿수 초과할 경우 #로 표시됨
- 저장된 십진수를 형식모델에서 지원하는 십진수로 반올림
select to_char(salary, '$99,999.00') SALARY
from employees
where last_name = 'Ernst';
-- $6,000.00- date -> character (사용자가 지정한 형식으로 날짜 변환) => to_char(date, 'format_model')
- 형식 모델은 ' '로 묶고 대소문자 구분
select last_name, to_char(hire_date, 'fmDD Month YYYY') as HIREDATE
from employees
-- 17 June 1987 식으로 변환select last_name,
to_char(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') HIREDATE
from employees;
-- 날짜가 Severth of June 1994 12:00:00 AM 형식으로 표시
to_number(char[, 'format_model'])
- character -> number (숫자를 포함하는 문자열을 숫자로 변환)
to_date(char[, 'format_model'])
- character -> date (날짜를 나타내는 문자열을 날짜 값으로 변환)
- ' ' 안에 공백 있으면 안됨.
- 숫자 자릿수 같아야 함.
4-3. 중첩 함수 -- to_char, to_num, to_date
- 단일 행 함수는 여러 번 중첩될 수 있음.
- 안쪽부터 바깥쪽 순으로 계산됨.
select last_name, nvl(to_char(manager_id), 'No Manager')
from employees
where manager_id is null;
-- 관리자가 없는 회사 대표 표시
-- result1 = to_char(manager_id)
-- nvl(result1, 'No Manager')
select to_char(next_day(add_months (hire_date, 6), 'FRIDAY'),
'fmDay, Month DDth, YYYY') "Next 6 Month Review"
from employees
order by hire_date;
-- 입사일로부터 여섯 달 경과 후 첫번쨰 금요일의 날짜 표시
-- 날짜는 Friday, August 13th, 1999와 같은 형식으로 표시됨
-- 입사일 기준으로 결과 정렬
4-4. 일반 함수 -- nvl, nullif, coalesce
- 데이터 유형은 서로 일치해야 함. (날짜, 문자, 숫자)
nvl (ex1, ex2) : 널값을 실제 값으로 변환
nvl (commission_pct, 9)
nvl (hire-date, '01-JAN-95')
nvl (job_id, 'No Job Yet')
select last_name, salary, nvl(commission_pct, 0),
(salary*12) + (salary*12*nvl(commission_pct, 0)) an_sal
from employees;
-- 커미션을 받는 사원에 대해 연간 총수입 계산.
-- 모든 사원에 대해 값을 계산하려면 산술연산자를 적용하기 전에 널 값을 숫자값으로 변환해야 함.
nvl2 (ex1, ex2, ex3) : ex1이 널이 아닌 경우 ex2 반환. ex1이 널인 경우 ex3 반환.
- ex1 : long을 제외한 모든 유형 가능
- ex2, ex3: ex3이 널 상수가 아니라면 ex3을 ex2의 데이터 유형으로 변환한 후 비교.
select last_name, salary, commission_pct,
nvl2(commission_pct, 'SAL+COMM', 'SAL') income
from employees
where department_id in (50, 80);
-- commission_pct열을 검사해서 값이 발견되면 'SAL+COMM', 널값이면 'SAL'
nullif (ex1, ex2) : ex1, ex2을 비교하여 동일한 경우 널, 동일하지 않은 경우 ex1 반환.
= case when ex1 = ex2 then null else ex1 end
select first_name, length(first_name) "expr1",
lsat_name, length(last_name) "expr2",
nullif(length(first_name), length(last_name)) result
from employees;
coalesce (ex1, ex2, ..., exn) : 널이 아닌 첫번째 표현식 반환.
select last_name, coalesce(commission_pct, salary, 10) comm
from employees
order by commission_pct;
4-5. 조건 표현식 -- case, decode
- if - then - else 논리 사용
case 표현식
: expr이 comparison_expr과 동일한 첫번째 when~then 쌍을 찾아서 return_expr 반환.
없으면 else_expr 반환. else절이 없으면 널 반환.
- 모든 표현식은 같은 데이터 유형이어야 함. (char, varchar2, nchar, nvarchar2)
CASE expr WHEN comparicon_expr1 THEN return_expr1
[WHEN comparicon_expr2 THEN return_expr2
WHEN comparicon_exprn THEN return_exprn
ELSE else_expr]
END
select last_name, job_id, salary,
case job_id when 'IT-PROG' then 1.10*salary
when 'ST_CLERK' then 1.15*salary
when 'SA_REP' then 1.20*salary
else salary end "REVISED_SALARY"
from employees;
-- job_id가 IT-PROG면 급여 10% 인상, ST_CLERK면 급여 15%인상, SA_REP면 20%인상.
-- 다른 업무에 대한 급여 인상은 없음.
(오라클 구문) decode 함수 : case 또는 if-then-else문의 역할 수행
DECODE(col | expr, search1, result1
[, search2, result2, ....,]
[, default])
select last_name, job_id, salary,
decode(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
revised_salary
from employees;select last_name, salary,
decode (trunc(salary/2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) tax_rate
from employees
where department_id = 80;
-- 월급에 따라 부서80의 각 사원에 대한 세율 결정.
-- 0~6은 월급 범위
