데이터베이스와 SQL 기초

데이터베이스란 무엇을 말하는걸까?

데이터란 컴퓨터 안의 기록되어있는 숫자를 의미한다. (0과 1)

이러한 데이터의 집합을 데이터베이스라 말한다.

 

넓은 의미의 데이터베이스는 컴퓨터 안의 기록된 모든 것을 말한다고도 할 수 있겠다.

데이터 베이스의 데이터는 영구 보존되어야 하기에 주기억장치에만 저장된다면 전원을 끄는 순간 데이터가 모두 날아가버릴것이다.

(여기서 주기억장치란 H/W에서의 메모리 (ROM과 RAM이 있다) 중 램이라고 기억하고 넘어가자_ 램은 휘발성 메모리이다.)

따라서 하드디스크 또는 플래시메모리(SSD)와 같은 비휘발성 저장장치에 저장한다. (램에 저장하는 경우도 있다.)

 

데이터베이스를 효율적으로 관리하기 위한 소프트웨어를 데이터베이스 관리 시스템 (DBMS)라고 부른다.

데이터베이스를 왜 DBMS를 이용해 다뤄야할까 이유는 아래와 같다.

생산성 : 어떤 시스템이든 데이터 검색, 추가, 삭제, 갱신과 같은 처리가 이루어지기 마련이며 이같은 기능을 DBMS가 제공한다.

(시스템 구축시 DBMS를 사용하지 않을 수도 있다고 하나, 비용 측면에서 효율적이지 못하다)

기능성 : 복수 유저의 요청 대응, 고속 검색, 확장 등 데이터베이스를 유연하게 사용할 수 있도록 도와준다.

신뢰성 : DBMS를 통해 확장성, 부하 분산을 구현하는데 이를 보통 클러스터 구성 또는 스케일 아웃이라 부른다. 또한 DBMS 데이터 Export(내보내기), Insert(삽입)등의 기능을 갖춰 데이터베이스를 간단하게 백업할 수 있다.

DBMS의 사용 목적은 데이터베이스의 생산성 향상, 기능성, 신뢰성 확보에 있다.

---

SQL이란?

SQL이란 위와 같은 DBMS를 다루기 위한 기본 언어이다.

SQL은 관계형 데이터베이스 관리 시스템 (RDBMS) 에서만 사용할 수 있다. 

 

SQL 명령의 종류

1. DML (Data Manipulation Language) : 데이터 추가 삭제 및 갱신 등 데이터 조작할때 사용하는 SQL의 가장 기본 명령어이다.

 INSERT, UPDATE, DELETE 등이 있다.

2. DDL (Data Definition Language) : 데이터 베이스 객체 라는 데이터 그릇을 이용하여 데이터를 관리하는데 이 객체를 만들거나 삭제하는 명령어이다.

CREATE, ALTER, DROP, SHOW 등이 있다.

3. DCL (Data Control Language) : 데이터를 제어하는 명령어 DCL에는 트랜잭션과 접근 권한을 제어하는 명령어가 포함되어있다.

GRANT, REVOKE 등이 있다.

4. DQL (Data Query Language) : 데이터 질의 명령어로 원래는 SELECT 가 DML에 속해있었으나 90% SELECT를 주력으로 사용하기 때문에 DML에서 분리되어 나온 개념으로 이해해도 좋다.

SELECT 등이 있으며 WHERE , LIKE와 같은 조건문과 함께 사용하기도 한다.

5. TCL (Transation Control Language) : DCL에서 트랜잭션을 제어하는 명령인 COMMIT과 ROLLBACK만을 따로 분리해서 SQL 명령어로 포함시키기도 한다.

COMMIT , ROLLBACK 등이 있다.

https://wtmatter.com/sql-command-types-ddl-dql-dml-dcl-tcl/

 

 

참고 : codermun-log.tistory.com/209

TIL. 95 SQL DDL, DML, DQL

---

데이터 자료형

수치형 데이터 : 1, 2, 3 과 같은 숫자로만 구성된 데이터를 수치형 데이터라 부르며 테이블에서 오른쪽 정렬로 표시된다.

문자열형 데이터 : 사람 이름처럼 임의의 문자로 구성된 데이터를 문자열형 데이터라 테이블에서 왼쪽 정렬로 표시된다.

