Manual Práctico de MySQL

Aplicaciones de Base de Datos

ADMINISTRACIÓN DE MYSQL MEDIANTE CONSOLA

MySQL tiene un programa, que se llama con el mismo nombre de la base de datos (mysql) que sirve para gestionar la base datos por línea de comandos.

El directorio puede variar, por ejemplo, puede estar localizado en la raíz del disco C:, o en cualquier otro lugar donde podamos haber instalado MySQL. Para acceder a la consola de MySQL en Windows tendremos que estar situados dentro de ese directorio.

Para el efecto utilizaremos el Wamp Server 2.0 el cual al ser instalado se ubica en:

C:\wamp\bin\mysql\mysql5.1.36\bin>_

O seleccionar:

o

En Linux, por supuesto, también se puede acceder a MySQL por línea de comandos. Posiblemente desde cualquier directorio podamos acceder a la consola de MySQL, sin necesidad de situarse en el directorio donde esté instalado.

CONECTAR CON EL SERVIDOR MYSQL

Lo primero que tendremos que hacer es conectar con el sistema gestor de MySQL. Para ello, simplemente tenemos que escribir el comando "mysql" e indicarle unas opciones de conexión.

% mysql

Con el "%" expresamos el principio de la línea de comandos. Ese principio es el prompt que tengamos en nuestra consola de Linux o MsDOS, que puede ser algo como (C:\wamp\bin\mysql\mysql5.1.36\bin>). El carácter "%", por tanto, no tenemos que escribirlo.

Con esa sentencia se conecta uno con la base de datos con los parámetros por defecto. Es decir, al servidor local, con usuario y password igual a cadenas vacías.

Lo más normal es que tengamos que indicar algún otro dato para conectar con la base de datos, como el usuario, la clave o la dirección del servidor con el que queremos conectar. La sintaxis sería la siguiente:

% mysql -h nombre_servidor -u nombre_usuario -p

Si deseamos conectarnos a la base de datos en local y con nombre de usuario root tendríamos que escribir:

1 Ing. Gabriel Demera Ureta MgSc.


% mysql -h localhost -u root -p

Lo primero que nos preguntará será el password para el usuario root. Una vez introducida la clave, ya estaremos dentro de la línea de comandos de MySQL. Con ello el prompt cambiará a algo como esto:

mysql>

Podríamos haber indicado la contraseña directamente en la línea de comandos para iniciar la sesión con MySQL, pero esto se desaconseja por razones de seguridad. De todos modos, la sintaxis hubiera sido:

% mysql -h localhost -u root -pmi_clave

Nos fijamos que entre -h y el nombre del host hay un espacio en blanco, igual que entre -u y el nombre de usuario. Sin embargo, entre -p y la clave no debemos poner ningún espacio.

DENTRO DE LA CONSOLA DE MYSQL

Una vez dentro, tendremos a nuestra disposición todas las sentencias de MySQL para el trabajo con la base de datos y el lenguaje SQL.

Lo más normal es que necesites conectarte con una base de datos en concreto, de entre todas las que puedes tener creadas en tu servidor MySQL. Eso se hace con el comando use, seguido del nombre de la base de datos que deseas conectar.

mysql> use mibasedatos;

Esto nos conectaría con la base de datos llamada "mibasedatos".

ATENCIÓN: Hay que fijarse que todas las sentencias dentro de la línea de comandos de MySQL acaban en ";". Si no colocamos el punto y coma, lo más seguro es que NO se ejecute el comando y nos vuelva a salir el prompt para que sigamos introduciendo el comando. Si lo que queríamos era ejecutar la sentencia que habíamos escrito antes, con simplemente entrar el ";" será suficiente. Es decir, no debemos escribir de nuevo la sentencia entera, sólo el ";" y volver a apretar "enter".

Si queremos ver una lista de las bases de datos alojadas en nuestro servidor podemos escribir el comando show databases. Así:

mysql>show databases;

Con esto nos mostraría una lista de las bases de datos de nuestro servidor. Algo como esto:

mysql> show databases -> ;

5 rows in set (0.02 sec)



Si queremos crear una base datos, podremos hacerlo con el comando "create database" seguido del nombre de la nueva base de datos.

mysql> create database miprueba;

mysql>create database IF NOT EXISTS miprueba;

Eso nos creará una base de datos que se llama "miprueba". Si ya existe una base de datos con ese nombre, MySQL devolverá un error. Si queremos crear una base de datos solo cuando no exista ya (y por lo tanto no obtener este error) podemos añadir el texto IF NOT EXISTS que evita el error en caso de existir

Si queremos luego usar esa base de datos escribiríamos:mysql> use miprueba;

Lógicamente, esta base de datos recién creada estará vacía, pero si estuviéramos usando una base de datos ya creada y queremos ver las tablas que tiene escribiríamos el comando "show tables".

mysql> show tables; Si no hay tablas, nos dirá algo como "Empty set", pero si tenemos varias tablas dadas de alta en la base de datos que estamos usando, nos saldrá una lista de ellas:

mysql> show tables;

2 rows in set (0.00 sec) Ahora, si deseamos obtener información sobre una tabla, para saber qué campos tiene y de qué tipo, podremos utilizar el comando describe seguido del nombre de la tabla.

mysql> describe administrador;


En este apartado se resumen las principales sentencias SQL que pueden ser utilizadas en el gestor de base de datos MySQL, por ejemplo también se puede utilizar SHOW FULL COLUMNS FROM tabla; para mostrar la estructura de una tabla.

PARA SALIR DE LA LÍNEA DE COMANDOS DE MYSQL Una vez hemos terminado de trabajar con MySQL, si queremos cerrar la conexión con el servidor, simplemente escribimos "quit" o “\q” desde el prompt de MySQL:

mysql> quit

CREACIÓN DE TABLASUna tabla es utilizada para organizar y presentar información. Las tablas se componen de filas y columnas de celdas que se pueden rellenar con información, las tablas se componen de dos estructuras:



Registro: se la podría definir como una fila que contiene datos de los mismos tipos que las demás filas. Ejemplo: en una tabla que tiene columnas como nombres y direcciones, cada fila contendrá un nombre y una dirección.Campo: es cada una de las columnas que forman la tabla. Contienen datos de tipo diferente a los de otros campos. En el ejemplo anterior, un campo contendrá un tipo de datos único, como una dirección, o un número de teléfono, un nombre, etc.

Cada tabla creada debe tener un nombre único en la cada Base de Datos, haciéndola accesible mediante su nombre o su seudónimo (Alias). Las tablas son los objetos principales de bases de datos que se utilizan para guardar gran cantidad de datos.

TIPOS DE DATOS DE MYSQL

Después de la fase de diseño de una base de datos, en necesario crear las tablas correspondientes dentro de la base de datos. Para cada campo o columna de cada una de las tablas, es necesario determinar el tipo de datos que contiene, para de esa forma ajustar el diseño de la base de datos, y conseguir un almacenamiento óptimo con la menor utilización de espacio. Los tipos de datos que puede haber en un campo, se pueden agrupar en tres grandes grupos:

1.- TIPOS NUMÉRICOS: Existen tipos de datos numéricos, que se pueden dividir en dos grandes grupos, los que están en coma flotante (con decimales) y los que no (enteros).

TinyInt: es un número entero con o sin signo. Con signo el rango de valores válidos va desde -128 a 127. Sin signo, el rango de valores es de 0 a 255 Bit ó Bool: un número entero que puede ser 0 ó 1SmallInt: número entero con o sin signo. Con signo el rango de valores va desde -32768 a 32767. Sin signo, el rango de valores es de 0 a 65535. MediumInt: número entero con o sin signo. Con signo el rango de valores va desde -8.388.608 a 8.388.607. Sin signo el rango va desde 0 a16777215. Integer, Int: número entero con o sin signo. Con signo el rango de valores va desde -2147483648 a 2147483647. Sin signo el rango va desde 0 a 429.4967.295 BigInt: número entero con o sin signo. Con signo el rango de valores va desde -9.223.372.036.854.775.808 a 9.223.372.036.854.775.807. Sin signo el rango va desde 0 a 18.446.744.073.709.551.615. Float: número pequeño en coma flotante de precisión simple. Los valores válidos van desde -3.402823466E+38 a -1.175494351E-38, 0 y desde 1.175494351E-38 a 3.402823466E+38. xReal, Double: número en coma flotante de precisión doble. Los valores permitidos van desde -1.7976931348623157E+308 a -2.2250738585072014E-308, 0 y desde 2.2250738585072014E-308 a 1.7976931348623157E+308Decimal, Dec, Numeric: Número en coma flotante desempaquetado. El número se almacena como una cadena

2.- TIPOS FECHA:

A la hora de almacenar fechas, hay que tener en cuenta que Mysql no comprueba de una manera estricta si una fecha es válida o no. Simplemente comprueba que el mes está comprendido entre 0 y 12 y que el día está comprendido entre 0 y 31.



Date: tipo fecha, almacena una fecha. El rango de valores va desde el 1 de enero del 1001 al 31 de diciembre de 9999. El formato de almacenamiento es de año-mes-dia

DateTime: Combinación de fecha y hora. El rango de valores va desde el 1 de enero del 1001 a las 0 horas, 0 minutos y 0 segundos al 31 de diciembre del 9999 a las 23 horas, 59 minutos y 59 segundos. El formato de almacenamiento es de año-mes-dia horas:minutos:segundos

TimeStamp: Combinación de fecha y hora. El rango va desde el 1 de enero de 1970 al año 2037. El formato de almacenamiento depende del tamaño del campo:

Tamaño Formato

14AñoMesDiaHoraMinutoSegundo

aaaammddhhmmss


aammddhhmmss

8 ñoMesDia aaaammdd

6 AñoMesDia aammdd

4 AñoMes aamm

2 Año aa

Time: almacena una hora. El rango de horas va desde -838 horas, 59 minutos y 59 segundos a 838, 59 minutos y 59 segundos. El formato de almacenamiento es de 'HH:MM:SS'

Year: almacena un año. El rango de valores permitidos va desde el año 1901 al año 2155. El campo puede tener tamaño dos o tamaño 4 dependiendo de si queremos almacenar el año con dos o cuatro dígitos.

Tipo de CampoTamaño de

Almacenamiento

DATE 3 bytes

DATETIME 8 bytes

TIMESTAMP 4 bytes

TIME 3 bytes

YEAR 1 byte

3.- TIPOS DE CADENA:

Char(n): almacena una cadena de longitud fija. La cadena podrá contener desde 0 a 255 caracteres. VarChar(n): almacena una cadena de longitud variable. La cadena podrá contener desde 0 a 255 caracteres.Dentro de los tipos de cadena se pueden distinguir otros dos subtipos, los tipo Test y los tipo BLOB (Binary large Object)La diferencia entre un tipo y otro es el tratamiento que reciben a la hora de realizar ordenamientos y comparaciones. Mientras que el tipo test se ordena sin tener en cuenta las Mayúsculas y las minúsculas, el tipo BLOB se ordena teniéndolas en cuenta.



Los tipos BLOB se utilizan para almacenar datos binarios como pueden ser ficheros. TinyText y TinyBlob: Columna con una longitud máxima de 255 caracteres.Blob y Text: un texto con un máximo de 65535 caracteres. MediumBlob y MediumText: un texto con un máximo de 16.777.215 caracteres. LongBlob y LongText: un texto con un máximo de caracteres 4.294.967.295. Hay que tener en cuenta que debido a los protocolos de comunicación los paquetes pueden tener un máximo de 16 Mb. Enum: campo que puede tener un único valor de una lista que se especifica. El tipo Enum acepta hasta 65535 valores distintos Set: un campo que puede contener ninguno, uno ó varios valores de una lista. La lista puede tener un máximo de 64 valores.

Tipo de campo Tamaño de Almacenamiento

CHAR(n) n bytes

VARCHAR(n) n +1 bytes

TINYBLOB, TINYTEXT Longitud+1 bytes

BLOB, TEXT Longitud +2 bytes

MEDIUMBLOB, MEDIUMTEXT Longitud +3 bytes

LONGBLOB, LONGTEXT Longitud +4 bytes

ENUM('value1','value2',...)1 ó dos bytes dependiendo del número de

valores

SET('value1','value2',...)1, 2, 3, 4 ó 8 bytes, dependiendo del número

de valores

Diferencia de almacenamiento entre los tipos Char y VarChar

Valor CHAR(4)Almace

namientoVARCHAR(4)

Almacenamiento

'' '' 4 bytes " 1 byte

'ab' 'ab ' 4 bytes 'ab' 3 bytes

'abcd' 'abcd' 4 bytes 'abcd'

'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

La sintaxis general para crear una tabla es:CREATE TABLE nombre_tabla(nombre_campo1 TIPO_COLUMNA(), nombre_campo1 TIPO_COLUMNA(), nombre_campo1 TIPO_COLUMNA(), …..);

Supongamos que se desea crear una tabla de nombre PRUEBA cuya clave primaria es id (campo que identifica unívocamente a la tabla) que va incrementando su valor automáticamente cada vez que insertamos un nuevo valor en la tabla, un campo nombre que es de tipo cadena de caracteres de como máximo 60 caracteres y un campo sexo que es de tipo cadena de caracteres:

CREATE TABLE prueba( id int NOT NULL AUTO_INCREMENT, nombre VARCHAR(60) default NULL, sexo VARCHAR(1), PRIMARY KEY (id) ) TYPE=INNODB;



TYPE=INNODB es para especificar el tipo de motor de base de datos que utilizará para crear la tabla, también se puede crear una tabla utilizando el motor que se encuentra ejecutando por defecto con el siguiente formato:

CREATE TABLE usuario ( id_usuario INT PRIMARY KEY AUTO_INCREMENT, nombre VARCHAR(50) NOT NULL, direccion VARCHAR(50) NOT NULL, ciudad VARCHAR(20) NOT NULL, edad TINYINT NOT NULL );

Asumiendo que se le olvidó crear un campo fecha de registro a la tabla usuario usted tendría que utilizar el comando ALTER TABLE, por ejemplo:

ALTER TABLE usuario ADD Fecha_Registro DATE; DESCRIBE usuario;

Para añadir un campo 'email' como un índice del tipo único (no puede haber dos iguales).ALTER TABLE usuario ADD email varchar(50);ALTER TABLE usuario ADD UNIQUE(email);DESCRIBE usuario;

Para modifica el tamaño de la columna como email, se puede utilizar: ALTER TABLE usuario MODIFY email varchar(150);DESCRIBE usuario;

Cambia el nombre de la columna 'ciudad' al nuevo nombre ' accesos_permitidos' que incluye la definición del tipo de dato:

ALTER TABLE usuario CHANGE ciudad accesos_permitidos int(4);DESCRIBE usuario;

Para cambia solo el tipo de datos de la columna 'edad' y especifica que no admite nulos:ALTER TABLE usuario MODIFY edad FLOAT(6,2) NOT NULL;

En la tabla 'usuario' cualquiera que sea la columna que tenga 'AUTO_INCREMENT' en sus propiedades (solo puede haber una), los nuevos registros comenzarán a partir de '1000' o cualquier número indicado, no es posible utilizar un valor ya existente.

1. ALTER TABLE usuario AUTO_INCREMENT=1000;2. INSERT INTO usuario(nombre,direccion,accesos_permitidos,edad,Fecha_Registro,email) VALUES (‘Carlos’,’portoviejo’,20,16,’2013-09-26’,’[email protected]’);3. SELECT * FROM usuario;

Al aplicar la sentencia número 3 SELECT, notará que el primer registro ya no empezará por el índice 1 sino que comenzará por el 1000.

Para eliminar la columna 'edad' de la tabla 'usuario', usted tendría que realizar lo siguiente:ALTER TABLE usuario DROP COLUMN edad;DESCRIBE usuario;

Para eliminar varias columnas se utilizará el siguiente formato:ALTER TABLE Nombre_Tabla DROP COLUMN campo1, DROP COLUMN campo2;



Para ejemplificar la creación de una base de datos con sus respectivas tablas, tomaremos como referencia el almacenamiento de registros datos para una unidad educativa, para el siguiente análisis considere que un alumno puede ser con el tiempo ser un padre de familia, o un docente de la misma institución:

DROP DATABASE IF EXISTS Unidad;

Create Database if not exists Unidad;

use Unidad;

create table if not exists personas(IdPersona int primary key auto_increment,ApellidoPaterno Varchar(50) not null,ApellidoMaterno Varchar(50) not null,Nombres Varchar(50) not null,Direccion Varchar(150),telefono Varchar(15),sexo enum('M','F'),FechaNacimiento date,EstadoCivil enum('S','C','D','V','U'));

create table if not exists profesores(IdPersona int,cargo Varchar(50) not null,Titulacion Varchar(50),Fechaingreso date,index(Idpersona),foreign key(IdPersona) references personas(Idpersona));

create table if not exists padres(IdPersona int,cargasfamiliares int(2),TelefonoTrabajo varchar(15),DireccionTrabajo varchar(100),ocupacion varchar(80),index(Idpersona),foreign key(IdPersona) references personas(Idpersona));

create table if not exists Estudiante(IdPersona int not null,IdPersonaPapa int not null,IdPersonaMama int not null,Procedencia varchar(80),index(Idpersona),foreign key(IdPersona) references personas(Idpersona),foreign key(IdPersonaPapa) references personas(Idpersona),foreign key(IdPersonaMama) references personas(Idpersona));

PARA INGRESAR DATOS EN LAS TABLAS CREADAS:

Insert Into personas(ApellidoPaterno,ApellidoMaterno,Nombres,Direccion,telefono,sexo,FechaNacimiento,EstadoCivil) Values ('Demera', 'Ureta', 'Gabriel', 'Cdla. Parque Forestal', '052123456','M','1974-03-18','C');Insert Into personas(ApellidoPaterno,ApellidoMaterno,Nombres,Direccion,telefono,sexo,FechaNacimiento,EstadoCivil) Values ('Montero', 'Zambrano','Paola', 'Cdla. Parque Forestal', '052123456','F','1979-04-23','C');Insert Into personas(ApellidoPaterno,ApellidoMaterno,Nombres,Direccion,telefono,sexo,FechaNacimiento,EstadoCivil) Values ('Espinoza','Alcívar', 'Elsa Argentina','Mejia', '052636111','F','1978/10/20','C');

Existe otra posibilidad de ingresar datos un poco más cómoda:8 Ing. Gabriel Demera Ureta MgSc.


Insert Into personas(ApellidoPaterno,ApellidoMaterno,Nombres,Direccion,telefono,sexo,FechaNacimiento,EstadoCivil) Values ('Hidalgo', 'Villamar','Luis Ernesto','Limón', '052580113','M','1977/08/25','C'), ('Aveiga', 'Zambrano','Enma', 'Montecristi', '052636222','F','1974/06/18','C'), ('Zambrano','Franco', 'Luis Felipe','García Moreno y sucre','052632333','M','1976/04/10','C'), ('López', 'Intriago','Blanca', 'Primero de Mayo', '052636444','F','1972/02/16','C'), ('Zedeño', 'Franco', 'Nora Alice', 'Morales y Rocafuerte', '052630555','F','1970/05/18','C'), ('Panta', 'Chica', 'José Vicente','Pedro Gual y morales','052580666','M','1972/10/12','C'), ('Ronquillo','Delgado','Ramón ','Av.Manabí,los Mangos ','052636777','M','1974/12/30','C'), ('Vaca', 'Arteaga', 'Víctor Hugo','Av. Ajuela y morales', '052580888','M','1976/10/10','C'), ('Salazar', 'Montesinos','Claudia', 'Parroq. San Pablo', '052580999','F','1979/09/08','C'), ('Moncayo', 'Hidalgo', 'Flora', 'Cdla. Los tamarindos', '052636505','F','1979/07/28','C'), ('Garcés', 'Jorge', 'Humberto', 'Cdla. Los Bosques', '052580123','M','1980/02/20','C');

Los datos registrados pueden ser extraídos de la tabla mediante el uso de la instrucción SELECT , por ejemplo, si se necesita el listado se todas las personas con todos sus datos podríamos hacerlo de la siguiente forma:SELECT IdPersona, ApellidoPaterno, ApellidoMaterno, Nombres, Direccion, telefono, sexo, FechaNacimiento, EstadoCivil FROM personas;

Cuando se desea omitir el listado de todos los campos y obtener el mismo resultado usted puede hacer uso del asterisco (*), por ejemplo:SELECT * FROM personas;

Cuando se desea datos específicos usted puede utilizar de la lista de campos los que desee, por ejemplo, listar los apellidos, nombres, direcciones y fechas de nacimiento de todos los ingresados:SELECT ApellidoPaterno, ApellidoMaterno, Nombres, Direccion, FechaNacimiento FROM personas;

Sí lo solicitado incluye la condición de que solo sea para los de sexo femenino, se debe incluir la clausula WHERE que permite agregar condiciones de clasificación de datos, por ejemplo:SELECT ApellidoPaterno, ApellidoMaterno, Nombres, Direccion, FechaNacimiento FROM personas WHERE sexo=’f’;

