MariaDB - Tutorialspoint

MariaDBAbout the TutorialMariaDB is a fork of the MySQL relational database management system. The originaldevelopers of MySQL created MariaDB after concerns raised by Oracle's acquisition ofMySQL.This tutorial will provide a quick introduction to MariaDB and aid you in achieving a highlevel of comfort with MariaDB programming and administration.AudienceThis tutorial targets novice developers and those new to MariaDB. It guides them inunderstanding basic through more advanced concepts in MariaDB. After completing thistutorial, your firm foundation in MariaDB and level of expertise will allow you to begindeveloping and easily build on your knowledge.PrerequisitesThe tutorial assumes that you are familiar with relational database management systems,querying languages, MySQL, and general programming. It also assumes familiarity withtypical database operations in an application.Copyright & Disclaimer Copyright 2016 by Tutorials Point (I) Pvt. Ltd.All the content and graphics published in this e-book are the property of Tutorials Point (I)Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republishany contents or a part of contents of this e-book in any manner without written consentof the publisher.We strive to update the contents of our website and tutorials as timely and as precisely aspossible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt.Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of ourwebsite or its contents including this tutorial. If you discover any errors on our website orin this tutorial, please notify us at contact@tutorialspoint.comi

MariaDBTable of ContentsAbout the Tutorial . iAudience . iPrerequisites . iCopyright & Disclaimer . iTable of Contents . ii1.MariaDB – Introduction . 1RDBMS Terminology . 1MARIA Database . 22.MariaDB – Installation . 3Installing on LINUX/UNIX . 3Installing on Windows . 4Testing the Installation . 4Post- Installation . 4Upgrading on Windows . 53.MariaDB – Administration . 6Creating a User Account . 6The Configuration File . 6Administration Commands . 84.MariaDB – PHP Syntax . 95.MariaDB – Connection . 10MYSQL Binary . 10PHP Connection Script . 106.MariaDB – Create Database . 13mysqladmin Binary . 13PHP Create Database Script . 137.MariaDB – Drop Database. 15mysqladmin Binary . 15PHP Drop Database Script . 158.MariaDB – Select Database . 17The Command Prompt . 17PHP Select Database Script. 179.MariaDB – Data Types. 19Numeric Data Types . 19Date and Time Data Types . 20String DataTypes . 2010. MariaDB – Create Tables . 22The Command Prompt . 22PHP Create Table Script . 23ii

MariaDB11. MariaDB – Drop Tables . 25The Command Prompt . 25PHP Drop Table Script. 2512. MariaDB – Insert Query . 27The Command Prompt . 27PHP Insertion Script . 2813. MariaDB – Select Query . 30The Command Prompt . 30PHP Select Script. 3114. MariaDB – Where Clause . 34The Command Prompt . 34PHP Scripts Using Where Clause . 3515. MariaDB – Update Query . 37The Command Prompt . 37PHP Update Query Script. 3716. MariaDB – Delete Query . 39The Command Prompt . 39PHP Delete Query Script . 3917. MariaDB – Like Clause . 41The Command Prompt . 41PHP Script Using Like Clause . 4218. MariaDB – Order By Clause . 44The Command Prompt . 44PHP Script Using Order By Clause . 4519. MariaDB – Join . 47The Command Prompt . 47PHP Script Using JOIN . 4820. MariaDB – Null Values . 50NULL Operators . 50Sorting NULL Values . 50NULL Functions . 51Inserting NULL Values . 5121. MariaDB – Regular Expression . 5222. MariaDB – Transactions . 54Structure of a Transaction . 5423. MariaDB – Alter Command . 56Using ALTER to Modify Columns . 56Using ALTER to Modify Tables . 57iii

MariaDB24. MariaDB – Indexes and Statistics Tables . 58Create an Index . 58Drop an Index . 58Rename an Index . 59Managing Indexes. 59Table Statistics . 5925. MariaDB – Temporary Tables . 60Create a Temporary Table . 60Administration . 60Drop a Temporary Table . 6126. MariaDB – Table Cloning . 6227. MariaDB – Sequences . 63Installating the Sequence Engine . 63Creating Sequence . 6328. MariaDB – Managing Duplicates . 65Strategies and Tools . 65Using INSERT. 65Using DISTINCT . 66Using INSERT IGNORE . 6629. MariaDB – SQL Injection Protection . 6730. MariaDB – Backup Methods. 69Backup Tools . 69Using THE SELECT.INTO OUTFILE Statement . 70Using CONNECT in Backups . 70Other Tools . 7131. MariaDB – Backup Loading Methods . 72Using LOAD DATA . 72Using MYSQLIMPORT . 73Using MYSQLDUMP . 7332. MariaDB – Useful Functions . 74MariaDB Aggregate Functions . 74MariaDB Age Calculation . 74MariaDB String Concatenation . 75MariaDB Date/Time Functions . 75MariaDB Numeric Functions. 77MariaDB String Functions . 78iv

1. MariaDB – IntroductionMariaDBA database application exists separate from the main application and stores datacollections. Every database employs one or multiple APIs for the creation, access,management, search, and replication of the data it contains.Databases also use non-relational data sources such as objects or files. However,databases prove the best option for large datasets, which would suffer from slow retrievaland writing with other data sources.Relational database management systems, or RDBMS, store data in various tables.Relationships between these tables are established using primary keys and foreign keys.RDBMS offers the following features They enable you to implement a data source with tables, columns, and indices. They ensure the integrity of references across rows of multiple tables. They automatically update indices. They interpret SQL queries and operations in manipulating or sourcing data fromtables.RDBMS TerminologyBefore we begin our discussion of MariaDB, let us review a fe

MariaDB offers Galera cluster technology. MariaDB also offers many operations and commands unavailable in MySQL, and eliminates/replaces features impacting performance negatively. Getting Started Before you begin this tut