Environment & Database (4) - SQL Property & Syntax

New Stuff

Key Points

• SQL (Structured Query Language)
  • ACID Property
  • SQL Syntax

---

Learned Stuff

[SQL (Structured Query Language)]

• Relational Database 를 관리하기 위해 만들어진 프로그래밍 언어

 

ACID Property

• Transaction : Query 를 하나의 묶음으로 처리해준 것
  • 하나의 Transaction 은 성공 또는 실패로 나뉨
    • 모든 작업이 성공 --> SUCCESS!
    • 중간에 실패 --> FAIL!
  • SQL Database 는 ACID 성질을 가집니다

 

Transaction Property

1. Atomicity
   • 부분적으로 실행되지 않거나 중단되지 않아야 합니다.

 

2. Consistency
   • Transaction 이 완료되더라도 이전 Database 형태와 동일해야 합니다.
     • 이전에 없던 Column 이 추가되거나 특정 Column 의 Datatype 이 바뀌면 안됩니다.

 

3. Isolation
   • Transaction 끼리 서로 간섭할 수 없습니다.

 

4. Durability
   • 하나의 Transaction 이 성공적으로 수행되었다면 해당 기록은 영구적이여야 합니다.

 

SQL Syntax

1. CREATE

# Table 만들기
CREATE TABLE table_name (
    column_name_1 datatype,
    column_name_2 datatype,
    column_name_3 datatype
);

# Primary Key 또는 Foreign Key 설정하기
# ex) column_name_1 - primary key 로 설정
# ex) column_name_2 - foreign key 로 설정
CREATE TABLE table_name (
    column_name_1 datatype,
    column_name_2 datatype,
    column_name_3 datatype,
    PRIMARY KEY (column_name_1)
    FOREIGN KEY (column_name_2)
);

# Null 값 포함하지 않도록 설정하기
CREATE TABLE table_name (
    column_name_1 datatype NOT NULL,
    column_name_2 datatype NOT NULL,
    column_name_3 datatype NOT NULL
);

• Datatypes
  • String
    • CHAR(size) : fixed length string (0 ~ 255)
    • VARCHAR(size) : variable length string (0 ~ 65535)
  
  
  • Numeric
    • INTEGER
    • FLOAT
  
  
  • Date
    • DATE

 

2. SELECT & FROM

# Artist Table 에 있는 모든 Column 가져오기
SELECT * FROM Artist;

# Artist Table 에 있는 특정 Columns 가져오기
SELECT Artist_Name, Artist_Album FROM Artist;

 

3. WHERE

• 조건 추가하는 Syntax
  • (< , <= , > , >=) : 대소 관계
  • LIKE & IN : 특정 value 값이 담긴 데이터 불러오기
  • NULL & NOT NULL : Null 값인 (or Null 값이 아닌) 데이터 가져오기

 

# 선택한 Column들 중 'Alan Walker' 라는 Artist 관련 데이터만 가져오기
SELECT Artist_Name, Artist_Album 
FROM Artist 
WHERE Artist_Name = 'Alan Walker';

# 선택한 Column들 중 'Alan Walker' 라는 Artist 를 제외한 데이터만 가져오기
SELECT Artist_Name, Artist_Album 
FROM Artist 
WHERE Artist_Name <> 'Alan Walker';

# 선택한 Column 에서 노래 시간이 180 초 이상인 데이터만 가져오기
# > , < , <= 도 적용 가능
SELECT Song_Time
FROM Song 
WHERE Song_Time >= 180;

# 선택한 Column 에서 Artist 이름 중에 'the' 가 들어간 데이터만 가져오기
# "the%" : the 로 시작하는 데이터만 가져오기
# "%the" : the 로 끝나는 데이터만 가져오기
SELECT Artist_ID , Artist_NAME
FROM Artist
WHERE Artist_NAME LIKE "%the%";

# 선택한 Column 에서 Artist 이름이 'Alan Walker' 이거나 'Zedd' 인 데이터만 가져오기
SELECT Artist_ID , Artist_NAME
FROM Artist
WHERE Artist_NAME IN ("Alan Walker" , "Zedd")

# 선택한 Column 에서 Artist 이름이 Null 값으로 표현된 데이터만 가져오기
# IS NOT NULL : Artist 이름이 Null 값이 아닌 데이터만 가져오기
SELECT Artist_ID , Artist_NAME
FROM Artist
WHERE Artist_NAME IS NULL

 

4. ORDER BY

• 정렬 Method

# 선택한 Column 에서 노래 시간을 기준으로 오름차순으로 정렬
SELECT Song_ID, Song_Name, Song_Time
FROM Song 
ORDER BY Song_Time;

