필기 정리 - DataBase(sql,oracle)

조형찬·2023년 3월 15일
Databaseoraclesql
0

필기 정리

목록 보기
9/11
  • sql 문자열 데이터 타입의 사이즈

문자열은 반드시 사이즈를 지정해야 한다.
(cf.number, long와 같은 숫자형은 따로 크기지정이 필요하진 않다.)
1) char => 고정크기, 사이즈 지정 필수
예) char(6)을 주면 안에 내용이 4만큼만 들어가도 6의 저장공간을 사용한다.
즉, 데이터 공간이 낭비가 된다. (모든 컬럼이 char이라면 varchar2 사용x)속도가 상대적으로 빠르다.
2) varchar2 =>가변크기, 사이즈 지정 필수
예)varchar(6)이면 최대 6만큼 들어갈 수 있는데 용량이 4만큼이 들어오면 4만큼만 사용한다.
대부분은 varchar2방식으로 사용한다. 하나의 컬럼에 문자열로 넣을 수 있는 최대 용량은 4000바이트까지이다. oracle에서는 varchar 대신 varchar2를 쓴다.
(varchar의 경우 oracle에서 변경이 예고되어 있어서 추후 오류 방지를 위해 varchar2를 쓴다.)
cf) long => 최대 2GB까지 저장이 가능하다. 사이즈 지정이 필요없음.
하나의 테이블에 long형은 없거나 하나만 있어야 한다. 보통 4000byte가 넘어갈때부터 사용한다.
조회시 많은 제약 사항이 발생한다. 따라서 오류 가능성이 많아서 가능하면 varchar로 나누어서 저장하는 편이 좋다.

  • sql 숫자형 데이터 타입 number

number(p,s)
p= 최대 유효숫자 자릿수, s=소수점 기준 자릿수
소수점 기준 자릿수 s로 최대 범위를 지정하고 그 안에서 0이 아닌 유효숫자 자릿수가 맞는지 확인한다.

예)
col1 number(3), //col1 number(3,0)과 동일하다.
col2 number(8,3), //총 8자리중 소숫점 아래 3자리까지만 들어갈수 있다.
col3 number(8,-2), //총 8자리중 소숫점 왼쪽 2자리까지 0으로 채우고 반올림함.
col4 number(3,5) //총 5자리 데이터 저장 앞에 2곳은 00이 들어감 예) 0.00xxx가 저장됨

cf) 날짜형 데이터 date, timestamp (예.col1 date, col2 TIMESTAMP,)

  • 참고 ANSI 타입 (DB의 표준 데이터 타입)

character(n) => char(n)
character varying(n) => varchar2(n)
numeric(p,s) or decimal(p,s) => number(p,s)
integer , int , smallint => number(38) (= number)
float, double precision => number

좌측처럼 쓰는게 ANSI 타입이지만 쓸일이 없고 우측과 같이 쓴다.

  • 참고 TTL

내 컴퓨터안의 것에 접속할 때 TTL=128
어느 사이트에 접속할 때 몇개의 라우터를 거치느냐에 따라 TTL이 줄어든다.
많이 거칠수록 접속시간ms(밀리세컨드)가 늘어난다.

  • sql 테이블 관련

desc 테이블명; //테이블 구조 확인
(cf. desc가 처음에 나오지 않는경우 descending의 약자이다(내림차순))
select * from tab; //전체 테이블 확인,
자바 프로그래밍에서 해당 계정의 테이블 목록을 얻어내고자 할 때 유용하게 사용될 수 있다.

dual // 더미 테이블
예) select 28*3*65 result from dual; // dual이라는 더미테이블에 result컬럼에 5460 계산결과가 들어간다(컬럼명에 계산식을 적을 수 있음).
cf) from절 다음에는 반드시 테이블로 해석될 수 있는 것
(table, dual,임시테이블, view,시노님(테이블에 별명을 붙인 것) 등 이 온다,)

임시테이블 예) select로 찾은 결과를 임시테이블로 주었다.

select * from (select rownum, employee_id,last_name || first_name, as irum,
email mail from employees);
  • sql select관련
select first_name as 이름 , last_name from employees emp;  
//first_name이라는 컬럼 이름을 '이름'이라고 별명을 짓기( 띄어쓰기로 해도 되지만 as 사용 권장)
단 from절의 테이블명은 as사용대신 띄어쓰기만으로 사용해야한다.  
예)from employees emp(o)  from employees as emp(x)

