[혼자공부하는SQL] Summary2

-- 변수의 선언 및 값 대입
SET @변수이름 = 변수의 값

-- 변수의 값 출력
SELECT @변수이름;

SET @count = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROm member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @count;

-- 문자와 문자를 더함 (정수로 변환되어 연산됨) → 결과: 300  
SELECT '100' + '200';  

-- 문자와 문자를 연결 (문자로 처리) → 결과: '100200'  
SELECT CONCAT('100', '200');  

-- 정수와 문자를 연결 (정수가 문자로 변환되어 처리) → 결과: '100200'  
SELECT CONCAT(100, '200');  

-- '2mega'가 정수 2로 변환되어 비교 → 결과: FALSE (1 > 2 → FALSE)  
SELECT 1 > '2mega';  

-- '2MEGA'가 정수 2로 변환되어 비교 → 결과: TRUE (3 > 2 → TRUE)  
SELECT 3 > '2MEGA';  

-- 'mega2'가 0으로 변환되어 비교 → 결과: TRUE (0 = 0 → TRUE)  
SELECT 0 = 'mega2';

SELECT <열 목록>
FROM <첫 번째 테이블>
    INNER JOIN <두 번째 테이블>
    ON <조인될 조건>
[WHERE 검색 조건]

-- 오류 발생
SELECT mem_id, mem_name, prod_name, addr, CONCAT(phone1, phone2) AS '연락처'
FROM buy
    INNER JOIN member
    ON buy.mem_id = member.mem_id;
-- 해결
SELECT buy.mem_id, mem_name, prod_name, addr, CONCAT(phone1, phone2) AS '연락처'
FROM buy
    INNER JOIN member
    ON buy.mem_id = member.mem_id;

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
    <LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
    ON <조인될 조건>
[WHERE 검색 조건]

SELECT *
FROM 테이블
CROSS JOIN 테이블;

SELECT <열 목록>
FROM <테이블> 별칭A
    INNER JOIN <테이블> 별칭 B
    ON <조인될 조건>
[WHERE 검색 조건]

IF <조건식> THEN
    SQL문장들
END IF;

-- 한 번만 사용한 경우
IF <조건식> THEN
    SQL문장들
ELSE
    SQL문장들
END IF;

-- 두 번 이상 사용한 경우
IF <조건식> THEN
    SQL문장들
ELSEIF
    SQL문장들
ELSEIF
    SQL문장들    
ELSE
    SQL문장들
END IF;

DECLARE 변수명 변수타입;

SELECT 열_이름 INTO 저장할 변수
    FROM 테이블
    WHERE 조건

-- 예시
SET curDATE = CURRENT_DATE()

-- 특징 : 결과가 양수면 날짜1이 더 미래, 음수면 날짜2가 더 미래이다.
SELECT DATEDIFF('2025-12-31', '2025-01-01') AS result; -- 결과: 364 (날짜 차이)  
SELECT DATEDIFF('2025-01-01', '2025-12-31') AS result; -- 결과: -364  
SELECT DATEDIFF(NOW(), '2024-01-01') AS result; -- 현재 날짜와 비교 가능

CASE
    WHEN 조건1 THEN
        SQL문장들1
    WHEN 조건2 THEN
        SQL문장들2
    WHEN 조건3 THEN
        SQL문장들3
    ELSE
        SQL문장들4
END CASE;

USE market_db;

-- 그룹 별 총 구매액
SELECT mem_id, SUM(price * amount)
    FROM buy
    GROUP BY mem_id;

-- 그룹 별 총 구매액 (내림차순)
SELECT mem_id, SUM(price * amount)
    FROM buy
    GROUP BY mem_id
    ORDER BY SUM(price * amount) DESC;

-- 그룹 별 총 구매액 (내림차순) - 아이디, 이름, 구매액
SELECT B.mem_id, M.mem_name, SUM(price * amount)
    FROM buy B
        INNER JOIN member M
        ON B.mem_id = M.mem_id
    GROUP BY B.mem_id
    ORDER BY SUM(B.price * B.amount) DESC;

