Tema 5 Fundamentos de Bases de Datos - SQL

SQL estructura. Principales sentencias.

1. Bases de Datos (BD)

2. Es un lenguaje de consulta y programación de bases de datos utilizado para la organización, acceso, consulta y gestión de bases de datos relacionales. Cliente Aplicación del Cliente API’s de la BD (OLEDB, ODBC, Microsoft Jet, etc.) Librería de Autentificación del Cliente Solicitud SQL Datos Server Database Management System (SGBD) Validación de Permisos ¿Qué es SQL?

3. Definición de Datos Estructura de la BD Organización de Datos Relaciones Recuperación de Datos Extracción de Datos Manipulación de Datos Permite la inserción, eliminación, modificación y actualización de los datos. Control de Acceso Control sobre los Permisos en los datos Compartimiento de Datos Coordina el acceso y la compartición de datos entre varios usuarios. Integridad de Datos Protege la BD de deterioros o errores causados por el sistema Funciones Principales de SQL en un SGBD

4. Un Sistema SQL puede implementarse como sistema cliente/servidor o como sistema independiente. El tipo de sistema que se diseñe dependerá del número de usuarios que vaya a acceder a la base de datos de manera simultanea y de la clase de trabajo que se vaya a realizar. Sistemas de SQL Server

5. Este sistema se puede configurar en dos o tres capas, independientemente de la configuración, software y bases de datos de SQL Server. Los usuarios tienen equipos independientes llamados clientes. Los usuarios acceden a la base de datos por medio de aplicaciones en sus equipos cliente. Sistema cliente/servidor

6. En los sistemas de dos capas los clientes ejecutan una aplicación desde su equipo y que accede al servidor de base de datos por medio de la red, por lo tanto el cliente ejecuta el código correspondiente para presentar los resultados al usuario. La configuración de 2 capas es útil cuando el número de usuarios es relativamente pequeño, dado que cada conexión de usuario requiere de recursos del sistema como memoria y bloqueos. Clientes SBD Sistema cliente/servidor

7. La configuración de tres capas involucra a un equipo llamado servidor de aplicaciones, en este sistema la única tarea del cliente es la ejecución de código para solicitar las funciones del servidor de aplicaciones y posteriormente la presentación de los resultados. El Servidor de aplicaciones ejecuta un programa que incluye el código específico, por lo que muchos usuarios pueden acceder a este programa de manera simultanea. El Servidor de aplicaciones se conecta con el servidor de base de datos para acceder a la aplicación y devolver los resultados al cliente. Clientes SA SBD Sistema cliente/servidor

8. SQL Server también se puede utilizar como servidor de base de datos independiente que se ejecuta en una PC o portátil. Las aplicaciones cliente se ejecutan en la misma máquina que almacena el motor de SQL Server y las bases de datos, por lo tanto no se realiza ninguna conexión de red del cliente al servidor. El sistema es útil cuando un único usuario accede a la base de datos o en los que unos pocos usuarios que comparten un equipo acceden a la base de datos en diferentes momentos. Sistema Independiente (Local)

9. Cada Base de Datos de SQL Server se crea a partir de un conjunto de archivos del sistema operativo. Archivos Un archivo de base de datos puede ser un archivo de datos o bien un archivo de registro. Archivos de Datos.- Se usan para almacenar datos y objetos como tablas, índices, vistas y procedimientos almacenados. Existen dos tipos de archivos de datos: Principales Secundarios Estructura de las Bases de Datos

10. Archivos de datos principal Contiene toda la información de inicio para la base de datos, tablas y objetos del sistema. Apunta al resto de archivos creados en la base de datos. También puede almacenar tablas y objetos definidos por el usuario. Cada base de datos debe tener un archivo principal con la extensión *.mdf Archivos de datos secundarios Estos archivos son opcionales. Pueden contener datos y objetos que no se encuentren en el archivo principal. Una base de datos podría no tener ningún archivo secundario si todos sus datos se encuentran en el archivo principal. Se puede tener más de un archivo secundario. La extensión de estos archivos son * . ndf Archivos de Registro de Transacciones Contiene toda la información de registro de transacciones utilizada para recuperar la base de datos. Toda base de datos debe tener al menos una archivo de registro La extensión de este tipo de archivo es * .ldf Estructura de las Bases de Datos

