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 gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. 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!