Oracle - SQL

띠그랭 2020. 8. 19. 18:23

2020. 8. 19. 18:23

Oracle - SQL

Oracle (feat. docker)

※ Oracle Database 11g Express Edition 중심으로

자바에서 Oracle DB를 사용할 때 JDBC 라이브러리를 사용하게 되는데 이때 JDBC를 사용하기 위해 몇가지 필요한 정보가 있다. 즉 JDBC를 통해 Oracle DB에 connection하기 위해 몇 가지 필요한 정보가 있다. 대표적으로 Oracle 사용자 계정에 대한 id, password... 그 중에서 Oracle database에 대한 url 정보가 있다. 대체로 다음과 같은 형태이다.

jdbc:oracle:thin:@localhost:1521:xet

이것이 무엇을 의미하는지에 대해서 살펴본다. 이에 대한 프로토타입은 다음과 같다.

jdbc:oracle:driver_type:[username/password]@[//]host_name[:port][/XE]

위의 프로토타입으로부터 아래 정보를 해석해 보면 다음과 같다.

jdbc:oracle:thin:@localhost:1521:xe

-. jdbc:oracle:thin은 사용하는 JDBC드라이버가 thin 타입을 의미한다. 자바용 오라클 JDBC드라이버는 크게 두가지가 있는데 하나는 Java JDBC THIN 드라이버고, 다른 하나는 OCI기반의 드라이버라고 한다.

-. username/password은 option이다. [ ]안에 있는 정보는 반드시 명기할 필요는 없다는 뜻이다.

-. :port 번호도 option이다. 다만 Oracle의 listener port인 1521을 사용하지 않을 경우는 이 값을 명기해 줘야 된다. 예를 들어서 jdbc:oracle:thin:hr/hr@//localhost:1522

-. localhost는 Oracle DB가 설치되어 있는 서버의 IP인데 위 경우는 로컬에 설치되어 있다는 뜻이다.

-. 1521 은 오라클 listener의 포트번호이다.

-. /XE는 Oracle database client의 고유한 service name이다. 디폴트로 XE를 사용하므로 이 정보도 option이다. 이에 대한 설정 정보는 Oracle이 설치된 폴더 아래의 app\oracle\product\11.2.0\server\network\ADMIN\listener.ora 파일에 다음과 같이 표시되어 있다

SQLPLUS 를 docker 에서 접속

docker exec -it oracle12c sqlplus

계정 변경

conn kh/1234

SQLPLUS 계정 수정하기

alter user hr account unlock; ==> hr유저의 상태를 unlock

USER hr의 비밀번호를 1234로 지정

alter user hr identified by 1234;

화면 clear

cl scr;

계정 만들기 (id = kh , pwd = 1234 )

create user kh identified by 1234 default table space users;

권한 주기

grant connect to kh;

로그아웃

disconnect

현 사용자 확인

show user

username , account_status 확인

select username , account_status from dba_users

sql 파일 실행하기

start 파일경로

접속시 오류 및 해결 방법

SQL> connect username/password
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL>

Oracle User 접속시 Profile 을 액세스 하는 오류입니다.
SYSTEM User 계정으로 PUPBLD.SQL 실행이 필요합니다.

SQL> connect system/password Connected. SQL> @?/sqlplus/admin/pupbld.sql

일반관리자 , 슈퍼 관리자

sys (슈퍼 관리자) - 데이터베이스 생성/삭제하는 권한 있음
데이터베이스 관련 모든 문제를 처리 가능
로그인시 SYSDBA룰로 접속해야함
system (일반 관리자) - 데이터베이스 생성/삭제 권한 X
xxxxxxxxxx
 데이터베이스 유지보수

no privileges on tablespace 'USERS' , 테이블 스페이스 권한 주기

grant create session, create table, create view,resource to (User_Name);

테이블 스페이스 권한 주기


xxxxxxxxxx
ALTER USER <user> quota 100M on <tablespace name>;

예제

show user;
--kh가 가진 테이블 조회 select * from tab;
--하나씩 조회 select * from department; select * from employee; select * from job; select * from location; select * from nation; select * from sal_grade;
--일반관리자, 슈퍼관리자 -- 1. sys(슈퍼관리자) : -- 데이터베이스를 생성/삭제하는 권한 있음 -- 데이터베이스 관련 모든 문제를 처리 가능 -- 로그인 시 SYSDBA로 접속해야함
-- 2. system(일반관리자) : -- 데이터베이스 생성/삭제 권한이 없음 -- 데이터베이스를 유지보수하는 관리자
/*
Table
데이터를 담고 있는 객체
데이터에 대해서 생성, 조회, 수정, 삭제(CRUD)작업을 하는 대상
행과 열로 이루어져 있다.
Table(Eneity, Relation) : Employee 테이블과 테이블을 붙여서(조인) = Relation Column(Filed, Attribute : Emp_Id 200 201 202... Row(Record, Tuple) : 행, 200 선동일 621225-1985634 sun_di@kh.or.kr 01099546325 D9 J1 S1 8000000 0.3 90/02/06 N Domain : 하나의 컬럼(속성_이 취할 수 있는 같은 타입으로 이루어진 값의 집합 gender : 남, 여 (여기서 남, 여를 도메인이라고 함) */
--하나의 테이블 정보조회 desc employee; --desc : 내림차순 --null여부 (nullable) --1. not null : 필수요소 --2. null : 선택요소
/* Data type
문자형 CHAR : 고정길이 문자형(최대 2000byte) VARCHAR2 : 가변길이의 문자형(최대 4000byte)
NCHAR : char와 동일하나 유니코드 문자개수로 처리 (고정형), 1000개까지 됨. 유니코드(2byte라서) NVARCHAR2 : varchar2와 동일하나 유니코드 문자개수로 처리 (가변형), 2000개까지 됨.
char(10) 고정형으로 문자열 10byte 'korea' => 실제로는 5byte지만, 저장시에는 10byte를 모두 사용한다. 빈공백으로 잔여크기 채움 >>> 고정형 '대한민국' => 글자당 2byte지만, 저장시에는 8byte가 아닌 10byte를 모두 사용 '대한민국 서울시' => 글자2, 공백1 : 15byte지만 최대크기가 10byte로 지정되어있어 에러가 발생된다.
varchar(20) 가변형으로 문자열 20byte까지 저장가능 'korea' : 실제 5byte, 저장도 5byte '대한민국' : 실제 8byte, 저장도 8byte '대한민국 서울시' : 실제 15byte, 저장도 15byte 물론 20byte보다 크면 오류
nchar(10) 고정형, 문자열개수가 10개까지 허용 nvarchar2(10) 가변형, 문자열개수가 10개까지 허용
long : 최대 2GB까지 작성 가능한 가변형 문자타입
*/
create table tb_chartype( col1 char(8), col2 varchar2(8), col3 nchar(6), col4 nvarchar2(6) );
--데이터입력(create) Insert into tb_chartype values('abc', 'abc', 'abc', 'abc');
Insert into tb_chartype values('가다', '하다', '호호나', '푸호나'); --오라클에서 한글처리는 2byte지만, 11g xe버전은 3byte로 처리된다.
select * from tb_chartype;
--메모리에서 작업인것을 취소 rollback;
--메모리에서 작업한 내용을 실제 파일에 적용 commit;
/*
숫자형 number([p,s]) -- precision : 표현할 수 있는 전체 숫자 자리수(1~38) -- scale : 소수점이하 자리수(-84 ~ 127)
데이터입력값이 1234 678 일때,
데이터 타입 저장되는 값
number(7,3) 1234.678 number(7) 1235 number 1234.678 number(7,1) 1234.7 (반올림) number(5, -2) 1200 (마이너스는 소수점을 기준으로 왼쪽, 0으로 처리)
날짜형 : date 일자(년/월일) 및 시간(시/분/초) 정보를 관리 기본적으로는 화면에 년/월/일만 표시됨 (물론 시분초도 저장되어있음) 날짜의 연산 또는 비교가 가능함
연산 결과타입 설명
날짜+-숫자 date 하루를 1로 계산해서 처리함 날짜+-날짜 number 두 날짜의 차이를 리턴(하루가 1기준) */
select sysdate from dual; select sysdate-1 from dual; select sysdate+1 from dual;
select to_char(sysdate, 'yyyy/mm/dd hh:Mi:ss')from dual;
-- 정확한 시각정보(밀리초 이하)를 표현하기 위한 timestamp타입도 있다 select systimestamp from dual; /*
LOB형 clob : 최대 4GB 허용가능한 문자타입 */
--실습문제 : today라는 회사의 회원테이블을 작성할 때, -- 다음 필드에 적절한 db데이터 타입을 설정해서 tb_today_member 라는 테이블을 생성하세요. /*
id : 8자리에서 15자리
pw : 8자리 이상 15자리
name
phone : -없이 11자리
ssn : 주민번호 -없이 13자리
mileage : 회원 마일리지
reg_date : 가입일 */
create table tb_today_member( id varchar2(15) NOT NULL,
pw char(15) NOT NULL,
name varchar2(100) NOT NULL,
phone char(11) NOT NULL,
ssn char(13) NOT NULL,
mileage number,
reg_date date
);

--## SQL
/*
Structed Query Language : 구조화된 질의 언어
dbms사용되는 SQL문법이 조금씩 다름
DDL (Data Definition Language) 데이터베이스의 구조, 객체에 대한 언어 ex) create , drop , alter
DML (Data Manipulation Language) 저장된 데이터의 조작 ex) insert, update , delete, select ==> CRUD
DCL (Data Control Language) 사용자권한, 설정 ex) grant , revoke
TCL (Transaction Control Language) ex) commit , rollback, savepoint
*/

--##DQL
/*
데이터를 검색(추출)하기 위한 언어
DML로 분류
데이터를 조회한 결과를 ResultSet(결과집합)
-> 0개 이상의 행이 포함
-> 특정 기준에 의해서 정렬될 수 있음

select : 조회하고자 하는 컬럼명을 기술
from : 조회하고자 하는 테이블명 기술
where : 특정 행을 선출하는 조건을 기술
group by :
having :
order by : 특정 컬럼을 대상으로 오름, 내림 차순 지정 asc , desc
처리 되는 순서 From -> where -> group by -> having -> select -> order by (중요 앙앙)
*/
select * from employee;
select emp_id, emp_name,salary from employee;
select emp_id, emp_name,salary,job_code from employee where job_code='J5' order by emp_name asc;
select emp_id,emp_name,salary,job_code -- 3
from employee --1
where job_code='J5' -- 2
order by emp_name; -- 4

—1. job 테이블에서 job_name 컬럼만 출력
select job_name
from job;
--2. department테이블의 전체 컬럼을 출력
select *
from department;
--3 employee테이블에서 이름,이메일, 전화번호, 입사일을 출력
select emp_name,email,phone,hire_date
from employee;
—4. employee테이블에서 월급이 2,500,000원 이상인 사람만 출력
select *
from employee
where salary >= 2500000;
—5. employee테이블에서 월급이 350만원 이상이면서 job_code가 'J3 '인 사람의 이름
select emp_name
from employee
where salary >= 3500000 and job_code = 'J3';
--6 산술연산
— 연봉 : 월급 * 12 + (월급보너스) * 12
select emp_name,
salary,
bonus,
salary * 12,
(salary +(salary * nvl(bonus,0))) *12
from employee;

— null처리 함수 : nvl(col, val) ==> 값을 리턴
— col의 값이 null인 경우 val을 리턴
— col의 값이 null이 아닌 경우 col의 값을 리턴
select nvl(7,3), nvl(null,3), nvl('이순신','거북선'), nvl(null,'거북선')
from dual;
select nvl(bonus, 0), bonus
from employee;
— 컬럼명 별칭(alias)지정
— as "별칭" ===> as 와 "" 생략 가능
-- as는 항상 생략이 가능하지만 ""을 반드시 써야하는 경우가 있음 : 공백이 포함된 경우, 숫자로 시작되는 경우

— 컬럼에 리터럴(literal)지정
— 리터럴은 resultset의 모든 행에 반복 표시
select emp_name, salary, '원' as 단위 , 12345
from employee;

— 중복값 제거 distinct
— select절에 한번만 사용 가능, 여러 컬럼을 사용하면, 여러 컬럼을 합친 것을 고유값으로 간주
select distinct job_code
from employee;

— 연결 연산자 ||
— 문자열연결, 기타값과 문자열연결에 +연산자가 아닌 || 연산자 사용
— 문자는 왼쪽 정렬, 정수형은 오른쪽 정렬
select emp_name, salary || '원' as "연봉"
from employee;

— where절에 사용하는 연산자
/*
-1. 논리연산자
and
or
not : 해당 결과를 반전 (null 제외)
-2. 비교 연산자
=
< , >
<= , >=
<> , ^= , != : 같지 않다
between 값1 and 값2 : 값1 이상 값2 이하
like , not like : 문자 패턴비교
is null , is not null : null여부 비교 -> where bonus is not null
in , not in : 비교값 목록에 포함되었는지 여부
*/

— 부서코드 D6이고, 급여를 2000000원보다 많이 받는 사람의 이름, 부서, 월급을 출력
select emp_name,dept_code,salary
from employee
where dept_code = 'D6' and salary > 2000000;

— 직급코드가 J1이 아닌 사원들의 월급등급(sal_level)을 중복없이 출력
select distinct sal_level
from employee
where job_code != 'J1';

— 급여를 3500000원 이상, 6000000 이하 의 이름과 연봉 출력
select emp_name,salary
from employee
where salary >= 3500000 and salary <= 6000000;

— 숫자 , 날짜에 대해서 사용가능
— 입사일이 90/01/01 ~ 01/01/01인 사원을 조회
— 기본 date 형식을 사용하면 됨
select *
from employee
where hire_date between '90/01/01' and '01/01/01';

— 문자열 패턴비교 like
— 비교하려는 값이 지정한 문자패턴을 만족시키면 true를 리턴함
— 와일드카드 % _를 사용함
— % : 글자가 없든지, 있으면 true
-- _ : 임의의 문자 1개를 의미

select emp_name,salary
from employee
where emp_name like '형';

--email에서 _의 앞자리가 3자리인 직원을 조회 --이스케이핑처리 : 임의의 문자를 이스케이프 문자로 사용하고, 이를 명시

create table tb_watch(
watchname varchar2(40),
description varchar2(200)
);
insert into tb_watch values ('금시계','순금 99.99% 함유 고급시계');
insert into tb_watch valuse('은시계','고객만족도 99.99점을 획득한 고급시계');
commit;

실습문제


xxxxxxxxxx
#@실습문제
1. EMPLOYEE 테이블에서 이름, 연봉, 총수령액(보너스포함), 실수령액(총 수령액-(월급*세금 3%))가 출력되도록 하시오
(컬럼명 변경)
2. EMPLOYEE 테이블에서 이름, 근무 일수(입사한지 몇일인가)를 출력해보시오.
3. EMPLOYEE 테이블에서 20년 이상 근속자의 이름,월급,보너스율를 출력하시오
4. EMPLOYEE 테이블에서 이름 끝이 연으로 끝나는 사원의 이름을 출력하시오 
5. EMPLOYEE 테이블에서 전화번호 처음 3자리가 010이 아닌 사원의 이름, 전화번호를 출력하시오
    
6. EMPLOYEE 테이블에서 메일주소 '_'의 앞이 4자이면서, DEPT_CODE가 D9 또는 D6이고 고용일이 90/01/01 ~ 00/12/01이면서, 월급이 270만원인 사원의 전체 정보를 출력하시오

select emp_name as "이름" , (salary * 12) || '원' as "연봉" , (salary + (salary * nvl(bonus,0))) * 12 as "총수령액", ((salary + (salary * nvl(bonus,0))) * 12) - (salary * 0.03) as "실수령액" from employee;

select emp_name as "이름",

trunc(sysdate - hire_date) as "근무 일수"

from employee;

select emp_name as "이름",

salary as "월급",

nvl(bonus,0) as "보너스율"

from employee

where trunc(sysdate - hire_date) >= (365 * 20);

select emp_name as "이름"

from employee

where emp_name like '%연';


xxxxxxxxxx
select emp_name as "이름",
  phone as "전화번호"
  from employee
  where phone not like '010%';


xxxxxxxxxx
select *
    from employee
    where email like '____\_%' escape '\'
    and (dept_code = 'D9' or dept_code = 'D6')
    and hire_date between '90/01/01' and '00/12/01'
    and salary >= 2700000;

비교연산자 in

비교 하려는 값 목록에 일치하는 것이 있는가? 있으면 true, 없으면 false

D6 , D8 부서인 사원들을 조회하라


xxxxxxxxxx
select *
  from employee
  where dept_code = 'D6' or dept_code = 'D8';
--------  
select *
  from employee
  where dept_code in('D6','D8');

D6, D8 부서가 아닌 사원들을 조회하라


xxxxxxxxxx
select *
  from employee
  where dept_code != 'D6' and dept_code != 'D8';
select *
  from employee
  where dept_code not in('D6','D8');

연산자 우선순위

산술 연산자
연결연산자 ||
비교연산자
IS NULL / IS NOT NULL, LIKE , IN / NOT IN
BETWEEN AND / NOT BETWEEN AND
NOT
AND
OR

부서코드가 J7 또는 J2이고, 급여가 2000000원 이상인 사원 조회

select *
from employee
where job_code in ('J7','J2')
and salary >= 2000000;

order by

select한 컬럼에 대해서 정렬기준을 제시
order by 컬럼명 | 별칭 | 컬럼순서 {정렬방식 (asc , desc)][NULLS FIRST | LAST]


xxxxxxxxxx
select emp_name
  from employee;
  order by emp_name desc;
  
select emp_id as "아이디",
  emp_name as "사원명",
  dept_code as "부서코드"
  from employee
  order by 아이디 desc;
  
// 부서코드로 선 정렬 후, 그안 에서 다시 사원명으로 정렬
select emp_id as "아이디",
  emp_name as "사원명",
  dept_code as "부서코드"
  from employee
  order by 3,2;

FUNCTION

일련의 처리과정을 반복적으로 사용하기 위해서 작성해둔 서브프로그램
호출시 값을 전달하면, 수행결과를 리턴하는 방식 (반드시 값을 리턴 , void X)

함수의 유형
단일행 처리 함수
문자처리함수
숫자처리함수
날짜처리함수
형변환함수
기타함수
그룹 함수
group by
having

단일행 처리 함수

문자 처리 함수
length : 문자 개수를 리턴


xxxxxxxxxx
select emp_name, length(emp_name),
  email, length(email)
  from employee;

lengthb : 문자열의 byte를 리턴


xxxxxxxxxx
select emp_name, lengthb(emp_name),
  email, lengthb(email)
  from employee;

instr : 찾는 문자열이 대상문자열에서 지정한 위치부터 지정한 회수번째에 나타난 위치를 반환

instr(대상문자열, 찾고자하는 문자열[,시작 인덱스 [, 몇번째]])

오라클에서 인덱스는 0기반이 아닌 1기반이다.


xxxxxxxxxx
select instr('kh정보교육원 국가정보원 정보문화사','정보',1,1),
     instr('kh정보교육원 국가정보원 정보문화사','정보',4),
     instr('kh정보교육원 국가정보원 정보문화사','정보',4,2),
     instr('kh정보교육원 국가정보원 정보문화사','정보',-6),  -- 음수는 역방향으로 찾는다, 오른쪽에서 왼쪽으로 검색
  from dual;

연습문제

email컬럼에서 @의 위치인덱스값을 출력

select email,
instr(email,'@')
from employee;

lpad / rpad : lpad(string,byte[,paddingString])

byte공간에 string문자열을 쓰고, 남은 공간 왼쪽/오른쪽에 paddingString으로 채워라


xxxxxxxxxx
select lpad(email,20,'#'), rpad(email,20,'#'),
  lpad(email,20), rpad(email,20)
  from employee;

ltrim / rtrim

ltrim / rtrim(string, str)


xxxxxxxxxx
select ltrim('             kh',' '),
  ltrim('               kh'),
  ltrim('000123456','0'),
  ltrim('12343124556797923kh','123456789') — 삭제할 문자열은 문자단위로 처리 (하나하나)
  from dual;

trim : 양쪽에서 지정한 문자를 제거


xxxxxxxxxx
select trim('        kh       '),
  trim('z' from 'zzzzzzzzkhzzzzz'),
  trim(leading 'z' from 'zzzzzzkhzzzzzzz'), -- 앞쪽 z만 제거
  trim(trailing 'z' from 'zzzzzkhzzzzzzz'),   -- 뒤쪽 z만 제거
  trim(both 'z' from 'zzzzzzkhzzzzzzzz')    -- 양쪽 z 제거
  from dual;
  
  요놈은 제거할 문자열 하나만 지정할 수 있다,

연습문제

1231233546356542hello48763987649387461


xxxxxxxxxx
select rtrim(ltrim('1231233546356542hello48763987649387461' , '123456789') , '123456789')
  from dual;

substr

substr(string, position ,[, length])


xxxxxxxxxx
select substr('showmethemoney', 5,2),
  substr('showmethemoney',10),
  substr('showmethemoney',-8,3)
  from dual;

lower / upper / initcap


xxxxxxxxxx
select lower('ABCDE'),
  upper('abcde'),
  initcap('abcde')    -- 첫글자만 대문자 잉잉
  from dual;

concat : 문자 연결함수이지만 , 2개의 문자만 처리가능 , 3개 이상연결에는 ||사용할 것


xxxxxxxxxx
select concat('abc','def')
  from dual;
  
select 'abc'||'def'||'ghi'
  from dual;

replace : replace(대상문자열,fromString, toString)


xxxxxxxxxx
select replace('show me the money','money','gimothy')
  from dual;

연습문제

사원명에서 성만 중복없이 출력(성은 한글자라고 가정)


xxxxxxxxxx
select distinct substr(emp_name , 1 , 1)
  from employee;
  order by 1;

employee테이블에서 남자만 사원번호,사원명,주민번호 출력(주민 뒷자리 *출력)


xxxxxxxxxx
select emp_name as "사원명",
  emp_id as "사원번호",
  rpad(substr(emp_no , 1,7) ,13, '*')
  from employee
    where substr(emp_no , 8 , 1) = '1' or substr(emp_no , 8 , 1) = '3';

다음 tb_files테이블에서 파일명만 출력


xxxxxxxxxx
create table tb_files(
  fileno number(3),
  filepath varchar2(500)
);
insert into tb_files values(1,'C:\abc\def\salesinfo.xls);
insert into tb_files values(2,'C:\music\안녕.mp3);
insert into tb_files values(3,'C:\movies\서치.mp4);


xxxxxxxxxx
select substr(filepath,instr(filepath , '\' , -1) + 1)
    from tb_files;

숫자 처리 함수

abs : 절대값


xxxxxxxxxx
select abs(10),
  abs(-10)
  from dual;

mod : 나머지 구하는 함수

%가 아닌 mod함수를 사용


xxxxxxxxxx
select mod(10,3),
  mod(10,2),
  mod(10,4)
  from dual;

round : 반올림

round(nunber, [, position])


xxxxxxxxxx
select round(123.456 , 3),
  round(123.456 , 2),
  round(123.456 , 1),
  round(123.456 , 0),   -- 0은 생략 가능
  round(123.456 , -1),
  round(123.456 , -2),
  from dual;

ceil


xxxxxxxxxx
select cell(123.456)
  from dual;

12.3456을 소수점 둘째자리까지 올림처리해서 표현하기


xxxxxxxxxx
select cell(123.456*100)
  from dual;

floor


xxxxxxxxxx
select floor(10.5),
  foor(10.51)
  from dual;

trunk : trunc(number , position)


xxxxxxxxxx
select trunc(123.456 , 1),
  trunc(123.456 , 2),
  trunc(123.456),
  trunc(123.456 , -1)
  from dual;

날짜처리함수

sysdate


xxxxxxxxxx
// 시분초 표현
select to_char(sysdate - 1, 'yyyy-mm-dd hh24:mi:ss'),
  to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
  to_char(sysdate + 1, 'yyyy-mm-dd hh24:mi:ss')
  from dual;
  
// 어제 , 오늘 , 내일 표현
// 하루를 1로 치환해서 연산
select sysdate - 1, sysdate , sysdate + 1
  from dual;
  
// 한시간 전
select to_char(sysdate - 1 / 24, 'yyyy-mm-dd hh24:mi:ss')
  from dual;
  
// 30분전
select to_char(sysdate - (30*1/24/60), 'yyyy-mm-dd hh24:mi:ss')
  from dual;

add_months : add_months(date, number);


xxxxxxxxxx
select add_months(sysdate , 1)
  from dual;
  
// 입사 후 3개월 후 날짜 조회
select emp_name, hire_date, add_months(hire_date , 3);
  from dual;

months_between : months_between(date1, date2)

date1 과 date2의 날짜 차이를 month단위로 리턴
1이 리턴


xxxxxxxxxx
select months_between(add_months(sysdate , 12) - sysdate)
  from dual;
  
// 수료 일로부터 남은 개월수
select months_between(to_date('19/03/12', 'yy/mm/dd') , sysdate)
  from dual;

연습문제

근무개월수 구하기


xxxxxxxxxx
select trunc(months_between(sysdate , hire_date)) || '개월' as "근무 개월수"
  from employee;

군복무 1년 6개월, 전역일, 전역일까지 먹어야 할 짬밥의 수


xxxxxxxxxx
select add_months(sysdate , 18) as "전역일",
  (add_months(sysdate , 18) - sysdate) * 3 as "짬밥 수"
  from dual;

next_day : 인자로 전달받은 요일데이터를 토대로 가장 빠른 해당요일을 리턴
next_day(date, string | number)


xxxxxxxxxx
-- 요일을 추출
select to_char(sysdate, 'day') "월요일",
  to_char(sysdate, 'dy')  "월",
  to_char(sysdate, 'd') "1" -- 일요일 1
  from dual;
  
select next_day(sysdate, 'thursday'),
    next_day(sysdate, 'monday'),
  next_day(sysdate, 6)
  from dual;

last_day : 해당일자를 기준으로 해당 월의 말일을 리턴


xxxxxxxxxx
select last_day(sysdate)
  from dual;

extract : 특정일에서 년월일 정보를 추출

extract(year|month|day from date)


xxxxxxxxxx
select extract(year from sysdate) "year",
    extract(month from sysdate) "month",
    extract(day from sysdate) "day"
  from dual;

trunc : trunc(date) : 시분초 정보를 00:00:00으로 처리


xxxxxxxxxx
select to_char(trunc(sysdate), 'yyyy-mm-dd hh24:mi:ss'),
  to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
  from dual;

// docker 의 컨테이너로 진입


xxxxxxxxxx
docker exec -t -i container_name /bin/bash

// /etc/localtime 링크


xxxxxxxxxx
sudo ln -sf /usr/share/zoneinfo/Asia/Seoul /etc/localtime


xxxxxxxxxx
-- 1
select department_name as "학과 명",
    category as "계열"
    from tb_department;
    
-- 2
select department_name || '의 정원은 ' || capacity || ' 입니다.' as "학과별 정원"
    from tb_department;
    
-- 3
select student_name
    from tb_student
    where absence_yn = 'Y'
    and department_no = '001'
    and (substr(student_ssn , 8 , 1) = '2' or substr(student_ssn , 8 , 3) = '3');
    
-- 4
select student_name
    from tb_student
    where student_no in('A513079','A513090','A513091','A513110','A513119')
    order by 1;
    
-- 5
select department_name as "학과 이름",
    category as "계열"
    from tb_department
    where capacity >= 20 and capacity <= 30;
    
-- 6
select professor_name
    from tb_professor
    where department_no is null;
    
-- 7
select *
    from tb_student
    where department_no is null;
    
-- 8
select class_no as "과목 이름"
    from tb_class
    where preattending_class_no is not null;
    
-- 9
select distinct category
    from tb_department
    order by 1;
    
-- 10
select student_no as "학번",
    student_name as "이름",
    student_ssn as "주민번호",
    student_address as "주소"
    from tb_student
    where absence_yn = 'N'
    and student_address like '%전주%'

형변환 함수


xxxxxxxxxx
  to_char()              to_number()
    ----------->            ----------->
  DATE          CHARACTER          NUMBER 
    <----------             <----------
    to_date()              to_char()


xxxxxxxxxx
select to_char(sysdate-1, 'yyyy-mm-dd-hh24:mi:ss') "어제",
    to_char(sysdate, 'yyyy-mm-dd-hh24:mi:ss') "오늘",  
    to_char(sysdate+1, 'yyyy-mm-dd-hh24:mi:ss') "내일"
from dual;

날짜에서 앞에 0 지우기


xxxxxxxxxx
select emp_name "사원명",
      to_char(hire_date, 'fmyyyy-mm-dd (dy)') "입사일" --fm은 공백제거 (0을 제거함!)
from employee;

sysdate 연산


xxxxxxxxxx
--2. 현재시각으로부터 1일 2시간 3분 4초뒤를 출력 (yyyy-mm-dd hh24:mi:ss)
select (to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')) "현재",
      (to_char(sysdate+1, 'yyyy-mm-dd hh24:mi:ss')) "+1일뒤",
      (to_char(sysdate+(1 + (1/24)*2), 'yyyy-mm-dd hh24:mi:ss')) "+2시간뒤",
      (to_char(sysdate+((1 + (1/24)*2)+((1/24)/60)*3), 'yyyy-mm-dd hh24:mi:ss')) "+3분뒤",
      (to_char(sysdate+((1 + (1/24)*2)+((1/24)/60)*3) + (1/24/60/60*4), 'yyyy-mm-dd hh24:mi:ss')) "+4초뒤"
from dual;

1이 1일이다

to_char 포맷


xxxxxxxxxx
-- to_char(number([,format])
/*
, ==> 9.999 : 세자리마다 콤마
. ==> 9.99 : 소수점이하 2자리까지 표현
9 ==> 999 해당 자리의 숫자
0 ==> 900 해당자리의 숫자가 없을 시 0으로 표현
$ ==> $999 통화기호
L ==> L999 Local통화로 표시 >> 한국은 \으로 표시됨
fm ==> fm999 포맷9로부터 치환된 공백 혹은 소수점 이하 0을 제거함
*/
select to_char(1234567, '999,999,999'),
      to_char(1234567, '999,999'), -- << 오류남 범위를 벗어남
      to_char(1234567, 'L999,999,999'),
      to_char(1234567, '999,999,999.999'),
      to_char(1234567, 'fm000,000,000.000')
from dual;


xxxxxxxxxx
select trunc((to_date('201903122130','yyyymmddhh24mi') - sysdate) * 24 *60 * 60) -- 초 구하기
    from dual;
    
-- ?일 ?시간 ?분 ?초 남았습니다.
--151.201608~~~~~~~~~~~~~
--일단위 -> 초단위 -> 결과도출
? == 초
select trunc(?/60/24/24) as "일",
  mod(trunc(?/60/60),24) as "시간",
  mod(tunc(?/60),60) as "분",
  mod(?,60) as "초"
  from dual;

to_number

to_number(character[, format])
1,000,000,000 - 50,000,000


xxxxxxxxxx
select to_number('1,000,000,000','9,999,999,999')  - to_number('50,000,000','999,999,999')
    from dual;

자동 형변환


xxxxxxxxxx
select '1000' + '100',
  to_number()
  from dual;

nvl

null처리 함수 nvl(컬럼, null일 경우 값)
nvl2(col,val1,val2)
col의 값이 null이 아니면 val1, null이면 val2


xxxxxxxxxx
select nvl2(7,3,2),
  nvl2(null,3,2),
  nvl2('거북선','이순신','이성계'),
  nvl2('null','이순신','이성계'),
  from dual;

보너스가 있는 사원은 보너스있음, 없으면 보너스없음으로 표시하세요,


xxxxxxxxxx
select emp_name,
  bonus,
  nvl2(bonus,'보너스 있음','보너스 없음')
  from employee;

기타함수

greatest , least

숫자,문자,날짜 비교


xxxxxxxxxx
select greatest('이재선','한동준','김민성'),
    least('이재선','한동준','김민성'),
    greatest(1,2,3),
    least(1,2,3),
    greatest(sysdate+1,sysdate,sysdate-1),
    least(sysdate+1,sysdate,sysdate-1)
  from dual;

decode

선택함수
switch문과 흡사함
decode(표현식,조건1,결과1,조건2,결과2) - 표현식은 반드시 값이여야 함

사원명과 직급코드에 따른 직급명으로 출력

J1 대표 , J2 부사장 , J3 부장 , J4 차장, J5 과장, J6 대리 , J7 사원


xxxxxxxxxx
select emp_name,
  job_code,
  decode(job_code,'J1','대표',
  'J2','부사장',
  'J3','부장',
  'J4','차장',
  'J5','과장',
  'J6','대리',
  'J7','사원'),
  decode(job_code,'J1','대표',
  'J2','부사장',
  'J3','부장',
  '평사원')
  from employee;

case

case when 조건1 then 결과1
when 조건2 then 결과2
...
else 결과
end

case 조건
when 조건1 then 결과1
when 조건2 then 결과2
...
else 결과
end


xxxxxxxxxx
select emp_name,
  case when substr(emp_no,8,1) = '1' then '남'
    when substr(emp_no,8,1) = '3' then '남'
    else '여'
    end "성별",
  case when substr(emp_no,8,1) in ('1','3') then '남'
    else '여'
    end "성별"
  from employee;


xxxxxxxxxx
select emp_name,
  case substr(emp_no,8,1)
    when '1' then '남'
    when '3' then '남'
    else '여'
    end
  from employee;

사원 나이를 조회 , 사원명, 주민번호 , 성별 , 나이 (현재년도 - 출생년도 + 1)


xxxxxxxxxx
select emp_name as "사원명",
  emp_no as "주민번호",
  case substr(emp_no,8,1)
    when '1' then '남'
    when '3' then '남'
    else '여'
    end as "성별",
  case when substr(emp_no,8,1) in('1','2') then to_char(sysdate,'yyyy') - to_char(substr(emp_no,1,2) + 1900)+1
         else to_char(sysdate,'yyyy') - to_char(substr(emp_no,1,2)+2000)+1
        end as "나이"
  from employee;
  
select extract(year from sysdate)
  - (to_number(substr(emp_no,1,2))+
  case when substr(emp_no,8,1) in ('1','2') then 1900
    else 2000 end) + 1 as "나이"
  from employee;

날짜 포맷 (rr을 사용할 시)

현재년도 입력년도 처리년도
00~49 00~49 현재세기(2000년도)
50~99 전세기(1900년도)
50~99 00~49 다음세기(2100년도)
50~99 현재세기(2000년도)

ex)

현재년도 입력년도 rr yy
1994 95 1995 1995
04 2004 1994

4자리인 경우엔 차이가 없구요. 2자리인경우엔 차이가 있습니다. yy 는 무조건 '20'이 앞에 붙습니다. rr 은 50년을 기준으로 작으면 '20' 크거나 같으면 '19'가 붙습니다. TO_DATE('00','yy') ==> 2000 TO_DATE('50','yy') ==> 2050 TO_DATE('00','rr') ==> 2000 TO_DATE('50','rr') ==> 1950

그룹함수

하나의 행이 아니라, 여러행을 그룹으로 묶어서 종합, 평균 등의 값을 리턴하는 함수

sum


xxxxxxxxxx
select sum(salary)
  from employee;

avg


xxxxxxxxxx
select avg(salary)
  from employee

count


xxxxxxxxxx
select count(*) as "총 사원수"
  from employee
select count(*) as "보너스 없는 사원"
  from employee
  where bonus is null;

min , max


xxxxxxxxxx
select max(salary),
  max(salary)
  from employee;
  
select max(hire_date),
  max(hire_date)
  from employee;

2번 pdf


xxxxxxxxxx
--1
select student_no as "학번",
    student_name as "아룸",
    entrance_date as "입학년도"
    from tb_student
    order by 3;
    
--2
select professor_name as "교수 이름",
    professor_ssn as "주민번호"
    from tb_professor
    where length(professor_name) != 3;
    
--3
select professor_name as "교수 이름",
    extract(year from sysdate) -
        (19 || substr(professor_ssn,1,2)) as "나이"
    from tb_professor
    order by 2;
    
--4
select substr(professor_name, 2) as "이름"
    from tb_professor;
    
--5
select student_no as "학번",
    student_name as "이름" 
    from tb_student
    where extract(year from entrance_date) - (1900 + substr(student_ssn , 1 , 2)) = 20;
    
--6
select to_char(to_date('20/12/25' , 'yy/mm/dd') , 'dy') as "크리스마스 요일"
    from dual;
--8
select student_no as "학번",
    student_name as "이름"
    from tb_student
    where substr(student_no ,1,1) != 'A';
    
select student_no as "학번",
    student_name as "이름"
    from tb_student
    where extract(year from entrance_date) < 2000;
--9
select round(avg(point),1)
    from tb_grade
    where student_no = 'A517178';
--10
select department_no as "학과번호",
    count(*)
    from tb_student
    group by department_no
    order by 1;
    
--11
select count(*)
    from tb_student
    where coach_professor_no is null;
select * from tb_student;
--12
select to_char(to_date(term_no ,'yyyymm'), 'yyyy') as "년도",
    round(avg(point),1) as "년도 별 평점"
    from tb_grade
    where student_no = 'A112113'
    group by to_char(to_date(term_no ,'yyyymm'), 'yyyy')
    order by 1;
--13
select department_no,
    sum(decode(absence_yn,'Y',1,0))
    from tb_student
    group by department_no
    order by 1;

group by

세부적인 그룹을 지정해서 그룹함수를 사용할 수 있다.

부서별 salary의 합계


xxxxxxxxxx
select dept_code,
  sum(salary)
  from employee
  group by dept_code;
  
select * 
  from employee
  where dept_code is null;  --null도 하나의 그룹으로 친다
--직급별 사원수를 조회하고, 직급순으로 정렬하라
select job_code, count(*) 사원수
    from employee
    group by job_code
    order by 1;
-- 부서코드별 급여의 합계, 급여의 평균(정수처리),인원수 조회
select dept_code,
    sum(salary),
    trunc(avg(salary))
    from employee
    group by dept_code;
--부서코드별로 보너스를 지급받는 사원 수를 조회
select dept_code,
    count(*)
    from employee
    where bonus is not null
    group by dept_code
    order by 1;
    
select dept_code,
    count(bonus) -- 컬럼의 값이 null인 행을 제외하고 연산
    from employee
    group by dept_code
    order by 1;
    
-- 두개이상의 컬럼을 대상으로 할 수 있다
select dept_code , job_code, count(*)
  from employee
  group by job_code, dept_code
  order by 1,2;
-- employee테이블에서 직급코드가 J1인 사원을 제외하고, 직급별 사원수, 평균 급여를 조회
select job_code,
  count(*),
  trunc(avg(salary))
  from employee
    where job_code != 'J7'
  group by job_code;
  
-- employee테이블에서 직급코드가 J1인 사원을 제외하고,
-- 인사년도별 인원수를 조회해서, 입사년 기준으로 정렬할 것
select extract(year from hire_date),
    count(*)
    from employee
    where job_code != 'J1'
    group by extract(year from hire_date)
    order by 1 ;
-- group by 안에 있는 것은 컬럼안에 존재 해야함
-- employee테이블에서 성별 인원수와 급여의 평균을 출력할 것
select decode(substr(emp_no,8,1),'1' , '남' , '3', '남' ,'여'),
    count(*),
    trunc(avg(salary))
    from employee
    group by decode(substr(emp_no,8,1),'1' , '남' , '3', '남' ,'여');
-- 부서내 성별 인원수를 구하세요
select dept_code,
    decode(substr(emp_no,8,1),'1' , '남' , '3', '남' ,'여'),
    count(*)
    from employee
    group by dept_code , decode(substr(emp_no,8,1),'1' , '남' , '3', '남' ,'여')
    order by 1;

Having

그룹함수 값을 구해야하는 그룹에 조건은 Having절에 기술함
그룹함수를 사용한 where은 쓸수 없다


xxxxxxxxxx
-- 부서별 급여평균이 300만원 이상인 부서들만 부서명, 급여평균을 출력
select dept_code as "부서명",
  trunc(avg(salary)) || '원' as "급여평균"
  from employee
  having trunc(avg(salary)) >= 3000000
  group by dept_code
    order by 1;
-- 부서별인원이 5명 이상인 부서만 출력
select dept_code,
    count(*)
    from employee
    having count(*) >= 5
    group by dept_code;
    
-- 매니저가 관리하는 사원이 2명 이상인 매니저 아이디와 관리하는 사원 수를 출력
select manager_id as "매니저 아이디",
     count(*) as "관리하는 사원의 수"
     from employee
     group by manager_id
     having count(*) >= 2 and manager_id is not null
     order by 1;

소계, 누계를 처리하기 : rollup, cube

rollup : 가장 먼저 지정한 그룹별 합계와 총 합계를 출력
cube : 그룹으로 지정된 모든 그룹에 대한 합계와 총 합계를 출력
그룹지정한 컬럼이 하나인 경우 , rollup 과 cube의 결과는 같다


xxxxxxxxxx
select dept_code,
  count(*)
  from employee
  group by rollup(dept_code)
  order by 1;
  
select dept_code,
  count(*)
  from employee
  group by cube(dept_code)
  order by 1;

grouping함수
group by절에 의해서 산출된 컬럼은 0을 리턴
rollup, cube절에 의해서 산출된 컬럼은 1을 리턴


xxxxxxxxxx
select dept_code,
    grouping(dept_code),
  count(*)
  from employee
  group by cube(dept_code)
  order by 1;
  
select dept_code,
    decode(grouping(dept_code),0,nvl(dept_code,'인턴'),1,'총계') as "부서",
  count(*)
  from employee
  group by cube(dept_code)
  order by 1;
  
--부서별 급여정도 출력(null처리 할것)
select case grouping(dept_code)
  when 0 then nvl(dept_code, '인턴')
  else '총계' end 부서코드,
  sum(salary)
  from employee
  group by rollup(dept_code)
  order by 1;
  
--두개이상의 컬럼으로 그룹핑하고, rollup/cube를 사용하면
--rollup
--부서내 직급별 인원정보
select dept_code,
    job_code,
    decode(grouping(dept_code), 0 , nvl(dept_code,'인턴') , '총계'),
    decode(grouping(job_code),0,job_code,'소계') as "직급",
    count(*)
    from employee
    group by rollup(dept_code,job_code)  -- 롤업은 첫번째 컬럼에서 두번째 컬럼 순으로 진행
    order by 1,2;
    
    
select dept_code,
    job_code,
    decode(grouping(dept_code), 0 , nvl(dept_code,'인턴') , '총계'),
    decode(grouping(job_code),0,job_code,'소계') as "직급",
    count(*)
    from employee
    group by cube(dept_code,job_code)  -- cube은 첫번째 컬럼에서 두번째 컬럼 순으로 진행 , 그리고 반대순인 두번째컬럼에서 첫번쨰 컬럼순으로도 한번 더 진행
    order by 1,2;

Join

Join : 가로합침, 컬럼과 컬럼을 합치는 것
union : 세로합침, 행과 행을 합치는 것
여러테이블의 레코드를 조합하여 하나의 행으로 표현한 가상 테이블을 생성
연관성이 있는 컬럼을 기준으로 조합


xxxxxxxxxx
select *
    from employee
    where emp_name = '송종기';
    
select *
    from department;
-- 조인으로 합체 (equal-join)
select *
    from employee join department
    on employee.dept_code = department.dept_id
    where emp_name = '송종기';

join 두가지 종류
equal-join : 동등조건으로 조인하는 경우 , = 을 이용
non equal-join : 동등조건이 아닌 between and , is null , is not nul , in등을 이용한 조인

join 두가지 문법
ANSI 표준문법 : DBMS와 상관없이 공통적으로 사용하는 표준 SQL
Oracle 표준문법 (벤더사 제공하는 문법) : Oracle에서 사용하는 구문 , 콤마(오라콤마)

equl - Join

조인하는 컬럼명이 다른 경우


xxxxxxxxxx
-- 부서명과 지역명을 출력하라
select distinct *
  from location join department
  on location.local_code = department.location_id;

조인하는 컬럼명이 같을 경우


xxxxxxxxxx
-- employee , job 
select * from employee;
select * from job;
select *
  from employee join job
  on employee.job_code = job.job_code;
  
-- 별칭을 줄수도 있음
select *
  from employee E join job J
  on E.job_code = J.job_code; 
  
select emp_name,
  E.job_code,
  job_name
  from employee E join job J
  on E.job_code = J.job_code;
-- 컬럼명이 같을경우에 한해서 on 이 아닌 using 키워드를 사용할 수 있음
-- 테이블명이나 별칭을 사용하면 ERROR
select emp_name,
  job_code,
  job_name
  from employee join job
  using(job_code)
  
-- 지역명과 국가명을 출력(location, nation)
select * from location;
select * from nation;
select national_name,
    local_name
    from nation join location
    using(national_code);

eqal-join 종류

inner join : 보통 교집합 생각하면됨
기본 조인 : (inner) join
outer join : 합집합
left (outer) join : 왼쪽 외부 조인
right (outer) join : 오른쪽 외부 조인
full (outer) join : 완전 외부 조인
cross join
self join
multiple join

inner join

교집합, 기존되는 컬럼에서 null이나 해당되는 값이 없다면 제외


xxxxxxxxxx
-- employee , department
select * from employee; -- 23개 row
select * from department; -- 9개 row
select *    -- 21개 row
    from employee inner join department
    on employee.dept_code = department.dept_id;

outer join

left outer join : 좌측 table 모두 반환, 우측 테이블 조건 컬럼에 일치하는 row만 반환


xxxxxxxxxx
select *
  from employee left outer join department
  on employee.dept_code = department.dept_id;

right outer join : 우측 table 모두 반환, 좌측 테이블 조건 컬럼에 일치하는 row만 반환


xxxxxxxxxx
select *
  from employee right outer join department
  on employee.dept_code = department.dept_id;

full outer join : 조건 컬럼에 일치하지않아도 , 우측 table , 좌측 table 모두 row 반환


xxxxxxxxxx
select *
  from employee full outer join department
  on employee.dept_code = department.dept_id;

조인 결과 : employee(23row) , department(9row)
inner join : 21
left outer join : 23 = 21 + 2
right outer join : 24 = 21 + 3
full outer join : 26 = 21 + 2 + 3

3번 pdf


xxxxxxxxxx
--1
select student_name as "학생 이름",
    student_address as "주소지"
    from tb_student
    order by 1;
    
--2
select student_name as "학생 이름",
    student_ssn as "주민 번호"
    from tb_student
    where absence_yn = 'Y'
    order by 2 desc;
--3
select student_name as "학생 이름",
    student_no as "학번",
    student_address as "거주지 주소"
    from tb_student
    where (student_address like '%경기도%' or student_address like '%강원도%')
    and student_no not like 'A%'
    order by 2;
--4
select tb_professor.professor_name as "교수 이름",
    tb_professor.professor_ssn as "주민 번호"
    from tb_professor join tb_department
    using(department_no)
    where tb_department.department_name = '법학과'
    order by 2;
--5
select student_no as "학생 학번",
    point as "학점"
    from tb_grade
    where class_no = 'C3118100' and term_no = '200402'
    order by 2 desc , 1;
--6
select student_no as "학생 번호",
    student_name as "학생 이름",
    tb_department.department_name as "학과 이름"
    from tb_student join tb_department
    using(department_no)
    order by 2;
    
--7
select class_name as "과목 이름",
    department_name as "학과 이름"
    from tb_class join tb_department
    using(department_no);
    
--8
select class_name as "과목 이름",
    tb_professor.professor_name as "교수 이름"
    from tb_class join tb_professor
    using(department_no)
    order by 1;
--9
select * from tb_class;
select * from tb_class_professor;
select * from tb_department;
select * from tb_grade;
select * from tb_professor;
select * from tb_student;

cross join

좌측 테이블 * 오른쪽 테이블
모든 경우의 수를 구하고, 행의 수는 (좌측테이블 행 개수 * 우측테이블 행 개수)
cartesian product : 카테시안의 곱


xxxxxxxxxx
select *
  from employee cross join department; -- 207
  
select count(*) from employee; --23
select count(*) from department; --9
-- 평균월급과 평균월급과의 차이
select trunc(avg(salary)) -- trunc(avg(salary))를 하나의 테이블로 인식
  from employee;
select emp_id,
    emp_name,
    salary,
    평균,
    salary - 평균 as "월급차이"
    from employee cross join(select trunc(avg(salary)) as "평균"
                            from employee);

self join

같은 테이블을 조인해서 결과셋을 얻고자 할 때 사용


xxxxxxxxxx
--사원명과 해당사원의 매니저명을 조회
select E.emp_id,
    E.emp_name,
    E.manager_id,
    M.emp_name
  from employee E join employee M
  on E.manager_id = M.emp_id; --15

multiple join(다중 조인)


xxxxxxxxxx
--사원명, 부서명, 지역명을 출력
select emp_name, dept_code from employee;
select dept_id,dept_title, location_id from department;
select local_name,local_name from location;
select emp_name,
    dept_code,
    local_name 
    from employee E
        join department D on E.dept_code = D.dept_id
        join location L on D.location_id = L.local_code;
        
--직급이 대리이면서, asia지역에 근무하는 직원을 조회
--사번,이름,직급명,부서명,근무지역명,급여
select emp_id as "사번",
    emp_name as "이름",
    J.job_name as "직급명",
    d.dept_title as "부서명",
    L.local_name as "근무지역명",
    salary as "급여"
    from employee E
        join JOB J on E.job_code = J.job_code
        join department D on E.dept_code = d.dept_id
        join location L on D.location_id = L.local_code
    where J.job_name = '대리' and L.local_name like 'ASIA%';

Oracle 전용 문법

join키워드 대신 ,(콤마)를 사용
on절 대신 where절을 사용


xxxxxxxxxx
-- 조인하는 컬럼명이 다를 경우
select dept_title, local_name
  from department D, location L
  where D.location_id = L.local_code;
  
-- 조인하는 컬럼명이 같을 경우 (Oracle 전용에는 Using이 없음)
-- employee, job;
select * from employee;
select * from job;

outer join
where절에 (+)이 없는 쪽을 우선 다출력
left : (+) 우항에 작성
right : (+) 좌항에 작성


xxxxxxxxxx
select *
  from employee E, department D
  where E.dept_code = D.dept_id(+); --employee E기준으로 join을 하고 싶으면 D.dept_id에 +표시

self join


xxxxxxxxxx
select E.emp_id,
  E.emp_name,
  E.manager_id,
  M.emp_name
  from employee E, employee M
  where E.manager_id = M.emp_id

multiple join (oracle전용구문의 다중조인에서는 테이블 순서가 중요하지 않다.)


xxxxxxxxxx
select emp_name,
  dept_title,
  local_name
  from employee E, department D, location L
  where E.dept_code = D.dept_id and D.location_id = L.local_code;

non - equal join

동등조건이 아닌 기준으로 조인


xxxxxxxxxx
select * from employee;
select * from sal_grade;
select emp_name,
  E.salary,
  S.sal_level
  from employee E join sal_grade S
    on E.salary between S.min_sal and S.max_sal;

집합연산자(Set Operator)

여러개의 질의결과를 가로로 연결하여 합치거나 뺀 후, 하나로 결합하는 방식
조건1. slect절의 컬럼수가 동일해야됨
조건2. select절의 동일위치계 존재하는 컬럼의 데이터타입이 상호 호환 가능해야함

union 합집합
union all 합집합
intersect 교집합
minus 차집합

A = {1,2,3,4,5};
B = {3,5,7,9};
union --- A U B = {1,2,3,4,5,7,9}; ==> 중복 제거 O , 첫번째 컬럼기준으로 오름차순
union all --- A U B = {1,2,3,4,5,3,5,7,9}; == > 중복 제거 X , 그냥 연결
intersect --- A n B = {3,5};
minus --- A - B = {1,2,4};


xxxxxxxxxx
--union
-- 가로합침, 중복데이터제거, 첫번째 컬럼 기준정렬(오름차순)
-- 부서코드가 D5인 사원의 사원번호, 사원명, 부서코드, 급여를 출력
select emp_id,
  emp_name,
  dept_code,
  salary
    from employee
    where dept_code = 'D5' --6
    
-- 급여가 300만원보다 많은 사원의 사원번호, 사원명, 부서코드, 급여를 출력
select emp_id,
  emp_name,
  dept_code,
  salary
  from employee
  where salary > 3000000; --9
  
-- 위에 두개를 union
select emp_id,
  emp_name,
  dept_code,
  salary
    from employee
    where dept_code = 'D5' --6
union
select emp_id,
  emp_name,
  dept_code,
  salary
  from employee
  where salary > 3000000; --9
  
-- 컬럼 타입과 위치가 모두 동일 해야함
select 123, 'zzz' from dual
union
select 'abc' 456 from dual  (XXXX)
select 123, 'zzz' from dual
union
select 456, 'abc' from dual
union
select 345, 'def' from dual;

grouping sets

group by + union all 을 한번에 처리


xxxxxxxxxx
--부서코드별 인원수, 직급코드별로 인원수를 동시에 출력
select dept_code,
  job_code,
  count(*)
  from employee
  group by grouping sets(dept_code,job_code);
    
select dept_code,
    null job_code,
    count(*)
    from employee
    group by dept_code
union all
select null dept_code,
    job_code,
    count(*)
    from employee
    group by job_code;
    
-- 성별, 부서별, 직급별 급여평균을 구하시오
select decode(substr(8,1,1),'1','남','3','남','여'),
    dept_code,
    job_code,
    trunc(avg(salary))
  from employee
  group by grouping sets(
    decode(substr(8,1,1),'1','남','3','남','여'),
    dept_code,
    job_code
  )

SubQuery

하나의 SQL문안에 포함된 또 다른 SQL문
메인쿼리에 종속된 서브쿼리가 존재함
존재하지 않는 조건에 근거한 값들을 검색 할 때

서브쿼리는 반드시 소괄호로 묶을 것
서브쿼리는 연산자의 오른쪽 위치 할 것.


xxxxxxxxxx
--노옹철사원의 관리자이름을 출력하라.
-- 셀프조인
select E.emp_name
  from employee E left join employee M
  on E.manager_id = m.emp_id
  where e.emp_name = '노옹철';
  
-- 서브쿼리
select emp_name
    from employee
    where emp_id = (select manager_id
                        from employee
                        where emp_name ='노옹철');


xxxxxxxxxx
--전직원의 급여 평균보다 많은 급여를 받고 사원의 사번,이름,직급코드,급여를 출력
--1. 급여 평균
--2. 급여평균보다 많은 급열 받는 사원 필터링
select emp_id as "사번",
    emp_name as "이름",
    sal_level as "직급코드",
    salary as "월급"
    from employee
    where salary >= (select avg(salary)
                            from employee);

서브쿼리의 유형

단일행 서브쿼리(단일컬럼)
다중행 서브쿼리(단일컬럼)
다중열 서브쿼리(단일행)
다중행 다중열 서브쿼리
상관 서브쿼리
스칼라 서브쿼리
인라인 뷰

1.단일행 서브쿼리


xxxxxxxxxx
-- 1.윤은혜사원과 급여가 같은 사원의 사번,사원명,급여 출력
select emp_id 사번,
    emp_name 사원명,
    salary 급여
    from employee
    where emp_name != '윤은해' 
    and salary = (select salary
                     from employee
                     where emp_name = '윤은해');
                     
-- 2.기본급여가 가장 많은 사람과 제일 적은 사람을 출력
select *
    from employee
    where salary = (select max(salary)
                            from employee)
    or salary = (select min(salary)
                        from employee);
                    
-- 3. D1, D2부서에 근무하는 사원중에 급여가 D5부서의 평균급여보다 많은 사원을 조회
select emp_id 사번,
    emp_name 사원명,
    dept_code 부서코드,
    salary 급여
    from employee
    where dept_code in ('D1','D2')
    and salary > (select avg(salary)
                        from employee
                        where dept_code = 'D5');

2. 다중행 서브쿼리

IN, NOT IN, ANY , ALL , EXISTS


xxxxxxxxxx
--in
--송종기, 하이유가 속한 부서의 사원정보 출력
select dept_code
    from employee
    where emp_name in ('송종기','하이유');
    
select emp_name, dept_code
    from employee
    where dept_code in('D5','D9');
    
-- 서브쿼리로 합쳐 보리기
select emp_name,dept_code
    from employee
    where dept_code in (
        select dept_code
             from employee
             where emp_name in ('송종기','하이유')
    );
    
-- not in
-- 송종기와 하이유가 근무하지 않는 부서를 다니는 닝겐들
select emp_name , dept_code
    from employee
    where dept_code not in (select dept_code
                                from employee
                                where emp_name in('송중기','하이유'));
                                
--any(some)
--서브쿼리의 결과 중에서 하나라도 참이면 참
/*
x > any(..) : any절의 어떤 값보다도 크기만 하면 참, 최소값보다 크기만 하면 참
x < any(..) : any절의 어떤 값보다도 작기만 하면 참, 최대값보다 작기만 하면 참
x = any(..) : any절의 어떤 값과 일치하기만 하면 참, in 과 동일
x != any(..) : any절의 하나 이상의 값과 일치하지 않으면 참
*/
select emp_name, salary
    from employee
    where salary > any(2000000,5000000);
    
-- all
-- 서브쿼르이 결과 중에서 모두 참이면 참.
/*
x> all(..) 모든 값보다 크면 참, 최대값 보다 크면 참
x > all(..) 모든 값보다 작으면 참, 최소값 보다 작으면 참
x = all(..) 모든 값과 같으면 참, 보통 false처리
x != all(..) 모든 값과 다르면 참, not in하고 동일함
*/
select emp_name , salary
    from employee
    where salary > all(2000000,5000000,3000000); -- 50000000이랑만 비교하게 됨
    
select emp_name , salary
    from employee
    where salary < all(2000000,5000000,3000000); -- 20000000이랑만 비교하게 됨
    
-- D2부서의 모든 사원보다 적은 급여를 받는 사원 조회
select *
    from employee
    where salary < all(select salary 
                            from employee
                            where dept_code ='D2');
                            
--부서별 평균급여중 가장 높은 부서의 부서코드, 평균급여를 조회
--all
select dept_code,
    trunc(avg(salary))
    from employee
    group by dept_code
    having avg(salary) >= all(select trunc(avg(salary))
                                    from employee
                                    group by dept_code); --최대값과 비교해서 같거나 크면 되는데, 최대값과 똑같은 결과를 원할 떄 씀
                                      
select trunc(avg(salary))
    from employee
    group by dept_code;
    
--exist
--서브쿼리의 결과 중에서 만족하는 행이 하나라도 존재하면 참
select *
  from employee
  where 1=1; --무적권 참
  
select *
  from employee
  where 1=2; -- 절대 거짓
  
--행을 검사 할 때 서브쿼리의 결과셋에 행이 존재하면 참 => 메인쿼리의 해당행을 resultset에 포함시킴
--메인쿼리행을 검사할때 서브쿼리의 결과셋에 행이 존재하지 않으면 거짓
-- 메인쿼리의 해당행을 resulltset에 포함시키지 않음
select *
  from employee
  where exists(select emp_name from employee where dept_code='D5');
  
--부하직원이 한명이라도 있는 직원을 출력
select emp_id, emp_name
  from employee E
  where exists(select emp_name
          from employee
          where manager_id = E.emp_id
  );
  
--not exists를 이용해서 최대/최소값 구하기
--가장 많은 급여를 받는 사원을 조회
--행이 존재하면 거짓, 행이 존재하지 않으면 참
select emp_id, salary
  from employee E
  where not exists(
            select 1
              from employee
              where salary > E.salary --이 부분에서는 계속 돈다. 메인쿼리 salary받아와서 한바퀴 쭈욱 받고
  );
  
select emp_id, salary
  from employee E
  where not exists(
            select 1
              from employee
              where salary < E.salary
  );

실습문제


xxxxxxxxxx
--1. 2011년 1월1일 이전 입사자 중에서
--2011년 1월 1일 이후의 어떤 입사자보다 급여를 적게 받는 사원 조회
--사원명,입사일,급여 출력
select emp_name 사원명,
    hire_date 입사일,
    salary 급여
    from employee
    where extract(year from hire_date) < 2011
    and salary < all(select salary
                        from employee
                        where extract(year from hire_date) >= 2011);
    order by 2;
--2. 어떤 'J4'직급의 사원보다도 많은 급여를 받은 직급이 'J5,J6,J7'인 사원 출력
--사원명,직급코드,급여
select emp_name 사원명,
    job_code 직급코드,
    salary 급여
    from employee
    where job_cOde in ('J5','J6','J7')
    and salary > all(select salary
                    from employee
                    where job_code = 'J4');
    order by 3;
--3. 직급이 대표,부사장이 아닌 모든 사원 출력(메인쿼리 조인하지 말것)
--부서코드 , 사원명
select *
    from employee
    where job_code != all(select job_code
                                from job
                                where job_name in ('대표','부사장'))
    order by job_code;
--4.Asia1지역에 근무하는 사원정보 출력(메인쿼리 조인하지 말것)
--부서코드 , 사원명
select dept_code,
    emp_name
    from employee
    where dept_code = any(
                            select dept_id
                                 from department
                                 where location_id = (select local_code
                                 from location
                                 where local_name = 'ASIA1')
    );
    
    
select dept_id
  from department E join location L
    on E.location_id = L.local_code;

3. 다중열 서브쿼리

하나의 컬럼이 아닌 여러 컬럼


xxxxxxxxxx
--퇴사한 여직원
--퇴사한 여직원 같은 부서, 같은 직급에 해당하는 사원을 조회
select emp_name,
    dept_code,
    job_code
    from employee
    where (dept_code, job_code) in (select dept_code, job_code
                                        from employee
                                         where quit_yn = 'y');
                                         
--이때, 서브쿼리 결과가 단일행이든, 다중행이든 처리방식은 같다.
--직급별 최소급여를 받는 직원의 사번, 이름, 직급, 급여를 조회
select job_code,
    min(salary)
    from employee
    group by job_code;
select emp_id,
    emp_name,
    job_code,
    salary
    from employee
    where(job_code,salary) in (
                                select job_code,
                                    min(salary)
                                    from employee
                                    group by job_code
    );

상관서브쿼리

상호연관 서브쿼리
메인쿼리의 값을 서브쿼리에 전달하고, 서브쿼리를 수행한 다음, 그 결과를 다시 메인쿼리로 리턴


xxxxxxxxxx
--직급별 평균급여보다 많은 급여를 받는 사원 출력
select *
  from employee E
  where salary > (select avg(salary)
              from employee
              where E.job_code = 'J2');
--직급별 평균급여보다 많은 급여를 받는 사원 출력
select emp_name, job_code, salary
  from employee E
  where salary > (select avg(salary)
              from employee
              where job_code = E.job_code)
    order by 2;

스칼라 서브쿼리

결과값이 1개(단일행, 단일컬럼)인 상관서브쿼리
select : 주로 select절 스칼라서브쿼리를 일컫음
where
order by


xxxxxxxxxx
--1. select
--모든 사원의 사번,이름,관리자 사번,관리자명 조회
select emp_id,
  emp_name,
  manager_id,
  ( select emp_name
      from employee
      where emp_id = E.manager_id)
  from employee E;
  
--2. where절
--사원이 속한 직급평균보다 많은 급여를 받은 사원 조회
select emp_id,emp_name,salary
  from employee E
  where salary > (select avg(salary)
                        from employee
                        where job_code = E.job_code);
  
--3. order by
-- 모든 직원의 사번, 이름, 부서코드를 부서명으로 조회
select emp_id, emp_name, dept_code
  from employee E
  order by (select dept_title
            from department
            where E.dept_code = dept_id);
  
select dept_title
  from deaprtment;

인라인 뷰

실제테이블에 근거한 논리적 가상테이블(result set)
inline view : 1회용
stored view : db에 저장해서 영구적 사용 가능
view를 사용하면 복잡한 쿼리문을 가독성 좋게 만들 있음


xxxxxxxxxx
--여사원의 사번, 사원명, 부서코드, 성별을 출력
select emp_id,emp_no,emp_name,dept_code
    from(
        select emp_id,
            emp_no,
            emp_name,
            dept_code,
            decode(substr(emp_no,8,1),'1','남','3','남','여') 성별
            from employee)
    where 성별 = '여';

실습문제


xxxxxxxxxx
--1. 1990년대 입사한 사원을 조회
--사번,사원명,입사년도
select 사번,사원명,입사년도
    from (
        select emp_id 사번,
    emp_name 사원명,
    extract(year from hire_date) 입사년도
    from employee
    order by 3)
    where 입사년도 >= 1900 and 입사년도 < 2000;
    
--2. 사원명, 부서명, 급여, 부서별 평균임금을 스칼라서브쿼리를 이용해서 출력
select emp_name 사원명,
    (
        select dept_title
            from department
            where dept_id = E.dept_code
    ) 부서명,
    salary 급여,
    (
        select trunc(avg(salary))
            from employee
            where dept_code = E.dept_code
    ) 평균임금
    from employee E;
    
--3 직급이 J1을 제외하고, 부서를 지정받은 사원중에서
--자신의 부서별 평균급여보다 적은 급여를 받는 사원출력
--부서코드, 사원명, 급여, 부서별 급여평균
select dept_code 부서코드,
    emp_name 사원명,
    salary 급여,
    (
        select trunc(avg(salary))
            from employee
            where dept_code = E.dept_code
    ) 평균임금
    from employee E
    where salary > (
                    select avg(salary)
                        from employee
                        where dept_code = E.dept_code
                    )
    and dept_code <> 'J1'
    order by 부서코드;
    
--4. employee테이블에서 사원중 30대, 40대인 여자사원의 사번, 부서명, 성별, 나이를 출력 (인라인 뷰 사용)
select 사번,
    nvl(부서명,'인턴'),
    성별,
    나이,
    emp_name
    from (
            select E.emp_id 사번,
                (
                    select dept_title
                        from department
                        where dept_id = E.dept_code
                ) as 부서명,
                decode(substr(emp_no,8,1),'1','남','3','남','여') 성별,
                extract(year from sysdate) - extract(year from to_date(substr(emp_no,1,2),'rr'))+1 나이,
                emp_name
                from employee E
    )
    where 나이 >= 30 and 나이 < 50
    and 성별 = '여';

DML

Data Manipulation Language
CRUD - 데이터를 이용하려는 사용자와 Database서버 사이의
인터페이스를 직접적으로 제공하는 언어
C : insert into
R : select ~ from
U : update set
D : delete from

insert

테이블에 새로운 행 을 추가


xxxxxxxxxx
insert into 테이블명 values(col값1, col값2,...);
insert into 테이블명(col1명 , col2명,...) values(col1값, col2값,...);
테스트용 테이블 emp_test1 생성 - employee의 테이블을 쫘악 가져옴, 복붙(서브쿼리 이용)
create table emp_test1 as
  select * from employee;
  
-- 데이터 추가 (컬럼명 없이 추가)
insert into emp_test1
  values(301,'장채현','920727-1231231','eunchan2000@gmail.com','01068685797','D0','J0','S0',10000000,0.5 , 200,sysdate,default,default );
  
-- 데이터 추가 (컬럼명 지정 후 추가) 
insert into emp_test1 
                 (emp_id , emp_name , emp_no , 
                 email , phone , dept_code , 
                 job_code, sal_level , salary , 
                 bonus , manager_id , hire_date , 
                 quit_date , quit_yn)
                 values(302,'함지민','940321-2123123','ikso2000@naver.com',
                     '01012341234','D0','J0','S0',
                     43000000,0.2,'200',SYSDATE,default,default);
-- 컬럼명은 생략 가능 , 단 nullabe , default값이 지정된 한해서 생략 가능
-- 컬럼지정순서도 자유롭다, 하지만 반드시 values절과 반드시 일치헤야 함
insert into emp_test1 
                 (emp_id , emp_name , emp_no , 
                 email , phone , dept_code , 
                 job_code, sal_level , salary , 
                 bonus , manager_id)
                 values(302,'잡채현','911212-1231111','ikarus@naver.com',
                     '01043214321','D0','J0','S0',
                     4300000,0.1,'200');
-- 서브쿼리를 이용한 insert into
create table emp_test2(
 emp_id number,
 emp_name varchar2(30),
 dept_title varchar2(20)
);
select emp_id,
 emp_name,
 dept_title
 from employee left join department
 on dept_code = dept_id;
 
-- 서브쿼리 대입
insert into emp_test2(
 select emp_id,
 emp_name,
 dept_title
 from employee left join department
 on dept_code = dept_id
);
-- 데이터 사전 보기
select *
 from user_tab_cols
 where table_name = 'EMP_TEST1';

update

테이블에 이미 기록된 행에 대해서 컬럼의 값을 수정

작업 후 행의 개수에는 변화가 없다


xxxxxxxxxx
-- 다른 테이블 복사해보리기
create table dept_copy as
  select * from department;
  
-- D9의 부서명을 총무부에서 전략기획팀으로 변경
-- where 조건절을 반드시 명시할 것
update dept_copy set dept_title = '전략기획팀'
  where dept_id = 'D9';
  
-- 서브쿼리를 이용한 update
-- 방명수사원의 급여와 보너스율을 유재식사원과 동일하게 변경
create table emp_salary as
  select emp_id,
    emp_name,
    dept_code,
    job_code,
    salary,
    bonus
    from employee;
    
select emp_name,
  salary,
  bonus
  from emp_salary
  where emp_name in ('유재식','방명수');
  
update emp_salary set salary = 3400000, bonus = 0.2
  where emp_name = '방명수';
rollback;
-- 서브쿼리
update emp_salary
  set salary = (
    select salary from emp_salary where emp_name = '유재식'
  ),
  bonus = (
     select bonus from emp_salary where emp_name = '유재식'    
  )
  where emp_name = '방명수';
  
-- 서브쿼리2
update emp_salary
  set (salary, bonus) = (
              select salary , bonus from emp_salary where emp_name = '유재식'
  )
  where emp_name = '방명수';

delete

테이블의 행을 삭제

where절을 반드시 사용 , 안할 시 테이블 내용 다 삭제


xxxxxxxxxx
select * from emp_test1;
-- emp_id  300번대 친구들 날려 보리기
delete from emp_test1 where emp_name >= 300;
-- 외래키 (다른 테이블에 참조 시, 기본 옵션으로는 삭제 불가)
delete from department where dept_id = 'D2';
--ORA-02292: integrity constraint (KH.FK_EMPLOYEE_DEPARTMENT) violated - child record found
--truncate로 테이블 행삭제하기
--DML이 아닌 DDL이다. 그러므로 자동커밋되므로 사용시 주의
select * from dept_copy;
delete from dept_copy; --DML은 작업시 before image를 생성 , 그래서 롤백이 가능
rollback;
-- 대량의 데이터를 삭제 할 땐, before image를 만들면 느리기 때문에, 때에 따라서 사용함
truncate table dept_copy; -- DDL은 before image를 생성하지 않기 때문에 , 롤백 불가능
-- DML / DDL 혼용시 주의할 점
-- DML은 commit / rollback (TCL) 사용가능
-- DDL은 autocommit되므로 TCL 사용불가
create table tbl_test(
  id varchar2(20)
);
insert into tbl_test values('Hello');
insert into tbl_test values('world');
-- 현재 데이터를 추가했고, commit하지 않았음
create table tbl_other(
  no number
);
-- 의도대로 데이터 롤백되지 않는다
-- DDL을 사용했으므로, 자동 커밋 됨
rollback;
delete from tbl_test where id = 'world';
select * from tbl_test;
rollback;
-- 드랍을 날렸을 경우는 rollback 불가
drop table tbl_other;

DDL

Data Definition Language 데이터 정의 언어
데이터베이스객체에 대해서 , 생성 create , 수정 alter , 삭제 drop하는 명령어 모음
rollback / commit 등의 tcl사용이 불가 - 자동 커밋(auto commit)

데이터베이스 객체
table
user
sequence : numbering을 담당
index : 컬럼에 인덱스를 넣어, 조회할 때 속도향상 B Tree + 가 기본
view : 가상테이블
package
procedure
function
trigger
synonym

create


xxxxxxxxxx
create table member(
  member_id varchar2(20),
  member_pwd varchar2(20),
  member_name varchar2(20)
);
-- 주석
-- 테이블 / 컬럼 등에 주석을 다는 습관 !
-- DD : dba_ , all_, user_   객체에 대한 메타정보를 가지고 있는 테이블
select * 
  from user_tab_comments
  where table_name = 'EMPLOYEE';
select *
  from user_col_comments
  where table_name = 'EMPLOYEE'; 
  
-- 1. 테이블 주석 달기
comment on table member is '회원관리테이블';
select * 
  from user_tab_comments
  where table_name = 'MEMBER';
  
-- 2. 컬럼 주석 달기
comment on column member.member_id is '회원아이디';
comment on column member.member_pwd is '회원비번';
comment on column member.member_name is '회원이름';
select *
  from user_col_comments
  where table_name = 'MEMBER';
  
-- 수정 / 삭제는 동일한 명령어
comment on column member.member_id is '';
comment on column member.member_pwd is '';
comment on column member.member_name is '';
-- ##제약조건
-- 데이터무결성을 지키기 위해 컬럼단위로 제한된 조건
-- 무결성(intergrity) : 데이터의 정합성, 일관성이 유지 되는 것.
/*
  1. NOT NULL : 데이터에 NULL을 허용하지 않는 것 => 필수항목(컬럼)
  2. UNIQUE : 중복된 값을 허용하지 않음 => 주민등록번호, 이메일
  3. PRIMARY KEY : 레코드의 고유식별자로 사용. unique + not null => 아이디
  4. FOREIGN KEY : 두 테이블간 데이터를 연결하여 설정 함
          외래키테이블에 올 수 있는 데이터를 제한함.
  5. CHECK : 저장가능한 데이터의 값의 조건, 범위를 설정 => 성별, 기혼여부(0,1), 삭제 여부
  제약조건 정보 확인
  user_constraints
  user_cons_columns
  
  select *
    from user_constraints
    where table_name = 'EMPLOYEE';
  select *
    from user_cons_columns
    where table_name = 'EMPLOYEE';
    
  제약조건 조회
  select *
    from user_constraints UC join user_cons_columns UUC using(constraint_name)
    where UC.table_name = 'EMPLOYEE'; 
*/

1. NOT NULL

필수항목인 컬럼에 추가하는 제약조건


xxxxxxxxxx
-- 제약조건이 없는 막장테이블
create table user_nocons(
  user_no number,
  user_id varchar2(20),
  user_pwd varchar2(20),
  user_name varchar2(30),
  gender char(3),
  phone char(13),
  email varchar2(50)
);
-- 데이터 추가
insert into user_nocons
  values(1,'user01','p1234','홍길동','남','010-1234-1234','test@naver.com');
    
insert into user_nocons
    values(2,null,null,null,null,'010-3214-3214','tttt@naver.com');
    
select * from user_nocons;
-- 제약조건이 있는 테이블
create table user_notnull(
  user_no number not null,
  user_id varchar2(20) not null,
  user_pwd varchar2(20) not null,
  user_name varchar2(30) not null,
  gender char(3),
  phone char(13),
  email varchar2(50)
);
-- 제약 조건이 있는 테이블에 , 제약조건 이름을 내가 붙여주기
create table user_notnull(
  user_no number constraint nn_user_notnull_user_no not null, --컬럼 레벨에서 작성 했다
  user_id varchar2(20) constraint nn_user_notnull_user_id not null,
  user_pwd varchar2(20) constraint nn_user_notnull_user_pwd not null,
  user_name varchar2(30) constraint nn_user_notnull_user_name not null,
  gender char(3) default '여',
  phone char(13),
  email varchar2(50)
); -- 이쪽에서 제약조건을 줄수도 있음 ㅇㅈ? ㅇ ㅇㅈ
-- 데이터 추가
insert into user_notnull
  values(1,'user01','p1234','홍길동','남','010-1234-1234','test@naver.com');
-- 데이터 추가
insert into user_notnull
  values(1,'user01','p1234','홍길순',default,'010-1234-1234','test@naver.com');
-- ORA-01400: cannot insert NULL into ("KH"."USER_NOTNULL"."USER_ID")
insert into user_notnull
    values(2,null,null,null,null,'010-3214-3214','tttt@naver.com');
    
select * from user_notnull;

2. Unique

컬럼 입력값에 대해서 중복을 제한하는 제약조건


xxxxxxxxxx
create table user_unique(
  user_no number not null,
  user_id varchar2(20) constraint uq_user_unique_id unique, -- 컬럼레벨에서 제약조건 작성
  user_pwd varchar2(20) not null,
  user_name varchar2(30) not null,
  gender char(3) default '남',
  phone char(13),
  email varchar2(50)
);
-- 같은 결과
create table user_unique(
  user_no number not null,
  user_id varchar2(20),
  user_pwd varchar2(20) not null,
  user_name varchar2(30) not null,
  gender char(3) default '남',
  phone char(13),
  email varchar2(50),
    constraint uq_user_unique_user_id unique(user_id)
);
-- 데이터 삽입
insert into user_unique
  values(1,'user01','p1234','홍길동','남','010-1234-1234','test@naver.com');
-- 같은 데이터를 삽입시
-- ORA-00001: unique constraint (KH.UQ_USER_UNIQUE_USER_ID) violated
insert into user_unique
  values(1,'user01','p1234','홍길동','남','010-1234-1234','test@naver.com');
    
--unique제약조건이 걸린 컬럼에 null데이터 추가
-- mssql은 null데이터 입력 불가
-- mysql 과 oracle은 null데이터 입력 가능이라 ,not null이라는 옵션을 줘야함
create table user_unique(
  user_no number not null,
  user_id varchar2(20) not null,
  user_pwd varchar2(20) not null,
  user_name varchar2(30) not null,
  gender char(3) default '남',
  phone char(13),
  email varchar2(50),
    constraint uq_user_unique_user_id unique(user_id)
);

3. primary key

not null + unique 기능을 하지만, 테이블당 한개만 선언가능하다
테이블에서 한행의 정보를 구분하기 위한 고유식별자(identifier)역할을 하기 위해


xxxxxxxxxx
create table user_primarykey(
  user_no number not null,
  user_id varchar2(20) constraint pk_user_primarykey_user_id primary key,
  user_pwd varchar2(20) not null,
  user_name varchar2(30) not null,
  gender char(3) default '남',
  phone char(13),
  email varchar2(50),
    constraint uq_user_unique_user_id unique(user_id)
);
-- or
create table user_primarykey(
  user_no number not null,
  user_id varchar2(20),
  user_pwd varchar2(20) not null,
  user_name varchar2(30) not null,
  gender char(3) default '남',
  phone char(13),
  email varchar2(50),
    constraint uq_user_unique_user_id unique(user_id),
    constraint pk_user_primarykey_user_id primary key(user_id)
);
-- primary key는 단일컬럼에 대해서 부여하는 simgle primary key
-- 여러 컬럼을 묶어서 부여하는 composite primary key
-- 주문 테이블 : 상품번호, 주문자아이디, 주문일자, 주문수량
create table tbl_order_composite_pl(
  product_no varchar2(20),
  user_id varchar2(20),
  order_date date,
  order_num number, -- 주문수량
  constraint pk_tbl_order primary key(product_no , user_id, order_date)
);
-- 데이터 추가
insert into tbl_order_composite_pk
    values ('p123','u123',sysdate,10);
    
select * from tbl_order_composite_pk;

4. foreign key

결성을 유지하기 위한 제약조건
참조하는 컬럼(자식)과 참조되는 컬럼(부모)간의 부모자식테이블관계를 형성


xxxxxxxxxx
--shop_member
--shop_buy : shop_member.user_id 컬럼값을 참조
create table shop_member(
    user_no number,
    user_id varchar2(20),
    user_pwd varchar2(30) not null,
    user_name varchar2(30),
    gender varchar2(10) default '남',
    phone varchar2(30),
    email varchar2(50),
    constraint user_no_uq unique(user_no),
    constraint user_id_pk primary key(user_id)
);
-- 데이터 삽입
insert into shop_member
  values(1,'user01','p1234','홍길동','남','010-1234-1234','test1@naver.com');
insert into shop_member
  values(2,'user02','p1234','이순신','남','010-1234-1234','test2@naver.com');
insert into shop_member
  values(3,'user03','p1234','장보고','남','010-1234-1234','test3@naver.com');
insert into shop_member
  values(4,'user04','p1234','정지수','남','010-1234-1234','test4@naver.com');
insert into shop_member
  values(5,'user05','p1234','박광준','남','010-1234-1234','test5@naver.com');
  
  
-- 회원이 물품을 구매했을 때 기록하는 테이블
-- shop_member.user_id 를 참조하는 외래키를 설정
-- 1. 컬럼 레벨 작성
create table shop_buy(
    buy_no number constraint pk_shop_buy_no primary key,
    user_id varchar2(20) constraint fk_shop_buy_user_id
                        references shop_member(user_id),
    product_name varchar2(20),
    reg_date date
);
-- 2. 테이블 레벨 작성
create table shop_buy(
    buy_no number constraint pk_shop_buy_no primary key,
    user_id varchar2(20),
    product_name varchar2(20),
    reg_date date,
    constraint fk_shop_buy_user_id foreign key(user_id)
                                   references shop_member(user_id)
);
insert into shop_buy values(1,'user01','축구화',sysdate);
insert into shop_buy values(2,'user01','축구화',sysdate);
insert into shop_buy values(3,'user01','축구화',sysdate);
-- ORA-02291: integrity constraint (KH.FK_SHOP_BUY_USER_ID) violated - parent key not found
insert into shop_buy values(4,'kkkk','축구화',sysdate);
insert into shop_buy values(5,null,'축구화',sysdate); -- fk컬럼에 null값은 추가가능
select * from shop_member;
select * from shop_buy;
delete from shop_member
  where user_id = 'user03';
-- 참조되고 있는 데이터는 삭제 불가능
-- ORA-02292: integrity constraint (KH.FK_SHOP_BUY_USER_ID) violated - child record found
-- 자식테이블데이터 -> 부모테이블데이터 순으로 삭제
drop table shop_member;
-- ORA-02292: integrity constraint (KH.FK_SHOP_BUY_USER_ID) violated - child record found
-- 자식 테이블 삭제 -> 부모테이블 삭제 순으로 삭제
-- 외래키 삭제 옵션
-- on delete restricted : 기본값
-- on delete set null : 부모데이터가 삭제된 자식데이터를 null로 변경
-- on delete cascade : 부모데이터가 삭제되면 자식데이터도 삭제
create table shop_buy_set_null(
  buy_no number constraint pk_shop_buy_set_null_no primary key,
    user_id varchar2(20),
    product_name varchar2(20),
    reg_date date,
    constraint fk_shop_buy_set_null_user_id foreign key(user_id)
                                   references shop_member(user_id)
                                   on delete set null
);
insert into shop_buy_set_null values(1,'user01','축구화',sysdate);
insert into shop_buy_set_null values(2,'user02','농구화',sysdate);
-- 부모데이터 삭제시도
delete from shop_member
    where user_id = 'user02';
    
select * from shop_buy_set_null;

5. check

컬럼 도메인의 값을 제한


xxxxxxxxxx
-- 컬럼레벨
create table user_check(
  user_no number not null,
  user_id varchar2(20),
  user_pwd varchar2(20) not null,
  user_name varchar2(30) not null,
  gender char(3) default '남' constraint ck_user_check_gender
                check(gender in('남','여')),
  phone char(13),
  email varchar2(50),
    constraint pk_user_check_user_id primary key(user_id)
);
-- 테이블 레벨
create table user_check(
  user_no number not null,
  user_id varchar2(20),
  user_pwd varchar2(20) not null,
  user_name varchar2(30) not null,
  gender char(3) default '남' not null, -- default절 이후에 not null추가
  phone char(13),
  email varchar2(50),
    constraint pk_user_check_user_id primary key(user_id),
    constraint ck_user_check_gender check(gender in ('남','여'))
);

--subquery를 이용한 create table --employee테이블의 특정컬럼 emp_id,emp_name,salary,dept_title,job_name --의 결과셋을 특정테이블로 생성하고 싶다면..

create table emp_copy as select emp_id,emp_name,salary,dept_title,job_name from employee left join department on dept_code = dept_id left join job using (job_code);
select * from emp_copy;

--제약조건 조회 --create table [table명] as로 생성된 테이블에는 not null제약조건만 복사. --생성된 테이블에 제약조건 추가는 alter문을 통해서 사용해야함. select constraint_name,UC.owner,UC.table_name,UC.constraint_type, UC.search_condition from user_constraints UC join user_cons_columns UCC using (constraint_name) where UC.table_name = 'EMP_COPY';

select constraint_name,UC.owner,UC.table_name,UC.constraint_type, UC.search_condition from user_constraints UC join user_cons_columns UCC using (constraint_name) where UC.table_name = 'EMPLOYEE';

Alter


xxxxxxxxxx
--테이블등 데이터베이스객체의 정보를 수정할 때 사용.
--1. 컬럼/제약조건 : add,modify,rename,drop
--2. 테이블 : rename
--연습용테이블생성
create table tbl_alter(
    user_no number primary key,
    user_id varchar2(20),
    user_pwd char(20)
);
desc tbl_alter;
--####1. 컬럼/제약조건
--1. add 컬럼
alter table tbl_alter add user_name varchar2(20);
desc tbl_alter;
--컬럼 default값 추가
alter table tbl_alter add user_age number default 0;
desc tbl_alter;
--제약조건 추가
alter table tbl_alter add user_gender char(3) default '남'
                    check(user_gender in ('남','여'));
                  
--2. add 제약조건
alter table tbl_alter add constraint uq_tbl_alter_user_id unique(user_id);
--not null 제약조건은 추가가 아니라 변경으로 해줘야함
--최초의 상태가 nullable 이기 때문에 => not null로 바꿔줘야함
alter table tbl_alter modify user_pwd not null;
desc tbl_alter;
--3. modify 컬럼
--데이터 타입, default값 변경
alter table tbl_alter modify user_name char(10);
alter table tbl_alter modify user_age default 20;
--4. modify 제약조건
--이런건 없음, 제약좋건 삭제후 다시 생성할 것, (제약조건의 이름을 변경하는 것은 가능)
--5. rename 컬럼
alter table tbl_alter rename column user_pwd to password;
--6. rename 제약조건명
alter table tbl_alter rename constraint SYS_C0010263 to pk_tbl_alter;
--7. drop 컬럼
alter table tbl_alter drop column user_age;
--8. drop 제약조건
alter table tbl_alter drop constraint SYS_C0010266;
-- add 컬럼/제약조건 둘다 가능
-- modify 컬럼만 가능
-- rename 컬럼/제약조건 둘다 가능
-- drop 컬럼/제약조건 둘다 가능
--####2. 테이블
--테이블 명만 변경가능
alter table tbl_alter rename to tbl_alter_alter;
select * from tab; --user_tables (bin 안에 있는놈들은 지워진 애들)
-- 간단 버전
rename tbl_alter_alter to tbl_alter

drop

데이터베이스 객체 삭제시 사용


xxxxxxxxxx
--제약조건 조회
select constraint_name,UC.owner,UC.table_name,UC.constraint_type, UC.search_condition
    from user_constraints  UC join user_cons_columns UCC using (constraint_name) 
    where UC.table_name = 'TBL_ALTER_CHILD';
    
--자식테이블에서 외래키로써, 부모테이블의 기본키를 참조하고 있을 때,
--부모테이블을 삭제 할 수 있을 까?
create table tbl_alter_child(
  parent_no number references tbl_alter(user_no)
);
--부모 테이블 삭제
drop table tbl_alter;
----ORA-02449: unique/primary keys in table referenced by foreign keys
--부모테이블 강제삭제 옵션 : cascade constraint
drop table tbl_alter cascade constraint;

DCL

Data Control Language

grant(권한부여), revoke(권한회수), commit(실행), rollback(복구)


xxxxxxxxxx
-- 1. grant
-- grant [System_Privilege | role] to [user | role | PUBLIC] [WITH ADMIN OPTION]
-- System_Privilege : 권한 create session(접속할수 있는 권한), create table,...
-- role : 권한 묶음 connect, resource, dba
-- PUBLIC : 관리자가 모든 사용자가 사용할 수 있도록 부용함
-- WITH ADMIN OPTION : 권한을 부여받은 사용자도 이 권한을 다른 사용자에게 부여 가능
-- qwerty 사용자를 생성해서 관리
-- 관리자계정에서 
create user qwerty identified by qwerty
  default tablespace users;
 
select * from dba_users;
-- 접속권한 주기
grant create session to qwerty;
grant connect to qwerty;
-- 테이블 만드는 권한 주기 => create table
-- resource라는 role부여 : create table 등등, 다른 권한들 묶음
grant resource to qwerty;
-- 테이블스페이스 users에 대한 권한을 주어야함
alter user qwerty default tablespace users quota unlimited on users;
--(관리자계정) 사용자에게 부여된 권한/role 조회
select * from dba_sys_privs
 where grantee = 'QWERTY';
 
select * from dba_role_privs
 where grantee = 'QWERTY';
-- 일반사용자가 본인의 권한/role조회
select * from user_sys_privs;
 
select * from user_role_privs;
-- role에 포함된 권한 보기 (관리자에서 가능)
select *
 from dba_sys_privs 
 where grantee in ('CONNECT','RESOURCE');
-- 특정테이블에 대한 권한 부여
-- kh계정의 테이블에 대한 권한을 kh가 qwerty에게 부여
create table tbl_coffee(
 pname varchar2(20) primary key,
 price number not null,
 company varchar2(20) not null
);
insert into tbl_coffee values('맥심커피', 30000 , '동서식품');
insert into tbl_coffee values('카누커피', 20000 , '동남식품');
insert into tbl_coffee values('캔커피', 10000 , '동북식품');
commit;
select * from tbl_coffee;
--tbl_coffee을 qwerty가 볼 수 있도록 권한 주기
grant select on kh.tbl_coffee to qwerty;
--qwerty에서 이방식으로 보면됨 (select만 가능)
select * from kh.tbl_coffee;
--qwerty에게 insert , update, delete , select 권한 주는 2가지 방식
grant insert, update, delete on kh.tbl_coffee to qwerty;
grant all on kh.tbl_coffee to qwerty; --select , insert, update, delete
--qwerty에서 insert 하는 방법
insert into kh.tbl_coffee values('맥심잡지',40000,'어후');
select * from tbl_coffee;

2. revoke

권한 회수해보리기


xxxxxxxxxx
revoke insert on kh.tbl_coffee from qwerty;
revoke all on kh.tbl_coffee from qwerty;

Database Object

DD - Data Dictionary

자원(데이터베이스 객체)을 효율적으로 관리하기 위해서 다양한 메타정보를 담고 있는 테이블

다양한 메타정보를 담고 있는 테이블

사용자가 DB에 직접적으로 작업하지 않음

사용자가 테이블 등에 작업을 하게되면, 자동으로 DD에 반영됨.

사용자는 조회만 가능하다


xxxxxxxxxx
select * from dictionary;
select * from dict; -- dictionary의 동의어(synonym)으로 지정
select * from tab; -- user_tables 동의어
select * from user_tables;

user_xxx : 자신의 계정이 소유한 객체 조회
all_xxx : 자신에게 부여받은 객체 조회
dba_xxx : 관리자가 조회하는 모든 객 조회

VIEW

stored view

inline view(일회용)과 다르게 저장된 가상테이블

뷰를 사용해서 특정사용자가 원본테이블에 제한적으로 접근하게 함.

view를 생성하기 위해서는 권한을 부여받아야함 , resource에는 포함 안됨


xxxxxxxxxx
select * from user_views;
grant create view to kh;
create view view_emp as
  select emp_id, emp_no, emp_name , email , phone , job_code , sal_level
  from emp_test1;
  
-- 다른유저에게 테이블에서 제한된 정보를 제공
grant select on view_emp to qwerty;
update emp_test1 set emp_name = '암지민' where emp_name = '함지민';
-- 가상 테이블이라 update한 내용이 반영 되지 않음
select * from emp_test1;
-- DD에서 조회 (서브쿼리가 저장되있는 걸 확인 할 수 있음)
select * from user_views;
--select * from view_emp 를 한다는 것은 view_emp가 가지고 있는 서브쿼리를 실행한다고 생각하면 된다.
--select emp_id, emp_no, emp_name , email , phone , job_code , sal_level
--  from emp_test1;
-- 뷰삭제
drop view view_emp;
-- 뷰특징
-- 1. 실제 존재하는 컬럼뿐아니라 가상컬럼(산술연산) 생성가능 - or replace는 만약 view가 존재하면 갱신
create or replace view view_emp_salary -- 객체가 존재하면 갱신
  as
  select emp_name 사원명,
    (salary+(salary * nvl(bonus,0))) * 12 연봉
    from emp_test1;
select * from view_emp_salary;
-- join 뷰
-- 사번, 사원명, 부서명, 직급명을 포함하는 view_emp_read 뷰객체를 작성
create or replace view view_emp_read
  as
  select emp_id 사번,
    emp_name 사원명,
    nvl(job_name,'인턴') 직급,
    nvl(dept_title,'인턴') 부서
    from employee E left join department D
    on E.dept_code = D.dept_id
    left join job J using (job_code);
    
-- view의 DML
-- 생성된 뷰에 대해서 제한적으로 DML이 가능
insert into view_emp
 values(401,'790405-1012333','강호동','kangod@kh.or.kr','01012341234','J4','S3');
 
update view_emp
 set emp_no = '810405-1012333'
 where emp_id = 401;
 
delete from view_emp where emp_id = 401;
-- view에 DML이 불가능한 경우
-- 1. 뷰에 가상컬럼이 포함된 경우, 가상컬럼을 조작할 수 없다.
-- 2. insert시 뷰에 포함되지 않은 컬럼중에 not null인 경우 , 추가 할 수 없다.
-- 3. 산술표현식으로 처리된 경우 , 연봉 -> salary , bonus (X)
-- 4. distince, group by가 포함된 경우
-- 5. join view에서 fk로 연결되지 않은 경우
-- view 옵션
-- 1. with check option
-- where절 조건에 사용된 컬럼은 수정하지 못한다
create or replace view view_emp_d5 as
  select emp_id, emp_name,salary, dept_code
  from emp_test1
  where dept_code='D5'  -- 여기에 사용된 컬럼은 변경 불가
  with check option;
  
-- 급여가 250만원 이상인 사원을 D2부서로 변경
update view_emp_d5
  set dept_code ='D2'   -- 안된다잉
  where salary >= 2500000;
  
--2. with read only
-- 이 뷰는 읽기만 가능 , 수정 불가
create or replace view view_emp_d5 as
  select emp_id, emp_name,salary, dept_code
  from emp_test1
  where dept_code='D5'
  with read only;
  
--수정불가 
update view_emp_d5
  set salary = salary + 5000000; -- 안된다잉

SEQUENCE

순차적으로 정수값을 자동으로 생성하는 객체, 채번기


xxxxxxxxxx
/*
  create sequence [시퀀스명]
  [start with 숫자]
  [increment by 숫자]
  [maxvalue 숫자 | nomaxvalue] -- 최대값 , 기본값은 nomaxvalue
  [minvalue 숫자 | nominvalue] -- 최소값 , 기본값은 nominvalue
  [cycle | nocycle] -- 순환여부 , 기본값은 nocycle
  [cache | nocache] -- 캐쉬여부 , 기본값은 nocache
*/
create table tbl_sequence(
  no number primary key,
  name varchar2(20) not null,
  age number not null
);
-- 시퀀스 객체 생성
create sequence seq_tbl_sequence_no
  start with 1
  increment by 1
  nominvalue
  nomaxvalue
  nocycle
  nocache;
 
-- 데이터 삽입
-- no : 기본키 : 중복x , 열을 유일하게 식별하는 고유 키
insert into tbl_sequence values (seq_tbl_sequence_no.nextval,'홍길동',20);
insert into tbl_sequence values (seq_tbl_sequence_no.nextval,'띠그랭',30);
insert into tbl_sequence values (seq_tbl_sequence_no.nextval,'필리멍',40);
-- dd : user_sequences
select * from user_sequences;
--last_number컬럼이 다음 nextval요청이 부여받는 번호.
--curr_val : 현재 번호
select seq_tbl_sequence_no.currval from dual;
--nextval 호출시 시퀀스 객체 1씩 증가
select seq_tbl_sequence_no.nextval from dual;
insert into tbl_sequence values(seq_tbl_sequence_no.nextval, '하춘화', 50);
select * from tbl_sequence;
--실습문제
--고객이 상품주문시 사용할 테이블 tbl_order를 생성
create table tbl_order(
 order_no number primary key,
 user_id varchar2(20),
 product_id varchar2(20),
 product_cnt number,
 order_date date default sysdate
);
--order_no 컬럼은 seq_order_no 시퀀스객체에서 채번할 것,
create sequence seq_order_no
 start with 1
 increment by 1
 nominvalue
  nomaxvalue
  nocycle
  nocache;
 
--주문 내역
--kang님이 swk 상품을 5개 주문
--gam님이 gjk 상품을 20개 주문
--ring님이 oring 상품을 30개 주문
insert into tbl_order values (seq_order_no.nextval,'gang','swk',5,sysdate);
insert into tbl_order values (seq_order_no.nextval,'gam','gjk',20,sysdate);
insert into tbl_order values (seq_order_no.nextval,'ring','oring',30,sysdate);
--order_no : kh-20181024-1000 고유번호를 가질 수 있게 작성
--고객이 상품주문시 사용할 테이블 tbl_order2를 생성
create table tbl_order2(
 order_no varchar2(20) primary key,
 user_id varchar2(20),
 product_id varchar2(20),
 product_cnt number,
 order_date date default sysdate
);
create sequence seq_order2_no
 start with 1
 increment by 1
 nominvalue
  nomaxvalue
  nocycle
  nocache;
insert into tbl_order2 (order_no, user_id, product_id , product_cnt)
 values(
     'kh-' || to_char(sysdate,'yyyymmdd') || '-' || seq_order2_no.nextval,
     'kang',
     'swk',
     5
);
select * from tbl_order2;

INDEX

색인 -> 처리속도 향상을 위해서 컬럼에 대하여 생성하는 객체
key = value
key = 해당컬럼값
value = 행이 저장된 주소값
단점 : 덱스에 대한 추가저장공간이 필요, 인덱스생성시간이 소요
데이터변경(insert / update / delete)가 자주 일어나는 컬럼에는 오버헤드가 오히려 더크다

어떤 컬럼에 인덱스를 만들어야 될까?
선택도가 좋은 컬럼을 가지고 인덱스를 만들어야 한다.
고유한 값을 많이 가지는 컬럼이 선택도가 좋다고 할 수 있다
선택도가 좋은 컬럼 : 주민번호 > 성명
선택도가 나쁜 컬럼 : 성별

효율적으로 인덱스를 사용하려면
where절에 자주 사용되는 컬럼을 인덱스를 만들것
전체데이터에서 10~15%에 해당하는 컬럼
조인시에 조건절로 사용되는 컬럼
데이터변경이 적은 컬럼
테이블의 데이터가 아주 많은 컬럼(최소 20만건)
xxxxxxxxxx
-- 인덱스 조회
-- pk와 uq제약조건이 걸린 컬럼은 자동으로 인덱스 생성
select * from user_indexes
    where table_name = 'EMPLOYEE';
    
select * from employee
    where emp_id = 200; --pk컬럼을 통한 검색
    
select * from employee
    where emp_name = '송중기';
    
-- 인덱스
create index idx_emp_name on employee(emp_name);

복합 인덱스

2개 이상의 컬럼으로 이루어진 인덱스

선행이 무엇이냐가 아주 중요하다


xxxxxxxxxx
create index idex_emp_name_dept_code on employee(emp_name.dept_code);
select * from employee
  where emp_name='박나라' and dept_code = 'D5';

PL/SQL

Oracle's Procedural Language Extension to SQL

오라클이 제공하는 절차적 언어 확장

기존 sql에 변수, 조건문, 반복처리등을 지원

종류

익명블럭 : 1회용
프로시져 : 특정작업처리를 위한 서브프로그램, 단독으로 실행하거나, 다른 프로시저를 호출해서 실행
함수 : 거의 프로시저와 유사하나 반드시 리턴값이 있다

구조

DECLEAR(선택) : 선언부

BEGIN(필수) : 실행부

EXCEPTION(선택) : 예외처리부

END;(필수)

/ -> pl/sql 선언 끝 및 실행


xxxxxxxxxx
declare
  vid number;
begin
  select emp_id
  into vid -- 변수에 대입
  from employee
  where emp_name = '&emp_name'; -- 사용자로부터 입력값 받기
  
  dbms_output.put_line('emp_id=' || vid);
exception
    when no_data_found then dbms_output.put_line('데이터가 없음');
end;
/
-- 1. 변수선언
declare
    v_empno number(4);
    v_empname varchar2(10);
    test_num number := 50;
begin
    v_empno := 1010;
    v_empname := '강호동';
    dbms_output.put_line('사번    이름');
    dbms_output.put_line('----------');
    dbms_output.put_line(v_empno || ' ' || v_empname);
    dbms_output.put_line('----------');
    dbms_output.put_line('test_num =' || test_num);
end;
/
-- 참조변수 %type
declare
    vid employee.emp_id%type;
 &nbsnbsp;  vname employee.emp_name%type;
begin
    --주민번호를 입력받아 emp_id , emp_name 조회
    
    select emp_id,
        emp_name
        into vid, vname
        from employee
        where emp_no = '&emp_no';
        
        dbms_output.put_line('사번 : ' || vid);
        dbms_output.put_line('사원명 : ' || vname);
end;
/
select * from employee;
-- 참조변수 %rowtype
declare
    vemp employee%rowtype;
begin
    select *
    into vemp
        from employee
        where emp_name = '&emp_name';
        
    dbms_output.put_line('사번 : ' || vemp.emp_id);
    dbms_output.put_line('이름 : ' || vemp.emp_name);
    dbms_output.put_line('주민 : ' || vemp.emp_no);
    dbms_output.put_line('부서코드 : ' || vemp.dept_code);
end;
/
-- 실습문제 : 사원명을 입력받아서 사번, 사원명 , 직급명을 출력
declare
    vemp_id employee.emp_id%type;
    vemp_name employee.emp_name%type;
    vjob_name job.job_name%type;
    vdept_title department.dept_title%type;
begin
    select emp_id, emp_name, job_name, dept_title
    into vemp_id, vemp_name, vjob_name, vdept_title
    from employee E 
     left join department D on E.dept_code = D.dept_id
     left join job J using(job_code)
    where emp_name = '&사원명';
    
    dbms_output.put_line('사번 : ' || vemp_id);
    dbms_output.put_line('사원명 : ' || vemp_name);
    dbms_output.put_line('직급 : ' || vjob_name);
    dbms_output.put_line('부서 : ' || vdept_title);   
end;
/
--pl/sql 에서 dml문도 실행가능
select * from member;
begin
  insert into member
  values('abc','123@','김동현');
  --적용처리까지 함께 할 것
  commit;
end;
/
--### pl/sql 조건문
--1 if ~ then ~ end if문
begin
  if '&이름' = '김동현' then
    dbms_output.put_line('김동현님, 환영합니다');
  else
    dmbs_output.put_line('엥');
  end if;
end;
/
--2. if then ~ elsif then end if
declare
  vno number;
begin
  vno := &number;
  if vno = 1 then
    dbms_output.put_line('1을 입력하셨습니다');
  elsif vno = 2 then
    dbms_output.put_line('2를 입력하셨습니다');
  else
    dbms_output.put_line('딴거 입력');
  end if;
end;
/
--3. case when then
declare
  vinput number;
begin
  vinput := &input;
  
  case vinput
    when 1 then
      dbms_output.put_line(11111);
    when 2 then
      dbms_output.put_line(22222);
    else
      dbms_output.put_line('딴걸 입력');
    end case;
end;
/
-- loop
declare
  no number := 1;
begin
  loop
    dbms_output.put_line(no);
    no := no + 1;
    
        --탈출조건문
        if no > 5 then
          exit;
        end if;
  end loop;
end;
/
declare
  no number := 1;
begin
  loop
    dbms_output.put_line(no);
    no := no + 1;
    
        --탈출조건문
        exit when no>5;
  end loop;
end;
/
--2. while loop
-- 조건식이 true인 동안만 반복문 실행
-- 별도의 exit절 필요업ㅈㅅ음
declare
    num number := 1;
begin
    while num <= 5 loop
        dbms_output.put_line(num);
        num := num + 1;
    end loop;
end;
/
--실습문제
--사용자로부터 2~9까지의 숫자를 입력받아 구구단을 출력
declare
    n number;
    i number := 0;
begin
    n := &number;
    if n between 2 and 9 then
        dbms_output.put_line(n || '단');
        while i <= 9 loop
            -- n이 짝수일 경우 건너 뜀
            i := i + 1;
            continue when mod(n , 2) = 0;
            dbms_output.put_line(n || ' X ' || i || ' = ' || n * i);
        end loop;
    end if;
end;
/
--3. for in loop
-- 별도의 증감식은 필요없음
begin
  for n in 1..5 loop
    dbms_output.put_line(n);
  end loop;
end;
/
begin
  for n in reverse 1..5 loop
    dbms_output.put_line(n);
  end loop;
end;
/

문제


xxxxxxxxxx
select max(emp_id)
    from emp_test1;
    
declare
    vemp_id emp_test1.emp_id%type;
    vemp_name emp_test1.emp_name%type;
    vemp_no emp_test1.emp_no%type;
    vemp_phone emp_test1.phone%type;
    vjob_code emp_test1.job_code%type;
    vsal_level emp_test1.sal_level%type;
    
begin
    select max(emp_id)+1
        into vemp_id
        from emp_test1;
    
    --dbms_output.put_line('이름을 입력하세요.');
    vemp_name := '&name';
    --dbms_output.put_line('주민번호를 입력하세요.');
    vemp_no := '&no';
    --dbms_output.put_line('전화번호를 입력하세요.');
    vemp_phone := '&phone';
    --dbms_output.put_line('직급코드를 입력하세요.');
    vjob_code := '&job';
    --dbms_output.put_line('급여등급을 입력하세요.'); 
    vsal_level := '&sal';
    
    insert into emp_test1(emp_id,emp_name, emp_no,job_code,sal_level) values(vemp_id,vemp_name,vemp_phone,vjob_code,vsal_level);
end;
/
select * from emp_test1;
-------------
select * from employee;
declare
    vemp employee%rowtype;
    vgrade char(1);
begin
    dbms.output.put_line('사번을 입력하세요');
    
    select *
    into vemp
        from employee
        where emp_name = '&emp_name';
        
    if vemp.salary >= 5000000 then
        vgrade := 'A';
    elsif vemp.salary >= 4000000 then
        vgrade := 'B';
    elsif vemp.salary >= 3000000 then
        vgrade := 'C';
    elsif vemp.salary >= 2000000 then
        vgrade := 'D';
    elsif vemp.salary >= 1000000 then
        vgrade := 'E';
    else
        vgrade := 'F';
    end if;
    
        dbms_output.put_line(vgrade);
end;
/

--양모자 씌우기 --리턴절에 세미클론 금지, 파라미터와 리턴절의 데이터 타입의 크기 지정 금지 ->컴파일 에러 create or replace function myfunc( P_str varchar2 ) return varchar2 is result varchar2(1000); --리턴될 변수 begin dbms_output.put_line('p_str = ' || P_str ); result := 'd' || P_str || 'b'; return result; end; / --확인 --user_functions(X) --user_procedures에서 object_type = 'FUNCTION'로 검색할 것 select * from user_procedures where object_type='FUNCTION';

--실행 --1. exec명령문 실행 var result varchar2; exec :result :=myfunc('김민우'); print result;

--2. 쿼리문에서 실행 select myfunc('&이름') 양모자 from dual;

--3. 익명블럭 begin dbms_output.put_line(myfunc('&이름')); end; /

--사번을 입력 받아서 해당 사원의 연봉을 계산해서 리턴하는 저장 함수 작성 create or replace function fn_bonus_calc( p_emp_id employee.emp_id%type ) return number is result number; v_sal employee.salary%type; v_bonus employee.bonus%type; begin select salary, nvl(bonus,0) into v_sal, v_bonus from employee where emp_id = p_emp_id;
result := trunc((v_sal+(v_salv_bonus))12); return result; end; /

select * from user_procedures where object_type = 'FUNCTION';

--실행 var annualPay number; exec :annualPay := fn_bonus_calc('&emp_id'); print annualPay;

select emp_id, emp_name, fn_bonus_calc(emp_id) 연봉 from employee;

--@실습문제 : 주민번호를 입력 받아서 성별을 리턴하는 저장함수 fn_get_gender를 생성하고 --사번, 사원명, 성별을 조회하는데 사용할 것 create or replace function fn_get_gender( p_emp_no employee.emp_no%type ) return varchar2 is result varchar2(10); gender varchar2(10); begin select case when substr(emp_no,8,1) in('1','3') then '남' else '여' end 성별 into gender
from employee where emp_no = p_emp_no;
result := gender; return result; end; / drop function fn_get_gender;

var empGender varchar2; exec :empGender := fn_get_gender('&emp_no'); print empGender;

select emp_id 사번, emp_name 사원명, fn_get_gender(emp_no) 성별 from employee where emp_no = '&emp_no';

select * from employee;

--@실습문제 : 사원번호를 받아서 직급명을 리턴하는 저장 함수 fn_get_job_name create or replace function fn_get_job_name( v_emp_id employee.emp_id%type ) return job.job_name%type is result varchar2(10); jn varchar2(10); begin select J.job_name into jn from employee E join job J using(job_code) where emp_id = v_emp_id;
result := jn; return result; end; /

var emp_job_name varchar2; exec :emp_job_name := fn_get_job_name('&emp_id'); print :emp_job_name;

select emp_id, emp_name, fn_get_job_name(emp_id) from employee where emp_id = '&emp_id';

--## PROCEDURE

--Stored Procedure --함수와 달리 반환값이 없을 수도 있다. /* create [or replace] procedure 프로시저명( 매개변수명1 [IN | OUT] 자료형, 매개변수명2 [IN | OUT] 자료형, ... ) is 변수선언부; begin 실행부; end; / */ --1. 매개변수 없는 프로시저 : 정기적 실행하는 프로그램시 주로 사용 select * from emp_copy; --emp_copy 테이블의 모든 데이터를 삭제하는 프로시저 create or replace procedure proc_del_all_emp is begin delete from emp_copy; commit; end; / select * from user_procedures;

--실행 exec proc_del_all_emp; select * from emp_copy;

--2. 매개변수 있는 프로시저 insert into emp_copy (select emp_id, emp_name, salary, dept_title, job_name from employee E join department D on E.dept_code = D.dept_id join job J using(job_code)); commit; select * from emp_copy;

--emp_id를 입력 받아서, emp_copy테이블에서 제거하는 프로시저 --IN : 프로시저 입장에서 받는 것(default) create procedure proc_del_emp( p_emp_id IN emp_copy.emp_id%type ) is

begin delete from emp_copy where emp_id = p_emp_id; commit; dbms_output.put_line(p_emp_id || '번 사원을 삭제했습니다!'); end; / exec proc_del_emp('&emp_id');--201삭제 select * from emp_copy where emp_id = '201';

--매개변수 OUT --사번을 입력 받아서 사원명, 급여, 보너스를 OUT하는 프로시저 create or replace procedure proc_select_emp( p_emp_id IN employee.emp_id%type, p_emp_name OUT employee.emp_name%type, p_salary OUT employee.salary%type, p_bonus OUT employee.bonus%type ) is begin select emp_name, salary, nvl(bonus,0) into p_emp_name, p_salary, p_bonus from employee where emp_id = p_emp_id;
end; / --실행 --바인드 변수 선언 var b_emp_name varchar2(30); var b_salary number; var b_bonus number; --프로시저 호출 exec proc_select_emp('&emp_id', :b_emp_name, :b_salary, :b_bonus); --IN과 OUT을 한번에 입력 print :b_emp_name; print :b_salary; print :b_bonus; --exec 명령 후 바인드 변수 자동출력 해주는 옵션 set autoprint on;

--프로시저 활용 예제 : UPSERT --insert + update : 기존에 데이터가 존재하면 update 존재하지 않으면 insert --직급코드 관리 create table job_copy as select * from job;

select* from job_copy; --제약조건 조회 select constraint_name, UC.owner, UC.table_name, UCC.column_name, UC.constraint_type, UC.search_condition from user_constraints UC join user_cons_columns UCC using(constraint_name) where UC.table_name = 'JOB_COPY';

--제약조건 추가 alter table job_copy add constraint pk_job_code primary key(job_code) modify job_name not null; --프로시저 생성 create or replace procedure proc_upsert_job_copy( p_job_code job_copy.job_code%type, p_job_name job_copy.job_name%type ) is v_cnt number; begin --0. null여부 검사 if p_job_name is null then dbms_output.put_line('ERROR: null값 대입 불가'); return;
end if; --1. 데이터가 존재하는지 확인 select count(*) into v_cnt from job_copy where job_code = p_job_code;
dbms_output.put_line('v_cnt = ' || v_cnt); --2. insert or update if v_cnt = 0 then insert into job_copy values(p_job_code, p_job_name);
else update job_copy set job_name = p_job_name where job_code = p_job_code;
end if;
commit;

-- insert into job_copy values(p_job_code, p_job_name); -- commit; end; / --실행 exec proc_upsert_job_copy('J8', '인턴'); select * from job_copy;

exec proc_upsert_job_copy('J8', '수습'); --ORA-00001: unique constraint (KH.PK_JOB_CODE) violated exec proc_upsert_job_copy('J9', ''); --에러 메세지 보여주기

--2018-10-25 @실습문제

/*@실습문제

주민번호를 입력받아 나이를 리턴하는 저장함수 fn_get_age를 사용해서 사번, 이름, 성별, 연봉, 나이를 조회/ create or replace function fn_get_age( p_emp_no employee.emp_no%type ) return number is
empAge number; begin select extract(year from sysdate) - (case when substr(emp_no,8,1) in('1','2') then to_number(substr(emp_no,1,2)) +1900 else to_number(substr(emp_no,1,2)) +2000 end) 나이
into empAge
from employee where emp_no = p_emp_no;
return empAge; end; / select emp_id 사번, emp_name 사원명, fn_get_gender(emp_no) 성별,fn_bonus_calc(emp_id) 연봉, fn_get_age(emp_no) 나이 from employee where emp_no = '&emp_no'; --621225-1985634 selectfrom employee;

/2. 사원에게 특별상여금(보너스)를 지급하려고 하는데, 입사일기준으로 차등 지급하려 한다. 입사일기준 10년이상이면 150%, 3년이상 10년미만이면 125%, 3년미만이면 50%를 지급함. 저장함수명 : FN_BONUS_CALC, FN_GET_WORKING_DAYS(HIRE_DATE) 조회컬럼 : 사번, 사원명, 입사일, 근무기간(~년 ~개월), 보너스금액/

--사원번호와 근속년수를 입력받아 상여금 계산 create function FN_BONUS_CALC2( p_emp_id employee.emp_id%type, p_workYear number ) return number is result number; begin select case when p_workYear >= 10 then salary1.5 when p_workYear between 3 and 9 then salary1.25 else salary*0.5 end into result
from employee where emp_id = p_emp_id;
return result; end; / drop function FN_BONUS_CALC2;

--사번과 입사일을 입력하면 근속 년수를 계산하는 함수 create function FN_GET_WORKING_DAYS( v_emp_id employee.emp_id%type --v_hire_date employee.hire_date%type ) return number is workYear number; begin select to_number(trunc(months_between(sysdate, hire_date)/12)) 근속년수 into workYear from employee where emp_id = v_emp_id;


xxxxxxxxxx
    return workYear;

end; / --정답! select emp_id 사번, emp_name 사원명, FN_GET_WORKING_DAYS(emp_id) 근속년수, FN_BONUS_CALC2(emp_id, FN_GET_WORKING_DAYS(emp_id)) 상여금 from employee;

select emp_name, hire_date, to_number(trunc(months_between(sysdate, hire_date)/12))근속년수, round(mod(months_between(sysdate, hire_date), 12)) 개월 from employee order by 1;

/3. 기존부서테이블의 DEPT_ID, DEPT_TITLE만 복제한 DEPT_COPY를 생성한다. DEPT_ID 컬럼 PK추가. DEPT_ID 컬럼 변경 CHAR(3) DEPT_COPY를 관리하는 프로시져 PROC_MAN_DEPT_COPY를 생성한다. 첫번째 인자로 작동FLAG값 'UPSERT'/'DELETE'를 받는다. UPDATE시, 데이터가 존재하지 않으면 INSERT, 데이터가 존재하면 UPDATE DELETE시, 해당부서에 사원이 존재하는지(EMPLOYEE에서 FK로 참조하고있다고 가정)를 검사해서, 존재하면, 경고메세지와 함께 실행취소함. 그렇지 않으면, 삭제. 프로시저의 매개변수에 기본값을 지정하면, 생략가능함(매개변수명 mode 자료형 := 값)/ create table dept_copy as select dept_id, dept_title from department; select* from dept_copy;

alter table dept_copy add constraint pk_dept_id primary key(dept_id); alter table dept_copy modify dept_id char(3);

create or replace procedure PROC_MAN_DEPT_COPY( flagNo IN number, p_dept_id dept_copy.dept_id%type, p_dept_title dept_copy.dept_title%type
) is v_cnt number; c_cnt2 number; begin select count(*) into v_cnt from department where dept_id = p_dept_id;


xxxxxxxxxx
    select count(*)
    into c_cnt2
    from department D join employee E on D.dept_id = E.dept_code
    group by D.dept_id, D.dept_title
    having D.dept_id = p_dept_id;
    if flagNo = 1 then 
                                 if v_cnt = 0 then  insert into dept_copy values(p_dept_id, p_dept_title);
                                
                                else update dept_copy set dept_title = p_dept_title where dept_id = p_dept_id;
                                
                                end if;

else

if c_cnt2 = 0 then delete from dept_copy where dept_id = p_dept_id; else dbms_output.put_line('해당 직원이 있어 삭제 불가'); return; end if;

end if; commit; end; /

select * from user_unique; --기록하기 commit; --commit 전까지 복구 --rollback;

select * from location; select * from employee; desc employee; select * from job; select * from department; select * from sal_grade;

--제약조건 조회 select constraint_name, UC.owner, UC.table_name, UCC.column_name, UC.constraint_type, UC.search_condition from user_constraints UC join user_cons_columns UCC using(constraint_name) where UC.table_name = 'EMPLOYEE'; /* constraint_type = 제약조건 유형 (user_constraints)

P : 기본키 (Primary Key)
R : 외래키 (Foreign Key) + r_constraint_name = 참조하고 있는 제약조건 명 (user_constraints)
U : 고유값 (Unique)
C : 체크 (Not Null, Check). search_condition 컬럼에서 내용 확인 가능합니다.

CURSOR

sql문의 result set(결과집합)을 가리키는 참조변수(포인터)

하나 이상의 row에 대해서 순차적으로 접근할 수 있다.

--OPEN ~ FETCH ~ CLOSE

커서 종류

암시적 커서 : 모든 sql문이 실행됨과 동시에 암시적커서가 생성되서 처리됨


xxxxxxxxxx
 커서에 직접적으로 접근할 수 없다. 속성으로 처리하는 기능


xxxxxxxxxx
declare
  v_emp_id employee.emp_id%type;
  v_emp employee%rowtype;
begin
  v_emp_id := '&emp_id';
  -- 커리 실행
  select *
    into v_emp
    from employee
    where emp_id = v_emp_id;
    
  --커서 접근
  if sql%found then   -- 실행시 행이 하나라도 있다면 found는 true
    dbms_output.put_line(v_emp_id || '사원 : ' || v_emp.emp_name);
    dbms_output.put_line('검색된 row수 = ' || sql%rowcount);
  end if;
  
exception
  when no_data_found then dbms_output.put_line('검색 결과 없음');
end;
/
--커서속성
--sql%rowcount : 최근 실행된 sql문의 결과행을 리턴
--sql%found : open후 fetch된 행이 있으면 true, 없으면 false;
--sql%notfound : open후 fetch된 행이 있으면 false, 없으면 true;
declare
  
begin
  update emp_copy set emp_name = emp_name
    where dept_code = '&부서코드';
  commit;
  
  dbms_output.put_line(sql%rowcound || '행이 업데이트 되었음');
end;
/

명시적 커서 : 쿼리 결과집합에 직접 접근해서 처리할 필요가 있을경우 커서를 선언해서 사용한다.
(선언부)cursor
(실행부)OPEN
(실행부)fetch
(실행부)close


xxxxxxxxxx
   create table emp_cursor as
    select * from employee;
    
   --1. 파라미터 없는 커서
   declare 
    v_emp emp_cursor%rowtype;
    --커서 선언
    cursor mycursor
     is select *
      from employee;
   begin
    open mycursor;
    
    loop
      fetch mycursor into v_emp;
      exit when mycursor%notfound;
      
      dbms_output.put_line('사번 : ' || v_emp.emp_id);
      dbms_output.put_line('사원명 : ' || v_emp.emp_name);
      dbms_output.put_line('급여 : ' || v_emp.salary);
           dbms_output.put_line('---------------------------');
    end loop;
    
    close mycursor;
   end;
   /
   
   --2. 파라미터 있는 커서
   
   declare 
    cursor myc(p_dept_code emp_cursor.dept_code%type)
    is
      select *
        from emp_cursor
        where dept_code = p_dept_code;
    
       --커서에서 fetch된 행을 담아둘 행변수
    v_emp emp_cursor%rowtype; 
    --사용자 입력 부서코드를 담아둘 변수
    v_dept_code emp_cursor.dept_code%type;
   begin
    v_dept_code := '&dept_code';
    
    open myc(v_dept_code);
    loop fetch myc into v_emp;
      exit when myc%notfound;
    
    
    dbms_output.put_line('사번 : ' || v_emp.emp_id);
    dbms_output.put_line('사원명 : ' || v_emp.emp_name);
    dbms_output.put_line('급여 : ' || v_emp.salary);
       dbms_output.put_line('---------------------------');
    
    end loop;
   end;
   /
   
   --컬럼단위처리
   
   declare 
    cursor cs(p_dept_code emp_cursor.dept_code%type)
    is
      select emp_name,
        salary
        from emp_cursor
        where dept_code = p_dept_code;
    v_emp_name emp_cursor.emp_name%type;
    v_salary emp_cursor.salary%type;
   begin
    open cs('&dept_code')
    loop fetch cs into v_emp_name , v_salary;
    exit when cs%notfound
      dbms_output.put_line(v_emp_name || ',' || v.salary);
    end loop;
    close cs;
   end;
   /

TRIGGER

방아쇠, 연쇄반응

특정이벤트, ddl, dml문이 실행되었을 때

자동으로 어떤동작이 수행되도록 처리하는 객체

트리거 종류

dml trigger
ddl trigger
logon/logoff trigger

회원테이블에 탈퇴가 발생하면, 탈퇴회원테이블에 추가하기

테이블변경이 있을때, 수정내역을 로그테이블에 추가하기


/*
create or replace trigger 트리거명
  before | after
  update or delete or insert on 테이블명
  [for each row] --> 행단위, 문장단위 옵션
begin
  (해당테이블에 dml을 실행시 처리로직)
end;
/
*/
create or replace trigger trg_emp_new 
  after
  insert on emp_test1
  for each row
begin
  dbms_output.put_line('신입사원이 입사했습니다.');
end;
/
-- 신입사원 insert
insert into emp_test1 values('999','띠그량','920727-1239874','eunchan2000@gmail.com',
               '01068681231','D1','J1','s1',123123123,0.5,200,sysdate,null,default);

띠그랭

Oracle - SQL

Oracle (feat. docker)

예제

Table

데이터 타입 저장되는 값

연산 결과타입 설명

실습문제

비교연산자 in

연산자 우선순위

order by

FUNCTION

단일행 처리 함수

숫자 처리 함수

날짜처리함수

형변환 함수

날짜에서 앞에 0 지우기

sysdate 연산

to_char 포맷

to_number

nvl

기타함수

날짜 포맷 (rr을 사용할 시)

그룹함수

group by

Having

소계, 누계를 처리하기 : rollup, cube

Join

equl - Join

eqal-join 종류

cross join

self join

multiple join(다중 조인)

Oracle 전용 문법

self join

multiple join (oracle전용구문의 다중조인에서는 테이블 순서가 중요하지 않다.)

non - equal join

집합연산자(Set Operator)

grouping sets

SubQuery

서브쿼리의 유형

1.단일행 서브쿼리

2. 다중행 서브쿼리

3. 다중열 서브쿼리

상관서브쿼리

스칼라 서브쿼리

인라인 뷰

DML

insert

update

delete

DDL

create

1. NOT NULL

2. Unique

3. primary key

4. foreign key

5. check

Alter

drop

DCL

2. revoke

Database Object

DD - Data Dictionary

VIEW

SEQUENCE

INDEX

복합 인덱스

PL/SQL

문제

--## PROCEDURE

CURSOR

TRIGGER

+ Recent posts

티스토리툴바