Chap.02. DDL

CoLab

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

https://bit.ly/39G3WzN

Create Table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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

1
2
3
4
5
6
7
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

1
2
3
4
5
6
create view simple as
select name, model from mechanic;

describe mechanic;

select * from mechanic;