The following assume a table users(login, passwd).
To add a new row into the table:
INSERT INTO users (login, passwd) VALUES ('bugs', 'bunny')
In the first parentheses, you list the columns whose values you are specifying; and in the second, you list the values to place into those columns, in the same order. So in this case, we're adding a user named bugs with a password of bunny. We could do exactly the same thing, reversing the values in both sets of parentheses.
INSERT INTO users (passwd, login) VALUES ('bunny', 'bugs')
To remove a row from the table:
DELETE FROM users WHERE login = 'bugs'
This deletes all rows matching the
WHEREclause. In this case, there is likely only one such row, but in general
DELETEcan remove multiple rows.
Make doubly sure you always include the
WHEREclause! If you leave it out, the DBMS will remove all rows in the table.
To change some data in an existing row:
UPDATE users SET passwd = '123456' WHERE login = 'bunny'
Again, this changes all rows matching the
WHEREclause; you should make doubly sure that the
WHEREclause is given, to avoid changing all rows in the table.
To create a new table that doesn't already exist in the database:
CREATE TABLE users (login TEXT, passwd TEXT)
The table name appears between the word
the parentheses. In the parentheses are descriptors of each
column, consisting of the column name and the column's type.
SQLite recognizes five types: text, integer, real (i.e.,
floating-point), blobs (raw binary - from Binary Large OBject),
and numeric (basically untyped).
In addition, each column can optionally have one or more
“constraints.”. Here, then, is a more realistic
definition for the
CREATE TABLE users (
login TEXT PRIMARY KEY,
passwd TEXT NOT NULL
Several options for constraints worth noting:
PRIMARY KEYsuggests that the table should be organized to facilitate fast lookup on this column. Only one primary key may exist for the table, and the table is essentially sorted on that column.
No two rows in the table are allowed to match on the primary key. If somebody were to try an
INSERTquery adding a new row with a
loginmatching one of the existing rows, the
INSERTquery would leave the table unchanged and fail with an error message.
It is possible to have a primary key include multiple columns, where the table is essentially sorted by the first column mentioned and then ties are broken by looking at the second column. The following illustrates how to do this:
CREATE TABLE users (
passwd TEXT NOT NULL,
PRIMARY KEY (login, passwd)
NOT NULLdisallows any queries that would set a value in that column to be
UNIQUEdisallows any queries that would lead that column to contain two identical values.
CHECK (condition)disallows any queries for which the condition evaluates to false. For instance, if we want the database to guarantee that every login name has at least four characters, we could use
login TEXT CHECK (LENGTH(login) >= 4)
DEFAULT valueindicates what value should be given to that column when a row is created without specifying a value for that column.
REFERENCES table(column)indicates that every value in that column should correspond to a value specified in another table. For example, a Twitter-like database might have another table specifying which users have subscribed to which other users' twitter feeds.
CREATE TABLE subscriptions (
subscriber TEXT REFERENCES users(login),
feed TEXT REFERENCES users(login),
PRIMARY KEY (subscriber, feed)
When a new subscription is added, the DBMS will reject the query if either of the columns is not a valid user login. Also, a query will fail if it attempts to delete a user who is represented in the
We saw that we can indicate a primary key to tell the DBMS
how to sort the table. This will lead queries accessing the
table by the primary key column(s) to go much faster, as they
can be done by a binary search rather than going through the
entire table. For example, because
login is a primary
users, a query like
SELECT passwd FROM users WHERE login = 'bugs'”
will go much faster than if
login were not a primary
key. Since that query seems quite realistic, it's a good thing.
Sometimes we expect other queries that aren't facilitated by
the primary key. For example, for
subscriber first and then by
that makes sense, since you'd expect that we'd often want to
retrieve all the feeds for a particular subscriber.
But we might sometimes want to list all the subscribers to a
particular feed, and this sorting technique doesn't help much
The way around that is to create a secondary index,
which creates an auxiliary data structure listing another
column's values and the rows containing that value.
To create a secondary index for
feed, we would do the following.
CREATE INDEX feed_subscribers ON subscriptions (feed)