04day DB Oracle

1. 데이터 조작---데이터 조작 언어(DML) **inset into 테이블 명 values (모든 컬럼의 데이터(순서대로));

SQL> insert into dept2 values (90, '인사과', '서울'); 

SQL> insert into dept2 values (901, '인사과', '서울'); //에러남 //데이터의 형식 지켜야 함 
SQL> insert into dept2 values (90, 인사과, '서울'); //에러마다 코드가 있음 

**inset into 테이블 명(컬럼명 ...) values (컬럼명 순서대로 데이터);

SQL> insert into dept2(deptno, loc, dname) values (91, '서울', '인사과'); 
//데이터를 넣을때 순서를 정해줌 

SQL> insert into dept2(deptno, dname) values (93,'인사과'); 
// 데이터 형식에 <널?>이 빈칸으로 되어있으면 null을 허용함 

SQL> insert into dept2 values (94, '인사과', null); 
//데이터 투입시에 null이라고 적어줘도 됨 

*특수값 입력

SQL> create table log ( 
  2  seq number(2), 
  3  users varchar2(10), 
  4  wdate date 
  5  ); 
SQL> insert into log values (1, user, sysdate); 

*날짜 입력

SQL> insert into log values(2, user,to_date('27-6월-16', 'DD-MON-YY')); 
  //날짜 형식 지정 

SQL> insert into log values(3, user, '16/06/27'); 
  //날짜 기본 형식 

**다른 테이블로부터 행 복사

SQL> create table dept3 as select * from dept where 1 !=1 
SQL> insert into dept3 select * from dept;              //테이블 내용 전체 복사 

SQL> create table dept4 ( 
  2  ename varchar2(10), 
  3  sal number(7,2)); 
SQL> insert into dept4(sal, ename) 
  2  select sal, ename                                     //테이블 내용 선택 복사 
  3  from emp 
  4  where deptno=10; 

2. UPDATE  --저장되어 있는 행을 변경 **update 테이블명 컬럼명= 값, 컬럼명= 값, ...

SQL> update dept2 
  2  set loc='부산';        //열값 전체 바꾸기 

SQL> update dept2 
  2  set loc='';          //데이터 값을 비울 수 있음 //오라클에서는 공백을 null로 인식 
SQL> update dept2 
  2  set loc=null;       //null 값을 주는 확실한 방법 

SQL> select * from dept2 where loc is null;      //null 검사 

SQL> update dept2 
  2  set loc=' ';             //스페이스바 처리하면 null 값이 아님 

SQL> update dept2 
  2  set dname='총무과' 
  3  where deptno = 90;        //행을 선택함 

3. DELETE    ----복원가능 / dml계열 **delete from 테이블명 [where 조건]

SQL> delete from dept2;                 //모든행을 삭제 

SQL> delete from dept2 
  2  where deptno = 10;                  //특정행 삭제 

4. TRUNCATE -------복원 불가함 / ddl계열

SQL> truncate table dept2; 

5. MERGE   ---동기화  //데이터 존재하면 update, 데이터 없으면 insert

SQL> merge into emp2 n 
  2    using emp3 o 
  3    on (n.empno=o.empno) 
  4    when matched then 
  5       update set 
  6       n.ename = o.ename, 
  7       n.job = o.job, 
  8       n.sal = o.sal 
  9    when not matched then 
 10     insert (n.empno, n.ename, n.job, n.sal) 
 11     values (o.empno, o.ename, o.job, o.sal); 

**merge에 조건 추가

 insert into emp20(empno, ename, job, sal) 
 select empno, ename, job, 0 
 from emp 
 where deptno = 20; 
 
 merge into emp20 n 
 using emp o 
 on (n.empno = o.empno) 
 when matched then 
  update set 
  n.ename = o.ename,n.job = o.job, n.sal = o.sal 
  where o.job='CLERK'                                   //where 구문 
 when not matched then 
  insert (n.empno, n.ename, n.job, n.sal) 
  values (o.empno, o.ename, o.job, o.sal) 
  where o.job='CLERK'; 
 
select * from emp20; 

6. Transaction 트랜잭션  --자바의 스레드 싱크로나이즈와 유사함                                 -- 한 가지 작업을 수행하는 동안 lock을 건다                                 -- 아이디는 같은데 열결이 다른사람 (세션이 다름)의 출돌 방지 **commit

