[SQLite3] Wenn man SQLite3 erlernt I

[Joopi]

In diesem Artikel möchte ich die grundlegenden Befehle für die Admininstration und Verwendung von SQLite3 auf der Konsole zeigen.

Im Gegensatz zu meinem bisherigen Wissen, können durchaus Datentypen bei der Erstellung einer Tabelle mitgegeben werden:

Code:

sqlite> CREATE TABLE images(name TEXT, type TEXT, img BLOB); 
sqlite> INSERT INTO images(name,type,img) 
  ...>  VALUES('icon','jpeg',readfile('icon.jpg'));

Das sind wichtige Seiten im Netz, um etwas über die Syntax zu erfahren:

https://sqlite.org/cli.html

Datenbank anlegen

Eine Datenbank kann in der Konsole wie folgt angelegt werden:

Code:

sqlite3 dbname.db

Existiert die Datenbank bereits, so wird sie geöffnet und der Cursor steht am Prompt.

Existiert die Datenbank noch nicht, wird sie erstellt und der Cursor steht am Prompt. Wird allerdings in der Datenbank nicht mindestens eine Tabelle erstellt, so wird die Datenbank(-datei) beim Verlassen nicht gespeichert. Die Datenbank existiert dann nicht.

Eine Tabelle anlegen

Code:

create table personen (id integer primary key, name, erfasst, geaendert);

Es werden keine expliziten Datentypen mitgegeben. Nur der primary key wird gesetzt und ist ein auto_increment.

Einen Datensatz erzeugen/einfügen

Code:

insert into tbname (name, erfasst, geaendert) values ("Vera", datetime('now'), time('now', 'localtime'));

Die Datums- und Uhrzeitmitgabe ist auch in SQLite3 möglich.

Die Datenbank abfragen

Code:

select * from tbname where id = 1;

Die einfache Abfrage über eine Tabelle funktioniert in SQLite3 analog zu MySQL

Einen Datensatz löschen

Code:

delete from tbname where id = 1;

Einen Datensatz ändern

Code:

update tbname set position = "Abteilung WHERE position = "Abt";

Die Anzahl von Datensätzen abfragen**

Code:

select count(distinct "personalnummer") FROM "personen";

Eine Summe bilden

Code:

select sum(distinct "gehalt") FROM "personen";

Was ist vorhanden?

Eine SQLite3-Datei enthält nicht nur eine Datenbanken, sondern kann mehrere Datenbanken mit den jeweiligen Tabellen enthalten. Hier die wichtigsten Systembefehlte, um zu prüfen, was alles enthalten und wie es aufgebaut ist:

• Datenbank = .databases
  
• Tabellen = .tables
  
• Tabellenstruktur = .schema tbname
  

Hilfen in der Konsole

• Spalterüberschriften zeigen = .headers on
  
• Anzeige/Ausgabe der SELECT-Ergebnisse = .mode formatname
  

The sqlite3 program is able to show the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "list", "quote", "tabs", and "tcl". You can use the ".mode" dot command to switch between these output formats.

Diese Ausgabeformate können am Bildschirm, aber auch zur Ausgabe in eine Datei gesetzt werden.

Import von Daten aus CSV-Datei

Code:

.import /home/django/help/somedata.csv tbname

Dies ersetzt wahrscheinlich nicht die vorherige Erstellung der Tabelle, in die die Daten importiert werden sollen.

Im DB-Browser kann ich Tabellen-Exporte als csv-Datei importeren, wobei dann die Tabelle angelegt wird (ohne Datentypen und ohne primary key).

Tabellen verknüpfen mittels JOIN

Das sind die beiden Tabellen in einer Datenbank:

Code:

sqlite> create table artikel (id integer primary key, titel, user_id);

Code:

sqlite> create table user (id integer primary key, name);

Das sind die eingefügten Werte in den beiden Tabellen:

Code:

sqlite> insert into artikel (titel, user_id) values ("Der Mond", 1);
sqlite> insert into artikel (titel, user_id) values ("Die Sonne", 2);
sqlite> insert into artikel (titel, user_id) values ("Das Universum", 1);
sqlite> insert into artikel (titel, user_id) values ("Fischwelten", 3);
sqlite> insert into artikel (titel, user_id) values ("Schlangenfarm", 3);
sqlite> insert into user (name) values ("Hermann");
sqlite> insert into user (name) values ("Burkhard");
sqlite> insert into user (name) values ("Robert");

Und das ist das SQL-Statement zum JOIN der beiden Tabellen:

Code:

select a.titel, u.name from artikel a inner join user u on a.user_id = u.id where u.id = 3;

Zum Glück ist hier die gleichen Syntax gegeben wie bei MySQL, daher kann ich ggfs. das Jagdzentrum auf SQLite umstellen und die Bilderausgabe direkt aus dem Ordner bewerkstelligen.

Update 30.05.2020 - 21:15 Uhr

Das Thema mit der Erfassung von Datum und Uhrzeit (Uhrzeit lokal) ist so eine Sache für sich. Es gibt verschiedene Möglichkeiten, die unter aufgelistet sind (die letzte ist die beste):

Code:

sqlite> insert into test2 (name, erfasst, geaendert) values ("Vera", datetime('now'), time('now', 'localtime'));

Code:

sqlite> .headers on

Code:

sqlite> select * from test2;
id|name|erfasst|geaendert
1|Vera|2020-05-30 19:10:50|21:10:50

Code:

sqlite> insert into test2 (name, erfasst, geaendert) values ("Helga", date('now'), time('now', 'localtime'));

Code:

sqlite> select * from test2;
id|name|erfasst|geaendert
1|Vera|2020-05-30 19:10:50|21:10:50
2|Helga|2020-05-30|21:12:38

Code:

sqlite> insert into test2 (name, erfasst, geaendert) values ("Irma", date('now'), datetime('now', 'localtime'));

Code:

sqlite> select * from test2;
id|name|erfasst|geaendert
1|Vera|2020-05-30 19:10:50|21:10:50
2|Helga|2020-05-30|21:12:38
3|Irma|2020-05-30|2020-05-30 21:13:21

Bei der letzten Variante im Feld geaendert wird das Datum und die lokale Uhrzeit ausgegeben. Für die Nutzung ist diese Art wohl die beste, denn bei Änderungen wird eh nur das Feld geaendert mitgegeben, bei einer Neuanlage eines Datensatzes werden beide Felder angesprochen.