DB07 - 29일(sql16~27, jdbc39) - alter, foreign key, join, union

16alter-table.sql

♠ MODIFY COLUMN

ALTER TABLE 테이블명 MODIFY COLUMN 변경할컬럼명 datatype constraint;

datatype 혹은 constraint 를 바꿀 수 있음

추가하려는 제약 사항을 위반하는 레코드가 이미 있다면 제약 사항 추가하는 코드가 실행되지 않음

ALTER TABLE mytable MODIFY COLUMN col1 INT NOT NULL; -- NOT NULL 추가
ALTER TABLE mytable MODIFY COLUMN col1 INT NULL; -- NOT NULL 삭제
ALTER TABLE mytable MODIFY COLUMN col1 INT DEFAULT 0; -- DEFAULT 추가
ALTER TABLE mytable MODIFY COLUMN col1 INT UNIQUE; -- UNIQUE 추가
ALTER TABLE mytable MODIFY COLUMN col1 INT PRIMARY KEY; -- PRIMARY KEY 추가

♠ DROP CONSTRAINT

ALTER TABLE 테이블명 ALTER 컬럼명 DROP constraint;
ALTER TABLE 테이블명 DROP constraint;

ALTER TABLE mytable ALTER col1 DROP DEFAULT; -- DEFAULT 삭제
ALTER TABLE mytable DROP PRIMARY KEY; -- PRIMARY KEY 삭제

 

♠ ADD CHECK : CHECK 추가 (컬럼이 아닌 테이블)

ALTER TABLE 테이블명 ADD CHECK (조건);

ALTER TABLE mytable ADD CHECK (col1 > 10); -- CHECK 추가

 

♠ SHOW : 테이블에 설정된 constraint 확인하기

SHOW CREATE TABLE 테이블명;

실제로 조회하면 다음과 같이 뜸

REATE TABLE `mytable` (
  `col1` int(11),
  `col2` int(11) DEFAULT NULL,
  CONSTRAINT `CONSTRAINT_1` CHECK (`col1` > 10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

→ 각 컬럼의 constraint 와 테이블의 constraint 를 확인할 수 있음

특히, CHECK constraint 가 CONSTRAINT_1 로 설정되어있음을 확인할 수 있다

 

♠ CHECK 삭제 (컬럼이 아닌 테이블)

ALTER TABLE mytable DROP CONSTRAINT CONSTRAINT_1;

 

♠ INDEX 

SELECT * FROM mytable WHERE col1 = 10;
-- index 가 없다면 모든 레코드 탐색, 있다면 col1 인덱스를 먼저 탐색

ALTER TABLE mytable DROP INDEX col1;
-- index 로 UNIQUE constraint 삭제

---

17create-table-1nf.sql

♠ 제 1 정규화 (1NF)

정규화 - '중복' 과 'NULL' 을 가능한 피하기

https://ko.wikipedia.org/wiki/%EC%A0%9C1%EC%A0%95%EA%B7%9C%ED%98%95

↓ 제 1 정규화

: 하나의 행이 여러개의 컬럼값을 가지게 되어 다른 행에 null 값을 가지는 컬럼들이 생김

따라서 'Customer ID', 'Telephone Number' 테이블을 따로 만드는 게 좋다

CREATE TABLE myCustomer (
	customerID INT PRIMARY KEY AUTO_INCREMENT,
	firstName VARCHAR(255) NOT NULL,
	surName VARCHAR(255) NOT NULL
	-- tel1 VARCHAR(255),
	-- tel2 VARCHAR(255),
	-- tel3 VARCHAR(255)
);

CREATE TABLE myTel (
	id INT PRIMARY KEY AUTO_INCREMENT,
	customerID INT NOT NULL,
	tel VARCHAR(255) NOT NULL
);

---

18create-table-2nf.sql

♠ 제 2 정규화 (2NF)

↓ 제 2 정규화

: 기존 테이블에서 '종업원'+'기술' 이 key 인데, '근무지' 가 key 의 일부분인 '종업원' 에만 의존

따라서 '종업원', '근무지' 테이블을 따로 만드는 게 좋다

CREATE TABLE myEmployee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	employee VARCHAR(255),
	location VARCHAR(255)
);

CREATE TABLE mySkill (
	id INT PRIMARY KEY AUTO_INCREMENT,
	employeeID INT NOT NULL,
	skill VARCHAR(255)
);

---

19create-table-3nf.sql

♠ 제 3 정규화 (3NF)

↓ 제 3 정규화

: 기존 테이블의 '대회'+'연도' 가 key 인데 key 가 아닌 어떤 컬럼'우승자 생년 월일'이 다른 컬럼'우승자'에 의존

따라서 '우승자', '우승자 생년 월일' 테이블을 따로 만드는 게 좋다

CREATE TABLE mytable25Winner (
	id INT PRIMARY KEY AUTO_INCREMENT,
	tournament VARCHAR(255),
	year CHAR(4),
	winnerID INT
);

CREATE TABLE mytable26WinnerBirth (
	id INT PRIMARY KEY AUTO_INCREMENT,
	winner VARCHAR(255),
	birth DATE
);

---

20foreign-key.sql

♠ FOREIGN KEY : 외래키, 참조키

FOREIGN KEY (foreignkey로설정할컬럼명) REFERENCES 다른테이블명(다른테이블의primarykey컬럼명)

예1)

