iridescent

• jdbc api
  • 실제 수행코드는 정의되어 있지 않다. 
  • 매소드 호출 방식만 정의해 놨다. 
  • DB 서버마다 처리하는 방식이 다르다. 
    • 인터페이스로
    • 인터페이스의 자식 클래스를 jdbc driver로 대신하는 것
    • 알맹이는 드라이버가 제공 
  • 공통적인건 class
  • 코드는 같은데 driver만 다른거 준비하면 된다. 
  • 객체 생성을 대신해주는 매소드 (팩토리 매소드)
  • 인터페이스 활용이 많이 돋보이는 기술

• executeQuery()
  • resultset으로 반환함
    • next() 행 단위로 옮겨서 여러 값 추출

주요 JDBC API

• statement -> (확장) preparedStatement
• DriverManger 
  • DB서버에 대한 접속이 선행되어야 한다. drivermanager.getconnection

1. jdbc 로딩

   Class.forName("com.mysql.cj.jdbc.Driver");

2. jdbc url ( Uniform Resource Locator ) -> 위치 알릴 때 단일화

   "jdbc:mysql://localhost:3306/sqldb?characterEncoding=UTF-8&serverTimezone=UTC";​

   • 어떤 DB 서버를 어떤 driver에 연결할지 
   • jdbc:어떤 서버://[호스트 네임][:port]/dbname[?param1 = value1][&param2=value2]... → mysql
3. user id, passwd 설정
4. 실제 연결 DriverManager.getConnection (내부적으로 연결)

   Connection conn = DriverManager.getConnection(url, user, passwd);​

데이터 읽기

• DriverManager

  Class.forName("com.mysql.cj.jdbc.Driver");​

• Connection

  String url = 
  			"jdbc:mysql://localhost:3306/sqldb?characterEncoding=UTF-8&serverTimezone=UTC";
  			String user = "root";
  			String passwd = "1234";
  			Connection conn = DriverManager.getConnection(url, user, passwd);​

• Statement
  • statement의 팩토리 메소드를 connection이 가지고 있음
  • 객체 생성을 대신해주는 일반 메소드: 팩토리 메소드

    Statement stmt = conn.createStatement()

  • statement 객체를 가지고 올 수 있다. 
• ResultSet
  • 추출된 데이터 집합을 하나씩 읽어 옴
  • ReultSet rs = stmt.excuteQurey("SELECT ename, sal FROM emp);
  • resultSet 객체에서 값 들고오기
    • next() : 첫번째 값 가르킴
    • 최초의 집합에서 0번째 줄 가리키고 있음
• 커넥션 반환하기
  • rs.close()
  • stmt.close()
  • conn.close()
    • 다른 프로그램의 connection 요청 못 받을 수도 있음, connection꺼줘야 한다.

package mysql.level1;

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


public class SelectEmp {
	public static void main(String[] args) {
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		}
		catch (ClassNotFoundException cnfe) {
			System.out.println("해당 클래스를 찾을 수 없습니다." + cnfe.getMessage());
		}
		String url = "jdbc:mysql://localhost:3306/sqldb?characterEncoding=UTF-8&serverTimezone=UTC";
		String user = "root";
		String passwd = "1234";
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = DriverManager.getConnection(url, user, passwd);
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select ename, sal, hiredate from emp");
			while(rs.next()) {
				System.out.println(rs.getString("ename") + ","+rs.getDate("hiredate") + "," + rs.getInt("sal"));
			}
		}catch(SQLException se1) {
			System.out.println(se1.getMessage());
		}finally {	//	생성된 객체 close
			try {
				if(rs!= null)
					rs.close();
				if(stmt!= null)
					stmt.close();
				if(conn != null)
					conn.close();
			}catch(SQLException se2) {
				System.out.println(se2.getMessage());
			}
		}
	}
}

• conn 인터페이스를 구현하고 있는 자식의 객체를 반환한다.

  conn = DriverManager.getConnection(url, user, passwd);​

   
  • com.mysql.cj.jdbc.ConnectionImpl
• executeQuery
  • select 반환 값 DB 서버에 저장소에 들어있음
  • 반환값으로 ResultSet 객체 반환
