sql 5일차(0714)

https://www.exerd.com/down.do

eXERD

평가판 다운로드

이메일 다운로드

현업에서 create table하지않는다 ERD프로그램을 통해서 다이어그램을 그리면 자동으로 테이블을 생성한다.

테이블을 먼저 만들고 제약조건을 나중에 만드는 형태로 생성된다.

 

https://www.visual-paradigm.com/

 

--unique제약조건을 걸면 중복값은 들어갈수 없다. 근데 null값은 들어갈 수있다. null은 중복 가능!!!!주의하자

create table temp8(
    id number constraint pk_temp8_id primary key,
    name varchar2(20) not null,
    jumin nvarchar2(6) constraint uk_temp8_jumin unique,
    addr varchar2(50)
);

insert into temp8(id,name,addr) values(20,'아무개','서울');
insert into temp8(id,name,addr) values(30,'김유신','서울');

 

--null을 허용하고 싶지 않아요 (not null)을 써주면 된다.

jumin nvarchar2(6) not null constraint uk_temp8_jumin unique,

--테이블 생성 후에 제약 걸기 (추천)

alter table temp9
add constraint pk_temp9_id primary key(id);

 

 

--복합키설정

create table temp91(num number, empno number , ename varchar2(20));
alter table temp91
add constraint pk_temp91_num_empno primary key(num,empno);

복합키 설정시 단점:

--복합키로 설정하면 검색할때 where절에서 유일값을 고르려면 두가지를 모두 검색해야한다.
--where num=1 and empno=7788;--유일한 한개의 row 반환시.. pk 컬럼 모두명시

 

 

--주의 not null 제약이지만 추가 할때는 다른 문법이다.

alter table temp9 add ename varchar2(50);
alter table temp9 modify(ename not null);

--modify는 타입수정할때 쓰는거였는데 

 

--check 제약(업무 규칙 : where 조건을 정의)
--where gender in('남','여')
--제약조건은 해킹을 막기 위함도 있음

 

create table temp10(
    id number constraint pk_temp10_id primary key,
    name varchar2(20) not null,
    jumin char(6) not null constraint uk_temp10_jumin unique,
    addr varchar2(30),
    age number constraint ck_temp10_age check(age >= 19)
);

--check 제약조건에는 java에서 이미 검증되어 넘어온 값을 가지고 사용하기 때문에 
-- 무의미한것처럼 보이지만 해킹을 막기 위해서 바로 DB를 통해서 접근하려할수 있기 때문에사용

 

 

--강제 FK
--c_dept (deptno 컬럼이 신용확보 : 제약 pk)

alter table c_dept
add constraint pk_c_dept_deptno primary key(deptno);

--fk로 설정하려면 적어도 primary key나 unique여야한다. 걍 해버리면 오류남
--ORA-02270: no matching unique or primary key for this column-list

alter table c_emp
add constraint fk_c_emp_empno foreign key(deptno) references c_dept(deptno);

VIEW생성하는 권한주기

 

 

 

 

 

 

--제약조건은 언제 만들어야하는가
--1. create table 생성시 제약생성
--2. create table 생성 후에 필요에 따라 추가(alter table emp add constraint..)

--제약 확인
select * from user_constraints where table_name='EMP';
create table temp7(
--    id number primary key,--권장하지 않는 약식표현(제약이름을 자동설정하기 때문)
    id number constraint pk_temp7_id primary key, --정식표현법--약자_테이블명_컬럼명 
    name varchar2(20) not null,
    addr varchar2(50)
);
desc temp7;

select * from user_constraints where table_name='TEMP7';

insert into temp7(name,addr) values('홍길동','서울시 강남구');
--ORA-01400: cannot insert NULL into ("KOSA"."TEMP7"."ID")

insert into temp7(id,name,addr) values(10,'홍길동','서울시 강남구');
select * from temp7;
commit;
--제약조건 위배되어 실행안됨
insert into temp7(id,name,addr) values(10,'아무개','서울시 성북구');
--ORA-00001: unique constraint (KOSA.PK_TEMP7_ID) violated

--UNIQUE key(UK)  :  테이블의 모든 행을 유일하게 하는 값을 가진 열(NULL 을 허용)
--중복값 허용하지 않아요
--테이블에 컬럼수만큼 UNIQUE key를 달수 있다.
--(NULL 을 허용)
--null도 허용하고 싶지 않으면 (제약을 unique걸고 not null도 걸면 된다.)
create table temp8(
    id number constraint pk_temp8_id primary key,
    name varchar2(20) not null,
    jumin nvarchar2(6) constraint uk_temp8_jumin unique,
    addr varchar2(50)
);

select * from user_constraints where table_name='TEMP8';

