10장. SELECT명령문 : 통계함수 모든 열 선택(*) 2. SELECT 절의 수식

Presentation on theme: "10장. SELECT명령문 : 통계함수 모든 열 선택(*) 2. SELECT 절의 수식"— Presentation transcript:

1 10장. SELECT명령문 : 통계함수 모든 열 선택(*) 2. SELECT 절의 수식
3. DISTINCT을 사용한 중복된 행 제거 4. 언제 2개의 행이 동등한가? 5. 통계함수 소개 COUNT 함수 7. MAX와 MIN 함수 SUM 함수 9. AVG 함수 10. STDDEV 함수와 VARIANCE 함수 11. 통계함수의 규칙 12. 열의 표제어 사용 쉽게 배우는 MySQL 5.x

2 10.1 모든 열의 선택(*) 특수 문자 *는 FROM 절에서 사용된 각 테이블에 있는 모든 행, 열 값을 표현
다음 2개의 SELECT 명령문은 동일 SELECT    * FROM      CIRCLE ; SELECT CIR_NUM, CIR_NAME, STU_NO, STU_NAME, PRESIDENT FROM   CIRCLE; 다음 3개의 SELECT 명령문은 동일 SELECT  CIRCLE.* FROM CIRCLE, STUDENT WHERE CIRCLE.STU_NO = STUDENT.STU_NO; 쉽게 배우는 MySQL 5.x

3 SELECTCIR_NUM, CIR_NAME, CIRCLE.STU_NO, CIRCLE.STU_NAME, PRESIDENT
FROM CIRCLE, STUDENT WHERE CIRCLE.STU_NO = STUDENT.STU_NO; SELECT  CI.* FROM CIRCLE CI, STUDENT WHERE CI.STU_NO = STUDENT.STU_NO; | cir_num | cir_name     | stu_no   | stu_name | president | |       1 | 컴맹탈출     | | 고혜진   | 0         | |       2 | 컴맹탈출     | | 유하나   | 1         | |       3 | 컴맹탈출     | | 김문영   | 2         | |       4 | Java길라잡이 | | 장수인   | 2         | |       5 | Java길라잡이 | | 정인정   | 1         | |       6 | Java길라잡이 | | 박도준   | 0         | |       7 | PHP길라잡이  | | 이상길   | 0         | 7 rows in set (0.02 sec) 쉽게 배우는 MySQL 5.x

4 10.2 SELECT 절의 수식 SELECT 절의 수식에는 리터럴, 계산 또는 스칼라 함수를 사용 가능
[예제 10-1] 2007년에 등록한 학생에 대한 학번, 년도, 학기, 장학금액, 납부총액(등록금-장학금), 납부금비율(납부총액/등록금*100), ‘%'를 출력하시오. mysql> select stu_no, fee_year, fee_term, fee_total, jang_total,       -> fee_pay "납부총액", fee_pay/fee_total*100 "납부금비율",'%'     -> from fee     -> where fee_year = 2007; | stu_no   | fee_year | fee_term | fee_total | jang_total | 납부총액 | 납부금비율 | % | | | 2007     |        1 |   |    |  |    | % | | | 2007     |        2 |   |    |   |    | % | | | 2007     |        1 |   |    |  |    | % | | | 2007     |        2 |   |     |  |    | % | | | 2007     |        1 |   |     |  |    | % | | | 2007     |        2 |   |       NULL |  |   | % | | | 2007     |        1 |   |     |  |    | % | | | 2007     |        2 |   |       NULL |  |   | % | | | 2007     |        1 |   |     |  |    | % | | | 2007     |        2 |   |       NULL |  |   | % | | | 2007     |        1 |   |     |  |    | % | | | 2007     |        2 |   |    |  |    | % | | | 2007     |        1 |   |     |  |    | % | | | 2007     |        2 |   |       NULL |  |   | % | | | 2007     |        1 |   |     |  |    | % | | | 2007     |        2 |   |    |   |    | % | 16 rows in set (0.00 sec) 쉽게 배우는 MySQL 5.x