날짜시간형 데이터 : 생년월일과 같은 데이터를 날짜시간형 데이터라 부르며 왼쪽 정렬로 표시된다.

열은 하나의 자료형만 가질 수 있으며, 수치형의 열에 문자형의 데이터를 저장 할 수 는 없다.

 

NULL : 이라는 데이터는 값이 아무것도 저장되어 있지 않은 상태를 말한다. NULL 이라는 값이 저장되어있는 것이 아니라는 의미이다.

---

위에서 언급한 대로 SQL은 관계형 데이터베이스시스템(RDBMS)에서만 사용할 수 있다.

MySQL 예제를 통해 가장 많이 사용하는 SELECT 와 WHERE, LIKE를 이용한 조건문으로 데이터를 검색하는 방법을 알아보자

 

SELECT

SELECT * FROM users;

애스터리스크(*) 는 모든 열이라는 의미이다.

select * from users;

 

SELECT + WHERE

SELECT *  FROM users WHERE 조건식;

ex) 조건식 = >

korean_name= 'k';  (열/연산자/값)

여기서 열의 이름은 korean_name, 연산자는 = , 값은 문자열 k이다.

select * from users where id=877;

select * from users where korean_name='k';

select * from users where country_id=1;

 

NULL값 검색하기

SELECT * FROM 테이블명 WHERE exam = NULL;  ==> 아무것도 출력되지 않음 (검색불가)

SELECT * FROM 테이블명 WHERE exam IS NULL; ==> NULL 값이 해당하는 raw 출력!

SELECT * FROM 테이블명 WHERE exam IS NOT NULL; ==> NULL 값이 아닌 raw 출력!

(마치 파이썬에서의 == / is 비교 연산자와도 같다.)

---

SQL 기본 연산자

=  : 좌/우 값이 같을 경우 True

<> : 좌/우 값이 같지 않을 경우 True

>, <, >=, <= 같은 연산자도 물론 사용할 수 있다.

---

SELECT + WHERER + AND , OR, NOT

보통 데이터를 검색할 때 단순한 조건식을 넣으면 많은 값이 반환되기 때문에 한눈에 알아보기 어렵다. 그래서 통상적으로 2개 이상의 조건식을 조합해 검색하는 경우가 많다.

이때 WHERE 조건식과 함께 논리 연산자 AND, OR, NOT을 사용하여 함께 쓰기도 한다.

수많은 제품 중에서 WHERE과 논리연산자를 함께 사용해보도록 하자.

 

1. AND 

카테고리 id 1번 이면서 재고가 있는 제품을 검색해보자

A and B 

A, B 조건식이 모두가 True 여야만 True이다.

밴다이어그램으로 생각해볼때 and 는 교집합을 구하는 것이라 할 수 있다.

select * from products where category_id=1 and is_in_stock=1;

---

2. OR

가장 하위의 카테고리(33) 제품 또는 가장 먼저 등록된 제품을 찾아보자.

A OR B 

A, B 조건식 중 하나라도 True 면 True 이다.

or은 합집합을 구하는 것이라 할 수 있다.

select * from products where id=1 or category_id=33;

주의

간혹 햇갈려하는 사람들을 봤어서 공유한다.

예를 들어 no 열 값이 1 또는 2인 행을 찾고자 할때 아래와 같은 실수를 범하면 안된다.

select * from tests where no = 1 or 2;

상수 2 는 항상 True이다.

아래 코드가 올바르다.

select * from tests where no =1 or no = 2;

---

3. AND 와 OR 같이 사용하기

and 와 or 연산자를 함께 사용하기 위해선 연산자의 '우선순위'가 존재하기 때문에 주의가 필요하다.

select * from products whrer a=1 or a=2 and b=1 or b=2;

select * from products where (a=1 or a=2) and (b=1 or b=2);

위 아래의 결괏값이 다르기에 괄호를 붙혀주는 습관을 갖자.

---

4. NOT

품절인! 제품을 찾기

NOT A

A의 조건식에 따라 그값을 '제외' 하고로 해석 할 수 있다.

아래 이미지는 품절인 제품들이다.

select * from products where not is_in_stock=1;

 

---

패턴매칭

SELECT + WHERE + LIKE

LIKE 술어를 사용하여 문자열의 일부분을 비교하는 부분 검색을 할 수 있는데 이를 패턴 매칭이라 부른다.

