<실습> 3.mysql join&outer join & 프로그래밍

-- join

-- inner join 은 두테이블 양쪽에 다 데이터가 있는 것만 출력 한다. 

select *

from buyTbl

   inner join userTbl                  -- userTbl 과 join 해라

      on buyTbl.userID = userTbl.userID   -- 설정해주기

WHERE buyTbl.userID = 'JYP';

-- 오류 예)

-- 전체를 다 출력할때는 해당 칼럼이 어느 테이블의 칼럼인지 명시해 줘야 한다.

select userID, name, prodName, addr, mobile1 + mobile2 as '연락처'

from buyTbl

   inner join userTbl                  -- userTbl 과 join 해라

      on buyTbl.userID = userTbl.userID;   -- 설정해주기

-- 해결 예)

-- 전체를 다 출력할때는 해당 칼럼이 어느 테이블의 칼럼인지 명시해 줘야 한다.

select buytbl.userID, name, prodName, addr, mobile1 + mobile2 as '연락처'

from buyTbl

   inner join userTbl                  -- userTbl 과 join 해라

      on buyTbl.userID = userTbl.userID;   -- 설정해주기

-- 해결 예)

-- 전체를 다 출력할때는 해당 칼럼이 어느 테이블의 칼럼인지 명시해 줘야 한다.

-- 회원이 10명인데 5명 밖에 안나온다. buy tbl 에 존재하는 회원만 나온다.

-- 한번도 구매하지 않은 이승기, 김경호, 임재범, 윤종신, 조관우는 나오지 않았다. 

-- inner join은 양쪽테이블에 모두 내용이 있는 것만 조인이 되는 방식이다. 

-- 다 보고 싶을 때는 outter 조인 사용한다.

select B.userID, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 as '연락처'

from buyTbl B

   inner join userTbl U                  -- userTbl 과 join 해라

      on B.userID = U.userID   -- 설정해주기

ORDER BY  U.userID;

-- 다대다 풀기

create table stdTbl( -- 학생 테이블

stdName varchar(10) not null primary key,

addr char(4) not null

);

create table clubTbl( -- 동아리테이블

clubName varchar(10) not null primary key,

roomNo char(4) not null

);

create table stdclubTbl(

num int auto_increment not null primary key,

stdName varchar(10) not null,

clubName varchar(10) not null,

foreign key(stdName) references stdTbl(stdName),

foreign key(clubName) references clubTbl(clubName)

);

insert into stdTbl values('김범수','경남'),('성시경','서울'),('조용필','경기'),('은지원','경북'),('바비킴','서울');

insert into ClubTbl values ('수영','101호'),('바둑','102호'),('축구','103호'),('봉사','104호');

INSERT INTO stdClubTbl values (null, '김범수', '바둑'),(null, '김범수', '축구'),

(null, '조용필', '축구'),(null, '은지원', '축구'),(null, '은지원', '봉사'),(null, '바비킴', '봉사');

-- 학생을 기준으로 학생 이름/지역/가입한 동아리/동아리 이름을 출력하기

select S.stdName, S.addr, C.clubname, C.roomNo

from stdTbl S

inner join stdclubTbl SC

   ON S.stdName = SC.stdName

inner join clubTbl C

   ON SC.clubName = C.clubName

order by S.stdName;

-- 동아리를 기준으로 가입한 학생 출력하기

select C.clubname, C.roomNo, S.stdName, S.addr

from stdTbl S

inner join stdclubTbl SC

   ON S.stdName = SC.stdName

inner join clubTbl C

   ON SC.clubName = C.clubName

order by C.clubname;

-- outter join 한쪽만 있어도 보여준다.

-- 전체 회원의 구매 기록을 출력

-- 왼쪽 테이블(userTbl) 모두 출력

select B.userID, U.name, B.prodName, U.addr, concat(mobile1 , U.mobile2) as '연락처'

from usertbl U

   left outer join buytbl B                  

      ON U.userID = B.userID

ORDER BY  U.userID;

-- right outter join 

select B.userID, U.name, B.prodName, U.addr, concat(mobile1 , U.mobile2) as '연락처'

from buytbl B

   right outer join usertbl U                  

      ON U.userID = B.userID

ORDER BY  U.userID;

-- 가입만 하고 활동안한 회원 보기

select U.userID, U.name, B.prodName, U.addr, concat(mobile1 , U.mobile2) as '연락처'

from usertbl U

   left outer join buytbl B                  

      ON U.userID = B.userID

        where B.prodName is null

ORDER BY  U.userID;

-- 동아리에 가입하지 않은 학생(학생이 기준이됨)

select S.stdName, S.addr, C.clubname, C.roomNo

from stdTbl S

left OUTER join stdclubTbl SC

   ON S.stdName = SC.stdName

left OUTER join clubTbl C

   ON SC.clubName = C.clubName

