[수강일지 28] select 5 ( view,sequences)

 

view

가상테이블  : View
-
가상테이블이란? 
    -> 실제테이블이 아니다  
-> 존재하지 않는 테이블이다. 

왜 사용할까? 
한 개의 view로 여러개의 테이블을 접근하기 위해서 
select  절에서 많이 사용한다 
속도가 빠르다
설정에 제한을 둬서 사용이 가능하다 -> read-only 등과 같이 읽기 전용으로 사용이 가능하다 
 
 

view (가상테이블) 만들기 

잡스 테이블로부터 데이터를 가져와서 가상 테이블 ub_test 만들기

CREATE VIEW ub_test
as
select job_id, job_title, max_salary
from jobs;

select * from ub_test; -- 출력구문

 

생성 됨 확인 완료

 

ub_test 와 똑같은 데이터가 들어있다

 
 

가상테이블 삭제 

drop view ub_test;

> 구문 실행 후 views 에서 refresh 누르면 사라짐 확인 된다 
 

가상테이블을 생성 시 칼럼명 변경

CREATE VIEW ub_test(jid,jtitle,maxal)
as
select job_id, job_title, max_salary
from jobs;

> job_id 는 jid , job_title 은 jtitle  , max_salary 는 maxal 로 출력되었다 

변경 확인 완료

 

가상테이블에 데이터 추가하기 

> 가상 테이블을 통해 불러온 원본테이블에서 작업을 진행할 수 있다 
> 뷰를 통해 원본 데이터 접근이 가능해졌기 때문이다. 

INSERT into ub_test
VALUES('SALES', '영업부', 20000);

> INSERT INTO ub_test: "ub_test라는 테이블에 데이터를 넣겠다"라는 선언
> VALUES(...): "그 테이블에 구체적으로 어떤 값들을 넣을지" 이 괄호 안에 순서대로 적어주는 역할
> VALUES -> 실제로 삽입할 값들을 지정하는 키워드이다.

데이터 추가 확인

> 원본 테이블인 Jobs 테이블에서도 영업부 데이터가 추가 되었음을 확인할 수 있다 
 

or replace 기능 : 재정의 

> OR REPLACE(오타가 난 부분 포함)는 SQL에서 "이미 존재하면 덮어쓰고, 없으면 새로 만들어라 라는 의미 
> 기존 뷰를 자동으로 삭제하고 새로운 정의로 깔끔하게 덮어써준
>테이블에서는 불가능하지만 view 에서는 가능한  or replace 기능
>drop을 사용하지 않아도 확인이 가능하다 

💡 주요 장점 번거로움 감소: DROP VIEW 명령어를 따로 실행할 필요가 없습니다. 권한 유지: 기존 뷰에 부여되어 있던 사용자 권한(Privileges)이 유지된 채로 내부 쿼리만 수정할 수 있습니다

 
 

CREATE or replace view ub_test(jid,jtitle,maxal)
as
select job_id, job_title, max_salary
from jobs;

 
 
 

강사님 work 

-- 문제1) EMPLOYEES 테이블에서 20번 부서의 모든 컬럼을 포함하는 EMP_20 VIEW를 생성 하라 -- 문제2) EMPLOYEES 테이블에서 30번 부서만 EMPLOYEE_ID 를 emp_no 로 LAST_NAME을 name으로 SALARY를 sal로 바꾸어 EMP_30 VIEW를 생성하라. -- 문제3) 부서별로 부서명, 최소 급여, 최대 급여, 부서의 평균 급여를 포함하는 DEPT_SUM VIEW을 생성하라. -- 문제4) 앞에서 생성한 EMP_20,EMP_30 VIEW을 삭제하라.

⭕❌

---

-- 문제1) EMPLOYEES 테이블에서 20번 부서의 모든 컬럼을 포함하는 EMP_20 VIEW를 생성 하라 ⭕
CREATE VIEW EMP_20
as
select *
from employees
where department_id=20;

