Jins' Dev Inside

SQL이란 DBMS에서 데이터를 질의하기 위해 만들어진 언어이다. 크게 다음과 같이 3가지로 분류된다.

 - DDL (Data Definition Language) : 데이터를 정의하기 위한 언어로, CREATE, DROP, ALTER, TRUNCATE 등으로 시작하는 Query 문이 속한다.
 - DML (Data Manipulation Language) : 데이터를 다루기 위한 언어로 흔히 말하는 CRUD 에 해당하는 SELECT, INSERT, UPDATE, DELETE 등 Query 문이 속한다.
 - DCL (Data Control Language) : 데이터를 관리하기 위한 언어로 주로 사용자에게 권한을 부여하거나 철회할 수 있는 GRANT, REVOKE 등의 Query 문이 있다.

실제로 사용하면서 유용하게 썼던 SQL 쿼리문들을 정리해두었다. 실무에서도 응용해서 사용하면 크게 무리는 없었던 것 같다.

인터넷을 돌아다니다 발견할 수 있는 Geek 한 것들을 모아두진 않았다.

-        접속 방법

 : mysql -u root -p (dbname)

-        비밀번호 변경

 : mysqladmin -u root password 새로운 비밀번호

-        테이블의 생성

 : create table {테이블이름}({칼럼명} {칼럼타입});

(ex) CREATE TABLE member (

id int(11) NOT NULL,

);

-        구조 보기

 : desc 테이블 / explain 테이블  /  show create table {테이블명}

-        이름 변경

 : rename table {테이블명A} to {테이블명B}

-        삭제

 : drop table {테이블명}

-        레코드 삽입

 : Insert into {테이블명} values(v1, v2) / Insert into table(col1, col2) values(v1, v2);

-        조회

 : select * from table {테이블명}

> AS : 칼럼의 이름을 달리 명명해서 출력. (ex) Col1 as 'name'

> Desc : 내림차순, Asc : 오름차순 (ORDER BY)

> LIMIT 10 : 0~10 까지 레코드 수 제한. / LIMIT 100, 10 : 100~110까지 레코드 범위

-        수정

 : Update {테이블명} set col1 = 칼럼1 where 조건

-        삭제

 : Delete from {테이블명} where 조건

-        칼럼 추가

 : Alter table {테이블명} add col3 varchar(255) not null.

-        칼럼 삭제

 : Alter table {테이블명} drop col3

-        칼럼 수정

 : Alter table {테이블명} modify col3 char(50) not null.

-        In : 원하는 필드값만을 선택 추출하는데 사용되는 그룹 조건문

-        조인

(1)  Inner join

 : Select * from tableA inner join tableB on tableA.col1 = tableB.col1

 => tableA의 col1과 tableB의 col1이 일치하는 데이터만을 출력. ON 절의 조건이 일치하는 조인테이블의 결과만을 출력한다.

(2)  Outer join

 : Select * from tableA left outer join tableB on tableA.col1 = tableB.col1

 => tableA.col1이 존재하나 tableB.col1이 존재하지 않으면 tableB.col1 = NULL인 상태로 출력. 조인하는 테이블의 ON 절 조건 중 한쪽의 모든 데이터를 가져옴(LEFT JOIN , RIGHT JOIN) 양쪽(FULL JOIN)

-        내장함수 Benchmark

 : Select Benchmark(반복횟수, 실행쿼리)

(ex) Select Benchmark(100, (“select * from table”)); => 해당 쿼리를 100번 반복한 벤치마크 결과를 출력.

-        DISTINCT

 : 주로 UNIQUE한 COLUMN이나 TUPLE을 조회할 때 사용되는 키워드. 칼럼을 DISTINCT 를 이용하여 조회한다면 중복을 제거한 값들을 바로 얻을 수 있다. 단 이 때, 여러 개의 칼럼을 지정한다면 칼럼의 조합이 중복되는 것을 제외한다. DISTINCT는 함수처럼 WHERE이 아닌 HAVING 조건식에도 사용이 가능하다.

(ex) Select DISTINCT email from table;

(ex) SELECT class FROM courses GROUP BY(class) HAVING count(distinct student) >= 5;

-        GROUP BY

 : 데이터를 그루핑해서 결과를 가져오는 경우 사용. 내부적으로 중복값을 배제한채 정렬된 결과를 가져온다. 주로 HAVING과 같이 사용되며 그룹으로 묶어서 자체 정렬한다. 좀 더 정확히는 그룹의 대표값을 정렬해서 가져온다. 그렇기 때문에 모든 컬럼에 대해 단순 SELECT 하는 쿼리문에는 쓰기 적절치 않으며 테이블 내에서 데이터를 가공할 때 사용하기가 좋다. 예를 들어 accountType에 따라 해당하는 accountName의 row수를 그루핑 하고 싶다면 다음 쿼리를 사용해보자. Select accountType, COUNT(accountName) from accounts group by(accountType);

-      HAVING

 : HAVING은 GROUP BY 와 같이 쓰이는 구문으로 GROUP BY의 조건문이라 할 수 있다. 위의 쿼리에서 COUNT가 1개 이상인 내용만 쿼리를 하는데 다음처럼 사용 가능하다. SELECT accountType, COUNT(accountName) FROM accounts GROUP BY(accountType) HAVING COUNT(accountName) > 1; HAVING의 시점은 GROUPING이 끝난 이후이고 WHERE 절과 다르게 HAVING 절은 통계함수를 포함할 수 있다.

