SQLite INSERT, UPDATE, DELETE Consulta com Exemplo

Índice:

Anonim

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

Neste tutorial, você aprenderá-

  • INSERIR
  • Atualizar
  • Excluir
  • Cláusula de Conflito

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

Etapa 1) Nesta etapa,

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

Etapa 2) Abra o banco de dados " TutorialsSampleDB.db " pelo seguinte comando:

.open TutorialsSampleDB.db

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

SQLite INSERT

SQLite INSERT é usado para inserir registros em uma tabela especificada do banco de dados. você tem que 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, na qual 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 você deseja inserir várias linhas, deve escrever várias cláusulas INSERT, uma para cada linha.

Exemplo de inserção de SQLite

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 instrução, não o fizemos.

Os quatro valores " 12, 'Aly', 4, '1996-10-12' " serão inseridos em todas as quatro colunas da tabela de Alunos na mesma ordem em que as colunas são definidas.

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

SELECIONE * DOS Alunos;

Em seguida, você verá os dois alunos retornados dessa consulta da seguinte forma:

Atualização SQLite

SQLite UPDATE Query é usado para modificar os registros existentes em uma tabela. Você pode usar a cláusula WHERE com a consulta UPDATE para atualizar as 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. Apenas as linhas que a expressão avalia como verdadeiras são atualizadas. Se você não especificou uma cláusula WHERE, todas as linhas serão atualizadas.

Exemplo de atualização SQLite

Na seguinte declaração UPDATE, iremos atualizar o DepartmentId para o Student with StudentId = 6 para ser 3:

ATUALIZAR AlunosSET DepartmentId = 3ONDE StudentId = 6;

Isso deve ser executado com êxito e você não deve obter nenhum resultado:

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

  • 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 para os alunos selecionados para ser 3.

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

SELECIONE * DE Alunos ONDE StudentId = 6;

Agora você deve ver que o valor do Id do departamento agora é 3, conforme a seguir:

SQLite Delete

A consulta SQLite 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: a cláusula DELETE é usada para excluir alguns registros de uma tabela ou excluir todos os registros e não excluirá a própria tabela. No entanto, a cláusula DROP é usada para excluir toda a tabela com todos os registros nela. )
  • Se você escrever a cláusula DELETE como esta "DELETE FROM guru", isso irá deletar todos os registros da tabela "guru".
  • Você pode especificar uma condição WHERE com uma expressão se quiser excluir algumas linhas específicas. Apenas 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 com id maior que 5.

Exemplo

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

APAGAR DE Alunos ONDE StudentId = 11 OU StudentId = 12;

A expressão " StudentId = 11 OR StudentId = 12 " será verdadeira apenas para alunos com Ids 11 e 12. Portanto, a cláusula DELETE será aplicada em ambos e excluirá apenas eles.

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 de Alunos da seguinte forma:

SELECIONE * DOS Alunos;

Você não deve ver os dois alunos com ids 11 e 12 da seguinte maneira:

Cláusula de conflito SQLite

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