Si necesitamos mostrar la lista con varones casados, quedaría así:SELECT ApellidoPaterno, ApellidoMaterno, Nombres, Direccion, FechaNacimiento FROM personas WHERE sexo=’m’ and EstadoCivil=’c’;

Si necesitamos la lista de personas mayores de 30 años de edad, se tendría que conocer la fecha de hoy asumamos que es 18 de agosto de 2013, si resto 30 años quedaría 1983/09/18 (este es el formato de MySQL), así que la instrucción quedaría:SELECT ApellidoPaterno, ApellidoMaterno, Nombres, telefono, sexo, FechaNacimiento FROM personas WHERE FechaNacimiento <’1983/09/18’;

Podemos cambiar los formatos de salida que se utilizan en MySQL, por ejemplo, mostraremos unidos los apellidos y el nombre, fecha de nacimiento con el formato día mes y año, y sexo mostrará masculino o femenino:SELECT concat(personas.ApellidoPaterno, ' ',personas.ApellidoMaterno ,' ',personas.Nombres) As Estudiante, if(sexo='F','Femenino','Masculino') As Sexo, date_format(FechaNacimiento,’%d/%m/%Y’) FROM personas;

Sí se necesita ver las edades de todas las personas utilizaremos la función TIMESTAMPDIFF que devuelve la diferencia en años entre dos fechas, la cláusula CURRENT_DATE devuelve la fecha actual que registra el computador:SELECT concat(personas.ApellidoPaterno, ' ',personas.ApellidoMaterno ,' ',personas.Nombres) As Nombre, TIMESTAMPDIFF(YEAR,fechanacimiento,CURRENT_DATE) as edad FROM personas;



Sí necesitamos la lista ordenada por apellido paterno de forma ascendente o descendente se utilizaría:SELECT ApellidoPaterno, ApellidoMaterno, Nombres, telefono, sexo, FechaNacimiento FROM personas ORDER BY ApellidoPaterno;

SELECT ApellidoPaterno, ApellidoMaterno, Nombres, telefono, sexo, FechaNacimiento FROM personas ORDER BY ApellidoPaterno DESC;

Por defecto ordena ascendentemente o si prefiere puede utilizar la instrucción ASC, también puede incluir sub órdenes agregando una lista separado por comas, por ejemplo:SELECT ApellidoPaterno, ApellidoMaterno, Nombres, telefono, sexo, FechaNacimiento FROM personas ORDER BY ApellidoPaterno ASC, ApellidoMaterno DESC;

Asumiendo que necesitamos las tres personas que tengan más edad podríamos utilizar:SELECT ApellidoPaterno, ApellidoMaterno, Nombres, sexo, FechaNacimiento FROM personas ORDER BY FechaNacimiento ASC LIMIT 3;

Para obtener un listado de años de nacimiento sin que se repitan tendríamos que realizar lo siguiente:SELECT DISTINCT YEAR(FechaNacimiento) AS anioFROM personas ORDER BY anio;

A menudo tenemos columnas que son cadenas de caracteres, y queremos buscar las cadenas que contienen cierta palabra. Esto se realiza a través de un nuevo tipo de condición: Nombre_de_columna LIKE cadena_de_caracteres.Con LIKE puede usar los siguientes dos caracteres comodines en el patrón: Carácter Descrición% Coincidencia de cualquier número de caracteres, incluso cero caracteres_ Coincide exactamente un carácter

Listar todas las personas cuyo nombre empieza con la letra ESELECT ApellidoPaterno, ApellidoMaterno, Nombres, sexo, FechaNacimiento FROM personas WHERE Nombres LIKE ‘E%’;

Listar todas las personas cuya segunda letra del apellido paterno es ESELECT ApellidoPaterno, ApellidoMaterno, Nombres, sexo, FechaNacimiento FROM personas WHERE ApellidoPaterno LIKE ‘_E%’;

Listar todas las personas cuyo apellido paterno o materno es ZambranoSELECT ApellidoPaterno, ApellidoMaterno, Nombres FROM personas WHERE concat(ApellidoPaterno, ' ', ApellidoMaterno ,' ', Nombres) LIKE ‘%Zambrano%’;

Otra posibilidad de obtener el mismo resultado es:(SELECT ApellidoPaterno, ApellidoMaterno, Nombres FROM personas WHERE ApellidoPaterno= ‘Zambrano’)UNION ALL(SELECT ApellidoPaterno, ApellidoMaterno, Nombres FROM personas WHERE ApellidoMaterno= ‘Zambrano’);

Si se necesita listar todos las personas cuyo apellido paterno no empiece con Z entonces quedaría así:SELECT ApellidoPaterno, ApellidoMaterno, Nombres, sexo, FechaNacimiento FROM personas WHERE ApellidoPaterno NOT LIKE ‘Z%’;



Para testear instancias literales de un carácter comodín, preceda el carácter con el carácter de escape. Si no especifica el carácter ESCAPE , se asume '\' .

Cadena Descrición\% Coincide un carácter '%'\_ Coincide un carácter '_'

Si desea filtrar registros utilizando una lista de valores o palabras puede hacer uso de la cláusula IN, por ejemplo asumamos que queremos ver la lista de personas cuyo apellido paterno es VACA, PANTA y SALAZAR tendríamos que hacer lo siguiente:

SELECT ApellidoPaterno, ApellidoMaterno, Nombres, sexo, direccion FROM personas WHERE ApellidoPaterno IN (‘Vaca’, ‘Panta’ , ‘Salazar’);

SELECT ApellidoPaterno, ApellidoMaterno, Nombres, sexo, direccion FROM personas WHERE ApellidoPaterno=‘Vaca’ OR ApellidoPaterno=‘Panta’ OR ApellidoPaterno=‘Salazar’;

También puede mostrar todos excepto los anterioresSELECT ApellidoPaterno, ApellidoMaterno, Nombres, sexo, direccion FROM personas WHERE ApellidoPaterno NOT IN (‘Vaca’, ‘Panta’ , ‘Salazar’);

En algún momento necesitaremos consultas que nos devuelva totales (1 solo registro), para ello se utilizará unas funciones estandarizadas de cálculo de registros COUNT(), SUM(), AVG(), MAX(), NIM(), entre otros.

Sí deseamos conocer cuántas personas tiene registrada la tabla persona:SELECT count(ApellidoPaterno) As Total_de_PersonasFROM personas;

También podemos ver el promedio de edades de todas las personas registradasSELECT AVG(TIMESTAMPDIFF(YEAR,fechanacimiento,CURRENT_DATE)) AS Promedio_EdadesFROM personas;

Existe la posibilidad de utilizar agrupamientos para que puedan existir varios totales dependiendo de las necesidades de agrupar, para esto se utiliza la cláusula GROUP BY

Para conocer cuántas personas nacieron por año de las que se encuentran registradas en nuestra tabla podríamos realizar lo siguiente:

SELECT YEAR(FechaNacimiento), COUNT(YEAR(FechaNacimiento)) AS N_Personas_Nacidas FROM Personas GROUP BY YEAR(FechaNacimiento)ORDER BY COUNT(YEAR(FechaNacimiento)) ;

Si se necesita conocer el número de persona clasificados por sexo:SELECT IF(Sexo='F','FEMENINO','MASCULINO') As Sexo, count(*) As Total_de_PersonasFROM personasGROUP BY Sexo;



LAS SUBCONSULTAS

Una subconsulta, es una sentencia SELECT que aparece dentro de otra sentencia SELECT que llamaremos consulta principal. Se puede encontrar en la lista de selección, en la cláusula WHERE o en la cláusula HAVING de la consulta principal.

Una subconsulta tiene la misma sintaxis que una sentencia SELECT normal exceptuando que aparece encerrada entre paréntesis, no puede contener la cláusula ORDER BY, ni puede ser la UNION de varias sentencias SELECT, además tiene algunas restricciones en cuanto a número de columnas según el lugar donde aparece en la consulta principal.

Cuando se ejecuta una consulta que contiene una subconsulta, la subconsulta se ejecuta por cada fila de la consulta principal.

Se aconseja no utilizar campos calculados en las subconsultas, ralentizan la consulta.

Las consultas que utilizan subconsultas suelen ser más fáciles de interpretar por el usuario.

Para realizar las siguientes prácticas agregaremos más registros a las tablas “Personas y Profesores”:INSERT INTO Personas (ApellidoPaterno,ApellidoMaterno,Nombres,Direccion,telefono,sexo,FechaNacimiento,EstadoCivil) VALUES ('Falcones','Canchingre','Ángela', 'Cdla. Los tamarindos', '052636456','F','1982/06/18','C'), ('Mora', 'Hidalgo', 'Octavio', 'Cdla. Ceibos del Norte','052360789','M','1970/05/08','C'),

('Delgado', 'Ramirez', 'Maricela', 'Cdla. Ceibos del Norte','054511133','F','1996/08/12','C'), ('Zambrano','Delgado', 'Javier', 'Cdla. Los Bosques', '052456123','M','1984-05-28','D'), ('Cardenas','Flores', 'Ana María', 'Eloy Alfaro y Plaza', '052100456','F','1991-03-18','D'),

('Basurto', 'Cedeño', 'Dolores', 'Cdla. Los Mangos', '052390987','F','1971/04/01','C'), ('Zambrano','López', 'José', 'parroq. San Placido', '052111654','M','1973/03/30','C'), ('Montesdeoca','Ureta','Elena ', 'Cdla. Forestal', '052222321','F','1974/01/25','C'), ('Farías', 'Salazar', 'Joel', 'Cdla. Terra Nostra', '052333254','M','1979/08/13','C'), ('Delgado', 'Manuel', 'Benedicto', 'Cdla. Los Bosques', '052444157','M','1979/09/23','C'), ('Navarrete','Ormaza', 'Yolanda', 'Cdla. Los tamarindos', '052534876','F','1981/01/10','C'), ('Giler', 'Mejía', 'Sócrates', 'Cdla. Ceibos del Norte','052778654','M','1985/03/18','C'), ('Mendieta','Vera', 'Juan Carlos','No Registrada', '052580505','M','1974/05/18','C'), ('Briónes', 'Zambrano','Gema', '25 de Diciembre', '052654987','F','1980/04/14','S'), ('Moya', 'Loor', 'Fernando', 'Morales', '052654987','M','1982/09/28','S'), ('Arias', 'De la Cruz','Ivan', 'Ramos y Duarte', '321654987','M','1990/08/25','V'), ('Andrade', 'Castro', 'Viviana ', 'San Placido', '052222233','F','2000/03/01','S'), ('Benitez', 'Sabando', 'Carmen', 'Calderon', '053333233','F','2001/06/30','S'), ('Burbano', 'Vera', 'José ', 'Rio Chico', '054444233','M','1995/02/17','S'),

('Zambrano','Cardenas', 'María José','Cdla. Bosques', '054561233','F','1999/06/10','S'), ('Zambrano','Cardenas','Eduardo', 'Rio Chico', '051111233','M','1995/03/01','S'),

('Zambrano','Falcones','Paola', 'Los tamarindos', '054871233','F','2000/03/18','S'), ('Demera', 'Montero', 'Alejandro', ' Cdla Parque Forestal', '055551233','M','2001/01/20','S'), ('Mora', 'Delgado', 'Miguel Ángel','18 de octubre', '056666233','M','2002/03/15','S'), ('Zambrano','Bazurto', 'Leonardo', 'Los Mangos', '057771233','M','2000/08/11','S'), ('Zambrano','Bazurto', 'Enrique ', 'Los Tamarindos', '058888233','M','2002/11/11','S'), ('Farías', 'Montesdeoca','Francisco','Los Bosques', '059999233','M','1996/03/19','S'), ('Delgado','Navarrete','Carlos Luis','San Placido', '050001233','M','2001/12/31','S'), ('Giler', 'Briones', 'LilY Anabel', 'Cdla Forestal', '051112222','F','1990/03/21','S'), ('Mendieta','Andrade', 'Marcos', 'Cdla Primero de Mayo', '051114444','M','1993/10/10','S'), ('Moya', 'Aveiga', 'Antonio', 'Parroquia Calderon', '051115555','M','2000/05/25','S'), ('Arias', 'Benitez', 'John Jairo', 'Vía a Rio Chico', '051116666','M','2002/07/30','S'), ('Burbano', 'Moncayo', 'Pedro Andrés','Floron 1', '051117777','M','1998/01/17','S'), ('Demera', 'Montero','María Gabriela',' Cdla Parque Forestal','051118888','F','1995/05/09','S'), ('Mora', 'Delgado', 'Virginia Yessenia','Parr. Rio Chico','051119999','F','2001/06/04','S'),



('Zambrano','Falcones','José Daniel','San Alejo', '051111000','M','1992/07/15','S'), ('Moya', 'Aveiga', 'David Fabian','18 de octubre', '051111545','M','2000/09/21','S'), ('Giler', 'Briones', 'Fabricio', 'San Placido', '051111129','M','1994/11/12','S'), ('Mendieta','Andrade','Lourdes', 'Km1 vía a rocafuerte', '051119876','F','2002/11/03','S'), ('Farías', 'Montesdeoca', 'Junior José','Los Angeles', '051115433','M','1998/12/18','S');

INSERT INTO Profesores (IdPersona, cargo, Titulacion, Fechaingreso) VALUES (1, 'Profesor', 'Ingeniero en Sistemas', '2005-10-23'),

(2, 'Inspector', 'Ldca en Educación básica', '2005-10-23'), (3, 'Profesor', 'Lcda. En Literatura', '2002/01/10'), (4, 'Profesor', 'Ingeniero Industrial', '2002/05/15'), (5, 'Profesor', 'Lcda. En Cultura Física', '2000/05/20'), (6, 'Profesor', 'Lcdo. En Historia y Geografía','2000/04/10'), (7, 'Profesor', 'Economista', '2001/09/08'), (8, 'Profesor', 'Lcda. Ciencias Matemáticas', '2001/04/18'), (9, 'Profesor', 'Lcdo. En Idiomas', '2003/08/29'), (10,'Profesor', 'Lcdo. En Psicología Clínica', '2000/05/29'), (11,'Profesor', 'Lcdo. En Laboratorio Químico', '2003/04/10'), (12,'Rector', 'Lcda. En Ciencias Matemáticas','1999/03/10'), (13,'Profesor', 'Lcda. En Educación Básica', '1999/10/15'), (14,'Vicerector','ingeniero Civil', '1999/07/25');

INSERT INTO padres(IdPersona,cargasfamiliares,TelefonoTrabajo,DireccionTrabajo,ocupacion) VALUES (1, 2,'052000111','Av. Urbina y Che Guevara','Profesor'),

(2, 1,'052345678','Alajuela y Quito', 'Profesora'),(16,2,'052176892','Manta', 'Abogado'),(17,3,'052987654','Av. Manabí y America', 'Comerciante'),(18,2,'052670158','Charapotó', 'Agricultor'),(19,4,'052182637','Km 1 Via a Manta', 'Secretaria'),(6, 2,'052123123','Alajuela y Quito', 'Profesor'),(15,2,'052123321','Morales y Rocafuerte', 'Diseñadora de carteles'),(20,3,'052987654','América y Urbina', 'Foto Copiador'),(21,3,'052987654','América y Urbina', 'Foto Copiador'),(22,4,'', 'Cdla. Parque Forestal', 'Ama de Casa'),(23,4,'', 'Los Almendros', 'Jardinero'),(24,2,'052111999','Km1 via a crucita', 'Maquinista'),(25,2,'052222333','Km1 via a crucita', 'Policia'),(26,3,'052098765','Coop. Portoviejo', 'Chofer'),(28,3,'052654876','Via a Crucita altura de gasolinera Univ.','Comerciante'),(27,2,'', 'Manta', 'Capitan de Barco'),(31,2,'052564876','San Placido', 'Ama de Casa'),(29,1,'', 'Ciudad de Portoviejo', 'Taxista'),(5, 1,'052345678','Alajuela y Quito', 'Profesora'),(30,3,'052630123','Av. Universitaria y Che Guevara','Ingeniero Civil'),(32,3,'052987396','18 de Octubre y Pedro Gual','Recepcionista'),(33,2,'052198823','América y Urbina N1432', 'Electricista'),(13,2,'052345678','Alajuela y Quito', 'Profesora');

INSERT INTO Estudiante(IdPersona,IdPersonaPapa,IdPersonaMama,Procedencia) VALUES (37,1,2,'Escuela 12 de Marzo'),

(50,6,15,'Unidad Educativa Arco Iris'),(36,6,15,'Unidad Educativa Arco Iris'),(49,16,17,'Escuela 12 de Marzo'),(38,16,17,'Escuela 12 de Marzo'),(35,18,19,'Unidad Edutativa Manta'),(34,18,19,'Unidad Edutativa Manta'),(39,21,20,'Escuela Naval'),(40,21,20,'Escuela Naval'),(41,23,22,'Unidad Educativa Arco Iris'),(54,23,22,'Unidad Educativa Arco Iris'),



(42,24,25,'Escuela Reales Tamarindos'),(43,26,28,'Escuela 12 de Marzo'),(52,26,28,'Escuela 12 de Marzo'),(53,27,31,'Escuela Naval'),(44,27,31,'Escuela Naval'),(45,29,5,'Unidad Edutativa Manta'),(51,29,5,'Unidad Edutativa Manta'),(46,30,32,'Escuela Reales Tamarindos'),(47,33,13,'Escuela Reales Tamarindos'),(48,1,2,'Escuela 12 de Marzo');

Para mostrar el listado de personas que son autoridades en la institución se lo realizaría así:SELECT ApellidoPaterno, ApellidoMaterno, Nombres FROM personas WHERE IdPersona IN (SELECT IdPersona FROM Profesores WHERE cargo <>’Profesor’);

Para mostrar el nombre y apellidos del Rector de la institución se lo realizaría así:SELECT ApellidoPaterno, ApellidoMaterno, Nombres FROM personas WHERE IdPersona = (SELECT IdPersona FROM Profesores WHERE cargo=’Rector’);

Para mostrar lo nombres y apellidos de los profesores que son padres de familia en la institución se lo realizaría así:SELECT ApellidoPaterno, ApellidoMaterno, Nombres FROM personas WHERE IdPersona IN (SELECT IdPersona FROM Padres WHERE ocupacion like ’Profe%’);

Si necesita mostrar la lista de estudiantes cuyas madres sean profesoras de la institución quedaría así:SELECT ApellidoPaterno, ApellidoMaterno, Nombres FROM personas WHERE IdPersona IN (SELECT IdPersona FROM Estudiante WHERE IdPersonaMama IN (SELECT idpersona FROM Profesores)) ORDER BY ApellidoPaterno;

Las subconsultas permiten comparar, desde la consulta principal con datos extraídos desde la misma u otras tablas, pero no se pueden mostrar los campos de las subconsulta con los campos de la consulta principal.

CONSULTAS MULTI-TABLAS

Hasta ahora todas las consultas que hemos usado se refieren a mostrar datos de sólo una tabla, pero también es posible hacer consultas usando varias tablas en la misma sentencia SELECT.

Este proceso permite realizar dos operaciones de álgebra relacional: el producto cartesiano y la composición.

Producto cartesiano.- Este es un operador binario, se aplica a dos relaciones y el resultado es otra relación. El resultado es una relación que contendrá todas las combinaciones de las tuplas de los dos operandos.

Esto es: si partimos de dos relaciones, R y S, cuyos grados son n y m, y cuyas cardinalidades a y b, la relación producto tendrá todos los atributos presentes en ambas relaciones, por lo tanto, el grado será n+m. Además la cardinalidad será el producto de a y b.Para ver un ejemplo usaremos dos tablas inventadas al efecto:


http://mysql.conclase.net/curso/?sqlsen=SELECT


tabla1(id, nombre, apellido)tabla2(id, número)tabla1id nombre apellido15 Fulginio Liepez26 Cascanio Suanchiez tabla2id número15 1234567826 2122211215 66525425

El resultado del producto cartesiano de tabla1 y tabla2: tabla1 x tabla2 es:tabla1 x tabla2id nombre apellido id número15 Fulginio Liepez 15 1234567826 Cascanio Suanchiez 15 1234567815 Fulginio Liepez 26 2122211226 Cascanio Suanchiez 26 2122211215 Fulginio Liepez 15 6652542526 Cascanio Suanchiez 15 66525425

Podemos ver que el grado resultante es 3+2=5, y la cardinalidad 2*3 = 6.

Composición (Join).- Una composición (Join en inglés) es una restricción del producto cartesiano, en la relación de salida sólo se incluyen las tuplas que cumplan una determinada condición.

La condición que se usa más frecuentemente es la igualdad entre dos atributos, uno de cada tabla.<relación1>[<condición>]<relación2>

