[데이터베이스] 6주차 실습

뷰 실습

12-1 뷰 생성

CREATE VIEW highorders AS
SELECT b.bookid, b.bookname, c.name, b.publisher, o.saleprice
FROM Book b, Customer c, Orders o
WHERE b.bookid = o.bookid AND c.custid = o.custid
AND o.saleprice >= 20000;

• 여러 테이블에 흩어져 있는 정보들 중, 판매가격이 2만원 이상인 데이터만 골라 뷰로 만든다.
• CREATE VIEW를 이용하면 된다!

12-2 뷰 조회

SELECT bookname, name
FROM highorders;

• 복잡한 조인을 이용하지 않고 뷰에 이미 생성되어있는 정보를 조회할 수 있다.

12-3 뷰 변경

CREATE OR REPLACE VIEW highorders AS
SELECT b.bookid, b.bookname, c.name, b.publisher
FROM Book b, Customer c, Orders o
WHERE b.bookid = o.bookid AND c.custid = o.custid
AND o.saleprice >= 20000;

SELECT bookname, name
FROM highorders;

• CREATE OR REPLACE VIEW를 이용하여 기존 뷰를 덮어쓸 수 있다.

---

저장 프로그램 실습

 

1. 프로시져 실습

 

heloworld 프로시저 만들어보기

-- 1. 프로시저 정의를 위해 구분자(Delimiter)를 //로 변경
DELIMITER //

-- 2. 만약 이미 같은 이름의 프로시저가 있다면 삭제 (선택 사항)
DROP PROCEDURE IF EXISTS HelloWorld //

-- 3. HelloWorld 프로시저 생성
CREATE PROCEDURE HelloWorld()
BEGIN
    -- 'Hello, World'라는 문자열을 message라는 별칭으로 출력
    SELECT 'Hello, World' AS message;
END //

-- 4. 구문 종료 기호를 다시 ;로 복구
DELIMITER ;

-- 5. 프로시저 호출
CALL HelloWorld();

HelloWorld 프로시저 만들고 호출

 

person 테이블 만들고 getAge 프로시저 생성하기

-- 1. 테이블 생성
CREATE TABLE person (
    id INT PRIMARY KEY,
    이름 VARCHAR(50),
    나이 INT
);

-- 2. 샘플 데이터 삽입
INSERT INTO person (id, 이름, 나이) VALUES
(1, '짱구', 5),
(2, '흰둥이', 30),
(3, '봉미선', 35);

-- 3. getAge 프로시저 생성
DELIMITER //

CREATE PROCEDURE getAge(
    IN p_id INT, 
    OUT p_name VARCHAR(50), 
    OUT p_age INT
)
BEGIN
    SELECT 이름, 나이 INTO p_name, p_age
    FROM person
    WHERE id = p_id;
END //

DELIMITER ;

-- 4. 프로시저 호출 및 결과 확인
CALL getAge(2, @name, @age);

SELECT @name AS 이름, @age AS 나이;

 

2. 트리거 실습

 

person 테이블에 새로운 행이 삽입될 때, 같은 이름이 이미 존재하면 로그 테이블에 기록하는 트리거

-- 1. 알림 로그를 저장할 테이블 생성
CREATE TABLE person_alert (
    alert_id INT AUTO_INCREMENT PRIMARY KEY,
    이름 VARCHAR(50),
    action_time DATETIME,
    message VARCHAR(255)
);

-- 2. 중복 이름 체크 트리거 생성
DELIMITER //

CREATE TRIGGER check_duplicate_name
BEFORE INSERT ON person
FOR EACH ROW
BEGIN
    DECLARE cnt INT;

    -- 동일한 이름이 이미 몇 명 있는지 확인
    SELECT COUNT(*) INTO cnt
    FROM person
    WHERE 이름 = NEW.이름;

    -- 만약 1명 이상(중복) 있으면 알림 테이블에 기록
    IF cnt > 0 THEN
        INSERT INTO person_alert (이름, action_time, message)
        VALUES (NEW.이름, NOW(), '동명이인이 존재합니다.');
    END IF;
END //

DELIMITER ;

-- 3. 데이터 삽입 테스트 (이미 '짱구'가 1번에 있으므로 알림이 쌓여야 함)
INSERT INTO person (id, 이름, 나이) VALUES (4, '짱구', 7);

-- 4. 알림 로그 확인
SELECT * FROM person_alert;

 

3. 함수 실습

 

함수 정의 기본

-- 1. 함수 정의를 위해 구분자 변경
DELIMITER //