SQL> commit;   //입력한 모든 작업을 완료했다는 것을 알림 
                     //다른 접속에서 불러오는게 가능해짐 

**rollback

SQL> rollback;   //입력한 작업을 취소함 

SQL> rollback;  //롤백은 작업의 갯수와 상관없이 commit전까지 모든 작업을 되돌림 

SQL> exit   //정상종료시 commit실행됨  //비정상 종료시 rollback 

SQL> create table a (col varchar2(10));  //테이블 생성시 commit 실행됨 

 **트랜젝션은 행에 lock 을 건다

                       // 다른 세션에서 실행명령을 수행해도 수행되지 않음

**savepoint

7. 테이블의 종류

SQL> desc user_tables; 

**사용자가 소유하고 있는 테이블의 이름

SQL> select table_name 
  2  from user_tables; 

**사용자가 소유하고 있는 객체의 종류

SQL> SELECT DISTINCT OBJECT_TYPE 
  2  FROM USER_OBJECTS; 

**사용자가 소유하고 있는 테이블, 뷰, 동의어, 시퀀스

SQL> select * 
  2  from user_catalog; 

8. DEFAULT 옵션

SQL> create table b ( 
  2  col1 varchar2(10), 
  3  col2 date default sysdate);   //디폴드 값을 지정할 수 있음 
SQL> insert into b values( 'c', default); //데이터 생성시 디폴트 사용 

SQL> update b set col2 = default where col1='b';  //데이터 수정시 디폴트 사용 

9. 제약조건  constraint --테이블 안의 데이터 무결성을 위한 제약장치를 주는 것                               --잘못된 데이터 입력 방지 **not null  --컬럼을 필수로 입력하도록

SQL> create table sawon( 
  2  s_no number(4), 
  3  s_name varchar2(10) not null,      //NOT NULL 제약조건은 컬럼 수준에서만 지정가능 
  4  s_hiredate date constraint sawons_s_hiredate_nn not null);  //제약조건 이름 설정 

SQL> insert into sawon values( 1, '길동', sysdate); 
SQL> insert into sawon values( 2, '문수', null);                          //에러남 
SQL> insert into sawon (s_no, s_name) values( 2, '문수');            //에러남 

**unique  --중복값 입력 못하도록  //컬럼단위 제약조건

SQL> create table sawon( 
  2  s_no number(4), 
  3  s_name varchar2(10), 
  4  s_sal number(10), 
  5  s_email varchar2(20) constraint sawon_s_email_uk unique); 
 
SQL> insert into sawon values(1,'길동',1000, 'GDH@XYZ.COM'); 
SQL> insert into sawon values(2,'콩쥐',2000, 'GDH@XYZ.COM');   //에러남 

SQL> insert into sawon values(2,'콩쥐',2000, null); 
SQL> insert into sawon values(3,'팥쥐',3000, null);  //null은 중복검사 하지 않음 

//테이블 단위 제약조건

SQL> create table sawon( 
  2  s_no number(4), 
  3  s_name varchar2(10), 
  4  s_sal number(10), 
  5  s_email varchar2(20), 
  6  constraint sawon_s_email_uk unique (s_email)); 
//테이블 단위 제약조건 2개 이상의 컬럼 
SQL> create table sawon( 
  2  s_no number(4), 
  3  s_name varchar2(10), 
  4  s_sal number(10), 
  5  s_email varchar2(20), 
  6  constraint sawon_s_email_uk unique (s_no, s_name)); 

**primary key --필수 + 중복x                    --기본키 : 테이블을 한 행 단위로 접근이 가능함

SQL> create table sawon( 
  2  s_no number(4) constraint swon_s_no_pk primary key, 
  3  s_name varchar2(10), 
  4  s_sal number(10)); 

SQL> insert into sawon values(null, '길동', 1000);  //에러남 //null값 줄 수 없음 
SQL> insert into sawon values(1, '길동', 1000); 
SQL> insert into sawon values(1, '콩쥐', 2000);     //에러남 //중복값 줄 수 없음 

//테이블단위 제약조건 2개이살 컬럼 설정

SQL> create table sawon( 
  2  s_no number(4), 
  3  s_name varchar2(10), 
  4  s_sal number(10), 
  5  constraint sawon_pk primary key (s_no, s_name)); 
 
