DAY 30 - ORACLE 연습5 (2024.08.13)

[ ROLL UP ]

 

1학년 - department_name

    1반 총점 or 평균

    2반

    3반

서브 total 구하는 거 - roll up

 

2학년

    1반

    2반

    3반

select department_name, job_title, round(avg(salary), 2) as "Avg_sal"
from employees
join departments using(department_id)
join jobs using(job_id)
group by rollup(department_name, job_title);

 

• 설명: ROLLUP은 계층적인 집계 결과를 반환합니다. 지정된 그룹화 컬럼의 순서에 따라 집계 레벨이 결정다.

• 그룹화 수준:
  1. department_name, job_title로 그룹화된 결과
  2. department_name만으로 그룹화된 결과
  3. 전체 합계 (모든 행의 평균)

 

---

[ CUBE ]

select department_name, job_title, round(avg(salary), 2) as "Avg_sal"
from employees
join departments using(department_id)
join jobs using(job_id)
group by cube(department_name, job_title);

 

• 설명: CUBE는 모든 가능한 조합에 대한 집계를 반환합니다. 지정된 컬럼들로 가능한 모든 집계 수준을 생성한다.
• 그룹화 수준:
  1. department_name, job_title로 그룹화된 결과
  2. department_name만으로 그룹화된 결과
  3. job_title만으로 그룹화된 결과
  4. 전체 합계 (모든 행의 평균)

 

---

[ GRUPING SETS ]

select job_title, round(avg(salary), 2) as "Avg_sal"
from employees
join departments using(department_id)
join jobs using(job_id)
group by grouping sets((job_title), ( ));-- () All Rows의 역할

 

• 설명: GROUPING SETS는 여러 그룹화 옵션을 명시적으로 지정할 수 있다. 이 쿼리에서는 job_title로 그룹화된 결과와 전체 행의 평균만을 반환하도록 설정되었다.
• 그룹화 수준:
  1. job_title로 그룹화된 결과
  2. 전체 합계 (모든 행의 평균)

---

차이점 요약

• ROLLUP: 위에서 아래로 그룹화 수준을 쌓아가며 계층적인 집계 결과를 생성한다.
• CUBE: 가능한 모든 조합에 대한 집계 결과를 반환한다.
• GROUPING SETS: 명시적으로 지정한 그룹화 수준들만 반환한다.

 

---

[테이블생성]

create table 테이블명(컬럼명1 컬럼타입 [제약조건],

                                   컬럼명2 컬럼타입 [제약조건],.....);

 

- 문자로 시작(30자이내) : 영문 대소문자, 숫자, 특수문자( _ , $ , # ), 한글

- 중복되는 이름은 사용 안됨

- 예약어(create, table, column등)은 사용할 수 없다

 

---

- 자료형 number :

• number(전체자리, 소수이하), number → 숫자형(가변형) - 정수 실수 따지지 않는다.
• int : 정수형 숫자(고정형)
• varchar/varchar2 : 문자, 문자열(가변형) → 최대 4000byte 
• char : 문자, 문자열(고정형) → 2000byte - 문자, 문자열 가리지 않는다.
• date : 날짜형
• clob : 문자열 → 최대4GB
•  blob : 바이너리형(그림, 음악, 동영상..) → 최대4GB

 

---

- 제약조건

• not null : 해당 컬럼에 NULL을 포함되지 않도록 함 (컬럼)
• unique : 해당 컬럼 또는 컬럼 조합 값이 유일하도록 함 (컬럼, 테이블)
• primary key : 각 행을 유일하게 식별할 수 있도록 함 (컬럼, 테이블)
• references table(column):해당 컬럼이 참조하고 있는 (부모)테이블의 특정 (컬럼, 테이블) 컬럼 값들과 일치하거나 또 는 NULL이 되도록 보장함
•  check : 해당 컬럼에 특정 조건을 항상 만족시키도록 함 (컬럼, 테이블)
• [참고] primary key = unique + not null (중복 X / NULL 값 허용 X)

---

- 제약조건 확인

• constraint_name:이름
• constraint_type:유형

             P : primary key

             U : unique

             R : reference

             C : check, not null

 

• search_condition : check조건 내용
• r_constraint_name : 참조테이블의 primary key 이름
• delete_rule : 참조테이블의 primary key 컬럼이 삭제될 때 적용되는 규칙 (no action, set null, cascade등)

---

create table test(
id number(5),
name char(10),
address varchar2(50));

---

 

ex) idx                  일련번호                 primary key 

      id                    아이디                     unique

      name              이름                        not null

      phone             전화번호                 

      address           주소

      score               점수                        check

      subject_code  과목코드

      hire_date         입학일                     기본값 (오늘날짜)

      marriage          결혼                         check

