[패스트캠퍼스] 3. SQL 기초2

2022.12.19 - [데이터베이스/0 + SQL] - [패스트캠퍼스] 2. SQL 기초1

[패스트캠퍼스] 2. SQL 기초1

SQL 기초2

규칙 만들기

IF

• IF(조건식, 참결과, 거짓결과) 형식으로 사용
• 주로 SELECT 절에 사용하는 함수
• 결과 값을 새로운 컬럼으로 반환

SELECT name, 
    IF(attack >= 60, 'strong', 'weak') AS attack_class
FROM mypokemon
;

IFNULL

• 데이터가 NULL 인지 아닌지 확인해 NULL 이라면 새로운 값을 반환
• IFNULL([컬럼명], NULL일 때 값) 형식으로 사용
• 해당 컬럼의 값이 NULL인 행에서 NULL일 때 값을 반환
• 주로 SELECT 절에 사용하는 함수
• 결과 값을 새로운 컬럼으로 반환

SELECT name, 
    IFNULL(name, 'unknown') AS full_name
FROM mypokemon
;

CASE

• 조건을 여러 개 만들 때 사용하는 문법

형식1

CASE
    WHEN 조건식1 THEN 결과값1
    WHEN 조건식2 THEN 결과값2
    ELSE 결과값3
END

형식2

CASE[컬럼명]
    WHEN 조건값1 THEN 결과값1
    WHEN 조건값2 THEN 결과값2
    ELSE 결과값3
END

• 주로 SELECT 절에 사용하는 함수
• 결과 값을 새로운 컬럼으로 반환
• ELSE 문장 생략 시 NULL 값을 반환

SELECT name,
    CASE 
        WHEN attack >= 100 THEN 'very_strong'
        WHEN attack >= 60 THEN    'strong'
        ELSE 'weak'
    END AS attack_class
FROM mypokemon
;

SELECT name,
    CASE type
        WHEN 'bug' THEN 'grass'
        WHEN 'electric' THEN 'water'
        WHEN 'grass' THEN 'bug'
    END AS rival_type
FROM mypokemon
;

CREATE FUNCTION

원하는 함수 직접 만들기!

CREATE FUNCTION [함수명]([입력값 이름] [데이터 타입], ...)
    RETURNS [데이터 타입] -- 결과값 데이터 타입
BEGIN
    DECLARE [임시값명] [데이터타입];
    SET [임시값명] = [입력값명];
    쿼리;
    RETURN 결과값
END

getAbility(attack INT, defense INT) 함수를 만들어 보자!

SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER $$
CREATE FUNCTION getAbility(attack INT, defense INT)
    RETURNS INT
BEGIN
    DECLARE a INT;
    DECLARE b INT;
    DECLARE ability INT;
    SET a = attack;
    SET b = defense;
    SELECT a + b INTO ability; -- 쿼리
    RETURN ability;
END $$
DELIMITER ;

실습

함수 만들기를 배웠으면 실습을 통해 해당 내용을 이해했는지 다시한번 점검해 봅시다!

• 문제
  공격력과 방어력의 합이 120보다 크면 very strong, 90보다크면 strong, 모두 해당 되지 않으면 not strong을 반환하는 함수 isStrong을 만들고 사용해주세요.
  조건1: attack과 defense를 입력값으로 사용
  조건2: 결과값 데이터 타입은 VARCHAR(20) 사용

DELIMITER $$
CREATE FUNCTION isStrong(attack INT, defense INT)
    RETURNS VARCHAR(20)
BEGIN
    DECLARE isstrong VARCHAR(20);
    DECLARE sum INT;
    SET sum = attack + defense;
    SELECT 
        CASE 
            WHEN sum > 120 THEN 'very strong'
            WHEN sum > 90 THEN 'strong'
            ELSE 'not strong'
        END
        INTO isstrong;
    RETURN isstrong;
END $$
DELIMITER ;

테이블 합치기(JOIN)

• 같은 의미를 가지는 컬럼의 값을 기준으로 테이블을 합칠 때 사용하는 키워드
  • 주로 FK를 이용하여 데이터를 합침
