[BACK-END]/DB

데이터베이스 기초 요약 및 JDBC

지기음 2023. 2. 12. 17:34

데이터 검색

RDBMS → 관계형 데이터베이스

  • 데이터를 테이블 단위로 관리
  • 중복을 최소화
  • 여러 테이블에 분산되어 있는 데이터를 검색 시 테이브 간의 관계를 이용하여 필요한 데이터를 검색
  • row(행) column(열)

SQL → Structured Query Language

  • 데이터베이스에 있는 정보를 사용할 수 있도록 지원하는 언어
  • 모든 DBMS에서 사용가능
  • Query의 대소문자는 구분하지 않음 (MySQL은 데이터의 대소문자도 구분하지 않는다)

DDL

  • 데이터베이스 객체의 구조를 정의

create 데이터 베이스 객체 생성

drop 데이터베이스 객체 삭제
alter 기존에 전재하는 데이터베이스 객체 수정

create database 데이터베이스명 default character set utf8mb4 collation utf8mb4_general_ci;

utf8mb4 —→ 이모지 문자까지 처리하는 명

collation —> 데이터 정렬할떄 규칙임 (한글로 정렬하려면 특정한 키워드 필요함)

alter database 데이터베이스명 default character set utf8mb4 collation utf8mb4_general_ci;

drop database 데이터베이스명

use 데이터베이스명

DML

  • 데이터 조작어 (CRUD)

insert 입력

select 조회
udpate 수정
delete 삭제

select → select 와 from은 필수요소

#모든 사원의 사번,이름,급여,급여*12검색
select employ_id as 사번, first_name "이 름", salary as "급여", salary*12 "연봉"
from employees;
#ifnull 함수
#IFNULL(ex1,ex2) ex1이 널이면 ex2를 반환

#고액연봉 저액연봉 등등 (case when end)
select employ_id, first_name, salary,
			case when salary>15000 then '고액'
					 when salary>8000 then '평균'
					 else '저액연봉'
			end "연봉등급"
from employees;

#where in 
select employee_id, first_name, department_id
from employees
where department_id in(50,60,70);

#where not in 
select employee_id, first_name, department_id
from employees
where department_id not in(50,60,70);

#between and 
select employee_id, first_name, slary
from employees
where salary between 6000 and 10000;

중요한점 !!!!!!!!!!!11 xx = null 이라고 비교하면 안됨

where department_id is null;
where department_id is not null;

#LIKE (wild card :%,_)
#이름에 x가 들어간 사원
select *
from employees
where first_name like "%x%";

#이름 끝에서 3번째 자리에 x가 들어간 사원 
select *
from employees
where first_name like "%x__";

!!and or not 등 논리 연산시 null이 끼면 무조건 NULL 도출되는 것음 아님

ex ) null && false → false

   null || true → true;

정렬

order by

desc → 내림차순

#부서별  정렬 , 급여 순 내림차순
order by department_id, salary desc;

INSERT

insert into ssafy_member (userid ......)
values (.....);

insert into ssafy_member(...)
values (),(),();

UPDATE (주의 : where절을 생략하면 모든 데이터가 바뀐다)

update ssafy_member
set userpwd =1111
where userid = "1234";

DELETE (주의 : where절을 생략하면 모든 데이터가 바뀐다.)

delete from ssafy_member
where userid ="1234"

DCL

  • 데이터 제어어
revoke 권한을 취득
grant 권한을 부여

TCL (transaction control languager)

  • 트랜잭션 제어어

commit 실행한 쿼리를 최종적으로 적용

rollback 실행한 쿼리를 마지막 커밋 전으로 최소시켜 데이터를 복구  
commit 실행한 퀴리를 최종적으로 적용  

savepoint f1;

rollback to f1;

내장함수

숫자관련 함수

abs() 절대값

ceiling() 올림
floor() 내림
round(숫자, 자리수) 자리수까지 반올림(1의자리가 0) 음수가 위로
truncate(숫자,자리수) 버림
pow(x,y) x의 y승
mod(분자, 분모) 나머지
greatest(1,2,3,…) 가장 큰수
least(1,2,3,4) 작은 수 반환

문자관련 함수

ascii() 아스키코드값 리턴

concat(1,2,3) 문자열 결함
insert(문자열,시작위치, 길이, 새로운 문자열) 새로운 문자열로 대치
replace(문자열,기존문자열,바뀔문자열) 문자열 변
insrt(문자열, 찾는문자열) 문자열 중 찾는 문자열의 위치 값을 리턴
mid(문자열, 시작위치, 개수) 문자열 중 시작위치부터 개수만큼 리턴
substring(문자열,시작위치,개수) 문자열 중 시작위치부터 개수만큼 리턴
ltrim(문자열) ,rtrim(),trim() 왼쪽 공백제거 , 오른쪽 공백제거, 양쪽 모두 공백 제거
lcase(), lower() 소문자
ucase(), upper() 대문자
left(문자열,개수) 문자열 중 왼쪽에서 개수만큼 추출
right(문자열,개수) 오른쪽에서 개수만큼 추출
reverse() 반대로
length() 문자열의 길

