CSGO Database

csgo.sql

-- 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 mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`stats`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`stats` ;

CREATE TABLE IF NOT EXISTS `mydb`.`stats` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `win` INT NULL,
  `loss` INT NULL,
  `kill` INT NULL,
  `death` INT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`team`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`team` ;

CREATE TABLE IF NOT EXISTS `mydb`.`team` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `p1` INT NOT NULL,
  `p2` INT NOT NULL,
  `p3` INT NOT NULL,
  `p4` INT NOT NULL,
  `p5` INT NOT NULL,
  `p6` INT NOT NULL,
  `p7` INT NOT NULL,
  `stats` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fKeyPlayer_idx` (`p1` ASC) VISIBLE,
  INDEX `fkeyP2_idx` (`p2` ASC) VISIBLE,
  INDEX `fkeyP3_idx` (`p3` ASC) VISIBLE,
  INDEX `fkeyP4_idx` (`p4` ASC) VISIBLE,
  INDEX `fkeyP5_idx` (`p5` ASC) VISIBLE,
  INDEX `fkeyP6_idx` (`p6` ASC) VISIBLE,
  INDEX `fkeyP7_idx` (`p7` ASC) VISIBLE,
  INDEX `fkeyStats_idx` (`stats` ASC) VISIBLE,
  CONSTRAINT `fKeyPlayer1`
    FOREIGN KEY (`p1`)
    REFERENCES `mydb`.`players` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fkeyP2`
    FOREIGN KEY (`p2`)
    REFERENCES `mydb`.`players` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fkeyP3`
    FOREIGN KEY (`p3`)
    REFERENCES `mydb`.`players` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fkeyP4`
    FOREIGN KEY (`p4`)
    REFERENCES `mydb`.`players` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fkeyP5`
    FOREIGN KEY (`p5`)
    REFERENCES `mydb`.`players` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fkeyP6`
    FOREIGN KEY (`p6`)
    REFERENCES `mydb`.`players` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fkeyP7`
    FOREIGN KEY (`p7`)
    REFERENCES `mydb`.`players` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fkeyStats`
    FOREIGN KEY (`stats`)
    REFERENCES `mydb`.`stats` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`players`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`players` ;

CREATE TABLE IF NOT EXISTS `mydb`.`players` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `team` INT NOT NULL,
  `stats` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fkeyTeam_idx` (`team` ASC) VISIBLE,
  INDEX `fkeyStats_idx` (`stats` ASC) VISIBLE,
  CONSTRAINT `fkeyTeam`
    FOREIGN KEY (`team`)
    REFERENCES `mydb`.`team` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fkeyPlayerStats`
    FOREIGN KEY (`stats`)
    REFERENCES `mydb`.`stats` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`matches`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`matches` ;

CREATE TABLE IF NOT EXISTS `mydb`.`matches` (
  `id` INT NOT NULL,
  `team1` INT NOT NULL,
  `team2` INT NOT NULL,
  `score` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fKeyT1_idx` (`team1` ASC) VISIBLE,
  INDEX `fKeyT2_idx` (`team2` ASC) VISIBLE,
  CONSTRAINT `fKeyT1`
    FOREIGN KEY (`team1`)
    REFERENCES `mydb`.`team` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fKeyT2`
    FOREIGN KEY (`team2`)
    REFERENCES `mydb`.`team` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`tournament`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`tournament` ;

CREATE TABLE IF NOT EXISTS `mydb`.`tournament` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `m1` INT NOT NULL,
  `m2` INT NOT NULL,
  `m3` INT NOT NULL,
  `m4` INT NOT NULL,
  `m5` INT NOT NULL,
  `m6` INT NOT NULL,
  `m7` INT NOT NULL,
  `m8` INT NOT NULL,
  `m9` INT NOT NULL,
  `m10` INT NOT NULL,
  `winner` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fKeyM1_idx` (`m1` ASC) VISIBLE,
  INDEX `fKeyM2_idx` (`m2` ASC) VISIBLE,
  INDEX `fKeyM3_idx` (`m3` ASC) VISIBLE,
  INDEX `fKeyM4_idx` (`m4` ASC) VISIBLE,
  INDEX `fKeyM5_idx` (`m5` ASC) VISIBLE,
  INDEX `fKeyM6_idx` (`m6` ASC) VISIBLE,
  INDEX `fKeyM7_idx` (`m7` ASC) VISIBLE,
  INDEX `fKeyM8_idx` (`m8` ASC) VISIBLE,
  INDEX `fKeyM9_idx` (`m9` ASC) VISIBLE,
  INDEX `fKeyM10_idx` (`m10` ASC) VISIBLE,
  INDEX `fKeyWinner_idx` (`winner` ASC) VISIBLE,
  CONSTRAINT `fKeyM1`
    FOREIGN KEY (`m1`)
    REFERENCES `mydb`.`matches` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fKeyM2`
    FOREIGN KEY (`m2`)
    REFERENCES `mydb`.`matches` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fKeyM3`
    FOREIGN KEY (`m3`)
    REFERENCES `mydb`.`matches` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fKeyM4`
    FOREIGN KEY (`m4`)
    REFERENCES `mydb`.`matches` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fKeyM5`
    FOREIGN KEY (`m5`)
    REFERENCES `mydb`.`matches` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fKeyM6`
    FOREIGN KEY (`m6`)
    REFERENCES `mydb`.`matches` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fKeyM7`
    FOREIGN KEY (`m7`)
    REFERENCES `mydb`.`matches` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fKeyM8`
    FOREIGN KEY (`m8`)
    REFERENCES `mydb`.`matches` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fKeyM9`
    FOREIGN KEY (`m9`)
    REFERENCES `mydb`.`matches` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fKeyM10`
    FOREIGN KEY (`m10`)
    REFERENCES `mydb`.`matches` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fKeyWinner`
    FOREIGN KEY (`winner`)
    REFERENCES `mydb`.`team` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

DROP PROCEDURE IF EXISTS `WinLoss`;

CREATE PROCEDURE `WinLoss`(IN player INT)
	SELECT win.stats / loss.stats
	WHERE @player = id;

CREATE PROCEDURE `KillDeath`(IN player INT)
	SELECT kills.stats / death.stats
    WHERE @player = id;