CREATE TABLE IF NOT EXISTS bookshop consumer idconsumer INT 11 NOT NUL

 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
28
29
30
31
32
33
34
35
36
CREATE TABLE IF NOT EXISTS `bookshop`.`consumer` (
`idconsumer` INT(11) NOT NULL,
`fio` VARCHAR(70) NOT NULL,
`db` DATE NULL DEFAULT NULL,
`email` VARCHAR(45) NULL DEFAULT NULL, `phone` CHAR(10) NULL,
`address` VARCHAR(100) NOT NULL, PRIMARY KEY (`idconsumer`),
UNIQUE INDEX `idconsumer_UNIQUE` (`idconsumer` ASC)) ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE TABLE IF NOT EXISTS `bookshop`.`order` (
`idorder` INT(11) NOT NULL,
`time` DATETIME NOT NULL,
`status` ENUM('выполнен','проверяется','отклонен','доставка') NOT NULL, `price` FLOAT NULL DEFAULT NULL,
`consumer_idconsumer` INT(11) NOT NULL,
PRIMARY KEY (`idorder`),
INDEX `fk_order_consumer_idx` (`consumer_idconsumer` ASC), CONSTRAINT `fk_order_consumer`
FOREIGN KEY (`consumer_idconsumer`) REFERENCES `bookshop`.`consumer` (`idconsumer`) ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `bookshop`.`store` (
`order_idorder` INT(11) NOT NULL,
`books_id` MEDIUMINT(9) NOT NULL,
`price` FLOAT NOT NULL,
`quantity` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`order_idorder`, `books_id`),
INDEX `fk_store_order1_idx` (`order_idorder` ASC), INDEX `fk_store_books1_idx` (`books_id` ASC), CONSTRAINT `fk_store_order1`
FOREIGN KEY (`order_idorder`) REFERENCES `bookshop`.`order` (`idorder`) ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_store_books1` FOREIGN KEY (`books_id`) REFERENCES `bookshop`.`books` (`id`) ON DELETE NO ACTION
ON UPDATE NO ACTION) ENGINE = InnoDB;
source c:/bookshopDATA.sql;
SELECT * FROM consumer;
SELECT * FROM order;
SELECT * FROM store;