MySQL Crash Course: A Hands-on Introduction to Database Development

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