Sql 05

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;

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

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

/* 이름의 내림 차순으로 정렬하기 */ 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;

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 from room;

select count(*) from room;

select count(*) cnt from room;

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;

조인

=> 서로 관련된 테이블의 데이터를 연결하여 추출하는 방법 => 기법 1) CROSS 조인 2) NATURAL 조인 3) JOIN ~ ON 4) OUTER JOIN

/* cross join : 두 테이블의 데이터를 1:1로 모두 연결한다.*/ select mno, name from memb; select mno, work, bank from stnt;

/* => mno가 어떤 테이블의 컬럼인지 지정하지 않으면 실행 오류!*/ select mno, name, mno, work, bank from memb cross join stnt;

/* => select 컬럼이 두 테이블 모두 있을 경우, 컬럼명 앞에 테이블명을 명시하여 구분하라!*/ select memb.mno, name, stnt.mno, work, bank from memb cross join stnt;

/* 예전 문법 */ select memb.mno, name, stnt.mno, work, bank from memb, stnt;

/* => 컬럼명 앞에 테이블명을 붙이면 너무 길다. 테이블에 별명을 부여하고 그 별명을 사용하여 컬럼을 지정하라. */ select m.mno, name, s.mno, work, bank from memb m cross join stnt s;

/* 예전 문법 */ select m.mno, name, s.mno, work, bank from memb m, stnt s;

/* natural join: 같은 이름을 가진 컬럼 값을 기준으로 레코드를 연결한다. */ select m.mno, name, s.mno, work, bank from memb m natural join stnt s;

/* 예전 문법 */ select m.mno, name, s.mno, work, bank from memb m, stnt s where m.mno=s.mno;

/* natural join 의 문제점

• 1) 두 테이블의 조인 기준이 되는 컬럼 이름이 다를 때 연결되지 못한다. 2) 상관 없는 컬럼과 이름이 같을 때 잘못 연결된다. 3) 같은 이름의 컬럼이 여러 개 있을 경우 잘못 연결된다. 모든 컬럼의 값이 일치할 경우에만 연결되기 때문이다. */

/* 만약에 두 테이블에 같은 이름을 가진 컬럼이 여러 개 있다면, join ~ using (기준컬럼) 을 사용하여 두 테이블의 데이터를 연결할 때 기준이 될 컬럼을 지정한다.*/ select m.mno, name, s.mno, work, bank from memb m join stnt s using (mno);

/* natural join 의 문제점 2
=> 두 테이블에 같은 이름의 컬럼이 없을 경우 연결하지 못한다.*/

/* 만약 두 테이블에 같은 이름을 가진 컬럼이 없으면, natural join을 수행하지 못한다. 또한 join using 으로도 해결할 수 없다. 이럴 경우 join ~ on 컬럼a=컬럼b 문법을 사용하여 각 테이블의 어떤 컬럼과 값을 비교할 것인지 지정하라!*/ select m.mno, name, s.mno, work, bank from memb m inner join stnt s on m.mno=s.mno;

/* inner는 생략 가능하다 / select m.mno, name, s.mno, work, bank from memb m join stnt s on m.mno=s.mno; / 즉 inner join 은 기준 컬럼의 값이 일치할 때만 데이터를 연결한다. */

/* 예전의 조인 문법 = inner join */ select m.mno, name, s.mno, work, bank from memb m, stnt s where m.mno=s.mno;

/* [inner] join ~ on 의 문제점 => 반드시 on 에서 지정한 컬럼의 값이 같을 경우에만 두 테이블의 데이터가 연결된다. => 같은 값을 갖는 데이터가 없다면 연결되지 않고, 결과로 출력되지 않는다. / / 전체 강의 목록 */ select lno, titl, rno, mno from lect;

/* 전체 강의실 목록 */ select rno, loc, name from room;

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

