DB의 인덱스 번호는 1번부터 시작됨.
desc 테이블명 : 테이블구조 보는 법
SELECT *
컬럼명1, 컬럼명x | 컬럼명1, "별명", 컬럼명x "별명" ---> 조회하는 방법
select 수식(예를들면 3*
5) | 함수명() | (SUB-QUERY) 서브쿼리는 쿼리안에 쿼리
from 테이블명;
컬럼에 대한 별명 규칙 :
"" 으로 꼭 감싸야됨. 아래규칙들은 ""안에 넣는 것들
대소문자 구분
공백 가능
특수문자
// 별명생략가능(대문자만, 공백없고, 특수문자 없는 경우에는 ""생략가능)
DUAL 테이블
: 오라클에 셀렉트 필수형식을 위한 DUMMY테이블
주로 연산식을 테스트하거나 함수테스트할 때 가끔 사용
SELECT~~
FROM 테이블명1 [, 테이블명X, (SUB-QUERY)] 이런식으로 뒤에 더 올수잇음
WHERE 조건식1 [AND | OR | NOT] 조건식X
GROUP BY 그룹핑대상컬럼명 [, 그룹핑대상컬럼명X]
HAVING 그룹핑결과에대한조건식 // 해빙은 그룹바이없으면 못 씀
ORDER BY 정렬컬럼명 정렬방법, 정렬컬럼명X 정렬방법
order by에 올 수 있는거
문제 : 직원의 사번, 이름 ,급여, 정보를 제목으로 조회
답:
select empno "사번", ename "이름", sal "급여" from emp;
문제 : 직원의 사번 이름 급여 정보를 제목으로 조회 => 급여가 높은 순서대로 정렬조회
답 :
select empno "사번", ename"이름", sal "급여" from emp order by 급여 desc; order by sal desc; order by 3 desc;
// 3은 인덱스번호.
SQL전용연산자
BETWEEN A AND B : A는 시작값, B는 종료값 (A에서 B까지)
null 여부 체킹 : is null / is not null
like 부분 매칭 조회 : %(모든 문자 대체), _ (한문자 대체)
-- like '%java', like '%java%', like 'java%' 이런식으로 검색이 가능하다.
-- like '_java'
-- io가 많이 발생되기 때문에 꼭 필요할 때만 사용해야 함
in / not in : 존재 여부 체킹
exist / not exist
값 | 컬럼명 연산자 비교값
결과는 왼쪽을 기준으로 true/false
true일 경우 조회, false일 경우 조회x
select ~
from ~
where 조건식1 [and | or | not] 조건식2 인덱스번호 사용 불가
order by 인덱스번호 사용가능
직원의 모든 정보 조회 _ 급여가 2000미만인 직원들 낮은 순서대로 정렬
select * from emp where sal < 2000 order by sal asc;
// 기본정렬이 asc라서 생략가능.
직원의 모든 정보 조회 _ 급여가 2000 ~ 3000 사이인 직원들 낮은 순서대로 정렬
논리연산자:
select * from emp where sal >= 2000 and sal <= 3000 order by sal;
SQL전용연산자:
select * from emp where sal between 2000 and 3000 order by sal;
직원의 사번, 급여, 수당 조회
select empno "사번", sal "급여", comm "수당" from emp;
수당이 null인 직원의 정보 조회
select * from emp comm where comm is null;
수당이 null이 아닌 직원
select * from emp comm where comm is not null;
수당을 받지 않는 직원의 정보 조회 (수당이 null값이거나 0값인 직원들)
select * from emp where comm is null or comm = 0;
수당을 실질적으로 받은 직원의 정보 조회
select * from emp where comm >0;
미션
- 직원들에게 특별 수당을 지급하기로 함.
특별수당 = 급여 + 수당한 값에 30% 지급
---출력양식---
사번, 이름, 급여, 수당, 특별수당 조회
정렬: 특별수당 많은 순답:
select empno 사번, ename 이름, sal 급여, nvl(comm, 0) 수당, (nvl(comm, 0) + sal) * 0.3 특별수당
from emp
order by 특별수당 desc ;
NVL(arg1, arg2)
NVL2(arg1, arg2, arg3)
직원정보 조회: 수당이 많은 사람 순서대로 정렬 조회
select * from emp order by comm desc;
직원정보 조회: 수당이 적은 사람 순서대로 정렬 조회
select * from emp order by comm asc;
- null 데이터가 어느 위치에 출력되는지 체크해보기
이름에 A가 들어간 직원 조회
select ename from emp where ename like ('%A%');
이름이 J로 시작하는 직원 조회
select ename from emp where ename like ('J%');
이름이 R로 끝나는 직원 조회
select ename from emp where ename like ('%R');
이름에 두 번째 문자가 L인 직원 조회
select ename from emp where ename like ('_L%');
이름의 길이가 4자리인 직원 조회
select ename from emp where ename like ('____');
length() : 문자 길이 반환
lengthb() : 문자 byte 단위 길이 반환
(테이블 설계시 도메인데이터 분석해서 컬럼에 길이를 지정할 때 주로 사용)
한글 1글자가 3byte의 크기로 기본 설정돼 있음(oracle 11g)
// 보통은 2바이트
함수를 사용해서 길이가 4자리인 직원 조회
select ename from emp where length(ename) = 4;
'가'의 길이를 조회
'A1'의 길이를 조회
select length('가'), length('a1'), lengthb('가'), lengthb('a1') from dual;
10, 20번 부서원의 정보 조회
in 사용 :
select * from emp where deptno in (10, 20) order by deptno;
or 사용 :
select * from emp where deptno=10 or deptno=20 order by deptno;
30번이 아닌 부서원의 정보 조회
not in 사용 :select * from emp where deptno not in (30) order by deptno;
select * from emp where not deptno = 30 order by deptno;
10, 20번 부서원이 아닌 직원의 정보 조회
or 사용 :
select * from emp where not (deptno=10 or deptno=20) order by deptno;
직원의 이름, 직무, 정보를 조회
출력형식 : 000 사원님의 직무는 000 입니다.
concat()
select concat((concat(ename, ' 사원님의 직무는 ')), (concat(job, '입니다.'))) from emp;
||
select ename || ' 사원님의 직무는 ' || job || '입니다' from emp;
이름의 전체길이를 15자리로 하고 빈자리는 * 로 대체
왼쪽대체
select lpad(ename, 15,'*'
) from emp;
오른쪽 대체
select rpad(ename, 15,'*'
) from emp;
직원의 이름 2자리만 보여주고 남은 문자는 * 로 대체처리 조회
select rpad(substr(ename, 1, 2), length(ename), '*') "이름" from emp;
1234.45678
1. 소수이하 올림처리
select ceil(1234.45678) from dual;
- 100이하 버림처리
select trunc(1234.45678, -2) from dual;
- 소수이하 2자리 버림 처리
select trunc(1234.45678, 2) from dual;
- 소수이하 1자리 올림 처리
select round(1234.45678, 1) from dual;
// 틀림
select round(trunc(1234.45678, 3), 1) from dual;
select ceil(1234.4567 * 10) / 10 from dual;
- 숫자 5를 2로 나눈 나머지
select mod(5, 2) from dual;
날짜함수를 이용할 때는 date type을 이용해야 함
현재날짜/시간
: sysdate (함수가 아니라 패키지 또는 키워드라고 봐도 됨)(기본형식 : 21/06/11
last_day(date)
21/06/11
해당 date의 마지막 날짜 (매월말일)
-- select last_day(sysdate) from dual;
next_day(sysdate, arg) : 아규먼트는 1,2,3 같은 숫자 줄 수 있음.
요일수 / 지정한 다음요일에 해당하는 날짜 .. 일요일1 월요일2 화요일 ..
add_months(sysdate, arg) : 개월 수 의미, arg에 6 넣으면 6개월 후.
months_between(startDate, endDate) : 경과 개월수, 그래서 앞에 큰 값 넣음
select months_between(sysdate, '2000/01/01') from dual;
내가 살아온 개월수를 조회
- 버림처리
select trunc(months_between(sysdate, '1999/01/23')) || '개월' from dual;
날짜형식 '99/01/23'/ '99.01.23' 가능
- 반올림처리
select round(months_between(sysdate, '1999/01/23')) from dual;
- 올림처리
select ceil(months_between(sysdate, '1999/01/23')) from dual;
- 산술연산
-- date + 14
-- date - 14
select sysdate + 14, sysdate - 14 from dual;
경과 일수 계산 : sysdate - '21/05/17'
select sysdate - '21/05/17' from dual;
=>날짜를 문자로 인지해서 에러남날짜로 변환하면 됨.
select sysdate - to_date('21/05/17', 'yy/mm/dd') from dual;
숫자 / 날짜 => 문자열 변환
=> to_char(숫자, 'pattern'), to_char(date, 'pattern')
=> 123,456.78, $123, 천단위 컴마표기, 소수이하 자리수지정, 화폐통화기호 표기
=> 날짜를 원하는 형식으로 지정
문자열 => 날짜타입 변환
=> to_date('날자형식문자열', 'pattern')
문자열형식의 숫자 => 숫자 변환
// 자동형변환 해주기 때문에 거의 쓸 일 없음.
=> to_number()
요일 : 일요일 1, 월요일 2 ....
년도 : yy or yyyy
월 : mm
일 : dd (대소문자 상관없음)
시간 : hh
분 : mi
초 : ss
숫자 :
=> 999,999.99 => 1234.4567 => 1,234.45
=> 099,999.99 => 1234.4567 => 001,234.45
=> 09999 => 123 => 00123
통화기호
=> $999,999 => 1234 => $1,234
=> $999 => 1234 => #### (제대로 안나옴)
디폴트로케일 쓰고 싶어요 (통화단위를 고정해두고싶다)
=> L999,999 => 기본 설정 로케일의 화폐기호
직원 정보 조회
-조회항목 : 사번, 급여, 수당, 입사일(hiredate)
-급여, 수당은 천단위 마다 컴마 표기
-통화 단위는 기본로케일(한국)
-입사일 : 년도 4자리-월2자리-일2자리 형식으로 조회
-최근 입사자 순서대로 정렬 조회
select empno "사번", to_char(sal, 'L9,999') "급여", to_char(comm, 'L9,999') "수당", to_char(hiredate, 'yyyy-mm-dd') "입사일" from emp order by 입사일 desc;
+추가로 null 값인 부분을 0값으로 대체해주면
select empno "사번", to_char((nvl(sal, 0)), 'L9,999') "급여", to_char((nvl(comm, 0)), 'L9,999') "수당", to_char(hiredate, 'yyyy-mm-dd') "입사일" from emp order by 입사일 desc;