[20230802] 클라우드 플랫폼 기반 웹서비스 개발자 양성 과정 19일차.

CHECK : 지정한 컬럼에 들어갈 데이터를 제한하는 제약조건

• INSERT, UPDATE에서 영향을 미친다.

CREATE TABLE sample_table_5 (
	column1 INT,
    check_column INT CHECK (check_column > 0 AND check_column < 10) 
);

INSERT INTO sample_table_5 VALUES (1, 0);
INSERT INTO sample_table_5 VALUES (1, 10);
INSERT INTO sample_table_5 VALUES (1, 4);

UPDATE sample_table_5 SET check_column = 100;

DEFAULT : 해당 컬럼에 값을 지정하지 않으면 기본값으로 지정해주는 제약조건

• INSERT에서 영향을 미친다.

CREATE TABLE sample_table_6 (
	column1 INT,
    default_column INT DEFAULT 10
); 

INSERT INTO sample_table_6 (column1) VALUES (1);
INSERT INTO sample_table_6 VALUES (1, null);
SELECT * FROM sample_table_6;

SQL 고급

별칠 Alias

• 컬럼이나 테이블명을 다른 이름으로 표현하고자 할때 사용한다.
• 컬럼 혹은 테이블명이 길거나, 더 의미있는 이름을 부여하고 싶거나,
• 다른 테이블간의 연결을 할때, 혹은 같은 테이블을 여러번 참조할때 사용한다.

USE human_resource;
SELECT 업소명 AS 가게명 FROM bbq;
SELECT 업소명 AS 가게명 FROM bbq AS 치킨사업테이블;

• 테이블 다시 만들기

DROP TABLE employee;

CREATE TABLE department (
department_name VARCHAR(100) NOT NULL UNIQUE,
department_tel_number VARCHAR(15) NOT NULL UNIQUE,
department_address TEXT NOT NULL,
PRIMARY KEY (department_name)
);

CREATE TABLE employee (
employee_number VARCHAR(10) NOT NULL UNIQUE,
name VARCHAR(10) NOT NULL,
address TEXT NOT NULL,
department_name VARCHAR(100),
PRIMARY KEY (employee_number),
FOREIGN KEY (department_name) REFERENCES department (department_name)
);

INSERT INTO department
VALUES ('기획부', '051-111-1111', '부산광역시');
INSERT INTO department
VALUES ('영업부', '051-222-1111', '부산광역시');
INSERT INTO department
VALUES ('연구소', '051-333-1111', '부산광역시');
INSERT INTO department
VALUES ('인사부', '051-444-1111', '부산광역시');
INSERT INTO department
VALUES ('개발부', '051-555-1111', '부산광역시');
INSERT INTO department
VALUES ('재무부', '051-666-1111', '부산광역시');

INSERT INTO employee
VALUES ('3015', '양희정', '부산광역시', '인사부');
INSERT INTO employee
VALUES ('3032', '여상길', '부산광역시', '연구소');
INSERT INTO employee
VALUES ('3076', '김경훈', '부산광역시', '영업부');
INSERT INTO employee
VALUES ('1687', '조창석', '부산광역시', '개발부');
INSERT INTO employee
VALUES ('3103', '유정봉', '부산광역시', '기획부');
INSERT INTO employee
VALUES ('3110', '이수광', '부산광역시', '인사부');
INSERT INTO employee
VALUES ('3133', '박진우', '부산광역시', '연구소');
INSERT INTO employee
VALUES ('3138', '이수경', '부산광역시', '기획부');
INSERT INTO employee
VALUES ('3141', '이지훈', '부산광역시', '영업부');
INSERT INTO employee
VALUES ('3151', '최지선', '부산광역시', '개발부');
INSERT INTO employee
VALUES ('2894', '정아린', '부산광역시', null);
INSERT INTO employee
VALUES ('2939', '김정민', '부산광역시', '연구소'); #했음
INSERT INTO employee
VALUES ('2374', '김유정', '부산광역시', '기획부');
INSERT INTO employee
VALUES ('2861', '정이제', '부산광역시', '개발부');
INSERT INTO employee
VALUES ('3263', '이소진', '부산광역시', null);
INSERT INTO employee
VALUES ('0000', '서지훈', '부산광역시', null);

SELECT FROM department;
SELECT FROM employee;

