SQLite Vistas, índice y disparador con ejemplo

En el uso diario de SQLite, necesitará algunas herramientas administrativas sobre su base de datos. También puede usarlos para hacer que las consultas a la base de datos sean más eficientes mediante la creación de índices, o más reutilizables mediante la creación de vistas.

SQLite Ver

Las vistas son muy similares a las tablas. Pero las Vistas son tablas lógicas; no se almacenan físicamente como tablas. Una vista se compone de una declaración selecta.

Puede definir una vista para sus consultas complejas y puede reutilizar estas consultas cuando lo desee llamando a la vista directamente en lugar de reescribir las consultas nuevamente.

CREAR VISTA declaración

Para crear una vista en una base de datos, puede usar la instrucción CREATE VIEW seguida del nombre de la vista y luego realizar la consulta que desee después de eso.

Ejemplo: En el siguiente ejemplo crearemos una vista con el nombre “AllStudentsView” en la base de datos de ejemplo “TutorialsSampleDB.db” de la siguiente manera:

Paso 1) Abra Mi PC y navegue hasta el siguiente directorio “C:\sqlite” y luego abra “sqlite3.exe”:

Paso 2) Abra la base de datos “TutorialsSampleDB.db” con el siguiente comando:

Paso 3) A continuación se muestra una sintaxis básica del comando sqlite3 para crear la vista

CREATE VIEW AllStudentsView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

No debería haber ningún resultado del comando como este:

Paso 4) Para garantizar que se cree la vista, puede seleccionar la lista de vistas en la base de datos ejecutando el siguiente comando:

SELECT name FROM sqlite_master WHERE type = 'view';

Debería ver que se devuelve la vista “AllStudentsView”:

Paso 5) Ahora que nuestra vista está creada, puedes usarla como una tabla normal, algo como esto:

SELECT * FROM AllStudentsView;

Este comando consultará la vista “AllStudents” y seleccionará todas las filas como se muestra en la siguiente captura de pantalla:

Vistas temporales

Las vistas temporales son temporales para la conexión de base de datos actual que se utilizó para crearlas. Luego, si cierra la conexión de base de datos, todas las vistas temporales se eliminarán automáticamente. Las vistas temporales se crean utilizando uno de los siguientes comandos:

• CREAR VISTA TEMPORAL, o
• CREAR VISTA TEMPORAL.

Las vistas temporales son útiles si desea realizar algunas operaciones por el momento y no necesita que sean una vista permanente. Por lo tanto, solo debe crear una vista temporal y luego realizar el procesamiento utilizando esa vista. Later al cerrar la conexión con la base de datos, se eliminará automáticamente.

Ejemplo:

En el siguiente ejemplo, abriremos una conexión de base de datos y luego crearemos una vista temporal.

Después de eso, cerraremos esa conexión y comprobaremos si la vista temporal todavía existe o no.

Paso 1) Abra sqlite3.exe desde el directorio “C:\sqlite” como se explicó anteriormente.

Paso 2) Abra una conexión a la base de datos “TutorialsSampleDB.db” ejecutando el siguiente comando:

.open TutorialsSampleDB.db

Paso 3) Escriba el siguiente comando que creará una vista temporal llamada “AllStudentsTempView”:

CREATE TEMP VIEW AllStudentsTempView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Paso 4) Asegúrese de que la vista temporal “AllStudentsTempView” se haya creado ejecutando el siguiente comando:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Paso 5) Cierre sqlite3.exe y ábralo nuevamente.

Paso 6) Abra una conexión a la base de datos “TutorialsSampleDB.db” mediante el siguiente comando:

.open TutorialsSampleDB.db

Paso 7) Ejecute el siguiente comando para obtener la lista de vistas temporales creadas en la base de datos:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

No debería ver ningún resultado, ya que la vista temporal que creamos se eliminó cuando cerramos la conexión a la base de datos en el paso anterior. De lo contrario, siempre que mantenga abierta la conexión con la base de datos, podrá ver la vista temporal con datos.

Notas:

• No puede usar las declaraciones INSERTAR, ELIMINAR o ACTUALIZAR con vistas, solo puede usar el comando "seleccionar de vistas" como se muestra en el paso 5 en el ejemplo CREAR vista.
• Para eliminar una VISTA, puede utilizar la instrucción "DROP VIEW":

