2022-01-27-Database3(2)

yyj·2022년 2월 2일

.

목록 보기

9/21

Grouping

예시를 들어 설명해보자

buyTBL에서 사용자가 구매한 물품의 개수를 출력(사용자 ID와 구매수량을 출력)
그룹핑은 GROUP BY 를 활용하여 적을 사용할 수 있다.

SELECT *
FROM buyTBL;

SELECT userID, buyAmount
FROM buyTBL
ORDER BY userID;
-- userID 하나하나 출력된다
-- BBK 5
-- BBK 10
-- BBk 2
-- ....

SELECT userID, SUM(buyAmount)
FROM buyTBL
GROUP BY userID
ORDER BY userID;
-- userID를 기준으로 각 더한 값이 출력된다!
-- BBK 19
-- EJW 4
-- JYP 1
-- KBS 6
-- SSK 5

SELECT userID, COUNT(buyAmount)
FROM buyTBL
GROUP BY userID
ORDER BY userID;
-- userID기준으로 값을 카운트 해줌
-- BBK 4
-- EJW 3
-- JYP 1
-- KBS 3
-- SSK 51

SELECT userID, AVG(buyAmount)
FROM buyTBL
GROUP BY userID
ORDER BY userID;
-- 해당 결과도 userID 기준으로 각 평균을 구해준다!

각 사용자 별 구매액의 총합을 출력하세요.(구매액 = 단가 * 수량) 한번 풀어보자!
SELECT userID AS 'ID', SUM(productPrice * buyAmount) AS 'amount'
FROM buyTBL
GROUP BY userID
ORDER BY userID;
-- ID  AMOUNT
-- BBK 1920
-- EJW 95
-- JYP 200
-- KBS 1210
-- SSK 75
SQL문의 순서!
-- SELECT
-- FROM
-- WHERE
-- GROUP BY
-- ORDER BY 순서로 진행된다! 꼭 명심하고 기억하고 있자!

집계 함수

집계함수의 종류는 여러가지 있다 그 중 많이 사용하는 것을 알아보자!
SUM(), AVG(), MAX(), MIN(), COUNT(), STDEV(), ... 등등 있다

문제를 풀며 활용해 보자

각 사용자별로 한번 구매 시 물건을 평균 몇 개 구매했는지 조회하세요.
SELECT userID, AVG(buyAmount)
FROM buyTBL
GROUP BY userID
ORDER BY userID;

-- userTBL에서 가장 큰 키와 가장 작은키의 회원의 이름과 키를 출력하세요!
-- SELECT userName, MAX(userHeight), MIN(userHeight)
-- 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 이상인 사용자의 아이디와 총 구매 금액을 출력하세요!

조건이 있기 때문에 WHERE 절을 활용해야 하지만 WHERE 절은 GRUOP 함수(집계함수)를 사용할 수 없다!

그룹에 대한 조건을 WHERE를 사용하지 않고 HAVING을 이용해야 한다!

HAVING절에는 GROUP 함수(집계함수)를 사용할 수 있다!
SELECT userID, SUM(productPrice * buyAmount)
FROM buyTBL
GROUP BY userID
HAVING SUM(productPrice * buyAmount) >= 1000;
-- BBk 1920
-- KBS 1210
SQL SELECT 구문의 순서!
-- SELECT
-- FROM
-- WHERE
-- GROUP BY
-- HAVING
-- ORDER BY (HAVING절이 추가됨에 따라 다음과 같이 순서가 적용된다 꼭 있지말고 기억하자!)

"SQL"

1.DML : SELECT, INSERT, UPDATE, DELETE

- Trasaction을 적용할 수 있다!

2.DDL : CREATE, DROP, ALTER

- Transaction을 설정할 수 없다!

3.DCL : GRANT

Transaction?

일(작업)의 최소 단위 : 개발자가 임의로 설정 가능
- 예시를 한번 들어보자 ex) 은행 이체 업무
  1. A의 계좌에 1000이 있는지 잔액확인 --> SELECT
  2. B의 계좌가 존재하는지 확인 --> SELECT
  3. A의 계좌를 1000원 뺀 금액으로 설정 --> UPDATE
  4. B의 계좌를 1000원 더한 값으로 설정 --> UPDATE
  1에서 4까지의 과정을 진행한다 했을 때 1에서 3까지 진행 완료후 ATM에 오류가 발생하여 꺼진다면 A의 계좌에선 1000원이 빠져나간 거 까지만 실행이 되는 오류가 발생한다.

1에서 4까지의 과정을 Transaction을 설정하면 DBMS가 4가지 특성(기능)을 제공하여 이러한 오류를 해결 할 수있다. (ACID)
- Transaction의 4가지 특징
1. Atomicty (원자성) : All or Nothing
2. Consistency (일치성) : 데이터의 일관성을 유지
3. Isolation (독립성) : 동시성때문에 발생하는 문제를 해결
4. Durability (영속성) : 데이터가 안전하게 저장되는 것을 보장

Transaction의 예시를 함 알아보자!

START TRANSACTION;    -- Transaction 시작

SELECT *
FROM buyTBL;

DELETE FROM buyTBL;

ROLLBACK;  -- or COMMIT  

SELECT *
FROM buyTBL;
Transaction을 끝내려면 COMMIT, ROLLBACK을 이용하면 된다!

ROLLBACK은 현재 작업한 Transaction을 취소!

COMMIT은 현재 작업한 Transaction을 실행 시킴!

SELECT를 알아 보았으니 나머지 DML에 대해 알아보자

INSERT

테이블에 데이터를 삽입

우선 테이블을 하나 생성한 후
CREATE TABLE testTBL (
    id        INT,
    userName  VARCHAR(10),
    userAge   INT
);
가장 기본적인 INSERT(모든 컬럼에 데이터를 다 넣을 경우)

작성은 다음과 같이 한다
- INSERT INTO 테이블명 VALUES (컬럼의 값) -
--INSERT INTO testTBL VALUES(1, '아이유', 20);

필요한 것만 선택해서 INSERT

다음과 같이 작성한다!
- INSERT INTO 테이블명(컬럼명) VALUES(컬럼명에 들어가는 값) -
INSERT INTO testTBL(id, userName) VALUES(2, '김연아');
-- 컬럼의 순서를 바꾸어도 상관 없어요!
INSERT INTO testTBL(userAge, userName) VALUES(30, '신사임당');

많은 양의 데이터를 일괄적을 삽입할 때!

-- 일단 기존의 테이블을 삭제
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;

employees스키마에서 employees테이블의 값을 가져와 넣을 수 있다!.

추가적으로 몇가지더 알아보자

CREATE 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);
-- id  userName  userAge
-- 1   아이유     20
-- 2   김연아     30
AUTO_INCREMENT를 사용하면 값을 입력하지 않아도 컬럼 값이 1씩 증가한다.

꼭 1씩 증가는 아니다 증가값을 설정해 줄수 있지만 MySQL내부 변수를 수정 해야한다.!

UPDATE

테이블에 데이터를 수정
테이블 안의 데이터를 수정하려면 UPDATE 구문을 이용

UPDATE문은 다음과 같이 사용한다
-UPDATE 테이블명 SET 컬럼명 WHERE 조건-

START TRANSACTION;

UPDATE testTBL
SET lname = '홍길동';

SELECT * FROM testTBL LIMIT 10;

ROLLBACK;


UPDATE testTBL
SET lname = '홍길동'
WHERE fname = 'Parto';

SELECT * FROM testTBL LIMIT 10;

DELETE

테이블에 데이터를 삭제

DELETE문은 다음과 같이 사용한다.
DELETE FROM 테이블명 WHERE 조건
DELETE 
FROM testTBL
WHERE lname = '홍길동';

yyj

초보개발자

이전 포스트

2022-01-27-Database3

다음 포스트

2022-01-27-Database3(2)

.

Grouping

집계 함수

"SQL"

1.DML : SELECT, INSERT, UPDATE, DELETE

- Trasaction을 적용할 수 있다!

2.DDL : CREATE, DROP, ALTER

- Transaction을 설정할 수 없다!

3.DCL : GRANT

Transaction?

SELECT를 알아 보았으니 나머지 DML에 대해 알아보자

INSERT

UPDATE

DELETE

2022-01-27-Database3

2022-01-28-Database3

0개의 댓글