SQL 심화5~7

• 실습환경

use zerobase;

Primary key (PRI로 표시)

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

문법

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));
desc person;

여러개의 칼럼을 기본키로 설정하는 경우

create table animal(name varchar(16) not null,type varchar(16) not null,age int,primary key (name,type));
desc animal;

Ptimary key 삭제 문법

alter table tablename
drop primary key;

하나의 칼럼이 기본키 설정인 경우

alter table person
drop primary key;
desc person;

여러개의 컬럼이 기본키인 경우 (삭제 방법은 동일)

alter table animal
drop primary key;
desc animal;

기존 테이블에 primary key 생성 문법

alter table tablename
add primary key (column1,column2,...);

alter table person
add primary key (pid);

여러개 컬럼 지정하기

alter table animal
add constraint PK_animal PRIMARY key (name,type);

Foreign Key (MUL로 표시)

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

문법

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 (column1,column2,...) references ref_tablename(ref_column));

1.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));

2. 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));

3. 자동 생성된 constraint 확인 방법 (foreign key는 여러개 일수 있기 때문에)

show create table tablename;
show create table job;

• foreign key 삭제 문법

alter table tablename
drop foreign key FK_constraint;

1. order 테이블에서 foreign key 삭제하기

alter table orders drop foreign key FK_person;
desc orders;

키속성에 남아있음.

show create table orders;

레퍼런스 관계는 삭제가 됨(잘된거)

2. table이 생성된 이후에도 alter table을 통해 foreign key 를 지정할 수 있다.

alter table tablename
add foreign key (column) references REF_tableanme(REF_column);

alter table orders
add foreign key (pid) references person(pid);

references 가 추가되고 constraint 이름을 주지 않아도 자동 생성 됨.

예제 1. police_station과 crime_status 테이블 사이의 관계(foreign key) 설정하기
AWS RDS 의 zerobase에서 작업

• police_station.name과 crime_status.police_station 매칭하기

select distinct name from police_station limit 3;
select distinct police_station from crime_status limit 3;

• crime_status.police_station 을 police_station.name 과 같이 만들어서 비교하도록 하자

select c.police_station,p.name from crime_status as c, police_station as p
where p.name like concat('서울',c.police_station,'경찰서')
group by c.police_station,p.name limit 5;

• police_station.name 을 primary key 로 설정하기

alter table police_station
add primary key (name);

• crime_status 테이블에 foreign key 로 사용할 column 추가

alter table crime_status
add column reference varchar(16);

• foreign key 생성

alter table crime_status
add foreign key (reference) references police_station(name);
desc crime_status;

• foreign key 값 update

update crime_status c, police_station p
set c.reference=p.name
where p.name like concat('서울',c.police_station,'경찰서');
select distinct police_station ,reference from crime_status;

• foreign key 기준으로 두 테이블을 연관 시켜 검색(조인)할 수 있다.(핵심)

select c.police_station,p.address from crime_status c,police_station p where c.reference=p.name group by c.police_station;

Aggregate Functions(집계 함수)

Count

• 총 갯수를 계산해 주는 함수

select count(*) from police_station; #특정 컬럼을 넣어도 됨

1. Crime_status 테이블에서 경찰서는 총 몇 군데?

select count(distinct police_station) from crime_status;

2. Crime_type이 몇가지인가

select count(distinct crime_stype) from crime_status;

Sum

• 숫자 칼럼의 합계를 계산

범죄 발생 건수

select sum(case_number) from crime_status where status_type='발생';

문제

1. police_station 에서 경찰서는 몇개이고 각각 경찰서 이름은 무엇인가

select count(distinct name) from police_station; #경찰서 개수
select name from police_station; #이름

2. crime_status 에서 status_type 은 몇개고 각각 타입은 무엇인가

select count(status_type) from crime_status;
select distinct status_type from crime_status;

3. 종로 경찰서와 남대문 경찰서의 강도 발생 건수의 합

select sum(case_number) from crime_status where (police_station like '종로' or police_station like '남대문') and crime_stype='강도';

4. 폭력 범죄의 검거 건수

select sum(case_number) from crime_status where crime_stype='폭력' and status_type='검거';

