create.sql

-- Active: 1704473444253@@127.0.0.1@3306@siniestros
CREATE DATABASE IF NOT EXISTS SINIESTROS;

USE SINIESTROS;

SHOW VARIABLES LIKE 'secure_file_priv';

DROP TABLE IF EXISTS `HECHOS`;

CREATE TABLE Hechos (
    ID VARCHAR(10) PRIMARY KEY,
    N_VICTIMAS INT,
    FECHA DATE,
    AAAA INT,
    SEMESTRE INT,
    ANIO_SEMESTRE VARCHAR(10),
    MM INT,
    DD INT,
    ASPO VARCHAR(10),
    HORA TIME,
    HH INT,
    DIA VARCHAR(10),
    LUGAR_DEL_HECHO VARCHAR(255),
    TIPO_DE_CALLE VARCHAR(10),
    CALLE VARCHAR(255),
    CRUCE VARCHAR(255),
    DIRECCION_NORMALIZADA VARCHAR(255),
    COMUNA INT,
    XY_CABA VARCHAR(50),
    LONGITUD FLOAT,
    LATITUD FLOAT,
    PARTICIPANTES VARCHAR(50),
    VICTIMA VARCHAR(50),
    ACUSADO VARCHAR(50)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET GLOBAL local_infile = ON;

LOAD DATA local INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/homicidios_hechos.csv' INTO TABLE HECHOS
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; 

SELECT * FROM HECHOS;

DROP TABLE IF EXISTS `VICTIMAS`;

CREATE TABLE VICTIMAS (
    ID VARCHAR(10),
    FECHA DATE,
    AAAA INT,
    MM INT,
    DD INT,
    ROL VARCHAR(20),
    VICTIMA VARCHAR(20),
    SEXO VARCHAR(10),
    EDAD INT,
    FECHA_FALLECIMIENTO DATETIME,
    RANGO_ETARIO VARCHAR(10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOAD DATA local INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/homicidios_victimas.csv' INTO TABLE VICTIMAS
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; 


SELECT * FROM VICTIMAS;

DROP TABLE IF EXISTS `COMUNAS`;

CREATE TABLE COMUNAS (
    COMUNA INT PRIMARY KEY,
    BARRIO VARCHAR(255),
    AREA FLOAT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOAD DATA local INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/comunas.csv' INTO TABLE COMUNAS
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; 


SELECT * FROM COMUNAS;


DROP TABLE IF EXISTS `POBLACION`;

CREATE TABLE POBLACION (
    COMUNA INT PRIMARY KEY,
    A2016 INT,
    A2017 INT,
    A2018 INT,
    A2019 INT,
    A2020 INT,
    A2021 INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOAD DATA local INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/poblacion.csv' INTO TABLE POBLACION
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; 


SELECT * FROM POBLACION;