DataslopeInserting Data in SQLite
Add one row or many rows to SQLite tables with INSERT INTO and then SELECT the results.
A table's structure is only the beginning. To make a table useful, you add rows.
The SQL statement for adding rows is INSERT INTO.
INSERT adds rows
An INSERT statement names the table, names the columns you are filling, and supplies matching values.
Here is a complete example: create a table, insert one row, then read it back.
SQLite does not have a separate BOOLEAN type. In beginner examples, we often use INTEGER values 0 and 1 for yes/no data: 0 means false or no, and 1 means true or yes.
Omit the auto id column
When a table has id INTEGER PRIMARY KEY, you usually leave id out of the insert. SQLite assigns it for you.
The insert only gave title. SQLite supplied id, and DEFAULT 0 supplied done.
Why can the insert omit id in a table with id INTEGER PRIMARY KEY?
SQLite does not store ids.
SQLite can automatically assign the id value.
The id column becomes text instead.
The row is inserted without any columns.
Insert several rows at once
You can add multiple rows with one INSERT by separating value groups with commas.
Each parenthesized group becomes one new row.
Column order matters
The column list and values match by position. The first value goes into the first named column, the second value goes into the second named column, and so on.
In INSERT INTO contacts (name, email, age) VALUES ('Maya', 'maya@example.com', 31);, where does 31 go?
Into name
Into email
Into age
Into id
Insert with explicit columns
You do not have to fill every column every time. Name the columns you are providing, and let defaults or empty values handle the rest.
The first row has no page count, so pages uses its default 0. The second row has no author, so that value is empty in the result.
Practice challenge
The pantry table is already created. Insert two rows:
'Rice'with quantity3'Beans'with quantity5
Then select name and quantity from pantry ordered by id.
Check your understanding
What does INSERT INTO do?
It reads rows from a table.
It adds new rows to a table.
It deletes a table's structure.
It renames every column.
How do you insert multiple rows in one INSERT statement?
Write several table names after INSERT INTO.
Put every value in one long quoted string.
Add several parenthesized value groups separated by commas.
Use SELECT * before VALUES.
In this course's SQLite examples, what do 0 and 1 often represent in yes/no columns?
Table names and column names.
False/no and true/yes values.
Text and dates.
Primary keys only.