📂 방학공부
└📁 모각코
└📁 HTML
└📁 CSS
└📁 jQuery
└📁 일기장
모각코 plus - MySQL
13. 2개 이상의 테이블 사용하기(JOIN)
하나의 웹서비스를 운영하려면 여러개의 테이블을 사용한다.
이 절에서는 2개 이상의 테이블을 다루는 방법에 대해 알아보겠다.
어떠한 상품의 리뷰 정보를 담는 테이블을 생성하기 위해서 아래의 명령문을 사용해보자.
create table review(
prodReviewID int unsigned auto_increment comment '리뷰의 고유 번호',
myMemberID int unsigned comment '리뷰를 작성한 회원번호',
content tinytext comment '리뷰 내용',
regTime datetime not null comment '리뷰 작성 날짜',
primary key(prodReviewID))
charset=utf8 comment='상품 리뷰';
desc명령문을 통해서 잘 생성된 것을 확인할 수 있다.
이제 이 테이블에 들어갈 내용을 임의로 입력해 보겠다.
insert into review(myMemberID, content, regTime)
values(2, '너무 맛있어요.', now());
insert into review(myMemberID, content, regTime)
values(3, '실제로 보니 양이 많아요.', now());
insert into review(myMemberID, content, regTime)
values(4, '맛있기는 하나 비싸요.', now());
insert into review(myMemberID, content, regTime)
values(5, '친구에게 먹어보라고 추천했어요!', now());
insert into review(myMemberID, content, regTime)
values(6, '비싸나 먹을 가치가 있어요.', now());
insert into review(myMemberID, content, regTime)
values(7, '벌써 다 먹어서 하나 더 주문합니다.', now());
데이터 확인하는 명령문을 사용해 확인한 입력이 완료된 상태이다.
이 두개의 테이블을 아래의 join 명령문을 통해서 연결을 해줄 수 있다.
select 필드명 from 테이블명 앨리어스 join 연결할 테이블명 앨리어스 on(두 테이블의 연결고리 역할을 할 필드 조건문)
앨리어스는 테이블명의 별명을 짓는 것이다.
join을 위해서 미리 만들어놓았던 mymember 테이블을 짧게m으로 앨리어스 하고, review 테이블을r로 앨리어스 해보자.
on은 테이블의 공통값을 갖는 필드를 기입한다.
mymember과 review테이블에서의 공통값은 myMemberID이므로 여기서는 myMemberID를 기입해야 한다.
그럼 2개의 테이블을 이용하여 아래의 명령문을 통해 사람의 이름, 리뷰, 리뷰를 남긴 시간을 출력해 보자.
select m.name, r.content, r.regTime from mymember m join review r on(m.myMemberID = r.myMemberID);
명령문을 실행하면 위와 같은 결과가 나온다.
14. 집계함수
집계함수는 레코드의 수, 값들의 합, 평균, 최댓갑, 최솟값을 구하는 함수이다.
먼저 집계함수를 활용하기 위해 학생 성적을 담는 테이블을 생성해보자.
create table record (
studentID int(10) unsigned not null auto_increment comment '학생 번호',
class tinyint unsigned comment '소속 클래스(반)',
english tinyint unsigned not null comment '영어 점수',
math tinyint unsigned not null comment '수학 점수',
science tinyint unsigned not null comment '과학 점수',
history tinyint unsigned not null comment '역사 점수',
coding tinyint unsigned not null comment '코딩 점수',
primary key (studentID))
charset=utf8 comment='성적 정보';테이블을 생성했으니, 학생들의 성적 정보를 입력해보자.
insert into record(english, math, science, history, coding)
values(67, 4, 20, 100, 100);
insert into record(class, english, math, science, history, coding)
values(1, 55, 60, 80, 50, 100);
insert into record(class, english, math, science, history, coding)
values(2, 100, 90, 100, 50, 100);
insert into record(class, english, math, science, history, coding)
values(2, 90, 86, 100, 70, 100);
insert into record(class, english, math, science, history, coding)
values(3, 80, 50, 60, 20, 100);
insert into record(class, english, math, science, history, coding)
values(3, 100, 80, 70, 20, 80);
insert into record(class, english, math, science, history, coding)
values(4, 100, 100, 100, 30, 40);
데이터를 만들었으니 집계함수에 대해 알아보자.
14-1) 집계함수의 종류
| 종류 | 뜻 |
|---|---|
| count(필드명) | 레코드의 개수를 표시(값이 null인 경우 포함 X) |
| count( * ) | 레코드의 개수를 표시(값이 null인 경우 포함) |
| sum(필드명) | 필드의 값의 합을 표시 |
| avg(필드명) | 필드의 값의 평균을 표시 |
| max(필드명) | 필드의 값의 최댓값을 표시 |
| min(필드명) | 필드의 값의 최솟값을 표시 |
14-2) count( ) 함수
coutn() 함수는 레코드의 개수를 표시하는 함수이다.
select count(class) from record;count() 함수에 class 필드를 넣었기 때문에 class에서 null값을 제외한 레코드의 수를 표시한다.
select count( * ) from record;count() 함수에 * 를 넣었기 때문에 null값을 포함한 레코드 수를 표시한다.
14-3) sum( ) 함수
sum() 함수는 필드의 값의 합을 표시하는 함수이다.
모든 학생의 코딩 점수 합계를 표시해보겠다.
select sum(coding) from record;
명령문 뒤에 where을 사용하여 조건을 달아 특정 레코드들의 합을 표시할 수 있다.
14-4) avg( ) 함수
avg() 함수는 필드의 값의 평균을 표시하는 함수이다.
번호가 1번부터 5번까지인 학생의 영어 점수의 평균값을 표시해보겠다.
select avg(english) from record where studentID >= 1 and studentID <= 5;
14-5) max( ) 함수
max() 함수는 필드의 값의 최댓값을 표시하는 함수이다.
가장 높은 수학 점수를 표시해보겠다.
select max(math) from record;
14-6) min( ) 함수
min() 함수는 필드의 값의 최솟값을 표시하는 함수이다.
가장 낮은 수학 점수를 표시해보겠다.
select min(math) from record;
15. 그룹별 집계
그룹별 집계는 특정 집단의 수치를 group by를 이용하여 값을 표시해보자.
select 필드명 from 테이블명 group by 필드명위의 명령문을 record 테이블에 저장되어 있는 데이터에 적용해보자.
반별로 학생들의 코딩 점수 평균값을 구해보자.
select class, avg(coding)from record where class in(1,2,3,4) group by class;
이와 같이 class마다 코딩점수의 평균값을 표시해준다.
이 명령문에 조건문을 추가하면 특정 조건에 해당하는 값만 표시할 수 있다. 이전에는 where 명령문을 사용했다면 group by 명령문을 사용할 때는 having을 사용해야 한다.
having을 사용해서 수학 점수의 총합이 150점 이상인 결과만 출력해보자.
select class, sum(math) from record where class in(1,2,3,4) group by class having sum(math) >= 150;
이와 같이 수학 점수의 총합이 150점 이상인 반과 결과를 표시해준다.
16. 출력 결과의 정렬
값을 정렬하려면 order by를 사용하여 표시할 수 있다.
select 필드명 from 테이블명 order by 정렬 기준이 될 필드명 desc 또는 asc;desc는 큰 값에서 작은 값 순으로 표시
asc는 작은 값에서 큰 값 순으로 표시
학생들의 코딩 점수를 높은 순에서 낮은 순으로 표시해보자.
select studentID, coding from record order by coding desc;
반대로 코딩 점수를 낮은 순에서 높은 순으로 표시해보자.
select studentID, coding from record order by coding asc;
이번에는 반별 코딩 점수를 높은 순에서 낮은 순으로 표시해보자.
select class, sum(math) from record where class in(1,2,3,4) group by class order by sum(math) desc;
집계함수를 이용하는 경우 order by에도 집계함수를 사용하여 필드명을 명시해야 한다.