-- 그룹 별 총 구매액 (내림차순) - 유령 고객까지 반영 (mem_id를 신경 쓰기)
SELECT M.mem_id, M.mem_name, SUM(price * amount)
    FROM buy B
        RIGHT OUTER JOIN member M
        ON B.mem_id = M.mem_id
    GROUP BY M.mem_id
    ORDER BY SUM(B.price * B.amount) DESC;

-- 그룹 별 총 구매액 (내림차순) - CASE 적용
SELECT M.mem_id, M.mem_name, SUM(price * amount),
        CASE
            WHEN (SUM(price * amount) >= 1500) THEN '최우수고객'
            WHEN (SUM(price * amount) >= 1000) THEN '우수고객'
            WHEN (SUM(price * amount) >= 1) THEN '일반고객'
            ELSE '유령고객'
        END "회원등급"
    FROM buy B
        RIGHT OUTER JOIN member M
        ON B.mem_id = M.mem_id
    GROUP BY M.mem_id
    ORDER BY SUM(B.price * B.amount) DESC;

WHILE <조건식> DO
    SQL 문장들
END WHILE;

DROP DATABASE IF EXISTS naver_db;
CREATE DATABASE naver_db;

USE naver_db;
DROP TABLE IF EXISTS member;
CREATE TABLE member(
        mem_id CHAR(8) NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10) NOT NULL,
    mem_number TINYINT NOT NULL,
    addr CHAR(2) NOT NULL,
    phone1 CHAR(3) NULL,
    phone2 CHAR(8) NOT NULL,
    height TINYINT UNSIGNED NOT NULL,
    debut_date DATE NOT NULL
);

DROP TABLE IF EXISTS buy;
CREATE TABLE buy(
    num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    mem_id CHAR(8) NULL,
    prod_name CHAR(6) NOT NULL,
    group_name CHAR(4) NULL,
    price INT UNSIGNED NOT NULL,
    amount SMALLINT UNSIGNED NOT NULL,
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

INSERT INTO member VALUES ('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015-10-10');
INSERT INTO member VALUES ('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016-08-08');
INSERT INTO member VALUES ('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015-01-15');

INSERT INTO buy VALUES (NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES (NULL, 'BLK', '맥북프로', '디지털', 1000, 1);

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(
    mem_id   CHAR(8)          NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10)      NOT NULL,
    height   TINYINT UNSIGNED NULL
);

DROP TABLE IF EXISTS member;
CREATE TABLE member
(
    mem_id   CHAR(8)          NOT NULL,
    mem_name VARCHAR(10)      NOT NULL,
    height   TINYINT UNSIGNED NULL,
    PRIMARY KEY (mem_id)
);

DROP TABLE IF EXISTS member;
CREATE TABLE member
(
    mem_id   CHAR(8)          NOT NULL,
    mem_name VARCHAR(10)      NOT NULL,
    height   TINYINT UNSIGNED NULL
);
ALTER TABLE member -- 테이블 정보를 수정하겠다
    ADD CONSTRAINT -- 제약 조건을 추가하겠다
        PRIMARY KEY (mem_id); -- 이 조건을

USE naver_db;

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(
    mem_id   CHAR(8)          NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10)      NOT NULL,
    height   TINYINT UNSIGNED NULL
);

CREATE TABLE buy
(
    num       INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    mem_id    CHAR(8)            NOT NULL,
    prod_name CHAR(6)            NOT NULL,
    FOREIGN KEY (mem_id) REFERENCES member (mem_id)
);

-- 외래 키의 이름도 바꿀 수 있다. (히지만 혼란을 위해 동일한 이름을 권장한다.)
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(
    mem_id   CHAR(8)          NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10)      NOT NULL,
    height   TINYINT UNSIGNED NULL
);

