DATABASE (1) : DataBase Expression, DataType, MySQL Syntax 요약

DataBase Expression

1. 테이블
   • 데이터베이스에서 데이터를 저장하는 단위
   • 행(row : record)과 열(column : field)로 구성
2. 레코드
   • 관계된 데이터의 묶음 → 하나의 Entity에 대한 정보들을 나타냄
3. 필드
   • 각 Record에서의 특정한 정보를 나타냄
4. 아이템
   • 필드에서 각각의 값을 나타냄

---

Syntax

SELECT

// SELECT {FIELD NAME} FROM {TABLE NAME}
SELECT age, gender FROM POPULATION;

 

WHERE

// SELECT * FROM TABLE WHERE {조건 1} ANR {조건 2} ...

SELECT * FROM POPULATION WHERE age >= 20 AND gender = "m";

 

Operator

// 덧셈

SELECT 123 + 45    // 168

SELECT 'ASDF' + 123    // 123    <-- 문자는 0 취급

SELECT OrderID, ProductID, OrderID + ProductID FROM OrderDetails;

// OrderID    ProductID    OrderID + ProductID
//    1           11               12
//    2           42               44
//    ...

 

AND / OR

// 조건문에서 AND
SELECT * FROM Orders WHERE CustomerID > 33 AND EmployeeID < 5;
// CustomerID 값이 33 초과 그리고 EmployeeID 값이 5 미만인 record를 출력

// 조건문에 OR
SELECT * FROM Orders WHERE CustomerID > 33 OR EmployeeID < 5;
// CustomerID 값이 33 초과 또는 EmployeeID 값이 5 미만인 record를 출력

// 둘 다 같이 쓰인다면, AND가 OR보다 우선순위가 높음
// 헷갈리지 않도록 소괄호로 묶어서 할 것

 

AS (Alias)

Alias를 지정하여 특정 필드명을 원하는 필드 이름으로 변경

// SELECT {field} AS {별칭} FROM TABLE
SELECT name AS "이름" FROM MINCO.HONEY

 

ORDER BY

특정 필드를 정렬 - 연결된 Record도 정렬에 따라 같이 움직임

// 오름차순 : SELECT * FROM TABLE ORDER BY {필드명};
// 내림차순 : SELECT * FROM TABLE ORDER BY {필드명} DESC;
SELECT * FROM POPULATION ORDER BY age;

// 여러 열을 기준으로 정렬
SELECT * FROM POPULATION ORDER BY age DESC, name, birth DESC
// age 기준 내림차순, age가 같다면 name 기준 오름차순, name마저 같다면 birth 기준 내림차순 

 

LIMIT

원하는 데이터 개수만큼 가져올 수 있음

// 문법 1 : SELECT * FROM TABLE LIMIT {A}
SELECT * FROM POPULATION ORDER BY 3;    // 3 개만큼 테이블에서 가져와라

// 문법 2 : SELECT * FROM TABLE LIMIT {B}
SELECT * FROM POPULATION ORDER BY 4, 6; // 4개 건너뛰고, 6개를 테이블에서 가져와라

 

BETWEEN

원하는 구간 사이의 Record만 추출

// WHERE 절 다음 입력
// 다음 코드는 동일한 결과를 가져오는 코드

SELECT * FROM CITY WHERE POPULATION >= 500 AND POPULATION <= 900;
SELECT * FROM CITY WHERE POPULATION BETWEEN 500 AND 900;

 

IN

후보들 중 나오는 값을 의미

// WHERE 필드명 IN ( candidate 1, candidate 2, candidate 3, ...)

SELECT * FROM CITY WHERE NAME IN ("Seoul", "Sydney", "Oxford");

 

LIKE

문자열 검색

// % : 다중 문자를 의미
// _ : 한 글자를 의미

SELECT * FROM CITY WHERE Name LIKE "New%";

// Name    CountryCode ...
// NewCastle    AUS
// Newport      GBR
// New Delhi    IND    ...
// ...

SELECT * FROM CITY WHERE CountryCode LIKE "K_R";

// Name    CountryCode ...
// Bikenibeu    KIR
// Seoul        KOR
// Pusan        KOR
// ...

// 즉, LIKE % -> 0 ~ N개   ||   LIKE _ -> 1개

 

GROUP BY

집계 함수 (SUM, AVG, MIN, MAX, COUNT) 를 사용하기 위해 묶음

SELECT CountryCode, count(Population) as "도시 수" FROM CITY
GROUP BY CountryCode;

// CountryCode    도시 수
// ABW            1
// AFG            4
// AGO            5

SELECT ID, SUM(Salary) FROM CITY GROUP BY Depart;

// ID    SUM(Salary)
// 1    5100
// 3    1000
// 2    10500

--------------------------------------------------------------
// ID    Depart    Salary
// 1       A       100
// 2       A       500
// 3       A       1000
// 1       B       5000
// 2       B       10000

 

HAVING

GROUP BY 후 집계된 테이블에 적용하는 조건 절

SELECT ID, SUM(Salary) FROM CITY GROUP BY Depart HAVING SUM(Salary) >= 1000;

// ID    SUM(Salary)
// 3    1000
// 2    10500

 

JOIN

1. INNER JOIN교집합이 되는 각 테이블의 FIELD 명을 ON 또는 WHERE로 명시해 줌
2. 조건에 부합하는 Record만 가지고 오는 것 (교집합)

SELECT * FROM TABLE_1 INNER JOIN TABLE_2 ON TABLE_1.COMP_1 = TABLE_2.COMP_2;

// TABLE_1의 COMP_1과 TABLE_2의 COMP_2가 같은 것만을 Record로 연결해서 가져옴

