instalacion

<?php

$host='localhost';
$user='root';
$pass='';
$con=mysqli_connect($host, $user,$pass);
if (!$con){
	die("ERROR AL CONECTAR: ".mysqli_connect_error());
} echo "CONEXI&OacuteN HECHA</br>";


$sql="CREATE DATABASE baloncesto;";
if (mysqli_query($con, $sql)){
	echo "BASE DE DATOS CREADA</br>";
} else {
	echo "ERROR AL CREAR BASE DE DATOS: ". mysqli_error($con);
}

$sql="USE baloncesto;";
$sql.="GRANT ALL ON baloncesto.* to limitado@localhost identified by 'limitado';";
if (mysqli_multi_query($con, $sql)){
	echo "USUARIO CREADO.</br>";
}
else{
	echo "ERROR AL CREAR USUARIO".mysqli_error($con);
}

mysqli_close($con);

$host="localhost";
$user="limitado";
$pass="limitado";
$db_name="baloncesto";

$con2=mysqli_connect($host, $user,$pass, $db_name);
if (!$con2){
	die("ERROR AL CONECTAR: ".mysqli_connect_error());
} echo "CONEXI&OacuteN HECHA</br>";

$sql="CREATE TABLE `clases`(
`codigo` char(3) not null,
`grupo` varchar(20) not null,
`nombre_tutor` varchar(40),
`puntuacion` int unsigned,
`capitan` char(7),
primary key (`codigo`));";

$sql.="CREATE TABLE `puestos`(
`codigo` tinyint(3) not null auto_increment,
`nombre` varchar(10) not null,
`descripcion` text null,
primary key (`codigo`));";

$sql.="CREATE TABLE `jugadores`(
`codalumno` char(7) not null,
`nombre` varchar(20) not null,
`apellido` varchar(20) not null,
`tantos_marcados` smallint unsigned default 0,
`puesto` tinyint(3) not null,
`clase` char(3) not null,
primary key (`codalumno`),
foreign key (`clase`) references clases(`codigo`),
foreign key (`puesto`) references puestos(`codigo`));";

$sql.="CREATE TABLE `hco_capitanes`(
`id` int auto_increment not null,
`nomantc` varchar(20) not null,
`apeantc` varchar(20) not null,
`nomactc` varchar(20) not null,
`apeactc` varchar(20) not null,
`clase` char(3) not null,
`fecambio` datetime not null,
primary key (`id`));";

if (mysqli_multi_query($con2, $sql)) {
    echo "TABLAS CREADAS CON &EacuteXITO</br>";
} else {
    echo "ERROR AL CREAR LAS TABLAS:</br> " .mysqli_error($con2);
}

mysqli_close($con2);

$con2=mysqli_connect($host, $user, $pass, $db_name);

$sql='INSERT INTO `puestos` set nombre = "BASE";';
$sql.='INSERT INTO `puestos` set nombre = "ALERO";';
$sql.='INSERT INTO `puestos` set nombre = "ALA-PIVOT";';
$sql.='INSERT INTO `puestos` set nombre = "PIVOT";';
$sql.='INSERT INTO `puestos` set nombre = "ESCOLTA";';

$sql.='INSERT INTO `clases` VALUES
("E1A","1 ESO A","FEDERICO PEREZ",6,"E1A603"),
("E1B","1 ESO B","TERESA CANO",2,"E1B603"),
("E2A","2 ESO A","JAVIER GONZALEZ",0,"E2A606"),
("E2B","2 ESO B","PATRICIA SANCHEZ",4,"E2B606");';

