1. OracleDB 설정 및 SQL 기본 설정 : 230130

0) 오라클DB 설정

• sql developer에서 id 입력하고
• pwd 입력하고
• 서비스 이름 : id, pwd, xepdb1 입력 후 테스트 버튼 누르고 접속 버튼 누르기

1) SQL :

• DDL(CREATE, ALTER, DROP) : 테이블
• DML(INSERT, SELECT, UPDATE, DELETE) : CRUD(주로 쓴다!)
• DCL : 허가, 허락, 취소 등

• 테이블 정의하기 : 데이터 구조 정의하기 : 개념상의 데이터
• 오라클에서는 정수/실수를 따로 구분하지 않는다.(NUMBER)
• 오라클에서는 문자와 문자열을 따로 구분하지 않는다.(VARCHAR2)
• 문자는 ‘‘로 쓰고 ““는 아니다.

• 오라클DB의 데이터 타입 :

  • CHAR : 바이트 단위이다. 데이터 크기를 4000바이트까지 쓸 수 있다. 고정된 길이로 된 데이터 타입으로 검색하면, 검색이 빠르다.

  • VARCHAR2 : CHAR보다 큰 단위이다. 하지만, 검색 속도는 느리다.

  • NVARCHAR2 :영어도 한글도 모두 2바이트로 인식하게 해준다.(원래는 영어가 1바이트, 한글이 3바이트 였다. )

  • DATE 형식(BC 단위의 날짜 형)
  • TIMESTAMP 형식(시간 까지 포함한 날짜이다. 현재 시간을 출력 가능 )

  • 다른 나라와의 주문 날짜를 맞추려면 서버에 타임존 설정을 맞춰야 한다.

  • LONG : 테이블에서 한 칼럼만 전송할 파일을 2기가까지만 설정할 수 있다.(예전에는 이미지 파일에서 사용했고, 제한적이다.)

  • CLOB : 대용량 텍스트 타입(4000바이트 이상!)

  • NCLOB : 전송할 파일이 최대 4기가 바이트이고 여러 칼럼에도 쓸 수 있다.

• 실습 코드 :

// 테이블 생성 
CREATE TABLE MEMBER3144(
    ID  NUMBER,
    NAME NVARCHAR2(100),
    PWD NVARCHAR2(100),
    EMAIL   VARCHAR2(100)
)

// 테이블 삭제
DROP TABLE MEMBER3144;

// INSERT INTO MEMBER3144(ID, NAME) VALUES IN(2,'가나다'); 

// 문자열 길이 출력
SELECT LENGHB('가나다') FROM MEMBER3144; 

// 퀴즈에 대한 정답 1
NUMBER
NVARCHAR2(1000)			// 한국에서 운용하므로 NVARCHAR2이다. 
NVARCHAR2(100)
CLOB
TIMESTAMP 				// DATE가 아니라 TIMESTAMP이다.
NUMBER
VARCHAR				// 파일은 디스크에 저장하고 경로만 저장한다! 그래서 NCLOB이 아니라 VARCHAR이다. 보통 영어로 파일명을 쓰므로 

// 퀴즈에 대한 정답 2 : COMMENT
NUMBER
CLOB
DATE
VARCHAR2
VARCHAR2

• 웹서버와 파일스토리지 그리고 DBMS와의 관계 :
  • 파일 용량이 커서 DBMS에 파일을 저장하는 것은 비효율적이고 파일 경로만 저장한다.
  • 사용자 입장에서는 웹서버를 하나만 있다고 알고 있게 하고 실제로는 웹서버가 여러개로 있으며 합쳐져서 로드가 된다. 파일 경로를 DBMS에서 찾아서 파일스토리지에 가기 전 중간 공간에서 경로를 비교해 알맞은 경로에서 파일을 찾아 사용자에게 반환해준다.

2. OracleDB 개념, JDBC 개념 : 230131

1) SQL 기본(암기) :

• COMMIT, ROLLBACK : commit을 해야 데이터가 업데이트된다. Rollback은 데이터를 수정한 것을 다시 되돌릴 수 있다.

• 데이터 삽입 : INSERT INTO MEMBER(ID, PWD, NICNAME) VALUES(2, 22, ‘suesue’);

