클라우드 50일차

문자열끼리 +하기 하면 숫자로 합쳐진다;;

 

그래서 함수를 따로 사용해야한다.

 

CONCAT을 사용하면 문자열을 붙일 수 있다.

 

```

select name, CONCAT(mobile1, mobile2) from usertbl where name='이승기';

```

select name, CONCAT(mobile1, mobile2) as phone from usertbl where CONCAT(mobile1,mobile2) is not NULL;

보면 위와같이 NULL이 아닌것만 확인할 수 있다.

 

---

 

Quiz. 사용자별 구매 개수가 평균 구매 개수보다 많은 사람들을 출력하라.

 

일반적으로 조건절은

where height > 185;

where sum(amount) > 평균구매건수

 

조건절은 집계함수 대상이 될 수 없다. 그래서 쓰는게 HAVING이다.

집계함수에 대해서는 HAVING 절을 써야한다.

 

위치로만 따지면 위와 같다.

select userid, SUM(amount) 
from buytbl 
group by userid 
having sum(amount) > (select AVG(amount) from buytbl) 
order by SUM(amount) DESC;

 

Quiz. group by, cout를 이용하여 가수들의 출신 지역과 각 지역별로 몇명의 가수들이 있는지 출력하세요

select addr, count(addr) 
from usertbl 
group by(addr) 
order by count(addr) desc;

Quiz. 가장 큰 키와 작은 키의 사람 찾기

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

Quiz. buytbl에서 총합 1000원 이상 사용한 고객 검색하기

SELECT userid, sum(amount * price) 
FROM buytbl 
GROUP BY userid 
HAVING SUM(amount * price) > 1000 
ORDER BY sum(amount * price) desc;

where는 조건에 집계함수를 사용할 수 없다. 이는 having 절에서 사용되며 위치는 group by 뒤에 위치하기

 

[ ROLLUP ]

[SQL] ROLLUP과 GROUP BY. 집계 통계에 사용되는 롤업 함수 사용법을 알아보자 (tistory.com)

[SQL] ROLLUP과 GROUP BY. 집계 통계에 사용되는 롤업 함수 사용법을 알아보자

반드시 with를 쓸 필요는 없는데 GROUP BY랑 함꼐 와야 사용가능하고 GROUP BY에서 사전에 어떤것으로 묶을지를 알려주어야 사용가능해진다.

 

 

select num, groupname, SUM(price * amount) FROM buytbl GROUP BY groupname, num WITH rollup;

위와같이 각각에 맞추어 계산된게 뭉치고 또 마지막에 총합이 뭉치는 것을 확인할 수 있다.

 

 

select num, groupname, SUM(price * amount) FROM buytbl GROUP BY groupname with rollup;

총 판매금액이 마지막에 나오는 것을 확인할 수 있다.

 

---

 

[ DML, DDL, DCL ]

SQL 문은 크게 DML, DDL, DCL 로 분류한다.

DML(데이터 조작언어) -> SELECT, INSERT, UPDATE, DELETE

DDL(데이터 정의언어) -> CREATE, DROP, ALTER, RENAME

DCL(데이터 제어 언어 = 권한) -> GRANT, REVOKE, DENY

 

[ 데이터의 삽입(INSERT) ]

INSERT는 테이블에 데이터를 삽입하는 명령이며 기본적인 형식은 다음과 같다

3씩 값이 자동으로 증가하게 만들 수도 있다.

 

use sqldb;
INSERT IGNORE INTO buytbl VALUES (
	NULL,
    'kbt',
    'shirt',
    '의류',
    50,
    1);
INSERT IGNORE INTO buytbl VALUES (
	NULL,
    'kbs',
    'shirt',
    '의류',
    50,
    1);

현재 kbt라는 것은 없고 kbs는 있다. 이러면 kbt가 FK로 존재하지 않기 때문에 에러가 발생하지만 위와같이 IGNORE를 붙이면 오류가 발생해도 무시하고 그 다음명령을 실행한다. 그 결과 kbt는 안들어가더라도 kbs는 들어간다.

 

 

 

[ 대량의 샘플 데이터 생성 ]

[ 데이터의 수정(UPDATE) ]

 

위와같이 해서 데이터 수정도 가능해진다.

 

[ 데이터의 삭제(DELETE FROM) ]

delete 도 update 와 비슷하다. delete 는 행 단위로 삭제하는데, 형식은 다음과 같다.

[ 변수 사용하기 ]

SQL도 프로그래밍 기능이 가능하므로 변수 등의 사용 역시 가능

