With databases lurking in the background of every website, knowing how to manage them with MySQL is a no-brainer. This practical, hands-on introduction teaches readers all they need to know.
This complete guide to all things MySQL will take readers from the absolute basics of creating a table to the complexities of managing an entire database. Learn to build efficient databases through interesting examples, exercises, and three hands-on projects creating databases for weather, voter, and salary data.
The book leads readers through all of MySQL’s intricacies, without the technical jargon. It’s designed to help build confidence in creating simple databases and tables before learning more advanced concepts, including how to update and delete data, work with multiple tables, and avoid common mishaps. By the end of the book, readers will be database management pros, who know how to carry out comprehensive data projects with ease.
PRAISE FOR MySQL Crash Course Title Page Copyright Dedication About the Author Acknowledgments Introduction About This Book Who Is This Book For? SQL in MySQL vs. SQL in Other Database Systems Using the Online Resources Part I: Getting Started Chapter 1: Installing MySQL and Tools The MySQL Architecture Installing MySQL Summary Chapter 2: Creating Databases and Tables Using MySQL Workbench Creating a New Database Creating a New Table Constraints Indexes Dropping and Altering Tables Summary Part II: Selecting Data from a MySQL Database Chapter 3: Introduction to SQL Querying Data from a Table Using the Wildcard Character Ordering Rows Formatting SQL Code Uppercase Keywords Backticks Code Comments Null Values Summary Chapter 4: MySQL Data Types String Data Types char varchar enum set tinytext, text, mediumtext, and longtext Binary Data Types tinyblob, blob, mediumblob, and longblob binary varbinary bit Numeric Data Types tinyint, smallint, mediumint, int, and bigint Boolean Decimal Data Types Date and Time Data Types The json Data Type Spatial Data Types Summary Chapter 5: Joining Database Tables Selecting Data from Multiple Tables Table Aliasing Types of Joins Inner Joins Outer Joins Natural Joins Cross Joins Self Joins Variations on Join Syntax Parentheses Old-School Inner Joins Column Aliasing Joining Tables in Different Databases Summary Chapter 6: Performing Complex Joins with Multiple Tables Writing One Query with Two Join Types Joining Many Tables Associative Tables Managing the Data in Your Result Set The limit Keyword The union Keyword Temporary Tables Common Table Expressions Recursive Common Table Expressions Derived Tables Subqueries Subqueries That Return More Than One Row Correlated Subqueries Summary Chapter 7: Comparing Values Comparison Operators Equal Not Equal Greater Than Greater Than or Equal To Less Than Less Than or Equal To is null is not null in not in between not between like not like exists Checking Booleans or Conditions Summary Chapter 8: Calling Built-in MySQL Functions What Is a Function? Passing Arguments to a Function Optional Arguments Calling Functions Within Functions Calling Functions from Different Parts of Your Query Aggregate Functions count() max() min() sum() avg() group by String Functions concat() format() left() right() lower() upper() substring() trim() ltrim() rtrim() Date and Time Functions curdate() curtime() now() date_add() date_sub() extract() datediff() date_format() str_to_date() time_format() Mathematical Operators and Functions Mathematical Operators Mathematical Functions Other Handy Functions cast() coalesce() distinct() database() if() version() Summary Chapter 9: Inserting, Updating, and Deleting Data Inserting Data Inserting Null Values Inserting Multiple Rows at Once Inserting Without Listing Column Names Inserting Sequences of Numbers Inserting Data Using a Query Using a Query to Create and Populate a New Table Updating Data Updating Multiple Rows Updating Multiple Columns Deleting Data Truncating and Dropping a Table Summary Part III: Database Objects Chapter 10: Creating Views Creating a New View Using Views to Hide Column Values Inserting, Updating, and Deleting from Views Dropping a View Indexes and Views Summary Chapter 11: Creating Functions and Procedures Functions vs. Procedures Creating Functions Redefining the Delimiter Adding Parameters and Returning a Value Specifying Characteristics Defining the Function Body Creating Procedures Using select to Display Values Defining Local Variables and User Variables Using Logic in Procedures Displaying Procedure Results with select Using a Cursor Declaring Output Parameters Writing Procedures That Call Other Procedures Listing the Stored Routines in a Database Summary Chapter 12: Creating Triggers Triggers That Audit Data After Insert Triggers After Delete Triggers After Update Triggers Triggers That Affect Data Before Insert Triggers Before Update Triggers Before Delete Triggers Summary Chapter 13: Creating Events The Event Scheduler Creating Events with No End Date Creating Events with an End Date Checking for Errors Summary Part IV: Advanced Topics Chapter 14: Tips and Tricks Common Mistakes Working in the Wrong Database Using the Wrong Server Leaving where Clauses Incomplete Running Partial SQL Statements Transactions Supporting an Existing System Using the MySQL Command Line Client Loading Data from a File Loading Data to a File MySQL Shell Summary Chapter 15: Calling MySQL from Programming Languages PHP PDO Object-Oriented MySQLi Procedural MySQLi Python Selecting from a Table Inserting a Row into a Table Calling a Stored Procedure Java Selecting from a Table Inserting a Row into a Table Calling a Stored Procedure Summary Part V: Projects Chapter 16: Building a Weather Database Technologies You’ll Use cron Bash SQL Scripts Project Overview The Data File Creating the Weather Tables Data Types Constraints Loading the Data File Copying the Data to Your Final Table Scheduling the Bash Script on cron Alternative Approaches Summary Chapter 17: Tracking Changes to Voter Data with Triggers Setting Up the Database Creating the Tables The voter Table The ballot Table The race Table The candidate Table The ballot_candidate Table Adding Triggers Before Triggers After Triggers Alternative Approaches Audit Tables Triggers vs. Privileges Replacing check Constraints with New Tables Summary Chapter 18: Protecting Salary Data with Views Creating the employee Table Creating the View Controlling Permissions Using MySQL Workbench to Test User Access An Alternative Approach Summary Afterword Index
How to download source code?
1. Go to:
2. Search the book title:
MySQL Crash Course: A Hands-on Introduction to Database Development, sometime you may not get the results, please search the main title
3. Click the book title in the search results
3. Download the Source Code.
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.