Transcript Clase VII - Area para alumnos registrados

Clase VII
DelioAlanis
Agenda


Bases de Datos Relacionales
SQL Server 2005









Instalación
Computer Manager
SQL Server Management Studio
Object Explorer
Project Management
Query Editor
Assisted Editors
SQLiMail
Sentencias Transact-SQL
Agenda


Bases de Datos Relacionales
SQL Server 2005









Instalación
Computer Manager
SQL Server Management Studio
Object Explorer
Project Management
Query Editor
Assisted Editors
SQLiMail
Sentencias Transact-SQL
Base de Datos Relacionales

Se organizan por Tablas


Bases de Datos Relacionales




Contiene Filas y Columnas
Una columna representa un “campo” en la tabla
Los valores de una columna tienen un “tipo de dato”
definido
Las tablas pueden contener Claves Primarias
(Primary Key) y/o Claves Foraneas
Utilizando Claves entre tablas obtenemos una
integridad referencial entre ellas
Utilización de Indices mejora el rendimiento en la
búsqueda
Ejemplo a continuación…
Bases de Datos Relacionales
Base de Datos Relacionales
Base de Datos Relacionales

Tipos de Relaciones




Integridad Referencial
Bases de Datos Relacionales




Uno a uno
Uno a varios
Varios a varios
Es un sistema de reglas
Mantiene la consistencia de los datos entre tablas
relacionadas
Actualizar y/o eliminar registros en cascada
Indices



Estructura de datos que permite recuperar filas rápidamente
Proporciona ordenación
Se define sobre una columna o varias o conjunto de
columnas
Agenda


Bases de Datos Relacionales
SQL Server 2005









Instalación
Computer Manager
SQL Server Management Studio
Object Explorer
Project Management
Query Editor
Assisted Editors
SQLiMail
Sentencias Transact-SQL
SQLServer 2005
SQLServer 2005
Instalación

Fácil de usar: Instalación basada en Windows Installer

Inspector de configuración del sistema para verificar la
máquina destino

Utilidades Command-line
para ejecución desatendida
e instalación remota

Permite instalar o
desinstalar componentes
SQL Server 2005 desde
Add/Remove.

Logging de error mejorado y
reporte de fallas
SQLServer 2005
Computer Manager

SQLServer 2005


Computer Manager combina la funcionalidad de las siguientes
herramientas SQL Server en la versión anterior:

Server Network Utility

Client Network Utility

Server Manager
Los siguientes servicios son administrados por el “Computer Manager” :

SQL Server

SQL Server Agent

SQL Server Analysis Services

Report Server

Microsoft Search

Distributed Transaction Coordinator (DTC)

Full Text Search
El computer manager usa el WMI para cambiar los seteos del server y es
parte del Microsoft Management Console (MMC)
SQLServer 2005
SQLServer 2005
Computer Manager
SQLServer 2005
SQL Server Management Studio
Provee un entorno centralizado integrado para la
gestión de múltiples servers, tareas y servicios

Las características incluyen:
SQLServer 2005


Explorador de Objetos

Editores Asistidos

Ventana de resumen

Extensiones para Editor de Consultas de SQLCMD

Explorador de Templates

Manejo de errores mejorado

Administración de Proyectos

Dialog Boxes no modales

Scripting y Schedulling mejorado
SQLServer 2005
SQLServer 2005
SQL Server Management Studio
SQLServer 2005
Object Explorer (Explorador de Objetos)
Muestra los componentes SQL en una jerarquía de árbol grafica

Permite al usuario expandir múltiples nodos del árbol
simultáneamente.

Permite al
usuario filtrar un
nodo basado en
uno o múltiples
criterios de filtro.

Provee una
interface de
usuario amigable
para la
administración
SQLServer 2005

SQLServer 2005
Project Management
(Administrador de Proyectos)
SQLServer 2005

Las funcionalidades disponibles para administrar un proyecto
son:

Organiza scripts relacionados dentro de un único proyecto

Mantiene el control de versiones sobre scripts (Si VSS esta
disponible)

Conexión almacenada y opciones resultantes con cada script
SQLServer 2005
Query Editor (Editor de consultas)

