실습환경만들기
로컬 mysql에 접속
1. ♠MySQL 8.0 Command Line Client 실행 후 root계정으로 mysql에 접속
2. cmd를 열어
mysql -u root -p
비밀번호 입력
실습할데이터베이스로이동
zerobase 사용 (이동)
USE zerobase;
Database changed1. Primary Key (기본키)
<PRIMARY KEY(기본키)>
- 테이블의 각 레코드를 식별
- 중복 되지 않은 고유값을 포함
- NULL 값을 포함 할 수 없음
- 테이블당 하나의 기본키를 가짐
PRIMARY KEY 생성문법 1
CREATE TABLE tablename
(
column1 datatype NOT NULL,
column2 datatype NOT NULL,
...
CONSTRAINT contraint_name, #ex> CONSTRAINT PK_person, 생략시 자동생성
PRIMARY KEY(column1, column2, ...) #여러개 지정 가능
);PRIMARY KEY 생성예제 1 - 1
하나의칼럼을기본키로설정하는경우
CREATE TABLE person
(
pid int NOT NULL,
name varchar(16),
age int,
sex char,
PRIMARY KEY (pid)
);
=> pid(person id)를 기본키로 지정하는 예제
=> CONSTRAINT 생략가능
=> pid 옆에 PRI 표시가 됨
desc person;
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | PRI | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.03 sec)PRIMARY KEY 생성예제 1 - 2
여러개의 칼럼을 기본키로 설정하는 경우
CREATE TABLE animal2
(
name varchar(16) NOT NULL,
type varchar(16) NOT NULL,
age int,
PRIMARY KEY (name, type)
);
=> PRI(primary key)가 name과 type에 표시되어 있으나 기본키가 2개라는 뜻이 아니라
테이블당 기본키는 1개만 설정가능하기 때문에 이 두개의 컬럼이 1개의 기본 키라는 뜻
desc animal2;
mysql> desc animal2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | NO | PRI | NULL | |
| type | varchar(16) | NO | PRI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)PRIMARY KEY 삭제문법
ALTER TABLE tablename
DROP PRIMARY KEY;
=> 이름을 별도로 지정할 필요가 없는 이유는 테이블당 1개의 기본키만 있기 때문에 알아서 삭제됨
하나의 칼럼이 기본키로 설정된 경우
ALTER TABLE person
DROP PRIMARY KEY;
desc person;
=> PRI 표시가 없어짐
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | | NULL | |
PRIMARY KEY 삭제예제 2
여러개의칼럼이기본키로설정된경우 (삭제하는방법은동일)
ALTER TABLE animal2
DROP PRIMARY KEY;
=> 동일함
desc animal2;
mysql> desc animal2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | NO | | NULL | |
| type | varchar(16) | NO | | NULL | |
| age | int | YES | | NULL | |
PRIMARY KEY 생성문법 2
ALTER TABLE person
ADD PRIMARY KEY (pid);
=> 이미 만들어진 테이블에 기본키를 추가하는 방법
desc person;
=> 다시 PRI표기됨
| pid | int | NO | PRI | NULL | |
PRIMARY KEY 생성예제 2 - 2
여러개의칼럼을기본키로지정하는경우
ALTER TABLE animal2
ADD CONSTRAINT PK_animal PRIMARY KEY (name, type);
=> CONSTRAINT PK_animal 생략가능, 생략했을 시 자동생성됨, animal테이블의 PK를 주겠다.
desc animal2;2. Foreign Key(외래키)
FOREIGN KEY (외래키)
한테이블을 다른테이블과 연결해주는 역할이며,
참조되는 테이블의 항목은 그테이블의 기본키 (혹은단일값)
<FOREIGN KEY생성문법>
CREATE TABLE tablename
(
column1 datatype NOT NULL,
column2 datatype NOT NULL,
column3 datatype,
column4 datatype,
...
CONSTRANT constraint_name
PRIMARY KEY (column1, column2, ...),
CONSTRAINT constraint_name
FOREIGN KEY (column3, column4, ...) REFERENCES REF_tablename(REF_column)
);
=> FOREIGN KEY는 참조되는 얘이기 때문에 참조되는 다른 테이블이 있어야함, REF_column과
연결이됨
=> CONSTRANT constraint_name (FK_참조하는 테이블의 이름이 와야함, ex>CONSTRAINT FK_person)
FOREIGN KEY 생성예제 1 - 1 p20
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)
);
=> 컬럼3개
=> oid: order id, 기본키
=> pid는 외래키(포린키), person테이블의 pid를 참조한다.
=> 결과값에서 외래키는 MUL로 표시됨
결과값 확인
desc orders;
mysql> desc orders;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| order_no | varchar(16) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
=> oid가 PRI(PK), pid가 MUL(FK)로 지정됨
FOREIGN KEY 생성예제 1 - 2 p22<CONSTRAINT 를생략하고 FK 만들기>
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)
);
=> jid : job id
=> 공백부분에 CONSTRAINT FK_person 구문을 생략해줌
=> CONSTRAINT FK_pid하면 오류뜸
desc job;<COSNTRAINT 확인문법>
: 자동생성된 CONSTRAINT 를확인하는방법 p24
SHOW CREATE TABLE tablename;
=> FK는 여러개 있을 수 있다.
COSNTRAINT 확인예제 p25
SHOW CREATE TABLE job;
=> 결과값을 보면 CONSTRAINT `job_ibfk_1`이 생성되었음을 확인할 수 있다.
즉, 콘스트레인트가 `job_ibfk_1`로 자동으로 생성되었음
### <FOREIGN KEY 삭제문법> p26
ALTER TABLE tablename
DROP FOREIGN KEY FK_contstraint;
=> 드롭할 폴인키 이름을 주면됨FOREIGN KEY 삭제예제
ALTER TABLE orders
DROP FOREIGN KEY FK_person;
desc orders;
=> 여기서는 pid | int | YES | MUL | NULL으로 떠서 FK삭제되었는지 알 수 없지만
show create table orders;
=> 을 통해 KEY `FK_person` (`pid`)만 나오고 FOREIGN KEY가 없음을 확인 할 수 있다.
즉, FK가 삭제되었구나, 연결이 끊어졌구나를 확인할 수 있음<FOREIGN KEY 생성문법> 2 p29
Table 이 생성된이후에도 ALTER TABLE 을통해 FOREIGN KEY 를지정할수있다
ALTER TABLE tablename
ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);
FOREIGN KEY 생성예제 2 p30~32
desc orders;
ALTER TABLE orders
ADD FOREIGN KEY (pid) REFERENCES person(pid);
desc orders;
=> 여기서는 여전히 전후동일하게 pid가 MUL로 표시되어 FK생성여부를 알 수 없음
show create table orders;
=> CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `person` (`pid`)
생성이 되어있음을 볼 수 있다.
※show create table job;
=> 다시확인 CONSTRAINT `job_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `person` (`pid`)
<다시 FK삭제>
<삭제 안되는 오류 해결>
#1.
ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;
-> Query OK, 0 rows affected (0.01 sec)
=> show create ~로 확인했을 때 CONSTRAINT 옆에 있는 게 자동생성된 외부키 이름이어서
외부키를 삭제할 때는 이를 확인하고 이를 명시해줘야함#2.
ALTER TABLE job DROP FOREIGN KEY job_ibfk_1;
-> Query OK, 0 rows affected (0.01 sec)
show create table orders;
show create table job;
<삭제안됨, 에러뜸>------------------------------------------
ERROR 1091 (42000): Can't DROP 'pid'; check that column/key exists
ALTER TABLE orders
DROP FOREIGN KEY pid;
ALTER TABLE orders
ADD FOREIGN KEY (pid) REFERENCES person(pid);
ALTER TABLE orders
DROP FOREIGN KEY ('pid') REFERENCES person('pid');
<아래 에러코드>-------------------------------------------
ERROR 1091 (42000): Can't DROP 'FK_person'; check that column/key exists
ALTER TABLE orders
DROP FOREIGN KEY FK_person;
ALTER TABLE job
DROP FOREIGN KEY FK_person;FOREIGN KEY 예제
- police_station 과 crime_status 테이블사이에관계 (Foreign Key)를설정해봅시다. AWS RDS(database-1) 의 zerobase 에서작업합니다.
실습환경 구축
mysql -h "database-1.ㅁㅁㅁㅁㅁㅁㅁㅁ.ap-northeast-1.rds.amazonaws.com" -P 3306 -u admin -p비밀번호입력
use zerobase;
show tables;
<분석>
- 1. police_station.name 과 crime_status.police_station 을매칭하여관계를맺도록하겠습니다
desc police_station;
select count(distinct name) from police_station;
=>
count(distinct name) |
+----------------------+
| 31 |
중복제거하면 이름 컬럼에 31개가 있다.
desc crime_status;
=> 여기서는 police_station이 name과 같은 부분
select count(distinct police_station) from crime_status;
| count(distinct police_station) |
+--------------------------------+
| 31 |
중복제거하면 경찰서(이름) 컬럼에 31개가 있다.
select distinct name from police_station limit 3;
=> 상위 3개만 보겠다.
| name |
+------------------+
| 서울중부경찰서 |
| 서울종로경찰서 |
| 서울남대문경찰서 |
+------------------+
select distinct police_station from crime_status limit 3;
| police_station |
+----------------+
| 중부 |
| 종로 |
| 남대문 |-
- 경찰서이름이각테이블에서표시되는형식이다릅니다.
-
- crime_status.police_station 을 police_station.name 과같이만들어서비교하도록합니다.
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;
=> crime_status 테이블의 police_station 컬럼의 중부가
police_station 테이블의 name 컬럼의 서울중부경찰서와 매칭이 되면 c.police_station~오름차순순으로 출력
desc police_station;
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(16) | YES | | NULL | |
| address | varchar(128) | YES | | NULL | |Primary Key 설정 p38
police_station.name 을 Primary Key 로설정합니다.
ALTER TABLE police_station
ADD PRIMARY KEY (name);
alter table police_station add primary key (name);Foreign Key 설정 p39
- crime_status 테이블에 Foreign Key 로사용할 Column 추가 p39
ALTER TABLE crime_status
ADD COLUMN reference VARCHAR(16);Foreign Key 생성 p40
ALTER TABLE crime_status
ADD FOREIGN KEY (reference) REFERENCES police_station (name);
=> Query OK, 310 rows affected (0.11 sec)
Records: 310 Duplicates: 0 Warnings: 0Foreign Key 값 Update p41
<crime_status 테이블의 생성한 reference 컬럼에 데이터 추가해주기>
UPDATE crime_status c, police_station p
SET c.reference = p.name
WHERE p.name LIKE concat('서울', c.police_station, '경찰서');
=> SET: c의 reference 컬럼과 p의 name컬럼과 매칭을 시켜준다.
=> Query OK, 310 rows affected (0.06 sec)
Rows matched: 310 Changed: 310 Warnings: 0Foreign Key 설정 확인
SELECT distinct police_station, reference FROM crime_status;
=> 결과값, police_station을 reference 형태로 만들어줌
| police_station | reference |
+----------------+------------------+
| 중부 | 서울중부경찰서 |
| 종로 | 서울종로경찰서 |
...
31 rows in set (0.04 sec)
desc crime_status;
=> | reference | varchar(16) | YES | MUL | NULL | |
show create table crime_status;
=> ...
`reference` varchar(16) DEFAULT NULL,
KEY `reference` (`reference`),
CONSTRAINT `crime_status_ibfk_1` FOREIGN KEY (`reference`) REFERENCES `police_station` (`name`)JOIN p43
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;
=> c.reference에 p.name을 매칭해준다.
police_station | address |
+----------------+-----------------------------------------------------------+
| 강남 | 서울특별시 강남구 테헤란로 114길 11 |
| 강동 | 서울특별시 강동구 성내로 33 |
| 강북 | 서울특별시 강북구 오패산로 406 |문제 1~5.
- 다음과같이 study_id 가 PRIMARY KEY, patient_id 가 person 테이블의 pid 와연결된 FOREIGN KEY 로지정된 study 테이블을생성하세요.
person 테이블 => 로컬환경
mysql> use zerobase;
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;
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | PRI | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
show create table study;
=> PRIMARY KEY (`study_id`),
KEY `FK_study` (`patient_id`),
CONSTRAINT `FK_study` FOREIGN KEY (`patient_id`) REFERENCES `person` (`pid`)- 생성한테이블의 PRIMARY KEY 를삭제하세요.
alter table study drop primary key;
=> 컬럼 2개에 PRI 키표시가 되어도 기본키는 1개인 것이기에 명령어는 동일함
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | | NULL | |- 생성한테이블의 FOREIGN KEY 를삭제하세요.
alter table study drop foreign key FK_study;
-> Query OK, 0 rows affected (0.01 sec)
desc study;
| patient_id | int | YES | MUL | NULL | |
show create table study;
KEY `FK_study` (`patient_id`) 만 있고 FOREIGN KEY 없음- study 테이블의 patient_id 를 person 테이블의 pid 와연결된 FOREIGN KEY 로등록하세요.
=> 다시 연결함
alter table study
add foreign key (patient_id) references person(pid);
show create table study;
=> KEY `patient_id` (`patient_id`),
CONSTRAINT `study_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `person` (`pid`)- study 테이블의 study_id 를 PRIMARY KEY로등록하세요.
alter table study
add primary key (study_id);
desc study;
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | PRI | NULL | |