달력

32024  이전 다음

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

[펌] MySQL의 SQL

SQL 2005. 1. 14. 12:54

3장 MySQL의 SQL

MySQL 데이터 베이스에서 데이터를 입/출력할 때에는 구조화된 질의 언어(SQL, Structured Query Language)를 사용한다. SQL을 사용하면 데이터를 검색하거나 입력, 수정, 삭제할 수 있다. MySQL과 데이터를 주고받을 때 가장 기본이 되는 도구는 SQL이다. 애플리케이션이나 그래픽 사용자 인터페이스를 통해 데이터베이스를 액세스할 때에도 애플리케이션에서는 SQL을 만들어낸다.
SQL은 일종의 "자연 언어"다. 바꿔 말하면 SQL 선언문은(적어도 겉으로 보기에는) 영어 문장의 구문과 비슷하게 생겼다. 이러한 방법을 사용하면 장점도 있고 단점도 있지만 결국 C, 자바, 펄과 같은 일반 언어와 비슷하다.
SQL 기초
SQL은 규격화된 규칙을 따른다는 점에서 "구조화"된 언어이다. 컴퓨터 프로그램을 이용하면 정형화된 SQL 질의를 쉽게 파싱할 수 있다. 오라일리에서 나온 존 레바인, 토니 메이슨, 더그 브라운의 lex dy yacc, end ed. (오라일리, 1992)에서는 언어를 해석하기 위한 프로그램을 만드는 예로 SQL 문법을 구현하고 있다. 질의(query)는 데이터베이스 서버로 전송되는 규격화된 명령어이며 이 명령어를 통해 원하는 동작을 처리할 수 있다. SQL 질의의 예로 다음과 같은 구문을 들 수 있다:
 SELECT name FROM people WHERE name LIKE 'Stac%'
위의 질의를 보면 SQL 구문은 영어 구문을 조금 이상하게 써 놓은 것과 매우 비슷하다는 사실을 알 수 있다(Select names from a list of people where the names are like Stac). SQL에서는 다른 컴퓨터 언어에서 많이 사용하는 특별한 형식이나 특수 문자를 거의 사용하지 않는다.
SQL 이야기
SQL은 코드(Codd) 박사가 관계형 데이터베이스의 개념을 개발하고 얼마 지나지 않은 1970년대에 IBM에서 처음 탄생했다. SQL은 처음 개발되었을 때부터 강력하면서도 쉽게 배울 수 있는 언어로 제작되었다. 영어처럼 자연 언어와 매우 비슷해서 컴퓨터 기술에 능숙하지 않은 사람들도 쉽게 배울 수 있다. 1970년대에도 그랬듯이 SQL의 이러한 특징은 지금도 큰 장점으로 작용하고 있다.
1970년대 초반에는 소위 말하는 해커가 없었다. BASIC을 배우거나 HTML로 웹 페이지를 만들면서 어린 시절을 보낸 사람이 없었기 때문이다. 컴퓨터 프로그래밍을 하는 사람은 컴퓨터를 속속들이 아는 사람들뿐이었다. SQL은 컴퓨터 기술을 잘 모르는 회계, 비즈니스 및 행정 담당자들도 손쉽게 관계형 데이터베이스를 활용할 수 있도록 하기 위해 개발되었다.
SQL이 컴퓨터 기술자가 아닌 일반인들에게 선풍적인 인기를 끌게 되자 1980년대에 오라클에서는 세계 최초로 대중적으로 사용할 수 있는 상용 SQL 시스템을 출시하기 시작했다. 오라클 SQL은 엄청난 반향을 불러 일으켰고, SQL을 기반으로 하는 산업 전반에 급속하게 퍼져 나갔다. 그 이후로 사이베이스, 인포믹스, 마이크로소프트를 비롯한 여러 회사에서 SQL 기반의 관계형 데이터베이스 관리 시스템(RDBMS)을 내놓게 되었다.
오라클 및 다른 경쟁 회사들이 막 증가하기 시작할 무렵에도 SQL은 비교적 최신 기술에 속했고, 아직 표준이 정해지지 않은 상태였다. 1989년이 되어서야 ANSI 표준 위원회에서 최초의 공식 SQL 표준을 제정하였다. 지금은 그 당시에 발표된 표준안을 SQL89라고 한다. 하지만 그 표준안에서는 SQL 언어의 기술적인 구조를 제대로 정의하지 못했다. 그로 인해 다양한 상용 SQL 언어가 어느 정도 비슷해지긴 했지만 여전히 문법이 조금씩 차이가 나서 서로 다른 시스템을 사용하기가 까다로웠다. 결국 1992년이 되어서야 ANSI SQL 표준이 제대로 정립되었다.
1992년에 발표된 표준안은 SQL92 또는 SQL2라고 한다. SQL2 표준에서는 상업용 소프트웨어에서 사용하는 다양한 확장 기능을 최대한 많이 포함시켰다. 대부분의 교차 DBMS 도구는 관계형 데이테베이스에서 사용하는 방법과 같이 SQL2를 바탕으로 표준화되었다. 하지만 SQL2 표준안에서 워낙 확장성을 중요시했기 때문에 표준을 완벽하게 따르는 관계형 데이터베이스는 너무 복잡하고 자원을 많이 소모한다.
SQL2가 SQL의 최종 표준안은 아니다. 객체 지향 데이터베이스 관리 시스템(OODBMS, Object-Oriented Database Management System)과 객체 관계형 데이터베이스 관리 시스템(ORDBMS, Object-Relational Database Management System)이 인기를 얻으면서 SQL 표준에도 객체 지향 데이터베이스 액세스를 지원해야 할 필요성이 제기되었다. 가장 최근에 나온 SQL3에서는 객체 지향 데이터베이스 액세스를 지원한다.
MySQL에서는 데이터베이스 서버 개발이라는 사업 분야에 새로운 방식으로 접근하였다. 몬티는 대기업 제품에 비해 성능이 떨어지는 새로운 초대형 RDBMS를 만들기보다 자주 쓰이는 SQL 기능만 제공하는 작고 빠른 데이터베이스를 만들기로 했다. 시간이 지나면서 MySQL은 더 많은 기능을 지원하게 되었고, 요즘은 데이터베이스 애플리케이션을 만들 때 필요한 거의 모든 기능을 제공한다.
SQL 설계
앞에서도 말했듯이 SQL은 간단하면서도 확실하게 정의된 명령문 형태의 구조를 가지기 때문에 컴퓨터 언어라기보다는 사람들이 사용하는 언어에 더 가깝다. "질의"라고 하는 SQL 명령은 영어 구문과 마찬가지로 언어 요소별로 나눌 수 있다. 다음과 같은 예를 생각해보자:
 CREATE  TABKE    people (name CHAR(10)) 동사    목적어     형용사구 INSERT INTO people   VALUE ('me') 동사   간접 목적어   직접목적어 SELECT   name         FROM people     WHERE name LIKE '%e' 동사     직접목적어   간접 목적어        형용사구
MySQL을 포함해서 대부분의 SQL에서는 대소문자를 구분하지 않는다. SQL 키워드의 대소문자는 전혀 구분하지 않는다. 위에 있는 CREATE 구문의 예는 다음과 같이 입력해도 된다:
 cREatE TAblE people (name cHAR(10))