HAVING은 () 를 안 싸는 것이 좋다. 버전에 따라 오작동 위험이 있는듯하다 ;;

WHERE 구문과 같이 사용할 때, WHERE 구문이 먼저 적용되고 난 다음의 조건 결과에 대해 GROUP BY ~ HAVING 조건문이 걸린다. HAVING 조건문은 그룹화되어진 필드들에 대해 적용된다.

-      SubQuery 사용법

 : 복잡한 쿼리문을 만들 때 많이 사용하게 되는 구문이 서브쿼리문이다. 서브쿼리의 사용은 Nested Loop 를 돌기 때문에 사용에 주의하자.

(ex) SELECT accountInfo from accounts where accountName in (select accountName from accountNames);       //accountNames 테이블에 있는 이름에 대해서만 accountInfo를 조회하는 쿼리(Validation)

간단한 쿼리문 들이라 대부분 자주 쓰다보니 외워진 상태이지만, SQL에 막 입문하는 사람이거나 쿼리문에 익숙하지 않은 분들은 이 내용만 알게되어도 어느정도 복잡한 쿼리문도 다룰 수 있을 것이다.

사실 MySQL은 이미 프로그래밍을 모르는 사람들도 들어봤을 정도로 너무 유명한 Database라 MySQL이 무엇인가를 얘기하는 건 진부한 주제일 지 모른다.

그렇듯 MySQL은 우리가 흔히 "데이터베이스" 라고 하는 시스템의 표준과 같은 소프트웨어이며 그만큼 RDBMS 중 세계적으로 가장 널리 사용되고 있는 소프트웨어이다.

RDBMS에 대해서 간단히 언급을 하자면, 저장한 데이터들 간의 관계를 명시하는 "관계형 데이터 모델링" 을 지원하는 DataBase Management System 이다. 데이터는 테이블에 명시된 여러 Column 값들을 포함하는 Tuple 또는 Record 로 구성되어 Row를 이룬다. 말이 좀 복잡하지만, 간단히 얘기하면 정의된 포맷대로 나열된 데이터의 목록이라고 생각하면 쉽다. 가령 Id 와 Password 를 저장하는 유저 정보의 데이터는 다음과 같이 나타내어질 수 있다.

위의 그림에서 테이블이 나타내는건 유저의 ID와 Password 이다. 이것이 일반적인 DBMS, 즉 데이터베이스 관리 시스템의 모습이다. ID 와 PASSWORD는 테이블을 구성하는 데이터를 묘사(Description)하는 데 기준이 되는 Column 이고, 그 아래의 Record 들은 실제 데이터가 담긴다. 적층된 데이터의 행들이 ROW 이다.

그렇다면 여기에 "관계" 를 추가해보자.

왼쪽 그림과 같은 기존의 유저 ID 관리 테이블에 이번엔 오른쪽의 유저 정보 테이블을 추가했다. 

그런데 자세히 보면, 같은 Column 에 같은 데이터를 갖는 Row를 확인 할 수 있다. 가령 "a123de" 라는 유저와 "jinsp" 라는 유저는 양쪽 테이블 모두에 있으며, 왼쪽 테이블에서는 해당 ID의 비밀번호를, 오른쪽 테이블에서는 해당 ID의 이름과 나이 정보를 알 수 있다.

이렇게 두 테이블은 ID라는 데이터를 기준으로 하는 "관계(Relation)" 를 갖고 있다고 할 수 있으며 RDBMS 는 이런 기초적인 관계에 대한 고민에서 출발한다.

MySQL은 이러한 RDBMS 중에서도 가장 널리 쓰이고 잘 알려져있으며 심지어 무료로 제공되는 훌륭한 소프트웨어라고 할 수 있다. (그 외에도 장점은 많지만, 차차 기술하도록 한다.)

실제로 실무에서도 대부분 MySQL 을 사용하며, 같은 갈래에서 나온 MariaDB나, PostgreSQL 과 같은 DBMS들도 MySQL 에서 상당 부분 아이디어를 공유한다. (물론 각 DB들에 따라 특색은 있다.)

MySQL 을 설치하게 되면 패키지 형태의 프로그램이 깔리게 되며, 이는 여러 가지 단위 모듈들로 구성되어 있다.

<그림 - 출처 MySQL 홈페이지>

엔진을 담당하는 MySQL 은 DB 자체이며, DB를 접근하기 위한 서버와 클라이언트의 형태를 제공한다. 기본적으로 DB에 접근하기 위한 인터페이스를 서버로 두고 있다. 이 서버는 기본 설정에 따르면 localhost IP 에 3306 포트로 열려있으며 이를 통해 MySQL 클라이언트가 이에 접근(Connect) 하여 질의(Query) 하는 구조로 되어 있다. 이때 질의하는 언어는 SQL이다.

즉, 외부에서 드라이버 또는 통신으로 접근 시에도 마찬가지로 TCP/IP 를 통해 MySQL 패키지의 클라이언트 또는 서버로 접근하여 MySQL DB 자체에 접근하게 되는 것이다. (당연해보이는 개념이지만, 개발 시에 SDK 형태로 제공되는 Driver 의 동작원리를 이해하는 것은 중요하다.)

MySQL Server 를 설치하게 되면, MySQL 서버 프로그램으로 MySQL 서비스와 mysqld 프로그램, MySQL 클라이언트 프로그램으로 mysql.exe(CLI 인터페이스), Workbench, slap, mysqladmin 등이 같이 설치된다.