Tema5-6-7 Apuntes.pdf

8/18/2019 Tema5-6-7 Apuntes.pdf

1/53

1

TEMAS 5 – 6 – 7: EL LENGUAJE SQL

1.- INTRODUCCIÓN

2.- SENTENCIAS

2.1.- TIPOS DE SENTENCIAS

3.- TIPOS DE DATOS Y OPERADORES

4.- CREAR, USAR Y ACCEDER A BBDD CON MySQL.

5.- LENGUAJE DE DEFINICIÓN DE DATOS

5.1.- TABLAS

5.2.- ÍNDICES

6.- LENGUAJE DE MANIPULACIÓN DE DATOS

6.1.- INSERT

6.2.- UPDATE

6.3.- DELETE

6.4.- SELECT

6.5.- SELECT DENTRO DE OTRAS INSTRUCCIONES

7.- LENGUAJE DE CONTROL DE DATOS

7.1.- VISTAS

7.2.- SINÓNIMOS

8.- EJERCICIOS (SQL1 – Gestion_ventas)

ANEXO (TABLAS DE TRABAJO)


2/53

2

1.- INTRODUCCIÓN.

SQL es una herramienta para organizar, gestionar y recuperar datos almacenados en unabase de datos. Es la abreviatura de Structured Query Language (Lenguaje Estructurado deConsultas) y funciona con un tipo de bases de datos específico: Las bases de datos relacionales. Esun lenguaje relacional desarrollado inicialmente por IBM y que actualmente vienen definido por el

estándar ISO/ANSI_SQL, y es utilizado por la mayor parte de los Sistemas de Gestión de Bases deDatos Relacionales (SGBDR).

SQL, además de ser una herramienta de consulta y recuperación de datos, se utiliza paracontrolar todas las funciones que suministra un Sistema Gestor de Bases de Datos Relacionales asus usuarios, incluyendo:

Definición de datos.- Permite que el usuario defina la estructura y organización de los datosalmacenados, así como las relaciones entre ellos.

Recuperación de datos.- Permite al usuario o a un programa recuperar y utilizar los datos

almacenados en una base de datos.

Manipulación de datos.- Permite al usuario o a un programa actualizar la base de datosañadiendo datos nuevos, borrando los viejos y modificando los almacenados previamente.

Control de acceso.- Puede ser utilizado para restringir la capacidad de un usuario pararecuperar, añadir y modificar datos, protegiendo los datos almacenados contra accesos noautorizados .

Compartición de información.- Se utiliza para coordinar la compartición de datos entreusuarios concurrentes, asegurando que no haya interferencias entre ellos.

Integridad de los datos.- Define restricciones de integridad en la base de datos,protegiéndola de alteraciones debidas a actualizaciones inconsistentes o fallos del sistema

SQL se puede utilizar de forma interactiva, mediante alguna de las herramientas delSGBDR, como mysql.exe, SQL*Plus, ORACLE, DB2I ..., o bien, embebido en un lenguaje deprogramación anfitrión, como COBOL, C, PASCAL, PL/I, FORTRAN, ADA ...

2.- SENTENCIAS.

SQL es el lenguaje que se utiliza para pedir al SGBD que realice las operaciones deseadassobre las tablas. Las sentencias de este lenguaje contienen los siguientes componentes:

1.- Palabras predefinidas o claves.- Palabras que tienen un significado predefinido en ellenguaje SQL (SELECT, WHERE, INTO, etc.) suelen aparecer en mayúsculas.

2.- Nombres de tablas (Relaciones) y columnas (atributos).3.- Constantes.- También llamadas literales, son secuencias de caracteres que representan un

valor determinado. Si no es un número, debe ir entre apóstrofes (‘).4.- Signos delimitadores.- Signos especiales que aparecen en las sentencias fuera de otros

elementos. Sirven para delimitar dichos elementos cuando no van entre comillas o apóstrofes. El

más utilizado es el espacio en blanco, pero también actúan como delimitadores paréntesis, comas,signos de operaciones aritméticas o de comparación.


3/53

3

Las palabras predefinidas o claves y los nombres de tablas y columnas pueden escribirsetanto en mayúsculas como en minúsculas o en una mezcla de ambas, si bien es costumbre utilizarsiempre mayúsculas.

Todas las sentencias SQL comienzan con un verbo, es decir, una palabra clave que describelo que la sentencia hace (p.e. CREATE, INSERT, DELETE, SELECT, etc.).

La sentencia continúa con una o más cláusulas. Una cláusula puede especificar los datossobre los que debe actuar la sentencia, o proporcionar más detalles acerca de lo que hace lasentencia. Todas las cláusulas comienzan también con una palabra clave (p.e. WHERE, FROM,etc.). En definitiva, una cláusula puede contener nombres de tablas, de columnas, otras palabrasclaves, constantes o expresiones.

EJEMPLO:

Verbo Cláusulas Constante

DELETE FROM INFVENTAS WHERE VENTAS < 2000.00 ;

Palabras clave Nombre de tabla Nombre de columna

Las palabras claves no pueden ser utilizadas para designar objetos de la base de datos, talescomo tablas, columnas y usuarios.

La lista completa de palabras reservadas se puede encontrar en:http://dev.mysql.com en el apartado Reserved_words.html

2.1. – TIPOS DE SENTENCIAS.

Según el tipo de operación que expresan, las sentencias en SQL pueden clasificarse en:

1.- Sentencias de Definición de Datos (Data Definition Language - DDL).-Permiten definir nuevos objetos (CREATE Tablas, Vistas, Índices) o destruir (DROP)

objetos existentes en una base de datos.

2.- Sentencias de Manipulación de Datos (Data Manipulation Language - DML).- Permitenrealizar consultas u mantenimiento de datos (inserción, modificación y borrado). Son:

SELECT.- Permite extraer datos de una o varias tablas. Se utiliza para realizar consultas.

INSERT.- Permite añadir una o varias filas (tuplas) a una tabla (relación).

UPDATE.- Permite modificar uno o varios valores de una o más filas de una tabla.

DELETE.- Permite borrar una o varias filas de una tabla.

3.- Sentencias de Control de Datos (Data Control Language - DCL).- Permiten definir la seguridad de los datos y garantizar la confidencialidad de acceso a los

datos, mediante la concesión (GRANT) de autorizaciones o denegación (REVOKE) de éstas.


4/53

4

3.- TIPOS DE DATOS Y OPERADORES.

TIPOS DE DATOS

Al asignar, con la sentencia CREATE, el nombre a las columnas de una tabla, se le asignatambién un determinado tipo de datos de los predefinidos en SQL, con ello se está definiendo:

• El conjunto de los valores posibles que puede tomar el atributo (Dominio).• Las operaciones que pueden realizarse con los valores almacenados en el atributo.

Los tipos de datos permitidos en SQL pueden ser:

a) Numéricos o cantidades susceptibles de participar en cálculos aritméticos.b) Alfanuméricos o de texto que representan combinaciones de caracteres cualesquiera.c) Datos lógicos o Booleanos.d) Datos de tipo fecha y hora.

Datos Numéricos

Tipo Espacio Rango Rango sin signoTINYINT 1 byte -128 a 127 0 a 255SMALL INT 2 bytes -32.768 a 32.767 0 a 65.535MEDIUM INT 3 bytes -8.388.608 a 8.388.607 0 a 16.777.215INT 4 bytes -2.147.483.648 a 2.147.483.647 0 a 4.294.967.295INTEGER 4 bytes -2.147.483.648 a 2.147.483.647 0 a 4.294.967.295BIGINT 8 bytes -9,223 * 1018 a 9,223 * 1018 0 a 18,446 * 1018 FLOAT(M,D) 4 bytes varía según los parámetrosDOUBLE(M,D) 8 bytes varía según los parámetros

DECIMAL(M,D) M bytes varía según los parámetrosNUMERIC(M,D) M bytes varía según los parámetros

Modificadores numéricos:

Los números enteros se pueden colocar sin signo adelantando la palabra UNSIGNED.Los decimales permiten indicar el número total de dígitos (M) y el número de decimales

(D), puede ser 0 para números enteros.A los números enteros se les puede asignar el modificador AUTO_INCREMENT para que

el campo marcado almacene más rápido los valores.Se puede declarar una anchura de columna de esta forma: INT(8).Si además se define la columna con ZEROFILL, entonces se colocan ceros a la izquierda

hasta llegar a esa anchura.

Datos alfanuméricos o de textoipo Espacio Tamaño máximo

Tipo Espacio Tamaño máximoCHAR(X) X bytes 255 bytesVARCHAR(X) X + 1 bytes 255 bytesTINYTEXT X + 1 bytes 255 bytesTINYBLOB X + 1 bytes 255 bytesTEXT X + 2 bytes 65.535 bytes

BLOB X + 2 bytes 65.535 bytesMEDIUMTEXT X + 3 bytes 1,6 MBMEDIUMBLOB X + 3 bytes 1,6 MBLONGTEXT X + 4 bytes 4,2 GBLONGBLOB X + 4 bytes 4,2 GB


5/53

5

Los que más se usan son CHAR y VARCHAR. Ambos almacenan cadenas de caracterespero CHAR las almacena de tamaño fijo y VARCHAR de tamaño variable. Dicho de otra forma: sidefinimos un CHAR de tamaño 8, CHAR(8), cualquier dato ocupa 8 bytes. Con VARCHAR(8) sialgún dato ocupa 4 caracteres, en disco ocupará 5 bytes, 4 +1 carácter de fin de cadena.

TEXT y BLOB se usan para grandes cantidades de texto variable. La diferencia es que los

tipos BLOB distinguen entre mayúsculas y minúsculas.

Datos lógicos

Son los tipos BIT o BOOL que admiten los valores 0 o 1.

Datos de fechas y horastipo Rango Formato

Tipo Rango FormatoDATE Del 1/1/1001 al 31/12/9999 AAAA-MM-DDDATETIME De las 0 horas del 1/1/1001 a las 0

horas del 31/12/9999AAAA-MM-DD HH:MM:SS

TIMESTAMP De las 0 horas del 1/1/1970 a las 0horas del 31/12/2037

AAAA-MM-DD HH:MM:SS

Permite estos tamaños:* 14 (aaaammddhhmmss)* 12 (aaaammddhhmm)* 8 (aaaammdd)* 6 (aammdd)* 4 (aamm)* 2 (aa)

TIME Hora desde 00:00 a 23:59 HH:MM:SSYEAR Año desde 1901 a 2037 AAAA

Modificadorese aplica a Uso

Modificador Se aplica a UsoAUTO_INCREMENT Enteros El valor se va incrementando en cada

registroBINARY char y varchar Convierte las cadenas a forma binaria en

la que se distingue entre mayúsculas yminúsculas

DEFAULT Todos menos TEXTy BLOB

Coloca un valor por defecto, el valor secoloca justo después de la palabra

NOT NULL Todos Impide que un campo sea nuloPRIMARY KEY Todos Hace que el campo se considere clave

primariaUNIQUE Todos Evita la repetición de valoresUNSIGNED Enteros Solo valen los valores positivosZEROFILL Enteros Rellena con ceros a la izquierda hasta

llegar al ajuste

