10. 데이터베이스
데이터베이스란 서로 관련된 자료들의 집합을 의미하며 컴퓨터 분야에서 데이터베이스 시스템 기술은 방대한 자료의 효율적 저장과 관리에 필수적인 요소입니다. 최근 개발되는 대다수의 웹프로그램은 데이터베이스와의 연동을 분리하고는 생각할 수 조차 없는 실정입니다. PHP역시 데이터베이스 시스템과의 연동을 염두해 두고 개발이 되었습니다. 따라서 이 책의 초반부에서 언급한 것과 같이 다양한 데이터베이스 시스템을 지원합니다. 이 장에서는 MySQL을 위주로 데이터베이스 시스템의 기본 개념에 대해서 공부하도록 하겠습니다.
10.1 데이터베이스 시스템
현대 사회는 방대한 양의 자료가 다양한 형태로 존재합니다. 학교의 경우 학생 및 교직원과 관련된 신상정보, 수강과목 및 성적 등 취급해야 하는 많은 정보들이 있습니다. 그러나 일반적으로 이러한 자료들은 구조화 되어있지 못하고 상당 부분이 중복 되는 등 여러 문제점으로 인하여 과거 업무 운영 측면에 있어 매우 비효율적이었습니다. 그러나 최근 컴퓨터 기술 특히 데이테베이스 시스템 기술의 비약적 발전으로 방대한 양의 자료들을 신속하고 효율적으로 처리할 수 있게 되었습니다.
데이터베이스 시스템이란 컴퓨터를 중심으로 자료를 저장 관리하고 이를 토대로 사용자에게 유용한 정보를 창출 제공하는 시스템을 말합니다. 이러한 데이터베이스 시스템은 데이터베이스 관리시스템(Database Management System), 데이터베이스 언어(Database Language), 관리자(Administrator) 그리고 사용자(User) 등으로 구성됩니다.
■ 데이터베이스 관리 시스템(DBMS)
데이터베이스 관리시스템이란 파일 시스템의 문제점 특히 자료의 종속성과 중복을 해결하기 위해 제안된 시스템으로 데이터베이스의 구성, 접근방법, 유지 관리 등에 대한 모든 책임을 지는 프로그램을 의미하며 흔히 DBMS라고 합니다.
■ 데이터베이스 언어(Database Language)
데이터베이스 언어는 크게 데이터 정의문(DDL)과 데이터 조작문(DML)로 구분할 수 있습니다. 데이터 정의문은 테이블 및 인덱스의 생성, 수정, 제거 등의 기능을 수행하는 언어입니다. 반면 DML은 테이블에 저장된 데이터의 검색, 수정, 삭제 등의 기능을 수행하는 언어입니다.
대표적인 데이터베이스 언어로 SQL이 있습니다. SQL에서 DDL로는 CREATE 문, ALTER 문, DROP 문 등이 있으며 DML로는 INSERT 문, SELECT 문, UPDATE 문, DELETE 문 등이 있습니다. SQL은 대부분의 DBMS에의해 지원되므로 SQL을 아는 것이 데이터베이스의 모든것이라 해도 무리가 아닙니다. 따라서 SQL의 중요성은 두말할 필요가 없으며 다음절에서 상세히 다루고 있습니다.
■ 데이터베이스 관리자(Administrator)
데이터베이스 관리자란 말 그대로 데이터베이스 시스템을 관리하고 접근 권한 등을 부여하는 등의 역할을 하는 사람입니다. 데이터베이스는 여러 사람이 공유하여 사용하는 만큼 비인가된 조작으로부터 데이터베이스 시스템을 보호해야 합니다. 예를 들어 학사관리 데이터베이스의 경우를 생각해 보겠습니다. 학사관리 데이터베이스에서 성적과 관련된 자료의 경우 해당 과목의 교수님은 학생에 대한 성적을 입력하고 수정할 수 있는 권한이 주어져야 합니다. 그러나 학생들에게도 그러한 권한이 주어진다면 큰 혼란이 야기될 것입니다. 그러므로 학생들에게는 단순히 검색 권한만이 주어집니다. 바로 이러한 권한 부여등과 관련된 일들이 데이터베이스 관리자가 하는 일 입니다.
■ 데이터베이스 사용자(User)
일반적으로 데이터 조작 및 검색등 데이터베이스에 접근이 부여된 대부분의 사람을 데이터베이스 사용자라고 합니다. 데이터베이스 사용자는 관리자가 부여한 권한에 따라 데이터베이스에 접근할 수 있습니다.
10.2 SQL
SQL(Structured Query Language)은 1974년 IBM 연구소에서 개발한 SEQUEL (Structured English QUEry Language)에 기원합니다. 이 SQL은 관계 대수와 관계 해석을 기초로 한 혼합 데이터 언어라고 볼 수 있습니다. SQL은 IBM 뿐만 아니라 ORACLE과 같은 제품에서도 채택하게 되어 SQL을 지원하는 관계 데이터베이스 시스템이 많이 나오게 되었습니다. 특히 이 SQL은 미국 표준 연구소(ANSI)와 국제 표준 기구(ISO)에서 관계 데이터베이스의 표준 언어로 채택함으로써 더욱 중요한 위치에 서게 된 언어입니다. SQL은 문자 그대로 구조화 질의어이지만 단순한 데이터 질의어 이상의 역할을 합니다. 즉 데이터 정의, 데이터 조작, 그리고 제어 기능을 모두 제공하고 있습니다. 여기서는 MySQL에서 지원하는 SQL 문을 중심으로 설명해 가도록 하겠습니다. 참고로 MySQL에서 지원하는 SQL 형식은 표준 SQL 형식과 다소 차이가 있는 경우도 있습니다.
■ CREATE DATABASE
데이터베이스란 관련된 데이터들의 집합이라고 하였습니다. 예를 들어 학교에서 데이터베이스 시스템을 도입하여 운영할 때 학생테이블, 과목테이블, 교수테이블, 성적테이블 등이 서로 관련된 다수의 데이블들이 학사관리라는 이름의 데이터베이스로 관리될 수 있습니다. 또한 도서관에서는 도서목록테이블, 도서대여테이블 등 또 다른 관련 테이블들이 도서관리라는 데이터베이스로 관리될 수 있습니다.
CREATE DATABASE는 학사관리, 도서관리 등과 같은 데이터베이스를 생성하는 문장으로 위 형식은 이름이 db_name인 데이터베이스를 생성합니다.
[예제 1] COLLEGE 라는 이름의 데이터베이스를 생성하시오.
■ DROP DATABASE
|
DROP DATABASE [IF EXISTS] db_name | |
위 형식은 이름이 db_name인 데이터베이스를 삭제합니다. 이 때 해당 데이터베이스에서 사용되는 모든 데이블도 함께 삭제 되므로 사용에 주의를 기울여야 합니다. [ ]안의 IF EXISTS는 데이터베이스가 존재하지 않을 경우 발생하는 오류를 방지하기위해 사용할 수 있습니다. 참고로 이하의 모든 형식에서 [ ] 안의 내용은 생략 가능함을 의미합니다.
[예제 2] COLLEGE 라는 이름의 데이터베이스를 삭제하시오.
■ CREATE TABLE
|
CREATE [TEMPORARY] TABLE [IF NOT EXIST] tbl_name [(create_definition,…)]
[table_options] [select_statement]
create_definition :
col_name type [NOT NULL|NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,…)
or KEY [index_name] (index_col_name, …)
or INDEX [index_name] (index_col_name,…)
or UNIQUE [INDEX] [index_name] (index_col_name,…)
or [CONSTRAINT symbol] FOREGIN KEY index_name (index_col_name,…)
[reference_definition]
or CHECK (expr)
type :
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
or INT[(length)] [UNSIGNED] [ZEROFILL]
or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
or CHAR(length) [BINARY]
or VARCHAR(length) [BINARY]
or DATA
or TIME
or TIMESTAMP
or DATETIME
or TINYBLOB
or BLOB
or MEDIUMBLOB
or LONGBLOB
or TINYTEXT
or TEXT
or MEDIUMTEXT
or LONGTEXT
or ENUM(value1, value2, value3, …)
or SET(value1, value2, value3, …)
index_col_name :
col_name [(length)]
reference_definition :
REFERENCES tbl_name [(index_col_name,…)]
[MATCH FULL | MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option :
RESTRICT | CASCADE | SET FULL | NO ACTION | SET DEFAULT
Table_option :
TYPE = {ISAM | MYISAM | HEAP}
or AUTO_INCREMENT = #
or AVE_ROW_LENGTH = #
or CHECKSUM = {0|1}
or MAX_ROWS = #
or MIN_ROWS = #
or PACK_KEYS = {0 | 1}
or PASSWORD = "string"
or DELAY_KEY_WRITE = {0 | 1}
or ROW_FORMAT = { default | dynamic | static | compressed }
select_statement :
[IGNORE | REPLACE] SELECT … (some legal select statement)
| |
위의 형식은 매우 복잡해 보이지만 실제로 첫 두 줄만이 형식의 전부이며 상당 부분은 거의 사용하지 않습니다. 위 형식은 create_definition에 의해 정의된, 이름이 tbl_name인 테이블을 생성합니다. create_definition은 각각의 필드(또는 애트리뷰트)에 대한 타입을 정의하는 부분입니다. 다음 표는 MySQL에서 지원하는 타입 중 일부를 나타낸 것입니다.
[Numeric Type]
|
타 입 |
설 명 |
|
TINYINT |
-128~127 (또는 0~255) 사이의 정수 |
|
SMALLINT |
-32768~32767 (또는 0~65535) 사이의 정수 |
|
MEDIUMINT |
-8388608~8388607 (또는 0 to 16777215)사이의 정수 |
|
INT, INTEGER |
-2147483648~2147483647(또는 0~4294967295) 사이의 정수 |
|
BIGINT |
-9223372036854775808~9223372036854775807 (또는 0~18446744073709551615) 사이의 정수 |
|
FLOAT |
-3.402823466E+38~-1.175494351E-38, 0, 1.175494351E-38~3.402823466E+38 사이의 실수 |
|
DOUBLE |
-1.7976931348623157E+308~-2.2250738585072014E-308, 0, 2.2250738585072014E-308~1.7976931348623157E+308사이의 실수 |
|
DOUBLE PRECISION, REAL |
DOUBLE과 동일 |
|
DECIMAL |
unpacked 실수로 최대 사용 범위는 DOUBLE과 동일 |
|
NUMERIC |
DECIMAL과 같다. | |
[Date / Time Type]
|
타 입 |
설 명 |
|
DATE |
1000-01-01 ~ 9999-12-31 사이의 날짜 |
|
DATETIME |
1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 사이의 날짜 및 시간 |
|
TIMESTAMP |
1970년 01월 01일 00시 00분 00초 이후의 시간을 자릿수(6,8,12,14)에 따라 YYMMDD, YYYYMMDD, YYMMDDHHMMSS, YYYYMMDDHHMMSS 형식으로 표현 |
|
TIME |
00:00:00 ~ 23:59:59 사이의 시간 |
|
YEAR |
네 자리 표현의 경우 0000년, 1901~2155 사이의 연도
두 자리 표현의 경우 1970~2069 사이의 연도 | |
[String Type]
|
타 입 |
설 명 |
|
CHAR |
길이가 정해진 문자열 |
|
VARCHAR |
가변길이의 문자열 |
|
TINY BLOB |
최대 길이가 255 (2^8 - 1)인 BLOB(바이너리 파일 등을 저장하는데 사용) 컬럼 |
|
TINY TEXT |
최대 길이가 255 (2^8 - 1)인 TEXT |
|
BLOB |
최대 길이가 65535 (2^16 - 1)인 BLOB 컬럼 |
|
TEXT |
최대 길이가 65535 (2^16 - 1)인 TEXT |
|
MEDIUM BLOB |
최대 길이가 16777215 (2^24 - 1)인 BLOB 컬럼 |
|
MEDIUM TEXT |
최대 길이가 16777215 (2^24 - 1)인 TEXT |
|
LONG BLOB |
최대 길이가 4294967295 (2^32 - 1)인 BLOB 컬럼 |
|
LONG TEXT |
최대 길이가 4294967295 (2^32 - 1)인 TEXT |
|
ENUM |
목록 컬럼으로 목록 내의 하나의 값 또는 NULL만을 가질 수 있는 문자열 객체 |
|
SET |
목록 컬럼으로 목록 내의 NULL 또는 하나 이상의 값을 가질 수 있는 문자열 객체 | |
CREATE TABLE 형식에서 NOT NULL은 특별히 열의 데이터 값이 널(NULL)이 될 수 없다는 것을 의미합니다. 예를 들면 테이블의 기본키에 속하는 열의 값은 널 값을 가질 수 없기 때문에 그런 성질을 가진 열의 값을 나타낼 사용할 수 있습니다. 이 NOT NULL이 생략된 열은 역으로 널 값을 가질 수 있다는 것을 의미합니다.
[예제 3] 다음과 같은 구조의 테이블을 생성하시오.
[테이블명 : STUDENTS]
|
필 드 명 |
필 드 타 입 |
특 징 |
|
STDNO |
CHAR(6) |
기본키(PRIMARY KEY) |
|
NAME |
VARCHAR(10) |
NOT NULL |
|
DEPARTMENT |
CHAR(2) |
|
|
YEAR |
INT(4) |
NULL인 경우 기본값으로 1 저장 | |
|
CREATE TABLE STUDENTS (
STDNO CHAR(9) NOT NULL,
NAME VARCHAR(10) NOT_NULL,
DEPARTMENT CHAR(2),
YEAR INT(4) NOT NULL DEFAULT 1,
PRIMARY KEY (STDNO)
)
| |
■ ALTER TABLE
|
ALTER TABLE tbl_name alter_spec [, alter_spec …]
Alter_spec :
ADD [COLUMN] create_definition [FIRST | AFTER column_name]
or ADD INDEX [index_name] (index_col_name, …)
or ADD PRIMARY KEY (index_col_name, …)
or ADD UNIQUE [index_name] (index_col_name, …)
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
or MODIFY [COLUMN] create_definition
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or RENAME [AS] new_tbl_name
or table_option | |
ALTER TABLE문을 이용하면 기존의 기본 테이블에 새로운 열을 추가하거나 삭제 또는 변경을 할 수 있습니다. 위 형식은 이름이 tbl_name인 테이블을 alter_spec에 따라 변경합니다.
[예제 4] 위의 INSERT 문에의해 생성된 STUDENTS 테이블에서 컬럼 DEPARTMENT의 타입을 CHAR(2)에서 CHAR(4)로 변경하시오.
|
ALTER TABLE STUDENTS
CHANGE COLUMN DEPARTMENT DEPARTMENT CHAR(4) | |
위 형식은 STUDENTS 테이블의 DEPARTMENT 필드에서 타입만을 변경한 경우입니다. 만약 타입과 함께 필드의 이름도 DEPT로 변경하기 위해서는 다음과 같이 ALTER 문을 바꿔줄 수 있습니다.
|
ALTER TABLE STUDENTS
CHANGE COLUMN DEPARTMENT DEPT CHAR(4) | |
■ OPTIMIZE TABLE
테이블에대한 조작이 많아지다 보면 저장공간이 낭비되어 비 효율적인 상태가 됩니다. 특히 삭제된 레코드가 차지하던 공간을 새로 추가된 레코드가 사용하지 못하는 경우가 발생하게 됩니다. 테이블의 이러한 낭비적 공간은 OPTIMIZE TABLE 문을 사용해 제거함으로써 테이블을 최적화 할 수 있습니다. 위 형식은 이름이 tbl_name인 테이블을 최적화합니다. 그러나 테이블이 최적화 되는 동안 테이블에 대한 조작은 읽기만 할 수 있습니다.
[예제 5] STUDENTS 테이블을 최적화 하시오
■ DROP TABLE
생성된 테이블은 DROP TABLE 문을 사용해 삭제할 수 있습니다. DROP TABLE 문에의해 삭제된 테이블을 다시 만들기 위해서는 CREATE TABLE 문을 사용해야 합니다. 위 형식은 이름이 tbl_name인 테이블을 데이터베이스에서 제거 합니다.
[예제 6] STUDENTS 테이블을 삭제하시오.
■ SELECT
|
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT]
[HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL]
select_expression, …
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name, …]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], …]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name]] | |
테이블에 저장된 자료의 검색은 SELECT 문을 이용합니다. 위 형식은 FROM 절에 주어진 이름이 tbl_name인 테이블로부터 WHERE 절에 주어진 조건과 일치하는 레코드들을 검색합니다. WHERE 절이 생략된 경우 tbl_name 테이블의 모든 레코드들을 검색합니다. 만약 특정 필드만을 출력하고 싶을 경우 SELECT 뒤에 원하는 필드명을 기술해주면 됩니다. SELECT 문은 자주 사용하므로 다양한 예제를 통해 살펴보겠습니다.
[예제 7] STUDENTS 테이블의 모든 레코드들을 검색하시오.
[테이블명 : STUDENTS]
(이후의 모든 예제는 다음과 같은 테이블 구성을 가정합니다.)
|
STDNO
CHAR(9) |
NAME
VARCHAR(10) |
DEPARTMENT
CHAR(20) |
YEAR
INT(4) |
|
960001 |
홍길동 |
인터넷정보 |
1 |
|
960003 |
이순신 |
인터넷정보 |
2 |
|
970012 |
유관순 |
웹프로그래밍 |
2 |
|
970001 |
김유신 |
인터넷정보 |
2 |
|
980100 |
성춘향 |
인터넷상거래 |
1 |
|
990055 |
강감찬 |
웹프로그래밍 |
1 |
|
980010 |
박문수 |
인터넷정보 |
1 |
|
950045 |
박정희 |
인터넷상거래 |
2 |
|
990001 |
이승만 |
인터넷상거래 |
1 | |
[테이블명 : SUBJECT]
|
SUBCODE
CHAR(5) |
SUBNAME
VARCHAR(20) |
|
S0001 |
웹프로그래밍 |
|
S0002 |
홈페이지제작 |
|
S0003 |
인터넷실무 |
|
S0004 |
시스템분석및설계 |
|
S0005 |
윈도우즈프로그래밍 | |
[테이블명 : ENROL]
|
STDNO
CHAR(6) |
SUBCODE
CHAR(5) |
SCORE
INT(3) |
|
960001 |
S0001 |
100 |
|
990055 |
S0001 |
90 |
|
960001 |
S0003 |
90 |
|
950045 |
S0005 |
62 |
|
980010 |
S0001 |
100 |
|
980010 |
S0004 |
85 |
|
960001 |
S0004 |
83 |
|
950045 |
S0002 |
55 |
|
980010 |
S0005 |
76 |
|
990055 |
S0002 |
55 | |
[결 과]
|
STDNO |
NAME |
DEPARTMENT |
YEAR |
|
960001 |
홍길동 |
인터넷정보 |
1 |
|
960003 |
이순신 |
인터넷정보 |
2 |
|
970012 |
유관순 |
웹프로그래밍 |
2 |
|
970001 |
김유신 |
인터넷정보 |
2 |
|
980100 |
성춘향 |
인터넷상거래 |
1 |
|
990055 |
강감찬 |
웹프로그래밍 |
1 |
|
980010 |
박문수 |
인터넷정보 |
1 |
|
950045 |
박정희 |
인터넷상거래 |
2 |
|
990001 |
이승만 |
인터넷상거래 |
1 | |
테이블의 열 전부를 검색할 때는 열 이름을 전부 나열할 필요없이 *로 표시하면 됩니다. 이 때 결과 테이블은 FROM 절에 주어진 테이블의 생성을 위한 CREATE 문에 정의된 열 순서대로 검색됩니다. 즉 위 명령문은 다음 검색문과 동등하다.
|
SELECT STDNO, NAME, DEPARTMENT, YEAR FROM STUDENTS | |
이 예에서 볼 수 있는 것과 같이 검색 연산의 결과는 다시 테이블이 됩니다. 즉 기존의 테이블을 처리해서 또 다른 테이블을 만드는 것입니다. 이러한 특징은 뒤에 설명할 중첩질의문(nested query)을 만들 수 있는 이론적 기초가 됩니다. 중첩질의문을 사용하면 논리적 명확성 이외에도 데이터베이스의 검색시간과 같은 시스템 성능에 많은 이점을 제공합니다. 그러나 아쉽게도 우리가 사용할 MySQL에서는 이 중첩질의문을 지원하지 않습니다.
[예제 8] STUDENTS 테이블에서 학과(DEPARTMENT)가 '인터넷정보'과인 학생의 학번(STDNO)과 이름(NAME)을 출력하시오.
|
SELECT STDNO, NAME FROM STUDENTS WHERE DEPARTMENT = '인터넷정보' | |
[결 과]
|
STDNO |
NAME |
|
960001 |
홍길동 |
|
960003 |
이순신 |
|
970001 |
김유신 |
|
980010 |
박문수 | |
위 예는 테이블내의 레코드들 중 조건에 일치하는 특정 레코드의 검색을 위해 WHERE 절을 사용했습니다. 일반적으로 WHERE 절에 나오는 조건식에는 비교연산자(=, <>, >, >=, <, <=)와 AND, OR, 그리고 NOT 등을 사용할 수 있습니다. 또한 필요한 경우에 괄호를 사용할 수 있다.
[예제 9] STUDENTS 테이블에서 '인터넷정보'과 1학년 학생들의 이름(NAME)과 학번(STDNO)을 검색하시오.
|
SELECT NAME, STDNO FROM STUDENTS
WHERE DEPARTMENT = '인터넷정보' AND YEAR = 1 | |
[결 과]
|
STDNO |
NAME |
|
960001 |
홍길동 |
|
980010 |
박문수 | |
위 예는 AND를 사용하여 인터넷정보과 1학년 학생들을 검색하고 있습니다. 참고로 SQL에서 사용되는 상수는 크게 숫자와 문자열(날짜 및 시간 포함)로 나눌 수 있습니다. 문자열 상수는 '인터넷정보'와 같이 반드시 작은인용부호(')와 함께 사용하며 하며 숫자 상수는 1과 같이 작은인용부호(') 없이 바로 사용합니다.
[예제 10] STUDENTS 테이블에서 성이 '박'씨인 학생을 검색하시오.
|
SELECT * FROM STUDENTS WHERE NAME LIKE '박%' | |
[결 과]
|
STDNO |
NAME |
DEPARTMENT |
YEAR |
|
980010 |
박문수 |
인터넷정보 |
1 |
|
950045 |
박정희 |
인터넷상거래 |
2 | |
LIKE는 문자열에서 특정 패턴의 문자열을 검사하기 위해 사용합니다. 이 예에서 사용된 '박%'에서 '%'는 모든문자열을 의미합니다. 그러므로 '박%'의 의미는 '박'으로 시작하는 모든 문자열을 의미합니다. '%'과 유사하게 사용되는 것 중 '_'이 있습니다. '_'의 의미는 임의의 한글자를 나타냅니다. 만일 NAME LIKE '박__'이 사용되었다면 이름(성포함)이 세자인 사람 중 '박'씨성으로 시작되는 가진 이름을 의미합니다. NAME LIKE '%박'의 의미는 이름이 '박'으로 끝나는 이름을 의미합니다. 또, 단순히 NAME LIKE '%박%'라 하면 '박'이 포함된 모든 이름을 의미합니다.
[예제 11] STUDENTS 테이블에 존재하는 학과(DEPARTMENT)를 검색하시오.
|
SELECT DISTINCT DEPARTMENT FROM STUDENTS | |
[결 과]
|
DEPARTMENT |
|
인터넷정보 |
|
웹프로그래밍 |
|
인터넷상거래 | |
일반적으로 SQL에서는 검색 결과로 얻어지는 테이블에 같은 레코드의 중복에 대한 제약이 없습니다. 따라서 검색 결과에 레코드의 중복을 제거하기 위해서는 SELECT 뒤에 DISTINCT를 사용합니다.
[예제 12] STUDENTS 테이블에서 '인터넷정보'과 학생을 검색하여 이름(NAME)과 학년(YEAR)을 1학년부터 출력하시오.
|
SELECT NAME, YEAR FROM STUDENTS
WHERE DEPARTMENT = '인터넷정보' ORDER BY YEAR | |
[결 과]
|
NAME |
YEAR |
|
홍길동 |
1 |
|
박문수 |
1 |
|
이순신 |
2 |
|
김유신 |
2 | |
일반적으로 검색 결과는 시스템이 정하는 순서에 따라 출력됩니다. 따라서 특별히 사용자가 원하는 순서가 있을 때는 필드명을 ORDER BY 뒤에 사용하여 오름차순(ASC)이나 내림차순(DESC)의 순으로 출력할 수 있습니다. 이 예에서는 학년(YEAR)을 기준으로 한 오름차순으로 출력을 하라는 의미입니다. 오름차순의 경우 ASC는 생략할 수 있으나 내림차순의 경우에는 DESC를 ORDER BY절의 필드명 뒤에 반드시 명시해야 합니다.
[예제 13] STUDENTS 테이블에서 '인터넷정보'과 학생을 검색하여 이름(NAME)과 학년(YEAR)을 2학년부터 출력하되 학년이 같은 경우 이름순으로 출력하시오.
|
SELECT NAME, YEAR FROM STUDENTS
WHERE DEPARTMENT = '인터넷정보' ORDER BY YEAR DESC, NAME ASC | |
[결 과]
|
NAME |
YEAR |
|
김유신 |
2 |
|
이순신 |
2 |
|
박문수 |
1 |
|
홍길동 |
1 | |
위의 경우는 2차 정렬을 사용한 예 입니다. 이 예에서는 1차 정렬은 학년(YEAR)의 내림차순으로 2차 정렬은 이름의 오름차순(가나다 순)으로 정렬합니다.
[예제 14] ENROL 테이블에서 과목코드(SUBCODE)가 'S0002'인 과목의 점수를 5점 더하여 그 결과를 출력하시오.
|
SELECT STDNO, SUBCODE, '수정후 점수 = ', SCORE+5 FROM ENROL
WHERE SUBCODE = 'S0002' | |
[결 과]
|
STDNO |
SUBCODE |
수정후 점수 = |
SCORE+5 |
|
950045 |
S0002 |
수정후 점수 = |
60 |
|
990055 |
S0002 |
수정후 점수 = |
60 | |
일반적으로 SELECT 절에는 필드명 뿐 아니라 문자열 상수를 명세할 수 있으며 열이름, 상수, 그리고 산술연산자로 구성된 산술식이 나타날 수 있습니다. 또한 필요한 경우에 괄호를 산술식에 사용할 수 있습니다.
[예제 15] ENROL 테이블에 등록된 학생의 학번과 이름을 학번순으로 출력하시오.
|
SELECT DISTINCT ENROL.STDNO, NAME
FROM ENROL, STUDENTS
WHERE ENROL.STDNO = STUDENTS.STDNO
ORDER BY ENROL.STDNO | |
[결 과]
|
STDNO |
NAME |
|
950045 |
박정희 |
|
960001 |
홍길동 |
|
980010 |
박문수 |
|
990055 |
강감찬 | |
관계형 데이터베이스(RDB) 시스템이 계층형 및 망형의 데이터베이스 시스템에 비해 강력한 점은 둘 이상의 테이블을 참조(join)할 수 있다는 것입니다. 위의 예는 SQL에서 조인이 어떻게 표현되는가를 보여주고 있습니다. 조인 검색은 FROM 절에 관련된 테이블들을 여러 개 사용함으로써 가능해집니다. 그러나 여러 개의 테이블을 조인할 경우 위의 STDNO와 같이 여러 테이블(ENROL, STUDENTS)에서 중복 사용되는 필드명이 존재합니다. 따라서 STDNO 만을 SQL에서 사용할 경우 필드의 모호성이 발생합니다. 이러한 모호성은 ENROL.STDNO, STUDENTS.STDNO과 같이 점으로 구분된 테이블명을 필드명 앞에 사용함으로써 제거할 수 있습니다.
조인 질의문에서 WHERE 절에 나타나는 ENROL.STDNO = STUDENTS.STDNO 는 어떤 조건으로 조인을 하느냐를 표현하는 조인 조건 또는 조인 프레디키트(join predicate)입니다.
[예제 16] STUDENTS, SUBJECT, ENROL 테이블로부터 '인터넷정보'과 학생 중 과목코드 'S0002'를 수강한 학생의 학번, 이름, 과목명 그리고 점수를 출력하시오.
|
SELECT STUDENTS.STDNO, NAME, SUBJECT.SUBNAME, SCORE
FROM STUDENTS, SUBJECT, ENROL
WHERE DEPARTMENT = '인터넷정보'
AND ENROL.SUBCODE = 'S0001'
AND STUDENTS.STDNO = ENROL.STDNO
AND ENROL.SUBCODE = SUBJECT.SUBCODE | |
[결 과]
|
STDNO |
NAME |
SUBNAME |
SCORE |
|
960001 |
홍길동 |
웹프로그래밍 |
100 |
|
980010 |
박문수 |
웹프로그래밍 |
100 | |
위 예제는 세 개의 테이블을 조인하여 결과를 출력한 경우입니다. 이 예에서는 ENROL.STDNO = STUDENTS.STDNO과 ENROL.SUBCODE = SUBJECT.SUBCODE 두 개의 조인 조건이 사용되었습니다.
[예제 17] ENROL 테이블에서 과목코드가 'S0001'인 과목을 수강한 학생 중 자신보다 점수가 높은 학생의 학번을 쌍으로 구성하여 출력하시오.
|
SELECT S1.STDNO, S2.STDNO
FROM ENROL S1, ENROL S2
WHERE S1.SUBCODE = 'S0001' AND S1.SCORE < S2.SCORE | |
[결 과]
|
STDNO |
STDNO |
|
990055 |
960001 |
|
990055 |
980010 | |
이 경우는 동일한 테이블을 조인한 예입니다. 이와 같이 서로 다른 테이블 간의 조인 뿐만 아니라 자기 자신과도 조인을 할 수 있습니다. 이 질의에서 테이블명 뒤에 사용된 S1과 S2를 범위 변수(range variable)라 합니다. 지금까지는 테이블 이름만을 사용하였는데 사실 내부적으로 SQL은 범위 변수를 사용합니다. 다만 범위 변수가 명시적으로 사용되지 않은 경우는 테이블 이름이 자동적으로 범위 변수로 사용되었다고 생각할 수 있습니다.
[예제 18] STUDENTS 테이블에 등록된 학생수를 검색하시오.
|
SELECT COUNT(*) FROM STUDENTS | |
[결 과]
SQL은 유용한 몇몇 함수를 제공함으로써 편리함을 사용자에게 제공하고 있습니다. COUNT(*) 함수는 질의에 의해 검색된 총 레코드의 수를 얻을 수 있습니다.
[예제 19] STUDENTS 테이블에 등록된 학생 중 가장 빠른 학번을 출력하시오.
|
SELECT MIN(STDNO) FROM STUDENTS | |
[결 과]
MIN 함수는 테이블에서 지정된 필드의 값 중 가장 작은 값을 구합니다. 위 형식은 STUDENTS 테이블에서 가장 빠른(작은) 학번(STDNO)을 구합니다. 가장 큰 값을 구하기 위해서는 MAX 함수를 사용합니다.
[예제 20] 과목코드가 'S0001'인 과목의 총점을 출력하시오.
|
SELECT SUM(SCORE) FROM ENROL WHERE SUBCODE='S0001' | |
[결 과]
SUM 함수는 검색된 레코드들에서 지정된 필드의 합계를 구하는 산술함수 입니다. 그러므로 지정된 필드는 그 타입에 관계없이 모든 값이 숫자 상수로 구성되어야 합니다. 만약 숫자가 아닌 값이 저장된 경우 그 값은 0으로 계산됩니다.
[예제 21] 과목코드가 'S0001'을 수강하는 '인터넷정보'과 학생의 평균을 출력하시오.
|
SELECT AVG(SCORE)
FROM ENROL, STUDENTS
WHERE STUDENTS.DEPARTMENT = '인터넷정보'
AND SUBCODE = 'S0001'
AND ENROL.STDNO = STUDENTS.STDNO | |
[결 과]
AVG 함수는 검색된 레코드들에서 지정된 필드의 평균을 구하는 산술함수 입니다. 그러므로 이 함수 역시 SUM 함수와 마찬가지로 지정된 필드의 값이 숫자 상수로 구성되어야 합니다. 위 예는 '웹프로그래밍'과목을 수강하는 학생들 중 '인터넷정보'과 학생들만의 평균을 구하기 위해 두 개의 테이블 ENROL, STUDENTS를 조인하였습니다.
[예제 22] 학생별 총점을 출력하시오.
|
SELECT STDNO, SUM(SCORE) FROM ENROL GROUP BY STDNO | |
[결 과]
|
STDNO |
SUM(SCORE) |
|
950045 |
117 |
|
960001 |
273 |
|
980010 |
261 |
|
990055 |
145 | |
GROUP BY는 논리적으로 FROM절에 있는 테이블을 GROUP BY절 뒤의 필드에 저장된 값 별로 분할하여 그룹을 형성합니다. 그러나 실제로 테이블이 데이터베이스 내에서 물리적으로 분할된다는 의미는 아닙니다.
위의 예에서는 ENROL 테이블에서 학번이 같은 레코드 별로 그룹을 만든다는 의미입니다. 즉 ENROL 테이블을 4개의 그룹(950045, 960001, 980010, 990055)으로 분할하여 각 그룹에 대해 SELECT문을 각각 적용합니다.
[예제 23] 과목별 과목코드, 과목명 및 평균을 출력하시오.
|
SELECT ENROL.SUBCODE, SUBJECT.SUBNAME, AVG(SCORE)
FROM ENROL, SUBJECT
WHERE ENROL.SUBCODE = SUBJECT.SUBCODE
GROUP BY ENROL.SUBCODE | |
[결 과]
|
SUBCODE |
SUBNAME |
AVG(SCORE) |
|
S0001 |
웹프로그래밍 |
96.6667 |
|
S0002 |
홈페이지제작 |
55.0000 |
|
S0003 |
인터넷실무 |
90.0000 |
|
S0004 |
시스템분석및설계 |
84.0000 |
|
S0005 |
윈도우즈프로그래밍 |
69.0000 | |
위 예는 GROUP BY 절을 사용하여 과목별 평균점수를 구한 예입니다. 또한 과목명을 출력하기 위해 ENROL, SUBJECT 두 개의 테이블을 조인하였습니다.
[예제 24] 3 사람 이상 등록한 과목의 기말성적 평균을 출력하시오.
|
SELECT SUBCODE, SUM(SCORE)
FROM ENROL
GROUP BY SUBCODE
HAVING COUNT(*) >= 3 | |
[결 과]
|
SUBCODE |
SUM(SCORE) |
|
S0001 |
290 | |
HAVING은 일반적으로 GROUP BY 절과 함께 사용되며 각 그룹에 대한 검색 조건을 나타냅니다. 만일 GROUP BY절이 생략되면 전체를 하나의 그룹으로 취급합니다.
■ INSERT
|
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name, …)]
VALUES (expression, …), (…),…
or
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name, …)]
SELECT …
or
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=expression, col_name=expression, … | |
지금까지는 데이터베이스 또는 테이블 자체에대한 생성, 삭제 및 변경과 관련된 내용을 다뤘습니다. 이제부터는 SQL에서의 데이터 조작과 관련된 데이터 조작문(DML)에 대해 살펴보겠습니다.
INSERT 문은 테이블에 레코드를 추가할 때 사용하는 SQL 입니다. 첫 번째와 세 번째 형식은 이름이 tbl_name인 테이블에 하나의 행을 추가합니다. 두 번째 형식은 부속 질의문인 SELECT문을 실행한 결과(일반적으로 복수의 열)를 tbl_name 테이블에 추가합니다. 첫 번째의 경우 필드명 col_name이 주어지면 각각의 값 expression은 col_name 순서대로 대응하여 저장됩니다. 만일 열이름을 명세하지 않으면 이 테이블 정의문에 명세된 열의 순서로 저장됩니다.
[예제 25] 다음과 같은 학생을 STUDENTS 테이블에 추가하시오.
(학번:990111, 성명:정몽주, 학과:인터넷정보, 학년:2)
|
INSERT INTO STUDENTS VALUSES('990111', '정몽주', '인터넷정보', 2)
or
INSERT INTO STUDENTS (STDNO, NAME, DEPARTMENT, YEAR)
VALUSES('990111', '정몽주', '인터넷정보', 2)
or
INSERT INTO STUDENTS
SET STDNO = '990111',
NAME = '정몽주',
DEPARTMENT = '인터넷정보',
YEAR = 2 | |
첫 번째 경우는 테이블의 필드명을 생략한 경우 입니다. 이 경우에는 앞서 언급한 것과 같이 VALUSES 뒤의 값 '990111', '정몽주', '인터넷정보', 2가 각각 STUDENTS 테이블의 STDNO, NAME, DEPARTMENT, YEAR필드에 대응되어 저장됩니다. 두 번째 예와 같이 STUDENTS 테이블에 있는 필드명이 모두 기술되면 VALUSES 뒤의 값이 질의에 주어진 필드명과 차례로 대응되어 저장 됩니다. 세 번째 경우는 필드명과 저장될 값을 = 기호를 이용하여 일대일로 대응시킨 경우입니다. 그러나 이 형식은 MySQL에서만 지원하는 비표준 형식입니다.
[예제 26] STUDENTS 테이블의 학생 중 '인터넷정보'과 학생들을 다음과 같은 구조의 INTERNET 테이블에 추가하시오.
[테이블명 : INTERNET]
|
필 드 명 |
필 드 타 입 |
|
STDNO |
CHAR(6) |
|
NAME |
VARCHAR(10) |
|
YEAR |
INT(4) | |
|
INSERT INTO INTERNET (STDNO, NAME, YEAR)
SELECT STDNO, NAME, YEAR
FROM STUDENTS
WHERE DEPARTMENT = '인터넷정보' | |
이 문장은 특정 테이블에서 조건과 일치하는 레코드들을 추출하여 또 다른 테이블에 추가할 때 유용하게 사용할 수 있습니다. 즉 SELECT 문의 결과에서 얻어진 레코드들을 INSERT 문에 명시된 테이블에 추가합니다.
■ UPDATE
|
UPDATE [LOW_PRIORITY] tbl_name
SET col_name=expression, col_name=expression, …
[WHERE where_definition] [LIMIT rows] | |
기존 레코드의 열 값을 변경하기 위해서는 다음과 같은 UPDATE 문을 사용합니다. 위 형식은 이름이 tbl_name인 테이블의 컬럼 col_name을 새로운 값 expression으로 변경합니다. 만약 WHERE 절이 주어지면 조건을 만족하는 모든 레코드들이 SET절에 지시된 대로 변경된다. 그러나 WHERE 절이 생략되면 그 테이블의 모든 레코드들이 변경되므로 사용에 주의를 기울여야 합니다.
[예제 27] 과목코드가 'S0002'인 과목의 점수를 5점씩 더하시오.
|
UPDATE ENROL SET SCORE = SCORE + 5 WHERE SUBCODE = 'S0002' | |
위 예에서 주의를 기울여야 할 부분은 SCORE = SCORE + 5 입니다. 이것의 의미는 SCORE 필드를 현재 값에서 5를 더한 값으로 수정하라는 의미입니다. 앞서 SELECT 문에서 이미 살펴본 바와 같이 필드에 직접 산술식을 사용함으로서 보다 편리하고 단순한 질의를 구성할 수 있습니다.
■ DELETE
|
DELETE [LOW_PRIORITY] FROM tbl_name
[WHERE where_definition] [LIMIT rows] | |
기존 테이블의 행을 삭제할 때는 다음 형식의 DELETE 문을 사용합니다. 위 형식은 이름이 tbl_name인 테이블의 행을 삭제합니다. 만약 WHERE 절이 주어지면 조건을 만족하는 레코드이 모두 삭제됩니다. 그러나 WHERE 절이 생략되면 그 테이블은 투플들이 없는 빈 테이블로 된다. 따라서 UPDATE 문과 마찬가지로 사용에 주의를 기울여야 합니다.
[예제 28] 학번 990001인 학생을 STUDENTS 테이블에서 삭제하시오.
|
DELETE FROM STUDENTS WHERE STDNO = '990001' | |
이 경우 만일 STDNO가 외부 테이블에서도 외래키(foreign key) 사용되었다면 그 테이블에서도 해당 학번에 대한 삭제 연산이 수행되어야 합니다. 예를들어 STUDENTS와 ENROL의 관계에서 학생 레코드의 삭제로 인하여 등록하지도 않은 학생이 과목을 수강하고 있는 경우가 발생하기 때문입니다. 이를 참조 무결성이라 하는데 데이터베이스에서는 이 참조 무결성을 반드시 유지해야 합니다.
[예제 29] 등록(ENROL) 테이블의 모든 레코드를 삭제하시오.
이 삭제문의 실행 결과로서 등록 테이블은 빈 테이블로 계속 데이터베이스에 존재합니다. 다만 레코드 즉 데이터가 없는 빈 테이블로 존재하는 것 입니다. 그러므로 새로운 레코드를 추가할 경우 INSERT 문을 이용해 추가할 수 있습니다. 반면 앞서 언급한 것과 같이 DROP 문에 의해 삭제된 테이블은 테이블 자체가 존재하지 않기 때문에 INSERT 문을 이용해 레코드를 쿠가할 수 없습니다.
10.3 MySQL
지금까지 SQL 및 Database의 기본 개념에 대하여 살펴보았습니다. 그러나 지금까지 살펴본 내용은 Database 이론의 극히 일부에 지나지 않습니다. 따라서 더 많은 정보를 얻기 위해서는 관련 서적을 참조하기 바랍니다. 자 그러면 MySQL의 간단한 사용법부터 살펴보겠습니다. 앞서 2 장에서 MySQL의 설치방법은 다룬적이 있으므로 그 이후의 내용부터 다루도록 하겠습니다.
■ MySQL Server 실행
MySQL은 크게 서버와 클라이언트로 나뉩니다. 서버는 MySQL의 핵심이 되는 부분으로 클라이언트로부터의 요청을 받아 실제 작업을 수행하는 부분이라 할 수 있습니다. 반면 클라이언트는 사용자와 서버 사이에 위치하여 다리의 역할을 해 줍니다. 따라서 PHP와의 연동 및 앞으로 이 절에서 실습할 모든 내용들은 반드시 서버가 실행된 상태에서 확인할 수 있습니다. 서버의 실행은 UNIX 계열의 경우 쉘 상태에서 다음 명령
를 통해 실행할 수 있으며 Windows NT(2000 포함) 계열의 경우 제어판의 서비스 프로그램을 통해 실행할 수 있습니다. 단 유닉스의 경우 해당 safe_mysqld 프로그램이 있는 경로에서 위 명령을 입력한 경우입니다.
■ 관리자 비밀번호 변경
앞서 데이터베이스 시스템에서 관리자의 역할에 대해 설명하였습니다. 그만큼 관리자의 역할은 중요합니다. 따라서 관리자는 자신의 비밀번호가 노출되지 않도록 잘 관리를 해야 합니다. 그러므로 MySQL을 설치 후 관리자 비밀번호를 변경해야 합니다. 우선 비밀번호를 변경하기 위해 굵은 글씨체로 된 명령을 다음과 같은 순서로 입력하기 바랍니다.
|
1. 우선 cd 명령을 이용하여 MySQL이 설치된 경로로 이동 합니다.(Windows 사용자의 경우 명령어
프롬프트를 실행하십시오)
cd /usr/local/mysql (대부분의 UNIX 사용자)
cd c:\mysql (대부분의 Windows 사용자)
2. cd 명령을 이용하여 MySQL 클라이언트 프로그램(mysql)가 있는 경로로 이동하십시오.
cd ./bin (대부분의 UNIX 사용자)
cd .\bin (대부분의 Windows 사용자)
3. 다음 명령을 입력합니다.
./mysql -u root mysql (UNIX 사용자)
.\mysql -u root mysql (대부분의 Windows 사용자)
4. 다음 SQL 문장을 입력합니다. 여기서 newpassword 부분에 원하는 비밀번호를 입력합니다.
UPDATE user SET Password = password('newpassword') WHERE User = 'root' ;
5. Exit 명령을 이용 MySQL 클라이언트를 종료 합니다.
exit ;
6. 다음 명령을 이용 변경된 관리자 비밀번호를 MySQL 서버가 인식하도록 합니다.
mysqladmin -u root reload | |
■ MySQL Client 실행
클라이언트의 실행은 UNIX 계열이나 Windows 계열에 상관없이 유사합니다. UNIX에서의 MySQL 클라이언트의 실행은 다음 형식을 취합니다. 윈도우의 경우는 경로 지정방식의 차이 때문에 './' 대신 '.\' 를 사용합니다. 단 이 경우도 클라이언트 프로그램(mysql)이 위치한 경로를 기준으로 합니다.
|
./mysql [-h hostname] [-u id -p[password]] [database] | |
위 형식은 hostname 컴퓨터에서 실행중인 MySQL 서버로 id라는 사용자가 비밀번호 password로 데이터베이스 database를 사용하기위해 접속하겠다는 의미입니다. 자세한 내용은 다음 사이트를 방문해 보기 바랍니다.
■ PHP와 MySQL의 연동
PHP 에서 MySQL과의 연동을 통한 데이터베이스 프로그램은 PHP에서 제공하는 MySQL 함수를 통해 프로그래밍 할 수 있습니다. 자세한 내용은 부록 A에 다루었으므로 참조하기 바라며 다음장의 예제 프로그래밍에 사용된 테크닉을 응용하기 바랍니다.
