DML (Data Manipulation Language)
The Data Manipulation Language (DML) is used to retrieve, insert and
modify database information. These commands will be used by all
database users during the routine operation of the database. Let's take
a brief look at the basic DML commands:
INSERT
The INSERT command in SQL is used to add records to an existing table.
Returning to the personal_info example from the previous section, let's
imagine that our HR department needs to add a new employee to their
database. They could use a command similar to the one shown below:
INSERT INTO personal_info
values('bart','simpson',12345,$45000)
Note that there are four values specified for the record. These
correspond to the table attributes in the order they were defined:
first_name, last_name, employee_id, and salary.
SELECT
The SELECT command is the most commonly used command in SQL. It allows
database users to retrieve the specific information they desire from an
operational database. Let's take a look at a few examples, again using
the personal_info table from our employees database.
The command shown below retrieves all of the information contained
within the personal_info table. Note that the asterisk is used as a
wildcard in SQL. This literally means "Select everything from the
personal_info table."
SELECT *
FROM personal_info
Alternatively, users may want to limit the attributes that are retrieved
from the database. For example, the Human Resources department may
require a list of the last names of all employees in the company. The
following SQL command would retrieve only that information:
SELECT last_name
FROM personal_info
Finally, the WHERE clause can be used to limit the records that are
retrieved to those that meet specified criteria. The CEO might be
interested in reviewing the personnel records of all highly paid
employees. The following command retrieves all of the data contained
within personal_info for records that have a salary value greater than
$50,000:
SELECT *
FROM personal_info
WHERE salary > $50000
UPDATE
The UPDATE command can be used to modify information contained within a
table, either in bulk or individually. Each year, our company gives all
employees a 3% cost-of-living increase in their salary. The following
SQL command could be used to quickly apply this to all of the employees
stored in the database:
UPDATE personal_info
SET salary = salary * 1.03
On the other hand, our new employee Bart Simpson has demonstrated
performance above and beyond the call of duty. Management wishes to
recognize his stellar accomplishments with a $5,000 raise. The WHERE
clause could be used to single out Bart for this raise:
UPDATE personal_info
SET salary = salary + $5000
WHERE employee_id = 12345
DELETE
Finally, let's take a look at the DELETE command. You'll find that the
syntax of this command is similar to that of the other DML commands.
Unfortunately, our latest corporate earnings report didn't quite meet
expectations and poor Bart has been laid off. The DELETE command with a
WHERE clause can be used to remove his record from the personal_info
table:
DELETE FROM personal_info
WHERE employee_id = 12345
