210908(수) DB 2일차 - select 함수들
[연산자]
= : 같다
!=, ^=, <> : 같지 않다
>=, <=, >, < : 크거나 같다, 작거나 같다, 크다, 작다
and, or, between and, in, like, is null, is not null
※ select
select [distinct] [컬럼1, 컬럼2.......][*]
from 테이블명
[where 조건절]
[order by 컬럼명 asc|desc ]
cf) where order 순서 이렇게만 가능
<Select - order by>
order by : 정렬
asc - 오름차순(생략가능, 기본오름차순)
desc - 내림차순
컬럼명 : 숫자로도 가능
ex1) 사원명, 부서ID, 입사일을 부서별로 내림차순 정렬하시오
select last_name, department_id, hire_date
from employees
order by 2 desc;
ex2) 사원명, 부서ID, 입사일을 부서별로 내림차순 정렬하시오
같은 부서가 있을때는 입사일순으로 정렬하시오
select last_name, department_id, hire_date
from employees
order by 2 desc, 3 asc; :2번째 컬럼으로 내림차순 하시오, 근데 만약 같은 데이터가 존재하면 그때 3번째 컬럼으로 오름차순 하시오라는 뜻 같은데이터없으면 안한다~!!
[문제1] 사원들의 연봉을 구한 후 연봉 순으로 내림차순 정렬하시오
답 : select last_name as "이 름", salary*12 as "연 봉" from employees order by 2 desc;
[단일행 함수]
1. 숫자함수 : mod, round(반올림, 사사오입), trunc(소수점이하다내림), ceil(소수점이하다올림)
2. 문자함수 : lower(소문자로), upper, length(글자개수), substr(부분문자열추출), ltrim(lefttrim, 왼쪽공백제거), rtrim(오른쪽공백제거), trim(좌우공백제거)
3. 날짜함수 : sysdate, add_month, month_between 달수로 계산
4. 변환함수
(1) 암시적(implict)변환 : 자동, 필요할때 자동으로
VARCHAR2 또는 CHAR ------> NUMBER
VARCHAR2 또는 CHAR ------> DATE
NUMBER ------> VARCHAR2
DATE ------> VARCHAR2
(2) 명시적(explict)변환 : 강제, 필요에따라 강제로
TO_NUMBER TO_DATE
<------ ------>
NUMBER CHARACTER DATE
------> <------
TO_CHAR TO_CHAR
- 날짜 형식 -
YYYY : 네자리 연도(숫자) (ex. 2005)
YEAR : 연도(문자)
MM : 두자리 값으로 나타낸 달 (ex. 01, 08, 12)
MONTH : 달 전체이름 (ex. January)
MON : 세자리 약어로 나타낸 달 (ex. Jan)
DY : 세자리 약어로 나타낸 요일 (ex. Mon)
DAY : 요일전체 (ex. Monday)
DD : 숫자로 나타낸 달의 일 (ex. 31, 01)
HH(12간제), HH24(24시간제)
MI(분) cf)자바는 mi
SS(초)
- 숫자 형식 -
9 : 숫자를 표시
cf) 자바는 #,###.00
오라클은 9,999.00하겠다는 얘기, 형식자체는 같고 #에서 9만
0 : 0을 강제로 표시
$ : 부동$기호를 표시
L : 부동 지역통화기호 표시
. : 소수점출력
, : 천단위 구분자 출력
5. 그룹(집합)함수 : avg, sum, max, min, count
6. 기타함수 : nvl, dcode, case(다중if같은거)
<Select - lower, mod, round, trunc, concat>
ex1) 이름을 소문자로 바꾼후 검색
'Higgins'사원의 사원번호, 이름, 부서번호를 검색하시오
select employee_id, last_name, department_id
from employees
where lower(last_name)='higgins';
ex2) 10을 3으로 나눈 나머지 구하시오(mod)
select mod(10,3) from dual; → dual : 가상의 테이블, from은 필수기 때문에 테이블이 없을 때 가상의 테이블을 써야한다
ex3) 35765.357을 반올림(round)
위치가 n일 때 n이 양수이면 (n+1)에서 반올림이 되고
n이 음수이면 n의 위치에서 반올림 된다
select round(35765.357, 2) from dual; -- 35765.36
select round(35765.357, 0) from dual; -- 35765
select round(35765.357, -3) from dual; -- 36000
ex4) 35765.357을 내림(trunc)
위치가 n일 때 n이 양수이면 (n+1)에서 반올림이 되고
n이 음수이면 n의 위치에서 반올림 된다
select trunc(35765.357, 2) from dual; -- 35765.35
select trunc(35765.357, 0) from dual; -- 35765
select trunc(35765.357, -3) from dual; -- 35000
ex5) concat('문자열1', '문자열2) : 문자열의 결합(문자열1+문자열2)
select concat('Hello', ' World') from dual;
cf)||이랑 같음 함수로쓸땐 concat~!, 근데 2개만 가능
<Select - length vs lengthb>
ex6) length('문자열') : 문자열의 길이
lengthb('문자열') : 문자열의 길이 : b는 바이트 바이트는 영문자 기준!!!
create table text (
str1 char(20), : 20은 영문자기준 바이트 수를 말한다!!!
str2 varchar2(20));
char : 고정문자길이
varchar2 : 가변문자길이
insert into text(str1,str2) values('angel', 'angel');
insert into text(str1,str2) values('사천사', '사천사');
commit;
select lengthb(str1), lengthb(str2) from text;
20 5
20 9
select length(str1), length(str2) from text;
20 5
14 3
ex7)
select length('korea') from dual; -- 5
select length('코리아') from dual; -- 3
select lengthb('korea') from dual; -- 5
select lengthb('코리아') from dual; -- 9 :b는 바이트 계산!
<Select - instr, subst>
ex8) 지정한 문자열 찾기 : instr(표현식, 찾는 문자, [위치]) 1:앞(생략가능), -1:뒤
select instr('HelloWorld', 'W') from dual; -- 6
select instr('HelloWorld', 'o', -5) from dual; -- 5 (-면 거꾸로시작해서)
select instr('HelloWorld', 'o', -1) from dual; -- 7
cf) 자바 인덱스오브랑 같다
ex9) 지정한 길이의 문자열을 추출 : substr(표현식, 시작, [개수])
select substr('I am very happy', 6, 4) from dual; -- very
select substr('I am very happy', 6) from dual; -- very happy
[문제2] 사원의 레코드를 검색하시오(concat, length)
조건1) 이름과 성을 연결하시오(concat)
조건2) 구해진 이름의 길이를 구하시오(length)
조건3) 성이 n으로 끝나는 사원(substr)
답 :
<Select - width_bucket, trim, sysdate, add_months, last_day(date)>
ex10) 임의의 값이 지정된 범위 내에 어느 위치에 있는지를 찾는다
: width_bucket(표현식, 최소값, 최대값, 구간)
최소-최대값을 설정하고 10개의 구간을 설정 후 위치 찾기
0-100까지의 구간을 나눈 후 74가 포함되어 있는 구간을 표시하시오
select width_bucket(74, 0, 100, 10) from dual; -- 8
ex11) 공백제거 : ltrim(왼), rtrim(오른), trim(양쪽)
select rtrim('test ') || 'exam' from dual;
ex12) sysdate : 시스템에 설정된 시간표시
select sysdate from dual;
select to_char(sysdate, 'YYYY"년" MM"월" DD"일"') as 오늘날짜 from dual; -> 싱글따옴표 양끝에
select to_char(sysdate, 'HH"시" MI"분" SS"초"') as 오늘날짜 from dual;
select to_char(sysdate, 'HH24"시" MI"분" SS"초"') as 오늘날짜 from dual;
ex13) add_months(date, 달수) : 날짜에 달수 더하기
select add_months(sysdate, 7) from dual;
ex14) last_day(date) : 해당달의 마지막 날
select last_day(sysdate) from dual;
select last_day('2004-02-01') from dual;
select last_day('2005-02-01') from dual;
[문제3] 오늘부터 이번 달 말까지 총 남은 날수를 구하시오
답: select last_day(sysdate) - sysdate from dual;