[새싹] 현대IT&E 231031 기록 - Oracle 7~11

최정윤·2023년 10월 31일

oracle sql

새싹

목록 보기

11/67

11. 서브쿼리

11.3 서브 쿼리를 이용하여 테이블을 생성하고, 데이터를 조작하기

11.3.1 서브 쿼리로 테이블 생성하기

create table emp02
as
select * from emp;

desc emp02;
describe emp02;
desc emp;
describe emp;

원하는 컬럼으로 구성된 복제 테이블 생성하기

create table emp03
as
select empno, ename from emp;

select * from emp03;

-- emp03 테이블 제거 (휴지통으로 이동한다.)
drop table emp03;

-- emp03 테이블 제거 (휴지통으로 이동하지 않고 영구삭제한다.)
drop table emp03 purge;

-> purge와 같은 역할을 한다.

11.3.2 테이블의 구조만 복사하기

create table emp06
as
select * from emp where 1=0;
-- where 1=0; 조건은 항상 거짓이기 때문에 테이블의 데이터는 가져오지 않고 구조만 복사하게 된다.

11.3.3 서브 쿼리로 데이터를 삽입하기

-- dept 테이블과 동일한 구조의 테이블 이름을 dept02로 하여 빈 테이블 생성하
create table dept01
as
select * from dept where 1=0;

-- 테이블 구조 살피기
desc dept01;

-- 테이블 내용 살피기 
select * from dept01;

-- 테이블에 내용 추가하기
insert into dept01
select * from dept;

07. 테이블 구조를 생성, 변경 및 제거하는 DDL

7.1 테이블 구조를 만드는 CREATE TABLE 문

-- 사원 정보를 저장하기 위한 테이블 생성하기
create table emp01 (
empno number(4),
ename varchar2(14),
sal number(7, 3)
);

desc emp01;

7.2 테이블 구조를 변경하는 ALTER TABLE 문

7.2.1 컬럼 추가

-- 사원 테이블에 날짜 타입을 가지는 birth 컬럼 추가하기 
alter table emp01
add(birth date);

7.2.2 컬럼 변경

-- 사원 이름 컬럼 크기 변경하기
alter table emp01
modify ename varchar2(30);

7.2.3 컬럼 제거

ALTER TABLE table_name
DROP COLUMN column_name;

alter table emp01
drop column ename;

7.2.4 SET UNUSED

-- 사원 테이블에서 사원번호 제거하기
alter table emp01
set unused (empno);

7.3 테이블명을 변경하는 RENAME 문

-- 테이블명 변경하기
rename emp01 to emp02;

select * from tab;
select * from user_tables;
select * from user_objects;

7.4 테이블 구조를 제거하는 DROP TABLE 문과 모든 데이터를 제거하는 TRUNCATE TABLE 문

-- 테이블 제거하기
drop table emp02;
desc emp02;

-- 삭제 테이블 복구하기
flashback table emp02 to before drop;
select * from tab;

-- 테이블 삭제 후 휴지통 비우기
drop table emp02 purge;
purge recyclebin;

7.5 데이터 딕셔너리

USER_: 자신의 계정이 소유한 객체 등에 관한 정보 조회
ALL_: 자신 계정 소유 또는 권한을 부여 받은 객체 등에 관한 정보 조회
DBA_: 데이터베이스 관리자만 접근 가능한 객체 등의 정보 조회

7.5.1 USER_ 데이터 딕셔너리

select table_name from user_tables;

7.5.2 ALL_ 데이터 딕셔너리

접두어로 ALL이 붙은 데이터베이스는 전체 사용자와 관려된 뷰로, 사용자가 접근할 수 있는 모든 객체에 대한 정보를 조회할 수 있다.

-- all_tables로 테이블에 대한 정보 조회하기
show user;
select * from all_tables;

7.5.3 DBA_ 데이터 딕셔너리

-- dba_tables로 테이블에 대한 정보 조회하기
select * from dba_tables;

08. 테이블의 내용을 추가, 수정, 삭제하는 DML과 트랜잭션

8.1 테이블에 내용을 추가하는 INSERT 문

insert into table_name
(column_name, ...)
values(column_value, ...);

-- conn tester1
-- 부서 정보를 저장하기 위한 테이블 생성하기
create table dept01(
depno number(2),
dname varchar2(14),
loc varchar2(13)
);
desc dept01;

-- 경리부를 입력하기
insert into dept01 values(10, '경리부', '서울');
insert into dept01(deptno, dname) values(20, '테스트');
select * from dept01;

-- 명시적으로 NULL값 삽입하기 
insert into dept01
values(40, '전산부', NULL);

select * from dept01;

-- 공백문자 삽입하기
insert into dept01
values(50, '기획부', '');

select * from dept01;

-- 사원 정보를 저장하기 위한 테이블 생성하기
create table emp02(
empno number(4),
ename varchar2(10),
job varchar2(9),
hiredate date,
deptno number(2)
);

desc emp02;

-- TO_DATE 함수로 날짜 데이터 입력하기
insert into emp02
values(1002, '한예슬', '대리', to_date('2014, 05, 01', 'YYYY, MM, DD'), 20);
select * from emp02;