날짜 관련 함수

now(),current_timestamp() 현재

sysdate() 함수가 호출될 때 시간
curdate(), current_date() 현재 날짜
curtime(),current_time() 현재 시간
Date_Add(날짜, interval 기준값) 날짜 + 기준 값 ,second, hoiur, day
date_sub(날짜, interval 기준값) 날짜 - 기준 값
year(),month() 연도,월
monthname()dayname() 영어로 리턴
dayofmonth() 날짜의 월별 일자 리턴
dayofweek() 주별 일자 리턴(일요일 1)
weekday() 월요일 0
dayofyear() 1년 중 몇일
week() 일년 중 몇번 째 주
from_days(날수) 날수많큼 경과한 시간 리턴
to_days(날짜) 날짜까지의 일수 리턴
date_formate(날짜,’형식’) 날짜 형식

논리관련 함수

IF(논리식,값1,값2) 참: 값1 거짓 : 값2

IFNULL(값1,값2) 값1이 NULL이면 값2, NULL이 아니면 값1
NULLIF(값1,값2) 값1==값2 NULL 아니면 값1

집계함수

  • 하나 이상의 행을 그룹으로 묶어 연산

sum, avg,count,max,min

group by : 테이블의 행을 그룹으로 묶음

실행 순서

  1. from
  2. where
  3. group by
  4. having
  5. select
  6. order by
select department_id,sum(salary), avg(salary)
from employees;
#-----> XXXXXXXXXXXXXXXXXXXXXXx department_id는 다중행, 집계함수는 단일 행이라 안됨 
#group by로 묶어주어야 함 
#group by에서의 조건은 having에 걸어줘야함

JDBC

JDBC란? SQL문을 실행할 수 있는 함수 호출 ㅇ니터페이스

특징

  • DBMS 종류에 독립적인 자바 프로그래밍 가능
  • 데이터베이스가 달라지더라도 동일한 API사용 가능 (드라이버 및 URL만 수정)
  • 자바가 가지는 플랫폼에 독립적이라는 특성과 DBMS에 독립적인 특성을 가진다.

기능

  • 데이터베이스에 연결
  • SQL문장을 전송
  • 처리

Driver, connection

Class.forname(DRIVER);
DriverManager.getConnection(URL,DB_ID,DB_PASS);
//디폴티로 setAutoCommit(true)
// false로 하면 sql문 수행할 때마다 명시적으로 commit() 해야함

Statement

public boolean execute(String sql) throws SQLException
//DML , 쿼리 DDL 실행 
public ResultSet excuteQuery(String sql) throws SQLException
//Select 처리할 떄 씀 
public int executeUpdate(String sql) throws SQLExcepion
//주로 DML등을 수행

PrepaeredStatement도 스테이트먼트랑 동일 but

conn = DBConnection.getConnection();
			String sql = "insert into person (name,age,job) values (?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, person.getName());
			pstmt.setInt(2, person.getAge());
			pstmt.setString(3, person.getJob());
int rowCnt = pstmt.excuteUpdate();

// SQL insection 방

ResultSet → 결과값 처리

ResultSet rs;
conn = DBConnection.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.excuteQuery();
while(rs.next()){}
//getInt() ,,,,

JDBC 개발 순서

1. JDBC 드라이버와 로딩 (JVM안으로 특정 드라이버 클래스 적재)

Class.forName(url);

2. DBMS와 연결

  • 드라이버매니저 클래스를 이용하여 URL형태로 주어진 데이터베이스 접속
Connection conn = DriverManager.getConnection(URL,id,pass);

3. SQL 실행 준비

Statememt stmt = conn.createStatement();
PrepaeredStatement pstmt = conn.prepareStatement(SQL);

4. SQL 실행

//DML
int cnt = stmt.excuteUpdate(SQL);
//select
ResultSet rs = stmt.excuteQuery(SQL);

pstmt.setInt(1,4); 
//DML
int cnt = pstmt.excuteUpdate();
//select 
ResultSet rs = pstmt.excuteQuery();

//결과 값 가져오기
rs.next();
rs.getInt(index or name);

5. DBMS 연결 끊기

  • connection은 상당한 overhead를 가져온다. 따라서 최적화된 상태를 유지하기 위해서는 반드시 connection을 닫아주어야 한다.

 

후기 : 노션에 정리한 걸 블로그에 옮기니 서식이 망가진다.ㅜㅜ

저작자표시

'[BACK-END]/DB'의 다른글

  • 현재글데이터베이스 기초 요약 및 JDBC
댓글

티스토리툴바