[MySQL] Table 생성, 제약조건, 컬럼 추가/수정, 외래키 설정, insert

Table 생성

MySQL Workbench 접속

생성한 scott Schema 클릭해 Tables 우클릭 → Create Table 클

Apply 클릭 → 위 코드 복사 후 Cancel

 

오라클에서 booklist 생성 후 MySQL에서 Table Refresh하면 오른쪽 사진과 같이 booklist 테이블이 생긴 것을 확인할 수 있음

---

자료형 제약조건

자주 쓰는 자료형

-int: 정수 자료형(float, bouble은 실수)

-varchar: 문자열 자료형, 가변길이(char은 고정 길이)

-text: 긴 문자열은 'text'로 별도 저장

-datetime: 날짜 자료형 저장

-tinyint: -128에서 127까지 저장

​

자주 쓰는 제약 조건

-not null: 빈 값은 받지 않는다는 뜻(null은 빈 값 허용)

-auto_increment

-unsigned: 0과 양수만 허용

-zerofill: 숫자의 자리수가 고정된 경우 빈 자리에 0을 넣음

-default now(): 날짜 컬럼의 기본값을 현재 시간으로

​

AUTO_INCREMENT

-오라클의 sequence를 대시하는 자동 숫자 증가 옵션

-숫자 자료형인 경우 다음 로우가 저장될 때 자동으로 1 증가

-varchar2는 없고, varchar가 가변 길이 문자를 나타냄

-constraint 없이 제약 사항을 표시함

-테이블 이름 앞에 '스키마이름'.을 반드시 붙여서 사용

---

column 추가/수정

테이블 필드 생성하기

-테이블명: memberlist

-필드명: num(int, 자동 증가, 기본키), name(varchar(30), not null), Birth(date, not null), bpoint(int),joindate(date, 기본값 now()), age(int),gender(varchar(3))

-comment: 회원 목록

-Charset/Collation: utf8mb4 / utf8mb4_0900_ai_general_ci