• executeUpdate
  • Int 타입 값 반환
    • insert, delete, update : 건수 반환
    • create, drop : -1반환
  • select 구문 제외한 다른거 사용할 때

select절에 나온 값 두 가지로 할 수 있다. 

• rs.getString("ename") 
• rs.getString(1)

• join
  • 2개의 분산테이블에 있는 여러 컬럼 사용
  • ex) 맥시코에 사는 사원의 이름과 팀장 이름
• 서브 쿼리
  • 두 개의 쿼리로 가능한거면
  • 특정 컬럼 안에서만 사용
  • ex) 맥시코에  사는 사원

셀프 조인

• 실제 하나지만 두 개 테이블로 간주
• 이름 다르게 해줘야함

SQL

mysql

• 이체 작업
  • 뺀 만큼 마이너스, 넣은 만큼 플러스가 되어져야 하는 게 한 덩엉리에 되어져야한다. 
  • 두 작업이 모두 실패하거나, 성공하거나

-> 하나의 트랜잭션으로 묶는다. 

• 무결한 데이터 처리 

• 변화를 반영한다. 
  • 커밋한다. 
  • 최종적으로 반영하겠어. 

• 지금부터 트랜잭션 시작이야라고 알려야함
  • 명시 안하면 DML 명령 수행할 때 마다 자동 커밋됨. (mysql 특징)
  • 명시하면 취소 가능 (올백)
• 되돌려야겠어.
  • 올백

DDL

• 트랜잭션이와 관련 X
• 무조건 커밋 -> 만들어라고하면 만들어짐 

insert (create)

• 없었던 행 추가
• 열이름 생략될 경우, values다음에 나오는 값들의 순서 및 개수가 동일해야함
• 자동으로 증가하는 AUTO_INCREMENT
  • 데이터를 집어넣을 때 없는 컬럼이라고 생각해도 된다. 
  • 숫자 형식만 가능
• values대신에 select명령
  • 이미 만들어진 테이블에 입력
• 에러나면 그 이후 처리 안 함
  • insert ignore 사용하면 무시하고 넘어감
• 이미 존재하는 얘면 수정,

update (new)

• 이미 존재하는 컬럼 변경하는 것
• where절 생략되면 테이블 전체 행 내용 변경 됨
• 올백하면 복구할 수 있음, 

delete

• 테이블만 남겨두고 조건된 행 삭제(내용만 삭제)
• drop table : 테이블 자체 삭제
• where절 없으면 전체 데이터 삭제
• 트랜잭션 사용 가능 : 로그 작업
• truncate : 트랜잭션 적용 X
  • 삭제되면 끝, 복구 불가
  • 테이블 구조 남겨놓고 싶으면 효율적

with절

• CTE를 표현하기 위한 구문
  • 일시적인 뷰 ( 수행하는 동안만 )
    • 뷰 
      • 저장소를 가지고 있지 않음
      • 자주 쓰는 selet명령이라고 생각하면 됨
      • 만들어놓고 계속 재활용할 수 있음
      • 복잡한 sql을 간략하게 하기 위해서 
      • 보안적
        • 뷰를 통해서만 가져갈수 있도록, 
        • 개발자들은 뷰만 쓸 수 있도록
        • 금융권에서 많이 사용함
      • select * from (뷰); 
        • 뷰를 테이블이라고 생각해도 무방

mysql의 데이터 형식

• 숫자 데이터 형식
  • 정수 & 실수 : DECIMAL
• 문자 데이터 형식
  • char(n) : 고정길이 문자형
    
    • 남은 길이는 공백으로 들어감
    • n : 문자 갯수
  • varchar(n) : 가변길이 문자형
    
    • 남은 공간은 없는 공간으로 취급
• 날짜와 시간 데이터 형식
  • date
  • datetime 
• 텍스트
  • longtext : 큰 텍스트 파일
  • longblob : 동영상 파일, 큰 바이너리 파일

데이터 형식과 형 변환

• 내부 형 변환
• 문자는 0으로 변환
  • select 0 = 'mega2', 결과 : 0
  • select 1 > '2mega', 결과 : 0

• 암시적인 형 변환
  • cats() : 형변환하는 대상 as 타입
  • convert() " 대방 , 타입 길이

