CRUD란?,CONSTRAINTS,UPDATE, SEQUENCE

▶CRUD

대부분의 컴퓨터 소프트웨어가 가지는 기본적인 데이터 처리 기능인 
Create(생성), Read(읽기), Update(갱신), Delete(삭제)를 묶어서 일컫는 말. 
사용자 인터페이스가 갖추어야 할 기능(정보의 참조/검색/갱신)을 지칭하는 용어로도 사용.
데이터베이스 SQL문과 대응.

▷이름 조작 SQL
Create 생성 INSERT
Read(또는 Retrieve) 읽기 SELECT
Update 갱신 UPDATE
Delete(또는 Destroy) 삭제 DELETE

▶USER_CONSTRAINTS : 시스템 뷰 테이블(가상테이블)***********
--오류발생 시 참조관계검색
--INSERT, DELETE 시 유용

 

SELET * FROM USER_CONSTRAINTS
WHERE CONSTRAINTS_NAME ='SG_SCORES_FK2';--오류코드

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'STUDENT';

SYS_C007257 C "NAME" IS NOT NULL
SYS_C007258 C "ID_NUMBER" IS NOT NULL
STUDENT_PK P
STUDENT_UK U
STUDENT_FK R

 

▷ CONSTRAINT_TYPE

P : PRIMARY KEY 

R  : FOREIGN KEY 

U : UNIQUE 

C : CHECK, NOT NULL 

->무결성 제약조건은  5개이지만 제약 조건 유형이 4가지인 이유?

 NOT NULL과 CHECK 제약 조건을 모두 C로 표현하기 때문.
 NOT NULL 제약 조건은 칼럼에 NOT NULL 조건을 체크할지 말지를 결정하기 때문에 CHECK를 나타내는 C로 표현.
 FOREIGN KEY는 참조의 무결성을 지켜야 하기에 참조(REFERENCE) 무결성(INTEGRITY)의 이니셜인

R을 제약 조건 유형으로 지정

--SCORE_ASSIGEND
날짜형 컬럼에 날짜형 문자 데이터로 입력함
데이터타입이 동일하지 않아도 날짜형문자->DATE 예외사항 적용되어
날짜형으로 자동형변환이 되어 저장

--sqL오류 :ORA-00947 : not enough values
--값의 수가 불충분
--ex) 테이블의 컬럼수가 5개, 입력데이터가 4개일때 발생
->개수를 맟춰주면 해결

--sqL오류 
ORA-00001 : unique constraint (TEST.COURSE_PK) violated
-->무결성(개체무결성-기본키 :중복불가,N.N) 제약조건에 위배됩니다
UNIQUE(중복불가, NULL허용) : 유일한 <-> PRIMARY(중복불가, NULL불허)

values too large for colum "계정" ."테이블" ."컬럼명"
-입력데이터가 최대허용데이터보다 크다

===================================================
insert delete update는 시행 후 commit필수!

---

실습

--UPDATE 마스터하기
--'이' 와 '김'씨 성을 가진 회원의 
--휴대폰 컬럼에 011-111-1111로 갱신

--업데이트전에 정보확인!!!
SELECT MEM_ID 회원ID
     , MEM_NAME 성명
     , MEM_HP 휴대폰
FROM MEMBER
WHERE MEM_NAME LIKE '이%'
OR MEM_NAME LIKE '김%';

UPDATE MEMBER
SET MEM_HP = '011-111-1111'
WHERE  MEM_NAME LIKE '이%'
OR MEM_NAME LIKE '김%';

SELECT MEM_NAME, MEM_HP
FROM MEMBER;

-- 회원ID가 a001인 회원의 데이터를 
--(취미 : 독서, 직업 :  군인)으로 수정

SELECT MEM_ID
    ,  MEM_LIKE
    ,  MEM_JOB
FROM MEMBER
WHERE MEM_ID = 'a001';

UPDATE MEMBER
SET MEM_LIKE = '독서'
  , MEM_JOB ='군인'
WHERE MEM_ID = 'a001';

SELECT MEM_ID, MEM_LIKE, MEM_JOB
FROM MEMBER
WHERE MEM_ID = 'a001';


------------------시퀀스

--시퀀스 : 연속, 열거, 순서의 의미
--시퀀스 객체는 자동적으로 번호 생성하기 위한 객체
--이용하는 경우 : 
--P.K를 설정할 후보키가 없거나 PK를 의미있게 만들지않아도되는경우
--자동적인 순서가 필요 시

--시퀀스 생성
--INCREMENT BY : 1씩 증가
--START WITH : 1부터 시작
CREATE SEQUENCE LPROD_SEQ --OR REPLACE 없음
INCREMENT BY 1
START WITH  1;

--시퀀스제거
--DROP SEQUENCE LPROD_SEQ;

--시퀀스 1증가
--NEXTVAL : 다음 값
SELECT LPROD_SEQ.NEXTVAL FROM DUAL;


