| Web Programming >> PHP Programming
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| [목차] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
제6장 PHP와 웹 데이터베이스의 연동
4. MySQL에서 SQL사용하기 가. 새로운 테이블의 생성(CREATE TABLE) 데이터베이스 내에 새로운 테이블을 생성하기 위한 명령문은 다음과 같다.
여기서 []안의 부분은 생략할 수 있고 {}* 안의 부분은 반복될 수 있는 선택 사항임을 가리킨다. 데이터베이스는 여러 개의 테이블로 구성되며 각 테이블은 다시 레코드와 레코드로 구성하는 필드로 나누어진다. 모든 데이터는 이 테이블에 저장되는데 테이블 내 위치에 따라 레코드는 '행'(row), 필드는 '열'(column)이라고 말로도 표현된다. CREATE TABLE문은 데이터를 저장하기 위한 빈 테이블을 생성한다. 테이블 내에서 저장될 데이터를 가리키는 필드명과 해당 필드의 데이터 타입 등을 지정해주어야 한다. SQL에서 사용할 수 있는 데이터 타입은 대부분 동일하지만 어떤 데이터베이스를 사용하느냐에 따라서 조금씩 차이가 있다. MySQL의 경우 이 데이터베이스에서 지원하는 대표적인 데이터 타입으로는 다음과 같은 것들이 있다.
PRIMARY KEY(필드명)는 CREATE TABLE문에서 정의한 필드 중 ( )안에 지정한 필드를 테이블의 기본키로 설정한다. 기본키는 테이블 내에 저장된 레코드를 다른 레코드와 구별시켜주는 역할을 하는 식별자이다. 기본키로 설정된 필드에서 동일한 값이 두 개 이상 존재할 수 없다. 이름과 전자우편주소, 성별 등은 테이블 내에 동일한 값을 가진 레코드가 여러 개 있을 수 있다. 그러나 똑같은 이름에 똑같은 전자우편 주소, 똑같은 성별을 갖는다 해도 모든 레코드는 ID 필드 값이 갖는 유일성에 의해 다른 레코드와 구별된다. 마치 한국의 모든 사람이 서로 중복되지 않는 고유의 주민등록번호를 통해서 유일하게 식별될 수 있는 것처럼 이러한 식별자가 테이블의 기본키가 되는 것이다. 해당 필드명에 "NOT NULL"을 선언하게 되면 이렇게 선언된 필드는 NULL 값을 가질 수 없다. 예를 들면, 기본키는 테이블 내의 레코드를 유일하게 식별할 수 있는 역할을 하므로 기본키로 설정된 필드는 반드시 NOT NULL이어야 한다. 또한 게시판이나 회원 가입 양식에서 사용자가 반드시 입력하고 넘어가야 하는 부분이 있다면 이들 데이터에 대한 테이블을 생성할 때에도 해당 필드를 NOT NULL로 선언해야 한다. 이름(name)과 아이디(id), 전자우편(email), 성별(sex)을 필드로 갖는 userdb 테이블을 CREATE TABLE문으로 정의하면 다음과 같다.
여기서 필드 id는 기본키로 설정하였으므로 NOT NULL로 정의하였다. 나. 생성된 테이블의 변경(ALTER TABLE) CREATE TABLE문으로 생성된 테이블은 ALTER TABLE문으로 변경할 수 있다. ALTER TABLE문을 이용하면 기존의
테이블에 미쳐 정의하지 못한 필드를 추가시킬 수 있고 이미 존재하는 필드의 테이터타입을 바꿀 수도 있다. 또한 특정 필드를 테이블에서 삭제할
수도 있다.
위에서 생성한 userdb 테이블에 주소 필드 addr을 추가하려면 다음과 같이 ALTER TABLE문을 사용해야 한다. 이때 필드를 가리키는 COLUMN은 생략해도 된다.
여기서 ADD [COLUMN]뒤에서 CREATE TABLE문으로 필드를 정의했던 것과 동일한 방식으로 적어주면 된다. 기존의 필드에 이미 데이터가 삽입되어 있는 상태에서 ALTER TABLE문으로 새로운 필드를 추가할 경우 새로 정의된 필드의 값은 초기에 모두 NULL 값을 갖는다. 따라서 ALTER TABLE문으로 새로 추가되는 필드는 NOT NULL이 될 수 없다. 다음의 명령은 새로 정의된 필드 addr의 필드명을 address로 바꾸고 그의 데이터타입을 VARCHAR(100)에서 VARCHAR(255)로 변경한다.
다음의 명령은 필드 address를 테이블 userdb에서 삭제한다.
다. 데이터 추가(INSERT) 이제
mysql 클라이언트로 제2절에서 설명한 SQL 문을 직접 실행하면서 userdb 테이블에 데이터를 삽입하고, 수정, 삭제, 검색하는 조작
방법을 확인해 보자.
데이터가 모두 입력되었는지 SELECT 문으로 확인해 보자.
6개의 레코드(행)가 모두 정상적으로 입력된 것을 확인할 수 있다. 라. 데이터 검색(SELECT) 데이터베이스의 테이블에 저장되어 있는 데이터를 검색하기 위한 기본 형식은 다음과 같다.
위의 형식으로부터 다양한 데이터 검색 방법의 예를 보기 위해 테이블 userdb에 데이터가 저장되어 있다고 하자.
[예제5.1] userdb 테이블 1. 일반적인 검색 테이블에서 사용자 레벨이 B인 사용자의 이름과 아이디는 다음과 같은 명령문으로 검색할 수 있다.
위의 명령문은 테이블 userdb에서 사용자 레벨이 'B'인 사용자를 찾아서 해당 사용자의 이름과 아이디를 출력한다, 결과는 다음과 같다.
테이블 userdb에 등록되어 있는 모든 데이터를 검색하려면 모든 필드명을 나열하는 대신 다음과 같이 '*'를 사용하면 된다.
위의 명령문은 userdb 테이블의 모든 필드의 값을 검색하여 출력하는데 이때 결과는 userdb 테이블을 생성할 때 정의한 필드의 순서대로 출력된다. 즉 위의 명령문은 다음의 명령문과 동일하다.
위 명령문의 검색 결과 userdb 테이블에 저장되어 있는 모든 데이터가 검색된다.
2. 결과 레코드의 중복 제거 사용자 레벨의 종류가 몇 단계나 있는지 알아보기 위해 다음과 같은 명령문을 입력한다.
위 명령문의 결과는 다음과 같이 출력된다.
userdb 테이블을 보면 사용자 레벨은 'A', 'B', 'C', 'D' 네 개가 존재한다. 그러나 위 검색 명령문의 결과를 보면 모든 사용자의 레벨이 그대로 출력되어버려 데이터가 많아질 경우에는 사용자 레벨의 종류가 몇 개인지 알기 어렵게 된다. 이럴 때 중복 값을 제거하면 보기가 훨씬 좋아진다.
DISTINCT를 사용한 위의 명령문의 결과는 다음과 같다.
즉, 검색 결과 DISTINCT 옵션을 준 level 필드의 값이 같은 값을 갖는 레코드가 있을 경우 해당 레코드는 하나만 출력하므로 레코드 값의 종류가 몇 개인지를 알 수 있다. 3.조건 검색 WHERE 구문으로 검색 조건을 주어 원하는 레코드를 검색할 수 있다. 다음의 명령문은 마일리지가 3000 이상인 남자의 이름과 아이디, 이메일 주소를 출력한다.
검색 결과는 다음과 같다.
검색 조건을 표시하는 WHERE 절에는 비교 연산자 >, >=, <, <=, = 와 AND, OR, NOT과 같은 연산자를 사용할 수 있다. 4.검색 결과의 정렬 특정 필드의 값을 오름차순이나 내림차순으로 정렬하여 출력하면 ORDER BY문을 사용한다. 다음의 명령문은 마일리지가 4000 이상인 사람과 이름, 아이디, 마일리지를 마일리지가 높은 순서대로 출력한다.
검색 결과는 다음과 같다.
ORDER BY문은 검색된 레코드에서 지정한 필드의 값을 오름차순이나 내림차순으로 정렬하여 출력하고자 할 때 사용한다. ASC는 오름차순이고 DESC는 내림차순이다. ASC나 DESC를 정하지 않고 ORDER BY문을 사용할 경우 기본 값인 ASC로 설정된다. 5.검색 결과에 대한 산술 계산 및 문자열 처리 SELECT절에서 필드명뿐 아니라 문자열과 산술식이 올 수 있다.
위 명령문의 출력 결과는 다음과 같다.
위의 명령문에서 보는 바와 같이 SELECT절에는 name, milage와 같은 필드명뿐 아니라 "님의 마일리지는", "점입니다"와 같은 문자열도 올 수 있다. 위 명령문의 결과 마일리지가 4000 이상인 레코드에 한해 SELECT절의 문자열을 포함한 결과가 출력되며 ORDER BY에서는 ASC나 DESC가 생략되었으므로 마일리지 값의 오름차순으로 정력이 된다. 다음의 명령문은 id가 'soony'인 레코드를 검색하여 해당 레코드의 마일리지를 300점 올린 값을 출력한다.
검색 결과는 다음과 같다.
명령문의 실행 결과 id가 soony인 사용자 강연순의 milage가 2600에서 300점 올라간 2900으로 출력된다. 6. 그룹 함수(group function)를 이용한 검색 SQL에서 지원하는 그룹 함수를 이용하면 해당 필드의 평균값이나 값의 개수, 최대, 최소값등을 알 수 있다. 이러한 그룹 함수에는 다음과 같은 함수들이 있다.
다음의 명령문은 userdb 테이블에서 여자인 사용자가 몇 명되는지를 검색한다.
검색 결과는 다음과 같다.
다음의 명령문은 userdb 테이블에서 여자 사용자의 마일리지 값의 평균을 출력한다.
출력 결과는 다음과 같다.
[예제5.1]의 여섯 명 중 여자인 네 명의 milage 필드값의 평균은 3400임을 알 수 있다. 7. GROUP BY를 이용한 검색 각 레벨당 사용자의 마일리지 최대 및 최소값, 그리고 평균값은 다음과 같은 명령문으로 검색할 수 있다.
이 명령문의 검색 결과는 다음과 같이 각 레벨에 포함되는 모든 레코드에 대하여 마일리지 필드의 최소값과 최대값, 평균값을 각각 출력한다.
이와 같이 GROUP BY 는 GROUP BY 뒤에 지정한 필드가 가진 값의 종류별로 SELECT 문에 정의한 검색 조건을 적용하여 그 결과를 출력한다. 위에서 GROUP BY로 지정한 필드 level은 userdb 테이블에서 보면 'A', 'B', 'C'의 세 가지 값을 갖고 'A'에는 '길수현(4200)'과 '박현영(4000)'의 레코드가, 'B에는 '이완철(3500)'과 '장현이(3300)', 'C'에는 '김영미(2800)'와 '강현순(2600)'의 레코드가 있다. 따라서 GROUP BY level문 각 레벨에 속하는 레코드만을 대상으로 마일리지의 최대, 최소, 평균값을 출력하므로 위와 같은 결과를 얻게 된다. 만일 남자와 여자의 마일리지 최대, 최소, 평균값을 구한다면 다음과 같은 GROUP BY문을 사용하면 된다.
검색 결과는 다음과 같다.
8. HAVING을 이용한 검색 HAVING은 GROUP BY로 지정한 필드에 대한 검색 조건을 지정한다. 예를 들어, userdb 테이블에서 여자만의 마일리지 최대, 최소, 평균값을 검색하려면 다음과 같은 HAVING문을 사용한다.
검색 결과는 다음과 같다.
위 명령문의 실행 결과 GROUP BY문에 의해 전체 레코드는 'M'과 'F' 값을 갖는 두 개의 그룹으로 나누어지며 HAVING절 이하의 검색 조건에 의해서 성별이 여자인 레코드만을 대상으로 마일리지의 최대 및 최소, 평균값이 출력된다. 9. BETWEEN 연산자를 이용한 검색 BETWEEN 연산자는 AND와 함께 사용되어 정해진 수치 영역 안에 포함되는 값을 갖는 데이터를 검색할 수 있다. 다음의 명령문은 마일리지가 3000 이상 4000 이하인 여자의 이름과 이메일, 마일리지, 성별을 출력한다.
여기서 'milage BETWEEN 3000 AND 4000' 은 BETWEEN 앞에 지정한 milage 필드 값이 3000과 40000 사이에 해당하는 모든 레코드를 검색한다. 앞에 WHERE문에서 추가로 성별이 여자인 경우로 제한하였으므로 다음과 같은 결과가 출력된다.
즉, 위의 BETWEEN을 이용한 명령문은 다음의 명령문과 동일함.
다음의 명령문은 userdb 테이블에서 레벨이 'A' 나 'C'인 레코드를 검색한다.
검색 결과는 다음과 같다.
위와 같이 IN 다음의 괄호 안에 검색하고자 하는 필드(level)의 값을 지정함으로써 그 값을 갖는 레코드만을 선별하여 검색할 수 있다. NOT을 이용하여 해당 조건과 일치하지 않는 레코드만을 검색할 수 도 있다.
위의 명령문은 마일리지가 3300,3500,3700이 아닌 레코드만을 검색하며 그 결과는 다음과 같다.
10. LIKE를 사용한 검색 LIKE를 사용하면 필드값의 문자열을 검색할 수 있다. 다음의 명령문은 name 필드값으로 '현' 이라는 문자를 포함하는 모든 레코드를 검색한다.
검색 결과 다음과 같이 이름에 '현이라는 문자가 들어간 세 개의 레코드가 검색된다.
LIKE 다음에는 필드값에서 검색할 문자열을 지정한다. 위의 명령문에서 '%현%'은 LIKE 앞에 지정한 필드(name)의 값중에 문자 '현'을 포함하는 레코드를 검색하라는 의미이다. 그에 반해 '%현'과 같이 뒤의 %가 생략되면 name 필드의 문자열이 '현'으로 끝나는 레코드만을 검색한다. 따라서 '%현'으로 검색하면 여섯 개의 데이터 중 '길수현' 만이 검색된다. 반대로 '현%'은 뒷 문자열은 어떤 글자이든 상관없이 첫 글자가 '현'으로 시작하는 레코드만을 검색한다. 다음의 명령문은 userdb 테이블의 id 필드값이 'm'으로 시작하는 레코드를 검색한다.
검색 결과 아이디가 'm'으로 시작하는 다음 두 개의 레코드가 검색된다.
11. NULL 값을 갖는 데이터 검색 [예제5.1]의 userdb 테이블에 다음과 같이 김영배라는 사용자를 한 명 더 추가했다고 가정하자.
위의 테이블에서와 같이 이메일 주소와 같은 특정 필드값이 NULL인 레코드를 검색하려면 다음과 같은 명령문을 사용하면 된다.
검색 결과는 다음과 같다.
위의 명령문에서 보듯이 이메일 주소가 없는 사용자, 즉 email 필드의 값이 NULL인 레코드를 검색하려면 WHERE 절에서 해당 필드 다음에 IS NULL문을 사용하면 된다. 만일 NULL 값이 아닌 레코드를 검색하려면 다음과 같이 NOT 연산자를 사용할 수 있다.
그러면 '김영배'를 제외한 나머지 여섯 명의 레코드가 검색된다. 마. 데이터의 수정(UPDATE) INSERT 문으로 삽입한 데이터는 UPDATE 문으로 수정할 수 있으며 그 형식은 다음과 같다.
앞에서 userdb 테이블에 id 와 name 필드값만을 삽입한 "김현아"의 성별9sex)을 여자(F)로 수정하려면 다음과 같은 명령문을 쓴다.
이는 name 필드의 값이 "김현아"인 레코드를 찾아서 해당 레코드의 sex 필드의 값을 "F"로 수정하라는 의미이다. 만일 다음과 같이 WHERE 절로 검색 조건을 명시하지 않은 경우에는 해당 테이블(userdb)내의 모든 레코드의 필드(sex)값이 설정한 값("F")으로 수정되므로 유의해야 한다.
현재 테이블에 저장되어 있는 모든 사용자의 마일리지를 200점 추가하려면 다음과 같이 해당 필드값에 대한 산술식을 적용하면 된다.
위 명령의 실행 결과 userdb 테이블의 마일리지(milage) 필드가 NULL값이 아닌 모든 레코드에 대해 마일리지 필드값이 기존의 값에 300점이 추가된 값으로 수정된다. 바. 데이터의 삭제(DELETE) 테이블
내에 저장되어 있는 데이터는 DELETE 문으로 삭제할 수 있으며 그 기본 형식은 다음과 같다.
DELETE 문은 WHERE 절에서 검색 조건을 지정하였을 경우 지정한 검색 조건과 일치하는 모든 레코드를 삭제한다. 다음의 명령문은 userdb 테이블에서 아이디(id)가 "king94"인 레코드를 삭제한다.
만일 다음과 같이 검색 조건을 지정하지 않으면 userdb 테이블 내의 모든 레코드가 삭제되므로 주의해야 한다.
사. 생성된 테이블의 삭제(DROP TABLE) 데이터베이스에서 테이블을 삭제하기 위한 명령문은 DROP TABLE문이며 그 형식은 다음과 같다.
앞에서 CREATE TABLE문으로 생성한 테이블 userdb는 다음과 같이 삭제할 수 있다.
DROP TABLE문은 테이블 내에 저장되어 있던 데이터도 함께 삭제하며 일단 삭제된 테이블은 더 이상 접근할 수 없다. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| [목차] |
PHP 프로그래밍
