49. Oracle ( 함수 이용, DB 등록, 수정 예시 문제 )

1. 테이블 생성, 컬럼 추가, 수정

-- 1.테이블 생성
아이디 USER_ID 가변길이 30
암호 USER_PW 가변길이 200
나이 USER_AGE 숫자형 3,0
연락처 USER_TEL 가변길이 15
등록일 USER_DATE DATE

CREATE TABLE MEMBERTBL(
USER_ID VARCHAR2(30),
USER_PW VARCHAR2(200),
USER_AGE NUMBER(3,0),
USER_TEL VARCHAR2(15),
USER_DATE DATE
)

SELECT * FROM MEMBERTBL;

-- 2.USER_ID에 기본키 제약조건 추가
ALTER TABLE MEMBERTBL MODIFY USER_ID PRIMARY KEY;
ALTER TABLE MEMBERTBL ADD PRIMARY KEY(USER_ID);  -- SYS지정키
ALTER TABLE MEMBERTBL ADD CONSTRAINT PK_MEMBERTBL_USER_ID PRIMARY KEY(USER_ID); -- 키 설정

-- 3.USER_NAME 가변길이 20 컬럼 추가
ALTER TABLE MEMBERTBL ADD USER_NAME VARCHAR2(20);

-- 4.USER_TEL 컬럼 명을 USER_PHONE으로 변경
-- ALTER TABLE 테이블명 RENAME COLUME 변경전컬럼 TO 변경후 컬럼
ALTER TABLE MEMBERTBL RENAME COLUMN USER_TEL TO USER_PHONE;

MEMBERTBL 테이블

 

2. 데이터 추가, 가공

