SQLite Consulta INSERT, UPDATE, DELETE com exemplo

As cláusulas de modificação de dados em SQLite são instruções INSERT, UPDATE e DELETE. É usado para inserir novas linhas, atualizar valores existentes ou excluir linhas do banco de dados.

Observe que, para todos os exemplos a seguir, você deve executar sqlite3.exe e abrir uma conexão com o banco de dados de amostra conforme flui:

Passo 1) Nesta etapa,

1. Abra Meu Computador e navegue até o seguinte diretório “C:\sqlite"E
2. Em seguida, abra “sqlite3.exe":

Passo 2) Abra o banco de dados “TutoriaisSampleDB.db”pelo seguinte comando:

.open TutorialsSampleDB.db

Agora você está pronto para executar qualquer tipo de consulta no banco de dados.

SQLite INSIRA

SQLite INSERT é usado para inserir registros em uma tabela especificada do banco de dados. você deve usar a cláusula 'INSERT'. A sintaxe da cláusula INSERT é a seguinte:

• Após a cláusula INSERT, você deve indicar em qual tabela deseja inserir os valores.
• Após o nome da tabela você escreve a lista de colunas nas quais deseja inserir os valores.
• Você pode ignorar o nome das colunas e não escrever nelas.
• Se você não escrever o nome das colunas, os valores serão inseridos em todas as colunas encontradas na tabela com a mesma ordem, as colunas são definidas na tabela.
• Após a cláusula VALUES, você deve listar os valores a serem inseridos.
• Cada cláusula INSERT insere apenas uma linha. Se quiser inserir múltiplas linhas, você deve escrever múltiplas cláusulas INSERT, uma para cada linha.

SQLite Inserir exemplo

No exemplo a seguir, inseriremos 2 linhas na tabela de alunos, uma para cada aluno:

INSERT INTO Students(StudentId, StudentName, DepartmentId, DateOfBirth)
              VALUES(11, 'Ahmad', 4, '1997-10-12');

INSERT INTO Students VALUES(12, 'Aly', 4, '1996-10-12');

Isso deve ser executado com êxito e não há saída para isso:

Isso irá inserir dois alunos:

• O primeiro aluno com StudentId = 11, StudentName = Ahmad, DepartmentId = 4 e DateOfBirth = 1997-10-12.
• O segundo aluno com StudentId=12, StudentName = Aly, DepartmentId = 4 e DateOfBirth = 1996-10-12′.

Na primeira instrução, listamos os nomes das colunas “StudentId, StudentName, DepartmentId, DateOfBirth“.No entanto, na segunda declaração, não o fizemos.

Os quatro valores “12, 'Aly', 4, '1996/10/12'” será inserido em todas as quatro colunas da tabela Alunos na mesma ordem em que as colunas são definidas.

Agora, vamos verificar se os dois alunos foram inseridos na tabela Alunos executando o seguinte pergunta:

SELECT * FROM Students;

Então você deverá ver os dois alunos retornados dessa consulta da seguinte forma:

SQLite Atualizar

SQLite A consulta UPDATE é usada para modificar os registros existentes em uma tabela. Você pode usar a cláusula WHERE com a consulta UPDATE para atualizar linhas selecionadas. A cláusula UPDATE atualiza uma tabela alterando um valor para uma coluna específica. A seguir está a sintaxe da cláusula UPDATE:

Como segue:

• Após a “cláusula de atualização”, você deve escrever o nome da tabela a ser atualizada.
• Você deve escrever “cláusula SET” que é usada para escrever o nome da coluna a ser atualizada e o valor a ser atualizado.
• Você pode atualizar mais de uma coluna. Você pode usar uma vírgula entre cada linha.
• Você pode especificar uma cláusula WHERE para especificar apenas algumas linhas. Somente as linhas avaliadas pela expressão como verdadeiras são atualizadas. Se você não especificou uma cláusula WHERE, todas as linhas serão atualizadas.

SQLite Exemplo de atualização

Na instrução UPDATE a seguir, atualizaremos o DepartmentId do Student com StudentId = 6 para 3:

UPDATE Students
SET DepartmentId = 3 
WHERE StudentId = 6;

Isso deve ser executado com sucesso e você não deve obter nenhuma saída:

Na cláusula UPDATE, especificamos que queremos atualizar a tabela Students.

• Na cláusula WHERE, filtramos todos os alunos para selecionar apenas a linha para StudentId = 6.
• A cláusula SET atualizará o valor do ID do Departamento dos alunos selecionados para 3.

Agora vamos verificar se o aluno com ID 6 está atualizado, executando o seguinte comando:

SELECT * FROM Students WHERE StudentId = 6;

Agora você deve ver que o valor do ID do departamento agora é 3 da seguinte forma:

SQLite Apagar

SQLite A consulta DELETE é usada para remover registros existentes de uma tabela especificada. Você pode usar a cláusula WHERE com consultas DELETE para excluir as linhas selecionadas.

A cláusula DELETE tem a seguinte sintaxe:

• Você deve escrever um nome de tabela após a cláusula DELETE FROM, da qual deseja excluir os registros. (Observação: que o Cláusula DELETE é usado para excluir alguns registros de uma tabela ou excluir todos os registros e não excluirá a tabela em si. No entanto, o Cláusula DROP é usado para excluir a tabela inteira com todos os registros nela contidos.)
• Se você escrever a cláusula DELETE assim “DELETE FROM guru”, isso excluirá todos os registros da tabela “guru”.
• Você pode especificar uma condição WHERE com uma expressão se desejar excluir algumas linhas específicas. Somente as linhas para as quais a expressão for avaliada como verdadeira serão excluídas. Por exemplo, “DELETE FROM guru WHERE id > 5” – isso excluirá apenas os registros que possuem id maior que 5.

Exemplo

Na instrução a seguir, excluiremos dois alunos com StudentId 11 e 12:

DELETE FROM Students WHERE StudentId = 11 OR StudentId = 12;

A expressão "StudentId = 11 OU StudentId = 12” será verdadeiro apenas para alunos com Ids 11 e 12. Portanto, a cláusula DELETE será aplicada em ambos e irá excluí-los apenas.

Este comando deve ser executado com sucesso e você não deve obter nenhuma saída como a seguir:

Você pode verificar se os dois alunos foram excluídos selecionando todos os registros da tabela Alunos da seguinte forma:

SELECT * FROM Students;

Você não deverá ver os dois alunos com IDs 11 e 12 da seguinte forma:

SQLite Cláusula de Conflito

Suponha que você tenha uma coluna que tenha uma das seguintes restrições de coluna: UNIQUE, NOT NULL, CHECK ou PRIMARY KEY. E então você tentou inserir ou atualizar um valor naquela coluna com um valor que entra em conflito com essa restrição.

Por exemplo, se uma coluna tiver uma restrição UNIQUE e você tentar inserir um valor que já existe (um valor duplicado), que entra em conflito com a restrição UNIQUE. Então a cláusula CONFLICT permite que você escolha o que fazer nesses casos para resolver o conflito.

Antes de continuarmos explicando como a cláusula CONFLICT resolve o conflito. Você deve entender o que é uma transação de banco de dados.

Transação de banco de dados

O termo transação de banco de dados é uma lista de SQLite operações (inserir ou atualizar ou excluir). A transação do banco de dados deve ser executada como uma unidade, com todas as operações executadas com sucesso ou nenhuma. Todas as operações serão canceladas se uma delas falhar na execução.

Exemplo para uma transação de banco de dados

A transação de transferência de dinheiro de uma conta bancária para outra envolverá algumas atividades. Esta operação de transação inclui a retirada de dinheiro da primeira conta e o depósito em outra conta. Esta transação deve ser totalmente concluída ou totalmente cancelada e não falhar no meio do caminho.

Aqui está a lista de cinco resoluções que você pode escolher na cláusula CONFLITO:

1. RECUPERAR – isso reverterá a transação na qual o atual SQLite declaração que contém o conflito (isso cancelará toda a transação). Por exemplo, se você estiver tentando atualizar 10 linhas e a quinta linha tiver um valor que entre em conflito com uma restrição, nenhuma linha será atualizada e as 10 linhas permanecerão iguais. Um erro será lançado.
2. ABORTAR – isso irá abortar (cancelar) o atual SQLite apenas a declaração que contém o conflito e a transação não será cancelada. Por exemplo, se você estiver tentando atualizar 10 linhas e a quinta linha tiver um valor que entre em conflito com uma restrição, apenas o quinto valor não será atualizado, mas as outras 9 linhas serão atualizadas. Um erro será lançado.
3. FALHAR - aborta o atual SQLite declaração que contém o conflito. No entanto, a transação não continuará, mas as alterações anteriores feitas nas linhas anteriores à linha que contém o conflito serão confirmadas. Por exemplo, se você estiver tentando atualizar 10 linhas e a quinta linha tiver um valor que entre em conflito com uma restrição, apenas as 4 linhas serão atualizadas e as outras não. Um erro será lançado.

4. IGNORAR – isso pulará a linha que contém a violação de restrição e continuará processando as outras linhas seguintes do SQLite declaração. Por exemplo, se você estiver tentando atualizar 10 linhas e a quinta linha tiver um valor que entre em conflito com uma restrição, apenas as 4 linhas serão atualizadas e as outras não. Ele não prosseguirá para atualizar outras linhas e parará na linha que possui o valor de conflito. Nenhum erro será lançado.

5. SUBSTITUIR – depende do tipo de restrição que possui a violação:

• Quando há uma violação de restrição para a restrição UNIQUE ou PRIMARY KEY. O REPLACE substituirá a linha que causa a violação pela nova linha inserida ou atualizada.
• Quando houver uma violação da restrição NOT NULL, a cláusula REPLACE substituirá o valor NULL pelo valor padrão dessa coluna. Se a coluna não tiver um valor padrão, então SQLite abortará a declaração (a declaração será cancelada)
• SE ocorrer a violação da restrição CHECK, a cláusula será abortada.

Observação: As 5 resoluções acima são opções de como você deseja resolver o conflito. Pode não ser necessariamente que o que é aplicável para resolver um conflito seja aplicável para resolver outros tipos de conflitos.