11. Las transacciones son comandos universales SQL que permiten realizar petición a un servidor de BD por medio de otras aplicaciones. Existen 3 tipos de Transacciones: Data Definition Lenguage (DDL) El lenguaje de definición de datos es utilizado para definir la estructura de la BD Data Control Lenguage (DCL) Son utilizadas para establecer los permisos sobre los elementos de una BD Data Manipulation Lenguage (DML) Son utilizadas para trabajar con los datos de las Bases de Datos como consultar, insertar, actualizar o la eliminación de datos. Tran sacciones SQL

12. Al realizar transacciones o instrucciones SQL se llegan a utilizar ciertos elementos que forman parte del uso del SQL como lo son: Los Nombres y Alias Batches Comentarios Tipos de Datos Variables Operadores Funciones Elementos de Sintaxis en SQL

13. Cuand o se escriben declaraciones de SQL, se utilizan nombres para referirse a bases de datos, tablas, columnas o sentencias reservadas de SQL. Pero también es posible crear alias los cuales funcionan como sinónimos para llamar a las tabla o campos. Pueden contener caracteres alfanuméricos, guión bajo y el $. No puede estar formado solamente de dígitos. Se deben incluir entre caracteres de comillas o ap ó strofes. No incluir signos especiales como diagonales, puntos y comas. El nombre no debe corresponder a una palabra reservada de SQL Cuando se utilice el nombre de una tabla o campo compuesto por espacios en blanco se deberá colocar el nombre entre [ ] o entre ‘’. Ejemplo: SELECT Nombre,[Fecha de Nacimiento] FROM Empleados Nombres y Alias

14. Un Batche esta formado por un conjunto de sentencias o transacciones SQL. GO Mediante la palabra GO, SQL marca el final de un batche y es enviado a procesar al Servidor de SQL. Ejemplo: Use Northwind SELECT * FROM Productname GO Batches

15. Los comentarios son cadenas de texto que no son tomados en cuenta durante la ejecución de sentencias. Existen 2 formas de colocar comentarios en SQL: Comentarios por Línea Para colocar un comentario en una línea, se deben colocar 2 (--) guiones antes del comentario, ya que SQL ignora todo el texto que se encuentre a la derecha de los guiones. Comentarios de Bloque Para generar un bloque de varias líneas de comentarios se utiliza los caracteres (/*) para marcar el inicio del comentario y los caracteres (*/) para finalizar el comentario Ejemplo: Use Ventas - - utiliza la BD Ventas SELECT * - - Selecciona todos los registros FROM Pedidos - - de la tabla pedidos Go - - Manda a hacer la instrucción Ejemplo: /* Este es un bloque de varias líneas que termina precisamente aquí */ Comentarios

16. Clasificación Tipos de Datos Descripción Números SMALLINT Números enteros pequeños INTEGER Números enteros BIT ( Longitud ) Cadena de Bits de longitud fija REAL Números con coma flotante de baja Precisión DOUBLE PRECICIÓN Números con coma flotante de doble Precisión FLOAT ( Precisión ) Números en coma flotante DECIMAL Números decimales y $ Cadenas de Texto CHAR ( Longitud ) Cadena de longitud fija CHARACTER VARCHAR ( Longitud ) Cadenas de longitud variable Fechas y Horas DATE Fechas TIME (Precisión) Horas INTERVAL Intervalo de Tiempo DATETIME Fecha y Hora Tipos de Datos

17. Dentro de SQL se pueden definir variables para almacenar valores temporales. Todas las variables deben iniciar con el símbolo @ Declaración de variables Asignar valores en las variables Devolver el valor de las variables Sintaxis: DECLARE @ variable Tipo de Dato Ejemplo: DECLARE @nombre varchar(15) Sintaxis: SET @ variable = valor Ejemplo: SET @nombre = ‘Juan Pérez’ Sintaxis: SELECT @ variable Ejemplo: SELECT @nombre Variables

18. Tipos de Operadores Aritméticos + - * / ^ Comparación > < <> = >= <= Concatenación + Lógicos And Or Not Operadores

19. COUNT Número de registros seleccionados POSITION ( Caracter IN Cadena ) SUM Suma de un campo SUM ( Campo ) AVG Promedio de un campo AVG ( Campo ) MAX Máximo de un campo MAX ( Campo ) MIN Mínimo de un campo MIN ( Campo ) Funciones