하지만 SQL 키워드를 제외한 부분에서는 대소문자르르 구분한다. MySQL에서는 데이터베이스, 테이블 및 열의 이름에서도 대소문자를 구분한다. 하지만 모든 데이터베이스 엔진에서 이런 식으로 대소문자를 구분하지는 않는다. 따라서 데이터베이스에서 사용할 수 있는 애플리케이션을 만들고 싶다면 모든 이름에 사용하는 대소문자를 확실히 구분하는 것이 좋다.
SQL 질의는 반드시 동사로 시작한다. 이 동사는 데이터베이스 엔진에서 취할 동작을 나타낸다. 선언문의 나머지 부분은 동사에 따라 다르지만 항상 정해진 형식으로 따른다. 동작을 취할 대상의 이름을 적은 다음 그 동작에서 사용할 데이터를 기술한다. 예를 들어, CRETE TABLE people (name CHAR(10))이라는 문장에서는 CREATE라는 동사를 사용하고, 그 뒤에 TABLE이라는 목적어가 온다. 질의의 나머지 부분에는 새로 만들 테이블에 대한 설명이 들어간다.
SQL 질의는 클라이언트(사용자가 데이터베이스와 데이터를 주고받는 창구같은 역할을 하는 애플리케이션)에서 입력한다. 클라이언트에서는 사용자의 동작에 따라 질의를 구성한 다음 그 질의를 SQL 서버에 보낸다. 서버에서는 그 질의를 처리하고 주어진 동작을 수행한다. 서버에서 할 일을 끝마치면 어떤 값을 클라이언트에 보낸다.
SQL의 주된 목적은 데이터베이스 서버에 어떤 동작을 전달하는 것이기 때문에 범용 언어만큼 유연성이 뛰어나지는 않다. SQL의 거의 모든 기능은 데이터 추가, 변경, 읽기와 같은 데이터베이스의 입출력과 연관되어 있다. 다른 기능도 어느 정도 제공하지만 항상 데이터베이스에 있는 데이터를 조작하는 방법에 주안점을 둔다.
MySQL에 SQL을 보내는 방법
MySQL에 SQL을 보낼 때는 다양한 방법을 사용할 수 있다. 그 중 3부에서 다루는 프로그래밍 API를 경유하는 방법을 가장 많이 사용한다. 하지만 이 장에서는 SQL을 배우는 것이 목적이므로 대화식 명령행 도구인 mysql을 사용하기로 하자. 명령행에서 이 프로그램을 실행시키면 SQL을 받아들일 수 있는 프롬프트가 나타난다:
 [09:04pm] carthage$ mysql -u root -p Enther password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 3 to server version: 3.22.29 Type 'help' for help. mysql>
위에 나와 있는 'mysqwl -u root -p'는 루트 사용자로(-u 옵션) 비밀번호를 입력하도록 하여 (-p 옵션) MySQL 서버에 연결하라는 명령이다. 원격 호스트에 있는 MySQL 서버에 연결할 때에는 -h 옵션을 사용한다:
 [09:04pm] carthage$ mysql -u root -h db.imginary.com -p
운영체제의 계정과 MySQL의 계정은 서로 독립적이다. 즉 MySQL용 사용자 목록이 따로있고 MySQL이 있는 호스트와는 별개로 MySQL 관리자가 별도로 새로운 사용자를 추가해야 한다. 따라서 처음 MySQL을 설치했을 때에는 루트 외에는 다른 사용자가 전혀 없다. 여기에서 루트는 유닉스 루트 게정과는 다르다. 일반적으로 데이터베이스 관리 작업을 하는 경우를 제외하면 절대 MySQL에 루트로 접속하면 안 된다. 새로 설치한 MySQL이 있고, 그 MySQL을 독자가 임의대로 쓸 수 있다면 루트로 접속해서 이 장에 나와 있는 데이터베이스를 만들고 지우는 예제를 실행해도 된다. 그렇지 않다면 항상 자신에게 할당된 계정으로 MySQL에 접속해야 한다.
mysql을 실행하고 나면 SQL 명령을 한 줄에 모두 입력해도 되고, 여러 줄에 걸쳐서 입력해도 된다. MySQL에서는 세미콜론이 입력될 때까지 SQL을 실행하지 않는다:
 mysql> SELECT book_number    -> FROM book    -> ; +-------------+ | book_number | +-------------+ |           1 | |           2 | |           3 | +-------------+ 3 rows in set (0.00 sec)
클라이언트 도구를 컴파일한 방법에 따라 mysql 명령행에서 명령 히스토리를 사용할 수 있다. mysql 클라이언트를 만들 때 명령 히스토리 기능을 포함시켜서 컴파일했다면 키보드의 위, 아래 화살표로 앞에서 실행시킨 SQL 명령을 다시 사용할 수 있다.
데이터베이스 생성
MySQL을 사용하려면 데이터베이스를 만들어야 한다. 우선 SHOW DATABASES 명령으로 처음 설치할 때 만들어진 데이터베이스를 살펴보자. MySQL 3.23.40을 설치하고 나면 다음과 같은 테이블이 생성된다:
 mysql> SHOW DATABASES; +-------------+ |     Database| +-------------+ |        mysql| |         test| +-------------+ 2 rows in set (0.37 sec)
첫 번째 데이터베이스인 mysql은 MySQL의 시스템 데이터베이스이며, 자세한 내용은 5장에 나와 있다. 두 번째 데이터베이스인 test는 MySQL을 배우거나 테스트할 때 쓸 수 있는 테스트용 데이터베이스이다. MySQL이 원래 깔려 있었다면 다른 데이터베이스가 들어있을 수 있다. 우선 MySQL의 CREATE 선언문을 사용하는 법을 알아보기 위해 새로운 데이터베이스를 만들어 보자:
 CREATE DATABASE TEMPDB;
그리고 새로운 데이터베이스인 TEMPDB로 작업을 하자:
 USE TEMPDB;
DROP DATABASE 명령을 내리면 데이터베이스를 삭제할 수 있다:
 DROP DATABASE TEMPDB;
방금 해 본 것과 같이 CREATE 선언문을 이용하면 새로운 대상을 만들 수 있고 DROP 선언문을 이용하면 삭제할 수 있다.
테이블 관리
지금까지 MySQL 서버에 있는 데이터베이스에 연결하는 방법을 알아보았다. 이제 MySQL을 설치했을 때 기본으로 들어 있는 test 데이터베이스 또는 사용자가 만든 실습용 데이터베이스를 사용하여 실습해 보자. 데이터베이스의 목록을 출력할 때와 마찬가지로 SHOW 명령어로 현재 데이터베이스에 들어 있는 테이블의 목록을 출력할 수 있다. mysql 시스템 데이터베이스에 연결된 상태에서 SHOW TABLES 명령을 내리면 다음과 같은 화면이 출력된다:
 mysql> USE mysql; Database changed mysql> SHOW TABLES; +-------------------+ |    Tables_in_mysql| +-------------------+ |       columns_priv| |                 db| |               func| |               host| |        tables priv| |               user| +-------------------+ 6 rows in set (0.00 sec)
위에 있는 여섯 개의 테이블은 MySQL이 동작하기 위해 필요한 시스템 테이블이다. 이 테이블 중 특정 테이블의 구성을 알고 싶다면 DESCRIBE 명령을 사용하면 된다:
 mysql> DESCRIBE db; +----------------------------------------------------------------------+ |Field           |Type           |Null |Key |Default   |Extra   |      | +----------------------------------------------------------------------+ |Host            |char(60) binary|     |PRI |          |        |      | |Db              |char(64) binary|     |PRI |          |        |      | |User            |char(16) binary|     |PRI |          |        |      | |Select priv     |enum('N', 'Y') |     |    |N         |        |      | |Insert priv     |enum('N', 'Y') |     |    |N         |        |      | |Update priv     |enum('N', 'Y') |     |    |N         |        |      | |Delete priv     |enum('N', 'Y') |     |    |N         |        |      | |Create priv     |enum('N', 'Y') |     |    |N         |        |      | |Drop priv       |enum('N', 'Y') |     |    |N         |        |      | |Grant priv      |enum('N', 'Y') |     |    |N         |        |      | |References priv |enum('N', 'Y') |     |    |N         |        |      | |Index priv      |enum('N', 'Y') |     |    |N         |        |      | |Alter priv      |enum('N', 'Y') |     |    |N         |        |      | +----------------------------------------------------------------------+ 13 rows in set (0.36 sec)