select first_name || ' ' || last_name as irum from employees;
// irum이라는 컬럼 하나에 first_name, last_name 내용이 하나로 합쳐져서 나온다.

select count(*) from 테이블명; //count() =>직계 함수  ()안의 내용의 수를 출력
select distinct department_id from employees; 
// distinct 중복되는 것 제거(여러개의 컬럼에 걸쳐서 쓸경우 모든 컬럼에서 중복되는게 있는것만 제거한다.)

cf) null값에 해당하는 값을 더할것인지 제외할것인지는 매우 중요한 문제

  • sql select문 서브 쿼리 사용
예)-- 사원번호, 성명, 급여액, 보너스액(급여의 20%), 실지급액을 조회하고 싶다.

select employee_id, first_name || ' ' || last_name as irum, salary,salary*0.2
as bonus,salary*1.2 as gigeub from employees; 
/*사원 번호, 성과 이름을 합쳐서 이름컬럼에 넣고, 봉급, 봉급*0.2를 보너스로 넣고, 
실 지급액은 봉급+보너스지만 한번에 진행되기 때문에 salary+bonus는 실행 안되고 salary*1.2로 해야 한다.
*/

select employee_id, irum, salary, bonus, salary+bonus as gigeub from (select employee_id, 
first_name || ' ' || last_name as irum, salary,salary*0.2 as bonus,salary*1.2 as gigeub 
from employees);
bonus를 salary*0.2로 이미 구해둔 상황이고 이를 임시 테이블로 묶은 상태로 select를 하는 경우이다. 
이 경우 bonus가 있기 때문에 실 지급액을 salary+bonus로 할 수 있다.
  • sql 정렬 order by
select * from employees order by department_id desc;
employees 테이블의 모든 내용을 정렬하는데 department_id내림차순으로 정렬해서 보여준다. 
(기본값은 오름차순이지만 desc를 적어서 내림차순)

단, order by는 sql의 자원을 많이 잡기 때문에 효율이 낮아진다. 즉, 가능하면 적지 않는것이 좋다.

  • sql select

select의 결과
- 1.단일행(primary key)
1.1. 단일 컬럼 select와 from 사이에 하나만 있음
1.2. 다중컬럼 select와 from 사이에 여러개 있음

-2. 다중행(리스트)
2.1. 단일 컬럼 select와 from 사이에 하나만 있음
2.2. 다중컬럼 select와 from 사이에 여러개 있음

cf) 

다중행 예)
select * from employees where department_id is null; 
--결과적으로 하나의 결과가 나올지라도 단일행으로 보면 안되고 다중행으로 봐야 한다.
--(데이터의 구성을 모르기 때문이다)

단일행 예)
select * from emlpoyees where employee_id = 180;
--없거나 하나만 받는다. 1의 최대 단일행을 보장받는다.
  • sql where

cf) DB에서 같지 않다는 뜻 (!=, <>, )
예) where col1!= 'a' 또는 where col1 <> 'aa`
cf) from절 다음에는 where절이 제일 먼저 나와야 한다.


select * from empLoyees  
    ORDER  BY EMOLOYEE_ID DESC
    WHERE EMPLOYEE_ID >150;  // order by가 먼저 나와서 오류

select * from empLoyees 
    WHERE EMPLOYEE_ID >150
    ORDER  BY EMPLOYEE_ID DESC; //정상 작동 (from 다음 where)
  • sql and, or

oracle DB sql에서 논리연산자로 AND, OR를 사용한다.
|| 는 연결할때 사용되기 때문에 or연산자로 쓰이지 않는다.

  • sql between, in, like
between
두 방법 모두 salary가 8000~10000사이인 데이터를 조회하는 방법이다.
select * from employees where salary >= 8000 and salary <= 10000;
select * from employees where salary between 8000 and 10000;

in
두 방법 모두 department_id가 50또는 80또는 100인 데이터를 불러온다.
select * from employees where department_id = 50 or department_id = 80 or department_id = 100;
select * from employees where department_id in(50, 80,100);

like
select * from employees where email like '%z%'; 
//테이블에서 email에서 중간에 'z'글자가 들어가는 것 조회(대소문자 구분)
%z가 들어가면 마지막 글자에 z가 들어가는 것을 조회한다
cf)'_S%' 는 앞에서 2번째 글자가 S인것 조회
  • sql nvl, nvl2
select count(department_id) from employees;
//원래 null값이 하나 있어서 106 출력
select count(nvl(department_id,0)) from employees;
//null값을 0으로 넣으라는 뜻, 즉 null값이었던 부분이 0으로 채워졌으니 107출력