--현재번호 확인
--CURRVAL : CURRENT 현재 VALUE값
--시퀀스를 생성한 후에 바로 CURRVAL 할 수 없음
SELECT LPROD_SEQ.NEXTVAL FROM DUAL;--선행되어야 함
SELECT LPROD_SEQ.CURRVAL FROM DUAL;

--사용방법
CREATE TABLE TESTSEQ(
    TS_ID NUMBER NOT NULL,
    TS_NAME VARCHAR2(10),
    CONSTRAINT PK_TESTSEQ PRIMARY KEY(TS_ID)
);

CREATE SEQUENCE TESTSEQ_SEQ 
INCREMENT BY 1
START WITH  1;


--TESTSEQ_SEQ : 1
INSERT INTO TESTSEQ(TS_ID, TS_NAME)
VALUES(TESTSEQ_SEQ.NEXTVAL,'글1');

--TESTSEQ_SEQ : 2
INSERT INTO TESTSEQ(TS_ID, TS_NAME)
VALUES(TESTSEQ_SEQ.NEXTVAL,'글2');

--TESTSEQ_SEQ : 3
INSERT INTO TESTSEQ(TS_ID, TS_NAME)
VALUES(TESTSEQ_SEQ.NEXTVAL,'글3');

commit;

SELECT * FROM TESTSEQ;

--시퀀스 연습문제
  --AOA 테이블 생성
  --( NO NUMBER NOT NULL
  --, NAME VARCHAR2(30)
  --, BIR  VARCHAR2(20))
  --NO컬럼을 PRIMARY KEY로 CONSTRAINT하기

CREATE TABLE AOA(
   NO NUMBER NOT NULL,
   NAME VARCHAR2(30),
   BIR  VARCHAR2(20),
   CONSTRAINT PK_AOA PRIMARY KEY(NO)
);

CREATE SEQUENCE AOA_SEQ
INCREMENT BY 1
START WITH 1;

  --시퀀스를 사용하여 다음의 데이터를 입력
  --1, 지민, 1991-01-08 2, 초아   3, 설현   4, 유나  5, 유경
  --6,  혜정   7,  민아  8,  찬미
SELECT * FROM AOA;

INSERT INTO AOA(NO,NAME,BIR) VALUES(AOA_SEQ.NEXTVAL,'지민','1991-01-08');
INSERT INTO AOA(NO,NAME,BIR) VALUES(AOA_SEQ.NEXTVAL,'초아',NULL);
INSERT INTO AOA(NO,NAME,BIR) VALUES(AOA_SEQ.NEXTVAL,'설현',NULL);
INSERT INTO AOA(NO,NAME,BIR) VALUES(AOA_SEQ.NEXTVAL,'유나',NULL);
INSERT INTO AOA(NO,NAME,BIR) VALUES(AOA_SEQ.NEXTVAL,'유경',NULL);
INSERT INTO AOA(NO,NAME,BIR) VALUES(AOA_SEQ.NEXTVAL,'혜정',NULL);
INSERT INTO AOA(NO,NAME,BIR) VALUES(AOA_SEQ.NEXTVAL,'민아',NULL);
INSERT INTO AOA(NO,NAME,BIR) VALUES(AOA_SEQ.NEXTVAL,'찬미',NULL);

COMMIT;

--1부터 시작하여 2씩 증가하는 시퀀스 생성
CREATE SEQUENCE LPROD_SEQ2
INCREMENT BY 2
START WITH 1;

SELECT LPROD_SEQ2.NEXTVAL FROM DUAL; --2씩증가
SELECT LPROD_SEQ2.CURRVAL FROM DUAL;

--VALUE를 1로 초기화 하는 방법은
--SEQUNCE를 DROP한 후에 다시 CREATE하면 됨.
--증가량은 ALTER가 가능
ALTER SEQUENCE LPROD_SEQ2
INCREMENT BY 1;

SELECT LPROD_SEQ2.NEXTVAL FROM DUAL; --1씩증가

--다음요건을 만족하는 시퀀스 생성
--객체명 : CART_SEQ
--증감값 : 1, 최소값 : 1, 최대값 21, 순환가능

CREATE SEQUENCE CART_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 21 -- 1 CYCLE > CHCHE(20):정해져있는값
CYCLE;

SELECT CART_SEQ.NEXTVAL FROM DUAL;

DROP SEQUENCE CART_SEQ;


-------------------------------
--Synonym은 동의어, 별칭의 의미
--Synonym은 객체에 대한 다른 이름으로 대체
--Synonym을 이용하는 경우
--다른 소유자의 객체를 접근하는 경우 "스키마명.객체명"으로
--접급하는데 이를 편하게한다
--긴 이름의 객체명을 쉬운 이름으로 대체하고자 할 때 사용

--insufficient(불충분) privileges(권한) -> sys계정으로 ddit에게 synonym권한부여

--SYS.DUAL : SYS계정의 DUAL 가상테이블
CREATE SYNONYM MYDUAL FOR SYS.DUAL;

SELECT SYSDATE FROM MYDUAL;

SELECT SYSDATE
    ,  3+5
    ,  SUBSTR('개똥이',1,2)
FROM MYDUAL;