국비학원 27일차 : SQL_6

Board ERD

중복제거한 데이터 값들

유저 : 이메일 주소, 비밀번호, #비밀번호 확인, 닉네임, 휴대전화번호, 주소, 상세주소, 개인정보동의여부, 프로필사진
→ 비밀번호 확인은 굳이 관리할 필요없다, 개인정보처리동의는 법적으로는 동의확인에대한 결과를 갖고있어야한다.

게시물 : 게시물번호, 게시물 사진,작성자이메일,작성날짜, 제목, 내용, 조회수,
#작성자 프로필사진(데이터 중복해서 갖고있는것, 유저에대한 관계를 가진다),
#작성자 닉네임(데이터 중복해서 갖고있는것, 유저에대한 관계를 가진다),
#좋아요수(데이터 중복해서 갖고있는것, 좋아요리스트의 갯수로 알수있다), #댓글수(데이터 중복해서 갖고있는것, 댓글리스트의 갯수로 알수있다?))

게시물 작성 BOARD(title, content, board_image_url, write_datetime , writer_email)
게시물 수정

검색로그 : (로그시퀀스, 검색어, 관련검색어)

좋아요리스트 : 유저이메일(관계), 게시물번호(관계),
#유저프로필사진(유저이메일로 설명이 가능하니까 뺄수 있다),
#유저닉네임(유저이메일로 설명이 가능하니까 뺄수 있다)

댓글리스트 : 댓글번호, 유저이메일(관계), 게시물번호(관계), 작성시간, 댓글내용
#유저프로필사진(유저이메일로 설명이 가능하니까 뺄수 있다),
#유저닉네임(유저이메일로 설명이 가능하니까 뺄수 있다)

추려냈으면 중복을 허용할지 안할건지에 대한 결정을 해야한다.
무결성을 유지하려면 중복을 지우는게 맞다. 유저에서 꺼내올수 있기때문에 문제는 없다. 좋아요수와 댓글수도 count로 가져올수 있다.

중복허용안해서 빼게 되면 실시간으로 따로 지정하지 않는이상 바뀌지 않게된다. 중복허용을 하게 되면 게시물 리스트를 가져올때 몇개의 테이블을 조인해야 하는것도 복잡해지게 된다.

업뎃이 자주자주 일어난다 → 조인을 시킨다, 매번 신뢰도 있는 데이터를 가져올수있다.
업데이트가 자주 필요하지않다 → 레코드로 추가해두는게 좋다

정규화, 역정규화 관련해서 데이터의 관리 필요.

검색할때 편하게 하고 신뢰성을(삽입,수정,삭제에 대한) 높일수 있는 방법
→ 뷰를 활용하는 방법도 있다 (속도가 증가하는건 아니고, 쿼리를 한번 더 짤 필요가 없다는것)
검색을 위한 가상의 테이블을 쿼리로 먼저 짜놓고, 변동이 발생하면 알아서 신뢰성 있는 데이터를 구축할수있다.
검색속도 증가시키려면 중복을 허용해서 레코드를 넣고, 신뢰성을 증가하려면 중복을 허용하지 않으면 된다.

원래는 user_email과 board_number에 PK가 설정되어 있었다. 이렇게 설정이 되어있으면, NOT NULL과 UNIQUE가 설정되므로 유저A가 게시물 1번에 댓글을 쓰면 더이상 중복으로 댓글을 달 수 없게 된다. 이런 문제를 없애기 위해 둘 모두 PK를 해제해줘야한다! → 모두 해제를 하게 되면 Comment의 테이블에는 구분자가 없게된다. 임의의 구분 가능하게 하는 구분자가 필요하다! comment_number칼럼을 추가해준다.

Board SQL

USE d_board;

-- 1️⃣회원가입 
-- 삽입작업

INSERT INTO USER (email, password, nickname, tel_number, address, address_detail, consent_personal_information)
VALUES ('qwer@gamil.com', 'P!ssw0rd', 'digeut' ,'010-1111-2222', '부산광역시', '진구', true);

