SQL NOTAS PROFESIONALES
Cargado por
elber martinezSQL NOTAS PROFESIONALES
Cargado por
elber martinezMachine Translated by Google
sql
Notas para profesionales SQL
Notas para profesionales
más de 100 páginas
de consejos y trucos profesionales
GolKicker.com Libros Descargo
de responsabilidad Este es un libro gratuito no oficial creado con fines educativos
de Programación Gratis y no está relacionado con grupos o empresas oficiales de SQL.
Todas las marcas comerciales y marcas registradas
son propiedad de sus respectivos dueños
Machine Translated by Google
Contenido
Acerca de .................................................... .................................................... .................................................... ............................. 1
Capítulo 1: Primeros pasos con SQL ............................................... .................................................... ...................... 2
Sección 1.1: Resumen .................................................... .................................................... .................................................... 2
Capítulo 2: Identificador Sección .................................................... .................................................... ................................................ 3
2.1: Identificadores sin comillas Capítulo .................................................... .................................................... ............................... 3
3: Tipos de datos .................................. .................................................... .................................................... ....... 4
Sección 3.1: DECIMAL y NUMÉRICO .................................................... .................................................... ........................ 4
Sección 3.2: FLOTANTE y REAL .................................................... .................................................... .................................... 4
Sección 3.3: Números enteros ............................................... .................................................... .................................................... .... 4
Sección 3.4: DINERO y DINERO PEQUEÑO .................................................... .................................................... .................. 4
Sección 3.5: BINARIO y VARBINARIO .................................................... .................................................... ...................... 4
Sección 3.6: CHAR y VARCHAR .................................................... .................................................... ............................ 5
Sección 3.7: NCHAR y NVARCHAR .................................................... .................................................... ...................... 5
Sección 3.8: IDENTIFICADOR ÚNICO ............................................... .................................................... .................................... 5
Capítulo 4: NULL Sección .................................................... .................................................... .................................................... ...... 6
4.1: Filtrado de NULL en consultas ........................................... .................................................... ............................. 6 Sección 4.2: Columnas
anulables en tablas .................................................... .................................................... ................... 6
Sección 4.3: Actualización de campos a .................................................... .................................................... ....................... 6
NULL Sección 4.4: Insertar filas con campos NULL .................................................... .................................................... ........... 7
Capítulo 5: Bases de datos y tablas de ejemplo Sección 5.1: Base .................................................... .................................................... 8
de datos de tiendas de automóviles .................................................... .................................................... ............................. 8
Sección 5.2: Base de datos de la .................................................... .................................................... ................................ 10
biblioteca Sección 5.3: Tabla de países
.................................................... .................................................... .................................... 13
Capítulo 6: SELECCIONAR .................................................... .................................................... .................................................... 14
Sección 6.1: Uso del carácter comodín para seleccionar todas las columnas en una consulta .................................. ...................... 14
Sección 6.2: SELECCIONAR usando alias de .................................................... .................................................... ........... 15
columna Sección 6.3: Seleccionar columnas individuales
.................................................... .................................................... .................. 18
Sección 6.4: Selección de un número específico de registros .................................................... .......................................................... 19
Sección 6.5: Selección con condición .................................................... .................................................... ..................... 20
Sección 6.6: Selección con CASE .................................................... .................................................... ............................ 20
Sección 6.7: Selección de columnas que llevan el nombre de palabras clave reservadas ........... .......................................... 21 Sección
6.8: Selección con tabla alias Sección 6.9: .................................................... .................................................... ................... 21
Selección con más de 1 condición Sección 6.10: Selección .................................................... .................................................... 22
sin bloquear la tabla Sección 6.11: Selección con funciones .................................................... .................................................... 23
agregadas Sección 6.12: Selección con condición de .................................................... .................................................... . 23
valores múltiples de la columna Sección 6.13: Obtener resultado agregado para .................................................... ..................... 24
grupos de filas ..... .................................................... .......................................... 24
Sección 6.14: Selección con resultados ordenados .................................................... .................................................... ............ 25
Sección 6.15: Seleccionar con nulo .................................................... .................................................... ............................... 25
Sección 6.16: Seleccionar distinto (solo valores únicos) .................................. .................................................... .......... 25 Sección 6.17:
Seleccionar filas de varias tablas .................................................... .................................................... ..... 26
Capítulo 7: AGRUPACIÓN POR .................................................... .................................................... .......................................... 27
Sección 7.1: Ejemplo básico de GROUP BY ........................................... .................................................... ....................... 27 Sección 7.2:
Filtre los resultados de GROUP BY usando una cláusula HAVING Sección .................................................... ............................... 28
7.3: USE GROUP BY para CONTAR el número de filas para cada entrada única en una columna dada
.................................................... .................................................... .................................................... .......................... 28
Sección 7.4: Agregación ROLAP (Minería de datos) ........................................... .................................................... ............. 29
Machine Translated by Google
Capítulo 8: ORDENAR POR .................................................... .................................................... .......................................... 31
Sección 8.1: Clasificación por número de columna (en lugar de nombre) .................................. ............................................. 31 Sección 8.2: Use
ORDER BY con TOP para devolver las filas x superiores en función del valor de una columna Sección 8.3: Orden de ............................... 31
clasificación personalizado Sección 8.4: Ordenar .................................................... .................................................... ................ 32
por alias Sección 8.5: Clasificación .................................................... .................................................... ..................................... 32
por varias columnas .................................................... .................................................... .............. 33
Capítulo 9: Operadores AND & OR ............................................... .................................................... ............................ 34 Sección 9.1: Y O
Ejemplo .................. .................................................... .................................................... ..................... 34 Capítulo 10: CASO
.................................................... .................................................... .................................................... ... 35
Sección 10.1: Use CASE para CONTAR el número de filas en una columna que coincide con una condición ...................................... 35
Sección 10.2: CASE buscado en SELECT (Coincide con una expresión booleana) .................................. ...................... 36 Sección 10.3: CASO en
una cláusula ORDEN POR .................................................... .................................................... ............. 36
Sección 10.4: CASE abreviado en SELECT .................................................... .................................................... .............. 36
Sección 10.5: Uso de CASE en UPDATE .................................................... .................................................... ..................... 37
Sección 10.6: Uso de CASE para valores NULL ordenados en último lugar
.................................................... ............................................ 37
Sección 10.7: CASO en la cláusula ORDER BY para ordenar registros por el valor más bajo de 2 columnas ...................................... 38
Capítulo 11: Operador LIKE .............................................. .................................................... ........................................ 39
Sección 11.1: Emparejar patrón abierto ........................................... .................................................... ........................ 39 Sección 11.2: Coincidencia
de un solo carácter Sección 11.3: Declaración .................................................... .................................................... ..................... 40
ESCAPE en la consulta LIKE ......... .................................................... .......................................... 40 Sección 11.4: Búsqueda de un rango de
caracteres Sección 11.5: Partido por rango o set .................................................... .................................................... ..... 41
.................................................... .................................................... ....................... 41
Sección 11.6: Caracteres comodín .................................................... .................................................... .......................... 41
Capítulo 12: Cláusula IN Sección .................................................... .................................................... ............................................. 43
12.1: Cláusula IN simple Sección .................................................... .................................................... .................................. 43
12.2: Uso de la cláusula IN con una subconsulta ............... .................................................... ....................... 43 Capítulo 13: Filtrar resultados
usando WHERE y HAVING .................................................... ............................... 44
Sección 13.1: Usar ENTRE para filtrar resultados .................................................... .................................................... ......... 44
Sección 13.2: Usar HAVING con funciones agregadas Sección 13.3: .................................................... ............................................ 45
Cláusula WHERE con valores NULL/NOT NULL Sección 13.4: .................................................... .................................... 45
Igualdad ................................. .................................................... .................................................... ................... 46 Sección 13.5: La cláusula WHERE
solo devuelve filas que coinciden con sus criterios .................................................... ......... 46
Sección 13.6: Y y O .................................................... .................................................... ............................................. 46
Sección 13.7: Use IN para devolver filas con un valor contenido en una lista .................................................... ..................... 47
Sección 13.8: Use LIKE para encontrar cadenas y subcadenas coincidentes ....................................... ...................................... 47 Sección 13.9:
Donde EXISTE .................................................... .................................................... ..................................... 48
Sección 13.10: Use HAVING para verificar múltiples condiciones en un grupo .................................. .......................... 48 Capítulo 14: SALTO DE
TOMA (Paginación) .............. .................................................... .................................................... 50
Sección 14.1: Limitar la cantidad de resultados .................................................... .................................................... ................ 50
Sección 14.2: Omitir y luego tomar algunos resultados (Paginación) ................................ .......................................... 50 Sección 14.3: Saltarse
algunas filas del resultado .................................................... .................................................... ...... 51
Capítulo 15: EXCEPTO .................................................... .................................................... ................................................ 52
Sección 15.1: Seleccionar conjunto de datos excepto donde los valores están en este otro .................................................... ........ 52
conjunto de datos Capítulo 16: EXPLICAR y DESCRIBIR .................................................... .................................................... .............. 53
Sección 16.1: EXPLICAR Seleccionar consulta ........................................... .................................................... ............................. 53 Sección 16.2:
DESCRIBE tablename; .................................................... .................................................... ....................... 53
Capítulo 17: CLÁUSULA EXISTE .................................................... .................................................... ............................... 54
Sección 17.1: CLÁUSULA EXISTE .................................................... .................................................... .................................... 54
Capítulo 18: ÚNETE .................................................... .................................................... .................................................... .... 55
Machine Translated by Google
Sección 18.1: Unión propia .................................................... .................................................... .......................................................... 55
Sección 18.2: Diferencias entre uniones internas/externas ........................................... .................................................... .... 56 Sección 18.3: JOIN
Terminología: Interior, Exterior, Semi, Anti.. .................................................... ..................................... 59
Sección 18.4: Unión exterior izquierda .................................................... .................................................... .................................... 69
Sección 18.5: Unión implícita .................................................... .................................................... ........................................ 70
Sección 18.6: UNIÓN CRUZADA .................................................... .................................................... ............................................. 71
Sección 18.7: APLICACIÓN CRUZADA Y UNIÓN LATERAL .................................................... .................................................... ...... 72
Sección 18.8: UNIÓN COMPLETA .................................................... .................................................... .......................................... 73
Sección 18.9: JOIN recursivos .................................................... .................................................... ................................ 74
Sección 18.10: Unión interna explícita básica ........................................... .................................................... .......................... 74 Sección 18.11:
Unión en una subconsulta .................. .................................................... .................................................... ...... 75
Capítulo 19: ACTUALIZACIÓN .................................................... .................................................... .......................................................... 76
Sección 19.1: ACTUALIZAR con datos de otra tabla .................................................... ............................................................. 76
Sección 19.2: Modificación de valores existentes .................................................... .................................................... .................. 77
Sección 19.3: Actualización de filas especificadas .................................................... .................................................... .................. 77
Sección 19.4: Actualización de todas las .................................................... .................................................... ............................. 77
filas Sección 19.5: Captura de registros actualizados .................................................... .................................................... ............. 77
Capítulo 20: Base de datos CREATE .................................................... .................................................... ........................ 78
Sección 20.1: Base de datos CREAR .................................................... .................................................... ............................. 78
Capítulo 21: CREAR TABLA Sección 21.1: .................................................... .................................................... ................................ 79
Crear tabla a partir de Seleccionar .................................................... .................................................... .................. 79
Sección 21.2: Crear una nueva tabla .................................................... .................................................... .......................... 79
Sección 21.3: CREAR TABLA CON CLAVE EXTRANJERA .................................................... .................................................... . 79
Sección 21.4: Duplicar una tabla .................................................... .................................................... ............................... 80
Sección 21.5: Crear una tabla temporal o en memoria .................................................... ........................................ 80
Capítulo 22: CREAR FUNCIÓN .................................................... .................................................... ....................... 82
Sección 22.1: Crear una nueva función .................................................... .................................................... ...................... 82
Capítulo 23: INTENTAR/ATRAPAR ........................................... .................................................... ............................................ 83
Sección 23.1: Transacción en un TRY/CATCH ........................................... .................................................... ................... 83
Capítulo 24: UNION / UNION ALL Sección 24.1: .................................................... .................................................... ................... 84
Consulta UNION ALL básica .................................. .................................................... .................................. 84 Sección 24.2: Explicación simple y
ejemplo ........ .................................................... ............................................. 85
Capítulo 25: ALTER TABLE .................................................... .................................................... .................................... 86
Sección 25.1: Añadir columna(s) ........................................... .................................................... .......................................... 86 Sección 25.2: Sección
de columna desplegable 25.3: .................................................... .................................................... ..................................... 86
Agregar clave principal ............................................. .................................................... .................................... 86 Sección 25.4: Alterar columna
.................................................... .................................................... ..................................... 86
Sección 25.5: Restricción de caída .................................................... .................................................... ................................ 86
Capítulo 26: INSERTAR .................................................... .................................................... ................................................ 87
Sección 26.1: INSERTAR datos de otra tabla usando SELECCIONAR Sección .................................................... ............................... 87
26.2: Insertar nueva fila .................................................... .................................................... .................................. 87
Sección 26.3: Insertar solo columnas especificadas .................................................... .................................................... ........ 87
Sección 26.4: Insertar varias filas a la vez Capítulo .................................................... .................................................... ............ 87
27: FUSIONAR Sección 27.1: .................................................... .................................................... ................................................ 88
FUSIONAR para hacer que el objetivo coincida con el origen .................................................... ............................................. 88
Sección 27.2: MySQL: contar usuarios por nombre Sección .................................................... .................................................... .... 88
27.3: PostgreSQL: contar usuarios por nombre .................................................... ............................................................. 88
Capítulo 28: aplicación cruzada, aplicación externa .................................. .................................................... .................... 90
Sección 28.1: Conceptos básicos de APLICACIÓN CRUZADA y APLICACIÓN EXTERNA .................................................... ............................................. 90
Machine Translated by Google
Capítulo 29: ELIMINAR .................................................... .................................................... .......................................................... 92
Sección 29.1: ELIMINAR todas las filas .................................................... .................................................... .................................. 92
Sección 29.2: ELIMINAR ciertas filas con DONDE .................................................... .................................................... .. 92
Sección 29.3: Cláusula TRUNCATE .................................................... .................................................... ............................ 92
Sección 29.4: ELIMINAR ciertas filas según las comparaciones con otras tablas .......................................................... 92
capitulo 30: truncamiento .................................................... .................................................... ........................................ 94
Sección 30.1: Eliminación de todas las filas de la tabla Empleado .................................................... .................................... 94
Capítulo 31: Tabla DROP .................................................... .................................................... ...................................... 95
Sección 31.1: Verifique la existencia antes de descartar ........................................... .................................................... ....... 95 Sección 31.2:
Caída simple ...................................... .................................................... .................................................... ... 95
Capítulo 32: DROP o DELETE Database .................................................... .................................................... ..... 96
Sección 32.1: Base de datos DROP .................................................... .................................................... ................................ 96
Capítulo 33: Eliminación en cascada .................................................... .................................................... .......................... 97
Sección 33.1: EN ELIMINAR CASCADA .................................................... .................................................... ....................... 97
Capítulo 34: OTORGAR y REVOCAR Sección 34.1: .................................................... .................................................... ................... 99
Otorgar/revocar privilegios ........................................... .................................................... .......................... 99 Capítulo 35: XML Sección 35.1:
Consulta desde el tipo de .................................................... .................................................... .................................................... .. 100
datos XML .... .................................................... .................................................... ....... 100 Capítulo 36: Teclas
primarias ...................................... .................................................... ............................................. 101 Sección 36.1: Crear una clave
principal ................................................. .................................................... ..................... 101 Sección 36.2: Uso del incremento automático
Capítulo 37: Índices .................................................... .................................................... ..................... 101
.................................................... .................................................... ............................................. 102
Sección 37.1: Índice ordenado .................................................... .................................................... ..................................... 102
Sección 37.2: Índice parcial o filtrado .................................................... .................................................... .................. 102
Sección 37.3: Creación de un índice .................................................... .................................................... .......................... 102
Sección 37.4: Eliminación de un índice, o deshabilitación y reconstrucción .................................................... ....................... 103
Sección 37.5: Índices agrupados, únicos y ordenados .................................................... .......................................... 103
Sección 37.6: Índice de reconstrucción .................................................... .................................................... .................................... 104
Sección 37.7: Inserción con un índice único .................................................... .................................................... ........ 104
Capítulo 38: Número de fila Sección .................................................... .................................................... .................................... 105
38.1: Eliminar todo excepto el último registro (tabla de 1 a muchos) .................................. .................................................... . 105
Sección 38.2: Números de fila sin particiones ............................................... .................................................... .......... 105 Sección 38.3:
Números de fila con particiones .................................. .................................................... .......................... 105
Capítulo 39: SQL Group By vs Distinct .................................................... .................................................... ........ 106
Sección 39.1: Diferencia entre GROUP BY y DISTINCT .................................................... ............................... 106
Capítulo 40: Búsqueda de duplicados en un subconjunto de columnas con detalle .................................................... .. 107
Sección 40.1: Estudiantes con el mismo nombre y fecha de nacimiento .................................................... .................................. 107
Capítulo 41: Funciones de cadena .................................................... .................................................... .......................... 108
Sección 41.1: Concatenar .................................................... .................................................... ..................................... 108
Sección 41.2: Longitud ............................................... .................................................... .................................................... 108
Sección 41.3: Recorte de espacios vacíos ........................................... .................................................... ............................... 109
Sección 41.4: Mayúsculas y minúsculas .................................................... .................................................... ......................... 109
Sección 41.5: División ....................................... .................................................... .................................................... ............ 109 Sección 41.6:
Reemplazar .................................. .................................................... .................................................... ............ 110 Sección 41.7: REGEXP
.................................................... .................................................... ............................................. 110
Sección 41.8: Subcadena ............................................... .................................................... ............................................. 110 Sección 41.9: Stu
.................................................... .................................................... .................................................... 110
Sección 41.10: IZQUIERDA DERECHA .................................................... .................................................... .................................... 110
Machine Translated by Google
Sección 41.11: MARCHA ATRÁS .................................................... .................................................... .......................................... 111
Sección 41.12: RÉPLICA .................................................... .................................................... ...................................... 111
Sección 41.13: Reemplazar función en sql Seleccionar y actualizar consulta ........................................... .................................... 111
Sección 41.14: INSTR. .................................................... .................................................... .......................................................... 112
Sección 41.15: NOMBRE PARSE .................................................... .................................................... .................................... 112
Capítulo 42: Funciones (agregado) ........................................... .................................................... ........... 114 Sección 42.1: Agregación
condicional ......................... .................................................... ....................................... 114 Sección 42.2: Concatenación de listas
.................................................... .................................................... ......................... 114
Sección 42.3: SUMA .................................................... .................................................... .................................................... 116
Sección 42.4: PROMEDIO () ........................................... .................................................... .................................................... ... 116 Sección 42.5:
Recuento .................................................... .................................................... .......................................................... 116
Sección 42.6: Mín. .................................................... .................................................... .................................................... .. 117
Sección 42.7: Máx. .................................................... .................................................... .................................................... . 118
Capítulo 43: Funciones (escalares/fila única) ........................................... .................................................... ..... 119
Sección 43.1: Fecha y Hora .................................................... .................................................... .................................. 119
Sección 43.2: Modificaciones de carácter .................................................... .................................................... ................ 120
Sección 43.3: Función de configuración y conversión Sección 43.4: .................................................... ........................................ 120
Función lógica y matemática Capítulo 44: Funciones .................................................... .......................................................... 121
(analíticas) ............................. .................................................... ...................................... 123
Sección 44.1: LAG y LEAD .................................................... .................................................... .................................. 123
Sección 44.2: PERCENTILE_DISC y PERCENTILE_CONT ........................................... ...................................... 123
Sección 44.3: PRIMER_VALOR ............................................... .................................................... ...................................... 124 Sección 44.4:
ÚLTIMO_VALOR ....... .................................................... .................................................... ............................ 125 Sección 44.5:
PERCENT_RANK y CUME_DIST ............... .................................................... .................................... 125
Capítulo 45: Funciones de ventana .................................................... .................................................... ..................... 127
Sección 45.1: Configuración de una bandera si otras filas tienen una propiedad común .................................. .......................... 127 Sección 45.2:
Búsqueda de registros "fuera de secuencia" mediante la función LAG() Sección 45.3: .................................................... ..... 127
Obtención de un total acumulado Sección .................................................... .................................................... ................... 128
45.4: Adición del total de filas seleccionadas a cada fila Sección 45.5: .................................................... ................................ 128
Obtención de las N filas más recientes en agrupaciones múltiples .............. ...................................... 129
Capítulo 46: Expresiones de tabla comunes ........................................... .................................................... ....... 130
Sección 46.1: generación de valores .................................................... .................................................... .......................... 130
Sección 46.2: enumeración recursiva de un subárbol Sección .................................................... ................................................ 130
46.3: consulta temporal ................................ .................................................... ............................................ 131
Sección 46.4: Subir recursivamente en un árbol .................................................... .................................................... ....... 131
Sección 46.5: Generar fechas recursivamente, ampliado para incluir la lista de equipos como ejemplo ........... 132 Sección 46.6: Funcionalidad
Oracle CONNECT BY con CTE recursivos .................................................... ............... 132
Capítulo 47: Vistas Sección .................................................... .................................................... ................................................ 134
47.1: Vistas simples Sección .................................................... .................................................... ..................................... 134
47.2: Vistas complejas .................................................... .................................................... ................................ 134
Capítulo 48: Vistas materializadas .................................................... .................................................... ..................... 135
Sección 48.1: Ejemplo de PostgreSQL ............................................... .................................................... .......................... 135
Capítulo 49: Comentarios Sección .................................................... .................................................... ..................................... 136
49.1: Comentarios de una sola línea Sección .................................................... .................................................... ..................... 136
49.2: Comentarios de varias líneas .................................................... .................................................... ....................... 136
Capítulo 50: Claves foráneas ............................................. .................................................... ...................................... 137 Sección 50.1: Explicación
de las claves foráneas ..... .................................................... .................................................... ............. 137 Sección 50.2: Crear una tabla con
una clave foránea ........................... .................................................... ...................... 137 Capítulo 51:
Secuencia ....................... .................................................... .................................................... .................. 139
Machine Translated by Google
Sección 51.1: Crear Secuencia ............................................... .................................................... .......................... 139 Sección 51.2: Uso de
secuencias ......... .................................................... .................................................... .................... 139 Capítulo 52:
Subconsultas ......................... .................................................... .................................................... ............ 140
Sección 52.1: Subconsulta en la cláusula FROM .................................................... .................................................... .............. 140
Sección 52.2: Subconsulta en la cláusula SELECT .................................................... .................................................... ........... 140
Sección 52.3: Subconsulta en la cláusula WHERE .................................................... .................................................... ........... 140
Sección 52.4: Subconsultas correlacionadas ........... .................................................... ............................................. 140 Sección 52.5: Filtro
consultar los resultados usando una consulta en una tabla diferente Sección .................................................... ....................... 140
52.6: Subconsultas en la cláusula FROM Sección .................................................... .................................................... ........... 141
52.7: Subconsultas en la cláusula WHERE Capítulo .................................................... .................................................... ........ 141
53: Bloques de ejecución Sección 53.1: Uso .................................................... .................................................... ......................... 142
de BEGIN ... END Capítulo 54: .................................................... .................................................... ......................... 142
Procedimientos almacenados .................................................... .................................................... ..................... 143
Sección 54.1: Crear y llamar a un procedimiento almacenado .................................. .................................................... ....... 143 Capítulo 55:
Disparadores ...................................... .................................................... .................................................... ..... 144
Sección 55.1: CREAR DISPARADOR .................................................... .................................................... ............................ 144
Sección 55.2: Use Trigger para administrar una "Papelera de reciclaje" para los elementos eliminados .................................................... .......... 144
Capítulo 56: Transacciones .................................................... .................................................... .................................. 145
Sección 56.1: Transacción simple .................................................... .................................................... ......................... 145
Sección 56.2: Transacción de reversión .................................................... .................................................... ..................... 145
Capítulo 57: Diseño de la tabla ........................................... .................................................... ...................................... 146
Sección 57.1: Propiedades de una tabla bien diseñada .................................................... .......................................................... 146
Capítulo 58: Sinónimos ....................................... .................................................... .................................................... . 147
Sección 58.1: Crear sinónimo ............................................... .................................................... .................................... 147
Capítulo 59: Esquema de información .................................................... .................................................... .................. 148
Sección 59.1: Búsqueda de esquema de información básica .................................................... .................................................... 148
Capítulo 60: Orden de ejecución .................................................... .................................................... ..................... 149
Sección 60.1: Orden lógico del procesamiento de consultas en SQL .................................. ............................................. 149 Capítulo 61 :
Código limpio en SQL .............................................. .................................................... ............................ 150
Sección 61.1: Formato y ortografía de palabras clave y nombres Sección 61.2: .................................................... ........................ 150
Sangría ........................................... .................................................... .................................................... ...... 150
Sección 61.3: SELECCIONAR * .................................................... .................................................... .......................................... 151
Sección 61.4: Uniones .................................................... .................................................... .......................................................... 152
Capítulo 62: Inyección SQL ............................................. .................................................... ...................................... 153
Sección 62.1: ejemplo de inyección de SQL .................................. .................................................... ............................ 153
Sección 62.2: muestra de inyección simple ........................................... .................................................... ....................... 154
Créditos .................................................... .................................................... .................................................... ...................... 155
También te puede interesar .................................................... .................................................... .................................................... 159
Machine Translated by Google
Acerca de
No dude en compartir este PDF con cualquier persona de forma
gratuita, la última versión de este libro se puede descargar desde:
https://goalkicker.com/SQLBook
Este libro SQL Notes for Professionals está compilado a partir de la documentación de
desbordamiento de pila , el contenido está escrito por la hermosa gente de Stack Overflow.
El contenido del texto se publica bajo Creative Commons BYSA, vea los créditos al final de este libro
que contribuyeron a los diversos capítulos. Las imágenes pueden ser propiedad de sus respectivos
propietarios a menos que se especifique lo contrario
Este es un libro gratuito no oficial creado con fines educativos y no está afiliado a grupos o
empresas oficiales de SQL ni a Stack Overflow. Todas las marcas comerciales y marcas
comerciales registradas son propiedad de sus respectivos
dueños de la empresa
No se garantiza que la información presentada en este libro sea correcta ni precisa, utilícela bajo
su propio riesgo.
Envíe comentarios y correcciones a [email protected]
GoalKicker.com Notas SQL para profesionales 1
Machine Translated by Google
Capítulo 1: Primeros pasos con SQL
Versión Nombre abreviado Estándar 1986 SQL86 ANSI Fecha de
X3.1351986, ISO 9075:1987 lanzamiento 19860101
1989 SQL89 ANSI X3.1351989, ISO/IEC 9075:1989 19890101
1992 SQL92 ISO/CEI 9075:1992 19920101
1999 SQL:1999 ISO/CEI 9075:1999 19991216
2003 SQL:2003 ISO/CEI 9075:2003 20031215
2006 SQL:2006 ISO/CEI 9075:2006 20060601
2008 SQL:2008 ISO/CEI 9075:2008 20080715
2011 SQL:2011 ISO/CEI 9075:2011 20111215
2016 SQL:2016 ISO/IEC 9075:2016 20161201
Sección 1.1: Resumen
El lenguaje de consulta estructurado (SQL) es un lenguaje de programación de propósito especial diseñado para administrar datos almacenados
en un sistema de administración de bases de datos relacionales (RDBMS). Los lenguajes similares a SQL también se pueden usar en sistemas
de gestión de flujo de datos relacionales (RDSMS) o en bases de datos "no solo SQL" (NoSQL).
SQL se compone de 3 sublenguajes principales:
1. Lenguaje de definición de datos (DDL): para crear y modificar la estructura de la base de datos; 2. Lenguaje
de manipulación de datos (DML): para realizar operaciones de lectura, inserción, actualización y eliminación en los datos de
la base de datos;
3. Lenguaje de Control de Datos (DCL): para controlar el acceso de los datos almacenados en la base de datos.
Artículo de SQL en Wikipedia
Las operaciones principales de DML son Crear, Leer, Actualizar y Eliminar (CRUD para abreviar), que se realizan mediante las
instrucciones INSERTAR, SELECCIONAR, ACTUALIZAR y ELIMINAR.
También hay una declaración MERGE (recientemente agregada) que puede realizar las 3 operaciones de escritura (INSERTAR, ACTUALIZAR,
ELIMINAR).
Artículo CRUD en Wikipedia
Muchas bases de datos SQL se implementan como sistemas cliente/servidor; el término "servidor SQL" describe dicha base de datos.
Al mismo tiempo, Microsoft crea una base de datos que se llama "SQL Server". Si bien esa base de datos habla un dialecto de SQL, la información
específica de esa base de datos no está en el tema de esta etiqueta, sino que pertenece a la documentación de SQL Server.
GoalKicker.com Notas SQL para profesionales 2
Machine Translated by Google
Capítulo 2: Identificador
Este tema trata sobre identificadores, es decir, reglas de sintaxis para nombres de tablas, columnas y otros objetos de bases de datos.
Cuando corresponda, los ejemplos deben cubrir las variaciones utilizadas por diferentes implementaciones de SQL o identificar la implementación de SQL del ejemplo.
Sección 2.1: Identificadores sin comillas
Los identificadores sin comillas pueden usar letras (az), dígitos (09) y guión bajo (_), y deben comenzar con una letra.
Según la implementación de SQL y/o la configuración de la base de datos, se pueden permitir otros caracteres, algunos incluso como el primer carácter, p.
MS SQL: @, $, # y otras letras Unicode (fuente)
MySQL: $ (fuente)
Oracle: $, # y otras letras del conjunto de caracteres de la base de datos (fuente)
PostgreSQL: $ y otras letras Unicode (fuente)
Los identificadores sin comillas no distinguen entre mayúsculas y minúsculas. Cómo se maneja esto depende en gran medida de la implementación de SQL:
MS SQL: Conservación de mayúsculas y minúsculas, sensibilidad definida por el conjunto de caracteres de la base de datos, por lo que puede distinguir entre mayúsculas y minúsculas.
MySQL: Conservación de mayúsculas y minúsculas, la sensibilidad depende de la configuración de la base de datos y del sistema de archivos subyacente.
Oracle: convertido a mayúsculas, luego manejado como identificador entrecomillado.
PostgreSQL: convertido a minúsculas, luego manejado como identificador entrecomillado.
SQLite: preservación de mayúsculas y minúsculas; insensibilidad a mayúsculas y minúsculas solo para caracteres ASCII.
GoalKicker.com Notas SQL para profesionales 3
Machine Translated by Google
Capítulo 3: Tipos de datos
Sección 3.1: DECIMAL y NUMÉRICO
Números decimales de escala y precisión fija. DECIMAL y NUMERIC son funcionalmente equivalentes.
Sintaxis:
DECIMAL ( precisión [ , escala] )
NUMÉRICO ( precisión [ , escala] )
Ejemplos:
SELECCIONAR CAST(123 COMO DECIMAL(5,2)) devuelve 123,00
SELECCIONA CAST(12345,12 COMO NUMÉRICO(10,5)) devuelve 12345,12000
Sección 3.2: FLOTANTE y REAL
Tipos de datos de números aproximados para usar con datos numéricos de punto flotante.
SELECCIONE CAST( PI() COMO FLOTANTE) devuelve 3,14159265358979
SELECCIONE CAST( PI() COMO REAL) devuelve 3,141593
Sección 3.3: Enteros
Tipos de datos de números exactos que utilizan datos enteros.
Tipo de datos Rango Bigint de
almacenamiento 2^63 (9,223,372,036,854,775,808) a 2^631 (9,223,372,036,854,775,807) 8 bytes 2^31 (2,147,483,648)
En t a 2^311 (2,147,483,647) 4 bytes
de entero pequeño 2 ^ 15 (32 768) a 2 ^ 151 (32 767) 2 bytes
de espesor 0 a 255 1 byte
Sección 3.4: DINERO y DINERO PEQUEÑO
Tipos de datos que representan valores monetarios o de moneda.
tipo de dato Intervalo Almacenamiento 922 337 203 685 477,5808
dinero a 922 337 203 685 477,5807 8 bytes 4 bytes
dinero pequeño 214.748,3648 a 214.748,3647
Sección 3.5: BINARIO y VARBINARIO
Tipos de datos binarios de longitud fija o longitud variable.
Sintaxis:
BINARIO [ ( n_bytes ) ]
VARBINARIO [ ( n_bytes | máx ) ]
n_bytes puede ser cualquier número de 1 a 8000 bytes. max indica que el espacio de almacenamiento máximo es 2^311.
GoalKicker.com Notas SQL para profesionales 4
Machine Translated by Google
Ejemplos:
SELECCIONE CAST(12345 COMO BINARIO(10)) 0x0000000000000003039
SELECCIONE CAST (12345 COMO VARBINARIO (10)) 0x00003039
Sección 3.6: CHAR y VARCHAR
Cadena de tipos de datos de longitud fija o longitud variable.
Sintaxis:
CARÁCTER [ ( n_caracteres ) ]
VARCHAR [ ( n_caracteres ) ]
Ejemplos:
SELECT CAST('ABC' AS CHAR(10)) 'ABC ' (rellenado con espacios a la derecha)
SELECT CAST('ABC' AS VARCHAR(10)) 'ABC' (sin relleno debido al carácter variable)
SELECT CAST('ABCDEFGHIJKLMNOPQRSTUVWXYZ' AS CHAR(10)) 'ABCDEFGHIJ' (truncado a 10 caracteres)
Sección 3.7: NCHAR y NVARCHAR
Tipos de datos de cadena UNICODE de longitud fija o longitud variable.
Sintaxis:
NCHAR [ ( n_chars ) ]
NVARCHAR [ ( n_chars | MAX ) ]
Use MAX para cadenas muy largas que pueden exceder los 8000 caracteres.
Sección 3.8: IDENTIFICADOR ÚNICO
Un GUID/UUID de 16 bytes.
DECLARAR @GUID IDENTIFICADOR ÚNICO = NEWID();
SELECCIONE @GUID 'E28B3BD9917441A98508899A78A33540'
DECLARAR @bad_GUID_string VARCHAR(100) = 'E28B3BD9917441A98508899A78A33540_foobarbaz'
SELECCIONAR
@bad_GUID_string, 'E28B3BD9917441A98508899A78A33540_foobarbaz'
CONVERTIR (IDENTIFICADOR ÚNICO, @bad_GUID_string) 'E28B3BD9917441A98508899A78A33540'
GoalKicker.com Notas SQL para profesionales 5
Machine Translated by Google
Capítulo 4: NULO
NULL en SQL, así como en la programación en general, significa literalmente "nada". En SQL, es más fácil de entender como "la
ausencia de cualquier valor".
Es importante distinguirlo de valores aparentemente vacíos, como la cadena vacía '' o el número 0, ninguno de los cuales es NULL.
También es importante tener cuidado de no incluir NULL entre comillas, como 'NULL', que está permitido en columnas que aceptan texto, pero no es NULL y puede
causar errores y conjuntos de datos incorrectos.
Sección 4.1: Filtrado por NULL en consultas
La sintaxis para filtrar por NULL (es decir, la ausencia de un valor) en bloques WHERE es ligeramente diferente a filtrar por valores específicos.
SELECCIONE * DE Empleados DONDE ManagerId ES NULL ;
SELECCIONE * DE Empleados DONDE ManagerId NO ES NULO ;
Tenga en cuenta que debido a que NULL no es igual a nada, ni siquiera a sí mismo, el uso de operadores de igualdad = NULL o <>
NULL (o != NULL) siempre arrojará el valor de verdad de UNKNOWN que será rechazado por WHERE.
WHERE filtra todas las filas en las que la condición es FALSA o UKNOWN y mantiene solo las filas en las que la condición es VERDADERA.
Sección 4.2: Columnas anulables en tablas
Al crear tablas, es posible declarar una columna como anulable o no anulable.
CREAR TABLA MiTabla (
MyCol1 INT NOT NULL, no anulable
MyCol2 INT NULL anulable
) ;
De forma predeterminada, todas las columnas (excepto aquellas en la restricción de clave principal) pueden anularse a menos que establezcamos explícitamente la
restricción NOT NULL .
Si intenta asignar NULL a una columna que no admite valores NULL, se producirá un error.
INSERTAR EN MyTable (MyCol1, MyCol2) VALORES (1, NULL) ; funciona bien
INSERTAR EN MyTable (MyCol1, MyCol2) VALORES (NULL, 2) ;
no se puede insertar
el valor NULL en la columna 'MyCol1', table 'MyTable'; la columna no
permite valores nulos. INSERTAR falla.
Sección 4.3: Actualización de campos a NULL
Establecer un campo en NULL funciona exactamente igual que con cualquier otro valor:
ACTUALIZAR Empleados
SET ManagerId = NULL
DONDE ID = 4
GoalKicker.com Notas SQL para profesionales 6
Machine Translated by Google
Sección 4.4: Insertar filas con campos NULL
Por ejemplo, insertar un empleado sin número de teléfono y sin gerente en la tabla de ejemplo Empleados:
INSERTAR EN Empleados
(Id, FName, LName, PhoneNumber, ManagerId, DepartmentId, Salary, HireDate)
VALORES
(5, 'Jane', 'Doe', NULL, NULL, 2, 800, '20160722') ;
GoalKicker.com Notas SQL para profesionales 7
Machine Translated by Google
Capítulo 5: Bases de datos y tablas de ejemplo
Sección 5.1: Base de datos de talleres de automóviles
En el siguiente ejemplo: base de datos para un negocio de taller de automóviles, tenemos una lista de departamentos, empleados,
clientes y automóviles de clientes. Estamos utilizando claves externas para crear relaciones entre las distintas tablas.
Ejemplo en vivo: violín SQL
Relaciones entre tablas
Cada Departamento puede tener 0 o más Empleados
Cada Empleado puede tener 0 o 1 Gerente
Cada Cliente puede tener 0 o más Autos
Departamentos
Nombre de identificación
1 hora
2 Ventas
3 tecnología
Sentencias SQL para crear la tabla:
CREAR TABLA Departamentos (
Id INT NO NULO AUTO_INCREMENT,
Nombre VARCHAR(25) NO NULO,
CLAVE PRIMARIA (Id)
);
INSERTAR EN Departamentos
([Identificación], [Nombre])
VALORES
(1, 'HR'),
(2, 'Ventas'),
(3, 'Tecnología')
;
Empleados
Id FName LName Número de teléfono ID del gerente ID del departamento Salario Fecha de contratación
1 Santiago Smith 1234567890 NULO 1 1000 01012002
2 Juan Johnson 2468101214 1 1 400 23032005
3 Michael Williams 1357911131 1 2 600 05122009
4 jonathan smith 1212121212 2 1 500 24072016
Sentencias SQL para crear la tabla:
CREAR TABLA Empleados (
Id INT NO NULO AUTO_INCREMENT,
FName VARCHAR(35) NO NULO,
LNombre VARCHAR(35) NO NULO,
Número de teléfono VARCHAR(11),
ManagerId INT,
DepartmentId INT NO NULO,
GoalKicker.com Notas SQL para profesionales 8
Machine Translated by Google
Salario INT NO NULO,
HireDate DATETIME NO NULO,
CLAVE PRIMARIA (Id),
CLAVE EXTRANJERA (ManagerId) REFERENCIAS Empleados (Id),
CLAVE EXTRANJERA (DepartmentId) REFERENCIAS Departamentos (Id)
);
INSERTAR EN Empleados
([Id], [FName], [LName], [PhoneNumber], [ManagerId], [DepartmentId], [Salary], [HireDate])
VALORES
(1, 'James', 'Smith', 1234567890, NULL, 1, 1000, '01012002'),
(2, 'Juan', 'Johnson', 2468101214, '1', 1, 400, '23032005'),
(3, 'Miguel', 'Williams', 1357911131, '1', 2, 600, '12052009'),
(4, 'Johnathon', 'Smith', 1212121212, '2', 1500 , '24072016')
;
Clientes
Id FName LName Correo Número de teléfono PreferidoContacto
electrónico 1 William Jones [email protected] 3347927472 TELÉFONO
2 David Miller [email protected] 3 Richard 2137921892 CORREO ELECTRÓNICO
Davis [email protected] NULL CORREO ELECTRÓNICO
Sentencias SQL para crear la tabla:
CREAR TABLA Clientes (
Id INT NO NULO AUTO_INCREMENT,
FName VARCHAR(35) NO NULO,
LNombre VARCHAR(35) NO NULO,
Correo electrónico varchar (100) NO NULO,
Número de teléfono VARCHAR(11),
PreferredContact VARCHAR(5) NOT NULL,
CLAVE PRIMARIA (Id)
);
INSERTAR EN Clientes ([Id],
[FName], [LName], [Email], [PhoneNumber], [PreferredContact])
VALORES (1, 'William', 'Jones', '[email protected]', '3347927472', 'TELÉFONO'), (2, 'David', 'Miller',
'[email protected]', '2137921892', 'CORREO ELECTRÓNICO'), (3, 'Richard', 'Davis',
'[email protected]', NULL, 'CORREO ELECTRÓNICO')
;
Carros
Id CustomerId EmployeeId Modelo Estado Costo total
2 Ford F150 LISTO 230 1 1
2 1 2 Ford F150 LISTO 200
3 2 1 Ford Mustang ESPERANDO 100
4 3 3 Toyota Prius TRABAJANDO 1254
Sentencias SQL para crear la tabla:
CREAR TABLA Coches (
Id INT NO NULO AUTO_INCREMENT,
CustomerId INT NO NULO,
EmployeeId INT NO NULO,
Modelo varchar(50) NO NULO,
Estado varchar (25) NO NULO,
GoalKicker.com Notas SQL para profesionales 9
Machine Translated by Google
TotalCost INT NO NULO,
CLAVE PRIMARIA (Id),
CLAVE EXTRANJERA (IdCliente) REFERENCIAS Clientes(Id),
CLAVE EXTRANJERA (EmployeeId) REFERENCIAS Empleados (Id)
);
INSERTAR EN Coches
([Id], [CustomerId], [EmployeeId], [Model], [Status], [TotalCost])
VALORES
('1', '1', '2', 'Ford F150', 'LISTO', '230'),
('2', '1', '2', 'Ford F150', 'LISTO', '200'),
('3', '2', '1', 'Ford Mustang', 'ESPERANDO', '100'),
('4', '3', '3', 'Toyota Prius', 'TRABAJANDO', '1254')
;
Sección 5.2: Base de datos de la biblioteca
En esta base de datos de ejemplo para una biblioteca, tenemos las tablas Authors, Books y BooksAuthors .
Ejemplo en vivo: violín SQL
Los autores y los libros se conocen como tablas base, ya que contienen definición de columna y datos para las entidades reales en el modelo relacional.
BooksAuthors se conoce como la tabla de relaciones, ya que esta tabla define la relación entre la tabla Books y Authors .
Relaciones entre tablas
Cada autor puede tener 1 o más libros.
Cada libro puede tener 1 o más autores
Autores
(ver tabla)
Identificación País
Nombre 1 JD ciervo
Salinger 2 F. Scott. Fitzgerald
EE.UU. 3 Jane Austen Reino Unido
4 Scott Hanselman Estados Unidos
5 Jason N. Gaylord Estados
Unidos 6 Pranav Rastogi India
7 Todd Miranda Estados Unidos
8 Christian Wenz Estados Unidos
SQL para crear la tabla:
CREAR TABLA Autores (
Id INT NO NULO AUTO_INCREMENT,
Nombre VARCHAR(70) NO NULO,
País VARCHAR(100) NO NULO,
CLAVE PRIMARIA (Id)
);
INSERTAR EN Autores
GoalKicker.com Notas SQL para profesionales 10
Machine Translated by Google
(Nombre , País )
VALORES ('JD Salinger' , 'USA' ), ('F. Scott.
Fitzgerald' , 'USA' ), ('Jane Austen'
, 'Reino
Unido' ) , ('Scott Hanselman', 'EE.UU.' ) ,
('Jason N. Gaylord' , 'EE.UU.' ) , ('Pranav
Rastogi' , 'India' ) , ('Todd Miranda'
, 'Estados
Unidos' ) , ('Christian Wenz' , 'Estados Unidos' )
Libros
(ver tabla )
Título de identificación
1 El guardián entre el centeno
2 nueve historias
3 Franny y Zooey
4 El gran Gatsby
5 Tierna identificación de la noche
6 Orgullo y prejuicio
7 Profesional ASP.NET 4.5 en C# y VB
SQL para crear la tabla:
CREAR TABLA Libros (
Id INT NO NULL AUTO_INCREMENT ,
Título VARCHAR (50 ) NO NULO ,
CLAVE PRIMARIA (Id )
) ;
INSERTAR EN Libros
(identificación , Título )
VALORES
( 1 ,'El guardián entre el centeno' ), ( 2 'Nueve
,
historias' ), ( 3 , 'Franny y
Zooey' ), ( 4 'El gran Gatsby' ), ( 5
,
'Tender
id the Night' ), ( 6 'Orgullo
,
y prejuicio' ), ( 7 'Professional ASP.NET
, en C# y VB' )
4.5
,
;
LibrosAutores
(ver tabla)
ID del libro ID del autor
1 1
2 1
3 1
4 2
5 2
GoalKicker.com Notas SQL para profesionales 11
Machine Translated by Google
6 3
7 4
7 5
7 6
7 7
7 8
SQL para crear la tabla:
CREAR TABLA LibrosAutores (
AuthorId INT NO NULO,
BookId INT NO NULO,
CLAVE EXTRANJERA (AuthorId) REFERENCIAS Autores (Id),
CLAVE EXTRANJERA (BookId) REFERENCIAS Libros (Id)
);
INSERTAR EN LibrosAutores
(BookId, AuthorId)
VALORES (1,
1), (2,
1), (3,
1), (4,
2), (5,
2), (6,
3), (7,
4 ), (7,
5), ( 7,
6), (7,
7), (7, 8)
;
Ejemplos
Ver todos los autores (ver ejemplo en vivo):
SELECCIONE * DE Autores;
Ver todos los títulos de libros (ver ejemplo en vivo):
SELECCIONE * DE Libros;
Ver todos los libros y sus autores (ver ejemplo en vivo):
SELECCIONE
ba.AuthorId, a.Name
AuthorName,
ba.BookId, b.Title BookTitle
¿Es DE BooksAuthors?
INNER JOIN Autores a ON a.id = ba.authorid INNER
JOIN Libros b ON b.id = ba.bookid
;
GoalKicker.com Notas SQL para profesionales 12
Machine Translated by Google
Sección 5.3: Tabla de países
En este ejemplo, tenemos una tabla de países . Una tabla de países tiene muchos usos, especialmente en aplicaciones financieras que involucran
monedas y tipos de cambio.
Ejemplo en vivo: violín SQL
Algunas aplicaciones de software de datos de mercado como Bloomberg y Reuters requieren que proporcione a su API un código de país de
2 o 3 caracteres junto con el código de moneda. Por lo tanto, esta tabla de ejemplo tiene tanto la columna de código ISO de 2 caracteres como
las columnas de código ISO3 de 3 caracteres.
Países
(ver tabla)
Id ISO ISO3 ISONumérico CountryName Capital ContinentCode CurrencyCode
1 UA DE DESCUENTO 36 Australia Canberra CO AUD
2 DE DEU 276 Alemania Berlina UE EUR
2 EN IND 356 India Nueva Delhi AS EUR
3 LA LAO 418 Laos Vientián AS LAK
4 EE. UU. EE. UU. 840 Estados UnidosWashington NA Dólar estadounidense
5 ZW ZWE 716 Zimbabue Harare DE ZWL
SQL para crear la tabla:
CREAR TABLA Países (
Id INT NO NULO AUTO_INCREMENT,
ISO VARCHAR(2) NO NULO,
ISO3 VARCHAR(3) NO NULO,
ISONumérico INT NO NULO,
CountryName VARCHAR(64) NO NULO,
Capital VARCHAR(64) NO NULO,
ContinentCode VARCHAR(2) NO NULO,
Código de moneda VARCHAR(3) NO NULO,
CLAVE PRIMARIA (Id)
) ;
INSERTAR EN Países
(ISO, ISO3, ISONumeric, CountryName, Capital, ContinentCode, CurrencyCode)
VALORES
('AU', 'AUS', 36, 'Australia', 'Canberra', 'OC', 'AUD'),
('DE', 'DEU', 276, 'Alemania', 'Berlín', 'UE', 'EUR'),
('IN', 'IND', 356, 'India', 'Nueva Delhi', 'AS', 'INR'),
('LA', 'LAO', 418, 'Laos', 'Vientiane', 'AS', 'LAK'),
('US', 'USA', 840, 'Estados Unidos', 'Washington', 'NA', 'USD'),
('ZW', 'ZWE', 716, 'Zimbabue', 'Harare', 'AF', 'ZWL')
;
GoalKicker.com Notas SQL para profesionales 13
Machine Translated by Google
Capítulo 6: SELECCIONAR
La declaración SELECT está en el corazón de la mayoría de las consultas SQL. Define qué conjunto de resultados debe devolver la consulta y casi
siempre se usa junto con la cláusula FROM, que define qué partes de la base de datos deben consultarse.
Sección 6.1: Uso del carácter comodín para seleccionar todas las columnas en
una consulta
Considere una base de datos con las siguientes dos tablas.
Tabla de empleados:
Id FName LName DeptId
1 James Smith 3
2 Juan Johnson 4
Tabla de departamentos:
Nombre de identificación
1 Ventas
2 comercialización
3 Finanzas
4 TI
Declaración de selección simple
* es el carácter comodín utilizado para seleccionar todas las columnas disponibles en una tabla.
Cuando se utiliza como sustituto de los nombres de columna explícitos, devuelve todas las columnas de todas las tablas de las que se selecciona una
consulta . Este efecto se aplica a todas las tablas a las que accede la consulta a través de sus cláusulas JOIN .
Considere la siguiente consulta:
SELECCIONE * DE Empleados
Devolverá todos los campos de todas las filas de la tabla Empleados :
Id FName LName DeptId
1 James Smith 3
2 Juan Johnson 4
notación de puntos
Para seleccionar todos los valores de una tabla específica, el carácter comodín se puede aplicar a la tabla con notación de puntos.
Considere la siguiente consulta:
SELECCIONAR
Empleados.*,
Departamentos.Nombre
DESDE
Empleados
UNIRSE
GoalKicker.com Notas SQL para profesionales 14
Machine Translated by Google
Departamentos
ON Departamentos.Id = Empleados.DeptId
Esto devolverá un conjunto de datos con todos los campos en la tabla Empleado , seguido solo por el campo Nombre en la tabla
Departamentos :
Id FName LName DeptId Nombre
1 James Smith 3 Finanzas
2 Juan Johnson 4 ÉL
Advertencias contra el uso
En general, se recomienda evitar el uso de * en el código de producción siempre que sea posible, ya que puede causar una serie de
problemas potenciales, que incluyen:
1. Exceso de E/S, carga de red, uso de memoria, etc., debido a que el motor de la base de datos lee datos que no son necesarios y los
transmite al código frontend. Esto es especialmente preocupante cuando puede haber campos grandes, como los que se utilizan para
almacenar notas largas o archivos adjuntos.
2. Más exceso de carga de E/S si la base de datos necesita enviar resultados internos al disco como parte del procesamiento de un
consulta más compleja que SELECT <columns> FROM <table>.
3. Procesamiento adicional (y/o incluso más IO) si algunas de las columnas innecesarias son:
columnas calculadas en bases de datos que las admiten en el
caso de seleccionar desde una vista, columnas de una tabla/vista que el optimizador de consultas podría optimizar
de otro modo 4. El potencial
de errores inesperados si las columnas se agregan a las tablas y vistas más tarde que resultan ambiguas
nombres de columna Por ejemplo, SELECCIONE * DE pedidos ÚNASE a personas EN personas.id = pedidos.id de persona ORDEN
POR nombre de visualización : si se agrega una columna llamada nombre de visualización a la tabla de pedidos para permitir a
los usuarios dar a sus pedidos nombres significativos para futuras referencias, aparecerá el nombre de la columna dos veces en la
salida, por lo que la cláusula ORDER BY será ambigua, lo que puede causar errores ("nombre de columna ambiguo" en versiones
recientes de MS SQL Server), y si no en este ejemplo, el código de su aplicación podría comenzar a mostrar el nombre del
pedido donde está el nombre de la persona previsto porque la nueva columna es la primera de ese nombre devuelta, y así sucesivamente.
¿Cuándo puede usar *, teniendo en cuenta la advertencia anterior?
Si bien es mejor evitarlo en el código de producción, el uso de * está bien como abreviatura al realizar consultas manuales en la base de datos
para investigación o trabajo de prototipo.
A veces, las decisiones de diseño en su aplicación lo hacen inevitable (en tales circunstancias, prefiera tablealias.* en lugar de solo * cuando
sea posible).
Cuando se usa EXISTS, como SELECT A.col1, A.Col2 FROM A WHERE EXISTS (SELECT * FROM B where A.ID = B.A_ID), no
devolvemos ningún dato de B. Por lo tanto, no es necesaria una combinación y el El motor sabe que no se devolverán valores de B, por lo
que no se afectará el rendimiento al usar *. De manera similar, COUNT (*) está bien, ya que tampoco devuelve ninguna de las columnas,
por lo que solo necesita leer y procesar aquellas que se usan para fines de filtrado.
Sección 6.2: SELECT usando alias de columna
Los alias de columna se utilizan principalmente para acortar el código y hacer que los nombres de las columnas sean más legibles.
El código se vuelve más corto a medida que se pueden evitar los nombres de tabla largos y la identificación innecesaria de columnas (p. ej.,
puede haber 2 ID en la tabla, pero solo se usa uno en la declaración) . Junto con los alias de tabla, esto le permite usar nombres
descriptivos más largos en la estructura de su base de datos mientras mantiene concisas las consultas sobre esa estructura.
Además, a veces se requieren, por ejemplo, en vistas, para nombrar salidas calculadas.
GoalKicker.com Notas SQL para profesionales 15
Machine Translated by Google
Todas las versiones de SQL
Los alias se pueden crear en todas las versiones de SQL utilizando comillas dobles (").
SELECCIONAR
FName AS " Nombre",
MName AS "Segundo nombre",
LName COMO "Apellido "
DE Empleados
Diferentes versiones de SQL
Puede usar comillas simples ('), comillas dobles (") y corchetes ([]) para crear un alias en Microsoft SQL Server.
SELECCIONAR
FName AS " Nombre",
MName AS 'Segundo nombre',
LNombre AS [Apellido]
DE Empleados
Ambos resultarán en:
Primer nombre segundo nombre apellido
Jaime John Herrero
John Jaime Johnson
Miguel marcus Williams
Esta declaración devolverá las columnas FName y LName con un nombre dado (un alias). Esto se logra utilizando el operador AS seguido
del alias, o simplemente escribiendo el alias directamente después del nombre de la columna. Esto significa que la siguiente consulta tiene el
mismo resultado que la anterior.
SELECCIONAR
FName " Nombre",
MName "Segundo nombre",
LName "Apellido "
DE Empleados
Primer nombre segundo nombre apellido
Jaime John Herrero
John Jaime Johnson
Miguel marcus Williams
Sin embargo, la versión explícita (es decir, usando el operador AS ) es más legible.
Si el alias tiene una sola palabra que no es una palabra reservada, podemos escribirla sin comillas simples, comillas dobles o corchetes:
SELECCIONAR
FName AS FirstName,
LName AS Apellido
DE Empleados
Nombre Apellido
Jaime Herrero
John Johnson
miguel williams
GoalKicker.com Notas SQL para profesionales dieciséis
Machine Translated by Google
Otra variación disponible en MS SQL Server, entre otras, es <alias> = <columnaocálculo>, por ejemplo:
'
SELECCIONE Nombre completo = Nombre + ' + Apellido,
Dirección1 = dirección completa de la calle,
Dirección2 = Nombre de la ciudad
DESDE Detalles del cliente
que es equivalente a:
'
SELECCIONA Nombre + ' + Apellido como nombre completo
FullStreetAddress Como Addr1,
Nombre del pueblo Como Addr2
DESDE Detalles del cliente
Ambos resultarán en:
Nombre completo Dirección1 Dirección2
james smith 123 Cualquier calle TownVille
John Johnson 668 MyRoad Cualquier ciudad
Michael Williams 999 High End Dr. Williamsburgh
Algunos encuentran que usar = en lugar de As es más fácil de leer, aunque muchos no recomiendan este formato, principalmente porque no es estándar, por lo
que no es compatible con todas las bases de datos. Puede causar confusión con otros usos del carácter = .
Todas las versiones de SQL
Además, si necesita usar palabras reservadas, puede usar corchetes o comillas para escapar:
SELECCIONAR
FName como "SELECCIONAR",
MNombre como "DE",
LNombre como "DÓNDE"
DE Empleados
Diferentes versiones de SQL
Del mismo modo, puede escapar palabras clave en MSSQL con todos los enfoques diferentes:
SELECCIONAR
FNombre COMO "SELECCIONAR",
MNombre COMO 'DE',
LNombre COMO [DONDE]
DE Empleados
SELECCIONA DESDE DONDE
James Juan Smith
Juan James Johnson
miguel marcus williams
Además, se puede usar un alias de columna en cualquiera de las cláusulas finales de la misma consulta, como ORDER BY:
SELECCIONAR
FName AS FirstName,
LName AS Apellido
DE
GoalKicker.com Notas SQL para profesionales 17
Machine Translated by Google
Empleados
ORDENAR POR
Apellido DESC
Sin embargo, no puede utilizar
SELECCIONAR
FName COMO SELECCIONAR,
LNombre DESDE
DE
Empleados
ORDENAR POR
Apellido DESC
Para crear un alias a partir de estas palabras reservadas (SELECT y FROM).
Esto causará numerosos errores en la ejecución.
Sección 6.3: Seleccionar columnas individuales
SELECCIONAR
Número de teléfono,
Correo electrónico,
Contacto preferido
DE Clientes
Esta declaración devolverá las columnas Número de teléfono, Correo electrónico y Contacto preferido de todas las filas de la tabla Clientes . Además, las
columnas se devolverán en la secuencia en que aparecen en la cláusula SELECT .
El resultado será:
Número de teléfono Correo electrónico Contacto preferido
3347927472 [email protected] TELÉFONO 2137921892
[email protected] CORREO ELECTRÓNICO
NULO [email protected] CORREO ELECTRÓNICO
Si se unen varias tablas, puede seleccionar columnas de tablas específicas especificando el nombre de la tabla antes del nombre de la columna:
[table_name].[column_name]
SELECT
Customers.PhoneNumber,
Customers.Email,
Customers.PreferredContact, Orders.Id
AS OrderId
DE
Clientes
UNIRSE A LA IZQUIERDA
Pedidos ON Pedidos.CustomerId = Clientes.Id
*AS OrderId significa que el campo Id de la tabla Pedidos se devolverá como una columna denominada OrderId. Consulte la selección con alias de
columna para obtener más información.
Para evitar el uso de nombres de tabla largos, puede usar alias de tabla. Esto mitiga el dolor de escribir nombres de tabla largos para cada campo que seleccione en las uniones.
Si está realizando una unión automática (una unión entre dos instancias de la misma tabla), debe usar alias de tabla para distinguir sus tablas. Podemos escribir un alias de
tabla como Clientes c o Clientes AS c. Aquí c funciona como un alias para Clientes y podemos seleccionar digamos Correo electrónico así: c.Correo electrónico.
GoalKicker.com Notas SQL para profesionales 18
Machine Translated by Google
SELECCIONE
c.PhoneNumber, c.Email,
c.PreferredContact, o.Id AS OrderId
DE
Clientes c
UNIRSE A LA IZQUIERDA
Pedidos o ON o.CustomerId = c.Id
Sección 6.4: Selección de un número específico de registros
El estándar SQL 2008 define la cláusula FETCH FIRST para limitar el número de registros devueltos.
SELECCIONE Id, ProductName, UnitPrice, Package
DE Producto
ORDEN POR PrecioUnidad DESC
OBTENER LAS PRIMERAS 10 FILAS SOLAMENTE
Este estándar solo es compatible con versiones recientes de algunos RDMS. La sintaxis no estándar específica del proveedor se proporciona en otros sistemas.
Progress OpenEdge 11.x también es compatible con la sintaxis FETCH FIRST <n> ROWS ONLY .
Además, OFFSET <m> ROWS before FETCH FIRST <n> ROWS ONLY permite omitir filas antes de obtener filas.
SELECCIONE Id, ProductName, UnitPrice, Package
DE Producto
ORDEN POR PrecioUnidad DESC
DESPLAZAMIENTO 5 FILAS
OBTENER LAS PRIMERAS 10 FILAS SOLAMENTE
La siguiente consulta es compatible con SQL Server y MS Access:
SELECCIONE LOS 10 PRINCIPALES Id, ProductName, UnitPrice, Package
DE Producto
ORDEN POR PrecioUnidad DESC
Para hacer lo mismo en MySQL o PostgreSQL se debe usar la palabra clave LIMIT :
SELECCIONE Id, ProductName, UnitPrice, Package
DE Producto
ORDEN POR PrecioUnidad DESC
LÍMITE 10
En Oracle se puede hacer lo mismo con ROWNUM:
SELECCIONE Id, ProductName, UnitPrice, Package
DE Producto
DONDE ROWNUM <= 10
ORDEN POR PrecioUnidad DESC
Resultados: 10 registros.
Identificación Nombre del producto Precio unitario Envase 12
38 Côte de Blaye 263.50 botellas de 75 cl
29 Thüringer Rostbratwurst 123,79 50 bolsas x 30 salchichas. 18
9 Mishi Kobe Niku 97.00 paquetes de 500 g.
GoalKicker.com Notas SQL para profesionales 19
Machine Translated by Google
20 Mermelada de Sir Rodney 81,00 30 cajas regalo 16
18 Tigres de Carnarvon 62,50 kg paq. Paquete
59 Raclette Courdavault 55,00 de 5 kg.
51 Manjimup Manzanas Secas 53,00 Paquetes de 50 300 g.
62 pastel de azucar 49,30 48
43 Café Ipoh 46,00 empanadas 16 latas de
28 chucrut Rössle 45.60 500 g 25 latas de 825 g
Matices del proveedor:
Es importante tener en cuenta que TOP en Microsoft SQL opera después de la cláusula WHERE y devolverá el número especificado de resultados
si existen en cualquier lugar de la tabla, mientras que ROWNUM funciona como parte de la cláusula WHERE , por lo que si no existen otras
condiciones en el número especificado de filas al principio de la tabla, obtendrá cero resultados cuando podría encontrar otros.
Sección 6.5: Selección con condición
La sintaxis básica de SELECT con la cláusula WHERE es:
SELECCIONE columna1, columna2, columnaN
DESDE table_name
DONDE [condición]
La [condición] puede ser cualquier expresión SQL, especificada mediante operadores lógicos o de comparación como >, <, =, <>, >=, <=, LIKE,
NOT, IN, BETWEEN, etc.
La siguiente declaración devuelve todas las columnas de la tabla 'Autos' donde la columna de estado es 'LISTO':
SELECCIONE * DESDE Automóviles DONDE estado = 'LISTO'
Vea WHERE y HAVING para más ejemplos.
Sección 6.6: Seleccionar con CASE
Cuando los resultados necesitan tener alguna lógica aplicada 'sobre la marcha', se puede usar la instrucción CASE para implementarla.
SELECCIONE CASO CUANDO Col1 < 50 ENTONCES 'por debajo' DE LO CONTRARIO 'por encima' del umbral END
DESDE NombreTabla
también se puede encadenar
SELECCIONAR
CASO CUANDO Col1 < 50 ENTONCES 'bajo'
CUANDO Col1 > 50 Y Col1 <100 ENTONCES 'entre'
ELSE 'cambio'
Umbral FIN
DESDE NombreTabla
uno también puede tener CASE dentro de otra declaración CASE
SELECCIONAR
CASO CUANDO Col1 < 50 ENTONCES 'bajo'
DEMÁS
CASO CUANDO Col1 > 50 Y Col1 <100 ENTONCES Col1
ELSE 'sobre' FIN
Umbral FIN
GoalKicker.com Notas SQL para profesionales 20
Machine Translated by Google
DESDE NombreTabla
Sección 6.7: Seleccionar columnas que llevan el nombre de palabras clave
reservadas
Cuando un nombre de columna coincide con una palabra clave reservada, SQL estándar requiere que lo escriba entre comillas dobles:
SELECCIONAR
"ORDEN",
IDENTIFICACIÓN
DE PEDIDOS
Tenga en cuenta que hace que el nombre de la columna distinga entre mayúsculas y minúsculas.
Algunos DBMS tienen formas propietarias de citar nombres. Por ejemplo, SQL Server usa corchetes para esto
objetivo:
SELECCIONAR
[Orden],
IDENTIFICACIÓN
DE PEDIDOS
mientras que MySQL (y MariaDB) por defecto usan acentos graves:
SELECCIONAR
`Pedido`,
identificación
DESDE pedidos
Sección 6.8: Selección con alias de tabla
SELECCIONE e.Fname, e.LName
FROM Empleados e
La tabla Empleados recibe el alias 'e' directamente después del nombre de la tabla. Esto ayuda a eliminar la ambigüedad en escenarios en los
que varias tablas tienen el mismo nombre de campo y debe especificar de qué tabla desea obtener datos.
SELECCIONE e.Fname, e.LName, m.Fname AS ManagerFirstName FROM Empleados
e JOIN Managers m ON
e.ManagerId = m.Id
Tenga en cuenta que una vez que define un alias, ya no puede usar el nombre de la tabla canónica. es decir,
SELECCIONE e.Fname, Employees.LName, m.Fname AS ManagerFirstName FROM Employees e
JOIN Managers m ON
e.ManagerId = m.Id
arrojaría un error.
Vale la pena señalar que los alias de tabla, más formalmente 'variables de rango', se introdujeron en el lenguaje SQL para resolver el problema de las columnas
duplicadas causadas por INNER JOIN. El estándar SQL de 1992 corrigió este defecto de diseño anterior al introducir NATURAL JOIN (implementado en mySQL,
PostgreSQL y Oracle pero aún no en SQL Server), cuyo resultado nunca tiene nombres de columna duplicados. El ejemplo anterior es interesante porque las tablas
se unen en
GoalKicker.com Notas SQL para profesionales 21
Machine Translated by Google
columnas con nombres diferentes (Id y ManagerId) pero se supone que no deben unirse en las columnas con el mismo nombre (LName, FName),
lo que requiere que se realice el cambio de nombre de las columnas antes de la unión:
SELECCIONE Fname, LName, ManagerFirstName
DE Empleados
UNIÓN NATURAL
( SELECCIONE Id AS ManagerId, Fname AS ManagerFirstName
DE Gerentes ) m;
Tenga en cuenta que aunque se debe declarar una variable de alias/rango para la tabla derivada (de lo contrario, SQL arrojará un error),
nunca tiene sentido usarla en la consulta.
Sección 6.9: Selección con más de 1 condición
La palabra clave AND se usa para agregar más condiciones a la consulta.
Nombre Edad Sexo
Sam 18 meses
Juan 21 M
bob 22 m
María 23 F
SELECCIONE nombre DE personas DONDE sexo = 'M' Y edad > 20;
Esto devolverá:
Nombre
John
Beto
usando la palabra clave OR
SELECCIONE nombre DE personas DONDE género = 'M' O edad < 20;
Esto devolverá:
nombre
Él mismo
John
Beto
Estas palabras clave se pueden combinar para permitir combinaciones de criterios más complejas:
SELECCIONA nombre
DE personas
DONDE (género = 'M' Y edad < 20)
O (género = 'F' Y edad > 20);
Esto devolverá:
nombre
Él mismo
María
GoalKicker.com Notas SQL para profesionales 22
Machine Translated by Google
Sección 6.10: Seleccionar sin bloquear la tabla
A veces, cuando las tablas se usan principalmente (o solo) para lecturas, la indexación ya no ayuda y cada bit cuenta, uno puede usar
selecciones sin LOCK para mejorar el rendimiento.
servidor SQL
SELECCIONE * DESDE TableName CON (nolock)
mysql
ESTABLECER NIVEL DE AISLAMIENTO DE TRANSACCIÓN DE SESIÓN LEER NO COMPROMETIDO;
SELECCIONE * DE TableName;
ESTABLECER LECTURA REPETIBLE DE NIVEL DE AISLAMIENTO DE TRANSACCIÓN DE SESIÓN;
Oráculo
ESTABLECER NIVEL DE AISLAMIENTO DE TRANSACCIÓN LEER NO COMPROMETIDO;
SELECCIONE * DE TableName;
DB2
SELECCIONE * DESDE TableName CON UR;
donde UR significa "lectura no confirmada".
Si se usa en una tabla que tiene modificaciones de registro en curso, puede tener resultados impredecibles.
Sección 6.11: Seleccionar con funciones Agregadas
Promedio
La función agregada AVG() devolverá el promedio de los valores seleccionados.
SELECCIONE PROMEDIO (Salario) DE Empleados
Las funciones agregadas también se pueden combinar con la cláusula where.
SELECCIONE PROMEDIO (Salario) DE Empleados donde DepartmentId = 1
Las funciones agregadas también se pueden combinar con la cláusula group by.
Si el empleado está categorizado con varios departamentos y queremos encontrar el salario promedio para cada departamento, entonces
podemos usar la siguiente consulta.
SELECCIONE PROMEDIO (Salario) DEL GRUPO de empleados POR ID de departamento
Mínimo
La función agregada MIN() devolverá el mínimo de valores seleccionados.
SELECCIONE MIN(Salario) DE Empleados
Máximo
La función agregada MAX() devolverá el máximo de valores seleccionados.
SELECCIONE MAX(Salario) DE Empleados
Contar
La función agregada COUNT() devolverá el recuento de valores seleccionados.
SELECCIONE Recuento(*) DE Empleados
También se puede combinar con condiciones where para obtener el recuento de filas que satisfacen condiciones específicas.
GoalKicker.com Notas SQL para profesionales 23
Machine Translated by Google
SELECCIONE Count(*) FROM Empleados donde ManagerId NO ES NULO
También se pueden especificar columnas específicas para obtener el número de valores en la columna. Tenga en cuenta que los valores NULL no
se cuentan.
Seleccione Count (ManagerId) de Empleados
Contar también se puede combinar con la palabra clave distinta para una cuenta distinta.
Seleccione Recuento (DISTINCT DepartmentId) de Empleados
Suma
La función agregada SUM() devuelve la suma de los valores seleccionados para todas las filas.
SELECCIONE SUMA (Salario) DE Empleados
Sección 6.12: Seleccionar con condición de múltiples valores de la
columna
SELECCIONE * DESDE Automóviles DONDE estado EN ( 'Esperando', 'Trabajando' )
Esto es semánticamente equivalente a
SELECCIONE * DESDE Automóviles DONDE ( estado = 'Esperando' O estado = 'Trabajando' )
es decir, el valor IN ( < lista de valores> ) es una forma abreviada de disyunción ( OR lógico).
Sección 6.13: Obtener resultados agregados para grupos de filas
Contar filas en función de un valor de columna específico:
SELECCIONE categoría, COUNT(*) AS item_count
DESDE artículo
AGRUPAR POR categoría;
Obtener el ingreso promedio por departamento:
SELECCIONE departamento, AVG(ingresos)
DE empleados
AGRUPAR POR departamento;
Lo importante es seleccionar solo las columnas especificadas en la cláusula GROUP BY o utilizadas con funciones agregadas.
La cláusula WHERE también se puede usar con GROUP BY, pero WHERE filtra los registros antes de realizar cualquier agrupación:
SELECCIONE departamento, AVG(ingresos)
DE empleados
DONDE departamento <> 'CONTABILIDAD'
AGRUPAR POR departamento;
Si necesita filtrar los resultados después de que se haya realizado la agrupación, por ejemplo, para ver solo los departamentos cuyo ingreso
promedio es superior a 1000, debe usar la cláusula HAVING :
SELECCIONE departamento, AVG(ingresos)
DE empleados
DONDE departamento <> 'CONTABILIDAD'
AGRUPAR POR departamento
TENIENDO promedio (ingresos) > 1000;
GoalKicker.com Notas SQL para profesionales 24
Machine Translated by Google
Sección 6.14: Selección con resultados ordenados
SELECCIONE * DE Empleados ORDEN POR LName
Esta declaración devolverá todas las columnas de la tabla Empleados.
Id FName LName Número de teléfono
2 Juan Johnson 2468101214
1 Jaime Smith 1234567890
3 Miguel Williams 1357911131
SELECCIONE * DE Empleados ORDEN POR LName DESC
SELECCIONE * DE Empleados ORDEN POR LName ASC
Esta instrucción cambia la dirección de clasificación.
También se pueden especificar varias columnas de clasificación. Por ejemplo:
SELECCIONE * DE Empleados ORDEN POR LName ASC, FName ASC
Este ejemplo ordenará los resultados primero por LName y luego, para los registros que tienen el mismo LName, ordene por FName. Esto le dará un
resultado similar al que encontraría en una guía telefónica.
Para evitar tener que volver a escribir el nombre de la columna en la cláusula ORDER BY , es posible utilizar en su lugar el número de la columna.
Tenga en cuenta que los números de columna comienzan desde 1.
SELECCIONE Id, FName, LName, PhoneNumber FROM Empleados ORDENAR POR 3
También puede incrustar una instrucción CASE en la cláusula ORDER BY .
SELECT Id, FName, LName, PhoneNumber FROM Empleados ORDENAR POR CASO CUANDO LName='Jones' THEN 0 ELSE 1
FIN ASC
Esto ordenará sus resultados para tener todos los registros con el LName de "Jones" en la parte superior.
Sección 6.15: Seleccionar con nulo
SELECCIONE Nombre DESDE Clientes DONDE Número de teléfono ES NULO
La selección con nulos toma una sintaxis diferente. No use =, use IS NULL o IS NOT NULL en su lugar.
Sección 6.16: Seleccionar distintos (solo valores únicos)
SELECCIONE UN CÓDIGO DE Continente DISTINTO
DE Países;
Esta consulta devolverá todos los valores DISTINTOS (únicos, diferentes) de la columna ContinentCode de la tabla de países
ContinentCode
jefe
UE
GoalKicker.com Notas SQL para profesionales 25
Machine Translated by Google
COMO
ESO
DE
Demostración de SQLFiddle
Sección 6.17: Seleccionar filas de varias tablas
SELECCIONAR *
DESDE tabla1,
tabla2
SELECCIONE
tabla1.columna1,
tabla1.columna2, tabla2.columna1
DESDE tabla1,
tabla2
Esto se llama producto cruzado en SQL, es lo mismo que el producto cruzado en conjuntos
Estas declaraciones devuelven las columnas seleccionadas de varias tablas en una consulta.
No existe una relación específica entre las columnas devueltas de cada tabla.
GoalKicker.com Notas SQL para profesionales 26
Machine Translated by Google
Capítulo 7: AGRUPACIÓN POR
Los resultados de una consulta SELECT se pueden agrupar por una o más columnas usando la declaración GROUP BY : todos los
resultados con el mismo valor en las columnas agrupadas se agregan juntos. Esto genera una tabla de resultados parciales, en lugar de un
resultado. GROUP BY se puede usar junto con funciones de agregación usando la instrucción HAVING para definir cómo se agregan las
columnas no agrupadas.
Sección 7.1: Ejemplo básico de GROUP BY
Podría ser más fácil si piensa en GROUP BY como "para cada uno" por el bien de la explicación. La consulta a continuación:
SELECCIONE EmpID, SUM (Salario Mensual)
DE Empleado
AGRUPAR POR EMPID
esta diciendo:
"Dame la suma de MonthlySalary's para cada EmpID"
Entonces, si su tabla se viera así:
+++
|EmpID|SalarioMensual|
+++
|1 |200 |
+++
|2 |300 |
+++
Resultado:
+++
|1|200|
+++
|2|300|
+++
Sum no parecería hacer nada porque la suma de un número es ese número. Por otro lado, si se viera así:
+++
|EmpID|SalarioMensual|
+++
|1 |200 |
+++
|1 |300 |
+++
|2 |300 |
+++
Resultado:
GoalKicker.com Notas SQL para profesionales 27
Machine Translated by Google
+++
|1|500|
+++
|2|300|
+++
Entonces lo haría porque hay dos EmpID 1 para sumar.
Sección 7.2: Filtre los resultados de GROUP BY usando una cláusula HAVING
Una cláusula HAVING filtra los resultados de una expresión GROUP BY. Nota: Los siguientes ejemplos utilizan la base de datos de ejemplo de
la biblioteca.
Ejemplos:
Devuelve todos los autores que escribieron más de un libro (ejemplo en vivo).
SELECCIONE
un.Id, un.Nombre, COUNT(*) LibrosEscritos
¿Es DE BooksAuthors?
INNER JOIN Autores a ON a.id = ba.authorid
GROUP
BY a.Id,
un nombre
TENER CONTEO(*) > 1 igual a TENER LibrosEscritos > 1
;
Devuelve todos los libros que tengan más de tres autores (ejemplo en vivo).
SELECCIONE
b.Id, b.Title, COUNT(*)
NumberOfAuthors FROM BooksAuthors ba
INNER JOIN Books b ON b.id = ba.bookid GROUP
BY b.Id,
b.Title
HAVING
COUNT(*) > 3 ; es igual a TENER Número de Autores > 3
Sección 7.3: USAR GROUP BY para CONTAR el número de filas para cada entrada
única en una columna dada
Supongamos que desea generar recuentos o subtotales para un valor dado en una columna.
Dada esta tabla, los "westerosianos":
Nombre Gran Casa Lealtad
Arya Rígido
Pendientes Lannister
myrcella lannister
Niños alegría gris
Catelyn Stark
GoalKicker.com Notas SQL para profesionales 28
Machine Translated by Google
sansa stark
Sin GROUP BY, COUNT simplemente devolverá un número total de filas:
SELECCIONE Recuento(*) Número_de_occidentales
DE Poniente
devoluciones...
Número_de_occidentales 6
Pero al agregar GROUP BY, podemos CONTAR los usuarios para cada valor en una columna dada, para devolver la cantidad de
personas en una Gran Casa dada, digamos:
SELECCIONE GreatHouseAllegience House, Count(*) Number_of_Westerosians
DE Poniente
GRUPO POR GreatHouseAllegience
devoluciones...
Número_de_la_casa_de_los_occidentales
rígido 3
alegría gris 1
Lanister 2
Es común combinar GROUP BY con ORDER BY para ordenar los resultados por categoría más grande o más pequeña:
SELECCIONE GreatHouseAllegience House, Count(*) Number_of_Westerosians
DE Poniente
GRUPO POR GreatHouseAllegience
ORDENAR POR Número_de_occidentales Desc
devoluciones...
Número_de_la_casa_de_los_occidentales
rígido 3
Lanister 2
alegría gris 1
Sección 7.4: Agregación ROLAP (Minería de datos)
Descripción
El estándar SQL proporciona dos operadores agregados adicionales. Estos usan el valor polimórfico "TODOS" para denotar el conjunto de
todos los valores que puede tomar un atributo. Los dos operadores son:
con cubo de datos que proporciona todas las combinaciones posibles que los atributos de argumento de la cláusula.
con roll up que proporciona los agregados obtenidos al considerar los atributos en orden de izquierda a derecha en
comparación con cómo se enumeran en el argumento de la cláusula.
Versiones estándar de SQL que admiten estas características: 1999,2003,2006,2008,2011.
Ejemplos
GoalKicker.com Notas SQL para profesionales 29
Machine Translated by Google
Considere esta tabla:
Marca de alimentos Total_amount
Pegar marca1 100
Pasta Marca2 250
Marca de pizza2 300
con cubo
seleccione Comida, Marca, Total_amount
de la tabla
agrupar por comida, marca, cantidad total con cubo
Marca de alimentos Total_amount
Pegar marca1 100
Pasta Marca2 250
Pastas TODO 350
Marca de pizza2 300
Pizza TODO 300
TODAS Marca1 100
TODAS Marca2 550
TODO TODO 650
con enrollado
seleccione Comida, Marca, Total_amount
de la tabla
agrupar por Comida, Marca, Total_amount con roll up
Marca de alimentos Total_amount
Pegar marca1 100
Pasta Marca2 250
Marca de pizza2 300
Pastas TODO 350
Pizza TODO 300
TODO TODO 650
GoalKicker.com Notas SQL para profesionales 30
Machine Translated by Google
Capítulo 8: ORDENAR POR
Sección 8.1: Clasificación por número de columna (en lugar de nombre)
Puede usar el número de una columna (donde la columna más a la izquierda es '1') para indicar en qué columna basar la ordenación, en lugar
de describir la columna por su nombre.
Pro: si cree que es probable que cambie los nombres de las columnas más adelante, hacerlo no romperá este código.
Desventaja: esto generalmente reducirá la legibilidad de la consulta (es inmediatamente claro lo que significa 'ORDEN POR Reputación', mientras
que 'ORDEN POR 14' requiere un poco de conteo, probablemente con un dedo en la pantalla).
Esta consulta ordena el resultado por la información en la posición relativa de la columna 3 de la declaración de selección en lugar del nombre de la
columna Reputación.
SELECCIONE DisplayName, JoinDate, Reputation FROM Usuarios ORDENAR POR 3
DisplayName JoinDate Reputación
Comunidad 20080915 1
Jarrod Dixon 20081003 11739
Geoff Dalgas 20081003 12567
Joel Spolski 20080916 25784
Jeff Atwood 20080916 37628
Sección 8.2: Use ORDER BY con TOP para devolver las filas x superiores
en función del valor de una columna
En este ejemplo, podemos usar GROUP BY no solo para determinar el tipo de filas devueltas, sino también qué filas se devuelven, ya que estamos
usando TOP para limitar el conjunto de resultados.
Supongamos que queremos devolver los 5 usuarios con mayor reputación de un sitio popular de preguntas y respuestas sin nombre.
Sin ORDEN POR
Esta consulta devuelve las 5 filas principales ordenadas de forma predeterminada, que en este caso es "Id", la primera columna de la tabla
(aunque no es una columna que se muestra en los resultados).
SELECCIONE LOS 5 PRINCIPALES DisplayName, Reputación
DE Usuarios
devoluciones...
Reputación de DisplayName
Comunidad 1
Geoff Dalgas 12567
Jarrod Dixon 11739
Jeff Atwood 37628
Joel Spolski 25784
con ORDEN POR
SELECCIONE LOS 5 PRINCIPALES DisplayName, Reputación
DE Usuarios
GoalKicker.com Notas SQL para profesionales 31
Machine Translated by Google
ORDEN POR Reputación desc
devoluciones...
Reputación de DisplayName
JonSkeet 865023
Darin Dimitrov 661741
BalusC 650237
Hans Passant 625870
Marcos Gravell 601636
Observaciones
Algunas versiones de SQL (como MySQL) usan una cláusula LIMIT al final de un SELECT, en lugar de TOP al principio, por ejemplo:
SELECCIONE DisplayName, Reputación
DE Usuarios
ORDEN POR Reputación DESC
LÍMITE 5
Sección 8.3: Orden de clasificación personalizado
Para ordenar esta tabla Empleado por departamento, usaría ORDENAR POR Departamento. Sin embargo, si desea un orden de clasificación diferente que no sea
alfabético, debe asignar los valores del departamento a valores diferentes que se ordenen correctamente; esto se puede hacer con una expresión CASE:
Nombre Departamento
Hasan IT
Yusuf HR
Hillary Recursos Humanos
Joe TI
feliz recursos humanos
Ken Contador
SELECCIONAR *
DE Empleado
ORDEN POR CAJA Departamento
CUANDO 'HR' ENTONCES 1
CUANDO 'Contador' ENTONCES 2
MÁS 3
FIN;
Nombre Departamento
Yusuf HR
Hillary Recursos Humanos
feliz recursos humanos
Ken Contador
Hasan IT
Joe TI
Sección 8.4: Orden por Alias
Debido al orden lógico de procesamiento de consultas, el alias se puede usar en orden.
GoalKicker.com Notas SQL para profesionales 32
Machine Translated by Google
SELECCIONE DisplayName, JoinDate como jd, Reputación como representante
DE Usuarios
ORDEN POR jd, representante
Y puede usar el orden relativo de las columnas en la declaración de selección. Considere el mismo ejemplo anterior y, en lugar de
usar un alias, use el orden relativo como para mostrar el nombre es 1 , para Jd es 2 y así sucesivamente
SELECCIONE DisplayName, JoinDate como jd, Reputación como representante
DE Usuarios
ORDEN POR 2, 3
Sección 8.5: Clasificación por múltiples columnas
SELECCIONE DisplayName, JoinDate, Reputation FROM Usuarios ORDENAR POR JoinDate, Reputation
DisplayName JoinDate Reputación
Comunidad 20080915 1
Jeff Atwood 20080916 25784
Joel Spolski 20080916 37628
Jarrod Dixon 20081003 11739
Geoff Dalgas 20081003 12567
GoalKicker.com Notas SQL para profesionales 33
Machine Translated by Google
Capítulo 9: Operadores AND & OR
Sección 9.1: Y O Ejemplo
tener una mesa
Nombre Edad Ciudad
Bob 10 París
Comida 20 Berlín
María 24 Praga
seleccione Nombre de la tabla donde Edad> 10 Y Ciudad = 'Praga'
Da
Nombre
María
seleccione Nombre de la tabla donde Edad = 10 O Ciudad = 'Praga'
Da
Nombre
Beto
María
GoalKicker.com Notas SQL para profesionales 34
Machine Translated by Google
Capítulo 10: CASO
La expresión CASE se utiliza para implementar la lógica sientonces.
Sección 10.1: Use CASE para CONTAR el número de filas en una columna
que coincide con una condición
Caso de uso
CASE se puede usar junto con SUM para devolver un recuento de solo aquellos elementos que coincidan con una condición predefinida.
(Esto es similar a COUNTIF en Excel).
El truco consiste en devolver resultados binarios que indiquen coincidencias, de modo que los "1" devueltos para las entradas coincidentes se
puedan sumar para contar el número total de coincidencias.
Dada esta tabla ItemSales, supongamos que desea conocer el número total de artículos que se han categorizado como
"Caro":
Id ItemId Precio PriceRating 1 100
34.5 CARO
2 145 2.3 BARATO
3 100 34,5 CARO
4 100 34,5 CARO
5 145 10 ASEQUIBLE
Consulta
SELECCIONAR
COUNT(Id) AS ItemsCount,
SUMA ( CASO
CUANDO PriceRating = 'Caro' ENTONCES 1
OTRO 0
FIN
) AS CostoItemsCount
DESDE ItemSales
Resultados:
ItemsCount CaroItemsCount 5
3
Alternativa:
SELECCIONAR
COUNT(Id) como ItemsCount,
SUMA (
Clasificación de precios de CASE
CUANDO 'Caro' ENTONCES 1
OTRO 0
FIN
) AS CostoItemsCount
DESDE ItemSales
GoalKicker.com Notas SQL para profesionales 35
Machine Translated by Google
Sección 10.2: CASO buscado en SELECT (Coincide con una expresión
booleana)
El CASO buscado devuelve resultados cuando una expresión booleana es VERDADERA.
(Esto difiere del caso simple, que solo puede verificar la equivalencia con una entrada).
SELECCIONE Id, ItemId, Precio,
CASO CUANDO Precio < 10 ENTONCES 'BARATO'
CUANDO Precio < 20 ENTONCES 'ASEQUIBLE'
MÁS 'CARO'
TERMINAR COMO Clasificación de precios
DESDE ItemSales
Id ItemId Precio PriceRating 1 100
34.5 CARO
2 145 2.3 BARATO
3 100 34,5 CARO
4 100 34,5 CARO
5 145 10 ASEQUIBLE
Sección 10.3: CASO en una cláusula ORDEN POR
Podemos usar 1,2,3.. para determinar el tipo de orden:
SELECCIONE * DESDE EL DEPARTAMENTO
ORDENAR POR
DEPARTAMENTO DE CASOS
CUANDO 'MARKETING' ENTONCES 1
CUANDO 'VENTAS' ENTONCES 2
CUANDO 'INVESTIGAR' ENTONCES 3
CUANDO 'INNOVACIÓN' ENTONCES 4
DEMÁS 5
FIN,
CIUDAD
REGIÓN DE IDENTIFICACIÓN CIUDAD EMPLEADOS DEL DEPARTAMENTO_NUMBER
12 Nueva Inglaterra Boston MARKETING 9
15 Oeste Mercadotecnia de San Francisco 12
9 Medio Oeste chicago VENTAS 8
14 Atlántico medio de Nueva York VENTAS 12
5 Oeste Los Ángeles INVESTIGACIÓN 11
10 Filadelfia del Atlántico Medio INVESTIGACIÓN 13
4 medio oeste chicago INNOVACIÓN 11
2 medio oeste detroit RECURSOS HUMANOS 9
Sección 10.4: CASE abreviado en SELECT
La variante abreviada de CASE evalúa una expresión (normalmente una columna) frente a una serie de valores. Esta variante es un poco más
corta y ahorra tener que repetir la expresión evaluada una y otra vez. Sin embargo, todavía se puede usar la cláusula ELSE :
SELECCIONE Id, ItemId, Precio,
CASO Precio CUANDO 5 ENTONCES 'BARATO'
CUANDO 15 ENTONCES 'ASEQUIBLE'
GoalKicker.com Notas SQL para profesionales 36
Machine Translated by Google
DEMÁS 'CARO'
END como PriceRating
DESDE ItemSales
Una palabra de precaución. Es importante darse cuenta de que cuando se usa la variante corta, la declaración completa se evalúa en cada CUÁNDO. Por
lo tanto la siguiente declaración:
SELECCIONAR
ABS DEL CASO (SUMA DE COMPROBACIÓN (NUEVO ())) % 4
CUANDO 0 ENTONCES 'Dr'
CUANDO 1 ENTONCES 'Maestro'
CUANDO 2 ENTONCES 'Señor'
CUANDO 3 ENTONCES 'Sra'
FIN
puede producir un resultado NULL . Esto se debe a que en cada WHEN NEWID() se vuelve a llamar con un nuevo resultado. Equivalente
a:
SELECCIONAR
CASO
CUANDO ABS(SUMA DE CONTROL(NEWID())) % 4 = 0 ENTONCES 'Dr'
CUANDO ABS(CHECKSUM(NEWID())) % 4 = 1 ENTONCES 'Maestro'
CUANDO ABS(CHECKSUM(NEWID())) % 4 = 2 ENTONCES 'Señor'
CUANDO ABS(CHECKSUM(NEWID())) % 4 = 3 ENTONCES 'Sra'
FIN
Por lo tanto, puede pasar por alto todos los casos WHEN y dar como resultado NULL.
Sección 10.5: Uso de CASE en ACTUALIZAR
muestra sobre aumentos de precios:
ACTUALIZAR artículoPrecio
SET Precio = Precio *
Id. del artículo del CASO
CUANDO 1 ENTONCES 1.05
CUANDO 2 ENTONCES 1.10
CUANDO 3 ENTONCES 1.15
DE LO CONTRARIO
1.00 FIN
Sección 10.6: Uso de CASE para valores NULL pedidos en último lugar
de esta manera, '0' que representa los valores conocidos se clasifican en primer lugar, '1' que representa los valores NULOS se ordenan por el
último:
SELECCIONAR DNI
,REGIÓN
,CIUDAD
,DEPARTAMENTO
,EMPLEADOS_NUMERO
DESDE EL DEPARTAMENTO
ORDENAR POR
CASO CUANDO LA REGIÓN ES NULA ENTONCES 1
OTRO 0
FIN,
REGIÓN
GoalKicker.com Notas SQL para profesionales 37
Machine Translated by Google
REGIÓN DE IDENTIFICACIÓN CIUDAD EMPLEADOS DEL DEPARTAMENTO_NÚMERO 13
10 Filadelfia del Atlántico Medio INVESTIGACIÓN
14 Atlántico medio de Nueva York VENTAS 12
9 Medio Oeste chicago VENTAS 8
12 Nueva Inglaterra Boston MARKETING 9
5 Oeste Los Ángeles INVESTIGACIÓN 11
15 NULO Mercadotecnia de San Francisco 12
4 NULO chicago INNOVACIÓN 11
2 NULO detroit RECURSOS HUMANOS 9
Sección 10.7: CASO en la cláusula ORDER BY para ordenar registros
por el valor más bajo de 2 columnas
Imagine que necesita ordenar los registros por el valor más bajo de cualquiera de las dos columnas. Algunas bases de datos podrían usar una función
MIN() o MENOS() no agregada para esto (... ORDER BY MIN(Date1, Date2)), pero en SQL estándar, debe usar una expresión CASE .
La expresión CASE en la consulta a continuación examina las columnas Fecha1 y Fecha2 , verifica qué columna tiene el valor más bajo y ordena los
registros según este valor.
Data de muestra
Fecha de identificación1 fecha2
1 20170101 20170131
2 20170131 20170103
3 20170131 20170102
4 20170106 20170131
5 20170131 20170105
6 20170104 20170131
Consulta
SELECCIONE Id, Fecha1, Fecha2
DESDE TuMesa
ORDEN POR CAJA
CUANDO SE COALESCE(Fecha1, '17530101') < COALESCE(Fecha2, '17530101') ENTONCES Fecha1
ELSE Fecha2
FIN
Resultados
Fecha de identificación1 fecha2
1 20170101 20170131
3 20170131 20170102
2 20170131 20170103
6 20170104 20170131
5 20170131 20170105
4 20170106 20170131
Explicación
Como ve, la fila con Id = 1 es la primera, porque Date1 tiene el registro más bajo de toda la tabla 20170101, la fila donde Id = 3 es la segunda porque
Date2 es igual a 20170102 que es el segundo valor más bajo de la tabla etcétera.
Por lo tanto, hemos ordenado los registros de 20170101 a 20170106 de forma ascendente y no nos importa en qué columna Date1 o Date2 son esos
valores.
GoalKicker.com Notas SQL para profesionales 38
Machine Translated by Google
Capítulo 11: Operador LIKE
Sección 11.1: Emparejar patrón abierto
El comodín % añadido al principio o al final (o ambos) de una cadena permitirá que coincidan 0 o más de cualquier carácter antes del
principio o después del final del patrón.
El uso de '%' en el medio permitirá que coincidan 0 o más caracteres entre las dos partes del patrón.
Vamos a utilizar esta tabla de empleados:
Id FName LName PhoneNumberId ManagerId DepartmentId Salario Hire_date
1 John Johnson 2468101214 1 400 23032005 1
2 Sofía Amudsen 2479100211 1 1 400 01112010
3 Ronny Smith 2462544026 2 1 600 08062015
4 Juan Sánchez 2454124602 1 1 400 23032005
5 Hilde Knag 2468021911 2 1 800 01012000
La siguiente declaración coincide con todos los registros que tienen FName que contiene la cadena 'on' de la tabla de empleados.
SELECCIONE * DE Empleados DONDE FName LIKE '%on%';
Id FName LName PhoneNumberId ManagerId DepartmentId Salario Hire_date
3 Ronny Smith 2462544026 2 600 08062015 1
4 Juan Sánchez 2454124602 1 1 400 23032005
La siguiente declaración coincide con todos los registros que tienen Número de teléfono que comienza con la cadena '246' de Empleados.
SELECCIONE * DE Empleados DONDE Número de teléfono LIKE '246%';
Id FName LName PhoneNumberId ManagerId DepartmentId Salario Hire_date
1 John Johnson 2468101214 1 400 23032005 1
3 Ronny Smith 2462544026 2 1 600 08062015
5 Hilde Knag 2468021911 2 1 800 01012000
La siguiente declaración coincide con todos los registros que tienen un número de teléfono que termina con la cadena '11' de los empleados.
SELECCIONE * DE Empleados DONDE Número de teléfono LIKE '%11'
Id FName LName PhoneNumberId ManagerId DepartmentId Salario Hire_date
2 Sophie Amudsen 2479100211 1,400 20100111 1
5 Hilde Knag 2468021911 2 1 800 01012000
Todos los registros donde el tercer carácter de Fname es 'n' de Empleados.
SELECCIONE * DE Empleados DONDE FName LIKE '__n%';
(se usan dos guiones bajos antes de 'n' para omitir los primeros 2 caracteres)
Id FName LName PhoneNumberId ManagerId DepartmentId Salario Hire_date
3 Ronny Smith 2462544026 2 600 08062015 1
4 Juan Sánchez 2454124602 1 1 400 23032005
GoalKicker.com Notas SQL para profesionales 39
Machine Translated by Google
Sección 11.2: Coincidencia de un solo carácter
Para ampliar las selecciones de una instrucción de lenguaje de consulta estructurado (SQLSELECT), se pueden utilizar caracteres comodín, el
signo de porcentaje (%) y el guión bajo (_).
El carácter _ (guion bajo) se puede utilizar como comodín para cualquier carácter único en una coincidencia de patrón.
Encuentre todos los empleados cuyo Fname comience con 'j' y termine con 'n' y tenga exactamente 3 caracteres en Fname.
SELECCIONE * DE Empleados DONDE FName LIKE 'j_n'
El carácter _ (guión bajo) también se puede usar más de una vez como comodín para hacer coincidir los patrones.
Por ejemplo, este patrón coincidiría con "jon", "jan", "jen", etc.
Estos nombres no se mostrarán "jn","john","jordan", "justin", "jason", "julian", "jillian", "joann" porque en nuestra consulta se usa un guión bajo y
se puede omitir exactamente un carácter, por lo que el resultado debe ser de 3 caracteres Fname.
Por ejemplo, este patrón coincidiría con "LaSt", "LoSt", "HaLt", etc.
SELECCIONE * DE Empleados DONDE FName LIKE '_A_T'
Sección 11.3: sentencia ESCAPE en la consulta LIKE
Si implementa una búsqueda de texto como una consulta LIKE, generalmente lo hace así:
SELECCIONAR *
DE T_Lo que sea
DONDE AlgúnCampo LIKE CONCAT('%', @in_SearchText, '%')
Sin embargo, (aparte del hecho de que no debería usar necesariamente LIKE cuando puede usar la búsqueda de texto completo), esto
crea un problema cuando alguien ingresa texto como "50%" o "a_b".
Entonces (en lugar de cambiar a la búsqueda de texto completo), puede resolver ese problema usando la instrucción LIKEescape:
SELECCIONAR *
DE T_Lo que sea
DONDE AlgúnCampo LIKE CONCAT('%', @in_SearchText, '%') ESCAPE '\'
Eso significa que \ ahora se tratará como un carácter ESCAPE. Esto significa que ahora puede anteponer \ a cada carácter de la cadena que
busca, y los resultados comenzarán a ser correctos, incluso cuando el usuario ingrese un carácter especial como %
o _.
p.ej
string stringToSearch = "abc_def 50%"; cadena
nuevaCadena = ""; foreach(char
c en stringToSearch) newString += @"\" + c;
sqlCmd.Parameters.Add("@in_SearchText", newString); // en lugar de
sqlCmd.Parameters.Add("@in_SearchText", stringToSearch);
Nota: El algoritmo anterior es solo para fines de demostración. No funcionará en los casos en que 1 grafema consta de varios caracteres (utf8). por
ejemplo, string stringToSearch = "Les Mise\u0301rables"; Tendrás que hacer esto
GoalKicker.com Notas SQL para profesionales 40
Machine Translated by Google
para cada grafema, no para cada carácter. No debe usar el algoritmo anterior si se trata de idiomas asiáticos/del este de Asia/del sur de Asia.
O, más bien, si desea comenzar con el código correcto, debe hacerlo para cada graphemeCluster.
Véase también ReverseString, una pregunta de entrevista de C#
Sección 11.4: Buscar un rango de caracteres
La siguiente declaración coincide con todos los registros que tienen FName que comienza con una letra de la A a la F de la tabla de empleados.
SELECCIONE * DE Empleados DONDE FName LIKE '[AF]%'
Sección 11.5: Partido por rango o set
Coincide con cualquier carácter individual dentro del rango especificado (p. ej.: [af]) o conjunto (p. ej.: [abcdef]).
Este patrón de rango coincidiría con "gary" pero no con "mary":
SELECCIONE * DE Empleados DONDE FName LIKE '[ag]ary'
Este patrón establecido coincidiría con "mary" pero no con "gary":
SELECCIONE * DE Empleados DONDE Fname LIKE '[lmnop]ary'
El rango o conjunto también se puede negar agregando el signo de intercalación ^ antes del rango o conjunto:
Este patrón de rango no coincidiría con "gary" pero sí con "mary":
SELECCIONE * DE Empleados DONDE FName LIKE '[^ag]ary'
Este patrón establecido no coincidiría con "mary" pero sí con "gary":
SELECCIONE * DE Empleados DONDE Fname LIKE '[^lmnop]ary'
Sección 11.6: Caracteres comodín
los caracteres comodín se utilizan con el operador SQL LIKE. Los comodines de SQL se utilizan para buscar datos dentro de una tabla.
Los comodines en SQL son:%, _, [lista de caracteres], [^ lista de caracteres]
% Un sustituto de cero o más caracteres
Por ejemplo: //selecciona todos los clientes con una ciudad que comienza con "Lo"
SELECCIONE * DE Clientes
DONDE Ciudad COMO 'Lo%';
//selecciona todos los clientes con una Ciudad que contenga el patrón "es"
SELECCIONE * DE Clientes
DONDE Ciudad LIKE '%es%';
_ Un sustituto de un solo carácter
Por ejemplo: // selecciona todos los clientes con una ciudad que comience con cualquier carácter, seguido de "erlin"
SELECCIONE * DE Clientes
GoalKicker.com Notas SQL para profesionales 41
Machine Translated by Google
DONDE Ciudad COMO '_erlin';
[charlist] Conjuntos y rangos de caracteres para hacer coincidir
Por ejemplo: //selecciona todos los clientes con una ciudad que comienza con "a", "d" o "l"
SELECCIONE * DE Clientes
DONDE Ciudad LIKE '[adl]%';
//selecciona todos los clientes con una ciudad que comienza con "a", "d" o "l"
SELECCIONE * DE Clientes
DONDE Ciudad LIKE '[ac]%';
[^charlist] : coincide solo con un carácter NO especificado entre corchetes
Por ejemplo: // selecciona todos los clientes con una ciudad que comienza con un carácter que no es "a", "p" o "l"
SELECCIONE * DE Clientes
DONDE Ciudad LIKE '[^apl]%';
SELECCIONE * DE Clientes
DONDE Ciudad NO COMO '[apl]%' y ciudad como '_%';
GoalKicker.com Notas SQL para profesionales 42
Machine Translated by Google
Capítulo 12: Cláusula IN
Sección 12.1: Cláusula IN simple
Para obtener registros que tengan cualquiera de los identificadores dados
seleccione *
de productos donde
id en (1,8,3)
La consulta anterior es igual a
seleccione *
de productos donde
id = 1 o id = 8
o identificación = 3
Sección 12.2: Uso de la cláusula IN con una subconsulta
SELECCIONAR *
DE clientes
DONDE ID EN (
SELECCIONE DISTINTO customer_id
DESDE pedidos
);
Lo anterior le dará todos los clientes que tienen pedidos en el sistema.
GoalKicker.com Notas SQL para profesionales 43
Machine Translated by Google
Capítulo 13: Filtrar resultados usando WHERE y
TENIENDO
Sección 13.1: Usar ENTRE para filtrar resultados
Los siguientes ejemplos utilizan las bases de datos de ejemplo de Ventas de artículos y Clientes.
Nota: El operador BETWEEN es inclusivo.
Usando el operador BETWEEN con Números:
SELECCIONE * De ItemSales
DONDE Cantidad ENTRE 10 Y 17
Esta consulta devolverá todos los registros ItemSales que tengan una cantidad mayor o igual a 10 y menor o igual
a 17. Los resultados se verán así:
Id Fecha de venta ItemId Cantidad Precio 1
20130701 100 10 34.5
4 20130723 100 15 34.5
5 20130724 145 10 34.5
Usando el operador BETWEEN con valores de fecha:
SELECCIONE * De ItemSales
DONDE SaleDate ENTRE '20130711' Y '20130524'
Esta consulta devolverá todos los registros ItemSales con una fecha de venta mayor o igual al 11 de julio de 2013 y menor o igual al 24 de
mayo de 2013.
Id SaleDate ItemId Cantidad Precio 3
20130711 100 20 34.5
4 20130723 100 15 34.5
5 20130724 145 10 34.5
Al comparar valores de fecha y hora en lugar de fechas, es posible que deba convertir los valores de fecha y hora en valores de
fecha, o sumar o restar 24 horas para obtener los resultados correctos.
Usando el operador BETWEEN con valores de texto:
SELECCIONE Id, FName, LName DE Clientes
DONDE LNombre ENTRE 'D' Y 'L';
Ejemplo en vivo: violín SQL
Esta consulta devolverá todos los clientes cuyo nombre esté alfabéticamente entre las letras 'D' y 'L'. En este caso, se devolverá el Cliente
#1 y #3. No se incluirá al cliente n.° 2, cuyo nombre comienza con una 'M'.
Id FName LNombre
GoalKicker.com Notas SQL para profesionales 44
Machine Translated by Google
1 Guillermo Jones
3 Ricardo Davis
Sección 13.2: Usar HAVING con funciones agregadas
A diferencia de la cláusula WHERE , HAVING se puede usar con funciones agregadas.
Una función agregada es una función donde los valores de múltiples filas se agrupan como entrada en ciertos criterios para
formar un valor único de significado o medida más significativo (Wikipedia).
Las funciones agregadas comunes incluyen COUNT(), SUM(), MIN() y MAX().
Este ejemplo utiliza la tabla de coches de las bases de datos de ejemplo.
SELECCIONE CustomerId, COUNT(Id) AS [Número de autos]
DESDE Coches
GRUPO POR ID de cliente
TENER CONTEO (Id) > 1
Esta consulta devolverá el ID de cliente y el número de automóviles de cualquier cliente que tenga más de un automóvil. En este caso, el único cliente
que tiene más de un auto es el Cliente #1.
Los resultados se verán como:
CustomerId Número de coches
1 2
Sección 13.3: Cláusula WHERE con valores NULL/NOT NULL
SELECCIONAR *
DE Empleados
DONDE ManagerId ES NULO
Esta declaración devolverá todos los registros de empleados en los que el valor de la columna ManagerId sea NULL.
El resultado será:
FNombre LNombre Número de ID de administrador DepartamentoId 1
identificación 1 Jaime Herrero teléfono 1234567890 NULO
SELECCIONAR *
DE Empleados
DONDE ManagerId NO ES NULO
Esta declaración devolverá todos los registros de empleados en los que el valor de ManagerId no sea NULL.
El resultado será:
FNombre LNombre Número de teléfono ID de administrador DepartamentoId 1
John Johnson 2468101214 1
identificación 2 3 Miguel Williams 1357911131 1 2
4 Johnathon Smith 1212121212 2 1
GoalKicker.com Notas SQL para profesionales 45
Machine Translated by Google
Nota: la misma consulta no arrojará resultados si cambia la cláusula WHERE a WHERE ManagerId = NULL o WHERE ManagerId <> NULL.
Sección 13.4: Igualdad
SELECCIONE * DE Empleados
Esta declaración devolverá todas las filas de la tabla Empleados.
Id FName LNombre Número de teléfono ID del administrador ID del departamento Salario Hire_date
Fecha de creación Fecha de modificación
1 Jaime herrero 1234567890 NULO 1 1000 01012002 01012002
01012002 2
Juan Johnson 2468101214 1 1 400 23032005 23032005
01012002 3
Michael Williams 1357911131 1 2 600 12052009 12052009
NULO
4 jonathan smith 1212121212 2 1 500 24072016 24072016
01012002
El uso de un DONDE al final de su instrucción SELECT le permite limitar las filas devueltas a una condición. En este caso, donde hay una
coincidencia exacta usando el signo = :
SELECCIONE * DE Empleados DONDE DepartmentId = 1
Solo devolverá las filas donde el DepartmentId es igual a 1:
Id FName LNombre Número de teléfono ID del administrador ID del departamento Salario Hire_date
Fecha de creación Fecha de modificación
1 Jaime herrero 1234567890 NULO 1 1000 01012002 01012002
01012002 2
Juan Johnson 2468101214 1 1 400 23032005 23032005
01012002
4 jonathan smith 1212121212 2 1 500 24072016 24072016
01012002
Sección 13.5: La cláusula WHERE solo devuelve filas que coinciden con sus
criterios
Steam tiene una sección de juegos por menos de $ 10 en su página de tienda. En algún lugar profundo en el corazón de sus sistemas,
probablemente haya una consulta que se parece a:
SELECCIONAR *
DESDE artículos
DONDE Precio < 10
Sección 13.6: Y y O
También puede combinar varios operadores para crear condiciones WHERE más complejas . Los siguientes ejemplos usan la tabla
Empleados :
Id FName LNombre Número de teléfono ID del administrador ID del departamento Salario Hire_date
GoalKicker.com Notas SQL para profesionales 46
Machine Translated by Google
Fecha de creación Fecha de modificación
1 Jaime herrero 1234567890 NULO 1 1000 01012002 01012002
01012002 2
Juan Johnson 2468101214 1 1 400 23032005 23032005
01012002 3
Michael Williams 1357911131 1 2 600 12052009 12052009
NULO
4 jonathan smith 1212121212 2 1 500 24072016 24072016
01012002
SELECCIONE * DE Empleados DONDE DepartmentId = 1 Y ManagerId = 1
Volverá:
Id FName LNombre Número de teléfono ID del administrador ID del departamento Salario Hire_date
Fecha de creación Fecha de modificación
2 John Johnson 2468101214 1 1 400 23032005 23032005
01012002
SELECCIONE * DE Empleados DONDE DepartmentId = 2 O ManagerId = 2
Volverá:
Id FName LNombre Número de teléfono ID del administrador ID del departamento Salario Hire_date
Fecha de creación Fecha de modificación
Michael Williams 1357911131 3 1 2 600 12052009 12052009
NULO
4 jonathan smith 1212121212 2 1 500 24072016 24072016
01012002
Sección 13.7: Use IN para devolver filas con un valor contenido en una lista
Este ejemplo utiliza la tabla de coches de las bases de datos de ejemplo.
SELECCIONAR *
DESDE Coches
DONDE ENTRADA CostoTotal (100, 200, 300)
Esta consulta devolverá el Auto #2 que cuesta 200 y el Auto #3 que cuesta 100. Tenga en cuenta que esto es equivalente a usar
varias cláusulas con OR, por ejemplo:
SELECCIONAR *
DESDE Coches
DONDE Costo Total = 100 O Costo Total = 200 O Costo Total = 300
Sección 13.8: Use LIKE para encontrar cadenas y subcadenas coincidentes
Consulte la documentación completa sobre el operador LIKE.
GoalKicker.com Notas SQL para profesionales 47
Machine Translated by Google
Este ejemplo utiliza la tabla de empleados de las bases de datos de ejemplo.
SELECCIONAR *
DE Empleados
DONDE FNombre COMO 'Juan'
Esta consulta solo devolverá el Empleado n.° 1 cuyo nombre coincida exactamente con 'John'.
SELECCIONAR *
DE Empleados
DONDE FNombre como 'John%'
Agregar % le permite buscar una subcadena:
John% : devolverá cualquier empleado cuyo nombre comience con 'John', seguido de cualquier cantidad de caracteres
%John : devolverá cualquier empleado cuyo nombre termine en 'John', precedido por cualquier cantidad de caracteres
%John% : devolverá cualquier empleado cuyo nombre contenga 'John' en cualquier lugar dentro del valor
En este caso, la consulta devolverá el Empleado n.º 2 cuyo nombre es 'John', así como el Empleado n.º 4 cuyo nombre es 'Johnathon'.
Sección 13.9: Donde EXISTE
Seleccionará registros en TableName que tengan registros coincidentes en TableName1.
SELECCIONE * DE TableName t DONDE EXISTE
( SELECCIONE 1 DE TableName1 t1 donde t.Id = t1.Id)
Sección 13.10: Use HAVING para verificar múltiples condiciones en
un grupo
Tabla de pedidos
CustomerId ProductId Cantidad Precio 1
2 5 100
1 3 2 200
1 4 1 500
2 1 4 50
3 5 6 700
Para verificar si hay clientes que hayan pedido ambos, ProductID 2 y 3, se puede usar HAVING
seleccione el ID de
cliente de los
pedidos donde ID de producto en
(2,3) grupo por ID de
cliente con recuento (ID de producto distinto ) = 2
Valor de retorno:
Identificación del cliente
La consulta selecciona solo registros con los ID de producto en las preguntas y con la cláusula HAVING verifica los grupos
GoalKicker.com Notas SQL para profesionales 48
Machine Translated by Google
tener 2 productIds y no solo uno.
Otra posibilidad sería
seleccione el ID de
cliente del
grupo de pedidos por ID
de cliente que tiene suma (caso cuando ID de producto = 2 luego 1 más
0 final) > 0 y suma (caso cuando ID de producto = 3 luego 1 más 0 final) > 0
Esta consulta selecciona solo grupos que tienen al menos un registro con productID 2 y al menos uno con productID 3.
GoalKicker.com Notas SQL para profesionales 49
Machine Translated by Google
Capítulo 14: SALTAR TOMA (Paginación)
Sección 14.1: Limitación de la cantidad de resultados
SQL ISO/ANSI:
SELECCIONE * DE TableName FETCH SOLAMENTE LAS PRIMERAS 20 FILAS;
mysql; PostgresSQL; SQLite:
SELECCIONE * DESDE TableName LIMIT 20;
Oráculo:
SELECCIONE Identificación,
col1
DE (SELECCIONE ID,
Col1,
número_de_fila() sobre (ordenar por Id) NúmeroDeFila
DESDE NombreDeTabla)
DONDE Número de Fila <= 20
Servidor SQL:
SELECCIONA LOS 20 PRINCIPALES *
DESDE dbo.[Venta]
Sección 14.2: Saltar y luego tomar algunos resultados (Paginación)
SQL ISO/ANSI:
SELECCIONE Id, Col1
DESDE NombreTabla
ORDEN POR ID
DESPLAZAR 20 FILAS OBTENER LAS SIGUIENTES 20 FILAS SOLAMENTE;
mysql:
SELECCIONE * DESDE TableName LIMIT 20, 20; compensación, límite
Oráculo; Servidor SQL:
SELECCIONE Identificación,
col1
DE (SELECCIONE ID,
Col1,
número_de_fila() sobre (ordenar por Id) NúmeroDeFila
DESDE NombreDeTabla)
DONDE RowNumber ENTRE 21 Y 40
PostgresSQL; SQLite:
SELECCIONE * DESDE TableName LIMIT 20 OFFSET 20;
GoalKicker.com Notas SQL para profesionales 50
Machine Translated by Google
Sección 14.3: Saltarse algunas filas del resultado
SQL ISO/ANSI:
SELECCIONE Id, Col1
DESDE NombreTabla
ORDEN POR ID
DESPLAZAMIENTO 20 FILAS
mysql:
SELECCIONE * DESDE TableName LIMIT 20, 42424242424242; salta 20 para
usar un número muy grande que es más que filas en la tabla
Oráculo:
SELECCIONE Identificación,
col1
DE (SELECCIONE ID,
Col1,
número_de_fila() sobre (ordenar por Id) NúmeroDeFila
DESDE NombreDeTabla)
DONDE Número de Fila > 20
PostgresSQL:
SELECCIONE * DESDE TableName OFFSET 20;
SQLite:
SELECCIONE * DESDE TableName LIMIT 1 OFFSET 20;
GoalKicker.com Notas SQL para profesionales 51
Machine Translated by Google
Capítulo 15: EXCEPTO
Sección 15.1: Seleccionar conjunto de datos excepto donde los valores están en este
otro conjunto de datos
los esquemas del conjunto de datos deben ser idénticos
SELECCIONE 'Data1' como 'Columna' UNION ALL
SELECCIONE 'Data2' como 'Columna' UNION ALL
SELECCIONE 'Data3' como 'Columna' UNION ALL
SELECCIONE 'Data4' como 'Columna' UNION ALL
SELECCIONE 'Data5' como 'Columna'
EXCEPTO
SELECCIONE 'Data3' como 'Columna'
Devuelve Data1, Data2, Data4 y Data5
GoalKicker.com Notas SQL para profesionales 52
Machine Translated by Google
Capítulo 16: EXPLICA Y DESCRIBE
Sección 16.1: EXPLICAR Seleccionar consulta
Una explicación delante de una consulta de selección le muestra cómo se ejecutará la consulta. De esta manera, verá si la consulta usa un índice o si puede
optimizar su consulta agregando un índice.
Consulta de ejemplo:
explicar seleccionar * del usuario unirse a los datos en user.test = data.fk_user;
Resultado de ejemplo:
id select_type tipo de tabla prueba de prueba de índice key_len ref (nulo) filas adicionales
1 índice SIMPLE usuario de clave de possible_keys 5 1 Usando donde; Usando
1 SIMPLE datos árbitro usuario_fk fk_usuario 5 prueba de usuario 1 (nulo)
en el tipo se ve si se utilizó un índice. En la columna possible_keys se ve si el plan de ejecución puede elegir entre diferentes índices o si no existe ninguno. La tecla
le dice el índice usado acutal. key_len le muestra el tamaño en bytes de un elemento de índice. Cuanto menor sea este valor, más elementos de índice caben en el
mismo tamaño de memoria y se pueden procesar más rápido. filas le muestra el número esperado de filas que la consulta necesita escanear, cuanto más
bajo, mejor.
Sección 16.2: DESCRIBE tablename;
DESCRIBE y EXPLICA son sinónimos. DESCRIBE en un nombre de tabla devuelve la definición de las columnas.
DESCRIBE nombre de tabla;
Resultado de ejemplo:
ID de COLUMN_NAME COLUMN_TYPE ES_NULABLE COLUMN_KEY COLUMN_DEFAULT
int(11) NO EN Incremento automático EXTRA
prueba varchar(255) SÍ 0 (nulo)
Aquí verá los nombres de las columnas, seguidos del tipo de columnas. Muestra si se permite nulo en la columna y si la columna usa un índice. el valor predeterminado
también se muestra y si la tabla contiene algún comportamiento especial como un
autoincremento.
GoalKicker.com Notas SQL para profesionales 53
Machine Translated by Google
Capítulo 17: CLÁUSULA EXISTE
Sección 17.1: CLÁUSULA EXISTE
Mesa de clientes
Identificación Nombre Apellido
1 Özgur Ozturk
2 Youssef Médi
3 Enrique Este
Tabla de pedidos
Id. Cantidad de Id. de cliente
1 2 123.50
2 3 14.80
Obtener todos los clientes con al menos un pedido
SELECCIONE * DESDE Cliente DONDE EXISTE (
SELECCIONE * DESDE Pedido DONDE Pedido.CustomerId=Customer.Id
)
Resultado
Identificación Nombre Apellido
2 Youssef Médi
3 Enrique Este
Obtener todos los clientes sin pedido
SELECCIONE * DE Cliente DONDE NO EXISTE (
SELECCIONE * DESDE Pedido DONDE Pedido.Id. de Cliente = Id. de Cliente
)
Resultado
Identificación Nombre Apellido
1 Özgur Ozturk
Objetivo
EXISTS, IN y JOIN podrían usarse en algún momento para el mismo resultado, sin embargo, no son iguales:
EXISTS debe usarse para verificar si existe un valor en otra tabla
IN debe usarse para la lista estática
JOIN debe usarse para recuperar datos de otra(s) tabla(s)
GoalKicker.com Notas SQL para profesionales 54
Machine Translated by Google
Capítulo 18: ÚNETE
JOIN es un método para combinar (unir) información de dos tablas. El resultado es un conjunto unido de columnas de ambas tablas, definido por el
tipo de combinación (INTERIOR/EXTERIOR/CRUZADO e IZQUIERDO/DERECHO/COMPLETO, explicado a continuación) y el criterio de combinación
(cómo se relacionan las filas de ambas tablas).
Una mesa puede estar unida a sí misma oa cualquier otra mesa. Si se necesita acceder a la información de más de dos tablas, se
pueden especificar varias uniones en una cláusula FROM.
Sección 18.1: Unión propia
Una tabla puede estar unida a sí misma, con diferentes filas que coincidan entre sí por alguna condición. En este caso de uso, se
deben usar alias para distinguir las dos ocurrencias de la tabla.
En el siguiente ejemplo, para cada empleado en la tabla de empleados de la base de datos de ejemplo, se devuelve un registro que
contiene el nombre del empleado junto con el nombre correspondiente del gerente del empleado. Dado que los gerentes también
son empleados, la tabla se une consigo misma:
SELECCIONE e.FName COMO
"Empleado", m.FName COMO "Gerente"
FROM
Empleados e
JOIN
Empleados m
ON e.ManagerId = m.Id
Esta consulta devolverá los siguientes datos:
Gerente de empleados
John Jaime
miguel james
jonathan juan
Entonces, ¿cómo funciona esto?
La tabla original contiene estos registros:
Id FName LName Número de teléfono ID del gerente ID del departamento Salario Fecha de contratación
1 Santiago Smith 1234567890 NULO 1 1000 01012002
2 Juan Johnson 2468101214 1 1 400 23032005
3 Michael Williams 1357911131 1 2 600 05122009
4 jonathan smith 1212121212 2 1 500 24072016
La primera acción es crear un producto cartesiano de todos los registros de las tablas utilizadas en la cláusula FROM . En este
caso, es la tabla Empleados dos veces, por lo que la tabla intermedia se verá así (he eliminado los campos que no se usan en
este ejemplo):
e.Id e.FName e.ManagerId m.Id m.FName m.ManagerId 1 James
NULL 1 James NULO
1 Santiago NULO 2 Juan 1
1 Santiago NULO 3 Miguel 1
GoalKicker.com Notas SQL para profesionales 55
Machine Translated by Google
1 Santiago NULO 4 jonathan 2
2 Juan 1 1 Santiago NULO
2 Juan 1 2 Juan 1
2 Juan 1 3 Miguel 1
2 Juan 1 4 jonathan 2
3 Miguel 1 1 Santiago NULO
3 Miguel 1 2 Juan 1
3 Miguel 1 3 Miguel 1
3 Miguel 1 4 jonathan 2
4 jonathan 2 1 Santiago NULO
4 jonathan 2 2 Juan 1
4 jonathan 2 3 Miguel 1
4 jonathan 2 4 jonathan 2
La siguiente acción es mantener solo los registros que cumplan con los criterios de JOIN , por lo que cualquier registro donde la tabla e con alias
ManagerId es igual al ID de la tabla m con alias :
e.Id e.FName e.ManagerId m.Id m.FName m.ManagerId 2 John
1 1 James NULL
3 Miguel 1 1 James NULO
4 jonathan 2 2 Juan 1
Luego, cada expresión utilizada dentro de la cláusula SELECT se evalúa para devolver esta tabla:
e.FName m.FName
John James
Michael James
jonathan juan
Finalmente, los nombres de columna e.FName y m.FName se reemplazan por sus nombres de columna de alias, asignados con el
operador AS:
Gerente de empleados
John Jaime
miguel james
jonathan juan
Sección 18.2: Diferencias entre uniones internas/externas
SQL tiene varios tipos de combinación para especificar si las filas (no) coincidentes se incluyen en el resultado: INNER JOIN, LEFT
OUTER JOIN, RIGHT OUTER JOIN y FULL OUTER JOIN (las palabras clave INNER y OUTER son opcionales). La siguiente figura
destaca las diferencias entre estos tipos de uniones: el área azul representa los resultados devueltos por la unión y el área blanca representa
los resultados que la unión no devolverá.
GoalKicker.com Notas SQL para profesionales 56
Machine Translated by Google
Presentación pictórica SQL de combinación cruzada (referencia) :
A continuación se muestran ejemplos de este respuesta.
Por ejemplo, hay dos tablas de la siguiente manera:
GoalKicker.com Notas SQL para profesionales 57
Machine Translated by Google
A B
1 2 3 4
3 5
4 6
Tenga en cuenta que (1,2) son exclusivos de A, (3,4) son comunes y (5,6) son exclusivos de B.
Unir internamente
Una unión interna usando cualquiera de las consultas equivalentes da la intersección de las dos tablas, es decir, las dos filas que tienen en común:
seleccione * de un INNER JOIN b en aa = bb; seleccione a.*,b.*
de a,b donde aa = bb;
un | b
+
3 | 3 4
| 4
Izquierda combinación externa
Una combinación externa izquierda dará todas las filas de A, además de las filas comunes de B:
seleccione * de una UNIÓN EXTERNA IZQUIERDA b en aa = bb;
un | b
+
1 | nulo 2 |
nulo 3 | 3 4
| 4
Unión exterior derecha
De manera similar, una combinación externa derecha dará todas las filas en B, además de las filas comunes en A:
seleccione * de una UNIÓN EXTERNA DERECHA b en aa = bb;
a | b
+
3 | 3 |
4 4
nulo | 5 nulo |
6
Unión exterior completa
Una combinación externa completa le dará la unión de A y B, es decir, todas las filas en A y todas las filas en B. Si algo en A no tiene un dato correspondiente en B, entonces
la parte B es nula, y viceversa.
seleccione * de una UNIÓN EXTERNA COMPLETA b en aa = bb;
a | b
+
58
GoalKicker.com Notas SQL para profesionales
Machine Translated by Google
1 | nulo 2 |
nulo 3 | 3 4 |
4 nulo | nulo
|
sesenta
y cinco
Sección 18.3: JOIN Terminología: Interior, Exterior, Semi, Anti..
Digamos que tenemos dos tablas (A y B) y algunas de sus filas coinciden (en relación con la condición JOIN dada,
cualquiera que sea en el caso particular):
GoalKicker.com Notas SQL para profesionales 59
Machine Translated by Google
Podemos usar varios tipos de combinación para incluir o excluir filas coincidentes o no coincidentes de cualquier lado, y nombrar correctamente la
combinación eligiendo los términos correspondientes del diagrama anterior.
Los siguientes ejemplos utilizan los siguientes datos de prueba:
CREAR TABLA A (
X varchar(255) CLAVE PRINCIPAL
GoalKicker.com Notas SQL para profesionales 60
Machine Translated by Google
);
CREAR TABLA B (
Y varchar(255) CLAVE PRIMARIA
);
INSERTAR EN UN VALORES
('Emy'),
('John'),
('Lisa'),
('Marcos'),
('Phil');
INSERTAR EN VALORES B
('Lisa'),
('Marcos'),
('Fila'),
('Tim'),
('Vicente');
Unir internamente
Combina las filas izquierda y derecha que coinciden.
SELECCIONE * DE A ÚNASE A B EN X = Y;
X Y
Lisa Lisa
Marco Marco
phil phil
Izquierda combinación externa
A veces abreviado como "unión izquierda". Combina las filas izquierda y derecha que coinciden e incluye filas izquierdas que no coinciden.
filas
GoalKicker.com Notas SQL para profesionales 61
Machine Translated by Google
SELECCIONE * DESDE UNA IZQUIERDA ÚNASE B EN X = Y ;
X Y
amy NULO
Juan NULL
Lisa Lisa
Marco Marco
phil phil
Unión exterior derecha
A veces abreviado como "unión derecha". Combina filas izquierda y derecha que coinciden e incluye filas derechas que no coinciden
filas
GoalKicker.com Notas SQL para profesionales 62
Machine Translated by Google
SELECCIONE * DESDE A LA DERECHA ÚNASE B EN X = Y;
X Y
Lisa Lisa
Marco Marco
phil phil
Tim NULL
NULO Vicente
Unión exterior completa
A veces abreviado como "unión completa". Unión de unión externa izquierda y derecha.
GoalKicker.com Notas SQL para profesionales 63
Machine Translated by Google
SELECCIONE * DE UNA UNIÓN COMPLETA B EN X = Y;
X Y
amy NULO
Juan NULL
Lisa Lisa
Marco Marco
phil phil
Tim NULL
NULO Vicente
Unión semi izquierda
Incluye las filas de la izquierda que coinciden con las filas de la derecha.
64
GoalKicker.com Notas SQL para profesionales
Machine Translated by Google
SELECCIONE * DESDE A DONDE X EN (SELECCIONE Y DESDE B);
X
Lisa
marco
phil
Unión semi derecha
Incluye filas de la derecha que coinciden con las filas de la izquierda.
GoalKicker.com Notas SQL para profesionales sesenta y cinco
Machine Translated by Google
SELECCIONE * DE B DONDE Y EN (SELECCIONE X DE A);
Y
Lisa
marco
phil
Como puede ver, no hay una sintaxis IN dedicada para la semiunión izquierda frente a la derecha: logramos el efecto simplemente cambiando las posiciones de la tabla
dentro del texto SQL.
Izquierda Anti Semi Unión
Incluye filas de la izquierda que no coinciden con las filas de la derecha.
GoalKicker.com Notas SQL para profesionales 66
Machine Translated by Google
SELECCIONE * DE A DONDE X NO ESTÁ EN (SELECCIONE Y DE B);
X
amy
John
ADVERTENCIA: ¡ Tenga cuidado si está usando NOT IN en una columna NULLable! Más detalles aquí.
Anti Semi Unión Derecha
Incluye filas de la derecha que no coinciden con las filas de la izquierda.
GoalKicker.com Notas SQL para profesionales
67
Machine Translated by Google
SELECCIONE * DE B DONDE Y NO ESTÁ EN (SELECCIONE X DE A);
Y
Tim
Vicente
Como puede ver, no hay una sintaxis NOT IN dedicada para la semi unión izquierda frente a la derecha: logramos el efecto simplemente cambiando las posiciones de la tabla dentro del
texto SQL.
Unión cruzada
Un producto cartesiano de todas las filas a la izquierda con todas a la derecha.
SELECCIONE * DE UNA CRUZ ÚNASE A B;
X Y
amy Lisa
Juan Lisa
Lisa Lisa
Marco Lisa
phillisa
amy marco
John Marco
Lisa Marco
Marco Marco
Phil Marco
amy phil
juan phil
lisa phil
Marco Phil
phil phil
amy Tim
68
GoalKicker.com Notas SQL para profesionales
Machine Translated by Google
juan tim
lisa tim
marca tim
phil tim
amy Vicente
Juan Vicente
lisa vicente
Marco Vincent
phil vicente
La combinación cruzada es equivalente a una combinación interna con una condición de combinación que siempre coincide, por lo que la siguiente
consulta habría devuelto el mismo resultado:
SELECCIONE * DE A ÚNASE A B EN 1 = 1;
AutoUnirse
Esto simplemente denota una tabla que se une a sí misma. Una autounión puede ser cualquiera de los tipos de unión discutidos anteriormente. Por ejemplo,
esta es una autounión interna:
SELECCIONE * DE A A1 ÚNASE A A2 EN LEN (A1.X) < LEN (A2.X);
X X
amy juan
amy lisa
amy marco
John Marco
Lisa Marco
Phil Marco
amy phil
Sección 18.4: Unión exterior izquierda
Una combinación externa izquierda (también conocida como combinación izquierda o combinación externa) es una combinación que garantiza que todas las filas de la
tabla de la izquierda estén representadas; si no existe ninguna fila coincidente de la tabla de la derecha, sus campos correspondientes son NULL.
El siguiente ejemplo seleccionará todos los departamentos y el nombre de los empleados que trabajan en ese departamento.
Los departamentos sin empleados todavía se devuelven en los resultados, pero tendrán NULL para el nombre del empleado:
SELECT Departments.Name, Employees.FName FROM
Departments LEFT OUTER JOIN
Empleados ON
Departamentos.Id = Empleados.DepartamentoId
Esto devolvería lo siguiente de la base de datos de ejemplo:
Departamentos.Nombre Empleados.FName HR James
HORA John
HORA jonathan
Ventas Miguel
tecnología NULO
Entonces, ¿cómo funciona esto?
GoalKicker.com Notas SQL para profesionales 69
Machine Translated by Google
Hay dos tablas en la cláusula FROM:
Id FName LName Número de teléfono ID del gerente ID del departamento Salario Fecha de contratación
1 Santiago Smith 1234567890 NULO 1 1000 01012002
2 Juan Johnson 2468101214 1 1 400 23032005
3 Michael Williams 1357911131 1 2 600 05122009
4 jonathan smith 1212121212 2 1 500 24072016
Nombre de identificación
1 hora
2 Ventas
3 tecnología
Primero se crea un producto cartesiano a partir de las dos tablas dando una tabla intermedia.
Los registros que cumplen con los criterios de unión (Departments.Id = Employees.DepartmentId) están resaltados en negrita; estos
se pasan a la siguiente etapa de la consulta.
Como se trata de una UNIÓN EXTERNA IZQUIERDA, todos los registros se devuelven desde el lado IZQUIERDO de la unión
(Departamentos), mientras que los registros del lado DERECHO reciben un marcador NULL si no coinciden con los criterios de unión. En la
siguiente tabla, esto devolverá Tech con NULL
Id Nombre Id FName LName Número de teléfono ID del gerente ID del departamento Salario Fecha de contratación
1 HR 1 James Smith 1234567890 NULO 1 1000 01012002
1 hora 2 Juan Johnson 2468101214 1 1 400 23032005
1 hora 3 Miguel Williams 1357911131 1 2 600 05122009
1 HR 4 Johnathon Smith 1212121212 2 1 500 24072016
2 Ventas 1 Jaime Smith 1234567890 NULO 1 1000 01012002
2 Ventas 2 Juan Johnson 2468101214 1 1 400 23032005
2 Ventas 3 Michael Williams 1357911131 1 2 600 05122009
2 Ventas 4 Johnathon Smith 1212121212 2 1 500 24072016
3 Tecnología 1 James Smith 1234567890 NULO 1 1000 01012002
3 Tecnología 2 Juan Johnson 2468101214 1 1 400 23032005
3 Tecnología 3 Michael Williams 1357911131 1 2 600 05122009
3 Tecnología 4 Johnathon Smith 1212121212 2 1 500 24072016
Finalmente, cada expresión utilizada dentro de la cláusula SELECT se evalúa para devolver nuestra tabla final:
Departamentos.Nombre Empleados.FName HR
James John
HORA
Ventas Ricardo
tecnología NULO
Sección 18.5: Unión implícita
Las uniones también se pueden realizar teniendo varias tablas en la cláusula from , separadas por comas y definiendo
, la relación entre
ellas en la cláusula where . Esta técnica se denomina unión implícita (ya que en realidad no contiene una cláusula de unión ).
GoalKicker.com Notas SQL para profesionales 70
Machine Translated by Google
Todos los RDBMS lo admiten, pero generalmente se desaconseja la sintaxis. Las razones por las que es una mala idea usar esta sintaxis
son:
Es posible obtener uniones cruzadas accidentales que luego arrojan resultados incorrectos, especialmente si tiene muchas uniones en la
consulta.
Si pretendía una unión cruzada, entonces no está claro en la sintaxis (escriba CROSS JOIN en su lugar), y es probable que alguien la cambie
durante el mantenimiento.
El siguiente ejemplo seleccionará los nombres de los empleados y el nombre de los departamentos para los que trabajan:
SELECCIONE e.FName,
d.Name FROM Empleado e, Departamentos
d DONDE e.DeptartmentId = d.Id
Esto devolvería lo siguiente de la base de datos de ejemplo:
e.FName d.Nombre
James HR
John HORA
Ricardo Ventas
Sección 18.6: UNIÓN CRUZADA
La unión cruzada hace un producto cartesiano de los dos miembros. Un producto cartesiano significa que cada fila de una tabla
se combina con cada fila de la segunda tabla en la unión. Por ejemplo, si TABLEA tiene 20 filas y TABLEB tiene 20 filas, el
resultado sería 20*20 = 400 filas de salida.
Usando una base de datos de ejemplo
SELECCIONE d.Nombre,
e.FNombre DE
Departamentos d CROSS JOIN Empleados e;
que devuelve:
d.Nombre e.FNombre
HORA Jaime
HORA John
HORA Miguel
HORA jonathan
ventas james
Ventas Juan
ventas miguel
Johnatón de ventas
James técnico
Juan técnico
Miguel técnico
Johnatón tecnológico
Se recomienda escribir un CROSS JOIN explícito si desea realizar una unión cartesiana, para resaltar que esto es lo que desea.
GoalKicker.com Notas SQL para profesionales 71
Machine Translated by Google
Sección 18.7: APLICACIÓN CRUZADA Y UNIÓN LATERAL
Un tipo muy interesante de JOIN es LATERAL JOIN (nuevo en PostgreSQL 9.3+), que también se conoce
como CROSS APPLY/OUTER APPLY en SQLServer y Oracle.
La idea básica es que se aplica una función con valores de tabla (o subconsulta en línea) para cada fila que se une.
Esto hace posible, por ejemplo, unir solo la primera entrada coincidente en otra tabla.
La diferencia entre una unión normal y una lateral radica en el hecho de que puede usar una columna que unió previamente en la subconsulta que "CRUZA
APLICAR".
Sintaxis:
PostgreSQL 9.3+
izquierda | derecho | UNIÓN interior LATERAL
Servidor SQL:
CRUZ | APLICACIÓN EXTERIOR
UNIÓN INTERNA LATERAL es lo mismo que APLICACIÓN CRUZADA
y LEFT JOIN LATERAL es lo mismo que OUTER APPLY
Ejemplo de uso (PostgreSQL 9.3+):
SELECCIONE * DE T_Contactos
LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit EN MAP_CTCOU_CT_UID = T_Contacts.CT_UID Y MAP_CTCOU_SoftDeleteStatus = 1 DONDE
T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID
ES NULO 989
UNIÓN IZQUIERDA LATERAL
(
SELECCIONAR
MAP_CTCOU_UID
MAP_CTCOU_CT_UID
,MAP_CTCOU_COU_UID
,MAP_CTCOU_DateFrom
,MAP_CTCOU_DateTo
DESDE T_MAP_Contacts_Ref_OrganisationalUnit DONDE
MAP_CTCOU_SoftDeleteStatus = 1 Y MAP_CTCOU_CT_UID
= T_Contacts.CT_UID
/*
Y (
(__in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo)
Y
(__in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom)
) */
ORDENAR POR MAP_CTCOU_DateFrom
LÍMITE 1
GoalKicker.com Notas SQL para profesionales 72
Machine Translated by Google
) AS PrimeroOE
Y para SQLServer
SELECCIONE * DE T_Contactos
LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit EN MAP_CTCOU_CT_UID = T_Contacts.CT_UID Y MAP_CTCOU_SoftDeleteStatus = 1 DONDE
T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID ES NULO 989
APLICACIÓN CRUZADA = UNIÓN INTERNA
APLICACIÓN EXTERIOR = UNIRSE A LA IZQUIERDA
SELECCIONA 1 PRINCIPAL
MAP_CTCOU_UID
MAP_CTCOU_CT_UID
,MAP_CTCOU_COU_UID
,MAP_CTCOU_DateFrom
,MAP_CTCOU_DateTo
DESDE T_MAP_Contacts_Ref_OrganisationalUnit DONDE
MAP_CTCOU_SoftDeleteStatus = 1 Y MAP_CTCOU_CT_UID
= T_Contacts.CT_UID
/*
Y (
(@in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo)
Y
(@in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom)
) */
ORDENAR POR MAP_CTCOU_DateFrom
) AS PrimeroOE
Sección 18.8: UNIÓN COMPLETA
Un tipo de JOIN menos conocido es el FULL JOIN.
(Nota: MySQL no admite FULL JOIN a partir de 2016)
A FULL OUTER JOIN devuelve todas las filas de la tabla de la izquierda y todas las filas de la tabla de la derecha.
Si hay filas en la tabla de la izquierda que no tienen coincidencias en la tabla de la derecha, o si hay filas en la tabla de la derecha que no tienen
coincidencias en la tabla de la izquierda, esas filas también se mostrarán.
Ejemplo 1 :
SELECCIONE * DE la Tabla1
UNIÓN COMPLETA Table2
EN 1 = 2
Ejemplo 2:
SELECCIONE COALESCE(T_Budget.Year, tYear.Year) COMO
RPT_BudgetInYear ,COALESCE(T_Budget.Value, 0.0) COMO RPT_Value
DESDE T_Presupuesto
UNIÓN COMPLETA tfu_RPT_All_CreateYearInterval(@budget_year_from, @budget_year_to) AS tYear
GoalKicker.com Notas SQL para profesionales 73
Machine Translated by Google
ON tAño.Año = T_Presupuesto.Año
Tenga en cuenta que si está utilizando eliminaciones temporales, tendrá que verificar el estado de eliminación temporal nuevamente en la cláusula
WHERE (porque FULL JOIN se comporta como una
UNIÓN); Es fácil pasar por alto este pequeño hecho, ya que coloca AP_SoftDeleteStatus = 1 en la cláusula de combinación.
Además, si está realizando una UNIÓN COMPLETA, normalmente tendrá que permitir NULL en la cláusula WHERE; olvidar permitir NULL en un
valor tendrá los mismos efectos que una unión INTERNA, que es algo que no desea si está haciendo una UNIÓN COMPLETA.
Ejemplo:
SELECCIONAR
T_AccountPlan.AP_UID ,T_AccountPlan.AP_Code ,T_AccountPlan.AP_Lang_EN ,T_BudgetPositions.BUP_Budget ,T_BudgetPositions.BUP_UID ,T_BudgetPositio
ÚNASE POR COMPLETO
T_AccountPlan EN T_AccountPlan.AP_UID = T_BudgetPositions.BUP_AP_UID Y
T_AccountPlan.AP_SoftDeleteStatus = 1
DONDE (1=1)
Y (T_BudgetPositions.BUP_SoftDeleteStatus = 1 O T_BudgetPositions.BUP_SoftDeleteStatus ES NULO)
Y (T_AccountPlan.AP_SoftDeleteStatus = 1 O T_AccountPlan.AP_SoftDeleteStatus ES NULO)
Sección 18.9: JOIN recursivos
Las uniones recursivas se utilizan a menudo para obtener datos padrehijo. En SQL, se implementan con expresiones de tablas comunes recursivas,
por ejemplo:
CON MisDescendientes RECURSIVO COMO (
SELECCIONE Nombre
DE Personas
DONDE Nombre = 'John Doe'
UNIÓN TODOS
SELECCIONE People.Name
FROM People
JOIN MyDescendants ON People.Name = MyDescendants.Parent
)
SELECCIONE * DE Mis Descendientes;
Sección 18.10: Unión interna explícita básica
Una unión básica (también llamada "unión interna") consulta datos de dos tablas, con su relación definida en una cláusula de unión .
El siguiente ejemplo seleccionará los nombres de los empleados (FName) de la tabla Empleados y el nombre del departamento para el que trabajan
(Nombre) de la tabla Departamentos:
SELECCIONE Empleados.FNombre, Departamentos.Nombre
DE Empleados ÚNASE
Departamentos
GoalKicker.com Notas SQL para profesionales 74
Machine Translated by Google
ON Employees.DepartmentId = Departamentos.Id
Esto devolvería lo siguiente de la base de datos de ejemplo:
Empleados.FNombre Departamentos.Nombre James HR
John
HORA
Ricardo Ventas
Sección 18.11: Unión en una subconsulta
Unirse a una subconsulta se usa a menudo cuando desea obtener datos agregados de una tabla secundaria/de detalles y mostrarlos junto con los registros
de la tabla principal/de encabezado. Por ejemplo, es posible que desee obtener un recuento de registros secundarios, un promedio de alguna columna
numérica en registros secundarios o la fila superior o inferior en función de una fecha o un campo numérico. Este ejemplo usa alias, lo que hace que las
consultas sean más fáciles de leer cuando hay varias tablas involucradas. Así es como se ve una unión de subconsulta bastante típica. En este caso,
recuperamos todas las filas de la tabla principal Órdenes de compra y recuperamos solo la primera fila de cada registro principal de la tabla secundaria
Elementos de línea de pedido de compra.
SELECCIONE po.Id, po.PODate, po.VendorName, po.Status, item.ItemNo,
item.Description, item.Cost, item.Price FROM
PurchaseOrders po LEFT
JOIN
(
SELECCIONE l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price, Min(l.id) as Id FROM
PurchaseOrderLineItems l
GRUPO POR l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price
) COMO artículo ON artículo.PurchaseOrderId = po.Id
GoalKicker.com Notas SQL para profesionales 75
Machine Translated by Google
Capítulo 19: ACTUALIZACIÓN
Sección 19.1: ACTUALIZAR con datos de otra tabla
Los ejemplos a continuación completan un número de teléfono para cualquier empleado que también sea cliente y actualmente no tenga un número de
teléfono establecido en la tabla de empleados .
(Estos ejemplos utilizan las tablas de empleados y clientes de las bases de datos de ejemplo).
SQL estándar
Actualizar usando una subconsulta correlacionada:
ACTUALIZAR
Empleados
SET PhoneNumber =
(SELECT
c.PhoneNumber
DE
Clientes c
DÓNDE
c.FName = Empleados.FName Y
c.LName = Empleados.LName)
DONDE Employees.PhoneNumber ES NULO
SQL:2003
Actualizar usando MERGE:
UNIRSE CON
Empleados
USANDO
Clientes c
EN
e.FName = c.Fname Y
e.LName = c.LName Y
e.PhoneNumber ES NULO
CUANDO EMPAREJADO ENTONCES
ACTUALIZAR SET PhoneNumber = c.PhoneNumber
servidor SQL
Actualizar usando INNER JOIN:
ACTUALIZAR
Empleados
COLOCAR
Número de teléfono = c. Número de teléfono
DESDE
Empleados
INNER JOIN Clientes c
ON e.FName = c.FName
Y e.LName = c.LName
DÓNDE
Número de teléfono ES NULO
GoalKicker.com Notas SQL para profesionales 76
Machine Translated by Google
Sección 19.2: Modificación de valores existentes
Este ejemplo utiliza la tabla Cars de las bases de datos de ejemplo.
ACTUALIZAR Coches
SET CostoTotal = CostoTotal + 100
DONDE Id = 3 o Id = 4
Las operaciones de actualización pueden incluir valores actuales en la fila actualizada. En este ejemplo simple, TotalCost se incrementa
en 100 para dos filas:
El costo total del automóvil n. ° 3 aumentó de 100 a 200
El costo total del automóvil n. ° 4 aumentó de 1254 a 1354
El nuevo valor de una columna puede derivarse de su valor anterior o del valor de cualquier otra columna en la misma tabla o en una tabla unida.
Sección 19.3: Actualización de filas especificadas
Este ejemplo utiliza la tabla Cars de las bases de datos de ejemplo.
ACTUALIZAR
Carros
COLOCAR
Estado = 'LISTO'
DÓNDE
identificación = 4
Esta declaración establecerá el estado de la fila de 'Autos' con id 4 en "LISTO".
La cláusula WHERE contiene una expresión lógica que se evalúa para cada fila. Si una fila cumple los criterios, su valor se actualiza. De lo
contrario, una fila permanece sin cambios.
Sección 19.4: Actualización de todas las filas
Este ejemplo utiliza la tabla Cars de las bases de datos de ejemplo.
ACTUALIZAR Coches
ESTABLECER Estado = 'LISTO'
Esta declaración establecerá la columna 'estado' de todas las filas de la tabla 'Autos' en "LISTO" porque no tiene un DONDE
cláusula para filtrar el conjunto de filas.
Sección 19.5: Captura de registros actualizados
A veces uno quiere capturar los registros que se acaban de actualizar.
CREAR TABLA # TempUpdated (ID INT)
Actualizar TableName SET Col1 = 42 SALIDA
insertada.ID EN #TempUpdated DONDE Id > 50
GoalKicker.com Notas SQL para profesionales 77
Machine Translated by Google
Capítulo 20: Base de datos CREATE
Sección 20.1: Base de datos CREAR
Se crea una base de datos con el siguiente comando SQL:
CREAR BASE DE DATOS miBase de Datos;
Esto crearía una base de datos vacía llamada myDatabase donde puede crear tablas.
GoalKicker.com Notas SQL para profesionales 78
Machine Translated by Google
Capítulo 21: CREAR TABLA
Parámetro Detalles
tableName El nombre de la tabla
Contiene una 'enumeración' de todas las columnas que tiene la tabla. Consulte Crear una nueva tabla para obtener más detalles.
columnas
La declaración CREATE TABLE se usa para crear una nueva tabla en la base de datos. Una definición de tabla consta de una lista de
columnas, sus tipos y cualquier restricción de integridad.
Sección 21.1: Crear tabla a partir de seleccionar
Es posible que desee crear un duplicado de una tabla:
CREAR TABLA ClonedEmployees COMO SELECCIONAR * DE Empleados;
Puede usar cualquiera de las otras características de una declaración SELECT para modificar los datos antes de pasarlos a la nueva tabla.
Las columnas de la nueva tabla se crean automáticamente de acuerdo con las filas seleccionadas.
CREAR TABLA ModificadoEmpleados COMO
SELECCIONE Id, CONCAT(FName," ",LName) AS FullName FROM Empleados
DONDE Id > 10;
Sección 21.2: Crear una nueva tabla
Se puede crear una tabla básica de Empleados , que contiene una identificación y el nombre y apellido del empleado junto con su número de
teléfono usando
CREAR TABLA Empleados(
Id int identidad (1,1) clave principal no nula,
FName varchar (20) no es nulo,
LName varchar (20) no es nulo,
Número de teléfono varchar (10) no nulo
);
Este ejemplo es específico de TransactSQL
CREAR TABLA crea una nueva tabla en la base de datos, seguida del nombre de la tabla, Empleados
A esto le sigue la lista de nombres de columna y sus propiedades, como el ID
Id int identidad (1,1) no nulo
Valor Significado
Identificación
el nombre de la columna.
En t es el tipo de datos.
establece que la columna tendrá valores generados automáticamente que comienzan en 1 y se incrementan en 1 para
cada identidad (1,1)
nueva fila
la clave principal establece que todos los valores en esta columna tendrán valores únicos
no nulo establece que esta columna no puede tener valores nulos
Sección 21.3: CREAR TABLA CON CLAVE EXTRANJERA
A continuación puede encontrar la tabla Empleados con una referencia a la tabla Ciudades.
GoalKicker.com Notas SQL para profesionales 79
Machine Translated by Google
CREAR TABLA Ciudades(
CityID INT IDENTIDAD(1,1) NO NULO,
Nombre VARCHAR(20) NO NULO,
Zip VARCHAR(10) NO NULO
);
CREAR TABLA Empleados(
EmployeeID INT IDENTIDAD (1,1) NO NULO,
Nombre VARCHAR(20) NO NULO,
Apellido VARCHAR(20) NO NULO,
Número de teléfono VARCHAR(10) NO NULO,
CityID INT REFERENCIAS CLAVE EXTRANJERAS Ciudades (CityID)
);
Aquí podría encontrar un diagrama de base de datos.
La columna CityID de la tabla Employees hará referencia a la columna CityID de la tabla Cities. A continuación puede encontrar la sintaxis
para hacer esto.
CityID INT REFERENCIAS CLAVE EXTRANJERAS Ciudades (CityID)
Valor Significado
ID de la ciudad Nombre de la columna
En t
tipo de columna
FOREIGN KEY Hace la clave foránea (opcional)
REFERENCIAS hace la referencia
Ciudades (CityID) a la tabla Ciudades columna CityID
Importante: no se puede hacer referencia a una tabla que no existe en la base de datos. Sea fuente para hacer primero la tabla Ciudades
y segundo la tabla Empleados. Si lo haces al revés, arrojará un error.
Sección 21.4: Duplicar una tabla
Para duplicar una tabla, simplemente haga lo siguiente:
CREAR TABLA newtable LIKE oldtable;
INSERT newtable SELECT * FROM oldtable;
Sección 21.5: Crear una tabla temporal o en memoria
PostgreSQL y SQLite
GoalKicker.com Notas SQL para profesionales 80
Machine Translated by Google
Para crear una tabla temporal local para la sesión:
CREAR TABLA TEMPORAL MiTabla(...);
servidor SQL
Para crear una tabla temporal local para la sesión:
CREAR TABLA #TempPhysical(...);
Para crear una tabla temporal visible para todos:
CREAR TABLA ##TempPhysicalVisibleToEveryone(...);
Para crear una tabla en memoria:
DECLARAR @TempMemory TABLE(...);
GoalKicker.com Notas SQL para profesionales 81
Machine Translated by Google
Capítulo 22: CREAR FUNCIÓN
Argumento Descripción el
nombre_función nombre de la función
list_of_paramenters parámetros que la función acepta
return_data_type tipo que devuelve la función. Algún tipo de datos SQL
function_body el codigo de funcion
escalar_expresión valor escalar devuelto por la función
Sección 22.1: Crear una nueva función
CREAR FUNCIÓN Primera Palabra (@input varchar(1000))
DEVUELVE varchar(1000)
COMO
COMENZAR
DECLARAR @output varchar(1000)
SET @output = SUBSTRING(@input, 0, CASE CHARINDEX(' ', @input)
CUANDO 0 ENTONCES LEN(@input) + 1
ELSE CHARINDEX(' ', @entrada)
FIN)
RETORNO @salida
FIN
Este ejemplo crea una función denominada FirstWord, que acepta un parámetro varchar y devuelve otro valor varchar.
GoalKicker.com Notas SQL para profesionales 82
Machine Translated by Google
Capítulo 23: INTENTAR/ATRAPAR
Sección 23.1: Transacción en un TRY/CATCH
Esto revertirá ambas inserciones debido a una fecha y hora no válida:
COMENZAR TRANSACCIÓN
COMENZAR
INTENTAR INSERTAR EN dbo.Venta (Precio, Fecha de venta, Cantidad)
VALORES (5.2, GETDATE(), 1)
INSERTAR EN dbo. Venta (Precio, Fecha de venta, Cantidad)
VALORES (5.2, 'no es una cita', 1)
COMPROMETER TRANSACCIÓN
FIN DE PRUEBA
COMENZAR LA CAPTURA
TIRAR
TRANSACCIÓN DE RETROCESO
FIN DE CAPTURA
Esto confirmará ambas inserciones:
COMENZAR TRANSACCIÓN
COMENZAR
INTENTAR INSERTAR EN dbo.Venta (Precio, Fecha de venta, Cantidad)
VALORES (5.2, GETDATE(), 1)
INSERTAR EN dbo. Venta (Precio, Fecha de venta, Cantidad)
VALORES (5.2, GETDATE(), 1)
COMPROMETER TRANSACCIÓN
FIN DE PRUEBA
COMENZAR LA CAPTURA
TIRAR
TRANSACCIÓN DE RETROCESO
FIN DE CAPTURA
GoalKicker.com Notas SQL para profesionales 83
Machine Translated by Google
Capítulo 24: UNIÓN / UNIÓN TODOS
La palabra clave UNION en SQL se usa para combinar los resultados de la declaración SELECT sin ningún duplicado. Para usar UNION y
combinar resultados, ambas declaraciones SELECT deben tener el mismo número de columnas con el mismo tipo de datos en el mismo orden, pero
la longitud de la columna puede ser diferente.
Sección 24.1: consulta UNION ALL básica
CREAR TABLA HR_EMPLOYEES (
ID de persona int,
Apellido VARCHAR(30),
Nombre VARCHAR(30),
Posición VARCHAR(30)
);
CREAR TABLA FINANZAS_EMPLEADOS (
ID de persona INT,
Apellido VARCHAR(30),
Nombre VARCHAR(30),
Posición VARCHAR(30)
);
Digamos que queremos extraer los nombres de todos los gerentes de nuestros departamentos.
Usando un UNION podemos obtener todos los empleados de los departamentos de Recursos Humanos y Finanzas, que ocupan el puesto de un
gerente
SELECCIONAR
Nombre, Apellido
DE
RRHH_EMPLEADOS
DÓNDE
Posición = 'gerente'
UNIÓN TODOS
SELECCIONAR
Nombre, Apellido
DE
FINANZAS_EMPLEADOS
DÓNDE
Posición = 'gerente'
La instrucción UNION elimina las filas duplicadas de los resultados de la consulta. Dado que es posible tener personas con el mismo nombre y cargo
en ambos departamentos, estamos utilizando UNION ALL, para no eliminar duplicados.
Si desea utilizar un alias para cada columna de salida, simplemente puede colocarlos en la primera declaración de selección, de la siguiente manera:
SELECCIONAR
Nombre como 'Nombre', Apellido como 'Apellido'
DE
RRHH_EMPLEADOS
DÓNDE
Posición = 'gerente'
UNIÓN TODOS
SELECCIONAR
Nombre, Apellido
DE
GoalKicker.com Notas SQL para profesionales 84
Machine Translated by Google
FINANZAS_EMPLEADOS
DÓNDE
Posición = 'gerente'
Sección 24.2: Explicación simple y Ejemplo
En lenguaje sencillo:
UNION une 2 conjuntos de resultados mientras elimina duplicados del conjunto de resultados
UNION ALL une 2 conjuntos de resultados sin intentar eliminar los duplicados
Un error que comete mucha gente es usar una UNIÓN cuando no necesitan que se eliminen los duplicados.
El costo de rendimiento adicional frente a grandes conjuntos de resultados puede ser muy significativo.
Cuándo podrías necesitar UNION
Suponga que necesita filtrar una tabla con 2 atributos diferentes y ha creado índices no agrupados separados para cada columna. UNA
UNIÓN le permite aprovechar ambos índices y al mismo tiempo evitar los duplicados.
SELECCIONE C1, C2, C3 DESDE Table1 DONDE C1 = @Param1
UNIÓN
SELECCIONE C1, C2, C3 DESDE Table1 DONDE C2 = @Param2
Esto simplifica el ajuste del rendimiento, ya que solo se necesitan índices simples para realizar estas consultas de manera óptima.
Incluso es posible que pueda arreglárselas con un poco menos de índices no agrupados, lo que también mejora el rendimiento general de
escritura en comparación con la tabla de origen.
Cuando podrías necesitar UNION ALL
Suponga que aún necesita filtrar una tabla con 2 atributos, pero no necesita filtrar registros duplicados (ya sea porque no importa o porque sus
datos no producirían ningún duplicado durante la unión debido al diseño de su modelo de datos).
SELECCIONE C1 DE Table1
UNIÓN TODOS
SELECCIONE C1 DE Table2
Esto es especialmente útil cuando se crean vistas que unen datos que están diseñados para particionarse físicamente en varias tablas
(quizás por razones de rendimiento, pero aún desea acumular registros). Dado que los datos ya están divididos, hacer que el motor de la base
de datos elimine los duplicados no agrega valor y solo agrega tiempo de procesamiento adicional a las consultas.
GoalKicker.com Notas SQL para profesionales 85
Machine Translated by Google
Capítulo 25: ALTER TABLE
El comando ALTER en SQL se usa para modificar la columna/restricción en una tabla
Sección 25.1: Agregar columna(s)
ALTER TABLE Empleados
AÑADIR Fecha de inicio fecha NO NULO PREDETERMINADO GetDate(),
DateOfBirth fecha NULL
La declaración anterior agregaría columnas denominadas Fecha de inicio que no puede ser NULL con el valor predeterminado como fecha actual y
Fecha de nacimiento que puede ser NULL en la tabla Empleados.
Sección 25.2: Columna desplegable
ALTER TABLE Empleados
DROP COLUMN salario;
Esto no solo eliminará la información de esa columna, sino que eliminará el salario de la columna de los empleados de la tabla (la columna ya no
existirá).
Sección 25.3: Agregar clave principal
ALTER TABLE EMPLOYEES AGREGAR pk_EmployeeID CLAVE PRINCIPAL (ID)
Esto agregará una clave principal a la tabla Empleados en el ID de campo. Incluir más de un nombre de columna entre paréntesis junto con el ID
creará una clave primaria compuesta. Al agregar más de una columna, los nombres de las columnas deben estar separados por comas.
ALTER TABLE EMPLOYEES AGREGAR pk_EmployeeID CLAVE PRINCIPAL (ID, FName)
Sección 25.4: Alterar columna
ALTER TABLE Empleados
ALTER COLUMN Fecha de inicio DATETIME NOT NULL DEFAULT (GETDATE())
Esta consulta alterará el tipo de datos de la columna de Fecha de inicio y lo cambiará de fecha simple a fecha y hora y establecerá el valor
predeterminado en la fecha actual.
Sección 25.5: Restricción de caída
ALTER TABLE Empleados
DROP CONSTRAINT DefaultSalary
Esto Elimina una restricción llamada DefaultSalary de la definición de la tabla de empleados.
Nota: Asegúrese de eliminar las restricciones de la columna antes de eliminar una columna.
GoalKicker.com Notas SQL para profesionales 86
Machine Translated by Google
Capítulo 26: INSERTAR
Sección 26.1: INSERTAR datos de otra tabla usando SELECCIONAR
INSERTAR EN Clientes (FName, LName, PhoneNumber)
SELECCIONE FName, LName, PhoneNumber DE Empleados
Este ejemplo insertará todos los Empleados en la tabla Clientes. Dado que las dos tablas tienen campos diferentes y no desea mover todos
los campos, debe establecer en qué campos insertar y qué campos seleccionar. Los nombres de los campos correlacionados no
necesitan llamarse de la misma manera, pero deben ser del mismo tipo de datos. Este ejemplo asume que el campo Id tiene un
conjunto de especificaciones de identidad y se incrementará automáticamente.
Si tiene dos tablas que tienen exactamente los mismos nombres de campo y solo desea mover todos los registros, puede
usar:
INSERTAR EN Table1
SELECCIONE * DE Table2
Sección 26.2: Insertar nueva fila
INSERTAR EN VALORES
de Clientes ('Zack', 'Smith', '[email protected]', '7049989942', 'EMAIL');
Esta instrucción insertará una nueva fila en la tabla Clientes . Tenga en cuenta que no se especificó un valor para la columna Id , ya que se
agregará automáticamente. Sin embargo, se deben especificar todos los demás valores de columna.
Sección 26.3: Insertar solo columnas especificadas
INSERTAR EN Clientes (FName, LName, Email, PreferredContact)
VALORES ('Zack', 'Smith', '[email protected]', 'EMAIL');
Esta instrucción insertará una nueva fila en la tabla Clientes . Los datos solo se insertarán en las columnas especificadas; tenga en cuenta que
no se proporcionó ningún valor para la columna PhoneNumber . Tenga en cuenta, sin embargo, que se deben incluir todas las columnas
marcadas como no nulas .
Sección 26.4: Insertar varias filas a la vez
Se pueden insertar varias filas con un solo comando de inserción:
INSERTAR EN tbl_name (campo1, campo2, campo3)
VALORES (1,2,3), (4,5,6), (7,8,9);
Para insertar grandes cantidades de datos (inserción masiva) al mismo tiempo, existen características y recomendaciones específicas de
DBMS.
MySQL CARGAR ARCHIVO DE DATOS
MSSQL INSERCIÓN A GRANEL
GoalKicker.com Notas SQL para profesionales 87
Machine Translated by Google
Capítulo 27: FUSIÓN
MERGE (a menudo también llamado UPSERT por "actualizar o insertar") permite insertar nuevas filas o, si ya existe una fila, actualizar la fila
existente. El punto es realizar todo el conjunto de operaciones atómicamente (para garantizar que los datos permanezcan consistentes) y evitar la
sobrecarga de comunicación para múltiples declaraciones SQL en un sistema cliente/servidor.
Sección 27.1: FUSIONAR para hacer que el objetivo coincida con el origen
COMBINAR EN targetTable t
UTILIZANDO sourceTable s
ON t.PKID = s.PKID
CUANDO COINCIDE Y NO EXISTE (
SELECCIONE s.ColumnA, s.ColumnB, s.ColumnC
INTERSECT
SELECT t.ColumnA, t.ColumnB, s.ColumnC )
LUEGO ACTUALIZAR
SET t.ColumnA =
s.ColumnA ,t.ColumnB =
s.ColumnB ,t.ColumnC =
s.ColumnC CUANDO NO COINCIDE
CON EL OBJETIVO ENTONCES INSERTAR (PKID, ColumnaA, ColumnaB, ColumnaC)
VALORES (s.PKID, s.ColumnA, s.ColumnB, s.ColumnC)
CUANDO NO COINCIDE CON LA FUENTE
LUEGO ELIMINAR
;
Nota: La parte Y NO EXISTE evita la actualización de registros que no han cambiado. El uso de la construcción INTERSECT permite comparar
columnas anulables sin un manejo especial.
Sección 27.2: MySQL: conteo de usuarios por nombre
Supongamos que queremos saber cuántos usuarios tienen el mismo nombre. Vamos a crear usuarios de tabla de la siguiente manera:
crear usuarios de tabla ( id
int clave principal auto_incremento, nombre
varchar (8), recuento
int, nombre
de clave única (nombre)
);
Ahora, acabamos de descubrir un nuevo usuario llamado Joe y nos gustaría tenerlo en cuenta. Para lograr eso, debemos determinar si existe una fila
con su nombre y, de ser así, actualizarla para incrementar la cuenta; por otro lado, si no existe una fila, debemos crearla.
MySQL usa la siguiente sintaxis: insertar … en la actualización de clave duplicada …. En este caso:
inserte en los valores de los usuarios
(nombre, conteo) ('Joe',
1) en la actualización de clave duplicada conteo = conteo + 1;
Sección 27.3: PostgreSQL: conteo de usuarios por nombre
Supongamos que queremos saber cuántos usuarios tienen el mismo nombre. Vamos a crear usuarios de tabla de la siguiente manera:
GoalKicker.com Notas SQL para profesionales 88
Machine Translated by Google
crear usuarios de tabla
( id serial,
nombre varchar (8) único,
contar int
);
Ahora, acabamos de descubrir un nuevo usuario llamado Joe y nos gustaría tenerlo en cuenta. Para lograr eso, debemos determinar
si existe una fila con su nombre y, de ser así, actualizarla para incrementar la cuenta; por otro lado, si no existe una fila, debemos
crearla.
PostgreSQL usa la siguiente sintaxis: insertar ... en conflicto ... actualizar …. En este caso:
inserte en los valores de los usuarios
(nombre, recuento)
('Joe', 1) en conflicto (nombre) actualice el conjunto de recuento = usuarios. recuento + 1;
GoalKicker.com Notas SQL para profesionales 89
Machine Translated by Google
Capítulo 28: aplicación cruzada, aplicación externa
Sección 28.1: Conceptos básicos de APLICACIÓN CRUZADA y APLICACIÓN EXTERNA
Aplicar se utilizará cuando el valor de la tabla funcione en la expresión correcta.
cree una tabla de departamentos para almacenar información sobre los departamentos. Luego cree una tabla de empleados que
contenga información sobre los empleados. Tenga en cuenta que cada empleado pertenece a un departamento, por lo que la tabla
Empleado tiene integridad referencial con la tabla Departamento.
La primera consulta selecciona datos de la tabla Departamento y usa APLICACIÓN CRUZADA para evaluar la tabla Empleado para
cada registro de la tabla Departamento. La segunda consulta simplemente une la tabla Departamento con la tabla Empleado y se
producen todos los registros coincidentes.
SELECCIONAR *
DEL Departamento D
APLICACIÓN CRUZADA (
SELECCIONAR *
FROM Empleado E
DONDE E.DepartmentID = D.DepartmentID ) A GO
SELECT *
FROM Departamento D
INNER JOIN Empleado E ON
D.DepartmentID = E.DepartmentID
Si observa los resultados que produjeron, es exactamente el mismo conjunto de resultados; ¿En qué se diferencia de JOIN y cómo ayuda
a escribir consultas más eficientes?
La primera consulta en el Script #2 selecciona datos de la tabla Departamento y usa APLICACIÓN EXTERNA para evaluar la tabla
Empleado para cada registro de la tabla Departamento. Para aquellas filas para las que no hay una coincidencia en la tabla de
empleados, esas filas contienen valores NULL, como puede ver en el caso de las filas 5 y 6. La segunda consulta simplemente
usa una UNIÓN EXTERNA IZQUIERDA entre la tabla Departamento y la tabla Empleado. Como se esperaba, la consulta devuelve
todas las filas de la tabla Departamento; incluso para aquellas filas para las que no hay coincidencia en la tabla Empleado.
SELECCIONE * DE
Departamento D
APLICACIÓN
EXTERNA
( SELECCIONE * DE Empleado E DONDE E.DepartmentID = D.DepartmentID
) un
IR
SELECCIONAR *
FROM Departamento D
LEFT OUTER JOIN Empleado E ON
D.DepartmentID = E.DepartmentID GO
Aunque las dos consultas anteriores devuelven la misma información, el plan de ejecución será un poco diferente. Pero en cuanto al
costo, no habrá mucha diferencia.
Ahora llega el momento de ver dónde se requiere realmente el operador APPLY. En el Script #3, estoy creando una función con
valores de tabla que acepta DepartmentID como su parámetro y devuelve todos los empleados que pertenecen a este
departamento. La siguiente consulta selecciona datos de la tabla Departamento y usa APLICACIÓN CRUZADA para unirse a la función
GoalKicker.com Notas SQL para profesionales 90
Machine Translated by Google
creamos. Pasa el DepartmentID para cada fila de la expresión de la tabla externa (en nuestro caso, la tabla Department) y evalúa la función
para cada fila de forma similar a una subconsulta correlacionada. La siguiente consulta utiliza APLICACIÓN EXTERNA en lugar de
APLICACIÓN CRUZADA y, por lo tanto, a diferencia de APLICACIÓN CRUZADA que solo devuelve datos correlacionados, APLICACIÓN
EXTERNA también devuelve datos no correlacionados, colocando valores NULL en las columnas que faltan.
CREAR FUNCIÓN dbo.fn_GetAllEmployeeOfADepartment (@DeptID AS int)
TABLA DE DEVOLUCIONES
COMO
DEVOLVER
(
SELECCIONAR
*
FROM Empleado E
DONDE E.DepartmentID = @DeptID )
IR
SELECCIONAR
*
DESDE Departamento
D APLICACIÓN CRUZADA dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
IR
SELECCIONAR
*
DESDE Departamento
D APLICACIÓN EXTERNA dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
IR
Entonces, si se está preguntando, ¿podemos usar una combinación simple en lugar de las consultas anteriores? Entonces la respuesta es NO,
si reemplaza CROSS/OUTER APPLY en las consultas anteriores con INNER JOIN/LEFT OUTER JOIN, especifique la cláusula ON (algo así
como 1=1) y ejecute la consulta, obtendrá "El identificador de varias partes" D.DepartmentID" no se pudo vincular". error. Esto se debe a que
con JOIN, el contexto de ejecución de la consulta externa es diferente del contexto de ejecución de la función (o una tabla derivada), y no
puede vincular un valor/variable de la consulta externa a la función como parámetro.
Por lo tanto, se requiere el operador APPLY para tales consultas.
GoalKicker.com Notas SQL para profesionales 91
Machine Translated by Google
Capítulo 29: ELIMINAR
La instrucción DELETE se utiliza para eliminar registros de una tabla.
Sección 29.1: ELIMINAR todas las filas
Omitir una cláusula WHERE eliminará todas las filas de una tabla.
ELIMINAR DE Empleados
Consulte la documentación de TRUNCATE para obtener detalles sobre cómo el rendimiento de TRUNCATE puede mejorar porque ignora los activadores, los
índices y los registros para simplemente eliminar los datos.
Sección 29.2: ELIMINAR ciertas filas con DONDE
Esto eliminará todas las filas que coincidan con los criterios WHERE .
ELIMINAR DE Empleados
WHERE FNombre = 'Juan'
Sección 29.3: Cláusula TRUNCATE
Use esto para restablecer la tabla a la condición en la que fue creada. Esto elimina todas las filas y restablece valores como
autoincremento. Tampoco registra cada eliminación de fila individual.
TABLA TRUNCADA Empleados
Sección 29.4: ELIMINAR ciertas filas según las comparaciones con otras tablas
Es posible ELIMINAR datos de una tabla si coincide (o no coincide) con ciertos datos en otras tablas.
Supongamos que queremos ELIMINAR datos de la Fuente una vez que se cargan en el Destino.
ELIMINAR DE la fuente
DONDE EXISTE ( SELECCIONAR 1 : el valor específico en SELECCIONAR no importa
DESDE
Destino Donde Origen.ID = Destino.ID )
Las implementaciones de RDBMS más comunes (por ejemplo, MySQL, Oracle, PostgresSQL, Teradata) permiten que las tablas se unan durante la
ELIMINACIÓN , lo que permite una comparación más compleja en una sintaxis compacta.
Agregando complejidad al escenario original, supongamos que Aggregate se crea a partir de Target una vez al día y no contiene el mismo ID pero contiene la
misma fecha. Supongamos también que queremos eliminar los datos de la fuente solo después de que se complete el agregado para el día.
En MySQL, Oracle y Teradata, esto se puede hacer usando:
DELETE FROM Source
DONDE Source.ID = TargetSchema.Target.ID
AND TargetSchema.Target.Date = AggregateSchema.Aggregate.Date
En el uso de PostgreSQL:
GoalKicker.com Notas SQL para profesionales 92
Machine Translated by Google
ELIMINAR DEL Origen
UTILIZANDO TargetSchema.Target, AggregateSchema.Aggregate
DONDE Source.ID = TargetSchema.Target.ID AND
TargetSchema.Target.DataDate = AggregateSchema.Aggregate.AggDate
Básicamente, esto da como resultado INNER JOIN entre el origen, el destino y el agregado. La eliminación se realiza en el Origen cuando existen los
mismos ID en el Destino Y la fecha presente en el Destino para esos ID también existe en el Agregado.
También se puede escribir la misma consulta (en MySQL, Oracle, Teradata) como:
DELETE Source
FROM Source, TargetSchema.Target, AggregateSchema.Aggregate WHERE
Source.ID = TargetSchema.Target.ID AND
TargetSchema.Target.DataDate = AggregateSchema.Aggregate.AggDate
Las uniones explícitas se pueden mencionar en declaraciones de eliminación en algunas implementaciones de RDBMS (p. ej., Oracle, MySQL), pero
no se admiten en todas las plataformas (p. ej., Teradata no las admite)
Las comparaciones se pueden diseñar para verificar escenarios que no coinciden en lugar de hacerlos coincidir con todos los estilos de sintaxis (observe
NO EXISTE a continuación)
ELIMINAR DESDE
Origen DONDE NO EXISTE ( SELECCIONAR 1 : el valor específico en SELECCIONAR no
importa
DESDE Destino Donde Origen.ID = Destino.ID )
GoalKicker.com Notas SQL para profesionales 93
Machine Translated by Google
capitulo 30: truncamiento
La instrucción TRUNCATE elimina todos los datos de una tabla. Esto es similar a DELETE sin filtro, pero, según el software de la base de
datos, tiene ciertas restricciones y optimizaciones.
Sección 30.1: Eliminación de todas las filas de la tabla Empleado
TABLA TRUNCADA Empleado;
Usar una tabla truncada a menudo es mejor que usar ELIMINAR TABLA, ya que ignora todos los índices y activadores y simplemente
elimina todo.
Eliminar tabla es una operación basada en filas, lo que significa que se elimina cada fila. Truncar tabla es una operación de página de datos,
se reasigna toda la página de datos. Si tiene una tabla con un millón de filas, será mucho más rápido truncar la tabla que usar una declaración
de eliminación de tabla.
Aunque podemos eliminar Filas específicas con ELIMINAR, no podemos TRUNCAR filas específicas, solo podemos TRUNCAR todos los
registros a la vez. Eliminar todas las filas y luego insertar un nuevo registro continuará agregando el valor de la clave principal incrementada
automáticamente desde el valor insertado anteriormente, mientras que en Truncar, el valor de la clave primaria incrementada automáticamente
también se restablecerá y comenzará desde 1.
Tenga en cuenta que al truncar la tabla, no debe haber claves foráneas, de lo contrario obtendrá un error.
GoalKicker.com Notas SQL para profesionales 94
Machine Translated by Google
Capítulo 31: Tabla DROP
Sección 31.1: Comprobar la existencia antes de soltar
MySQL Versión 3.19
DROP TABLE SI EXISTE MyTable;
PostgreSQL versión 8.x
DROP TABLE SI EXISTE MyTable;
Servidor SQL versión 2005
Si existe (seleccione * de Information_Schema.Tables
Donde Table_Schema = 'dbo'
Y Table_Name = 'MiTabla')
Soltar tabla dbo.MyTable
SQLite versión 3.0
DROP TABLE SI EXISTE MyTable;
Sección 31.2: Caída simple
Drop Table MyTable;
GoalKicker.com Notas SQL para profesionales 95
Machine Translated by Google
Capítulo 32: DROP o DELETE Database
Sección 32.1: Base de datos DROP
Descartar la base de datos es una simple declaración de una sola línea. Eliminar la base de datos eliminará la base de datos, por lo tanto, siempre asegúrese de tener una copia
de seguridad de la base de datos si es necesario.
A continuación se muestra el comando para eliminar la base de datos de empleados
DROP DATABASE [dbo].[Empleados]
GoalKicker.com Notas SQL para profesionales 96
Machine Translated by Google
Capítulo 33: Eliminación en cascada
Sección 33.1: EN ELIMINAR CASCADA
Suponga que tiene una aplicación que administra salas.
Suponga además que su aplicación funciona por cliente (inquilino).
Tienes varios clientes.
Por lo tanto, su base de datos contendrá una tabla para clientes y otra para habitaciones.
Ahora, cada cliente tiene N habitaciones.
Esto debería significar que tiene una clave externa en la mesa de su habitación, que hace referencia a la mesa del cliente.
ALTER TABLE dbo.T_Room CON COMPROBAR AÑADIR RESTRICCIÓN FK_T_Room_T_Client FOREIGN KEY(RM_CLI_ID)
REFERENCIAS dbo.T_Client (CLI_ID)
IR
Suponiendo que un cliente cambie a otro software, deberá eliminar sus datos en su software. pero si lo haces
ELIMINAR DE T_Client DONDE CLI_ID = x
Entonces obtendrá una violación de clave externa, porque no puede eliminar al cliente cuando todavía tiene habitaciones.
Ahora tendría que escribir un código en su aplicación que elimine las habitaciones del cliente antes de que elimine al cliente. Suponga además que
en el futuro se agregarán muchas más dependencias de clave externa en su base de datos, debido a que la funcionalidad de su aplicación se
expande. Horrible. Por cada modificación en su base de datos, deberá adaptar el código de su aplicación en N lugares. Posiblemente también
tendrá que adaptar el código en otras aplicaciones (por ejemplo, interfaces a otros sistemas).
Hay una mejor solución que hacerlo en su código.
Simplemente puede agregar ON DELETE CASCADE a su clave externa.
ALTER TABLE dbo.T_Room CON COMPROBACIÓN SQLServer puede especificar CON COMPROBACIÓN/SIN
COMPROBACIÓN AGREGAR RESTRICCIÓN FK_T_Room_T_Client FOREIGN KEY(RM_CLI_ID)
REFERENCIAS dbo.T_Client (CLI_ID)
EN ELIMINAR CASCADA
Ahora puedes decir
ELIMINAR DE T_Client DONDE CLI_ID = x
y las habitaciones se eliminan automáticamente cuando se elimina el cliente.
Problema resuelto: sin cambios en el código de la aplicación.
Una palabra de precaución: en Microsoft SQLServer, esto no funcionará si tiene una tabla que hace referencia a sí misma. Entonces, si intenta definir
una cascada de eliminación en una estructura de árbol recursiva, como esta:
SI NO EXISTE (SELECCIONE * DESDE sys.foreign_keys DONDE object_id = OBJECT_ID(N'[dbo].
[FK_T_FMS_Navigation_T_FMS_Navigation]') Y parent_object_id = OBJECT_ID(N'[dbo].[T_FMS_Navigation]'))
ALTER TABLE [dbo].[T_FMS_Navigation] CON COMPROBAR AGREGAR RESTRICCIÓN
[FK_T_FMS_Navigation_T_FMS_Navigation] CLAVE EXTERNA ([NA_NA_UID])
REFERENCIAS [dbo].[T_FMS_Navigation] ([NA_UID])
EN ELIMINAR CASCADA
GoalKicker.com Notas SQL para profesionales 97
Machine Translated by Google
IR
SI EXISTE (SELECCIONE * DESDE sys.foreign_keys DONDE object_id =
OBJECT_ID(N'[dbo].[FK_T_FMS_Navigation_T_FMS_Navigation]') Y parent_object_id = OBJECT_ID(N'[dbo].
[T_FMS_Navigation]'))
ALTERAR TABLA [dbo].[T_FMS_Navigation] VERIFICAR RESTRICCIÓN [FK_T_FMS_Navigation_T_FMS_Navigation]
IR
no funcionará, porque MicrosoftSQLserver no le permite establecer una clave externa con ON DELETE CASCADE en una estructura de árbol recursiva. Una
de las razones de esto es que el árbol posiblemente sea cíclico, y eso posiblemente conduciría a un interbloqueo.
PostgreSQL, por otro lado, puede hacer esto; el requisito
es que el árbol no sea cíclico.
Si el árbol es cíclico, obtendrá un error de tiempo de ejecución.
En ese caso, solo tendrá que implementar la función de eliminación usted mismo.
Una palabra de precaución:
Esto significa que ya no puede simplemente eliminar y volver a insertar la tabla del cliente, porque si hace esto, eliminará todas las entradas en "T_Room"...
(ya no habrá actualizaciones que no sean delta)
GoalKicker.com Notas SQL para profesionales 98
Machine Translated by Google
Capítulo 34: OTORGAR y REVOCAR Sección 34.1: Otorgar/
revocar privilegios
OTORGAR SELECCIÓN, ACTUALIZAR
EN Empleados
A Usuario1, Usuario2;
Otorgue permiso a Usuario1 y Usuario2 para realizar operaciones SELECCIONAR y ACTUALIZAR en la tabla Empleados.
REVOCAR SELECCIONAR, ACTUALIZAR
EN Empleados
DESDE Usuario1, Usuario2;
Revocar a Usuario1 y Usuario2 el permiso para realizar operaciones SELECCIONAR y ACTUALIZAR en la tabla Empleados.
GoalKicker.com Notas SQL para profesionales 99
Machine Translated by Google
Capítulo 35: XML
Sección 35.1: Consulta de tipo de datos XML
DECLARAR @xmlIN XML = '<TableData> <aaa
Main="First">
<fila nombre="a" valor="1" /> <fila nombre="b"
valor="2" /> <fila nombre="c" valor="3" />
</aaaa>
<aaa Principal="Segundo">
<fila nombre="a" valor="3" /> <fila nombre="b"
valor="4" /> <fila nombre="c" valor="5" />
</aaa>
<aaa Principal="Tercero"> <fila
nombre="a" valor="10" /> <fila nombre="b"
valor="20" /> <fila nombre="c" valor=" 30" />
</aaaa>
</TableData>'
SELECCIONE t.col.value('../@Main', 'varchar(10)') [Encabezado], t.col.value('@name',
'VARCHAR(25)') [nombre], t. col.valor('@valor', 'VARCHAR(25)') [Valor]
DE @xmlIn.nodes('//TableData/aaa/row') AS t (col)
Resultados
Encabezamiento nombre Valor 1
Primero a
Primero b
Primero C
Segundo a
Segundo b 2
Segundo C
Tercero a 3
Tercero b 3
Tercero C 4 5 10 20 30
GoalKicker.com Notas SQL para profesionales 100
Machine Translated by Google
Capítulo 36: Claves primarias
Sección 36.1: Creación de una clave principal
CREAR TABLA Empleados (
Id int NO NULO,
CLAVE PRINCIPAL (Id),
...
);
Esto creará la tabla Empleados con 'Id' como su clave principal. La clave principal se puede utilizar para identificar de forma única las filas de
una tabla. Solo se permite una clave principal por tabla.
Una clave también puede estar compuesta por uno o más campos, denominada clave compuesta, con la siguiente sintaxis:
CREAR TABLA EMPLEADO
( e1_id INT,
e2_id INT,
CLAVE PRINCIPAL (e1_id, e2_id)
)
Sección 36.2: Uso del incremento automático
Muchas bases de datos permiten que el valor de la clave principal se incremente automáticamente cuando se agrega una nueva clave.
Esto asegura que cada tecla sea diferente.
mysql
CREAR TABLA Empleados (
Id int NOT NULL AUTO_INCREMENT,
CLAVE PRIMARIA (Id)
);
postgresql
CREAR TABLA Empleados (
ID CLAVE PRIMARIA DE SERIE
);
servidor SQL
CREAR TABLA Empleados (
Id int IDENTIDAD NO NULA ,
CLAVE PRIMARIA (Id)
);
SQLite
CREAR TABLA Empleados (
Id CLAVE PRIMARIA ENTERA
);
GoalKicker.com Notas SQL para profesionales 101
Machine Translated by Google
Capítulo 37: Índices
Los índices son una estructura de datos que contiene punteros al contenido de una tabla dispuesta en un orden específico, para ayudar a la base de
datos a optimizar las consultas. Son similares al índice de libro, donde las páginas (filas de la tabla) están indexadas por su número de página.
Existen varios tipos de índices y se pueden crear en una tabla. Cuando existe un índice en las columnas utilizadas en la cláusula WHERE, la
cláusula JOIN o la cláusula ORDER BY de una consulta, puede mejorar sustancialmente el rendimiento de la consulta.
Sección 37.1: Índice ordenado
Si usa un índice que está ordenado de la forma en que lo recuperaría, la instrucción SELECT no ordenaría más durante la recuperación.
CREAR ÍNDICE ix_scoreboard_score ON scoreboard (puntuación DESC);
Cuando ejecutas la consulta
SELECCIONE * DEL marcador ORDENAR POR puntuación DESC;
El sistema de la base de datos no realizaría una clasificación adicional, ya que puede realizar una búsqueda de índice en ese orden.
Sección 37.2: Índice parcial o filtrado
SQL Server y SQLite permiten crear índices que contienen no solo un subconjunto de columnas, sino también un subconjunto de filas.
Considere una cantidad de pedidos en constante crecimiento con order_state_id igual a terminado (2) y una cantidad estable de pedidos con
order_state_id igual a iniciado (1).
Si su negocio hace uso de consultas como esta:
SELECCIONE id, comentario
DESDE pedidos
DONDE order_state_id = 1
AND product_id = @some_value;
La indexación parcial le permite limitar el índice, incluyendo solo los pedidos sin terminar:
CREAR ÍNDICE Pedidos_iniciados
EN pedidos (product_id)
DONDE order_state_id = 1;
Este índice será más pequeño que un índice sin filtrar, lo que ahorra espacio y reduce el costo de actualizar el índice.
Sección 37.3: Creación de un índice
CREAR ÍNDICE ix_cars_employee_id ON Cars (EmployeeId);
Esto creará un índice para la columna EmployeeId en la tabla Cars. Este índice mejorará la velocidad de las consultas que le piden al servidor que
ordene o seleccione por valores en EmployeeId, como las siguientes:
SELECCIONE * DESDE Autos DONDE EmployeeId = 1
GoalKicker.com Notas SQL para profesionales 102
Machine Translated by Google
El índice puede contener más de 1 columna, como se muestra a continuación;
CREAR ÍNDICE ix_cars_e_c_o_ids ON Cars (EmployeeId, CarId, OwnerId);
En este caso, el índice sería útil para las consultas que piden ordenar o seleccionar por todas las columnas incluidas, si el conjunto de condiciones
se ordena de la misma manera. Eso significa que al recuperar los datos, puede encontrar las filas para recuperar utilizando el índice, en lugar de
buscar en la tabla completa.
Por ejemplo, el siguiente caso utilizaría el segundo índice;
SELECCIONE * DESDE Automóviles DONDE IdEmpleado = 1 Pedido por IdCarro DESC
Sin embargo, si el orden difiere, el índice no tiene las mismas ventajas, como a continuación;
SELECCIONE * DESDE Automóviles DONDE OwnerId = 17 Ordenar por CarId DESC
El índice no es tan útil porque la base de datos debe recuperar el índice completo, en todos los valores de EmployeeId y CarID, para encontrar qué
elementos tienen OwnerId = 17.
(Se puede seguir usando el índice; puede darse el caso de que el optimizador de consultas descubra que recuperar el índice y filtrar por el OwnerId, y
luego recuperar solo las filas necesarias, es más rápido que recuperar la tabla completa, especialmente si la tabla es grande).
Sección 37.4: Descartar un índice o deshabilitarlo y reconstruirlo
DROP INDEX ix_cars_employee_id ON Cars;
Podemos usar el comando DROP para eliminar nuestro índice. En este ejemplo, DROP el índice llamado ix_cars_employee_id en
la mesa Coches.
Esto elimina el índice por completo y, si el índice está agrupado, eliminará cualquier agrupación. No se puede reconstruir sin volver a crear el índice, lo
que puede ser lento y computacionalmente costoso. Como alternativa, el índice se puede deshabilitar:
ALTER INDEX ix_cars_employee_id ON Cars DESHABILITAR;
Esto permite que la tabla conserve la estructura, junto con los metadatos sobre el índice.
Fundamentalmente, esto conserva las estadísticas del índice, de modo que es posible evaluar fácilmente el cambio. Si se justifica, el índice se puede
reconstruir más tarde, en lugar de volver a crearlo por completo;
ALTER INDEX ix_cars_employee_id EN Autos RECONSTRUIR;
Sección 37.5: Índices agrupados, únicos y ordenados
Los índices pueden tener varias características que se pueden configurar en el momento de la creación o modificando los índices existentes.
CREAR ÍNDICE CLÚSTER ix_clust_employee_id ON Empleados (EmployeeId, Correo electrónico);
La instrucción SQL anterior crea un nuevo índice agrupado en Empleados. Los índices agrupados son índices que dictan la estructura real de la tabla; la
tabla en sí se ordena para que coincida con la estructura del índice. Eso significa que puede haber como máximo un índice agrupado en una tabla. Si ya
existe un índice agrupado en la tabla, la declaración anterior
GoalKicker.com Notas SQL para profesionales 103
Machine Translated by Google
fallar. (Las tablas sin índices agrupados también se denominan montones).
CREAR ÍNDICE ÚNICO uq_customers_email ON Clientes (Correo electrónico);
Esto creará un índice único para la columna Correo electrónico en la tabla Clientes. Este índice, además de acelerar las consultas como un
índice normal, también obligará a que cada dirección de correo electrónico en esa columna sea única. Si se inserta o actualiza una fila con un valor
de correo electrónico no exclusivo , la inserción o actualización fallará de forma predeterminada.
CREAR ÍNDICE ÚNICO ix_eid_desc ON Clientes (EmployeeID);
Esto crea un índice en Clientes que también crea una restricción de tabla de que el ID de empleado debe ser único.
(Esto fallará si la columna no es actualmente única; en este caso, si hay empleados que comparten una identificación).
CREAR ÍNDICE ix_eid_desc ON Clientes (EmployeeID Desc);
Esto crea un índice que se ordena en orden descendente. De forma predeterminada, los índices (en el servidor MSSQL, al menos) son
ascendentes, pero eso se puede cambiar.
Sección 37.6: Índice de reconstrucción
Con el transcurso del tiempo, los índices de BTree pueden fragmentarse debido a la actualización/eliminación/inserción de datos. En la
terminología de SQLServer, podemos tener interna (página de índice que está medio vacía) y externa (el orden de la página lógica no
corresponde al orden físico). Reconstruir el índice es muy similar a eliminarlo y volver a crearlo.
Podemos reconstruir un índice con
ALTER INDEX index_name RECONSTRUIR;
Por defecto, la reconstrucción del índice es una operación fuera de línea que bloquea la tabla y evita que DML permita la , pero muchos RDBMS
reconstrucción en línea. Además, algunos proveedores de bases de datos ofrecen alternativas a la reconstrucción de índices, como
REORGANIZE (SQLServer) o COALESCE/SHRINK SPACE (Oracle).
Sección 37.7: Inserción con un índice único
ACTUALIZAR Clientes SET Email = "[email protected]" WHERE id = 1;
Esto fallará si se establece un índice único en la columna Correo electrónico de Clientes. Sin embargo, se puede definir un comportamiento alternativo
para este caso:
ACTUALIZAR Clientes ESTABLECER Correo electrónico = "[email protected]" DONDE id = 1 EN CLAVE DUPLICADA ;
GoalKicker.com Notas SQL para profesionales 104
Machine Translated by Google
Capítulo 38: Número de fila
Sección 38.1: Eliminar todos menos el último registro (tabla de 1 a muchos)
CON cte COMO (
SELECCIONE ID de proyecto,
ROW_NUMBER() SOBRE (PARTICIÓN POR ProjectID ORDEN POR InsertDate DESC) COMO rn
DE Notas del proyecto
)
ELIMINAR DESDE cte DONDE rn > 1;
Sección 38.2: Números de fila sin particiones
Incluya un número de fila según el orden especificado.
SELECCIONAR
ROW_NUMBER() SOBRE(ORDENAR POR Fname ASC) COMO RowNumber,
Nombre,
LNombre
DE Empleados
Sección 38.3: Números de fila con particiones
Utiliza un criterio de partición para agrupar la numeración de filas de acuerdo con él.
SELECCIONAR
ROW_NUMBER() SOBRE(PARTICIÓN POR IdDepartamento ORDEN POR IdDepartamento ASC) AS NúmeroDeFila,
DepartmentId, Fname, LName
DE Empleados
GoalKicker.com Notas SQL para profesionales 105
Machine Translated by Google
Capítulo 39: SQL Group By vs Distinct
Sección 39.1: Diferencia entre GROUP BY y DISTINCT
GROUP BY se utiliza en combinación con funciones de agregación. Considere la siguiente tabla:
orderId userId storeName orderValue orderDate 1 20032016
43 Tienda A 25
2 57 Tienda B 50 22032016
3 43 Tienda A 30 25032016
4 82 Tienda C 10 26032016
5 21 Tienda A 45 29032016
La consulta siguiente utiliza GROUP BY para realizar cálculos agregados.
SELECCIONAR
nombre de la tienda,
COUNT(*) COMO total_nr_orders,
CONTAR ( ID de usuario DISTINTO) COMO nr_unique_customers,
AVG(orderValue) COMO promedio_order_value,
MIN(fechapedido) COMO primer_pedido,
MAX ( fecha de pedido ) AS último pedido
DE
pedidos
AGRUPAR POR
nombre de la tienda;
y devolverá la siguiente información
storeName total_nr_orders nr_unique_customers Average_order_value first_order lastOrder
Tienda A 3 2 33.3 20032016 29032016
Tienda B 1 1 50 22032016 22032016
Tienda C 1 1 10 26032016 26032016
Mientras que DISTINCT se usa para enumerar una combinación única de valores distintos para las columnas especificadas.
SELECCIONE DISTINTO
nombre de la tienda,
ID de usuario
DESDE pedidos;
ID de usuario del nombre de la tienda
Tienda A 43
Tienda B 57
Tienda C 82
tienda a las 21
GoalKicker.com Notas SQL para profesionales 106
Machine Translated by Google
Capítulo 40: Encontrar duplicados en un
Subconjunto de columnas con detalle
Sección 40.1: Estudiantes con el mismo nombre y fecha de nacimiento
CON CTE (StudentId, Fname, LName, DOB, RowCnt) como (
SELECCIONE StudentId, FirstName, LastName, DateOfBirth como DOB, SUM(1) OVER (Partición por FirstName,
Apellido, Fecha de nacimiento) como RowCnt
DESDE tblEstudiante )
SELECCIONE * de CTE donde RowCnt > 1
ORDEN POR DOB, LNombre
Este ejemplo usa una expresión de tabla común y una función de ventana para mostrar todas las filas duplicadas (en un subconjunto de
columnas) una al lado de la otra.
GoalKicker.com Notas SQL para profesionales 107
Machine Translated by Google
Capítulo 41: Funciones de cadena
Las funciones de cadena realizan operaciones en valores de cadena y devuelven valores numéricos o de cadena.
Con las funciones de cadena, puede, por ejemplo, combinar datos, extraer una subcadena, comparar cadenas o convertir una cadena a todos los caracteres en
mayúsculas o minúsculas.
Sección 41.1: Concatenar
En SQL (ANSI/ISO estándar), el operador para la concatenación de cadenas es ||. Esta sintaxis es compatible con todas las principales bases de
datos excepto SQL Server:
SELECCIONE 'Hola' || 'Mundo' || '!'; devuelve ¡HolaMundo!
Muchas bases de datos admiten una función CONCAT para unir cadenas:
SELECT CONCAT('Hola', 'Mundo'); devuelve 'HolaMundo'
Algunas bases de datos admiten el uso de CONCAT para unir más de dos cadenas (Oracle no):
SELECT CONCAT('Hola', 'Mundo', '!'); devuelve '¡HolaMundo!'
En algunas bases de datos, los tipos que no son cadenas se deben convertir o convertir:
SELECCIONE CONCAT('Foo', CAST(42 AS VARCHAR(5)), 'Bar'); devuelve 'Foo42Bar'
Algunas bases de datos (p. ej., Oracle) realizan conversiones implícitas sin pérdidas. Por ejemplo, un CONCAT en un CLOB y NCLOB produce un NCLOB.
Un CONCAT en un número y un varchar2 da como resultado un varchar2, etc.:
SELECCIONE CONCAT(CONCAT('Foo', 42), 'Bar') FROM dual; devuelve Foo42Bar
Algunas bases de datos pueden usar el operador + no estándar (pero en la mayoría, + funciona solo para números):
SELECCIONE 'Foo' + CAST(42 AS VARCHAR(5)) + 'Bar';
En SQL Server < 2012, donde CONCAT no es compatible, + es la única forma de unir cadenas.
Sección 41.2: Longitud
servidor SQL
El LEN no cuenta el espacio final.
SELECT LEN('Hola') devuelve 5
SELECCIONE LEN('Hola '); devuelve 5
El DATALENGTH cuenta el espacio final.
SELECCIONE DATALENGTH('Hola') devuelve 5
SELECCIONE DATALENGTH('Hola '); devuelve 6
GoalKicker.com Notas SQL para profesionales 108
Machine Translated by Google
Sin embargo, debe tenerse en cuenta que DATALENGTH devuelve la longitud de la representación de bytes subyacente de la cadena, que depende, entre
otras cosas, del conjunto de caracteres utilizado para almacenar la cadena.
DECLARE @str varchar(100) = 'Hola' varchar suele ser una cadena ASCII, que ocupa 1 byte por carácter
SELECCIONE DATALENGTH(@str) devuelve 6
DECLARE @nstr nvarchar(100) = 'Hola' nvarchar es una cadena unicode, que ocupa 2 bytes por carácter
SELECCIONE DATALENGTH(@nstr) devuelve 12
Oráculo
Sintaxis: Longitud ( char )
Ejemplos:
SELECCIONE Longitud('Biblia') DESDE dual; Devuelve 5
SELECCIONE Longitud('justicia') FROM dual; Devoluciones 13
SELECCIONE Longitud (NULO) DESDE dual; Devuelve NULL
Ver también: LongitudB, LongitudC, Longitud2, Longitud4
Sección 41.3: Recorte de espacios vacíos
Trim se utiliza para eliminar el espacio de escritura al principio o al final de la selección
En MSSQL no hay un único TRIM()
SELECCIONE LTRIM(' Hola ') devuelve 'Hola'
SELECCIONE RTRIM(' Hola ') devoluciones ' Hola'
SELECCIONE LTRIM(RTRIM(' Hola ')) devuelve 'Hola'
MySql y Oracle
SELECCIONE TRIM(' Hola ') devuelve 'Hola'
Sección 41.4: Mayúsculas y minúsculas
SELECCIONE SUPERIOR('HolaMundo') devuelve 'HOLAMUNDO'
SELECCIONE LOWER('HelloWorld') devuelve 'helloworld'
Sección 41.5: División
Divide una expresión de cadena usando un separador de caracteres. Tenga en cuenta que STRING_SPLIT() es una función con valores de tabla.
SELECT value FROM STRING_SPLIT('Es importante saber que el dolor en sí.', ' ');
Resultado:
valor
Bienvenido
que sea
muy
doloroso
GoalKicker.com Notas SQL para profesionales 109
Machine Translated by Google
mucho
Sección 41.6: Reemplazar
Sintaxis:
REEMPLAZAR ( Cadena a buscar , Cadena para buscar y reemplazar , Cadena para colocar en la cadena original )
Ejemplo:
SELECCIONE REEMPLAZAR ( 'Peter Steve Tom', 'Steve', 'Billy' ) Valores devueltos: Peter Billy Tom
Sección 41.7: REGEXP
MySQL Versión 3.19
Comprueba si una cadena coincide con una expresión regular (definida por otra cadena).
SELECCIONE 'bedded' REGEXP '[af]' devuelve True
SELECCIONE 'beam' REGEXP '[af]' devuelve Falso
Sección 41.8: Subcadena
La sintaxis es: SUBCADENA ( expresión_cadena, inicio, longitud ). Tenga en cuenta que las cadenas SQL están indexadas en 1.
SELECCIONE SUBCADENA('Hola', 1, 2) devuelve 'Él'
SELECCIONE SUBCADENA('Hola', 3, 3) devuelve 'hola'
Esto se usa a menudo junto con la función LEN() para obtener los últimos n caracteres de una cadena de longitud desconocida.
DECLARAR @str1 VARCHAR(10) = 'Hola', @str2 VARCHAR(10) = 'FooBarBaz';
SELECCIONE SUBCADENA(@str1, LEN(@str1) 2, 3) devuelve 'llo'
SELECCIONE SUBCADENA(@str2, LEN(@str2) 2, 3) devuelve 'Baz'
Sección 41.9: Stu
Rellene una cadena en otra, reemplazando 0 o más caracteres en una posición determinada.
Nota: la posición de inicio está indexada en 1 (usted comienza a indexar en 1, no en 0).
Sintaxis:
COSAS ( expresión_caracteres , comenzar , longitud , reemplazar con_expresión )
Ejemplo:
SELECCIONE COSAS('FooBarBaz', 4, 3, 'Hola') devuelve 'FooHelloBaz'
Sección 41.10: IZQUIERDA DERECHA
La sintaxis es:
IZQUIERDA ( expresióncadena , entero )
GoalKicker.com Notas SQL para profesionales 110
Machine Translated by Google
DERECHA (expresiónserie, entero)
SELECCIONE IZQUIERDA('Hola',2) return Él
SELECCIONE DERECHA('Hola',2) return lo
Oracle SQL no tiene funciones IZQUIERDA y DERECHA. Se pueden emular con SUBSTR y LENGTH.
SUBSTR (expresiónserie, 1, entero)
SUBSTR ( expresióncadena, longitud(expresióncadena)entero+1, entero)
SELECT SUBSTR('Hola',1,2) return He
SELECCIONE SUBSTR('Hola', LENGTH('Hola')2+1,2) return lo
Sección 41.11: MARCHA ATRÁS
La sintaxis es: REVERSE (expresióncadena)
SELECCIONE REVERSE('Hola') devuelve olleH
Sección 41.12: RÉPLICA
La función REPLICATE concatena una cadena consigo misma un número específico de veces.
La sintaxis es: REPLICATE ( expresióncadena , entero )
SELECCIONE REPLICAR ('Hola', 4) devuelve 'HolaHolaHolaHola'
Sección 41.13: Reemplazar función en sql Seleccionar y actualizar consulta
La función Reemplazar en SQL se usa para actualizar el contenido de una cadena. La llamada de función es REPLACE( ) para MySQL,
Oracle y SQL Server.
La sintaxis de la función Reemplazar es:
REEMPLAZAR (str, buscar, reemplazar)
El siguiente ejemplo reemplaza las apariciones de Sur con Sur en la tabla Empleados:
Nombre Dirección
Jaime Sur de Nueva York
John boston del sur
Michael Sur de San Diego
Seleccione Declaración:
Si aplicamos la siguiente función Reemplazar:
SELECCIONAR
Nombre de pila,
REEMPLAZAR (Dirección, 'Sur', 'Sur') Dirección
DE Empleados
ORDENAR POR Nombre
Resultado:
GoalKicker.com Notas SQL para profesionales 111
Machine Translated by Google
Nombre de pila DIRECCIÓN
Jaime Sur de Nueva York
John sur de boston
Michael Sur de San Diego
Declaración de actualización:
Podemos usar una función de reemplazo para realizar cambios permanentes en nuestra tabla a través del siguiente enfoque.
Actualizar empleados
Establecer ciudad = (Dirección, 'Sur', 'Sur');
Un enfoque más común es usar esto junto con una cláusula WHERE como esta:
Actualizar empleados
Establecer dirección = (Dirección, 'Sur', 'Sur')
Donde Dirección LIKE 'Sur%';
Sección 41.14: INSTR.
Devuelve el índice de la primera aparición de una subcadena (cero si no se encuentra)
Sintaxis: INSTR (cadena, subcadena)
SELECCIONE INSTR('FooBarBar', 'Bar') devuelva 4
SELECCIONE INSTR('FooBarBar', 'Xar') devuelva 0
Sección 41.15: NOMBRE PARSE
BASE DE DATOS : Servidor SQL
La función PARSENAME devuelve la parte específica de la cadena dada (nombre del objeto). el nombre del objeto puede contener una cadena como el nombre del
objeto, el nombre del propietario, el nombre de la base de datos y el nombre del servidor.
Más detalles MSDN:PARSENAME
Sintaxis
PARSENAME('NombreDeCadenaParaAnalizar',ÍndiceParte)
Ejemplo
Para obtener el nombre del objeto, use el índice de parte 1
SELECT PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',1) // devuelve ̀ObjectName` SELECT
PARSENAME('[10121111].SchoolDatabase.school.Student',1) // devuelve ̀Student`
Para obtener el nombre del esquema, use el índice de parte 2
SELECT PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',2) // devuelve ̀SchemaName` SELECT
PARSENAME('[10121111].SchoolDatabase.school.Student',2) // devuelve ̀escuela`
Para obtener el nombre de la base de datos, use el índice de parte 3
GoalKicker.com Notas SQL para profesionales 112
Machine Translated by Google
SELECT PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',3) // devuelve ̀DatabaseName` SELECT
PARSENAME('[10121111].SchoolDatabase.school.Student',3) // devuelve ̀SchoolDatabase`
Para obtener el nombre del servidor, use el índice de parte 4
SELECT PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',4) // devuelve ̀ServerName` SELECT
PARSENAME('[10121111].SchoolDatabase.school.Student',4) // devuelve ̀[10121111]`
PARSENAME devolverá nulo si la parte especificada no está presente en la cadena de nombre de objeto dada
GoalKicker.com Notas SQL para profesionales 113
Machine Translated by Google
Capítulo 42: Funciones (agregado)
Sección 42.1: Agregación condicional
Tabla de pagos
Importe del tipo de pago del cliente
Pedro Crédito 100
Pedro Crédito 300
John Crédito 1000
John Débito 500
seleccionar cliente, suma
(caso cuando tipo_pago = 'crédito' luego cantidad final 0 ) como crédito, suma (caso cuando tipo_pago = 'débito' luego
cantidad final 0 ) como débito del grupo de pagos por cliente
Resultado:
Débito de crédito del cliente
Pedro 400 0
John 1000 500
seleccione cliente, suma
(caso cuando tipo_pago = 'crédito' luego 1 más 0 final) como cuenta_transacción_crédito, suma(caso cuando tipo_pago = 'débito' luego 1
más 0 final) como cuenta_transacción_débito del grupo de pagos por cliente
Resultado:
Cliente credit_transaction_count debit_transaction_count
Pedro 2 0
John 1 1
Sección 42.2: Concatenación de listas
Crédito parcial a esto Pues contesta.
La concatenación de listas agrega una columna o expresión al combinar los valores en una sola cadena para cada grupo. Se puede especificar
una cadena para delimitar cada valor (ya sea en blanco o una coma cuando se omite) y el orden de los valores en el resultado. Si bien no es
parte del estándar SQL, todos los principales proveedores de bases de datos relacionales lo admiten en su propio
forma.
mysql
SELECCIONE la columna A
, GROUP_CONCAT(ColumnB ORDER BY ColumnB SEPARATOR ',') AS ColumnBs
DESDE NombreTabla
AGRUPAR POR ColumnA
ORDEN POR ColumnA;
Oracle y DB2
SELECCIONE la columna A
, LISTAGG(ColumnaB, ',') DENTRO DEL GRUPO (ORDENAR POR ColumnaB) COMO ColumnaB
DESDE NombreTabla
GoalKicker.com Notas SQL para profesionales 114
Machine Translated by Google
AGRUPAR POR ColumnA
ORDEN POR ColumnA;
postgresql
SELECCIONE la columna A
, STRING_AGG(ColumnaB, ',' ORDENAR POR ColumnaB) COMO ColumnasB
DESDE NombreTabla
AGRUPAR POR ColumnA
ORDEN POR ColumnA;
servidor SQL
SQL Server 2016 y anteriores
(CTE incluido para fomentar el principio DRY)
CON CTE_TableName AS
( SELECCIONE ColumnaA, ColumnaB
DE TableName)
SELECCIONE t0.ColumnaA
,
COSAS(( SELECCIONAR ',' +
t1.ColumnaB DESDE
CTE_TableName t1 DONDE t1.ColumnaA =
t0.ColumnaA ORDENAR POR t1.ColumnaB
FOR XML PATH('')), 1, 1, '') AS ColumnBs
DESDE CTE_NombreTabla t0
GRUPO POR t0.ColumnaA
ORDEN POR ColumnaA;
SQL Server 2017 y SQL Azure
SELECCIONE la columna A
, STRING_AGG(ColumnaB, ',') DENTRO DEL GRUPO (ORDENAR POR ColumnaB) COMO ColumnaB
DESDE NombreTabla
AGRUPAR POR ColumnA
ORDEN POR ColumnA;
SQLite
sin ordenar:
SELECCIONE la columna A
, GROUP_CONCAT(ColumnaB, ',') COMO ColumnasB
DESDE NombreTabla
AGRUPAR POR ColumnA
ORDEN POR ColumnA;
ordenar requiere una subconsulta o CTE:
CON CTE_TableName COMO (
SELECCIONE ColumnaA, ColumnaB
DESDE NombreTabla
ORDEN POR ColumnaA, ColumnaB)
SELECCIONE la columna A
, GROUP_CONCAT(ColumnaB, ',') COMO ColumnasB
DESDE CTE_NombreTabla
AGRUPAR POR ColumnA
ORDEN POR ColumnA;
GoalKicker.com Notas SQL para profesionales 115
Machine Translated by Google
Sección 42.3: SUMA
La función de suma suma el valor de todas las filas del grupo. Si se omite la cláusula group by, suma todas las filas.
seleccione la suma (salario) TotalSalary
de los empleados;
Salario total 2500
seleccione DepartmentId, sum(salary) TotalSalary del
grupo de
empleados por DepartmentId;
DepartmentId TotalSalary 1 2000
2 500
Sección 42.4: PROMEDIO()
La función agregada AVG() devuelve el promedio de una expresión dada, generalmente valores numéricos en una columna.
Supongamos que tenemos una tabla que contiene el cálculo anual de la población en ciudades de todo el mundo. Los registros de la ciudad de Nueva York
son similares a los siguientes:
TABLA DE EJEMPLO
city_name población año Ciudad de
Nueva York 8,550,405 2015 Ciudad de
Nueva York ... ...
Ciudad de Nueva York 8,000,906 2005
Para seleccionar la población promedio de la ciudad de Nueva York, EE. UU. de una tabla que contiene nombres de ciudades, medidas de
población y años de medidas de los últimos diez años:
CONSULTA
select city_name, AVG(población) avg_population from
city_population where
city_name = 'NEW YORK CITY';
Observe cómo el año de medición no aparece en la consulta, ya que la población se promedia a lo largo del tiempo.
RESULTADOS
ciudad_nombre población_promedio
Ciudad de Nueva York 8,250,754
Nota: La función AVG() convertirá valores a tipos numéricos. Es especialmente importante tener esto en cuenta cuando se trabaja con fechas.
Sección 42.5: Recuento
Puedes contar el número de filas:
SELECCIONE recuento(*) TotalRows
DE empleados;
filas totales
GoalKicker.com Notas SQL para profesionales 116
Machine Translated by Google
O contar los empleados por departamento:
SELECCIONE DepartmentId, cuenta (*) NumEmployees
DE empleados
GROUP BY DepartmentId;
DepartmentId NumEmpleados 1 3
2 1
Puede contar sobre una columna/expresión con el efecto de que no contará los valores NULL :
SELECCIONE recuento (GestorId) administrador
DE EMPLEADOS;
director
(Hay una columna managerID de valor nulo)
También puede usar DISTINCT dentro de otra función como COUNT para encontrar solo los miembros DISTINCT del conjunto para realizar la operación.
Por ejemplo:
SELECT COUNT(ContinentCode) AllCount
, COUNT(DISTINCT ContinentCode) SingleCount
DE Países;
Devolverá valores diferentes. SingleCount solo contará continentes individuales una vez, mientras que AllCount incluirá duplicados .
ContinentCode
jefe
UE
COMO
ESO
ESO
DE
DE
Recuento total: 7 Recuento único: 5
Sección 42.6: Mín.
Encuentre el valor más pequeño de la columna:
seleccione min (edad) del empleado;
El ejemplo anterior devolverá el valor más pequeño para la edad de la columna de la tabla de empleados .
Sintaxis:
GoalKicker.com Notas SQL para profesionales 117
Machine Translated by Google
SELECCIONE MIN (nombre_columna) DESDE nombre_tabla;
Sección 42.7: Máx.
Encuentre el valor máximo de la columna:
seleccione max (edad) del empleado;
El ejemplo anterior devolverá el valor más grande para la edad de la columna de la tabla de empleados .
Sintaxis:
SELECCIONE MAX(nombre_columna) DESDE nombre_tabla;
GoalKicker.com Notas SQL para profesionales 118
Machine Translated by Google
Capítulo 43: Funciones (escalares/fila única)
SQL proporciona varias funciones escalares integradas. Cada función escalar toma un valor como entrada y devuelve un valor como salida para cada fila
en un conjunto de resultados.
Utiliza funciones escalares siempre que se permita una expresión dentro de una instrucción TSQL.
Sección 43.1: Fecha y Hora
En SQL, utiliza tipos de datos de fecha y hora para almacenar información de calendario. Estos tipos de datos incluyen la hora, fecha, fecha y hora
pequeña, fecha y hora, fecha y hora2 y fecha y hora compensada. Cada tipo de datos tiene un formato específico.
tiempo de tipo Formato
de datos hh:mm:ss[.nnnnnnn]
fecha AAAAMMDD
smalldatetime AAAAMMDD hh:mm:ss
fecha y hora AAAAMMDD hh:mm:ss[.nnn]
fechahora2 AAAAMMDD hh:mm:ss[.nnnnnnn]
desplazamiento de fecha y hora AAAAMMDD hh:mm:ss[.nnnnnnn] [+/]hh:mm
La función DATENAME devuelve el nombre o valor de una parte específica de la fecha.
SELECCIONE NOMBRE DE FECHA (día de la semana, '20170114') como nombre de fecha
Nombre del archivo
Sábado
Utilice la función GETDATE para determinar la fecha y la hora actuales de la computadora que ejecuta la instancia de SQL actual. Esta función no
incluye la diferencia de zona horaria.
SELECCIONE GETDATE() como Systemdate
Systemdate
20170114 11:11:47.7230728
La función DATEDIFF devuelve la diferencia entre dos fechas.
En la sintaxis, datepart es el parámetro que especifica qué parte de la fecha desea usar para calcular la diferencia. La parte de fecha puede
ser año, mes, semana, día, hora, minuto, segundo o milisegundo. A continuación, especifique la fecha de inicio en el parámetro de fecha de inicio y la fecha
de finalización en el parámetro de fecha de finalización para el que desea encontrar la diferencia.
SELECCIONE SalesOrderID, DATEDIFF(día, OrderDate, ShipDate)
AS 'Tiempo de procesamiento'
FROM Ventas.SalesOrderHeader
SalesOrderID Tiempo de procesamiento
43659 7
43660 7
43661 7
43662 7
La función DATEADD le permite agregar un intervalo a parte de una fecha específica.
GoalKicker.com Notas SQL para profesionales 119
Machine Translated by Google
SELECCIONE FECHA AÑADIR (día, 20, '20170114') COMO Agregado 20 Más Días
Agregado20MásDías
20170203 00:00:00.000
Sección 43.2: Modificaciones de carácter
Las funciones de modificación de caracteres incluyen la conversión de caracteres a mayúsculas o minúsculas, la conversión de números a
números formateados, la manipulación de caracteres, etc.
La función lower(char) convierte el parámetro de carácter dado en caracteres en minúsculas.
SELECCIONE customer_id, lower(customer_last_name) DESDE el cliente;
devolvería el apellido del cliente cambiado de "SMITH" a "smith".
Sección 43.3: Función de configuración y conversión
Un ejemplo de una función de configuración en SQL es la función @@SERVERNAME . Esta función proporciona el nombre del servidor local que
ejecuta SQL.
SELECCIONE @@ NOMBRE DEL SERVIDOR COMO 'Servidor'
Servidor
SQL064
En SQL, la mayoría de las conversiones de datos ocurren implícitamente, sin intervención del usuario.
Para realizar cualquier conversión que no se pueda completar implícitamente, puede usar las funciones CAST o CONVERT .
La sintaxis de la función CAST es más simple que la sintaxis de la función CONVERT , pero está limitada en lo que puede hacer.
Aquí, usamos las funciones CAST y CONVERT para convertir el tipo de datos de fecha y hora al tipo de datos varchar .
La función CAST siempre usa la configuración de estilo predeterminada. Por ejemplo, representará fechas y horas utilizando el formato AAAAMM
DD.
La función CONVERTIR utiliza el estilo de fecha y hora que especifique. En este caso, 3 especifica el formato de fecha dd/mm/aa.
UTILIZAR AdventureWorks2012
IR
' ' '
SELECCIONA Nombre + + Apellido + fue contratado en ' +
CAST (HireDate COMO varchar (20)) COMO 'Reparto',
' ' '
Nombre + + Apellido + fue contratado en ' +
CONVERTIR (varchar, HireDate, 3) COMO 'Convertir'
FROM Person.Person AS p JOIN
HumanResources.Employee AS e ON
p.BusinessEntityID = e.BusinessEntityID GO
Elenco Convertir
David Hamiltion fue contratado el 20030204 David Hamiltion fue contratado el 04/02/03
Otro ejemplo de una función de conversión es la función PARSE . Esta función convierte una cadena en un tipo de datos especificado.
En la sintaxis de la función, especifica la cadena que se debe convertir, la palabra clave AS y luego la requerida
GoalKicker.com Notas SQL para profesionales 120
Machine Translated by Google
tipo de datos. Opcionalmente, también puede especificar la referencia cultural en la que se debe formatear el valor de la cadena. Si no
especifica esto, se utiliza el idioma de la sesión.
Si el valor de la cadena no se puede convertir a un formato numérico, de fecha o de hora, se producirá un error. Luego deberá usar CAST o
CONVERT para la conversión.
SELECCIONE PARSE('Lunes, 13 de agosto de 2012' COMO datetime2 USING 'enUS') COMO 'Fecha en inglés'
Fecha en inglés
20120813 00:00:00.0000000
Sección 43.4: Función Lógica y Matemática
SQL tiene dos funciones lógicas : ELEGIR e IIF.
La función ELEGIR devuelve un elemento de una lista de valores, según su posición en la lista. Esta posición está especificada por el índice.
En la sintaxis, el parámetro de índice especifica el elemento y es un número entero o entero. El parámetro val_1 … val_n identifica la
lista de valores.
SELECCIONE ELEGIR (2, 'Recursos humanos', 'Ventas', 'Administrador', 'Marketing' ) COMO resultado;
Resultado
Ventas
En este ejemplo, utiliza la función ELEGIR para devolver la segunda entrada en una lista de departamentos.
La función IIF devuelve uno de dos valores, según una condición particular. Si la condición es verdadera, devolverá el valor verdadero. De lo
contrario, devolverá un valor falso.
En la sintaxis, el parámetro boolean_expression especifica la expresión booleana. El parámetro true_value especifica el valor que debe
devolverse si boolean_expression se evalúa como verdadero y el parámetro false_value especifica el valor que se debe devolver si
boolean_expression se evalúa como falso.
SELECCIONE BusinessEntityID, SalesYTD,
IIF(SalesYTD > 200000, 'Bonus', 'No Bonus') AS 'Bonus?'
DE Ventas.Vendedor
IR
BusinessEntityID SalesYTD ¿Bonificación?
274 559697.5639 Bono
275 3763178.1787 Bono
285 172524.4512 Sin bonificación
En este ejemplo, utiliza la función IIF para devolver uno de dos valores. Si las ventas del año hasta la fecha de un vendedor están por encima de
200,000, esta persona será elegible para una bonificación. Los valores por debajo de 200,000 significan que los empleados no califican para
bonos
SQL incluye varias funciones matemáticas que puede usar para realizar cálculos en valores de entrada y
devolver resultados numéricos.
Un ejemplo es la función SIGNO , que devuelve un valor que indica el signo de una expresión. El valor de 1 indica una expresión
negativa, el valor de +1 indica una expresión positiva y 0 indica cero.
GoalKicker.com Notas SQL para profesionales 121
Machine Translated by Google
SELECCIONE SIGNO(20) COMO 'Firmar'
Signo
1
En el ejemplo, la entrada es un número negativo, por lo que el panel Resultados muestra el resultado 1.
Otra función matemática es la función POTENCIA . Esta función proporciona el valor de una expresión elevada a una potencia específica.
En la sintaxis, el parámetro float_expression especifica la expresión y el parámetro y especifica la potencia a la que desea elevar la expresión.
SELECCIONE POTENCIA (50, 3) COMO resultado
Resultado
125000
GoalKicker.com Notas SQL para profesionales 122
Machine Translated by Google
Capítulo 44: Funciones (analíticas)
Las funciones analíticas se utilizan para determinar valores en función de grupos de valores. Por ejemplo, puede usar este tipo de función para
determinar totales acumulados, porcentajes o el resultado principal dentro de un grupo.
Sección 44.1: LAG y LEAD
La función LAG proporciona datos sobre las filas antes de la fila actual en el mismo conjunto de resultados. Por ejemplo, en una declaración
SELECT , puede comparar valores en la fila actual con valores en una fila anterior.
Utiliza una expresión escalar para especificar los valores que deben compararse. El parámetro de desplazamiento es el número de filas antes de
la fila actual que se utilizará en la comparación. Si no especifica el número de filas, se utiliza el valor predeterminado de una fila.
El parámetro predeterminado especifica el valor que debe devolverse cuando la expresión en el desplazamiento tiene un valor NULL . Si no
especifica un valor, se devuelve un valor NULL .
La función LEAD proporciona datos sobre las filas posteriores a la fila actual en el conjunto de filas. Por ejemplo, en una instrucción SELECT ,
puede comparar los valores de la fila actual con los valores de la fila siguiente.
Especifique los valores que deben compararse utilizando una expresión escalar. El parámetro de compensación es el número de filas después
de la fila actual que se utilizará en la comparación.
Especifique el valor que debe devolverse cuando la expresión en el desplazamiento tiene un valor NULL utilizando el parámetro
predeterminado. Si no especifica estos parámetros, se usa el valor predeterminado de una fila y se devuelve un valor NULL .
SELECCIONE BusinessEntityID, SalesYTD,
LEAD(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS " Valor de Lead",
LAG(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS " Valor de retraso"
DE Vendedor;
Este ejemplo usa las funciones LEAD y LAG para comparar los valores de ventas de cada empleado hasta la fecha con los de los empleados
enumerados arriba y abajo, con registros ordenados según la columna BusinessEntityID.
ID de entidad empresarial VentasYTD Valor de prospecto Valor de
274 559697.5639 3763178.1787 retraso 0.0000
275 3763178.1787 4251368.5497 559697.5639
276 4251368.5497 3189418.3662 3763178.1787
277 3189418.3662 1453719.4653 4251368.5497
278 1453719.4653 2315185.6110 3189418.3662
279 2315185.6110 1352577.1325 1453719.4653
Sección 44.2: PERCENTILE_DISC y PERCENTILE_CONT
La función PERCENTILE_DISC enumera el valor de la primera entrada donde la distribución acumulada es más alta que el percentil que
proporciona con el parámetro numeric_literal .
Los valores se agrupan por conjunto de filas o partición, según lo especificado por la cláusula WITHIN GROUP .
La función PERCENTILE_CONT es similar a la función PERCENTILE_DISC , pero devuelve el promedio de la suma de la primera entrada
coincidente y la entrada siguiente.
GoalKicker.com Notas SQL para profesionales 123
Machine Translated by Google
SELECT BusinessEntityID, JobTitle, SickLeaveHours, CUME_DIST()
OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
COMO "Distribución acumulativa",
PERCENTILE_DISC(0.5) DENTRO DEL GRUPO(ORDEN POR Horas de baja por enfermedad)
OVER(PARTITION BY JobTitle) COMO "Percentil Discreto"
DE Empleado;
Para encontrar el valor exacto de la fila que coincide o excede el percentil 0.5, pasa el percentil como el literal numérico en la función
PERCENTILE_DISC . La columna Percentil discreto en un conjunto de resultados enumera el valor del
fila en la que la distribución acumulada es mayor que el percentil especificado.
ID de entidad empresarial Título de Percentil de distribución acumulada de horas de baja por enfermedad discreto
trabajo 272 Especialista en aplicaciones 55 0.25 56
268 Especialista en aplicaciones 56 0.75 56
269 Especialista en aplicaciones 56 0.75 56
267 Especialista en aplicaciones 57 1 56
Para basar el cálculo en un conjunto de valores, utilice la función PERCENTILE_CONT . La columna "Percentil continuo" en los resultados enumera
el valor promedio de la suma del valor del resultado y el siguiente valor coincidente más alto.
SELECT BusinessEntityID, JobTitle, SickLeaveHours, CUME_DIST()
OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
COMO "Distribución acumulativa",
PERCENTILE_DISC(0.5) DENTRO DEL GRUPO(ORDEN POR Horas de baja por enfermedad)
SOBRE(PARTICIÓN POR Título del trabajo) COMO "Percentil discreto",
PERCENTILE_CONT(0.5) DENTRO DEL GRUPO (ORDEN POR Horas de baja por enfermedad)
OVER(PARTITION BY JobTitle) COMO "Percentil Continuo"
DE Empleado;
Acumulativo percentil percentil
ID de entidad empresarial Título profesional EnfermoBajaHoras
Distribución Discreto Continuo
272 Especialista en aplicaciones 55 0.25 56 56
268 Especialista en aplicaciones 56 0.75 56 56
269 Especialista en aplicaciones 56 0.75 56 56
267 Especialista en aplicaciones 57 1 56 56
Sección 44.3: FIRST_VALUE
Utiliza la función FIRST_VALUE para determinar el primer valor en un conjunto de resultados ordenado, que identifica mediante una expresión escalar.
SELECCIONE StateProvinceID, Nombre, TaxRate,
PRIMER_VALOR(EstadoProvinciaID)
SOBRE (ORDEN POR TaxRate ASC) COMO FirstValue
FROM TasaImpuestoVentas;
En este ejemplo, la función FIRST_VALUE se usa para devolver el ID del estado o provincia con la tasa impositiva más baja.
La cláusula OVER se utiliza para ordenar las tasas impositivas para obtener la tasa más baja.
EstadoProvinciaID Nombre TasaImpositivaPrimerValor
74 Impuesto sobre las ventas del estado de Utah 5.00 74
36 Impuesto sobre las ventas del estado de Minnesota 6.75 74
30 Impuesto sobre las ventas del estado de Massachusetts 7.00 74
GoalKicker.com Notas SQL para profesionales 124
Machine Translated by Google
1 GST canadiense 7.00 74
57 GST canadiense 7.00 74
63 GST canadiense 7.00 74
Sección 44.4: ÚLTIMO_VALOR
La función LAST_VALUE proporciona el último valor en un conjunto de resultados ordenado, que se especifica mediante una expresión
escalar.
SELECCIONE TerritoryID, StartDate, BusinessentityID,
LAST_VALUE(ID de entidad comercial)
SOBRE (ORDEN POR ID de territorio) COMO LastValue
DESDE SalesTerritoryHistory;
Este ejemplo usa la función LAST_VALUE para devolver el último valor de cada conjunto de filas en los valores ordenados.
Id. de territorio 1 Fecha de inicio BusinessEntityID Último valor 283
20050701 00.00.00.000 280
1 20061101 00.00.00.000 284 283
1 20050701 00.00.00.000 283 283
2 20070101 00.00.00.000 277 275
2 20050701 00.00.00.000 275 275
3 20070101 00.00.00.000 275 277
Sección 44.5: PERCENT_RANK y CUME_DIST
La función PERCENT_RANK calcula la clasificación de una fila en relación con el conjunto de filas. El porcentaje se basa en el número de filas del
grupo que tienen un valor inferior al de la fila actual.
El primer valor en el conjunto de resultados siempre tiene un rango porcentual de cero. El valor para el valor clasificado más alto, o el último, en el
conjunto es siempre uno.
La función CUME_DIST calcula la posición relativa de un valor específico en un grupo de valores, determinando el porcentaje de valores menores o
iguales a ese valor. A esto se le llama distribución acumulada.
SELECCIONE BusinessEntityID, JobTitle, SickLeaveHours,
PERCENT_RANK() SOBRE(PARTICIÓN POR Título de trabajo ORDEN POR Horas de licencia por enfermedad DESC)
AS " Rango porcentual",
CUME_DIST() SOBRE(PARTICIÓN POR Título de trabajo ORDEN POR Horas de licencia por enfermedad DESC)
AS "Distribución acumulativa"
DE Empleado;
En este ejemplo, utiliza una cláusula ORDER para particionar, o agrupar, las filas recuperadas por la declaración SELECT en función de los títulos de
trabajo de los empleados, con los resultados en cada grupo ordenados según la cantidad de horas de licencia por enfermedad que han utilizado
los empleados.
ID de entidad comercial Título del Distribución acumulativa de rango porcentual de horas de ausencia por enfermedad
trabajo 267 Especialista en Aplicaciones 57 0 0.25
268 Especialista en Aplicaciones 56 0,333333333333333 0,75
269 Especialista en Aplicaciones 56 0,333333333333333 0,75
272 Especialista en Aplicaciones 55 1 1
GoalKicker.com Notas SQL para profesionales 125
Machine Translated by Google
Asistente del Jefe Financiero
262 48 0 1
Oficial
239 Especialista en beneficios 45 0 1
252 Comprador 50 0 0.111111111111111
251 Comprador 49 0.125 0.333333333333333
256 Comprador 49 0.125 0.333333333333333
253 Comprador 48 0.375 0.555555555555555
254 Comprador 48 0.375 0.555555555555555
La función PERCENT_RANK clasifica las entradas dentro de cada grupo. Para cada entrada, devuelve el porcentaje de entradas
en el mismo grupo que tienen valores más bajos.
La función CUME_DIST es similar, excepto que devuelve el porcentaje de valores menores o iguales al valor actual.
GoalKicker.com Notas SQL para profesionales 126
Machine Translated by Google
Capítulo 45: Funciones de ventana
Sección 45.1: Configuración de una bandera si otras filas tienen una
propiedad común
Digamos que tengo estos datos:
Elementos de la mesa
etiqueta de nombre de
identificación 1 ejemplo
2 pies
42 bares etiqueta_única simple simple
3 bases Hola
51 quux mundo
Me gustaría obtener todas esas líneas y saber si otras líneas usan una etiqueta
SELECCIONE id, nombre, etiqueta, CONTAR(*) SOBRE (PARTICIÓN POR etiqueta) > 1 COMO bandera DESDE elementos
El resultado será:
id nombre bandera 1 ejemplo
etiqueta etiqueta_única
falso 2 foo simple simple
verdadero
42 bares verdadero
3 bases Hola FALSO
51 quux mundo FALSO
En caso de que su base de datos no tenga OVER y PARTITION, puede usar esto para producir el mismo resultado:
SELECCIONE id, nombre, etiqueta, (SELECCIONE CONTEO (etiqueta) DESDE elementos B DONDE etiqueta = A.etiqueta) > 1 COMO bandera DESDE elementos A
Sección 45.2: Búsqueda de registros "fuera de secuencia" mediante la
función LAG()
Dados estos datos de muestra:
ESTADO DE IDENTIFICACIÓN ESTADO_HORA ESTADO_POR 1
UNO 2016092819.47.52.501398 USUARIO_1 3 UNO
2016092819.47.52.501511 USUARIO_2
1 TRES 2016092819.47.52.501517 USUARIO_3
3 DOS 2016092819.47.52.501521 USUARIO_2
3 TRES 2016092819.47.52.501524 USUARIO_4
Los elementos identificados por valores de ID deben pasar del ESTADO 'UNO' a 'DOS' a 'TRES' en secuencia, sin omitir estados. El problema es encontrar valores de
usuarios (ESTADO_POR) que violen la regla y pasen de 'UNO' inmediatamente a 'TRES'.
La función analítica LAG() ayuda a resolver el problema devolviendo para cada fila el valor de la fila anterior:
GoalKicker.com Notas SQL para profesionales 127
Machine Translated by Google
SELECCIONE * DESDE (
SELECCIONAR
t.*,
LAG(estado) SOBRE (PARTICIÓN POR id ORDEN POR status_time) COMO prev_status DESDE prueba t
) t1 DONDE estado = 'TRES' Y estado_anterior != 'DOS'
En caso de que su base de datos no tenga LAG(), puede usar esto para producir el mismo resultado:
SELECCIONE A.id, A.status, B.status como prev_status, A.status_time, B.status_time como prev_status_time DESDE Datos A, Datos B DONDE A.id = B.id Y
B.status_time = (SELECCIONE
MAX(status_time) DESDE
Datos donde status_time < A.status_time e id = A.id)
Y A.status = 'TRES' Y NO B.status = 'DOS'
Sección 45.3: Obtener un total acumulado
Dados estos datos:
fecha cantidad
20160312 200
20160311 50
20160314 100
20160315 100
20160310 250
SELECCIONE fecha, cantidad, SUMA (cantidad) SOBRE (ORDENAR POR fecha ASC) COMO en ejecución
DESDE operaciones
ORDENAR POR fecha ASC
Te regalaré
fecha importe corriente
20160310 250 250
20160311 50 300
20160312 200 100
20160314 100 0
20160315 100 100
Sección 45.4: Agregar el total de filas seleccionadas a cada fila
SELECCIONE your_columns, COUNT(*) OVER() como Ttl_Rows FROM your_data_set
id nombre Ttl_Rows 1
ejemplo 5
2 pies 5
3 barras 5
4 bases 5
5 quux 5
En lugar de usar dos consultas para obtener un conteo y luego la línea, puede usar un agregado como función de ventana y usar el conjunto
de resultados completo como ventana.
GoalKicker.com Notas SQL para profesionales 128
Machine Translated by Google
Esto se puede usar como base para cálculos posteriores sin la complejidad de las uniones automáticas adicionales.
Sección 45.5: Obtención de las N filas más recientes en agrupaciones
múltiples
Dado este dato
User_ID Completion_Date 1
20160720
1 20160721
2 20160720
2 20160721
2 20160722
;con CTE como
(SELECCIONAR *,
ROW_NUMBER() SOBRE (PARTICIÓN POR User_ID
ORDEN POR Finalización_Fecha DESC) Fila_Num
DE Datos)
SELECCIONE * FORMULARIO CTE DONDE Row_Num <= n
Usando n=1, obtendrá la fila más reciente por ID_usuario:
User_ID Completion_Date Row_Num 1
20160721 1
2 20160722 1
GoalKicker.com Notas SQL para profesionales 129
Machine Translated by Google
Capítulo 46: Expresiones de tabla comunes
Sección 46.1: generación de valores
La mayoría de las bases de datos no tienen una forma nativa de generar una serie de números para uso adhoc; sin embargo, las
expresiones de tabla comunes se pueden usar con recursividad para emular ese tipo de función.
El siguiente ejemplo genera una expresión de tabla común llamada Números con una columna i que tiene una fila para los números 15:
Dé un nombre de tabla "Números" y una columna "i" para guardar los números
CON Números(i) COMO (
Número inicial/índice
SELECCIONA 1
Se requiere operador UNION ALL de nivel superior para la recursividad
UNIÓN TODOS
Expresión de iteración:
SELECCIONA i + 1
Expresión de tabla que primero declaramos usada como fuente para la recursividad
DE Números
Cláusula para definir el final de la recursividad
DONDE yo < 5
)
Usar la expresión de la tabla generada como una tabla regular
SELECCIONE i DE Números;
i
1
2
3
4
5
Este método se puede utilizar con cualquier intervalo numérico, así como con otros tipos de datos.
Sección 46.2: enumeración recursiva de un subárbol
CON RECURSIVO ManagedByJames(Level, ID, FName, LName) AS ( comience con esta fila
SELECCIONE 1, ID, FName, LName
DE Empleados
DONDE ID = 1
UNIÓN TODOS
obtenga empleados que tengan cualquiera de las filas previamente seleccionadas como gerente SELECCIONE
ManagedByJames.Level + 1, Employees.ID,
Employees.FName,
Employees.LName FROM
Employees JOIN
ManagedByJames ON
Employees.ManagerID =
ManagedByJames.ID
ORDENAR POR 1 DESC búsqueda en profundidad
)
SELECCIONE * DESDE ManagedByJames;
GoalKicker.com Notas SQL para profesionales 130
Machine Translated by Google
ID de nivel FName LName
1 1 Santiago Herrero
2 2 Juan Johnson
3 4 Jonathan Smith
2 3 Miguel Williams
Artículo 46.3: Consulta temporal
Estos se comportan de la misma manera que las subconsultas anidadas pero con una sintaxis diferente.
CON ReadyCars COMO (
SELECCIONAR *
DESDE Coches
DONDE Estado = 'LISTO'
)
SELECCIONE ID, modelo, costo total
DESDE ReadyCars
ORDEN POR CostoTotal;
Costo total del modelo de ID
1 Ford F150 200
2 Ford F150 230
Sintaxis de subconsulta equivalente
SELECCIONE ID, modelo, costo total
DESDE (
SELECCIONAR *
DESDE Coches
DONDE Estado = 'LISTO'
) AS ReadyCars
ORDENAR POR CostoTotal
Sección 46.4: subir recursivamente en un árbol
CON ManagersOfJonathon AS RECURSIVO ( comience
con esta fila
SELECCIONAR *
DE Empleados
DONDE ID = 4
UNIÓN TODOS
Obtener gerente(s) de todas las filas previamente seleccionadas
SELECCIONAR Empleados.*
DE Empleados
ÚNETE a GerentesDeJonathon
ON Empleados.ID = ManagersOfJonathon.ManagerID
)
SELECCIONE * DE ManagersOfJonathon;
Id FName LName Número de teléfono ManagerId DepartmentId
4 jonathan smith 1212121212 2 1
2 Juan Johnson 2468101214 1 1
1 Santiago Smith 1234567890 NULO 1
GoalKicker.com Notas SQL para profesionales 131
Machine Translated by Google
Sección 46.5: Generar fechas de forma recursiva, extendida para incluir la lista
de equipos como ejemplo
DECLARAR @DateFrom DATETIME = '20160601 06:00'
DECLARAR @DateTo DATETIME = '20160701 06:00'
DECLARAR @IntervalDays INT = 7
Secuencia de transición = Descanso y relajación del turno de día al turno de noche
RR (Descanso y Relajación) = 1
DS (Turno de día) = 2
NS (Turno Nocturno) = 3
;CON lista COMO (
SELECCIONE @DateFrom AS RosterStart, 1 AS TeamA, 2 AS TeamB, 3 AS TeamC
UNIÓN TODOS
SELECCIONE DATEADD(d, @IntervalDays, RosterStart),
CASO TeamA CUANDO 1 ENTONCES 2 CUANDO 2 ENTONCES 3 CUANDO 3 ENTONCES 1 TERMINA COMO TeamA,
CASO TeamB CUANDO 1 ENTONCES 2 CUANDO 2 ENTONCES 3 CUANDO 3 ENTONCES 1 TERMINA COMO TeamB,
CASO TeamC CUANDO 1 ENTONCES 2 CUANDO 2 ENTONCES 3 CUANDO 3 ENTONCES 1 TERMINA COMO TeamC
DESDE la lista DONDE Comienzo de la lista < DATEADD(d, @IntervalDays, @DateTo)
)
SELECCIONE RosterStart,
ISNULL(LEAD(RosterStart) SOBRE (ORDEN POR RosterStart), RosterStart + @IntervalDays) COMO
RosterEnd,
CASO TeamA CUANDO 1 LUEGO 'RR' CUANDO 2 LUEGO 'DS' CUANDO 3 LUEGO 'NS' TERMINA COMO TeamA,
CASO TeamB CUANDO 1 LUEGO 'RR' CUANDO 2 LUEGO 'DS' CUANDO 3 LUEGO 'NS' TERMINA COMO TeamB,
CASO TeamC CUANDO 1 LUEGO 'RR' CUANDO 2 LUEGO 'DS' CUANDO 3 LUEGO 'NS' TERMINA COMO TeamC
DESDE la lista
Resultado
Es decir, para la Semana 1, el Equipo A está en R&R, el Equipo B está en el Turno de Día y el Equipo C está en el Turno de Noche.
Sección 46.6: Funcionalidad Oracle CONNECT BY con CTE recursivos
La funcionalidad CONNECT BY de Oracle proporciona muchas funciones útiles y no triviales que no están integradas cuando se utilizan CTE recursivos estándar de SQL. Este
ejemplo replica estas características (con algunas adiciones para completar), usando la sintaxis de SQL Server. Es más útil para los desarrolladores de Oracle
que encuentran muchas funciones que faltan en sus consultas jerárquicas en otras bases de datos, pero también sirve para mostrar lo que se puede hacer con una consulta
jerárquica en general.
CON tbl AS (
SELECCIONE id, nombre, parent_id
DESDE mytable) ,
tbl_hierarchy AS (
/* Ancla */
GoalKicker.com Notas SQL para profesionales 132
Machine Translated by Google
SELECCIONE 1 COMO "NIVEL"
, 1 COMO CONECTAR_POR_ISROOT
, 0 COMO CONECTAR_POR_ISBRANCH
, CASO CUANDO t.id IN (SELECCIONE parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF 0 AS
, CONNECT_BY_ISCYCLE '/' +
, CAST(t.id AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id '/' + CAST(t .name AS
, VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name t.id AS root_id
,
, t.*
DESDE tbl t
DONDE t.parent_id ES NULL UNION COMENZAR CON parent_id ES NULO
ALL /*
Recursivo */
SELECCIONE th."NIVEL" + 1 COMO "NIVEL"
, 0 COMO CONECTAR_POR_ISROOT
, CASO CUANDO t.id EN (SELECCIONE parent_id DE tbl) ENTONCES 1 OTRO 0 TERMINAR COMO
CASO
,
CONNECT_BY_ISBRANCH
CUANDO t.id IN (SELECCIONE parent_id DE tbl) ENTONCES 0 DE LO CONTRARIO 1 TERMINA COMO
, CONNECT_BY_ISLEAF CASO CUANDO th.SYS_CONNECT_BY_PATH_id LIKE '%/' + CAST(t.id AS VARCHAR(MAX)) + '/%'
ENTONCES 1 OTRO 0 TERMINA COMO CONNECT_BY_ISCYCLE
, th.SYS_CONNECT_BY_PATH_id + CAST(t.id COMO VARCHAR(MAX)) + '/' COMO
SYS_CONNECT_BY_PATH_id
, th.SYS_CONNECT_BY_PATH_name + CAST(t.name AS VARCHAR(MAX)) + '/' AS
SYS_CONNECT_BY_PATH_name
, th.root_id
, t.*
DESDE tbl t
ÚNASE a tbl_hierarchy th ON (th.id = t.parent_id) CONECTAR POR id ANTERIOR = parent_id
DONDE th.CONNECT_BY_ISCYCLE = 0) NO CICLO
SELECCIONE
th.* , REPLICATE(' ', (th."LEVEL" 1) * 3) + th.name AS tbl_hierarchy FROM tbl_hierarchy th JOIN
tbl CONNECT_BY_ROOT ON
(CONNECT_BY_ROOT.id = th.root_id)
ORDENAR POR th.SYS_CONNECT_BY_PATH_name; ORDENAR HERMANOS POR nombre
Funciones de CONNECT BY demostradas anteriormente, con explicaciones:
Cláusulas
CONECTAR POR: Especifica la relación que define la jerarquía.
COMENZAR CON: especifica los nodos raíz.
ORDENAR HERMANOS POR: Ordena los resultados correctamente.
Parámetros
NOCYCLE: deja de procesar una rama cuando se detecta un bucle. Las jerarquías válidas son gráficos acíclicos dirigidos y las
referencias circulares violan esta construcción.
Operadores
PRIOR: Obtiene datos del padre del nodo.
CONNECT_BY_ROOT: Obtiene datos de la raíz del nodo.
pseudocolumnas
NIVEL: Indica la distancia del nodo a su raíz.
CONNECT_BY_ISLEAF: Indica un nodo sin hijos.
CONNECT_BY_ISCYCLE: Indica un nodo con referencia circular.
Funciones
SYS_CONNECT_BY_PATH: Devuelve una representación plana/concatenada de la ruta al nodo desde su raíz.
GoalKicker.com Notas SQL para profesionales 133
Machine Translated by Google
Capítulo 47: Vistas
Sección 47.1: Vistas simples
Una vista puede filtrar algunas filas de la tabla base o proyectar solo algunas columnas de ella:
CREATE VIEW new_employees_details COMO
SELECCIONA E.id, Fname, Salary, Hire_date FROM
Empleados E WHERE
contrat_date > date '20150101';
Si selecciona desde la vista:
seleccione * de new_employees_details
Id FName Salario Hire_date
4 jonathan 500 24072016
Sección 47.2: Vistas complejas
Una vista puede ser una consulta realmente compleja (agregaciones, uniones, subconsultas, etc.). Solo asegúrese de agregar nombres de columna para
todo lo que seleccione:
Crear VER ingreso_departamento
COMO SELECCIONAR d.Nombre como NombreDepartamento, sum(e.salario) como
SalarioTotal DE
Empleados e ÚNETE Departamentos d en e.IdDepartamento
= d.id GRUPO POR d.Nombre;
Ahora puede seleccionar de él como de cualquier tabla:
SELECCIONAR *
DESDE ingreso_depto;
DepartamentoNombre SalarioTotal
hora 1900
Ventas 600
GoalKicker.com Notas SQL para profesionales 134
Machine Translated by Google
Capítulo 48: Vistas materializadas
Una vista materializada es una vista cuyos resultados se almacenan físicamente y deben actualizarse periódicamente para permanecer
actualizados. Por lo tanto, son útiles para almacenar los resultados de consultas complejas y de larga ejecución cuando no se requieren
resultados en tiempo real. Las vistas materializadas se pueden crear en Oracle y PostgreSQL. Otros sistemas de bases de datos ofrecen una
funcionalidad similar, como las vistas indexadas de SQL Server o las tablas de consultas materializadas de DB2.
Sección 48.1: ejemplo de PostgreSQL
CREAR TABLA mitabla (número INT);
INSERTAR EN VALORES mytable (1);
CREAR VISTA MATERIALIZADA myview COMO SELECCIONAR * DESDE mytable;
SELECCIONE * DESDE mi vista;
número
1 (1 fila)
INSERTAR EN VALORES de mi tabla (2);
SELECCIONE * DESDE mi vista;
número
1 (1 fila)
REFRESCAR LA VISTA MATERIALIZADA myview;
SELECCIONE * DESDE mi vista;
número
1
2
(2 filas)
GoalKicker.com Notas SQL para profesionales 135
Machine Translated by Google
Capítulo 49: Comentarios
Sección 49.1: Comentarios de una sola línea
Los comentarios de una sola línea van precedidos de y van hasta el final de la línea:
SELECCIONAR *
DE Empleados esto es un comentario
WHERE FNombre = 'Juan'
Sección 49.2: Comentarios de varias líneas
Los comentarios de código de varias líneas están envueltos en /* ... */:
/* Esta consulta
devuelve todos los empleados */
SELECCIONAR *
DE Empleados
También es posible insertar un comentario de este tipo en medio de una línea:
SELECCIONAR /* todas las columnas: */ *
DE Empleados
GoalKicker.com Notas SQL para profesionales 136
Machine Translated by Google
Capítulo 50: Claves foráneas
Sección 50.1: Claves foráneas explicadas
Las restricciones de claves foráneas garantizan la integridad de los datos al exigir que los valores de una tabla coincidan con los valores de otra tabla.
Un ejemplo de donde se requiere una clave foránea es: En una universidad, un curso debe pertenecer a un departamento. El código para este escenario es:
CREAR TABLA Departamento (
Código_depto CARÁCTER (5) CLAVE PRIMARIA,
Nombre_departamento VARCHAR (20) ÚNICO
);
Inserte valores con la siguiente declaración:
INSERTAR EN VALORES del Departamento ('CS205', 'Ciencias de la Computación');
La siguiente tabla contendrá la información de las materias que ofrece la rama de Informática:
CREAR TABLA Programación_Cursos (
Código_depto CARÁCTER(5),
Prg_Code CHAR(9) CLAVE PRIMARIA,
Prg_Nombre VARCHAR (50) ÚNICO,
CLAVE EXTRANJERA (Dept_Code) Referencias Departamento (Dept_Code)
);
(El tipo de datos de la clave externa debe coincidir con el tipo de datos de la clave a la que se hace referencia).
La restricción de clave externa en la columna Dept_Code permite valores solo si ya existen en la tabla a la que se hace referencia, Departamento. Esto significa que
si intenta insertar los siguientes valores:
INSERTAR EN los valores de los cursos de programación ('CS300', 'FDBDB001', 'Sistemas de base de datos');
la base de datos generará un error de violación de clave externa, porque CS300 no existe en la tabla Departamento . Pero cuando prueba un valor clave que
existe:
INSERTAR EN VALORES de Programación_Cursos ('CS205', 'FDBDB001', 'Sistemas de base de datos');
INSERTAR EN VALORES de Cursos_Programación ('CS205', 'DB2DB002', 'Sistemas de bases de datos II');
entonces la base de datos permite estos valores.
Algunos consejos para el uso de claves foráneas
Una clave externa debe hacer referencia a una clave ÚNICA (o PRIMARIA) en la tabla principal.
Ingresar un valor NULL en una columna de clave externa no genera un error.
Las restricciones de clave externa pueden hacer referencia a tablas dentro de la misma base de datos.
Las restricciones de clave externa pueden hacer referencia a otra columna en la misma tabla (autorreferencia).
Sección 50.2: Creación de una tabla con una clave externa
En este ejemplo tenemos una tabla existente, SuperHeros.
GoalKicker.com Notas SQL para profesionales 137
Machine Translated by Google
Esta tabla contiene un ID de clave principal.
Agregaremos una nueva tabla para almacenar los poderes de cada superhéroe:
CREAR TABLA HeroPowers (
ID int CLAVE PRIMARIA NO NULA ,
Nombre nvarchar(MAX) NO NULO,
HeroId int REFERENCIAS SuperHeros(ID)
)
La columna HeroId es una clave externa a la tabla SuperHeros.
GoalKicker.com Notas SQL para profesionales 138
Machine Translated by Google
Capítulo 51: Secuencia
Sección 51.1: Crear Secuencia
CREAR SECUENCIA orders_seq
EMPIEZA CON 1000
INCREMENTO EN 1;
Crea una secuencia con un valor inicial de 1000 que se incrementa en 1.
Sección 51.2: Uso de secuencias
se usa una referencia a seq_name.NEXTVAL para obtener el siguiente valor en una secuencia. Una sola instrucción solo puede generar un único valor de secuencia. Si hay
múltiples referencias a NEXTVAL en una declaración, usarán el mismo número generado.
NEXTVAL se puede utilizar para INSERTOS
INSERTAR EN Pedidos (Order_UID, Cliente)
VALORES (orders_seq.NEXTVAL, 1032);
Se puede utilizar para ACTUALIZACIONES
ACTUALIZAR
Pedidos SET Order_UID = orders_seq.NEXTVAL
WHERE Cliente = 581;
También se puede utilizar para SELECCIONES
SELECCIONE Order_seq.NEXTVAL DESDE dual;
GoalKicker.com Notas SQL para profesionales 139
Machine Translated by Google
Capítulo 52: Subconsultas
Sección 52.1: Subconsulta en cláusula FROM
Una subconsulta en una cláusula FROM actúa de manera similar a una tabla temporal que se genera durante la ejecución de una consulta y se pierde después.
SELECCIONE Gerentes.Id, Empleados.Salario DE
( SELECCIONE
Id DE Empleados
DONDE ManagerId ES NULO )
COMO Gerentes
ÚNASE a Empleados EN Gerentes.Id = Empleados.Id
Sección 52.2: Subconsulta en cláusula SELECT
SELECCIONE
Id,
FName, LName, (SELECT COUNT (*) FROM Cars DONDE Cars.CustomerId = Customers.Id) AS NumberOfCars
DE Clientes
Sección 52.3: Subconsulta en cláusula WHERE
Utilice una subconsulta para filtrar el conjunto de resultados. Por ejemplo, esto devolverá a todos los empleados con un salario igual al del empleado mejor
pagado.
SELECCIONAR *
DE Empleados
DONDE Salario = (SELECCIONE MAX(Salario) DE Empleados)
Sección 52.4: Subconsultas correlacionadas
Las subconsultas correlacionadas (también conocidas como sincronizadas o coordinadas) son consultas anidadas que hacen referencia a la fila actual de su
consulta externa:
SELECCIONE Id. de
empleado DESDE Empleado AS
eOuter DONDE Salario >
( SELECCIONE PROMEDIO (Salario)
FROM Empleado eInner DONDE
eInner.DepartmentId = eOuter.DepartmentId
)
La subconsulta SELECT AVG (Salario) ... está correlacionada porque se refiere a la fila de empleados eOuter de su consulta externa.
Sección 52.5: Filtre los resultados de la consulta utilizando la consulta en una tabla
diferente
Esta consulta selecciona a todos los empleados que no están en la tabla Supervisores.
SELECCIONAR *
GoalKicker.com Notas SQL para profesionales 140
Machine Translated by Google
DE Empleados
DONDE EmployeeID no está en (SELECCIONE EmployeeID
DE Supervisores)
Se pueden lograr los mismos resultados usando LEFT JOIN.
SELECCIONAR *
FROM Empleados AS e
LEFT JOIN Supervisores AS s ON s.EmployeeID=e.EmployeeID DONDE
s.EmployeeID es NULL
Sección 52.6: Subconsultas en cláusula FROM
Puede usar subconsultas para definir una tabla temporal y usarla en la cláusula FROM de una consulta "externa".
SELECCIONE * DESDE (SELECCIONE ciudad, temp_hi temp_lo AS temp_var FROM tiempo) AS w
DONDE temp_var > 20;
Lo anterior encuentra ciudades de la tabla meteorológica cuya variación diaria de temperatura es mayor a 20. El resultado es:
ciudad temp_var 21
SAN LOUIS
LOS ANGELES 31
LOS ANGELES 23
LOS ANGELES 31
LOS ANGELES 27
LOS ANGELES 28
LOS ANGELES 28
LOS ANGELES 32
Sección 52.7: Subconsultas en la cláusula WHERE
El siguiente ejemplo encuentra ciudades (del ejemplo de ciudades) cuya población está por debajo de la temperatura promedio (obtenida a través de
una subqquery):
SELECCIONE nombre, pop2000 DE ciudades
DONDE pop2000 < (SELECT avg(pop2000) FROM cities);
Aquí: la subconsulta (SELECT avg(pop2000) FROM cities) se usa para especificar condiciones en la cláusula WHERE. El resultado es:
nombre pop2000
San Francisco 776733
SAN LOUIS 348189
Ciudad de Kansas 146866
GoalKicker.com Notas SQL para profesionales 141
Machine Translated by Google
Capítulo 53: Bloques de ejecución
Sección 53.1: Usando BEGIN ... END
COMENZAR
ACTUALIZAR Empleados SET PhoneNumber = '5551234567' WHERE Id = 1;
ACTUALIZAR Empleados SET Salario = 650 DONDE Id = 3;
FIN
GoalKicker.com Notas SQL para profesionales 142
Machine Translated by Google
Capítulo 54: Procedimientos almacenados
Sección 54.1: Crear y llamar a un procedimiento almacenado
Los procedimientos almacenados se pueden crear a través de una GUI de administración de base de datos (ejemplo de SQL Server), o a través de una declaración
SQL de la siguiente manera:
Definir un nombre y parámetros CREAR
PROCEDIMIENTO Northwind.getEmployee
@LastName nvarchar(50),
@FirstName nvarchar(50)
COMO
Defina la consulta que se ejecutará
SELECCIONE FirstName, LastName, Department
FROM Northwind.vEmployeeDepartment
WHERE FirstName = @FirstName AND LastName = @LastName AND
EndDate IS NULL;
Llamando al procedimiento:
EJECUTAR Northwind.getEmployee N'Ackerman', N'Pilar';
O
EXEC Northwind.getEmployee @LastName = N'Ackerman', @FirstName = N'Pilar'; IR
O
EJECUTAR Northwind.getEmployee @FirstName = N'Pilar', @LastName = N'Ackerman'; IR
GoalKicker.com Notas SQL para profesionales 143
Machine Translated by Google
Capítulo 55: Desencadenantes
Sección 55.1: CREAR DISPARADOR
Este ejemplo crea un activador que inserta un registro en una segunda tabla (MyAudit) después de insertar un registro en la tabla
en la que se define el activador (MyTable). Aquí, la tabla "insertada" es una tabla especial utilizada por Microsoft SQL Server para
almacenar las filas afectadas durante las declaraciones INSERT y UPDATE; también hay una tabla especial "eliminada" que realiza
la misma función para las declaraciones DELETE.
CREAR DISPARADOR MyTrigger
EN Mi Mesa
DESPUÉS DE INSERTAR
COMO
COMENZAR
insertar registro de auditoría en la tabla MyAudit
INSERTAR EN MyAudit(MyTableId, Usuario)
(SELECCIONE MyTableId, CURRENT_USER FROM insertado)
FIN
Sección 55.2: Use Trigger para administrar una "Papelera de reciclaje" para los
elementos eliminados
CREATE TRIGGER BooksDeleteTrigger ON
MyBooksDB.Books
DESPUÉS DE ELIMINAR
COMO
INSERTAR EN BooksRecycleBin
SELECCIONAR *
DESDE eliminado;
IR
GoalKicker.com Notas SQL para profesionales 144
Machine Translated by Google
Capítulo 56: Transacciones
Sección 56.1: Transacción Simple
COMENZAR TRANSACCIÓN
INSERTAR EN Empleados eliminados (ID de empleado, Fecha de eliminación, Usuario)
(SELECCIONE 123, GetDate(), CURRENT_USER);
ELIMINAR DE Empleados DONDE EmployeeID = 123;
COMPROMETER TRANSACCIÓN
Sección 56.2: Transacción de reversión
Cuando algo falla en su código de transacción y desea deshacerlo, puede revertir su transacción:
COMENZAR PROBAR
COMENZAR TRANSACCIÓN
INSERTAR EN Usuarios (ID, Nombre, Edad)
VALORES(1, 'Bob', 24)
ELIMINAR DE Usuarios DONDE Nombre = 'Todd'
COMPROMETER TRANSACCIÓN
FIN DE PRUEBA
COMENZAR LA CAPTURA
TRANSACCIÓN DE RETROCESO
FIN DE CAPTURA
GoalKicker.com Notas SQL para profesionales 145
Machine Translated by Google
Capítulo 57: Diseño de la mesa
Sección 57.1: Propiedades de una tabla bien diseñada
Una verdadera base de datos relacional debe ir más allá de arrojar datos en unas pocas tablas y escribir algunas declaraciones SQL para extraer esos
datos.
En el mejor de los casos, una estructura de tabla mal diseñada ralentizará la ejecución de las consultas y podría imposibilitar que la base de datos
funcione según lo previsto.
Una tabla de base de datos no debe considerarse como una tabla más; tiene que seguir un conjunto de reglas para ser considerado verdaderamente
relacional. Académicamente se le llama 'relación' para hacer la distinción.
Las cinco reglas de una tabla relacional son:
1. Cada valor es atómico; el valor en cada campo en cada fila debe ser un solo valor.
2. Cada campo contiene valores que son del mismo tipo de datos.
3. Cada encabezado de campo tiene un nombre único.
4. Cada fila de la tabla debe tener al menos un valor que la haga única entre los demás registros de la tabla.
mesa.
5. El orden de las filas y columnas no tiene importancia.
Una tabla conforme a las cinco reglas:
ID Nombre Fecha de nacimiento Gerente
1 Fred 02/11/1971 3
2 Fred 02/11/1971 3
3 sue 07/08/1975 2
Regla 1: Cada valor es atómico. Id, Name, DOB y Manager solo contienen un único valor.
Regla 2: Id contiene solo números enteros, Nombre contiene texto (podríamos agregar que es texto de cuatro caracteres o menos), Fecha de
nacimiento contiene fechas de un tipo válido y Gerente contiene números enteros (podríamos agregar que corresponde a un campo Clave principal
en un administrador mesa).
Regla 3: Id, Name, DOB y Manager son nombres de encabezado únicos dentro de la tabla.
Regla 4: La inclusión del campo Id asegura que cada registro sea distinto de cualquier otro registro dentro de la tabla.
Una mesa mal diseñada:
Nombre de identificación fecha de nacimiento Nombre
1 Fred 02/11/1971 3
1 Fred 02/11/1971 3
3 Sue Viernes 18 de julio de 1975 2, 1
Regla 1: el segundo campo de nombre contiene dos valores: 2 y 1.
Regla 2: El campo DOB contiene fechas y texto.
Regla 3: Hay dos campos llamados 'nombre'.
Regla 4: El primer y segundo registro son exactamente iguales.
Regla 5: Esta regla no se rompe.
GoalKicker.com Notas SQL para profesionales 146
Machine Translated by Google
Capítulo 58: Sinónimos
Sección 58.1: Crear sinónimo
CREAR SYNONYM EmployeeData
PARA MyDatabase.dbo.Employees
GoalKicker.com Notas SQL para profesionales 147
Machine Translated by Google
Capítulo 59: Esquema de información
Sección 59.1: Búsqueda de esquema de información básica
Una de las consultas más útiles para los usuarios finales de grandes RDBMS es la búsqueda de un esquema de información.
Tal consulta permite a los usuarios encontrar rápidamente tablas de bases de datos que contienen columnas de interés, como cuando intentan relacionar
datos de 2 tablas indirectamente a través de una tercera tabla, sin saber qué tablas pueden contener claves u otras columnas útiles en común con las tablas
de destino. .
Usando TSQL para este ejemplo, el esquema de información de una base de datos se puede buscar de la siguiente manera:
SELECCIONAR *
DESDE INFORMACIÓN_ESQUEMA.COLUMNAS DONDE
COLUMNA_NOMBRE COMO '%Institución%'
El resultado contiene una lista de columnas coincidentes, los nombres de sus tablas y otra información útil.
GoalKicker.com Notas SQL para profesionales 148
Machine Translated by Google
Capítulo 60: Orden de ejecución
Sección 60.1: Orden lógico del procesamiento de consultas en SQL
/*(8)*/ SELECCIONAR /*9*/ DISTINTO /*11*/ SUPERIOR
/*(1)*/ DESDE /
*(3)*/ / UNIRSE
*(2)*/ EN
/*(4)*/ DONDE
/*(5)*/ AGRUPAR POR
/*(6)*/ CON {CUBO | ENROLLAR}
/*(7)*/ TENER
/*(10)*/ ORDENAR POR
/*(11)*/ LÍMITE
El orden en que se procesa una consulta y la descripción de cada sección.
VT significa 'Tabla virtual' y muestra cómo se producen varios datos a medida que se procesa la consulta
1. FROM: Se realiza un producto cartesiano (unión cruzada) entre las dos primeras tablas en la cláusula FROM, y como
como resultado, se genera la tabla virtual VT1.
2. ON: El filtro ON se aplica a VT1. Solo las filas para las que es VERDADERO se insertan en VT2.
3. OUTER (join): si se especifica OUTER JOIN (a diferencia de CROSS JOIN o INNER JOIN), las filas de la tabla o tablas
preservadas para las que no se encontró una coincidencia se agregan a las filas de VT2 como filas externas. , generando VT3.
Si aparecen más de dos tablas en la cláusula FROM, los pasos 1 a 3 se aplican repetidamente entre el resultado de la última
combinación y la siguiente tabla en la cláusula FROM hasta que se procesan todas las tablas.
4. DONDE: El filtro DONDE se aplica a VT3. Solo las filas para las que es VERDADERO se insertan en VT4.
5. GROUP BY: las filas de VT4 se organizan en grupos según la lista de columnas especificada en la cláusula GROUP BY. Se
genera VT5.
6. CUBO | ROLLUP: Se agregan supergrupos (grupos de grupos) a las filas desde VT5, generando VT6.
7. HAVING: El filtro HAVING se aplica a VT6. Solo los grupos para los que es VERDADERO se insertan en VT7.
8. SELECT: Se procesa la lista SELECT, generando VT8.
9. DISTINTO: las filas duplicadas se eliminan de VT8. Se genera VT9.
10. ORDER BY: Las filas de VT9 se ordenan según la lista de columnas especificada en la cláusula ORDER BY. A
se genera el cursor (VC10).
11. SUPERIOR: el número o porcentaje de filas especificado se selecciona desde el principio de VC10. La tabla VT11 es
generado y devuelto a la persona que llama. LIMIT tiene la misma funcionalidad que TOP en algunos dialectos SQL como
Postgres y Netezza.
GoalKicker.com Notas SQL para profesionales 149
Machine Translated by Google
Capítulo 61: Código limpio en SQL
Cómo escribir consultas SQL buenas y legibles y ejemplos de buenas prácticas.
Sección 61.1: Formato y ortografía de palabras clave y nombres
Nombres de tablas/columnas
Dos formas comunes de formatear nombres de tablas/columnas son CamelCase y serpiente_caso:
SELECCIONE Nombre, Apellido
DE Empleados
DONDE Salario > 500;
SELECCIONE nombre, apellido
DE empleados
DONDE salario > 500;
Los nombres deben describir lo que está almacenado en su objeto. Esto implica que los nombres de las columnas normalmente deben ser singulares.
Si los nombres de las tablas deben usar singular o plural es un tema muy discutido. pregunta, pero en la práctica, es más común usar nombres de tablas
en plural.
Agregar prefijos o sufijos como tbl o col reduce la legibilidad, así que evítelos. Sin embargo, a veces se usan para evitar conflictos con las palabras clave de SQL y,
a menudo, se usan con disparadores e índices (cuyos nombres generalmente no se mencionan en las consultas).
Palabras clave
Las palabras clave de SQL no distinguen entre mayúsculas y minúsculas. Sin embargo, es una práctica común escribirlos en mayúsculas.
Sección 61.2: Sangría
No existe un estándar ampliamente aceptado. En lo que todos están de acuerdo es que comprimir todo en una sola línea es malo:
SELECCIONE d.Nombre, CUENTA(*) AS Empleados DE Departamentos AS d ÚNASE Empleados AS e EN d.ID = e.IDDepartamento
DONDE d.Nombre != 'HR' HABIENDO CONTADOR(*) > 10 ORDEN POR CONTADOR(*) DESC ;
Como mínimo, coloque cada cláusula en una nueva línea y divida las líneas si se volverían demasiado largas de lo contrario:
SELECCIONE
d.Nombre, CUENTA(*) AS Empleados
FROM Departamentos AS d
JOIN Empleados AS e ON d.ID = e.DepartmentID WHERE d.Name !
= 'HR'
TENIENDO CUENTA(*) > 10
ORDEN POR CUENTA(*) DESC;
A veces, todo lo que sigue a la palabra clave de SQL que introduce una cláusula se sangra en la misma columna:
SELECCIONE d.Nombre,
COUNT(*) AS Empleados
DE Departamentos AS d
UNIRSE Empleados AS e ON d.ID = e.DepartmentID d.Name != 'HR'
DÓNDE
TENER CONTEO(*) > 10
GoalKicker.com Notas SQL para profesionales 150
Machine Translated by Google
ORDEN POR CUENTA(*) DESC;
(Esto también se puede hacer mientras se alinean las palabras clave de SQL a la derecha).
Otro estilo común es poner palabras clave importantes en sus propias líneas:
SELECCIONE
d.Nombre, CUENTA(*) AS
Empleados DE Departamentos
AS d ÚNASE
Empleados AS e ON
d.ID = e.DepartmentID
DÓNDE
d.Nombre != 'HR'
TENIENDO CUENTA(*) >
10 ORDEN
POR CUENTA(*) DESC;
La alineación vertical de varias expresiones similares mejora la legibilidad:
SELECCIONE el modelo,
ID de empleado
DESDE Coches
DONDE IDCliente = 42
Y Estado = 'LISTO';
El uso de varias líneas dificulta la integración de comandos SQL en otros lenguajes de programación. Sin embargo, muchos lenguajes tienen un
mecanismo para cadenas de varias líneas, por ejemplo, @"..." en C#, """...""" en Python o R"(...)" en C++.
Sección 61.3: SELECCIONAR *
SELECT * devuelve todas las columnas en el mismo orden en que están definidas en la tabla.
Al usar SELECT *, los datos devueltos por una consulta pueden cambiar cada vez que cambia la definición de la tabla. Esto aumenta el
riesgo de que diferentes versiones de su aplicación o su base de datos sean incompatibles entre sí.
Además, leer más columnas de las necesarias puede aumentar la cantidad de E/S de disco y red.
Por lo tanto, siempre debe especificar explícitamente las columnas que realmente desea recuperar:
SELECCIONAR * no
SELECCIONE ID, FName, LName, PhoneNumber hacer
DE Empleados;
(Al realizar consultas interactivas, estas consideraciones no se aplican).
Sin embargo, SELECT * no hace daño en la subconsulta de un operador EXISTS, porque EXISTS ignora los datos reales de todos modos (comprueba
solo si se ha encontrado al menos una fila). Por la misma razón, no tiene sentido enumerar ninguna columna específica para EXISTE, por lo que
SELECCIONAR * en realidad tiene más sentido:
enumere los departamentos donde no se contrató a nadie recientemente
SELECCIONE ID,
Nombre
DESDE Departamentos
GoalKicker.com Notas SQL para profesionales 151
Machine Translated by Google
DONDE NO EXISTE (SELECCIONE *
FROM Empleados
WHERE DepartmentID = Departments.ID
AND HireDate >= '20150101');
Sección 61.4: Uniones
Siempre se deben utilizar uniones explícitas; Las uniones implícitas tienen varios problemas:
La condición de unión está en algún lugar de la cláusula WHERE, mezclada con cualquier otra condición de filtro. Esto hace que sea más
difícil ver qué tablas están unidas y cómo.
Por lo anterior, existe un mayor riesgo de errores, y es más probable que se encuentren más tarde.
En SQL estándar, las uniones explícitas son la única forma de usar uniones externas:
SELECCIONE
d.Nombre, e.Fname || e.LName AS
EmpName FROM Departamentos
AS d LEFT JOIN Empleados AS e ON d.ID = e.DepartmentID;
Las uniones explícitas permiten usar la cláusula USING:
SELECT RecipeID,
Recipes.Name,
COUNT (*) AS NumberOfIngredients
DE Recetas
LEFT JOIN Ingredientes USING (RecipeID);
(Esto requiere que ambas tablas usen el mismo nombre de columna.
USING elimina automáticamente la columna duplicada del resultado, por ejemplo, la unión en esta consulta devuelve una sola columna
RecipeID ).
GoalKicker.com Notas SQL para profesionales 152
Machine Translated by Google
Capítulo 62: Inyección SQL
La inyección de SQL es un intento de acceder a las tablas de la base de datos de un sitio web mediante la inyección de SQL en un campo de formulario. Si
un servidor web no protege contra los ataques de inyección SQL, un pirata informático puede engañar a la base de datos para que ejecute el código SQL adicional.
Al ejecutar su propio código SQL, los piratas informáticos pueden actualizar el acceso a su cuenta, ver la información privada de otra persona o
realizar cualquier otra modificación en la base de datos.
Sección 62.1: ejemplo de inyección SQL
Suponiendo que la llamada al controlador de inicio de sesión de su aplicación web se vea así:
https://somepage.com/ajax/login.ashx?username=admin&password=123
Ahora en login.ashx, lee estos valores:
strUserName = getHttpsRequestParameterString("nombre de usuario"); strPassword
= getHttpsRequestParameterString("contraseña");
y consulte su base de datos para determinar si existe un usuario con esa contraseña.
Entonces construyes una cadena de consulta SQL:
'" "'
txtSQL = "SELECT * FROM Usuarios WHERE nombre de usuario + strNombreUsuario + Y contraseña = '"+ strContraseña
= +"'";
Esto funcionará si el nombre de usuario y la contraseña no contienen comillas.
Sin embargo, si uno de los parámetros contiene una comilla, el SQL que se envía a la base de datos se verá así:
strNombreUsuario = "d'Alambert"; txtSQL
= "SELECCIONE * DE Usuarios DONDE nombre de usuario = 'd'Alambert' Y contraseña = '123'";
Esto dará como resultado un error de sintaxis, porque la comilla después de la d en d'Alambert finaliza la cadena SQL.
Puede corregir esto escapando de las comillas en el nombre de usuario y la contraseña, por ejemplo:
strNombreUsuario = strNombreUsuario.Replace("'", "''");
strContraseña = strContraseña.Replace("'", "''");
Sin embargo, es más apropiado usar parámetros:
cmd.CommandText = "SELECCIONE * DE Usuarios DONDE nombre de usuario = @nombre de usuario Y contraseña = @contraseña";
cmd.Parameters.Add("@nombre de usuario", strNombreDeUsuario);
cmd.Parameters.Add("@contraseña", strContraseña);
Si no usa parámetros y olvida reemplazar las comillas incluso en uno de los valores, entonces un usuario malicioso (también conocido como pirata
informático) puede usar esto para ejecutar comandos SQL en su base de datos.
Por ejemplo, si un atacante es malvado, establecerá la contraseña para
jajaja'; DROP DATABASE maestro;
y luego el SQL se verá así:
GoalKicker.com Notas SQL para profesionales 153
Machine Translated by Google
"SELECT * FROM Usuarios WHERE nombre de usuario = 'alguien' Y contraseña = 'lol'; DROP DATABASE master; '";
Desafortunadamente para usted, este es un SQL válido, ¡y la base de datos lo ejecutará!
Este tipo de explotación se denomina inyección de SQL.
Hay muchas otras cosas que un usuario malicioso podría hacer, como robar la dirección de correo electrónico de cada usuario, robar la contraseña de
todos, robar números de tarjetas de crédito, robar cualquier cantidad de datos en su base de datos, etc.
Es por eso que siempre necesitas escapar de tus cuerdas.
Y el hecho de que invariablemente te olvides de hacerlo tarde o temprano es exactamente la razón por la que deberías usar parámetros. Porque si usa
parámetros, entonces su marco de lenguaje de programación hará cualquier escape necesario por usted.
Sección 62.2: muestra de inyección simple
Si la instrucción SQL se construye así:
'" "'
SQL = "SELECCIONAR * DE Usuarios DONDE nombre de + usuario + Y contraseña ='" + pw + "'";
usuario = db.execute(SQL);
Luego, un pirata informático podría recuperar sus datos al proporcionar una contraseña como pw' o '1'='1; la declaración SQL resultante será:
SELECCIONE * DE Usuarios DONDE nombre de usuario = 'alguien' Y contraseña = 'pw' o '1' = '1'
Este pasará la verificación de contraseña para todas las filas en la tabla Usuarios porque '1'='1' siempre es verdadero.
Para evitar esto, use parámetros SQL:
SQL = "SELECCIONE * DESDE Usuarios DONDE nombre de usuario = ? Y contraseña = ?";
db.execute(SQL, [usuario, pw]);
GoalKicker.com Notas SQL para profesionales 154
Machine Translated by Google
Créditos
Muchas gracias a todas las personas de Stack Overflow Documentation que ayudaron a proporcionar este contenido, se
pueden enviar más cambios a [email protected] para que se publique o actualice nuevo contenido
Özgur Özturk Capítulos 8 y 17
3N1GM4 Capítulo 7
a1ex07 capitulo 37
abe miessler Capítulo 7
Abhilash R Vankayala Capítulos 5, 6, 11, 27, 30 y 32
aholmes Capítulo 6
Aidan Capítulos 21 y 25
alex9311 capitulo 21
Almir Vuk Capítulos 21 y 37
alok singh Capítulo 6
Ameya Deshpande capitulo 26
Amir Pourmand
capitulo 56
Amnón Capítulo 6
Andrea capitulo 24
andres montani capitulo 36
andreas Capitulo 2
Andy G. capitulo 18
apomeno Capítulo 6
Ares capitulo 21
Arco capitulo 45
Arpit Solanki Capítulo 6
arturo d capitulo 41
Arulkumar Capítulos 13 y 41
ashja99 Capítulos 11 y 42
Atafoud capitulo 24
Ayaz Shah Capítulo 11
a_arnold capitulo 18
bart schuyt Capítulo 11
Batsu capitulo 41
bhs capitulo 45
bignose Capítulo 5
blackbishop capitulo 25
Leve capitulo 17
Bostjan Capítulos 5, 7 y 13
branco dimitrijevic capitulo 18
Brichines de Capítulo 6
Brent Oliver capitulo 54
carlosb Capítulos 37 y 39
cris Capítulo 6
cristiano Capítulo 5
Christian Sagmuller Capítulo 6
Cristos Capítulo 6
CL. Capítulos 1, 2, 6, 8, 10, 14, 18, 19, 21, 31, 36, 37, 41, 42, 46, 49, 61 y 62
Cristian Abelleira capitulo 30
daimto capitulo 30
Daniel capitulo 46
GoalKicker.com Notas SQL para profesionales 155
Machine Translated by Google
Daniel Langemann Capítulos 18 y 24
cientos Capítulo 6
Darío Capítulos 10 y 19
darrel lee capitulo 40
darren bartrup Capítulos 18 y 57
daryl Capítulos 6, 55, 56 y 58
dasblinkenlight capitulo 52
david manheim capitulo 37
david pino Capítulo 6
David Spillett Capítulo 6
soñador de día Capítulo 6
dd4711 capitulo 46
dmfay capitulo 48
Durgpal Singh Capítulo 6
dylan vanderberg Capítulos 21 y 29
Emilio Rowland capitulo 20
Eric VB Capítulo 6
Florín Ghita Capítulos 5, 18, 25, 42 y 47
Defensor Capítulos 5, 6, 19, 36 y 37
FlyingPiMonster Capítulos 5 y 18
franck dernoncourt Capítulos 6, 18 y 41
Franco Capítulo 7
lógica difusa capitulo 46
galus capitulo 60
adicto Capítulo 1
ir capitulo 45
puerta Dorada capitulo 41
guiguiblitz Capítulo 9
H. Pauwelyn capitulo 21
Hack capitulo 59
Harish Gyanani Capítulo 11
Hacha capitulo 50
Harjot capitulo 41
Hellyale Capítulo 11
HK1 capitulo 18
HLGEM capitulo 18
Hoang Hieu Capítulo 6
Horaciux capitulo 37
bernardo hynek capitulo 30
Ian Kenney me capitulo 42
gusta codificar Capítulo 6
Imran Alí Khan Capítulos 6, 41 y 42
Todavía Capítulo 6
IncrediApp capitulo 55
jared hooper Capítulo 6
jason w capitulo 24
JavaHopper Capítulo 5
Jaideep Jadhav capitulo 41
jaydles Capítulos 6, 7, 8 y 10
genismo capitulo 37
Jerry Jeremías capitulo 45
Jim capitulo 24
joe taras capitulo 24
GoalKicker.com Notas SQL para profesionales 156
Machine Translated by Google
Joel Capítulos 29 y 31
Juan Odom Capítulos 6, 18, 22, 32 y 56
Juan Slegers Capítulos 6 y 18
John Smith capitulo 51
Juan L Bevan Capítulo 1
Jojodmo capitulo 21
jon chan Capítulo 13
Jon Ericson Capítulos 1 y 13
Juan Capítulo 6
Juergen d Capítulos 12, 13 y 42
Karthikeyan capitulo 28
Kevin Björk Nielsen Capítulos 41 y 43
KIRAN KUMAR MATAM capitulo 21
KjetilNordin capitulo 36
sin bragas capitulo 62
Lankymart Capítulo 6
LCIII Capítulo 15
Leigh Riffel capitulo 41
lexi capitulo 25
Lohitha Palagiri Capítulo 11
Marcos Iannucci Capítulos 6 y 18
Marcos Perera Capítulos 6 y 11
marco stewart capitulo 43
Matas Vaitkevicius Capítulos 6, 13, 14, 19, 21 y 41
Mateusz Piotrowski capitulo 41
Mate Capítulos 5, 6 y 10
mate s Capítulo 6
matthew whit Capítulo 6
divertido capitulo 37
mihait Capítulos 6 y 24
mithra chintha Capítulos 8 y 25
MotKohn Capítulo 10
Mureinik Capítulos 10, 18 y 45
mustaccio Capítulos 6 y 45
Mzzzzzz Capítulo 5
natan capitulo 42
nazark Capítulo 8
Neria Nachum capitulo 41
Nunie123 capitulo 52
Odado Capítulo 6
Capítulos 6 y 11
Generador de datos omini Capítulos 42 y 44
un día cuando Capítulo 6
Ozair Kafray capitulo 25
Interrumpido Capítulos 8 y 37
Pablo Bambury capitulo 30
paulo freitas capitulo 37
pedro k Capítulos 42 y 46
francisco Capítulos 1, 3, 4, 8, 11, 13, 18, 19, 29, 38, 41, 46, 49, 52 y 53
Prateek Capítulos 1, 6 y 21
camión Capítulo 6
Raquel Hilán Capítulo 6
Raholling capitulo 18
GoalKicker.com Notas SQL para profesionales 157
Machine Translated by Google
Machine Translated by Google
También te puede interesar
También podría gustarte
- Manual Base de Datos Avanzado I (0264)Aún no hay calificacionesManual Base de Datos Avanzado I (0264)238 páginas
- Diseño de Repositorios NoSQL con MongoDBAún no hay calificacionesDiseño de Repositorios NoSQL con MongoDB42 páginas
- 0racl3 12c - SQL. Curs0 Práctico de Formación PDF100% (4)0racl3 12c - SQL. Curs0 Práctico de Formación PDF351 páginas
- Comparativa: SQL vs NoSQL en Bases de DatosAún no hay calificacionesComparativa: SQL vs NoSQL en Bases de Datos2 páginas
- Gestión de Proyectos Con Git: CORE IWEB 2018-2019 Santiago Pavón Juan Quemada Versión: 2019-01-30Aún no hay calificacionesGestión de Proyectos Con Git: CORE IWEB 2018-2019 Santiago Pavón Juan Quemada Versión: 2019-01-30113 páginas
- MySQL Triggers Funciones y ProcedimientoAún no hay calificacionesMySQL Triggers Funciones y Procedimiento80 páginas
- Modelado y Programación OO: Guía Práctica100% (1)Modelado y Programación OO: Guía Práctica519 páginas
- Crear una Vista de Origen en SSASAún no hay calificacionesCrear una Vista de Origen en SSAS130 páginas
- Manual SQL Server 2022 Pit - Implementación90% (10)Manual SQL Server 2022 Pit - Implementación213 páginas
- Arquitecturas para Aplicaciones JavaAún no hay calificacionesArquitecturas para Aplicaciones Java137 páginas
- Una Nueva Forma de Desarrollo Visual Studio 2010 - Cristian Sánchez Flores100% (1)Una Nueva Forma de Desarrollo Visual Studio 2010 - Cristian Sánchez Flores368 páginas
- Guía Completa de VueJS para Desarrolladores100% (1)Guía Completa de VueJS para Desarrolladores209 páginas
- Funciones y Procedimientos en DelphiAún no hay calificacionesFunciones y Procedimientos en Delphi7 páginas
- Introduccion A Las Bases de Dat - Antonio Sarasa100% (4)Introduccion A Las Bases de Dat - Antonio Sarasa308 páginas
- Recursos sobre Bases de Datos GratisAún no hay calificacionesRecursos sobre Bases de Datos Gratis737 páginas
- Alice Zhao SQL Pocket Guide A Guide To SQL Usage O'Reilly Media100% (4)Alice Zhao SQL Pocket Guide A Guide To SQL Usage O'Reilly Media460 páginas
- SQL - Guía Completa para Principiantes de La Programación SQL Con Ejercicios83% (6)SQL - Guía Completa para Principiantes de La Programación SQL Con Ejercicios153 páginas
- Guía Completa de SQL: Desde Básico a Avanzado0% (1)Guía Completa de SQL: Desde Básico a Avanzado57 páginas
- Descarga El Apunte de La Semana 4Aún no hay calificacionesDescarga El Apunte de La Semana 421 páginas
- Índice Libro SQL Server 2005 1 - 6Aún no hay calificacionesÍndice Libro SQL Server 2005 1 - 66 páginas
- Introducción a SQL y bases de datosAún no hay calificacionesIntroducción a SQL y bases de datos7 páginas
- Presentación Diapositivas Propuesta de Proyecto Minimalista Simple Verde y - 20250321 - 112602 - 0000Aún no hay calificacionesPresentación Diapositivas Propuesta de Proyecto Minimalista Simple Verde y - 20250321 - 112602 - 00008 páginas
- ENI - SQL Server 2008, SQL y Transact SQL PDFAún no hay calificacionesENI - SQL Server 2008, SQL y Transact SQL PDF203 páginas
- Resumen de SQL: Fundamentos y ALTER TABLEAún no hay calificacionesResumen de SQL: Fundamentos y ALTER TABLE4 páginas
- Analisis de Datos Nivel Intermedio M 1 Fundamentos y Estructura de Bases de Datos en SQLAún no hay calificacionesAnalisis de Datos Nivel Intermedio M 1 Fundamentos y Estructura de Bases de Datos en SQL28 páginas
- Fundamentos de Consultas SQL con SELECTAún no hay calificacionesFundamentos de Consultas SQL con SELECT8 páginas
- Operaciones CRUD en SQL: Guía BásicaAún no hay calificacionesOperaciones CRUD en SQL: Guía Básica7 páginas
- Manual de SQL Server Airam de JesúsAún no hay calificacionesManual de SQL Server Airam de Jesús161 páginas
- Introducción a SQL y sus ComandosAún no hay calificacionesIntroducción a SQL y sus Comandos36 páginas
- Tarea 2 Diseño de Compensadores A Partir Del Lugar Geometrico de Las RaicesAún no hay calificacionesTarea 2 Diseño de Compensadores A Partir Del Lugar Geometrico de Las Raices14 páginas
- Diseño de Compensadores en Control AnálogoAún no hay calificacionesDiseño de Compensadores en Control Análogo7 páginas
- Guía para Inscripción en Cursos PrácticosAún no hay calificacionesGuía para Inscripción en Cursos Prácticos1 página
- Ebook Vende Tu Conocimiento - Alexis Garu100% (1)Ebook Vende Tu Conocimiento - Alexis Garu33 páginas
- Ejercicios de Física: Tarea 3 UNADAún no hay calificacionesEjercicios de Física: Tarea 3 UNAD16 páginas
- Mantenimiento de Computadores Sena PDFAún no hay calificacionesMantenimiento de Computadores Sena PDF110 páginas
- Funciones para Cuarto de SecundariaAún no hay calificacionesFunciones para Cuarto de Secundaria4 páginas
- PUJ D Brochure IAProductividadLaboralAún no hay calificacionesPUJ D Brochure IAProductividadLaboral17 páginas
- Forcípula Digital para Marcaje ForestalAún no hay calificacionesForcípula Digital para Marcaje Forestal16 páginas
- Auditoria Aplicaciones Fraude PED AmpliadoAún no hay calificacionesAuditoria Aplicaciones Fraude PED Ampliado6 páginas
- Estrategias Clave en Comercio ElectrónicoAún no hay calificacionesEstrategias Clave en Comercio Electrónico3 páginas
- Tarea IV Analisis y Diseno de SistemaAún no hay calificacionesTarea IV Analisis y Diseno de Sistema4 páginas
- Fundamentos de Sistemas de InformaciónAún no hay calificacionesFundamentos de Sistemas de Información44 páginas
- Levantamiento Topográfico en MoyocorralAún no hay calificacionesLevantamiento Topográfico en Moyocorral15 páginas
- Evaluación de Conocimientos en ExcelAún no hay calificacionesEvaluación de Conocimientos en Excel3 páginas
- Uso de Empatía en Atención al ClienteAún no hay calificacionesUso de Empatía en Atención al Cliente13 páginas
- Diseño y Simulación de un ElectrocardiógrafoAún no hay calificacionesDiseño y Simulación de un Electrocardiógrafo4 páginas
- Dibujo Topográfico con AutoCAD Civil 3DAún no hay calificacionesDibujo Topográfico con AutoCAD Civil 3D17 páginas
- Guía de Algoritmos y PseudocódigoAún no hay calificacionesGuía de Algoritmos y Pseudocódigo13 páginas
- Ciberdelincuencia en Perú durante la pandemiaAún no hay calificacionesCiberdelincuencia en Perú durante la pandemia2 páginas
- Catálogo LTM 1095-5.1 - DecryptedAún no hay calificacionesCatálogo LTM 1095-5.1 - Decrypted11 páginas
- Opciones y Requisitos de InstalaciónAún no hay calificacionesOpciones y Requisitos de Instalación10 páginas
- Jshell: Conceptos, Testing, Jshell en Consola, Jshell in An IdeAún no hay calificacionesJshell: Conceptos, Testing, Jshell en Consola, Jshell in An Ide10 páginas