• 데이터가 더 많아짐(뻥튀기!)

SELECT [컬럼명]
FROM [테이블명1]
조인방식 키워드 JOIN [테이블명2]
ON [테이블명1].[컬럼명1] = [테이블명2].[컬럼명2]
WHERE 조건식
;

INNER JOIN

SELECT *
FROM mypokemon
INNER JOIN ability
ON mypokemon.number = ability.number
;

LEFT JOIN, RIGHT JOIN

SELECT *
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
;

SELECT *
FROM mypokemon
RIGHT JOIN ability
ON mypokemon.number = ability.number
;

 

OUTER JOIN

• MySQL 키워드에 없음 😭
• LEFT + RIGHT JOIN의 결과를 합침(UNION)
• UNION은 두 쿼리의 결과를 중복을 제외하고 합쳐서 보여주는 집합 연산자

SELECT [컬럼명]
FROM [테이블명1]
LEFT JOIN [테이블명2]
ON [테이블명1].[컬럼명1] = [테이블명2].[테이블명2]
UNION
SELECT [컬럼명]
FROM [테이블명1]
RIGHT JOIN [테이블명2]
ON [테이블명1].[컬럼명1] = [테이블명2].[테이블명2]
;

SELECT *
FROM mypokemon
LEFT JOIN ability
ON mypokemon.number = ability.number
UNION
SELECT *
FROM mypokemon
RIGHT JOIN ability
ON mypokemon.number = ability.number

 

CROSS JOIN

* 두 테이블에 있는 모든 값을 각각 합치기

 

SELECT [컬럼명]
FROM [테이블명1]
CROSS JOIN [테이블명2] -- ON 키워드가 없어도 됨
WHERE 조건식
;

SELECT *
FROM mypokemon
CROSS JOIN ability
;

모든 행이 합쳐진다….!

SELF JOIN

• 같은 테이블에 있는 값 합치기

SELECT [컬럼명]
FROM [테이블명1] AS T1
INNER JOIN [테이블명2] AS T2
ON T1.[컬럼명1] = T2.[컬럼명2]
WHERE 조건식
;

SELECT *
FROM mypokemon AS t1
INNER JOIN mypokemon AS t2
ON t1.number = t2.number
;

실습

포켓몬고 DBA로 일한지 어느덧 3개월차가 된 당신에게 다음과 같은 업무가 주어졌습니다!

1. 포켓몬 테이블과 능력치 테이블을 합쳐서 포켓몬 이름, 공격력, 방어력을 한번에 가져와 주세요.
이 때, 포켓몬 테이블에 있는 모든 포켓몬의 데이터를 가져와 주세요.
만약, 포켓몬의 능력치 데이터를 구할 수 없다면, NULL로 가져와 주세요.

2. 포켓몬 테이블과 능력치 테이블을 합쳐서 포켓몬 번호와 이름을 한번에 가져와 주세요.
이 때, 능력치 테이블에 있는 모든 포켓몬의 데이터를 가져와 주세요.
만약 포켓몬의 이름 데이터를 구할 수 없다면, NULL로 가져와 주세요.

• 동일한 이름을 가지는 컬럼이 있다면 SELECT 해 올때, 어느 테이블에서 합쳐진 컬럼을 가져올 것인지 명시해 줘야함.
  • mypokemon.number 또는 ability.number

/*
1. 포켓몬 테이블과 능력치 테이블을 합쳐서 포켓몬 이름, 공격력, 방어력을 한번에 가져와 주세요.
이 때, 포켓몬 테이블에 있는 모든 포켓몬의 데이터를 가져와 주세요.
만약, 포켓몬의 능력치 데이터를 구할 수 없다면, NULL로 가져와 주세요.
*/
SELECT m.name, a.attack, a.defense 
FROM mypokemon m 
LEFT JOIN ability a 
ON m.`number` = a.`number` 
;