20. Son instrucciones que controlan la estructura de una BD con las cuales se pueden realizar las siguientes acciones: Crear Tablas Eliminar Tablas Modificar la estructura de la tabla Definir Vistas (Tablas Virtuales) Establecer parámetros de seguridad a la BD Creación de Índices para la aceleración el acceso a los datos. Control del almacenamiento de los datos Las transacciones DDL que se utilizan son: CREATE Nombre del Objeto ALTER Nombre del Objeto DROP Nombre del Objeto Transacciones DDL

21. Comando T-SQL: CREATE DATABASE Sintaxis: CREATE DATABASE [Nombre de BD] Ejemplo: create database cursosql on (name = MiDB, filename = 'C:\Archivos de programa\Microsoft SQL Server\MSSQL$SQLCEDEC1\Data\cursosql.mdf', size=8MB, Maxsize=9MB,filegrowth=100Kb) LOG ON (name = cursosql_reg, filename = 'C:\Archivos de programa\Microsoft SQL Server\MSSQL$SQLCEDEC1\Data\cursosql.ndf', size=1000MB, Maxsize=1500MB,filegrowth=100Mb) use cursosql go sp_helpdb [base de datos] Permite mostrar la información de los archivos de la base de datos y muestra el espacio asignado. Crear una Base de Datos (CREATE)

22. Para realizar una acción dentro de una Base de Datos, primero hay que decirle a MySQL que queremos trabajar con esa Base de Datos, para esto se utiliza el siguiente comando : Sentencia SQL: USE Sintaxis: USE Nombre de la BD Ejemplo: USE Animales Utilizar una BD

23. Comando T-SQL: CREATE TABLE Sintaxis: CREATE TABLE Nombre_Tabla ( Nombre_Campo1 Tipo_Dato[(Tamaño)] [NULL | NOT NULL] [DEFAULT Valor], Nombre_Campo2 Tipo_Dato[(Tamaño)] [NULL | NOT NULL] [DEFAULT Valor], etc… ) * Solo es necesario definir el tamaño cuando se utilizan los Tipos de Datos Char y Varchar. Ejemplo 1: CREATE TABLE MiTabla ( Matricula INT , Nombre VARCHAR(15), Edad INT ) Creación de una Tabla

24. Valores Ausentes y Valores Predeterminados Al momento de definir cada una de las columnas es posible determinar si el campo va a permitir valores nulos o no. Es posible definir para cada columna en caso de ser necesario, una valor que funcione como predeterminado al momento de ingresar registros a la tabla. CREATE TABLE oficinas (oficina integer NOT NULL, ciudad varchar(15) NOT NULL, region varchar(15) NOT NULL DEFAULT 'Este', jef e integer DEFAULT 106, objetivo money DEFAULT NULL, ventas money NOT NULL) Creación de una Tabla

25. Es posible eliminar una tabla de la base de datos, es importante considerar que al momento de que la tabla es eliminada todos los registros almacenados en dicha tabla, también serán eliminados. Al eliminar una tabla debe considerar si esta no se encuentra relacionada con alguna otra tabla, en caso de que así sea, la integridad en las relaciones se ver á afectada. DROP TABLE nombre_tabla Eliminación de una Tabla

26. En ocasiones será necesario alterar la definición de la estructura de las tablas, por lo que será posible modificar la tabla en los siguientes aspectos: Cambiar el tipo de datos para las columnas Establecer valores nulos o no para las columnas Especificar valores predeterminados Agregar columnas Eliminar columnas ALTER TABLE nombre_tabla ADD nueva_columna tipo_datos ALTER TABLE nombre_tabla DROP COLUMN nombre_columna ALTER TABLE MITABLA alter column matricula varchar(15) sp_rename 'MiTabla.Matricula','Credencial' go Modificación de una Tabla

27. Es posible que al momento de que muestre el resultado de la consulta, los nombres de los campos sean distintos. Lo anterior es útil cuando los nombres de los campos no son muy representativos para los usuarios finales. La asignación del Alias se hace a través de la cláusula AS seguido del nombre del alias o sobrenombre que se quiera asignar. SELECT idproducto AS [Código Producto], nombreproducto AS Producto from productos Asignar Alias a los Campos

28. La instrucción Select permite recuperar información de una base de datos y los devuelve en forma de resultados de la consulta. En la instrucción Select se especifican los elementos de datos que recupera la consulta. Los elementos se especifican a través de una lista de elementos separados por comas. Cada elemento de selección en la lista genera una única columna de resultados de la consulta, de izquierda a derecha. Un elemento de selección puede ser: Un nombre de columna Identifica una columna de la tabla o tablas que aparecen en la cláusula FROM SQL toma el valor de esa columna de cada fila de la tabla y lo muestra como resultado de la consulta Una constante Especifica que ese mismo valor de constante aparecerá en cada fila de los resultados de la consulta Una expresión Indica a SQL que debe calcular el valor a poner e n los resultados, según se especifica en la expresión Consultas Simples – SELECT

