MySQL programming is at the heart of today's web applications. Learn the basic 4 commands SELECT, UPDATE, INSERT and DELETE.

What Will I Learn?

• Basic MySQL Database Programming Commands

Requirements

• MySQL Server

Difficulty

• Intermediate

Tutorial Contents

How to use SELECT, UPDATE, INSERT and DELETE commands.

Start Learning Basic Database Programming Commands

This tutorial covers concepts and syntax of some basic SQL commands. In order to begin working with MySQL Server and try out the examples listed here, install the free MySQL platform applications.

There are 4 basic SQL commands that will be discussed. While each of these commands have depth and complexity that are beyond the scope of this primer, they have their basic forms and understanding them is an excellent place to begin.

How to Use MySQL SELECT Command

Perhaps the most commonly used SQL command of all, SELECT is used to view records in the database. Open SQL Server and a new query window. Type the following command then click "Execute" or equivalently type Ctrl-e.

SELECT *
FROM Person.Contact

The results are displayed below the query windows. In the lower right corner it shows how long the query took and how many records were retrieved. There are 19,972 records in the Person.Contact table. In the command above, the "" indicates a selection of all fields, or columns, in the table. To limit these, replace the "" with a list of the columns desired.

SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact

View the result and see that only the four columns listed are now included in the results. To view only the contacts that have a title of "Sr." use the WHERE clause to specify a limitation on the type of records returned. This yields 11 records.

SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE Title = 'Sr.'

Notice that the form [column] = [value] is used by the WHERE clause to limit the records returned to only those that match the specified value in the specified field. The use of single-quotes is important to specify string values, as double-quotes have a different meaning in MySQL.

How to Use MySQL UPDATE Command

The UPDATE command is used to modify 1 or more records in a table. Use the WHERE clause to limit which records are updated, just as it was used to limit which records were returned for the SELECT command. View a record in the Person.Contact table, then update it and view it again.

SELECT *
FROM Person.Contact
WHERE ContactID = 10
UPDATE Person.Contact
SET FirstName = 'Ron'
WHERE ContactID = 10
SELECT *
FROM Person.Contact
WHERE ContactID = 10

Notice that the contact's first name has been changed from "Ronald" to "Ron." It is extremely important that the WHERE clause is used with UPDATE. If it were removed from the UPDATE statement above, every single contact in the Person.Contact table would now have the first name "Ron" because there was nothing to limit the scope of the update. Without a limit, or conditional, SQL will happily update every record.

How to Use MySQL INSERT Command

The INSERT command creates a new record in a table. View the records in the Production.Location table, add a new record to it, then view it again. The SELECT statements used to view the records are left to the reader as an exercise.

INSERT 
INTO Production.Location (Name, CostRate, Availability)
VALUES ('Plastics', 14.5, 90.0)

The list of columns after the table name specify that values will be provided for Name, CostRate and Availability. The other two columns in the table, LocationID and ModifiedDate are automatically set by SQL so values are not provided for them. Notice that the number of columns listed must match the number of values provided.

How to Use MySQL DELETE Command

Finally, the DELETE command, as expected, deletes one or more records from a table. The same caution must be taken with DELETE as with UPDATE. If no WHERE clause is specified, every single record in the table will be deleted! Delete the "Plastics" record that was just inserted into Production.Location.

DELETE
FROM Production.Location
WHERE LocationID = 61

Practice variations of these four commands on the AdventureWorks database. Don't be afraid to make mistakes, as a fresh copy of the database can always be installed.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---