MariaDB Crash Course

This first-to-market tutorial covers everything beginners need to succeed with MariaDB, the open, community-based branch of MySQL. Master trainer Ben Forta introduces all the essentials through a series of quick, easy-to-follow, hands-on lessons. Instead of belaboring database theory and relational design, Forta focuses on teaching solutions for the majority of SQL users who simply want to interact with data. Forta covers all this, and more: Using the MariaDB toolset * Retrieving and sorting data Filtering data using comparisons, wildcards, and full text searching Analyzing data with aggregate functions Performing insert, update, and delete operations Joining relational tables using inner, outer, and self joins Combining queries using unions * Using views Creating and modifying tables, and accessing table schemas Working with stored procedures, cursors, and other advanced database features Managing databases, users, and security privileges This book was reviewed and is supported by MariaDB's developers, Monty Program AB. It contains a foreword by project founder Monty Widenius, primary developer of the original version of MySQL.

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

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Summary

Author Biography

Table of Contents

Supplemental Materials