29. SELECT Lista las columnas que se van a recuperar de la BD. FROM Se indica el nombre de la tabla que contiene la información a recuperar. WHERE Se especifica una condición para los registros que debe recuperar SQL. GROUP BY Genera una columna donde agrupa los registros que tengan la misma información, generando un resumen. HAVING Indica a SQL que incluya en el resultado sólo determinados grupos producidos por la cláusula GROUP BY. ORDER BY Ordena los resultados tomando como base los datos de una o más columnas. Instrucción SELECT

30. Normalmente no se desea seleccionar sólo algunos registros de una tabla e incluir sólo estos en los resultados de la consulta. La cláusula WHERE se usa para especificar los registros que se desea recuperar. SELECT idpedido,idproducto,preciounidad FROM [detalles de pedidos] WHERE preciounidad >=100 SELECT idcliente,nombrecompañía,nombrecontacto FROM clientes WHERE nombrecontacto = 'Antonio Moreno' Selección de Filas (WHERE)

31. SQL ofrece una gran variedad de condiciones de búsqueda que permiten especificar muchos tipos diferentes de consulta de forma eficiente y natural. Existen cinco condiciones básicas de consulta: Comparación . Compara el valor de una expresión con el valor de otra expresión. Rango . comprueba si el valor de una expresión se encuentra en un rango especificado de valores Pertenencia a conjuntos . Comprueba si el valor de una expresión con uno de un conjunto de valores. Encaje de patrones . Comprueba si el valor de una columna que contiene datos de cadena coincide con un patrón especificado. Valores nulos . Comprueba si una columna tiene una valor NULL. Condiciones de Búsqueda

32. La condición de comparación es la más usual en una consulta de SQL, en dicha comparación SQL calcula y compara los valores de dos expresiones para cada fila de datos. Las expresiones pueden tan sencillas o complejas, dependiendo de lo que el usuario desea obtener en la consulta. SELECT * FROM productos WHERE unidadesenexistencia>=50 AND unidadesenexistencia<=100 SELECT * FROM clientes WHERE cargocontacto<>'propietario' Condiciones de Búsqueda (Comparación)

33. SQL proporciona una forma diferente de condición de búsqueda con el rango (BETWEEN), el cual comprueba si un valor se encuentra entre dos especificaciones. Implica tres expresiones SQL: La primera define el valor a comprobar La segunda el límite inferior del rango a comprobar La tercera el límite superior del rango a comprobar SELECT idpedido,idcliente,fechapedido,fechaentrega FROM pedidos WHERE idpedido BETWEEN 10300 AND 10400 SELECT idpedido,idcliente,fechapedido,fechaentrega FROM pedidos WHERE fechapedido BETWEEN '09-01-96' AND '09-30-96' mm /dd/aa Condiciones de Búsqueda (Rango)

34. La búsqueda de pertenencia a conjuntos (IN), comprueba si un valor de datos coincide con uno de una lista de posibles valores. SELECT idproducto, nombreproducto, preciounidad, unidadesenexistencia FROM productos WHERE unidadesenexistencia IN (0,5,10) SELECT idpedido,idcliente,fechapedido,fechaentrega FROM pedidos WHERE fechapedido IN ('05-01-97','06-01-97','07-01-97','08-01-97') Co ndiciones de Búsqueda (Pertenencia a Conjuntos)

35. La búsqueda de encaje de patrones, permite recuperar los registros que coincidan con un texto en concreto. SELECT idcliente,nombrecompañía,nombrecontacto FROM clientes WHERE nombrecompañía like 'S%' SELECT idcliente,nombrecompañía,nombrecontacto FROM clientes WHERE nombrecompañía like 'S%' or nombrecompañía like 'A%' SELECT * FROM mitabla WHERE nombre LIKE '_egador' SELECT * FROM mitabla WHERE nombre LIKE '_egador%' SELECT * FROM mitabla WHERE nombre LIKE '_egador_' Condiciones de Búsqueda (Encaje de Patrones)