-- 5.회원아이디가 A,B,C인 회원 3명을 추가
INSERT INTO MEMBERTBL VALUES('A','A',30,'010-0000-0001',CURRENT_TIMESTAMP,'A님');
INSERT INTO MEMBERTBL VALUES('B','B',40,'010-0000-0002',CURRENT_TIMESTAMP,'B님');
INSERT INTO MEMBERTBL VALUES('C','C',50,'010-0000-0003',CURRENT_TIMESTAMP,'C님');
COMMIT;
-- 6.회원아이디가 A로 일치하는 개수 구하기 (아이디 중복확인)
SELECT COUNT(*) FROM MEMBERTBL WHERE USER_ID = 'A';
-- 7.회원아이디와 암호가 일치하는 회원정보 조회하기 (로그인)
SELECT* FROM MEMBERTBL WHERE USER_ID = 'A' AND USER_PW='A';
-- 8.회원정보 변경하기 (아이디가 C인회원 이름 CCC 나이 77 연락처 010-0000-1234
UPDATE MEMBERTBL SET USER_NAME='CCC',USER_AGE=77,USER_PHONE='010-0000-1234' WHERE USER_ID='C';
COMMIT;
-- 9.회원아이디가 C이고 암호가 일치하는 회원 삭제 (회원탈퇴)
DELETE FROM MEMBERTBL WHERE USER_ID='C' AND USER_PW='C';
COMMIT;
-- 10.아이디가 B인 회원의 아이디를 제외한 모든 항목 0 또는 NULL로 변경
UPDATE MEMBERTBL SET USER_NAME=NULL,USER_PHONE=NULL,USER_AGE=0 WHERE USER_ID='B';
COMMIT;
-- 11. 모든 회원정보에 대해 이름을 기준으로 내림차순으로 정렬하여 조회
SELECT * FROM MEMBERTBL ORDER BY USER_NAME DESC;

6.일치하는 ID 수

7. A ID , A PW 에 일치하는 데이터

10. B의 데이터 ID를 제외하고 모두 0 과 NULL처리

11. 이름기준 내림차순 정렬

 

예시 문제 

-- BOARDTBL 테이블 생성
BRD_NO 글번호 NUMBER
BRD_TITLE 글제목 가변길이 300
BRD_CONTENT 글내용 CLOB
BRD_WRITER 가변길이 20
BRD_HIT NUMBER
BRD_DATE DATE

CREATE TABLE BOARDTBL (
BRD_NO NUMBER,
BRD_TITLE VARCHAR2(300),
BRD_CONTENT CLOB,
BRD_WRITER VARCHAR2(20),
BRD_HIT NUMBER,
BRD_DATE DATE
)
SELECT * FROM BOARDTBL;

-- 글번호 기본키 제약조건 추가
ALTER TABLE BOARDTBL ADD PRIMARY KEY(BRD_NO);
-- BRD_WRITER외래키 연결(MEMBERTBL, USER_ID) 오류 발생시 해결
ALTER TABLE BOARDTBL ADD FOREIGN KEY(BRD_WRITER) REFERENCES MEMBERTBL(USER_ID);
-- SEQ_BOARD_BRD_NO 이름의 시퀀스 생성( 시작값101, 증가값1 )
CREATE SEQUENCE SEQ_BOARD_BRD_NO
START WITH 101 
INCREMENT BY 1
NOMAXVALUE
NOCACHE;
-- 3개의 게시글 추가하기 (글번호는 시퀀스 사용)
INSERT INTO BOARDTBL VALUES (SEQ_BOARD_BRD_NO.NEXTVAL,'A글제목','A글내용','A',3,CURRENT_TIMESTAMP);
INSERT INTO BOARDTBL VALUES (SEQ_BOARD_BRD_NO.NEXTVAL,'B글제목','B글내용','B',3,CURRENT_TIMESTAMP);
INSERT INTO BOARDTBL VALUES (SEQ_BOARD_BRD_NO.NEXTVAL,'C글제목','C글내용','C',3,CURRENT_TIMESTAMP);
COMMIT;
-- 14 전체 게시물 최신순으로 조회하기 ( 글번호,제목,작성자,조회수,날짜(연,월만)) 표시
SELECT BRD_NO,BRD_TITLE,BRD_WRITER,BRD_HIT,BRD_DATE FROM BOARDTBL ORDER BY BRD_NO DESC;
-- 15 제목에 '제'가 포함된 항목조회
SELECT * FROM BOARDTBL WHERE BRD_TITLE LIKE '%' || '제' || '%';
-- 15 에서 10개씩 조회(페이지네이션)
SELECT * FROM
(SELECT BRD_NO, BRD_TITLE, BRD_WRITER,BRD_HIT,BRD_DATE, ROW_NUMBER() OVER (ORDER BY BRD_NO DESC) BRD_ROWN FROM BOARDTBL)
WHERE BRD_ROWN BETWEEN 1 AND 2;
-- 게시글 101의 조회수 3증가
UPDATE BOARDTBL SET BRD_HIT=BRD_HIT+3 WHERE BRD_NO=101;
COMMIT;
-- 14 조회시 작성자 아이디 대신 작성자 이름으로 조회
SELECT * FROM MEMBERTBL,
    (SELECT BRD_NO, BRD_TITLE, BRD_WRITER, BRD_HIT, BRD_DATE FROM BOARDTBL ORDER BY BRD_NO DESC) BOARDTBL
WHERE MEMBERTBL.USER_ID = BOARDTBL.BRD_WRITER;
-- 일자별 작성한 글 개수 구하기
SELECT TO_CHAR(BRD_DATE, 'YYYY-MM-DD'), COUNT(*) FROM BOARDTBL GROUP BY(TO_CHAR(BRD_DATE, 'YYYY-MM-DD'));
-- 작성자별 평균 조회수 구하기(소수점 1자리에서 반올림)
SELECT BRD_WRITER, ROUND( AVG(BRD_HIT),1) FROM BOARDTBL GROUP BY(BRD_WRITER);

중요한 점

정렬순 ORDER BY DESC (내림차순) , ASC (오름차순)

 

검색기능 - 해당 제목의 부분에 'A' 가 들어가는 검색시 조건

WHERE 제목?? LIKE '%' || 'A' || '%' ;

 

페이지 네이션 기능