테이블 생성

• auto_increment 는 primary key여야 한다.
• 컬럼명 리스트 없으면 넣은 것 그대로 넣겠다. 
  • insert into usertbl values('---', '---', ,,,,)
• 집어 넣을 값 없으면 null

제약조건

• primary key 제약조건 
  • 행들 구별 할 수 있는 식별자 역활
  • primary key로 설정된 얘는 자동으로 index가 설정됨
• foreign key
  • 가지고 있는 값 정해져 있다. 
  • on delect cascade 또는 ON UPDATE CASCADE
    • 참조하는 얘가 사라지면 나도 사라짐
    • ex) 게시판 댓글 
  • add, modify 상요해서 생성
• unique 제약
  • 중복된 값 저장 X
  • null값 허용
    • 입력하지 않아도 되는데, 입력하면 유니크 해야한다.
• check 제약 조건
  • mysql 8.부터 지원
  • 데이터 넣고 변경할 때마다, 이 컬럼에 저장하기 적합한 값인가를 본다. 
• default 제약조건
  • 안 넣으면 자동으로 default값이 됨
  • null이 될 수 밖에 없는 상황에서 default값이 넣어짐
  • 값 넣어야 하는 부분에 안 넣었을 때 

• 데이터 무결성을 위한 제약 조건
  • null 값 허용
    • primay key는 자동으로 not null
    • 공백과 0과 다름

테이블 수정

• 열 추가 add, modify
• 열 삭제 drop
• chang column : 타입 바꾸기
• 제약조건 수정
  • drop primary key
    • 하나의 테이블에 primary key 하나만 가능

• 세로로 묶는거 union
• 가로로 묶는거 join
  • 아무행이나 붙이는 거 : cross join
• 테이블 수 -1 만큼의 조인 조건이 있어야함.
• 두 개 이상인 테이블 하나의 결과 집합으로 만들어 냄
• 직접적인 관계가 있는 경우에 많이 씀

nc join
표준화된 규격

1. from table1 join table2 -> 표준화된 규격

on 

• 조인 조건 자유롭게 줄 수 있다.

• 동일한 얘들끼리 : 등가조인

using

• 등가조인을 할 경우, 그 때 사용하는 테이블이 똑같을 때
• 조인 조건하는 컬럼명이 똑같을 때

inner join

• 일치하는 것 만 
• 일치하는게 없으면 빼는 것
• 기본이라서 생략 많이 함

outer join

• 일치하지 않아도 포함하고 싶을 때, 
• left, right, full
  • left [outer] join
  • mysql -> full outer join 지원 X
    • left outer join, right outer join union해야함
    • left, rigth적으면 자동으로 outer 조인이라고 생각함
  • 왼쪽 테이블 기준 - left join
  • 오른쪽 테이블 기준 - rigth join

select ename, emp.deptno, dname
from emp
left join dept
on emp.DEPTNO = dept.DEPTNO;

select ename, emp.deptno, dname
from emp
right join dept
on emp.DEPTNO = dept.DEPTNO;

full outer join 지원 안 되니깐.

select ename, emp.deptno, dname
from emp
left join dept
on emp.DEPTNO = dept.DEPTNO
UNION
select ename, emp.deptno, dname
from emp
right join dept
on emp.DEPTNO = dept.DEPTNO;

union사용하면 된다. 

cross join 

다 만나는 것

select * from emp join dept on emp.deptno = dept.deptno;

emp 테이블 14명이 deptno의 값을 dept값으로 매칭시키겠다. 

1. emp : 기준테이블
2. dept : 조인 테이블

• dept 테이블 친구들이 emp에 붙는다
• dept 열이 null인 친구 제외 - inner join
• 사용할 때, 어느 테이블에 있는 DEPTNO인지 명시해줘야한다. (DEPTNO가 두 개) 
  • 테이블 별로 각자 들어가는 것

SELECT * FROM emp JOIN dept USING (deptno);

DEPNO 열이 하나만 나온다. 

on

SELECT ename, emp.deptno, dname
FROM emp
JOIN dept
ON emp.deptno = dept.deptno;

어느것에 있는 deptno인지 명시해줘야한다. 

