[Web] SQL(DDL, DML)

SQL

SQL(Structured Query Language)은 관계형 데이터베이스에서 데이터를 관리하기 위한 쿼리 언어로 대부분의 프로그래밍 언어보다는 단순한 구조를 가지고 있다. SQL 자체는 표준 언어이지만 데이터베이스마다 세부적인 차이가 있을 수 있기 때문에 특정 데이터베이스만 대상으로 개발된 프로그램의 경우 다른 데이터베이스와 호환이 되지 않을 수도 있으니 주의해야 한다.

SQL은 단순히 데이터 관련 작업 이외에 데이터베이스 자체의 관리 기능 수행에도 사용된다.

SQL에서 할 수 있는 일은 다음과 같다.

• 새로운 테이블 생성
• 내장 프로시저(Stored Procedure) 생성
• 뷰 생성
• 테이블, 프로시저, 뷰 등의 접근 권한 부여
• 데이터베이스에 대해 쿼리 실행
• 데이터베이스로부터 데이터 조회
• 데이터베이스에 기록 삽입, 갱신, 삭제
• 새로운 데이터베이스 생성

SQL의 유형

SQL 문은 크게 DDL(Data Definition Language)과 DML(Data Manipulation Language)로 나눌 수 있다. DDL은 테이블의 생성, 수정, 삭제와 같은 관리 기능을 제공하는 SQL 문이며, DML은 테이블의 데이터를 조작할 때 사용하는 SQL 문을 의미한다.

기본 SQL의 유형

DDL은 스키마, 테이블, 시퀀스, 인덱스, 사용자, 권한 객체를 생성하고 관리하기 위한 명령으로 구성되고, DML은 데이터 조작의 기본 기능인 CRUD(Create, Read, Update, Delete)와 관계된 명령으로 이루어져 있다.

H2의 자료형

테이블 칼럼에 들어가는 데이터의 유형을 자료형으로 지정해야 하며 칼럼의 데이터는 모두 동일한 타입을 가진다. 데이터의 유형은 숫자형, 문자형, 날짜형, 바이너리형, 텍스트형 등으로 구분할 수 있지만 구체적인 이름은 데이터베이스마다 차이가 있다.

H2의 대표적인 자료형은 다음과 같다.

자료형	종류	크기
INT	정수형	32bit
DOUBLE	실수형	64bit
DATE	날짜형	날짜와 시간을 포함
CHAR	문자열	고정길이로 INT 범위
VARCHAR	문자열	가변길이로 INT 범위
CLOB	문자열	INT 범위를 넘어서는 대용량 문자열
BLOB	바이너리형	대용량 바이너리형

MySQL의 경우 H2의 자료형과 상당히 비슷하지만 H2보다 세분화된 자료형을 제공하며, TINYINT, SMALLINT, MEDIUMTEXT, LONGBLOB과 같이 계열형 구조를 가진다.

SQL의 기본 규칙 

SQL 구문은 명령, 식별자(테이블 이름, 칼럼 이름 등), 데이터 타입, 조건절 등으로 구성되며 함수나 내장 프로시저 호출, 서브쿼리 등을 포함한다. 기본적인 규칙을 다음과 같다.

• 모든 SQL 구문과 식별자는 대소문자를 구분하지 않는다.(데이터베이스에 따라 구분하는 경우도 있다.)
• 명령과 키워드는 대문자, 식별자는 정해진 이름 규칙에 따를 것을 권장한다.
• 식별자는 영문으로 작성하며 공백을 허용하지 않는다.
• 서술적인 접두어 사용은 자제한다(예: tbl_mamber, idx_, pk_).

DDL

DDL(Data Definition Language)은 테이블의 생성, 수정, 삭제와 같은 관리 기능을 제공하는 SQL 문을 의미한다. DDL을 통해 데이터베이스 스키마, 테이블, 인덱스 등 데이터 저장 및 운영을 위한 객체의 생성과 관리가 가능하다. 이는 데이터 구조와 관련된 부분이므로 프로그램 영역이 아니라 데이터베이스 관리의 영역으로 여겨지며 데이터베이스 관리 도구나 SQL 실행 환경에서 주로 사용한다.

CREATE

테이블을 생성할 때 사용하는 명령어이며 각 칼럼의 자료형과 최대 크기를 명시해야 한다. 필요에 따라 칼럼에 제약 조건과 속성을 추가할 수 있다. 마지막 칼럼 설정 뒤에 ','를 넣지 않도록 주의한다.

CREATE TABLE 테이블_이름 (
    칼럼_이름 자료형(크기) 제약조건|속성,
    칼럼_이름 자료형(크기),
    ...
)

ALTER

