DB

• 조직에 필요한 정보를 얻기 위해 논리적으로 연관된 데이터를 모아 구조적으로 통합해 놓은것
• 데이터 : 관찰의 결과->정량적, 정성적인(=어떤 물질의 성질이나 성질을 밝힌다) 실제 값
• 정보 : 데이터에 의미를 부여한 것
• 지식 : 사물이나 현상에 대한 이해

DB 관련 용어

integrated data

• 데이터의 중복을 최소화하여 중복으로 인한 데이터 불일치 현상을 제거

stored data

• 문서 보관이 아닌 디스크, 테이프 같이 컴퓨터 저장장치에 저장된 데이터를 의미

operational(운영) data

• 조직의 목적(업무)을 위해 사용되는 데이터

shared data

• 한 사람 또는 한 업무를 위해 사용되는 데이터가 아닌 공동으로 사용되는 데이터

DB 특징

real time accessibility

• 실시간으로 서비스, 사용자가 데이터를 요청하면 수 초 내에 결과를 서비스

continuous change

• DB에 저장된 내용은 어느 한 순간의 상태를 나타내지만, 데이터 값은 시간에 따라 항상 바뀐다
• insert, delete, update 등의 작업을 통해 바뀐 데이터 값을 저장

concurrent sharing

• DB는 서로 다른 업무 또는 여러 사용자에게 동시공유
• DB에 접근하는 프로그램이 여러 개 있다는 의미

reference by content(내용에 따른 참조)

• DB에 저장된 데이터는 데이터의 물리적인 위치가 아니라 데이터 값에 따라 참조

Data Base System

1. DBMS : 사용자와 데이터베이스를 연결시켜주는 소프트웨어, 주기억장치에 상주
2. DB : 데이터를 모아 둔 토대
3. data model : 데이터가 저장되는 기법에 관한 내용

• DBMS가 설치되어 데이터를 가진 쪽이 server
• 외부에서 데이터를 요청하는 쪽이 client
• 데이터의 중복을 줄이고 데이터를 표준화하며 무결성을 유지

DBMS

• 데이터를 DBMS 내부에 저장
• 데이터 정의와 데이터 값을 관리
• 프로그램에 데이터 정의나 데이터 값을 포함하지 않기 때문에 데이터 구조가 바뀌어도 다시 컴파일 할 필요가 없다

장점

1. 데이터 중복 최소화
2. 데이터 일관성 유지 -> 중복을 최소화 하였기 때문에 일관성이 유지 된다
3. 데이터 독립성 유지 -> 데이터 정의과 프로그램의 독립성 유지 가능
4. 관리 기능 제공
5. 프로그램 개발 생산성 향상
6. 데이터 무결성 유지
7. 데이터 표준 준수 용이

---

MySQL 기본 명령어

• MySQL 접속 : mysql -u [username] -p; = 사용자가 username에 접속한다
• DB 선택 : user [datebase];
• DB 보기 : show databases;
• DB 생성 : create database [database];
• 테이블 보기 : show tables;

---

SQL

• Structured Query Language
• 관계형 데이터베이스 언어
• Data Sub Language : 데이터 구조에 관한 데이터를 생성하고 처리하는 문법

기능에 따른 분류

1. 데이터 정의어(DDL) 적용대상 = 테이블
   CREATE-생성, ALTER-수정, DROP-삭제
2. 데이터 조작어(DML) 적용대상 = 객체(인덱스, 뷰 등)
   SELECT, INSERT, UPDATE, DELETE
3. 데이터 제어어(DCL)
   GRANT, REVOKE, COMMIT, ROLLBACK
4. 데이터 질의어(DQL)
   SELECT

내부적 실행 순서

• select 속성이름 from 테이블이름 where 검색조건;
  1. from 테이블이름
  2. where 검색조건
  3. select 속성이름

desc 테이블 이름

• 테이블 정보 출력

where

• 비교 : =, <>(=not), >, <, >=, <=
• 범위 : between
• 집합 : in(=or), not in
• 패턴 : like
• NULL : is null, is not null
• 복합조건 : and, or, not
• or보단 in/not in이 퍼포먼스가 좋다

와일드 문자

