[엘리스 AI 트랙 2기] 4주차-SQL (07/13)

자료의 출처는 '엘리스 AI 트랙 2기 (https://aitrack.elice.io/)' 'SQL'이며, 학습 후 정리한 내용입니다.

⚡️올바르지 않은 내용이 있을 경우 댓글로 남겨주시면 감사하겠습니다.⚡️

---

1. SQL 시작하기

01. SQL

데이터 베이스란?

여러 사람이 공유하여 사용할 목적으로 체계화해 통합, 관리하는 데이터의 집합 

작성된 목록으로써 여러 응용 시스템들의 통합된 정보들을 저장하여 운영할 수 있는 공용 데이터들의 묶음

 

Maria DB, Amazon Redshift, Oracle DB 등 많은 종류가 존재

 

데이터베이스의 종류에 따라 사용 방법이 조금씩 다름나,

데이터베이스에서 검색과 분석에 사용되는 기본 사용 방법은 데이터베이스 종류와 상관없이 동일

 

'''

많은양의 데이터를 관리하기 위해 나온 솔루션
데이터를 여러 컴퓨터에 공유할 수 있다.
데이터를 규격과 정합성을 맞춰 저장할 수 있다.
데이터를 이쁘게 잘 정리하기 위해, 보존하기 위해 사용한다.

'''

SQL

Structured Query Language의 약자로

 

관계형 데이터베이스 관리 시스템(RDBMS)의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어

관계형 데이터베이스 관리 시스템에서 자료의 검색과 관리, 데이터베이스 스키마 생성과 수정, 데이터베이스 객체 접근 조정 관리를 위해 고안되었다.

많은 수의 데이터베이스 관련 프로그램들이 SQL을 표준으로 채택

'''

주로 데이터베이스에서 데이터를 쿼리할 때 사용을 하는 언어
database 종류 상관없이 대부분의 경우 sql을 사용함으로 데이터를 조작할 수 있다.

 

SQL 의 종류는 DDL, DML, DCL, TCL 로 나누어짐

DDL: Data Definition Language

DML: Data Manipulation Language

DCL: Data Control Language
TCL: Transaction Control Language

# DDL, DML만으로도 충분히 ORM을 잘 사용할 수 있다.

'''

DDL

Data Definition Language

database나 table, 데이터 구조를 정의하고 수정하는 명령어들

 

CREATE: db 또는 table을 생성한다.

- CREATE DATABASE database_name;

- CREATE TABLE table_name;
DROP: db 또는 table을 삭제

- DROP database_name | table_name;

ALTER: db 또는 table의 구조를 수정

TRUNCATE: table을 (강력하게) 제거

# 테이블 또는 데이터를 강력하게 제거 - 어떤 조건이 있더라도 무조건 제거가된다.

DML

Data Manipulation Language

table의 데이터를 조작하는 명령어들

 

INSERT: table에 데이터를 적재한다.

- INSERT INTO table_name(column1, column2) VALUES(value1, value2);

UPDATE: table에 데이터를 수정한다.

- UPDATE table_name SET column1=value1 WHERE @@;

SELECT: table에서 데이터를 꺼내온다.

- SELECT column1 | * FROM table_name WHERE column1=value1;

DELETE: table에서 데이터를 삭제한다.

- DELETE FROM table_name WHERE column1=value1;

SQL이 할 수 있는 것

데이터 검색 /데이터 삽입 /데이터 수정 /데이터 삭제 /데이터베이스 생성 /테이블 생성 등

쿼리 작성

DESC 대상(table);

DESC: Describe의 약자

 

* SQL에서 사용되는 모든 명령어는 대문자 또는 소문자로 나타낼 수 있으며, 문자열을 나태낼 경우 '' 또는 "" 따옴표를 기입하며,

종결시 ;(세미 콜론)을 통해 명령의 종료를 알려준다.

db의 종류

MySQL, mongoDB, mongoDB, SQLite, Oracle, PostgreSQL, MariaDB, DynamoDB 등

sqlite

간단한 작은 RDBMS(Relation DataBase Management System) 파이썬에 이미 내장되어있기에 따로 설치를 하지 않아도 된다.

세팅하기고 사용하기가 매우 간편
여러 컴퓨터에서 접근하지 힘들다.

가벼운 db기에 성능적으로 느리다

02. 테이블에서 데이터 검색하기

데이터베이스의 종류

데이터베이스는 다양한 종류를 가지고 있지만, 가장 많이 사용되는 종류는

관계형 데이터베이스

비관계형 데이터베이스

관계형 데이터베이스의 정의

관계형 데이터베이스란?

하나 이상의 테이블로 이루어지며

서로 연결된 데이터를 가지고 있음 관계형 데이터베이스는 SQL을 통해 제어 가능

