관계형 Database

Jelkov Ahn·2021년 12월 16일
schema designsql
0

관계형 데이터베이스

목록 보기
1/1
post-thumbnail

소개

백엔드 개발에서 매우 중요한 데이터베이스를 학습합니다.

  • SQL 문법
  • 스키마 디자인 (Schema design)
  • Node.js에서 데이터베이스를 사용하는 방법

Learn SQL, Designing Schema
SQL(Structured Query Language)을 학습하면, 관계형 데이터베이스를 자유자재로 다룰 수 있습니다. 대표적인 관계형 데이터베이스(RDBMS)인 MySQL로 Schema를 설계하고, SQL을 사용하여 데이터를 영속성있게(persistently) 저장하는 방법을 학습합니다.

Achievement Goals

  • 3 Tier Architecture 를 이해한다.
  • 영속성의 개념을 이해하고, 데이터베이스의 필요성을 인지한다.
  • 데이터베이스 종류를 이해한다.
    • 관계형 데이터베이스와 NoSQL의 차이를 이해한다.
    • 관계형 데이터베이스 및 NoSQL이 어떤 경우에 적합한지 이해한다.

Learn SQL

  • SQL 주요 문법을 이해할 수 있다.
    • 조회, 삽입, 갱신, 삭제 구문을 자유자재로 사용할 수 있다.
    • 조회 시 다양한 조건을 걸어 원하는 정보만 조회할 수 있다.
    • 통계를 위한 쿼리를 만들 수 있다.
  • 스키마 디자인을 할 수 있다.
    • 앱에 필요한 테이블과 필드, 그리고 관계를 부여할 수 있다.
  • 1:N, N:N 관계를 이해하고, 데이터베이스에서 테이블을 조작할 수 있다.
    • Foreign Key, Primary Key에 대해 이해할 수 있다.

SQL 소개

SQL은 구조화된 쿼리 언어입니다.

소개

하나의 언어인 Structured Query Language (SQL)은 데이터베이스 언어로, 주로 관계형 데이터베이스에서 사용합니다. 예를 들어 MySQL, Oracle, SQLite, PostgreSQL 등 다양한 데이터베이스에서 SQL 구문을 사용할 수 있습니다.

SQL이란 데이터베이스 용 프로그래밍 언어입니다.
데이터베이스에 쿼리를 보내 원하는 데이터를 가져오거나 삽입할 수 있습니다.
그리고 이름에서 유추할 수 있듯이,
SQL은 (relation 이라고도 불리는) 데이터가 구조화된(structured) 테이블을 사용하는 데이터베이스에서 활용할 수 있습니다.

SQL을 사용할 수 있는 데이터베이스와 달리, 데이터의 구조가 고정되어 있지 않은 데이터베이스를 NoSQL이라고 합니다.
관계형 데이터베이스와는 달리, 테이블을 사용하지 않고 데이터를 다른 형태로 저장합니다.
NoSQL의 대표적인 예시는 MongoDB 와 같은 문서 지향 데이터베이스입니다.

이처럼 데이터베이스 세계에서 SQL은 데이터베이스 종류를 SQL이라는 언어 단위로 분류할 정도로 중요한 자리를 차지하고 있습니다. 그리고 SQL을 사용하기 위해서는 데이터가 구조가 고정되어 있어야 합니다.

SQL vs. NoSQL

SQL(구조화 쿼리 언어) vs. NoSQL(비구조화 쿼리 언어)

데이터베이스는 크게 관계형 데이터베이스와 비관계형 데이터베이스로 구분합니다.

관계형 데이터베이스는 SQL
비관계형 데이터베이스는 NoSQL로 데이터를 다룹니다.

SQL과 NoSQL은

  • 만들어진 방식
  • 저장하는 정보의 종류
  • 저장하는 방법 등에 차이가 있습니다.

