카테고리 없음
오라클 - 테이블 생성 / 수정 / TRANSACTION/ SEQUENCE
장꾸꾸
2020. 10. 21. 11:00
테이블 만들기
--ex1) 테이블 : test
--id number(5) -> 아무것도 안쓰면 기본값 6임, name char(10), address varchar2(50);
create table test(
id number(5),
name char(10),
address varchar2(50)
);
describe test; --테이블의 구조 확인
select * from test;
describe test -> desc test
user1 테이블
--ex2) 테이블명 : user1
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')));
desc user1;
제약조건 확인
desc 로는 제약조건 확인이 불가 따라서 아래와 같이 확인
만들 때는 user1으로 했지만 확인할 때는 USER1 대문자로 검색하여 확인한다.
C : 체크제약조건/ P : primary key
-> 이 표시는 바꿀 수 있음
user2 테이블
--ex4) 테이블명 : user2
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 * from tab;
--ex5) 제약조건확인
select constraint_name, constraint_type
from user_constraints
where table_name='USER2';
이때 serach_condition을 추가하면 설명을 볼 수 있다.
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name='USER2';
추가하기
일대일 매칭이어야한다. 갯수가 맞지 않으면 혹은 조건에 맞지 않으면 추가되지 않는다.
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');
---> 무결성제약조건에 위배(이유: idx 1 중복)-> 명령의 224 행에서 시작하는 중 오류 발생 -
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')
오류 보고 -
ORA-00001: unique constraint (EDU.SYS_C007011) violated
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');
---> 무결성제약조건에 위배(이유: id aaa 중복)-> 명령의 228 행에서 시작하는 중 오류 발생 -
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')
오류 보고 -
ORA-00001: unique constraint (EDU.SYS_C007012) violated
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');
---> 값의수가 충분하지 않습니다(이유: name 은 null허용 않는다)
-- or 누락된 표현식-> 명령의 232 행에서 시작하는 중 오류 발생 -
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')
오류 발생 명령행: 233 열: 1
오류 보고 -
SQL 오류: ORA-00947: not enough values
00947. 00000 - "not enough values"
*Cause:
*Action:
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');
---> 체크제약조건에 위배되었습니다(이유: score가 0~100사이의 수 이어야함)
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');
---> 체크제약조건에 위배되었습니다(이유:marriage가 Y 또는 N이어야함) -> 명령의 237 행에서 시작하는 중 오류 발생 -
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')
오류 보고 -
ORA-02290: check constraint (EDU.SYS_C007009) violated
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');
---> 성공
값의 수 맞고/ not null 제약조건이 없었던 hire date -> 삽입됨
insert into user1(idx,id,name,phone,address,score,subject_code,marriage)
values(3,'ccc','park','010-000-0000','부산',80,100,'N');확인하면 다음과 같이 정상적으로 삽입되었음.
그러나 날짜가 20/10/21로 들어감(시스템의 오늘날짜를 기본값으로 해서) sysdate를 해놔서
문제)
--ex7) 테이블목록확인
select * from tab;
--ex8) user1, user2 테이블의 내용을 각각 확인
select * from user1;
select * from user2;
--ex9) user1 테이블의 구조확인
desc user1;
--ex10) 제약조건확인(제약조건의 이름을 생성하지 않은경우-user1)
select constraint_name, constraint_type
from user_constraints
where table_name='USER1';
--ex11) 제약조건확인(제약조건의 이름을 생성한 경우-user2)
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name='USER2';
컬럼과 테이블
[테이블수정]
-구문-
alter table 테이블명
add 컬럼명 데이터타입 [제약조건]
add constraint 제약조건명 제약조건타입(컬럼명)
modify 컬럼명 데이터타입
drop column 컬럼명 [cascade constraints]
drop primary key [cascade] | union (컬럼명,.....) [cascade] .... | constraint
제약조건명 [cascade]
-이름변경-
alter table 기존테이블명 rename to 새테이블명
rename 기존테이블명 to 새테이블명
alter table 테이블명 rename column 기존컬럼명 to 새컬럼명
alter table 테이블명 rename constraint 기존제약조건명 to 새제약조건명
옆에 쓰는 걸 컬럼 레벨이라고 한다. 위에서 한 작업은 모두 컬럼 레벨
create table exam1(
num number constraint PNUM primary key, --컬럼레벨
id varchar2(10) constraint UNIID unique,
name varchar2(10) constraint NONAME not null);
create table exam2(
num number,
id varchar2(10),
name varchar2(10) constraint NONAME2 not null, -- not null은 컬럼레벨만 가능
constraint PNUM2 primary key(num), -- 테이블 레벨 가능
constraint UNID2 unique(id));
select * from tab;제약 조건을 쓸 때 만들고 조건명을 써도 되고, 아래처럼 제약 조건을 밑으로 빼도 된다.
그러나 not null은 밑에 붙일 수 없고 옆에 조건명을 써줘야 한다.
위에서 PNUM 라고 썼기 때문에 아래에서는 PNUM2로 겹치지 않게 써줘야 한다.
제약조건 확인과 삭제 후 확인
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name='EXAM2'; -- 제약조건 확인
-- 제약조건삭제
-- exam2 테이블의 제약조건 삭제
alter table exam2 drop constraints NONAME2 cascade;
alter table exam2 drop constraints PNUM2 cascade;
alter table exam2 drop constraints UNID2 cascade;
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name='EXAM2'; -- 제약조건 확인
alter table exam2 add constraint NONAME2 check(name is not null);
alter table exam2 add constraint PNUM2 primary key(num);
alter table exam2 add constraint UNID2 unique(id);
제약조건 추가
문제풀이)
--ex12) 테이블명 변경 : test ==> user3
alter table test rename to user3;
select * from tab;
--ex13) 컬럼추가 : user3 == > phone varchar2(15)
select * from user3;
alter table user3 add phone varchar2(15);
--ex14) 제약조건추가 : user3 ==> id에 unique ,제약조건명 UID2
alter table user3 add constraint UID2 unique(id);
select constraint_name,constraint_type, search_condition
from user_constraints
where table_name='USER3';
--ex15) 컬럼추가 : user3 ==> no number (PK설정)
alter table user3 add no number primary key;
--ex16) 구조변경 : user3 ==> name char(10) 를 varchar2(10)로 바꿈
-- 주의) 자료형이 작은->큰 (가능) / 큰->작은(불가능)
select * from user3;
desc user3;
alter table user3 modify name char(5); -- 데이터가 없을 경우에는 축소로 변경 가능
alter table user3 modify name varchar2(10);
--ex17) 컬럼삭제 : user3 ==> address
alter table user3
drop column address ;
--ex18) 테이블삭제 user3
drop table user3;
select * from tab;
+ 자료가 들어있지 않을 때에는 큰사이즈 -> 작은사이즈 ex) char (10 ) -> char (5)로 변경 불가
반면 자료가 있어도 char(5) -> char(10)은 가능 / 더 큰 사이즈로는 가능
내용 없이 껍데기만 복사하기 & 이름 바꾸기
--ex19) user1의 구조만 복사해서 user4를 생성
-- idx -> bunho, id -> sid, name -> irum, address -> juso로 변경해서 복사
select * from user1; -- 구조 확인
desc user1; --> 제약조건 확인
-- 구조 껍데기만 복사할 때 where 1 = 0;
create table user4(bunho, sid, irum, juso)
as select idx, id, name, address from user1 where 1 = 0;
select*from user4;
제약조건 확인
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'USER1'; -- 제약조건이 있는 원본 user1
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'USER4'; --user4 제약조건이 이름에만
제약조건은 not null 외에 복사되지 않는다.
INSERT UPDATE DELETE
[ insert ]
: 테이블에 데이터(새로운 행)추가 -- 행의 수가 변경
insert into 테이블명 [ (column1, column2, .....)] values (value1,value2,.....)
- column과 values의 순서일치
- column과 values의 개수 일치
insert into 테이블명 values (value1, value2, ...);
---------------------------------------------------------------------------------
[ update ]
: 테이블에 포함된 기존 데이터수정 -- 행의 수가 변경되지 않음
전체 데이터 건수(행수)는 달라지지 않음
조건에 맞는 행(또는 열)의 컬럼값을 갱신할수 있다
update 테이블명 set 컬럼명1=value1, 컬럼명2=value2 ..... [where 조건절]
- where 이 생략이 되면 전체행이 갱신
- set절은 서브쿼리사용가능, default옵션 사용가능
---------------------------------------------------------------------------------
[ delete ]
: 테이블에 포함된 기존데이터를 삭제 -- 행의 수가 변경
행 단위로 삭제되므로 전체행수가 달라짐
delete [from] 테이블명 [where 조건절];
- where을 생략하면 전체행이 삭제됨 ( 데이터는 삭제되고 테이블 구조만 유지됨)
COMMIT/ ROLLBACK/ TRANSACTION
[ tracnsation처리 ]
: 일의 시작과 끝이 완벽하게 마무리(commit)
처리도중 인터럽트(interrupt:장애)가 발생하면 되돌아옴(rollback)
--ex19_1)insert ,update, delete테스트
select * from user4;
insert into user4(bunho,sid,irum,juso) values(1,'aaa','lee','서울');
insert into user4 values(2,'bbb','kim','부산');
insert into user4 values(3,'ccc','park','광주');
insert into user4(bunho,sid,irum) values(4,'ddd','hong');
insert into user4(bunho,sid,irum) values(5,'eee','kang');
insert into user4(irum) values('jang');
commit;
- commit을 안하면 CMD / developer창에서 차이가 남
- 콘솔창에서 rollback을 쓰면 developer에서만 추가한 것도 원상복구된다.
- commit은 일의 시작과 마무리가 완벽하게 될 경우 마지막으로 처리하는 것.
>> 임시 기억 장소에 있는 것이 문제가 없는 경우 commit 문제가 발생하면 rollback으로 없던 것으로 처리한다. 이 경우 임시 기억장치에 있는 것도 삭제 이를 보고 Transaction처리라 한다. <<
-> 데이터에 변경이 생기는 insert/ delete/ add ... 등의 경우는 commit해줘야 한다.
또한 오라클 종료시 자동 commit이 된다.
--[문제1]user4의 'hong'의 주소를 '강원'으로 바꾸고 ID를 'abc'로 바꾸기
update user4 set juso='강원', sid='abc' where irum='hong';
select * from user4;
commit;
--[문제2]user4의 주소가 '주'로 끝나는 데이터만 제외하고 모두 삭제하시오
delete from user4 where juso not like '%주' or juso is null;
select * from user4;
commit;
--[문제3]user4의 구조는 남기고 모든행을 삭제하시오
delete from user4;
select * from user4;
commit;
시퀀스
[시퀀스]
: 순차적으로 정수값을 자동으로 생성하는 객체
create sequence 시퀀스명
[increment by 증가값] [start with 시작값]
[maxvalue 최대값 | minvalue 최소값]
[cycle | nocycle]
[cache | nocache]
- increment by 증가값 : 증가/감소 간격(기본값 : 1)
- start with : 시작번호(기본값 : 1)
- maxvalue / minvalue : 시퀀스의 최대/최소값지정
- cycle/nocycle : 최대/최소값에 도달시 반복여부결정
- cache / nocache : 지정한수량만큼 메모리 생성여부결정 (최소값 : 2, 기본값 : 20)
시퀀스 생성/ 삭제
--ex20) 시퀀스생성 / 삭제
create sequence idx_sql increment by 2 start with 1 maxvalue 9 cycle nocache;
select idx_sql.nextval from dual; --다음 시퀀스 값(nextval)
select idx_sql.currval from dual; --현재 시퀀스 값(currval)
drop sequence idx_sql; -- 시퀀스 삭제
--ex21) 테이블생성과 시퀀스적용
--테이블명 : book
-- num number , pk
-- subject varchar2(50)
-- price number
-- year date
create table book(
num number primary key, --제약조건
subject varchar2(50),
price number,
year date
);
describe book;
select * from book;
-- 시퀀스 명 : num_seq
-- 1부터 1씩증가, nocycle, nocache
create sequence num_seq increment by 1 start with 1 nocycle nocache;
--2건 추가
-- 오라클 무작정 따라하기, 10000, 오늘날짜
-- 자바 3일 완성, 15000, 2017-01-12
insert into book (num, subject, price, year)
values (num_seq.nextval, '오라클 무작정 따라하기', 10000, sysdate);
insert into book (num, subject, price, year)
values (num_seq.nextval, '자바 3일 완성', 15000, '2017-01-12');
select * from book;
commit;
연도 포맷이 틀릴때 => 삽입X
insert into book
values(num_seq.nextval, 'JSP 달인되기',25000, to_char(sysdate, 'YYYY"년"MM"월"DD"일"')); --오류->명령의 453 행에서 시작하는 중 오류 발생 -
insert into book
values(num_seq.nextval, 'JSP 달인되기',25000, to_char(sysdate, 'YYYY"년"MM"월"DD"일"'))
오류 보고 -
ORA-01861: literal does not match format string
--트랜젝션 처리를 안했을 경우 오류
--콘솔창에서
update book set subject='JSP 20일 완성' where num=4;
commit;
--developer창에서
update book set subject='JSP 마스터북' where num=4;
commit;
--ex22) user2를 user3로 테이블 구조만 복사하시오
-- (컬럼명은 그대로 복사)
create table user3
as select * from user2 where 1=0;
desc user3;
--ex23) 테이블(idx->bunho, name -> irum, address -> juso) 을 복사하고
--id가 bbb인 레코드를 복사하시오
--원본테이블 : user1 / 사본테이블 : user5
create table user5(bunho, irum, juso)
as select idx, name, address from user1 where id='bbb'; -- as 를 붙이는것을 인라인(서브쿼리) 방식
select * from user5;
--[문제4]
--1)테이블명 : member
-- idx, 숫자(5), 기본키 / name, 문자(10), 널허용X / kor, 숫자(5) / eng, 숫자(5) / mat, 숫자(5)
create table member(
idx number(5) primary key,
name varchar2(10) not null,
kor number(5),
eng number(5),
mat number(5));
--2)시퀀스명 : m_seq, 1부터 1씩증가, nocycle, nocache
create sequence m_seq increment by 1 start with 1 nocycle nocache;
--3)데이타 : 둘리, 90, 80,95
-- 또치, 75, 90,65
-- 고길동, 100,95,90
-- 마이콜, 60,60,60
-- 도우넛, 75,80,75
-- 희동이, 80,78,90
-- idx는 자동으로 1씩증가값을 넣으시오
insert into member(idx, name, kor,eng,mat) values(m_seq.nextval, '둘리', 80, 80, 95);
insert into member(idx, name, kor,eng,mat) values(m_seq.nextval, '또치', 75,90,65);
insert into member(idx, name, kor,eng,mat) values(m_seq.nextval, '고길동', 100,95,90);
insert into member(idx, name, kor,eng,mat) values(m_seq.nextval, '마이콜', 60,60,60);
insert into member(idx, name, kor,eng,mat) values(m_seq.nextval, '도우넛', 75,80,75);
insert into member(idx, name, kor,eng,mat) values(m_seq.nextval, '희동이', 80, 78, 90);
commit;
--4)select문으로 다음과 같이 출력하시오
--조건1)평균별 내림차순 정렬하시오
--조건2)평균은 소수점이하 2째자리까지 출력하시오
--조건3)타이틀은 아래와 같이 처리해 주시오
--학번 이름 국어 영어 수학 총점 평균
-- -----------------------------------------------------
-- 1 둘리 90 80 96 266 88.66
select idx as "학번", name as "이름", kor as "국어", eng as "영어", mat as "수학",
(kor+eng+mat) as "총점", to_char(round((kor+eng+mat)/3, 2), '99,999.00') as "평균"
from member
order by 7 desc;
더보기
문법정리>
[테이블생성]
create table 테이블명(컬럼명1 컬럼타입 [제약조건],컬럼명2 컬럼타입 [제약조건],.....);
- 문자로 시작(30자이내) : 영문 대소문자,숫자,특수문자( _ , $ , # ),한글
- 중복되는 이름은 사용안됨
- 예약어(create, table, column등)은 사용할수 없다
- 자료형
number : number(전체자리,소수이하), number ==> 숫자형(가변형)
ex) number(7) 정수 / number(7,2) 실수
int : 정수형 숫자(고정형)
float / double : 실수형 숫자(고정형)
varchar/varchar2 : 문자,문자열(가변형) ==> 최대 4000byte
-- varchar는 가변적이기에 메모리 관리에 효율적이다. 늘어났다 줄어들었다 하니까
-- 이름/ 전화번호/ 메모 등을 하려면 ex)이름이 다섯글자일 수도 있으니까
-- 그런 경우에는 varchar2가 낫다.
-- 결혼여부 (Y/N), 성별(M/W)의 경우는 고정된 char가 낫다.
char : 문자,문자열(고정형) ==> 2000byte
date : 날짜형
clob : 문자열 ===> 최대4GB
blob: 바이너리형(그림,음악,동영상..) ===> 최대4GB
-- 게시판 제작시 따로 메모리를 할당해주는 것이 낫다.
- 제약조건
not null : 해당컬럼에 NULL을 포함되지 않도록 함 (컬럼)
-- ex_ 제품 주문시 주소는 not null해야 함 / null을 허용하지 않음
unique : 해당컬럼 또는 컬럼 조합값이 유일하도록 함 (컬럼,테이블)
-- ex_ 아이디 중복 확인 / 중복을 허용하지 않음
-- unique의 경우 하나의 null값은 허용
primary key : 각 행을 유일하게 식별할수 있도록함 (컬럼,테이블)
-- unique, not null 조건을 모두 포함/ 유일무이하면서 null하지 않다.
references table(column)
: 해당 컬럼이 참조하고 있는 (부모)테이블의 특정 (테이블)
컬럼값들과 일치하거나 또는 NULL이 되도록
보장함
check : 해당컬럼에 특정 조건을 항상 만족시키도록함 (컬럼, 테이블)
[참고] primary key = unique + not null
-- 걸러내는 것/ ex_ 0~100 값만 허용하는 과목 점수
ex) idx 일련번호 primary key
id 아이디 unique
name 이름 not null
phone 전화번호
address 주소
score 점수 check
subject_code 과목코드
hire_date 입학일 (기본값: 오늘날짜)
marriage 결혼 check (기본값 : N)
---------------------------------------------------------------------
- 제약조건확인
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등)
- 삭제 RULE
on delete cascade:대상 데이터를 삭제하고,해당 데이터를 참조하는 데이터도 삭제
on delete set null:대상 데이터를 삭제하고,해당 데이터를 참조하는 데이터는 NULL로 바꿈
on delete restricted:삭제대상 데이터를 참조하는 데이터가 존재하면 삭제할수 없음(기본값)
- 수정 RULE
on update cascade:대상 데이터를 수정하면,해당 데이터를 참조하는 데이터도 수정
---------------------------------------------------------------------------------
[테이블수정]
-구문-
alter table 테이블명
add 컬럼명 데이터타입 [제약조건]
add constraint 제약조건명 제약조건타입(컬럼명)
modify 컬럼명 데이터타입
drop column 컬럼명 [cascade constraints]
drop primary key [cascade] | union (컬럼명,.....) [cascade] .... | constraint
제약조건명 [cascade]
-이름변경-
alter table 기존테이블명 rename to 새테이블명
rename 기존테이블명 to 새테이블명
alter table 테이블명 rename column 기존컬럼명 to 새컬럼명
alter table 테이블명 rename constraint 기존제약조건명 to 새제약조건명
---------------------------------------------------------------------------------
[테이블복사]
- 서브쿼리를 이용한 테이블생성및 행(레코드)복사
- 서브쿼리를 이용해서 복사한경우 not null을 제외한 제약조건은 복사안됨
(not null제약조건도 sys_*****로 복사됨)
- 구문 -
create table 테이블명([컬럼명1,컬럼명2.....]) as 서브쿼리
- 구조만복사 -
create table 테이블명1
as select * from 테이블명2 where 1=0
---------------------------------------------------------------------------------
[시퀀스]
: 순차적으로 정수값을 자동으로 생성하는 객체
create sequence 시퀀스명
[increment by 증가값] [start with 시작값]
[maxvalue 최대값 | minvalue 최소값]
[cycle | nocycle]
[cache | nocache]
- increment by 증가값 : 증가/감소 간격(기본값 : 1)
- start with : 시작번호(기본값 : 1)
- maxvalue / minvalue : 시퀀스의 최대/최소값지정
- cycle/nocycle : 최대/최소값에 도달시 반복여부결정
- cache / nocache : 지정한수량만큼 메모리 생성여부결정 (최소값 : 2, 기본값 : 20)
---------------------------------------------------------------------------------
[ 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을 생략하면 전체행이 삭제됨
- 데이터는 삭제되고 테이블 구조는 유지됨
---------------------------------------------------------------------------------
[ tracnsation처리 ]
: 일의 시작과 끝이 완벽하게 마무리(commit)
처리도중 인터럽트(interrupt:장애)가 발생하면 되돌아옴(rollback)
==================================================================================================
--계정을 바꾸시오 : edu/1234
select * from tab;
--ex1) 테이블 : test
--id number(5) -> 아무것도 안쓰면 기본값 6임, name char(10), address varchar2(50);
create table test(
id number(5),
name char(10),
address varchar2(50)
);
desc test; --테이블의 구조 확인
select * from test;
--ex2) 테이블명 : user1
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')));
desc user1;
--ex3) 제약조건확인
select constraint_name, constraint_type
from user_constraints
where table_name='USER1';
--1 SYS_C004081 C
--2 SYS_C004082 C
--3 SYS_C004083 C
--4 SYS_C004084 P
--5 SYS_C004085 U
--ex4) 테이블명 : user2
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 * from tab;
--ex5) 제약조건확인
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name='USER2';
--1 NOTNAME C
--2 CKSCORE C
--3 CKMARR C
--4 PKIDX P
--5 UNID U
--또는
select *
from all_tab_columns
where table_name='USER2';
select * from user1;
--ex6) 추가
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');
---> 무결성제약조건에 위배(이유: idx 1 중복)
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');
---> 무결성제약조건에 위배(이유: id aaa 중복)
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');
---> 값의수가 충분하지 않습니다(이유: name 은 null허용 않는다)
-- or 누락된 표현식
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');
---> 체크제약조건에 위배되었습니다(이유: score가 0~100사이의 수 이어야함)
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');
---> 체크제약조건에 위배되었습니다(이유:marriage가 Y 또는 N이어야함)
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');
---> 성공
insert into user1(idx,id,name,phone,address,score,subject_code,marriage)
values(3,'ccc','park','010-000-0000','부산',80,100,'N');
---> 성공
--ex7) 테이블목록확인
select * from tab;
--ex8) user1, user2 테이블의 내용을 각각 확인
select * from user1;
select * from user2;
--ex9) user1 테이블의 구조확인
desc user1;
--ex10) 제약조건확인(제약조건의 이름을 생성하지 않은경우-user1)
select constraint_name, constraint_type
from user_constraints
where table_name='USER1';
--ex11) 제약조건확인(제약조건의 이름을 생성한 경우-user2)
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name='USER2';
===============================================================
[컬럼레벨 / 테이블레벨]
create table exam1(
num number constraint PNUM primary key, --컬럼레벨
id varchar2(10) constraint UNIID unique,
name varchar2(10) constraint NONAME not null);
create table exam2(
num number,
id varchar2(10),
name varchar2(10) constraint NONAME2 not null, -- not null은 컬럼레벨만 가능
constraint PNUM2 primary key(num), -- 테이블 레벨 가능
constraint UNID2 unique(id));
select * from tab;
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name='EXAM2'; -- 제약조건 확인
-- 제약조건삭제
-- exam2 테이블의 제약조건 삭제
alter table exam2 drop constraints NONAME2 cascade;
alter table exam2 drop constraints PNUM2 cascade;
alter table exam2 drop constraints UNID2 cascade;
alter table exam2 add constraint NONAME2 check(name is not null);
alter table exam2 add constraint PNUM2 primary key(num);
alter table exam2 add constraint UNID2 unique(id);
desc exam2;
select constraint_name,constraint_type, search_condition
from user_constraints
where table_name='EXAM2';
================================================================
--ex12) 테이블명 변경 : test ==> user3
alter table test rename to user3;
select *from tab; --- 이거 써서 확인
select * from user3;
--ex13) 컬럼추가 : user3 == > phone varchar2(15)
alter table user3 add phone varchar2(15);
--ex14) 제약조건추가 : user3 ==> id에 unique ,제약조건명 UID2
alter table user3 add constraint UID2 unique(id);
select constraint_name,constraint_type, search_condition
from user_constraints
where table_name='USER3';
--ex15) 컬럼추가 : user3 ==> no number (PK설정)
****************
alter table user3
add no number primary key;
--ex16) 구조변경 : user3 ==> name char(10) 를 varchar2(10)로 바꿈
-- 주의) 자료형이 작은->큰 (가능) / 큰->작은(불가능)
select * from user3;
desc user3;
alter table user3 modify name char(5); -- 데이터가 없을 경우에는 축소로 변경 가능
alter table user3 modify name varchar2(10);
--ex17) 컬럼삭제 : user3 ==> address
alter table user3
drop column address ;
--ex18) 테이블삭제 user3
drop table user3;
select * from tab;
==> drop table user3 purge --휴지통에 넣지 않고 바로 삭제
==> flashback table user3 to before drop; --휴지통에서 되살리기 (oracle 10g에서 가능)
--ex19) user1의 구조만 복사해서 user4를 생성
-- idx -> bunho, id -> sid, name -> irum, address -> juso로 변경해서 복사
--ex19_1)insert ,update, delete테스트
--[문제1]user4의 'hong'의 주소를 '강원'으로 바꾸고 ID를 'abc'로 바꾸기
--[문제2]user4의 주소가 '주'로 끝나는 데이터만 제외하고 모두 삭제하시오
--[문제3]user4의 구조는 남기고 모든행을 삭제하시오
---------------------------------------------------------------------------
--ex20) 시퀀스생성 / 삭제
--ex21) 테이블생성과 시퀀스적용
--트랜젝션 처리를 안했을 경우 오류
--콘솔창에서
--developer창에서
--ex22) user2를 user3로 테이블 구조만 복사하시오
-- (컬럼명은 그대로 복사)
select constraint_name, constraint_type,search_condition
from user_constraints
where table_name='USER3' -- not null을 제외하고는 제약조건이 복사 안됨
-- not null제약조건도 sys_*****로 복사됨
select * from user3; -- 내용없음을 확인
--ex23) 테이블(idx->bunho, name -> irum, address -> juso) 을 복사하고
--id가 bbb인 레코드를 복사하시오
--원본테이블 : user1 / 사본테이블 : user5
================================================================================================
--ex24) 테이블생성후 행추가
--테이블명 : dept
--deptno number ==> 기본키, 제약조건명(DNO)
--dname varcahr2(30) ==> 널 허용안됨, 제약조건명(DNAME)
--테이블명 : emp
--empno number ==> 기본키,제약조건명(ENO)
--ename varchar2(30) ==> 널허용안됨, 제약조건명(ENAME)
--deptno number ==> 외래키, 제약조건명(FKNO),
-- 대상데이터를 삭제하고 참조하는데이터는 NULL로 바꿈
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번부서 없음(무결성제약조건위배)-부모키가 없습니다
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허용 안함
commit;
select * from emp;
select * from dept;
--ex25) 삭제
--dept테이블에서 20번 부서를 삭제하시오
--삭제된 행을 되돌리시오
--참고)
--dept테이블의 deptno=20과 emp테이블의 deptno=20이 releation 이 형성된경우
-- ==> 삭제안됨(자식레코드가 발견되었습니다)
--on delete cascade를 설정하면 부모테이블과 자식테이블의 레코드가 함께 삭제됨
--on delete set null를 설정하면 부모테이블은 삭제되고 자식테이블은 null로 바뀜
--ex27) 수정(update)
--장동건의 부서를 30으로 수정하시오
--================================================================================================
--[문제4]
--1)테이블명 : member
-- idx, 숫자(5), 기본키 / name, 문자(10), 널허용X / kor, 숫자(5) / eng, 숫자(5) / mat, 숫자(5)
--2)시퀀스명 : m_seq, 1부터 1씩증가, nocycle, nocache
--3)데이타 : 둘리, 90, 80,95
-- 또치, 75, 90,65
-- 고길동, 100,95,90
-- 마이콜, 60,60,60
-- 도우넛, 75,80,75
-- 희동이, 80,78,90
-- idx는 자동으로 1씩증가값을 넣으시오
--4)select문으로 다음과 같이 출력하시오
--조건1)평균별 내림차순 정렬하시오
--조건2)평균은 소수점이하 2째자리까지 출력하시오
--조건3)타이틀은 아래와 같이 처리해 주시오
--학번 이름 국어 영어 수학 총점 평균
-- -----------------------------------------------------
-- 1 둘리 90 80 96 266 88.66