SQL to modify database

Updating data

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 WHERE clause. In this case, there is likely only one such row, but in general DELETE can remove multiple rows.

  Make doubly sure you always include the WHERE clause! 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 WHERE clause; you should make doubly sure that the WHERE clause is given, to avoid changing all rows in the table.

Creating tables

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 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:

CREATE TABLE users (
    login TEXT PRIMARY KEY,
    passwd TEXT NOT NULL
)

Several options for constraints worth noting:

• PRIMARY KEY suggests 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 INSERT query adding a new row with a login matching one of the existing rows, the INSERT query 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 (
    login TEXT,
    passwd TEXT NOT NULL,
    PRIMARY KEY (login, passwd)
)

• NOT NULL disallows any queries that would set a value in that column to be NULL.

• UNIQUE disallows 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 value indicates 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 subscriptions table.

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)