B.2. SQL Data Manipulation Language (DML)

SQL is a nonprocedural language providing a syntax for extracting data, including a syntax to update, insert, and delete records.

These query and update commands together form the Data Manipulation Language (DML) part of SQL. We cover the following SQL commands in this section:

• SELECT— Extracts data from a database table.

• UPDATE— Updates data in a database table.

• DELETE— Deletes data from a database table.

• INSERT INTO— Inserts new data into a database table.

B.2.1. The SELECT Command

One of the most commonly used SQL commands is SELECT, mandatory when performing a query. The SELECT command is used to retrieve data from a table based on some criteria. It specifies a comma-separated list of fields to be retrieved, and the FROM clause specifies the table(s) to be accessed. The results are stored in a result table known as the result set. The * symbol can be used to represent all of the fields.

Format SELECT column_name(s) FROM table_name Example: SELECT LastName, FirstName, Address FROM Students; Example B.3. mysql> SELECT CompanyName FROM Shippers; +------------------+ | CompanyName | +------------------+ | Speedy Express | | United Package | | Federal Shipping | +------------------+ 3 rows in set (0.05 sec) Explanation The SELECT command will retrieve all items in the field CompanyName FROM the Shippers table. The result-set table is displayed in response to the query.

Select Specified Columns

To select the columns named CompanyName and Phone from the Shippers table, SELECT is followed by a comma-separated list of fields to be selected FROM the Shippers table. The resulting table is called the result set, as shown in Example B.4.

Example B.4.

mysql> SELECT CompanyName, Phone FROM Shippers; +------------------+----------------+ | CompanyName | Phone | +------------------+----------------+ | Speedy Express | (503) 555-9831 | | United Package | (503) 555-3199 | | Federal Shipping | (503) 555-9931 | +------------------+----------------+ 3 rows in set (0.09 sec)

Select All Columns

To select all columns from the Shippers table, use a * symbol instead of column names, as shown in Example B.5. The * is a wildcard character used to represent all of the fields (columns).

Example B.5.

mysql> SELECT * FROM Shippers; +-----------+------------------+----------------+ | ShipperID | CompanyName | Phone | +-----------+------------------+----------------+ | 1 | Speedy Express | (503) 555-9831 | | 2 | United Package | (503) 555-3199 | | 3 | Federal Shipping | (503) 555-9931 | +-----------+------------------+----------------+ 3 rows in set (0.06 sec)

The SELECT DISTINCT Statement

The DISTINCT keyword is used to return only distinct (unique) values from the table. If there are multiple values of a specified field, the DISTINCT result set will display only one.

In the next example, ALL values from the column named ShipName are first selected, and more than 800 records are displayed, but notice that with the DISTINCT keyword, fewer than 90 records are retrieved.

Format SELECT DISTINCT column_name(s) FROM table_name Example B.6. Code View: SELECT ShipName from Orders (Partial Output) | North/South | | Blauer See Delikatessen | | Ricardo Adocicados | | Franchi S.p.A. | | Great Lakes Food Market | | Reggiani Caseifici | | Hungry Owl All-Night Grocers | | Save-a-lot Markets | | LILA-Supermercado | | White Clover Markets | | Drachenblut Delikatessen | | Queen Cozinha | | Tortuga Restaurante | | Lehmanns Marktstand | | LILA-Supermercado | | Ernst Handel | | Pericles Comidas clásicas | | Simons bistro | | Richter Supermarkt | | Bon app' | | Rattlesnake Canyon Grocery | +------------------------------------+ 830 rows in set (0.00 sec) With the DISTINCT keyword, fewer than 90 records are retrieved: SELECT DISTINCT ShipName FROM Orders; | Océano Atlántico Ltda. | | Franchi S.p.A. | | Gourmet Lanchonetes | | Consolidated Holdings | | Rancho grande | | Lazy K Kountry Store | | Laughing Bacchus Wine Cellars | | Blauer See Delikatessen | | North/South | | Cactus Comidas para llevar | | Great Lakes Food Market | | Maison Dewey | | Trail's Head Gourmet Provisioners | | Let's Stop N Shop |

Limiting the Number of Lines in the Result Set with LIMIT

If you do not want to display a huge database, you can limit the number of lines to print by using LIMIT; for example, the tables in the northwind database contain thousands of records. In the previous examples, it would have been better to display a few lines to demonstrate that the query was successful. Because you are getting only a partial list, you might want to know the total number in the table. This can be done by using the SQL_CALC_FOUND_ROWS option and the SQL FOUND_ROWS() function. SQL will calculate the total number of records, and the FOUND_ROWS() function will let you display the results of that calculation.