insert into temp8(id,name,jumin,addr) values(10,'홍길동','123456','경기도');
insert into temp8(id,name,jumin,addr) values(20,'아무개','123456','서울');
--ORA-00001: unique constraint (KOSA.UK_TEMP8_JUMIN) violated
--unique제약조건을 걸면 중복값은 들어갈수 없다. 근데 null값은 들어갈 수있다. null은 중복 가능!!!!주의하자
insert into temp8(id,name,addr) values(20,'아무개','서울');
insert into temp8(id,name,addr) values(30,'김유신','서울');
--unique : null을 중복체크하지 않는다.
select * from temp8;

--null을 허용하고 싶지 않아요 (not null)을 써주면 된다.
--jumin nvarchar2(6) not null constraint uk_temp8_jumin unique,
--unique는 테이블의 모든 컬럼에 걸수 있다는게 장점!!!
------------------------------------------------------------------
--테이블 생성 후에 제약 걸기 (추천)
create table temp9(id number);
--작업하다보니  제약이 필요해짐
alter table temp9
add constraint pk_temp9_id primary key(id);
create table temp91(num number, empno number , ename varchar2(20));
--복합키설정
alter table temp91
add constraint pk_temp91_num_empno primary key(num,empno);

select * from user_constraints where table_name='TEMP91';
--복합키로 설정하면 검색할때 where절에서 유일값을 고르려면 두가지를 모두 검색해야한다.
--where num=1 and empno=7788;--유일한 한개의 row 반환시.. pk 컬럼 모두명시

alter table temp9
add ename varchar2(50);

--주의 not null 제약이지만 추가 할때는 다른 문법이다.
alter table temp9 
modify(ename not null);
--modify는 타입수정할때 쓰는거였는데 
select * from user_constraints where table_name='TEMP9';

----------------------------------------------------------------------
--check 제약(업무 규칙 : where 조건을 정의)
--where gender in('남','여')
--제약조건은 해킹을 막기 위함도 있음
create table temp10(
    id number constraint pk_temp10_id primary key,
    name varchar2(20) not null,
    jumin char(6) not null constraint uk_temp10_jumin unique,
    addr varchar2(30),
    age number constraint ck_temp10_age check(age >= 19)
);
--check 제약조건에는 java에서 이미 검증되어 넘어온 값을 가지고 사용하기 때문에 
-- 무의미한것처럼 보이지만 해킹을 막기 위해서 바로 DB를 통해서 접근하려할수 있기 때문에사용
select * from user_constraints where table_name='TEMP10';

insert into temp10(id,name,jumin,addr,age)
values(100,'홍길동','123456','서울시 강남구',20);

select * from temp10;
--check제약조건에 걸리는 insert
insert into temp10(id,name,jumin,addr,age)
values(200,'길동','234567','서울시 강남구',18);
--ORA-02290: check constraint (KOSA.CK_TEMP10_AGE) violated

commit;

--FOREIGN KEY(FK) :  열과 참조된 열 사이의 외래키 관계를 적용하고 설정합니다.
--참조제약(테이블과 테이블간의 제약)
create table c_emp
as
    select empno, ename, deptno from emp where 1=2;
--틀만 복사

select * from c_emp;

create table c_dept
as 
    select deptno, dname from dept where 1=2;

select * from c_dept;

--강제 FK
--c_dept (deptno 컬럼이 신용확보 : 제약 pk)
alter table c_dept
add constraint pk_c_dept_deptno primary key(deptno);


alter table c_emp
add constraint fk_c_emp_empno foreign key(deptno) references c_dept(deptno);
--fk로 설정하려면 적어도 primary key나 unique여야한다. 걍 해버리면 오류남
--ORA-02270: no matching unique or primary key for this column-list

select * from user_constraints where table_name='C_EMP';
select * from user_constraints where table_name='C_DEPT';

--부서(코드성 데이터)
insert into c_dept(deptno,dname) values(100,'인사팀');
insert into c_dept(deptno,dname) values(200,'관리팀');
insert into c_dept(deptno,dname) values(300,'회계팀');
commit;

--사원입사
insert into c_emp(empno,ename,deptno) values(1,'코린이',400);
--ORA-02291: integrity constraint (KOSA.FK_C_EMP_EMPNO) violated - parent key not found
--부모에 존재하지 않음이 나옴
insert into c_emp(empno,ename,deptno) values(1,'코린이',100);
select *from c_emp;
---------------------------------제약 END------------------------------
--카페 문제
drop table student;
create table student(
    student_id number,
    student_name varchar2(20),
    score_kor number,
    score_eng number,
    score_math number,
    score_total number GENERATED ALWAYS as (score_kor+ score_eng + score_math) VIRTUAL,
    score_avg number GENERATED ALWAYS as ((score_kor+ score_eng + score_math)/3) VIRTUAL,
    dept_code number 
);
--여기서 score_avg에서 score_total을 사용하여 평균을 구하지 못하는 이유는 insert, update할때만
--실행되기 때문이다

