select...select(s) update...select(s) delete...select(s) insert...select(s) update tabla set campo=valor where condicion; update tabla set (subconsulta),(subconsulta), compo=valor where subconsultas; Restaurar la base datos subconsultas Query OK, 0 rows affected (0.05 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show tables; +------------------------+ | Tables_in_subconsultas | +------------------------+ | articulo | | detalle | +------------------------+ 2 rows in set (0.00 sec) mysql> select * from articulo; +--------+------------+----------+---------------+------------+ | codigo | articulo | cantidad | valorunitario | existencia | +--------+------------+----------+---------------+------------+ | 150 | nevera | 25 | 950000 | 0 | | 200 | televisor | 11 | 1200000 | 0 | | 250 | estufa | 30 | 750000 | 0 | | 300 | ventilador | 17 | 110000 | 0 | | 350 | lavadora | 13 | 980000 | 0 | +--------+------------+----------+---------------+------------+ 5 rows in set (0.00 sec) mysql> select * from detalle; +----+------------+------------+----------+------------+-------+--------+ | id | nrofactura | fecha | cantidad | valorventa | total | codigo | +----+------------+------------+----------+------------+-------+--------+ | 1 | 1200 | 2010-01-30 | 3 | 0 | 0 | 150 | | 2 | 1250 | 2010-02-13 | 5 | 0 | 0 | 150 | | 3 | 1250 | 2010-02-13 | 7 | 0 | 0 | 250 | | 4 | 1300 | 2010-03-02 | 1 | 0 | 0 | 350 | | 5 | 1300 | 2010-03-02 | 2 | 0 | 0 | 300 | | 6 | 1400 | 2010-03-11 | 3 | 0 | 0 | 200 | | 7 | 1500 | 2010-03-21 | 5 | 0 | 0 | 250 | +----+------------+------------+----------+------------+-------+--------+ 7 rows in set (0.00 sec) 1. calcular el valorventa de la tabla detalle con un incremento del 23% del valorunitario de la tabla articulo Analisis: a) Campo a reemplazar= Valorventa de la tabla detalle b) Con quien o que lo va a reemplazar= (valorunitario*0.23)+valorunitario de la tabla articulo c) Condiciones= articulo.codigo=detalle.codigo d) Comando Update SENTENCIA MYSQL mysql> update detalle set valorventa = (select valorunitario + (valorunitario * 0.23) from articulo where articulo.codigo = detalle.codigo); Query OK, 7 rows affected (0.19 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> select * from detalle; +----+------------+------------+----------+------------+-------+--------+ | id | nrofactura | fecha | cantidad | valorventa | total | codigo | +----+------------+------------+----------+------------+-------+--------+ | 1 | 1200 | 2010-01-30 | 3 | 1168500 | 0 | 150 | | 2 | 1250 | 2010-02-13 | 5 | 1168500 | 0 | 150 | | 3 | 1250 | 2010-02-13 | 7 | 922500 | 0 | 250 | | 4 | 1300 | 2010-03-02 | 1 | 1205400 | 0 | 350 | | 5 | 1300 | 2010-03-02 | 2 | 135300 | 0 | 300 | | 6 | 1400 | 2010-03-11 | 3 | 1476000 | 0 | 200 | | 7 | 1500 | 2010-03-21 | 5 | 922500 | 0 | 250 | +----+------------+------------+----------+------------+-------+--------+ 7 rows in set (0.00 sec) mysql> select * from articulo; +--------+------------+----------+---------------+------------+ | codigo | articulo | cantidad | valorunitario | existencia | +--------+------------+----------+---------------+------------+ | 150 | nevera | 25 | 950000 | 0 | | 200 | televisor | 11 | 1200000 | 0 | | 250 | estufa | 30 | 750000 | 0 | | 300 | ventilador | 17 | 110000 | 0 | | 350 | lavadora | 13 | 980000 | 0 | +--------+------------+----------+---------------+------------+ 5 rows in set (0.00 sec) 2. calcular el total de la tabla detalle a) Campo a reemplazar= total de la tabla detalle e) Con quien o que lo va a reemplazar= cantidad*valorventa f) Condiciones= ninguna g) Comando Update mysql> update detalle set total = cantidad * valorventa; Query OK, 7 rows affected (0.00 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> select * from detalle; +----+------------+------------+----------+------------+---------+--------+ | id | nrofactura | fecha | cantidad | valorventa | total | codigo | +----+------------+------------+----------+------------+---------+--------+ | 1 | 1200 | 2010-01-30 | 3 | 1168500 | 3505500 | 150 | | 2 | 1250 | 2010-02-13 | 5 | 1168500 | 5842500 | 150 | | 3 | 1250 | 2010-02-13 | 7 | 922500 | 6457500 | 250 | | 4 | 1300 | 2010-03-02 | 1 | 1205400 | 1205400 | 350 | | 5 | 1300 | 2010-03-02 | 2 | 135300 | 270600 | 300 | | 6 | 1400 | 2010-03-11 | 3 | 1476000 | 4428000 | 200 | | 7 | 1500 | 2010-03-21 | 5 | 922500 | 4612500 | 250 | +----+------------+------------+----------+------------+---------+--------+ 7 rows in set (0.00 sec) 3. calcular las existencias según la tabla detalle de cada artículo Análisis a) Campo a reemplazar= existencia de la tabla artículo b) Con quien o que lo va a reemplazar= cantidad-suma(cantidad) de la tabla detalle c) Condiciones= detalle.codigo = articulo.codigo d) Comando Update update articulo set existencia = cantidad - (select sum(cantidad) from detalle where detalle.codigo = articulo.codigo); Query OK, 0 rows affected, 5 warnings (0.17 sec) Rows matched: 5 Changed: 0 Warnings: 5 mysql> select * from articulo; +--------+------------+----------+---------------+------------+ | codigo | articulo | cantidad | valorunitario | existencia | +--------+------------+----------+---------------+------------+ | 150 | nevera | 25 | 950000 | 17 | | 200 | televisor | 11 | 1200000 | 8 | | 250 | estufa | 30 | 750000 | 18 | | 300 | ventilador | 17 | 110000 | 15 | | 350 | lavadora | 13 | 980000 | 12 | +--------+------------+----------+---------------+------------+ 5 rows in set (0.00 sec) mysql> select * from detalle; +----+------------+------------+----------+------------+---------+--------+ | id | nrofactura | fecha | cantidad | valorventa | total | codigo | +----+------------+------------+----------+------------+---------+--------+ | 1 | 1200 | 2010-01-30 | 3 | 1168500 | 3505500 | 150 | | 2 | 1250 | 2010-02-13 | 5 | 1168500 | 5842500 | 150 | | 3 | 1250 | 2010-02-13 | 7 | 922500 | 6457500 | 250 | | 4 | 1300 | 2010-03-02 | 1 | 1205400 | 1205400 | 350 | | 5 | 1300 | 2010-03-02 | 2 | 135300 | 270600 | 300 | | 6 | 1400 | 2010-03-11 | 3 | 1476000 | 4428000 | 200 | | 7 | 1500 | 2010-03-21 | 5 | 922500 | 4612500 | 250 | +----+------------+------------+----------+------------+---------+--------+ 7 rows in set (0.02 sec) 4. eliminar los artículos que hayan vendido entre 2 y 5 artículos en cada venta mysql> delete from articulo where codigo = (select codigo from detalle where cantidad between 2 and 5 and detalle.codigo = articulo.codigo group by articulo.codigo); Query OK, 4 rows affected (0.00 sec) mysql> select * from articulo; +--------+----------+----------+---------------+------------+ | codigo | articulo | cantidad | valorunitario | existencia | +--------+----------+----------+---------------+------------+ | 350 | lavadora | 13 | 980000 | 12 | +--------+----------+----------+---------------+------------+ 1 row in set (0.00 sec) 5. Crear una tabla copia (duplicar la tabla detalle) y llenarla con la información de detalle, solo los registros del mes de febrero mysql> create table copia like detalle; Query OK, 0 rows affected (0.03 sec) mysql> show tables; +------------------------+ | Tables_in_subconsultas | +------------------------+ | articulo | | copia | | detalle | +------------------------+ 3 rows in set (0.02 sec) mysql> describe copia; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | nrofactura | char(10) | NO | | | | | fecha | date | NO | | | | | cantidad | int(11) | NO | | | | | valorventa | int(11) | NO | | | | | total | int(11) | NO | | | | | codigo | char(10) | NO | | | | +------------+----------+------+-----+---------+----------------+ 7 rows in set (0.00 sec) mysql> select * from detalle; +----+------------+------------+----------+------------+---------+--------+ | id | nrofactura | fecha | cantidad | valorventa | total | codigo | +----+------------+------------+----------+------------+---------+--------+ | 1 | 1200 | 2010-01-30 | 3 | 1168500 | 3505500 | 150 | | 2 | 1250 | 2010-02-13 | 5 | 1168500 | 5842500 | 150 | | 3 | 1250 | 2010-02-13 | 7 | 922500 | 6457500 | 250 | | 4 | 1300 | 2010-03-02 | 1 | 1205400 | 1205400 | 350 | | 5 | 1300 | 2010-03-02 | 2 | 135300 | 270600 | 300 | | 6 | 1400 | 2010-03-11 | 3 | 1476000 | 4428000 | 200 | | 7 | 1500 | 2010-03-21 | 5 | 922500 | 4612500 | 250 | +----+------------+------------+----------+------------+---------+--------+ 7 rows in set (0.00 sec) mysql> select * from copia; Empty set (0.00 sec) mysql> insert into copia select * from detalle where month(fecha)=2; Query OK, 2 rows affected (0.19 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from copia; +----+------------+------------+----------+------------+---------+--------+ | id | nrofactura | fecha | cantidad | valorventa | total | codigo | +----+------------+------------+----------+------------+---------+--------+ | 2 | 1250 | 2010-02-13 | 5 | 1168500 | 5842500 | 150 | | 3 | 1250 | 2010-02-13 | 7 | 922500 | 6457500 | 250 | +----+------------+------------+----------+------------+---------+--------+ 2 rows in set (0.00 sec)