====== Lenguaje de Consultas SQL ====== Esta sección sirve de guía respecto al lenguaje SQL para consultas DDL y DML. ===== Sentencias DDL ===== El lenguaje de definición de datos es el que permite crear la estructura de la base de datos, y las sentencias que usaremos se indican a continuación: ==== Crear un objeto: CREATE ==== Es la sentencia utilizada para la creación de un objeto (base de datos, tabla, usuario, vista, procedimiento, . . .) en una Base de Datos. Para crear una Base de Datos: CREATE DATABASE [IF NOT EXISTS] La sintaxis para la creación de una tabla es la siguiente: CREATE TABLE [IF NOT EXISTS] ( , , ................................ ) -- Ejemplo CREATE TABLE asignaturas( id INT PRIMARY KEY, nombre VARCHAR(20), departamento VARCHAR(20), id_curso INT, FOREIGN KEY (id_curso) REFERENCES cursos(id) ); ==== Eliminar un objeto: DROP ==== Es la sentencia utilizada para eliminar objetos (tabla, usuario, vista, procedimiento, . . .) en una Base de Datos. La sintaxis para la eliminación de tablas es la siguiente: DROP TABLE [IF EXISTS] Y para eliminar una Base de Datos: DROP DATABASE [IF EXISTS] ==== Modificar un objeto: ALTER ==== Es la sentencia utilizada para modificar objetos (tabla, usuario, vista, procedimiento, . . .) en una Base de Datos. La sintaxis para modificar una tabla es la siguiente: ALTER TABLE [ ADD ] [ CHANGE ] [ DROP COLUMN ] [ ADD CONSTRAINT ] Ejemplos: ALTER TABLE alumnos ADD PRIMARY KEY(id); ALTER TABLE alumnos DROP PRIMARY KEY; ALTER TABLE alumnos ADD FOREIGN KEY (id_curso) REFERENCES cursos(id); ALTER TABLE alumnos DROP COLUMN nombre; ALTER TABLE alumnos MODIFY nombre VARCHAR(30) NOT NULL; === Renombrar un objeto: RENAME=== La usamos para cambiar de nombre a una tabla. RENAME TABLE nombre_anterior TO nombre_nuevo; -- también con ALTER TABLE ALTER TABLE tabla_anterior RENAME nueva_tabla; === Conectar con una Base de Datos === Permite realizar la conexión con una Base de Datos de MySQL. USE ==== Tipos de datos ==== === Cadenas de caracteres === == Tipo CHAR, VARCHAR == Este tipo de datos permite almacenar cadenas de texto fijas (''CHAR'') o variables (''VARCHAR''). El tipo ''CHAR'' permite almacenar cadenas de caracteres de longitud fija entre 1 y 255 caracteres. La longitud de la cadena se debe especificar entre paréntesis en el momento de la declaración (''cadena CHAR(25)''). Por otro lado, el tipo ''VARCHAR'' permite almacenar cadenas de caracteres variables de hasta 4.000 caracteres. La declaración del tipo ''VARCHAR'' es similar a la de un tipo CHAR (''cadena VARCHAR(25)''). La principal y única diferencia entre estos dos tipos, es que el tipo ''CHAR'' declara una cadena fija de la longitud que se especifica mientras que en la declaración de un tipo ''VARCHAR'' lo que se especifica es un tamaño máximo, la cadena sólo ocupará el tamaño necesario para almacenar el dato que contenga (hasta llegar al máximo). En cualquier caso, no es posible almacenar cadenas de mayor tamaño al especificado en su declaración, puesto que el SGBD truncará el valor almacenándose sólo hasta la longitud establecida. == Tipo TEXT == El tipo ''TEXT'' permite almacenar cadenas de caracteres de hasta varios GB de longitud. Sólo se recomienda su uso para almacenar textos realmente grandes, puesto que presenta ciertas restricciones, aunque algunas pueden variar dependiendo del SGBD que se utiliza: * Sólo se puede definir una columna ''TEXT'' por tabla * No se pueden establecer restricciones en columnas de este tipo * No se permite su utilización en ciertas cláusulas === Tipos numéricos === Para la representación de tipos de datos numéricos. Los tipos más utilizados son ''BIT'', ''TINYINT'', ''INT'', ''BIGINT'', ''FLOAT'' y ''DOUBLE'', para la representación de números enteros de menor o mayor tamaño, y para números en coma flotante de menor o mayor precisión, respectivamente. En ocasiones el rango de los valores negativos resultará prescindible (claves numéricas, valores de dinero, cantidades, . . .) por lo que será posible ampliar el rango positivo de un tipo numérico añadiendo la restricción ''UNSIGNED'' tras definir el tipo de éste. id INT UNSIGNED Para el caso de los números de coma flotante conviene tener en cuenta los problemas de precisión ((https://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html)) que existen con estos tipos de datos. === Tipos para fechas === Los tipos más utilizado para almacenar valores de fechas (''DATE'') o fechas con hora (''DATETIME''). Por defecto el formato más utilizado es ''DD/MM/YY'' ó ''DD/MM/YYYY''. También se puede usar el tipo ''TIMESTAMP'' para almacenar una marca de tiempo (fecha y hora). Además, permite el uso de la constante ''CURRENT_TIMESTAMP'' en la definición de la columna al definirle un valor por defecto cuando se crea la tabla. === Tipo booleano === Permite almacenar valores lógicos Verdadero/Falso o Sí/No. ''boolean'' realmente se define la columna como del tipo ''TINYINT(1)'', que simplemente almacena los valores 0 y 1 para indicar los valores lógicos Verdadero y Falso, respectivamente. Así, podremos utilizar los valores ''TRUE'' ó ''FALSE'' o directamente asignar 1 ó 0 para asignar valor. ==== Restricciones ==== Las restricciones se pueden establecer, o no, a las columnas de cada tabla para forzar a que los datos almacenados en ellas cumplan una serie de condiciones, con la finalidad de que la información sea más correcta. Por ejemplo, podemos obligar a que un campo donde almacenamos el DNI de una persona tenga una longitud mínima, o bien un campo donde almacenamos la categoría de un equipo de fútbol, sólo pueda tomar unos determinados valores predefinidos (benjamín, juvenil, cadete, . . .) o bien podemos hacer que un campo no pueda repetirse, por tratarse de un valor único (DNI, NSS, teléfono, email, . . .). Hay que tener en cuenta que, por lo general, las restricciones se definen en línea con la definición del campo (tal y como se muestra en la sintaxis de la sentencia de ''CREATE TABLE'', pero de forma opcional también pueden ser definidas por separado justo debajo de la definición de todos los campos de la tabla. === Clave primaria === Una clave primaria dentro de una tabla, es una columna o conjunto de columnas cuyo valor identifica unívocamente a cada fila. Debe ser única, no nula y es obligatoria. Como máximo podremos definir una clave primaria por tabla y es muy recomendable definirla. Para definir una clave primaria utilizamos la restricción ''PRIMARY KEY''. CREATE TABLE personas( dni VARCHAR(9) PRIMARY KEY, ... ); Y si lo hacemos al final de la definición de las columnas, quedaría así: CREATE TABLE personas( dni VARCHAR(9), nombre VARCHAR(10), PRIMARY KEY (dni) ); Hay que tener en cuenta que a la hora de definir claves primarias compuestas (la componen 2 ó más campos de la tabla), ésta deberá ser definida forzosamente tras la definición de los campos involucrados, siguiendo esta sintaxis CREATE TABLE personas( dni VARCHAR(9), nombre VARCHAR(10), apellidos VARCHAR(20), PRIMARY KEY (nombre, apellidos) ); === Autonumérico === Especialmente útil en el caso de aquellas columnas que se definan como claves primarias de cada tabla, resulta añadir la restricción de campo autonumérico, siempre y cuando ésta sea una columna de tipo entero. De esa manera será el SGBD el encargado de asignarle valor de forma automática, siempre asignando un valor entero de forma secuencial a medida que se van insertando las filas en dicha tabla. La forma de definirlo es añadiendo la restricción ''AUTO_INCREMENT'' en la definición de la columna que se ha definido como clave primaria: id INT PRIMARY KEY AUTO_INCREMENT === Clave ajena === Una clave ajena está formada por una o varias columnas que hacen referencia a una clave primaria de otra o de la misma tabla. Se pueden definir tantas claves ajenas como sea necesario (no hay límite) en cada tabla. El valor de la columna o columnas que son clave ajena será el valor de la clave primaria de la tabla a la que hacen referencia (integridad referencial). Así, a la hora de definir una clave ajena, deberemos indicar con la cláusula ''REFERENCES'' la tabla y el campo clave primaria a la que ésta hace referencia . Las claves ajenas se deben definir después de la definición de los campos de la tabla: CREATE TABLE asignaturas( id INT PRIMARY KEY AUTO_INCREMENT, nombre VARCHAR(20), departamento VARCHAR(20), id_curso INT, FOREIGN KEY (id_curso) REFERENCES cursos (id) ); El ejemplo anterior haría referencia a una definición de tabla como la siguiente: CREATE TABLE cursos( id INT PRIMARY KEY AUTO_INCREMENT, horario VARCHAR(20), . . . ); Si una tabla tiene diferentes claves ajenas, se definen del mismo modo: id_curso INT, id_profesor INT, FOREIGN KEY (id_curso) REFERENCES cursos (id), FOREIGN KEY (id_curso) REFERENCES cursos (id) En caso que la clave ajena haga referencia a una clave primaria compuesta: FOREIGN KEY (id_curso, id_aula) REFERENCES cursos(id_curso, id_aula) En cualquiera de los casos hay que tener en cuenta que habrá que definir primero el campo con el tipo de dato correcto (el mismo que dicho campo en la tabla donde aparece como clave principal) y luego la propia definición de dicho campo como clave ajena. Habrá que tener en cuenta que mientras que un campo definido como clave ajena haga referencia a un campo definido como clave primaria, la columna de la segunda tabla no podrá ser eliminada hasta que no lo haga la columna que le hace referencia (integridad referencial). Para evitar estos problemas (aunque no siempre es un problema) es posible definir la restricción de clave ajena añadiendo la cláusula ''ON DELETE'' o bien ''ON UPDATE'' para el caso de una actualización. De esa manera, cuando se vaya a eliminar o actualizar una fila a cuya clave primaria se haga referencia, podremos indicar a MySQL que operación queremos realizar con las filas que le hacen referencia: * **RESTRICT**: Se rechaza la operación de eliminación/actualización * **CASCADE**: Realiza la operación y se elimina o actualiza en cascada en las filas que hacen referencia * **SET NULL**: Realiza la operación y fija a ''NULL'' el valor en las filas que hacen referencia * **NO ACTION**: Se rechaza la operación de eliminación/actualización, como ocurre con la opción ''RESTRICT'' Si no especifico ningun tipo de acción, se tomará ''CASCADE'' por defecto para ambas operaciones (''UPDATE'' y ''DELETE''). -- No podré eliminar cursos si hay asignaturas referenciandolos: FOREIGN KEY (id_curso) REFERENCES cursos (id) ON DELETE RESTRICT -- Si modifico el valor de la clave primaria de un curso, se actualiza la clave ajena -- Si elimino un curso, se pondrá a NULL el valor de la clave ajena de las asignaturas FOREIGN KEY (id_curso) REFERENCES cursos (id) ON UPDATE CASCADE ON DELETE SET NULL == Consideraciones == Para definir claves ajenas en MySQL habrá que tener en cuenta algunas consideraciones: * Una columna clave ajena nunca podrá ser ''AUTO_INCREMENT'', ya que rompería el propósito de la relación. * La columna deberá ser del mismo tipo (y atributos) que la columna a la que se referencia. * La columna deberá ser un índice. A partir de Mysql 8 y versiones compatibles de MAriaDB, se hace automáticamente. * Si la columna se define como obligatoria (''NOT NULL'') no podrá contener la claúsula (''SET NULL'') para los casos de borrado (''ON DELETE'') o actualización (''ON UPDATE''). === Campos obligatorios === Esta restricción obliga a que se le tenga que dar valor obligatoriamente a una columna. Por tanto, no podrá tener el valor NULL. Se utiliza la palabra reservada ''NOT NULL''. apellidos VARCHAR(250) NOT NULL === Valores por defecto === Se puede definir el valor que una columna tomará por defecto, es decir, si al introducir una fila no se especifica valor para dicha columna. Se utiliza la palabra reservada ''DEFAULT''. fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP nombre VARCHAR(250) DEFAULT 'Sin nombre' === Condiciones === De forma más genérica, podemos forzar a que los valores de determinados campos de la tabla cumplan una ciertas condiciones. En caso contrario no se permitirá la inserción de esa fila en dicha tabla. Lo habitual es definir una columna como de tipo enumeración (''ENUM'' en MySQL) si queremos indicar que solamente una serie de valores (definidos) son válidos: curso ENUM ('0', '1', '2'), horario ENUM ('mañana', 'tarde', 'noche'), === Valores únicos === La restricción ''UNIQUE'' evita valores repetidos en una misma columna. Al contrario que ocurre con la restricción ''PRIMARY KEY'', la restricción de valor único se puede aplicar a varias columnas de una misma tabla y admite el valor ''NULL''. Con respecto a esta última consideración, conviene saber que si una columna se define como ''UNIQUE'', sólo una de sus filas podrá contener el valor ''NULL'' email VARCHAR(100) UNIQUE ==== Creación de scripts en MySQL ==== La forma más habitual de trabajo a la hora de lanzar órdenes en SQL sobre un SGBD relacional como MySQL es crear ficheros por lotes de órdenes SQL, lo que se conoce como //scripts SQL//, donde podemos escribir todas las sentencias SQL que queremos ejecutar una detrás de otra separadas por el carácter '';''. Existe la posibilidad de añadir comentarios al código según la siguiente sintaxis: -- Esto es un comentario y MySQL no lo ejecuta /* Esto también es un comentario y tampoco se ejecuta */ Por ejemplo, para la creación de una nueva Base de Datos y sus tablas podríamos preparar un script SQL como el siguiente: CREATE DATABASE IF NOT EXISTS pagina_web; USE pagina_web; CREATE TABLE IF NOT EXISTS usuarios ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, . . . . . . ); CREATE TABLE IF NOT EXISTS productos ( . . . . . . ); . . . . . . Una vez creado el script podremos lanzar su ejecución sobre MySQL y se ejecutarán todas las sentencias contenidas en él de forma secuencial. Es una forma muy útil de crear scripts para la creación de una Base de Datos y todas sus tablas y restricciones y también para crear scripts de actualización o parcheo de una Base de Datos existentes de forma que se incluyan todas las sentencias SQL que actualicen o arreglen los problemas que actualmente pueda haber (añadir una nueva tabla, eliminar un campo, añadir una nueva restricción, . . .). Para estos casos a veces resulta útil desactivar las claves ajenas, realizar algunas operaciones sobre tablas que puedan tener relaciones con otras y volver a activarlas. De esa manera es posible realizar ciertas operaciones sin que las reglas de validación de la integridad referencial lancen ningún error. -- Desactivar claves ajenas SET FOREIGN_KEY_CHECKS = 0; . . . // Realizar algunos cambios en la estructura y datos de la Base de Datos . . . -- Activar claves ajenas SET FOREIGN_KEY_CHECKS = 1; === Ejemplos de cómo crear un script en MySQL === * Crear el script a partir del modelo relacional {{ youtube>T317i5DXj5s }} \\ * Cómo crear un script en SQL {{ youtube>TKM90c-akXw }} \\ ==== Comprobaciones sobre el script SQL ==== - Utilizar notación [[https://en.wikipedia.org/wiki/Snake_case|snake_case]] para todos los identificadores (nombre de la base de datos, nombres de tablas, nombres de columnas, . . .). Y siempre en minúscula - No utilizar acentos, el caracter ñ ni otros caracteres extraños (|@#...) para nombres de bases de datos, tablas, columnas o cualquier otro elemento - Escribir las palabras reservadas del lenguaje SQL en mayúsculas - Todas las tablas tendrán un campo clave primaria cuyo nombre será id (definir como ''id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT'') - Las claves ajenas indicarán la tabla a la que hacen referencia (en singular) como parte de su nombre. Por ejemplo: ''id_usuario'' si es una clave ajena de una tabla usuarios. Si en una tabla hay dos claves ajenas que apuntan a la misma tabla, añadiremos algo al nombre para distinguirla (''id_usuario_emisor'' e ''id_usuario_receptor'', por ejemplo) - Se recomienda que los nombres de las tablas sean en plural (''users'' mejor que ''user'', ''orders'' mejor que ''order'') - Antes de definir un tipo de dato como numérico, comprobar si realmente voy a operar con él como tal - Cuidado con los campos contraseña. Realmente nunca se guarda tal cual sino como un hash utilizando algún algoritmo, por lo que la longitud real es mayor (la longitud de un hash creado con SHA1 es de 40 caracteres y con SHA2 hasta 128) ===== Sentencias DML ===== Dentro de SQL, lenguaje DML (Data Manipulation Language, Lenguaje de Manipulación de Datos) engloba aquellas sentencias que nos permiten manipular la información que almacenamos en las Bases de Datos. Principalmente existen 4 instrucciones: insertar datos en una tabla (''INSERT''), modificar esos datos (''UPDATE''), eliminarlos (''DELETE'') y consultarlos (''SELECT''). ==== Base de Datos de prueba ==== Todos los ejemplos sobre estas sentencias DML se han escrito tomando como referencia la Base de Datos ''reservas'' que se proporciona junto con este documento. También pueden aparecer algunos ejemplos tomando como referencia las Bases de Datos de [[http://dev.mysql.com/doc/index-other.html|ejemplos oficiales de MySQL]] A continuación se muestran el modelo entidad-relación y relacional de esta Base de Datos de prueba: {{ reservas.png | Modelo E-R. Base de Datos de prueba (reservas)}} pistas (#id, codigo, tipo, precio, -id_polideportivo) pistas_abiertas(#-id_pista, operativa, precio, fecha_ultima_reserva, proxima_revision) pistas_cerradas (#-id_pista, fecha_clausura, motivo) polideportivos (#id, nombre, direccion, ciudad, extension) servicios_polideportivo(#id, servicio, -id_polideportivo) reservas (#id, fecha_reserva, fecha_uso, precio, -id_pista) usuarios (#id, dni, nombre, apellidos, email, ciudad, fecha_nacimiento, descuento, fecha_alta) usuario_usuario (#(-id_usuario, -id_amigo)) usuario_reserva (#(-id_usuario, -id_reserva), asiste) La base de datos puede descargarse desde {{ :extra:bd-reservas.zip| este enlace.}} ===== Modificar Información ===== ==== Inserción de registros ==== La inserción de nuevos registros a una tabla se efectúa con la sentencia ''INSERT'', que tiene el siguiente formato: INSERT INTO nombre_tabla [ '('columnas')' ] { VALUES '(' { valores } ')',} | consulta Veamos algunos ejemplos: * Para insertar una fila en una tabla INSERT INTO usuarios (dni, nombre, apellidos, email, fecha_nacimiento) VALUES ('123456789A', 'Antonio', 'García', 'agarcia@gmail.com', '1990-12-12'); INSERT INTO usuarios VALUES (200, '123456789A', 'Antonio', 'García', 'agarcia@gmail.com', 'Zaragoza', '1990-12-12', 0.3, '2003-02-01'); INSERT INTO usuarios (id, dni, nombre, apellidos, email, fecha_nacimiento) VALUES (45, '123456789A', 'Antonio', 'García', 'agarcia@gmail.com', '1990-12-12'); * Para insertar varias filas en una tabla INSERT INTO usuarios (dni, nombre, apellidos, email, fecha_nacimiento) VALUES ('123456789A', 'Pepe', 'Sanz', 'psanz@gmail.com', '1990-12-12'), ('987654321Z', 'Luis', 'Peréz', 'lperez@gmail.com', '1988-01-03'); * Para insertar el resultado de una consulta en una tabla (suponiendo que la tabla ''otros_usuarios'' existe y tiene al menos los campos indicados del mismo tipo que en la tabla ''usuarios'') INSERT INTO usuario (dni, nombre, apellidos, email, fecha_nacimiento) SELECT dni, nombre, apellidos, email, fecha_nacimiento FROM otros_usuarios; ==== Modificación de registros ==== La modificación de registros ya insertados en la tabla se realiza con la sentencia ''UPDATE'', que tiene el siguiente formato: UPDATE nombre_tabla SET columna = valor [ {, columna = valor} ] [ WHERE condiciones ] Veamos algunos ejemplos: * Actualizar una columna de una fila UPDATE usuarios SET nombre = 'Felipe' WHERE id = 12; * Actualizar varias columnas de una fila UPDATE usuarios SET nombre = 'Felipe', dni = '123654789H' WHERE id = 15; * Actualizar una columna de varias filas UPDATE pistas SET precio = precio + precio * 0.10 WHERE precio < 20 AND tipo = 'tenis'; * Actualizar una columna utilizando una subconsulta: -- Reduce el precio de las pistas que no se han reservado todavía UPDATE pistas SET precio = precio - precio * 0.1 WHERE id NOT IN (SELECT id_pista FROM reservas); Cabe destacar que la ausencia de condiciones en una sentencia de actualización, ejecutaría dicha sentencia sobre todas las filas de la tabla. ==== Eliminación de registros ==== El borrado de filas de una tabla se efectúa con la sentencia ''DELETE'', que tiene el siguiente formato: DELETE FROM nombre_tabla [ WHERE condiciones ] Veamos algunos ejemplos: * Elimina todos los usuarios DELETE FROM usuarios; * Borrar una fila estableciendo una condición DELETE FROM pistas WHERE id = 10; * Borrar varias filas estableciendo varias condiciones DELETE FROM pistas WHERE tipo = 'baloncesto' OR codigo = 'BAL001'; * Borrar filas relacionando varias tablas -- Elimina los usuarios que se dieron de alta antes de 2014 -- y aún no han reservado ninguna pista DELETE FROM usuarios WHERE id NOT IN (SELECT id_usuario FROM usuario_reserva) AND fecha_alta < '2014-01-01'; Cabe destacar que la ausencia de condiciones en una sentencia de borrado, eliminaría todas las filas de la tabla. ==== Generar Datos de Prueba ==== Para poder trabajar con una base de datos es necesario que esta contenga datos, cuanta más cantidad mejor. Para ello necesitamos hacer muchas inserciones (INSERT INTO) en cada una de las tablas de la base de datos. Existen programas que tienen sus propias bases de datos con datos de ejemplo y nos permiten generar datos adaptados a los campos de nuestras tablas. En la sección [[extra:software#Generadores de datos |Software Necesario]] de esta wiki tenemos distintos generadores, de los que tratamos en el siguiente video: {{ vimeo>663374033?medium }} > Generadores de datos ===== Consulta de registros ===== ==== La sentencia SELECT ==== La consulta de registros es la operación mas compleja, y también la más ejecutada, de una Base de Datos. Se lleva a cabo con la sentencia ''SELECT'', que tiene el siguiente formato: SELECT columnas FROM tablas [ WHERE condiciones ] [ GROUP BY columnas ] [ HAVING condiciones_de_grupo ] [ ORDER BY columnas_a_ordenar [ASC|DESC] ] Veamos para que sirve cada una de las cláusulas de esta sentencia: === SELECT . . . FROM . . . === La cláusula ''SELECT'' se utiliza para selecciona las columnas que se quieren visualizar como resultado de la consulta. Se puede seleccionar cualquier columna de las tablas afectadas por la consulta (cláusula ''FROM''), valores constantes establecidos a la hora de ejecutar la consulta, o bien el comodín '*' para indicar que se quieren visualizar todas las columnas afectadas. La cláusula ''FROM'' permite indicar con qué tablas se trabajará en la consulta. No siempre serán tablas de las que se visualicen columnas, puesto que muchas veces sólo se utilizarán para relacionar unas tablas con otras. En cualquier caso, se usen para que se visualicen sus campos o bien para relacionar otras tablas (que no están directamente relacionadas), se deben indicar en esta cláusula. Veamos algunos ejemplos: * Consulta de una columna de una tabla -- Nombre de todos los usuarios (incluye repeticiones) SELECT nombre FROM usuarios; * Consulta de dos columnas de una tabla -- Nombre y apellidos de todos los usuarios SELECT nombre, apellidos FROM usuarios; * Consulta de todas las columnas de una tabla -- Toda la información de todos los usuarios SELECT * FROM usuarios; === WHERE === La cláusula ''WHERE'' permite establecer condiciones sobre que filas se mostrarán en una sentencia de consulta. En ausencia de esta cláusula se muestran todos los registros de la tabla (aunque sólo las columnas establecidas en la cláusula ''SELECT''). Si se indican condiciones mediante la cláusula ''WHERE'' sólo se mostrarán aquellas filas que las cumplan. Veamos algunos ejemplos: -- Nombre y dirección de los polideportivos de Zaragoza SELECT nombre, direccion FROM polideportivos WHERE ciudad = 'Zaragoza'; Además, nos permitirá establecer condiciones para establecer lo que se conoce como un ''INNER JOIN'' (implícito) entre dos o más tablas: -- Código y tipo de las pistas de tenis que están operativas SELECT pistas.codigo, pistas.tipo FROM pistas, pistas_abiertas WHERE pistas.id = pistas_abiertas.id_pista AND pistas_abiertas.operativa = TRUE AND pistas.tipo = 'tenis'; -- Código y tipo de las pistas de los polideportivos -- de Zaragoza SELECT pistas.codigo, pistas.tipo FROM pistas, polideportivos WHERE pistas.id_polideportivo = polideportivos.id AND polideportivos.ciudad = 'Zaragoza' De manera que si utilizamos //alias// para los nombres de las tablas, podemos escribir la misma consulta algo más rápido: -- Código y tipo de las pistas de los polideportivos -- de Zaragoza SELECT P.codigo, P.tipo FROM pistas P, polideportivos PP WHERE P.id_polideportivo = PP.id AND PP.ciudad = 'Zaragoza' {{ youtube>hX1EZ-5mCIk }} \\ === GROUP BY / HAVING === Las cláusulas ''GROUP BY'' y ''HAVING'' permiten crear agrupaciones de datos y establecer condiciones sobre dichas agrupaciones, respectivamente. -- Número de polideportivos hay en cada ciudad SELECT ciudad, COUNT(*) AS cantidad FROM polideportivos GROUP BY ciudad; -- Número de polideportivos hay en cada ciudad, solamente de aquellas -- ciudades donde hay más de 10.000 SELECT ciudad, COUNT(*) AS cantidad FROM polideportivos GROUP BY ciudad HAVING COUNT(*) > 10000; -- Número de usuarios en cada ciudad SELECT ciudad, COUNT(*) AS cantidad FROM usuarios GROUP BY ciudad; También es posible añadir claúsulas ''WHERE'' para filtrar registros y agrupar el resultado final -- Precio medio, por tipo de pista, de las pistas que no están operativas SELECT P.tipo, AVG(P.precio) AS precio_medio FROM pistas P, pistas_abiertas PA WHERE P.id = PA.id_pista AND PA.operativa = FALSE GROUP BY P.tipo; En los casos en los que usemos las cláusulas ''WHERE'' y ''HAVING'' junto con ''GROUP BY'', debemos tener en cuenta que: * ''WHERE'': filtra los registros antes de agrupar * ''HAVING'': filtra los resultados después de agrupar Además, hay que tener en cuenta que en ocasiones nos puede interesar agrupar por algún campo que no va a ser mostrado como resultado de la consulta en la claúsula ''SELECT''. En este caso, agruparíamos por ''id'' pero mostraríamos el ''nombre'' del polideportivo, ya que puede ser un dato más interesante para el usuario. Además, en casos en que el campo que queramos mostrar pueda repetirse, no conviene agrupar por dicho campo puesto que agruparía registros que en principio no tienen nada que ver. Además, en los dos siguientes ejemplos, unimos dos tablas para realizar la consulta y trabajar con los datos relacionados entre ambas (utilizando la claúsula ''WHERE'' para realizar la unión o //JOIN// que más adelante se explicará con más detalle): -- Cantidad de pistas que hay en cada polideportivo SELECT PP.nombre, COUNT(*) AS numero_pistas FROM polideportivos PP, pistas P WHERE PP.id = P.id_polideportivo GROUP BY PP.id; -- Número de reservas que ha hecho cada usuario SELECT U.apellidos, U.nombre, U.ciudad, COUNT(*) AS numero_reservas FROM usuarios U, usuario_reserva UR WHERE U.id = UR.id_usuario GROUP BY U.id ORDER BY U.apellidos; -- Número de pistas que hay de cada tipo en el polideportivo 'ACTUR 1' SELECT P.tipo, COUNT(*) AS numero_pistas FROM pistas P, polideportivos PP WHERE P.id_polideportivo = PP.id AND PP.nombre = 'ACTUR 1' GROUP BY P.tipo; {{ youtube>XhYHvqigNiE }} \\ === ORDER BY === La cláusula ''ORDER BY'' permite ordenar el resultado de cualquier consulta atendiendo al campo o campos especificados en esta cláusula, ya sea en orden ascendente (''ASC'') o descendente (''DESC'') -- Nombre y apellidos de los usuarios, ordenados por nombre SELECT nombre, apellidos FROM usuarios ORDER BY nombre ASC; {{ youtube>Nk6JnS4egC0 }} \\ === LIMIT === La clausula ''LIMIT'' restringe el número máximo de registros devueltos por una consulta ''SELECT'' al número indicado. Se puede aplicar a casi todas las consultas, y genera un menor tiempo de respuesta ya que la ejecución de la consulta termina en el momento en que se completa el cupo de registros solicitados: -- Muestra los 3 primeros registros que cumplan la consulta SELECT nombre, apellidos FROM usuarios ORDER BY nombre ASC LIMIT 3; ==== Operadores ==== A la hora de establecer condiciones en una sentencia de consulta, podremos utilizar los siguientes operadores: * ''='': Igual * ''<'': Menor * ''>'': Mayor * ''< ='': Menor o igual * ''>='': Mayor o igual * ''<>'': Distinto * ''NOT'': Operador lógico para la negación de condiciones * ''AND'': Operador lógico para la conjunción de condiciones * ''OR'': Operador lógico para la disyunción de condiciones * ''DISTINCT'': Se utiliza para indicar a la cláusula SELECT que no se muestren valor de columnas repetidos * ''LIKE'': Permite comprobar si una cadena de caracteres cumple algún patrón determinado: Permite la expresión de patrones a través de dos caracteres comodín: * El carácter '_' para expresar un único carácter * El carácter '%' para expresar cualquier secuencia de caracteres o incluso la secuencia vacía Veamos un par de ejemplos: -- Nombre de los polideportivos que están en una ciudad -- cuyo nombre empieza por Z y tiene 8 caracteres SELECT nombre FROM polideportivos WHERE ciudad LIKE 'Z_______'; -- Nombre de los polideportivos que están en una ciudad -- cuyo nombre empieza por Z SELECT nombre FROM polideportivos WHERE ciudad LIKE 'Z%'; * ''IN'' | ''NOT IN'': Permite comprobar si un valor coincide (o no) con algún valor especificado como un conjunto. -- Nombre y extensión de los polideportivos de -- Zaragoza, Huesca y Teruel SELECT nombre, extension FROM polideportivos WHERE ciudad IN ('Zaragoza', 'Huesca', 'Teruel'); * ''IS NULL'' | ''IS NOT NULL'': Se utiliza para comprobar si un valor es igual (o no) a ''NULL''. Dicha comprobación no debe realizar con ningún otro operador. -- Nombre y apellidos de los usuarios que no indicaron su fecha de nacimiento SELECT nombre, apellidos FROM usuarios WHERE fecha_nacimiento IS NULL; * ''BETWEEN'': Permite comprobar si el valor de una columna está comprendido entre dos valores determinados: -- Nombre y apellidos de los usuarios que tienen un descuento -- entre 10 y 20 % SELECT nombre, apellidos FROM usuarios WHERE descuento BETWEEN 0.1 AND 0.2; {{ youtube>V_h6u6cLpHg }} \\ === Operadores Aritméticos === Por otra parte, también tenemos los operadores aritméticos habituales de los lenguajes de programación: ^Operador^Operación^ |+|Suma| |-|Resta| |*|Producto| |/|División decimal| |div|División entera| |mod ó %| Módulo: resto entero de división.| -- Calcula el 10% de las ganancias por reservas del año pasado SELECT (SUM(precio) * 0.1) AS 'impuestos' FROM reservas WHERE YEAR(fecha_reserva) = YEAR(CURDATE()) - 1; ==== Funciones agregadas ==== Son funciones que proporciona el lenguaje SQL, que permiten realizar operaciones sobre los datos de una base de datos: * ''COUNT'': Devuelve el número de filas seleccionadas -- Número de pistas SELECT COUNT(*) FROM pistas; -- Número de polideportivos en Zaragoza SELECT COUNT(*) FROM polideportivos WHERE ciudad = 'Zaragoza'; * ''SUM'': Devuelve la suma de todos los valores de una columna -- Cuánto dinero costaría alquilar todas las pistas del -- polideportivo cuyo id es 23 SELECT SUM(precio) FROM pistas WHERE id_polideportivo = 23; * ''MIN'': Devuelve el valor mínimo de una columna -- Cuánto vale la pista más barata SELECT MIN(precio) FROM pistas; * ''MAX'': Devuelve el valor máximo de una columna -- Cuánto vale la pista más cara SELECT MAX(precio) FROM pistas; * ''AVG'': Devuelve el valor medio de los valores de una columna -- Valor medio de las pistas SELECT AVG(precio) FROM pistas; Hay que tener en cuenta que, excepto la función ''COUNT'', todas las demás devolverán el valor ''NULL}'' si no hay columnas sobre las que puedan operar. La función ''COUNT'', sin embargo, devolverá el valor 0 en ese caso. {{ youtube>TKpCt-gOxFQ }} \\ ==== Subconsultas ==== La creación de subconsultas permite utilizar el resultado de una consulta como valor de entrada para la condición de otra consulta principal. Veamos unos ejemplos -- Código y tipo de la pista más barata SELECT codigo, tipo FROM pistas WHERE precio = (SELECT MIN(precio) FROM pistas); -- Codigo y tipo de las pistas cuyo precio está por encima de la media SELECT codigo, tipo FROM pistas WHERE precio > (SELECT AVG(precio) FROM pistas) -- Nombre y apellidos de los usuarios que aún no han realizado -- ninguna reserva SELECT nombre, apellidos FROM usuarios WHERE id NOT IN (SELECT id_usuario FROM usuario_reserva) ==== Consultas con varias tablas ==== Como se ha visto anteriormente, aplicando la cláusula ''WHERE'', introducíamos una posibilidad más a la hora de realizar consultas sobre los datos de nuestra base de datos, lo que se conoce como una consulta de varias tablas o combinación de tablas (en inglés ''JOIN''). Veamos varios ejemplos: -- Mostrar, para cada polideportivo, el código y tipo de las pistas -- que tiene SELECT PP.id, PP.nombre, P.codigo, P.tipo FROM polideportivos PP, pistas P WHERE PP.id = P.id_polideportivo -- Consulta equivalente SELECT PP.id, PP.nombre, P.codigo, P.tipo FROM polideportivos PP INNER JOIN pistas P ON PP.id = P.id_polideportivo De esta forma, al incluir a más de una tabla en la cláusula ''FROM'' estamos realizando lo que se conoce como una combinación interna (''INNER JOIN''), de forma que cabe la posibilidad de que sólo se muestren algunos datos de alguna de las tablas, puesto que la combinación interna sólo se queda con aquellos registros que están relacionadas con algún registro de la otra tabla. {{ inner_join.gif }} {{ youtube>ywbpcn0ogGI }} \\ Si ahora tenemos en cuenta que algún cliente puede no haber realizado pedido alguno, veremos como no aparecen en el resultado de la consulta anterior. En algunos casos eso será lo que queramos, pero quizás en otros casos nos interesa que su nombre aparezca aunque no esté vinculado con ninguno de los pedidos. En este caso nos interesa lo que se conoce como ''OUTER JOIN''. En resumen, si alguna fila de cualquier tabla de la consulta puede no estar relacionado con alguna de las otras, puede ser interesante utilizar un ''OUTER JOIN''. Decidir si utilizar un ''LEFT OUTER JOIN'' o bien un ''RIGHT OUTER JOIN'' depende de si el dato que puede no tener relación con la otra tabla está a la izquierda o la derecha, respectivamente, según el sentido en que se escribe el código //SQL//. {{ left_join.png }} {{ right_join.gif }} -- Mostrar, para cada pista, el codigo de reserva que ha tenido -- Si nunca se ha reservado, se mostrarán sólo sus datos -- (En este caso puede pasar que una pista no esté relacionada con -- ninguna reserva, como se puede ver en el modelo E-R) SELECT P.id, P.codigo, P.tipo, R.id AS codigo FROM pistas P LEFT OUTER JOIN reservas R ON P.id = R.id_pista; ORDER BY P.codigo De esta forma mostraremos también los datos de los pistas que no estén relacionados con ninguna reserva. Hay que tener en cuenta que, sólo en este caso, es relevante el orden en el que se especifican las tablas a la hora de definir el ''JOIN'' puesto que se incluirán //aquellas filas de la tabla del lado izquierdo que no tengan relación con las de la tabla del lado derecho//. Es por ello que en los ''INNER JOIN'' no se tiene que indicar el sentido de la unión. -- Mostrar cuántas veces se ha reservado cada pista SELECT P.id, P.codigo, P.tipo, COUNT(R.id) AS reservas FROM pistas P LEFT OUTER JOIN reservas R ON P.id = R.id_pista GROUP BY P.id ORDER BY P.codigo; -- Mostrar cuántas reservas ha hecho cada usuario -- (Es posible que algún usuario no haya hecho reservas. Ver E-R) SELECT U.dni, U.nombre, U.apellidos, COUNT(R.id) AS numero_reservas FROM usuarios U LEFT OUTER JOIN usuario_reserva UR ON U.id = UR.id_usuario LEFT OUTER JOIN reservas R ON UR.id_reservas = R.id LEFT OUTER JOIN pistas P ON R.id_pista = P.id; {{ youtube>rP91QeY9P6Y }} \\ En definitiva, a la hora de construir una consulta //SQL// hay que añadir en la claúsula ''FROM'' todas aquellas tablas que estén involucradas en la consulta, bien porque se muestre alguna de sus columnas en la claúsula ''SELECT'', porque se establezca alguna condición con ''WHERE'', se agrupe por alguno de sus campos o incluso simplemente dicha tabla haga de //puente// entre dos tablas que deban estar involucradas en dicha consulta. Con respecto al número de tablas que pueden ser incluidas en un //JOIN//, hay que tener en cuenta que el límite en //MySQL// es de 61. {{ joins.jpeg }} {{ youtube>iLH8fqBlPBQ }} \\ ==== Unión e intersección de consultas ==== La unión de consultas permite unir los resultados de dos consultas totalmente diferentes como si fuera el de una sola. Se realiza mediante la instrucción ''UNION'' y muestra los resultados sin repeticiones. -- Código y tipo de las pistas abiertas y cerradas, indicando -- el estado actual SELECT 'abierta', codigo, tipo FROM pistas WHERE id IN (SELECT id_pista FROM pistas_abiertas) UNION SELECT 'cerrada', codigo, tipo FROM pistas WHERE id IN (SELECT id_pista FROM pistas_cerradas); La intersección de consultas muestra sólo los valores que aparecen en las dos consultas que se intersectan. Se realiza mediante la instrucción ''INTERSECT'': -- Ciudades con polideportivos que cuentan con usuarios registrados SELECT ciudad FROM usuarios INTERSECT SELECT ciudad FROM polideportivos; ==== Consultas complejas==== A continuación se indican algunos métodos para resolver tipos de consultas que no podemos resolver con los visto anteriormente. === Subconsultas en cláusula FROM === El supuesto anterior se puede resolver también con otro tipo de subconsultas. Existe la posibilidad de incluir subconsultas en la clausula ''FROM'': -- Nombre del polideportivo que más pistas tiene SELECT pistas_polideportivo.nombre FROM (SELECT PP.id, PP.nombre, COUNT(*) AS cantidad FROM polideportivos PP, pistas P WHERE PP.id = P.id_polideportivo GROUP BY PP.id) AS pistas_polideportivo WHERE cantidad = (SELECT MAX(cantidad) FROM (SELECT COUNT(*) AS cantidad FROM polideportivos PP, pistas P WHERE PP.id = P.id_polideportivo GROUP BY PP.id) AS cantidad_pistas); Viendo los dos enfoques, resulta más sencillo guardar una consulta como una //Vista// para luego hacer una consulta más sencilla sobre ella. === Operadores ANY y ALL === Son dos operadores que se emplean en subconsultas. Se utilizan con los operadores de comparación (>,>=, <, <=, !=, <>, =). Al igual que en los ejemplos anteriores, los usaremos con subconsultas que devuelven varios valores pero solo una columna. * ''ANY'': comprueba si hay valores de la condición que se encuentran entre los valores que devuelve la subconsulta. * ''ALL'': comprueba si hay valores de la condición que cumplen con todos los valores que devuelve la subconsulta. *Cuando la condición emplea ''= ANY'' es identico a usar el operador ''IN''. Cuando la condición emplea ''<> ALL'' es identico a usar el operador ''NOT IN''. -- Usuarios de Zaragoza que tienen más descuente que algún -- usuario de Huesca SELECT nombre, apellidos, descuento FROM usuarios WHERE ciudad = 'Zaragoza' AND descuento > ANY (SELECT descuento FROM usuarios WHERE ciudad = 'Huesca'); -- Nombre y apellidos del usuario/s que más reservas -- ha realizado SELECT u.nombre, u.apellidos FROM usuarios u, usuario_reserva ur WHERE u.id = ur.id_usuario GROUP BY u.id HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM usuario_reserva ur GROUP BY ur.id_usuario); ===== Funciones de MySQL ===== ==== Funciones para cadenas de caracteres ==== * ''CHAR_LENGTH(str)'': Devuelve la longitud, en caracteres, de una cadena de texto > SELECT CHAR_LENGTH('Esto es una cadena'); -> 18 * ''CONCAT(str1, str2, . . .)'': Concatena las cadenas de texto que se pasan como parámetros > SELECT CONCAT('Esto', ' forma ', 'una cadena'); -> 'Esto forma una cadena' * ''LOWER(str)'': Devuelve la cadena convertida a minúsculas > SELECT LOWER('Bases de Datos Relacionales'); -> 'bases de datos relacionales' * ''LTRIM(str)'': Elimina los espacios en blanco al comienzo de la cadena > SELECT LTRIM(' MySQL'); -> 'MySQL' * ''REPLACE(str, from_str, to_str)'': Reemplaza todas las ocurrencias de 'from_str' por 'to_str' que aparezcan en la cadena 'str' > SELECT REPLACE('Access es un buen SGBD', 'Access', 'MySQL'); -> 'MySQL es un buen SGBD' * ''RTRIM(str)'': Elimina los espacios en blanco al final de la cadena > SELECT RTRIM('MySQL '); -> 'MySQL' * ''STRCMP(expr1, expr2)'': Devuelve 0 si las cadenas son idénticas, -1 si la primera es menor, y 1 en cualquier otro caso > SELECT STRCMP('MySQL', 'Access'); -> -1 * ''SUBSTRING(str, pos), SUBSTRING(str, pos, len)'': Devuelve una subcadena de 'str' comenzando en la posición 'pos'. En el segundo caso tomará caracteres hasta completar una subcadena de tamaño 'len' > SELECT SUBSTRING('MySQL es un buen SGBD', 7); -> 'es un buen SGBD' > SELECT SUBSTRING('MySQL es un buen SGBD', 7, 2); -> 'es' * ''TRIM(str)'': Elimina los espacios en blanco al comienzo y al final de la cadena > SELECT TRIM(' MySQL '); -> 'MySQL' * ''UPPER(str)'': Devuelve la cadena convertida a mayúsculas > SELECT UPPER('Esto es una cadena'); -> 'ESTO ES UNA CADENA' ==== Funciones numéricas ==== * ''RAND(), RAND(semilla)'': Devuelve un número aleatorio del rango de 0 a 1.0. Es posible pasar una semilla como parámetro para generar secuencias repetibles > SELECT RAND(); -> 0.838373287632872 > SELECT RAND(20); -> 0.1588498478438734 > SELECT RAND(20); -> 0.1588498478438734 * ''ROUND(X), ROUND(X, D)'': Redondea el valor de X al número más cercano. Si se especifica el parámetro 'D' se pueden indicar los decimales con los que se quiere aproximar > SELECT ROUND(0.67); -> 1 > SELECT ROUND(1.235); -> 1 > SELECT ROUND(1.235, 1); -> 1.2 ==== Funciones de fecha ==== * ''CURDATE()'': Devuelve la fecha actual > SELECT CURDATE(); -> '2012-01-09' * ''CURTIME()'': Devuelve la hora actual > SELECT CURTIME(); -> '19:23:02' * ''CURRENT_TIMESTAMP()'': Devuelve la fecha y hora actual > SELECT CURRENT_TIMESTAMP(); -> '2012-01-09 19:23:02' * ''DATE(expr)'': Extrae la fecha de una expresión de fecha o de fecha y hora > SELECT DATE('2012-01-09 19:23:02'); -> '2012-01-09' * ''DATEDIFF(expr, expr2)'': Devuelve el número de días entre la fecha inicial 'expr' y la fecha final 'expr2' > SELECT DATEDIFF('2011-01-09', CURDATE()); -> -365 * ''DATE_ADD(fecha, INTERVAL expr1 type), DATE_SUB(fecha, INTERVAL expr type)'': Realizan operaciones aritméticas con fechas Como valor de 'type' se pueden especificar los partes de fecha con los que se quiera realizar el cálculo. Estos son algunos: * ''SECOND'': Segundos * ''MINUTE'': Minutos * ''HOUR'': Horas * ''DAY'': Días * ''WEEK'': Semanas * ''MONTH'': Meses * ''YEAR'': Años * ''DAY_HOUR'': Días Horas * ''YEAR_MONTH'': Años-Meses > SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL 1 DAY): -> '1998-01-01 23:59:59' > SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY); -> '1997-12-02' > SELECT DATE_ADD('1998-01-02', INTERVAL 1-1 YEAR_MONTH); -> '1999-02-02' * ''DATE_FORMAT(fecha, formato)'': Formatea el valor de fecha a través de estos especificadores: * ''%c'' Mes (1-12) * ''%e'' Día del mes (1-31) * ''%h'' Hora (01-12) * ''%H'' Hora (00-23) * ''%i'' Minutos (00-59) * ''%M'' Nombre del mes * ''%m'' Mes (01-12) * ''%s'' Segundos (00-59) * ''%Y'' Año (cuatro dígitos) * ''%y'' Año (dos dígitos) > SELECT DATE_FORMAT('1998-01-01 23:59:59', '%e/%m/%y'); -> '1/01/98' > SELECT DATE_FORMAT('1998-01-01 23:59:59', '%e/%M/%Y %H:%i'); -> '1/January/1998 23:59' * ''DAY(fecha)'': Devuelve el día del mes de la fecha especificada (1-31) > SELECT DAY('1998-01-01 23:59:59'); -> 1 * ''DAYNAME(fecha)'': Devuelve el nombre del día de la semana de la fecha especificada > SELECT DAYNAME('1998-01-01 23:59:59'); -> Thursday * ''DAYOFYEAR(fecha)'': Devuelve el día del año para la fecha especificada (1-366) > SELECT DAYOFYEAR('1998-01-01 23:59:59'); -> 1 * ''MONTH(fecha)'': Devuelve el número de mes para la fecha especificada (1-12) > SELECT MONTH('1998-01-01 23:59:59'); -> 1 * ''MONTHNAME(fecha)'': Devuelve el nombre del mes para la fecha especificada > SELECT MONTHNAME('1998-01-01 23:59:59'); -> January * ''WEEK(fecha)'': Devuelve el número de la semana para la fecha especificada (0-52) > SELECT WEEK('1998-01-01 23:59:59'); -> 0 * ''WEEKDAY(fecha)'': Devuelve el día de la semana para la fecha especificada (0-6) > SELECT WEEKDAY('1998-01-01 23:59:59'); -> 3 * ''YEAR(fecha)'': Devuelve el número de año para la fecha especificada > SELECT YEAR('1998-01-01 23:59:59'); -> 1998 ---- (c) {{date> %Y}} Santiago Faci y Fernando Valdeón