2023.04.19 59일차 SpringBoot
59일차
db의 데이터를 조작할 수 있다.
17. 트랜젝션
하나의 업무가 여러 insert update delete단위로 이루어질 수 있다.
//이체 5000원 (A -> B)
UPDATE Bank SET money = money - 5000
WHERE customerName = 'A';
UPDATE Bank SET money = money + 5000
WHERE customerName = 'B';하나의 업무가 완전히 실행되어야 한다.
성공하면 commit; 실패하면 rollback;해야한다.
COMMIT : 트랜젝션완료
ROLLBACK : 트랜젝션 실패(트랜젝션 시작완료 이전으로 돌아가기)
자동 커밋 (autocommit)
SET autocommit = 0; -- 자동 커밋 비활성화(disable)
SET autocommit = 1; -- 자동 커밋 활성화 (enable)
이체시 오류(A->B)
UPDATE Bank SET money = money - 5000
WHERE customerName = 'A';
-> 오류 생겼다고 가정
ROLLBACK;
트렌잭션 중간에 오류가 생기면 ROLLBACK을 해야한다.
17.1 JDBC 트랜젝션
//트랜젝션 없이 실행
@RequestMapping("link1")
public void method1() {
String sql1 = "UPDATE Bank SET money = money - 5000 "
+ "WHERE customerName = 'A'";
String sql2 = "UPDATE Bank SET money = money + 5000 "
+ "WHERE customerName = 'B'";
try {
Connection con = DriverManager.getConnection(url, dbId, pwd);
Statement stmt3 = con.createStatement();
Statement stmt1 = con.createStatement();
Statement stmt2 = con.createStatement();
stmt1.executeUpdate(sql1);
stmt2.executeUpdate(sql2);
System.out.println("이체완료");
} catch (Exception e) {
e.printStackTrace();
}
}일부러 중간에 예외를 발생시키면 이체실패가 발생한다.
그러면 돈빼는 것만 처리되고 돈이 들어오는 처리가 발생하지않아 문제가 생긴다.
A계좌에서만 돈이빠지고 B는 처리를 받지 못하게 된다
stmt1.executeUpdate(sql1); // 성공
//exception 발생
int a = 3 / 0;
stmt2.executeUpdate(sql2); // 실패17.2 JDBC 트랜젝션
커넥션의 con.setAutoCommit(false); 을 false로 하면 오토커밋이 비활성화된다.
이후 con.commit();하면 커밋이되고 예외발생시 con.rollback();하면 롤백이된다.
// 트랜젝션 설정 후 모두 실패
@RequestMapping("link4")
public void method4() throws Exception {
String sql1 = "UPDATE Bank SET money = money - 5000 "
+ "WHERE customerName = 'A'";
String sql2 = "UPDATE Bank SET money = money + 5000 "
+ "WHERE customerName = 'B'";
Connection con = DriverManager.getConnection(url, dbId, pwd);
try (con;
Statement stmt1 = con.createStatement();
Statement stmt2 = con.createStatement();) {
con.setAutoCommit(false);
stmt1.executeUpdate(sql1);
//exception 발생
int a = 3 / 0;
stmt2.executeUpdate(sql2);
con.commit();
System.out.println("이체완료");
} catch (Exception e) {
System.out.println("이체실패");
con.rollback();
e.printStackTrace();
}
}18. create table
데이터를 설계할 수 있어야한다.
TABLE 이름의 작성규칙은 회사마다 다르다. 우리 과정에서는 UpperCamleCase
버전마다 대소문자 구분 규칙 등이 다 다르니 확인하고 사용해야한다.
CREATE TABLE 테이블이름 (
-- 컬럼 정의
-- 컬럼명 값의 TYPE, ...
-- 컬럼명 작성 규칙(회사 규칙 따르기)
-- 우리과정에서는 UpperCamelCase(대소문자 구분안함)
);
CREATE TABLE MyTable01 (
Col1 INT,
Col2 VARCHAR(255)
);18.1 data -type
컬럼의 타입이 결정되면 타입에 맞는 값만 들어갈 수 있다.
크게보면 Numeric (수), Character(String) 문자형, 날짜시간으로 나뉜다.
DB마다 자료형의 용어가 조금씩 다르므로 알고 있자.
18.2 Character(String) 문자형
18.2.1 VARCHAR(길이)
VARCHAR(길이) 길이가 가변적인 문자열이다.
길이에 맞지않게 넣으면 DB에 따라서 실패하거나 잘라서 들어간다.
테이블이 다음과 같을 때 설정해보도록 하자.
메뉴얼대로는 65000자 정도 까지 가능하다.
CREATE TABLE MyTable02 (
Col1 VARCHAR(3),
Col2 VARCHAR(5)
);
INSERT INTO MyTable02 VALUES('abc', 'abcde');
INSERT INTO MyTable02 VALUES('ab', 'abcd'); -- 작으면 당연히 가능
INSERT INTO MyTable02 VALUES('abcd', 'abcdef'); -- fail연습 MyTable03 3개의 컬럼
첫번째컬럼 Type : 문자열 10개까지 저장가능
두번째컬럼 Type : 문자열 100개까지 저장가능
세번째컬럼 Type : 문자열 1000개까지 저장가능
CREATE TABLE MyTable03 (
NAME VARCHAR(10),
TITLE VARCHAR(100),
CONTENT VARCHAR(1000)
);18.3 Numeric
Numeric (수)
크게 int 정수형과 decimal, dec 실수형이 있다.
18.3.1 int
int() 최대값 2147483647 / 최소값 -2147483648 4바이트 정수 integer
CREATE TABLE MyTable04 (
Col1 INT
);
INSERT INTO MyTable04 VALUES (33); -- OK
INSERT INTO MyTable04 VALUES (3.14); -- 소수점 이하 생략 후 3으로 들어감연습 MyTable05 만들기 두개의 컬럼이 모두 정수 저장 가능
CREATE TABLE MyTable05 (
Col1 INT,
Col2 INT
);18.3.2 DEC 실수형
DEC(총길이, 소수점이하길이)
CREATE TABLE MyTable06 (
Col1 DEC(5,2),
Col2 DEC(3,1)
);
INSERT INTO MyTable06 (Col1) VALUES (999.99); -- ok
INSERT INTO MyTable06 (Col1) VALUES (-999.99); -- ok
INSERT INTO MyTable06 (Col1) VALUES (1000.99); -- fail
INSERT INTO MyTable06 (Col1) VALUES (999.999); -- fail
INSERT INTO MyTable06 (Col1) VALUES (99.999); -- 100.00
INSERT INTO MyTable06 (Col1) VALUES (10.8888); -- 10.89총 길이를 5자 넘으면 오류인데 소수점이 늘어나면 반올림 해버린다.
연습 MyTable07 만들기
두개 컬럼 (실수형) 총길이 소수점 이하 길이 직접 작성
CREATE TABLE MyTable07 (
Col1 DEC(3, 2),
Col2 DEC(5, 3)
);18.4 날짜형
DATE 날짜 (YYYY-MM-DD)
DATETIME 날짜시간(YYYY-MM-DD hh:mm:ss)
1000-01-01~9999-12-31까지 작성가능하다.
CREATE TABLE MyTable08 (
Col1 DATE,
Col2 DATETIME
);
INSERT INTO MyTable08 (Col1) VALUES ('2023-04-19');
INSERT INTO MyTable08 (Col1) VALUES ('9999-12-31');
INSERT INTO MyTable08 (Col1) VALUES ('1000-01-01');
INSERT INTO MyTable08 (Col2) VALUES ('2023-04-19 11:41:30');
INSERT INTO MyTable08 (Col2) VALUES ('9999-12-31 23:59:59');
INSERT INTO MyTable08 (Col2) VALUES ('1000-01-01 00:00:00');오늘 날짜 : NOW();
INSERT INTO MyTable08 (Col1, Col2) VALUES (now(), now());예제 MyTable09 반 학생의 정보
학생이름, 성별, 생년월일, 기타정보, 시험성적
필요한정보의 크기나 타입을 결정해서 설계를 해야한다.
CREATE TABLE MyTable09 (
Name VARCHAR(20),
Gender VARCHAR(1),
BirthDate DATE,
Score DEC(3, 2),
Extra VARCHAR(1000)
);연습 내가 가지고 있는 교재 정보 저장 MyTable10
책제목 저자 출판사 발행날짜 가격 기타
CREATE TABLE MyTable10 (
Title VARCHAR(50),
Writer VARCHAR(20),
Publisher VARCHAR(20),
Published DATE,
Price INT,
Extra VARCHAR(100)
);19 Database
테이블을 만들었는데 테이블들이 어디에 만들어지는가?
Database에 만들어진다.
DATABASE는 Schema라고도 한다. 테이블이 있는곳이다.
19.1 DB만들기
CREATE DATABASE db이름하면 database 만들어진다.
CREATE DATABASE mydb;
USE mydb; -- 사용19.2 DB삭제
매우 많이 주의하기
아래의 테이블, 데이터 모두 날아가니 주의하기
DROP DATABASE 데이트베이스명
연습
mydb1 데이터베이스 만들기
mydb1에 새로운 테이블 만들기
mydb1 데이터베이스 삭제하기
CREATE DATABASE mydb1;
CREATE TABLE MyTable01(
Title VARCHAR(10),
Price INT
);
DROP DATABASE mydb1;다른 DB의 테이블 선택
SELECT * FROM DB이름.테이블이름; 하면된다.
SELECT * FROM MyTable10; -- 현재 DB
SELECT * FROM w3schools.MyTable10 -- 다른 DB20 테이블 지우기
DROP TABLE 테이블이름 하면 테이블이 삭제가 된다.
역시나 데이터를 지우는 행위이기 때문에 매우 주의해서 사용하자.
DROP TABLE MyTable01;
DROP TABLE MyTable02;
DROP TABLE MyTable03;이와 유사한 명령어는 다음과 같다.
복구 불가능하기 때문에 매우 주의하자 왠만해선 사용하지말자
TRUNCATE : 테이블 유지 데이터 삭제
TRUNCATE TABLE MyTable04;21 테이블 복사하기
구조를 보고 똑같이 배껴써도 되지만 불편하니 테이블 내용을 복사할 수 있다.
SHOW CREATE TABLE 테이블이름 : 테이블 생성 명령 보기
해당 테이블의 구조를 만들때 사용된 명령어가 나온다.
SHOW CREATE TABLE MyTable10;다음과 같은 방법도 있다.
완번히 똑같진 않지만 컬럼과 타입이 들어가고 데이터도 같이 복사된다.
데이터 삭제 복사 등의 작업을 할때 임시테이블을 사용하고 싶다면 이것을 사용하면 될 것 같다.
CREATE TABLE 새테이블이름 AS SELECT * FROM 원본테이블이름;
22 테이블 수정
이미 만들어져 있는 테이블을 변경하고 싶을때 사용한다.
ALTER TABLE : 테이블을 변경한다.
컬럼추가, 컬럼명 변경 등등
ALTER TABLE 테이블이름
변경키워드 내용
22.1 컬럼 추가 ADD COLUMN
ALTER TABLE MyTable13
ADD COLUMN Col3 VARCHAR(10);
ALTER TABLE MyTable13
ADD COLUMN Col4 DEC(10,2);MyTable13 5번째 컬럼추가하기
ALTER TABLE MyTable13
ADD COLUMN Col5 INT;마지막 컬럼에 추가가 아니라 중간에 삽입할 수도 있다.
첫 번째 컬럼으로 추가 하기
ADD COLUMN 컬럼명 타입 FIRST
ALTER TABLE MyTable13
ADD COLUMN Col6 INT FIRST;중간에 컬럼 추가하기
ADD COLUMN 컬럼명 타입 AFTER 기준컬럼명
ALTER TABLE MyTable13
ADD COLUMN Col8 INT AFTER Col7;연습 문제
첫번째 컬럼으로 하나 추가
컬럼을 중간에 추가
ALTER TABLE MyTable13
ADD COLUMN Col9 INT FIRST;
ALTER TABLE MyTable13
ADD COLUMN Col10 INT AFTER Col9;22.2 컬럼 삭제
ALTER TABLE 테이블이름 DROP COLUMN 컬럼명;
으로 컬럼을 삭제할 수 있다. 데이터가 삭제되기 때문에 주의하자!!
ALTER TABLE MyTable13
DROP COLUMN Col1;
ALTER TABLE MyTable13
DROP COLUMN Col2;22.3 컬럼 변경
ALTER TABLE 테이블 이름 CHANGE COLUMN 원본컬럼 새컬럼 타입;
이것또한 위험하니 조심해서 사용해야한다.
ALTER TABLE MyTable13
CHANGE COLUMN Col3 Col13 VARCHAR(20); -- 위험!!만약 데이터 타입이 맞지 않은 데이터가 들어 있는데 변경한다면 에러 발생하거나 데이터가 손실된다.
INSERT INTO MyTable13 (Col13) VALUES ('가나다라마바사');
ALTER TABLE MyTable13
CHANGE COLUMN Col13 Col3 VARCHAR(3); -- 에러연습 Col10을 Col20 VARCHAR(20)으로 변경
ALTER TABLE MyTable13
CHANGE COLUMN Col10 Col20 VARCHAR(20);22.4 컬럼 데이터 타입변경
컬럼명은 그대로두고 컬럼데이터 타입만 변경할 수 있다.
ALTER TABLE 테이블 이름 MODIFY COLUMN 컬럼 새타입;
만약 데이터 타입이 맞지 않은 데이터가 들어 있는데 변경한다면 에러 발생하거나 데이터가 손실된다.
ALTER TABLE MyTable13
MODIFY COLUMN Col20 VARCHAR(40);
ALTER TABLE MyTable13
MODIFY COLUMN Col13 VARCHAR(3); -- 에러, 위험연습 아무 컬럼이나 타입 변경하기
ALTER TABLE MyTable13
MODIFY COLUMN Col9 VARCHAR(20);타입 변경 뿐만이 아니라 특정 위치로 변경할 수도 있다.
ALTER TABLE MyTable13
MODIFY COLUMN Col8 VARCHAR(9) FIRST;연습 MyTable13 의 아무 컬럼의 위치 변경하기
ALTER TABLE MyTable13
MODIFY COLUMN Col20 INT AFTER Col13;23 default
DEFAULT 키워드로 기본값을 정할 수 있다.
CREATE TABLE MyTable14 (
Col1 INT,
Col2 INT DEFAULT 99
);
INSERT INTO MyTable14 (Col1, Col2) VALUES (100,200); -- 100 , 200
INSERT INTO MyTable14 (Col1) VALUES (100); -- 100, 99
INSERT INTO MyTable14 (Col2) VALUES (200); -- NULL, 200연습 MyTable15 테이블에 3개 행 추가
Col1 Col2에 모두 값 넣기
Col2에만 값넣기
Col1에만 값 넣기
CREATE TABLE MyTable15 (
Col1 VARCHAR(30),
Col2 VARCHAR(30) DEFAULT '기본'
);
INSERT INTO MyTable15 (Col1, Col2) VALUES ('HI', 'HELLO'); -- HI, HELLO
INSERT INTO MyTable15 (Col2) VALUES ('GO HOME'); -- NULL, GO HOME
INSERT INTO MyTable15 (Col1) VALUES ('Surrender'); -- Surrender, 기본24 NOT NULL
기본값 없이 NULL값으로 들어가는게 마음이 들지 않는다면 NULL이 아니게 지정할 수있다.
NOT NULL : NULL허용안함
CREATE TABLE MyTable17 (
Col1 INT,
Col2 INT NOT NULL
);
INSERT INTO MyTable17 (Col1, Col2) VALUES (100,200); -- OK
INSERT INTO MyTable17 (Col2) VALUES (200); -- OK
INSERT INTO MyTable17 (Col1) VALUES (100); -- fail연습 MyTable18 두개 컬럼, VARCHAR(20) 두번째 컬럼만 NOT NULL 제약사항 적용
CREATE TABLE MyTable18 (
Col1 VARCHAR(20),
Col2 VARCHAR(20) NOT NULL
);딱히 이유가 없다면 NOT NULL로 해주자.
DEFAULT와 NOT NULL은 다르다. 그냥 DEFAULT키워드만 사용하면 NULL허용하고 DEFAULT가 된다.
명시적으로 NULL값을 넣는것이 가능하기때문에 다른 속성이다.
CREATE TABLE MyTable19 (
Col1 INT DEFAULT 0,
Col2 INT NOT NULL
);
INSERT INTO MyTable19 (Col1, Col2) VALUES (100,200); -- OK
INSERT INTO MyTable19 (Col2) VALUES (200); -- OK
INSERT INTO MyTable19 (Col1) VALUES (100); -- fail
INSERT INTO MyTable19 (Col1, Col2) VALUES (NULL,200); -- OKNOT NULL DEFAULT은 조합이 가능하다
CREATE TABLE MyTable19 (
Col1 INT, -- NULL 허용 DEFAULT 없음
Col2 INT NOT NULL, -- NULL 허용안함 DEFAULT없음
Col3 INT DEFAULT 0, -- NULL 허용 DEFAULT 0
Col4 INT NOT NULL DEFAULT 0 -- NULL 허용안함 DEFAULT 0
);25 UNIQUE
그 컬럼의 값이 여러행에서 중복이 될 수 있는가 없는가의 키워드이다.
값을 중복허용하지 않는다.
CREATE TABLE MyTable21 (
Col1 INT,
Col2 INT UNIQUE
);
INSERT INTO MyTable21 (Col1, Col2) VALUES (1,1); -- ok
INSERT INTO MyTable21 (Col1, Col2) VALUES (2,2); -- ok
INSERT INTO MyTable21 (Col1, Col2) VALUES (2,3); -- ok
INSERT INTO MyTable21 (Col1, Col2) VALUES (4,3); -- failUNIQUE 컬럼으로 인해서 다르다는게 보장이 된다.
여러 행을 구분할 수 잇는 값을 KEY라고 한다.
구분하기 위해서 중복값이 포함되지 못하도록 하려면 UNIQUE제약사항을 붙여주자.
그런데 UNIQUE라고 해서 NULL을 허용안하는 것은 아니다.
NULL은 값이 없는 것이지 중복된 값이 아니다.
INSERT INTO MyTable22 (Ssn, Name) VALUES ('1', '정대만'); -- ok
INSERT INTO MyTable22 (Ssn, Name) VALUES ('1', '송태섭'); -- fail
INSERT INTO MyTable22 (Ssn, Name) VALUES (NULL, '채소연'); -- OK
INSERT INTO MyTable22 (Ssn, Name) VALUES (NULL, '강백호'); -- OK26 PRIMARY KEY
NOT NULL + UNIQUE인 키를 PRIMARY KEY(기본키)라고 한다.
CREATE TABLE MyTable23(
Col1 VARCHAR(20) NOT NULL UNIQUE DEFAULT ''
);
CREATE TABLE MyTable24(
Col1 VARCHAR(20) PRIMARY KEY -- NOT NULL + UNIQUE
);제약사항을 따로 지정할 수도 있다.
CREATE TABLE MyTable25(
Col1 INT,
PRIMARY KEY (Col1)
);두 개의 컬럼을 조합해서 PRIMARY KEY로 지정할 수 있다.
조합해서 중복이 아니라면 같은 값이 들어갈 수도 있다.
CREATE TABLE MyTable26(
Col1 INT,
Col2 INT,
PRIMARY KEY (Col1, Col2)
);
INSERT INTO MyTable26 (Col1, Col2) VALUES (1,1); -- OK
INSERT INTO MyTable26 (Col1, Col2) VALUES (2,2); -- OK
INSERT INTO MyTable26 (Col1, Col2) VALUES (1,2); -- OKPRIMARY KEY 제약사항은 식별하는 유일한 값이어야 하기때문에 한테이블에 한개만 가능하다.
CREATE TABLE MyTable26(
Col1 INT,
Col2 INT,
PRIMARY KEY (Col1),
PRIMARY KEY (Col2)
); -- fail27. 제약사항 (CONSTRAINTS)
DEFAULT, UNIQUE, NOT NULL, PRIMARY KEY
각 컬럼에 적절한 제약사항을 주고 싶다면 이런 제약사항들을 이용해야한다.
테이블을 만들때에만 제약사항을 줄 수 있는 것은 아니다.
원래 잇는 테이블에 ALTER TABLE를 통해 제약사항을 추가할 수 있다.
CREATE TABLE MyTable28 (
Col1 INT,
Col2 INT,
Col3 INT,
Col4 INT
);
ALTER TABLE MyTable28
MODIFY COLUMN Col1 INT UNIQUE;
ALTER TABLE MyTable28
MODIFY COLUMN Col2 INT NOT NULL;
ALTER TABLE MyTable28
MODIFY COLUMN Col3 INT DEFAULT 0; -- Col3에 default 0 제약사항 추가
ALTER TABLE MyTable28
MODIFY COLUMN Col4 INT NOT NULL UNIQUE DEFAULT 100; -- Col4에 not null unique defulat 100 제약사항 추가PRIMARY KEY를 추가하려면 ALTER TABLE ADD PRIMARY KEY를 사용하면된다.
CREATE TABLE MyTable29 (
Col1 INT,
Col2 INT,
Col3 INT
);
ALTER TABLE MyTable29
ADD PRIMARY KEY (Col1, Col2);제약사항을 추가할 시 주의할점은 이미 제약사항을 어기고 있는 데이터가 있다면 제약사항 추가가 되지않는다.
이런 경우에는 데이터를 정리하고 추가해야한다.
2023.04.19
테이블 복사를 통해 임시테이블을 만들어서 테스트를 해보는 방법을 배웟다.
데이터 관리를 더 안전하게 할 수 있을 것 같다.
어떻게 하면 테이블을 잘만드는가 정규화 내일이어서 배울 것이다.
가장 중요한 부분인 것 같다 데이터를 설계하고 만드는 방법을 알아야한다.