MySQL로 배우는 데이터베이스 개론과 실습 내용정리 - Chapter 05

01. 데이터베이스 프로그래밍의 개념

💡프로그래밍이란?

프로그램을 설계하는 소스코드를 작성하여 디버깅하는 과정을 말한다.

 

💡데이터베이스 프로그래밍이란?

DBMS에 데이터를 정의하고 저장된 데이터를 읽어와 데이터를 변경하는 프로그램을 작성하는 과정이다.

 

💡삽입 프로그래밍이란?

SQL 단독으로 프로그래밍하는 것이 아니라, 일반 프로그래밍 언어에 SQL문을 삽입하여 각 언어의 장점을 살린 프로그래밍을 하는 것이다.

 

💡호스트 언어란?

SQL문이 삽입되는 프로그래밍 언어

 

데이터베이스 프로그래밍의 대표적인 방법 4가지

1. SQL 전용 언어를 사용하는 방법

SQL 자체의 기능을 확장하여 변수, 제어, 입출력 등의 기능을 추가한 새로운 언어를 사용하는 방법이다. 하지만, GUI를 구축하는 기능이 없다는 단점이 있다.

1. 일반 프로그래밍 언어에 SQL을 삽입하여 사용하는 방법이다.

일반 프로그래밍 언어에 SQL을 삽입하여 사용하는 방법이다. 복잡한 로직의 구현이 용이하다.

1. 웹 프로그래밍 언어에 SQL을 삽입하여 사용하는 방법

웹 스크립트 언어에 SQL을 삽입하여 사용하는 방법이다. 아파치와 같은 웹 서버가 데이터베이스 연동을 지원한다.

1. 4GL(4th Generation Language)

4세대 언어 중 하나인 ‘GUI기반 소프트웨어 개발 도구를’를 사용하여 프로그래밍 하는 방법이다.

 

 

DBMS의 종류와 특징

특징 Access SQL server Oracle MySQL DB2 SQLite

제조사	마이크로소프트사	마이크로소프트사	오라클사	오라클사	IBM사	리처드 힙(오픈소스)
운영체제 기반	윈도우	윈도우, 리눅스	윈도우, 유닉스, 리눅스	윈도우, 유닉스, 리눅스	유닉스	모바일OS(안드로이드, iOS 등)
특징	개인용 DBMS	윈도우 기반 기업용 DBMS	대용량 데이터베이스를 위한 응용	소용량 데이터베이스를 위한 응용	소용량 데이터베이스를 위한 응용	모바일 전용 데이터베이스

데이터베이스 프로그래밍을 할 때는 하드웨어, 운영체제, DBMS, 프로그램 환경이 다양하기 때문에 비용, 성능, 개발의 용이성에 따라 적절한 환경을 선택해야 한다!

---

02. 저장 프로그램

💡저장 프로그램

데이터베이스 응용 프로그램을 작성하는데 사용하는 MySQL의 SQL 전용 언어이다.

(오라클은 PL/SQL, 윈도우는 T-SQL이라는 이름의 SQL 전용 언어를 사용한다.)

01. 저장 프로그램

저장 프로그램은 프로그램 로직을 프리시저로 구현하여 객체 형태로 사용한다. 저장 프로그램은 일반 프로그래밍 언어에서 사용하는 함수와 비슷한 개념이다.

저장 프로그램의 구성

• 저장 루틴
• 트리거
• 이벤트

저장 루틴

• 프로시저
• 함수

MySQL에서 저장 프로그램을 정의하는 과정 (간단히)

• 프로시저는 선언부와 실행부로 구성된다. 선언부에서는 변수와 매개변수를 선언하고 실행부에서는 프로그램 로직을 구현한다.
• 매개변수는 저장 프로시저가 호출될 때 그 프로시저에 전달되는 값이다.
• 변수는 저장 프로시저나 트리거 내에서 사용되는 값이다.
• 소스코드에 대한 설명문은 /와 / 사이에 기술한다. 만약 설명문이 한 줄이면 이중 대시(—) 기호 다음에 기술하면 된다.

 

프로시저 정의, 실행 (간단히)

- MySQL 명령라인에서 데이터베이스를 이용하지 않는 프로시저 dorepeat를 정의하고 실행하는 과정

mysql> delimiter // 
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
-> REPEAT SET @x = @x + 1; UNIT @x > p1 END REPEAT;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter;
mysql> CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x;
+---+
| @x |
+---+
| 1001 |
+---+
 1 row in set (0.00 sec)

 

삽입 작업을 하는 프로시저

프로시저의 의미와 유용성 - Book 테이블의 데이터를 삽입하는 프로시저 InsertBook.sql

use madang
delimiter //
CREATE PROCEDURE InsertBook(
  IN myBookID INTEGER, 
  IN myBookName VARCHAR(40), 
  IN myPublisher VARCHAR(40), 
  IN myPrice INTEGER)
BEGIN
  INSERT INTO Book(bookid, bookname, publisher, price)
      VALUES(myBookID, myBookName, myPublisher, myPrice);
END;
//
delimiter ;

CALL InsertBook(13, '스포츠과학', '마당과학서적', 25000);
SELECT * FROM Book;

 

제어문을 사용하는 프로시저

저장 프로그램의 제어문

+) DELIMITER 의미: 구문 종료 기호 설정/ 문법: DELIMITER{기호}

 

 

동일한 도서가 있는지 점검한 후 삽입하는 프로시저 BookInsertOrUpdate.sql

use madang
delimiter //
CREATE PROCEDURE BookInsertOrUpdate(
  myBookID INTEGER,
  myBookName VARCHAR(40), 
  myPublisher VARCHAR(40),
  myPrice INT) 
BEGIN
  DECLARE mycount INTEGER;
  SELECT count(*) INTO mycount FROM Book 
    WHERE bookname LIKE myBookName; 
  IF mycount!=0 THEN
    SET SQL_SAFE_UPDATES=0; /* DELETE, UPDATE 연산에 필요한 설정 문 */
    UPDATE Book SET price = myPrice
      WHERE bookname LIKE myBookName;
  ELSE
    INSERT INTO Book(bookid, bookname, publisher, price)
      VALUES(myBookID, myBookName, myPublisher, myPrice);
  END IF;
END;
//
delimiter ;

-- BookInsertOrUpdate 프로시저를 실행하여 테스트하는 부분
CALL BookInsertOrUpdate(15, '스포츠 즐거움', '마당과학서적', 25000);
SELECT * FROM Book; -- 15번 투플 삽입 결과 확인
-- BookInsertOrUpdate 프로시저를 실행하여 테스트하는 부분
CALL BookInsertOrUpdate(15, '스포츠 즐거움', '마당과학서적', 20000);
SELECT * FROM Book; -- 15번 투플 가격 변경 확인

 

결과를 반환하는 프로시저

저장 프로시저는 함수와 같이 계산된 결과를 반환할 수도 있다.

Book 테이블에 저장된 도서의 평균가격을 반환하는 프로시저 AveragePrice.sql

delimiter //
CREATE PROCEDURE AveragePrice(
  OUT AverageVal INTEGER)
BEGIN
  SELECT AVG(price) INTO AverageVal 
  FROM Book WHERE price IS NOT NULL;
END;
//
delimiter ;

/* 프로시저 AveragePrice를 테스트하는 부분 */
CALL AveragePrice(@myValue);
SELECT @myValue;

코드 1~9행은 AveragePrice 프로시저를 정의하는 부분이고, 10~12행은 AveragePrice 프로시저를 실행하는 부분이다.

 

커서를 사용하는 프로시저

💡커서sursor는 실행 결과 테이블을 한 번에 한 행씩 처리하기 위하여 테이블의 행을 순서대로 가리키는 데 사용한다.

 

커서와 관련된 키워드

Orders 테이블의 판매 도서에 대한 이익을 계산하는 프로시저 Interest.sql

delimiter //  
CREATE PROCEDURE Interest()
BEGIN
  DECLARE myInterest INTEGER DEFAULT 0.0;
  DECLARE Price INTEGER;
  DECLARE endOfRow BOOLEAN DEFAULT FALSE; 
  DECLARE InterestCursor CURSOR FOR 
	SELECT saleprice FROM Orders;
  DECLARE CONTINUE handler 
	FOR NOT FOUND SET endOfRow=TRUE;
  OPEN InterestCursor;
  cursor_loop: LOOP
    FETCH InterestCursor INTO Price;
    IF endOfRow THEN LEAVE cursor_loop; 
    END IF;
    IF Price >= 30000 THEN 
        SET myInterest = myInterest + Price * 0.1;
    ELSE 
        SET myInterest = myInterest + Price * 0.05;
    END IF;
  END LOOP cursor_loop;
  CLOSE InterestCursor;
  SELECT CONCAT(' 전체 이익 금액 = ', myInterest);
END;
//
delimiter ;

/* Interest 프로시저를 실행하여 판매된 도서에 대한 이익금을 계산 */
CALL Interest();

