yoni
오라클 본문
show user;
show user;
grant select on departments to pmorauser; -- 이렇게하면 pmoruser에서도 departments테이블을 select해서 읽어올 수 있다.
grant select on employees to pmorauser;
select *
from dba_users; --관리자만 볼수있다. 그래서 오류
-- 현재 오라클 서버에 접속한 사용자(hr)가 만든 테이블명과 뷰명을 조회하는것
select * from tab;
/*
중요 !! 꼭 암기!!!
>>> select 문의 처리순서 <<<
5. select 컬럼명 -- select (고르다)
1. from 테이블명
2. where 조건절 ---- where 절이 뜻하는 것은 해당 테이블에서 조건에 만족하는 행을 메모리(RAM)에 로딩(올리는것)하는 것이다.
3. group by 절
4. having 그룹함수
6. order by 절
*/
select *
from employees;
-- 현재 오라클 서버에 접속한 사용자(hr)가 만든 테이블명에 주석문을 단것을 조회하는 것.
--테이블 생성 이후에 해야할 일은 테이블명과 컬럼명에 주석문을 달아 두어야 한다.
select *
from user_tab_comments;
-- 현재 오라클 서버에 접속한 사용자(hr)가 만든 테이블명에 주석문을 단것을 조회하는 것.
create table tbl_member_test --tbl = 테이블
(id varchar2(20) -- 괄호 안은 바이트길이
,passwd varchar2(20)
,name varchar2(20)
,addr varchar2(100)
);
comment on table tbl_member_test -- 주석(코멘트 달기)
is '회원정보 입력 임시 테이블z';
-- 현재 오라클 서버에 접속한 사용자(hr)가 만든 employees 테이블에 존재하는 컬럼에 주석문을 단것을 조회하는 것.
select column_name, comments
from user_col_comments
where table_name='EMPLOYEES';
select column_name, comments
from user_col_comments
where table_name='TBL_MEMBER_TEST'; -- 테이블name과 컬럼name 은 대문자!!
--컬럼에대한 주석문달기
comment on column tbl_member_test.id
is '회원 아이디';
comment on column tbl_member_test.passwd
is '회원 암호';
comment on column tbl_member_test.name
is '회원 이름';
comment on column tbl_member_test.addr
is '회원의 주소';
-- 데이터 추가하기
insert into tbl_member_test
values('superman','pclass','송성학','경기도 평택');
commit;
select*
from tbl_member_test --이 테이블에서 패스워드 값이 PCLASS =(같은) 행을 추출해라 라는거.
where passwd = 'PCLASS';
select*
from tbl_member_test --이 테이블에서 패스워드 값이 pclass =(같은) 행을 추출해라 라는거. (대문자를 구분함)
where passwd = 'pclass';
select comments -- 커멘트만 보자는 말.
from user_tab_comments
where table_name='EMPLOYEES';
--
describe employees;
--또는
desc employees;
-- employees 테이블에 존재하는 컬럼명과 컬럼의 데이터 타입을 조회해 주는것.
-- NUMBER(8,2) = -999999.99 ~ 999999.99
-- NUMBER(6) = -999999 ~ 999999
-------------------------------------------------------------------------------------------------------------------------
--몽땅 다 . 보겠다.
select * --(보고자하는것)
from employees
-- where(조건) 몽땅 다 올리니 조건이 필요없다. -- where 절이 뜻하는 것은 해당 테이블에서 조건에 만족하는 행을 메모리(RAM)에 로딩(올리는것)하는 것이다.
--특정컬럼만볼때.
select employee_id, first_name, last_name, salary --특정값 설정
from employees;
-------------------------------------
--컬럼(column) = Field = 열 = 속성
-------------------------------------
--EMPLOYEE_ID : 사원번호
--FIRST_NAME : 성
--LAST_NAME : 이름
--EMAIL : 이메일
--PHONE_NUMBER : 핸드폰번호
--HIRE_DATE : 입사일자
--JOB_ID : 직종아이디
--SALARY : 기본급여
--COMMISSION_PCT : 수당퍼센티지
--MANAGER_ID : 직속상관의 사원번호
--DEPARTMENT_ID : 부서번호
-- 행(Row) = Record = 튜플(Tuple)
-- 한직원 한직원 행이 다모인것이 = 테이블.
-- employees = 테이블 = Entity(엔티티)
select *
from departments; -- 부서정보 테이블
select *
from locations; -- 지역정보 테이블
select * from tab;
select *
from countries; -- 국가 정보 테이블
select *
from jobs; -- 직업 정보 테이블
desc departments;
--DEPARTMENT_ID : 부서 번호
--DEPARTMENT_NAME : 부서명
--MANAGER_ID : 부서장의 사원번호
--LOCATION_ID : 지역아이디
--1. employees 테이블에서 부서번호가 50번인 사원들에 대해 사원번호,사원명,입사일자,기본급여를 나타내세요.
select employee_id, last_name, first_name, hire_date, salary --(보고자 하는것)
from employees --(테이블 에서~)
where Department_Id=50; --( 조건 (부서번호가 50번인 사원들)) -A
--2. employees 테이블에서 부서번호가 50번인 사원들에 대해 사원번호,사원명,입사일자,기본급여,직종번호,부서번호 를 나타내세요.
select employee_id, last_name, first_name, hire_date, salary ,--(보고자 하는것)
job_id,department_id
from employees --(테이블 에서~)
where Department_Id=50; --( 조건 (부서번호가 50번인 사원들)) -B
--[퀴즈] 위에 A와 B중에 CPU가 연산하는 시간이 적은 것은?
-- select이 적을수록 CPU연산이 작다 = 그러므로 A
--[퀴즈] 위에 A와 B중에 메모리(RAM)에 로드되어지는 크기가 작은것은?
-- A와 B는 같다. 모든것이 올라가는것이고 화면에 보이는것은 select절에 해당되는것만 뽑아서 모니터에 올라옴.
------------ ***** NULL 처리 ***** ------------
null 은 존재하지 않는 것이므로 사칙연산( + - * / )에 들어가면 그 결과는 무조건 null 이다.
select 2+3 , 2+null, 3-null, 0*null, 1/null
from dual;
select employee_id,first_name,
last_name, salary, commission_pct,
salary * commission_pct,
salary + (salary*commission_pct)
from employees;
------***** null을 처리해주는 함수*****------
1. nvl (numvalue)
2. nvl2
select nvl(7,3), -- null값이 없으면 앞에것을 나타냄.
nvl(null,3),
nvl('이순신','거북선'),
nvl(null,'거북선')
from dual;
-- null이라면 null이 아닌것을 나타냄.
select employee_id,first_name,
last_name, salary, commission_pct,
salary * commission_pct,
salary + (salary*commission_pct),
nvl(salary + (salary*commission_pct), salary) -- null인 사람들은 ,salary를 보여내어라 라는 뜻.
from employees;
select employee_id as "사원번호", --오라클에서 as 는 이퀄(같다)라는 말.
first_name "성", -- as는 생략가능하다.
last_name 이름, -- "" 를 생략가능
salary "기본 급여", -- 띄우고 싶을때는 ""를 사용해서 한다.
commission_pct as 수당퍼센티지,
nvl(salary + (salary*commission_pct), salary) "월 급여" -- null인 사람들은 ,salary를 보여내어라 라는 뜻.
from employees;
--5/10-----------------------------------------------------------
select '자바 '||'오라클' -- ||이 문자열을 붙여준다.
from dual;
select employee_id as "사원번호",
first_name ||' '|| last_name as "사원명", -- first와 last name을 붙였다. ||
salary "기본 급여",
commission_pct as 수당퍼센티지,
nvl(salary + (salary*commission_pct), salary) "월 급여"
from employees;
select '오늘은 ' || sysdate || ' 입니다.' || 2*3 || '입니다.' --문자든 숫자든 둘이 붙일 수 있다.
from dual;
-- nvl2 설명.
select nvl2(7,3,2), --null이 아니라면 2번째값을 나타내주고 끝내줌.
nvl2(null,3,2), -- null이라면 3번째값을 나타내주고 끝냄.
nvl2('이순신','거북선','난중일기'), -- null아니라 2번째값 나타냄
nvl2(null,'거북선', '난중일기') -- null이라 3번째값 나타냄
from dual;
select employee_id as "사원번호",
first_name ||' '|| last_name as "사원명",
salary "기본 급여",
commission_pct as 수당퍼센티지,
nvl(salary + (salary*commission_pct), salary) "월급1",
nvl2(commission_pct, salary + (salary*commission_pct))
from employees;
desc employees; -- employees 에 저장된 컬럼이 뭔지 알아볼때.
----- 테이블에 컬럼 추가하기 (employees 테이블에 주민번호 컬럼을 추가해 보겠습니다.)
alter table employees
add jubun varchar2(13) -- 문자열로 생성하는이유. 숫자일경우 10이나 010 이나 같이보기 때문이다.
select 10+2 , 010+2
from dual;
select 10, 010, '10' ,'010'
from dual;
select 0207304123456, '0207304123456'
from dual;
-- 제일앞에 0이 올수 있는 데이터라면 무조건 문자로 해야한다. 숫자로 하면 안된다.
desc employees -- 만든 jubun 컬럼 조회.
select * from employees
update employees set jubun = '5510151234567'
where employee_id = 100;
update employees set jubun = '8510151234567'
where employee_id = 101;
update employees set jubun = '6510152234567'
where employee_id = 102;
update employees set jubun = '7510151234567'
where employee_id = 103;
update employees set jubun = '5510152234567'
where employee_id = 104;
update employees set jubun = '6510151234567'
where employee_id = 105;
update employees set jubun = '5809201234567'
where employee_id = 106;
update employees set jubun = '0803153234567'
where employee_id = 107;
update employees set jubun = '0712154234567'
where employee_id = 108;
update employees set jubun = '8810151234567'
where employee_id = 109;
update employees set jubun = '8908152234567'
where employee_id = 110;
update employees set jubun = '9005052234567'
where employee_id = 111;
update employees set jubun = '6610151234567'
where employee_id = 112;
update employees set jubun = '6710151234567'
where employee_id = 113;
update employees set jubun = '6709152234567'
where employee_id = 114;
update employees set jubun = '5710151234567'
where employee_id = 115;
update employees set jubun = '5610151234567'
where employee_id = 116;
update employees set jubun = '5810152234567'
where employee_id = 117;
update employees set jubun = '7810151234567'
where employee_id = 118;
update employees set jubun = '7909151234567'
where employee_id = 119;
update employees set jubun = '7702152234567'
where employee_id = 120;
update employees set jubun = '7009151234567'
where employee_id = 121;
update employees set jubun = '7111011234567'
where employee_id = 122;
update employees set jubun = '8010131234567'
where employee_id = 123;
update employees set jubun = '8110191234567'
where employee_id = 124;
update employees set jubun = '9012132234567'
where employee_id = 125;
update employees set jubun = '9406251234567'
where employee_id = 126;
update employees set jubun = '9408252234567'
where employee_id = 127;
update employees set jubun = '9204152234567'
where employee_id = 128;
update employees set jubun = '8507251234567'
where employee_id = 129;
update employees set jubun = '6511111234567'
where employee_id = 130;
update employees set jubun = '0010153234567'
where employee_id = 131;
update employees set jubun = '0005254234567'
where employee_id = 132;
update employees set jubun = '0110194234567'
where employee_id = 133;
update employees set jubun = '0412154234567'
where employee_id = 134;
update employees set jubun = '0503253234567'
where employee_id = 135;
update employees set jubun = '9510012234567'
where employee_id = 136;
update employees set jubun = '9510021234567'
where employee_id = 137;
update employees set jubun = '9610041234567'
where employee_id = 138;
update employees set jubun = '9610052234567'
where employee_id = 139;
update employees set jubun = '7310011234567'
where employee_id = 140;
update employees set jubun = '7310092234567'
where employee_id = 141;
update employees set jubun = '7510121234567'
where employee_id = 142;
update employees set jubun = '7612012234567'
where employee_id = 143;
update employees set jubun = '7710061234567'
where employee_id = 144;
update employees set jubun = '7810052234567'
where employee_id = 145;
update employees set jubun = '6810251234567'
where employee_id = 146;
update employees set jubun = '6811062234567'
where employee_id = 147;
update employees set jubun = '6712052234567'
where employee_id = 148;
update employees set jubun = '5911251234567'
where employee_id = 149;
update employees set jubun = '5910062234567'
where employee_id = 150;
update employees set jubun = '6010191234567'
where employee_id = 151;
update employees set jubun = '5712062234567'
where employee_id = 152;
update employees set jubun = '5810231234567'
where employee_id = 153;
update employees set jubun = '6011051234567'
where employee_id = 154;
update employees set jubun = '6010182234567'
where employee_id = 155;
update employees set jubun = '6110191234567'
where employee_id = 156;
update employees set jubun = '6210112234567'
where employee_id = 157;
update employees set jubun = '6311132234567'
where employee_id = 158;
update employees set jubun = '8511112234567'
where employee_id = 159;
update employees set jubun = '8710131234567'
where employee_id = 160;
update employees set jubun = '8710072234567'
where employee_id = 161;
update employees set jubun = '9010171234567'
where employee_id = 162;
update employees set jubun = '9112072234567'
where employee_id = 163;
update employees set jubun = '9110241234567'
where employee_id = 164;
update employees set jubun = '9212251234567'
where employee_id = 165;
update employees set jubun = '9310232234567'
where employee_id = 166;
update employees set jubun = '9811151234567'
where employee_id = 167;
update employees set jubun = '9810252234567'
where employee_id = 168;
update employees set jubun = '9910301234567'
where employee_id = 169;
update employees set jubun = '0910153234567'
where employee_id = 170;
update employees set jubun = '1011153234567'
where employee_id = 171;
update employees set jubun = '1006153234567'
where employee_id = 172;
update employees set jubun = '1111154234567'
where employee_id = 173;
update employees set jubun = '1209103234567'
where employee_id = 174;
update employees set jubun = '1207154234567'
where employee_id = 175;
update employees set jubun = '0906153234567'
where employee_id = 176;
update employees set jubun = '0812113234567'
where employee_id = 177;
update employees set jubun = '9810132234567'
where employee_id = 178;
update employees set jubun = '8712111234567'
where employee_id = 179;
update employees set jubun = '8310012234567'
where employee_id = 180;
update employees set jubun = '6510191234567'
where employee_id = 181;
update employees set jubun = '6510221234567'
where employee_id = 182;
update employees set jubun = '6510232234567'
where employee_id = 183;
update employees set jubun = '8512131234567'
where employee_id = 184;
update employees set jubun = '8510182234567'
where employee_id = 185;
update employees set jubun = '7510192234567'
where employee_id = 186;
update employees set jubun = '8512192234567'
where employee_id = 187;
update employees set jubun = '9511151234567'
where employee_id = 188;
update employees set jubun = '7510192234567'
where employee_id = 189;
update employees set jubun = '8510161234567'
where employee_id = 190;
update employees set jubun = '9510192234567'
where employee_id = 191;
update employees set jubun = '0510133234567'
where employee_id = 192;
update employees set jubun = '0810194234567'
where employee_id = 193;
update employees set jubun = '0910183234567'
where employee_id = 194;
update employees set jubun = '1010134234567'
where employee_id = 195;
update employees set jubun = '9510032234567'
where employee_id = 196;
update employees set jubun = '9710181234567'
where employee_id = 197;
update employees set jubun = '9810162234567'
where employee_id = 198;
update employees set jubun = '7511171234567'
where employee_id = 199;
update employees set jubun = '7810172234567'
where employee_id = 200;
update employees set jubun = '7912172234567'
where employee_id = 201;
update employees set jubun = '8611192234567'
where employee_id = 202;
update employees set jubun = '7810252234567'
where employee_id = 203;
update employees set jubun = '7803251234567'
where employee_id = 204;
update employees set jubun = '7910232234567'
where employee_id = 205;
update employees set jubun = '8010172234567'
where employee_id = 206;
commit;
select * from employees
--[문제] employees 테이블에서 각 부서번호마다 존재하는 직종ID값을 출력하세요.
select department_id, job_id, first_name ||' ' || last_name
from employees;
select department_id, job_id -- 이름은 필요없음. 하지만 같은 직종들이 중복이됨..
from employees;
-- select 되어진 결과물에서 행의 결과값이 중복되어 나온다면 중복된 행을 제거하고 1개만 보이고 싶다.
--> select 다음에 distinct 을 사용하면 된다.
select distinct department_id, job_id
from employees;
-- select 되어진 결과물을 정렬(sort)하려면 order by절을 사용하면 된다.
select distinct department_id, job_id
from employees
order by department_id asc; -- 오름차순
select distinct department_id, job_id
from employees
order by department_id desc;-- 내림차순
select distinct department_id, job_id
from employees
order by department_id; -- 오름차순 ( asc 를 안써줘도 자동으로 오름차순 )
-------------------------*********** 비교 연산자 ***********--------------------------------------
-- [문제] employees 테이블에서 부서번호가 50번은 부서에 근무하는 사원들의
-- 부서번호,사원번호,사원명,월급을 나타내세요.
select department_id as "부서번호",
employee_id as "사원번호",
first_name || ' ' || last_name as "사원명",
nvl2(commission_pct, salary+(salary*commission_pct), salary) as "월급"
from employees
where department_id = 50
-- [문제] employees 테이블에서 부서번호가 50번이 아닌 부서에 근무하는 사원들의
-- 부서번호,사원번호,사원명,월급을 나타내세요.
select department_id as "부서번호", employee_id as "사원번호" ,
first_name || ' ' || last_name as "사원명",
nvl2(commission_pct, salary+(salary*commission_pct), salary) as "월급"
from employees
where department_id != 50 -- <> , != , ^= 3가지 모두 같지않다는말 1
--order by department_id;
--order by "부서번호";
order by 1; --1번째의 컬럼(부서번호세로줄)을 오름차순해라.
select department_id as "부서번호", employee_id as "사원번호" ,
first_name || ' ' || last_name as "사원명",
nvl2(commission_pct, salary+(salary*commission_pct), salary) as "월급"
from employees
where department_id <> 50 -- <> , != , ^= 3가지 모두 같지않다는말 2
order by 1;
select department_id as "부서번호", employee_id as "사원번호" ,
first_name || ' ' || last_name as "사원명",
nvl2(commission_pct, salary+(salary*commission_pct), salary) as "월급"
from employees
where department_id ^= 50 -- <> , != , ^= 3가지 모두 같지않다는말 3
order by 1;
select department_id as "부서번호", employee_id as "사원번호" ,
first_name || ' ' || last_name as "사원명",
nvl2(commission_pct, salary+(salary*commission_pct), salary) as "월급"
from employees
where department_id < 50 -- 50번 미만 부서번호.
order by 1;
select department_id as "부서번호", employee_id as "사원번호" ,
first_name || ' ' || last_name as "사원명",
nvl2(commission_pct, salary+(salary*commission_pct), salary) as "월급"
from employees
where department_id >= 50
order by 1;
-- null은 존재하지 않는 것이므로 비교연산자( =, != , <>, ^=, >, <, >=, <= )를 사용할 수 없다. 대신에 is연산자를 사용한다.
-- employees 테이블에서 부서번호를 배정받지 못한 사원들을 출력하세요.
select *
from employees
where department_id = null; -- 안나옴.
select *
from employees
where department_id is null; -- null은 비교연산자를 못쓰고 is연산자를 쓴다.
-- employees 테이블에서 부서번호를 배정받은 사원들을 출력하세요 ( null이 아닌사원 모두 출력.)
select *
from employees
where department_id != null; -- 안됨.
select *
from employees
where department_id is not null; -- is not 연산자를 사용.
-- [문제] employees 테이블에서 부서번호가 50번이 아닌 부서에 근무하는 사원들의
-- 부서번호,사원번호,사원명,월급을 나타내세요. ( null 까지 )
select department_id as "부서번호", employee_id as "사원번호" ,
first_name || ' ' || last_name as "사원명",
nvl2(commission_pct, salary+(salary*commission_pct), salary) as "월급"
from employees
where nvl(department_id, -9999) != 50
order by 1;
select department_id, nvl(department_id,-9999), -- department_id의 값이 널이라면 -9999라 하겠다. select 부분은 보여주고자 하는 부분을 나타내는것.
first_name || ' ' || last_name
from employees
select department_id as "부서번호",
nvl(department_id,-9999) as"부서번호2", -- department_id값이 null이라면 -9999라 한것을 보여주겠다는것 . select 부분은 보여주고자 하는 부분을 나타내는것.
employee_id as "사원번호" ,
first_name || ' ' || last_name as "사원명",
nvl2(commission_pct, salary+(salary*commission_pct), salary) as "월급"
from employees
where nvl(department_id, -9999) != 50
order by 1;
/*
오라클은 테이블에 저장된 데이터값에 대해서만 대,소문자를 구분한다.
employees 테이블에서 성(frist_name)이 'John' 인 사원만
사원번호, 사원명, 기본급여의 정보를 추출하세요.
*/
select employee_id,
first_name || ' ' || last_name,
salary
from employees
where first_name = 'John';
select employee_id,
first_name || ' ' || last_name,
salary
from employees
where first_name = 'john'; -- 데이터값에 대해 대소문자 구분되어서 안나옴.
select employee_id,
first_name || ' ' || last_name,
salary
from employees
where first_name = 'JOHN'; -- 마찬가지로 데이터값에 대해 구분되어서 안나온다.
--그런데 나오고 싶다..
--- 문자열 함수중에 대,소문자를 변경해주는 것이 있다.
--1. upper : 모든 문자를 대문자로 변경해주는 함수.
--2. lower : 모든 문자를 소문자로 변경해주는 함수.
--3. initcap : 단어를 첫글자만 대문자로 나머지는 소문자로 변경해주는 함수.
select 'kOrEA seOUL',
upper('kOrEA seOUL') ,
lower('kOrEA seOUL') ,
initcap('kOrEA seOUL')
from dual;
select employee_id, -- 화면에 원래 값이 보임 .
first_name,
first_name || ' ' || last_name,
salary
from employees
where lower(first_name) = lower('jOhN'); -- 전부가 같이 lower로 바꿔주면 검색이 가능하다. 화면에 보여줄때는 원래대로 나온다 select이 담당함.
----- [문제] employees 테이블에서 성명을 'jOHn sEo' 로 검색을 하였을때
------ 사원번호 , 성, 이름, 기본급여가 나와야한다.
select employee_id 사원번호,
first_name 성,
last_name 이름,
salary 기본급여
from employees
where upper(first_name || ' ' || last_name) = upper('jOHn sEo'); --가운데 띄어쓰기 주의.
-----****** 연산자 or , and , not, in 연산자에 대해서 알아본다. ********-------------------
-- employees 테이블에서 부서번호가 30, 60, 90번 부서에 근무하는 사원들에 대해서
-- 사원번호, 사원명, 연봉(월급 *12), 부서번호를 출력하세요.
SELECT employee_id,
first_name || ' ' || last_name,
nvl(salary+(salary*commission_pct), salary)*12 as "연봉", --null인 사람들은 ,salary를 보여내서 *12 = 연봉 이라는 뜻 (월급*12)
department_id
from employees
where department_id = 30 or
department_id = 60 or
department_id = 90;
SELECT employee_id,
first_name || ' ' || last_name,
nvl(salary+(salary*commission_pct), salary)*12 as "연봉",
department_id
from employees
where department_id in(30,60,90); -- in 연산자 괄호 안은 or
-- 단 or는 in 보다 속도가 빠르므로 현업에서는 불편하더라도 or를 쓰자.
-- 연산자 or , and , not, in
-- employees 테이블에서 부서번호가 30, 60, 90번이 아닌 부서에 근무하는 사원들에 대해서
-- 사원번호, 사원명, 연봉(월급 *12), 부서번호를 출력하세요.
SELECT employee_id,
first_name || ' ' || last_name,
nvl(salary+(salary*commission_pct), salary)*12 as "연봉",
department_id
from employees
where department_id != 30 and
department_id != 60 and
department_id != 90;
--or 은 한개라도 맞으면 맞는것
--and 는 모두 맞아야 맞는것.
SELECT employee_id,
first_name || ' ' || last_name,
nvl(salary+(salary*commission_pct), salary)*12 as "연봉",
department_id
from employees
where department_id not in(30,60,90); -- not in 은 괄호 안은 and
--null인 사람것도 구하기.
SELECT employee_id,
first_name || ' ' || last_name,
nvl(salary+(salary*commission_pct), salary)*12 as "연봉",
department_id
from employees
where nvl(department_id, -9999) not in(30,60,90);
--같은것.
SELECT employee_id,
first_name || ' ' || last_name,
nvl(salary+(salary*commission_pct), salary)*12 as "연봉",
department_id
from employees
where nvl(department_id,-9999) != 30 and
nvl(department_id,-9999) != 60 and
nvl(department_id,-9999) != 90;
-- employees 테이블에서 부서번호가 30, 60, 90번 부서가 아닌 근무하는 사원들에 대해서
-- 사원번호, 사원명, 연봉(월급 *12), 부서번호를 부서번호의 오름차순으로 정렬한 후, 연봉의 내림차순으로 출력하세요.
select employee_id as 사원번호,
first_name || ' ' || last_name as 사원명,
nvl(salary+(salary*commission_pct), salary)*12 as "연봉",
department_id as"부서번호"
from employees
where nvl(department_id,-9999) not in(30,60,90)
order by 4, 3 desc; --4번째(부서번호) 세로줄을 정렬하고 4번째줄(부서번호)을 기준으로 3번째(연봉)줄을 desc으로 정렬 (부서별로 연봉정렬) * 4뒤에는 asc이 생략되어도 오름차순정렬이됨.
-------**** and, or, not 연산자의 우선순위가 있다.********-------
select 2+3*4
from dual;
-- employees 테이블에서 부서번호가 30, 60, 90번 부서에 근무하는 사원들에 대해서
-- 사원들중에 연봉이 30000 이상 50000 이하인 사원들만
-- 사원번호, 사원명, 연봉(월급 *12), 부서번호를 출력하세요.
SELECT employee_id as 사원번호,
first_name || ' ' || last_name as 사원명,
nvl(salary+(salary*commission_pct), salary)*12 as "연봉",
department_id as 부서번호
from employees
where department_id = 30 or
department_id = 60 or
department_id = 90 and -- 부서번호가 30,60,90 인 사람들중에
nvl(salary+(salary*commission_pct), salary)*12 >=30000 and -- 연봉이 30000이상 50000이하인 사람들을 출력하는것. 하지만 우선순위가 and>or 이기때문에 and먼저 처리된다.
nvl(salary+(salary*commission_pct), salary)*12 <=50000;
-- 출력해보면 108000도 나온다.. 우선순위의 문제다.
-- and , or , not 연산자가 혼용되어지면 우선순위를 따르는데,
-- not > and > or 의 순위를 따라간다.
-- 우선순위에 있어서 최우선은 "() 괄호" 이다.
select employee_id as 사원번호,
first_name || ' ' || last_name as 사원명,
nvl(salary+(salary*commission_pct), salary)*12 as "연봉",
department_id as 부서번호
from employees
where (department_id = 30 or
department_id = 60 or
department_id = 90) and
nvl(salary+(salary*commission_pct), salary)*12 >=30000 and
nvl(salary+(salary*commission_pct), salary)*12 <=50000;
--괄호를 통해서 우선순위를 정해준다.
SELECT employee_id as 사원번호,
first_name || ' ' || last_name as 사원명,
nvl(salary+(salary*commission_pct), salary)*12 as "연봉",
department_id as 부서번호
from employees
where department_id in(30,60,90)and
nvl(salary+(salary*commission_pct), salary)*12 >=30000 and
nvl(salary+(salary*commission_pct), salary)*12 <=50000;
--in 은 괄호가 이미 있는 or 이다.
--범위를 나타내어주는 연산자가 있다.
--그것은 A 부터 B까지 나타내어주는 것은 => between A and B
SELECT employee_id as 사원번호,
first_name || ' ' || last_name as 사원명,
nvl(salary+(salary*commission_pct), salary)*12 as "연봉",
department_id as 부서번호
from employees
where department_id in(30,60,90)and
nvl(salary+(salary*commission_pct), salary)*12 between 30000 and 50000; -- between A이상 and B이하 (속도는 더 늦다.)
-------****** like 연산자 ******--------- (~~처럼 , ~~같이 라는뜻)
select *
from employees
where department_id = 80;
select *
from employees
where department_id like 80;
-- 위 두개는 똑같이 출력함. like 연산자는 = 의 의미이다. 별도의 특별한 의미가 있다.
update employees set first_name='J' -- 변경할 것.
where employee_id=100; --변경할 놈
--데이터의 변경!
select *
from employees; --변경된거 확인 (메모리상(RAM)에서만)
rollback; -- 변경한 데이터를 취소하겠다.
select *
from employees; -- 롤백한거 확인.
update employees set first_name='J' -- 변경할 것.
where employee_id=100; --변경할 놈
commit; -- 메모리상에서 변경되어진 것이 실제 디스크에 저장됨.
rollback; -- commit 한 이후에 롤백을 해도 복구가 안된다.
select *
from employees; -- commit 하면 롤백 불가.
-- employees 테이블에서 first_name 의 값이 첫글자가 'J'로 시작하는 사원들만
-- 모든 정보를 나타내세요.
-- like의 특별한 기능
select *
from employees
where first_name like 'J%'; -- first_name의 칼럼이 시작되는 것이 J 인것을 출력
-- like 연산자와 함께 사용되어지는 %는 wild character 라고 부르는데, 그 뜻은 글자가 있든지 없든지 상관없다 라는 뜻.
select *
from employees
where first_name like '%s'; -- first_name의 칼럼이 끝나는 것이 s 인것을 출력
select *
from employees
where first_name like '%ee%'; -- first_name의 칼럼이 붙어있는 ee를 출력
select *
from employees
where first_name like '%e%e%'; -- 붙어있는 ee나 e와 e사이에 떨어져있는 것을 출력 (e가 2개 이상만 나온다.1개는 안나옴. 조건자체가 %e%e% 기 때문에 e 가2개만)
--like 연산자와 함께 사용하는 _ (언더바)는 아무글자 1개를 뜻한다.
--like 연산자와 함께 사용하는 % 는 글자가 있든 없든 관계없다는 말. -- % 에는 어떤것이든 와도 된다는 것인뜻.
select first_name, last_name, department_id
from employees
where last_name like 'F_e%'; -- 첫번째 글짜는 F 그 뒤는 어떤글자든 _ 1자가 나와야하고 3번째 글짜는 e가 나온후 그 뒤는 아무거나 나와도됨.
--[문제] employees 테이블에서 성별이 여자만
-- 사원번호, 사원명, 주민번호를 나타내세요
select department_id 사원번호,
first_name || ' ' || last_name as 사원명,
jubun as 주민번호
from employees
where jubun like '______2%' or --and 는 둘다 충족해야되기 때문에 or만 가능.
jubun like '______4%';
create table tbl_watch -- watch 테이블 생성
(
watchname varchar2(30)
,bigo varchar2(200)
);
insert into tbl_watch values('금시계','순금 99.99% 함유 고급시계'); -- 행 삽입
insert into tbl_watch values('은시계','고객만족도 99.99점 획득 고급시계'); -- 행 삽입
select *
from tbl_watch;
---- DML(Data Manuplation Language) 문 -------
--> 데이터조작어 : insert, upbate, delete, merge
--> DML문은 기본적으로 수동 commit 이다.
-- 그러므로 명령(insert, upbate, delete, merge)을 내린다음에
-- commit 이나 rollback 을 해주어야 한다.
-- commit(디스크에저장) rollback(취소)
rollback; -- 취소됨.
select * --메모리에 올라간것만 보는것. 디스크에 저장하려면 commit
from tbl_watch;
insert into tbl_watch values('금시계','순금 99.99% 함유 고급시계');
insert into tbl_watch values('은시계','고객만족도 99.99점 획득 고급시계');
commit; -- commit 되면 디스크에서 읽어옴. rollback을 할수없다.
--근데 금시계를 없애고싶다. 그때엔 delete를 이용하자
delete from tbl_watch; -- where절을 안쓰면 모든행이 삭제됨..
--그런데 다시 취소할수 있다. rollback 으로! (commit을 안했으니까)
rollback;
delete from tbl_watch
where watchname='금시계'; -- where를 이용해 금시계부분만 삭제한다.
select *
from tbl_watch;
update tbl_watch set bigo = '몰라';
rollback;
update tbl_watch set bigo='몰라'
where watchname ='금시계'; -- 금시계 부분만 bigo를 몰라로 바꿔라.
rollback;
select *
from tbl_watch;
--*** rollback 과 commit 을 이해하고, insert와 delete를 이해하자.
--tbl_watch 테이블에서 bigo 컬럼에 99.99% 라는 글자가 들어있는 행만 추출 하세요.
-- (99.99% 에 %는 와일드캐릭터가 아니라 순수한글자 %인것을 추출하는것이 포인트.)
select *
from tbl_watch
where bigo like '%99.99/%%' escape '/'; -- 와일드캐릭터에서 빠져나간다 (escape) 뒤에 '/' 글자를 설정하고 /뒤에 한자만 와일드캐릭터를 빠져나간다는뜻 (설정하는글자는 아무거나도 가능함)
select *
from tbl_watch
where bigo like '%99.99a%%' escape 'a'; --설정은 아무거나 가능.
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
>>>>>>>> 단일행 함수 <<<<<<<<
※단일행 함수의 종류
1. 문자함수
2. 숫자함수
3. 날짜함수
4. 변환함수
5. 기타함수
-------------------------------------------------------------------------------
1. 문자함수
1.1 lower
1.2 upper
1.3 initcap
1.4 substr : 문자열중 특정문자 또는 문자열의 일부분을 선택할때 사용함
select 'KH정보교육원',
substr('KH정보교육원',3,2), -- 3은 3부터 시작점 / 2는 나타내는 글자수 2글자 = 정보
substr('KH정보교육원',3) -- 3은 3부터 시작점이고 글자수를 안잡아주면 끝까지 나옴.
from dual;
--[문제] employees 테이블에서 성별이 남자만
-- 사원번호, 사원명, 주민번호를 나타내세요
select department_id 사원번호,
first_name || ' ' || last_name as 사원명,
jubun as 주민번호
from employees
where substr(jubun,7,1)=1 or -- 7부터 시작해서 1글자가 1이거나
substr(jubun,7,1)=3; -- 3인 조건의 사람만.
select department_id 사원번호,
first_name || ' ' || last_name as 사원명,
jubun as 주민번호
from employees
where substr(jubun,7,1) in('1','3'); -- 위랑 같은말.
1.5 length : 문자열의 길이를 알려주는 것.
select length('kh정보교육원'),length('korea')
from dual;
1.6 lengthb : 문자열의 바이트수를 알려주는것
select lengthb('kh정보교육원'),lengthb('korea') -- lengthb = 바이트알아보기. 한글은 3바이트로...
from dual;
1.7 instr : 어떤 문자열에서 명명된 문자의 위치를 알려주는 것 (위치값 어디에있냐)
select instr('kh정보교육원 국가정보원 정보문화사', '정보', 1, 1) -- 2번째 정보는 '정보'가 나오는곳찾는것 3번째위치의 1은 맨앞부터의 출발점. 4번째 1번의 위치는 정보라는 글자가 첫번째 나오는곳부터 출발.
,instr('kh정보교육원 국가정보원 정보문화사', '정보', 1, 2) -- 4번째의 1은 2번째 정보가 나오는 곳부터 출발.
,instr('kh정보교육원 국가정보원 정보문화사', '정보', 4, 1)
,instr('kh정보교육원 국가정보원 정보문화사', '정보', 4, 3)
,instr('kh정보교육원 국가정보원 정보문화사', '정보', 1) --안쓰면 무조건 뒤는 ,1 임.
,instr('kh정보교육원 국가정보원 정보문화사', '정보', 4)
,instr('kh정보교육원 국가정보원 정보문화사', '정보', -1, 1) -- 3번째 숫자가 -1 (음수) 이면 역방향으로 읽어드린다.
,instr('kh정보교육원 국가정보원 정보문화사', '정보', -1, 2)
,instr('kh정보교육원 국가정보원 정보문화사', '정보', -4, 1)
,instr('kh정보교육원 국가정보원 정보문화사', '정보', -4, 3)
,instr('kh정보교육원 국가정보원 정보문화사', '정보', -1)
,instr('kh정보교육원 국가정보원 정보문화사', '정보', -4)
from dual;
1.8 reverse : 어떤 문자열을 거꾸로 보여주는 것이다.
select 'ORACLE', reverse('ORACLE'),
'대한민국', reverse('대한민국') -- 액박뜸
,reverse(reverse('대한민국')) --꺼꾸로를 다시 꺼꾸로하면 나옴
from dual;
create table tbl_files
(fileno number(3)
,filepath varchar2(100)
);
insert into tbl_files values(1,'c:\documents\resume.hwp');
insert into tbl_files values(2,'d:\music.mp3');
insert into tbl_files values(3,'c:\myphoto\abc\def\myface.jpg');
select *
from tbl_files;
commit;
----------------------------------------------------------------------------------------------------------------------------------------
-- [문제] 아래와 같이 나오도록 하세요.
----------------------------------
파일번호 파일명
----------------------------------
1 resume.hwp
2 music.mp3
3 myface.jpg
[힌트] substr,instr,reverse
select fileno, reverse(filepath),
substr(reverse(filepath),1,?) -- ? 자리는 글자수를 뽑야낼 개수의 자리임.. 그걸 뽑아야한다.
from tbl_files;
select fileno, reverse(filepath),
instr (reverse(filepath),'\',1) -1 -- 뽑아야할 글자수가 나왔음.
from tbl_files;
select fileno as "파일번호", reverse(filepath),
substr(reverse(filepath),1, instr (reverse(filepath),'\',1) -1) ,
reverse ( substr(reverse(filepath),1, instr (reverse(filepath),'\',1) -1) ) as "파일명" -- 다시 글자 돌리기.
from tbl_files;
--다른방법
select fileno as "파일번호",
filepath as "파일명",
instr(filepath, '\', -1)
from tbl_files;
select fileno as "파일번호",
filepath as "파일명",
instr(filepath, '\', -1),
substr(filepath,instr(filepath, '\', -1)+1) as "파일명"
from tbl_files;
select fileno as "파일번호",
substr(filepath,instr(filepath, '\', -1)+1) as "파일명"
from tbl_files;
----------------------------------------------------------------------------------------------------------------------------------------
1.9 lpad : 왼쪽부터 문자를 채움
1.10 rpad : 오른쪽부터 문자를 채움
select lpad('정보교육원', 20, '*') -- 숫자는 20바이트를 의미함 바이트확보크기를 의미. 한글=2바이트로 인식. 남은바이트를 *(별)로 채우라는 이야기 왼쪽부터..
,rpad('정보교육원', 20, '*') -- 남은바이트를 *(별)로 채우라는 이야기 오른쪽부터..
from dual;
--[문제] 대한민국이 연속해서 5번나오게 해보시오
select lpad(' ',41,'대한민국'), --' '을 기준으로 왼쪽에 채움.
rpad('대한민국',40,'대한민국')
from dual;
select lpad('대한민국',length('대한민국')*2*5,'대한민국') -- length 길이 = 4
from dual;
1.11 ltrim : 왼쪽부터 문자를 제거하는 함수.
1.12 rtrim : 오른쪽부터 문자를 제거하는 함수.
select ltrim('aabccdadsabcd','abcd'), -- 왼쪽부터 a,b,c,d 중 해당되는것들을 모두 제거하고 해당되지않는 곳에서 멈춘다음에 출력됨.
rtrim('aabbbtddabca', 'abcd') -- 오른쪽부터 a,b,c,d 중 해당되는것들을 모두 제거하고 해당되지 않는 곳에서 멈춘다음에 출력됨.
from dual;
select 'KH'|| ' 정보 교육원',
'KH'|| ltrim(' 정보 교육원') , --제거해야할 글자를 안써주면 공백을 제거해줌.
rtrim(' KH ')|| '정보 교육원' --제거해야할 글자를 안써주면 공백을 제거해줌.
from dual;
1.13 translate
select translate('010-3456-0987' -- 요런식으로 바꿀껀데 // 영일영-삼사오육-영구팔칠
,'0123456789' -- 0은 영 1은 1로 바꿀거라는것.
,'영일이삼사오육칠팔구')
from dual;
1.14 replace
select replace('KH정보교육원 교육진흥원 서울교육대학교' ,'교육', 'education') -- 교육이라는 글자를 찾아서 eduation 으로 바꿔라.
from dual;
-----------------------------------------------------------------------------------
select *
from tbl_member_test;
insert into tbl_member_test values('kimys','abcd','김유신','서울');
insert into tbl_member_test values('leess','abcd','이순신','서울');
insert into tbl_member_test values('young2','abcd','이영이','서울');
insert into tbl_member_test values('leesa','abcd','이에리사','서울');
insert into tbl_member_test values('park','abcd','박이남','서울');
insert into tbl_member_test values('leebon','abcd','이본','서울');
commit;
--[문제] : tbl_member_test 테이블에서 성이 '이'씨인 사람만
-- 성을 '리'씨로 변경(update)하세요.
-- 바꾼다고 replace만 쓰는것이 아님!!!!!! ******************************
update tbl_member_test set name = '리' || substr(name,2) -- '리' ||(붙이기) substr(name에서,2번째부터끝까지) ***
where name like '이%'; -- name 중 이씨를 범위로잡음
commit;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
2. 숫자함수
mod(5,2) -- mod 5를 2로 나누었을때의 나머지.
trunc(5/2) -- trunc 는 5를 2로 나누었을때의 몫 소수점은 다 짜른다.
select 5/2,
mod(5,2) -- mod 5를 2로 나누었을때의 나머지.
,trunc(5/2) -- trunc 는 5를 2로 나누었을때의 몫 소수점은 다 짜른다.
from dual;
--연봉을 별로 표현해서 보기쉽게 하겠다. * = 10000
select employee_id as 사원번호,
first_name || ' ' || last_name as 사원명,
nvl(salary+(salary*commission_pct), salary)*12 as "연봉",
department_id as 부서번호,
lpad('*',trunc(nvl(salary+(salary*commission_pct), salary)*12 /10000) ,'*') as "*1개는 1만임"
--trunc(nvl(salary+(salary*commission_pct), salary)*12 /10000) -- 소수분을 필요없다 몫만 취하자.
from employees;
숫자함수 2016라인부터 다시 있음
2.3 부터
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
5. 기타함수
5.1 case when then else end
--case 다음에 조건식이 온다.
select case 5-2 -- 이값이
when 1 then '5-2=1 입니다.' -- 위 case값이 (when)1이 참이라면. then 을 출력하라. 그것도아니라면 다시 when
when 3 then '5-2=3 입니다.' -- 위 case값이 (when)1이 참이라면. then 을 출력하라. 그것도아니라면 else
else '나는 산수를 몰라요 ㅠㅠ'
end
from dual;
select case
when 5-2 > 3 then '5-2 >3 입니다.' --when 값이 참이면 then뒤를 출력
when 5-2 > 2 then '5-2 >2 입니다.' -- 마찬가지.
else '나는 산수를 몰라요 ' -- 위 when들이 모두 거짓이면 else를 출력
end
from dual;
5.2 decode
-- case와 비슷함
select decode(5-2, 2, '5-2=2 입니다.'--1번째가 2번째와 같으면 3번째거를 출력 아니면 밑으로
, 3, '5-2=2 입니다.'--마찬가지로 아니면 밑에거 출력
, '나는 산수를몰라요') --모두아닐때 출력
5.3 rank, dense_rank
----------------------------------------------------------------------------------
--[문제]--
-- employees 테이블에서 부서번호 50번과 80번에 근무하는 사원들에 대해서
-- 사원번호, 사원명, 부서번호, 성별을 나타내세요
select employee_id 사원번호,
first_name|| ' ' ||last_name 사원명,
department_id 부서번호,
case substr(jubun, 7, 1)
when '1' then '남'
when '3' then '남' -- 7번째 자리주번이 1이나 3이면 남
else '여' -- 나머지는 모두 여자
end as 성별 -- 그것을 다시 성별이라고 as로 명칭을 바꿈.
from employees
where department_id in(50,80);
--질의결과에서 칸마다 왼쪽맞춤은 숫자 오른쪽맞춤은 문자
select employee_id 사원번호,
first_name|| ' ' ||last_name 사원명,
department_id 부서번호,
decode (substr(jubun, 7, 1),1,'남'
,3,'남'
,'여') as 성별
from employees
where department_id in(50,80);
----------------------------------------------------------------------------------
-----***** 로그인 처리를 해주는 쿼리문 작성 *****----- ****암기****암기****암기****암기****암기****암기
select *
from tbl_member_test;
alter table tbl_member_test
add constraint PK_tbl_member_test_id primary key(id);
--tbl_member_test 테이블에서 id 컬럼에 primary key(기본키) 제약을 준것인데,
--이렇게 주면 id 컬럼에 null값은 들어오지 못하고, 반드시 고유한 값만 들어와야 한다.
--즉 , 중복된 ID는 허락하지 않는 것이다.
insert into tbl_member_test
values('superman','pclass','송성학','경기도 평택시');
insert into tbl_member_test
values('superman','qwer','이상호','경기도 부천시'); -- primary key 제약 줘서 동일한거 또 생성 못함.
insert into tbl_member_test
values('batman','qwer','이상호','경기도 부천시');
insert into tbl_member_test
values('pretty','1234','강지혜','서울시 충무로');
commit;
select count(*) --count는 그 테이블에 조건에 만족하는 갯수를 말해줌.
from tbl_member_test;
select count(*) -- 졸라맨은 없다. 그러니까 0이 나옴.
from tbl_member_test
where id='zolaman';
select count(*)
from tbl_member_test
where id='superman' and passwd='abcd';
-- tbl_member_test 테이블에서 아이디와 암호가 일치하는 행이
-- 존재하면 '로그인 성공' 이라고 나타내어 주고
-- 만약에 아이디는 존재하지만 암호가 틀리면 '암호가 틀립니다' 라고 나타내어 주고,
-- 존재하지 않는 아이디라면 '회원 가입부터 하세요!!' 라고 나타내어주고 싶다.
select case(select count(*)
from tbl_member_test
where id='superman' and passwd ='pclass')
when 1 then '로그인 성공' -- 1
else( case( select count(*)
from tbl_member_test
where id='superman')
when 1 then '암호가 틀립니다.' -- 0
else '회원가입부터 하세요!!' -- -1
end)
end as "로그인 처리"
from dual;
-- 1=로그인 성공, 0=암호가 틀림, -1=회원가입부터
select decode( (select count(*) from tbl_member_test where id='superman' and passwd='pclass'), 1, '로그인성공(1)' -- 2번째꺼는 카운트가 1이면 (존재하면) 1을 반환함.
,decode( (select count(*) from tbl_member_test where id= 'superman'),1,'암호가틀려[0]' -- 이 카운트는 아이디만 비교해서 아이디부분이 1이면(존재하면) 0을 반환함.
,'회원가입부터해[-1]')) as "로그인처리"-- 두개다 정보가 없을때 -1이 나옴.
from dual; --요 4줄 암기해보자.
select decode(조건,값1,결과값1 -- 조건과 값1을 비교해서 같으면 결과값1을 반환함.
,값2,결과값2 -- 조건과 값2를 비교해서 같으면 결과값2을 반환함.
,값3,결과값3 -- 조건과 값3을 비교해서 같으면 결과값2를 반환함.
,결과값4) -- 모두아닐때 결과값4를 반환.
--푸는과정
select decode( (id,passw),1,1
,decode( (id), 1,0
,-1) )
from dual;
select decode( (select count(*)
from tbl_member_test
where id='superman' and passwd='pclass'),1,1
,decode( (select count(*)
from tbl_member_test
where id='superman'), 1,0
,-1) )
as "로그인처리"
from dual;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
3. 날짜함수
- 현재 오라클 서버 시스템의 시간을 알려준 것.
3.1 sysdate
select sysdate, -- 년 월 일
current_date, --년 월 일
localtimestamp, -- 초단위까지나옴
current_timestamp -- 어디지역인지 까지 나옴.
from dual;
select sysdate, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') --년 월 일 시 분 초 로 나옴.
from dual;
-- 날짜 + 숫자 = 날짜
-- 날짜 - 숫자 = 날짜
-- 여기서 숫자의 단위는 일(日)수 이다.
-- 날짜 - 날짜 = 숫자
-- 숫자의 단위는 일수
select sysdate - 1 , sysdate , sysdate + 1 -- 일수가 늘어남.
from dual;
select to_char(sysdate - 1 , 'yyyy-mm-dd hh24:mi:ss') as "어제"
,to_char(sysdate , 'yyyy-mm-dd hh24:mi:ss') as "현재시각"
,to_char(sysdate + 1 , 'yyyy-mm-dd hh24:mi:ss') as "내일"
from dual;
--[문제] 현재시각으로 부터 1일 2시간 3분 4초 뒤를 나타내세요.
select to_char(sysdate , 'yyyy-mm-dd hh24:mi:ss') as 현재
,to_char(sysdate +1.08546296296296296296296296296296296296 , 'yyyy-mm-dd hh24:mi:ss') as "1일 2시간 3분 4초 뒤" --초로바꿔서 다시계산함...
from dual;
select to_char(sysdate , 'yyyy-mm-dd hh24:mi:ss') as 현재
,to_char(sysdate+1+2/24+3/(24*60)+4/(24*60*60) , 'yyyy-mm-dd hh24:mi:ss') as "1일 2시간 3분 4초 뒤 2" -- 일수로 계산해서 간편하게함...
from dual;
3.2 add_months
--개월수를 더하거나 빼는것
select add_months(sysdate, -2) --현재 날짜에서 2달을 뺀 달
, sysdate
, add_months(sysdate, 2) -- 현재 날짜에서 2달을 더한 달
from dual;
-- [문제] 오늘 부로 송성학씨가 또 군대에 끌려간다.
-- 군복무 기간이 21개월을 한다라고 가정하면
-- 첫번째, 제대일자를 구하시고,
-- 두번째, 제대일자까지 먹어야할 짬밥의 그릇수를 구하시오.
-- (단, 1일 3끼를 먹는다.)
select add_months(sysdate, 21) as "제대일자"
,add_months(sysdate, 21) - sysdate as "근무일수"
,(add_months(sysdate, 21) - sysdate) *3 as "그릇 수"
from dual;
--------------------------------------------------------------------
--[문제] 현재시각으로 부터 2016년 10월 12일 21:00시까지 남은 기간을
-- 몇일 몇시간 몇분 몇초 로 나타내세요.
-- 일단 모든시간을 초로 바꿔서 다시 일수, 시간, 분, 초 로 바꿔야한다.
--하루*하루의시간*한시간의분*1분의초
select 1*24*60*60 + 2*60*60 + 3*60 + 4
from dual; -- 93784 초
-- 1일 2시간 3분 4초를 초로 나타내어 본다.
183초
select 185/60, trunc(185/60)||'분', mod(185,60)||'초'
from dual;
3.3 trunc , mod -- trunc = 나누었을때 몫 trunc(/), mod 나누었을때 나머지 mod(,)
select trunc(trunc(trunc(93784/60)/60)/24)||'일'||mod(trunc(trunc(93784/60)/60),24)||'시간',
trunc(trunc(93784/60)/60)||'시간'||mod(trunc(93784/60),60),
trunc(93784/60)||'분'|| mod(93784,60)||'초'
from dual;
select 1*(24*60*60), --하루에 대한 초
2*(24*60*60) --이틀에 대한 초
from dual;
select '2016-10-12 21:00:00' - sysdate
from dual; --안됨 문자타입이라. 변환함수를 쓰자.
select to_date('2016-10-12 21:00:00','yyyy-mm--dd hh24:mi:ss') - sysdate --'yyyy-mm--dd hh24:mi:ss' 한타입으로 된 문자를 날자로 바꾸라는 변환함수 to_date
from dual;
select trunc((to_date('2016-10-12 21:00:00','yyyy-mm--dd hh24:mi:ss') - sysdate)*(24*60*60))
from dual;
select trunc(trunc(trunc((1*24*60*60 + 2*60*60 + 3*60 + 4)/60)/60)/24)||'일'||mod(trunc(trunc(93784/60)/60),24)||'시간'
||mod(trunc(93784/60),60)||'분'|| mod(93784,60)||'초'
from dual;
select trunc(trunc(trunc(trunc( (to_date('2016-10-12 21:00:00','yyyy-mm-dd hh24:mi:ss') - sysdate) * (24*60*60) )/60)/60)/24)||'일 '||
mod(trunc(trunc(trunc( (to_date('2016-10-12 21:00:00','yyyy-mm-dd hh24:mi:ss') - sysdate) * (24*60*60) )/60)/60),24)||'시간 '||
mod(trunc(trunc( (to_date('2016-10-12 21:00:00','yyyy-mm-dd hh24:mi:ss') - sysdate) * (24*60*60) )/60),60)||'분 '||
mod(trunc( (to_date('2016-10-12 21:00:00','yyyy-mm-dd hh24:mi:ss') - sysdate) * (24*60*60) ), 60) ||'초'
from dual;
create table tbl_auction
(auction_no number
,expire_date date
);
insert into tbl_auction values
(1, to_date('2016-05-12 21:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into tbl_auction values
(2, to_date('2016-05-13 11:30:00','yyyy-mm-dd hh24:mi:ss'));
insert into tbl_auction values
(3, to_date('2016-05-14 20:00:00','yyyy-mm-dd hh24:mi:ss'));
select auction_no, to_char(expire_date,'yyyy-mm-dd hh24 mi:ss') as expire_date
from tbl_auction;
commit;
--[문제] 해당 테이블을 조회했을때
-------------------------------------
경매번호 종료시각 남은기간
-------------------------------------
1 2016-05-12 21 00:00 일 시간 분 초
2 2016-05-13 11 30:00
3 2016-05-14 20 00:00
select auction_no as "경매번호",
to_char(expire_date, 'yyyy-mm-dd hh24:mi:ss') as "종료시각",
trunc( trunc( trunc((expire_date - sysdate)*(24*60*60)/60)/60 )/24 )||'일 ' ||
mod( trunc( trunc((expire_date - sysdate)*(24*60*60)/60)/60 ), 24)||'시간 ' ||
mod( trunc((expire_date - sysdate)*(24*60*60)/60), 60)||'분 ' ||
round( mod( (expire_date - sysdate)*(24*60*60), 60) )||'초'
as "남은기간"
from tbl_auction;
--(expire_date - sysdate)*(24*60*60)
--------------------------------------------------------------------- 0일 미만만 보고싶을때 -- 조건 where 절에 간단하게 하기위해 view를 만듬.
---------------- 저장된뷰라고 부름 stored view -----------------------********
create or replace view view_auction -- 컬럼을 view_auction 테이블처럼 보겠다는것
as
select auction_no,
to_char(expire_date, 'yyyy-mm-dd hh24:mi:ss') as expire_date,
trunc( trunc( trunc((expire_date - sysdate)*(24*60*60)/60)/60 )/24 )||'일 ' ||
mod( trunc( trunc((expire_date - sysdate)*(24*60*60)/60)/60 ), 24)||'시간 ' ||
mod( trunc((expire_date - sysdate)*(24*60*60)/60), 60)||'분 ' ||
round( mod( (expire_date - sysdate)*(24*60*60), 60) )||'초'
as nameungigan
from tbl_auction;
select *
from view_auction; --마치 테이블처럼 나온다.
--하루가 안되어진것만 보자.
select nameungigan
from view_auction
where to_number(substr(nameungigan,1,instr(nameungigan,'일',1)-1)) < 1 ; -- to number는 문자정보를 숫자로 바꿔준다
-- 칼럼들을 테이블로 바라보게 view를 만들고 , 조건절을 넣어서 처리함.
select * from tab; --모든테이블보기
desc view_auction; -- 테이블의 칼럼과 데이터타입유형보기
-- view_auction의 원본 소스를 조회해 보자.
select *
from user_views
where view_name='VIEW_AUCTION'; -- view_auction의 원본 소스를 조회
select *
from user_views
where view_name='EMP_DETAILS_VIEW'; --EMP_DETAILS_VIEW 의 원본소스 조회
3.4 extract -- 날짜에서 년,월,일을 추출해주는 함수이다.
select sysdate,
extract(year from sysdate) as "년1" -- 현재 날짜에서 년만 빼오겠다.
,to_char(sysdate, 'yyyy') as "년2"
,extract(month from sysdate) as "월1"-- 현재 날짜에서 월만 빼오겠다.
,to_char(sysdate, 'mm') as "월2"
,extract(day from sysdate) as "일1"--현재 날짜에서 날만 빼오겠다
,to_char(sysdate, 'dd') as "일2"
from dual;
-- 결과는 같은데 to_char 는 문자타입
-- extract 는 숫자타입
-- 연산할때는 숫자타입인 extract 를 쓰자.
-- employees 테이블에서
-- 사원번호,사원명,주민번호,성별, 생년,나이 구하기.
select employee_id as "사원번호"
,first_name|| ' ' || last_name as"사원명"
,jubun as "주민번호"
,case when substr(jubun,7,1) in('1','3') then '남' else'여' end as "성별"
,case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end + to_number(substr(jubun,1,2)) as "생년" -- 주번컬럼은 문자열로 되있어서 to_number로 숫자로 바꾼다음에 구함.
,extract(year from sysdate)-(case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end + to_number(substr(jubun,1,2)))+1 as "현재나이" -- extract 로 현재 년도를 빼와서 나이를뺀다.
from employees;
-- employees 테이블에서 여자중 나이가 20대와 40대만
-- 사원번호, 사원명, 주민번호, 성별, 나이 나타내세요.
-------********************************************** 잘 기 억 합 시 다 ! *********************
------------------ inline view를 사용한 쿼리문 작성.
--전체 괄호하기-> 이름짓기 -> 자바처럼 이름. 으로 접근하기.-------********************************************** 잘 기 억 합 시 다 ! *********************
select V.*
from
(
select employee_id
,first_name|| ' ' || last_name as ename
,jubun
,case when substr(jubun,7,1) in('1','3') then '남' else'여' end as gender
,extract(year from sysdate)-(case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end + to_number(substr(jubun,1,2)))+1 as age
from employees
) V -- inline view 테이블로 간주하게끔 괄호 하고 이름을 정함. 괄호전체가 V 임.
where V.gender = '여' and trunc( V.age / 10 ) in (2,4) -- age를 10으로 나눈값의 몫만 취해서 2또는 4 찾기.
order by V.age ;-- 오름차순으로
3.5 last_day(날짜) --> 해달날짜가 포함된 달에 마지막 날짜(일)를 알려주는 것.
select last_day(sysdate) -- 이번달의 마지막날짜.
from dual;
select last_day('2016-02-01') -- 지정한 날짜의 마지막날짜.
from dual;
select last_day(to_date('2016-02-01','yyyy-mm-dd'))
,last_day(to_date('2015-02-01','yyyy-mm-dd'))
from dual;
select sysdate, to_char(sysdate,'day'), to_char(sysdate, 'dy') -- 요일을 나타나는것! 보여줄때만쓰자.
from dual;
select sysdate, to_char(sysdate,'d')
-- to_char(sysdate,'d') 값이
-- 1:일 , 2:월 , 3:화 , 4:수 , 5:목 , 6:금 , 7:토
from dual;
select last_day(to_date('2016-07-25','yyyy-mm-dd')) -- 7월이 포함된 달력에서..(마지막날)
,to_char(last_day(to_date('2016-07-25','yyyy-mm-dd')),'d') -- 그날(마지막날) 의 요일구하기.
from dual;
select case
to_char(last_day(to_date('2016-07-25','yyyy-mm-dd')),'d')
when '1' then last_day(to_date('2016-07-25','yyyy-mm-dd')) + 1
when '7' then last_day(to_date('2016-07-25','yyyy-mm-dd')) + 2
else last_day(to_date('2016-07-25','yyyy-mm-dd'))
end as "이자내는날"
from dual;
select case
to_char(last_day(to_date('2016-12-25','yyyy-mm-dd')),'d')
when '1' then last_day(to_date('2016-12-25','yyyy-mm-dd')) + 1
when '7' then last_day(to_date('2016-12-25','yyyy-mm-dd')) + 2
else last_day(to_date('2016-12-25','yyyy-mm-dd'))
end as "이자내는날"
from dual;
--[문제] employees 테이블에서
--
-- 정년퇴직일, 정년퇴직행사일 을 나타내세요.
-- 정년 퇴직일이라함은 [교육공무원법상]
-- 해당 사원의 생일이 3월에서 8월에 태어난 사람은 해당사원의 나이(한국나이 {+1} )가 63세가 되는 년도에
-- 8월 31일으로 하고,
-- 해당 사원의 생일이 9월에서 2월에 태어난 사람은 해당사원의 나이(한국나이 {+1} )가 63세가 되는 년도에
-- 2월말일 (2월 28일 또는 2월 29일)으로 한다.
-- 정년 퇴직 행사일은 [내가 맘대로지은 법]
-- 정년 퇴직일에 해당하는 요일이 토,일 이라면 그전 금요일로 한다.
-- 정년 퇴직일에 해당하는 요일이 월~금 이라면 그냥 그대로 한다.
select V.*,
to_char(last_day(to_date(to_char(add_months(sysdate, (63-V.age)*12),'yyyy-') ||
case when to_number(substr(V.jubun,3,2)) between 3 and 8 then '08-01'
else '02-01' end ,'yyyy-mm-dd')) , 'yyyy-mm-dd')
as expire_date
from
(
select employee_id as"사원번호"
,jubun
,first_name|| ' ' ||last_name as ename
,extract(year from sysdate) - (case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end +to_number( substr(jubun,1,2))) +1 as age
from employees
) V;
3.6 months_between -- 두 날짜의 개월차이를 알려주는 것. 앞에꺼에서 뒤에껄 빼는거임!!
select months_between( add_months(sysdate, 5), sysdate)
from dual;
select V.employee_id as "사원번호"
,V.ename as "사원명"
,V.jubun as "주민번호"
,V.age as "현재나이"
,V.monthsal as "월급"
,V.hire_date as "입사일자"
,to_char(last_day(to_date(to_char(add_months(sysdate, (63-V.age)*12),'yyyy-') ||
case when to_number(substr(V.jubun,3,2)) between 3 and 8 then '08-01'
else '02-01' end ,'yyyy-mm-dd')) , 'yyyy-mm-dd') as "정년퇴직일자"
-- months_between(to_date( ?정년퇴직일자? ,'yyyy-mm-dd' ),V.hire_date) /12 --나누기 12는 퇴직금 이 년별로 구해야하는거기때문에 소수부가 나옴. 그걸짜르기위해서
-- trunc( months_between(to_date( ?정년퇴직일자? ,'yyyy-mm-dd' ),V.hire_date) /12 )
-- trunc( months_between(to_date( ?정년퇴직일자? ,'yyyy-mm-dd' ),V.hire_date) /12 ) * V.monthsal -- 월급을 뒤에 곱하면 예상 퇴직금!
,trunc(months_between(to_date((to_char(last_day(to_date(to_char(add_months(sysdate, (63-V.age)*12),'yyyy-') ||
case when to_number(substr(V.jubun,3,2)) between 3 and 8 then '08-01'
else '02-01' end ,'yyyy-mm-dd')) , 'yyyy-mm-dd')) ,'yyyy-mm-dd' ),V.hire_date) /12 ) * V.monthsal as "예상 퇴직금"
from
(
select employee_id
,jubun
,first_name|| ' ' ||last_name as ename
,nvl(salary + (salary * commission_pct), salary) as monthsal
,hire_date
,extract(year from sysdate) - (case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end +to_number( substr(jubun,1,2))) +1 as age
from employees
) V;
3.7 next_day -- 1932라인에 있음
--[문제] employees 테이블에서 예상퇴직금이 300000 이상인 사원들만
-- 사원번호 , 사원명, 입사일자, 정년퇴직일자, 예상퇴직금을 나타내세요.
create or replace view view_retireinfo -- 테이블처럼 이미만들어진inline view를 stored view처럼 뷰를 만드는 소스 ******~~~
as
select V.employee_id
,V.ename
,V.jubun
,V.age
,V.monthsal
,V.hire_date
,to_char(last_day(to_date(to_char(add_months(sysdate, (63-V.age)*12),'yyyy-') ||
case when to_number(substr(V.jubun,3,2)) between 3 and 8 then '08-01'
else '02-01' end ,'yyyy-mm-dd')) , 'yyyy-mm-dd') as retireday
-- months_between(to_date( ?정년퇴직일자? ,'yyyy-mm-dd' ),V.hire_date) /12 --나누기 12는 퇴직금 이 년별로 구해야하는거기때문에 소수부가 나옴. 그걸짜르기위해서
-- trunc( months_between(to_date( ?정년퇴직일자? ,'yyyy-mm-dd' ),V.hire_date) /12 )
-- trunc( months_between(to_date( ?정년퇴직일자? ,'yyyy-mm-dd' ),V.hire_date) /12 ) * V.monthsal -- 월급을 뒤에 곱하면 예상 퇴직금!
,trunc(months_between(to_date((to_char(last_day(to_date(to_char(add_months(sysdate, (63-V.age)*12),'yyyy-') ||
case when to_number(substr(V.jubun,3,2)) between 3 and 8 then '08-01'
else '02-01' end ,'yyyy-mm-dd')) , 'yyyy-mm-dd')) ,'yyyy-mm-dd' ),V.hire_date) /12 ) * V.monthsal as retiremoney
from
(
select employee_id
,jubun
,first_name|| ' ' ||last_name as ename
,nvl(salary + (salary * commission_pct), salary) as monthsal
,hire_date
,extract(year from sysdate) - (case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end +to_number( substr(jubun,1,2))) +1 as age
from employees
) V;
select *
from view_retireinfo
where retiremoney >=300000; -- 간단하게 추가한 뷰를 가지고 조건을 정해 간단한 소스로 원하는부분(퇴직금이 30만이상인 사람) 만 뽑아냄.
--------------------------------------------------------------------------------------------------------------------------------------------------------
---------기타함수 5.3부터 추가되는 내용~
-- ****** 등수 : rank, dense_rank *******************
-- 예매율 가장좋은것, 물건이 가장 잘팔린것 에 응용할수있다.
5.3 rank, dense_rank
select department_id as "부서번호"
,employee_id as "사원번호"
,first_name||' '||last_name as"사원명"
,salary as "기본급여"
from employees
-- 위에서 기본급여에 대한 등수를 구해보자~
select department_id as "부서번호"
,employee_id as "사원번호"
,first_name||' '||last_name as"사원명"
,salary as "기본급여"
,rank()over(order by salary desc) as "전체등수"--order by로 내림차순정렬 => 가장많은급여부터 내림차순으로 나옴
,rank()over(partition by department_id --부서 번호별로 칸막이를 쳤을때 등수를 구해라 => partition by 칸막이칠 기준
order by salary desc) as "부서내등수"
from employees
order by 1, 4 desc; -- 1번째컬럼은 오름차순(생략) , 4번째는 내림차순 ㄱㄱ
-- over() 는 범위. 어디에대한 등수가 나옴 1등,2등,3...... 이런식으로 ..
-- partition by 은 어디기준에서 칸막이를 치는것
--ex)
-- 시험성적 등수 내무반 서열
-- 강지혜 98 2 넘버투 2
-- 송성학 100 1 왕고참 1
-- 공현정 97 3 넘버쓰리 3
-- 김수현 51 5 넘버포 4
-- 오진석 97 3 넘버쓰리 3
-- 서영학 10 6 넘버파이브 5
-- 최재우 8 7 넘버식스 6
--공동 3등이니 김수현은 5등이다 / 내무반 서열은 동기니까 서열은 같다.
-- 이럴때에는 dense_rank 를 쓴다. 서열을 구할때~
select department_id as "부서번호"
,employee_id as "사원번호"
,first_name||' '||last_name as"사원명"
,salary as "기본급여"
,rank()over(order by salary desc) as "전체등수"
,rank()over(partition by department_id
order by salary desc) as "부서내등수"
,dense_rank()over(order by salary desc) as "전체서열" -- 몇등이 아니라 몇번째로 많이버냐를 구하는것 !
--,dense_rank()over(partition by department_id
-- order by salary desc) as "부서내서열"
from employees;
--order by 1, 4 desc;
----
--[문제] 퇴직금에 대한 등수를 구해라
select *, dense_rank()over(order by reitremoney desc) as"퇴직금서열" -- 요렇게하면 모든것이 view_retireinfo 나오면서 퇴직금서열이 추가될것같지만 안됨.
from view_retireinfo;
select employee_id, ename, jubun, age, monthsal, hire_date, retireday, retiremoney, --일일히 써줘야함.
dense_rank()over(order by retiremoney desc) as "퇴직금서열"
from view_retireinfo;
--[문제] 위 문제에 연령대별 서열을 추가하시오. + ename, age, monthsal, retiremoney
select ename
, age
, trunc(age,-1)
--,trunc(age/10)*10 --소수부 필요없으니 짜르고 몫에 10곱함
, monthsal
, retiremoney
,dense_rank()over(partition by trunc(age,-1) order by retiremoney desc) "연령대별 퇴직금서열"
,dense_rank()over(partition by (age/10)*10 order by retiremoney desc) "연령대별 퇴직금서열2"
--dense_rank()over(order by retiremoney desc) as "퇴직금서열"
from view_retireinfo;
select ename
, age
, trunc(age/10)
, trunc(age/10)*10
, trunc(age/10,1) -- 일.X 까지나와라
, trunc(age/10,0) -- = trunc(age/10) 랑같은말.
, trunc(age,0) -- 현재 나이에서 소수부 짤러버려라 일 자리까지 나와
, trunc(age,-1) -- 10자리까지를 잘라버려라 = trunc(age/10)*10 얘랑 같은말
, trunc(age,-2) -- 100자리까지만 보여주고 나머지 잘러라
, monthsal
, retiremoney
--dense_rank()over(order by retiremoney desc) as "퇴직금서열"
from view_retireinfo;
--[문제] 1등부터~ 10등 까지에 해당하는 사원만 나타내어보시오
select department_id as "부서번호"
,employee_id as "사원번호"
,first_name||' '||last_name as"사원명"
,salary as "기본급여"
,dense_rank()over(order by salary desc) as "전체등수"
from employees
where dense_rank()over(order by salary desc) <= 10; -- ** # rank , dense_rank 는 where 절 바로 쓸수없다. 인라인뷰에서 가능하다. # **
--그래서 아래처럼 인라인뷰를 형성해서 ㄱㄱ
select V.*
from
(
select department_id as "부서번호"
,employee_id as "사원번호"
,first_name||' '||last_name as"사원명"
,salary as "기본급여"
,dense_rank()over(order by salary desc) as "전체등수"
from employees
)V
where V."전체등수" <= 10;
select V.*
from
(
select ename
, age
, trunc(age,-1) as ageline
--,trunc(age/10)*10 as ageline --소수부 필요없으니 짜르고 몫에 10곱함
, monthsal
, retiremoney
, dense_rank()over(order by retiremoney desc) as "퇴직금서열"
from view_retireinfo
)V
where V."퇴직금서열" <=5;
5.4 greatest , least 가장큰것 greatest, 가장작은것 least
select greatest(10,90,100,80) -- 괄호안에서 가장큰것
,least(10,90,100,80) -- 괄호안에서 가장작은것
from dual;
select greatest('김유신','윤봉길','허준','고두심') -- 가장 뒤에나오는것
,least('김유신','윤봉길','허준','고두심') -- 가장 앞에나오는것 (작으니까 앞에나오지)
from dual;
--------------------------------------------------------------------------------------------------------------------------------------------------------
---------날짜함수 3.6부터 추가되는 내용~
-- ****** 3.7 next_day , 3.8 to_ym , to_dsinterval *******************
3.7 next_day -- 돌아오는 요일명중 가장 빠른날을 알려주는 것.
select sysdate, next_day(sysdate, '토') --돌아오는 가장 빠른 토요일
from dual;
select sysdate, next_day(sysdate, '토') --돌아오는 가장 빠른 토요일
, next_day(sysdate, '월') --돌아오는 가장 빠른 월요일
, next_day(sysdate, '금') --돌아오는 가장 빠른 금요일
from dual;
3.8 to_yminterval --년, 개월 ex) ('01-02')
to_dsinterval --일, 시간,분,초 ex) ('003 04:05:06')
--현재일로부터 1년 2개월 3일 4시간 5분 6초 뒤를 나타내세요.
select add_months(sysdate, 1*12+2) +3 +4/24+ 5/(24*60)+6/(24*60*60) -- 요것이 1년 2개월 3일 4시간 5분 6초 뒤
from dual;
select to_char(add_months(sysdate, 1*12+2) +3 +4/24+ 5/(24*60)+6/(24*60*60) ,'yyyy-mm-dd hh24:mi:ss') as "1"
,to_char(sysdate + to_yminterval('01-02')+to_dsinterval('003 04:05:06'), 'yyyy-mm-dd hh24:mi:ss') as "2" --앞에 0은 생략가능하지만 쓴다. 1년은 365이기 떄문에 0은 3자리
-- ,sysdate - to_yminterval('01-02') - 빼기는 1년2개월 전
from dual;
--------------------------------------------------------------------------------------------------------------------------------------------------------
---------숫자함수 2.1부터 추가되는 내용~
-- *************************
2.2 round : 반올림을 해주는 것.
select 94.547
,round(94.547) --0 을생략한거라 밑에거와 똑같음.
,round(94.547,0)
,round(94.547,1) --소수부 첫째자리까지 보여주면서 나머지를 반올림
,round(94.547,-1) --10의자리까지만 보여주고 1의자리는 반올림한다
,round(94.547,-2) --100의짜리까지만 보여주고 10의짜리를 반올림한다
from dual;
2.3 trunc : 절삭해주는 것.
select 94.547
,trunc(94.547) --0 을생략한거라 밑에거와 똑같음.
,trunc(94.547,0) --10자리까지만 보여주고 나머지 짤라버린다 .
,trunc(94.547,1) --소수부 첫째자리까지 보여주면서 나머지를 짤라버린다.
,trunc(94.547,-1) --10자리까지만 보여주고 1자리는 짤라버린다.
,trunc(94.547,-2) --100짜리까지만 보여주고 10짜리를 짤라버린다.
from dual;
--테이블 생성
create table tbl_sungjuk
(hakbun varchar2(10)
,jumsu number(3)
);
--정보삽입
insert into tbl_sungjuk values('101',94);
insert into tbl_sungjuk values('102',89);
insert into tbl_sungjuk values('103',98);
insert into tbl_sungjuk values('104',69);
insert into tbl_sungjuk values('105',59);
insert into tbl_sungjuk values('106',100);
--최종확인
commit;
--조회
select *
from tbl_sungjuk
--[문제] ------------------- 아래와같이 나타내시오
학번 점수 학점
---------------------------
101 94 A
102 89 B
103 98 A
104 69 D
105 59 F
106 100 A
90점대 = A
80점대 = B
70점대 = C
60점대 = D
이하 = F
-- case 문
select case trunc(jumsu,-1) when 100 then 'A'
when 90 then 'A'
when 80 then 'B'
when 70 then 'C'
when 60 then 'D'
else 'F' end as 학점
from tbl_sungjuk;
--decode 문
select
decode (trunc(jumsu,-1) , 100, 'A'
, 90, 'A'
, 80, 'B'
, 70, 'C'
, 60, 'D'
, 'F' )as 학점
from tbl_sungjuk;
--
select case substr(trunc(jumsu,-1),1,3) when '100' then 'A'
when '90' then 'A'
when '80' then 'B'
when '70' then 'C'
when '60' then 'D'
else 'F' end as 학점
from tbl_sungjuk;
2.4 power 거듭제곱
select 2*2*2*2*2, power(2,5) --2를 연속해서 5번곱해라
from dual;
2.5 sqrt 제곱근 (루트)
select sqrt(4), sqrt(2) ,sqrt(3)
from dual;
2.6 sign 결과값이 양수라면 1,
0이라면 0,
음수라면 -1
select sign(5-2) --양수라 1
,sign(5-5) -- 0
,sign(2-5) --음수라 -1
from dual;
2.7 ascii 아스키코드 , chr 반대개념
select ascii('A'),ascii('a'),ascii('0'),ascii(' ') -- 암기
from dual;
select chr(65),chr(97),chr(48),chr(32)
from dual;
2.8 sin, cos, tan, asin, acos, atan 삼각함수
select sin(90),cos(90),tan(90)
,asin(0.3),acos(0.3),atan(0.3)
from dual;
2.9 log
select log(10,100)
from dual;
------------------------------------------------------------------------------------------------
4. 변환함수
4.1 to_char 숫자나 날짜를 문자타입으로 변환해주는것
--
select to_char(sysdate,'yyyy')
,to_char(sysdate,'mm')
,to_char(sysdate,'dd')
,to_char(sysdate,'hh24')
,to_char(sysdate,'hh am')
,to_char(sysdate,'hh pm')
,to_char(sysdate,'mi')
,to_char(sysdate,'ss')
from dual;
--
select to_char(sysdate,'q') as 분기 -- 쿼터 1월:1(분기), 4~6월:2, 7~9월:3, 10~12월:4
from dual;
--
select to_char(sysdate, 'day') "X요일"
,to_char(sysdate, 'dy') "X"
from dual;
--
select case to_char(sysdate,'d')
when '1' then '일'
when '2' then '월'
when '3' then '화'
when '4' then '수'
when '5' then '목'
when '6' then '금'
when '7' then '토'
end as " 오늘의 요일 "
from dual;
--
select (sysdate-to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')) *(24*60*60) --오늘밤 (0시0분0초)자정부터 현재까지의 초
, to_char(sysdate,'sssss') -- 오늘밤 (0시0분0초)자정부터 현재까지의 초 * 위에거와 같다!
from dual;
--
select to_char(3000000, '9,999,999') --자릿수만큼 9를 써주고 콤마찍을 위치에 찍어줌.
,to_char(3000000, '$9,999,999')
,to_char(3000000, 'L9,999,999') -- 앞에 대문자 L을 쓰면 각 나라의 돈의 단위가 나온다.
from dual;
--
select round(salary/12,1)
,to_char(round(salary/12,1),'9999.0') -- 자릿수가 통일되어서 정렬되어 나온다.
from employees;
4.2 to_date 문자를 날짜타입으로 변환해주는것
4.3 to_number 문자를 숫자타입으로 변환해주는것
select 2+to_number('3') --문자타입 '3'을 숫자타입으로 바꿔서 연산해줌
from dual;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------- ***** 그룹함수 (집계함수) ***** ---------
------------- *@@@* { 중요!!!!! } 그룹함수(집계함수) 에서는 null은 무조건 제외하고서 연산을 한다. @@@@@*******
1.sum -- 합계
2.avg -- 평균
3.max -- 최대값
4.min -- 최소값
5.count -- select 되어서 나온 결과물의 행의 갯수
6.variance --분산
7.stddev --표준편차
-- 1.sum -- 합계 ----------------------
select count(salary) -- null 없음
from employees;
select count(commission_pct) --null있음. null 아닌것의 갯수를 나타냄. 35개
from employees;
select commission_pct*salary
from employees;
select sum(salary) -- null 없는놈의 합
from employees;
select sum(commission_pct*salary) -- 그룹함수는 null값을 빼고 연산한다.
from employees;
--
--2.avg -- 평균 ----------------------
select avg(salary) -- 평균 분모의 107이 들어가야 평균이나옴 null이없으니 이건 가능.
from employees; --107명
select avg(salary)
,sum(salary)/count(salary)
from employees;
select avg(commission_pct*salary) -- commission_pct null이있다. 널은 무조건 빼버리기 때문에 commission_pct은 35명만이다. => 이것은 commission_pct 수당을 받는 35명의 평균치다.
from employees;
select avg(commission_pct*salary)
,sum(commission_pct*salary)/count(commission_pct*salary) --위와 같음.
from employees;
select sum(commission_pct*salary)/count(*) --모든컬럼의 가로줄이 모두 null일수가 없다. 그래서 이렇게 계산하면 모든사람의 수가 카운트된다.
from employees;
--[문제] 우리회사의 평균 commission_pct을 구해야하는데 null인사람은 0으로 간주를 하고 평균을 구해보시오. -----------------------
--
select sum(nvl(commission_pct*salary,0))/count(*) --null값까지 0으로 던져주고 모든사람들을 평균치를 냈음.
,avg(nvl(commission_pct*salary, 0)) --null값까지 0으로 던져주고 모든사람들을 평균치를 냈음.
from employees;
--------------------------------------------------------------------------------------------------------------
--[문제] employees 테이블에서 부서번호별 인원수를 나태내세요. * * * * * group by 와 having 을 알아보자
--ex)-----------------------
부서번호 인원수
-------------------------
10 2
20 3
30 5
40 3
50 12
......................
-- 이런식으로 나타내어 보자.
select department_id as 부서번호
,count(*) as 인원수 --group by에 의해서 department_id끼리 묶은놈들의 카운트를 센다. 그것이 인원수가 됨.
from employees
group by department_id --department_id가 같은놈들끼리 그룹을 지은다음에~~ having은 그룹함수를 쓸때 사용함.
order by 1;
--[문제] employees 테이블에서 남녀별 인원수를 나타내세요. (힌트: 취하고자하는 컬럼이없으면 인라인뷰를 이용)
--ex)-----------------------
성별 인원수
----------------------------
남자 X명
여자 X명
select V.gender as 성별
,count(*) as 인원수 -- 그룹으로 묶은상태의 카운트
from
(
select
case when substr(jubun,7,1) in('1','3') then '남' else'여' end as gender
from employees
)V
group by V.gender -- gender 데이터중 같은놈끼리 묶어서 그룹을 지어준다.
order by 1 ;
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 5.16 월
-- rollup을 해서 전체 인원수를 나타내주자
select department_id as 부서번호
,count(*) as 인원수
from employees
group by rollup (department_id); -- rollup은 (department_id)의 null 과 관계없다. 부서별로 묶지않고, 그냥 전체에 대한 인원수.
--grouping을 해서 그룹여부를 조회하고 변경값을 부여.
select decode(grouping(V.gender), 0, V.gender
,'전체')as "성별" -- grouping한 값이 0이라면 젠더를 나타내고 아니라면 '전체'라고 출력되게함.
,count(*) as 인원수
,grouping(V.gender) -- 그룹이 됬는지 안됬는지 보여줌. 값이 0이나오면 메모리상에서 실제로 그룹을 지었다는말. 1값이나오면 그룹을 안지었다는 말.
from
(
select
case when substr(jubun,7,1) in('1','3') then '남' else'여' end as gender
from employees
)V
group by rollup (V.gender); -- rollup을 쓰면 107이 나옴..
--남여 비율구하기
select decode(grouping(V.gender), 0, V.gender
,'전체')as "성별"
,count(*) as 인원수 -- 칼럼에해당되는인원 / 전체인원 * 100(비율) = 비율구하는것
,round(count(*)/(select count(*) from employees)*100,1) ||'%' as 비율 -- 56/107*100 이 비율구하는건데 107은 select count(*) from employees 로 전체인원수를 카운트세면 됨.
from
(
select
case when substr(jubun,7,1) in('1','3') then '남' else'여' end as gender
from employees
)V
group by rollup (V.gender);
--부서번호에 전체를 넣기
select decode(grouping(department_id), 0 , to_char(department_id) -- to_char로 해서 '전체'를 나오도록한다. to_char를 (그룹여부를묻는)grouping에 묶으면 숫자0이랑 비교를 할수없다. 뒤에 결과값이 나오는 젤뒷부분에 to_char를 붙이자.
, '전체')as 부서번호
,count(*) as 인원수
from employees
group by rollup (department_id);
--비율 추가
select decode(grouping(department_id), 0 , to_char(department_id)
, '전체')as 부서번호
,count(*) as 인원수
,round(count(*)/(select count(*) from employees)*100,1) ||'%' as 비율
from employees
group by rollup (department_id);
--부서번호에 전체를 넣기 case문으로
select case grouping(department_id)
when 0 then to_char(department_id)
else '전체' end as 부서번호
,count(*) as 인원수
from employees
group by rollup (department_id);
-- 킴벌리의 null을 인턴이라고 바꾸고 비율구하기.
select case grouping(department_id)
when 0 then nvl(to_char(department_id),'인턴')
else '전체' end as 부서번호
,count(*) as 인원수
,to_char( round(count(*)/(select count(*) from employees )*100,1),'9990.9' )|| '%' as "비율" --원래는 '999.9'이지만 그렇게하면 정수부가없는것들은 .9 이런식으로나온다 그래서 '9990.9' 0은 정수부가 없으면 0 을써라라는 말임.
from employees
group by rollup (department_id);
-- 비율부분에 공백을 없애라. ltrim 왼쪽공백자르기 이용.
select case grouping(department_id)
when 0 then nvl(to_char(department_id),'인턴')
else '전체' end as 부서번호
,count(*) as 인원수
,ltrim(to_char( round(count(*)/(select count(*) from employees )*100,1),'9990.9' ))|| '%' as "비율"
from employees
group by rollup (department_id);
--
--[문제] employess 테이블에서 직종별(job_id) 인원수를 나타내었을때 --------------
-- 그 인원수가 10명 이상인 직종별 인원수를 나타내세요.
select job_id
,count (*) as 인원수
from employees
-- where count(*) >=10 -- 에러뜸. 어디의 무슨 카운트인지 모름.
group by job_id
having count(*) >=10; -- 그룹함수에 대한 조건절.
-- having 을 이용해 그룹에대한 조건을 부여해서 그조건만 출력되도록 한다.
select department_id as 부서번호
,count(*) as 인원수
,ltrim(to_char( round(count(*)/(select count(*) from employees )*100,1),'9990.9' ))|| '%' as "비율"
from employees
group by department_id
having round(count(*)/(select count(*) from employees )*100,1) >= 10 ; -- 숫자를 비교하니까 숫자타입:숫자타입 으로 맞춰서 조건을 넣는다.
--
select department_id, first_name, salary
from employees
order by department_id asc, salary desc;
--[문제]-- 아래처럼 나타내시오
------------------------------------------
연령대 성별 인원수
------------------------------------------
0 남 X명
0 여 X명
10대 남 X명
10대 여 X명
20대 남 X명
20대 여 X명
.........
--[힌트] 나이구하기. => extract(year from sysdate) - ( to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end ) +1 => 나이
select V.ageline as 연령대
,V.gender as 성별
,count(*) as 인원수
from
(
select trunc((extract(year from sysdate) - ( to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end ) +1),-1) as ageline
,case when substr(jubun, 7, 1) in ('1','3') then '남' else '여' end as gender
from employees
)V
group by V.ageline, V.gender -- 연령대별로 모은다음 다시 성별대로 2차로 그룹을 모음.
order by 1, 2 ;
--rollup 해서 연령대별 전체가 몇명인지. 보이게하고, grouping으로 그룹인지 아닌지를 판별하고 null값의 이름을 바꾸기. decode
select decode(grouping(V.ageline),0,to_char(V.ageline)
,'전체')as 연령대
,decode(grouping(V.gender),0,V.gender
,'전체')as 성별
,count(*) as 인원수
from
(
select trunc((extract(year from sysdate) - ( to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end ) +1),-1) as ageline
,case when substr(jubun, 7, 1) in ('1','3') then '남' else '여' end as gender
from employees
)V
group by rollup(V.ageline, V.gender)
order by 1, 2 ;
-- 롤백보다 좀더 자세히 나오는것 : case 로 하고 비율까지 구하기.
select case grouping(V.ageline) when 0 then to_char(V.ageline)
else '전체' end as "연령대"
,case grouping(V.gender) when 0 then V.gender
else '전체' end as "성별"
,count(*) as "인원수"
,round(count(*)/(select count(*) from employees)*100,1)||'%' as "비율"
from
(
select trunc((extract(year from sysdate) - ( to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end ) +1),-1) as ageline
,case when substr(jubun, 7, 1) in ('1','3') then '남' else '여' end as gender
from employees
)V
group by rollup(V.ageline, V.gender)
order by 1, 2 ;
--cube --그룹으로 나눈거의 전체를 다시 나눠주는 것이 생김..
select case grouping(V.ageline) when 0 then to_char(V.ageline)
else '전체' end as "연령대"
,case grouping(V.gender) when 0 then V.gender
else '전체' end as "성별"
,count(*) as "인원수"
,round(count(*)/(select count(*) from employees)*100,1)||'%' as "비율"
from
(
select trunc((extract(year from sysdate) - ( to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end ) +1),-1) as ageline
,case when substr(jubun, 7, 1) in ('1','3') then '남' else '여' end as gender
from employees
)V
group by cube(V.ageline, V.gender)
order by 1, 2 ;
-------------------------------------------------------------------------------------------------------------------------------------------------------------
--
select department_id
,decode(department_id,80,1) -- 80번이면 1을주고 아니면 아무것도안줌(null을줌)
from employees;
--
select sum( decode(department_id,80,1) ) --sum은 그룹함수이므로 null값을 빼버리고 계산한다.
from employees;
select count(*)
from employees
where department_id = 80; --위와 같은 것임.
--
--[문제] 아래처럼 나오도록 하시오 -----
전체사원수 0대 10대 20대 30대 40대 50대 60대
------------------------------------------------------
107 12명 12명 20명 23명 14명 21명 5명
------------------------------------------------------
select count(*) as "전체사원수"
-- count(V.ageline)
,sum(decode(V.ageline,0,1)) as " 0대"
,sum(decode(V.ageline,10,1)) as "10대"
,sum(decode(V.ageline,20,1)) as "20대"
,sum(decode(V.ageline,30,1)) as "30대"
,sum(decode(V.ageline,40,1)) as "40대"
,sum(decode(V.ageline,50,1)) as "50대"
,sum(decode(V.ageline,60,1)) as "60대"
from
(
select
trunc( (extract(year from sysdate) - ( to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end ) +1),-1 ) as ageline
from employees
)V ;
-- 만드려는 조건에 칼럼이 존재하지않으면 인라인뷰로 만들어서 처리해준다 .
-----------------------------------------------------------------------------------------------------
--[어려운 문제]
-----------------------------------------------------------------------------------------------------
job_id 로만 되어있다. // avg를 써야한다 평균치니까~
---------------------------------------------------------------------
직급명(job_id) 남자평균급여(수당포함) 여자평균급여(수당포함) 전체평균
---------------------------------------------------------------------
사원
주임
대리 ex) 8500 ex) 9000 8750
과장
차장
부장
이사
사장
--1번
select V.job_id as "직급명"
,avg(V.salaryavg*V.nam) as 남자수당평균
,avg(V.salaryavg*V.wo) as 여자수당평균
,avg(V.salaryavg) as 전체평균
from
(
select job_id
,nvl(salary + (salary*commission_pct), salary) as salaryavg
,case when substr(jubun, 7, 1) in ('1','3') then to_number('1') end as nam
,case when substr(jubun, 7, 1) in ('2','4') then to_number('1') end as wo
from employees
)V
group by V.job_id
order by 2;
--2번
select V.job_id as "직급명"
,avg(decode(V.gender,'남','1')*salaryavg) as 남자평균급여
,avg(decode(V.gender,'여','1')*salaryavg) as 여자평균급여
,avg(salaryavg) as 전체평균
from
(
select job_id
,nvl(salary + (salary*commission_pct), salary) as salaryavg
,case when substr(jubun, 7, 1) in ('1','3') then '남' else '여' end as gender
from employees
)V
group by V.job_id
order by 2;
--3번 --강사님의 문제풀이
select V.job_id as "직종ID"
-- ,V.gender
-- ,V.montsal
,avg(case V.gender when '남' then V.montsal end) as "남자평균급여"
,avg(case V.gender when '여' then V.montsal end) as "여자평균급여"
,avg(montsal) as "전체평균"
from
(
select job_id
,case when substr(jubun, 7, 1) in ('1','3') then '남' else '여' end as gender
,nvl(salary + (salary*commission_pct), salary) as montsal
from employees
)V
group by V.job_id
--***결과물 확인하기
직종id 남 여 전체
IT_PROG 5700 6000 5760
select avg(nvl(salary + (salary*commission_pct), salary))
from employees
where job_id = 'IT_PROG'
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 5.17화
-- ***** 누적에 대해서 알아봅시다 ***** --
create table tbl_panmae
(panmaedate date default sysdate
,jepumname varchar2(20)
,panmaesu number);
insert into tbl_panmae values(add_months(sysdate,-2),'새우깡',10);
insert into tbl_panmae values(add_months(sysdate,-2)+1,'새우깡',15);
insert into tbl_panmae values(add_months(sysdate,-2)+2,'감자깡',20);
insert into tbl_panmae values(add_months(sysdate,-2)+3,'새우깡',10);
insert into tbl_panmae values(add_months(sysdate,-1),'고구마깡',7);
insert into tbl_panmae values(add_months(sysdate,-1)+1,'새우깡',8);
insert into tbl_panmae values(add_months(sysdate,-1)+2,'감자깡',10);
insert into tbl_panmae values(add_months(sysdate,-1)+3,'감자깡',5);
insert into tbl_panmae values(sysdate-4 ,'허니버터칩',30);
insert into tbl_panmae values(sysdate-3 ,'고구마깡',15);
insert into tbl_panmae values(sysdate-2 ,'고구마깡',10);
insert into tbl_panmae values(sysdate-1 ,'허니버터칩',20);
insert into tbl_panmae values(sysdate ,'새우깡',10);
insert into tbl_panmae values(sysdate ,'새우깡',10);
insert into tbl_panmae values(sysdate ,'감자깡',5);
insert into tbl_panmae values(sysdate ,'허니버터칩',15);
insert into tbl_panmae values(sysdate ,'고구마깡',20);
insert into tbl_panmae values(sysdate ,'감자깡',10);
commit;
select *
from tbl_panmae;
select jepumname
,to_char(panmaedate, 'yyyy-mm-dd hh24:mi:ss') as panmaedate
,panmaesu
from tbl_panmae;
--새우깡만 본다.
select jepumname
,to_char(panmaedate, 'yyyy-mm-dd hh24:mi:ss') as panmaedate
,panmaesu
from tbl_panmae
where jepumname='새우깡';
---------------------------------
제품명 판매일자 판매량
새우깡 2016-03-17 10
새우깡 2016-03-18 15
새우깡 2016-03-20 10
새우깡 2016-04-18 8
새우깡 2016-5-17 10개
새우깡 2016-5-17 10개
-----------------------------------
--[문제]아래와 같이 구해보자
제품명 판매월 판매량
새우깡 2016-03 35
새우깡 2016-04 8
새우깡 2016-05 20
--
select
to_char(panmaedate, 'yyyy-mm') as "판매월"
,sum(panmaesu) as"판매량"
from tbl_panmae
where jepumname='새우깡'
group by to_char(panmaedate, 'yyyy-mm')
order by 1;
--[문제] 누적판매량을 추가해서 구해보자----------------------------------
제품명 판매월 판매량 누적판매량
새우깡 2016-03 35 35
새우깡 2016-04 8 43
새우깡 2016-05 20 63
--모든제품을 일단 한번보자
select jepumname
,to_char(panmaedate, 'yyyy-mm') as "판매월"
,sum(panmaesu) as"판매량"
from tbl_panmae
group by jepumname, to_char(panmaedate, 'yyyy-mm')
order by jepumname;
--새우깡먼저해보자
--누적 sum()over()
select V.panmonth as "판매월"
,V.monthqty as "월판매량"
,sum(V.monthqty)over(order by V.panmonth) as "월판매누적량" --sum(어떤것을 차곡차곡 누적할것인지 )over(정렬 기준)
from
(
select to_char(panmaedate, 'yyyy-mm') as panmonth --판매월
,sum(panmaesu) as monthqty --판매량
from tbl_panmae
where jepumname='새우깡'
group by to_char(panmaedate, 'yyyy-mm')
) V;
--모든것을 해보자.
select V.jepumname as "제품명"
,V.panmonth as "판매월"
,V.monthqty as "월판매량"
,sum(V.monthqty)over(partition by V.jepumname order by V.panmonth) as "월판매누적량" -- sum(어떤것을 차곡차곡 누적할것인지)over(정렬 기준), partition by (어떤것을 기준으로 칸막이를 칠것인지)
from
(
select jepumname
,to_char(panmaedate, 'yyyy-mm') as panmonth --판매월
,sum(panmaesu) as monthqty --판매량
from tbl_panmae
group by jepumname, to_char(panmaedate, 'yyyy-mm')
) V;
--
create or replace view view_panmae -- view_panmae 를 테이블은 아니지만 테이블처럼 보이게하는것.
as
select V.jepumname as "제품명"
,V.panmonth as "판매월"
,V.monthqty as "월판매량"
,sum(V.monthqty)over(partition by V.jepumname order by V.panmonth) as "월판매누적량" -- sum(어떤것을 차곡차곡 누적할것인지)over(정렬 기준), partition by (어떤것을 기준으로 칸막이를 칠것인지)
from
(
select jepumname
,to_char(panmaedate, 'yyyy-mm') as panmonth --판매월
,sum(panmaesu) as monthqty --판매량
from tbl_panmae
group by jepumname, to_char(panmaedate, 'yyyy-mm')
) V;
-- 만들어진 뷰테이블을 본다.
select *
from view_panmae;
-- 만들어진 view_panmae 을 조건을 걸어 보고싶은것만 본다
select *
from view_panmae
where 제품명 in ('감자깡','새우깡');
--[문제]---------------------------------------------- employees를 가지고 해보자.
영화명 상영일자 관객수 누적관객수
-----------------------------------------------------
create table tbl_movie
as
select job_id as moviename
, hire_date as playdate
, salary as people
from employees;
--
select *
from tbl_movie;
create or replace view view_movie
as
select V.moviename as 영화명
,V.playdate as 상영일자
,V.people as 관객수
,sum(V.people)over(partition by V.moviename order by V.playdate) as "누적관객수"
from
(
select moviename
,to_char(playdate, 'yyyy-mm-dd') as playdate
,sum(people) as people
from tbl_movie
group by moviename , to_char(playdate, 'yyyy-mm-dd')
)V ;
select *
from view_movie;
select *
from view_movie;
where substr(상영일자,1,4) in ('2006','2007');
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------- *** 서브쿼리 ( sub Query ) *** ---------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
서브쿼리 (sub Query)란?
select 문 속에 또 다른 select문이 포함되어져 있을때
포함되어진 select 문을 서브쿼리(sub Query)라고 부른다.
select ....
from .... --> 메인쿼리(Main Query) == 외부쿼리
where ... in(select .....
from ....) --> 서브쿼리(sub Query) ==내부쿼리
--
---[문제] employees 테이블에서 기본급여가 제일많은 사람의 정보를
-- 사원번호, 사원명, 기본급여를 나타내세요.
--힌트
from employees
where salary = (기본급여가 제일큰 salary)
--문제풀이
select employee_id as 사원번호
,first_name||' '||last_name as 사원명
,salary as 기본급여
from employees
where salary = (select max(salary) from employees);
--
---[문제] employees 테이블에서 기본급여가 제일많은 사람과 제일적은 사람의 정보를
-- 사원번호, 사원명, 기본급여를 나타내세요.
select employee_id as 사원번호
,first_name||' '||last_name as 사원명
,salary as 기본급여
from employees
where salary = (select max(salary) from employees) or --제일많은사람
salary = (select min(salary) from employees) ; --제일적은사람
---[문제] employees 테이블에서 부서번호가 60,80번 부서에 근무하는 사원들 중에
-- 월급이 50번 부서 직원들의 '평균월급'보다 많은 사원들만
-- 부서번호,사원번호,사원명,월급을 나타내세요.
--1
select department_id as 부서번호
,employee_id as 사원번호
,first_name||' '||last_name as 사원명
,nvl(salary + (salary*commission_pct), salary) as 월급
from employees
where department_id=60 and nvl(salary + (salary*commission_pct), salary) > (select avg(nvl(salary + (salary*commission_pct), salary)) from employees where department_id=50) or
department_id=80 and (nvl(salary + (salary*commission_pct), salary)) > (select avg(nvl(salary + (salary*commission_pct), salary)) from employees where department_id=50)
order by 1;
--2
select department_id as 부서번호
,employee_id as 사원번호
,first_name||' '||last_name as 사원명
,nvl(salary + (salary*commission_pct), salary) as 월급
from employees
where department_id in (60,80) and nvl(salary + (salary*commission_pct), salary) > (select avg(nvl(salary + (salary*commission_pct), salary)) from employees where department_id=50)
order by 1;
--
select avg(nvl(salary + (salary*commission_pct), salary)) as "부서번호50평균월급여"
from employees
where department_id=50;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------- ***** pairwise SubQuery ***** ----------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--[문제] employees 테이블에서 부서번호별로 기본급이 최대인 사원과
-- 기본급이 최소인 사원의 정보를
-- 부서번호, 사원번호, 사원명, 기본급여를 나타내시오.
select department_id
,max(salary) -- 최대
from employees
group by department_id;
select department_id
,min(salary) -- 최소
from employees
group by department_id;
--
from employees
where () -- pairwise SubQuery 컬럼을 2개를 묶어버림.
--
select department_id as 부서번호
,employee_id as 사원번호
,first_name||' '||last_name as 사원명
,salary as 기본급여
from employees
where (department_id, salary) in (select department_id ---- pairwise subquery 컬럼을 2개를 묶어버림. 부서번호이면서 기본급여인사람 중 in (부서별로묶고 각각의 부서들 중에서 max기본급여)
,max(salary)
from employees
group by department_id)
or
(department_id, salary) in (select department_id
,min(salary)
from employees
group by department_id)
order by 1, 4 desc;
-- 부서번호가 null인 Kimberely Grant 까지 올려주기
select department_id as 부서번호
,employee_id as 사원번호
,first_name||' '||last_name as 사원명
,salary as 기본급여
from employees
where (nvl(department_id,-9999), salary) in (select nvl(department_id,-9999) ---- pairwise subquery 컬럼을 2개를 묶어버림. 부서번호이면서 기본급여인사람 중 in (부서별로묶고 각각의 부서들 중에서 max기본급여)
,max(salary)
from employees
group by department_id)
or
(nvl(department_id,-9999), salary) in (select nvl(department_id,-9999)
,min(salary)
from employees
group by department_id)
order by 1, 4 desc;
--is로 처리하는거
select department_id as 부서번호
,employee_id as 사원번호
,first_name||' '||last_name as 사원명
,salary as 기본급여
from employees
where (department_id, salary) in (select department_id ---- pairwise subquery 컬럼을 2개를 묶어버림. 부서번호이면서 기본급여인사람 중 in (부서별로묶고 각각의 부서들 중에서 max기본급여)
,max(salary)
from employees
group by department_id)
or
(department_id, salary) in (select department_id
,min(salary)
from employees
group by department_id)
or department_id is null
order by 1, 4 desc;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------- ***** 상관 SubQuery (==서브상관쿼리) ***** ----------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[ 상관 SubQuery ] 이라 함은 Main Query(==외부쿼리) 에서 사용된
테이블에 존재하는 컬럼이 Sub Query(==내부쿼리)의 조건절( where절 , having 절 )에
사용되어질때를 말한다
select department_id
,employee_id
,salary --이 라인이 외부쿼리 테이블에 존재하는 컬럼
,( select count(*)+1 from employees where salary > E.salary ) as "기본급여등수1" -- +1은 등수를 구하니까. 이런식으로 구하면 어느 DB건 호환할수있다. --이 라인이 내부(서브)쿼리 에 사용되는것이 상관서브쿼리
,rank()over(order by salary desc) as "기본급여등수2" -- rank는 오라클이 제공해주는 함수이다. 제한적이다.
,rank()over(partition by department_id order by salary desc) as "부서내기본급여등수1"
,( select count(*)+1 from employees where department_id = E.department_id and salary > E.salary ) as "부서내기본급여등수2" -- 내 department_id가 E.department_id and salary(나의기본급여) > E.salary
from employees E
order by 1,3 desc;
select employee_id
,salary
,sum(salary)over(order by employee_id) as"누적1"
,( select sum(salary)
from employees
where employee_id <= E.employee_id ) as "누적2"
from employees E
order by employee_id
--
select V.moviename as 영화명
,V.playdate as 상영일자
,V.people as 관객수
,sum(V.people)over(partition by V.moviename order by V.playdate) as "누적관객수1"
,(select sum(people)
from tbl_movie
where moviename = V.moviename and playdate <= V.playdate) as "누적관객수2"
from
(
select moviename
,to_char(playdate, 'yyyy-mm-dd') as playdate
,sum(people) as people
from tbl_movie
group by moviename , to_char(playdate, 'yyyy-mm-dd')
)V ;
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 5.18
---------------------------------------------------------------------------------------------------
------------------************** 상관 서브쿼리를 통한 update 문 처리 **************------------------
----------------------------------------------------------------------------------------------------
create table tbl_bjh_emp
as
select * from employees;
select *
from tbl_bjh_emp;
update tbl_bjh_emp set first_name='배재현'
where employee_id = 100;
commit;
--
-- 컴퓨터속성 -> 이름바꾸고 다시 시작하면 오류가난다. C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN 경로에 간다. 그다음 리스너켜서 호스트부분과 일치시켜준다. ,또 tnsnames 켜서 호스트부분도 일치시켜준다
--또 , 명령프롬프트 키고 lsnrctl start 하고 엔터, (안되면 제어판: 제어판\모든 제어판 항목\관리 도구 서비스 오라클 XETN리스너 시작시켜줌 그다음 재부팅한다. )
--XE*(이름바꿔준다)* = <- 이부분이 네트서비스명
-- (DESCRIPTION =
-- (ADDRESS = (PROTOCOL = TCP)(HOST = user2-PC12*(연결할컴퓨터명으로바꿔준다)*)(PORT = 1521)) --원래 호스트에는 아이피를 씀
-- (CONNECT_DATA =
-- (SERVER = DEDICATED)
-- (SERVICE_NAME = XE)
-- )
-- )
--제어판\모든 제어판 항목\Windows 방화벽 -> 고급설정 -> 인바운드 -> 새규칙 -> 포트 -> 특정포트입력-> 만들기.
-->확인하기 cmd-> tnsping ->
------ *** 데이터베이스 링크 생성하기 *** -----
--상대방오라클에가자 링크생성
create database link link12
connect to hr identified by hr --상대방 hr임 뒤 hr은 암호.
using 'EX12'; --이렇게하면 Database link LINK12이(가) 생성되었습니다.
--생성된 데이터베이스 링크 조회하자
select *
from user_db_links;
--연결해보자 남의 테이블보기
select *
from tbl_ojs_emp@link12; -- 골뱅이를 쓰고 생성한 접근하고픈 링크에 간다.
select *
from employees@link12; -- 골뱅이를 써야 남에것이 나옴.
----자기테이블보기 실수후 처리방법..
select *
from tbl_bjh_emp;
--실수함
update tbl_bjh_emp set last_name ='왕';
--실수함... last_name 부분이 모두 왕이됨.. commit해서 롤백도안됨;
commit;
--상대방 employees last_name 부분을 덮어씌어보자
select *
from employees@link12;
--상대방것을 서브쿼리를 이용해서 바꿔보자
update tbl_bjh_emp ME set last_name = ( select last_name --상대방것으로 내것을 바꿀 정보를 set last_name 에다가 정보를바꿔서 넣어 보여줌.
from employees@link12
where employee_id = ME.employee_id ) --서브쿼리를 이용 조건을 맞춰줘서 반복문처럼 하나하나 비교하면서 넣어줌.
commit;
-----------------------------------------------------------------------------------------------------------------------------------------------
------------- ************** JOIN (조인) ************** ---------------- 중요!
-----------------------------------------------------------------------------------------------------------------------------------------------
join(조인)은 테이블(뷰)과 테이블(뷰)를 합치는 것을 말하는데,
행(row)를 합치는 것이 아니라,
컬럼(column)과 컬럼(column)을 합치는것이다.
그리고, 서로 다른 테이블(뷰)의 행(row)과 행(row)을 합치는 것은 UNION 연산자를 사용하는 것이다.
A = {1,2,3} 원소가 3개
B = {a,b} 원소가 2개
A⊙B = {(1,a),(1,b)
,(2,a),(2,b)
,(3,a),(3,b)}
데카르트 곱
데카르트 곱(수학) => 원소의 곱 : 3*2 = 6개 (모든 경우의 수)
짝을 맺을수있는 가지수는 6가지.
--> 데카르트곱을 데이터베이스에서는 Catersian Product (모든 경우의 수) 라고 부른다.
select *
from employees;
select count(*) --107개
from employees;
select count(*) --27개
from departments;
select count(*) --2889 (107*27) Catersian Product (모든 경우의 수)
from employees,departments;
--------- JOIN : SQL 1992 CODE 방식 (데이터베이스 벤더(회사)마다 문법이 다름)
-- SQL 1999 CODE 방식 (ANSI SQL 표준화) => 이것을 쓰기를 권장함
--1.SQL 1992 CODE 방식 ==> 테이블(뷰)과 테이블(뷰) 사이에 조인시 콤마(,)를 찍어주는 것.
--2.SQL 1999 CODE 방식 (ANSI SQL 표준화) ==> 테이블(뷰)과 테이블(뷰) 사이에 조인시 join 이라는 단어를 넣어주는 것.
--방식1
select *
from employees,departments; -- Catersian Product (모든 경우의 수)
--방식2
select *
from employees cross join departments; -- Catersian Product (모든 경우의 수) 모든경우일때는 join 앞에 cross를 넣어준다.
--
select count(*)
from employees,departments; -- 2899
select count(*)
from employees cross join departments; --2899
--방식1
select *
from employees E , departments D
where E.department_id = D.department_id; --조인조건절
-- equi join (=)
--방식2
select *
from employees E inner join departments D --조건절에 일치하는것만 보려면 inner 라고 쓴다.
on E.department_id = D.department_id; -- 조인조건절이 1999방식에선 on 이라고 쓴다.
-- inner join (내부조인)
--방식2-1
select *
from employees E join departments D
on E.department_id = D.department_id ;
-- inner는 생략가능 하다.
-- null 인놈은 안나왔다. 킴벨리를 나오도록해보자.
--방식1( 1992 )
select *
from employees E , departments D
where E.department_id = D.department_id(+) ; -- (+) 가 없는 employees E 의 모든행을 출력하고 부서번호가 그다음 조건 E.department_id = D.department_id 로 짝지어라 라는말. (+)를 붙이면 null값도 나옴.
--방식1-2( 1992 )
select *
from employees E , departments D
where E.department_id(+) = D.department_id ; -- (+) 가 없는 departments D 의 모든행을 출력하고 그다음 조건 E.department_id = D.department_id 로 짝지어라 라는말. 킴벌리의 null값은 안나옴
--방식2 ( 1999 )
select *
from employees E () join departments D --> () 에 left right full 이 들어갈수있다.
from employees E left join departments D --> 왼쪽을 테이블을 보여라
from employees E right join departments D -->오른쪽을 테이블을 보여라
from employees E full join departments D --> 모든테이블을 일단 보여라.
-- 요 3개를 outer join(외부조인) 이라하고 outer는 생략이 가능하다.
--1 ( 1999 )
select *
from employees E left join departments D
on E.department_id = D.department_id; --킴벨리나옴
--2 ( 1999 )
select *
from employees E right join departments D
on E.department_id = D.department_id; --킴벨리는 부서가없기때문에 안나옴.
--3 ( 1999 )
select *
from employees E full join departments D
on E.department_id = D.department_id; --킴벨리도 나오고 사람없는 부서도 다나옴.
--4( 1999 ) 아우터 생략
select *
from employees E join departments D
on E.department_id = D.department_id;
-- 기본 이너조인에서 left , right, full 을 쓰느냐에 따라서 달라짐 기본이너조인은 조건절 on 에맞는것만 나오게함.
----
select employee_id
,first_name||' '||last_name
,department_name
,department_id -- 요부분 실행이 안됨... -- 얘는 employees,departments 이랑 둘다 갖고있는 성분이 아니라서 오류가남
from employees E join departments D
on E.department_id = D.department_id;
----
select employee_id
,first_name||' '||last_name
,department_name
,E.department_id -- E를 붙여주면 가능함.
from employees E join departments D
on E.department_id = D.department_id;
----
select E.employee_id as 사원번호 -- E나 D를 붙여주면 속도가 높아짐 왜냐하면 다뒤지는거에 비해서 있는곳에서 뒤지기때문에.. 불편해도 이렇게하자.
,E.first_name||' '||last_name as 사원명
,D.department_name as 부서명
,E.department_id as 부서번호
from employees E join departments D
on E.department_id = D.department_id;
---- null 값까지 나오게하려면..
select E.employee_id as 사원번호
,E.first_name||' '||last_name as 사원명
,D.department_name as 부서명
,E.department_id as 부서번호
from employees E left join departments D -- 킴벌리 까지 나오게함.
on E.department_id = D.department_id;
--[문제]-------------
부서명 인원수
-------------------- 부서명 별로 이름을 구하시오 . + 전체사원과 비율을 구하시오
--문제풀이
부서명 - departments 테이블에 있다.
인원수 - employees 테이블에 있다.
두개를 합쳐야함. 조인해서.
select nvl(D.department_name,'인턴') as "부서명"
,count(*) as "인원수"
from employees E left join departments D
on E.department_id = D.department_id
group by D.department_name
order by 1;
-- + 전체사원과 비율을 구하시오
select decode(grouping(D.department_name), 0 , nvl(D.department_name,'인턴')
, '전체') as "부서명"
,count(*) as "인원수"
,round(count(*)/(select count(*) from employees)*100,1) ||'%' as 비율
from employees E left join departments D
on E.department_id = D.department_id
group by rollup(D.department_name)
order by 1;
--[문제]------------------------------------------------------
사원번호 사원명 월급 전체사원평균월급 평균차액(월급-전체사원평균월급)
-------------------------------------------------------------
--정답풀이
select A.employee_id as 사원번호
,A.first_name||' '||last_name as 사원명
,nvl(A.salary + (A.salary*A.commission_pct), A.salary) as 월급
,E.avgsal as 전체사원평균월급
,nvl(A.salary + (A.salary*A.commission_pct), A.salary) - E.avgsal as 평균차액
from employees A cross join (select trunc(avg(nvl(salary + (salary*commission_pct), salary))) as avgsal from employees ) E
order by 1;
cross -- 모든경우의수
select trunc(avg(nvl(salary + (salary*commission_pct), salary))) as avgsal from employees -- 오로지 1개가 나옴.. employees A(107개) * E(1개) = 107개
-- + 지표(평균보다 몇배 더버는지)
-- 7150(평균월급) : 100 = 24000(월급) : x
-- 7150 : 100 = 9000 : x
-- X = 24000(월급) * 100 / 7150(평균월급) - 지표공식
select A.employee_id as 사원번호
,A.first_name||' '||last_name as 사원명
,nvl(A.salary + (A.salary*A.commission_pct), A.salary) as 월급
,E.avgsal as 전체사원평균월급
,nvl(A.salary + (A.salary*A.commission_pct), A.salary) - E.avgsal as 평균차액
,trunc((nvl(A.salary + (A.salary*A.commission_pct), A.salary)*100)/E.avgsal) as 지표 -- X = 24000(월급) * 100 / 7150(평균월급)
from employees A cross join (select trunc(avg(nvl(salary + (salary*commission_pct), salary))) as avgsal from employees ) E
order by 6 desc;
--[문제]-----------------------------------------------------------------
부서번호 사원번호 사원명 월급 부서별평균월급 평균차액(월급-부서별평균월급) 지표
------------------------------------------------------------------------
--문제풀이
select A.department_id as 부서번호
,A.employee_id as 사원번호
,A.first_name||' ' ||last_name as 사원명
,nvl(A.salary + (A.salary*A.commission_pct), A.salary) as 월급
,trunc(B.avgsal) as 부서별평균월급
,trunc(nvl(A.salary + (A.salary*A.commission_pct), A.salary) - B.avgsal) as 평균차액
,trunc((nvl(A.salary + (A.salary*A.commission_pct), A.salary) * 100) / B.avgsal ) as 지표 -- X(지표) = 24000(월급) * 100 / 7150(평균월급) - 지표공식
from employees A left join (select nvl(department_id,-999) as department_id -- department_id 라고 as를 해줘야한다. B.department_id 로 접근해야하니까
,avg(nvl(salary + (salary*commission_pct), salary)) as avgsal
from employees
group by department_id order by 1) B
on nvl(A.department_id,-999) = B.department_id
order by 1, 7 desc;
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 5.19
-----------------------------------------------------------------------------------------------------------------------------------------------
------------- ************** Non Equi-JOIN ************** ----------------
-----------------------------------------------------------------------------------------------------------------------------------------------
Non Equi-JOIN --> 어떤 컬럼의 값이 특정 범위에 속할 때 사용하는 JOIN이다 . 범위에만 들어가는것이지 정확하게 일치하지 않음.
-- 소득세율 지표관련 테이블을 생성한다.
create table tbl_taxindex
(taxpercent number -- 세율 2%,5%,8%,10% .... 세금%
,lowincome number -- 연봉의 최저범위
,highincome number -- 연봉의 최고범위
);
insert into tbl_taxindex values(0.02,1,99999);
insert into tbl_taxindex values(0.05,100000,149999);
insert into tbl_taxindex values(0.08,150000,199999);
insert into tbl_taxindex values(0.1,200000,10000000000000000);
commit;
select *
from tbl_taxindex;
--각사원마다 연봉으로 세금을 얼마때는지 알아보자.
select employee_id
,first_name||' '||last_name as ename
,nvl(salary + ( salary * commission_pct), salary) *12 as yearincome
from employees;
--1992 방식
select A.employee_id as 사원번호
,A.ename as 사원명
,A.yearincome as 연봉
,B.taxpercent*100 ||'%' as 소득세율 --0.02 이런식으로 보여주기 싫으니까 100곱하고 %를 붙여주자.
,to_char(trunc (A.yearincome * B.taxpercent),'L999,999') as 연소득세액 -- 보기좋게 보여주도록한다.
from
(
select employee_id
,first_name||' '||last_name as ename
,nvl(salary + ( salary * commission_pct), salary) *12 as yearincome
from employees
) A , tbl_taxindex B
where A.yearincome between B.lowincome and B.highincome; -- between 을써서 비교할것을 앞에 쓰고 범위를 (a범위) between (b범위) 에 넣어준다. case 문을써도 되지만 case문은 값이 편동되지않는것에 쓰는게 좋다.
--1999 방식
select A.employee_id as 사원번호
,A.ename as 사원명
,A.yearincome as 연봉
,B.taxpercent*100 ||'%' as 소득세율 --0.02 이런식으로 보여주기 싫으니까 100곱하고 %를 붙여주자.
,to_char(trunc (A.yearincome * B.taxpercent),'L999,999') as 연소득세액 -- 보기좋게 보여주도록한다.
from
(
select employee_id
,first_name||' '||last_name as ename
,nvl(salary + ( salary * commission_pct), salary) *12 as yearincome
from employees
) A join tbl_taxindex B
on A.yearincome between B.lowincome and B.highincome;
-----------------------------------------------------------------------------------------------------------------------------------------------
------------- ************** 자기조인 (Self Join) ************** ----------------
-----------------------------------------------------------------------------------------------------------------------------------------------
--[문제]-------------------------------------------------------------------------
부서번호 사원번호 사원명 입사일자 직속상관번호 직속상관명
-----------------------------------------------------------------------------------
department_id employee_id first_name hire_date manager_id first_name
last_ name employee_id last_ name --직속상관명과 사원명의 컬럼은 같지만 다르게 표현해야한다.
select *
from employees;
--1992 방식
select E1.department_id as 부서번호
,E1.employee_id as 사원번호
,E1.first_name||' '|| E1.last_name as 사원명
,to_char(E1.hire_date,'yyyy-mm-dd') as 입사일자
,E2.employee_id as 직속상관번호
,E2.first_name||' '|| E2.last_name as 직속상관명
from employees E1 , employees E2 -- ( 자기가 자기한테 같은 테이블을 셀프조인함 단, as 만 다르게한다)
where E1.manager_id = E2.employee_id(+) -- 직속상관 id 와 직속상관의 부서번호id가 일치하는놈만 나와라 라는말. -- king 은 직속상관이없다
order by 1,2;
--1999 방식
select E1.department_id as 부서번호
,E1.employee_id as 사원번호
,E1.first_name||' '|| E1.last_name as 사원명
,to_char(E1.hire_date,'yyyy-mm-dd') as 입사일자
,E2.employee_id as 직속상관번호
,E2.first_name||' '|| E1.last_name as 직속상관명
from employees E1 left join employees E2 -- ( 자기가 자기한테 같은 테이블을 셀프조인함 단, as 만 다르게한다)
on E1.manager_id = E2.employee_id -- 직속상관 id 와 직속상관의 부서번호id가 일치하는놈만 나와라 라는말. -- king 은 직속상관이없다
order by 1,2;
--책에대한 테이블만들고 예제를 해보자--------------------
create table tbl_authorbook
(bookname varchar2(100)
,authorname varchar2(20)
,loyalty number(5)
);
insert into tbl_authorbook values('오라클','송성학',1000);
insert into tbl_authorbook values('오라클','강지혜',1500);
insert into tbl_authorbook values('해리포터','송성학',2000);
insert into tbl_authorbook values('상실의시대','공현정',3000);
insert into tbl_authorbook values('연금술사','김수현',1000);
insert into tbl_authorbook values('개미','전해정',2000);
insert into tbl_authorbook values('개미','김수현',1700);
commit;
select *
from tbl_authorbook;
--[문제]-tbl_authorbook 테이블에서 공저(같이지음)로 지어진 도서정보만 출력하세요.
--------------------
도서명 작가명 로얄티
--------------------
오라클 송성학 1000
오라클 강지혜 1500
개미 전해정 2000
개미 김수현 1700
--1 셀프조인
select A1.bookname as 책이름
,A1.authorname as 작가명
,A1.loyalty as 로얄티
from tbl_authorbook A1 join tbl_authorbook A2
on A1.bookname = A2.bookname and A1.authorname != A2.authorname; --책이름은 같지만 작가이름이 달라야한다.
--2 group by
select bookname,count(*)
from tbl_authorbook
group by bookname
select *
from tbl_authorbook
where bookname in ( select bookname
from tbl_authorbook
group by bookname
having count(*) > 1 ); -- 카운트되는 갯수로 그룹의 조건절인 having 을써서 구함.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@5.20 추가내용
-----------------------------------------------------------------------------------------------------------------------------------------------
------------- ************** multi table join (다중 테이블 조인) ************** ----------------
-----------------------------------------------------------------------------------------------------------------------------------------------
multi table join (다중 테이블 조인) -> 3개이상의 테이블을 가지고 조인스켜주는 것이다.
--------------------------------------------------------------
부서번호 부서명 사원번호 사원명 연봉 세율
--------------------------------------------------------------
D D E E E T
departmnets : D
employees : E
tbl_taxindex : T
-- 일단 두개를 가지고 조인을한다.
select *
from employees E left join departments D
on E.department_id = D.department_id;
-- 위 전체를 테이블로보고 다시 조인함 / 다중조인 D E T 모두 조인하는법.
select E.department_id as 부서번호
,D.department_name as 부서명
,E.employee_id as 사원번호
,E.first_name||' '||E.last_name as 사원명
,nvl(E.salary+(E.salary*E.commission_pct), E.salary)*12 as 연봉
,T.taxpercent as 세율
,(nvl(E.salary+(E.salary*E.commission_pct), E.salary)*12 * T.taxpercent) as 연소득세
from employees E left join departments D
on E.department_id = D.department_id
join tbl_taxindex T
on nvl(E.salary+(E.salary*E.commission_pct), E.salary)*12 between T.lowincome and T.highincome ; --이런식으로 다중으로 조인시킨다.
--[문제] ------------------------------------------------------이렇게 나오게하시오.
부서번호 부서명 도시명 사원번호 사원명 기본급여
--------------------------------------------------------------
select * from employees;
select * from departments;
select* from locations; -- 각각 중복되는 내용이 뭔지 보고 잘 이어주면 쉽게할수있다.
select E.department_id as 부서번호
,D.department_name as 부서명
,L.location_id as 도시명
,E.employee_id as 사원번호
,E.first_name||' '||E.last_name as 사원명
,E.salary as 기본급여
from employees E left join departments D
on E.department_id = D.department_id
left join locations L
on L.location_id = D.location_id;
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@5.20 유니온설명 위에 위치
-----------------------------------------------------------------------------------------------------------------------------------------------
------------- ************** UNION / UNION ALL / INTERSECT / MINUS ************** ----------------
-----------------------------------------------------------------------------------------------------------------------------------------------
1.UNION 은 테이블(뷰)과 테이블(뷰) 을 합쳐서 보여주는데
이것은 행과 행을 합친 결과를 보여주는 것이다.
select *
from tbl_panmae;
--[문제] tbl_panmae 테이블에서 이번달에 판매한것만 추출하세요.
--이번달
select *
from tbl_panmae
where to_char(panmaedate,'yyyy-mm') = to_char(sysdate,'yyyy-mm');
--3월달
create table tbl_panmae_201603 -- 예전꺼는 따로 빼서 저장해놓음.
as
select *
from tbl_panmae
where to_char(panmaedate,'yyyy-mm') = '2016-03';
--4월달
create table tbl_panmae_201604 --예전꺼는 따로 빼서 저장
as
select *
from tbl_panmae
where to_char(panmaedate,'yyyy-mm') = '2016-04';
-- tbl_panmae 테이블에서 이번달 판매한 것만 놔두고 나머지는 삭제한다. -- 최신것만 tbl_panmae에 넣겠다.
delete from tbl_panmae
where to_char(panmaedate,'yyyy-mm')!=to_char(sysdate,'yyyy-mm');
commit;
select *
from tbl_panmae
--[문제] 2016년 3월 부터~ 2016년 현재월(5월)까지 제품별 월별 판매량의 합을 출력하세요
--3,4월은 우리가 위에서 이미 삭제했다. 다시 행과행을 합쳐서 만들어보자.
select *
from tbl_panmae_201603
union
select *
from tbl_panmae_201604
union
select *
from tbl_panmae;
-- 문제풀이. 유니온으로 하면 합쳐짐. -- union 합집합에 중복포함되는 내용은 중복안되게 다 보여줌. (자동으로 소트됨)
select jepumname as 제품명
,to_char(panmaedate,'yyyy-mm') 판매월
,sum(panmaesu) as 판매량
from
(
select *
from tbl_panmae_201603
union
select *
from tbl_panmae_201604
union
select *
from tbl_panmae
) V
group by jepumname, to_char(panmaedate,'yyyy-mm')
order by 1,2 ;
-------------------------------------------
--------------------------------- union all
select *
from tbl_panmae_201603;
insert into tbl_panmae
select *
from tbl_panmae_201603
where jepumname='감자깡'; --3 월달에 판매되어진 감자깡을 tbl_panmae에 넣으시오 라는말.
select *
from tbl_panmae_201604;
insert into tbl_panmae
select *
from tbl_panmae_201604
where jepumname='새우깡' --4 월달에 판매되어진 새우깡을 tbl_panmae에 넣으시오 라는말.
commit;
select *
from tbl_panmae ;
-- union all 합집합에 포함되는 중복된내용도 전부 다 보여줌. (확실하게 중복된 사항이 없다고하면 union 보다는 union all이 속도가 더빠르니 union all을 쓰자)
select *
from tbl_panmae_201603
union all
select *
from tbl_panmae_201604
union all
select *
from tbl_panmae ;
--위치를 바꿔보자
select *
from tbl_panmae_201603
union all
select *
from tbl_panmae
union all
select *
from tbl_panmae_201604 ;
----------------------------------------INTERSECT / MINUS
INTERSECT - 합집합부분(중복된부분)만 나오게해줌.
MINUS - 차집합부분만(중복된부분빼고 나머지부분만) 나오게해줌.
--INTERSECT
select *
from tbl_panmae_201603
intersect --둘다 갖고있는것을 뽑아서 보여줌.
select *
from tbl_panmae;
--MINUS
select *
from tbl_panmae_201604
minus --위에 테이블을 기준으로 중복된것을 빼고 보여줌
select *
from tbl_panmae;
--MINUS
select *
from tbl_panmae
minus --위에 테이블을 기준으로 중복된것을 빼고 보여줌
select *
from tbl_panmae_201604;
-----------------------------------------------------------------------------------------------------------------------------------------------
------------- ************** 계층형 쿼리 ************** ---------------- 답변형 게시판을 만들때 쓰기좋다.
-----------------------------------------------------------------------------------------------------------------------------------------------
강지혜
|
| |
송성학 공현정
| | |
수지 최재우 최재이
<계층 구조>
select *
from employees;
--내 아래라인을 보고싶다 . 이부분 실행시켜보고 결과로 이해하자.
select *
from employees
start with employee_id = 100 --100번부터 출발한다 / 처음에만 100번이고 그다음값은 101이 온다 반복문같다.
connect by prior employee_id = manager_id; --*[prior 다음에 나오는 컬럼명은 start 하는 그 행에 있는 컬럼의 값] 이라고 기억하자 * / manager_id의 컬럼은 start의 컬럼이 아닌 다른행에있는 아이디를 찾는다.
--100 = manager_id 가 100인 행을 찾는다.
--101 = manager_id 가 101인 행을 찾는다.
ex) 강지혜 100
|
| |
송성학 101 공현정 102
| | |
수지 103 최재우 104 최재이 105
<계층 구조> 쭈욱 찾아주고 최하위에서 끝남
-- 내 윗라인을 보고싶다
select *
from employees
start with employee_id = 105 -- 105=>103=>102=>100
connect by prior manager_id = employee_id ; -- start 하는 105번에 해당하는 manager_id를 먼저씀 prior뒤에 .
--103 = 103
--102 = 102
--100 = 100
--null 끝
--계층형 쿼리에만 쓰이는 level 이있다.
select level -- 내밑으로 다 나와
,employee_id as 사원번호
,first_name||' '||last_name as 사원명
from employees
start with employee_id = 100
connect by prior employee_id = manager_id ;
---------------------------------------------------------
100 J king --1레벨
101 Neena Kochhar --2레벨
108 Nancy Greenberg --3레벨
109 Daniel Faviet --4레벨
110 John Chen
111 Ismael Sciarra
112 Jose Manuel Urman
113 Luis Popp
200 Jennifer Whalen --3레벨
203 Susan Mavris
204 Hermann Baer
205 Shelley Higgins
---------------------------------------------------------
-- level 이 보기쉽게 들여쓰기를 입힌다
select --level
lpad(' ', (level-1)*5,' ') || employee_id ||' '|| first_name||' '||last_name as 사원명-- (level-1)*5 만큼 공간을 확보하고, || 을 사용해 붙여줘야 이름전체가 붙으면서 레벨이 뛰어짐
from employees
start with employee_id = 100
connect by prior employee_id = manager_id ;
-----------------------------------------------------------------------------------------------------------------------------------------------
------------- ************** 시퀀스 (Sequence) ************** ----------------## 웹에서 대부분 엄청나게 많이쓴다 **!!! ## 게시판같은거할때
-----------------------------------------------------------------------------------------------------------------------------------------------
시퀀스 (Sequence) -> 숫자를 자동으로 증가시켜주는 것이다.
create table tbl_gesipan
(writeno number
,title varchar2(200)
,content varchar2(4000)
,writeday date default sysdate
);
create sequence seq_writeno
start with 1 --나는 1번부터 들어갑니다 라는뜻
increment by 1 -- 증가치(1)
nomaxvalue -- 들어오는 족족 계속 증가 (맥시멈은없다)
nominvalue -- (미니멈도없다)
nocycle -- cycle하지 않겠다 . cycle을 설정을 1000이라 설정해놓으면 다끝나면 다시 1부터~1000, 다시 1~1000 반복.
nocache;
-- create sequence seq_writeno ; 위에 설정해둔것과 같다
insert into tbl_gesipan values(seq_writeno.nextval,'집에가고싶다!!','집에 보내주세요~~ 제발요~~~~',default); -- ' ' 여기에 사용자가 입력한 글이 들어온다.
insert into tbl_gesipan values(seq_writeno.nextval,'자고싶다!!','집에서 자게 해주세요~~ 제발요~~~~',default) ;
insert into tbl_gesipan values(seq_writeno.nextval,'먹고싶다!!','삼겹살도 좋고 떡볶이도 좋고 밥도 좋고 ',default) ;
commit;
select *
from tbl_gesipan
--2번글을 지웠다
delete from tbl_gesipan
where writeno = 2;
commit;
--다른것을 넣었다
insert into tbl_gesipan values(seq_writeno.nextval,'안녕하세요? 한석규 입니다.','좋은 하루되세요~~',default) ;
select *
from tbl_gesipan; --방금넣은것은 4번이되었다.
--또 넣는다.
insert into tbl_gesipan values(seq_writeno.nextval,'안녕하세요? 두석규 입니다.','좋은좋은 하루되세요~~',default) ;
insert into tbl_gesipan values(seq_writeno.nextval,'안녕하세요? 세석규 입니다.','좋은좋은좋은 하루되세요~~',default) ;
insert into tbl_gesipan values(seq_writeno.nextval,'안녕하세요? 네석규 입니다.','좋은좋은좋은좋은 하루되세요~~',default) ;
commit;
select *
from tbl_gesipan
order by 1;
-- 또 지운다 5번글을
delete from tbl_gesipan
where writeno = 5;
commit;
--또 넣는다
insert into tbl_gesipan values(seq_writeno.nextval,'안녕하세요? 오석규 입니다.','오~~좋은 하루되세요~~',default) ;
insert into tbl_gesipan values(seq_writeno.nextval,'안녕하세요? 육석규 입니다.','육성재 친구입니다. 하루되세요~~',default) ;
commit;
select *
from tbl_gesipan
order by 1; --전체가 7개이다 2개를 삭제했으니. 더보기기능을 입혀보자. 시퀀스번호로는 사용할수없다. 삭제된글이 있기때문에. 그래서 행의 개수를 쓴다. rownum, row_number을 사용해서
--
select writeno
,title
,writeday
from
(
select row_number()over(order by writeno) as rno -- row nomber
,writeno
,title
,writeday
from tbl_gesipan
order by 1
) V -- 인라인뷰를 이용해 rno 를 컬럼화 한다.
where rno between 1 and 3 ; --3개씩 보여줘라
--더보기를 누르면
where rno between 1 and 3 이것이 where rno between 4 and 6 으로 바뀌고 계속 더보기를 누르면 3의 배수로 증가하다가 끝이남.
-- 시퀀스는 고유한 값을 넣을때 자동으로 증가시켜준다. 삭제를 시키면 빵구가 일어나기 때문에 더보기기능은 row_number로 설정해서 쓴다.
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 5.19
밑에꺼는 내일 이어서할거임
-----------------------------------------------------------------------------------------------------------------------------------------------
------------- ************** 테이블을 만들때************** ----------------
-----------------------------------------------------------------------------------------------------------------------------------------------
--constraint-> 제약조건
--부모 테이블 1 * 부모와 자식은 [ 1 : 多 ] 관계라고하고 이렇게 되어진것을 제1정규화를 한 결과이다.
create table tbl_buseo
(buno number(3) not null -- 이미 밑에서 프라이머리키설정해주어서 안써도되지만 not null을 써주는것이 좋다.
,buname varchar2(20) not null --null은 들어올수없다
,addr Nvarchar2(100) -- not null을 안쓰면 널을 쓸수있다.
,constraint PK_tbl_buseo_buno primary key(buno) --constraint PK_테이블명_프라이머리키로할컬럼 으로 한다. 프라이머리키는 null값이 들어올수없다.또한 중복된 ID는 허락하지 않는 것.
);
--자식 테이블 多(Many)
create table tbl_sawon
(
sawonno number(4) not null
,saname Nvarchar2(20) not null
,jubun varchar2(13) not null
--,hakbun varchar2(20) not null
,jik Nvarchar2(10) default '사원'-- 값을 안넣어주면 '사원'으로 자동으로됨
,hiredate date default sysdate-- 값을 안넣어주면 sysdate 현재시각 . default는 안넣어주면 이라는 것이라 생각하자.
,buno number(3)
,constraint PK_tbl_sawon_sawonno primary key(sawonno)
--,constraint PK_tbl_sawon_jubun primary key(jubun) --오류(table can have only one primary key) 프라이머리키는 오로지 테이블당 1개밖에 안된다.
,constraint UQ_tbl_sawon_jubun unique(jubun) --그래서 UQ를 unique 쓴다.UQ는 여러개 들어와도된다.
--,constraint UQ_tbl_sawon_hakbun unique(hakbun) --unique 여러개써도됨
,constraint CK_tbl_sawon_jik check(jik in('사장','부장','과장','대리','사원')) --CK check는 들어올 내용을 검사해서 어느칼럼의 in() in의 괄호 안에있는것만 들어와야한다는 조건을 거는것.
,constraint FK_tbl_sawon_buno foreign key(buno) references tbl_buseo(buno) -- FK foreign key 외부에있는것을 references (테이블명(칼럼)) 의 형식을 써주고 테이블명의 칼럼을 참조하겠다는 말 (참조받는테이블이 먼저 존재해야함)
);
insert into tbl_buseo values(10,'연구부','서울');
insert into tbl_buseo values(20,'관리부','부산');
insert into tbl_buseo values(30,'생산부','대구');
insert into tbl_buseo values(40,'영업부','광주');
insert into tbl_sawon values(1001,'일미자','8010012234567','사장', default, 20);
insert into tbl_sawon values(1002,'이미자','8110012234567','부장', default, 10);
insert into tbl_sawon values(1003,'삼미자','8210012234567','과장', default, 10);
insert into tbl_sawon values(1004,'사미자','8310012234567','대리', default, 10);
insert into tbl_sawon values(1005,'오미자','8410012234567','사원', default, 10);
commit;
select *
from tbl_buseo;
select *
from tbl_sawon;
drop table tbl_sawon purge; --테이블삭제방법
------- *** 컬럼명 변경하기 *** -----------------
select *
from tbl_sawon;
alter table tbl_sawon rename column jubun to juminno; --alter table (바꿀테이블) rename column (현재컬럼명) to (새로운컬럼명);
alter table tbl_sawon rename column juminno to jubun; --원상복구.
------- *** 테이블명 변경하기 *** -----------------
rename tbl_sawon to tbl_jikwon; --rename (현재테이블명) to (바꿀테이블명) ;
select *
from tbl_jikwon;
rename tbl_jikwon to tbl_sawon; --원상복구
select *
from tbl_sawon;
------- *** 컬럼의 데이터타입 변경하기 *** -----------------
desc tbl_sawon; --테이블에 데이터타입검색하는것.
alter table tbl_sawon modify saname Nvarchar2(30); --alter table (테이블명) modify (변경해야할컬럼명) (변경할데이터유형)
alter table tbl_sawon modify saname Nvarchar2(20); --원상복구
------- *** tbl_sawon 테이블의 컬럼에 주어진 default 값을 조회하기 *** -----------------
select column_name, data_default
from user_tab_columns
where table_name = 'TBL_SAWON';
------- *** tbl_sawon 테이블의 jik칼럼의 default 값 변경하기 *** -----------------
alter table tbl_sawon
modify jik default '대리'; -- 변경방법
alter table tbl_sawon
modify jik default '사원'; --원상복구
--default 하면 자동으로 사원이됨
insert into tbl_sawon values(1006,'육미자','8510012234567',default, default, 10);
commit;
select *
from tbl_sawon;
--------- *** default인 정보 넣기 *** -----------------
insert into tbl_sawon values(1007,'칠미자','8510012234567', 10); --요렇게 하면 이미만들어진컬럼은 6개인데 값을 4개만 주니까 값이충분치않다고 에러가뜸.
insert into tbl_sawon(sawonno, saname, jubun, buno) values(1007,'칠미자','8610012234567', 10); -- 컬럼네임뒤에 ()로 치고 들어갈항복을 써주면 가능함! 차례대로 1:1로 맵핑하면됨. 이렇겐 가능함.
select *
from tbl_sawon;
------- *** 컬럼에 부여한 default 값 제거하기 *** -----------------
alter table tbl_sawon
modify jik default null; -- null로 바꾸면됨
insert into tbl_sawon(sawonno, saname, jubun, buno) values(1008,'팔미자','8710012234567', 10);
commit;
select *
from tbl_sawon; -- 팔미자 직급 null
------- *** 컬럼 추가하기 *** -----------------
tbl_sawon 테이블에 email 이라는 컬럼을 추가해봅시다.
alter table tbl_sawon add email varchar2(50); -- alter table (해당테이블) add (추가할칼럼명) (데이터타입);
------- *** 컬럼 삭제하기 *** -----------------
tbl_sawon 테이블에 email 이라는 컬럼을 삭제해봅시다.
alter table tbl_sawon drop column email; -- alter table (해당테이블) drop column (삭제할컬럼명);
------- *** 컬럼 추가하기2 *** -----------------
--[문제] tbl_sawon 테이블에 email 이라는 컬럼을 추가해봅시다.
-- 그런데 추가하는 email 컬럼은 not null 이어야 한다.
alter table tbl_sawon add email varchar2(50) not null; -- 하지만 이미 해당테이블에 데이터가 존재하고있어서 안됨. 텅비어있어야한다.
alter table tbl_sawon add email varchar2(50) default ' ' not null; --일단 default 를 넣어주고 우리가 봤을땐 null이나 공백이나 같은거다. 그다음 다시업데이트해주면됨.
select *
from tbl_sawon;
------- *** tbl_sawon 테이블에 존재하는 제약조건 조회하기 *** -----------------
제약조건의 5가지 종류
1. primary key => 표기방법 P
2. unique key => U
3. check => C
4. foreign key => R
5. not null => C
--요렇게 조회하면 됨~~~
select *
from user_constraints
where table_name = 'TBL_SAWON';
--컬럼명까지 보고싶다하면
select *
from user_cons_columns
where table_name= 'TBL_SAWON';
--일반적으로 다같이 조인해서 본다.
select *
from user_constraints A join user_cons_columns B
on A.constraint_name = B.constraint_name
where A.table_name= 'TBL_SAWON';
------- *** 제약조건의 내용을 변경하기 *** -----------------
순서
1. 변경하려는 제약조건을 제거하기.
2. 제약조건을 추가하기
--1. 변경하려는 제약조건을 제거하기.
alter table tbl_sawon
drop constraint CK_TBL_SAWON_JIK;
--2. 제약조건을 추가하기
alter table tbl_sawon
add constraint CK_TBL_SAWON_JIK check(jik in('사장','부장','차장','과장','대리','사원'));
------- *** 테이블을 삭제하기 *** -----------------
drop table tbl_buseo; --자식이 참조하고있으니까 못지운다 . 자식부터 지워야함.
drop table tbl_sawon; --자식삭제
select * from tbl_sawon;
drop table tbl_panmae_201603;
select * from tbl_panmae_201603;
drop table tbl_panmae_201604;
select * from tbl_panmae_201604;
--create , alter , drop은 오토커밋임. 롤백이안댐.
------- *** 삭제한것을 휴지통에서 꺼내오기 *** -----------------
select *
from user_recyclebin; --이것이 휴지통임.
--한번보기
select *
from "BIN$CPFBPKt3QAi854RHpZHQSw==$0";
--휴지통에 복원하기
flashback table TBL_SAWON to before drop; -- 이것이 복구하는것 flashback table (오리지날 테이블명) to before drop
select * from tbl_sawon; --복원하니 다시나옴
flashback table TBL_PANMAE_201603 to before drop;
flashback table TBL_PANMAE_201604 to before drop;
------- *** 삭제한것을 휴지통에서 영구히 삭제하기 *** -----------------
--휴지통에 있는것을 필요없을때 영구히 버리는것
--일단 테스트겸 테이블을 만들겠음.
create table tbla
(no number);
insert into tbla values(1001);
create table tblb
(no number);
insert into tblb values(2001);
create table tblc
(no number);
insert into tblc values(3001);
commit;
-- create, drop, alter 으로 시작하는 명령어를 DDL (Data Definition Language 데이터정의어)라고하고 그것은 auto commit 이다.
select *
from tbla;
select *
from tblb;
select *
from tblc;
--휴지통으로 보내기~
drop table tbla;
drop table tblb;
drop table tblc;
select *
from user_recyclebin; --휴지통보기
-- 휴지통에서 제거하기 (영구히 삭제하기) B는 살리고 나머지 A,C 지워보겠음.
purge table TBLA;
purge table TBLC;
flashback table TBLB to before drop;
select *
from user_recyclebin; --휴지통보기 / 다없어졌지만 B는 살아서 다시돌아감 A와 C는 이제 영영볼수없다.
-- 휴지통속 몽땅다 제거하기 ( 휴지통속 모두 영구히 삭제 )
purge recyclebin; --깡그리비우는것
--테이블 삭제시 휴지통에 버리지 않고 바로 영구히 삭제하기
--일단 삭제할것을 만들고
create table tbld
(no number);
insert into tbld values(4001);
--그자리에서 바로 삭제해 버리기
drop table tbld purge;
select *
from user_recyclebin;
-----------------------------------------------------------------------------------------------------------------------------------------------
------------- ************** index(인덱스) ************** ----------------
-----------------------------------------------------------------------------------------------------------------------------------------------
적절한 인덱스를 넣으면 속도가 빨라지고 , 인덱스를 많이 넣으면 느려질수 있다.
create index 인덱스명
on 만들테이블명 (컬럼명)
-- (성,이름) -여기서 성이 선행컬럼
-- (이름,성) -이름이 선행컬럼
--1.선택도가 좋은 컬럼이어야 함
--2.단독으로 where 절에 많이 타는 컬럼이어야 함
--쓰는법
create index idx_employees_firstname
on employees(first_name);
create index idx_employees_ename
on employees(first_name, last_name);
-- create unique index idx_employees_firstname -- unique를 쓸수있고. 쓰면 중복되는것이 못들어옴. 그렇기때문에 주민번호 학번 군번 이럴때 쓰는게 좋다.
--생성되어진 index를 조회하기 --
select *
from user_indexes
where table_name = 'EMPLOYEES'; -- 생성한 인덱스 보여주기.
--컬럼에 대한정보까지 나오게하려면
select *
from user_ind_columns
where table_name = 'EMPLOYEES';
--인덱스는 무조건 오름차순. 내림차순을 하고싶다면
create index idx_employees_firstname2
on employees(first_name desc); -- 내림차순정렬
--보통 조인시켜서 봄.
select *
from user_indexes A join user_ind_columns B
on A.index_name = B.index_name
where A.table_name='EMPLOYEES'
-- 인덱스 삭제하기
drop index idx_employees_firstname2; -- drop index (인덱스명);
-- 프라이머리나 유니크를 쓰면 인덱스가 자동생성되는것을 보려고함.
create table tbl_student
(
hakbun varchar2(10) not null
,name varchar2(20)
,jubun varchar2(13)
,addr varchar2(100)
,constraint PK_tbl_student_hakbun primary key(hakbun)
,constraint UQ_tbl_student_jubun unique(jubun)
);
select *
from user_indexes A join user_ind_columns B
on A.index_name = B.index_name
where A.table_name='TBL_STUDENT';
--인덱스삭제하기 ->프라이머리키와 유니크를 쓴 인덱스는 제약조건을 없애버리면 자동적으로 없어진다.
drop index idx_PK_TBL_STUDENT_HAKBUN; -- 오류 (이거아님)
--프라이머리로된것 제약조건 없애버리는것
alter table tbl_student
drop constraint PK_tbl_student_hakbun;
select *
from user_indexes A join user_ind_columns B
on A.index_name = B.index_name
where A.table_name='TBL_STUDENT'; -- 제약조건없애니까 프라이머리인덱스 없어짐.
--유니크까지없애보자
alter table tbl_student
drop constraint UQ_TBL_STUDENT_JUBUN;
select *
from user_indexes A join user_ind_columns B
on A.index_name = B.index_name
where A.table_name='TBL_STUDENT'; -- 모두 없어짐. ㅋ
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------- **** JOB 스케줄러 테스트 **** ----------
JOB 스케줄러란?
==> 지정된 시간에 특정 업무(프로시저)가 자동적으로 발생되어지도록 만들어주는 것.
--SYS에서 작업시작
pmorauser 사용자가 JOB 스케줄러를 만들수 있도록 SYS에서 권한을 부여해주어야 한다.
grant create table to pmorauser;
grant create any job to pmorauser;
select value from v$parameter where name like '%job%';
-- 여기에 나오는 job_queue_processes 의 value 값은 job 을 몇개 까지
-- 만들어 줄수 있는지 갯수를 말한다.
-- 기본값은 4 인데 이 값이 0 이면 job 을 만들수가 없으므로 주의를 요한다.
-- 우리는 아래처럼 해서 10개 까지 늘려본다.
alter system set job_queue_processes = 10;
select value from v$parameter where name like '%job%';
--SYS에서 작업끝
--PMORAUSER 에서 작업시작
------ 데이터를 주기적으로 검사해서 자동으로 status 컬럼의 값이 1 에서 0 으로 update 되도록 하는 테이블 생성 -------
create table tbl_jobtest
(seqcode varchar2(20)
,name varchar2(20)
,content varchar2(100)
,opendate date
,status number(1) default 1
,updatedate date default sysdate
,constraint tbl_seq_PK primary key(seqcode)
);
--drop table tbl_jobtest purge;
--drop sequence seq_tbl_jobtest;
create sequence seq_tbl_jobtest
start with 1
increment by 1
nomaxvalue
nominvalue
nocycle
nocache;
select seqcode, name, content,
to_char(opendate, 'yyyy-mm-dd hh24:mi:ss') as opendate,
status,
to_char(updatedate, 'yyyy-mm-dd hh24:mi:ss') as updatedate
from tbl_jobtest;
-- 테이블 tbl_jobtest 에 insert 하는 프로시저생성
create or replace procedure pcd_tbl_jobtest_insert
(p_name in tbl_jobtest.name%TYPE -- tbl_jobtest.의 name값의 타입을 말한다. / 값을 입력받을때 변수의 데이터타입을 쓸때 varchar2(20)같은 싸이즈를 쓰면 안된다.
,p_content in tbl_jobtest.content%TYPE
,p_opendate in tbl_jobtest.opendate%TYPE
)
is
v_seq number; -- 여기는 사이즈 잡아도 아무 문제 없다.
begin
select seq_tbl_jobtest.nextval into v_seq
from dual;
insert into tbl_jobtest values
('KH'||to_char(sysdate, 'yyyymmdd')||v_seq , p_name , p_content, p_opendate , default, default);
commit;
end pcd_tbl_jobtest_insert;
exec pcd_tbl_jobtest_insert('한석규','한석규 주연 영화입니다.', to_date('2016-10-04 21:17:00','yyyy-mm-dd hh24:mi:ss'));
exec pcd_tbl_jobtest_insert('두석규','두석규 주연 영화입니다.', to_date('2016-10-04 21:18:00','yyyy-mm-dd hh24:mi:ss'));
exec pcd_tbl_jobtest_insert('세석규','세석규 주연 영화입니다.', to_date('2016-10-04 21:19:00','yyyy-mm-dd hh24:mi:ss'));
exec pcd_tbl_jobtest_insert('네석규','네석규 주연 영화입니다.', to_date('2016-10-04 21:20:00','yyyy-mm-dd hh24:mi:ss'));
exec pcd_tbl_jobtest_insert('오석규','오석규 주연 영화입니다.', to_date('2016-10-04 21:21:00','yyyy-mm-dd hh24:mi:ss'));
exec pcd_tbl_jobtest_insert('육석규','육석규 주연 영화입니다.', to_date('2016-10-04 21:22:00','yyyy-mm-dd hh24:mi:ss'));
select seqcode, name, content,
to_char(opendate, 'yyyy-mm-dd hh24:mi:ss') as opendate,
status,
to_char(updatedate, 'yyyy-mm-dd hh24:mi:ss') as updatedate
from tbl_jobtest;
-- 테이블 tbl_jobtest 에 update 하는 프로시저생성
create or replace procedure pcd_tbl_jobtest_update
is
v_seq number;
begin
update tbl_jobtest set status = 0, updatedate = sysdate
where status = 1
and (sysdate - opendate) > 0;
commit;
end pcd_tbl_jobtest_update;
------ ***** JOB Scheduler 생성하기 ****** --------
-- JOB 등록하기 --
-- 여기서는 프로시저 pcd_tbl_jobtest_update 를 JOB 으로 설정하도록 한다.
DECLARE
jobno NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => jobno
,what => 'PMORAUSER.PCD_TBL_JOBTEST_UPDATE;'
,next_date => to_date('2016-10-04 21:18:00','yyyy-mm-dd hh24:mi:ss')
,interval => 'SYSDATE + 1/(24*60)'
,no_parse => TRUE
);
END;
--* job : 실행할 job number 기본적으로 1부터 들어옴.
--* what - 실행할 PL/SQL 프로시저(procedure) 명 혹은 psm 문장의 sequence
--* next_date - job을 언제 처음 시작할 것인지 지정한다. date type으로 evaluate되는 문자열 입력(SYSDATE)
--* interval - job을 수행한 후, 다음 실행시간까지의 시간을 지정한다.
-- 위 셋팅 'SYSDATE + 1/(24*60)' 은 1분마다 실행한다.
--* no_parse - true 이면 submit시에 job을 parsing하지 않는다.
-- *** 프로시저 소스 보기
select text
from user_source
where type='PROCEDURE' and name='PCD_TBL_JOBTEST_UPDATE';
-- job queue 정보 조회
SELECT * FROM USER_JOBS;
-- 몇분이 지난후 아래를 실행해본다.
select seqcode, name, content,
to_char(opendate, 'yyyy-mm-dd hh24:mi:ss') as opendate,
status,
to_char(updatedate, 'yyyy-mm-dd hh24:mi:ss') as updatedate
from tbl_jobtest;
-- JOB 지금바로 실행하기 -- 여기서 숫자 1은 job 의 번호 임.
SELECT job FROM USER_JOBS;
execute dbms_job.run(1);
commit;
-- JOB 정지 -- 여기서 숫자 1은 job 의 번호 임.
SELECT job FROM USER_JOBS;
execute dbms_job.broken(1, true);
commit;
-- 정지된 JOB 재시작 -- 여기서 숫자 1은 job 의 번호 임.
SELECT job FROM USER_JOBS;
execute dbms_job.broken(1, false);
commit;
-- JOB 삭제 -- 여기서 숫자 1은 job 의 번호 임.
SELECT job FROM USER_JOBS;
execute dbms_job.remove(3);
commit;
-- JOB 변경 next_date -- 여기서 숫자 1은 job 의 번호 임.
SELECT job FROM USER_JOBS;
execute dbms_job.next_date(1, trunc(sysdate)+1+1/24/60);
commit;
**** job Interval 설정
ex)
SYSDATE+7 : 7일에 한번 씩 job 수행
SYSDATE+1/24 : 1시간에 한번 씩 job 수행
SYSDATE+30/ : 30초에 한번 씩 job 수행(24: 시간 당, 1440(24x60):분 당, 86400(24x60x60):초 당 )
TRUNC(SYSDATE, 'MI')+8/24 : 최초 job 수행시간이 12:29분 일 경우 매시 12:29분에 job 수행
TRUNC(SYSDATE+1) : 매일 밤 12시에 job 수행
TRUNC(SYSDATE+1)+3/24 : 매일 오전 3시 job 수행
NEXT_DAY(TRUNC(SYSDATE),'MONDAY')+15/25 : 매주 월요일 오후 3시 정각에 job 수행
TRUNC(LAST_DAY(SYSDATE))+1 : 매월 1일 밤 12시에 job 수행
TRUNC(LAST_DAY(SYSDATE))+1+8/24+30/1440 : 매월 1일 오전 8시 30분
--- sys 였었던 부분
show user;
-- sys의 권한으로 pmorauser라는 이름으로 새로운 접속을 할 수 있게 해주기.
create user pmorauser identified by pmorauser
default tablespace users;
-- sys의 권한으로 다른 접속에서 처리할수있게끔하기
grant connect,resource,unlimited tablespace, create view to pmorauser;
-- 오라클사에서 기본적으로 제공하는 hr 계정 잠긴것 풀기.
alter user hr account unlock;
alter user hr IDENTIFIED by hr;
--
create user oppacar identified by oppacar
default tablespace users;
grant connect,resource,unlimited tablespace, create view to oppacar;
show user;
--파이널 프로젝트 오라클 유저 id / passwd ==> p_commit
create user p_commit identified by p_commit
default tablespace users;
--
grant connect,resource,unlimited tablespace, create view to p_commit;
-- 파이널전 테스트 하는것.
create user f_test identified by f_test
default tablespace users;
--
grant connect,resource,unlimited tablespace, create view to f_test;
select *
from dba_users;
--오라클사에서 공부를 위해 제공해주고 있는 샘플용 오라클 일반사용자 계정이 있다.
alter user hr account unlock; --잠긴 계정을 푸는것
alter user hr identified by hr; -- 암호를 새로이 부여하는것
------------------------------------------------
---------- JDBC ------------
------------------------------------------------
show user;
--오라클 사용자 계정생성하기
create user pmorauser identified by pmorauser
default tablespace users;
grant connect,
resource,
unlimited tablespace,
create view to pmorauser;
---------------------------------------------------------
'코딩' 카테고리의 다른 글
| $$로 받기 때문에 인젝션 공격이 됨 (0) | 2018.07.31 |
|---|---|
| SELECT 원하는 날짜 조회 하기 혹은 오늘날짜 조회 (0) | 2018.07.30 |
| 마리아 db에서 sql시 부호 쓰기위한 파싱 (0) | 2018.07.23 |
| docker 삭제, 생성, 빌드 등 (0) | 2018.06.25 |
| map형과 list형 (0) | 2018.06.18 |
