Adding records into a table

Before adding records to a table it is important to know what data types each field are, which fields cannot be null, and if any auto increment. It is good practice to use quotes around string data type values and no quotes around number data type values. You do not insert values for auto incrementing fields, this happens automatically when the record is created.

INSERT INTO

The INSERT INTO statement is used to insert new records in a table. It has two parts, the keyword INSERT INTO is used to select the table and the keyword VALUES is used to set the field values. Below is an example of the code.

INSERT INTO table_name (field1, field2, field3...)
VALUES (fieldvalue1, fieldvalue2, fieldvalue3...);

You may encounter the INSERT INTO command without the use of field names. If you are inserting records in the correct order listed in a table, this can be used as a shorter version of the code.

As mentioned above, fields with a string data type use quotes around the field values. Take for example a table created with the following code:

CREATE TABLE student(
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
state CHAR(2) NOT NULL DEFAULT "VA",
zip MEDIUMINT UNSIGNED NOT NULL,
phone VARCHAR(20) NOT NULL,
birth_date DATE NOT NULL,
sex ENUM(‘M’, ‘F’) NOT NULL,
);

To insert a record into this table we could use:

INSERT INTO student (first_name, last_name, zip, phone, birth_date, sex)
VALUES ("Matthew", "Penning", 55555, "(555) 555-5555", 1900-12-31, "M");

The student_id field auto increments so we do not add a value for that. The state field has a default value of “VA” so if we do not include it, it will use the default value.

Viewing table records

You can view the records from your database using a simple SELECT query. This will ask the database to display the records of a table. To view all the records from a table we can use the wildcard * as our criteria. We must also use the keyword FROM to distinguish the table we wish to query. The command is as follows

SELECT * FROM table_name;

We can also view specific fields rather than the entire table by replacing the * with the field column name(s). For example:

SELECT field_name1, field_name2
FROM table_name;

Lesson Activites

  1. We will insert a record into the faculty table. Use the following code (you can copy and paste the code in to the terminal, use right-click to paste.):
    Click to see code
    INSERT INTO faculty (first_name, last_name, email, street, city, state, zip, phone, birth_date, sex)
    VALUES ("William", "Hartnell", "whartnell@bbcamerica.com", "123 Hartnell Ln", "Norfolk", "VA", 55555, "(111) 555-5555", "1963-11-23", "M");
  2. Now let’s view the record in the table. Enter the following command:
    Click to see code
    SELECT * FROM faculty;
  3. We will insert another record into the faculty table. Use the following code:
    Click to see code
    INSERT INTO faculty (first_name, last_name, email, street, city, state, zip, phone, birth_date, sex)
    VALUES ("Patrick", "Troughton", "ptroughton@bbcamerica.com", "456 Troughton Ln", "Norfolk", "VA", 55555, "(222) 555-5555", "1966-10-29", "M");
  4. Now let’s add multiple records to the faculty table. I recommend copying and pasting. Use the following code:
    Click to see code
    INSERT INTO faculty (first_name, last_name, email, street, city, state, zip, phone, birth_date, sex)
    VALUES ("Jon", "Pertwee", "jpertwee@bbcamerica.com", "789 Pertwee Ln", "Richmond", "VA", 55555, "(333) 555-5555", "1970-01-03", "M");
    INSERT INTO faculty (first_name, last_name, email, street, city, state, zip, phone, birth_date, sex)
    VALUES ("Tom", "Baker", "jtbaker@bbcamerica.com", "987 Baker Ln", "Richmond", "VA", 55555, "(444) 555-5555", "1974-06-08", "M");
    INSERT INTO faculty (first_name, last_name, email, street, city, state, zip, phone, birth_date, sex)
    VALUES ("Bob", "Smith", "bsmith@bbcamerica.com", "555 Smith Ln", "Roanoke", "VA", 55555, "(555) 555-5555", "1999-12-31", "M");
  5. To view the records in the faculty table use:
    Click to see code
    SELECT * FROM faculty;
  6. Now let’s add multiple records to the program table.
    Click to see code
    INSERT INTO program (program_name, description)
    VALUES ("Web Development", "A program designed to prepare students on being a web developer");
    INSERT INTO program (program_name, description)
    VALUES ("Networking", "A program designed to prepare students on being a networking professional");
    INSERT INTO program (program_name, description)
    VALUES ("Desktop Programming", "A program designed to prepare students on being a desktop application developer");
    INSERT INTO program (program_name, description)
    VALUES ("Mobile Programming", "A program designed to prepare students on being a mobile developer");
  7. To view the records in the program table use:
    Click to see code
    SELECT * FROM program;
  8. Now let’s add multiple records to the student table.
    Click to see code
    INSERT INTO student (first_name, last_name, email, street, city, state, zip, phone, birth_date, sex, program_id)
    VALUES ("Clara", "Oswald", "coswald@bbcamerica.com", "1112 Coleman Dr", "Danville", "VA", 55555, "(666) 555-5555","2012-12-01", "F", 1);
    INSERT INTO student (first_name, last_name, email, street, city, state, zip, phone, birth_date, sex, program_id)
    VALUES ("River", "Song", "rsong@bbcamerica.com", "1011 Kingston Dr", "Danville", "VA", 55555, "(777) 555-5555","2008-05-31", "F", 2);
    INSERT INTO student (first_name, last_name, email, street, city, state, zip, phone, birth_date, sex, program_id)
    VALUES ("Rory", "Williams", "rwilliams@bbcamerica.com", "11 Darvill Dr", "Fairfax", "VA", 55555, "(888) 555-5555","2010-04-03", "M", 3);
    INSERT INTO student (first_name, last_name, email, street, city, state, zip, phone, birth_date, sex, program_id)
    VALUES ("Amy", "Pond", "apond@bbcamerica.com", "11 Gillan Dr", "Franklin", "VA", 55555, "(999) 555-5555","2010-04-03", "F", 4);
    INSERT INTO student (first_name, last_name, email, street, city, state, zip, phone, birth_date, sex, program_id)
    VALUES ("Wilfred", "Mott", "wmott@bbcamerica.com", "10 Cribbins Dr", "Lexington", "VA", 55555, "(000) 555-5555","2007-12-25", "M", NULL);
    INSERT INTO student (first_name, last_name, email, street, city, state, zip, phone, birth_date, sex, program_id)
    VALUES ("Sarah", "Smith", "ssmith@bbcamerica.com", "341011 Sladen Dr", "Lexington", "VA", 55555, "(111) 555-5555","1973-12-15", "F", NULL);
  9. To view the records in the student table use:
    Click to see code
    SELECT * FROM student;

Questions

  • 2

    Error on line six?

    tleister
    Reply

    I was having an issue with line six

    • bsmith66

      It needed “program_id”

    • wkuykendall50

      It is now fixed thanks for your patience.