Tema 5.diseño Físico Bd Sql(ddl) 6y615

TEMA 5. DISEÑO FÍSICO DE BASES DE DATOS:SQL(DDL) 1.

INTRODUCCION AL SQL

2.

INTERPRETES DE COMANDOS DE LOS SGBD: CLIENTES MYSQL

3.

CONJUNTOS DE CARACTERES

4.

ALGUNOS COMANDOS UTILES: Show, describe, etc..

5.

DDL 1.

CREACIÓN, MODIFICACIÓN Y BORRADO DE BASES DE DATOS

2.

CREACIÓN DE TABLAS 1.

TIPOS DE DATOS

2.

CARACTERISTICAS DE LA CREACIÓN DE TABLAS

3.

MODIFICACIÓN, BORRADO Y RENOMBRADO DE TABLAS

4.

CREACIÓN DE INDICES

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

1

1. INTRODUCCIÓN AL SQL Š Lenguaje de los SGBD relacionales. Š SQL (Structured Query Language) Š Lenguaje declarativo, se incide en el qué hay que hacer y no en el cómo hay que hacerlo. Š IBM en los años 70 funda el lenguaje SEQUEL precursor del SQL. Š ORACLE en 1979 presenta su primera versión del SQL, más tarde se convierte en un estándar avalado por ISO y ANSI. Š En 1989 aparece el estándar ISO llamado SQL89 o SQL1. Š El ultimo estándar es el del 2008 SQL2008 GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

2

Introducción al Sql Š El nombre no refleja todas las posibilidades del lenguaje (Query) Š Aunque existe una estandarización, cada SGBD tiene sus propias sentencias SQL. Š Donde se puede utilizar: „

„

Directamente en aplicaciones (clientes) que permitan su introducción. Autocontenido Embebido en los lenguajes de programación. Sql Huesped

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

3

Introducción al Sql Š Subconjuntos del SQL: „

„

„

„

„

DQL (Data Query Language).- La orden que se emplea es la SELECT. DML (Data Manipulation Language).- Entre sus ordenes destacan INSERT, DELETE y UPDATE. DDL (Data Definition Language).- Las ordenes que se utilizan son CREATE, DROP, CHECK, CONSTRAINT DCL (Data Control Language).- Las ordenes usadas son: GRANT y REVOKE. TPL (Transaction Processing Language).- Se emplea como lenguaje de control del DML. Las ordenes que se emplean son: START TRANSACTION, BEGIN TRANSACTION, etc..

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

4

2. INTERPRETES DE COMANDOS DE LOS SGBD Š Aplicación Cliente cuya misión es enviar comandos al SGBD Š Permiten ejecutar cualquier tipo de sentencia SQL. Š Ocupan muy poco espacio y sin requerimientos específicos del SO. Š En Mysql es el comando mysql Š En Oracle el comando sqlplus GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

5

Interprete de Comandos: Mysql Š Documentación Oficial Š La Sintaxis del comando: mysql [options] [database]

Š Ejecutar sentencias SQL en mysql, escribir la sentencia acabada con ; y pulsar <Enter> Š Ejecución de scripts SQL, „

Dentro de mysql, sintaxis: source

„

En modo batch, usando las redirecciones: z

mysql …. < fichero.sql

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

6

Interprete de Comandos: Mysql Š Desde el prompt del cliente de consola se pueden ejecutar una serie de ordenes o comandos, para visualizar todas las ordenes disponibles ejecutar ? Š Algunas de estas ordenes son: „ „ „ „

ego o \G.- Muestra los datos verticalmente Prompt o \R.- Para cambiar el prompt Status o \s.- Obtener información de estado del servidor. Clear o \c.- Limpia la pantalla.

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

7

3. CONJUNTOS DE CARACTERES Š ¿Qué son los Conjuntos de Caracteres? Š Conjuntos de caracteres más habituales en España: „

„

ASCII (American Standard Code for Information Interchange). – Surge en los años 60 para los teletipos. Creada por el ANSI. Utiliza 7 bits para representar los caracteres más habituales (la ñ no). Actualizado por última vez en 1986. No confundir con ASCII extendido (son cjtos. de caracteres de 8 bits). Latin1 o ISO_8859-1(8bits).- Norma elaborada por la ISO para codificar los caracteres del alfabeto latino (ñ incluida). Típico de sistemas Unix. Los 128 primeros caracteres (7bits) coinciden con el ASCII, por eso se le considera una codificación ASCII Extendido, el resto de caracteres para codificar otros caracteres.

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

8

3. CONJUNTOS DE CARACTERES Š Conjuntos de caracteres más habituales: „

„

