클라우드 49일차

모델링,

백업

입력된 데이터의 조회, 활용 -> select

 

mariadb 계정생성 + 패스워드 + 권한부여

처음에는  root 패스워드가 없는 상태

mysql -u root -p

그냥 엔터 -> 로그인 이후 즉시 진입가능

 

grant all privileges on testdb.* to root@'localhost' identified by 'test111'

grant all privileges on testdb.* to root@'%' identified by 'test111'

( * 두개의 root는 각각 다른 root이다. )

- grant : 권한 부여

- all privileges : 모든 권한

- on testdb.* : 권한 부여 db

 

mysql 8.X -> 생성하면 전체 관리자 root에 대하여 임시 패스워드를 발급한다.

/var/log/mysqld.log 파일에 기록되어 있다. (systemctl start mysqld)

 

패스워드 변경 :

(데이터에 대한 변경, 삭제 -> update, delete)

 

[ 테이블, DB, 패스워드 ]

- 처음 mysql을 설치하면 로컬접속용 root가 있으므로 원격 접속용 root는 계정을 별도로 생성해야 한다.

alter user 'root'@'localhost' identified by 'TEst!234';

 

create user 'root'@'%' identified by 'TEst!2345';

alter user 'root'@'%' identified by 'TEst!234';

 

[ 모든 db에 대한 권한부여 ]

grant all privileges on *.* to root@'%';

 

flush privileges;

 

데이터베이스 안에 데이터를 담을 수 있는 공간을 Table이라고 부른다.

데이터는 user Table안에 행단위로 데이터가 들어오게 된다. 그리고 각각의 데이터를 저장하게 된다.

데이터는 무결성이 보장되어야 한다.

 

????

 

---

 

```

DROP DATABASE IF EXISTS testdb;

CREATE DATABASE testdb;

```

 

CREATE TABLE usertbl ( 
userid CHAR(8) PRIMARY KEY, 
name  CHAR(10), 
birthyear INT NOT NULL DEFAULT -1, 
addr CHAR(3) NOT NULL DEFAULT '서울', 
mobile1 CHAR(3),  
mobile2 CHAR(8), 
mdate DATE);

NOT NULL을 안넣어놓은 이상 디폴트는 NULL 로 들어간다.

 

만약 userid를 PK로 선언하지 않았다면? 

ALTER TABLE usertbl

  ADD PRIMARY KEY (userid);

 

기본키 삭제

ALTER TABLE usertbl

  DROP PRIMARY KEY; 

 

데이터 삽입

```

INSERT INTO usertbl () VALUES ();

INSERT INTO usertbl VALUES('user1','홍길동',1999,null,'010','11111111','2022-03-15');

```

null 이라고 했을때 에러가 나는 것을 확인할 수 있다. 이걸 해결하기 위해 작은 따옴표를 사용했는데... 이상하게 데이터가 들어간다. ?왜지

 

데이터가 이미 입력되어 있는 상태에서 추가적으로 열을 추가해 본다. 대신 기본값을 설정하고 추가하기.

 

DB에 추가 칼럼을 넣어보자

```

ALTER TABLE usertbl ADD homepage VARCHAR(40) DEFAULT 'http://example.com' NOT NULL;

```

 

workbench에서 위와같이 데이터를 넣으면 빈곳에 데이터가 잘들어간다.....

 

---

 

테이블 삭제하기

DROP TABLE usertbl;

 

특정 열(컬럼)만 삭제하고 싶다면?

alter table usertbl drop column homepage;

위와같이 homepage 컬럼이 없어진것을 확인할 수 있다.

 

sns열을 추가하되, addr 다음에 위치하도록 설정하기

alter table usertbl add sns char(30) after addr; -- 이게 적용된 상황임
-- alter table usertbl add sns char(30) befor addr;

열의 이름을 변경(change)하고 싶다면??

 

alter table usertbl change column addr address CHAR(10) NOT NULL DEFAULT '미선택';

 

데이터변경하기

update usertbl set address='용인시' where userid='user2';

데이터 삭제하기 (delete)

delete from usertbl where userid='user1';

---

 

backup 폴더를 만들어서 데이터를 백업해보자

 

testdb를 위와같이 싹다 백업한다고 해주자

 

