[NC7기-61일차(7월20일)] - 웹프로그래밍 42일차

[네이버클라우드] 클라우드 기반의 개발자 과정 7기/웹프로그래밍

[NC7기-61일차(7월20일)] - 웹프로그래밍 42일차

quarrrter 2023. 7. 20. 17:07

https://github.com/PacktPublishing/Java-Coding-Problems.git

GitHub - PacktPublishing/Java-Coding-Problems: Java Coding Problems, published by Packt

Java Coding Problems, published by Packt. Contribute to PacktPublishing/Java-Coding-Problems development by creating an account on GitHub.

github.com

코딩 개념 잡는 자바 코딩 문제집

* 테이블 조인 - ERD(Entitiy Relationship Diagram)

(데이터(테이블)간의 관계도)

* sql의 주석 : --

* COMMENT '내용' : DBMS에 저장되는 주석

* 유니크 인덱스: PK는 아니지만 중복되면 안되는 인덱스

파일에 그대로 긁어오깅

source C:/Users/bitcamp/git/eomcs-docs/sql/Exam05.sql

mysql안에서 경로 설정하는 법 - 그 폴더 안에 들어가서 mysql 킨다.

/* distinct 와 all */ 전체 수강생이 어떤 은행을 쓰는 지 궁금할 때

/* 모든 데이터를 가져온다.*/
select all loc from room;

/* all은 생략할 수 있다*/
select loc from room;

/* 중복 값을 한 개만 추출할 때 distinct 를 붙인다.*/
select distinct loc from room;

/* 컬럼이 2 개 이상일 때
그 컬럼들의 값이 중복될 경우만 한 개로 간주한다.*/
select distinct loc, name from room;

/* order by */

/* 기본 인덱스 컬럼을 기준으로 정렬한다.*/
select rno, loc, name
from room;

/* 이름의 오름 차순(ascending)으로 정렬하기 */
select rno, loc, name
from room
order by name asc;

/* asc는 생략 가능하다. */
select rno, loc, name
from room
order by name;

/* 이름의 내림 차순(desceding)으로 정렬하기 */
select rno, loc, name
from room
order by name desc;

/* 이름은 오름차순, 지점명도 오름차순으로 정렬하기*/
select rno, loc, name
from room
order by name asc, loc asc;

/* 이름은 오름차순, 지점명은 내림차순으로 정렬하기*/
select rno, loc, name
from room
order by name asc, loc desc;

/* 지점명은 오름차순으로, 이름은 오름차순  정렬하기*/
select rno, loc, name
from room
order by loc asc, name asc;

-- order by 에서 컬럼을 지정할 때 select 절에 선택된 컬럼이 아니더라도 지정할 수 있다.
-- 즉 select 절에 있는 컬럼 또는 테이블 컬럼을 지정할 수 있다.
select rno, name, concat(loc,'-',name) as name2
from room
order by loc asc, name2 asc;

-- 실행 순서: from -> where -> select -> order by
-- 1) from 또는 join : 테이블의 전체 데이터 또는 조인 데이터
-- 2) where : 조건에 따라 결과로 뽑을 데이터를 selection 한다.
-- 3) group by : 조건에 따라 뽑은 데이터를 특정 컬럼을 기준으로 데이터를 묶는다.
-- 4) having : 그룹으로 묶은 데이터를 조건에 따라 선별한다.
-- 5) select : 최종 결과로 뽑을 컬럼을 표시(projection)한다. 표현식으로 계산한 컬럼도 포함시킨다.
-- 6) order by : select 절에서 추가한 임의 컬럼이나 테이블 컬럼을 기준으로 정렬한다.
-- 7) limit : 결과 데이터에서 지정한 범위의 데이터를 선택한다.
-- 8) 결과 추출: 7번을 수행한 결과 데이터에서 5번에 표시된 컬럼만 추출한다.
select
  concat(name,'-',loc) as class_name
from
  room
where
  loc <> '강남'
order by
  class_name; -- select 절에 있는 컬럼 또는 테이블 컬럼

