Learning MySQL By Example
Introduction
1. How to Retrieve Data From a Single Table
1.1. The Five Clauses of the SELECT Statement
1.2. Column Specifications
1.3. LIKE and REGEXP Operators
1.4. Arithmetic Operators
1.5. Column Aliases
1.6. Comparison Operators
1.7. IS NULL, BETWEEN, IN Operators
1.8. AND, OR, NOT Logical Operators
1.9. DISTINCT Clause
2. How to Retrieve Data from Multiple Tables
2.1. The JOIN Clause
2.2. Joining More Than Two Tables
2.3. The OUTER JOIN Clause
2.4. How to Code a UNION
3. Using Functions
3.1. Date Functions
3.2. Numeric Functions
3.3. String Functions
4. How to Insert, Update, Delete Data in Tables
4.1. The INSERT Clause With a Column List
4.2. The INSERT Clause Without a Column List
4.4. The UPDATE Clause With a Column List
4.4. The DELETE Clause
5. Summary Queries and Aggregate Functions
5.1. Aggregate Functions
5.2. Grouping Data
5.3. Simple GROUP BY Query
5.4. Improving the GROUP BY Query
5.5. Using the HAVING Clause
5.5. Using the HAVING and WHERE Clauses Together
5.6. COUNT(column_name) and COUNT(*)
5.7. Using the DISTINCT Statement
6. Working With Subqueries
6.1. The Subquery In a SELECT Statement
6.2. The Subquery in an UPDATE statement
6.3. Create a Duplicate Table From An Existing Table
6.4. The Subquery In a Delete Statement
7. SQL Views
7.1. SQL View Explained
7.2. Benefits of Using Views
7.3. Views That Allow UPDATE Statements
8. SQL Indexes
8.1. SQL Indexes Explained
8.2. Clustered vs. Non-clustered Indexes
8.3. Create an Index in Workbench Using an ERD
8.4. How to Manually Add an Index to an Existing Table
Glossary
Index
Close
Images
References
Abstracts
Download
Endorsements
Front Matter
Search
Summaries
Sign in
Close
Search
Choose a Sign-in Option
Tools and Settings
Abstracts
Download
Endorsements
Front Matter
Search
Summaries
Content
Introduction
How to Retrieve Data From a Single Table
The Five Clauses of the SELECT Statement
Column Specifications
LIKE and REGEXP Operators
Arithmetic Operators
Column Aliases
Comparison Operators
IS NULL, BETWEEN, IN Operators
AND, OR, NOT Logical Operators
DISTINCT Clause
How to Retrieve Data from Multiple Tables
The JOIN Clause
Joining More Than Two Tables
The OUTER JOIN Clause
How to Code a UNION
Using Functions
Date Functions
Numeric Functions
String Functions
How to Insert, Update, Delete Data in Tables
The INSERT Clause With a Column List
The INSERT Clause Without a Column List
The UPDATE Clause With a Column List
The DELETE Clause
Summary Queries and Aggregate Functions
Aggregate Functions
Grouping Data
Simple GROUP BY Query
Improving the GROUP BY Query
Using the HAVING Clause
Using the HAVING and WHERE Clauses Together
COUNT(column_name) and COUNT(*)
Using the DISTINCT Statement
Working With Subqueries
The Subquery In a SELECT Statement
The Subquery in an UPDATE statement
Create a Duplicate Table From An Existing Table
The Subquery In a Delete Statement
SQL Views
SQL View Explained
Benefits of Using Views
Views That Allow UPDATE Statements
SQL Indexes
SQL Indexes Explained
Clustered vs. Non-clustered Indexes
Create an Index in Workbench Using an ERD
How to Manually Add an Index to an Existing Table
Glossary
Index
Search within this publication
Search the entire site
Questions and Tasks
Download the publication as a PDF.
Endorse this publication.
Read a summary of all chapters.
Search within this book's content.
Submit your own content to the publication.
Close
Search
Search
1.3. LIKE and REGEXP Operators
Match any str
in
g of characters to the left of the symbol
Match a s
in
gle character
Match the pattern to the beg
in
n
in
g of the value be
in
g tested.
… pattern to the end of the value be
in
g tested.
Matches any s
in
gle character.
Matches any s
in
gle character listed with
in
the brackets.
Matches any s
in
gle character with
in
the given range.
Separates two str
in
g patterns and matches either one
1.4. Arithmetic Operators
in
teger Division
Modulo (rema
in
der)
5.1. Aggregate Functions
AVG([DIST
in
CT] column_values)
… average of the non-null columns
in
the expression
SUM([DIST
in
CT] column_values)
… total of the non-null columns
in
the expression
M
in
([DIST
in
CT] column_values)
numeric, date, str
in
g
… value off the non-null columns
in
the expression
MAX([DIST
in
CT] column_values)
numeric, date, str
in
g
… value of the non-null columns
in
the expression
COUNT([DIST
in
CT] column_values)
… number of the non-null columns
in
the expression
… AVG(list_price), SUM(list_price), M
in
(list_price), MAX(list_price),…
1.8. AND, OR, NOT Logical Operators
… but b must NOT be present to be
in
cluded
… a and b are present, item is
in
cluded
… either a OR b is present item is
in
cluded
7.2. Benefits of Using Views
… country_nameFROM city ci JO
in
country co ON ci.CountryCode = co.Code;
Results by select
in
g from the city_country view:
JO
in
country co
Benefits of Us
in
g Views
8.3. Create an Index in Workbench Using an ERD
1.5. Column Aliases
in
the previous example, we created a new column that was a calculated value.…
We used the AS keyword then
in
quotes we put the new column alias of âPeople…
1.6. Comparison Operators
6.3. Create a Duplicate Table From An Existing Table
… Duplicate Table from an Exist
in
g Table with a Select Statement
7.3. Views That Allow UPDATE Statements
Creat
in
g Views That Can Be Used With an UPDATE Statement
