6.22 database 평가 answer

Database/mysql

6.22 database 평가 answer

amungstudy 2023. 7. 3. 09:07

-- 데이터베이스 구현
-- 1.1 Database, Schema 생성
CREATE DATABASE test_db;
CREATE SCHEMA test_db;

-- test_db 라는 database 공간 사용
use test_db;
-- Server - Data Import Import 할 파일 선택 후
-- Start Import

commit;

SELECT * FROM emp;
SELECT * FROM dept;

-- 1.2 DML
-- emp 테이블에 사원 정보 추가
INSERT INTO emp
VALUES(8000,'MASTER','SALESMAN',7839,'1982-06-07',1200,500,40);
SELECT * FROM emp WHERE empno = 8000;

-- - emp 테이블의 사원 정보 중 사원번호가 7566(JONES)
-- 사원의 comm 의 공백을 수정하는 질의문을 완성 하시오. (comm = 600)
UPDATE emp SET comm = 600 WHERE empno = 7566;
SELECT * FROM emp;

-- emp 테이블의 행 정보 중 사원번호가 8000번인 사원의 정보를 삭제하는 질의문을 완성하시오.
DELETE FROM emp WHERE empno = 8000;
SELECT * FROM emp WHERE empno = 8000;

-- TRANSACTION
SELECT @@autocommit;
-- 0 : false , 1 : TRUE
SET @@autocommit = TRUE;
SET @@autocommit = 0;

DELETE FROM emp;
SELECT * FROM emp;

-- 후진 복귀 - 마지막 commit 시점으로 되돌림
rollback;

SELECT * FROM emp;

-- 2.1 그룹별 집계함수 결과 값에 전체 결과값을 같이 출력  -- WITH ROLLUP
SELECT deptno, count(*) AS `부서별 인원` FROM emp GROUP BY deptno WITH ROLLUP;

-- 2.2 계정 생성
/**
계정명 : testUser
비밀번호 : 12345
연결위치 : localhost
*/
CREATE USER testUSer@'localhost' IDENTIFIED BY '12345';

SELECT * FROM mysql.USER WHERE user = 'testUSer';

-- 2.3 권한 부여
GRANT SELECT, INSERT, UPDATE, DELETE on test_db.* TO testUSer@'localhost';

SHOW GRANTS FOR testUSer@'localhost';
-- 계정 삭제
DROP USER testUSer@'localhost';

SELECT * FROM mysql.user WHERE user= 'testUSer';

/**
1-1. 저장 프로시저 생성
(emp) table과 (dept) table을 이용하여 급여가 2000 이상인 사원의 사원번호, 이름, 부서명을
부서 순으로 검색하는 프로시저를 작성하시오.
*/

DELIMITER //
CREATE PROCEDURE test_procedure()
BEGIN
SELECT empno, ename, dname FROM emp, dept
    WHERE emp.deptno = dept.deptno AND emp.sal >= 2000
    ORDER BY emp.deptno ASC;
END //
DELIMITER ;

CALL test_procedure();

/****
1-2. (emp) table 사원번호와 부서번호를 정수값으로 넘겨받아 사원번호로 사원의 정보를 찾아
부서번호를 변경하는 프로시저를 작성하시오.
- ex 사원 번호가 7369인 사원의 부서번호를 10으로 변경하는 저장 프로시저
*/
DROP PROCEDURE IF EXISTS test_update;
DELIMITER $$
CREATE PROCEDURE test_update(IN vEmpno INT, IN vDeptno INT)
BEGIN
UPDATE emp SET deptno = vDeptno WHERE empno = vEmpno;
END $$
DELIMITER ;

SELECT * FROM emp WHERE empno = 7369;
CALL test_update(7369,10);
/**
1-3. 트리거
- (emp) emp과 구조만 동일한 back_up_emp 테이블 생성(data는 존재하지 않음)
- emp 테이블에서 사원정보가 삭제되고 난 후 back_up_emp table에
   삭제된 정보를 추가하는 트리거를 작성하시오.
*/
-- back_up 테이블 생성
CREATE TABLE back_up_emp AS (SELECT * FROM emp WHERE 1=0);
CREATE TABLE back_up_emp LIKE emp;

SELECT * FROM back_up_emp;
DESC back_up_emp;

-- TRIGGER 삭제
DROP TRIGGER IF EXISTS delete_emp_trg;

