데이터베이스 DDL, 뷰, 인덱스

 

DDL 

: 데이터베이스 내에 테이블이나 인덱스, 뷰 등의 객체를 만들거나 수정, 삭제할 때 사용

CREATE, ALTER, DROP

 

 

CREATE

: 데이터베이스나 테이블, 뷰, 인덱스 등 객체를 만들 때 사용

CREATE TABLE 테이블명 (컬럼1 (데이터타입), 컬럼2 (데이터타입))

 

문제 1. 다음 요구사항을 만족하는 MySQL 데이터베이스를 생성하고, 테이블을 만드세요.
데이터베이스 이름: company_db
테이블: test
컬럼:
id (INT, PRIMARY KEY, AUTO_INCREMENT)
first_name (VARCHAR(50))
last_name (VARCHAR(50))
email (VARCHAR(100), UNIQUE)
date_of_birth (DATE)
date_of_joining (DATE)
department (VARCHAR(50))
salary (DECIMAL(10, 2))

create database company_db;

use company_db;

CREATE TABLE test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    date_of_birth DATE,
    date_of_joining DATE,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

 

 

문제 2. test 테이블에 직원의 직급을 저장할 rank 컬럼을 추가하세요. 이 컬럼은 최대 20자의 가변 길이 문자열을 저장할 수 있어야 합니다.

alter table test 
add column `rank` VARCHAR(20);

문제 3. test 테이블의 salary 컬럼의 데이터 타입을 변경하여 최대 15자리 숫자를 소수점 이하 두 자리까지 저장할 수 있도록 수정하세요.

alter table test
modify column salary decimal(15, 2);

-> decimal(전체자리수, 소수점 자리수) : 고정 소수점

문제 4. test 테이블의 date_of_joining 컬럼의 이름을 hire_date로 변경하고, 데이터 타입을 TIMESTAMP로 변경하세요.

alter table test
change column date_of_joining hire_date timestamp;

-> 컬럼 변경

ALTER TABLE 테이블명 CHANGE COLUMN 기존컬럼명 새로운컬럼명 데이터타입;

문제 5. test 테이블에서 rank 컬럼을 삭제하세요.

alter table test drop column `rank`;

문제 6. test 테이블의 이름을 staff로 변경하세요.

alter table test rename table test to staff;

-> ALTER TABLE 기존테이블명 RENAME 새로운테이블명;

 

---

 

뷰 (View)

: 한 개 이상의 테이블을 기반으로 생성된 가상의 테이블

뷰는 실제 데이터 저장 x, 쿼리 실행 시점에 생성된 쿼리 결과를 가상 테이블로 만들어서 제공

 

문제 1. (view)각 부서별로 직원 수를 조회하시오. 단, 부서 번호와 부서 이름, 그리고 직원 수를 포함하시오.

create view view1 as
SELECT
    d.dept_no,
    d.dept_name,
    COUNT(e.Emp_no) AS employee_count
FROM
    department d
LEFT JOIN
    employee e ON d.dept_no = e.Dept_no
GROUP BY
    d.dept_no, d.dept_name;

 

 

문제 2. 특정 고객이 주문한 제품들의 총 금액을 조회하는 VIEW를 생성하시오. 단, 고객 번호와 고객 이름, 그리고 총 금액을 포함하시오.

CREATE VIEW view2 AS SELECT c.customer_no, c.person_in_charge_name,
 sum(od.unit_price * od.order_quantity * (1 - od.discount_rate)) 
from orders o
join customer c  on c.customer_no = o.customer_no
join order_details od on o.order_no = od.order_no
group by c.customer_no;

 

문제 3. 각 직원의 상사 이름과 직원 이름을 함께 조회하는 VIEW를 생성하시오. 단, 상사가 없는 직원도 포함하시오.

create view view3 as
SELECT
    e1.name AS employee_name,
    e2.name AS boss_name
FROM
    employee e1
LEFT JOIN
    employee e2 ON e1.boss_number = e2.Emp_no;

 

