8/5 Mysql설치 및 사용, Mysql JDBC, order by, 문자 함수, 문자열 길이 반환 함수, 문자조작 함수, 숫자 함수, 날짜 함수

[MySQL 설치 및 사용 방법]

MySQL Server 8.0.30 선택 후 execute

user name : root

비밀번호 : mysql80

 

1) scottdb 스키마 만듦 (4번째 아이콘 클릭)

 

*mysql에서는 varchar2를 varchar라고 쓴다.

 

2) 스키마 이름에서 마우스 우클릭 > Set as Default Schema

 

3) sql 작성

4) 영역 선택 후 3번째 번개 아이콘 클릭하여 sql문 실행

5) 스키마 이름 > Refresh all 선택하면 생성한 테이블 나옴.

6) 테이블의 오른쪽 누르면

이렇게 생성한 모습 보여줌

 

 

[함수와 프로시저의 차이점]

함수 : 리턴값이 1개이다. 실무에서는 간단한 계산식에서 쓴다.

프로시저는 : 리턴값이 없지만 파라미터를 통해 여러개의 값을 돌려줄 수 있다. 실무에서는 DML작업 시 (테이블 여러개 사용 시) 사용한다.

 

 

[JDBC]

*oracle을 mysql과 연결하는 법

mysql-connector-java을 program files에서 검색하여 mysql-connector-java-8.0.30.jar 파일을 찾아 DB폴더에 복붙한다.

oracle의 jar파일처럼 mysql의 jar파일을 라이브러리에 추가한다.

 

MyDr.java

package ch18;

import java.sql.Connection;
import java.sql.DriverManager;

public class MyDr {

	public static void main(String[] args) {
		String driver 	= "com.mysql.cj.jdbc.Driver";
		String url 		= "jdbc:mysql://127.0.0.1:3306/scottdb";
		
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, "root", "mysql80");
			if (conn!=null) {
				System.out.println("mysql 연결 성공");				
			}
			conn.close();
		} catch (Exception e) {
			System.out.println("mysql 연결 실패" + e.getMessage());
		}

	}

}

실행 결과

 

MyInsert.java

오라클이나 mysql이나 java 코드는 구조 같음

package ch18;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class MyInsert {

	public static void main(String[] args) throws SQLException {
		Scanner sc = new Scanner(System.in);
		System.out.println("MySql 부서코드?"); 	String dno = sc.nextLine();
		System.out.println("MySql 부서명?");		String dname = sc.nextLine();
		System.out.println("MySql 위치?"); 		String position = sc.nextLine();
		
		Connection conn = null;
		Statement stmt = null;
		String driver 	= "com.mysql.cj.jdbc.Driver";
		String url 		= "jdbc:mysql://127.0.0.1:3306/scottdb";
		String sql = String.format("Insert Into division(dno, dname, position) " 
		+ "Values(%s, '%s', '%s')", dno, dname, position);
		
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, "root", "mysql80");
			stmt = conn.createStatement();
			int result = stmt.executeUpdate(sql);
			if (result > 0)
				System.out.println("MySql 입력 성공");
			else
				System.out.println("MySql 입력 실패");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
		sc.close();
	}
}

출력 결과

division 테이블에 데이터 새로 생김

 

 

 

MyPrepare.java (과제1)

package ch18;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

//과제 1
//1. division 테이블에 입력, 폰 번호 포함
//2. prepare statement 반드시 쓰기

//출력 결과
//입력할 Mysql 부서코드 ?
//51
//입력할 Mysql 부서명 ?
//구매1팀
//입력할 Mysql 폰 ?
//01023456789
//입력할 Mysql 근무지 ?
//강남
//Mysql Prepare 입력성공 ^^

public class MyPrepare {

	public static void main(String[] args) throws SQLException {
		Scanner sc = new Scanner(System.in);
		System.out.println("입력할 Mysql 부서코드?");	String dno = sc.nextLine();
		System.out.println("입력할 Mysql 부서명?");		String dname = sc.nextLine();
		System.out.println("입력할 Mysql 폰?");		String phone = sc.nextLine();
		System.out.println("입력할 Mysql 근무지?");
		String position = sc.next();

		Connection conn = null;
		PreparedStatement pstmt = null;
		String driver = "com.mysql.cj.jdbc.Driver";
		String url = "jdbc:mysql://127.0.0.1:3306/scottdb";
		String sql = "Insert Into division values(?, ?, ?, ?)";
		
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, "root", "mysql80");
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dno);
			pstmt.setString(2, dname);
			pstmt.setString(3, phone);
			pstmt.setString(4, position);
			int result = pstmt.executeUpdate();
			if (result > 0)
				System.out.println("Mysql Prepare 입력 성공");
			else
				System.out.println("Mysql Prepare 입력 실패");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			if (pstmt != null) pstmt.close();
			if (conn != null) pstmt.close();
		}
	}

}