1. OUTER JOIN
   • ~ A LEFT OUTER JOIN B
   • 왼쪽 테이블(A) 고정, 오른쪽 테이블(B)을 조건에 맞게 A 오른쪽에 붙임
   • ~ A RIGHT OUTER JOIN B
   • 오른쪽 테이블(B) 고정, 왼쪽 테이블(A)을 조건에 맞게 B 오른쪽에 붙임
   • ~ A FULL OUTER JOIN B
   • RIGHT OUTER JOIN의 결과와 LEFT OUTER JOIN의 결과를 모두 합한 모습

// < BASIC SYNTAX >
SELECT * FROM A LEFT OUTER JOIN B ON A.학번 = B.학번

// B 테이블에 A 학번을 갖고 있는 record가 없으면 NULL로 표시되어 덧붙여짐

 

https://hongong.hanbit.co.kr/sql-기본-문법-joininner-outer-cross-self-join/

 

---

CRUD

SQL에서 데이터베이스 작업에 관한 약어로, Create, Read, Update, Delete의 약자

1. Create: 새로운 레코드를 데이터베이스에 추가하는 것
2. Read: 데이터베이스에서 특정 레코드를 읽어오는 것
3. Update: 데이터베이스의 기존 레코드를 수정하는 것
4. Delete: 데이터베이스에서 특정 레코드를 삭제하는 것

 

웹서비스에 사용하는 쿼리

• 로그인
  • SELECT : 사용자가 입력한 아이디와 비밀번호가 데이터베이스에 저장된 정보와 일치하는지 확인
• 회원가입
  • SELECT : 사용자가 입력한 아이디가 이미 데이터베이스에 존재하는지 확인
  • INSERT : 새로운 회원 정보를 데이터베이스에 추가
  • UPDATE : 회원 프로필 정보를 업데이트
• 회원탈퇴
  • DELETE : 데이터베이스에서 회원의 정보를 삭제
  • INSERT를 DELETE 대신 활용 // DELETE를 쓰면 회원 정보 복구가 어렵기 때문
• 게시판은 CRUD 전체를 사용

 

---

데이터 타입

 

숫자형

• int : **-2^31** 이상 **2^31-1** 이하 상수
• float : 소수점을 포함한 수 // 자세한 건 여기를 참고

문자형

• char : 고정 길이 데이터 타입, 빈 공간은 띄어쓰기로 채움 → Type char(5) : “BTS “ex) Type char(3) : 3 Btye 만큼 char 사용
• 길이가 일정한 문자 / 빠른 성능이 필요한, 자주 읽는 필드에 사용
• ex) Type char(35) : 35 Byte 만큼 char 사용
• varchar : 가변 길이 데이터 타입, 빈 공간은 채우지 않음공간을 아낄 수 있지만, 데이터 파편화(data fragmentation) 문제가 있음
• 주소 등 길이 변화가 큰 문자 저장
• ex) Type varchar(20) : 해당 필드에 “BTS” 를 넣으면, 3 Byte만 사용
• text
• 64KB 미만의 긴 문자열에 사용

날짜형

• date
• 날짜를 나타냄 // YYYY-MM-DD
• datetime
• 날짜 + 시간을 나타냄 // YYYY-MM-DD HH:MM:SS

---

// 날짜 함수 다루기

SELECT YEAR("2023-02-12 12:02:14");   // 2023
SELECT MONTH("2023-02-12 12:02:14");  // 2
SELECT DAY("2023-02-12 12:02:14");    // 12

SELECT hour("2023-02-12 12:02:14");   // 12
SELECT minute("2023-02-12 12:02:14"); // 2
SELECT second("2023-02-12 12:02:14"); // 14

 

 

TIMESTAMPDIFF()

두 날짜 또는 시간 간의 차이를 계산하는 데 사용

// 사용법
// TIMESTAMPDIFF(unit, start_date, end_date)

// unit: 두 날짜/시간 값 사이의 차이를 계산하는 데 사용되는 단위
//       ex) SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
// start_date: 시작 날짜/시간 값
// end_date: 끝나는 날짜/시간 값

TIMESTAMPDIFF(DAY, "2020-01-19", "2020-01-21")  // 2 : 반환 값은 정수

 

DATE_ADD() / DATE_SUB()

날짜나 시간 값에 일정 기간을 더하거나 빼는 데 사용

// DATE_ADD(date, INTERVAL value unit)
// DATE_SUB(data, INTERVAL value unit)

// date: 더하려는 날짜나 시간 값
// value: 더하려는 값
// unit: value 매개변수의 단위(ex. DAY, HOUR, MINUTE, SECOND)

SELECT date_add(now(), interval 1 hour);  // 기존에 1시간 더한 값 반환
SELECT date_sub(now(), interval 1 month); // 기존에 1시간 뺀 값 반환

 

IFNULL()

첫 번째 값이 NULL인 경우, 두 번째 값으로 대체해주는 함수

// SELECT IFNULL(column_name, replacement_value) FROM table_name;

SELECT age, IFNULL(name, '#') FROM CITY;

// age    IFNULL(name, '#')
// 15     #
// 21     amanda
// 22     jason
// 16     chick

 

CONCAT()

둘 이상의 문자열 값을 결합하여 하나의 문자열 값을 생성

// SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
// 'users' 테이블에서 'first_name'과 'last_name' 열을 결합하여 'full_name' 열을 생성

| id | first_name | last_name |
|----|------------|-----------|
|  1 | John       | Smith     |
|  2 | Jane       | Doe       |

-->

| full_name   |
|-------------|
| John Smith  |
| Jane Doe    |

 

CONVERT()

타입을 반환하는 함수

// CONVERT(필드명, 변환 Type)

SELECT CONVERT('2017-02-11', DATE); // 문자열을 DATE 형식으로 반환