36. Para una fila dada, el resultado de una condición de búsqueda puede ser TRUE o FALSE, o puede ser NULL por que una de las columnas usadas en la evaluación de la condición de búsqueda contenga un valor NULL SELECT nombrecompañía,cargocontacto,dirección, región, fax FROM clientes WHERE región IS NULL SELECT idcliente,fechapedido,regióndestinatario FROM pedidos WHERE idcliente='TOMSP' AND regióndestinatario IS NULL Condiciones de Búsqueda (Valores Nulos)

37. Una consulta que contiene GROUP BY se denomina consulta de agrupación por que agrupa los datos de las tablas fuente en una única fila de resumen por cada registro. SELECT idproveedor,sum(unidadesenexistencia) FROM productos group by idproveedor SELECT idproveedor,unidadesenexistencia FROM productos ORDER BY idproveedor COMPUTE sum(unidadesenexistencia) SELECT idproveedor,unidadesenexistencia FROM productos ORDER BY idproveedor COMPUTE sum(unidadesenexistencia) by idproveedor COMPUTE sum(unidadesenexistencia) Agrupación de Registros (Group By)

38. La cláusula HAVING permite establecer una condición en los registros agrupados de una consulta, para que SQL muestre sólo los registros que hayan cumplido con la condición. HAVING debe utilizarse en combinación con la cláusula GROUP BY. SELECT idproducto, idorden,cantidad FROM pedidos idproducto Idorden cantidad 1 1 5 1 1 10 2 1 10 2 2 25 3 1 15 3 2 30 SELECT idproducto, sum(cantidad) as Total FROM pedidos GROUP BY idproducto HAVING sum(cantidad)>=30 idproducto Total 2 35 3 45 Uso de la Cláusula HAVING en GROUP BY

39. En caso de que en la tabla se tenga especificado un campo como clave principal, todos los registros de la tabla en ese campo tendrá información diferente, ya que la clave principal por naturaleza impide tener registros duplicados en el campo de clave principal. Si la clave principal no se incluye, la tabla podrá tener registros con información duplicada. Para no mostrar los registros con información duplicada será necesario insertar la palabra DISTINCT en la instrucción SELECT a n tes de la lista de selección. SELECT DISTINCT fechapedido FROM pedidos SELECT DISTINCT cargo FROM empleados SELECT DISTINCT cargo, región FROM empleados Registros Duplicados (DISTINCT)

40. Una consulta generada por la cláusula SELECT puede presentar los resultados de manera organizada tanto numéricamente y alfabéticamente. Cláusula T-SQL: ORDER BY Sintaxis: SELECT [Campos] FROM [Tabla] ORDER BY [Campos] [Tipo de Ordenación] Los tipos de ordenación pueden ser: ASC - Ordenación Ascendente DESC - Ordenación Descendente SELECT * FROM empleados ORDER BY idempleado desc SELECT * FROM empleados ORDER BY cargo asc Ordenación de Registros (ORDER BY)

41. A través de la cláusula TOP, SQL permite mostrar los registros que contengan los valores m á s superiores o inferiores de la consulta. Para especificar que sean los primeros o últimos se agrega la cláusula WITH TIES después de la cláusula TOP. SELECT TOP 3 * FROM productos order by unidadesenexistencia desc SELECT TOP 3 * FROM productos Order BY unidadesenexistencia asc SELECT TOP 3 with ties * FROM productos ORDER BY unidadesenexistencia asc Mostrar los Valores Superiores/Inferiores

42. Los resultados de una consulta vienen directamente de la bas de datos, una consulta de SQL puede incluir columnas calculadas, cuyos valores se obtiene a partir de los valores almacenados. Para solicitar una columna calculada se hace a través de una expresión Las expresiones de SQL pueden incluir la suma, resta, multiplicación y la división. Las columnas referenciadas en una expresión aritmética deben tener un tipo numérico. select idpedido, idproducto, (preciounidad*cantidad) as 'Total de Venta' from [detalles de pedidos] Consultas con Columnas Calculadas

43. A través de la cláusula INNER JOIN será posible realizar consultas entre dos o más tablas. Para realizar consultas entre varias tablas, es recomendable asignar Alias haciendo uso de la cláusula AS para nombrar las tablas. Select idpedido, idproducto,producto,cantidad FROM pedidos INNER JOIN productos ON pedidos.idproducto=productos.idproducto GO Select idpedido, idproducto,producto,cantidad FROM pedidos AS pe INNER JOIN productos AS pr ON pe.idproducto=pr.idproducto GO Consultas de Varias Tablas