null값이 포함된 계산식이 있으면 그 결과값은 항상 null값이 나온다.
select salary + commission_pct, employees.* from employees;
//commission_pct에 null값이 있어서 계산 오류가 생김

select salary + nvl(commission_pct,0), employees.* from employees;
//commission_pct의 null값을 0으로 바꾸고 계산하면 제대로된 결과가 나옴
select salary + nvl2(commission_pct,commission_pct,0), employees.* from employees;
// commission_pct의 값이 null이 아니면  commission_pct 출력, null이면 0출력

상세설명
nvl(컬럼명, null일때 바꿀 값) 
예) nvl(commission_pct, 0) 
commission_pct이 null값이 아니면 원래 값 출력, null이면 0출력

nvl2(컬럼명 , null이 아닐때 지정할 값, null일 때 지정할 값)
예) nvl2(commission_pct, 1, 0)
commission_pct이 null 값이 아니면 1 값이 null이면 0을 출력
  • sql decode 함수와, case 문

decode사용

select employee_id, First_name, salary,job_id,
    decode((substr(job_id,4)),'ACCOUNT', 1000,'MGR',2000, 'REP',3000, 500 ) 
	as upsal
	from employees ;
// cf . (substr(job_id,4)는 job_id의 4번째 부터 읽는 값이다 이 값이 ACCOUNT면 SALARY에 1000 , 
//MGR이면 2000 ...을 추가하고 다 아니면 500을 추가하여 upsal이란 컬럼명으로 추가한다. 

case문 사용

select employee_id, First_name, salary,job_id,
    case substr(job_id,4)
        when'ACCOUNT' then 1000
        when 'MGR' then 2000 
        when 'REP' then 3000 
        else 500 
    end as upsal 
    from employees ; 
사용 결과는 위와 동일하다.
  • sql group by

부서를 의미하는 부서 번호 별 값을 구하기 위해 사용하곤 한다.

예) select avg(salary) "부서별 평균 급여" , sum(salary) as 부서별급여함, department_id "부서코드" 
		from employees 
        group by department_id;
	//부서별 평균 급여라는 컬럼에 봉급 평균을 넣고 이를 부서코드별로 출력한다.
    //부서별 급여함에는 봉급 합계를 넣는다. 
    //(department_id의 직계에 해당하는 내용들만 select와 from절 사이에 넣을 수 있다.

cf. where salary>8000을 추가할경우 부서별, 봉급이 8000이상 받는 직원들의 값으로 계산하여 출력한다. 
   이때 조건에 해당하는 직원이 없는 부서의 경우 조회가 안될 수 있다.
cf. 조건부분 내용 설명
      --where salary > 8000          // 조건을 주고 결과를 찾기
        group by department_id
        having avg(salary) >7000;  // 조회된 결과에서 조건 추가

패키지를 총 4개 만들어준다.
.controller  (서블릿)
.dao	   (모델)
.service	   (모델)
.vo	   

controller패키지 안에 우클릭 뉴 서블릿을 만든다.
HttpServlet을 상속받으면 서블릿이 된다.
  • sql join 조인

두 개 이상의 테이블을 연결하여 하나의 테이블처럼 출력할 때 사용하는 방식이다.

예1)
SELECT * from employees, departments
    where employees.department_id = departments.department_id(+);

//table emplyees와 table departments를 조인하는데 department_id가 같은 부분을 조인한다.
//(+) 겹치는 사람중에 부서코드가 null값인 사람도 추가해준다.

예2)
select employees.employee_id, employees.first_name , departments.department_name
    from employees, departments 
        where employees.department_id = departments.department_id(+);

예3 cf) 두 테이블 중 하나에만 있는 컬럼을 쓸 때는 테이블 명을 빼도 된다.
select employee_id, first_name , departments.department_name
    from employees, departments 
        where employees.department_id = departments.department_id(+);

예4) 테이블명에 별명을 달아서 만들기 (많이 사용된다) 
select employee_id, first_name , dep.department_name
    from employees emp, departments dep 
        where emp.department_id = dep.department_id(+);

예5) 겹치는 부분이 무엇인지 찾아서 where절에 조건으로 넣기
model 구조를 보고 관련성을 확인
select employee_id, first_name, email, d.department_id, department_name, job_title, city
    from employees e, departments d, jobs j, locations l
        where  e.department_id=d.department_id
            and e.job_id = j.job_id
                and d.location_id = l.location_id;  --여기까지 조인의 조건
	        and l.state_province = 'California'; --일반 조건
  • 레프트 join, 라이트 join / (+) 사용