# 비관계형 데이터: 데이터들간에 관계가 없음 => 데이터들이 연결되어 있지 않음.

테이블의 구성 요소

컬럼(Column)과 레코드(Record)로 구성된 표 모든 테이블은 고유의 이름으로 구분

테이블에서 데이터를 가져오기

SELECT

SELECT column
FROM table;

모든 데이터를 가져오는 방법

SELECT * -- 검색할 컬럼
FROM table;

검색할 데이터에 *을 입력하면 모든 데이터 검색

 

DISTINCT : 뚜렷한, 분명한
뒤에 나오는 컬럼의 중복을 제거하고 보여

DISTINCT 사용시 주의점

DISTINCT 뒤에 2개 이상의 컬럼을 적으면,

한 쪽 컬럼에 중복이 있어도 다른 쪽 컬럼의 값이 다르면 다르게 취급

DISTNICT문의 기본 문법

SELECT DISTINCT column
FROM table;

 

검색할 데이터 앞에 DISTINCT를 입력하여 사용

03. 조건을 추가하여 검색하기

내가 원하는 데이터만 검색하는 방법

조건을 만드는 방법

검색하고자 하는 데이터의 조건을 설정할 수 있는 명령

WHERE

SELECT/WHERE문의 기본 문법

SELCET *
FROM table
WHERE 조건

04. 여러 개의 조건을 추가하기

비교 연산자

연산자	연산의 예	의미
>, <	N < 10 N >10	N이 10 미만 N이 10 초과
>= , <=	N <= 10 N >= 10	N이 10 이하 N이 10 이상
=	N = 10	N이 10인 값
!=	N != 10	N이 10이 아닌 값

비교 연산자를 사용하여 검색

SELECT * -- 검색할 컬럼
FROM score -- 테이블
WHERE math >= 90; -- 조건

 

score 테이블에서 수학(math) 성적이 90 이상인 값 검색

복합조건 연산자

연산자	연산의 예	의미
AND, &&	A AND B A && B	A 그리고 B를 모두 만족하는 값
OR, ||	A OR B A || B	A 또는 B인 값
NOT, !	NOT A !A	A가 아닌 값

복합조건 연산자를 사용하여 검색

SELECT *
FROM score
WHERE english >= 90 OR science > 80;

score 테이블에서 영어(english) 성적이 90 이상이거나 과학(science) 성적이 80 초과인 값 검색

기타 연산자

연산자	연산의 예	의미
BETWEEN	A BETWEEN 10 AND 20	A가 10과 20 사이에 포함된 값
IN	A IN B	B에 A가 포함된 값
NOT IN	A NOT IN B	B에 A가 포함되지 않은 값

기타 연산자를 사용하여 검색

SELECT *
FROM score
WHERE science BETWEEN 80 AND 90;

score 테이블에서 science 성적이 80과 90사이의 값 검색

---

2. 데이터를 제어하는 DML (데이터 조작어)

01. 데이터에서 유사한 값 찾기

LIKE

특정 문자가 포함된 문자열을 찾고 싶을 때 사용하는 명령

LIKE 조건의 기본 문법

SELECT column
FROM table
WHERE colunn LIKE %

where + column + like + ‘%데이터’ or ‘데이터%’ or ‘%데이터%’
% : 와일드카드

02. 데이터 정렬하기

ORDER BY

데이터를 검색할 때 정렬하여 결과를 출력하는 명령어

cf) 엑셀 텍스트 오름차순/내림차순 정렬

SELECT 검색할 컬럼
FROM 테이블이름
ORDER BY 컬럼 DESC; -- 정렬조건

테이블에서 컬럼 값이 높은 데이터부터 정렬하여 검색

SELECT 검색할 컬럼
FROM 테이블이름
ORDER BY 컬럼 ASC; -- 정렬조건

 

테이블에서 컬럼 값이 낮은 데이터부터 정렬하여 검색

ASC = 오름차순 (작은 값부터) DESC = 내림차순 (큰 값부터)

03. 테이블에 데이터 삽입하기

새로운 데이터 추가하기

INSERT

관계형 데이터 베이스의 테이블에 값을 저장하는 명령

INSERT INTO 테이블(컬럼, 컬럼, 컬럼,...)
VALUES(추가할 데이터, 데이터 데이터,...);

컬럼 수 = values 수

컬럼을 명시하지 않으면 순서대로 값 삽입

04. 테이블의 데이터 수정하기

UPDATE

관계형 데이터베이스의 테이블에서 이미 저장된 값을 수정하는 명령

UPDATE 테이블
SET 컬럼이름 = '변경할 값' -- 변경할 값(미래)
WHERE 컬럼이름 = '현재 값'; -- 조건(현재)

05. 테이블의 데이터 삭제하기

DELETE