CREATE TABLE firstTable (
	idone INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255),
	content VARCHAR(255)
);

CREATE TABLE secondTable (
	idtwo INT PRIMARY KEY AUTO_INCREMENT,
	firstId INT,
	memo VARCHAR(255),
	FOREIGN KEY (firstId) REFERENCES firstTable(idone)
);

예2)

CREATE TABLE mytable27Customer (
	id INT PRIMARY KEY AUTO_INCREMENT,
	firstName VARCHAR(255),
	lastName VARCHAR(255)
);

CREATE TABLE mytable28Tel (
	id INT PRIMARY KEY AUTO_INCREMENT,
	customerId INT,
	tel VARCHAR(255),
	FOREIGN KEY (customerID) REFERENCES mytable27Customer(id)
);

INSERT INTO mytable27Customer (firstName, lastName) VALUES ('hong', 'gildong');
INSERT INTO mytable27Customer (firstName, lastName) VALUES ('son', 'hm');

INSERT INTO mytable28Tel (customerId, tel) VALUES (1, '01099999999');
INSERT INTO mytable28Tel (customerId, tel) VALUES (2, '01088888888');
INSERT INTO mytable28Tel (customerId, tel) VALUES (2, '01077777777');

> mytable27Customer 

PRIMARY KEY : id

> mytable28Tel

PRIMARY KEY : id, FOREIGN KEY : customerId

 

♠ 참조키가 존재하지 않는 오류가 발생하는 경우

mytable27Customer 의 id 값은 1, 2 뿐이고 3 은 존재하지 않기 때문에 오류 발생함

INSERT INTO mytable28Tel (customerId, tel) VALUES (3, '01066666666'); -- 불가능

♠ 다른 테이블에서 참조하고 있는 경우 삭제 불가능

mytable28Tel 의 customerId 값으로 2가 존재하고 있으므로 오류 발생함

DELETE FROM mytable27Customer WHERE id = 2; -- 불가능

따라서 참조하고 있는 테이블(mytable28Tel)에서 먼저 삭제 후 테이블(mytable27Customer)에서 삭제해야 한다

♠ FOREIGN KEY constraint 삭제

SHOW 키워드로 먼저 값을 얻기

'CREATE TABLE `mytable28Tel` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customerId` int(11) DEFAULT NULL,
  `tel` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `customerId` (`customerId`),
  CONSTRAINT `mytable28Tel_ibfk_1` FOREIGN KEY (`customerId`) REFERENCES `mytable27Customer` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'

mytable28Tel_ibfk_1 이용하여 다음과 같이 작성하여 삭제

ALTER TABLE mytable28Tel DROP FOREIGN KEY mytable28Tel_ibfk_1;

---

21join.sql

CREATE TABLE customerTable (
	id INT PRIMARY KEY AUTO_INCREMENT, 
	name VARCHAR(50),
	memo VARCHAR(255)
);