/*
2. 포켓몬 테이블과 능력치 테이블을 합쳐서 포켓몬 번호와 이름을 한번에 가져와 주세요.
이 때, 능력치 테이블에 있는 모든 포켓몬의 데이터를 가져와 주세요.
만약 포켓몬의 이름 데이터를 구할 수 없다면, NULL로 가져와 주세요.
*/
SELECT a.`number`, m.name 
FROM mypokemon m
RIGHT JOIN ability a 
ON m.`number` = a.`number` 
;

여러 테이블 한번에 다루기

합집합(UNION, UNION ALL)

• UINON은 동일한 값은 제외하여 조회(전체가 동일해야함, 하나라도 다르면 다른것으로 인지)
• UNION ALL은 동일한 값도 포함하여 조회
• [쿼리1] UNION [쿼리2] 또는 [쿼리1] UNION ALL [쿼리2] 형식으로 사용
• 쿼리1과 쿼리2 의 결과 값을 합쳐서 보여줌
• 쿼리1 과 쿼리2의 결과 값의 갯수가 동일해야 함
  • 다를 경우 에러 발생
• ORDER BY는 쿼리 가장 마지막에 작성가능
  • 쿼리1 에서 가져온 컬럼으로만 가능

SELECT [컬럼명]
FROM [테이블1명]
UNION
SELECT [컬럼명]
FROM [테이블2명]
;

SELECT [컬럼명]
FROM [테이블1명]
UNION ALL
SELECT [컬럼명]
FROM [테이블2명]
;

교집합(INTERSECT), 차집합(MINUS)

• MySQL에는 두 표현이 존재하지 않음
  • JOIN을 사용하여 해결!
• 같은 이름을 가지는 컬럼이 있다면, [테이블명].[컬럼명] 형식으로 조회

교집합

• 교집합을 확인 하고 싶은 컬럼은 모두 다 기준으로 두고 합쳐 줘야 함

  SELECT [컬럼명]
  FROM [테이블1명] AS A
  INNER JOIN [테이블2명] AS B
  ON A.[컬럼1명] = B.[컬럼1명] AND ... AND A.[컬럼n명] = B.[컬럼n명]
  ;

차집합

• A 교집합 B의 데이터는 제외!

  SELECT [컬럼명]
  FROM [테이블1명] AS A
  LEFT JOIN [테이블2명] AS B
  ON A.[컬럼1명] = B.[컬럼1명] AND ... AND A.[컬럼n명] = B.[컬럼n명]
  WHERE B.[컬럼명] IS NULL -- A - B 의미
  ;

실습

1. 내 포켓몬과 친구의 포켓몬에 어떤 타입들이 있는지 중복 제외하고, 
    같은 타입은 한 번씩만 가져와 주세요.

2. 내 포켓몬과 친구의 포켓몬 중에 grass 타입들의 포켓몬 번호와 이름을
     중복 포함하여 전부 가져와 주세요.
     my, friend's를 whose라는 별명을 사용하여 구분해주세요.

 

/*
1. 내 포켓몬과 친구의 포켓몬에 어떤 타입들이 있는지 중복 제외하고, 
    같은 타입은 한 번씩만 가져와 주세요.
*/
SELECT m.type
FROM mypokemon AS m
UNION
SELECT p.type
FROM friedpokemon AS p
;

SELECT distinct m.type
FROM mypokemon AS m
UNION
SELECT distinct p.type
FROM friedpokemon AS p
;
/*
2. 내 포켓몬과 친구의 포켓몬 중에 grass 타입들의 포켓몬 번호와 이름을
     중복 포함하여 전부 가져와 주세요. 
     my, friend's를 whose라는 별명을 사용하여 구분해주세요.
*/
SELECT m.number,
    m.name,
    'my' AS whose
FROM mypokemon AS m
WHERE m.type = 'grass'
UNION ALL
SELECT p.number, 
    p.name,
    "friend's" AS whose
FROM friendpokemon AS p
WHERE p.type = 'grass'

서브쿼리(조건에 조건 더하기)

