국비지원 JAVA(자바) 프로그래밍 (데이터베이스, SQL문,제약조건,공공데이터포털,트랜잭션)

1. 데이터 베이스 제약조건

• not null
• unique

1-1. 키본키 PRIMARY KEY

• 각레코드들을 구별해주는 필드
• 중복내용 입력 불가능
• NULL 불가능

CREATE TABLE PK_TEST(
    ID VARCHAR(20) PRIMARY KEY,
    PW VARCHAR(20)
);

DESC PK_TEST;

-- 레코드 추가
INSERT INTO PK_TEST
    VALUES('A', '1234');
    
-- 레코드 추가 (ID가 기본키이므로 중복내용 입력 안됨 - 오류)
INSERT INTO PK_TEST
    VALUES('A', 'ZXCV');
    
SELECT * FROM PK_TEST;

--레코드 추가 (ID가 기본키이므로 NULL 안됨 - 오류)
INSERT INTO PF_TEST
    VALUES(NULL, '1234');
    
    

CREATE TABLE PR_TEST(
    ID VARCHAR(20) CONSTRAINT ID_PK PRIMARY KEY,
    PW VARCHAR(20)
);

ALTER TABLE PR_TEST
    DROP CONSTRAINT ID_PK;
    
ALTER TABLE PR_TEST
    MODIFY ID CONSTRAINT PK_ID PRIMARY KEY;
    
DROP TABLE PR_TEST;

---

 

1-2. 외래키 FOREIGN KEY

다른 테이블간 관계를 정의할 때 사용

기본키를 참조

참조하는 기본키의 데이터타입과 일

 

FK_TEST 테이블에 있는 ID를 참조하는 외래키

NAME

 

CREATE TABLE FK_TEST(
    ID VARCHAR(20) CONSTRAINT FK_ID REFERENCES PK_TEST(ID),
    NAME VARCHAR(20)
);

--참조하는 PK에 B라는 데이터가 없으므로
--INSERT 안됨
INSERT INTO FK_TEST
    VALUES('B', '1234');

--참조하는 PK컬럼(ID)에 A가 있으므로 잘 추가됨
INSERT INTO FK_TEST
    VALUES('A', '홍길동');
    
SELECT * FROM FK_TEST;

---

1-3. CHECK

직접 조건을 지정해서 조건에 만족하는 데이터만 입력

CREATE TABLE CHECK_TEST(
    NAME VARCHAR(20),
    AGE NUMBER CHECK(AGE>=1 AND AGE<=100),
    GENDER VARCHAR(20) CONSTRAINT G_CHECK CHECK (GENDER IN('M', 'F'))
);

--AGE 제약조건에 위배도므로 105는 입력이 안됨 -오류
INSERT INTO CHECK_TEST
    VALUES('AA', 105, 'M');

--제약조건에 알맞게 잘 입력했으니 정상작동
INSERT INTO CHECK_TEST
    VALUES('AAA', 50, 'M');

--대소문자 구분함 -오류(오류를 없애려면 제약조건에 소문자도 같이 넣어주면 된다.)
INSERT INTO CHECK_TEST
    VALUES('AAA', 20, 'f');

SELECT * FROM CHECK_TEST;

INSERT INTO CHECK_TEST
    VALUES('B', 20, 'F');
    
--NAME은 3글자 이상만 입력되도록 제약조건을 추가
--CHECK에 맞지 않은 데이터가 존재하므로 지우고 제약조건을 걸어야됨
ALTER TABLE CHECK_TEST
    MODIFY NAME CHECK(LENGTH(NAME)>=3);
    
--제약조건을 추가하기 위해 위배되는 데이터 삭제
DELETE FROM CHECK_TEST WHERE LENGTH(NAME)<3;

SELECT * FROM CHECK_TEST;

--위배되는 데이터를 지웠으니 정상실행
ALTER TABLE CHECK_TEST
    MODIFY NAME CHECK(LENGTH(NAME)>=3);
   
--NAME이 2글자라서 추가 안됨 - 오류 
INSERT INTO CHECK_TEST
    VALUES('이름', 30, 'F');

--NAME이 3글자이상이라서 추가 잘 됨
INSERT INTO CHECK_TEST
    VALUES('김유신', 30, 'F');