변수를 선언하는 방법은 아래와 같다.

 

SET @var1=5;

대충 위와같은 느낌으로 사용해주면 된다.

 

Quiz. 가수들의 키를 확인하여 180이 넘는 사람들은 아래와 같이 출력되도록 하세요

가수이름 : 홍길동

 

대충위와같은 형태로 덜 예쁜 FORMAT 출력도 가능해진다.

 

---

 

데이터의 형 변환

- 명시적인 변환 (대놓고 바꿔라고 하는거)

- 암묵적인 변환 (알아서 바꾸기)

 

select CAST('2022/03/16' as DATE);

대충 위와같은 느낌이다.

 

select CAST(avg(amount) as signed integer) from buytbl;

암시적변환

 

select name, CONCAT(mobile1, mobile2) as '휴대폰' from usertbl where mobile1 is not null;

select CONCAT(100, '200');

앞에 있는 정수를 문자로 본다.

 

데이터의 출력을 JSON 형태로 출력하기

 

MSA(Micro Service Architecture)

개발 방법론 -> 폭포스모델

기능을 나누어 개발하고 데이터 포맷만 주고 받아서 처리하는 것. 표준화된 데이터 포맷을 사용하면 서로 알아먹을 수 있다. 이 표준 데이터 포맷중 하나가 JSON이다.

 

select json_object('name',name,'height',height) from usertbl where height >= 180;

위와같이 만들면 JSON형태로 객체 형식의 데이터가 반환된다. key-value형태이다.

 

JSON 형태로 데이터가 옮겨다니기 때문에 언어는 크게 상관이 없어졌다. 그냥 언어를 중급이상 다룰 수만 있다면 JSON으로 데이터를 보내고 받을 수 있기 때문이다.

 

[ JOIN ]

지금까지 우리는 하나의 테이블을 다루는 작업 위주로 실습을 진행했다. 하지만 실제 상황이라면 두 개 이상의 테이블이 연계되어 작업이 대부분일 것이다. 이제 두 개 이상의 테이블이 서로 관계되어 있는 경우를 고려해 보자. 조인(Join) 이란 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것을 말한다.

 

RDBMS는 테이블간 관계를 통해 유의미한 결과값을 확인할 수 있다.

테이블을 이용할 경우 불필요한 공간낭비와 중복 데이터를 줄일 수 있으나

"관계"로 인한 단점도 있음.

-> 확장시 많은 비용과 서비스 중단등의 문제가 발생할 수 있다.

 

SCALE OUT으로 DB 확장은 쉽지 않다. 기존 DB를 다른곳으로 옮기어야 하니까

SCALE UP으로 DB를 확장해야한다. 이 방식으로 하면 데이터를 migration해주는 과정에서 데이터 손실과 비용이 청구될 수 있다.

NoSQL -> 자료 보관시 테이블을 생성하고 Key:Value 형태로 자료를 보관한다. 샤드 단위로 데이터가 보관된다. 데이터 확장이 용이하다. (조그만 스토리지 여러개를 연결하여 확장하는 것이 가능하다, I/O에도 유리하다.) 그래서 빅데이터에 이용되는 것이다.

 

데이터베이스의 테이블은 중복과 공간 낭비를 피하고 데이터의 무결성을 위해서 여러 개의 테이블을 분리하여 저장한다. 그리고 이 분리된 테이블들은 서로 관계를 맺고 있다.

 

join (두개 이상의테이블을 연결하기 위한 방법)

= 일반적으로 두 테이블을 join 시킨다라고 얘기하는 경우는 inner join을 의미한다.

 

[ inner join ]

위와같이 PK와 FK로 묶여 있는 관계에서 나타난다.

단, inner join은 있는 정보만 출력된다.

 

구매기록이 없는 정보를 포함하여 모두 다 출력하고 싶다면 -> outer join

그럼 비어있는 정보는 NULL로 표현된다.

 

위와같이 작성하는데 첫 번째 테이블에 두 번째 테이블이 끼어 넣어진다.

ON을 사용하여 공통요소 기준을 잡아준다.

 

select * 
FROM buytbl 
INNER JOIN usertbl ON usertbl.userid=buytbl.userid 
WHERE buytbl.userid='kbs';

위와같이 두 테이블이 붙어서 정보가 출력된다.

 

// column이 명확한 경우
select usertbl.userid,name,addr, CONCAT(mobile1, mobile2), prodname 
FROM buytbl 
INNER JOIN usertbl ON usertbl.userid=buytbl.userid 
WHERE usertbl.userid='kbs';

