Beginners guide to MySQL and MariaDB

With MySQL you would either want to connect to using MySQL client to query the database you have created or connect programmatically using a programming language such as PHP or C# etc to manipulated data. 

MySQL Client

When you want to use MySQL client, you would normally issue the following command

> mysql -uroot -p [ENTER]

You will then be asked to enter root user password to get access to MySQL prompt. -u is followed by the name of the database user you wish to use. This must be an already existing account. root account is pretty much a super user can have access to everything. Here is how it looks when you are successfully logged in. 

There are heaps of options to go with mysql command, you can find all those details on this page

Connection using a scripting language PHP

We recommend using PDO to connect to MySQL databases from your PHP script. PDO can connect to heaps more databases than a traditional MySQLi  module. MySQLi's sole purpose is to connect to MySQL DB. However if you wish to use MySQLi, you can. There is no difference in terms of security or reliability when you connect to MySQL database.

MySQLi Installation

The MySQLi extension is automatically installed when you end up installing a PHP MySQL package.

For installation details, go to: http://php.net/manual/en/mysqli.installation.php

PDO Installation

If you wish to go with PDO, here are the installation details, go to: http://php.net/manual/en/pdo.installation.php

Using PDO for connecting to MySQL, here's a sample code 

<?php
$server = "localhost";
$user = "username";
$pwd = "password";

try {
    $connection = new PDO("mysql:host=$server;dbname=testDB", $user, $pwd);
    // PDO can throw exceptions rather than Fatal errors, so let's change the error mode to exception
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "connection successful"; 
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
?>

Closing the connection

Connection are automatically closed when your script execution ends, however you may want to close connection before that, which in many ways is a good practise as well, here is how its done

$connection->close();

or

$connection = null;

Connecting to MySQL using C#

You can connect to MySQL from pretty much any language you use. This is possible from of the wide variety of connectors already available. 

First you would need to download MySQL Connector/Net from MySQL website (https://dev.mysql.com/downloads/connector/net/6.9.html), Once downloaded, you will then install it. Installation instructions can be found here

Once all is set then opening a connection to your MySQL server is rather simple and is done using MySQLConnection class as shown below

MySql.Data.MySqlClient.MySqlConnection connection;
string connectionString;

connectionString = "server=127.0.0.1;uid=guest;" +
    "pwd=guiest123;database=testDB;";

try
{
    connection = new MySql.Data.MySqlClient.MySqlConnection();
    connection.ConnectionString =connectionString;
    connection.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show(ex.Message);
}

To close connection, you will have to call Close method of your connection as shown below

connection.Close();

Here is a quick video tutorial by ProgrammingKnowledge guys on Youtube

In the next part of this tutorial, we will explore how to create a database.