예) 레프트 아우터 조인
select employee_id, first_name , departments.department_name
    from employees, departments 
        where employees.department_id = departments.department_id(+);

employees.department_id 안에 null값이 있는 자료가 있을 때 departments.department_id에서 찾아서 넣어줌

예) 라이트 아우터 조인
select employee_id, first_name , departments.department_name
    from employees, departments 
        where employees.department_id(+) = departments.department_id;

departments.department_id안에 null값이 있는 자료가 있을 때 employees.department_id 에서 찾아서 넣어줌
  • sql view p338

view를 잘 만들고 vo객체를 만들면 효율적으로 join을 쓸 수 있다.
CREATE [OR REPLACE] VIEW .EMP_DETAILS_VIEW
//or replce 없으면 만들고 있으면 대체하라
AS
조인 SELECT 구문이 여기에 들어옴
WITH READ ONLY; //읽기 전용

  • 뷰(view)

데이터 베이스의 하나의 오브젝트.
join대신 뷰를 활용하는 방법이 있다.
cf) 단, 인라인 뷰는 object가 아니라 서브쿼리이다. 일시적인 테이블로서 명령을 수행한 후에 사라진다.
뷰 : 물리적인 테이블을 근거해서 만들어진 논리적인 가상 테이블 객체(실제 자료를 저장하고 있는 테이블은 아니다)이다. 조회(select)쿼리의 from절 뒤에 사용된다.
뷰의 사용형식 : create [or replace] view 뷰이름 as select 문장 ;
(cf. []기호안의 내용은 필요하면 쓰고 필요하지 않다면 쓰지 않아도 된다는 의미이다. create or replace를 붙여서 쓰면 기존에 있어도 오류없이 만들어진다. {} 기호 안의 내용중 하나이상은 반드시 써야 한다.)
view는 보안성과 편리성의 측면에서 도움이 되어 사용된다.

뷰 만드는 예1)
CREATE or replace view partEmployees
    as select employee_id 사번, first_name 이름, last_name 성, email 메일, phone_number 휴대폰 
    from employees;

뷰 만드는 예2)
CREATE or replace view partEmployees(사번, 이름, 성, 메일, 휴대폰)
    as select employee_id, first_name, last_name, email, phone_number from employees;

뷰자체에는 물리적인 데이터가 존재하는 건 아니지만,
뷰를 이용해서 물리적인 데이터 자료의 수정을 할 수 있다.

insert into partemployees values(270, 'chan', 'cho','chan@naver','010.233.3333',sysdate,
'IT_PROG',12000,null,100,60);

view에 insert를 했을 때 원래 테이블인 employees의 자료에 추가된다.( delete, update등도 가능)

단, view를 만들때 with Read only를 추가하여 생성한다면 자료에 수정을 가할 수 없다.(오직 읽기만 가능)

CREATE or replace view partEmployees
    as select * from employees;
    with Read only

with check option 을 사용할 경우에는 지정한 제약 조건을 만족하는 데이터에 한해서 작업이 가능하도록 한다.


CREATE or replace view emp_update
    as select employee_id, first_name, last_name, email,hire_date, job_id, department_id
        from employees
        where department_id in(20,30)
    with check option;

단, with read only 또는 with check option중 하나만 써야한다.

  • 시퀀스(sequence)
예)
create table testSeq(
    seNum number primary key, (// seNum에 데이터의 구분 번호를 준다)
    irum varchar2(30),
    email varchar2(50)
    );

insert into testseq(seNum,irum, email) values((select max(seNum)+1 from testSeq)
,'park', 'namgil@naver.com');  
//이렇게 seNum 다음번호를 부여하여 만들면 간편하지만 동시접속자가 많은 경우 데드락이걸리는 오류가 생길 수 있다.

따라서 시퀀스를 만든 후 사용한다.
시퀀스 생성
CREATE SEQUENCE  testTB_SEQ MINVALUE 1 MAXVALUE 9999999999999999999999999999 
INCREMENT BY 1 START WITH 6 NOCACHE  NOORDER  NOCYCLE ; 

시퀀스를 활용하여 insert사용
insert into testseq(seNum,irum, email) values(testTB_seq.nextval,'park', 'namgil@naver.com');
  • 객체 권한 부여

다른 테이블에 있는 자료를 가져올 수 있도록함.
(객체 권한은 소유자가 부여할 수 있다.)

grant select on employees to chan;

select에 대한 권한을 부여해 준다. (다른 테이블에서 select사용 가능)
  • 인덱스

인덱스 사용을 고민해야 하는 경우 (성능이 저하될 수 있는 경우)
1. 입력, 삭제, 수정이 빈번하게 이루어지는 경우 ( 입력, 삭제, 수정등이 있을 때마다 인덱스를 재구성하기 때문이다.)
2. 적용 컬럼의 data가 밀집되는 경우(데이터 중복이 많은 경우 예) gender컬럼의 남,여)
3. 하나의 테이블에 너무많은 인덱스를 만드는 경우
4. 거의 조회되지 않는 컬럼의 경우

  • rollback

