[패스트 캠퍼스] SQL 기초 강의 (2주차)

Day 6

데이터 그룹화 (GROUP BY)

• 데이터를 그룹화해서 통계낼 수 있어야 한다.
• 고객 등급 별 매출 통계, 상품 카테고리별 실적 통계 등…

Ex) SELECT type FROM mypokemon GROUP BY type;

그룹에 조건 주기 (HAVING)

• HAVING 조건식 형식으로 사용 → 조건식에는 그룹 함수를 사용.
• GROUP BY로 묶은 조건 중 HAVING 조건에 해당하는 값만 가져와라.

다양한 그룹 함수 (집계 함수)

모두 SELECT, HAVING 절에서 사용 가능하다.

데이터를 그룹화한 후 (조건에 따라) 집계할 수 있는 함수들로 보면 된다.

• COUNT, SUM, AVG, MIN, AVG

문법 순서 : SELECT(5) → FROM(1) → WHERE(2) → GROUP BY(3) → HAVING(4) → ORDER BY

---

Day 7

조건 만들기 (하나만 - IF)

• Ex) 구매 금액 10억 이상 고객은 VVIP로 1억 이상이면 VIP로…
• SELECT 절에수 주로 사용 → 결과 값을 새로운 컬럼으로 반환
• IF(attack ≥ 60, ‘strong’, ‘weak’) AS attack_class → attack_class 컬럼 참이면 strong 거짓이면 weak 반환.
• IFNULL : 데이터가 NULL인지 아닌지를 확인해서 NULL이면 새로운 값 반환.
  • Ex) IFNULL(name, ‘unknown’) AS full_name : 이름 없으면 full_name 컬럼 unknown으로 반환

조건 만들기 (여러개 - CASE)

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

함수 만들기

원하는 함수를 직접 만들 수 있다. 어떻게?

• CREATE 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 워크벤치에서는 함수 생성 권한 생성해야함.

예시

set global log_bin_trust_function_creators = 1;
delimiter //
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;
		return isStrong;
end
// delimiter ;

select name, isStrong(attack, defense) as isStrong
from mypokemon;

---

Day 8

테이블 합치기 (JOIN)

원하는 데이터가 항상 같은 테이블에 있지 않을 수 있다. → 같은 의미를 가지는 컬럼을 기준으로 테이블 합칠 수 있다.

• INNER JOIN : 두 테이블 모두 가지는 값만 합치기.

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

• LEFT, RIGHT JOIN : RIGHT JOIN [테이블] 인 경우에 테이블에 있는 모든 값 사용.
• OUTER JOIN : MySQL에는 없음 → LEFT JOIN + RIGHT JOIN
  • UNION 사용함 → 두 쿼리의 결과를 중복 제거하고 합쳐서 보여줌.
• CROSS JOIN : 두 테이블에 있는 모든 값 각각 합치기.
  • ON 없어도 됨. → 두 테이블의 ROW 수의 곱만큼의 ROW 결과
• SELF JOIN : 같은 테이블끼리의 INNER JOIN을 의미함.

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

---

Day 9

여러 테이블 한 번에 다루기 (서로 다른 테이블의 값을 한 번에 가져오고 싶다면 ?)

합집합, 교집합, 차집합을 의미하는 것임.

• 합집합
  • UNION : 동일한 값은 제외
  • UNION ALL : 동일한 값 포함
  • 두 쿼리의 결과 값을 합쳐서 보여줌.
  • 마지막에 ORDER BY 작성할 수 있다.

Ex)

select name from mypokemon UNION ALL select name from friendpokemon;

---

Day 10

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

• 하나의 쿼리 내 포함된 또 하나의 쿼리
• 반드시 괄호 안에 있어야 함.
• group by 절에서는 사용 불가함
• insert ,update delete 문에서도 사용 가능
• 세미콜론을 붙이지 않아도 된다.

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

• 반드시 결과값이 하나의 값이어야 한다.

Ex) select number, name, (select height from ability where number = 25) as height from mypokemon where name = ‘pikachu’;

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

• 반드시 결과값이 하나의 테이블이어야 하며, 별명을 가진다.

Ex) select number, height_rank from (select number.rank() over(order by height desc) as height_rank from ability) as A where height_rank = 3;

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

• 반드시 결과값이 하나의 컬럼이어야 하며, 하나의 컬럼에는 여러 개의 값이 존재할 수 있다. (EXISTS 제외)
• 특별히, 비교 연산자 사용시에는 결과값이 하나의 값이 된다.

Ex) 평균 키보다 작은 포켓몬의 번호 select number from ability where height < (select avg(height) from ability);

Ex) 공격력이 모든 전기 포켓몬의 공격력보다 작은 포켓몬 번호 select number from ability where attack < all(select attack from ability where type=’electric’);

• ALL : <, > 와 사용함 → 서브쿼리의 최소, 최댓값보다 작고 큰지를 확인할 수 있음.

Ex) bug 타입 포켓몬이 있다면, 모든 포켓몬의 번호 가져와라.

select number from ability where exists(select * from ability where type = ‘bug’);

---

Day 11

데이터 삭제하기

delete from mypokemon where attack > 50;

데이터 수정하기

이름이 chikorita인 포켓몬에 대해서 type을 변경하겠다.

update from mypokemon set type = ‘normal’ where name = ‘chikorita’;

제약 조건

• 데이터 입력 규칙
• 테이블을 만들거나 변경하면서 설정
• create table 및 alter table 구문에서 설정 가능

종류

• NOT NULL : 컬럼에 NULL 저장할 수 없다.
• UNIQUE : 컬럼의 값들은 서로 다른 값을 가져야 한다.
• DEFAULT : 입력값 없을 시 기본값 설정.
• PRIMARY KEY : 해당 컬럼은 기본 키다.
• FOREIGN KEY : 해당 컬럼은 외래 키다.

Ex) create table new_mypokemon(

number INT PRIMARY KEY,

name VARCHAR(20) UNIQUE,

type VARCHAR(20) NOT NULL,

attack INT DEFAULT 0,

defense INT DEFAULT 100,

FOREIGN KEY(number) REFERENCES mypokemon(number)

);

권한과 DCL

• DDL : 데이터 정의어
• DML : 데이터 조작어
• DCL : 데이터 제어어
• TCL : 트랜잭션 제어어

root 사용자(모든 데이터/모든 기능 사용)

다른 사용자들은 데이터에 대한 접근 권한을 명시할 수 있다.

Ex)

USE mysql; select user, host from user;

사용자 목록 조회할 수 있다.

1. 사용자 생성 : create user [이름]@[ip 주소];
2. 비밀번호와 함께 생성 : create user [이름]@[ip 주소] identified by “[사용자 비밀번호]”;
3. 사용자 삭제 : drop user [이름];
4. 권한 부여 : grant [권한] on [DB 명].[Table 명] to [이름]@[ip 주소];
5. 권한 삭제 : revoke [권한] on [DB 명].[Table 명] from[이름]@[ip 주소];
6. 권한 적용 : flush privileges;

트랜잭션과 TCL

트랜잭션 : DB의 데이터 상태를 바꾸는 작업 묶음

쿼리 실행 이후에 [트랜잭션 확정 or 트랜잭션 이전으로 돌아가기] 선택할 수 있다.

트랜잭션 시작 : START TRANSACTION;

확정 : COMMIT;

이전으로 돌아가기 : ROLLBACK;

트랜잭션 내에 특정한 저장지점 생성 : savepoint A;

→ rollback to [세이브포인트 이름];

 

!! 국비지원교육_SQL 데이터 분석 첫걸음 !!