[Linux (리눅스)/#12 DB(데이터 베이스)]

DataBase

관계형 데이터베이스는 현재 가장 많이 사용하는 데이터베이스 모델입니다.
Oracle의 Oracle Database가 가장 유명하고 널리 사용 됩니다.
오픈 소스 데이터베이스 : MySQL, MariaDB, PostgreSQL 서버 등의 제품들이 널리 사용되고 있음.

• 통합하여 관리되는 데이터의 집합체
• 중복된 데이터 최소화, 자료 구조화, 효울적인 처리를 할 수 있도록 관리
• 데이터 베이스 정의
  • 통합된 데이터
    • 자료의 중복을 배제한 데이터 모임
    • 저장된 데이터
    • 컴퓨터가 접근할 수 있는 저장 매체에 저장된 자료
  • 운영 데이터
    • 조직의 고유한 업무를 수행하는 데 존재 가치가 확실하고 없어서는 안되는 반듯 필요한 자료
  • 공용 데이터
    • 여러 응용 시스템들이 공동으로 소유하고 유지하는 자료
• 데이터 베이스 특징
  • 실시간 접근성
    • 수시적인고 비정형적인 질의에 대해 실시간 처리에 의한 응답 가능
  • 계속적인 변화
    • 새로운 데이터의 삽입, 삭제, 갱신으로 항상 최신 데이터 유지
  • 동시 공용
    • 다수의 사용자가 동시에 같은 내용의 데이터 이용
  • 내용에 의한 참조
    • 데이터베이스에 있는 데이터를 참조할 때 데이터 레코드의 주소나 위치에 의해서가 아니라, 사용자가 요구하는 데이터 내용으로 데이터를 찾음
  • 데이터 독립성
    • 응용 프로그램과 데이터베이스를 독립, 데이터의 논리적 구조를 변경 시켜도 응용 프로그램은 변경이 안됨
• 데이터 베이스 용어 #1
  • 엔티티(Entity) = 독립체
    • 사람, 장소, 사물, 사건 등과 같이 독립적으로 존재하면서 고유하게 식별이 가능한 실세계의 객체.
    • 
  • 엔티티 집합(Entity Set)
    • 동일한 속성을 가진 엔티티들의 집합. 엔티티 집합에 속한 요소들이 여러 엔티티 집합에 속할 수도 있음
  • 스키마(Schema)
    • 데이터 베이스의 구조를 전반적으로 기술한 것
    • 구체적으로 데이터 베이스를 구성하는 데이터 개체(Entity), 속성(Attribut), 관계(Relaionship) 등을 정의 한 것
    • 사용자의 관점에 따라 외부 스키마, 개념 스키마, 내부 스키마로 구분
    • DBMS는 외부 스키마에 명세된 사용자의 요구를 개념 스키마 형태로 변환하고, 이를 다시 내부 스키마 형태로 반환

• 스키마( Schema )
  
  • 외부 스키마(External) : 사용자 뷰
    • 사용자의 입장에서 정의한 데이터 베이스의 논리적 구조
    • 데이터들을 어떤 형식, 구조, 화면을 통해 사용자에게 보여줄 것인가에 대한 명세를 말하며 하나의 데이터베이스에는 여려개의 외부 스키마가 있을 수 있음
    • 일반 사용자는 SQL을 이용하여 DB를 쉽게 사용 할 수 있음
    • 응용 프로그래머는 C, 자바 등의 언어를 사용하여 DB에 접근
  • 개념 스키마(Conceptual) : 전체적인 뷰
    • 데이터베이스의 전체적인 논리적 구조
    • 모든 이용자가 필요로 하는 데이터를 총합한 조직 전체의 데이터 베이스로 하나만 존재함
    • 개체 간의 관계와 제약 조건 데이터 베이스의 접근 권한,보안 등에 관한 명세를 나타냄
    • 데이터 베이스 관리자에 의해서 구성
  • 내부 스키마(Internal)
    • 물리적 저장 장치의 입장에서 본 데이터 베이스 구조
    • 실제로 데이터베이스에 저장될 레코드의 물리적인 구조, 저장 데이터 항목의 표현 방법, 내부 레코드의 물리적 순서등을 나타냄

• 데이터베이스 용어 #2
  • 테이블( table ), 관계( relation )
    • 관계형 데이터 베이스에서 정보를 구분하여 저장하는 기본 단위.
  • Tuple( =Record)
    • 테이블에서 행을 의미. 튜플은 릴레이션에서 같은 값을 가질 수 없음
    • 튜플의 수는 카디날리티(Cardinality)라고 함
  • Attribute( =Field)
    • 테이블에서 열을 의미. 같은 말로는 칼럼이라고도 하며 어트리뷰트의 수는 디그리(Degree)라고도 함
  • 식별자( Identifier)
    • 여러개의 집합체를 담고있는 관계형 데이터 베이스에서 각각의 구분할 수 있는 논리적인 개념
    • 특징
      • 유일성 : 하나의 릴레이션에서 모든 행은 서로 다른 키 값을 가져야 함
      • 최소성 : 꼭 필요한 최소한의 속성들로만 키를 구성해야 함
  • KEY
    • 데이터베이스에서 조건에 만족하는 튜플을 찾거나 순서대로 정렬할 때 튜플들을 서로 구문할 수 있는 기준이 되는 어트리뷰트
  • 후보키(Cardidate Key)
    • 릴레이션을 구성하는 속성들 중에서 튜플을 유일하게 식별하기 위해 사용하는 속성들의 부분 집합
    • 기본 키로 사용할 수 있는 속성들을 의미
    • 모든 릴레이션에는 반드시 하나 이상의 후보키가 존재
    • 유일성과 최소성을 만족시켜야 함
  • 기본키( Primary Key)
    • 후보키 중에서 선택한 Main Key
    • 한 릴레이션에서 특정 튜플을 유일하게 구별할 수 있는 속성
    • null 값을 가질 수 없음
    • 동일한 값이 중복되어 저장될 수 없음
  • 슈퍼키 (Super Key)
    • 한 릴레이션 내에 있는 속성들의 집합으로 구성된 키로서 릴레이션을 구성하는 모든 튜플들 중 슈퍼키로 구성된 속성의 집합과 동일한 값을 나타나지 않음
    • 릴레이션을 구성하는 모든 튜플에 대해 유일성을 만족시키지만, 최소성을 만족시키지 못함
    • ex) 학번 + 주민번호를 사용하여 슈퍼키를 만들면 유일성은 만족하지만, 학번이나 주민번호 하나만 가지고도 다른 튜플들을 구분할 수 있으므로 최소성은 만족시키지 못함
  • DML
    • Data Multipulation Language (데이터 조작어)
    • 저장된 데이터를 실질적으로 처리하는데 사용하는 언어. 데이터 베이스의 생성 및 변경, 제거
  • DDL
    • Data Definition Langauge(데이터 정의어)
    • 데이터 베이스를 정의하는 언어. 데이터베이스 안의 값들을 변경, 수정, 입력
  • DCL
    • Data Control Language(데이터 제의어)
    • 데이터베이스에 접근하거나 객체에 권한을 주는 역할. 데이터 베이스의 접속 권한 등을 수정

  데이터베이스 관리 시스템(DBMS, DataBase Management System)

