practica sql toreros
DESCRIPTION
PRACTICA SQLTRANSCRIPT
UNIVERSIDAD TECNOLÓGICA DE
TECAMACHALCO
TECNOLOGÍAS DE LA INFORMACIÓN
Y COMUNICACIÓN
BASE DE DATOS
INTEGRANTES DE EQUIPO:
Rocío Juárez Ramos
Sandra Salas Hernández
Héctor Campos Alonso
En esta nuevo practica nosotros pondremos a prueba
lo que llevamos viendo de base de datos, ya que en
las primeras practicas nosotros solo estábamos
utilizando las clausulas básicos que eran como hacer
una base de datos, crear una tabla e agregarle
información a las tablas en el cmd y ahora estas
misma practica le agregaremos mas información.
C:\Users\usuario>cd..
C:\Users>cd..
C:\>
C:\>cd xampp
C:\xampp>cd mysql
C:\xampp\mysql>cd bin
C:\xampp\mysql\bin>mysql -u root –p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.41 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| mysql |
| phpmyadmin |
| test |
+--------------------+
mysql> create database toreros;
Query OK, 1 row affected (0.03 sec)
mysql> use toreros
Database changed
mysql> SHOW TABLES;
+--------------------+
| Tables_in_toreross |
+--------------------+
| acturar |
| apoderado |
| corrida |
| ganaderia |
| plaza |
| premio |
| torero |
| toro |
+--------------------+
8 rows in set (0.00 sec)
mysql> INSERT INTO corrida(numero_de_orden,feria,anio_de_celebracion) VALUES
('001','TLAXCALA','2010');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO corrida(numero_de_orden,feria,anio_de_celebra) VALUES ('002','SAN
NICOLAS','2009');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> INSERT INTO corrida(numero_de_orden,feria,anio_de_celebra) VALUES ('003','MAYO
','2010');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO corrida(numero_de_orden,feria,anio_de_celebra) VALUES
('004','MAYO','2011');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO corrida(numero_de_orden,feria,anio_de_celebra) VALUES ('005','MEXICO
VIVE','2010');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> INSERT INTO premio (NUMERO,OREJAS,RABO,PUERTAGRANDE,DNI) VALUES ('3178','
1','0','NO' ,'1001');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO premio (NUMERO,OREJAS,RABO,PUERTAGRANDE,DNI) VALUES ('3145','
4','2','SI' ,'1002');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO premio (NUMERO,OREJAS,RABO,PUERTAGRANDE,DNI) VALUES ('3122','
2','1','SI' ,'0,'1003');
Query OK, 1 row affected (0.00 sec)
'> INSERT INTO premio (NUMERO,OREJAS,RABO,PUERTAGRANDE,DNI) VALUES ('3122','
2','1','SI' ,'0','1003');
Query OK, 1 row affected (0.00 sec)
'> INSERT INTO premio (NUMERO,OREJAS,RABO,PUERTAGRANDE,DNI) VALUES ('3122','
2','1','SI' ,'1003');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO premio (NUMERO,OREJAS,RABO,PUERTAGRANDE,DNI) VALUES ('3122','
2','1','SI' ,'1003');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO premio (NUMERO,OREJAS,RABO,PUERTAGRANDE,DNI) VALUES ('3131','
1','0','NO' ,'1004');
Query OK, 1 row affected (0.00 sec)
mysql> DESCRIBE TORO;
+------------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------+------+-----+---------+-------+
| NUMERO | int(11) | NO | PRI | 0 | |
| COLOR | char(10) | YES | | NULL | |
| aniodenacimiento | char(8) | NO | | NULL | |
| ORDEN | int(11) | YES | MUL | NULL | |
| NOMBRE | char(10) | YES | | NULL | |
+------------------+----------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> INSERT INTO toro (NUMERO,COLOR,ANIODENACIMIENTO,ORDEN,NOMBRE) VALUES ('1'
,'NEGRO','1999' ,'005' ,'EL PAJARITO');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> INSERT INTO toro (NUMERO,COLOR,ANIODENACIMIENTO,ORDEN,NOMBRE) VALUES ('2'
,'PINTO','1998' ,'005' ,'ATREVIDO');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO toro (NUMERO,COLOR,ANIODENACIMIENTO,ORDEN,NOMBRE) VALUES ('3'
,'NEGRO','1999' ,'005' ,'VALEDOR');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO toro (NUMERO,COLOR,ANIODENACIMIENTO,ORDEN,NOMBRE) VALUES ('4'
,'PINTO','1998' ,'005' ,'NAVEGANTE');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO toro (NUMERO,COLOR,ANIODENACIMIENTO,ORDEN,NOMBRE) VALUES ('5'
,'NEGRO','1999' ,'005' ,'ISLERO');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO toro (NUMERO,COLOR,ANIODENACIMIENTO,ORDEN,NOMBRE) VALUES ('6'
,'PINTO','1999' ,'005' ,'CABATISTO');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO toro (NUMERO,COLOR,ANIODENACIMIENTO,ORDEN,NOMBRE) VALUES ('7'
,'PINTO','1998' ,'005' ,' JABONERO');
Query OK, 1 row affected (0.01 sec)
mysql> DESCRIBE ACTURAR;
+---------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| dni | char(10) | NO | PRI | NULL | |
| numerodeorden | int(11) | NO | PRI | NULL | |
+---------------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> INSERT INTO ACTURAR (DNI,NUMERODEORDEN) VALUES ('1002',' 004');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO ACTURAR(DNI,NUMERODEORDEN) VALUES ('1002',' 004');
ERROR 1062 (23000): Duplicate entry '1002-4' for key 'PRIMARY'
mysql> INSERT INTO ACTURAR (DNI,NUMERODEORDEN) VALUES ('1003',' 001');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO ACTURAR (DNI,NUMERODEORDEN) VALUES ('1001',' 001');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO ACTURAR (DNI,NUMERODEORDEN) VALUES ('1001',' 005');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO ACTURAR (DNI,NUMERODEORDEN) VALUES ('1001',' 005');
ERROR 1062 (23000): Duplicate entry '1001-5' for key 'PRIMARY'
mysql> INSERT INTO ACTURAR (DNI,NUMERODEORDEN) VALUES ('1004',' 003');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO ACTURAR (DNI,NUMERODEORDEN) VALUES ('1004',' 002');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM ACTURAR;
+------+---------------+
| dni | numerodeorden |
+------+---------------+
| 1001 | 1 |
| 1001 | 5 |
| 1002 | 4 |
| 1003 | 1 |
| 1004 | 2 |
| 1004 | 3 |
+------+---------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM CORRIDA;
+---------------+------------+---------------+
| numerodeorden | feria | aniodecelebra |
+---------------+------------+---------------+
| 1 | TLAXCALA | 2010 |
| 2 | SAN NICOLA | 2009 |
| 3 | MAYO | 2010 |
| 4 | en MAYO | 2011 |
| 5 | VIVE MEXIC | 2010 |
+---------------+------------+---------------+
5 rows in set (0.01 sec)
mysql> SELECT * FROM GANADERIA;
+--------+--------------+-----------+----------------+
| CODIGO | NOMBRE | LOCALIDAD | FECHA_CREACIOM |
+--------+--------------+-----------+----------------+
| G1 | LA LAGUNA | TLAXCALA | 1907 |
| G2 | SAN MATEO | ZACATECA | 1906 |
| 03 | REYES HUERTA | TLAXCALA | 1904 |
+--------+--------------+-----------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM PLAZA;
+-------+------------+------------+------------+
| aforo | nombre | localidad | direccion |
+-------+------------+------------+------------+
| 5000 | EL RELICAR | PUEBLA | RECINTO FE |
| 41000 | PLAZA DE T | DISTRITO F | MEXICO DF |
| 7000 | PLAZA MONU | APIZACO | TLAXCALA |
+-------+------------+------------+------------+
3 rows in set (0.01 sec)
mysql> SELECT * FROM TORERO;
+------+------------+---------------------------+------------+
| dni | nombre | fechaenquetomoalternativa | APODO |
+------+------------+---------------------------+------------+
| 1001 | jose | 20/12/1978 | EL JOSELIT |
| 1002 | JOS RUBIO | 12/01/1985 | EL SOTOLUC |
| 1003 | CARLOS AUR | 02/07/1990 | EL YEYITO |
| 1004 | ALFREDO | 01/03/1992 | EL CUÑADO |
+------+------------+---------------------------+------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM PREMIO;
+--------+--------+------+--------------+------+
| NUMERO | orejas | rabo | PUERTAGRANDE | dni |
+--------+--------+------+--------------+------+
| 3178 | 1 | 0 | NO | 1001 |
| 3145 | 4 | 2 | SI | 1002 |
| 3122 | 2 | 1 | SI | 1003 |
| 3131 | 1 | 0 | NO | 1004 |
+--------+--------+------+--------------+------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM TORO;
+--------+-------+------------------+-------+------------+
| NUMERO | COLOR | aniodenacimiento | ORDEN | NOMBRE |
+--------+-------+------------------+-------+------------+
| 1 | NEGRO | 1999 | 5 | EL PAJARIT |
| 2 | PINTO | 1998 | 5 | ATREVIDO |
| 3 | NEGRO | 1999 | 5 | VALEDOR |
| 4 | PINTO | 1998 | 5 | NAVEGANTE |
| 5 | NEGRO | 1999 | 5 | ISLERO |
| 6 | PINTO | 1999 | 5 | CABATISTO |
| 7 | PINTO | 1998 | 5 | JABONERO |
+--------+-------+------------------+-------+------------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM TORERO;
+------+------------+---------------------------+------------+------+
| dni | nombre | fechaenquetomoalternativa | APODO | DNT1 |
+------+------------+---------------------------+------------+------+
| 1001 | jose | 20/12/1978 | EL JOSELIT | NULL |
| 1002 | JOS RUBIO | 12/01/1985 | ZOTOLUCO | NULL |
| 1003 | CARLOS AUR | 02/07/1990 | EL YEYITO | NULL |
| 1004 | ALFREDO | 01/03/1992 | EL CUÑADO | NULL |
+------+------------+---------------------------+------------+------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM TORERO ORDER BY NOMBRE;
+------+------------+---------------------------+------------+------+
| dni | nombre | fechaenquetomoalternativa | APODO | DNT1 |
+------+------------+---------------------------+------------+------+
| 1004 | ALFREDO | 01/03/1992 | EL CUÑADO | NULL |
| 1003 | CARLOS AUR | 02/07/1990 | EL YEYITO | NULL |
| 1002 | JOS RUBIO | 12/01/1985 | ZOTOLUCO | NULL |
| 1001 | jose | 20/12/1978 | EL JOSELIT | NULL |
+------+------------+---------------------------+------------+------+
4 rows in set (0.01 sec)
mysql> DESCRIBE CORRIDA;
+---------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| numerodeorden | int(11) | NO | PRI | NULL | |
| feria | char(10) | NO | | NULL | |
| aniodecelebra | char(10) | NO | | NULL | |
+---------------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql>
mysql> DESCRIBE CORRIDA;
+---------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| numerodeorden | int(11) | NO | PRI | NULL | |
| feria | char(10) | NO | | NULL | |
| ANIODECELEBRA | int(11) | YES | | NULL | |
+---------------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> SELECT * FROM CORRIDA ORDER BY ANIODECELEBRA DESC;
+---------------+------------+---------------+
| numerodeorden | feria | ANIODECELEBRA |
+---------------+------------+---------------+
| 4 | en MAYO | 2011 |
| 1 | TLAXCALA | 2010 |
| 3 | MAYO | 2010 |
| 5 | VIVE MEXIC | 2010 |
| 2 | SAN NICOLA | 2009 |
+---------------+------------+---------------+
5 rows in set (0.01 sec)
mysql> ALTER TABLE ACTURAR RENAME PARTICIPAN;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT * FROM WHERE Toreros.DNIT=Premios.DNTI;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'WHERE
Toreros.DNIT=Premios.DNTI' at line 1
mysql> ALTER TABLE TORO
-> ;
Query OK, 0 rows affected (0.01 sec)
mysql> UPDATE TORO SET COLOR='PINTO' WHERE COLOR='NEGRO';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> ALTER TABLE TORO
-> ADD PESO INTEGER;
Query OK, 7 rows affected (0.11 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE TORO
-> MODIFY PESO CHAR (10);
Query OK, 7 rows affected (0.45 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> DESCRIBE TORO;
+-------------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+----------+------+-----+---------+-------+
| NUMERO | int(11) | NO | PRI | 0 | |
| COLOR | char(10) | YES | | NULL | |
| FECHADENACIMIENTO | char(20) | YES | | NULL | |
| ORDEN | int(11) | YES | MUL | NULL | |
| NOMBRE | char(10) | YES | | NULL | |
| PESO | char(10) | YES | | NULL | |
+-------------------+----------+------+-----+---------+-------+
6 rows in set (0.02 sec)
mysql> UPDATE TORO SET PESO='500' WHERE NUMERO='1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE TORO SET PESO='450' WHERE NUMERO='2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE TORO SET PESO='487' WHERE NUMERO='3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE TORO SET PESO='460' WHERE NUMERO='4';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE TORO SET PESO='510' WHERE NUMERO='5';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE TORO SET PESO='490' WHERE NUMERO='6';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE TORO SET PESO='505' WHERE NUMERO='7';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> SELECT * FROM TORO;
+--------+-------+-------------------+-------+------------+------+
| NUMERO | COLOR | FECHADENACIMIENTO | ORDEN | NOMBRE | PESO |
+--------+-------+-------------------+-------+------------+------+
| 1 | PINTO | 1999 | 5 | EL PAJARIT | 500 |
| 2 | PINTO | 1998 | 5 | ATREVIDO | 450 |
| 3 | PINTO | 1999 | 5 | VALEDOR | 487 |
| 4 | PINTO | 1998 | 5 | NAVEGANTE | 460 |
| 5 | PINTO | 1999 | 5 | ISLERO | 510 |
| 6 | PINTO | 1999 | 5 | CABATISTO | 490 |
| 7 | PINTO | 1998 | 5 | JABONERO | 505 |
| 0 | NULL | NULL | NULL | NULL | 505 |
+--------+-------+-------------------+-------+------------+------+
8 rows in set (0.00 sec)
mysq
En conclusión esta práctica al principio fue muy fácil
realizarla, pero después se nos complico un poco
porque teníamos errores de dedo y teníamos que
acordarnos de que clausula era la que debíamos
utilizar para corregir esos errores, pero en si nosotros
creemos que estas practicas nos han servido
demasiado para hacer una base de datos bien.