MySQL Crash Course

Cover
Half Title
Title Page
Copyright Page
Contents
1 Understanding SQL
	Database Basics
		What Is a Database?
		Tables
		Columns and Datatypes
		Rows
		Primary Keys
	What Is SQL?
	Try It Yourself
	Summary
2 Introducing MySQL
	What Is MySQL?
		Client/Server Software
		MySQL Versions
	MySQL Tools
		mysql Command-Line Utility
		MySQL Workbench
		Other Tools
	Summary
3 Working with MySQL
	Using the Command-Line Tool
		Selecting a Database
		Learning About Databases and Tables
	Using MySQL Workbench
		Getting Started
		Using MySQL Workbench
		Selecting a Database
		Learning About Databases and Tables
		Executing SQL Statements
	Next Steps
	Summary
4 Retrieving Data
	The SELECT Statement
	Retrieving Individual Columns
	Retrieving Multiple Columns
	Retrieving All Columns
	Retrieving Distinct Rows
	Limiting Results
	Using Fully Qualified Table Names
	Using Comments
	Summary
	Challenges
5 Sorting Retrieved Data
	Sorting Data
	Sorting by Multiple Columns
	Sorting by Column Position
	Specifying Sort Direction
	Summary
	Challenges
6 Filtering Data
	Using the WHERE Clause
	WHERE Clause Operators
		Checking Against a Single Value
		Checking for Nonmatches
		Checking for a Range of Values
		Checking for No Value
	Summary
	Challenges
7 Advanced Data Filtering
	Combining WHERE Clauses
		Using the AND Operator
		Using the OR Operator
		Understanding the Order of Evaluation
	Using the IN Operator
	Using the NOT Operator
	Summary
	Challenges
8 Using Wildcard Filtering
	Using the LIKE Operator
		The Percent Sign (%) Wildcard
		The Underscore (_) Wildcard
	Tips for Using Wildcards
	Summary
	Challenges
9 Searching Using Regular Expressions
	Understanding Regular Expressions
	Using MySQL Regular Expressions
		Basic Character Matching
		Performing OR Matches
		Matching One of Several Characters
		Matching Ranges
		Matching Special Characters
		Matching Character Classes
		Matching Multiple Instances
		Anchors
	Summary
	Challenges
10 Creating Calculated Fields
	Understanding Calculated Fields
	Concatenating Fields
		Using Aliases
	Performing Mathematical Calculations
	Summary
	Challenges
11 Using Data Manipulation Functions
	Understanding Functions
	Using Functions
		Text Manipulation Functions
		Date and Time Manipulation Functions
		Numeric Manipulation Functions
	Summary
	Challenges
12 Summarizing Data
	Using Aggregate Functions
		The Avg() Function
			The Count() Function
			The Max() Function
			The Min() Function
			The Sum() Function
	Aggregates on Distinct Values
	Combining Aggregate Functions
	Summary
	Challenges
13 Grouping Data
	Understanding Data Grouping
	Creating Groups
	Filtering Groups
	Grouping and Sorting
	Combining Grouping and Data Summarization
	SELECT Clause Ordering
	Summary
	Challenges
14 Working with Subqueries
	Understanding Subqueries
	Filtering by Subquery
	Using Subqueries As Calculated Fields
	Summary
	Challenges
15 Joining Tables
	Understanding Joins
		Understanding Relational Tables
		Why Use Joins?
	Creating a Join
		The Importance of the WHERE Clause
		Inner Joins
		Joining Multiple Tables
	Summary
	Challenges
16 Creating Advanced Joins
	Using Table Aliases
	Using Different Join Types
		Self-Joins
		Natural Joins
		Outer Joins
	Using Joins with Aggregate Functions
	Using Joins and Join Conditions
	Summary
	Challenges
17 Combining Queries
	Understanding Combined Queries
	Creating Combined Queries
		Using UNION
		UNION Rules
		Including or Eliminating Duplicate Rows
		Sorting Combined Query Results
	Summary
	Challenges
