von Markus Winand.

# SQLite Skripts für „Die Where-Klausel“

## Der Gleicheheitsoperator

### Primärschlüssel

`EMPLOYEES`-Tabelle mit 1000 Einträgen anlegen.

``````CREATE TABLE employees (
employee_id   NUMERIC      NOT NULL,
first_name    VARCHAR(255) NOT NULL,
last_name     VARCHAR(255) NOT NULL,
date_of_birth DATE                 ,
phone_number  VARCHAR(255) NOT NULL,
junk          CHAR(255)            ,
CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);``````
``````CREATE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL
SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15;``````
``````CREATE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
FROM generator_16 lo, generator_16 hi;``````
``````CREATE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
FROM generator_256 lo, generator_16 hi;``````
``````CREATE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
FROM generator_256 lo, generator_256 hi;``````
``````INSERT INTO employees (employee_id,  first_name,
last_name,    date_of_birth,
phone_number, junk)
SELECT gen.n +1,
CHAR( ABS(random()) % 26 + 65
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
),
CHAR( ABS(random()) % 26 + 65
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
),
DATE('now', '-' || (abs(random()) % 3650 + 40*365) || ' day'),
ABS(RANDOM())%9000+1000,
printf('%.1000c','x')
FROM generator_4k gen
WHERE gen.n < 1000;``````
``````UPDATE employees
SET first_name='MARKUS',
last_name='WINAND'
WHERE employee_id=123;``````
``ANALYZE employees;``

Hinweise:

• Die `GENERATOR_X`-Views erzeugen Zeilen wie in diesem Artikel beschrieben. SQLite unterstützt seit Version 3.8.3 auch die rekursive WITH-Klausel, die hier verwendete Syntax funktioniert jedoch auch in älteren Versionen.

• Die `JUNK`-Spalte wird verwendet, um eine realistische Zeilenlänge zu erhalten. Ohne diese Spalte wäre die Tabelle unrealistisch klein und viele Beispiele würden nicht funktionieren.

• Die Tabelle wird mit Zufallsdaten gefüllt. Danach wird mein Eintrag durch ein `update` geändert.

• Die Tabellen und Index Statistiken werden erstellt, sodass der Optimizer einen Überblick über den Tabelleninhalt hat.

### Zusammengesetzte Schlüssel

``DROP TABLE employees;``
``````CREATE TABLE employees (
employee_id   NUMERIC      NOT NULL,
first_name    VARCHAR(255) NOT NULL,
last_name     VARCHAR(255) NOT NULL,
date_of_birth DATE                 ,
phone_number  VARCHAR(255) NOT NULL,
junk          CHAR(255)            ,
subsidiary_id NUMERIC      NOT NULL,
CONSTRAINT employees_pk PRIMARY KEY (employee_id, subsidiary_id)
);``````
``````INSERT INTO employees (employee_id,  first_name,
last_name,    date_of_birth,
phone_number, junk, subsidiary_id)
SELECT gen.n +1,
CHAR( ABS(random()) % 26 + 65
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
),
CHAR( ABS(random()) % 26 + 65
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
),
DATE('now', '-' || (abs(random()) % 3650 + 40*365) || ' day'),
ABS(RANDOM())%9000+1000,
printf('%.1000c','x'),
30
FROM generator_4k gen
WHERE gen.n < 1000;``````
``````UPDATE employees
SET first_name='MARKUS',
last_name='WINAND'
WHERE employee_id=123
AND subsidiary_id=30;``````
``````-- generate more records (Very Big Company)
INSERT INTO employees (employee_id,  first_name,
last_name,    date_of_birth,
phone_number, subsidiary_id, junk)
SELECT gen.n + 1,
CHAR( ABS(random()) % 26 + 65
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
),
CHAR( ABS(random()) % 26 + 65
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
),
DATE('now', '-' || (abs(random()) % 3650 + 40*365) || ' day'),
ABS(RANDOM())%9000+1000,
CAST(ABS(RANDOM())%(gen.n/9000.0 * 29 + 1) + 1 AS INTEGER),
printf('%.10c','x')
FROM generator_64k gen
WHERE gen.n < 9000;``````
``ANALYZE employees;``

Hinweise:

• SQLite kann Primärschlüssel nicht ändern. Daher wird die ganze Tabelle neu angelegt.

• Die neuen Einträge werden zufällig auf die Töchter 1 bis 29 aufgeteilt.

• Die Tabelle und Indizes werden neu analysiert, damit der Optimizer das neue Mengengerüst kennt.

Das nächste Skript erstellt den Index auf `SUBSIDIARY_ID`, um Abfragen nach allen Angestellten eines bestimmten Subunternehmens zu unterstützen:

``CREATE INDEX emp_sub_id ON employees(subsidiary_id)``

Besser ist es jedoch, einen Index für beide Anforderungen zu verwenden.

``DROP TABLE employees;``
``````CREATE TABLE employees (
employee_id   NUMERIC      NOT NULL,
first_name    VARCHAR(255) NOT NULL,
last_name     VARCHAR(255) NOT NULL,
date_of_birth DATE                 ,
phone_number  VARCHAR(255) NOT NULL,
junk          CHAR(255)            ,
subsidiary_id NUMERIC      NOT NULL,
CONSTRAINT employees_pk PRIMARY KEY (subsidiary_id, employee_id)
);``````
``````INSERT INTO employees (employee_id,  first_name,
last_name,    date_of_birth,
phone_number, junk, subsidiary_id)
SELECT gen.n +1,
CHAR( ABS(random()) % 26 + 65
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
),
CHAR( ABS(random()) % 26 + 65
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
),
DATE('now', '-' || (abs(random()) % 3650 + 40*365) || ' day'),
ABS(RANDOM())%9000+1000,
printf('%.1000c','x'),
30
FROM generator_4k gen
WHERE gen.n < 1000;``````
``````UPDATE employees
SET first_name='MARKUS',
last_name='WINAND'
WHERE employee_id=123
AND subsidiary_id=30;``````
``````-- generate more records (Very Big Company)
INSERT INTO employees (employee_id,  first_name,
last_name,    date_of_birth,
phone_number, subsidiary_id, junk)
SELECT gen.n + 1,
CHAR( ABS(random()) % 26 + 65
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
),
CHAR( ABS(random()) % 26 + 65
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
, ABS(random()) % 26 + 97
),
DATE('now', '-' || (abs(random()) % 3650 + 40*365) || ' day'),
ABS(RANDOM())%9000+1000,
CAST(ABS(RANDOM())%(gen.n/9000.0 * 29 + 1) + 1 AS INTEGER),
printf('%.10c','x')
FROM generator_64k gen
WHERE gen.n < 9000;``````
``ANALYZE employees;``

Hinweise:

• Die Tabelle wird wieder neu angelegt, da SQLite den Primärschlüssel nicht ändern kann.

## Funktionen

SQLite bietet keine `Create function`-Syntax an, sodass die entsprechende Ausdruck direkt in die Abfrage geschrieben werden muss.

``````SELECT first_name, last_name
, CAST(STRFTIME('%Y.%m%d', 'now') - STRFTIME('%Y.%m%d', date_of_birth) AS INT)
FROM employees
WHERE CAST(STRFTIME('%Y.%m%d', 'now') - STRFTIME('%Y.%m%d', date_of_birth) AS INT) = 42``````

Das Alter wird ermittelt, indem Daten als Jahreszahlen mit Komma formatiert werden.

Ausdrücke können indiziert werden, wenn sie deterministisch sind.

``````CREATE INDEX emp_age ON employees
( CAST(STRFTIME('%Y.%m%d', 'now') - STRFTIME('%Y.%m%d', date_of_birth) AS INT) )``````
``Error: non-deterministic use of strftime() in an index``

## Über den Autor

Markus Winand ist der SQL Renaissance Botschafter auf der Mission, Entwickler auf die Evolution von SQL im 21. Jahrhundert aufmerksam zu machen. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Besuche meine Schwester-Seite!Seit SQL-92 hat sich einiges getan!

Aufkleber, Bierdeckel, Bücher und Kaffeetassen. Alles was man beim Lernen braucht!