<SQL 데이타 정의문>
0.데이타 타입
INTEGER : 4바이트 정수
SMALLINT : 2바이트 정수
FLOAT(n) : 부동소수
DECIMAL(i, j) : 고정소수
CHAR(n) : 문자의 수가 최대 n인 스트링;
검색시 속도가 약간 빠름
VARCHAR(n) : 문자의 수가 최대 n인 스트링;
저장시 문자열의 크기만큼 처리;
검색시 속도가 약간 느림
DATE : 일자(YYYYMMDD)
TIME : 시간(HHMMSS)
1.기본 테이블의 생성
CREATE TABLE 기본테이블이름
(열이름 데이타타입 [NOT NULL]
{ , 열이름 데이타타입 [NOT NULL] } * );
2.기본 테이블의 변경
ALTER TABLE 테이블이름
ADD 열이름 데이타타입;
3. 인덱스의 생성
CREAT [UNIZUE] INDEX 인덱스이름
ON 기본테이블 (열이름 [ASC | DESC]
{ , 열이름 [ASC | DESC] } * )
[CLUSTER];
! CLUSTER : 물리적으로 가까운 곳에 저장됨.
! 열의 이름 순서는 순서의 순위.
4. 기본 테이블의 제거
DROP TABLE 기본테이블이름 ;
5. 인덱스의 제거
DROP INDEX 인덱스이름 ;
<SQL 데이타 조작문>
1. 데이타 검색
SELECT [ALL | DISTINCT] 열리스트
FROM 테이블이름 [범위변수] [{, 테이블이름 [범위변수]}*]
[WHERE 조건]
[GROUP BY 열리스트
[HAVING 조건]]
[ORDER BY 열리스트 [ASC | DESC]] ;
! 열 전부를 검색 : *
! DISTINCT : 중복제거
! 조건식 사용 비교연산자 : = >, <, AND, OR, NOT
! 범위변수가 없으면 테이블 이름을 범위 변수로 사용
! GROUP BY : 열이름별로 분할 (각 그룹에 대해 SELECT문을 적용)
! HAVING : GROUP에 대한 조건 명세.
예 )
SELECT CNO, AVG(FINAL)
FROM ENROL
GROUP BY CNO
HAVING COUNT(*)> 3;
- 집단함수를 이용한 검색
SELLCT COUNT | SUM | AVG | MAX | MIN ([DISTINCT] 열이름)
FROM .....
- SELFJOIN
예)
SELECT S1.SNO, S2.SNO
FROM STUDENT S1, STUDENT S2
WHERE S1.DEPT = S2.DEPT
AND S1.SNO < S2.SNO;
- SUBQUERY
예)
SELECT SNAME
FROM STUDENT
WHERE SNO IN
(SELECT SNO
FROM ENROL
WHERE CNO='C413');
=
SELECT STUDENT.SNAME
FROM STUDENT, ENROL
WHERE STUDENT.SNO=ENROL.SNO
AND ENROL.CNO='C413';
! IN 이외에 =, > 등의 조건식을 사용할 수 있다.
- LIKE
예)
SELECT CNO, CNAME
FROM COURSE
WHERE CNO LIKE 'C%';
! C% : C로 시작하는 문자열
! %C__: 끝에서 3번째가 C인 문자열
- NULL
예)
SELECT SNO, SNAME
FROM STUDENT
WHERE DEPT IS [NOT] NULL;
! 항상 IS 를 사용해서 검색
- EXISTS
예)
SELECT SNAME
FROM STUDENT
WHERE [NOT] EXISTS
(SELECT *
FROM ENROL
WHERE SNO=STUDENT.SNO
AND GRADE='A'
AND CNO='C413');
- UNION
예)
SELECT SNO
FROM STUDENT
WHERE YEAR = 3
UNION
SELECT SNO
FROM ENROL
WHERE CNO='C324';
! 중복된 투플은 제거됨
2. 데이타의 갱신
UPDATE 테이블이름
SET 열이름 = 산술식 {, 열이름 = 산술식}*
[WHERE 조건];
3. 데이타의 삽입
INSERT INTO 테이블 [(열이름 {[, 열이름]}*]
VALUES (열값리스트) | SELECT문 ;
4. 데이타의 삭제
DELETE FROM 테이블이름
[WHERE 조건문 ];
<SQL VIEW>
1. VIEW의 생성
CREATE VIEW 뷰이름 [(열이름리스트)]
AS SELECT문
[WITH CHECK OPTION];
! WITH CHECK OPTION : 이 VIEW의 갱신이나 삽입연산은 VIEW정의 조건을 확인
! ALTER문의 사용할 수 없다.
예)
CREATE VIEW DEPTISIZE(DEPT, STNUM)
AS SELECT DEPT, COUNT(*)
FROM STUEDNT
CROUP BY DEPT;
2. VIEW의 제거
DROP VIEW 뷰이름;
! VIEW가 유도된 기본 테이블이 제거되면 그 기본 테이블에 연관된
모든 VIEW가 자동적으로 제거된다.
! VIEW가 제거되면 그 VIEW 위에 정의된 VIEW도 자동적으로 제거된다.
3. VIEW의 조작 연산
! INSERT, DELETE, UPDATE문을 VIEW에 사용하기 위해서는
TABLE의 행과 열의 부분집합으로만 정의되어야 한다.
이것이 ASP 에서 DB 를 컨트롤 하기위한 SQL Query 기본 사용법이다.
' [1] 웹 페이지에서 DB를 컨트롤하기위한 기본 과정 정리
' 1. DB 연결 문자열 만들기(4개 중에 하나만을 선택해야 함)
' 1-1 ODBC 없이 SQL Server 에 연결문자열을 이용한 직접 연결
YourServer = "AosSQL"
YourDB = "aosSQLDB"
YourUID = "adminaos"
YourPWD = "aosadmin"
strConnect = "Driver={SQL Server}; Server=" & YourServer & "; Database=" & YourDB & "; UID=" & YourUID & "; PWD=" & YourPWD & ""
' 1-2 ODBC 의 DSN 을 이용한 SQL Server 연결
YourDSN = "ageofstone"
YourUID = "adminaos"
YourPWD = "aosadmin"
strConnect = "DSN=" & YourDSN & ";Uid=" & YourUID & ";pwd=" & YourPWD & ";"
' 혹은
strConnect = "DSN=ageofstone;uid=adminaos;pwd=aosadmin;"
' 1-3 ODBC 없이 Access MDB 에 연결문자열을 이용한 직접 연결
YourDBpath = "d:\AOS-Working-DB(mdb)\ageofstone\"
YourDBname = "Ageofstone.mdb"
YourDB = YourDBpath & YourDBname
strConnect = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & YourDB
' 1-4 ODBC 의 DSN 을 이용한 Access MDB 연결
YourDSN = "ageofstone"
strConnect = "DSN=" & YourDSN
' 2. 위에서 지적한 연결로 DB 연결
Set Dbcon = Server.CreateObject("ADODB.Connection")
Dbcon.Open(strConnect)
' 3. Table 이름 지정 및 SQL 스크립트 만들기
strTable = "AGEOFSTONE_BBS"
strSQL = _
" SELECT " &_
" bbs_num, bbs_subj, bbs_name" &_
" FROM " &_
strTable &_
" WHERE " &_
" bbs_index = " & "'" & bbs_index & "'" &_ ' bbs_index 는 다중게시판을 위한 인덱스
" ORDER BY " &_
" bbs_ref DESC, bbs_step, bbs_level"
' 4. Table 열기
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL,Dbcon,adOpenKeyset
' 5. 레코드의 존재유무 확인
'레코드가 하나도 없다면
if rs.EOF or rs.BOF then
NoData = True ' 이 부분엔 입력된 레코드가 하나도 없을 때의 처리를...
Else '레코드가 있다면
NoData = False ' 이 부분엔 입력된 레코드가 있을 때의 출력하는 코딩을...
bbs_num = rs("bbs_num")
bbs_subj = rs("bbs_subj")
bbs_name = rs("bbs_name")
end if
' 6. 레코드셋 종료와 DB 연결 끊기
rs.Close
DbCon.Close
set rs = nothing
set DbCon = nothing
' 이것이 ASP 에서 DB 를 컨트롤 하기위한 기본과정이다.
' [2] DB의 레코드 추가하기(데이터 입력)
' 1. 입력할 데이터를 저장
bbs_num = request("bbs_num")
bbs_name = request("bbs_name")
bbs_subj = request("bbs_subj")
bbs_index = request("bbs_index")
' 2. DB 연결
strConnect = "DB 연결 문자열"
Set DbCon = Server.CreateObject("ADODB.Connection")
DbCon.Open strConnect
' 3. 새 글 번호 생성
strTable = "테이블 이름"
' 새 글의 번호 생성
strSQL = _
" SELECT " &_
" MAX(bbs_num) " &_
" FROM " &_
strTable &_
" WHERE " &_
" bbs_index = " & "'" & bbs_index & "'"
' 위에서 작성한 스크립트로 테이블을 연다.
Set Rs = Server.CreateObject("ADODB.Recordset")
Rs.Open strSQL,DbCon,adOpenKeyset
if IsNull(Rs(0)) then ' 아무것도 없는 새 테이블이면
MaxNo = 1
else ' 기존 레코드가 있다면
MaxNO = Rs(0) + 1
End If
' 새 글의 번호 생성 끝
Rs.close
Set Rs = nothing
strSQL = _
" INSERT INTO " &_
strTable &_
" (bbs_num, bbs_subj, bbs_name, bbs_index)" &_
" VALUES " &_
" (" &_
MaxNO & ", " &_
"'" & bbs_subj & "'" & ", " &_
"'" & bbs_name & "'" & ", " &_
"'" & bbs_index & "'" &_
")"
DbCon.Execute(strSQL) ' 위에서 작성한 레코드입력 스크립트를 실행한다.
' 6. DB 연결 끊기
DbCon.Close ' 모든 작업이 끝나면
Set DbCon = nothing ' 객체를 환원한다.
' 이것이 ASP 에서 DB 의 레코드를 추가하는(데이터를 입력하는) 기본이다.
' [3] DB의 레코드 수정하기(데이터 수정)
' 1. 수정할 새로운 데이터의 정보를 저장
bbs_num = request("bbs_num")
bbs_name = request("bbs_name")
bbs_subj = request("bbs_subj")
bbs_pwd = request("bbs_pwd")
bbs_index = request("index")
' 2. DB 연결
strConnect = "DB 연결 문자열"
Set DbCon = Server.CreateObject("ADODB.Connection")
DbCon.Open strConnect
' 3. 수정할 데이터의 존재유무 확인(다른 사람(관리자)이 간발의 차이로 삭제했을 수도 있으니)
strSQL = _
" SELECT " &_
" bbs_num" &_
" FROM " &_
strTable &_
" WHERE " &_
" bbs_index = " & "'" & bbs_index & "'" &_
" and bbs_num = " & bbs_num
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL,DbCon,adOpenKeyset
If rs.BOF Then
rs.Close
DbCon.Close
Set rs = nothing
Set DbCon = nothing
%>
<SCRIPT LANGUAGE="JavaScript">
<!--
alert("수정하시려는 글은 이미 삭제되었습니다.");
history.go(-2);
//-->
</SCRIPT>
<%
End if
rs.Close
' 4. 비밀번호 확인
strSQL = _
" SELECT " &_
" * " &_
" FROM " &_
strTable &_
" WHERE " &_
" bbs_index = " & "'" & bbs_index & "'" &_
" and bbs_pwd = " & bbs_pwd
rs.Open strSQL,DbCon,adOpenKeyset
If rs.BOF Then
rs.Close
DbCon.Close
Set rs = nothing
Set DbCon = nothing
%>
<SCRIPT LANGUAGE="JavaScript">
<!--
alert("입력하신 비밀번호가 맞지 않습니다.\n확인하시고 다시 입력하시기 바랍니다.");
history.go(-1);
//-->
</SCRIPT>
<%
End if
' 5. 레코드 업데이트(실제 데이터 수정 부분)
strSQL = _
" UPDATE " &_
strTable &_
" SET " &_
" bbs_subj=" & "'" & bbs_subj & "'" & "," &_
" bbs_name=" & "'" & bbs_name & "'" &_
" WHERE " &_
" bbs_index = " & "'" & bbs_index & "'" &_
" and bbs_num =" & bbs_num
DbCon.Execute(strSQL)
' 6. 객체 환원(끝내기)
rs.Close
DbCon.Close
Set rs = nothing
Set DbCon = nothing
' 이것이 ASP 에서 DB 의 레코드를 업데이트하는(데이터를 수정하는) 기본이다.
' [4] DB의 레코드 삭제하기(데이터 삭제)
' 1. 삭제한 데이터의 정보를 저장
bbs_num = request("bbs_num")
bbs_pwd = request("bbs_pwd")
bbs_index = request("index")
' 2. DB 연결
strConnect = "DB 연결 문자열"
Set DbCon = Server.CreateObject("ADODB.Connection")
DbCon.Open strConnect
' 3. 삭제할 데이터의 존재유무 확인(다른 사람(관리자)이 간발의 차이로 삭제했을 수도 있으니)
strSQL = _
" SELECT " &_
" bbs_num" &_
" FROM " &_
strTable &_
" WHERE " &_
" bbs_index = " & "'" & bbs_index & "'" &_
" and bbs_num = " & bbs_num
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL,DbCon,adOpenKeyset
If rs.BOF Then
rs.Close
DbCon.Close
Set rs = nothing
Set DbCon = nothing
%>
<SCRIPT LANGUAGE="JavaScript">
<!--
alert("수정하시려는 글은 이미 삭제되었습니다.");
history.go(-2);
//-->
</SCRIPT>
<%
End if
rs.Close
' 4. 비밀번호 확인
strSQL = _
" SELECT " &_
" * " &_
" FROM " &_
strTable &_
" WHERE " &_
" bbs_index = " & "'" & bbs_index & "'" &_
" and bbs_pwd = " & bbs_pwd
rs.Open strSQL,DbCon,adOpenKeyset
If rs.BOF Then
rs.Close
DbCon.Close
Set rs = nothing
Set DbCon = nothing
%>
<SCRIPT LANGUAGE="JavaScript">
<!--
alert("입력하신 비밀번호가 맞지 않습니다.\n확인하시고 다시 입력하시기 바랍니다.");
history.go(-1);
//-->
</SCRIPT>
<%
End if
' 5. 레코드 삭제(실제 데이터 삭제 부분)
strSQL = _
" DELETE FROM " &_
strTable &_
" WHERE " &_
" bbs_index = " & "'" & bbs_index & "'" &_
" and bbs_num =" & bbs_num
DbCon.Execute(strSQL)
' 6. 객체 환원(끝내기)
rs.Close
DbCon.Close
Set rs = nothing
Set DbCon = nothing
' 이것이 ASP 에서 DB 의 레코드를 삭제하는(데이터를 삭제하는) 기본이다.