alter table student add constraint pk_stu_id primary key(student_id);
alter table student modify (student_name not null);
alter table student modify (score_kor default 0);
alter table student modify (score_eng default 0);
alter table student modify (score_math default 0);
alter table student add constraint fk_stu_dcode foreign key(dept_code) references department(dept_code);

create table department(
    dept_code number,
    dept_name varchar2(50)
);
alter table department add constraint pk_dept_code primary key(dept_code);
alter table department modify(dept_name not null);
alter table department modify(dept_name varchar2(50));
select * from user_constraints where table_name='STUDENT';

insert into department(dept_code,dept_name) values(1,'컴퓨터공학');
insert into department(dept_code,dept_name) values(2,'경영학');
insert into department(dept_code,dept_name) values(3,'회계학');

select * from department;

insert into student(student_id,student_name,
                    score_kor,score_eng,score_math,
                    dept_code) values(230001,'이연수',45,50,60,1);
insert into student(student_id,student_name,
                    score_math,
                    dept_code) values(230002,'이수연',80,2);
insert into student(student_id,student_name,
                    score_kor,score_eng,
                    dept_code) values(230003,'이나연',88,77,3);
insert into student(student_id,student_name,
                    score_eng,score_math,
                    dept_code) values(230004,'이희연',77,66,1);
commit;

select student_id 학번,student_name 이름,
       score_total 총점,trunc(score_avg,1) 평균,
       dept_code 학과코드,dept_name 학과명
from student s join department d using(dept_code);
--마무리 해보자!!








--|:or
--[]:생략가능

--참조하고 있는 테이블에서 설정해줘야하는  on delete cascade -- oracle은 이것만
--on update cascade -- ms sql에만 있음

--------------------------------------------
--cascade
--개발자 관점에서 FK 살펴보기--
--master - detail 관계
--부모 - 자식 관계

--c_emp 과  c_dept  (관계 FK) >> c_emp(deptno) 컬럼이 c_dept(deptno) 컬럼을 참조
--FK 관계 :  master(c_dept) - detail(c_emp) >> 화면 (부서 출력) >> 부서번호 클릭 >> 사원정보 출력
--deptno 참조 관계 부모(c_dept) - 자식(c_emp)

--관계 PK가지고 있는 쪽 (master)   , FK ( detail)

select * from c_dept;
select * from c_emp;

--1. 위 상황에서 c_emp 테이블에 있는 신입이 삭제할 수 있을 까요?
delete from c_dept where deptno=100; 
--ORA-02292: integrity constraint (KOSA.FK_C_EMP_DEPTNO) violated - child record found
delete from c_dept where deptno=200; -- 삭제가능 (c_emp 가 빌려쓰거 있지 않아요)


delete from c_dept where deptno=100; 
delete from c_emp where empno=1; --참조하지 않게 .... 

--자식 삭제
--부모 삭제 하시면 됩니다
commit;

/*
column datatype [CONSTRAINT constraint_name]
 REFERENCES table_ name (column1[,column2,..] [ON DELETE CASCADE])
column datatype,
. . . . . . . ,
[CONSTRAINT constraint_name] FOREIGN KEY (column1[,column2,..])
 REFERENCES table_name (column1[,column2,..] [ON DELETE CASCADE]


ON DELETE CASCADE 부모 테이블과 생명을 같이 하겠다

alter table c_emp
add constraint fk_c_emp_deptno foreign key(deptno)
references c_dept(deptno) ON DELETE CASCADE; 

delete from c_emp where empno=1  >> deptno >> 100번

delete from c_dept where deptno=100; 삭제 안되요 (참조 하고 있으니까)
ON DELETE CASCADE 걸면 삭제되요 

부모삭제 >> 참조하고 있는 자식도 삭제


MS-SQL
ON DELETE CASCADE
ON UPDATE CASCADE
*/
/* 사원 */
CREATE TABLE EMP (
	empno NUMBER NOT NULL, /* 사번 */
	ename VARCHAR2(20), /* 이름 */
	sal NUMBER, /* 급여 */
	deptno NUMBER /* 부서번호 */
);

/* 부서 */
CREATE TABLE DEPT (
	deptno NUMBER, /* 부서번호 */
	dname VARCHAR2(20) /* 부서명 */
);

ALTER TABLE EMP
ADD CONSTRAINT PK_EMP_EMPNO	PRIMARY KEY (empno);

ALTER TABLE DEPT
ADD CONSTRAINT PK_DEPT_DEPTNO 	PRIMARY KEY (deptno);

