sesion10_bdppt.pdf
TRANSCRIPT
-
CARRERA DE INGENIERA DE SISTEMAS
SEMESTRE ACADMICO 2015-I
Agosto 2010
FDI/BD
SESIN 10
MG. JOSE LUIS HERRERA SALAZAR
CONSULTAS DE DATOS
-
CARRERA DE INGENIERA DE SISTEMAS
Consulta de Datos
57
-
CARRERA DE INGENIERA DE SISTEMAS
Temario Comando SELECT Consulta de Varias tablas Tpicos avanzados de Consulta de datos
58
-
CARRERA DE INGENIERA DE SISTEMAS
Comando SELECT Introduccin al SELECT Seleccin de columnas Funciones matemticas Funciones de caracteres Funciones de fechas Funciones del sistema Seleccin de registros
59
-
CARRERA DE INGENIERA DE SISTEMAS
Comando SELECT Permite la extraccin (consulta) de informacin
de una o varias tablas de la base de datosSELECT (columnas a extraer)FROM (Tablas donde estn los datos)WHERE (Condiciones de la consulta)
Para mostrar todas las columnas en una extraccin sobre una tabla sera:
SELECT * FROM nombre_tabla
60
-
CARRERA DE INGENIERA DE SISTEMAS
Comando SELECT En un SELECT se especifican los campos que
se desean ver en el resultado. Si se escribe DISTINCT previo a los campos de
la consulta es para indicar que no queremos que se incluyan valores de registros duplicados
61
Todas las nacionalidades de los alumnos
Select NacionalidadFrom Alumno Todas las nacionalidades de los alumnos
(Sin duplicados)
Select destinct NacionalidadFrom Alumno
-
CARRERA DE INGENIERA DE SISTEMAS
Comando SELECT En la clusula FROM se especifican la o las
tablas de donde se obtendrn los datos. Las tablas pueden ser identificadas por Alias
62
Select *From Materias MWhere M.MateriaId = 2293
Select *From Materias as MWhere M.MateriaId = 2293
-
CARRERA DE INGENIERA DE SISTEMAS
Comando SELECT El orden de las columnas en el SELECT es en el orden
que apareceran en la salida. No corresponde con el orden en la tabla
Se pueden escribir encabezados para asignar o cambiar el nombre de una columna en el resultado
SELECT NumMateria = MateriaID,Nombre Materia = NomMateriaNomMaestro as Maestro
FROM Materias Se pueden poner literales dentro del SELECT como
valores prestablecidosSELECT Cve_escuela = RODHE, MatriculaID, AlumnoNomFROM Alumnos
63
-
CARRERA DE INGENIERA DE SISTEMAS
Comando SELECT Al seleccionar columnas numricas, es psible
realizar operaciones de: Suma operador + Resta operador - Multiplicacin operador * Divisin operador / Mdulo operador % (enteros)
SELECT CostoMateria, Costo con IVA = CostoMateria * 1.10
FROM Materias64
-
CARRERA DE INGENIERA DE SISTEMAS
Funciones Matemticas Las funciones matemticas realizan
operaciones datos numricos Funciones comnes:
ABS(nmero) AbsolutoEXP(float) ExponencialLOG(float) Logaritmo NaturalPI() Valor de PIROUND(nmero, cantidad) RedondeoSIGN(nmero) (-), (+), 0SQRT(float) Raz CuadradaSQUARE(float) El cuadrado dePOWER(nmero, elevado a) Nmero elevado a65
-
CARRERA DE INGENIERA DE SISTEMAS
Funciones MatemticasFLOOR(nmero) Entero mayor
-
CARRERA DE INGENIERA DE SISTEMAS
Funciones de Caracteres
ASCII(char)CHAR(entero)CHARINDEX(p, string)LOWER(string)UPPER(string)LTRIM(string)RTRIM(string)REPLICATE(string, n)
ConcatenacinValor Asccii del ms izquierdoPosicin de inicio de p en el stringConvierte a minsculasConvierte a maysculasQuita espacios de la izquierdaQuita espacios de la derechaRepite n veces el string
67
Estas funciones realizan operaciones sobre datos binarios ycaracteres de strings o expresionesFunciones ms comunes:
-
CARRERA DE INGENIERA DE SISTEMAS
Funciones de CaracteresSPACE(n) Repte n veces un espacioSTR(float, l, d) Convierte a string un float con
una longitud total de I y con d nmero de decimales
SUBSTRING(string, ini, long) Regresa la parte del string desde la posicin de inicio con una longitud long
PATINDEX(%p%, string) Posicin de inicio de p en el string (con wildcards)
REVERSE(string) Regresa el string al revsRIGHT(string, n) Regresa los ltimos n
caracteresSTUFF(str1, ini, long, str2) Borra del str1, long caracteres
desde inicio y coloca el str2 en inicio
68
-
CARRERA DE INGENIERA DE SISTEMAS
Funciones de Fechas Las funciones de Fechas, manejan los tipos de datos de
fecha y sus componentes Funciones
DATEADD(datepart, nmero, fecha) DATEDIFF(datepart, fecha1, fecha2) DATENAME(datepart, fecha) DATEPART(datepart, fecha) GETDATE)=
EJEMPLOSELECT Dif. En Meses = DATEDIFF(mm, Fecha1, Fecha2)
Hoy m[as 15 Das = DATEADD(dd, 15, GETDATE())El mes actual es = DATENAME(mm, GETDATE())El ao actual es = DATEPART(yy, GETDATE())
FROM Fechas
69
-
CARRERA DE INGENIERA DE SISTEMAS
Funciones de Fechas DateParts
DatePart Abreviacin ValoresYear yy,yyyy 1753-9999Quarter qq, q 1-4Month mm, m 1-12Day of year dy, y 1-366Day dd, d 1-31Week wk, ww 0-51Weekday dw 1-7 (1 es domingo)Hour hh 0-23Minute mi, n 0-59Second ss, s 0-59Millisecond ms 0-999
70
-
CARRERA DE INGENIERA DE SISTEMAS
Funciones de Fechas Mediante el Comando CONVERT, podemos formatear
los tipos de datos fecha a un estilo determinado Ejemplo
SELECT Hoy es: = CONVERT(Char(8), GETDATE(), 1) Algunos de los estilos que provee SQL Server son:
Estilo (yy) Estilo (yyyy) Formato1 101 Mm/dd/yy3 103 Dd/mm/yy5 105 Dd-mm-yy8 108 Hh:mi:ss
13 113 Dd mes yyhh:mi:ss:ms (24Hrs)
71
-
CARRERA DE INGENIERA DE SISTEMAS
Funciones del Sistema Estas funciones nos regresan informacin especial de la
Base de Datos Obtencin de valores de la conexin
APP_NAME( ), HOST_NAME( ), HOST_ID( ),USER, USER_NAME( ), USER_ID( )SUSER_ID( ), SUSER_NAME( )DB_NAME( ), DB_ID( )
ValidacionesISDATE(string), ISNUMERIC(string)
72
-
CARRERA DE INGENIERA DE SISTEMAS
Funciones del Sistema Objetos de la base de datos
COL_NAME ( tabla, id_de_la_columna )COL_LENGTH ( tabla, columna )INDEX_COL ( tabla, id_indice, id_llave )OBJECT_ID ( objeto )OBJECT_NAME ( id_del_objeto )STATS_NAME ( tabla.indice ) = fecha de la ltima actualizacin de
las estadsticas de un ndice Propiedad IDENTITY
IDENT_INCR ( tabla )IDENT_SEED ( tabla )
73
-
CARRERA DE INGENIERA DE SISTEMAS
Funciones del Sistema De Caracteres
DATALENGTH ( string )
CondicionalesISNULL ( expresion, valor )NULLIF ( expresion1, expresion2 )COALESCE ( expresion1, expresion2, expresion3, etc.)
74
-
CARRERA DE INGENIERA DE SISTEMAS
Seleccin de Registros En la clusula WHERE definimos los criterios de
busqueda de una consulta y esta puede incluir: Comparaciones =, >, , !< Rangos BETWEEN, NOT BETWEEN Listas IN, NOT IN Datos Null IS NULL, IS NOT NULL Porciones de caracteres LIKE Lgicos AND, OR Negacin NOT
Para hacer ms rpida la ejecucin de las consultas, se recomienda usar lo menos posible las negaciones.
75
-
CARRERA DE INGENIERA DE SISTEMAS
Seleccin de Registros Comparaciones
Alumnos que se dieron de alta el da de hoySelect * from AlumnoWhere AltaFecha = CONVERT(char(8), GETDATE(),1)
RangosAlumnos con matricula entre el 100 y 200
Select * from AlumnoWhere MatriculaID BETWEEN 100 and 200
76
-
CARRERA DE INGENIERA DE SISTEMAS
Seleccin de Registros Listas
Alumnos que se dieron de alta el 9 o 10 de Ene. 2003Select * from AlumnoWhere AltaFecha IN ( 01/08/03, 01/08/03 )
Datos NULLAlumnos sin fecha de alta
Select * from AlumnoWhere AltaFecha IS NULL
77
-
CARRERA DE INGENIERA DE SISTEMAS
Seleccin de Registros Porciones de caracteres. Utilizan comodines
(wildcards) para hacer comparaciones % Cualquier string de cero o ms caracteres _ Cualquier caracter (una posicion) [ ] Cualquier carcater dentro del rango descrito [^] Cualquier caracter fuera del rangoSelect MatriculaId, ApellidoPaterno, NomAlumno FROM Alumnos WhereNomAlumno LIKE Mar% Las 3 primeras letras del nombre sean MarNomAlumno LIKE _arla El nomre sea de 5 caracteres y termine en arlaNomAlumno LIKE [A-C]% La primer letra del nombre sea A, B o CNomAlumno LIKE [^R]% El nombre no empiece con R
78
-
CARRERA DE INGENIERA DE SISTEMAS
Seleccin de Registros Lgicos
Alumnos con matricula > a 200, que entraron un da especifico y que sean de primer semestre
Select * from AlumnoWhere MatriculaId > 200 AND AltaFecha = 08/10/2002 AND Semestre = 1
NegacinAlumnos que no estn en 1 o 3 semestre[
Select * from AlumnoWhere Semestre NOT IN (1,3)
79
-
CARRERA DE INGENIERA DE SISTEMAS
Consultas a varias tablas Introduccin el uso de JOINS Tipos de JOINS JOINS de ms de dos tablas JOINS de una tabla consigo misma Union de varios conjuntos de resultados Creacin de tablas a partir de resultados
80
-
CARRERA DE INGENIERA DE SISTEMAS
Introduccin al uso de Joins Es posible utilizar cualquiera de las sintaxis que
brinda SQL NO estndar
Las tablas del JOIN se especifican en el FROM, separadas por comas y en el WHERE se indican las condiciones del JOIN.
Estandar ANSI SQL-92 Las tablas del JOIN se especifican en el FROM,
indicando el tipo de JOIN mediante ciertas claves, y en el ON se indican las condiciones del JOIN
Select * from tabla CROSS JOIN tabla | tabla [Clave_JOIN] JOIN tablaON Condiciones (Claves: INNER, LEFT (OUTER), RIGHT (OUTER), FULL OUTER)
81
-
CARRERA DE INGENIERA DE SISTEMAS
Tipos de Joins
Joins Internos ( inner joins ) Operacin tpica de join. Utiliza operadores como el
=, , etc. para comparar columnas comunes a ambas tablas
SELECT a.MatriculaID, AlumnoNom, MateriaCveFROM Alumno a, AlumnoMateria mWhere a.MatriculaId = m.MatriculaId
SELECT a.MatriculaID, AlumnoNom, MateriaCveFROM Alumno a INNER JOIN AlumnoMateria mON a.MatriculaId = m.MatriculaId
82
-
CARRERA DE INGENIERA DE SISTEMAS
Tipos de Joins
Joins Internos ( inner joins ) Operacin tpica de join. Utiliza operadores como el
=, , etc. para comparar columnas comunes a ambas tablas
SELECT a.MatriculaID, a.AlumnoNom, m.MateriaCveFROM Alumno a, AlumnoMateria mWhere a.MatriculaId = m.MatriculaId
SELECT a.MatriculaID, a.AlumnoNom, m.MateriaCveFROM Alumno a INNER JOIN AlumnoMateria mON a.MatriculaId = m.MatriculaId
83
-
CARRERA DE INGENIERA DE SISTEMAS
Tipos de Joins
Joins cruzados ( cross joins ) Producto cartesiano. Regresa todas las
combinaciones de los registros de la tabla izquierda con los registros de la tabla derecha
SELECT a.MatriculaID, a.AlumnoNom, m.MateriaCveFROM Alumno a, AlumnoMateria m
SELECT MatriculaID, AlumnoNom, MateriaCveFROM Alumno CROSS JOIN AlumnoMateria
84
-
CARRERA DE INGENIERA DE SISTEMAS
Tipos de Joins Joins Externos ( outer joins )
Cuando un regsitro en la tabla X no tiene un registro correspondiente en la tabla Y, el resultado de ese rengln tendr valores NULOS para los campos que provengan de la tabla Y
Left Outer Join: *= incluye todos los registros de la tabla izquierda. Donde: X=izquierda y Y=derecha
Right Outer Join: =* incluye todos los registros de la tabla derecha. Donde: X=derecha y Y=izquierda
Full Outer Join: incluye todos los registros de ambas tablas, izquierda y derecha, colocando nulos en donde no exista informacin
85
-
CARRERA DE INGENIERA DE SISTEMAS
Tipos de Joins
Ejemplo de Left Outer JoinVer todos los alumnos (con o sin materias inscritas), y para los alumnos inscritos la clave las materias que inscribieron
SELECT a.MatriculaID, a.AlumnoNom, m.MateriaCveFROM Alumno a, AlumnoMateria mWHERE a.MatriculaId *= m.MatriculaId
SELECT a.MatriculaID, a.AlumnoNom, m.MateriaCveFROM Alumno a LETF OUTER JOIN AlumnoMateria mON a.MatriculaId = m. MatriculaId
86
-
CARRERA DE INGENIERA DE SISTEMAS
Joins de dos o ms tablas Cualquier tabla a la que se haga referencia en una
operacin de Join, se puede combinar con otra tabla mediante un campo comn.
Seleccionar la matrcula, nombre del alumno, y nombre de las materias que cursa un alumnoSELECT a.MatriculaID, a.AlumnoNom, m.MateriaNomFROM Alumno a, AlumnoMateria am, Materia mWHERE a.MatriculaId = am.MatriculaId AND
am.MateriaCve = m.MateriaCve
SELECT a.MatriculaID, a.AlumnoNom, m.MateriaNomFROM Alumno a JOIN AlumnoMateria amON a.MatriculaId = am.MatriuculaIdJOIN Materia mON am.MateriaCve = m.MateriaCve
87
-
CARRERA DE INGENIERA DE SISTEMAS
Joins de una tabla consigo mismaSELECT MateriaCve,
SemestreNo, CostoMtoFROM Materia a, Materia bWHERE a.MateriaCve =
b.MateriaCve ANDa.CostoMto <
b.CostoMtoMateriCve SemestreNo CostoMto
CL0402 1 500
AL1524 1 400
MA0327 1 300
RU2031 2 300
CL0402 2 800
MA0327 2 150
MateriCve SemestreNo CostoMtoCL0402 1 500
AL1524 1 400
MA0327 1 300
RU2031 2 300
CL0402 2 800
MA0327 2 150
88
MateriCve SemestreNo CostoMtoCL0402 1 500
MA0327 2 400Resultado
Materia bMateria a
-
CARRERA DE INGENIERA DE SISTEMAS
Unin de varios conjuntos de Resultados
Se utiliza el operador UNION Combina los resultados de 2 o ms queries en uno solo Cada consta debe de tener
El mismo nmero de columnas Tipos de datos similares en las columnas El mismo orden de columnas en la lista de seleccin
SELECT MatriculaId, AlumnoNom FROM AlumnoWHERE AlumnoId = 3UNIONSELECT MatriculaId, AlumnoNom FROM AlumnoWHERE AlumnoId = 2
89
-
CARRERA DE INGENIERA DE SISTEMAS
Creacin de tablas a partir de resultados
Utilizar la instruccin SELECT INTO Caracteristicas
Crea una nueva tabla con la misma definicin que la original La tabla puede ser temporalp o permanente y no debe de exisitir Debe esta encendida la opcin SELECT INTO/BulkCopy para
crear una tabla permanente Asignar un nombre a las columnas en caso de que stas
provengan de operaciones
SELECT MateriaCve, MateriaNom, CostoMto, CostoIVA = CostoMto * 1.15INTO #MateriaTempFrom Materia
90
-
CARRERA DE INGENIERA DE SISTEMAS
Laboratorio
91