MySQL Crash Course

✍ Scribed by Ben Forta

• Publisher: Addison-Wesley Professional
• Year: 2023
• Tongue: English
• Leaves: 304
• Edition: 2
• Category: Library

✦ Synopsis

MySQL is one of the most popular database management systems available, powering everything from Internet powerhouses to individual corporate databases to simple end-user applications, and everything in between. This book will teach you all you need to know to be immediately productive with the latest version of MySQL. By working through 30 highly focused hands-on lessons, your MySQL Crash Course will be both easier and more effective than you'd have thought possible.

Learn How To

• Retrieve and Sort Data
• Filter Data Using Comparisons, Regular Expressions, Full Text Search, and Much More
• Join Relational Data
• Create and Alter Tables
• Insert, Update, and Delete Data
• Leverage the Power of Stored Procedures and Triggers
• Use Views and Cursors
• Manage Transactional Processing
• Create User Accounts and Manage Security via Access Control

✦ Table of Contents

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

✦ Subjects

MySQL; MySQL 8; RDBMS; Relational Database Management System; SQL; Structured Query Language