02. 트리거

💡트리거trigger는 데이터의 변경(INSERT, DELETE, UPDATE) 문이 실행될 때 자동으로 같이 실행되는 프로시저를 말한다. (DBMS 제조사에 따라 트리거의 정의가 많이 다르다)

보통 트리거는 데이터의 변경문이 처리되는 세 가지 시점, 즉 실행 전, 대신하여, 실행 후에 동작한다.

 

신규 도서를 삽입한 후 자동으로 Book_log 테이블에 삽입한 내용을 기록하는 트리거

root계정에서 트리거 작동을 위해서 다음 문장을 실행한다.

SET golbal log_bin_trust_function_creators=ON;

madang계정에서 실습을 위한 Book_log 테이블을 생성한다. Booklog.sql

CREATE TABLE Book_log(
  bookid_l INTEGER,
  bookname_l VARCHAR(40),
  publisher_l VARCHAR(40),
  price_l INTEGER);

delimiter //
CREATE TRIGGER AfterInsertBook 
  AFTER INSERT ON Book FOR EACH ROW
BEGIN  
  DECLARE average INTEGER;
  INSERT INTO Book_log 
    VALUES(new.bookid, new.bookname, new.publisher, new.price);
END;
//
delimiter ;

/* 삽입한 내용을 기록하는 트리거 확인 */
INSERT INTO Book VALUES(14, '스포츠 과학 1', '이상미디어', 25000);
SELECT * FROM Book WHERE BOOKID=14;
SELECT * FROM Book_log  WHERE BOOKID_L='14' ; -- 결과 확인

 

03. 사용자 정의 함수

사용자 정의 함수는 입력된 값을 가공하여 결과 값을 되돌려준다.

사용자 정의 함수는 사용자가 직접 필요한 기능을 함수로 만들어 사용한다.

MySQL에서 작성할 수 있는 사용자 정의 함수는 단일 값을 돌려주는 스칼라 함수가 일반적이다.

 

 

판매된 도서에 대한 이익을 계산하는 함수 fnc_Interest.sql

delimiter //
CREATE FUNCTION fnc_Interest(
  Price INTEGER) RETURNS INT  
BEGIN
  DECLARE myInterest INTEGER;
-- 가격이 30,000원 이상이면 10%, 30,000원 미만이면 5%
  IF Price >= 30000 THEN SET myInterest = Price * 0.1;
  ELSE SET myInterest := Price * 0.05;
  END IF;
  RETURN myInterest;
END; //
delimiter ;

/* Orders 테이블에서 각 주문에 대한 이익을 출력 */
SELECT custid, orderid, saleprice, fnc_Interest(saleprice) interest 
FROM Orders;

 

 

프로시저 VS 트리거 VS 사용자 정의 함수

04. 저장 프로그램 문법 요약

저장 프로그램의 기본적인 문법은 https://dev.mysql.com/doc/refman/8.0/en/ 참고!

---

04. 데이터베이스 연동 자바 프로그래밍

01. 소스코드 설명

여기서 연동이란 자바 프로그램을 수행하여 DBMS를 동작시킨다는 의미이다.

 

 

다음은 Book테이블에 저장된 도서를 읽어와 출력하는 프로그램이다.

import java.io.*;
import java.sql.*;
// SQL 관련 클래스는 java.sql .*에 포함되어 있다.
public class BookList {
   Connection con;
// 클래스 booklist를 선언한다. java.sql의 Connection 객체 con을 선언한다.  	
   public BookList() {
     String Driver="";
     String 
url="jdbc:mysql://localhost:3306/madang?&serverTimezone=Asia/Seoul"; 
     String userid="madang";
     String pwd="madang";
// 접속변수를 초기화한다. url은 자바 드라이버 이름, 호스트명(localhost), 포트번호를 입력한다
// userid는 관리자(madang), pwd는 사용자의 비밀번호(madang)를 입력한다.    
     try { /* 드라이버를 찾는 과정 */
       Class.forName("com.mysql.cj.jdbc.Driver");   
       System.out.println("드라이버 로드 성공");
     } catch(ClassNotFoundException e) {
         e.printStackTrace();
      }
// Class.forName()으로 드라이버를 로딩한다. 드라이버 이름을 Class.forName에 입력한다.      
     try { /* 데이터베이스를 연결하는 과정 */
       System.out.println("데이터베이스 연결 준비...");	
       con=DriverManager.getConnection(url, userid, pwd);
       System.out.println("데이터베이스 연결 성공");
     } catch(SQLException e) {
         e.printStackTrace();
       }
   }
// 접속 객체 con을 DriverManager.getConnection 함수로 생성한다. 
// 접속이 성공하면 "데이터베이스 연결 성공"을 출력하도록 한다.  
// 문자열 query에 수행할 SQL 문을 입력한다.
   private void sqlRun() {
  	  String query="SELECT * FROM Book"; /* SQL 문 */
  	  try { /* 데이터베이스에 질의 결과를 가져오는 과정 */
  	  	 Statement stmt=con.createStatement();
  	  	 ResultSet rs=stmt.executeQuery(query);
  	  	 System.out.println(" BOOK NO \\tBOOK NAME \\t\\tPUBLISHER \\tPRICE ");
  	  	 while(rs.next()) {
  	  	 	System.out.print("\\t"+rs.getInt(1));
  	  	 	System.out.print("\\t"+rs.getString(2));
  	  	 	System.out.print("\\t\\t"+rs.getString(3));
  	  	 	System.out.println("\\t"+rs.getInt(4));
  	  	 }
  	  	 
  	  	 con.close();
  	  } catch(SQLException e) {
  	  	   e.printStackTrace();
  	    }
   }
  	  