CREATE TABLE buy
(
    num       INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    user_id   CHAR(8)            NOT NULL,
    prod_name CHAR(6)            NOT NULL,
    FOREIGN KEY (user_id) REFERENCES member (mem_id)
);

DROP TABLE IF EXISTS buy;
CREATE TABLE buy
(
    num       INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    mem_id   CHAR(8)            NOT NULL,
    prod_name CHAR(6)            NOT NULL
);
ALTER TABLE buy
    ADD CONSTRAINT
        FOREIGN KEY (mem_id) REFERENCES member (mem_id);

INSERT INTO member VALUES ('BLK', '블랙핑크', 163);
INSERT INTO buy VALUES (NULL, 'BLK', '지갑');
INSERT INTO buy VALUES (NULL, 'BLK', '맥북');

-- 검색
SELECT M.mem_id, M.mem_name, B.prod_name
    FROM buy B
        INNER JOIN member M
        ON B.mem_id = M.mem_id;

-- PK와 FK로 연결되어 있기에, 변경/삭제가 불가능하다. (데이터 무결성을 보장하기 위해서)
UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK';
DELETE FROM member WHERE mem_id = 'BLK';

DROP TABLE IF EXISTS buy;
CREATE TABLE buy
(
    num       INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    mem_id    CHAR(8)            NOT NULL,
    prod_name CHAR(6)            NOT NULL
);

-- 테이블 정보 수정
ALTER TABLE buy
    ADD CONSTRAINT
        FOREIGN KEY (mem_id) REFERENCES member (mem_id)
            ON UPDATE CASCADE
            ON DELETE CASCADE;

INSERT INTO buy VALUES (NULL, 'BLK', '지갑');
INSERT INTO buy VALUES (NULL, 'BLK', '맥북');

-- 검색 (BLK인 상태)
SELECT M.mem_id, M.mem_name, B.prod_name
    FROM buy B
        INNER JOIN member M
        ON B.mem_id = M.mem_id;

-- PK 수정
UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK';
-- 검색 (PINK로 바뀐 상태)
SELECT M.mem_id, M.mem_name, B.prod_name
    FROM buy B
        INNER JOIN member M
        ON B.mem_id = M.mem_id;

-- PK 삭제
DELETE FROM member WHERE mem_id = 'PINK';
-- 검색
SELECT * FROm buy;

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(
    mem_id   CHAR(8)          NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10)      NOT NULL,
    height   TINYINT UNSIGNED NULL,
    email    CHAR(30)         NULL UNIQUE
);

-- 정상
INSERT INTO member VALUES ('BLK', '블랙핑크', 163, 'pink@gmail.com');
INSERT INTO member VALUES ('TWC', '트와이스', 167, NULL);
-- 오류 : 이메일 중복
INSERT INTO member VALUES ('APN', '에이핑크', 164, 'pink@gmail.com');

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(
    mem_id   CHAR(8)          NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10)      NOT NULL,
    height   TINYINT UNSIGNED NULL CHECK ( height >= 100 ),
    phone1   CHAR(3)          NULl
);

-- 정상
INSERT INTO member VALUES ('BLK', '블랙핑크', 163, NULL);
-- 오류
INSERT INTO member VALUES ('TWC', '트와이스', 99, NULL);

-- 연락처(phone1의 범위 지정)
ALTER TABLE member
    ADD CONSTRAINT
    CHECK (phone1 IN ('02', '031', '032', '054', '055', '061'));

-- 정상
INSERT INTO member VALUES ('TWC', '트와이스', 163, '02');
-- 오류
INSERT INTO member VALUES ('OMY', '오마이걸', 99, '010');

DROP TABLE IF EXISTS member;
CREATE TABLE member
(
    mem_id   CHAR(8)          NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10)      NOT NULL,
    height   TINYINT UNSIGNED NULL DEFAULT 160,
    phone1   CHAR(3)          NULl
);

-- CONSTAINT와 문법이 조금 다르다.
ALTER TABLE member
    ALTER COLUMN phone1 SET DEFAULT '02';