Por exemplo, se uma coluna tem uma restrição UNIQUE e você tentou inserir um valor que já existe (um valor duplicado), o que está 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 operações SQLite (inserir, atualizar ou excluir). A transação do banco de dados deve ser executada como uma unidade, todas as operações executadas com sucesso ou não. 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 deve falhar no meio do caminho.

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

  1. ROLLBACK - isso irá reverter a transação em que a instrução SQLite atual que está em conflito (irá cancelar toda a transação). Por exemplo, se você está tentando atualizar 10 linhas e a quinta linha tem um valor que está em conflito com uma restrição, nenhuma linha será atualizada, as 10 linhas permanecerão as mesmas. Um erro será lançado.
  2. ABORT - isto irá abortar (cancelar) a instrução SQLite atual apenas que tem o conflito e a transação não será cancelada. Por exemplo, se você está tentando atualizar 10 linhas, e a quinta linha tem um valor que está em conflito com uma restrição, então apenas o quinto valor não será atualizado, mas as outras 9 linhas serão atualizadas. Um erro será lançado.
  3. FAIL - aborta a instrução SQLite atual que está em conflito. No entanto, a transação não continuará, mas as alterações anteriores feitas nas linhas anteriores à linha que tem o conflito serão confirmadas. Por exemplo, se você está tentando atualizar 10 linhas, e a quinta linha tem um valor que está em conflito com uma restrição, então apenas as 4 linhas serão atualizadas e as outras não. Um erro será lançado.
  1. IGNORE - isso irá ignorar a linha que contém a violação de restrição e continuar processando as outras linhas seguintes da instrução SQLite. Por exemplo, se você está tentando atualizar 10 linhas, e a quinta linha tem um valor que está em conflito com uma restrição, então apenas as 4 linhas serão atualizadas e as outras não. Ele não continuará atualizando outras linhas e parará na linha que tem o valor de conflito. Nenhum erro será lançado.
  1. REPLACE - 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 de 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, o SQLite irá abortar a instrução (a instrução será cancelada)
  • SE ocorrer a violação da restrição CHECK, a cláusula será abortada.

Nota: As 5 resoluções acima são opções de como você deseja resolver o conflito. Pode não ser necessariamente o que é aplicável para resolver um conflito é 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 dentro da cláusula CREATE TABLE. Usando a seguinte sintaxe:

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

ON CONFLICT IGNORE Exemplo

Etapa 1) Crie um novo assunto de tabela da seguinte maneira:

CRIAR TABELA [Assuntos] ([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 permite que dois valores duplicados sejam inseridos na coluna SubjectId, de forma que todos os valores dessa coluna sejam exclusivos. Além disso, observe que escolhemos uma resolução de conflito como " IGNORAR ".

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

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

INSERT INTO Disciplinas VALUES (1, 'Álgebra');INSERT INTO Disciplinas VALORES (2, 'Curso de banco de dados');INSERT INTO Disciplinas VALUES (2, 'Estruturas de dados');INSERT INTO Assuntos VALUES (4, 'Algoritmos');

Nessa instrução INSERT, tentamos inserir dois cursos com o mesmo ID de Assunto da 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:

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

SELECIONE * DE Assuntos;

Isso lhe dará a lista de assuntos:

Observe que apenas três disciplinas foram inseridas " Álgebra, Curso de Banco de Dados e Algoritmos " em vez de 4 linhas.

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

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

Assuntos DROP TABLE;

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

ON CONFLICT REPLACE Exemplo

Etapa 1) Crie um novo assunto de tabela da seguinte maneira:

CRIAR TABELA [Assuntos] ([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 permite que dois valores duplicados sejam inseridos na coluna SubjectId, de forma que todos os valores dessa 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:

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

INSERT INTO Disciplinas VALUES (1, 'Álgebra');INSERT INTO Disciplinas VALORES (2, 'Curso de banco de dados');INSERT INTO Disciplinas VALUES (2, 'Estruturas de dados');INSERT INTO Assuntos VALUES (4, 'Algoritmos');

Nessa instrução INSERT, tentamos inserir dois cursos com o mesmo ID de Assunto da 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:

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

SELECIONE * DE Assuntos;

Isso lhe dará a lista de assuntos:

Observe que apenas três assuntos foram inseridos " Álgebra, Estruturas de Dados e Algoritmos ", enquanto tentamos inserir 4 linhas.

A linha que possui o valor que viola a restrição da chave primária, que é " Estruturas de dados ", substituiu o valor " Curso de banco de dados " da seguinte forma:

  • As duas primeiras instruções de inserção funcionam sem problemas. Serão inseridas duas disciplinas de Álgebra e Curso de Banco de Dados com ids 1, 2.
  • Quando o SQLite tenta executar a terceira instrução insert com SubjectId 2 e SubjectName " Data Structures ", ele 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.
  • O 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. Portanto, o SubjectName do " Curso de banco de dados " será substituído pelo SubjectName de " Estruturas de dados ".

Resumo:

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