• '+' : 문자열을 연결
• % : 0개 이상의 문자열과 일치 -> %축구%
• [] : 한 개의 문자와 일치 -> '[0-5]%' : 0~5 사이 숫자로 시작하는 문자열
• [^] : 한 개의 문자와 불일치 ->'[^0-5]%' : 0~5 사이 숫자로 시작하지 않는 문자열
• : 특정 위치의 한 개의 문자와 일치 -> '구%' : 두 번째 위치에 '구'가 들어가는 문자열

order by

• 정렬
• asc(오름차순) 가 기본

commit

create : commit x
insert into : commit o -> rollback을 염두하기 때문

트랜잭션

• 테이블의 데이터를 변경(입력/수정/삭제)할 때 실제 테이블에 완전히 적용하지 않고 임시로 적용시키는 것 rollback 가능

DDL : 정의

• create, alter, drop, truncate

create

create table 테이블이름(
1. 속성이름 데이터타입 [not null] [default 기본 값]
2. [primary key (속성리스트)]
3. [unique (속성리스트)]
4. [foreign key(속성리스트) references 테이블이름(속성리스트)]
5. [constraint 이름] [check(조건)]
);

• auto_increment

create table testtbl2(id int auto_increment primary key, userName char(3), age int);
select last_insert_id(); -- auto_increment가 얼마나 증가되었나 확인
alter table testtbl2 auto_increment = 100;
set @@auto_increment_increment = 3;

대량의 데이터 삽입하는 방법

• 제약조건은 자동으로 가져올 수 없다

-- 대량의 샘플데이터 생성방법 : insert into ... select 구문
-- 테이블 생성 후 데이터 삽입
create table testtbl3(id int, Fname varchar(50), Lname varchar(50));
insert into testtbl3 select emp_no, first_name, last_name from employees.employees;
select count(id) from testtbl3;
commit;

-- testtbl4 employees.employees 테이블 속성 중 emp_no, first_name, last_name
-- 테이블 생성과 동시에 데이터 삽입
create table testtbl4 (select emp_no, first_name, last_name from employees.employees);
desc testtbl4;
select count(emp_no) from testtbl4;

drop

• 테이블 자체를 삭제한다

drop table 테이블이름;

truncate

• delete보다 속도면에서 훨씬 빠르다 -> 트랜젝션 로그를 기록하지 않기 때문에

truncate table 테이블명;

alter

alter table 테이블명 add column 컬럼명 데이터타입 after 컬럼명;
alter table 테이블명 add 컬럼명 데이터타입;
alter table 테이블명 drop column 컬럼명;
alter table 테이블명 add constraint 제약조건이름 check(제약조건);
alter table 테이블명 drop constraint 제약조건이름;

• sql문은 대소문자 구분x (값은 구분한다)

키

• primary key :
  1. unique + not null
  2. 기본키를 지정하는 키워드
  3. primary key(customer_id)
• unique :
  1. 대체키를 지정
  2. 대체키로 지정된 속성의 값은 유일성을 가진다
  3. 기본키와 달리 null 값이 허용된다
  4. unique(customer_name)
• foreign key :
  1. 외래키를 지정
  2. 외래키가 어떤 테이블의 무슨 속성을 참조하는 references 키워드 다음 제시 -> 참조 하는 테이블에 대해 종속적이다
  3. 참조 무결성 제약조건을 유지하기 위한 키

//java에서의 references 예시
class Customer{
	Product product;
    Customer(Product product){
    	this.product = product;
    }
}

check

• 데이터 무결성 제약조건의 정의
• 테이블에 정확하고 유효한 데이터를 유지하기 위해 특정 속성에 대한 제약조건을 지정
• constraint 키워드와 함께 고유의 이름을 부여할 수도 있다

check(age>=0 and age<=20)
constraint chk_cpy check(제조업체='냥냥')

DML : 조작

• select, insert, delete, update

select

• all/distinct
• select 문으로 테이블 복사시 키에 대한 제약조건은 복사되지 않기때문에 따로 지정해야한다

group by

-- 사용자 별 한 번 구매시 평균 몇개를 구매했는지 출력
select userID , prodName, avg(amount) 평균구매수 from buytbl group by userID, prodName;
-- from -> group by -> select

having

-- group by의 조건은 having절로
-- 사용자별 총 구매액 출력
select userID 사용자, sum(price*amount) 총구매액 from buytbl group by userID;
select userID 사용자, sum(price*amount) 총구매액 from buytbl group by userID having 총구매액>=1000;
-- from -> group by -> having -> select

