Transcript UNIDAD 2

Lenguaje de Definición de Datos (DDL)




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)

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.)






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






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
 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



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).

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



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 database_name
[ ON
[ PRIMARY ] [ <fileSPec> [ ,...n ]
[ , <filegroup> [ ,...n ] ]
[ LOG ON { <fileSPec> [ ,...n ] } ]
]
[ COLLATE collation_name ]
[ WITH <external_access_option> ]
]
[;]
<fileSPec> ::=
{
(
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 ]
}
Controla las propiedades del grupo de archivos
<filegroup> ::=
{
FILEGROUP filegroup_name [ CONTAINS FILESTREAM ] [
DEFAULT ]
<fileSPec> [ ,...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 } ]
}

CREATE DATABASE database_name
ON <fileSPec> [ ,...n ]
FOR { ATTACH [ WITH
<service_broker_option> ]
| ATTACH_REBUILD_LOG }
[;]
<service_broker_option> ::=
{
ENABLE_BROKER
| NEW_BROKER
| ERROR_BROKER_CONVERSATIONS
}
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
[;]

CREATE DATABASE ventas
ON
( NAME = Ventas_dat,
FILENAME = ‘c:\tallerbd\ventas.mdf’,
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Ventas_log,
FILENAME = ' c:\tallerbd\ventas.ldf’ ,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB );
GO
CREATE DATABASE Archivo
ON
PRIMARY
(NAME = Arch1, FILENAME ='c:\tallerbd\archdat1.mdf',
SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20),
( NAME = Arch2, FILENAME = 'c:\tallerbd\archdat2.ndf',
SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20),
( NAME = Arch3, FILENAME = 'c:\tallerbd\archdat3.ndf',
SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20)
LOG ON
(NAME = Archlog1, FILENAME = 'c:\tallerbd\archlog1.ldf',
SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20),
(NAME = Archlog2, FILENAME = 'c:\tallerbd\archlog2.ldf',
SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20)
GO
CREATE DATABASE Ventas
ON PRIMARY
( NAME = VPri1_dat, FILENAME = 'c:\tallerbd\VPri1dat.mdf', SIZE = 10,
MAXSIZE = 50, FILEGROWTH = 15% ),
( NAME = VPri2_dat, FILENAME = 'c:\tallerbd\VPri2dt.ndf', SIZE = 10,
MAXSIZE = 50, FILEGROWTH = 15% ),
FILEGROUP VentaGrupo1
( NAME = VGrp1Fi1_dat, FILENAME = 'c:\tallerbd\VG1Fi1dt.ndf', SIZE = 10,
MAXSIZE = 50, FILEGROWTH = 5 ),
( NAME = VGrp1Fi2_dat, FILENAME = 'c:\tallerbd\VG1Fi2dt.ndf', SIZE = 10,
MAXSIZE = 50, FILEGROWTH = 5 ),
FILEGROUP VentaGrupo2
( NAME = VGrp2Fi1_dat, FILENAME = 'c:\tallerbd\VG2Fi1dt.ndf', SIZE = 10,
MAXSIZE = 50, FILEGROWTH = 5 ),
( NAME = VGrp2Fi2_dat, FILENAME = 'c:\tallerbd\VG2Fi2dt.ndf', SIZE = 10,
MAXSIZE = 50, FILEGROWTH = 5 )
LOG ON
( NAME = Ventas_log,FILENAME = 'c:\tallerbd\ventalog.ldf', SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
Primero se separa la BD:
SP_detach_db Archive;
GO
 Ahora se adjuntan los archivos:
CREATE DATABASE Archive
ON (FILENAME = 'c:\tallerbd\archdat1.mdf')
FOR ATTACH
GO

CREATE DATABASE ventas_snapshot0600 ON
( NAME = VPri1_dat,
FILENAME = ‘c:\tallerbd\VPri1dat_0600.ss'),
( NAME = VPri2_dat,
FILENAME = ‘c:\tallerbd\VPri2dt_0600.ss'),
( NAME = VGrp1Fi1_dat, FILENAME =
'c:\tallerbd\VG1Fi1dt_0600.ss'),
( NAME = VGrp1Fi2_dat, FILENAME =
'c:\tallerbd\VG1Fi2dt_0600.ss'),
( NAME = VGrp2Fi1_dat, FILENAME =
'c:\tallerbd\VG2Fi1dt_0600.ss'),
( NAME = VGrp2Fi2_dat, FILENAME =
'c:\tallerbd\VG2Fi2dt_0600.ss')
AS SNAPSHOT OF ventas
GO
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
CREATE DATABASE FileStreamDB
ON PRIMARY
( NAME = FileStreamDB_data,
FILENAME='c:\MSQL\FileStreamDB_data.mdf’ ,SIZE = 10MB,
MAXSIZE = 50MB,FILEGROWTH = 15%),
FILEGROUP FileStreamPhotos CONTAINS FILESTREAM DEFAULT
(NAME = FSPhotos, FILENAME = 'C:\MyFSfolder\Photos‘),
-- No se especifica SIZE, MAXSIZE, FILEGROWTH
FILEGROUP FileStreamResumes CONTAINS FILESTREAM
( NAME = FileStreamResumes, FILENAME =
'C:\MyFSfolder\Resumes')
LOG ON
(NAME = FileStream_log,
FILENAME = ' c:\MSQL\ FileStreamDB_log.ldf',
SIZE = 5MB,MAXSIZE = 25MB,
FILEGROWTH = 5MB)
GO
ALTER DATABASE database_name
{
| MODIFY NAME = new_database_name
| COLLATE collation_name
| <file_and_filegroup_options>
| <set_database_options>
}
[;]
<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> ::=
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>
}
<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 ]
)
<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 }
}
ALTER DATABASE Produccion
ADD FILE
(
NAME = Test1dat2,
FILENAME = ‘c:\tallerbd\t1dat2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
ALTER DATABASE nomina ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE nomina
ADD FILE
(
NAME = test1dat3,
FILENAME = ‘c:\bd\t1dat3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
),
(
NAME = test1dat4,
FILENAME = ‘c:\bd\t1dat4.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1
ALTER DATABASE nomina
ADD LOG FILE
(
NAME = test1log2,
FILENAME = ‘c:\tallerbd\test2log.ldf’,
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
),
(
NAME = test1log3,
FILENAME = 'c:\tallerbd\test3log.ldf’,
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
GO
Quitar un archivo:
ALTER DATABASE nomina
REMOVE FILE test1dat4;
GO
Modifica Tamaño:
ALTER DATABASE nomina
MODIFY FILE
(NAME = test1dat3,
SIZE = 20MB);
GO
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 = Test1dat2,
FILENAME = 'c:\t1dat2.ndf'
);
GO
ALTER DATABASE nomina
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE nomina
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO
ALTER database FileStreamPhotoDB
ADD FILEGROUP TodaysPhotoShoot
CONTAINS FILESTREAM
GO
ALTER database FileStreamPhotoDB
ADD FILE
(
NAME= 'PhotoShoot1',
FILENAME =
'C:\Users\Administrator\Pictures\TodaysPhotoShoot.ndf'
)
TO FILEGROUP TodaysPhotoShoot
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.

DROP DATABASE Nomina;

DROP DATABASE Nomina1, Nomina2;

DROP DATABASE sales_snapshot0600;
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 ] ) ] [ ; ]
<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 ]
<data type> ::= [ type_schema_name . ]
type_name [ ( precision [ , scale ] | max |
[ { CONTENT | DOCUMENT } ]
xml_schema_collection ) ]
<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 ) }
<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" } ] ]
< 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 ] ) ] }
<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>