Example B.7.

mysql> select ShipName from Orders LIMIT 10; +---------------------------+ | ShipName | +---------------------------+ | Vins et alcools Chevalier | | Toms Spezialitaten | | Hanari Carnes | | Victuailles en stock | | Suprêmes délices | | Hanari Carnes | | Chop-suey Chinese | | Richter Supermarkt | | Wellington Importadora | | HILARION-Abastos | +---------------------------+ 10 rows in set (0.00 sec) Explanation With one argument, in this case 10, LIMIT specifies the number of rows to return from the beginning of the result set.

Example B.8.

mysql> SELECT SQL_CALC_FOUND_ROWS ShipName from Orders -> LIMIT 5; +---------------------------+ | ShipName | +---------------------------+ | Vins et alcools Chevalier | | Toms Spezialitaten | | Hanari Carnes | | Victuailles en stock | | Suprêmes délices | +---------------------------+ 5 rows in set (0.03 sec) mysql> SELECT FOUND_ROWS(); +--------------+ | FOUND_ROWS() | +--------------+ | 830 | +--------------+ 1 row in set (0.03 sec) Explanation SQL will calculate the total number of records, limited to 5, and the FOUND_ROWS() function will let you display the results of that calculation.

The WHERE Clause

What if you want to select fields only when a certain set of conditions is true? For example, you might want to list all the customers who come from Sweden and were paid more than $50,000 last year. The WHERE clause is optional and specifies which data values or rows will be selected, based on a condition described after the keyword WHERE. To create the conditions, called the selection criteria, SQL provides a set of operators to further qualify what criteria should be specified in the WHERE clause. See Table B.2.

Table B.2. SQL Operators

Operator	Description	Example
=	Equal to	where country = 'Sweden'
<>, !=	Not equal to[a]	where country <> 'Sweden'
>	Greater than	where salary > 50000
<	Less than	where salary < 50000
>=	Greater than or equal	where salary >= 50000
<=	Less than or equal	where salary <= 50000
IS [NOT] NULL	Is NULL (no value) or Not NULL	where birth = NULL
BETWEEN	Between an inclusive range	where last_name BETWEEN 'Dobbins' AND 'Main'
LIKE	Search for a value like a pattern	where last_name LIKE 'D%'
NOT LIKE	Search for a value not like a pattern	where country NOT LIKE 'Sw%'
!, NOT	Logical not for negation	where age ! 10;
||, OR	Logical OR	where order_number > 10 || part_number = 80
&&, AND	Logical AND	where age > 12 && age < 21
XOR	Exclusive OR	where status XOR

^[a] In some versions of SQL, the <> operator can be written as !=.

Format SELECT column FROM table WHERE column operator value Example: SELECT phone FROM shippers WHERE country like "Sw";

Using Quotes

Quotes are always an issue in programming languages. Should you use a set of single quotes or double quotes, and when should you use them?

SQL uses single quotes around text values (most database systems, including MySQL, also accept double quotes). Numeric values should not be enclosed in quotes.

For text values, this example is correct:

SELECT * FROM Students WHERE FirstName='Marco'

and this example is wrong:

SELECT * FROM Students WHERE FirstName=Marco    Marco should be "Marco"

For numeric values, this example is correct:

SELECT * FROM Students WHERE Year>2004

and this example is wrong:

SELECT * FROM Students WHERE Year>'2004'    '2004' should be 2004

Using the = and <> Operators

In Figure B.9, the CompanyName and Phone fields are retrieved from the Customers table if the condition following the WHERE clause is true; that is, if the string values in the Country field are exactly equal to the string Italy (they must contain the same number and type of characters). The <> operator can be used to test for "not equal to."

What Is NULL?

Null means that there is not a value in a field, or it is unknown, but does not mean a value of zero. If a field is NULL, it is empty, and if it is NOT NULL, it has data. Fields have NULL as a default unless they are specified by NOT NULL in the definition of the table.

Example B.9.

