저번 게시글에서 sampleDB를 이용해 집계함수의 사용에 대해 살펴보았습니다.

 

Sample DB 설정 방법은 다음 링크를 참조하세요. (https://m-datastudy.tistory.com/5)

 

이번에는 마찬가지로 SELECT ~ FROM 쿼리(구문)에 사용할 수 있는 집계함수 외의 함수를 알아볼것입니다. 저번 게시물 (https://m-datastudy.tistory.com/10)를 보신 분들은 이제 레코드의 개수나 숫자로 된 레코드 값의 합, 평균 등을 알아볼 수 있고, 특정 기준에 따라 레코드를 묶을 수도 있습니다. 

 

먼저 함수는 저번과 이번 게시물에 적힌 것보다 훨씬 많습니다. 지금 쓰려는 것은 그 중 기본적이라고 생각되는 함수인것입니다.

 

1. SUBSTRING('문자열' , offset, limit) 

SUBSTRING 함수는 문자열에서 원하는 부분만 뽑아내는 함수입니다.

FROM 구문이 반드시 필요하지는 않습니다. 다음과 같이 사용합니다.

-> SELECT SUBSTRING('문자열' , offset, limit);

-> SELECT SUBSTRING('2019-03-19' , 6, 2);

 

또한 모든 함수는 반드시 정해진 인자에 맞춰서 사용해야만 합니다. 인자란 ('문자열', offset, limit)의 부분을 의미합니다.

 

1. '문자열' 부분은 우리가 뽑아내고싶은 글자가 있는 문장이나 긴 텍스트를 넣습니다.

 

2. offset 부분은 저번에 살펴보았던 LIMIT~OFFSET~ 구문처럼 어디서 부터 텍스트를 뽑아낼 것인지 숫자로 정합니다.

예를 들어 '2019-03-19' 문자열에서 첫번째 글자인 '2'가 1번째 글자이고 '0'이 2번째, '1'이 3번째, '9'가 4번째 글자입니다.

 

3. limit 부분은 몇글자를 뽑아낼지 정합니다.

 

 

정리하자면, SELECT SUBSTRING('2019-03-19' , 6, 2); 쿼리는 '2019-03-19'라는 문장의 6번째 글자부터 2글자를 찾아 출력하라는 뜻입니다. 결과값은 '03'이 됩니다. 작은 따옴표 안의 모든 글이 텍스트입니다. 따라서 ' - ' 또한 텍스트입니다.

 

2. TRIM('문자열')

 

TRIM 함수는 문자열 앞 뒤에 있는 공백을 제거합니다. ' 서울특별시 '를 함수 처리 할 경우 '서울특별시'로 출력됩니다.

단, 문자 내부의 공백은 제거되지 않습니다. substring처럼 반드시 FROM이 필요하지는 않습니다.

-> SELECT TRIM(' 문자열 ');

 

 

3. CHAR_LENGTH('문자열'), OCTET_LENGTH('문자열')

 

CHAR_LENGTH 함수는 문자열의 길이. 즉, 글자수를 알려주는 함수입니다. 다음과 같이 사용합니다.

-> SELECT CHAR_LENGTH('문자열');

-> SELECT CHAR_LENGTH('123');

 

OCTET_LENGTH 함수는 문자열의 byte 수. 즉, 용량을 알려주는 함수입니다.

일반적으로 문자 하나 당 숫자 및 영어는 1byte. 한글은 2byte입니다. 다음과 같이 사용합니다.

-> SELECT OCTET_LENGTH('문자열');

-> SELECT OCTET_LENGTH('123abc가나다');

 

 

4. ROUND(컬럼명/숫자, 반올림위치)

 

ROUND 함수는 반올림해주는 함수입니다. 어느 위치에서 반올림 할 지도 직접 정할 수 있습니다. 

mySQL에서는 2가 소숫점 둘째 자리로 반올림해줍니다. 1이 첫째 짜리로, -1이 1의 자리, -2가 10의 자리로 반올림해주는 식입니다. 소숫점 둘째 자리에서 반올림 해주는 것이 아니라 결과값이 소숫점 둘째자리로 출력됩니다.

-> SELECT ROUND(컬럼명/숫자, 반올림 위치);

-> SELECT ROUND(컬럼명, 2) FROM 테이블명;

-> SELECT ROUND(5961.6651, 2);

 

 

5. CONCAT('문자열1'/컬럼명, '문자열2'/컬럼명)

 

CONCAT 함수는 여러개의 문자열을 합칠 수 있는 함수입니다. 앞 뒤 글자가 합쳐집니다. 컬럼명일 경우엔 자료형이 숫자형 혹은 문자형이어야 합니다. 

 

컬럼명 + 문자열도 가능합니다. 예를 들어,

-> SELECT CONCAT(customerNumber, '번 고객') FROM customers;

일 경우에, 컬럼 내의 모든 레코드의 뒤에 '번 고객'이 합쳐져 출력됩니다.

 

 

 

저번 게시글에서 sampleDB를 이용해 WHERE 구의 사용과 연산자 등에 대해 살펴보았습니다.

 

Sample DB 설정 방법은 다음 링크를 참조하세요. (https://m-datastudy.tistory.com/5)

 

이번에는 SELECT ~ FROM 쿼리(구문)에 사용할 수 있는 여러가지 함수를 알아볼것입니다. 저번 게시물 (https://m-datastudy.tistory.com/9)를 보신 분들은 이제 원하는 조건을 지정해서 테이블로부터 레코드를 불러 올 수 있습니다. 

그런데 만약 레코드의 개수가 엄청나게 많아서 개수조차 세기 힘들 때에는 어떻게 해야 할까요?

 

그럴 때는 집계함수를 이용해 한 컬럼 내의 레코드의 개수를 세거나 합과 산술평균 등을 낼 수 있습니다. 

 

1. 집계함수 : COUNT(개수), SUM(총합), AVG(평균), GROUP BY(묶음)

 

 

◈ COUNT 함수는 컬럼내 레코드의 개수를 세주는 함수로 다음과 같이 사용합니다.

-> SELECT COUNT(컬럼명1) FROM 테이블명;

-> SELECT COUNT(컬럼명1), COUNT(컬럼명2), ... FROM 테이블명;

다음은 예시입니다.

COUNT 함수는 레코드의 자료형에 상관없이 사용할 수 있습니다. 

다만 특별한 경우를 제외하면, 집계함수와 일반 컬럼을 함께 사용할 경우 오류가 발생합니다.

즉, 다음과 같은 경우에는 오류가 발생합니다.

-> SELECT COUNT(customerNumber), customerNumber FROM customers; 

 

 

◈ SUM 함수는 컬럼 내 레코드 값의 총합을 내주는 함수입니다.

다음과 같이 사용합니다.

-> SELECT SUM(컬럼명1) FROM 테이블명;

-> SELECT SUM(컬럼명1), SUM(컬럼명2), ... FROM 테이블명;

다음은 예시입니다.

SUM 함수는 컬럼의 자료형이 숫자형일때만 사용할 수 있습니다. 

그리고 같은 집계함수끼리는 다음과 같이 함께 사용할 수 있습니다.

 

 

◈ AVG 함수는 컬럼 내 레코드 값의 평균을 구해줍니다. 

다음과 같이 사용합니다.

-> SELECT AVG(컬럼명1) FROM 테이블명;

-> SELECT AVG(컬럼명1), AVG(컬럼명2), ... FROM 테이블명;

다음은 예시입니다.

마찬가지로 AVG 함수는 컬럼의 자료형이 수치형일 때만 사용할 수 있습니다.

 

 

◈ GROUP BY 함수는 종류가 같은 것들이 하나로 묶입니다.

GROUP BY는 쿼리(구문)의 가장 마지막에 사용합니다. 기준을 제시하면, 그 기준에 따라 모아줍니다.

말로 설명하면 이해하기 힘들 수 있으므로 예시로 확인하겠습니다.

 

도시가 중복되는 것이 있는데 같은 도시가 여러번 나오기도 하고 중구난방으로 배열되어있어서 알아보기 힘듭니다.

 

그때 GROUP BY를 사용하면 더 보기 편해집니다. 중복되는 데이터는 하나로 모아줍니다.

 

그럼 다음과 같이 테이블 내에 표시된 모든 도시의 종류를 한번에 볼 수 있습니다.

 

GROUP BY 함수는 자료형 상관없이 사용할 수 있습니다. 

 

그리고 다른 집계함수와 연계하여 사용하면 그 진가를 발휘합니다.

 

예외적으로 GROUP BY 함수에 기준으로 제시된 컬럼은 위와 같이 집계함수와 함께 사용될 수 있습니다.

 

또한 중요한 것으로 모든 집계함수는 WHERE구와 함께 사용할 수 없고, 대신 HAVING 구를 사용하여 조건식을 지정할 수 있습니다.

구문이 처리되는 순서는 GROUP BY -> SELECT -> ORDER BY 순으로 처리됩니다.

 

저번 게시글에서 sampleDB와 SELECT 예약어(SELECT, FROM, * 등의 구문을 예약어라고 합니다)를 이용해 간단한 테이블을 불러오고, 테이블에 대한 정보를 확인하는 법을 살펴보았습니다.

 

Sample DB 설정 방법은 다음 링크를 참조하세요. (https://m-datastudy.tistory.com/5)

 

이번에는 SELECT~FROM 예약어를 더 활용해보겠습니다. 저번 게시물 (https://m-datastudy.tistory.com/6?category=856604)를 보신 분들은 이제 테이블의 컬럼을 지정해서 불러올 수 있습니다. 그렇다면 컬럼 내에서 원하는 데이터를 선택하려면 어떻게 해야 할까요?

 

1. WHERE 구에서 레코드(row) 지정하기.

다음은 customer라는 테이블에서 customerNumber, customerName, city 컬럼을 불러오고, 그 중 customerNumber가 200 이하인 고객의 정보만 불러오는 명령입니다.

 

다음과 같이 WHERE 구는 컬럼에 조건을 설정해 특정 데이터만을 불러 올 수 있게 해줍니다. WHERE 구를 사용하기 위해서는 먼저 알아야 하는 것이 데이터의 자료형과 연산자 입니다.

 

2. 데이터의 자료형과 연산자.

 

일상생활에선 글을 쓸 때 한 페이지에 숫자이든, 글자이든 그저 손으로 적으면 됩니다. 하지만 컴퓨터는 이런 작업을 할 수 없습니다. 한 페이지마다 숫자를 사용할지, 글자를 사용할지, 숫자나 글자를 몇개 넣을지, 아니면 숫자나 글자를 같이 사용할지 등을 따로 정한 뒤에 써야합니다. 가장 많이 사용되는 자료형은 다음과 같습니다.

저번 게시글에서 desc 명령어를 통해 테이블의 구조를 살펴보았습니다. 그때 이미 int와 varchar는 본 적이 있습니다. char와 varchar의 차이는 char는 정확히 입력한 용량만큼 글자가 들어가야합니다. 더 적거나 많아도 안됩니다. varchar는 이러한 제약으로부터 자유롭습니다. 대신 char보다 컴퓨터의 처리속도를 더 많이 깎아먹게 됩니다.

 

다음은 비교연산자입니다. 이러한 비교연산자들은 다른 코딩 프로그램에서도 거의 같게 사용됩니다.

 

테이블이나 컬럼의 자료형이 문자형일 때는 =, <, >, <=, >= 연산자는 사용할 수 없음에 유의해주세요.

 

3. (추가) 출력 레코드의 제한. LIMIT ~ OFFSET ~

LMIT ~ OFFSET ~ 구문을 이용해 출력할 레코드의 수를 조정할 수 있습니다.

 

LIMIT은 출력할 레코드의 개수를 지정하는 것입니다.

OFFSET은 어느 레코드부터 개수를 지정할지 정하는 것입니다.

 

SELECT - FROM 구문은 우리가 구축한 DB의 TABLE에서 데이터를 불러올 수 있는 구문입니다. 일반적으로 다음과 같이 씁니다.

 

SELECT "컬럼1","컬럼2" FROM "테이블명";

Ex1) sample1이라는 테이블에서 모든 컬럼(열) 데이터 가져오기

       -> SELECT * FROM sample1; 

Ex2) sample1이라는 테이블에서 a, b 컬럼만 가져오기

       -> SELECT a, b FROM sample1;

 

mySQL에서 제공한 Sample DB를 이용해 직접 해보겠습니다.

Sample DB 설정 방법은 다음 링크를 참고하세요(https://m-datastudy.tistory.com/5)

 

1. 테이블 목록 확인하기. show tables;

 

 

SELECT 구문을 사용하기 위해서는 먼저 DB에 어떤 테이블 들이 있는지 확인할 필요가 있습니다.

show tables; 명령어를 통해 간단하게 확인 할 수 있습니다. 저희는 customers 테이블을 사용해보겠습니다.

 

 

 

 

 

 

2. 테이블 구성 확인하기. desc tables; 

 

SELECT 구문으로 컬럼을 가져오기 위해서는 테이블 내에 어떤 컬럼이 있는지 확인해봐야합니다.

desc "테이블명"; 을 통해서 모든 테이블을 각각 확인해 볼 수 있습니다. 위 사진은 customers테이블의 컬럼과 데이터 타입을 보여줍니다.

 

Type은 컴퓨터가 어떤 데이터를 저장 할 수 있는지 보여줍니다. 예를 들어 int(11)은 숫자 데이터를 11byte만큼 저장 할 수 있다는 뜻입니다. varchar(50)은 문자 데이터를 50byte만큼 저장 할 수 있다는 뜻입니다.

 

customerName과 city, country가 눈에 띄입니다. 한번 불러와 보겠습니다.

 

Ex1) SELECT customerName, city, country FROM customers;

 

 

컬럼명은 꼭 순서대로 불러와야 하는 것이 아닙니다. city와 country를 앞에 불러올 수 도 있습니다.

 

Ex2) SELECT city, country, customerName FROM customers;

 

 

즉 SELECT 다음에 오는 컬럼명은 사용자가 원하는 순서대로 붙히면 됩니다.

 

 

 

 

 

 

3. 목록 정렬하기. ORDER BY 구문.

 

테이블을 불러와도 이름이 정렬되어있지 않아 보기가 힘듭니다. 이 때는 ORDER BY를 사용할 수 있습니다. ORDER BY에는 asc와 desc 두가지 방식이 있습니다. asc(Ascend의 약자)는 오름차순, desc(Descend의 약자)는 내림차순입니다.

 

그런데 desc는 아까 테이블의 구성요소를 확인하기 위해 썼습니다. 두가지 desc는 서로 다른 단어의 약자입니다.

이는 테이블의 구성요소를 확인하기 위해 사용하는 desc 명령어가 엄밀히 따지자면 SQL 명령어가 아니기 때문에 약자가 겹치게 되었습니다.

다른 SQL 프로그램에서는 desc(Describe의 약자)가 아닌 str(Structure의 약자) 일 수도 있습니다.

 

ORDER BY는 일반적으로 이렇게 사용합니다.

SELECT "컬럼1", "컬럼2" FROM "테이블명" ORDER BY "컬럼명";

Ex1) SELECT * FROM table1 ORDER BY a asc;

Ex2) SELECT a, b FROM table1 ORDER BY b desc;

 

직접 해보겠습니다.

Ex3) SELECT customerName FROM customers ORDER BY customerName;

 

 

 

 

 