CREATE TABLE telTable (
	id INT PRIMARY KEY AUTO_INCREMENT,
	tel VARCHAR(20),
	cusId INT
);

INSERT INTO customerTable (id, name, memo) VALUES (3, 'lisa', 'cake');
INSERT INTO customerTable (id, name, memo) VALUES (4, 'rose', 'soup');
INSERT INTO customerTable (id, name, memo) VALUES (5, 'jennie', 'candy');

INSERT INTO telTable (tel, cusId) VALUES ('010-1234', 3);
INSERT INTO telTable (tel, cusId) VALUES ('010-222-1', 2);
INSERT INTO telTable (tel, cusId) VALUES ('010-222-2', 2);

customerTable

telTable

♠ JOIN

> join 조건 없음 (cartesign product, 카테시안 곱)

SELECT * FROM 테이블명1, 테이블명2;
SELECT * FROM customerTable, telTable;

customerTable 컬럼, telTable 컬럼 같이 출력됨 (행 조합 3x3 = 9)

 

> join 조건 있음

SELECT * FROM 테이블명1 JOIN 테이블명2 ON 조건(테이블1.컬럼 = 테이블2.컬럼);
SELECT * FROM customerTable JOIN telTable ON customertable.id = telTable.cusId;

customerTable 의 id 컬럼 값과 telTable 의 cusId 컬럼 값이 같은 경우만 출력됨

 

♠ alias 별칭

AS (생략가능)

테이블에 별칭 설정

SELECT * FROM 테이블1 AS 테이블1별칭 JOIN 테이블2 AS 테이블2별칭 ON 조건;
SELECT * FROM 테이블1 테이블1별칭 JOIN 테이블2 테이블2별칭 ON 조건; -- AS 생략

SELECT * FROM customerTable AS ctb JOIN telTable AS ttb ON ctb.id = ttb.cusId;
SELECT * FROM customerTable ctb JOIN telTable ttb ON ctb.id = ttb.cusId; -- AS 생략

SELECT ctb.id, ctb.name, ttb.tel FROM customerTable ctb JOIN telTable ttb ON ctb.id = ttb.cusId;

 

컬럼에 별칭 설정

SELECT ctb.id AS 고객번호, ctb.name AS 이름, ttb.tel AS 전화번호 
FROM customerTable ctb JOIN telTable ttb ON ctb.id = ttb.cusId;

SELECT ctb.id 고객번호, ctb.name 이름, ttb.tel 전화번호 
FROM customerTable ctb JOIN telTable ttb ON ctb.id = ttb.cusId; -- AS 생략

---

22inner-join.sql

 

♠ (INNER) JOIN 

SELECT * FROM 테이블명1 INNER JOIN 테이블명2 ON 조건(테이블1.컬럼 = 테이블2.컬럼);
SELECT * FROM 테이블명1 JOIN 테이블명2 ON 조건(테이블1.컬럼 = 테이블2.컬럼); -- INNER 생략가능

SELECT * FROM customerTable ctb INNER JOIN telTable ttb ON ctb.id = ttb.cusId;
SELECT * FROM customerTable ctb JOIN telTable ttb ON ctb.id = ttb.cusId; -- INNER 생략

(customerTable - 3, 4, 5) INNER JOIN (telTable - 2, 3)  →  inner join - 3

---

23left-outer-join.sql

♠ LEFT (OUTER) JOIN

SELECT * FROM 테이블명1 LEFT OUTER JOIN 테이블명2 ON 조건(테이블1.컬럼 = 테이블2.컬럼);
SELECT * FROM 테이블명1 LEFT JOIN 테이블명2 ON 조건(테이블1.컬럼 = 테이블2.컬럼); -- OUTER 생략가능

SELECT * FROM customerTable c LEFT OUTER JOIN telTable t ON c.id = t.cusId;
SELECT * FROM customerTable c LEFT JOIN telTable t ON c.id = t.cusId; -- OUTER 생략

(customerTable - 3, 4, 5) LEFT OUTER JOIN (telTable - 2, 3)  →  left outer join - 3, 4, 5