출력 결과

데이터베이스 실행 후 모습

 

 

MySelect1.java(과제2)

package ch18;

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

//과제2
//부서번호 받아 조회하기
//일반  stmt 객체 생성 

////출력 결과
//Mysql 부서코드를 입력하세요
//50
//mysql 부서코드 :50
//mysql 부서명   :구매팀
//mysql 전화번호 :null
//mysql 근무지   :역삼

public class MySelect1 {

	public static void main(String[] args) throws SQLException {
		Scanner sc = new Scanner(System.in);
		System.out.println("Mysql 부서코드를 입력하세요");
		int dno = sc.nextInt();
		String driver = "com.mysql.cj.jdbc.Driver";
		String url = "jdbc:mysql://127.0.0.1:3306/scottdb";
		String sql = "SELECT dname, phone, position FROM division WHERE dno = " + dno;

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, "root", "mysql80");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);

			if (rs.next()) {
				String dname = rs.getString("dname");
				String phone = rs.getString("phone");
				String position = rs.getString("position");
				System.out.println("mysql 부서코드 : " + dno);
				System.out.println("mysql 부서명 : " + dname);
				System.out.println("mysql 전화번호 : " + phone);
				System.out.println("mysql 근무지 : " + position);
			} else {
				System.out.println("자료가 없습니다");
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				rs.close();
			if (conn != null)
				rs.close();
		}
		sc.close();
	}
}

 

기준 테이블 모습

 

출력 결과

 

MyDelete.java (과제3)

package ch18;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

//과제3
//부서번호 받아 Row 삭제하기
//일반  stmt 객체 생성 

//출력 결과
//MySql 삭제할 부서를 입력하세요 ?
//50
//MySql 삭제 성공  ^ ^ 

public class MyDelete {

	public static void main(String[] args) throws SQLException {
		Scanner sc = new Scanner(System.in);
		System.out.println("MySql 삭제할 부서를 입력하세요 ?");
		int dno = sc.nextInt();
		Connection conn = null;
		Statement stmt = null;
		String driver = "com.mysql.cj.jdbc.Driver";
		String url = "jdbc:mysql://127.0.0.1:3306/scottdb";
		String sql = String.format("DELETE FROM division where dno = %d", dno);
		System.out.println("sql->" + sql);

		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, "root", "mysql80");
			stmt = conn.createStatement();

			int result = stmt.executeUpdate(sql);
			if (result > 0)
				System.out.println("Mysql 삭제 성공");
			else
				System.out.println("Mysql 삭제 실패");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
		sc.close();
	}
}

출력 결과

Mysql > division 테이블을 확인해보면 해당 데이터가 삭제돼있다.

*Oracle과 SQL Server는 FROM의 생략을 허용하지만 Mysql은 FROM의 생략을 허용하지 않는다.

 

 

[Order by 절]

Order by.sql

-- 학생 테이블에서 학년을 오름차순으로 정렬하여 이름, 학년, 전화번호를 출력
SELECT name, grade, tel FROM student ORDER BY grade ASC;

-- 학생 테이블에서 학년을 내림차순으로 정렬하여 이름, 학년, 전화번호를 출력
SELECT name, grade, tel FROM student ORDER BY grade DESC;

-- 모든 사원의 이름과 급여 및 부서번호를 출력하는데,  
-- 부서 번호로 결과를 정렬한 다음 급여에 대해서는 내림차순으로 정렬
SELECT ename, sal, deptno FROM emp ORDER BY deptno, sal DESC;

-- 부서 10과 30에 속하는 모든 사원의 이름과 부서번호를 이름의 알파벳 순으로 
-- 정렬되도록 질의문(emp), in을 사용
-- or을 쓸 수도 있지만 (특히 데이터가 많을 때)가독성 때문에 in을 쓴다.
SELECT ename, deptno FROM emp WHERE deptno IN(10, 30) ORDER BY ename;

-- 1982년에 입사한 모든 사원의 이름과 입사일을 구하는 질의문
SELECT ename, hiredate FROM emp WHERE hiredate like '82%';
-- 옛날 버전(위의 코드가 오류날 경우 자동 처리가 되지 않은 것이므로 옛날 버전으로 코딩)
SELECT ename, hiredate FROM emp WHERE TO_CHAR(hiredate, 'yymmdd') LIKE '82%';

