25 enero 2008

Funciones útiles en Oracle y MySQL

Por si alguien necesita cambiar entre MySQL y Oracle, aquí están algunas de las funciones más habituales que permiten desde SQL, y la equivalencia entre ambos sistemas:


Funciones de agregación

Las funciones que trabajan sobre un conjunto de datos, como COUNT, SUM,
AVG, MIN y MAX funcionarán en Oracle igual que en MySQL.



Funciones de cadenas



  • Convertir a minúsculas

  • MySQL: LOWER o LCASE

  • Oracle: LOWER: LOWER(nombre)



  • Convertir a mayúsculas

  • MySQL: UPPER o UCASE

  • Oracle: UPPER: UPPER(ciudad)




  • Extraer una subcadena

  • MySQL: Para cualquier posición: MID, SUBSTR o SUBSTRING; desde la izquierda: LEFT; desde la derecha: RIGHT

  • Oracle: SUBSTR: SUBSTR(nombre, 1, 4)




  • Ver si una cadena contiene a otra, y en qué posición

  • MySQL: INSTR o LOCATE

  • Oracle: INSTR: INSTR(nombre, 'Juan')




  • Eliminar espacios iniciales o finales

  • MySQL: RTRIM para la derecha, LTRIM a la izquierda, TRIM en ambos lados

  • Oracle: RTRIM, LTRIM, TRIM: TRIM(direccion)




  • Reemplazar parte de una cadena por otra

  • MySQL: REPLACE

  • Oracle: REPLACE: REPLACE(nombre, 'D.', 'Don')




  • Longitud de la cadena

  • MySQL: LENGTH

  • Oracle: LENGTH: LENGTH(nombre)




  • Formar una cadena a partir de varias

  • MySQL: CONCAT, para dos o más subcadenas

  • Oracle: CONCAT, para dos subcadenas: CONCAT(apellidos, nombre); doble barra para concatenar varias: apellido || ', ' || nombre




