SQL Query Language

A database management system (DBMS) is a separate program that acts as a server, independent of PHP.
You can create your own databases, tables and fill them with data directly from the same program, but to perform these operations, you first have to get acquainted with another programming language - SQL.

SQL or Structured Query Language (structured query language) is a programming language designed to manipulate data in a DBMS. All modern DBMSs support SQL.

All actions that can be performed with data are expressed in SQL: from writing and reading data to administering the DBMS server itself.
It is not at all necessary to know all this language for everyday work; it is enough to become familiar with the basic concepts of syntax and keywords. In addition, SQL is a very simple language in its structure, so its mastery will not be difficult.

SQL is primarily a query language and is very similar to natural language.
Every time when it is required to read or write any information to the database, it is required to compose a correct query. Such a query must be expressed in terms of SQL.

For example, to display all the records from a table города, write the following query:

READ ALL FROM TABLE 'cities'

If we translate this query into SQL, then the correct result will be:

SELECT * FROM 'cities'

Now let's write a request to add a городаnew city to the table :

INSERT INTO TABLE 'city' VALUES 'city name' = 'Saint Petersburg'

SQL translation:

INSERT INTO 'cities' SET 'name' = 'New york'

This command will create a new record in the 'city' table, where the 'city name' field will be assigned the value 'St. Petersburg'.

Using SQL, you can not only add and read data, but also:

• delete and update records in tables;
• create and edit tables themselves;
• perform operations on data: calculate the amount, get the largest or smallest value, and so on;
• configure the operation of the DBMS server.

MySQL

There are many different relational DBMSs available. The most famous DBMS is Microsoft Access, which is part of the office suite of Microsoft Office applications.
There are no obstacles to using MS Access as a DBMS, but an alternative program, MySQL, is much better suited for web programming tasks.
Unlike MS Access, MySQL is completely free, can run on Linux servers, and has much better performance and security, making it an ideal candidate for a database role in web development.
The overwhelming majority of PHP sites and applications use MySQL as a DBMS.

Installation

If you use the OpenServer software environment for your work, then you can safely skip this section, since the latest version of MySQL is already included in OpenServer.

The latest version of MySQL is available for download at the link: https://dev.mysql.com/downloads/mysql/
On this page, select "MySQL Installer for Windows" and click on the "Download" button to download.

During the installation process, remember the directory where you install MySQL (hidden under the "Advanced options" link).
At the “Accounts and Roles” step, the installer will ask you to come up with a password for accessing the database (MySQL Root Password) - be sure to remember or write down this password - you will still need it.

Executing queries

By default, MySQL does not have a graphical user interface unless you have installed additional programs. This means that the only way to work with it is by using the command line.

1. Open a command prompt (Run: cmd.exe).
2. Navigate to the directory with installed the MySQL: cd /d <каталог установки>/bin.
3. Follow: mysql -uroot -p.
4. Enter the password you specified during installation.

If you have done everything correctly, then the client for working with MySQL will start on the command line (you will understand this from the "mysql>" prompt line). From now on, you can enter any SQL queries, but each query must necessarily end with a semicolon;

Create database SQL statement: create a new database

Let's get down to practice - let's start creating a database for keeping a weather diary.
We should start by creating a new database for our site.
A new database in MySQL is created with a simple command: CREATE DATABASE

So, by executing the command CREATE DATABASE weather_diary;, MySQL will create a new database for us, in which all further work will take place.
This is important: after creating a database, it will not be possible to rename it, but only to delete and recreate it. For this reason, be very careful when choosing a name for your database.

Create table statement: creating tables

Having created a new database, we will inform MySQL that now we are going to work with it.
The selection of the active database is performed by the command:USE ;

It's time to create the first tables!
To keep a diary according to all the rules, you need to create three tables: cities (cities), users (users) and weather records (weather_log).
The Recording subsection of this chapter describes how the weather_log table structure should look like. Let's translate this description into SQL language:

CREATE TABLE weather_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  city_id INT,
  day DATE,
  temperature INT,
  cloud TINYINT DEFAULT 0
);

To enter a multi-line command on the command line, use a character \at the end of each line (except the last).

Now let's create a table of cities:

CREATE TABLE cities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name CHAR(128)
)

