DAY_035[국비] 자바 웹프로그래밍 과정

Bona의 블로그 입니다.·2022년 11월 16일

SQL DDL SQL DML oracleDB

국비과정

목록 보기

36/99

⛅ Day 035

DDL, 오라클 자료형, DDL, DML

🌲 DDL(Data Definition Language)데이터 정의어

🌱 테이블의 생성(Create)

Create Table 명령의 세부 규칙
- 테이블의 이름은 객체를 의미할 수 있는 적절한 이름을 사용함
  (자바 변수 이름 규칙과 거의 동일)
- 다른 테이블과 중복되지 않게 테이블 이름을 지정함
- 한 테이블 내에서 필드 이름도 중복되지 않게 함
- 각 필드들은 ","로 구분하여 생성함 (마지막 필드는 ','안 찍음 에러 발생)
- create를 비롯한 모든 sql명령은 ";"로 끝남
  ( 단독 실행에서는 생략이 가능하다 )
- 필드명 뒤에 DATATYPE은 반드시 지정하고 [ ]안에 내용은 해당 내용이 있을 때 작성하며 생략 가능
- 테이블 명과 필드명은 반드시 문자로 시작해야 하고 예약어 명령어 등을 테이블명과 필드명으로 쓸 수 없음
- 테이블 생성 시 대/소문자 구분은 하지 않는다.
  (기본적으로 테이블이나 컬럼명은 대문자로 만들어짐)
- DATE데이터형식은 별도로 크기를 지정하지 않음(날짜)
- 문자데이터의 DataType -> varchar2(10),
  숫자데이터의 DataType -> number(4)
- varchar2, variable charactor2(베리어블캐릭터, var캐릭터)
  숫자는 자리수를 지정
- 문자 데이터 유형은 반드시 가질 수 있는 "최대" 길이를 표시해야 함
- 컬럼과 컬럼의 구분은 콤마로 하되, 마지막 컬럼은 콤마를 찍지 않음

🍂 도서 대여점의 도서 목록 테이블의 생성

테이블명 : BookList
필드 : bookname, subject, makeyear, inprice, outprice
자료형
: booknum(문자 5 자리), subject(문자 30), makeyear(숫자 4),
inprice(숫자 6), outprice(숫자 4)
제약 조건 : booknum(Not null), subject(Not null)
기본키 : booknum

🍂 제약 조건 (CONSTRAINT)

PRIMARY KEY
- 테이블에 저장된 레코드를 고유하게 식별하기 위한 키, 하나의 테이블에 하나의 기본키만 정의 할 수 있다.
- 여러 필드가 조합된 기본키 생성 가능
- 기본키는 중복된 값을 갖을 수 없으며 빈칸도 있을 수 없다.
- PRIMARY KEY = UNIQUE KEY + NOT NULL
UNIQUE KEY
- 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 정의
- 단 NULL은 고유키 제약의 대상이 아니므로, NULL값을 가진 행
  여러 개가 UNIQUE KEY 제약에 위반하지는 않는다.
NOT NULL
- 비어있는 상태, 아무 것도 없는 상태를 허용하지 않는다
  (입력 필수)
CHECK
- 입력할 수 있는 값의 범위를 제한,
  CHECK 제약으로는 TRUE or FALSE로 평가할 수 있는 논리식을 지정
FOREIGN KEY
- 관계형 데이터 베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 외래키가 생성
  ⇒ 참조 무결성 제약 옵션이 생성

🍂 테이블 생성 2

테이블이름 : MemberList(회원리스트)
필드 : memberNum, memberName, Phone, Birth, Bpoint
데이터 형식
: memberNum : VARCHAR2(5), memberName : VARCHAR(12), Phone : VARCHAR2(13),
Birth : DATE, Bpoint : NUMBER(6)
- 0으로 시작하는 경우(전화번호, 계좌번호) 데이터 타입이 숫자이면 인식이 잘 안되서 오류 발생 가능성 높음
제약 조건
: memberNum, memberName, Phone 세 개의 필드
➡ not null - 필드 레벨로 설정
: memberNum
➡ primary key - 테이블 레벨로 설정

🍂 테이블 생성 3

테이블 이름 : rentlist
필드
: rent_date(date), indexk(NUMBER(3)), booknum(VARCHAR2(5)),
membernum(VARCHAR2(5)), discount(NUMBER(4))
제약 조건 : booknum, membernum - not null
기본값 : rent_date - 오늘 날짜