5 10.3 DISTINCT을 사용한 중복된 행의 제거 SELECT 절에서 DISTINCT를 사용하면 중간 결과에서 중복된 행을 삭제 [예제 10-2] 등록(FEE) 테이블에서 등록년도가 2006년인 학생의 학번, 이름을 출력하라. mysql> select fe.stu_no, stu_name mysql> select distinct fe.stu_no, stu_name     -> from fee fe, student s > from fee fe, student s     -> where fe.stu_no = s.stu_no > where fe.stu_no = s.stu_no     -> and fe.fee_year = 2006; > and fe.fee_year = 2006; | stu_no   | stu_name | | stu_no   | stu_name | | | 박도준   | | | 박도준   | | | 박도준   | | | 박정인   | | | 박정인   | | | 박정인   | rows in set (0.14 sec) 4 rows in set (0.14 sec) 결과 테이블에서 학생과 학생은 2번씩 출력 DISTINCT를 사용하면 중복된 행은 삭제 쉽게 배우는 MySQL 5.x

6 [예제 10-3] FEE 테이블에서 2006년과 2007년에 등록한 모든 학생의 학번, 이름, 등록년도를 출력하라.
mysql> select fe.stu_no, stu_name, fee_year     -> from fee fe, student s     -> where fe.stu_no = s.stu_no     -> and (fe.fee_year = 2007 or fe.fee_year = 2006); | stu_no   | stu_name | fee_year | | | 박도준   | 2006     | | | 박도준   | 2007     | | | 박정인   | 2006     | | | 박정인   | 2007     | | | 장수인   | 2007     | | | 홍길동   | 2007     | | | 이순신   | 2007     | | | 유하나   | 2007     | | | 김문영   | 2007     | | | 최차영   | 2007     | 20 rows in set (0.01 sec) 쉽게 배우는 MySQL 5.x

7 [예제 10-3] 예제에 중복된 데이터를 제거하기 위하여 DISTINCT를 사용하면 다음과 같다.
mysql> select distinct fe.stu_no, stu_name, fee_year     -> from fee fe, student s     -> where fe.stu_no = s.stu_no     -> and (fe.fee_year = 2007 or fe.fee_year = 2006); | stu_no   | stu_name | fee_year | | | 박도준   | 2006     | | | 박도준   | 2007     | | | 박정인   | 2006     | | | 박정인   | 2007     | | | 장수인   | 2007     | | | 홍길동   | 2007     | | | 이순신   | 2007     | | | 유하나   | 2007     | | | 김문영   | 2007     | | | 최차영   | 2007     | 10 rows in set (0.00 sec) DISTINCT는 DISTINCT 바로 다음에 있는 수식에만 적용되는 것은 아니고 전체 행에 적용된다. 위의 예제에서 보는바와 같이 “박도준” 학생과 “박정인” 학생은 학번, 성명이 같지만 등록년도가 다르기 때문에 중복을 배제하지 않고 출력 쉽게 배우는 MySQL 5.x

8 ① SELECT 절이 FROM 절에 지정된 각 테이블에 대하여 적어도 하나의 후보키를 가지고 있을 때 DISTINCT는 불필요
-후보 키의 가장 중요한 속성은 중복이 되는 값을 열에 허용하지 않음 -SELECT 절에 후보 키를 포함하면 결과 테이블에 중복된 행이 없음 ②SELECT 절의 결과가 하나의 행만을 가질 때 DISTINCT는 불필요 ③SELECT 절의 결과가 Primary  key 만을 출력할 때는 DISTINCT는 불필요 ALL은 DISTINCT의 반대되는 효과를 나타냄 SELECT ALL STU_NAME FROM  STUDENT; SELECT STU_NAME 이 두 명령문의 결과는 동일 쉽게 배우는 MySQL 5.x

9 10.4 언제 2개의 행이 동등한가? [예제 10-4] 학적테이블에서 휴대폰번호(PHONE_NO)가 서로 다른 학생의 휴대폰번호를 출력하라. mysql> select distinct phone_no     -> from student; | phone_no      | |   | |   | | NULL          | |   | |   | |   | | | |   | | | | | |   | | | 12 rows in set (0.13 sec) 학적 테이블 전체 행은 15개 행이 존재, PHONE_NO가 NULL인 사람이 4명 NULL 값은 서로 동등한 것으로 구성되어 한 번만 출력되고 12개 행이 출력 쉽게 배우는 MySQL 5.x

