Top
Ben Forta is Adobe Systems’ Director of Developer Relations and has more than 20 years experience in the computer industry in product development, support, training, and product marketing. Ben is the author of the best-selling Sams Teach Yourself SQL in 10 Minutes (now in its third edition, and translated into more than a dozen languages), spinoff titles on MySQL and SQL Server T-SQL, ColdFusion Web Application Construction Kit and Advanced ColdFusion Application Development (both published by Adobe Press), Sams Teach Yourself Regular Expressions in 10 Minutes, as well as books on Flash, Java, Windows, and other subjects. He has extensive experience in database design and development, has implemented databases for several highly successful commercial software programs and Web sites, and is a frequent lecturer and columnist on Internet and database technologies. Ben lives in Oak Park, Michigan, with his wife, Marcy, and their seven children. Ben welcomes your e-mail at ben@forta.com and invites you to visit his Web site at http://forta.com/.
Introduction 1
What Is MariaDB Crash Course? 1
Who Is This Book For? 2
Companion Web Site 3
Conventions Used in This Book 3
1: Understanding SQL 5
Database Basics 5
What Is a Database? 6
Tables 6
Columns and Datatypes 7
Rows 8
NULL 8
Primary Keys 9
What Is SQL? 10
Try It Yourself 11
Summary 11
2: Introducing MariaDB 13
What Is MariaDB? 13
Client-Server Software 14
MySQL Compatibility 15
MariaDB Tools 16
mysql Command Line 16
MySQL Workbench 17
Summary 19
3: Working with MariaDB 21
Making the Connection 21
Selecting a Database 22
Learning About Databases and Tables 23
Summary 26
4: Retrieving Data 27
The SELECT Statement 27
Retrieving Individual Columns 27
Retrieving Multiple Columns 29
Retrieving All Columns 30
Retrieving Distinct Rows 31
Limiting Results 32
Using Fully Qualified Table Names 34
Using Comments 35
Summary 36
5: Sorting Retrieved Data 37
Sorting Data 37
Sorting by Multiple Columns 39
Specifying Sort Direction 40
Summary 43
6: Filtering Data 45
Using the WHERE Clause 45
The WHERE Clause Operators 46
Checking Against a Single Value 47
Checking for Nonmatches 48
Checking for a Range of Values 49
Checking for No Value 50
Summary 51
7: Advanced Data Filtering 53
Combining WHERE Clauses 53
Using the AND Operator 53
Using the OR Operator 54
Understanding Order of Evaluation 55
Using the IN Operator 57
Using the NOT Operator 58
Summary 59
8: Using Wildcard Filtering 61
Using the LIKE Operator 61
The Percent Sign (%) Wildcard 62
The Underscore (_) Wildcard 64
Tips for Using Wildcards 65
Summary 65
9: Searching Using Regular Expressions 67
Understanding Regular Expressions 67
Using Regular Expressions 68
Basic Character Matching 68
Performing OR Matches 70
Matching One of Several Characters 71
Matching Ranges 72
Matching Special Characters 73
Matching Character Classes 75
Matching Multiple Instances 75
Anchors 77
Summary 79
10: Creating Calculated Fields 81
Understanding Calculated Fields 81
Concatenating Fields 82
Using Aliases 84
Performing Mathematical Calculations 85
Summary 87
11: Using Data Manipulation Functions 89
Understanding Functions 89
Using Functions 90
Text Manipulation Functions 90
Date and Time Manipulation Functions 92
Numeric Manipulation Functions 96
Summary 96
12: Summarizing Data 97
Using Aggregate Functions 97
The AVG() Function 98
The COUNT() Function 99
The MAX() Function 100
The MIN() Function 101
The SUM() Function 102
Aggregates on Distinct Values 103
Combining Aggregate Functions 104
Summary 105
13: Grouping Data 107
Understanding Data Grouping 107
Creating Groups 108
Filtering Groups 109
Grouping and Sorting 112
SELECT Clause Ordering 113
Summary 114
14: Working with Subqueries 115
Understanding Subqueries 115
Filtering by Subquery 115
Using Subqueries as Calculated Fields 119
Summary 122
15: Joining Tables 123
Understanding Joins 123
Understanding Relational Tables 123
Why Use Joins? 125
Creating a Join 125
The Importance of the WHERE Clause 127
Inner Joins 129
Joining Multiple Tables 130
Summary 132
16: Creating Advanced Joins 133
Using Table Aliases 133
Using Different Join Types 134
Self Joins 134
Natural Joins 136
Outer Joins 137
Using Joins with Aggregate Functions 139
Using Joins and Join Conditions 140
Summary 140
17: Combining Queries 141
Understanding Combined Queries 141
Creating Combined Queries 141
Using UNION 142
UNION Rules 144
Including or Eliminating Duplicate Rows 144
Sorting Combined Query Results 145
Summary 146
18: Full-Text Searching 147
Understanding Full-Text Searching 147
Using Full-Text Searching 148
Enabling Full-Text Searching Support 148
Performing Full-Text Searches 149
Using Query Expansion 152
Boolean Text Searches 154
Full-Text Search Usage Notes 158
Summary 159
19: Inserting Data 161
Understanding Data Insertion 161
Inserting Complete Rows 161
Inserting Multiple Rows 165
Inserting Retrieved Data 166
Summary 168
20: Updating and Deleting Data 169
Updating Data 169
Deleting Data 171
Guidelines for Updating and Deleting Data 172
Summary 173
21: Creating and Manipulating Tables 175
Creating Tables 175
Basic Table Creation 176
Working with NULL Values 177
Primary Keys Revisited 179
Using AUTO_INCREMENT 180
Specifying Default Values 181
Engine Types 182
Updating Tables 183
Deleting Tables 185
Renaming Tables 185
Summary 186
22: Using Views 187
Understanding Views 187
Why Use Views 188
View Rules and Restrictions 188
Using Views 189
Using Views to Simplify Complex Joins 189
Using Views to Reformat Retrieved Data 191
Using Views to Filter Unwanted Data 192
Using Views with Calculated Fields 193
Updating Views 194
Summary 195
23: Working with Stored Procedures 197
Understanding Stored Procedures 197
Why Use Stored Procedures 198
Using Stored Procedures 199
Executing Stored Procedures 199
Creating Stored Procedures 200
Dropping Stored Procedures 201
Working with Parameters 202
Building Intelligent Stored Procedures 205
Inspecting Stored Procedures 208
Summary 208
24: Using Cursors 209
Understanding Cursors 209
Working with Cursors 209
Creating Cursors 210
Opening and Closing Cursors 210
Using Cursor Data 212
Summary 216
25: Using Triggers 217
Understanding Triggers 217
Creating Triggers 218
Dropping Triggers 219
Using Triggers 219
INSERT Triggers 219
DELETE Triggers 221
UPDATE Triggers 223
More on Triggers 223
Summary 224
26: Managing Transaction Processing 225
Understanding Transaction Processing 225
Controlling Transactions 227
Using ROLLBACK 227
Using COMMIT 228
Using Savepoints 229
Changing the Default Commit Behavior 230
Summary 230
27: Globalization and Localization 231
Understanding Character Sets and Collation Sequences 231
Working with Character Set and Collation Sequences 232
Summary 234
28: Managing Security 235
Understanding Access Control 235
Managing Users 236
Creating User Accounts 237
Deleting User Accounts 238
Setting Access Rights 238
Changing Passwords 241
Summary 242
29: Database Maintenance 243
Backing Up Data 243
Performing Database Maintenance 243
Diagnosing Startup Problems 245
Review Log Files 245
Summary 246
30: Improving Performance 247
Improving Performance 247
Summary 249
A: Getting Started with MariaDB 251
What You Need 251
Obtaining the Software 252
Installing the Software 252
Preparing to Try It Yourself 253
B: The Example Tables 255
Understanding the Sample Tables 255
Table Descriptions 256
Creating the Sample Tables 259
Using mysql 260
Using MySQL Workbench 261
C: MariaDB Datatypes 263
String Datatypes 263
Numeric Datatypes 265
Date and Time Datatypes 266
Binary Datatypes 266
D: MariaDB Reserved Words 269
Index 275
The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.
The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.
Need Help? Copyright © 1999-2026
