on
내장함수 & 파일로드 & JDBC
내장함수 & 파일로드 & JDBC
SQLday_5 정리 (2021.11.24 수요일)
내장 함수
수학 함수
ROUND(값, 자리수) 반올림한 값을 구하는 함수 자리수 아래에서 반올림하여 자리수까지 출력 양수 : 소수점 오른쪽 자릿수(소수점 이하) 음수 : 소수점 왼쪽 자릿수(1의 자리부터) 양수 예 : ROUND(3.456, 1) -> 3.500
ROUND예시
-- ROUND -- 고객별 평균 주문액을 출력 select clientNo, round(avg(bookPrice * bsQty)) as '평균주문액', round(avg(bookPrice * bsQty), 0) as '1의 자리까지 출력', round(avg(bookPrice * bsQty), -1) as '10의 자리까지 출력', round(avg(bookPrice * bsQty), -2) as '100의 자리까지 출력', round(avg(bookPrice * bsQty), -3) as '1000의 자리까지 출력' from book, booksale where book.bookNo = booksale.bookno group by clientNo; -- 평균 주문액 : 88333 -- 1의 자리까지 : 88333 -- 10의 자리까지 : 88330 -- 100의 자리까지 : 88300 -- 1000의 자리까지 : 88000
순위 출력 함수
RANK() 값의 순위 반환(동일 순위 개수만큼 증가) 순위 : 1 1 3 4 5 6 6 8 9 ....
DENSE_RANK() 값의 순위 반환(동일 순위 상관없이 1증가) 순위 : 1 1 2 3 4 5 5 6 7 ....
ROW_NUMBER() 행의 순위 증가
순위 출력 함수 예시
-- 순위 출력 함수 -- RANK(), DENSE_RANK(), ROW_NUMBER() select bookPrice, rank() over(order by bookPrice desc) "rank", dense_rank() over(order by bookPrice desc) "dense_rank", row_number() over(order by bookPrice desc) "row_number" from book;
문자함수
REPLACE() 문자열을 치환(대체)하는 함수 실제 데이터는 변경되지 않고 출력만 바꿔서 해줌
CHAR_LENGTH() 문자열 길이(글자 수)를 반환 글자와 공백 모두 포함 자바 프로그래밍 -> 8 반환, 안드로이드 프로그래밍 -> 11 반환
LENGTH() 바이트 수 utf-8인 경우 한글은 3바이트 유니코드는 한글 2바이트 자바 프로그래밍 -> 22 반환됨(한글은 3바이트, 공백은 1바이트) HTML & CSS -> 10 반환됨(영어, 공백 모두 1바이트)
SUBSTR() 지정한 길이만큼의 문자열을 반환하는 함수 SUBSTR(전체 문자열, 시작, 길이) 맨 처음 시작은 개발 언어처럼 0이 아닌 1이다.
문자함수 예시
-- 문자 함수 -- 도서명에 '안드로이드'가 포함된 도서에 대해 -- '안드로이드'를 'Android'로 변경해서 출력 -- 실제 데이터는 변경되지 않음(출력만 한다) -- replace select bookNo, replace(bookName, '안드로이드', 'Android') bookName, bookAuthor, bookPrice from book where bookName like '안드로이드%'; -- char_length(), length() -- '서울 출판사'에서 출간한 도서의 도서명과 바이트 수, 문자열 길이 출력, 출판사명 출력 select b.bookName as '도서명', length(b.bookName) as '바이트 수', char_length(b.bookName) as '길이', p.pubName as '출판사' from book b inner join publisher p on p.pubNo = b.pubNo where p.pubName = '서울 출판사'; -- substr(전체 문자열, 시작, 길이) -- 도서 테이블의 '저자' 열에서 성만 출력 select substr(bookAuthor, 1, 1) as '성' from book; -- 도서 테이블의 '저자' 열에서 이름만 출력 select substr(bookAuthor, 2, 2) as '이름' from book;
날짜 함수
DATE(NOW()) : 현재 날짜 출력
TIME(NOW()) : 현재 시간 출력
YEAR(CURDATE()) : 현재 날짜 연도 출력
MONTH(CURDATE()) : 현재 날짜 월 출력
DAYOFMONTH(CURDATE()) : 현재 날짜 일 출력
DATEDIFF() : 날짜 차이 계산
TIMEDIFF() : 시간 차이 계산
날짜 함수 예시
-- 날짜 함수 -- 현재 날짜와 시간 출력 select date(now()), time(now()); -- 현재 날짜에서 연, 월, 일 추출 select year(curdate()), month(curdate()), dayofmonth(curdate()); -- 시간에서 시, 분, 초, 마이크로초 출력 -- current_time(), curtime() 둘 다 사용해도 무방하다. select hour(curtime()), minute(current_time()), second(current_time()), microsecond(current_time()); select hour(curtime()), minute(curtime()), second(curtime()), microsecond(curtime());
파일 로드 함수
대용량 데이터 저장할 때 사용
대본 : text타입
동양상 파일 : LONGBLOB 타입
LOAD_FILE(파일경로)
파일 용량이 현재 설정된 크기보다 큰 경우 데이터 저장 안 됨 my.ini파일에서 파일 최대 크기 변수 변경 파일 업로드 / 다운로드 하는 폴더 경로를 별도로 허용하는 내용 추가(동영상 파일이 저장된 경로로 지정.. 하려 했지만 맥은 자동 null값이 들어가는 듯 싶다.)
DDL(Data Control Language)
계정 관리
데이터의 사용 권한 관리
데이터베이스 트랜잭션 명시 (COMMIT / REVOKE)
COMMIT : 작업 완료
REVOKE : 작업 취소
트랜잭션 처리 중 오류 발생 REVOKE 작업 하여 처리하기 이전으로 되돌림.
GRANT : 데이터베이스 객체 권한 부여
REVOKE : 이미 부여된 데이터베이스 객체의 권한 취소
권한(Privilege)
특정 유형의 SQL문을 실행하거나 다른 사용자의 객체를 사용할 수 있는 권리
권한의 종류 시스템 권한 객체 권한 : 특정 객체를 조작할 수 있는 권한 DML 사용 권한 : SELECT / INSERT / DELETE / UPDATE
계정 & 권한 사용 방법
-- DCL use sqldb3; -- 사용자 계정 조회하려면 해당 테이블 사용해야 함. use mysql; -- 사용자 계정 조회 select * from user; -- 사용자 계정 생성 -- create user 계정@호스트 identified by 비밀번호 /* 호스트 - localhost : 로컬에서 접근 가능 - 192.168.@@@.@@@ : 특정 ip에서 접근 가능 - '%' : 어디에서나 접근 가능 비밀번호 변경 - SET PASSWORD for '계정명'@호스트 = '새 비밀번호'; 계정 삭제 - DROP USER 계정@호스트; */ -- 계정생성 create user newuser1@'%' identified by '1111'; -- 스키마 접근 불가 -- 비밀번호 변경 set password for 'newuser1'@'%' = '1234'; -- 서버 연결 (newuser1) -- 계정 삭제 drop user newuser1@'%'; -- 권한 조회 : SHOW GRANTS FOR 사용자계정; show grants for root; -- 권한 부여 : GRANT 권한 ON 데이터베이스.테이블 TO 계정@호스트; -- 모든 권한 부여 : GRANT ALL PRIVILEGES ON *.* TO 계정@호스트; (*은 '모두'라는 뜻) -- 특정 DB의 모든 테이블에 특정 권한 부여 : GRANT select, insert, update, delete ON DB명.* TO 계정@호스트; -- 특정 DB의 모든 테이블에 대한 권한 삭제 : REVOKE ALL PRIVILEGES ON DB.* FROM TO 계정@호스트; -- 특정 DB의 모든 테이블에 대해 특정 권한 삭제 : REVOKE select, insert, update, delete ON DB명.* FROM 계정@호스트; -- 계정 생성 create user newuser1@'%' identified by '1111'; -- 권한 조회 show grants for newuser1; -- 서버 접속은 가능하지만 아무런 스키마가 보이지 않는다(스키마 사용 권한 없음) -- 모든 권한 부여 grant all privileges on *.* to newuser1@'%'; -- 모든 스키마 / 테이블 접근 가능 -- user1 select 권한 삭제 revoke select on *.* from newuser1@'%'; -- table could not fetched -- sqldb3의 모든 테이블에 select권한 부여 grant select on sqldb3.* to newuser1@'%'; -- 다른 테이블은 could not fetched
백업 및 복구
데이터베이스를 주기적으로 백업해 두거나 다른 서버로 이관할 때 사용
백업 : Export
복구 : Import
Server - Data Export or Data Import
JAVA와 DB연동
JDBC(Java Database Connectivity) 다양한 종류의 관계형 데이터베이스를 자바와 연동시켜 사용할 수 있게 도와주는 API 모든 DBMS에서 공통적으로 사용할 수 있는 인터페이스와 클래스로 구성됐다 실제 구현 클래스는 각 DBMS 벤더가 구현했기 때문에 모든 벤더가 JDBC드라이버를 제공 각 DBMS에 맞는 JDBC드라이버 사용 JDBC 드라이버 JDBC 인터페이스를 구현한 클래스 파일 모음(.jar파일) 각 DBMS 벤더에서 제공되는 구현 클래스 응용프로그램과 DBMS 사이에서 연결 역학 SQL문을 DBMS에 전달하고 그 결과값을 응용프로그램에 전달하는 역할 장점 사용하는 RDBMS에 독리적인 프로그래밍 가능 쉽게 RDBMS 교체 가능 자바는 단순히 문자열로 쿼리를 전송하고 해석은 각 벤더가 구현한 드라이버에서 담당 표준 SQL뿐 아니라 각 JDBC Driver를 제공하는 DBMS 벤더별로 최적의 성능 발휘 벤더 종속적인 SQL도 처리 가능
JDBC를 이용한 연결 과정
드라이버 로드 Connection 객체 생성 Statement 또는 PreparedStatement 생성 쿼리 수행 (sql 문 실행) SQL문에 결과 반환이 있는 경우 ResultSet 객체 생성 (결과 받아옴) 모든 객체 close() : 반환 순서 ResultSet
Statement
Connection (접속 종료)
패키지 import
JDBC는 java.sql 패키지에 포함되어 있음
import java.sql.DriverManager;
import java.sql.Connection; ….
JDBC는 데이터베이스 접속하기 위해
한 개의 클래스 java.sql.DriverManager와
두 개의 인터페이스(java.sql.Driver 와 java.sql.Connection)를 사용
1 .JDBC 드라이버 로드
Java에서 MySQL Driver를 사용하기 위해 드라이버를 JVM에 로딩하는 과정
Class.forName(“com.mysql.cj.jdbc.Driver”);
2. Connection 객체 생성
DriverManager 클래스의 static 메서드 인
getConnection() 메서드를 이용해서
Connection 객체를 얻어옴
MySQL 서버 실제 연결
Connection 객체가 생성되면 DBMS 접속 성공
DriverManager.getConnection(String url, String user, String password)
jdbcLmysql : JDBC 드라이버 jdbc : JDBC URL의 프로토콜 이름 mysql : MySQL JDBC 드라이버
localhost : MySQL이 설치된 IP (호스트 이름) localhost 또는 127.0.0.1 : 내 컴퓨터 192.168.172.1 : 서버 IP (현재 내 컴퓨터의 Workbench에서 서버 접속해서 사용)
3306 : MySQL 접속 포트
sqldb6 : 사용하는 데이터 베이스 이름
serverTimezone=UTC
3. Statement 객체 생성
쿼리문 전송을 위한 Statement 객체 생성
또는 PreparedStatement
Connection 인터페이스의 createStatement() 메서드를 사용해서 객체 생성
PreparedStatement pstmt = con.preparedStatement(sql);
PreparedStatement 객체를 통해 SQL 전송 가능
4. 쿼리 수행
SQL 전송에 사용되는 메소드 executeQuery() / executeUpadte() 두 가지로 구분하는 이유 : sql문 실행 결과가 다르기 때문 executeUpdate() 쿼리문이 insert / update / delete 구문인 경우 사용 영향을 받은 행의 수 반환 insert 후 반환된 결과값이 0인 경우 insert 되지 않았음 executeQuery() select 구문의 경우 ResultSet 객체로 반환 (select 문 결과에 해당되는 여러 행 반환) ResultSet에서 데이터 추출 next() 메소드 이용해서 논리적 커서를 이동하며 각 열의 데이터를 바인딩 rs.next() 호출 결과 true이면 반복해서 다음 행 데이터 가져옴(반복문 사용) cursor : 다음 열로 이동 next() : 다음 행으로 이동 정수 : getInt("bookPrice) 문자 : getString("bookName")
5. 모든 객체 자원반납 : close()
ResultSet
Statement
Connection(접속 종료)
JDBC 이용한 DB연동해서 출력 예시
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Arrays; import java.util.Date; public class BookSelectEx { public static void main(String[] args) { try { // JDBC Driver 클래스의 객체 생성 런타임시 로드 //Class.forName("com.mysql.cj.jdbc.Driver"); // 연결 주소, 사용자 계정, 패스워드 문자열 변수 등록 String url = "jdbc:mysql://localhost:3306/sqldb6?serverTimezone=UTC"; String user = "root"; String pwd = ""; // DB연결 위한 객체 생성 // DriverManager 통해 Connection 객체 생성 //MySQL 서버 연결 : 주소, 사용자 계정, 패스워드 전송 Connection con = DriverManager.getConnection(url, user, pwd); if(con != null) { System.out.println("연결 성공"); } // select 쿼리문 문자열 작성 String sql = "select * from book order by bookNo"; // 쿼리문 전송을 위한 PreparedStatement 객체 생성 // Connection 인터페이스와 PreparedStatement() 메소드 사용하여 객체 생성 PreparedStatement pstmt = con.prepareStatement(sql); // 쿼리문 실행하고 결과 받아옴 // select 구문이므로 executeQuery() 메소드 사용 // 반환되는 결과는 ResultSet객체가 반응 ResultSet rs = pstmt.executeQuery(sql); // executeQuery() 실행 결과 받아온 ResultSet에서 데이터 추출 // ResultSet의 next() 메소드 이용해서 논리적 커서를 이동하여 각 열의 데이트 바인딩해 옴 // next() : 커서를 이동하면 다음 행 지정 // 다음 행이 있으면 true, 없으면 false반환 // 반복문 이용해서 ture인 동안 다음 행을 계속해서 가져옴 // 데이터 타입에 맞춰 get@@@() 메소드 사용 while(rs.next()) { // 1. next()하면서 항 행씩 데이터 가져와 변수에 저장 String bookNo = rs.getString(1); String bookName = rs.getString(2); String bookAuthor = rs.getString(3); int bookPrice = rs.getInt(4); Date bookDate = rs.getDate(5); int bookStock = rs.getInt(6); String pubNo = rs.getString(7); // 2. 한 행씩 변수에 있는 값 출력 System.out.format("%-10s \t %-25s \t %-10s \t %6d \t %13s \t%3d \t %s10
", bookNo, bookName, bookAuthor, bookPrice, bookDate, bookStock, pubNo); } rs.close(); pstmt.close(); con.close(); } catch (Exception e) { System.out.println("오류 발생!"); e.printStackTrace(); } } }
from http://5bong2-develop.tistory.com/32 by ccl(A) rewrite - 2021-11-24 17:27:32