10 휴대폰이 없어 NULL값을 가지면 “휴대폰 없음”을 출력
[예제 10-5] 학적테이블에서 휴대폰번호(PHONE_NO)가 서로 다른 학생의 휴대폰 번호를 출력하라. (단, 휴대폰이 없는 학생은 “휴대폰 없음”으로 출력하라.) mysql> select distinct     -> ifnull(phone_no, '휴대폰 없음')     -> from student; | ifnull(phone_no, '휴대폰 없음') | |                     | |                     | | 휴대폰 없음                     | |                     | |                     | |                     | |                    | |                     | |                    | |                    | |                     | |                    | 12 rows in set (0.01 sec) 휴대폰이 없어 NULL값을 가지면  “휴대폰 없음”을 출력 쉽게 배우는 MySQL 5.x

11 10.5 통계 함수의 소개 -SELECT 명령문이 GROUP BY 절을 가지고 있지 않다면 SELECT 절에 있는 통계 함수는 모든 행에 적용 -스칼라 함수는 항상 많아야 하나의 행에서 수행 -통계 함수는 행의 집합을 통하여 수행 -SELECT 절이 통계 함수를 가지고 있다면 전체 SELECT 명령문은 그 결과로서 한 행만 생성 함 수 의 미 COUNT 테이블에서 열의 수 또는 행의 수를 결정한다. MIN 열에서 최소 값을 결정한다. MAX 열에서 최대 값을 결정한다. SUM 열에 있는 값들의 합을 결정한다. AVG 열에 있는 값들의 산술평균을 결정한다. 쉽게 배우는 MySQL 5.x

12 [예제 10-6] STUDENT 테이블에 전체 학생 수는?
mysql> select count(*)     -> from student; | count(*) | |       15 | 1 row in set (0.01 sec) 함수 COUNT(*)는 FROM 절에 있는 테이블의 행의 수를 구한다. 행의 수는 STUDENT 테이블에 있는 행의 수와 일치한다. [예제 10-7] 성별이 남자인 학생은 몇 명인가?   mysql> select count(*)       -> from student       -> where substring(id_num, 8, 1) = 1;      | count(*) |   |        9 |   1 row in set (0.03 sec) 쉽게 배우는 MySQL 5.x

13 10.6 COUNT 함수 -COUNT 함수에서는 괄호 내부에 * 또는 수식을 지정
[예제 10-8] 2학년인 학생의 수는 얼마나 되는가?   mysql> select count(*)       -> from student       -> where grade = 2;      | count(*) |   |        3 | [예제 10-9] 휴대폰을 가지고 있는 학생의 수는 얼마나 되는가?   mysql> select count(phone_no)       -> from student;      | count(phone_no) |   |              11 | COUNT(PHONE_NO)는 중간 결과 테이블에 있는 행의 수를 계산하는 대신에 PHONE_NO열에 있는 NULL 값을 제외한 수를 계산 쉽게 배우는 MySQL 5.x

14 [예제 10-10] BAN 열에는 서로 다른 반이 얼마나 되는가?
  mysql> select count(distinct class)       -> from student;      | count(distinct class) |   |                     3 | [예제 10-11] 주민등록번호의 앞 두 자리의 값이 서로 다른 숫자를 가지는 것은 몇 명인가?(즉, 태어난 연도가 서로 다른 경우의 수를 나타내어라)  mysql> select count(distinct substring(id_num, 1, 2))        -> from student;      | count(distinct substring(id_num, 1, 2)) |   |                                       8 | 위의 예제를 태어난 연도(birth_year)가 서로 다른 경우의 수를 나타내보면 다음과 같다.    mysql> select count(distinct birth_year) 쉽게 배우는 MySQL 5.x

15 [예제10-12]STUDENT테이블에나타난서로다른학과코드의수?
  mysql> select count(distinct dept_code)       -> from student;      | count(distinct dept_code) |   |                         3 |   1 row in set (0.00 sec) [예제 10-13] 서로 다른 입학년도의 총 연도 개수와 성별의 수를 출력하라. mysql> select count(distinct substring(stu_no, 1, 4)),     -> count(distinct substring(id_num, 8,1))     -> from student; | count(distinct substring(stu_no, 3, 2)) | count(distinct substring(id_num, 8,1)) | |                                       4 |                                      2 | 1 row in set (0.00 sec) 쉽게 배우는 MySQL 5.x

