[Day26] SQL 문법 & 실습

Posted Mar 4, 2025

java

By yeeun426

12 min read

[Day26] SQL 문법 & 실습

SQL 기본 문법 정리

📌 데이터베이스 생성

  
CREATE DATABASE IF NOT EXISTS `w3schools` 
/*!40100 DEFAULT CHARACTER SET utf8 */;

📌 데이터 조회 (SELECT)

중복 제거 (DISTINCT)

  
SELECT DISTINCT country FROM customers;

  
SELECT COUNT(DISTINCT country) FROM customers; -- 중복 제거 후 개수
SELECT COUNT(*) FROM customers; -- 중복 미제거 개수 (전체 행 개수)

별칭 (Alias)

  
SELECT MIN(Price) AS SmallestPrice FROM Products;

SELECT COUNT(*) AS DistinctCountries
FROM (SELECT DISTINCT country FROM customers) AS c;

조건 검색 (WHERE)

  
SELECT * FROM Customers WHERE Country = 'Mexico';

<> : 같지 않음
BETWEEN : 특정 범위 내 값 선택
LIKE : 특정 패턴 검색
IN : 여러 개의 값 중 하나 선택

📌 정렬 (ORDER BY)

  
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

ASC : 오름차순 (기본값)
DESC : 내림차순

📌 패턴 검색 (LIKE)

  
SELECT * FROM Customers WHERE CustomerName LIKE 'a%'; -- 'a'로 시작
SELECT * FROM Customers WHERE city LIKE 'L_nd__'; -- 특정 패턴 매칭
SELECT * FROM Customers WHERE city LIKE '%L%'; -- 'L' 포함
SELECT * FROM Customers WHERE CustomerName LIKE 'La%'; -- 'La'로 시작
SELECT * FROM Customers WHERE CustomerName LIKE '%a'; -- 'a'로 끝남
SELECT * FROM Customers WHERE CustomerName LIKE '%or%'; -- 'or' 포함

% : 0개 이상 문자 포함 가능
_ : 정확히 한 문자 매칭

📌 데이터 제한 (LIMIT)

  
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

📌 집계 함수 (MIN, MAX, COUNT)

  
SELECT MIN(Price) FROM Products;
SELECT MAX(Price) FROM Products;

  
SELECT MIN(Price) AS SmallestPrice, CategoryID
FROM Products
GROUP BY CategoryID;

🔴 SQL 조인 (JOIN)

JOIN 종류	예제 코드	설명
INNER JOIN	`SELECT * FROM orders o INNER JOIN customers c ON o.customerid = c.customerid;`	주문한 고객 명단
LEFT JOIN	`SELECT * FROM orders o LEFT JOIN customers c ON o.customerid = c.customerid;`	모든 주문 정보 + 고객 정보 (없는 경우 NULL)
RIGHT JOIN	`SELECT * FROM orders o RIGHT JOIN customers c ON o.customerid = c.customerid;`	모든 고객 정보 + 주문 정보 (없는 경우 NULL)
FULL OUTER JOIN (MySQL 미지원)	`SELECT * FROM orders o FULL OUTER JOIN customers c ON o.customerid = c.customerid;`	모든 주문 + 모든 고객 정보 (MySQL에서는 cross join이라는 것이 있기는 하나 이것은 ‘모든 주문정보 x모든 고객 정보’가 되므로 제대로 결과가 나오지 않음)

쇼핑몰 만들기

백엔드 (BACK)

1. 데이터베이스 (MySQL)

product 테이블을 생성하고 샘플 데이터를 추가
prodcode(상품 코드)를 기본 키로 설정
prodname(상품명)은 중복되지 않도록 unique not null 제약 조건 설정

  
drop table if exists product;

create table product(
prodcode int auto_increment ,
prodname varchar(50) unique not null,
pimg varchar(50) ,
price int not null,
primary key (prodcode)
);

insert into product( prodname, price, pimg)
values
('아이스 아메리카노', 2700, '아이스아메리카노.png'),
('아이스 카페라떼',3500,'아이스카페라떼.png'),
('아이스 카푸치노',3800,'아이스카푸치노.png'),
('따듯한 아메리카노',2500,'핫아메리카노.png'),
('따듯한 카페라떼',3200,'핫카페라떼.png'),
('따듯한 카푸치노',3500,'핫카푸치노.png');

2. 데이터 모델(DTO) : Product.java

Product.java (DTO - Data Transfer Object)

product 테이블의 데이터와 매핑되는 객체
prodcode, prodname, pimg, price 필드를 가짐

  
package com.shop.cafe.dto;

public class Product {
	private int prodcode, price;
	private String prodname,pimg;
	
	public Product(int prodcode, String prodname, String pimg, int price) {
		setProdcode(prodcode);
		setProdname(prodname);
		setPimg(pimg);
		setPrice(price);
	}
	
