Learning MySQL By Example

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.1. SQL View Explained

7.2. Benefits of Using Views

7.3. Views That Allow UPDATE Statements

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

Publication Information
Pages	181
License	CC BY-NC-ND International 4.0

SQL MySQL Technical Information Technology

Learning MySQL By Example

Table of Contents

This book covers the basics of MySQL. It includes two sample databases that you can load into your local MySQL instance and try the examples for yourself. In a future edition, the following items will be added. - Information on SQL database design - An explanation of the three types of table relationships and when each should be used - Screencasts that will walk you thought the SQL concepts that are discussed

1. How to Retrieve Data From a Single Table3

1.1. The Five Clauses of the SELECT Statement17

1.2. Column Specifications21

1.3. LIKE and REGEXP Operators23

1.4. Arithmetic Operators27

1.5. Column Aliases29

1.6. Comparison Operators31

1.7. IS NULL, BETWEEN, IN Operators33

1.8. AND, OR, NOT Logical Operators37

1.9. DISTINCT Clause39

2. How to Retrieve Data from Multiple Tables41

2.1. The JOIN Clause43

2.2. Joining More Than Two Tables47

2.3. The OUTER JOIN Clause49

2.4. How to Code a UNION53

3. Using Functions57

3.1. Date Functions59

3.2. Numeric Functions67

3.3. String Functions71

4. How to Insert, Update, Delete Data in Tables77

4.1. The INSERT Clause With a Column List79

4.2. The INSERT Clause Without a Column List83

4.4. The UPDATE Clause With a Column List85

4.4. The DELETE Clause87

5. Summary Queries and Aggregate Functions89

5.1. Aggregate Functions91

5.2. Grouping Data93

5.3. Simple GROUP BY Query95

5.4. Improving the GROUP BY Query97

5.5. Using the HAVING Clause101

5.5. Using the HAVING and WHERE Clauses Together103

5.6. COUNT(column_name) and COUNT(*)105

5.7. Using the DISTINCT Statement107

6. Working With Subqueries109

6.1. The Subquery In a SELECT Statement111

6.2. The Subquery in an UPDATE statement115

6.3. Create a Duplicate Table From An Existing Table 117

6.4. The Subquery In a Delete Statement119

7. SQL Views123

7.1. SQL View Explained125

7.2. Benefits of Using Views127

7.3. Views That Allow UPDATE Statements131

8. SQL Indexes133

8.1. SQL Indexes Explained135

8.2. Clustered vs. Non-clustered Indexes137

8.3. Create an Index in Workbench Using an ERD139

8.4. How to Manually Add an Index to an Existing Table141

Mathew Miles

This content is provided to you freely by BYU-I Books.

Access it online or download it at https://books.byui.edu/learning_mysql.