[패캠] SQL: 조건문, JOIN, 집합연산자

IF문

• SELECT 절에 작성
• 형태: IF(조건식, 참일 때 값, 거짓일 때 값)

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

 

 

IFNULL문

• SELECT 절에 작성
• NULL 값을 다른 값으로 대체할 때 사용
• 형태: IFNULL(컬럼명, NULL일 때 값)

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

 

 

CASE문

* 자바스크립트 등의 다른 언어(왼쪽)와 동일 개념을 대조해본 모습

• 조건이 여러 개일 때 (1개일 때는 IF문)
• 항상 END로 닫아줘야
• 결과값을 새로운 컬럼으로 반환 (= 뒤에 AS 별칭 붙이기 가능)
• ELSE는 위의 WHEN ~ THEN 문장이 모두 False일 때 실행
  • ELSE 생략 시 원래 ELSE 자리에 들어갔었을 값은 NULL 반환
• 2가지 형태가 있음 (기준 컬럼명이 있는 경우 / 없는 경우)

-- 1) 조건식에 따른 결과값 출력
CASE
    WHEN 조건식1 THEN 결과값1
    WHEN 조건식2 THEN 결과값2
    ELSE 결과값3
END

-- 2) 컬럼명의 값(조건값)에 따른 결과값 출력
CASE 컬럼명
    WHEN 조건값1 THEN 결과값1
    WHEN 조건값2 THEN 결과값2
END

 

 

예시

1) 조건식 사용

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

 

2) 컬럼에 따른 조건일 때

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

 

 

함수 (Function)

함수 생성

CREATE FUNCTION 함수명(인수값 이름 데이터타입, ...)
    -- 결과값의 데이터타입 선언
    RETURNS 결과값 데이터타입 -- RETURN에 'S' 들어감!
BEGIN
    -- 변수 선언
    DECLARE 임시값 이름 데이터타입
    -- 변수에 인수 할당
    SET 임시값 이름 = 인수값 이름
    -- SELECT문(출력문) 쿼리
    쿼리;
    -- 반환할 값
    RETURN 결과값
END

 

함수 삭제

DROP FUNCTION 함수명;

 

예시

공격력과 방어력의 합을 가져오는 함수

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

 

 

MYSQL Workbench에서 함수 생성 시

MYSQL Workbench에서 함수 생성 시 아래 내용 설정

-- 사용자 계정에 함수 생성 권한 부여
SET GLOBAL log_bin_trust_function_creators=1;

-- 함수 시작 선언
DELIMITER
    -- 함수 쿼리 (BEGIN ~ END까지)
	CREATE FUNCTION ~
-- 함수 끝 선언
DELIMITER;

 

 

함수 실습

문제1) 공격력과 방어력의 합이 120보다 크면 'very strong', 90보다 크면 'strong',

모두 해당되지 않으면 'not strong'을 반환하는 함수 'isStrong'을 만들고 사용해주세요.

 

조건1: attack과 defense를 입력값으로 사용하세요.

조건2: 결과값 데이터 타입은 VARCHAR(20)으로 해주세요.

CREATE FUNCTION isStrong(attack INT, defense INT)
    RETURNS VARCHAR(20)
BEGIN
    DECLARE a INT;
    DECLARE b INT;
    DECLARE isStrong VARCHAR(20);
    SET a = attack;
    SET b = defense;
    SELECT 
        CASE
           WHEN a + b >= 120 THEN 'very strong' 
           WHEN a + b >= 90 THEN 'strong' 
           ELSE 'not strong'
        END INTO isStrong -- 결과값에 할당하는 'INTO isStrong'은 END 다음에 한 번만 넣어줘도 됨
    RETURN isStrong;
END

 

 

UNION, UNION ALL

• 두 쿼리의 결과값을 합쳐서 보여줌
• UNION은 중복 제외, UNION ALL은 중복 포함

주의: 두 쿼리의 각 결과 값 개수가 같아야 함 (다를 경우 에러 발생)

 

ORDER BY에서의 유의:

