2008.07.19 23:07

SQL Basic

SQL은 무엇인가?
Structured Query Lanaguage의 줄임말 입니다.

SQL문은 데이터 정의문과 데이터 조작문으로 나눌 수 있습니다.
데이터 정의문은 테이블을 생성, 수정, 삭제하는 작업을 수행합니다. 물론 테이블 내에 존재하는 테이터 타입을 정의하는 작업을 포함합니다. Create, Alter, Drop
데이터 조작문은 생성된 테이블 내에 존재하는 데이터들을 저장, 수정, 삭제, 검색하는 작업을 의미
합니다. Insert, Update, Delete, Select 입니다.

데이터 타입은 아래와 같습니다.
INT : 4bytes 정수
CHAR(M) : 문자의 수가 M개인 문자열
VARCHAR(M) : 문자의 수가 최대 M개인 문자열
TEXT : 문자의 수가 최대 65535개인 문자열


테이블 생성하는 방법은 다음과 같습니다.
 //[]는 생략가능, {}* 반복가능
Create Table Table_name (
    column_name Date_type [NOT NULL]
    {, column_name Date_type [NOT NULL]}*
    [, PRIMARY KEY(column_name)]
)

그럼 간단한 예제를 보겠습니다. (Default Table)
Create Table Table_User (
    name CHAR(8),
    id VARCHAR(10) NOT NULL,
    email VARCHAR(40),
    sex CHAR(1),
    PRIMARY KEY(id)


생성된 테이블 정보를 수정하는 방법을 보겠습니다.
ALTER Table Table_name
    ADD [COLUMN] column_name column_type            // 필드 추가
    CHANGE [COLUMN] Old_column_name New_column_name column_type   
                                                                            // 필드속성 변경
    DROP [COLUMN] column_name                          // 필드 삭제

위에서 생성한 Table_User 테이블에 addr 필드를 추가해 봅니다.

ALTER Table Table_User ADD COLUMN addr VARCHAR(100)
ALTER Table Table_User ADD addr VARCHAR(100)

Add의 경우 주위해야할 점이 있습니다.
추가된 값은 모두 NULL입니다. 당연히 NOT NULL 속성과 함께 사용될 수 없겠죠?

그럼 다시 addr을 address varchar(255)로 수정해 보겠습니다.
ALTER Table Table_User CHANGE COLUMN addr address VARCHAR(255)

추가하고 수정했던 address 필드를 삭제해 보겠습니다.
ALTER Table Table_User Drop COLUMN address

DB에서 테이블을 삭제하는 방법을 보겠습니다.
DROP Table Table_name

그럼 이번에는 Table_User 테이블을 삭제해 보겠습니다.
DROP Table Table_User

여기까지가 데이터 정의문에 관한 이야기 였습니다.

그럼 지금부터는 SQL Injection 공격에서도 많이 응용되는 데이터 조작문에 대해서 알아보겠습니다.

가장 기본이 되는 데이터 검색입니다.
Select [Distinct] column_name {, column_name}*   
From table_name
[WHERE 검색조건]
[Order by column_name [ASC or DESC] {, column_name [ASC or DESC]}*]
[Group by column_name {, column name}*]
[Having 검색조건]

저렇게 적어 놓으니 상당히 복잡해 보이는군요..
[] 부분을 빼고 봅시다.

Select column_name from table_name;

간단한 예제를 보겠습니다.
Select name, id, address, email, sex from Table_User;
Select * from Table_User;
(*는 모든 컬럼을 나타내는 특수문자 입니다.)

그럼 Distinct는 어디에 쓰는 녀석일까요?
바로 결과 레코드의 중복을 제거해 줍니다.

Select sex From Table_User;
Select DISTINCT sex From Table_User;

첫번째의 경우 F, M 등의 문자가 가입된 회원수 만큼 반복되어 나타날 것입니다.
하지만 두번째의 경우 F, M 두가지만 나타나게 됩니다.

만약 회원정보 Table에 회원등급과 관련된 컬럼이 존재할 경우 Distinct를 사용하면 쉽게 권한 레벨들에 대한 정보를 획득할 수 있을 것입니다.

다음은 조건검색에 대해서 알아보겠습니다.
Select column From table where 조건

// id가 'admin'인 사람의 모든 정보를 검색
ex) Select * from Table_User where id='admin';

다음은 검색 결과를 정렬하는 방법을 알아보겠습니다.
Select column From table where 조건 Order BY Column [ASC or DESC]

// 남자 사용자들을 id 내림순서로 정렬
ex) Select * Form Table_User where sex='m' Order BY id DESC;

