21.09.27 - 웹 개발 입문 31일차

데이터베이스 - SQL Developer

    테이블 제약조건
    - 테이블에 데이터 추가와 관련된 제약을 설정하는 것을 말한다
    - 원치 않는 데이터를 차단하여 데이터의 신뢰도를 높인다
    1. NOT NULL 제약조건   
    2. UNIQUE 제약조건
    3. CHECK 제약조건
    4. DEFAULT 제약조건
    5. PRIMARY KEY 제약조건
    6. FOREIGN KEY 제약조건

 

 

Q.  다음 요구사항에 맞는 테이블을 구현하고 데이터를 추가해보세요
    <요구사항>
    - 상품(Product) 정보를 보관할 수 있는 테이블을 구현
    - 보관할 항목 : 상품번호, 상품이름, 상품분류, 판매가, 재고수량
    - 상품번호(product_code)는 시퀀스를 이용하여 부여되는 고유번호
    - 상품이름(product_name)은 한글 20자 이내로 등록되며 필수항목
    - 상품분류(product_type)는 다음 중에서만 허용하며 필수항목
        - 생활용품, 가전제품, 식료품, 애견용품
    - 판매가(product_price)는 0 이상으로 설정 가능하며 설정하지 않으면 0원으로 자동설정
    - 재고수량(product_stock)은 판매가와 동일하게 처리
    <데이터>
    - 1번 상품 : 고무장갑(생활용품), 판매가 2500원. 재고 30개
    - 2번 상품 : 푸라면(식료품), 판매가 1300원. 재고 100개
    - 3번 상품 : 드럼세탁기(가전제품), 판매가 500000원, 재고 3개
    - 4번 상품 : 개껌(애견용품), 판매가 500원. 재고 250개

create table Product(
product_code number not null unique,
product_name varchar2(60) not null,
product_type varchar2(12) not null check(product_type in('생활용품', '가전제품', '식료품', '애견용품')),
product_price number default 0 check(product_price >= 0),
product_stock number default 0 check(product_stock >= 0)
);

create sequence Product_seq;

insert into product(product_code, product_name, product_type, product_price, product_stock) values(PRODUCT_SEQ.nextval, '고무장갑','생활용품',2500,30);
insert into product(product_code, product_name, product_type, product_price, product_stock) values(PRODUCT_SEQ.nextval, '푸라면','식료품',1300,100);
insert into product(product_code, product_name, product_type, product_price, product_stock) values(PRODUCT_SEQ.nextval, '드럼세탁기','가전제품',500000,3);
insert into product(product_code, product_name, product_type, product_price, product_stock) values(PRODUCT_SEQ.nextval, '개껌','애견용품',500,250);

select * from product;

commit;

 

 

 

데이터베이스 - 날짜 데이터 다루기

    날짜 데이터 다루기
    - date : 일반적인 시각
    - timestamp : 정밀한 시각

    to_date() 시간형식
    - YYYY : 연도 4자리
    - MM : 월 2자리
    - DD : 일 2자리
    - HH24 : 24시간방식 시간 2자리
    - MI : 분 2자리
    - SS : 초 2자리

create table schedule(
schedule_no number,
schedule_name varchar2(300),
schedule_time date
);
create sequence schedule_seq;

-- 1. 시간은 문자열 형태로도 추가할 수 있다.
-- 문제점 : 시간 표시 형식이 다른 운영체제 또는 데이터베이스에서는 오류가 발생
insert into schedule(schedule_no, schedule_name, schedule_time)
values(schedule_seq.nextval, '자바스터디', '2021-09-28');--28/09/21

-- 2. to_date() 라는 명령을 이용하여 문자열을 원하는 시간 형식으로 변환할 수 있다.
-- 사용법 : to_date('시간데이터', '시간형식')
insert into schedule(schedule_no, schedule_name, schedule_time)
values(schedule_seq.nextval, '딸내미100일', to_date('2021-10-03', 'YYYY-MM-DD'));

insert into schedule(schedule_no, schedule_name, schedule_time)
values(schedule_seq.nextval, '딸내미100일', to_date('21-10-03', 'YY-MM-DD'));

insert into schedule(schedule_no, schedule_name, schedule_time)
values(schedule_seq.nextval, '딸내미100일점심식사', to_date('2021-10-03 13:10:00', 'YYYY-MM-DD HH24:MI:SS'));

insert into schedule(schedule_no, schedule_name, schedule_time)
values(schedule_seq.nextval, '딸내미100일점심식사', to_date('2021-10-03T13:10', 'YYYY-MM-DD"T"HH24:MI'));