16 10.7 MAX와 MIN 함수 MAX와 MIN 함수를 사용하여 값들 중에서 최대값과 최소값을 구함
[예제 10-14] 납부총액중 가장 많은 등록금을 출력하라.   mysql> select max(fee_pay) mysql> SELECT MAX(ALL FEE_PAY)       -> from fee; > FROM FEE;      | max(fee_pay) |   |      | 우측과 같이 ALL을 사용함으로써 모든 값을 고려하였고 결과는 동일하다. [예제 10-15] 여학생 중에서 등록금을 가장 적게 납부한 등록금은 얼마인가? mysql> select min(fee_pay)      -> from fee     -> where stu_no in      -> (select stu_no     -> from student      -> where substring(id_num, 8, 1) = 2); | min(fee_pay) | |       | 쉽게 배우는 MySQL 5.x

17 [예제 10-16] 최대로 납부한 등록금과 동일한 등록금을 납부한 행의 수는?
  mysql> select count(*)            -> from fee       -> where fee_pay =       -> (select max(fee_pay)       -> from fee);      | count(*) |   |       11 | 부속 질의어는 최대 등록금을 계산하는데, 이는 3,000,000원이다. 주 질의어의 SELECT 명령문은 최대의 등록금(3,000,000원)과 동일한 등록금을 납부한 행의 수를 계산한다. [예제 10-17] 최대로 납부한 등록금과 동일한 등록금을 납부한 학생의 수는? mysql> select count(distinct stu_no)     -> from fee     -> where fee_pay =     -> (select max(fee_pay)     -> from fee); | count(distinct stu_no) | |                      7 | 쉽게 배우는 MySQL 5.x

18 [예제 10-18] 등록한 학생중 학생별로 가장 많은 등록금을 납부했을 때 학생의 학번, 등록년도, 학기, 납부총액을 출력하라
[예제 10-18] 등록한 학생중 학생별로 가장 많은 등록금을 납부했을 때  학생의 학번, 등록년도, 학기, 납부총액을 출력하라. 단, 출력순서는 학번, 등록년도, 학기 오름차순이다. mysql> select stu_no, fee_year, fee_term, fee_pay        -> from fee f1        -> where fee_pay =        -> (select max(fee_pay)        -> from fee f2        -> where f1.stu_no = f2.stu_no)        -> order by stu_no, fee_year, fee_term; | stu_no   | fee_year | fee_term | fee_pay | | | 2000     |        1 | | | | 2006     |        1 | | | | 2007     |        1 | | | | 2007     |        2 | | | | 2007     |        1 | | | | 2007     |        2 | | | | 2007     |        1 | | | | 2007     |        2 | | | | 2007     |        1 | | | | 2007     |        1 | | | | 2007     |        2 | | | | 2007     |        1 | | 12 rows in set (0.02 sec) 쉽게 배우는 MySQL 5.x

19 [예제 10-19] 등록금을 납부한 학생 중 최대 등록금과 최소 등록금, 최대-최소간의 차이는 얼마인가?
mysql> select max(fee_pay), min(fee_pay), (max(fee_pay) - min(fee_pay))     -> from fee; | max(fee_pay) | min(fee_pay) | (max(fee_pay) - min(fee_pay)) | |      |            0 |                       | [예제 10-20] 영문이름 중 알파벳 순서로 가장 큰 값(마지막)을 나타나는 이름의 첫 문자를 찾아 출력하라.   mysql> select substring(max(stu_ename), 1, 1)       -> from student;      | substring(max(stu_ename), 1, 1) |   | Y                               | MAX 함수는 알파벳 순서로 마지막이 되는 이름(최대 값)을 찾고, SUBSTRING 함수는 이 이름의 첫 번째 문자를 찾아낸다. 학번 학생의 영문이름이 “Yoo Ha-Na”이므로 첫 번째 문자 “Y"를 출력 쉽게 배우는 MySQL 5.x