CREATE TABLE `scott`.`memberlist` (
  `num` INT NOT NULL,
  `name` VARCHAR(30) NOT NULL,
  `birth` DATETIME NOT NULL,
  `bpoint` INT NULL,
  `joindate` DATETIME NULL DEFAULT now(),
  `age` INT NULL,
  `gender` VARCHAR(3) NULL,
  PRIMARY KEY (`num`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = '회원목록';

필드 추가, 수정

생성된 booklist에 phone 필드 추가, bpoint default 0으로 수정

ALTER TABLE `scott`.`memberlist` 
ADD COLUMN `phone` VARCHAR(15) NOT NULL AFTER `birth`,
CHANGE COLUMN `bpoint` `bpoint` INT NULL DEFAULT 0 ;

memberlist 테이블

---

외래키 설정하기

테이블 필드 생성하기

-테이블명: rentlist

-필드: rentdate(datetime,default now()), num(int, AI, pk), booknum(int)

membernum(int), discount(int)

-comment: 매출목록

외래키 생성하기

-r_fk1설정: referenced table-booklist, column-bnum, referenced column-num

-r_fk2설정: referenced table-memberlist, column-mnum, referenced column-num

On Update, On Delete : CASCADE 로 설정

CREATE TABLE `scott`.`rentlist` (
  `rentdate` DATETIME NOT NULL DEFAULT now(),
  `num` INT NOT NULL AUTO_INCREMENT,
  `bnum` INT NULL,
  `mnum` INT NULL,
  `discount` INT NULL,
  PRIMARY KEY (`num`),
  INDEX `r_fk1_idx` (`bnum` ASC) VISIBLE,
  INDEX `r_fk2_idx` (`mnum` ASC) VISIBLE,
  CONSTRAINT `r_fk1`  FOREIGN KEY (`bnum`) 
  REFERENCES `scott`.`booklist` (`num`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `r_fk2` FOREIGN KEY (`mnum`) 
  REFERENCES `scott`.`memberlist` (`num`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = '매출목록';

 

rentlist 테이블

---

insert 추가

테이블 생성

-- booklist table
CREATE TABLE `scott`.`booklist` (
  `num` INT NOT NULL AUTO_INCREMENT,
  `subject` VARCHAR(100) NOT NULL,
  `makeyear` INT NULL,
  `inprice` INT NOT NULL,
  `rentprice` INT NOT NULL,
  `grade` VARCHAR(5) NULL DEFAULT 'all',
  PRIMARY KEY (`num`)  
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = '도서 목록';
-- memberlist table
CREATE TABLE `scott`.`memberlist` (
  `num` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL,
  `birth` DATETIME NOT NULL,
  `bpoint` INT NULL,
  `joindate` DATETIME NULL DEFAULT now(),
  `age` INT NULL,
  `gender` VARCHAR(3) NULL,
  PRIMARY KEY (`num`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = '회원목록';
ALTER TABLE `scott`.`memberlist` 
ADD COLUMN `phone` VARCHAR(15) NOT NULL AFTER `birth`,
CHANGE COLUMN `bpoint` `bpoint` INT NULL DEFAULT 0 ;
-- rentlist table
CREATE TABLE `scott`.`rentlist` (
  `rentdate` DATETIME NOT NULL DEFAULT now(),
  `num` INT NOT NULL AUTO_INCREMENT,
  `bnum` INT NULL,
  `mnum` INT NULL,
  `discount` INT NULL DEFAULT 0,
  PRIMARY KEY (`num`),
  INDEX `r_fk1_idx` (`bnum` ASC) VISIBLE,
  INDEX `r_fk2_idx` (`mnum` ASC) VISIBLE,
  CONSTRAINT `r_fk1`   FOREIGN KEY (`bnum`)   REFERENCES `scott`.`booklist` (`num`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `r_fk2`    FOREIGN KEY (`mnum`)  REFERENCES `scott`.`memberlist` (`num`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = '대여 목록';

insert

-- 세 개의 테이블에 각 필드의 자료형과 제약사항에 맞게 
-- 각 10개의 레코드를 insert 

-- booklist
insert into scott.booklist(subject,makeyear,inprice,rentprice,grade)
values('좀비아이',2020,12000,2500,'all');
insert into scott.booklist(subject,makeyear,inprice,rentprice,grade)
values('일곱해의 마지막',2020,12150,2000,'all');
insert into scott.booklist(subject,makeyear,inprice,rentprice,grade)
values('봉제인형 살인사건',2020,12000,2500,'13');
insert into scott.booklist(subject,makeyear,inprice,rentprice,grade)
values('쇼코의 미소',2019,10800,2500,'18');
insert into scott.booklist(subject,makeyear,inprice,rentprice,grade)
values('가면산장 살인사건',2018,13320,1500,'13');
insert into scott.booklist(subject,makeyear,inprice,rentprice,grade)
values('나미야 잡화점의 기적',2017,13320,2000,'18');
insert into scott.booklist(subject,makeyear,inprice,rentprice,grade)
values('유튜브영상편집',2020,20700,2500,'18');
insert into scott.booklist(subject,makeyear,inprice,rentprice,grade)
values('이것이자바다',2017,30000,3000,'18');
insert into scott.booklist(subject,makeyear,inprice,rentprice,grade)
values('JSP웹프로그래밍',2016,25000,2500,'13');
insert into scott.booklist(subject,makeyear,inprice,rentprice,grade)
values('오라클데이터베이스',2020,30000,3000,'all');

-- memberlist
insert into scott.memberlist(name,phone,birth,bpoint,age,gender)
values('박지성','010-1111-2222','1981/04/04',140,29,'F');
insert into scott.memberlist(name,phone,birth,bpoint,age,gender)
values('구자철','010-1111-2222','1982/05/05',230,25,'M');
insert into scott.memberlist(name,phone,birth,bpoint,age,gender)
values('지동원','010-1111-2222','1983/06/06',150,35,'F');
insert into scott.memberlist(name,phone,birth,bpoint,age,gender)
values('추신수','010-1111-2222','1984/07/07',240,28,'M');
insert into scott.memberlist(name,phone,birth,bpoint,age,gender)
values('손흥민','010-1111-2222','1982/09/23',220,23,'M');
insert into scott.memberlist(name,phone,birth,bpoint,age,gender)
values('구자철','010-1111-2222','1982/05/05',230,25,'M');
insert into scott.memberlist(name,phone,birth,bpoint,age,gender)
values('이청용','010-1111-2222','1981/06/14',440,236,'F');
insert into scott.memberlist(name,phone,birth,bpoint,age,gender)
values('이영표','010-1111-2222','1982/03/16',140,31,'F');
insert into scott.memberlist(name,phone,birth,bpoint,age,gender)
values('최지만','010-1111-2222','1983/04/14',340,29,'F');
insert into scott.memberlist(name,phone,birth,bpoint,age,gender)
values('홍길동','010-1111-2222','1980/06/05',240,30,'M');

-- rentlist
insert into scott.rentlist(bnum,mnum,discount) values(1,1,100);
insert into scott.rentlist(bnum,mnum,discount) values(1,10,100);
insert into scott.rentlist(bnum,mnum,discount) values(2,9,100);
insert into scott.rentlist(bnum,mnum,discount) values(3,4,100);
insert into scott.rentlist(bnum,mnum,discount) values(1,7,100);
insert into scott.rentlist(bnum,mnum,discount) values(5,6,100);
insert into scott.rentlist(bnum,mnum,discount) values(6,5,100);
insert into scott.rentlist(bnum,mnum,discount) values(7,4,100);
insert into scott.rentlist(bnum,mnum,discount) values(2,9,100);
insert into scott.rentlist(bnum,mnum,discount) values(2,2,100);
insert into scott.rentlist(bnum,mnum,discount) values(10,5,100);