Funciones avanzadas


  • Esquivar valores nulos

  • MySQL: IFNULL: IFNULL(direccion, 'No disponible')

  • Oracle: NVL: NVL(direccion, 'No disponible')




  • Comprobar condiciones

  • MySQL: CASE: SELECT CASE tipoCliente WHEN 'p' THEN 'Propio' WHEN 'a' THEN 'Ajeno' ELSE 'Desconocido'; IF: SELECT IF(tipoCliente='p', 'Propio', 'Ajeno')

  • Oracle: DECODE: SELECT DECODE (tipoCliente, 'p','Propio', 'a','Ajeno', 'Desconocido' FROM CLIENTE

16 enero 2008

MySQL Básico (2)

(Nociones básicas de SQL usando MySQL, bajo Windows.
Un repaso a lo fundamental en 2 lecciones.
-Parte 2-
Si quieres sabes más, echa un vistazo en nachocabanes.com/sql/curso)

Hemos visto cómo introducir datos, cómo borrarlos y modificarlos, y también cómo hacer consultas básicas para acceder a la información que contiene nuestra base de datos. Ahora vamos a ver algunas consultas ligeramente más avanzadas...


10. Funciones de agregación

Podemos aplicar ciertas funciones matemáticas a todo un conjunto de datos de una tabla. Por ejemplo, podemos saber cual es la edad más baja de entre las personas que tenemos en nuestra base de datos, haríamos:

select min(edad) from personas;

Las funciones de agregación más habituales son:

  • min = mínimo valor
  • max = máximo valor
  • sum = suma de los valores
  • avg = media de los valores
  • count = cantidad de valores

La forma más habitual de usar "count" es pidiendo con "count(*)" que se nos muestren todos los datos que cumplen una condición. Por ejemplo, podríamos saber cuantas personas tienen una dirección que comience por la letra "s", así:

select count(*) from personas where direccion like 's%';

11. Cero y valor nulo

En ocasiones querremos dejar un campo totalmente vacío, sin valor. Para las cadenas de texto, existe una forma "parecida" de conseguirlo, que es con una cadena vacía, indicada con dos comillas que no contengan ningún texto entre ellas (ni siquiera espacios en blanco): '' ; En cambio, para los números, no basta con guardar un 0 para indicar que no se sabe el valor: no es lo mismo un importe de 0 euros que un importe no detallado. Por eso, existe un símbolo especial para indicar cuando no existe valor en un campo.

Este símbolo especial es la palabra NULL. Por ejemplo, añadiríamos datos parcialmente en blanco a una tabla haciendo

insert into personas
(nombre, direccion, edad)
values
(
'pedro', NULL, NULL
);

Para saber si algún campo está vacío, compararíamos su valor con NULL, pero de una forma un tanto especial: no con el símbolo "igual" (=), sino con la palabra IS. Por ejemplo, sabríamos cuales de las personas de nuestra bases de datos tienen dirección usando

select * from personas
where direccion is not null;

Y, de forma similar, sabríamos quien no tiene dirección, así:

select * from personas
where direccion is null;

12. Resultados agrupados

Puede ocurrir que no nos interese un único valor agrupado (el total, la media, la cantidad de datos), sino el resultado para un grupo de datos. Por ejemplo: saber no sólo la cantidad de clientes que hay registrados en nuestra base de datos, sino también la cantidad de clientes que viven en cada ciudad.

La forma de obtener subtotales es creando grupos con la orden "group by", y entonces pidiendo una valor agrupado (count, sum, avg, ...) para cada uno de esos grupos. Por ejemplo, en nuestra tabla "personas", podríamos saber cuantas personas aparecen de cada edad, con:

select count(*), edad from personas group by edad;

13. Cómo filtrar los datos agrupados

Podemos llegar más allá: podemos no trabajar con todos los grupos posibles, sino sólo con los que cumplen alguna condición.

La condición que se aplica a los grupos no se indica con "where", sino con "having" (que se podría traducir como "los que tengan..."). Por ejemplo, podríamos saber la cantidad de personas que tenemos con cada edad, pero considerando sólo las mayores de 24 años:

select count(*), edad from personas group by edad having edad > 24;

14. Subconsultas

A veces tenemos que realizar operaciones más complejas con los datos, operaciones en las que nos interesaría ayudarnos de una primera consulta auxiliar que extrajera la información en la que nos queremos basar. Esta consulta auxiliar recibe el nombre de "subconsulta" o "subquery".

Por ejemplo, si queremos saber qué clientes tenemos en la ciudad que más habitantes tenga, la forma "razonable" de conseguirlo sería saber en primer lugar cual es la ciudad que más habitantes tenga, y entonces lanzar una segunda consulta para ver qué clientes hay en esa ciudad.

Como la estructura de nuestra base de datos de ejemplo es muy sencilla, no podemos hacer grandes cosas, pero un caso parecido al anterior (aunque claramente más inútil) podría ser saber qué personas tenemos almacenadas que vivan en la última ciudad de nuestra lista.

Para ello, la primera consulta (la "subconsulta") sería saber cual es la última ciudad de nuestra lista. Si lo hacemos tomando la que tenga el último código, la consulta podría ser

SELECT MAX(codigo) FROM ciudades;

Vamos a imaginar que pudiéramos hacerlo en dos pasos. Si llamamos "maxCodigo" a ese código obtenido, la "segunda" consulta podría ser:

SELECT * FROM personas WHERE codciudad= maxCodigo;

Pero estos dos pasos se pueden dar en uno: al final de la "segunda" consulta (la "grande") incluimos la primera consulta (la "subconsulta"), entre paréntesis, así

SELECT * FROM personas WHERE codciudad= (
SELECT MAX(codigo) FROM ciudades
);

Si la subconsulta no devuelve un único dato, sino un conjunto de datos, la forma de trabajar será básicamente la misma, pero para comprobar si el valor coincide con uno de la lista, no usaremos el símbolo "=", sino la palabra "in".

Por ejemplo, vamos a hacer una consulta que nos muestre las personas que viven en ciudades cuyo nombre tiene una "a" en segundo lugar (por ejemplo, serían ciudades válidas Madrid o Barcelona, pero no Alicante).

Para consultar qué letras hay en ciertas posiciones de una cadena, podemos usar SUBSTRING (en el próximo apartado veremos las funciones más importantes de manipulación de cadenas). Así, una forma de saber qué ciudades tienen una letra A en su segunda posición sería:

SELECT codigo FROM ciudades 
WHERE SUBSTRING(nombre,2,1)='a';

Como esta subconsulta puede tener más de un resultado, deberemos usar IN para incluirla en la consulta principal, que quedaría de esta forma:

SELECT * FROM personas 
WHERE codciudad IN
(
SELECT codigo FROM ciudades WHERE SUBSTRING(nombre,2,1)='a'
);

15. Funciones de cadena

En MySQL tenemos muchas funciones para manipular cadenas: calcular su longitud, extraer un fragmento situado a la derecha, a la izquierda o en cualquier posición, eliminar espacios finales o iniciales, convertir a hexadecimal y a binario, etc. Vamos a comentar sólo las más habituales. Los ejemplos estarán aplicados directamente sobre cadenas, pero (por supuesto) también se pueden aplicar a campos de una tabla:

Funciones de conversión a mayúsculas/minúsculas

  • LOWER o LCASE convierte una cadena a minúsculas: SELECT LOWER('Hola'); -> hola
  • UPPER o UCASE convierte una cadena a mayúsculas: SELECT UPPER('Hola'); -> HOLA

Funciones de extracción de parte de la cadena

  • LEFT(cadena, longitud) extrae varios caracteres del comienzo (la parte izquierda) de la cadena: SELECT LEFT('Hola',2); -> Ho
  • RIGHT(cadena, longitud) extrae varios caracteres del final (la parte derecha) de la cadena: SELECT RIGHT('Hola',2); -> la
  • MID(cadena, posición, longitud), SUBSTR(cadena, posición, longitud) o SUBSTRING(cadena, posición, longitud) extrae varios caracteres de cualquier posición de una cadena, tantos como se indique en "longitud": SELECT SUBSTRING('Hola',2,2); -> ol (Nota: a partir MySQL 5 se permite un valor negativo en la posición, y entonces se comienza a contar desde la derecha -el final de la cadena-)
  • CONCAT une (concatena) varias cadenas para formar una nueva: SELECT CONCAT('Ho', 'la'); -> Hola
  • TRIM devuelve la cadena sin los espacios en blanco que pudiera contener al principio ni al final: SELECT TRIM(' Hola '); -> Hola (Nota: realmente, TRIM puede eliminar cualquier prefijo, no sólo espacios; mira el manual de MySQL para más detalles)

Otras funciones de modificación de la cadena

  • INSERT(cadena,posición,longitud,nuevaCadena) inserta en la cadena otra cadena: SELECT INSERT('Hola', 2, 2, 'ADIOS'); -> HADIOSa
  • REPLACE(cadena,de,a) devuelve la cadena pero cambiando ciertas secuencias de caracteres por otras: SELECT REPLACE('Hola', 'l', 'LLL'); -> HoLLLa
  • REPEAT(cadena,numero) devuelve la cadena repetida varias veces: SELECT REPEAT(' Hola',3); -> HolaHolaHola

Funciones de información sobre la cadena

  • CHAR_LENGTH o CHARACTER_LENGTH devuelve la longitud de la cadena en caracteres
  • INSTR(cadena,subcadena) o LOCATE(subcadena,cadena,posInicial) devuelve la posición de una subcadena dentro de la cadena: SELECT INSTR('Hola','ol'); -> 2

16. Enlazar tablas con "join"

Sabemos enlazar varias tablas para mostrar datos que estén relacionados. Por ejemplo, podríamos mostrar nombres de deportistas, junto con los nombres de los deportes que practican. Pero todavía hay un detalle que se nos escapa: ¿cómo hacemos si queremos mostrar todos los deportes que hay en nuestra base de datos, incluso aunque no haya deportistas que los practiquen?

Vamos a crear una base de datos sencilla para ver un ejemplo de cual es este problema y de cómo solucionarlo.

Nuestra base de datos se llamará "ejemploJoins":

create database ejemploJoins;
use
ejemploJoins;

En ella vamos a crear una primera tabla en la que guardaremos "capacidades" de personas (cosas que saben hacer):

create table capacidad(
codigo
varchar(4),
nombre varchar(20),
primary
key(codigo) );

También crearemos una segunda tabla con datos básicos de personas:

create table persona(
codigo
varchar(4),
nombre varchar(20),
codcapac
varchar(4),
primary
key(codigo) );

Vamos a introducir datos de ejemplo:

insert into capacidad values
('c','Progr.C'),
('pas','Progr.Pascal'),
(
'j','Progr.Java'),
(
'sql','Bases datos SQL');

insert
into persona values
(
'ju','Juan','c'),
(
'ja','Javier','pas'),
(
'jo','Jose','perl'),
(
'je','Jesus','html');

Como se puede observar, hay dos capacidades en nuestra base de datos para las que no conocemos a ninguna persona; de igual modo, existen dos personas que tienen capacidades sobre las que no tenemos ningún detalle.

Por eso, si mostramos las personas con sus capacidades de la forma que sabemos, sólo aparecerán las parejas de persona y capacidad para las que todo está claro (existe persona y existe capacidad), es decir:

select * from capacidad, persona where persona.codcapac = capacidad.codigo;

(Hay que recordar que la orden "where" es obligatoria: si no indicamos esa condición, se mostraría el "producto cartesiano" de las dos tablas: todos los parejas (persona, capacidad), aunque no estén relacionados en nuestra base de datos).

Pues bien, con órdenes "join" podemos afinar cómo queremos enlazar (en inglés, "join", unir) las tablas. Por ejemplo, si queremos ver los datos que coinciden en ambas tablas, lo que antes conseguíamos comparando los códigos con un "where", también podemos usar un "inner join" (unión interior; se puede abreviar simplemente "join"):

select persona.nombre, capacidad.nombre
from persona inner join capacidad
on persona.codcapac = capacidad.codigo;

Pero aquí llega la novedad: si queremos ver todas las personas y sus capacidades, incluso para aquellas personas cuya capacidad no está detallada en la otra tabla, usaríamos un "left join" (unión por la izquierda, también se puede escribir "left outer join", unión exterior por la izquierda, para dejar claro que se van a incluir datos que están sólo en una de las dos tablas):

select persona.nombre, capacidad.nombre
from persona left outer join capacidad
on persona.codcapac = capacidad.codigo;

De igual modo, si queremos ver todas las capacidades, incluso aquellas para las que no hay detalles sobre personas, podemos escribir el orden de las tablas al revés en la consulta anterior, o bien usar "right join" (o "right outer join"):

Otros gestores de bases de datos permiten combinar el "right join" y el "left join" en una única consulta, usando "full outer join", algo que no permite MySQL en su versión actual.

17. Uniendo dos consultas en una

En el apartado anterior comentábamos que la versión actual de MySQL no permite usar "full outer join" para mostrar todos los datos que hay en dos tablas enlazadas, aunque alguno de esos datos no tenga equivalencia en la otra tabla.

También decíamos que se podría imitar haciendo a la vez un "right join" y un "left join".

En general, tenemos la posibilidad de unir dos consultas en una usando "union", así:

select persona.nombre, capacidad.nombre
from persona right outer join capacidad
on persona.codcapac = capacidad.codigo
union
select
persona.nombre, capacidad.nombre
from persona left outer join capacidad
on persona.codcapac = capacidad.codigo;
Nota: en algunos gestores de bases de datos, podemos no sólo crear "uniones" entre dos tablas, sino también realizar otras operaciones habituales entre conjuntos, como calcular su intersección ("intersection") o ver qué elementos hay en la primera pero no en la segunda (diferencia, "difference"). Estas posibilidades no están disponibles en la versión actual de MySQL.

MySQL Básico (1)

(Nociones básicas de SQL usando MySQL, bajo Windows.
Un repaso a lo fundamental en 2 lecciones.
Si quieres sabes más, echa un vistazo en nachocabanes.com/sql/curso)

1. Entrar a MySQL

Daremos por sentado que MySQL ya está instalado. Si hemos hecho la instalación típica, MySQL debería haber quedado dentro de "Archivos de programa".

Para llegar hasta allí, entramos al intérprete de comandos de Windows (por ejemplo, desde el menú de Inicio, en la opción "Ejecutar", tecleando la orden "cmd"), entonces usamos la orden "cd" para llegar hasta la carpeta "bin", en la que están los "binarios" (programas ejecutables) de MySQL. Debería ser algo como:

cd "Archivos de programa\MySQL\MySQL Server 5.0\bin"

Para entrar al entorno de MySQL, como no hemos permitido el acceso anónimo, tendremos que indicar un nombre de usuario con la opción "-u". Como por ahora sólo existe el usuario "root", escribiríamos:

mysql -u root

Y entonces deberíamos ver algo parecido a:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

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

2. Crear una base de datos

Para crear una base de datos "create database", seguido del nombre que tendrá la base de datos:

create database ejemplo1;

Podemos tener varias bases de datos en MySQL, así que deberemos indicar cual de ellas queremos usar, con la orden "use":

use ejemplo1;

3. Crear un tabla e introducir datos

Una base de datos, en general, estará formada por varios bloques de información llamados "tablas". En nuestro caso, nuestra tabla almacenará los datos de nuestros amigos. Después decidimos qué datos concretos ("campos") guardaremos de cada amigo, el tipo de datos y su tamaño, por ejemplo:

nombre - texto, hasta 20 letras
dirección - texto, hasta 40 letras
edad - números, de hasta 3 cifras

En MySQL podemos usar "VARCHAR" para referirnos a texto hasta una cierta longitud, y "NUMERIC" para números de una determinada cantidad de cifras:

create table personas (
nombre varchar(20),
direccion varchar(40),
edad decimal(3)
);

Para introducir datos usaremos la orden "insert", e indicaremos tras la palabra "values" los valores para los campos de texto entre comillas, y los valores para campos numéricos sin comillas, así:

insert into personas values ('juan', 'su casa', 25);

Si no queremos introducir todos los datos, o queremos hacerlo en otro orden, podemos detallar los nombres de cada uno de los campos que vamos a introducir, así:

insert into personas
(nombre, direccion)
values (
'pedro', 'su calle'
);

4. Mostrar datos

Para ver los datos almacenados en una tabla usaremos el formato "SELECT campos FROM tabla". Si queremos ver todos los campos, lo indicaremos usando un asterisco:

select * from personas;


Si queremos ver sólo ciertos campos, detallamos sus nombres, separados por comas:

select nombre, direccion from personas;


Normalmente no querremos ver todos los datos que hemos introducido, sino sólo aquellos que cumplan cierta condición. Esta condición se indica añadiendo un apartado WHERE a la orden "select", así:

select nombre, direccion from personas where nombre = 'juan';


A veces no querremos comparar con un texto exacto, sino sólo con parte del contenido del campo (por ejemplo, porque sólo sepamos un apellido o parte de la calle). En ese caso usaríamos la palabra "like", y el comodín "%":

select nombre, direccion from personas where direccion like '%calle%';

que nos diría el nombre y la dirección de nuestros amigos llamados que viven en calles que contengan la palabra "calle", precedida por cualquier texto (%) y con cualquier texto (%) a continuación.


5. Crear dos tablas enlazadas. Las claves primarias

Generalmente será necesario tener algún dato que nos permita distinguir de forma clara los datos que tenemos almacenados. Por ejemplo, el nombre de una persona no es único: pueden aparecer en nuestra base de datos varios usuarios llamados "Juan López". Si son nuestros clientes, debemos saber cual es cual, para no cobrar a uno de ellos un dinero que corresponde a otro. Eso se suele solucionar guardando algún dato adicional que sí sea único para cada cliente, como puede ser el Documento Nacional de Identidad, o el Pasaporte. Si no hay ningún dato claro que nos sirva, en ocasiones añadiremos un "código de cliente", inventado por nosotros, o algo similar.

Estos datos que distinguen claramente unas "fichas" de otras los llamaremos "claves primarias".


Para jugar con ellas, vamos a crear una nueva base de datos:

create database ejemplo2;
use ejemplo2;

En ella guardaremos datos de personas y de ciudades. Para cada ciudad, guardaremos su nombre y su código. Este código será el que actúe como "clave primaria", para distinguir otra ciudad.

create table ciudades (
codigo varchar(3),
nombre varchar(30),
primary key (codigo)
);

Mientras que la tabla de personas sería casi igual al ejemplo anterior (si no queremos usar clave primaria para ellas), pero añadiendo un nuevo dato: el código de la ciudad en la que vive

create table personas (
nombre varchar(20),
direccion varchar(40),
edad decimal(3),
codciudad varchar(3)
);

Los datos se introducen igual que si no hubiera claves primarias:

insert into ciudades values ('a', 'alicante');
insert into ciudades values ('b', 'barcelona');
insert into ciudades values ('m', 'madrid');

insert into personas values ('juan', 'su casa', 25, 'a');
insert into personas values ('pedro', 'su calle', 23, 'm');
insert into personas values ('alberto', 'calle uno', 22, 'b');

Para mostrar datos tomados de las dos tablas, deberemos incluir ambas en el "from", añadir la condición que enlace ambas tablas (que el código de ciudad que aparece en "personas" sea el mismo que el que aparece en "ciudad"), y quizá también añadir el nombre de alguna tabla delante el nombre del campo, si es que tenemos algún campo repetido (como el "nombre", que existe en ambas tablas):

select personas.nombre, direccion, ciudades.nombre
from personas, ciudades
where personas.codciudad = ciudades.codigo;

Cualquier otra consulta que implique las dos tablas deberá terminar comprobando que los dos códigos coinciden. Por ejemplo, para ver qué personas viven en la ciudad llamada "madrid", haríamos:

select personas.nombre, direccion, edad
from personas, ciudades
where ciudades.nombre='madrid'
and
personas.codciudad = ciudades.codigo;

Y para saber las personas de ciudades que comiencen con la letra "b", haríamos:

select personas.nombre, direccion, ciudades.nombre
from
personas, ciudades
where ciudades.nombre like 'b%'
and
personas.codciudad = ciudades.codigo;


6. Saber qué información hay

Podemos saber las bases de datos que hay creadas en nuestro sistema con:

show databases;

Una vez que estamos trabajando con una base de datos concreta (con la orden "use"), podemos saber las tablas que contiene con:

show tables;

Y para una tabla concreta, podemos saber los campos (columnas) que la forman con "show columns from":

show columns from personas;


7. Borrar datos

Podemos borrar toda una base de datos:

drop database ejemplo2;

O borrar una tabla:

drop table personas;

O bien borrar los datos que cumplen una cierta condición, con "delete from", a la que le indicamos las condiciones que se deben cumplir:

delete from personas where nombre = 'juan';

Esto borraría todas las personas llamadas "juan" que estén almacenadas en la tabla "personas".

Cuidado: si no se indica la parte de "where", no se borrarían los datos que cumplen una condición, sino TODOS los datos. Si es eso lo que se pretende, una forma más rápida de conseguirlo es usar:

truncate table personas;

8. Modificar de datos

Por supuesto, podemos modificar datos. Usaremos "update tabla set campo=nuevoValor where condicion". Por ejemplo, si hemos escrito "Alberto" en minúsculas ("alberto"), lo podríamos corregir con:

update personas
set
nombre = 'Alberto'
where
nombre = 'alberto';

Y si queremos corregir todas las edades para sumarles un año se haría con

update personas
set edad = edad+1;

(al igual que habíamos visto para "select" y para "delete", si no indicamos la parte del "where", los cambios se aplicarán a todos los registros de la tabla).


También podemos modificar la estructura de una tabla: añadir campos, eliminarlos, cambiar su nombre o el tipo de datos. En general, para todo ello se usará la orden "alter table".

Por ejemplo, para añadir un campo usaríamos "add":

alter table ciudades
add habitantes decimal(7);

Y si queremos borrar algún campo, usaremos "drop column":

alter table ciudades
drop
column numhabitantes;


9. Salir de MySQL

Para terminar nuestra sesión de MySQL, tecleamos la orden quit o exit y volvemos al sistema operativo.