MySQL SQL
What is SQL?
SQL is the standard language for dealing with Relational Databases.
SQL is used to insert, search, update, and delete database records.
How to Use SQL
The following SQL statement selects all the records in the "Customers" table:
Example
SELECT * FROM Customers;
Keep in Mind That...
- SQL keywords are NOT case sensitive:
select is the same as
SELECT
In this tutorial we will write all SQL keywords in upper-case.
Semicolon after SQL Statements?
Some database systems require a semicolon at the end of each SQL statement.
Semicolon is the standard way to separate each SQL statement in database
systems that allow more than one SQL statement to be executed in the same call
to the server.
In this tutorial, we will use semicolon at the end of each SQL statement.
Some of The Most Important SQL Commands
-
SELECT - extracts data from a database
-
UPDATE - updates data in a database
-
DELETE - deletes data from a database
-
INSERT INTO - inserts new data into a database
-
CREATE DATABASE - creates a new database
-
ALTER DATABASE - modifies a database
-
CREATE TABLE - creates a new table
-
ALTER TABLE - modifies a table
-
DROP TABLE - deletes a table
-
CREATE INDEX - creates an index (search key)
-
DROP INDEX - deletes an index
The MySQL SELECT Statement
The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
SELECT Syntax
SELECT column1, column2, ... FROM table_name;
Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:
SELECT * FROM table_name;
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table in the Northwind sample database:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1
|
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4
|
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Luleå |
S-958 22 |
Sweden |
SELECT Columns Example
The following SQL statement selects the "CustomerName", "City", and "Country" columns from the "Customers" table:
Example
SELECT CustomerName, City, Country FROM Customers;
SELECT * Example
The following SQL statement selects ALL the columns from the "Customers" table:
Example
SELECT * FROM Customers;
The MySQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, ... FROM table_name;
SELECT Example Without DISTINCT
The following SQL statement selects all (including the duplicates) values from the "Country" column in the "Customers" table:
Example
SELECT Country FROM Customers;
Now, let us use the SELECT DISTINCT statement and see the result.
SELECT DISTINCT Examples
The following SQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table:
Example
SELECT DISTINCT Country FROM Customers;
The following SQL statement counts and returns the number of different (distinct) countries in the "Customers" table:
Example
SELECT COUNT(DISTINCT Country) FROM Customers;
The MySQL WHERE Clause
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
WHERE Syntax
SELECT column1, column2, ... FROM table_name WHERE condition;
Note: The WHERE clause is not only used in
SELECT statements, it is also used in UPDATE , DELETE , etc.!
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1
|
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4
|
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Luleå |
S-958 22 |
Sweden |
WHERE Clause Example
The following SQL statement selects all the customers from "Mexico":
Example
SELECT * FROM Customers WHERE Country = 'Mexico';
Text Fields vs. Numeric Fields
SQL requires single quotes around text values (most database systems will also allow double quotes).
However, numeric fields should not be enclosed in quotes:
Example
SELECT * FROM Customers WHERE CustomerID = 1;
Operators in The WHERE Clause
The following operators can be used in the WHERE clause:
Operator |
Description |
Example |
= |
Equal |
|
> |
Greater than |
|
< |
Less than |
|
>= |
Greater than or equal |
|
<= |
Less than or equal |
|
<> |
Not equal. Note: In some versions of SQL this operator may be written as != |
|
BETWEEN |
Between a certain range |
|
LIKE |
Search for a pattern |
|
IN |
To specify multiple possible values for a column |
|
The MySQL AND, OR and NOT Operators
The WHERE clause can be combined with
AND , OR , and
NOT operators.
The AND and OR operators are used to filter records based on more than one condition:
- The
AND operator displays a record if all the conditions separated by
AND are TRUE.
- The
OR operator displays a record if any of the conditions separated by
OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.
AND Syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...;
OR Syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;
NOT Syntax
SELECT column1, column2, ... FROM table_name WHERE NOT condition;
Demo Database
The table below shows the complete "Customers" table from the Northwind sample database:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1 |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4 |
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Luleå |
S-958 22 |
Sweden |
6 |
Blauer See Delikatessen |
Hanna Moos |
Forsterstr. 57 |
Mannheim |
68306 |
Germany |
7 |
Blondel père et fils |
Frédérique Citeaux |
24, place Kléber |
Strasbourg |
67000 |
France |
8 |
Bólido Comidas preparadas |
Martín Sommer |
C/ Araquil, 67 |
Madrid |
28023 |
Spain |
9 |
Bon app' |
Laurence Lebihans |
12, rue des Bouchers |
Marseille |
13008 |
France |
10 |
Bottom-Dollar Marketse |
Elizabeth Lincoln |
23 Tsawassen Blvd. |
Tsawassen |
T2F 8M4 |
Canada |
11 |
B's Beverages |
Victoria Ashworth |
Fauntleroy Circus |
London |
EC2 5NT |
UK |
12 |
Cactus Comidas para llevar |
Patricio Simpson |
Cerrito 333 |
Buenos Aires |
1010 |
Argentina |
13 |
Centro comercial Moctezuma |
Francisco Chang |
Sierras de Granada 9993 |
México D.F. |
05022 |
Mexico |
14 |
Chop-suey Chinese |
Yang Wang |
Hauptstr. 29 |
Bern |
3012 |
Switzerland |
15 |
Comércio Mineiro |
Pedro Afonso |
Av. dos Lusíadas, 23 |
São Paulo |
05432-043 |
Brazil |
16 |
Consolidated Holdings |
Elizabeth Brown |
Berkeley Gardens 12 Brewery |
London |
WX1 6LT |
UK |
17 |
Drachenblut Delikatessend |
Sven Ottlieb |
Walserweg 21 |
Aachen |
52066 |
Germany |
18 |
Du monde entier |
Janine Labrune |
67, rue des Cinquante Otages |
Nantes |
44000 |
France |
19 |
Eastern Connection |
Ann Devon |
35 King George |
London |
WX3 6FW |
UK |
20 |
Ernst Handel |
Roland Mendel |
Kirchgasse 6 |
Graz |
8010 |
Austria |
21 |
Familia Arquibaldo |
Aria Cruz |
Rua Orós, 92 |
São Paulo |
05442-030 |
Brazil |
22 |
FISSA Fabrica Inter. Salchichas S.A. |
Diego Roel |
C/ Moralzarzal, 86 |
Madrid |
28034 |
Spain |
23 |
Folies gourmandes |
Martine Rancé |
184, chaussée de Tournai |
Lille |
59000 |
France |
24 |
Folk och fä HB |
Maria Larsson |
Åkergatan 24 |
Bräcke |
S-844 67 |
Sweden |
25 |
Frankenversand |
Peter Franken |
Berliner Platz 43 |
München |
80805 |
Germany |
26 |
France restauration |
Carine Schmitt |
54, rue Royale |
Nantes |
44000 |
France |
27 |
Franchi S.p.A. |
Paolo Accorti |
Via Monte Bianco 34 |
Torino |
10100 |
Italy |
28 |
Furia Bacalhau e Frutos do Mar |
Lino Rodriguez |
Jardim das rosas n. 32 |
Lisboa |
1675 |
Portugal |
29 |
Galería del gastrónomo |
Eduardo Saavedra |
Rambla de Cataluña, 23 |
Barcelona |
08022 |
Spain |
30 |
Godos Cocina Típica |
José Pedro Freyre |
C/ Romero, 33 |
Sevilla |
41101 |
Spain |
31 |
Gourmet Lanchonetes |
André Fonseca |
Av. Brasil, 442 |
Campinas |
04876-786 |
Brazil |
32 |
Great Lakes Food Market |
Howard Snyder |
2732 Baker Blvd. |
Eugene |
97403 |
USA |
33 |
GROSELLA-Restaurante |
Manuel Pereira |
5ª Ave. Los Palos Grandes |
Caracas |
1081 |
Venezuela |
34 |
Hanari Carnes |
Mario Pontes |
Rua do Paço, 67 |
Rio de Janeiro |
05454-876 |
Brazil |
35 |
HILARIÓN-Abastos |
Carlos Hernández |
Carrera 22 con Ave. Carlos Soublette #8-35 |
San Cristóbal |
5022 |
Venezuela |
36 |
Hungry Coyote Import Store |
Yoshi Latimer |
City Center Plaza 516 Main St. |
Elgin |
97827 |
USA |
37 |
Hungry Owl All-Night Grocers |
Patricia McKenna |
8 Johnstown Road |
Cork |
|
Ireland |
38 |
Island Trading |
Helen Bennett |
Garden House Crowther Way |
Cowes |
PO31 7PJ |
UK |
39 |
Königlich Essen |
Philip Cramer |
Maubelstr. 90 |
Brandenburg |
14776 |
Germany |
40 |
La corne d'abondance |
Daniel Tonini |
67, avenue de l'Europe |
Versailles |
78000 |
France |
41 |
La maison d'Asie |
Annette Roulet |
1 rue Alsace-Lorraine |
Toulouse |
31000 |
France |
42 |
Laughing Bacchus Wine Cellars |
Yoshi Tannamuri |
1900 Oak St. |
Vancouver |
V3F 2K1 |
Canada |
43 |
Lazy K Kountry Store |
John Steel |
12 Orchestra Terrace |
Walla Walla |
99362 |
USA |
44 |
Lehmanns Marktstand |
Renate Messner |
Magazinweg 7 |
Frankfurt a.M. |
60528 |
Germany |
45 |
Let's Stop N Shop |
Jaime Yorres |
87 Polk St. Suite 5 |
San Francisco |
94117 |
USA |
46 |
LILA-Supermercado |
Carlos González |
Carrera 52 con Ave. Bolívar #65-98 Llano Largo |
Barquisimeto |
3508 |
Venezuela |
47 |
LINO-Delicateses |
Felipe Izquierdo |
Ave. 5 de Mayo Porlamar |
I. de Margarita |
4980 |
Venezuela |
48 |
Lonesome Pine Restaurant |
Fran Wilson |
89 Chiaroscuro Rd. |
Portland |
97219 |
USA |
49 |
Magazzini Alimentari Riuniti |
Giovanni Rovelli |
Via Ludovico il Moro 22 |
Bergamo |
24100 |
Italy |
50 |
Maison Dewey |
Catherine Dewey |
Rue Joseph-Bens 532 |
Bruxelles |
B-1180 |
Belgium |
51 |
Mère Paillarde |
Jean Fresnière |
43 rue St. Laurent |
Montréal |
H1J 1C3 |
Canada |
52 |
Morgenstern Gesundkost |
Alexander Feuer |
Heerstr. 22 |
Leipzig |
04179 |
Germany |
53 |
North/South |
Simon Crowther |
South House 300 Queensbridge |
London |
SW7 1RZ |
UK |
54 |
Océano Atlántico Ltda. |
Yvonne Moncada |
Ing. Gustavo Moncada 8585 Piso 20-A |
Buenos Aires |
1010 |
Argentina |
55 |
Old World Delicatessen |
Rene Phillips |
2743 Bering St. |
Anchorage |
99508 |
USA |
56 |
Ottilies Käseladen |
Henriette Pfalzheim |
Mehrheimerstr. 369 |
Köln |
50739 |
Germany |
57 |
Paris spécialités |
Marie Bertrand |
265, boulevard Charonne |
Paris |
75012 |
France |
58 |
Pericles Comidas clásicas |
Guillermo Fernández |
Calle Dr. Jorge Cash 321 |
México D.F. |
05033 |
Mexico |
59 |
Piccolo und mehr |
Georg Pipps |
Geislweg 14 |
Salzburg |
5020 |
Austria |
60 |
Princesa Isabel Vinhoss |
Isabel de Castro |
Estrada da saúde n. 58 |
Lisboa |
1756 |
Portugal |
61 |
Que Delícia |
Bernardo Batista |
Rua da Panificadora, 12 |
Rio de Janeiro |
02389-673 |
Brazil |
62 |
Queen Cozinha |
Lúcia Carvalho |
Alameda dos Canàrios, 891 |
São Paulo |
05487-020 |
Brazil |
63 |
QUICK-Stop |
Horst Kloss |
Taucherstraße 10 |
Cunewalde |
01307 |
Germany |
64 |
Rancho grande |
Sergio Gutiérrez |
Av. del Libertador 900 |
Buenos Aires |
1010 |
Argentina |
65 |
Rattlesnake Canyon Grocery |
Paula Wilson |
2817 Milton Dr. |
Albuquerque |
87110 |
USA |
66 |
Reggiani Caseifici |
Maurizio Moroni |
Strada Provinciale 124 |
Reggio Emilia |
42100 |
Italy |
67 |
Ricardo Adocicados |
Janete Limeira |
Av. Copacabana, 267 |
Rio de Janeiro |
02389-890 |
Brazil |
68 |
Richter Supermarkt |
Michael Holz |
Grenzacherweg 237 |
Genève |
1203 |
Switzerland |
69 |
Romero y tomillo |
Alejandra Camino |
Gran Vía, 1 |
Madrid |
28001 |
Spain |
70 |
Santé Gourmet |
Jonas Bergulfsen |
Erling Skakkes gate 78 |
Stavern |
4110 |
Norway |
71 |
Save-a-lot Markets |
Jose Pavarotti |
187 Suffolk Ln. |
Boise |
83720 |
USA |
72 |
Seven Seas Imports |
Hari Kumar |
90 Wadhurst Rd. |
London |
OX15 4NB |
UK |
73 |
Simons bistro |
Jytte Petersen |
Vinbæltet 34 |
København |
1734 |
Denmark |
74 |
Spécialités du monde |
Dominique Perrier |
25, rue Lauriston |
Paris |
75016 |
France |
75 |
Split Rail Beer & Ale |
Art Braunschweiger |
P.O. Box 555 |
Lander |
82520 |
USA |
76 |
Suprêmes délices |
Pascale Cartrain |
Boulevard Tirou, 255 |
Charleroi |
B-6000 |
Belgium |
77 |
The Big Cheese |
Liz Nixon |
89 Jefferson Way Suite 2 |
Portland |
97201 |
USA |
78 |
The Cracker Box |
Liu Wong |
55 Grizzly Peak Rd. |
Butte |
59801 |
USA |
79 |
Toms Spezialitäten |
Karin Josephs |
Luisenstr. 48 |
Münster |
44087 |
Germany |
80 |
Tortuga Restaurante |
Miguel Angel Paolino |
Avda. Azteca 123 |
México D.F. |
05033 |
Mexico |
81 |
Tradição Hipermercados |
Anabela Domingues |
Av. Inês de Castro, 414 |
São Paulo |
05634-030 |
Brazil |
82 |
Trail's Head Gourmet Provisioners |
Helvetius Nagy |
722 DaVinci Blvd. |
Kirkland |
98034 |
USA |
83 |
Vaffeljernet |
Palle Ibsen |
Smagsløget 45 |
Århus |
8200 |
Denmark |
84 |
Victuailles en stock |
Mary Saveley |
2, rue du Commerce |
Lyon |
69004 |
France |
85 |
Vins et alcools Chevalier |
Paul Henriot |
59 rue de l'Abbaye |
Reims |
51100 |
France |
86 |
Die Wandernde Kuh |
Rita Müller |
Adenauerallee 900 |
Stuttgart |
70563 |
Germany |
87 |
Wartian Herkku |
Pirkko Koskitalo |
Torikatu 38 |
Oulu |
90110 |
Finland |
88 |
Wellington Importadora |
Paula Parente |
Rua do Mercado, 12 |
Resende |
08737-363 |
Brazil |
89 |
White Clover Markets |
Karl Jablonski |
305 - 14th Ave. S. Suite 3B |
Seattle |
98128 |
USA |
90 |
Wilman Kala |
Matti Karttunen |
Keskuskatu 45 |
Helsinki |
21240 |
Finland |
91 |
Wolski |
Zbyszek |
ul. Filtrowa 68 |
Walla |
01-012 |
Poland |
AND Example
The following SQL statement selects all fields from "Customers" where country is "Germany" AND city is "Berlin":
Example
SELECT * FROM Customers WHERE Country = 'Germany' AND City = 'Berlin';
OR Example
The following SQL statement selects all fields from "Customers" where city is "Berlin" OR "Stuttgart":
Example
SELECT * FROM Customers WHERE City = 'Berlin' OR City = 'Stuttgart';
The following SQL statement selects all fields from "Customers" where country is "Germany" OR "Spain":
Example
SELECT * FROM Customers WHERE Country = 'Germany' OR Country = 'Spain';
NOT Example
The following SQL statement selects all fields from "Customers" where country is NOT "Germany":
Example
SELECT * FROM Customers WHERE NOT Country = 'Germany';
Combining AND, OR and NOT
You can also combine the AND ,
OR and NOT operators.
The following SQL statement selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "Stuttgart" (use parenthesis to form complex expressions):
Example
SELECT * FROM Customers WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart');
The following SQL statement selects all fields from "Customers" where country is NOT "Germany" and NOT "USA":
Example
SELECT * FROM Customers WHERE NOT Country = 'Germany' AND NOT Country = 'USA';
The MySQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the
DESC keyword.
ORDER BY Syntax
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1
|
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4
|
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Luleå |
S-958 22 |
Sweden |
ORDER BY Example
The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" column:
Example
SELECT * FROM Customers ORDER BY Country;
ORDER BY DESC Example
The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:
Example
SELECT * FROM Customers ORDER BY Country DESC;
ORDER BY Several Columns Example
The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:
Example
SELECT * FROM Customers ORDER BY Country, CustomerName;
ORDER BY Several Columns Example 2
The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column:
Example
SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
The MySQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two ways:
1. Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the
INSERT INTO syntax would be as follows:
INSERT INTO table_name VALUES (value1, value2, value3, ...);
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
89 |
White Clover Markets |
Karl Jablonski |
305 - 14th Ave. S. Suite 3B |
Seattle |
98128 |
USA |
90
|
Wilman Kala |
Matti Karttunen |
Keskuskatu 45 |
Helsinki |
21240 |
Finland |
91
|
Wolski |
Zbyszek |
ul. Filtrowa 68 |
Walla |
01-012 |
Poland |
INSERT INTO Example
The following SQL statement inserts a new record in the "Customers" table:
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
The selection from the "Customers" table will now look like this:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
89 |
White Clover Markets |
Karl Jablonski |
305 - 14th Ave. S. Suite 3B |
Seattle |
98128 |
USA |
90
|
Wilman Kala |
Matti Karttunen |
Keskuskatu 45 |
Helsinki |
21240 |
Finland |
91
|
Wolski |
Zbyszek |
ul. Filtrowa 68 |
Walla |
01-012 |
Poland |
92 |
Cardinal |
Tom B. Erichsen |
Skagen 21 |
Stavanger |
4006 |
Norway |
Did you notice that we did not insert any number into the CustomerID field? The CustomerID column is an field and will be generated automatically when a new record is inserted into the table.
Insert Data Only in Specified Columns
It is also possible to only insert data in specific columns.
The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):
Example
INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');
The selection from the "Customers" table will now look like this:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
89 |
White Clover Markets |
Karl Jablonski |
305 - 14th Ave. S. Suite 3B |
Seattle |
98128 |
USA |
90
|
Wilman Kala |
Matti Karttunen |
Keskuskatu 45 |
Helsinki |
21240 |
Finland |
91
|
Wolski |
Zbyszek |
ul. Filtrowa 68 |
Walla |
01-012 |
Poland |
92 |
Cardinal |
null |
null |
Stavanger |
null |
Norway |
MySQL NULL Values
What is a NULL Value?
A field with a NULL value is a field with no value.
If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!
How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and
IS NOT NULL operators instead.
IS NULL Syntax
SELECT column_names FROM table_name WHERE column_name IS NULL;
IS NOT NULL Syntax
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1
|
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4
|
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Luleå |
S-958 22 |
Sweden |
The IS NULL Operator
The IS NULL operator is used to test for empty values (NULL values).
The following SQL lists all customers with a NULL value in the "Address" field:
Example
SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NULL;
Tip: Always use IS NULL to look for NULL values.
The IS NOT NULL Operator
The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).
The following SQL lists all customers with a value in the "Address" field:
Example
SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NOT NULL;
The MySQL UPDATE Statement
The UPDATE statement is used to modify the existing records in a table.
UPDATE Syntax
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
CustomerID
|
CustomerName
|
ContactName
|
Address
|
City
|
PostalCode
|
Country
|
1
|
Alfreds Futterkiste
|
Maria Anders
|
Obere Str. 57
|
Berlin
|
12209
|
Germany
|
2
|
Ana Trujillo Emparedados y helados
|
Ana Trujillo
|
Avda. de la Constitución 2222
|
México D.F.
|
05021
|
Mexico
|
3
|
Antonio Moreno Taquería
|
Antonio Moreno
|
Mataderos 2312
|
México D.F.
|
05023
|
Mexico
|
4
|
Around the Horn
|
Thomas Hardy
|
120 Hanover Sq.
|
London
|
WA1 1DP
|
UK
|
UPDATE Table
The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.
Example
UPDATE Customers SET ContactName = 'Alfred Schmidt', City = 'Frankfurt' WHERE CustomerID = 1;
The selection from the "Customers" table will now look like this:
CustomerID
|
CustomerName
|
ContactName
|
Address
|
City
|
PostalCode
|
Country
|
1
|
Alfreds Futterkiste
|
Alfred Schmidt
|
Obere Str. 57
|
Frankfurt
|
12209
|
Germany
|
2
|
Ana Trujillo Emparedados y helados
|
Ana Trujillo
|
Avda. de la Constitución 2222
|
México D.F.
|
05021
|
Mexico
|
3
|
Antonio Moreno Taquería
|
Antonio Moreno
|
Mataderos 2312
|
México D.F.
|
05023
|
Mexico
|
4
|
Around the Horn
|
Thomas Hardy
|
120 Hanover Sq.
|
London
|
WA1 1DP
|
UK
|
UPDATE Multiple Records
It is the WHERE clause that determines how many records will be updated.
The following SQL statement will update the PostalCode to 00000 for all records where country is "Mexico":
Example
UPDATE Customers SET PostalCode = 00000 WHERE Country = 'Mexico';
The selection from the "Customers" table will now look like this:
CustomerID
|
CustomerName
|
ContactName
|
Address
|
City
|
PostalCode
|
Country
|
1
|
Alfreds Futterkiste
|
Alfred Schmidt
|
Obere Str. 57
|
Frankfurt
|
12209
|
Germany
|
2
|
Ana Trujillo Emparedados y helados
|
Ana Trujillo
|
Avda. de la Constitución 2222
|
México D.F.
|
00000
|
Mexico
|
3
|
Antonio Moreno Taquería
|
The MySQL DELETE Statement
The DELETE statement is used to delete existing records in a table.
DELETE Syntax
DELETE FROM table_name WHERE condition;
Note: Be careful when deleting records in a table! Notice the
WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1
|
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4
|
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Luleå |
S-958 22 |
Sweden |
SQL DELETE Example
The following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table:
Example
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
The "Customers" table will now look like this:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4
|
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Luleå |
S-958 22 |
Sweden |
Delete All Records
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:
The following SQL statement deletes all rows in the "Customers" table, without deleting the table:
Example
DELETE FROM Customers;
The MySQL LIMIT Clause
The LIMIT clause is used to specify the number of records to return.
The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
LIMIT Syntax
SELECT column_name(s) FROM table_name WHERE condition LIMIT number;
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
CustomerID
|
CustomerName
|
ContactName
|
Address
|
City
|
PostalCode
|
Country
|
1
|
Alfreds Futterkiste
|
Maria Anders
|
Obere Str. 57
|
Berlin
|
12209
|
Germany
|
2
|
Ana Trujillo Emparedados y helados
|
Ana Trujillo
|
Avda. de la Constitución 2222
|
México D.F.
|
05021
|
Mexico
|
3
|
Antonio Moreno Taquería
|
Antonio Moreno
|
Mataderos 2312
|
México D.F.
|
05023
|
Mexico
|
4
|
Around the Horn
|
Thomas Hardy
|
120 Hanover Sq.
|
London
|
WA1 1DP
|
UK
|
5
|
Berglunds snabbköp
|
Christina Berglund
|
Berguvsvägen 8
|
Luleå
|
S-958 22
|
Sweden
|
MySQL LIMIT Examples
The following SQL statement selects the first three records from the "Customers" table:
ExampleGet your own SQL Server
SELECT * FROM Customers LIMIT 3;
-
What if we want to select records 4 - 6 (inclusive)?
MySQL provides a way to handle this: by using OFFSET.
The SQL query below says "return only 3 records, start on record 4 (OFFSET 3)":
Example
SELECT * FROM Customers LIMIT 3 OFFSET 3;
-
ADD a WHERE CLAUSE
The following SQL statement selects the first three records from the "Customers" table, where the country is "Germany":
Example
SELECT * FROM Customers WHERE Country='Germany' LIMIT 3;
MySQL MIN() and MAX() Functions
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
MIN() Syntax
SELECT MIN(column_name) FROM table_name WHERE condition;
MAX() Syntax
SELECT MAX(column_name) FROM table_name WHERE condition;
Demo Database
Below is a selection from the "Products" table in the Northwind sample database:
ProductID |
ProductName |
SupplierID |
CategoryID |
Unit |
Price |
1 |
Chais |
1 |
1 |
10 boxes x 20 bags |
18 |
2 |
Chang |
1 |
1 |
24 - 12 oz bottles |
19 |
3 |
Aniseed Syrup |
1 |
2 |
12 - 550 ml bottles |
10 |
4 |
Chef Anton's Cajun Seasoning |
2 |
2 |
48 - 6 oz jars |
22 |
5 |
Chef Anton's Gumbo Mix |
2 |
2 |
36 boxes |
21.35 |
MIN() Example
The following SQL statement finds the price of the cheapest product:
Example
SELECT MIN(Price) AS SmallestPrice FROM Products;
MAX() Example
The following SQL statement finds the price of the most expensive product:
Example
SELECT MAX(Price) AS LargestPrice FROM Products;
MySQL COUNT(), AVG() and SUM() Functions
The COUNT() function returns the number of rows that matches a specified criterion.
COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
The AVG() function returns the average value of a numeric column.Â
AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;
The SUM() function returns the total sum of a numeric column.Â
SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;
Demo Database
Below is a selection from the "Products" table in the Northwind sample database:
ProductID |
ProductName |
SupplierID |
CategoryID |
Unit |
Price |
1 |
Chais |
1 |
1 |
10 boxes x 20 bags |
18 |
2 |
Chang |
1 |
1 |
24 - 12 oz bottles |
19 |
3 |
Aniseed Syrup |
1 |
2 |
12 - 550 ml bottles |
10 |
4 |
Chef Anton's Cajun Seasoning |
2 |
2 |
48 - 6 oz jars |
22 |
5 |
Chef Anton's Gumbo Mix |
2 |
2 |
36 boxes |
21.35 |
COUNT() Example
The following SQL statement finds the number of products:
Example
SELECT COUNT(ProductID) FROM Products;
Note: NULL values are not counted.
AVG() Example
The following SQL statement finds the average price of all products:
Example
SELECT AVG(Price) FROM Products;
Note: NULL values are ignored.
Demo Database
Below is a selection from the "OrderDetails" table in the Northwind
sample database:
OrderDetailID |
OrderID |
ProductID |
Quantity |
1 |
10248 |
11 |
12 |
2 |
10248 |
42 |
10 |
3 |
10248 |
72 |
5 |
4 |
10249 |
14 |
9 |
5 |
10249 |
51 |
40 |
SUM() Example
The following SQL statement finds the sum of the "Quantity" fields
in the "OrderDetails" table:
Example
SELECT SUM(Quantity) FROM OrderDetails;
Note: NULL values are ignored.
The MySQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
- The percent sign (%) represents zero, one, or multiple characters
- The underscore sign (_) represents one, single character
The percent sign and the underscore can also be used in combinations!
LIKE Syntax
SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;
Tip: You can also combine any number of conditions using AND or OR operators.
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
LIKE Operator
|
Description
|
WHERE CustomerName LIKE 'a%'
|
Finds any values that start with "a"
|
WHERE CustomerName LIKE '%a'
|
Finds any values that end with "a"
|
WHERE CustomerName LIKE '%or%'
|
Finds any values that have "or" in any position
|
WHERE CustomerName LIKE '_r%'
|
Finds any values that have "r" in the second position
|
WHERE CustomerName LIKE 'a_%'
|
Finds any values that start with "a" and are at least 2 characters in length
|
WHERE CustomerName LIKE 'a__%'
|
Finds any values that start with "a" and are at least 3 characters in length
|
WHERE ContactName LIKE 'a%o'
|
Finds any values that start with "a" and ends with "o"
|
Demo Database
The table below shows the complete "Customers" table from the Northwind sample database:
CustomerID
|
CustomerName
|
ContactName
|
Address
|
City
|
PostalCode
|
Country
|
1
|
Alfreds Futterkiste
|
Maria Anders
|
Obere Str. 57
|
Berlin
|
12209
|
Germany
|
2
|
Ana Trujillo Emparedados y helados
|
Ana Trujillo
|
Avda. de la Constitución 2222
|
México D.F.
|
05021
|
Mexico
|
3
|
Antonio Moreno Taquería
|
Antonio Moreno
|
Mataderos 2312
|
México D.F.
|
05023
|
Mexico
|
4
|
Around the Horn
|
Thomas Hardy
|
120 Hanover Sq.
|
London
|
WA1 1DP
|
UK
|
5
|
Berglunds snabbköp
|
Christina Berglund
|
Berguvsvägen 8
|
Luleå
|
S-958 22
|
Sweden
|
6
|
Blauer See Delikatessen
|
Hanna Moos
|
Forsterstr. 57
|
Mannheim
|
68306
|
Germany
|
7
|
Blondel père et fils
|
Frédérique Citeaux
|
24, place Kléber
|
Strasbourg
|
67000
|
France
|
8
|
Bólido Comidas preparadas
|
Martín Sommer
|
C/ Araquil, 67
|
Madrid
|
28023
|
Spain
|
9
|
Bon app'
|
Laurence Lebihans
|
12, rue des Bouchers
|
Marseille
|
13008
|
France
|
10
|
Bottom-Dollar Marketse
|
Elizabeth Lincoln
|
23 Tsawassen Blvd.
|
Tsawassen
|
T2F 8M4
|
Canada
|
11
|
B's Beverages
|
Victoria Ashworth
|
Fauntleroy Circus
|
London
|
EC2 5NT
|
UK
|
12
|
Cactus Comidas para llevar
|
Patricio Simpson
|
Cerrito 333
|
Buenos Aires
|
1010
|
Argentina
|
13
|
Centro comercial Moctezuma
|
Francisco Chang
|
Sierras de Granada 9993
|
México D.F.
|
MySQL Wildcards
MySQL Wildcard Characters
A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the
operator. The LIKE operator is used in a
WHERE clause to search for a specified pattern in a column.
Wildcard Characters in MySQL
Symbol |
Description |
Example |
% |
Represents zero or more characters |
bl% finds bl, black, blue, and blob |
_ |
Represents a single character |
h_t finds hot, hat, and hit |
The wildcards can also be used in combinations!
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
LIKE Operator |
Description |
WHERE CustomerName LIKE 'a%' |
Finds any values that starts with "a" |
WHERE CustomerName LIKE '%a' |
Finds any values that ends with "a" |
WHERE CustomerName LIKE '%or%' |
Finds any values that have "or" in any position |
WHERE CustomerName LIKE '_r%' |
Finds any values that have "r" in the second position |
WHERE CustomerName LIKE 'a_%_%' |
Finds any values that starts with "a" and are at least 3 characters in length |
WHERE ContactName LIKE 'a%o' |
Finds any values that starts with "a" and ends with "o" |
Demo Database
The table below shows the complete "Customers" table from the Northwind sample database:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1 |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno TaquerÃa |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4 |
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Luleå |
S-958 22 |
Sweden |
6 |
Blauer See Delikatessen |
Hanna Moos |
Forsterstr. 57 |
Mannheim |
68306 |
Germany |
7 |
Blondel père et fils |
Frédérique Citeaux |
24, place Kléber |
Strasbourg |
67000 |
France |
8 |
Bólido Comidas preparadas |
MartÃn Sommer |
C/ Araquil, 67 |
Madrid |
28023 |
Spain |
9 |
Bon app' |
Laurence Lebihans |
12, rue des Bouchers |
Marseille |
13008 |
France |
10 |
Bottom-Dollar Marketse |
Elizabeth Lincoln |
23 Tsawassen Blvd. |
Tsawassen |
T2F 8M4 |
Canada |
11 |
B's Beverages |
Victoria Ashworth |
Fauntleroy Circus |
London |
EC2 5NT |
UK |
12 |
Cactus Comidas para llevar |
Patricio Simpson |
Cerrito 333 |
Buenos Aires |
1010 |
Argentina |
13 |
Centro comercial Moctezuma |
Francisco Chang |
Sierras de Granada 9993 |
México D.F. |
05022 |
Mexico |
14 |
Chop-suey Chinese |
Yang Wang |
Hauptstr. 29 |
Bern |
3012 |
Switzerland |
15 |
Comércio Mineiro |
Pedro Afonso |
Av. dos LusÃadas, 23 |
São Paulo |
05432-043 |
Brazil |
16 |
Consolidated Holdings |
Elizabeth Brown |
Berkeley Gardens 12 Brewery |
London |
WX1 6LT |
UK |
17 |
Drachenblut Delikatessend |
Sven Ottlieb |
Walserweg 21 |
Aachen |
52066 |
Germany |
18 |
Du monde entier |
Janine Labrune |
67, rue des Cinquante Otages |
Nantes |
44000 |
France |
19 |
Eastern Connection |
Ann Devon |
35 King George |
London |
WX3 6FW |
UK |
20 |
Ernst Handel |
Roland Mendel |
Kirchgasse 6 |
Graz |
8010 |
Austria |
21 |
Familia Arquibaldo |
Aria Cruz |
Rua Orós, 92 |
São Paulo |
05442-030 |
Brazil |
22 |
FISSA Fabrica Inter. Salchichas S.A. |
Diego Roel |
C/ Moralzarzal, 86 |
Madrid |
28034 |
Spain |
23 |
Folies gourmandes |
Martine Rancé |
184, chaussée de Tournai |
Lille |
59000 |
France |
24 |
Folk och fä HB |
Maria Larsson |
Ã…kergatan 24 |
Bräcke |
S-844 67 |
Sweden |
25 |
Frankenversand |
Peter Franken |
Berliner Platz 43 |
München |
80805 |
Germany |
26 |
France restauration |
Carine Schmitt |
54, rue Royale |
Nantes |
44000 |
France |
27 |
Franchi S.p.A. |
Paolo Accorti |
Via Monte Bianco 34 |
Torino |
10100 |
Italy |
28 |
Furia Bacalhau e Frutos do Mar |
Lino Rodriguez |
Jardim das rosas n. 32 |
Lisboa |
1675 |
Portugal |
29 |
GalerÃa del gastrónomo |
Eduardo Saavedra |
Rambla de Cataluña, 23 |
Barcelona |
08022 |
Spain |
30 |
Godos Cocina TÃpica |
José Pedro Freyre |
C/ Romero, 33 |
Sevilla |
41101 |
Spain |
31 |
Gourmet Lanchonetes |
André Fonseca |
Av. Brasil, 442 |
Campinas |
04876-786 |
Brazil |
32 |
Great Lakes Food Market |
Howard Snyder |
2732 Baker Blvd. |
Eugene |
97403 |
USA |
33 |
GROSELLA-Restaurante |
Manuel Pereira |
5ª Ave. Los Palos Grandes |
Caracas |
1081 |
Venezuela |
34 |
Hanari Carnes |
Mario Pontes |
Rua do Paço, 67 |
Rio de Janeiro |
05454-876 |
Brazil |
35 |
HILARIÓN-Abastos |
Carlos Hernández |
Carrera 22 con Ave. Carlos Soublette #8-35 |
San Cristóbal |
5022 |
Venezuela |
36 |
Hungry Coyote Import Store |
Yoshi Latimer |
City Center Plaza 516 Main St. |
Elgin |
97827 |
USA |
37 |
Hungry Owl All-Night Grocers |
Patricia McKenna |
8 Johnstown Road |
Cork |
|
Ireland |
38 |
Island Trading |
Helen Bennett |
Garden House Crowther Way |
Cowes |
PO31 7PJ |
UK |
39 |
Königlich Essen |
Philip Cramer |
Maubelstr. 90 |
Brandenburg |
14776 |
Germany |
40 |
La corne d'abondance |
Daniel Tonini |
67, avenue de l'Europe |
Versailles |
78000 |
France |
41 |
La maison d'Asie |
Annette Roulet |
1 rue Alsace-Lorraine |
Toulouse |
31000 |
France |
42 |
Laughing Bacchus Wine Cellars |
Yoshi Tannamuri |
1900 Oak St. |
Vancouver |
V3F 2K1 |
Canada |
43 |
Lazy K Kountry Store |
John Steel |
12 Orchestra Terrace |
Walla Walla |
99362 |
USA |
44 |
Lehmanns Marktstand |
Renate Messner |
Magazinweg 7 |
Frankfurt a.M. |
60528 |
Germany |
45 |
Let's Stop N Shop |
Jaime Yorres |
87 Polk St. Suite 5 |
San Francisco |
94117 |
USA |
46 |
LILA-Supermercado |
Carlos González |
Carrera 52 con Ave. BolÃvar #65-98 Llano Largo |
Barquisimeto |
3508 |
Venezuela |
47 |
LINO-Delicateses |
Felipe Izquierdo |
Ave. 5 de Mayo Porlamar |
I. de Margarita |
4980 |
Venezuela |
48 |
Lonesome Pine Restaurant |
Fran Wilson |
89 Chiaroscuro Rd. |
Portland |
97219 |
USA |
49 |
Magazzini Alimentari Riuniti |
Giovanni Rovelli |
Via Ludovico il Moro 22 |
Bergamo |
24100 |
Italy |
50 |
Maison Dewey |
Catherine Dewey |
Rue Joseph-Bens 532 |
Bruxelles |
B-1180 |
Belgium |
51 |
Mère Paillarde |
Jean Fresnière |
43 rue St. Laurent |
Montréal |
H1J 1C3 |
Canada |
52 |
Morgenstern Gesundkost |
Alexander Feuer |
Heerstr. 22 |
Leipzig |
04179 |
Germany |
53 |
North/South |
Simon Crowther |
South House 300 Queensbridge |
London |
SW7 1RZ |
UK |
54 |
Océano Atlántico Ltda. |
Yvonne Moncada |
Ing. Gustavo Moncada 8585 Piso 20-A |
Buenos Aires |
1010 |
Argentina |
55 |
Old World Delicatessen |
Rene Phillips |
2743 Bering St. |
Anchorage |
99508 |
USA |
56 |
Ottilies Käseladen |
Henriette Pfalzheim |
Mehrheimerstr. 369 |
Köln |
50739 |
Germany |
57 |
Paris spécialités |
Marie Bertrand |
265, boulevard Charonne |
Paris |
75012 |
France |
58 |
Pericles Comidas clásicas |
Guillermo Fernández |
Calle Dr. Jorge Cash 321 |
México D.F. |
05033 |
Mexico |
59 |
Piccolo und mehr |
Georg Pipps |
Geislweg 14 |
Salzburg |
5020 |
Austria |
60 |
Princesa Isabel Vinhoss |
Isabel de Castro |
Estrada da saúde n. 58 |
Lisboa |
1756 |
Portugal |
61 |
Que DelÃcia |
Bernardo Batista |
Rua da Panificadora, 12 |
Rio de Janeiro |
02389-673 |
Brazil |
62 |
Queen Cozinha |
Lúcia Carvalho |
Alameda dos Canà rios, 891 |
São Paulo |
05487-020 |
Brazil |
63 |
QUICK-Stop |
Horst Kloss |
Taucherstraße 10 |
Cunewalde |
01307 |
Germany |
64 |
Rancho grande |
Sergio Gutiérrez |
Av. del Libertador 900 |
Buenos Aires |
1010 |
Argentina |
65 |
Rattlesnake Canyon Grocery |
Paula Wilson |
2817 Milton Dr. |
Albuquerque |
87110 |
USA |
66 |
Reggiani Caseifici |
Maurizio Moroni |
Strada Provinciale 124 |
Reggio Emilia |
42100 |
Italy |
67 |
Ricardo Adocicados |
Janete Limeira |
Av. Copacabana, 267 |
Rio de Janeiro |
02389-890 |
Brazil |
68 |
Richter Supermarkt |
Michael Holz |
Grenzacherweg 237 |
Genève |
1203 |
Switzerland |
69 |
Romero y tomillo |
Alejandra Camino |
Gran VÃa, 1 |
Madrid |
28001 |
Spain |
70 |
Santé Gourmet |
Jonas Bergulfsen |
Erling Skakkes gate 78 |
Stavern |
4110 |
Norway |
71 |
Save-a-lot Markets |
Jose Pavarotti |
187 Suffolk Ln. |
Boise |
83720 |
USA |
72 |
Seven Seas Imports |
Hari Kumar |
90 Wadhurst Rd. |
London |
OX15 4NB |
UK |
73 |
Simons bistro |
Jytte Petersen |
Vinbæltet 34 |
København |
1734 |
Denmark |
74 |
Spécialités du monde |
Dominique Perrier |
25, rue Lauriston |
Paris |
75016 |
France |
75 |
Split Rail Beer & Ale |
Art Braunschweiger |
P.O. Box 555 |
Lander |
82520 |
USA |
76 |
Suprêmes délices |
Pascale Cartrain |
Boulevard Tirou, 255 |
Charleroi |
B-6000 |
Belgium |
77 |
The Big Cheese |
Liz Nixon |
89 Jefferson Way Suite 2 |
Portland |
97201 |
USA |
78 |
The Cracker Box |
Liu Wong |
55 Grizzly Peak Rd. |
Butte |
59801 |
USA |
79 |
Toms Spezialitäten |
Karin Josephs |
Luisenstr. 48 |
Münster |
44087 |
Germany |
80 |
Tortuga Restaurante |
Miguel Angel Paolino |
Avda. Azteca 123 |
México D.F. |
05033 |
Mexico |
81 |
Tradição Hipermercados |
Anabela Domingues |
Av. Inês de Castro, 414 |
São Paulo |
05634-030 |
Brazil |
82 |
Trail's Head Gourmet Provisioners |
Helvetius Nagy |
722 DaVinci Blvd. |
Kirkland |
98034 |
USA |
83 |
Vaffeljernet |
Palle Ibsen |
Smagsløget 45 |
Ã…rhus |
8200 |
Denmark |
84 |
Victuailles en stock |
Mary Saveley |
2, rue du Commerce |
Lyon |
69004 |
France |
85 |
Vins et alcools Chevalier |
Paul Henriot |
59 rue de l'Abbaye |
Reims |
51100 |
France |
86 |
Die Wandernde Kuh |
Rita Müller |
Adenauerallee 900 |
Stuttgart |
70563 |
Germany |
87 |
Wartian Herkku |
Pirkko Koskitalo |
Torikatu 38 |
Oulu |
90110 |
Finland |
88 |
Wellington Importadora |
Paula Parente |
Rua do Mercado, 12 |
Resende |
08737-363 |
Brazil |
89 |
White Clover Markets |
Karl Jablonski |
305 - 14th Ave. S. Suite 3B |
Seattle |
98128 |
USA |
90 |
Wilman Kala |
Matti Karttunen |
Keskuskatu 45 |
Helsinki |
21240 |
Finland |
91 |
Wolski |
Zbyszek |
ul. Filtrowa 68 |
Walla |
01-012 |
Poland |
Using the % Wildcard
The following SQL statement selects all customers with a City starting with "ber":
Example
SELECT * FROM Customers
WHERE City LIKE 'ber%';
The following SQL statement selects all customers with a City containing the
pattern "es":Â
Example
SELECT * FROM Customers
WHERE City LIKE '%es%';
Using the _ Wildcard
The following SQL statement selects all customers with a City starting with any character, followed by "ondon":
Example
SELECT * FROM Customers
WHERE City LIKE '_ondon';
The following SQL statement selects all customers with a City starting with
"L", followed by any character, followed by "n", followed by any character, followed by "on":
Example
SELECT * FROM Customers
WHERE City LIKE 'L_n_on';
The MySQL IN Operator
The IN operator allows you to specify multiple values in a
WHERE clause.
The IN operator is a shorthand for multiple
OR conditions.
IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
or:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
Demo Database
The table below shows the complete "Customers" table from the Northwind sample database:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1 |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno TaquerÃa |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4 |
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Luleå |
S-958 22 |
Sweden |
6 |
Blauer See Delikatessen |
Hanna Moos |
Forsterstr. 57 |
Mannheim |
68306 |
Germany |
7 |
Blondel père et fils |
Frédérique Citeaux |
24, place Kléber |
Strasbourg |
67000 |
France |
8 |
Bólido Comidas preparadas |
MartÃn Sommer |
C/ Araquil, 67 |
Madrid |
28023 |
Spain |
9 |
Bon app' |
Laurence Lebihans |
12, rue des Bouchers |
Marseille |
13008 |
France |
10 |
Bottom-Dollar Marketse |
Elizabeth Lincoln |
23 Tsawassen Blvd. |
Tsawassen |
T2F 8M4 |
Canada |
11 |
B's Beverages |
Victoria Ashworth |
Fauntleroy Circus |
London |
EC2 5NT |
UK |
12 |
Cactus Comidas para llevar |
Patricio Simpson |
Cerrito 333 |
Buenos Aires |
1010 |
Argentina |
13 |
Centro comercial Moctezuma |
Francisco Chang |
Sierras de Granada 9993 |
México D.F. |
05022 |
Mexico |
14 |
Chop-suey Chinese |
Yang Wang |
Hauptstr. 29 |
Bern |
3012 |
Switzerland |
15 |
Comércio Mineiro |
Pedro Afonso |
Av. dos LusÃadas, 23 |
São Paulo |
05432-043 |
Brazil |
16 |
Consolidated Holdings |
Elizabeth Brown |
Berkeley Gardens 12 Brewery |
London |
WX1 6LT |
UK |
17 |
Drachenblut Delikatessend |
Sven Ottlieb |
Walserweg 21 |
Aachen |
52066 |
Germany |
18 |
Du monde entier |
Janine Labrune |
67, rue des Cinquante Otages |
Nantes |
44000 |
France |
19 |
Eastern Connection |
Ann Devon |
35 King George |
London |
WX3 6FW |
UK |
20 |
Ernst Handel |
Roland Mendel |
Kirchgasse 6 |
Graz |
8010 |
Austria |
21 |
Familia Arquibaldo |
Aria Cruz |
Rua Orós, 92 |
São Paulo |
05442-030 |
Brazil |
22 |
FISSA Fabrica Inter. Salchichas S.A. |
Diego Roel |
C/ Moralzarzal, 86 |
Madrid |
28034 |
Spain |
23 |
Folies gourmandes |
Martine Rancé |
184, chaussée de Tournai |
Lille |
59000 |
France |
24 |
Folk och fä HB |
Maria Larsson |
Ã…kergatan 24 |
Bräcke |
S-844 67 |
Sweden |
25 |
Frankenversand |
Peter Franken |
Berliner Platz 43 |
München |
80805 |
Germany |
26 |
France restauration |
Carine Schmitt |
54, rue Royale |
Nantes |
44000 |
France |
27 |
Franchi S.p.A. |
Paolo Accorti |
Via Monte Bianco 34 |
Torino |
10100 |
Italy |
28 |
Furia Bacalhau e Frutos do Mar |
Lino Rodriguez |
Jardim das rosas n. 32 |
Lisboa |
1675 |
Portugal |
29 |
GalerÃa del gastrónomo |
Eduardo Saavedra |
Rambla de Cataluña, 23 |
Barcelona |
08022 |
Spain |
30 |
Godos Cocina TÃpica |
José Pedro Freyre |
C/ Romero, 33 |
Sevilla |
41101 |
Spain |
31 |
Gourmet Lanchonetes |
André Fonseca |
Av. Brasil, 442 |
Campinas |
04876-786 |
Brazil |
32 |
Great Lakes Food Market |
Howard Snyder |
2732 Baker Blvd. |
Eugene |
97403 |
USA |
33 |
GROSELLA-Restaurante |
Manuel Pereira |
5ª Ave. Los Palos Grandes |
Caracas |
1081 |
Venezuela |
34 |
Hanari Carnes |
Mario Pontes |
Rua do Paço, 67 |
Rio de Janeiro |
05454-876 |
Brazil |
35 |
HILARIÓN-Abastos |
Carlos Hernández |
Carrera 22 con Ave. Carlos Soublette #8-35 |
San Cristóbal |
5022 |
Venezuela |
36 |
Hungry Coyote Import Store |
Yoshi Latimer |
City Center Plaza 516 Main St. |
Elgin |
97827 |
USA |
37 |
Hungry Owl All-Night Grocers |
Patricia McKenna |
8 Johnstown Road |
Cork |
|
Ireland |
38 |
Island Trading |
Helen Bennett |
Garden House Crowther Way |
Cowes |
PO31 7PJ |
UK |
39 |
Königlich Essen |
Philip Cramer |
Maubelstr. 90 |
Brandenburg |
14776 |
Germany |
40 |
La corne d'abondance |
Daniel Tonini |
67, avenue de l'Europe |
Versailles |
78000 |
France |
41 |
La maison d'Asie |
Annette Roulet |
1 rue Alsace-Lorraine |
Toulouse |
31000 |
France |
42 |
Laughing Bacchus Wine Cellars |
Yoshi Tannamuri |
1900 Oak St. |
Vancouver |
V3F 2K1 |
Canada |
43 |
Lazy K Kountry Store |
John Steel |
12 Orchestra Terrace |
Walla Walla |
99362 |
USA |
44 |
Lehmanns Marktstand |
Renate Messner |
Magazinweg 7 |
Frankfurt a.M. |
60528 |
Germany |
45 |
Let's Stop N Shop |
Jaime Yorres |
87 Polk St. Suite 5 |
San Francisco |
94117 |
USA |
46 |
LILA-Supermercado |
Carlos González |
Carrera 52 con Ave. BolÃvar #65-98 Llano Largo |
Barquisimeto |
3508 |
Venezuela |
47 |
LINO-Delicateses |
Felipe Izquierdo |
Ave. 5 de Mayo Porlamar |
I. de Margarita |
4980 |
Venezuela |
48 |
Lonesome Pine Restaurant |
Fran Wilson |
89 Chiaroscuro Rd. |
Portland |
97219 |
USA |
49 |
Magazzini Alimentari Riuniti |
Giovanni Rovelli |
Via Ludovico il Moro 22 |
Bergamo |
24100 |
Italy |
50 |
Maison Dewey |
Catherine Dewey |
Rue Joseph-Bens 532 |
Bruxelles |
B-1180 |
Belgium |
51 |
Mère Paillarde |
Jean Fresnière |
43 rue St. Laurent |
Montréal |
H1J 1C3 |
Canada |
52 |
Morgenstern Gesundkost |
Alexander Feuer |
Heerstr. 22 |
Leipzig |
04179 |
Germany |
53 |
North/South |
Simon Crowther |
South House 300 Queensbridge |
London |
SW7 1RZ |
UK |
54 |
Océano Atlántico Ltda. |
Yvonne Moncada |
Ing. Gustavo Moncada 8585 Piso 20-A |
Buenos Aires |
1010 |
Argentina |
55 |
Old World Delicatessen |
Rene Phillips |
2743 Bering St. |
Anchorage |
99508 |
USA |
56 |
Ottilies Käseladen |
Henriette Pfalzheim |
Mehrheimerstr. 369 |
Köln |
50739 |
Germany |
57 |
Paris spécialités |
Marie Bertrand |
265, boulevard Charonne |
Paris |
75012 |
France |
58 |
Pericles Comidas clásicas |
Guillermo Fernández |
Calle Dr. Jorge Cash 321 |
México D.F. |
05033 |
Mexico |
59 |
Piccolo und mehr |
Georg Pipps |
Geislweg 14 |
Salzburg |
5020 |
Austria |
60 |
Princesa Isabel Vinhoss |
Isabel de Castro |
Estrada da saúde n. 58 |
Lisboa |
1756 |
Portugal |
61 |
Que DelÃcia |
Bernardo Batista |
Rua da Panificadora, 12 |
Rio de Janeiro |
02389-673 |
Brazil |
62 |
Queen Cozinha |
Lúcia Carvalho |
Alameda dos Canà rios, 891 |
São Paulo |
05487-020 |
Brazil |
63 |
QUICK-Stop |
Horst Kloss |
Taucherstraße 10 |
Cunewalde |
01307 |
Germany |
64 |
Rancho grande |
Sergio Gutiérrez |
Av. del Libertador 900 |
Buenos Aires |
1010 |
Argentina |
65 |
Rattlesnake Canyon Grocery |
Paula Wilson |
2817 Milton Dr. |
Albuquerque |
87110 |
USA |
66 |
Reggiani Caseifici |
Maurizio Moroni |
Strada Provinciale 124 |
Reggio Emilia |
42100 |
Italy |
67 |
Ricardo Adocicados |
Janete Limeira |
Av. Copacabana, 267 |
Rio de Janeiro |
02389-890 |
Brazil |
68 |
Richter Supermarkt |
Michael Holz |
Grenzacherweg 237 |
Genève |
1203 |
Switzerland |
69 |
Romero y tomillo |
Alejandra Camino |
Gran VÃa, 1 |
Madrid |
28001 |
Spain |
70 |
Santé Gourmet |
Jonas Bergulfsen |
Erling Skakkes gate 78 |
Stavern |
4110 |
Norway |
71 |
Save-a-lot Markets |
Jose Pavarotti |
187 Suffolk Ln. |
Boise |
83720 |
USA |
72 |
Seven Seas Imports |
Hari Kumar |
90 Wadhurst Rd. |
London |
OX15 4NB |
UK |
73 |
Simons bistro |
Jytte Petersen |
Vinbæltet 34 |
København |
1734 |
Denmark |
74 |
Spécialités du monde |
Dominique Perrier |
25, rue Lauriston |
Paris |
75016 |
France |
75 |
Split Rail Beer & Ale |
Art Braunschweiger |
P.O. Box 555 |
Lander |
82520 |
USA |
76 |
Suprêmes délices |
Pascale Cartrain |
Boulevard Tirou, 255 |
Charleroi |
B-6000 |
Belgium |
77 |
The Big Cheese |
Liz Nixon |
89 Jefferson Way Suite 2 |
Portland |
97201 |
USA |
78 |
The Cracker Box |
Liu Wong |
55 Grizzly Peak Rd. |
Butte |
59801 |
USA |
79 |
Toms Spezialitäten |
Karin Josephs |
Luisenstr. 48 |
Münster |
44087 |
Germany |
80 |
Tortuga Restaurante |
Miguel Angel Paolino |
Avda. Azteca 123 |
México D.F. |
05033 |
Mexico |
81 |
Tradição Hipermercados |
Anabela Domingues |
Av. Inês de Castro, 414 |
São Paulo |
05634-030 |
Brazil |
82 |
Trail's Head Gourmet Provisioners |
Helvetius Nagy |
722 DaVinci Blvd. |
Kirkland |
98034 |
USA |
83 |
Vaffeljernet |
Palle Ibsen |
Smagsløget 45 |
Ã…rhus |
8200 |
Denmark |
84 |
Victuailles en stock |
Mary Saveley |
2, rue du Commerce |
Lyon |
69004 |
France |
85 |
Vins et alcools Chevalier |
Paul Henriot |
59 rue de l'Abbaye |
Reims |
51100 |
France |
86 |
Die Wandernde Kuh |
Rita Müller |
Adenauerallee 900 |
Stuttgart |
70563 |
Germany |
87 |
Wartian Herkku |
Pirkko Koskitalo |
Torikatu 38 |
Oulu |
90110 |
Finland |
88 |
Wellington Importadora |
Paula Parente |
Rua do Mercado, 12 |
Resende |
08737-363 |
Brazil |
89 |
White Clover Markets |
Karl Jablonski |
305 - 14th Ave. S. Suite 3B |
Seattle |
98128 |
USA |
90 |
Wilman Kala |
Matti Karttunen |
Keskuskatu 45 |
Helsinki |
21240 |
Finland |
91 |
Wolski |
Zbyszek |
ul. Filtrowa 68 |
Walla |
01-012 |
Poland |
IN Operator Examples
The following SQL statement selects all customers that are located in "Germany", "France"
or "UK":
Example
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
The following SQL statement selects all customers that are NOT located in "Germany", "France" or "UK":
Example
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
The following SQL statement selects all customers that are from the same
countries as the suppliers:
Example
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);
The MySQL BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Demo Database
Below is a selection from the "Products" table in the Northwind
sample database:
ProductID |
ProductName |
SupplierID |
CategoryID |
Unit |
Price |
1 |
Chais |
1 |
1 |
10 boxes x 20 bags |
18 |
2 |
Chang |
1 |
1 |
24 - 12 oz bottles |
19 |
3 |
Aniseed Syrup |
1 |
2 |
12 - 550 ml bottles |
10 |
4 |
Chef Anton's Cajun Seasoning |
1 |
2 |
48 - 6 oz jars |
22 |
5 |
Chef Anton's Gumbo Mix |
1 |
2 |
36 boxes |
21.35 |
BETWEEN Example
The following SQL statement selects all products with a price between 10 and 20:
Example
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
NOT BETWEEN Example
To display the products outside the range of the previous example, use
NOT BETWEEN :
Example
SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;
BETWEEN with IN Example
The following SQL statement selects all products with a price between 10 and
20. In addition; do not show products with a CategoryID of 1,2, or 3:
Example
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20 AND CategoryID NOT IN (1,2,3);
BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName between "Carnarvon
Tigers" and "Mozzarella di Giovanni":
Example
SELECT * FROM Products WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella
di Giovanni' ORDER BY ProductName;
The following SQL statement selects all products with a ProductName between "Carnarvon
Tigers" and "Chef Anton's Cajun Seasoning":
Example
SELECT * FROM Products WHERE ProductName BETWEEN "Carnarvon Tigers" AND
"Chef Anton's Cajun Seasoning" ORDER BY ProductName;
NOT BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName not
between "Carnarvon
Tigers" and "Mozzarella di Giovanni":
Example
SELECT * FROM Products WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella
di Giovanni' ORDER BY ProductName;
Sample Table
Below is a selection from the "Orders" table in the Northwind
sample database:
OrderID |
CustomerID |
EmployeeID |
OrderDate |
ShipperID |
10248 |
90 |
5 |
7/4/1996 |
3 |
10249 |
81 |
6 |
7/5/1996 |
1 |
10250 |
34 |
4 |
7/8/1996 |
2 |
10251 |
84 |
3 |
7/9/1996 |
1 |
10252 |
76 |
4 |
7/10/1996 |
2 |
BETWEEN Dates Example
The following SQL statement selects all orders with an OrderDate between '01-July-1996' and
'31-July-1996':
Example
SELECT * FROM Orders WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
MySQL Aliases
Aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of that query.
An alias is created with the AS keyword.
Alias Column Syntax
SELECT column_name AS alias_name FROM table_name;
Alias Table Syntax
SELECT column_name(s) FROM table_name AS alias_name;
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID
|
CustomerName
|
ContactName
|
Address
|
City
|
PostalCode
|
Country
|
2
|
Ana Trujillo Emparedados y helados
|
Ana Trujillo
|
Avda. de la Constitución 2222
|
México D.F.
|
05021
|
Mexico
|
3
|
Antonio Moreno Taquería
|
Antonio Moreno
|
Mataderos 2312
|
México D.F.
|
05023
|
Mexico
|
4
|
Around the Horn
|
Thomas Hardy
|
120 Hanover Sq.
|
London
|
WA1 1DP
|
UK
|
And a selection from the "Orders" table:
OrderID
|
CustomerID
|
EmployeeID
|
OrderDate
|
ShipperID
|
10354
|
58
|
8
|
1996-11-14
|
3
|
10355
|
4
|
6
|
1996-11-15
|
1
|
10356
|
86
|
6
|
1996-11-18
|
2
|
Alias for Columns Examples
The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:
ExampleGet your own SQL Server
SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;
-
The following SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column. Note: Single or double quotation marks are required if the alias name contains spaces:
Example
SELECT CustomerName AS Customer, ContactName AS "Contact Person" FROM Customers;
-
The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):
Example
SELECT CustomerName, CONCAT_WS(', ', Address, PostalCode, City, Country) AS Address FROM Customers;
-
Alias for Tables Example
The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter):
Example
SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;
-
The following SQL statement is the same as above, but without aliases:
Example
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName FROM Customers, Orders WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;
-
Aliases can be useful when:
- There are more than one table involved in a query
- Functions are used in the query
- Column names are big or not very readable
- Two or more columns are combined together
MySQL Joining Tables
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Let's look at a selection from the "Orders" table:
OrderID
|
CustomerID
|
OrderDate
|
10308
|
2
|
1996-09-18
|
10309
|
37
|
1996-09-19
|
10310
|
77
|
1996-09-20
|
Then, look at a selection from the "Customers" table:
CustomerID
|
CustomerName
|
ContactName
|
Country
|
1
|
Alfreds Futterkiste
|
Maria Anders
|
Germany
|
2
|
Ana Trujillo Emparedados y helados
|
Ana Trujillo
|
Mexico
|
3
|
Antonio Moreno Taquería
|
Antonio Moreno
|
Mexico
|
Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.
Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:
Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
-
and it will produce something like this:
OrderID
|
CustomerName
|
OrderDate
|
10308
|
Ana Trujillo Emparedados y helados
|
9/18/1996
|
10365
|
Antonio Moreno Taquería
|
11/27/1996
|
10383
|
Around the Horn
|
12/16/1996
|
10355
|
Around the Horn
|
11/15/1996
|
10278
|
Berglunds snabbköp
|
8/12/1996
|
Supported Types of Joins in MySQL
- INNER JOIN: Returns records that have matching values in both tables
- LEFT JOIN: Returns all records from the left table, and the matched records from the right table
- RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
- CROSS JOIN: Returns all records from both tables
MySQL INNER JOIN Keyword
The INNER JOIN keyword selects records that have matching values in both tables.
INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Orders" table:
OrderID |
CustomerID |
EmployeeID |
OrderDate |
ShipperID |
10308 |
2 |
7 |
1996-09-18 |
3 |
10309 |
37 |
3 |
1996-09-19 |
1 |
10310 |
77 |
8 |
1996-09-20 |
2 |
And a selection from the "Customers" table:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1
|
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno TaquerÃa |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
MySQL INNER JOIN Example
The following SQL statement selects all orders with customer information:
Example
SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID;
Note: The INNER JOIN keyword selects all rows from both
tables as long as there is a match between the columns. If there are records in the
"Orders" table that do not have matches in "Customers", these orders will not
be shown!
JOIN Three Tables
The following SQL statement selects all orders with customer and shipper
information:
Example
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM
((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
MySQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records (if any) from the right table (table2).
LEFT JOIN Syntax
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID
|
CustomerName
|
ContactName
|
Address
|
City
|
PostalCode
|
Country
|
1
|
Alfreds Futterkiste
|
Maria Anders
|
Obere Str. 57
|
Berlin
|
12209
|
Germany
|
2
|
Ana Trujillo Emparedados y helados
|
Ana Trujillo
|
Avda. de la Constitución 2222
|
México D.F.
|
05021
|
Mexico
|
3
|
Antonio Moreno Taquería
|
Antonio Moreno
|
Mataderos 2312
|
México D.F.
|
05023
|
Mexico
|
And a selection from the "Orders" table:
OrderID
|
CustomerID
|
EmployeeID
|
OrderDate
|
ShipperID
|
10308
|
2
|
7
|
1996-09-18
|
3
|
10309
|
37
|
3
|
1996-09-19
|
1
|
10310
|
77
|
8
|
1996-09-20
|
2
|
MySQL LEFT JOIN Example
The following SQL statement will select all customers, and any orders they might have:
Example
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName;
-
Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).
MySQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records (if any) from the left table (table1).
RIGHT JOIN Syntax
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Orders" table:
OrderID |
CustomerID |
EmployeeID |
OrderDate |
ShipperID |
10308 |
2 |
7 |
1996-09-18 |
3 |
10309 |
37 |
3 |
1996-09-19 |
1 |
10310 |
77 |
8 |
1996-09-20 |
2 |
And a selection from the "Employees" table:
EmployeeID |
LastName |
FirstName |
BirthDate |
Photo |
1 |
Davolio |
Nancy |
12/8/1968 |
EmpID1.pic |
2 |
Fuller |
Andrew |
2/19/1952 |
EmpID2.pic |
3 |
Leverling |
Janet |
8/30/1963 |
EmpID3.pic |
MySQL RIGHT JOIN Example
The following SQL statement will return all employees, and any orders they might have placed:
Example
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;
Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).
SQL CROSS JOIN Keyword
The CROSS JOIN keyword returns all records from both tables (table1 and table2).
CROSS JOIN Syntax
SELECT column_name(s) FROM table1 CROSS JOIN table2;
Note: CROSS JOIN can potentially return very large result-sets!
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID
|
CustomerName
|
ContactName
|
Address
|
City
|
PostalCode
|
Country
|
1
|
Alfreds Futterkiste
|
Maria Anders
|
Obere Str. 57
|
Berlin
|
12209
|
Germany
|
2
|
Ana Trujillo Emparedados y helados
|
Ana Trujillo
|
Avda. de la Constitución 2222
|
México D.F.
|
05021
|
Mexico
|
3
|
Antonio Moreno Taquería
|
Antonio Moreno
|
Mataderos 2312
|
México D.F.
|
05023
|
Mexico
|
And a selection from the "Orders" table:
OrderID
|
CustomerID
|
EmployeeID
|
OrderDate
|
ShipperID
|
10308
|
2
|
7
|
1996-09-18
|
3
|
10309
|
37
|
3
|
1996-09-19
|
1
|
10310
|
77
|
8
|
1996-09-20
|
2
|
MySQL CROSS JOIN Example
The following SQL statement selects all customers, and all orders:
Example
SELECT Customers.CustomerName, Orders.OrderID FROM Customers CROSS JOIN Orders;
-
Note: The CROSS JOIN keyword returns all matching records from both tables whether the other table matches or not. So, if there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.
If you add a WHERE clause (if table1 and table2 has a relationship), the CROSS JOIN will produce the same result as the INNER JOIN clause:
Example
SELECT Customers.CustomerName, Orders.OrderID FROM Customers CROSS JOIN Orders WHERE Customers.CustomerID=Orders.CustomerID;
-
MySQL Self Join
A self join is a regular join, but the table is joined with itself.
Self Join Syntax
SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;
T1 and T2 are different table aliases for the same table.
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID
|
CustomerName
|
ContactName
|
Address
|
City
|
PostalCode
|
Country
|
1
|
Alfreds Futterkiste
|
Maria Anders
|
Obere Str. 57
|
Berlin
|
12209
|
Germany
|
2
|
Ana Trujillo Emparedados y helados
|
Ana Trujillo
|
Avda. de la Constitución 2222
|
México D.F.
|
05021
|
Mexico
|
3
|
Antonio Moreno Taquería
|
Antonio Moreno
|
Mataderos 2312
|
México D.F.
|
05023
|
Mexico
|
MySQL Self Join Example
The following SQL statement matches customers that are from the same city:
Example
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City FROM Customers A, Customers B WHERE A.CustomerID <> B.CustomerID AND A.City = B.City ORDER BY A.City;
The MySQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
- Every SELECT statement within UNION must have the same number of columns
- The columns must also have similar data types
- The columns in every SELECT statement must also be in the same order
UNION Syntax
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
UNION ALL Syntax
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
Note: The column names in the result-set are usually equal to the column names in the first SELECT statement.
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID
|
CustomerName
|
ContactName
|
Address
|
City
|
PostalCode
|
Country
|
1
|
Alfreds Futterkiste
|
Maria Anders
|
Obere Str. 57
|
Berlin
|
12209
|
Germany
|
2
|
Ana Trujillo Emparedados y helados
|
Ana Trujillo
|
Avda. de la Constitución 2222
|
México D.F.
|
05021
|
Mexico
|
3
|
Antonio Moreno Taquería
|
Antonio Moreno
|
Mataderos 2312
|
México D.F.
|
05023
|
Mexico
|
And a selection from the "Suppliers" table:
SupplierID
|
SupplierName
|
ContactName
|
Address
|
City
|
PostalCode
|
Country
|
1
|
Exotic Liquid
|
Charlotte Cooper
|
49 Gilbert St.
|
London
|
EC1 4SD
|
UK
|
2
|
New Orleans Cajun Delights
|
Shelley Burke
|
P.O. Box 78934
|
New Orleans
|
70117
|
USA
|
3
|
Grandma Kelly's Homestead
|
Regina Murphy
|
707 Oxford Rd.
|
Ann Arbor
|
48104
|
USA
|
SQL UNION Example
The following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:
Example
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
-
Note: If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!
SQL UNION ALL Example
The following SQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:
Example
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;
-
SQL UNION With WHERE
The following SQL statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table:
Example
SELECT City, Country FROM Customers WHERE Country='Germany' UNION SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
The MySQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
GROUP BY Syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
CustomerID
|
CustomerName
|
ContactName
|
Address
|
City
|
PostalCode
|
Country
|
1
|
Alfreds Futterkiste
|
Maria Anders
|
Obere Str. 57
|
Berlin
|
12209
|
Germany
|
2
|
Ana Trujillo Emparedados y helados
|
Ana Trujillo
|
Avda. de la Constitución 2222
|
México D.F.
|
05021
|
Mexico
|
3
|
Antonio Moreno Taquería
|
Antonio Moreno
|
Mataderos 2312
|
México D.F.
|
05023
|
Mexico
|
4
|
Around the Horn
|
Thomas Hardy
|
120 Hanover Sq.
|
London
|
WA1 1DP
|
UK
|
5
|
Berglunds snabbköp
|
Christina Berglund
|
Berguvsvägen 8
|
Luleå
|
S-958 22
|
Sweden
|
MySQL GROUP BY Examples
The following SQL statement lists the number of customers in each country:
ExampleGet your own SQL Server
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
-
The following SQL statement lists the number of customers in each country, sorted high to low:
Example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC;
-
Demo Database
Below is a selection from the "Orders" table in the Northwind sample database:
OrderID
|
CustomerID
|
EmployeeID
|
OrderDate
|
ShipperID
|
10248
|
90
|
5
|
1996-07-04
|
3
|
10249
|
81
|
6
|
1996-07-05
|
1
|
10250
|
34
|
4
|
1996-07-08
|
2
|
And a selection from the "Shippers" table:
ShipperID
|
ShipperName
|
1
|
Speedy Express
|
2
|
United Package
|
3
|
Federal Shipping
|
GROUP BY With JOIN Example
The following SQL statement lists the number of orders sent by each shipper:
Example
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName;
The MySQL HAVING Clause
The HAVING clause was added to SQL because the
WHERE keyword cannot be
used with aggregate functions.
HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s) HAVING condition ORDER BY
column_name(s);
Demo Database
Below is a selection from the "Customers" table in the Northwind sample
database:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1
|
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno TaquerÃa |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4
|
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Luleå |
S-958 22 |
Sweden |
MySQL HAVING Examples
The following SQL statement lists the number of customers in each country.
Only include countries with more than 5 customers:
Example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country
HAVING COUNT(CustomerID) > 5;
The following SQL statement lists the number of customers in each country,
sorted high to low (Only include countries with more than 5 customers):
Example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID)
> 5
ORDER BY COUNT(CustomerID) DESC;
Demo Database
Below is a selection from the "Orders" table in the Northwind sample database:
OrderID |
CustomerID |
EmployeeID |
OrderDate |
ShipperID |
10248 |
90 |
5 |
1996-07-04 |
3 |
10249 |
81 |
6 |
1996-07-05 |
1 |
10250 |
34 |
4 |
1996-07-08 |
2 |
And a selection from the "Employees" table:
EmployeeID |
LastName |
FirstName |
BirthDate |
Photo |
Notes |
1 |
Davolio |
Nancy |
1968-12-08 |
EmpID1.pic |
Education includes a BA.... |
2 |
Fuller |
Andrew |
1952-02-19 |
EmpID2.pic |
Andrew received his BTS.... |
3 |
Leverling |
Janet |
1963-08-30 |
EmpID3.pic |
Janet has a BS degree.... |
More HAVING Examples
The following SQL statement lists the employees that have registered more
than 10 orders:
Example
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM
(Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10;
The following SQL statement lists if the employees "Davolio" or "Fuller" have registered
more than 25 orders:
Example
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller' GROUP BY LastName HAVING
COUNT(Orders.OrderID) > 25;
The MySQL EXISTS Operator
The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns TRUE if the subquery returns one or more records.
EXISTS Syntax
SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
Demo Database
Below is a selection from the "Products" table in the Northwind sample database:
ProductID
|
ProductName
|
SupplierID
|
CategoryID
|
Unit
|
Price
|
1
|
Chais
|
1
|
1
|
10 boxes x 20 bags
|
18
|
2
|
Chang
|
1
|
1
|
24 - 12 oz bottles
|
19
|
3
|
Aniseed Syrup
|
1
|
2
|
12 - 550 ml bottles
|
10
|
4
|
Chef Anton's Cajun Seasoning
|
2
|
2
|
48 - 6 oz jars
|
22
|
5
|
Chef Anton's Gumbo Mix
|
2
|
2
|
36 boxes
|
21.35
|
And a selection from the "Suppliers" table:
SupplierID
|
SupplierName
|
ContactName
|
Address
|
City
|
PostalCode
|
Country
|
1
|
Exotic Liquid
|
Charlotte Cooper
|
49 Gilbert St.
|
London
|
EC1 4SD
|
UK
|
2
|
New Orleans Cajun Delights
|
Shelley Burke
|
P.O. Box 78934
|
New Orleans
|
70117
|
USA
|
3
|
Grandma Kelly's Homestead
|
Regina Murphy
|
707 Oxford Rd.
|
Ann Arbor
|
48104
|
USA
|
4
|
Tokyo Traders
|
Yoshi Nagase
|
9-8 Sekimai Musashino-shi
|
Tokyo
|
100
|
Japan
|
MySQL EXISTS Examples
The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:
Example
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
-
The following SQL statement returns TRUE and lists the suppliers with a product price equal to 22:
Example
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);
-
The MySQL ANY and ALL Operators
The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.
The ANY Operator
The ANY operator:
- returns a boolean value as a result
- returns TRUE if ANY of the subquery values meet the condition
ANY means that the condition will be true if the operation is true for any of the values in the range.
ANY Syntax
SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
The ALL Operator
The ALL operator:
- returns a boolean value as a result
- returns TRUE if ALL of the subquery values meet the condition
- is used with SELECT, WHERE and HAVING statements
ALL means that the condition will be true only if the operation is true for all values in the range.
ALL Syntax With SELECT
SELECT ALL column_name(s) FROM table_name WHERE condition;
ALL Syntax With WHERE or HAVING
SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
Demo Database
Below is a selection from the "Products" table in the Northwind sample database:
ProductID
|
ProductName
|
SupplierID
|
CategoryID
|
Unit
|
Price
|
1
|
Chais
|
1
|
1
|
10 boxes x 20 bags
|
18
|
2
|
Chang
|
1
|
1
|
24 - 12 oz bottles
|
19
|
3
|
Aniseed Syrup
|
1
|
2
|
12 - 550 ml bottles
|
10
|
4
|
Chef Anton's Cajun Seasoning
|
2
|
2
|
48 - 6 oz jars
|
22
|
5
|
Chef Anton's Gumbo Mix
|
2
|
2
|
36 boxes
|
21.35
|
6
|
Grandma's Boysenberry Spread
|
3
|
2
|
12 - 8 oz jars
|
25
|
7
|
Uncle Bob's Organic Dried Pears
|
3
|
7
|
12 - 1 lb pkgs.
|
30
|
8
|
Northwoods Cranberry Sauce
|
3
|
2
|
12 - 12 oz jars
|
40
|
9
|
Mishi Kobe Niku
|
4
|
6
|
18 - 500 g pkgs.
|
97
|
And a selection from the "OrderDetails" table:
OrderDetailID
|
OrderID
|
ProductID
|
Quantity
|
| | | |