ENUMPermite declarar campos cuyo contenido puede ser una de entre varias opciones.Ejemplo:CREATE TABLE personas (Sexo ENUM(‘M’, ‘H’), .....


6/53

6

Valores nulos (NULL)

Sea cual sea el tipo de dato especificado para una columna puede permitirse o no que entresus posibles valores figure un valor especial, el valor nulo (NULL) que indica la carencia de datos ofalta de información sobre dichos datos. Es decir, la interpretación que se hace del valor nulo es lade “valor Desconocido”.

Al definir una columna en una tabla mediante la sentencia CREATE, puede permitirse lainclusión de valores nulos, indicando en su dominio correspondiente la palabra NULL o excluirdicho valor del dominio mediante la palabra NOT NULL.

Debe tenerse en cuenta que en una columna de tipo numérico el valor NULL no significa elvalor 0. Su significado real es número desconocido.

En una columna de tipo alfanumérico NULL no significa campo en blanco ni cadena delongitud cero. Su significado real es literal desconocido.

Constantes

Las constantes o literales son secuencias de caracteres que representan un valor

determinado, numérico o alfanumérico. Se especifican de la forma siguiente:

Numéricas:

Enteras y Decimales: mediante el valor de la constante con su signo caso de ser negativo: -10 , 20 , 3.14 , -5.72 etc.

Exponenciales: mediante una secuencia de caracteres formada por una constante entera odecimal de hasta 17 dígitos seguida de la letra E y de una constante entera de hasta 2 dígitos:1.23E45 , 314E-2 , -11.2E17 , etc.

Alfanuméricas

Se especifican como secuencias de caracteres cualesquiera dentro del juego de caracteresdisponibles, que empiezan y terminan con una comilla simple (‘): ‘Informática’, ‘S’, ‘10-11-2000’ ,‘Harrold’’s’, etc.

Como se ve en el último ejemplo se utilizan dos apóstrofes consecutivos para indicar alSGBD que se utiliza el carácter ‘ como literal y no como fin de la constante.

El SGBD trata las constantes alfanuméricas como datos de tipo VARCHAR.

OPERADORES

Operadores aritméticos

Operan entre valores numéricos y devuelven un valor numérico como resultado de realizarlos cálculos indicados. Se emplean para realizar cálculos numéricos:

+ Suma- Resta* Multiplicación

/ DivisiónDiv División entera ( parte entera de la división, sin decimales)Mod Resto de la división


7/53

7

Operadores de comparación

Las expresiones formadas con operadores de comparación dan como resultado un valor detipo Verdadero/Falso/Nulo (True/False/Null).

= Igual

!= Distinto Distinto< Menor Mayor>= Mayor o igualBETWEEN / NOT BETWEENIN / NOT INIS NULL / IS NOT NULLLIKE

Los primeros son los operadores relaciones ya conocidos. Los segundos son pares de unoperador y su negación con la siguiente forma de actuar:

BETWEEN valor1 AND valor2Es VERDADERO si el valor comparado es >= que valor1 y SHOW DATABASES;+-----------+| Database |


8/53

8

+-----------+| mysql || test |+-----------+2 rows in set (0.00 sec)mysql>

Es probable que la lista de bases de datos que veamos sea diferente en nuestro caso, peroseguramente las bases de datos "mysql" y "test " estarán entre ellas. En particular, la base de datos"mysql" es necesaria, ya que ésta tiene la información de los privilegios de los usuarios de MySQL.La base de datos "test " se crea durante la instalación de MySQL con el propósito de servir comoárea de trabajo para los usuarios que inician en el aprendizaje de MySQL.

Para acceder a la base de datos "test " se usa el comando USE:

mysql> USE testDatabase changedmysql>

Observar que USE, al igual que QUIT, no requieren el uso del punto y coma, aunque si seusa éste, no hay ningún problema. El comando USE es especial también de otra manera, debe serusado en una sola línea.

mysql> USE pruebaERROR 1049: Unknown database 'prueba'mysql>

El mensaje anterior indica que la base de datos no ha sido creada, por lo tanto necesitamoscrearla.

mysql> CREATE DATABASE prueba;Query OK, 1 row affected (0.00 sec)mysql> USE pruebaDatabase changedmysql>

Ahora ya se pueden lanzar sentencias CREATE TABLE para crear tablas sobre la base dedatos prueba que hemos creado y hemos accedido.

Bajo el sistema operativo WINDOWS, los nombres de las bases de datos no diferencian

entre mayúsculas y minúsculas.Al crear una base de datos no se selecciona ésta de manera automática; debemos hacerlo demanera explícita, por ello usamos el comando USE en el ejemplo anterior.

La base de datos se crea sólo una vez, pero nosotros debemos seleccionarla cada vez queiniciamos una sesión con mysql. Por ello es recomendable que se indique la base de datos sobre laque vamos a trabajar al momento de invocar al monitor de MySQL. Por ejemplo:

shell>mysql -h 192.168.1.2 -u root –p pruebaEnter password: ******Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 17 to server version: 4.1.8-nt-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffermysql>

Nota: Observar que "prueba" no es la contraseña que se está indicando desde la línea de comandos,sino el nombre de la BD a la que deseamos acceder.


9/53

9

Si deseamos proporcionar la contraseña en la línea de comandos después de la opción "-p",debemos de hacerlo sin dejar espacios (por ejemplo, -phola123, no como -p hola123). Sin embargo,escribir nuestra contraseña desde la línea de comandos no es recomendado, ya que es bastanteinseguro.

Para ver en un momento dado en que base de datos nos encontramos situados:

mysql> select database( );+-------------+| database( ) |+-------------+| prueba |+-------------+1 row in set (0.00 sec)mysql>

Para acceder a una tabla de una base de datos cuando nos encontramos situados en otra base

de datos es necesario anteponer en la sentencia el nombre de la base de datos seguido de un punto.Por ejemplo accedo mediante el comando USE a la base de datos test y a continuación hago unselect de la tabla departamentos de la base de datos prueba.

mysql> use testDatabase changedmysql> select * from prueba.departamentos;

+-----------+------------------------+-------------------+| DEP_NO | DNOMBRE | LOCALIDAD |+-----------+------------------------+-------------------+| 10 | CONTABILIDAD | BARCELONA || 20 | INVESTIGACION | VALENCIA || 30 | VENTAS | MADRID || 40 | PRODUCCION | SEVILLA |+-----------+------------------------+-------------------+4 rows in set (0.10 sec)mysql>

Esto se podrá hacer siempre que el usuario tenga privilegios suficientes como para acceder aambas bases de datos (en este ejemplo a prueba y a test).

Algunas consultas básicas de interés para el administrador

- Consulta para conocer la versión del servidor y la fecha actual:

mysql> SELECT VERSION( ), CURRENT_DATE;+------------------+------------------------+| VERSION( ) | CURRENT_DATE |+------------------+------------------------+| 5.5.8 | 2012-01-08 |+------------------+------------------------+

1 row in set (0.09 sec)mysql>

Nota: También tenemos: CURRENT_TIME NOW( )


10/53

10

Un comando normalmente consiste de una sentencia SQL seguida por un punto y coma.Cuando emitimos un comando, mysql lo manda al servidor para que lo ejecute, nos muestra losresultados y regresa el prompt indicando que está listo para recibir más consultas. mysql muestra losresultados de la consulta como una tabla (filas y columnas). La primera fila contiene etiquetas paralas columnas. Las filas siguientes muestran los resultados de la consulta. Normalmente las etiquetasde las columnas son los nombres de los campos de las tablas que estamos usando en alguna

consulta. Si lo que estamos recuperando es el valor de una expresión (como en el ejemplo anterior)las etiquetas en las columnas son la expresión en sí.mysql muestra cuántas filas se han obtenido y cuanto tiempo tardó en ejecutarse la consulta,

lo que puede darnos una idea de la eficiencia del servidor, aunque estos valores pueden ser un tantoimprecisos ya que pueden verse afectados por otros factores, tales como la carga del servidor y lavelocidad de comunicación en una red.

Las palabras clave pueden ser escritas usando mayúsculas y minúsculas.

-Consulta para conocer el nombre del usuario que ha efectuado la conexión:

mysql> SELECT

-> USER( );+------------------+| user( ) |+------------------+| root@localhost |+------------------+1 row in set (0.00 sec)mysql>

En este ejemplo vemos como cambia el prompt (de mysql> a ->) cuando se escribe unaconsulta en varias líneas, así se indica que está esperando a que finalice la consulta. Sin embargo siqueremos no terminar de escribir la consulta, podemos hacerlo al escribir \c como se muestra en elsiguiente ejemplo:

mysql> SELECT-> USER( ),-> \cmysql>

Así aparece el prompt mysql> que nos indica que mysql está listo para una nueva consulta.En la siguiente tabla se muestran cada uno de los prompts que podemos obtener y una breve

descripción de su significado para mysql:Prompt Significadomysql> Listo para una nueva consulta.-> Esperando la línea siguiente de una consulta multi- línea.'> Esperando la siguiente línea para completar una cadena que comienza con una

comilla sencilla (')."> Esperando la siguiente línea para completar una cadena que comienza con una

comilla doble (").

Los prompts '> y "> ocurren durante la escritura de cadenas.

En mysql podemos escribir cadenas utilizando comillas sencillas o comillas dobles (porejemplo, 'hola' y "hola"), y mysql nos permite escribir cadenas que ocupen múltiples líneas. Demanera que cuando veamos el prompt '> o "> , mysql nos indica que hemos empezado a escribir unacadena, pero no la hemos finalizado con la comilla correspondiente.


11/53

11

5.- LENGUAJE DE DEFINICIÓN DE DATOS.

Los cambios en la estructura de una BD son manejados por un conjunto diferente desentencias SQL, denominadas conjuntamente Lenguaje de Definición de Datos o DDL. Utilizandosentencias DDL, se pueden realizar las siguientes operaciones entre otras:

• Definir y crear una nueva tabla.• Suprimir una tabla que ya no se necesita.• Cambiar la definición de una tabla existente.• Definir una tabla virtual o vista de datos.• Construir un índice para hacer más rápido el acceso a la tabla.• Etc.

5.1. – TABLAS.

Reglas para los nombres de tablas:

• Comenzar por una letra.• Longitud máxima de 64 caracteres.• No utilizar espacios en blancos ni caracteres de puntuación especiales NI GUIONES (en

cambio _ si se puede usar).• Que el nombre no esté duplicado.• Que no sea una palabra reservada.• No diferencia entre mayúsculas y minúsculas.

CREAR TABLAS

El usuario debe tener este privilegio y un área de almacenamiento para crear objetos.Cuando un usuario crea una tabla, se convierte en su propietario. El Administrador de la BD (ADBo DBA) utiliza comandos del Lenguaje de Control de Datos (DCL) para dar privilegios a losusuarios.

Para definir una tabla hay que tener unas consideraciones básicas:o El nombre de la tabla.o El nombre de cada columna (no admite guiones).o El tipo de dato almacenado en cada columna.o El tamaño de cada columna.

o

La clave primaria y las claves ajenas.

Formato básico para la creación de la estructura de las tablas

CREATE TABLE [IF NOT EXISTS] nombre_tabla ( nombre_columna tipo_datos

[, nombre_columna tipo_datos , … ] );

El nombre de la tabla debe ser único en la BD. Los nombres de columnas deben ser únicosdentro de la tabla.

La cláusula IF NOT EXISTS previene el posible error generado si ya existiese una tabla conese nombre.

Nota: Los corchetes en estos apuntes indican que lo que está en su interior es opcional, NUNCA seponen:

CREATE TABLE ejemplo ……


12/53

12

CREATE TABLE IF NOT EXISTS ejemplo ……CREATE TABLE [IF NOT EXISTS] ejemplo ……

EJERCICIOS:

CREATE DATABASE PRUEBA;

USE PRUEBA;

1.- Crear la tabla PRU con las columnas:- COD número entero de 4 bytes.- DATO cadena de 10 caracteres de longitud fija.

2.- Crear la tabla ALUMNOS con las columnas:- N_MATRICULA número entero de 4 bytes.- NOMBRE cadena de 40 caracteres máximo.- DNI campo numérico de 8 dígitos.

La sintaxis completa utilizada en MySQL para crear tablas es la siguiente:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nombre_tabla ( definición_columna [, definición_columna, … ]

[, especificaciones_tabla] );

Definición_columna:nombre_columna tipo_datos [NULL | NOT NULL] [DEFAULT default_value]

[AUTO_INCREMENT] [UNIQUE | [PRIMARY] KEY][COMMENT 'string'] [CHECK (restricción) ]

Especificaciones_tabla:[CONSTRAINT [nombre_restricción]]

PRIMARY KEY (nombre_columna [ , nombre_columna, …])| KEY [nombre_índice] [tipo_índice] (nombre_columna [, nombre_columna, …])| INDEX [nombre_índice] [tipo_índice] (nombre_columna [, nombre_columna, …])| [CONSTRAINT [nombre_restricción]] UNIQUE [INDEX]

[nombre_índice] [tipo_índice] (nombre_columna [, nombre_columna, …])| [CONSTRAINT [nombre_restricción]] FOREIGN KEY

(nombre_columna [, nombre_columna, …]) REFERENCES tb_referenciada (nombre_columna [, nombre_columna, …])

[ON DELETE acción_realizar][ON UPDATE acción_realizar]| [CHECK (restricción)]

Acción_realizar:RESTRICT | CASCADE | SET NULL | SET DEFAULT | NO ACTION

NULL / NOT NULL.- La columna puede ser NULL / no puede ser NULL (campo obligatorio).

DEFAULT.- Permite dar valor por defecto a una columna, puede ser un literal, una expresión o unafunción SQL como CURRENT_DATE. No puede ser el nombre de otra columna. La expresión por

defecto debe ser del mismo tipo que el de la columna.

AUTO_INCREMENT.- Solo para tipo de datos numéricos enteros y tomará valoresautomáticamente desde el valor 1, incrementándose de 1 en 1.


13/53

13

UNIQUE.- Esa columna no puede tomar valores repetidos.

PRIMARY KEY.- Identifica de forma única a cada fila de la tabla, es decir, indica la claveprincipal. Asume que es NOT NULL.

COMMENT.- Podemos poner un comentario.

KEY.- Define una clave.

INDEX.- Define un índice.

FOREIGN KEY.- Establece una relación de clave ajena entre dicha columna y la columna de latabla referenciada. La clave ajena puede ser una columna o conjunto de columnas. Las claves ajenaspueden tomar el valor NULL. Siempre hay que poner el nombre del campo de la clave primariaentre paréntesis:

REFERENCES tb_referenciada (nombre_columna [, nombre_columna, …])

La acción a realizar puede ser:RESTRICT: Impide suprimir una fila de la tabla padre si la fila tiene algún hijo. Esta es la

opción que se toma por defecto.CASCADE: Si se borra o actualiza una fila de la tabla padre, automáticamente se borrarán o

actualizarán todas las filas de la tabla hija.SET NULL: Pone a nulos los valores de las claves ajenas de la relación que

referencia, es decir de la hija.SET DEFAULT: Pone el valor por defecto a los valores de las claves ajenas de la relación

que referencia.NO ACTION.

CHECK.- Comprueba si la columna o columnas especificadas cumplen una determinadarestricción. La versión actual de MySQL lo acepta dentro del formato (no da error al crear la tabla)pero no lo tiene implementado, es decir, no funciona.

EJERCICIOS (es más cómodo trabajar en Word o en el bloc de notas y luego “copiar ypegar” sobre la línea de comandos):

1.- Crear la tabla ALUMNOS1 con las columnas:- N_MATRICULA campo numérico de 5 dígitos.- NOMBRE cadena de 40 caracteres máximo.- DNI campo numérico de 8 dígitos único y obligatorio.

2.- Crear otra tabla ALUMNOS2 igual a la anterior pero con clave primaria el número de matrícula(N_MATRICULA).

3.- Crear otra tabla ALUMNOS3 igual a la anterior pero añadiéndole un campo para el códigopostal (CP) y obligando a que sea de Madrid, es decir que esté comprendido entre 28000 y 28999(veremos que no da error al crearla pero no funciona).

4.- Crear la tabla USUARIOS con:- N_USUARIO campo numérico de 4 dígitos y clave primaria.- NOM_USUARIO cadena de 15 caracteres y obligatorio.

- ID_USUARIO cadena de 5 caracteres y clave candidata (único).- TIPO que sea A ó B.

5.- Crear la tabla DOCUMENTO con los siguientes campos:


14/53

14

TIPO: A si es un artículo y L si es un libro.COD_DOC: 4 caracteres.TITULO: 25 caracteres. Obligatorio.IDIOMA: 20 caracteres.NUM_EDI: número entero de 4 bytes.ANNO: tipo YEAR.

ISBN: numérico (10).Clave primaria: TIPO + COD_DOCISBN: es único para cada fila.Clave ajena: NUM_EDI de la tabla EDITORIAL (COD-EDI INT(1), NOM-EDI CHAR(9))

6.- Crear la tabla SOCIOS con:- SOCIO_NO número entero de 4 bytes y clave primaria.- APELLIDOS cadena de 14 caracteres máximo y clave candidata.- TELEFONO cadena de 9 caracteres y obligatorio.- FECHA_ALTA fecha y valor por defecto ‘2012-01-01’.- DIRECCION cadena de 20 caracteres máximo.

- CODIGO_POSTAL campo numérico de 5 dígitos.

7.- Crear la tabla PRESTAMOS con:- NUM_PRESTAMO número entero de 2 bytes y clave primaria.- SOCIO_NO número entero de 4 bytes y clave ajena de la tabla SOCIOS.

8.- Crear la tabla PEDIDOS con las siguientes características:Atributos: NUM_PEDIDO, FECHA_PEDIDO, CLIENTE, COD_VEND, FABRICA,

PRODUCTO, IMPORTE. Poner los tipos de datos adecuados para la naturaleza de los atributos.Clave primaria: NUM_PEDIDO, que se asigne automáticamente.

9.- Crear la tabla PEDIDOS1 igual a PEDIDOS pero añadiendo lo siguiente:Claves ajenas: CLIENTE, COD_VEND y FABRICA + PRODUCTO.Tablas referenciadas:

CLIENTES ( COD_CLI, ... )VENDEDORES ( ID_VEND, ... )PRODUCTOS ( ID_FAB, ID_PROD, ... )

Nota: Para ver la estructura de una tabla creada podemos usar: DESC o DESCRIBE o EXPLAIN:

DESC SOCIOS;

DESCRIBE SOCIOS;EXPLAIN SOCIOS;

Y si queremos saber las tablas que tenemos definidas:

SHOW TABLES;

También podemos crear una tabla a partir de la definición de otra (solo la estructura):

CREATE TABLE [IF NOT EXISTS] nombre_tabla_nueva LIKE nombre_tabla_antigua;

Y también a partir de los datos de una tabla ya existente (la estructura y el contenidoresultante de la selección):

CREATE TABLE [IF NOT EXISTS] nombre_tabla_nueva[(definición_columna1, ..., definición_columnaN) ] [ IGNORE | REPLACE ]


15/53

15

[AS] ( SELECT columna1, ..., columnaNFROM nombre_tabla_antigua [WHERE condición] );

Los nombres de las columnas de la nueva tabla son opcionales. En caso de que no seespecifiquen tomarán los valores de la otra tabla o de los alias correspondientes. Pero debe tenersecuidado con ello pues si hay expresiones o funciones en la lista de la SELECT y no tienen alias ni

nuevo nombre, luego estas columnas no pueden referenciarse.

Las opciones de duplicados en campos únicos (claves primarias o unique) permiten indicarque hacer si hay un valor repetido en ese campo. Si no se indica nada se producirá un error. Paraevitar estos errores podemos especificar una de las dos opciones IGNORE que ignora la fila y no lagraba y REPLACE que reemplaza la fila por la anterior.

La nueva tabla creada no hereda las CONSTRAINTS que tenga asignada la tabla origen, hayque definirlos posteriormente con ALTER TABLE.

EJEMPLO:

CREATE TABLE MOROSOS AS( SELECT Nombre, Dirección, Cantidad, Fecha_Vencimiento

FROM CLIENTES WHERE Cantidad > 1000 );

EJERCICIOS:

1.- Crear una tabla llamada SOCIOS_MOD a partir de la tabla SOCIOS.

CREAR LAS TABLAS DE TRABAJO DEL ANEXO FINAL: DEPARTAMENTOS,EMPLEADOS, CLIENTES, PRODUCTOS Y PEDIDOS (script: “DB_Ventas.sql”)

USE VENTAS;

2.- Crear una tabla llamada PRU1 a partir de la tabla EMPLEADOS, con las columnas:EMP_NO y APELLIDO.

3.- Crear una tabla llamada PRU2 a partir de la tabla EMPLEADOS, con todos losempleados cuyo sueldo es mayor que 2000.

MODIFICAR TABLASLa modificación de la definición de una tabla conllevaría alguna de las siguientes

posibilidades:

• Añadir o eliminar una columna a una tabla• Añadir o eliminar una restricción (clave primaria, ajena, unicidad, …)• Modificar una columna (sin cambiar su nombre o cambiándolo)• Renombrar la tabla

Con la orden ALTER TABLE se permite cambiar la estructura de una tabla ya creada

añadiendo columnas o modificándolas. Para ello tiene dos formas:ALTER TABLE ..... ADD/DROP y ALTER TABLE ..... MODIFY.

La sintaxis es la siguiente:


16/53

16

ALTER TABLE nombre-de-tabla[ ADD [COLUMN] nombre-de-columna tipo-de-dato [FIRST | AFTER nombre-de-columna ]

[NOT NULL] [DEFAULT expr] , .... ][ DROP [COLUMN] nombre-de-columna][ ADD PRIMARY KEY (atributo) ][ DROP PRIMARY KEY ]

[ ADD CONSTRAINT [nom-restricción] restricción ][ DROP CONSTRAINT nom-restricción ][ ADD FOREIGN KEY (atributo) REFERENCES nombre-de-tabla (nom_columna) ][ DROP FOREIGN KEY nom-constraint ];

ALTER TABLE nombre-de-tabla[ MODIFY [COLUMN] nombre-de-columna tipo-de-dato [restricciones] , .... ]

ALTER TABLE nombre-de-tabla[ CHANGE [COLUMN] nombre-columna-antiguo

nombre-columna-nuevo tipo-de- dato [restricciones] , .... ]

ALTER TABLE nombre-de-tabla[ RENAME [ TO ] nombre-de-tabla-nuevo ]

A la hora de modificar la estructura de una tabla, hay que tener en cuenta las siguientesconsideraciones:

• La columna que se añade siempre será la última, a no ser que usemos AFTER paraindicar que va después de una columna determinada o FIRST para ponerla en primeraposición.

• Los valores de la nueva columna se inicializarán con valores nulos, por esa razón nopodemos indicar NOT NULL al añadirla.

• Se puede modificar tipo de dato, tamaño y valor por defecto de una columna con lamisma sentencia de ALTER TABLE.

• Cuando se disminuye el tamaño o se cambia el tipo de datos de una columna de unatabla sus valores deberán estar a nulos.

• No existe el comando ALTER VIEW.

EJEMPLOS.

ALTER TABLE PEDIDOS ADD COL1 CHAR (2) ADD COL2 CHAR (2);ó

ALTER TABLE PEDIDOS ADD ( COL1 CHAR (2), COL2 CHAR (2) );

ALTER TABLE PEDIDOS ADD COL3 CHAR (3) MODIFY COL2 CHAR (4);

ALTER TABLE DOCUMENTO ADD CODIGO NUMERIC (10);

ALTER TABLE DOCUMENTO DROP PRIMARY KEY,ADD PRIMARY KEY (CODIGO);

Nota: no se puede poner como clave el ISBN porque está definido como UNIQUE, habría queborrar antes esa restricción.

EJERCICIOS:

USE PRUEBA;


17/53

17

1.- Modificar la tabla PEDIDOS en la siguiente forma• Poner la fecha de pedidos a NOT NULL.• Añadir un nuevo atributo para guardar un teléfono de contacto con el cliente.• Aumentar el tamaño del campo código de vendedor en 2.

2.- Añadir en la tabla SOCIOS_MOD la restricción UNIQUE para la columna TELEFONO.

3.- Borrar la columna FECHA_ALTA de la tabla SOCIOS_MOD

4.- Borrar la clave primaria de la tabla SOCIOS_MOD

5.- Modificar la tabla SOCIOS_MOD para poner NOT NULL en la columna APELLIDOS.

6.- Modificar la tabla SOCIOS_MOD cambiándole el nombre a la columna APELLIDOSpor APELLIDOS_A e incrementar de 14 a 20 caracteres el tamaño.

7.- Cambiar el nombre de la tabla SOCIOS_MOD por SOCIOS_FIN.

BORRAR TABLAS

El comando para borrar una tabla junto con su contenido e índices y vistas definidos sobredicha tabla es:

DROP TABLE [IF EXISTS] nombre-de-la-tabla;

EJERCICIOS:

USE PRUEBA;

1.- Crea una tabla nueva con 2 columnas y a continuación borrarla.

2.- Borrar una tabla que no exista con IF EXISTS (la anterior) y ver que no da error. ¿Y sino añadimos IF EXISTS?

3.- Borrar la tabla SOCIOS y analizar lo que ocurre.

TRUNCATE TABLE nombre-de-la-tabla;

Es otra forma de borrar tablas, suprime todas las filas de una tabla y libera el espacio sin quedesaparezca la definición de la tabla.

Nota: no se puede truncar una tabla cuya clave primaria sea referenciada por la clave ajenade otra tabla.

RENOMBRAR TABLAS

Permite cambiar el nombre de una tabla.

RENAME TABLE nombre-de-la-tabla-antiguo TO nombre-de-la-tabla-nuevo ;

También se pueden renombrar varias a la vez:


18/53

18

RENAME TABLE nombre-de-la-tabla-antiguo TO nombre-de-la-tabla-nuevo;

[, nombre-de-la-tabla-antiguo TO nombre-de-la-tabla-nuevo…] ;

EJEMPLO:

RENAME TABLE SOCIOS_FIN TO SOCIOS2;

También:ALTER TABLE SOCIOS RENAME [TO] SOCIOS2;

5.2. ÍNDICES.

Un índice es una estructura que proporciona un acceso rápido a las filas de una tabla enfunción de los valores de una o más columnas.

El SGBD utiliza el índice de la misma manera que se usa el índice de un libro: El índicealmacena los valores y punteros a las filas en las que los valores se producen. En el índice losvalores de datos están ordenados de forma ascendente o descendente para que el SGBD puedabuscar rápidamente el índice para encontrar un valor particular; posteriormente puede seguir elpuntero para localizar la fila que tiene el valor.

La presencia o ausencia de índice es completamente transparente al usuario de SQL queacceda a una tabla. El funcionamiento de los índices es el siguiente:

Si el usuario realiza una consulta a la base de datos como:SELECT campo1, campo2 FROM tabla WHERE campo3 = ‘Condición de Búsqueda’;

Si no existe un índice asociado a la columna campo3, el SGBD realizará la consulta exista ono exista información en ella. Procesará la consulta recorriendo secuencialmente la tabla fila a fila,verificando si se cumple ‘Condición de Búsqueda’ en campo3. Si la tabla es muy grande laexploración puede llevar minutos, incluso horas.

Si existiera un índice asociado a la columna campo3, el SGBD examina el índice paraencontrar el valor solicitado (‘Condición de Búsqueda’) y luego sigue el puntero para encontrar lafila o filas solicitadas de la tabla. La búsqueda en índices es mucho más rápida ya que el índice estáordenado y sus filas son muy pequeñas. Pasar del índice a la fila correspondiente es también muyrápido ya que el índice informa al SGBD el lugar del disco en el que está localizada la fila.

La gran ventaja de la utilización de índices se centra en que se acelera enormemente la

ejecución de sentencias SQL con condiciones de búsqueda que se refieren a columnas indexadas.Los inconvenientes se refieren a que consumen un espacio de disco adicional y a que debenser actualizados cada vez que se añada a la tabla una fila nueva o se actualice el contenido de unafila existente.

Para establecer un índice a una columna deben tenerse en cuenta lo siguiente:

• La indexación es apropiada en columnas que se utilizan frecuentemente en condicionesde búsqueda.

• La indexación es aconsejable cuando las consultas de una tabla son más frecuentes que lasinserciones y las actualizaciones.

• El SGBD siempre establece un índice para la clave primaria ya que presupone que el

acceso a la tabla se efectuará más frecuentemente a través de dicha clave.

Cuando indexamos una columna, el SGBD crea una estructura de datos para almacenardatos extra sobre los valores de esa columna indexada. Tenemos 4 tipos de índices:


19/53

19

• De clave primaria.• Ordinarios.• Únicos.• De texto completo.

De clave primaria (PRIMARY KEY)

Son los índices relacionados con la clave primaria de la tabla. Identifica unívocamente acada fila de la tabla y no puede contener NULL. Se crean automáticamente al definir PRIMARYKEY.

OrdinariosSon índices que no son primarios y que permiten valores duplicados. Se crean con el

comando CREATE INDEX.

CREATE INDEX nom-índice ON nom-tabla ( campo1 [,campo2, …] …..;

ÚnicosSon básicamente como los ordinarios pero no se permiten valores duplicados. Sirven para

definir las claves candidatas.

CREATE UNIQUE INDEX nom-índice ON nom-tabla ( campo1 [,campo2, …] …..;

De texto completoSu utilizan para buscar palabras clave en grandes cantidades de texto y se definen en campos

de tipo CHAR, VARCHAR o TEXT.

CREATE FULLTEXT INDEX nom-índice ON nom-tabla ( campo1 [,campo2, …] …..;

CREAR ÍNDICES

El comando utilizado en SQL para crear índices es el siguiente:

CREATE [UNIQUE | FULLTEXT] INDEX nombre-del-índice ON nombre-de-tabla( nombre-de-columna [ORDEN], nombre-de-columna [ORDEN], ... ) [CLUSTER] ;

ORDEN: ASC ó DESC (ASC por defecto).

CLUSTER: Indica al sistema que las filas de las tablas deben estar almacenadas físicamentesegún el orden de las columnas que impone el índice.Una tabla puede tener varios índices asociados a ella pero sólo uno de ellos podrá ser

CLUSTER.

Cada índice se actualiza de forma automática con cada modificación, inserción o borrado detuplas en la tabla, por lo que siempre será consistente respecto a los datos de la misma.

La creación de índices permite al SGBDR utilizarlos a la hora de acceder a los datos, aunquees importante resaltar que el usuario no puede influir en el SGBD para indicarle que utilice undeterminado índice en una consulta.

EJEMPLO:

CREATE UNIQUE INDEX I_Isbn ON DOCUMENTO (Isbn ASC);

CREATE UNIQUE INDEX I_Tip_Cod ON DOCUMENTO (Tipo DESC, Cod_Doc ASC);


20/53

20

EJERCICIOS:

USE PRUEBA;

1.- Crear un índice en la tabla PEDIDOS para acceder a ella por cliente (en orden

ascendente) y por fecha de pedido (descendente).

2.- Crear un índice único en la tabla DOCUMENTO para acceder a ella por título.

CONSULTAR ÍNDICES DEFINIDOS.

SHOW KEYS FROM nombre-de-tabla;

BORRAR UN ÍNDICE.

Cuando un índice que ya no es necesario se puede eliminar con:

DROP INDEX nombre-de- índice ON nombre-de-tabla;

EJERCICIO:

1.- Borrar el índice de la tabla PEDIDOS creado anteriormente.

6. LENGUAJE DE MANIPULACIÓN DE DATOS.

6.1. INSERT.El comando INSERT es utilizado en SQL para insertar registros en la Base de Datos. Su

sintaxis tiene el siguiente formato:

INSERT INTO nombre-de-tabla [ ( nombre-de-columna [ , nombre-de-columna ] ... ) ]VALUES ( literal [, literal ] ... ) ;

ó bienINSERT INTO nombre-de-tabla [ ( nombre-de-columna [ , nombre-de-columna ] ... ) ]

subconsulta ;

Insertaría en las columnas especificadas de la tabla con nombre nombre-de-tabla los valoresindicados en los literales correspondientes.

Este formato de inserción tiene, además, las siguientes características:

• En la lista de columnas se pueden indicar todas o algunas de las columnas de la tabla. Eneste último caso, aquellas columnas que no se incluyan en la lista quedarán sin ningún valoren la fila insertada, es decir, se asumirá el valor NULL o el valor por defecto para lascolumnas que no figuren en la lista. En caso de una columna definida como NOT NULLdará error si no se introduce un valor.

• Los valores incluidos en la lista de valores deberán corresponderse posicionalmente con lascolumnas indicadas en la lista de columnas, de forma que el primer valor de la lista devalores se incluirá en la columna indicada en primer lugar, el segundo en la segundacolumna, y así sucesivamente.

• Se puede utilizar cualquier expresión para indicar un valor siempre que el resultado de laexpresión sea compatible con el tipo de dato de la columna correspondiente.


21/53

21

EJEMPLOS:

Insertar el departamento 50 de nombre MARKETING y localidad BILBAO.

INSERT INTO DEPARTAMENTOS (DEP_NO, DNOMBRE, LOCALIDAD)

VALUES (50, 'MARKETING','BILBAO');

Si no se especifican nombres de columnas, los literales se almacenarán en las columnascorrespondientes, según el orden de las columnas en la instrucción CREATE TABLE.

INSERT INTO DEPARTAMENTOS VALUES (50, 'MARKETING','BILBAO');

Conceptualmente, la sentencia INSERT construye una fila de datos que se corresponde conla estructura en columnas de la tabla. La nueva fila insertada no tiene por que ser la primera ni laúltima de la tabla. Es simplemente una fila más de la tabla que el SGBD situará donde proceda.

Cuando SQL inserta una nueva fila en una tabla, automáticamente asigna el valor NULL acualquier campo que no haya sido especificado en la lista de columnas de la sentencia INSERT. Asímismo, puede hacerse más explícita esta asignación de NULL a un campo incluyendo la columnacorrespondiente en la lista y especificando la palabra clave NULL en el valor correspondiente:

INSERT INTO Tabla (Campo1, Campo2, Campo3, ... , CampoN)VALUES (Valor1, Valor2, NULL, ... , ValorN) ;

La sentencia anterior asigna el valor NULL al Campo3 en la nueva fila insertada

Debe tenerse en consideración que, si en la definición de un campo específico en una tablase ha especificado que éste debe tener un valor NOT NULL, debe actualizarse explícitamente en lasentencia INSERT, ya que, de no hacerlo, el sistema produciría un error.

Así mismo, pueden utilizarse en la inserción constantes del sistema si el SQL utilizado lopermite:

INSERT INTO Tabla (Campo1, Campo2, Fecha3, ... , CampoN)VALUES (Valor1, Valor2, CURRENT_DATE, ... , ValorN) ;

La sentencia anterior introduce el valor de la fecha actual en el Campo Fecha3.

En el caso de la inserción multifila, los valores de datos para las nuevas filas se obtienen deuna consulta SELECT:

INSERT INTO Tabla1 (Campo1, Campo2, Campo3, ... , CampoN)SELECT Campo1, Campo2, Campo3, ... , CampoNFROM Tabla2WHERE Condición-de-Búsqueda ;

EJEMPLO:

INSERT INTO GERENTES (ID, NOMBRE, SALARIO )

SELECT EMP_NO, APELLIDO, SALARIOFROM EMPLEADOSWHERE OFICIO=’DIRECTOR’ ;


22/53

22

Insertaría en la tabla GERENTES el identificativo, el nombre y el salario de los empleadosque son directores en la tabla empleados.

Nota: La fechas se capturan: ‘2012/01/15’ o “2012/01/15” o ‘2012-01-15’ o “2012-01-15”

EJEMPLOS:

(hacerlos en el ordenador porque luego vamos a utilizar la tabla SOCIOS)

INSERT INTO socios (socio_no,apellidos,telefono,fecha_alta,direccion,codigo_postal)VALUES ( 1000,'LOPEZ SANTOS','916543267', '2005-01-08', 'C. REAL 5',28400);

INSERT INTO socios (socio_no,apellidos,telefono,fecha_alta,direccion,codigo_postal)VALUES ( 1001,'PEREZ CERRO','918451256', '2005-01-12', 'C. MAYOR 31',28400),

( 1002,'LOPEZ PEREZ','916543267', '2005-01-18', 'C. REAL 5',28400),( 1003,'ROMA LEIVA', '914747474', '2005-01-21', 'C. PANADEROS 9 ',28431);

INSERT INTO socios

VALUES ( 1004,'GOMEZ DURUELO','918654329', '2005-01-31', 'C. REAL 15',28400);

Inserción de un socio con socio_no=1005, con una instrucción INSERT sin valor en el campofecha que tiene un valor por defecto (pondrá ese valor 2012-01-01)

INSERT INTO socios (socio_no,apellidos,telefono,direccion,codigo_postal)VALUES ( 1005,'PEÑA MAYOR','918515256','C. LARGA 31', 28431);

INSERT INTO prestamos (num_prestamo, socio_no) VALUES ( 1,1000), ( 2,1002);

INSERT INTO prestamos VALUES ( 4,1004);

EJERCICIOS:

USE PRUEBA;

1.- Crear una tabla INVENTARIO con 2 columnas: NUM INT(2) clave primaria yauto_increment, DESCRIPCION VARCHAR(15).

2.- Insertar 2 filas para ‘ARMARIO BLANCO’ y ‘MESA MADERA’ sin enumerar lacolumna autonumérica.

3.- Insertar 2 filas para ‘ORDENADOR’ y ‘SILLA GIRATORIA’ sin poner el nombre delas columnas (debemos poner NULL o 0 en el valor auto_increment).

6.2. UPDATE.

El comando UPDATE se utiliza en SQL para modificar datos de la Base de Datos. Susintaxis tiene el siguiente formato:

UPDATE nombre-de-tabla

SET nombre-de-columna = expresión[, nombre-de-columna = expresión] ...[ WHERE condición ] ;


23/53

23

Las columnas especificadas en la cláusula SET de las filas de la tabla nombre-de-tabla quecumplan la “condición”, se modifican con los valores indicados en la expresión.

El comando UPDATE permite modificar varios registros a la vez en una sola operación.

Si se omite WHERE se modifican todos los registros de la tabla.

El comando UPDATE se puede usar con subconsultas multicolumna.

UPDATE nombre_tablaSET (nombre_columna, nombre_columna, ...) =

( SELECT nombre_columna, nombre_columna, ....FROM nombre_tabla[WHERE condición] )

[ WHERE condición ];

NOTA IMPORTANTE: La subconsultas no puede afectar a la misma tabla que la UPDATE.

EJERCICIOS:

USE PRUEBA;Partimos de la tabla socios: SELECT * FROM socios;

1.- Cambiar la dirección del socio de número 1000 a 'C.CUESTA 2'.

2.- Cambiar el teléfono del socio de número 1000 a ‘918455431’.

3.- Cambiar todos los códigos postales 28400 a 28401.

4.- Modificar la fecha de alta de todos los empleados al valor 1 de enero de 2005.

Podemos comprobar las modificaciones: SELECT * FROM socios;

USE VENTAS;

5.- Modificar el oficio del empleado 7698 con el valor correspondiente al empleado 7499(NO FUNCIONA).

6.3. DELETE.

El comando DELETE se utiliza en SQL para eliminar registros de la Base de Datos. Susintaxis tiene el siguiente formato:

DELETE FROM nombre-de-tabla[ WHERE condición ];

Borraría las filas de la tabla nombre-de-tabla que cumplan la “condición”.También se pueden usar las subconsultas en la condición del WHERE.

EJERCICIOS:

USE PRUEBA;


24/53

24

1.- Borrar el socio 1001.

Nota: Hay que tener cuidado con las claves ajenas. Como prestamos tiene una clave ajena quereferencia a socios si pretendemos borrar un socio que tiene préstamos nos dará error.

2.- Borrar el socio 1002, ¿es posible?.

USE VENTAS;

3.- Borrar en departamentos todas las filas cuyo nombre sea ‘INVESTIGACION’.

4.- Añadir un nuevo departamento (50, RRHH, MADRID) y luego borrarlo.

Ejemplo de borrado en cascada

Creamos las tablas departamentos2 y empleados2 con una clave ajena con borrado encascada e insertamos los valores desde las tablas departamentos y empleados

CREATE TABLE departamentos2( dep_no INT(4),

dnombre VARCHAR(14),localidad VARCHAR(10),CONSTRAINT PK2_DEP PRIMARY KEY (DEP_NO)

);

INSERT INTO departamentos2SELECT dep_no, dnombre, localidad FROM departamentos;

CREATE TABLE empleados2( emp_no INT(4),

apellido VARCHAR(8),oficio VARCHAR(15),director INT(4),fecha_alta DATE,dep_no INT(2),CONSTRAINT PK_EMPLEADOS_EMP_NO2 PRIMARY KEY (emp_no),CONSTRAINT FK_EMP_DEP_NO2 FOREIGN KEY (dep_no)

REFERENCES departamentos2(dep_no) ON DELETE CASCADE

);INSERT INTO empleados2

SELECT emp_no, apellido, oficio, director, fecha_alta, dep_no FROM empleados;

SELECT * FROM departamentos2;SELECT * FROM empleados2;

Vamos a borrar una fila en la tabla departamentos. Si no existiese borrado en cascada,debido a la integridad referencial, no podríamos borrar ningún departamento que tuviese empleados.De esta forma al borrar un departamento se borrarán todos los empleados de ese departamento.

DELETE FROM departamentos2 WHERE dep_no=10;

SELECT * FROM departamentos2;SELECT * FROM empleados2;


25/53

25

6.4. SELECT.

El comando SELECT es utilizado en SQL para realizar consultas en una Base de Datos. Susintaxis básica es la siguiente:

SELECT { * | [DISTINCT | ALL] | columna [ , columna, … ] } FROM nombre-de-tabla ;

Nota: Las llaves en estos apuntes indican que alguna de las opciones separadas por | es obligatorioponerla, pero las llaves (ni los corchetes) NUNCA se ponen.

Es necesario tener presente que:

1.- SQL recupera filas repetidas [ALL] a no ser que se especifique explícitamente con laopción DISTINCT.

2.- Si queremos todos los elementos/columnas de una tabla, utilizamos *

EJERCICIOS:

USE VENTAS;

1.- Seleccionar todos los datos de la tabla empleados.

2.- Obtener los números de empleados, los apellidos y el número de departamento de todoslos empleados de la tabla empleados.

3.- Obtener los departamentos diferentes que hay en la tabla empleados.

4.- Obtener los diferentes oficios que hay en cada departamento de la tabla empleados.

La sintaxis completa tiene el siguiente aspecto:

SELECT { * | [DISTINCT | ALL] columna [ , columna, … ] } [ [AS] aliascolumnas ]FROM nombre_de_tabla(s) [ [AS] aliastabla(s) ] [ WHERE condición ][ GROUP BY { campo(s) | funciones de columna | posición } ]

[ HAVING condición ][ ORDER BY {columna | posición [ ASC | DESC ] }[ , {columna | posición [ ASC | DESC ] }…] ]

[ LIMIT [ m, ] n ] ;

Podemos cambiar el nombre de las columnas utilizando los “alias”:

SELECT nom-col AS nombre FROM tabla;SELECT nom-col nombre FROM tabla;SELECT nom-col “nombre” FROM tabla;

EJEMPLOS:

SELECT EMP_NO AS Num_empleado FROM EMPLEADOS;SELECT EMP_NO “Num-empleado” FROM EMPLEADOS;


26/53

26

SELECT EMP_NO “Num-empleados”, APELLIDO “Nombre” FROM EMPLEADOS;SELECT EMP_NO Num_empleado, APELLIDO Nombre FROM EMPLEADOS;

3.- Podemos realizar la consulta de valores calculados:

SELECT apellido, ‘Salario en pesetas =’, salario * 166.386 FROM EMPLEADOS ;

Podemos usar la tabla DUAL que está disponible para todos los usuarios de la BD. Tieneuna fila y una columna y se usa para seleccionar variables del sistema, para evaluar expresiones oprobar funciones:

Nota: Para obtener la fecha del sistema tenemos:CURRENT_DATE / CURDATE( )Para obtener la fecha y la hora:CURRENT_TIMESTAMP / NOW( )

SELECT CURRENT_DATE [ FROM DUAL ] ;

SELECT USER( ) [ FROM DUAL ] ;

SELECT 5 + 3 [ FROM DUAL ] ;

4.- En la condición de una cláusula WHERE se pueden utilizar los siguientes operadores:

4.1.- EXPRESIONES ARITMÉTICAS: + - * / DIV (división entera) MOD (resto)

Ejemplo: SELECT 25 / 2, 25 DIV 2, 25 MOD 2 ; /* FROM DUAL */

EJERCICIOS:

1.- Seleccionar los empleados cuyo salario sea mayor que el triple de su comisión.

2.- Seleccionar los clientes de Madrid y su límite de crédito en unidades de 1000.

4.2.- Comparación: < >= = y para distinto: ó !=

EJERCICIOS:

1.- Seleccionar los empleados que son analistas.2.- Obtener los clientes que no son de Sevilla.

3.- Obtener los oficios de los empleados que cobran más de 2000€.

4.3.- Booleanos: AND (&&), OR (||), NOT (!) y XOR (devuelve verdadero si uno de losoperadores es verdadero y el otro falso, devuelve falso si ambos son verdaderos o ambos falsos).

EJERCICIOS:

1.- Seleccionar los empleados existentes en los departamentos 10 y 30.

2.- Obtener los apellidos de los empleados vendedores con comisión mayor de 300.

3.- Obtener los empleados que no son vendedores o que ganan más de 2000€.


27/53

27

4.- Obtener los empleados que no son vendedores sin usar el distinto (!= ni ).

5.- Obtener los empleados que no son vendedores o no ganan más de 1500€.

4.4.- BETWEEN …… AND …… / NOT BETWEEN …… AND ……

EJERCICIOS:

1.- Obtener los empleados que cobran entre 2000 y 3500€ (inclusive).

2.- Obtener los productos cuyo precio no esté comprendido entre 300 y 500.

4.5.- IN ( , , , ) / NOT IN ( , , , )

EJERCICIOS:

1.- Obtener los departamentos de Madrid y Valencia.

2.- Obtener los empleados cuyo departamento esté en Madrid o Valencia.

3.- Obtener los pedidos cuyos productos tengan un precio mayor de 500€.

4.6.- LIKE / NOT LIKE

Especifica una comparación con algún literal del que no conocemos la informacióncompleta, para ello usamos comodines que se ponen en el literal a la derecha del LIKE. Estoscomodines se interpretan de la siguiente manera:

• El carácter “_” (subrayado) representa cualquier carácter individual en la posición en laque se encuentra.

• El carácter “%” (tanto por ciento) representa cualquier secuencia de n caracteres (donden puede ser 0).

• Todos los demás caracteres se representan a sí mismos, es decir no hay más comodines.

EJERCICIOS:

1.- Obtener los departamentos cuya localidad no contenga la letra e.

2.- Obtener los departamentos cuya localidad tenga una a en la segunda posición.

3.- Seleccionar aquellos empleados cuyo apellido empiece por 'M' y tengan un salario entre1000 y 2000 euros.

4.- Seleccionar aquellos empleados cuyo apellido termine en ‘z’.

Precedencia o prioridad en los operadores

Hay que tener en cuenta la prioridad de los operadores ya que puede afectar al resultado de

una operación. Nos indica que operación se realizará primero en una expresión cuando hay variosoperadores.

La evaluación de las operaciones en las expresiones se realiza de izquierda a derecha, perorespetando las reglas de prioridad. Por ejemplo: 8 + 4 * 5 = 28. Aunque la suma esté antes (más a


28/53

28

la izquierda) que la multiplicación, primero se realiza la multiplicación y luego la suma. Esto esdebido a que el operador * tiene mayor prioridad que el operador +.

El orden de precedencia o prioridad de los operadores determina, por tanto, el orden deevaluación de las operaciones de una expresión. La tabla siguiente muestra los operadoresdisponibles agrupados y ordenados de mayor a menor por su orden de precedencia.

Prioridad Operador Operación1º *, /, DIV Multiplicación, división2º +, - Suma, resta3º = , != , < , > , = ,

IS, LIKE , BETWEEN, INComparación

4º NOT Negación5º AND Conjunción6º OR, XOR Inclusión, exclusión

4.7.- ALL / ANY ó SOME

Se aplican a la hora de comparar una columna o literal con una lista de valores, la cuálpuede ser un conjunto de valores encerrados entre paréntesis o bien el resultado de una consulta a labase de datos. El resultado de comparar con ALL es cierto si la comparación se cumple para cadauno de los valores de la lista, mientras que el resultado de comparar con ANY o SOME se cumple sila comparación es cierta para algún elemento de la lista. Siempre se pueden sustituir utilizando lafunción MIN o MAX respectivamente, como puede verse en los siguientes ejemplos:

ALL MIN y ANY ó SOME MAX

Ej.: Seleccionar los nombres de los clientes con límite de crédito menor o igual al límite decrédito de los clientes de Madrid.

SELECT nombre FROM clientes WHERE lim_credito


29/53

29

La sintaxis ‘columna’ = NULL no está permitida, porque nada, ni siquiera un nulo, seconsidera igual a nulo.

EJERCICIOS:

1.- Obtener los empleados cuya comisión sea nula.

2.- Obtener los apellidos, el salario, la comisión y el salario + la comisión de los empleados.

Como acabamos de ver, los valores nulos en muchas ocasiones pueden representar unproblema, especialmente en columnas que contienen valores numéricos.

Para evitar estos problemas y asegurarnos de la ausencia de valores nulos en una columna yavimos que existe una restricción NOT NULL (es una orden de definición de datos) que impide quese incluyan valores nulos en una columna.

En caso de que permitamos la existencia de valores nulos hay que evitar estos problemas y

utilizar la función IFNULL, que se utiliza para devolver un valor determinado en el caso de que elvalor del argumento sea nulo. Así nos aseguramos en las operaciones aritméticas que no hay nuloscon los que operar.

Por ejemplo, IFNULL (comision,0) retornará 0 cuando el valor de la comisión sea nulo.

SELECT apellido, salario, comision, salario + IFNULL(comision, 0) FROM empleados;

4.9.- EXISTS / NOT EXISTS

La expresión WHERE EXISTS ( SELECT ... FROM ... ) resulta verdadera si y solo si elresultado de evaluar la subconsulta a la derecha del cuantificador EXISTS no es el conjunto vacío,es decir, si existe un registro que satisfaga la condición. Se utiliza en aquellas consultas tipo “paratodos”.

EJERCICIOS:

1.- Hacer una lista de los clientes para los que hay un departamento en su misma localidad.

2.- Obtener los nombres y los códigos de los clientes que han hecho pedidos.

3.- Obtener los nombres de los clientes que han pedido el producto 20.4.- Obtener los nombres de los clientes que no han hecho pedidos.

5.- En la sintaxis de la SELECT vimos que se puede consultar sobre una columna o conjuntode columnas de una o más tablas. Si intervienen más de una tabla, para recuperar todas las

columnas de una de las tablas se indica con nombre_de_tabla .*

EJERCICIO:

1.- Seleccionar todos los datos de EMPLEADOS y DEPARTAMENTOS donde elDEPARTAMENTO sea el mismo (EQUIJOIN).


30/53

30

SELECCIÓN CON ORDENACIÓN

6.- [ ORDER BY {columna | posición [ ASC | DESC ] } ]

Si no se especifica la cláusula ORDER BY, SQL no garantiza ningún orden en la tablaresultado. Sin embargo con ORDER BY podemos ordenar por uno o varios campos separados por

comas. En columna o posición se puede indicar un nombre de columna o posición seguido por eltipo de ordenamiento ASC o DESC. (Por defecto asume ASC). Posición corresponde al número deorden que ocupa en la lista de expresiones visualizadas en la SELECT.

Se puede ordenar el resultado de una SELECT por más de una columna, incluso ordenar poralgún campo que no se haya seleccionado.

EJERCICIOS:

1.- Obtener la relación alfabética de todos los empleados con todos sus datos.

2.- Obtener la clasificación alfabética de empleados por departamentos.

3.- Obtener los datos de los empleados clasificados por oficios y en orden descendente desalarios.

4.- Obtener los apellidos de los empleados junto con su salario anual (salario + comision en14 pagas) ordenado de mayor a menor por este salario total.

Podemos especificar un límite en el número de filas de salida:

SELECT * ------------- [ LIMIT [m,] n ];

Donde m es el número de fila por el que comienza la visualización (por defecto es 0, 1ª fila)y n cuantas filas va a mostrar.

EJERCICIOS:

1.- Obtener los datos de los 5 empleados con mayores salarios.

2.- Obtener la clasificación alfabética de empleados según su apellido y mostrar desde el 5ºhasta el 7º de la lista.

Nota: Si observamos la salida producida al ordenar por apellido comprobamos que se hanvisualizado 3 filas desde la 5ª (Fila 4 empezando por 0)

SELECT emp_no, apellido, salario, dep_no FROM empleados ORDER BY apellido ;

7.- SQL ofrece una serie de funciones de agregados especiales para ampliar su capacidadbásica de recuperación de información. Estas funciones operan sobre conjuntos de filas para dar unresultado por cada uno de ellos. Los grupos pueden ser una tabla entera o parte de la misma. Estasfunciones pueden aparecer en SELECT y HAVING. Se pueden dividir en dos categorías:

Funciones de columna, en las que se opera sobre un grupo de filas para obtener como

resultado un único valor.Funciones escalares, que operan sobre una única fila. Se pueden dividir en funcionesnuméricas, de tratamiento de caracteres, de tratamiento de datos de tipo fecha y hora, etc.


31/53

31

7.1.- FUNCIONES DE COLUMNA:

AVG(expr) Valor medio de “expr” ignorando los valores nulos COUNT( { * | expr } ) Número de veces que “expr” tiene un valor no nulo. La opción “*”

cuenta el número de filas seleccionadas MAX(expr) Valor máximo de “expr”

MIN(expr) Valor mínimo de “expr” STDDEV(expr) Desviación típica de “expr” sin tener en cuenta los valores nulos SUM(expr) Suma de “expr” VARIANCE(expr) Varianza de “expr” sin tener en cuenta los valores nulos.

Con COUNT se devuelve el número total de filas, sean o no distintas y contengan o nonulos. Si sólo queremos los valores distintos es necesario especificar DISTINCT. En las demásfunciones los valores nulos se eliminan antes de aplicarse la función.

Por ejemplo, COUNT(DISTICT( Nombre_Columna)) cuenta cuantos valores diferentes hayen esa columna.

Por lo general, las funciones de grupos se utilizan sobre más de un grupo de filas. Lacláusula GROUP BY establece el criterio o columnas de agrupación y se calculará el valor de lafunción para cada grupo. Pero también pueden utilizarse sin la cláusula GROUP BY y en ese casoestas funciones actúan sobre un único grupo formado por todas las filas seleccionadas.

EJERCICIOS:

1.- Obtener la masa salarial mensual de todos los empleados.

2.- Obtener los salarios máximo, mínimo y la diferencia existente entre ambos.3.- Obtener la fecha de alta más reciente.

4.- Calcular el salario medio de los empleados.

A veces hacer la media con la función AVG no da el mismo resultado que hacer la suma ydividirla por el número de filas, SUM/COUNT. COUNT(*) cuenta todas las filas de la tabla, sinconsiderar que en algunas columnas existan valores NULL. Sin embargo la función AVG si tiene encuenta las filas con valores NULL y no las considera.

Veamos un ejemplo:

SELECT AVG(comision) FROM empleados;

SELECT SUM(comision)/COUNT(comision) FROM empleados;

SELECT SUM(comision)/COUNT(*) FROM empleados;

5.- Calcular el salario medio de los empleados que sean ANALISTAS.

6.- Calcular el salario medio de los empleados del departamento 10 (EMPLEADOS)

7.- Calcular el del número de filas de la tabla EMPLEADOS

8.- Calcular del número de filas de la tabla EMPLEADOS sin comisión.¿Y si quiero saber cuantos hay con comisión?


32/53

32

9.- Calcular la varianza y la desviación típica de salario de la tabla EMPLEADOS.

10.- Obtener la media, el salario máximo, el mínimo y la suma de los salarios de losempleados que son vendedores.

7.2.- FUNCIONES ESCALARES:

7.2.1.- Funciones numéricas o aritméticas:

ABS(num) Valor absoluto.Valor absoluto de num.

CEIL( num) Función “Techo”Devuelve el entero mas pequeño mayor que num

FLOOR(num) Función “Suelo”Devuelve el entero mas grande menor que num

EXP(num) Potencia del número e

Devuelve el número e elevado a numLN(num) Logaritmo neperiano

Devuelve el logaritmo en base e de numLOG(num) Logaritmo

Devuelve el logaritmo en base 10 de numMOD(num1, num2). Módulo

Resto de la división entera de num1 por num2PI( ) Pi

Devuelve el valor de la constante PIPOWER(num1, num2). Potencia

Devuelve num1 elevado a num2.RAND( ) Número aleatorioGenera un número aleatorio entre 0 y 1

ROUND(num1, num2) RedondeoDevuelve num1 redondeado a num2 decimales.Si se omite num2 redondea a 0 decimales.

SIGN(num). SignoSi num < 0 devuelve -1,Si num = 0 devuelve 0Si num > 0 devuelve 1.

SQRT(num). Raíz cuadrada

Devuelve la raíz cuadrada de numTRUNCATE(num1, num2). Truncado

Devuelve num1 truncado a num2 decimales.Si se omite num2 trunca a 0 decimales.

EJERCICIOS:

1.- Obtener el apellido, el salario y el valor absoluto del salario – 10000, para todos losempleados.

2.- Hacer algunas pruebas con números positivos y negativos

3.- Visualizar los salarios de los empleados redondeados sin decimales

4.- Mostrar los datos de los empleados en los que su comisión es múltiplo de 100 y no seacero.


33/53

33

7.2.2.- Funciones de caracteres:

ASCII(cad1) ASCIICódigo ASCII del carácter cad1.

CHAR(num) Carácter ASCII

Devuelve el carácter cuyo código ASCII es numCONCAT(cad1,cad2[,cad3…]) ConcatenarConcatena cad1 con cad2.Si hay más, cad3…, las concatena a continuación

INSERT(cad1, pos,len,cad2) InsertarDevuelve cad1 con len caracteres desde pos en adelantesustituidos en cad2

LENGTH(cad1) LongitudDevuelve la longitud de cad1

LOCATE(cad1,cad2,pos) LocalizarDevuelve la posición de la primera ocurrencia de cad1en cad2 empezando desde pos

LOWER(cad1) MinúsculasLa cadena cad1 en minúsculas

LPAD(cad1,n,cad2) Rellenar (Izquierda)Añade a cad1 por la izquierda cad2, hasta que tenga ncaracteres.Si se omite cad2, añade blancos.

LTRIM(cad1) Suprimir (Izquierda)Suprime blancos a la izquierda de cad1

REPLACE(cad1,cad2,cad3) Reemplazar

Devuelve cad1 con todas las ocurrencias de cad2reemplazadas por cad3RPAD(cad1,n,cad2) Rellenar (Derecha)

Igual que LPAD pero por la derechaRTRIM(c1) Suprimir (Derecha)

Suprime blancos a la derecha de c1.Igual que LTRIM pero por la izquierda

SUBSTR(c1,n,m) SubcadenaDevuelve una subcadena a partir de c1 comenzando enla posición n tomando m caracteres

UPPER(cad1) Mayúsculas

La cadena cad1 en mayúsculas

En ORACLE: OPERACIÓN DE CONCATENACIÓN ( || ) Conector de Springs

EJERCICIOS:

1.- Obtener la longitud de APELLIDO en EMPLEADOS.

2.- Obtener el valor ASCII de las letras ‘A’ y ‘a’.

3.- Visualizar los tres primeros caracteres de los apellidos de los empleados seguidos de unpunto.

4.- Visualizar los nombres de los departamentos cuyo nombre tenga de más de 6 caracteresreemplazando las letras ‘A’ por ‘*’


34/53

34

7.2.3.- Funciones de fecha y hora:

ADDDATE(Fecha, Num) Incremento de díasDevuelve Fecha incrementada en Num días

SUBDATE(Fecha, Num) Decremento de días

Devuelve Fecha decrementada en Num díasDATE_ADD(Fecha, INTERVAL N Formato)

IncrementoDevuelve Fecha incrementada en N veces loindicado en FormatoFormato puede ser entre otros: DAY, WEEK,MONTH, YEAR, HOUR, MINUTE, SECOND

DATE_SUB(Fecha, INTERVAL N Formato)

DecrementoDevuelve Fecha decrementada en N veces loindicado en FormatoFormato puede ser entre otros: DAY, WEEK,MONTH, YEAR, HOUR, MINUTE, SECOND

DATEDIFF(Fecha1,Fecha2) Diferencia de fechasDevuelve el número de días entre Fecha1 yFecha2

DAYNAME(Fecha) Nombre del día de la semanaDevuelve el nombre del día de la semana de Fecha

DAYOFMONTH(Fecha) Día del mesDevuelve el número del día del mes de Fecha

DAYOFWEEK(Fecha) Día de la semanaDevuelve el número del día de la semana de Fecha(1-Domingo, 2-Lunes.....7-Sábado)

DAYOFYEAR(Fecha) Día del añoDevuelve el número de día del año (de 1 a 366)WEEKOFYEAR(Fecha) Semana

Devuelve el número de semana (de 1 a 53)MONTH(Fecha) Mes

Devuelve el número de mes de Fecha (de 1 a 12)YEAR(Fecha) Año

Devuelve el número de año con 4 dígitos de Fecha(de 0000 a 9999)

HOUR(Tiempo) HoraDevuelve la hora de Tiempo (de 0 a 23)

MINUTE(Tiempo) MinutosDevuelve los minutos de Tiempo (de 0 a 59)

SECOND(Tiempo) SegundosDevuelve los segundos de Tiempo (de 0 a 59)

CURDATE( ) Devuelve la fecha actual con el formato‘YYYY-MM-DD’

CURTIME( ) Devuelve la hora actual con el formato ‘HH:MM:SS’SYSDATE( ) Devuelve la fecha y la hora actual con el formato

‘YYYY-MM-DD HH:MM:SS’

Para la conversión de fechas a otro tipo de datos:

DATE_FORMAT(Fecha,Formato)

Devuelve una cadena de caracteres con la fecha con el formatoespecificado.Formato es una cadena de caracteres (irá entre comillas) con lassiguientes máscaras:


35/53

35

%a%b%c%e%H%h

%i%j%M%m%p%r%s%T%u%W%w

%Y%y

Abreviatura (3 letras) del nombre del día de la semanaAbreviatura (3 letra ) del nombre mesNúmero del mes (1 a 12)Número del día del mes (0 a 31)Número de la hora en formato 24 horas (00 a 23)Número de la hora en formato 12 horas (01 a 12)

Número de minutos (00 a 59)Número del día del año (001 a 366)Nombre del mesNúmero de mes (01 a 12)Am o PMHora en formato 12 horas (hh:mm seguido de AM o PM)Número de segundos (00 a 59)Hora en formato 24 horas (hh:mm:ss)Número de semana en el año (00 a 53)Nombre del día de la semanaNúmero del día de la semana (0:domingo a 6:Sábado)

Número de año con cuatro dígitosNúmero de año con dos dígitos

Nota: estas funciones de fechas varían bastante de un sistema gestor a otro.

EJERCICIOS:

1.- Obtener la fecha y la hora del sistema.

2.- Visualiza la suma de 12 meses a la fecha_alta de los empleados del departamento 10.

3.- Obtener el último día del mes de cada una de las fechas de alta de los empleados deldepartamento 30 (usar LAST_DAY( fecha) ).

4.- Obtener los días transcurridos desde vuestra fecha de nacimiento.¿Y si quiero saber cuantos años son?

5.- Si hoy es jueves XXXX/XX/XX (fecha del sistema) ¿Qué fecha será el próximo jueves?

6.- Visualizar la fecha que será dentro de dos semanas.

7.- Visualizar el nombre y la fecha de alta de los empleados con el formato - de de .

8.- Mostrar los datos de los empleados que entraron en la empresa en martes.

9.- Mostrar para cada empleado su apellido junto con el número de trienos que tiene (setiene un trienio por cada tres años en la empresa).

10.- Mostrar los empleados que llevan más de 30 años en la empresa.

11.- Visualizar la fecha de 4/10/1997 con el formato ,

de de .


36/53

36

7.2.4.- Funciones de comparación:

GREATEST(lista de valores) Mayor de la listaDevuelve el valor más grande de una lista de columnaso expresiones de columna

LEAST(lista de valores) Menor de la lista

Devuelve el valor más pequeño de una lista de columnaso expresiones de columnaIFNULL(exp1, exp2) Conversión de nulos

Si exp1 es nulo devuelve exp2, sino devuelve exp1ISNULL(exp) Comprobación de nulo

Devuelve 1(True) si exp es NULL y 0 (False) en casocontrario

STRCMP(cad1,cad2) Comparación de cadenasDevuelve 1(True) si cad1 y cad2 son iguales, 0(False)si no lo son y NULL si alguna de ellas es nula

EJERCICIOS:

1.- Obtener el último nombre (orden alfabético) de una lista de nombres.¿Y si quiero el último nombre de los empleados?

2.- Visualizar para cada empleado el valor que sea mayor entre su salario y su comisión.

3.- Mostar los empleados en los que la suma de su salario más su comisión es menor de2.000 euros.

7.2.5.- Otras funciones:

DATABASE( ) Base de datosNombre de la base de datos actual

USER( ) UsuarioDevuelve el usuario y el host de la sesión: usuario@host

VERSION( ) VersiónDevuelve una cadena indicando la versión que estamos utilizando

EJERCICIOS:

1.- Obtener la base de datos, el usuario y la versión en uso.

SELECCIÓN CON AGRUPAMIENTOS

8.- Mediante la cláusula GROUP BY podemos agrupar los datos de una tabla o conjunto detablas formando grupos. Se pueden usar las funciones de grupo para devolver información paracada grupo.

Sirve para calcular propiedades de uno o más conjuntos de filas (un grupo).Podemos utilizar la posición que ocupa la expresión por la que queremos agrupar en la lista

de expresiones visualizadas.

El funcionamiento de la sentencia SELECT con cláusulas de agrupamiento es el siguiente:• primero realiza una selección de filas según la cláusula WHERE• forma grupos según la cláusula GROUP BY• hace una selección de grupos según la cláusula HAVING.


37/53

37

Es importante tener en cuenta que, en caso de selección con cláusula de agrupación, solotiene sentido mostrar expresiones que contengan columnas de agrupación y/o funciones degrupo. Así mimo, si se utilizan funciones de agrupación sin la cláusula GROUP BY no puedenmostrarse el resto de las filas de la tabla.

La cláusula HAVING actúa como un filtro sobre el resultado de agrupar las filas en

grupos, a diferencia de la cláusula WHERE que actúa sobre las filas antes de la agrupación.

Todas las columnas de la SELECT que no son funciones de grupo deben aparecer enGROUP BY.

Con WHERE se pueden excluir filas antes de agrupar.

Se pueden realizar GROUP BY sobre múltiples columnas, es decir, se pueden devolverresultados resumen para grupos y subgrupos indicando más de una columna en GROUP BY(separándolas por comas).

EJERCICIOS:

1.- Obtener los salarios medios por departamento.

2.- Obtener cuántos empleados hay en cada oficio.

3.- Agrupar las filas por departamento, y luego, dentro de los departamentos, agrupar poroficio, indicando la suma de los salarios.

Probar que pasa si cambiamos el orden dentro del GROUP BY.

9.- La cláusula HAVING es a los grupos lo que WHERE es a las filas, es decir sirve paraeliminar grupos de la misma manera que WHERE sirve para eliminar filas.

El proceso sería agrupar registros, aplicar alguna función de grupo en ellos y por últimovisualizar los que cumplan la condición de grupo de la cláusula HAVING.

EJERCICIOS:

1.- El ejemplo anterior para aquellos que la suma de los salarios sea > 2000.

2.- Seleccionar los oficios que tengan dos o más empleados:

3.- Seleccionar los oficios que tengan dos o más empleados, cuyo salario supere los 1400€.

Ejemplo: de una SELECT con todo completo.

SELECT OFICIO, SUM(SALARIO) FROM EMPLEADOSWHERE OFICIO ‘VENDEDOR’GROUP BY OFICIOHAVING SUM(SALARIO) > 5000ORDER BY SUM(SALARIO)LIMIT 2;

Too:SELECT OFICIO, SUM(SALARIO) FROM EMPLEADOSGROUP BY OFICIOHAVING SUM(SALARIO) > 5000 AND OFICIO ‘VENDEDOR’ORDER BY SUM(SALARIO)


38/53

38

LIMIT 2;

EJERCICIOS:

1.- Seleccionar el nº de departamento con menor salario medio.

2.- Seleccionar el nº de departamento con mayor número de empleados.

CONSULTAS MULTITABLAS

10.- Mediante el operador UNION podemos unir dos o más tablas.

Ej.: Obtener las localidades de los departamentos o de los clientes con límite de créditomayor que 6.000 o ambas.

SELECT LOCALIDAD FROM DEPARTAMENTOSUNION

SELECT LOCALIDAD FROM CLIENTES WHERE LIM_CREDITO > 6000 ;

11.- Mediante la cláusula SELECT se pueden reunir cualquier número de tablas. Cuando senecesitan datos de más de una tabla, se utiliza una condición JOIN. Esta combinación normalmentese hace a través de los atributos comunes, es decir, de las claves ajenas.

La sintaxis es la siguiente:

SELECT [ALL/DISTINCT] ExpresionColumna [,ExpresionColumna .....]FROM NombreTabla [AliasTabla] [ , NombreTabla [AliasTabla].....][WHERE Condicion_Composicion ]

donde:Condicion_Composicion es una condición que selecciona las filas de la composición de las tablas.

La forma más habitual suele ser:

SELECT TABLA1.COLUMN1, TABLA2.COLUMN2, ...FROM TABLA1, TABLA2

WHERE TABLA1.COLUMN1 = TABLA2.COLUMN2 ;

Si hay columnas con el mismo nombre en las distintas tablas de la FROM, para poderdiferenciarlas deben identificarse con Nombre_tabla . Nombre_columna.

Para evitar que los nombres de los atributos sean tan largos, al tener que poner el nombre dela tabla a la que pertenece, se pueden utilizar los alias de las tablas, que se definen en la cláusulaFROM. Estos alias sólo se pueden utilizar en la consulta en la que se definen.

EJERCICIOS:

1.- Obtener el nº del empleado, su nombre de la tabla empleados y el nº de departamento, sunombre y localidad de la tabla departamentos cuando los departamentos coinciden.

2.- Obtener los distintos departamentos existentes (nº y nombre) en la tabla de empleados.

3.- Mostrar el producto, su descripción y precio de todos los pedidos almacenados.


39/53

39

4.- ¿Y si solo quiero la información para los pedidos del cliente número 106?.

5.- ¿Y si piden la información del cliente llamado SIGNOLOGIC S.A.?

Combinaciones basadas en desigualdad

6.- Listar los empleados de los departamentos diferentes al de VENTAS.

7.- Listar los empleados de departamentos con códigos mayores que el código deldepartamento de contabilidad.

8.- Listar los empleados de departamentos con códigos menores que el código deldepartamento de Valencia.

12.- Combinación externa (OUTER JOIN)

Hay una variedad de combinación de tablas que se llama OUTER JOIN y que nos permite

seleccionar algunas filas de una tabla aunque éstas no tengas correspondencia con las filas de la otratabla con la que se combina.

Su formato es el siguiente:

SELECT [ALL | DISTINCT] ExpresionColumna [, ExpresionColumna .....]FROM NombreTabla [AliasTabla]{ LEFT | RIGHT [OUTER] JOIN NombreTabla [AliasTabla].....}ON CondicionComposicion ;

donde:LEFT | RIGHT [OUTER] JOIN indica que es un join externo y si la extensión del producto

de las tablas se quiere realizar por la izquierda o por la derecha.Condicion_Composicion es la misma condición de composición anterior, pero escrita aquí

en lugar de en la cláusula WHERE.

El funcionamiento de un join externo es el siguiente:

LEFT JOIN: join donde se obtienen todas las filas de la tabla de la izquierda, aunque notenga correspondencia en la tabla de la derecha.

Realiza el producto cartesiano de las tablas que se indican, aplica la condición decomposición (expresada en la cláusula ON) al resultado de este producto y añade, por cada fila de la

tabla de la izquierda que no tenga correspondencia en la tabla de la derecha, una fila con los valoresde la tabla de la izquierda y en la tabla de la derecha valores NULL en todas las columnas.

RIGHT JOIN: join donde se obtienen todas las filas de la tabla de la derecha, aunque notengan correspondencia en la tabla de la izquierda.

Realiza el producto cartesiano de las tablas que se indican, aplica la condición decomposición (expresada en la cláusula ON) al resultado de este producto y añade, por cada fila de latabla de la derecha que no tenga correspondencia en la tabla de la izquierda, una fila con los valoresde la tabla de la derecha y en la tabla de la izquierda valores NULL en todas las columnas.

Por ejemplo si queremos visualizar los datos de los departamentos y de sus empleados,

visualizando también los departamentos que no tengan empleados.

SELECT dnombre Departamento, localidad, emp_no "Nº empleado", apellidoFROM departamentos d LEFT JOIN empleados e ON d.dep_no=e.dep_no;


40/53

40

Aparecerán todas las filas de la tabla DEPARTAMENTOS, tanto si tienen correspondenciaen la tabla EMPLEADOS como si no la tienen. Los departamentos que no tengan empleadostambién aparecerían. El departamento de PRODUCCIÓN no tiene ningún empleado asignado y seañade una fila en la tabla empleados con todos los campo con valor NULL en correspondencia.

Obtendremos el mismo resultado si cambiamos LEFT por RIGHT y el orden de las tablas:

SELECT dnombre Departamento, localidad, emp_no "Nº empleado", apellidoFROM empleados e RIGHT JOIN departamentos d ON d.dep_no=e.dep_no;

Hay casos en los que hacer el OUTER JOIN obtendremos el mismo resultado con unacombinación natural, ya que no hay filas sin correspondencia en la tabla de la correspondencia.

SELECT emp_no "Nºempleado", apellido, dnombre "Departamento", localidadFROM departamentos d RIGHT JOIN empleados e ON d.dep_no=e.dep_no;

Aparecerán todas las filas de la tabla EMPLEADOS, tanto si tienen correspondencia en la

tabla DEPARTAMENTOS como si no. Los empleados que no tuviesen departamento asignadotambién aparecerían. En este ejemplo como todos los empleados tienen departamento asignado elresultado es el mismo.

EJERCICIOS:

1.- Obtener los departamentos con su nombre y localidad y el número de empleadostrabajando en ellos, incluyendo los que no tienen empleados.

2.- Obtener los productos, su descripción y la suma de las unidades pedidas pero incluyendolos que no han sido pedidos.

13.- Además es posible reunir una tabla consigo misma:

EJERCICIOS:

1.- Obtener la lista de los empleados con los nombres de sus directores.

Cada empleado de la tabla tiene una columna para su número de empleado (emp_no) y otrapara el número de empleado de su director (director). A partir del dato de la columna director de unempleado se puede acceder a otro empleado que contenga el mismo dato en su columna emp_no.

Las dos filas de la tabla se están relacionando a través de las columnas director y emp_no.El uso de alias es obligado por tratarse de la misma tabla y coincidir los nombres de las columnas.

2.- Obtener la lista de empleados con los nombres de sus directores, incluyendo alPRESIDENTE. (Ejemplo con OUTER JOIN) .

3.- Obtener los jefes de los empleados cuyo oficio sea el de VENDEDOR.

14.- SUBCONSULTAS.

Una subconsulta es una sentencia SELECT incluida en otra cláusula SQL. Se utilizan

cuando se necesitan seleccionar filas de una tabla con una condición que depende de los datos de lamisma tabla o de otra u otras tablas. Es decir, una subconsulta consiste en utilizar los resultados deuna consulta dentro de otra, que se considera la principal. Esta posibilidad fue la razón original parala palabra “estructurada” que da el nombre al SQL de Lenguaje de Consultas Estructuradas(Structured Query Language).


41/53

41

Ejemplo: Supongamos que se quiere localizar los empleados cuyo sueldo es superior al de“Martín”. Sería necesario dos consultas:

• Primero averiguar el salario de Martín.• Luego buscar sueldos superiores al resultado de la consulta anterior.

Este problema se soluciona combinando las dos consultas e incluyendo una dentro de laotra. La consulta más interna o subconsulta devuelve un valor que será usado por la consultaprincipal.

La subconsulta puede incluirse en una de las siguientes cláusulas:

• WHERE• HAVING• FROM de SELECT o DELETE.

La sintaxis con WHERE es la siguiente:

SELECT lista_atributos FROM tablaWHERE expr_operador [ > < = .... IN NOT IN .... ]( SELECT expresión FROM tabla ) ;

Una subconsulta tiene que ir entre paréntesis y no debe contener ORDER BY. Éste sólopuede aparecer en la consulta principal.

Existen dos tipos de subconsulta:

1.- MONO-REGISTRO. Son las que devuelven un solo registro.

Dentro de éstas tenemos:Las que utilizan un operador de comparación: =, , , ....Las que utilizan funciones de grupo: AVG, SUM, COUNT, MAX y MIN.

2.- MULTIREGISTRO. Devuelven más de un registro. Estas subconsultas suelen utilizar eloperador IN.

EJEMPLOS:

- Subconsulta que devuelve una sola expresión. Obtener el nombre del departamento dondetrabaja GARRIDO.

SELECT dnombre FROM departamentos WHERE dep_no =(SELECT dep_no FROM empleados WHERE apellido = 'GARRIDO') ;

La subconsulta devuelve una sola expresión dep_no, que en este caso el valor deldepartamento de GARRIDO que la consulta compara con el correspondiente en la tabladepartamentos.

- Subconsulta que devuelven más de una expresión. Obtener los empleados que tengan elmismo oficio y departamento que ALONSO.

SELECT emp_no, apellido, oficio, dep_no FROM empleados WHERE (dep_no, oficio) =(SELECT dep_no, oficio FROM empleados WHERE apellido = 'ALONSO') ;


42/53

42

La subconsulta devuelve dos expresiones, dep_no y oficio (en este caso formadas por unacolumna cada una) correspondientes al departamento y oficio de ALONSO y la consulta lo comparacon dos columnas dep_no y oficio, de cada una de las filas de la tabla.

- Subconsulta multiregistro. Obtener los empleados que pertenezcan a departamentos que no

estén en Madrid.

SELECT * FROM empleados WHERE dep_no IN(SELECT dep_no FROM departamentos WHERE localidad ‘MADRID’) ;

La subconsulta devuelve varios valores por lo que tenemos que usar IN y no = paraconsultar por el resultado de una lista de valores.

EJERCICIOS:

1.- Obtener todos los empleados que tienen el mismo oficio que GARRIDO.

2.- Obtener información de los empleados que ganan más que cualquier empleado deldepartamento 30.

3.- Visualizar el número de VENDEDORES del departamento VENTAS.

4.- Visualizar la suma de los salarios para cada oficio de los empleados del depar

Documents

Tema5-6-7 Apuntes.pdf