UIUX _국비과정 0611 [오라클DB SQL 쿼리문]

내가 설치한 오라클 버전 : 21c Express Edition

 

컴퓨터 재시작 했을 때 터미널에서

💡 colima start --memory 4 --arch x86_64

💡 docker start oracle

 

계정

• system / pass
• scott / tiger
• oe / oe

 

시험일정 )

수 → 자바스크립트 [퀴즈만들기] 제이쿼리로 바꾸기

금 → 오라클

---

 

🍀 DDL

-- 테이블 만드는 방식 알아보기
-- DDL (data definition language)
-- creat : 객체 생성
-- alter : 객체 수정
-- drop : 객체 삭제
-- truncate : 테이블을 초기화 (전체 데이터 삭제)

-- create table 테이블명 (
-- 컬럼명1 타입, 
-- 컬럼명2 타입,
-- 컬럼명3 타입,
-- );

-- 컬럼이름을 만든 후 타입에 사용
-- 숫자 : number(4), number(7,2)
-- 문자 : char(10) : 고정형, varchar2(10) : 가변형
-- 문자를 다룰 때는 가변형을 주로 사용한다
-- 날짜 : date, timestamp
-- 큰 데이터를 담을 때 : lob, blob 

-- 테이블명, 객체이름
-- 공백, 특수문자, 예약어 안됨

 

테이블을 만드는 방식 두 가지

1. 직접 만들기
2. 서브쿼리문을 사용한 테이블 생성

 

서브쿼리문을 사용해 테이블 만들기

-- 서브쿼리문을 사용한 테이블 생성 (테이블 복사)
-- 테스트용, 백업용으로 주로 만든다
create table emp02
as 
select * from emp;

desc emp02;

select * from emp02;

create table emp03
as
select empno, ename from emp;


create table emp04
as
select * from emp
where deptno = 30;

desc emp04;

select * from emp04;

-- 구조만 복사 
create table emp05
as
select * from emp
where 1 = 0;

select * from emp05;

 

 

객체 수정

-- alter : 객체 수정 
-- 테이블을 대상으로 컬럼을 수정 (add, modify, drop)

desc emp01;

alter table emp01 
add (job varchar2(9));

desc emp01;



-- modify
-- 해당 컬럼의 크기나 타입 수정
-- 기존 데이터에 문제가 되지 않는 선에서 수정해야 한다

alter table emp01
modify (job varchar(30));

desc emp01;

 

 

객체 삭제

-- 삭제
alter table emp01
drop column job;

desc emp01;

select * from tab;

drop table emp01;

삭제를 해도 임시로 휴지통에 보관을 해서 다시 사용할 수 있다!

 

🗑 휴지통에서 데이터 가져오기, 비우기

-- 휴지통 정보를 확인
show recyclebin;

-- 휴지통에서 원래 위치로 가져다 놓기
flashback table emp01
to before drop;

-- 휴지통 비우기
-- 이걸하면 원복할 방법이 없다
purge recyclebin;

 

🗑  휴지통에 넣지 않고 데이터 완전 삭제

-- 데이터 완전 삭제
select * from emp02;

truncate table emp02;

 

 

🍀 딕셔너리 (데이터 사전)

-- 이런 모든 작업을 다 저장, 관리해주는 오라클DB
-- 딕셔너리 (데이터 사전)
-- 오라클DB가 자체적으로 관리하는 테이블. 
-- 사용자가 직접 접근하지 못한다
-- 권한 별 접근 제한
-- XXX : 대상 객체를 의미 (table, index, veiw ...)
-- DBA_XXX (DBA 계정만) (system 계정도 DBA)
-- ALL_XXX (해당 계정의 객체 + 위임받은 정보)
-- USER_XXX (해당 계정의 객체)

select * from dict
where table_name like '%USER_TABLE%';

desc user_tables;

select table_name
from user_tables;

desc all_tables;

-- 위임받은 테이블 조회
select owner, table_name
from all_tables;

 

 

🍀 DML

-- DML (Data Manipulation Language)
-- 테이블에 데이터 조작
-- insert : 데이터 삽입
-- update : 데이터 수정
-- delete : 데이터 삭제

 

