Jins' Dev Inside

실무에서도 자주 사용하는 MySQL 쿼리문들을 정리해보았다. 많이 쓰는 기본 쿼리문 사용법들이므로, 필수적으로 알아두어야 한다.

응용만 한다면 어느정도 복잡한 쿼리문도 손쉽게 만들어낼 수 있다.

- 접속 방법

 : mysql -u root -p (dbname)

- 비밀번호 변경

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

- 테이블의 생성

 : create table 테이블(col int);

- 구조 보기

 : desc 테이블 / explain 테이블

- 이름 변경

 : rename table A to B

- 삭제

 : drop table 테이블

- 레코드 삽입

 : Insert into table values(v1, v2) / Insert into table(col1, col2) values(v1, v2);

- 조회

 : select * from table A

> 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은 () 를 안 싸는 것이 좋다. 버전에 따라 오작동 위험이 있는듯하다 ;;

- SubQuery 사용법

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

(ex) SELECT accountInfo from accounts where accountName in (select accountName from accountNames);

위의 쿼리는 accountNames 테이블에 있는 이름에 대해서만 accountInfo를 조회하는 쿼리(Validation)

기본적인 쿼리문들을 정리해보았다. 시간이 나면 Tricky 하게 사용되는 쿼리문들도 정리해볼 생각이다.

 MyBatis 를 접하고 업무를 할 때 대부분의 일은 Sql Mapper 를 만들고, SELECT, INSERT, UPDATE, DELETE 와 같은 CRUD 작업을 위한 DML(Data Manipulation Language) 를 작성하는 업무이지만, 간혹 Create Table, Drop Table 과 같은 DDL(Data Definition Language) 을 작성해야할 경우가 있다.

 Mybatis를 이용해서 DDL 쿼리도 적용이 가능하다. 

예를들어 CreateTable / DropTable / ShowCreateTable / DescTable 등인데 이 내용이 Mybatis 백서에도 제대로 안나와있다.

결론적으로 말하면 그냥 사용하면 되는데, 사용방법은 다음과 같다.

 (참고로 위의 예제에 ${value} 부분은 MyBatis 의 동적쿼리문을 사용한 부분으로 Parameter Type 을 String 으로 한다면 value 라는 명칭을 사용해야 한다. 내부적으로 String.value 를 이용하기 때문이다. 또한 위의 예시에서 createNewTable 로 매핑된 부분의 ${value} 에는 Create Table 쿼리가 포함되면 된다.)

 다만 한가지 주의할 점은, DML(Data Manipulation Language)가 아닌 위와 같은 DDL(Data Definition Language) 들은 대부분 트랜잭션을 사용하더라도 롤백이 되지 않는다.

몇가지 SQL 구문들은 MySQL 이 암묵적으로 커밋을 하기 때문에 트랜잭션을 감싸더라도 실행하면 롤백이 불가능하다. 트랜잭션이 불가능한 대표적 구문들은 다음과 같다.

CREATE / ALTER / DROP DATABASE

CREATE /ALTER / DROP / RENAME / TRUNCATE TABLE

CREATE / DROP INDEX

CREATE / DROP EVENT

CREATE / DROP FUNCTION

CREATE / DROP PROCEDURE

 많은 경우 조인과 서브쿼리를 통해 복잡한 쿼리 연산을 수행할 수 있으나 쿼리만으로 두 테이블의 다른 항목들을 열거하는 작업은 UNION 을 사용하면 아주 간단해진다.

 다음과 같은 규칙만 만족한다면 N개의 다양한 테이블을 하나의 쿼리로 연결한 SELECT 결과를 받을 수 있다. UNION은 다음과 같이 사용가능하다.

* SELECT name from professor UNION select name from girl_group

 위의 쿼리에서 우리는 Professor 테이블의 존재하는 name 들과 girl_group 테이블 내에 존재하는 name 들을 얻을 수 있다. 교수들의 이름과 걸그룹의 이름을 같이 얻을 수 있겠다.(;;)

(1) UNION 내에서 쿼리들은 하나의 ORDER BY만 사용한다. UNION 으로 감싸는 쿼리들을 각각 정렬하는건 허용되지 않는다.

(2) 각 SELECT의 열수, 표현식이 같아야 한다.

(3) SELECT 문들끼리의 순서는 상관없다.