• 첫 번째 쿼리에서 가져온 컬럼만 가능
• 쿼리 가장 마지막에 작성

 

서브 쿼리

• 메인 쿼리에 또 다른 쿼리를 넣는 것
• SELECT, FROM, WHERE, HAVING, ORDER BY에 가능
  • * GROUP BY는 불가
• INSERT, UPDATE, DELETE 문에도 사용 가능
• 서브쿼리에는 세미콜론(;) 붙이지 않아도 됨

 

SELECT 절의 서브쿼리

• 스칼라 서브쿼리라고도 함
• SELECT 절의 서브쿼리는 반드시 결과값이 1개의 값이어야 함 (여러 개의 값이 서브쿼리의 결과가 되면 에러 발생)

 

FROM 절의 서브쿼리

• 인라인 뷰 서브쿼리라고도 함
• FROM 절의 서브쿼리는 반드시 결과값이 1개의 테이블이어야 함
• 또한, 서브쿼리로 만들어진 테이블은 반드시 별명을 가져야 함
  • FROM 서브쿼리 결과(소괄호) 뒤에 AS ~별명 붙이기

 

WHERE 절의 서브쿼리

• 중첩 서브쿼리라고도 함
• WHERE 절의 서브쿼리는 반드시 결과값이 1개의 컬럼이어야 함
  • 하나의 컬럼에는 여러 값이 존재
• 연산자와 함께 사용
  • WHERE 컬럼명 연산자 서브쿼리
  • 예: WHERE lat_n = (SELECT ~)

 

[ 연산자의 종류 ]

=, >, < 등의 기본 연산자 외에 다른 연산자들 (IN, ALL, ANY, EXISTS)

 

 

* WHERE 절 서브쿼리는 반드시 결과값이 컬럼 1개여야 하지만,

EXISTS의 경우 결과값이 여러 컬럼이어도 됨

 

이유: EXISTS는 (서브쿼리 결과)값이 있는지 없는지만 확인하므로

 

 

EXISTS (서브쿼리)의 의미 = 서브쿼리의 결과값이 '있다면'

EXISTS의 서브쿼리에는 SELECT * 이 가능 (여러 개를 가져와도 그 결과가 존재하는지 여부만 확인)

 

* 다른 연산자와 다르게 컬럼명 없이 단독으로 사용

(WHERE 다음에 바로 EXISTS(서브쿼리))

 

 

서브쿼리 예시

1) 요청: 키가 평균 키보다 작은 포켓몬의 번호를 가져와 주세요.

SELECT number
FROM ability
WHERE height < (SELECT AVG(height) FROM ability);

 

 

2) 요청: 공격력이 모든 전기 포켓몬의 공격력보다 작은 포켓몬의 번호를 가져와 주세요.

SELECT number 
FROM ability
WHERE attack < ALL(SELECT attack FROM ability WHERE type = 'electric');

 

 

3) 요청: 방어력이 모든 전기 포켓몬의 공격력보다 하나라도 큰 포켓몬의 번호를 가져와 주세요.

SELECT number 
FROM ability
WHERE defense > ANY(SELECT attack FROM ability WHERE type = 'electric');

 

 

4) 요청: bug 타입 포켓몬이 있다면 모든 포켓몬의 번호를 가져와 주세요.

SELECT number 
FROM ability
WHERE EXISTS (SELECT * FROM ability WHERE type = 'bug');

 

 

실습

미션1) 몸무게가 가장 많이 나가는 포켓몬의 번호

SELECT number
FROM ability
WHERE weight = (SELECT MAX(weight) 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 * FROM ability WHERE attack > defense);

 

 

테이블 데이터 삭제/수정

 

DELETE: 데이터 삭제하기

pokemon DB의 mypokemon 테이블에서

공격력이 50 이상인 포켓몬 데이터 삭제

DELETE FROM pokemon.mypokemon
WHERE attack > 50;

 

UPDATE: 데이터 수정하기

pokemon DB의 mypokemon 테이블에서

이름이 'chikorita'인 포켓몬의 타입을 'normal'로 수정

UPDATE pokemon.mypokemon
SET type='normal'
WHERE name='chikorita';

 