• 하나의 쿼리 내 포함된 또 하나의 쿼리
• 반드시 괄호 안에 존재해야함
• SELECT, FROM, WHERE, HAVING, ORDER BY 절에 사용 가능
• INSERT, UPDATE, DELETE 문에도 사용 가능
• 서브 쿼리에는 ; 을 붙이지 않아도 됨

서브쿼리 장단점

SQL 성능 관점에서의 서브쿼리(Subquery)

장점

• 쿼리 작성에 있어 편리함

단점

• 연산 비용 추가
• 최적화 불가
• 쿼리 복잡

SELECT절 서브쿼리(스칼라 서브쿼리)

• 서브쿼리의 결과값이 반드시 하나의 값
  • 주로 조건식에 PK를 활용

SELECT [컬럼명]
    (SELECT [컬럼명]
    FROM [테이블명]
    WHERE [조건식])
FROM [테이블명]
WHERE 조건식
;

FROM절 서브쿼리(인라인 뷰 서브쿼리)

• 서브쿼리 결과는 반드시 하나의 테이블
• 서브 쿼리로 만든 테이블은 반드시 별명을 가져야함

SELECT [컬럼명]
FROM (SELECT [컬럼명]
        FROM [테이블명]
        WHERE 조건식) AS [테이블 별 명]
WHERE 조건식
;

WHERE절 서브쿼리(중첩 서브쿼리)

• WHERE절의 서브쿼리는 반드시 결과값이 하나의 컬럼(EXISTS 제외)
  • 하나의 컬럼에는 여러 개의 값이 존재할 수 있음
• 연산자와 함께 사용
  • 일반적으로 WHERE [컬럼명] [연산자] [서브쿼리] 형식 사용

SELECT [컬럼명]
FROM [테이블명]
WHERE [컬럼명] [연산자] (SELECT [컬럼명]
                        FROM [테이블명]
                        WHERE 조건식);

서브쿼리에 사용하는 연산자

대부분의 WHERE 절의 서브쿼리는 비교연산자를 사용
비교 연산자

비교 연산자만 사용 시, WHERE 절의 서브 쿼리는 반드시 결과값이 하나의 값이어야 합니다.

주요 연산자

주요 연산자 사용 시, WHERE 절의 서브 쿼리는 반드시 결과값이 하나의 컬럼이어야 합니다.

• EXISTS는 단독으로 사용하며, 결과값이 여러 컬럼이어도 됩니다.
• ALL 연산자 팁
  • < : 서브쿼리 결과의 최솟값 보다 작은 지 확인
  • > : 서브쿼리 결과의 최댓값 보다 큰 지 확인
• ANY 연산자 팁
  • < : 서브쿼리 결과의 최댓값 보다 작은 지 확인
  • > : 서브쿼리 결과의 최솟값 보다 큰 지 확인
• EXISTS 연산자 팁
  • 값이 있는지 확인할 때 사용하는 연산자로 TRUE/FALSE 반환

실습

포켓몬고 DBA 팀에 이슈가 생겼습니다..!!
레거시 쿼리에서 문제가 생겨서 리팩토링 하기로 했습니다.
문제가 생긴 요구사항을 볼까요?

1. 내 포켓몬 중에 몸무게가 가장 많이 나가는 포켓몬의 번호를 조회
2. 속도가 모든 전기 포켓몬의 공격력보다 하나라도 작은 포켓몬의 번호를 조회
3. 공격력이 방어력보다 큰 포켓몬이 있다면 모든 포켓몬의 이름 조회

음… 서브 쿼리를 이용해서 SQL을 작성해 봅시다.!

/*
1. 내 포켓몬 중에 몸무게가 가장 많이 나가는 포켓몬의 번호를 조회
*/
SELECT number
FROM ability
WHERE weight = (SELECT MAX(weigth) FROM ability)
;

/*
2. 속도가 모든 전기 포켓몬의 공격력보다 하나라도 작은 포켓몬의 번호를 조회
*/
SELECT number
FROM ability
WHERE speed < ANY(SELECT attack
                    FROM ability
                    WHERE type = 'electric')
;