• DBMS의 종류
  • 관계형 데이터베이스( RDBMS ) :
    • 행(Column)과 열(Row)을 가지는 표 형식 데이터를 저장하는 형태의 데이터 베이스
      • SQL을 이용하여 관리 및 접근
      • 관계 대수(Realional Algebra)에서 착안하여 만들어 짐
    • 장점
      • 다양한 용도로 사용 가능(범용성), 높은 성능
      • 데이터 무결성 보장, 높은 신뢰성
      • 명확한(= 엄격한) 스키마 정의
      • 정규화에 따른 갱신 비용 최소화
      • 데이터의 분류, 정렬, 탐색 속도가 빠름
      • 관계를 통해 각 데이터를 중복없이 한 번만 저장
    • 단점
      • 대량의 데이터 입력 처리
      • 갱신이 발생한 테이블의 인덱스 생성 및 스키마 변경
      • 컬럼 확장 어려움 -> 유연성 부족
      • 데이터베이스 부하 분석이 어려움
      • 수평 확장의 어려움 -> 데이터베이스 저장 방식으로 인해 수직 확장만 지원
  • 객체지향 데이터베이스( ODBMS )
    • 객체 그대로를 데이터베이스의 데이터에 저장하는 것
      • 멀티미디어 데이터의 원할한 처리
      • RDBMS의 비지니스형 데이터 타입만 처리되는 제한적 극복
  • NoSQL( Not Only SQL )
    • 키(key) : 값(Value)의 형태로 저장되는 데이터베이스, 키를 사용해 데이터 관리 및 접근
      • 스키마 없음, 관계 없음
      • Join이 존재하지 않고 데이터를 가져올 때, 컬렉션에 있는 데이터를 복제하여 필요한 데이터의 일부만 가져옴
      • 정확한 데이터 요구 사항을 알 수 없거나 관계를 맺는 데이터가 자주 변경될 때 사용
      • 읽기를 자주 하지만 데이터를 자주 변경하지 않는 경우 사용
    • 장점
      • 높은 유연성(스키마X) -> 저장된 데이터를 조정하고 새로운 필드 추가 가능
      • 애플리케이션에 필요한 형식으로 저장 -> 데이터를 가져오는 속도가 빠름
      • 수직 및 수평 확장 가능
    • 단점
      • 데이터 구조 결정이 늦어짐
      • 복사된 데이터가 변경되면 모든 콜렉션과 문서 수정 필요
    • RDB와 NoSQL의 차이점
      • NoSQL은 비정형 데이터(메신저 텍스트, 음성, 이미지 등)를 다룰 수 있음
      • 수평 확장이 가능하여 분산형 컴퓨팅(클라우드)에 적합