insert : 데이터 삽입

-- insert

-- 1. 컬럼의 개수가 값의 개수와 일치해야 한다
-- 2. 값의 타입도 일치해야 한다. 
insert into 테이블명 (컬럼명1, 컬럼명2, 컬럼명3, ... )
values (값1, 값2, 값3, ... )

desc dept01;

insert into dept01 (deptno, dname, loc) -- 자동형변환
values (10, 'sales' , 'inchon');

-- insert into dept01 (deptno, dname, loc) 
-- values ('aaa', 'sales' , 'inchon'); -- 자동형변환 불가

insert into dept01 (deptno, dname, loc) -- 자동형변환
values (10, 'sales' , 300); -- 숫자를 문자로 묶어도 문제가 되지 않아 자동형변환이 일어남

insert into dept01 (deptno, dname) -- 묵시적 null 삽입
values (20, 'sales');

insert into dept01 (deptno, dname, loc) -- 명시적 null 삽입
values (10, 'sales' , null);

insert into dept01 -- 컬럼을 생략하면 반드시 모든 컬럼에 값을 정의해야 한다
values (30, 'sales' , 'seoul'); 

select * from dept01;

오 이거 이제 보니 어제 oe 계정 만들면서 빡세게 했던거다!

역시 프로그래밍은 해매도 길이다 !

 

update : 데이터 수정

-- update 

-- update 테이블명
set 컬럼명1 = 값1, 컬럼명2 = 값2, 컬럼명3 = 값3, ...
[where 조건식];

drop table emp01;
create table emp01
as 
select * from emp;

update emp01
set deptno = 30;

update emp01
set empno = 1111, job = 'SALESMAN';

update emp01
set sal = sal * 1.1
where sal >= 3000;

select * from emp01;

-- 83년에 입사한 사원을 오늘 날짜로 수정
update emp01
set hiredate = sysdate
where substr(hiredate,1,2) = '83';

select * from emp01;

select ename, hiredate
from emp
where substr(hiredate,1,2) = '83';

 

delete : 데이터 삭제

-- delete
delete from 테이블명
[where 조건식]

delete from dept01
where deptno = 20;

select * from dept01;

 

 

🍀 트랜잭션

DML를 이용해 작업한 후에는 트랜잭션를 이용해 테이블에 반영하는 작업을 해줘야 한다. 그렇지 않으면 반영되지 않는다.

-- DML를 이용해 작업한 후에는 트랜잭션를 이용해 테이블에 반영하는 작업을 해줘야 한다.

-- 트랜잭션
-- TCL
-- commit : 해당테이블에 작업한 내용을 반영

-- rollback : 해당테이블에 작업한 내용을 반영하지 않는다. (작업 이전 상태로 원복)
-- rollback을 하면 이전 commit 위치로 돌아간다.

drop table dept01; 

create table dept01
as
select * from dept;

delete from dept01;

commit;

delete from dept01;

rollback;

select * from dept01;

-- delete (tx 가능), truncate (tx 불가)

 

commit 을 해야 실질적으로 반영된다.

 

 

테이블 생성 시 5대 제약조건

-- 5대 제약조건 : 무결성 데이터 처리 
-- 무결성 -> 원하지 않는 데이터
-- 컬럼의 값을 제약
-- not null : null를 허용하지 않는다.
-- unique : 중복된 값을 허용하지 않는다.
-- primary key : not null + unique
-- check : 값의 범위를 제한
-- foreign key : 두개의 테이블을 사용하여 값을 참조하고 제한 하는 조건


-- 제약조건을 주는 방식 3가지
-- 컬럼레벨 방식 : 컬럼을 정의하면서 제약조건을 정의하는 방식
-- 테이블 레벨 방식 : 컬럼의 정의와 제약조건 정의를 따로 분리하는 방식
--                1. 테이블 안에서 제약 조건을 분리하는 방식
--                2. 테이블 밖으로 제약 조건을 분리하는 방식



desc user_constraints;

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME
from user_constraints;

p : pk
r : fk
u : uk
c : ck,not null  

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME
from user_constraints;

select *
from user_cons_columns;

 

not null , unique