order by S.stdName;

-- 가입한 학생이 하나도 없는 동아리 (동아리가 기준이됨)

select  C.clubname, C.roomNo,S.stdName, S.addr

from stdTbl S

left OUTER join stdclubTbl SC

   ON S.stdName = SC.stdName

RIGHT OUTER join clubTbl C

   ON SC.clubName = C.clubName

order by  C.clubname;

-- 동아리에 가입하지 않은 학생도 출력, 학생이 없는 동아리도 출력

select S.stdName, S.addr, C.clubname, C.roomNo

from stdTbl S

left OUTER join stdclubTbl SC

   ON S.stdName = SC.stdName

left OUTER join clubTbl C

   ON SC.clubName = C.clubName

union

select  C.clubname, C.roomNo,S.stdName, S.addr

from stdTbl S

left OUTER join stdclubTbl SC

   ON S.stdName = SC.stdName

RIGHT OUTER join clubTbl C

   ON SC.clubName = C.clubName;

    

    

 

 

 

-- 셀프조인. 테이블하나에 자기가 조인

CREATE TABLE empTbl (emp char(3), manager char(3), empTel varchar(8));

insert into empTbl values('나사장',null,'0000');

insert into empTbl values('김재무','나사장','2222');

insert into empTbl values('김부장','김재무','2222-1');

insert into empTbl values('이부장','김재무','2222-2');

insert into empTbl values('우대리','이부장','2222-2-1');

insert into empTbl values('지사원','이부장','2222-2-2');

insert into empTbl values('이영업','나사장','1111');

insert into empTbl values('한과장','이영업','1111-1');

insert into empTbl values('최정보','나사장','3333');

insert into empTbl values('윤차장','최정보','3333-1');

insert into empTbl values('이주임','윤차장','3333-1-1');

-- 직송상관의 연락처

select A.emp AS '부하직원', B.emp AS '직속상관', B.empTel AS '직속상관연락처'

FROM empTbl A

   INNER JOIN empTbl B

      ON A.manager = B.emp

WHERE A.emp = '우대리';

-- union 중복된 열 하나만 출력 / union all 중복 상관 없이 다 출력/ 

-- select 문장 1, 과 2의 결과 열의 개수가 같아야 한다. 호환되는 데이터 형식도 같아야 한다.

select stdName, addr from stdTbl

union all

select clubName, roomNo from clubTbl;

-- not in 첫번째 쿼리의 결과 중에서 두 번째 쿼리에 해당하는 것을 제외하기 위한 구문

-- 전화가 없는 사람 제외

select name ,concat(mobile1, mobile2) as '전화번호' from userTbl

where name not in

(select name from userTbl where mobile1 is null);

-- 반대로 전화가 없는 사람만 조회

select name ,concat(mobile1, mobile2) as '전화번호' from userTbl

where name in

(select name from userTbl where mobile1 is null);

-- sql 프로그래밍

drop procedure if exists ifProc; -- 기존에 만든적 있다면 삭제

delimiter $$

create procedure ifProc()

begin

   declare var1 int; -- 변수선언

    set var1 = 100; -- 변수에 값 대입

    

    if var1 = 100 then -- 만약 @var1이 100이라면

      select '100입니다';

   else

      select '100이 아닙니다';

   end if;

    

end $$

delimiter ;

call ifProc();

-- 해당직원 출력

drop procedure if exists ifProc2; -- 기존에 만든적 있다면 삭제

delimiter $$

create procedure ifProc2()

begin

   declare hireDATE DATE;  -- 입사일

    declare curDATE DATE; -- 오늘

    declare days int;     -- 근무한 일수

    select hire_date into hireDATE -- hire_date 열의 결과를 hireDATE에 대입

    from employees.employess

    where emp_no = 10001;

    

    set curDATE = current_date(); -- 현재 날짜

    SET days = datediff(curDATE,hireDATE); -- 날자의 차이 , 일 단위

    

    IF (days/365) >= 5 THEN -- 5년이 지났다면

      SELECT concat('입사한지',days, '일이나 지났습니다. 축하합니다.');

   else

      select '입사한지' + days + '일밖에 안되었네요. 열심히 일하세요';

    end if;

end $$

delimiter ;

call ifProc2();

-- case 

drop procedure if exists case2; -- 기존에 만든적 있다면 삭제

delimiter $$

create procedure case2()

begin

   declare point int;

    declare credit char(1);

    set point = 77;

case

    when point >= 90 then

      set credit = 'a';

   when point >= 80 then

      set credit = 'b';

   when point >= 70 then

      set credit = 'c';

   when point >= 60 then

      set credit = 'd';    

    else

      set credit = 'f';

   end case;

    select concat('취득점수==>', point), concat('학점==>', credit);

end $$

delimiter ;

call case2();