MySQL Crash Course

MySQL Crash Course

by Rick Silva
Released May 2023
Publisher(s): No Starch Press
ISBN: 9781718503007

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

MySQL Crash Course is a fast-paced, no-nonsense introduction to relational database development. It’s filled with practical examples and expert advice that will have you up and running quickly.

You’ll learn the basics of SQL, how to create a database, craft SQL queries to extract data, and work with events, procedures, and functions. You’ll see how to add constraints to tables to enforce rules about permitted data and use indexes to accelerate data retrieval. You’ll even explore how to call MySQL from PHP, Python, and Java.

Three final projects will show you how to build a weather database from scratch, use triggers to prevent errors in an election database, and use views to protect sensitive data in a salary database.

You’ll also learn how to:

•Query database tables for specific information, order the results, comment SQL code, and deal with null values
•Define table columns to hold strings, integers, and dates, and determine what data types to use
•Join multiple database tables as well as use temporary tables, common table expressions, derived tables, and subqueries
•Add, change, and remove data from tables, create views based on specific queries, write reusable stored routines, and automate and schedule events

The perfect quick-start resource for database developers, MySQL Crash Course will arm you with the tools you need to build and manage fast, powerful, and secure MySQL-based data storage systems.

Table of contents

  1. PRAISE FOR MySQL Crash Course
  2. Title Page
  3. Copyright
  4. Dedication
  5. About the Author
  6. Acknowledgments
  7. Introduction
    1. About This Book
    2. Who Is This Book For?
    3. SQL in MySQL vs. SQL in Other Database Systems
    4. Using the Online Resources
  8. Part I: Getting Started
    1. Chapter 1: Installing MySQL and Tools
      1. The MySQL Architecture
      2. Installing MySQL
      3. Summary
    2. Chapter 2: Creating Databases and Tables
      1. Using MySQL Workbench
      2. Creating a New Database
      3. Creating a New Table
        1. Constraints
        2. Indexes
      4. Dropping and Altering Tables
      5. Summary
  9. Part II: Selecting Data from a MySQL Database
    1. Chapter 3: Introduction to SQL
      1. Querying Data from a Table
      2. Using the Wildcard Character
      3. Ordering Rows
      4. Formatting SQL Code
        1. Uppercase Keywords
        2. Backticks
        3. Code Comments
      5. Null Values
      6. Summary
    2. Chapter 4: MySQL Data Types
      1. String Data Types
        1. char
        2. varchar
        3. enum
        4. set
        5. tinytext, text, mediumtext, and longtext
      2. Binary Data Types
        1. tinyblob, blob, mediumblob, and longblob
        2. binary
        3. varbinary
        4. bit
      3. Numeric Data Types
        1. tinyint, smallint, mediumint, int, and bigint
        2. Boolean
      4. Decimal Data Types
      5. Date and Time Data Types
      6. The json Data Type
      7. Spatial Data Types
      8. Summary
    3. Chapter 5: Joining Database Tables
      1. Selecting Data from Multiple Tables
      2. Table Aliasing
      3. Types of Joins
        1. Inner Joins
        2. Outer Joins
        3. Natural Joins
        4. Cross Joins
        5. Self Joins
      4. Variations on Join Syntax
        1. Parentheses
        2. Old-School Inner Joins
      5. Column Aliasing
      6. Joining Tables in Different Databases
      7. Summary
    4. Chapter 6: Performing Complex Joins with Multiple Tables
      1. Writing One Query with Two Join Types
      2. Joining Many Tables
      3. Associative Tables
      4. Managing the Data in Your Result Set
        1. The limit Keyword
        2. The union Keyword
      5. Temporary Tables
      6. Common Table Expressions
      7. Recursive Common Table Expressions
      8. Derived Tables
      9. Subqueries
        1. Subqueries That Return More Than One Row
        2. Correlated Subqueries
      10. Summary
    5. Chapter 7: Comparing Values
      1. Comparison Operators
        1. Equal
        2. Not Equal
        3. Greater Than
        4. Greater Than or Equal To
        5. Less Than
        6. Less Than or Equal To
        7. is null
        8. is not null
        9. in
        10. not in
        11. between
        12. not between
        13. like
        14. not like
        15. exists
      2. Checking Booleans
      3. or Conditions
      4. Summary
    6. Chapter 8: Calling Built-in MySQL Functions
      1. What Is a Function?
      2. Passing Arguments to a Function
      3. Optional Arguments
      4. Calling Functions Within Functions
      5. Calling Functions from Different Parts of Your Query
      6. Aggregate Functions
        1. count()
        2. max()
        3. min()
        4. sum()
        5. avg()
        6. group by
      7. String Functions
        1. concat()
        2. format()
        3. left()
        4. right()
        5. lower()
        6. upper()
        7. substring()
        8. trim()
        9. ltrim()
        10. rtrim()
      8. Date and Time Functions
        1. curdate()
        2. curtime()
        3. now()
        4. date_add()
        5. date_sub()
        6. extract()
        7. datediff()
        8. date_format()
        9. str_to_date()
        10. time_format()
      9. Mathematical Operators and Functions
        1. Mathematical Operators
        2. Mathematical Functions
      10. Other Handy Functions
        1. cast()
        2. coalesce()
        3. distinct()
        4. database()
        5. if()
        6. version()
      11. Summary
    7. Chapter 9: Inserting, Updating, and Deleting Data
      1. Inserting Data
        1. Inserting Null Values
        2. Inserting Multiple Rows at Once
        3. Inserting Without Listing Column Names
        4. Inserting Sequences of Numbers
        5. Inserting Data Using a Query
        6. Using a Query to Create and Populate a New Table
      2. Updating Data
        1. Updating Multiple Rows
        2. Updating Multiple Columns
      3. Deleting Data
      4. Truncating and Dropping a Table
      5. Summary
  10. Part III: Database Objects
    1. Chapter 10: Creating Views
      1. Creating a New View
      2. Using Views to Hide Column Values
      3. Inserting, Updating, and Deleting from Views
      4. Dropping a View
      5. Indexes and Views
      6. Summary
    2. Chapter 11: Creating Functions and Procedures
      1. Functions vs. Procedures
      2. Creating Functions
        1. Redefining the Delimiter
        2. Adding Parameters and Returning a Value
        3. Specifying Characteristics
        4. Defining the Function Body
      3. Creating Procedures
        1. Using select to Display Values
        2. Defining Local Variables and User Variables
        3. Using Logic in Procedures
        4. Displaying Procedure Results with select
        5. Using a Cursor
        6. Declaring Output Parameters
        7. Writing Procedures That Call Other Procedures
      4. Listing the Stored Routines in a Database
      5. Summary
    3. Chapter 12: Creating Triggers
      1. Triggers That Audit Data
        1. After Insert Triggers
        2. After Delete Triggers
        3. After Update Triggers
      2. Triggers That Affect Data
        1. Before Insert Triggers
        2. Before Update Triggers
        3. Before Delete Triggers
      3. Summary
    4. Chapter 13: Creating Events
      1. The Event Scheduler
      2. Creating Events with No End Date
      3. Creating Events with an End Date
      4. Checking for Errors
      5. Summary
  11. Part IV: Advanced Topics
    1. Chapter 14: Tips and Tricks
      1. Common Mistakes
        1. Working in the Wrong Database
        2. Using the Wrong Server
        3. Leaving where Clauses Incomplete
        4. Running Partial SQL Statements
      2. Transactions
      3. Supporting an Existing System
      4. Using the MySQL Command Line Client
      5. Loading Data from a File
      6. Loading Data to a File
      7. MySQL Shell
      8. Summary
    2. Chapter 15: Calling MySQL from Programming Languages
      1. PHP
        1. PDO
        2. Object-Oriented MySQLi
        3. Procedural MySQLi
      2. Python
        1. Selecting from a Table
        2. Inserting a Row into a Table
        3. Calling a Stored Procedure
      3. Java
        1. Selecting from a Table
        2. Inserting a Row into a Table
        3. Calling a Stored Procedure
      4. Summary
  12. Part V: Projects
    1. Chapter 16: Building a Weather Database
      1. Technologies You’ll Use
        1. cron
        2. Bash
        3. SQL Scripts
      2. Project Overview
      3. The Data File
      4. Creating the Weather Tables
        1. Data Types
        2. Constraints
      5. Loading the Data File
      6. Copying the Data to Your Final Table
      7. Scheduling the Bash Script on cron
      8. Alternative Approaches
      9. Summary
    2. Chapter 17: Tracking Changes to Voter Data with Triggers
      1. Setting Up the Database
      2. Creating the Tables
        1. The voter Table
        2. The ballot Table
        3. The race Table
        4. The candidate Table
        5. The ballot_candidate Table
      3. Adding Triggers
        1. Before Triggers
        2. After Triggers
      4. Alternative Approaches
        1. Audit Tables
        2. Triggers vs. Privileges
        3. Replacing check Constraints with New Tables
      5. Summary
    3. Chapter 18: Protecting Salary Data with Views
      1. Creating the employee Table
      2. Creating the View
      3. Controlling Permissions
      4. Using MySQL Workbench to Test User Access
      5. An Alternative Approach
      6. Summary
  13. Afterword
  14. Index

Product information

  • Title: MySQL Crash Course
  • Author(s): Rick Silva
  • Release date: May 2023
  • Publisher(s): No Starch Press
  • ISBN: 9781718503007