850 (Code Page 850).- Cjto. de caracteres de Ms-dos, no estándar. Ascii extendido que incluye caracteres como la ñ. Usado por defecto en la consola de windows. Se puede modificar de forma temporal con el comando ch, y de forma permanente HKEY_CURRENT_>Software->Microsoft->Command Processor y crear un valor de cadena con el Nombre AutoRun y el valor ch codigodepagina. Windows-1252.- Codificación por defecto en los sistemas Microsoft (Mal llamado codificación ANSI, puesto que no es un estándar) que se ajusta casi al ISO_8859_1 o Latin1 (más en concreto al ISO_8859_15). Los ficheros sin formato en Windows se guardan con esta codificación (en España).

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

9

3. CONJUNTOS DE CARACTERES Š Variables en Mysql para definir el cjto. de caracteres: „

„

„

„

„

„

character_set_client.- Esta variable indica en que juego de caracteres le serán enviados los datos del cliente al servidor. character_set_connection.- Sirve para indicar la conversión que realizará el servidor de lo que recibe del cliente, es decir, el servidor convierte los datos que recibe del cliente a este cjto. de caracteres. character_set_results.- indica el conjunto de caracteres usado por el servidor para devolver los resultados de las consultas al cliente. character_set_database.- Será el conjunto de caracteres con el que se almacenarán los datos (y estructuras) en la base de datos. character_set_server.- Sirve para indicar el cjto. de caracteres por defecto que se utilizarán en las bases de datos. character_set_filesystem.- Se usa para interpretar los strings que se refieren a nombres de ficheros en ordenes como LOAD DATA INFILE o SELECT ... INTO OUTFILE.

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

10

3. CONJUNTOS DE CARACTERES Š Modificar las variables que afectan al conjunto de caracteres: „

De forma temporal: z z

„

Comando Set =valor.Comando Chartset, set names, set character set

De forma permanente: z z

En fichero de configuración. Cuando arrancamos el sistema gestor.

Š Visualizar variables y conjuntos de caracteres „ „

Show character_set Show variables like ‘%character%’;

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

11

3. CONJUNTOS DE CARACTERES Š Por defecto la consola Windows (cmd.exe,) usa el conjunto de caracteres 850, por lo que si se pretenden enviar comandos con datos con acentos, ñ, etc. (character_set_client, character_set_connection) o recibir datos con acentos, etc.. (character_set_result) será necesario establecer estas variables con el conjunto de caracteres a 850 (charset 850, charset es lo mismo que character set) Š Si se lanza un fichero, normalmente estará codificado en ISO_8859-1, por lo que habrá que cambiar el conjunto de caracteres para las conexiones a la codificación del fichero. Š PRUEBAS GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

12

4. COMANDOS SQL ÚTILES Š Comandos SHOW „ „

Show databases Show tables

Š Comando describe Š Comando use

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

13

5. INTRODUCCIÓN AL DDL Š Las funciones de este sublenguaje son: „

„

Crear, modificar y borrar los elementos de la BD: tablas, índices y otros objetos de las BD (vistas, sinónimos, etc..) Definir las estructuras físicas donde se almacenarán los objetos de las BD (tablespaces, datafiles, etc..)

Š El DDL tiene tres instrucciones básicas: „ „ „

CREATE tipo_objeto Nombre Definición DROP tipo_objeto Nombre ALTER tipo_objeto Nombre Modificación.

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

14

DDL: Creación de una BD Š En MySql, documentación oficial: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] Nombre_BD [especificación_create [,especificación_create] …] especificación_create: [DEFAULT] CHARACTER SET juego_caracteres | [DEFAULT] COLLATE nombre_colación

Š Database y Schema son lo mismo

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

15

DDL: Creación de una BD Š Character Set juego de caracteres de la base de datos, para visualizar los disponibles comando: show character set; Š Collate especifica cómo tratar el alfabeto, como se ordena (la ñ despues de la m) y como se comparan los caracteres (p.ej.: Á es igual á), comando: show collate para visualizar los disponibles. Š Para usar una base de datos: Use Nombre_BD;

Š Mostrar las bases de datos existentes: Show databases;

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

16

DDL: Modificación y borrado de una BD Š Modificación en MySql: ALTER {DATABASE | SCHEMA} [db_name] alter_specification [, alter_specification] ... alter_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

Š Borrado en Mysql: DROP {DATABASE | SCHEMA } db_name

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

17

DDL: Creación de tablas Š En MySql, documentación oficial: CREATE [TEMPORARY] TABLE [If Not Exists] [esquema.]nombre_tabla [(definición_create, …)] [opciones_tabla] definición_create: definición_columna | [CONSTRAINT [símbolo]] PRIMARY KEY (nombre_columna, …) | [CONSTRAINT [símbolo]] FOREIGN KEY (nombre_columna, …) [definición_referencia]

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

18

