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