PRACTICA SQL TOREROS

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 | COLOR

| int(11) | NO | PRI | 0 | char(10) | YES |

|

|

| NULL |

|

| aniodenacimiento | char(8) | NO | | ORDEN | NOMBRE

| NULL |

| int(11) | YES | MUL | NULL | | 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

|

4 | en MAYO | 2011

|

|

5 | VIVE MEXIC | 2010

|

| 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

| 1002 | JOS RUBIO

| 12/01/1985

| 1003 | CARLOS AUR | 02/07/1990 | 1004 | ALFREDO

| 01/03/1992

| EL JOSELIT | | EL SOTOLUC | | EL YEYITO | | 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

|

|

2 | PINTO | 1998

|

3 | NEGRO | 1999

|

4 | PINTO | 1998

|

5 | NEGRO | 1999

|

6 | PINTO | 1999

|

5 | CABATISTO |

|

7 | PINTO | 1998

|

5 | JABONERO |

| | | |

5 | EL PAJARIT | 5 | ATREVIDO | 5 | VALEDOR | 5 | NAVEGANTE | 5 | ISLERO

+--------+-------+------------------+-------+------------+ 7 rows in set (0.00 sec)

|

|

mysql> SELECT * FROM TORERO; +------+------------+---------------------------+------------+------+ | dni | nombre

| fechaenquetomoalternativa | APODO

| DNT1 |

+------+------------+---------------------------+------------+------+ | 1001 | jose

| 20/12/1978

| 1002 | JOS RUBIO

| 12/01/1985

| 1003 | CARLOS AUR | 02/07/1990 | 1004 | ALFREDO

| 01/03/1992

| EL JOSELIT | NULL | | ZOTOLUCO | NULL | | EL YEYITO | NULL | | 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

| 1003 | CARLOS AUR | 02/07/1990 | 1002 | JOS RUBIO | 1001 | jose

| 12/01/1985

| 20/12/1978

| EL CUÑADO | NULL | | EL YEYITO | NULL | | ZOTOLUCO | NULL | | 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

|

5 | VIVE MEXIC |

2010 |

|

2 | SAN NICOLA |

2009 |

|

2010 |

+---------------+------------+---------------+ 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 | COLOR

| int(11) | NO | PRI | 0 | char(10) | YES |

|

|

| NULL |

|

| FECHADENACIMIENTO | char(20) | YES |

| NULL |

|

| ORDEN | NOMBRE | PESO

| int(11) | YES | MUL | NULL | | char(10) | YES | | char(10) | YES |

|

| NULL |

| 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 +--------+-------+-------------------+-------+------------+------+ |

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

|

6 | PINTO | 1999

|

5 | CABATISTO | 490 |

|

7 | PINTO | 1998

|

5 | JABONERO | 505 |

|

0 | NULL | NULL

| NULL | NULL

| 510 |

| 505 |

+--------+-------+-------------------+-------+------------+------+ 8 rows in set (0.00 sec)

mysq

| PESO |

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.