Como declarar a cláusula CONFLICT

Você pode declarar a cláusula ON CONFLICT ao definir uma restrição para uma definição de coluna na cláusula CREATE TABLE. Usando a seguinte sintaxe:

Você pode escolher uma das cinco resoluções para resolver o conflito conforme explicado anteriormente.

Exemplo de IGNORAR EM CONFLITO

Passo 1) Crie um novo assunto de tabela da seguinte forma:

CREATE TABLE [Subjects] (  
    [SubjectId] INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE,
    [SubjectName] NVARCHAR NOT NULL
);

Observe que definimos uma restrição PRIMARY KEY na coluna SubjectId. A restrição de chave primária não permitirá que dois valores duplicados sejam inseridos na coluna SubjectId para que todos os valores nessa coluna sejam exclusivos. Além disso, observe que escolhemos uma resolução de conflito para ser “IGNORAR".

O comando deve ser executado com sucesso e você não deve receber nenhum erro:

Passo 2) Agora, vamos inserir alguns valores nos novos assuntos da tabela, mas com um valor que viola a restrição de chave primária:

INSERT INTO Subjects VALUES(1, 'Algebra');
INSERT INTO Subjects VALUES(2, 'Database Course');
INSERT INTO Subjects VALUES(2, 'Data Structures');
INSERT INTO Subjects VALUES(4, 'Algorithms');

Nesta instrução INSERT, tentamos inserir dois cursos com o mesmo ID de assunto de chave primária 2, o que é uma violação da restrição de chave primária.

Os comandos devem funcionar bem e você não deve receber nenhum erro. Como segue:

Passo 3) Selecione todos os assuntos da tabela da seguinte forma:

SELECT * FROM Subjects;

Isso lhe dará a lista de assuntos:

Observe que apenas três assuntos foram inseridos “Álgebra, Curso de Banco de Dados e Algorithms”em vez de 4 linhas.

A linha que possui o valor que viola a restrição de chave primária, que é “Estruturas de Dados” foi ignorada e não inserida. No entanto, SQLite continua executando outras instruções após essa linha.

Passo 4) DELETE os assuntos da tabela para criá-la novamente com uma cláusula ON CONFLICT diferente para o exemplo a seguir executando o seguinte comando:

DROP TABLE Subjects;

O comando drop exclui a tabela inteira. Os assuntos da tabela agora não existem.

Exemplo de substituição em conflito

Passo 1) Crie um novo assunto de tabela da seguinte forma:

CREATE TABLE [Subjects] (  
    [SubjectId] INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE,
    [SubjectName] NVARCHAR NOT NULL
);

Observe que definimos uma restrição PRIMARY KEY na coluna SubjectId. A restrição de chave primária não permitirá que dois valores duplicados sejam inseridos na coluna SubjectId para que todos os valores nessa coluna sejam exclusivos.

Além disso, observe que escolhemos uma opção de resolução de conflito como “SUBSTITUIR“. O comando deve ser executado com sucesso e você não deve receber nenhum erro:

Passo 2) Agora, vamos inserir alguns valores na nova tabela Assuntos, mas com um valor que viola a restrição de chave primária:

INSERT INTO Subjects VALUES(1, 'Algebra');
INSERT INTO Subjects VALUES(2, 'Database Course');
INSERT INTO Subjects VALUES(2, 'Data Structures');
INSERT INTO Subjects VALUES(4, 'Algorithms');

Nesta instrução INSERT, tentamos inserir dois cursos com o mesmo ID de assunto de chave primária 2, o que é uma violação da restrição de chave primária.

Os comandos devem funcionar bem e você não deve receber nenhum erro. Como segue:

Passo 3) Selecione todos os assuntos da tabela da seguinte forma:

SELECT * FROM Subjects;

Isso lhe dará a lista de assuntos:

Observe que apenas três assuntos foram inseridos “Álgebra, Estruturas de Dados e Algorithms”Enquanto tentamos inserir 4 linhas.

A linha que possui o valor que viola a restrição de chave primária, que é “Estruturas de dados”substituiu o valor“Curso de Base de Dados" como segue:

• As duas primeiras instruções de inserção funcionam bem sem problemas. Serão inseridas duas disciplinas Álgebra e Curso de Banco de Dados com os ids 1, 2.
• Ao SQLite tenta executar a terceira instrução de inserção com SubjectId 2 e SubjectName “Estruturas de dados“, descobre que já existe um assunto com SubjectId = 2. O que é uma violação da restrição de chave primária definida na coluna SubjectId.
• SQLite escolherá uma resolução REPLACE para este conflito. Ele substitui o valor que já existe na tabela de assuntos pelo novo valor da instrução insert. Então o "Curso de Base de Dados”AssuntoName será substituído por“Estruturas de dados" Nome do assunto.

Resumo

As cláusulas INSERT, UPDATE e DELETE são usadas para modificar os dados no SQLite banco de dados. A cláusula CONFLICT é uma cláusula poderosa para resolver qualquer conflito entre os dados e os dados a serem modificados.