2023.04.24 62일차 SpringBoot
62일차
29.20 마이바티스 update - 2
직원 조회 및 수정 코드 작성하기
먼저 조회를 해서 정보를 얻어온 후 수정을 해야한다.
@Select("SELECT EmployeeId,lastName, firstName, Birthdate birth, Photo, Notes "
+ "FROM Employees WHERE EmployeeId = #{id}")
Employee sql6(int id);@RequestMapping("link7")
public void method(int id, Model model) {
Employee employee = mapper.sql6(id);
model.addAttribute("employee", employee);
}@Update("UPDATE Employees SET "
+ "lastName = #{lastName}, "
+ "firstName = #{firstName}, "
+ "birthdate = #{birth}, "
+ "photo = #{photo}, "
+ "notes = #{notes} "
+ "WHERE employeeId = #{employeeId}")
int sql7(Employee employee);@RequestMapping("link8")
public void method(Employee employee) {
int cnt = mapper.sql7(employee);
System.out.println(cnt + "개 행 수정됨");
}30 졍규화(normalization)
테이블을 어떻게 하면 잘 만들 수 있는가를 배워보자.
테이블을 설계하는 방법, 정규화
이상하지 않고 보통의 테이블로 만드는 방법
정규화는 여러 과정을 거치게 된다.
1.첫번째 정규화 1NF (first noraml form)
2.두번째 정규화 2NF (second noraml form)
3.세번째 정규화 3NF (third noraml form)
1정규형->2정규형->3정규형의 순서로 가야한다.
이전을 만족해야 한다.
30.1 첫번째 정규화(제1 정규형)
제 1정규화를 만족하려면 다음의 조건들이 필요하다 책 215쪽
30.1.1 열은 원자적 값만을 포함한다.
원자적이란 하나의 레코드에 특정컬럼이 하나의 값만 가지는 것을 말한다.
30.1.2 같은 데이터가 여러열에 반복되지 말아야한다.
전화번호가 여러개라고 전화번호 컬럼이 늘어나는것 또한 원자적이지 않다.
여러 컬럼에 같은 데이터가 있으면 안된다.
결론적으로 테이블을 나눠야한다. 이것이 제 1정규형이다.
데이터를 항상 쪼개야하느냐? 그것은 아니고 주관적이다.
예를들어 배달원이 주소하는것은 어느동 어느아파트 등등 전체 주소를 하나의 원자로 볼 수 있다.
그런데 주소를 부동산 업자가 본다면 특정 거리에 거리번호 동별 구별로 나눌 필요가 있다.
항상 쪼개야하는 것은 아니다라는 것을 알아두자.
데이터의 목적에 따라서 판단해서 나눠야 한다.
30.1.3 각 행은 유일무이한 식별자인 기본키를 가져야한다.
책 216쪽~217쪽
기본키는 각 레코드를 식별하는데 사용한다.
다른 레코드와 구분하는 열이다.
두 개 이상의 컬럼을 묶어서 합성키로 기본키를 만들 수 있다.
1.기본키는 NULL이 될 수없다.
2.기본키는 레코드가 삽입될때 값이 있어야한다.
3.기본키는 간결해야 한다.
4.기본키의 값은 변경불가하다.
->기본키를 위한 열을 새로 만들기
주민등록번호와 같은 유일하게 할당되는 것을 기본키로 만들면된다.
그런데 주민등록번호 변경할 수도 있다.
이런 문제로 주관적인 입장이지만 주민등록번호를 기본키로 사용하는 것은 좋지않다.
그래서 이런 이유로 데이터에서 기본키를 찾는게 어려운 일이 될 수 있다.
결론적으로 가장 좋은 기본키는 의미없는 기본키를 위한 열을 새로 만드는 것이다.
테이블에 이미 존재하는 것을 기본키로 사용하는것을 natural key라고 한다.
새로만들어 내는 키를 synthetic key라고 한다.
실제 일할땐 회사의 방침에 따라 작성하면 된다.
예제) 학생명 전화번호 테이블
기본키를 할 열이 없으니 기본키를 위한 열을 새로 만들어줘야한다.
CREATE TABLE Student (
Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(50),
Phone VARCHAR(50)
);그런데 전화번호를 여러개 가지고 잇을 수 있으니 분리를 해줘야한다.
StudentId와 Phone를 합쳐서 기본키로 만들 수 있는데
전화번호가 바뀔수 잇으니 기본키를 해칠 수 있다. 그래서 따로 또 만들어주면 좋다.
CREATE TABLE Student (
Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(50)
);CREATE TABLE StudentPhone (
Id INT PRIMARY KEY AUTO_INCREMENT,
StudentId INT,
Phone VARCHAR(50)
);주관적인 것이 포함되어잇으니 알아서 판단을 해야한다.
예제) 교재 정보를 저장할 테이블 작성하기
책이름, 출판사, 발행일, 저자, 가격, 책소개 + 기본키로 id
CREATE TABLE Book (
Id INT PRIMARY KEY AUTO_INCREMENT,
Title VARCHAR(50),
Writer VARCHAR(50),
Publisher VARCHAR(50),
Published DATE,
Price INT
Info VARCHAR(100)
);저자가 여러명인 것을 고려하여 다시 짜면 더 나눌수도 있다.
CREATE TABLE Book (
Id INT PRIMARY KEY AUTO_INCREMENT,
Title VARCHAR(50),
Publisher VARCHAR(50),
Published DATE,
Price INT
Info VARCHAR(100)
);CREATE TABLE BookWriter (
Id INT PRIMARY KEY AUTO_INCREMENT,
BookId INT,
Writer VARCHAR(50)
);만들어진 테이블이 계속 가지는 않는다.
만들어 놓고 수정해나가는 것이 좋다.
30.2 두번째 정규화(제2 정규형)
369페이지
1.1NF(제1정규형)여야한다.
2.부분적 함수 의존이 없다.
기본키를 복합키로 만들엇을 경우 어떤 컬럼이 복합키 컬럼들 중 일부에 종속될때 부분적 함수의존이 있다고한다.
종속된다는 것은 복합키 중 하나가 바뀌면 그 종속된 컬럼이 바뀌는것을 말한다.
키가 아닌 컬럼이 키의 일부분에 종속된 경우를 말한다.
결론은 특정 컬럼이 변경될때 다른 컬럼이 변경되면 부분적 함수 의존이라고 한다.
복합키가 없으면 자동으로 2NF이다.
그래서 새로운 열을 만들어서 AUTO_INCREMENT id를 만드는 이유이다.
AUTO_INCREMENT을 추가하면 부분적 함수의존이 없어지기 때문에 문제해결할 수 있다.
30.3 세번째 정규화(제3 정규형)
1.2NF(제2정규형)이어야한다.
2.이행적 종속이 없어야한다
부분적 함수의존과 이행적 종속은 거의 비슷한 말이다.
전자는 키의 일부분에 의존하는 것이고 후자는 키가 아닌 컬럼에 의존하는 것이다.
키 컬럼이 아닌 컬럼끼리 의존하고 있는 것이 이행적 종속이다.
그럼 이 컬럼들을 다른 테이블로 분리하는 것이 좋다.
31 외래키(foreign-key), 참조키
정규형과정을 거치면 여러 테이블로 나뉘게 된다. 여기서 사용하는 것이 외래키이다.
학생명 전화번호(여러개가능)한 테이블을 만들어보자.
StudentPhone테이블의 StudentId컬럼이 Student의 Id컬럼을 참조하게 된다.
CREATE TABLE Student (
Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(50)
);CREATE TABLE StudentPhone (
Id INT PRIMARY KEY AUTO_INCREMENT,
StudentId INT,
Phone VARCHAR(50),
-- foreign key (외래키 참조키)
FOREIGN KEY (StudentId) REFERENCES Student(Id)
);데이터 입력
INSERT INTO Student (Name) VALUES ('손흥민');
INSERT INTO Student (Name) VALUES ('박지성');
INSERT INTO StudentPhone (StudentId, Phone) VALUES (1, '010-9999-8888');
INSERT INTO StudentPhone (StudentId, Phone) VALUES (2, '010-8888-9999');
INSERT INTO StudentPhone (StudentId, Phone) VALUES (3, '010-8888-9999'); -- fail다른테이블의 id를 참조하기 때문에 참조키하는 키가 없는 데이터를 넣으려하면 안들어간다.
그런데 NULL값은 들어갈 수 있게 된다.
그래서 주의해서 NOT NULL키워드를 통해서 데이터에 문제가 안생기게 해주는 것이 좋다.
만약 StudentPhone가 참조하고 있는데 Student를 지우고자 한다면 에러가 발생한다.
외래키 제약사항 있는 테이블의 데이터를 먼저 지워야한다.
현실에서는 지우는 일이 거의 없고 지웟다는 표시만 한다.
DELETE FROM StudentPhone WHERE StudentId = 2;
DELETE FROM Student WHERE Id = 2;32 테이블 관계
여러 테이블의 조합되있는 것을 어떻게 가져오는가?
외래키와 기본키를 기준으로 데이터를 조합할 수 있다.
먼저 관계를 맺어주자
삼지창이 있는 테이블이 일자로 되있는 것을 참조하고 있는 것이라고 생각하면된다.
한쪽의 ID가 한쪽에 여러개 나올 수 있다면 1대 다 관계이다.
프로젝트 만들때 관계를 보려면 이렇게 설정하면된다.
DATABASE - REVERSE ENGINEER로 관계확인
만약 물건을 조회하고 그 물건의 카테고리를 알고 싶을 때 두개로 나눠서 sql을 작성하면 비효율적이다.
SELECT * FROM Products WHERE ProductId = 1;
SELECT * FROM Categories WHERE CategoryId = 1;join을 사용해서 데이터를 합쳐서 볼 수 있다.
SELECT * FROM Products JOIN Categories ON Products.CategoryId = Categories.CategoryId
WHERE Products.ProductId = 1;JOIN의 종류는 inner Join(교집합) / Left Join(교집합+왼쪽의 차집합) / Right Join(교집합+오른쪽차집합)이 있다.
33. CATESIAN PRODUCT (곱집합) 카테시안 곱
SELECT COUNT(*) FROM Products; -- 77
SELECT COUNT(*) FROM Categories; -- 8
SELECT COUNT(*) FROM Products JOIN Categories; -- 616조건을 부여하지 않으면 Products레코드개수 * Categories레코드개수 616개로 합쳐진다.
Categories레코드가 Products의 레코드에 각각 매칭이 되기 때문이다.
여기서부터 걸러내기 위해 JOIN을 시작하면된다.
기본적인 작성은 왼쪽 테이블에 오른쪽 테이블에 매칭되어서 보여준다.
우리가 관심있는 것은 같은 categoryId만 보기를 원한다.
ON을 사용해서 조인조건을 추가하면된다.
SELECT * FROM Products P JOIN Categories C ON P.CategoryID = C.CategoryID;상품 테이블이 77개가 있기 때문에 77개의 레코드가 조회된다.
레코드는 조인조건이 없다면 왼쪽에 오른쪽 테이블이 나온다.
조인 후 컬럼 수는 왼쪽 테이블 컬럼 수 + 오른쪽 테이블 컬럼 수이다.
관심있는 컬럼만 보기 위해서는 SELECT절에 보고싶은 컬럼을 지정해주면된다.
SELECT ProductName, CategoryName FROM Products P JOIN Categories C ON P.CategoryID = C.CategoryID;연습
상품명, 그 상품을 공급하는 공급자명조회하기 Products의 SupplierID가 Suppliers 테이블의 SupplierID 참조하고 있음
서로 다른 테이블인데 중복된 컬럼명이 있으면 헷갈릴 수 있으니 테이블에 별명을 붙여서 사용하자.
SELECT P.ProductName, S.SupplierName FROM Products P JOIN Suppliers S ON P.SupplierID = S.SupplierID;34. JOIN
34.1 INNER JOIN
교집합만 조회하는 것이다.
그냥 JOIN을 쓰면 INNER JOIN이된다.
SELECT * FROM TableA A INNER JOIN TableB B ON A.Num1 = B.Num2; 34.2 LEFT JOIN / RIGHT JOIN
LEFT(OUTER) JOIN이라고 하며 INNERJOIN + 왼쪽테이블의 값을 표시하는 것이다.
SELECT * FROM TableA A LEFT JOIN TableB B ON A.Num1 = B.Num2; RIGHT JOIN은 INNERJOIN + 오른쪽의 테이블 값 모두 조회한다.
연습
주문보기
INNERJOIN은 양쪽에 데이터가 다잇어야해서 공통적인 값만 나온다.
RIGHT JOIN을 하면 모든 손님에서 주문을 보는데 주문한적이 없는 고객이 있어서 더 많다.
SELECT * FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID; -- 830
SELECT * FROM Orders o RIGHT JOIN Customers c ON o.CustomerID = c.CustomerID; -- 832연습
주문을 처리한적 없는 직원 조회하기
SELECT * FROM Employees e LEFT JOIN Orders o ON e.EmployeeID = o.EmployeeID WHERE o.OrderID IS NULL;34.3 두개 이상의 테이블 JOIN하기
예를들어 order를 보면 고객 id 직원 id 배달원id 가 있다.
이 것들을 모두 모아 보고싶다면 여러개를 JOIN하면된다.
JOIN ON을 연속해서 작성하면된다.
SELECT * FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID JOIN Suppliers s ON p.SupplierID = s.SupplierID;예)1번 상품의 상품명, 카테고리명 공급자명
SELECT p.ProductName, c.CategoryName, s.SupplierName
FROM Products p
JOIN Categories c ON p.CategoryID = c.CategoryID
JOIN Suppliers s ON p.SupplierID = s.SupplierID
WHERE p.ProductID = 1;예제 1996-07-04에 주문한 상품 명 조회
SELECT ProductName FROM Orders o
JOIN OrderDetails d ON o.OrderID = d.OrderID
JOIN Products p ON d.ProductID = p.ProductID
WHERE o.OrderDate = '1996-07-04';예제2 1996-07-04의 매출조회
SELECT SUM(p.Price * d.Quantity) 매출
FROM Orders o
JOIN OrderDetails d ON o.OrderID = d.OrderID
JOIN Products p ON d.ProductID = p.ProductID
WHERE o.OrderDate = '1996-07-04';예제3 일별 매출 조회 (날짜순으로 결과 조회)
SELECT OrderDate, SUM(p.Price * d.Quantity) 매출
FROM Orders o JOIN OrderDetails d ON o.OrderID = d.OrderID
JOIN Products p ON d.ProductID = p.ProductID
GROUP BY OrderDate;예제4 직원별 처리금액
SELECT e.EmployeeID, concat(e.lastName, ' ', e.firstName ) 이름, SUM(p.Price * d.Quantity) 처리금액
FROM Orders o JOIN OrderDetails d ON o.OrderID = d.OrderID
JOIN Products p ON d.ProductID = p.ProductID
JOIN Employees e ON o.EmployeeID = e.EmployeeID
GROUP BY e.EmployeeID;예제 5 상품별 판매금액
SELECT d.ProductID, p.ProductName, SUM(p.Price * d.Quantity) 매출
FROM Orders o JOIN OrderDetails d ON o.OrderID = d.OrderID
JOIN Products p ON d.ProductID = p.ProductID
GROUP BY ProductID;예제 6 고객별 소비 금액
SELECT c.CustomerID, c.CustomerName, SUM(p.Price * d.Quantity) 소비금액
FROM Orders o JOIN OrderDetails d ON o.OrderID = d.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON d.ProductID = p.ProductID
GROUP BY c.CustomerID;2023.04.24
테이블 정규화
테이블 설계가 프로젝트의 기본이 된다. 잘 알아두어야한다.
JOIN은 관계형데이터베이스의 꽃이라고 할 수 있을 것 같다. 필요한 데이터를 조회하는 SQL들을 잘 알아두자.