-- 보너스를 받는 모든 사원에 대해서 이름, 급여 그리고 보너스를 출력하는 질의문을 형성. 
-- 단 급여와 보너스에 대해서 급여/보너스순으로 내림차순 정렬
SELECT ename, sal, comm FROM emp WHERE (comm IS NOT NULL AND comm<> 0) ORDER BY sal DESC, COMM DESC;

-- 보너스가 급여의 20% 이상이고 부서번호가 30인 모든 사원에 대해서 
-- 이름, 급여 그리고 보너스, 부서번호를 출력하는 질의문을 형성하라
SELECT ename, sal, comm, deptno FROM emp WHERE comm>= sal*0.2 AND deptno = 30;

 

 

[SQL 함수 > 문자 함수(INITCAP, LOWER, UPPER)]

 

문자함수.sql

--대소문자 변환
SELECT ename, UPPER(ename), LOWER(ename), INITCAP(ename) FROM emp;

--소문자인지 확인하기
--저장된 데이터는 대소문자를 구별하기 때문에 대소문자를 모르는 상태에서 select할 때 쓴다.
--첫 번째 sql문 : 데이터 안나옴, 두 번째 sql문 : 데이터 나옴(scott이 대문자였기 때문)
SELECT ename, sal, deptno FROM emp WHERE ename = 'scott';
SELECT ename, sal, deptno FROM emp WHERE LOWER(ename) = 'scott';

--학생 테이블에서 학번이 ‘20101’인 학생의 사용자 아이디를 소문자와 대문자로 변환하여 출력
SELECT LOWER(userid), UPPER(userid) FROM student WHERE studno = 20101;

 

[문자열 길이 반환 함수(LENGTH, LENGTHB)]

문자열 길이 반환 함수.sql

--문자열 길이
--영어의 LENGTH(문자열의 길이)와 LENGTHB(문자열의 바이트수)는 그대로 나오는데
--한글의 LENGTHB(문자열의 바이트수)는 3배가 나오는 이유
--UTF 8은 한글이 3바이트이기 때문.(외우기)
SELECT dname, LENGTH (dname), LENGTHB(dname) FROM dept;

출력 결과

 

--한글 문자열 길이 테스트 : insert 안 된 예시
--원인은 UTF 8 기준이기 때문에 글자 5개는 15바이트다.
--그래서 dept의 dname은 varchar14로 제한됐기 때문에 바이트 수를 초과하는 것이다.
--면접 질문 가능성 있음.
INSERT INTO DEPT VALUES (59,'경영지원팀','충정로');

실행 결과

 

 

[문자조작 함수(CONCAT, SUBSTR, INSTR, LPAD, RPAD, LTRIM, RTRIM)]

현장에서 많이 쓰는 명령어들. 알아둬야 함.

SUBSTR('SQL*Plus',5,4) 일 때 다섯번째자리(포함)부터 4자리를 추출하여 Plus 가 나옴. 정말 많이 씀.

LTRIM에 별표같은게 없으면 공백문자를 삭제해줌.

 

SELECT CONCAT(CONCAT(name, '의 직책은 '), position) FROM professor;

 

기준 테이블

출력 결과

--학생 테이블에서 1학년 학생의 주민등록 번호에서 생년월일과 태어난 달을 추출하여 이름, 주민번호, 생년월일, 태어난 달을 출력하여라
SELECT name, idnum, SUBSTR(idnum, 1, 6) birth_date, SUBSTR(idnum, 3,2) birth_mon
FROM student
WHERE grade = 1;

출력 결과

 

--부서 테이블의 부서 이름 칼럼에서 ‘과’ 글자의 위치를 출력하여라
--INSTR 함수 : 문자열중에서 사용자가 지정한 특정 문자가 포함된 위치를 반환하는 함수
SELECT dname, instr(dname, '과')
FROM department;

--LPAD, RPAD 함수
--LPAD와 RPAD 함수는 문자열이 일정한 크기가 되도록 왼쪽 또는 오른쪽에 지정한 문자를 삽입하는 함수
--교수테이블에서 직급 칼럼의 왼쪽에 ‘*’ 문자를 삽입하여 10바이트로 출력하고 
--교수 아이디 칼럼은 오른쪽에 ‘+’문자를 삽입하여 12바이트로 출력하여라
SELECT position, LPAD(position, 10, '*') Lpad_position,
        userid, RPAD(userid, 12, '+') RPAD_userID