select
  concat(name,'-',loc) as class_name
from
  room
where
  loc <> '강남'
order by
  loc desc; -- select 절에 있는 컬럼 또는 테이블 컬럼

/* as 로 컬럼에 별명(라벨명) 붙이기  */

/* 출력 라벨명을 변경하기
   => 라벨명을 지정하지 않으면 컬럼명이 라벨명이 된다*/
select rno as room_no, loc as location, name
from room;

/* as 생략 가능 */
select rno room_no, loc location, name
from room;

/* 라벨명에 공백을 넣고 싶으면 '' 안에 작성한다.*/
select rno 'room no', loc location, name
from room;

/* 복잡한 형식으로 출력할 경우 라벨명(별명)을 부여한다.
    예) 강의실명(지점명)*/
select concat(name, '(', loc, ')')
from room;

select concat(name, '(', loc, ')') title /*(as) title 생략 가능 */
from room;

select count(*)
from room;

select count(*) cnt
from room;

select count(*) cnt
from room
where loc='서초';

/* count()를 호출할 때 컬럼 이름을 지정하면
   해당 컬럼의 값이 null 이 아닌 데이터만 카운트한다. */
select count(mno) cnt
from lect;

/* union 과 union all */

/* select 결과 합치기
   union : 중복 값 자동 제거, 합집합*/
select distinct bank from stnt
union
select distinct bank from tcher;

/* union all: 중복 값 제거 안함*/
select distinct bank from stnt
union all
select distinct bank from tcher;

/* 차집합
   mysql 은 차집합 문법을 지원하지 않는다.
   따라서 다음과 기존의 SQL 문법을 사용해서 처리해야 한다.
*/
select distinct bank
from stnt
where not bank in (select distinct bank from tcher);

/* 교집합
   mysql 은 교집합 문법을 지원하지 않는다.
   따라서 다음과 기존의 SQL 문법을 사용해서 처리해야 한다.
*/
select distinct bank
from stnt
where bank in (select distinct bank from tcher);

/* 조인
=> 서로 관련된 테이블의 데이터를 연결하여 추출하는 방법
=> 기법
1) CROSS 조인(=Cartesian product)
2) NATURAL 조인
3) JOIN ~ USING(컬럼명)
4) JOIN ~ ON
5) OUTER JOIN
*/

-- 1) CROSS 조인(=Cartesian product) - 두 테이블의 데이터를 1:1로 모두 연결한다.
create table board1 (
  bno int primary key auto_increment,
  title varchar(255) not null,
  content text
);

create table attach_file1 (
  fno int primary key auto_increment,
  filepath varchar(255) not null,
  bno int not null
);
alter table attach_file1
  add constraint attach_file1_fk foreign key (bno) references board1 (bno);

insert into board1 values(1, '제목1', '내용');
insert into board1 values(2, '제목2', '내용');
insert into board1 values(3, '제목3', '내용');

insert into attach_file1 values(101, 'a1.gif', 1);
insert into attach_file1 values(102, 'a2.gif', 1);
insert into attach_file1 values(103, 'c1.gif', 3);

select bno, title from board1;
select fno, filepath, bno from attach_file1;

-- bno 컬럼이 두 테이블에 모두 존재한다.
-- 따라서 어떤 테이블의 컬럼인지 지정하지 않으면 실행 오류!
select bno, title, content, fno, filepath
from board1 cross join attach_file1;

-- select 컬럼이 두 테이블에 모두 있을 경우,
-- 컬럼명 앞에 테이블명을 명시하여 구분하라!
select board1.bno, title, content, fno, filepath, attach_file1.bno
from board1 cross join attach_file1;

-- cross join 고전 문법 (그냥 곱하기임)*/
select board1.bno, title, content, fno, filepath, attach_file1.bno
from board1, attach_file1;

-- 컬럼명 앞에 테이블명을 붙이면 너무 길다.
-- 테이블에 별명을 부여하고 그 별명을 사용하여 컬럼을 지정하라.
select b.bno, title, content, fno, filepath, a.bno
from board1 as b cross join attach_file1 as a;