-- 2️⃣로그인 
-- 패스워드 암호화가 되어있지 않을때, 동일한 값이 있는지 확인하는 작업
SELECT * FROM USER
WHERE email = 'qwer@gamil.com'
AND password = 'P!ssw0rd';

-- 패스워드가 암호화 되어있을때는 이메일만 가져와서 해당하는 
-- 비번찾은다음 동일한 패턴으로 된 암호화인지 확인할수있다
-- sha2('P!ssw0rd', 512) 이렇게 암호화할수있다.
SELECT * FROM USER
WHERE email = 'qwer@gamil.com';

-- 3️⃣게시물 작성
INSERT INTO BOARD(title, content, board_image_url, write_datetime , writer_email)
VALUES ('Test Title', 'Test Content', null, now(), 'qwer@gamil.com');

-- 4️⃣게시물 보기(상세페이지)
-- 1. 게시물 (게시물 제목, 작성자이메일, 작성자프로필사진, 작성자 닉네임, 작성일, 내용, 게시물사진, 좋아요수, 댓글수, [좋아요 리스트, 댓글 리스트])
-- 2. 좋아요리스트 (유저 프로필사진, 유저 닉네임)
-- 3. 댓글리스트 (유저프로필사진, 유저닉네임, 작성시간, 댓글내용)
SELECT *
FROM Board B , User U, (
SELECT B.board_number AS board_number, count(L.user_email) AS like_count
FROM Board B
LEFT JOIN Liky L
ON B.board_number = L.board_number
WHERE B.board_number = 1
GROUP BY B.board_number) LC, (
SELECT B.board_number AS board_number, count(C.user_email) AS comment_count
FROM Board B
LEFT JOIN Comment C
ON B.board_number = C.board_number
WHERE B.board_number = 1
GROUP BY board_number
) CC
WHERE B.board_number = 1
AND B.writer_email = U.email #로그인한 유저가 작성한 글인지 확인
AND B.board_number = LC.board_number
AND B.board_number = CC.board_number;

-- AND B.writer_email = U.email 
-- AND B.board_number = L.board_number # 좋아요 달린 글번호와 게시글의 번호가 같은지
-- AND B.board_number = C.board_number; # 댓들 달린 글번호와 게시글의 번호가 같은지
-- 이너조인하면 댓글이랑 좋아요에 데이터 없으면 안나온다..? 좋아요, 댓글 리스트 수 만든걸로 이너조인해야한다

-- 좋아요리스트 수
SELECT B.board_number AS board_number, count(L.user_email) AS like_count
FROM Board B
LEFT JOIN Liky L
ON B.board_number = L.board_number
WHERE B.board_number = 1
GROUP BY B.board_number;

-- 댓글리스트 수
SELECT B.board_number AS board_number, count(C.user_email) AS comment_count
FROM Board B
LEFT JOIN Comment C
ON B.board_number = C.board_number
WHERE B.board_number = 1
GROUP BY board_number;

-- 5️⃣좋아요리스트
SELECT * 
FROM Liky L, User U
WHERE L.user_email = U.email
AND L.board_number = 1 ;

-- 6️⃣댓글리스트
SELECT *
FROM Comment C, User U
WHERE C.user_email = U.email
AND C.board_number = 1;

-- 뷰 미리 생성
CREATE VIEW Board_View AS
SELECT 
	B.board_number AS board_number,
    B.title AS title,
    B.content AS content,
    B.write_datetime AS write_datetime,
    B.board_image_url AS board_image_url,
    U.email AS writer_email,
    U.nickname AS writer_nickname,
    U.profile_url AS writer_profile_url,
    LC.like_count AS like_count,
    CC.comment_count AS comment_count
FROM Board B , User U, (
SELECT B.board_number AS board_number, count(L.user_email) AS like_count
FROM Board B
LEFT JOIN Liky L
ON B.board_number = L.board_number
GROUP BY B.board_number) LC, (
SELECT B.board_number AS board_number, count(C.user_email) AS comment_count
FROM Board B
LEFT JOIN Comment C
ON B.board_number = C.board_number

GROUP BY board_number
) CC
WHERE B.writer_email = U.email #로그인한 유저가 작성한 글인지 확인
AND B.board_number = LC.board_number
AND B.board_number = CC.board_number;

