JAVA 41일차 (2023-07-19) 자바 프로그래밍_DBMS_SQL - DML, DQL, Foreign Key

- 자바 프로그래밍
  - JDBC 프로그래밍(com.eomcs.jdbc)
    - SQL - DML 사용법
    - SQL - DQL 사용법

DML(Data Manipulation Language)

# DML(Data Manipulation Language)
데이터 등록, 변경, 삭제를 다루는 SQL 문법

ㄴ study 라는 계정으로 MySQL 접속 후 studydb 를 기본 데이터베이스로 설정

 

insert

## insert
- 데이터를 입력할 때 사용하는 문법이다.

ㄴ 연락처 테이블 생성 

ㄴ PK 컬럼 지정

ㄴ 자동 증가 컬럼 지정

 

- 전체 컬럼 값 입력하기

컬럼을 지정하지 않으면 테이블을 생성할 때 선언한 컬럼 순서대로 값을 지정해야 함

insert into 테이블명 values(값,....);

 

컬럼을 명시할 수 있음

=> 이때 값을 입력하는 컬럼의 순서를 바꿀 수 있음

insert into 테이블명(컬럼,컬럼,...) values(값,값,...);

 

- 값을 입력할 컬럼을 선택하기

=> 단 필수 입력 컬럼은 반드시 선택해야 함

no 컬럼은 필수 입력 컬럼이지만, 자동 증가 컬럼이기 때문에 값을 입력하지 않아도 됨

 

여러 개의 값을 한 번에 insert 하기

 

select 결과를 테이블에 insert하기

select 결과를 테이블에 바로 입력하기
  => select 결과의 컬럼명과 insert 테이블의 컬럼명이 같을 필요는 없다.
  => 그러나 결과의 컬럼 개수와 insert 하려는 컬럼 개수가 같아야 한다.
  => 결과의 컬럼 타입과 insert 하려는 컬럼의 타입이 같거나 입력 할 수 있는 타입이어야 한다.

=>

 

update

## update
- 등록된 데이터를 변경할 때 사용하는 명령이다.

update 테이블명 set 컬럼명=값, 컬럼명=값, ... where 조건...;

ㄴ 조건을 지정하지 않으면, 모든 데이터에 대해 변경함

 

delete

## delete
- 데이터를 삭제할 때 사용하는 명령이다.

delete from 테이블명 where 조건;

 

조건을 지정하지 않으면 모든 데이터가 삭제됨 -> 주의!

delete from test1;

=> test1 테이블 삭제됨

 

 

autocommit

## autocommit
mysql은 autocommit의 기본 값이 true이다.
따라서 명령창에서 SQL을 실행하면 바로 실제 테이블에 적용된다.
수동으로 처리하고 싶다면 autocommit을 false로 설정하라!
> set autocommit=false;

insert/update/delete을 수행한 후 승인을 해야만 실제 테이블에 적용된다.
> commmit;

마지막 commit 상태로 되돌리고 싶다면,
> rollback;

ㄴ autocommit 을 false 로 변경해줌

=>

ㄴ 다른 터미널을 띄워서 확인 시 삭제한 데이터가 실제로는 삭제되지 않음(commit 을 아직 안 했기 때문)

=>

ㄴ commit 후에는 데이터 삭제가 실제 데이터베이스에 반영됨을 확인

ㄴ 데이터베이스에 해당 데이터가 추가된 것으로 보임

=>

ㄴ 다른 터미널에서 확인 시 실제로 데이터베이스에 데이터가 추가된 것이 아님을 확인

=>

ㄴ commit 후에는 데이터 추가가 실제 데이터베이스에 반영됨을 확인

ㄴ 데이터베이스에 해당 데이터가 추가된 것으로 보임

=>

ㄴ 다른 터미널에서 확인 시 실제로 데이터베이스에 데이터가 추가된 것이 아님을 확인

=>

ㄴ rollback 을 할 경우 추가 마지막 commit 이후에 수행한 insert 작업은 취소됨

=>

ㄴ 데이터 추가 작업이 취소됨을 확인

ㄴ 데이터베이스에 데이터 추가

=>

ㄴ 데이터베이스에 해당 데이터가 추가된 것으로 보임

ㄴ 1번과 5번 데이터 삭제된 것으로 보임

ㄴ commit 을 안 했으므로 데이터베이스에 작업이 모두 반영되지 않음

ㄴ 데이터베이스에 있는 6번 데이터 이름 업데이트

=>

ㄴ commit 을 안 했으므로 데이터베이스에 작업이 모두 반영되지 않음

=>

ㄴ rollback 을 이용하여 모든 작업 되돌리기

=>

ㄴ 진행한 어떠한 작업도 반영되지 않음을 확인

ㄴ rollback 후 다시 commit 을 해봤자 아무 작업도 반영되지 않음을 확인

 

 

DQL

# DQL(Data Query Language)
데이터를 조회할 때 사용하는 문법

 

테스트 용 테이블 및 데이터 준비

 

