2. Manipulating Data

Overview

This chapter teaches you to implement the INSERT, UPDATE, and DELETE statements which help you keep the content present in a table up-to-date. We will also cover how we can use default values while updating the tables.

Introduction

In Chapter 1, SQL Basics, we learned the concepts that help set up a database. Although we did insert some data into the tables, we didn't quite get into the specifics of managing the data within the database. However, there might be circumstances where we might need to change the data inserted or present in the database. For example, an employee working for a company might want to change their official number from what was updated earlier. A product that is no longer manufactured needs to be removed from the list of products available. MySQL provides some commands we can implement to make changes to the database, which will be covered in this chapter. In this chapter, we will fill the tables we created in the previous chapter with data. We will also look at the UPDATE and DELETE operations that are part of CRUD (Create, Read, Update, and Delete).

The INSERT Operation

The INSERT operation inserts a record within a table. We have already used the insert operation in the previous chapter, however, in this chapter, we are looking at it in more detail. The following are some important points about the INSERT operation:

• It is not always essential to provide data for every single column when performing an INSERT operation. The columns can be left blank unless there is a constraint that forbids it. Some columns may even have default or system-generated values.
• You should not alter the system-generated values.
• The column values must match the order, data type, and size requirements.
• The values to be inserted into the table must be enclosed in quotes in the case of strings, date-time, and characters. Numbers should not be enclosed in quotes.
• If you do not specify the column names in the INSERT statement, your record should have a value for all the columns. Also, you should maintain the sequence of columns in...

The DELETE Operation

The DELETE statement deletes one or more rows within a table. Like the INSERT operations, DELETE also works only on a single table at a time. A deletion operation should be performed very carefully because deletion is permanent. Many database tools require you to add a WHERE clause to the DELETE FROM statement. When performing these deletion operations, which require a WHERE clause, and you would like to delete all the rows in a table, you could work around this by using a condition that is true for all the rows in the table.

For example, say you have an employee with empno 1234 who is no longer associated with the company. In such cases your query would look like the following:

DELETE FROM employees
    WHERE empno = 1234;

If you would like to remove the top 5 rows from the employees table, we would use the following query:

DELETE FROM employees
    LIMIT 5;

Exercise 2.03: Deleting a record from a table

...

The ALTER Operation

The ALTER keyword is used to make changes to the schemas present in the database. For example, if we want to add or delete columns in a table, we should be using ALTER. It can also be used rename to tables. For example:

ALTER TABLE departmentdemo RENAME TO departmentcopy;

This will rename the table departmentdemo to departmentcopy.

Now, let us look at solving one of the main issues we might encounter with auto-increment values using alter.

Exercise 2.04: Manipulating the Auto-Increment Values in a Table

In this exercise, we'll alter a table and manipulate the auto-increment values. We'll be continuing from where we left off in Exercise 2.03, Specifying Default Values. Let's go through the following steps:

1. Delete the rows where departmentNo is greater than 2; this will delete the two rows where departmentNo is 3 and 4:

   delete from department where departmentNo>2;

2. Select the department table to get a preview of the existing...

The UPDATE Operation

As we saw in the overview, UPDATE modifies data in one or more columns in a table. Just like the INSERT operation, the UPDATE operation can also only be performed against a single table using a single statement. In most situations, you will filter out the records you would like to update and update only them. This filtration is done using a WHERE clause in the SELECT statement. The UPDATE statement also contains a SET clause, which defines what needs to be modified within the table, along with the values.

In our demo, we'll update a table called email. You can set one column or more during an UPDATE operation. If you want to update multiple columns, separate the column names with a comma.

The database engine looks for the column that is specified in the statement and updates all the rows in it. If you would like to update only a certain row or a certain set of rows, you would use the WHERE clause. This way, you can identify only those intersections of...

Summary

In this chapter, we looked at some advanced implementations of INSERT and how we can manipulate data using the UPDATE, ALTER, and DROP commands. It is very important to remember that the ALTER command is used to update the schemas, while the update command is used to make changes in the data contained in the schemas. However, changes in the data need to managed correctly, or they will result in inconsistent data. In the next chapter, we will look at normalizing data to ensure that data integrity is maintained.