20 10.8 SUM 함수 -SUM 함수는 특별한 열에 있는 모든 값의 합을 계산
-행에 있는 열이 오직 NULL 값만을 가지면 결과 값이 NULL이 된다. [예제 10-21] "박정인" 학생이 재학 중 받은 전체 장학금의 총액은 얼마인가? mysql> select sum(jang_total) mysql> select sum(distinct jang_total)      -> from fee     -> from fee      -> where stu_no in      -> where stu_no in      -> (select stu_no      -> (select stu_no      -> from student      > from student      -> where stu_name = '박정인');       -> where stu_name = '박정인');    | sum(jang_total) |  |         |  1 row in set (0.02 sec) SELECT 명령문에서 DISTINCT를 가지도록 SUM 함수를 확장한다면 우측과 같이 명령문을 작성할 수 있고 결과는 동일하다. 쉽게 배우는 MySQL 5.x

21 10.9 AVG 함수 -AVG 함수는 특별한 열에 있는 값의 산술 평균을 계산
[예제 10-22] 학번 ("박정인") 학생이 받은 장학금의 평균을 계산하라.   mysql> select avg(jang_total)       -> from fee       -> where stu_no = ' ';      | avg(jang_total) |   |    |   1 row in set (0.00 sec) 평균 장학금 1,450,000은 500,000, 2,500,000, 2,000,000, 800,000을 합산한 값(5,800,000)을 4로 나눈 평균값이다. 쉽게 배우는 MySQL 5.x

22 [예제 10-24] 평균 장학금 보다 더 많은 장학금을 받은 학생의 학번과 장학금을 출력하라.
[예제 10-23] 동일한 등록금 납부총액을 제외한 등록금 납부총액의 평균은 얼마인가?   mysql> select avg(distinct fee_pay)       -> from fee; | avg(distinct fee_pay) | |          | [예제 10-24] 평균 장학금 보다 더 많은 장학금을 받은 학생의 학번과 장학금을 출력하라.   mysql> select distinct stu_no, jang_total       -> from fee       -> where jang_total >       -> (select sum(jang_total) / count(*)       -> from fee); | stu_no   | jang_total | | |    | | |    | | |    | | |    | | |    | | |    | 쉽게 배우는 MySQL 5.x

23 -AVG 함수에서도 만약 주어진 행에 있는 열 전체가 NULL이면 AVG 함수의 결과 또한 NULL을 가진다.
[예제 10-25] 영문이름의 평균 길이(문자 수)와 이름의 최대 길이는 얼마인가? mysql> select avg(length(rtrim(stu_ename))),max(length(rtrim(stu_ename)))     -> from student; | avg(length(rtrim(stu_ename))) | max(length(rtrim(stu_ename))) | |                       |                            16 | 1 row in set (0.00 sec) -AVG 함수에서도 만약 주어진 행에 있는 열 전체가 NULL이면 AVG 함수의 결과 또한 NULL을 가진다. -열이 NULL 값과 NULL이 아닌 값을 가지고 있다면 AVG 함수의 결과는 NULL이 아닌 값의 전체 합을 NULL이 아닌 값의 수로 나눈 것이다. -AVG 함수는 전체의 합을 NULL값을 가진 값의 수를 뺀 나머지 값의 개수만으로 나눈다. -산술평균 값이 정확하지 않다. -NULL 값을 포함 한경우 : AVG 함수를 사용하지 않음 산술평균 = SUM( ) / COUNT(열의 이름) 쉽게 배우는 MySQL 5.x

24 [예제 10-26] 입학금의 평균을 avg( )함수와 산술평균(입학금의 전체 합 / 전체 행의 수)을 구하여라.
mysql> select avg(fee_enter), sum(fee_enter) / count(*)     -> from fee; | avg(fee_enter) | sum(fee_enter) / count(*) | |    |               | 1 row in set (0.00 sec) 위의 예제에서 avg( )함수를 이용한 경우에는 입학금에 NULL 값이 포함되어 있으나 NULL 값은 무시하고 실제 데이터 값만을 계산하므로 올바른 결과가 아니다. 그러므로 데이터의 일부에 NULL 값이 포함된 경우의 산술평균은 전체의 합을 행의 수로 나누어 구한 것이 올바른 결과이므로 산술평균 값은 (sum(fee_enter) / count(*) )   원이 맞는 값 쉽게 배우는 MySQL 5.x