select

## select
- 테이블의 데이터를 조회할 때 사용하는 명령이다.

 

모든 컬럼 값 조회하기. 컬럼 순서는 테이블을 생성할 때 선언한 순서.

select * from 테이블;

 

특정 컬럼의 값만 조회할 때 => "프로젝션(projection)"이라 부름

select 컬럼명,컬럼명 from 테이블;

 

 

MySQL 공식 문서 확인

=>

=>

=>

 

오라클 공식 문서 확인

https://docs.oracle.com/en/database/oracle/oracle-database/19/development.html

Oracle Database 19c - Development

=>

=>

 

가상의 컬럼 값을 조회하기

 

조회하는 컬럼에 별명 붙이기

### 조회하는 컬럼에 별명 붙이기
- 별명을 붙이지 않으면 원래의 컬럼명이 조회 결과의 컬럼이름으로 사용된다.
- 위의 예제처럼 복잡한 식으로 표현한 컬럼인 경우 컬럼명도 그 식이 된다.
- 이런 경우 별명을 붙이면 조회 결과를 보기 쉽다.

 

컬럼에 별명 붙이기

select 컬럼명 [as] 별명 ...

=>

ㄴ as를 생략해도 됨

 

조회할 때 조건 지정하기

### 조회할 때 조건 지정하기
- where 절과 연산자를 이용하여 조회 조건을 지정할 수 있다.
- 이렇게 조건을 지정하여 결과를 선택하는 것을 "셀렉션(selection)" 이라 한다.

select ... from ... where 조건...
select *
from test1
where no > 5;

 

 

연산자

OR, AND, NOT

### OR, AND, NOT
- OR  : 두 조건 중에 참인 것이 있으면 조회 결과에 포함시킨다.
- AND : 두 조건 모두 참일 때만 조회 결과에 포함시킨다.
- NOT : 조건에 일치하지 않을 때만 결과에 포함시킨다.

재직자 또는 java100기 학생만 조회하라!

java100기 학생 중에 재직자만 조회하라!

 

주의!
where 절을 통해 결과 데이터를 먼저 선택(selection)한 다음
결과 데이터에서 가져올 컬럼을 선택(projection)한다.
따라서 실행 순서는:
from ==> where ==> select

 

재직자가 아닌 사람만 조회하라!

 

학생 번호가 짝수인 경우 전화 번호를 '1111'로 변경하라

 

학생 번호가 3의 배수인 경우 전화번호를 '2222'로 변경하라

 

전화 번호가 있는 학생만 조회하라!

ㄴ 위와 같이 null에 != 연산자를 사용하면 조건이 맞지 않음

 

null인지 여부를 가릴 때는 is 또는 is not 연산자를 사용하라!

 

전화 번호가 없는 학생만 조회하라!

ㄴ 위와 같이 null에 = 연산자를 사용하면 조건이 맞지 않음

 

null인지 여부를 가릴 때는 = 연산자가 아닌 is 연산자를 사용해야 함

 

사칙연산

 

비교연산

 

between 값1 and 값2

### between 값1 and 값2
- 두 값 사이(두 값도 포함)에 있는지 검사한다.

 

like

### like
- 문자열을 비교할 때 사용한다.

 

class 이름이 java로 시작하는 모든 학생 조회하기
=> % : 0개 이상의 문자

 

class 이름에 java를 포함한 모든 학생 조회하기

=> 이 경우 조회 속도가 느림!

 

class 이름이 101로 끝나는 반의 모든 학생 조회하기

 

학생의 이름에서 첫번째 문자가 s이고 두번째 문자가 0인 학생 중에서 딱 세자의 이름을 가진 학생을 모두 조회하라!

_는 딱 1자를 의미함

%는 0자 이상을 의미하기 때문에 이 조건에 맞지 않음

 

날짜 다루기

### 날짜 다루기
- 날짜 함수와 문자열 함수를 사용하여 날짜 값을 다루는 방법.

테이블 생성 및 테스트 데이터 준비

 

날짜 값 비교하기

특정 날짜의 게시글 찾기

특정 기간의 게시글 조회

 

날짜를 다루는 연산자와 함수

현재 날짜 및 시간 알아내기

 

현재 날짜 알아내기

 

현재 시간 알아내기

 

주어진 날짜, 시간에서 날짜만 뽑거나 시간만 뽑기

 

특정 날짜에 시,분,초,일,월,년을 추가하거나 빼기

date_add(날짜데이터, interval 값 단위);
date_sub(날짜데이터, interval 값 단위);

 

두 날짜 사이의 간격을 알아내기

datediff(날짜1, 날짜2);

 

날짜에서 특정 형식으로 값을 추출하기

date_format(날짜, 형식)

