3. Getting Started with SQL | MariaDb Essentials

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

MariaDb Essentials

By : Emilien Kenler

4.5 (2)

MariaDb Essentials

4.5 (2)

By: Emilien Kenler

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.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Free Chapter

1. Installing MariaDB

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

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

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

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

5. Views and Virtual Columns

Views

Virtual and persistent columns

Summary

6. Dynamic Columns

6. Dynamic Columns

The problem: storing non-homogeneous data

Dynamic columns

Summary

7. Full-Text Searches

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

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

Joining tables

While examining the GROUP BY clause, we joined two tables into one resultset. This is a common operation when working with relational databases, because a table is generally correlated with other tables. The statement that allows us to do so is JOIN.

There are several types of joins:

CROSS JOIN
INNER JOIN
LEFT JOIN
RIGHT JOIN

The cross join operation

A CROSS JOIN is an operation that associates all the rows from the left table to all the rows in the right table. The number of rows in the resultset is the product of the number of rows in the left table multiplied by the number of rows in the second table, which is usually a huge number. This operation is very rarely used, and it is very expensive.

The following is an example of CROSS JOIN. Imagine we have two tables, both containing the numbers from 1 to 3:

MariaDB [test]> SELECT a.c, b.c FROM t1 a CROSS JOIN t2 b;
+------+------+
| c    | c    |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    3 |    1 |
|    1 |  ...

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

MariaDb Essentials

Search

Your notes and bookmarks