DELIMITER //
CREATE TRIGGER delete_emp_trg -- 트리거 이름
AFTER DELETE
ON emp FOR EACH ROW
BEGIN
-- 실행될 쿼리
    -- back_up_emp
    -- NEW , OLD
    INSERT INTO back_up_emp VALUES(
OLD.empno, OLD.ename, OLD.job, OLD.mgr, OLD.hiredate, OLD.sal, OLD.comm, OLD.deptno
    );
END //
DELIMITER ;

SELECT * FROM emp WHERE empno = 7369;

SELECT * FROM back_up_emp;

DELETE FROM emp WHERE empno = 7369;

rollback;

-- 2.1 인덱스 생성
CREATE INDEX idx_emp_ename ON emp(ename);
ALTER TABLE emp ADD INDEX inx_emp_ename(ename);

-- 2.2 인덱스 확인
SHOW INDEXES FROM test_db.emp;

-- 2.3 인덱스 삭제
DROP INDEX inx_emp_ename ON test_db.emp;
ALTER TABLE test_db.emp DROP INDEX idx_emp_ename;

/*******************************
SQL Injection -- (SQL 삽입 공격)
사용자가 입력할 수 있는 영역을 활용해서
    개발자가 의도하지 않은 SQL문을 실행 하게 하는 공격 방법
*/
USE sqlDB;
show tables;
SELECT * FROM member_tbl;
-- id001
-- pw001
-- id001'; --
SELECT * FROM member_tbl WHERE id = 'id001'; -- ' AND pw = '';
-- ' OR '1' = '1
SELECT * FROM member_tbl WHERE id = 'id001' AND pw = '' OR '1' = '1';

PREPARE
mQuery
FROM "SELECT * FROM member_tbl WHERE id = ? AND pw = ?";

SET @id = 'id001'; -- ';
SET @pw = '' OR '1' = '1';

SELECT @id, @pw;
EXECUTE mQuery USING @id, @pw;

내가 한 풀이

DESC EMP;
INSERT INTO emp VALUES(8000,'MASTER','SALESMAN',7839,'1982-06-07',1200,500,40);

UPDATE emp SET comm = 600 WHERE empno = 7566;
DELETE FROM emp WHERE empno = 8000;

SET @@autocommit :=0;
select @@autocommit;

DELETE FROM emp;
SELECT * FROM emp;
SELECT * FROM dept;
rollback;
SELECT * FROM emp;

SELECT deptno, count(*) AS '부서별인원' FROM emp GROUP BY deptno WITH ROLLUP;

CREATE USER testUser@localhost IDENTIFIED BY '12345';
SELECT * FROM mysql.USER WHERE user = 'testUser';

GRANT SELECT, INSERT, UPDATE, DELETE ON test_db.* TO testUser@'localhost';
SHOW GRANTS FOR testUser@'localhost';

DELIMITER $$
CREATE PROCEDURE test_procedure()
BEGIN
SELECT E.empno,E.ename, D.dname FROM emp E JOIN dept D
        USING (deptno) WHERE E.sal >=2000;
    END $$
DELIMITER ;

call test_procedure();
SELECT * FROM emp;
DESC emp;

DELIMITER $$
CREATE PROCEDURE test_update(
IN _empno INT,
    IN _deptno INT)
BEGIN
DECLARE result INT;
    SET result = (SELECT deptno FROM emp WHERE empno = _empno);
    IF(result IS NOT NULL)
    THEN
UPDATE emp SET deptno = _deptno WHERE empno = _empno;
    END IF;
END $$
DELIMITER ;

call test_update(7369,10);
select * from emp where empno=7369;

CREATE TABLE back_up_emp LIKE emp;
SELECT * FROM back_up_emp;

DELIMITER $$
CREATE TRIGGER backup_trg
    AFTER DELETE
    on emp FOR EACH ROW
    BEGIN
        INSERT INTO back_up_emp
        VALUES(OLD.empno,OLD.ename,OLD.job,OLD.mgr,OLD.hiredate,OLD.sal,OLD.comm,OLD.deptno);
    END $$
DELIMITER ;

commit;

DELETE FROM emp WHERE empno = 7369;
SELECT * FROM back_up_emp;
SELECT * FROM emp;

SELECT * FROM emp;

CREATE INDEX index_emp_ename ON emp(ename);
DROP INDEX index_emp_ename ON test_db.emp;

SHOW INDEXES FROM emp;