SQL 관계형 데이터베이스

  • 테이블의 구조와 데이터 타입 등을 사전에 정의하고,
  • 테이블에 정의된 내용에 알맞은 형태의 데이터만 삽입할 수 있습니다.
  • 행(row)과 열(column)로 구성된 테이블에 데이터를 저장합니다.

    각 열은 하나의 속성에 대한 정보를 저장하고, 행에는 각 열의 데이터 형식에 맞는 데이터가 저장됩니다.
    특정한 형식을 지키기 때문에, 데이터를 정확히 입력했다면 데이터를 사용할 때에는 매우 수월합니다.
    관계형 데이터베이스에서는 SQL을 활용해 원하는 정보를 쿼리할 수 있습니다.
    이 말은 관계형 데이터베이스에서는 스키마가 뚜렷하게 보인다는 말과 같습니다.
    다시 말해, 관계형 데이터베이스에서는 테이블 간의 관계를 직관적으로 파악할 수 있습니다.

대표적인 관계형 데이터베이스는 MySQL, Oracle, SQLite, PostgresSQL, MariaDB 등이 있습니다.

SQL 기반의 관계형 데이터베이스를 사용하는 케이스

    1. 데이터베이스의 ACID 성질을 준수해야 하는 경우
    • ACID :ACID는 데이터베이스 내에서 일어나는 하나의 트랜잭션(transaction)의 안전성을 보장하기 위해 필요한 성질입니다.
      • Atomicity: 원자성은 하나의 트랜잭션에 속해있는 모든 작업이 전부 성공하거나 전부 실패해서 결과를 예측할 수 있어야 합니다. 하나의 단위로 묶여있는 여러 작업이 부분적으로 실행된다면, 업데이트가 일어났지만 누가 업데이트했는지 모르거나, 업데이트 날짜가 누락되는 등 데이터가 오염될 수 있습니다. 예를 들어 계좌이체를 할 때에는 다음과 같은 두 단계가 있습니다.
      • Consistency: 두 번째는 데이터베이스의 상태가 일관되어야 한다는 성질입니다. 하나의 트랜잭션 이전과 이후, 데이터베이스의 상태는 이전과 같이 유효해야 합니다. 다시 말해, 트랜잭션이 일어난 이후의 데이터베이스는 데이터베이스의 제약이나 규칙을 만족해야 한다는 뜻입니다.
      • Isolation: Isolation(격리성) 은 모든 트랜잭션은 다른 트랜잭션으로부터 독립되어야 한다 는 뜻입니다.실제로 동시에 여러 개의 트랜잭션들이 수행될 때, 각 트랜잭션은 고립(격리) 되어 있어 연속으로 실행된 것과 동일한 결과를 나타냅니다.
      • Durability: Durability(지속성)는 하나의 트랜잭션이 성공적으로 수행되었다면, 해당 트랜잭션에 대한 로그가 남아야 합니다. 만약 런타임 오류나 시스템 오류가 발생하더라도, 해당 기록은 영구적이어야 한다는 뜻입니다.
    1. 소프트웨어에 사용되는 데이터가 구조적이고 일관적인 경우

NoSQL

  • 주로 데이터가 고정되어 있지 않은 데이터베이스를 가리킵니다.
  • NoSQL이 SQL과 반대되는 개념처럼 사용된다고 해서, NoSQL에 스키마가 반드시 없는 것은 아닙니다.
  • 관계형 데이터베이스에서는 데이터를 입력할 때 스키마에 맞게 입력해야 하는 반면, NoSQL에서는 데이터를 읽어올 때 스키마에 따라 데이터를 읽어 옵니다.('schema on read')
  • 읽어올 때에만 데이터 스키마가 사용된다고 하여, 데이터를 쓸 때 정해진 방식이 없다는 의미는 아닙니다. 데이터를 입력하는 방식에 따라, 데이터를 읽어올 때 영향을 미칩니다.

대표적인 NoSQL은 몽고DB, Casandra 등이 있습니다.

NoSQL 기반의 비관계형 데이터베이스를 사용하는 케이스

    1. 데이터의 구조가 거의 또는 전혀 없는 대용량의 데이터를 저장하는 경우
    1. 클라우드 컴퓨팅 및 저장 공간을 최대한 활용하는 경우
    1. 빠르게 서비스를 구축하는 과정에서 데이터 구조를 자주 업데이트하는 경우