• 데이터 수정 : UPDATE MEMBER SET NICNAME=’NEWNEW’;

• 데이터 삭제 : DELETE MEMBER WHERE ID=22;

• 칼럼을 별명으로 사용 가능 : select id, name, pwd as password from member;

2) JDBC

• JDBC : 남이 만든 플랫폼을 중간역할을 통해 문제를 해결하게 해준다.

• 원래는 MS사의 odbc로부터 jdbc가 만들어진 것이다.

• DriverManager, Connection, Statement, Resultset 각각의 역할 이해!

3) 간단히 배포하는 방법 :

• 이클립스에서 배포하고 싶은 코드를 우측 클릭하여 export하면, java 탭에서 jar파일로 만들 수 있다.

4) 외부파일을 불러들여 값으로 클래스 정보를 받아와서 객체(인스턴스) 생성하기 :

• xepdb1 개념 : CDB, PDB, xepdb 설명!

• res/setting.txt

	com.newlecture.web.entity.Menu

• Program.java

import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.Scanner;

import com.newlecture.web.entity.Menu;

public class Program {

	public static void main(String[] args) throws IOException, InstantiationException, IllegalAccessException, ClassNotFoundException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException {
		
		Menu menu = new Menu();												// Class.forName과 같은 역할이다.
		menu.setName("아메리카노");
		System.out.println(menu);
		
		FileInputStream fis = new FileInputStream("res/setting.txt");
		Scanner scan = new Scanner(fis);
		
		String clsName = scan.nextLine();				// clsName은 값인데 이것을 객체로 만드는 방법은 new로 하면 안된다.
																					// 해결방법은? Class.forName으로 만든다! 
		
		Menu menu1 = (Menu) Class.forName(clsName).newInstance();			// 값을 객체로 만들기! 설명!!
		
		//Menu menu1 = (Menu) Class.forName(clsName).getDeclaredConstructor().newInstance();
		
		
		menu1.setName("카페라떼");
		System.out.println(menu1);
		
		
		scan.close();
		fis.close();
	}

}

5) JDBC로 오라클DB 연동시키기 :

package com.newlecture.web.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCProgram {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
//		DriverManager			// Class.forName :  new와 같은 역할이다. 뭐 입력 받을지 모르기 때문에 new로 쓸수 있는 것 아니라서 Class.forName이다.  
//		Connection				// 드라이버와 연결
//		Statement					// 업데이트 하려는 쿼리문이 여기 안에 들어 있다. 
//		Resultset					// 쿼리의 결과를 확인하기 위해서 사용된다. 
		
		Class.forName("oracle.jdbc.driver.OraleDriver");			// 스태틱 생성자와 같이 바로 불러낼 수 있다. 설명!
		
		// https://service/a/b/c와 같이 http라는 프로토콜에 service 객체에 a,b,c 순으로 파일이 이동된다.
		// thin 도 약속되어있는 명칭이다.
		String url = "jdbc:oracle:thin:@oracle.newlecture.com:1521/xepdb1";		// xepdb1는 SEED로부터 복제된 샘플(플러그된 것에서 복제 )이다. 
																																					// 조작을 아무렇게 해도 된다!
		
		Connection con = DriverManager.getConnection(url, "NEWLEC", "rland");		// import java.sql.DriverManager;라서 DriverManager를 ojdbc8에서 가져온다.
		// DriverManager는 클래스이고 서버와 드라이버를 연결시켜주고 Connection이라는 클래스를 생성해준다.(연결 생성) 		// Connection은 클래스이고 con은 객체이다.
																											
		Statement st = con.createStatement();												// 그래서, 그 con 객체에서 createStatement를 실행시켜서 Statement클래스를  가져와서 
																									//실행시킨다. Statement는 sql 구문을 가지고 있다. 
		ResultSet rs = st.executeQuery("select * from member");
		
		
		{
			rs.next();
			String nicName = rs.getString("nicname");
			System.out.println(nicName);
		}
		
		
		{
			rs.next();
			String nicName = rs.getString("nicname");
			System.out.println(nicName);
		}		
		
		{
			rs.next();
			String nicName = rs.getString("nicname");
			System.out.println(nicName);
		}
		
		System.out.println(con.isClosed());
		
		con.close();			// 습관적으로 닫아주자!  나중엔 커넥션 에러가 발생한다!! 
		
	}

}