commit;을 하지 않은 경우 rollback을 하면 여러번 delete를 해주어도 전부 복구된다.
rollback은 가장 최근에 commit한 상태로 돌아가기 때문이다.

  • savepoint

rollback은 마지막 commit한 상태로 돌아가기 때문에 예) savepoint sp01; 처럼 rollback으로 돌아갈 지점을 만들어 주면 오류가 생겼을 때 해당 지점으로 돌아갈 수 있다.


예) 
delete employees where employee_id >190;
savepoint sp01;
delete employees where employee_id >180;
savepoint sp02;
rollback to sp02;
  • truncate table 테이블명

truncate table employees; 를 하면 delete와 같이 모두 지워주는 역할을 한다.
하지만 이를 사용하면 rollback을 해도 복구되지 않는다.

  • 서브쿼리

sql문을 실행하는 데 필요한 데이터를 추가로 조회하기 위해 sql문 내부에서 사용하는 select문이다.
cf) 단일행, 단일 컬럼으로 나오는 편이 가장 좋다.

예1) 단일행 서브쿼리

select avg(salary) from employees where department_id =80;
select * from employees where salary>=(select avg(salary) from employees where 
department_id =80);
--부서번호 80번의 평균 봉급보다 높은 직원들을 조회

예2-1) 다중행 서브쿼리 (in, any, some, all, exists 등 사용)
select * from employees where salary in (select max(salary) from employees group by 
department_id);
--부서별 최고 봉급들인 직원들 조회
--서브쿼리 안에 단일 값이 아니라 여러 값들이 나오기 때문에 쿼리문에in을 써줬다.
--in은 등호나 크기비교가 불가능하다. (cf in은 =any와 결과가 같다)

예2-2) 다중행 서브쿼리 any
select * from employees where salary > any (select max(salary) 
from employees group by department_id); 
--any를 사용하면 등호나 크기비교 가능하다, any뒤의 값들중 아무거나보다 큰 자료를 조회한다 
--(서브쿼리중 제일 작은 값이 4400, 따라서 4400보다 큰 데이터들을 조회한다.)
--cf)여기서 any대신 all이 들어갈 경우 all뒤의 서브쿼리 결과의 모든 값보다 큰값이어야 한다.

예2-3) 다중행 서브쿼리 exists
select * from employees where exists(select department_name from departments 
where department_id=1000);
--exists는 전부 또는 전무의 역할을 한다. select문에서 where문 뒤에 true값이 올 경우 그대로 출력하고
--false값이 올경우 아무것도 출력하지 않는다.
--exists뒤의 값이 false기 때문에 아무런 값도 출력하지 않는다.

예3) 다중행,다중열 서브쿼리
select * from employees where (department_id, salary) in (select department_id, max(salary)
from employees group by department_id);
--열이 department_id, salary로 2개고 행도 여러개인 경우의 내용이다.
--자주사용하지 않는 것을 권장

예4) 임시뷰
select * from (select rownum numberring, firstImsi.* from (select * from employees order by
employee_id desc) firstImsi) 
    where numberring <21 and numberring >10;
--rownum을 numberring이라는 별명을 주어서 한번더 묶지 않으면 where절을 사용할 때 오류가 생길 수 있다.

예5) select 절에서 서브 쿼리 사용
select employee_id, first_name || '' || last_name as empName, job_id, salary,(select '1등급' 
from dual) as salgrade, department_id,(select department_name from departments d where 
e.department_id=d.department_id) as dname 
    from employees E; 
-- 주의점은 서브쿼리의 결과는 하나의 컬럼만 나오게 해야 한다.
profile
조형찬
서버개발 공부중
이전 포스트

필기 정리 - java

다음 포스트

필기 정리-Spring

0개의 댓글