컴알못의 SQL 공부

데이터베이스는 데이터베이스 관리 시스템 DBMS가 관리합니다.

사람은 DBMS에게 데이터베이스 언어로 데이터베이스에게 요청을 보낼 수가 있습니다.

SQL은 관계형 데이터베이스 표준 언어로서 가장 많이 사용됩니다.

특징이라고 한다면 선언적 언어이기 때문에 어떤 데이터를 원하는지 명시만하고 어떻게 그 정보를 얻어올지에 대한 절차는 기술하지 않습니당.

현재는 대부분의 DBMS가 SQL을 지원한다고 하네요.

 

SQL은 1970년대에 IBM 연구소에서 처음 개발되었습니다. 처음에는 DBMS별로 여러 변형된 SQL이나 저마다의 데이터언어가 나와 혼란이 있었다고 하는데 지금은 SQL이 데이터베이스 표준 언어로 승인되어 이러한 혼란이 해결되었습니다.

 

SQL은 그 기능으로 3가지로 분류할 수 있습니다.

데이터 정의어(DDL)과 데이터 조작어(DML)과 데이터 제어어(DCL)로 말이죠.

 

SQL은 질의어라고도 불리는데, 대화 방식 또는 내포 방식으로 사용할 수 있습니다.

대화 방식은 DBMS에서 제공하는 온라인 인터페이스를 통해 질의어를 입력을 하고, 그 결과를 직접 확인하는 방식을 말합니다. 즉각적인 피드백이 오기 때문에 이게 마치 대화같아서 대화 방식이라고 부르는 것 같습니다.

내포 방식은 응용 프로그램의 프로그래밍 코드 안에 SQL을 문자열 형태로 포함합니다. 실행 결과 역시 사용자에게 즉각적으로 보여주는게 아니라 응용 프로그램 안으로 반환받겠죠. 단순하게 말하면 응응프로그램이 DBMS를 컨트롤 할 수 있도록 코드를 짜넣는거겠네요. 온라인 인터페이스를 사용하지 않는 방식입니다.

SQL이 응용 프로그램 안에 삽입될 때 이걸 데이터 부속어라고도 부른다고 합니다.

 

SQL은 사실 모든 DBMS에서 동일하지 않습니다. 표준이 양이 워낙 방대하기도 하고 어떤 부분은 표준에 대한 명세가 애매모호해서 해석에 차이가 있을 수도 있다고 하네요. 그리고 DBMS 별로 차별성을 갖기 위해 완벽하게 표준만 따라가기는 어렵다는 입장도 있습니다. 하지만 기본적으로 쓰이는 대부분의 SQL 명령어는 DBMS 별로 차이가 거의 없습니다.

 

일단은 간단한 SQL문부터 살펴봅시다.

CREATE USER 'manager'@`%` IDENTIFIED BY `1234`

이건 별도의 사용자 계정을 생성하는 명령어입니다. 기본적으로 사용자를 생성하는 권한은 ROOT 계정에 있습니다.

그렇기 때문에 ROOT 계정에 접속하여 아이디가 manager인 관리자 계정을 생성해야 합니다.

MYSQL에서 사용자 계정은 `아이디@호스트`로 구성됩니다. 호스트라는 건 접속 지점으로... 접속을 하는 지점. 즉, IP가 되겠네요. %를 입력하면 모든 IP 혹은 호스트명을 의미하기 때문에 어디서든 접속 가능합니다.

그리고 IDENTIFIED BY로 비밀번호를 1234 로 설정합니다.

 

예상하셨겠지만 계정에는 각각 권한이라는게 있습니다.

가장 큰 권한을 가진 녀석은 당연히 처음부터 존재하던 ROOT 계정이겠죠.

이 ROOT 계정을 수퍼 사용자라고 하는데 이녀석이 가진 권한을 다른 계정에 부여하는 것도 가능합니다.

GRANT ALL ON *.* TO 'manager'@'%' WITH GRANT OPTION;

GRANT는 권한을 부여하는 명령어입니다.

부여할 수 있는 권한은 SELECT, INSERT, UPDATE, DELETE를 부여할 수 있는데, ALL은 이 네가지 권한을 전부 부여하겠다는 뜻이에요.

ON 뒤에는 권한을 부여할 데이터베이스와 테이블을 명시합니다(EX hanidatabase.hanitable). *.* 는 모든 데이터베이스의 모든 테이블들이 대상임을 의미합니다.

그리고 마지막에 WITH GRANT OPTION을 적어주면, 권한을 부여받은 사용자는 부여받은 권한을 또 다른 사용자에게 부여할 수 있습니다.

SELECT USER();
SHOW DATABASE;

현재 사용자의 목록을 보고 싶다면 SELECT USER()를 통해서 볼 수 있습니다.

그리고 DATABASE의 목록은 SHOW 명령어를 통해서 볼 수 있어요.

이처럼 SELECT는 USER() 라는 특수한 테이블을 검색할 수 있도록 해주며, SHOW는 아마도 메타데이터같이 기본적인 정보들을 볼 수 있도록 하는 명령어일 겁니다.

 

데이터 조작문 (DML)

DML은 통상적으로 가장 많이 쓰이는 SQL일 겁니다.

