관계형 데이터베이스 - SQL(Structured Query Language)

1. SQL(Structured Query Language)

• 구조화된 Query 언어이다
• 데이터베이스를 위한 프로그래밍 언어이다
• 데이터베이스에 query를 보내서 원하는 데이터를 가져오거나 삽입할 수 있다
• MySQL, Oracle, SQLite, PostgreSQL 등 다양한 데이터베이스에서 SQL 구문을 사용할 수 있다
• 데이터가 구조화된(structured) 테이블을 사용하는 고정된 데이터베이스 구조에서 활용할 수 있다

 1) Query

• '질의하다'의 의미를 가진다
• 검색창에 입력하는 검색어도 Query의 일종이다
• 저장되어 있는 정보를 필터하기 위한 질의문이다

▶ In-memory
 - 프로그램을 종료하면 데이터가 사라진다
 - 예기치 못한 상황으로부터 데이터를 보호할 수 없다

 - 데이터의 수명이 프로그램의 수명에 의존하게 된다

▶ File I/O

 - 파일을 읽는 방식으로 작동하는 형태를 의미한다

 - 원하는 데이터를 가져오기 위하여 전체 데이터를 가져온 후 필터링을 해야 한다

 - 파일의 크기가 커질수록 비효율적이다

 - 여러개의 파일을 동시에 작업할 경우나 복잡하고 데이터 양이 많은 경우에는 작업이 어렵다

 

▶ Database

 - 필터링 기능 외에 File I/O로 구현하기 어려운 여러가지 관리 기능을 가지고 있는 데이터 특화 서버이다

 - 관계형 데이터베이스에서는 하나의 CSV 파일이나 엑셀 시트를 한 개의 테이블로 저장할 수 있다

 - 한번에 여러 개의 테이블을 가질 수 있기 때문에 SQL 을 활용해 데이터를 불러오기 쉽다

 

2. NoSQL(No Structured Query Language)

• 데이터의 구조가 고정되어 있지 않은 데이터베이스 구조에서 활용할 수 있다
• 테이블을 사용하지 않고 데이터를 다른 형태로 저장한다
• MongoDB 와 같은 문서 지향 데이터베이스에 사용한다

3. 기본 Query 명령어

Select	데이터 테이블을 선택한다	// SELECT 테이블필드이름1, 테이블필드이름2    FROM 테이블_이름;
Where
And, Or, Not
Order By
Insert Into	데이터를 삽입한다
Null Values
Update	데이터를 변경, 수정 한다
Delete
Count
Like
Wildcards
Aliases
Joins	Inner Join Left Join Right Join
Group By

 

4. Database 관련 용어

• 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

5. Database 관련 명령어

• 데이터베이스 생성
  // CREATE DATABASE 데이터베이스_이름 ;
• 데이터베이스 사용 : 데이터베이스를 이용하여 테이블 생성, 수정, 삭제 등의 작업을 하기 전에 사용 명령을 전달한다
  // USE 데이터베이스_이름 ;
• 테이블 생성
  // CREATE TABLE 테이블_이름

ex) 다음의 조건을 가지는 user 테이블 생성

 

필드 이름	필드 타입	그 외 속성
id	숫자	Primary key이면서 자동 증가되도록 설정
name	문자열(최대 255개 문자)
email	문자열(최대 255개 문자)

1 CREATE TABLE user (
2      id int PRIMARY KEY AUTO_INCREMENT,
3      name varchar(255),
4      email varchar(255),
5  );

• 테이블 정보 확인
  // DESCRIBE 테이블명 ;
• 데이터베이스 삭제
  // DROP DATABASE 데이터베이스_이름
• 테이블 삭제
  // DROP TABLE 테이블_이름
• 데이터베이스 전체 백업
  // BACKUP DATABASE 데이터베이스_이름
     TO DISK = 백업할 디렉토리_이름
• 기존 테이블의 열 추가, 삭제, 수정
  1) 테이블에 열 추가
  // ALTER TABLE 테이블_이름
     ADD 열_이름 데이터_형식
  
  2) 테이블에 열 삭제
  // ALTER TABLE 테이블_이름
     DROP COLUMN 열_이름 
  
  3) 테이블의 열 변경
  // ALTER TABLE 테이블_이름
     ALTER COLUMN 열_이름 데이터_형식