DROP VIEW AllStudentsView;

Para garantizar que se elimine la vista, puede ejecutar el siguiente comando que le proporcionará la lista de vistas en la base de datos:

SELECT name FROM sqlite_master WHERE type = 'view';

No encontrará ninguna vista devuelta ya que la vista fue eliminada, como se muestra a continuación:

Además de reutilizar consultas complejas con vistas, SQLite Además, recupera las filas coincidentes más rápidamente mediante índices.

SQLite Home

Si tiene un libro y desea buscar una palabra clave en ese libro. Buscará esa palabra clave en el índice del libro. Luego, navegará hasta el número de página de esa palabra clave para leer más información sobre esa palabra clave.

Sin embargo, si no hay un índice en ese libro ni números de página, deberás escanear todo el libro desde el principio hasta el final hasta encontrar la palabra clave que estás buscando. Y esto es muy difícil, especialmente cuando tienes un índice y un proceso muy lento para buscar una palabra clave.

Índices en SQLite (y el mismo concepto válido para otros Sistemas de gestión de bases de datos también) funciona de la misma manera que los índices que se encuentran al final de los libros.

Cuando buscas algunas filas en un SQLite tabla con criterios de búsqueda, SQLite buscará en todas las filas de la tabla hasta encontrar las filas que busca y que coincidan con los criterios de búsqueda. Y ese proceso se vuelve muy lento cuando tienes mesas más grandes.

Los índices acelerarán las consultas de búsqueda de datos y ayudarán a realizar la recuperación de datos de las tablas. Los índices se definen en las columnas de la tabla.

Mejorando el rendimiento con índices:

Los índices pueden mejorar el rendimiento de la búsqueda de datos en una tabla. Cuando crea un índice en una columna, SQLite creará una estructura de datos para ese índice donde cada valor de campo tiene un puntero a toda la fila a la que pertenece el valor.

Luego, si ejecuta una consulta con una condición de búsqueda en una columna que forma parte de un índice, SQLite Primero buscará el valor en el índice. SQLite no escaneará toda la tabla en busca de ello. Luego leerá la ubicación donde apunta el valor para la fila de la tabla. SQLite localizará la fila en esa ubicación y la recuperará.

Sin embargo, si la columna que estás buscando no es parte de un índice, SQLite realizará una exploración de los valores de las columnas para encontrar los datos que está buscando. Generalmente será un proceso más lento si no hay un índice.

Imagine un libro sin índice y necesita buscar una palabra específica. Escanearás todo el libro desde la primera página hasta la última página buscando esa palabra. Sin embargo, si tiene un índice de ese libro, primero buscará la palabra que contiene. Obtenga el número de página donde se encuentra y luego navegue hasta ella. Lo cual será mucho más rápido que escanear todo el libro de principio a fin.

SQLite CREAR ÍNDICE

Para crear un índice en una columna, debe usar el comando CREAR ÍNDICE. Y deberías definirlo de la siguiente manera:

• Debe especificar el nombre del índice después del comando CREATE INDEX.
• Después del nombre del índice, hay que poner la palabra clave “ON”, seguida del nombre de la tabla en la que se creará el índice.
• Luego, la lista de nombres de columnas que se utilizan para el índice.
• Puede utilizar una de las siguientes palabras clave “ASC” o “DESC” después de cualquier nombre de columna para especificar un orden de clasificación utilizado para ordenar los datos del índice.

Ejemplo:

En el siguiente ejemplo, crearemos un índice llamado “StudentNameIndex” en la tabla “Students” de la base de datos “Students” de la siguiente manera:

Paso 1) Navegue hasta la carpeta “C:\sqlite” como se explicó anteriormente.

Paso 2) Abra sqlite3.exe.

Paso 3) Abra la base de datos “TutorialsSampleDB.db” con el siguiente comando:

.open TutorialsSampleDB.db

Paso 4) Cree un nuevo índice llamado “StudentNameIndex” utilizando el siguiente comando:

CREATE INDEX StudentNameIndex ON Students(StudentName);