rent_date와 indexk를 조합하여 기본키(re_pk)를 생성, 두 개의 필드가 조합되어 기본키로 지정될 수 있다. 이를 슈퍼키라고 한다.
rentlist 테이블의 booknum은 booklist 테이블의 booknum을 참조하는 외래키로 지정(제약 조건 이름은 fk1)
rentlist 테이블의 membernum은 memberlist 테이블의 membernum을 참조하는 외래키로 지정(제약 조건 이름은 fk2)

🌱 오라클 자료형

1. 정수형 타입

number(2)는 총 두자리 정수형 값이 필드에 설정된다(-99 ~ 99)
괄호 안의 숫자가 자리수를 표시하며, 이는 바이트수가 아닌 것을 꼭 기억해야 한다.

2. 실수형 타입

number(6, 2)는 소수점을 포함한 총 자리수가 6자리 이고 소수점 둘째 자리까지 있는 실수형 값이 설정된다.
첫 번째 인자값 6은 소수점 자리수를 포함한 총 자리수를 의미하고, 두 번째 인자값 2는 소수점 뒤에 자리수를 의미
소수점도 자리수를 차지한다. - 자바의 %6.2f와 같은 형식
오라클에서는 지정된 용량을 초과하는 데이터를 입력하려고 하면 자료 추가 오류가 발생한다.

3. 가변형 문자열(최대 4000 BYTE)

varchar2(XX)
입력 데이터가 실제 크기를 넘어서면 넘어선 크기만큼 자료형의 크기가 늘어나지는 않는다.
반대로 지정한 크기보다 적은 문자가 저장되려고 하면 실제 저장크기가 저장한 문자만큼 줄어서 저장된다.
기억장소가 절약되는 장점이 있다.

4. 고정형 문자열 (최대 2000 BYTE)

char(XX)
char(10)으로 만들어진 필드에 영문자 5 크기만큼 데이터를 저장하면 실제 크기와 상관없이 고정형으로 10byte가 저장된다.

5. 고정형 유니코드 문자

nchar(XX) : 다국어를 입력하기 위한 고정형 자료형 최대 2000BYTE

6. 가변형 유니코드 문자

nvarchar(XX) : 다국어를 입력하기 위한 가변형 자료형 최대 4000BYTE

7. 날짜 데이터

DATE : BC 4712년 9999년 12월 31일 연월일시분초 입력 가능
TIMESTAMP : 연도 월 일 시 분 초 밀리초 까지 입력 가능
가장 일반적이고 많이 사용하는 날짜데이터 타입은 DATE
sysdate와 systimestamp는 현재 일자와 시간을 반환하는 오라클 내부 함수

8. LOB 타입

Large OBject의 약자로 대용량 데이터를 저장할 수 있는 타입
CLOB
: 문자형 대용량 객체, 고정길이와 가변길이 문자집합지원
(최대 크기(4G-1)(데이터베이스 블록 사이즈))
NCLOB : 유니코드(다국어 지원)를 포함한 문자형 대용량 객체
(최대크기(4G-1)(데이터베이스 블록 사이즈))
BLOB : 이진형 대용량 객체 (최대크기(4G-1)*(데이터베이스 블록 사이즈))
BFILE : 대용량 이진 파일에 대한 로케이너(위치, 이름) 저장 최대 4G

9. 지금 잘 사용되지 않는 자료형

varchar : varchar2와 같은 형식이지만 용량에서 varchar2가 업그레이드 되었으며, 이 때문에 잘 사용하지 않는다
LONG형 : 최대 2GB의 가변 길이 문자형, 잘 사용하지 않음
FLOAT : number의 하위 타입 2진수 22바이트
BINARY_FLOAT : 32비트 부동 소수점
BINARY_DOUBLE : 64비트 부동 소수점 수

🌱 Alter - 테이블의 수정

이미 만들어져 있는 테이블의 구조 등을 수정할 때 사용

🍂 필드명의 변경

ALTER TABLE 테이블명 RENAME COLUMN 변경전 이름 TO 변경후 이름

테이블이름 : 변경하고자하는 필드명이 있는 테이블의 이름
- booklist 테이블의 subject 필드명을 title로 수정하기
  alter table booklist rename column subject to title;
- 반대로 title 필드명을 subject 필드 명으로 수정
  alter table booklist rename column title to subject;
- memberlist 테이블의 memvername 필드를 name으로 변경
  alter table memberlist rename column membername to name;
- rentlist 테이블의 rent_date 필드를 rentdate로 변경
  alter table rentlist rename column rent_date to rentdate;
- rentlist의 indexk를 numseq로 변경
  alter table rentlist rename column indexk to numseq;
create로 테이블을 만들며 생성한 기본키와 외래키 등등은 필드 명이 바뀌어도 바뀐이름으로 자동 적용