SELECT ename, deptno, dname	--에러난다.
FROM emp
JOIN dept
ON emp.deptno = dept.deptno;

using

SELECT ename, deptno, dname
FROM emp
JOIN dept
USING (deptno);

직원들이 근무하는 부서명과 지역(도시) 정보 출력

using

select ename, dname, city
from emp
join dept
using (deptno)
join locations
using (loc_code);

on

SELECT ename, dname, city
FROM emp e
JOIN dept d
ON e.deptno = d.deptno
JOIN locations l
ON d.loc_code = l.loc_code;

테이블 별칭써도 됨. 

직원들이 근무하는 지역(도시) 정보 출력

SELECT ename, city
FROM emp e
JOIN dept
USING (deptno)
JOIN locations
using(loc_code);

SELECT ename, city
FROM emp e
JOIN dept d
ON e.DEPTNO = d.DEPTNO
JOIN locations l
ON d.LOC_CODE = l.LOC_CODE;

on절만 사용 가능 -> between 사용 (and)

SELECT ename, sal, grade 
FROM emp e
JOIN salgrade s
ON e.sal BETWEEN s.losal and s.hisal;

어떤 범위에 속하는가

SELECT ename, sal, concat(grade,"등급") 등급 
FROM emp e
JOIN salgrade s
ON e.sal >= s.losal AND e.sal <= s.hisal;

GROUP BY

• 모든 단위는 그룹 단위로 나옴
• group by 안 써도 집계 함수 쓸 수 있다.
  • 주로 쓰이는 게 group by라서 그렇지
• 기준이 됐던 칼럼 명 나타내 줘야 함 옆에 - 보기 좋음
• 그룹핑해서 select절에 올 수 있는 행이 제한되어있다.
  • ename(일반 컬럼) 같은 거 못 옴
  • 그룹핑하는데 기준이 된 칼럼,칼럼에 대한 식, 집계함수집계 함수만 올 수 있음
    • 열 단위로 카디널리티 개수가 같아야 해서
• ORDER BY 안 해도 됨 -> 원래 정렬되어 있음

  SELECT deptno, avg(sal) from emp GROUP BY deptno;​

  

• is not null

  select deptno, sum(sal) from emp where deptno is not null group by deptno;

   카디널리티 : 그룹 단위의 행
  1. from 문
  2. where절
  3. group by 절
• count
  • count(*) -> 행의 개수 (null 상관없이 개수 세기)
  • count(특정 칼럼 명) -> null이 아닌 얘들 개수 세줌

• 뒤에 올 수록 서브 칼럼 

  

• select deptno, job, count(*) from emp where deptno is not null group by deptno, job;​

Having절

• 어떤 조건을 충족하지 못하는 건 제외
  • 행 단위로 읽어올 때 -> where절 (행에 대한 조건, from 다음에 실행)
  • 그룹으로 나눈 다음에 그룹에 대한 조건 -> having (그룹에 대한 조건, 그룹 다음에 실행)

ROLLUP

• 각 그룹 단위의 총합계 내줌
  • 소 합계 -> 총합계 

    

    select ifnull(job,'합계') 직무, sum(sal) '급여' from emp group by job with rollup;​

    

  • select job, sum(sal) '급여' from emp group by job with rollup;​
• 보충

  select ifnull(job,"직원 별 합계") 잡, ifnull(deptno,"부서 별 합계"), count(*)
  from emp
  where deptno is not NULL
  group by job, DEPTNO
  with rollup;​

  

Set

• intersect, minus는 mysql에서 지원 안 함. 
• 추출되는 컬럼의 개수가 같아야함
  • select 순서를 다르게 하면, 에러는 안 나지만 논리적 문제가 발생함 (자동으로 안 바꿔줌)
• union

  select ename, hiredate from emp where year(hiredate) = 1981
  UNION
  select ename, hiredate from emp where deptno = 30;​

  

• union all

  select ename, hiredate from emp where year(hiredate) = 1981
  UNION all
  select ename, hiredate from emp where deptno = 30;​

  

DBMS : 데이터의 집합인 데이터베이스를 잘 관리하고 운영하기 위한 시스템 or SW

