학원 수업 23일차 220921

개발일기

학원 수업 23일차 220921

hhana 2022. 10. 25. 09:31

9-10

SQL developer > F10 계획 설명

-- 스칼라 서브쿼리
-- select employee_id, email, (select '데이터' from dual) from employees;
-- select employee_id, email, (select department_name from departments) from employees;-
-- 다중행 서브쿼리 (결과가 2개 이상 나오는 쿼리)는 사용 불가능
-- 단일행 서브쿼리만 사용가능

-- 스칼라 서브쿼리
select
employee_id,
email,
(select department_name from departments d where e.department_id = d.department_id) 부서명,
(select job_title from jobs j where j.job_id = e.job_id) 직종
from employees e
order by 1;

-- oracle문법
select employee_id, email, department_name, job_title
from employees e, departments d, jobs j
where e.department_id = d.department_id(+) and e.job_id = j.job_id
order by 1;

select employee_id, email, department_name, job_title
from employees e, departments d, jobs j
where e.department_id(+) = d.department_id and e.job_id = j.job_id
order by 1;

-- ANSI문법
select employee_id, email, department_name, job_title
from employees e left outer join departments d -- 좌측 테이블 employee 기준
on e.department_id = d.department_id
join jobs j
on e.job_id = j.job_id
order by 1;

10-11

뷰
-- 인라인 뷰
-- 입사일 기준으로 11~15번째 입사한 사원을 확인하고 싶어요
-- emp
-- rownum : 원본에서 저장된 순서

select * from emp;
select rownum, e.* from emp e order by hire_date;

-- 입사일 기준으로 정렬한 뷰를 출력
select *
from
(select rownum rnum, e.*
from (select * from emp order by hire_date) e)
where rnum between 11 and 15;
--페이징 처리시 원하는 위치의 데이터만 가지고 올 수 있어요

desc employees;

create view EMP
as
SELECT
  employee_id as emp_id,
    first_name||' '||last_name as name,
    email,
    phone_number as phone,
    hire_date,
    salary as sal,
    commission_pct as com_pct,
    manager_id as mgr,
    department_id as dept_id
FROM
  employees
ORDER BY 1
with read only;

select * from emp;

update emp
set sal=1000
where emp_id 100;

delete emp where emp_id=100;

읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

11-12

시퀀스
-- 1. 연속적인 번호를 생성시켜주는 기능
-- 2. 숫자형 PK에 적용하여 사용 가능

create sequence seq_emp
increment by 1 start with 1;

-- 시퀀스로 번호 생성
select seq_emp.nextval from dual;

-- 현재 시퀀스가 발행한 번호
select seq_emp.currval from dual;

drop table board;

create table board(
  no number primary key,
    title varchar2(255) not null,
    content varchar2(255) not null,
    writer varchar2(40) not null,
    read_count number default 0,
    created_date timestamp default systimestamp,
    updated_date timestamp
);

create sequence seq_board start with 1 increment by 1;

insert into board (no, title, content, writer, updated_date)
values(
seq_board.nextval,
'제목'||seq_board.currval,
'내용'||seq_board.currval,
'작성자'||seq_board.currval,
systimestamp
);

commit;

select * from board;

12-1

select * from board;

insert into board(no, title, content, writer, updated_date)
values(
    seq_board.nextval,
    '제목'||seq_board.currval,
    '내용'||seq_board.currval,
    '작성자'||(mod(seq_board.currval, 5)+1),
    systimestamp
);

commit;

select * from board order by no desc;
-- 페이지 적용 : 최근 게시글로부터 페이지당 10개씩
-- 총 게시글 수 / 페이지당 개수 : 52 / 10 = 5
-- 52 % 10 = 2 (0~9)
-- 나머지가 1~ 이면 페이지 수 증가 5 + 1 = 6

select count(*) from board;

delete board where no in(7,17,27,37,47);

-- 1페이지 정보만을 보고싶어요
-- 인라인뷰를 서브쿼리로 실행할 수 있어요
select *
from (select rownum rn, b.*
from (select * from board order by no desc) b)
where rn between 1 and 10;

select * from board;

update board
set title='제목수정1', content='내용수정1', updated_date=systimestamp
where no=1;

commit;

select no, title, writer, read_count, updated_date from board;

-- pk컬럼에 대해 = 조건은 1개만 출력 : 상세페이지
select * from board where no=1;

-- 조회수 1 증가
update board
set read_count = read_count + 1
where no=1;

select * from board where no=1;

-- 20분 전 데이터 확인 가능 (hour, minute, second)
select * from board as of TIMESTAMP(systimestamp - interval '20' minute)
where no=1;

erd 툴
https://exerd.com/
다운로드 > 이클립스 플러그인으로 설치 eXERD 3.X > 플러그인 설치 및 업데이트 가이드 를 따라하세요

