MariaDB Essentials

MariaDB Essentials

by Emilien Kenler, Federico Razzoli
Released October 2015
Publisher(s): Packt Publishing
ISBN: 9781783982868

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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.

Table of contents

  1. MariaDB Essentials
    1. Table of Contents
    2. MariaDB Essentials
    3. Credits
    4. About the Authors
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers, and more
        1. Why subscribe?
        2. Free access for Packt account holders
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    8. 1. Installing MariaDB
      1. Choosing a MariaDB version
      2. Installing MariaDB
        1. Installing on Windows
          1. The noinstall package
          2. The package for Microsoft Installer
        2. Installing on Linux
          1. Using official repositories
          2. The .deb packages
          3. The .rpm packages
          4. Installing MariaDB on Gentoo
          5. Generic Linux binaries
        3. Installing on MacOS
      3. Starting and stopping MariaDB
      4. Getting started with the mysql client
        1. Starting and quitting the client
        2. Running queries
        3. Client commands
      5. Configuring MariaDB
        1. Configuration files
        2. Passing options to mysqld
        3. Setting server variables at runtime
      6. Upgrading MariaDB
      7. Managing plugins
      8. Summary
    9. 2. Databases and Tables
      1. Working with databases
      2. Working with tables
      3. Working with columns
        1. Data types
          1. String types
          2. Numeric types
          3. Temporal types
          4. ENUM and SET types
        2. NULL values
        3. Default values
        4. Character sets and collations
      4. Storage engines
      5. Indexing
      6. Using comments
      7. Working with metadata
      8. Creating an installation script
      9. Summary
    10. 3. Getting Started with SQL
      1. Working with rows
        1. Inserting rows
        2. Modifying rows
        3. Deleting rows
      2. Understanding transactions
        1. The autocommit mode
        2. The limitations of a transaction in MariaDB
      3. Foreign keys
        1. Creating relationships between tables
        2. Foreign keys explained
        3. Self-referencing tables
        4. Many-to-many relationships
      4. Dealing with duplicates and consistency errors
      5. Reading rows
        1. Specifying the table and column names
        2. Aggregate functions
        3. Filtering rows
        4. Sorting rows
        5. Limiting the number of rows
        6. Grouping results
      6. Joining tables
        1. The cross join operation
        2. The inner join operation
        3. The left join and right join operations
        4. Unions
        5. Subqueries
          1. Scalar subqueries
          2. Row subqueries
          3. Table subqueries
          4. Derived tables
      7. Working with operators
        1. Comparison operators
        2. String operators
          1. The LIKE operator and its variants
          2. Using regular expressions with the REGEXP operator and its variants
        3. Logical operators
          1. The NOT operator
          2. The AND operator
          3. The OR operator
          4. The XOR operator
        4. Arithmetic operators
        5. Operator precedence
      8. Working with times and dates
        1. Writing temporal values
        2. Adding and subtracting time intervals
        3. Extracting date or time parts
      9. Using comments to annotate your database schema
        1. Executable comments
      10. Summary
    11. 4. Importing and Exporting Data
      1. The basics of importing and exporting data
      2. Creating and importing CSV files
        1. The SELECT ... INTO OUTFILE statement
          1. File options
          2. Column options
          3. Row options
        2. The LOAD DATA INFILE statement
      3. Creating and importing a dump file
        1. Using mysqldump
          1. Login options
          2. Choosing what to dump
          3. Options affecting the dumping operation
          4. Options affecting the output
          5. Usage examples for mysqldump
      4. Speeding up data import
      5. Summary
    12. 5. Views and Virtual Columns
      1. Views
        1. Creating or modifying a view
        2. View limitations
          1. Queries that cannot be used as a view
          2. Updatable views
        3. View security
          1. View definers
          2. Constraints on inserts
      2. Virtual and persistent columns
        1. A virtual column overview
        2. Syntax for virtual columns
        3. Limitations of virtual columns
          1. Storage engine support
          2. Allowed expressions
        4. Compatibility with other database systems
        5. Examples of virtual columns
          1. Taxed prices
          2. Indexing values
          3. Stricter UNIQUE constraints
      3. Summary
    13. 6. Dynamic Columns
      1. The problem: storing non-homogeneous data
        1. Storing all product types in the same table
        2. Storing whole products in separate tables
        3. Storing product-specific attributes in separate tables
        4. Storing attributes in a relational table
      2. Dynamic columns
        1. Creating the dynamic columns container
        2. Dynamic column functions
          1. Creating dynamic columns
          2. Obtaining a dynamic column set structure
          3. Reading a dynamic column
          4. Adding a dynamic column
          5. Dropping a dynamic column
          6. Converting a dynamic column set to JSON
          7. Checking the integrity of dynamic columns
        3. Nesting dynamic columns
        4. Storing multiple dynamic column containers in the same table
        5. Indexing dynamic columns
      3. Summary
    14. 7. Full-Text Searches
      1. Defining a full-text search
      2. Full-text searches in MySQL and MariaDB
      3. Working with full-text indexes
      4. Full-text queries
        1. The natural language mode
        2. The Boolean mode
        3. Query expansion
        4. Limitations to the full-text search
      5. Mroonga
        1. Installation
        2. Mroonga modes
        3. Creating a table
          1. The storage mode
          2. The wrapper mode
        4. Full-text queries
        5. Choosing a different parser
        6. The Boolean mode
      6. Connecting MariaDB to Sphinx
        1. Installation
        2. Configuration
        3. Usage
      7. Summary
    15. 8. Using the CONNECT Storage Engine
      1. Understanding the CONNECT storage engine
      2. Accessing CSV files
      3. Accessing XML and HTML files
        1. Accessing XML data
        2. Detecting the data structure
        3. Working with HTML data
      4. Using the XCOL table type
      5. Using the OCCUR table type
      6. Using the PIVOT table type
      7. The MariaDB community
      8. MariaDB resources
      9. Included storage engines
      10. Summary
    16. Index

Product information

  • Title: MariaDB Essentials
  • Author(s): Emilien Kenler, Federico Razzoli
  • Release date: October 2015
  • Publisher(s): Packt Publishing
  • ISBN: 9781783982868