# 선택한 Column 에서 노래 시간을 기준으로 내림차순으로 정렬
SELECT Song_ID, Song_Name, Song_Time
FROM Song 
ORDER BY Song_Time DESC;

 

5. LIMIT

• 데이터를 제한하여 불러오는 Method

# 선택한 Column 에서 처음 150 개의 Data만 가져오기
SELECT Song_ID, Song_Name, Song_Time
FROM Song 
LIMIT 150;

 

6. DISTINCT

• Unique 한 데이터만 가져오기
  • SELECT 뒤에 사용할 것

# Artist 와 노래가 담긴 Table 에서 Unique 한 Artist 이름을 가진 데이터만 가져오기
SELECT DISTINCT Artist_Name
FROM Artist_Song

 

7. JOIN

• 공통된 부분을 연결하는 Method
• 아래와 같은 2 개의 Table 로 예시를 들겠습니다.

Artist Table & Song Table

 

# Artist Table 의 Artist_ID 와 Song Table 의 Artist_ID 의 공통된 부분 연결하기
SELECT * FROM Artist 
JOIN Song 
    ON Artist.Artist_ID = Song.Artist_ID

 

• 아래와 같은 Join Table 이 만들어 집니다.
  

  

  Song_Artist Join Table

 

# Other Examples
# Join 을 여러번 수행할 수 있음
# 위에 만들어진 Table 에서 Album Table 을 Artist_ID 로 Join 시키기
SELECT * FROM Artist 
JOIN Song 
    ON Artist.Artist_ID = Song.Artist_ID
JOIN Album
    ON Album.Artist_ID = Song.Artist_ID

 

8. Functions (함수) + GROUP BY

• Functions
  • SUM() : 합산 Return
  • COUNT() : 몇 개인 지 Return
  • AVG() : 평균 값 Return
  • MAX() : 최고값 Return
  • MIN() : 최저값 Return

 

• GROUP BY
  • 묶어서 보겠다는 의미
  • Pandas 의 groupby() 비슷한 개념

 

• 위에서 쓰인 Song_Artist Join Table 을 가지고 예시를 들겠습니다.
  • Song_Artist Join Table 은 아래와 같습니다.
    

    

    Song_Artist Join Table

# Song_Artist Join Table 에서 Artist_Name 으로 묶어서 Data 불러오기
SELECT * FROM Artist 
JOIN Song 
    ON Artist.Artist_ID = Song.Artist_ID
GROUP BY Artist_Name;

Output

Group by Artist_Name

Analysis

• Artist_Name 으로 묶어서 데이터를 불러올 경우 각 그룹의 첫번째 Data만 불러오게 됩니다.
  • 중간에 'Alan Walker' Artist 의 'All Falls Down' 노래 관련 Data가 빠진 것을 알 수 있습니다.
  • GROUP BY 는 주로 Functions 와 함께 쓰입니다.

 

# Song_Artist Join Table 에서 Artist_Name 그룹 별 데이터 갯수 확인하기 
# COUNT(Artist_Name) AS Artist_Counts : 해당 Column 의 이름을 'Artist_Counts' 로 바꾸겠다는 의미
SELECT Artist_Name , COUNT(Artist_Name) AS Artist_Counts FROM Artist 
JOIN Song 
    ON Artist.Artist_ID = Song.Artist_ID
GROUP BY Artist_Name;

Output

Group by Artist_Name + Count Function

 

Analysis

• 이런 방식으로 SUM() 또는 다른 함수들도 적용할 수 있습니다.

 

9. HAVING

• GROUP BY 로 조회된 Data 에 필터를 적용한다는 의미
  • GROUP BY 와 같이 쓰임

 

# Song_Artist Join Table 에서 Artist_Name 그룹 별 데이터 갯수가 2 이상인 것만 불러오기
SELECT Artist_Name , COUNT(Artist_Name) AS Artist_Counts FROM Artist 
JOIN Song 
    ON Artist.Artist_ID = Song.Artist_ID
GROUP BY Artist_Name
HAVING Artist_Counts >= 2;

 

Output

Group by Artist_Name + Count Function + Having

 

10. SUBQUERY

• Query 문 안에 Query 문이 있는 형식

# 상위 Query 문 : 만들어진 Data 에 Artist_ID , Artist_Name Column 들만 가져오기
# 하위 Query 문 : Artist Table 과 Song Table 을 Artist_ID 로 Join 시키기
SELECT Artist_ID , Artist_Name 
FROM (
    SELECT * FROM Artist 
    JOIN Song 
        ON Artist.Artist_ID = Song.Artist_ID
);