SQL 사용법

• 패키지 설치
 mariadb-server 혹은 mariadb, mariadb-client 
• 서비스 활성화
 mariadb
• 방화벽 설정
 mysql
• 기본 보안 설정
 mysql_secure_installation
• 설정파일
 /etc/my.cn
-----------------------------------------------------
• mysql_secure_installation
 - Enter current password for root (enter for none) :
현재 root 계정의 패스워드를 입력, 초기값은 설정되지 않았기 때문에 Enter 입력

Switch to unix_socket authentication [Y/n]
 - 유닉스 소켓이라는 인증 방식으로 전환할 것인지 물어보는 질문

Change the root password? [Y/n]
- root password를 변경할 건지 물어보는 질문

Remove anonymous users? [Y/n]
- 익명 사용자를 제거할 건지 물어보는 질문. 만약 Y를 하면 mysql -u root 로 로그인 해야 됨
- n를 하면 ‘mysql’로도 로그인 되지만 익명 사용자가 권한을 갖는 것이기 때문에 보안상
지워주는 게 좋음

 Disallow root login remotely [Y]
- localhost의 ip가 아닌 곳에서 root로 로그인이 가능하게 할 지에 대한 질문
- Y를 하면 원격 로그인이 안됨
- test 용도이며 장소를 옮긴다 하면 n를 권장. 로컬에서만 사용하면 y

Remove test database and access to it? [Y/n]
- test 데이터베이스를 제거할지에 대한 질문
- 쓸 일이 없으면 n

 Reload privilege tables now? [Y/n]
- 권한을 변경을 했다면 y

-----------------------------------------------------

구문	설명
SELECT	데이터 조회
INSERT	데이터 추가
UPDATE	데이터 수정
DELETE	데이터 삭제
CREATE	객체 생성
DROP	객체 제거
ALTER	객체 특성 수정
GRANT	권한 부여
REVOKE	권한 제거
COMMIT	수정사항 적용
ROLLBACK	수정사항 반영 안함

• SELECT 구문

  • 데이터를 조회하기 위해 사용
  • 특정 테이블 및 컬럼을 지정해서 확인
  • 필요에 따라 조건을 부여해 알맞은 데이터 검색
    • SELECT 컬럼이름
    • FROM 테이블이름
    • WHERE 조건문
    • ORDER BY 정렬방식