집계함수

• count(), sum(), avg(), max(), min()...

select count(속성리스트) as '이름설정' from 테이블명 group by 속성리스트;

update

• 기존에 입력되어 있는 값을 변경하는 명령어

update 테이블명 set 변경할속성명 = 변경할값 where 변경할속성을지칭='';

delete

• row, tuple 단위 삭제
• limit 사용 가능
• 튜플(데이터) 삭제로 테이블의 구조는 유지된다

delete from 테이블이름 where 조건; 

insert

-- on duplicate key update 로 기존 Pk를 수정할 수 있다.
-- 만약 없는 값이라면 그냥 insert into 진행
insert ignore into 테이블명 values('BBK','비비케이_2','미국') 
on duplicate key update name='비비케이_2', addr='미국';

cte

• common table expression
• 일회성

1. 재귀적 cte

with cte_테이블명(열이름...) as (쿼리문) select 열이름 from cte_테이블명;
with abc(userid, total) 
as (select userid, sum(price*amount) 총구매액 from buytbl group by userid) 
select * from abc order by total desc;

2. 비재귀적 cte

with

• cte를 표현하기 위한 구문

rollup

• 항목별 합계에 대한 총합을 구한다

-- 총합 또는 중간합계를 구하려면 group by절과 with rollup 문 사용
-- groupName(분류) 별 합계와 총합을 구하기
select * from buytbl;
select num, groupName, sum(price*amount) 비용 from buytbl group by groupName, num with rollup;

---

테이블에 작성된 제약조건 확인하기

select * from information_schema.table_constraints where constraint_schema='테이블이름';

---

관계 데이터 모델

• 릴레이션(sql로 생성 및 관리), 제약조건(sql로 제약 선언), 관계대수(sql로 연산)를 가진다
• 데이터를 2차원 테이블 형태인 릴레이션으로 표현한다
• 릴레이션에 대한 제약조건과 관계연산을 위한 관계대수를 정의한다

릴레이션

• 행과 열로 구성된 테이블
• 릴레이션 내에서 생성되는 관계 : 릴레이션 내 데이터들의 관계
• 릴레이션 간에 생성되는 관계 : 릴레이션 간의 관계 -> foreign key
• 속성(애트리뷰트, 열) = 필드
• 투플(행)
• 스키마(내포) = 헤더
• 인스턴스(외연) = 데이터

릴레이션 스키마

• 속성 attribute : 릴레이션 스키마의 열
• domain : 속성이 가질 수 있는 값의 집합
• 차수 degree : 속성의 개수

스키마의 표현

• 릴레이션 이름(속성1:도메인1, 속성2:도메인2...)

릴레이션 인스턴스

• tuple : 릴레이션의 행
• cardinality : 투플의 수

특징

1. 속성은 단일 값을 가진다
2. 속성은 서로 다른 이름을 가진다
3. 한 속성의 값을 모두 같은 도메인 값을 가진다
   = int로 지정하면 varchar로 쓸 수 없다
4. 속성의 순서는 상관없다

select name, id, age or select id, name, age -> 내 맘대로

5. 릴레이션 내의 중복된 투플은 허용하지 않는다
6. 투플의 순서는 상관없다
   -> 개체 무결성 제약 조건(primary key)
   -> 독립적인 매핑 구조(입력한 순서는 상관없다)

무결성 제약 조건

1. 데이터 무결성

• 데이터베이스에 저장된 데이터의 일관성과 정확성을 지켜야한다

2. 도메인 무결성 제약조건

• 도메인 제약
• 릴레이션 내의 튜플들이 각 속성의 도메이네 지정된 값만을 가져야한다
• sql 문에서 type, null, default, check 등을 사용해서 지정할 수 있다

3. 개체 무결성 제약조건

• 기본키 제약
• 기본키는 null 값을 가져서는 안되며 릴레이션 내에서 유일한 값이어야 한다는 조건

4. 참조 무결성 제약조건

• 외래키 제약
• 릴레이션 간의 참조 관계를 선언하는 제약조건(부모-자식 관계)
• 자식 릴레이션의 외래키는 부모 릴레이션의 기본키와 도메인이 동일해야 하며
  자식 릴레이션의 값이 변경될 때 부모 릴레이션의 제약을 받는다