MySQL can show created table, if you ask about this command: SHOW COLUMNS FROM weather_log.
The response will list all the fields in the table, their type and other characteristics.

Primary key

In the example with creating a new table, when listing the required fields, the first field is id INT AUTO_INCREMENT PRIMARY KEY.
This field is called the primary key. It is imperative to create a primary key in every table.

A primary key is a special field that stores a unique identifier for a record. It is needed so that the programmer and the database always have the opportunity to unambiguously refer to one specific record to read, update or delete it.
If you assign a field as a primary key, the database will make sure that the value in this field is no longer repeated in the table.
And if you also add an attribute AUTO_INCREMENT, then MySQL will fill in this field itself when adding new records. AUTO_INCREMENTwill play the role of a counter - each new record in the table will receive a value one more than the maximum existing value.

Insert into statement: adding a record to a table

Let's start by adding new data to the table. The following syntax is used to add an entry:

insert into <table_name> set <column_name1> = <data>, <column_name2> = <data>...

First, let's add the city to the cities table:

insert into cities set name = 'Romania'

When adding a record, you do not need to provide values ​​for all fields. Many of the fields have default values ​​that are filled in on save.

Now let's create a weather record for today.
When defining the weather_log table, we decided to refer to the city by writing the city ID from the cities table into the city_id field. Since we just added a new city, there is nothing to prevent us from using its identifier in the weather record.
The city identifier will be the primary key, which was also defined as the first field in the table. The numbering of this field starts with one, which means that the first added record has an identifier 1. Knowing this, the request to add a record for the weather in St. Petersburg for September 3, 2017 looks like this:

INSERT INTO weather_log SET city_id = 1, day = '2017-09-03', temperature = 5, cloud = 1;

Select statement: reading information from the database

To display information from the database, queries of the type are used SELECT.
In the request, you need to specify the name of the table, the required fields, as well as additional parameters (will be discussed in the next lesson).

SELECT <columns_list> FROM <table_name>

For example, to get a list of all available cities:

SELECT id, name FROM cities

All weather records:

SELECT id, day, city_id, temperature, cloud FROM weather_log

Instead of listing all columns, you can use the asterisk - *.

Update statement: updating information in the database

It is very easy to make a mistake when adding a record: make a typo, omit a value for one of the fields, and so on.
Naturally, SQL offers the ability to edit existing records.

Suppose that when adding a weather record, the user made a mistake and entered the wrong date. To fix this error, you need to use the update operator - UPDATE.
A query with this operator allows you to update the value of one or more fields in an existing record. It looks like this:

UPDATE <table_name> SET <column_name1> = <value>, <column_name2> = <value>... WHERE <column_name> = <value>

But in order to correctly compose a query, it is necessary to define a condition for finding the record that is proposed to be updated. Otherwise, if you do not specify this condition, then absolutely all records in the table will be updated.
It is best to use the primary record identifier as such a condition. Therefore, before executing an update request, you need to execute a request to read information from the table in order to find out under which identifier the erroneous record was stored.
Let's say this ID is one and the correct date is September 5th, 2021.

Update request:

UPDATE weather_log SET day = '2021-09-05' WHERE id = 1

Join statement: joining records from two tables

In our table for storing the weather diary, the city is saved as an identifier, therefore, when reading data from this table, an incomprehensible number is used instead of the name of the city. To substitute a real value for a number, more specifically the name of a city, SQL has the join operators - JOIN.
Supports join operators and allows the database to be called relational .

Let's change the request for displaying weather records so that it combines two tables, and its name is displayed in the city field, and not the identifier:

SELECT day, cities.name, temperature, cloud FROM weather_log JOIN cities ON weather_log.city_id = cities.id

It is important to learn the three most important points:

1. When reading from joined tables, in the list of fields after SELECT, you must also explicitly indicate in the name field the name of the table with which the join is performed.
2. There is always a main table (tb1), from which most of the fields are read, and a join table (tb2), the name of which is determined after the JOIN operator.
3. In addition to specifying the name of the second table, be sure to specify the condition by which the join will take place. In this example, such a condition will be the correspondence of the city identifier from tb1 (weather_log.city_id) to the primary key of the city from tb2 (cities.id).