Taller Unidad N° 2
ANALISIS
CONSULTAS EN XLS
consulta3.xls (90)
consulta4.xls (46)
consulta5.xls (7)
consulta6.xls (82)
consulta7.xls (36)
consulta9.xls (16)
consulta11.xls (7)
consulta12.xls (11)
consulta4.xls (46)
consulta5.xls (7)
consulta6.xls (82)
consulta7.xls (36)
consulta9.xls (16)
consulta11.xls (7)
consulta12.xls (11)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| libreria |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| webauth |
+--------------------+
8 rows in set (0.01 sec)
mysql> use libreria;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| libreria |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| webauth |
+--------------------+
8 rows in set (0.00 sec)
mysql> show tables;
+--------------------+
| Tables_in_libreria |
+--------------------+
| asignatura |
| autor |
| editorial |
| liautedi |
| libro |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from asignatura;
+-----------+---------------------+
| codigomat | nombre |
+-----------+---------------------+
| M01 | Calculo |
| M02 | Matematicas |
| M03 | Estructura de Datos |
| M04 | Ingles |
| M05 | Sistemas de Inf. |
| M06 | Contabilidad |
| M07 | Redes |
| M08 | Diagramacion |
| M09 | Bases de Datos |
+-----------+---------------------+
9 rows in set (0.00 sec)
mysql> select * from autor;
+----------+----------------------+
| codautor | nombre |
+----------+----------------------+
| A01 | Luis Joyanes |
| A02 | Jorge Vasquez Posada |
| A03 | Jhon Soars |
| A04 | Riaz Khadem |
| A05 | Robert Lorber |
| A06 | Mario Dream |
+----------+----------------------+
6 rows in set (0.01 sec)
mysql> select * from editorial;
+---------+--------------+
| codedit | nombre |
+---------+--------------+
| E01 | Oveja Negra |
| E02 | Norma |
| E03 | Mc Graw Hill |
+---------+--------------+
3 rows in set (0.00 sec)
mysql> select * from liautedi;
+---------+----------+---------+
| idlibro | codautor | codedit |
+---------+----------+---------+
| L02 | A01 | E01 |
| L02 | A05 | E03 |
| L06 | A02 | E02 |
| L07 | A05 | E03 |
| L04 | A04 | E01 |
| L04 | A04 | E02 |
| L04 | A04 | E03 |
+---------+----------+---------+
7 rows in set (0.00 sec)
mysql> select * from libro;
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L01 | Calculo II | 120 | 55000 | M01 |
| L02 | BD II | 150 | 65000 | M09 |
| L03 | Estructara de Datos | 180 | 85000 | M03 |
| L04 | Ingles | 280 | 105000 | M04 |
| L05 | Admon en una Pagina | 70 | 7500 | M05 |
| L06 | Contabilidad I | 170 | 27500 | M06 |
| L07 | Redes | 370 | 32500 | M07 |
| L08 | Diagramacion | 85 | 45000 | M08 |
+---------+---------------------+-----------+--------+-----------+
8 rows in set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,autor.codautor,autor.nombre from libro,liautedi,autor where libro.idlibro=liautedi.idlibro and liautedi.codautor=autor.codautor and autor.nombre='riaz khadem';
+---------+-------------+----------+-------------+
| idlibro | descripcion | codautor | nombre |
+---------+-------------+----------+-------------+
| L04 | Ingles | A04 | Riaz Khadem |
| L04 | Ingles | A04 | Riaz Khadem |
| L04 | Ingles | A04 | Riaz Khadem |
+---------+-------------+----------+-------------+
3 rows in set (0.00 sec)
mysql> select editorial.codedit,editorial.nombre,autor.codautor,autor.nombre from editorial,liautedi,autor where editorial.codedit=liautedi.codedit and liautedi.codautor=autor.codautor and autor.nombre='robert lordert';
Empty set (0.00 sec)
mysql> select editorial.codedit,editorial.nombre,autor.codautor,autor.nombre from editorial,liautedi,autor where editorial.codedit=liautedi.codedit and liautedi.codautor=autor.codautor and autor.nombre='Robert Lordert';
Empty set (0.00 sec)
mysql> select editorial.codedit,editorial.nombre,autor.codautor,autor.nombre from editorial,liautedi,autor where editorial.codedit=liautedi.codedit and liautedi.codautor=autor.codautor and autor.nombre='Robert Lorder';
Empty set (0.00 sec)
mysql> select editorial.codedit,editorial.nombre,autor.codautor,autor.nombre from editorial,liautedi,autor where editorial.codedit=liautedi.codedit and liautedi.codautor=autor.codautor and autor.nombre='Robert Lorber';
+---------+--------------+----------+---------------+
| codedit | nombre | codautor | nombre |
+---------+--------------+----------+---------------+
| E03 | Mc Graw Hill | A05 | Robert Lorber |
| E03 | Mc Graw Hill | A05 | Robert Lorber |
+---------+--------------+----------+---------------+
2 rows in set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,autor.codautor,autor.nombre from libro,liautedi,autor where libro.idlibro=liautedi.idlibro and liautedi.codautor=autor.codautor and autor.nombre='riaz khadem';
+---------+-------------+----------+-------------+
| idlibro | descripcion | codautor | nombre |
+---------+-------------+----------+-------------+
| L04 | Ingles | A04 | Riaz Khadem |
| L04 | Ingles | A04 | Riaz Khadem |
| L04 | Ingles | A04 | Riaz Khadem |
+---------+-------------+----------+-------------+
3 rows in set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,autor.codautor,autor.nombre from libro,liautedi,autor where libro.idlibro=liautedi.idlibro and liautedi.codautor=autor.codautor and autor.nombre='riaz khadem' into outfile 'f:/consulta1.xls';
Query OK, 3 rows affected (0.03 sec)
mysql> select libro.idlibro,libro.descripcion,autor.codautor,autor.nombre from libro,liautedi,autor where libro.idlibro=liautedi.idlibro and liautedi.codautor=autor.codautor and autor.nombre='riaz khadem';
+---------+-------------+----------+-------------+
| idlibro | descripcion | codautor | nombre |
+---------+-------------+----------+-------------+
| L04 | Ingles | A04 | Riaz Khadem |
| L04 | Ingles | A04 | Riaz Khadem |
| L04 | Ingles | A04 | Riaz Khadem |
+---------+-------------+----------+-------------+
3 rows in set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,autor.codautor,autor.nombre from libro,liautedi,autor where libro.idlibro=liautedi.idlibro and liautedi.codautor=autor.codautor and autor.nombre='riaz khadem' into outfile 'f:/consulta2.xls';
Query OK, 3 rows affected (0.02 sec)
mysql> select precio,descripcion from libro where descripcion='redes' and 'bdII';
Empty set, 1 warning (0.00 sec)
mysql> select precio,descripcion from libro where descripcion='redes' and descripcion='bdII';
Empty set (0.00 sec)
mysql> select precio,descripcion from libro where descripcion='REDES' or descripcion='BDII';
+--------+-------------+
| precio | descripcion |
+--------+-------------+
| 32500 | Redes |
+--------+-------------+
1 row in set (0.00 sec)
mysql> select precio,descripcion from libro where descripcion='REDES' or descripcion='BDII' into outfile 'f:/consulta3.xls';
Query OK, 1 row affected (0.00 sec)
mysql> select precio,descripcion from libro where descripcion='REDES' and descripcion='BDII';
Empty set (0.00 sec)
mysql> select precio,descripcion from libro where descripcion='REDES' and descripcion='BDII';
Empty set (0.00 sec)
mysql> select precio,descripcion from libro where descripcion='REDES' and descripcion='BDII';
Empty set (0.00 sec)
mysql> select precio,descripcion from libro where descripcion='REDES' or descripcion='BDII';
+--------+-------------+
| precio | descripcion |
+--------+-------------+
| 32500 | Redes |
+--------+-------------+
1 row in set (0.00 sec)
mysql> select precio,descripcion from libro where descripcion='REDES' or descripcion='BD II';
+--------+-------------+
| precio | descripcion |
+--------+-------------+
| 65000 | BD II |
| 32500 | Redes |
+--------+-------------+
2 rows in set (0.00 sec)
mysql> select precio,descripcion from libro where descripcion='REDES' and descripcion='BD II';
Empty set (0.00 sec)
mysql> select precio,descripcion from libro where descripcion='REDES' or descripcion='BD II';
+--------+-------------+
| precio | descripcion |
+--------+-------------+
| 65000 | BD II |
| 32500 | Redes |
+--------+-------------+
2 rows in set (0.00 sec)
mysql> select precio,descripcion from libro where descripcion='REDES' or descripcion='BD II'into outfile 'f:/consulta3.xls';
Query OK, 2 rows affected (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.nombre,asignatura.codigomat,asignatura.nombre from libro,asignatura,editorial,liautedi where libro.idlibro=liautedi.idlibro and asignatura.codigomat=libro.codigomat and editorial.codedit=liautedi.codedit and asignatura.nombre='contabilidad';
+---------+----------------+---------+--------+-----------+--------------+
| idlibro | descripcion | codedit | nombre | codigomat | nombre |
+---------+----------------+---------+--------+-----------+--------------+
| L06 | Contabilidad I | E02 | Norma | M06 | Contabilidad |
+---------+----------------+---------+--------+-----------+--------------+
1 row in set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.nombre,asignatura.codigomat,asignatura.nombre from libro,asignatura,editorial,liautedi where libro.idlibro=liautedi.idlibro and asignatura.codigomat=libro.codigomat and editorial.codedit=liautedi.codedit and asignatura.nombre='contabilidad' into outfile 'f:/consulta4.xls';
Query OK, 1 row affected (0.00 sec)
mysql> select sum(precio) from libro where nropagina<180;
+-------------+
| sum(precio) |
+-------------+
| 200000 |
+-------------+
1 row in set (0.02 sec)
mysql> select sum(precio) from libro where nropagina<180;
+-------------+
| sum(precio) |
+-------------+
| 200000 |
+-------------+
1 row in set (0.00 sec)
mysql> select sum(precio) from libro where nropagina<180 into outfile 'f:/consulta5.xls';
Query OK, 1 row affected (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.nombre from libro,editorial,liautedi where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit and editorial.nombre='Mac Graw Hill';
Empty set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.nombre from libro,editorial,liautedi where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit and editorial.nombre='Oveja Negra';
+---------+-------------+---------+-------------+
| idlibro | descripcion | codedit | nombre |
+---------+-------------+---------+-------------+
| L02 | BD II | E01 | Oveja Negra |
| L04 | Ingles | E01 | Oveja Negra |
+---------+-------------+---------+-------------+
2 rows in set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.nombre from libro,editorial,liautedi where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit and editorial.nombre='Norma';
+---------+----------------+---------+--------+
| idlibro | descripcion | codedit | nombre |
+---------+----------------+---------+--------+
| L06 | Contabilidad I | E02 | Norma |
| L04 | Ingles | E02 | Norma |
+---------+----------------+---------+--------+
2 rows in set (0.00 sec)
mysql> select * from editorial;
+---------+--------------+
| codedit | nombre |
+---------+--------------+
| E01 | Oveja Negra |
| E02 | Norma |
| E03 | Mc Graw Hill |
+---------+--------------+
3 rows in set (0.00 sec)
mysql> describe editorial;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| codedit | char(3) | NO | PRI | NULL | |
| nombre | char(15) | NO | | NULL | |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.nombre from libro,editorial,liautedi where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit and editorial.nombre='Mac Graw Hill';
Empty set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.nombre from libro,editorial,liautedi where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit and editorial.nombre='Mac Graw Hill' into outfile 'f:/consulta6.xls';
Query OK, 0 rows affected (0.03 sec)
mysql> select * from libro where descripcion like '%e' or descripcion like 'a%';
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L05 | Admon en una Pagina | 70 | 7500 | M05 |
+---------+---------------------+-----------+--------+-----------+
1 row in set (0.00 sec)
mysql> select * from libro where descripcion like '%e' and descripcion like 'a%';
Empty set (0.00 sec)
mysql> select * from libro where descripcion like '%e' or descripcion like 'a%';
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L05 | Admon en una Pagina | 70 | 7500 | M05 |
+---------+---------------------+-----------+--------+-----------+
1 row in set (0.00 sec)
mysql> select * from libro where descripcion like '%e' or descripcion like 'a%'into outfile 'f:/consulta7.xls';
Query OK, 1 row affected (0.00 sec)
mysql> describe asignatura;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| codigomat | char(3) | NO | PRI | NULL | |
| nombre | char(20) | NO | | NULL | |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> select * from asignatura;
+-----------+---------------------+
| codigomat | nombre |
+-----------+---------------------+
| M01 | Calculo |
| M02 | Matematicas |
| M03 | Estructura de Datos |
| M04 | Ingles |
| M05 | Sistemas de Inf. |
| M06 | Contabilidad |
| M07 | Redes |
| M08 | Diagramacion |
| M09 | Bases de Datos |
+-----------+---------------------+
9 rows in set (0.00 sec)
mysql> update asignatura set nombre='calculo' where nombre='calculo diferencial';
Query OK, 0 rows affected (0.05 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select * from asignatura;
+-----------+---------------------+
| codigomat | nombre |
+-----------+---------------------+
| M01 | Calculo |
| M02 | Matematicas |
| M03 | Estructura de Datos |
| M04 | Ingles |
| M05 | Sistemas de Inf. |
| M06 | Contabilidad |
| M07 | Redes |
| M08 | Diagramacion |
| M09 | Bases de Datos |
+-----------+---------------------+
9 rows in set (0.00 sec)
mysql> describe asignatura;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| codigomat | char(3) | NO | PRI | NULL | |
| nombre | char(20) | NO | | NULL | |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> update asignatura set nombre='calculo' where nombre='calculo diferencial';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> describe asignatura;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| codigomat | char(3) | NO | PRI | NULL | |
| nombre | char(20) | NO | | NULL | |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> select * from asignatura;
+-----------+---------------------+
| codigomat | nombre |
+-----------+---------------------+
| M01 | Calculo |
| M02 | Matematicas |
| M03 | Estructura de Datos |
| M04 | Ingles |
| M05 | Sistemas de Inf. |
| M06 | Contabilidad |
| M07 | Redes |
| M08 | Diagramacion |
| M09 | Bases de Datos |
+-----------+---------------------+
9 rows in set (0.00 sec)
mysql> update asignatura set nombre='calculo diferencial' where codigomat='M01';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from asignatura;
+-----------+---------------------+
| codigomat | nombre |
+-----------+---------------------+
| M01 | calculo diferencial |
| M02 | Matematicas |
| M03 | Estructura de Datos |
| M04 | Ingles |
| M05 | Sistemas de Inf. |
| M06 | Contabilidad |
| M07 | Redes |
| M08 | Diagramacion |
| M09 | Bases de Datos |
+-----------+---------------------+
9 rows in set (0.00 sec)
mysql> update asignatura set nombre='calculo diferencial' where codigomat='M01';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from asignatura into outfile 'f:/consulta8.xls';
Query OK, 9 rows affected (0.00 sec)
mysql> select * from libro;
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L01 | Calculo II | 120 | 55000 | M01 |
| L02 | BD II | 150 | 65000 | M09 |
| L03 | Estructara de Datos | 180 | 85000 | M03 |
| L04 | Ingles | 280 | 105000 | M04 |
| L05 | Admon en una Pagina | 70 | 7500 | M05 |
| L06 | Contabilidad I | 170 | 27500 | M06 |
| L07 | Redes | 370 | 32500 | M07 |
| L08 | Diagramacion | 85 | 45000 | M08 |
+---------+---------------------+-----------+--------+-----------+
8 rows in set (0.00 sec)
mysql> update libro set descripcion='administracion' where codigomat='L05';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select * from libro;
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L01 | Calculo II | 120 | 55000 | M01 |
| L02 | BD II | 150 | 65000 | M09 |
| L03 | Estructara de Datos | 180 | 85000 | M03 |
| L04 | Ingles | 280 | 105000 | M04 |
| L05 | Admon en una Pagina | 70 | 7500 | M05 |
| L06 | Contabilidad I | 170 | 27500 | M06 |
| L07 | Redes | 370 | 32500 | M07 |
| L08 | Diagramacion | 85 | 45000 | M08 |
+---------+---------------------+-----------+--------+-----------+
8 rows in set (0.00 sec)
mysql> update libro set descripcion='administracion' where idlibro='L05';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from libro;
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L01 | Calculo II | 120 | 55000 | M01 |
| L02 | BD II | 150 | 65000 | M09 |
| L03 | Estructara de Datos | 180 | 85000 | M03 |
| L04 | Ingles | 280 | 105000 | M04 |
| L05 | administracion | 70 | 7500 | M05 |
| L06 | Contabilidad I | 170 | 27500 | M06 |
| L07 | Redes | 370 | 32500 | M07 |
| L08 | Diagramacion | 85 | 45000 | M08 |
+---------+---------------------+-----------+--------+-----------+
8 rows in set (0.00 sec)
mysql> describe libro;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | PRI | NULL | |
| descripcion | char(20) | NO | | NULL | |
| nropagina | int(3) | NO | | NULL | |
| precio | int(10) | NO | | NULL | |
| codigomat | char(3) | NO | MUL | NULL | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> describe libro;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | PRI | NULL | |
| descripcion | char(20) | NO | | NULL | |
| nropagina | int(3) | NO | | NULL | |
| precio | int(10) | NO | | NULL | |
| codigomat | char(3) | NO | MUL | NULL | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select * from libro;
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L01 | Calculo II | 120 | 55000 | M01 |
| L02 | BD II | 150 | 65000 | M09 |
| L03 | Estructara de Datos | 180 | 85000 | M03 |
| L04 | Ingles | 280 | 105000 | M04 |
| L05 | administracion | 70 | 7500 | M05 |
| L06 | Contabilidad I | 170 | 27500 | M06 |
| L07 | Redes | 370 | 32500 | M07 |
| L08 | Diagramacion | 85 | 45000 | M08 |
+---------+---------------------+-----------+--------+-----------+
8 rows in set (0.00 sec)
mysql> select min(nropagina) from libro;
+----------------+
| min(nropagina) |
+----------------+
| 70 |
+----------------+
1 row in set (0.03 sec)
mysql> select min(nropagina) from libro into outfile 'f:/consulta10.xls';
Query OK, 1 row affected (0.01 sec)
mysql> select max(precio) from libro;
+-------------+
| max(precio) |
+-------------+
| 105000 |
+-------------+
1 row in set (0.00 sec)
mysql> select max(precio) from libro into outfile 'f:/consulta11.xls';
Query OK, 1 row affected (0.01 sec)
mysql> select AVG(precio) from libro;
+-------------+
| AVG(precio) |
+-------------+
| 52812.5000 |
+-------------+
1 row in set (0.03 sec)
mysql> select AVG(precio) from libro into outfile 'f:/consulta12.xls';
Query OK, 1 row affected (0.00 sec)
mysql> alter table editorial change nombre descripcion char not null;
Query OK, 3 rows affected, 3 warnings (0.66 sec)
Records: 3 Duplicates: 0 Warnings: 3
mysql> describe editorial;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| codedit | char(3) | NO | PRI | NULL | |
| descripcion | char(1) | NO | | NULL | |
+-------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from editorial;
+---------+-------------+
| codedit | descripcion |
+---------+-------------+
| E01 | O |
| E02 | N |
| E03 | M |
+---------+-------------+
3 rows in set (0.00 sec)
mysql> describe asignatura;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| codigomat | char(3) | NO | PRI | NULL | |
| nombre | char(20) | NO | | NULL | |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from editorial;
+---------+-------------+
| codedit | descripcion |
+---------+-------------+
| E01 | O |
| E02 | N |
| E03 | M |
+---------+-------------+
3 rows in set (0.00 sec)
mysql> show tables;
+--------------------+
| Tables_in_libreria |
+--------------------+
| asignatura |
| autor |
| editorial |
| liautedi |
| libro |
+--------------------+
5 rows in set (0.00 sec)
mysql> describe editorial;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| codedit | char(3) | NO | PRI | NULL | |
| descripcion | char(1) | NO | | NULL | |
+-------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table editorial modify descripcion char(10) not null;
Query OK, 3 rows affected (0.62 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> describe editorial;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| codedit | char(3) | NO | PRI | NULL | |
| descripcion | char(10) | NO | | NULL | |
+-------------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from editorial;
+---------+-------------+
| codedit | descripcion |
+---------+-------------+
| E01 | O |
| E02 | N |
| E03 | M |
+---------+-------------+
3 rows in set (0.00 sec)
mysql> alter table editorial change descripcion nombres char not null;
Query OK, 3 rows affected (2.85 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> describe editorial;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| codedit | char(3) | NO | PRI | NULL | |
| nombres | char(1) | NO | | NULL | |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from editorial;
+---------+---------+
| codedit | nombres |
+---------+---------+
| E01 | O |
| E02 | N |
| E03 | M |
+---------+---------+
3 rows in set (0.00 sec)
mysql> alter table editorial change nombres descripcion char not null;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from editorial;
+---------+-------------+
| codedit | descripcion |
+---------+-------------+
| E01 | O |
| E02 | N |
| E03 | M |
+---------+-------------+
3 rows in set (0.00 sec)
mysql> select * from editorial into outfile 'f:/consulta13.xls';
Query OK, 3 rows affected (0.06 sec)
mysql> describe libro;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | PRI | NULL | |
| descripcion | char(20) | NO | | NULL | |
| nropagina | int(3) | NO | | NULL | |
| precio | int(10) | NO | | NULL | |
| codigomat | char(3) | NO | MUL | NULL | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.02 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.descripcion from libro, editorial,liautedi where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit and editorial.descripcion='Oveja Negra';
Empty set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.descripcion from libro, editorial,liautedi where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit and editorial.descripcion='o';
+---------+-------------+---------+-------------+
| idlibro | descripcion | codedit | descripcion |
+---------+-------------+---------+-------------+
| L02 | BD II | E01 | O |
| L04 | Ingles | E01 | O |
+---------+-------------+---------+-------------+
2 rows in set (0.00 sec)
mysql> describe libro;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| idlibro | char(10) | NO | PRI | NULL | |
| descripcion | char(20) | NO | | NULL | |
| nropagina | int(3) | NO | | NULL | |
| precio | int(10) | NO | | NULL | |
| codigomat | char(3) | NO | MUL | NULL | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select * from editorial;
+---------+-------------+
| codedit | descripcion |
+---------+-------------+
| E01 | O |
| E02 | N |
| E03 | M |
+---------+-------------+
3 rows in set (0.00 sec)
mysql> select * from editorial;
+---------+-------------+
| codedit | descripcion |
+---------+-------------+
| E01 | O |
| E02 | N |
| E03 | M |
+---------+-------------+
3 rows in set (0.00 sec)
mysql> describe asignatura;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| codigomat | char(3) | NO | PRI | NULL | |
| nombre | char(20) | NO | | NULL | |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> select * from asignatura;
+-----------+---------------------+
| codigomat | nombre |
+-----------+---------------------+
| M01 | calculo diferencial |
| M02 | Matematicas |
| M03 | Estructura de Datos |
| M04 | Ingles |
| M05 | Sistemas de Inf. |
| M06 | Contabilidad |
| M07 | Redes |
| M08 | Diagramacion |
| M09 | Bases de Datos |
+-----------+---------------------+
9 rows in set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.descripcion from libro, editorial,liautedi where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit and editorial.descripcion='o';
+---------+-------------+---------+-------------+
| idlibro | descripcion | codedit | descripcion |
+---------+-------------+---------+-------------+
| L02 | BD II | E01 | O |
| L04 | Ingles | E01 | O |
+---------+-------------+---------+-------------+
2 rows in set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.descripcion from libro, editorial,liautedi where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit and editorial.descripcion='o' into outfile 'f:/consulta14';
Query OK, 2 rows affected (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.descripcion from libro,editorial,liautedi where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit;
+---------+----------------+---------+-------------+
| idlibro | descripcion | codedit | descripcion |
+---------+----------------+---------+-------------+
| L02 | BD II | E01 | O |
| L04 | Ingles | E01 | O |
| L06 | Contabilidad I | E02 | N |
| L04 | Ingles | E02 | N |
| L02 | BD II | E03 | M |
| L07 | Redes | E03 | M |
| L04 | Ingles | E03 | M |
+---------+----------------+---------+-------------+
7 rows in set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.descripcion from libro,editorial,liautedi where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit and libro.descripcion='o';
Empty set (0.02 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.descripcion from libro,editorial,liautedi where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit and libro.descripcion='BD II';
+---------+-------------+---------+-------------+
| idlibro | descripcion | codedit | descripcion |
+---------+-------------+---------+-------------+
| L02 | BD II | E01 | O |
| L02 | BD II | E03 | M |
+---------+-------------+---------+-------------+
2 rows in set (0.00 sec)
mysql> select libro.idlibro,libro.descripcion,editorial.codedit,editorial.descripcion from libro,editorial,liautedi where libro.idlibro=liautedi.idlibro and liautedi.codedit=editorial.codedit and libro.descripcion='BD II' into outfile 'f:/consulta15.xls';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from editorial;
+---------+-------------+
| codedit | descripcion |
+---------+-------------+
| E01 | O |
| E02 | N |
| E03 | M |
+---------+-------------+
3 rows in set (0.00 sec)
mysql> select * from libro where idlibro in ('L08','L07','L05');
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L05 | Admon en una Pagina | 70 | 7500 | M05 |
| L07 | Redes | 370 | 32500 | M07 |
| L08 | Diagramacion | 85 | 45000 | M08 |
+---------+---------------------+-----------+--------+-----------+
3 rows in set (0.02 sec)
mysql> select * from libro where idlibro in ('L08','L07','L05') into outfile 'f:/consulta.xls';
Query OK, 3 rows affected (0.02 sec)
mysql> exit