Lesson 01: Introduction to MySQL

	Introduction to MySQL

Fundamentals of MySQL

Introduction to Databases

A database is a list or a group of lists. The list or each list contains a series of values. The list is well organized so that values can be added to it and values can be easily retrieved or found. The list should also be easy to use and manage.

MySQL as a Computer Database System

MySQL is a computer application used to create and manage electronic databases. There are various ways you can use MySQL. You can download MySQL from its website (http://www.mysql.com) and install it in your computer.

Instead of using MySql locally installed on your computer, you can use it remotely on a computer server. This time too, there are different ways you can do it. One way you can use MySQL remotely is from a website. One way you can do this is to get a website hosted by a company and that website or the company would offer MySQL. In most cases, if you get a website hosted on a Linux operating system, the company would make sure MySQL is installed and running.

For our lessons, we will use use MySQL installed in a web server of a website hosted on Linux. We will also use the PHP scripting language (running on an Apache web server).

MySQL Server Credentials

There are many basic pieces of information you need in order to access a web server and its databases. The most fundamental piece of information about a server is its name. In order to log in and perform some operations, you must provide a username and a password. Normally, when you install a server, you must provide a primary username and its password. Later on, you can create additional usernames and passwords. If you don't know how, you can't, or you are not allowed to do that, you can ask your network administrator or your web host either to create the account(s) for you or to show you how, or to give you the ability (permissions) to create accounts.

Introduction to the Structured Query Language

The Structured Query Language, or SQL, is the language used to give instructions to the MySQL database server about anything related to a database. An instruction is also called a SQL statement. The SQL is divided in two sub-languages named DML and DDL. To provide the ability to create and manipulate a database, you use a sub-language named Data Manipulation Language, or DML. The DML is used to create and manage databases. The Data Definition Language, or DDL, is used to create commands to submit to the database server for execution.

MySQL and PHP

Introduction

As mentioned in our introduction, you can use MySQL either on your computer or on a remote server. We also mentioned that, for our lessons, we will use MySQL directly on a website. To make this possible, you can use a scripting language such as PHP. PHP provides many classes and functions.

The most fundamental class used in PHP for MySQL databases is named mysqli. PHP also provides many functions for all types of database operations.

A Connection to MySQL

In order to use MySQL, you must create a connection to the server. To assist you with this, the constructor of the mysqli class can be used. Its syntax is:

__construct([ string $host     = ini_get("mysqli.default_host") [,
              string $username = ini_get("mysqli.default_user") [,
              string $passwd   = ini_get("mysqli.default_pw") [,
              string $dbname   = "" [,
              int    $port     = ini_get("mysqli.default_port") [,
              string $socket   = ini_get("mysqli.default_socket")
            ]]]]] ])

As you can see, all arguments are optional as each has a default value. The $host argument is the name of the server. In many cases, you can set it as localhost. The $username and the $passwd arguments are the credentials to log in to the database server. If you are creating a database, those three pieces of information are the only ones necessary.

Here is an example of connecting to a databasse server:

mysqli("localhost", "usermine", "P@s$w0rd1");

You can save this call by storing it in a variable. Here is an example:

$link = new mysqli("localhost", "usermine", "P@s$w0rd1");

Passing a SQL Statement to MySQL

In order to perform an action on a database, you can create a SQL statement and send it to the databasse. To support this, PHP provides a function named mysqli_query. Its syntax is:

mixed mysqli_query(mysqli $link, string $query[, int $resultmode = MYSQLI_STORE_RESULT])

The first argument is a connection to the database server. It can be a value or variable produced from using the constructor of the mysqli class. As an alternative to the mysqli_query() function, the mysqli class is equipped with a method named query. Its syntax is:

mixed mysqli::query(string $query [, int $resultmode = MYSQLI_STORE_RESULT])

The argument of the mysqli::query() method and the second argument of the mysqli_query() function is the statement that must be executed.

Creating a Database

In order to use a database, you must create one. The basic formula to create a database is:

CREATE DATABASE database-name

In this simplified formula, you start with the CREATE DATABASE expression followed by a name for the database. Here is an example:

<!DOCTYPE html>
<html>
<head>
<title>Database Administration: Database Creation</title>
</head>
<body>
<h2>Database Administration: Database Creation</h2>

<?php
$connection = new mysqli("localhost", "usermine", "P@s$w0rd1");

if(mysqli_connect_errno())
{
    echo "The connection to the server could not be made.";
    exit();
}

if($connection->query("CREATE DATABASE Exercise1") === true)
{
    echo "<p>A database named Exercise1 has been created.</p>";
}
else
    echo "Something went wrong when trying to create the database.";

$connection->close();
?>

</body>
</html>

A Connection to a MySQL Database

In order to use a MySQL database or perform an operation on it, you must establish a connect to the database. This is done using the constructor of the mysqli class as seen earlier. We saw that its syntax was:

__construct([ string $host     = ini_get("mysqli.default_host") [,
              string $username = ini_get("mysqli.default_user") [,
              string $passwd   = ini_get("mysqli.default_pw") [,
              string $dbname   = "" [,
              int    $port     = ini_get("mysqli.default_port") [,
              string $socket   = ini_get("mysqli.default_socket")
            ]]]]] ])

To create a connection to the database, add the 4^th argument as the name of the database. An example would be:

<!DOCTYPE html>

<html>
<head>
<title>Database Administration: Table Creation</title>
</head>
<body>
<h2>Database Administration: Table Creation</h2>

<?php

$link = mysqli_connect("localhost", "usermine", "P@s$w0rd1", "Exercise1");

?>

</body>
</html>