Permite la ejecución de scripts T-SQL y sentencias contra SQL Server y SQL
Server Mobile Edition,

Permite ejecución de expresiones:

multidimensionales (MDX)

data mining (DMX)

XMLA
Escritura de scripts en
modo desconectado

Ayuda dinámica

Display mejorado para
resultados XML.
SQLServer 2005



Resultados en forma
XML cuando usa la
columna XML

Funciones XML para
Transact-SQL
Legibilidad mejorada
por el uso de colorcoding
SQLServer 2005
Assisted Editors & Templates
(Editores Asistidos)
Editores asistidos para creación de objetos SQL Server con
propiedades de texto como stored procedures, triggers y
funciones

Ejemplo de cómo acceder a Editores asistidos:
SQLServer 2005


Abrir SQL Server Management Studio

Explorar Object Browser. Seleccionar SQL Instance → Databases→
<database> →Programmability→ Stored Procedures

Click derecho sobre Stored Procedures → New Stored Procedure

Esto abrirá el editor asistido con información de cabecera para crear
el stored procedure
SQLServer 2005
SQLServer 2005
Assisted Editors & Templates
SQLServer 2005
SQLiMail
Usa SMTP para enviar mail. No necesita Outlook o MAPI
extendida

Corre out-of-process desde SQL Server para confiabilidad

Provee background y entrega asíncrona para escalabilidad

Failover múltiple de cuentas SMTP y perfiles permitidos

Cluster y compatibilidad con 64-bit

Servicio esta off por default

Fuerza la seguridad para perfiles de usuario
SQLServer 2005


El tamaño y tipo de adjuntos pueden ser configurados

Logs activity para SQL Server y monitores de rendimiento
SQLServer 2005
SQL-CLR







SQLServer 2005





Beneficios del CLR
Beneficios para la Programación
Assemblies
La Nueva Arquitectura de
SQL Server 2005
Confiabilidad
Seguridad
Configuración del CLR en SQL Server 2005
Creación y distribución
Functions
Tipos de datos definidos por el usuario
Triggers
Performance Tuning
SQLServer 2005
Beneficios del CLR




SQLServer 2005

Modelo de programación mejorado
User Defined Types y Aggregates
Seguridad mejorada
Compatibilidad Cross-Language
Performance y Escalabilidad
SQLServer 2005
Beneficios para la Programación



SQLServer 2005

Modelo de programación mejorado
Soporte en Visual Studio 2005
Fácil construcción y distribución de soluciones utilizando el
entorno de Visual Studio
Mejor debugging
SQLServer 2005
Assemblies




SQLServer 2005

Assemblies archivos ejecutables que actúan como punto de
entrada para una aplicación
Contienen el código que el CLR ejecuta
Estos no son creados si no poseen un manifest asociado
(información de la metadata)
Contienen toda la información requerida acerca de tipos y
recursos que la aplicación necesita para ejecutarse
Dependencia entre assemblies
SQLServer 2005
La Nueva Arquitectura de SQL Server 2005

La Hosting layer provee coordinación para:







SQLServer 2005

Carga de los Assembly
Manejo de Memoria
Modelo de Seguridad
Confiabilidad
Threads & Fibers
Deteccion de Deadlock
Contexto de Ejecución
SQL OS




Manejador de Conexiones
Manejo de Memoria
Manejo de Thread
Servicios de Sincronización
SQL Engine
CLR
Hosting
Layer
SQL OS
Windows
SQLServer 2005
Confiabilidad
SQLServer 2005

AppDomain

AppDomain, el dominio de la aplicación, es un dominio aislado para
la ejecución.

Todos los assemblies de un usuario en particular residen en un
AppDomain

Assemblies de un usuario no pueden ser referenciados por ningun
otro usuario

AppDomain aísla completamente todos los recursos que el código
.NET desde los recursos de SQL Server
SQLServer 2005
Seguridad
La seguridad en el CLR es definida por Code Access Security
(CAS)

En CAS, los permisos son seteados en tres niveles:
SQLServer 2005


Políticas de Maquina: Son las políticas para todo el código
manejado por la maquina que ejecuta el SQL Server

Políticas de Usuario: Son las políticas para el código manejado que
sea hosteado por un proceso. En SQL Server, esta política es
especifica para la cuenta de Windows que levanta los servicios para
SQL Server

