현재 데이터베이스와 자바를 연동하는데, 개념을 잘 모르겠어서 도서관을 왔다. 위의 책의 내용을 정리하고 말할 수 있도록 정리하는 것이 이 글을 쓰는 목표다.
조인이란 여러 테이블에 있는 정보 중 사용자가 필요한 정보만을 가져와 가상의 테이블처럼 만들어서 결과를 보여주는 것으로 n개의 테이블을 조합하여 하나의 열로 표현한 것이다.
INNER JOIN은 키 값이 있는 테이블의 칼럼 값을 비교해서 맞는 값을 가져오는 것.
한마디로 서로 관련된 내용을 검색해 가져오는 방법이다.
내부조인
SELECT 테이블명.칼럼명, ... FROM 테이블명1
INNER JOIN 테이블명2
ON 테이블명1.컬럼명1 = 테이블명2. 컬럼명2;
SELECT (얻을 컬럼) FROM 테이블명1
INNER JOIN 테이블명2 ON (조인 조건);
select *
from A,B
A.칼럼=B.칼럼
외부조인
SELECT 테이블명. 컬럼 명, ... FROM 테이블명1
LEFT OUTER JOIN 테이블명2
ON 테이블명1.컬럼명1 = 테이블명2.컬럼명2;
SELECT (조회 컬럼) FROM 테이블명1
LEFT OUTER JOIN 테이블명2 ON (조인 조건);
SELECT COUNT(*) from employees;
employees 테이블 행의 개수를 반환한다.
SUM함수, AVG함수, GROUP BY 등으로 정렬도 가능하다.
INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...)
VALUES (데이터값1, 데이터값2, 데이터값3, ...)
INSERT INTO 테이블이름
VALUES (데이터값1, 데이터값2, 데이터값3, ...)
위 2가지 모두 같은 결과를 갖지만, 두번째로 할 경우 예상치 못한 오류를 발생할 수 있다. 번거럽더라도 첫번째와 같은 방법으로 데이터를 INSERT하는 습관을 갖자.
UPDATE 예제
UPDATE employees
set home_address - "37, King Street"
where employee_number = 6;
DELETE 예제
DELETE FROM employees WHERE name = "wooyoungwoo";
WHERE 구문을 생략할 경우 모든 행이 삭제된다.
DML은 SELECT, INSERT, UPDATE, DELETE 등의 키워드를 사용해 데이터를 관리하는 데 사용되는 구문의 집합.
DDL은 테이블 구조를 생성하고 관리하는데 사용되는 구문의 집합.
예를들어 테이블을 만드는 쿼리문을 작성해보자
CREATE TABLE meeting_rooms(
meeting_room_id INT,
office_location_id INT,
meeting_room_name VARCHAR(100)
);
열을 추가하는 작업을 해보자
ALTER TABLE meeting_rooms ADD COLUMN telephone_extension VARCHAR(100);
열을 삭제하기 위한 구문을 보자
ALTER TABLE meeting_rooms DROP COLUMN telephone_extension;
예시
CREATE TABLE member
( mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGEND NULL
);
또는 PK를 설정하지 않은 member테이블을 만든 후,
CREATE TABLE member
( mem_id CHAR(8) NOT NULL,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL
);
ALTER TABLE을 이용해 PK를 수정할 수 있다.
ALTER TABLE member
ADD CONSTRAINT
PRIMARY KEY(mem_id);
외래키란?
FOREIGN KEY 제약 조건을 설정한 필드는 외래 키라고 부르며, 한 테이블을 다른 테이블과 연결해주는 역할을 합니다.
외래 키가 설정된 테이블에 레코드를 입력하면, 기준이 되는 테이블의 내용을 참조해서 레코드가 입력됩니다.
즉, FOREIGN KEY 제약 조건은 하나의 테이블을 다른 테이블에 의존하게 만듭니다.
FOREIGN KEY 제약 조건을 설정할 때 참조되는 테이블의 필드는 반드시 UNIQUE나 PRIMARY KEY 제약 조건이 설정되어 있어야 합니다.
FK를 설정해 보자
CREATE TABLE buy
(
num INT AUTO_INCREMENT PRIMARY KEY,
mem_id CHAR(8) NOT NULL,
prod_name CHAR(6) NOT NULL
);
ALTER TABLE buy
ADD CONSTRAINT
FOREIGN KEY(mem_id) REFERENCES member(mem_id);
SELECT * FROM employees WHERE office_location_id = 2;
위 쿼리는 WHERE문이 맞는지 확인하기 위해 모든 행을 순서대로 점검한다. 테이블에 수천 개의 행이 있다면 이 쿼리는 실행하는 데 시간이 많이 소요된다. 특히 조건에 맞는 쿼리가 없으면 더 그렇다.
ACID
A(Atomic) - 원자성
트랜잭션에 예제를 살펴보도록 하자.
일관성과 격리 내구성에 대한 것은 예제를 통해 알아보기가 쉽지 않다고 한다.
원자성에 대해서만 예제를 다루어보자.
https://www.sqlitetutorial.net/sqlite-transaction/ 의 코드를 참고했다.
CREATE TABLE accounts (
account_no INTEGER NOT NULL,
balance DECIMAL NOT NULL DEFAULT 0,
PRIMARY KEY(account_no),
CHECK(balance >= 0)
);
CREATE TABLE account_changes (
change_no Integer PRIMARY KEY auto_increment,
account_no INTEGER NOT NULL,
flag TEXT NOT NULL,
amount DECIMAL NOT NULL,
changed_at TEXT NOT NULL
);
INSERT INTO accounts (account_no,balance)
VALUES (100,20100);
INSERT INTO accounts (account_no,balance)
VALUES (200,10100);
START TRANSACTION;
으로 트랜젝션을 실행한다.START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE account_no = 100;
UPDATE accounts
SET balance = balance + 1000
WHERE account_no = 200;
INSERT INTO account_changes(account_no,flag,amount,changed_at)
VALUES(100,'-',1000,now());
INSERT INTO account_changes(account_no,flag,amount,changed_at)
VALUES(200,'+',1000,now());
COMMIT;
SELECT * FROM account_changes;
JDBC(Java Database Connectivity)는 데이터베이스에 연결하기 위해 표준 자바 라이브러리에 내장된 메커니즘이다.
데이터베이스에 연결하기 위해 JDBC를 이용할 때는 벤더의 JDBC 구현 라이브러리가 클래스 패스에 있는지 확인해야 한다.
자바6 이전 버전에서는 JDBC 드라이버가 Class.forName() 호출을 통해 클래스 로더에 의해 로드됐는지 확인해야 한다.
그레이들을 이용해서 쉽게 jar파일을 불러 올 수도 있다.
import java.sql.*;
Connection c = null;
try{
Class.forName("com.mysql.cj.jdbc.Driver");
}catch(ClassNotFoundException ex){
System.out.println("JDBC error" + ex.getMessage());
ex.printStackTrace();
}
Class.forname
을 이용해 구현라이브러리가 있는지 확인하자. try{
c = DriverManager.getConnection("jdbc:mysql://localhost:3306/classicmodels","root","gkseogh1!23");
String sql = "select employeeNumber, lastName, firstName, extension, email, officeCode, reportsTo, jobTitle from employees";
try(PreparedStatement ps = c.prepareStatement(sql)){
ResultSet rs = ps.executeQuery();
while(rs.next()) {
int employeeNumber = rs.getInt("employeeNumber");
String lastName = rs.getString("lastName");
String firstName = rs.getString("firstName");
String extension = rs.getString("extension");
String email = rs.getString("email");
String officeCode = rs.getString("officeCode");
int reportsTo = rs.getInt("reportsTo");
String jobTitle = rs.getString("jobTitle");
System.out.println(employeeNumber + "," + lastName + ","+ firstName + ","+ extension + ","+ email +"," + officeCode +","+reportsTo+","+jobTitle);
}
rs.close();
}
}catch(SQLException ex){
ex.printStackTrace();
}
애플리케이션은 종종 데이터베이스 커넥션 풀을 관리하는데, 스레드풀과 비슷하다. 몇몇 오픈 소스 라이브러리는 커넥션 풀을 생성해서 작업하는데, 이는 모두 다음 접근 방법을 이용한다.
참고글 : https://helloworld92.tistory.com/34
https://araikuma.tistory.com/724?category=874473
http://www.tcpschool.com/mysql/mysql_constraint_foreignKey
트랜잭션은 무엇인가?
https://coding-factory.tistory.com/226
https://www.youtube.com/watch?v=0DnuiH_nnoQ
https://www.sqlitetutorial.net/sqlite-transaction/