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