🍂 필드 자료형의 변경

ALTER TABLE 테이블명 MODIFY 필드명 자료형

varchar2(12)였던 memberlist 테이블의 name 필드를 varchar2(30)으로 변경
alter table memberlist modify name varchar2(30);
booklist의 booknum 필드를 number(5)로 자료형 변경
alter table booklist modify booknum number(5);
memberlist의 membernum 필드를 number(5)로 자료형 변경
alter table memberlist modify membernum number(5);
rentlist의 booknum 필드를 number(5)로 자료형 변경
alter table rentlist modify booknum number(5);
rentlist의 membernum 필드를 number(5)로 자료형 변경
alter table rentlist modify membernum number(5);
외래키로 연결되어 참조되고, 참조하고 있는 필드들은 위 명령으로 수정이 바로 불가능하다
가능하게 하려면, 외래키 제약 조건을 수정하여 없애버리고, 참조되는 필드와 참조하는 필드를 모두 수정한 후, 외래키 제약 조건을 다시 설정해야 한다.

🍂 필드의 추가

ALTER TABLE 테이블명 ADD 필드명 자료형

booklist에 구매등급을 저장할 수 있는 grade 필드를 varchar2(15)로 추가
alter table booklist add grade varchar2(15);
memberlist에 성별(gender) 필드를 varchar2(3)으로 추가
alter table memberlist add gender varchar2(3);
memberlist에 나이(age) 필드를 number(2)로 추가
alter table memberlist add age number(2);

🍂 필드의 삭제

ALTER TABLE 테이블명 DROP COLUMN 필드명

memberlist 테이블에서 성별 필드 제거
alter table memberlist drop column gender;
booklist에서 grade 필드 삭제
alter table booklist drop column grade;
memberlist에서 나이(age) 필드 삭제
alter table memberlist drop column age;
다음 항목을 위해 삭제됐던 필드를 다시 생성

🍂 제약 조건의 추가/제거

추가

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건식

필드LEVEL(수준)의 제약 조건은 필드를 modify 해서 같이 수정 생성한다.
테이블 LEVEL의 제약 조건은 위의 명령 형식으로 제약조건 이름과 함께 추가한다.
memberlist 테이블의 성별 (gender) 필드에 'F', 'M' 두 글자만 입력되도록 제약 조건을 추가하기
- in()함수
  : 괄호 안의 항목 중 하나에 해당하면 true가 리턴되는 함수,
- 위의 내용은 check 함수에 의해 gender 필드의 들어갈 값이 in() 함수 안의 항목 중 하나와 같다면 입력 허용, 아니면 불허하는 제약 조건
memberlist 테이블의 나이(age) 필드에 120살이 초과되는 나이는 입력되지 못하게 제약 조건 추가
alter table memberlist add constraint check_age check(age<=120)

삭제

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명

rentlist 테이블의 booknum에 걸려있는 외래키 제약조건 제거
alter table rentlist drop constraint fk1;
rentlist 테이블의 membernum에 걸려 있는 외래키 제약조건 제거
alter table rentlist drop constraint fk2;
rentlist 테이블의 기본키 제거
alter table rentlist drop constraint rt_pk;
위에서 실패했던 필드의 자료형 수정 재실행
- booklist의 booknum 필드를 number(5)로 자료형 변경
  alter table booklist modify booknum number(5);
- memberlist의 membernum 필드를 number(5)로 자료형 변경
  alter table memberlist modify membernum number(5);
- rentlist의 booknum 필드를 number(5)로 자료형 변경
  alter table rentlist modify booknum number(5);
- rentlist의 membernum 필드를 number(5)로 자료형 변경
  alter table rentlist modify membernum number(5);
위에 삭제되었던 fk1, fk2, rt_pk 제약조건을 다시 설정

🍂 테이블 생성 & 수정 연습

1. 테이블 생성 EX

테이블명 : ORDERS1
필드
ORDER_ID NUMBER(12,0)           ORDER_DATE DATE
ORDER_MODE VARCHAR2(8)       CUSTOMER_ID NUMBER(6,0)
ORDER_STATUS NUMBER(2,0)      ORDER_TOTAL NUMBER(8,2)
SALE_REP_ID NUMBER(6,0)          PROMOTION_ID NUMBER(6,0)
제약사항 : 기본키는 ORDER_ID - 테이블레벨
ORDER_MODE에는 'direct', 'online'만 입력 가능 - 테이블레벨
ORDER_TOTAL의 디폴트 값은 0 - 필드레벨

2. 테이블 수정 EX