-- 정상
INSERT INTO member VALUES ('RED', '레드벨벳', 163, '054');
-- 기본값을 사용
INSERT INTO member VALUES ('SPC', '우주소녀', default, default);

SELECT * FROM member;

CREATE VIEW 뷰_이름
AS
    SELECT 문;

SELECT 열_이름 FROM 뷰_이름
    [WHERE 조건];

-- 뷰 생성
CREATE VIEW v_member
AS
SELECT mem_id, mem_name, addr
FROM member;

-- 뷰로부터 검색
SELECT *
FROM v_member;

SELECT mem_name, addr
FROM v_member
WHERE addr In ('서울', '경기');

-- 뷰의 생성
CREATE VIEW v_viewtest1
AS
SELECT B.mem_id                      'Member ID',
       M.mem_name                 AS 'Member Name',
       B.prod_name                   "Product Name",
       CONCAT(M.phone1, M.phone2) AS "Office Phone"
FROM buy B
         INNER JOIN member M
                    ON B.mem_id = M.mem_id;

SELECT DISTINCT `Member ID`, `Member Name`
FROM v_viewtest1;

-- 뷰의 수정
ALTER VIEW v_viewtest1
AS
    SELECT B.mem_id                   '회원 아이디',
        M.mem_name                 AS '회원 이름',
        B.prod_name                   "제품 이름",
        CONCAT(M.phone1, M.phone2) AS "연락처"
    FROM buy B
        INNER JOIN member M
            ON B.mem_id = M.mem_id;

SELECT DISTINCT `회원 아이디`, `회원 이름`
FROm v_viewtest1;

-- 뷰의 삭제
DROP VIEW v_viewtest1;

-- 뷰가 있으면 덮어 써서 생성한다.
CREATE OR REPLACE VIEW v_viewtest2
AS
SELECT mem_id, mem_name, addr
FROM member;

DESCRIBE 뷰_이름;

DESCRIBE v_viewtest2;

-- 뷰를 생성하던 쿼리문 보기
SHOW CREATE VIEW v_viewtest2;

-- 뷰의 내용 수정
UPDATE v_member
SET addr = '부산'
WHERE mem_id = 'BLK';

SELECT * FROM v_member;

-- 뷰에 입력하려고 하는 경우 : 오류 발생 (NOT NULl이 존재해서)
-- 따라서 뷰를 통해서 입력이 될 수도 있지만, 안 될 수도 있다.
-- Default를 사용하게 되면, NOT NULL의 제약 조건을 만족시킬 수 있게 된다.
INSERT INTO v_member(mem_id, mem_name, addr) VALUES ('BTS', '방탄소년단', '경기');

CREATE VIEW v_height167
AS
SELECT *
FROM member
WHERE height >= 167;

-- 뷰 조회 : height가 167 이상인 레코드만 보여준다. 
SELECT *
FROM v_height167;

-- 상황1 : 뷰에서 행을 삭제하지만, 0개가 삭제된다.
DELETE
FROM v_height167
WHERE height < 167;

-- 상황2 : 입력은 성공해서 테이블에 들어가긴 한다.
INSERT INTO v_height167
VALUES ('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01');
-- 하지만 조회가 안 된다.
SELECT *
FROM v_height167; -- 존재하지 않는다.
SELECT *
FROM member; -- 존재한다.

-- 뷰의 조건 수정
ALTER VIEW v_height167
AS
    SELECT * FROM member WHERE height >= 167
        WITH CHECK OPTION;
-- 입력이 실패한다.
INSERT INTO v_height167
VALUES ('TOB', '텔레토비', 4, '영국', NULL, NULL, 140, '1995-01-01');

-- 테이블 삭제
DROP TABLE IF EXISTS buy, member;

-- 오류 발생
SELECT * FROM v_height167;

-- 왜 조회가 안 되는지 탐색 : MySQL에서 특정 테이블 또는 뷰의 무결성을 검사하는 명령어
CHECK TABLE v_height167;