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 usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8'); INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4'); INSERT INTO usertbl VALUES('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
▷ 구매 테이블 데이터 입력
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의 테이블에서 외부키의 이름을 확인할 수 있습니다.
테이블 생성, 기본키, 외부키 등을 통해서 기본키와 외부키의 연결, 해제 등을 해보았습니다.
기본키, 외부키를 잘 사용하면 값이 쉽게 변경되는 것들을 막을 수 있겠네요!!
많은 분들의 피드백은 언제나 환영합니다! 많은 댓글 부탁드려요~~
'BackEnd > DB(SQL)' 카테고리의 다른 글
[DB_MySQL] 이것이 MySQL이다 ch08 테이블과 뷰 2(뷰, view) (0) | 2023.02.09 |
---|---|
[DB_MySQL] 이것이 MySQL이다 ch08 테이블과 뷰 1-1(테이블 제약 조건 및 수정 방법 실습) (0) | 2023.02.09 |
[DB_MySQL] 이것이 MySQL이다 ch07 SQL 고급5 (SQL 프로그래밍, if else, case, while) (0) | 2023.02.08 |
[DB_MySQL] 이것이 MySQL이다 ch07 SQL 고급 4(피벗, JSON 데이터, 조인) (0) | 2023.02.07 |
[DB_MySQL] 이것이 MySQL이다 ch07 SQL 고급 3(수학 함수, 날짜 및 시간 함수, 시스템 정보 함수) (0) | 2023.02.07 |