CUSTOMER_ID 필드명을 CUSTOMER_NUMBER로 수정
alter table orders1 rename column customer_id to customer_number;
Promotion_ID값은 10000~99999 사이의 값만 저장 가능
alter table orders1 add constraint pro_check check(promotion_id between 10000 and 99999);

🍂 테이블의 제거

DROP TABLE orders2 purge;
- purge는 생략 가능
- purge가 없이 삭제된 테이블은 나중에 휴지통과 같이 복구가 가능
- purge를 사용하면 완전 삭제

🍂 테이블의 복사

CREATE TABLE orders2 AS SELECT*FROM orders1;
as select 구문은 select구문 이후에 다시 학습

🌱 View (일단 정리만)

오라클 - 뷰(View)

물리적인 테이블에 근거한 논리적인 가상 테이블
가상이란 단어는 실질적으로 데이터를 저장하고 있지 않기 때문에 붙인 것이고,
테이블이란 단어는 실질적으로 데이터를 저장하고 있지 않더라도
사용 계정자는 마치 테이블을 사용하는 것과 동일하게 뷰를 사용할 수 있기 때문에 붙인 것
뷰는 기본테이블에서 파생된 객체로서 기본테이블에 대한 하나의 쿼리문
실제 테이블에 저장된 데이터를 뷰를 통해서 볼 수 있도록 한다.
사용자에게 주어진 뷰를 통해서 기본 테이블을 제한적으로 사용할 수 있다.
뷰는 이미 존재하고 있는 테이블에 제한적으로 접근하도록 한다.
뷰를 생성하기 위해서는 실질적으로 데이터를 저장하고 있는 물리적인 테이블이 존재해야 하는데 이 테이블은 기본테이블이라고 한다.
두 개 이상의 테이블 또는 한개의 테이블이나 또 다른 뷰를 참조하는 객체이다.
저장된 테이블이라기 보다 공식 또는 select 문을 갖고 있다가 명령대로 불러와 그때 그때 구성하는 형식이다.
원본의 데이터 변화가 실시간으로 반영된다.
생성방법
- Create or Replace View 뷰이름 as (select 구문)
- select 명령
  : 실제테이블의 부분집합(행일부 또는 전부, 열 일부 또는 전부)
- select 명령과 Join 명령 학습 후 다시 상세하게 공부할 예정

🌱 Sequence

오라클 - 시퀀스(Sequence)

테이블 내의 유일한 숫자를 자동으로 생성하는 자동 번호 발생기
테이블 생성 후 시퀀스(일련번호)를 따로 만들어야 한다.
생성 방법
- create sequence 시퀀스이름 start with 시작숫자 increment by 증가량;
- 주로 number 형식에 기본키값으로 사용한다.
- 일련번호정도로 이해해도 무방
- number(자리수) : 자료형의 자리수가 몇자리냐에 따라 그 만큼 숫자가 증가할 수 있다.

🍂 시퀀스의 생성

🍂 현재 시퀀스가 어디까지 증가되어 있는지 확인

위에서 생성한 member_seq 시퀀스로 확인
select member_seq.currVal, member_seq.nextVal from dual;

🍂 시퀀스 수정

시퀀스 수정 : 최대 증가량을 14까지로 제한
alter sequence member_seq maxvalue 14;

🍂 시퀀스 삭제

drop sequence member_seq;

🍂 시퀀스 재생성

다음 숫자부터 시작하게 하여 기존 레코드와 중복되지 않게 한다
create sequence member_seq start with 4 increment by 1;

현재 로그인한 사용자가 만들거나 보유하고 있는 테이블의 목록을 조회
select*from tabs;
1부터 1씩 늘어나는 book_seq / rent_seq를 생성하기
create sequence book_seq start with 1 increment by 1;
create sequence rent_seq start with 1 increment by 1;

🌲 DML(Data Manegement Language) - 데이터 조작어

테이블에 레코드를 조작(추가, 수정, 삭제, 조회)하기 위한 명령어들
- INSERT(추가)
- UPDATE(수정)
- DELETE(삭제)
- SELECT(조회 및 선택)

🍂 샘플 테이블 생성

	create table exam01(
		deptno number(2),       -- 부서번호
		dname varchar2(14),     -- 부서명
		loc varchar2(14)        -- 위치
	);

🍂 레코드 추가