파일이 만들어진것을 확인할 수 있다.

 

 

백업된 데이터를 이용하여 복구해보자

 

데이터가 복구되어 있다.

 

뷰

뷰란 가상의 테이블을 의미. 뷰는 실제 행 데이터를 가지고 있지 않음. 뷰를 SELECT 하면 진짜 테이블의 데이터를 조회하는 것과 동일한 결과를 가지고 온다.

 

위와같은 데이터가 나오는 것을 확인할 수 있는데 우리는 userid, name ,address만 보기를 원하지만 매번  select를 써서 데이터를 불러오는 것이 쉽지는 않다. 그래서 이를 해결하기 위해 뷰를 미리 지정해놓는것이다.

또는 다른 개발자가 보면 안되는 민감한 정보가 있을 수도 있다. 그러면 우리는 이 개발자에게 userid, name, address만 볼 수 있게끔 가상의 테이블을 만들어 주어야 한다. 예시로 v_usertable을 만들고 이 테이블에는 userid, name, address만 들어가게 한다음 기능도 select만 넣어준다. 개발자가 이 테이블을 사용할 수 있게 만들어주어서 사용에 제한을 가한다.

 

CREATE VIEW v_usertbl AS SELECT userid, name, address from usertbl;

Quiz

새로만들어진 v_usertable을 dev1 계정이 select 만 할 수 있도록 설정하세요.

dev1 계정으로 로그인 한 다음 데이터를 삭제하려고 하면 오류가 발생해야 합니다!

 

create user 'dev1'@'%' identified by 'TEst!234';

grant select on testdb.v_usertbl to dev1@'%';

 

으로 만든다음 db에 dev1으로 접속해보았다.

 

놀랍게도 볼 수 있는 DB나 table도 제한되고 데이터 조회도 우리가 지정한 영역에서만 볼 수 있게 되어있다.

 

---

 

stored procedure (스토어드 프로시져)

MySQL에서 제공하는 프로그래밍 기능 -> 변수선언, 함수 작성 등의 여러 코드가 일괄적으로 처리되어야 하는 상황이 많다. 일반적으로 sql은 입력 -> 출력 형태의 interactive 한 동작이 기본이다.

 

스토어드 프로시져는 프로그래밍을 위하여 여러 코드를 한꺼번에 실행시킬 수 있는 기능을 제공한다. -> 내장함수가 아니라 사용자가 직접 만들어서 사용하는 사용자 함수를 만드는 것으로 생각하자.

 

일반적인 종료 -> ;

이걸 바꾸어보자

 

DELIMITER //

CREATE PROCEDURE test()

BEGIN

  SELECT .. 첫번째 명령;

  CREATE .. 두번째 명령;

END

// DELIMITER

CALL test()

 

 

use testdb;
DROP PROCEDURE IF EXISTS test;
DELIMITER //
CREATE PROCEDURE test()
BEGIN
	SELECT userid from usertbl;
    SELECT name from usertbl;
END //
DELIMITER ;

CALL test(); -- test() 호출하기

트리거(방아쇠)

특정 테이블의 특정 행(모든 행)을 백그라우드에서 모니터링하고 해당 행에서 특별한 이벤트가 발생하면 (delete, update, insert ...) 지정된 동작이 실행되도록 하는 기능.

-> aws 에서 사용하는 람다함수(cloud function@gcp)가 이러한 트리거와 같은 역활을 수행한다.

 

???

 

usertbl에서 회원이 탈퇴하면 행이 삭제된다. 그런데 만약 삭제된 회원의 정보를 별도의 테이블에서 관리하고자 한다면?

캐시에 데이터를 남겨두었다가 나중에 copy 해서 사용하면 된다.

 

이번에는 userid, name, birthyear, address만 남기고 나머지는 없애는 트리거를 만들어보자

 

안타깝게도 mysql에는 create or replace procedure이라는 문법은 없나보다 ㅠ

 

use testdb;
create table deluser (
   userid char(8),
    name char(10),
    year int,
    addr char(10),
    deldate date
    );
delimiter //
CREATE TRIGGER trg_deluser
   AFTER DELETE
    ON usertbl
   FOR EACH ROW
BEGIN
   INSERT INTO deluser
      VALUES (OLD.userid, OLD.name, OLD.birthyear, OLD.address, CURDATE());