+ (추가) 별명을 지어주는 AS '이름'

 

-> SELECT customerName AS name FROM customers;

위와 같은 방식으로 컬럼이름을 일시적으로 바꿀 수 있습니다. 구문을 작성할 때에만 이용되고 테이블 자체의 이름이 바뀌는 것이 아닙니다.

 

그러나 이 AS 구를 사용할 때에는 구문이 처리되는 순서를 이해할 필요가 있습니다.

 

구문은 일반적으로 WHERE 구가 가장 먼저, 그 다음 SELECT, 마지막에 ORDER BY 구가 처리되는 형식입니다.

WHERE -> SELECT -> ORDER BY

우리는 AS 구를 SELECT 구에서 사용하여 별명을 설정합니다. 따라서 ORDER BY를 처리할 때 별명을 사용할 수 있습니다. 그러나 WHERE 구에서는 사용할 수 없습니다. WHERE 구가 처리될 시점에 아직 별명이 설정되지 않았기 때문입니다. 이 점을 유의해야 합니다.

 

 

 

SELECT 구문은 SQL에서 가장 대표적인 구문인 만큼 다른 구문과 함께 사용할 수 있습니다. 다음 게시물에서는 그것을 알아볼 것입니다.

mySQL로 구문을 조작하는 연습을 하기 위해서는 먼저 테이블이 저장되는 데이터베이스가 따로 있어야 합니다. 그런데 이 데이터베이스 설계가 간단한 일이 아닌만큼 mySQL에서는 당장 연습에 사용할 수 있게 해주는 sample DB를 제공합니다.

 