• 테이블 데이터 삭제
  //TRUNCATE TABLE 테이블_이름

6. SQL 명령어

SELECT	- 데이터베이스에서 데이터를 선택할 때 사용 // SELECT 'hello world'    : 일반 문자열 // SELECT 2                  : 숫자 // SELECT 10+5             : 일반 연산	// SELECT 테이블필드이름1, 테이블필드이름2    FROM 테이블_이름
FROM	- 테이블과 관련한 작업 시 반드시 입력해야 한다 - FROM 뒤에는 결과를 도출할 데이터베이스 테이블을 명시한다	1) 특정 특성을 테이블에서 사용    // 1 SELECT 특성_1       2 FROM 테이블_이름 2) 여러 개의 특성을 테이블에서 사용    // 1 SELECT 특성_1, 특성_2       2 FROM 테이블_이름 3) 테이블의 모든 특성을 사용    (* 는 wildcard로 전체를 선택할 때 사용된다)    // 1 SELECT *       2 FROM 테이블_이름
WHERE	- 필터 역활을 하는 쿼리문이다 - 선택적으로 사용할 수 있다	1) 특정 값과 동일한 데이터 찾기    // 1 SELECT 특성_1, 특성_2       2 FROM 테이블_이름       3 WHERE 특성_1 = "특정 값" 2) 특정 값을 제외한 값을 찾기    // 1 SELECT 특성_1, 특성_2       2 FROM 테이블_이름       3 WHERE 특성_2 < > "특정 값" 3) 특정 값보가 크거나 작은 값을 필터하는 경우    // 1 SELECT 특성_1, 특성_2       2 FROM 테이블_이름       3 WHERE 특성_1 < "특정 값"    // 1 SELECT 특성_1, 특성_2       2 FROM 테이블_이름       3 WHERE 특성_1 > "특정 값" 4) 특정 값을 포함하는 이상, 이하 값을 필터하는 경우    // 1 SELECT 특성_1, 특성_2       2 FROM 테이블_이름       3 WHERE 특성_1 < = "특정 값"    // 1 SELECT 특성_1, 특성_2       2 FROM 테이블_이름       3 WHERE 특성_1 > = "특정 값" 5) 리스트에 있는 값과 일치하는 데이터를 필터하는 경우    // 1 SELECT 특성_1, 특성_2       2 FROM 테이블_이름       3 WHERE 특성_1 IN ("특정값_1", "특정값_2") 6) 리스트에 있는 값과 유사한 값을 필터하는 경우    // 1 SELECT 특성_1, 특성_2       2 FROM 테이블_이름       3 WHERE 특성_1 LIKE "%특정 ANSWKDUF%" 7) 값이 없는 경우를 찾기    // 1 SELECT *       2 FROM 테이블_이름       3 WHERE 특성_1 IS NULL 8) 값이 없는 경우를 제외하고 찾기    // 1 SELECT *       2 FROM 테이블_이름       3 WHERE 특성_1 IS NOT NULL
ORDER BY	- 돌려받는 데이터 결과의 정렬 기준을 결정한다 - 선택적으로 사용할 수 있다	1) 오름차순 정렬    // 1 SELECT *       2 FROM 테이블_이름       3 ORDER BY 특성_1 2) 내림차순 정렬    // 1 SELECT *       2 FROM 테이블_이름       3 ORDER BY 특성_1 DESC
LIMIT	- 결과로 출력할 데이터의 갯수를 지정한다 - 선택적으로 사용할 수 있다 - 쿼리문에서 사용할 경우 가장 마지막에 추가한다	// 1 SELECT *    2 FROM 테이블_이름    3 LIMIT 200
DISTINCT	- 유니크한 값을 도출할 경우 사용한다 - SELECT와 함께 사용한다	1) 특성_1을 기준으로 유니크한 값을 선택    // 1 SELECT DISTINCT 특성_1        2 FROM 테이블_이름 2) 특성_1, 특성_2, 특성_3의 유니크한 값을 조합하여 선택    // 1 SELECT       2     DISTINCT       3          특성_1       4          , 특성_2       5          , 특성_3       6     FROM 테이블_이름
INNER JOIN	- INNER JOIN 또는 JOIN 으로 실행할 수 있다 - 두 개 이상의 테이블에서 일치하는 레코드 선택	SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
OUTER JOIN	- 모든 레코드 선택 : FULL JOIN - 왼쪽 테이블의 모든 레코드와 일치하는 레코드 선택 : LEFT JOIN -오른쪽 테이블의 모든 레코드와 일치하는 레코드 선택 : RIGHT JOIN	SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition; SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
INSERT INTO	- 테이블에 새 레코드를 삽입한다	// 1 INSERT INTO 테이블_이름(열1, 열2, 열3,...)    2 VALUES (값1, 값2, 값3,...)
UPDATE	- 테이블의 기존 레코드를 수정한다	// 1 UPDATE 테이블_이름    2 SET 열1 = 값1, 열2 = 값2, 열3 = 값3,...    3 WHERE 수정_대상명
DELETE	- 테이블의 기존 레코드를 삭제한다	// 1 DELETE FROM 테이블_이름    2 WHERE 수정_대상명
MIN( ) / MAX( )	- 선택한 열의 가장 작거나 큰 값을 반환한다	// 1 SELECT MIN(열_이름) AS 조건    2 FROM 테이블_이름    3 WHERE 수정_대상명 // 1 SELECT MAX(열_이름) AS 조건    2 FROM 테이블_이름    3 WHERE 수정_대상명
COUNT( )	- 지정된 기준과 일치하는 행 수를 반환한다	SELECT COUNT(column_name) FROM table_name WHERE condition;
AVG( )	숫자 열의 평균 값을 반환한다	SELECT AVG(column_name) FROM table_name WHERE condition;
SUM( )	숫자 열의 합계를 반환한다	SELECT SUM(column_name) FROM table_name WHERE condition;
LIKE	- 열에서 지정된 패턴을 검색한다 - AND, OR 연산자와 사용하여 여러 조건을 결합할 수 있다  - %, _ 를 사용하여 PATTEN을 조합할 수 있다	SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;
WILDCARD	- 와일드카드 문자는 연산자와 결합하여 다양하게 사용된다 - LIKE 사용 예시
IN	WHERE문에 여러 값을 지정할 수 있다	SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
BETWEEN	- 주어진 범위 내에서 값을 선택한다 - 시작과 종료 값이 포함된다	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
AS	- 테이블이나 열에 임시 이름을 지정한다 - 임시 이름은 쿼리가 실행되는 동안에만 존재한다	1) 열 SELECT column_name AS alias_name FROM table_name; 2) 테이블 SELECT column_name(s) FROM table_name AS alias_name;
UNION	- 두개 이상의 테이블에서 공통된 값을 선택한다 - 고유한 값만 반환된다	SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
UNION ALL	- 두개 이상의 테이블에서 공통된 값을 선택한다 - 중복된 값을 포함하여 반환된다	SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
GROUP BY	1) 각 Country의 CustomerID의 개수 SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; 2) 정렬된 각 Country의 CustomerID의 개수 SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC; 3) 주문 수를 나열 SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName;	SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
HAVING	GROUP BY로 조회한 결과를 필터링 한다