Code View: mysql> SELECT region, country FROM suppliers -> WHERE region IS NULL; +--------+-------------+ | region | country | +--------+-------------+ | NULL | UK | | NULL | Japan | | NULL | Japan | | NULL | UK | | NULL | Sweden | | NULL | Brazil | | NULL | Germany | | NULL | Germany | | NULL | Germany | | NULL | Italy | | NULL | Norway | | NULL | Sweden | | NULL | France | | NULL | Singapore | | NULL | Denmark | | NULL | Netherlands | | NULL | Finland | | NULL | Italy | | NULL | France | | NULL | France | +--------+-------------+ 20 rows in set (0.00 sec) Explanation Displays the region and country from the suppliers database where the region IS NULL; that is, has no value.

Example B.10.

mysql> SELECT region, country FROM suppliers -> WHERE region NOT NULL; +----------+-----------+ | region | country | +----------+-----------+ | LA | USA | | MI | USA | | Asturias | Spain | | Victoria | Australia | | OR | USA | | MA | USA | | NSW | Australia | | Québec | Canada | | Québec | Canada | +----------+-----------+ 9 rows in set (0.00 sec) Explanation Displays the region and country from the suppliers database where the region is NOT NULL; that is, has a value.

The > and < Operators

The > and < operators are used to select rows where the value of a field is greater or less than some value, such as:

SELECT product, price FROM table WHERE price > 50;

SELECT product, price FROM table
WHERE price > 50 && price < 100;

You can also use the >= and <= operators to select rows that are greater than or equal to or less than or equal to some value:

SELECT product, price FROM table
WHERE price >=50;

Example B.11.

mysql> SELECT UnitPrice, Quantity FROM Order_Details -> WHERE UnitPrice > 1 && UnitPrice < 3; +-----------+----------+ | UnitPrice | Quantity | +-----------+----------+ | 2.0000 | 25 | | 2.0000 | 60 | | 2.0000 | 24 | | 2.0000 | 20 | | 2.0000 | 8 | | 2.0000 | 60 | | 2.0000 | 49 | | 2.0000 | 50 | | 2.0000 | 20 |

Example B.12.

mysql> SELECT CategoryName from categories WHERE CategoryName < 'D'; +--------------+ | CategoryName | +--------------+ | Beverages | | Condiments | | Confections | +--------------+ 3 rows in set (0.00 sec)

The AND and OR Operators

AND and OR operators are used in a WHERE clause to further qualify what data you want to select from a table. The AND operator tests one or more conditions to see if all the conditions are true; if so, SELECT displays the rows. The OR operator displays a row if only one of the conditions listed is true. The AND operator can be designated by the && symbol, and the OR operator can be designated as ||.

Example B.13.

mysql> SELECT ContactName FROM Suppliers -> WHERE City = 'Montreal' AND Region = 'Quebec'; +-----------------+ | contactname | +-----------------+ | Jean-Guy Lauzon | +-----------------+ 1 row in set (0.03 sec) Explanation When using the && (AND) operator, both of the conditions being tested in the WHERE clause must be true; that is, both the City must be Montreal and the Region must be Quebec. If both conditions are true, then SELECT will print the ContactName from the Suppliers database.

Example B.14.

mysql> SELECT CompanyName, City FROM Suppliers WHERE -> City = 'Montreal' OR City = 'Boston'; +-----------------------------+----------+ | CompanyName | City | +-----------------------------+----------+ | New England Seafood Cannery | Boston | | Ma Maison | Montreal | +-----------------------------+----------+ 2 rows in set (0.00 sec) Explanation When using the || (OR) operator, only one of the conditions being tested must be true; that is, if either the City is Montreal or the City is Boston, then SELECT will print the CompanyName and City from the Suppliers database.

The LIKE and NOT LIKE Condition

The LIKE pattern-matching operator is a powerful operator that can be used as a condition in the WHERE clause, allowing you to select only rows that are "like" or match a pattern.

A percent sign (%) can be used as a wildcard to match any possible character that might appear before and/or after the characters specified.

A _ is used to match a single character.

The LIKE condition can be used in any valid SQL statement, including SELECT, INSERT, UPDATE, or DELETE.

Format SELECT column FROM table WHERE column LIKE pattern SELECT column FROM table WHERE column NOT LIKE pattern Example: SELECT column FROM customer WHERE last_name LIKE 'Mc%';

The next examples will demonstrate how the % and _ are used with LIKE and NOT LIKE as a wildcard in pattern matching.

Pattern Matching and the % Wildcard

The % wildcard is used to represent one or more of any character when performing pattern matching. For example, if you are looking for all phone numbers in the 408 area code, you could say 408%, and the % will be replaced by any characters after 408.

