MariaDB Crash Course

Product Author Bios

Ben Forta is a Senior Technical Evangelist with Adobe. He has more than 15 years of experience in the computer industry, and spent six years as part of the development team responsible for creating OnTime, one of the most successful calendar and group-scheduling products, with more than one million users worldwide. His many books include Sams Teach Yourself SQL in 10 Minutes, one of the world's bestselling SQL books, with 100,000+ copies sold.

MariaDB is a database server that offers drop-in replacement functionality for MySQL. Built by some of the original authors of MySQL, with assistance from the broader community of free and open source software developers, MariaDB offers a rich set of feature enhancements to MySQL, including alternate storage engines, server optimizations, and patches.

MariaDB Crash Course teaches you all you need to know to be immediately productive with MariaDB. 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 users who simply want to interact with data.

Learn how to:

• Retrieve and sort data
• Filter data using comparisons, regular expressions, and full text search
• 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

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

Sample Pages

Download the sample pages (includes Chapter 3 and Index)