[DB] 22-07-15 TIL

제약 조건

• 무결성을 보장하기 위한 제한된 조건
• PK, FK, UNIQUE, CHECK, DEFAULT 정의, NULL 허용

외래키(FK): 두 테이블 사이의 관계를 통해 무결성을 보장 받을 수 있다.
FK를 따라가면 부모 테이블에서 유일한 값을 얻을 수 있다.
CASCADE: 원본 테이블 PK의 data가 변경된 경우, 연결된 FK의 데이터도 함꼐 변경되거나 삭제되도록 할 수 있다.
UNIQUE: PK와 동일하지만 NULL을 허용한다. 보통 id가 PK라면 휴대폰 번호는 UNIQUE로 지정할 수 있다.
CHECK: 은 프론트에서 데이터 유효성 검사와 같이, DB에 등록하기 전 범위나 조건 등을 제시하여 해당 조건에 부합하는 경우에만 데이터 insert가 가능하도록 할 때 사용할 수 있다.
DEFAULT 정의: NOT NULL에서 데이터를 입력하지 않으면 자동으로 입력되도록 하는 기본 값을 지정한다. 예를 들어 지역을 입력하지 않으면 자동으로 '서울'로 입력되도록 하여 NOT NULL 조건에 부합하도록 한다.
NULL 허용: null을 허용하여 값이 입력되지 않아도 DB에 다른 열 정보가 입력되도록 할 수 있다.

select문

select: 열 이름, 열 이름 as '표기되는 이름', sum(price)
from: 테이블
where: 조건
group by: 여러 개 표기된 것을 하나의 그룹으로 묶어서 표현(주로 sum, max, min, avg 등)
having: 조건(where)을 부여하는 방법으로 집계 함수와 함꼐 사용한다. group by 뒤에 표기한다.
order by: 정렬에 대한 제한 default ASC, DESC를 이용하여 내림차순으로도 표기가 가능하다

서브쿼리

• 쿼리 내에 또 다른 쿼리를 두는 방식. 예를 들어 "이승기와 동일한 지역에 거주하는 사람들의 리스트"를 보고 싶다면
  select name, addr
  -> from userTbl
  -> where addr = (
  -> select addr
  -> from userTbl
  -> where name = '이승기');
  

group by

select userID, SUM(price*amount)
from buyTbl
group by userID;

집계 함수는 where 절에 넣을 수 없다.
집계 함수는 group by 뒤인 having 절에 넣어야 한다.

select userID, sum(price*amount)
from buyTbl
group by userID
having sum(price*amount) > 1000;

Rollup

• 총합 또는 중간 합계가 필요한 경우
  select num, groupName, SUM(price * amount)
  from buyTbl
  group by groupname, num
  with rollup;
  

select groupName, SUM(price * amount)
from buyTbl
group by groupname
with rollup;

실습

인스턴스 생성하고 생성된 인스턴스의 정보를 DB로 전송하기

클라우드 또는 가상화 환경에서 사용하는 인스턴스는 일반 VM과는 달리 이미 OS가 설치되어있는 형태를 이미지화 한 것이다.

클라우드 환경은 on-demand여야 한다.
데이터베이스와 같은 경우 사용자가 직접 운영체제를 설치하고 서비스 애플리케이션을 설치하는 과정이 생략된 상태에서 주문 즉시 해당 서비스를 이용할 수 있어야 한다. -> "완전 관리형 서비스" -> 대신 사용자는 OS, 애플리케이션 자체에 대한 접근 권한은 없다.
클라우드에서 제공하는 serverless 서비스 -> 파이썬 등과 같은 언어를 이용하여 일종의 트리거(함수)를 만들어두는 것
https://dashbird.io/blog/serverless-case-study-coca-cola/
(실제로는 서버가 있지만 해당 서버 자체에 대한 접근은 불가하며 언어를 이용하여 서비스를 호출하는 방법)
[VM WorkStation]

가상머신 설정 변경 -> Virtualize Intel VT-x/EPT 체크