-- 3. 날짜에만 있는 특수한 데이터
-- sysdate : 현재시각을 date 형태로 보관하는 내장객체
-- systimestamp : 현재시각을 timestamp 형태로 보관하는 내장객체
insert into schedule(schedule_no, schedule_name, schedule_time)
values(schedule_seq.nextval, '열심히공부하기', sysdate);

commit;

 

 

 

Q.  다음 요구사항에 맞게 테이블을 구현하고 데이터를 저장하세요.
    <요구사항>
    - 호텔 예약현황을 저장하고 싶다(hotel_reservation)
    - 저장할 항목 : 예약번호, 예약일자, 예약자이름, 예약숙소명, 입실예정일자, 퇴실예정일자
        - 예약번호(hotel_reservation_code)는 시퀀스로 자동부여되도록 설정하며 필수, 중복 불가
        - 예약일자(hotel_reservation_time)은 예약 데이터가 등록된 시점의 시간 정보로 추가
        - 예약자 이름(hotel_reservation_client)은 일반적인 이름 규약에 맞게 한글 기준으로 작성
        - 예약숙소명(hotel_name)은 한글 20자 이내로 작성
        - 입실예정일자는 날짜 형태이며, 지정하지 않을 경우에는 현재시각으로 설정
        - 퇴실예정일자는 반드시 지정되어야 한다
    <데이터>
    [예약번호 : 1] 홍길동 - 신라호텔 (2021-10-01 ~ 2021-10-05)
    [예약번호 : 2] 이몽룡 - 그랜드하얏트 ( ~ 2021-10-01)
    [예약번호 : 3] 이소룡 - 호텔캘리포니아 (2021-10-05 ~ 2021-10-06)
    [예약번호 : 4] 장보고 - 시그니엘 ( ~ 2021-10-10)
    [예약번호 : 5] 임꺽정 - 매리어트 (2021-10-20 ~ 2021-10-22)

 

create table hotel_reservation(
hotel_reservation_code number not null unique,
hotel_reservation_time date default sysdate not null,
hotel_reservation_client varchar2(21) not null,
hotel_name varchar2(60) not null,
check_in_time date default sysdate not null,
check_out_time date not null
);
create sequence hotel_reservation_seq;

insert into hotel_reservation(
    hotel_reservation_code, 
    hotel_reservation_time, 
    hotel_reservation_client, 
    hotel_name, 
    check_in_time, 
    check_out_time
)
values(
    hotel_reservation_seq.nextval, 
    sysdate, 
    '홍길동', 
    '신라호텔', 
    to_date('2021-10-01', 'YYYY-MM-DD'), 
    to_date('2021-10-05', 'YYYY-MM-DD')
);

insert into hotel_reservation(
    hotel_reservation_code, 
    --hotel_reservation_time, 
    hotel_reservation_client, 
    hotel_name, 
    --check_in_time, 
    check_out_time
)
values(
    hotel_reservation_seq.nextval, 
    --sysdate, 
    '이몽룡', 
    '그랜드하얏트', 
    --sysdate, 
    to_date('2021-10-05', 'YYYY-MM-DD')
);

insert into hotel_reservation(
    hotel_reservation_code, 
    hotel_reservation_time, 
    hotel_reservation_client, 
    hotel_name, 
    check_in_time, 
    check_out_time
)
values(
    hotel_reservation_seq.nextval, 
    sysdate, 
    '이소룡', 
    '호텔캘리포니아', 
    to_date('2021-10-05', 'YYYY-MM-DD'), 
    to_date('2021-10-06', 'YYYY-MM-DD')
);

insert into hotel_reservation(
    hotel_reservation_code, 
    hotel_reservation_time, 
    hotel_reservation_client, 
    hotel_name, 
    check_in_time, 
    check_out_time
)
values(
    hotel_reservation_seq.nextval, 
    sysdate, 
    '장보고', 
    '시그니엘', 
    sysdate, 
    to_date('2021-10-10', 'YYYY-MM-DD')
);

insert into hotel_reservation(
    hotel_reservation_code, 
    hotel_reservation_time, 
    hotel_reservation_client, 
    hotel_name, 
    check_in_time, 
    check_out_time
)
values(
    hotel_reservation_seq.nextval, 
    sysdate, 
    '임꺽정', 
    '매리어트', 
    to_date('2021-10-20', 'YYYY-MM-DD'), 
    to_date('2021-10-22', 'YYYY-MM-DD')
);

commit;

 

 

 

 