	public Product() {
		super();
	}
	
	@Override
	public String toString() {
		return "Product [prodcode=" + prodcode + ", prodname=" + prodname + ", pimg=" + pimg + ", price=" + price + "]";
	}
	
	public int getProdcode() {
		return prodcode;
	}
	
	public void setProdcode(int prodcode) {
		this.prodcode = prodcode;
	}
	
	public String getProdname() {
		return prodname;
	}
	
	public void setProdname(String prodname) {
		this.prodname = prodname;
	}
	
	public String getPimg() {
		return pimg;
	}
	
	public void setPimg(String pimg) {
		this.pimg = pimg;
	}
	
	public int getPrice() {
		return price;
	}
	
	public void setPrice(int price) {
		this.price = price;
	}	
}

3. 데이터 접근 계층(DAO) : ProductDao.java

ProductDao.java (데이터베이스 접근)

MySQL과 연결하여 상품 목록을 조회하는 역할
JDBC를 사용하여 MySQL의 product 테이블 데이터를 가져옴
getAllProducts() 메서드:
1. MySQL 드라이버 로드
2. 데이터베이스 연결
3. SQL 실행 (SELECT * FROM product)
4. 조회 결과를 Product 리스트로 변환하여 반환

  
package com.shop.cafe.dao;

import java.sql.*;
import java.util.*;

import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.CrossOrigin;

import com.shop.cafe.dto.Product;

@Component
public class ProductDao {

	public List<Product> getAllProducts() throws Exception{
		// JDBC 6단계
		// 1. 드라이버 등록
		Class.forName("com.mysql.cj.jdbc.Driver");
		
		String url = "jdbc:mysql://localhost:3306/ureca?serverTimezone=UTC"; // 로컬 환경에서 MySQL의 포트번호가 3306
		String user = "ureca";
		String pw = "ureca";
		
		String sql = "select * from product";
		
		// try-width-resources 기능 auto closable 객체만 표현
		// 객체 생성 구문만 try 소괄호 안에 넣을 수 있음.
		try (
			// 2. 연결
			Connection con=DriverManager.getConnection(url, user, pw);
			// 3. Statement 생성
			PreparedStatement stmt=con.prepareStatement("select * from product");
			// 4. SQL 전송
			ResultSet rs=stmt.executeQuery();
			){
			// 5. 결과 받기
			List<Product> list = new ArrayList<>();
			
			while(rs.next()) {
				int prodcode = rs.getInt("prodcode");
				String prodname = rs.getString("prodname");
				String pimg = rs.getString("pimg");
				int price = rs.getInt("price");
				
				list.add(new Product(prodcode, prodname, pimg, price));
			}
			return list;			
		} 
	}
}

4. 서비스 계층 (Service) : ProductService.java

ProductService.java (비즈니스 로직)

ProductDao에서 데이터를 가져와서 가공하는 역할
@Service 어노테이션을 사용하여 스프링 서비스 빈으로 등록
getAllProducts() 메서드: DAO에서 조회한 상품 목록을 컨트롤러에 전달

  
package com.shop.cafe.service;

import com.shop.cafe.dao.ProductDao;
import com.shop.cafe.dto.Product;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class ProductService {
	
	@Autowired // DI -> new 해주는거
	ProductDao productDao;
	
	public List<Product> getAllProducts() throws Exception{
		return productDao.getAllProducts();
	}

}

5. 컨트롤러 (Controller) : ProductController.java

@RestController를 사용하여 REST API 제공
@CrossOrigin으로 CORS 정책 허용 (프론트엔드에서 API 호출 가능)
getAllProducts() 엔드포인트:
- http://localhost:8080/getAllProducts 요청 시 ProductService를 호출하여 상품 목록 반환

  
package com.shop.cafe.controller;

import java.util.*;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import com.shop.cafe.dto.Product;
import com.shop.cafe.service.ProductService;

@RestController
@CrossOrigin("http://127.0.0.1:5500/")
public class ProductController {
	
	@Autowired // DI -> new 해주는거
	ProductService productService;
	