문제 4. 모든 주문에 대해 주문 번호와 주문한 제품의 개수, 총 주문 금액을 조회하는 VIEW를 생성하시오.

create view v_order_summary as
SELECT
    o.order_no,
    COUNT(od.product_no) AS product_count,
    SUM(od.unit_price * od.order_quantity * (1 - od.discount_rate)) AS total_order_amount
FROM
    orders o
JOIN
    order_details od ON o.order_no = od.order_no
GROUP BY
    o.order_no;

 

문제 5. 특정 직원이 담당한 주문 내역을 조회하는 VIEW를 생성하시오. 단, 직원 번호와 이름, 주문 번호, 주문 날짜, 고객 이름을 포함하시오.

create view v_order_detail_employee as
select e.emp_no, e.name, o.order_no, o.order_date, c.customer_comp_name
from employee e
join orders o on e.emp_no = o.emp_no
join customer c on o.customer_no = c.customer_no
where e.name = '이소미'
group by o.order_no;

 

 

---

인덱스

: 데이터베이스 테이블에서 특정 컬럼이나 컬럼의 집합에 대한

검색 성능을 향상시키기 위해 사용되는 자료구조

 

---

 

SQL 스포어드 프로그램

• 사용자 정의 변수
• 로컬 변수
• 시스템 변수

 

사용자 변수 설정

• 세선 내에서 사용자가 정의한 변수를 유지
• 세선이 종료될 때까지 값을 유지
• @를 접두사로 사용
• 변수에 값을 할당시 SET, SELECT 사용

set @abc = 100;
set @exam = 10;
set @var_name = '이소미';
select @from := 100;

 

-> 사용자 변수 지정

 

select * from employee where name = @var_name;

 

-> 변수 사용하여 employee 테이블에서 조회

 

 

 

---

 

특정 직원의 이름을 입력받아 해당 직원과 상사 명을 출력하는 sql

SET @employee_name = '이소미';
SELECT
    e.name AS employee_name,
    e.boss_number AS manager_no
    #m.name AS manager_name
FROM
    employee e
LEFT JOIN
    employee m ON e.boss_number = m.Emp_no
WHERE
    e.name = @employee_name;

 

---

 

사용자 정의 프로시저의 생성, 호출, 삭제

 

생성 형식

DELIMTER $$

CREATE PROCEDURE 프로시저명

   ( [IN | OUT | INOUT 매개변수] )

BEGIN 

  실행할 코드

END $$

DELIMITER;

DELIMITER //
CREATE PROCEDURE CountEmployeesInDept()
BEGIN
    -- 로컬 변수 선언
    DECLARE deptNo CHAR(2) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
    DECLARE employeeCount INT;
    -- 변수 초기화
    SET deptNo = '01';
    SET employeeCount = 0;
    -- 특정 부서의 직원 수 계산
    SELECT COUNT(*)
    INTO employeeCount
    FROM employee
    WHERE dept_no = deptNo COLLATE utf8mb4_general_ci;
    -- 결과 출력
    SELECT CONCAT('Department ', deptNo, ' has ', employeeCount, ' employees.') AS Result;
END //
DELIMITER ;

 

호출 형식

CALL 프로시저명;

 

삭제 형식

DROP PROCEDURE 프로시저명;

---

 

문제 1: 특정 고객의 총 주문 금액 계산
특정 고객의 모든 주문에 대한 총 금액을 계산하는 저장 프로시저를 작성할 것
프로시저는 고객 번호를 입력 매개변수로 받고, 해당 고객의 총 주문 금액을 반환

 

DELIMITER //
CREATE PROCEDURE totalOrderAmount(in customerNo char(5))
BEGIN
    DECLARE totalAmount float;
    set totalAmount = 0;
    select sum(unit_price * order_quantity * (1 - discount_rate))
    into totalAmount
	from orders o, order_details od
	where o.order_no = od.order_no and o.customer_no COLLATE utf8mb4_general_ci = customerNo;
    SELECT customerNo as customer, totalAmount as total_order_price;
END //
DELIMITER ;

 

 

---