• 외래키 Foreign key : 데이터 무결성 보장됨
• 기본 키 Primary key : 행 단위 구분 데이터
• 제약조건 
  • 새로운 데이터 들어갈 때는 부모 테이블에 먼저 넣어야함
  • 데이터 삭제 시에는 자식 테이블에서도 지워야 함

mysql 서버는 자동으로 구동 됨 -> 컴터 키면 

• 워크벤치
  • mysql 관리하는 목적으로 쓰임

Sql

• DDL
  • 데이터 정의
• DML
  • 데이터 추가/수정/삭제
• DCL
  • 데이터 관리, 접근 권한 제어

( ※ DML이 SELECT에 있다는 것은 바라보는 관점에 따라 다름,)

• 기본키 정의 

이렇게 2가지 방법을 기본키정의를 할수 있다. 

• 부호
  • 단일 인용 부호를 써야한다.
  • 별명일 경우는 더블 쓸 수도 있다 .

INSERT INTO LOCATIONS VALUES ('A1','SEOUL');

• 한글

한글 설정

• 정보 출력
  • desc 테이블 명;
  • 컴럼 사양들 

• 객체를 통해서 사용 : 변수 
• 메소드 or 함수 -> mysql 내장 함수
  • select user( ) : 현재 내가 어떤 계정으로 접속했는지
  • select database( ); 현재 사용하고 있는 database이름 (use한거)

• DBMS, 서버 환경에 따라 데이터베이스 or 필드명이 대소문자를 구분하기도 함

SELECT문

추출하고자하는 데이터베이스

select now() from emp;

from이 emp이지만, now의 결과 14개를 뽑음

select now() form dual;

dual -> 테이블 내용 중요하지 않고, 함수의 결과 한번만 출력

그래서 from 없애도 나옴 (mysql은 from 생략 가능)

• 테이블 내용 출력하는게 아니면 from 생략 가능

(오라클은 from 없애면 안됨)

select sysdate();

sysdate 오라클에서 추가함

use 구문

• 데이터 베이스 사용하겠다.

( * 시간은 ',' 으로 구분되고, 년월일은 '-'아니면 '/'로 구분됨 )

( null 연산하면 null이기 때문에 연산할 때는 따로 바꿔줘야한다 )

조회

show table status;

• 데이터 베이스에 있는 테이블 정보 조회

DESCRIBE EMP;

• emp 테이블에 열이 무엇이 있는지

WHERE 절

등가 연산자 : '='

관계 연산자

• BETEEN ... AND ( BETEEN A AND B ) (A<B)
  • 데이터가 숫자로 구성되어 있으며 연속된 값

• IN( 값의 리스트 ) 
  • 등가 연산자랑 똑같은데, 비교하려는 값이 2개 이상일 때
  • ex) select * from emp where deptno in(20, 30)
    • select * from emo where deptno=20 or deptno=30와 같다.

• Like (패턴 비교)
  • 패턴 문자 : %, _ 
    • % : 0개 이상의 모든 문자
    • _ : 임의의 한 문자

      where title like 'java%'	java로 시작하는 얘들만
      where title like '%java%'	중간에 java들어가있는 것
      where title like '%java'	끝이 java로 끝나는 얘들만
      where title like 'java_'	java로 시작하는데 뒤에 한 문자만 와야함
      where title like 'java___'	java뒤에 3개가 와야함

    • where tilte = '%java%' : 진짜로 %java% 문자를 찾는다. 

ORDER BY

order by 안 쓰면 테이블이 가지고있는 순서에 의해서 뽑아짐 (순서는 보장할 수 없다.)

오늘날짜가 더 큰 값 ( 1970년 1월 1일 기준)

• desc 내림차순
• aesc 오름차순 (디폴트)

DISTINCT

• SELECT distinct : 중복되는 것 골라서 하나만 보여줌
• distinct all : 중복되는 행이 발견해도 다 표현한다.
• 모든 컴럼이 모두 같을 때, 처리된다. (중복 처리하는 것) 
  • select distinct job, deptno from emp;

LIMIT IN

• LIMIT IN : 위에서 부터 몇 개
• as : 별칭
  • select now() as 현재시간;
  • select now() as "현재 시간"; : ""씀으로써 공백까지