Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
Gestión de Datos Mg. Ing. Gustavo E. Juárez FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional PARTE I - SQL Mg. Ing. Gustavo E. Juárez FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional UNIDAD TEMATICA Nº 8: Utilizar SQL y SQL DDL como lenguaje de consulta a tablas. Introducción a los lenguajes de consultas. Diccionarios de Datos. Tipos de Datos. Vistas. Índices. Set de Comandos. Optimización de Consultas e Indexado. Mg. Ing. Gustavo E. Juárez FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional Introducción SQL como lenguaje de consulta IBM desarrollo la versión original de SQL (Structured Query Language o Lenguaje de Consulta Estructurado), a la cual se conocía como Sequel, la cual era una parte del Proyecto del Sistema R. Desde entonces el SQL evoluciono, hasta nuestros días en que lo encontramos como lenguaje estándar de consulta a bases de datos. Existieron varias versiones que fueron estandarizadas por ANSI (86, 89, 92, 99 y 2003), lo cual permitió unificar criterios en cuanto al lenguaje de consulta a bases de datos, y facilito su utilización por parte de las empresas que desarrollan productos de estas características. 4 FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL SQL como lenguaje de consulta El Lenguaje SQL consta de varios componentes: Lenguaje de Definición de Datos (DDL): Proporciona comandos para la definición de esquemas de relación, borrado de relaciones y modificaciones a los esquemas de relación. Lenguaje Interactivo de Manipulación de datos (DML): Incluye un lenguaje de consultas basado tanto en el algebra relacional, como en el calculo relacional de tuplas. También contiene comandos para insertar, borrar y modificar tuplas. Integridad: EL DDL incluye comandos para especificar las restricciones de integridad que deben cumplir los datos almacenados en la base de datos. Las actualizaciones que violan las restricciones de integridad se rechazan. 5 FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL SQL como lenguaje de consulta El Lenguaje SQL consta de varios componentes: •Control de Transacciones: Incluye comandos para especificar el comienzo y el final de las transacciones. •Autorización: Incluye comandos para especificar los derechos de acceso a las relaciones y a las vistas. 6 FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL El lenguaje de Definición de Datos (DDL) Permite: - Especifica un conjunto de relaciones. - Proporcionar información relativa a un conjunto de relaciones. Permite Conocer: - El esquema de cada relación. - El dominio de valores asociados a cada atributo. - Las restricciones de integridad. - El conjunto de índices que se deben mantener para cada relación. - La información de seguridad y de autorización de cada relación. - A estructura de almacenamiento físico de cada relación en el disco. FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL Tipos Básicos de Dominios La norma SQL soporta gran variedad de tipos de dominios predefinidos, entre ellos: Char(n): cadena de caracteres de longitud fija, con una longitud n especificada por el usuario. Varchar (n): cadena de caracteres de longitud variable con una longitud máxima de n especificada por el usuario. Int: Entero Smallint: Entero pequeño Numeric (p,d): Numero de coma flotante, cuya precisión la especifica el usuario. El numero esta formado por p dígitos (mas el signo) y de esos p dígitos, d pertenecen a la parte decimal. Real, double precision: Números de coma flotante y números de coma flotante de doble precisión. Float(n): numero de coma flotante con al menos n dígitos de precisión. FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL Comandos El lenguaje SQL está compuesto por comandos, cláusulas, operadores y funciones de agregado. Estos elementos se combinan en las instrucciones para crear, actualizar y manipular las bases de datos. FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL Clausulas Las cláusulas son condiciones de modificación utilizadas para definir los datos que desea seleccionar o manipular. FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL Operadores Lógicos Operadores de Comparación FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL Consulta de datos. El proceso más importante que podemos llevar a cabo en una base de datos es la consulta de los datos. Es además la operación que efectuaremos con mayor frecuencia. FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL Los comandos más importantes en DDL son: - CREATE TABLE - CREATE INDEX - ALTER TABLE - ALTER INDEX - DROP TABLE - DROP INDEX FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – DDL CREATE TABLE La forma de dicho comando es: CREATE TABLE nombre_tabla (nombre_columna tipo_columna, [restricciones – NULL/NOT NULL, DEFAULT, UNIQUE, CHECKJ],……, [restricciones tabla – PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK….]) FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – DDL CREATE INDEX Si queremos crear un índice con respecto al LAST_NAME quedaría la sintaxis de la siguiente manera: CREATE INDEX idxlastname ON Customer (Last_Name); Si queremos que el índice se de mas de una columna se escribe: CREATE INDEX idxdireccion ON Customer (City,Country); CREATE INDEX idxnombrecompleto ON Customer (First_Name, Last_Name); FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – DDL ALTER TABLE Una vez creada una tabla, los usuarios pueden encontrarla más útil si contiene un ítem de datos adicional, no tiene una columna particular o tiene diferentes restricciones. Aquí, la naturaleza dinámica de una estructura de base de datos relacional hace posible cambiar las tablas base existentes. Por ejemplo, para agregar una nueva columna a la derecha de la tabla se usa el comando de la siguiente forma: ALTER TABLE nombre_tabla ADD nombre_columna tipo_columna; Ejemplo: se decide agregar a la tabla Productos la columna Estado, cuyo tipo de datos es char(15) ALTER TABLE productos ADD Estado CHAR(15); Por lo que nuestra tabla queda de la siguiente forma: PRODUCTOS (codigo_producto, nombre_producto, tipo, descripcion, precio, Estado) FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – DDL DROP TABLE Las tablas se pueden eliminar en cualquier momento mediante el comando: DROP TABLE nombre_tabla; Cuando se ejecuta este comando se remueven la tabla en si y todos los registros contenidos en ella. Además, todos los indices y todas las vistas que dependen de ella se deben de eliminar. También se puede eliminar cualquier indice con la siguiente secuencia: DROP INDEX nombre_indice; Mg. Ing. GustavoE. Juárez FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – DDL Tipos de Datos Los tipos de datos disponibles concluyen varios tipos numéricos, cadenas de caracteres de longitud fija y de longitud variable, cadenas de bits y tipos definidos por el usuario. Los tipos de datos disponibles varían de DBMS a DBMS. FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – DML Manipulación de la Base de Datos (DML) El lenguaje de consulta de SQL es declarativo, también llamado no procedural, lo que significa que permite especificar cuales datos se recuperan sin dar los procedimientos para recuperarlos. Se puede usar como un lenguaje interactivo para consultas, incrustado en un lenguaje de programación huésped, o como un lenguaje completo en si para cálculos con el uso de SQL/PSM (Persistent Stored Modules= Modulos de almacenamiento persistentes). Los comandos DML son: • SELECT • UPDATE • INSERT • DELETE FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – DML Sentencia INSERT Una sentencia INSERT de SQL agrega uno o más registros a una ( y solo una) tabla en una base de datos relacional. Forma Básica INSERT INTO nombre_tabla (“nombre_columna1”,”[nombre_columna2…..]”) VALUES (“valor1”, “[valor2...]”); FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – DML Sentencia UPDATE Una sentencia UPDATE de SQL es utilizada para modificar los valores de un conjunto de registros existentes en una tabla. Forma Básica UPDATE nombre_tabla SET “nombre_columna1”=“nuevo_valor,” [“nombre_columna2”=“nuevo_valor2,….”] WHERE condición; Ejemplo UPDATE proveedores SET emailpro=mailto:marcoaurelio@gmail.com WHERE codigopro=4000; mailto:marcoaurelio@gmail.com FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – DML Sentencia DELETE La sentencia DROP de SQL elimina un objeto de la base de datos. Puede ser una tabla, vista, índice, trigger, función, procedimiento o cualquier otro objeto que el motor de la base de datos soporte. Forma Básica DROP TABLE nombre_tabla; La sentencia DELETE borra uno o más registros existentes en una tabla. Forma Básica DELETE FROM nombre_tabla WHERE nombre_columna1=valor1; Ejemplo DELETE FROM proveedores WHERE codigopro=4000 FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – DML Sentencia SELECT La sentencia SELECT nos permite consultar los datos almacenados en una tabla de la base de datos. El formato de la sentencia SELECT es: SELECT [ALL | DISTINCT ] nombre_columna [,nombre_columna…] FROM nombre_tabla|nombre_vista [nombre_tabla|nombre_vista…] [WHERE condicion [AND|OR condicion]] [GROUP BY nombre_columna [nombre_columna …]] [HAVING condicion [AND|OR condicion]] [ORDER BY nombre_columna|índice_columna [ASC | DESC], …[[nombre_columna|índice_columna[ASC | DESC ]]]] FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – DML Sentencia SELECT SELECT: Palabra clave que indica que la sentencia de SQL que queremos ejecutar es de selección. ALL: Indica que queremos seleccionar todos los valores. Es el valor por defecto y no suele especificarse casi nunca. DISTINCT: Indica que queremos seleccionar sólo los valores distintos. FROM: Indica la tabla (o tablas) desde la que queremos recuperar los datos. En el caso de que exista más de una tabla se denomina a la consulta "consulta combinada" o “JOIN". En las consultas combinadas es necesario aplicar una condición de combinación a través de una cláusula WHERE. WHERE: Especifica una condición que debe cumplirse para que los datos sean devueltos por la consulta. Admite los operadores lógicos AND y OR. GROUP BY : Especifica la agrupación que se da a los datos. Se usa siempre en combinación con funciones agregadas. HAVING: Especifica una condición que debe cumplirse para los datos. Especifica una condición que debe cumplirse para que los datos sean devueltos por la consulta. Su funcionamiento es similar al de WHERE pero aplicado al conjunto de resultados devueltos por la consulta. Debe aplicarse siempre junto a GROUP BY y la condición debe estar referida a los campos contenidos en ella. ORDER BY :Presenta el resultado ordenado por las columnas indicadas. El orden puede expresarse con ASC (orden ascendente) y DESC (orden descendente). El valor predeterminado es ASC. EJEMPLO SELECT matricula, marca, modelo, color, numero_kilometros, num_plazas FROM tCoches WHERE matricula= 'M-1525-ZA'; FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – SELECT La forma más sencilla es la que hemos usado hasta ahora, consiste en pedir todas las columnas y no especificar condiciones. mysql>mysql> SELECT * FROM gente; Pero podemos usar una lista de columnas, y de ese modo sólo se mostrarán esas columnas: FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – SELECT Las expresiones_select no se limitan a nombres de columnas de tablas, pueden ser otras expresiones, incluso aunque no correspondan a ninguna tabla: SELECT SIN(3.1416/2), 3+5, 7*4; También podemos aplicar funciones sobre columnas de tablas, y usar esas columnas en expresiones para generar nuevas columnas: SELECT nombre, fecha, DATEDIFF(CURRENT_DATE(),fecha)/365 FROM gente; FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – SELECT Aprovechemos la ocasión para mencionar que también es posible asignar un alias a cualquiera de las expresiones select. Esto se puede hacer usando la palabra AS, aunque esta palabra es opcional: SELECT nombre, fecha, DATEDIFF(CURRENT_DATE(),fecha)/365 as 'edad' FROM gente Vemos que existen dos valores de filas repetidos, para la fecha "1980-01-10" y para “2000-03-25". La sentencia que hemos usado asume el valor por defecto (ALL) para el grupo de opciones ALL, DISTINCT y DISTINCTROW. En realidad sólo existen dos opciones, ya que las dos últimas: DISTINCT y DISTINCTROW son sinónimos. La otra alternativa es usar DISTINCT, que hará que sólo se muestren las filas diferentes: SELECT DISTINCT fecha FROM gente; FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – SELECT Limitar las filas: selección Otra de las operaciones del álgebra relacional era la selección, que consistía en seleccionar filas de una relación que cumplieran determinadas condiciones. Lo que es más útil de una base de datos es la posibilidad de hacer consultas en función de ciertas condiciones. Generalmente nos interesará saber qué filas se ajustan a determinados parámetros. Por supuesto, SELECT permite usar condiciones como parte de su sintaxis, es decir, para hacer selecciones. Concretamente mediante la cláusula WHERE SELECT NOMBRE, FECHA FROM GENTE WHERE FECHA>='1982-12-20' AND FECHA<='2000-02-10' SELECT NOMBRE, FECHA FROM GENTE WHERE NOMBRE=‘RODRIGO’ FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – SELECT Agrupar filas Es posible agrupar filas en la salida de una sentencia SELECT según los distintos valores de una columna, usando la cláusula GROUP BY. Esto, en principio, puede parecer redundante, ya que podíamos hacer lo mismo usando la opción DISTINCT.Sin embargo, la cláusula GROUP BY es más potente SELECT FECHA, COUNT(FECHA) FROM GENTE GROUP BY FECHA Existen otras funciones de resumen o reunión, como MAX(), MIN(), SUM(), AVG(), STD(), VARIANCE()... SELECT MAX(IDGENTE), NOMBRE, FECHA FROM GENTE FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – SELECT Agrupar filas La cláusula HAVING permite hacer selecciones en situaciones en las que no es posible usar WHERE. SELECT IDGENTE, nombre, fecha, DATEDIFF(CURRENT_DATE(),fecha)/365 as 'edad‘ FROM gente GROUP BY NOMBRE HAVING EDAD>18; FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – SELECT Por último, la cláusula LIMIT permite limitar el número de filas devueltas: SELECT * FROM GENTE LIMIT 0,2; SELECT * FROM GENTE LIMIT 2,2; SELECT * FROM GENTE LIMIT 4,2; FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – Operador Asignación En MySQL podemos crear variables y usarlas posteriormente en expresiones. Para crear una variable hay dos posibilidades. La primera consiste en pulsar la sentencia SET de este modo: mysql> SET @hoy = CURRENT_DATE(); Query OK, 0 rows affected (0.02 sec) mysql> SELECT @hoy; La otra alternativa permite definir variables de usuario dentro de una sentencia SELECT: mysql> SELECT @x:=10; FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – Comparación MySQL Sigue las siguientes reglas a la hora de comparar valores: • Si uno o los dos valores a comparar son NULL, el resultado es NULL, excepto con el operador <=>, de comparación con NULL segura. • Si los dos valores de la comparación son cadenas, se comparan como cadenas. • Si ambos valores son enteros, se comparan como enteros. • Los valores hexadecimales se tratan como cadenas binarias, si no se comparan con un número. • Si uno de los valores es del tipo TIMESTAMP o DATETIME y el otro es una constante, la constantes se convierte a timestamp antes de que se lleve a cabo la comparación. Hay que tener en cuenta que esto no se hace para los argumentos de una expresión IN(). Para estar seguro, es mejor usar siempre cadenas completas datetime/date/time strings cuando se hacen comparaciones. • En el resto de los casos, los valores se comparan como números en coma flotante. El operador = compara dos expresiones, y da como resultado 1 si son iguales, o 0 si son diferentes. mysql> SELECT * FROM gente WHERE fecha="2001-12-02"; Operador de igualdad con NULL seguro El operador <=> funciona igual que el operador =, salvo que si en la comparación una o ambas de las expresiones es nula el resultado no es NULL. Si se comparan dos expresiones nulas, el resultado es verdadero: mysql> SELECT NULL <=> 1, NULL <=> NULL; FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – Comparación Operador de desigualdad MySQL dispone de dos operadores equivalente para comprobar desigualdades, <> y !=. Si las expresiones comparadas son diferentes, el resultado es verdadero, y si son iguales, el resultado es falso: mysql> SELECT 100 <> 32, 43 != 43; Disponemos de los cuatro operadores corrientes. Operador Descripcíón <= Menor o igual < Menor > Mayor >= Mayor o igual Operador de desigualdad Entre los operadores de MySQL, hay uno para comprobar si una expresión está comprendida en un determinado rango de valores. La sintaxis es: <expresión> BETWEEN mínimo AND máximo <expresión> NOT BETWEEN mínimo AND máximo mysql> SELECT 23 BETWEEN 1 AND 100; FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – JOIN Las composiciones internas usan estas sintaxis: referencia_tabla, referencia_tabla referencia_tabla [INNER | CROSS] JOIN referencia_tabla [condición] La condición puede ser: ON expresión_condicional | USING (lista_columnas) La coma y JOIN son equivalentes, y las palabras INNER y CROSS son opcionales. La condición en la cláusula ON puede ser cualquier expresión válida para una cláusula WHERE, de hecho, en la mayoría de los casos, son equivalentes. La cláusula USING nos permite usar una lista de atributos que deben ser iguales en las dos tablas a componer. FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional SQL – UNION También es posible realizar la operación de álgebra relacional unión entre varias tablas o proyecciones de tablas. Para hacerlo se usa la sentencia UNION que permite combinar varias sentencias SELECT para crear una única tabla de salida. Las condiciones para que se pueda crear una unión son las mismas que vimos al estudiar el álgebra relacional: las relaciones a unir deben tener el mismo número de atributos, y además deben ser de dominios compatibles. Referencias • Apuntes del Ing. Franco Menendez • Paper “A relational model for a large shared data banks”, E. F. Codd. ” – ACM – 1970. • “Procesamiento de bases de datos. fundamentos, diseño e implementación” - David M. Kroenke — (Tra. Ana Elizabeth García Hernández.--Rev. Tca. Juan Raúl Esparza Martínez). Pearson Educación – México - c.2003 – 8ª Edic. FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional Apuntes del Mg. Ing. Gustavo E. Juárez FRT -UTN Gestión de Datos Departamento Sistemas Facultad Regional Tucumán Universidad Tecnológica Nacional
Compartir