Example B.15.

mysql> SELECT CompanyName, Country FROM Customers -> WHERE country like 'Sw%'; +--------------------+-------------+ | CompanyName | Country | +--------------------+-------------+ | Berglunds snabbköp | Sweden | | Chop-suey Chinese | Switzerland | | Folk och fä HB | Sweden | | Richter Supermarkt | Switzerland | +--------------------+-------------+ 4 rows in set (0.00 sec) Explanation The SELECT returns all the customers who are from countries that start with Sw.

Example B.16.

mysql> SELECT City, Country FROM Suppliers WHERE City LIKE '%o'; +-----------+---------+ | City | Country | +-----------+---------+ | Tokyo | Japan | | Oviedo | Spain | | Sao Paulo | Brazil | | Salerno | Italy | +-----------+---------+ 4 rows in set (0.00 sec) Explanation The SELECT returns all cities and countries where the % matches any city that ends with a letter o.

Example B.17.

mysql> SELECT Companyname FROM customers -> WHERE CompanyName LIKE '%Super%'; +--------------------+ | Companyname | +--------------------+ | LILA-Supermercado | | Richter Supermarkt | +--------------------+ 2 rows in set (0.00 sec) Explanation The SELECT returns all company names where the % matches any company name that contains the pattern Super.

The _ Wildcard

The next example shows how the underscore (_) wildcard character works. Remember that the _ matches only one character.

Example B.18.

mysql> SELECT extension, firstname FROM employees -> WHERE extension LIKE '4_ _'; +-----------+-----------+ | extension | firstname | +-----------+-----------+ | 428 | Michael | | 465 | Robert | | 452 | Anne | +-----------+-----------+ 3 rows in set (0.00 sec) Explanation This SELECT returns all extensions and first names where the extension has three characters and the first character is a 4. The _ symbol is used to match a single character.

The BETWEEN Statement

The BETWEEN keyword allows you select a field based on criteria that represent a range of values. The syntax for the BETWEEN clause is as follows:

Format SELECT column FROM table WHERE column BETWEEN 'value1' AND 'value2' Example: select age from person where age BETWEEN 10 && 20; Example B.19. mysql> SELECT ProductName, ProductId -> FROM Products WHERE ProductId BETWEEN 30 AND 33; +-----------------------+-----------+ | ProductName | ProductId | +-----------------------+-----------+ | Nord-Ost Matjeshering | 30 | | Gorgonzola Telino | 31 | | Mascarpone Fabioli | 32 | | Geitost | 33 | +-----------------------+-----------+ 4 rows in set (0.06 sec) Explanation The SELECT returns product names and product IDs if the ProductId value is in the range between 30 and 33.

Sorting Results with ORDER BY

You can display the output of a query in a particular order by using the ORDER BY clause. Rows can be sorted in either ascending (ASC, the default) or descending (DESC) order where the values being sorted are either strings or numbers.

Format SELECT column FROM table [WHERE condition] ORDER BY column [ASC, DESC] Example: SELECT Company, OrderNumber FROM Orders ORDER BY Company Example B.20. mysql> SELECT CompanyName, ContactName FROM suppliers -> ORDER BY CompanyName LIMIT 10; +------------------------------------+----------------------------+ | CompanyName | ContactName | +------------------------------------+----------------------------+ | Aux joyeux ecclésiastiques | Guylène Nodier | | Bigfoot Breweries | Cheryl Saylor | | Cooperativa de Quesos 'Las Cabras' | Antonio del Valle Saavedra | | Escargots Nouveaux | Marie Delamare | | Exotic Liquids | Charlotte Cooper | | Forêts d'Trables | Chantal Goulet | | Formaggi Fortini s.r.l. | Elio Rossi | | G'day, Mate | Wendy Mackenzie | | Gai pâturage | Eliane Noz | | Grandma Kelly's Homestead | Regina Murphy | +------------------------------------+----------------------------+ 10 rows in set (0.06 sec) Explanation The CompanyName is sorted in ascending order, limited to 10 records.

Example B.21.

