MySQL 문법
*distinct 중복제거하기
select distinct job_id from employees;
*두가지 기준으로 정렬해주기
select * from employees where department_id in (90, 60)
order by department_id desc, salary asc;department_id로 내림차순 정렬 후, department_id가 같은 employees 내에서 salary로 오름차순 정렬
*세로로 결합해주기
주의점) 개수와 타입이 다른경우 결합X
union은 수행에 있어서 속도가 느리기때문에 where절을 사용하는 것이 더 빠르다.
union(중복제거)
(select 'A', 'B', 'C')
union
(select 'D', 'E', 'F')
union
(select 'G', 'H', 'I')
union
(select 'A', 'B', 'C');
union all(중복허용)
(select 'A', 'B', 'C')
union all
(select 'D', 'E', 'F')
union all
(select 'G', 'H', 'I')
union all
(select 'A', 'B', 'C');
*group
select department_id, count(*) from employees group by department_id;-- job_id로 분류(그룹)지어 개수 세기
select job_id, count(*) from employees group by job_id;
-- 직책별 평균 연봉 구하기, 직책별 최대연봉, 최소연봉
select job_id, avg(salary), max(salary), min(salary)
from employees
where job_id = 'it_prog'
group by job_id;
*having
where은 테이블에 대한 조건
having은 그룹에 대한 조건
select last_name, count(*)
from employees
group by last_name
having count(*) > 1;
임시테이블 생성
create temporary table if not exists book (
no int primary key auto_increment
, title varchar(25)
, price int
);접속한 동안에만 존재, 접속끊으면 사라짐, 임시테이블
SQL 구문
SQL(Structured Query Language)은 데이터베이스에서 데이터를 정의, 조작, 제어하기 위해 사용하는 언어입니다.
따라서 SQL 구문도 위의 목적에 맞게 크게 세 가지로 구분할 수 있습니다.
1. DDL(Data Definition Language)
2. DML(Data Manipulation Language)
3. DCL(Data Control Language)
*속성설명주요 명령어
| DDL | 데이터베이스나 테이블 등을 생성, 삭제하거나 그 구조를 변경하기 위한 명령어 | CREATE, ALTER, DROP |
| DML | 데이터베이스에 저장된 데이터를 처리하거나 조회, 검색하기 위한 명령어 | INSERT, UPDATE, DELETE, SELECT 등 |
| DCL | 데이터베이스에 저장된 데이터를 관리하기 위하여 데이터의 보안성 및 무결성 등을 제어하기 위한 명령어 | GRANT, REVOKE 등 |
MySQL 쿼리 문법 순서
1. 작성 순서
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
2. 실행 순서
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
연산
select 10000000000 + '1'; -- 10000000001
select '444' + 33; -- 477
select '453' + '123'; -- 576
형변환
select cast('444' as signed) - 33; -- 411
select convert('444', signed) - 33; -- 411
수학함수
select abs(-33); -- 33 // 절대값
select round(123.44, 1); -- 123.4 // 반올림
select ceil(123.44); -- 124 // 올림
select floor(123.44); -- 123 // 버림
select pow(5, 3); -- 125 // 5의 3제곱
select rand(); -- 랜덤값
select pi(); -- 파이값### 숫자 관련 함수
ABS : 절대값을 구하는 함수
ROUND : 반올림값을 구하는 함수
CEIL : 소수점을 올림해서 반환
FLOOR : 소수점 버림해서 반환
POW : 숫자의 n승을 가져오는 함수 (제곱)
MOD (나머지) : 분자를 분모로 나눈 나머지를 가져오는 함수 (% 연산자와 동일)
GREATEST : 주어진 수 중 제일 큰 수를 가져오는 함수
LEAST : 주어진 수 중 제일 작은 수를 가져오는 함수
문자열 함수
* 공백제거
select trim(' 234 '); -- 공백제거
select ltrim(' 234 '); -- 왼쪽공백제거
select rtrim(' 234 '); -- 오른쪽공백제거
*대소문자 변환
select lower('ABCD');
select upper('abcd');
*문자열 뒤집기
select reverse('뒤집기');
*문자열 결합하기
select concat('문자열', '여러개', '결합');
select concat('값 중에', null, '값이 있으면?'); -- 전체 결과 null
*특정문자 변환
select replace('원본 문자열에서 변환합니다.', '변환', 'replace');
*문자열 채우기
select lpad('123', 5, '0'); -- 5는 만들고자하는 문자길이 // 길이 모자라면 왼쪽을 '0'으로 채우기
select rpad('456', 5, '*'); -- 5는 만들고자하는 문자길이 // 길이 모자라면 오른쪽을 '*'으로 채우기
*위치 찾기
select locate('D', 'ABCDEFG'); -- 4
select locate('A', 'ABABAB', 2); -- 3
*문자열 자르기
select substring('employees', 3);
select substring('employees', 3, 3); -- 3번째부터 3개 자르기
select substring('employees', -3); -- 음수(왼쪽부터)
select substring('employees', -3, 3);
*null 대체
select ifnull(null, '왼쪽값이null이면대체');
select coalesce(null, null, null, '나열된값중null아닌첫번째값으로', null);### 문자열 관련 함수
문자열 표현 최대 길이 ? 255, 더 긴 텍스트는 TEXT 사용하자
고정길이 문자열의 경우 > 공백이 포함될 수 있음.
TRIM : 문자열의 좌우의 공백을 제거해주는 함수
LTRIM : 문자열의 왼쪽의 공백을 제거해주는 함수
RTRIM : 문자열의 오른쪽의 공백을 제거해주는 함수
UPPER, LOWER : 문자열을 모두 대문자, 소문자로 바꿔서 보여주는 함수
CHAR_LENGTH : 문자열의 길이를 구해주는 함수
REVERSE : 문자열의 순서를 뒤집어주는 함수
CONCAT : 문자열 결합해주는 함수 (NULL이 포함된다면 전체 결과가 NULL)
REPLACE (ⓐ, ⓑ, ⓒ) : 특정 문자열을 찾아서 변환하는 함수. ⓐ문자열에서, ⓑ문자열을, ⓒ로 변경
LPAD(ⓐ, ⓑ, ⓒ) : 문자열의 왼쪽을 특정 문자열로 채워주는 함수. ⓐ문자열이, ⓑ개보다 짧다면 , 왼쪽을 ⓒ로 채워준다.
RPAD(ⓐ, ⓑ, ⓒ) : 문자열의 오른쪽을 특정 문자열로 채워주는 함수. ⓐ문자열이, ⓑ개보다 짧다면 , 오른쪽을 ⓒ로 채워준다.
LOCATE(ⓐ, ⓑ) : ⓑ에서 ⓐ의 인덱스를 알려주는 함수, 인덱스를 1부터 센다 !
LOCATE(ⓐ, ⓑ, ⓒ) : ⓑ에서 ⓒ번째 ⓐ의 인덱스를 알려주는 함수
SUBSTRING(ⓐ, ⓑ) : ⓐ 문자열에서 ⓑ부터 짜르기 예) employees, 3 → ployees
SUBSTRING(ⓐ, ⓑ, ⓒ) : ⓐ 문자열에서 ⓑ부터 ⓒ 까지 짜르기 예) employees, 3, 3 → plo
SUBSTRING(ⓐ, ⓑ) : ⓑ 가 음수라면 끝에서 짜른다. 예) employees, -3 → ees
날짜와 시간 함수
*날짜, 시간
-- date 날짜
-- time 시간
-- datetime 날짜시간 → 문자열처럼 다루기, 비효율적(용량 많이 사용)
-- timestamp 날짜시간 → 숫자처럼 다루기(기준이 되는 시간으로 날짜 지정, 좀 더 많은 날짜표현 가능, 시간값이 반드시 존재(기본값 설정 - 보통은 현재시간))
select current_date(); -- 2022-12-26
select current_time(); -- 12:46:21
select current_timestamp(); -- 2022-12-26 12:46:37select date '2022-12-26';
select date '2022/12/26';
select '2022-12-55'; -- date가 아닌 문자열로 값을 다룸
select date '2022-12-55'; -- date 범위 벗어나므로 X
select time '12:48:33';
*날짜 형변환
select cast('2022-12-26' as date);
select convert('2022-12-26', date);
*날짜 계산
-- day, week, month, year
select date '2022-12-26' + interval 7 day; -- 2023-01-02
select date '2022-12-26' + interval 7 month; -- 2023-07-26
select date '2022-12-26' + interval 7 year; -- 2029-12-26
select date '2022-12-26' - interval 7 day; -- 2022-12-19
*날짜 더하기
select adddate('2022-12-26', 1); -- 2022-12-27 일(day)만큼 더하기
select date_add('2022-12-26', interval 1 day); -- 2022-12-27
*날짜 빼기
subdate, date_sub
*일수 계산
select datediff('2022-06-26', '2022-12-26'); -- 두 날짜 사이 간격(-183)
select datediff('2022-12-26', '2022-06-26'); -- 183
*날짜 중 원소 추출
select extract(year from '2022-06-26'); -- 2022
*특정 날짜 요일
select dayofweek('2022-12-26'); -- 2(1:일요일)
이클립스에서 JDBC(Java DataBase Connecitiviy) 등록
JDBC는 자바 프로그램 내에서 DB와 관련된 작업을 처리할 수 있도록 도와주는 API이다.
1. JDBC 설치하기
https://dev.mysql.com/downloads/file/?id=513754
MySQL :: Begin Your Download
The world's most popular open source database Contact MySQL | Login | Register
dev.mysql.com
홈페이지 접속 > JDBC Driver for MySQL (Connector/J) > Platform Independent > Platform Independent (Architecture Independent), ZIP Archive Download > No thanks, just start my download.
2. 이클립스에서 jar파일 연동
프로젝트 우클릭 > Properties > Java Build Path > Add External JARs > 다운로드 받은 jar 파일 선택 > Apply and Close
JDBC를 이용한 데이터베이스 사용 절차
JDBC를 이용하여 데이터베이스를 사용하는 전형적인 절차는 다음과 같다.
1. URL로 지정된 JDBC 드라이버를 적재(load)한다.
Class.forName("com.mysql.cj.jdbc.Driver");2. 사용자 이름과 패스워드를 가지고 데이터베이스에 연결한다.
Connection con = DriverManager.getConnection(url, user, pw);3. SQL 문장을 작성하여 전송하고 실행한다. SQL 명령어의 결과로 생성되는 결과 집합을 얻는다.
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM books");4. 결과 집합을 화면에 표시하거나 결과 집합을 처리한다. 사용이 끝나면 연결을 해제한다.
while (rs.next()) {
int number = rs.getInt("book_id");
String name = rs.getString("title");
}
드라이버 적재(클래스 로드)
드라이버 적재는 최초 1회만 이루어지면 된다.
Class.forName() 클래스 로드를 위한 스테틱 메소드이다.
ClassNotFoundException 예외 발생 가능성이 있으므로 try-catch 로 예외감싸준다.
*MySQL driver class name : "com.mysql.cj.jdbc.Driver"
Connection
Connection 클래스는 외부객체 연결시켜준다.
접속을하고 사용이 끝나면 접속을 해제해줘야한다.(메모리 공간 차지) → close() 메소드를 사용
*DriverManager.getConnection();
등록시킨 드라이버를 관리한다. 로드시킨 드라이버를 커넥션 만들어 달라고 한다.
파라미터는 주소와 아이디, 비밀번호를 순서대로 전달
*String url = "jdbc:mysql://localhost:3306";
jdbc:mysql -> 프로토콜
localhost -> 내컴퓨터(달라질 수 있음)
3306 -> 포트번호(달라질 수 있음, 기본값 : 3306)
*접속(연결) 실패
서버가 꺼져있는 경우
서버위치 주소가 잘못된 경우
포트번호가 잘못된 경우
아이디, 비밀번호 잘못된 경우
Statement
Statement (java.sql.Statement)는 Connection으로 연결한 객체에게, Query 작업을 실행하기 위한 객체.
데이터베이스 연결로부터 SQL문을 수행할 수 있도록 하는 클래스이다.
*executeUpdate(String sql) : 반환값 int / 생성, 변경, 삭제할 때 사용
*executeQuery(String sql) : 반환값 ResultSet / select문 조회할때 사용
데이터베이스 연결
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Main {
public static void main(String[] args) {
// 데이터베이스 서버에 접속(연결)을 원함.
// 주소
String url = "jdbc:mysql://localhost:3306";
// 아이디
String id = "root";
// 비밀번호
String password = "root";
// 드라이버 적재(클래스 로드)
try {
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("드라이버 적재 성공");
} catch (ClassNotFoundException e) {
System.out.println("드라이버를 찾을 수 없습니다.");
}
Connection conn = null;
try {
conn = DriverManager.getConnection(url, id, password);
System.out.println("데이터베이스 연결 성공");
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println("접속(연결)에 실패하였습니다.");
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
try (Connection anotherConn = DriverManager.getConnection(url, id, password)) {
System.out.println("데이터베이스 연결 한번 더");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