자바 프로젝트 우클릭 > New > Other... > eXERD > eXERD File > 대상 DBMS : Oracle 9i~21c > 프로젝트 선택 후 파일 이름 : test.exerd

alter session set "_oracle_script"=true;
오라클 12c부터 공통계정앞에 c##을 붙이도록 네이밍 규칙이 바뀜
C## 키워드가 붙는 이유는 12c 버전부터 등장하는 CDB, PDB 개념 때문!

위 쿼리문을 실행해주면 11g와 같은 이전에 쓰던 방식으로 돌려준다

https://yjy0755.tistory.com/22

(SYS계정으로접속0921)
-- user 생성
-- nowon / 1234 -- connect, resource, tablespace 사용 권한
alter session set "_oracle_script"=true;
CREATE user nowon IDENTIFIED by 1234;
grant connect, resource, dba to nowon; -- GRANT CONNECT, RESOURCE, DBA TO [계정명];

테이블에 컬럼 추가하기
ALTER TABLE 테이블이름 ADD 추가할컬럼이름 데이터형(데이터크기) 컬럼속성
alter table reply add bno number;

테이블에서 컬럼 삭제하기
ALTER TABLE 테이블이름 DROP COLUMN 추가할컬럼이름
alter table reply drop column bno;

테이블스페이스 'USERS'에 대한 권한이 없습니다.
grant connect, resource, dba to nowon

create table member(
  mno number primary key,
    email VARCHAR2(255) UNIQUE not null,
    pass VARCHAR2(255) not null,
    name VARCHAR2(255) not null,
    created_date TIMESTAMP
);

create table board(
  bno number primary key,
    title VARCHAR2(255) not null,
    content clob not null,
    read_count number ,
    created_date TIMESTAMP,
    mno number constraint board_member_fk REFERENCES member(mno)
);

create table reply(
  rno number constraint reply_pk primary key, -- pk 이름 지정
    content VARCHAR2(255) not null,
    created_date TIMESTAMP DEFAULT systimestamp
);

drop table reply;

alter table reply drop column bno;

alter table reply add bno number not null;
alter table reply add constraint reply_board_fk FOREIGN KEY(bno) REFERENCES board(bno);
alter table reply add mno number not null;
alter table reply add constraint reply_member_fk foreign key(mno) REFERENCES member(mno);

select * from reply;

-- 시퀀스 생성
create sequence seq_mem START with 1 INCREMENT by 1;
create sequence seq_bo START with 1 INCREMENT by 1;
create sequence seq_re START with 1 INCREMENT by 1;

-- 데이터 생성
insert into member(mno, email, pass, name, created_date)
values (seq_mem.nextval, 'test'||seq_mem.currval||'@test.com', '1234', 'test'||seq_mem.currval, systimestamp);

--컬럼 생략하면 순서에 맞게 모두 넣으면 돼요
insert into member
values (seq_mem.nextval, 'test'||seq_mem.currval||'@test.com', '1234', 'test'||seq_mem.currval, systimestamp);

select * from member;

commit;

rollback;

insert into board
values(seq_bo.nextval, '제목'||seq_bo.currval, '내용'||seq_bo.currval, 0, systimestamp,1);

select * from board;

-- ANSI 문법에서 join on 대신 join using
join - using ??
이름이 똑같으면 사용 가능
on b.mno=m.mno
-> using(mno)

to_char(b.created_date,'yyyy-mm-dd')
systimestamp에서 날짜만 추출

-- 글번호, 제목, 조회수, 작성자(이름), 작성일(날짜만)
insert into board
values(seq_bo.nextval, '제목'||seq_bo.currval, '내용'||seq_bo.currval, 0, systimestamp,1);
CREATE INDEX BOARD_MNO_IDX ON BOARD (MNO ASC); -- 인덱스 생성

select * from board;

-- 글번호, 제목, 조회수, 작성자(이름), 작성일(날짜만)
-- Oracle
select b.bno, b.title, b.read_count, m.name, to_char(b.created_date,'yyyy-mm-dd')
from board b, member m
where b.mno=m.mno
order by 1 desc;

-- ANSI
select b.bno, b.title, b.read_count, m.name, to_char(b.created_date,'yyyy-mm-dd')
from board b join member m
using(mno) -- on b.mno=m.mno
order by 1 desc;

-- 스칼라 서브쿼리
select b.bno, b.title, b.read_count 조회수, (select m.name from member m where m.mno=b.mno) name, to_char(b.created_date,'yyyy-mm-dd') 작성일
from board b
order by 1 desc;
-- (select m.name from member m where m.mno=b.mno)

인덱스 생성
CREATE INDEX BOARD_MNO_IDX ON BOARD (MNO ASC);

저작자표시