Manual de SQL

Manual de SQLite

El curso Desarrollo de apps para Android en Kotlin supone que estás familiarizado con los siguientes temas:

  • Bases de datos en general
  • Bases de datos SQL en particular
  • El lenguaje SQL que se usa para interactuar con las bases de datos

Esta página es una referencia rápida a modo de repaso.

Bases de datos SQL

Las bases de datos SQL almacenan datos en tablas de filas y columnas:

  • La intersección de una fila y una columna se denomina campo.
  • Los campos contienen datos, referencias a otros campos o referencias a otras tablas.
  • Cada fila contiene una entidad. La entidad se identifica con un ID único que se suele usar como su clave primaria.
  • Cada columna se identifica con un nombre que es único por tabla.

SQLite

SQLite implementa un motor de base de datos SQL que tiene las siguientes características:

  • Es autónomo (no requiere otros componentes).
  • Usa tecnología sin servidores (no requiere backend de servidor).
  • No requiere configuración (no es necesario que esté configurada para tu app).
  • Admite transacciones (los cambios en una sola transacción en SQLite pueden producirse o no).

SQLite es el motor de base de datos más implementado del mundo. El código fuente de SQLite es de dominio público. Para obtener detalles sobre la base de datos SQLite, consulta el sitio web de SQLite.

Tabla de ejemplos

  • Una base de datos llamada DATABASE_NAME
  • Una tabla llamada WORD_LIST_TABLE
  • Columnas para _id, word y definition

Después de insertar las palabras alpha y beta, donde alpha tiene dos definiciones, la tabla podría verse así:

DATABASE_NAME

WORD_LIST_TABLE

_id

palabra

definición

1

"alpha"

"first letter"

2

"beta"

"second letter"

3

"alpha"

"particle"

Para encontrar el contenido de una fila específica, usa _id o recupera filas formulando consultas que seleccionen filas de la tabla mediante la especificación de restricciones.

Transacciones

Una transacción es una secuencia de operaciones realizadas como una unidad de trabajo lógica única. Para calificar como una transacción, una unidad de trabajo lógica debe exhibir cuatro propiedades: atomicidad, coherencia, aislamiento y durabilidad (ACID):

  • Atomicidad. Se realizan todas las modificaciones de datos de una transacción o no se realiza ninguna. La atomicidad se aplica incluso si falla un programa o sistema operativo, o si un corte de energía interrumpe el acto de escribir el cambio en el disco.
  • Coherencia. Cuando se completa una transacción, esta debe dejar todos los datos en un estado coherente.
  • Aislamiento. Las modificaciones que realizan las transacciones simultáneas deben estar aisladas unas de otras. Una transacción reconoce los datos en el estado en que estaban antes de que otra transacción simultánea los modificara, o bien la transacción reconoce los datos después de que haya finalizado la segunda transacción. La transacción no reconoce un estado intermedio.
  • Durabilidad. Una vez que se completa una transacción, sus efectos se mantienen permanentemente en el sistema. Las modificaciones persisten incluso en caso de que falle el sistema.

Ejemplos de transacciones:

  • Transferir dinero de una cuenta de ahorro a una corriente
  • Ingresar un término y una definición en el diccionario
  • Confirmar una lista de cambios en la rama principal

Para obtener más información sobre las transacciones, consulta Atomic Commit In SQLite (disponible en inglés).

Lenguaje de consulta

Usa el lenguaje de consulta de SQL para interactuar con la base de datos. Las consultas pueden ser muy complejas, pero hay cuatro operaciones básicas:

  • Insertar filas
  • Borrar filas
  • Actualizar valores en filas
  • Recuperar filas que cumplan con ciertos criterios

En Android, el objeto de acceso a datos (DAO) proporciona métodos útiles para insertar, borrar y actualizar bases de datos. Puedes obtener una descripción completa del lenguaje de consulta en SQL Assuse By SQLite (disponible en inglés).

Estructura de una consulta

Una consulta de SQL está altamente estructurada. Consulta de muestra:

  • SELECT word, definition FROM WORD_LIST_TABLE WHERE word="alpha"

Versión genérica de la consulta de muestra:

  • SELECT columns FROM table WHERE column="value"

