MariaDB Crash Course

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