실제로 테이블에 데이터를 담고, 보고, 없애는 건 전부 DML의 역할이니까요.

DML의 대표적인 명령어로는 SELECT, INSERT, UPDATE, DELETE가 있습니다.

 

SELECT는 데이터를 보기 위한 명령어에요. 관계 대수의 SELECT 명령어와도 의미가 일치하죠.

SELECT는 조건을 검색하여 원하는 데이터를 열람합니다.

SELECT 속성
FROM 테이블
WHERE 조건
GROUP BY 그룹 기준 속성
HAVING 그룹 조건
ORDER BY 정렬 기준 속성

이게 SELECT의 기본입니다.

SELECT~FROM~WHERE까지는 직관적으로 이해가 가능하겠죠.

적어도 SELECT는 FROM까지는 필수적으로 적어줘야합니다.

 

SELECT는 너무 쉽기 때문에 자세하게 설명하지는 않을 겁니다.

기억할 만한 포인트를 위주로 기술할게요.

SELECT DISNTINCT 이름 FROM 친구

DISTINCT는 중복을 제거하는 옵션입니다.

모든 튜플은 유일하지만 기본키 없이 특정 속성만 가려내면 중복이 생길수도 있겠죠. 그런 중복을 제거해줍니다.

SELECT 이름, 나이, 학교
FROM 학생
WHERE 학교='가나고' OR 학교='다라고'
ORDER BY 나이 ASC, 이름 DESC

ORDER BY는 정렬을 위한 옵션입니다.

속성 뒤에 ASC, DESC를 붙임으로서 정렬의 기준을 세울 수가 있습니다.

ASC는 당연히 오름차순, DESC는 내림차순이에요.

그리고 LIMIT이라는 게 있습니다. LIMIT은 검색 결과의 개수를 제한하는 거에요.

LIMIT 3; 을 붙이면 반환받는 튜플의 개수는 3개가 되겠죠.

기본적으로 ORDER BY와 궁합이 아주 좋은 옵션입니다.

참고로 LIMIT (반환받는 첫번재 행의 위치) (반환하는 행들의 최대 개수)로 두가지 인자가 들어갈 수도 있습니다.

만약 LIMIT 5, 3이라면 5번째 행부터 3개의 행들을 반환받는다는 뜻이죠.

 

그리고 SELECT 명령어는 집계함수와 함께 쓰일 수 있습니다.

집계함수라는 건 검색하는 각 속성에 대한 통계를 제공하는 함수입니다.

종류는 COUNT, MAX, MIN, SUM, AVG가 가장 일반적입니다.

이름에서 어떤 의미의 통계를 제공하는지 감이 오죠?

SELECT COUNT(이름) AS 학생수 FROM 학생
=> 7
SELECT AVG(나이) AS 학생나이평균 FROM 학생
=> 19

문제는 SQL은 테이블을 처리해서 테이블을 뱉어내는 함수라는 겁니다.

이게 왜 문제냐면 이름 속성을 COUNT한 결괏값도 테이블이기 때문에 속성명을 가져야한다는 뜻이에요.

그래서 보통은 COUNT(이름) 같이 자동적으로 속성명이 정해져 나오지만 AS를 사용해서 별도의 이름을 지어줄 수도 있습니다. (AS는 생략가능합니다)

 

GROUP BY 절은 개별 튜플이 아니라 튜플의 그룹을 검색하고 싶을 때 사용합니다.

GROUP BY 옵션은 튜플들을 묶어서 그룹으로 만들어버립니다.

그렇다면 당연히 어떻게 그룹으로 만들지 거기에 대한 기준이 명시되어야겠죠.

GROUP BY절은 그룹이라는 특성 때문에 집계 함수와 궁합이 매우 좋습니다.

집계함수는 그룹에 대한 통계를 내놓기 때문이에요.

 

그리고 GROUP BY절은

HAVING 절과 세트로 쓰이는 경우가 많습니다.

HAVING 절은 그룹의 조건식을 뜻해요. 즉, GROUP BY로 그룹화 시켜버린 뒤에 WHERE 역할을 하는게 HAVING 절입니다. 좀 더 자세하게 말하면, 그룹이 만족해야하는 제한 조건을 명시하는게 HAVING 절입니다.

SELECT 학년, COUNT(*) AS '학년별 학생수'
FROM 학생
GROUP BY 학년
HAVING COUNT(*)>=2

 

그리고

LIKE 라는게 있습니다. 이건 문자열을 검색하는 옵션입니다.

만약 이름이 지혜라는 학생을 찾기 위해선 WHERE 이름 = "지혜"로 충분하지만 "="의 단점은 완벽하게 일치하지 않으면 검색이 안된다는 겁니다.

만약 이름에 "지"가 포함되는 학생을 찾고 싶다면 LIKE 키워드를 사용하는게 좋습니다.

부분문자열의 검색해 특화된 이 옵션은 검색하려는 키워드가 문자열에 포함되었나 그 여부를 판단합니다.

물론 문자열 특화 기능이기 때문에 숫자에는 사용할 수 없어요.

 

그 다음 부분은 내일 써야겠다. 손목이 아프네용.