관계형 데이터베이스의 테이블에서 이미 저장된 값을 삭제하는 명령

DELETE
FROM 테이블이름
WHERE 컬럼이름 = '컬럼 값'; -- 조건

DELETE
FROM 테이블이름

WHERE 조건이 없을 경우 모든 데이터 삭제

---

3. SQL과 함수

01. COUNT

COUNT

검색한 결과의 데이터의 개수를 가져오는 내장함수로 

NULL인 데이터는 제외

SELECT COUNT(검색할 컬럼) 
FROM 테이블 이름;

테이블안에 있는 컬럼의 개수 검색

SELECT COUNT(*) FROM 테이블 이름;

검색할 데이터에 *을 입력하면 모든 데이터 검색

02. LIMIT

LIMIT

테이블에서 출력하고자 하는 데이터의 개수를 제한하는 명령

SELECT 컬럼 
FROM 테이블 이름
LIMIT 제한할 숫자;

테이블에서 데이터를 제한숫자 만큼 가져오기

SELECT 컬럼 
FROM 테이블 이름
LIMIT num, 제한할 숫자;

테이블에서 데이터를 num+1번째 데이터부터 제한숫자만큼 가져오기

첫 번째 컬럼의 시작은 0이며, 즉, ‘LIMIT 1, 5’는 2번째 컬럼부터 5개를 가져오라는 의미

03. SUM & AVG

SUM

지정한 컬럼들의 값을 모두 더하여 총점을 구해주는 내장함수

SELECT SUM(검색할 컬럼) 
FROM 테이블 이름;

 

SUM을 이용해 원하는 데이터의 합을 구할

AVG

지정한 컬럼들의 평균값을 구해주는 내장함수

SELECT AVG(평균을 구한 컬럼) 
FROM 테이블 이름;

원하는 데이터의 평균을 구하며, 평균을 구할 컬럼이 여러개일 경우, 복수 기입 가능

04.MAX&MIN

MAX

테이블에 존재하는 데이터에서 최대값을 가져오는 내장함수 (숫자형 뿐만 아니라 문자형도 가능)

SELECT MAX(검색할 컬럼) 
FROM 테이블 이름;

MIN

테이블에 존재하는 데이터에서 최솟값을 가져오는 함수 (숫자형뿐만 아니라 문자형도 가능)

SELECT MIN(검색할 컬럼)
FROM 테이블 이름;

---

4. 다수의 테이블 제어하기

01. 데이터 그룹 짓기

GROUP BY

컬럼 유형별로 개수를 알고 싶을 때 컬럼에 데이터를 그룹화하는 함수

 

GROUP BY 사용시 두 가지 특징

특정 컬럼을 그룹화 하는 GROUP BY

특정 컬름을 그룹화한 결과에 조건을 거는 HAVING

- WHERE은 그룹화 하기 전, HAVING은 그룹화 후 조건

 

테이블에서 검색할 컬럼과 같은 데이터끼리 그룹화

SELECT 컬럼, 원하는 결과를 위한 함수(*)
FROM 테이블
GROUP BY 그룹화할 컬럼;

 

조건 처리 후에 컬럼 그룹화

SELECT 컬럼 
FROM 테이블 
WHERE 조건식 
GROUP BY 그룹화할 컬럼;

컬럼 그룹화 후 조건 처리 (GROUP BY / HAVING 절)

SELECT 컬럼 
FROM 테이블 
GROUP BY 그룹화할 컬럼 
HAVING 조건식;

조건 처리 후 컬럼 그룹화 후 조건 처리 (GROUP BY / HAVING 절)

SELECT 컬럼 
FROM 테이블 
WHERE 조건식 
GROUP BY 그룹화할 컬럼 
HAVING 조건식;

ORDER BY가 존재하는 경우

SELECT 컬럼 FROM 테이블 [WHERE 조건식]
GROUP BY 그룹화할 컬럼 [HAVING 조건식] ORDER BY 컬럼1 [, 컬럼2, 컬럼3 ...];

GROUP BY 활용

SUM, AVG, COUNT, MAX, MIN를 활용

SELECT 컬럼, SUM(컬럼명) FROM 테이블 GROUP BY 컬럼; 
-- 컬럼이 같은 열에서 컬럼의 내용을 다 더한 값을 출력
SELECT 컬럼, AVG(컬럼명) FROM 테이블 GROUP BY 컬럼; 
-- 컬럼이 같은 열의 컬럼의 평균을 출력
SELECT 컬럼, MAX(컬럼명) FROM 테이블 GROUP BY 컬럼; 
-- 컬럼이 같은 열중에서 해당 컬럼명이 가장 큰 값을 출력
SELECT 컬럼, MIN(컬럼명) FROM 테이블 GROUP BY 컬럼;
-- 컬럼이 같은 열중에서 해당 컬럼명이 가장 작은 값을 출력