select * from EMP_20;

-- 문제2) EMPLOYEES 테이블에서 30번 부서만 EMPLOYEE_ID 를 emp_no 로 LAST_NAME을 name으로 SALARY를 sal로 바꾸어 EMP_30 VIEW를 생성하라. ⭕
CREATE or replace view EMP_30 
as
select employee_id as emp_no , last_name as name ,salary as sal 
from employees
where department_id=30;

-- 문제3) 부서별로 부서명, 최소 급여, 최대 급여, 부서의 평균 급여를 포함하는 DEPT_SUM VIEW을 생성하라.
CREATE view DEPT_SUM
as
select d.department_name, max(e.salary) , min(e.salary), avg(e.salary)
from employees e, (select department_name,department_id
from departments
group by department_id,department_name)d
where e.employee_id = d.department_id
group by d.department_name;
 

CREATE OR REPLACE VIEW DEPT_SUM (dname, minsal, maxsal, avgsal) AS SELECT d.department_name, MIN(e.salary), MAX(e.salary), TRUNC(AVG(e.salary)) FROM employees e  JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_id, d.department_name;

-- 문제4) 앞에서 생성한 EMP_20,EMP_30 VIEW을 삭제하라. ⭕

drop view EMP_20;
drop view EMP_30;
drop view DEPT_SUM;

sequences

> 시퀀스(Sequence)는 고유한 순차적 번호(일반적으로 숫자)를 자동으로 생성해 주는 오직 번호 발급만을 위한 객체
> "중복되지 않는 고유한 번호 식별자"가 필요할 때 사용한다 
     ex) 1. 테이블의 기본키(Primary Key) 생성  사용 예시: 회원가입 시 회원번호 발급, 새로운 게시글 등록 시 글번호 발급.
          2. 비즈니스 일련번호 (사원번호, 상품번호, 송장번호 등)
          3. 주문번호 및 영수증 번호 생성
 
 
/*
sequence: 순서(차례)
  object
중복되지 않는 번호를 생성 -> 사원번호, 상품번호 
독립적인 기능 
초기화는 불가능하다  -> 삭제 후 생성 필요 
*/

drop sequence test_seq cascade;

--시퀀스 생성 
--sequence- > 자동카운트하는 기능
create sequence test_seq
increment by 1 -- 1씩 증가 
start with 1
maxvalue 100;



--<현재 sequence value 꺼내는 방법>

-- 1. 먼저 다음 값을 호출하여 시퀀스를 시작합니다 (최초 실행 시 'start with' 값인 1이 반환됨)
SELECT nextval('test_seq');

-- 2. 그 이후에 currval을 호출하면 정상적으로 현재 값(1)이 조회됩니다.
SELECT currval('test_seq');

--/////////////////////////////
--수정(increment by)수정 
alter sequence test_seq
INCREMENT by 3;

--실행하면 이제 3씩 늘어남 
select nextval('test_seq');

--고객 정보를 저장할 수 있는 기능을 심플하게 만들어 둔 것 
create table client (
   seq int PRIMARY key,
   id varchar(30) not null,
   name varchar (30) not null,
   age int 
   ); 

insert into client (seq, id, name, age)
VALUES(nextval('test_seq'),'abc','홍길동','24');

insert into client (seq, id, name, age)
VALUES(nextval('test_seq'),'bcd','성춘향','15');


insert into client (seq, id, name, age)
VALUES(nextval('test_seq'),'cbe','일지매','22');


create table clientone(
 seq int default nextval('test_seq')primary key,
 id varchar(30) not null,
 name varchar(30) not null,
 age integer
 );

 insert into clientone (id,name,age)
VALUES('abc','홍길동','24');

 insert into clientone (id,name,age)
VALUES('bcd','홍길','16');

 insert into clientone (id,name,age)
VALUES('cbe','길동','20');

select * from clientone;