25 10.10 STDDEV와 VARIANCE 함수 -STDDEV 함수는 열의 NULL 값을 제외한 표준편차를 계산하여 값을 결정
-VARIANCE 함수는 열의 NULL 값을 제외한 분산을 계산하여 값을 결정 [예제 10-27] 장학금의 표준편차와 분산을 구하여라. mysql> select stddev(ifnull(jang_total,0)), variance((ifnull(jang_total,0)))     -> from fee; | stddev(jang_total) | variance(jang_total) | |       |   | 1 row in set (0.00 sec) 쉽게 배우는 MySQL 5.x

26 10.11 통계 함수를 사용하는 일반적인 규칙 -NULL 값은 함수의 계산에 포함되지 않는다.
통계 함수를 사용하는 일반적인 규칙 -NULL 값은 함수의 계산에 포함되지 않는다. ① SELECT SUM(JANG_TOTAL) / COUNT(*) FROM  FEE; ② SELECT AVG(JANG_TOTAL) ① NULL이 아닌 값의 전체 합을 FEE 테이블에 있는 전체 열의 수로 나눈다. ② NULL이 아닌 모든 값의 합을 NULL이 아닌 값의 수로 나눈다. 위의 등록테이블(FEE)에서 -JANG_TOTAL 열이 NULL값을 가지고 있다면 ①은 정확한 결과 값을 출력하나 ②는 잘못된 결과 값이 출력된다. -JANG_TOTAL 열이 NULL값이 없다면 ①②는 동일하게 정확한 결과를 출력한다. 쉽게 배우는 MySQL 5.x

27 [예제 10-28] 장학금 총액의 평균을 SUM( )/COUNT(*)와 AVG( )함수를 이용한 결과 값을 출력하라.
mysql> select sum(jang_total) / count(*), avg(jang_total)     -> from fee; | sum(jang_total) / count(*) | avg(jang_total) | |               |    | 1 row in set (0.00 sec) 장학금(jang_total)에는 NULL 값을 포함하고 있기 때문에 서로 다른 결과 값을 출력하며, 좌측 값이 올바른 결과 값이다. [예제 10-29] 등록금 총액의 평균을 SUM( )/COUNT(*)와 AVG( )함수를 이용한 결과 값을 출력하라. mysql> select sum(fee_total) / count(*), avg(fee_total) | sum(fee_total) / count(*) | avg(fee_total) | |              |   | 등록금 총액(fee_total)에는 NULL 값을 포함하지 않았으므로 결과는 동일 쉽게 배우는 MySQL 5.x

28 열의 표제어 사용 -수식이나 칼럼이름(column name) 다음에 열의 표제어(column heading)라는 다른 이름을 사용할 수 있다. -SELECT 명령문 외의 다른 절에서는 열의 표제어를 사용할 수 없다. [예제10-30]동아리에 소속된 학생의 학번, 이름 소속 동아리 명을 출력하라(단, 동아리 명을 출력할 때는 표제어를 “동아리 명”이라고 기입하라). mysql> select stu_no, stu_name, cir_name "동아리명"     -> from circle; | stu_no   | stu_name | 동아리명     | | | 고혜진   | 컴맹탈출     | | | 유하나   | 컴맹탈출     | | | 김문영   | 컴맹탈출     | | | 장수인   | Java길라잡이 | | | 정인정   | Java길라잡이 | | | 박도준   | Java길라잡이 | | | 이상길   | PHP길라잡이  | 7 rows in set (0.03 sec) DONG_NAME 뒤에 “동아리 명"을 기입하여 동아리 이름을 명확하게 표현 쉽게 배우는 MySQL 5.x

29 [예제10-31]교수 테이블에서 교수코드, 교수명을 출력하라
[예제10-31]교수 테이블에서 교수코드, 교수명을 출력하라. 단, 출력할 때는 표제어를 “코드”, “교수명”이라고 기입하고, 교수코드는 4001, 4002, 4005, 5010만 출력한다. mysql> select prof_code "교수코드", prof_name "교수명"     -> from professor     -> where prof_code in ('4001','4002','4005','5010'); | 교수코드 | 교수명 | | 4001     | 정진용 | | 4002     | 나인섭 | | 4005     | 정병열 | | 5010     | 정종필 | 4 rows in set (0.06 sec) 쉽게 배우는 MySQL 5.x