20250925 DB

출처: https://velog.io/@woods0611/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EA%B8%B0%EB%B3%B8

 

오늘은 DB에 대해 배웠다. MySQL을 설치하고 SQL에 대한 기본 지식들을 공부하고 실습을 진행했다.

 

출처 : https://www.nexustech.je/technology-partnerships/microsoft-sql/

SQL

SQL은 데이터베이스에서 데이터를 저장, 조작, 검색하기 위한 표준 언어이다.

 

출처 : https://velog.io/@tlsdnxkr/SQL-%EA%B8%B0%EB%B3%B8%EB%AC%B8%EB%B2%95-%EC%A0%95%EB%A6%AC

SQL 문법

SQL CREATE DATABASE 문

새로운 SQL 데이터베이스를 만드는 데 사용된다.

CREATE DATABASE databasename;

 

SQL DROP DATABASE 문

기존 SQL 데이터베이스를 삭제하는 데 사용된다.

DROP DATABASE databasename;

 

SQL CREATE TABLE 문

데이터베이스에 새로운 테이블을 만드는 데 사용된다.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

 

SQL DROP TABLE 문

데이터베이스에서 기존 테이블을 삭제하는 데 사용된다.

DROP TABLE table_name;

 

SQL 테이블 자르기

테이블 자체가 아닌 테이블 내부의 데이터를 삭제하는 데 사용된다.

TRUNCATE TABLE table_name;

 

SQL ALTER TABLE 문

기존 테이블에 열을 추가, 삭제 또는 수정하는 데 사용된다. 기존 테이블에 다양한 제약 조건을 추가하거나 삭제하는 데에도 사용된다.

 

ALTER TABLE - 열 추가

ALTER TABLE table_name
ADD column_name datatype;

 

ALTER TABLE - 열 삭제

ALTER TABLE table_name
DROP COLUMN column_name;

 

ALTER TABLE - 열 이름 바꾸기

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

 

ALTER TABLE - 데이터 유형 변경/수정

MYSQL 버전

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

 

SQL NOT NULL 제약 조건

기본적으로 열은 NULL 값을 가질 수 있지만 NOT NULL 조건은 NULL 값을 허용하지 않도록 한다.

이렇게 하면 필드에 항상 값이 포함되어 있게 되므로 이 필드에 값을 추가하지 않고는 새 레코드를 삽입하거나 레코드를 업데이트할 수 없게 된다.

 

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

Persons 테이블이 생성될 때 "ID", "LastName" 및 "FirstName" 열이 NULL 값을 허용하지 않도록 보장한다.

 

만약 Persons 테이블이 이미 생성된 경우 제약을 걸려고 한다면 (MySQL 버전)

ALTER TABLE Persons
MODIFY COLUMN Age int NOT NULL;

 

SQL UNIQUE 제약 조건

제약 UNIQUE 조건은 열의 모든 값이 서로 다르다는 것을 보장한다.

 

// MySQL 버전
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);

만약 테이블이 이미 생성된 경우

// MySQL버전
ALTER TABLE Persons
ADD UNIQUE (ID);
// 열에 대한 제약조건을 정의할 때
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

 

삭제할 때

// MySQL 버전
ALTER TABLE Persons
DROP INDEX UC_Person;

 

SQL PRIMARY KEY 제약 조건

primary key 조건은 테이블의 각 레코드를 고유하게 식별하는 데 사용된다.

기본 키는 고유한 값을 포함해야 하며 null 값을 포함할 수 없고 각 테이블에는 기본 키가 하나만 있을 수 있다.

기본 키는 단일 열이거나 여러 열의 조합일 수 있다.

 

// MySQL 버전
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

여러 열에 대해 정의할 때

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

테이블이 이미 생성된 경우

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

// 여러 열일 때
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

삭제할 때

// MySQL 버전
ALTER TABLE Persons
DROP PRIMARY KEY;

 

SQL 구문

데이터베이스에서 수행해야 하는 대부분의 작업은 SQL 문을 사용하여 수행된다.

 

테이블의 모든 레코드 반환

// Customers 테이블 모든 레코드를 반환
SELECT * FROM Customers;

 

테이블에서 데이터를 골라서 반환

// CustomerName, City만 반환
SELECT CustomerName, City FROM Customers;

 

테이블에서 데이터를 골라 중복되지 않은 값만 반환

SELECT DISTINCT Country FROM Customers;

 

지정된 조건을 충족하는 레코드만 추출할 때

// Customers 테이블에서 Country가 Mexico인 데이터만 추출
SELECT * FROM Customers
WHERE Country='Mexico';

 

= 연산자 외의 다른 연산자를 사용해서 필터링할 수 있다.

=, >, <, >=, <= 등이 있다.

 

결과 집합을 오름차순이나 내림차순으로 정렬할 때

// 디폴트는 오름차순
SELECT * FROM Products
ORDER BY Price;
// 내림차순일 때
SELECT * FROM Products
ORDER BY Price DESC;

 

AND, OR , NOT 연산자

// 나라가 Spain이고 이름이 G로 시작하는 고객
SELECT *
FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%';
// 독일이거나 스페인인 고객
SELECT *
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';
// NOT 연산자
SELECT * FROM Customers
WHERE NOT Country = 'Spain';

 

만들었던 OTT를 DB와 연동해보기

예전에 만들었던 OTT 사이트를 이용해 DB에 데이터를 넣어보고 DB에서 데이터를 가져와 로그인까지 해보는 실습을 진행했다.

 

DB와 서버 연동하기

먼저 Maven을 이용해 필요한 라이브러리를 프로젝트에 설치해 추가했다.