> JOIN : 두 개 이상의 테이블을 조합하여 검색 결과를 얻고자 할때 사용한다.

- INNER, LEFT, RIGHT, FULL
- INNER JOIN : 첫 번째 테이블과 두 번째 테이블의 데이터 중 조건에 일치하는 데이터만 조합한다. 
- SELECT 컬럼명, ... FROM 첫번째테이블 INNER JOIN 두번째테이블 ON 조건; 
```sql
SELECT 
	E.employee_number AS '사원번호', 
    E.name AS '사원이름', 
    D.department_name AS '부서이름', 
    D.department_tel_number AS '전화번호'
FROM department AS D
INNER JOIN employee AS E
ON D.department_name = E.department_name;

• LEFT JOIN : 첫번째 테이블에 있는 모든 데이터와 두번째 테이블에서 조건에 부합하는 데이터만을 조합한다.
• 매칭되는 테이블이 없다면 null로 표현된다.
• SELECT 컬럼명, ... FROM 첫번째테이블 LEFT JOIN 두번째테이블 ON 조건;

SELECT *
FROM department LEFT JOIN employee
ON department.department_name = employee.department_name;

• RIGHT JOIN : 두번째 테이블에 있는 모든 데이터와 첫번째 테이블에서 조건에 부합하는 데이터만 조합한다.
• SELECT 컬럼, ... FROM 첫번째테이블 RIGHT JOIN 두번째테이블 ON 조건;

SELECT *
FROM department RIGHT JOIN employee
ON department.department_name = employee.department_name;

BOARO-DML

USE sample_board_2;

• 회원가입 SQL

INSERT INTO user (email, password, nickname, tel_number, address, address_detail, agreed_personal)
VALUES ('email3@email.com', 'p!ssw0rd', 'nickname3', '01011111113', '부산광역시 부산진구', '에이원프라자', true);

• 로그인 SQL

SELECT * FROM user
WHERE email = 'email@email.com' AND password = 'p!ssw0rd';

• 게시물 쓰기 SQL

INSERT INTO board(title, contents, writer_email)
VALUES ('제목5', '내용5', 'email@email.com');

• 게시물 수정 SQL

UPDATE board SET title = '수정 제목', contents = '수정 내용' WHERE board_number = 1;

• 게시물 삭제 SQL

DELETE FROM board WHERE board_number = 1;

• 댓글 작성 SQL

INSERT INTO comment (board_number, user_email, content)
VALUES (2, 'email@email.com', '안녕하세요'); 

• 좋아요 등록 SQL

INSERT INTO favorite VALUES (2, 'email@email.com');

• 좋아요 취소 SQL

DELETE FROM favorite 
WHERE board_number = 2 AND user_email = 'email@email.com';

• 최신게시물 불러오기 SQL

SELECT 
	B.board_number,
    B.title,
    B.contents,
    B.write_datetime,
    B.view_count,
    U.nickname,
    U.profile_image
FROM board AS B INNER JOIN user AS U
ON B.writer_email = U.email
ORDER BY B.write_datetime DESC;

• 검색 게시물 리스트 불러오기 SQL

SELECT 
	B.board_number,
    B.title,
    B.contents,
    B.write_datetime,
    B.view_count,
    U.nickname,
    U.profile_image
FROM board AS B INNER JOIN user AS U
ON B.writer_email = U.email
WHERE B.title LIKE '%4%'
ORDER BY B.write_datetime DESC;

• 특정 유저가 작성한 게시물 리스트 불러오기 SQL

SELECT 
	B.board_number,
    B.title,
    B.contents,
    B.write_datetime,
    B.view_count,
    U.nickname,
    U.profile_image
FROM board AS B INNER JOIN user AS U
ON B.writer_email = U.email
WHERE B.writer_email = 'email@email.com'
ORDER BY write_datetime DESC;

• 게시물 번호가 2번인 게시물을 조회
• (게시물번호, 제목, 내용, 작성날짜및시간, 작성자이메일, 작성자닉네임, 작성자프로필사진)

SELECT 
	B.board_number,
    B.title,
    B.contents,
    B.write_datetime,
    B.writer_email,
    U.nickname,
    U.profile_image
FROM board AS B INNER JOIN user AS U
ON B.writer_email = U.email
WHERE B.board_number = 2;
    


SELECT * FROM user;
SELECT * FROM board;
SELECT * FROM comment;
SELECT * FROM favorite;