• ELECT 실행 순서
  - 데이터를 조회하는 SELECT 문은 정해진 순서대로 동작한다
  - FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY
  - 예시로 확인해 본다

SELECT CustomerId, AVG(Total)
FROM invoices
WHERE CustomerId >= 10
GROUP BY CustomerId
HAVING SUM(Total) >= 30
ORDER BY 2

 

7. SQL 제약조건

• NOT NULL - 컬럼이 NULL 값을 가질 수 없도록 보장
• UNIQUE - 열의 모든 값이 서로 다른지 확인
• PRIMARY KEY - NOT NULL와 의 조합 UNIQUE. 테이블의 각 행을 고유하게 식별
• FOREIGN KEY - 테이블 간의 링크를 파괴하는 작업 방지
• CHECK - 컬럼의 값이 특정 조건을 만족하는지 확인
• DEFAULT - 값이 지정되지 않은 경우 열의 기본값을 설정합니다.
• CREATE INDEX - 데이터베이스에서 데이터를 매우 빠르게 생성하고 검색하는 데 사용

 

※ 참조 링크

▶ SQL 명령어 : https://www.w3schools.com/sql/default.asp

SQL Tutorial

▶ SQL 연습문제 :  https://www.w3schools.com/sql/exercise.asp?filename=exercise_select1 

W3Schools SQL Exercise