레코드 추가 명령 사용 #1
- insert into 테이블이름(필드명1, 필드명2, ... ) values(값1, 값2, ...)
- 값은 문자('123')와 숫자(123)를 구분하여 입력한다.
레코드 추가 명령 사용 #2
- insert into 테이블이름 value(전체 column(필드, 열)에 넣을 값들);
첫 번째 방식은 필드명과 입력되어야 하는 값을 1:1로 매핑하여 입력.
'null값이 있어도 되는 필드'와 '기본값이 있는 필드'는 필드명과 값을 생략하고 입력 가능하다
두번째 방식은 "모든" 필드에 해당하는 데이터를 "모두" 입력하는 경우로서 필드명들을 명령어 속에 나열하지 않아도 되지만,
필드 순서대로 “빠짐없이” 데이터가 나열되어야 하는 불편함도 있다.
첫 번째 방식의 레코드 추가
insert into exam01(deptno, dname, loc) values(10, 'ACCOUNT', 'NEW YORK');
두 번째 방식의 레코드 추가
insert into exam01 values(30, 'SALES', 'CHICHAGO');

두가지 방법 모두 null값을 입력할 수 있다.
booklist의 outprice 필드명을 rentprice로 수정
alter table booklist rename column outprice to rentprice;

연습 1

두가지 방법 중 자유롭게 선택해서, booklist 테이블에 10개의 레코드를 추가하기
booknum은 시퀀스를 이용
grade 는 'all' '13' '18' 세가지만 골라서 입력하기
1번 예시를 보고 만들기, 내용은 임의로 정할 것

연습 2

memberlist에 7명의 데이터를 추가하기 member_seq를 이용할 것
(memberlist도 레코드가 10개가 되도록)
마찬가지로 상세 내용은 임의로 정하기

연습 3

rentlist 테이블도 rent_seq를 이용해서 10개의 데이터를 추가하기
membernum과 booknum은 외래키이므로 memberlist와 booklist에 없는 내용이 들어가면 오류가 나므로 주의할 것!!

🍂 데이터 변경 - 수정(UPDATE)

UPDATE 테이블명 SET 변경내용 WHERE 검색 조건

update memberlist set age=30 where membernum=8;
memberlist에서 membernum이 8인 레코드의 age 값을 30으로 수정
오라클에서 문자는 'a' 작은따옴표로 묶고 숫자는 아무것도 넣지 않는다.
문자는 한글자이든 여러글자이든 작은따옴표로 묶어서 표시한다.
명령문에 WHERE 이후 구문은 생략이 가능
다만 이 부분을 생략하면 모든 레코드를 대상으로 해서 UPDATE 명령이 실행되어 모든 레코드가 수정된다.
검색조건
: 필드명 (비교-관계연산자) 조건값 으로 이루어진 조건연산이며, 흔히 자바에서 if() 괄호안에 사용했던 연산을 그대로 사용하는 것이 보통
예시) 나이가 29세 이상 -> WHERE AGE >= 29

데이터 수정의 예시

exam01 테이블에서 deptno 값을 모두 30으로 수정
update exam01 set deptno=30;
exam01 테이블에서 dname 'ACCOUNT'인 레코드의 deptno를 10으로 수정
update exam01 set deptno=10 where dname='ACCOUNT';
exam01 테이블에서 dname이 'SALES'인 레코드의 deptno를 20으로 수정
update exam01 set deptno=20 where dname='SALES';
exam01 테이블에서 dname이 'OPERATION'인 deptno를 30으로 수정
update exam01 set deptno=30 where dname='OPERATION';
exam01 테이블에서 dname이 'MARKETING'인 레코드의 deptno를 40으로 수정
update exam01 set deptno=40 where dname='MARKETING';
exam01 테이블에서 deptno가 30인 레코드의 loc를 'BOSTON'으로 수정
update exam01 set loc='BOSTON' where deptno=30;
exam01 테이블에서 deptno가 40인 레코드의 loc를 'LA'으로 수정
update exam01 set loc='LA' where deptno=40;
booklist 테이블의 제목 '봉제인형 살인사건'도서의 grade를 '18'로 수정
update booklist set grade='18' where subject='봉제인형 살인사건';
emp 테이블의 모든 사원의 sal 값을 10%씩 인상
update emp set sal = sal*1.1;
sal = sal + sal*0.1 (이렇게 작성해도 됨)
emp 테이블에서 sal 값이 3000 이상인 사원의 급여 10% 삭감
update emp set sal=sal*0.9 where sal>=3000;
emp 테이블의 hiredate가 2002년 이전인 사원의 급여를 +2000 -> (2001-12-31 보다 작거나 같거나)
update emp set sal = sal+2000 where hiredate < '2002-01-01';

Bona의 블로그 입니다.

제가 공부하고 공유하고 싶은 글을 올리고 있습니다.

이전 포스트

DAY_034[국비] 자바 웹프로그래밍 과정

다음 포스트