SQL에 대해 알아보자

Database의 상징

 

Author: 주니용

 

프로그램을 개발하기 위해서는 프로그래밍만 잘하면 안된다.

기본적으로 사용자들에게 보여주기 위한 데이터를 축적해놓아야 하고, 그 데이터를 수정하거나 입력하는 동작도 필요하다.

필요에 따라서는 데이터를 삭제해야 하는 경우도 발생한다.

 

우리가 대체적으로 사용하고 있는 앱들은 이러한 과정들을 사용자에게 보여주지 않고, 프로그램 내부에서 작동하는 경우가 대부분이다.

서버 개발자들은 이런 일련 과정들을 처리하는 프로세스를 개발하는 일이 정말 많다.

이것이 클라이언트 개발자와의 차이라고 볼 수 있다.

그래서 클라이언트에게 제공되는 API는 서버 개발자들이 만드는 경우도 많다.

(필요에 따라서는 클라이언트 개발자가 같이 작업을 진행하는 경우도 있음)

 

위에서 말한 데이터의 가공을 위해 우리는 테이블이라는 데이터를 저장할 공간을 만들고 데이터를 어떻게 축적할 지 규칙을 세우게 된다.

이러한 모든 과정을 SQL(Structured Query Language)라고 하고, 개발자들은 지원되는 포맷에 맞게 SQL을 호출하여 API를 개발한다.

 

SQL을 공부하기 전에 데이터베이스에 대한 기본적인 개념을 짚고 넘어가야 한다.

엑셀을 예시로 들어 설명해보려고 한다.

엑셀 파일들을 저장할 수 있는 폴더를 스키마(Schema)로 생각해보면,

엑셀 파일 자체를 데이터베이스(Database)라고 하고,

엑셀 시트 하나를 테이블(Table),

그 시트 내의 하나의 행을 row라고 말한다. 

전체 사이즈를 보게 되면 Schema > Database > Table > Row 의 크기로 넘어가게 되고, 

사용자가 다루는 중요도는 Row > Table > Database > Schema 순서라고 생각하면 될거 같다.

 

스키마(Schema) 또한 중요한 개념 중 하나인데, 데이터베이스(Database) 하위에서 SQL이 작동하기 때문에 이 글에서는 다루지 않으려고 한다.

 

SQL은 크게 3가지로 나뉘게 된다.

기존에 빈 데이터베이스에 테이블을 정의하기 위한 DDL(Data Definition Language)

데이터를 입력하고 수정할 수 있는 DML(Data Manipulation Language)

데이터를 제어할 수 있고 이러한 권한을 부여하는 DCL(Data Control Language)

우리는 각각의 수행되는 동작을 쿼리(Query)라고 부르게 되고, 이러한 쿼리를 수행시킬 때마다 구분자로 세미콜론(;)을 사용하게 된다.

 

---

DDL(Data Definition Language)

데이터베이스에 테이블을 세우기 위한 CREATE TABLE이 대표적인 DDL 중에 하나로 포함된다.

CREATE TABLE tableName (   column1 INT NOT NULL AUTO_INCREMENT DEFAULT(0),   column2 VARCHAR(50),   column3 DATE,   PRIMARY KEY (column1) );

 

엑셀 시트의 이름을 정할 수 있듯이 위의 tableName에 개발자가 원하는 테이블의 이름을 정해줄 수 있다.

시트에서 가로 행을 row라고 하면, 세로 열을 column이라고 한다.

위에 컬럼(column)1, 2, 3으로 되어 있는 것이 어떤 데이터를 담을 지에 대한 정의이다. 

column1 의 경우는 INT 타입을 가지고 NULL 값을 허용하지 않으며, 데이터가 입력될 때마다 자동 증가 옵션이 선택되어 있고, 데이터가 입력되지 않으면 0이라는 값을 기본값으로 가지고 들어간다는 의미이다. 사실 기본값 옵션은 NOT NULL조건이 붙어 있으면 의미가 없다.

위의 경우 column1을 PK로 가지게 되는 옵션이 마지막으로 붙게 된다. 

 

데이터베이스의 특징상 KEY를 지정할 수 있는데, KEY의 종류는 여러 가지가 있지만 이 또한 데이터베이스의 설명을 하게 되면 너무 길어지므로 따로 다루지 않으려고 한다.

 

---

ALTER라는 옵션은 현재 존재하는 테이블을 수정하는 쿼리이다. 기존에 테이블의 정의가 잘못되어 있거나 컬럼을 추가/수정해야 하는 경우도 이 쿼리를 사용한다.

 

ALTER TABLE tableName ADD column4 NUMBER(11) NOT NULL;

 

위 쿼리를 수행하면 기존 테이블에 column4 라는 이름으로 컬럼이 추가되게 된다.

 