-- as는 생략 가능
select b.bno, title, content, fno, filepath, a.bno
from board1 b cross join attach_file1 a;

-- 고전 문법
select b.bno, title, content, fno, filepath, a.bno
from board1 b, attach_file1 a;

/* 조인
=> 서로 관련된 테이블의 데이터를 연결하여 추출하는 방법
=> 기법
1) CROSS 조인(=Cartesian product)
2) NATURAL 조인 : 컬럼 명이 일치하는 경우, 조인의 조건으로 사용할 컬럼 외 같은 이름의 컬럼이 없어야 한다.
3) JOIN ~ USING(컬럼명)
4) JOIN ~ ON
5) OUTER JOIN
*/

-- 2) NATURAL 조인
--    같은 이름을 가진 컬럼 값을 기준으로 레코드를 연결한다.
select b.bno, title, content, fno, filepath, a.bno
from board1 b natural join attach_file1 a;

-- 고전 문법
select b.bno, title, content, fno, filepath, a.bno
from board1 b, attach_file1 a
where b.bno = a.bno;

-- natural join 의 문제점
-- 가. 두 테이블의 조인 기준이 되는 컬럼 이름이 다를 때 연결되지 못한다.
-- 나. 상관 없는 컬럼과 이름이 같을 때 잘못 연결된다.
-- 다. 같은 이름의 컬럼이 여러 개 있을 경우 잘못 연결된다.
--    모든 컬럼의 값이 일치할 경우에만 연결되기 때문이다.

-- 가. 두 테이블의 조인 기준이 되는 컬럼 이름이 다를 때:
--
create table board2 (
  no int primary key auto_increment,
  title varchar(255) not null,
  content text
);

create table attach_file2 (
  fno int primary key auto_increment,
  filepath varchar(255) not null,
  bno int not null
);
alter table attach_file2
  add constraint attach_file2_fk foreign key (bno) references board2 (no);

insert into board2 values(1, '제목1', '내용');
insert into board2 values(2, '제목2', '내용');
insert into board2 values(3, '제목3', '내용');

insert into attach_file2 values(101, 'a1.gif', 1);
insert into attach_file2 values(102, 'a2.gif', 1);
insert into attach_file2 values(103, 'c1.gif', 3);

-- natural join의 기준이 되는 같은 이름을 가진 컬럼이 양 테이블에 존재하지 않는다.
-- => cross join 처럼 실행된다.
select no, title, content, fno, filepath, bno
from board2 b natural join attach_file2 a;

-- 고전 문법 :
-- 고전 문법에서는 where 절의 조건으로 두 테이블의 조인 기준이 되는 컬럼 값을 검사하기 때문에
-- 실행 결과는 정상적으로 나온다.
select no, title, content, fno, filepath, bno
from board2 b, attach_file2 a
where b.no = a.bno;

-- 나. 같은 이름을 가진 컬럼이 있지만 서로 상관(PK와 FK 관계)이 없는 컬럼일 때:
--
create table board3 (
  no int primary key auto_increment,
  title varchar(255) not null,
  content text
);

create table attach_file3 (
  no int primary key auto_increment,
  filepath varchar(255) not null,
  bno int not null
);
alter table attach_file3
  add constraint attach_file3_fk foreign key (bno) references board3 (no);

insert into board3 values(1, '제목1', '내용');
insert into board3 values(2, '제목2', '내용');
insert into board3 values(3, '제목3', '내용');

insert into attach_file3 values(1, 'a1.gif', 1);
insert into attach_file3 values(2, 'a2.gif', 1);
insert into attach_file3 values(3, 'c1.gif', 3);

-- board3의 no와 attach_file3의 no는 PK/FK 관계가 아니다.
-- 그럼에도 불구하고 이름이 같기 때문에 이 컬럼을 기준으로 데이터를 연결한다.
select b.no, title, content, a.no, filepath, bno
from board3 b natural join attach_file3 a;