VARCHAR(20) -> BYTE  최대 20BYTE까지 입력 가능 한글 -> 3BYTE 나머지는 -> 1BYTE LENGTH로 처리 해야된다.

---

1-4. DEFAULT : 기본값

데이터를 입력하지 않을 경우 자동으로 입력되는 값을 기본값이라고 함

 

CREATE TABLE DE_TEST(
    이름 VARCHAR(50),
    지역 VARCHAR(50) DEFAULT '서울'
    --지역 VARCHAR(50) CONSTRAINT 제약조건이름 DEFAULT '서울'
);


SELECT * FROM DE_TEST;

--지역을 작성해서 추가하면 입력한 지역으로 추가
INSERT INTO DE_TEST
    VALUES('고길동', '부산');

-- 지역을 NULL 추가해도 기본값이 아닌 NULL 추가
INSERT INTO DE_TEST
    VALUES('둘리', 'NULL'); 

-- 지역부분을 추가하지 않고 이름만 추가하면 지역은 기본값으로 처리
INSERT INTO DE_TEST(이름)
    VALUES('또치');

SELECT * FROM DE_TEST;

INSERT INTO DE_TEST(지역)
    VALUES('인천');

INSERT INTO DE_TEST(지역)
    VALUES('대구');

SELECT * FROM DE_TEST;


ALTER TABLE DE_TEST
    ADD CONSTRAINT DE_이름 CHECK(LENGTH(이름)>=2);
    
ALTER TABLE DE_TEST
    MODIFY 이름 DEFAULT '이름';
    
SELECT * FROM DE_TEST;

INSERT INTO DE_TEST(지역)
    VALUES('울산');
    
SELECT * FROM DE_TEST;

---

제약조건 정리

NOT NULL: NULL 안됨

UNIQUE : 중복 불가능

PRIMARY KEY: 기본키

FOREIGN KEY REFERENCES: 외래키

CHECK: 조건에 만족하는 데이터만 입력

DEFAULT: 기본값 

 

CONSTRAINT 제약조건명 제약조건

 

 ALTER TABLE 테이블명

 DROP CONSTRAINT 제약조건명

 

ALTER TALBEL 테이블명

MODIFY  컬럼명 제약조건

 

DEFAULT 제외

나머지 제약조건은

이미입력된 데이터가 제약조건에 위배되면 제약조건에 추가 안됨!! 

---

PRODUCT 테이블을 생성하시오

컬럼명                데이터타입          제약조건 

ID                         숫자                기본키

NAME               문자20BYTE       NULL값안됨

PRO_DATE         날짜

 

CREATE TABLE PRODUCT(
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR(20) NOT NULL,
    PRO_DATE DATE 
);

INSERT INTO PRODUCT VALUES(1, 'COMPUTER', '2023-01-10');
INSERT INTO PRODUCT VALUES(2, 'SMARTPHONE', '2020-12-24');
INSERT INTO PRODUCT VALUES(3, 'TV', SYSDATE);

SELECT * FROM PRODUCT;

---

---

데이터베이스 설계순서 

요구조건분석-> 개념적 설계->논리적설계-> 물리적 설계

 

만화책방에 대한 데이터베이스 만든다고 생각했을 때 생각해보기

 

책에 대한 정보들을 보관하기 -도서테이블

고객정보-고객테이블

대여/반납정보-대여내역 테이블

 

<도서> 테이블에 어떤  컬럼이 있으면 좋을지?

->도서코드,도서명,대여가격 ,장르,출판사 

데이터타입은 어떻게 할지?

->문자(20) ,문자(50), 숫자 ,문자(50), 문자(50)

제약조건은 어떻게할지?

->도서코드를 기본키로 설정, 도서명은 NOT NULL,대여가격은 0이상만입력되게 기본값은 1000으로

 

<고객> 테이블에 어떤  컬럼이 있으면 좋을지?

->고객코드,고객명,전화번호,주소,나이,등급

데이터타입은 어떻게할지?

->문자(20) ,문자(50), 문자(50), 문자(50),숫자,문자(50)

제약조건 어떻게할지?

고객코드-기본키, 고객명 not null, 나이>=10 AND <=90 으로 설정 ,등급 (일반 우수 vip만 존재) IN(일반,우수,VIP) 

 

