🌞자료 수정
update 테이블명 set 컬럼명 = 바꿀값 where 컬럼명 = 바꿀값;🌞self join
예시) 관리자보다 입사일이 더 빠른 직원들의 이름, 관리자이름, 입사일, 관리자의 입사일, 부서번호, 부서명을 출력
select e.ename, m.ename, e.hiredate, m.hiredate, e.dno, dname
from emp e, emp m, dept d
where e.mgr = m.eno and e.dno = d.dno and
e.hiredate < m.hiredate;-> 동일한 테이블에서 참조되는 경우에는 self 조인을 해야하고, 어떠한 경우에는 반드시 서브쿼리를 써야만 문제 해결을 할 수 있고, 어떤 경우에는 서브쿼리를 써도되고, 조인을 해도 되는 경우가 있다. 쿼리에는 반드시 어떤 것을 써라. 라는 것은 없고 선택사항이다. 다만, 데이터 양이 많을 때에는 검색 속도 측면에서 서브쿼리가 조인보다 성능이 더 좋다.
🌞데이터베이스 명령어의 종류
🌈DDL(Data Definition Language) 데이터 정의어
🍁create문
-
테이블을 구성하고, 속성과 속성에 관한 제약을 정의하며, 기본키 및 외래키를 정의하는 명령(테이블을 생성하는 명령어)
-
형식
create table 테이블이름 (
컬럼이름 자료형 [제약],
...
)🍁alter문
-
테이블의 구조를 변경
-
이미 있는 테이블에 새로운 컬럼을 추가하거나 삭제, 제약을 추가하거나 삭제, 자료형을 변경
-
형식
alter table 테이블명 add 컬럼명 자료형;예시) member 테이블의 현재 구조
SQL> desc member;
이름 널? 유형
-------------------------- -------- ----------------------------
ID NOT NULL VARCHAR2(20)
PWD VARCHAR2(20)
NAME VARCHAR2(20)===> member 테이블에 주소를 위한 컬럼을 추가
alter table member add address varchar2(50);===> 이미 레코드가 있는 테이블에 새로운 컬럼을 추가하게 되면 그 컬럼의 값은 모두 null
<결과>
SQL> desc member;
이름 널? 유형
-------------------------- -------- ----------------------------
ID NOT NULL VARCHAR2(20)
PWD VARCHAR2(20)
NAME VARCHAR2(20)
ADDRESS VARCHAR2(50)===> 주소 컬럼이 추가된 것을 볼 수 있다.
🎄컬럼 추가
alter table 테이블명 add 컬럼명 자료형;예시) 이미 있는 테이블에 pk 설정
alter table 테이블명 add primary key (컬럼명);
alter table member add primary key(id);예시) 이미 있는 테이블에 fk 설정
alter table 테이블명 add foreign key (컬럼명) references 부모테이블(부모테이블컬럼이름);
alter table board add foreign key(id) references member(id);🎄컬럼 삭제
alter table 테이블명 drop column 컬럼명;🎄컬럼 자료형 변경
alter table 테이블명 modify 컬럼명 새로운자료형; 예시) 설정했던 컬럼의 자료형 중에 지정해준 컬럼 값이 너무 작을 때
insert into member values('lee', '1234', '이순신', '서울시 마포구 서교동 풍성빌딩');
*
1행에 오류:
ORA-12899: "C##SIST"."MEMBER"."ADDR" 열에 대한 값이 너무 큼(실제: 42, 최대값:20)
===> 테이블의 구조를 변경해야함. 주소는 최대 20인데 입력한 값은 42이기 때문에 오류🍁drop문
-
테이블을 삭제하는 명령어
-
형식
drop table 테이블명;🌞제약의 종류
🌈not null
- 생략할 수 없다.
- 중복을 허용하고, not null인지만 판별한다.
예시) newbook 테이블 생성
create table newbook (
bookid number not null,
bookname varchar2(20) not null,
publisher varchar2(20) null,
price number null
);insert into newbook (4, null, null, null);===> 오류가 발생, bookname에 not null 제약을 설정하였는데 null값을 주었기 때문에 오류가 발생한다.
🌈unique
- 중복할 수 없다.
- 그 컬럼의 값이 유일해야 할 때 설정하는 제약
- null을 허용
예시) newbook 테이블 생성
create table newbook (
bookid number unique,
bookname varchar2(20) not null,
publisher varchar2(20),
price number
);insert into newbook values(1, '재미있는 자바', null, null);
insert into newbook values(1, '즐거운오라클', null, null);
-> 1행에 오류:
ORA-00001: 무결성 제약 조건(C##SIST.SYS_C008327)에 위배됩니다
insert into newbook values(null, '재미있는자바', null, null); ===> bookid는 unique로 설정되었는데 중복된 값을 넣어서 오류가 발생.
===> unique는 유일한지만 판별, null값을 허용한다.
🌈primary key
- 기본키를 설정
- not null + unique
- 다른 레코드와의 구별을 위하여 식별자로 사용할 때에 설정
- 관계형 데이터베이스에서 모든 테이블에는 반드시 주식별자를 설정해야 한다.
- 때로는 두 개 이상의 컬럼이 합쳐져서 주식별자가 되기도 한다.
<컬럼레벨의 pk설정>
예시) newbook 테이블 생성
create table newbook (
bookid number primary key,
bookname varchar2(20),
publisher varchar2(20),
price number
);insert into newbook values(1, '즐거운자바', null, null);
insert into newbook values(1, '즐거운오라클', null, null);
1행에 오류:
ORA-00001: 무결성 제약 조건(C##SIST.SYS_C008330)에 위배됩니다
===> pk로 설정된 bookid에 이미 1번도서가 있는데 중복되었기 때문에 오류가 발생
insert into newbook values(null, '즐거운오라클', null, null);
1행에 오류:
ORA-01400: NULL을 ("C##SIST"."NEWBOOK"."BOOKID") 안에 삽입할 수 없습니다
==> pk로 설정된 bookid에 null을 추가할 수 없어 오류가 발생한다.<테이블 레벨의 pk설정>
create table newbook (
bookid number,
bookname varchar2(20),
publisher varchar2(20),
price number,
primary key(bookid)
);insert into newbook values(1, '즐거운자바', null, null);
insert into newbook values(1, '즐거운오라클', null, null);
1행에 오류:
ORA-00001: 무결성 제약 조건(C##SIST.SYS_C008331)에 위배됩니다===> 마찬가지로 중복이 허용되지 않는 bookid에 중복 값이 들어갔기 때문에 오류
🌈default 제약
- 기본 값을 설정
- 값을 생략할 경우 기본 값이 추가된다.
예시) newbook 테이블 생성, publisher에 '쌍용미디어'라는 기본 값을 제약
create table newbook (
bookid number primary key,
bookname varchar2(20) not null,
publisher varchar2(20) default '쌍용미디어',
price number
);insert into newbook values(1, '재미있는자바', default, null); //기본값을 설정
insert into newbook values(2, '즐거운오라클', '한빛미디어', null); //값을 설정해주면 기본값을 무시함
insert into newbook (bookid, bookname) values(3, '신나는자바'); //기본값으로 나옴<결과>
BOOKID BOOKNAME PUBLISHER PRICE
------ --------------- ------------------------ --------
1 재미있는자바 쌍용미디어
2 즐거운오라클 한빛미디어
3 신나는자바 쌍용미디어🌈check 제약
- 조건식을 만족하는 값을 설정
- 컬럼의 값이 특정 조건을 만족해야 하는 경우에 설정
예시) newbook 테이블 생성, price의 값이 1000원 이상을 만족해야 하는 제약
create table newbook (
bookid number primary key,
bookname varchar2(20) not null,
publisher varchar2(20) default '쌍용미디어',
price number default 10000 check(price >= 1000)
);===> price 값이 기본 값은 10000원이고, 값이 무조건 1000원 이상이여야 한다. 라는 제약!
insert into newbook values(3, '신나는자바', '한빛미디어', 500);
1행에 오류:
ORA-02290: 체크 제약조건(C##SIST.SYS_C008335)이 위배되었습니다
===> 1000원 이상이 아닌 500원이기 때문에 오류🌈foreign key(참조키 설정)
- 어떤 컬럼의 값이 반드시 이미있는 다른 컬럼의 값을 참조해야 하는 경우에 참조키를 설정
- 예를 들어, 사원테이블의 부서번호는 반드시 부서테이블에 있는 부서번호이어야 함.
- 이와 같이 어떤 컬럼의 값이 이미있는 다른 테이블의 값을 참조해야 하는 경우에 사용하는 것을 '참조키'라고 한다.
- 이 때, 참조되는 이미 있는 테이블을 '부모테이블'이라 하고, 참조하는 테이블을 '자식테이블' 이라고 한다.
- '부모테이블'과 '자식테이블'은 서로 참조키로, 관계에 있다 라고 한다.
==> 참조 키를 설정할 때에는 반드시 부모테이블의 pk로 설정된 '주식별자'만이 참조키로 설정할 수 있다 !!
예시) 기본키가 없고 부모테이블인 member 테이블과 그것을 참조해야 하는 board 테이블
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) references member(id),
content varchar2(3000)
);==> 위와 같은 방법으로 참조할 수 있다. 하지만 오류발생!!
4행에 오류:
ORA-02270: 이 열목록에 대해 일치하는 고유 또는 기본 키가 없습니다.==> 참조키로 설정하려는 컬럼은 반드시 부모테이블의 pk로 설정된 주식별자이어야 한다.
예시) 위와 같은 상황들을 제거하려면, 부모테이블인 member에 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)
);==> 위와 같이 테이블을 생성하게 되면 member 테이블과 board 테이블은 주종관계(부모, 자식관계)가 성립된다. member = 부모 , board = 자식
- 관계형 데이터베이스 테이블을 개체(Entity)라고도 표현한다.
- 이 때, 부모테이블에 레코드가 반드시 먼저 insert 되어야 한다.
<데이터추가>
insert into member values('tiger', '1234', '김범');
insert into member values('lion', '1234', '홍사자');
insert into board values(1, '안녕', 'tiger', '안녕');
insert into board values(2, '금요일', 'lion', '즐거운 금요일입니다.');
insert into board values(3, '점심', 'kim', '점심 뭐먹을까요?');
ORA-02291: 무결성 제약조건(C##SIST.SYS_C008343)이 위배되었습니다- 부모 키가 없습니다
==> 부모테이블에 'kim'이라는 데이터가 없기 때문에 오류가 난다.
==> 추가하려는 레코드에 'kim'이라는 아이디가 부모테이블인 member에 존재하지 않는 아이디라서 오류가 발생- 관계에 있는 테이블을 삭제할 때에는 자식테이블을 먼저 삭제해야 한다.
- 참조되고 있는 자식테이블이 있는 부모테이블을 먼저 삭제할 수는 없다.
🌈개체무결성과 참조무결성
🍁개체무결성
- 모든 레코드(튜플)는 pk로 설정된 주 식별자에의해서 구별이 가능해야 함을 말한다.
- pk는 null이 될 수 없고, 중복을 허용하지 않는다.
- 이것을 만족하지 않는 경우를 "개체무결성에 위배된다" 라고 말한다. (pk가 널이 되거나, 중복이 되었구나! 라는 말)
🍁참조무결성
- 참조키로 설정된 컬럼의 값은 반드시 부모테이블의 나타나는 값이어야 한다.
- 그렇지 않은 경우를 "참조무결성에 위배된다" 라고 말한다.
🌞기본키(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(3000)
);🌈테이블 레벨
- 컬럼을 다 설정해준 후에 아래에다가 키를 설정
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),
primary key(no),
foreign key(id) references member(id)
);🌞on delete cascade
- 참조키를 설정할 때에 부모의 참조하고 있는 레코드가 삭제될 때에 연쇄하여 자식의 레코드도 삭제하기 위한 옵션이다. ==> 자식에다가 적용
예시) 자식테이블에 참조하고 있는 레코드가 있어 삭제할 수 없는 경우
delete member where id = 'lion';
1행에 오류:
ORA-02292: 무결성 제약조건(C##SIST.SYS_C008346)이 위배되었습니다- 자식 레코드가 발견되었습니다
==> 자식 테이블인 board에 삭제하려고 하는 아이디 lion을 참조하고 있는 레코드가 있어서 삭제를 할 수 없다.
==> 자식레코드를 먼저 삭제한 후, 부모 레코드를 삭제할 수 있다.<해결>
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),
primary key(no),
foreign key(id) references member(id) on delete cascade
);==> 부모테이블에 레코드 삭제될 때에 참조되고 있는 자식레코드도 삭제.
🌞2개 이상의 컬럼이 합쳐져서 pk 설정
- 컬럼레벨은 사용할 수 없다.
- 테이블 레벨에서 설정하거나 테이블 구조의 변경으로 설정해야 한다.
//orderno와 bookid가 (pk)
create table orderDetail(
orderno number,
bookid number,
qty number,
primary key(orderno, bookid)
);
꾸준히 성장하는 개발자✨