Veamos un ejemplo. Partimos de dos relaciones:tabla1(id, nombre, apellido)tabla2(id, número)tabla1id nombre apellido15 Fulginio Liepez26 Cascanio Suanchiez tabla2id número15 1234567826 2122211215 66525425

La composición de estas dos tablas, para una condición en que 'id' sea igual en ambas sería:tabla1[tabla1.id = tabla2.id]tabla2id nombre apellido t2.id número15 Fulginio Liepez 15 1234567826 Cascanio Suanchiez 26 2122211215 Fulginio Liepez 15 66525425

Para aplicar el producto cartesiano en consultas multi-tablas crearemos otras entidades a nuestra base de datos UNIDAD:



CREATE TABLE if not exists PeriodosLectivos(IdPeriodoLectivo int primary key auto_increment,FechaInicio date,FechaFinal date,Nombre varchar(30)

);CREATE TABLE if not exists Especialidades(

IdEspecialidad int primary key auto_increment,NombreEspecialidad varchar(50)

);CREATE TABLE if not exists Cursos(

IdCurso int primary key auto_increment,Curso varchar(30)

);CREATE TABLE if not exists Paralelos(

IdParalelo int primary key auto_increment,Paralelo varchar(30)

);CREATE TABLE if not exists Matriculas(

IdMatricula int primary key auto_increment,IdPersonaEstudiante int not null,IdPeriodoLectivo int not null,IdEspecialidad int not null,IdCurso int not null,IdParalelo int not null,IdPersonaRepresentante int not null,Folder varchar(80),index(IdPersonaEstudiante),foreign key(IdPersonaEstudiante) references personas(Idpersona),foreign key(IdPeriodoLectivo) references periodosLectivos(IdperiodoLectivo),foreign key(IdEspecialidad) references Especialidades(IdEspecialidad),foreign key(IdCurso) references Cursos(IdCurso),foreign key(IdParalelo) references paralelos(Idparalelo),foreign key(IdPersonaRepresentante) references personas(IdPersona)

);INSERT INTO PeriodosLectivos(FechaInicio,FechaFinal,Nombre)

VALUES ('2012-03-15','2013-02-15','PERIODO 2012 - 2013'), ('2013-03-15','2014-02-15','PERIODO 2013 - 2014');INSERT INTO Especialidades(NombreEspecialidad)

VALUES ('Educacion Básica'), ('Bachillerato en Ciencias'), ('Bachillerato técnico');

INSERT INTO Cursos(Curso) VALUES ('Primero'), ('Segundo'), ('Tercero'), ('Cuarto'), ('Quinto'), ('Sexto'), ('Septimo');



INSERT INTO Paralelos(Paralelo) VALUES ('A'), ('B'), ('C'), ('D');

INSERT INTO Matriculas(IdPersonaEstudiante,IdPeriodoLectivo,IdEspecialidad,IdCurso,IdParalelo,IdPersonaRepresentante,Folder)

VALUES (46,2,1,7,1,30,'Archivador A 25'), (47,2,1,7,1,33,'Archivador A 26'), (42,2,1,7,1,24,'Archivador A 25'), (37,2,1,7,1,1, 'Archivador A 26'), (48,2,1,6,1,2 ,'Archivador A 27'), (41,2,1,7,1,22,'Archivador A 27'), (54,2,1,6,1,23,'Archivador A 28'), (43,2,1,7,1,26,'Archivador A 28'), (52,2,1,6,1,26,'Archivador A 29'), (44,2,1,7,1,27,'Archivador A 29'), (53,2,1,6,1,27,'Archivador A 30'), (38,2,1,7,1,16,'Archivador A 30'), (49,2,1,7,1,16,'Archivador A 31'), (45,2,1,6,1,29,'Archivador A 31'), (51,2,1,6,1,29,'Archivador A 32'), (35,2,1,6,1,19,'Archivador A 32'), (40,2,1,6,1,20,'Archivador A 33'), (39,2,1,7,1,20,'Archivador A 33'), (36,2,1,6,1,15,'Archivador A 34'), (34,2,1,7,1,19,'Archivador A 34');

EJEMPLOS CON PRODUCTO CARTESIANOSí Aplicamos producto cartesiano a las tablas cursos y paralelos de nuestra base de datos la instrucción sería:SELECT * FROM Cursos, paralelos;

El resultado sería, que por cada registro de la tabla cursos se combinan con todos los registros de la tabla paralelos, para el ejemplo anterior resultaría si necesitáramos todas las combinaciones posibles entre dos tablas pero es necesario utilizar condiciones para ciertas necesidades de filtración.

(Aplicando producto cartesiano con dos tablas) Si se necesita conocer la lista de profesores con sus datos personales tendríamos que hacer lo siguiente:SELECT concat(ApellidoPaterno,’ ‘, ApellidoMaterno,’ ‘,Nombres) AS Personas, cargo, titulacion FROM Personas, Profesores WHERE Personas.IdPersona=Profesores.IdPersona;

(Aplicando producto cartesiano con tres tablas) Sí necesitamos el listado de estudiantes matriculados con sus respectivos representantes, tendríamos:

SELECT concat(personas.ApellidoPaterno, ‘ ‘, personas.ApellidoMaterno, ‘ ‘, personas.Nombres) As Estudiantes, Matriculas.IdMatricula As Matricula, concat(personas_1.ApellidoPaterno, ‘ ‘, personas_1.ApellidoMaterno,’ ‘, personas_1.Nombres) As RepresentanteFROM Personas AS personas_1, Personas, Matriculas WHERE personas.IdPersona = matriculas.IdPersonaEstudiante AND personas_1.IdPersona = matriculas.IdPersonaRepresentante;

Cuando se utiliza dos veces la misma tabla, en este caso es obligatorio definir al menos un alias de tabla.



(Aplicando producto cartesiano con cuatro tablas) Sí necesitamos el listado de estudiantes matriculados de sexto año de básica con sus respectivos representantes, tendríamos:

El INNER JOIN es otro tipo de composición de tablas, permite emparejar filas de distintas tablas de forma más eficiente que con el producto cartesiano cuando una de las columnas de emparejamiento está indexada. Ya que en vez de hacer el producto cartesiano completo y luego seleccionar la filas que cumplen la condición de emparejamiento, para cada fila de una de las tablas busca directamente en la otra tabla las filas que cumplen la condición, con lo cual se emparejan sólo las filas que luego aparecen en el resultado.

La sintaxis es la siguiente:

tabla1 y tabla2 son especificaciones de tabla (nombre de tabla con alias o no, nombre de consulta guardada), de las tablas cuyos registros se van a combinar.

col1, col2 son las columnas de emparejamiento.

Observar que dentro de la cláusula ON los nombres de columna deben ser nombres cualificados (llevan delante el nombre de la tabla y un punto).

Las columnas de emparejamiento deben contener la misma clase de datos, las dos de tipo texto, de tipo fecha etc... los campos numéricos deben ser de tipos similares. Por ejemplo, se puede combinar campos AutoNumérico y Long puesto que son tipos similares, sin embargo, no se puede combinar campos de tipo Simple y Doble. Además las columnas no pueden ser de tipo Memo ni OLE.

comp representa cualquier operador de comparación ( =, <, >, <=, >=, o <> ) y se utiliza para establecer la condición de emparejamiento.

Se pueden definir varias condiciones de emparejamiento unidas por los operadores AND y OR poniendo cada condición entre paréntesis.


SELECT concat(personas.ApellidoPaterno, ‘ ‘, personas.ApellidoMaterno, ‘ ‘, personas.Nombres) As Estudiantes, Matriculas.IdMatricula As Matricula, concat(personas_1.ApellidoPaterno, ‘ ‘, personas_1.ApellidoMaterno,’ ‘, personas_1.Nombres) As RepresentanteFROM Personas AS personas_1, Personas, Matriculas, Cursos WHERE personas.IdPersona = matriculas.IdPersonaEstudiante AND personas_1.IdPersona = matriculas.IdPersonaRepresentante AND Cursos.IdCurso = matriculas.IdCurso AND Cursos.Curso='Sexto';


Ejemplo:Si necesitamos ver el listado de profesores, tendríamos:SELECT personas.ApellidoPaterno, personas.ApellidoMaterno, personas.Nombres, profesores.cargo, profesores.TitulacionFROM personas INNER JOIN profesores ON personas.IdPersona = profesores.IdPersona;

Se pueden combinar más de dos tablas, en este caso hay que sustituir en la sintaxis una tabla por un INNER JOIN completo.

Por ejemplo, sí necesitamos el listado de todos los estudiantes matriculados con sus respectivos representantes, tendríamos:

SELECT personas.ApellidoPaterno, personas.ApellidoMaterno, personas.Nombres, matriculas.IdMatricula, personas_1.ApellidoPaterno, personas_1.ApellidoMaterno, personas_1.Nombres, personas_1.NombresFROM personas AS personas_1 INNER JOIN (personas INNER JOIN matriculas ON personas.IdPersona = matriculas.IdPersonaEstudiante) ON personas_1.IdPersona = matriculas.IdPersonaRepresentante;

Considerando el ejemplo desarrollado con producto cartesiano, sí necesitamos el listado de estudiantes matriculados de sexto año de básica con sus respectivos representantes, tendríamos: SELECT personas.ApellidoPaterno, personas.ApellidoMaterno, personas.Nombres, matriculas.IdMatricula, personas_1.ApellidoPaterno, personas_1.ApellidoMaterno, personas_1.Nombres, personas_1.Nombres, cursos.CursoFROM cursos INNER JOIN (personas AS personas_1 INNER JOIN (personas INNER JOIN matriculas ON personas.IdPersona = matriculas.IdPersonaEstudiante) ON personas_1.IdPersona = matriculas.IdPersonaRepresentante) ON cursos.IdCurso = matriculas.IdCursoWHERE cursos.Curso='Sexto';

VISTAS EN MySQL

Las vistas tienen la misma estructura que una tabla: filas y columnas. La única diferencia es que sólo se almacena de ellas la definición, no los datos. Los datos que se recuperan mediante una consulta a una vista se presentarán igual que los de una tabla. De hecho, si no se sabe que se está trabajando con una vista, nada hace suponer que es así. Al igual que sucede con una tabla, se pueden insertar, actualizar, borrar y seleccionar datos en una vista. Aunque siempre es posible seleccionar datos de una vista, en algunas condiciones existen restricciones para realizar el resto de las operaciones sobre vistas.

Una vista se especifica a través de una expresión de consulta (una sentencia SELECT) que la calcula y que puede realizarse sobre una o más tablas. Sobre un conjunto de tablas relacionales se puede trabajar con un número cualquiera de vistas.

La mayoría de los SGBD soportan la creación y manipulación de vistas. Las vistas se crean cuando se necesitan hacer varias sentencias para devolver una tabla final.

CREANDO UNA VISTA: Se emplea la sentencia CREATE VIEW, que incluye una subconsulta (subquery) para determinar los datos a ser mostrados a través de la vista. Sintaxis:

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW <vista> [(<alias>[, <alias>] … )] AS <subconsulta> [WITH CHECK OPTION [CONSTRAINT <restricción>]] [WITH READ ONLY [CONSTRAINT <restricción>]];


http://es.wikipedia.org/wiki/DBMS


Dónde: OR REPLACE Se utiliza por si la vista ya estuviera creada anteriormente. En ese caso, la sustituye por la nueva definición. Por ejemplo si necesitamos crear una vista que muestra el listado de profesores:

CREATE VIEW V_Lista_Profesores ASSELECT ApellidoPaterno, ApellidoMaterno,Nombres, TitulacionFROM profesores INNER JOIN personas ON profesores.IdPersona = personas.IdPersonaWHERE profesores.cargo Like 'prof%'ORDER BY ApellidoPaterno;

Si necesita chequear la creación de la vista, utilice el comando (SHOW TABLES;), para verificar el contenido de la vista utilice (DESCRIBE V_lista_Profesores;), para observar la codificación de la vista se utiliza (SHOW CREATE VIEW V_lista_Profesores;), para ver la vista ejecutada utilice el comando (SELECT * FROM v_lista_profesores;), asumiendo que la vista tiene fallas, lo más recomendable es volver a ejecutar la vista utilizando la sintaxis OR REPLACE, por ejemplo:

CREATE OR REPLACE VIEW V_Lista_Profesores ASSELECT ApellidoPaterno, ApellidoMaterno,Nombres, TitulacionFROM profesores, personas WHERE profesores.IdPersona = personas.IdPersona AND profesores.cargo Like 'prof%'ORDER BY ApellidoPaterno;

Observe la codificación de la vista y verificará los cambios.

VISUALIZAR LA ESTRUCTURA DE UNA VISTA: DESCRIBE <vista>; Dónde: <vista> Es el nombre de la vista. Listar las vistas existentes: SELECT * FROM USER_VIEWS;

Indicaciones y restricciones de uso: - La subconsulta puede contener una sentencia SELECT de sintaxis compleja, incluyendo combinaciones (JOIN), agrupamientos (GROUP BY), y subconsultas internas.- Pero no puede incluir una cláusula ORDER BY. Si se desea ordenar, esto deberá hacerse mediante una consulta posterior que utilice la vista (ver ejemplo en apartado 4). ELIMINANDO UNA VISTA Cuando ya no se va a emplear más, una vista puede ser eliminada del esquema de la base de datos mediante la siguiente orden: DROP VIEW <vista>; Dónde: <vista> Es el nombre de la vista. Ejemplo: DROP VIEW EmpDepVentas;

Una vez desarrollada la vista, se la define como una tabla temporal, sí usted utiliza el comando SHOW TABLES; podrá observarla como si se tratara de una tabla adicional, de hecho se puede aplicar condiciones y relaciones sobre ellas, por ejemplo si necesitara clasificar la lista de estudiantes por cursos y paralelos, tendríamos:#Vista que muestra el listado de estudiantes

CREATE VIEW V_Lista_Estudiantes AS SELECT ApellidoPaterno, ApellidoMaterno, Nombres, Curso, ParaleloFROM personas INNER JOIN (paralelos INNER JOIN (cursos INNER JOIN matriculas ON cursos.IdCurso = matriculas.IdCurso) ON paralelos.IdParalelo = matriculas.IdParalelo) ON personas.IdPersona = matriculas.IdPersonaEstudianteORDER BY ApellidoPaterno, ApellidoMaterno;



Para aplicar una condición a la vista como si se tratara de una tabla se lo realiza de la siguiente manera:

SELECT * FROM V_lista_Estudiantes WHERE curso='septimo' AND paralelo=’A’;

Es importante entender que los campos a utilizar en la comparación deben estar en el listado de la vista. El siguiente ejemplo se creara 2 vistas para ser utilizadas en una relación, el resultado consiste en mostrar la lista de estudiantes cuyos padres sean profesores:

CREATE VIEW V_DatosEstudiantes ASSELECT idpersonapapa, concat(apellidopaterno,' ',apellidomaterno,' ',nombres) AS Estudiante FROM Estudiante, Personas WHERE Personas.idpersona=Estudiante.idpersona;

CREATE VIEW V_DatosProfesores ASSELECT personas.IdPersona, concat(ApellidoPaterno,’ ‘, ApellidoMaterno,’ ‘,Nombres) AS Profesor FROM profesores, personas WHERE profesores.IdPersona = personas.IdPersona AND profesores.cargo Like 'prof%';

CREATE VIEW V_ProfesoresHijos ASSELECT profesor, estudiante FROM V_DatosProfesores,V_DatosEstudiantes WHERE idpersona=idpersonapapa;

SELECT * FROM V_profesoreshijos;



REALIZAR EJERCICIO DE LA PRACTICA Nº 1

USUARIOS Y PRIVILEGIOS

Hasta ahora hemos usado sólo el usuario 'root', que es el administrador, y que dispone de todos los privilegios disponibles en MySQL.

Sin embargo, normalmente no será una buena práctica dejar que todos los usuario con acceso al servidor tengan todos los privilegios. Para conservar la integridad de los datos y de las estructuras será conveniente que sólo algunos usuarios puedan realizar determinadas tareas, y que otras, que requieren mayor conocimiento sobre las estructuras de bases de datos y tablas, sólo puedan realizarse por un número limitado y controlado de usuarios.

Los conceptos de usuarios y privilegios están íntimamente relacionados. No se pueden crear usuarios sin asignarle al mismo tiempo privilegios. De hecho, la necesidad de crear usuarios está ligada a la necesidad de limitar las acciones que tales usuarios pueden llevar a cabo.

MySQL permite definir diferentes usuarios, y además, asignar a cada uno determinados privilegios en distintos niveles o categorías de ellos.

NIVELES DE PRIVILEGIOS

En MySQL existen cinco niveles distintos de privilegios:

Globales: se aplican al conjunto de todas las bases de datos en un servidor. Es el nivel más alto de privilegio, en el sentido de que su ámbito es el más general.Estos permisos se almacenan en la tabla mysql.user. GRANT ALL ON *.* y REVOKE ALL ON *.* otorgan y quitan sólo permisos globales.De base de datos: se refieren a bases de datos individuales, y por extensión, a todos los objetos que contiene cada base de datos.Estos permisos se almacenan en las tablas mysql.db y mysql.host. GRANT ALL ON db_name.* y REVOKE ALL ON db_name.* otorgan y quitan sólo permisos de bases de datos.De tabla: se aplican a tablas individuales, y por lo tanto, a todas las columnas de esas tabla.Estos permisos se almacenan en la tabla mysql.tables_priv. GRANT ALL ON db_name.tbl_name y REVOKE ALL ON db_name.tbl_name otorgan y quian permisos sólo de tabla.De columna: se aplican a una columna en una tabla concreta.Estos permisos se almacenan en la tabla mysql.columns_priv . Usando REVOKE, debe especificar las mismas columnas que se otorgaron los permisos.De rutina: se aplican a los procedimientos almacenados. Aún no hemos visto nada sobre este tema, pero en MySQL se pueden almacenar procedimietos consistentes en varias consultas SQL.

CREAR USUARIOS

Aunque en la versión 5.0.2 de MySQL existe una sentencia para crear usuarios, CREATE USER, en versiones anteriores se usa exclusivamente la sentencia GRANT para crearlos.

En general es preferible usar GRANT, ya que si se crea un usuario mediante CREATE USER, posteriormente hay que usar una sentencia GRANT para concederle privilegios.


http://mysql.conclase.net/curso/?sqlsen=GRANT

http://mysql.conclase.net/curso/?sqlsen=CREATE_USER



http://mysql.conclase.net/curso/?sqlsen=CREATE_USER


