MySQL is the most popular Open Source Relational SQL Database Management System. MySQL is one of the best RDBMS being used for developing various web-based software applications. MySQL is developed, marketed and supported by MySQL AB, which is a Swedish company. This tutorial will give you a quick start to MySQL and make you comfortable with MySQL programming.

This basic MySQL tutorial explains some of the basic SQL statements. If this is the first time you have used a relational database management system, this tutorial gives you everything you need to know to work with MySQL such as querying data, updating data, managing databases, and creating tables.

If you are already familiar with other relational database management systems such as PostgreSQL, Oracle, and Microsoft SQL Server,  you can use this tutorial to refresh your knowledge and understand how SQL dialect of MySQL is different from other systems.

SQL Server Editions

The following table describes the editions of SQL Server.

Section 1. Getting started with MySQL

This section helps you get started with MySQL. We will start installing MySQL, downloading a sample database, and loading data into the MySQL server for practicing.

• Installing MySQL database server – show you step by step how to install MySQL database server on your computer.
• Downloading MySQL sample database – introduce you to a MySQL sample database named classicmodels. We also provide you links for downloading the sample database and its diagrams.
• Loading the sample database into your own MySQL database server – walk you through steps of how to load the classicmodels sample database into your MySQL database server for practicing.

Section 2. Querying data

This section helps you learn how to query data from the MySQL database server. We will start with a simple SELECT statement that allows you to query data from a single table.

• SELECT – show you how to use simple SELECT statement to query the data from a single table.

Section 3. Sorting data

• ORDER BY – show you how to sort the result set using ORDER BY clause. The custom sort order with the FIELD function will be also covered.

Section 4. Filtering data

• WHERE – learn how to use the WHERE clause to filter rows based on specified conditions.
• SELECT  DISTINCT – show you how to use the DISTINCT operator in the SELECT statement to eliminate duplicate rows in a result set.
• AND – introduce you to the AND operator to combine Boolean expressions to form a complex condition for filtering data.
• OR– introduce you to the OR operator and show you how to combine the OR operator with the AND operator to filter data.
• IN – show you how to use the IN operator in the WHERE clause to determine if a value matches any value in a list or a subquery.
• BETWEEN – show you how to query data based on a range using BETWEEN operator.
• LIKE  – provide you with technique to query data based on a specific pattern.
• LIMIT – use LIMIT to constrain the number of rows returned by SELECT statement
• IS NULL – test whether a value is NULL or not by using IS NULL operator.

What is a Database?

A database is a separate application that stores a collection of data. Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds.

Other kinds of data stores can also be used, such as files on the file system or large hash tables in memory but data fetching and writing would not be so fast and easy with those type of systems.

Nowadays, we use relational database management systems (RDBMS) to store and manage huge volume of data. This is called relational database because all the data is stored into different tables and relations are established using primary keys or other keys known as Foreign Keys.

A Relational DataBase Management System (RDBMS) is a software that −

• Enables you to implement a database with tables, columns and indexes.
• Guarantees the Referential Integrity between rows of various tables.
• Updates the indexes automatically.
• Interprets an SQL query and combines information from various tables.

RDBMS Terminology

Before we proceed to explain the MySQL database system, let us revise a few definitions related to the database.

• Database − A database is a collection of tables, with related data.
• Table − A table is a matrix with data. A table in a database looks like a simple spreadsheet.
• Column − One column (data element) contains data of one and the same kind, for example the column postcode.
• Row − A row (= tuple, entry or record) is a group of related data, for example the data of one subscription.
• Redundancy − Storing data twice, redundantly to make the system faster.
• Primary Key − A primary key is unique. A key value can not occur twice in one table. With a key, you can only find one row.
• Foreign Key − A foreign key is the linking pin between two tables.
• Compound Key − A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique.
• Index − An index in a database resembles an index at the back of a book.
• Referential Integrity − Referential Integrity makes sure that a foreign key value always points to an existing row.

MySQL Database

MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. MySQL is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons −

• MySQL is released under an open-source license. So you have nothing to pay to use it.
• MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
• MySQL uses a standard form of the well-known SQL data language.
• MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.
• MySQL works very quickly and works well even with large data sets.
• MySQL is very friendly to PHP, the most appreciated language for web development.
• MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).
• MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments.

PHP on MySQL

The PDO is a data-access abstraction layer. It is a PHP extension that provides a lightweight and consistent interface for interacting with any databases including MySQL.

Creating A New Table – this tutorial shows you step by step how to create a table by using PHP PDO object.

Querying Data from Database – you will learn how to query data from MySQL database by using PHP PDO and use PDO prepared statement to select data securely.

Inserting Data Into a Table – this tutorial shows you how to use PHP to insert data into MySQL a table.

Updating Data – this tutorial shows you how to update data in a MySQL table using PHP PDO prepared statement.

Deleting Data – in this tutorial, you are going to learn how to delete data from the MySQL database table using PHP PDO.

Calling MySQL Stored Procedures – this tutorial shows you how to call MySQL stored procedures using PHP PDO. It guides you on how to call stored procedures that return result sets and stored procedures that accept input/output parameters.

Dealing with transactions – in this tutorial, we will show you how to handle MySQL transactions in PHP to ensure data integrity of databases. In addition, we will give you an example of the money transfer transaction between two bank accounts for the demonstration.

mysql_function(value,value,...);

mysqli_connect($connect);
mysqli_query($connect,"SQL statement");

<html>
   <head>
      <title>PHP with MySQL</title>
   </head>
   
   <body>
      <?php
         $retval = mysql_function(value, [value,...]);
         if( !$retval ) {
            die ( "Error: a related error message" );
         }
         // Otherwise MySQL  or PHP Statements
      ?>
   </body>
</html>

ps -ef | grep mysqld

root@host# cd /usr/bin
./safe_mysqld &

root@host# cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user 
   (host, user, password, 
   select_priv, insert_priv, update_priv) 
   VALUES ('localhost', 'guest', 
   PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
|    host   |   user  |     password     |    
+-----------+---------+------------------+
| localhost |  guest  | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
   -> ON TUTORIALS.*
   -> TO 'zara'@'localhost'
   -> IDENTIFIED BY 'zara123';

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

[mysql.server]
user = mysql
basedir = /var/lib

[safe_mysqld]
err-log = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid