prev | Draft Version 547 (Thu Dec 1 09:18:43 2005) | next |
MySQL
and PostgreSQL
emerged in the 1990sSQLite
offers a lightweight alternative for small jobsLogin
column in Involved
identify records in the Person
tableExperiment
and Involved
tables into oneInvolvedId
) to relate these tablesCREATE TABLE Person( Login TEXT NOT NULL, LastName TEXT NOT NULL, FirstName TEXT NOT NULL );
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");
DROP TABLE name
sqlite < make_running_example.sql
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 ASC;
Dmitri|Mendeleev|dmitri Ivan|Pavlov|ivan Mikhail|Lomonosov|mlom Sofia|Kovalevskaya|skol
ORDER BY
)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
Person
and Involved
(which has 4×9=36 rows)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
AND
SELECT DISTINCT Person.Login FROM Person, Involved WHERE (ProjectId = 1214) AND (ProjectId = 1709);
ProjectId
cannot simultaneously be 1214 and 1709OR
SELECT DISTINCT Person.Login FROM Person, Involved WHERE (ProjectId = 1214) OR (ProjectId = 1709);
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, Involved B WHERE (A.Login = B.Login) AND (A.ProjectId != B.ProjectId);
mlom skol ivan
Involved
where ProjectId
is not 1737, since both Kovalevskaya and Pavlov have worked on other projectsSELECT DISTINCT Involved.Login FROM Involved WHERE (Involved.ProjectId != 1737);
mlom dmitri skol ivan
NOT IN
means exactly what it saysSELECT DISTINCT Login FROM Involved WHERE Login NOT IN (SELECT Login FROM Involved WHERE Involved.ProjectId = 1737);
mlom dmitri
SELECT DISTINCT Login FROM Involved WHERE Login NOT IN (SELECT A.Login FROM Involved A, Involved B WHERE (A.Login = B.Login) AND (A.ProjectId != B.ProjectId));
dmitri
SELECT SUM(Experiment.Hours) FROM Involved, 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, Experiment WHERE (Involved.ProjectId = Experiment.ProjectId) AND (Involved.ExperimentId = Experiment.ExperimentId) GROUP BY Involved.Login;
ivan|5.5 dmitri|7 skol|4.5 mlom|23
import sqlite connection = sqlite.connect("running.db") cursor = connection.cursor() cursor.execute("SELECT FirstName, LastName FROM Person;") results = cursor.fetchall(); for r in results: print r cursor.close(); connection.close();
('Sofia', 'Kovalevskaya') ('Mikhail', 'Lomonosov') ('Dmitri', 'Mendeleev') ('Ivan', 'Pavlov')
NULL
IS 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
Experiment.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)
"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, Python Software Foundation. See License for details. | next |