02. 두개의 테이블에서 조회

q. 테이블을 하나로 관리하면 되지 않는가?
1. 테이블 하나의 용량이 크면 관리하기 어별고, 분석하는 사람이 힘들기 때문에 테이블의 크기나 기능에 따라 분류
2. 우리나라 데이터 법상 한 사람을 특정할 수 있는 모든 데이터를 하나에 테이블에 담을 수 없다.

INNER JOIN

SELECT 컬럼
FROM 테이블
INNER JOIN 연결할 테이블;

JOIN에 조건 적용

두 테이블의 정보 중 같은 정보를 가리키는 컬럼끼리 연결

SELECT 컬럼
FROM 테이블
INNER JOIN 연결할 테이블
ON 연결할 조건 컬럼;

LEFT JOIN

왼쪽 테이블의 모든 값을 포함하여 연결하기

SELECT 컬럼
FROM 테이블
LEFT JOIN 연결할 테이블
ON 조건; -- 같은 조건을 가진 컬럼

기준 테이블을 모두 출력하되,

모든 기준 테이블의 조건 컬럼과 연결할 테이블의 조건 컬럼이 겹치도록 합친다.

RIGHT JOIN

오른쪽 테이블의 모든 값을 포함하여 연결하기

SELECT 컬럼
FROM 테이블
RIGHT JOUN 연결할 테이블
ON 조건;

기준 테이블을 모두 출력하되,

모든 연결할 테이블의 조건 컬럼과 기준 테이블의 조건 컬럼이 겹치도록 합친다.

INNER JOIN: 두 데이터 중 겹치는 부분만 출력

LEFT JOIN: 왼쪽 데이터와 겹치는 부분을 출력

RIGHT JOIN: 오른쪽 데이터와 겹치는 부분을 출력

---

5. 서브쿼리

01. 서브쿼리

서브쿼리

하나의 쿼리 안에 포함된 또 하나의 쿼리

메인 쿼리가 서브쿼리를 포함하는 종속적인 관계

서브쿼리의 특징

• 알려지지 않은 기준을 이용한 검색에 유용
• 메인 쿼리가 실행되기 이전에 한 번만 실행
• 한문장에서 여러번 사용 가능

select
(2) 서브쿼리
(1) 메인쿼리

서브쿼리에 우선순위가 있어서 메인쿼리보다 먼저 수행한다.

SELECT 컬럼
FROM 테이블
WHERE 조건 연산자
(SELECT 컬럼 FROM 테이블 WHERE 조건); -- 서브쿼리

서브쿼리 사용 시 주의사항

1. 서브쿼리는 괄호와 함께 사용되어야 한다.
2. 서브쿼리 안에서 ORDER BY 절은 사용할 수 없다.
3. 서브쿼리는 연산자의 오른쪽에 사용되어야 한다.
4. 서브쿼리는 오로지 SELECT문으로만 작성 할 수 있다.

02. 반환에 따른 분류

단일 행 서브쿼리

결과가 한 행만 나오는 서브쿼리 서브쿼리가 결과를 1개의 값만 반환하고,

이 결과를 메인쿼리로 전달하는 쿼리

 

단일 행 서브쿼리 기본 문법

SELECT 컬럼
FROM 테이블
WHERE 조건 연산자
(SELECT 컬럼 FROM 테이블 WHERE 조건); -- 서브쿼리

 

한 개의 행만 반환 = 단일 행

단일행 서브쿼리 연산자

기호	뜻
=	같다
<>	같지 않다
>	크다
>=	크거나 같다
<	작다
<=	작거나 같다

다중 행 서브쿼리

서브쿼리가 결과를 2개 이상 반환하고, 이 결과를 메인쿼리로 전달하는 쿼리

다중 행 서브쿼리 문법 예시

SELECT 컬럼 FROM 테이블
WHERE 조건 IN (
SELECT 컬럼 FROM 테이블 GROUP BY 그룹화할 조건);

다중 행 연산자

기호	뜻
IN	하나라도 만족하면 반환
ANY	하나라도 만족하면 반환 비교 연산 가능
ALL	모두 만족하면 반환 연산 가능

< any 최대값.     > any 최소값.

>all 최대값 <all 최소값

03. 위치에 따른 분류

스칼라 서브쿼리

SELECT 절에서 사용하는 서브쿼리 스칼라 서브쿼리는 오로지 한 행만 반환

마치 JOIN을 사용한 것과 같은 결과를 나타낸다.

# 데이터가 많은 경우 join보다 결과가 빠르게 추출되는 경우가 있기 때문에 사용

SELECT 컬럼, (
	SELECT 컬럼
    FROM 테이블 as A
    WHERE A.컬럼 = 컬럼
) AS B
FROM 테이블;