Usando GRANT podemos crear un usuario y al mismo tiempo concederle también los privilegios que tendrá. La sintaxis simplificada que usaremos para GRANT, es:

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON TO user [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

La primera parte priv_type [(column_list)] permite definir el tipo de privilegio concedido para determinadas columnas. La segunda ON {tbl_name | * | *.* | db_name.*}, permite conceder privilegios en niveles globales, de base de datos o de tablas.

Para crear un usuario sin privilegios usaremos la sentencia:mysql> GRANT USAGE ON *.* TO anonimo IDENTIFIED BY 'clave';

Hay que tener en cuenta que la contraseña se debe introducir entre comillas de forma obligatoria.

Un usuario 'anonimo' podrá abrir una sesión MySQL mediante una orden:C:\mysql -h localhost -u anonimo -p

Pero no podrá hacer mucho más, ya que no tiene privilegios. No tendrá, por ejemplo, oportunidad de hacer selecciones de datos, de crear bases de datos o tablas, insertar datos, etc.

CONCEDER PRIVILEGIOS

Para que un usuario pueda hacer algo más que consultar algunas variables del sistema debe tener algún privilegio. Lo más simple es conceder el privilegio para seleccionar datos de una tabla concreta. Esto se haría así:

La misma sentencia GRANT se usa para añadir privilegios a un usuario existente.mysql> GRANT SELECT ON prueba.gente TO anonimo;

Esta sentencia concede al usuario 'anonimo' el privilegio de ejecutar sentencias SELECT sobre la tabla 'gente' de la base de datos 'prueba'.

Un usuario que abra una sesión y se identifique como 'anonimo' podrá ejecutar estas sentencias:mysql> SHOW DATABASES;+-----------+| database |+-----------+| prueba |+-----------+1 row in set (0.01 sec)

mysql> USE prueba;Database changed

mysql> SHOW TABLES;+----------------------+| Tables_in_prueba |+----------------------+| gente |





+----------------------+1 row in set (0.00 sec)

mysql> SELECT * FROM gente;+------------+---------------+| nombre | fecha |+------------+---------------+| Fulano | 1985-04-12 || Mengano | 1978-06-15 || Tulano | 2001-12-02 || Pegano | 1993-02-10 || Pimplano | 1978-06-15 || Frutano | 1985-04-12 |+------------+---------------+6 rows in set (0.05 sec)

Como se ve, para este usuario sólo existe la base de datos 'prueba' y dentro de esta, la tabla 'gente'. Además, podrá hacer consultas sobre esa tabla, pero no podrá añadir ni modificar datos, ni por supuesto, crear o destruir tablas ni bases de datos.

Para conceder privilegios globales se usa ON *.*, para indicar que los privilegios se conceden en todas las tablas de todas las bases de datos.

Para conceder privilegios en bases de datos se usa ON nombre_db.*, indicando que los privilegios se conceden sobre todas las tablas de la base de datos 'nombre_db'.

Usando ON nombre_db.nombre_tabla, concedemos privilegios de nivel de tabla para la tabla y base de datos especificada.

En cuanto a los privilegios de columna, para concederlos se usa la sintaxis tipo_privilegio (lista_de_columnas), [tipo_privilegio (lista_de_columnas)].

Otros privilegios que se pueden conceder son: ALL: para conceder todos los privilegios. CREATE: permite crear nuevas tablas. DELETE: permite usar la sentencia DELETE. DROP: permite borrar tablas. INSERT: permite insertar datos en tablas. UPDATE: permite usar la sentencia UPDATE.

Para ver una lista de todos los privilegios existentes consultar la sintaxis de la sentencia GRANT.

Se pueden conceder varios privilegios en una única sentencia. Por ejemplo:mysql> GRANT SELECT, UPDATE ON prueba.gente TO anonimo IDENTIFIED BY 'clave';

Un detalle importante es que para crear usuarios se debe tener el privilegio GRANT OPTION, y que sólo se pueden conceder privilegios que se posean.

REVOCAR PRIVILEGIOS

Para revocar privilegios se usa la sentencia REVOKE.REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...



ON FROM user [, user] ...

La sintaxis es similar a la de GRANT, por ejemplo, para revocar el privilegio SELECT de nuestro usuario 'anonimo', usaremos la sentencia:mysql> REVOKE SELECT ON prueba.gente FROM anonimo;

MOSTRAR LOS PRIVILEGIOS DE UN USUARIO

Podemos ver qué privilegios se han concedido a un usuario mediante la sentencia SHOW GRANTS. La salida de esta sentencia es una lista de sentencias GRANT que se deben ejecutar para conceder los privilegios que tiene el usuario. Por ejemplo:mysql> SHOW GRANTS FOR anonimo;+------------------------------------------------------------------------------------------------------+| Grants for anonimo@% |+------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'anonimo'@'%' IDENTIFIED BY PASSWORD '*5...' || GRANT SELECT ON `prueba`.`gente` TO 'anonimo'@'%' |+------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

NOMBRES DE USUARIOS Y CONTRASEÑAS

Como podemos ver por la salida de la sentencia SHOW GRANTS, el nombre de usuario no se limita a un nombre simple, sino que tiene dos partes. La primera consiste en un nombre de usuario, en nuestro ejemplo 'anonimo'. La segunda parte, que aparece separada de la primera por el carácter '@' es un nombre de máquina (host). Este nombre puede ser bien el de una máquina, por ejemplo, 'localhost' para referirse al ordenador local, o cualquier otro nombre, o bien una ip.

La parte de la máquina es opcional, y si como en nuestro caso, no se pone, el usuario podrá conectarse desde cualquier máquina. La salida de SHOW GRANTS lo indica usando el comodín '%' para el nombre de la máquina.

Si creamos un usuario para una máquina o conjunto de máquinas determinado, ese usuario no se podrá conectar desde otras máquinas. Por ejemplo:mysql> GRANT USAGE ON * TO anonimo@localhost IDENTIFIED BY 'clave';

Un usuario que se identifique como 'anonimo' sólo podrá entrar desde el mismo ordenador donde se está ejecutando el servidor.

En este otro ejemplo:mysql> GRANT USAGE ON * TO [email protected] IDENTIFIED BY 'clave';

El usuario 'anonimo' sólo puede conectarse desde un ordenador cuyo IP sea '10.28.56.15'.

Aunque asignar una contraseña es opcional, por motivos de seguridad es recomendable asignar siempre una.

La contraseña se puede escribir entre comillas simples cuando se crea un usuario, o se puede usar la salida de la función PASSWORD() de forma literal, para evitar enviar la clave en texto legible.



Si al añadir privilegios se usa una clave diferente en la cláusula IDENTIFIED BY, sencillamente se sustituye la contraseña por la nueva.

BORRAR USUARIOS

Para eliminar usuarios se usa la sentencia DROP USER.

No se puede eliminar un usuario que tenga privilegios, por ejemplo:mysql> DROP USER anonimo;ERROR 1268 (HY000): Can't drop one or more of the requested users

Para eliminar el usuario primero hay que revocar todos sus privilegios:mysql> SHOW GRANTS FOR anonimo;+------------------------------------------------------------------------------------------------------+| Grants for anonimo@% |+------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'anonimo'@'%' IDENTIFIED BY PASSWORD '*5....' || GRANT SELECT ON `prueba`.`gente` TO 'anonimo'@'%' |+------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

mysql> REVOKE SELECT ON prueba.gente FROM anonimo;Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER anonimo;Query OK, 0 rows affected (0.00 sec)

REALIZAR EJERCICIO DE LA PRACTICA Nº 2



PROCEDIMIENTOS ALMACENADOS

Los procedimientos almacenados (stored procedure en inglés) y funciones son nuevas funcionalidades de la versión de MySQL 5.0. Un procedimiento almacenado es un conjunto de comandos SQL que pueden almacenarse en el servidor. Una vez que se hace, los clientes no necesitan relanzar los comandos individuales pero pueden en su lugar referirse al procedimiento almacenado.

Algunas situaciones en que los procedimientos almacenados pueden ser particularmente útiles:

Cuando múltiples aplicaciones cliente se escriben en distintos lenguajes o funcionan en distintas plataformas, pero necesitan realizar la misma operación en la base de datos.

Cuando la seguridad es muy importante. Los bancos, por ejemplo, usan procedimientos almacenados para todas las operaciones comunes. Esto proporciona un entorno seguro y consistente, y los procedimientos pueden asegurar que cada operación se loguea apropiadamente. En tal entorno, las aplicaciones y los usuarios no obtendrían ningún acceso directo a las tablas de la base de datos, sólo pueden ejectuar algunos procedimientos almacenados.

Los procedimientos almacenados pueden mejorar el rendimiento ya que se necesita enviar menos información entre el servidor y el cliente. El intercambio que hay es que aumenta la carga del servidor de la base de datos ya que la mayoría del trabajo se realiza en la parte del servidor y no en el cliente.

Los procedimientos almacenados le permiten tener bibliotecas o funciones en el servidor de base de datos. Esta característica es compartida por los lenguajes de programación modernos que permiten este diseño interno, por ejemplo, usando clases. Usando estas características del lenguaje de programación cliente es beneficioso para el programador incluso fuera del entorno de la base de datos. MySQL sigue la sintaxis SQL:2003 para procedimientos almacenados, que también usa IBM DB2.

Los Procedimientos Almacenados se guardan en la propia Base de Datos aunque se pueden crear de forma interactiva desde la propia Base de Datos o desde un gestor de aplicaciones.

Por ejemplo, el siguiente código es un Procedimiento Almacenado que inserta un registro con un valor en función del parámetro recibido.

Delimiter //CREATE PROCEDURE procedure1 (IN parameter1 INTEGER)

BEGIN DECLARE variable1 CHAR(10);IF parameter1 = 17 THEN

SET variable1 = 'birds'; ELSE

SET variable1 = 'beasts'; END IF; INSERT INTO table1 VALUES (variable1);

END// Delimiter ;

Este Procedimiento Almacenado lo podrías crear interactivamente mediante la versión para MySQL 5.0. Si quisieras Procedimiento Almacenado tendrías que aplicar el siguiente formato:



CREATE PROCEDURE sp_name ([parameter[,...]])[characteristic ...] routine_body

CREATE FUNCTION sp_name ([parameter[,...]])RETURNS type[characteristic ...] routine_body parameter:[ IN | OUT | INOUT ] param_name type

La lista de parámetros entre paréntesis debe estar siempre presente. Si no hay parámetros, se debe usar una lista de parámetros vacía ( ) . Cada parámetro es un parámetro IN por defecto, que indica que el contenido de la variable solo es de entrada. Para especificar otro tipo de parámetro, use la palabra clave OUT que indica que el contenido de la variable es retornado desde el procedimiento al lugar donde fue llamado, o INOUT que indica que su contenido en de ingreso y salida al mismo tiempo, estos indicadores se describen antes del nombre del parámetro. Especificando IN, OUT, o INOUT sólo son validos para una PROCEDURE. El procedimiento almacenado debe de tener un cuerpo y un final para eso usamos las etiquetas BEGIN y END

Se tiene que cambiar el delimitador (;) en la consola, ya que los procedimientos almacenados lo utilizan para especificar el final de una instrucción, para ejemplo se utilizará //, esto se logra mediante el comando DELIMITER //, este proceso se debe repetir para dejarlo como estaba, para ello se utiliza el comando DELIMITER ;

DELIMITER // CREATE PROCEDURE mysp () BEGIN Proceso que desee realizarEND // DELIMITER ;

CALL mysp();



El nombre del store procedure (Procedimiento Almacenado) puede ser en mayúsculas o minúsculas pues no son case sensitive.

Ahora la forma de ejecutarlo es muy diferente a los manejadores como SQL Server o Sybase donde se hacen mediante la sentencia exec, aquí se usa la sentencia call y colocando al final los paréntesis.

Sobre las sentencias que no se pueden usar dentro de un procedimiento almacenado son: CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, DROP FUNCTION, CREATE TRIGGER, DROP TRIGGER. También el uso de “USE database” no es permitido, ya que MySQL asume que la base de datos por default es donde se encuentra creado el procedimiento.

Sentencia DECLARE

DECLARE var_name[,...] type [DEFAULT value]

Este comando se usa para declarar variables locales. Para proporcionar un valor por defecto para la variable, incluye una cláusula DEFAULT . El valor puede especificarse como expresión, no necesita ser una constante. Si la cláusula DEFAULT no está presente, el valor inicial es NULL. La visibilidad de una variable local es dentro del bloque BEGIN ... END donde está declarado.

DECLARE var1 INT DEFAULT 0; #variables enteras DECLARE var2 VARCHAR(15); #variables varchar también le podemos decir strings

Sentencia SET para variables

SET var_nombre = expr [, var_nombre = expr] ...

El comando SET en procedimientos almacenados es una versión extendida del comando general SET. El comando SET en procedimientos almacenados se implementa como parte de la sintaxis SET pre-existente. Esto permite una sintaxis extendida de SET a=x, b=y, ... donde distintos tipos de variables pueden mezclarse.

SET vat1 = "hola";

La sentencia CALLCALL sp_nombre([parameter[,...]])

El comando CALL invoca un procedimiento definido previamente con CREATE PROCEDURE.

CALL puede pasar valores al llamador usando parámetros declarados como OUT o INOUT . También “retorna” el número de registros afectados, que con un programa cliente puede obtenerse a nivel SQL llamando la función ROW_COUNT() y desde C llamando la función de la API C mysql_affected_rows() .

La sentencia SELECT ... INTOSELECT col_name[,...] INTO var_name[,...] table_expr

Esta sintaxis SELECT almacena columnas seleccionadas directamente en variables. Por lo tanto, sólo un registro puede retornarse.



SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

Sentencia IFIF expresión THEN -- que hacer en caso correctoELSE -- que hacer en caso contrarioEND IF; -- necesario para cerrar el bloque

IF en una sola línea:

SET valor = IF (5<5,1,0); SELECT valor;

IF implementa un constructor condicional básico. Si (condición de búsqueda) se evalúa a cierto, el comando SQL correspondiente listado se ejectua. Si no coincide ninguna (condición de búsqueda) se ejecuta el comando listado en la cláusula ELSE. lista_de_instrucciones puede consistir en varios comandos.

El siguiente ejemplo de procedimiento almacenado en MySQL, guarda datos de vehículos en la tabla familiares si su velocidad es menor que 120 Km/h o en la tabla deportivos en el caso contrario:

DELIMITER //CREATE PROCEDURE autos(IN velocidad INT, IN marca VARCHAR(50))BEGINIF velocidad < 120 THEN INSERT INTO familiares VALUES (velocidad,marca);ELSE INSERT INTO deportivos VALUES (velocidad,marca);END IF;END//DELIMITER;

Otro ejemplo de utilización de la instrucción de control:delimiter //CREATE procedure compara(IN cadena varchar(25), IN cadena2 varchar(25))beginIF strcmp(cadena, cadena2) = 0 then

SELECT "son iguales!";else

SELECT "son diferentes!!";end IF;end;//delimiter ;call compara("hola","Hola");

La sentencia CASECASE case_value WHEN Coincidencia_Valor THEN lista_de_instrucciones [WHEN Coincidencia_Valor THEN lista_de_instrucciones] ... [ELSE lista_de_instrucciones]END CASE

O: CASE WHEN condición_de_búsqueda THEN lista_de_instrucciones



[WHEN condición_de_búsqueda THEN lista_de_instrucciones] ... [ELSE lista_de_instrucciones]END CASE

El comando CASE para procedimientos almacenados implementa un constructor condicional complejo. Si una condición_de_búsqueda se evalúa a cierto, el comando SQL correspondiente se ejecuta. Si no coincide ninguna condición de búsqueda, el comando en la cláusula ELSE se ejecuta.

Nota: La sitaxis de un comando CASE mostrado aquí para uso dentro de procedimientos almacenados difiere ligeramente de la expresión CASE SQL. El comando CASE no puede tener una cláusula ELSE NULL y termina con END CASE en lugar de END.

Ejemplo:

mysql> SELECT CASE 1 WHEN 1 THEN 'one' -> WHEN 2 THEN 'two' ELSE 'more' END; -> 'one'

mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;

-> 'true'

mysql> SELECT CASE BINARY 'B' -> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END; -> NULL

Un ejemplo de procedimiento almacenado que aplique la instrucción es:delimiter //CREATE procedure miProc (IN p1 int)begin

declare var int ;SET var = p1 +2 ;

case varwhen 2 then INSERT INTO lista VALUES (66666);when 3 then INSERT INTO lista VALUES (4545665);else INSERT INTO lista VALUES (77777777);

end case;end;//

Sentencia LOOP[begin_label:] LOOP lista_de_instruccionesEND LOOP [end_label]

LOOP implementa un constructor de bucle simple que permite ejecución repetida de comandos particulares. El comando dentro del bucle se repite hasta que acaba el bucle, usualmente con un comando LEAVE .

Un comando LOOP puede etiquetarse. end_label no puede darse hasta que esté presente begin_label , y si ambos lo están, deben ser el mismo.

Sentencia LEAVELEAVE label



Este comando se usa para abandonar cualquier control de flujo etiquetado. Puede usarse con BEGIN ... END o bucles.

La setencia ITERATE

ITERATE label

ITERATE sólo puede aparecer en comandos LOOP, REPEAT, y WHILE . ITERATE significa “vuelve a hacer el bucle.”

Por ejemplo:

CREATE PROCEDURE doiterate(p1 INT)BEGIN label1: LOOP SET p1 = p1 + 1; IF p1 < 10 THEN ITERATE label1; END IF; LEAVE label1; END LOOP label1; SET x = p1;END

Sentencia REPEAT[begin_label:] REPEAT lista_de_instruccionesUNTIL condición_de_búsquedaEND REPEAT [end_label]

El comando/s dentro de un comando REPEAT se repite hasta que la condición search_condition es cierta.

Un comando REPEAT puede etiquetarse. end_label no puede darse a no ser que begin_label esté presente, y si lo están, deben ser el mismo. Por ejemplo:

delimiter //CREATE PROCEDURE dorepeat(p1 INT) BEGIN SET x = 0; REPEAT SET x = x + 1; UNTIL x > p1 END REPEAT; END //CALL dorepeat(1000)//SELECT x//+------+| x |+------+| 1001 |+------+1 row in set (0.00 sec)

Otro ejemplo de aplicación del REPEAT

delimiter //CREATE procedure p15()BEGINdeclare v int;SET v = 20;



REPEAT INSERT INTO lista VALUES(v); SET v = v + 1;UNTIL v >= 1END REPEAT;

END;//

Sentencia WHILE[begin_label:] WHILE condición_de_búsqueda DO lista_de_instruccionesEND WHILE [end_label]

El comado/s dentro de un comando WHILE se repite mientras la condición search_condition es cierta.

Un comando WHILE puede etiquetarse. end_label no puede darse a no ser que begin_label también esté presente, y si lo están, deben ser el mismo. Por ejemplo:

CREATE PROCEDURE dowhile()BEGIN DECLARE v1 INT DEFAULT 5; WHILE v1 > 0 DO ... SET v1 = v1 - 1; END WHILE;END

Ejemplo de aplicación:

delimiter //CREATE procedure p14()BEGIN

declare v int;SET v = 0;while v < 5 do INSERT INTO lista VALUES (v); SET v = v +1 ;end while;

END;//

Para eliminar procedimientos almacenados se utiliza la instrucción DROP PROCEDURE, se puede utilizar el complemento IF EXISTS para evitar errores en caso de no existir en la base de datos:Drop procedure if exists sp_name;

FUNCIONES EN MYSQL

El format de una function es:CREATE FUNCTION sp_name ([parameter[,...]])

RETURNS type[characteristic ...] routine_body

La cláusula RETURNS puede especificarse sólo con FUNCTION, donde es obligatorio. Se usa para indicar el tipo de retorno de la función, y el cuerpo de la función debe contener un comando RETURN value. La lista de parámetros entre paréntesis debe estar siempre presente. Si no hay parámetros, se debe usar una lista de parámetros vacía () . Cada parámetro es un parámetro IN por defecto.



Un procedimiento o función se considera “determinista” si siempre produce el mismo resultado para los mismos parámetros de entrada, y “no determinista” en cualquier otro caso. Si no se da ni DETERMINISTIC ni NOT DETERMINISTIC por defecto es NOT DETERMINISTIC.

Un ejemplo de uso de funciones es:

CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)

RETURN CONCAT('Hello, ',s,'!');

mysql> SELECT hello('mundo');

Si al crear la función se muestra el siguiente error:

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Para solucionar este problema, utilice la siguiente instrucción para su solución:

mysql> set Global log_bin_trust_function_creators=1;

Para mostrar la lista de funciones creadas se utiliza la instrucción:SHOW FUNCTION STATUSSi se desea hacer una función que devuelva la fecha con el formato ‘dia/mes/año’, se podría realizar lo siguiente:

DELIMITER //CREATE FUNCTION fecha_db(fech DATETIME) RETURNS char(14)BEGINRETURN date_format(fech,’%d/%m/%Y’);END//DELIMITER ;



CURSORES EN MYSQL (anexo vi)

En el lenguaje SQL existen unos tipos de controles llamados cursores que son útiles para recorrer los registros que devuelve una consulta. Este tipo de estructuras sólo deben utilizarse cuando no haya otra forma de manipular conjuntos de datos en una sola instrucción. Los cursores no son sensibles (No sensible significa que el servidor puede o no hacer una copia de su tabla de resultados), son de sólo lectura, y no permiten scrolling.

DELIMITER //DROP PROCEDURE IF EXISTS micursor//CREATE PROCEDURE micursor()BEGINDECLARE done BOOLEAN DEFAULT FALSE;DECLARE uid integer;DECLARE newdate integer;

DECLARE c1 cursor for SELECT id,timestamp from employers ORDER BY id ASC;DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;

open c1;c1_loop: LOOPfetch c1 into uid, newdate; IF done THEN LEAVE c1_loop; END IF; UPDATE calendar SET timestamp=newdate WHERE id=uid;END LOOP c1_loop;close c1;END //

Los cursores deben declararse antes de declarar los handlers, y las variables y condiciones deben declararse antes de declarar cursores o handlers.

Un handler (manejadores) en MySQL es invocado cuando se da un evento o condición definida con anterioridad. Este evento está siempre asociado con una condición de error, pero puedes tener tres formas de definir el error:

Como código de error de MySQL Como código SQLSTATE ANSI-standard Como nombre de condición, ejemplo: SQLEXCEPTION,SQLWARNING y NOT FOUND.

MySQL tiene sus propios códigos de error que son únicos para MySQL Server. Por ejemplo:

DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key=1;

Un código de error SQLSTATE es definido por ANSI standard y son independientes de la base de datos, lo que significa que deberías tener el mismo valor de error para cualquier base de datos ANSI compatible. Por ejemplo:

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET duplicate_key=1;

Así, Oracle, SQL Server, DB2, y MySQL reportarán el mismo SQLSTATEvalue (23000) cuando hay un error de primary key duplicada..

CREATE PROCEDURE ...BEGIN



-- Declaro el handler que al darse una condicion-- continue su ejecucion, pero registre un flagSET flag=0;DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET flag = 1;INSERT INTO TABLA........;-- Si flag=1 es porque se dio la condicion SQLSTATE '23000'-- (23000, en realidad, engloba muchos errores de violacion)-- Como no se ejecuto el INSERT entonces probamos con UPDATE.IF flag = 1 THENUPDATE TABLA...........;END IF;END