END //
DELIMITER ;

트리거 실습

 

데이터를 삭제하고나니 정상적으로 데이터가 deluser 테이블에 들어온것을 확인할 수 있다.

 

[ 모델링 ]

데이터베이스 모델링을 하는 이유는 같은 형식의 데이터를 묶어서 중복되는 데이터를 최소화하는 것에 있다. 데이터 무결성을 달성하기 위해서 PK도 따로 지정해놓아 중복되는 데이터를 최소화해보자

 

위와같이 만든상황에서도 구매테이블의 구분이 안된상태이다. 두 테이블간의 관계를 형성해주어야 한다.

 

이 테이블 간에도 부모와 자식 테이블을 결정해주어야 한다. 이렇게 만들면 강한 부모/자식 관계를 가지게 된다. 그래서 이 둘간에는 직선으로 표시가 된다. (부모의 PK가 자식의 FK로 사용)

그리고 한 부모가 여러개의 자식과 관계가 생기면 다리 세계로 표현한다.

 

하지만 그렇지 못한 경우에는 점선으로 표시한다.

 

예를들어 위와같이 부모가 여러개의 물건을 구매하는 경우 표시할 수 있다.

 

반드시 고객 테이블이 있어야 주문테이블이 만들어지기 때문에 강한 관계 -> 나눌수가 없다.

사원은 무조건 부서에 속하지 않을 수 있어서 점선, 약한 관계 -> 나누어놓고 따로따로 사용할 수 있다.

 

마지막 연락처는 없을 수 있기 때문에 NULL값을 허용해주자.

 

구매테이블은 고객이름을 FK로 고객테이블로부터 받아오자.

 

보면 File > New Modal을 선택해서 새 db를 만들자

 

이름을 바꾸고 Add Diagram을 누르자

 

 

관계를 만들때 스포이드를 사용해야한다. 그리고 자식의 userid를 먼저 선택한다음 부모의 userid를 클릭해주어야 한다.

 

그러면 위와같이 관계가 만들어진다.

 

 

 

보면 쿼리가 나오는데 CONSTRAINT 라고 하면서 userid를 FOREIGN KEY를 사용한다.

REFERENCES는 sampledb.user의 userid를 참조해서 내 FK를 지정한다고 적혀있기 때문에 반드시 user table에 userid가 있어야 한다.

 

하다가 주테이블(기준테이블)의 데이터 내용에 변경이 있을 경우 보조 테이블의 내용을 변경할 것인가?

```

ON DELETE CASCADE

ON UPDATE CASCADE

```

별도로 지정하지 않으면 (ON DELECTE NO ACTION 과 ON UPDATE NO ACTION이 기본값)

 

WorkBench 왼쪽 위를 보면 3번째 플롯피 디스크가 있다 이걸 눌러스 mymodel이라고 저장해주자

 

로컬 DB에 만들어진 것을 확인할 수 있다.

 

user 테이블에 데이터를 넣어보자

 

ordernumber 빼고 데이터를 넣어보자

user2로 데이터를 넣어보자

에러가 뜬다. 이는 user에 user2라는 사용자가 없기 때문이다.

 

Quiz.

다음을 만족하는 간단한 모델링을 통한 DB/테이블 생성을 진행해라

 

host 테이블

hosename(PK), CPU, MEMORY, IP

 

instance 테이블

id(auto_increment) [PK], instance name, flavor (m1.small), instance ip, hostname[FK] 사용할 열 지정하여  모델링하기

 

kvmdb를 만들었다. hostname을 FK로 사용한다.

 

SQL 기본

- select 문

SELECT문은 가장많이 사용되는 구문이다. 이는 데이터베이스 내의 테이블에서 원하는 정보를 추출 하는 명령이다. SELECT 문은 다양한 옵션으로 인해서 전체 구문 형식은 복잡해 보이지만, 실제로 많이 사용되는 형태로는 아래와 같다. 대괄호는 생략 가능하다.

순서는 굉장히 중요하다.

대충 위와같이 많이 쓴다.

대충 위와같이 사용한다.

 

- use 문

SELECT 문을 사용하려면 먼저 사용할 데이터베이스를 지정해야 한다. 만약 employees를 사용하기 위해서는 쿼리 창에서 다음과 같이 입력한다. FROM 다음에는 table/view 등의 항목이다.

 

