필자에게 연락하기

SQL Tutorial
제 2장

요약: 이번 두번째 기사에서는 SQL 언어의 기본 적인 구문(statement)에 대해서 배운다.

들어 가는 말

이 글은 SQL 과정 중 두 번째 글이다. 이 글에서는 table을 만들고, 수정하고, 지우는 등의 SQL 명령에 대해서 배운다.

가장 먼저, 개인적으로 가장 중요하다고 생각되는 SELECT 명령에 대해 배운다.

이 글이 이 글을 읽는 모든 독자에게 재미있고, 많은 도움이 되었으면 한다.

Table 만들기

처음 설치을 했을 때 봤듯이, table을 만들기 위해서는 TABLE과 함께 사용하여CREATE 명령을 이용한다. 실제로는 CREATE는 다음과 같은 것들을 만들 수 있다.

• users: CREATE USER
• tables: CREATE TABLE
• indexes: CREATE INDEX
• views: CREATE VIEW

CREATE 명령은 무엇인가를 만들려고 한다는 것을 알려준다. 다음에 어떻게 하는지 배울 것이다.

문법

CREATE TABLE name ( column type [DEFAULT value] [NOT NULL], ...
      [INHERITS (inherits, ...)]
      [CONSTRAINT constraints CHECK (test), CHECK (test)] );

CREATE TABLE countries (
         cod_country integer NOT NULL,
         name varchar(30))
   CONSTRAINT cod_raro CHECK (cod_country > 0 AND cod_country < 154);

위의 예에서 countries라는 table을 만들었다. 테이블에 값을 입력할 때마다 다음 조건에 맞는지 확인하게 된다.

• cod_country는 NULL이 아니다. 만약 NULL은 대입하게 되면, 관리자는 error를 내게 된다.
  

  ExecAppend: cod_country의 속성이 NULL이 아니기 때문에 실패하게 된다.
  

• cod_country는 0보다 크고 154보다는 작다. 범위에 벗어나는 값을 대입하게 되면 관리자는 error를 낸다.
  

  ExecAppend: cod_raro 검사를 거부하게 된다.
  

Note

NULL은 무엇을 의미하는가? SQL에는 data와 data가 아닌 것 두가지 상태가 있다. 영(0)이나 공백 문자 데이타를 의미하는 것처럼 우리는 데이타를 가지지 않는 필드를 필요로 할 때가 있다. SQL에서는 NULL이라는 개념을 도입했다. 실제적인 예는 다음과 같다.

bill이라는 데이블을 만들고, customer, value, date_issued, date_paid라는 필드를 가지고 있다.

그리고 customer, value, date_issued 필드에 값을 대입하고,

date_paid는 NULL로 했다. 이런 식으로 지불해야할 돈을 알수가 있다.

 
SELECT * FROM bills WHERE date_paid IS NULL ;

date_padi를 영(0)으로 해도 값은 효과를 낼 수 있다고 생각하는 사람도 있을 텐데, 영(0)이 날짜가 아닌 것을 제외하고, 맞는 말이다. 그리고 날짜에 관한 함수를 이용하고, date type의 date_paid를 만드는 것을 방지한다.

(역자주 : 영어가 짧아서 번역이 엉성하군여 ^^; 원문입니다.
One can claim that a zero (0) in the field date_paid would do the same thing. It's true, except that zero (0) is not a date, and it prevents creating date_paid of date type and applying appropriate date functions.)

NULL을 이용하여 생성하는 예:

insert into countries values (15, NULL);

insert into countries (cod_country) values (27);

'name'에 값을 대입하지 않는 것은 NULL 값을 받는 다는 것을 의미한다.

Table 수정하기;

PostgreSQL에서는 Table의 수정은 새로운 컬럼을 추가하는 일만을 할 수 있다.

ALTER TABLE table ADD name type;

Table에 데이타 입력하기

이제 Table에 값을 입력하는 것에 대해 배운다.

INSERT INTO table [(column, column, ...)] VALUES (value-1, value-2,
...)

INSERT INTO table [(column, column, ...)] SELECT ....

보았다 시피 table에 데이타를 입력하는 방법은 두 가지가 있다. 한줄 한줄 입력하는 방법과 여러 row를 리턴하는 sub-select를 이용하는 방법이다.

table에 데이타를 입력할 때는 NULL 값을 포함한 모든 컬럼들에 값을 집어 넣어야 한다.

데이타를 입력하는 명령에 어떤 컬럼에 값을 입력할지 명시화하지 않으면 모든 컬럼에 데이타를 입력하는 것으로 이해한다. 예를 들어:

INSERT INTO countries VALUES (34, 'Spain');

INSERT INTO countries VALUES (34);

INSERT INTO countries (cod_country) VALUES (34);

필자는 C 언어에서 DB 프로그래밍을 하거나 database 함수를 사용할 때 원하는 컬럼을 꼭 명시화 할 것을 추천하는 바이다. 왜냐하면 ALTER TABLE등을 이용하여 새로운 컬럼을 추가했을 때 다음 번의 insert는 에러를 내기 때문이다.

예:

이는 'population' data가 빠졌기 때문에 구문 에러를 내게 된다.

Note

PostgreSQL에서는 에러를 발생하지 않고, 'population' 필드를 NULL로 채운다. 하지만, 이것은 PostgreSQL의 특징이고, 다른 SQL에서는 에러를 발생할 것이다.

sub-select를 이용한 다른 방법의 INSERT가 있다.

이 방법의 insert는 임시적인 table을 만들거나 위험을 내포한 계산을 안전하게 하기 위해서 자주 쓰인다.

insert를 이용해서 data가 자체가 대치된 경우, 이것은 SELECT에 의해서 반환된다. SELECT는 한 row 또는 여러 row를 리턴할 수 있다. SELECT는 SELECT와 같은 제약 사항을 가진다.

data 선택하기

필자는 빨리 이 부분으로 오길 원했다.! :-))