13 rows in set (0.36sec) 위의 결과를 보면 테이블에 있는 각 열이 나오고, 그 열에 널 값이 들어 있는지 여부, 키의 종류, 기본값, 추가 정보 등이 나와 있다. 이게 무슨 소린지 잘 모르는 독자들도 있겠지만, 앞으로 모두 배우게 될 내용이니 걱정하지 않아도 된다.
이제 테이블을 직접 만들어 보자. 우선 MySQL을 처음 설치하면 자동으로 생성되는 test 데이터베이스에 접속하자:
 USE test;
mysql에는 새로운 테이블을 추가하면 안 되기 때문에 test 테이터베이스에 우선 접속해야 한다는 점에 주의하도록 하자. 테이블(table)은 데이터를 구조적으로 저장하기 위한 컨테이너이며 관계형 데이터베이스에 있어서 가장 기본적인 개념이다. 테이블에 데이터를 추가하기 전에 데이블의 구조를 정의해야 한다. 다음과 같은 레이아웃을 생각해 보자:
 +------------------------------+ |          people              | +------------------------------+ | name     | char(10) not null | +------------------------------+ | address  | text(100)         | +------------------------------+ | id       | int               | +------------------------------+
테이블에는 열 이름뿐 아니라 각 필드의 유형과 그 필드에 들어갈 수 있는 추가 정보까지 포함된다. 필드의 데이터 유형은 그 필드에 저장할 수 있는 데이터 필드의 종류를 나타낸다. SQL의 데이터 유형은 다른 프로그래밍 언어의 데이터 유형과 비슷하다. SQL 표준에서는 매우 광범위한 데이터 유형을 사용할 수 있도록 되어 있다. MySQL에서는 SQL 표준에 있는 거의 모든 유형 외에도 MySQL에만 있는 몇 가지 데이터 유형을 지원한다.
테이블을 만들 때 사용하는 일반적인 문법은 다음과 같다:
 CREATE TABLE table_name (  column_name1 type [modifiers]  [, column_name2 type [modifiers]] )
사용할 수 있는 식별자(identifier, 테이블이나 열의 이름)는 DBMS에 따라 조금씩 다르다. MySQL에서는 식별자로 64자까지 사용할 수 잇으며, $ 문자도 사용할 수 있고 숫자로 시작해도 된다. 또한 MySQL이 설치된 시스템에서 사용할 수 있는 문자 세트에 포함된 모든 문자를 식별자로 사용할 수 있다.
열(column)은 테이블에 있는 행을 위한 개별 데이터의 단위이다. 테이블에 들어갈 수 있는 열의 개수에는 제한이 없지만 열이 너무 많아지면 효율이 떨어진다. 이런 면에서 7장에서 다루는 좋은 데이터베이스 설계 방법이 중요하다. 정규화된 테이블을 잘 만들면 여러 개의 테이블에서 저장된 테이터를 연결하여 검색할 수 있다. 연결(join)에 대한 것은 이 장의 후반부에 다룬다.
다음과 같은 CREATE 선언문을 생각해 보자:
 CREATE TABLE USER (  USER_ID BIGINT UNSIGNED NOT NULL PRIMARY KEY,  USER_NAME CHAR(10) NOT NULL,  LAST_NAME VARCHAR(30),  FIRST_NAME VARCHAR(30),  OFFICE CHAR(2) NOT NULL DEFAULT 'NY');
이 선언문은 USER_ID, USER_NAME, LAST_NAME, FIRST_NAME, OFFICE라는 다섯 개의 열이 있는 USER라는 테이블을 만든다. 각 열 이름 뒤에는 그 열의 데이터 유형이 나오고 그 뒤에 다른 변경자(modifier)가 붙는다.
NOT NULL 변경자는 그 열에 널 값이 들어갈 수 없다는 것을 의미한다. 그 열에 널 값을 대입하려고 하면 SQL에서 에러가 난다. 하지만 여기에는 두 가지 예외가 있다. 첫 번째는 어떤 열을 AUTO_INCREMENT로 정의한 경우인데 이 때는 널값을 대입하면 새로운 값이 자동으로 생성된다(자동 증가는 잠시 후에는 알아보기로 하자). 두 번째는 위에 나온 OFFICE 열에서처럼 열에 기본값을 지정하는 경우인데 이 때는 널 값을 대입하면 기본값(위의 경우에는 NY)이 대입된다(데이터 유형과 PRIMARY KEY 변경자에 대한 내용은 잠시 후에 다루겠다).
언제나 그렇듯이 삭제하는 것은 만드는 것보다 쉽다. 데이터베이스에서 테이블을 삭제하는 명령은 다음과 같다:
 DROP TABLE table_name
