2021-12-27 MySQL 내장함수, INSERT, CMD, JDBC
My SQL의 내장 함수
숫자 관련 함수
- abs(숫자) : 절대값 계산
- CEILING(숫자) : 값 보다 큰 정수 중 가장 작은 수
- FLOOR(숫자) : 값보다 작은 정수 중 가장 큰 수 (실수는 무조건 버림함, 음수일 경우에는 결과에서 제외)
- ROUND(숫자, 자리수) : 설정한 자리수만큼만 남기고 반올림
- TRUNCATE(숫자, 자리수) : ROUND와 같은 동작이지만 반올림이 아닌 버림
- POW(X, Y) : X의 Y승
- MOD(X, Y) : X를 Y로 나눈 나머지
- GREATES(X,Y,Z ...) : 주어진 숫자 중 가장 큰 숫자
- LEAST(X,Y,Z ...) : 주어진 숫자 중 가장 작은 숫자
SELECT GREATEST(23,15,26,64,54,17,23), LEAST(23,15,26,64,54,17,23) FROM DUAL;
문자 관련 함수
- ASCII(문자) : 해당 문자의 아스키 코드 값
- CONCAT(문자열1, 문자열2, 문자열3 ...) : 주어진 문자열들의 이어붙이기 연산
SELECT CONCAT('ABCD', 'EFG', 'HIJK') FROM DUAL;
--SELECT 'ABCD'||'EFG'||'HIJK' FROM DUAL; (오라클)
- INSERT(문자열, 시작위치, 길이, 바꿀문자열) : 문자열의 시작 위치부터 길이 만큼 바꿀 문자열로 대치
SELECT INSERT ('나는 버스를 타고 갑니다',4,3,'기차를') FROM DUAL;
- REPLACE(문자열, 기본문자열, 바꿀문자열) : 문자열 안에 있는 기본 문자열을 바꿀 문자열로 대치
SELECT REPLACE('나는 버스를 타고 값니다', '버스를', '기차를') FROM DUAL;
- INSTR('문자열', '찾는문자열') : 문자열 중 찾는 문자열의 위치 값을 출력
SELECT INSTR('나는 버스를 타고 갑니다', '버스를') FROM DUAL;
- LEFT('문자열', 개수) : 문자열 중 왼쪽에서 개수만큼을 추출
- RIGHT('문자열', 개수) : 문자열 중 오른쪽에서 개수만큼을 추출
SELECT LEFT('나는 버스를 타고 갑니다', 5) AS LEFTFUNCION,
RIGHT('나는 버스를 타고 갑니다', 5) AS RIGHTFUNCTION FROM DUAL;
- MID('문자열', 시작위치, 개수) : 문자열 중 시작 위치부터 개수만큼 출력
SELECT MID('나는 버스를 타고 갑니다', 8, 2) FROM DUAL;
- SUBSTRING('문자열', 시작위치, 개수) : 문자열 중 시작 위치부터 개수만큼 출력
- LTRIM('문자열') : 문자열 중 왼쪽의 공백을 없앤다.
- RTRIM('문자열') : 문자열 중 오른쪽의 공백을 없앤다.
- TRIM('문자열') : 양쪽 모두의 공백을 없앤다.
- LCASE('문자열') OR LOWER('문자열') : 소문자로 바꾼다.
- UCASE('문자열') OR UPPER('문자열') : 대문자로 바꾼다.
- REVERSE('문자열') : 문자열을 반대로 나열한다.
집계 함수
- COUNT(필드명) : NULL값이 아닌 레코드 수를 구한다.
- COUNT(*) : 모든 레코드 수를 구한다.
- SUM(필드명) : 필드명의 합계를 구한다.
- AVG(필드명) : 각 그룹 안에서 필드명의 평균값을 구한다.
- MAX(필드명) : 최댓값
- MIN(필드명) : 최솟값
날짜 관련 함수
- NOW() : 현재 날짜와 시간 (DATETIME 형식)
- CURDATE() or CURRNET_DATE() : 현재 날짜 (DATE 형식)
- CURTIME or CURRENT_TIME() : 현재 시간 (TIME 형식)
- DATE_ADD(날짜, INTERVAL) : 날짜에서 INTERVAL만큼 더한다.
※ 기준 값 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
SELECT DATE_ADD(NOW(), INTERVAL 2 DAY), DATE_ADD(NOW(), INTERVAL 3 MONTH) FROM DUAL;
- DATE_SUB(날짜, INRTERVAL) : 날짜에서 INRTERVAL만큼 뺀다.
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH) FROM DUAL;
- YEAR(날짜) : 연도 출력
- MONTH(날짜) : 월 출력
- MONTHNAME(날짜) : 월을 영어로 출력
- DAYNAME(날짜) : 요일을 영어로 출력
- DAYOFMONTH(날짜) : 날짜의 월별 일자 출력 ( ex: 일요일(1), 월요일(2) ... 토요일(7))
- WEEKDAY(날짜) : 날짜의 주별 일자 출력 ( ex: 월요일(0), 화요일(1) ... 일요일(6))
- DAYOFYEAR(날짜) : 해당연도 1월1일부터 날짜까지의 날 수
- WEEK(날짜) : 일 년 중 몇번째 주인지 출력
- FROM_DAYS(N) : 00년 00월 00일 부터 N만큼 경과한 날의 날짜
- TO_DAYS(날짜) : 00년 00월 00일 부터 날짜까지 며칠이 지났는지 출력
- DATE_FORMAT(날짜, '형식') : 날짜를 형식에 맞게 출력
SELECT DATE_FORMAT(NOW(), '%Y%M%D') FROM DUAL;
'형식'에 사용될 수 있는 명령어
-%a : 일(SUN~SAT)
-%b : 월(JUN~DEC)
-%d : 일(01~31)
-%D : 일(1st, 2nd, 3rd, ...)
-%e : 일(1st, 2nd, 3rd, ...)
-%f : Microseconds(000000 ~ 999999)
-%H : 시간(00~23)
-%h : 시간(00~12)
-%I : 시간(1~12)
-%i : 분(00~59)
-%j : DAY OF THE YEAR (001 ~ 366)
-%k : 시간(0~23)
-%M : 월(January ~ Decemer)
-%m : 월(01~12)
-%p : AM/PM
-%r : 시간(12시)을 hh:mm:ss AM/PM 형식으로 출력
-%S : 초(00~59)
-%s : 초(00~59)
-%T : 시간을(24시)를 hh:mm:ss 형식으로 출력
MySQL INSERT
테이블 명 앞에 스키마이름. 을 적어줘야 하고 SEQUENCE 대신 Auto Increment를 사용하기 때문에 따로 명시하지 않는다
INSERT INTO SCOTT.BOOKLIST(SUBJECT, MAKEYEAR,INPRICE, RENTPRICE,GRADE)
VALUES ('일곱해의 마지막', 2020, 12150, 2000, 'ALL');
CDM에서 mysql 연결
시스템 환경 변수의 path 에 mysql 경로를 추가한 후 실행해야 정상 작동한다
( C:\Program Files\MySQL\MySQL Server 8.0\bin ) -> 버전에 따라 빨갛게 표시된 숫자 변경
-h는 호스트, -u는 사용자 -p는 비밀본호를 의미한다.
종료는 quit 또는 exit
CMD에서 스키마 생성 및 이동, 확인
CREATE SCHEMA SCOTT DEFAULT CHARACTER SET utf8mb4;
use scott
show tables;
테이블 상세 구조 확인
desc 테이블명;
MySQL SELECT
LIMIT
SELECT로 데이터를 조회할 때 조회할 레코드의 갯수를 제한한다.
SELECT * FROM MEMBERLIST ORDER BY NUM DESC LIMIT 5;
OFFSET
SELECT로 데이터를 조회할 때 맨 위부터 OFFSET에 지정한번째까지는 뛰어 넘고 그 다음부터 리턴
SELECT * FROM MEMBERLIST ORDER BY NUM DESC LIMIT 5 OFFSET 5;
MySQL JDBC
생성한 자바 프로젝트 우클릭 -> properties
Java Build Path -> Add External JARs -> mysql -connector-java-버전-jar 을 찾아 추가 -> Apply and Close
(계속 빨간줄 에러가 난다면 위 창에서 Project Facets -> JAVA 버전을 1.8로 바꾸고 실행해본다)
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/scott";
//다른 컴퓨터에서 접속 시에는 localhost 대신 아이피 주소
private static String id = "root";
private static String pw = "adminuser";
DAO 파일에서 사용하는 전역변수만 위와 같이 수정하면 나머지는 오라클에서 했던 것과 거의 유사하다
DB MANAGER 클래스 (클래스명 : DBman)
import java.sql.*;
public class DBman {
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/scott";
//다른 컴퓨터에서 접속 시에는 localhost 대신 아이피 주소
private static String id = "root";
private static String pw = "adminuser";
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, id, pw);
} catch (ClassNotFoundException e) {e.printStackTrace();
} catch (SQLException e) { e.printStackTrace();
}
return conn;
}
public static void close(Connection con, PreparedStatement pstmt, ResultSet rs) {
try {
if(con != null)con.close();
if(pstmt != null)con.close();
if(rs != null)con.close();
} catch (SQLException e) {e.printStackTrace();
}
}
}
DTO 클래스 (클래스명 : MemberDto)
public class MemberDto {
private int num;
private String name;
private String phone;
private String birth;
private int bpoint;
private String joindate;
private int age;
private String gender;
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getBirth() {
return birth;
}
public void setBirth(String birth) {
this.birth = birth;
}
public int getBpoint() {
return bpoint;
}
public void setBpoint(int bpoint) {
this.bpoint = bpoint;
}
public String getJoindate() {
return joindate;
}
public void setJoindate(String joindate) {
this.joindate = joindate;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
}
DAO 클래스 (클래스명 : MemberDao)
import java.sql.*;
import java.util.ArrayList;
public class MemberDao {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
private MemberDao() {}
private static MemberDao itc = new MemberDao();
public static MemberDao getInstance() { return itc; }
public ArrayList<MemberDto>selectAll(){
ArrayList<MemberDto> list = new ArrayList<>();
String sql = "SELECT NUM, NAME, PHONE, BIRTH, BPOINT, "
+ "DATE_FORMAT(BIRTH, '%Y-%m-%d') AS BT, DATE_FORMAT(JOINDATE, '%Y-%m-%d') AS JD"
+ ", AGE, GENDER FROM MEMBERLIST ORDER BY NUM DESC";
con = DBman.getConnection();
try {
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
MemberDto mdto = new MemberDto();
mdto.setNum(rs.getInt("num"));
mdto.setName(rs.getString("name"));
mdto.setPhone(rs.getString("phone"));
mdto.setBirth(rs.getString("BT"));
mdto.setBpoint(rs.getInt("bpoint"));
mdto.setJoindate(rs.getString("JD"));
mdto.setGender(rs.getString("gender"));
mdto.setAge(rs.getInt("age"));
list.add(mdto);
}
} catch (SQLException e) {e.printStackTrace();
} finally { DBman.close(con,pstmt,rs);
}
return list;
}
public int insert(MemberDto mdto){
int result = 0;
String sql = "INSERT INTO MEMBERLIST(NAME, PHONE, BIRTH, GENDER, AGE) "
+ "VALUES (?, ?, STR_TO_DATE(CONCAT('',?,''),'%Y-%m-%d'), ?, ?)";
con = DBman.getConnection();
try {
pstmt = con.prepareStatement(sql);
pstmt.setString(1, mdto.getName() );
pstmt.setString(2, mdto.getPhone() );
pstmt.setString(3, mdto.getBirth() );
pstmt.setString(4, mdto.getGender() );
pstmt.setInt(5, mdto.getAge() );
result = pstmt.executeUpdate();
} catch (SQLException e) {e.printStackTrace();
} finally { DBman.close(con,pstmt,rs);
}
return result;
}
public int update(MemberDto mdto){
int result = 0;
String sql = "UPDATE MEMBERLIST SET NAME=?, PHONE=?, "
+ "BIRTH=STR_TO_DATE(CONCAT('',?,''),'%Y-%m-%d'),"
+ "JOINDATE=STR_TO_DATE(CONCAT('',?,''),'%Y-%m-%d'), BPOINT=?,"
+ " AGE=?, GENDER=? WHERE NUM=?";
con = DBman.getConnection();
try {
pstmt = con.prepareStatement(sql);
pstmt.setString(1, mdto.getName() );
pstmt.setString(2, mdto.getPhone() );
pstmt.setString(3, mdto.getBirth() );
pstmt.setString(4, mdto.getJoindate() );
pstmt.setInt(5, mdto.getBpoint());
pstmt.setInt(6, mdto.getAge() );
pstmt.setString(7, mdto.getGender() );
pstmt.setInt(8, mdto.getNum());
result = pstmt.executeUpdate();
} catch (SQLException e) {e.printStackTrace();
} finally { DBman.close(con,pstmt,rs);
}
return result;
}
public int delete(int num){
int result = 0;
String sql = "DELETE FROM MEMBERLIST WHERE NUM=?";
con = DBman.getConnection();
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1,num);
result = pstmt.executeUpdate();
} catch (SQLException e) {e.printStackTrace();
} finally { DBman.close(con,pstmt,rs);
}
return result;
}
public MemberDto selectOne(int num){
MemberDto mdto = null;
String sql = "SELECT NUM, NAME, PHONE, BPOINT, AGE, GENDER,"
+ "DATE_FORMAT(BIRTH, '%Y-%m-%d') AS BT, DATE_FORMAT(JOINDATE, '%Y-%m-%d') AS JD"
+ " FROM MEMBERLIST WHERE NUM = ?";
con = DBman.getConnection();
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, num);
rs = pstmt.executeQuery();
if(rs.next()) {
mdto = new MemberDto();
mdto.setNum(rs.getInt("num"));
mdto.setName(rs.getString("name"));
mdto.setPhone(rs.getString("phone"));
mdto.setBirth(rs.getString("BT"));
mdto.setJoindate(rs.getString("JD"));
mdto.setAge(rs.getInt("age"));
mdto.setGender(rs.getString("gender"));
mdto.setBpoint(rs.getInt("bpoint"));
}
} catch (SQLException e) {e.printStackTrace();
} finally { DBman.close(con,pstmt,rs);
}
return mdto;
}
}
Driver 클래스 (클래스명 : MemberDriver)
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Scanner;
public class MemberDriver {
public static void main(String[] args) {
while(true){
Scanner sc = new Scanner(System.in);
System.out.println("\n---메뉴선택---");
System.out.printf("1.데이터열람 2.데이터추가 3.데이터수정 4.데이터삭제");
System.out.printf("5.프로그램 종료 >> 메뉴선택 : ");
String choice = sc.nextLine();
if(choice.equals("5")) break;
MemberDao mdao = MemberDao.getInstance();
switch(choice) {
case"1":
select();
break;
case"2":
insert();
break;
case"3":
update();
break;
case"4":
delete();
break;
default : System.out.println("메뉴 선택이 잘못되었습니다.");
}
}
System.out.println("프로그램을 종료합니다.");
}
private static void delete() {
Scanner sc = new Scanner(System.in);
MemberDao mdao = MemberDao.getInstance();
MemberDto mdto = null;
System.out.print("수정할 회원 번호를 입력하세요 : ");
String input;
while(true) {
input = sc.nextLine();
if(input.equals("")) System.out.print("회원 번호 입력은 필수 입니다."
+ "\n삭제할 회원 번호를 입력하세요 : ");
else break;
}
mdto = mdao.selectOne(Integer.parseInt(input));
if(mdto == null) {
System.out.println("입력한 회원 번호가 존재하지 않습니다.");
return;
}
int result = mdao.delete(Integer.parseInt(input));
if(result == 1) System.out.println("\n레코드 삭제 성공");
else System.out.println("\n레코드 삭제 실패");
}
private static void update() {
Scanner sc = new Scanner(System.in);
MemberDao mdao = MemberDao.getInstance();
MemberDto mdto = null;
//대여번호
System.out.print("수정할 회원 번호를 입력하세요 : ");
String input;
while(true) {
input = sc.nextLine();
if(input.equals("")) System.out.println("회원 번호 입력은 필수 입니다."
+ "삭제할 회원 번호를 입력하세요 : ");
else break;
}
mdto = mdao.selectOne(Integer.parseInt(input));
if(mdto == null) {
System.out.println("입력한 회원 번호가 존재하지 않습니다.");
return;
}
System.out.println("회원 정보를 수정합니다. "
+ "수정을 원치 않는 항목은 엔터를 입력해주세요.");
System.out.printf("이름을 입력하세요(기존 성명 : %s) : ", mdto.getName());
String name = sc.nextLine();
if(!name.equals("")) mdto.setName(name);
System.out.printf("전화번호를 입력하세요(기존 전화번호 : %s) : ", mdto.getPhone());
String phone = sc.nextLine();
if(!phone.equals("")) mdto.setPhone(phone);
System.out.printf("생년월일 입력하세요(YYYY-MM-DD)(기존 생년월일 : %s) : ", mdto.getBirth());
String birth = sc.nextLine();
if(!birth.equals("")) mdto.setBirth(birth);
Calendar today = Calendar.getInstance();
int age = today.get(Calendar.YEAR) - Integer.parseInt(birth.substring(0,4)) + 1;
mdto.setAge(age);
System.out.printf("성별을 입력하세요(기존 성별 : %s) : ", mdto.getGender());
String gender = sc.nextLine();
if(!gender.equals("")) mdto.setGender(gender);
int result = mdao.update(mdto);
if(result == 1) System.out.println("\n레코드 수정 성공");
else System.out.println("\n레코드 수정 실패");
}
private static void insert() {
MemberDao mdao = MemberDao.getInstance();
Scanner sc = new Scanner(System.in);
MemberDto mdto = new MemberDto();
System.out.print("이름을 입력하세요 : ");
mdto.setName(sc.nextLine());
System.out.print("전화번호를 입력하세요 : ");
mdto.setPhone(sc.nextLine());
System.out.print("생일 입력하세요(YYYY-MM-DD) : ");
String birth = sc.nextLine();
mdto.setBirth(birth);
String year = birth.substring(0,4);
Calendar today = Calendar.getInstance();
int age = today.get(Calendar.YEAR) - Integer.parseInt(year) + 1;
mdto.setAge(age);
System.out.print("성별을 입력하세요 : ");
mdto.setGender(sc.nextLine());
int result = mdao.insert(mdto);
if(result==1) System.out.println("\n레코드 추가 성공");
else System.out.println("\n레코드 추가 실패");
}
private static void select() {
MemberDao mdao = MemberDao.getInstance();
ArrayList<MemberDto> list = mdao.selectAll();
System.out.println("번호\t이름\t\t전화번호\t성별\t나이\t생년월일\t포인트\t가입일");
System.out.println("------------------------------------------------------------------------------------------------------------------");
for(MemberDto mdto : list) {
System.out.printf("%d\t%s\t\t%s\t %s\t %d\t%s\t %d\t%s\n",
mdto.getNum(), mdto.getName(), mdto.getPhone(), mdto.getGender(),mdto.getAge(),
mdto.getBirth(), mdto.getBpoint(), mdto.getJoindate());
}
}
}
에러 없이 끝!!!!