제약 조건

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

 

제약 조건 종류

제약 조건	의미
NOT NULL	NULL 값 저장 불가
UNIQUE	고유한 값이어야
DEFAULT	입력값 없을 때의 기본 값
PRIMARY KEY	이 컬럼은 테이블의 기본 키이다. NOT NULL + UNIQUE
FOREIGN KY	이 컬럼은 테이블의 외래 키이다. 다른 테이블의 특정 컬럼을 참조

 

 

테이블 생성 시 제약 조건 넣기

CREATE TABLE new_mypokemon(
    number INT PRIMARY KEY,
    name VARCHAR(20) UNIQUE,
    type VARCHAR(10) NOT NULL,
    attack INT DEFAULT 0,
    defense INT DEFAULT 100,
    FOREIGN KEY(number) REFERENCES mypokemon(number)
);

 

 

SQL 분류

DDL(Data Definition Language)	CREATE, ALTER, DROP, RENAME, TRUNCATE	데이터 정의어
DML(Data Manipulation Language)	SELECT, INSERT, UPDATE, DELETE	데이터 조작어
DCL(Data Control Language)	GRANT, REVOKE	데이터 제어어
TCL(Transaction Control Language)	COMMIT, ROLLBACK, SAVEPOINT	트랜잭션 제어어

 

SQL DB에서는 모든 권한을 가진 Root 사용자가 있고 

그 아래에 일부 권한을 가진 나머지 사용자들이 있다.

 

사용자 확인

# MySQL의 기본 DB인 'mysql' DB 선택
USE mysql;

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

 

사용자 생성, 삭제

# 사용자 생성
CREATE USER 사용자명@IP주소;

# 비밀번호와 함께 사용자 생성
CREATE USER 사용자명@IP주소 IDENTIFIED BY '사용자비밀번호'; # 사용자 비밀번호는 문자열 - 꼭 따옴표 사용

# 사용자 삭제
DROP USER 사용자명;

 

권한 부여

# 권한 부여
GRANT 권한 ON 데이터베이스명.테이블명 TO 사용자명@IP주소;

# 권한 확인
SHOW GRANTS FOR 사용자명@IP주소;

# 권한 삭제
REVOKE 권한 ON 데이터베이스명.테이블명 FROM 사용자명@IP주소;

# 권한 적용
FLUSH PRIVILEGES; # 이 명령어를 꼭 실행해야 설정한 권한들이 DB에 적용됨

 

예시

IP 주소에 '%' - 모든 IP 주소 가능

*.* 의미: '*' = 모든 - 모든 데이터베이스.모든 테이블

# newuser@%에게 mydb.mytb에 대한 모든 권한 부여
GRANT ALL PRIVILEGES ON mydb.mytb TO newuser@%;

# newuser@%에게 모든 데이터베이스, 모든 테이블에 대한 SELECT, INSERT 권한 부여
GRANT SELECT, INSERT ON *.* TO newuser@%;

 

트랜잭션

• 데이터베이스의 데이터 상태를 바꾸는 작업의 묶음
  • 예: INSERT, INSERT, DELETE, INSERT, INSERT, ... => DB를 바꾸는 이러한 여러 개의 작업을 하나로 묶은 것
• 반드시 시작을 명시해줘야 함
• 시작한 뒤 DB 바꾸는 작업 여러 개(묶음) 진행하고 이후 2가지 코스로 나뉨
  • 트랜잭션 확정 (실행한 쿼리 내용을 확정)
  • 트랜잭션 취소 (쿼리 실행 이전으로 돌아가기)

# 트랜잭션 시작
START TRANSACTION

# 트랜잭션 확정
COMMIT;

# 트랜잭션 취소
ROLLBACK;

# 트랜잭션 도중 특정한 저장 지점 두기
INSERT ...
INSERT ...
SAVEPOINT A;
INSERT ...
DELETE ...

 

# 저장 포인트 만들기
SAVEPOINT 세이브포인트명;

# 세이브포인트로 돌아가기
ROLLBACK TO 세이브포인트명;