Sentencia CLOSE CLOSE nombre_de_cursor Cierra el cursor, el cual deberá estar abierto. Un cursor abierto es cerrado automáticamente cuando el bloque BEGIN dentro del cual está termina.

Sentencia FETCH FETCH [[NEXT] FROM] nombre_de_cursor INTO variable [, variable2,…] Obtiene el siguiente renglón para el cursor actual y almacena cada una de sus columnas en las variables mencionadas. El cursor debe estar abierto. Si no está disponible ningún renglón, un error con un valor de SQLSTATE igual a 02000 ocurre.

Sentencia OPEN OPEN nombre_de_cursor Abre el cursor para que pueda ser utilizado con la instrucción FETCH. Comentarios En las rutinas de MySQL, los comentarios pueden hacerse de tres maneras:

Empezando una línea con "#":# Este es un comentario

Encerrando un texto entre "/*" y "*/":/* Este es un comentario de una línea *//* Este es un comentario que abarca varias líneas */

Anteponiendo a una línea dos guiones y un espacio:-- -- Este es un comentario--



TRIGGERS (Disparadores)

El soporte para TRIGGERS en MySQL se realizó a partir de la versión 5.0.2. Un trigger puede ser definido para activarse en un INSERT, DELETE o UPDATE en una tabla y puede configurarse para activarse ya sea antes o después de que se haya procesado cada renglón por el query.

Los TRIGGERS en MySQL tienen la misma limitante que las funciones. No pueden referirse a una tabla en general. Pueden solamente referirse a un valor del renglón que está siendo modificado por el query que se está ejecutando. Las características más importantes de los TRIGGERS son:

Puede examinar el contenido actual de un renglón antes de que sea borrado o actualizado Puede examinar un nuevo valor para ser insertado o para actualizar un renglón de una tabla Un BEFORE TRIGGER, puede cambiar el nuevo valor antes de que sea almacenado en la base

de datos, lo que permite realizar un filtrado de la información.

Los triggers tienen dos palabras clave, OLD y NEW que se refieren a los valores que tienen las columnas antes y después de la modificación. Los INSERT permiten NEW, los DELETE sólo OLD y los UPDATE ambas.

El siguiente ejemplo muestra un BEFORE TRIGGER para un SELECT de una tabla:

CREATE TABLE tEjemplo (i INT, dt DATETIME);delimiter //CREATE TRIGGER t_ins BEFORE INSERT ON tEjemplo FOR EACH ROW BEGIN SET NEW.dt = CURRENT_TIMESTAMP; IF NEW.i < 0 THEN SET NEW.i = 0; END IF;END//delimiter ;



PRÁCTICA Nº 1

La Facultad de Ciencias Veterinarias necesita una base de datos que permita registrar los datos de los animales (canes) y las vacunas aplicadas a los mismos:

Asumiendo que el usuario root no tiene clave de accesomysql -h localhost -u root

Asumiendo que el usuario root tiene clave de accesomysql -h localhost -u root –pSuClave , si no desea escribir la clave por seguridad puede utilizar mysql -h localhost -u root –p

Si se desea ingresar y activar la base de datos directamente, puede utilizar la siguiente instrucción: mysql -h localhost -u root NombreBaseDatos -p

Crea la base de datos granjacreate database granja;

Comando para poder seleccionar y usar la base datos.use granja;

Muestra el nombre de las tablas que tengamos creadas en nuestra base de datos que estamos utilizando en este caso no muestra nada porque no hemos creado tablas.Show Tables; Forma de crear tablas y definiendo de una vez la llame primaria.CREATE TABLE animal(IdAnimal int(6) NOT NULL auto_increment,Nombre text(20) not null,Edad int(2), Raza varchar(20) , Precio int(7),primary key(IdAnimal)) TYPE=INNODB;

De ésta forma queda creada la tabla llamada animal con sus atributos y su llave primaria.describe animal;

El siguiente comando es para seleccionar la tabla de la base de datos “animal” y nos muestra los registros la tabla animal. (El * quiere decir todas las columnas).Select * From animal;

Sí se requiere especificar los atributos que queremos ver, como nombre y la edad de la tabla animal, este podría ser una de las formas: Select nombre, edad From animal;

Para insertar datos en la tabla animal, se lo realiza de la siguiente manera.Insert into animal(nombre, edad, raza, precio) values (‘pepe’,’2’,’pincher’,’2000’);Insert into animal(nombre, edad, raza, precio) values (‘Chiqui’,’3’,’Bulldoc’,’4000’);Insert into animal(nombre, edad, raza, precio) values (‘Mechón’,’5’,’Haspappi’,’2500’);Insert into animal(nombre, edad, raza, precio) values (‘Titón’,’1’,’Doverman’,’800’);



Este comando es para seleccionar solo los nombres de la tabla animal y nos muestra todos los nombres que tiene esa tabla.SELECT Nombre FROM animal;

Este comando nos muestra solo los animales donde la edad sea mayor a 2SELECT * FROM animal WHERE edad > 2;Este comando actualiza de la tabla animal el nombre animalito donde el id sea igual a 1UPDATE animal SET nombre = ‘animalito’ WHERE IdAnimal = ‘1’;

PARA PONER CLAVE AL USUARIO ROOTSe puede utilizar el UPDATE para cambiar la clave de acceso mediante el usuario root, para ello se debe activar la base de datos de MySQL y proceder a definir la clave:mysql>use mysql;mysql> UPDATE user SET Password = PASSWORD('contraseña') WHERE User = 'root';mysql> flush privileges;De la tabla animal borra la tupla que tenga el id numero 2DELETE FROM animal where IdAnimal = 2;

COMANDOS DE ELIMNACIÓN (No aplicarlos hasta finalizar la práctica)Comando para eliminar todos los registros de la tabla animal.DELETE FROM animal; o también TRUNCATE usuario;Para eliminar la tabla tanto los datos como su estructura.Drop table animal; o también Drop table IF EXISTS animal;El comando que permite borrar toda la base de datos que se llama granjaDrop database granja;

Como todos ya saben significa salir y se cierra la consola.Quit;

CREAR RELACIONES Y CLAVES FORANEASSe creará la tabla vacunas la cual se relacionará con la tabla animal, las tablas que se van a relacionar tienen que ser tipo InnoDb (InnoDB es el primer tipo de tabla que permite definir restricciones de claves foráneas para garantizar la integridad de los datos).

Se necesita usar la sintaxis: FOREIGN KEY (campo_fk) REFERENCES nombre_tabla(nombre_campo)Además se debe crear un índice en el campo que ha sido declarado como clave foránea

CREATE TABLE vacunas (IdVacuna int(9) not null auto_increment, Nombre text(20) not null, PRIMARY KEY (IdVacuna),Id_Animal int(6),index (Id_Animal), FOREIGN KEY (Id_Animal) REFERENCES animal(IdAnimal)) TYPE=INNODB;

En el ejemplo, se puede ver el diccionario de datos en cada uno de los atributos, de que tipo son, cuales permiten nulos, cuales no, la llave primaria, etc. ( el comando que muestra la estructura de la tabla) describe vacunas;



Para insertar datos en la tabla animal, se lo realiza de la siguiente manera.Insert into Vacunas(Nombre, Id_Animal) values (‘Antiparasitaria’,’1’);Insert into Vacunas(Nombre, Id_Animal) values (‘AntimoquilloA’,’3’);Insert into Vacunas(Nombre, Id_Animal) values (‘AntiMoquilloB’,’1’);Insert into Vacunas(Nombre, Id_Animal) values (‘Anti rabia’,’4’);

Este comando nos muestra los nombres de animales y sus vacunas aplicadasSELECT animal.Nombre, vacunas.Nombre FROM animal, vacunas WHERE animal.IdAnimal=vacunas.Id_Animal;

Este comando nos muestra lo mismo pero ordenado por el nombre de los animalesSELECT animal.Nombre, vacunas.Nombre FROM animal, vacunas WHERE animal.IdAnimal=vacunas.Id_Animal ORDER BY animal.Nombre;

Este comando nos muestra los nombres de animales en mayúscula y sus vacunas aplicadas en minúsculas:SELECT UPPER(animal.Nombre), LOWER(vacunas.Nombre) FROM animal, vacunas WHERE animal.IdAnimal=vacunas.Id_Animal;

Si se desea aplicar relación sin utilizar producto cartesiano:SELECT animal.nombre, vacunas.nombre FROM animal LEFT JOIN vacunas ON animal.idAnimal=vacunas.id_Animal;+---------------+-----------------------+| nombre | nombre |+---------------+-----------------------+| Yoyito | Antiparasitaria || Yoyito | AntiMoquilloB || Mechón | AntimoquilloA || Titón | Anti rabia || Sombra | NULL || Chiquita | NULL || Chilito | NULL || Chocolate | NULL |+---------------+-----------------------+Considere que los datos no relacionados entra la tabla animal y vacunas muestran NULL en la tabla vacunas.A diferencia del ejercicio anterior, la siguiente instrucción muestra solo los datos relacionados:SELECT animal.nombre, vacunas.nombre FROM animal JOIN vacunas ON animal.idAnimal=vacunas.id_Animal;+------------+---------------------+| nombre | nombre |+------------+---------------------+| Yoyito | Antiparasitaria || Mechón | AntimoquilloA || Yoyito | AntiMoquilloB || Titón | Anti rabia |+------------+--------------------+

Sí de desea utilizar condiciones para conseguir resultados personalizados usted podría aplicar la siguiente instrucción:SELECT Nombre, IF(edad <= 2, 'Joven', 'Adulto') as Tipo FROM animal ORDER BY edad;

Si se desea agregar un campo a la tabla “vacunas”, que registre la fecha cuando se aplicó la vacuna, sería del siguiente modo:



ALTER TABLE vacunas ADD fecha DATE; (anexo IV para ver más ejemplos)ALTER TABLE tabla ADD COLUMN valor INT;Para eliminar la columna agregada:ALTER TABLE tabla DROP COLUMN valor;Sí se desea cambiar el nombre de una tabla, podría aplicar:ALTER TABLE animal RENAME TO animales;mysql> show tables;+-------------------------+| Tables_in_granja |+-------------------------+| animales || vacunas |+-------------------------+


Dejemoslo como estaba:ALTER TABLE animales RENAME TO animal;

Select * From vacunas; o también aplicar describe vacunas;

Si se desea agregar fechas (Considere que el formato de las fechas en MYSQL de año/mes/día)se tendría que realizar lo siguiente:UPDATE vacunas SET fecha = ‘2012/01/01’ WHERE IdVacuna = ‘1’;UPDATE vacunas SET fecha = ‘2012/02/01’ WHERE IdVacuna = ‘2’;UPDATE vacunas SET fecha = ‘2012/03/25’ WHERE IdVacuna = ‘3’;UPDATE vacunas SET fecha = ‘2012/04/10’ WHERE IdVacuna = ‘4’;

Para ver el resultado de las actualizaciones select * from vacunas;

Crear un archivo con el listado de registros que contiene la tabla animal:SELECT * FROM animal INTO OUTFILE ‘RegAnimal.txt’;El archivo se crea en la carpeta de la base de datos, esta se encuentra en el directorio DATA

Para respaldar (exportar) la base de datos granja MYSQLDUMP --opt --user=root granja > backup.sql

PARA IMPORTAR O RESTAURAR UN RESPALDO

Usted debe pegar una copia del archivo de respaldo.sql a la carpeta

C:\wamp\bin\mysql\mysql5.1.36\bin>

Sí la base de datos no esta creada se la debe crear, para ello Ud. Debe ingresar a la consola y crearla:

C:\wamp\bin\mysql\mysql5.1.36\bin>mysql -u rootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.1.36-community-log MySQL Community Server (GPL)Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database granja;

Una vez creada la base de datos ud. debe salir de la consola mysql> quit;



y escribir la siguiente orden:C:\wamp\bin\mysql\mysql5.1.36\bin>mysql --user=root –p granja < backup.sql



PRÁCTICA Nº 2

La Facultad de Ciencias Veterinarias necesita una base de datos que permita registrar los datos de los animales (canes) y las vacunas aplicadas a los mismos, necesita generar usuarios con limitaciones para evitar problemas de seguridad:

mysql -h localhost -u root

mysql> SHOW GRANTS;

Para crear un usuario sin privilegios usaremos la sentencia:mysql> GRANT USAGE ON *.* TO octavo IDENTIFIED BY '123';

Para verificar los privilegios del usuario ‘octavo’

C:\wamp\bin\mysql\mysql5.1.36\bin>mysql -u octavo -p123Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.1.36-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;+-------------------------+| Database |+-------------------------+| information_schema |+-------------------------+1 row in set (0.00 sec)

Usted puede notar que no muestra el contenido real de las bases de datos, sí intenta trabajar con una base de datos, MySQL le mostrará:

mysql> use granja;ERROR 1044 (42000): Access denied for user 'octavo'@'%' to database 'granja'

MySQL le negará acceder a una base de datos, y si intenta cambiar los privilegios le mostrará el siguiente mensaje:

mysql> GRANT SELECT ON granja.animal TO octavo;ERROR 1142 (42000): SELECT,GRANT command denied to user 'octavo'@'localhost' for table 'animal'

Para hacer uso de la administración de usuarios es necesario entrar con el usuario que tiene todos los privilegios:mysql> quit;

C:\wamp\bin\mysql\mysql5.1.36\bin>mysql -u rootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.1.36-community-log MySQL Community Server (GPL)




mysql>_

Para crear privilegios de vistas sobre la base de datos ‘granja’ y la tabla ‘animal’ al usuario octavo se debe realizar lo siguiente:mysql> GRANT SELECT ON granja.animal TO octavo;

Volvemos a salir del usuario root, para entrar con el usuario octavomysql>\q;



mysql> show databases;+--------------------------+| Database |+--------------------------+| information_schema || granja |+-------------------------+2 rows in set (0.00 sec)

Le muestra solo la base de datos que puede observar

Comando para poder seleccionar y usar la base datos.mysql> use granja;

Muestra el nombre de la o las tablas que tengamos acceso.mysql> show tables;+---------------------+| Tables_in_granja |+---------------------+| animal |+---------------------+1 row in set (0.00 sec)

Solo le muestra las tablas a las cuales se le dio privilegios de vistas.

mysql> SELECT * FROM animal;+------------+-------------+-------+------------+--------+



| IdAnimal | Nombre | Edad | Raza | Precio |+------------+-------------+-------+------------+--------+| 1 | animalito | 2 | pincher | 2000 || 3 | Mechón | 5 | Haspappi | 2500 || 4 | Titón | 1 |Doverman | 800 |+------------+------------+--------+-------------+--------+3 rows in set (0.11 sec)

Si usted intenta ver el contenido de una tabla que no tiene permisos se mostrará el siguiente mensaje:mysql> SELECT * FROM vacunas;ERROR 1142 (42000): SELECT command denied to user 'octavo'@'localhost' for table 'vacunas'

Para que el usuario octavo pueda ver el contenido de la tabla vacunas debe ejecutar el siguiente comando:mysql> GRANT SELECT ON granja.* TO octavo;mysql>\q;

Al aplicar los comandos antes descritos como usuario ‘octavo’C:\wamp\bin\mysql\mysql5.1.36\bin>mysql -u octavo -p123Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.1.36-community-log MySQL Community Server (GPL)


mysql> use granja;Database changedmysql> show tables;+----------------------+| Tables_in_granja |+----------------------+| animal || vacunas |+----------------------+2 rows in set (0.03 sec)

mysql> select * from vacunas;+------------+-----------------+--------------+--------------+| IdVacuna | Nombre | Id_Animal | fecha |+------------+-----------------+--------------+--------------+| 1 | Antiparasitaria | 1 | 2012-01-01 || 2 | AntimoquilloA | 3 | 2012-02-01 || 3 | AntiMoquilloB | 1 | 2012-03-25 || 4 | Anti rabia | 4 | 2012-04-10 |+-----------+--------------------+------------+--------------+4 rows in set (0.02 sec)Si intentamos agregar un registro con el privilegio antes ejecutado, el mensaje es el siguiente:

mysql> Insert into animal(nombre, edad, raza, precio) values (‘Sombra’,’2’,’Criollo’,’70’);ERROR 1142 (42000): INSERT command denied to user 'octavo'@'localhost' for table 'animal'



Para permitir que el usuario ‘octavo’ pueda realizar procesos de actualización incluido el agregado de nuevos registros se puede realizar lo siguiente (Considere que el usuario debe ser ‘root’):

mysql> GRANT SELECT, UPDATE, INSERT ON granja.* TO octavo IDENTIFIED BY '123';Query OK, 0 rows affected (0.00 sec)

Una vez ingresado como usuario ‘octavo’ usted no tendrá impedimento para realizar lo siguiente:mysql> use granja;Database changed

mysql> Insert into animal(nombre, edad, raza, precio) values ('Sombra','2','Criollo','70');Query OK, 1 row affected (0.09 sec)

mysql> UPDATE animal SET nombre=’Yoyito’, edad=’1’ WHERE IdAnimal=’1’;

mysql> UPDATE animal SET Nombre='Yoyito', Edad='1' WHERE IdAnimal='1';Query OK, 1 row affected (0.06 sec)Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT IdAnimal, animal.Nombre, vacunas.Nombre, Edad FROM animal, vacunas WHERE animal.IdAnimal=vacunas.Id_Animal ORDER BY animal.Nombre;+-------------+----------+---------------------+-------+| IdAnimal | Nombre | Nombre | Edad |+------------+-----------+---------------------+-------+| 3 | Mechón | AntimoquilloA | 5 || 4 | Titón | Anti rabia | 1 || 1 | Yoyito | Antiparasitaria | 1 || 1 | Yoyito | AntiMoquilloB | 1 |+------------+-----------+---------------------+-------+4 rows in set (0.00 sec)

También se puede quitar los privilegios del usuario ‘octavo’, para controlar su administración (es necesario ingresar como usuario root para poder modificar los permisos del usuario):mysql>\q;



mysql> REVOKE SELECT ON granja.* FROM octavo;Query OK, 0 rows affected (0.00 sec)

Para mostrar los privilegios o permisos se ejecuta la siguiente orden:mysql> SHOW GRANTS FOR octavo;+-------------------------------------------------------------------------------------------------------+| Grants for octavo@% |+-------------------------------------------------------------------------------------------------------+



| GRANT USAGE ON *.* TO 'octavo'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' || GRANT INSERT, UPDATE ON ̀ granja .̀* TO 'octavo'@'%' || GRANT SELECT ON ̀ granja .̀̀ animal̀ TO 'octavo'@'%' |+-------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec)

mysql> REVOKE SELECT ON granja.animal FROM octavo;Query OK, 0 rows affected (0.03 sec)

Para mostrar los privilegios o permisos se ejecuta la siguiente orden:mysql> SHOW GRANTS FOR octavo;+-------------------------------------------------------------------------------------------------------+| Grants for octavo@% |+-------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'octavo'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' || GRANT INSERT, UPDATE ON ̀ granja .̀* TO 'octavo'@'%' |+-------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

mysql>\q;



mysql> use granja;Database changed

mysql> SELECT IdAnimal, animal.Nombre, vacunas.Nombre, Edad FROM animal, vacunas WHERE animal.IdAnimal=vacunas.Id_Animal ORDER BY animal.Nombre;ERROR 1142 (42000): SELECT command denied to user 'octavo'@'localhost' for table 'vacunas'

Para finalizar la práctica se eliminará al usuario ‘octavo’, es importante que antes de eliminar a un usuario, se debe eliminar primero sus privilegios, para realizar este proceso se debe ingresar a la consola como root:mysql>\q;



mysql> REVOKE UPDATE, INSERT ON granja.* FROM octavo;Query OK, 0 rows affected (0.02 sec)



mysql> DROP USER anonimo;Query OK, 0 rows affected (0.00 sec)

Ha….! Si desea que el usuario ‘octavo’ tenga todos los privilegios se tendría que realizar lo siguiente:

mysql> GRANT all ON granja.* to octavo identified by '123' with GRANT OPTION;Query OK, 0 rows affected (0.00 sec)



PRÁCTICA Nº 3

Una store procedure (Procedimiento Almacenado) es un pequeño programa que se encuentra almacenado en la base de datos, tiene muchas ventajas su uso ya que pueden realizarse cambios de código sin necesidad de afectar a la aplicación, también nos ayuda a minimizar el tráfico en la red ya que en lugar de mandar una sentencia larga, solo se manda a ejecutar el nombre corto del store procedure, por lo que su ejecución se vuelve mucho más rápida.

Para poder hacer uso de los store procedures debemos contar con la versión 5.0 de MySQL. Hagamos una pequeña demostración de ellos a continuación:

Antes que nada debemos revisar que contamos con la versión 5.0 de MySQL como mínimo, podemos usar cualquiera de las siguientes dos formas.

mysql> show variables like '%version%'; +-------------------------+------------------------------+| Variable_name | Value |+-------------------------+------------------------------+| protocol_version | 10 || version | 5.1.36-community-log || version_comment | MySQL Community Server (GPL) || version_compile_machine | ia32 || version_compile_os | Win32 |+-------------------------+------------------------------+5 rows in set (0.05 sec)

mysql> select version(); +----------------------+| version() |+----------------------+| 5.1.36-community-log |+----------------------+1 row in set (0.02 sec)

Ahora creemos nuestro primer store procedure.

Activamos la base de datos con la cual hemos estado trabajando: mysql> use granja;Database changed

Si se desea crear un procedimiento almacenado que muestre la lista de animales registrados se lo haría de la siguiente forma:mysql> CREATE PROCEDURE Lista_Animales() SELECT * FROM animal;Query OK, 0 rows affected (0.00 sec)

Para ejecutar el procedimiento almacenado se lo realizaría de la siguiente forma:mysql> call Lista_Animales();+----------+----------+------+----------------+--------+| IdAnimal | Nombre | Edad | Raza | Precio |+----------+----------+------+----------------+--------+| 1 | Yoyito | 1 | pincher | 2000 || 3 | Mechón | 5 | Haspappi | 2500 || 4 | Titón | 1 | Doverman | 800 || 5 | Sombra | 2 | Criollo | 70 |



| 6 | Chiquita | 2 | Lova Siveriana | 800 |+----------+----------+------+----------------+--------+5 rows in set (0.00 sec)Query OK, 0 rows affected (0.03 sec)

Sí deseamos crear un procedimiento almacenado que devuelva el total de registros almacenados en la tabla animal, se lo realizaría de la siguiente forma:

mysql> CREATE PROCEDURE Total_Animales() SELECT COUNT(*) as Total_Animales FROM animal;Query OK, 0 rows affected (0.00 sec)

Observe que el ejemplo aplica alias (AS) para especificar un nombre al resultado. Al ejecutar el procedimiento almacenado se obtiene: mysql> call Total_Animales();+-------+| Total |+-------+| 5 |+-------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

El siguiente ejemplo, crea un procedimiento almacenado que guarda un registro en la tabla animal, para ello se pasan 4 parámetros p_nombre, p_edad, p_raza, p_precio, no se antepone la referencia IN ya que su aplicación es por defecto. Delimiter es utilizado para evitar problemas por el uso del punto y coma (;) en el procedimiento almacenado. mysql> delimiter //mysql> CREATE PROCEDURE Agrega_Animal (p_nombre tinytext, p_edad int(2), p_raza varchar(20), p_precio int(7))BEGIN Insert into animal(Nombre,Edad,Raza,Precio)values(p_nombre, p_edad, p_raza, p_precio);END//Query OK, 0 rows affected (0.08 sec)

mysql> delimiter ;

Al llamar al procedimiento se lo aplicaría de la siguiente forma:mysql> call Agrega_Animal('Chiquita','2','Lova Siveriana','800');Query OK, 1 row affected (0.13 sec)

Para comprobar el resultado de aplicar el procedimiento almacenado se lo haría así:mysql> select * from animal;+----------+----------+------+----------------+--------+| IdAnimal | Nombre | Edad | Raza | Precio |+----------+----------+------+----------------+--------+| 1 | Yoyito | 1 | pincher | 2000 || 3 | Mechón | 5 | Haspappi | 2500 || 4 | Titón | 1 | Doverman | 800 || 5 | Sombra | 2 | Criollo | 70 || 6 | Chiquita | 2 | Lova Siveriana | 800 |+----------+----------+------+----------------+--------+5 rows in set (0.01 sec)

Sí se desea agregar otro registro tendríamos:



mysql> call Agrega_Animal('Chilito','3','Chiguagua','500');Query OK, 1 row affected (0.13 sec)

También se puede aplicar un procedimiento almacenado que mezcle varias instrucciones sql y obtener varios resultados, como por ejemplo agregar un registro y mostrar un resultado:mysql> delimiter //mysql> CREATE PROCEDURE Agrega_Animal_contador (p_nombre tinytext, p_edad int(2), p_raza varchar(20), p_precio int(7))BEGINDECLARE Total INT;Insert into animal(Nombre,Edad,Raza,Precio)values(p_nombre, p_edad, p_raza, p_precio);SELECT COUNT(*) as Total_Ingresados FROM Animal INTO Total;SELECT Total;END//Query OK, 0 rows affected (0.00 sec)mysql> delimiter;

Sí observa la primera instrucción SELECT, notará que al final, incluye el resultado a la variable Total, el resultado es mostrado por una segunda instrucción Select.

Para observar el resultado de aplicar el procedimiento almacenado se debe escribir: mysql> call Agrega_Animal_Contador('Chocolate','3','Chiguagua','500');+-------+| Total |+-------+| 7 |+-------+1 row in set (0.03 sec)Query OK, 0 rows affected (0.05 sec)

Para observar el resultado en la tabla aplicaríamos:mysql> select * from animal;+----------+-----------+------+----------------+--------+| IdAnimal | Nombre | Edad | Raza | Precio |+----------+-----------+------+----------------+--------+| 1 | Yoyito | 1 | pincher | 2000 || 3 | Mechón | 5 | Haspappi | 2500 || 4 | Titón | 1 | Doverman | 800 || 5 | Sombra | 2 | Criollo | 70 || 6 | Chiquita | 2 | Lova Siveriana | 800 || 7 | Chilito | 3 | Chiguagua | 500 || 8 | Chocolate | 3 | Chiguagua | 500 |+----------+-----------+------+----------------+--------+7 rows in set (0.00 sec)

Si se desea eliminar un procedimiento almacenado se puede utilizar:mysql> DROP PROCEDURE IF EXISTS Total_Animales;

En el siguiente ejemplo se utilice un procedimiento almacenado que devuelve un resultado mediante una variable:DELIMITER //CREATE PROCEDURE Busca_Amimal(IN letra CHAR(2), OUT Total INT)BEGIN SELECT * FROM animal WHERE nombre LIKE letra;



SELECT COUNT(*) INTO Total FROM animal WHERE nombre LIKE letra;END//DELIMITER ;En la aplicación de este procedimiento almacenado, busca todos los animales cuyo nombre empieza con la letra “c”, además se utiliza la variable @candidad para extraer el total de nombres que empiezan con “C”CALL Busca_Amimal('c%', @cantidad);+----------+-----------+------+----------------+--------+| IdAnimal | Nombre | Edad | Raza | Precio |+----------+-----------+------+----------------+--------+| 6 | Chiquita | 2 | Lova Siveriana | 800 || 7 | Chilito | 3 | Chiguagua | 500 || 8 | Chocolate | 3 | Chiguagua | 500 |+----------+-----------+------+----------------+--------+3 rows in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

Para evitar escribir el character % al momento de llamar al procedimiento almacenado, se podría utilizar la concatenación:

SELECT * FROM animal WHERE nombre LIKE concat(letra,’%’);

Para mostrar el contenido de la variable @cantidad se lo realiza de la siguiente forma:

mysql> select @cantidad;+-----------+| @cantidad |+-----------+| 3 |+-----------+1 row in set (0.00 sec)

Para mostrar la lista de procedimientos almacenados se podría aplicar lo siguiente:Desde la base de datos principal de MySQL:mysql>use mysql;mysql>SELECT specific_name from proc; o también SELECT specific_name from proc where type=’procedure’;

Desde la base de datos en la que se está trabajando:mysql>SHOW PROCEDURE STATUS;+------+----------------------+---------+--------------+-----------+-----------+-------------+----------------------------+--------------------+------------------+|Db | Name |Type |Definer |Modified |Created |Security_type|Comment|character_set_client|collation_connection|Database Collation|+------+----------------------+---------+--------------+-----------+-----------+-------------+----------------------------+--------------------+------------------+|granja|Agrega_Animal |PROCEDURE|root@localhost|2006-12-21 |2006-12-21 |DEFINER | | latin1 |latin1_swedish_ci |latin1_swedish_ci ||granja|Agrega_Animal_contador|PROCEDURE|root@localhost|2006-12-21 |2006-12-21 |DEFINER | | latin1 |latin1_swedish_ci |latin1_swedish_ci ||granja|Busca_Amimal |PROCEDURE|root@localhost|2006-12-21 |2006-12-21 |DEFINER | | latin1 |latin1_swedish_ci |latin1_swedish_ci ||granja|Lista_Animales |PROCEDURE|root@localhost|2006-12-21 |2006-12-21 |DEFINER | | latin1 |latin1_swedish_ci |latin1_swedish_ci ||granja|Total_Animales |PROCEDURE|root@localhost|2006-12-21 |2006-12-21 |DEFINER | | latin1 |latin1_swedish_ci |latin1_swedish_ci |+------+----------------------+---------+--------------+-----------+-----------+-------------+-------+--------------------+--------------------+------------------+5 rows in set (0.16 sec)

Para mostrar el contenido de un procedimiento almacenado se aplica:Desde la base de datos principal de MySQL:



mysql> use mysql;mysql> Select specific_name, body from proc where specific_name='agrega_animal';

Desde la base de datos en la que se está trabajando:mysql> SHOW CREATE PROCEDURE Agrega_Animal;+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| Agrega_Animal | | CREATE DEFINER=`root`@`localhost` PROCEDURE `Agrega_Animal`(p_nombre tinytext, p_edad int(2),p_raza varchar(20), p_precio int(7)) BEGIN Insert into animal(Nombre, Edad, Raza, Precio) values (p_nombre,p_edad,p_raza,p_precio); END | latin1 | latin1_swedish_ci | latin1_swedish_ci |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+1 row in set (0.05 sec)

APLICACIÓN DE FUNCIONES:

Suponiendo que usted desea mostrar la lista de animales y las fechas de las vacunas aplicadas con el formato “dia/mes/año”:Primero se creará la función:

delimiter //CREATE FUNCTION fecha_db(fech DATETIME) RETURNS char(14)BEGINRETURN date_format(fech,’%d/%m/%Y’);END//delimiter ;

Después aplica la instrucción SQL utilizando la función:SELECT IdAnimal, animal.Nombre, vacunas.Nombre As Vacuna, fecha_db(vacunas.fecha) FROM animal, vacunas WHERE animal.IdAnimal=vacunas.Id_Animal ORDER BY animal.Nombre;+------------+----------+-------------------+-------------------------------+| IdAnimal | Nombre | Vacuna | fecha_db(vacunas.fecha) |+------------+----------+-------------------+------------------------------+| 9 | KuKy | retroviral | 12/05/2012 || 3 | Mechón | AntimoquilloA | 01/02/2012 || 5 | Sombra | retroviral | 12/05/2012 || 5 | Sombra | Antirabica | 12/05/2012 || 4 | Titón | Anti rabia | 10/04/2012 || 4 | Titón | Antirabica | 12/05/2012 || 1 | Yoyito | Antiparasitaria | 01/01/2012 || 1 | Yoyito | AntiMoquilloB | 25/03/2012 |+------------+--------+-----------------------+----------------------------+

Sí se desea realizar algo más especializado, con detalles como nombre del mes y nombres de días de semana, entonces podríamos realizar algo así:

DELIMITER //CREATE FUNCTION Fechadetalle(fecha DATETIME) RETURNS varchar(150)BEGINDECLARE dia INT;DECLARE mes INT;DECLARE dia_s VARCHAR(20);DECLARE mes_s VARCHAR(20);DECLARE fecha_f VARCHAR(150);



SET dia = DAYOFWEEK(fecha);SET mes = MONTH(fecha);CASE diaWHEN 1 THEN SET dia_s = ‘Domingo’;WHEN 2 THEN SET dia_s = ‘Lunes’;WHEN 3 THEN SET dia_s = ‘Martes’;WHEN 4 THEN SET dia_s = ‘Miercoles’;WHEN 5 THEN SET dia_s = ‘Jueves’;WHEN 6 THEN SET dia_s = ‘Viernes’;WHEN 7 THEN SET dia_s = ‘Sabado’;END CASE;CASE mesWHEN 1 THEN SET mes_s = ‘Enero’;WHEN 2 THEN SET mes_s = ‘Febrero’;WHEN 3 THEN SET mes_s = ‘Marzo’;WHEN 4 THEN SET mes_s = ‘Abril’;WHEN 5 THEN SET mes_s = ‘Mayo’;WHEN 6 THEN SET mes_s = ‘Junio’;WHEN 7 THEN SET mes_s = ‘Julio’;WHEN 8 THEN SET mes_s = ‘Agosto’;WHEN 9 THEN SET mes_s = ‘Septiembre’;WHEN 10 THEN SET mes_s = ‘Octubre’;WHEN 11 THEN SET mes_s = ‘Noviembre’;WHEN 12 THEN SET mes_s = ‘Diciembre’;END CASE;SET fecha_f = CONCAT(dia_s,’ ‘,DAY(fecha),’ ‘,mes_s,’ ‘,YEAR(fecha));RETURN fecha_f;END//DELIMITER ;

Al realizar la misma consulta pero utilizando la nueva función tendríamos:SELECT IdAnimal, animal.Nombre, vacunas.Nombre As Vacuna, fechadetalle(fecha) FROM animal, vacunas WHERE animal.IdAnimal=vacunas.Id_Animal ORDER BY animal.Nombre;+-------------+------------+--------------------+------------------------------------+| IdAnimal | Nombre | Vacuna | fechadetalle(vacunas.fecha) |+-------------+------------+--------------------+------------------------------------+| 9 | KuKy | retroviral | Sabado 12 Mayo 2012 || 3 | Mechón | AntimoquilloA | Miercoles 1 Febrero 2012 || 5 | Sombra | retroviral | Sabado 12 Mayo 2012 || 5 | Sombra | Antirabica | Sabado 12 Mayo 2012 || 4 | Titón | Anti rabia | Martes 10 Abril 2012 || 4 | Titón | Antirabica | Sabado 12 Mayo 2012 || 1 | Yoyito | Antiparasitaria | Domingo 1 Enero 2012 || 1 | Yoyito | AntiMoquilloB | Domingo 25 Marzo 2012 |+-------------+-----------+---------------------+-----------------------------------+8 rows in set (0.02 sec)

También se puede realizar funciones de carácter matemático:delimiter//create function Factorial(n int) returns intbeginDeclare fa int default 1;Declare num int default 1;while num<=n doSet fa=fa*num;Set num=num+1;end while;return fa;



end //delimiter;

Para respaldar la base de datos granja incluido los procedimientos almacenadosMYSQLDUMP -u root -p granja --routines > backup_con_procedures.sql

Ejemplo Nº1 de uso de cursores en mysql, se trata de un procedimiento almacenado que almacena los datos de la tabla VACUNAS aplicados a los canes registrados en la tabla ANIMAL, para eso se pasa el nombre del animal y se busca su “IdAnimal” en la tabla ANIMAL para almacenarlo en la tabla VACUNAS, este cursor utiliza SQLSTATE para controlar el recorrido del cursor: delimiter //CREATE PROCEDURE RegistraVacuna(pNombreAnimal tinytext, pNombreVacuna tinytext, pfecha date)BEGIN

DECLARE done BOOLEAN DEFAULT FALSE;DECLARE id integer;DECLARE nomb tinytext;DECLARE c1 cursor for SELECT idanimal, nombre from animal ORDER BY idanimal ASC;DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;open c1;

c1_loop: LOOPFETCH c1 INTO id, nomb;IF done THEN LEAVE c1_loop; END IF; IF strcmp(nomb,pNombreAnimal) = 0 THEN INSERT INTO vacunas (Nombre,Id_Animal,fecha) VALUES (pNombreVacuna, id , pfecha);

END IF; END LOOP c1_loop;close c1;END //delimiter ;

Ejemplo Nº 2 de uso de cursores en mysql, se trata de un procedimiento almacenado que almacena los datos de la tabla VACUNAS aplicados a los canes registrados en la tabla ANIMAL, para eso se pasa el nombre del animal y se busca su “IdAnimal” en la tabla ANIMAL para almacenarlo en la tabla VACUNAS, este cursor utiliza FOR NOT FOUND para controlar el recorrido del cursor:

delimiter //CREATE PROCEDURE RegistraVacuna2(pNombreAnimal tinytext, pNombreVacuna tinytext, pfecha date)BEGIN

DECLARE done BOOLEAN DEFAULT FALSE;DECLARE id integer;DECLARE nomb tinytext;DECLARE c1 cursor for SELECT idanimal, nombre From animal ORDER BY idanimal ASC;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;open c1;

c1_loop: LOOPFETCH c1 INTO id,nomb; IF done THEN LEAVE c1_loop; END IF; if strcmp(nomb,pNombreAnimal) = 0 then

INSERT INTO vacunas (Nombre, Id_Animal, fecha) VALUES (pNombreVacuna, id, pfecha); end if; END LOOP c1_loop;close c1;END //delimiter ;

Ejemplo Nº 3 delimiter //CREATE PROCEDURE RegistraVacuna3(pNombreAnimal tinytext, pNombreVacuna tinytext, pfecha date)



BEGINDECLARE done BOOLEAN DEFAULT FALSE;DECLARE id integer;DECLARE nomb tinytext;DECLARE c1 cursor for SELECT idanimal, nombre from animal ORDER BY idanimal ASC;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;open c1;REPEAT FETCH c1 INTO id,nomb; if strcmp(nomb,pNombreAnimal)=0 then

INSERT INTO vacunas (Nombre,Id_Animal,fecha) VALUES (pNombreVacuna, id, pfecha); end if; UNTIL done END REPEAT;close c1;END //delimiter ;



ANEXO I

TIPOS DE DATOS DE MYSQL

LISTADO Y DESCRIPCIÓN DE LOS DISTINTOS TIPOS DE DATOS DE MYSQL.

Después de la fase de diseño de una base de datos, en necesario crear las tablas correspondientes dentro de la base de datos. Para cada campo de cada una de las tablas, es necesario determinar el tipo de datos que contiene, para de esa forma ajustar el diseño de la base de datos, y conseguir un almacenamiento óptimo con la menor utilización de espacio. El presente artículo describe cada uno de los tipos de datos que puede tener un campo en Mysql.

Los tipos de datos que puede haber en un campo, se pueden agrupar en tres grandes grupos:

1.- TIPOS NUMÉRICOS:

Existen tipos de datos numéricos, que se pueden dividir en dos grandes grupos, los que están en coma flotante (con decimales) y los que no.

TinyInt: es un número entero con o sin signo. Con signo el rango de valores válidos va desde -128 a 127. Sin signo, el rango de valores es de 0 a 255 Bit ó Bool: un número entero que puede ser 0 ó 1SmallInt: número entero con o sin signo. Con signo el rango de valores va desde -32768 a 32767. Sin signo, el rango de valores es de 0 a 65535. MediumInt: número entero con o sin signo. Con signo el rango de valores va desde -8.388.608 a 8.388.607. Sin signo el rango va desde 0 a16777215. Integer, Int: número entero con o sin signo. Con signo el rango de valores va desde -2147483648 a 2147483647. Sin signo el rango va desde 0 a 429.4967.295 BigInt: número entero con o sin signo. Con signo el rango de valores va desde -9.223.372.036.854.775.808 a 9.223.372.036.854.775.807. Sin signo el rango va desde 0 a 18.446.744.073.709.551.615. Float: número pequeño en coma flotante de precisión simple. Los valores válidos van desde -3.402823466E+38 a -1.175494351E-38, 0 y desde 1.175494351E-38 a 3.402823466E+38. xReal, Double: número en coma flotante de precisión doble. Los valores permitidos van desde -1.7976931348623157E+308 a -2.2250738585072014E-308, 0 y desde 2.2250738585072014E-308 a 1.7976931348623157E+308Decimal, Dec, Numeric: Número en coma flotante desempaquetado. El número se almacena como una cadena


Almacenamiento

TINYINT 1 byte

SMALLINT 2 bytes



MEDIUMINT 3 bytes

INT 4 bytes

INTEGER 4 bytes

BIGINT 8 bytes

FLOAT(X) 4 ú 8 bytes

FLOAT 4 bytes

DOUBLE 8 bytes

DOUBLE PRECISION

8 bytes

REAL 8 bytes