  	public static void main(String args[]) {
  	   BookList so=new BookList();
  	   so.sqlRun();
  	}
}

자바는 객체 지향 언어이기 때문에 객체를 호출하여 데이터베이스에 접속한다.

데이터 베이스에 접속하는 API를 java.sql.*에서 제공한다. java.sql에 정의된 API는 각 DBMS 제조사에서 자신의 제품의 맞게 구현해서 제공하는데 이를 JDBC 드라이버라고 한다.

 

 

02. 프로그램 실습

[1단계] DBMS 설치 및 환경설정

1. MtSQL 8.x 설치
2. SQL 접속을 위한 사용자(madang)설정

[2단계] 데이터베이스 준비

1. 마당서점 데이터베이스 준비(demo.madang.sql)

[3단계] 자바 실행(명령 프롬포트 이용)

1. 자바 컴파일러 설치
2. JDBC 드라이버 설치
3. 자바 프로그램 준비(Booklist.java)
4. 컴파일 및 실행
5. 자바 실행(이클립스 이용)
6. 이클립스 개발도구 설치
7. JDBC 드라이버 설치
8. 자바 프로그램 준비(Booklist.java)
9. 컴파일 및 실행

---

04. 데이터베이스 연동 웹 프로그래밍

웹에서 응용 프로그램을 개발하게 되면 웹서버, 데이터베이스, 프로그래밍 언어를 정해야 한다. 각각 사용이 간편한 환경을 가진 Apache, MySQL, PHP 세가지 소프트웨어를 AMP로 줄여 부른다.

 

Apache 웹서버에서 PHP를 이용하여 MySQL 데이터베이스와 연동하는 실습을 해보자!

01. 소스코드 설명

booklist.php는 테이블에 저장된 도서를 읽어와 웹 브라우저에 출력하는 PHP 프로그램이다.

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<h2><blockquote> 마당서점 도서목록 </blockquote></h2>
**<?php
	$conn = mysqli_connect('localhost', 'madang', 'madang', 'madang');
	if (mysqli_connect_error($conn)){
		echo 'Connection Error';
		exit();
	}

	$sql = "SELECT * FROM Book";
	$result = mysqli_query($conn, $sql);

	echo '<table border=1><tr><td>BOOKNAME</td><td>PUBLISHER</td><td>PRICE</td></tr> ';
	while($row = mysqli_fetch_array($result)) {
		echo ('<tr><td><b>
			<a href="bookview.php?id='.$row['bookid'].'">'
			.$row['bookname'].'</a></b></td><td>'
			.$row['publisher'].' </td><td> '
			.$row['price'].'</td></tr>'); 
	}
	echo '</table><p>';

?>**

PHP 프로그램은 HTML 태그에 PHP 스크립트를 끼워 넣어 작성하는데, PHP 스크립트 부분은 <?PHP .. ?>에 넣어서 실행시킨다.

02. 프로그래밍 실습

[1단계] DBMS 설치 및 환경설정

1. MtSQL 8.x 설치
2. SQL 접속을 위한 사용자(madang)설정

[2단계] 데이터베이스 준비

1. 마당서점 데이터베이스 준비(demo.madang.sql)

[3단계] PHP 실행

1. Apache, PHP 설치
2. PHP 프로그램 준비(booklist.php, bookreview.php)
3. 실행