Políticas de Host : Son las políticas seteadas por el Host del CLR,
concretamente para el código manejado ejecutándose en ese
servidor
SQLServer 2005
Configuración del CLR en SQL Server 2005

SQLServer 2005

La configuración del CLR se encuentra en las opciones de
advanced server configuration
Para deshabilitar la ejecución del CLR :
sp_configure ‘show_advanced_options’, 1
GO
RECONFIGURE
GO
sp_configure ‘clr enabled’, 0
GO
RECONFIGURE
GO
SQLServer 2005
Creación y distribución
Build
SQLServer 2005
.NET database objects
written in C#,VB.NET….
SQL SERVER
Assembly
SQL Data Definition:
create assembly …
create function …
create procedure …
create trigger …
create type …
SQLServer 2005
Manejando Assemblies

Cargar un Assembly

Para cargar una dll llamada ‘sqlclr.dll’ en un assembly con permisos
‘SAFE’ :
SQLServer 2005
CREATE ASSEMBLY SQLCLR
FROM ‘C: \YukonApps\sqlclr.dll’
With PERMISSION_SET = SAFE
SQLServer 2005
SQLServer 2005
Detalles del Assembly:
Sys.assemblies
Assembly
Create
Meta Data de los Assemblies
Codigo fuente:
Sys.assembly_files
Otra informacion en la metadata
•SYS.OBJECTS
•SYS.ASSEMBLY_MODULES
•SYS.ASSEMBLY_TYPES
Referencias:
Sys.assembly_references
SQLServer 2005
Creando Stored Procedures CLR
SQLServer 2005
CREATE ASSEMBLY Module15_assembly
FROM ‘\\MachineName\Module15\bin\Debug\Module15_library.dll’
GO
CREATE PROCEDURE NewProc
@name varchar(255)
AS
EXTERNAL NAME
Module15_assembly.Class1.getSSNbasedOnName
SQLServer 2005
Functions

Tipos:


SQLServer 2005

Scalar
Table Valued
Ejemplo de creacion de una UDF
CREATE ASSEMBLY ValidateString
FROM '\\MachineName\Module15\bin\Debug\validateString.dll';
GO
CREATE FUNCTION ValidateAreaCode ()
RETURNS int
AS EXTERNAL NAME
ValidateString.StringcheckClass.validateString;
GO
SQLServer 2005
Tipos de datos definidos por el usuario



Para extender los tipos de datos escalares
Soporte a indexado, queries distribuidos, integridad referencial,
replicación y operaciones bcp sobre columnas definidas por
UDTs
Para distribuir un UDT, se deben seguir los siguientes pasos en
este orden:


SQLServer 2005

Definir el UDT como una clase de lenguaje .NET
Distribuir el assembly que referencie el objeto CLR creado
Crear el tipo que referencie al assembly registrado
CREATE ASSEMBLY GISmeasures
FROM '\\MachineName\Module15\bin\Debug\GIS.dll' ;
GO
CREATE TYPE area
EXTERNAL NAME GISmeasures.GISclass.latitudeLongitude ;
GO
SQLServer 2005
Triggers

Standard: Fuerzan reglas de negocios e integridad referencial de
datos


SQLServer 2005

Insert, Delete, Update
DDL Triggers: Permiten al usuario prevenir cambios al schema
de la base de datos o disparar algun otro cambio en el schema
changes en respuesta a alguna otra modificacion de la base de
datos
Create, Alter, Drop
SQLServer 2005
Performance - CLR Vs T-SQL

Performance



SQLServer 2005

Puede reemplazar cursores T-SQL del lado del servidor
Grandes mejoras en la performance
Para llevar adelante grandes accesos a datos o implementar un
spool de datos en estadios intermedios
Nota: Si no se usan las UDAgg, se debe considerar la escritura
de stored procedures usando código manejado para un mejor
aprovechamiento en la performance sobre T-SQL
SQLServer 2005
Performance Tuning


Utilizar la herramienta Profiler para monitorear los planes de
ejecución y el funcionamiento de SQL con los objetos .NET
Observar los siguientes contadores


SQLServer 2005

