2022.04.22. 금요일 이공계 전문기술 연수사업 20일차

오늘 배운 것

ex) 주문하지 않은 고객의 이름 출력(서브쿼리 이용)
select name from customer 
minus select name from customer 
where customer.custid in (select orders.custid from orders);

ex)주문 금액의 총액과 주문의 평균 금액
select sum(saleprice), avg(saleprice) from orders;

ex)고객의 이름과 고객별 총구매액
select name, sum(saleprice) 
from orders o, customer c 
where o.custid=c.custid group by name;

ex)고객의 이름과 고객이 주문한 도서목록 출력
select name, bookname 
from customer c, book b, orders o 
where c.custid=o.custid and b.bookid=o.bookid;

ex)도서의 가격과 판매가격의 차이가 가장 많은 주문 출력
select * 
from orders o, book b 
where o.bookid=b.bookid and price-saleprice =
(select max(distinct price-saleprice) 
from book b, orders o 
where b.bookid=o.bookid);

ex)도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름 출력
select name 
from customer c, orders o 
where c.custid=o.custid 
having avg(saleprice) > (select avg(saleprice) from orders) 
group by name;

ex)관리자보다 입사일이 더 빠른 직원들의
이름,관리자이름,입사일,관리자의입사일,부서번호,부서명 출력

select e.ename 이름 ,m.ename 관리자이름 , e.hiredate 입사일, m.hiredate 관리자이름, e.eno 사원번호, dname 부서명  
from emp e, emp m, dept 
where e.mgr=m.eno and e.dno=dept.dno and m.hiredate>e.hiredate;

q)어떤 것을 self조인 해야하고
어떤 것을 조인해야하고 어떤 것을 서브쿼리를 해야하는지 모르겠음.

self조인: 테이블이 하나고 자기 자신을 참조해야할 때
반드시 서브쿼리를 써야하는 경우?
문제에따라서 서브쿼리로도 해결할 수 있고 조인으로 해결할 수도 있음
데이터 건수가 많을 때는 조인보단 서브쿼리가 더 성능이 좋음!

-------------------------------------------------------------------------------------------------
데이터베이스 명령어의 종류
ㄴDDL 정의 DATA DEFINITION LANGUAGE 데이터 정의어
ㄴDML 조작 DATA MANUFULATION LANGUAGE 데이터 조작어
ㄴDCL 권한 DATA CONTROL LANGUAGE 데이터 권한어

-------------------------------------------------------------------------------
DDL-create
ㄴ테이블을 구성하고 속성과 속성에 관한 제약을 정의하며
기본키 및 외래키를 정의하는 명령어

기본키 : primary key : 다른 레코드와 구별하기 위하여 생략할 수 없고 중복할 수 없음
외래키 : foreign key (참조키라고도 함) : 이미 있는 다른 테이블의 값을 반드시 참조해야 함

create table 테이블이름 ({속성이름 데이터타입, 속성이름 데이터타입, ...
[not null(생략 불가능) | unique(중복 불가능) | default(기본값 설정) | check(조건을 만족하는 값을 설정)]
[primary key 속성이름(들)]
[foreign key 속성이름(들) references 테이블이름]
//ㄴ제약설정
});

unique
ㄴ그 컬럼의 값이 유일해야할 때 설정하는 제약, null 허용함!

create table newbook(
bookid number unique,
bookname varchar2(20) not null,
publisher varchar(20),
price number
);

primary key
ㄴnot null + unique
null을 허용하지 않고 중복을 허용하지 않음
다른 레코드와의 구별을 위하여 식별자로 사용할 때 설정(주식별자)
관계형 데이터베이스에서 모든 테이블에는 반드시 주식별자를 설정해야 함
때로는 두 개 이상의 컬럼이 합쳐져서 주식별자가 되기도 함

create table newbook(
bookid number primary key,
bookname varchar2(20),
publisher varchar(20),
price number
);//컬럼레벨에서 설정

create table newbook(
bookid number,
bookname varchar2(20),
publisher varchar2(20).
price number,
primary key bookid
);//테이블레벨에서 설정

default
ㄴ값을 생략할 경우 기본값이 추가됨

create table newbook(
bookid number primary key,
bookname varchar2(20) not null,
publisher varchar2(20) default '쌍용미디어',
price number
);

check 제약
ㄴ컬럼의 값이 특정 조건을 만족해야하는 경우에 설정

create table newbook(
bookid number primary key,
bookname varchar2(20) not null,
publisher varchar2(20) default '쌍용미디어',
price number default 10000 check(price>=1000)
);
//기본 price는 만원이고 반드시 천원 이상이어야 함

ex)다음의 제약을 만족하는 member테이블을 생성
고객번호는 주식별자
고객이름은 생략불가능
주소의 기본값은 서울 
전화번호는 생략가능,유일해야함
나이는 20살이상이어야함

create table member(
memberid number primary key,
name varchar2(20) not null,
address varchar2(20) default '서울',
phone varchar2(20) unique,
age number check(age>=20)
);

참조키(foreign key) 설정
ㄴ어떤 컬럼의 값이 반드시 이미 있는 다른 컬럼의 값을 참조해야하는 경우에 참조키를 설정함
예를 들어, 사원 테이블의 부서번호는 반드시 부서테이블에 있는 부서번호여야 함
또, 주문테이블의 고객번호는 반드시 고객테이블에 있는 고객번호여야 함
주문테이블의 도서번호는 반드시 도서테이블에 있는 도서번호여야 함

이와 같이 어떤 컬럼의 값이 이미 있는 다른 테이블의 값을 참조해야 하는 경우에 사용하는 것을 '참조키'라고 함
이 때 참조되는 이미 있는 테이블을 부모테이블이라 하고 참조하는 테이블을 자식테이블이라고 함
부모테이블과 자식테이블은 참조키로 서로 관계에 있다라고 함

그래서 우리가 학습하고 있는 오라클을 관계형 데이터베이스라고 함
Relational DateBase RDBS
참조키를 설정할 때는 반드시 부모테이블의 pk로 식별된 주식별자만이 참조키로 설정할 수 있음!!

create table member(
id varchar2(20) primary key,
pwd varchar2(20),
name varchar2(20)
);

create table board(
no number primary key,
title varchar2(50),
id varchar2(20) references member(id),
content varchar2(3000)
);

4행에 오류:
ORA-02270: 이 열목록에 대해 일치하는 고유 또는 기본 키가 없습니다.
=>참조 키로 설정하려는 컬럼은 반드시 부모테이블의 pk로 설정된 주식별자여야 함!!!

위와 같이 테이블을 생성하게 되면
member테이블과 board테이블은 주종관계(부모자식관계)가 성립됨
즉, member테이블이 부모, board테이블이 자식이 됨

관계형 데이터베이스에서 테이블을 Entity(개체)라고도 표현함
부모테이블의 레코드가 반드시 먼저 insert되어야 함

insert into board values(3,'점심','rabbit','밥보다 잠이 더 좋아')
*
1행에 오류:
ORA-02291: 무결성 제약조건(C##SIST.SYS_C008345)이 위배되었습니다- 부모 키가
없습니다
=>추가하려는 레코드의 'kim'이라는 아이디가 부모테이블인 member에 존재하지 않는 아이디라서 오류 발생

---------------------------------------------------------------------------
개체무결성과 참조무결성

개체무결성
ㄴ모든 레코드(튜플)은 pk로 설정된 주식별자에 의해서 구별이 가능해야 함
pk는 null이 될 수 없고 중복을 허용하지 않음
이것을 만족하지 않는 경우를 개체무결성에 위배된다고 말함

참조무결성
참조키로 설정된 컬럼의 값은 반드시 부모테이블에 나타나는 값이어야 함
그렇지 않은 경우를 참조무결성에 위배된다고 말함

관계에 있는 테이블을 삭제할 때는 참조되고 있는 자식테이블을 먼저 삭제함!!

-----------------------------------------------------------------------------
pk와 fk의 설정
ㄴ컬럼레벨에서도, 테이블레벨에서도 가능

create table member(
id varchar2(20) primary key,
pwd varchar2(20),
name varchar2(20)
);//컬럼레벨

create table board(
no number primary key,
title varchar2(50),
id varchar2(20) references member(id),
content varchar2(20)
);//컬럼레벨

create table member(
id varchar2(20),
pwd varchar2(20),
name varchar2(20),
primary key(id)
);//테이블레벨

create table board(
no number,
title varchar2(50),
id varchar2(20),
content varchar2(20),
primary key(no),
foreign key(id) references member (id)
);//테이블레벨

ex)몇 개의 레코드를 추가하여 개체무결성, 참조무결성에 대한 실험

관계에 있는 부모테이블의 레코드를 삭제할 때 연쇄하여 자식의 레코드를 삭제하도록
테이블 생성 시에 옵션을 설정할 수 있음 => on delete cscade 사용!

on delete cascade
ㄴ참조 키를 설정할 때 부모의 참조하고 있는 레코드가 삭제될 때
연쇄하여 자식의 레코드도 삭제하기 위한 옵션

create table member(
id varchar2(20),
pwd varchar2(20),
name varchar2(20),
primary key(id)
);

create table board(
no number,
title varchar2(50),
id varchar2(20),
content varchar2(3000),
foreign key(id) references member(id) on delete cascade
);

member테이블과 board테이블에 몇 개의 레코드를 추가한 다음
member테이블의 레코드를 삭제할 때 참조되고 있는 board테이블에 레코드도 연쇄하여 삭제되는지 실험

-------------------------------------------------------------------------------
DDL-alter
ㄴ테이블 구조를 변경하는 명령어
이미 있는 테이블에 새로운 컬럼을 추가하거나 삭제하거나
제약을 추가하거나 삭제하거나 자료형을 변경할 때 씀

새로운 칼럼 추가하기
alter table 테이블이름 add 컬럼이름 자료형;
=>이미 레코드가 있는 테이블에 새로운 컬럼을 추가하게 되면 그 컬럼의 값은 모두 null이 됨

필요없는 컬럼 삭제하기
alter table 테이블이름 drop column 컬럼이름;

컬럼 자료형 변경
alter table 테이블이름 modify 컬럼이름 새로운자료형;

create table member(
id varchar2(20),
pwd varchar2(20),
name varchar2(20)
);

이미 생성한 테이블에 pk설정하기
alter table 테이블이름 add primary key(컬럼이름);

데이터가 있는 상태에서 이미 생성한 테이블에 pk설정하기
=>pk 설정하려는 컬럼의 레코드에 중복과 null이 있으면 안됨!
null이 있는 레코드를 삭제하던지 null이 있는 레코드의 값을 다른 값으로 수정한 후에 pk설정 가능

이미 있는 테이블에 foreign키 설정
alter table 테이블이름 add foreign key(컬럼이름) references 부모테이블(컬럼이름);

create table member(
id varchar2(20) primary key,
pwd varchar2(20),
name varchar2(20)
);

create table board(
no number primary key,
title varchar2(50),
id varchar2(20),
content varchar2(3000)
);

alter table board add foreign key(id) references member(id);

create table member(
id varchar2(20),
pwd varchar2(20),
name varchar2(20)
);

create table board(
no number primary key,
title varchar2(50),
id varchar2(20),
content varchar2(3000)
);

SQL> exit;
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0에서 분리되었습니다.

C:\Users\feelj>sys
'sys'은(는) 내부 또는 외부 명령, 실행할 수 있는 프로그램, 또는
배치 파일이 아닙니다.

C:\Users\feelj>sqlplus

SQL*Plus: Release 21.0.0.0.0 - Production on 금 4월 22 15:40:48 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

사용자명 입력: sys
비밀번호 입력:
ERROR:
ORA-28009: SYS(SYSDBA 또는 SYSOPER)로 접속해야 합니다.


사용자명 입력: system
비밀번호 입력:
마지막 성공한 로그인 시간: 금 4월  15 2022 14:12:41 +09:00

다음에 접속됨:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> create user c##madang identified by madang;

사용자가 생성되었습니다.

SQL> grant connect, resource, dba to c##madang;

SQL> exit
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0에서 분리되었습니다.

create table dept(
dno number,
dname varchar2(20),
dloc varchar2(20)
);

create table emp(
eno varchar2(20),
ename varchar2(20),
dno number
);

ex)eno의 자료형을 number로 수정
emp에 salary 컬럼을 추가
eno에 pk를 설정
emp의 dno에 fk를 설정
dept에 다음의 레코드를 추가
부서번호 10
부서명 '개발1팀'
부서위치 '서울시 마포구 서교동 풍성빌딩'
만약 레코드가 추가되지 않으면 추가되도록 테이블의 구조를 변경

SQL> alter table emp modify eno number;

테이블이 변경되었습니다.

SQL> alter table emp add salary number;

테이블이 변경되었습니다.

SQL> alter table emp add primary key(eno);

테이블이 변경되었습니다.

SQL> alter table dept add primary key(dno);

테이블이 변경되었습니다.

SQL> alter table emp add foreign key(dno) references dept(dno);

테이블이 변경되었습니다.

SQL> alter table dept modify dloc varchar2(50);

테이블이 변경되었습니다.

SQL> insert into dept values(10,'개발1팀','서울시 마포구 서교동 풍성빌딩');

1 개의 행이 만들어졌습니다.
-----------------------------------------------------------------------
두 개 이상의 컬럼이 합쳐져서 pk 설정하기
ㄴ테이블레벨에서 설정하거나 테이블 구조의 변경으로 설정해야 함

create table orderDetail(
orderno number,
bookid number,
qty number,
primary key (orderno,bookid)
);

insert into orderDetail values(1,1,10);
insert into orderDetail values(1,2,5);
insert into orderDetail values(2,1,12);
insert into orderDetail values(1,1,12); ->오류! 개체무결성에 위배됨


create table theater(
tnum number primary key,
tname varchar2(20),
tloc varchar2(20)
);

create table hall(
tnum number references theater(tnum),
hnum number check (hnum>=1 and hnum<=9),
title varchar2(20),
price number check (price<20000),
chair number,
primary key(tnum,hnum)
);

create table reservation(
tnum number references theater(tnum),
hnum number references hall(hnum),
cnum number references customer(cnum),
chairnum number,
re_date date,
primary key(tnum,hnum,cnum),
foreign key(t
);

create table customer(
cnum number primary key,
name varchar2(20),
addr varchar2(20)
);
//나의 생성

create table customer(
cno number primary key,
cname varchar2(20),
caddr varchar2(20)
);

create table Theater(
tno number primary key,
tname varchar2(50),
taddr varchar2(20)
);

create table play(
tno number references theater(tno),
pno number check(pno>=1 and pno <=10),
title varchar2(100),
price number check(price < 20000),
seats number,
primary key(tno,pno)
);


create table reservation(
tno number,
pno number,
cno number,
seat number,
rdate date,
primary key(tno,pno,cno),
foreign key(pno,tno) references play(pno,tno),
foreign key(cno) references customer(cno)
);

//강사님 생성

 

오늘 헷갈린 것

•  a pk,b pk랑 pk(a,b)는 다르다는 것

오늘 한 생각

블로그 포스팅 미루면 안돼!!!!!!!!!!!!!!!!!!!