-- 고전 문법 :
-- 고전 문법에서는 where 절의 조건으로 두 테이블의 조인 기준이 되는 컬럼 값을 검사하기 때문에
-- 실행 결과는 정상적으로 나온다.
select b.no, title, content, a.no, filepath, bno
from board3 b, attach_file3 a
where b.no = a.bno;

-- 다. 같은 이름을 가진 컬럼이 여러 개 있을 때:
--
create table board4 (
  bno int primary key auto_increment,
  title varchar(255) not null,
  content text
);

create table attach_file4 (
  fno int primary key auto_increment,
  title varchar(255) not null,
  bno int not null
);
alter table attach_file4
  add constraint attach_file4_fk foreign key (bno) references board4 (bno);

insert into board4 values(1, '제목1', '내용');
insert into board4 values(2, '제목2', '내용');
insert into board4 values(3, '제목3', '내용');

insert into attach_file4 values(1, 'a1.gif', 1);
insert into attach_file4 values(2, 'a2.gif', 1);
insert into attach_file4 values(3, 'c1.gif', 3);

-- board4와 attach_file4에 같은 이름을 가진 컬럼이 여러 개 있다.
-- 해당 컬럼들의 값이 같을 때만 두 테이블의 데이터를 연결한다.
-- 따라서 실행 결과 데이터는 없을 것이다.
select b.bno, b.title, content, a.fno, a.title, a.bno
from board4 b natural join attach_file4 a;

-- 고전 문법 :
-- 고전 문법에서는 where 절의 조건으로 두 테이블의 조인 기준이 되는 컬럼 값을 검사하기 때문에
-- 실행 결과는 정상적으로 나온다.
select b.bno, b.title, content, a.fno, a.title, a.bno
from board4 b, attach_file4 a
where b.bno = a.bno;

/* 조인
=> 서로 관련된 테이블의 데이터를 연결하여 추출하는 방법
=> 기법
1) CROSS 조인(=Cartesian product)
2) NATURAL 조인
3) JOIN ~ USING(컬럼명)
4) JOIN ~ ON
5) OUTER JOIN
*/

-- 3) JOIN ~ USING
--    같은 이름을 가진 컬럼이 여러 개 있을 경우 USING을 사용하여 컬럼을 명시할 수 있다.
select b.bno, b.title, content, a.fno, a.title, a.bno
from board4 b join attach_file4 a using (bno);

-- join ~ using 의 한계
-- => 두 테이블에 같은 이름의 컬럼이 없을 경우 연결하지 못한다.

create table board5 (
  no int primary key auto_increment,
  title varchar(255) not null,
  content text
);

create table attach_file5 (
  fno int primary key auto_increment,
  filepath varchar(255) not null,
  bno int not null
);
alter table attach_file5
  add constraint attach_file5_fk foreign key (bno) references board5 (no);

insert into board5 values(1, '제목1', '내용');
insert into board5 values(2, '제목2', '내용');
insert into board5 values(3, '제목3', '내용');

insert into attach_file5 values(1, 'a1.gif', 1);
insert into attach_file5 values(2, 'a2.gif', 1);
insert into attach_file5 values(3, 'c1.gif', 3);

-- 두 테이블의 데이터를 연결할 때 기준이 되는 컬럼이 이름이 같지 않다.
-- 이런 경우 using을 사용할 수 없다. 실행 오류!
select no, title, content, fno, filepath, bno
from board5 b join attach_file5 a using (bno);

/* 조인
=> 서로 관련된 테이블의 데이터를 연결하여 추출하는 방법
=> 기법
1) CROSS 조인(=Cartesian product)
2) NATURAL 조인
3) JOIN ~ USING(컬럼명)
4) JOIN ~ ON
5) OUTER JOIN
*/

-- 4) JOIN ~ ON
--    조인 조건을 on에 명시할 수 있다.
select no, title, content, fno, filepath, bno
from board5 b join attach_file5 a on b.no=a.bno;