**foreign key  --참조키 
//참조할 테이블을 먼저 만든다 
SQL> create table buseo( 
  2  b_no number(4) constraint buseo_b_no_pk primary key, 
  3  b_name varchar2(10), 
  4  b_loc varchar2(10)); 
SQL> insert into buseo values (100, '인사과', '서울'); 
SQL> insert into buseo values (200, '총무과', '대전'); 
SQL> insert into buseo values (300, '경리과', '부산'); 
 
//본 테이블에서 참조할 부모테이블의 프라이머리키를 지정함 
SQL> create table sawon( 
  2  s_no number(4) constraint sawon_s_no_pk primary key, 
  3  s_name varchar2(10), 
  4  s_sal number(5), 
  5  b_no number(4) constraint swaon_b_no_fk references buseo(b_no)); 

//데이터삽입

SQL> insert into sawon values(1,'길동', 1000, 100);   //부모의 고유키(100)를 참조함 
SQL> insert into sawon values(2,'콩쥐', 2000, 500);   //에러남 //참조 부모키가 없음 
SQL> insert into sawon values(2,'콩쥐', 2000, 100);   //부모의 고유키 중복참조 가능 
SQL> insert into sawon values(3,'팥쥐', 3000, null);   //참조하지 않음 

//데이터 수정

SQL> update buseo set b_no=400 where b_no=100; //에러남 //자식키가 참조하고있음 
SQL> update buseo set b_no=400 where b_no=200; 

SQL> update sawon set b_no=400 where s_no=1; 
SQL> update sawon set b_no=200 where s_no=1; //에러남 //참조 부모키가 없음 
//데이터 삭제 
SQL> delete from buseo where b_no= 400; //에러남 //자식키가 참조하고있음 

//테이블 삭제

SQL> drop table buseo;   //에러남 //자식키가 참조하고 있음 

**check  --데이터를 검사

SQL> create table sawon( 
  2  s_no number(4), 
  3  s_name varchar2(10), 
  4  s_sal number(10) constraint sawon_s_sal_ck check (s_sal>0));        //조건 설정 
SQL> insert into sawon values (1, '길동', 1000); 
SQL> insert into sawon values (2, '콩쥐', -1000);  //에러남 조건에 위배됨 

//제약조건은 USER_CONSTRAINTS 데이터 딕셔너리를 이용하여 검색 할 수 있다 10. 제약조건의 관리 **제약조건의 추가 add constraint //테이블 생성

SQL> create table sawon( 
  2  s_no number(4) constraint sawon_s_no_pk primary key, 
  3  s_name varchar2(10), 
  4  s_mgr number(4)); 
//제약조건 추가 
SQL> alter table sawon 
  2  add constraint sawon_s_mgr_fk foreign key (s_mgr) 
  3  references sawon(s_no); 

//제약조건 조회

SQL> select constraint_name, constraint_type 
  2  from user_constraints 
  3  where lower(table_name) = 'sawon'; 

//제약조건 삭제

SQL> alter table sawon 
  2  drop constraint sawon_s_mgr_fk; 

//연관 제약조건 모두 삭제

SQL> alter table sawon 
  2  drop primary key;              //에러남 //참조되고있음 
SQL> alter table sawon 
  2  drop primary key cascade;   //cascade 옵션으로 참조키도 지움 

//제약조건 기능 정지

SQL> alter table sawon 
  2  disable constraint sawon_s_no_pk; 
SQL> insert into sawon values (2, '콩쥐', 1); 
SQL> insert into sawon values (2, '콩쥐', 1); //제약조건을 정지했으므로 같은값 입력가능 

//제약조건 활성화

SQL> alter table sawon 
  2  enable constraint sawon_s_no_pk;  //primary key가 중복되어서 활성화불가함 
SQL> delete from sawon where s_no=2; 
SQL> alter table sawon 
  2  enable constraint sawon_s_no_pk;  //지우고 나서 활성화 가능함 

11. 제약조건의 확인 //제약조건 유형 확인 //C인 경우는 CHECK 또는 NOT NULL //P인 경우는 PRIMARY KEY //R인 경우는 FOREIGN KEY //U인 경우는 UNIQUE

SQL> select constraint_name, constraint_type, search_condition 
  2  from user_constraints 
  3  where table_name ='EMP'; 

//제약조건 컬럼확인

SQL> select constraint_name, column_name 
  2  from user_cons_columns 
  3  where table_name ='EMP';