SQL Query 사용법 정리 > 기술자료

• 목록

   <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 ( https://www.happyjung.com/bbs/board.php?bo_table=lecture&wr_id=1452 참조 / update. 2013-05-13 )
        예)
        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)    ' 위에서 작성한 레코드입력 스크립트를 실행한다.

    4. 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 의 레코드를 삭제하는(데이터를 삭제하는) 기본이다.


자료출처
http://ttongfly.net/zbxe/?mid=database&document_srl=45365