MySQL 4일차 - 조인(JOIN), 서브쿼리, 프로시저(Stored Procedure)

Front단=유저=사용자=end최종유저화면=폼=form=레이아웃=layout
ㄴ웹브라우저, 스마트폰 프로그램앱
ㄴ웹브라우저(크롬, 애저, 사파리, IE, 오페라) 프로그램언어 HTML=Hyper Text Mark-up Language
ㄴ웹브라우저 통신기본은 HTTP
ㄴ웹브라우저==>데이터저장호출, 앱어플==>데이터저장호출

Back단=알맹이 보관=database관리하는 시스템 DBMS
SQL데이터보관, 기록, 저장 후 데이터호출 후 조회, 수정, 삭제, 출력
ㄴStructured Query Language 규칙문법

 

JOIN 종류 - INNER, OUTER, SELF, NATURAL, CROSS, NON-EQUI // UNION

★ emp테이블 empno, ename, job, sal, deptno
★ dept테이블 dname, loc

SELECT empno, ename, job, sal, e.deptno, dname, loc FROM emp e, dept d WHERE e.deptno=d.deptno;
SELECT empno, ename, job, sal, e.deptno, dname, loc FROM emp e join dept d ON e.deptno=d.deptno;

 

 

<서브쿼리 subquery>

※ subquery첫번째 (스칼라 서브쿼리)
SELECT 필드1, (SELECT ~~~새로운필드생성), 필드3
FROM 테이블명
WHERE 필드 연산 조건값;

※ subquery두번째 (테이블이름 대신 select쿼리) = 인라인view
SELECT 필드1, 필드2, 필드3
FROM (SELECT 필드 ~~ FROM table명)
WHERE 필드 연산 조건값;

※ subquery세번째 (중첩서브쿼리)
SELECT 필드1, 필드2, 필드3
FROM 테이블명
WHERE 필드 연산 (select ~~ where);
ex) SELECT empno, ename, job, sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='SCOTT');

서브쿼리예제
SELECT ename, job, sal 
FROM emp 
WHERE (job, sal) IN (SELECT job, max(sal) FROM emp GROUP BY job);

SELECT empno, ename, sal 
FROM emp e 
WHERE exists (SELECT empno FROM emp WHERE e.empno=mgr);

SELECT ename, job, a.deptno, b.dname, b.loc 
FROM (SELECT * FROM emp WHERE job='MANAGER') a 
JOIN dept b 
ON a.deptno=b.deptno;

SELECT ename, (SELECT dname FROM dept d WHERE d.deptno = e.deptno) AS "부서명"
FROM emp e
WHERE job ='MANAGER';
||
SELECT ename, dname FROM emp e, dept d WHERE e.deptno = d.deptno AND job = 'MANAGER';
SELECT ename, dname FROM emp e JOIN dept d ON e.deptno = d.deptno AND job = 'MANAGER';


-- 에러발생 --
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp GROUP BY deptno);  //에러

SELECT AVG(sal) FROM emp;  --  1건 조회
SELECT AVG(sal) FROM emp GROUP BY deptno;  --  3건조회
ㄴsal값은 하나이므로 3건이 조회되는 서브쿼리는 에러가 난다.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM client;
+-------+---------+----------+
| ct_no | ct_name | ct_grade |
+-------+---------+----------+
|     1 | kim     | A        |
|     2 | lee     | S        |
|     3 | goo     | B        |
|     4 | park    | C        |
|     5 | choi    | B        |
|     6 | hong    | S        |
|     7 | kang    | A        |
+-------+---------+----------+

SELECT * FROM corder;
+----------+------------+-------+--------+-----------+
| order_id | order_date | ct_no | pt_id  | order_cnt |
+----------+------------+-------+--------+-----------+
|     9900 | 2021-05-09 |     3 | 엘지   |         9 |
|     9911 | 2022-12-30 |     5 | 구글   |         7 |
|     9922 | 2021-06-23 |     3 | 다음   |         3 |
|     9933 | 2022-11-05 |     4 | 아마존 |         8 |
|     9944 | 2021-02-17 |     4 | 오라클 |         5 |
|     9955 | 2021-02-17 |     2 | 삼성   |         6 |
+----------+------------+-------+--------+-----------+

--------------------------------------------------------------------------------