-- 조건에 일치하는 경우에만 두 테이블의 데이터를 연결한다.
-- 이런 조인을 'inner join' 이라 부른다.
-- SQL 문에서도 inner join 이라 기술할 수 있다.
-- 물론 inner를 생략할 수도 있다.
select no, title, content, fno, filepath, bno
from board5 b inner join attach_file5 a on b.no=a.bno;

/* [inner] join ~ on 의 문제점
   => 반드시 on 에서 지정한 컬럼의 값이 같을 경우에만
      두 테이블의 데이터가 연결된다.
   => 같은 값을 갖는 데이터가 없다면 연결되지 않고, 결과로 출력되지 않는다.
   => 위 SQL의 실행 결과를 보라!
      첨부파일이 없는 2번 게시글은 결과에 포함되지 않는다.

*/

-- 5) OUTER JOIN
--    조인 조건에 일치하는 데이터가 없더라도 두 테이블 중에서 한 테이블의 데이터를
--    결과로 포함시키는 명령이다.
--    문법:
--         select  컬럼명, 컬럼명, ...
--         from 테이블1 t1 [left|right] outer join 테이블2 t2 on t1.컬럼=t2컬럼
--    left outer join => 왼쪽 테이블의 데이터는 반드시 포함시키라는 뜻이다.
--    right outer join => 오른쪽 테이블의 데이터를 반드시 포함시키는 뜻이다.
--
select no, title, content, fno, filepath, bno
from board5 b left outer join attach_file5 a on b.no=a.bno
order by no desc;

-- 실무
-- 1) 여러 테이블을 조인하여 컬럼을 projection 할 때
--    각 컬럼이 어떤 테이블의 컬럼인지 명시한다.
-- 2) 컬럼을 나열할 때 한 줄에 한 컬럼을 나열한다.
select
  b.no,
  b.title,
  b.content,
  a.fno,
  a.filepath,
  a.bno
from board5 b
  left outer join attach_file5 a on b.no=a.bno
order by
  no desc;

-- [inner join의 문제점 예1]
-- 1) 전체 강의 목록
select lno, titl, rno, mno from lect;

-- 2) 전체 강의실 목록
select rno, loc, name from room;

-- 3) 강의 정보를 출력할 때 센터 이름과 강의실 이름도 함께 출력해 보자!
--    강의 테이블(lect)에서 강의명을 가져오고,
--    강의실 테이블(room)에서 지점명과 강의실명을 가져오자.
select
    l.lno,
    l.titl,
    l.rno,
    r.rno,
    r.loc,
    r.name
from lect l
    inner join room r on l.rno=r.rno;
/* inner join의 문제는 위의 경우처럼
   강의실이 아직 지정되지 않은 강의의 경우 강의실 테이블의 데이터와 연결하지 못해
   결과로 출력되지 않는 문제가 있다. */

-- [inner join의 문제점 예2]
-- 1) 모든 강의장 이름을 출력하라.
--    단 강의장에 강의가 배정된 경우 그 강의 이름도 출력하라.
--
select
  r.rno,
  r.name,
  r.loc,
  l.titl
from room r
    inner join lect l on r.rno = l.rno;

-- 위의 경우 처럼 만약 기준 컬럼의 값과 일치하는 데이터가 없어서
-- 다른 테이블의 데이터와 연결되지 않았다 하더라도
-- 결과로 뽑아내고 싶다면 outer join을 사용하라!
-- 즉 아직 강의실이 배정되지 않은 강의 데이터도 출력하고 싶을 때
-- 출력하고 싶은 테이블을 바깥쪽 테이블로 지정하라!

select
    l.lno,
    l.titl,
    r.rno,
    r.loc,
    r.name
from lect l
    left outer join room r on l.rno=r.rno;
-- 왼쪽 테이블인 lect를 기준으로 room 데이터를 연결한다.
-- 만약 lect와 일치하는 데이터가 room에 없더라도
-- lect 데이터를 출력한다!

select
    l.lno,
    l.titl,
    r.rno,
    r.loc,
    r.name