예1)

-- 전화번호 없는 고객
SELECT * 
FROM customerTable c LEFT JOIN telTable t ON c.id = t.cusId
WHERE t.id IS NULL;

예2)

-- 연습 : 주문한 적 없는 고객명 오름차순 조회
-- 사용테이블 : Customers, Orders
SELECT c.CustomerName 
FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL
ORDER BY c.CustomerName;

---

24right-outer-join.sql

♠ RIGHT (OUTER) JOIN

SELECT * FROM 테이블명1 RIGHT OUTER JOIN 테이블명2 ON 조건(테이블1.컬럼 = 테이블2.컬럼);
SELECT * FROM 테이블명1 RIGHT JOIN 테이블명2 ON 조건(테이블1.컬럼 = 테이블2.컬럼); -- OUTER 생략가능

SELECT * FROM customerTable c RIGHT OUTER JOIN telTable t ON c.id = t.cusId;
SELECT * FROM customerTable c RIGHT JOIN telTable t ON c.id = t.cusId; -- OUTER 생략

(customerTable - 3, 4, 5) RIGHT OUTER JOIN (telTable - 2, 3)  →  right outer join - 2, 3

예1)

-- 주인없는 전화번호
SELECT * 
FROM customerTable c RIGHT JOIN telTable t ON c.id = t.cusId
WHERE c.id IS NULL;

예2)

-- 연습1 : 주문한 적 없는 고객명 오름차순 조회
-- 사용테이블 : Customers, Orders
SELECT c.CustomerName 
FROM Orders o RIGHT JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderID IS NULL 
ORDER BY 1;

---

25full-outer-join.sql

♠ FULL (OUTER) JOIN  (mysql 에 없음)

SELECT * FROM 테이블명1 FULL OUTER JOIN 테이블명2 ON 조건(테이블1.컬럼 = 테이블2.컬럼);
SELECT * FROM 테이블명1 FULL JOIN 테이블명2 ON 조건(테이블1.컬럼 = 테이블2.컬럼); -- OUTER 생략가능

SELECT * FROM customerTable c FULL OUTER JOIN telTable t ON c.id = t.cusId;
SELECT * FROM customerTable c FULL JOIN telTable t ON c.id = t.cusId; -- OUTER 생략

---

26union.sql

SELECT 컬럼 FROM 테이블1 UNION SELECT 컬럼 FROM 테이블2;

 

* 컬럼의 수가 같아야 함 (다르면 오류)

SELECT * FROM customerTable UNION SELECT * FROM telTable;

SELECT name FROM customerTable UNION SELECT tel FROM telTable;

 

> 값을 중복해서 출력하지 않음

INSERT INTO customerTable (id, name, memo) VALUES (6, 'jisoo', 'cake');
SELECT * FROM customerTable;

SELECT memo FROM customerTable;

→ memo 의 cake 를 중복되도록 새 레코드를 insert 했음

SELECT memo FROM customerTable UNION SELECT tel FROM telTable;

→ 하지만 UNION 에서 중복된 값은 한번만 출력됨

 

> 중복된 값도 출력하기 : UNION ALL

SELECT 컬럼 FROM 테이블1 UNION ALL SELECT 컬럼 FROM 테이블2;


SELECT memo FROM customerTable UNION ALL SELECT tel FROM telTable;

 

> UNION 사용하여 FULL OUTER JOIN 구현하기 (위에서 값 추가로 insert 하기 전에 작성함)

SELECT * FROM customerTable c LEFT JOIN telTable t ON c.id = t.cusId 
UNION 
SELECT * FROM customerTable c RIGHT JOIN telTable t ON c.id = t.cusId;

* 위의 cartesign product 와 혼동 주의

설명 ↓

SELECT * FROM customerTable c LEFT JOIN telTable t ON c.id = t.cusId;

SELECT * FROM customerTable c RIGHT JOIN telTable t ON c.id = t.cusId;

이 둘의 UNION 이므로

 

예)

