학원수업_57day [JSP,Servlet(24day) - media query/ SQL / ERD

SQL

 

-- Oracle dual 테이블 : 오라클에서 제공하는 기본 테이블 , 함수와 예약어를 실행할 때 사용 

SELECT SYSDATE FROM DUAL;

sysdate 예약어

: 현재 시간을 표현하는 키워드 

 

Date 타입 : 데이터베이스 시간 ( 연,월,일 시,분,초 ) 를 관리하는 데이터 타입 

 

* 주요 함수 *

1. to_char() : 데이터베이스에 저장된 date 형의 정보를 문자열로 반환받을 때 사용하는 함수 
2. to_date() : 문자 타입의 시간 정보를 데이터베이스의 date 형으로 변환해 저장할 때 사용하는 함수 

 

 

● TO_CHAR() 함수를 이용해 원하는 시간 포맷으로 설정해 문자열로 반환받는다 

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY-M.M-D.D') FROM DUAL;

● 요일

SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL;

 

● 테이블 생성하여 TEST

CREATE TABLE date_test(
	id VARCHAR2(100) PRIMARY KEY,
	mydate DATE NOT NULL
)
DROP table date_test
INSERT INTO date_test(id,mydate) VALUES('java',sysdate);

SELECT * FROM date_test;

● java id 의 등록일시 ( 연 월 일)를 조회 

SELECT to_char(mydate,'YYYY.MM.DD') FROM DATE_TEST WHERE ID='java';

● sping id, mydate  2022.1.11  정보를 insert (추가해보기)

INSERT INTO date_test(id,mydate) VALUES('spring',to_date('2022.1.11','YYYY.MM.DD'));
INSERT INTO date_test(id,mydate) VALUES('ajax',to_date('2022.1.11','YYYY.MM.DD'));

 * 중간중간 COMMIT 까먹지말기~!

 

●  DATE 연산 : mydate 에서 -1하면 하루 전

SELECT id,mydate,mydate-1 FROM date_test;

● 현재 기준으로 몇일 경과했는지 조회 

SELECT id,mydate,sysdate-mydate FROM date_test;

● trunc() 소수점 이하 제외

SELECT ID,mydate,trunc(sysdate-mydate) FROM date_test;

 

* TRUNC 함수는 주로 소수점 절사 및 날짜의 시간을 없앨 때 사용한다.

- 함수  :  TRUNC("값", "옵션")

 

 

● 몇개월 경과 months_beween()

SELECT id,mydate,trunc(months_beween(sysdate,mydate)) FROM date_test;

●  몇년 경과

SELECT id,mydate,trunc(months_beween(sysdate,mydate)/12) FROM date_test;

---

Oracle 문자열 varchar2 타입은 최대 4000byte 까지 가능 
Oracle clob datatype 을 이용하면 많은 문자열을 저장할 수 있다.

* CLOB (character large object) 최대 4GB까지 가능 → 사용법은 varchar2 와 동일함 

CREATE TABLE clob_test(
	id VARCHAR2(100) PRIMARY KEY,
	content CLOB NOT NULL
)
INSERT INTO clob_test(id,content) VALUES('java','test');

select * from clob_test;

---

ERD 프로그램으로 관계 형성을 보고 만들어 보자.

● 회원정보를 담은 테이블과 게시판 테이블 생성

CREATE TABLE community_member(
	id VARCHAR2(100) PRIMARY KEY,
	password VARCHAR2(100) NOT NULL,
	name VARCHAR2(100) NOT NULL
)

● 회원정보 추가

INSERT INTO community_member(id,password,name) VALUES('java','a','아이유');
INSERT INTO community_member(id,password,name) VALUES('spring','a','강하늘');

● community_member 테이블의 아이디가 'JAVA' , 'spring' 을 조회

COMMIT

SELECT * FROM community_member WHERE id='java';
SELECT * FROM community_member WHERE id='spring';

● 게시판 테이블 생성

CREATE TABLE board(
	no NUMBER PRIMARY KEY,
	title VARCHAR2(100) NOT NULL,
	content CLOB NOT NULL,
	hits	NUMBER default 0,
	time_posted DATE NOT NULL,
	id VARCHAR2(100) NOT NULL,
	CONSTRAINT board_fk FOREIGN KEY(id) REFERENCES community_member(id)
)

 

* 여기서 잠시 오래전에 해서 까먹었던 것들을 참고하자!

* constraint

제약, 데이터의 품질을 위한 data Object라 할수 있다. 제약 조건을 사용하여 잘못된, 적절하지 않은 데이터 입력하는것을 방지한다

- 데이터를 인가되지 않은 방법으로 변경할 수 없도록 보호하는 성질

 

* 흔히 사용 하는 제약조건

NOT NULL	'필수 입력 사항'을 의미
UNIQUE	중복성 배제, '유일한 값'으로 존재해야 함을 의미
PRIMARY KEY(기본키)	NOT NULL+ UNIQUE 로, '테이블 에서 대표되는 컬럼'을 의미
FOREIGN KEY(외래키)	'참조하는 테이블에서 존재하는 값만 사용 가능'을 의미
CHECK	'주어진 조건에 해당하는 값만 입력 가능'을 의미

 

 

● 게시판 번호 시퀀스 생성 

CREATE SEQUENCE board_seq;
DROP SEQUENCE board_seq;

 

 

board 에  insert DML

no :  board_seq.nextval   ,  title : 공부 1 

content : 월요일 즐겁네요  ,  time_posted : sysdate  , id : java

INSERT INTO board(no,title,content,time_posted,id)
VALUES(board_seq.nextval,'공부1','월요일 즐겁네요',sysdate,'java')

INSERT INTO board(no,title,content,time_posted,id)
VALUES(board_seq.nextval,'공부2','월요일 기쁘네요',sysdate,'spring')

INSERT INTO board(no,title,content,time_posted,id)
VALUES(board_seq.nextval,'공부3','월요일 행복해요',sysdate,'java')

COMMIT

SELECT * FROM board;

 

게시물 리스트 조회 SQL : 최신글 등록순 → board와 community_member 

board table     no , title, time_posted (YYYY. MM. DD ) , hits

join 조건  작성자 회원 id가 일치하면 
   
community_member table 
name 
           

●  ANSI SQL 

SELECT b.no,b.title,m.name,TO_CHAR(time_posted,'YYYY.MM.DD') as time_posted,b.hits
FROM community_member m
INNER JOIN board b ON m.id=b.id
ORDER BY b.no DESC

 

●  Oracle 

SELECT b.no,b.title,m.name,TO_CHAR(time_posted,'YYYY.MM.DD') as time_posted,b.hits
FROM community_member m, board b
WHERE	m.id=b.id
ORDER BY b.no DESC

 

Inner Join ?

 

 

 

 

 

ORDER BY ?