from lect l
    right outer join room r on l.rno=r.rno;
-- 오른쪽 테이블인 room을 기준으로 lect데이터를 연결한다.
-- 만약 room과 일치하는 데이터가 lect에 없더라도
-- room 데이터를 출력한다!

8-6 해보기

9-1

가로: 레코드, 튜플, 로우

세로: 컬럼, attribute

서브쿼리: where나 from에 들어가면 성능이 안 좋다함 .

/* 서브 쿼리
=> 쿼리문 안에 쿼리문을 실행하는 기법
=> 성능 문제를 생각하면서 사용해야 한다.
*/

/* join이용하여 데이터를 추출한 방법 */
select la.lano, l.titl, m.name, s.work, la.rdt, r.name, m2.name, mr.posi
from lect_appl la
        inner join memb m on la.mno=m.mno
        inner join stnt s on la.mno=s.mno
        inner join lect l on la.lno=l.lno
        left outer join room r on l.rno=r.rno
        left outer join memb m2 on l.mno=m2.mno
        left outer join mgr mr on l.mno=mr.mno;

/* select 절에 서브쿼리 사용하기 */

/* 수강신청 데이터를 출력 */
select
    la.lano,
    la.lno,
    la.mno,
    la.rdt
from lect_appl la;

/* => 1단계: 수강신청 데이터를 출력 */
select
  la.lano,
  la.lno,
  la.mno,
  date_format(la.rdt, '%m/%d/%Y') reg_dt
from lect_appl la;

/* => 2단계 : 서브 쿼리를 이용하여 강의명을 가져오기
   - 단, 컬럼 자리에 사용할 때는 결과 값이 한 개여야 한다.
   - 결과 값이 여러 개가 리턴된다면 컬럼 값으로 사용할 수 없기 때문에 오류이다.
   - 또한 컬럼 개수도 한 개여야 한다.
*/
select
    la.lano,
    (select titl from lect where lno=la.lno) as lect_title,
    la.mno,
    la.rdt
from lect_appl la;

/* => 3단계 : 서브 쿼리를 이용하여 학생명을 가져오기 */
select
    la.lano,
    (select titl from lect where lno=la.lno) as lect_title,
    (select name from memb where mno=la.mno) as stud_name,
    la.rdt
from lect_appl la;

/* from 절에 서브쿼리 사용하기 */
/* 0단계 : 강의 정보를 가져온다. */
select
    l.lno,
    l.titl,
    l.rno,
    l.mno
from lect l;

/* 1단계 : 강의 상세 정보를 가져오는 select를 준비한다.
    => 서브 쿼리를 이용하여 강의실 이름과 매니저 이름, 직위 정보를 가져오기 */
select
    l.lno,
    l.titl,
    (select name from room where rno=l.rno) as room_name,
    (select name from memb where mno=l.mno) as manager_name,
    (select posi from mgr where mno=l.mno) as manager_posi
from lect l;

/* 2단계: 위에서 준비한 select 결과를 가상 테이블로 사용하여
             기존의 lect_appl 테이블과 조인한다.*/
select
    la.lano,
    /*(select titl from lect where lno=la.lno) as lect_title,*/
    (select name from memb where mno=la.mno) as stud_name,
    lec.titl,
    lec.room_name,
    lec.manager_name,
    lec.manager_posi
from lect_appl la
    join (select
                l.lno,
                l.titl,
                (select name from room where rno=l.rno) as room_name,
                (select name from memb where mno=l.mno) as manager_name,
                (select posi from mgr where mno=l.mno) as manager_posi
            from lect l) as lec on la.lno=lec.lno;

/* lect_appl 테이블 대신에 서브 쿼리의 결과를 테이블로 사용할 수 있다. */
select
    la2.lano,
    la2.rdt,
    la2.sname,
    la2.work,
    l2.titl,
    l2.rname,
    l2.mname,
    l2.posi