Contadores de SQL Server
Contadores de .NET CLR
Relacionar los contadores de performance con los datos del
profiler
Agenda


Bases de Datos Relacionales
SQL Server 2005









Instalación
Computer Manager
SQL Server Management Studio
Object Explorer
Project Management
Query Editor
Assisted Editors
SQLiMail
Sentencias Transact-SQL
Sentencias Transact-SQL


Transact-SQL

El Lenguaje de programación Transact-SQL
Tipos de intrucciones de Transact-SQL
Elementos de sintaxis del Transact-SQL
Transact-SQL
Sentencias Transact-SQL

Implementa el estándar ISO de nivel básico de la
especificación ANSI-SQL99

Se pueden ejecutar desde cualquier producto que
cumpla los requisitos básicos

Incluye una funcionalidad ampliada
Sentencias Transact-SQL
Transact-SQL
Tipos de intrucciones

Instrucciones del Lenguaje de definincion de datos (DDL)

Instrucciones del Lenguaje de Control de Datos (DCL)

Instrucciones del Lenguaje de tratamientos de datos (DML)
Sentencias Transact-SQL
Tipos de intrucciones
Instruciones del Lenguaje de definicion de datos

Definen los objetos de la base de datos



Transact-SQL

CREATE nombreobjeto
ALTER nombreobjeto
DROP nombreobjeto
Deben tener los permisos adecuados
Sentencias Transact-SQL
Tipos de intrucciones
Instruciones del Lenguaje de control de datos

Establecer o cambiar los permisos



Transact-SQL

GRANT
DENY
REVOKE
Deben tener los permisos adecuados
Sentencias Transact-SQL
Tipos de intrucciones
Instrucciónes del Lenguaje de Tratamiento de datos

Las instrucciones DML se utilizan para cambiar datos
o recuperar información




Transact-SQL

SELECT
INSERT
UPDATE
DELETE
Deben tener los permisos adecuados
Sentencias Transact-SQL
Elementos de la sintaxis de Transact-SQL
Directivas de proceso por lotes

GO



EXEC

Transact-SQL
Envia lotes de instrucciones de TSQL a las herramientas y
utilidades
No se trata, realmente, de una instrucción de TSQL

Ejecuta una funcion definida por el usuario, un
prodecimiento de sistema, un prodecimiento definido por el
usuario o un prodecimiento almacenado extendido
Controla la ejecución de una cadena de caracteres dentro de
un lote de TSQL
Sentencias Transact-SQL
Elementos de la sintaxis de Transact-SQL
Transact-SQL
Comentarios

Comentarios en línea

Comentarios en Bloque
Sentencias Transact-SQL
DataTypes

Números Enteros

Bigint


Int


Transact-SQL
Números enteros entre 2E15 (-32.768) y 2E-15 (32.767)
Tinyint


Números enteros entre -2E31 (-2,147,483,648 ) y 2E31(2,147,483,647 )
Smallint


Números enteros entre -2E63 (-9.223.372.036.845.775.808) y 2E63 (9.223.372.036.845.775.807)
Números enteros entre 0 y 255
Bit

Bit

Enteros entre 0 y 1
Sentencias Transact-SQL
DataTypes

Decimal y númericos

Decimal


Numeric


Función equivalente a decimal
Money & SmallMoney

Money


Transact-SQL
Dátos númericos con precisión fija de -10E38 a 10E-38
Valores monetarios entre -2E63 y 2E63-1 con precisión de hasta
10 dígitos.
SmallMoney

Valores monetarios entre -214.748,3648 y +214.748,3647
Sentencias Transact-SQL
DataTypes

Números aproximádos

Float


Real


Dátos numéricos de punto flotante entre -3.40E+38 y 3.40E+38
Datetime & Smalldatetime

Datetime

Transact-SQL
Dátos numéricos de punto flotante entre -1.79E+308 y
1.79E+308

Fechas entre 1 de Enero del 1753 y 31 de diciembre de 9999 con
una precisión de 3 milésimas de segúndo
Smalldatetime

Fechas entre 1 de Enero de 1900 y 6 de Junio de 2079, con una
precisión de 1 minúto
Sentencias Transact-SQL
DataTypes

Caracteres

Char


Varchar


Transact-SQL
Carácter de longitud fija (unicode) con un máximo de 4000 caracteres
Nvarchar


Caracteres de longitud variable (no unicode) con un máximo de
2.147.483.647 caracteres
Nchar


Caracteres de longitud variable (no unicode) con un máximo de 8000
caracteres
Text


Carácter de longitud fija (no unicode) con un máximo de 8000
caracteres
Caracteres de longitud variable (unicode) con un máximo de 8000
caracteres
Ntext

Caracteres de longitud variable (unicode) con un máximo de
1,073,741,823 caracteres
Sentencias Transact-SQL
DataTypes

Binary

Binary


Varbinary


Datos binarios de longitud variable con un máximo de 8000
bytes
Image

Transact-SQL
Datos binarios de longitud fija con un máximo de 8000 bytes
Datos binarios de lóngitud variable con un máximo de
2.147.483.647 bytes
Sentencias Transact-SQL
Operadores






Transact-SQL

Operadores Aritméticos
Operadores de Asignación
Operadores Bitwise
Operadores de Comparación
Operadores Lógicos
Operadores de Concatenación
Operadores Unarios
Sentencias Transact-SQL
Operadores Aritméticos


Realizan operaciones matemáticas entre dos
expresiones
Operadores aritméticos




Transact-SQL

+ (suma)
- (resta)
* (Muliplicación)
/ (División)
% (Módulo) , devuelve el resto Devuelve el resto entero de
una división. Por ejemplo, 12 % 5 = 2 porque el resto de 12
dividido entre 5 es 2.
Sentencias Transact-SQL
Operadores de Asignación
Transact-SQL

Operadores de Asignación
Sentencias Transact-SQL
Operadores bit a bit

Operadores bit a bit
& (operador bit a bit AND)
Realiza una operación lógica AND a nivel de bit entre dos
valores enteros.
| (operador bit a bit OR)
Realiza una operación OR lógica a nivel de bit entre dos
valores enteros dados que se convirtieron a expresiones
binarias dentro de instrucciones de Transact-SQL.
Transact-SQL
^ (operador bit a bit OR exclusivo)
Realiza una operación OR exclusivo a nivel de bit entre dos
valores enteros dados que se convierten a expresiones
binarias dentro de instrucciones de Transact-SQL.
Sentencias Transact-SQL
Operadores de comparación
Transact-SQL

Operadores de comparación
= (igual al)
> (mayor que)
< (menor que)
>= (mayor que o igual que)
<= (menor que o igual que)
<> (no es igual a)
!= (no es igual a)
!< (no es menor que)
!> (no es mayor que)
Sentencias Transact-SQL
Operadores lógicos

Transact-SQL

Comprueban la veracidad de alguna condición
Operadores Lógicos
ALL
AND
ANY
BETWEEN
EXISTS
IN
LIKE
NOT
OR
SOME
Sentencias Transact-SQL
Operadores de concatenación de cadenas

Permite concatenar cadenas de textos

Operadores de concatenación
Transact-SQL

Signo + (mas)
Sentencias Transact-SQL
Operadores unarios

Los operadores unarios realizan una operación sobre
una única expresión de cualquiera de los tipos de
datos de la categoría del tipo de datos numérico.

+ (positivo)
Es un operador unario que devuelve el valor positivo de una
expresión numérica (un operador unario).

- (negativo)
Es un operador unario que devuelve el valor negativo de una
expresión numérica (un operador unario).
Transact-SQL

~ (operador bit a bit NOT)
Realiza una operación NOT lógica a nivel de bit en un valor entero
dado que se convierte a una expresión binaria dentro de
instrucciones de Transact-SQL.
Sentencias Transact-SQL
Crear Tablas

La sintaxis para crear tablas es:
Transact-SQL
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2“ "data_type_for_column_2",... )
Sentencias Transact-SQL
Sentencia de INSERT

La sintaxis para insertar registros es:
Transact-SQL
Insert into <tablename>
(<fieldname>, <fieldname>, <fieldname>)
Values
(@fieldname, @fieldname, @fieldname);
Sentencias Transact-SQL
INSERT FROM

Transact-SQL