앞서 비교 연산자와 논리 연산자를 이용해 검색을 한 경우는 값이 동일한지가 초점이었다면

부분검색 즉, 패턴매칭을 사용하여 특정 문자나 문자열이 포함되어 있는지를 검색할 수 있다. 

이는 LIKE를 사용하면 열 값이 부분적으로 일치해도 True로 판단하기 때문이다.

 

열명 LIKE '패턴';

왼쪽에는 매칭 대상을 지정하고 오른쪽에는 패턴을 문자열로 지정한다. 단, 수치형 상수(id=1, 2)등은 지정할 수 없다고 한다.

패턴은 메타문자를 사용하여 정의한다.

메타문자

와일드카드라고도 불리는 메타문자는 패턴 매칭시 '임의의 문자 또는 문자열'에 매치하는 부분을 지정하기 위해 쓰이는 특수 문자이다.

% => 임의의 문자열

_ (언더스코어) => 임의의 문자 하나를 의미한다.

메타문자를 지정하지 않으면 완전 일치로 검색되므로 정의하는 의미가 사라짐을 명심하자.

또한, 와일드카드로 자주쓰이는 애스터리스크(*)는 LIKE에서 사용할 수 없다는 점을 알아두자.

 

아래는 임의로 추가한 데이터를 통한 예제이다.

select * from products where name like 'SQL%';

여기서 집고넘어가야할 점이 있다.

73번째에도 SQL이 있고, 75번에도 SQL이 있는데 왜 75는 출력되지 않았을까?

75번은 SQL로 열값이 시작하지 않기 때문이다. 다시말해 SQL 앞에도 문자열이 존재하기 때문이다.

name이라는 열 안에 SQL이 들어간 모든 항목을 검색하고 싶다면 아래와 같이 수정해주면 된다.

select * from products where name like '%SQL%';

여기서 핵심

메타문자 %가 빈 문자열과도 매칭하는 것!!

73번의 경우 SQL 앞에 매치하는 문자열이 없는데도 검색이 된다!

'SQL%'; ==> 전방일치 SQL로 시작
'%SQL%'; ==> 중간일치 SQL 포함
'%SQL'; ==> 후방일치 SQL로 끝

 

이제 %와 _를 포함하고 있는 열을 검색해보자

메타문자와 동일한 문자인 %를 LIKE로 검색할 경우 LIKE 와 함께 쓰이는 % 메타문자는 임의의 문자열을 의미하므로,  일반문자 %를 검색하기위해서는 '이스케이프'처리(예외처리) 해야한다.
'\%'와 같이 \을 %앞에 붙이면 된다.

select * from products where name like '%%%'; => X

select * from products where name like '%\%%';

첫번째 코드의 경우 문자열이 포함된 모든 값이 검색되므로 두번째 코드가 올바르다.

---

문자열 상수 ' 의 이스케이프 

문자열 상수 안에 ' 를 포함하고 싶을 경우 :  표준 SQL에서는 '를 2개 연속해서 기술하는것으로 이스케이프 처리한다.

'it's'라는 문자열을 문자열 상수로 표기하려면 'it''s'로 씀.
SELECT 'it's';  ==>  SQL syntax 에러. (열고 닫고 문법 자체가 맞지 않는다.. 불편하다)
SELECT 'it''s'; ==> 올바르다.

아래 테스트 케이스로 추가한것을 예로 들어보자

select * from products where name like '%it''s%';

 

간단한 패턴 매칭의 경우 LIKE로 모두 소화할 수 있지만 복잡한 패턴의 경우 정규표현식을 사용하는게 좋다.

최근 거의 대부분 데이터베이스에서는 정규표현식을 지원하기 때문에 문제없다.

 

 

지금까지 데이버베이스는 무엇이고 DBMS는 무엇이고 SQL은 무엇인지를 알아보았다.

알고리즘과 자료구조처럼 SQL도 데이터베이스의 근본으로 익히 알고 있어야한다는 이야기를 항상 들어왔다.

Django ORM, TypeORM이 굉장히 나이스하게 도와주지만 근본은 크게 변하지 않는다!

(표준 SQL을 사용하여 설명한다.)

출처 : SQL 첫걸음/ 아사이 아츠시 지음/ 한빛미디어