[centos]
df -h
-> 저장 공간 확인

openstack cloud image 검색
-> centos 7
-> x86 64 GenericCloud 2003 qcow2.xz 2020-04-22 12:24 254M

-> cd /cloud
-> wget http://cloud.centos.org/centos/7/images/CentOS-7-x86_64-GenericCloud-2003.qcow2.xz

[리눅스 쉘 익히기]
a="hello"
echo $a
-> hello

echo ${a}all
-> helloall

date
-> Fri Jul 15 11:21:38 KST 2022

date 명령의 결과값을 변수에 담고 싶다면?
a=$(date)
echo $a
-> Fri Jul 15 23:05:35 KST 2022

a=$(date +%y%m%d)
echo $a
-> 220715

직전 실행에 문제가 없는지 표시
echo $?
-> 0

ping www.google.com -c 1
-> 1 packets transmitted, 1 received, 0% packet loss, time 0ms
echo $?
-> 0

ping www.google.c -c 1
-> ping: www.google.c: Name or service not known
echo $?
-> 2

pin www.google.com -c 1
-> bash: pin: command not found...
echo $?
-> 127

결과를 파일로 저장
date > result.txt
cat result.txt
-> Fri Jul 15 11:30:35 KST 2022

pin www.google.com -c 1 > result.txt
-> bash: pin: command not found...
cat result.txt
-> 아무 것도 저장되지 않았음
-> > 는 정상 실행 결과값만 저장하기 떄문
-> 오류 메시지도 저장하고 싶다면?

pin www.google.com -c 1 2> result.txt
cat result.txt
-> bash: pin: command not found...
오류에 대해서만 result.txt로 보내겠다.

[centos]
xz -d CentOS-7-x86_64-GenericCloud-2003.qcow2.xz
ll
-> 용량이 858783744인지 확인

mv CentOS-7-x86_64-GenericCloud-2003.qcow2 CentOS.qcow2
-> 이름 간단하게 CentOS.qcow2로 변경

가상화 환경을 만들기 위한 설정들
1. 하이퍼바이저 설치하기
yum update -y

yum -y install @virt
-> 가상화 관련 도구들을 패키지로 묶어 설치해준다.

yum -y install libguestfs-tools
-> 클라우드용 이미지를 커스터마이징 할 수 있는 도구

&& -> 앞에 작업이 성공하면 이어서 다음 작업 진행
; -> 앞에 작업이 실패해도 이어서 다음 작업 진행

귀찮으니까 세 명령어 한 방에 실행
yum -y update && yum -y install @virt && yum -y install libguestfs-tools

2. 하이퍼바이저 실행을 위한 설정
   setenforce 0
   -> 당장 실행할 것임
   sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
   -> 파일 안에 'SELINUX=enforcing'문자열이 있다면 'SELINUX=disabled'로 변경
   

vi /etc/selinux/config 로 변경된 것 확인할 수 있음

리눅스에 firewalld와 selinux가 있는데, selinux는 웹서비스를 위한 디렉토리를 /var/www/html로 강제함. 다른 경로에서 웹서비스를 이용하고 싶기 떄문에 selinux를 disable한 것임

vi /etc/libvirt/qemu.conf
:set nu
519, 523번 째 줄에서 # 제거

systemctl enable libvirtd && systemctl start libvirtd

yum -y install virt-manager
-> 가상머신 관리 도구 설치

virtual machine manager 실행

3. 클라우드용 이미지의 내용을 요구사항에 맞게 변경시키기
   요구 사항:
   1. 기존 CentOS 이미지를 복사하여 gildong.qcow2를 생성
   2. 해당 이미지에는 루트 패스워드가 없다. 우리는 root/test123으로 지정하자
   3. 기본적으로 웹서비스를 제공할 수 있어야 한다.
      httpd를 이용한다. (httpd 설치 -> OS 부팅 시 자동으로 실행되어야 한다.)
   4. '3'에서 만든 이미지를 이용하여 인스턴스 배포하기