SQL 기반의 데이터베이스와 NoSQL 데이터베이스의 차이점

데이터 저장(Storage)

  • NoSQL은 key-value, document, wide-column, graph 등의 방식으로 데이터를 저장합니다.
  • 관계형 데이터베이스는 SQL을 이용해서 데이터를 테이블에 저장합니다. 미리 작성된 스키마를 기반으로 정해진 형식에 맞게 데이터를 저장해야 합니다.

스키마(Schema)

  • SQL을 사용하려면, 고정된 형식의 스키마가 필요합니다. 다시 말해, 처리하려는 데이터 속성별로 열(column)에 대한 정보를 미리 정해두어야 합니다. 스키마는 나중에 변경할 수 있지만, 이 경우 데이터베이스 전체를 수정하거나 오프라인(down-time)으로 전환할 필요가 있습니다.
  • NoSQL은 관계형 데이터베이스보다 동적으로 스키마의 형태를 관리할 수 있습니다. 행을 추가할 때 즉시 새로운 열을 추가할 수 있고, 개별 속성에 대해서 모든 열에 대한 데이터를 반드시 입력하지 않아도 됩니다.

쿼리(Querying)

  • 쿼리는 데이터베이스에 대해서 정보를 요청하는 질의문입니다. 관계형 데이터베이스는 테이블의 형식과 테이블 간의 관계에 맞춰 데이터를 요청해야 합니다. 그래서 정보를 요청할 때, SQL과 같이 구조화된 쿼리 언어를 사용합니다.
  • 비관계형 데이터베이스의 쿼리는 데이터 그룹 자체를 조회하는 것에 초점을 두고 있습니다. 그래서 구조화되지 않은 쿼리 언어로도 데이터 요청이 가능합니다. UnQL(UnStructured Query Language)이라고 말하기도 합니다.

확장성(Scalability)

  • 일반적으로 SQL 기반의 관계형 데이터베이스는 수직적으로 확장합니다. 높은 메모리, CPU를 사용하는 확장이라고도 합니다. 데이터베이스가 구축된 하드웨어의 성능을 많이 이용하기 때문에 비용이 많이 듭니다. 여러 서버에 걸쳐서 데이터베이스의 관계를 정의할 수 있지만, 매우 복잡하고 시간이 많이 소모됩니다.
  • NoSQL로 구성된 데이터베이스는 수평적으로 확장합니다. 보다 값싼 서버 증설, 또는 클라우드 서비스 이용하는 확장이라고도 합니다. NoSQL 데이터베이스를 위한 서버를 추가적으로 구축하면, 많은 트래픽을 보다 편리하게 처리할 수 있습니다. 그리고 저렴한 범용 하드웨어나 클라우드 기반의 인스턴스에 NoSQL 데이터베이스를 호스팅 할 수 있어서, 수직적 확장보다 상대적으로 비용이 저렴합니다.

쿼리문

(1) Select(SELECT는 데이터셋에 포함될 특성을 특정합니다.)

From (테이블과 관련한 작업을 할 경우 반드시 입력해야 합니다. FROM 뒤에는 결과를 도출해낼 데이터베이스 테이블을 명시합니다.)

Q) Insert the missing statement to get all the columns from the Customers table.
A) SELECT * FROM Customers;

Q) Write a statement that will select the City column from the Customers table.
A) SELECT CITY FROM Customers;

Q)Select all the different values from the Country column in the Customers table.
A) SELECT DISTINCT Country FROM Customers;

DISTINCT : 유니크한 값을 받고 싶을 때에는 SELECT DISTINCT 를 사용할 수 있습니다.

특성_1을 기준으로 유니크한 값들만 선택합니다.
SELECT DISTINCT 특성_1
FROM 테이블_이름

특성_1, 특성_2, 특성_3의 유니크한 '조합' 값들을 선택합니다.
SELECT
  DISTINCT
    특성_1
    ,특성_2
    ,특성_3
FROM 테이블_이름

(2) Where (필터 역할을 하는 쿼리문입니다. WHERE은 선택적으로 사용할 수 있습니다.) (3) And, Or, Not 

