We analyze databases and the SQL language - "Java project from A to Z"

An article from a series about creating a Java project. Its goal is to analyze key technologies, the result is to write a telegram bot. The introductory part is here . Aloha, software engineers. Today we are talking about databases and the SQL language. Who is this article intended for? Yes to everyone who is interested. Some can start their journey with this article, others will be able to refresh their memory of interesting facts. This part will be purely theoretical. Before we tell you what databases and SQL syntax are, we need to understand and determine why we will study them. At this stage, we will talk ONLY about SQL databases: We will not consider NoSQL in this series of articles.

Database: what is it

A database (hereinafter referred to as DB) is a place where structured data is stored , which can be obtained using a query language. Databases can not only store, but also process and change information in large volumes. As a note: try quickly changing a set of values ​​in Excel based on some criteria. In principle, nothing complicated. Not like in our non-digitized real world. For example, renaming lanes, streets, cities. If everything were virtual and entered into the database, it would be normalized, it would be a couple of trifles. As it is, many storage media suffer because they cannot change automatically. In this context, SQL for databases is a language that the database understands and responds accordingly. Let's say, renaming the city would not be difficult; renaming Dnepropetrovsk to Dnipro would look something like this:

UPDATE city SET name = “Днипро” WHERE id = 1231;

All subsequent requests to the system would already produce the name that we need.

Why are databases needed?

As I already said, using databases you can store data. But what is this data? To understand using a real-life example, we can talk about some application. For example, about the same telegram bot. We want to know how many people are using the bot. How can this be implemented? In a regular Java application, you can create many unique elements - a Set, which will store the user's nickname or chat ID. Will this work? Will. Exactly until the Java application is stopped, and as soon as it is started again, the set of unique elements will be empty. That is, the data that was stored in the running application simply disappeared. What can you do about it? You can move the storage of system state (data) from a Java application somewhere else. You can store this in CSV format in a regular file on your computer. You can do it in one line in a separate file. You can do this and then overwrite this data before the Java application stops. Although the guarantee that the recording will be successful is far from close to 100%, because you can simply pull out the server’s power cord and the recording will not happen. This approach has a significant drawback: there is no functionality for obtaining, aggregating and searching information at the file level. Yes, of course, you can read a file and create objects for it, but you will have to pump all the data into the application EVERY time. And there can be a lot of them, for example, a couple of gigabytes. This can be avoided by storing data in a database. How? Through tables and relationships between them. This is what all relational databases are based on.

SQL: a language that databases understand

There are Database Management Systems (hereinafter referred to as DBMS) and the language they understand is SQL. SQL is a programming language for manipulating and managing databases. To understand how this works, let's look at the figure: The user sends SQL queries to the DBMS, the DBMS understands what needs to be done, does it, and if the request was to obtain data, then returns it. Therefore, as developers, we need to master the SQL query language. You might think, “Oh, another language. With God’s help, I barely understand Java here, and you’re immediately offering me another language.” This is not true: SQL was invented as a specification language for a DBMS so that accountants who are very far from databases and programming in general could write in this language. This means that learning it will not be that difficult. The main thing is PRACTICE-PRACTICE-PRACTICE. Today there will be theory, but the next article will be about practice. Since a relational database is a set of two-dimensional array-tables and relationships between them, the work will work around them. If we talk about an example, we can show two tables - “Countries” and “Cities” and how they are connected. At this stage, it is important for us to understand that the records in the table are data about some object from the Java world. For example, these two tables can be described in Java like this:

public class Country {
   private Long id;
   private String name;
}

public class City {

   private Long id;
   private String name;
   private Country country;
   private Integer population;
}

Well, isn't it beautiful, huh? The database can store any number of tables. In our case there are two of them.

Table structure

I think that everyone has come across Excel tables in one way or another, and you understand the terms row and column . In the context of databases, we talk more about records and fields: Thus, it turns out that each object of the City class is a record in a table in the database.

Primary key

It often happens that fields in databases have the same values. For example, social networks, where more than one person can be found by name, surname and even patronymic. And relational databases require a unique field that can be used to access a record. This is called the first key or Primary Key. Typically, the ID(id) field is used as such a key - this is short for identifier. This is why you need to add an ID field to every table .

External key

In our example, such a field is in the City table, which uses the key from Country. And it works like this: each city knows a unique information identifier for its country, and if we take it and create a query in the database, we will receive comprehensive information about the country. As you can see in the picture, there is a relationship between two tables: The principle of a foreign key is shown here.

What sections are there in SQL?

By the way, during interviews they are often asked what operations are in SQL:

• DDL (Data Definition Language) is a group of operators that change/create tables, their structure, and more. That is, creating a table, deleting it, creating/deleting fields in tables; creating a new primary key and so on;
• DML (Data Manipulation Language) is a group of operators that manage data modification. These are all operations that change data in the database: adding, getting, changing and deleting;
• DCL (Data Control Language) is a means of confirming user rights to perform actions. Operations to grant access and rights to a specific user so that he can perform DDL/DML operations.

What data types are there in SQL

Tables can store and process certain types of data. Everything here will be similar to what we use in Java. Let's talk about the main ones. There are only three of them, we will add others as needed and/or desired: As can be seen from the picture, these are:

• INT - integer values. Used for a unique identifier and for a simple integer;
• VARCHAR is our String ;
• DATE is our LocalDate.

So far everything is simple, right? Field types are specified when creating the table. It is clear that it will not be possible to write a value of a different type into the field.

What are SQL operators

An operator is a separate command that performs some action. Operators are made up of sentences, which in turn are made up of keywords that are used in queries. Let's look at an example: It's more interesting here, a query has already been built here. What are they doing there? It's simple, it says: “Take three fields (Name, Age and CreatedDate) from the Clients table, select only those records where name is equal to Roman.”

Conclusion

In this article we began to study databases. We understood what it is and why we need it. Next, we went over the first signs of SQL, which we will talk about in more detail in the next article. As usual, I suggest registering on GitHub and following my account to follow this series and my other projects that I work on there.

Homework

To add interest to this series of articles on JRTB, there will be homework assignments from time to time. For example, without today's assignment, the next article will be much more difficult to understand, because there will be a lot of practice there. Therefore, the task is to install the MySQL DBMS on your computer and log into the database either through the console or through other solutions. Thank you all for reading, see you soon!

A list of all materials in the series is at the beginning of this article.