SQL 명령을 몇 가지 보았느데, SELECT없는 SQL 언어는 순대에 불과하다.

SELECT 명령은 우리가 data에 접근하는 것을 허락한다. 하지만 이러한 접근은 예약된 것들, 즉 탐색이나, table들의 결합, data에 관한 함수, 탐색 규약 등이다.

예:

select * from countries;

*는 모든 컬럼을 의미한다.

다른 예:

SELECT a.name, SUM(population)
    FROM countries a, states b, counties c
    WHERE b.cod_country = a.cod_country
        AND (c.cod_country = b.cod_country
        AND c.state_code = b.state_code)
        AND population IS NOT NULL
        GROUP BY a.name
        ORDER BY sum ASC;

설명상, 필자는 모든 나라의 인구를 올림차순으로 보길 원했다.

이를 위하여 'population'이라는 컬럼을 counties table에 추가했다.

create table counties (cod_country int, 
                        state_code int, 
                        county_code int,
                        county_name varchar(60),
                        population int);
insert into counties values (1, 1, 1, 'Country 1, State 1, County 1',
5435);
insert into counties values (2, 1, 1, 'Country 2, State 1, County 1',
7832);
insert into counties values (3, 1, 1, 'Country 3, State 1, County 1',
4129);
insert into counties values (1, 2, 1, 'Country 1, State 2, County 1',
76529);
insert into counties values (2, 2, 1, 'Country 2, State 2, County 1',
9782);
insert into counties values (3, 2, 1, 'Country 3, State 2, County 1',
852);
insert into counties values (1, 3, 1, 'Country 1, State 3, County 1',
3433);
insert into counties values (2, 3, 1, 'Country 2, State 3, County 1',
7622);
insert into counties values (3, 3, 1, 'Country 3, State 3, County 1',
2798);
insert into counties values (1, 1, 2, 'Country 1, State 1, County 2',
7789);
insert into counties values (2, 1, 2, 'Country 2, State 1, County 2',
76511);
insert into counties values (3, 1, 2, 'Country 3, State 1, County 2',
98);
insert into counties values (1, 2, 2, 'Country 1, State 2, County 2',
123865);
insert into counties values (2, 2, 2, 'Country 2, State 2, County 2',
886633);
insert into counties values (3, 2, 2, 'Country 3, State 2, County 2',
982345);
insert into counties values (1, 3, 2, 'Country 1, State 3, County 2',
22344);
insert into counties values (2, 3, 2, 'Country 2, State 3, County 2',
179);
insert into counties values (3, 3, 2, 'Country 3, State 3, County 2',
196813);
insert into counties values (1, 1, 3, 'Country 1, State 1, County 3',
491301);
insert into counties values (2, 1, 3, 'Country 2, State 1, County 3',
166540);
insert into counties values (3, 1, 3, 'Country 3, State 1, County 3',
165132);
insert into counties values (1, 2, 3, 'Country 1, State 2, County 3',
0640);
insert into counties values (2, 2, 3, 'Country 2, State 2, County 3',
65120);
insert into counties values (3, 2, 3, 'Country 3, State 2, County 3',
1651462);
insert into counties values (1, 3, 3, 'Country 1, State 3, County 3',
60650);
insert into counties values (2, 3, 3, 'Country 2, State 3, County 3',
651986);
insert into counties values (3, 3, 3, 'Country 3, State 3, County 3',
NULL);
commit work;

이제 우리는 ALTER TABLE를 이용할 수 없지만, 아직 설명하지 않은 UPDATE를 사용할 수 있다. "cut & paste"를 이용하라. 모든 사람이 happy 해질 것이다. :-))

쿼리를 수행함으로서 다음과 같은 결과를 얻을 수 있다.

name     |    sum
- ---------+-------
country 1| 705559
country 2|1212418
country 3|2804018
(3 rows)

   sum