/*
3. 공격력이 방어력보다 큰 포켓몬이 있다면 모든 포켓몬의 이름 조회
*/
SELECT name
FROM mypokemon
WHERE EXISTS (SELECT name 
                FROM ability 
                WHERE attack > defense)
;

수정, 삭제

데이터 삭제하기

DELETE FROM [테이블명]
WHERE 조건식
;

DELETE FROM mypokemon
WHERE attack > 50
;

데이터 수정

UPDATE [테이블명]
SET [컬럼명] = [수정값]
WHERE 조건식
;

UPDATE mypokemon
SET name = 'shinkai'
WHERE name = 'chikorita'
;

제약 조건

• 데이터를 입력할 때 실행되는 데이터 입력 규칙
• 테이블을 만들거나 변경하면서 설정
  • CREATE TABLE 및 ALTER TABLE 구문

CREATE TABLE [테이블명] (
    [컬럼명] [데이터 타입] [제약 조건],
    [컬럼명] [데이터 타입] [제약 조건],
    ...
);

CREATE TABLE member(
    id Long PRIMARY KEY, -- 기본키
    phone VARCHAR(11) UNIQUE, -- 연락처는 서로 다른값을 가져야함
    name VARCHAR(20) NOT NULL, -- 이름은 NULL이면 안됨
    gender INT DEFAULT 0 -- 남: 0, 여: 1
    FOREIGN KEY(team_id) REFERENCES team(id) -- 팀 id를 외래키로 사용
    -- team_id는 team 테이블의 기본키(PK) 
);

권한과 DCL(Data Control Language)

SQL은 다음 4가지로 분류가 가능합니다.

 

데이터 베이스는 권한에 따라서 데이터 접근을 제어 할 수 있습니다.

사용자 확인하기

-- MySQL 기본 데이터베이스인 mysql 데이터베이스 선택
USE mysql;

-- 사용자 목록 조회
SELECT user, host FROM user;

사용자 생성, 삭제

• ip주소가 %이면 모든 ip에서 접근을 허용한다는 의미
  ```sql
• • 사용자 생성
    CREATE USER [사용자명]@[ip주소];

-- 비밀번호와 함께 사용자 생성
CREATE USER [사용자명]@[ip주소]
IDENTIFIED BY '[사용자 비밀번호]'
;

-- 사용자 삭제
DROP USER [사용자명]@[ip주소];

### 권한 확인, 삭제, 적용
```sql
-- 권한 부여
GRANT [권한] 
ON [데이터베이스명].[테이블명] 
TO [사용자명]@[ip주소];

-- 권한 확인
SHOW GRANTS FOR [사용자명]@[ip주소];

-- 권한 삭제
REVOKE [권한] 
ON [데이터베이스이름].[테이블이름] 
FROM [사용자명]@[ip주소];

-- 권한 적용
FLUSH PRIVILEGES;

권한 종류

권한 종류는 크게 3가지 입니다.

• SUPER(글로벌 권한)
  • GRANT SUPER ON *.* TO seaung@localhost;
  • 모든 데이터베이스의 모든 테이블에 권한이 있음
• EVENT(DB 권한)
  • GRANT EVNET ON members.* TO seaung@localhost;
  • members 데이터 베이스의 모든 테이블에 권한이 있음
• 특정 키워드(오브젝트 권한)
  • GRANT SELECT, INSERT, UPDATE ON *.* TO seaung@localhost;
  • 모든 데이터베이스의 모든 테이블에 SELECT, INSERT, UPDATE 기능만 권한 있음

트랜잭션과 TCL(Transaction Control Language)

• 데이터베이스의 데이터 상태를 바꾸는 작업(transaction)

트랜잭션 과정

-- 트랜잭션 시작하기
START TRANSCATION;

-- 트랙잭션 확정
COMMIT;

-- 트랜잭션 이전으로 돌아가기
ROLLBACK;

-- 세이브포인트 만들기
SAVEPOINT [세이브포인트명];

-- 세이브포인트로 돌아가기
ROLLBACK TO [세이브포인트명];