1. mySQL Sample Database(http://www.mysqltutorial.org/mysql-sample-database.aspx) 다운로드

 

 

 

 

 

 

이후 cmd를 실행해 mysql을 실행해줍니다.

 

 

 

무언가 시행된 뒤, 다시 mysql> 가 cmd 하단에 뜰 때 까지 기다립니다.

mysql>가 뜨면, 다음과 같이 입력해줍니다.

 

 

 

다음부터 mySQL을 실행하면 항상 USE classicmodels;를 입력해줘야합니다.

가장 먼저 알아두어야 할 점은, mySQL은 원래 오픈소스였으나 현재는 Oracle에 인수되어 무료버전과 유료버전으로 나뉘어버렸다는 것입니다. 따라서 저작권 문제가 발생할까봐 두렵거나, Oracle에 반감을 가지고 있거나, 오픈소스 개발에 참여하고 싶거나 하시다면 SQL 커뮤니티에서 mysql의 소스코드를 이용해 새롭게 만든 오픈소스 SQL 프로그램인 mariaDB를 이용하시는 것을 추천합니다. mySQL과 mariaDB는 사실상 같은 프로그램이라고 봐도 되지만 오히려 mySQL보다 mariaDB가 더 편리한 점이 많습니다.

현재 제가 파악한 가장 큰 차이점 몇 가지를 적어보자면. 1. mariaDB는 mySQL과는 달리 현재 사용하고 있는 DB가 무엇인지 항상 확인할 수 있게 개선해두었고 2. mySQL에서는 현재 사용하고 있는 테이블을 한 구문 내에서 다시 참조 할 수 없지만 mariaDB에서는 가능하도록 되어있어 좀 더 쾌적한 구문 작성이 가능합니다.

 

그럼 곧바로 설치를 시작해보겠습니다.

 

1. MySQL 홈페이지(https://www.mysql.com/products/community/) 에서 community edition 다운로드

 

Oracle에 인수되어 나뉘어져버린 무료버전과 유료버전 중 무료버전입니다. 영리적인 목적으로만 사용하지 않으시면 됩니다.(기업에서 업무 용도로 사용 등)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2. 다운로드 받은 파일 실행 후 인스톨

 

다운로드 받은 파일을 실행 시켜주세요. 실행시키면 다음과 같은 화면이 나옵니다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

이후 몇번의 next를 누르면 설치가 완료됩니다. 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. 설치된 mySQL 실행하기

 

바로 cmd를 사용해 실행할 수 있으면 좋겠지만 먼저 path를 설정해줘야 명령어가 먹힙니다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL과 DB

SQL은 Structured Query Language의 준말로 RDB를 관리하기위해 만들어낸 프로그램 언어입니다. RDB는 관계형 데이터베이스를 의미합니다. Realated Data Base의 약자이기도 합니다. 관계형 데이터베이스는 간단하게 말하면 테이블 형태의 데이터를 모아놓은 것입니다.

 

RDB, RDBMS를 관리하기 위해 SQL을 사용한다면 흔히 얘기하는 DB, DBMS는 뭔가?

DB는 아시다시피 데이터베이스를 뜻합니다. 데이터의 집합입니다. 데이터를 정리하는데에는 다양한 방법이 있습니다만 그 중에 테이블 형태로 데이터를 모아놓는 것을 RDB, 관계형 데이터베이스라고 합니다. DB를 관리하는 툴이 DBMS(Data Base Management System)이며 보통 DBMS에서 DB를 생성, 갱신, 삭제 할 수 있습니다. 마찬가지로 RDB를 관리하는 Management System이 RDBMS입니다. DBMS라는 범주에 RDBMS가 속하게 됩니다. 다만 DBMS 중 RDBMS를 가장 많이 사용하므로 SQL을 다룰 때 DB 혹은 DBMS 혹은 RDBMS 등을 이야기해도 듣는 사람이 일반적으로 같은 것으로 받아들입니다. 그리고 이러한 DBMS는 정형(Structured) 데이터를 관리하게 됩니다. 

 

noSQL의 시대

제 블로그의 첫 글(Introducing Data Science: https://m-datastudy.tistory.com/1)에 제가 Bigdata를 다룰 때에는 정형 데이터를 다루는것 보단 비정형(Unstructured) 데이터를 다루는 것이 더 어렵다고 적어두었습니다. RDBMS의 데이터는 테이블이라는 특정한 형식으로 저장이 되어있기 때문에 데이터를 불러오고 조작하는데에 큰 어려움이 없습니다. 그러나 Bigdata에서 정보를 불러올 때는 다양한 형식의 텍스트 들이 복잡하게 섞여있기 때문에 정형 데이터를 조작하는 방식으로는 도저히 다룰 수 가 없게 되었습니다. 그래서 나타난 것이 noSQL입니다. SQL이 아니다 (no)의 의미가 아닌 not only SQL의 약자로 데이터를 다룰 때 SQL만 사용하지는 않는다는 뜻입니다. mongoDB, Cassandra등 이런 noSQL 프로그램들이 대세인 상황에서 정형 데이터만을 다루는 SQL의 입지는 비교적 줄어들었다고 얘기할 수 있을 것 같습니다. 당장 한국 Oracle만 해도 몇 주 전 (2019.11.19)에 Bigdata 시대의 꽃인 클라우드 트렌드를 따라가지 못해 대규모 정리해고를 단행했으니까요.

 

그럼 noSQL의 시대에 SQL이 웬말이냐

not only SQL이라는 말을 풀어보면 not only Structured Query Language이 됩니다. 이는 정형 질의어(Query Language)만 사용하지 않겠다는 뜻이 됩니다. 즉 다른 질의어나 관리 방식을 추가적으로 사용한다는 뜻이지 SQL을 완전히 배제하겠다는 뜻이 아닙니다. 또한 SQL은 비정형 데이터를 다룰 필요가 없는 영역에서는 당연히 noSQL보다 선호됩니다. noSQL보다는 SQL이 비교적 표준화되어있기 때문입니다. 프로그램마다 특성이 다른 noSQL 보다는 사용하기 쉬울 것입니다.

 

결론

noSQL의 등장으로 비교적 SQL의 입지가 줄어들었지만, 배울 가치가 없다는 것은 어불성설입니다. Bigdata 시대의 모든 기술은 Bigdata 이전 시대로부터 나온 것입니다. 완전히 새로운 것은 없습니다. 따라서 저는 SQL을 공부하며 이 블로그에 정리해나갈 예정입니다.