$sql.='INSERT INTO jugadores VALUES ("E2A666","MEPHISTO","ROZCO",16,3,"E2A"),
                             ("E2A766","MERLIN","WIZARD",16,3,"E2A"),
                             ("E2A655","MORGANA","PENDRAGON",16,1,"E2A"),
                             ("E2A676","MELQUIADES","BUHO",20,2,"E2A"),
							               ("E2A686","GIOVANNI","BERTUCCIO",16,5,"E2A"),
							               ("E2A606","ANNA","KARENINA",16,1,"E2A"),
							               ("E2A696","AL","DEGEA",16,4,"E2A"),
                             ("E1A777","ALEPH","ONSO",16,3,"E1A"),
                             ("E1A666","OLGA","SCOTT",16,3,"E1A"),
                             ("E1A888","PAUVAR","ELA",16,1,"E1A"),
                             ("E1A776","MELVIN","SQUIRRELS",20,1,"E1A"),
							               ("E1A689","JOHNNY","BERTO",16,5,"E1A"),
							               ("E1A603","ENRIQUE","ALFARERO",16,2,"E1A"),
							               ("E1A016","ALBUS","DEKA",16,4,"E1A"),
							               ("E2B666","EMMET","BROWN",16,3,"E2B"),
                             ("E2B626","PHIL","LIP",16,3,"E2B"),
                             ("E2B636","LINUS","STROMBERG",16,1,"E2B"),
                             ("E2B676","PAUL","FONTOFTHE",20,2,"E2B"),
							               ("E2B686","ANGEL","BIGTABLES",16,5,"E2B"),
							               ("E2B606","OSKAR","KRUM",16,1,"E2B"),
							               ("E2B696","TITTO","LOPEZ",16,4,"E2B"),
							               ("E1B777","RUCH","WORTH",16,3,"E1B"),
                             ("E1B666","ALF","MELMAC",16,3,"E1B"),
                             ("E1B996","NAZARIUS","FLINT",16,1,"E1B"),
                             ("E1B776","MELVIN","MCFLY",20,2,"E1B"),
							               ("E1B689","LORDDARTH","VADER",16,1,"E1B"),
							               ("E1B603","DRACO","MALFOY",16,1,"E1B"),
							               ("E1B016","SEVERIUS","STUKA",16,4,"E1B");';

 if (mysqli_multi_query($con2, $sql)) {
 echo "DATOS INSERTADOS CORRECTAMENTE</br>";
														 } else {
														 echo "ERROR AL INSERTAR DATOS: </br>" . mysqli_error($con2);
														 }

														 mysqli_close($con2);

														 $con2=mysqli_connect($host, $user, $pass, $db_name);

/*PROCEDIMIENTO= EJERCICIO1*/
$sql="create procedure ejercicio1()
BEGIN
declare grupo1 varchar(20);
declare grupo2 varchar(20);
select clase into grupo1 from jugadores group by clase order by sum(tantos_marcados) desc limit 1;
select clase into grupo2 from jugadores group by clase order by sum(tantos_marcados) desc limit 1, 1;
update clases set puntuacion=puntuacion+2 where codigo=grupo1;
update clases set puntuacion=puntuacion+1 where codigo=grupo2;
END;";

if (mysqli_query($con2, $sql)) {
echo "PROCEDIMIENTO CREADO CORRECTAMENTE</br>";
														} else {
														echo "ERROR AL CREAR PROCEDIMIENTO: </br>" . mysqli_error($con2);
														}

														mysqli_close($con2);

														$con2=mysqli_connect($host, $user, $pass, $db_name);

/*EVENTO=EJERCICIO2*/
$sql="set global event_scheduler='ON'"; /*PARA CAMBIAR LA VARIABLE DE MYSQL Y NO TENER QUE HACERLO MANUAL*/
$sql.="CREATE EVENT ejercicio2 ON schedule every 1 week starts '2017-02-19 00:00:00' do call ejercicio1();";

if (mysqli_multi_query($con2, $sql)) {
echo "EVENTO CREADO CORRECTAMENTE</br>";
														} else {
														echo "ERROR AL CREAR EVENTO: </br>" . mysqli_error($con2);
														}

														mysqli_close($con2);

														$con2=mysqli_connect($host, $user, $pass, $db_name);

/*TRIGGER EJERCICIO3*/
$sql="CREATE TRIGGER ejercicio3
AFTER
update on clases
for each row
BEGIN
declare clasecambio char(3);
declare nombreviejo varchar(20);
declare apellidoviejo varchar(20);
declare nombrenuevo varchar(20);
declare apellidonuevo varchar(20);
set clasecambio=(select clase from jugadores where codalumno=old.capitan);
set nombreviejo=(select nombre from jugadores where codalumno=old.capitan);
set apellidoviejo=(select apellido from jugadores where codalumno=old.capitan);
set nombrenuevo=(select nombre from jugadores where codalumno=new.capitan);
set apellidonuevo=(select apellido from jugadores where codalumno=new.capitan);
insert into hco_capitanes (nomantc,apeantc,nomactc,apeactc,clase,fecambio)
values (nombreviejo,apellidoviejo,nombrenuevo,apellidonuevo,clasecambio, NOW());
end;";

if (mysqli_query($con2, $sql)) {
echo "TRIGGER CREADO CORRECTAMENTE</br>";
} else {
echo "ERROR AL CREAR TRIGGER: </br>" . mysqli_error($con2);
}

mysqli_close($con2);
?>