• 실행 결과! :

	1004
	□■□■□■
	ㅇㅇ
	false

3. OracleDB, 연산 문법 : 230201

1) where 조건절 :

• Id가 100보다 큰 경우 :
  • select * from member where id > 100;

2) 산술연산

• SELECT는 무조건 FROM이 무조건 필요하다!

• SELECT ID+1 ID FROM MEMBER WHERE ID=1;

• 더미 테이블 : 레코드 하나짜리 테이블로 계산하고 싶을 때, 사용한다.

  • SELECT 3+4 SUM FROM DUAL;

• +는 숫자 형식만 더할 수 있다.(3, ‘3’)
• || : 문자도 더하여 문자열로 만들수 있다.

3) 산술연산

• 비교 연산자 :
  • = : 같다의 의미
  • !=, ^=, <> : 다르다의 의미

• IS NULL, IS NOT NULL : 널값을 비교하는 연산자!
  • select * from member where name is null;
  • select * from member where name is not null;
  • ex) select * from member where name is not null;

4) 관계 연산자 :

• NOT, AND, OR, BETWEEEN, IN

• AND :
  • SELECT * FROM NOTICE WHERE 0<=HIT AND HIT<=2;(불편하다.)
• BETWEEN :
  • SELECT * FROM NOTICE WHERE BETWEEN 0 AND 2;
• IN :
  • select * from member where id in(11,19,24);
• NOT IN :
  • select * from member where id not in(11,19,24);

5) 패턴 연산자 :

• Like, %, _

• 이름 중에 영을 포함하는 이름을 조회하시오.
  • SELECT * FROM MEMBER WHERE NAME LIKE ‘%영%’;

• %는 자리수가 한정이 없다. 그래서 자리수를 제한 걸어주는 방법 : _

  • 언더바 1개가 문자열 자리수 1개를 의미한다.
  • SELECT * FROM MEMBER WHERE NAME LIKE ‘_영’;

• 실습 :

  • 회원 검색 프로그램 만들기 :

  • 회원, 이름, 입력 : DB라는 테이블이다.

  • 칼럼 : ID, TITLE, NICNAME

  • sql문 짜서 jdbc를 통해서 자바에서 출력하기!

	[회원 검색 프로그램]
	
	회원 이름 입력 : SQL -> rs
	
	목록출력
	ID NAME NICNAME

6) 정규식 표현법(regular expression - regexp) :

• 범용적으로 사용하고 JSON와같은 데이터 타입에서 많이 쓰인다.

• 포맷을 유지하고 내용의 값의 형식도 일치해야한다.

• /^01[0|1|2|6|8|9]-\d{3,4}-\d{4}$/;

4. SQL, 정규 표현식 : 230202

1) 정규표현식 :

• 정규식은 크롤링 할 때, 많이 사용한다. 예를 들어, 네이버에서 특정 문구가 들어가는 것을 찾을 때, 사용한다.

• 전화번호 패턴 검색 :

• 예시 1 : 010-1234-1111이에요.
  • 정규식 : 010-\d\d\d\d-\d\d\d\d
  • 결과 : 010-1234-1111
• 예시 2 : 010-124-1111이에요.
  • 정규식 : 01[012689]-\d{3,4}-\d{4}
  • 결과 : 010-124-1111
• 예시 3 : 010-123-12321
  • 정규식 : ^01[01689]-\d{3,4}-\d{4}$
  • 결과 : No Results
