MariaDB - Tutorialspoint

MariaDB

About the Tutorial

MariaDB is a fork of the MySQL relational database management system. The original developers of MySQL created MariaDB after concerns raised by Oracle's acquisition of MySQL. This tutorial will provide a quick introduction to MariaDB and aid you in achieving a high level of comfort with MariaDB programming and administration.

Audience

This tutorial targets novice developers and those new to MariaDB. It guides them in understanding basic through more advanced concepts in MariaDB. After completing this tutorial, your firm foundation in MariaDB and level of expertise will allow you to begin developing and easily build on your knowledge.

Prerequisites

The tutorial assumes that you are familiar with relational database management systems, querying languages, MySQL, and general programming. It also assumes familiarity with typical 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 republish any contents or a part of contents of this e-book in any manner without written consent of the publisher. We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or in this tutorial, please notify us at contact@

i

MariaDB

Table of Contents

About the Tutorial ............................................................................................................................................ i Audience........................................................................................................................................................... i Prerequisites..................................................................................................................................................... i Copyright & Disclaimer ..................................................................................................................................... i Table of Contents ............................................................................................................................................ ii

1. MariaDB ? Introduction ............................................................................................................................1 RDBMS Terminology........................................................................................................................................ 1 MARIA Database.............................................................................................................................................. 2

2. MariaDB ? Installation ..............................................................................................................................3 Installing on LINUX/UNIX ................................................................................................................................. 3 Installing on Windows ..................................................................................................................................... 4 Testing the Installation .................................................................................................................................... 4 Post- Installation.............................................................................................................................................. 4 Upgrading on Windows ................................................................................................................................... 5

3. MariaDB ? Administration ........................................................................................................................6 Creating a User Account .................................................................................................................................. 6 The Configuration File ..................................................................................................................................... 6 Administration Commands.............................................................................................................................. 8

4. MariaDB ? PHP Syntax ..............................................................................................................................9

5. MariaDB ? Connection ............................................................................................................................10 MYSQL Binary ................................................................................................................................................ 10 PHP Connection Script ................................................................................................................................... 10

6. MariaDB ? Create Database ....................................................................................................................13 mysqladmin Binary ........................................................................................................................................ 13 PHP Create Database Script........................................................................................................................... 13

7. MariaDB ? Drop Database.......................................................................................................................15 mysqladmin Binary ........................................................................................................................................ 15 PHP Drop Database Script ............................................................................................................................. 15

8. MariaDB ? Select Database .....................................................................................................................17 The Command Prompt .................................................................................................................................. 17 PHP Select Database Script............................................................................................................................ 17

9. MariaDB ? Data Types.............................................................................................................................19 Numeric Data Types ...................................................................................................................................... 19 Date and Time Data Types............................................................................................................................. 20 String DataTypes............................................................................................................................................ 20

10. MariaDB ? Create Tables.........................................................................................................................22 The Command Prompt .................................................................................................................................. 22 PHP Create Table Script ................................................................................................................................. 23

ii

MariaDB

11. MariaDB ? Drop Tables ...........................................................................................................................25 The Command Prompt .................................................................................................................................. 25 PHP Drop Table Script.................................................................................................................................... 25

12. MariaDB ? Insert Query ..........................................................................................................................27 The Command Prompt .................................................................................................................................. 27 PHP Insertion Script ....................................................................................................................................... 28

13. MariaDB ? Select Query ..........................................................................................................................30 The Command Prompt .................................................................................................................................. 30 PHP Select Script............................................................................................................................................ 31

14. MariaDB ? Where Clause ........................................................................................................................34 The Command Prompt .................................................................................................................................. 34 PHP Scripts Using Where Clause ................................................................................................................... 35

15. MariaDB ? Update Query........................................................................................................................37 The Command Prompt .................................................................................................................................. 37 PHP Update Query Script............................................................................................................................... 37

16. MariaDB ? Delete Query .........................................................................................................................39 The Command Prompt .................................................................................................................................. 39 PHP Delete Query Script ................................................................................................................................ 39

17. MariaDB ? Like Clause.............................................................................................................................41 The Command Prompt .................................................................................................................................. 41 PHP Script Using Like Clause.......................................................................................................................... 42

18. MariaDB ? Order By Clause .....................................................................................................................44 The Command Prompt .................................................................................................................................. 44 PHP Script Using Order By Clause.................................................................................................................. 45

19. MariaDB ? Join........................................................................................................................................47 The Command Prompt .................................................................................................................................. 47 PHP Script Using JOIN .................................................................................................................................... 48

20. MariaDB ? Null Values ............................................................................................................................50 NULL Operators ............................................................................................................................................. 50 Sorting NULL Values ...................................................................................................................................... 50 NULL Functions .............................................................................................................................................. 51 Inserting NULL Values.................................................................................................................................... 51

21. MariaDB ? Regular Expression ................................................................................................................52

22. MariaDB ? Transactions ..........................................................................................................................54 Structure of a Transaction ............................................................................................................................. 54

23. MariaDB ? Alter Command .....................................................................................................................56 Using ALTER to Modify Columns ................................................................................................................... 56 Using ALTER to Modify Tables ....................................................................................................................... 57

iii

MariaDB

24. MariaDB ? Indexes and Statistics Tables .................................................................................................58 Create an Index ............................................................................................................................................. 58 Drop an Index ................................................................................................................................................ 58 Rename an Index ........................................................................................................................................... 59 Managing Indexes.......................................................................................................................................... 59 Table Statistics ............................................................................................................................................... 59

25. MariaDB ? Temporary Tables..................................................................................................................60 Create a Temporary Table ............................................................................................................................. 60 Administration ............................................................................................................................................... 60 Drop a Temporary Table................................................................................................................................ 61

26. MariaDB ? Table Cloning.........................................................................................................................62

27. MariaDB ? Sequences .............................................................................................................................63 Installating the Sequence Engine .................................................................................................................. 63 Creating Sequence......................................................................................................................................... 63

28. MariaDB ? Managing Duplicates .............................................................................................................65 Strategies and Tools ...................................................................................................................................... 65 Using INSERT.................................................................................................................................................. 65 Using DISTINCT .............................................................................................................................................. 66 Using INSERT IGNORE .................................................................................................................................... 66

29. MariaDB ? SQL Injection Protection ........................................................................................................67

30. MariaDB ? Backup Methods....................................................................................................................69 Backup Tools.................................................................................................................................................. 69 Using THE SELECT...INTO OUTFILE Statement ............................................................................................... 70 Using CONNECT in Backups ........................................................................................................................... 70 Other Tools .................................................................................................................................................... 71

31. MariaDB ? Backup Loading Methods ......................................................................................................72 Using LOAD DATA .......................................................................................................................................... 72 Using MYSQLIMPORT .................................................................................................................................... 73 Using MYSQLDUMP ....................................................................................................................................... 73

32. MariaDB ? Useful Functions ....................................................................................................................74 MariaDB Aggregate Functions ....................................................................................................................... 74 MariaDB Age Calculation ............................................................................................................................... 74 MariaDB String Concatenation ...................................................................................................................... 75 MariaDB Date/Time Functions ...................................................................................................................... 75 MariaDB Numeric Functions.......................................................................................................................... 77 MariaDB String Functions .............................................................................................................................. 78

iv

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download