Inserting Data

Inserting data into a table means adding new information or records to that table. If we think of a table as a drawer of a filing cabinet, inserting data is like adding a new file into that drawer.

Let's say you have an online store, and you're keeping a table of all your customers. When a new customer signs up, you would want to "insert" their details into your customers table. This might include their name, email address, and other important information. This way, you keep your customer list up-to-date, and you can refer back to it when needed.

INSERT INTO Keywords

We can insert data with the INSERT INTO keywords. The INSERT INTO statement in SQL is used to add new rows of data to a table in the database. Here's a breakdown of how it works:

1. INSERT INTO: These are the keywords that begin the command to insert new data. They tell SQL that you're about to add new data.
2. Table Name: After INSERT INTO, you specify the name of the table where you want to add data. For example, INSERT INTO customers means you're adding data to the customers table.
3. Column Names: After the table name, in parentheses, you list the column names where the data will go. These should be the columns for which you'll provide new data. For example, INSERT INTO customers (first_name, last_name, email) specifies that you'll be inserting data into the first_name, last_name, and email columns.
4. VALUES: The VALUES keyword tells SQL that the data for the new row follows.
5. Data Values: After the VALUES keyword, in parentheses, you list the new data that you want to insert in the same order as the column names you specified earlier. Each piece of data is separated by a comma. For example, VALUES ('John', 'Doe', 'john.doe@example.com') adds a new row with these values to the specified columns.

Here's a full example:

INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');

In this example, the INSERT INTO command adds a new row to the customers table. The new row has 'John' as the first_name, 'Doe' as the last_name, and 'john.doe@example.com' as the email.

Default Values

You can set default values for a column when creating a table. The default value is the value that is used if you don't specify a value when inserting a new record.

During the creation of a table, you can set a default value by adding the DEFAULT keyword followed by the default value. Here's an example of how you might create a customers table with default values for a column:

CREATE TABLE customers (
  id BIGINT(20),
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  email VARCHAR(255),
  is_active BOOLEAN DEFAULT TRUE
);

In this example, the is_active column will default to TRUE if no value is provided when a new record is inserted.

If you're inserting data into the table, you can skip columns that have default values, and the default value will be used. For example:

INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');

In this case, since we didn't specify values for is_active, the database will automatically use the default values we specified when creating the table. So, is_active will be set to TRUE for this new customer.

Key Takeaways

• The INSERT INTO is the SQL command used to add new data to a table. It's followed by the table name and the data you want to insert.
• After INSERT INTO, you specify the table and column names where you want to insert data.
• The VALUES keyword in SQL indicates that the data for the new row follows.
• After the VALUES keyword, you list the new data that you want to insert into the table. This data should match up with the column names you specified and in the same order.
• When creating a table, you can set default values for certain columns.
• If a column has a default value, you can skip it when inserting new data. The database will automatically use the default value for that column.