SQL to modify database

Updating data

The following assume a table users(loginpasswd).

Creating tables

To create a new table that doesn't already exist in the database:

CREATE TABLE users (login TEXTpasswd TEXT)

The table name appears between the word TABLE and 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 users table:

    passwd TEXT NOT NULL

Several options for constraints worth noting:

Creating indexes

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 key for 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 subscriptions, we've sorted by subscriber first and then by feed; 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 for that.

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 subscriptions sorted by feed, we would do the following.

CREATE INDEX feed_subscribers ON subscriptions (feed)