1. MySQL – Introduction

Understanding Databases

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 types of systems.

Nowadays, we use relational database management systems (RDBMS) to store and manage huge volumes of data. These are called relational databases because all the data is stored in different tables, and relations are established using primary keys or other keys known as foreign keys.

Relational Database Management System (RDBMS)

An RDBMS is software that:

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

RDBMS Terminology

• Database: A collection of tables with related data.
• Table: A matrix with data, similar to a simple spreadsheet.
• Column: Contains data of one type, e.g., a postcode column.
• Row: A group of related data, e.g., data of one subscription.
• Redundancy: Storing data twice to make the system faster.
• Primary Key: A unique key; a value cannot occur twice in one table, identifying one row.
• Foreign Key: The linking pin between two tables.
• Compound Key: A key consisting of multiple columns when one column is not sufficiently unique.
• Index: Resembles an index at the back of a book, used for faster data retrieval.
• Referential Integrity: Ensures a foreign key value always points to an existing row.

MySQL Database

MySQL is a fast, easy-to-use RDBMS used for many small and large businesses. It is developed, marketed, and supported by MySQL AB, a Swedish company. MySQL is popular for several reasons:

• Released under an open-source license, so it’s free to use.
• A powerful program handling a large subset of the functionality of expensive database packages.
• Uses a standard form of the well-known SQL data language.
• Works on many operating systems and with languages like PHP, PERL, C, C++, JAVA, etc.
• Works quickly and efficiently, even with large datasets.
• Very friendly with PHP, the most appreciated language for web development.
• Supports large databases, up to 50 million rows or more in a table, with a default file size limit of 4GB (extendable to 8 million terabytes if supported by the operating system).
• Customizable, with the open-source GPL license allowing programmers to modify MySQL to fit specific environments.

2. MySQL – Installation

All downloads for MySQL are located at MySQL Downloads. Pick the version number of the MySQL Community Server which is required along with the platform you will be running it on.

Installing MySQL on Linux/UNIX

The recommended way to install MySQL on a Linux system is via RPM. MySQL AB makes the following RPMs available for download on its website:

• MySQL – The MySQL database server manages the databases and tables, controls user access, and processes SQL queries.
• MySQL-client – MySQL client programs, which make it possible to connect to and interact with the server.
• MySQL-devel – Libraries and header files for compiling other programs that use MySQL.
• MySQL-shared – Shared libraries for the MySQL client.
• MySQL-bench – Benchmark and performance testing tools for MySQL.

These RPMs are built on a SuSE Linux system but generally work on other Linux variants.

Installation Steps:

1. Login to the system using the root user.
2. Switch to the directory containing the RPMs.
3. Install the MySQL server using the following command (replace the filename as appropriate):

This command installs the MySQL server, creates a MySQL user, configures the server, and starts it automatically.

Binaries are located in /usr/bin and /usr/sbin. Databases and tables are created in /var/lib/mysql.

Optional RPMs:

 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)

 use mysql;
Database changed

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

This will also create an entry in the user table. Remember: SQL statements must end with a semicolon (;).

The /etc/my.cnf File Configuration

Generally, this file should not be modified. A default configuration may look like this:

You can change the error log location, but avoid modifying other entries unless necessary.

Administrative MySQL Commands

Important commands to manage and interact with MySQL:

• USE Databasename: Selects a database to work with.
• SHOW DATABASES: Lists all accessible databases.
• SHOW TABLES: Lists tables in the selected database.
• SHOW COLUMNS FROM tablename: Displays details of a table's columns.
• SHOW INDEX FROM tablename: Shows indexes, including PRIMARY KEY.
• SHOW TABLE STATUS LIKE tablename\G: Displays table performance and status information.

4. MySQL – PHP Syntax

PHP provides various functions to access the MySQL database and to manipulate the data records inside the MySQL database. You would call PHP functions for MySQL in the same way you call any other PHP function.

The PHP functions for use with MySQL generally follow this format:

The second part of the function name describes the operation. Two common MySQLi functions used are:

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

The following is a generic example of calling a MySQL function from PHP:

Starting from the next chapter, we will explore all important MySQL functionalities integrated with PHP.

5. MySQL – Connection

Using Command Prompt

You can establish a connection to the MySQL database using the mysql binary at the command prompt.

Example

Here is a simple example to connect to the MySQL server from the command prompt:

This will give you the mysql> command prompt, where you can execute SQL commands.

Sample Output

In the above example, root was used as the username, but you can use any valid MySQL user. Each user will be able to perform the SQL operations permitted for their account.

You can disconnect from the MySQL database anytime by using the exit command at the mysql> prompt:

MySQL Connection Using PHP Script

PHP provides the mysql_connect() function to open a database connection. This function accepts five parameters and returns a MySQL link identifier on success or FALSE on failure.

Syntax

6. MySQL – CREATE DATABASE