select regdt, date_format(regdt, '%m/%e/%Y') from test1; /* 09/7/2022 */
select regdt, date_format(regdt, '%M/%d/%y') from test1; /* September/07/17 */
select regdt, date_format(regdt, '%W %w %a') from test1; /* Thursday 4 Thu */
select regdt, date_format(regdt, '%M %b') from test1; /* September Sep */
select now(), date_format(now(), '%p %h %H %l'); /* PM 01 13 1 */
select now(), date_format(now(), '%i %s'); /* 05 45 */

 

문자열을 날짜 값으로 바꾸기

 

날짜 값을 저장할 때 기본 형식은 yyyy-MM-dd

 

다음 형식의 문자열을 날짜 값으로 지정할 수 없음

 

특정 형식으로 입력된 날짜를 date 타입의 컬럼 값으로 변환하면 입력할 수 있음

 

 

Foreign Key

# FK(Foreign Key)
- 다른 테이블의 PK를 참조하는 컬럼이다.

첨부파일 정보가 포함된 게시글을 저장하는 테이블을 정의해 보자.

 

한 개의 테이블로 게시글과 첨부파일을 저장하기

## 한 개의 테이블로 게시글과 첨부파일을 저장하기

최대 5개의 첨부파일 경로를 저장할 컬럼을 만든다.

게시글을 저장할 테이블 생성 및 데이터 준비

고민해볼 사항!
- 첨부 파일의 개수를 5 개로 정해 놓았다.
- 따라서 최대 5개의 첨부 파일만 테이블에 저장할 수 있다.
- 첨부파일이 없더라도 5개의 컬럼은 메모리를 차지한다.

실무에서 원하는 것!
- 첨부 파일의 개수에 제한을 받고 싶지 않다.
- 첨부 파일 개수만큼만 값을 저장하고 싶다.

이렇게 같은 데이터를 저장할 컬럼이 중복된 경우에는,
- 중복 컬럼을 별도의 테이블로 분리한다.
- 중복 컬럼의 값이 어느 테이블의 어느 데이터의 값인지 지정한다.

 

 

게시글과 첨부파일의 정보를 여러 개의 테이블에 분산 저장하기

게시판 테이블 생성

 

첨부 파일 테이블 생성

 

게시판 데이터 입력

 

첨부파일 데이터 입력

 

 

FK(Foreign Key) 제약 조건이 없을 때

문제점 1

### 문제점 1
- 첨부파일 데이터를 입력할 때 존재하지 않는 게시물 번호가 들어 갈 수 있다.
- 그러면 첨부파일 데이터는 무효한 데이타 된다.

 

문제점 2

### 문제점 2
- 첨부 파일이 있는 게시물을 삭제할 때,
  해당 게시물을 참조하는 첨부파일 데이터는 무효한 데이터가 된다.

이런 문제가 발생한 이유?
- 다른 테이블의 데이터를 참조하는 경우, 참조 데이터의 존재 유무를 검사하지 않기 때문이다.
- 테이블의 데이터를 삭제할 때 다른 테이블이 참조하는지 여부를 검사하지 않기 때문이다.

해결책?
- 다른 데이터를 참조하는 경우 해당 데이터의 존재 유무를 검사하도록 강제한다.
- 데이터를 삭제하는 경우 다른 테이터에 의해 참조되는지 여부를 검사하도록 강제한다.
- 이것을 가능하게 하는 문법이 "외부키(Foreign Key)" 이다.

 

 

FK(foreign key) 제약 조건 설정

## FK(foreign key) 제약 조건 설정
- 다른 테이블의 데이터와 연관된 데이터를 저장할 때 무효한 데이터가 입력되지 않도록 제어하는 문법이다.
- 다른 테이블의 데이터가 참조하는 데이터를 임의의 지우지 못하도록 제어하는 문법이다.
- 그래서 데이터의 무결성(data integrity; 결함이 없는 상태)을 유지하게 도와주는 문법이다.

 

다른 테이블의 PK를 참조하는 컬럼으로 선언하기

alter table 테이블명
    add constraint 제약조건이름 foreign key (컬럼명) references 테이블명(컬럼명);

=>

기존에 테이블에 무효한 데이터가 있을 수 있기 때문에 먼저 테이블의 데이터를 지움

=>

fk 컬럼을 설정하기 전에 무효한 데이터를 삭제해야 함

 

위와 같이 test2 테이블에 FK 제약 조건을 건 다음에 데이터를 입력해보자!

ㄴ 1번 게시물이 존재하지 않기 때문에 데이터를 입력할 수 없음

ㄴ 5번, 10번 게시물은 존재하기 때문에 첨부파일 데이터를 입력할 수 있다.

ㄴ 2번 게시물은 test2 테이블의 데이터들이 참조하지 않기 때문에 마음대로 지울 수 있음

ㄴ 그러나 5번 게시물은 삭제할 수 없음

     => test2 테이블의 데이터 중 일부가 참조하기 때문

 

용어 정리

## 용어 정리
- test1 처럼 다른 테이블에 의해 참조되는 테이블을 '부모 테이블'이라 부른다.
- test2 처럼 다른 테이블의 데이터를 참조하는 테이블을 '자식 테이블'이라 부른다.