11.3 SQL
- SQL(Structured Query Language) 개요
- DDL(Data Definition Language)
- DML(Data Manipulation Language)
- 함수(Standard funtions)
- 조인(Join)
- 서브쿼리(Sub Query)
- 실습: SQL 종합 예제
이번 강좌에서는 관계형 데이터베이스의 기본 쿼리 언어인 SQL이 무엇인지 살펴보고 기본 문법과 주요 구문의 사용법을 배운다.
이 강의를 통해 표준 SQL을 사용해 테이블을 생성하고 데이터를 관리할 수 있으며 원하는 조건의 데이터를 효과적으로 검색 할 수 있게 된다.
SQL(Structured Query Language) 개요
SQL은 관계형 데이터베이스에서 데이터를 관리하기 위한 쿼리 언어로 일반적인 프로그램 언어보다는 단순한 구조를 가지고 있다. SQL 자체는 표준이지만 세부적으로는 데이터베이스마다 차이가 있을수 있기 때문에 특정 데이터베이스만을 대상으로 개발된 프로그램의 경우 다른 데이터베이스와 호환이 되지 않을수도 있으니 참고 바란다.
SQL은 단순히 데이터 관련 작업 이외 데이터베이스 자체의 관리 기능 수행에도 사용되며 일반적으로 할수있는 일은 다음과 같다.
- 데이터베이스에 새로운 테이블 생성
- 데이터베이스에 stored procedures를 생성
- 데이터베이스에 뷰를 생성
- 테이블, 프로시져, 뷰등의 접근 권한 부여
- 데이터베이스에 대해 쿼리를 실행
- 데이터베이스로 부터 데이터를 조회
- 데이터베이스에 기록을 삽입, 갱신, 삭제
- 새로운 데이터베이스 생성
SQL 유형
SQL문은 크게 DML(Data Manipulation Language)과 DDL(Data Definition Language)로 나눌 수 있다. DML은 테이블의 데이터를 조작할 때 사용하는 SQL문이며, DDL은 테이블의 생성, 수정, 삭제와 같은 관리 기능을 제공하는 SQL문을 말한다.
DML은 데이터조작의 기본 기능인 CRUD(Create, Read, Update, Delete)와 관계된 명령들로 구성되고 DDL은 스키마, 테이블, 시퀀스, 인덱스, 사용자, 권한 객체들을 생성하고 관리하기 위한 명령들로 이루어져 있다.
여기서는 테이블을 중심으로 살펴보고 전체 쿼리는 http://www.h2database.com/html/commands.html 에서 참고 바란다.
자료형
본격적인 SQL 구문을 살펴보기 전에 H2 의 기본 자료형에 대해 살펴본다.
테이블 컬럼에 들어가는 데이터의 유형을 자료형으로 지정해야 하며 컬럼의 데이터는 모두 동일한 타입을 가지게 된다. 데이터의 유형은 숫자형, 문자형, 날짜형, 바이너리형, 텍스트형등으로 구분할 수 있지만 구체적인 이름은 데이터베이스 마다 차이가 있다. 여기서는 H2 데이터베이스를 기준으로 하며 MySQL의 경우 H2와 거의 동일 하다.
대표적인 데이터타입은 다음과 같다.
| 자료형 | 종류 | 크기 | MySQL |
|---|---|---|---|
| INT | 정수형 | 32bit | INT 계열 |
| DOUBLE | 실수형 | 64bit | DOUBLE |
| DATE | 날짜형 | 날짜와 시간을 포함 | DATETIME |
| CHAR | 문자열 | 고정길이로 INT범위 | CHAR, 최대 255 |
| VARCHAR | 문자열 | 가변길이로 INT범위 | VARCHAR, 최대 5,536 |
| CLOB | 문자열 | INT범위를 넘어서는 대용량 문자열 | TEXT 계열 |
| BLOB | 바이너리형 | 대용량 바이너리형 | BLOB 계열 |
H2 데이터베이스의 전체 자료형은 http://www.h2database.com/html/datatypes.html를 참고 바란다.
SQL 기본 규칙
SQL 구문은 명령, 식별자(테이블명, 컬럼이름 등), 데이터타입, 조건절 등으로 구성되며 함수나 stored procedure 호출, 서브쿼리등을 포함하며 기본적인 규칙은 다음과 같다.
- 모든 SQL 구문과 식별자는 대소문자를 구분하지 않지만 데이터베이스에 따라 구분하는 경우도 있다.
- 명령과 키워드는 대문자, 식별자는 정해진 이름 규칙에 따를것을 권장.
- 식별자는 영문으로 작성하며 공백을 허용하지 않는다.
- 키워드는 식별자로 사용할 수 없다.
- 서술적인 접두어 사용은 자제(tbl_member, idx_, pk_ 등)
그외 보기좋고 이해하기 쉬운 코드를 작성하기 위한 일반적인 규칙을 따르면 된다.
DDL(Data Definition Language)
DDL을 통해 데이터베이스 스키마, 테이블, 인덱스 등 데이터 저장 및 운영을 위한 객체들의 생성과 관리가 가능하다. 객체의 생성은 create, 수정은 alter, 삭제는 drop 이라는 명령을 사용하며 대소문자 구분은 없다. 여기서는 테이블을 예로 각 명령의 사용법을 살펴본다.
DDL은 데이터 구조와 관련된 부분이므로 프로그램 영역이 아니라 데이터베이스 관리의 영역으로 봐야 하며 데이터베이스 관리 도구나 SQL 실행 환경에서 주로 사용하게 된다.
CREATE
테이블을 생성할때 데이터형과 최대 크기를 명시해야 한다. 마지막 컬럼 뒤에 “,”를 넣지 않도록 주의 한다.
CREATE TABLE 테이블_이름 (
칼럼_이름 데이터형(크기) 제약조건|속성,
칼럼_이름 데이터형(크기),
...
)
단순히 새로운 테이블을 생성하는것 이외 다른 테이블에 있는 일부 컬럼과 데이터로 새로운 테이블을 생성하는 것도 가능하다.
CREATE TABLE 새로운_테이블 AS
SELECT 컬럼1, 컬럼2
FROM 기존_테이블
다음은 테이블 생성 예 이다.
// 새로운 학생정보 테이블 생성
CREATE TABLE student (
id INT NOT NULL Primary Key AUTO_INCREMENT,
username VARCHAR(20),
univ VARCHAR(40),
birth DATE,
email VARCHAR(40)
)
// 학생정보 테이블로 부터 이름과 이메일로만 구성되는 임시 테이블 생성
CREATE TABLE student_tmp as
SELECT username, email
FROM student
권장되는 방법은 id 컬럼의 자료형을 IDENTITY로 설정하는 것이며 이경우 NOT NULL Primary Key AUTO_INCREMENT가 자동으로 적용된다. 타입은 BIGINT로 설정 된다.
ALTER
테이블 수정은 가능하지만 데이터가 들어가 있는 상태에서는 구조 변경에 여러 제약이 따르고 변경된 구조로 인해 관련 프로그램들의 실행도 문제가 될 수 있기 때문에 항상 주의해야 한다.
테이블에 데이터가 들어가 있을 경우 수정 가능한 범위는 다음과 같다.
- 컬럼의 자료형 변경은 불가.
- 크기를 줄일 수는 없고 늘리는것만 가능.
- NOT NULL 속성을 갖는 필드는 추가 가능, NULL 속성이 있는 필드는 추가 불가.
ALTER TABLE 테이블_이름 [ADD | ALTER | DROP] 컬럼명 자료형 제약조건
다음은 다양한 테이블 정보 수정 예이다.
ALTER TABLE student ADD (tel varchar(30) NOT NULL);
ALTER TABLE student ALTER username varchar(10);
ALTER TABLE student DROP PRIMARY KEY;
DROP
테이블 자체를 삭제하면 데이터와 함께 인덱스, 룰, 트리거, 제약조건도 함께 삭제되므로 주의 하도록 한다.
DROP TABLE 테이블_이름 [RESTRICT | CASCADE]
- RESTRICT: 기본값으로 삭제 테이블이 다른곳에서 참조되고 있다면 삭제 중지
- CASCADE: 삭제 테이블과 의존관계가 있는 모든 개체도 함께 삭제
DROP TABLE student
DROP TABLE student CASCADE
DROP TABLE student, univ_code
테이블 정보를 조회하기 위해서는 데이터베이스 마다 제공되는 별도의 명령 혹은 스키마 구조에 접근하는 쿼리를 사용해야 한다. H2 의 경우 다음과 같이 테이블 정보 확인이 가능하다.
SHOW COLUMNS FROM student
DML(Data Manipulation Language)
데이터의 입력, 수정, 삭제, 검색등에 사용되는 쿼리로 주로 프로그램 코드 안에서 사용하게 되는 쿼리문 이다.
INSERT
테이블에 데이터를 추가하기 위한 명령이다. 일부 컬럼의 값만 저장할수도 있고 테이블의 모든 컬럼에 해당하는 값을 저장할수도 있다.
전체 컬럼값을 모두 저장할때 values 에 오는 값의 순서는 테이블 생성시 지정한 컬럼 순서와 반드시 일치해야 한다.
숫자형 값은 그냥 숫자만 사용하면 되지만 문자형은 반드시 작은따옴표로 감싸야 한다.
// 특정 컬럼값만 저장하는 경우
INSERT INTO 테이블_이름(칼럼_이름1, 컬럼_이름2...) VALUES(칼럼에 넣을 값...)
// 전체 컬럼값을 모두 저장하는 경우
INSERT INTO 테이블_이름 VALUES(칼럼에 넣을 값...)
다음은 다양한 INSERT 사용 예이다.
INSERT INTO student values('김길동','AA대학교','1999-10-21','kim@aa.com')
INSERT INTO student(username, email) values('김길동','kim@aa.com')
부분 컬럼 데이터만 저장하는 경우 not null 컬럼은 반드시 포함되어야 하며 auto_increment 속성이 적용된 컬럼은 명시해서 저장하지 않는다.
SELECT
테이블에서 데이터를 조회하기 위한 명령이다. 전체 데이터 혹은 조건에 맍는 데이터만 조회하는 것이 가능하다. 데이터베이스에서 제일 중요한 쿼리이며 효율적인 조회를 위해서는
- 여러 테이블의 데이터를 조합
- FK 컬럼의 코드 데이터를 참조 테이블의 이름 컬럼으로 대체
- 날짜 형식을 변경
- 데이터 정렬 이나 집계
등의 작업이 쿼리내에서 함께 이루어져야 하므로 잘 사용하기까지는 추가적인 학습이 필요하다. 여기서는 우선 단일 테이블의 조회만 살펴보도록 한다.
SELECT [ALL | DISTINCT] 컬럼명(혹은 *)..
FROM 테이블_이름
[ WHERE 조건절 ]
[ GROUP BY 컬럼명 ]
[ HAVING 검색조건 ]
[ ORDER BY 컬럼명 [ ASC | DESC ]
- 특정 컬럼을 지정하거나 전체컬럼(*) 조회가 가능
- DISTINCT는 중복된 값은 제거하고 가지고옴
- WHERE 를 이용해 검색 조건 지정
- GROUP BY는 특정 컬럼을 그룹화
- HAVING은 특정 컬럼을 그룹화한 결과에 조건 설정
- ORDER BY 는 특정 컬럼을 기준으로 내림/오름 차순 정렬
조건절에서는 일반적인 비교연산, 논리연산과 LIKE 연산등이 가능하다.
다음은 가장 기본적인 사용 예 이다.
SELECT * FROM student
SELECT username, email FROM student
WHERE 조건절을 사용한 예는 다음과 같다.
SELECT * FROM student WHERE username = '홍길동'
SELECT * FROM student WHERE username == '홍길동' AND univ == 'AA대학교'
SELECT * FROM student WHERE univ == 'AA대학교' OR univ = 'CC대학교'
SELECT * FROM product WHERE price BETWEEN 1000 AND 2000
// 홍으로 시작하는 모든 데이터 조회
SELECT * FROM student WHERE username LIKE '홍%'
LIKE 검색의 경우 %를 이용해 특정 단어를 포함하거나 시작 혹은 끝나는 데이터를 검색할 수 있으나 컬럼의 데이터를 모두 비교해 봐야 하기 때문에 별도의 인덱스 설정이 필요하며 남용시 성능저하의 원인이 될 수 있으니 사용에 주의 한다.
ORDER BY 를 사용한 예는 다음과 같다.
// 이름을 내림차순으로 정렬
SELECT * FROM student ORDER BY username DESC
// 학생이름을 올림차순으로 하고 대학은 내림차순으로
SELECT * FROM student ORDER BY username ASC, univ DESC
다은은 DISTINCT 를 사용한 예이다.
// 중복된 이름은 하나만 가지고옴
SELECT DISTINCT username FROM student
// 이름과 대학의 조합으로 중복 제거
SELECT DISTINCT username, univ FROM student
함수(Standard funtions)
데이터베이스에는 데이터 조회의 편의를 위해 몇몇 함수가 기본적으로 제공되며 데이터베이스 마다 전용의 함수들이 제공되기도 한다. 여기서는 대부분의 데이터베이스에서 제공되는 유용한 함수들을 중심으로 살펴본다.
H2 데이터베이스에서 지원되는 전체 함수는 http://www.h2database.com/html/functions.html 를 참고 바란다.
숫자관련 함수
숫자를 조작하기 위한 함수로 ABS(절대값), CEILING(올림), ROUND(반올림), FLOOR(버림) , SQRT(제곱근)등이 있다.
SELECT ABS(-20), CEILING(20.25) as CE, ROUND(20.25), FLOOR(20.25), SQRT(4) from dual
-> 20 , 21.0 , 20.0 , 20.0 , 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-01-2020','MM-dd-yy','GMT') from dual
-> 2020-11-08 17:27:39.865652 , 2020-11-08 17:27:39.865652+09 , Sunday , 2020-10-01 00:00:00
집계 함수
COUNT(레코드 수), SUM(컬럼값 더하기), AVG(컬럼값 평균), MAX(컬럼 최고값), MIN(컬럼 최소값)
SELECT count(score), SUM(score), AVG(score), MAX(score), MIN(score) FROM student
-> 7 , 586 , 83 , 97 , 65
조인(Join)
조인은 관계형데이터베이스에서 두개이상의 테이블이나 데이터베이스를 조합해 데이터를 검색하는 것을 말한다. 조회하고자 하는 컬럼이 서로 다른 테이블에 있을 경우 주로 사용하며 여러개의 테이블을 마치 하나의 테이블인 것처럼 사용할 수 있는 방법이다.
보통 PK 와 FK로 연결된 두 테이블의 데이터를 조합하기 위해 사용할 수 있다. 조인은 여러 유형이 있으며 조인을 통해 여러번 쿼리를 보내거나 결과를 프로그램에서 조합할 필요 없이 한번의 쿼리로 원하는 데이터 구조를 받아볼 수 있다.
일반적인 조인의 형태는 Inner Join, Outter Join, Cross Join, Self Join 등이 있습니다.
Inner Join
결합된 테이블에 공통으로 들어가 있는 조건에 일치하는 결과 집합을 만든다. ON 다음에 들어가는 조건에 맞는 내용들만 보여주게 된다. 두개의 테이블을 구분해 컬럼정보등을 사용하기 위해 AS 를 이용해 별칭을 만든다. INNER JOIN은 생략 가능하며 ON 대신 WHERE 를 사용할수도 있다.
SELECT 열 목록
FROM 첫번째 테이블 [AS 별칭] INNER JOIN 두번째 테이블 [AS별칭]
ON(join_condition)
SELECT s.username, u.uname, s.email
FROM student AS s
INNER JOIN univ_code AS u
ON s.univ = u.ucode
ORDER BY s.id
SELECT s.username, u.uname, s.email
FROM student AS s, univ_code AS u
WHERE s.univ = u.ucode
ORDER BY s.id
-> 김길동 , AA대학교 , kim@aa.com
Outter Join
INNER JOIN 문을 포함하고 한쪽에만 내용이 있더라도 지정한 기준 테이블에 있는 모든 데이터를 가져오는 조인방식
SELECT 열목록
FROM 첫번째 테이블
<LEFT | RIGHT | FULL> OUTER JOIN 두번째 테이블
ON(join_condition)
[WHERE 검색조건]
Cross Join
결과값이 한쪽 테이블의 모든행들과 다른쪽 테이블의 모든 행을 조인시킨다. 결과 집합은 두 테이블의 개수를 곱한 값만큼 생성되며, 조인되는 테이블에 공통되는 행이 없어도 되며 조건절인 ON 키워드가 사용되지 않는다.
SELECT M.회원이름, B.구매한도서, B.작가, B.구매시기
FROM 회원정보 AS M
CROSS JOIN 구매내역 AS B
ORDER BY M.회원번호
Self Join
하나의 테이블에 같은 데이터가 존재하는데 그 의미가 다르게 존재하는 경우. 즉, 같은 데이터이지만 다른 열에 있는 경우에는 두 테이블을 서로 SELF JOIN 문으로 확인가능
SELECT A.회원이름, A.주거지역, A.연락처, B.회원이름 AS 추천인, B.연락처
FROM 회원정보 AS A
INNER JOIN 회원정보 AS B
ON A.추천한 회원 = B.회원번호
WHERE A.회원이름 = '김군'
서브쿼리(Sub Query)
서브쿼리는 하나의 SQL문에 다른 SQL문을 포함하는 것을 말합니다. 특정 쿼리의 실행결과를 통해 조회된 값을 다른 쿼리에서 활용하는 형태로 조인과 마찬가지로 프로그램에서 여러분 수행할 쿼리를 줄여줄 수 있지만 성능문제와 함께 복잡한 쿼리 구조로 인해 오류 발생 가능성 및 유지 보수에 문제가 있을 수 있다.
서브쿼리 사용시 주의사항
- 서브쿼리를 괄호로 감싸서 사용한다.
- 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다.
- 서브쿼리에서는 ORDER BY 를 사용하지 못한다.
서브쿼리가 사용 가능한 곳
- SELECT 절
- FROM 절
- WHERE 절
- HAVING 절
- ORDER BY 절
- INSERT 문의 VALUES 절
- UPDATE 문의 SET 절
실습: SQL 종합 예제
데이터베이스 이해 및 JDBC API를 사용해 자바에서 데이터베이스 연동 프로그램 개발을 배우기 위해 H2 데이터베이스를 설치 합니다.
H2 데이터베이스에 대한 간략한 소개와 설치는 다음 강좌를 참고 합니다.
- https://ko.wikipedia.org/wiki/Join_(SQL)
- https://velog.io/@josworks27/데이터베이스-기초-Query-문법