After establishing a connection with MySQL, to manipulate data in it you need to connect to a database. You can connect to an existing database or create your own.

You need special privileges to create or delete a MySQL database. If you have access to the root user, you can create any database using the MySQL CREATE DATABASE statement.

MySQL CREATE DATABASE Statement

The CREATE DATABASE statement is a DDL (Data Definition Language) statement used to create a new database in MySQL RDBMS.

Note: On Linux/Unix, database names are case-sensitive. SQL keywords are not case-sensitive. On Windows, database names are not case-sensitive.

Syntax

Where DatabaseName is the name of the database you want to create.

Example

Ensure you have the necessary privileges before creating any database.

Verification

To check if the database has been created, use the SHOW DATABASES; command:

Expected output:

CREATE DATABASE with IF NOT EXISTS Clause

If you try to create a database that already exists, you will get an error:

To avoid this, use the IF NOT EXISTS clause:

Create Database Using mysqladmin

You can also create a database using the mysqladmin binary tool:

Example

This creates a MySQL database named TUTORIALS.

Creating Database Using a Client Program

Besides using SQL directly, you can also use a client program in languages like PHP, Node.js, Java, and Python.

PHP Example

To create a database using PHP, use the query() method of the mysqli object:

Complete PHP Example

Output

7. MySQL – DROP DATABASE

The DROP DATABASE statement is used to delete an existing SQL database.

Syntax

Note: Be careful before dropping a database. Deleting a database will result in loss of all information stored in it!

Example

The following SQL statement drops the existing database testDB:

The DROP DATABASE statement deletes the entire database, including:

• Tables
• Views
• Indexes
• Stored Procedures
• Constraints

Important Considerations

• Always back up the database before executing DROP DATABASE.
• Ensure no users or applications are connected to the database before dropping it.
• You must have the necessary privileges to execute the DROP DATABASE statement.

Syntax

Example: Create and Drop a Database

Create the database:

Verify creation:

Expected output:

Now drop the TUTORIALS database:

Output

Verify deletion:

Expected output (TUTORIALS no longer listed):

Dropping a Database Using mysqladmin

Use the mysqladmin tool if you have root privileges:

This will show a warning asking for confirmation:

Output:

Dropping a Database Using a Client Program

You can drop a database using client-side code written in PHP, Node.js, Java, or Python.

PHP Example

Use mysqli::query() to execute the DROP DATABASE SQL statement:

Output

8. MySQL – SELECT DATABASE

Once you get connected with the MySQL server, it is required to select a database to work with. This is because there might be more than one database available on the MySQL Server.

MySQL USE Statement

To select a database in MySQL, we use the SQL USE statement. Once a specific database is selected, we can perform different operations such as creating tables, adding data, updating, and deleting information. Every operation we perform after selecting a database will be stored in that particular database.

Syntax

Here, the DatabaseName is a placeholder representing the name of the database that we want to use.

The database name must always be unique within the MySQL or any other RDBMS.

Example

Create a database named TUTORIALS using:

Fetch all databases using:

Output:

• information_schema
• mysql
• performance_schema
• tutorials

Select the database:

Output: Database changed

Create a Table

List all tables in the current database:

Output:

• customers

Selecting a Non-existing Database

If we try to select/switch a non-existent database, it will result in an error:

Output:

Selecting Database Using a Client Program

Besides switching a database using SQL query, we can also use client programs like PHP, Node.js, Java, or Python.

PHP Example

connect_errno ) {
    printf("Connect failed: %s<br />", $mysqli->connect_error);
    exit();
}
printf("Connected successfully.<br />");

if ($mysqli->query("USE TUTORIALS")) {
    printf("Database selected successfully...!<br />");
}
if ($mysqli->errno) {
    printf("Database could not connect: %s<br />", $mysqli->error);
}
$mysqli->close();
    

Output

• Connected successfully.
• Database selected successfully...!

9. MySQL – DATATYPES

MySql 8.0 Each column in a database table must have a name and a data type. The data type defines the kind of data that can be stored in the column and how MySQL will process that data.

In MySQL, there are three main data types: String, Numeric, and Date and Time.

String Data Types

Numeric Data Types

Note: Numeric types can have UNSIGNED (no negatives) and ZEROFILL (pads with zeros and implies UNSIGNED).

Date and Time Data Types

10. MySQL – CREATE TABLES

The CREATE TABLE statement is used to create a new table in a database.

Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ....
);
  

The column parameters specify the names of the columns of the table.

The datatype parameter specifies the type of data the column can hold (e.g., varchar, integer, date, etc.).

MySQL CREATE TABLE Example

The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);
  

The PersonID column is of type int and will hold an integer.

The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.

The empty "Persons" table will now look like this:

Create Table Using Another Table

A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns can be selected.

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

Syntax

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;
  

The following SQL creates a new table called "TestTable" (which is a copy of the "Customers" table):

CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;
  

