--DDL :객체를 수정 / 삭제 / 생성하는 명령어
--CREATE (객체 생성)/DROP (객체제거) / ALTER (객체 수정)
--DML -(INSERT) 데이터를 생성 / 수정/ 삭제 /조회하는 명령어 insert select delete
-- 한글 한글자당 3바이트
--varchar 20바이트로 되어있을때 5글자만 저정해도 15바이트 크기로 넣으면 크기가 줄어든상태로 저장된다
--char 지정된 크기만큼 저장이 안되어도 변하지 않는다
--값이 수시로 바뀌어야 한다면 char 타입이 좋다 그게 아니라면 varchar-
--테이블 생성
--제약 조건 NOT NULL : 빈값을 허용하지 않겠다 , cannot insert NULL into ("KH"."CAFE_MENU"."PPRICE") Null 값을 집어넣을수 없다 오류 발생가능성이 있다
--보편적으로 id값은 중복을 허용하지 않는게 좋다
--primary key 동일한 값 두번 사용불가 고유값 unique constraint 에러 발생, 테이블생성시 primary key 두번 사용불가
--unique 중복사용불가 , null값 사용가능, unique not null 로 사용해야 null값도 불가
--check (iced in ( 'Y', 'N', 'y', 'n)) 특정값만 받겠다
-- 컬럼갯수랑 다르게 입력 되었을떄"not enough values"
--check constraint (KH.SYS_C008171) violated 체크값에 충족 안되었을때
--foreign key(데이터 무결성을 지키기위한 제약조건)
--craete
--테이블 제약조건primary key / not null / unique / check / foreign key(데이터 무결성을 지키기위한 제약조건)
================================================================
--drop 객체 종류 객체명
--insert
--sequence / 컬럼선택입력 / 전체입력
create table cafe_menu(
--컬럼명 자료형, 제약조건(값을 저장할때 이규칙을 따라야한다)
pid number primary key,
pname varchar(20) unique not null,
pprice number NOT NULL,
iced char(1) check (iced in ( 'Y', 'N', 'y', 'n')) not null --네글자 외에는 저장 불가
);
--테이블 완전 삭제, 조심
drop table cafe_menu;
-- unique/primary keys in table referenced by foreign keys 다른테이블에서 참조되어 있으면 삭제불가
--참조된걸 지워야 지워짐 -- 실제 프로젝트 설계에서는 설정 해놓되 개발 막바지에 외래키 설정 추가
--DML -(INSERT) 데이터를 생성 / 수정/ 삭제 /조회하는 명령어 insert select delete
insert into cafe_menu values(1001, 'Americano',2000, 'Y'); -- 모든 컬럼값을 다 입력할떄 문법
insert into cafe_menu values(1002, 'CAFE LATTE',2000, 'Y');
insert into cafe_menu values(1003, 'CAFE mocha',3500, 'Y');
insert into cafe_menu values(1004, 'CAFE mocha',3500, 'Y');
insert into cafe_menu values(cafe_menu_seq.nextval, 'CAFE mocha',3500, 'Y');--시퀸스사용
insert into cafe_menu(pid, pname) values(1003, 'cafe Latte'); --일부컬럼 값을 선택적으로 입력할때 문법
select * from cafe_menu;
--SEQUENSE특정 단일 값을 규칙에 따라 증가시키며 기억하는 객체
-- pid
create sequence cafe_menu_seq
start with 1003
increment by 1
nomaxvalue
nocache;
drop sequence cafe_menu_seq; -- 시퀸스 삭제
select cafe_menu_seq.nextval from dual; -- 호출마다 값증가
select cafe_menu_seq.currval from dual; --현재값
-- 값을 내리게 하는법은 만들때 increment by -1 값을 주는 방법 외엔 없다
create table sales_record( --구매 목록
sid number primary key,--거래 ID값
pid references cafe_menu(pid) on delete set null, 카페 메뉴테이블이 pid 참조 , unique아니면 primary key만 외래키 가능
sdate timestamp default sysdate not null --기본값으로 현재시간 설정
);
create table sales_record(
sid number primary key,--거래 ID값
pid references cafe_menu(pid) on delete cascade, --연쇄 삭제-- 카페 메뉴테이블이 pid 참조 , unique아니면 primary key만 외래키 가능
sdate timestamp default sysdate not null --기본값으로 현재시간 설정
);
--cafe menu 부모테이블 sales record 자식테이블
-- 부모 레코드가 지워지는 순간 참조되어 있는 테이블 내용도 완전히 지워짐 on delete cascade, --연쇄 삭제
-- 부모 레코드가 지워져도 참조되어 있는 테이블 내용이 null값으로 on delete set null,
--시퀸스 생성 1부터 무한대까지 1씩 증가하는 sales_record_seq 생성
create sequence sales_record_seq
start with 1
increment by 1
nomaxvalue
nocache;
insert into sales_record values(sales_record_seq.nextval, 1004, default);
--integrity constraint (KH.SYS_C008176) violated - parent key not found 무결성 에러 부모테이블 cafe_menu에 해당값이 없다 1004
--시퀸스는 에러가 나도 증가
--sales_record에 값추가
insert into sales_record values(sales_record_seq.nextval, 1003, default);
select * from sales_record;
insert into sales_record values(sales_record_seq.nextval, 1002, default);
insert into sales_record values(sales_record_seq.nextval, 1001, default);
delete from sales_record where sid=2; -- 거래번호sid 2값인애를 지우겠다
select from cafe_menu;
select from sales_record;
delete from cafe_menu where pid=1002; -- pid 2값인애를 지우겠다
drop table sales_record; --테이블 완전삭제
drop table cafe_menu;
-------update 가격이 바뀐다면?
update cafe_menu set pprice = 3000 where pid = 1002; -- pid가 1002 번 제품 pprice를 변경 하겠다
update cafe_menu set pprice = 2500, pname = 'orange juice' where pid = 1002; -- pid가 1002 번 제품 pprice를 변경 하겠다
update cafe_menu set orgin = 'Congo' where pid = 1001;
--INSERT : C
--SELECT : R = JOIN
--UPDATE : U
--DELETE : D
--DB최소한의 기능들 공부
--------alter : 수정하다 / 변경하다 -객체를 수정하는 명령
--컬럼 추가
alter table cafe_menu add (orgin varchar(30) default 'none' not null) ; --새로운 컬럼추가
select * from cafe_menu;
desc cafe_menu;
drop
desc sales_record;
alter table cafe_menu modify(origin varchar(50)); --기존 컬럼타입크기변경
--컬럼 추가 후 값넣기
update cafe_menu set orgin = 'Congo' where pid = 1001;
--기존 컬럼명변경
ALTER TABLE cafe_menu RENAME COLUMN origin TO originate;
alter table cafe_menu drop column originate; --기존 컬럼 삭제
--Dictionary :오라클 내에서 내장된 시스템 테이블
select table_name from user_tables; --로그인한 계정이 갖고있는테이블 나옴 -
select from user_sequences; --생성된 시퀸스 목록 나옴
select from user_constraints;
create table cafe_menu(
pid number constraint cafe_menu_pk primary key,--거래 ID값
pname varchar(20) constraint cafe_menu_pname_uk unique constraint cafe_menu_pname_nn not null,
pprice number constraint cafe_menu_pprice_nn not null,
iced char(1) constraint cafe_menu_iced_ck check (iced in('Y', 'N', 'y', 'n')) constraint cafe_menu_iced_nn not null
); -- 뭐지..
alter table cafe_menu drop constraint cafe_menu_pk; -- 컬럼에 적용된 제약조건을 제거하는 명령
alter table cafe_menu drop constraint cafe_menu_uk
alter table cafe_menu add constraint cafe_menu_pk primary key(pid); --컬럼에 적용된 제약조건 추가하는 명령
desc cafe_menu;
------------TCL
---DBMS와 DB사이 임시 저장공간에 DML쿼리를 보관하는 작업의 단위
--COMMIT // ROLLBACK //SAVEPOINT
select from cafe_menu;
select from sal_grade;
--DDL 명령어는 자동 COMMIT DROP 주의
-- USER ->DEVELOPER -> DBMS -> TRANSACTION(작업단위)이 쌓여있음(COMMIT 하면 적용, ROLLBACK하면 가장 마지막 커밋 위치로 - > DB
--TRANSACTION 쿼리가 쌓였을떄 COMMIT 되게끔하는것도 있다
commit;
savepoint abc;
--무언갈 삭제하고
rollback to abc; -- 롤백하면 세이브 포인트로
--원자성 쪼개질수 없는 단위 EX)- Transaction 작업단위가 3개의 INSERT쿼리가 한번에 실행해야 하는게 있다면 하나라도 실패시 모두 실패
--A계정이 특정 테이블에서 작업을 하고 있을때 B 계정에서 작업중인 테이블에 접근하면 블락
----------------------------------------------------------------트랜잭션
--VIEW , 가상의 임시 테이블
--특정 테이블(OR 테이블들)에서 추려낸 정보로 만들어지는 링크형 임시 테이블
-- 테이블의 민감한 정보를 비공개 하고 일부만 보여주기위한
----KH
create table employee_dev
as select emp_id, emp_name, email, phone from employee; --employee에서 emp_id, emp_name, email, phone만 공개하는테이블
--employee, employee_dev 물리적으로 나누어진 테이블
select *from employee_Dev;
--다른 계정(Dev)에도 접근 할수 있게 권한을 주겠다
grant select on employee_Dev to dev; -- Dev에 계정에 접속권한 부여 명령
drop table employee_Dev;
------ employee, employee_Dev 물리적으로 나누어진 테이블이라 실시간으로 변경사항이 반영이 안되서
---employee_view 테이블은 하나인데 다른 계정에서 해당 테이블을 볼수있는 링크
create view employee_view
as
select emp_id, emp_name, phone, email from employee;
select * from employee_view;
delete from employee_view where emp_id=218; --id가218인 데이터지우고 커밋해야 적용 완료 dev에서도 삭제 확인가능
commit;
grant select on employee_view to dev;
---DEV 계정
select from employee;
select from kh.employee_dev; --employee_dev 테이블로 접근 성공
select * from kh.employee_view;
--employee_dev에는 신규직원, 퇴사직원 자동 업데이트가 안된다, 원본테이블과는 물리적으로 나누어져 있기 때문에 변화 상황 실시간 반영x
-------관리자 DB 명령
grant create view to kh; --kh에게 create view 명령어 권한 부여
