DBMS 고오급 들어갑니다~ day03 ( join)

출처 https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/

끝나지 않은 SELECT...ㅎㅎㅎ
DBMS 세번째 배우니까 이제 좀 할만하다(머리 사양이 좀..)
join 이번에 확실히 기억하고 넘어가보자구!!

day03.txt

SELECT
    컬럼 골라내기 ( SELECT a, b)
    행 골라내기 ( WHERE 절)

정렬하여 조회하기
    최신순, 오래된순, 좋아요순, ...

SELECT 컬럼1, 컬럼2, 컬럼3
FROM 테이블명
WHERE 조건 -- 행을 안 골라내고 싶으면 안써도됨
ORDER BY 컬럼 옵션;

옵션
    ASC
        오름차순(작은값->큰값)
    DESC
        내림차순(큰값->작은값)

컬럼 이름에 별명 붙이기(alias)
SELECT id 아이디, name "사용자 이름", age AS "나이"
from tbl_tmp;
    AS 는 생략 가능,
    "" 는 컬럼의 별명을 이미 사용되고 있는 예약어(명령어)로 사용하거나
    띄어쓰기가 포함된 별명을 사용하는 경우를 제외하고는 생략가능
    SELECT
        a AS "안녕",
        b "반가워",
        c AS 가격,
        d cnt

두개의 테이블을 합치기
    JOIN

내부/동등조인(inner/equi join)
    두개의 테이블을 합치긴 할건데, 조건을 만족하는 행들끼리 합치겠다

SELECT 컬럼, 컬럼, ...
FROM 테이블명1 INNER JOIN 테이블명2
ON 조인조건
[WHERE 조건]
[ORDER BY 컬럼 옵션]

OUTER JOIN

    SELECT 컬럼, 컬럼, ...
    FROM 테이블명1 LEFT OUTER JOIN 테이블명2
    ON 조인조건
    [WHERE 조건]
    [ORDER BY 컬럼 옵션]

    SELECT 컬럼, 컬럼, ...
    FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
    ON 조인조건
    [WHERE 조건]
    [ORDER BY 컬럼 옵션]

날짜 형식 패턴
    %m : 두글자 월 03
    %M : March
    %d : 두글자 일  22
    %D : 22nd
    %y : 두글자 년도
    %Y : 네글자 년도
    %h : 00 ~ 12
    %H : 00 ~ 23

데이터 수정
    UPDATE 테이블명
    SET 컬럼명 = 값, 컬럼명 = 값, ...
    WHERE 조건;

데이터 삭제
    DELETE FROM 테이블명
    WHERE 조건;

데이터 추가 시에는
    부모에 없는 값을 자식테이블에 추가할수 없다

데이터 삭제 시에는
    자식에서 사용되고 있는 값을 부모에서 삭제할수 없다
    자식에서 사용되고 있는 값이 부모에서 삭제된다면 자동으로
    자식에서도 삭제가 되게 만드는 옵션 : ON DELETE CASCADE
    
    자식에서 사용되고 있는 값이 부모에서 삭제된다면 자동으로
    자식의 값을 NULL 로 바꾸는 옵션 : ON DELETE SET NULL

01정렬.sql

select * from tbl_tmp;

select *
from tbl_tmp
order by age asc;

select *
from tbl_tmp
order by age desc;

select id 아이디, name as "이름", age as "나이"
from tbl_tmp
order by 나이 asc; -- 컬럼의 별명으로도 정렬 가능    

-- 테이블 하나 더 만들어보자(고오급 진도 나간다)
create table tbl_posts(
	post_id int,
    title varchar(100),
    contents varchar(200),
    writer int,
    constraint tbl_posts_pk primary key(post_id),
    constraint tbl_posts_writer_fk foreign key(writer) references tbl_tmp(id)
);

# tbl_tmp의 id 컬럼에 4645가 없기에
# tbl_posts의 writer에는 4645가 저장될 수 없다(외래키 제약조건위반)
insert into tbl_posts
(post_id, title, contents, writer)
values
(2315, 'javascript를 배워봅시다', 'javascript 얼마나 재밌게요~', 40);

insert into tbl_posts
(post_id, title, contents, writer)
values
(105, '오늘 뭐먹을까요?', '오늘 저녁메뉴 추천좀', 20);

insert into tbl_posts
(post_id, title, contents, writer)
values
(50, '안녕하세요', '반가워여~', null);

select * from tbl_posts;

select * from tbl_tmp;

# 내부조인
select tbl_posts.title, tbl_tmp.name-- 컬럼이름이 같을 수도 있기 때문에 소속을 알려주는 습관 들이기
from tbl_posts inner join tbl_tmp -- 자리를 바꿔도 결과는 동일(컬럼 순서 바뀌는건 상관X)
on tbl_posts.writer = tbl_tmp.id;

# 테이블 이름에 별명 짓기 (AS XX)
select tp.title 제목, tt.name 작성자
from tbl_posts tp inner join tbl_tmp tt
on tp.writer = tt.id;

# left outer join
# 왼쪽에 있는 테이블의 행을 모두 보겠다(손실없이)
select *
from tbl_posts tp left outer join tbl_tmp tt
on tp.writer = tt.id;

# Right outer join
# 왼쪽에 있는 테이블의 행을 모두 보겠다(손실없이)
select *
from tbl_posts tp right outer join tbl_tmp tt
on tp.writer = tt.id;

# 산술연산자 자유롭게 사용 가능
# 예) 사원, 이름, 나이, 봉급 컬럼이 있을텐데 원래 봉급의 x2.4 해서 성과급 볼때 사용
select * from tbl_tmp;

select age, age + 10
from tbl_tmp;

# 날짜 데이터
alter table tbl_tmp
modify join_date datetime default now(); 

insert into tbl_tmp
(id, name, age, join_date)
values
(78, 'Kyle', 7, str_to_date('07-31-2017', '%m-%d-%Y'));

insert into tbl_tmp
(id, name, age, join_date)
values
(120, 'Jay', 40, now() );

select now(); -- Oracle의 sysdate() 와 동일

select * from tbl_tmp;

# 데이터 수정
update tbl_tmp
set name = 'Stephany', age = 33
where id = 30;

# Oracle 가능, MySQL은 기본적으로 safe mode가 적용되어
# primary key 가 아닌 조건으로 행을 골라내어 수정하는것을 막아준다(해제 가능)
# 영어식 문법이기때문에 거꾸로 쓰면 더 편함
update tbl_tmp
set join_date = now()
where id <=60;

# 데이터 삭제
delete from tbl_tmp
where id = 50;

# 자식에서 사용중인 값을 부모에서 삭제하려면 오류발생(FK)
delete from tbl_tmp
where id = 40;

# 외래키 제약조건을 설정할 때
# 부모의 값이 삭제되면 자식의 값도 삭제
# 혹은 자식의 값을 null로 자동으로 바꿔주는 옵션을 만들수 있음
create table tbl_a(
	id int primary key,
    pw varchar(20)
);

insert into tbl_a
values(4, 'wlwl');

select * from tbl_a;

create table tbl_b(
	post_id int primary key,
    writer int,
    constraint fk foreign key (writer)
    references tbl_a(id) on delete cascade -- 계단식으로(id 삭제하면 writer도 자동으로 삭제시켜라)
);

create table tbl_b(
	post_id int primary key,
    writer int,
    constraint fk foreign key (writer)
    references tbl_a(id) on delete set null -- 삭제가 발생하면 그 부분을 null값 으로 대체해라
);

insert into tbl_b
values(300, 3);

select * from tbl_b;

delete from tbl_a
where id = 3;