MySQL

The INSERT statement in MySQL is used to add new rows to a table. This is a basic command under Data Manipulation Language (DML) and is fundamental when interacting with any database. To insert data into a table, you specify the table name and values for the columns in that table. The data must adhere to the structure and constraints defined during the table creation.

Example Scenario:

You are working with a database related to a company, and the tables involved are for employees, departments, and branches.

Steps to Use the INSERT Statement:

1. Basic Syntax of INSERT

   The general format for inserting data into a table is:

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

2. Example 1: Inserting into the employees Table

   Let's assume the employees table has the following structure:

   CREATE TABLE employees(
   employee_id INT AUTO_INCREMENT,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   department_id INT,
   hire_date DATE,
   PRIMARY KEY (employee_id)
   );

   To insert data into this table:

   INSERT INTO employees
   (first_name, last_name, department_id, hire_date)
   VALUES ('John', 'Doe', 1, '2023-09-01');

   Explanation:

   • We’re inserting a new employee named John Doe who works in department 1, and his hire date is September 1, 2023.
3. Example 2: Inserting into the departments Table

   Suppose you have a departments table like this:

   CREATE TABLE departments (
   department_id INT AUTO_INCREMENT,
   department_name VARCHAR(100),
   branch_id INT,
   PRIMARY KEY (department_id)
   );

   To insert data into this table:

   INSERT INTO departments
   (department_name, branch_id)
   VALUES ('Human Resources', 1);

   Explanation:

   • This statement adds a new department named "Human Resources" in branch 1.
4. Inserting Multiple Rows

   You can also insert multiple rows in a single query by using multiple sets of values:

   INSERT INTO employees
   (first_name, last_name, department_id, hire_date)
   VALUES
   ('Alice', 'Smith', 2, '2024-02-15'),
   ('Bob', 'Johnson', 3, '2024-03-10');

   Explanation:

   • This query inserts two new employees, Alice and Bob, with their respective department IDs and hire dates.
5. Example 3: Inserting into the branches Table

   Let's assume the branches table has the following structure:

   CREATE TABLE branches (
   branch_id INT AUTO_INCREMENT,
   branch_name VARCHAR(100),
   location VARCHAR(100),
   PRIMARY KEY (branch_id)
   );

   To insert data into this table:

   INSERT INTO branches
   (branch_name, location)
   VALUES ('Main Branch', 'New York');

   Explanation:

   • This statement adds a new branch called "Main Branch" located in New York.

Notes on Using the INSERT Statement:

1. Auto-Increment Columns:
   • You don't need to specify values for columns with the AUTO_INCREMENT attribute like employee_id or department_id. MySQL will automatically generate the next number.
2. Default Values:
   • If a column has a default value, you can skip it during insertion, and MySQL will use the default value.
3. NULL Values:
   • If a column is nullable, you can explicitly insert NULL into that column:

     INSERT INTO employees
     (first_name, last_name, department_id, hire_date)
     VALUES ('Jane', 'Doe', NULL, '2024-01-05');

4. Inserting into All Columns:
   • If you want to insert values into every column in the table, you can omit the column list:

     INSERT INTO employees
     VALUES (NULL, 'Charlie', 'Brown', 2, '2024-04-05');

   • The NULL value for the employee_id will trigger the AUTO_INCREMENT feature to assign the next available ID.

By following these steps, beginners will be able to insert data into MySQL tables related to company management, including employees, departments, and branches.

To gain complete access, login with gmail or outlook, no need of signup, click here