// column이 명확하지 못한 경우
select usertbl.userid, usertbl.name, usertbl.addr, CONCAT(usertbl.mobile1, usertbl.mobile2), buytbl.prodname 
FROM buytbl 
INNER JOIN usertbl ON usertbl.userid=buytbl.userid 
WHERE usertbl.userid='kbs';

이를 활용해서 위와같이 쿼리를 날리어 테이블을 만들 수 있다.

 

만약 한쪽에만 있는 데이터면 테이블명을 명시하지 않아도 되기는 하지만 가급적 어디에서 데이터를 긁어올지를 알려주는 것이 좋다.

 

다만 inner join으로 처리하면 중복되는 데이터에 대해서만 처리해준다.

 

또 하나 문제점은 테이블을 매번 모두 적어주기 매우 힘들다.

select U.name, U.addr, CONCAT(U.mobile1, U.mobile2) as '휴대폰', B.prodname 
FROM buytbl B 
INNER JOIN usertbl U ON B.userid=U.userid;

그래서 위와같이 테이블 명을 축약해서 사용할 수도 있다.

위의 내용을 참고하여 우리 쇼핑몰에서 구매를 한번이라고 한 경험이 있는 회원들의 이름과 주소를 출력하라. 단, 이름은 한번씩만 출력되어야 한다.

 

select DISTINCT U.name, U.addr 
FROM buytbl B 
INNER JOIN usertbl U 
ON B.userid=U.userid;

결국 inner join은 두 테이블에 모두 존재하는 데이터에 대해서만 출력이 가능하므로 구매 기록이 없는 사용자는 출력되지 않음.

만약 이를 출력하고자 한다면 outer join을 사용해야한다.

 

[ outer join ]

 

use sqldb;
SELECT U.userid, U.name, B.prodname, U.addr
FROM usertbl U -- 기준 테이블, 왼쪽에 위치함
LEFT OUTER JOIN buytbl B
ON U.userid=B.userid;

위와같이 데이터가 없는 곳은 당당하게 NULL이라고 적혀있다.

 

왼쪽테이블에 있는 정보중 오른쪽 구매 테이블에 있는 정보와 매치되지 않은 정보(구매경험 없는 가수들의 정보)도 그냥 출력해라.

 

동일한 정보를 출력하되, 기준 테이블을 usertbl이 아닌 buytbl로 삼아보자.

use sqldb;
SELECT U.userid, U.name, B.prodname, U.addr
FROM buytbl B -- 기준 테이블, 왼쪽에 위치함
RIGHT OUTER JOIN usertbl U
ON U.userid=B.userid;

 

Quiz. 구매기록이 한번도 없는 사용자들의 ID, 이름, 주소를 출력하라.

use sqldb;
SELECT U.userid, U.name, B.prodname, U.addr
FROM buytbl B -- 기준 테이블, 왼쪽에 위치함
RIGHT OUTER JOIN usertbl U
ON U.userid=B.userid
WHERE B.prodname is NULL;

위와같이 테이블이 3개넘게 있는 경우가 있다. 이런 상황에서도 데이터를 긁어와야 한다.

 

이게 미래에 가상머신 위에 docker을 올리는 경우도 생각해볼 수 있다. DB들이 있고 이 안에 설치된 패키지들을 패키지로 따로 관리하는 등의 경우이다.

 

DB를 가지고 위와같이 INNER JOIN을 두번타서 원하는 결과를 얻을 수 있다.

 

 

 

[ CROSS JOIN(상호조인) ]

CROSS JOIN 은 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인 시키는 기능을 한다. 따라서 CROSS JOIN 의 결과 개수는 두 테이블 개수를 곱한 개수가 된다.

 

CROSS 를 하면 두개에서 이어진것으로 모든 경우가 나온다.

 

우와같은 문법으로 사용한다.

 

select COUNT(usertbl.userid) from buytbl cross join usertbl;

 

이를 카티션 곱이라고 부른다. 두 테이블의 데이터를 기준으로 가능한 모든 조합의 데이터가 조회된다.

 

SELF JOIN(자체조인)

SELF 조인을 활용하는 대표적인 예가 조직도와 관련된 테이블이다.

테이블이 위와같이 직원이름(기본키)가 들어가 있고 

 

이부장을 보면 이부장은 직원이므로 직원이름 열에 존재한다. 그러면서 동시에 우대리와 지사원의 상관이어서 상관 이름 열에도 존재한다. 만약, 우대리 상관의 구내번호를 알려면 EMP열과 MANAGER 열을 조인해야 이부장의 구내 번호를 알 수 있다. 아래와 같이 테이블을 작성하고 이를 확인해 보자.

 