ALTER TABLE EMP
ADD CONSTRAINT FK_DEPT_TO_EMP 	FOREIGN KEY (deptno)	REFERENCES DEPT (deptno);
--------------------------------------------------------------------------------

--view
/*
가상테이블 (subquery>>in line view >> 메모리 >> 가상테이블 조인
필요한 가상테이블을 객체형태로 만들어 줄께 (영속적으로)
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name [(alias[,alias,...])]
AS Subquery 
[WITH CHECK OPTION [CONSTRAINT constraint ]]
[WITH READ ONLY]

OR REPLACE      이미 존재한다면 다시 생성한다.
FORCE           Base Table 유무에 관계없이 VIEW 을 만든다.
NOFORCE         기본 테이블이 존재할 경우에만 VIEW 를 생성한다.
view_name       VIEW 의 이름
Alias           Subquery 를 통해 선택된 값에 대한 Column 명이 된다.
Subquery        SELECT 문장을 기술한다.
WITH CHECK OPTION VIEW 에 의해 액세스 될 수 있는 행만이 입력,갱신될 수 있다. 
Constraint CHECK OPTON 제약 조건에 대해 지정된 이름이다.
WITH READ ONLY 이 VIEW 에서 DML 이 수행될 수 없게 한다.

*/
--에러뜬다. 뷰를 만들수 있는 권한 은 없기 때문에
---- SYSTEM PRIVILEGES
--GRANT CREATE ANY VIEW TO "KOSA" WITH ADMIN OPTION;
create view view001
as 
    select * from emp;

--view001 객체가 생성되었어요
--가상테이블 ( 사용방법이 테이블과 동일)
select * from view001; -- 실행될 수 있는 쿼리문을 갖고 있는 객체 , 실제는 안에 쿼리가 실행된다.
--view 쿼리 문장을 가지고 있는 객체이다.
--사용법은 일반 테이블과 동일하다
select * from view001 where deptno=10;

create view v_emp
as
    select deptno, avg(sal) as avgsal from emp group by deptno;
    
--view는 쿼리문을 등록해 놓는다.
--테이블을 만드는 건 1회성! view를 사용하는 이유가 이거
---------------------------------------------------------------------
/*
view 가상테이블(쿼리문장을 가지고 있는 객체) : 사용법은 테이블과 동일
view를 통해서 ... 데이터 처리(select, insert, update, delete)
단 view를 통해서 볼 수 있는 데이터에 한해서만

view 목적
1. 개발자의 편리성 : join, subquery 복잡한 쿼리 또는 join되는 데이터를 view 미리생성
2. 쿼리의 단순화 : view join
3. 보안 : 원본 테이블을 노출하지 않고 뷰를 만들어서 특정 컬럼만 볼수 있게 제공한다.


*/
--create or replace
--없으면 만들고 있으면 덮어써라
create or replace view v_001
as
    select empno, ename from emp;

select * from v_001;    

create or replace view v_emp
as
select empno, ename, job, hiredate from emp;

select * from v_emp;

select * from v_emp where job='CLERK';

--편리성
--같은 쿼리를 view하나 생성을 통해서 view 명을 통해서 관리할 수 있다.
create or replace view v_002
as 
    select e.empno, e.ename, d.deptno, d.dname
    from emp e join dept d
    on e.deptno = d.deptno;
    
select * from v_002;


--직종별 평균 급여를 볼수 있는 view작성(객체)
--v_003
create or replace view v_003
as
select job, trunc(avg(sal),0) as avgsal from emp group by job;
--view 만들때 집계함수 쓸라면 별칭 지정해줘야한다. 안그럼 에러얘도 나름 테이블이라고 주장하는것
select * from v_003;

----------------------------------------------------------------------------
--view 가상테이블
--view를 통해서 볼수 있는 데이터 DML이 가능하다.
--하지만 view는 select로만 쓰기로하자

create or replace view v_emp
as
    select empno, ename, job, hiredate from emp;

select * from v_emp;
 update v_emp
 set sal=0;
 --view 가 볼 수 없는 데이터는 update 불가능하다.
 update v_emp
 set job='AAA';
 --업데이트 된다.
 
 select *from emp;
 rollback;
 
-- 30번 부서 사원들의 직종, 이름, 월급을 담는데 view 를 만드는데,
-- 각각의 컬럼명을 직종, 사원이름, 월급으로 Alias를 주고 월급이
-- 300보다 많은 사원들만 추출하도록하라. view101
 
 create or replace view view101
 as
 select job 직종,ename 사원이름,sal 월급
    from emp
    where deptno=30 and sal>300;
 
 
 
--부서별 평균월급을 담는view를 만들되, 평균월급이 2000이상인
--부서만 출력하도록하라 view102
create or replace view view102
as
select deptno, avg(sal) as avgsal
from emp
group by deptno
having avg(sal) >=2000;