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
… world;SELECT nameFROM country
where
name LIKE âA%â
… world;SELECT nameFROM country
where
name REGEXP 'g[o,u]';
1.4. Arithmetic Operators
1.8. AND, OR, NOT Logical Operators
… world;SELECT name, populationFROM country
where
region = 'caribbean'AND population…
7.2. Benefits of Using Views
1.5. Column Aliases
1.6. Comparison Operators
… world;SELECT name, populationFROM country
where
population > 1000000;
3.1. Date Functions
Week (00..53),
where
Sunday is the first day of the week; WEEK() mode 0
Week (00..53),
where
Monday is the first day of the week; WEEK() mode 1
Week (01..53),
where
Sunday is the first day of the week; WEEK() mode 2; used…
Week (01..53),
where
Monday is the first day of the week; WEEK() mode 3; used…
Year for the week
where
Sunday is the first day of the week, numeric, four digits;…
Year for the week,
where
Monday is the first day of the week, numeric, four…
5.2. Grouping Data
Filtering With
where
And HAVING
2.4. How to Code a UNION
… SELECT name, population3 FROM city
where
CountryCode = 'AUS'4 UNION5 SELECT…
SELECT name, populationFROM city
where
CountryCode = 'AUS'
SELECT name, populationFROM country
where
continent = 'Oceania'
1. How to Retrieve Data From a Single Table
… world;SELECT nameFROM country
where
name LIKE âA%â
… world;SELECT nameFROM country
where
name REGEXP 'g[o,u]';
… world;SELECT name, populationFROM country
where
population > 1000000;
SELECT name, IndepYearFROM country
where
IndepYear IS NULL;
… world;SELECT name, IndepYearFROM country
where
name BETWEEN "Aruba" and "Bahamas";
… world;SELECT nameFROM country
where
name IN ('Aruba', 'Barbados', 'Cuba', 'Bahamas')ORDER…
… world;SELECT name, populationFROM country
where
region = 'caribbean'AND population…
