11 Thu

데이터 분석을 위한 고급 SQL

OT

앞으로 배울 것

• DML, Data Manipulation Language

• ERD, Entity Relationship Diagram

  • 데이터 타입

  • ERD 읽는법

• 서브 쿼리

• 윈도우 함수

  • 집계 함수의 일종

  • 코테에도 종종 나오는 유형

• MySQL 말고도 다른 데이터베이스도 다룰 예정

• 정규 포현식

• MySQL 함수

DML (Data Manipulation Language)

지금까지는 SELECT를 통해 데이터를 보기만 했는데, 지금부터는 데이터를 조작하는 일까지 할 것

INSERT : 데이터 추가

• 테이블 전체에 데이터를 추가하는 법

-- INSERT INTO 테이블 명 VALUES (VALUE_LIST);
INSERT INTO Salary Values ('1', 'A', '250', '2020-03-31');

• 특정 컬럼에 데이터를 추가하는 법

  • 이 때 추가해주지 않은 컬럼에는 자동으로 Null이 추가된다

-- INSERT INTO 테이블명 (COLUMN_LIST) VALUES (VALUE_LIST);
INSERT INTO Salary (Id, Salary) VALUES ('2', '550');

DESC : 데이터 타입 확인

• DESC : Describe

• DESC 테이블 : 테이블의 structure를 확인한다

  • int : 정수

  • varchar(10) : 최대 길이가 10인 문자열

  • date : 날짜 시간형 데이터

UPDATE : 데이터 갱신

• 컬럼 전체에 데이터 업데이트

  • 이 때 = 를 지금까지는 비교 연산자로 사용했는데, SET 에서 사용하는 = 는 대입(또는 할당)연산자 이다.

-- UPDATE 테이블명 SET 컬럼 = 값;
UPDATE Salary SET Salary = Salary + 100

• 특정 컬럼의 특정 데이터만 갱신하기

-- UPDATE 테이블명 SET 컬럼 = 값 WHERE 조건식;
UPDATE Salary SET Salary = Salary + 100 WHERE Id = 2;

LeetCode 627

update Salary set sex = case
    when sex = 'f' then 'm' else 'f' end

DELETE : 데이터 삭제

• UPDATE 와 문법이 비슷

• 테이블 전체에 데이터 삭제하는 방법

-- DELETE FROM 테이블명;
DELETE FROM Salary

• 특정 데이터만 삭제하기

-- DELETE FROM 테이블명 WHERE 조건식;
DELETE FROM Salary WHERE Id = 2;

서브쿼리 맛보기

• FROM 이하의 SELECT 구문을 서브쿼리 라고 하며, 이 쿼리를 daily_status 라는 테이블이 있다고 간주할 수 있다.

• 아래와 같이 FROM 절 뿐만 아니라 WHERE 절에서도 사용 가능하다.

SELECT daily_stats.week, AVG(daily_stats.incidents_daily)
FROM (
    SELECT week, date, COUNT(incident_id) AS incidents_daily
    FROM crimes
    GROUP BY week, date
    ) daily_status
GROUP BY daily_stats.week

SELECT *
FROM crimes
WHERE date = (SELECT MIN (date) FROM crimes)

SELECT *
FROM crimes
WHERE date IN (SELECT date FROM crimes ORDER BY date DESC LIMIT 5

DELETE WITH JOIN

• DELETE FROM 테이블 이라고 배웠지만 JOIN을 할 때에는 DELETE 테이블 FROM 테이블 JOIN ... 으로 사용하게 된다. 이는 JOIN 할 테이블 에서 조건이 맞더라도 선택적으로 지울 테이블을 결정하기 위해서이다.

• 쉽게 말하면 두 개의 테이블이 JOIN 되었을 때 두 개의 테이블에서 모두 삭제 할지, 어느 한쪽에서 삭제할지를 선택할 수 있도록 하기 위함

DELETE t1, t2
FROM t1
     INNER JOIN t2 ON t1.id = t2.ref
WHERE t1.id = 1;

LeetCode 196

• 서브쿼리 또는 이너 조인을 이용한 딜리트의 두 가지 방법으로 풀 수 있다.

delete from person
where id not in (
    select sub.min_id
    from (
        select email, min(id) as min_id
        from person
        group by email ) sub )

delete p1
from person as p1
    inner join person as p2 on p1.email = p2.email
    where p1.id > p2.id

ERD (Entity Relationship Diagram)

개체들 간 관계를 보기 위한 지도

ERD의 3가지 요소

• Entity

  • 테이블을 의미 한다고 봐도 무방

• Attribute

  • Entity의 특징. 컬럼을 의미 한다고 봐도 무방

  • 컬럼 이름과 Key의 두 항목으로 나누어져 있음

    • 세 항목으로 나누면 컬럼의 데이터 타입까지 포함

  • Key

    • 식별자라고도 불린다

    • PK : Primary key

      • PK에는 테이블 당 하나

      • 그리고 중복값이 없다

    • FK : Foreign key

      • 외부 키라고도 하며 다른 테이블에서 참조되는 값이다.

  • one to many의 연결선은 one쪽에 세로선 두개, many쪽에 삼선이 되어있다

• Relationship

데이터 타입

• 숫자

  • 정수 : tinyint, smallint, mediumint, int ,bigint

    • 정수의 범위 차이만 있음

  • 실수 : decimal, double, float

    • 실수의 범위 차이만 있음

• 문자 : varchar, char

  • various character, character의 약자

• 날짜, 시간

  • date : 1010-01-01 ~ 9999-12-31

  • datetime : 1010-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999

  • timestamp() = datetime() + timezone

  • 이 때 OrderDate는 varchar이다

    • ex) 12/8/1968

    • 따라서 date_add 등의 함수는 사용 불가

    • 이럴 때는 str_to_date() 함수를 사용해야함(mysql 기준)

• 참, 거짓

  • boolean