sql

SQL(LENGUAJE DE CONSULTA ESTRUCTURADO)

• Lenguaje de definición de datos (LDD): Comandos para la definicion de esquemas de relacion , borrado de relaciones y modificaciones de los esquemas de relacion.

• Lenguaje de manipulación de datos (LMD): Lenguaje de consulta basado en el algebra relacional como en el calculo relacional de tuplas. Comandos para insertar, borrar y modificar tuplas.

• Integridad: Comandos para espicificar las restricciones que deben cumplir los datos almacenedso enla base de datos.

• Definicion de vistas: Comandos para la definicion de vistas.

SQL(LENGUAJE DE CONSULTA ESTRUCTURADO) • Control de transaciones:Comandos para espedificar el cominzo y el

final de las transacciones• SQL Incorporado y SQL Dinamico: Comandos que definien como se

pueden incorporar instruciones d SQL en los lenguajes de programacion. • Autorización: Comandos para espedificar los derechos de acceso a las

relaciones y a las vistas(LDD).

Notas: Varios de los motores relacionales no soportan la mayor parte de la norma SQL, y en algunos casos algunos motores tiene constructores no estandares.

Lenguaje de definición de datos (LDD)Comando CREATE DATABASES– define una nueva base de datos.Sintaxis PosgresqlCREATE DATABASE name

[ [ WITH ] [ OWNER [=] dbowner ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace ] Lugar donde se almacena los archivos en disco [ CONNECTION LIMIT [=] connlimit ] ]

WITH OWNER –Define propietario de la base de datos.

TEMPLATE -- Define y copia los objetos estructuras de una base de datos de referencia .

ENCODING – Es la codificación de cómo se almacenan los caracteres en la base de datos• No se puede cambiar el encode a una Base de Datos existente(Respaldar y volverla a crear)• Codificar la Base de Datos en UTF-8 por que este tipo de codificación permite cualquier tipo de carácter.• http://es.wikipedia.org/wiki/Codificaci%C3%B3n_de_caracteres.

LC_COLLAT– (Intercalación ) se refiere a las reglas utilizadas para ordenar y comparar caracteres. Evidentemente, se trata de un concepto que afecta sólo a los campos de texto.

LC_CTYPE - Especifica una colección de nombres de tabla de las cuales esta tabla hereda todos los campos.

CONNECTION LIMIT– Permite hasta un numero determinado de conexciones.

Lenguaje de definición de datos (LDD) Tablas• Comandos para la definicion de esquemas de relacion , borrado de

relaciones y modificaciones de los esquemas de relacion.

• Las tablas son la estructura básica donde se almacena la información en la base de datos.

• Las tablas se dividen en filas y columnas.

Lenguaje de definición de datos (LDD)Comando CREATE TABLE – define una nueva tabla.Sintaxis PosgresqlCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [ { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ] | table_constraint | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] } [, ... ] ] ) [ INHERITS ( parent_table [, ... ] ) ] [ WITH OIDS | WITHOUT OIDS ] números seriales sirven como identificador de[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] (usada en tablas temporales) tabla vaciarse de registros cada vez que se ejecuta un COMMIT[ TABLESPACE tablespace ]

TEMP--Cuando se utiliza esta clausula se crean tablas temporales. Estas tablas se usan para almacenar datos que seran usados posteriormente, guardar resultados parciales, analizar grandes cantidades de filas.

DEFAULT-- Un valor por defecto para el campo.

table_constraint o column_constraint -- Las cláusulas opcional de restricciones (CONSTRAINT) especifica una lista de restricciones de integridad que las nuevas inserciones o las actualizaciones deberán satisfacer para que una sentencia insert o update se puedan realizar.

INHERITS -- Especifica una colección de nombres de tabla de las cuales esta tabla hereda todos los campos.

TABLESPACE– Define el lugar , en el sistema de archivos, donde los archivos de la tabla se almacenan en disco.

Lenguaje de definición de datos (LDD)Clausulas que se utilizan en la restriciones de columnas – column_constraint : [ CONSTRAINT constraint_name ] { NOT NULL | NULL | UNIQUE [ USING INDEX TABLESPACE tablespace ] | PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] | CHECK (expression) | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]

[ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

NULL-- El campo puede contener valores NULL. NOT NULL-- El campo no puede contener valores NULL. Esto equivale a la restricción de campo CHECK (column NOT NULL). UNIQUE-- El campo debe contener un valor único. PRIMARY KEY-- Este campo es una clave primaria, lo que implica que la unicidad es forzada por el sistema CHECK--La restricción CHECK especifica una restricción sobre los valores permitidos en un campo

Lenguaje de definición de datos (LDD)Clausulas que se utilizan en la restriciones de tablas– table_constraint [ CONSTRAINT constraint_name ] { UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |CHECK ( expression ) |FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

CONSTRAINT – Se denota el nombre de la restricción (constraint_name )UNIQUE-- El campo debe contener un valor único. PRIMARY KEY-- Este campo es una clave primaria, lo que implica que la unicidad es forzada por el sistema CHECK--La restricción CHECK especifica una restricción sobre los valores permitidos en un campo.FOREIGN KEY – Son campos de clave externa para relacionar dos tablas.

TIPOS DE DATOS (NUMERICOS)• Se debe buscar el tipo de dato numérico que mejor balance ofrezca entre rango, tamaño de almacenamiento, y

desempeño.• Tres tipo de datos numéricos

• Enteros• Precisión

• La precisión es el número de dígitos de un número. La escala es el número de dígitos situados a la derecha de la coma decimal de un número. Por ejemplo, el número 123,45 tiene una precisión de 5 y una escala de 2.

• Utilizado para realizar cálculos exactos. Tiene una precisión máxima de 1000 dígitos.• Punto flotante

• se utilizan cuando se calculan funciones que requieren precisión fraccionaria.• Seriales

• No es un verdadero tipo de datos, es una convención que permite configurar un identificador unico de columna que usa un generador de secuencia para generarlo.

Name Storage Size Description Range

smallint 2 bytes small-range integer -32768 to +32767integer 4 bytes usual choice for integer -2147483648 to +2147483647bigint 8 bytes large-range integer -9223372036854775808 to 9223372036854775807decimal variable user-specified precision, exact no limitnumeric variable user-specified precision, exact no limitreal 4 bytes variable-precision, inexact 6 decimal digits precisiondouble precision 8 bytes variable-precision, inexact 15 decimal digits precisionserial 4 bytes autoincrementing integer 1 to 2147483647bigserial 8 bytes large autoincrementing integer 1 to 9223372036854775807

TIPOS DE DATOS (CARACTER)

Name Description

character varying(n), varchar(n) variable-length with limit

character(n), char(n) fixed-length, blank padded

text variable unlimited length

TIPOS DE DATOS (Date/Time)

Name Storage Size Description Low Value High Value Resolution

timestamp [ (p) ] [ without time zone ] 8 bytes both date and time 4713 BC 5874897 AD

1 microsecond / 14 digits

timestamp [ (p) ] with time zone 8 bytes

both date and time, with time zone 4713 BC 5874897 AD


interval [ (p) ] 12 bytes time intervals -178000000 years 178000000 years


date 4 bytes dates only 4713 BC 5874897 AD 1 daytime [ (p) ] [ without time zone ] 8 bytes times of day only 00:00:00 24:00:00


time [ (p) ] with time zone 12 bytes

times of day only, with time zone 00:00:00+1359 24:00:00-1359


• Las fechas se almacenan internamente como datos numéricos, por lo que es posible realizar operaciones aritméticas con ellas.

• Los timestamps(fractional seconds precision) ) son típicamente usados para seguimiento de eventos, controlar concurrencia entre otros, se genera automáticamente cada vez que insertamos o modificamos una fila.

• Time zone el valor de la hora será ajustado a la zona horaria de la sesión actual.• Intervals son intervalos de tiempo• functions-datetime Funciones definidas que permiten obtener información de estos tipos de

datos.

TIPOS DE DATOS (BOLEANO)

• La sintexis es boolean • Lo valores permitidos para representar el estado verdadero

• TRUE• 't‘• ‘true‘• 'y‘• 'yes‘• '1‘

• Valores permitidos para representar el estado falso • FALSE• ‘f‘• 'false‘• 'n‘• 'no‘• '0’

Lenguaje de definición de datos (LDD)Comando CREATE TRIGGER– define disparador.Sintaxis Posgresql

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE funcname ( arguments )

DEFINICIÓN DE TRIGGER - http://es.wikipedia.org/wiki/Trigger_(base_de_datos)

BEFORE | AFTER – Determina si la función es llamada antes o después del evento.EVENT-- INSERT, UPDATE, o DELETE. Multiples eventos pueden ser especificados con OR.ROW | STATEMENT –Define si se ejecuta por cada fila afectada en el procedimiento o solo por la sentencia.funcname -La funcion o procedimiento que realiza el disparadorvalores permitidos en un campo.FOREIGN KEY – Son campos de clave externa para relacionar dos tablas.

Lenguaje de definición de datos (LDD)Comando CREATE VIEW– define una vista.Sintaxis Posgresql

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ] AS query

DEFINICIÓN DE VISTA- http://es.wikipedia.org/wiki/Vista_(base_de_datos)

[ TEMP | TEMPORARY – Es una vista temporal que se elimina al terminar la sesión.REPLACE – Remplaza una vista que ya tiene el mismo nombre. column_name – Son los nombres opcionales que se le dan a cada columna , sino se nombran se toman las de la consulta. Query– Se define la consulta que forma la vista .

Lenguaje de definición de datos (LDD)Comando CREATE RULE– define una regla.Sintaxis Posgresql

CREATE [ OR REPLACE ] RULE name AS ON eventTO table [ WHERE condition ] DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

DEFINICIÓN DE VISTA- http://es.wikipedia.org/wiki/Vista_(base_de_datos)

[ TEMP | TEMPORARY – Es una vista temporal que se elimina al terminar la sesión.REPLACE – Remplaza una vista que ya tiene el mismo nombre. column_name – Son los nombres opcionales que se le dan a cada columna , sino se nombran se toman las de la consulta. Query– Se define la consulta que forma la vista .

Lenguaje de consulta de datos(LCD)Comandos para selecionar informacion– define una seleccion.Sintaxis PosgresqlSELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression [ AS output_name ] [, ...]

[ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ] [ LIMIT { count | ALL } ]

where from_item can be one of: [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

• El SQL es muy diferente a muchos de los lenguajes de programación, ya que, el orden en el que se procesas las sentencias no es el mismo en la que se escribe.

• La primera sentencia que se procesa es el FROM , mientras la sentencia SELECT, la cual es la primera que aparece en la instrucción , se procesa tiempo después.

• Cada paso del procesamiento genera tablas virtuales que son entradas del siguiente paso (estas tablas internas no son accesibles por aplicaciones o otras consultas), solo la tabla del paso final es la que es retornado y puede ser utilizada para realizar otra operación.

PROCESAMINETO LOGICO DE UNA CONSULTA

EJEMPLO DE FASES DE LA DESCRIPCIÓN LOGICA DEL PROCESAMIENTO DE UNA CONSULTA

TBLCONSUMIDOR TBLORDENESIDCONSUMIDOR NOMBRE CIUDAD IDORDEN IDCONSUMIDOR

AA JOSE MEDELLIN 1 ABAB JUAN MEDELLIN 2 ABAC DIANA MEDELLIN 3 ACAD NATALIA BOGOTA 4 AC

5 AC6 AD7 NULL

• Tablas TBLCONSUMIDOR y TBLORDENES

• Consulta:SELECT C.IDCONSUMIDOR, COUNT(O.IDORDEN) AS NUMORDEN

FROM TBLCONSUMIDOR AS CLEFT OUTER JOIN TBLORDENES AS OON C.IDCONSUMIDOR = O.IDCONSUMIDOR

WHERE C.CIUDAD = “MEDELLIN”GROUP BY C.IDCONSUMIDORHAVING COUNT(O.IDORDEN) < 3ORDER BY NUMORDEN

• PASO 1:– FROM: Producto cartesiano (Reunión cruzada) de las primeras dos tablas en la sentencia

FROM y da como resultado una tabla virtual VT1

• PASO 2:

– ON: El filtro ON es aplicado a VT1. Solo las tuplas o filas para la cual la condición <condicion_reunion> es verdadera son agregadas en VT2.

• PASO 3:

– OUTER (JOIN): Las filas de las tablas o tablas para la cual no hay coincidencias son agregadas a la tabla VT2 como filas de reunión externas, generando VT3. Si mas de dos tablas aparecen en la clausula FROM , el paso 1 al paso 3 es aplicado repetidamente entre el resultado(tabla VT) del ultimo JOIN y la siguiente tabla dentro la clausula FROM, hasta procesar todas las tablas.

FASES DE LA DESCRIPCIÓN LOGICA DEL PROCESAMIENTO DE UNA CONSULTA

• PASO 4:– WHERE: Es el filtro aplicado a VT3o la tabla virtual generada del paso anterior. Solo las

filas para la cual la condición en WHERE es verdadera son agregadas a la tabla virtual VT4.

• PASO 5:– GROUP BY: Las Filas de VT4 son organizadas en grupos basados en las listas de las

columnas especificadas en la sentencia GROUP BY generando la tabla VT5.

• PASO 6:– HAVING: El Filtro HAVING es aplicado a VT5solo los grupos para lo cual la condición

expresada en HAVING <condicion_having> es verdadera son adicionada a la tabla VT6.

• PASO 7:– SELECT: La lista de columnas es procesada generando la tabla VT7.


• PASO 8:– DISTINC: Las filas duplicadas son removidas de la relación VT7 generada en el paso

anterior generando VT8.

• PASO 9:– ORDEN BY: Las Las Filas de VT4 son organizadas en grupos basados en las listas de las

columnas especificadas en la sentencia GROUP BY generando la tabla VT5.

• PASO 10:– TOP: El Filtro HAVING es aplicado a VT5solo los grupos para lo cual la condición

expresada en HAVING <condicion_having> es verdadera son adicionada a la tabla VT6.



VT1Aplicación condicion

C.IDCONSUMIDOR C.NOMBRE C.CIUDAD O.IDORDEN O.IDCONSUMIDOR F/VAA JOSE MEDELLIN 1 AB FAA JOSE MEDELLIN 2 AB FAA JOSE MEDELLIN 3 AC FAA JOSE MEDELLIN 4 AC FAA JOSE MEDELLIN 5 AC FAA JOSE MEDELLIN 6 AD FAA JOSE MEDELLIN 7 NULL NULLAB JUAN MEDELLIN 1 AB VAB JUAN MEDELLIN 2 AB VAB JUAN MEDELLIN 3 AC FAB JUAN MEDELLIN 4 AC FAB JUAN MEDELLIN 5 AC FAB JUAN MEDELLIN 6 AD FAB JUAN MEDELLIN 7 NULL NULLAC DIANA MEDELLIN 1 AB FAC DIANA MEDELLIN 2 AB FAC DIANA MEDELLIN 3 AC VAC DIANA MEDELLIN 4 AC VAC DIANA MEDELLIN 5 AC VAC DIANA MEDELLIN 6 AD FAC DIANA MEDELLIN 7 NULL NULLAD NATALIA BOGOTA 1 AB FAD NATALIA BOGOTA 2 AB FAD NATALIA BOGOTA 3 AC FAD NATALIA BOGOTA 4 AC FAD NATALIA BOGOTA 5 AC FAD NATALIA BOGOTA 6 AD VAD NATALIA BOGOTA 7 NULL NULL

• Producto cruz o producto cartesiana entre las tablas y columna de evaluación de la condición


VT2

C.IDCONSUMIDOR C.NOMBRE C.CIUDAD O.IDORDENO.IDCONSUMIDOR

AB JUAN MEDELLIN 1 AB


AC DIANA MEDELLIN 3 AC



AD NATALIA BOGOTA 6 AD

• PASO 2: Tabla virtual después de aplicar el JOIN y la condición


VT3

C.IDCONSUMIDOR C.NOMBRE C.CIUDAD O.IDORDENO.IDCONSUMIDOR





AC DIANA MEDELLIN 5 ACAA JOSE MEDELLIN NULL NULL

• PASO 3: aplicando la operación OUTER JOIN


VT4

GRUPO C.IDCONSUMIDOR C.NOMBRE C.CIUDAD O.IDORDEN O.IDCONSUMIDOR

JUAN AB JUAN MEDELLIN 1 AB


DIANA AC DIANA MEDELLIN 3 AC



JOSE AA JOSE MEDELLIN NULL NULL

• PASO 4: aplicando el operador de agregación GROUP BY


• PASO 5: aplicando el filtro HAVING

VT5

GRUPOC.IDCONSUMIDOR C.NOMBRE C.CIUDAD O.IDORDEN

O.IDCONSUMIDOR

JUAN AB JUAN MEDELLIN 1 AB


JOSE AA JOSE MEDELLIN NULL NULL


• PASO 6: Procesando sentencia SELECT

VT6

C.NOMBRE NUMORDEN

JUAN 2

JOSE 0


• PASO 7: Aplicando la sentencia ORDER BY e imprimiendo en pantalla el resultado.

VT7

C.NOMBRE NUMORDEN

JOSE 0JUAN 2

COMANDOS SQLParametros para definir restriciones en tablashttp://www.postgresql.org/docs/8.2/static/sql-createtable.htmlCONSTRAINTUNIQUE PRIMARY KEYCHECKFOREIGN KEYParametros para definir restriciones en columnasCONSTRAINT NOT NULLNULLUNIQUEPRIMARY KEYCHECKCrear tipo de datosCREATE DOMAINhttp://www.postgresql.org/docs/8.2/static/sql-createdomain.htmlhttp://www.postgresql.org/docs/8.2/static/sql-dropdomain.html

RESUMEN COMANDOS SQL(DLL)

CREATE TABLE – define una nueva tabla.http://www.postgresql.org/docs/8.2/static/sql-createtable.htmlhttp://www.postgresql.org/docs/8.2/static/sql-createtableas.htmlDROP TABLE – remueve una tabla y el esquema.http://www.postgresql.org/docs/8.2/static/sql-droptable.htmlALTER TABLE – cambia la definicion de una tabla.http://www.postgresql.org/docs/8.2/static/sql-altertable.htmlTRUNCATE – Borra la(s) tabla(s) y la vuelve a crear sin registrar transacción.http://www.postgresql.org/docs/8.2/static/sql-truncate.htmlINSERT – crea nuevas filas en la tabla.http://www.postgresql.org/docs/8.2/static/sql-insert.htmlUPDATE – actualiza filas de una tabla.http://www.postgresql.org/docs/8.2/static/sql-update.htmlDELETE – elimina filas de una tabla.http://www.postgresql.org/docs/8.2/static/sql-delete.htmlSCHEMA– elimina filas de una tabla.http://www.postgresql.org/docs/8.2/static/sql-createschema.html

RESUMEN COMANDOS SQL(DLL)

DROP VIEW – Elimina una vista.http://www.postgresql.org/docs/8.2/interactive/sql-dropview.htmlDROP TRIGGER – Elimina la definicion de un disparador.http://www.postgresql.org/docs/8.2/static/sql-droptable.htmlALTER VIEW – cambia la definicion de la vista.http://www.postgresql.org/docs/8.2/static/sql-altertable.htmlTRUNCATE – Borra la(s) tabla(s) y la vuelve a crear sin registrar transacción.http://www.postgresql.org/docs/8.2/static/sql-truncate.htmlINSERT – crea nuevas filas en la tabla.http://www.postgresql.org/docs/8.2/static/sql-insert.htmlUPDATE – actualiza filas de una tabla.http://www.postgresql.org/docs/8.2/static/sql-update.htmlDELETE – elimina filas de una tabla.http://www.postgresql.org/docs/8.2/static/sql-delete.htmlSCHEMA– elimina filas de una tabla.http://www.postgresql.org/docs/8.2/static/sql-createschema.html

Lenguaje de definición de datos (LDD)

Sentencia de seleccion – http://www.postgresql.org/docs/8.2/static/sql-select.html

Sentencia de seleccion – http://www.postgresql.org/docs/8.2/static/sql-select.html

sql

Education