※ 주문을 한번이라도 한 고객 (2번, 3번, 4번, 5번 출력)
>> IN( ), WHERE EXISTS( ), DISTINCT 사용

SELECT DISTINCT c.* FROM client c INNER JOIN corder o ON c.ct_no=o.ct_no ORDER BY c.ct_no;

SELECT * FROM client WHERE ct_no IN (SELECT ct_no FROM corder) ORDER BY ct_no;

SELECT * FROM client c WHERE EXISTS(SELECT ct_no FROM corder o WHERE c.ct_no=o.ct_no) ORDER BY c.ct_no;

+-------+---------+----------+
| ct_no | ct_name | ct_grade |
+-------+---------+----------+
|     2 | lee     | S        |
|     3 | goo     | B        |
|     4 | park    | C        |
|     5 | choi    | B        |
+-------+---------+----------+



※ 주문을 한번이라도 안한 고객 (1번, 6번, 7번)

SELECT * FROM client WHERE ct_no NOT IN (SELECT ct_no FROM corder) ORDER BY ct_no;

SELECT * FROM client c WHERE NOT EXISTS (SELECT ct_no FROM corder o WHERE c.ct_no=o.ct_no) ORDER BY c.ct_no;

+-------+---------+----------+
| ct_no | ct_name | ct_grade |
+-------+---------+----------+
|     1 | kim     | A        |
|     6 | hong    | S        |
|     7 | kang    | A        |
+-------+---------+----------+


※ 조인쿼리를 이용해서 emp, dept테이블에 한번도 참조가 안된 부서명이름 출력

SELECT d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno WHERE e.deptno IS NULL GROUP BY d.dname;

SELECT DISTINCT d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno WHERE e.deptno IS NULL;

+------------+
| dname      |
+------------+
| OPERATIONS |
| coder      |
+------------+


SELECT d.dname, d.loc FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno WHERE e.empno IS NULL;
+------------+--------+
| dname      | loc    |
+------------+--------+
| OPERATIONS | BOSTON |
| coder      | seoul  |
+------------+--------+

------------------------------------------------------------------------------------------------------------------------------------------------------

 

[유저 추가]
show databases;
use mysql;
show tables;
select host, user from user;

새로운 유저추가
create user bit@localhost identified by 1234;
grant all privileges on *.* to bit@localhost;
flush privileages;
show grants for bit@localhost;
select host, user from user;

------------------------------------------------------------------------------------------------------------------------------------------------------

 

저장 프로시저 SP(Stored Procedure=처리)란?
 ㄴ일련의 SQL문장을 선언해서 MySQL에 저장하고, 해당 SQL문을 함수처럼 사용하는 것으로
    만들어 두기만 하면 함수처럼 호출하여 편하게 사용 할 수 있다.
 ㄴ반복처리를 할 수 있다.(Loop)
 ㄴ비교처리를 할 수 있다.(IF)
 ㄴError 처리를 할 수 있다.(예외처리)
 ㄴSQL문을 캡슐화 할 수 있다.(데이터의 보안 및 무결성)
 ㄴ변수 선언을 할 수 있다.
 ㄴ실행할때마다 분석된 결과를 실행만 하기 때문에 성능이 빠르다.
 ㄴNetwork Traffic이 감소된다.
    여러 SQL문장을 block으로 묶고 한번에 블럭 전부를 서버로 전송하기 때문에 통신량을 줄일 수 있다.
 ㄴ생성 CREATE PROCEDURE 이름( ) 아래에 코드기술

----교재 81페이지 참고----

DELIMITER 뒤에 들어가는 특수문자는 다른문자로 대체 가능함 ex) $$, @@, //

DROP PROCEDURE IF EXISTS myProc;
DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
    SELECT * FROM memberTBL WHERE memberName='당탕이';
    SELECT * FROM productTBL WHERE productName='냉장고';
END //
DELIMITER ;  --  세미콜론 앞에 띄어쓰기★

CALL myProc();  --  프로시저 호출




DROP PROCEDURE IF EXISTS show_msg;
DELIMITER $$
CREATE PROCEDURE show_msg()
BEGIN
    DECLARE msg VARCHAR(50);
    SET msg='MySQL 비트컴퓨터 Hello sp';
    SELECT msg;
END $$
DELIMITER ;

CALL show_msg();