ALTER TABLE tableName ADD INDEX idxName (column1, column2); ALTER TABLE tableName DROP INDEX idxName;

위 쿼리를 수행하면 해당 테이블의 INDEX를 추가, 삭제를 할 수 있으며 이는 나중에 테이블을 조회할 때 성능을 개선하기 위한 용도로 사용한다.

KEY와는 다른 컬럼을 사용하는 것을 권장하는데, 만약 KEY와 같은 조합으로 추가되면 크게 의미가 없고, 인덱스가 너무 많아져도 성능에 저하가 있을 수 있다. 대개 테이블을 생성하는 시점에 요구사항에 맞춰 많이 생성하고 작업을 하는 편이다.

 

---

TRUNCATE는 테이블에 들어있는 데이터를 빠르게 삭제하고 AUTO_INCREMENT 로 증가된 값과 테이블의 기본 값으로 초기화하는 쿼리이다.

TRUNCATE TABLE tableName;

---

DROP 은 데이터베이스의 테이블을 삭제하기 위함이다.

DROP TABLE tableName;

 

---

DML(Data Manipulation Language)

개발자들이 가장 많이 접하는 쿼리인 SELECT, INSERT, UPDATE, DELETE쿼리가 이에 속한다.

업계에서는 대표적으로 CRUD(Create=INSERT, Read=SELECT, Update, Delete)라고 읽는 경우가 많고,

각각의 의미에 맞게 데이터를 Create(생성), Read(조회), Update(수정), Delete(삭제)하는 용도로 쓴다.

 

SELECT

SELECT *, column2 AS diff FROM tableName WHERE column1 IS NOT NULL AND column2 > 1  ORDER BY column1 DESC GROUP BY column1 LIMIT 5, 15;

SELECT는 데이터를 조회할 때 가장 먼저 사용되는 쿼리 시작문이다.

AS를 넣어서 조회된 컬럼명을 바꿔줄 수 있다.

FROM에 테이블의 이름을 지정해 어느 테이블의 데이터를 가져올지 지정하고,

WHERE에 필요한 데이터의 조건문을 정하고 더 필요한 조건이 있을 시 'AND' 키워드로 연결한다.

ORDER BY로 데이터의 정렬 순서를 순차적(기본값, ASC)으로 할 지 역순(DESC)으로 정하고

GROUP BY로 중복된 컬럼을 묶는 역할을 한다. (GROUP BY시 데이터의 조회가 일부 되지 않을 수도 있으므로 주의필요!!)

LIMIT은 데이터의 가져오는 개수를 제한하는 것으로 코드로 표현하면 아래와 같다.

쉽게 말해서 가져온 데이터들의 시작 인덱스와 개수를 정해준다.

 

조회에서 가장 중요한 부분은 WHERE 절에서의 INDEX 사용 유무이다. 

테이블의 크기가 커지면 담긴 데이터가 많다는 의미인데, 이럴 때 INDEX를 정해놓지 않은 상태로 사용하게 되면 조회를 하는데 상당히 오랜 시간이 걸리게 된다. 그래서 주로 WHERE 문의 컬럼은 INDEX에 맞춰서 사용하게 된다.

---

INSERT

 

없는 Row를 생성하기 위한 용도이고 여기에도 SELECT 옵션을 추가할 수 있다.

기본적인 포맷은 아래와 같다.

INSERT INTO tableName (`column1`, `column2`, `column3`) VALUES ('1', 'abbo', '2019-12-15 13:00:00'), ('2', 'abbo', '2019-12-15 14:00:00');

INSERT 에서 주의할 점은 PRIMARY KEY로 설정된 컬럼과 중복된 값을 넣게 되면 오류가 발생한다. 

이는 데이터베이스의 기본 개념 중 '고유성'에 위배되기 때문이다.

이를 해결하기 위해 IGNORE 옵션을 종종 추가하게 된다.

 

INSERT IGNORE INTO tableName (`column1`, `column2`, `column3`) VALUES ('1', 'abbo', '2019-12-15 13:00:00'), ('1', 'abbo', '2019-12-15 14:00:00');

이렇게 쿼리를 작성하면 에러가 나지 않고 1건만 입력이 된다.

 

tableName에 데이터를 입력할 테이블을 선택하는데, 이 다음에 컬럼들이 나열된 곳에 테이블에서 가지고 있는 컬럼명이 아니면 오류가 난다. 위 컬럼 순서에 맞게 VALUES 뒤로 데이터를 각각 입력하게 되고 위의 쿼리를 수행하면 아래와 같이 데이터가 입력되게 된다.

 

column1	column2	column3
1	abbo	2019-12-15 13:00:00
2	abbo	2019-12-15 14:00:00

입력되는 데이터의 타입 확인도 필요한데, VARCHAR 타입인 경우는 쿼터(')를 붙여서 구분하게 된다. 입력되는 데이터 중 쿼터가 들어가게 되면 이스케이프 문자(\)를 넣어주어야 한다. ('\'' 의 형태로 넣는다.)

 