위와 같은 명령을 사용하면 데이터베이스에서 table_name이라는 이름을 가진 테이블이 흔적도 없이 사라진다. 주어진 테이블에 있는 모든 데이터는 남김 없이 삭제된다. 일단 지우고 나면 테이블을 미리 백업해두지 않은 이상 절대로 데이터를 살릴 수 없다. 따라서 항상 데이터를 백업해 두고 테이블을 삭제할 때는 신중을 기해야 한다. 데이터를 백업해 두는 습관을 길러 두면 언젠가는 반드시 크게 도움이 될 것이다.
MySQL에서는 테이블 이름을 쉼표로 구분하여 입력하면 여러 개의 테이블을 한꺼번에 삭제할 수 있다. 예를 들어, DROP TABLE people, animals, plants라는 명령을 사용하면 people, animals, plants라는 세 개의 테이블이 지워진다. 또한 삭제하고자 하는 테이블이 원래 없는 경우를 대비하여 IF EXISTS 변경자를 사용할 수도 있다. 이 변경자는 데이터베이스와 그 안에 들어갈 모든 테이블을 한꺼번에 만드는 복잡한 스크립트를 만들 때 많이 쓰인다. 이러한 스크립트에서는 데이터베이스를 만들기 전에 DROP TABLE IF EXISTS table_name이라는 명령을 사용한다.
MySQL 데이터 유형
테이블의 각 열에는 유형이 있다. 앞에서 언급했다시피 SQL의 데이터 유형은 일반적인 프로그래밍 언어의 데이터 유형과 비슷하다. 보통 컴퓨터 언어에서는 완결성을 위한 최소한의 유형만 정의하지만 SQL에서는 일반 사용자들의 유용하게 쓸 수 있도록 DATE와 같은 유형들을 추가로 제공한다. DATE 유형을 그냥 숫자 유형으로 저장할 수도 있지만, 날짜 처리를 위한 유형을 따로 만들어 두면 SQL을 더 쉽게 사용할 수 있다(사용의 용이성은 SQL의 핵심 목표중 하나다).
MySQL에서 지원하는 SQL 유형에 대한 자세한 내용은 16장에서 나와 있다. [표 3-1]에서는 가장 흔하게 쓰이는 유형을 간략하게 모아놓았다.
[표 3-1] MySQL에서 자주 사용하는 데이터 유형 (전체 목록은 16장 참조)
데이터 유형
설명
INT
정수값. MySQL에서 INT는 부호가 있을 수도 있고 없을 수도 있다.
REAL
부동수수점 값. 이 유형은 INT 유형에 비해 범위도 더 넓고 유효숫자의 수도 많지만 INT처럼 정확하지는 않다.
CHAR(length)
길이가 고정된 문자 값. CHAR 필드에서는 length 값으로 정해진 길이 이상의 문자열을 저장할 수 없다. 필드의 길이가 짧으면 남는 부분은 공백으로 채워진다. SQL에서 가장 많이 쓰이는 유형.
VARCHAR(length)
길이가 바뀔 수 있는 문자 값
TEXT(length)
길이가 바뀔 수 있는 문자 값
DATE
표준 날짜 값. DATE에는 과거, 현재, 미래의 임의의 날짜를 저장할 수 있다.
TIME
표준 시간 값. 이 유형에는 날짜와 상관 없이 시각을 저장 할 수 있다. 날짜와 함께 사용하면 특정한 날짜의 특정한 시각을 저장할 수 있다. MySQL에서는 한 필드에 날짜와 시각을 동시에 저장할 수 있도록 DATETIME이라는 유형을 지원한다.
MySQL에서는 모든 숫자 유형에서 UNSIGNED 속성을 지원한다. 이 변경자를 사용하면 그열에 (부호는 없는) 양수만 저장할 수 있다. 부호가 없는 필드의 최대값은 부호가 있는 필드의 최대값의 두 배다. 예를 들어, 부호가 없는 TINYINT(MySQL의 1바이트 숫자 유형)의 범위는 0 에서 255까지이고, 부호가 있는 TINYINT의 범위는 -128에서 127까지이다. MySQL에서는 [표 3-1]에 정리한 것보다 더 많은 유형을 지원한다. 하지만 일상적인 프로그래밍에서는 위에 나와 있는 유형을 가장 많이 사용할 것이다. MySQL 테이블을 설계할 때에는 저장하고자 하는 데이터의 크기에 알맞은 유형을 선택하는 것이 좋다.
숫자 유형
테이블을 만들기 전에 먼저 테이블에 저장할 테이터의 종류를 알아야 한다. 데이터가 문자 기반인지 숫자 기반인지를 결정하고 나면 저장할 데이터의 대략적인 크기를 알아야 한다. 숫자 필드라면 최대, 최소값이 얼마인지, 나중에 바뀔수 있는지 등을 고려해야 한다. 최소값이 0 이상이라면 부호가 없는 유형을 사용하는 것이 좋다. 될 수 있으면 사용하게 될 최대값을 지원하는 유형 중 가장 작은 숫자 유형을 사용해야 한다. 예를 들어, 어떤 주의 인구를 나타내튼 필드가 있다면 부호가 없는 INT 필드를 사용하면 된다. 인구 수가 음수인 경우는 없고, 그 주에 사는 사람들의 수가 전 세계 인구수에 육박하지 않는 이상 한 주의 인구를 나타내기에는 부호가 없는 INT 필드로도 충분하기 때문이다.
문자 유형
문자 유형은 조금 더 복잡하다. 문자열의 최소 및 최대 길이뿐만 아니라 길이의 평균 및 편차도 감안해야 한다. 인덱스(index)는 검색하고자 하는 필드 또는 필드의 조합이다(WHERE 절에 들어갈 필드라고 생각하면 된다). 인덱싱은 사실 훨씬 복잡하기 때문에 나중에 자세히 다루기로 하겠다. 일단 여기에서 중요한 것은 필드의 길이가 고정되어 있을 때 문자 필드에 대한 인덱싱이 가장 잘 동작한다는 점이다. 문자 필드의 길이에 편차가 거의 없을 때(가능하다면 아예 없을 때)에는 CHAR 유형이 가장 좋다. CHAR 필드로 사용할 수 있는 좋은 예로 국가 코드를 들 수 있다. ISO에서는 국가 코드를 나타내는 두 글자로 표기되는 표준을 정해 놓았다(미국은 US, 프랑스는 FR 등). 이렇나 코드는 항상 두 글자이므로 ISO 표기법을 따르는 국가 코드를 저장할 때에는 CHAR(2)를 사용하는 것이 최선의 방법이다.
CHAR 필드에 저장되는 값의 길이가 항상 일정해야 하는 것은 아니다. 하지만 편차가 아주 작아야 한다. 예를 들어, 전화번호는 나라마다 조금씩 다르긴 하지만 CHAR(13)에 저장해도 문제가 되지 않는다. 편차가 충분히 작기 때문에 전화번호 필드를 굳이 길이가 바뀔 수 있는 필드로 만들지 않아도 된다. CHAR 필드를 사용할 때에는 저장되는 문자열의 길이가 아무리 크더라도 필드의 크기로 정해진 문자의 개수만큼의 공간을 차지한다는 점을 기억해 두자(절대로 그 이상, 그 이하의 공간을 차지하지 않는다). 저장되는 텍스트의 길이와 필드의 길이가 차이가 난다면 남는 부분은 공백으로 채워진다. 전화번호 데이터에서 조금 공간이 남아도는 게 큰 문제가 안 될지 몰라도 너무 많은 공간을 낭비하는 것은 좋지 않다.
가변 길이(variable-length) 텍스트 필드는 길이가 크게 달라지는 텍스트 필드에 적합하다. 가변 길이 데이터 유형이 필요한 필드의 대표적인 예로 웹 URL을 들 수 있다. 대부분의 웹 주소는 비교적 짧기 때문에(예를 들면, http://www.ora.com이나 http://www.imaginary.com, http://www.mysql.com 등) 별 문제가 없지만 다음과 같이 긴 웹 주소가 등장할 때가 있다:
 http://www.innekw.com/wkwekekkkkkkkkkkkkkkkkkkkkkkkkkkkkjre
이렇게 긴 URL도 저장할 수 있을 만큼 큰 CHAR 필드를 만들면 다른 짧은 URL을 저장 할 때에는 너무 많은 공간을 낭비하게 된다. 하지만 가변 길이 필드를 사용하면 짧은 값을 저장할 때에는 공간을 낭비하지 않고, 위와 같이 긴 텍스트 값도 저장할 수 있다.
MySQL의 길이를 바꿀 수 있는 텍스트 필드는 필드에 값을 저장하는데 필요한 공간만을 차지한다. 예를 들어, VARCHAR(225) 필드에 "hello world"라는 문자열을 저장하더라도 12바이트(한 글자가 1바이트씩 차지하고, 나머지 1바이트에 길이를 저장한다)만 차지한다.
MySQL에서는 ANSI 표준과 다르게 VARCHAR 필드에서 남는 자리에 공백 문자를 채우지 않고, 값을 저장하기 전에 남는 공백을 모두 삭제한다.
지정한 필드 길이보다 긴 문자열을 저장할 수는 없다. VARCHAR(4) 필드를 만드었다면 최대 네 글자 길이의 문자열만 저장할 수 있다. 만약 "happy birthday"라는 문자열을 그 필드에 저장하려고 하면 MySQL에서는 "happ"까지만 저장한다. 이렇게 원래 정해 놓은 필드 크기를 벗어나는 문자열을 저장할 수 없다는 단점이 있다. [표 3-2]는 각 테스트 데이터유형으로 앞에 나왔던 144 길이의 와인 감별사 사이트 URL과 일반적인 30자 길이의 URL을 저장할 때 필요한 저장 공간 그리고 각 유형의 최대 문자열 길이를 정리해 놓은 표다.
[표 3-2] 여러 가지 MySQL 문자 유형에서 필요한 저장 공간
데이터 유형
144자 문자열
30자 문자열
최대 문자열 길이
CHAR(150)
150
150
255
VARCHAR(150)
145
31
255
TINYTEXT(150)
145
31
255
TEXT(150)
146
32
65535
MEDIUMTEXT(150)
147
33
16777215
LONGTEXT(150)
148
34
4294967295
이 표를 보면 길이를 바꿀 수 있는 유형인 MEDIUMTEXT와 LONGTEXT에서는 데이터을 저장하는데 필요한 공간이 각각 1, 2 바이트씩 더 필요하다는 것을 알 수 있다. 이는 필드의 크기를 저장하는데 각각 1바이트씩 공간이 더 필요하기 때문이다. TEXT에서는 문자열 길이를 저장하는 데 1바이트로는 모자라기 때문에 VARCHAR보다 한 바이트가 더 필요하고 마찬가지 이유로 MEDIUMTEXT, LONGTEXT로 갈수록 2바이트, 3바이트 씩의 공간이 더 있어야 한다.
데이터베이스를 사용하다 보면 처음에는 VARCHAR(25) 만으로도 충분했던 필드 크기를 더 늘려야 할 때도 있는데, 이런 문제는 그리 어렵지 않게 해결할 수 있다. MySQL에서는 ALTER TABLE이라는 명령을 지원하기 때문에 이 명령을 이용하여 데이터 손실 없이 필드 유형을 재정할 수 있다:
 ALTER TABLE mytable MODIFY mycolumn LONGTEXT
이진 데이터 유형
MySQL에서는 문자 유형과 매우 유사한 이진 데이터 유형을 제공한다. MySQL의 이진 유형에는 CHAR, BINARY, VARCHAR, BINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB이 있다. 문자 유형과 그에 상응하는 이진 유형간의 실질적인 차이점은 인코딩이라는 개념에서 찾을 수 있다. 이진 데이터(binary data)은 기본적으로 MySQL에서 해석하지 않는 데이터 조각이다. 하지만 문자 데이터는 사람들이 사용하는 글자로 이루어진 텍스트 형식의 데이터를 나타낸다. 따라서 문자 데이터는 해당 문자 세트에 알맞은 규칙을 바탕으로 인코딩되고 정렬된다. ASCII 시스템에서 문자 데이터를 정렬할 때에는 대소문자를 구분하여 ASCII 순서대로 정렬한다.
열거 및 집합
그 밖에도 MySQL에서 제공하는 특수 유형이 있다. ENUM 유형(열거 유형)을 이용하면 테이블을 만들 때 그 필드에 들어갈 수 있는 값들의 목록을 지정할 수 있다. 예를 들어 apple, orange, kiwi, banana 중 하나만 들어갈 수 있는 fruit라는 열이 있는데, 이 열의 유형을 ENUM으로 한다고 가정해 보자:
 CREATE TABLE meal (meal_id INT NOT NULL PRIMARY KEY,                fruit ENUM('apple', 'orange', 'kiwi',                          'banana'))
이 열에 어떤 값을 넣는다면 그 값을 넣는다면 그 값을 위에 열거되어 있는 과일 이름 중 하나가 되어야 한다. MySQL에서는 미리 이 열에 들어갈 수 있는 값들을 알고 있기 때문에 문자열 형태가 아닌 숫자 형태로 저장한다. 즉 apple을 열에 대입할 때 문자열이 아니라 내부적으로 한 바이트자리 숫자로 바꿔서 저장한다. 하지만 MySQL에서 그 값을 읽어들이거나 질의에서 사용할 때에는 그냥 apple이라고 표기하면 된다. 또한 테이블에서 결과를 보거나 테이블을 호출할 때에도 apple을 사용하면 된다.
MySQL의 SET 유형(집합 유형)도 똑같은 방식으로 작동하지만 하나의 필드에 여러 값을 동시에 저장할 수 있고 바이트 대신 비트를 사용한다는 점이 다르다.
기타 데이터 유형
모든 데이터는 숫자 또는 문자 유형으로 저장할 수 있다. 사실 숫자도 문자 유영으로 저장할 수 있다. 하지만 그렇게 할 수 있다고 해서 꼭 그렇게 해야 하는 것은 아니다. 예를 들어, 데이터베이스에 날짜를 저장하는 것을 생각해 보자. 그 값을 유닉스 형식의 BIGINT로 저장할 수도 있고 날짜, 월, 연도를 저장하기 위한 열을 따로 만들 수도 있다. 하지만 그런 경우에 특정한 날짜보다 이틀 이상 이전의 날짜가 들어 있는 열을 찾으려면 어떻게 해야 할까? 그 날짜를 숫자로 표시해 놓은 값을 직접 계산하거나 날짜, 월, 연도 값을 모두 고려하는 아주 복잡한 연산을 처리해야 할 것이다.
실제 프로그래밍을 할 때 이렇게 하면 정말 골치 아프다. MySQL에서 이런 문제를 모두 해결해 줄수는 없을까? 다행히도 MySQL에는 해결책이 있다. MySQL에서는 일상적인 개념을 데이터로 표현할 수 있도록 여러 가지 복합 데이터 유형을 제공한다. 날짜의 개념을 DATE 데이터 유형을 통해 지원한다. 이와 비슷한 것으로 DATETIME과 TIMESTAMP가 있다.
인덱싱
MySQL은 다른 대형 데이터베이스 서버보다 속도가 훨씬 빠르지만, 데이터베이스를 설계할 때에는 몇가지 문제를 고려해야 한다. 예를 들어, 행의 개수가 수백만 개인 테이블을 사용한다면 하나의 행을 찾는 데에도 꽤 오랜 시간이 걸릴 수 있다. 대부분의 데이터베이스 엔진에서는 빠른 검색을 위해 인덱스를 사용한다.
인덱스는 데이터베이스에서 데이터를 저장할 때 더 빠르게 검색할 수 있도록 도와주는 역할을 한다. 이렇게 검색 속도를 향상시키려면 어쩔 수 없이 디스크 공간과 데이터 수정 속도 면에서 약간의 희생을 감수해야 한다. 인덱스의 효율을 높이고 싶다면 가장 많이 검색하는 열의 인덱스를 만드는 것이 좋다. 데이블의 인덱스를 만들 때에는 다음과 같은 구문을 사용한다:
 CREATE INDEX index_name ON tablename (column1, column2, ..., columnN)
다음과 같이 하면 테이블을 처음 만들 때 인덱스도 같이 만들 수도 있다:
 CREATE TABLE masterial (id INT NOT NULL,                        name CHAR(50) NOT NULL,                        resistance INT,                        melting_pt REAL,                        INDEX index1 (id, name),                        UNIQUE INDEX index2 (name))
이 예에서는 테이블에 두 개의 인덱스를 만든다. 첫 번째 인덱스(index1)는 id와 name 필드로 구성된다. 두 번째 인덱스에는 name 필드만 포함되며 name 필드의 값은 반드시 유일해야 한다고 되어 있다. 이미 데이터베이스의 다른 행의 name 열에 들어 있는 값과 같은 name 값을 가진 필드를 새로 삽입하면 에러가 난다. 보통 유일 인덱스(UNIQUE INDEX)에 포함된 필드는 NOT NULL로 선언해야 한다.
name 자체의 인덱스는 만들었지만 id의 인덱스는 별도로 만들지 않았다. id만으로 구성된 인덱스가 필요하다면 굳이 따로 만들 필요가 없다. 이미 만들어져 있기 때문이다. 인덱스에 두 개 이상의 열이 포함된다면(예를 들어, name, rank, serial_number 같은 식으로) MySQL에서는 그 열들을 왼쪽에서 오른쪽으로 가는 열의 부분집합이 메인 인덱스 안에 자동으로 생성된다. 예를 들어, name, rank, serial_number 인덱스를 만들면 name 자체와 name, rank로 구성되는 인덱스가 자동으로 만들어진다. 하지만 rank만으로 또는 name과 serial_number로 이루어지는 인덱스는 따로 만들어야 한다.
MySQL에서는 기본키(primary key)라는 특별한 인덱스에 대한 ANSI SQL 문법도 지원한다. MySQL에서 기본키는 PRIMARY라는 이름이 붙은 유일한 키이다. 어떤 열을 기본키로 선언하면 이 열을 테이블 연결을 지원하는 지원하는 유일 인덱스가 된다. 다음은 id를 기본키로 하여 cities라는 테이블을 만드는 예이다:
 CREATE TABLE cities (id INT NOT NULL PRIMARY KEY,                    name VARCHAR(100),                    pop MEDIUMINT,                    founded DATE)
테이블을 만들기 전에 우선 키를 지정할 계획이라면 어떤 필드를 키로 지정할지 결정해야 한다. 앞에서 말했듯이 연결을 지원할 필드를 기본키로 지정하는 것이 좋다. 테이블을 만들 때 적절한 기본키를 선정하는 방법은 7장에서 자세하게 알아보자.
ANSI SQL에서는 외부키(foreign key)라는 특별한 키를 지원한다. 외부키는 다른 테이블에 의존적인 관계를 가진 행의 삭제와 같은 작업을 데이터베이스에 관리할 수 있게 함으로써 데이터베이스의 무결성을 보호하는 도움을 준다.
MySQL에서도 외부키에 대한 ANSI 문법을 지원하긴 하지만 데이터베이스의 무결성을 검사할 때에 실제 그 기능을 사용하지는 않는다. 이 기능은 속도만 느리게 만들고 실절적인 장점이 거의 없기 때문이다. 일반적으로 외부키 무결성은 애플리케이션 자체에서 신경을 쓰도록 하는 편이 좋다.
데이터 관리
데이블을 새로 만들고 나서 가장 먼서 할 일은 데이터를 추가하는 것이다. 데이터를 집어넣고 나면 테이블을 관리(추사, 수정 및 삭제)해야 한다.
삽입
테이블에 행을 추가하는 방법은 비교적 간단하다. 이미 앞에서도 몇 번 해 본 작업이다. 우선 SQL 표준 INSERT 문법을 사용하는 방법이 있다:
 INSERT INTO table_name (column1, column2, ..., columnN) VALUES (value1, value2, ..., valueN)
이 문법에서는 열의 이름을 지정한 다음 그 열에 채울 값들을 순서대로 입력한다. 숫자 필드에 데이터를 삽입할 때에는 그대로 사용하면 되지만 다른 필드에 들어갈 값들은 작은 따옴표로 감싸야 한다. 예를 들어, 주소록 테이블에 새로운 행을 삽입할 때에는 다음과 같은 식으로 하면 된다:
 INSERT INTO addresses (name, address, city, state, phone, age) VALUES ('Irving Forbush', '123 Mockingbird Lane', 'Corbin', 'KY',      '(800) 555-1234', 26)
작은 따옴표나 이스케이프 문자 자체를 표시할 때에는 이스케이프 문자(기본값은 )를 앞에 덧붙이면 된다:
 # c:PersonalStacie 에 있는 Stacie's Directory라는 # 디렉토리에 대한 정보를 넣는다. INSERT INTO files (description, location) VALUES ('Stacie 's Directory', 'C:\Personal\Stacie')
MySQL에서는 모든 값을 테이블을 만들 때 사용한 CREATE 명령에서 지정한 열 순서대로 값을 입력할 때에는 열 이름을 생략해도 된다. 하지만 어떤 열에 기본값이 있다면 기본값이 아닌 테이더를 입력하고자 하는 열의 이름은 확실히 지정해야 한다. 예를 들어, 앞에 나온 files 테이블에 size라는 열이 있었다면 그 열에는 Stacie's Directory가 아닌 기본값이 적용된다. 기본값은 테이블을 만드는 CREATE 명령에서 지정할 수 있다. NOT NULL 이면서 기본값을 지정하지 않은 열이 있다면 INSERT 선언문에 그 열을 반드시 포함시키고 NULL이 아닌 값을 넣어야 한다.
MySQL 최근 버전에서는 한꺼번에 여러 행을 삽입할 수 있는 비표준 INSERT 호출도 지원한다:
 INSERT INTO foods VALUES (NULL, 'Oranges', 133, 0, 2, 39),                        (NULL, 'Bananas', 122, 0, 4, 29),                        (NULL, 'Liver', 232, 3, 15, 10)
간단한 시스템 관리에는 MySQL에서 지원되는 비표준 문법을 사용하는 것도 좋지만 데이터베이스 애플리케이션을 만들 때에는 속도 문제가 아주 중요한 경우를 제외하면 비표준 문법을 사용하지 않는 편이 좋다. 될 수 있으면 MySQL에선 허용하는 범위 안에서는 최대한 ANSI SQL2 표준을 따르도록 하자. 이렇게 해야 나중에 데이터베이스를 바꿔도 애플리케이션을 그대로 사용할 수 있다. 중간 규모의 데이터베이스를 사용하는 사람들도 대체로 나중에는 대규모 데이터베이스를 사용하고자 하는 경향이 있기 때문에 애플리케이션을 만들 때에도 이런 경우를 감안하여 유연하게 만들어야 한다.
MySQL에서는 열의 이름과 값을 함께 지정하는 비표준 문법도 지원한다:
 INSERT INTO book SET title='The Vampire Leatat, author='Anne Rice';
마지막으로 다른 테이블(또는 여러 개의 다른 테이블)의 데이터를 이용하여 데이터를 삽입할 수도 있다. 예를 들면, 다음과 같다:
 INSERT INTO foods (name, fat) SELECT food_name, fat_grams FROM recipes
INSERT 호출에 들어 있는 열의 개수가 SELECT 호출에 들어 있는 열의 개수와 같어야 한다는 점에 유의해야 한다. 또한 INSERT 열의 데이터 유형도 그에 해당하는 SELECT 열의 데이터 유형과 맞아야 한다. 마지막으로 INSERT 선언문에 들어가는 SELECT 절에는 ORDER BY 변경자를 사용할 수 없으며, INSERT 작업을 하는 테이블과 같은 테이블에 대해 SELECT를 실행시킬 수 없다.
시퀀스 생성
기본키로 가장 좋은 것은 기본키 역할 외에 데이터베이스에서 아무 의미가 없는 열이다. 기본키는 관계형 데이터베이스에서 각 열을 유일하게 알아볼 수 있도록 해 주는 도구이다. 계정이나 이메일 주소 같은 정보를 기본키로 사용하면 그 계정이나 이메일 주소가 그 사용자를 나타낼 수 있는 고유의 성질이라고 간주하는 셈이 된다. 만약 그 사람이 계정이나 이메일 주소를 바꾸면 데이터베이스에 있는 데이터의 무결성을 새로 점검해야 하기 때문에 일이 번거로워진다. 따라서 좋은 데이터베이스를 설계하려면 다른 의미를 가지지 않는 숫자를 기본키로 사용하는 것이 좋다.
아무런 의미를 가지지 않는 숫자를 기본키로 사용하고 싶다면 매번 새로운 행을 추가할 때마다 1씩 증가하는 숫자를 기본키로 사용하면 된다. 예를 들어, 앞에서 나온 cities 테이블의 경우에는 첫 번째로 집어넣는 도시의 id가 1이 되고, 두 번째 도시의 id는 2, 세번째 도시의 id는 3, 이런 식으로 id를 정하면 된다. 이러한 기본키 시퀀스를 제대로 관리하려면 한 번에 하나의 클라이언트만 이 숫자를 읽고, 그 값에 1을 더할 수 있도록 해야 한다. 이렇게 하고 싶다면 기본키 필드를 AUTO_INCREMENT로 지정하면 된다.
MySQL에서 데이블을 만들 때에는 두 개 이상의 열을 AUTO_INCREMENT로 지정할 수 없다. 어떤 열을 AUTO_INCREMENT로 지정하면 행을 삽입할 때 그 열에 0 또는 NULL 값을 집어넣을 때 현재 그 열에 있는 값 중 가장 큰 값에 1을 더한 값이 자동으로 입력된다. AUTO_INCREMENT 열은 반드시 인덱싱해야 한다. 다음과 같은 명령으로 자동으로 증가하는 id 필드가 있는 cities 테이블을 만들 수 있다:
 CREATE TABLE cities (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,                    name VARCHAR(100),                    pop INT,                    founded DATE)
INSERT 선언문에서 id 필드의 값을 0이나 NULL로 대입하면 첫 번째 행의 id 필드의 값은 자동으로 1이 된다. 아래의 명령은 AUTO_INCREMENT 기능을 활용하는 방법을 보여준다:
 INSERT INTO cities (id, name, pop) VALUES (NULL, 'Houston', 3000000)
테이블에 다른 데이터가 없는 상태에서 위와 같은 명령을 내리면 MySQL에서는 이 필드에 NULL이 아닌 1을 자동으로 넣는다(이 필드에서 NULL 값이 들어갈 수 없다). 테이블에 이미 다른 데이터가 있다면 새로 삽입한 행의 id 값에는 현재 있는 가장 큰 id 값에 1을 더한 숫자가 들어간다.
LAST_INSERT_ID() 함수에서 리턴한 값에 직접 1을 더해서 수동으로 시퀀스를 만들 수도 있다:
 UPDATE table_name SET id = LAST_INSERT_ID(id + 1);
AUTO_INCREMENT 속성은 테이블에 있는 정수 유형의 열 중 최대 1 개의 열에만 적용할 수 있다. 이 속성이 적용되는 열은 정수 유형이어야만 하며, 기본키 혹은 그 열만으로 유일 인덱스를 구성할 수 있어야 한다. 그러한 정수 필드가 있는 테이블에 새로운 행을 삽입할 때 해당 필드에 아무 값도 대입하지 않거나 NULL 값을 대입하면 그 열에 있는 값 중 가장 큰 값에 1을 더한 값이 자동으로 입력된다.
LAST_INSERT_ID() 함수에 대한 상세한 내용은 17장에 있다.
갱신
데이터베이스에 새로운 행을 삽입하는 것은 데이터 관리의 시작에 불과하다. 데이터베이스가 읽기 전용이 아닌 이상 데이터는 주기적으로 바뀐다. 표준 SQL 변경 선언문은 다음과 같다:
 UPDATE table_name SET column1=value1, column2=value2, ..., columnN=valueN [WHERE 절]
갱신하고자 하는 테이블의 이름을 지정한 다음 SET 절에 대입할 값을 적는다. WHERE 절에서는 갱신할 행을 지정한다. WHERE 절을 생략하면 테이블에 있는 모든 행을 갱신한다.
열에 리터럴 값 대신 계산 결과를 대입할 수도 있다. 다른 열에 있는 값으로부터 대입할 값을 계산하는 것도 가능하다:
 UPDATE years SET end_year = begin_year + 5
위의 명령을 사용하면 테이블의 모든 행에 있는 end_year 열에 begin_year 열의 값에 5를 더한 숫자를 대입할 수 있다.
WHERE 절
앞에서 SQL에서 가장 중요한 것 중 하나인 WHERE 절을 소개하였다. SQL에서 WHERE 절은 어떤 조건을 지정해서 테이블의 특정 행을 선택할 수 있게 해 주는 역할을 한다. 예를 들면 다음과 같다:
 UPDATE bands SET lead_singer = 'Ian Anderson' WHERE band_name = 'Jethro Tull'
이 UPDATE 문에서는 band_name이 Jethro Tull인 행에 대해서만 lead_singer 열을 바꾸도록 한다. band_name 열이 유일 인덱스가 아니라면 WHERE 절의 조건에 맞는 행이 여러 개 나올 수도 있다. SQL을 사용하다 보면 WHERE 절로 조작할 행을 선택하는 경우가 많다. WHERE 절에 들어 있는 열을 검색하게 되므로 일반적으로 WHERE 절에 많이 들어가는 열에 대해 인덱스를 만드는 것이 좋다. WHERE 절에서 사용할 수 있는 비교문은 잠시후에 알아보도록 하자.
삭제
데이터를 삭제하는 작업은 간단하다. 테이블 이름을 입력하고 WHERE 절을 이용하여 삭제할 행을 지정하기만 하면 된다:
 DELETE FROM table_name [WHERE 절]
WHERE 절을 사용할 수 있는 다른 명령과 마찬가지로 WHERE 절을 반드시 사용해야 하는 것은 아니다. WHERE 절을 생략하면 테이블을 있는 모든 데이터가 지워진다. SQL에 데이터를 없애는 명령어가 많이 있지만, 그 중 DELETE는 가장 쉽게 데이터를 지울 수 잇는 명령어이므로 특히 조심해야 한다.
MySQL 4.0에서는 명령어 하나로 여러 개의 테이블에 있는 데이블에 있는 데이터를 지울 수 있는, 다소 위험한 DELETE 형식을 새로 지원한다:
 DELETE table1, table2, ..., tableN FROM table1, table2, ... tableN [WHERE 절]
이 구문의 FROM 절은 간단한 형태의 DELETE 구문에서 쓰이는 FROM 절과는 다른 의미로 쓰인다. 즉 행을 지울 테이블을 나타내는 것이 아니라 WHERE 절에서 참조하는 테이블의 목록을 나타낸다. SELECT 선언문을 떠올려 보면 SELECT 선언문의 FROM 절과 똑같은 식으로 작동하는 것을 알 수 있다. 데이터를 지울 테이블을 DELETE 선어문 바로 뒤에 입력해야 한다:
 DELETE Author, Address FROM Author, Book, Address WHERE Author.author_id = Address.address_id AND Author.author_id = Book.author_id AND Book.publish_date < 1980;
위의 선언문을 사용하면 1979년 이전에 책을 출판한 모든 저자에 대한 정보(Author 테이블)와 그 저자의 주소에 대한 정보(Address 테이블)를 삭제할 수 있다. 하지만 DELETE 키워드 뒤에 Book 테이블을 입력하지 않았기 때문에 Book 테이블에 있는 책에 관한 정보는 그대로 남는다. WHERE 절에 대한 복잡한 내용은 나중에 알아보기로 하자.
질의
마지막으로 배울 SQL 명령은 SELECT이다. SELECT를 이용하면 데이터베이스에 있는 데이터를 볼 수 있다. SELECT는 SQL에 있는 다른 어떤 명령보다도 많이 쓰인다. 신규 데이터를 추가하거나 데이터를 변경하는 일은 가끔 있을 뿐 사실 데이터베이스에서는 대부분의 시간을 데이터를 읽어들이는 데 쓴다. SELECT 선언문의 일반적인 형식은 다음과 같다:
 SELECT column1, column2, ..., columnN FROM table1, table2, ..., tableN [WHERE 절]
위와 같은 문법으로 거의 모든 SQL 데이터베이스에서 데이터를 읽어올 수 있다. SELECT 선언문을 이용하면 하나 혹은 여러 개의 테이블로부터 원하는 열을 선택할 수 있다. WHERE 절은 찾고자 하는 데이터가 들어 있는 행을 선택하는 부분이다.
물론 여러 가지 방법으로 복잡한 그리고 강력한 질의를 처리할 수 있다(SELECT와 관련된 자세한 문법은 15장에서 다룬다). 아래의 SELECT 선언문은 가장 간단한 SELECT 명령이다:
 SELECT 1;
이 선언문은 간단하긴 하지만 아무 쓸모가 없다. 단지 1이라는 값을 가진 하나의 열을 가진 하나의 행이 질의의 결과로 돌아올 뿐이다. 다음과 같은 질의는 어느 정도 써 먹을 수가 있다:
 mysql> SELECT DATABASE(); +---------------+ | DATABASE()    | +---------------+ | TEST          | +---------------+ 1 row in set (0.01 sec)
DATABASE()라는 표현식은 현재 데이터베이스의 이름을 리턴하는 MySQL 함수이다(함수에 대한 자세한 내용은 잠시 후에 알아보기로 하자). 위와 같은 예에서 알 수 있듯이 SQL를 이용하여 중요한 정보를 간단하게 찾아낼 수 있다.
하지만 대부분의 경우 데이터베이스에 있는 테이블에서 테이터를 추출하기 위해 조금 더 복잡한 질의를 사용한다. SELECT 선언문의 첫 번째 부분에서는 가져오고자 하는 열을 열거한다. 모든 열을 선택할 때에는 *을 사용하면 된다. FROM 절은 그 열을 어떤 테이블로부터 선택할지를 나타낸다. WHERE 절에서는 행을 선택하는 조건을 지정할 있으며 두 개의 테이블을 연결하는 방법을 지정할 수도 있다.
연결
연결(join)은 어떤 테이블에 있는 데이터를 다른 테이블에 있는 데이터와 연관시켜주는 기능으로 관계형 데이터베이스의 핵심이라고 할 수 있다. 기본적인 연결 형식은 내부 연결(inner join)이라고도 한다. 테이블을 연결할 때에는 서로 다른 테이블에 있는 열 사이에 등호를 넣으면 된다:
 SELECT book.title, author.name FROM author, book WHERE book.author = author.id
이렇게 하면 두 테이블에 있는 행끼리 어떤 관계가 있을 때 두개의 서로 다른 테이블로부터 열을 뽑아낼 수 있다. 위의 질의에서는 book 테이블의 author 열의 값이 author 테이블의 id 값과 같은 경우를 찾아낸다. book 테이블은 [표 3-3], author 테이블은 [표 3-4]와 같이 주어지는 테이터베이스를 생각해 보자:
 [표 3-3] book 테이블 -------------------------------------------------------------------------------  ID           | 제목                   | 저자               | 페이지 -------------------------------------------------------------------------------  1            | The Green Mile         | 4                  | 894  2            | Guards, Guards!        | 2                  | 302  3            | Imzadi                 | 3                  | 354  4            | Gold                   | 1                  | 405  5            | Howling Mad            | 3                  | 294 ------------------------------------------------------------------------------- [표 3-4] author 테이블 -------------------------------------------------------------------------------  ID           | 이름                       | 국적 -------------------------------------------------------------------------------  1            | Isaac Asimov               | US               | Terry Pratchett            | UK  2            | Peter David                | US  3            | Stephen King               | US  4            | Neil Gaiman                | UK -------------------------------------------------------------------------------
내부 연결을 하면 데이터베이스 서버에서는 두 테이블로부터 질의를 만족시키는 행을 찾아서 두 테이블의 필드를 합친 가상 테이블을 만든다. 위에 나온 예에서는 질의에서 book 테이블의 author 필드가 author 테이블의 id 필드와 같은 경우를 요구했다. 따라서 질의의 결과는 [표 3-5]와 같다:
 [표 3-5] 내부 연결에 의한 질의 결과 -------------------------------------------------------------------------------  책 제목                        | 저자 이름 -------------------------------------------------------------------------------  The Green Mile                 | Stephen King  Guards, Guards!                | Terry Pratcher  Imzadi                         | Peter David  Gold                           | Isasc Asimov  Howling Mad                    | Peter David -------------------------------------------------------------------------------
닐 게이먼(Neil Gaiman)은 위의 결과에 포함되지 않는다. 그 저자에 해당하는 author.id 값이 book 테이블의 author 열에 없기 때문이다. 즉 이 데이터베이스에 있는 책 중에는 닐 게이먼의 책이 하나도 없다는 것을 의미한다. 내부 연결의 결과에는 질의와 정확하게 일치하는 행만 포함된다. 데이터베이스에 저자가 들어 있지만 책이 없는 경우에 사용할 수 있는 외부 연결은 잠시 후에 알아보기로 하자.
별칭
테이블 및 열 이름을 만들다 보면 이름이 너무 길어지게 될 수 있다. 또한 SQL 함수를 사용하다 보면 하나의 선언문에서 같은 함수를 여러 번 호출하는 것이 귀찮게 느껴질 수도 있다. 별칭(alias)을 이용하면 이러한 문제를 해결할 수 있다. 보통 복잡한 이름 대신 더 간단하고 이해하기 쉬운 별칭을 사용한다. SQL 선언문에서 복잡한 이름 대신 간단한 별칭을 사용할 수 있다. 예를 들면, 다음과 같다:
 # 열 별칭 SELECT long_field_name_are_annoying AS myfiled FROM table_name WHERE myfiled = 'Joe' # 테이블 별칭 SELECT people.names, tests.score FROM tests, really_long_people_table_name AS people
정렬 및 분류
SELECT의 결과에는 따로 순서가 정해져 있지 않다. 하지만 이렇게 무작위적으로 열거된 결과를 원하는 순서대로 정리할 수 있도록 SQL에서는 정렬(ordering)과 분류(grouping) 기능을 제공한다.
기본정렬
데이터베이스에서 나온 결과는 특정 열을 기준으로 정렬할 수 있다. 예를 들어, 질의 결과를 last_name 순으로 정렬하도록 하면 last_name 값을 기준으로 알파벳 순서대로 정렬된 결과가 나타난다. 정렬은 ORDER BY 절로 처리할 수 있다:
 SELECT last_name, first_name, age FROM people ORDER BY last_name, first_name
위의 질의에서는 두 개의 열을 기준으로 정렬한다. 정렬을 할 때 기준의 되는 열의 개수에는 제한이 없다. 결과를 무작위순으로 출력하고 싶다면 ORDER BY RAND() 절을 사용하면 된다.
DESC (내림차순, descending) 키워드를 사용하면 순서를 거꾸로 하여(내림차순으로) 정렬 할 수 있다:
 ORDER BY last_name DESC
DESC 키워드는 그 키워드 바로 앞에 있는 필드에만 적용된다. 여러 개의 필드를 기준으로 정렬한다면 DESC 바로 앞에 있는 필드에 대해서만 내림차순으로 정렬하고 나머지에 대해서는 오름차순으로 정렬한다.
지역화 정렬
전 세계 여러 나라의 컴퓨터에서 실행시켜야 하는 애플리케이션에서는 사실 정렬이 꽤 골치 아픈 문제이다. 문자열을 정렬하는 방법이 사용하는 알파벳에 따라 다를 뿐만 아니라 같은 알파벳에 대해서도 정렬 순서가 다를 수 있기 때문이다. MySQL에서는 MySQL 엔진에서 사용하는 문자 세트에 따라 정렬을 다르게 하는 방법으로 이 문제를 해결한다. 처음 설치했을 때의 기본 문자 세트는 ISO-8859-1(Latin 1)이며 ISO-8859-1과 스웨덴어, 핀란드어의 정렬 방법을 기본으로 사용한다.
정렬 방벙을 바꾸고 싶다면 문자 세트를 바꾸면 된다. 우선 MySQL을 컴파일하고 설치할 때 원하는 문자 세트를 포함시켰는지 확인해야 한다. 서버를 컴파일할 때 문자 세트를 제대로 포함시켰다면 서버를 시작할 때 -default-characte-set=CHARSET 이라는 인자를 사용하여 기본 문자 세트를 바꿀 수 있다. 영문 알파벳은 워낙 간단하기 때문에 영어만 사용하는 경우에는 MySQL의 기본 문자세트인 ISO-8859-1을 그대로 사용하면 된다. 하지만 스웨덴어나 독일어는 둘 다 ISO-8859-1을 사용하긴 하지만 정렬할 때 문제를 일으킬 수 있다. 스웨덴어에서는 정렬을 할때 a가 z 다음에 오지만 독일어에서는 a가 a앞으로 간다. 따라서 독일어를 사용하는 경우에는 기본 정렬 방법을 그대로 사용할 수 가 없다.
MySQL에서는 이러한 문제를 해결하기 위해 사용자 정의 문자 세트를 만들 수 있도록 되어 있다. 원하는 문자 세트에 대한 설정 파일이 있다면 드라이버를 컴파일할 때 그 문자 세트 지원 기능을 포함시키면 된다. 이 파일에는 그 문자 세트를 구성하는 문자와 그 문자를 정렬하는 방법이 들어 있다. 이 파일은 직접 만들어서 쓸 수도 있고 MySQL에 포함되어 있는 것을 사용해도 된다.
여기에서 가장 문제가 되는 것
Posted by tornado
|