MariaDB Essentials

By Kenler Emilien and Razzoli Federico

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

• Language: English
• Publisher: Packt Publishing
• Release date: Oct 27, 2015
• ISBN: 9781783982875

Book preview

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 the CONNECT Storage Engine

Understanding the CONNECT storage engine

Accessing CSV files

Accessing XML and HTML files

Accessing XML data

Detecting the data structure

Working with HTML data

Using the XCOL table type

Using the OCCUR table type

Using the PIVOT table type

The MariaDB community

MariaDB resources

Included storage engines

Summary

Index

MariaDB Essentials

---

MariaDB Essentials

Copyright © 2015 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: October 2015

Production reference: 1231015

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-78398-286-8

www.packtpub.com

Credits

Authors

Emilien Kenler

Federico Razzoli

Reviewers

Aravinth C

Josh King

Pradeesh Parameswaran

Commissioning Editor

Kunal Parikh

Acquisition Editor

Vivek Anantharaman

Content Development Editor

Priyanka Mehta

Technical Editor

Siddhesh Ghadi

Copy Editors

Shruti Iyer

Sonia Mathur

Project Coordinator

Mary Alex

Proofreader

Safis Editing

Indexer

Mariammal Chettiyar

Graphics

Disha Haria

Production Coordinator

Arvindkumar Gupta

Cover Work

Arvindkumar Gupta

About the Authors

Emilien Kenler began focusing on game development after working on small web projects in 2008, when he was in High School. Until 2011, he worked for different groups and specialized in system administration.

A student of computer science engineering, Emilien founded a company that sold Minecraft servers in 2011. He created a lightweight IaaS (https://github.com/HostYourCreeper/) based on new technologies such as Node.js and RabbitMQ.

Thereafter, Emilien worked for TaDaweb as a system administrator, building its infrastructure and creating tools to manage deployment and monitoring.

In 2014, he began a new adventure at Wizcorp, Tokyo. In 2014, Emilien graduated from the University of Technology Compiègne, France.

He also contributed as a reviewer to Learning Nagios 4, MariaDB High Performance, OpenVZ Essentials, Vagrant Virtual Development Environment Cookbook, and Getting Started with MariaDB Second Edition, all by Packt Publishing.

Federico Razzoli is a software developer, database consultant, and free software supporter. He has been working on websites and database applications since 2000 and used MySQL and other relational databases extensively during this period. He is now a MariaDB Ambassador.

About the Reviewers

Aravinth C is a MySQL DBA at Mafiree with an experience of over 5 years.

Apart from MySQL, he is also good at shell scripting, Postgres, MongoDB, and Linux.

Pradeesh Parameswaran is a tech lover born and raised in Malaysia. He is passionate when it comes to technology and programming. Pradeesh has previously developed applications for Palm Handhelds and currently works for a telecommunication company in Malaysia. He is also pursuing his master's degree in Computer Science at the moment. In the future, Pradeesh plans to work towards making classrooms and learning much more interactive in order to help the underprivileged. He is a simple and humble guy who plans to change the world and make it a better place.

I would like to thank my parents for everything. You guys rock!

www.PacktPub.com

Support files, eBooks, discount offers, and more

For support files and downloads related to your book, please visit www.PacktPub.com.

Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at for more details.

At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.

https://www2.packtpub.com/books/subscription/packtlib

Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can search, access, and read Packt's entire library of books.

Why subscribe?

Fully searchable across every book published by Packt

Copy and paste, print, and bookmark content

On demand and accessible via a web browser

Free access for Packt account holders

If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view 9 entirely free books. Simply use your login credentials for immediate access.

Preface

Nowadays, computers are present everywhere, and they are all connected to each other. A lot of information is exchanged between them, but this data needs to be stored somewhere.

MariaDB is a fork of MySQL, started when MySQL was acquired by Sun Microsystems in 2008. Sun Microsytems and MySQL were then acquired by Oracle in 2009.

In most Linux distributions, MariaDB is now the default package, providing a relational database that is compatible with MySQL.

MariaDB has interesting new features, better testing, performance improvement, and bug fixes that are not available in MySQL.

This book provides an introduction to the SQL language. It presents some advanced features of MariaDB and features that aren't present in MySQL but come with MariaDB. By the end of this book, you should be able get your own MariaDB database server running and take advantage of its features.

What this book covers

Chapter 1, Installing MariaDB, describes the steps that need be taken before starting to use MariaDB.

Chapter 2, Databases and Tables, explains how to create data structures.

Chapter 3, Getting Started with SQL, covers the statements used to add, modify, or delete data in a database and the queries used to extract information.

Chapter 4, Importing and Exporting Data, explains how to import and export data.

Chapter 5, Views and Virtual Columns, presents views and virtual columns, which are used to shift the data logic from an application to a database.

Chapter 6, Dynamic Columns, explains how to store heterogeneous data in MariaDB using dynamic columns.

Chapter 7, Full-Text Searches, presents different ways to do full-text searches with MariaDB.

Chapter 8, Using the CONNECT Storage Engine, presents the CONNECT storage engine and ways to connect external data sources directly to MariaDB.

What you need for this book

To put the information provided in this book into practice, you need to install MariaDB on your computer. MariaDB runs on most operating systems, but the following are specifically mentioned in this book: Windows (from XP to Windows 8), Mac OS X, or a standard Linux distribution. Ubuntu, Debian, Mint, Fedora, CentOS, and Red Hat EL, among others, are also supported.

You will also need an Internet connection and the necessary administrative rights to install any software in order to install MariaDB.

No other software is required.

Who this book is for

If you don't know the SQL language but want to quickly jump into the SQL world and learn how to use MariaDB, this is the book for you.

Alternatively, if you already know how to use MySQL but want to go further into it in detail, this book is ideal for you. You will learn all the features added in MariaDB but absent in MySQL.

Conventions

In this book, you will find a number of text styles that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.

Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: Create a /tmp/mariadb/books2.xml file following this structure.

A block of code is set as follows:

  bk101>

     

        Gambardella

        Matthew

     

     

      Computer

      44.95

      2000-10-01

      An in-depth look at creating applications

      with XML.

 

Any command-line input or output is written as follows:

SELECT WEEKDAY(date), COUNT(*)   FROM product_order   WHERE YEAR(date) = YEAR(NOW())   GROUP BY WEEKDAY(date);

New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: Clicking the Next button moves you to the next screen.

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of.

To send us general feedback, simply e-mail <[e-mail]>, and mention the book's title in the subject of your message.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the example code

You can download the example code files from your account at http://www.packtpub.com for all the Packt Publishing books you have purchased. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

Errata

Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you could report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website or added to any list of existing errata under the Errata section of that title.

To view the previously submitted errata, go to https://www.packtpub.com/books/content/support and enter the name of the book in the search field. The required information will appear under the Errata section.

Piracy

Piracy of copyrighted material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the