[DB_MySQL] 이것이 MySQL이다 ch08 테이블과 뷰 1(테이블 생성, 기본키, 외부키)
bobo12
2023. 2. 8. 19:20
728x90
1. 테이블 만들기
1. MySQL Workbench에서 테이블 생성 ▷ Navigator – [Schemas] 클릭 – ‘tabledb’ 확장 – ‘Tables’ 마우스 오른쪽 버튼 – [Create Table] 선택 ▷ usertbl 생성 ▷ buytbl 생성 ▷ num열에 AUTO_INCREMENT, FOREIGN KEY 추가 2. MySQL Workbench에서 데이터 입력 ▷ Navigator에서 usertbl 선택 – 마우스 오른쪽 버튼 클릭 – [Select Rows – Limit 1000] 선택 ▷ <Insert new row> 아이콘 클릭한 후, 3개 행 입력 - <Apply> 클릭 - <Finish> 클릭
▷ buytbl 선택 – 마우스 오른쪽 버튼 클릭 – [Select Rows – Limit 1000] 선택 ▷ <Insert new row> 아이콘 클릭한 후, 3개 행 입력 ▶ num열은 자동 입력되니 NULL 값은 그대로 둠 - <Apply> 클릭
▷ 오류 메시지 이해 ▶ EX) 구매 테이블의 외래 키로 설정된 userid에 데이터가 입력되기 위해서는 입력될 값이 회원 테이블의 userid 열에 존재해야합니다. ▷ JYP 열 선택 – 마우스 오른쪽 버튼 – [Delete Row(s)] 선택 ▷ <Apply> 클릭 - <Finish> 클릭(문제없이 입력됩니다.)
2. SQL로 테이블 생성
1. SQL로 테이블 생성 ▷ 앞의 실습에서 사용한 tabledb을 삭제하고 다시 생성 ▶ DROP DATABASE tabledb; ▶ CREATE DATABASE tabledb; ▷ usertbl 생성
-- 테이블 생성 단계
-- 필요한 컬럼과 타입에 설정
-- not null : 반드시 입력을 해야되는 컬럼
-- primary key : 중복이 되면 안되는 컬럼(PK)
-- auto_increment : 자동으로 입력할 컬럼
-- foriegn key(외래키) : 제약조건, 관계설정
create table usertbl
(
userID char(8) not null primary key,
name varchar(10) not null,
birthYear int not null,
addr char(2) not null,
mobile1 char(3),
mobile2 char(8),
height smallint,
mDate date
);
▷ buytbl 생성
create table usertbl
(
userID char(8) not null primary key,
name varchar(10) not null,
birthYear int not null,
addr char(2) not null,
mobile1 char(3),
mobile2 char(8),
height smallint,
mDate date
);
INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL, 30, 2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200, 1);
-- 제약조건 외래키에 의해 usertbl에 없는 userid는 입력 불가
→ 마지막줄 삭제하면 데이터가 INSERT 됩니다!
3. 제약 조건
◎ 제약 조건(Constraint) 이란?
▷ 데이터의 무결성을 지키기 위한 제한된 조건 의미
▷ 특정 데이터를 입력 시 어떠한 조건을 만족했을 때에 입력되도록 제약
▶ ex) 동일한 아이디로 다시 회원 가입이 안되는 것
▷ 데이터 무결성을 위한 제약조건
▶ PRIMARY KEY 제약 조건
▶ FOREIGN KEY 제약 조건
▶ UNIQUE 제약 조건
▶ CHECK 제약 조건(MySQL 8.0.16부터 지원)
▶ DEFAULT 정의
▶ NULL 값 허용
◎ 기본 키(Primary Key) 제약 조건
▷ 기본 키(Primary Key) 란? ▶ 테이블에 존재하는 많은 행의 데이터를 구분할 수 있는 식별자
▶ 중복이나 NULL값이 입력될 수 없음
▶ ex) 회원 테이블의 회원 아이디, 학생 테이블이 학번
▷기본 키로 생성한 것은 자동으로 클러스터형 인덱스 생성 ▷ 테이블에서는 기본 키를 하나 이상 열에 설정 가능
▷ 기본 키 생성 방법
◎ 기본키 생성 방법
1. table 만들 때 기본키 지정 방법
drop table if exists buytbl;
drop table if exists usertbl;
-- 일반적으로 가장 많이 primary key 설정 방법
-- primary key 따로 설정 방법
create table usertbl
(
userID char(8) not null,
name varchar(10) not null,
birthYear int not null,
constraint primary key PK_usertbl_userID (userID)
);
show keys from usertbl;
2. 테이블 생성할 때 기본키를 빼고 생성했을 경우 기본키 추가 방법
-- 테이블 생성시 기본키 설정을 빼고 테이블 생성한 경우
drop table if exists usertbl;
create table usertbl
(
userID char(8) not null,
name varchar(10) not null,
birthYear int not null
);
-- 테이블 수정을 통해 기본 키 설정
alter table usertbl add constraint primary key PK_usertbl_userID (userID);
show keys from usertbl;
3. 여러 컬럼을 기본 키로 설정 방법(슈퍼키)
-- 여러 컬럼을 기본 키로 설정
drop table if exists prodTbl;
create table prodTbl
(
prodCode char(3) not null,
prodID char(4) not null,
prodDate datetime not null,
prodCur char(10) null
);
alter table prodTbl
add constraint PK_prodtbl_prodCode_prodID
PRIMARY KEY(prodCode, prodID);
show keys from prodTbl;
-- 반환 값 :
-- prodtbl 0 PRIMARY 1 prodCode A 0 BTREE
-- prodtbl 0 PRIMARY 2 prodID A 0 BTREE
두 가지 컬럼의 조합해서 하나의 기본키로 만든 것입니다. 기본키로 지정된 하나의 컬럼은 중복이 되어도 상관없지만 두가지 컬럼이 조합된 기본키는 중복되면 안됩니다.
◎ 외래 키(Foreign Key) 제약 조건
▷ 두 테이블 사이의 관계 선언하여 데이터의 무결성 보장해주는 역할
▷ 외래 키 관계를 설정하면 하나의 테이블이 다른 테이블에 의존
▷ 외래 키 테이블이 참조하는 기준 테이블의 열은 반드시 Primary Key이거나 Unique 제약 조건이 설정되어 있어야 함
▷ 외래 키의 옵션 중 ON DELETE CASCADE 또는 ON UPDATE CASCADE
▶ 기준 테이블의 데이터가 변경되었을 때 외래 키 테이블도 자동으로 적용되도록 설정
▷ 외래키 생성 방법
1. CREATE TABLE 끝에 FOREIGN KEY 키워드로 설정
create table buytbl
(
num int auto_increment not null primary key,
userID char(8) not null,
prodName char(6) not null,
foreign key(userID) references usertbl(userID)
);
2. ALTER TABLE 구문 이용
drop table if exists buytbl;
-- FOREIGN KEY (외래키) 설정 방법 : 테이블 생성 후 외래키 추가해서 작성
create table buytbl
(
num int auto_increment not null primary key,
userID char(8) not null,
prodName char(6) not null
);
-- 외래키 추가
alter table buytbl
add constraint FK_userTbl_buyTbl
foreign key(userID)
references usertbl(userID);
show keys from buytbl;
show index from buytbl;
Database → Revers Engineer → Stored Connection → 연결하고 싶은 사용자 계정(MySQL connections) 선택 → 비밀번호 작성 후 next → 표현하고 싶은 db 선택 후 next → next → execute
아래와 같은 다이어그램이 나타납니다. 현재 기본키와 외래키의 연결 상태를 확인하실 수 있습니다!
◎ UNIQUE 제약 조건 ▷ ‘중복되지 않는 유일한 값’을 입력해야 하는 조건 ▷PRIMARY KEY와 비슷하나 UNIQUE는 NULL 값 허용 ▶ NULL은 여러 개가 입력되어도 상관 없음 ▶ex) 회원 테이블 Email 주소 Unique로 설정
-- primary key(기본키) : 중복이 되지 않는 컬럼, not null
-- unique(유니크) 제약 조건 : 중복이 되지 않는 컬럼, null
drop table if exists usertbl;
drop table if exists buytbl;
-- 유니크 제약 조건 설정 : 테이블 생성시 컬럼에 unique 표시
create table usertbl
(
userID char(8) not null primary key,
name varchar(10) not null,
birthYear int not null,
email char(30) null unique
);
show keys from usertbl;
show index from usertbl;
drop table if exists usertbl;
create table usertbl
(
userID char(8) not null primary key,
name varchar(10) not null,
birthYear int not null,
email char(30) null,
constraint Ak_email unique(email)
);
unique는 primary key와는 유사하게 중복이 허용이 안되지만 null 값은 입력할 수 있습니다.
◎ CHECK 제약 조건 ▷ 입력되는 데이터를 점검하는 기능 ▶ex) 키(Height) 제한 - 마이너스 값이 들어올수 없도록, ▶ 출생년도 제한 - 1900년 이후이고 현재시점 이전 ▷ALTER TABLE문으로 제약 조건 추가 가능
-- CHECK 제약 조건 : 조건을 만들어서 조건이 맞으면 입력 가능
drop table if exists usertbl;
create table usertbl
(
userID char(8) primary key,
name varchar(10),
birthYear int CHECK (birthYear >= 1900 and birthYear <= 2023),
mobile1 char(3) null,
constraint CK_name CHECK (name is not null) -- name에 check 조건 추가
);
-- 추가 mobile1 국번에는 제한 010, 011, 016, 017, 018, 019
alter table usertbl
add constraint CK_mobile1
check (mobile1 in ('010', '011', '016', '017', '018', '019'));
desc usertbl;
desc buytbl;
check를 사용하면 제약 조건을 줄 수 있습니다.
◎ DEFAULT 정의 ▷ 값 입력하지 않았을 때 자동으로 입력되는 기본 값 정의하는 방법 ▷ ALTER TABLE 사용 시에 열에 DEFAULT를 지정하기 위해서 ALTER COLUMN문 사용
1. 테이블 생성시 default 값 추가하는 방법
-- DEFAULT 정의 : 값을 입력하지 않을 때, 자동으로 입력되는 기본 값을 정의
-- 기본값이 입력되기 원하는 컬럼에 정의하고 정의 뒤에 default 기본값
drop table if exists usertbl;
create table usertbl
(
userID char(8) not null primary key,
name varchar(10) not null,
birthYear int not null default -1,
addr char(2) not null default '서울',
mobile1 char(3) null,
mobile2 char(8) null,
height smallint null default 170,
mDate date null
);
2. 테이블 우선 생성 후 테이블 수정, 컬럼 수정을 통해 default 정의
-- 테이블 생성 후 테이블 수정, 컬럼 수정을 통해 default 정의
create table usertbl
(
userID char(8) not null primary key,
name varchar(10) not null,
birthYear int not null,
addr char(2) not null,
mobile1 char(3) null,
mobile2 char(8) null,
height smallint null,
mDate date null
);
alter table usertbl
alter column birthYear set default -1;
alter table usertbl
alter column addr set default '서울';
alter table usertbl
alter column height set default 170;
default 값을 별도로 지정하기 위해서는 alter을 이용하면 됩니다. default는 해당 컬럼에 null 대신 초기값을 지정하기 위해서 사용합니다.
▷ 디폴트 설정된 열에는 다음과 같은 방법으로 데이터 입력
-- 데이터 삽입시 : null로 입력하면 null 입력, default 입력하면 정의된 값으로 입력
insert into userTbl values('LHL', '이해리', default, default, '011', '1234567', default, '2023.12.12');
-- 컬럼을 명시하지 않으면 default 값이 자동으로 입력
insert into userTbl(userID, name) values('KAY', '김아영');
insert into userTbl values('WB', '원빈', 1982, '대전', '019', '9876543', 176, '2020.05.05');
select * from usertbl;
◎ Null 값 허용 ▷ NULL 값을 허용하려면 NULL을, 허용하지 않으려면 NOT NULL을 사용 ▷ PRIMARY KEY가 설정된 열에는 생략하면 자동으로 NOT NULL ▷ NULL 값은 ‘아무 것도 없다’라는 의미, 공백(‘ ‘) 이나 0과 다름
◎ 테이블 압축
▷ 압축 기능은 대용량 테이블의 공간 절약하는 효과 ▷MySQL 5.0부터 자체적으로 테이블 압축 기능 제공 ▷MySQL 8.0에서 내부적인 기능이 더욱 강화 ▷MySQL이 허용하는 최대 용량의 데이터도 오류 없이 압축 가능
◎ 임시 테이블 ▷ 임시로 잠깐 사용되는 테이블
▷세션(Session) 내에서만 존재 ▶ 세션이 닫히면 자동 삭제 ▷생성한 클라이언트에서만 접근 가능 ▶다른 클라이언트에는 접근 불가 ▷임시 테이블 삭제 시점 ▶사용자가 DROP TABLE로 직접 삭제 ▶Workbench를 종료하거나 mysql 클라이언트를 종료하면 삭제됨 ▶MySQL 서비스가 재시작되면 삭제됨
-- 임시 테이블 (temporary)
use employees;
create temporary table if not exists temptbl (id int, name char(5));
create temporary table if not exists employees (id int, name char(5));
desc temptbl;
desc employees;
insert into temptbl values (1, 'This');
insert into employees values (2, 'MySQL');
select * from temptbl;
select * from employees;
서버를 끊고 다시 연결하면 table이 삭제됩니다. 다른 세션에서도 임시 테이블에는 접근할 수 없습니다.
◎ 테이블 삭제
▷ 외래 키 제약 조건의 기준 테이블은 삭제할 수가 없음 ▶ 먼저 외래 키가 생성된 외래 키 테이블을 삭제해야 함 ▶구매 테이블이 존재하는데 회원 테이블을 삭제 할 수 없음, 구매 테이블 삭제가 선행 되어야 함 ▷ 동시에 여러 테이블 삭제도 가능 ▶DROP TABLE 테이블1, 테이블2, 테이블3;
drop table 테이블이름;
◎ 테이블 수정
▷ ALTER TABLE문 사용 ▶ 테이블에 무엇인가 추가/변경/수정/삭제 모두 ALTER TABLE문 사용
alter table 테이블명......
▷ 열의 추가
▶ 기본적으로 가장 뒤에 추가
▶ 순서를 지정하려면 제일 뒤에 ‘FIRST’ 또는 ‘ALTER 열 이름’ 지정
▶ ex) 회원 테이블(usertbl)에 회원 홈페이지 주소 추가
use tabledb;
-- 테이블 컬럼의 추가 (ADD)
alter table usertbl
add homepage varchar(30)
default 'http://www.hanbit.co.kr'
null;
desc usertbl;
usertbl에 homepage라는 컬럼을 추가해서 디폴트 값을 지정했습니다.
▷ 열의 삭제
▶ 제약 조건이 걸린 열을 삭제할 경우 제약 조건을 먼저 삭제한 후에 열을 삭제해야 함
-- 데이터 컬럼의 삭제 (DROP COLUMN)
alter table usertbl
drop column mobile1;
desc usertbl;
▷ 열의 이름 및 데이터 형식 변경
▶ ex) 회원 이름(name)의 열 이름을 uName으로 변경하고 데이터 형식을 VARCHAR(20)으로 변경,
NULL 값도 허용하는 경우
-- 데이터 컬럼의 이름 및 데이터 형식 변경 (CHANGE)
alter table usertbl
change column name uName varchar(20) null;
desc usertbl;
▷ 열의 제약 조건 추가 및 삭제
▶ ex) 기본 키를 삭제 하는 경우
-- 테이블 컬럼의 제약조건 추가 및 삭제 (DROP)
-- 제약 조건 기본키 삭제
alter table usertbl
drop primary key;
desc usertbl;
▷오류가 발생
▶ usertbl의 기본 키인 userID열은 buytbl에 외래 키로 연결되어 있기 때문에 외래 키를 제거한 후에
다시 기본 키를 제거해야 함
▶ex) 외부 키를 삭제 하는 경우
drop table if exists buytbl;
drop table if exists usertbl;
create table usertbl
(
userID char(8) not null primary key,
name varchar(10) not null,
birthYear int not null,
addr char(2) not null,
mobile1 char(3),
mobile2 char(8),
height smallint,
mDate date
);
create table buytbl
(
num int auto_increment not null primary key,
userID char(8) not null,
prodName char(6) not null,
groupName char(4),
price int not null,
amount smallint not null,
foreign key(userID) references usertbl(userID)
);
-- 테이블 확인
desc usertbl;
desc buytbl;
-- buytbl의 외부키 삭제
alter table buytbl
drop foreign key buytbl_ibfk_1;
-- usertbl의 기본키 삭제
alter table usertbl
drop primary key;
-- auto_increment 조건 삭제
ALTER TABLE `tabledb`.`buytbl`
CHANGE COLUMN `num` `num` INT NOT NULL ;
-- buytbl의 기본키 삭제
alter table buytbl
drop primary key;
외부키를 지우고 싶다면 vavigator의 테이블에서 외부키의 이름을 확인할 수 있습니다.
테이블 생성, 기본키, 외부키 등을 통해서 기본키와 외부키의 연결, 해제 등을 해보았습니다.