	@GetMapping("getAllProducts")
	public List<Product> getAllProducts() {
		try {
			System.out.println("getAllProducts 실행");
			return productService.getAllProducts();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
}

✅ http://localhost:8080/getAllProducts

프론트엔드 (FRONT)

1. index.html (메인 페이지)

  
<!DOCTYPE html>
<html lang="en">
<head>
<title>Bootstrap 5 Website Example</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link
	href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css"
	rel="stylesheet">
<script
	src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
<style>
.fakeimg {
	height: 200px;
	background: #aaa;
}
</style>
</head>
<body>
	<div class="px-3 py-1 bg-info text-white text-center d-flex justify-content-between">
		<span>effect!</span> 	
		<span id="loginSpan"></span>
	</div>
	<nav class="navbar navbar-expand-sm bg-dark navbar-dark">
		<div class="container-fluid">
			<ul class="navbar-nav">
				<li class="nav-item" id="signupLi"><a class="nav-link active" href="#"
					data-bs-toggle="modal" data-bs-target="#signupModal">SignUp</a></li>
				<li class="nav-item"><a class="nav-link" href="#">Link</a></li>
				<li class="nav-item"><a class="nav-link" href="#">Link</a></li>
				<li class="nav-item"><a class="nav-link disabled" href="#">Disabled</a>
				</li>
			</ul>
		</div>
	</nav>

	<div class="container mt-5">
		<div class="row" id="productListDiv">  
		</div>
	</div>

	<div class="mt-5 p-4 bg-dark text-white text-center">
		<p>Footer</p>
	</div>
	<!-- signupModal -->
	<div class="modal" id="signupModal">
		<div class="modal-dialog">
			<div class="modal-content">

				<!-- Modal Header -->
				<div class="modal-header">
					<h4 class="modal-title">회원가입</h4>
					<button type="button" class="btn-close" data-bs-dismiss="modal"></button>
				</div>

				<!-- Modal body -->
				<div class="modal-body">
					<div class="mb-3 mt-3">
						<label for="nickname" class="form-label">Nickname:</label> <input
							type="text" class="form-control" id="nickname"
							placeholder="Enter nickname" name="nickname">
					</div>
					<div class="mb-3">
						<label for="email" class="form-label">Email:</label> <input
							type="email" class="form-control" id="email"
							placeholder="Enter email" name="email">
					</div>
					<div class="mb-3">
						<label for="pwd" class="form-label">Password:</label> <input
							type="password" class="form-control" id="pwd"
							placeholder="Enter password" name="pswd">
					</div>
					
					<button type="submit" class="btn btn-primary" id="signupBtn">가입</button>
				</div>

				<!-- Modal footer -->
				<div class="modal-footer">
					<button type="button" class="btn btn-danger"
						data-bs-dismiss="modal">Close</button>
				</div>

			</div>
		</div>
	</div>
	<script src="./js/index.js"></script>
</body>
</html>

2. js/index.js (클라이언트 사이드 로직)

window.onload 이벤트
- 서버의 getAllProducts API 호출 → 상품 목록 조회
- JSON 데이터를 가져와 productListDiv에 동적으로 HTML 생성

  
window.onload = async () => {
  let productList = await fetch("http://localhost:8080/getAllProducts", {
    method: "GET",
  }); // 서버에서 가져온 내용
  // XMLHttpRequest (XHR)
  // await : 데이터 올 때까지 기다릴게 (async 내부에서만 쓸 수 있는 키워드)
  console.log(productList);
  productList = await productList.json(); // 배열로
  console.log(productList);

  let productListDiv = ``;
  productList.forEach((item) => {
    productListDiv += `<div class="card m-3" style="width: 10rem;">
                      <img src="img/${item.pimg}" class="card-img-top" alt="...">
                      <div class="card-body">
                        <b class="card-title">${item.prodname}</b>
                        <p class="card-text text-danger">${item.price}</p>
                        <a href="#" class="btn btn-outline-info">장바구니 담기</a>
                      </div>
                    </div>`;
  });
  document.getElementById("productListDiv").innerHTML = productListDiv;
};

document.getElementById("signupBtn").addEventListener("click", async () => {
  const nickname = document.getElementById("nickname").value;
  const email = document.getElementById("email").value;
  const pwd = document.getElementById("pwd").value;
  const data = { nickname, email, pwd };
  let response = await fetch("insertMember", {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      // 'Content-Type': 'application/x-www-form-urlencoded',
    },
    body: JSON.stringify(data),
  });
  response = await response.json();
  console.log(response);
  if (response.msg === "ok") {
    console.log("ok");
    const modal = bootstrap.Modal.getInstance(
      document.getElementById("signupModal")
    );
    modal.hide();
    //hero icons
    document.getElementById(
      "loginSpan"
    ).innerHTML = `<svg xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="1.5" stroke="currentColor" class="size-6" width="24" height="24">
  <path stroke-linecap="round" stroke-linejoin="round" d="M15.75 9V5.25A2.25 2.25 0 0 0 13.5 3h-6a2.25 2.25 0 0 0-2.25 2.25v13.5A2.25 2.25 0 0 0 7.5 21h6a2.25 2.25 0 0 0 2.25-2.25V15M12 9l-3 3m0 0 3 3m-3-3h12.75" />
</svg>`;
    document.getElementById("signupLi").remove();
  }
});

✅ http://127.0.0.1:5500/front/index.html

END

Ureca, SQL

Git

This post is licensed under CC BY 4.0 by the author.