[게임 데이터 베이스] 수업 내용 정리

• 테이블 인덱스 삽입 , 읽기, 업데이트, 삭제

use exam11_db;
use import_exam11_db;
show tables;
desc user;
desc score;


#테이블 인덱스 삽입 , 읽기, 업데이트, 삭제
drop table if exists user;
#create
insert into user (id,nickname) values ("test2",'김동준');
#read
select * from user;
#update
update user set nickname = "김동준" where id ='test2' ;
#delete
delete from user where id = 'test1';

 

• 뷰 만들기  (inner join 활용)

select U.id , U.nickname, S.score from user U
inner join score S
on U.id = S.user_id;
# id, nickname,score

drop view if exists viewscore;
create view viewscore as
select U.id, U.nickname,S.score from user U
inner join score S
on U.id = S.user_id;

select * from viewscore
order by score;

 

• 프로시저 선언 및 사용

DROP PROCEDURE IF EXISTS rankUserProc;
DELIMITER //

CREATE PROCEDURE rankUserProc(IN count INT)
BEGIN
    SELECT * FROM viewscore
    ORDER BY score DESC
    LIMIT count;
END //

DELIMITER ;

CALL rankUserProc(2);

 

• 함수 선언 및 사용

DROP FUNCTION IF EXISTS getRankByid;
DESCRIBE `user`;
DELIMITER //
CREATE FUNCTION getRankByid(userid varchar(45)) RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE rtn_rank_no INT;
    
    SELECT rank_no 
    INTO rtn_rank_no
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS rank_no, id, nickname, score
        FROM viewscore
    ) AS ranked
    WHERE id = userid;
    
    RETURN rtn_rank_no;
END //

DELIMITER ;

select getRankByid('test1S');

drop procedure if exists restoreUserFromBackupProc;
delimiter //
create procedure restoreUserFromBackupProc($id varchar(25))
begin
insert into user (id , nickname , date)
	(select id, nickname , current_timestamp() from backup_user
    where id = $id );
    
    delete from backup_user where id = $id;
end //
delimiter ;

select * from backup_user;
select * from user;
call restoreUserFromBackupProc('test1');

 

• 테이블 복사 및 트리거 선언

DESC `user`;

-- 테이블 만들기
CREATE TABLE backup_user LIKE `user`;
select * from backup_user;
DESC backup_user;

-- 테이블 구조 복사
INSERT INTO backup_user SELECT * FROM `user` LIMIT 4;

-- 트리거 만들기
DROP TRIGGER IF EXISTS triggerDeleteAfterUser;
DELIMITER //

CREATE TRIGGER triggerDeleteAfterUser
AFTER DELETE ON `user`
FOR EACH ROW
BEGIN
    INSERT INTO backup_user (id, nickname, date) VALUES (OLD.id, OLD.nickname, CURRENT_TIMESTAMP());
END //

DELIMITER ;

select * from user;
select * from backup_user;
delete from user where id = 'test1';