Primer video

vistas

vista1
CREATE  OR REPLACE VIEW `fiesta`.`Toreros_premios` AS
select x.nombre_tore,x.apodo, x.orejas,x.rabos, c.puerta_grande from torero x, premios c where x.idtorero=c.idtorero_1 order by x.rfc;
VISTA 3
CREATE  OR REPLACE VIEW `fiesta`.`vista3` AS
select x.feria,.nombre_torero,c.idcorri_torero, e.nombre_apoderado from corridas x, torero c, corri_torero e, apoderado a
where x.id_corridas=a.id_corrida
and c.id_torero=e.id_torero and d.id_apoderado=a.id_apoderado1 order by c.idtorero;
VISTA 4
create view edad as select nombre_toro, x.feria,
 (year(x.fecha_celebracion)- year (c.fecha_nacimiento))
 as Edad from corridas x,toros c order by id_toros;
VISTA 5
CREATE  OR REPLACE VIEW `fiesta`.`padrino` AS
select x.nombre_torero,x.padrino from torero a order by x.idtorero;

Creacion de Usuarios

line 4
mysql> CREATE USER 'maria'@'localhost' IDENTIFIED BY 'maria';
Query OK, 0 rows affected (0.20 sec)

mysql> use mysql
Database changed
mysql> INSERT INTO user VALUES('localhost','febe',PASSWORD('febebe'),'Y','Y',
-> 'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','
N','N','N',
-> 'N','N','N','','','','',0,0,0,0);
Query OK, 1 row affected (0.00 sec)