Ésta técnica es usada para copiar datos desde una tabla a la
tabla indicada
La sintaxis para insertar copiando registros de otra tabla es
insert new_studentdetails
select id_number, passwd from studentdetails
Sentencias Transact-SQL
SELECT

Transact-SQL
Ej:
La sentencia SELECT es usada para seleccionar registros desde
una tabla de la base de datos
SELECT <Col_1>, <Col_2>,…
FROM <Table_name>
Sentencias Transact-SQL
SELECT INTO


La sentencia SELECT INTO es usado para copiar los registros
seleccionados de una tabla e insertarla en una tabla diferente al
mismo tiempo
La sintaxis es la siguiente:
SELECT Column1, Column2, Column3,
INTO Table2 FROM Table1
Transact-SQL
SELECT *
INTO Customers_copy FROM Customers
Sentencias Transact-SQL
DISTINCT
Transact-SQL

La clausula DISTINCT es usada para devolver sólo los valores
en la expresión especificada
Sentencias Transact-SQL
WHERE

La clausula WHERE es utilizada para seleccionar datos
condicionalmente en base a criterios especificados

SELECT * FROM Customers
WHERE LastName <> 'Smith‘



Transact-SQL


SELECT * FROM Customers
WHERE DOB > '1/1/1970‘
SELECT * FROM Customers
WHERE DOB >= '1/1/1970‘
Sentencias Transact-SQL
Transact-SQL
WHERE - EJEMPLO
Sentencias Transact-SQL
Transact-SQL
WHERE con LIKE
Sentencias Transact-SQL
Funciones de Agregado

Funciones de Agregado:
Transact-SQL
AVG
COUNT
MIN
MAX
SUM
STDEV
Sentencias Transact-SQL
Transact-SQL
Funciones de Agregado - Ejemplo
Sentencias Transact-SQL
Funciones Matemáticas

Funciones Matemáticas
Transact-SQL
ABS
RAND
SIN
COS
TAN
COT
SQRT
SQUARE
POWER
CEILING
FLOOR
EXP
Sentencias Transact-SQL
Funciones de Texto
Transact-SQL

Funciones de Texto
LEFT
RIGHT
LTRIM
RTRIM
REPLACE
SUBSTRING
STUFF
REVERSE
CHAR
LEN
LOWER
UPPER
Sentencias Transact-SQL
Funciones de Fecha

Funciones de Fecha
Transact-SQL
DATEADD
DATEDIFF
GETDATE
DAY
MONTH
YEAR…
Sentencias Transact-SQL
ORDER BY

Transact-SQL

La clausula ORDER BY es usado para ordernar los registros de
una sentencia SELECT previamente indicada
La sintaxis es la siguiente:
[ ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n] ]
Sentencias Transact-SQL
UPDATE
La sentencia UPDATE es utilizada para modificar los valores de
los registros especirficando algún criterio.

La sintaxis es la siguiente:
UPDATE tablename SET (campo1 = valor1, campo2 = valor2,…) WHERE
Campo4 = Criterio1
Transact-SQL

Sentencias Transact-SQL
DELETE
Ésta sentencia elimina los registros que especifiquemos dentro
de la clausula WHERE

La sintaxis es la siguiente:
DELETE FROM tablename Campo4 = Criterio1
Transact-SQL

Sentencias Transact-SQL
STORE PROCEDURE
Transact-SQL

Un procedimiento almacenado es un grupo de instrucciones
compiladas en un único plan de ejecución

Parámetros de salida, que pueden devolver datos (por ejemplo, un
valor entero o de carácter) o una variable de cursor (los cursores
son conjuntos de resultados que pueden devolver filas de una en
una).

Códigos de retorno, que siempre son un valor entero.

Un conjunto de resultados por cada instrucción SELECT contenida
en el procedimiento almacenado o en cualquier otro procedimiento
almacenado invocado por el procedimiento almacenado original.

Un cursor global al que se puede hacer referencia desde fuera del
procedimiento almacenado.
Sentencias Transact-SQL
Transact-SQL
STORE PROCEDURE – Crear un SP
Sentencias Transact-SQL
Transact-SQL
STORE PROCEDURE – Consumir
Demo
•Crear un Base de Datos con tablas, campos y registros