Chap.02. DDL

CoLab

https://colab.research.google.com/drive/1wts3NIBojdz2mBUfBott6vAA0kqfsMq2

https://bit.ly/39G3WzN

Create Table

DROP DATABASE IF EXISTS `gunpladb` ;

CREATE DATABASE IF NOT EXISTS `gunpladb` 
  DEFAULT CHARACTER SET utf8 
  DEFAULT COLLATE utf8_general_ci;

USE `gunpladb` ;

CREATE TABLE `mechanic` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `model` VARCHAR(100) NOT NULL,
  `manufacturer` VARCHAR(255) NULL,
  `armor` VARCHAR(255) NULL,
  `height` DECIMAL(7,2) NULL,
  `weight` DECIMAL(7,2) NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARACTER SET utf8 
  DEFAULT COLLATE utf8_general_ci;

Referential Integrity: Set Null

DROP DATABASE IF EXISTS `ridb` ;
CREATE DATABASE IF NOT EXISTS `ridb`;
USE `ridb` ;
CREATE TABLE `mechanic` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;
CREATE TABLE `gunpla` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mid` INT,
  `grade` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`mid`) REFERENCES `mechanic` (`id`)
    ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE = InnoDB;
INSERT INTO mechanic (id, name) VALUES
  (1, 'Gundam'),
  (2, 'Gun Cannon'),
  (3, 'Gun Tank');
INSERT INTO gunpla (id, mid, grade) VALUES
  (1, 1, 'MG'),
  (2, 2, 'SD'),
  (3, 3, 'HGUC');
select * from gunpla;
delete from mechanic where name='Gundam';
select * from gunpla;

Referential Integrity: Cascade

DROP DATABASE IF EXISTS `ridb` ;
CREATE DATABASE IF NOT EXISTS `ridb`;
USE `ridb` ;
CREATE TABLE `mechanic` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;
CREATE TABLE `gunpla` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mid` INT,
  `grade` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`mid`) REFERENCES `mechanic` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;
INSERT INTO mechanic (id, name) VALUES
  (1, 'Gundam'),
  (2, 'Gun Cannon'),
  (3, 'Gun Tank');
INSERT INTO gunpla (id, mid, grade) VALUES
  (1, 1, 'MG'),
  (2, 2, 'SD'),
  (3, 3, 'HGUC');
select * from gunpla;
delete from mechanic where name='Gundam';
select * from gunpla;

Alter Table

alter table mechanic add designer varchar(10);

alter table mechanic modify column designer varchar(100);

alter table mechanic change column designer artist varchar(100);

alter table mechanic drop designer;

Create View

create view simple as
    select name, model from mechanic;

describe mechanic;

select * from mechanic;