7. SQL

1. SQL 개요

1. SQL이란?

- 관계형 DBMS를 연구할 때, 관계형 대수와 관계형 해석을 기반으로 개발된 데이터 언어

- 관계형 데이터베이스의 "표준언어"

- 모든 DBMS에서 사용 가능

- 대소문자는 구별하지 않음 (단, 데이터의 대소문자는 구분)

 

 

*MySQL은 데이터 대소문자도 구분 x => binary()를 써서 대소문자를 구분하게 함

select id from employee where binary(first_name) = "steven";

 

 

2. SQL의 특징

- SQL 구문은 데이터 정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL)로 구분

- 형태

   1) 대화형 질의어 형태(터미널을 통한 workbench, cmd)

   2) SQL이 삽입된 형태(Java, C 등)

 

 

 

2. SQL 데이터 정의어 : DDL

1. DDL(Data Definition Language)이란?

- 데이터베이스 객체(table, view, index 등)의 구조를 생성, 변경, 제거한다.

• 테이블 생성/정의
• 제약 조건 지정/수정
• 애트리뷰트(열) 추가/제거
• 뷰 생성/제거
• 인덱스 생성/제거

 

2. DDL 종류 = CREATE, ALTER, DROP, RENAME

 

 

3. DDL 활용

2022.03.16 - [개발에 필요한 지식/데이타베이스] - [SQL] 데이터 정의어(DDL) 활용

[SQL] 데이터 정의어(DDL) 활용

 

 

 

 

3. SQL 데이터 조작어 : DML

1. DML(Data Manipulation Language)이란?

- Database테이블에서 개별 데이터(=행)을 검색, 입력, 변경, 삭제 등을 처리

- 테이블의 레코드를 CRUD(Create, Read, Update, Delete)

 

 

2. DML 종류

문장	설명
INSERT (C)	Database 테이블에서 행을 입력, 변경, 삭제
UPDATE (U)
DELETE (D)
SELECT (R)	Database로 부터 Data를 검색
COMMIT	수행한 변경을 관리
ROLLBACK

 

 

3. DML의 활용

2022.03.16 - [개발에 필요한 지식/데이타베이스] - [SQL] 데이터 조작어(DML) 활용

[SQL] 데이터 조작어(DML) 활용

 

 

 

4. SQL 데이터 제어어 : DCL

1. DCL(Data Control Language)란?

- Database 객체에 대한 접근 권한을 부여하고 회수

 

2. DCL의 종류 = GRANT(부여), REVOKE(회수)

 

 

 

5. SQL 뷰

1. 개념

- 하나 이상의 기본 테이블로부터 유도되어 만들어지는 "가상 테이블"

 

 

2. 뷰의 생성

- "CREATE VIEW 뷰 이름 AS SELECT 문" 형태

- WITH CHECK OPTION : 뷰 정의 조건인 학과 = '컴퓨터'를 위반 => 뷰에 대한 갱신, 삽입 연산이 거절됨

/*학생 테이블의 ‘컴퓨터’과 학생들로 구성된 학생뷰를 생성하라*/
CREATE VIEW 학생뷰(학번, 성명, 전화번호)
AS SELECT 학번, 성명, 전화번호
FROM 학생
WHERE 학과 = '컴퓨터'
WITH CHECK OPTION;

/*집계 함수 사용*/
CREATE VIEW 학과별통계(학과, 학생수)
AS SELECT 학과, COUNT(*)
FROM 학생
GROUP BY 학과;

/*2개 이상의 테이블을 조인*/
CREATE VIEW 우수학생(성명, 학과, 점수)
AS SELECT 학생.성명, 학생.학과, 수강.점수
FROM 학생, 수강
WHERE 학생.학번 = 수강.학번 AND 수강.점수 >= 90;

 

 

3. 뷰의 제거

- "DROP VIEW 뷰이름 {RESTRICT|CASCADE};" 형태

 

*RESTRICT : 종속적인 뷰가 정의되지 않았을 때만 뷰를 삭제

*CASCADE : 종속적인 다른 모든 뷰나 제약 조건을 함께 제거

 

 

4. 뷰의 장점

1) 관련된 데이터만 이용 가능

- 중요하고 필요한 데이터만으로 구성 가능

- 민감한 데이터에 대한 접근 금지(보안) 가능

 

2) 데이터베이스 복잡성 해소

- 복잡한 데이터베이스 구조 숨길 수 있음

- 복잡한 질의 단순화 가능

 

3) 권한 부여를 단순화

 

 

5. 뷰의 단점

1) 정의를 변경할 수 없다.

2) 삽입, 삭제, 갱신 연산에 제한이 많음

 

 

 

6. 삽입 SQL

1. 삽입 SQL 특징

EXEC SQL BEGIN DECLARE SECTION;
    int sno;
    char sname[21];
    char dept[7];
    char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
sno = 20181234;

EXEC SQL SELECT 학번, 성명, 학과
    INTO :sno, :sname, :sdept
    FROM 학생
    WHERE 학번 = :sno;
IF(SQLSTATE = '00000')
	...;
ELSE ...;