특정 값과 동일한 데이터 찾기
SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_1 = "특정 값"

특정 값을 제외한 값을 찾기
SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_2 <> "특정 값"

특정 값보다 크거나 작은 데이터를 필터할 때에는 '<', '>', 비교하는 값을 포함하는 '이상', '이하' 값은 '<=', '>=' 을 사용합니다.
SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_1 > "특정 값"

SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_1 <= "특정 값"

문자열에서 특정 값과 비슷한 값들을 필터할 때에는 'LIKE'와 '\%' 혹은 '\*' 를 사용합니다.
SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_2 LIKE "%특정 문자열%"

리스트의 값들과 일치하는 데이터를 필터할 때에는 'IN' 을 사용합니다.
SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_2 IN ("특정값_1", "특정값_2")

값이 없는 경우 'NULL' 을 찾을 때에는 'IS' 와 같이 사용합니다.
SELECT *
FROM 테이블_이름
WHERE 특성_1 IS NULL

값이 없는 경우를 제외할 때에는 'NOT' 을 추가해 이용합니다.
SELECT *
FROM 테이블_이름
WHERE 특성_1 IS NOT NULL

Q)Select all records where the City column has the value "Berlin".
A) SELECT * FROM Customers WHERE city = 'Berlin';

Q)Use the NOT keyword to select all records where City is NOT "Berlin".
A) SELECT * FROM Customers WHERE NOT city = 'Berlin';

Q)Select all records where the CustomerID column has the value 32.
A) SELECT * FROM Customers WHERE CustomerID = 32;

Q)Select all records where the City column has the value 'Berlin' and the PostalCode column has the value 12209.
A) SELECT* FROM Customers WHERE city ='Berlin'AND'PostalCode = 12209;

Q)Select all records where the City column has the value 'Berlin' or 'London'.
A) SELECT* FROM Customers WHERE City = 'Berlin' OR City = 'London';

(4) Order By (기본 정렬은 오름차순입니다.)

오름차순 정렬
SELECT *
FROM 테이블_이름
ORDER BY 특성_1

내림차순 정렬
SELECT *
FROM 테이블_이름
ORDER BY 특성_1 DESC

Q)Select all records from the Customers table, sort the result alphabetically by the column City.
A) SELECT * FROM Customers Order By City;

Q)Select all records from the Customers table, sort the result reversed alphabetically by the column City.
A) SELECT * FROM Customers Order By City DESC;

Q)Select all records from the Customers table, sort the result alphabetically, first by the column Country, then, by the column City.
A) SELECT * FROM Customers Order By Country, City;

(5) Insert Into

Q)Insert a new record in the Customers table.
A)
Insert into Customers(
CustomerName,
Address,
City,
PostalCode,
Country
)
Values(
'Hekkan Burger',
'Gateveien 15',
'Sandnes',
'4306',
'Norway');

(6) Null Values

Q)Select all records from the Customers where the PostalCode column is empty.
A) SELECT * FROM Customers WHERE Postalcode is null;

Q)Select all records from the Customers where the PostalCode column is NOT empty.
A) SELECT * FROM Customers WHERE PostalCode is Not null;

(7) Update

Q)Update the City column of all records in the Customers table.
A) Update Cusotmers Set City = 'Oslo';

Q)Set the value of the City columns to 'Oslo', but only the ones where the Country column has the value "Norway".
A) Update Customers Set City = 'Oslo' Where Country = 'Norway';

Q)Update the City value and the Country value.
A) Update Customers Set City = 'Oslo',Country= 'Norway' WHERE CustomerID = 32;

(8) Delete
Q) Delete all the records from the Customers table where the Country value is 'Norway'.
A) DELETE FROM Customers WHERE Country = 'Norway';

Q) Delete all the records from the Customers table.
A) Delete from Customers;

(9) Count
MIN() MAX() AVG()

Q)Use the MIN function to select the record with the smallest value of the Price column.
A) SELECT Min(Price) FROM Products;

Q)Use an SQL function to select the record with the highest value of the Price column.
A) SELECT Max(Price) FROM Products;