(4) 결과가 중복되면 DEFAULT 설정으로는 하나만 나온다.

(5) 열의 타입은 같거나 반환 가능한 형태여야 한다.

(6) 중복값을 나타내고 싶다면 UNION ALL을 사용한다.

 물론 UNION 내의 쿼리들을 각각 정리하는 편법은 존재한다. 바로 서브쿼리를 이용하면 가능한데 예를들어 다음과 같이 하면 된다.

SELECT * from (select name from professor order by reg_date)

UNION ...

위의 예시는 교수의 이름들을 등록 시간 별로 정렬해서 name 을 묶고, 걸그룹의 이름들을 등록 시간 별로 정렬해서 묶는 쿼리의 모습이다.

 EXPLAIN 은 어느정도 수준있는 DB를 다루는 개발자라면 꼭 알아야할 정도로 중요한 MySQL 의 쿼리문이다.

 실제로 많은 데이터베이스 관련 서적들에서도 최적화를 위한 선행 단계로 추천하고 있을 만큼 MySQL 의 강력하고 효과적인 쿼리문이다.

 EXPLAIN 키워드는 MySQL 에게 쿼리문의 실행 계획을 물어보는 키워드이다.

 Explain 구문을 이용해서 SQL Query를 수행하기 전에 데이터를 어떻게 가져올 건지에 대한 시스템의 실행계획을 받아볼 수 있다.

주로 쿼리 퍼포먼스 측정을 위해 Explain 을 많이 사용하지만 매 쿼리를 코드에 삽입할 때 테스트해보는 습관을 들이는 것이 좋다.

 SELECT 구문에서 explain 을 사용하는 방법은 단순히 키워드 앞에 붙여주기만 하면 된다. 단 SELECT 구문이 아닐 경우에는 INSERT, UPDATE, DELETE 등의 구문을 SELECT로 재구성시켜줘야 한다.

(ex) EXPLAIN select * from members

      UPDATE name from members where member_id = ‘1’

       EXPLAIN SELECT name from members (UPDATE 후)

 EXPLAIN 에서 각 칼럼은 다음과 같은 의미를 갖는다.

Select_type – 간단한 쿼리인지 복잡한 쿼리인지를 나타낸다.

Table – 어떤 테이블에 접근하는지를 나타낸다. 복잡한 쿼리문에서도 어떤 테이블에 실질적으로 접근하는지를 알 수 있다.

Type – 조인 방식이자, 테이블에서 해당 레코드를 어떻게 찾아가는지를 나타내며 퍼포먼스 측정에 있어서 중요한 지표이다. 

(ALL-풀스캔, INDEX-유사 풀스캔, RANGE-제한된 인덱스 스캔, REF-부분적인 값에 매칭되는 부분만 검사, EQ_REF-단 하나의 

값만 참조하는 경우, CONST-쿼리 일부를 상수로 대채시켜서 찾음. SYSTEM-무조건 하나의 열만을 갖는 테이블)

Possible Key- 해당 조회문에서 MySQL이 선택한 인덱스를 나타낸다.

Key – MySQL이 실제로 사용할 키를 나타낸다. 

Key len – 인덱스 필드가 가질 수 있는 최대길이

Ref – 키 칼럼의 인덱스를 찾기 위해 선행 테이블의 어떤 칼럼이 사용되었는지

Row – 원하는 행을 찾기 위해 읽어야 하는 예측 Row 카운트

Extra – 각종 조건문이 사용되는지 여부를 나타낸다.

 특히 많은 종류의 웹서비스가 SELECT 에 대한 처리를 주로 하는 경우가 많다.(SELECT 가 주가 아닌 데이터라면 MySQL 을 사용하지 않을것이다.) 

가령 Key 로 잡히지 않은 Column 을 조건으로 SELECT 쿼리를 수행하는 경우, 특히 유저 DB라면, FULL SCAN 이 발생해 막대한 비용이 들게 된다...

 이런 참사를 개발시에 눈치챌 수도 있지만, 본인이 DB 구조를 잘 모르거나 구조가 매우 복잡한 레거시 프로젝트에 투입된 상황이라면, EXPLAIN 의 생활화는 큰 도움이 될 것이다.

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에 막 입문하는 사람이거나 쿼리문에 익숙하지 않은 분들은 이 내용만 알게되어도 어느정도 복잡한 쿼리문도 다룰 수 있을 것이다.