Q.  다음 요구사항에 맞게 회원테이블을 구현  
    <요구사항>
    - 테이블 이름은 "member"로 설정
    - 구성 항목 : 아이디, 비밀번호, 닉네임, 이메일, 전화번호, 생년월일, 가입일자, 포인트, 등급
        - 아이디(member_id) : 5~20자의 영문 소문자, 숫자와 특수기호(_),(-)만 사용 가능합니다.
        - 비밀번호(member_pw) : 8~16자 영문 대 소문자, 숫자, 특수문자를 사용하세요.
        - 닉네임(member_nick) : 한글 10글자 이내로 설정.
        - 생년월일(member_birth) : YYYY-MM-DD 형태의 데이터
        - 이메일(member_email) : 50byte 이내로 저장되며, 선택 입력 사항
        - 전화번호(member_phone) : 010-XXXX-XXXX 형태이며, 선택 입력 사항
        - 가입일자(member_join) : 반드시 저장시점의 시각으로 설정
        - 포인트(member_point) : 가입 시 기본적으로 100point를 지급
        - 등급(member_grade) : 가입 시 기본적으로 '준회원'으로 설정되며 준회원, 정회원, 우수회원, 관리자

           중에서 설정 가능

 

create table member(
member_id varchar2(20) not null unique,
member_pw varchar2(16) not null,
member_nick varchar2(30) not null unique,
member_birth date not null,
member_email varchar2(50),
member_phone char(13),
member_join date default sysdate not null,
member_point number default 100 not null check(member_point >= 0),
member_grade varchar2(12) default '준회원' not null check(member_grade in ('준회원', '정회원', '우수회원', '관리자'))
);

insert into member(member_id, member_pw, member_nick, member_birth, member_email, member_phone)
values('testuser', 'testuser', '테스트유저', to_date('1999-01-10', 'YYYY-MM-DD'), null, null); 

commit;

 

추가 조건 설정
1. 대표 항목 설정(PRIMARY KEY) : 테이블 컬럼의 대표
     단 하나만 설정이 가능하며 기본적으로 not null + unique 설정이 되어 있다

      not null + unique 항목 중 가장 범용성이 좋은 항목으로 선정
2. 정규표현식 조건 설정
      regexp_like() 함수 사용
      regexp_like(항목, '정규표현식')
      check 안에 사용하여 해당 항목이 검사되도록 구현
      데이터 추가 시 속도가 느려짐

drop table member;
create table member(
member_id varchar2(20) primary key check(regexp_like(member_id, '^[a-z][a-z0-9-_]{4,19}$')),
member_pw varchar2(16) not null check(regexp_like(member_pw, '^[a-zA-Z0-9-_!@#$]{8,16}$')),
member_nick varchar2(30) not null unique check(regexp_like(member_nick, '^[ㄱ-ㅎㅏ-ㅣ가-힣0-9]{1,10}$')),
member_birth date not null,
member_email varchar2(50),
member_phone char(13) check(regexp_like(member_phone, '^010-[0-9]{4}-[0-9]{4}$')),
member_join date default sysdate not null,
member_point number default 100 not null check(member_point >= 0),
member_grade varchar2(12) default '준회원' not null check(member_grade in ('준회원', '정회원', '우수회원', '관리자'))
);

insert into member(member_id, member_pw, member_nick, member_birth, member_email, member_phone)
values('testuser1', 'testuser1', 'ㄴㅇㄴ123', to_date('1999-01-10', 'YYYY-MM-DD'), null, '010-1234-1234'); 

commit;

 

 

데이터베이스 - 조회(항목)

 테이블 조회(Read)
   - 테이블에 들어있는 데이터를 "원하는 목적"에 맞게 조회하는 기법
   - 필터링, 정렬, 분석(그룹, 통계), 오라클 제공 함수

create table product(
no number primary key,
name varchar2(30) not null,
type varchar2(15) check(type in ('과자','아이스크림','주류','사탕')),
price number,
made date,
expire date
);

insert into product values(1, '스크류바', '아이스크림', 1200, '2020-05-01', '2020-10-01');
insert into product values(2, '마이쮸', '사탕', 900, '2020-01-01', '2021-01-01');
insert into product values(3, '초코파이', '과자', 3000, '2020-01-01', '2021-01-01');
insert into product values(4, '맛동산', '과자', 2200, '2020-02-01', '2020-10-20');
insert into product values(5, '참이슬', '주류', 1000, '2020-01-05', '2020-04-05');
insert into product values(6, '처음처럼', '주류', 1000, '2020-03-15', '2020-08-15');
insert into product values(7, '바나나킥', '과자', 1500, '2020-05-03', '2020-06-03');
insert into product values(8, '빠삐코', '아이스크림', 1000, '2019-12-01', '2020-06-01');
insert into product values(9, '멘토스', '사탕', 1200, '2020-03-20', '2020-12-31');
insert into product values(10, '오레오', '과자', 2100, '2019-06-01', '2020-06-01');

commit;

 

product 테이블의 모든 항목(*)을 조회하겠다

SELECT * FROM product;

 

product 테이블의 이름(name)을 조회