DECIMAL(M,DM+2 bytes sí D > 0, M+1

bytes sí D = 0

NUMERIC(M,D)M+2 bytes if D > 0, M+1

bytes if D = 0

2.- TIPOS FECHA:

A la hora de almacenar fechas, hay que tener en cuenta que Mysql no comprueba de una manera estricta si una fecha es válida o no. Simplemente comprueba que el mes esta comprendido entre 0 y 12 y que el día esta comprendido entre 0 y 31.

Date: tipo fecha, almacena una fecha. El rango de valores va desde el 1 de enero del 1001 al 31 de diciembre de 9999. El formato de almacenamiento es de año-mes-dia

DateTime: Combinación de fecha y hora. El rango de valores va desde el 1 de enero del 1001 a las 0 horas, 0 minutos y 0 segundos al 31 de diciembre del 9999 a las 23 horas, 59 minutos y 59 segundos. El formato de almacenamiento es de año-mes-dia horas:minutos:segundos

TimeStamp: Combinación de fecha y hora. El rango va desde el 1 de enero de 1970 al año 2037. El formato de almacenamiento depende del tamaño del campo:

Tamaño Formato


aaaammddhhmmss


aammddhhmmss

8 ñoMesDia aaaammdd

6 AñoMesDia aammdd

4 AñoMes aamm

2 Año aa

Time: almacena una hora. El rango de horas va desde -838 horas, 59 minutos y 59 segundos a 838, 59 minutos y 59 segundos. El formato de almacenamiento es de 'HH:MM:SS'



Year: almacena un año. El rango de valores permitidos va desde el año 1901 al año 2155. El campo puede tener tamaño dos o tamaño 4 dependiendo de si queremos almacenar el año con dos o cuatro dígitos.


Almacenamiento

DATE 3 bytes

DATETIME 8 bytes

TIMESTAMP 4 bytes

TIME 3 bytes

YEAR 1 byte

3.- TIPOS DE CADENA:

Char(n): almacena una cadena de longitud fija. La cadena podrá contener desde 0 a 255 caracteres.

VarChar(n): almacena una cadena de longitud variable. La cadena podrá contener desde 0 a 255 caracteres.

Dentro de los tipos de cadena se pueden distinguir otros dos subtipos, los tipo Test y los tipo BLOB (Binary large Object)

La diferencia entre un tipo y otro es el tratamiento que reciben a la hora de realizar ordenamientos y comparaciones. Mientras que el tipo test se ordena sin tener en cuenta las Mayúsculas y las minúsculas, el tipo BLOB se ordena teniéndolas en cuenta.

Los tipos BLOB se utilizan para almacenar datos binarios como pueden ser ficheros.

TinyText y TinyBlob: Columna con una longitud máxima de 255 caracteres.

Blob y Text: un texto con un máximo de 65535 caracteres.

MediumBlob y MediumText: un texto con un máximo de 16.777.215 caracteres.

LongBlob y LongText: un texto con un máximo de caracteres 4.294.967.295. Hay que tener en cuenta que debido a los protocolos de comunicación los paquetes pueden tener un máximo de 16 Mb.

Enum: campo que puede tener un único valor de una lista que se especifica. El tipo Enum acepta hasta 65535 valores distintos

Set: un campo que puede contener ninguno, uno ó varios valores de una lista. La lista puede tener un máximo de 64 valores.

Tipo de campo Tamaño de Almacenamiento

CHAR(n) n bytes

VARCHAR(n) n +1 bytes

TINYBLOB, TINYTEXT Longitud+1 bytes

BLOB, TEXT Longitud +2 bytes

MEDIUMBLOB, MEDIUMTEXT Longitud +3 bytes



LONGBLOB, LONGTEXT Longitud +4 bytes

ENUM('value1','value2',...)1 ó dos bytes dependiendo del número de

valores

SET('value1','value2',...)1, 2, 3, 4 ó 8 bytes, dependiendo del número

de valores

Diferencia de almacenamiento entre los tipos Char y VarChar

Valor CHAR(4)Almace

namientoVARCHAR(4)

Almacenamiento

'' '' 4 bytes " 1 byte

'ab' 'ab ' 4 bytes 'ab' 3 bytes

'abcd' 'abcd' 4 bytes 'abcd'

'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes



ANEXO II

DESDE LA CONSOLA MYSQL

Operaciones básicas

Operación ComandoEntrar a la consola de MySQL mysqlEntrar a la consola de MySQL como el usuario usuario

mysql -u usuario -p

Salir de la consola de MySQL \qAyuda de la consola (hay que estar en ella)

\help o \h

Operaciones con Bases de datos

Operación ComandoCrear base de datos create database basededatos ;Eliminar base de datos drop database basededatos ;Mostrar las bases de datos disponibles

show databases ;

Trabajar sobre con una base de datos

use basededatos ;

Operaciones con Tablas

Operación ComandoMostrar tablas de la BD show tables from basededatos ;Muestra los campos de la tabla show columns from tabla ; o describe tabla ;Crear tabla create table nombretabla (columna1 tipodato, columna2

tipodato...) ;Crear tabla temporal create temporary table nombretabla (columna1 tipodato);Crear tabla verificando que no existe

create table inf not exists nombretabla (columna1 tipodato, columna2 tipodato...) ;

Eliminar tabla drop table nombretabla ;Editar tabla alter table nombretabla operacion ;Cambiar nombre a tabla alter table nombretablaviejo rename nombretablanuevo;Bloquea tabla lock nombretabla1, nombretabla2... ;Desbloquea tabla unlock nombretabla1 READ|WRITE, nombretabla2 READ|

WRITE... ;- -

Operaciones con Columnas

Operación ComandoAñadir columna alter table nombretabla ADD nombrecolumna tipodato;Cambia el tipo de dato de la columna

alter table nombretabla change nombrecolumna nombrecolumna nuevotipodato;

Cambiar el nombre de la columna

alter table nombretabla change nombrecolumnaviejo nombrecolumnanuevo tipodato;



Eliminar columna alter table nombretabla drop nombrecolumna;Añadir índice a columna create index nombreíndice on nombretabla (nombrecolumna1,...);

ó alter table nombretabla add index (nombrecolumna);Añadir campo clave (key) create primary key on nombretabla (nombrecolumna1,...); ó

alter table nombretabla add primary key (nombrecolumna);Eliminar campo clave (key) alter table nombretabla drop primary key;

Operaciones con Datos

Operación ComandoInsertar nuevo dato en tabla insert into nombretabla values

(valorcampo1,'valorcampo2',valorcampo3...);Importar archivo de datos a tabla load data infile 'archivo.txt' into table nombretabla;Seleccionar datos de una tabla select nombrecampo1, nombrecampo2... from nombretabla

where condiciónBorrar todos los datos de una tabla (conserva la tabla con sus campos)

delete from nombretabla;

Actualizar un dato del campo1 update nombretabla SET nombrecampo1='nuevovalorcampo' WHERE nombrecampo2='valorcampo2';

Contar registros que cumplen un criterio

select count(campos) from nombretabla;

Operaciones con Índices

Operación ComandoCrear índice create index nombreindice on

nombretabla(listanombrescolumnas);Elimina índice drop index indexname on tablename; o alter table

nombretabla drop index nombreindice;Mostrar claves show keys from nombretabla ; o show index from

nombretabla;



ANEXO III

INSTALAR MYSQL EN EL DSN DEL SISTEMA (MYODBC)

CÓMO INSTALAR MYSQL EN EL DSN DE SISTEMA

Podemos instalar MySQL como origen de datos de sistema ODBC para, por ejemplo, conectar a las bases de datos de manera remota.

Para ello necesitamos añadir esa base de datos al DSN del sistema.

Los pasos para hacer esto son :o Instalar controladores ODBC para MySQL: MyODBC.o Añadir la base de datos que queramos a la lista de DSN de sistema

El ejemplo siguiente es aplicable a plataformas Microsoft Windows 2000.

INSTALAR CONTROLADORES ODBC PARA MYSQL: MYODBC

Descargamos los controladores MyODBC de I nternet . Descomprimimos el archivo y ejecutamos SETUP.EXE Seguimos las instrucciones en pantalla hasta concluir la instalación.

AÑADIR LA BASE DE DATOS MYSQL AL DSN DE SISTEMA

Vamos a Inicio\Configuración\Panel de control\Herramientas administrativas\Orígenes de datos (ODBC).

Se abre la ventana 'Administrador de orígenes de datos ODBC'. Pulsamos la pestaña 'DNS de sistema'. Pulsamos 'Agregar'. Como controlador para el origen de los datos elegimos 'MySQL' y pulsamos finalizar. Se abre la ventana 'TDX MySQL driver default configuration'.

o En 'Windows DSN name' ponemos el nombre que queremos dar a esa conexión. Ej: Musica2002.

o En 'MySQL database name' ponemos el nombre de la base de datos MySQL que queremos añadir al DSN (la base de datos debe existir previamente). Ej: Musica2002.

o En principio, los demás campos pueden dejarse en blanco. Pulsamos 'OK'. La base de datos debe aparecer entonces en la lista de DSN de sistema.


http://www.mysql.com/downloads/api-myodbc.html

http://www.mysql.com/downloads/api-myodbc.html


ANEXO IV

REFERENCIA DE USO DE 'ALTER TABLE' EN MYSQL

SOBRE LA TABLA

ALTER TABLE ejemplo ENGINE = InnoDBCambiar el tipo de motor (engine) de la tabla 'ejemplo' ALTER TABLE personas RENAME usuariosCambia el nomnbre de la tabla 'personas' a 'usuarios'

ALTER TABLE ejemplo AUTO_INCREMENT=1000En la tabla 'ejemplo' cualquiera que sea la columna que tenga 'AUTO_INCREMENT' en sus propiedades (solo puede haber una), los nuevos registros comenzarán a partir de '1000' o cualquier número indicado, no es posible utilizar un valor ya existente.

ALTER TABLE ejemplo CONVERT TO CHARACTER SET latin1La tabla 'ejemplo' ahora almacenará sus valores en base al juego de caracteres 'latin1' (iso-8859-1).

OPERACIONES CON DROP

ALTER TABLE ejemplo DROP COLUMN nombreElimina la columna 'nombre' de la tabla 'ejemplo'.

ALTER TABLE ejemplo DROP COLUMN nombre, DROP COLUMN paternoElimina más de una columna.

ALTER TABLE ejemplo DROP COLUMN nombre, DROP COLUMN paternoElimina más de una columna.

ALTER TABLE ejemplo DROP INDEX usuarioElimina el índice 'usuario'.

ALTER TABLE ejemplo DROP PRIMARY KEYElimina la llave primaria de la tabla 'ejemplo'

ALTER TABLE ejemplo DROP FOREIGN KEY id_usuarioElimina de la tabala 'ejemplo' la llave foranea 'id_usuario'.

OPERACIONES CON CHANGE Y MODIFY

ALTER TABLE ejemplo CHANGE monto cantidad FLOAT(8,2)Cambia el nombre de la columna 'monto' al nuevo nombre 'cantidad' con la definición del tipo de datos.

ALTER TABLE ejemplo CHANGE cantidad cantidad FLOAT(10,2)Cambia solo el tipo de datos de la columna, conservando el mismo nombre. ALTER TABLE ejemplo MODIFY cantidad FLOAT(10,2)Cambia solo el tipo de datos de la columna, conservando el mismo nombre. (Igual que el anterior)



ALTER TABLE ejemplo MODIFY cantidad FLOAT(6,2) NOT NULLCambia el tipo de datos de la columna 'cantidad' y especifica que no admite nulos.

ALTER TABLE ejemplo MODIFY paterno VARCHAR(30)Modifica el tamaño de la columna 'paterno'.

ALTER TABLE ejemplo MODIFY correo CONVERT TO CHARACTER SET utf8Es posible convertir solo una columna, en este caso 'correo' a un juego de caracteres distinto al de todo el resto de la tabla.

OPERACIONES CON ADD

ALTER TABLE ejemplo ADD fecha DATEAñade una columna llamada 'fecha' del tipo 'DATE' al final de todas las demás existentes.

ALTER TABLE ejemplo ADD INDEX(categoria)Añade un índice a la columna 'categoria'.

ALTER TABLE ejemplo ADD INDEX(categoria), ADD PRIMARY KEY(clave)Añade un índice a la columna 'categoria' y además crea la llave primaria en la columna 'clave'.

ALTER TABLE ejemplo ADD UNIQUE(email)Añade a la columna 'email' un índice del tipo único, no puede haber dos iguales.

ALTER TABLE ejemplo ADD consecutivo BIGINT AUTO_INCREMENT, ADD INDEX(consecutivo)Añade la columna 'consecutivo' con la característica de auto incremento y además genera un índice sobre la misma.

ALTER TABLE ejemplo ADD materno VARCHAR(20) AFTER paternoAñade la columna 'materno' después de la columna 'paterno'.

ALTER TABLE ejemplo ADD id INT FIRSTAñade la columna 'id' en primer lugar con respecto a las existentes.

ALTER TABLE usuarios ADD FOREIGN KEY(id) REFERENCES entradas(id_user)Añade un 'Foreign key' en la columna 'id' de la tabla 'usuarios' que apunta a la columna 'id_user' de la tabla 'entradas'.



ANEXO IV

FUNCIONES

Si consideramos que MySQL es rico en lo que respecta a operadores, en lo que se refiere a funciones, podemos considerarlo millonario. MySQL dispone de multitud de funciones.

Pero no las explicaremos aquí, ya que este curso incluye una referencia completa. Tan sólo las agruparemos por tipos, e incluiremos los enlaces correspondientes a la documentación de cada una.

FUNCIONES DE CONTROL DE FLUJO

Las funciones de esta categoría son:

IF Elección en función de una expresión booleanaIFNULL Elección en función de si el valor de una expresión es NULLNULLIF Devuelve NULL en función del valor de una expresión

FUNCIONES MATEMÁTICAS

Las funciones de la categoría de matemáticas son:

ABS Devuelve el valor absolutoACOS Devuelve el arcocosenoASIN Devuelve el arcosenoATAN y ATAN2 Devuelven el arcotangenteCEILING y CEIL

Redondeo hacia arriba

COS Coseno de un ánguloCOT Cotangente de un ánguloCRC32 Cálculo de comprobación de redundancia cíclicaDEGREES Conversión de grados a radianesEXP Cálculo de potencias de eFLOOR Redondeo hacia abajoLN Logaritmo naturalLOG Logaritmo en base arbitrariaLOG10 Logaritmo en base 10LOG2 Logaritmo en base dosMOD o % Resto de una división enteraPI Valor del número πPOW o POWER Valor de potenciasRADIANS Conversión de radianes a gradosRAND Valores aleatoriosROUND Cálculo de redondeosSIGN Devuelve el signoSIN Cálculo del seno de un ánguloSQRT Cálculo de la raíz cuadradaTAN Cálculo de la tangente de un ánguloTRUNCATE Elimina decimales


http://mysql.conclase.net/curso/?sqlfun=TRUNCATE_

http://mysql.conclase.net/curso/?sqlfun=TAN

http://mysql.conclase.net/curso/?sqlfun=SQRT

http://mysql.conclase.net/curso/?sqlfun=SIN

http://mysql.conclase.net/curso/?sqlfun=SIGN

http://mysql.conclase.net/curso/?sqlfun=ROUND

http://mysql.conclase.net/curso/?sqlfun=RAND

http://mysql.conclase.net/curso/?sqlfun=RADIANS

http://mysql.conclase.net/curso/?sqlfun=POW

http://mysql.conclase.net/curso/?sqlfun=PI

http://mysql.conclase.net/curso/?sqlfun=MOD

http://mysql.conclase.net/curso/?sqlfun=LOG2

http://mysql.conclase.net/curso/?sqlfun=LOG10

http://mysql.conclase.net/curso/?sqlfun=LOG

http://mysql.conclase.net/curso/?sqlfun=LN

http://mysql.conclase.net/curso/?sqlfun=FLOOR

http://mysql.conclase.net/curso/?sqlfun=EXP

http://mysql.conclase.net/curso/?sqlfun=DEGREES

http://mysql.conclase.net/curso/?sqlfun=CRC32

http://mysql.conclase.net/curso/?sqlfun=COT

http://mysql.conclase.net/curso/?sqlfun=COS

http://mysql.conclase.net/curso/?sqlfun=CEILING

http://mysql.conclase.net/curso/?sqlfun=CEILING

http://mysql.conclase.net/curso/?sqlfun=ATAN

http://mysql.conclase.net/curso/?sqlfun=ASIN

http://mysql.conclase.net/curso/?sqlfun=ACOS

http://mysql.conclase.net/curso/?sqlfun=ABS

http://mysql.conclase.net/curso/?sqlfun=NULLIF

http://mysql.conclase.net/curso/?sqlfun=IFNULL

http://mysql.conclase.net/curso/?sqlfun=IF


FUNCIONES DE CADENAS

Las funciones para tratamiento de cadenas de caracteres son:

ASCII Valor de código ASCII de un carácterBIN Converión a binarioBIT_LENGTH Cálculo de longitud de cadena en bitsCHAR Convierte de ASCII a carácterCHAR_LENGTH o CHARACTER_LENGTH

Cálculo de longitud de cadena en caracteres

COMPRESS Comprime una cadena de caracteresCONCAT Concatena dos cadenas de caracteresCONCAT_WS Concatena cadenas con separadoresCONV Convierte números entre distintas basesELT Elección entre varias cadenasEXPORT_SET Expresiones binarias como conjuntosFIELD Busca el índice en listas de cadenasFIND_IN_SET Búsqueda en listas de cadenasHEX Conversión de números a hexadecimalINSERT Inserta una cadena en otraINSTR Busca una cadena en otraLEFT Extraer parte izquierda de una cadenaLENGTH u OCTET_LENGTH Calcula la longitud de una cadena en bytesLOAD_FILE Lee un fichero en una cadenaLOCATE o POSITION Encontrar la posición de una cadena dentro de otraLOWER o LCASE Convierte una cadena a minúsculasLPAD Añade caracteres a la izquierda de una cadenaLTRIM Elimina espacios a la izquierda de una cadenaMAKE_SET Crea un conjunto a partir de una expresión binariaOCT Convierte un número a octalORD Obtiene el código ASCII, incluso con caracteres

multibyteQUOTE Entrecomilla una cadenaREPEAT Construye una cadena como una repetición de otraREPLACE Busca una secuencia en una cadena y la sustituye por otraREVERSE Invierte el orden de los caracteres de una cadenaRIGHT Devuelve la parte derecha de una cadenaRPAD Inserta caracteres al final de una cadenaRTRIM Elimina caracteres blancos a la derecha de una cadenaSOUNDEX Devuelve la cadena "soundex" para una cadena concretaSOUNDS LIKE Compara cadenas según su pronunciaciónSPACE Devuelve cadenas consistentes en espaciosSUBSTRING o MID Extraer subcadenas de una cadenaSUBSTRING_INDEX Extraer subcadenas en función de delimitadoresTRIM Elimina sufijos y/o prefijos de una cadena.UCASE o UPPER Convierte una cadena a mayúsculasUNCOMPRESS Descomprime una cadena comprimida mediante

COMPRESSUNCOMPRESSED_LENGTH Calcula la longitud original de una cadena comprimidaUNHEX Convierte una cadena que representa un número

hexadecimal a cadena de caracteres


http://mysql.conclase.net/curso/?sqlfun=UNHEX

http://mysql.conclase.net/curso/?sqlfun=UNCOMPRESSED_LENGTH

http://mysql.conclase.net/curso/?sqlfun=COMPRESS

http://mysql.conclase.net/curso/?sqlfun=UNCOMPRESS

http://mysql.conclase.net/curso/?sqlfun=UCASE

http://mysql.conclase.net/curso/?sqlfun=TRIM

http://mysql.conclase.net/curso/?sqlfun=SUBSTRING_INDEX

http://mysql.conclase.net/curso/?sqlfun=SUBSTRING

http://mysql.conclase.net/curso/?sqlfun=SPACE

http://mysql.conclase.net/curso/?sqlfun=SOUNDS_LIKE

http://mysql.conclase.net/curso/?sqlfun=SOUNDEX

http://mysql.conclase.net/curso/?sqlfun=RTRIM

http://mysql.conclase.net/curso/?sqlfun=RPAD

http://mysql.conclase.net/curso/?sqlfun=RIGHT

http://mysql.conclase.net/curso/?sqlfun=REVERSE

http://mysql.conclase.net/curso/?sqlfun=REPLACE

http://mysql.conclase.net/curso/?sqlfun=REPEAT

http://mysql.conclase.net/curso/?sqlfun=QUOTE

http://mysql.conclase.net/curso/?sqlfun=ORD

http://mysql.conclase.net/curso/?sqlfun=OCT

http://mysql.conclase.net/curso/?sqlfun=MAKE_SET

http://mysql.conclase.net/curso/?sqlfun=LTRIM

http://mysql.conclase.net/curso/?sqlfun=LPAD

http://mysql.conclase.net/curso/?sqlfun=LOWER

http://mysql.conclase.net/curso/?sqlfun=LOCATE

http://mysql.conclase.net/curso/?sqlfun=LOAD_FILE

http://mysql.conclase.net/curso/?sqlfun=LENGTH

http://mysql.conclase.net/curso/?sqlfun=LEFT

http://mysql.conclase.net/curso/?sqlfun=INSTR

http://mysql.conclase.net/curso/?sqlfun=INSERT_

http://mysql.conclase.net/curso/?sqlfun=HEX

http://mysql.conclase.net/curso/?sqlfun=FIND_IN_SET

http://mysql.conclase.net/curso/?sqlfun=FIELD

http://mysql.conclase.net/curso/?sqlfun=EXPORT_SET

http://mysql.conclase.net/curso/?sqlfun=ELT

http://mysql.conclase.net/curso/?sqlfun=CONV

http://mysql.conclase.net/curso/?sqlfun=CONCAT_WS

http://mysql.conclase.net/curso/?sqlfun=CONCAT

http://mysql.conclase.net/curso/?sqlfun=COMPRESS

http://mysql.conclase.net/curso/?sqlfun=CHAR_LENGTH

http://mysql.conclase.net/curso/?sqlfun=CHAR_LENGTH

http://mysql.conclase.net/curso/?sqlfun=CHAR

http://mysql.conclase.net/curso/?sqlfun=BIT_LENGTH

http://mysql.conclase.net/curso/?sqlfun=BIN

http://mysql.conclase.net/curso/?sqlfun=ASCII


FUNCIONES DE COMPARACIÓN DE CADENAS

Además de los operadores que vimos para la comparación de cadenas, existe una función:

STRCMP Compara cadenas

FUNCIONES DE FECHA

Funciones para trabajar con fechas:

ADDDATE Suma un intervalo de tiempo a una fechaADDTIME Suma tiemposCONVERT_TZ Convierte tiempos entre distintas zonas horariasCURDATE o CURRENTDATE Obtener la fecha actualCURTIME o CURRENT_TIME Obtener la hora actualDATE Extraer la parte correspondiente a la fechaDATEDIFF Calcula la diferencia en días entre dos fechasDATE_ADD Aritmética de fechas, suma un intervalo de tiempoDATE_SUB Aritmética de fechas, resta un intervalo de tiempoDATE_FORMAT Formatea el valor de una fechaDAY o DAYOFMONTH Obtiene el día del mes a partir de una fechaDAYNAME Devuelve el nombre del día de la semanaDAYOFWEEK Devuelve el índice del día de la semanaDAYOFYEAR Devuelve el día del año para una fechaEXTRACT Extrae parte de una fechaFROM_DAYS Obtener una fecha a partir de un número de díasFROM_UNIXTIME Representación de fechas UNIX en formato de cadenaGET_FORMAT Devuelve una cadena de formatoHOUR Extrae la hora de un valor timeLAST_DAY Devuelve la fecha para el último día del mes de una

fechaMAKEDATE Calcula una fecha a partir de un año y un día del añoMAKETIME Calcula un valor de tiempo a partir de una hora, minuto

y segundoMICROSECOND Extrae los microsegundos de una expresión de

fecha/hora o de horaMINUTE Extrae el valor de minutos de una expresión timeMONTH Devuelve el mes de una fechaMONTHNAME Devuelve el nombre de un mes para una fechaNOW o CURRENT_TIMESTAMP o LOCALTIME o LOCALTIMESTAMP o SYSDATE

Devuelve la fecha y hora actual

PERIOD_ADD Añade meses a un periodo (año/mes)PERIOD_DIFF Calcula la diferencia de meses entre dos periodos

(año/mes)QUARTER Devuelve el cuarto del año para una fechaSECOND Extrae el valor de segundos de una expresión timeSEC_TO_TIME Convierte una cantidad de segundos a horas, minutos y

segundosSTR_TO_DATE Obtiene un valor DATETIME a partir de una cadena con

una fecha y una cadena de formatoSUBDATE Resta un intervalo de tiempo de una fechaSUBTIME Resta dos expresiones time


http://mysql.conclase.net/curso/?sqlfun=SUBTIME

http://mysql.conclase.net/curso/?sqlfun=SUBDATE

http://mysql.conclase.net/curso/?sqlfun=STR_TO_DATE

http://mysql.conclase.net/curso/?sqlfun=SEC_TO_TIME

http://mysql.conclase.net/curso/?sqlfun=SECOND

http://mysql.conclase.net/curso/?sqlfun=QUARTER

http://mysql.conclase.net/curso/?sqlfun=PERIOD_DIFF

http://mysql.conclase.net/curso/?sqlfun=PERIOD_ADD

http://mysql.conclase.net/curso/?sqlfun=NOW

http://mysql.conclase.net/curso/?sqlfun=NOW

http://mysql.conclase.net/curso/?sqlfun=MONTHNAME

http://mysql.conclase.net/curso/?sqlfun=MONTH

http://mysql.conclase.net/curso/?sqlfun=MINUTE

http://mysql.conclase.net/curso/?sqlfun=MICROSECOND

http://mysql.conclase.net/curso/?sqlfun=MAKETIME

http://mysql.conclase.net/curso/?sqlfun=MAKEDATE

http://mysql.conclase.net/curso/?sqlfun=LAST_DAY

http://mysql.conclase.net/curso/?sqlfun=HOUR

http://mysql.conclase.net/curso/?sqlfun=GET_FORMAT

http://mysql.conclase.net/curso/?sqlfun=FROM_UNIXTIME

http://mysql.conclase.net/curso/?sqlfun=FROM_DAYS

http://mysql.conclase.net/curso/?sqlfun=EXTRACT

http://mysql.conclase.net/curso/?sqlfun=DAYOFYEAR

http://mysql.conclase.net/curso/?sqlfun=DAYOFWEEK

http://mysql.conclase.net/curso/?sqlfun=DAYNAME

http://mysql.conclase.net/curso/?sqlfun=DAYOFMONTH

http://mysql.conclase.net/curso/?sqlfun=DATE_FORMAT

http://mysql.conclase.net/curso/?sqlfun=DATE_ADD

http://mysql.conclase.net/curso/?sqlfun=DATE_ADD

http://mysql.conclase.net/curso/?sqlfun=DATEDIFF

http://mysql.conclase.net/curso/?sqlfun=DATE

http://mysql.conclase.net/curso/?sqlfun=CURTIME

http://mysql.conclase.net/curso/?sqlfun=CURDATE

http://mysql.conclase.net/curso/?sqlfun=CONVERT_TZ

http://mysql.conclase.net/curso/?sqlfun=ADDTIME

http://mysql.conclase.net/curso/?sqlfun=ADDDATE

http://mysql.conclase.net/curso/?sqlfun=STRCMP


TIME Extrae la parte de la hora de una expresión fecha/horaTIMEDIFF Devuelve en tiempo entre dos expresiones de tiempoTIMESTAMP Convierte una expresión de fecha en fecha/hora o

suma un tiempo a una fechaTIMESTAMPADD Suma un intervalo de tiempo a una expresión de

fecha/horaTIMESTAMPDIFF Devuelve la diferencia entre dos expresiones de

fecha/horaTIME_FORMAT Formatea un tiempoTIME_TO_SEC Convierte un tiempo a segundosTO_DAYS Calcula el número de días desde el año ceroUNIX_TIMESTAMP Devuelve un timestamp o una fecha en formato UNIX,

segundos desde 1070UTC_DATE Devuelve la fecha UTC actualUTC_TIME Devuelve la hora UTC actualUTC_TIMESTAMP Devuelve la fecha y hora UTC actualWEEK Calcula el número de semana para una fechaWEEKDAY Devuelve el número de día de la semana para una

fechaWEEKOFYEAR Devuelve el número de la semana del año para una

fechaYEAR Extrae el año de una fechaYEARWEEK Devuelve el año y semana de una fecha

DE BÚSQUEDA DE TEXTO

Función de búsqueda de texto:

MATCH

FUNCIONES DE CASTING (CONVERSIÓN DE TIPOS)CAST o CONVERT Conversión de tipos explícita

MISCELANEA

Funciones generales:

DEFAULT Devuelve el valor por defecto para una columnaFORMAT Formatea el número según la plantilla '#,###,###.##GET_LOCK Intenta obtener un bloqueo con el nombre dadoINET_ATON Obtiene el entero equivalente a la dirección de red dada en formato de cuarteto con

puntosINET_NTOA Obtiene la dirección en formato de cuarteto con puntos dado un enteroIS_FREE_LOCK Verifica si un nombre de bloqueo está libreIS_USED_LOCK Verifica si un nombre de bloqueo está en usoMASTER_POS_WAIT Espera hasta que el esclavo alcanza la posición especificada en el diario maestroRELEASE_LOCK Libera un bloqueoUUID Devuelve un identificador único universal


http://mysql.conclase.net/curso/?sqlfun=UUID

http://mysql.conclase.net/curso/?sqlfun=RELEASE_LOCK

http://mysql.conclase.net/curso/?sqlfun=MASTER_POS_WAIT

http://mysql.conclase.net/curso/?sqlfun=IS_USED_LOCK

http://mysql.conclase.net/curso/?sqlfun=IS_FREE_LOCK

http://mysql.conclase.net/curso/?sqlfun=INET_NTOA

http://mysql.conclase.net/curso/?sqlfun=INET_ATON

http://mysql.conclase.net/curso/?sqlfun=GET_LOCK

http://mysql.conclase.net/curso/?sqlfun=FORMAT

http://mysql.conclase.net/curso/?sqlfun=DEFAULT

http://mysql.conclase.net/curso/?sqlfun=CAST

http://mysql.conclase.net/curso/?sqlfun=MATCH

http://mysql.conclase.net/curso/?sqlfun=YEARWEEK

http://mysql.conclase.net/curso/?sqlfun=YEAR

http://mysql.conclase.net/curso/?sqlfun=WEEKOFYEAR

http://mysql.conclase.net/curso/?sqlfun=WEEKDAY

http://mysql.conclase.net/curso/?sqlfun=WEEK

http://mysql.conclase.net/curso/?sqlfun=UTC_TIMESTAMP

http://mysql.conclase.net/curso/?sqlfun=UTC_TIME

http://mysql.conclase.net/curso/?sqlfun=UTC_DATE

http://mysql.conclase.net/curso/?sqlfun=UNIX_TIMESTAMP

http://mysql.conclase.net/curso/?sqlfun=TO_DAYS

http://mysql.conclase.net/curso/?sqlfun=TIME_TO_SEC

http://mysql.conclase.net/curso/?sqlfun=TIME_FORMAT

http://mysql.conclase.net/curso/?sqlfun=TIMESTAMPDIFF

http://mysql.conclase.net/curso/?sqlfun=TIMESTAMPADD

http://mysql.conclase.net/curso/?sqlfun=TIMESTAMP

http://mysql.conclase.net/curso/?sqlfun=TIMEDIFF

http://mysql.conclase.net/curso/?sqlfun=TIME


DE GRUPOS

Funciones de grupos:

AVG Devuelve el valor medioBIT_AND Devuelve la operación de bits AND para todos los bits de una expresiónBIT_OR Devuelve la operación de bits OR para todos los bits de una expresiónBIT_XOR Devuelve la operación de bits XOR para todos los bits de una expresiónCOUNT Devuelve el número de valores distintos de NULL en las filas recuperadas por una

sentencia SELECTCOUNT DISTINCT Devuelve el número de valores diferentes, distintos de NULLGROUP_CONCAT Devuelve una cadena con la concatenación de los valores de un grupoMIN Devuelve el valor mínimo de una expresiónMAX Devuelve el valor máximo de una expresiónSTD o STDDEV Devuelve la desviación estándar de una expresiónSUM Devuelve la suma de una expresiónVARIANCE Devuelve la varianza estándar de una expresión


http://mysql.conclase.net/curso/?sqlfun=VARIANCE

http://mysql.conclase.net/curso/?sqlfun=SUM

http://mysql.conclase.net/curso/?sqlfun=STD

http://mysql.conclase.net/curso/?sqlfun=MIN

http://mysql.conclase.net/curso/?sqlfun=MIN

http://mysql.conclase.net/curso/?sqlfun=GROUP_CONCAT

http://mysql.conclase.net/curso/?sqlfun=COUNT_DISTINCT

http://mysql.conclase.net/curso/?sqlfun=COUNT

http://mysql.conclase.net/curso/?sqlfun=BIT_XOR

http://mysql.conclase.net/curso/?sqlfun=BIT_OR

http://mysql.conclase.net/curso/?sqlfun=BIT_AND

http://mysql.conclase.net/curso/?sqlfun=AVG


ANEXO V

EJEMPLOS DE USO DE mysqldump

Respaldo de una sola base de datos completamysqldump clientes > clientes.sql

Respaldo de una sola base de datos con dos tablasmysqldump clientes saldos facturas > clientes.sql

Respaldo completo de base de datos clientes y ventasmysqldump --databases clientes ventas > respaldo_cli_ven_sep_2011.sql

Respaldamos la base de datos clientes pero ignoramos las tablas 'temporal' y 'basura' (Obligatorio indicar base.tabla)mysqldump clientes --ignore-table=clientes.temporal --ignore-table=clientes.basura > respaldo_clientes_2011.sql

Respaldo completo de todas las bases de datosmysqldump --all-databases > respaldo_full_sep_2011.sql

Si se tiene contraseña (como debe ser) se indica usuario y que pregunte por el passwordmysqldump -u root -p --all-databases > respaldo_full_sep_2011.sql

No muy buena idea, pero se puede indicar el password directamente, además nos aseguramos que se indiquen las opciones por defecto más comunesmysqldump -u root -psecreto --all-databases --opt > respaldo_full_sep_2011.sql

Respaldo de una base de datos transaccional tipo InnoDB o BDB asegurando su consistenciamysqldump -u root -p --single-transaction --quick ventas > respaldo_ventas_2011.sql

Todas las bases de datos del host 192.168.0.100 y agregamos los procedemientos almacenados que sean respaldados también.mysqldump -h 192.168.1.100 -u root -p --routines --all-databases > respaldo_ventas_2011.sql

Respaldo completo de un servidor MySQL maestro en replicación, indicando en el respaldo la posición para sincronización con servidores esclavos, además añadimos insertar completos que incluyen los nombres de columnas en sentencias INSERTmysqldump -u root --password=secreto --all-databases --master-data --complete-insert > respaldo_2011.sql Respaldamos solo el esquema de clientes sin registrosmysqldump --no-data clientes > respaldo_esquema_clientes_2011.sql

Se produce una salida compatible para restaurar la base de datos en Oraclemysqldump --compatible=oracle --databases clientes > respaldo_clientes_oracle_2011.sql

MYSQLDUMP CON GZIP

Al mismo tiempo que realizamos el respaldo podemos comprimirlo para ahorrar espacio en nuestros respaldos.mysqldump -u root -p --all-databases | gzip > respaldo_2011.sql.gz

Para descomprimir lo anterior y dejar el archivo listo para la restauración en si utiliza gunzipgunzip respaldo_2011.sql.gz

RESTAURANDO EL RESPALDOHay varias maneras de lograr la restauración del respaldo. Con el comando mysql:



mysql -u root -p < respaldo.sql

Si se utilizó gzip para comprimir el respaldo, se puede descomprimir y restaurar en una sola línea:gunzip < respaldo.sql.gz | mysql -u root -p

Si el respaldo contiene una sola base de datos y no contiene sentencias 'drop database' y 'create database', se puede entonces indicar la base de datos donde se debe realizar la restauración:mysql -u root -p clientes < respaldo_clientes.sql

Lo anterior (cuando ya existe la base de datos) también se puede lograr con el comando mysqlimportmysqlimport -u root -p clientes respaldo_clientes.sql

Es posible también utilizar la opción "-e" (execute) de mysql, que permite ejecutar un archivo con sentencias SQL, y un respaldo es exactamente eso, un largo script con sentencias SQL para recrear esquema y datos:mysql -u root -p -e "source /ruta/a/respaldo.sql"



Anexo VI

SQLSTATE vs códigos de error MySQL

En teoría es mejor utilizar codigos SQLSTATE porque son independientes de la plataforma y hacen a nuestro código más portable. Sin embargo, hay algunas razones para utilizar el código de error de MySQL, específicamente en la escritura de Stored Procedures.

El lenguaje para la escritura de procedimientos almacenados en Oracle y SQL Server es totalmente incompatible con el de MySQL. El de DB2, es algo más compatible (ambos basados en SQL:2003 standard).

No todos los códigos de error MySQL tienen su equivalente en código SQLSTATE. Cada error de Mysql está relacionado con un código de error SQLState, pero no siempre esta relación es uno a uno. HY000 es un código de error SQLSTATE para propósitos generales que devuelve MySQL cuando su código de error no tiene asociado un código SQLSTATE.

Errores comumes MySQL y códigos SQLSTATE

Código de error

MySQL

Código SQLSTATE

Mensaje de error

1011 HY000 Error on delete of ‘%s’ (errno: %d)

1021 HY000Disk full (%s); waiting for someone to free some space . . .

1022 23000 Can’t write; duplicate key in table ‘%s’

1027 HY000 ‘%s’ is locked against change

1036 HY000 Table ‘%s’ is read only

1048 23000 Column ‘%s’ cannot be null

1062 23000 Duplicate entry ‘%s’ for key %d

1099 HY000Table ‘%s’ was locked with a READ lock and can’t be updated

1100 HY000 Table ‘%s’ was not locked with LOCK TABLES

1104 42000

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

1106 42000 Incorrect parameters to procedure ‘%s’

1114 HY000 The table ‘%s’ is full

1150 HY000Delayed insert thread couldn’t get requested lock for table %s

1165 HY000INSERT DELAYEDcan’t be used with table ‘%s’ because it is locked withLOCK TABLES

1242 21000 Subquery returns more than 1 row

1263 22004 Column set to default value; NULL supplied to NOT



NULL column ‘%s’ at row %ld

1264 22003 Out of range value adjusted for column ‘%s’ at row %ld

1265 1000 Data truncated for column ‘%s’ at row %ld

1312 0A000 SELECTin a stored program must haveINTO

1317 70100 Query execution was interrupted

1319 42000 Undefined CONDITION: %s

1325 24000 Cursor is already open

1329 2000 No data to FETCH

1336 1337 42000 42000USEis not allowed in a stored program Variable or condition declaration after cursor or handler declaration

1338 42000 Cursor declaration after handler declaration

1339 20000 Case not found for CASE statement

1348 1357 HY000 HY000

Column ‘%s’ is not updatable Can’t drop a %s from within another stored routine

1358 1362 HY000 HY000

GOTOis not allowed in a stored program handler

1363 HY000 Updating of %s row is not allowed in %s trigger There is no %s row in %s trigger



ANEXO VII

Posibilidades de utilizar los permisos de usuarios en MySQL

Permiso Significado

ALL [PRIVILEGES] Da todos los permisos simples excepto GRANT OPTION

ALTER Permite el uso de ALTER TABLE

ALTER ROUTINE Modifica o borra rutinas almacenadas

CREATE Permite el uso de CREATE TABLE

CREATE ROUTINE Crea rutinas almacenadas

CREATE TEMPORARY TABLES Permite el uso de CREATE TEMPORARY TABLE

CREATE USERPermite el uso de CREATE USER, DROP USER, RENAME USER, y REVOKE ALL PRIVILEGES.

CREATE VIEW Permite el uso de CREATE VIEW

DELETE Permite el uso de DELETE

DROP Permite el uso de DROP TABLE

EXECUTE Permite al usuario ejecutar rutinas almacenadas

FILE Permite el uso de SELECT ... INTO OUTFILE y LOAD DATA INFILE

INDEX Permite el uso de CREATE INDEX y DROP INDEX

INSERT Permite el uso de INSERT

LOCK TABLES Permite el uso de LOCK TABLES en tablas para las que tenga el permiso SELECT

PROCESS Permite el uso de SHOW FULL PROCESSLIST

REFERENCES No implementado

RELOAD Permite el uso de FLUSH

REPLICATION CLIENT Permite al usuario preguntar dónde están los servidores maestro o esclavo

REPLICATION SLAVE

Necesario para los esclavos de replicación (para leer eventos del log binario desde el maestro)



SELECT Permite el uso de SELECT

SHOW DATABASES SHOW DATABASES muestra todas las bases de datos

SHOW VIEW Permite el uso de SHOW CREATE VIEW

SHUTDOWN Permite el uso de mysqladmin shutdown

SUPER

Permite el uso de comandos CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL , el comando mysqladmin debug le permite conectar (una vez) incluso si se llega a max_connections

UPDATE Permite el uso de UPDATE

USAGE Sinónimo de “no privileges”

GRANT OPTION Permite dar permisos



Para vaciar datos desde un terminal cliente al servidorLOAD DATA LOCAL INFILE " RegAnimal.txt" INTO TABLE Animal;

Desde el mismo servidor y en la carpeta de la base de datosLOAD DATA INFILE "RegAnimal.txt" INTO TABLE Animal;

Es recomendable renombrar la cuenta del administrador de MySQL (root). De esta forma estaremos disminuyendo la posibilidad de éxito de un ataque de fuerza bruta contra la contraseña del administrador. Para ello deberemos ejecutar:

UPDATE user SET user = "nuevonombre" WHERE user = "root";flush privileges;


Documents

Manual Práctico de MySQL