UNIDAD 2 Lenguaje de Definicin de Datos DDL

UNIDAD 2 Lenguaje de Definición de Datos (DDL)

DEFINICIÓN - SQL (Structured Query Language) – Se podría traducir como “lenguaje estructurado para consultas”. � Es el lenguaje más importante para el manejo de base de datos relacionales. � Forma parte del DML (Data Manipulation Language) y lo desarrolló IBM a principios de los 70. � Es el estándar para el “relational database management systems”(RDBMS) � SQL

DEFINICIÓN � Se compone de tres partes: ◦ Data Definition Language (DDL)– Instrucciones que crean una base de datos, tabla o índices entre otras cosas. ◦ Data Manipulation Language (DML) – Instrucciones que añaden, eliminan o modifican instancias (records) y las instrucciones que sirven para realizar consultas (queries) a la base de datos. ◦ Data Control Language (DCL) – Instrucciones relacionadas a la administración de la base de datos (cuentas, privilegios, accesos, tunning, etc. )

Historia del SQL � 1970 – E. Codd develops relational database concept � 1974 -1979 – System R with Sequel (later SQL) created at IBM Research Lab � 1979 – Oracle markets first relational DB with SQL � 1986 – ANSI SQL standard released � 1989, 1992, 1999, 2003 – Major ANSI standard updates � Hoy día – SQL se utiliza por todos los vendedores de bases de datos

Beneficios de Utilizar un Lenguaje Relacional Estandarizado � Reduce costos de entrenamiento � Mayor productividad � Portabilidad de la aplicación � Longetividad de la aplicación � Reduce la dependencia de un sólo vendedor � Comunicación que cruza sistemas

Propósito del SQL Standard �Especificar sintaxis y semántica para la definición y manipulación de los datos. �Definir estructuras de datos �Capacitar la portabilidad �Permitir crecimiento y/o mejoras a los estándares

Medio ambiente del SQL � Catalog ◦ Un conjunto de schemas que constituye la descripción de la base de datos. � Schema ◦ La estructura que contiene descripciones de objetos creados por un usuario (tablas, views y restricciones (constraints)) � Data Definition Language (DDL) ◦ Comandos que definen una base de datos, incluyendo su creación, alteración y eliminación de tablas y establecer limitaciones (constraints).

Medio ambiente del SQL �Data Manipulation Language (DML) �Data Control Language (DCL) ◦ Comandos que mantienen e interrogan una base de datos (query) ◦ Comandos que controlan la base de datos, incluyendo la administración de privilegios y asegurando (committing) data (eliminando o guardando transacciones)

Figure 7 -1 A simplified schematic of a typical SQL environment, as described by the SQL-2003 standard

Figure 7 -4 DDL, DML, DCL, and the database development process

Creando Base de Datos Se puede utilizar una instrucción CREATE DATABASE para crear una base de datos y los archivos donde se almacena. SQL Server implementa la instrucción CREATE DATABASE de la siguiente manera: � SQL Server utiliza una copia de la base de datos model para inicializar la base de datos y sus metadatos. � Se asigna un GUID de Service Broker a la base de datos. � A continuación, el Database Engine (Motor de base de datos) rellena el resto de la base de datos con páginas vacías, excepto las páginas que tengan datos internos que registren cómo se emplea el espacio en la base de datos.

Create Database CREATE DATABASE database_name [ ON [ PRIMARY ] [ <file. SPec> [ , . . . n ] [ , <filegroup> [ , . . . n ] ] [ LOG ON { <file. SPec> [ , . . . n ] } ] ] [ COLLATE collation_name ] [ WITH <external_access_option> ] ] [; ]

ESPecificaciones de Archivo: <file. SPec> : : = { ( NAME = logical_file_name , FILENAME = { 'os_file_name' | 'filestream_path' } [ , SIZE = size [ KB | MB | GB | TB ] ] [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB |%]] ) [ , . . . n ] }

ESPecificaciones de Archivo: Controla las propiedades del grupo de archivos <filegroup> : : = { FILEGROUP filegroup_name [ CONTAINS FILESTREAM ] [ DEFAULT ] <file. SPec> [ , . . . n ] } � Controla el acceso externo a la base de datos y desde ésta para la intercalación. <external_access_option> : : = { [ DB_CHAINING { ON | OFF } ] [ , TRUSTWORTHY { ON | OFF } ] } �

Crear BD adjuntando archivos existentes CREATE DATABASE database_name ON <file. SPec> [ , . . . n ] FOR { ATTACH [ WITH <service_broker_option> ] | ATTACH_REBUILD_LOG } [; ] <service_broker_option> : : = { ENABLE_BROKER | NEW_BROKER | ERROR_BROKER_CONVERSATIONS }

Creando instantáneas de una BD � Es una vista estática de sólo lectura de una base de datos existente. CREATE DATABASE database_snapshot_name ON ( NAME = logical_file_name, FILENAME = 'os_file_name' ) [ , . . . n ] AS SNAPSHOT OF source_database_name [; ]

Ejemplo 1 CREATE DATABASE ventas ON ( NAME = Ventas_dat, FILENAME = ‘c: tallerbdventas. mdf’, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = Ventas_log, FILENAME = ' c: tallerbdventas. ldf’ , SIZE = 5 MB, MAXSIZE = 25 MB, FILEGROWTH = 5 MB ); GO

Ejemplo 2: varios archivos CREATE DATABASE Archivo ON PRIMARY (NAME = Arch 1, FILENAME ='c: tallerbdarchdat 1. mdf', SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch 2, FILENAME = 'c: tallerbdarchdat 2. ndf', SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch 3, FILENAME = 'c: tallerbdarchdat 3. ndf', SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON (NAME = Archlog 1, FILENAME = 'c: tallerbdarchlog 1. ldf', SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20), (NAME = Archlog 2, FILENAME = 'c: tallerbdarchlog 2. ldf', SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20) GO

Ejemplo 3: grupos de archivos CREATE DATABASE Ventas ON PRIMARY ( NAME = VPri 1_dat, FILENAME = 'c: tallerbdVPri 1 dat. mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), ( NAME = VPri 2_dat, FILENAME = 'c: tallerbdVPri 2 dt. ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), FILEGROUP Venta. Grupo 1 ( NAME = VGrp 1 Fi 1_dat, FILENAME = 'c: tallerbdVG 1 Fi 1 dt. ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), ( NAME = VGrp 1 Fi 2_dat, FILENAME = 'c: tallerbdVG 1 Fi 2 dt. ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), FILEGROUP Venta. Grupo 2 ( NAME = VGrp 2 Fi 1_dat, FILENAME = 'c: tallerbdVG 2 Fi 1 dt. ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), ( NAME = VGrp 2 Fi 2_dat, FILENAME = 'c: tallerbdVG 2 Fi 2 dt. ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = Ventas_log, FILENAME = 'c: tallerbdventalog. ldf', SIZE = 5 MB, MAXSIZE = 25 MB, FILEGROWTH = 5 MB ) GO

Ejemplo 4: Adjuntar una base de datos � Primero se separa la BD: SP_detach_db Archive; GO � Ahora se adjuntan los archivos: CREATE DATABASE Archive ON (FILENAME = 'c: tallerbdarchdat 1. mdf') FOR ATTACH GO

Ejemplo 5: crear Instantánea de BD CREATE DATABASE ventas_snapshot 0600 ON ( NAME = VPri 1_dat, FILENAME = ‘c: tallerbdVPri 1 dat_0600. ss'), ( NAME = VPri 2_dat, FILENAME = ‘c: tallerbdVPri 2 dt_0600. ss'), ( NAME = VGrp 1 Fi 1_dat, FILENAME = 'c: tallerbdVG 1 Fi 1 dt_0600. ss'), ( NAME = VGrp 1 Fi 2_dat, FILENAME = 'c: tallerbdVG 1 Fi 2 dt_0600. ss'), ( NAME = VGrp 2 Fi 1_dat, FILENAME = 'c: tallerbdVG 2 Fi 1 dt_0600. ss'), ( NAME = VGrp 2 Fi 2_dat, FILENAME = 'c: tallerbdVG 2 Fi 2 dt_0600. ss') AS SNAPSHOT OF ventas GO

Ejemplo 6: especificando un nombre de intercalación y sus opciones CREATE DATABASE Prueba COLLATE French_CI_AI WITH TRUSTWORTHY ON, DB_CHAINING ON; GO Verificando configuración: SELECT name, collation_name, is_trustworthy_on, is_db_chaining_on FROM sys. databases WHERE name = N‘Prueba'; GO

Ejemplo 7: especificando un grupo de archivos de filas y dos grupos de archivos FILESTREAM CREATE DATABASE File. Stream. DB ON PRIMARY ( NAME = File. Stream. DB_data, FILENAME='c: MSQLFile. Stream. DB_data. mdf’ , SIZE = 10 MB, MAXSIZE = 50 MB, FILEGROWTH = 15%), FILEGROUP File. Stream. Photos CONTAINS FILESTREAM DEFAULT (NAME = FSPhotos, FILENAME = 'C: My. FSfolderPhotos‘), -- No se especifica SIZE, MAXSIZE, FILEGROWTH FILEGROUP File. Stream. Resumes CONTAINS FILESTREAM ( NAME = File. Stream. Resumes, FILENAME = 'C: My. FSfolderResumes') LOG ON (NAME = File. Stream_log, FILENAME = ' c: MSQL File. Stream. DB_log. ldf', SIZE = 5 MB, MAXSIZE = 25 MB, FILEGROWTH = 5 MB) GO

Alter Database ALTER DATABASE database_name { | MODIFY NAME = new_database_name | COLLATE collation_name | <file_and_filegroup_options> | <set_database_options> } [; ]

Especificaciones generales <file_and_filegroup_options >: : = <add_or_modify_files>: : = <filespec>: : = <add_or_modify_filegroups> : : = <filegroup_updatability_option >: : = <set_database_options>: : = <optionspec>: : = <auto_option> : : = <change_tracking_option> : : = <cursor_option> : : = <database_mirroring_option> : : = <date_correlation_optimizati on_option> : : = <db_encryption_option> : : = <db_state_option> : : = <db_update_option> : : = <db_user_access_option> : : = <external_access_option> : : = <parameterization_option> : : = <recovery_option> : : = <service_broker_option> : : = <snapshot_option> : : = <sql_option> : : = <termination> : : =

Especificaciones de archivo: ALTER DATABASE database_name { <add_or_modify_files> | <add_or_modify_filegroups> } [; ] <add_or_modify_files>: : = { ADD FILE <filespec> [ , . . . n ] [ TO FILEGROUP { filegroup_name } ] | ADD LOG FILE <filespec> [ , . . . n ] | REMOVE FILE logical_file_name | MODIFY FILE <filespec> }

Especificaciones de archivo: <filespec>: : = ( NAME = logical_file_name [ , NEWNAME = new_logical_name ] [ , FILENAME = {'os_file_name' | 'filestream_path' } ] [ , SIZE = size [ KB | MB | GB | TB ] ] [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] [ , OFFLINE ] )

Especificaciones de Archivo <add_or_modify_filegroups>: : = { | ADD FILEGROUP filegroup_name [ CONTAINS FILESTREAM ] | REMOVE FILEGROUP filegroup_name | MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME = new_filegroup_name } } <filegroup_updatability_option>: : = { { READONLY | READWRITE } | { READ_ONLY | READ_WRITE } }

Ejemplo 8: Agregar un archivo a una base de datos ALTER DATABASE Produccion ADD FILE ( NAME = Test 1 dat 2, FILENAME = ‘c: tallerbdt 1 dat 2. ndf', SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB )

Ejemplo 9: Agregar a una base de datos un grupo de archivos con dos archivos ALTER DATABASE nomina ADD FILEGROUP Test 1 FG 1; GO ALTER DATABASE nomina ADD FILE ( NAME = test 1 dat 3, FILENAME = ‘c: bdt 1 dat 3. ndf', SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB ), ( NAME = test 1 dat 4, FILENAME = ‘c: bdt 1 dat 4. ndf', SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB ) TO FILEGROUP Test 1 FG 1

Ejemplo 10: Agregar dos archivos de registro a una base de datos ALTER DATABASE nomina ADD LOG FILE ( NAME = test 1 log 2, FILENAME = ‘c: tallerbdtest 2 log. ldf’, SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB ), ( NAME = test 1 log 3, FILENAME = 'c: tallerbdtest 3 log. ldf’, SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB ) GO

Ejemplo 11: Quitar y modificar archivos Quitar un archivo: ALTER DATABASE nomina REMOVE FILE test 1 dat 4; GO Modifica Tamaño: ALTER DATABASE nomina MODIFY FILE (NAME = test 1 dat 3, SIZE = 20 MB); GO

Ejemplo 12: Mover un archivo a otra ubicación Debe mover físicamente el archivo al directorio nuevo antes de ejecutar este ejemplo. A continuación, detenga e inicie la instancia de SQL Server o establezca la base de datos nomina en OFFLINE y después en ONLINE para implementar el cambio. ALTER DATABASE nomina MODIFY FILE ( NAME = Test 1 dat 2, FILENAME = 'c: t 1 dat 2. ndf' ); GO

Ejemplo 13: Establecer un grupo de archivos como predeterminado ALTER DATABASE nomina MODIFY FILEGROUP Test 1 FG 1 DEFAULT; GO ALTER DATABASE nomina MODIFY FILEGROUP [PRIMARY] DEFAULT; GO

Ejemplo 14: Agregar un grupo de archivos mediante ALTER DATABASE ALTER database File. Stream. Photo. DB ADD FILEGROUP Todays. Photo. Shoot CONTAINS FILESTREAM GO ALTER database File. Stream. Photo. DB ADD FILE ( NAME= 'Photo. Shoot 1', FILENAME = 'C: UsersAdministratorPicturesTodays. Photo. Shoot. ndf' ) TO FILEGROUP Todays. Photo. Shoot GO

DROP DATABASE { database_name | database_snapshot_name } [ , . . . n ] [; ] Para utilizar DROP DATABASE, el contexto de base de datos de la conexión no puede ser el mismo que el de la base de datos o instantánea de la base de datos que se va a quitar.

Ejemplo 15: Quitando database � DROP DATABASE Nomina; � DROP DATABASE Nomina 1, Nomina 2; � DROP DATABASE sales_snapshot 0600;

CREATE TABLE Creando Tablas CREATE TABLE [ database_name. [ schema_name ]. | schema_name. ] table_name ( { <column_definition> | <computed_column_definition> | <column_set_definition> } [ <table_constraint> ] [ , . . . n ] ) [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] [ { TEXTIMAGE_ON { filegroup | "default" } ] [ FILESTREAM_ON { partition_scheme_name | filegroup | "default" } ] [ WITH ( <table_option> [ , . . . n ] ) ] [ ; ]

Especificaciones de Columna <column_definition> : : = column_name <data_type> [ FILESTREAM ] [ COLLATE collation_name ] [ NULL | NOT NULL ] [ [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] | [ IDENTITY [ ( seed , increment ) ] [ NOT FOR REPLICATION ] ] [ ROWGUIDCOL ] [ <column_constraint> [. . . n ] ] [ SPARSE ]

Especificaciones de tipo <data type> : : = [ type_schema_name. ] type_name [ ( precision [ , scale ] | max | [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]

Especificaciones de restricción <column_constraint> : : = [ CONSTRAINT constraint_name ] { { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor| WITH ( < index_option > [ , . . . n ] )] [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] | [ FOREIGN KEY ] REFERENCES [ schema_name. ] referenced_table_name [ ( ref_column ) ] [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) }

Especificaciones para columnas calculadas <computed_column_definition> : : = column_name AS computed_column_expression [ PERSISTED [ NOT NULL ] ] [ [ CONSTRAINT constraint_name ] { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor | WITH ( <index_option> [ , . . . n ] ) ] | [ FOREIGN KEY ] REFERENCES referenced_table_name [ ( ref_column ) ] [ ON DELETE { NO ACTION | CASCADE } ] [ ON UPDATE { NO ACTION } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] ]

Restricciones de Tabla < table_constraint > : : = [ CONSTRAINT constraint_name ] { { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ , . . . n ] ) [ WITH FILLFACTOR = fillfactor |WITH ( <index_option> [ , . . . n ] ) ] [ ON { partition_scheme_name (partition_column_name) | filegroup | "default" } ] | FOREIGN KEY ( column [ , . . . n ] ) REFERENCES referenced_table_name [ ( ref_column [ , . . . n ] ) ] [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) } <table_option> : : = { DATA_COMPRESSION = { NONE | ROW | PAGE } [ ON PARTITIONS ( { <partition_number_expression> | <range> } [ , . . . n ] ) ] }

Opciones de Index <index_option> : : = { PAD_INDEX = { ON | OFF } | FILLFACTOR = fillfactor | IGNORE_DUP_KEY = { ON | OFF } | STATISTICS_NORECOMPUTE = { ON | OFF } | ALLOW_ROW_LOCKS = { ON | OFF} | ALLOW_PAGE_LOCKS ={ ON | OFF} | DATA_COMPRESSION = { NONE | ROW | PAGE } [ ON PARTITIONS ( { <partition_number_expression> | <range> } [ , . . . n ] ) ] } <range> : : = <partition_number_expression> TO <partition_number_expression>

Ejemplos: