[db] 기본 개념 정리

 

DB 라는 것을 배운지가 어연 몇년이 지났는데,

정말 기초적인 부분에서 지적을 받은 뒤 많은 충격을 받았다

내가 이렇게 기초가 허술했나..?

당연한 것 처럼 익숙하게 썼던 것 중에 아주 기본적인 부분을 틀리게 썼다는 것에 너무 스트레스를 받았었다ㅜ

 

시간을 내서라도 기본적인 공부는 다시 해야겠다 싶어 다시 시작해본다!!

 

개념이나 기초 위주는 그냥 정리만 해보는 걸로

 

 

Database 특성

- 실시간 접근 및 응답

- 최신성(동적)

- 동시공용

- 내용기반참조(키워드)

 

 

 

데이터모델 3요소

data model 이란 데이터를 명세하는 고유 표현 방식이자 data modeling 을 위한 도구

- 데이터 구조(data structure) 

- 연산(operation)

- 제약조건(constraint)

 

 

RDB

- 테이블 형태의 릴레이션을 통해 데이터를 저장, 관련성을 표현

- DB를 논리적 수준에서 이해/사용할 수 있도록 함

 

RDB 3요소

- 데이터를 저장하는 기본 데이터 구조 -> 릴레이션

- 릴레이션 데이터를 처리하는 연산은 관계 대수를 사용

- 제약사항을 무결성 제약 조건으로 명세

 

 

관계형 데이터베이스 모델 (relational database)

• 릴레이션으로 불리는 2차원 테이블 형태의 단순 구조에 데이터를 저장하는 방식
• 테이블 구조는 릴레이션 개념을 직관적으로 쉽게 이해하도록 함

릴레이션 (relation)

• 관계형 데이터 모델의 핵심 요소로 특별한 의미를 갖는 테이블
• 테이블 이상의 많은 의미와 제약 사항이 숨겨져 있음

 

 

릴레이션 관련 용어 정리

 

속성(attribute)

• 테이블의 열 (column)
• 데이터를 표현하는 가장 작은 논리적 단위
• 의미적으로 더 이상 분해할 수 없는 원자 값 (atomic value)만 사용
• 릴레이션이 표현하는 대상의 주요 특성들을 서로 다른 이름으로 구별하여 표현

튜플(tuple)

• 테이블의 각 행 (row)
• 현실 세계의 개체를 표현
• 각 속성 값 (attribute) 들의 조합으로 구성
• '학생' 릴레이션에서 하나의 튜플은 학생 개체 한 명의 정보를 표현

도메인(domain)

• 각 속성이 취할 수 있는 모든 값들의 집합을 정의한 것
• 데이터 값들의 유형과 크기 , 범위를 정의

카디널리티와 차수

• 각 릴레이션은 카디널리티와 차수를 통해 그 구성이 정의됨

카디널리티(cardinality)

• 릴레이션 안의 전체 튜플의 개수
• 입력, 수정, 삭제 등을 통해 계속 변화
• 동적 특성

차수(degree)

• 릴레이션을 구성하는 전체 속성의 개수
• 각 튜플이 가지는 속성 값의 개수는 릴레이션의 차수와 같음
• 정적 특성

 

Relation Structure

 

릴레이션(relation)

• 릴레이션 스키마와 릴레이션 인스턴스 2가지 요소로 구성

 

릴레이션 스키마(relation schema)

• 특정 릴레이션의 논리적 구조를 의미
• 릴레이션의 이름과 릴레이션 안에 포함된 모든 속성의 이름들로 정의
• 테이블의 첫 번째 행인 헤더 부분에 표현
• 릴레이션 내포 라고도 부름
• 시간이 경과해도 좀처럼 변경되지 않는 정적인 특성

 

릴레이션 인스턴스(relation instance)

• 어느 한 시점에 릴레이션에 존재하는 튜플들의 집합
• 보통 테이블의 첫 번째 행인 헤더 부분을 제외한 나머지 모든 행들의 집합으로 표현
• 릴레이션 외연 이라고도 부름
• 특정 시점에서의 전체 튜플들의 내용 즉 , 릴레이션의 상태를 의미
• 시간에 따라 변하는 동적인 특성이 있음

 

 

 

SELECT문의 기본 문법
SELECT [ALL | DISTINCT] 속성이름(들)
FROM 테이블이름(들)
[WHERE 검색조건(들)]
[GROUP BY 속성이름]
[HAVING  검색조건(들)]
[ORDER BY 속성이름 [ASC | DESC]]

 

 

 

실무에서 쓰는 sql 정리

--데이터에서 목록보기
show database;

--사용할 데이터베이스 선택하기
use myschool;

--현재 데이터베이스에서 테이블 목록 보기
show tables;

--특정 테이블의 구조 확인
desc department;