AVG (평균)

• 숫자 칼럼의 평균

평균 폭력 검거 건수

select AVG(case_number) from crime_status where crime_stype like'폭력' and status_type='검거';

MIN (최소값)

• 가장 작은 값 반환

강도 발생 건수가 가장 적은 경우 몇건?

select min(case_number) from crime_status where crime_stype like '강도' and status_type='발생';

중부경찰서에서 가장 낮은 검거 건수

select min(case_number)
from crime_status
where police_station like '중부' and status_type like '검거';

Max (최대값)

• 숫자 칼럼중 가장 큰값

살인이 가장 많이 검거된 건수

select max(case_number)
from crime_status
where crime_stype like '살인' and status_type like '검거';

강남 경찰서에서 가장 많이 발생한 범죄 건수

select max(case_number)
from crime_status
where police_station like '강남' and status_type like '발생';

문제

1. 살인의 평균 발생 건수

select avg(case_number)
from crime_status
where crime_stype like '살인';

2. 서초경찰서의 범죄 별 평균 검거 건수

select avg(case_number) from crime_status where police_station like'서초' and status_type like '검거';

select police_station,crime_stype,status_type,case_number
from crime_status
where police_station like '서초' and status_type like '검거';

3. 구로 경찰서와 도봉경찰서의 평균 살인 검거 건수

select avg(case_number)
from crime_status
where (police_station like '구로' or police_station like '도봉') and crime_stype like '살인';

4. 광진 경찰서에서 가장 낮은 범죄 검거 건수

select min(case_number)
from crime_status
where police_station like '광진';

5. 절도 검거가 가장 많은 건수

select max(case_number)
from crime_status
where crime_stype like '절도' and status_type like '검거' ;

Group By

• 그룹화하여 데이터를 조회

crime_status 에서 경찰서별로 그룹화 하여 경찰서 이름 조회

select police_station
from crime_status
group by police_station
order by police_station
limit 5;

경찰서 종류 검색

select distinct police_station from crime_status limit 10;

경찰서 별로 총 발생 범죄 건수 검색

select police_station,sum(case_number) 발생건수
from crime_status
where status_type like '발생'
group by police_station
order by 발생건수 desc
limit 5;

경찰서 별로 평균 범죄 검거 건수

select police_station, avg(case_number) 검거건수
from crime_status
where status_type like '검거'
group by police_station
order by 검거건수 desc
limit 5;

경찰서 별 평균 범죄 발생건수와 평균 범죄 검거 건수 검색

select police_station,status_type,avg(case_number)
from crime_status
group by police_station,status_type
limit 5;

Having

• 조건에 집계함수가 포함되는 경우 where 대신 having 사용

경찰서 별로 범죄건수의 합이 4000건 보다 큰 경우

select police_station,sum(case_number) count
from crime_status
where status_type like '발생'
group by police_station
having count>4000;

경찰서 별로 발생한 폭력과 절도의 범죄 건수 평균이 2000이상인 경우

select police_station,avg(case_number) count
from crime_status
where (crime_stype='폭력' or crime_stype='절도') and status_type='발생'
group by police_station
having count>=2000;

문제

1. 경찰서 별로 절도 범죄 평균 발생 건수 많은 순으로 10개

select police_station,avg(case_number) count
from crime_status
where crime_stype='절도' and status_type='발생'
group by police_station
order by count desc
limit 10;

2. 경찰서 별로 많이 검거한 범죄 건수를 가장 적은 순으로 5개

select police_station,max(case_number) count
from crime_status
where status_type='검거'
group by police_station
order by count asc
limit 5;

3. 경찰서 별로 가장 적게 검거한 건수 중 4건보다 큰 경우 건수가 큰 순으로 정렬

select police_station,min(case_number) count
from crime_status
where status_type='검거'
group by police_station
having count>4
order by count desc;

4.대문으로 끝나는 이름의 경찰서 별 범죄 발생 건수를 평균이 500건 이상인 경우

select police_station ,avg(case_number) count
from crime_status
where police_station like '%대문' and status_type like '발생'
group by police_station
having count>500;