테이블 구조를 수정할 때 사용하는 명령어다. 테이블의 수정은 가능하지만 데이터가 들어가 있는 상태에서는 구조 변경에 여러 제약이 따른다. 변경된 구조로 인해 관련 프로그램의 실행에도 문제가 발생할 수 있기 때문이다. 따라서 테이블을 수정하기 전에 신중히 테이블을 생성하는 것이 좋다.

ALTER TABLE 테이블_이름 [ADD | ALTER | DROP] 칼럼명 자료형 제약 조건

테이블에 데이터가 들어가 있을 때 수정 제약 사항은 다음과 같다.

• 칼럼의 자료형은 변경할 수 없다.
• 칼럼의 크기(해당 칼럼에 저장할 수 있는 데이터의 크기)를  줄일 수는 없고 늘리는 것만 가능하다.
• NOT NULL 속성을 갖는 필드는 추가할 수 있으나, NULL 속성이 있는 필드는 추가할 수 없다.

DROP

테이블 자체를 삭제하는 명령이다. 테이블을 삭제할 때는 데이터와 함께 테이블과 연관되어 정의된 인덱스, 룰, 트리거, 제약 조건도 함께 삭제되므로 주의해야 한다.

DROP TABLE 테이블_이름 [RESTRICT | CASCADE]

• RESTRICT: 기본값으로 삭제 테이블이 다른 곳에서 참조되고 있다면 삭제를 중지한다.
• CASCADE: 삭제 테이블과 의존관계가 있는 모든 개체를 함께 삭제한다.

DROP TABLE student
DROP TABLE student CASCADE

SHOW

테이블 정보를 조회하기 위해서는 데이터베이스마다 제공되는 별도의 명령 혹은 스키마 구조에 접근하는 쿼리를 사용해야 한다. H2에서는 다음과 같이 SHOW 명령어를 이용하여 테이블 정보를 확인할 수 있다.

SHOW COLUMNS FROM student

DML

DML(Data Manipulation Language)은 테이블의 데이터를 조작할 때 사용하는 SQL 문으로 데이터의 입력, 수정, 삭제, 검색 등에 사용된다. 주로 프로그램 코드 안에서 사용하는 쿼리문이다.

INSERT

테이블에 데이터를 추가하기 위한 명령어다. 일부 칼럼의 값만 추가할 수도 있고 테이블의 모든 칼럼에 해당하는 값을 추가할 수도 있다.

전체 칼럼값을 모두 추가할 때 VALUES에 오는 값의 순서는 테이블을 생성할 때 지정한 칼럼 순서와 반드시 일치해야 한다.

// 전체 칼럼값을 모두 저장하는 경우
INSERT INTO 테이블_이름 VALUES(칼럼에 넣을 데이터)

INSERT INTO student VALUES('김길동','AA대학교','1999-10-21','kim@aa.com')

부분 칼럼 데이터만 저장하는 경우 NOT NULL 칼럼은 반드시 포함되어야 하며 auto_increment 속성이 적용될 칼럼은 비워두면 된다.

// 특정 칼럼값만 저장하는 경우
INSERT INTO 테이블_이름(칼럼_이름1, 칼럼_이름2...) VALUES(칼럼에 넣은 데이터)

INSERT INTO student(username, email) VALUES('김길동','kim@aa.com')

숫자형 값은 그냥 숫자만 사용하면 되지만 문자형은 반드시 작은따옴표로 감싸야 한다.

SELECT

테이블에서 데이터를 조회하기 위한 명령어다. 전체 데이터 혹은 조건에 맞는 데이터만 조회가 가능하다. 데이터베이스에서 제일 중요한 쿼리이며, 효율적인 조회를 위해서는 다음과 같은 작업이 필요하다.

• 여러 테이블의 데이터를 조합해서 조회하기
• 외래키 칼럼의 코드 데이터를 참조 테이블의 이름 칼럼으로 대체하기
• 날짜 형식 변경하기
• 데이터 정렬 또는 집계하기

위와 같은 작업이 쿼리 내에서 함께 이루어져야 하므로 잘 사용하기 위해서는 추가적인 학습이 필요하다.