- ------
791986
(1 row)

!!!!!! 하나가 다르다 !!!!!!

이제 states table을 보자. state 3를 빼억었다.

INSERT INTO states VALUES (3, 1, 'State 3, Country 1');
INSERT INTO states VALUES (3, 2, 'State 3, Country 2');
INSERT INTO states VALUES (3, 3, 'State 3, Country 3');

name     |    sum
- ---------+-------
country 1| 791986
country 2|1872205
country 3|3003629 

각각의 country에 state 3가 없다.

(테이블 간의 joindl EXACT임을 기억하라) 없어진 것들에 대해서 조건에 맞는 것들만 추출할 수 있다.

다시 윗 부분으로 올라가서WHERE b.cod_country = a.cod_country부분을 보자.

이것은 country table과 state를 cod_country가 같은 것을 join했다는 것을 말해준다. country data가 사입됐다는 것을 기억해라.

다음을 실행시키지는 마라. 단순한 예이다.

create table countries (cod_country integer, name varchar(30));
insert into countries values (1, 'country 1');
insert into countries values (2, 'country 2');
insert into countries values (3, 'country 3');
commit work;

create table states (state_code int, 
                        cod_country int, 
                        state_name varchar(30));
insert into states values (1, 1, 'State 1, Country 1');
insert into states values (2, 1, 'State 2, Country 1');
insert into states values (1, 2, 'State 1, Country 2');
insert into states values (2, 2, 'State 2, Country 2');
insert into states values (1, 3, 'State 1, Country 3');
insert into states values (2, 3, 'State 2, Country 3');
commit work;

        AND (c.cod_country = b.cod_country
        AND c.state_code = b.state_code)

state는 counties table에는 있지만, states table에는 없다.

위에서 말한 것이 이해가 잘 안 간다면, 아스피린 한 알을 먹고, 개와 함께 산책을 나가라(개가 없으면 혼자 가라). 심호흡을 좀 하면서 쉰 후에 처음부터 다시 시작하라

Join을 한 결과가 어떻게 나오는지 이해하는 것은 매우 중요하다. 이러한 개발을 하지 않는다면, 예측할 수 없는 결과가 나올 수 있다.

이제 관점을 조금 바꿔서 SELECT 명령의 문법에 대해서 알아 보자

SELECT [DISTINCT] expression1 [AS nom-attribute] {, expression-i [as
nom-attribute-i]} 
       [INTO TABLE classname] 
       [FROM from-list] 
       [WHERE where-clause] 
       [GROUP BY attr_name1 {, attr_name-i....}] 
       [ORDER BY attr_name1 [ASC | DESC ] [USING op1 ] {,
nom-attribute-i...}] 
       [UNION {ALL} SELECT ...] 

manu=> select cod_country from countries;
cod_country
- -----------
          1
          2
          3
(3 rows)          

manu=> select cod_country as countr from countries;
countr
- ------
     1
     2
     3
(3 rows)

SELECT 명령은 DB로부터 아이템을 리턴하는 것 뿐만아니라, item을 수정할 수도 있다는 것을 보았다.

이것은 지불의 10%를 리턴한다.

사용가능한 함수들에 대해서 알아 보자:

이것들은 SQL에 있는 몇가지의 함수들이다. 이들 함수는 ANSI SQL에 정의되어 있고, Postgres95에도 정의되어 있다.

WHERE에 대한 자세한 설명

지금까지 SELECT에서의 WHERE에 대해 보았고, 다음과 같이 쓰일 수 있다.

AND column = value

간단한 예일뿐이고, 다음과 같이 쓰거나, 아니면 함께 묶어서 쓸 수 있다.

AND, OR, NOT, IN, IN ALL, =, !=, >, <, (SELECT....), LIKE 등도 또한 쓰일 수 있다. 예:

WHERE 
   column IN (SELECT DISTINCT column FROM table WHERE ....)
   column IN ('value1','value2','value3',...)
   (column = 'value' and column = 'other_value' OR column != 'value')

WHERE column LIKE '%Pepito%'

WHERE column LIKE 'Pepito%'

WHERE column LIKE '%Pepito'

WHERE와 함께 쓰일 수 있는 모든 옵션들을 열거할만한 시간이 없다. 한계는 프로그래머의 상상력이나, 각각의 문장 작업의 한계에 있다. 이제 SELECT 명령은 그만하고, 마지막 두가지에 대해서 집중을 하자

UPDATE 명령

UPDATE 명령은 WHERE에 있는 조건들을 만족하는, 한 row 혹은 여러 row의 값을 수정할 수 있다.

UPDATE table SET column-1 = expression-1 
                 [, column-i = expression-i] 
       [WHERE condition] 

DELETE 명령

DELETE FROM table
       [WHERE condition] 

참조

번역:허정수

영문으로 번역: Dallas L. Gale