from (
        select
            la.lano,
            la.lno,
            la.rdt,
            m.name sname,
            s.work
        from lect_appl la
            inner join memb m on la.mno=m.mno
            inner join stnt s on la.mno=s.mno) la2
     inner join (
        select
            l.lno,
            l.titl,
            r.name rname,
            m.name mname,
            mr.posi
        from lect l
            left outer join room r on l.rno=r.rno
            left outer join memb m on l.mno=m.mno
            left outer join mgr mr on l.mno=mr.mno
     ) l2 on la2.lno=l2.lno;

/* from 절에서 반복적으로 사용하는 서브 쿼리가 있다면,
* 차라리 가상 테이블인 view로 정의해놓고 사용하는 것이 편하다.
*/
create view lect2 as
select
    l.lno,
    l.titl,
    (select name from room where rno=l.rno) as room_name,
    l.mno as manager_no,
    (select name from memb where mno=l.mno) as manager_name,
    (select posi from mgr where mno=l.mno) as manager_posi
from lect l;

/* 위의 질의문을 view를 사용하여 다시 작성해보자! */
select
    la.lano,
    (select name from memb where mno=la.mno) as stud_name,
    lec.titl,
    lec.room_name,
    lec.manager_name,
    lec.manager_posi
from lect_appl la
    join lect2 lec on la.lno=lec.lno;

/* where 절에 서브쿼리 사용하기 */

/* 과장 또는 대리 매니저가 담당하고 있는 수강 신청만 추출하기 */
select
    la.lano,
    /* (select titl from lect where lno=la.lno) as lect_title, */
    (select name from memb where mno=la.mno) as stud_name,
    lec.titl,
    lec.room_name,
    /* lec.manager_no, */
    lec.manager_name,
    lec.manager_posi
from lect_appl la
    join lect2 as lec on la.lno=lec.lno
where
    lec.manager_no in (select mno from mgr where posi in ('과장', '주임'));

-- 서브쿼리 예1 : select 절에 서브쿼리를 둘 때 + where 절에 서브쿼리를 둘 때
select
  la.lano,
  (select titl from lect where lno=la.lno) lect_title,
  (select name from memb where mno=la.mno) student_name,
  (select work from stnt where mno=la.mno) student_work,
  date_format(la.rdt, '%Y-%m-%d') reg_date,
  ifnull((select name from room where rno=(select rno from lect where lno=la.lno)), '') room_name,
  ifnull((select name from memb where mno=(select mno from lect where lno=la.lno)), '') mgr_name,
  ifnull((select posi from mgr where mno=(select mno from lect where lno=la.lno)), '') mgr_posi
from lect_appl la;

-- 서브쿼리 예2 : from 절에 서브쿼리를 둘 때
-- 1) 강의 정보
select
  l.lno lect_no,
  l.titl lect_title,
  ifnull((select name from room where rno=l.rno), '') room_name,
  ifnull((select name from memb where mno=l.mno), '') mgr_name,
  ifnull((select posi from mgr where mno=l.mno), '') mgr_posi
from
  lect l;

-- 2) 수강생 정보
select
  s.mno std_no,
  (select name from memb where mno=s.mno) std_name,
  s.work std_work
from
  stnt s;

-- 3) 수강신청 정보
select
  la.lano,
  la.lno,
  la.mno,
  to_char(la.rdt, 'YYYY-MM-DD') reg_date
from lect_appl la;

-- 4) 수강신청 정보 + 강의 정보 + 수강생 정보
select
  la.lano,
  le.lect_no,
  le.lect_title,
  le.room_name,
  le.mgr_name,
  le.mgr_posi,
  st.std_no,
  st.std_name,
  st.std_work,
  to_char(la.rdt, 'YYYY-MM-DD') reg_date
from lect_appl la
  inner join (
      select
        l.lno lect_no,
        l.titl lect_title,
        ifnull((select name from room where rno=l.rno), '') room_name,
        ifnull((select name from memb where mno=l.mno), '') mgr_name,
        ifnull((select posi from mgr where mno=l.mno), '') mgr_posi
      from
        lect l
    ) le on la.lno=le.lect_no
  inner join (
    select
      s.mno std_no,
      (select name from memb where mno=s.mno) std_name,
      s.work std_work
    from
      stnt s
    ) st on la.mno=st.std_no;

