2022.01.27 Day-9 멀티캠퍼스 (Mysql)

mysql은 볼때 마다 느끼지만

보고 이해하는것 자체는 크게 문제가 없다.

 

근데 그 이해되는 느낌이 뭐랄까.. 힘겹게 부여잡아서 이해되는 느낌이랄까..

전혀 유기적인 이해가 아니라서 활용이 좀 어렵달까..

암튼 그러하다.

 

그리고 아마 내일 오전이면 mysql은 마무리가 될것같다.

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

-- 사용할 데이터 베이스를 선택해요

USE sqldb;

SELECT *
FROM userTBL

-- SELECT를 사용할 때 조건을 이용하고 싶으면.. -> WHERE
SELECT *
FROM userTBL
WHERE userName = '김경호';


-- 그러면 조건이 여러개 있으면 어떻게 해야 하나요?
-- 1970년 이후에 출생하고 키가 182이상인 사람의 아이디와 이름을 조회하세요
SELECT userID, userName
FROM userTBL
WHERE birthYear >=1970 and userHeight>=182;

-- 1970년 이후에 출생하거나 키가 182 이사인 사람의 아이디와 이름을 조회하세요

SELECT userID, userName
FROM userTBL
WHERE birthYear >=1970 or userHeight>=182;


-- 키가 180~183인 사람을 조회하세요!

SELECT userID, userName
FROM userTBL
WHERE userHeight BETWEEN 180 AND 183; -- BETWEEN은 이상,이하다. 초과, 미만이 아니다.


-- 주소가 경남, 전남, 경북 인 사람의 아이디와 이름을 출력하세요.
SELECT userID, userName
FROM userTBL
WHERE userAddr = '경남' or userAddr = '전남' or userAddr = '경북'

SELECT userID, userName
FROM userTBL
WHERE userAddr in ('경남','전남','경북');


-- 많이 사용되는 기능 중 하나가.. 패턴매칭
-- 성이 "김"씨인 사람들의 이름과 키를 조회하세요
-- 와일드카드 문자를 이용해야 해요. 이 문자를 이용해서 패턴을 완성해야 해요
-- 와일드카드 문자 -> %,_
-- % : 0개 이상의 글자를 의미,
-- _: 1개의 글자를 의미
-- 예를 들어보면 '_0%' 이렇게 패턴을 와일드카드를 이용해서 정했어요
-- '김용' -> ok
-- '홍용하하하하하하' -> ok
-- '사용해 주셔서 감사합니다.' -> ok
-- '김%' -> 성이 김인 사람이라는 의미의 패턴


SELECT userName, userHeight
FROM userTBL
WHERE userName LIKE '김%';

-- 김경호보다 키가 크거나 같은 사람의 이름과 키를 알아보세요
-- 1. 김경호의 키를 먼저 알아내야 한다.
SELECT userHeight
FROM userTBL
WHERE userName ="김경호"; -- 177

-- 2. 이렇게 알아낸 키를 이용해서 다른 사람의 이름과 키를 조회해요

SELECT userName, userHeight
FROM userTBL
WHERE userHeight >= 177;

-- 3. 위의 것을 한번에 이용하는 SubQuery

SELECT userName, userHeight
FROM userTBL
WHERE userHeight >= (
SELECT userHeight
FROM userTBL
WHERE userName ="김경호"
);

-- 주소가 '경남'인 사람들의 키보다 키가 크거나 같은 사람을 조회해보세요

SELECT userName, userHeight, userAddr
FROM userTBL
WHERE userHeight >= ANY (
SELECT userHeight
FROM userTbL
WHERE userAddr = '경남'
);

-- ANY() : 여러개 중의 하나
-- ALL() : 모두

-- 정렬을 해 보아요
-- 회원가입일이 빠른 순으로 모든 회원의 이름, 아이디, 가입일을 출력하세요
-- 작은 값이 앞에 혹은 위에 위치하는게 "오름차순", 큰 값이 앞에 혹은 위에 위치하는게 "내림차순"
-- 정렬의 기본은 오름차순 정렬
-- 명시적으로 오름차순, 내림차순을 표현하려면.. ASC(오름차순),DESC(내림차순)

SELECT userName, userID, userDate
FROM userTBL
ORDER BY userDate ASC;

-- 모든 회원의 이름과 키를 출력하세요. 단, 키에 대한 내림 차순으로 출력하세요.
SELECT userName, userHeight
FROM userTBL
ORDER BY userHeight DESC;

-- 돌률이 있어요.. 동률이 존재할 경우 동률에 대해서만 2차 정렬을 할 수 있어요.
SELECT userName, userHeight
FROM userTBL
WHERE userHeight >= 170
ORDER BY userHeight DESC, userName ASC;