mysql> describe user;
+-----------------------+-----------------------------------+------+-----+------
---+-------+
| Field | Type | Null | Key | Defau
lt | Extra |
+-----------------------+-----------------------------------+------+-----+------
---+-------+
| Host | char(60) | NO | PRI |
| |
| User | char(16) | NO | PRI |
| |
| Password | char(41) | NO | |
| |
| Select_priv | enum('N','Y') | NO | | N
| |
| Insert_priv | enum('N','Y') | NO | | N
| |
| Update_priv | enum('N','Y') | NO | | N
| |
| Delete_priv | enum('N','Y') | NO | | N
| |
| Create_priv | enum('N','Y') | NO | | N
| |
| Drop_priv | enum('N','Y') | NO | | N
| |
| Reload_priv | enum('N','Y') | NO | | N
| |
| Shutdown_priv | enum('N','Y') | NO | | N
| |
| Process_priv | enum('N','Y') | NO | | N
| |
| File_priv | enum('N','Y') | NO | | N
| |
| Grant_priv | enum('N','Y') | NO | | N
| |
| References_priv | enum('N','Y') | NO | | N
| |
| Index_priv | enum('N','Y') | NO | | N
| |
| Alter_priv | enum('N','Y') | NO | | N
| |
| Show_db_priv | enum('N','Y') | NO | | N
| |
| Super_priv | enum('N','Y') | NO | | N
| |
| Create_tmp_table_priv | enum('N','Y') | NO | | N
| |
| Lock_tables_priv | enum('N','Y') | NO | | N
| |
| Execute_priv | enum('N','Y') | NO | | N
| |
| Repl_slave_priv | enum('N','Y') | NO | | N
| |
| Repl_client_priv | enum('N','Y') | NO | | N
| |
| Create_view_priv | enum('N','Y') | NO | | N
| |
| Show_view_priv | enum('N','Y') | NO | | N
| |
| Create_routine_priv | enum('N','Y') | NO | | N
| |
| Alter_routine_priv | enum('N','Y') | NO | | N
| |
| Create_user_priv | enum('N','Y') | NO | | N
| |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | |
| |
| ssl_cipher | blob | NO | | NULL
| |
| x509_issuer | blob | NO | | NULL
| |
| x509_subject | blob | NO | | NULL
| |
| max_questions | int(11) unsigned | NO | | 0
| |
| max_updates | int(11) unsigned | NO | | 0
| |
| max_connections | int(11) unsigned | NO | | 0
| |
| max_user_connections | int(11) unsigned | NO | | 0
| |
+-----------------------+-----------------------------------+------+-----+------
---+-------+
37 rows in set (0.05 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

mysql> $ mysql -u mariano -p
-> $ mysql -u mariano -p-;


Database changed
mysql> grant select, insert on toros to ademar@'192.68.200.%' identified by 'ade
mar';
Query OK, 0 rows affected (0.02 sec)

mysql> select host, user, password from mysql.user where user='ademar';
+--------------+--------+-------------------------------------------+
| host | user | password |
+--------------+--------+-------------------------------------------+
| 192.68.200.% | ademar | *D82646DCB760100E74534AAB6A17B5875DF2181F |
+--------------+--------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select host, user, password from mysql.user where user='maria';
+-----------+-------+-------------------------------------------+
| host | user | password |
+-----------+-------+-------------------------------------------+
| localhost | maria | *8061C323A725701555411A7E18421F077A840CD7 |
+-----------+-------+-------------------------------------------+
1 row in set (0.01 sec)

mysql> select host, user, password from mysql.user where user='febe';
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | febe | *2D2CFA99BA23611614AE9D0A6F29DA2926ED478A |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select host, user, password from mysql.user where user='maria';
+-----------+-------+-------------------------------------------+
| host | user | password |
+-----------+-------+-------------------------------------------+
| localhost | maria | *8061C323A725701555411A7E18421F077A840CD7 |
+-----------+-------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> grant select, insert, update on toros to equipo@'192.68.200.%' identified
by 'equipo';
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, password from mysql.user where user='equipo';
+--------------+--------+-------------------------------------------+
| host | user | password |
+--------------+--------+-------------------------------------------+
| 192.68.200.% | equipo | *497ED77532F14EF4EA27305174C365D6356F8C9B |
+--------------+--------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> grant select, insert, update, DELETE on FIESTAS_CORRIDAS to TODOS@'192.68
.200.%' identified by 'TODOS';

mysql> select host, user, password from mysql.user where user='todos';
Empty set (0.03 sec)

mysql> DESCRIBE user;
mysql> use mysql;
Database changed
mysql> DESCRIBE user;
+-----------------------+-----------------------------------+------+-----+------
---+-------+
| Field | Type | Null | Key | Defau
lt | Extra |
+-----------------------+-----------------------------------+------+-----+------
---+-------+
| Host | char(60) | NO | PRI |
| |
| User | char(16) | NO | PRI |
| |
| Password | char(41) | NO | |
| |
| Select_priv | enum('N','Y') | NO | | N
| |
| Insert_priv | enum('N','Y') | NO | | N
| |
| Update_priv | enum('N','Y') | NO | | N
| |
| Delete_priv | enum('N','Y') | NO | | N
| |
| Create_priv | enum('N','Y') | NO | | N
| |
| Drop_priv | enum('N','Y') | NO | | N
| |
| Reload_priv | enum('N','Y') | NO | | N
| |
| Shutdown_priv | enum('N','Y') | NO | | N
| |
| Process_priv | enum('N','Y') | NO | | N
| |
| File_priv | enum('N','Y') | NO | | N
| |
| Grant_priv | enum('N','Y') | NO | | N
| |
| References_priv | enum('N','Y') | NO | | N
| |
| Index_priv | enum('N','Y') | NO | | N
| |
| Alter_priv | enum('N','Y') | NO | | N
| |
| Show_db_priv | enum('N','Y') | NO | | N
| |
| Super_priv | enum('N','Y') | NO | | N
| |
| Create_tmp_table_priv | enum('N','Y') | NO | | N
| |
| Lock_tables_priv | enum('N','Y') | NO | | N
| |
| Execute_priv | enum('N','Y') | NO | | N
| |
| Repl_slave_priv | enum('N','Y') | NO | | N
| |
| Repl_client_priv | enum('N','Y') | NO | | N
| |
| Create_view_priv | enum('N','Y') | NO | | N
| |
| Show_view_priv | enum('N','Y') | NO | | N
| |
| Create_routine_priv | enum('N','Y') | NO | | N
| |
| Alter_routine_priv | enum('N','Y') | NO | | N
| |
| Create_user_priv | enum('N','Y') | NO | | N
| |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | |
| |
| ssl_cipher | blob | NO | | NULL
| |
| x509_issuer | blob | NO | | NULL
| |
| x509_subject | blob | NO | | NULL
| |
| max_questions | int(11) unsigned | NO | | 0
| |
| max_updates | int(11) unsigned | NO | | 0
| |
| max_connections | int(11) unsigned | NO | | 0
| |
| max_user_connections | int(11) unsigned | NO | | 0
| |
+-----------------------+-----------------------------------+------+-----+------
---+-------+
37 rows in set (0.09 sec)

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

1.
fiesta.DNITorero
Select toro.NombreTorero  fiesta.premios
from fiesta
inner join toro ON toro.idtorero = fiesta.idtorero
2.
fiesta.idTorero
Select gANADERIA.nOMBRE_gANADERIA,cORRIDAS.FERIA, COUNT(fiesta.idtoro) 
from ganaderia,TORO,fiesta,corridas
WHERE  ganaderia.CodigoGanaderia = TORO.codigoGanaderia and
Toro.idtoro = fiesta.idtoro and
fiesta.idcorrida = CORRIDAS.idcorrida Group by ganaderia nombre_ganaderia, corrida.feria;

toro gris

consulta 5
select toro.color, COUNT(toro.color) FROM corridas JOIN fiesta ON ( fiesta.idcorrida=corridas.idcorrida )
join toro on (toro.idtoro= fiesta.idtoro) WHERE toro.color='gris'

consulta4
select COUNT(Apoderados.NombreApoderado) CANTIDAD , Apoderados.NombreApoderado
From apoderados
join toreros on (toreros.idapoderado=Apoderados.idapoderado)
join fiesta ON (toreros.idtorero=fiesta.idtorero) JOIN CORRIDAS ON (fiesta.idcorrida=corridas.idcorrida)
WHERE CORRIDAS.FERIA="Corrida de inicio de otoño" GROUP BY Apoderados.NombreApoderado,
fiesta.dnicorrida ORDER BY CANTIDAD DESC LIMIT 1

triggers

TRIGGER # 1

Create trigger EliminaToro AFTER INSERT on Corrrida
for each row
BEGIN

UPDATE Toro SET disponible = 'No'
WHERE Toro.idToro

consulta 1.¿Que toreros salieron por la puerta grande?
SELECT torero.Nombretorero, fiesta.premios
FROM torero INNER JOIN fiesta ON Torero.idtorero = fiesta.idtorero
WHERE fiesta.premios Like "puerta grandede";

consulta 2.            


consulta3.
select count(nombre)nombre from ganaderia natural join toros natural join fiesta natural join corridas
order by total desc
limit 1
max (ffiesta-anio_nacimiento)
where fiesta like%bacelo%

Dumping data for table `apoderado`


LOCK TABLES `apoderado` WRITE;
/*!40000 ALTER TABLE `apoderado` DISABLE KEYS */;
INSERT INTO `apoderado` VALUES ('BARV650909','ROPJ891002','VICENTE BARRERA','TORRE SUR 41071','955569246'),('VENJ761021','RURJ910131','JOSE CARLOS VENEGAS','PONTEVEDRA PLAZA DE ESPA¥A 36002','955562138');
/*!40000 ALTER TABLE `apoderado` ENABLE KEYS */;
UNLOCK TABLES;

Dumping data for table `corrida`


LOCK TABLES `corrida` WRITE;
/*!40000 ALTER TABLE `corrida` DISABLE KEYS */;
INSERT INTO `corrida` VALUES (14,'barcelona','tradicional fiesta de barcelo','2010-07-25'),(15,'pinos puente','festival de granada','2010-08-03'),(16,'barcelona','corrida de inicio de oto¤o','2010-09-21');
/*!40000 ALTER TABLE `corrida` ENABLE KEYS */;
UNLOCK TABLES;


Dumping data for table `toreros`


LOCK TABLES `toreros` WRITE;
/*!40000 ALTER TABLE `toreros` DISABLE KEYS */;
INSERT INTO `toreros` VALUES ('LODE690124',3,'BARV650909','EULALIO LOPEZ DIAZ','EL ZOTOLUCO',''),('PERJ781212',2,'BARV650909','JOSE MIGUEL PEREZ','JOSELILLO',''),('ROPJ891002',1,'BARV650909','JOSE ANTONIO RODRIGUEZ PEREZ','JOSE ANTONIO CAMPUZANO',''),('ROSM881122',2,'VENJ761021','MANUEL RODRIGUEZ SANCHEZ','MANOLETE',''),('RURJ910130',1,'VENJ761021','JUAN ANTONIO RUIZ ROMAN','ESPARTACO','');
/*!40000 ALTER TABLE `toreros` ENABLE KEYS */;
UNLOCK TABLES;


Dumping data for table `plazatoros`


LOCK TABLES `plaza_de_toros` WRITE;
/*!40000 ALTER TABLE `plaza_de_toros` DISABLE KEYS */;
INSERT INTO `plaza_de_toros` VALUES ('PLAZA DE TOROS DE PINOS PUENTE','GRANADA,ESPA¥A','SAN JERONIMO 27,GRANADA',3000),('PLAZA MONUMENTAL DE BARCELONA','BARCELONA,ESPA¥A','GRAN VIA CORTS CATALANES,749',19582);
/*!40000 ALTER TABLE `plaza_de_toros` ENABLE KEYS */;
UNLOCK TABLES;




Table structure for table `fiesta`


DROP TABLE IF EXISTS `fiesta`;
CREATE TABLE `fiesta` (
`TOREROS` char(50) default NULL,
`TOROS` char(30) default NULL,
`PREMIOS` char(50) default NULL,
`INFORMACION_ADICIONAL` char(50) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Dumping data for table `fiesta`


LOCK TABLES `fiesta` WRITE;
/*!40000 ALTER TABLE `fiesta` DISABLE KEYS */;
INSERT INTO `fiesta` VALUES ('EL ZOTOLUCO','PAJARITO','1 OREJA',''),('EL ZOTOLUCO','EXPO','2 OREJAS, RABO,PUERTA GRANDE',''),('ESPARTACO','TESTA','NUNGUNO',''),('ESPARTACO','MALTRATA','1 OREJA',''),('CAMPUZANO','LOCO','1 OREJA',''),('CAMPUZANO','NEGRO','NUNGUNO',''),('CAMPUZANO','BRAVO','1 OREJA',''),('CAMPUZANO','TARADO','NUNGUNO',''),('JOSELILLO','TORIBIO','2 OREJAS',''),('JOSELILLO','YMBRO','1 OREJA','RECIBE ALTERNATIVA POR PARTE DE CAMPUZANO'),('ESPARTACO','LUZ','NUNGUNO',''),('ESPARTACO','CHOCOLATE','2 OREJAS, RABO,PUERTA GRANDE','RECIBE ALTERNATIVA POR PARTE DEL CAMPUZANO'),('MANOLETE','TIESO','1 OREJA',''),('MANOLETE','SEVILLA','NINGUNO',''),('JOSELILLO','SOLITARIO','1 OREJA',''),('JOSELILLO','VENTORRILLO','NINGUNO',''),('EL ZOTOLUCO','MINOTAURO','NINGUNO',''),('EL ZOTOLUCO','PINTO','1 OREJA',''),('EL ZOTOLUCO','TAJO','2 OREJAS, RABO','');
/*!40000 ALTER TABLE `fiesta` ENABLE KEYS */;
UNLOCK TABLES;

tarea de insert

Dumping data for table `ganaderia`
--

LOCK TABLES `ganaderia` WRITE;
/*!40000 ALTER TABLE `ganaderia` DISABLE KEYS */;
INSERT INTO `ganaderia` VALUES (160,'Sevilla','1997-05-28'),(720,'Barcelona','2002-03-13'),(1001,'Granada','2000-01-01');
/*!40000 ALTER TABLE `ganaderia` ENABLE KEYS */;
UNLOCK TABLES;

Dumping data for table `toros`
--

LOCK TABLES `toros` WRITE;
/*!40000 ALTER TABLE `toros` DISABLE KEYS */;
INSERT INTO `toro` VALUES (100,'2007-03-06',1,'negro','pajarito',1001),(101,'2008-12-08',2,'cafe','bravo',1001),(102,'2008-10-23',3,'gris','luz',1001),(103,'2007-05-17',4,'negro','toribio',1001),(104,'2008-07-21',5,'negro','negro',1001),(160,'2008-04-16',1,'negro','solitario',160),(161,'2010-01-02',2,'gris','loco',160),(162,'2007-09-19',3,'negro','expo',160),(163,'2008-03-02',4,'gris','maltrata',160),(164,'2009-12-15',5,'cafe','sevilla',160),(165,'2008-10-03',6,'negro','tarado',160),(166,'2007-08-28',7,'cafe','pinto',160),(720,'2008-09-06',1,'gris','tieso',720),(721,'2009-05-05',2,'gris','testa',720),(722,'2008-07-29',3,'negro','minotauro',720),(723,'2009-08-20',4,'cafe','chocolate',720),(724,'2007-10-29',5,'negro','ventorillo',720),(725,'2008-02-19',6,'negro','ymbro',720),(726,'2009-01-20',7,'negro','tajo',720);
/*!40000 ALTER TABLE `toro` ENABLE KEYS */;
UNLOCK TABLES;

CODIGOS DE LAS TABLAS

 CREATE TABLE Apoderado (
  idApoderado TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  torero_idtorero TINYINT UNSIGNED NOT NULL,
  torero_corridas_idcorridas INTEGER UNSIGNED NOT NULL,
  nombre_apoderado VARCHAR(225) NULL,
  direccion_apoderado VARCHAR(225) NULL,
  telefono TINYINT UNSIGNED NULL,
  PRIMARY KEY(idApoderado, torero_idtorero, torero_corridas_idcorridas),
  INDEX Apoderado_FKIndex1(torero_idtorero, torero_corridas_idcorridas)
);

CREATE TABLE corridas (
  idcorridas INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  num-orden TINYINT UNSIGNED NULL,
  feria VARCHAR NULL,
  año-corrida DATE NULL,
  PRIMARY KEY(idcorridas)
);

CREATE TABLE ganaderia (
  idganaderia INT NOT NULL AUTO_INCREMENT,
  torero_corridas_idcorridas INTEGER UNSIGNED NOT NULL,
  torero_idtorero TINYINT UNSIGNED NOT NULL,
  codigo TINYINT UNSIGNED NULL,
  nombre VARCHAR(225) NULL,
  antiguedad VARCHAR(225) NULL,
  PRIMARY KEY(idganaderia, torero_corridas_idcorridas, torero_idtorero),
  INDEX ganaderia_FKIndex1(torero_idtorero, torero_corridas_idcorridas)
);

CREATE TABLE plaza (
  idplaza CHAR NOT NULL AUTO_INCREMENT,
  corridas_idcorridas INTEGER UNSIGNED NOT NULL,
  nombre_plaza TEXT NULL,
  localidad VARCHAR(25) NULL,
  direccion VARCHAR(225) NULL,
  aforo TINYINT UNSIGNED NULL,
  PRIMARY KEY(idplaza),
  INDEX plaza_FKIndex1(corridas_idcorridas)
);

CREATE TABLE premios (
  idpremios TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  torero_corridas_idcorridas INTEGER UNSIGNED NOT NULL,
  torero_idtorero TINYINT UNSIGNED NOT NULL,
  orejas INT NULL,
  rabos INT NULL,
  puerta-grande BOOL NULL,
  PRIMARY KEY(idpremios, torero_corridas_idcorridas, torero_idtorero),
  INDEX premios_FKIndex1(torero_idtorero, torero_corridas_idcorridas)
);

CREATE TABLE torero (
  idtorero TINYINT UNSIGNED NOT NULL,
  corridas_idcorridas INTEGER UNSIGNED NOT NULL,
  nombre_torero VARCHAR(225) NULL AUTO_INCREMENT,
  apodo_torero VARCHAR(225) NULL,
  fecha_inicio DATE NULL,
  PRIMARY KEY(idtorero, corridas_idcorridas),
  INDEX torero_FKIndex1(corridas_idcorridas)
);

CREATE TABLE toros (
  idtoros CHAR NOT NULL AUTO_INCREMENT,
  corridas_idcorridas INTEGER UNSIGNED NOT NULL,
  nacimiento DATE NULL,
  nombre_toro TEXT NULL,
  color_toro TEXT NULL,
  orden_toreo TINYINT UNSIGNED NULL,
  PRIMARY KEY(idtoros, corridas_idcorridas),
  INDEX toros_FKIndex1(corridas_idcorridas)
);

Los integrantes del equipo son:

1. Maria de los Angeles Baron Hoil.
2. Febe Esther Avila May.
3. Ademar Cab Ucan.