--

/* 데이터를 특정 컬럼을 기준으로 그룹으로 묶어 질의하기
=> group by ~ having ~
*/

/* 각 지점별 강의실 수 구하기*/
-- 1단계: 강의실 목록 구하기
select
    r.rno,
    r.loc,
    r.name
from
    room r;

-- 2단계: 지점정보를 저장한 컬럼을 기준으로 그룹으로 묶는다.
select
    r.rno, -- 그룹으로 묶인 경우 그 그룹의 첫 번째 데이터 값만 출력한다.
    r.loc,
    r.name -- 그룹으로 묶인 경우 그 그룹의 첫 번째 데이터 값만 출력한다.
from
    room r
group by
    r.loc;

-- 3단계: group by를 통해 데이터를 그룹으로 묶은 경우
--        개별 항목의 값을 나타내는 컬럼의 값은 의미가 없기 때문에 제거한다.
select
    r.loc
from
    room r
group by
    r.loc;

-- 4단계: 그룹으로 묶은 경우 그룹 관련 함수를 사용할 수 있다.
select
    r.loc,
    count(*) as cnt
from
    room r
group by
    r.loc;

-- 5단계: group by의 결과에서 최종 결과를 선택할 조건을 지정하고 싶다면
--       having절을 사용한다.

-- 예1) having 절에서 집합 함수 사용
select
    r.loc,
    count(*) as cnt
from
    room r
group by
    r.loc
having
    count(*) > 3; -- 집합 함수, group by 조건 컬럼, select 절의 컬럼 사용 가능

-- 예2) having 절에서 group by 조건 컬럼 사용
select
    count(*) as cnt
from
    room r
group by
    r.loc
having
    r.loc = '강남'; -- 집합 함수, group by 조건 컬럼, select 절의 컬럼 사용 가능

-- 예3) having 절에서 select 절 컬럼 사용
select
    count(*) as cnt
from
    room r
group by
    r.loc
having
    cnt > 3; -- 집합 함수, group by 조건 컬럼, select 절의 컬럼 사용 가능

select
    r.name, -- MySQL 8.x에서는 일반 컬럼을 지정할 수 없다.
    count(*) as cnt
from
    room r
group by
    r.loc
having
    r.name = '302'; -- 집합 함수, group by 조건 컬럼, select 절의 컬럼 사용 가능

-- 오류) select 절에 선언되지 않은 컬럼을 지정할 수 없다.
select
    r.loc,
    count(*) as cnt
from
    room r
group by
    r.loc
having
    r.name = '302'; -- 집합 함수, group by 조건 컬럼, select 절의 컬럼 사용 가능

-- 실행 순서: from --> where --> group by --> select --> having --> order by
-- => MySQL이 아닌 다른 DBMS에서 실행하여 실행 순서를 확인할 필요가 있음!
select
    'okok' as test,
    r.loc as location,
    count(*) as cnt
from
    room r
where
    r.loc <> '강남'
group by
    r.loc
having
    cnt > 2 and test = 'okok'
order by
    cnt desc;

저작자표시

'[네이버클라우드] 클라우드 기반의 개발자 과정 7기 > 웹프로그래밍' 카테고리의 다른 글

[NC7기-63일차(7월24일)] - 웹프로그래밍 44일차 (0)	2023.07.24
[NC7기-62일차(7월21일)] - 웹프로그래밍 43일차 (1)	2023.07.21
[NC7기-60일차(7월19일)] - 웹프로그래밍 41일차 (0)	2023.07.19
[NC7기-59일차(7월18일)] - 웹프로그래밍 40일차 (0)	2023.07.18
[NC7기-58일차(7월17일)] - 웹프로그래밍 39일차 (0)	2023.07.17

현재글[NC7기-61일차(7월20일)] - 웹프로그래밍 42일차

코드의 나라에서의 일상