// 단일 테이블의 조회
SELECT [ALL | DISTINCT] 칼럼명(혹은 *)..		// *은 전체 조회
FROM 테이블_이름
[ WHERE 조건절 ]
[ GROUP BY 칼럼명 ]
[ HAVING 검색조건 ]
[ ORDER BY [ ASC | DESC ]

• 특정 칼럼을 지정하거나 전체 칼럼(*)을 조회할 수 있다.
• DISTINCT: 중복된 값은 제거하고 가지고 온다.
• WHERE: 검색 조건을 지정할 때 사용한다.
• GROUP BY: 특정 칼럼을 그룹화할 때 사용한다.
• HAVING: 특정 칼럼을 그룹화한 결과에 조건을 설정할 때 사용한다.
• ORDER BY: 특정 칼럼을 기준으로 오름차순(ASC)/내림차순(DESC) 정렬할 때 사용한다.

WHERE 조건절의 사용
조건절에서는 일반적인 비교연산, 논리연산과 LIKE 연산 등이 가능하다. LIKE 검색의 경우 %를 이용해 특정 단어를 포함하거나 특정 단어로 시작 또는 끝나는 데이터를 검색할 수 있으나 칼럼의 데이터를 모두 비교하기 때문에 별도의 인덱스 설정이 필요하다. 하지만 조건절을 남용할 경우 성능 저하의 원인이 될 수 있으니 사용에 주의한다.

데이터 조회 함수

데이터베이스에는 데이터 조회의 편의를 위해 몇몇 함수가 기본적으로 제공되며 데이터베이스마다 전용 함수가 제공되기도 한다. 아래 예제들은 대부분의 데이터베이스에서 제공되는 유용한 함수이다.

테스트를 위해 실제 테이블의 데이터를 사용하는 것이 아니기 때문에 가상의 테이블인 DUAL과 SELECT 문을 사용한다.

DUAL은 간단하게 함수를 이용하여 계산 결괏값을 확인할 때 사용하는 가상 테이블이다.

숫자 관련 함수

숫자를 조작하기 위함 함수로 ABS(절댓값), CEILING(올림), ROUND(반올림), FLOOR(버림), SQRT(제곱근) 등이 있다.

SELECT ABS(-20), CEILING(20.25), ROUND(20.25), FLOOR(20.25), SQRT(4) from dual
// dual : 임의의 가상 테이블

결과

20

21

20

20

2.0

문자 관련 함수

문자, 문자열을 조작하기 위한 함수로 ASCII(아스키코드값), LENGTH(길이), CONCAT(문자열 결합), TRIM(양쪽 공백 제거), LOWER(소문자 변환), UPPER(대문자 변환), SUBSTRING(부분 선택) 등이 있다.

SELECT ASCII('A'), LENGTH('HELLO'), CONCAT('Hello', 'World'), TRIM('Hello World'),
       LOWER('ABC'), UPPER('abc'), SUBSTRING('HelloWorld' FROM 2 FOR 5) from dual

결과

65

5

'HelloWorld'

'Hello World'

'abc'

'ABC'

'elloW'

날짜/기간 함수

NOW(현재 날짜 시간), CURRENT_TIMESTAMP(현재 날짜 시간), DAYNAME(요일), PARSEDATETIME(문자열 포맷을 날짜 시간 정보로 변환) 등이 있다.

SELEcT NOW(), CURRENT_TIMESTAMP(), DAYNAME(now()),
       PARSEDATETIME('10-25-2022', 'MM-dd-yy', 'GMT') from dual

집계함수

COUNT(레코드 수), SUM(칼럼값 더하기), AVG(칼럼값 평균), MAX(칼럼 최댓값), MIN(칼럼 최솟값)

SELECT COUNT(id), SUM(id), AVG(id), MAX(id), MIN(id) FROM student

조인

조인(Join)은 관계형 데이터베이스에서 2개 이상의 테이블이나 데이터베이스를 조합해 데이터를 검색하는 것을 말한다. 조회하고자 하는 컬럼이 서로 다른 테이블에 있을 경우 주로 사용하며 여러 개의 테이블을 마치 하나의 테이블인 것처럼 사용할 수 있는 방법이다.

보통 기본키(PK)와 외래키(FK)로 연결된 두 테이블의 데이터를 조합하기 위해 사용할 수 있다.

조인은 여러 유형이 있으며 조인을 통해 여러 번 쿼리를 보내거나 결과를 프로그램에서 조합할 필요 없이 한 번의 쿼리로 원하는 데이터 구조를 받아볼 수 있다.

조인 형태로는 Inner Join, Outer Join, Cross Join, Self Join 등이 있다.

서브쿼리
SQL 관련 고급 기법 중 조인과 함께 서브쿼리를 꼽을 수 있다. 서브쿼리는 하나의 SQL 문에 다른 SQL 문을 포함하는 것을 말한다. 특정 쿼리의 실행 결과를 통해 조회된 값을 다른 쿼리에서 활용하는 형태로, 조인과 마찬가지로 프로그램에서 여러 번 수행해야 하는 작업을 덜어줄 수 있지만 성능 문제와 함께 복잡한 쿼리 구조로 인해 오류 발생 가능성 및 유지보수 문제가 있을 수 있다.

http://www.h2database.com/html/commands.html
→H2 데이터베이스의 전체 쿼리 참조

http://www.h2database.com/html/functions.html
→H2 데이터 베이스에서 지원되는 전체 함수