• restricted : 자식 릴레이션이 있으면 삭제 작업을 거부
• cascade : 자식 릴레이션에 있는 관련 투플까지 함께 삭제
• default : 자식 릴레이션의 관련 투플을 미리 설정해 둔 값으로 변경
• null : 자식 릴레이션의 관련 투플을 null값으로 설정

---

• 데이터베이스는 데이터 저장 시
  1. 일관성 유지
  2. 중복을 최소화 를 통해 데이터의 신뢰성을 유지한다
• 데이터의 삽입, 삭제, 수정 시 여러가지 제약조건이 따른다.

---

키

• 속성
• 특정 튜플을 식별
• 릴레이션 간의 관계를 맺는데 사용 -> foreign key

슈퍼키

• 튜플을 유일하게 식별할 수 있는 하나의 속성 혹은 속성의 집합
• ex>
  • 고객번호(가능)
  • 이름(불가능->동명이인)
  • 주민번호(가능)
  • 주소(불가능->가족들의 주소가 모두 같다)
  • 주민번호, 이름 -> 주민번호를 포함하였기에 가능
  • 주민번호, 이름, 주소

후보키

• 튜플을 유일하게 식별할 수 있는 속성의 최소 집합
• ex> 고객번호, 도서번호
  -> 각각은 후보키가 될 수 없지만, (고객번호, 도서번호) 복합키라면 가능하다
  -> 한 고객이 여러 권의 책을 살 수 있고, 한 권의 도서를 여러명이 구매할 수 있으므로

복합키

• composite key
• 2개 이상의 속성으로 이루어진 키

기본키

• 여러 후보키 중 하나를 선정하여 대표로 삼는 키
• 후보키가 하나라면 그 후보키를 사용하면 되고, 여러 개라면 릴레이션의 특성을 반영하여 하나를 선택하면 된다
• 릴레이션 내 튜플을 식별할 수 있는 고유한 값
• null 허용 x
• 변동이 없어야 한다
• 최대한 적은 수의 속성을 가진 것 이어야 한다

대리키 surrogate key

• 인조키 artificial key 라고도 한다
• dbms나 관련 소프트웨어에서 임의로 생성하는 값으로 사용자가 직관적인 그 값의 의미를 알 수 없다

대체키

• 기본키로 선정되지 않은 후보키
• 고객 릴레이션의 경우 고객번호와 주민번호 중 고객번호를 기본키로 정하면 주민번호가 대체키가 된다

외래키

• 다른 릴레이션의 기본키를 참조하는 속성
• 외래키와 기본키 양쪽 릴레이션의 도메인은 서로 같아야 한다
• 기본키 값이 변경되면 외래키 값도 변경된다
• null 값과 중복 값 등이 허용
• 자기 자신의 기본키를 참조하는 외래키도 가능하다= self join
• 외래키가 기본키의 일부가 될 수 있다

---

튜플, 릴레이션, 도메인, 엔티티에 대해 정리

---

SubQuery

단일행 서브쿼리

-- 김경호보다 키가 크거나 같은 사람의 이름과 키를 출력
select name, height from usertbl where name='김경호';
select name, height from usertbl where height > 177; 
select name, height from usertbl where height > (select height from usertbl where name='김경호');

다중행 서브쿼리

-- 지역이 경남 사람의 키보다 키가 크거나 같은 사람의 이름과 키를 출력
select name, height, addr from usertbl where height = any (select height from usertbl where addr='경남');
select name, height, addr from usertbl where height in (select height from usertbl where addr='경남');

select name, height from usertbl where height >= any (select height from usertbl where addr='경남');
select name, height from usertbl where height >= some (select height from usertbl where addr='경남');

select name, height from usertbl where height >= all (select height from usertbl where addr='경남');

---

• 데이터의 일관성을 위해 제약조건이 필요하다 -> 데이터 무결성, 도메인 무결성, 개체 무결성 제약조건
• 도메인 무결성, 개체 무결성 : 투플 삽입/수정 시 제약사항 우선 확인
• 참조 무결성 : +) 부모 릴레이션의 투플 수정/삭제 시 제약사항 우선확인
• unique 제약 조건 : null값은 허용하고, 투플을 유일하게 식별할 수 있다
• 데이터의 변경 = 삽입, 수정, 삭제
• pk는 삭제되면 영구결번처리

---