18 Full-Text Searching
	Understanding Full-Text Searching
	Using Full-Text Searching
		Performing Full-Text Searches
		Using Query Expansion
		Boolean Text Searches
		Full-Text Searching Notes
	Summary
	Challenges
19 Inserting Data
	Understanding Data Insertion
	Inserting Complete Rows
	Inserting Multiple Rows
	Inserting Retrieved Data
	Summary
	Challenges
20 Updating and Deleting Data
	Updating Data
	Deleting Data
	Guidelines for Updating and Deleting Data
	Summary
	Challenges
21 Creating and Manipulating Tables
	Creating Tables
		Basic Table Creation
		Working with NULL Values
		Primary Keys Revisited
		Using AUTO_INCREMENT
		Specifying Default Values
		Engine Types
	Updating Tables
	Deleting Tables
	Renaming Tables
	Summary
	Challenges
22 Using Views
	Understanding Views
		Why Use Views
		View Rules and Restrictions
	Using Views
		Using Views to Simplify Complex Joins
		Using Views to Reformat Retrieved Data
		Using Views to Filter Unwanted Data
		Using Views with Calculated Fields
		Updating Views
	Summary
	Challenges
23 Working with Stored Procedures
	Understanding Stored Procedures
	Why Use Stored Procedures
	Using Stored Procedures
		Executing Stored Procedures
		Creating Stored Procedures
		The DELIMITER Challenge
		Dropping Stored Procedures
		Working with Parameters
		Building Intelligent Stored Procedures
		Inspecting Stored Procedures
	Summary
	Challenges
24 Using Cursors
	Understanding Cursors
	Working with Cursors
		Creating Cursors
		Opening and Closing Cursors
		Using Cursor Data
	Summary
25 Using Triggers
	Understanding Triggers
	Creating Triggers
	Dropping Triggers
	Using Triggers
		INSERT Triggers
		DELETE Triggers
		UPDATE Triggers
	More on Triggers
	Summary
26 Managing Transaction Processing
	Understanding Transaction Processing
	Controlling Transactions
		Using ROLLBACK
		Using COMMIT
		Using Savepoints
		Changing the Default Commit Behavior
	Summary
27 Globalization and Localization
	Understanding Character Sets and Collation Sequences
	Working with Character Sets and Collation Sequences
	Summary
28 Managing Security
	Understanding Access Control
	Managing Users
		Creating User Accounts
		Deleting User Accounts
		Setting Access Rights
		Changing Passwords
	Summary
29 Database Maintenance
	Backing Up Data
	Performing Database Maintenance
	Diagnosing Startup Problems
	Reviewing Log Files
	Summary
30 Improving Performance
	Improving Performance
	Summary
A: Getting Started with MySQL
	What You Need
	Obtaining the Software
	Installing the Software
	Preparing to Read This Book
B: The Example Tables
	Understanding the Example Tables
	Table Descriptions
		The vendors Table
		The products Table
		The customers Table
		The orders Table
		The orderitems Table
		The productnotes Table
		Creating the Sample Tables
		Using Data Import
		Using SQL Scripts
C: MySQL Statement Syntax
	ALTER TABLE
	COMMIT
	CREATE INDEX
	CREATE PROCEDURE
	CREATE TABLE
	CREATE USER
	CREATE VIEW
	DELETE
	DROP
	INSERT
	INSERT SELECT
	ROLLBACK
	SAVEPOINT
	SELECT
	START TRANSACTION
	UPDATE
D: MySQL Datatypes
	String Datatypes
	Numeric Datatypes
	Date and Time Datatypes
	Binary Datatypes
E: MySQL Reserved Words
Index
	A
	B
	C
	D
	E
	F
	G
	H
	I
	J
	K
	L
	M
	N
	O
	P
	Q
	R
	S
	T
	U
	V
	W
	X
	Y
	Z