-- 회원의 주소를 출력해주세요.
SELECT userAddr
FROM userTBL;

-- 중복된 값을 배제하려면 어떻게 해야 하나요?

SELECT DISTINCT userAddr
FROM userTBL;

-- SELECT에서 가져온 결과 레코드 집합(Result Set, Result Grid) 중 일부분만
-- 가져오려면 -> LIMIT

SELECT *
FROM userTBL
LIMIT 3; -- 상위 3개

SELECT *
FROM userTBL
LIMIT 2,5; -- LIMIT OFFSET, 가져올 개수

-- 테이블을 복사해 보아요
-- 하나의 테이블의 컬럼명, 데이터타입, 들어가 있는 데이터를 복사해서 새로운 테이블을 생성

CREATE TABLE tmpTBL (
SELECT * FROM userTBL

);

select * from tmptbl;

DESC tmpTBL; -- 복사한 테이블의 명세를 살펴보아요.
-- 키(PK, FK는 복사되지 않아요)
            
            
            
-- 여기까지는 별로 어렵지 않다.

-- 이제 Grouping에 대해서 알아보아요
-- buyTBL에서 사용자가 구매한 물품의 갯수륾출력 (사용자 ID와 구매수량을 출력)

SELECT *
FROM buyTBL;

SELECT userID, buyamount
FROM buyTBL
order by USERID;

-- 해당 값을 출력하고, 해당 값을 각각의 그룹화 하고 각 그룹의 합친값을 출력해 주세요.
select USERID, sum(buyamount)
from buyTBL
group by userid
order by userid;

-- 그루핑 갯수가 몇개냐
select USERID, count(buyamount)
from buyTBL
group by userid
order by userid;

-- 평균
select USERID, avg(buyamount)
from buyTBL
group by userid
order by userid;

-- 각 사용자 별 구매액의 총합을 출력하세요 (구매액 = 단가 * 수량)
-- 1. 이건 내가 풀어본 버전
select userid, sum(productprice)*sum(buyamount)
from buytbl
group by userid
order by userid;

-- 2. 이건 강사님이 알려주신 버전
select userid as "id" , sum(productprice*buyamount) as 'amout'
from buytbl
group by userid
order by userid;

-- 1버전과 2버전의 결과값이 형태는 비슷하나, 디테일은 상당히 다르다.

-- GROUB BY

-- *********이건 작성의 순서다. 생략이 될 수는 있느나, 순서가 뒤바뀌어서는 안된다.************
-- SELECT -- 테이블 안의 내가 원하는 데이터를 어떤것을 끌고 올 것인지
-- FROM  -- 어디서 데이터를 끌고 올건지 (데이터가 담겨있는 틀)
-- WHERE
-- GROUP BY
-- HAVING
-- ORDER BY

-- 집계함수의 종류
-- SUM(), AVG(), MAX(), MIN(), COUNT(), STDEV(), etc

-- 각 사용자 별로 한번 구매시 물건을 평균 몇개 구매했는지 조회하세요
select userid, avg(buyamount)
from buytbl
group by userid
order by userid;

-- usertbl에서 가장 큰 키와 가장 작은 키의 회원의 이름과 키를 출력하세요
-- 내가 한 버전
select userid, max(userheight), min(userheight) -- username의 값은 여러개이지만 max와 min의 값은 한개다. 그래서 결과가 이상하다.
from usertbl
order by userid;
-- 무언가의 조건으로 선정된 1명만 나왔다.

-- 강사님 버전
select username, max(userheight), min(userheight) -- username의 값은 여러개이지만 max와 min의 값은 한개다. 그래서 결과가 이상하다. 
from usertbl;

select username, max(userheight), min(userheight) 
from usertbl
group by username; -- 각사람의 그룹별로 묶기 때문에 사실상 무의미하다 중복 이름만 없어질 뿐이다.

-- 문제가 어려우면 subquery를 이용하는 쪽으로 생각을 해봐라
select username, userheight
from usertbl
where userheight = (
select max(userheight) from usertbl
    ) or userheight = (
    select min(userheight) from usertbl
    );
    
-- 사용자 별 구매 금액이 1000이상인 사용자의 아이디와 총 구매 금액을 출력하세요
select userid, sum(productprice*buyamount)
from buytbl
group by userid
having sum(productprice*buyamount)>=1000;

-- 그룹에 대한 조건은 WHERE를 사용하지 않고 HAVING을 이용해야 해요
-- HAVING절에는 GROUP 함수(집계함수)를 사용할 수 있어요

SQL - DML -> SELECT, INSERT, UPDATE, DELETE -> ***** TRANSACTION 적용 가능 ******
- DDL -> CREATE, DROP, ALTER -> *****TRANSACTION 적용 불가*****
    - DCL -> GRANT 
    
    
    