1) 이중 모드

- 터미널에서 사용할 수 있는 모든 SQL문은 응용 프로그램에서도 사용 가능

2) 명령문 앞에 EXEC SQL을 붙여 다른 명령문과 구별

3) 호스트 변수 : BEGIN DECLARE과 END DECLARE SECTION문 사이에 선언

4) 호스트 변수는 콜론(:)을 앞에 붙임

5) 호스트 변수와 DB필드의 이름이 같아도 된다.(호스트 변수는 콜론을 붙이기 때문)

6) 호스트 변수와 대응하는 SQL열의 데이터 타입은 일치해야 함

7) SQLSTATE 호스트 변수 : SQL문이 실행되면 실행 상태(성공, 실패, 오류)가 이 변수에 전달

 

 

2. 단일 레코드 검색

/*학번 20181234인 학생의 학번, 성명, 학과 검색*/
#include <stdio.h>
int main(){
    EXEC SQL BEGIN DECLARE SECTION;
        int sno;
        char sname[10];
        char sdept[20];
    EXEC SQL END DECLARE SECTION;
    sno = 20181234;
    EXEC SQL CONNECT TO academicDB USER kim;
    EXEC SQL SELECT sno, sname sdept
        INTO :sno, :sname, :sdept
        FROM student
        WHERE sno = :sno;
    printf("Student no: %s, name: %s, dept: %s", sno, sname, sdept);
    return 0;
}

/*단일 레코드 갱신*/
/*학번이 20183654인 학생의 점수를 호스트 변수 x값만큼 증가시켜라*/
EXEC SQL UPDATE 수강 SET 점수 = 점수 + x WHERE 학번 = 20183654;

 

 

3. 복수 레코드 검색

- 하나씩 접근하기 위해 "커서"를 사용

- DECLARE cur문은 뒤에 나오는 SELECT문을 연결 => CURSOR가 open될 때 SELECT문 실행

- SELECT 실행 결과의 첫 번째 투플 이전을 커서가 가리킴

- FETCH문 : 커서를 다음 투플로 이동

- DO ... END문 내에서 루프 수행

- 더 이상 레코드가 없을 때 루프 종료, CLOSE문에서 커서 닫기

EXEC SQL DECLARE cur CURSOR FOR /*커서의 정의*/
    SELECT 학번, 성명, 학과
    FROM 학생
    WHERE 학과 = :sdept;
EXEC SQL OPEN cur; /*SELECT문 실행*/
    DO
        EXEC SQL FETCH cur INTO :sno, :sname, :sdept; /*다음 레코드로 이동*/
        .....
    END;
EXEC SQL CLOSE cur; /*커서 종료*/

/*커서가 가리키는 특정 레코드(CURRENT OF cur)를 변경/삭제*/
/*변경*/
EXEC SQL UPDATE 학생 SET 학과 = :sdept WHERE CURRENT OF cur;

/*삭제*/
EXEC SQL DELETE FROM 학생 WHERE CURRENT OF cur;

 

 

 

7. 동적 SQL

1. 동적 SQL의 개념

- SQL문을 동적으로 작성할 수 있도록 지원하는 기능

 

 

2. 기본적인 명령

1) PREPARE : 주어진 SQL문 컴파일 => 목적코드 => stSQL(목적파일)에 저장

2) EXECUTE : 저장되어 있는 stSQL의 SQL 목적코드 실행

varchar staticSQL[256]; /*호스트 변수*/
staticSQL = "SELECT * FROM 수강 WHERE 과목코드 = 'CO123' AND 점수 >= 90";
EXEC SQL PREPARE stSQL FROM :staticSQL; /*컴파일*/
EXECSQL EXECUTE stSQL; /*실행*/

 

3. 매개변수 사용

- staticSQL문에는 호스트 변수 사용 불가

- 대신, 물음표(?)로 표현되는 매개변수 사용

- 물음표가 있는 SQL문 실행을 위해 USING절 사용

          => 호스트 변수 :code, :score값이 SELECT문의 물음표 순서에 대응

varchar dynamicSQL[256];
dynamicSQL = "SELECT * FROM 수강 WHERE 과목코드=? AND 점수>=? ";
...
EXEC SQL PREPARE dySQL FROM :dynamicSQL;
  ...
  code=‘CO123’; 
  score=90;
EXEC SQL EXECUTE dySQL USING :code, :score; /*user가 입력하도록*/

 

8. JDBC

- Java에서 SQL Programming

public static void main (String[] args)
{ 
  Connection conn = null;
  String url = "jdbc:mysql://localhost/academicDB"; //academicDB 사용
  String id = "root"; 
  String pass = "root";
  Statement stmt = null; 
  ResultSet rs = null;
  String query = "select * from student";
  
  try {
    conn = DriverManager.getConnection(url, id, pass);
    stmt = conn.createStatement();
    rs = stmt.executeQuery(query);
    while(rs.next()) { //next = fetch = 다음 투플로 이동
    System.out.println(rs.getInt(1) + ", " + rs.getString(2) );
  }
  rs.close();
  stmt.close();
  conn.close();
}