---

create table user1(
idx number primary key,
id varchar2(10) unique,
name varchar2(10) not null,
phone varchar2(15),
address varchar2(50),
score number(6,2) check(score >= 0 and score <= 100),
subject_code number(5),
hire_date date default sysdate,
marriage char(1) default 'N' check(marriage in('Y', 'N')));

 

---

select constraint_name, constraint_type
from user_constraints
where table_name='USER1';

 

제약조건이 코드로 나오기 때문에

P는 primary key / U는 unique인 거까지는 알아도

나머지 C는 check인지 not null인지 알기 힘들다.

 

---

create table user2(
idx number constraint PKIDX primary key,
id varchar2(10) constraint UNID unique,
name varchar2(10) constraint NOTNAME not null,
phone varchar2(15),
address varchar2(50),
score number(6,2) constraint CKSCORE check(score >= 0 and score <= 100),
subject_code number(5),
hire_date date default sysdate,
marriage char(1) default 'N' constraint CKMARR check(marriage in('Y','N')));

 

 테이블을 생성할 때 제약조건 이름도 같이 생성하는 것이다.

---

select constraint_name, constraint_type
from user_constraints
where table_name='USER2';

 

이제는 제약조건이 설정했던 이름으로 나오기 때문에 무슨 타입인지 알 수 있다 !

---

 

select *
from user_constraints
where table_name='USER2';

 

이렇게 출력하면 제약조건이 무엇인지에 대한 다른 내용도 같이 볼 수 있다.

 

---

[ insert ]

   : 테이블에 데이터(새로운 행)추가

   

    insert into 테이블명 [ (column1, column2, .....)]       values (value1,value2,.....)

       - column과 values의 순서일치

       - column과 values의 개수 일치

 

---

[ update ]

    : 테이블에 포함된 기존 데이터수정

      전체 데이터 건수(행수)는 달라지지 않음

      조건에 맞는 행(또는 열)의 컬럼값을 갱신할 수 있다

 

     update 테이블명 set 컬럼명1=value1, 컬럼명2=value2 .....     [where 조건절]

           - where 이 생략이 되면 전체행이 갱신

            - set절은 서브쿼리 사용가능, default옵션 사용가능

 

---

[ delete ]

     : 테이블에 포함된 기존데이터를 삭제

       행 단위로 삭제되므로 전체행수가 달라짐

 

       delete [from] 테이블명 [where 조건절];

            - where을 생략하면 전체행이 삭제됨

            - 데이터는 삭제되고 테이블 구조는 유지됨

---

Lock

 → insert / delete / update  → commit

 

drop과 delete 차이점

---

[ truncate ]

     : 테이블의 데이터를 전부 삭제하고 사용하고 있던 공간을 반납

        해당 테이블의 데이터가 모두 삭제되지만 테이블 자체가 지워지는 것은 아님

        해당 테이블에 생성되어 있던 인덱스도 함께 truncate 됨

 

       TRUNCATE TABLE 테이블명;

 

DELETE 후에는 데이터만 지워지고 쓰고 있던 디스크 상의 공간은 그대로 가지고 있다.

 

TRUNCATE 작업은 최초 테이블이 만들어졌던 상태, 즉 데이터가 1건도 없는 상태로 모든 데이터 삭제, 칼럼 값만 남아 있다.

그리고 용량도 줄고 인덱스 등도 모두 삭제된다.

 

→ DELETE보다 TRUNCATE가 더 좋아 보이나 DELETE는 where를 이용하여 원하는 데이터만 골라서 삭제가 가능하나 TRUNCATE는 조건을 사용할 수 없다.

또 DDL이기 때문에 사용권한 문제도 있다.

 

DROP 명령어는 데이터와 테이블 전체를 삭제하게 되고 사용하고 있던 공간도 모두 반납하고 인덱스나 제약조건 등 오브젝트로 삭제된다.

---

[ transaction처리 ]

