MySQL Crash Course

Ben Forta is Adobe's Senior Director of Education Initiatives and has more than three decades of experience in the computer industry—in product development, support, training, and product marketing. He is the author of the best-selling Sams Teach Yourself SQL in 10 Minutes (as well as spinoff titles like this one and versions on SQL Server T-SQL, Oracle PL/SQL, and MariaDB), Learning Regular Expressions, and Captain Code, which teaches Python to younger coders (and those young at heart), Java, Windows, and more. He has extensive experience in database design and development, has implemented databases for several highly successful commercial software programs and websites, and is a frequent lecturer and columnist on application development and Internet technologies. Ben lives in Oak Park, Michigan, with his wife, Dr. Marcy Forta, and their children. He welcomes your email at ben@forta.com and invites you to visit his website at http://forta.com.

• Chapter 1 Understanding SQL 1Database Basics 1What Is a Database? 2Tables 2Columns and Datatypes 3Rows 4Primary Keys 4What Is SQL? 6Try It Yourself 6Summary 7Chapter 2 Introducing MySQL 9What Is MySQL? 9Client/Server Software 9MySQL Versions 10MySQL Tools 11mysql Command-Line Utility 11MySQL Workbench 12Other Tools 13Summary 13Chapter 3 Working with MySQL 15Using the Command-Line Tool 15Selecting a Database 16Learning About Databases and Tables 17Using MySQL Workbench 20Getting Started 20Using MySQL Workbench 21Selecting a Database 22Learning About Databases and Tables 22Executing SQL Statements 23Next Steps 23Summary 24Chapter 4 Retrieving Data 25The SELECT Statement 25Retrieving Individual Columns 25Retrieving Multiple Columns 27Retrieving All Columns 29Retrieving Distinct Rows 29Limiting Results 31Using Fully Qualified Table Names 32Using Comments 33Summary 34Challenges 34Chapter 5 Sorting Retrieved Data 35Sorting Data 35Sorting by Multiple Columns 37Sorting by Column Position 38Specifying Sort Direction 39Summary 41Challenges 42Chapter 6 Filtering Data 43Using the WHERE Clause 43WHERE Clause Operators 44Checking Against a Single Value 45Checking for Nonmatches 46Checking for a Range of Values 47Checking for No Value 48Summary 49Challenges 49Chapter 7 Advanced Data Filtering 51Combining WHERE Clauses 51Using the AND Operator 51Using the OR Operator 52Understanding the Order ofEvaluation 53Using the IN Operator 54Using the NOT Operator 56Summary 58Challenges 58Chapter 8 Using Wildcard Filtering 59Using the LIKE Operator 59The Percent Sign (%) Wildcard 60The Underscore (_) Wildcard 61Tips for Using Wildcards 63Summary 63Challenges 63Chapter 9 Searching Using Regular Expressions 65Understanding Regular Expressions 65Using MySQL Regular Expressions 66Basic Character Matching 66Performing OR Matches 68Matching One of Several Characters 68Matching Ranges 70Matching Special Characters 70Matching Character Classes 72Matching Multiple Instances 72Anchors 74Summary 75Challenges 76Chapter 10 Creating Calculated Fields 77Understanding Calculated Fields 77Concatenating Fields 78Using Aliases 80Performing Mathematical Calculations 81Summary 83Challenges 83Chapter 11 Using Data Manipulation Functions 85Understanding Functions 85Using Functions 86Text Manipulation Functions 86Date and Time Manipulation Functions 88Numeric Manipulation Functions 91Summary 92Challenges 92Chapter 12 Summarizing Data 93Using Aggregate Functions 93The Avg() Function 94The Count() Function 95The Max() Function 96The Min() Function 97The Sum() Function 98Aggregates on Distinct Values 99Combining Aggregate Functions 100Summary 101Challenges 101Chapter 13 Grouping Data 103Understanding Data Grouping 103Creating Groups 104Filtering Groups 105Grouping and Sorting 107Combining Grouping and Data Summarization 109SELECT Clause Ordering 110Summary 110Challenges 110Chapter 14 Working with Subqueries 113Understanding Subqueries 113Filtering by Subquery 113Using Subqueries As Calculated Fields 117Summary 119Challenges 119Chapter 15 Joining Tables 121Understanding Joins 121Understanding Relational Tables 121Why Use Joins? 122Creating a Join 123The Importance of the WHERE Clause 124Inner Joins 127Joining Multiple Tables 128Summary 130Challenges 130Chapter 16 Creating Advanced Joins 133Using Table Aliases 133Using Different Join Types 134Self-Joins 134Natural Joins 136Outer Joins 137Using Joins with Aggregate Functions 138Using Joins and Join Conditions 139Summary 140Challenges 140Chapter 17 Combining Queries 141Understanding Combined Queries 141Creating Combined Queries 141Using UNION 141UNION Rules 143Including or Eliminating Duplicate Rows 144Sorting Combined Query Results 145Summary 146Challenges 146Chapter 18 Full-Text Searching 147Understanding Full-Text Searching 147Using Full-Text Searching 148Performing Full-Text Searches 148Using Query Expansion 151Boolean Text Searches 153Full-Text Searching Notes 156Summary 157Challenges 157Chapter 19 Inserting Data 159Understanding Data Insertion 159Inserting Complete Rows 159Inserting Multiple Rows 163Inserting Retrieved Data 164Summary 166Challenges 166Chapter 20 Updating and Deleting Data 167Updating Data 167Deleting Data 169Guidelines for Updating and Deleting Data 170Summary 171Challenges 171Chapter 21 Creating and Manipulating Tables 173Creating Tables 173Basic Table Creation 173Working with NULL Values 175Primary Keys Revisited 176Using AUTO_INCREMENT 177Specifying Default Values 178Engine Types 179Updating Tables 180Deleting Tables 182Renaming Tables 182Summary 182Challenges 182Chapter 22 Using Views 183Understanding Views 183Why Use Views 184View Rules and Restrictions 185Using Views 185Using Views to Simplify Complex Joins 185Using Views to ReformatRetrieved Data 186Using Views to Filter Unwanted Data 188Using Views with Calculated Fields 188Updating Views 189Summary 190Challenges 190Chapter 23 Working with Stored Procedures 191Understanding Stored Procedures 191Why Use Stored Procedures 192Using Stored Procedures 193Executing Stored Procedures 193Creating Stored Procedures 193The DELIMITER Challenge 194Dropping Stored Procedures 195Working with Parameters 195Building Intelligent Stored Procedures 199Inspecting Stored Procedures 201Summary 202Challenges 202Chapter 24 Using Cursors 203Understanding Cursors 203Working with Cursors 204Creating Cursors 204Opening and Closing Cursors 205Using Cursor Data 206Summary 210Chapter 25 Using Triggers 211Understanding Triggers 211Creating Triggers 212Dropping Triggers 213Using Triggers 213INSERT Triggers 213DELETE Triggers 214UPDATE Triggers 215More on Triggers 216Summary 216Chapter 26 Managing Transaction Processing 217Understanding Transaction Processing 217Controlling Transactions 219Using ROLLBACK 219Using COMMIT 220Using Savepoints 220Changing the Default Commit Behavior 221Summary 222Chapter 27 Globalization and Localization 223Understanding Character Sets and Collation Sequences 223Working with Character Sets and Collation Sequences 224Summary 226Chapter 28 Managing Security 227Understanding Access Control 227Managing Users 228Creating User Accounts 229Deleting User Accounts 230Setting Access Rights 230Changing Passwords 233Summary 234Chapter 29 Database Maintenance 235Backing Up Data 235Performing Database Maintenance 235Diagnosing Startup Problems 237Reviewing Log Files 237Summary 238Chapter 30 Improving Performance 239Improving Performance 239Summary 240Appendix A Getting Started with MySQL 241What You Need 241Obtaining the Software 242Installing the Software 242Preparing to Read This Book 242Appendix B The Example Tables 243Understanding the Example Tables 243Table Descriptions 244The vendors Table 244The products Table 244The customers Table 245The orders Table 245The orderitems Table 246The productnotes Table 246Creating the Sample Tables 247Using Data Import 247Using SQL Scripts 248Appendix C MySQL Statement Syntax 249ALTER TABLE 249COMMIT 249CREATE INDEX 250CREATE PROCEDURE 250CREATE TABLE 250CREATE USER 250CREATE VIEW 251DELETE 251DROP 251INSERT 251INSERT SELECT 251ROLLBACK 252SAVEPOINT 252SELECT 252START TRANSACTION 252UPDATE 252Appendix D MySQL Datatypes 253String Datatypes 253Numeric Datatypes 255Date and Time Datatypes 256Binary Datatypes 256Appendix E MySQL Reserved Words 2579780138223021 TOC 10/2/2023