SELECT * FROM Board_View
WHERE board_number = 1 ; # 조건을 나중에 따로 설정해서 검색하기 편하게

-- 7️⃣최신게시물 리스트 불러오기
SELECT * FROM Board_View
ORDER BY write_datetime DESC;

-- 8️⃣주간 탑3 리스트
SELECT * FROM Board_View
WHERE write_datetime >= '2023-03-27'
ORDER BY like_count DESC
LIMIT 3; #위에서 3개까지 보여달라고 지정

-- 9️⃣검색어 리스트 
SELECT * FROM Board_View
WHERE title LIKE '%Title%'
OR content LIKE '%Title%';

-- 🔟내 게시물 리스트
SELECT * FROM Board_View
WHERE writer_email = 'qwer@gamil.com';

-- 1️⃣1️⃣좋아요 누르는것
INSERT INTO Liky(user_email, board_number)
VALUES ('qwer@gamil.com',1);

-- 1️⃣2️⃣좋아요 취소
DELETE FROM Liky
WHERE user_email = 'qwer@gamil.com'
AND board_number = 1;

-- 1️⃣3️⃣댓글 작성
INSERT INTO Comment (user_email, board_number, comment_content, write_datetime)
VALUES ('qwer@gamil.com', 1, 'Test Comment', now());

-- 1️⃣4️⃣게시물 수정
UPDATE Board 
SET title = 'Modified Title', content = 'Modified Content', board_image_url = 'http://~~'
WHERE board_number = 1 ;

-- 1️⃣5️⃣게시물 삭제

DELETE FROM Comment
WHERE board_number = 1; # 외래키 제약조건 걸려있어서 하나하나 다 지운다음 게시물 지우는 방법

DELETE FROM Liky
WHERE board_number = 1; # 외래키 제약조건 걸려있어서 하나하나 다 지운다음 게시물 지우는 방법

DELETE FROM Board
WHERE board_number = 1;
-- cascade 쓰면 위 처럼 하나하나 다 지우고 지우는게 아니고 한번에 다 지울수 있다.

-- 1️⃣6️⃣검색로그 그냥 누적되는 값 카운트에 대한 값을 직접 가져와야한다
INSERT INTO Search_Log(serach_word)
VALUES ('Title');
INSERT INTO Search_Log(serach_word, related_search_word)
VALUES ('modify', 'Title');

-- 1️⃣7️⃣인기검색어
SELECT Search_word, count(Search_word) AS count
FROM Search_Log
GROUP BY search_word
ORDER BY count DESC
LIMIT 10; 

-- 1️⃣8️⃣연관검색어
SELECT related_search_word, count(related_search_word) AS count
FROM Search_Log
WHERE search_word = 'Modify'
GROUP BY related_search_word
ORDER BY count DESC
LIMIT 10;

DCL

data control language
권한 제어 및 트랜잭션 제어를 담당하는 쿼리문의 집합

접근통제

사용자 권한 오브젝트를 조작하는 쿼리문. DBMS에 접근할 사용자를 등록하고, 특정 사용자에게 데이터베이스의 사용 권한을 부여하는 작업을 담당한다.

GRANT / REVOKE

-- 사용자권한
CREATE USER 'Developer_user'@'%' IDENTIFIED BY 'P!ssw0rd';
-- 메인화면에서 계정이름과 비번입력해서 접속시도해볼수있다.

-- Grant
-- 사용자에게 권한을 부여하는 쿼리문
-- GRANT 권한1, ... ON 데이터베이스.테이블 TO 사용자이름@호스트;
GRANT CREATE, INSERT, UPDATE, SELECT, DELETE
ON HOTEL.* TO 'Developer_user'@'%';
# 호텔 데이터베이스의 모든 데이터에 대해서 
# 생성, 삽입, 수정, 선택, 삭제에 대한 권한을 줌