11. MySQL – DROP TABLES

The DROP TABLE statement is used to drop (delete) an existing table from a database.

Syntax

DROP TABLE table_name;
  

MySQL DROP TABLE Example

The following SQL statement drops the existing table named "Shippers":

DROP TABLE Shippers;
  

MySQL TRUNCATE TABLE Statement

The TRUNCATE TABLE statement is used to delete all rows from a table without deleting the table itself.

Syntax

TRUNCATE TABLE table_name;
  

12. MySQL – INSERT QUERY

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways:

1. Specify Column Names

2. Without Specifying Column Names

If you are adding values for all the columns of the table, you do not need to specify the column names. Ensure the order of values matches the table columns:

Demo Database

Below is a selection from the Customers table in the Northwind sample database:

INSERT INTO Example

The following SQL statement inserts a new record in the Customers table:

The selection from the "Customers" table will now look like this:

Note: We did not insert any number into the CustomerID field. It is an auto-increment field and is generated automatically.

Insert Data Only in Specified Columns

It is also possible to only insert data into specific columns:

Now the table will look like this:

13. MySQL – SELECT QUERY

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax

Here, column1, column2, etc., are the field names of the table you want to select data from.

If you want to select all the fields available in the table, use the following syntax:

Demo Database

In this tutorial, we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

SELECT Columns Example

The following SQL statement selects the "CustomerName", "City", and "Country" columns from the "Customers" table:

SELECT * Example

The following SQL statement selects ALL the columns from the "Customers" table:

MySQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values. Sometimes you only want to list the different (distinct) ones.

SELECT DISTINCT Syntax

SELECT Example Without DISTINCT

The following SQL statement selects all (including the duplicates) values from the "Country" column in the "Customers" table:

SELECT DISTINCT Examples

This SQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table:

The following SQL statement counts and returns the number of different (distinct) countries in the "Customers" table:

14. MySQL – WHERE CLAUSE

The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified condition.

WHERE Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;
  

Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

WHERE Clause Example

The following SQL statement selects all the customers from Mexico:

SELECT * FROM Customers
WHERE Country = 'Mexico';
  

Text Fields vs. Numeric Fields

SQL requires single quotes around text values (most database systems also allow double quotes).

However, numeric fields should not be enclosed in quotes:

SELECT * FROM Customers
WHERE CustomerID = 1;
  

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

15. MySQL – UPDATE QUERY

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  

Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

UPDATE Table

The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.

The selection from the "Customers" table will now look like this:

UPDATE Multiple Records

The WHERE clause determines how many records will be updated.

The following SQL statement updates the PostalCode to 00000 for all records where country is "Mexico":

The selection from the "Customers" table will now look like this:

UPDATE Customers
SET PostalCode = 00000;
  

The selection from the "Customers" table will now look like this:

16. MySQL – DELETE QUERY

The DELETE statement is used to delete existing records in a table.

DELETE Syntax

DELETE FROM table_name WHERE condition;
  

Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

SQL DELETE Example

The following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table:

The "Customers" table will now look like this:

Delete All Records

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will remain intact:

DELETE FROM table_name;
  

The following SQL statement deletes all rows in the "Customers" table, without deleting the table:

DELETE FROM Customers;
  

17. MySQL – LIKE CLAUSE

LIKE is used in WHERE to search for a specified pattern in a column...

18. MySQL – SORTING RESULTS

ORDER BY sorts the result-set in ascending or descending order...

19. MySQL – USING JOIN

JOIN combines rows from two or more tables based on a related column...

20. MySQL – NULL VALUES

NULL represents missing or unknown data. Use IS NULL or IS NOT NULL...

21. MySQL – REGEXPS

REGEXP allows pattern matching with regular expressions in SQL queries...

22. MySQL – TRANSACTIONS

Transactions are a sequence of SQL statements executed as a single unit...

23. MySQL – ALTER COMMAND

ALTER TABLE modifies an existing table structure, such as adding columns...

24. MySQL – INDEXES

Indexes speed up retrieval of rows by using pointers to where data is stored...

25. MySQL – TEMPORARY TABLES

TEMPORARY tables are session-specific and deleted automatically...

26. MySQL – CLONE TABLES

Clone a table by copying structure and optionally data to a new table...

27. MySQL – DATABASE INFO

Retrieve metadata such as table structure, number of rows, etc...

28. MySQL – USING SEQUENCES

Use AUTO_INCREMENT columns to simulate sequences in MySQL...

29. MySQL – HANDLING DUPLICATES

Techniques to identify, avoid, or remove duplicate records...

30. MySQL – SQL INJECTION

SQL injection is a major security threat; always sanitize user input...

31. MySQL – DATABASE EXPORTS

Export data using SELECT ... INTO OUTFILE or mysqldump...

32. MySQL – DATABASE IMPORT

Import data using LOAD DATA INFILE or mysqlimport...