SQL programming and databases

SQL is the language in which developers "communicate" with relational databases - adding and retrieving information, changing given relationships, transferring permissions, and so on. A relational database is considered if it stores several interconnected tables. The work of the vast majority of modern information products, from websites and mobile applications to corporate CRM systems, is somehow tied to the RDB.

Scope of SQL

The most common use of SQL is in a web environment. For example, it processes logins and passwords of site users stored in a database on the server. But knowledge of SQL will be useful not only for webmasters, but also for:

• testers to understand the logic of data interaction;
• internet marketers and analysts to work with large information clusters;
• network administrators to properly maintain the enterprise information environment.

Let's say right away that programs are not written in SQL as such. The SQL language is a tool for processing ready-made tables that operates within certain software. But there is also a certain plus of SQL in it. The syntax of this language is quite simple. Its basics will be clear to users without knowledge of other programming tools.

How queries work

In the world of relational databases, there are three basic concepts that you should understand before you begin to understand the SQL query language.

1. Client.

This is the tool through which the user enters their database query. Usually the client is a standard search string, like in Google or Yandex.

2. Database management system, or DBMS.

A program that provides communication between the user and the RDB. It is the DBMS that analyzes the user's request and looks for the answer in the database.

3. Database.

Directly storage of information. The database itself does not know how to interpret the data contained in it. She, like a blank sheet of paper, simply accepts everything that is written into it through the DBMS.

4. Table.

A database unit in which structured data is stored in the form of columns, rows and cells. When creating an SQL query, it goes through the following path: User - Client - Query to the database - DBMS - Database - Search table

Main database components

1. Relationship.

The table itself. The structure of the tables in the database is identical to the sheets of tables from Excel. They just don't have visualization.

2. Attribute.

A specific column in the table that describes one characteristic of an object - color, size, date added, etc.

3. Tuple.

A table row with its own individual number. One tuple always describes one object - for example, a product, a customer, or a company.

4. Domain.

Format of valid values ​​for attributes - numeric, alphabetic, etc.

5. Value.

A single element in the database that occupies a single cell in the table. For example, the price of a particular dress in the product catalog.

6. Key.

The work of relational databases is based on keys, that is, identifiers. Keys make it possible to link two tables. Let's take the simplest case. To form a line with a buyer's order, you do not need to write a specific name of the purchase in the “product” column. It is enough to specify the product id from the catalog table.

There are three types of keys in RDB:

• primary - primary identifier, which is usually set automatically.
• potential - a fallback identifier by which the string can be determined;
• outer - serves to link tables.

A foreign key in one table always corresponds to a key in another table that needs to be referenced.

Common DBMS and their differences

A database management system is a shell that developers and users use to manipulate information in tables on a server. The DBMS solves the following tasks:

• configuring database access rights;
• creating and deleting tables;
• data editing;
• transactions;
• RAM management.

For developers, both free open-source databases and paid options are now available. Let's analyze some of the most popular solutions.

MySQL

A functional and easy-to-learn DBMS. Most often used to manage websites, browser applications and CRM systems. MySQL provides a high level of data security and fast query processing. The software is completely free to distribute.

PostgreSQL

A more complex management system than MySQL. Due to the object-oriented principle, PostgreSQL provides ample opportunities for working with parameters in tables and for using subqueries. Another plus of the system is support for third-party plugins. Banks, industrial enterprises and large corporations use this system due to the extended functionality and modifiability of PostgreSQL.

Oracle Database

DBMS with a long history of development. The first version of this system was released in 1977. The DBMS from Oracle works according to the object-relational principle, which makes it as flexible and multitasking as possible. Other advantages of Oracle DB include cross-platform and high performance.

SQLite

A specific, simplified version of a DBMS. Due to its light weight and single-file structure, SQLite is the best solution for small websites and applications. The main disadvantage of SQLite is the impossibility of transferring the rights to use databases.

Basic SQL statements

The SQL language is a specific set of commands (operators) for managing a database. Basic SQL statements are easy to learn, especially if the user has a basic knowledge of English.

Let's consider a simple task.

We need to find a specific person in the database of buyers of the online store - Ivanova Maria Viktorovna. Use the SELECT statement and the WHERE pointer to search.

SELECT * FROM customers WHERE name = Ivanova Maria Viktorovna

There are often several search conditions in tasks. Suppose we need to select all buyers from Moscow in the database and sort their names alphabetically. To do this, simply match the operators.

SELECT * FROM customers WHERE city = 'Moscow' ORDER BY name

For convenience, all operators in SQL are divided into 4 large groups.

1. DDL - data definer commands. Through them, you can add new tables to the database or delete and edit existing ones.

• CREATE - create a new table;
• ALTER - changing a table in the database;
• DROP - demolishing a table from the database.

2. DML - data editor commands. With their help, tables are filled with specific rows, columns, and values. Using DML statements, you can, for example, organize an online store catalog or maintain a register of calls to technical support.

• SELECT - selection of data that meets the specified conditions;
• INSERT - entering new data into tables;
• UPDATE - updating current data;
• DELETE - clear data.

3. DCL - commands that define access rights to the database. Allows specific users to open or close the ability to modify data in tables.

• GRANT - granting rights to edit objects in the database;
• REVOKE - revoke valid access;
• DENY - a ban that takes precedence over the GRANT statement.
• TCL - commands for performing transactions, that is, sequential actions on the site or in the application. To successfully complete a transaction, all conditions must be met without error. Otherwise, the system will “roll back” to the initial state. Booking tickets and paying online are typical examples of transactions.
• BEGIN TRANSACTION - transaction start point;
• ROLLBACK TRANSLATION - moment of rollback if transaction conditions are not met;
• SAVE TRANSACTION - save point of transaction results.

Creating a simple database

You can try writing a small database using https://www.mycompiler.io/.

First of all, let's define the conditions. Let's say our database is a bookstore product catalog.

Using the CREATE TABLE statement, we will create an appropriate table for further adding books to it, indicating the genre and author.

CREATE TABLE books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  gener VARCHAR(180) NOT NULL,
  autor VARCHAR(180) NOT NULL
);

Pay attention to the following points. The INT AUTO_INCREMENT PRIMARY KEY statement automatically generates the primary key of a row (tuple). The VARCHAR parameter specifies the maximum value in bytes.

Next, we can add several books to the created table. It is most convenient to do this in one line, specifying the attributes to be filled in at the beginning in the desired order.

CREATE TABLE books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  gener VARCHAR(180) NOT NULL,
  autor VARCHAR(180) NOT NULL
);

INSERT INTO books (name,gener,autor) VALUES ('Lord of Rings', 'Fantasy', 'John R. R. Tolkien'), ('A Study in Scarlet', 'Detective', 'Arthur Conan Doyle'), (' IT', 'Horror', 'Steven King');

Now we can find the desired book in the database by specifying its genre or author. To do this, we use the already familiar SELECT statement.

SELECT * FROM books WHERE gener = 'Fantasy';

The result will be like this

We've looked at an elementary way to manually maintain a SQL database. Of course, Backend developers rarely write database tables from scratch. Usually they set up automatic filling of fields or load information from files. Basic SQL queries allow you to edit parameters and correct errors if necessary.

Conclusion

SQL is a language for managing relational databases. With its help, data of sites and applications stored on servers is recorded, structured and edited.

DBMS - a program for operating data in the RDB through the SQL language. The most common DBMS developers are MySQL, PostgreSQL and Oracle Database. Deep knowledge of SQL is the prerogative of backend web developers. But understanding the basics of the language will not hurt all IT-related professionals.