SELECT name FROM product;

 

product 테이블의 이름(name)과 가격(price)을 조회

SELECT name, price FROM product;

 

product 테이블의 name, price에 별칭을 부여하여 조회

select name 이름, price 가격 from product;
select name "상품 이름", price "판매 가격" from product;

 

 

 

 행 필터링 조건 설정
    = 데이터베이스에 10만개의 데이터가 있을 경우 다 보는 경우보다는 
        "목적"에 맞게 선택적으로 보는 것이 중요
    = select 항목 from 대상 where 필터식;  

 

가격이 1000원인 상품을 조회

select * from product where price = 1000;

 

번호가 7번인 상품을 조회

select * from product where no = 7;

 

가격이 2000원 미만인 상품을 조회

select * from product where price < 2000;

 

 

가격이 1000원이상 2000원 이하인 상품을 조회
오라클에서는 &&, || 를 사용할 수 없다.(다른 의미의 기호)
&& 대신에 and, || 대신에 or 이라고 사용한다
a이상 b이하 라고 표현할 수 있지만 a와 b사이 처럼 표현도 가능하다. 

select * from product where price >= 1000 and price <=2000 ;
select * from product where price  between 1000 and 2000 ;

 

가격이 1000원이 아닌 상품 조회

select * from product where price != 1000;

 

 

 

문자열 조건
오라클에서는 문자열도 숫자처럼 비교가 가능하다.

 

이름이 스크류바인 상품 정보를 조회

select * from product where name = '스크류바';

 

아이스크림만 조회

select * from product where type = '아이스크림';

 

 

 

 유사검색 : 일부분만으로 검색하는 방법(instr 명령 사용 - 위치 ( index of) / like 연산자 사용 - (컨텐츠))
 like에서는 %를 "있어도 그만 없어도 그만"으로 처리한다
         = 시작한다는 의미는 뒤에 %를 붙이면 된다
         = 종료한다는 의미는 앞에 %를 붙이면 된다
         = 포함된다는 소리는
 instr()에서는 문자열의 시작점을 1로 본다.
         = 시작한다는 의미는 instr()의 결과가 1이란 소리이다.
        = 종료한다는 의미는 instr()의 결과가 (항목글자수 - 검색어글자수 +1)란 소리이다.
         = 포함된다는 의미는 instr()의 결과가 0보다 크단 소리이다.

 

 

select instr(name, '바')from product;

 

 

이름이 '바'로 시작하는 상품 정보를 조회

select * from product where name like '바%'; 
select * from product where instr(name, '바') = 1;
select * from product where regexp_like(name,'^바');

 

이름이 '바'로 끝나는 상품 정보를 조회

select * from product where name like '%바';
select * from product where instr(name, '바') = length(name) - length('바') +1;
select * from product where regexp_like(name,'바$');

 

이름에 '바'가 포함된 상품 정보를 조회

select * from product where name like '%바%';
select * from product where instr(name, '바') > 0;
select * from product where regexp_like(name,'바');

 

 

 

 날짜 조건
   = 문자열처럼 비교가 가능하지만 잘 사용하지 않는다
   = 날짜는 System.currentTimeMillis() 형태처럼 숫자로도 취급되어 계산이 가능하다.
   = extract() 명령을 이용하면 원하는 항목만 추출할 수도 있다.

 

 

2019년에 제조된 상품 정보를 조회

 

1. 2019년 1월 1일 0시 0분 0초 부터 2019년 12월 31일 23시 59분 59초까지

select * from product
where made >= to_date('20190101000000', 'YYYYMMDDHH24MISS')
and made <= to_date('20191231235959', 'YYYYMMDDHH24MISS');

select * from product
where made between to_date('20190101000000', 'YYYYMMDDHH24MISS')
and to_date('20191231235959', 'YYYYMMDDHH24MISS');

 

2. 연도가 2019인 경우

select * from product where extract(year from made) = 2019;

 

3. 날짜가 2019로 시작하는 경우

select * from product where made like '19/%';
 
select * from product where instr(made, '19/') = 1;

 

 

Q. 과제 : 다음에서 요구하는 데이터를 조회하시오.
1. 유통기한이 2021년인 제품을 출력

select * from product where extract(year from expire) = 2021;

2. 여름(6,7,8)월에 생산된 과자 목록을 출력

select * from product where extract(month from made) between 06 and 08 and type ='과자';

 

3. 과자와 사탕 중에서 유통기한이 1년 이하인 제품의 목록을 출력

select * from product where type = '과자' or type ='사탕' and expire - made <= 365;

4. 최근 2년간 생산된 제품의 목록을 출력(730일 전부터 지금까지)

select * from product where made between to_date('20190927', 'YYYYMMDD') and sysdate;