• DML 구문

  • 데이터를 수정할 때에 사용하는 구문들
  • 데이터 추가 시 INSERT 구문 사용
    • INSERT INTO 테이블이름 및 컬럼이름
    • VALUES 입력할 데이터
    • WHERE 조건문
  • 데이터 삭제시
    • DELETE FROM 테이블 이름 및 컬럼 이름
    • WHERE 조건문
  • 데이터를 수정할 경우
    • UPDATE 테이블이름 및 컬럼이름
    • SET 새로 입력할 데이터
    • WHERE 수정할 위치

• TCL 구문 : COMMIT / ROLLBACK

  • MariaDB 는 기본적으로 autocommit 이 활성화

• DDL문장

  • 객체 생성 및 수정 작업에 사용하는 언어(주로 테이블)
  • 객체 생성 시 CREATE 구문 사용
    • CREATE TABLE 테이블 이름
    • (컬럼이름 데이터유형 (길이) 제약조건....)
  • 객체 제거 시 DROP 구문 사용
    • DROP TABLE 테이블 이름
  • 객체의 특성(구조) 변경시 사용
    • 추가
      • ALTER TABLE 테이블이름
      • ADD 컬럼 이름 데이터 타입
    • 제거
      • ALTER TABLE 테이블이름
      • DROP 컬럼 이름
    • 컬럼 타입 변경
      • ALTER TABLE 테이블이름
      • MODIFY 컬럼이름 데이터 타입
    • 컬럼에 특성 추가
      • ALTER TABLE 테이블이름
      • 특성 컬럼이름

• 사용자 관리

  • 사용자 추가
    • CREATE USER 사용자 이름@ 호스트네임
    • IDENTIFIED BY 패스워드
  • 사용자 제거
    • DROP USER 사용자이름@호스트네임

===================================================

데이터 베이스 실습

mysql -u root -p

데이터 베이스 구문

SHOW DATABASES;

CREATE DATABASE test;
Use test;

SHOW tables;

USE mysql;

SHOW tables;

DESCRIBE user;

-----------------------------------------------------

SELECT 구문

SELECT host FROM user;

SELECT host,user,password FROM user;

WHERE 구문

* 숫자

- 부등호, 등호

SELECT host,user,password,max_updates FROM user WHERE max_updates = 0;

SELECT host,user,password,max_updates FROM user WHERE max_updates > 0;

SELECT host,user,password,max_updates FROM user WHERE max_updates >= 0;

SELECT host,user,password,max_updates FROM user WHERE max_updates != 1;

SELECT host,user,password,max_updates FROM user WHERE max_updates <> 1;

- BETWEEN number AND number

SELECT host,user,password,max_updates FROM user WHERE max_updates BETWEEN -1 AND 1;

SELECT host,user,password,max_updates FROM user WHERE max_updates BETWEEN 0 AND 1;

SELECT host,user,password,max_updates FROM user WHERE max_updates BETWEEN 1 AND 2;

- IN (....)

SELECT host,user,password,max_updates FROM user WHERE max_updates IN (1,2,3,4);

SELECT host,user,password,max_updates FROM user WHERE max_updates IN (1,2,3,0);

- IS NULL & IS NOT NULL

SELECT host,user,password,max_updates FROM user WHERE max_updates IS NULL;

SELECT host,user,password,max_updates FROM user WHERE max_updates IS NOT NULL;


* 문자열 검색 

- =

SELECT host,user FROM user WHERE host = 'localhost';

- LIKE

SELECT host,user FROM user WHERE host LIKE 'localhost';

SELECT host,user FROM user WHERE host LIKE 'local_';

SELECT host,user FROM user WHERE host LIKE 'local_%';

SELECT host,user FROM user WHERE host LIKE '_ocal%';

SELECT host,user FROM user WHERE host LIKE 'ocal%';

SELECT host,user FROM user WHERE host LIKE 'localhost%';

SELECT host,user FROM user WHERE host LIKE '_____host%';

SELECT host,user FROM user WHERE host LIKE '_o___host%';

- LIKE + 논리연산자 (AND , OR)

SELECT host,user FROM user WHERE host LIKE '%local%' AND user LIKE 'root';

SELECT host,user FROM user WHERE host LIKE '%local%' OR user LIKE 'root';


----------------------------------------------------------------------------

DML 구문

* 준비 과정 ( 테이블 생성 )

USE test;

CREATE TABLE user 
( name varchar(10), 
uid int,
locate varchar(10), 
email varchar(20)   );

char(10) : 2글자 건 10글자 건 무조건 10글자 인식
varchar(10) : 사용하는 글자에 따라서 글자수 인식
int : 정수

SELECT * FROM user;

DESCRIBE user;

- INSERT 구문

INSERT INTO user (name,uid,locate,email)
VALUES ('Lee',1,'seoul','lee@test.example.com');

SELECT * FROM user;

INSERT INTO user (uid,name,email,locate)
VALUES (2,'KIM','kim@test.example.com','daegu');

SELECT * FROM user;

INSERT INTO user
VALUES ('Lim',3,'kyunggi','lim@test.example.com');

SELECT * FROM user;

INSERT INTO user
VALUES (4,'kyunggi','park@test.example.com','Park');

SELECT * FROM user;

- UPDATE 구문

UPDATE user
SET name='Park'
WHERE uid=0;

- DELETE  구문

DELETE FROM user
WHERE uid=0;

-------------------------------

조심해야 할 사항

UPDATE 나 DELETE에 WHERE 을 지정해주지 않으면 전체 내용으로 반영

UDATE user
SET locate = busan;

SELECT * FROM user;

DELETE FROM user;

SELECT * FROM user;

------------------------------

트랜잭션 설정 확인

SHOW variables LIKE 'auto%';

실시간만 autocommit 설정

SET autocommit = on/off; ( true/false )


SET autocommit = off;
SHOW variables LIKE 'auto%';

INSERT INTO user
VALUES ('Lim',3,'kyunggi','lim@test.example.com');

SELECT * FROM user;

ROLLBACK

SELECT * FROM user;

exit

---------------------------

mysql -u root -p

----------------------------

SHOW variables LIKE 'autocommit';

ON 확인

exit

--------------------------------

영구설정

vi /etc/my.cnf

autocommit = 0 

:wq

systemctl restart mysql

-------------------------------

mysql -u root -p

------------------------------

SHOW variables LIKE 'autocommit';

OFF 확인

======================================================


테이블 수정

ALTER TABLE user ADD age int;

SELECT * FROM user;

DESCRIBE user;

ALTER TABLE user ADD level int(5);

DESCRIBE user;

ALTER TABLE DROP level;

DESCRIBE user;

ALTER TABLE user MODIFY email int;

DESCRIBE user;

SELECT * FROM user;

ALTER TABLE user ADD PRIMARY KEY(uid);

DESCRIBE user;

ALTER TABLE user MODIFY name varchar(10) NOT NULL; 

DESCRIBE user;

ALTER TABLE user MODIFY locate varchar(10) UNIQUE;

에러 발생 : 중복된 값이 있음.


-----------------------------------------------------------------------------------

사용자 설정


USE mysql;

SHOW TABLE;

DESCRIBE user;

SELECT host, user FROM user;

CREATE user user01@localhost IDENTIFIED BY '123';
 -  hostname =>  ex) 192.168.xxx.100 or 127.0.0.1 or '% ' 도 가능

DESCRIBE user;

SELECT Select_priv, user FROM user;

SHOW GRANT FOR user01@localhost

SHOW GRANT FOR root@localhost

GRANT SELECT ON test.user to user01@localhost;

SHOW GRANT FOR user01@localhost


---------------------------------------------------------------------------------------

데이터베이스 백업

백업

# mysqldump -u root -p test > test.dump

# mysql -u root -p

USE test

SHOW TABLES;

DROP TABLE user;

SHOW TABLES;

exit

백업 풀기

# mysql -u root -p test < test.dump

# mysql -u root -p

USE test

SHOW TABLES;