Partes de la consulta de muestra:

  • SELECT columns: Elige las columnas que quieras mostrar. Usa * para mostrarlas todas.
  • FROM table: Especifica la tabla de la que deseas obtener resultados.
  • WHERE: Es una palabra clave opcional que precede a las condiciones que deben cumplirse (por ejemplo, column="value"). Los operadores comunes son =, LIKE, < y >. Para conectar varias condiciones, usa AND o OR.

Otras partes de las consultas:

  • ORDER BY: Es un frase de clave opcional para ordenar los resultados de una columna. Especifica ASC para el orden ascendente y DESC para el descendente. Si no especificas un orden, se usará el predeterminado, que podría no estar ordenado.
  • LIMIT: Es una palabra clave para especificar una cantidad limitada de resultados.

Ejemplos de consultas y resultados

Las siguientes consultas usan la tabla definida anteriormente:

SELECT * FROM WORD_LIST_TABLE

Obtiene todas las filas de la tabla WORD_LIST_TABLE.
SELECT word, definition FROM WORD_LIST_TABLE WHERE _id > 2 Selecciona las columnas word y definition de todos los elementos con un id superior a 2.
Devuelve

[["alpha", "particle"]]

SELECT _id FROM WORD_LIST_TABLE WHERE word="alpha" AND definition LIKE "%art%"

Devuelve el id de la palabra alpha con la subcadena art en la definición.

[["3"]]

SELECT definition FROM WORD_LIST_TABLE ORDER BY word DESC LIMIT 1

Selecciona todas las definiciones. Ordena de forma inversa y obtiene la primera fila después de que se ordena la lista. Se ordena por la columna especificada, que es word. Ten en cuenta que podemos ordenar los datos según una columna que no mostramos.

[["second letter"]]

SELECT * FROM WORD_LIST_TABLE LIMIT 2,1

Devuelve 1 elemento que comienza en la posición 2. El conteo de posiciones comienza en 1 (no en cero). Devuelve [["2", "beta", "second letter"]]

Puedes practicar cómo crear y consultar bases de datos en este sitio web de SQL Fiddle.

Consultas de SQLite para Android

Puedes enviar consultas a la base de datos SQLite del sistema Android como consultas sin procesar o como parámetros.

El método rawQuery(String sql, String[] selectionArgs) ejecuta el SQL proporcionado. El método devuelve un Cursor del conjunto de resultados. En la siguiente tabla, se muestra cómo se verían las dos primeras consultas de ejemplo como consultas sin procesar:

1

String query = "SELECT * FROM WORD_LIST_TABLE";
rawQuery(query, null);

2

query = "SELECT word, definition FROM WORD_LIST_TABLE WHERE _id> ? ";
String[] selectionArgs = new String[]{"2"}
rawQuery(query, selectionArgs) ;

El método query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) consulta la tabla correspondiente. El método devuelve un Cursor sobre el conjunto de resultados. A continuación, se incluye una consulta que muestra cómo completar los argumentos:

SELECT * FROM WORD_LIST_TABLE
WHERE word="alpha"
ORDER BY word ASC LIMIT 2,1;

La consulta devuelve lo siguiente:

[["alpha", "particle"]]

Ejemplos de argumentos que puedes usar:

String table = "WORD_LIST_TABLE"
String[] columns = new String[]{"*"};
String selection = "word = ?"
String[] selectionArgs = new String[]{"alpha"};
String groupBy = null;
String having = null;
String orderBy = "word ASC"
String limit = "2,1"

query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);

Nota: En el código real, no podrías crear variables para los valores de null. Consulta la documentación de SQLiteDatabase para Android si deseas obtener versiones de este método con parámetros diferentes.

Cursores

Un cursor es un puntero en una fila de datos estructurados. Se usa como un puntero para una fila de la tabla.

Una consulta devuelve un objeto Cursor que apunta al primer elemento del resultado de la consulta. La clase Cursor proporciona métodos para mover el cursor por el resultado de la consulta y métodos para obtener los datos de las columnas de cada fila en el resultado.

Cuando un método devuelve un objeto Cursor, debes iterar en el resultado, extraer los datos, realizar alguna acción con los datos y cerrar el cursor para liberar la memoria.

Más información