我有一个问题,我有一个表,其中一些字段不为空,还有几个字段可以有空值,问题是当我想向它上传数据时,我只加载具有所有值的行,并且具有空字段的行不会加载它们。假设如果您没有 not null 选项,则可以取 null 值。这是我的数据库的代码,我遇到的问题是输入框表:
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema pizzahutalmacenbd
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `pizzahutalmacenbd` ;
-- -----------------------------------------------------
-- Schema pizzahutalmacenbd
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `pizzahutalmacenbd` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;
USE `pizzahutalmacenbd` ;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`guiaremision`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`guiaremision` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`guiaremision` (
`idGuiaRemision` INT(11) NOT NULL,
`FechaEntrega` DATE NOT NULL,
PRIMARY KEY (`idGuiaRemision`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`tipoinsumo`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`tipoinsumo` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`tipoinsumo` (
`idTipoInsumo` INT(11) NOT NULL,
`Tipo` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idTipoInsumo`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`unidad`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`unidad` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`unidad` (
`idUnidad` INT(11) NOT NULL,
`Unidad` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idUnidad`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`insumos`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`insumos` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`insumos` (
`idInsumos` INT(11) NOT NULL,
`Nombre` VARCHAR(45) NOT NULL,
`Cantidad` FLOAT NOT NULL,
`VidaUtil` INT NULL,
`Descongelamiento` INT(100) NULL,
`TipoInsumo_idTipoInsumo` INT(11) NOT NULL,
`Unidad_idUnidad` INT(11) NOT NULL,
`precioUnitario` DOUBLE NOT NULL,
PRIMARY KEY (`idInsumos`),
INDEX `fk_Insumos_TipoInsumo_idx` (`TipoInsumo_idTipoInsumo` ASC) VISIBLE,
INDEX `fk_Insumos_Unidad1_idx` (`Unidad_idUnidad` ASC) VISIBLE,
CONSTRAINT `fk_Insumos_TipoInsumo`
FOREIGN KEY (`TipoInsumo_idTipoInsumo`)
REFERENCES `pizzahutalmacenbd`.`tipoinsumo` (`idTipoInsumo`),
CONSTRAINT `fk_Insumos_Unidad1`
FOREIGN KEY (`Unidad_idUnidad`)
REFERENCES `pizzahutalmacenbd`.`unidad` (`idUnidad`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`ordenbaja`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`ordenbaja` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`ordenbaja` (
`idOrdenBaja` INT(11) NOT NULL,
`Fecha` DATE NOT NULL,
PRIMARY KEY (`idOrdenBaja`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`responsablecocina`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`responsablecocina` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`responsablecocina` (
`idResponsableCocina` INT(11) NOT NULL,
`Nombre` VARCHAR(45) NOT NULL,
`Apellidos` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idResponsableCocina`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`ordendescongelado`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`ordendescongelado` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`ordendescongelado` (
`idOrdenDescongelado` INT(11) NOT NULL,
`Fecha` DATE NOT NULL,
`ResponsableCocina_idResponsableCocina` INT(11) NOT NULL,
PRIMARY KEY (`idOrdenDescongelado`),
INDEX `fk_OrdenDescongelado_ResponsableCocina1_idx` (`ResponsableCocina_idResponsableCocina` ASC) VISIBLE,
CONSTRAINT `fk_OrdenDescongelado_ResponsableCocina1`
FOREIGN KEY (`ResponsableCocina_idResponsableCocina`)
REFERENCES `pizzahutalmacenbd`.`responsablecocina` (`idResponsableCocina`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`ordenpedido`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`ordenpedido` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`ordenpedido` (
`idOrdenPedido` INT(11) NOT NULL,
`Fecha` DATE NOT NULL,
PRIMARY KEY (`idOrdenPedido`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`ubicacion`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`ubicacion` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`ubicacion` (
`idUbicacion` INT(11) NOT NULL,
`Lugar` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idUbicacion`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`cajainsumo`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`cajainsumo` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`cajainsumo` (
`idCajaInsumo` INT(11) NOT NULL,
`FechaVencimiento` DATE NOT NULL,
`Stock` FLOAT NOT NULL,
`Insumos_idInsumos` INT(11) NOT NULL,
`OrdenBaja_idOrdenBaja` INT(11) NULL,
`GuiaRemision_idGuiaRemision` INT(11) NOT NULL,
`OrdenPedido_idOrdenPedido` INT(11) NOT NULL,
`OrdenDescongelado_idOrdenDescongelado` INT(11) NULL,
`Ubicación_idUbicación` INT(11) NOT NULL,
`CantidadBaja` FLOAT NULL,
PRIMARY KEY (`idCajaInsumo`),
INDEX `fk_CajaInsumo_Insumos1_idx` (`Insumos_idInsumos` ASC) VISIBLE,
INDEX `fk_CajaInsumo_OrdenBaja1_idx` (`OrdenBaja_idOrdenBaja` ASC) VISIBLE,
INDEX `fk_CajaInsumo_GuiaRemision1_idx` (`GuiaRemision_idGuiaRemision` ASC) VISIBLE,
INDEX `fk_CajaInsumo_OrdenPedido1_idx` (`OrdenPedido_idOrdenPedido` ASC) VISIBLE,
INDEX `fk_CajaInsumo_OrdenDescongelado1_idx` (`OrdenDescongelado_idOrdenDescongelado` ASC) VISIBLE,
INDEX `fk_CajaInsumo_Ubicación1_idx` (`Ubicación_idUbicación` ASC) VISIBLE,
CONSTRAINT `fk_CajaInsumo_GuiaRemision1`
FOREIGN KEY (`GuiaRemision_idGuiaRemision`)
REFERENCES `pizzahutalmacenbd`.`guiaremision` (`idGuiaRemision`),
CONSTRAINT `fk_CajaInsumo_Insumos1`
FOREIGN KEY (`Insumos_idInsumos`)
REFERENCES `pizzahutalmacenbd`.`insumos` (`idInsumos`),
CONSTRAINT `fk_CajaInsumo_OrdenBaja1`
FOREIGN KEY (`OrdenBaja_idOrdenBaja`)
REFERENCES `pizzahutalmacenbd`.`ordenbaja` (`idOrdenBaja`),
CONSTRAINT `fk_CajaInsumo_OrdenDescongelado1`
FOREIGN KEY (`OrdenDescongelado_idOrdenDescongelado`)
REFERENCES `pizzahutalmacenbd`.`ordendescongelado` (`idOrdenDescongelado`),
CONSTRAINT `fk_CajaInsumo_OrdenPedido1`
FOREIGN KEY (`OrdenPedido_idOrdenPedido`)
REFERENCES `pizzahutalmacenbd`.`ordenpedido` (`idOrdenPedido`),
CONSTRAINT `fk_CajaInsumo_Ubicación1`
FOREIGN KEY (`Ubicación_idUbicación`)
REFERENCES `pizzahutalmacenbd`.`ubicacion` (`idUbicacion`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`ordensalida`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`ordensalida` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`ordensalida` (
`idOrdenSalida` INT(11) NOT NULL,
`Fecha` DATE NOT NULL,
`ResponsableCocina_idResponsableCocina` INT(11) NOT NULL,
`Turno` TINYINT(1) NOT NULL,
PRIMARY KEY (`idOrdenSalida`),
INDEX `fk_OrdenSalida_ResponsableCocina1_idx` (`ResponsableCocina_idResponsableCocina` ASC) VISIBLE,
CONSTRAINT `fk_OrdenSalida_ResponsableCocina1`
FOREIGN KEY (`ResponsableCocina_idResponsableCocina`)
REFERENCES `pizzahutalmacenbd`.`responsablecocina` (`idResponsableCocina`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`cajainsumo_has_ordensalida`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`cajainsumo_has_ordensalida` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`cajainsumo_has_ordensalida` (
`CajaInsumo_idCajaInsumo` INT(11) NOT NULL,
`OrdenSalida_idOrdenSalida` INT(11) NOT NULL,
`Cantidad` FLOAT NOT NULL,
PRIMARY KEY (`CajaInsumo_idCajaInsumo`, `OrdenSalida_idOrdenSalida`),
INDEX `fk_CajaInsumo_has_OrdenSalida_OrdenSalida1_idx` (`OrdenSalida_idOrdenSalida` ASC) VISIBLE,
INDEX `fk_CajaInsumo_has_OrdenSalida_CajaInsumo1_idx` (`CajaInsumo_idCajaInsumo` ASC) VISIBLE,
CONSTRAINT `fk_CajaInsumo_has_OrdenSalida_CajaInsumo1`
FOREIGN KEY (`CajaInsumo_idCajaInsumo`)
REFERENCES `pizzahutalmacenbd`.`cajainsumo` (`idCajaInsumo`),
CONSTRAINT `fk_CajaInsumo_has_OrdenSalida_OrdenSalida1`
FOREIGN KEY (`OrdenSalida_idOrdenSalida`)
REFERENCES `pizzahutalmacenbd`.`ordensalida` (`idOrdenSalida`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`dias`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`dias` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`dias` (
`idDias` INT(11) NOT NULL,
`Dia` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idDias`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`demanda`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`demanda` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`demanda` (
`Insumos_idInsumos` INT(11) NOT NULL,
`Dias_idDias` INT(11) NOT NULL,
`Demanda` FLOAT NOT NULL,
PRIMARY KEY (`Insumos_idInsumos`, `Dias_idDias`),
INDEX `fk_Insumos_has_Días_Días1_idx` (`Dias_idDias` ASC) VISIBLE,
INDEX `fk_Insumos_has_Días_Insumos1_idx` (`Insumos_idInsumos` ASC) VISIBLE,
CONSTRAINT `fk_Insumos_has_Días_Días1`
FOREIGN KEY (`Dias_idDias`)
REFERENCES `pizzahutalmacenbd`.`dias` (`idDias`),
CONSTRAINT `fk_Insumos_has_Días_Insumos1`
FOREIGN KEY (`Insumos_idInsumos`)
REFERENCES `pizzahutalmacenbd`.`insumos` (`idInsumos`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
我们有如下表结构
观察结果
name
总是NOT NULL
需要输入一个值email
可以NULL
留空,或者您可以指定一个默认值age
为NULL
但不为空,分配一个默认值,这意味着如果用户未输入任何内容,则该列采用的值0
例 1
我不会插入任何值
email
,所以age
我的查询结构应该如下如果我做一个我
SELECT
应该得到这个解释
由于我没有为最后两列分配任何值,因此我在执行时不会调用它们
INSERT
例 2
解释
INSERT
这一次,如果我在为什么我会给一个值中调用 2 列,email
但是因为age
我会保留默认值,所以我不会将它放在句子中如果我做,我的结果
SELECT
应该是例 3
这次我将为现有的 3 个列中的每一列分配一个自定义值,所以如果我将它们放在
INSERT
如果我这样做,
SELECT
我的结果应该是这样的