오라클 데이터베이스
sql 4일차(0713)
피아마수
2023. 7. 13. 13:04
POJO
Plain Old Java Object
순수한 자바 클래스 , 어떤라이브러리도 의존하지 않는 클래스
바닐라 스크립트도 이런 느낌
--자기 부서의 평균 월급보다 더 많은 월급을 받는 사원의 사번, 이름, 부서번호,
--부서별 평균월급 출력하세요
--hint) 만약에 부서번호와 부서의 평균월급을 담고있는 테이블이 존재한다면 (메모리에)
from 절에서 inline view를 사용하면 이제 별칭을 써야하는 데 여기에는 as를 사용할 수 없다.
inline view로 정의한 것도 테이블이기 때문에 select 에 별칭을 지정해서 컬럼같이 사용한다.
PL/SQL
실행후에는 주석처리해야함 다른게 실행이 안됨 , 실행시 블럭 걸고 실행버튼, ctrl+Enter 안먹음
BEGIN
FOR i IN 1..100 LOOP-- 1부터 100까지 돌려라
insert into temp2(id) values('A' || to_char(i));
END LOOP; -- 세미콜론 주의
END; -- 세미콜론주의대량 데이터 삽입
insert into temp5(num)
select id from temp4;대량의 데이터를 통해서 테이블만들기
create table copyemp
as
select * from emp;
틀만 복제하기 항상 거짓인 값을 where 절에 넣는다
create table copyemp3
as
select * from emp where 1=2;--보안 공격코드 항상 참이되는 값
--항상 거짓인거 하면 틀만 복사됨kisa사이트 들어가면 보안취약점을 보안 하기 위한 코드들이 정리되 어 있어서 갖다 쓰기만 하면된다
https://www.kisa.or.kr/skin/doc.html?fn=20220104_2030303_9.pdf&rs=/result/2022-01
Document Viewer
www.kisa.or.kr
--oracle 11g >> 실무 >> 가상컬럼(조합컬럼)
--a 학생성적 테이블(학번, 국어, 영어, 수학)> 합계, 평균 로직
--b 학생성적 테이블(학번, 국어, 영어, 수학, 총점, 평균) >> 매번 합계, 평균
--점수하고 합계 그리고 평균
--문제점 :국어 ,영어, 수학 변경되면...총점과 평균도 다시 계산해야함...
--무결성을 보장하려면 >> 합계, 평균 수정 보장해야함
create table vtable(
no1 number,
no2 number,
no3 number GENERATED ALWAYS as (no1+ no2) VIRTUAL --가상컬럼(조합컬럼)
);
가상컬럼에서 case로 사용하기
create table vtable2(
no number, --순번
p_code char(4), --제품코드 (A001 , B003)
p_date char(8), --입고일 (20230101)
p_qty number, --수량
p_bungi number(1) GENERATED ALWAYS as (
case when substr(p_date,5,2) in ('01','02','03') then 1
when substr(p_date,5,2) in ('04','05','06') then 2
when substr(p_date,5,2) in ('07','08','09') then 3
else 4
end
) VIRTUAL
);ALTER
테이블 생성 후에 컬럼 추가하기
alter table temp6 add(ename varchar2(20));기존 테이블에 있는 기존 컬럼 이름 변경(ename -> username)
alter table temp6 rename column ename to username;기존 테이블에 있는 기존 컬럼의 데이터 크기 수정
alter table temp6 modify (username varchar2(100));기존 테이블에 있는 기존 컬럼 삭제하기
alter table temp6 drop column username;
delete
delete는 데이터만 삭제한다 dB의 테이블은 데이터를 넣으면 데이터 크기만큼 테이블의 크기 증가하는 데 delete 명령어를 통해서 삭제를해도 테이블의 크기는 그대로이다.
truncate를 사용하면 delete의 단점을 보완할 수 있다. 하지만 이는 where절을 사용할 수 없으므로 테이블을 통째로 날려버리기 때문에 주의해야한다.
table 삭제는 drop을 사용한다.
index
DB나 JAVA, 웹의 한페이지를 처리하려면 8kbyte가 들기 때문에 index 없이 전체를 순회하려면 IO처리 손해를 많이 본다
검색에 있어서는 인덱스가 있으면 좋지만 DML 입장에서는 insert,update, delete할때마다 다시 reordering해야하기 때문에 성능저하된다
nvarchar(2000) - > 한글 영문 상관없이 2byte씩 차지하게 작성한다. 따라서 이것의 최대 크기는 4000byte이다.
create ~as는 데이터만 복사할 뿐 not null , primarykey 등등 제약조건은 가져오지 않는다.
0713일차
--20번 부서의 사원중에서 가장많은 급여를 받는 사원보다 더 많은 급여를 받는 사원의
--사번, 이름, 급여, 부서번호를 출력하세요
select empno, ename, sal, deptno
from emp
where sal > (select max(sal) from emp where deptno=20);
--스칼라 서브 쿼리
--join안해도 앨리어스 붙일 수 있다.
--당장 select 절에서 join을 수행 할 수 있다. from을 먼저 실행하고 select 절을 실행하기 때문이다.
select e.empno, e.ename, e.deptno,(select d.dname from dept d where d.deptno=e.deptno) as deptname
from emp e
where e.sal >=3000;
--자기 부서의 평균 월급보다 더 많은 월급을 받는 사원의 사번, 이름, 부서번호,
--부서별 평균월급 출력하세요
--hint) 만약에 부서번호와 부서의 평균월급을 담고있는 테이블이 존재한다면 (메모리에)
select e.empno,e.ename,e.deptno, e.sal, a.avgsal
from emp e join (select deptno, avg(sal) as avgsal from emp group by deptno) a on e.deptno=a.deptno
where e.sal > a.avgsal;
--1. 'SMITH'보다 월급을 많이 받는 사원들의 이름과 월급을 출력하라.
select ename, sal
from emp
where sal>(select sal from emp where ename='SMITH');
--2. 10번 부서의 사원들과 같은 월급을 받는 사원들의 이름, 월급,
-- 부서번호를 출력하라.
select ename, sal, deptno
from emp
where sal in(select sal from emp where deptno=10);
--3. 'BLAKE'와 같은 부서에 있는 사원들의 이름과 고용일을 뽑는데
-- 'BLAKE'는 빼고 출력하라.
select ename, hiredate
from emp
where deptno in(select deptno from emp where ename='BLAKE') and ename != 'BLAKE';
--4. 평균급여보다 많은 급여를 받는 사원들의 사원번호, 이름, 월급을
-- 출력하되, 월급이 높은 사람 순으로 출력하라.
select empno, ename, sal
from emp
where sal > (select avg(sal) from emp)
order by sal desc;
--5. 이름에 'T'를 포함하고 있는 사원들과 같은 부서에서 근무하고
-- 있는 사원의 사원번호와 이름을 출력하라.
select empno, ename
from emp
where deptno in(select deptno from emp where ename like '%T%');
--6. 30번 부서에 있는 사원들 중에서 가장 많은 월급을 받는 사원보다
-- 많은 월급을 받는 사원들의 이름, 부서번호, 월급을 출력하라.
--(단, ALL(and) 또는 ANY(or) 연산자를 사용할 것)
select ename, deptno, sal
from emp
where sal> (select max(sal) from emp where deptno=30);
select ename, deptno, sal
from emp
where sal> ALL(select sal from emp where deptno=30);
--where sal>1600 and sal>1250 and sal > 2850 and sal>1500 and sal >950
--ANY
--where sal>1600 or sal>1250 or sal > 2850 or sal>1500 or sal >950
--7. 'DALLAS'에서 근무하고 있는 사원과 같은 부서에서 일하는 사원의
-- 이름, 부서번호, 직업을 출력하라.
select ename, deptno, job
from emp
where deptno in(select deptno from dept where loc='DALLAS');
--8. SALES 부서에서 일하는 사원들의 부서번호, 이름, 직업을 출력하라.
select deptno, ename, job
from emp
where deptno in(select deptno from dept where dname='SALES');
--9. 'KING'에게 보고하는 모든 사원의 이름과 급여를 출력하라
--king 이 사수인 사람 (mgr 데이터가 king 사번)
select ename, sal
from emp
where mgr in(select empno from emp where ename='KING');
--10. 자신의 급여가 평균 급여보다 많고, 이름에 'S'가 들어가는
-- 사원과 동일한 부서에서 근무하는 모든 사원의 사원번호, 이름,
-- 급여를 출력하라.
select empno,ename, sal
from emp
where sal > (select avg(sal) from emp) and deptno in(select deptno from emp where ename like '%S%');
--select * from emp
--where deptno in (
-- select deptno from emp where sal > (select avg(sal) from emp)
-- and ename like '%S%'
-- )
--11. 커미션을 받는 사원과 부서번호, 월급이 같은 사원의
-- 이름, 월급, 부서번호를 출력하라.
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
FROM EMP
WHERE COMM IS NOT NULL)
AND SAL IN(SELECT SAL
FROM EMP
WHERE COMM IS NOT NULL);
--12. 30번 부서 사원들과 월급과 커미션이 같지 않은
-- 사원들의 이름, 월급, 커미션을 출력하라.
SELECT ENAME, SAL, COMM
FROM EMP
WHERE SAL NOT IN(SELECT SAL
FROM EMP
WHERE DEPTNO=30)
AND COMM NOT IN(SELECT NVL(COMM, 0)
FROM EMP
WHERE DEPTNO=30 and comm is not null);
--------------------------------------------
--TIP)
select * from emp order by 1,2,3;
--오름차순 이렇게
select * from emp order by 1 desc,2,3;
/*
지금 까지 초급 개발자 해야 하는 필수 사항
select 5
from 1
where 2
group by 3
having 4
order by 6
+
기본함수 (문자 + 숫자 + 날짜 + 변환(to_) + 일반 + 집계)
+
하나 이상의 테이블 (union , join)
+
subquery
+
DML (insert , update , delete) 무조건 암기
여기까지 필수 사항 .....
필수!!
문자
숫자 round , trunc
날짜 날짜 - 날짜 = 숫자
변환 to_char,...
일반 nvl , decode , case
집계 sum,avg,...
하나이상의 테이블 (union, join)
+ subquery
+ DML(insert, update, delete) 무조건 암기
--DML (오라클.pdf 10장 168page, 교재 265page
*/
/*
DDL (데이터 정의어) : create, alter, drop , truncate... rename, modify
DML (데이터 조작어) : 트랜잭션을 일으키는 작업, commit전까지는 lock이 걸림
Tip)
데이터가 select...잘되고 ...insert, update, delete 안됨
commit , rollback 안함
다같이 rollback 해보는게 낫겠다 이럴때는
자바코드에서 실행 할때는 auto commit이기때문에 괜춘. sql에서 다룰 때만 문제가 있는거
1. 어떤사람이 commit, rollback(dml하고) 수행하지 않은 경우
2. log write누가 뭘 바꿨는지 log가 써진다. (누구, 쿼리, 수행)기록
3. 하드 디스크에 log 값들이 기록된다. 설치할때 log파일의 크기를 지정하면 여기에 쓰인다.
>>DBA 주기적으로 backup>>log 자동삭제
백업시 로그 자동삭제
4. log file이 가득차서 insert, update, delete가 안된다는거
결론 : commit, rollback을 잘하자
DQL : 데이터 질의어 : select
DCL : 데이터 제어어 : grant, revoke
TCL : commit, rollback , savepoint
*/
--테이블 또는 컬럼의 정보를 볼수 있다.
desc emp;
select * from tab; -- KOSA계정이 접근할 수 있는테이블 목록
--테이블에 대한 정보를 보여줌
select * from tab where tname='EMP';
--emp가 갖고 있는 컬럼의 정보
select * from col where tname='EMP';
----------------------------------------------------
--INSERT
create table temp(
id number primary key, --not null , unique ( ID , 주민번호)
name nvarchar2(10)
);
desc temp;
--1.일반적인 insert
insert into temp(id,name) values(100,'홍길동');
select * from temp;
--의사결정 : 실반영, 취소
commit;
--2. 컬럼 목록 생략 insert (쓰지 마세요)
--테이블의 컬럼을 모두 다 써야한다
insert into temp values(200,'김유신');
commit;
insert into temp values(300); --SQL 오류: ORA-00947: not enough values
insert into temp(id,name)
values(100,'개똥이'); --ORA-00001: unique constraint (KOSA.SYS_C007000) violated
--TIP
--SQL 은 프로그램 적인 요소 없다(변수(X), 제어문(X))
--PL-SQL변수, 제어문
create table temp2(id varchar2(50));
desc temp2;
--PL-SQL
--for(int i =0;i<100;i++){}
--for(let i=0;i<100;i++){}
--실행후에는 주석처리해야함 다른게 실행이 안됨 , 실행시 블럭 걸고 실행버튼, ctrl+Enter 안먹음
--BEGIN
-- FOR i IN 1..100 LOOP-- 1부터 100까지 돌려라
-- insert into temp2(id) values('A' || to_char(i));
-- END LOOP; -- 세미콜론 주의
--END; -- 세미콜론주의
select * from temp2;
create table temp3(
memberid number(3) not null, --3자리 정수
name varchar2(10), --null 허용
regdate date default sysdate -- 데이터 기본값 설정(insert 되지 않으면 서버 날짜를 기본으로 insert)
);
desc temp3;
select sysdate from dual;
insert into temp3(memberid,name) values(100,'홍길동'); --good
commit;
select * from temp3;
insert into temp3(memberid, name, regdate)
values(200,'김유신','2023-07-13'); -- 하지말자(가능하지만)>> default활용
create table temp4(id number);
create table temp5(num number);
desc temp4;
desc temp5;
insert into temp4(id) values(1);
insert into temp4(id) values(2);
insert into temp4(id) values(3);
insert into temp4(id) values(4);
insert into temp4(id) values(5);
insert into temp4(id) values(6);
insert into temp4(id) values(7);
insert into temp4(id) values(8);
insert into temp4(id) values(9);
insert into temp4(id) values(10);
commit;
select * from temp4;
select * from temp5;
--1. 대량 데이터 삽입하기
--temp4에 있는 모든 데이터를 temp5 넣고 싶어요
insert into temp5(num)
select id from temp4;
commit;
--2. 대량 데이터 삽입하기
--copyemp 테이블 자동생성 (emp 과 동일 스키마) emp 데이터 insert
--단점) 제약정보는 복세(PK,FK)
--순수한 구조 만들고 + 데이터 복제
create table copyemp
as
select * from emp;
--();
create table copyemp2
as
select empno, ename from emp
where deptno=20;
select * from copyemp2;
--틀만(스키마) 구조 복제하고 데이터는 복제하고 싶지 않다
create table copyemp3
as
select * from emp where 1=2;--보안 공격코드 항상 참이되는 값
--항상 거짓인거 하면 틀만 복사됨
select *from copyemp3;
-------------------insert end----------------------------------
--UPDATE
/*
update 테이블명
set 컬럼명 =value, 컬럼명 = value,...
where 조건절
update 테이블명
set 컬럼명 = (subquery)
where 컬럼명 = (subquery)
*/
select * from copyemp;
update copyemp
set sal=0;
select * from copyemp;
commit;
update copyemp
set sal =5555
where deptno=20;
update copyemp
set sal=(select sum(sal) from emp);
commit;
update copyemp
set ename='AAA', job='BBB', hiredate=sysdate, sal=(select max(sal) from emp)
where empno=7788;
select * from copyemp where empno=7788;
-----------------------UPDATE END----------------------
--delete
delete from copyemp;
select * from copyemp;
rollback;
delete from copyemp where deptno in(10,20);
select * from copyemp;
commit;
---------------------------DELETE END------------------------
/*
개발자로서 DB....
1.CRUD(create > insert , read > select , update , delete)
2.R (select) > 함수, 조인, 서브쿼리... 70%
패턴 : MVC 개발(협업을 위해서 할일을 나누기 위함)
model: DTO(VO)(data transfer object), DAO(db연결, crud를 만드는 클래스), SERVICE
view : html, jsp
controller : servlet(웹용 자바 파일) , 웹브라우저는 html, css, js, xml, json만 읽을 수있다
경찰서 상황실같은느낌 , 호출하는 함수를 때에 따라 변경해준다.
DAO(db연결, crud)
기본적인 함수를 만드는 클래스
APP - DB
하나의 테이블에 대해서
1. 전체 데이터
2. 1건 데이터
3. 삽입
4. 수정
5. 삭제
emp 테이블 가지고 있어요
select * from emp
select * from emp where empno=?
insert into emp(empno,ename...) values (?,?,...)
update emp set ename=? and job =? where empno=?
delete from emp where empno=?
5가지 쿼리를 처리 할 수 있는 class 만드는 것이 목적
class Emp{empno,ename,,,,}
class EmpDao{
//DB연결
public List<Emp>
public List<Emp> empAllList(){
//select * from emp;
return null;
}
public Emp empByEmpno(int empno){
//select * from emp where empno=?
return null;
}
public int insert(Emp emp){
// insert into emp(empno,ename..) values(?,?...)
return null;
}
}
*/
--------------------------------------------------------------
--DDL
--테이블 생성하기 (오라클. pdf 9장 page 138)
--교재 310page
create table board(
boardid number,
title nvarchar2(50),
content nvarchar2(2000), --4000byte,
regdate date
);
desc board;
--제약정보
select * from user_constraints where table_name='BOARD';
select * from user_constraints where table_name='EMP';
--oracle 11g >> 실무 >> 가상컬럼(조합컬럼)
--a 학생성적 테이블(학번, 국어, 영어, 수학)> 합계, 평균 로직
--b 학생성적 테이블(학번, 국어, 영어, 수학, 총점, 평균) >> 매번 합계, 평균
--점수하고 합계 그리고 평균
--문제점 :국어 ,영어, 수학 변경되면...총점과 평균도 다시 계산해야함...
--무결성을 보장하려면 >> 합계, 평균 수정 보장해야함
create table vtable(
no1 number,
no2 number,
no3 number GENERATED ALWAYS as (no1+ no2) VIRTUAL --가상컬럼(조합컬럼)
);
insert into vtable(no1, no2) values(100,200);
select * from vtable;
update vtable set no1 = 2000;
select * from vtable;
commit;
--실무에서 활용되는 코드
--제품 ..입고 : 분기별 데이터 추출 (4분기 기준)
create table vtable2(
no number, --순번
p_code char(4), --제품코드 (A001 , B003)
p_date char(8), --입고일 (20230101)
p_qty number, --수량
p_bungi number(1) GENERATED ALWAYS as (
case when substr(p_date,5,2) in ('01','02','03') then 1
when substr(p_date,5,2) in ('04','05','06') then 2
when substr(p_date,5,2) in ('07','08','09') then 3
else 4
end
) VIRTUAL
);
insert into vtable2(p_date) values('20230101');
insert into vtable2(p_date) values('20230201');
insert into vtable2(p_date) values('20230301');
insert into vtable2(p_date) values('20230601');
insert into vtable2(p_date) values('20230422');
insert into vtable2(p_date) values('20230501');
insert into vtable2(p_date) values('20231101');
insert into vtable2(p_date) values('20231201');
commit;
select * from vtable2 where p_bungi=2;
--테이블 만들고 수정 삭제
create table temp6(id number);
desc temp6;
--2.테이블 생성 후에 컬럼 추가하기
alter table temp6 add(ename varchar2(20));
--3. 기존 테이블에 있는 기존 컬럼 이름 변경(ename -> username)
alter table temp6 rename column ename to username;
--4. 기존 테이블에 있는 기존 컬럼의 데이터 크기 수정
-- 종종....
--테이블 입장에서 생각해서 쓰는 쿼리
alter table temp6 modify (username varchar2(100));
desc temp6;
--5. 기존 테이블에 있는 기존 컬럼 삭제하기
alter table temp6
drop column username; --commit, rollback없이도 바로 반영되니 조심해야함
desc temp6;
--이 모든 작업은 TOOL 통해서 가능
--그러나 뽀다구 있게 작업 하실려면 코드사용하자
--6 테이블 전체가 필요 없어요
--6.1 delete 데이터만 삭제
--테이블 처음 만들면 크기 설정 >> 데이터 넣으면 >> 데이터 크기 만큼 테이블 크기 증가
--처음 1M >> 데이터 10만건(insert)>> 100M >> 10만건 삭제 >> 테이블의 크기 100M
--테이블 데이터 삭제(공간의 크기도 줄일 수 없을까)
--truncate(단점 : where 절 사용하지 못해요)
--truncate table emp; -- DBA가...
--테이블 삭제
drop table temp6;
desc temp6;
--------------------------기본 DDL END--------------------------------
--테이블에 제약 설정하기
--oracle pdf 144page,
--오라클 교재 399page
/*
제 약 조 건 설 명
PRIMARY KEY(PK) : 유일하게 테이블의 각행을 식별(NOT NULL 과 UNIQUE 조건을 만족)
FOREIGN KEY(FK) : 열과 참조된 열 사이의 외래키 관계를 적용하고 설정합니다.
UNIQUE key(UK) : 테이블의 모든 행을 유일하게 하는 값을 가진 열(NULL 을 허용)
NOT NULL(NN) : 열은 NULL 값을 포함할 수 없습니다.
CHECK(CK) : 참이어야 하는 조건을 지정함(대부분 업무 규칙을 설정)
*/
/*
PRIMARY KEY(PK) : not null 하고 unique한 제약 >> null값과 중복 값은 안돼요
--PK 보장(유일값) where empno=7788>>데이터 1건 보장
--PK 데이터 (주민번호, 순번, 회원 ID, 학번)
PK 어떤 컬럼에 .... 그 컬럼은 where 조건에 자주 사용된다.
성능.. 검색 속도 ... 보장해야함
DB 서버는 그 컬럼의 자동으로 index를 생성한다.
한페이지를 지날때마다 8kbyte 이기때문에 IO 처리 손해를 많이 본다.
*/
--검색에 있어서는 인덱스가 있으면 좋지만 dml 입장에서는 다시 reordering해야하기 때문에 성능저하