-- Revoke
-- 사용자로부터 권한을 회수하는 쿼리문
-- REVOKE 권한1,... ON 데이터베이스.테이블 FROM 사용자이름@호스트;

REVOKE CREATE ON HOTEL.* FROM 'Developer_user'@'%';

트랜잭션 통제

-- COMMIT
-- 트랜잭션을 영구적으로 반영하는 쿼리문
COMMIT; 

-- ROLLBACK
-- 트랜잭션을 취소하고 되돌려놓는 쿼리문
ROLLBACK;

트랜잭션(Tranjection)

Chat GPT설명

데이터베이스의 트랜잭션은 단일 작업 단위로 실행되는 일련의 작업입니다. 트랜잭션은 일반적으로 일련의 데이터베이스 작업을 하나의 단위로 그룹화하여 모든 작업이 성공적으로 완료되거나 완료된 작업이 전혀 없는 상태로 데이터베이스에서 데이터 일관성과 무결성을 보장하는 데 사용됩니다.

ACID라는 약어는 종종 데이터의 일관성과 무결성을 보장하기 위해 트랜잭션이 필요한 속성을 설명하는 데 사용됩니다. 다음 속성이 있습니다:

원자성(Atomicity) : 트랜잭션은 원자형이어야 하며, 이는 트랜잭션이 전부 또는 전혀 아니어야 함을 의미합니다. 트랜잭션 내의 모든 작업이 성공적으로 완료되어야 하거나 완료되지 않아야 합니다.
일관성(Consistency) : 트랜잭션은 데이터베이스의 일관성을 유지해야 합니다. 즉, 데이터베이스는 트랜잭션 전후에 모두 유효한 상태여야 합니다.
독립성 (Isolation) : 트랜잭션은 동시에 실행되는 다른 트랜잭션과 독립적이어야 합니다. 즉, 첫 번째 트랜잭션이 완료될 때까지 한 트랜잭션의 효과가 다른 트랜잭션에 표시되지 않아야 합니다.
영구성 (Durability) : 트랜잭션이 commit되면 트랜잭션의 영향은 영구적이어야 하며 시스템 장애에서도 살아남아야 합니다.
이러한 속성을 보장하기 위해 트랜잭션은 일반적으로 다음 명령을 사용합니다

BEGIN TRANSACTION : 이 명령은 트랜잭션을 시작합니다.
COMMIT : 이 명령은 트랜잭션을 커밋하여 트랜잭션의 효과를 영구적으로 만듭니다.
ROLLBACK : 이 명령은 트랜잭션을 롤백하여 효과를 취소하고 데이터베이스를 트랜잭션이 시작되기 전의 상태로 되돌립니다.

요약하면 트랜잭션은 데이터베이스에서 데이터의 일관성과 무결성을 보장하기 위해 단일 작업 단위로 실행되는 일련의 작업입니다.

UPDATE와 ALTER의 차이점

UPDATE

UPDATE 명령은 테이블의 기존 레코드를 수정하거나 업데이트하는 데 사용됩니다. 테이블의 하나 이상 행에 있는 하나 이상의 열 값을 변경합니다.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

여기서 table_name은 테이블의 이름이고, column1과 column2는 업데이트해야 할 열의 이름이며, value1과 value2는 해당 열에 설정할 새 값이며, 조건은 업데이트할 행을 지정합니다. WHERE 절이 생략되면 테이블의 모든 행이 업데이트됩니다.

ALTER

ALTER 명령은 열 추가 또는 삭제, 열의 데이터 유형 변경 또는 테이블 이름 변경과 같은 기존 테이블의 구조를 수정하는 데 사용됩니다.

ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
ALTER TABLE table_name
RENAME TO new_table_name;

여기서 table_name은 수정해야 하는 테이블의 이름이고, columnn_name은 추가하거나 삭제할 열의 이름이고, datatype은 열의 데이터 유형이며, new_table_name은 이름을 바꾼 후 테이블의 새 이름입니다.

UPDATE는 테이블 내의 데이터를 수정하는 데 사용되는 반면 ALTER는 테이블 자체의 구조를 수정하는 데 사용됩니다.