insert into emp02
values(7020, 'JERRY', 'salesman', sysdate, 30);
select * from emp02;

8.2 테이블의 내용을 수정하는 UPDATE 문

update dept01
set dname='생산부'
where deptno=10;

-- WHERE 절로 특정 로우만 수정하기 
update dept01
set dname = '생산부', loc='부산'
where deptno=20;
commit;

update dept01
set dname = 'todtksqn', loc='부산';

8.3 테이블의 내용을 삭제하는 DELETE 문

delete dept01;
rollback;

select * from dept01;

-- where 절로 특정 로우만 삭제하기 
delete dept01
where deptno=10;

09. 데이터 무결성을 위한 제약조건

9.1 무결성 제약조건의 개념과 종류

데이터 무결성 제약조건이란, 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서, 테이블을 생성할 때, 각 컬럼에 대해서 정의하는 여러 가지 규칙을 말한다.

insert into dept
values(10, 'test', 'test');

NOT NULL: NULL을 허용하지 않는다.
UNIQUE: 중복된 값을 허용하지 않는다. 항상 유일한 값을 갖도록 한다.
PRIMARY KEY: NULL을 허용하지 않고 중복된 값을 허용하지 않는다. NOT NULL 조건과 UNIQUE 조건을 결합한 형태이다.
FOREIGN KEY: 참조되는 테이블에서 컬럼의 값이 존재하면 허용한다.
CHECK: 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만을 허용한다.

9.2 제약조건 확인하기

select * from dept;

insert into dept(deptno, dname, loc)
values(null, '개발부', '서울');

select * from user_constraints;

create table test01(
name varchar2(10) not null);
create table test02(
name varchar2(10) check(name is not null));
create table test03(
name varchar2(10),
constraint ck_mycheck check(name is not null));

select constraint_name, constraint_type, table_name
from user_constraints;

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;

9.3 필수 입력을 위한 NOT NULL 제약조건

insert into emp02
values(null, null, '사원', '2000/01/02', 20);
select

9.3.1 컬럼 레벨 정의 방법으로 제약조건 지정하기

컬럼 레벨, 테이블 레벨

create table emp02(
empno number(4) NOT NULL,
ename varchar2 (10) not null,
job varchar2(9),
deptno number(2)
);

insert into emp02
values(null, null, '사원', 30);

desc emp02;

insert into emp02
values(1000, '허준', '사원', 30);

9.4 유일한 값만 허용하는 UNIQUE 제약조건

UNIQUE 제약조건을 설정하여 테이블 생성하기

create table emp03(
empno number(4) UNIQUE,
ename varchar2(10) NOT NULL,
job varchar2(9),
deptno number(2)
);

-> NULL값은 UNIQUE값으로 사용할 수 없다.

9.6 데이터 구분을 위한 PRIMARY KEY 제약조건

-- primary key 제약조건을 주어 테이블 생성하기 
create table emp05(
empno number(4)
CONSTRAINT EMP05_EMPNO_PK PRIMARY KEY,
ename varchar2(10)
CONSTRAINT EMP05_ENAME_NN NOT NULL,
job varchar2(9),
deptno number(2)
);

-- 사원 정보 추가하기
insert into emp05 values(1000, '허준', '사원', 30);
insert into emp05 values(1000, '홍길동', '과장', 20);
insert into emp05 values(1000, '이순신', '과장', 20);

desc user_constraints;

9.7 참조 무결성을 위한 foreign key 제약조건

-- 제약 조건 살피기 
select constraint_name, constraint_type, table_name, table_name
from user_constarints
where table_name in('DEPT');

-- 외래키 제약조건을 주어 테이블 생성하기
create table emp06(
empno number(4)
CONSTRAINT EMP06_EMPNO_PK PRIMARY KEY,
ename varchar2(10)
CONSTRAINT EMP06_ENAME_NN NOT NULL,
job varchar2(9),
deptno number(2)
CONSTRAINT EMP06_DEPTNO_FK REFERENCES DEPT(DEPTNO)
);

-- 새로운 사원을 30번 부서에 소속시킥
insert into emp06
values(1010, '홍길동', '사원', 30);

-- 사원 테이블 살피기
select *
from emp06;

-- 새로운 사원을 존재하지 않는 50번 부서에 소속시키기
insert into emp06
values(1010, '홍길동', '사원', 50);

-- 제약조건 살피기
select constraint_name, constraint_type, r_constraint_name, table_name
from user_constraints
where table_name in ('EMP06');

9.8 CHECK 제약조건

-- CHECK 제약조건을 주어 테이블 생성하기
create table emp07(
empno numer(4)
CONSTRAINT EMP07_EMPNO_PK PRIMARY KEY,
ename varchar2(10)
CONSTRAINT EMP07_ENAME_NN NOT NULL,
sal number(7, 2) CONSTRAINT EMP07_SAL_CK
CHECK(SAL BETWEEN 500 AND 5000),
gender varchar2(1) CONSTRAINT EMP07_GENDER_CK
CHECK(GENDER IN('M', 'F'))
);

최정윤

개발 기록장

이전 포스트

[새싹] 현대IT&E 231030 기록 - Oracle 10~11

다음 포스트