SQL은 DBMS[1]마다 기본 적인 문법은 같지만 세부적인 부분은 서로 전혀 다르다. 단순한 쿼리 같은 것은 흡사하나 저장 프로시저나 함수 같은 부분만 봐도 완전히 다르다.
용어
데이터베이스라던가, DB라던가 SQL라던가 모르는 사람들은 위해 엑셀에 빗대어 설명을 해보겠습니다실제로 일반적인 기업들은 엑셀로 데이터베이스의 대용으로 사용하니까
데이터베이스
엑셀로 치면 엑셀 파일이라고 이해하시면 되겠습니다. 이에 대한 더 심도있는 설명을 원하는 경우 데이터베이스 항목 참고.
테이블
엑셀 파일에 있는 시트입니다. 엑셀파일의 시트처럼 각각의 디비에는 여러 개의 테이블이 있습니다.
컬럼
시트의 열과 같습니다. 다만, 시트의 열과는 다르게, 각각의 열과는 달리 이름이 배정되고 열의 개수가 무한정으로 확장되지는 않습니다
SQL
엑셀파일에 있는 시트나 열, 행, 자료를 수정하는 것과 같은 역할을 하는 언어입니다. 마우스와 단축키로 하는 것과 달리 프로그래밍 언어처럼 코드를 써넣음으로서 테이블을 만들고, 데이터를 불러 올 수 있습니다.
조회 쿼리
공통
SELECT 컬럼1, 컬럼2
FROM 테이블
가장 기본 적인 형태로 지정한 테이블에서 지정한 컬럼의 값을 읽어온다. 각 컬럼은 ,로 구분한다. 모든 컬럼을 읽어올 때는 SELECT 부분을 SELECT *로 입력해주면 된다.
SELECT 컬럼1, 컬럼2
FROM 테이블
WHERE 컬럼3 = 'A'
WHERE는 조건을 지정할 때 쓰인다. 위의 경우는 컬럼3의 값이 A인 행들만 조회하기 위한 조건.
SELECT 컬럼1, 컬럼2
FROM 테이블
WHERE 컬럼3 >= 100
조건에 사용된 컬럼이 숫자라면 이런 식으로 크기를 비교할 수도 있다. 이 예제는 컬럼3의 값이 100보다 크거나 같은 행들의 데이터를 읽어오도록 조건을 준 것이다. 한가지 덧붙여서 =는 같은 것을 !=나 <>는 다른 것을 뜻하며 >, <는 한쪽이 큰 것을 >=, <=는 한쪽이 크거나 같은 것을 뜻한다.
SELECT 컬럼1, 컬럼2
FROM 테이블
WHERE 컬럼3 = 'A'
AND (컬럼4 = 'B' OR 컬럼5 = 'C')
WHERE 조건에 여러개의 조건을 지정할 때는 위와 같이 AND나 OR을 사용한다. AND는 두 가지가 모두 만족해야하며 OR은 둘 중 하나만 만족하면 된다. AND와 OR가 둘다 사용되면 헷갈리지 않게 괄호로 묶어주도록 하자. 위의 경우는 컬럼3의 값이 A이면서 컬럼4의 값이 B이거나 컬럼5의 값이 C인 행만 읽어오게 하기 위한 조건.
SELECT 컬럼1, 컬럼2
FROM 테이블
WHERE 컬럼3 LIKE '%A%'
특정 값이 부분 적으로 포함된 행을 읽어오고 싶을 땐 LIKE 문을 사용하면 된다. 위의 예제는 컬럼3에 A가 들어가는 모든 행을 읽어오는 예제이다. A로 시작하는 행만 읽어오고 싶을 때는 LIKE 'A%', A로 끝나는 행만 읽어오고 싶을 때는 LIKE '%A'로 해주면 된다.
SELECT 컬럼1, 컬럼2
FROM 테이블
ORDER BY 컬럼1 ASC
ORDER BY 문을 사용하면 정렬을 할 수 있다. 위의 경우는 컬럼1을 기준으로 오름차순 정렬을 한 것으로 ASC는 생략이 가능하다. 생략시에는 오름차순으로 정렬이 된다. 내림차순으로 정렬하고 싶을 경우에는 ASC 대신 DESC를 입력해주면 된다. WHERE 절과 함께 사용 시에는 WHERE 절 아래에 사용하게 된다.
SELECT DISTINCT 컬럼1, 컬럼2
FROM 테이블
중복된 결과는 제외하고 조회하고 싶을 경우에는 DISTINCT를 붙여주면 된다.
MS-SQL
SELECT TOP 10 컬럼1, 컬럼2
FROM 테이블
ORDER BY 컬럼1 ASC
조회시 최상위의 데이터 몇 행만 가져올 경우에는 TOP을 사용하게 된다. 위의 예제는 컬럼1을 기준으로 오름차순 정렬을 한 후 최상위의 10개 행만 가져오는 예.
MySQL, MariaDB
SELECT 컬럼1, 컬럼2
FROM 테이블
ORDER BY 컬럼1 ASC
LIMIT 10
조회시 최상위의 데이터 몇 행만 가져올 경우에는 LIMIT를 사용하게 된다. 위의 예제는 컬럼1을 기준으로 오름차순 정렬을 한 후 최상위의 10개 행만 가져오는 예.
SELECT 컬럼1, 컬럼2
FROM 테이블
ORDER BY 컬럼1 ASC
LIMIT 5, 10
LIMIT 절에서 위와 같이 값을 2개 준 경우에는 첫번째 값의 행부터 두번째 값의 수만큼 가져오라는 뜻이다. 위의 예제는 6번째 행[2]부터 10개의 행 즉 15번째 행까지 읽어오게 된다. LIMIT 0, 10으로 입력하면 LIMIT 10과 동일하게 된다.
삽입 쿼리
INSERT INTO 테이블 (컬럼1, 컬럼2)
VALUES (값1, 값2)
가장 기본 적인 형태의 삽입 쿼리이다. 입력한 컬럼 수와 입력한 값의 수가 일치해야한다.
INSERT INTO 테이블
VALUES (값1, 값2)
테이블명 옆의 컬럼들은 생략이 가능하다. 하지만 이 경우 VALUES 부분에 모든 컬럼의 값을 컬럼 순서대로 적어주어야 하며 차후 테이블의 컬럼이 추가될 경우 VALUES 부분에도 추가된 컬럼 수 만큼 값의 수를 추가해주지 않으면 오류가 발생하게 되기 때문에 소스에서 이와 같은 방법으로 사용하는 것은 권장하지 않는다. 이 방식을 소스 내에서 사용하면 컬럼이 추가될 경우 이 테이블을 사용하는 모든 프로그램의 소스에서 전부 찾아내서 바꿔야하는데 사실상 힘들기 때문.
INSERT INTO 테이블A (컬럼1-A, 컬럼2-A)
SELECT 컬럼1-B, 컬럼2-B
FROM 테이블B
조회 쿼리로 조회한 결과를 삽입하는 쿼리. SELECT로 조회하는 컬럼 수와 INSERT 하는 컬럼 수가 일치해야한다. 조회쿼리 부분에선 당연히 WHERE절로 조건 주는 것도 가능하며 조인으로 여러 테이블을 묶어서 조회하는 것도 가능하다.
MySQL, MariaDB
INSERT INTO 테이블 (컬럼명1, 컬럼명2)
VALUES (값1-1, 값2-1), (값1-2, 값2-2), (값1-3, 값2-3)
위와 같은 방식으로 여러 행을 한번에 삽입할 수 있다.
수정 쿼리
UPDATE 테이블
SET 컬럼1 = 값1,
컬럼2 = 값2
WHERE 컬럼3 = 'A'
기본 적인 수정 쿼리. WHERE절은 조회 쿼리의 WHERE절과 사용 방법이 동일하다.
UPDATE 테이블
SET 컬럼1 = 값1,
컬럼2 = 값2
WHERE절은 생략도 가능하다. 하지만 해당 테이블의 모든 행이 업데이트 돼 버리는 대참사가 발생해버리므로 WHERE절은 꼭 붙여주자. 특히 현재 운영 중인 서버라면 더욱 더.
삭제 쿼리
DELETE FROM 테이블
WHERE 컬럼1 = 'A'
기본 적인 삭제 쿼리. WHERE절은 조회 쿼리의 WHERE절과 사용 방법이 동일하다.
DELETE FROM 테이블
WHERE절은 생략도 가능하다. 하지만 해당 테이블의 모든 행이 삭제돼 버리는 사상 최악의 대참사가 발생해버리므로 WHERE절은 꼭 붙여주자. 특히 현재 운영 중인 서버라면 더욱 더.
TRUNCATE TABLE 테이블
DELETE FROM 테이블와 비슷하지만 자동 증가 값도 리셋된다. 조건은 지정할 수 없다. 사실상 테이블을 초기화하는 거나 다름 없다.[3]
MS-SQL
DELETE 테이블
WHERE 컬럼1 = 'A'
FROM은 생략 가능하다. 이 예제는 DELETE FROM 테이블 WHERE 컬럼1 = 'A'와 동일하다.
조인
기본 함수
DBMS별 서로 같은 기능을 하는 함수 일람
| MS-SQL | MySQL, MariaDB |
|---|---|
| GETDATE() | NOW() |
| ISNULL() | IFNULL() |
| SCOPE_IDENTITY() | LAST_INSERT_ID() |
MS-SQL
GETDATE()
서버의 현재 날짜, 시간을 반환하는 함수.
ISNULL(A, B)
A가 NULL인지 체크하고 NULL이 아니면 A 그대로 반환하고 NULL이면 B를 반환하는 함수.
SCOPE_IDENTITY()
현재 연결된 세션에서 가장 마지막에 인서트된 자동 증가 값을 반환하는 함수.
MySQL, MariaDB
NOW()
서버의 현재 날짜, 시간을 반환하는 함수.
CONCAT(A, B, …)
입력한 문자열을 하나로 합쳐주는 함수. 문자열은 + 연산자로 합칠 수 없고 이 함수를 사용하여 합쳐야한다.
IF(A, B, C)
조건 A가 참이면 B를 거짓이면 C를 반환하는 함수.
IFNULL(A, B)
A가 NULL인지 체크하고 NULL이 아니면 A 그대로 반환하고 NULL이면 B를 반환하는 함수.
ISNULL(A)
A가 NULL인지 체크하여 NULL이면 1, 아니면 0를 반환하는 함수.
LAST_INSERT_ID()
현재 연결된 세션에서 가장 마지막에 인서트된 데이터의 자동 증가된 값[4]을 반환하는 함수.
저장 프로시저
사용자 정의 함수
트리거
트랜잭션
실습
위에 적힌 내용들을 한번 읽어보았다면 이제 실습에 들어가도록 하자. 보기만 하는 것 보다 직접 해보는 것이 더 쉽게 익혀지기 때문이다. 여기선 MariaDB를 기준으로 설명하도록 하겠다. MS-SQL이나 Oracle로 해볼 사람들은 이번 기회에 MariaDB 하고 어떻게 다른지도 같이 확인해보자. 그리고 여기선 OS를 윈도우즈로 사용하겠다. 절대 리눅스 설치하기가 귀찮아서 그런게 아니다.
준비
위 사이트에서 마리아 DB를 다운로드 받을 수 있다. 받고 설치하도록 하자.
접속
윈도우즈 용으로 설치하였다면 HeidiSQL 라는 프로그램도 같이 설치되었을 것이다. MariaDB나 MySQL에서 사용가능한 GUI 툴은 HeidiSQL 말고도 여러 종류가 있다. 다른 툴을 가지고 있는 것이 있다면 그것을 사용해도 상관없다. GUI툴 쓰기 싫으면 그냥 콘솔 환경에서 해도 말리지 않겠다. 여기선 MariaDB 설치 시 같이 설치되는 HeidiSQL를 기준으로 설명한다.
HeidiSQL을 실행하고 세션 관리자에서 신규 세션을 생성하고 네트워크 유형은 MySQL (TCP/IP), 호스트명은 127.0.0.1[5], 사용자는 root로 하고 암호는 설치할 때 입력했던 root 비밀번호를 입력하도록 하자. 포트번호는 따로 바꾸지 않았다면 3306이 기본 포트이다. Q. 그냥 막 설치 해서 root 비밀번호 뭘로 했는지 모르겠어요. A. 구글링 하세요.
DB 만들기
접속을 하였으면 좌측에 현재 연결된 DB서버와 그 서버에 있는 DB들이 나열돼 있을 것이다. 좌측의 빈 공간에 마우스 우클릭을 해서 새로 생성 ▶ 데이터베이스를 선택하자. 이름은 마음에 드는 것으로 지어주자. 여기선 wikineet 라고 하겠다. 조합은 utf8_general_ci 로 하겠다. DB를 만들었으면 좌측에 wikineet라는 DB가 추가된 것이 보일 것이다.
테이블 만들기
좌측의 wikineet DB를 우클릭해보면 녹색으로 체크가 되면서 메뉴가 뜰 것이다. 새로 생성 ▶ 테이블을 선택하자. 테이블 이름은 user 라고 지어주고 추가 버튼으로 열을 3개 추가하도록 하자. 그 4개의 열을 다음과 같이 입력해주도록 하자. 아래 표에 없는 부분은 신경쓰지 말자.
| # | 이름 | 데이터 유형 | 길이/설정 | 부호 없음 | NULL 허용 | 0으로 채움 | 기본값 |
|---|---|---|---|---|---|---|---|
| 1 | user_id | INT | AUTO_INCREMENT | ||||
| 2 | user_name | VARCHAR | 30 | ||||
| 3 | VARCHAR | 100 | |||||
| 4 | country_id | INT | v |
그리고 user_id를 우클릭 하고 새 인덱스 생성 ▶ PRIMARY를 선택해주자. 그리고 저장 버튼을 눌러주자. 그러면 좌측에 wikineet DB에 user 테이블이 생성된 것이 보일 것이다.
다시 새로 생성 ▶ 테이블을 선택하고 이번에는 이름을 country로 해주자. 열을 2개 추가하고 아래와 같이 넣어주자.
| # | 이름 | 데이터 유형 | 길이/설정 | 부호 없음 | NULL 허용 | 0으로 채움 | 기본값 |
|---|---|---|---|---|---|---|---|
| 1 | country_id | INT | AUTO_INCREMENT | ||||
| 2 | country_name | VARCHAR | 50 |
그리고 country_id를 우클릭 하고 새 인덱스 생성 ▶ PRIMARY를 선택해주자. 그리고 저장 버튼을 눌러주자. 그러면 좌측에 wikineet DB에 country 테이블이 생성된 것이 보일 것이다.
좌측에서 user 테이블을 선택해주고 위쪽 탭 중 외래 키 탭으로 이동 후 추가를 누르고 다음과 같이 입력해주자.
| 열 | 참조 테이블 | 외래 열 | UPDATE 될 때 | DELETE 될 때 |
|---|---|---|---|---|
| country_id | country | country_id | CASCADE | NO ACTION |
그리고 저장해주자.
이것으로 user 테이블과 country 테이블이 만들어졌으며 country_id를 가지고 외래 키도 생성해주었다.
샘플 데이터
탭 중에 보면 쿼리라는 탭이 보일 것이다. 아래 삽입 쿼리를 복사해서 붙여넣고 실행하자. 실행 단축키는 F9다.
INSERT INTO country (country_id,country_name)
VALUES (1,'한국'),(2,'미국'),(3,'중국'),(4,'영국'),(5,'일본'),(6,'러시아'),(7,'프랑스'),(8,'네덜란드'),(9,'이탈리아'),(10,'독일');
INSERT INTO user (user_id,user_name,email,country_id)
VALUES (1,'고길동','go@kmail.com',1),(2,'알트리아','altria@kmail.com',4),(3,'아스카','asuka@kmail.com',5),(4,'미라이','mirai@wohoo.com',5),(5,'바트 심슨','bart@kmail.com',2),(6,'티미 터너','timmy@wohoo.com',2),(7,'이기영','lee@never.com',1),(8,'아마미 하루카','haruka@kmail.com',5),(9,'우미하라 카와세','uk@wohoo.com',5),(10,'아이리스필 폰 아인츠베른','irisviel@kmail.com',10);/*고길동 : 아기공룡 둘리, 알트리아 : FATE 시리즈, 아스카 : (다들 알지 않을까...)신세계 에반게리온, 미라이 : (추측이지만)PROJECT IM@S, 바트 심슨 : The Simpsons, 티미 터너 : 티미의 못말리는 수호천사, 이기영 : 검정고무신, 아마미 하루카 : PROJECT IM@S, 우미하라 카와세 : 우미하라 카와세(캡콤의 게임이다.), 아이리스필 폰 아인츠베른 : FATE 시리즈
(이분 달빠인가보다)
추가로 kmail은 gmail, wohoo는 yahoo*/
데이터 조회하기
다음 쿼리를 입력하고 실행해보자.
SELECT *
FROM user
그러면 user 테이블에 들어 있는 모든 데이터 들이 조회될 것이다. user_id, country_id는 숫자로 되어 있어 알아보기 힘드니 이번에는 저 둘을 제외하고 이름과 이메일만 출력해보도록 하자.
SELECT user_name, email
FROM user
이제 이름과 이메일만 출력 된 것을 볼 수 있다. 이런 식으로 *를 쓰면 모든 열이 조회되며 원하는 열만 조회할 때는 해당 열의 이름을 직접 입력해주면 된다. 이번에는 이름 순으로 정렬이 되도록 해보자.
SELECT user_name, email
FROM user
ORDER BY user_name ASC
이름 순으로 정렬이 된 것을 볼 수 있다. 그런데 이번에는 kmail.com을 사용하는 사람들만 조회하고 싶어졌다.
SELECT user_name, email
FROM user
WHERE email LIKE '%@kmail.com'
ORDER BY user_name ASC
이제 kmail.com을 사용하는 사람들만 조회되는 것을 볼 수 있다. 추가바람