다른 테이블에 존재하는 데이터를 넣고 싶다면 SELECT를 INSERT 문에 넣어줄 수 있다.

INSERT INTO tableName (`column1`, `column2`)  SELECT column3, column4 FROM otherTable WHERE column3 > 1;

이렇게 쿼리를 작성하게 되면 tableName 테이블에 otherTable에 존재하는 데이터 중

column3의 값을 column1에, column4의 값을 column2에 각각 넣어주는 쿼리가 작성된다. 

 

---

DELETE

 

순서 상 UPDATE를 하는 것이 맞지만 DELETE가 더 간단하므로 먼저 진행하려고 한다.

기존 데이터를 삭제하기 위한 용도이고, 물리삭제이므로 복구할 수 없다.

DELETE FROM tableName WHERE column1 > 5;

column1의 값이 5보다 큰 데이터는 tableName 테이블에서 전부 삭제되고,

WHERE 조건은 SELECT 문의 WHERE절의 내용을 그대로 따른다.

 

---

UPDATE

 

기존의 데이터를 수정하기 위한 용도로 호출되는데 1.DELETE, 2.INSERT가 순차적으로 한번에 실행되는 문이라고 생각하면 된다.

UPDATE tableName SET column1 = column1+1, column2 = 'abbo2', column3 = NOW() WHERE column1 > 5;

위의 쿼리를 수행하면 column1이 5 초과인 데이터들에 한해

column1 의 값을 기존이 가지고 있던 값에서 +1을 시키고, column2는 'abbo2'라는 값으로 변경된다.

column3에는 MySQL 기준으로 NOW() 함수를 제공하는데 이는 현재 시간이라는 의미이며, 현재 시간으로 데이터가 수정된다.

 

---

DCL(Data Control Language)

데이터에 대한 사용/수정 권한을 부여하는 용도로 사용하는데, 거의 대부분의 서버 개발자의 경우 관리자의 권한으로 실행되는 경우가 많아 자주 사용하지는 않는다. 규모가 큰 회사인 경우 인력이 들어오거나 나가는 경우 각자 아이디를 하나씩 가지고 있는데 이러한 경우 사용한다.

 

GRANT

 

권한을 부여하는 질의문으로 관리자의 경우 아래와 같이 자주 사용한다.

GRANT ALL PRIVILEGES ON 'dbName'.'tableName' to 'userId'@'hostName' IDENTIFIED BY 'password';

userId라는 사용자에게 데이터베이스.테이블에 대한 모든 사용 권한을 부여하며 사용자에게 password라는 비밀번호를 지정한다.

모든 테이블에 대한 권한은 'dbName'.'tableName' 대신 'dbName'.* 을 부여하면 되고, 모든 데이터베이스도 '*.*'로 쓸 수 있다.

 

외부에서 제어가 가능하도록 옵션을 주는 것은 'userId'@'%'로 지정 가능하다.

GRANT를 수행하였다면 다음과 같은 쿼리를 호출해주어야 최종 반영이 된다.

FLUSH PRIVILEGES;

권한을 조회하는 것은 SHOW GRANTS이고, 삭제하는 것은 REVOKE이다.

SHOW GRANTS FOR 'userId'@'host' REVOKE ALL ON 'dbName'.'tableName' FROM 'userId'@'host'

---

COMMIT, ROLLBACK

 

위에서 권한을 부여하기 위해 FLUSH를 수행하였던 것처럼,

DML을 수행하기 위해서는 COMMIT 또는 ROLLBACK이 수행되어야 한다.

보통 자동으로 적용되는 경우가 많지만 그렇지 않고 일일히 수행되어야 하는 경우는 개발 툴에 맞게 옵션을 걸어주어야 한다.

COMMIT; ROLLBACK;

COMMIT 은 기존 트랜잭션에 걸려있는 INSERT, UPDATE, DELETE 되었던 데이터들에 대한 수행을 진행하며,

ROLLBACK 은 이를 취소한다.

 

트랜잭션은 데이터베이스의 수행 단위이며, DML 그룹이 COMMIT이나 ROLLBACK 될때마다 한 번씩 수행된다.

 

---

마치며, 데이터베이스의 역사는 정말 오래된 것 만큼 이 글에서 다루지 않은 내용이 더 많고, 자주 사용하는 SQL문들이라고 생각하여 정리를 해보았다.

 

현재 NoSQL이 많은 국가들에서 사용중인 기술이긴 하지만 아직까지 우리 나라의 특성상 Database Query를 많이 사용하고 있고,

NoSQL의 기본 원리 또한 SQL에서 나온 것이 대부분이므로 서버 개발자들이라면 알아야 하는 부분중에 하나라고 생각한다.