대충 위와같은 테이블이 존재한다.

 

- SELECT + WHERE

WHERE 절은 조회하는 결과에 특정한 조건을 부여하여 원하는 데이터만 보고 싶을 때 사용하는데, 다음과 같은 형식을 취하게 된다

where은 특히나 중요하다. 

 

그냥 복붙해서 사용하자

drop database if exists sqldb;
create database sqldb;
use sqldb;
create table usertbl
( userid char(8) not null primary key,
name varchar(10) not null,
birthyear int not null,
addr char(2) not null,
mobile1 char(3),
mobile2 char(8),
height smallint,
mdate date );

create table buytbl (
num int auto_increment not null primary key,
userid char(8) not null,
prodname char(6) not null,
groupname char(4),
price int not null,
amount smallint not null,
foreign key (userid) references usertbl(userid)
);


use sqldb;
insert into usertbl values ('lsg','이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
insert into usertbl values ('kbs','김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
insert into usertbl values ('kkh','김경호', 1971, '전남', '011', '3333333', 177, '2007-7-7');
insert into usertbl values ('jyp','조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
insert into usertbl values ('ssk','성시경', 1979, '서울', null, null, 186, '2013-12-12');
insert into usertbl values ('ljb','임재범', 1963, '서울', '011', '6666666', 182, '2009-9-9');
insert into usertbl values ('yjs','윤종신', 1969, '경남', null, null, 170, '2005-5-5');
insert into usertbl values ('ejw','은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
insert into usertbl values ('jkw','조관우', 1965, '경기', '011', '9999999', 172, '2010-10-10');
insert into usertbl values ('bbk','바비킴', 1973, '서울', '011', '0000000', 176, '2013-5-5');

insert into buytbl values (null, 'kbs', '운동화', null, 30, 2);
insert into buytbl values (null, 'kbs', '노트북', '전자', 1000, 1);
insert into buytbl values (null, 'jyp', '모니터', '전자', 200, 1);
insert into buytbl values (null, 'bbk', '모니터', '전자', 200, 5);
insert into buytbl values (null, 'kbs', '청바지', '의류', 50, 3);
insert into buytbl values (null, 'bbk', '메모리', '전자', 80, 10);
insert into buytbl values (null, 'ssk', '책', '서적', 15, 5);
insert into buytbl values (null, 'ejw', '책', '서적', 15, 2);
insert into buytbl values (null, 'ejw', '청바지', '의류', 50, 1);
insert into buytbl values (null, 'bbk', '운동화', null, 30, 2);
insert into buytbl values (null, 'ejw', '책', '서적', 15, 1);
insert into buytbl values (null, 'bbk', '운동화', null, 30, 2);

데이터가 잘 들어갔다.

 

db 백업해놓자

 

기본적인 where 절

1. 1970년 이후 출생이고(1970 보다 숫자가 큰 사람 -> 나이가 어린사람)

신장이 182이상인 사람

 

select name,birthyear as 생일,height from usertbl where birthyear>=1970 and height>=182;

select name,birthyear as 생일,height from usertbl where height between 170 and 180;

으로 특정 지역을 검색할수 있지만 매번 or로 이어붙일 수 없다. 그래서 쓰는게 IN이다.

select name,birthyear as 생일,addr as 지역 from usertbl where addr IN('경남','경북','서울');

사람의 이름 출력하기

 

wildcard를 사용할수도 있다.

select name, height, addr from usertbl where name Like '김%';

select name, height, addr from usertbl where name Like '_범수';

 

 

[ 서브쿼리(SubQuery) ]

서브쿼리란 쿼리내에 또 다른 쿼리를 두는 형태를 말한다. 만약, 류현진 보다 키가 큰 사람의 이름과, 키를 출력하고 싶다면 어떻게 해야할까? 첫 번째 방법은 류현진의 키를 먼저 검색한다.

 

Quiz. 이승기보다 키가 큰 사람?

1. 이승기의 키를 구한다.

2. 구해진 이승기의 키보다 큰 사람을 출력한다.

 

이를 위해서는 sub query를 사용해야 한다.

select name, height, addr 
from usertbl 
where height > (select height from usertbl where name='이승기');

Quiz. 이승기가 살고 있는 지역출신인 사람들은?

select name, height, addr 
from usertbl 
where addr = (select addr from usertbl where name='이승기');

 

만약에 서브 쿼리의 결과가 2행 이상이면 에러가 난다. 그래서 두개모두 쓰라는 명령을 넣을 수 있다.

 

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

안타깝게도 위와같이 결과가 173 이상이면서 170이상인 이상한 결과가 나온다.

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

 

ALL을 쓰면 두개의 조건을 모두 만족하는 데이터를 불러오기 때문에 173 이상의 키를 가진 사람들이 나오게 된다.

 

보는데 너무 드럽다. 그래서 정렬을 해줄 필요가 있다.

 

ORDER BY를 사용하면된다.

 

[ ORDER BY ]

ORDER BY는 출력순서를 조절할 수 있는 구문이다. 즉, 오름차순(ASCENDING - ASC) 또는 내림차순 (DESCENDING - DESC)을 적용시켜 출력할 수 있는 것이다. 기본은 오름차순(asc)으로 정렬된다.

결과 위와같이 height 순에 맞추어서 출력해준다.

 

그런데 정렬을 두번두고 싶으면 어떻게 할까? 그때는 order by에 조건을 또 붙여주면 된다.

select name, height, addr 
from usertbl 
where height >= ALL (select height from usertbl where addr='경남') order by height desc, name;

위와같이 height로 먼저 정렬하고 name으로 후 정렬해주는 결과를 볼 수 있다.

 

[ limit ]

Quiz.

모든 가수들의 키 순서대로 정렬하라. 단, 키가 큰 사람 부터 출력하라. 3명만 출력

select * from usertbl order by height desc limit 3;

select * from usertbl order by height desc limit 3,3;

3번째 이하 3명 뽑아라

 

[ DISTINCT ]

select DISTINCT addr  from usertbl order by addr;

중복을 없앨 수 있다.

 

create table ... select 를 이용하여 다른 테이블의 정보를 복사한 새로운 테이블 만들기.

 create table singertable (select name, height, addr from usertbl);

위와같이 필요한 열만 긁어올 수 있다.

 

[ GROUP BY ]

ROUP BY, HAVING 절에 대해 알아보자. GROUP BY 는 그룹으로 묶는 역할이다. 이를 위하여 다음과 같은 데이터베이스와 테이블을 간단히 생성해 보자.

 

위와 같이 order by를 사용하게 되면 각 사용자 별로 구매한 숫자를 확인할 수 있게 된다. 하지만 이를 일일이 더해야 하는 수고가 뒤따르게 된다. 이런 경우에는 집계함수(aggregate function)를 사용할 수 있다. 집계함수는 주로 group by 절과 함께 쓰이며 데이터를 그룹화 해주는 기능을 한 다. 위의 경우 각 id 별 구매 개수를 SUM( ) 과 GROUP BY 절을 이용하면 된다. 즉 GROUP BY 로 먼저 묶은 뒤에 이를 더하는 것이다.

 

select userid, SUM(amount) from buytbl GROUP BY userid;

Quiz. 아래와 같이 출력되도록 하라. (구매건수가 가장 많은 top3 뽑기)

select userid, SUM(amount) as sm from buytbl GROUP BY userid order by sm desc LIMIT 3;

결과 위와같이 구매건수가 가장 많은 top3를 확인할 수 있다.

 

Quiz. 아래와 같이 구매금액이 가장 많은 top3를 구할 수 있다.

select userid, SUM(amount * price) as sm from buytbl GROUP BY userid order by sm desc LIMIT 3;

 

집계함수들

AVG() : 평균

MIN() : 최소값

MAX() : 최대값

COUNT() : 행의 개수세기

COUNT(DISTINCT) : 행으 개수(중복은 1개만 인정)

 

 

Quiz. 사용자별 평균 구매 개수

select userid, AVG(amount) as '평균 구매 개수' from buytbl GROUP BY userid order by AVG(amount) desc;

Quiz. usertbl 에서 키가 가장 큰 사람의 이름과 키, 가장 작은 사람의 이름과 키를 출력하세요

select name, height 
from usertbl 
where height IN ((select max(height) from usertbl),(select min(height) from usertbl)) 
order by height desc;