-- 연습 : 고객명과, 직원의 LastName을 하나의 컬럼으로 조회
SELECT CustomerName FROM Customers
UNION
SELECT LastName FROM Employees
ORDER BY 1;

---

27join-sample.sql

Product 테이블의 컬럼은 ProductID, ProductName, SupplierID, CategoryID, Unit, Price 인데, 

CategoryID 대신 CategoryName 이 출력되도록 작성해보기

SELECT p.ProductID, p.ProductName, c.CategoryName, p.Unit, p.Price
FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID;

---

JDBC39Servlet

- 39Servlet, ProductDao, ProductDto(bean), v39.jsp, 27join-sample.sql

Bean 파일의 이름 끝에 DTO 를 붙여서 작성함 (DTO : Data Transaction Object)

jdbc09

39Servlet

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// 0. 사전작업
		ServletContext application = request.getServletContext();
		DataSource ds = (DataSource) application.getAttribute("dbpool");
		ProductDao dao = new ProductDao();
		List<ProductDto> list = null;
		
		// 2. request 분석
		
		// 3. business logic
		try (Connection con = ds.getConnection()) {
			list = dao.getList(con);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		// 4. add attributes
		request.setAttribute("list", list);
		
		// 5. forward/redirect
		String path = "/WEB-INF/view/jdbc09/v39.jsp";
		request.getRequestDispatcher(path).forward(request, response);
	}

ProductDao 객체 dao 생성

dao.getList 실행하여 (sql 실행하여 얻은 정보를 담은) list 를 반환받아 list 에 저장

list 를 setAttribute 로 저장하여 전달하고 v39.jsp 파일로 포워딩

 

jdbc09.dao

ProductDao

public class ProductDao {

	public List<ProductDto> getList(Connection con) {
		List<ProductDto> list = new ArrayList<>();
		String sql = "SELECT " + 
				"    c.CategoryName, p.ProductName, p.Unit, p.Price " + 
				"FROM " + 
				"    Products p " + 
				"        JOIN " + 
				"    Categories c ON p.CategoryID = c.CategoryID " + 
				"ORDER BY 1 , 2";
		
		try (Statement stmt = con.createStatement();
				ResultSet rs = stmt.executeQuery(sql);) {
			while (rs.next()) {
				ProductDto dto = new ProductDto();
				
				dto.setCategoryName(rs.getString(1));
				dto.setProductName(rs.getString(2));
				dto.setUnit(rs.getString(3));
				dto.setPrice(rs.getDouble(4));
				
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
	}

}

getList 메소드 

sql 코드 - SELECT c.CategoryName, p.ProductName, p.Unit, p.Price

FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID

ORDER BY 1, 2; 

ProductDto (bean) 객체 dto 생성

dto.set~ 메소드로 컬럼값들을 저장, dto 를 list 에 추가 (while 로 여러 행의 정보를 모두 list 에 추가)

list 반환

 

jdbc09.bean

ProductDto

public class ProductDto {
	private String categoryName;
	private String productName;
	private String unit;
	private Double price;

	public String getCategoryName() {
		return categoryName;
	}

	public void setCategoryName(String categoryName) {
		this.categoryName = categoryName;
	}

	public String getProductName() {
		return productName;
	}

	public void setProductName(String productName) {
		this.productName = productName;
	}

	public String getUnit() {
		return unit;
	}

	public void setUnit(String unit) {
		this.unit = unit;
	}

	public Double getPrice() {
		return price;
	}

	public void setPrice(Double price) {
		this.price = price;
	}

}

set, get 메소드

 

jdbc09

v39.jsp

<body>
  <div class="container">
    <div class="row">
      <div class="col">
        <h1>상품 목록</h1>
        <table class="table">
          <thead>
            <tr>
              <th>카테고리</th>
              <th>상품명</th>
              <th>단위</th>
              <th>가격</th>
            </tr>
          </thead>
          <tbody>
            <c:forEach items="${list }" var="item">
              <tr>
                <td>${item.categoryName }</td>
                <td>${item.productName }</td>
                <td>${item.unit }</td>
                <td>${item.price }</td>
              </tr>
            </c:forEach>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</body>

list 전달받아 테이블로 출력