Book Image

MariaDb Essentials

Book Image

MariaDb Essentials

Overview of this book

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.

Related Content you might be interested in

Current Title:

Small book image
MariaDb Essentials
Oct, 2015 | 206 pages
No titles found
Table of Contents (15 chapters)
MariaDB Essentials
MariaDB Essentials
Credits
Credits
About the Authors
About the Authors
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Installing MariaDB
Installing MariaDB
Choosing a MariaDB version
Installing MariaDB
Starting and stopping MariaDB
Getting started with the mysql client
Configuring MariaDB
Upgrading MariaDB
Managing plugins
Summary
2
Databases and Tables
Databases and Tables
Working with databases
Working with tables
Working with columns
Storage engines
Indexing
Using comments
Working with metadata
Creating an installation script
Summary
3
Getting Started with SQL
Getting Started with SQL
Working with rows
Understanding transactions
Foreign keys
Dealing with duplicates and consistency errors
Reading rows
Joining tables
Working with operators
Working with times and dates
Using comments to annotate your database schema
Summary
4
Importing and Exporting Data
Importing and Exporting Data
The basics of importing and exporting data
Creating and importing CSV files
Creating and importing a dump file
Speeding up data import
Summary
5
Views and Virtual Columns
Views and Virtual Columns
Views
Virtual and persistent columns
Summary
6
Dynamic Columns
Dynamic Columns
The problem: storing non-homogeneous data
Dynamic columns
Summary
7
Full-Text Searches
Full-Text Searches
Defining a full-text search
Full-text searches in MySQL and MariaDB
Working with full-text indexes
Full-text queries
Mroonga
Connecting MariaDB to Sphinx
Summary
8
Using the CONNECT Storage Engine
Using the CONNECT Storage Engine
Understanding the CONNECT storage engine
Accessing CSV files
Accessing XML and HTML files
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
Index

Using the XCOL table type

The XCOL table is used when you have a table with a field containing a list of elements, and you want to access all these elements individually.

First, let's create a table with some data using the following queries:

CREATE TABLE anime (
  title VARCHAR(50) NOT NULL,
  characters VARCHAR(250) DEFAULT NULL
) ENGINE=CONNECT;
INSERT INTO anime VALUES 
('Fullmetal Alchemist', 'Edward Elric, Alphonse Elric, Roy Mustang, Maes Hughes, Greed, Riza Hawkeye, Alex Louis Armstrong'),
('Steins;Gate', 'Rintarou Okabe, Kurisu Makise, Mayuri Shiina, Itaru Hashida'),
('Clannad: After Story', 'Tomoya Okazaki, Nagisa Furukawa, Ushio Okazaki');

The first column contains the title of a Japanese anime, and the second column contains the names of the main characters.

Note

If you want to use a source table which doesn't use the CONNECT engine, you will need to provide more options. Please consult the documentation at https://mariadb.com/kb/en/mariadb/connect-table-types-proxy-table-type/...

Previous Section
End of Section 5
Next Section