<대여내역> 테이블  어떤컬럼이 있으면 좋을지?

->고객코드 대여날짜 반납일자 연체료 반납여부

데이터타입은 어떻게할지?

->문자(20),문자(20),날짜,날짜,숫자,문자(20)

제약조건

->고객코드는 고객테이블에 고객코드를 참조하는 외래키, 도서코드는 도서테이블에 도서코드를 참조하는외래키, 

대여일자는 기본값:오늘날짜 로 설정, 반납여부는 Y 아니면 N만 입력되게 설정.

기본틀 

create table 도서(
    도서코드 VARCHAR(20) PRIMARY KEY,
    도서명 VARCHAR(50) NOT NULL,
    대여가격 NUMBER DEFAULT 1000 CHECK(대여가격>=0),
    장르 VARCHAR(50),
    출판사 VARCHAR(50)
);

SELECT * FROM 도서;

INSERT INTO 도서
    VALUES('A001', '해리포터', 500, '소설', '영진');

--기본키와, NOT NULL에 위배되므로 안됨
INSERT INTO 도서(도서코드)
    VALUES('AOO1');

--정상작동
INSERT INTO 도서(도서코드, 도서명)
    VALUES('AOO1', '오라클');

SELECT * FROM 도서;



CREATE TABLE 고객 (
    고객코드 VARCHAR(20) PRIMARY KEY,
    고객명 VARCHAR(50) NOT NULL,
    전화번호 VARCHAR(50),
    주소 VARCHAR(50),
    나이 NUMBER CHECK(나이 BETWEEN 10 AND 90),
    등급 VARCHAR(50) CHECK(등급 IN('일반', '우수', 'VIP'))
);


DESC 고객;

INSERT INTO 고객
    VALUES('P001', '김유신', '010-1234-4456', '서울', 20, '일반');
    
SELECT * FROM 고객;

--나이에 설정된 제약조건에 위배되므로 오류
INSERT INTO 고객(고객코드, 고객명, 나이)
    VALUES('P002', '김유신', 5);
    
--등급에 설정된 제약조건에 위배되므로 오류
INSERT INTO 고객(고객코드, 고객명, 등급)
    VALUES('P002', '티모', 'VVIP');
    
UPDATE 고객 SET 나이= 나이+1;

SELECT*FROM 고객;
    
    CREATE TABLE 대여내역(
    고객코드 VARCHAR(20) CONSTRAINT 고객코드_FK REFERENCES 고객(고객코드),
    도서코드 VARCHAR(20) CONSTRAINT 도서코드_FK REFERENCES 도서(도서코드),
    대여일자 DATE DEFAULT SYSDATE,
    반납일자 DATE,
    연체료 NUMBER,
    반납여부 VARCHAR(20) CHECK(반납여부 IN ('Y','N'))
    );

SELECT*FROM 대여내역;
    
--고객테이블에 A001이 없고
--도서테이블에 P001이 없기때문에 실행 안됨
INSERT INTO 대여내역
VALUES('A001','P001','2023-01-01','2023-01-02',0,'Y');


--고객테이블에 P001이 있고
--도서테이블에 A001이 있기때문에 실행됨
INSERT INTO 대여내역
VALUES('P001','A001','2023-01-01','2023-01-02',0,'Y');

SELECT*FROM 고객;
SELECT*FROM 도서;
   
  --참조하는(자식) 테이블에 P001이 있어서 삭제할 수 없음
  DELETE FROM 고객 WHERE 고객테이블='P001';  
 
 SELECT*FROM 대여내역;
  
   --참조하는(자식) 테이블에 A001이 있어서 삭제할 수 없음
   DELETE FROM 도서 WHERE 도서테이블='A001';

 

고객테이블에 모든 회원들은 나이가 1살증가하게끔

UPDATE 고객 SET 나이= 나이+1;

 

제약조건 쓸 때

테이블명_컬럼명_제약조건 이런식으로 작성

 

---

    CREATE TABLE MEMBER_TBL(
    CUSTNO NUMBER(6) PRIMARY KEY,
    CUSNAME NVARCHAR2(20) NOT NULL,
    PHONE  NUMBER(10),
    ADRESS NVARCHAR2(50),
    EMAIL VARCHAR2(50),
    GRADE CHAR(1)
    );
    
    SELECT * FROM MEMBER_TBL;

