기본키 참조와 서브 쿼리와 DML - 집계함수
Backend

기본키 참조와 서브 쿼리와 DML - 집계함수

2021-06-251수정

TABLE/DATA 추가 및 설정

department 테이블에 기본키(primary key) 설정

alter table department add primary key(dept_id);

신규 테이블 student 설정

  • 기본키 : stu_id (학번)
  • 외래키 : dept_id (학과아이디) – department 테이블의 기본키와 연결
create table student (
    stu_id varchar(10) primary key,
    name varchar(10) not null,
    address varchar(10),
    point int,
    dept_id varchar(10),
    foreign key(dept_id) references department(dept_id)
);

student 테이블 생성 결과

DATA 무결성 확인

참조무결성 확인

DELETE FROM department WHERE dept_id='G31';
  • 실행결과 : 에러 발생 (삭제 불가)
  • student 테이블에서 department테이블의 dept_id를 외래키로 참조하고 있기 때문에 임의로 삭제하는 것이 불가능함 (참조무결성 위반)

서브 쿼리(Sub Query)

쿼리문 안에 또 쿼리문이 들어있는 것

SELECT * FROM student
WHERE
dept_id = (SELECT dept_id FROM dept WHERE office='2nd floor');

만약 서브 쿼리의 결과가 둘 이상이 되면 에러 발생➡IN 사용

SELECT * FROM student
WHERE
dept_id IN (SELECT dept_id FROM dept WHERE office='2nd floor');

DML – 집계함수

통계연산 기능 제공

예:어떤반의학생수는? 학급별평균성적은?가장성적이높은학생은?

종류

  • count : 데이터의 개수를 구한다.
  • sum : 데이터의 합을 구한다.
  • avg : 데이터의 평균 값을 구한다.
  • max : 데이터의 최대 값을 구한다.
  • min : 데이터의 최소 값을 구한다.

sum, avg는 숫자형 데이터 타입을 갖는 필드에서만 적용 가능

count

count(<필드이름>)
count(distinct <필드이름>)
  • 해당 필드에 값이 몇 개인지 출력
  • distinct: 서로 구별되는 값의 개수가 필요한 경우에만 사용
  • NULL은 계산에서 제외됨
  • <필드이름>에 필드 이름 대신 '*'가 사용된 경우에는 레코드의 개수를 계산

예1> student 테이블의 전체 학생 수 출력

SELECT count(*) FROM student;

count(*) 실행 결과

예2> student 테이블의 address 필드의 수 출력

SELECT count(address) FROM student;
SELECT count(distinct address) FROM student; // 중복제거

count(address) 실행 결과

예3> student 테이블에서 dept_id 값이 G31 인 학생 수 출력

SELECT count(*) FROM student WHERE dept_id='G31';

조건부 count 실행 결과

sum

sum(<필드이름>)
  • 해당 필드 값의 합 출력

예1> student 테이블의 H31 학급 학생의 point 총 합

SELECT sum(point) FROM student WHERE dept_id='H31';

sum 실행 결과

avg

avg(<필드이름>)
  • 해당 필드의 평균값 출력

예1> 전체 학생의 point 평균

SELECT avg(point) FROM student;

avg 실행 결과

length

length(<필드이름>) 또는 length(‘문자열‘)
  • 해당 필드의 길이 또는 해당 문자열의 길이를 반환

예1> department 테이블의 dept_name 칼럼의 길이

SELECT length(dept_name) FROM department;

length 실행 결과

예2> ‘안녕’ 문자열의 길이(byte)

SELECT length(‘안녕’);

글자(한글 포함) 수를 알고 싶다면 CHAR_LENGTH 함수를 사용

length('안녕') 실행 결과

CHAR_LENGTH 실행 결과

CONCAT

CONCAT(‘문자열1’, ‘문자열2’, ‘문자열3’ ...);
  • 전달받은 문자열을 모두 결합하여 하나의 문자열로 반환
  • 전달받은 문자열 중 하나라도 NULL이 존재하면 NULL을 반환

예1> 문자열 3개를 연결하여 반환

SELECT CONCAT(‘Hello ’, ‘My’, ‘SQL’);

CONCAT 실행 결과

RIGHT, LEFT

RIGHT(‘문자열’, N) / LEFT(‘문자열’, N)
  • RIGHT : 문자열의 오른쪽부터 N개 문자열 반환
  • LEFT : 문자열의 왼쪽부터 N개 문자열 반환

예1> 문자열의 오른쪽 3개 반환

SELECT RIGHT(‘Hello MySQL’, 3);

RIGHT 실행 결과

LOWER, UPPER

LOWER(‘문자열’) / UPPER(‘문자열’)
  • LOWER : 문자열의 문자를 모두 소문자로 변경
  • UPPER : 문자열의 문자를 모두 대문자로 변경

예1> 해당 문자열을 모두 소문자로

SELECT LOWER(‘Hello MySQL’);

LOWER 실행 결과

REPLACE

REPLACE(‘문자열1’, ‘특정 문자열‘, ‘바꾸고 싶은 문자열')
  • 문자열1에 포함된 ‘특정 문자열'을 ‘바꾸고 싶은 문자열'로 교체

예1> MS를 My로 변경

SELECT REPLACE(‘MS SQL’, ‘MS’, ‘My’);

REPLACE 실행 결과

GROUP BY

GROUP BY <필드리스트>
  • GROUP BY 에 지정된 필드의 값이 같은 레코드끼리 그룹을 지어 각 그룹별로 집계함수 적용

예1> 학생 주소(지역)별 학생 수 출력

SELECT address, count(stu_id) FROM student GROUP by address;

GROUP BY 실행 결과

LIMIT

LIMIT <숫자> 또는 LIMIT <숫자1, 숫자2>
  • 출력 개수를 제한
  • LIMIT <N> : 상위의 N개만 출력
  • LIMIT <N1, N2> : N1번째 레코드 이후(N1+1번째)부터 N2개의 레코드 출력

예1> department 테이블에서 상위 3개의 레코드만 출력

SELECT * FROM department LIMIT 3;

LIMIT 3 실행 결과

예2> department 테이블에서 3번째 레코드 이후부터 2개의 레코드만 출력

SELECT * FROM department LIMIT 3, 2;

LIMIT 3, 2 실행 결과