MySql) 쿼리 활용

1. 스키마 생성

 

2. 테이블 생성

 

3. db, 테이블 조회

USE exam11_db;
SHOW TABLES;
DESC user;
DESC score;

 

4. 기본 CRUD 작성

# User
# Create
INSERT INTO user(id, nickname) VALUES ('hsh', 'HSH');
INSERT INTO user(id, nickname) VALUES ('lkj', '임꺽정');
INSERT INTO user(id, nickname) VALUES ('hkd', '홍길동');

# Read
SELECT * FROM user;

# Update
UPDATE user SET nickname = '임꺽정' WHERE id = 'hsh';

# Delete
DELETE FROM user WHERE id = 'hsh';

# Score
DESC score;

# Create
INSERT INTO score(user_id, score) VALUES ('hkd', 5000);

# Read
SELECT * FROM score;

# Update
UPDATE score SET score = 1400, date = CURRENT_TIMESTAMP() WHERE user_id = 'hsh' LIMIT 1;

# Delete
DELETE FROM score WHERE user_id = 'hsh' LIMIT 1;

 

5. inner join으로 두 테이블에서 일치하는 값 조회

SELECT * FROM user INNER JOIN score ON user.id = score.user_id;

 

6. 가상 테이블 생성, 조회

DROP VIEW IF EXISTS viewScore;

CREATE VIEW viewScore AS
SELECT user.id, user.nickname, score.score, score.date
FROM user
INNER JOIN score ON user.id = score.user_id;

SELECT * FROM viewScore
ORDER BY score DESC;

SELECT * FROM viewScore;

 

7. 프로시져 생성, 호출

DROP PROCEDURE IF EXISTS rankUserProc;

DELIMITER //
CREATE PROCEDURE rankUserProc(count INT)
BEGIN 
	DECLARE cnt INT;
    SET cnt = count;
	SELECT * FROM viewScore
ORDER BY score DESC LIMIT cnt;
END //
DELIMITER ;

CALL rankUserProc(3);

 

 

8. 함수 생성, 호출

DROP FUNCTION IF EXISTS getRankById;

DELIMITER //
CREATE FUNCTION getRankById(userId VARCHAR(45)) RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE rank_no INT;
    
    SET rank_no = (
        SELECT COUNT(*) + 1
        FROM (
            SELECT id, score
            FROM viewScore
            ORDER BY score DESC
        ) AS sorted
        WHERE sorted.score > (SELECT score FROM viewScore WHERE id = userId ORDER BY score DESC LIMIT 1)
            AND sorted.id <> userId
    );
    
    RETURN rank_no;
END //
DELIMITER ;

SELECT getRankById('hsh');

SELECT
    getRankById(id) AS rank_no,
    id,
    nickname,
    score
FROM
    viewScore
ORDER BY
    score DESC;

 

 

9. 트리거, 트리거 사용 백업 테이블 생성, 적용

# 테이블 만들기
CREATE TABLE backup_user LIKE user;
DESC backup_user;
SELECT * FROM backup_user;

# 트리거 만들기
DROP TRIGGER IF EXISTS triggerDeleteAfterUser;

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

SELECT * FROM user;
SELECT * FROM backup_user;
DELETE FROM user WHERE id = 'hsh';

DROP PROCEDURE IF EXISTS restoreUserFromBackupProc;

DROP PROCEDURE IF EXISTS restoreUserFromBackupProc;
DELIMITER //
CREATE PROCEDURE restoreUserFromBackupProc(IN userId VARCHAR(45))
BEGIN
    INSERT INTO user (id, nickname)
    SELECT id, nickname
    FROM backup_user
    WHERE id = userId;
    
    DELETE FROM backup_user
    WHERE id = userId;
END //
DELIMITER ;

CALL restoreUserFromBackupProc('hsh');