No deberías ver ningún resultado para esto:

Paso 5) Para asegurarse de que se creó el índice, puede ejecutar la siguiente consulta, que le proporcionará la lista de índices creados en la tabla Estudiantes:

PRAGMA index_list(Students);

Deberías ver el índice que acabamos de crear:

Notas:

• Los índices se pueden crear no solo en función de columnas sino también de expresiones. Algo como esto:

CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

El "OrderTotalIndex" se basará en la columna OrderId y también en la multiplicación del valor de la columna Cantidad y el valor de la columna Precio. Por lo tanto, cualquier consulta de "OrderId" y "Cantidad*Precio" será eficiente ya que la consulta utilizará el índice.

• Si especificó una cláusula WHERE en la declaración CREATE INDEX, el índice será un índice parcial. En este caso, habrá entradas en el índice solo para las filas que coincidan con las condiciones de la cláusula WHERE. Por ejemplo, en el siguiente índice:

  CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
  WHERE Quantity > 10000;

  (En el ejemplo anterior, el índice será un índice parcial ya que hay una cláusula WHERE especificada. En este caso, el índice se aplicará solo a aquellos pedidos que tengan un valor de cantidad mayor que 10000. Tenga en cuenta que este índice se llama índice parcial índice debido a la cláusula WHERE, no a la expresión utilizada en ella. Sin embargo, puede usar las expresiones con índices normales.)

• Puede utilizar la instrucción CREATE UNIQUE INDEX en lugar de CREATE INDEX para evitar entradas duplicadas para las columnas y, por lo tanto, todos los valores de la columna indexada serán únicos.
• Para eliminar un índice, utilice el comando DROP INDEX seguido del nombre del índice a eliminar.

Si bien los índices hacen que las lecturas sean más rápidas, los disparadores permiten SQLite Responderá automáticamente cada vez que cambien los datos.

SQLite Desencadenar

Introducción a los SQLite Desencadenar

Los activadores son operaciones automáticas predefinidas que se ejecutan cuando se produce una acción específica en una tabla de la base de datos. Se puede definir un activador para que se active cuando se produce una de las siguientes acciones en una tabla:

• INSERTAR en una mesa.
• BORRAR filas de una tabla.
• ACTUALIZAR una de las columnas de la tabla.

SQLite admite el disparador FOR EACH ROW, de modo que las operaciones predefinidas en el disparador se ejecutarán para todas las filas involucradas en las acciones ocurridas en la tabla (ya sea insertar, eliminar o actualizar).

SQLite CREAR GATILLO

Para crear un nuevo TRIGGER, puede utilizar la instrucción CREATE TRIGGER de la siguiente manera:

• Después de CREATE TRIGGER, debe especificar un nombre de activador.
• Después del nombre del activador, debe especificar cuándo se debe ejecutar exactamente el nombre del activador. Tienes tres opciones:
  • ANTES: el disparador se ejecutará antes de la instrucción INSERTAR, ACTUALIZAR o eliminar especificada.
  • Después: el disparador se ejecutará después de la instrucción INSERTAR, ACTUALIZAR o eliminar especificada.
  • EN LUGAR DE: Reemplazará la acción que ocurrió y que disparó el disparador con la declaración especificada en el DISPARADOR. El disparador INSTEAD OF no se aplica con tablas, solo con vistas.
• Luego, debes especificar el tipo de acción, el disparador se activará cuando suceda. ELIMINAR, INSERTAR o ACTUALIZAR.
• Puede elegir un nombre de columna opcional para que el activador no se active a menos que la acción ocurra en esa columna.
• Luego debe especificar el nombre de la tabla en la que se creará el activador.
• Dentro del cuerpo del disparador, debe especificar la declaración que debe ejecutarse para cada fila cuando se activa el disparador.

Los disparadores se activarán (dispararán) solo según el tipo de declaración especificada en el comando de creación de disparador. Por ejemplo:

• El disparador BEFORE INSERT se activará (disparará) antes de cualquier declaración de inserción.
• El disparador DESPUÉS DE LA ACTUALIZACIÓN se activará (disparará) después de cualquier declaración de actualización,... y así sucesivamente.