FROM professor;

--LTRIM, RTRIM 함수
--LTRIM와 RTRIM 함수는 문자열에서 특정 문자를 삭제하기 위해 사용
--함수의 인수에서 삭제할 문자를 지정하지 않으면 문자열의 앞뒤 부분에 있는 공백 문자를 삭제
--실행 결과 왼쪽은 공백이 없어짐
SELECT LTRIM('  abcdefg  ',' ') FROM dual;

--부서 테이블에서 부서 이름의 마지막 글자인 ‘과’를 삭제하여 출력하여라
--과가 없으면 그냥 출력됨
SELECT dname, RTRIM(dname, '과') FROM department;

 

 

[숫자함수 (중요함! 별 3개) (ROUND, TRUNC, MOD, CEIL, FLOOR)]

 

숫자함수.sql

---------------숫자함수 ***---------------
-- ROUND 함수
-- 지정한 자리 이하에서 반올림한 결과 값을 반환하는 함수
-- 교수 테이블에서 101학과 교수의 일급을 계산(월 근무일은 22일)하여 
-- 소수점 첫째 자리와 셋째 자리에서 반올림 한 값과 소숫점 왼쪽 첫째 자리에서 반올림한 값을 출력하여라
-- 마이너스는 정수자리기준임. 즉 -1이면 일의자리에서 반올림. 
-- ROUND(sal/22, 0)일 때 0은 생략 가능
SELECT name, sal, sal/22, ROUND(sal/22, 0), ROUND(sal/22, 2), ROUND(sal/22, -1)
FROM professor
WHERE deptno = 101;

--TRUNC 함수
--지정한 소수점 자리수 이하를 절삭(내림)한 결과 값을 반환하는 함수
--교수 테이블에서 101학과 교수의 일급을 계산(월 근무일은 22일)하여 
--소수점 첫째 자리와 셋째 자리에서 절삭 한 값과 소숫점 왼쪽 첫째 자리에서 절삭한 값을 출력하여라
SELECT name, sal, sal/22, TRUNC(sal/22), TRUNC(sal/22, 2), TRUNC(sal/22, -1)
FROM professor
WHERE deptno = 101;

--MOD 함수 
--MOD 함수는 나누기 연산후에 나머지를 출력하는 함수 
--교수 테이블에서 101번 학과 교수의 급여를 보직수당으로 나눈 나머지를 계산하여 출력하여라
SELECT name, sal, comm, MOD(sal, comm)
FROM professor
WHERE deptno = 101;

----CEIL, FLOOR 함수
--CEIL 함수는 지정한 숫자보다 크거나 같은 정수 중에서 최소 값을 출력하는 함수
--FLOOR함수는 지정한 숫자보다 작거나 같은 정수 중에서 최대 값을 출력하는 함수
SELECT ceil(19.7), FLOOR(12.345)
FROM dual;

 

[날짜함수. 하단 내용 중요함. 별 3개 (SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC)]

날짜 - 날짜 만 결과가 일수라는 것 기억하기

 

 

날짜함수.sql

-------------날짜함수***-----------
--1) 날짜 + 숫자 = 날짜(날짜에 일수를 가산)
-- 교수 번호가 9908인 교수의 입사일을 기준으로 입사 30일 후와 60일 후의 날짜를 출력
SELECT name, hiredate, hiredate+30, hiredate+60
FROM professor
WHERE profno = 9908;
--2) 날짜 - 숫자 = 날짜(날짜에 일수를 감산)
--3) 날짜 - 날짜 = 일수(날짜에 날짜를 감산)
--
--4) SYSDATE 함수
--SYSDATE 함수는 시스템에 저장된 현재 날짜를 반환하는 함수로서, 초 단위까지 반환
SELECT sysdate
FROM dual;

--5) MONTHS_BETWEEN : date1과 date2 사이의 개월 수를 계산
--ADD_MONTHS : date에 개월 수를 더한 날짜 계산
--MONTHS_BETWEEN과 ADD_MONTHS 함수는 월 단위로 날짜 연산을 하는 함수 

--입사한지 120개월 미만인 교수의 
--교수번호, 입사일, 입사일로 부터 현재일까지의 개월 수, 입사일에서 6개월 후의 날짜를 출력하여라
--월요일에 설명 다시 할 예정
SELECT profno, hiredate, MONTHS_BETWEEN(SYSDATE, hiredate) working_day,
    ADD_MONTHS(hiredate, 6) hire_6after
FROM professor
WHERE MONTHS_BETWEEN(SYSDATE, hiredate) < 120;