A -> B 1000이체
1. A의 계좌에 1000이 있는지 잔액 확인 -> SELECT
2. B의 계좌가 존재하는지 확인 -> SELECT
3. A의 계좌를 1000원 뺀 금액으로 설정 -> UPDATE
4. B의 계좌를 1000원 더한 값으로 설정 -> UPDATE
TRANSACTION 으로 설정

"왜 설정하나요?" -> 만약 TRANSACTION을 설정하면 DBMS가 4가지 특성(기능)을 제공 (ACID)
ATOMICTY (원자성 ) : ALL OR NOTHING
CONSISTENCY (일치성) : 데이터의 일관성을 유지
ISOLATION ( 독립성) : 동시성 때문에 발생하는 문제를 해결
DURABILITY (내구성)  : 데이터가 안전하게 저장 되는 것을 보장

SELECT *
FROM USERTBL;

start transaction;  -- transaction 시작

select *
from buytbl;

delete from buytbl;

select *
from buytbl;


-- transaction을 끝내려면 commit, rollback을 이용하면 되요
-- commit은 현재 작업한 transaction을 실제 데이터베이스에 적용
-- ROLLBACK은 현재 작업한 TRANSACTION을 취소
ROLLBACK; -- TRANSACTION이 종료

-- 나머지 DML에 대해서 알아보아요
-- INSERT, DELETE, UPDATE

-- INSERT (테이블에 데이터를 삽입)

USE sqldb;

testtblCREATE TABLE testTBL (
ID INT,
    userName  VARCHAR(10),
    userAge INT
);


-- 가장 기본적인 INSERT (모든 컬럼에 데이터를 다 넣을 경우)
INSERT INTO testTBL VALUES(1, '아이유', 20);

SELECT * FROM TESTTBL;

-- 필요한 것만 선택 해서 INSERT
INSERT INTO TESTTBL(ID, USERNAME) VALUES(2, '김연아')

-- 컬럼의 순서를 바꾸어도 상관 없어요
INSERT INTO TESTTBL(USERAGE, USERNAME) VALUES(30,"신사임당");

-- TESTTBL을 삭제

DROP TABLE TESTTBL (
ID INT AUTO_INCREMENT PRIMARY KEY,
    USERNAME VARCHAR(10),
    USERAGE INT
);

INSERT INTO TESTTBL VALUES(NULL, '아이유', 20);

SELECT * FROM TESTTBL;

INSERT INTO TESTTBL VALUES(NULL, '김연아', 30);


-- 꼭 시작은 1부터 시작해야 하나요? 아니예요 내가 정해줄 수 있어요
-- 꼭 1씩 증가해야 하나요? -> 아니예요 증가하는 값도 내가 정해줄 수 있어요
-- MYSQL 내부 변수를 수정해야해요 -> 구글링을 참조해주세요

-- 많은 양의 데이터를 일괄적으로 삽입할 때
DROP TABLE TESTTBL;

CREATE TABLE TESTTBL (
ID INT,
    FNAME VARCHAR(50),
    LNAME VARCHAR(50)
);

-- 이렇게 만들어진 TABLE에 데이터를 입력해 보아요
INSERT INTO TESTTBL
SELECT EMP_NO, FIRST_NAME, LAST_NAME
    FROM employees.EMPLOYEES;
    
SELECT *
FROM TESTTBL;


-- -------------------------------------
-- UPDATE (테이블에 데이터를 수정)

-- 테이블안의 데이터를 수정하려면 UPDATE 구문을 이용
START transaction;

UPDATE TESTTBL
SET LNAME = '홍길동'

SELECT * FROM TESTTBL LIMIT 10;

ROLLBACK;


UPDATE TESTTBL
SET LNAME = '홍길동'
WHERE FNAME = 'PARTO';

DELETE
FROM TESTTBL
WHERE LNAME = '홍길동';

-- 지금까지는 1개의 테이블을 이용해서 CRUD를 진행했다.
-- 테이블은 서로 RELATION(관계)가 존재해요> -> PK, FK
-- 테이블을 서로 결합하려면 어떻게 해야 하나요?
-- JOIN(조인) -> 내일 설명

 

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

 

결국 독일행 비행기티켓 취소했다... ㅡㅡ...

뭐 어떻게 하냐 그럼 돈, 시간이 넉넉한 것도 아닌데.

백신 2차까지 맞았는데 그것과 관계없이 pcr테스트는 왜이렇게 많고

뭐이렇게 제약사항들이 많은지;

그럴거면 그냥 백신 맞으라고 하질 말던가

 

하.. 암튼 오늘 하루도 일단은 정리되었다.