cp CentOS.qcow2 gildong.qcow2

https://libguestfs.org/virt-customize.1.html 참고하기
virt-customize
-a gildong.qcow2 -> 디스크 이미지 선택
--root-password password:test
--install httpd -> 아파치 설치
--run-command 'systemctl enable httpd' -> 부팅 시 아파치 실행
--selinux-relabel -> selinux 기능 실행

ll
-> 용량이 원래 약 800MB였던 것이 약 1GB로 늘어난 것을 확인할 수 있음

cp gildong.qcow2 chulsoo.qcow2

인스턴스 설치

virt-install 
    --name gildong 
    --vcpus 1 
    --ram 1024 
    --disk /cloud/gildong.qcow2 
    --os-type linux
    --os-variant rhel7.0 -> redhat enterprise linux 7 
    --import -> 설치하는 것이 아니라, 실행만 시키는 것임 
    --noautoconsole -> cli로 들어가는 것이 아니라 백그라운드에서 실행 시킬 것임 
    > /dev/null -> 화면에 띄우지 않을 것임 

virsh list --all
-> 동작 중인 인스턴스를 확인

virtual machine manager에서 인스턴스 더블클릭

루트 계정 로그인
ifconfig -> 192.168.122.10

인터넷 연결 확인 -> ping www.google.com -c 3

웹서버 동작 확인 -> systemctl status httpd

[centos]
virsh domifaddr gildong

• dom -> domain
• if -> interface
• addr -> address

virsh domifaddr gildong | grep ipv4

virsh domifaddr gildong | grep ipv4 | gwak '{print $0}'
-> 전체

virsh domifaddr gildong | grep ipv4 | gwak '{print $1}'
-> 첫 번째 출력

virsh domifaddr gildong | grep ipv4 | gwak '{print $4}'
-> 네 번째 출력

virsh domifaddr gildong | grep ipv4 | gawk '{print $4}' | gawk -F/ '{print $1}'
-> /를 기준으로 나누기
-> 192.168.122.10

name="gildong"
-> 변수 선언
virsh domifaddr $name
-> 변수의 값과 같은 인스턴스 출력

yum -y install sysstat

sar 3 10
-> 3초마다 한 번 씩 10번 체크해서 CPU 사용량 평균을 구함

touch instance.sh
chmod +x instance.sh
vim instance.sh

#!/bin/bash

name=''
vcpu=''
ram=''

clear
echo -e "\t\tINSTANCE CREATION PROGRAM"
echo

echo -en "\tINSTANCE NAME : "
read name # 값을 name이라는 변수에 담음 

echo -en "\tHOW MANY CPUs DO YOU WANT : "
read vcpu

echo -en "\tMEMORY SIZE : "
read ram

cp chulsoo.qcow2 ${name}.qcow2 # 입력 받은 이름으로 qcow2 이미지 생성.   chulsoo.qcow2는 템플릿용 기본 이미지 

virt-install --name $name --vcpus $vcpu --ram $ram --disk /cloud/${name}.qcow2  --os-type linux --os-variant rhel7.0 --import --noautoconsole > /dev/null

sleep 15

echo -e "\t\tFINISHED"
result=$(virsh list --all | grep ${name})
echo -e "\t$result"

값을 안 넣은 이유? -> 사용자에게받아서 넣겠다.
-e: 정규표현식을 쓸 수 있음
-n: 줄을 안 내리고 값을 바로 옆에서 받음

./instance.sh 

-> 쉘 실행

인스턴스 2개 만들기
centos1 - cpu1, ram1024
centos2 - cpu1, ram1024

virsh domifaddr centos1
virsh domifaddr centos2

[centos]

mysql sqlDB -u root -p1234 -e "select name, addr from userTbl"

-e: execute. 명령어 처리
한 줄로 sql에 명령어 처리 가능

id=user1
name=gildong
year=1996
addr=NY

데이터베이스로 값 전달하기

