Inserting Data

So far we where looking at how to get data from the database. But to get that data, there must be some data in the tables. Until now, I provided pre-existing data for you to play around with. Now lets see how to insert your own data into the table. Introducing the 'INSERT' command...

Syntax

INSERT INTO <Table_Name>(<Field_List>) VALUES(<Data>);

Lets add some new characters to our Simpsons database...

Do you want to insert multiple rows into the database in one go? Its possible in MySQL - but not in SQLite - so I cannot show it to you in the interactive mode. In MySQL you just have to add another value list to the end like this...

INSERT INTO Character(name, job, iq, sex, status) 
	VALUES('Chief Wiggum', 'Police Officer', 90, 'm', 1), 
	('Sarah Wiggum','Housewife', 110, 'f',1),
	('Ralph Wiggum', 'Student', 80, 'm', 1);

Fields and Values

The order in the field list must be used in the values list as well. In the above example, we used this order...

• name
• job
• iq
• sex
• status

That order was followed in the values list as well...

• Chief Wiggum(name)
• Police Officer(job)
• 90(iq)
• m(sex)
• 1(status)

The Missing Fields

What about the fields that are not included in the field list? For example, the Character table has some other fields like 'id' and 'description' - those where not specified in the field or value list.

id field

The id field is a integer primary key - so it has a property called 'auto increment'. The name may differ in different database engines, but the concept is the same. The id field will increase when ever a row is added to the table. The first row will have id 1, the second row will have 2 and so on. This is done automatically by the engine. In MySQL, this property must be explicitly set at table creation. It is done by turning on the 'auto_increment' property of the id field.

description Field

This is a text field so the default value is an empty string. Default values can be set at table creation. If a field has a default value, that value will be used if another value is not specified at insert.

Do you want to be in the Simpsons? Well, here is your chance - get you name in the Simpsons database. Or you can use the select command to see the list of all existing characters...