Enciclopedia Galáctica

Herramientas de usuario

Herramientas del sitio

Traza:
bases_de_datos:mysql:lenguaje_mysql

Diferencias

Muestra las diferencias entre dos versiones de la página.

Enlace a la vista de comparación

Ambos lados, revisión anteriorRevisión previa
Próxima revisión		Revisión previa
bases_de_datos:mysql:lenguaje_mysql [2016/12/05 17:35] – [Subconsultas correlacionadas] albertobases_de_datos:mysql:lenguaje_mysql [2016/12/07 21:32] (actual) – [Eliminación de vistas] alberto
Línea 4: Línea 4:
 La forma más básica de crear una tabla es de la siguiente manera: La forma más básica de crear una tabla es de la siguiente manera:
 <WRAP box> <WRAP box>
-    CREATE TABLE [IF NOT EXISTS] NombreTabla ( +  CREATE TABLE [IF NOT EXISTS] NombreTabla ( 
-        NombreColumna TipoDato [, +      NombreColumna TipoDato [, 
-        NombreColumna TipoDato] ...);+      NombreColumna TipoDato] ...);
 </WRAP> </WRAP>
  
Línea 478: Línea 478:
 </code> </code>
 ===== Consultas Multitabla ===== ===== Consultas Multitabla =====
 +Hasta ahora se han utilizados sentencias SQL en las que interviene una única tabla. Cuando se necesita obtener datos de dos o más tablas al mismo tiempo, se utilizan consultas multitabla.\\
 +Estas consultas simplemente se realizan indicando en el SELECT las diferentes columnas o expresiones a obtener (como se ha hecho siempre) sin importar la tabla a la que pertenecen. Se especifica en la clausula FROM las tablas a las que pertenecen.\\
 +Si hay dos columnas que se llaman igual, pero pertenecen a tablas diferentes, hay que especificar el nombre de la tabla en cada caso, de la forma //NombreTabla.NombreColumna//. También pueden utilizarse Alias, de forma similar a lo que se vio en el caso de las subconsultas correlacionadas.\\
 +
 +Cuando se realiza una consulta multitabla, se realiza una multiplicación o producto cartesiano, de las tablas. Esto quiere decir que internamente se crea una tabla en la cual cada fila de las tablas especificadas se combina con todas las filas del resto de tablas indicadas.\\
 +Esto no es muy útil, y para quedarnos con la información que nos interesa es necesario filtrar la información a través de lo que se denominan **Composiciones** o **Combinaciones de tablas**, también denominadas **Join**. Este filtro resulta de aplicar una condición en la cláusula WHERE, denominada **Condición o Criterio de composición**.\\
 +
 +Dependiendo de la condición de composición, tendremos:
 +  * **Composición natural**: 
 +    * La condición de selección se establece con el operador de igualdad, entre las columnas que deban coincidir exactamente en tablas diferentes.
 +    * SQL no exige que las columnas de emparejamiento estén relacionadas como clave primaria y clave ajena, aunque suele ser lo habitual.
 +  * **Composición basada en desigualdad**: Consiste en que la condición de selección no sea una igualdad.
 +
 +Es posible realizar composiciones o combinaciones de una tabla consigo misma. Basta con especificar un alias diferente para la misma tabla (como si fuesen tablas diferentes) en la clausula FROM.\\
 +
 +==== Composiciones externas ====
 +Puede darse el caso de que al realizar una combinación multitabla, no se emparejen todas las filas que debieran, por falta de correspondencia. Esto ocurre cuando existen filas en una tabla que no tienen correspondencia con las filas de la otra tabla. \\
 +Si deseamos que aparezcan todos las filas de un determinado campo de una tabla, aunque no tenga correspondencia con las filas de la otra tabla, se realiza lo que se denomina una **Composición o Combinación Externa (Outer Join)**.
 +
 +=== Formato===
 +<WRAP box>
 +    SELECT [ALL/DISTINCT] ExpresionColumna [, ExpresionColumna ...]
 +    FROM NombreTabla [AliasTabla] { LEFT|RIGHT [OUTHER] JOIN NombreTabla [AliasTabla] ...}
 +    ON CondicionComposicion
 +</WRAP>
 +
 +  * **LEFT JOIN**: Se obtienen todas las filas de la tabla de la izquierda, aunque no tengan correspondencia en la tabla de la derecha (los valores serán NULL).
 +  * **RIGHT JOIN**: Se obtienen todas las filas de la tabla de la dereecha, aunque no tengan correspondencia en la tabla de la izquierda (los valores serán NULL).
 +  * **ON**: En el caso de las Combinaciones Externas, se utiliza la cláusula ON, en vez de WHERE, aunque el funcionamiento es exactamente el mismo.
 +
 +
 +==== Ejemplos ====
 +Tabla EMPLEADOS
 +^ emp_no   ^ apellido    ^ oficio     ^ director  ^ fecha_alta  ^ salario ^ Comisión ^ dep_no ^
 +| 7499     | ALONSO      | VENDEDOR   | 7698      | 20/02/81    | 1400.0  | 400.00   | 30     |
 +| 7521     | LOPEZ       | EMPLEADO   | 7782      | 08/05/81    | 1350.0  | NULL     | 10     |
 +| 7654     | MARTIN      | VENDEDOR   | 7698      | 28/09/81    | 1500.0  | 1600.00  | 30     |
 +| 7698     | GARRIDO     | DIRECTOR   | 7839      | 01/05/81    | 3850.0  | NULL     | 30     |
 +| 7782     | MARTINEZ    | DIRECTOR   | 7839      | 09/06/81    | 2450.0  | NULL     | 10     |
 +| 7839     | REY         | PRESIDENTE | NULL      | 17/11/81    | 6000.0  | NULL     | 10     |
 +| 7844     | CALVO       | VENDEDOR   | 7698      | 08/09/81    | 1800.0  | 0.00     | 30     |
 +| 7876     | GIL         | ANALISTA   | 7782      | 06/05/82    | 3350.0  | NULL     | 20     |
 +| 7900     | JIMENEZ     | EMPLEADO   | 7782      | 24/03/82    | 1400.0  | NULL     | 20     |
 +
 +
 +Tabla DEPARTAMENTOS:
 +^ DEP_NO ^ DNOMBRE       ^ LOCALIDAD ^
 +| 10     | Contabilidad  | Barcelona |
 +| 20     | Investigación | Valencia  |
 +| 30     | Ventas        | Madrid    |
 +| 40     | Producción    | Sevilla   |
 +
 +
 +
 +**Ejemplo de Composición o Combinación Natural**
 +<code mysql>
 +SELECT emp_no "Nº empleado",
 +       apellido "Apellido",
 +       dnombre "Departamento",
 +       localidad "Localidad"
 +FROM empleados, departamentos
 +WHERE empleados.dep_no = departamentos.dep_no;
 +</code>
 +
 +Resulta:
 +^Nº empleado^Apellido^Departamento^Localidad^
 +|7499|ALONSO|VENTAS|MADRID|
 +|7521|LOPEZ|CONTABILIDAD|BARCELONA|
 +|7654|MARTIN|VENTAS|MADRID|
 +|7698|GARRIDO|VENTAS|MADRID|
 +|7782|MARTINEZ|CONTABILIDAD|BARCELONA|
 +|7839|REY|CONTABILIDAD|BARCELONA|
 +|7844|CALVO|VENTAS|MADRID|
 +|7876|GIL|INVESTIGACION|VALENCIA|
 +|7900|JIMENEZ|INVESTIGACION|VALENCIA|
 +
 +**Ejemplo de composición basada en la desigualdad**\\
 +Listar los empleados de departamentos con códigos menores que el código de departamento de Barcelona:
 +<code mysql>
 +SELECT e.emp_no "Nº Empleado",
 +       e.apellido
 +FROM empleados e, departamentos d
 +WHERE d.localidad = 'Barcelona'
 +      AND e.dep_no > d.dep_no;
 +</code>
 +
 +Resulta:
 +^Nº Empleado^apellido^
 +|7499|ALONSO|
 +|7654|MARTIN|
 +|7698|GARRIDO|
 +|7844|CALVO|
 +|7876|GIL|
 +|7900|JIMENEZ|
 +
 +
 +**Ejemplo de consulta de una tabla consigo misma**\\
 +Obtener la lista de empleados con los nombres de sus directores.
 +<code mysql>
 +SELECT e1.emp_no "Nº Empleado", 
 +       e1.apellido "Nombre empleado", 
 +       e1.director "Nº Director", 
 +       e2.apellido "Nombre Director"
 +FROM empleados e1, empleados e2
 +WHERE e1.director = e2.emp_no;
 +</code>
 +
 +Obteniendo como resultado:
 +^Nº Empleado^Nombre empleado^Nº Director^Nombre Director^
 +|7499|ALONSO|7698|GARRIDO|
 +|7521|LOPEZ|7782|MARTINEZ|
 +|7654|MARTIN|7698|GARRIDO|
 +|7698|GARRIDO|7839|REY|
 +|7782|MARTINEZ|7839|REY|
 +|7844|CALVO|7698|GARRIDO|
 +|7876|GIL|7782|MARTINEZ|
 +|7900|JIMENEZ|7782|MARTINEZ|
 +
 +
 +**Ejemplo de combinaciones externas**\\
 +Obtener los departamentos con su nombre, localidad y número de empleados trabajando en ellos, incluyendo los que no tienen empleados.
 +<code mysql>
 +SELECT dnombre "Departamento", 
 +       localidad "localidad", 
 +       COUNT(emp_no) "Nº empleados"
 +FROM departamentos d LEFT JOIN empleados e
 +ON d.dep_no = e.dep_no
 +GROUP BY dnombre, localidad;
 +</code>
 +
 +Resulta:
 +^Departamento^localidad^Nº empleados^
 +|CONTABILIDAD|BARCELONA|3|
 +|INVESTIGACION|VALENCIA|2|
 +|PRODUCCION|SEVILLA|0|
 +|VENTAS|MADRID|4|
 +
 +
 +===== Composiciones y subconsultas =====
 +Hay ocasiones que una consulta puede resolverse con una composición o combinación (Join) de tablas, o una subconsulta.\\
 +
 +Si puede solucionarse de ambas formas, siempre será preferible hacerlo con una subconsulta, ya que las consultas multitabla siempre requerirán más tiempo y recursos.\\
 +
 +En general, si no se necesitan visualizar columnas de más de una tabla, de utilizarán subconsultas. En caso contrario, se usará un join.
 +
 +===== Formato completo de consultas =====
 +<WRAP box>
 +    SELECT { *|[ALL/DISTINCT] ExpresionColumna [AliasColumna]
 +                           [, ExpresionColumna [AliasColumna]...] }
 +    FROM NombreTabla [AliasTabla] 
 +      [, NombreTabla [AliasTabla] ...]
 +    [WHERE {CondicionSeleccion|CondicionComposicion}]
 +    [GROUP BY ExpresionColumnaAgrupacion|Posicion]
 +            [,ExpresionColumnaAgrupacion|Posicion...]
 +     [HAVING CondicionSeleccionGrupos]]
 +    [ORDER BY {ExpresionColumna|Posicion}[ASC|DESC]
 +            [,{ExpresionColumna|Posicion}[ASC|DESC]...] ]
 +    [LIMIT [m,]n];
 +</WRAP>
 +
 ===== Consultas dentro de otras instrucciones ===== ===== Consultas dentro de otras instrucciones =====
 +==== Creación de una tabla a partir de una selección de otra tabla ====
 +<WRAP box>
 +  CREATE TABLE [IF NOT EXISTS] NombreTabla
 +   [( DefinicionColumna [, DefinicionColumna ...] )] [IGNORE|REPLACE]
 +  SentenciaSelect
 +</WRAP>
 +
 +Los nombres de las columnas de la nueva tabla son opcionales y en caso de que no se especifiquen, recibirán los valores de la otra tabla, o sus alias correspondientes. Si hay expresiones o funciones en la lista del SELECT, deben indicarse nombre o alias, ya que en caso contrario estos valores no podrán referenciarse.\\
 +
 +Para saber qué acción tomar cuando se produce la repetición de un campo UNIQUE o PRIMARY KEY, podemos indicar IGNORE (no se guarda el nuevo dato y es ignorado)  o REPLACE (el dato reemplaza al anterior). Si no se indica nada y ocurre la coincidencia, se producirá un error.\\
 +
 +LA nueva tabla creada no hereda las constraints, por lo que habría que crearlas en la definición de la misma, o añadirlas posteriormente con ALTER TABLE.\\
 +
 +Al crear la nueva tabla, se insertan las filas correspondientes de la tabla resultado de la sentencia SELECT.
 +
 +==== Actualización de una tabla a partir de una subconsulta ====
 +<WRAP box>
 +  INSERT INTO NombreTabla [( NombreColumna [,NombreColumna ...] )]
 +  SELECT FormatoSelect
 +</WRAP>
 +
 +La lista de columnas es opcional, pero deberá especificarse si las columnas que devuelve la consulta no coinciden en número u orden con las columnas de la tabla destino.\\
 +
 +==== Modificaciones con subconsultas ====
 +En este caso, la condición que deben cumplir las filas que deseamos modificar implica la realización de una subconsulta a otras tablas.\\
 +La forma de hacerlo es incluyendo la subconsulta en la cláusula WHERE.\\
 +
 +Existen algunas limitaciones:
 +  * La tabla destino no puede aparecer en la consulta.
 +  * No se puede incluir la cláusula ORDER BY en la consulta.
 +
 +**Ejemplo**
 +Se desea elevar 500 euros el salario de todos los empleados cuyo departamento no esté en Madrid.
 +<code mysql>
 +UPDATE empleados
 +SET salario = salario + 500
 +WHERE dep_no NOT IN ( SELECT dep_no
 +                      FROM departamentos
 +                      WHERE localidad <> 'Madrid' );
 +</code>
 +
 +
 +==== Eliminaciones con subconsultas ====
 +En ocasiones la condición que deben cumplir las filas que queremos eliminar implica realizar una subconsulta a otras tablas. En estos casos se incluirá la subconsulta en la cláusula WHERE con las mismas restricciones que las modificaciones:
 +  * La tabla destino no puede aparecer en la consulta.
 +  * No se puede incluir la cláusula ORDER BY en la consulta.
 +
 +Se pueden utilizar subconsultas anidadas a varios niveles, pero respetando la siguiente restricción: **La tabla destino no puede aparecer en la cláusula FROM de ninguna de las subconsultas que intervienen en la selección. __Sí se permiten referencias externas__.**
 +
 +**Ejemplo**
 +<code mysql>
 +DELETE FROM departamentos
 +WHERE NOT EXISTS ( SELECT *
 +                   FROM empleados
 +                   WHERE empleados.dep_no = departamentos.dep_no );
 +</code>
 ===== Vistas ===== ===== Vistas =====
 +Una vista es una "tabla virtual" que se almacena en memoria y que se crea a partir de una consulta. Esta "tabla virtual" o "Vista", está "conectada" directamente a las tablas de origen, de forma que se mantiene constantemente actualizada en tiempo real. Podemos operar sobre la vista como si de una tabla corriente se tratase, sirviendo de interfaz con el usuario y los datos de interés.\\
 +
 +Ventajas del uso de vistas:
 +  * Seguridad y confidencialidad: La vista ocultará los datos a los que el usuario no tenga permiso de acceso.
 +  * Comodidad: Sólo muestra los datos relevantes, aunque pertenezcan en realidad a diferentes tablas. Se permite incluso trabajar con agrupaciones de filas como si se tratase de una sola, o composiciones de tablas como si fuera sólo una.
 +  * Sigue funcionando aunque se produzcan cambios en las tablas de origen (nombres de columnas, tablas, etc).
 +
 +==== Creación de vistas ====
 +<WRAP box>
 +    CREATE VIEW NombreVista 
 +      [( DefinicionColumna [,DefinicionColumna ...] )]
 +    AS Consulta;
 +</WRAP>
 +
 +Deben tenerse en cuenta algunas consideraciones cuando se crean vistas:
 +  * Si no empleamos nombres para las columnas y expresiones, sólo podremos referirnos a ellas con un SELECT *
 +  * No se puede usar la cláusula ORDER BY en la creación de una vista.
 +  * es obligatorio definir nombres de columnas o alias, cuando la vista devuelve funciones de agrupamiento (SUM, COUNT, etc), y posteriormente quiere hacerse referencia a ellas.
 +  * Pueden usarse funciones de agrupación para crear vistas. Después también es posible usar funciones de agrupación en las consultas sobre esas mismas vistas, de forma que podemos recuperar "dobles agrupamientos", que no están permitidos por el estándar.
 +Se pueden realizar consultas a las vistas, de la misma manera que se hacía a las tablas.
 +
 +==== Ejemplo ====
 +Creamos una vista para trabajar con los datos de dos tablas agrupados y seleccionando las filas que interesen (todos los departamentos que tengan más de dos empleados):
 +<code mysql>
 +CREATE VIEW resumen_emp_dep
 +  (departamento, 
 +   num_empleados,
 +   suma_salario)
 + AS SELECT dnombre,
 +           COUNT(emp_no),
 +           SUM(salario)
 +    FROM empleados, departamentos
 +    WHERE empleados.dep_no = departamentos.dep_no
 +    GROUP BY empleados.dep_no, dnombre
 +     HAVING COUNT(*) > 2;
 +</code>
 +     
 +Consultamos los datos del número de empleados por departamento:
 +<code mysql>
 +SELECT departamento, num_empleados
 +FROM resumen_emp_dep;
 +</code>
 +
 +Que resulta:
 +^departamento^num_empleados^
 +|CONTABILIDAD|3|
 +|VENTAS|4|
 +
 +==== Eliminación de vistas ====
 +<WRAP box>
 +    DROP VIEW [IF EXISTS] NombreVista
 +     [RESTRICT|CASCADE]
 +</WRAP>
 +
 +RESTRICT y CASCADE están permitidas pero no implementadas en la versión 5 de MySQL.
bases_de_datos/mysql/lenguaje_mysql.1480955701.txt.gz · Última modificación: por alberto
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki