📅 2023년 11월 22일

[DB 5일차]

7일차: index, 사용자 관리, view, procedure, dump

Index :

색인과 목차처럼 데이터 검색 속도를 향상시키는데 사용
(조회 성능은 좋아지는데 삽입 성능은 떨어진다.)
(카디널리티가 높은 테이블에 인덱스를 걸어야 효율적이다.)

-- 단일 컬럼 index
CREATE INDEX author_name ON author(name);

-- 복합 컬럼 index
CREATE INDEX author_index ON author(name, email);

--  index 조회
show index from 테이블명;

조회시 where 조건에 index 컬럼을 조건으로 걸어줘야 index를 활용하여 검색이 이루어짐

사용자 관리 : 유저 생성, 권한 추가

SELECT User, Host FROM mysql.user;	-- 유저 검색

SHOW GRANTS FOR 'root'@'localhost';
-- GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
-- GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING '*A4B6157319038724E3560894F7F932C8886EBFCF' OR gssapi USING 'SID:BA' WITH GRANT OPTION

CREATE USER 'testuser'@'localhost' IDENTIFIED BY '1234';	-- 유저 생성

SHOW GRANTS FOR 'testuser'@'localhost';		-- 유저 확인
-- GRANT USAGE ON *.* TO `testuser`@`localhost` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF'

GRANT INSERT ON board.* TO 'testuser'@'localhost';		-- 유저 insert 권한 추가

flush privileges;	-- 적용 (깜박하지 말것)

View :

테이블과 유사한 구조를 가지지만,
가상의 테이블 로서 실제 데이터를 저장하지 않는다. 실제 데이터베이스를 참조만 한다.

-- view 생성
CREATE VIEW author_view AS 
SELECT * FROM author;

CREATE VIEW posts_view AS 
SELECT * FROM posts;

-- view 조회
SELECT * FROM author_view;

-- view에 권한 주기
GRANT SELECT ON board.author_view TO 'testuser'@'localhost';

procedure :

DB에 저장되어 실행될 수 있는 하나 이상의 SQL 집합
SQL 문을 미리 컴파일하여 저장함으로써 서버 부하를 줄이고 성능 향상
프로시저의 목적은 재사용하기 위함이다.

DELIMITER //
-- 파라미터는 생략가능하고 함수와 같이 파라미터를 전달하여 실행하는 것도 가능 
-- 기본 형식은  (IN 변수명 변수타입)
CREATE PROCEDURE MY_PROCEDURE(
	IN IN_auth_id int	-- 매개변수
)
BEGIN 
DECLARE AVG_price  INT DEFAULT 0;		-- 변수선언
	SELECT AVG(price)	
	INTO AVG_price		-- 변수에 select 값 입력
	FROM posts
	WHERE auth_id = IN_auth_id;

	IF AVG_price > 2000 THEN	-- 분기
		SELECT '고액 원고료 작가입니다.' AS MESSAGE;
	ELSE
		SELECT '고액 원고료 작가가 아닙니다.' AS MESSAGE;
	END IF ;
END  //
DELIMITER ;

CALL MY_PROCEDURE(2)	// 호출

DELIMITER //
CREATE PROCEDURE INSERT_100(
    IN auth_id int
)
BEGIN 
DECLARE A INT DEFAULT 0;	-- 변수 선언
WHILE A<100 DO	-- 반복문
    INSERT INTO
	posts(auth_id, title) values(auth_id, CONCAT('WELLO WORLD', A));
    SET A = A+1;	-- 변수 수정
END WHILE;
END  //
DELIMITER ;

CALL INSERT_100(1); 

dump :

데이터베이스의 구조와 데이터를 sql 형식으로 추출하는데 사용되는 방식

-- 덤프 생성 명령어 (utf8 설정)
mysqldump -u root -p --default-character-set=utf8mb4 board > dumpfile.sql

-- 덤프 입력 명령어 
mysql -u root -p board < dumpfile.sql

github 데이터베이스 실습 내용