Tuesday, April 26, 2011

Working with SQLite in AIR: Part 3

Today we will continue learning SQL.

We know how to create tables, now we need to add some data to our table.

To do that, we can use the "INSERT" command. You need to specify the name of the table, the columns we want to add our values to, and the values.

It will look like this:

INSERT INTO tablename (column1, column2, column3...) VALUES (value1, value2, value3...)

The position of the columns we've specified and the values should be the same. For example, value1 goes into column1 of the row. We only need to specify the columns that we want to add values to.

To add a row to our previously created contacts table, we can do this:

INSERT INTO contacts (fname, lname, age) VALUES ("Ted", "Johnson", 40)

As you can see, we've skipped the id column in our insertion. We did that because there's no need to set it manually - the column has the PRIMARY KEY AUTOINCREMENT requirement, which automatically creates a unique number as the value of that cell.

It should be noted that if we are passing string values, we must put them in "". If we don't do that, the engine will try to read the value as a column name instead of a text value.

To edit some existing data in our database, we can use the "UPDATE" command. We'll need to specify the name of the table, the column that will have the value we want to update, and a "WHERE" expression to tell the engine which row to edit.

UPDATE tablename SET column = value(, column2 = value2, column3 = value3...) WHERE expression

The "SET" expression contains the columns we need to update in the row and the values that we need to apply to those columns in the row. The "WHERE" expression is like an "if" statement, using this we tell the engine which row to edit.

Example:

UPDATE contacts SET age = 21 WHERE id = 1

This will set the age of the person in the database whose id is 1 to 21.

We can use "AND" and "OR" operators in the "WHERE" expression.

UPDATE contacts SET age = 21 WHERE fname = "Bob" AND lname = "Jackson"

That's all for now.

Thanks for reading!

Related:

Working with SQLite in AIR: Part 1
Working with SQLite in AIR: Part 2
Working with SQLite in AIR: Part 4
Working with SQLite in AIR: Part 5
Working with SQLite in AIR: Part 6
Working with SQLite in AIR: Part 7
Working with SQLite in AIR: Part 8
Working with SQLite in AIR: Part 9
Working with SQLite in AIR: Part 10
Working with SQLite in AIR: Part 11
Working with SQLite in AIR: Part 12

1 comment:

Post a Comment