조금 지루하실 텐데 수학함수처럼 SQL에는 그룹함수라는 개념이 있습니다.
count(Column)    // 조건에 만족하는 레코드의 개수
sum(Column)       // 해당 컬럼의 합
min(Column)       // 해당 컬럼의 최소값
max(Column  )    // 해당 컬럼의 최대값
avg(Column)      // 해당 컬럼의 평균값

그룹관련해서 Group By를 이용한 검색도 가능합니다.
Table_User 테이블에 age, level이라는 컬럼이 있고, level은 관리자=admin, 정회원=Normal, 준회원=ready라는 값을 가지고 있다고 가정해 봅시다.
이때 각 그룹별 최연소, 평균나이, 최연장자의 정보를 확인하기 위해서는 다음과 같이 하면 됩니다.
select level, min(age), avg(age), max(age) From Table_User Group By level

만약 위와 같은 조건에 성별을 추가하면 어떻게 될까요? 이미 배웠던  where를 사용하면 됩니다.
또한 having을 사용해도 됩니다.
select level, min(age), avg(age), max(age) From Table_User Group By level where sex='F';
select level, min(age), avg(age), max(age) From Table_User Group By level having sex='F'

또한 BETWEEN이라는 연산자도 있습니다. 단어 뜻대로 사이값을 구하는 것입니다.
만약 사이트 관리자가 20대 여성들에 대한 정보만을 추출하고자 한다면??
Select * From Table_User Where Sex='F' and age BETWEEN 20 and 30
Select * From Table_User Where Sex='F' and age>=20 and age<=30

IN과 NOT IN이라는 연산자도 있습니다.
Select * From Table_User where SEX IN ('F')
Select * From Table_User where SEX NOT IN ('M')

LIKE라는 막강한 연산자도 있습니다.
// 이름에 '몽' 자가 들어간 사용자들의 이름 확인
Select name From Table_User Where name LIKE '%몽%'

이 기능은 주로 주소 검색이나, 사용자 이름, 글 제목, 글 내용을 검색할 때 사용됩니다.

이제 select의 마지막인 NULL 입니다.
말 그대로 비어있는 컬럼을 찾는 것입니다.
// 회원가입시 이메일 주소를 입력하지 않은 사람 확인
select * from table_user where email IS NULL;

조금 길게 데이터 검색에 대해서 알아보았습니다.
그럼 다음은 데이터 삽입에 대해서 알아보겠습니다.
INSERT
INTO Table_name [ (column {[, column]}*)]
VALUES (column's value)

여기에서 column을 생략하기 위해서는 반드시 선언된 테이블 정보의 순서와 형식이 일치해야 합니다.

위에서 선언한 Default Table을 가지고 특정 값을 삽입해 보겠습니다.
INSERT INTO Table_User (name, id, email, sex) VALUES ('애화몽','ahm','ahm@test.com','M')
이와 같이 모든 컬럼에 해당되는 값을 입력할 경우에는 다음과 같이 표현할 수도 있습니다.
INSERT INTO Table_User VALUES ('애화몽','ahm','ahm@test.com','M')

그럼 다음은 데이터 수정에 대해서 알아보겠습니다.
UPDATE Table_Name
SET Column=Column's Value {,Column=Column's Value}*
[Where 조건]

// 애화몽의 이름을 '몽'으로 바꾼다고 가정하면
UPDATE Table_User SET Name='몽' WHERE name='애화몽'

Update 명령어는 특히 조심해서 사용해야 한다. where를 작성하기 전에 실수로라도 엔터키를 입력해 버리면 큰일이 납니다.

본인이 관리하는 웹 사이트 회원 중 한명의 비밀번호를 '0000'으로 초기화 하는 일을 해야 한다고 가정해 봅시다.
Update SET User passwd=password('0000')
실수로 where 절을 입력하지 않고 위와 같은 명령어를 실행하면 모든 사용자 비밀번호가 '0000'으로 바뀐니다. 이런 실수는 절대로 해서는 안됩니다.

이와 마찬가지로 주의해야할 명령어가 바로 데이터를 삭제하는 Delete입니다.
Delete
From Table_name
Where 검색조건

//아이디가 ahm인 사용자 정보 삭제
Delete From Table_User WHERE id='ahm'

// 모든 사용자 정보 삭제
Delete From Table_User

조금 길게 SQL 기본 명령어에 대해서 알아보았습니다.
빠진 부분들은 스스로 공부하길 바라면서, 다음에는 SQL Injection Tools의 Query에대해서 작성하고자 합니다.
Trackback 0 Comment 1