그래서 위와같이 열간의 연결을 해준것이 SELF JOIN이다.

CREATE database if not exists company;
use company;
CREATE TABLE member (
	emp CHAR(10) PRIMARY KEY,
    manager CHAR(10),
    emptel CHAR(5)
);

먼저 DB와 TABLE을 만들어주자.

 

대충정보를 넣어주자

 

select A.emp, A.emptel, B.emp, B.emptel
	FROM member A
		INNER JOIN member B
			ON A.manager=B.emp
	WHERE A.emp='박대리';

위와 같은 쿼리를 날리어 주면 결과적으로 직원의 관리자의 이름과 번호를 확인할 수 있다.

 

[ UNION/UNIONALL/NOT IN/IN ]

UNION은 두 쿼리의 결과를 행으로 합치는 것을 의미한다. 일반적인 형식은 아래와 같다.

 

UNION 만 사용하면 중복된 열은 제거되고 데이터가 정렬

UNION ALL을 사용하면 중복된 열까지 모두 출력

NOT IN 은 첫번쨰 쿼리의 결과중에서 두번째 쿼리에 해당하는 것을 제외하기 위한 구문

 

USE mlbdb;
SELECT pname, addr FROM ptable
	UNION
SELECT zname, tname FROM ztable;

SQL 프로그래밍

SQL 에서도 다른 프로그래밍 언어와 비슷한 분기, 흐름제어 반복의 기능이 있다. 이러한 기능을 변 수와 함께 잘 사용한다면 유용한 SQL 프로그래밍이 가능하다. SQL 프로그래밍 전에 우선 스토어드 프로시저를 다시한번 기억해 보자.

 

CALL을 하게되면 프로시저라는 함수를 실행하듯이 실행된다.

 

- DELIMITER // ~ END // 는 스토어드 프로시저의 코딩 부분을 묶는 역할이다. CREATE PRO.. 안에서도 세미콜론이 종료문자이므로 어디까지가 스토어드 프로시저인지 구분하기 어렵다. 따라서 END // 까지를 인식하도록 한다.

- DELIMITER ; 는 “ ; ” 를 원래의 종료문자로 바꾸기 위한 것이고

- CALL 스토어드프로시저이름(); 는 앞서 생성한 스토어드 프로시저를 호출하는 기능이다. 쉽게 생각 하면 “위에서 PROCEDURE 로 함수를 만들고 CALL 로 함수를 불러온다.” 고 생각하면 된다.

 

IF ··· ELSE

조건에 따라 분기한다. 한 문장 이상이 처리되어야 할 때에는 BEGIN... END 와 함께 묶어주어야 한다. 그냥 마음편히 한 문장이라고 하더라도 BEGIN... END 로 묶어준다고 생각하자. 일반적인 형 식은 아래와 같다.

 

 

이번에는 employees DB 의 employees 테이블을 사용하여 직원번호에 10001 번에 해당하는 직원의 입사일이 5년이 넘었는지를 살펴보자. 먼저 ‘10001’ 에 해당하는 직원이 누구인가를 확인해 보았다.

DATEDIFF를 사용해서 날짜의 차이를 계산한다는 의미이다. 그러면 몇일 차이인지 days에 넣을 수 있다.

 

Quiz. 이승기의 태어난 해를 확인하고 짝수년도에 태어났다면

이름     생년

이승기  1990  올해 검강검진 대상자 입니다.

 

이름     생년

이승기  1991  내년 검강검진 대상자 입니다.

DROP PROCEDURE IF EXISTS ifProc2;
DELIMITER //
CREATE PROCEDURE ifProc2()
BEGIN
	DECLARE birth INT;
    SELECT birthyear INTO birth
		FROM usertbl
        WHERE name='이승기';
    
    IF birth% 2 = 0 THEN
		SELECT CONCAT(birth,'는 짝수년도 이기 때문에 건강검진을 받으셔야 합니다.');
    ELSE
		SELECT CONCAT(birth,'는 짝수년도가 아니기 때문에 건강검진을 받지 않아도 됩니다.');
    END IF;
END//
DELIMITER ;

CALL ifProc2();

 

CASE

다른 프로그래밍에서와 동일하게 CASE는 ‘다중분기’ 의 기능을 수행한다. 아래의 예는 CASE를 활 용하여 만든 학점계산 프로그램이다.