/* inner join의 문제점 예2:

• 모든 강의장 이름을 출력하라.
• 단 강의장에 강의가 배정된 경우 그 강의 이름도 출력하라. */ 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 right outer join room r on l.rno=r.rno; / 왼쪽 테이블인 lect를 기준으로 room 데이터를 연결한다.

• 만약 lect와 일치하는 데이터가 room에 없더라도
• lect 데이터를 출력한다! */

/* 요구사항: 모든 멤버의 번호와 이름을 출력하라!
단 학생의 경우 재직여부도 출력하라!*/

– 1) 모든 멤버 데이터 출력하기 select mno, name from memb;

– 2) 학생 데이터를 가져와서 연결하기 select mno, name, work from memb natural join stnt;

select mno, name, work from memb join stnt using(mno);

select memb.mno, name, work from memb, stnt where memb.mno=stnt.mno;

select memb.mno, name, work from memb inner join stnt on memb.mno=stnt.mno;

select memb.mno, name, work from memb join stnt on memb.mno=stnt.mno;

select m.mno, name, work from memb m join stnt s on m.mno=s.mno;

/* 안타깝게도 위의 SQL문은 학생 목록만 출력한다. 왜? memb테이블의 데이터와 stnt 테이블의 데이터를 연결할 때 mno가 같은 데이터만 연결하여 추출하기 때문이다. 해결책!
상대 테이블(stnt)에 연결할 대상(데이터)이 없더라도 select에서 추출하는 방법 */ select m.mno, name, work from memb m left outer join stnt s on m.mno=s.mno;

/* 여러 테이블의 데이터를 연결하기 => 다음의 결과가 출력될 수 있도록 수강 신청 데이터를 출력하시오! 수강신청번호, 강의명, 학생명, 재직여부, 수강신청일, 강의실명, 매니저명, 직위 */

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

/* 2단계: 수강신청한 학생의 번호 대신 이름을 출력 */ select la.lano, la.lno, m.name, la.rdt from lect_appl la inner join memb m on la.mno=m.mno;

/* 3단계: 수강 신청한 학생의 재직 여부 출력

• => inner join 에서 inner는 생략 가능 */ select la.lano, la.lno, m.name, s.work, la.rdt from lect_appl la join memb m on la.mno=m.mno join stnt s on la.mno=s.mno;

/* 4단계: 수상신청한 강의 번호 대신 강의명을 출력 */ select la.lano, l.titl, m.name, s.work, la.rdt, l.rno from lect_appl la join memb m on la.mno=m.mno join stnt s on la.mno=s.mno join lect l on la.lno=l.lno;

/* 5단계: 강의실 이름을 출력한다.

• => 강의실 번호는 lect 테이블 데이터에 있다.
• => 강의실 이름은 room 테이블 데이터에 있다. */ select la.lano, l.titl, m.name, s.work, la.rdt, r.name, l.mno from lect_appl la join memb m on la.mno=m.mno join stnt s on la.mno=s.mno join lect l on la.lno=l.lno left outer join room r on l.rno=r.rno;

/* 6단계: 매니저 이름을 출력

• => 매니저 번호는 lect 테이블에 있다.
• => 매니저 이름은 memb 테이블에 있다. */ select la.lano, l.titl, m.name member_name, s.work, la.rdt, r.name room_name, m2.name manager_name from lect_appl la join memb m on la.mno=m.mno join stnt s on la.mno=s.mno 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;

/* 7단계: 매니저의 직위 출력

• => 매니저 번호는 lect 테이블 있다.
• => 매니저 직위는 mgr 테이블에 있다.
  */ select la.lano, l.titl, m.name, s.work, la.rdt, r.name, m2.name, mr.posi from lect_appl la join memb m on la.mno=m.mno join stnt s on la.mno=s.mno 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;

서브 쿼리

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

/* 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 절에 서브쿼리 사용하기 */

/* => 1단계: 수강신청 데이터를 출력 */ select la.lano, la.lno, la.mno, date_format(la.rdt, ‘%Y-%m-%d’) 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;

/* 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 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 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 (‘과장’, ‘주임’));