→ commit 하기 이전까지 sql 명령어들

 

    : 일의 시작과 끝이 완벽하게 마무리(commit)

       처리도중 인터럽트(interrupt:장애)가 발생하면 되돌아옴(rollback)

 

---

ex6) 데이터 값 추가 ( insert into )

insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(1,'aaa','kim','010-000-0000','서울',75,100,'2010-08-01','Y');

---

insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(1,'aaa','kim','010-000-0000','서울',75,100,'2010-08-01','Y');

 

---

insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(2,'aaa','kim','010-000-0000','서울',75,100,'2010-08-01','Y');

---

insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(2,'bbb','','010-000-0000','서울',75,100,'2010-08-01','Y');

---

insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(2,'bbb','lee','010-000-0000','서울',120,100,'2010-08-01','Y');

---

insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(2,'bbb','lee','010-000-0000','서울',75,100,'2010-08-01','K');

---

insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(2,'bbb','lee','010-000-0000','서울',75,100,'2010-08-01','N');

---

ex7) 테이블 목록 확인

select * from tab;

---

ex8) 테이블의 레코드(내용) 확인

select * from user1;

---

select * from user2;

---

ex9) 테이블의 구조 확인

desc user1;

 

---

describe user1;

---

ex10) 시퀀스 목록 확인

 select * from user_sequences;

---

ex11) 테이블명 변경 : test → user3

alter table test rename to user3;

---

ex12) 컬럼 추가 : user3 → phone  varchar2(15)

 alter table user3 add phone varchar2(15);

 desc user3;

---

ex13) 제약조건 추가 : user3 → id에 unique, 제약조건명 UID2

alter table user3 add constraint UID2 unique(id);

select constraint_name, constraint_type
from user_constraints
where table_name='USER3';

---

ex14) 제약조건 삭제 - alter table 테이블명 drop constraint 제약조건명;

alter table user3 drop constraint UID2;

alter table user3 DROP constraint SYS_C007693;

select *
from user_constraints
where table_name='USER3';

 

---

ex15) 컬럼 추가 : user3 → no number    (PK 설정)

alter table user3 add no number primary key;

desc user3;

---

ex16) 구조 변경 : user3 → name   char(10)를 varchar2(10)로 바꿈 

alter table user3 modify name varchar2(10);

desc user3;

---

ex17) 컬럼 삭제 : user3 → address

alter table user3 drop column address;

desc user3;

---

ex18) 테이블 삭제 / 휴지통비우기 : user3

 

[ 테이블 삭제 ]

drop table user3;

select * from tab;

---

[ 휴지통 비우기 ]

purge recyclebin; -- 휴지통 비우기

---

[ 휴지통에 넣지 않고 바로 삭제하기 ] 

drop table user1 purge; -- 휴지통에 넣지 않고 바로 삭제

select * from tab;

---

drop table user2;

select * from tab;

---

[ 휴지통 보기 ]

show recyclebin;

---

[ 휴지통에서 되살리기 ]

flashback table user2 to before drop; -- 휴지통에서 되살리기

flashback table "BIN$cEf2dC1fRhilpiULWNRf3A==$0" to before drop;

---

[ 휴지통에 테이블 정보 검색 ]

select * from recyclebin; -- 휴지통에 테이블 정보 검색

---

ex19) 시퀀스 생성 / 삭제

[ 시퀀스 생성 ]

create sequence idx_sql increment by 2 start with 1 maxvalue 9 cycle nocache;

---

[ 다음 시퀀스 값 표시 ] - nextval

select idx_sql.nextval from dual; -- 다음 시퀀스값 표시(nextval)

---

[ 현재 시퀀스 값 표시 ] - currtval

lect idx_sql.currval from dual; -- 현재 시퀀스값 표시(currtval)

select * from user_sequences;

---

[ 시퀀스 삭제 ]

drop sequence idx_sql;

select * from user_sequences;

---

ex20) 테이블 생성과 시퀀스 적용

[ 테이블 생성 ]

create table book(
no number primary key,
subject varchar2(50),
price number, year date);

---

[ 시퀀스 생성 ]

create sequence no_seq increment by 1 start with 1 nocycle nocache;

 

   increment by / start with은 생략 가능 

   nocache / nocycle은 넣어주는게 좋다 !!

---

[ 데이터 값 넣기 ]