• ` : 이것은 검사할 때, 이것부터 시작을 의미한다.
• & : 이것은 검사할 때, 마지막으로 이것이 있어야함을 의미한다.

• Email 패턴 검색 :

  • 예시 : newlec@newlecture.com
  • 정규식 : .+@.+.(com|net|org)
  • 결과 : newlec@newlecture.com
• . : 아무 문자 1개를 대신한다.
• + : 문자 1개 이상부터 조건 찾아냄.
• () : 여러 개의 문자열을 묶어낼 때 사용한다. 묶어낼때 |를 이용한다.
  • (abc){2} 이렇게 사용하면 abcabc가 된다.

4. 오라클DB 응용 : 230203

1) 프로젝트 피드백 :

• 내가 만들고 싶어하는 기능을 만들어보자!! 포트폴리오에 목숨을 걸지마라!!
• 인공지능은 알려주기만 하고 판단은 사람의 몫이다.(ChatGPT에서 이용)

2) 정규 표현식을 DB에서 사용하는 방법, 이것이 더 안전한 이유 :

• REGEXP_LIKE
• REGEXP_REPLACE
• 1) sql문의 where 절에서 정규표현식의 조건 판단에 사용할 수 있다 : SELECT * FROM MEMBER WHERe NAME LIKE '%모%';
• 2) sql문의 where 절에서 정규표현식의 조건 판단에 사용할 수 있다 : SELECT * FROM MEMBER WHERE REGEXP_LIKE(NAME, '^.*모.*&');, ` SELECT * FROM MEMBER WHERE REGEXP_LIKE(NAME, ‘.모.’);`
• 2)가 더 강력하고 안전하다? 왜? 안전하는 이유는? 입력값으로 ‘모’대신해서 쿼리문을 집어 넣는데 쿼리문 안에 쿼리문이 또 들어갈 수 있다. 또 다른 쿼리가 들어가는 것은 문제가 있다. 왜냐하면, 추가로 들어오는 쿼리가 Delete 로 지워버리는 쿼리가 들어오면 경우가 있기 때문에 문제가 발생한다!
• 그래서, 1)에서 오류가 발생하지 않고 돌아가게 하는 것이 문제가 발생한다. SQL Injection 관련하여 문제가 발생해서 REGEXP_LiKE는 범위를 벗어나는 경우가 어렵다. 에러가 발생하므로 더 안전하다!

3) 페이징 : 페이지를 나눠주는 경우

• oracle에서는 페이저를 구현하는 부분이 없다. mysql이나 mariaDB에서는 limit이라는 명령어로 가능하다.
• 따라서, 오라클에서는 ROWNUM을 이용하여 조작질을 한다.
• 실습 코드 : SELECT ROWNUM, MEMBER.* FROM MEMBER WHERE ROWNUM BETWEEN 1 AND 5;
• 하지만, 이렇게 하여 페이징하면, 2Page인 6 AND 10는 출력할 수 없다.

4) 페이징 문제 해결법 :

• FROM 절에는 격자형의 데이터가 들어오면 된다. 그래서, 우리는 서브쿼리를 이용한다!
• 코드 수정 : SELECT * FROM (SELECT ROWNUM, MEMBER.* FROM MEMBER) WHERE ROWNUM BETWEEN 6 AND 10; 이렇게 해도 출력되지 않는다.
• 이렇게 만들면, 기존의 ROWNUM과 새로 만들어지는 ROWNUM이 충돌해서 별칭으로 NUM을 써줘야 한다.
• SELECT * FROM (SELECT ROWNUM NUM, MEMBER.* FROM MEMBER) WHERE NUM BETWEEN 6 AND 10;

5) 레코드의 중복 제거하는 방법 :

• SELECT DISTINCT PWD FROM MEMBER;
• 서브쿼리에서 많이 사용한다.
• 로우가 중복되면 데이터 결함이 존재하는 것이다.

6) SQL의 데이터 타입별 함수 :

• 정수, 문자열, 날짜, 변환, NULL 관련한 함수가 있다.
• SUBSTR, LENTH 등등이 있는데 어디 정리해놓기도 뭐해서
• 구글링해서 찾기! SQL의 데이터 타입별 함수 : 블로그 참고

7) 집계 함수 :

• SQL문 작성 순서 : SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY
• SQL문 실행 순서 : FROM - WHERE - GROUP BY - HAVING - ORDER BY - SELECT

8) ORDER BY :

• SELECT * FROM MEMBER ORDER BY ID; 이름이 같은 경우 2차 정렬이 필요하다!
• ORDER BY : SELECT * FROM MEMBER ORDER BY NAME ASC,NICNAME DESC; 이름이 같은 경우 2차 정렬이 필요하다! 기본 정렬은 오름차순이다.

9) 문제1의 실습 코드 :

SELECT * FROM MEMBER WHERE NAME LIKE '박%'ORDER BY ID ASC;

10) COUNT 집계 함수 :

• null이 들어가지 않는 데이터칼럼을 찾아서 집계해야 한다.
• 그래서, 데이터 결함이 없는 칼럼인 ID칼럼을 선택해서 COUNT(*)보다는 COUNT(ID)을 사용한다.

11) GROUP BY :

• 기본적으로 사용하는 방법 :
  • SELECT AGE, COUNT(ID) FROM MEMBER GROUP BY AGE;
  • SELECT AGE, COUNT(ID) FROM MEMBER GROUP BY AGE ORDER BY AGE DESC;

• GROUP BY 절은 계산이 가능하다!! 나머지연산이나 나누기 가능 : SELECT AGE/10, COUNT(ID) FROM MEMBER GROUP BY AGE ORDER BY AGE/10 DESC;

• 그래서 계산을 해서 나이대별로 그룹을 만들어보자!(10대, 20대, 30대 등) : 숙제!!

SELECT TRUNC(AGE,-1) AGE, COUNT(ID) COUNT 
FROM MEMBER 
GROUP BY TRUNC(AGE,-1)
ORDER BY AGE ASC;

• 집계 될 때, 기준이 되는 칼럼은 출력할 수 있지만 다른 칼럼을 쓸 수 없다.

12) 데이터 분석 방법 :

• 데이터 분석할 때, 쿼리문이 가장 중요하게 생각된다. 엑셀의 그래프 그리는 것과 같다.
• 전체 평균나이 : SELECT AVG(AGE) FROM MEMBER;
• 하지만, 데이터 분석에서 멤버 전체에 대해서 평균 나이를 구하는 경우가 없다.

13) 집계가 이용되는 경우의 예시 :

• 카테고리별 상품수 조회
• 멤버별 게시글 수 조회

14) 문제2의 실습 코드 :

• 아이디가 홀수인 회원의 평균 나이?
• MOD 연산자가 이용된다. 이것 이외의 SQL 연산자 공부

SELECT AVG(AGE) FROM MEMBER WHERE MOD(ID,2) != 0;

15) 참조 칼럼 :

• 연결될 데이터 테이블과 관련된 칼럼을 참조 칼럼이라고 한다. 보통 MEMBER의 NICNAME(또는 ID)과 NOTICE의 WRITER_ID가 연결된다.

16) 이외의 칼럼 :

• REGDATE : 인자가 없는 함수는 소괄호를 안쓰고 바로 사용할 수 있다. 즉, REGDATE 칼럼에서 SYSTIMESTAMP(현재 날짜와 시간을 출력)가 이렇게 이용한다.
• HIT : HIT 칼럼을 기본값으로 설정했다.

17) 시퀀스 개념 :

• 시퀀스를 설정하면, 레코드에 데이터를 넣지 않아도 저절로 값이 순차적으로 증가하여 출력된다. 그래서 보통 ID 칼럼에 많이 이용된다.

18) 서브쿼리 이용하는 과정 :

SELECT 
	(SELECT NICNAME FROM MEMBER WHERE ID=WRITER_ID) WRITER_ID, -- 부모 자식간에는 서브 쿼리가 성능면에서 매우 안 좋다! 그래서 다른방식을 이용해야 한다.
	COUNT(ID) COUNT
	FROM NOTICE
	GROUP BY WRITER_ID;     -- 멤버의 ID별로 그룹짓는 것은 왜 바로 안되는가? NOTICE에서 MEMBER를 조회하기 때문에 서브쿼리를 이용한다!
	

19) HAVING :

• 집계함수 이후에 조건을 정해주기 위해서 사용된다.

• 하지만, HAVING을 사용하면, 역순으로는 정렬이 불가능하다?? 다른 방식이 필요하다??

SELECT
    (SELECT NICNAME FROM MEMBER WHERE ID=WRITER_ID) WRITER_ID,  
    COUNT(ID) COUNT
    FROM NOTICE
    GROUP BY WRITER_ID
    HAVING COUNT(ID) >= 2
    ORDER BY COUNT DESC;