DDL: Creación de tablas Š (Cont.): definición_columna: nombre_columna tipo_datos [NOT NULL | NULL] [DEFAULT valor] [UNIKE [KEY] | [PRIMARY] KEY] [definición_referencia] Definición_referencia: REFERENCES nombre_tabla [(nombre_columna, …)] [ON DELETE {CASCADE | SET NULL | NO ACTION}] [ON UPDATE {CASCADE | SET NULL | NO ACTION}]

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

19

DDL: Tipos de Datos Š En Mysql: Documentación Oficial

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

20

DDL: Tipos de Datos Š Para visualizar las tablas de una base de datos: show tables Š Comando para visualizar la estructura de una tabla: describe nombre_tabla Š Para mostrar la sentencia con la que se creo una tabla show create table

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

21

DDL: Restricciones a nivel de columna Š [NOT NULL | NULL].- Dar opción a incluir o no valores nulos en columnas, la opción por defecto es NULL. Š DEFAULT.- Valor por defecto de una columna Š AUTO_INCREMENT.- Datos se incrementan de forma automática sino damos valor para ese campo, es necesario que la columna sea de tipo entero. Š PRIMARY KEY o KEY.- Definir clave primaria, implícitamente lleva creación de índice y restricción NOT NULL. Š UNIQUE.- Valores únicos en los registros, por defecto ite NULL. Se pueden definir más de una columna UNIQUE a diferencia de PRIMARY. Lleva implícito la creación de un índice. Š REFERENCES.- En mysql no se tiene en cuenta a nivel de columna.

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

22

DDL: Restricciones a nivel de tabla Š PRIMARY KEY.- Restricción de clave primaria, normalmente se usa para definir que la clave primaria de la tabla esta formada por más de un campo. Š KEY e INDEX.- Significan lo mismo, KEY se añadió por compatibilidad con otros SGBD. Permiten definir uno o varios campos como indexados-> optimizan el a los datos en consultas. El index_type es el tipo de índice creado (BTREE solamente). Un índice FULLTEXT sirve para campos de tipo CHAR o VARCHAR, y SPATIAL para campos espaciales (más información) Š UNIQUE.- Lo mismo, pero se puede especificar para varias columnas. GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

23

DDL: Restricciones a nivel de tabla Š FOREIGN KEY.- Clave foránea. Cuando se definen claves foráneas en las tablas de una base de datos es necesario que la tabla referenciada exista, se pueden seguir tres estrategias: „ „

„

1ª Definir las tablas en el orden correcto 2ª No definir las claves foráneas y posteriormente modificar las tablas añadiendo las restricciones de clave foránea. 3ª Deshabilitar el uso de claves foráneas a través de la variable foreign_key_checks, en caso de relaciones reflexivas esta estrategia será obligada.

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

24

DDL: Características creación de tablas Š Opciones en la creación de tablas: „

opciones_tabla: ENGINE=nombre_motor | AUTO_INCREMENT= valor | [DEFAULT] CHARACTER SET juego_car [COLLATE colación] | COMMENT = ‘string’ | MAX_ROWS= valor | MIN_ROWS= valor

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

25

DDL: Modificación de tablas Š En Mysql, documentación oficial: ALTER TABLE nombre_tabla especificación_alter [, especificación_alter] … especificación_alter: ADD definición_columna [FIRST | AFTER nombre_columna] | ADD (definición_columna) | ADD [CONSTRAINT [simbolo]] PRIMARY KEY (nombre_columna, …) | ADD [CONSTRAINT [simbolo]] UNIQUE (nombre_columna, …) | ADD [CONSTRAINT [simbolo]] FOREIGN KEY (nombre_columna, …) [definición_referencia] GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

26

DDL: Modificación de tablas especificación_alter(cont.): | CHANGE [COLUMN] anterior_nombre_columna definición_columna [FIRST | AFTER nombre_columna] En mysql | RENAME COLUMN anterior_nombre_columna TO nuevo_nombre_columna Esto es solo en Oracle | MODIFY definición_columna [FIRST | AFTER nombre_columna] | DROP COLUMN nombre_columna | DROP PRIMARY KEY | DROP FOREIGN KEY fk_simbolo | opciones_tabla

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

27

DDL: Borrado y Renombrado de Tablas Š Borrado: DROP [TEMPORARY] TABLE nombre_tabla;

Š Renombrado: RENAME TABLE nombre_tabla TO nuevo_nombre_tabla

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

28

DDL: Creación de Índices Š Permiten optimizar el a datos en consultas. Documentación oficial Š Para ver los índices de una tabla: SHOW INDEX FROM nombre_tabla Š Sintaxis para la creación de índices: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,...) index_col_name: col_name [(length)] [ASC | DESC] GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

29

DDL: CONSULTA DE TABLAS Š El comando SHOW (ver 5.6) permite mostrar todas las definiciones de las tablas creadas: „

SHOW

GBD -1º A.S.I.R.

I.E.S. JULIAN MARIAS

30