mysql sqlDB -u root -p1234 -e "INSERT INTO userTbl VALUES('$id', '$name', $year, '$addr', NULL, NULL, NULL, NULL)"

주의: sql문 안에는 작은 따옴표를 해야됨. 처리할 명령어가 큰 따옴표로 묶여있기 떄문

echo $?
-> 0

[mariaDB]
select * from userTbl;
-> insert한 값 잘 들어갔음

실습 시나리오

DBname: cloudDB

물리 서버에서 확인할 수 있는 정보(hostTbl)

열 이름()
(hostname)hostname 명령을 통해서 서버의 이름 확인 가능 -> PK로 지정: dblab: (VARCHAR(20)
(hostip)ifconfig .. 일부 정보 필터링을 통해 ip 주소 확인 가능 -> NOT NULL: CHAR(15)

인스턴스에서 확인할 수 있는 정보(instanceTbl)

(iname)인스턴스 이름: $name (PK): VARCHAR(20)
(hostname)호스트서버 이름: $HOSTNAME (FK): VARCHAR(20)
(iip)인스턴스 IP 주소: $ipinfo: CHAR(15)
ipinfo = $(virsh domifaddr $name | grep ipv4 | gawk '{print $4}' | gawk -F/ '{print $1}')
(ram)인스턴스의 메모리 사이즈: $ram: INT

HeidiSQL로 데이터베이스(cloudDB), 테이블(hostTbl, instanceTbl) 생성

dbForge로 FK 지정

HeidiSQL로 hostTbl에 hostname, hostip 행 추가

vim instance.sh
다음 코드 추가

# to Database 
sleep 60 

iip = $(virsh domifaddr $name | grep ipv4 | gawk '{print $4}' | gawk -F/ '{print $1}')

mysql cloudDB -u root -p1234 -e "insert into instanceTbl values ('$name', '$HOSTNAME', '$iip', $ram)"

mysql cloudDB ur root -p1234 -e "select * from instanceTbl where iname = '$name'"

실행
.\instance.sh

virtual machine manager에서 결과 확인

조인

두 개 이상의 테이블을 연결하여 두 테이블에서 동시에 필요한 열 정보를 한 번에 출력할 수 있도록 해주는 방법 => join

• inner join
• outer join
• cross join
• self join

select 열 
from 첫 번째 테이블 
    inner join 두 번쨰 테이블 
    on 조인될 조건 
where 검색 조건 

select *
from buyTbl
    inner join userTbl 
    on buyTbl.userID = userTbl.userID
where buyTbl.userID = 'JYP';

select buyTbl.userID, buyTbl.prodName, userTbl.name, userTbl.addr, CONCAT(mobile1, mobile2) AS phone
from buyTbl
    inner join userTbl 
    on buyTbl.userID = userTbl.userID
where buyTbl.userID = 'JYP';

테이블 이름 축약하기
userTbl -> U, buyTbl -> B

select B.userID, B.prodName, U.name, U.addr, CONCAT(mobile1, mobile2) AS phone
from buyTbl B 
    inner join userTbl U 
    on B.userID = U.userID
where B.userID = 'JYP';

Quiz. host, instance 테이블을 inner join하고자 한다.
instance 테이블을 기준으로 host 테이블을 inner join하여 생성한 인스턴스 이름을 조건에 부여하여 전체 정보를 출력해보세요.

select I.iname, I.hostname, I.iip, I.ram 
from hostTbl H
    inner join instanceTbl I
    on H.hostname = I.hostname 
where I.iname = 'cent002';

물건을 산 사람들 출력

select * 
from userTbl U 
    inner join buyTbl B 
    on U.userID = B.userID 
    order by U.userID;

virtual machine 끄기
virsh autostart cent002
-> Domain 'cent002' marked as autostarted

virsh autostart cent003
-> Domain 'cent003' marked as autostarted

force off

init 0

참고: 이것이 MariaDB다, 우재남, 한빛미디어