[sql] pk/fk, aggregate, scalar functions, subquery

📌 PRIMARY KEY, FOREIGN KEY

📚 PRIMARY KEY (기본 키)

• 테이블의 각 레코드를 식별
• 중복되지 않은 고유값을 포함
• NULL 값을 포함할 수 없음
• 테이블 당 하나의 기본키를 가짐

PRIMARY KEY 생성 문법 1

CREATE TABLE tablename
(
	column1 datatype NOT NULL,
    column2 datatype NOT NULL,
    ...
    CONSTRAINT constraint_name
     PRIMARY KEY (column1, column2 ...)
);

• 하나의 칼럼 기본키로 설정

CREATE TABLE person
(
	pid int NOT NULL,
    name varchar(16),
    age int,
    sex char,
    PRIMARY KEY (pid)
);

• 여러 개의 칼럼 기본키로 설정

CREATE TABLE animal
(
	name varchar(16) NOT NULL,
    type varchar(16) NOT NULL,
    age int,
    PRIMARY KEY (name, type)
);

• PRIMARY KEY 삭제
  기본키로 설정된 칼럼 갯수 상관없이 모두 삭제

ALTER TABLE tablename
DROP PRIMARY KEY;

PRIMARY KEY 생성 문법 2

ALTER TABLE tablename
ADD PRIMARY KEY (column1, column2, ...);

• 하나의 칼럼

ALTER TABLE person
ADD PRIMARY KEY (id);

• 여러 개의 칼럼
  PK_animal 은 PRIMARY KEY 이름이고 아무거나 써도 됨

ALTER TABLE animal
ADD CONSTRAINT PK_animal PRIMARY KEY (name, type);

📚 FOREIGN KEY (외래 키)

한 테이블을 다른 테이블과 연결해주는 역할이며, 참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)

FOREIGN KEY 생성 문법 1

CREATE TABLE tablename
(
	column1 datatype NOT NULL,
    column2 datatype NOT NULL,
    column3 datatype,
    column4 datatype
    ...
    CONSTRAINT constraint_name
     PRIMARY KEY (column1, column2, ...),
    CONSTRAINT constraint_name
     FOREIGN KEY (column3, column4, ...) REFERENCES REF_tablename(REF_collumn)
);

• CREATE TABLE 에서 FOREIGN KEY 지정

CREATE TABLE orders
(
	oid int not null,
    order_no varchar(16),
    pid int,
    PRIMARY KEY (oid),
    CONSTRAINT FK_person FOREIGN KEY (pid) REFERENCES person(pid)
);

create table에서 foreign key 지정하는 경우 constraint 생략가능

CREATE TABLE job
(
	jid int not null,
    name varchar(16),
    pid int,
    PRIMARY KEY (jid),
    FOREIGN KEY (pid) REFERENCES person(pid)
);

• CONSTRAINT 확인

SHOW CREATE TABLE tablename;

• FOREIGN KEY 삭제

ALTER TABLE tablename
DROP FOREIGN KEY FK_constraint;

FOREIGN KEY 생성 문법 2

테이블 생성 이후 지정

ALTER TABLE tablename
ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column)

📌 Aggregate Functions (집계함수)

여러 칼럼 혹은 테이블 전체 칼럼으로부터 하나의 결과값을 반환하는 함수

Count

총 갯수를 계산해 주는 함수

• COUNT 문법

SELECT COUNT(column)
FROM tablename
WHERE condition;

• 예제

-- police_station 테이블의 데이터 count
SELECT COUNT(*) FROM police_ststion;

-- crime_status 테이블에서 경철서 count
SELECT COUNT(DISTINCT police_station) FROM crime_status;

-- crime_type 몇 가지?
SELECT COUNT(DISTINCT crime_type) FROM crime_status;

SUM

숫자 column의 합계 계산

• SUM 문법

SELECT SUM(column)
FROM tablename
WHERE condition;

• 예제

-- 범죄 총 발생건수
SELECT SUM(case_number) FROM crime_status WHERE status_type='발생';

AVG

숫자 column의 평균 계산

• AVG 문법

SELECT AVG(column)
FROM tablename
WHERE condition;

• 예제

-- 평균 폭력 검거 건수
SELECT AVG(case_number)
FROM crime_status LIKE '폭력' AND status_type='검거'

MIN

숫자 column 중 가장 작은 값

• MIN 문법

SELECT MIN(column)
FROM table
WHERE condition;

• 예제

-- 강도 발생 건수가 가장 적은 경우
SELECT MIN(case_number)
FROM crime_status
WHERE crime_type LIKE '강도' AND status_type='발생';

MAX

숫자 column 중 가장 큰 값

• MAX 문법

SELECT MAX(column)
FROM table
WHERE condition;

GROUP BY

그룹화하여 데이터를 조회

• GROUP BY 문법

SELECT column1, column2, ...
FROM table
WHERE condition
GROUP BY column1, column2, ...
ORDER BY column1, column2, ...;

• 예제

-- 경찰서 별로 총 발생 범죄 건수를 검색
SELECT police_station, sum(case_number) 발생건수
FROM crime_status
WHERE status_type LIKE '발생' 
GROUP BY police_station
ORDER BY 발생건수 DESC
LIMIT 5;

HAVING

조건에 집계함수가 포함되는 경우 WHERE 대신 HAVING 사용

• HAVING 문법

SELECT column1, column2, ...
FROM table
WHERE condition
GROUP BY column1, column1, ...
HAVING condition (Aggregate Functions)
ORDER BY column1, column2, ...

• 예제

-- 경찰서 별로 발생한 폭력과 절도의 범죄 건수 평균이 2000 이상인 경우 검색
SELECT police_station, AVG(case_number)
FROM crime_station
WHERE (crime_type LIKE '폭력' OR crime_type LIKE '절도')
	AND status_type LIKE '발생'
GROUP BY police_station
HAVING AVG(case_number) >= 2000;

📌 Scalar Functions

입력값을 기준으로 단일 값을 반환

UCASE

영문을 대문자로 반환하는 함수

• 문법

SELECT UCASE(string);

• $15가 넘는 메뉴를 대문자로 조회

SELECT UCASE(menu), price FROM sandwich WHERE price>15;

LCASE

영문을 소문자로 반환하는 함수

• 문법

SELECT LCASE(string);

MID

문자열 부분 반환

• 문법

SELECT MID(string, start_position, length);

LENGTH

문자열의 길이 반환

• 문법

SELECT LENGTH(string);

ROUND

지정한 자리에서 숫자 반올림

• 문법

SELECT ROUND(number, decimals_place)
-- number: 반올림할 대상, decimals: 반올림할 소수점 위치(optional)

NOW

현재 날짜 및 시간 반환

• 문법

SELECT NOW()

FORMAT

숫자를 천단위 콤마가 있는 형식으로 반환

SELECT FORMAT(number, decimal_place)
-- number: 포맷 적용 대상 문자/숫자, decimals: 표시할 소수점 위치

📌 SQL Subquery

📖 About Subquery

📖 Scalar Subquery (스칼라 서브쿼리)

📖 Inline View (인라인 뷰)

📖 Nested Subquery (중첩 서브쿼리)