😢 스터디노트(SQL 14)

실습환경 만들기

• zerobase 사용(이동)

cmd

mysql -u root -p

use zerobase;

---

PRIMARY KEY(기본키)

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

- primary key 생성 문법1 (constraint 생략 가능, 생략한 경우 자동생성됨)

create table tablename
(
column1 datatype NOT NULL,
column2 datatype NOT NULL,
...
constraint constraint_name primary key (column1, column2, ...)
)

• 예제 1-1 (primary key 생성) 테이블 생성 시 하나의 칼럼을 기본키로 설정하는 경우

create table person( pid int NOT NULL, name varchar(16), age int, sex char, primary key(pid) );

desc person;

• 예제 1-2 (primary key 생성) 테이블 생성 시 여러 개의 칼럼을 기본키로 설정하는 경우

 create table animal ( name varchar(16) NOT NULL, type varchar(16) NOT NULL, age int, primary key (name, type));

desc animal;

---

- primary key 삭제 문법

alter table tablename
drop primary key;

• 예제 1-1 (primary key 삭제) 하나의 칼럼이 기본키로 설정된 경우

alter table person drop primary key;

desc person;

• 예제 1-2 (primary key 삭제) 여러개의 칼럼이 기본키로 설정된 경우 (삭제하는 방법은 동일)

alter table animal drop primary key;

desc animal;

---

- primary key 생성 문법2

• Table 이 생성된 이후에도 ALTER TABLE 을 통해 PRIMARY KEY 를 지정 가능

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

• 예제 1-1 (primary key 생성) 하나의 칼럼을 기본키로 설정하는 경우

alter table person add primary key (pid);

desc person;

• 예제 1-2 (primary key 생성) 여러개의 칼럼을 기본키로 설정하는 경우

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

desc animal;

---

FOREIGN KEY (외래키)

• 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_column)
)

• 예제 1-1 (foreign key 생성) 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));

desc orders;

• 예제 1-2 (foreign key 생성) 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) );

desc job;

---

- constraint 확인 문법

show create table tablename;

• 예제 1 (constraint 확인)

show create table job;

---

- foreign key 삭제 문법

alter table tablename
drop foriegn key FK_constraint;

• 예제 1 (foreign key 삭제)

alter table orders drop foreign key FK_person;

show create table orders;

---

- foreign key 생성 문법2

• Table 이 생성된 이후에도 ALTER TABLE 을 통해 FOREIGN KEY 를 지정 가능

alter tble tablename
add foreign key (column) references REF_tablename(REF_column);

• 예제 1 (foreign key 생성)

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

 desc orders;

show create table orders;

---

- foreign key 예제

• 예제 1 (foreign key 예제) police_station 과 crime_status 테이블 사이에 관계 (Foreign Key)를 설정. AWS RDS(database-1) 의 zerobase 에서 작업

-h database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com -P 3306 -u admin -p

 use zerobase;

 show tables;

 desc police_station;

select count(distinct name) from police_station;

desc crime_status;

select count(distinct police_station) from crime_status;

select distinct name from police_station limit 3;

select distinct police_station from crime_status limit 3;

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

- 예제 1-1 (foreign key 예제) police_station.name 을 Primary Key 로 설정

 alter table police_station add primary key (name);

desc police_station;

• 예제 1-2 (foreign key 예제) crime_status 테이블에 Foreign Key 로 사용할 Column 추가

alter table crime_status add column reference varchar(16);

desc crime_status;

• 예제 1-3 (foreign key 예제) Foreign Key 생성

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

• 💡 예제 1-4 (foreign key 예제) 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;

• 예제 1-4 (foreign key 예제) 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;

💡 문제 1. 다음과 같이 study_id 가 PRIMARY KEY, patient_id 가 person 테이블의 pid 와 연결된FOREIGN KEY 로 지정된 study 테이블을 생성

cd Documents/ds_sql_ws

mysql -u root -p

use zerobase;

show tables;

create table study ( 
study_id int not null, 
study_date date, 
study_time time, 
patient_id int, 
primary key (study_id), 
constraint FK_study foreign key (patient_id) references person(pid));

desc study;

show create table study;

💡 문제 2. 생성한 테이블의 PRIMARY KEY 를 삭제

alter table study drop primary key;

💡 문제 3. 생성한 테이블의 FOREIGN KEY 를 삭제

alter table study drop foreign key FK_study;

show create table study;

💡 문제 4. study 테이블의 patient_id 를 person 테이블의 pid 와 연결된 FOREIGN KEY 로 등록

 alter table study add foreign key (patient_id) references person(pid);

show create table study;

💡 문제 5. study 테이블의 study_id 를 PRIMARY KEY로 등록

alter table study add primary key (study_id);

desc study;

어렵다...생소하다..

💻 출처 : 제로베이스 데이터 취업 스쿨