상여자는 커멘드로 간다!
move a2 a3
board 테이블
에 String으로, 보드 상태는 squares 테이블
에 저장한다board 테이블``squares 테이블
에서 해당 방 관련 정보를 삭제한다.cd docker
fps
docker exec -it chess-db-1 bash
mysql -u root -proot
show databases;
use chess;
show tables;
desc 테이블이름;
CREATE TABLE board (
name VARCHAR(100) NOT NULL primary key,
turn VARCHAR(10) NOT NULL
);
CREATE TABLE squares (
board_name VARCHAR(100) NOT NULL,
foreign key(board_name) references board(name),
position VARCHAR(5) NOT NULL,
piece VARCHAR(10) NOT NULL
);
INSERT into board (name, turn) VALUES (?, ?);
INSERT into squares (board_name, position, piece) VALUES (?, ?, ?);
-테이블에서 VALUES
뒤의 데이터 차례로 추가
SELECT * FROM board WHERE name = ?;
SELECT piece FROM squares WHERE board_name = ? AND position = ?;
WHERE
뒤 조건에 맞는 데이터를 추출UPDATE board SET turn = ? WHERE name = ?;
UPDATE squares SET piece = ? WHERE board_name = ? AND position = ?;
WHERE
뒤 조건에 맞는 데이터를 SET
뒤의 값으로 변경DELETE FROM squares WHERE board_name = ?;
DELETE FROM board WHERE name = ?;
WHERE
뒤 조건에 맞는 데이터를 제거SELECT c.CustomerID, c.CustomerName, SUM(od.Quantity*p.Price) AS TotalPrice
FROM Customers as c
JOIN orders as o on o.CustomerID = c.CustomerID
JOIN orderdetails as od on o.OrderID = od.OrderID
JOIN products as p on od.ProductID = p.ProductID
GROUP BY c.CustomerID
ORDER BY TotalPrice DESC
JOIN
: 여러 테이블 참조SUM
, GROUP BY
: 값을 특정 방식(합계)로 묶어 추출ORDER BY
: 내림차순 정렬(DESC) private static final String URL = "jdbc:mysql://localhost:3306/chess";
private static final String USER = "user";
private static final String PASSWORD = "password";
public Connection getConnection() {
loadDriver();
Connection connection = null;
try {
connection = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
private void loadDriver() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (final Exception e) {
e.printStackTrace();
}
}
private PreparedStatement getStatement(Connection connection, String sql, List<String> data) throws SQLException {
PreparedStatement statement = connection.prepareStatement(sql);
for (int i = 0; i < data.size(); i++) {
statement.setString(i + 1, data.get(i));
}
return statement;
}
connection
과 SQL
문, SQL
문에 들어갈 데이터를 인자로 받음SQL
문에 추가해 PreparedStatement
를 제공막막했지만, 구동되는 웹을 보며 뿌듯했다