-- 2. 만약 기존에 같은 이름의 함수가 있다면 삭제 (선택 사항)
DROP FUNCTION IF EXISTS fnHello //

-- 3. fnHello 함수 생성
CREATE FUNCTION fnHello() 
RETURNS VARCHAR(20)
DETERMINISTIC   -- 함수의 결과가 입력값에 따라 일정함을 명시 (MySQL 설정에 따라 필요함)
BEGIN
    RETURN 'Hello';
END //

-- 4. 구문 종료 기호를 다시 ;로 복구
DELIMITER ;

-- 5. 함수 호출 (SELECT 문에서 바로 사용 가능)
SELECT fnHello() AS message;

생년월일을 입력받아 나이를 반환하는 함수

-- 1. 함수 생성을 위한 구분자 변경
DELIMITER //

-- 2. 이미 함수가 존재한다면 삭제
DROP FUNCTION IF EXISTS fnGetAge //

-- 3. 나이 계산 함수 생성
CREATE FUNCTION fnGetAge(birthdate DATE) 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE age INT;
    
    -- TIMESTAMPDIFF를 사용하여 두 날짜 사이의 연도(YEAR) 차이를 계산
    SET age = TIMESTAMPDIFF(YEAR, birthdate, CURDATE());
    
    RETURN age;
END //

-- 4. 구분자 원복
DELIMITER ;

-- 5. 함수 호출 및 결과 확인
-- 문자열 형식을 DATE 타입으로 자동 변환하여 계산합니다.
SELECT fnGetAge('1990-05-15') AS Age;

---

JDBC 연결하기 실습

 

1. 일단 JDBC를 설치해준다. 저장 후 편한 경로에 압축만 풀어주면 된다.

https://dev.mysql.com/downloads/connector/j/

MySQL :: Download Connector/J

 

 

2. eclipse 켜고 프로젝트 생성, Main 클래스 하나 만들어두기

만들 때 꼭 Create module-info.java file 체크박스를 해제해야한다.

 

 

 

3. 실습에 앞서서 test 안에 데이터 입력을 좀 해두자...

 

 

 

3. 메인 클래스에서 DB를 연결한다!

import java.sql.*;

public class Main {

	public static void main(String[] args) {
		// DB 접속 위한 url, 사용자명, 비밀번호 설정
		String url = "jdbc:mysql://127.0.0.1:3306/test"; // db 서버 주소와 db 이름
		String username = "root"; // db 사용자 계정
		String passwd = "0304"; // db 사용자 비밀번호

		try (Connection conn = DriverManager.getConnection(url, username, passwd)) {
			System.out.println("DB연결 성공!");

			// Select 예
			String selectSql = "SELECT * FROM student";

			// sql 실행용 statement 객체 생성
			try (Statement stmt = conn.createStatement()) {
            
				ResultSet rs = stmt.executeQuery(selectSql);

				while (rs.next()) {
					int no = rs.getInt("no");
					String dept = rs.getString("dept");
					String stdID = rs.getString("stdID");
					String name = rs.getString("name");

					System.out.printf("No : %10d | 학과 : %10s | 학번 : %10s | 이름 : %10s\n", no, dept, stdID, name);
				}

			}

			// INSERT 예
			String insertSql = "INSERT INTO student (no, dept, stdID, name) VALUES (?, ?,?,?)";
			// PreparedStatement 사용 : ?에 값 바인딩 가능
			try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) {
				pstmt.setInt(1, 25);
				pstmt.setString(2, "컴퓨터공학과");
				pstmt.setString(3, "2466054");
				pstmt.setString(4, "조언");
                
				// SQL 실행 (INSERT, UPDATE, DELETE는 executeUpdate 사용)
				pstmt.executeUpdate();
				System.out.println("학생 정보 삽입 성공");
			}

		} catch (SQLException e) {
			e.printStackTrace();

		}

	}
}

• url과 username, passwd의 경우 자신의 값을 채워야한다.
• url: "jdbc:mysql://IP주소:포트번호/연결할 DB 주소"
• username: 사용자 이름
• passwd: 암호

heidi 시작화면에서 보고 적으면 된다!

 

 

 

4. 정보 조회 / 내 정보 입력 실습(select, insert 쿼리)

• 3번 코드를 실행해보자.
• DB 연결이 잘 된 경우

• 이미 표에 있는 내용을 조회하여 출력하는 것도 가능하고,

• 내 정보도 잘 삽입되어 있음을 확인할 수 있다.