MariaDB Essentials电子书

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 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