Q)Use the correct function to return the number of records that have the Price value set to 18.
A) SELECT Count(*) FROM Products Where Price = 18;

Q)Use an SQL function to calculate the average price of all products.
A) SELECT AVG(Price)FROM Products;

Q)Use an SQL function to calculate the sum of all the Price column values in the Products table.
A) SELECT SUM(Price)FROM Products;

(10) Like

Q)Select all records where the value of the City column starts with the letter "a".
A) SELECT * FROM Customers Where City like 'a%';

Q)Select all records where the value of the City column ends with the letter "a".
A) SELECT * FROM Customers Where City like '%a';

Q)Select all records where the value of the City column contains the letter "a".
A) SELECT * FROM Customers Where City like '%a%';

Q) Select all records where the value of the City column starts with letter "a" and ends with the letter "b".
A) SELECT * FROM Customers Where City like 'a%b';

Q) Select all records where the value of the City column does NOT start with the letter "a".
A) SELECT * FROM Customers Where City NOT like 'a%';

(11) Wildcards
Q)Select all records where the second letter of the City is an "a".
A) SELECT * FROM Customers Where City like '_a%';

Q)Select all records where the first letter of the City is an "a" or a "c" or an "s".
A) SELECT * FROM Customers
WHERE City LIKE '[acs]%';

Q)Select all records where the first letter of the City starts with anything from an "a" to an "f".
A) SELECT * FROM Customers
WHERE City LIKE '[a-f]%';

Q)Select all records where the first letter of the City is NOT an "a" or a "c" or an "f".
A) SELECT * FROM Customers
WHERE City LIKE '[!acf]%';

(12) In /between

Q) Use the IN operator to select all the records where Country is either "Norway" or "France".
A) SELECT * FROM Customers WHERE Country IN
('Norway','France');
리스트의 값들과 일치하는 데이터를 필터할 때에는 'IN' 을 사용합니다.

Q) Use the IN operator to select all the records where Country is NOT "Norway" and NOT "France".
A) SELECT * FROM Customers WHERE COUNTRY NOT IN ('Norway', 'France');

Q) Use the BETWEEN operator to select all the records where the value of the Price column is between 10 and 20.
A) SELECT * FROM Products
WHERE Price Between 10 and 20;

Q) Use the BETWEEN operator to select all the records where the value of the Price column is NOT between 10 and 20.
A) SELECT * FROM Products
WHERE Price not Between 10 and 20;

Q) Use the BETWEEN operator to select all the records where the value of the ProductName column is alphabetically between 'Geitost' and 'Pavlova'.
A) SELECT * FROM Product WHERE ProductName BETWEEN 'Geitost' AND 'Pavlova';

(13) Aliases

Q)When displaying the Customers table, make an ALIAS of the PostalCode column, the column should be called Pno instead.
A) SELECT CustomerName, Address, PostalCode as pno FROM Customers;

Q)When displaying the Customers table, refer to the table as Consumers instead of Customers.
A) SELECT *FROM Customers AS Consumers;

(14) Joins

  • Inner Join
  • Left Join
  • Right Join

Q)Insert the missing parts in the JOIN clause to join the two tables Orders and Customers, using the CustomerID field in both tables as the relationship between the two tables.

A) SELECT * FROM Orders LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Q) Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables.

A) SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Q) Choose the correct JOIN clause to select all the records from the Customers table plus all the matches in the Orders table.

A) SELECT * FROM Orders RIGHT JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

(15)Group By

Q) List the number of customers in each country.
A) SELECT COUNT (CustomerID),Country FROM Customers GROUP BY Country;

Q) List the number of customers in each country, ordered by the country with the most customers first.
A) SELECT COUNT(CustomerID),Country FROM Customers GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

데이터베이스 관련 용어

SQL Create DB
SQL Drop DB
SQL Create Table
SQL Drop Table
SQL Alter Table
SQL Not Null
SQL Unique
SQL Primary Key
SQL Foreign Key
SQL Default
SQL Auto Increment
SQL Dates

profile
Jelkov Ahn
끝까지 ... 가면 된다.

0개의 댓글