ALTER TABLE MEMBER_TBL
ADD AGE NUMBER(3);

DESC MEMBER_TBL;

ALTER TABLE MEMBER_TBL
MODIFY PHONE VARCHAR2(15);

DESC MEMBER_TBL;

ALTER TABLE MEMBER_TBL 
DROP COLUMN EMAIL;

INSERT INTO MEMBER_TBL
VALUES(1,'이순신','010-1111-1111','대전','A',30);

INSERT INTO MEMBER_TBL
VALUES(2,'강감찬','010-1111-2222','서울','B',20);

INSERT INTO MEMBER_TBL
VALUES(3,'성춘향','010-1111-3333','경기','C',10);

SELECT*FROM MEMBER_TBL;

INSERT INTO MEMBER_TBL
VALUES(4,'유재원','010-1111-4444','강원','A',15);

UPDATE MEMBER_TBL
SET GRADE='B'
WHERE CUSTNO=1;

DELETE FROM MEMBER_TBL
WHERE CUSTNO=3;

SELECT DISTINCT GRADE FROM MEMBER_TBL
ORDER BY GRADE ASC;

SELECT CUSTNAME, PHONE, ADDRESS
FROM MEMBER_TBL
WHERE AGE BETWEEN 20 AND 30;

SELECT AVG(AGE) FROM MEMBER_TBL;

---

트랜잭션

-> 하나라도 실패하면 둘다 실행X

-> 둘 다 성공하면 저장

 

은행DB 계좌이체 

A라는 사람이 B한테 5000원을 송금

A(계좌잔액)                 B(계좌잔액)

20000                           5000

 

UPDATE: A사람의 계좌 잔액 -5000

UPDATE: B사람의 계좌 잔액+5000

 

트랜잭션 특성 4가지

1.원자성

트랜잭션 안에 있는 모든 코드들은 전부 실행되거나, 실행되지않거나 둘중 하나여야 한다.

 

2. 일관성

트랜잭션을 조작하는 테이블의 조건들을 만족해야한다. 

 

3. 고립성

트랜잭션 끼리 서로 간섭하지 말아야 한다.

 

4. 영속성

트랜잭션이 COMMIT되면 영구적으로 저장되어야함

 

---

CREATE TABLE TRAN_TEST
AS SELECT * FROM DEPT;
--CREATE 트랜잭션 종료

--새 트랜잭션 시작
INSERT INTO TRAN_TEST
    VALUES(50,'ORACLE','SEOUL');
DELETE FROM TRAN_TEST
    WHERE DEPTNO=20;

ROLLBACK;
--트랜잭션 취소 

--새 트랜잭션시작

UPDATE TRAN_TEST
SET LOC= '서울'
WHERE DEPTNO=40;

COMMIT;
--트랜잭션 반영되고 종료

ROLLBACKL; --마지막 커밋으로 감

---

---

공공데이터포털 (data.go.kr)

공공데이터 포털

---

---

인덱스(index)색인- 검색속도 향상 

 

SELECT문 주로 사용할경우->INDEX 유리

UPDATE,DELETE,INSERT 주로사용-> INDEX 안쓰는게 나음(성능저하 우려)

 

기본키->INDEX

기본키 필드는 INDEX 만들필요가없음

인덱스가 필요한 경우

-> 데이터가많을 떄, 검색을 주로 할 경우 특정컬럼의 검색을 주로할경우, 검색의 결과가 전체데이터에 비해 적을 경우 

INDEX기본형식

CREATE INDEX 인덱스 이름 ON 테이블(컬럼명)

---

시퀀스(일련번호)  

시퀀스 기본형식

CREATE SEQUENCE 시퀀스이름

                INCREAMENT BY 숫자 -- 증가값

                START WITH 숫자-- 시작값 

                MAXVALUE 숫자 -- 최대값

                MINVALUE 숫자 --최소값 

                CYCLE  ㅣ NOCYCLE

 

CYCLE:최대값이 된 후 그 다음값이 다시START부터 시작  

NOCYCLE: 최대값이 된 후 안들어가짐

 

CACHE(캐시) : 번호생성시 메모리에 미리 할당

NOCACHE(노캐스) : 미리할당안함

 

시퀀스는 보통 게시판에서 사용