insert into book(no, subject, price, year)
values(no_seq.nextval, '오라클 무작정 따라하기', 10000, sysdate);

insert into book(no, subject, price, year)
values(no_seq.nextval, '자바 3일 완성', 15000, '2007-03-01');

insert into book values(no_seq.nextval, 'JSP 달인 되기', 18000, '2010-01-01');

---

select * from book;

---

ex21) 테이블 구조만 복사 [ where 1 = 0; ]

create table user3 as select * from user2 where 1=0;

desc user3;

---

select constraint_name, constraint_type, search_condition
from user_constraints
where table_name='USER2';

   USER2에는 제약조건이 5개가 보인다

---

select constraint_name, constraint_type, search_condition
from user_constraints
where table_name='USER3';

← not null을 제외하고는 제약조건이 복사 안됨

← not null 제약조건도 sys_*****로 복사됨 (제약조건명 그대로 복사가 안된다)

 

---

ex22) 테이블 레코드 복사

(idx → bunho, name → irum, address → juso) 을 복사하고 id가 bbb인 레코드 복사

create table user4(bunho, irum, juso) 
as select idx, name, address from user1 where id='bbb';

---

select * from user1;

 

---

select * from user4;

---

- 삭제 RULE

• on delete cascade : 대상 데이터를 삭제하고, 해당 데이터를 참조하는 데이터도 삭제

 

• on delete set null : 대상 데이터를 삭제하고, 해당 데이터를 참조하는 데이터는 NULL로 바꿈

     

• on delete restricted : 삭제대상 데이터를 참조하는 데이터가 존재하면 삭제할 수 없음 (기본값)

---

- 수정 RULE

•  on update cascade: 대상 데이터를 수정하면, 해당 데이터를 참조하는 데이터도 수정'

---

ex23) 테이블 생성 후 행 추가

테이블명 : dept

deptno      number       → 기본키, 제약조건명(DNO)

dname      varcahr2(30) → 널 허용 안됨, 제약조건명(DNAME)

 

테이블명 : emp

empno       number        → 기본키, 제약조건명(ENO)

ename       varchar2(30) → 널 허용 안됨, 제약조건명(ENAME)

deptno        number → 외래키, 제약조건명(FKNO),

                                      대상데이터를 삭제하고 참조하는 데이터는 NULL로 바꿈

---

create table dept(
deptno number constraint DNO primary key,
dname varchar2(30) constraint DNAME not null);

---

create table emp(
empno number constraint ENO primary key,
ename varchar2(30) constraint ENAME not null,
deptno number, constraint FKNO foreign key(deptno) references dept on delete set null);

 

 삭제됐을 때 set null로 만드는 조건 확인하기 !

---

insert into dept(deptno, dname) values(10, '개발부');
insert into dept(deptno, dname) values(20, '영업부');
insert into dept(deptno, dname) values(30, '관리부');

insert into dept(dname) values(40, '경리부');-- ORA-00913: 값의 수가 너무 많습니다.

  컬럼 수 부족이므로 error !!

nsert into dept(deptno, dname) values(40, '경리부');

---

insert into emp(empno, ename, deptno) values(100, '홍길동', 10);
insert into emp(empno, ename, deptno) values(101, '라이언', 20);

insert into emp(empno, ename, deptno) values(102, '튜브', 50); 
-- 50번부서 없음(무결성제약조건위배) - 부모키가 없습니다

   부모인 dept 테이블에 50이라는 deptno가 없기 때문에 error 발생 !

---

insert into emp(empno, ename, deptno) values(103, '어피치', 40);
insert into emp(empno, ename) values(105, '프로도');

insert into emp(ename, deptno) values('콘', 10); -- primary key는 NULL허용 안함

  dept에서 primary key로 설정했던 10에는 null값이 들어가면 안되므로 error 발생 !

commit;

---

ex24) 삭제

delete from dept;

 

select * from dept;

---

rollback;

select * from dept;

---

delete from dept where deptno=40;

select * from dept;

---

select * from emp; -- 40번 부서 컬럼에 (null)이 들어간다.

 

 참조하고 있는 테이블의 데이터가 삭제되면 null값으로 되게 설정했으므로  null 값이 들어간 것을 볼 수 있다.

---

ex25) 수정(update) - emp테이블 장동건 사원의 부서번호를 30으로 수정하시오

update emp set deptno=30 where ename='프로도';

select * from emp;

commit;