--데이터 조회하기
select [distinct] * | 컬럼이름 [[as] `별칭`] from 테이블이름        // distict 는 중복제거
[where 검색조건]
[order by 컬럼이름 [ASC|DESC]]
[limit 조회시작위치, 조회할 데이터 수];

-- 문자열 붙이기
select concat(name, ' ', grade, '학년') from student;

-- 암호화
select password(idnum) from student;

-- 시스템 현재시각 조회
now()

-- 날짜 연산
date_add(시각,INTERVAL 값 단위)
select date_add(now(), INTERVAL -3 MONTH);
select date_add(birthdate INTERVAL 1 YEAR) from student;


-- 주어진 시각을 형식에 맞춰 변경한 결과를 리턴
date_format(시각, 형식)
select date_format(now(), '%y/%m/%d %H:%i:%s');



5) query Tip
*보다는 전체컬럼 : *로 조회하는것보다 차라리 전체 컬럼을 다 명시하는게 성능에 유리하다
ASC 생략가능 : order by 는 기본값이 asc이므로 asc는 생략가능
in('과자', '사과', '사자')
"안쓰고'아니고 ` : 쿼리문에 큰따옴표는 안쓰고, (`)를 이용하여 묶는다
IS NOT NULL
between A and B
like '%영%'
limit 위치, 몇건
함수쓸 때 별칭 지정하면 변수처럼 쓸수있다
집계함수는 where 절에서 사용불가


6 ) 그룹조회
where절을 사용하여 검색된 결과에 대해서 그룹핑 가능


7) JOIN

7-1) EQUI JOIN : 두개 이상의 테이블에서 동시 데이터 검색
SELECT a,b
FROM table1, table2
WHERE ~~~

7-2) INNER JOIN : EQUI 조인의 다른형태
SELECT a,b
FROM table1
INNER JOIN table2
ON ~~~
(위 차이점 INNER JOIN은 조인조건에 ON절을 사용하기 때문에, 추가적인 검색 조건은 WHERE 절을 사용한다.)
조건에 맞지 않은 애(null)는 누락시키고 이걸 누락시키지않고 전체 조회를 하는 쿼리는 ↓↓↓ 로 처리한다

7-3) OUTER JOIN
양쪽 테이블 중에서 어느 한 테이블의 모든 데이터를 출력해야 하는 경우에 사용하고 (LEFT, RIGHT)
지정된 방향의 테이블에서 모든 데이터  출력을 보장한다.
(헷갈리니까 구분없이 그냥 LEFTOUTER쪽으로 몰아놓고 쓰자)

7-4) 서브쿼리
서브쿼리가 여러개의 결과를 가지고 올때 IN으로 조회하면된다.


8) JOIN Tip
테이블의 별칭을 쓰면 겹치는 변수쓸때 간결하게 쓸 수 있음
select s.name, p.name from student s, professor p

9) DDL Table 단위 SQL
9-1) INSERT (아래 형태로 써야 실무에서 이쁨받는다)
INSERT INTO student (
        name, userid, tel, column1, 
        column2, column3(--int일 경우), column4(--boolean일경우), 
) VALUES (
        깅택미', 'delightpip', '010123412340, 'column1', 'column2', column3, true
);

기본키(PK) : 테이블의 각행을 고유하게 식별하는 값 (데이터 무결성 제약조건)
기본키가 숫자 형태일 경우에 Extra라는 속성을 이용해서 auto_increment 자동증가 일련번호를 사용한다.
재설정 하고 싶으면 해제했다가 다시 설정하면 됨

MySQL에는 id, 사용자 아이디는 userid, username로 설정
MSSQL에는 num
ORACLE에는 seq라고 한다

HTML <form> 태그의 name 속성을 DB의 컬럼명과 맞추는게 좋다

9-2) UPDATE
UPDATE 테이블이름 SET 컬럼1=값 WHERE 검색조건;

9-3) DELETE
DELETE FROM 테이블이름 WHERE 검색조건;
하나 꼭집어서 지우는 경우가 많이 때문에 WHERE 절에 주로 PK값이 들어간다

 

 

 

DB 백업/복구

백업

mysqldump -uroot -p --database 데이터베이스이름 > 백업파일저장경로

복구

mysql -uroot -p < 백업파일 저장경로

 

 

 

프로시져

DELIMITER // 구문을 만들 준비 를 하겠다.

CREATE PROCEDURE myProc()

// myProc() 라는 이름의 프로시저 함수를 만들겠다. ( myProc 옆에 괄호를 붙여준 이유는 함수임을 파악하기 쉽게 하려고)

BEGIN // 쿼리시작하겠다

... //쿼리문 작성

END // 쿼리 끝인지!

DELIMITER; // 구문을 끝내겠다.

 

 

참고하려고 쓰는 것

mybatis에서 selectKey 사용법

mybatis에서 selectKey 사용법