mysql> SELECT CompanyName, ContactName FROM suppliers -> ORDER BY CompanyName DESC LIMIT 10; +---------------------------------------+----------------------------+ | CompanyName | ContactName | +---------------------------------------+----------------------------+ | Zaanse Snoepfabriek | Dirk Luchte | | Tokyo Traders | Yoshi Nagase | | Svensk Sjöföda AB | Michael Björn | | Specialty Biscuits, Ltd. | Peter Wilson | | Refrescos Americanas LTDA | Carlos Diaz | | Plutzer Lebensmittelgro-markte AG | Martin Bein | | PB Knackebröd AB | Lars Peterson | | Pavlova, Ltd. | Ian Devling | | Pasta Buttini s.r.l. | Giovanni Giudici | | Norske Meierier | Beate Vileid | 29 rows in set (0.00 sec) Explanation The CompanyName is sorted in descending order, limited to 10 records.

B.2.2. The INSERT Command

The INSERT INTO statement is used to insert new rows into a table. After the VALUES keyword, a comma-separated list of column names follows.

Format INSERT INTO table_name VALUES (value1, value2,....) You can also specify the columns for which you want to insert data: INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....) Example B.22. INSERT INTO Shippers (CompanyName, Phone) VALUES ('Canada Post', '416-555-1221'); +-----------+------------------+----------------+ | ShipperID | CompanyName | Phone | +-----------+------------------+----------------+ | 1 | Speedy Express | (503) 555-9831 | | 2 | United Package | (503) 555-3199 | | 3 | Federal Shipping | (503) 555-9931 | | 4 | Canada Post | 416-555-1221 | +-----------+------------------+----------------+ Explanation The INSERT INTO statement is inserting a new row into the Shippers table, first by listing the field name, and then the corresponding values after the VALUES keyword. The ShipperID value is not included, because when the table was created, ShipperID was set as a PRIMARY KEY to be autoincremented by the database every time a new shipper record is added. (Letting the database increment the PRIMARY KEY ensures that the value is always unique.) To see how the table was originally set up, see the output from the DESCRIBE command here: mysql> DESCRIBE shippers; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | ShipperID | int(11) | | PRI | NULL | auto_increment | | CompanyName | varchar(40) | | | | | | Phone | varchar(24) | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+

B.2.3. The UPDATE Command

The UPDATE statement is used to modify the data in a table. After the UPDATE command, you list the name of the table where the data will be changed, followed by the SET statement to indicate what field will be changed, and then the new value that will be assigned to the field. The WHERE clause further qualifies what data is to be modified, thereby limiting the scope of the update.

In Example B.23, the key is the use of the WHERE statement to limit the scope of the update.

Format UPDATE table_name SET column_name = new_value WHERE column_name = some_value Example: UPDATE orders SET ShipCountry="Luxembourg" WHERE CustomerId='whitc'; Example B.23. Code View: 1 mysql> select * from shippers; +-----------+------------------+----------------+ | ShipperID | CompanyName | Phone | +-----------+------------------+----------------+ | 1 | Speedy Express | (503) 555-9831 | | 2 | United Package | (503) 555-3199 | | 3 | Federal Shipping | (503) 555-9931 | +-----------+------------------+----------------+ 3 rows in set (0.00 sec) 2 mysql> UPDATE shippers SET PHONE='(777) 444-1234' -> WHERE companyname = 'Federal Shipping'; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 3 mysql> select * from shippers; +-----------+------------------+----------------+ | ShipperID | CompanyName | Phone | +-----------+------------------+----------------+ | 1 | Speedy Express | (503) 555-9831 | | 2 | United Package | (503) 555-3199 | | 3 | Federal Shipping | (777) 444-1234 | +-----------+------------------+----------------+ 3 rows in set (0.00 sec) Explanation The SELECT command shows all the fields in the Shippers table. The UPDATE command allows you to change an existing record. The phone number for Federal Shipping is being changed. This SELECT command shows that the phone number for Federal Shipping was changed by the previous UPDATE command.

B.2.4. The DELETE Statement

The DELETE statement is used to delete rows in a table and returns the number of rows that were deleted. DELETE uses the FROM clause to specify the name of the table that contains the data you want to delete, and the WHERE clause specifies the criteria to identify what data should be removed.

Be careful! Without a WHERE clause, all rows are deleted.^[1]

^[1] You can set up MySQL so that if you use DELETE without a WHERE clause, the rows will not be deleted.

If the ORDER BY clause is specified, the rows are deleted in the order that is specified. The LIMIT clause places a limit on the number of rows that can be deleted.

Format DELETE FROM table_name WHERE column_name = some_value

The DELETE statement is very similar to the UPDATE statement. To delete the previous record, you would enter this query:

DELETE FROM Shippers WHERE CompanyName='Canada Post';