DB(Data Base)
데이터의 집합
- DBMS(Database Management System) : Database를 관리하고 운영하는 소프트웨어
- RDBMS(Relational DBMS) : 관계형 데이터 베이스
- 대표적인 종류로는 Oracle, MySQL, PostgreSQL이 있다.
SQL
Structured Query Language의 약자로 RDBMS에서 사용되는 언어
DDL(Data Definition Language)
테이블이나 관계의 구조를 생성하는데 사용한다.
- CREATE : 새로운 데이터베이스 및 테이블을 생성한다.
CREATE DATABASE 데이터베이스이름;
CREATE TABLE 테이블이름
(
필드이름1 필드타입1,
필드이름2 필드타입2,
...
);
- ALTER : 데이터베이스와 테이블의 내용을 수정할 수 있다.
ALTER TABLE 테이블이름 ADD 필드이름 필드타입;
ALTER TABLE 테이블이름 DROP 필드이름;
ALTER TABLE 테이블이름 MODIFY COLUMN 필드이름 필드타입;- DROP : 데이터베이스와 테이블을 삭제할 수 있다.
DROP DATABASE 데이터베이스이름;
DROP TABLE 테이블이름;- TRUNCATE : 데이터베이스와 테이블을 삭제할 수 있으며, 컬럼값만 남긴다.
TRUNCATE DATABASE 데이터베이스이름;
TRUNCATE TABLE 테이블이름;DCL(Data Control Language)
데이터의 사용 권한을 관리하는데 사용한다.
- GRANT : 사용자 또는 ROLE에 대해 권한을 부여한다.
GRANT [객체권한명] (컬럼)
ON [객체명]
TO { 유저명 | 롤명 | PUBLC} [WITH GRANT OPTION];
//ex
GRANT SELECT ,INSERT
ON mp
TO scott WITH GRANT OPTION;- REVOKE : 사용자 또는 ROLE에 부여한 권한을 회수한다.
REVOKE { 권한명 [, 권한명...] ALL}
ON 객체명
FROM {유저명 [, 유저명...] | 롤명(ROLE) | PUBLIC}
[CASCADE CONSTRAINTS];
//ex
REVOKE SELECT , INSERT
ON emp
FROM scott
[CASCADE CONSTRAINTS];DML(Data Manipulation Language)
테이블에 데이터를 검색, 삽입, 수정, 삭제하는데 사용한다.
- INSERT : 테이블에 새로운 row를 추가한다.
INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...) VALUES(데이터값1, 데이터값2, 데이터값3, ...);
INSERT INTO 테이블이름 VALUES(데이터값1, 데이터값2, 데이터값3, ...);- SELECT : 테이블의 row를 선택할 수 있다.
SELECT 필드이름 FROM 테이블이름 [WHERE 조건];- UPDATE : 테이블의 row의 내용을 수정할 수 있다.
UPDATE 테이블이름 SET 필드이름1=데이터값1, 필드이름2=데이터값2, ... WHERE 필드이름=데이터값;- DELETE : 테이블의 row를 삭제할 수 있다.
DELETE FROM 테이블이름 WHERE 필드이름=데이터값;CREATE 제약조건
- AUTO_INCREMENT : 컬럼의 값이 중복되지 않게 1씩 자동으로 증가하게 해줘 고유번호를 생성해 줍니다.
CREATE TABLE 테이블이름
(
필드이름 필드타입 AUTO_INCREMENT,
// id bigint AUTO_INCREMENT,
...
);- NOT NULL : 해당 필드는 NULL 값을 저장할 수 없게 된다.
CREATE TABLE 테이블이름
(
필드이름 필드타입 NOT NULL,
...
);- UNIQUE : 해당 필드는 서로 다른 값을 가져야만 합니다.
CREATE TABLE 테이블이름
(
필드이름 필드타입 UNIQUE,
...
);- PRIMARY KEY : 해당 필드가 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가지게 됩니다.
CREATE TABLE 테이블이름
(
필드이름 필드타입 PRIMARY KEY,
...
);유일하게 존재하는 값의 조합을 설정해서 중복된 데이터를 막는다.
사용하는 이유
1. 데이터의 무결성이 꺠지지 않게 하기 위해
2. 데이터 인덱싱을 빠르게 하기 위해
- POREIGN KEY : 하나의 테이블을 다른 테이블에 의존하게 만들며, 데이터의 무결성을 보장
- FK 가지는 테이블이 참조하는 기준 테이블의 열은 반드시 PK, UNIQUE 제약조건이 설정되어있어야 한다.
CREATE TABLE 테이블이름
(
필드이름 필드타입,
...
FOREIGN KEY(필드이름)
REFERENCES 테이블이름(필드이름)
);POREIGN KEY는 두개의 테이블을 연결하는 다리 역할이다.
-CASCADE : FOREIGN KEY로 연관된 데이터를 삭제, 변경할 수 있다.
CREATE TABLE 테이블이름
(
필드이름 필드타입,
...
FOREIGN KEY(필드이름)
REFERENCES 테이블이름(필드이름) ON DELETE CASCADE
//ON UPDATE CASCADE
);JDBC (Java Database Connectivity)
자바 프로그래밍 언어를 사용하여 데이터베이스에 접근하기 위한 자바 API
JDBC는 데이터베이스와 독립적인 API를 제공하여, 개발자가 특정 데이터베이스에 종속되지 않고,
다양한 데이터베이스에 접근할 수 있도록 한다.
JDBC 드라이버는 각 DB 회사들은 자신의 DB에 맞도록 JDBC 인터페이스를 구현한 후
라이브러리로 제공하게 되는데, 이를 JDBC 드라이버라고 부른다.
JDBC의 주요 기능
- 데이터베이스 연결
- SQL 실행
- 결과 처리
JDBC의 이점
- 데이터베이스와 독립적인 API 제공
- 다양한 데이터베이스와 통합
- 재사용성 향상
- 유지보수 용이
JdbcTemplate
Spring 프레임워크에서 제공하는 JDBC API를 위한 탬플릿 클래스
JDBC를 직접 사용하는 것 보다 간편하고 효율적으로 데이터베이스에 접근할 수 있다.
JdbcTemplate 사용법
- build.gradle에 JDBC, MySQL 추가
implementation 'mysql:mysql-connector-java:8.0.28'
implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'- application.properties에 DB정보 작성
spring.datasource.url=jdbc:mysql://localhost:3306/memo
spring.datasource.username=root
spring.datasource.password={비밀번호}
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver- DB연결이 필요한 곳에서 jdbcTemplate 사용
private final JdbcTemplate jdbctemplate;
public MemoRepository(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}- 생성자의 파라미터를 통해 JdbcTemplate 객체가 자동으로 넘어와 변수에 저장됨.
<예시>
package com.sparta.memo.controller;
import com.sparta.memo.dto.MemoRequestDto;
import com.sparta.memo.dto.MemoResponseDto;
import com.sparta.memo.entity.Memo;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.web.bind.annotation.*;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
@RestController
@RequestMapping("/api")
public class MemoController {
private final JdbcTemplate jdbcTemplate;
public MemoController(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@PostMapping("/memos")
public MemoResponseDto createMemo(@RequestBody MemoRequestDto requestDto) {
// RequestDto -> Entity
Memo memo = new Memo(requestDto);
// DB 저장
KeyHolder keyHolder = new GeneratedKeyHolder(); // 기본 키를 반환받기 위한 객체
String sql = "INSERT INTO memo (username, contents) VALUES (?, ?)";
jdbcTemplate.update( con -> {
PreparedStatement preparedStatement = con.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, memo.getUsername());
preparedStatement.setString(2, memo.getContents());
return preparedStatement;
},
keyHolder);
// DB Insert 후 받아온 기본키 확인
Long id = keyHolder.getKey().longValue();
memo.setId(id);
// Entity -> ResponseDto
MemoResponseDto memoResponseDto = new MemoResponseDto(memo);
return memoResponseDto;
}
@GetMapping("/memos")
public List<MemoResponseDto> getMemos() {
// DB 조회
String sql = "SELECT * FROM memo";
return jdbcTemplate.query(sql, new RowMapper<MemoResponseDto>() {
@Override
public MemoResponseDto mapRow(ResultSet rs, int rowNum) throws SQLException {
// SQL 의 결과로 받아온 Memo 데이터들을 MemoResponseDto 타입으로 변환해줄 메서드
Long id = rs.getLong("id");
String username = rs.getString("username");
String contents = rs.getString("contents");
return new MemoResponseDto(id, username, contents);
}
});
}
@PutMapping("/memos/{id}")
public Long updateMemo(@PathVariable Long id, @RequestBody MemoRequestDto requestDto) {
// 해당 메모가 DB에 존재하는지 확인
Memo memo = findById(id);
if(memo != null) {
// memo 내용 수정
String sql = "UPDATE memo SET username = ?, contents = ? WHERE id = ?";
jdbcTemplate.update(sql, requestDto.getUsername(), requestDto.getContents(), id);
return id;
} else {
throw new IllegalArgumentException("선택한 메모는 존재하지 않습니다.");
}
}
@DeleteMapping("/memos/{id}")
public Long deleteMemo(@PathVariable Long id) {
// 해당 메모가 DB에 존재하는지 확인
Memo memo = findById(id);
if(memo != null) {
// memo 삭제
String sql = "DELETE FROM memo WHERE id = ?";
jdbcTemplate.update(sql, id);
return id;
} else {
throw new IllegalArgumentException("선택한 메모는 존재하지 않습니다.");
}
}
private Memo findById(Long id) {
// DB 조회
String sql = "SELECT * FROM memo WHERE id = ?";
return jdbcTemplate.query(sql, resultSet -> {
if(resultSet.next()) {
Memo memo = new Memo();
memo.setUsername(resultSet.getString("username"));
memo.setContents(resultSet.getString("contents"));
return memo;
} else {
return null;
}
}, id);
}
}1주차 숙제
문제 1
문제 내용
👉 수강생을 관리하는 MANAGER 테이블을 만들어보세요.
- 컬럼은 총 id, name, student_code 입니다.
- id는 bigint 타입이며 PK입니다.
- name은 최소 2자 이상, varchar 타입, not null 입니다.
- student_code는 STUDENT 테이블을 참조하는 FK이며 not null 입니다.
- FK는 CONSTRAINT 이름을 ‘manager_fk_student_code’ 로 지정해야합니다.
내 풀이
CREATE TABLE MANAGER (
id bigint PRIMARY KEY,
name varchar(100) NOT NULL,
student_code varchar(100) NOT NULL,
CONSTRAINT manager_fk_student_code foreign key (student_code) references STUDENT(student_code)
);답안
CREATE TABLE IF NOT EXISTS MANAGER
(
id bigint primary key,
name varchar(100) not null,
student_code varchar(100) not null,
CONSTRAINT manager_fk_student_code foreign key(student_code) references student(student_code)
);
IF NOT EXISTS : 테이블이 없을 경우 생성
문제 2
문제 내용
👉 ALTER, MODIFY를 이용하여 MANAGER 테이블의 id 컬럼에 AUTO_INCREMENT 기능을 부여하세요.
내 풀이
ALTER TABLE MANAGER MODIFY COLUMN id bigint auto_increment;답안
ALTER TABLE MANAGER MODIFY COLUMN id bigint auto_increment;문제 3
문제 내용
👉 INSERT를 이용하여 수강생 s1, s2, s3, s4, s5를 관리하는 managerA와 s6, s7, s8, s9를 관리하는 managerB를 추가하세요.
- AUTO_INCREMENT 기능을 활용하세요
내 풀이
INSERT INTO MANAGER(name, student_code) VALUES ('managerA', 's1');
INSERT INTO MANAGER(name, student_code) VALUES ('managerA', 's2');
INSERT INTO MANAGER(name, student_code) VALUES ('managerA', 's3');
INSERT INTO MANAGER(name, student_code) VALUES ('managerA', 's4');
INSERT INTO MANAGER(name, student_code) VALUES ('managerA', 's5');
INSERT INTO MANAGER(name, student_code) VALUES ('managerB', 's6');
INSERT INTO MANAGER(name, student_code) VALUES ('managerB', 's7');
INSERT INTO MANAGER(name, student_code) VALUES ('managerB', 's8');
INSERT INTO MANAGER(name, student_code) VALUES ('managerB', 's9');'managerA', 's1' 부분에서, 홑따옴표가 아닌 쌍따옴표로 입력해서 조금 헤맸다;
자바 문법이랑 다르니까 너무 헷갈린다.ㅜㅜ
답안
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's1');
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's2');
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's3');
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's4');
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's5');
INSERT INTO MANAGER(name, student_code) VALUES('managerB', 's6');
INSERT INTO MANAGER(name, student_code) VALUES('managerB', 's7');
INSERT INTO MANAGER(name, student_code) VALUES('managerB', 's8');
INSERT INTO MANAGER(name, student_code) VALUES('managerB', 's9');문제 4
문제 내용
👉 JOIN을 사용하여 managerA가 관리하는 수강생들의 이름과 시험 주차 별 성적을 가져오세요.
내 풀이
select m.name, s.name, e.exam_seq, e.score from MANAGER m
JOIN STUDENT s on s.student_code = m.student_code
JOIN EXAM e on s.student_code = e.student_code
WHERE m.name = 'managerA'답안
SELECT s.name, e.exam_seq, e.score
FROM MANAGER m JOIN STUDENT S on m.student_code = s.student_code
JOIN EXAM e on m.student_code = e.student_code WHERE m.name = 'managerA';MANAGER 테이블의 name까지 출력했는데 필요 없었나보다..
문제 5
문제 내용
👉 STUDENT 테이블에서 s1 수강생을 삭제했을 때 EXAM에 있는 s1수강생의 시험성적과 MANAGER의 managerA가 관리하는 수강생 목록에 자동으로 삭제될 수 있도록 하세요.
- ALTER, DROP, MODIFY, CASCADE 를 사용하여 EXAM, MANAGER 테이블을 수정합니다.
내 풀이
못품삭제시 어떻게 데이터들을 연동시켜야 할 지 방법을 찾지 못했다.
key들로 어떻게 하면 될 것 같은데..
답안
ALTER TABLE EXAM DROP CONSTRAINT exam_fk_student_code;
ALTER TABLE EXAM ADD CONSTRAINT exam_fk_student_code FOREIGN KEY(student_code) REFERENCES STUDENT(student_code) ON DELETE CASCADE;
ALTER TABLE MANAGER DROP CONSTRAINT manager_fk_student_code;
ALTER TABLE MANAGER ADD CONSTRAINT manager_fk_student_code FOREIGN KEY(student_code) REFERENCES STUDENT(student_code) ON DELETE CASCADE;
DELETE FROM STUDENT WHERE student_code = 's1';FOREIGN KEY가 걸려있기 때문에, 먼저 DROP으로 지워준다.
그 후 다시 FK를 만들어준다. on DELETE CASCADE 옵션은 삭제 때 연관되는 데이터까지 삭제할 수 있다.
