prev | Version 1130 (Mon Nov 27 20:46:06 2006) | next |
Oracle
and IBM's DB2
dominated the marketMySQL
and PostgreSQL
emerged in the 1990sSQLite
is a lightweight alternative for small jobsFigure 20.1: Database Tables
gravity
, Gravity
and GRAVITY
are considered the samesqlite experiments.db < find_names.sql
Figure 20.2: Interacting with a DBMS
CREATE TABLE Person( Login TEXT NOT NULL, LastName TEXT NOT NULL, FirstName TEXT NOT NULL );
NOT NULL
means that the value must be presentDROP TABLE name
INSERT
creates a new rowINSERT INTO Person VALUES("skol", "Kovalevskaya", "Sofia"); INSERT INTO Person VALUES("mlom", "Lomonosov", "Mikhail"); INSERT INTO Person VALUES("dmitri", "Mendeleev", "Dmitri"); INSERT INTO Person VALUES("ivan", "Pavlov", "Ivan");
SELECT Person.FirstName, Person.LastName, Person.Login FROM Person;
Sofia|Kovalevskaya|skol Mikhail|Lomonosov|mlom Dmitri|Mendeleev|dmitri Ivan|Pavlov|ivan
SELECT Person.FirstName, Person.LastName, Person.Login FROM Person ORDER BY Person.Login;
Dmitri|Mendeleev|dmitri Ivan|Pavlov|ivan Mikhail|Lomonosov|mlom Sofia|Kovalevskaya|skol
ORDER BY
SELECT Experiment.ProjectId, Experiment.ExperimentId, Experiment.Hours FROM Experiment WHERE Experiment.Hours < 0;
1737|1|-1.0 1737|2|-1.5
WHERE
to specify conditions that rows must satisfy to be included in resultsFigure 20.3: Inner Joins
SELECT Project.ProjectName, Experiment.ExperimentId, Experiment.Hours FROM Project INNER JOIN Experiment WHERE (Project.ProjectId = Experiment.ProjectId) AND (Experiment.Hours < 0);
Time Travel|1|-1.0 Time Travel|2|-1.5
Project
and Experiment
(which has 3×6=18 rows)Login
column in Involved
identify records in the Person
tableCREATE TABLE Person( Login TEXT NOT NULL, LastName TEXT NOT NULL, FirstName TEXT NOT NULL, PRIMARY KEY (Login) );
CREATE TABLE Experiment( ProjectId INTEGER NOT NULL, ExperimentId INTEGER NOT NULL, NumInvolved INTEGER NOT NULL, ExperimentDate DATE, Hours REAL NOT NULL CONSTRAINT Experiment_Key PRIMARY KEY (ProjectId, ExperimentId) );
SELECT Project.ProjectName, Involved.Login FROM Project, Involved WHERE Project.ProjectId = Involved.ProjectId;
Antigravity|mlom Antigravity|mlom Teleportation|dmitri Teleportation|skol Teleportation|ivan Teleportation|mlom Time Travel|skol Time Travel|skol Time Travel|ivan
mlom
appears twice for the Antigravity
project because he did two experiments for itDISTINCT
keyword to eliminate duplicatesSELECT DISTINCT Project.ProjectName, Involved.Login FROM Project, Involved WHERE Project.ProjectId = Involved.ProjectId;
Antigravity|mlom Teleportation|dmitri Teleportation|skol Teleportation|ivan Teleportation|mlom Time Travel|skol Time Travel|ivan
SELECT SUM(Experiment.Hours) FROM Involved INNER JOIN Experiment WHERE (Involved.Login = "mlom") AND (Involved.ProjectId = 1214) AND (Involved.ProjectId = Experiment.ProjectId) AND (Involved.ExperimentId = Experiment.ExperimentId);
15.8
GROUP BY
to apply aggregation function to specific subsets of rowsSELECT Involved.Login, SUM(Experiment.Hours) FROM Involved INNER JOIN Experiment WHERE (Involved.ProjectId = Experiment.ProjectId) AND (Involved.ExperimentId = Experiment.ExperimentId) GROUP BY Involved.Login;
dmitri|7 ivan|5.5 mlom|23.0 skol|4.5
AND
SELECT DISTINCT Person.Login FROM Person INNER JOIN Involved WHERE (ProjectId = 1214) AND (ProjectId = 1709);
ProjectID
cannot simultaneously be 1214 and 1709OR
SELECT DISTINCT Person.Login FROM Person INNER JOIN Involved WHERE (ProjectId = 1214) OR (ProjectId = 1709);
skol mlom dmitri ivan
skol mlom dmitri ivan
Involved
table with itself, so that we have two project IDs in the same rowSELECT DISTINCT A.Login FROM Involved A CROSS JOIN Involved B WHERE (A.Login = B.Login) AND (A.ProjectId != B.ProjectId);
mlom skol ivan
SELECT DISTINCT A.Login, B.Login FROM Involved A CROSS JOIN Involved B WHERE (A.ProjectId = B.ProjectId) AND (A.ExperimentId = B.ExperimentId) AND (A.Login != B.Login);
NULL
NULL
is a good idea or notIS NULL
NULL
is NULL
2 + NULL
is NULL
, NULL OR True
is NULL
, etc.False AND NULL
is False
, and True OR NULL
is True
NULL
, but this can be prohibited when the table is createdCREATE TABLE Experiment( ProjectId INTEGER NOT NULL, ExperimentId INTEGER NOT NULL, NumInvolved INTEGER NOT NULL, ExperimentDate DATE, Hours REAL NOT NULL );
NULL
into accountExperiment.ExperimentDate <> 1901-05-01
selects all experiments that weren't conducted on May 1, 1901, and all experiments whose date is NULL
(since NULL
isn't equal to anything except itself)(Experiment.ExperimentDate <> 1901-05-01) AND (Experiment.ExperimentDate IS NOT NULL)
Experiment
and Involved
tables into oneFigure 20.4: A Combined Table
InvolvedID
) to relate these tablesInvolved
where ProjectID
is not 1737?SELECT DISTINCT Involved.Login FROM Involved WHERE (Involved.ProjectId != 1737);
mlom dmitri skol ivan
SELECT DISTINCT Login FROM Involved WHERE Login NOT IN (SELECT DISTINCT Login FROM Involved WHERE Involved.ProjectId = 1737);
mlom dmitri
SELECT DISTINCT Login FROM Involved WHERE Login NOT IN (SELECT DISTINCT A.Login FROM Involved A INNER JOIN Involved B WHERE (A.Login = B.Login) AND (A.ProjectId != B.ProjectId));
dmitri
Figure 20.5: Using Databases from Programs
from pysqlite2 import dbapi2 as sqlite connection = sqlite.connect("example.db") cursor = connection.cursor() cursor.execute("SELECT FirstName, LastName FROM Person ORDER BY LastName;") results = cursor.fetchall(); for r in results: print r cursor.close(); connection.close();
("Sofia", "Kovalevskaya") ("Mikhail", "Lomonosov") ("Dmitri", "Mendeleev") ("Ivan", "Pavlov")
Figure 20.6: Race Conditions
"skol"
to "kovalev"
BEGIN TRANSACTION; UPDATE Person SET Login = "kovalev" WHERE Login = "skol"; UPDATE Involved SET Login = "kovalev" WHERE Login = "skol"; END TRANSACTION; SELECT * FROM Person WHERE (Login = "kovalev") OR (Login = "skol"); SELECT * FROM Involved WHERE (Login = "kovalev") OR (Login = "skol");
kovalev|Kovalevskaya|Sofia 1709|1|2|kovalev 1737|1|1|kovalev 1737|2|1|kovalev
Person
changes, but before Involved
changesprev | Copyright © 2005-06 Python Software Foundation. | next |