El Analizador de Consultas
-
Upload
linoquispe -
Category
Documents
-
view
24 -
download
1
description
Transcript of El Analizador de Consultas
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
EL ANALIZADOR DE CONSULTAS El Analizador de consultas es una aplicacion desde la cual podrá ejecutar directamente cualquier instrucción o secuencia de instrucciones SQL contra una base de datos existente en cualquier servidor disponible.Al entrar en el Analizador, se abre una pantalla previa que brindala posibilidad de establecer una conexión con un servidor (Se puede utilizar el Analizador de consultas SQL para mostrar una herramienta basada en una interfaz gráfica de usuario donde se pueden ejecutar instrucciones T-SQL.
Para ejecutar el Analizador de consultas SQL hay que seguir los siguientes pasos:
1. En el Menu Inicio elija Programas y luego Elija Microsoft SQL SERVER y Analizador de Consultas
2. Seleccione el servidor y luego ingrese el nombre de usuario y contraseña para este Ejemplo ingrese en el nombre de usuario sa y deje la contraseña en blanco
3. A continuacion pulse el boton Aceptar
1
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
Una vez conectado, el Analizador de consultas despliega su ventana principal.
2
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
CREAR UNA BASE DE DATOS UTILIZANDO EL ANALIZADOR DE CONSULTAS
EL proceso es muy sencillo, como ejemplo creamos una base de datos llamada Ventas con un tamaño de 10 MB y limitada a 50 MB y un incremento de 5 MB. El registro de transacciones lo creamos con un tamaño de 5MB y limitado a 25 y un incremento de la base de datos de 5 MB
COMANDO CREATE DATABASE
1. En el analizador de consultas en el panel de comandos escriba lo siguiente luego seleccione el texto y pulse F5 para Ejecutar las instrucciones
/*Abrir la base de datos Master*/USE master
2. Ahora crearemos la base de Datos Ventas
/*Crear la base de datos con el comando Create database*/CREATE DATABASE VENTASON
/*Generando el archivo de datos*/( NAME = ventas_data,FILENAME = 'c:\program files\microsoft sql server\mssql\data\ventas_data.mdf',SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 )
/*Generando el archivo de registro*/LOG ON( NAME = Pruebas_log',FILENAME = 'c:\program files\microsoft sql server\mssql\data\ventas_log.ldf',SIZE = 5MB,MAXSIZE = 25MB,FILEGROWTH = 5MB )-----------------------------------------------------------------------------------
3. Seleccione todas las instrucciones y pulse F5
ABRIR SU BASE DE DATOS1. Escribe el comando siguiente :
USE VENTAS2. Seleccione el comando y pulse F5
Sintaxis del comando Create Database:
3
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
NAME = Nombre_del_archivo_lógico,FILENAME = Nombre_del_archivo_en_el_sistema (path completo)SIZE = TAMAÑO (inicial)MAXSIZE = (tamaño_máximo | UNLIMITED) (Tamaño máximo que puede tener la base de datos, UNLIMITED = tamaño ilimitado)FILEGROWTH = Incremento del archivo (crecimiento en MB)
MODIFICAR UNA BASE DE DATOS CREADA
COMANDO ALTER DATABASE
Añade o elimina archivos o grupos de archivos de una base de datos. Se puede usar también para modificar las propiedades de archivos y grupos de archivos
CREATE DATABASE BDEMPRESA ON( NAME = Emp_dat1, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Emp_dat1.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)GO ALTER DATABASE BDEMPRESAADD FILE ( NAME = Emp_dat2, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Emp_dat2.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)GO
AÑADIR UN GRUPO DE ARCHIVOS A LA BASE DE DATOS
USE masterGO
ALTER DATABASE VENTASADD FILEGROUP GP_VENTASGO ALTER DATABASE VENTASADD FILE ( NAME = Ventas_dat3, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Ventas_dat3.ndf', SIZE = 5MB, MAXSIZE = 100MB,
4
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
FILEGROWTH = 5MB),( NAME =ventas_dat4, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Ventas_dat4.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)TO FILEGROUP GP_VENTAS ALTER DATABASE BDEMPRESAMODIFY FILEGROUP GPVENTAS DEFAULT
-----------------------------------------------------------------------------------------------------------------------------
AÑADIR ARCHIVOS LOG A LA BASE DE DATOS
USE masterGO
ALTER DATABASE BDEMPRESA ADD LOG FILE ( NAME = Emp_log2, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Emp_log2.ldf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB),( NAME =Fact_log3, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Emp_log3.ldf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)GO
ELIMINAR FICHEROS DE LA BASE DE DATOS
USE masterGO
ALTER DATABASE DBEMPRESAREMOVE FILE Emp_dat4GO
MODIFICAR UN ARCHIVO DE DATOS
USE masterGOALTER DATABASE BDEMPRESAMODIFY FILE (NAME = Emp_dat3, SIZE = 20MB)GO
5
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
CONVERTIR UN GRUPO DE ARCHIVOS EN GRUPO POR DEFECTO
USE masterGOALTER DATABASE BDEMPRESA MODIFY FILEGROUP GPVENTAS DEFAULTGO
CAMBIAR EL NOMBRE A UNA BASE DE DATOS
El comando Alter Database no permite cambiar el nombre a una base de datos en su lugar utilice el procedimiento almacenado SP_RENAMEDB
Este ejemplo cambia el nombre de la base de datos BDEMPRESA por BDNEPTUNO
EXEC sp_renamedb 'BDEMPRESA', 'BDNEPTUNO'
CAMBIAR UBICACIÓN DE BASES DE DATOS SQL SERVER DEL SERVIDOR
1. Mover Bases de Datos de Usuarios2. Mover master
Cada base de datos de SQL Server tiene al menos dos archivos:
El archivo de datos que tiene extensión mdf. El archivo de transacciones que tiene extensión ldf.
Estos dos archivos se encuentran en "C:\Archivos de Progama\Microsoft SQL Server\MSSQL\Data". Si por algún motivo necesitamos cambiar la ubicación de estos archivos a otra carpeta o a otro disco tenemos que realizar un proceso sencillo pero laborioso. Vamos a ver paso a paso como realizar este cambio de ubicación de los ficheros de las bases de datos.
1. Para mover la ubicación de los archivos de nuestras bases de datos vamos a suponer que hemos realizado una instalación por defecto del SQL Server, es decir, las bases de datos se encuentran en la carpeta "C:\Archivos de Progama\Microsoft SQL Server\MSSQL\Data", y queremos llevarlas a un disco distinto, por ejemplo a "D:\"
2. El primer paso es realizar una copia de seguridad de TODOS los datos y TODAS las bases de datos del servidor (master incluida por supuesto) puesto que estos cambios entrañan peligro para el propio servidor.
3. Ahora veamos como mover todas las bases de datos una por una.
6
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
MOVER BASES DE DATOS DE USUARIOS
4. A continuación vamos a mover las bases de datos de usuarios. Si tenemos una base de datos llamada "Pruebas" en el analizador de consultas ejecutamos el siguiente script para separar la base de datos del servidor
USE MASTERGOSP_DETACH_DB 'PRUEBAS'GO
5. Lo siguiente es mover los archivos de esta base de datos (pruebas.mdf y pruebas.ldf) a la carpeta destino ("D:\") Y por último volvemos a adjuntar la base de datos en su ubicación actual.
USE MASTERGOSP_ATTACH_DB 'PRUEBAS','D:\PRUEBAS.MDF','D:\PRUEBAS.LDF'GO
Y para ver que todo ha ido bien.
SP_HELPDB 'PRUEBAS' Ahora hay que repetir este procedimiento para todas las bases de datos de
usuario que tengamos
MOVER MASTER
1. Abrimos el Administrador Corporativo 2. Pulsamos con el botón derecho en el servidor y sacamos la ventana de
propiedades 3. Pulsamos clic en parámetros de inicio y vemos que hay las siguientes entradas ------------------------------------------------------------------------------------------------------------------------
4. -dC:\Archivos de Progama\Microsoft SQL Server\MSSQL\Data\master.mdf5. -eC:\Archivos de Progama\Microsoft SQL Server\MSSQL\log\ErrorLog6. -lC:\Archivos de Progama\Microsoft SQL Server\MSSQL\Data\mastlog.ldf------------------------------------------------------------------------------------------------------------------------
7. Y podemos cambiar los relacionados con master por ------------------------------------------------------------------------------------------------------------------------8. -dD:\master.mdf9. -lD:\mastlog.ldf------------------------------------------------------------------------------------------------------------------------
10. También podemos cambiar de la misma manera la ubicación de los registros de error
11. Detenemos el SQL Server
7
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
12. Copiamos "master.mdf" y "masterlog.ldf" a la nueva localización 13. Reiniciamos el SQL Server
Con esto debería estar todo listo y nuestro servidor debería funcionar perfectamente pero ahora con todos los ficheros de bases de datos en "D:\" como queríamos.
Sólo recordar una cosa más. Estos cambios son una operación de alto riesgo y tener copias de seguridad de TODO antes de empezar es imprescindible.
CREACIÓN DE TABLAS
Para crear una tabla en su base de datos utilice el comando CREATE TABLE
COMANDO CREATE TABLE
CREATE TABLE Empleados (Nombre VARCHAR (25), Apellidos VARCHAR (50))
(Crea una nueva tabla llamada Empleados con dos campos, uno llamado Nombre de tipo VarChar y longitud 25 y otro llamado apellidos con longitud 50).
CREATE TABLE Empleados ( Nombre VARCHAR (10), Apellidos VARCHAR, FechaNacimiento DATETIME )
CONSTRAINT Índicegeneral UNIQUE ( Nombre, Apellidos, FechaNacimiento)
Resultado:
Tabla Empleados
Nombre Apellidos FechaNacimiento
(Crea una nueva tabla llamada Empleados con un campo Nombre de tipo texto (Varchar) y longitud 10, otro con llamado Apellidos de tipo texto (Varchar) y longitud predeterminada (50) y uno más llamado FechaNacimiento de tipo Fecha/Hora.(Datetime) También crea un índice único - no permite valores repetidos - formado por los tres campos.)
8
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
EJEMPLOS DEL COMANDO CREATE TABLE
GENERAR UNA TABLA CON UN CAMPO AUTONUMERICO
El valor IDENTITY define que el campo Idalumno es autonumerico y empieza en el valor 100 y se incrementara de 1 en 1
PRIMARY KEY que el campo IDALUMNO es Clave Primaria de la tabla NOT NULL El ingreso de este dato es obligatorio
CREATE TABLE ALUMNOS(IDALUMNO INT NOT NULL IDENTITY(100,1) PRIMARY KEY, NOMBRE VARCHAR(40) NOT NULL, APELLIDOS VARCHAR(40) NOT NULL, SEXO BIT NOT NULL, FECHANACIMIENTO DATETIME NOT NULL)
GENERAR UN CAMPO AUTOGENERADO
Se puede generar un campo que sera el producto de una operación entre otros campos Por ejemplo PROMEDIO es el producto del calculo de la suma de N1+N2+N3
CREATE TABLE NOTAS(IDREGISTRO INT NOT NULL IDENTITY(1,1) PRIMARY KEY, IDALUMNO INT NOT NULL, CURSO VARCHAR(40) NOT NULL, N1 DECIMAL NOT NULL, N2 DECIMAL NOT NULL, N3 DECIMAL NOT NULL, PROMEDIO AS N1+N2+N3, APROBADO BIT NOT NULL)
GENERAR UN VALOR PREDETERMINADO PARA UN CAMPO CREATE TABLE CURSOS(IDCURSO INT NOT NULL IDENTITY(1,1) PRIMARY KEY, NOMBRECURSO VARCHAR(35) NOT NULL, COSTO MONEY DEFAULT(200))
GENERAR UNA REGLA PARA UN CAMPO DE UNA TABLA
CREATE TABLE MATRICULAS(IDMATRICULA INT NOT NULL PRIMARY KEY , FECHAMATRICULA DATETIME NOT NULL, PAGO MONEY CHECK (PAGO>100))
9
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
UTILIZAR EL TIPO DE DATOS UNIQUEIDENTIFIER EN UNA COLUMNA
Este ejemplo crea una tabla con una columna UNIQUEIDENTIFIER. Utiliza una restricción PRIMARY KEY para impedir que los usuarios inserten valores duplicados y utiliza la función NEWID() de la restricción DEFAULT para proporcionar valores para las nuevas filas.
CREATE TABLE PROFESORES (IDPROFESOR UNIQUEIDENTIFIER CONSTRAINT Guid_Default DEFAULT NEWID(), NOMBRES VARCHAR(60), CONSTRAINT Guid_PK PRIMARY KEY (IDPROFESOR)
CREAR UNA TABLA CON UNA CLAVE PRIMARIA COMPUESTA
CREATE TABLE [DETALLES DE PEDIDOS](IDPEDIDO INT ,IDPRODUCTO INT ,CANTIDAD INT ,PRECIOUNIDAD MONEYPRIMARY KEY(IDPEDIDO,IDPRODUCTO))
CREAR UNA TABLA EN UN GRUPO DE ARCHIVOS
Este ejemplo crea la tabla NOTAS en un grupo GP_EVALUACIONES de la base de datos BDCOLEGIO CREADA ANTERIORMENTE
CREATE TABLE NOTAS(IDREGISTRO INT NOT NULL IDENTITY(1,1) PRIMARY KEY, IDALUMNO INT NOT NULL, CURSO VARCHAR(40) NOT NULL, N1 DECIMAL NOT NULL, N2 DECIMAL NOT NULL, N3 DECIMAL NOT NULL, PROMEDIO AS N1+N2+N3, APROBADO BIT NOT NULL) ON GP_EVALUACIONES
Este ejemplo crea una tabla MATRICULAS y lo añade al grupo existente GP_MATRICULAS
CREATE TABLE MATRICULAS(IDREGISTRO INT NOT NULL IDENTITY(1,1) PRIMARY KEY, IDALUMNO INT NOT NULL, CURSO VARCHAR(40) NOT NULL, FECHAMATRICULA DATETIME NOT NULL, PROMEDIO AS N1+N2+N3) ON GP_MATRICULAS
10
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
COMO CREAR TABLAS ESTABLECIENDO RELACION
1. Vamos a desarrollar el siguiente ejemplo de relacion de tablas para ello usaremos el Analizador de Consultas.El objetivo sera relacionar las tablas CATEGORIAS,PROVEEDORES Y PRODUCTOS aplicando la ACTUALIZACION Y ELIMINACION en CASCADA.
Este ejemplo asume que las tablas CATEGORIAS Y PROVEEDORES ya existen
------------------------------------------------------------------------------------------------------------------
CREATE TABLE PRODUCTOS (IDPRODUCTO INT IDENTITY NOT NULL PRIMARY KEY,NOMBREPRODUCTO VARCHAR(50), IDPROVEEDOR INT REFERENCES PROVEEDORES(IDPROVEEDOR) ON UPDATE CASCADE ON DELETE CASCADE, IDCATEGORIA INT REFERENCES CATEGORIAS(IDCATEGORÍA) ON UPDATE CASCADE ON DELETE CASCADE,CANTIDADPORUNIDAD INT,PRECIOUNIDAD MONEY,UNIDADESENEXISTENCIA INT,UNIDADESENPEDIDO INT,NIVELNUEVOPEDIDO TINYNT,SUSPENDIDO BIT)
-------------------------------------------------------------------------------------------------------------------------2. En este segundo ejemplo se crea una tabla PEDIDOS la cual se relaciona con las
tablas CLIENTES y EMPLEADOS que ya existen
CREATE TABLE PEDIDOS(IDPEDIDO INT IDENTITY NOT NULL PRIMARY KEY,FECHAPEDIDO, IDCLIENTE INT REFERENCES CLIENTES(IDCLIENTE) ON UPDATE CASCADE ON DELETE CASCADE, IDEMPLEADO INT REFERENCES EMPLEADOS(IDEMPLEADO) ON UPDATE CASCADE ON DELETE CASCADE,FECHAENVIO DATETIME,CARGO MONEY,UNIDADESENEXISTENCIA INT,
12
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
UNIDADESENPEDIDO INT,NIVELNUEVOPEDIDO TINYNT,SUSPENDIDO BIT)
PARA COMPROBAR LA ESTRUCTURA DE LA NUEVA TABLA UTILICE EL PROCEDIMIENTO ALMACENADO SP_HELP
EJEMPLO : /*CURSOS es el nombrede la tabla*/
SP_HELP CURSOS
EL COMANDO DROP TABLE
Elimina una tabla y todos sus datos, índices, disparadores, restricciones y permisos especificados para esa tabla. Cualquier vista o procedimiento almacenado que referencia dicha tabla debe ser explícitamente borrado, la instrucciñón DROP TABLE no lo hace.
DROP TABLE MATRICULAS
/* Elimina La Tabla Matriculas */
13
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
MODIFICAR TABLAS DE LA BASE DE DATOSEntendemos por modificar una tabla, cambiar su estructura, es decir, añadir atributos, borrarlos, o cambiar la definición. La sentencia que permite modificar una tabla es la que muestra
LA SENTENCIA ALTER TABLE
Su sintaxis es la descrita en el Código fuente
ALTER TABLE tabla ADD atrib tipo NULL
Ejemplos Añadir una nueva columna a una tabla
CREATE TABLE CONTACTOS ( TIPO AS VARCHAR(20),DIRECCION VARCHAR(50)) GOALTER TABLE CONTACTOS ADD NOMBRECONTACTO VARCHAR(20) NOT NULLGO
Eliminar una columna de una tabla
ALTER TABLE CONTACTOS DROP COLUMN DIRECCION Añadir una nueva columna con una restricción
ALTER TABLE CONTACTOS ADD EDAD TINYINT NULL CONSTRAINT ED_unique UNIQUE
Añadir una restricción no validada a una tabla
ALTER TABLE CONTACTOS WITH NOCHECK ADD CONSTRAINT Edad_check CHECK (EDAD > 1)GO
Añadir varias columnas con restricciones
ALTER TABLE CONTACTOS ADD /* Añadir una columna como clave primaria */ IDCONTACTO INT IDENTITY CONSTRAINT Idcontacto_pk PRIMARY KEY,
/* Añadir una columna con una restricción de comprobacion */CIUDAD VARCHAR(16) NULL CONSTRAINT column_d_chkCHECK (column_d IS NULL OR
column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" ORcolumn_d LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),
/* Añadir una columna con valor por defecto */ column_e DECIMAL(3,3) CONSTRAINT column_e_default DEFAULT .081GO
14
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
Añadir una columna con valor por defecto y NULL
ALTER TABLE VIDEOSADD FECHAESTRENO smalldatetime NULLCONSTRAINT FECHA_C1 DEFAULT getdate() WITH VALUES
Deshabilitar y habilitar una restricción
CREATE TABLE empleado (id INT NOT NULL, nombre VARCHAR(10) NOT NULL, salario MONEY NOT NULL CONSTRAINT salario CHECK (salario < 100000)) -- Inserciones validasINSERT INTO empleado VALUES (1,"Joe Brown",65000)INSERT INTO empleado VALUES (2,"Mary Smith",75000) -- Inserción que viola la restricciónINSERT INTO empleado VALUES (3,"Pat Jones",105000) -- Deshabilitar la restricciónALTER TABLE empleado NOCHECK CONSTRAINT salarioINSERT INTO empleado VALUES (3,"Pat Jones",105000) -- Habilitar la restricciónALTER TABLE empleado CHECK CONSTRAINT salarioINSERT INTO empleado VALUES (4,"Eric James",110000)
15
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
CREACIÓN DE ÍNDICES
La creación de índices en SQL Server, así como en la mayoría de los SGBDR existentes, se debe realizar junto con la creación de la estructura de las tablas. De este modo se evitan posibles colisiones que pueden surgir al crear índices cuando la tabla ya tiene datos. Por ejemplo, si creamos un índice único por un campo, esto es no puede admitir duplicados, y se encuentran valores no únicos, la generación del índice daría un error. Sin embargo, SQL Server permite la creación de índices, aunque la base de datos esté cargada.
ALTER TABLE TABLA ADD CONSTRAINT K1 PRIMARY KEY (COD1, COD2)
Esta sentencia permite añadir una clave primaria en tabla, por los campos cod1 y cod2.
Para crear un índice en la tabla todos, denominado Código, por el campo cod_cliente, se debe especificar el Código fuente
CREATE INDEX CODIGO ON TODOS (COD_CLIENTE)
Sí además queremos que el índice no admita valores nulos, se debe ejecutar el Código fuente
CREATE UNIQUE INDEX codigo ON todos (cod) WITH IGNORE_DUP_KEY
La sentencia que se encarga de borrar un índice, se muestra en el Código fuente Esta sentencia se encarga de borrar el índice código creado anteriormente.
DROP INDEX codigo
Mas Ejemplos de indices con la siguiente tabla
La tabla EMPLEADOS ya dispone de un indice que se genera automaticamente cuando se crea la clave Primaria vamos a añadir dos indices mas.
1. CREATE INDEX IDX_NOMB ON EMPLEADOS(NOMBRE)2. CREATE INDEX IDX_APE ON EMPLEADOS(APELLIDOS)
Si desea hacer una selección de los registros de la tabla Empleados utilizando uno de sus indices creados en el ejemplo anterior haga lo siguiente:
SELECT IDEMPLEADO,NOMBRE,APELLIDOS FROM EMPLEADOS(INDEX=2)
SELECT IDEMPLEADO,NOMBRE,APELLIDOS FROM EMPLEADOS(INDEX=3)
El numero 2 hace referencia al indice por el campo NOMBRE y el numero 3 por el campo APELLIDOS .El numero 1 esta reservado para el IDPRODUCTO que es la clave primaria
Para obtener informacion acerca de los indices que tiene su tabla ejecute el procedimiento almacenado SP_HELPINDEX Ejemplo:
16
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
SP_HELPINDEX empleados /*empleados es el nombre de la tabla
17
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
EL LENGUAJE DE MANIPULACIÓN DE DATOS (DML)
Ya se ha visto en un capítulo anterior el lenguaje de definición de datos (DDL), que es el que permite definir y modificar la estructura de un esquema. Veremos a continuación el otro lenguaje, el de manipulación de datos, que nos permite, como su propio nombre indica, manejar los datos contenidos en el esquema.
LA SENTENCIA INSERT
La otra gran sentencia de manipulación de datos es INSERT. Si SELECT nos permitía recuperar datos, INSERT nos va a permitir añadirlos al esquema, es decir, con esta sentencia podemos añadir información a la base de datos. Recordemos que estamos en el modelo relacional, por lo que la información se añadirá a una tabla en forma de filas. Si sólo queremos insertar un valor para un atributo, el resto de los de la tabla deberá contener el valor nulo (NULL). Sin embargo, habrá ciertas ocasiones en que esto no será posible, cuando el atributo esté definido como NO NULO, en cuyo caso deberemos especificar un valor para éste. La sintaxis de esta sentencia es:
INSERT INTO tabla (atributos)VALUES (valores)
Donde tabla especifica la tabla en la cual se añadirá la fila, atributos es una lista de atributos separados por comas que determinan los atributos para los cuales se darán valores, y valores específicos los valores que se darán para estos atributos, separados por comas.
Por ejemplo, si queremos añadir un nuevo cliente a nuestra base de datos, deberemos ejecutar el Código.INSERT INTO clientes (idcliente,nombre,apellidos)VALUES ('409-99-9876', 'Pepe', 'Perez')
Destacar que si el valor a introducir es alfanumérico, deberá ir encerrado entre comillas, mientras que si es numérico no. Pues bien, si ejecutamos la anterior sentencia, obtenemos el siguiente error:
Server: Msg 515, Level 16, State 2, Line 1Cannot insert the value NULL into column 'idcontacto', table'pubs.dbo.authors'; column does not allow nulls. INSERT fails.The statement has been terminated.
La razón es que no hemos dado valor al atributo idcontacto, que ha sido definido como no nulo. Por lo tanto, rectificamos el Código fuente, para dar un valor al Código fuente 56.
INSERT INTO authors (idcliente,nombre, apellidos,idcontacto)VALUES ('409-99-9876', 'Pepe', 'Perez', 1)
18
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
LA SENTENCIA UPDATE
El objetivo de la sentencia UPDATE es actualizar los valores de una o varias filas de una tabla, sin necesidad de borrarla e insertarla de nuevo. La sintaxis es la siguiente:
UPDATE tabla SET atributo1 = valor1 , atributo2 = valor2, ...
WHERE condición donde tabla especifica la tabla donde se encuentran las filas que queremos actualizar, condición especifica la condición que se debe cumplir para actualizar las filas, y lo que viene a continuación de
SET especifica la asignación de los nuevos valores a los atributos. Por lo tanto se actualizarán todas las filas que cumplan la condición especificada. Si queremos cambiar el nombre al cliente que hemos insertado en el anterior apartado, deberemos escribir el Código fuente :
Elevar los precios de los productos en 10% pero solo aquellos que pertenezcan a la Categoria 2 (Bebidas)
UPDATE PRODUCTOSSET PRECIOUNIDAD = PRECIOUNIDAD+(PRECIOUNIDAD*010)WHERE IDCATEGORIA=2
Lo que hacemos con la anterior sentencia es incrementar en 10% el preciounidad de los productos pero solo aquellos de la idcategoria =2(condición where) cuyo codigo pertenece a la Categoria bebidas, Si ejecutamos la anterior sentencia, obtenemos el resultado:
(32 row(s) affected)
Lo que quiere decir que la fila ha sido actualizada con éxito. Podemos comprobarlo ejecutando el Código fuente
SELECT * FROM Productos WHERE idcategoria = 2
LA SENTENCIA DELETE
El objeto de la sentencia DELETE es el de borrar filas de una tabla. Para poder borrar filas en una tabla se deben cumplir las condiciones de seguridad determinadas por el administrador y deben de cumplirse también las reglas de integridad referencial. La sintaxis es la siguiente:
DELETE FROM tablaWHERE condición
Donde tabla especifica la tabla sobre la cual queremos borrar las filas, y condición especifica la condición que se debe cumplir para que se borren las filas. Si omitimos la
19
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
condición, se borrarán todas las filas de la tabla, es decir, la sentencia que aparece en el Código fuente borra todas las filas de la tabla Pedidos
DELETE FROM PEDIDOS
Por ejemplo, si queremos borrar una fila que hemos creado en la tabla Pedidos, deberemos ejecutar el Código fuente obteniendo el siguiente resultado:
(1 row(s) affected)
DELETE FROM PEDIDOSWHERE IDPEDIDO = 11077
lo que viene a decir que la fila se ha borrado. Para comprobarlo, ejecutamos la sentencia que muestra el Código fuente. cuyo resultado es: (0 row(s) affected), lo que quiere decir que la fila no se encuentra en la tabla, es decir, ha sido borrada.
SELECT * FROM PEDIDOS WHERE IDPEDIDO = 11077
20
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
BASE DE DATOS SUPERMERCADOSNEPTUNOPara seguir con los ejemplos del uso del Transact SQL utilizaremos la siguiente base de datos a la cual llamaremos SUPERMERCADOSNEPTUNO una base de datos creada como ejemplo que es muy similar a la base de datos Northwind que viene con SQL SERVER pero que utiliza los campos en nombre en español para hacer que los ejemplos que se desarrollen en este manual sean mas ilustrativos y didacticos a continucion presentamos el diseño en el siguiente diagrama
22
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
A continuacion presentamos los comandos TRANSACT-SQL para poder crearla.
CREATE TABLE Empleados (IdEmpleado [int] NOT NULL IDENTITY PRIMARY KEY,Apellidos varchar(20),Nombre varchar (10)Cargo varchar(30),FechaNacimiento smalldatetime, FechaContratacion smalldatetime ,Direccion varchar(60),Ciudad varchar(15),Pais varchar(15),Fono varchar(24),Foto image NULL
CREATE TABLE Clientes(IdCliente varchar(5)INT NOT NULL PRIMARY KEY,NombreCompañía varchar(40),NombreContacto varchar(30),CargoContacto varchar(30),Dirección varchar(60),Ciudad varchar(15),País varchar(15),Teléfono varchar(24),Fax varchar (24))
CREATE TABLE Categorías (IdCategoría int NOT NULL IDENTITY Primary key,NombreCategoría varchar(15),Descripción text)
CREATE TABLE Proveedores(IdProveedor int NOT NULL IDENTITY PRIMARY KEY,NombreCompañía varchar(40),Dirección varchar(60),Ciudad varchar(15),Teléfono varchar(24)
CREATE TABLE Productos(IdProducto int not null identity primary key,NombreProducto varchar(35),idproveedor REFERENCES Proveedores(idProveedor) ON UPDATE CASCADE ON DELETE CASCADE,idCategoria REFERENCES Categorias(idcategoria) ON UPDATE CASCADE ON DELETE
CASCADE
23
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
CREATE TABLE Pedidos(idpedido int not null identity primary key,idcliente varchar(6) REFERENCES Clientes(idcliente) ON UPDATE CASCADE ON DELETE CASCADE,idempleado int REFERENCES Empleados(idempleado) ON UPDATE CASCADE ON DELETE CASCADE,fechaPedido smallDatetime,FechaEnvio smallDatetime,FechaEntrega smallDatetime,cargo as varchar(25),Destinatario varchar(35),DireccionDestinatario varchar(50),PaisDestinatario varchar(35))
CREATE TABLE [Detalles de pedidos](IDPEDIDO INT NOT NULL REFERENCES PEDIDOS(IDPEDIDO) ON UPDATE CASCADE ON DELETE CASCADE,IDPRODUCTO INT NOT NULL REFERENCES PRODUCTOS(IDPRODUCTO) ON UPDATE CASCADE ON DELETE CASCADE,PRECIOUNIDAD MONEY,CANTIDAD INT PRIMARY KEY(IDPEDIDO,IDPRODUCTO))
24
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
MUESTRA DEL CONTENIDO REGISTROS DE CADA TABLA
25
EMPLEADOS
CATEGORIAS
CLIENTES
PROVEEDORES
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
LA SENTENCIA SELECT
La sentencia Select es una sentencia SQL, que pertenece al conjunto del Lenguaje de Manipulación de Datos, y que sirve para recuperar registros de una o varias tablas, de una o varias bases de datos. Su sintaxis es la siguiente:
SELECT <atributos> FROM <tablas>[WHERE <condicion>][GROUP BY <atributos>][HAVING <condición>][ORDER BY <atributos>]
Donde las mayúsculas representan palabras reservadas, y lo encerrado entre corchetes es opcional, puede ser omitido. Una vez vista la anterior forma de representación, vamos a detenernos en la sintaxis de la sentencia Select. Se compone de tres partes:
SELECT <atributos>: permite hacer una proyección de las tablas, es decir, seleccionar los campos que deseamos recuperar de la base de datos, separados por comas. Si se especifica el símbolo *, se obtendrán todos los campos de la tabla.
FROM <tablas>: permite especificar la tabla de la cual se desean obtener los datos. Si se especifica más de una tabla, éstas irán separadas por comas.
WHERE <condición>: permite establecer una condición de recuperación de las filas de la/s tabla/s. Sólo se obtendrán aquellas Registros que verifiquen dicha condición, que será opcional.
En el caso de que se omita esta parte, se recuperarán todas las filas.
GROUP BY <atributos>: permite establecer una selección de campos cuando se utilizan funciones escalares o de conteo (ya se verá más adelante lo que significa.
HAVING <condición>: establece una condición para los atributos obtenidos como resultado de la aplicación de funciones escalares.
ORDER BY <atributos>: permite obtener el resultado de la consulta ordenado por los atributos especificados.
En el caso de que se especifiquen varias tablas, en la cláusula FROM, será conveniente denotar los campos de la cláusula SELECT precedidos por el nombre de la tabla donde se encuentra y un punto, para que, en el caso de que dicho campo exista en más de una tabla, se sepa en cada momento a cual de ellos nos estamos refiriendo, evitando en este caso el problema de ambigüedad.
28
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
EJEMPLOS DE SENTENCIAS SQL CON SELECT
1. Mostrar todos los registros de la tabla Productos
SELECT * FROM PRODUCTOS
2. Mostrar nombre,apellidos y cargo de todos los empleados
SELECT NOMBRE,APELLIDOS,CARGO FROM EMPLEADOS
3. Mostrar los nombreproducto,preciounidad,stock de los productos que tengan un precio mayor a 20
SELECT NOMBREPRODUCTO,PRECIOUNIDAD,STOCK FROM PRODUCTOS WHERE PRECIOUNIDAD>20
4. Mostrar nombreproducto,preciounidad,idcategoria de los productos que tengan un precio entre 25 y 35 soles
SELECT NOMBREPRODUCTO,PRECIOUNIDAD,IDCATEGORIA FROM PRODUCTOS
29
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
5. Mostrar los pedidos entregados entre el 18/6/95 y el 25/12/97
SELECT IDPEDIDO,IDCLIENTE,FECHAPEDIDO,FECHAENTREGA,CARGOFROM PEDIDOS WHERE FECHAPEDIDO BETWEEN ‘18/06/95’ AND ‘25/12/97’
6. Mostrar a los clientes que no tengan numero de fax
SELECT NOMBRECOMPAÑÍA,DIRECCIÓN,CIUDAD,PAIS,FAX FROM CLIENTES WHERE FAX IS NULL
7. Mostrar a los clientes que si tengan numero de fax
SELECT NOMBRECOMPAÑÍA,DIRECCIÓN,CIUDAD,PAIS,FAX FROM CLIENTES WHERE FAX IS NOT NULL
8. Mostrar los productos cuyo nombre empiezen con las letras “Que”
SELECT IDPRODUCTO,NOMBREPRODUCTO,PRECIOUNIDAD FROM PRODUCTOS WHERE NOMBREPRODUCTO LIKE ‘Que%’
9. Mostrar los productos que empiezen con las letras “A,B,C,D”
SELECT IDPRODUCTO,NOMBREPRODUCTO,PRECIOUNIDAD FROM PRODUCTOS WHERE NOMBREPRODUCTO LIKE ‘[A-D]%’
10. Mostrar los clientes cuyo nombre termine en en la letra “S”
SELECT IDCLIENTE,NOMBRECOMPAÑIA,DIRECCIÓN FROM CLIENTES WHERE NOMBRECOMPAÑIA LIKE ‘%S’
11. Mostrar un listado de productos ordenado por precio en forma Ascendente
SELECT IDPRODUCTO,NOMBREPRODUCTO,PRECIOUNIDAD FROM PRODUCTOS ORDER BY NOMBREPRODUCTO
12. Mostrar un listado de productos ordenado por precio en forma Descendente
SELECT IDPRODUCTO,NOMBREPRODUCTO,PRECIOUNIDAD FROM PRODUCTOS ORDER BY NOMBREPRODUCTO DESC
13. Mostrar los 3 productos mas Caros
SELECT TOP 3 IDPRODUCTO,NOMBREPRODUCTO,PRECIOUNIDAD,STOCK FROM PRODUCTOS ORDER BY PRECIOUNIDAD DESC
14. Mostrar los 3 productos mas Baratos
SELECT TOP 3 IDPRODUCTO,NOMBREPRODUCTO,PRECIOUNIDAD,STOCK FROM PRODUCTOS ORDER BY PRECIOUNIDAD DESC
31
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
15. Mostrar los productos que tengan un precio menor o igual a 50 y su stock se encuentre entre 60 y 80
SELECT NOMBREPRODUCTO,PRECIOUNIDAD,STOCK FROM PRODUCTOS WHERE (PRECIOUNIDAD <=50) AND (STOCK>=60 AND STOCK<=80)
16. Mostrar los clientes cuyo pais sea igual alemania o francia
SELECT NOMBRECOMPAÑIA,PAIS FROM CLIENTES WHERE PAIS=’ALEMANIA’ OR PAIS=’FRANCIA’
17. Mostrar los clientes cuyo pais sea Italia,brasil,francia,irlanda,españa y suecia
SELECT NOMBRECOMPAÑIA,PAIS FROM CLIENTES WHERE PAIS IN(‘ITALIA’,’BRASIL’,’FRANCIA’,’IRLANDA’,’ESPAÑA’,’SUECIA’)
18. Mostrar los pedidos del cliente cuyo codigo es ‘Anton’ o ‘bonap’ y la fecha de los pedidos sea mayor a 11/5/95
SELECT IDPEDIDO,IDCLIENTE,FECHAPEDIDO FROM PEDIDOS WHERE (IDCLIENTE=’ANTON’ OR IDCLIENTE=’BONAP’) AND (FECHAPEDIDO>’11/5/95’)
32
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
CONSULTAS MULTITABLAEn este tema vamos a estudiar las consultas multitabla llamadas así porque están basadas en más de una tabla.
El SQL de Microsoft Jet 4.x soporta dos grupos de consultas multitabla:
la unión de tablas
la composición de tablas
LA UNIÓN DE TABLASEsta operación se utiliza cuando tenemos dos tablas con las mismas columnas y queremos obtener una nueva tabla con las filas de la primera y las filas de la segunda. En este caso la tabla resultante tiene las mismas columnas que la primera tabla (que son las mismas que las de la segunda tabla).
Por ejemplo tenemos una tabla de libros nuevos y una tabla de libros antiguos y queremos una lista con todos los libros que tenemos. En este caso las dos tablas tienen las mismas columnas, lo único que varía son las filas, además queremos obtener una lista de libros (las columnas de una de las tablas) con las filas que están tanto en libros nuevos como las que están en libros antiguos, en este caso utilizaremos este tipo de operación.
Cuando hablamos de tablas pueden ser tablas reales almacenadas en la base de datos o tablas lógicas (resultados de una consulta), esto nos permite utilizar la operación con más frecuencia ya que pocas veces tenemos en una base de datos tablas idénticas en cuanto a columnas. El resultado es siempre una tabla lógica.
Por ejemplo queremos en un sólo listado los productos cuyas existencias sean iguales a cero y también los productos que aparecen en pedidos del año 90. En este caso tenemos unos productos en la tabla de productos y los otros en la tabla de pedidos, las tablas no tienen las mismas columnas no se puede hacer una union de ellas pero lo que interesa realmente es el identificador del producto (idfab,idproducto), luego por una parte sacamos los códigos de los productos con existencias cero (con una consulta), por otra parte los códigos de los productos que aparecen en pedidos del año 90 (con otra consulta), y luego unimos estas dos tablas lógicas.
El operador que permite realizar esta operación es el operador UNION.
34
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
LA COMPOSICIÓN DE TABLASLa composición de tablas consiste en concatenar filas de una tabla con filas de otra. En este caso obtenemos una tabla con las columnas de la primera tabla unidas a las columnas de la segunda tabla, y las filas de la tabla resultante son concatenaciones de filas de la primera tabla con filas de la segunda tabla
El ejemplo anterior quedaría de la siguiente forma con la composición:
A diferencia de la unión la composición permite obtener una fila con datos de las dos tablas, esto es muy útil cuando queremos visualizar filas cuyos datos se encuentran en dos tablas.
Por ejemplo queremos listar los pedidos con el nombre del representante que ha hecho el pedido, pues los datos del pedido los tenemos en la tabla de pedidos pero el nombre del representante está en la tabla de empleados y además queremos que aparezcan en la misma línea; en este caso necesitamos componer las dos tablas (Nota: en el ejemplo expuesto a continuación, hemos seleccionado las filas que nos interesan).
Existen distintos tipos de composición, aprenderemos a utilizarlos todos y a elegir el tipo más apropiado a cada caso.
Los tipos de composición de tablas son:
El INNER JOIN
El LEFT / RIGHT JOIN
SELECT *
FROM PEDIDOS,CLIENTES
WHERE PEDIDOS.IDCLIENTE=CLIENTES.IDCLIENTE
35
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
Combinamos todos los pedidos con todos los clientes pero luego seleccionamos los que cumplan que el código de cliente de la tabla de pedidos sea igual al código de cliente de la tabla de clientes, por lo tanto nos quedamos con los pedidos combinados con los datos del cliente correspondiente.
Las columnas que aparecen en la cláusula WHERE de nuestra consulta anterior se denominan columnas de emparejamiento ya que permiten emparejar las filas de las dos tablas. Las columnas de emparejamiento no tienen por qué estar incluidas en la lista de selección.
Normalmente emparejamos tablas que están relacionadas entre sí y una de las columnas de emparejamiento es clave principal, pues en este caso, cuando una de las columnas de emparejamiento tienen un índice definido es más eficiente utilizar otro tipo de composición, el INNER JOIN.
EL INNER JOINEl INNER JOIN es otro tipo de composición de tablas, permite emparejar filas de distintas tablas de forma más eficiente que con el producto cartesiano cuando una de las columnas de emparejamiento está indexada. Ya que en vez de hacer el producto cartesiano completo y luego seleccionar la filas que cumplen la condición de emparejamiento, para cada fila de una de las tablas busca directamente en la otra tabla las filas que cumplen la condición, con lo cual se emparejan sólo las filas que luego aparecen en el resultado.
La sintaxis es la siguiente:
Ejem:
SELECT *
FROM PEDIDOS
INNER JOIN CLIENTES ON PEDIDOS.IDCLIENTE = CLIENTES.IDCLIENTE
tabla1 y tabla2 son especificaciones de tabla (nombre de tabla con alias o no, nombre de consulta guardada), de las tablas cuyos registros se van a combinar.
Pueden ser las dos la misma tabla, en este caso es obligatorio definir al menos un alias de tabla.
col1, col2 son las columnas de emparejamiento.
Observar que dentro de la cláusula ON los nombres de columna deben ser nombres cualificados (llevan delante el nombre de la tabla y un punto).
Las columnas de emparejamiento deben contener la misma clase de datos, las dos de tipo texto, de tipo fecha etc... los campos numéricos deben ser de tipos similares. Por ejemplo, se puede combinar campos AutoNumérico(IDENTIDAD) y INT puesto que son tipos similares, sin embargo, no se puede combinar campos de tipo INT y FLOAT. Además las columnas no pueden ser de tipo TEXT ni IMAGE
COMP representa cualquier operador de comparación ( =, <, >, <=, >=, o <> ) y se utiliza para establecer la condición de emparejamiento.
Se pueden definir varias condiciones de emparejamiento unidas por los operadores AND y OR poniendo cada condición entre paréntesis. Ejemplo:
SELECT * FROM PROVEEDORES INNER JOIN PRODUCTOS ON PRODUCTOS
36
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
PROVEEDORES.IDPROVEEDOR AND PROVEEDORES.IDPROVEEDOR = PRODUCTOS.IDPROVEEDOR
Se pueden combinar más de dos tablas En este caso hay que sustituir en la sintaxis una tabla por un INNER JOIN completo.
Por ejemplo:SELECT *FROM (PEDIDOS INNER JOIN CLIENTES ON PEDIDOS.IDCLIENTE = CLIENTES.IDCLIENTE) INNER JOIN EMPLEADOS ON PEDIDOS.IDEMPLEADO = EMPLEADOS.IDEMPLEADO
En vez de tabla1 hemos escrito un INNER JOIN completo, también podemos escribir:
SELECT *FROM CLIENTES INNER JOIN (PEDIDOS INNER JOIN EMPLEADOS ON PEDIDOS.IDEMPLEADO = EMPLEADOS.IDEMPLEADO) ON PEDIDOS.IDCLIENTE = CLIENTES.IDCLIENTE
En este caso hemos sustituido tabla2 por un INNER JOIN completo.
El LEFT JOIN y RIGHT JOINCrearemos otra base de datos llamada DBVENTAS la cual se utiiizara tambien con la base de datos SUPERMERCADOSNEPTUNO.las tablas de DBVENTAS seran las siguientesPara los siguientes ejemplos usaremos las siguientes tablas de ejemplo
37
OficinasEmpleados
Clientes Productos
Clientes
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
El LEFT JOIN y RIGHT JOIN son otro tipo de composición de tablas, también denominada composición externa. Son una extensión del INNER JOIN.
INNER JOIN) es una composicion interna ya que todos los valores de las filas del resultado son valores que están en las tablas que se combinan.
Con una composición interna sólo se obtienen las filas que tienen al menos una fila de la otra tabla que cumpla la condición, veamos un ejemplo:
Queremos combinar los empleados con las oficinas para saber la ciudad de la oficina donde trabaja cada empleado, si utilizamos un producto cartesiano tenemos:
SELECT EMPLEADOS.*,CIUDADFROM EMPLEADOS, OFICINASWHERE EMPLEADOS.OFICINA = OFICINAS.OFICINA
Observar que hemos cualificado el nombre de columna oficina ya que ese nombre aparece en las dos tablas de la FROM.
Con esta sentencia los empleados que no tienen una oficina asignada (un valor nulo en el campo oficina de la tabla empleados) no aparecen en el resultado ya que la condición empleados.oficina = oficinas.oficina será siempre nula para esos empleados.
Si utilizamos el INNER JOIN
SELECT EMPLEADOS.*, CIUDADFROM EMPLEADOS INNER JOIN OFICINAS ON EMPLEADOS.OFICINA = OFICINAS.OFICINA
Nos pasa lo mismo, el empleado 110 tiene un valor nulo en el campo oficina y no aparecerá en el resultado.
Pues en los casos en que queremos que también aparezcan las filas que no tienen una fila coincidente en la otra tabla, utilizaremos el LEFT o RIGHT JOIN.
La sintaxis del LEFT JOIN es la siguiente:
La descripción de la sintaxis es la misma que la del INNER JOIN (ver página anterior), lo único que cambia es la palabra INNER por LEFT (izquierda en inglés).
Esta operación consiste en añadir al resultado del INNER JOIN las filas de la tabla de la izquierda que no tienen correspondencia en la otra tabla, y rellenar en esas filas los campos de la tabla de la derecha con valores nulos.
Ejemplo:
SELECT *FROM EMPLEADOS LEFT JOIN OFICINAS ON EMPLEADOS.OFICINA = OFICINAS.OFICINA
Con el ejemplo anterior obtenemos una lista de los empleados con los datos de su oficina, y el empleado 110 que no tiene oficina aparece con sus datos normales y los datos de su oficina a nulos.
38
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
La sintaxis del RIGHT JOIN es la siguiente:
La sintaxis es la misma que la del INNER JOIN (ver página anterior), lo único que cambia es la palabra INNER por RIGHT (derecha en inglés).
Esta operación consiste en añadir al resultado del INNER JOIN las filas de la tabla de la derecha que no tienen correspondencia en la otra tabla, y rellenar en esas filas los campos de la tabla de la izquierda con valores nulos.
Ejemplo:
SELECT *FROM empleados RIGHT JOIN oficinas ON empleados.oficina = oficinas.oficina
Con el ejemplo anterior obtenemos una lista de los empleados con los datos de su oficina, y además aparece una fila por cada oficina que no está asignada a ningún empleado con los datos del empleado a nulos.
Una operación LEFT JOIN o RIGHT JOIN se puede anidar dentro de una operación INNER JOIN, pero una operación INNER JOIN no se puede anidar dentro de LEFT JOIN o RIGHT JOIN. Los anidamientos de JOIN de distinta naturaleza no funcionan siempre, a veces depende del orden en que colocamos las tablas, en estos casos lo mejor es probar y si no permite el anudamiento, cambiar el orden de las tablas ( y por tanto de los JOINs) dentro de la cláusula FROM.
Por ejemplo podemos tener:
SELECT *FROM CLIENTES INNER JOIN (EMPLEADOS LEFT JOIN OFICINAS ON EMPLEADOS.OFICINA = OFICINAS.OFICINA) ON CLIENTES.REPCLIE = EMPLEADOS.NUMCLIE
Combinamos empleados con oficinas para obtener los datos de la oficina de cada empleado, y luego añadimos los clientes de cada representante, así obtenemos los clientes que tienen un representante asignado y los datos de la oficina del representante asignado.
Si hubiéramos puesto INNER en vez de LEFT no saldrían los clientes que tienen el empleado 110 (porque no tiene oficina y por tanto no aparece en el resultado del LEFT JOIN y por tanto no entrará en el cálculo del INNER JOIN con clientes).
39
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
EJEMPLOS DE CONSULTAS MULTITABLA CON SUPERMERCADOSNEPTUNO
1. Mostrar Nombre del Producto,Precio Unidad del producto,Nombre de la Categoria,Nombre del Proveedor y stock de los productos
CON WHERESELECT PRODUCTOS.NOMBREPRODUCTO,PRODUCTOS.PRECIOUNIDAD,CATEGORIAS.NOMBRECATEGORIA,PROVEEDORES.NOMBRECOMPAÑIA,STOCK FROM PRODUCTOS,CATEGORÍAS,PROVEEDORES WHERE PRODUCTOS.IDCATEGORIA=CATEGORIAS.IDCATEGORIA AND PRODUCTOS.IDPROVEEDOR=PROVEEDORES.IDPROVEEDOR
CON INNER JOINSELECT PRODUCTOS.NOMBREPRODUCTO,PRODUCTOS.PRECIOUNIDAD,CATEGORIAS.NOMBRECATEGORIA,PROVEEDORES.NOMBRECOMPAÑIA,STOCK FROM PRODUCTOSINNER JOIN CATEGORIAS ON PRODUCTOS.IDCATEGORIA=CATEGORIAS.IDCATEGORIA INNER JOIN PROVEEDORES ON PRODUCTOS.IDPROVEEDOR=PROVEEDORES.IDPROVEEDOR
2. Mostrar los pedidos con los siguientes datos idpedido,fechapedido,nombre del cliente,el nombre del empleado y el cargo de pedido
SELECT PEDIDOS.IDPEDIDO,PEDIDOS.FECHAPEDIDO,CLIENTES.NOMBRECOMPAÑIA,EMPLEADOS.APELLIDOS+ ‘ ‘ + EMPLEADOS.NOMBRE,PEDIDOS.CARGO FROM PEDIDOSINNER JOIN CLIENTES ON PEDIDOS.IDCLIENTE=CLIENTES.IDCLIENTE
40
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
INNER JOIN EMPLEADOS ON PEDIDOS.IDEMPLEADO=EMPLEADOS.IDEMPLEADO
41
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
FUNCIONES DE SQL SERVER
FUNCIONES DE TIPO FECHAEstas funciones escalares realizan una operación sobre un valor de fecha y hora de entrada, y devuelven un valor de cadena, numérico o de fecha y hora.
DATEADDDevuelve un valor datetime nuevo que se basa en la suma de un intervalo a la fecha especificada.
SintaxisDATEADD ( partedeFecha , numero, Fecha )
Parte de fecha Abreviaturas
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
Mostrar la fecha de pago de los pedidos incrementando en 21 dias la fecha de pedido
SELECT DATEADD(day, 21, FechaPedido) AS FechaPagoFROM pedidos
DATEDIFFDevuelve el número de dias,meses o años que han transcurrido entre dos fechas especificadas.
Sintaxis DATEDIFF ( parteFecha , Fechainicial , fechafinal )
SELECT NOMBRE,APELLIDOS, DATEDIFF(YEAR,FECHANACIMIENTO,GETDATE()) AS EDAD FROM EMPLEADOS
42
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
DATENAME
Devuelve una cadena de caracteres que representa la parte de la fecha especificada de la fecha especificada.
Sintaxis
DATENAME (parteFecha , fecha )
En este ejemplo se extrae el nombre del mes de la fecha devuelta por GETDATE.
SELECT IDPEDIDO,FECHAPEDIDO,FECHAENVIO,FECHAENTREGA,DATENAME(MONTH, FECHAPEDIDO) AS MESPEDIDO FROM PEDIDOS
El siguiente es el conjunto de resultados:
DATEPARTDevuelve un entero que representa la parte de la fecha especificada de la fecha indicada.
Sintaxis DATEPART ( parteFecha , fecha )
La función GETDATE devuelve la fecha actual; sin embargo, la fecha completa no es siempre la información que se necesita para la comparación (a menudo, sólo se compara una parte de la fecha). En este ejemplo se muestra la salida de GETDATE y la de DATEPART.
SELECT GETDATE() AS 'FechaActual'GOEl siguiente es el conjunto de resultados:FechaActual --------------------------- Feb 18 1998 11:46PM
En este ejemplo se supone que la fecha es el 29 de mayo.
SELECT DATEPART(month, GETDATE())GO
El siguiente es el conjunto de resultados:----------- 5
43
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
DAYDevuelve un entero que representa la parte del día de la fecha especificada.
Sintaxis DAY ( fecha )
En este ejemplo se devuelve el número del día de la fecha 12/03/1998.
SELECT DAY('03/12/1998') AS 'NumeroDia'GOEl siguiente es el conjunto de resultados:NumeroDia ------------ 12
MONTHDevuelve un entero que representa el mes de una fecha especificada.
Sintaxis MONTH ( Fecha )
Este ejemplo devuelve el número del mes de la fecha 03/12/1998.
SELECT "NumeroMes" = MONTH('03/12/1998')GO
El siguiente es el conjunto de resultados:
NumeroMes------------ 3
YEARDevuelve un entero que representa la parte de año de la fecha especificada.
Sintaxis YEAR ( Fecha )
Este ejemplo devuelve el número del año de la fecha 03/12/1998.
SELECT "NumeroAño" = YEAR('03/12/1998')GO
El siguiente es el conjunto de resultados:
NumeroAño------------ 1998
44
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
UTILIZAR FUNCIONES MATEMÁTICASUna función matemática realiza una operación matemática en expresiones numéricas y devuelve el resultado de la operación. Las funciones matemáticas operan sobre datos numéricos suministrados por el sistema Microsoft SQL Server (decimal, integer, float, real, money, smallmoney, smallint y tinyint). La precisión de las operaciones integradas para el tipo de datos float es, de forma predeterminada, de seis lugares decimales.
ROUNDDevuelve una expresión numérica, redondeada a la longitud o precisión especificada.
Sintaxis ROUND ( numero , longitud )
Ejemplos Resultado
ROUND(748.58, -1) 750.00
ROUND(748.58, -2) 700.00
ROUND(748.58, -3) 1000.00
ROUND(123.4545, 2) 123.4500
ROUND(123.45, -2) 100.00
CEILINGDevuelve un numero entero más pequeño mayor o igual que la expresión numérica dada.
Sintaxis CEILING ( expressionNumerica )
Este ejemplo muestra valores numéricos positivos, negativos y cero con la función CEILING.
SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)
El siguiente es el conjunto de resultados:--------- --------- ------------------------- 124.00 -123.00 0.00
FLOORDevuelve el numero entero más grande menor o igual que la expresión numérica dada.
SintaxisFLOOR ( expresionNumerica )
Este ejemplo muestra valores numéricos positivos, negativos y valores de moneda con la función FLOOR.
SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)
El resultado es la parte entera del valor calculado en el tipo de datos correspondiente a expresionNumerica
45
IAP SQL SERVER 2000 Ing. Lino Martin Quispe Tincopa
--------- --------- -----------123 -124 123.0000
POWERDevuelve el valor de la expresión indicada elevada a la potencia especificada.
Sintaxis POWER ( expression numerica, EXPONENTE )
Ejemplo elevar el valor 2 al cubo SELECT POWER(2,3)
Resultado es igual a 8
SQRTDevuelve la raíz cuadrada de la expresión especificada.
Sintaxis SQRT ( expressionNumerica )
DECLARE @myvalue floatSET @myvalue = 16
SELECT SQRT(@myvalue)
Devuelve 4
46