오라클 데이터베이스
sql 5일차(0714)
피아마수
2023. 7. 14. 13:52
eXERD
eXERD 3.X eXERD v2.X 평가판 다운로드 이클립스 플러그인으로 설치 eXERD 3.X eXERD SAM Edition 평가판/기업용 평가판으로 설치 평가판안내 평가판은 30일간 eXERD의 모든 기능을 사용해 볼 수 있으나, 인
www.exerd.com
평가판 다운로드
이메일 다운로드
현업에서 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;