Dentro del disparador, puedes hacer referencia a los valores recién insertados usando la palabra clave “new”. También puedes hacer referencia a los valores eliminados o actualizados usando la palabra clave old. Como se muestra a continuación:

• Activadores INSERT internos: se puede utilizar una nueva palabra clave.
• Activadores internos de ACTUALIZAR: se pueden utilizar palabras clave nuevas y antiguas.
• Dentro de los activadores DELETE: se puede utilizar la palabra clave antigua.

Ejemplo

A continuación, crearemos un disparador que se activará antes de insertar un nuevo estudiante en la tabla "Estudiantes".

Registrará al estudiante recién insertado en la tabla “StudentsLog” con una marca de tiempo automática para la fecha y hora actuales en que se realizó la instrucción de inserción. Como se muestra a continuación:

Paso 1) Navegue hasta el directorio “C:\sqlite” y ejecute sqlite3.exe.

Paso 2) Abra la base de datos “TutorialsSampleDB.db” ejecutando el siguiente comando:

.open TutorialsSampleDB.db

Paso 3) Cree el activador “InsertIntoStudentTrigger” ejecutando el siguiente comando:

CREATE TRIGGER InsertIntoStudentTrigger 
       BEFORE INSERT ON Students
BEGIN
  INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;

La función “datetime()” proporciona la fecha y hora actuales en que se realizó la inserción. De esta manera, podemos registrar la transacción de inserción con marcas de tiempo añadidas automáticamente a cada transacción.

El comando debería ejecutarse correctamente y no obtendrá ningún resultado:

El disparador “InsertIntoStudentTrigger” se activará cada vez que se inserte un nuevo estudiante en la tabla de estudiantes. La palabra clave “new” se refiere a los valores que se insertarán. Por ejemplo, “new.StudentId” será el ID del estudiante que se insertará.

Ahora probaremos cómo se comporta el disparador cuando insertamos un nuevo estudiante.

Paso 4) Escriba el siguiente comando que insertará un nuevo estudiante en la tabla de estudiantes:

INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');

Paso 5) Escribe el siguiente comando que seleccionará todas las filas de la tabla “StudentsLog”:

SELECT * FROM StudentsLog;

Debería ver una nueva fila para el nuevo estudiante que acabamos de insertar:

Esta fila fue insertada por el disparador antes de insertar al nuevo estudiante con ID 11.

En este ejemplo, utilizamos el disparador “InsertIntoStudentTrigger” que creamos para registrar automáticamente cualquier transacción de inserción en la tabla “StudentsLog”. De la misma manera, puede registrar cualquier instrucción de actualización o eliminación.

Prevención de actualizaciones no deseadas con activadores:

Al utilizar los activadores ANTES DE ACTUALIZAR en una tabla, puede evitar las declaraciones de actualización en una columna según una expresión.

Ejemplo

En el siguiente ejemplo, evitaremos que cualquier declaración de actualización actualice la columna “nombredelestudiante” en la tabla Estudiantes:

Paso 1) Navegue hasta el directorio “C:\sqlite” y ejecute sqlite3.exe.

Paso 2) Abra la base de datos “TutorialsSampleDB.db” ejecutando el siguiente comando:

.open TutorialsSampleDB.db

Paso 3) Cree un nuevo disparador “preventUpdateStudentName” en la tabla “Students” ejecutando el siguiente comando.

CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'You cannot update studentname');
END;

El comando “RAISE” generará un error con el mensaje “No se puede actualizar studentname” y, a continuación, impedirá que se ejecute la instrucción de actualización.

Ahora, verificaremos que el activador funcione bien y evite cualquier actualización de la columna de nombre de estudiante.

Paso 4) Ejecute el siguiente comando de actualización, que actualizará el nombre del estudiante “Jack” a “Jack1”.

UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';

Debería aparecer el mensaje de error que especificamos en el activador, que dice "No se puede actualizar studentname", como se muestra a continuación:

Paso 5) Ejecute el siguiente comando, que seleccionará la lista de nombres de estudiantes de la tabla de estudiantes.

SELECT StudentName FROM Students;

Deberías ver que el nombre del estudiante "Jack" sigue siendo el mismo y no cambia:

Preguntas Frecuentes