방식은 pom.xml에 Maven dependency 코드를 추가해 설치했다.

 

JDBC 6단계 이해하고 클래스 생성하기

import java.sql.*;

import com.ureca.web.model.dto.Member;

public class MemberDAO {
	
	public static void insertMember(Member m) throws Exception {
		
		// JDBC 6단계 
		// 1. Driver 등록
		Class.forName("com.mysql.cj.jdbc.Driver");
		
		// 2. 연결
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/ottDB?user=ureca&password=ureca");
		
		// 3. Statement 생성
		
		Statement stmt = conn.createStatement();
		
		// 4. SQL 전송
		int i = stmt.executeUpdate("insert into persons(userId, userPw, nickname) values('"+m.getId()+"', '"+m.getPw()+"', '"+m.getName()+"')");
		System.out.println(i);
		
		// 5. 결과 얻기
		
		// 6. 자원 해제
		stmt.close();
		conn.close();
	}
	
	public static boolean selectMember(Member m) throws Exception {
		
		// JDBC 6단계 
		// 1. Driver 등록
		Class.forName("com.mysql.cj.jdbc.Driver");
		
		// 2. 연결
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/ottDB?user=ureca&password=ureca");
		
		// 3. Statement 생성
		
		Statement stmt = conn.createStatement();
		
		// 4. SQL 전송
		ResultSet rs = stmt.executeQuery("select * from persons where userId='"+m.getId()+"' and userPw='"+m.getPw()+"' ");
		
		// 5. 결과 얻기
		if (rs.next()) {
			return true;
		}
		
		// 6. 자원 해제
		rs.close();
		stmt.close();
		conn.close();
		
		return false;
	}
}

위의 코드를 보면

MemberDAO 클래스는 DB에 회원 정보를 넣거나 조회하는 역할을 담당하게 된다.

 

자세히 뜯어보기

1. import 부분

import java.sql.*;
import com.ureca.web.model.dto.Member;

java.sql.* 은 JDBC에서 클래스/인터페이스 (Connection, Statement, ResultSet 등)을 사용하기 위한 부분이다.

Member의 경우 회원 정보를 담은 클래스이다.

 

2. insetMember(Member m)

회원가입을 DB에 저장하는 기능

 

// 1. Driver 등록
Class.forName("com.mysql.cj.jdbc.Driver");

JDBC에서 MySQL 드라이버를 로딩한다.

JDBC DriverManager가 MySQL 드라이버를 사용할 수 있게 된다.

 

// 2. 연결
Connection conn = DriverManager.getConnection(
    "jdbc:mysql://localhost/ottDB?user=ureca&password=ureca");

DB 연결을 생성하고 ottDB라는 데이터베이스에 ureca/ureca 계정으로 접속한다.

결과로 Connection 객체가 반환된다.

 

// 3. Statement 생성
Statement stmt = conn.createStatement();

SQL 문장을 DB에 보내는 역할을 하는 객체

Statement는 SQL을 문자열 그대로 넣어야 해서 보안상 취약하다고 한다.

 

// 4. SQL 전송
int i = stmt.executeUpdate(
    "insert into persons(userId, userPw, nickname) values('"
    + m.getId() + "', '" + m.getPw() + "', '" + m.getName() + "')"
);
System.out.println(i);

executeUpdate() 은 INSERT, UPDATE, DELETE 같은 쿼리를 실행할 때 사용한다.

 

// 6. 자원 해제
stmt.close();
conn.close();

Statement와 Connection을 닫아준다. 닫지 않으면 메모리/커넥션 누수가 발생할 수 있다.

 

3. selectMember(Member m)

회원 로그인 여부를 체크하는 기능

 

1 ~ 3단계는 동일하다.

// 4. SQL 전송
ResultSet rs = stmt.executeQuery(
    "select * from persons where userId='"
    + m.getId() + "' and userPw='" + m.getPw() + "' "
);

executeQuery() 은 SELECT문을 실행하고 ResultSet으로 반환한다.

 

// 5. 결과 얻기
if (rs.next()) {
    return true;
}

rs.next()는 다음 행으로 이동하고 있으면 true 없으면 false를 반환한다.

즉, SELECT 결과가 존재한다면 true를 반환한다.

 

6도 동일. ResultSet도 닫아줌.

 

회원가입 및 로그인 실습

MemberService, MemberController를 수정한 뒤 실습

멤버 서비스에서 데이터를 저장하고 있었던 것을 주석 처리하고 MemberDAO 객체를 생성해 DB에 저장할 수 있도록 수정했다.

또한, MemberController의 예외 처리도 구현한 예외처리 클래스를 사용하지 않고 기본 Exception으로 간단하게 처리하도록 했다.

 

회원가입

STS에 1이 출력되고 MySQL의 table에 데이터가 잘 들어간 것을 볼 수 있다.

 

회원가입 실패 시

 

로그인

로그인이 잘 되는 것을 볼 수 있다.

 

로그인 실패할 때

 

느낀 점

소비자 - 클라이언트 - 서버 - DB - 응답으로 연계되는 모든 상황을 접해본 것 같아서 너무 재밌었다.

처음 DB에 대한 수업을 시작하기 전에 강사님께서 해커들에게 당하는 보안 사고들의 대부분이 biz에서 생긴다고 하셨다.

잘은 모르지만 아마도 공부했던 내용 중 Statement의 SQL을 문자열 그대로 넣는 것 같은 취약한 부분을 신경 써야하는구나 싶었다. 이건 빙산의 일각이고 아마도 더 많은 부분이 있을 것 같고 보안에 대한 지식을 쌓아 시큐어 코딩에도 도전해보고 싶다.