MariaDB Essentials

Emilien Kenler, Federico Razzoli

• Publisher: Packt Publishing
• Year: 2015
• eBook ISBN: 9781783982868
• Edition: 1
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

Quickly get up to speed with MariaDB—the leading, drop-in replacement for MySQL, through this practical tutorial

About This Book

• Get to know the basic SQL queries so you can quickly start using MariaDB
• Take control of your data through the advanced features of MariaDB
• Exploit the full potential of MariaDB's exclusive features through quick, practical examples

Who This Book Is For

If you don't know the SQL language, but you want to quickly jump into the SQL world and learn how to use MariaDB, or if you already know how to use MySQL but you want to go further, then this book is ideal for you.

What You Will Learn

• Install and configure MariaDB
• Create databases, tables, and indexes
• Import and export data from and to external files
• Work with views and virtual columns
• Create, read, update, and delete records in your database
• Use dynamic columns
• Set up a powerful full-text search system
• Access your external data from MariaDB through the CONNECT engine

In Detail

This book will take you through all the nitty-gritty parts of MariaDB, right from the creation of your database all the way to using MariaDB's advanced features.

At the very beginning, we show you the basics, that is, how to install MariaDB. Then, we walk you through the databases and tables of MariaDB, and introduce SQL in MariaDB. You will learn about all the features that have been added in MariaDB but are absent in MySQL.

Moving on, you'll learn to import and export data, views, virtual columns, and dynamic columns in MariaDB. Then, you'll get to grips with full-text searches and queries in MariaDb. You'll also be familiarized with the CONNECT storage engine. At the end of the book, you'll be introduced to the community of MariaDB.

Style and approach

This is a complete guide that uses concrete examples to help you understand and exploit the full potential of MariaDB.

MariaDB Essentials

---

Table of Contents

MariaDB Essentials

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Installing MariaDB

Choosing a MariaDB version

Installing MariaDB

Installing on Windows

The noinstall package

The package for Microsoft Installer

Installing on Linux

Using official repositories

The .deb packages

The .rpm packages

Installing MariaDB on Gentoo

Generic Linux binaries

Installing on MacOS

Starting and stopping MariaDB

Getting started with the mysql client

Starting and quitting the client

Running queries

Client commands

Configuring MariaDB

Configuration files

Passing options to mysqld

Setting server variables at runtime

Upgrading MariaDB

Managing plugins

Summary

2. Databases and Tables

Working with databases

Working with tables

Working with columns

Data types

String types

Numeric types

Temporal types

ENUM and SET types

NULL values

Default values

Character sets and collations

Storage engines

Indexing

Using comments

Working with metadata

Creating an installation script

Summary

3. Getting Started with SQL

Working with rows

Inserting rows

Modifying rows

Deleting rows

Understanding transactions

The autocommit mode

The limitations of a transaction in MariaDB

Foreign keys

Creating relationships between tables

Foreign keys explained

Self-referencing tables

Many-to-many relationships

Dealing with duplicates and consistency errors

Reading rows

Specifying the table and column names

Aggregate functions

Filtering rows

Sorting rows

Limiting the number of rows

Grouping results

Joining tables

The cross join operation

The inner join operation

The left join and right join operations

Unions

Subqueries

Scalar subqueries

Row subqueries

Table subqueries

Derived tables

Working with operators

Comparison operators

String operators

The LIKE operator and its variants

Using regular expressions with the REGEXP operator and its variants

Logical operators

The NOT operator

The AND operator

The OR operator

The XOR operator

Arithmetic operators

Operator precedence

Working with times and dates

Writing temporal values

Adding and subtracting time intervals

Extracting date or time parts

Using comments to annotate your database schema

Executable comments

Summary

4. Importing and Exporting Data

The basics of importing and exporting data

Creating and importing CSV files

The SELECT ... INTO OUTFILE statement

File options

Column options

Row options

The LOAD DATA INFILE statement

Creating and importing a dump file

Using mysqldump

Login options

Choosing what to dump

Options affecting the dumping operation

Options affecting the output

Usage examples for mysqldump

Speeding up data import

Summary

5. Views and Virtual Columns

Views

Creating or modifying a view

View limitations

Queries that cannot be used as a view

Updatable views

View security

View definers

Constraints on inserts

Virtual and persistent columns

A virtual column overview

Syntax for virtual columns

Limitations of virtual columns

Storage engine support

Allowed expressions

Compatibility with other database systems

Examples of virtual columns

Taxed prices

Indexing values

Stricter UNIQUE constraints

Summary

6. Dynamic Columns

The problem: storing non-homogeneous data

Storing all product types in the same table

Storing whole products in separate tables

Storing product-specific attributes in separate tables

Storing attributes in a relational table

Dynamic columns

Creating the dynamic columns container

Dynamic column functions

Creating dynamic columns

Obtaining a dynamic column set structure

Reading a dynamic column

Adding a dynamic column

Dropping a dynamic column

Converting a dynamic column set to JSON

Checking the integrity of dynamic columns

Nesting dynamic columns

Storing multiple dynamic column containers in the same table

Indexing dynamic columns

Summary

7. Full-Text Searches

Defining a full-text search

Full-text searches in MySQL and MariaDB

Working with full-text indexes

Full-text queries

The natural language mode

The Boolean mode

Query expansion

Limitations to the full-text search

Mroonga

Installation

Mroonga modes

Creating a table

The storage mode

The wrapper mode

Full-text queries

Choosing a different parser

The Boolean mode

Connecting MariaDB to Sphinx

Installation

Configuration

Usage

Summary

8. Using th...

Table of contents

• MariaDB Essentials