-- not null 과 unique
drop table emp01;

create table emp01(
    empno number(4) not null,
    ename varchar2(10) not null,
    job varchar2(10)
);

insert into emp01 (empno,ename,job)
values (1111,'hong','SALES');

select * from emp01;

drop table emp02;
create table emp02(
    empno number(4) unique not null,
    ename varchar2(10) not null,
    job varchar2(10)
);

insert into emp02
values (1111,'hong','sales');

insert into emp02
values (1111,'kim','dev'); -- 같은 값 중복 

insert into emp02
values (null,'park','dev'); -- null 삽입 불가

insert into emp02
values (null,'cho','dev');

select * from emp02;

 

primary key

-- primary key

drop table emp03;

create table emp03 (
empno number(4) primary key,
ename varchar2(10) not null,
job varchar2(10)
)

insert into emp03
values (1111, 'hong', 'sales');

insert into emp03
values (null, 'hong', 'sales'); -- null을 삽입할 수 없다

select * from emp03;

 

check

-- check
drop table emp04;

create table emp04 (
empno number(4) primary key,
ename varchar2(10) not null,
sal number(7,2) check(sal between 500 and 5000),
gender varchar2(2) check(gender in ('M','F'))
);


insert into emp04
values (1111, 'hong', 1000, 'F');

insert into emp04
values (2222, 'kim', 100, 'F'); -- 체크 제약조건 위배

insert into emp04
values (3333, 'kang', 1000, 'A'); -- 체크 제약조건 위배

select * from emp04;

 

제약조건명 지정

-- 제약조건명을 직접 지정할 수도 있다.
-- 제약조건명은 유니크해야 한다.
drop table emp05;

create table emp05 (
empno number(4) constraint emp05_empno_pk primary key,
ename varchar2(10) constraint emp05_ename_nn not null,
sal number(7,2) constraint emp05_sal_ck check(sal between 2000 and 5000),
gender varchar2(2) constraint emp05_gender_ck check(gender in ('M','F'))
);

insert into emp05
values (1111, 'kang', 2000, 'F'); 

insert into emp05
values (1111, 'kim', 2000, 'F');  -- 체크 제약조건 위배

insert into emp05
values (2222, 'hong', 100, 'F'); -- 체크 제약조건 위배

insert into emp05
values (2222, 'hong', 100, 'A'); -- 체크 제약조건 위배

select * from emp05;



-- 값을 디폴트하게 제어하는 방식

drop table dept01;

create table dept01 (
deptno number(2) primary key,
dname varchar2(10) not null,
loc varchar2(10) default 'SEOUL'
);

insert into dept01 (deptno, dname)
values (10, 'DEV');

insert into dept01 (deptno, dname, loc)
values (20, 'DEV', 'BUSAN');

select * from dept01;

위에는 다 컬럼레벨 방식으로 제약조건을 줬다.

 

 

제약조건을 주는 방식 - 테이블 레벨

-- 테이블 레벨 방식의 재약 조건 설정하기
-- not null 제약조건은 컬럼 레벨 방식만 가능
create table emp06 (
empno number(4),
ename varchar2(10) constraint emp06_ename_nn not null,
job varchar2(10),
deptno number(2),

constraint emp06_empno_pk primary key (empno),
constraint emp06_job_uk unique(job)
);

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME
from user_constraints
where table_name = 'EMP06';

select *
from user_cons_columns
where table_name = 'EMP06';



-- 외부에서 제약조건을 주는 방식

-- 테이블 생성
create table emp07 (
empno number(4),
ename varchar2(10),
job varchar2(10),
deptno number(2)
);

-- 테이블 생성 후 제약조건을 분리해서 주기
-- alter table 테이블명 
-- add 제약조건명 제약조건(컬럼명)

alter table emp07
add constraint emp07_empno_pk primary key(empno);

alter table emp07
add constraint emp07_job_uk unique(job);

-- null 값을 줄 수는 없지만 null로 상태를 변경할 수 있다
alter table emp07
modify ename constraint emp07_ename_nn not null;

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME
from user_constraints
where table_name = 'EMP07';

select *
from user_cons_columns
where table_name = 'EMP07';