Understanding databases and SQL. (Part 3) - "Java project from A to Z"

An article from a series about creating a Java project (links to other materials are at the end). Its goal is to analyze key technologies, the result is to write a telegram bot.

• Checking homework
  • Adding a primary key
  • Adding Moldova
  • Create the cities table
  • Adding a foreign key to cities
  • Bonus part. Testing
• SELECT statement
  • WHERE Parameter
  • ORDER BY parameter
  • GROUP BY parameter
• Homework
• Conclusion

Hello, ladies and gentlemen, we continue to talk about the database, SQL and more. In today's material there will be part theory and part practice. Let me remind you that last time we talked about how to set everything up, how to create a database, a table and get data from it. It's time to see if it worked out with the DZ. According to my feelings, half could be done only based on the last article. It turned out that in order to properly build an application and make everything more or less beautiful, you need to talk about databases, and to talk about them, you need to spend a lot of time.

Checking homework

To everyone who successfully completed the tasks - a huge respect. This means that you understand that only you need it and it helps only you. For those who have neglected my task, let me remind you of the condition:

1. You need to add a primary key (PRIMARY KEY) from the ID field to the schema of the country table.
2. Add another country to the country table - Moldova.
3. According to the scheme of the previous article, create a table city, in which there will be all the fields that are described. The field names will be the following: id, name, country_id, population.
4. Add a primary key to the city table.
5. Add a foreign key to the city table.

To get started, let's use the first part of the last article and go to the database terminal.

Adding a primary key

There are two ways to add a primary key (PRIMARY KEY): immediately when creating a table, and also after creating it using ALTER TABLE.

Primary key during table creation

Since we have already created a table, and without deleting it, we will not be able to show this approach within this database, we will simply create a temporary test database in which we will do everything. Let's enter the following commands:

• create a new database:

  $ CREATE DATABASE test;

• create a table with a primary key added:

  $ CREATE TABLE country(id INT, name VARCHAR(30), PRIMARY KEY (id));

In general, nothing complicated. After the declaration of variables, the following part PRIMARY KEY (id) is added , where the name of the field that will be the primary key is passed in brackets. And we look at how the table schema has changed: $ DESC country; As you can see, the PRI value has appeared in the Key field for the id entry .

Primary key after table creation

As I said earlier, the first key after creating a table can be assigned using ALTER TABLE . We will execute this example in our cities database :

• let's go to our database from the test one:

  $ USE cities;

• check that we are exactly in our database (there should be one more field - population). To do this, we write:

  $DESC population;

that's right, our table. Let's write the following:

$ ALTER TABLE country ADD PRIMARY KEY(id);

• and check immediately with the command:

  $DESCcountry;

As you can see from the picture, everything is correct, the PRI value is exactly where it should be. By the way, after all, we worked with a test database. Now it needs to be removed: why do we need to litter the server, right? To do this, we use the already quite well-known command: $ DROP DATABASE test;

Adding Moldova

First we need to decide what we will record. The next ID will be 4. The name will be Moldova, and its population is 3550900. Therefore, we execute the INSERT INTO command we already know: $ INSERT INTO country VALUES (4, 'Moldova', 3550900); And we check whether this value is exactly in the database: $ SELECT * FROM country WHERE id = 4; In the request for data, I immediately determined which field to search for, so we received only one record, which we needed.

Create the cities table

According to the scheme from the first article about the database, we will get the necessary information about the table. It will contain the following fields:

• id - unique identifier;
• name - city name;
• country_id - foreign key of the country;
• population - population of the city.

Each time writing a unique ID is somehow stressful, don't you think? I would like to give it to the MySQL authorities . And there is such a way - AUTO INCREMENT . We need to add this to the numeric field, and if we do not explicitly pass the values, MySQL itself will increase the ID by one compared to the previous one. So the table creation would look like this: $ CREATE TABLE city ( id INT AUTO_INCREMENT, name VARCHAR(30), country_id INT, population INT, PRIMARY KEY (id)); Let's look at the table schema to see if everything was done correctly: $ DESC city;As you can see from the table schema, we have a new description for the id field - auto_increment. So we did everything right. Let's check the data already on a fully configured table. To do this, let's do the last part of the task - a foreign key.

Adding a foreign key to cities

For a foreign key, the command would be: $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); And immediately check what is there with the table schema: has it changed in an hour? $DESCcity;

Bonus part. Testing

I forgot to add to the task - to fill in the data that were in the screen of the first part. I forgot, so now I'll do it myself. And for those who are interested, you can do it yourself without me and then we will check;) There were Kharkov, Kyiv, Minsk, Odessa, Voronezh, and we will add Chisinau. But this time we won't send IDs, we'll skip them: $ INSERT INTO city (name, country_id, population) VALUES ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk', 3, 2545500), ('Odessa', 1, 1017699), ('Voronezh', 2, 1058261), ('Kishinev', 4, 695400); As you can see, it is possible to make several records at the same time through one INSERT INTO command. A handy thing, remember) And immediately see what's in the table: $ SELECT * FROM city; AUTO_INCREMENT- worked exactly as we wanted. ID-shniks are all filled, although we did not transfer them. A foreign key is a dependent thing. To check if it works correctly, you can try to write a foreign key that is not in the foreign table. Let's say we decide that id = 5 is Kazakhstan. But in reality it is not in the table of countries. And to check that the database will swear, let's add the city - Astana: $ INSERT INTO city (name, country_id, population) VALUES ('Astana', 5, 1136156); And we naturally get an error: Now the foreign key makes sure that we do not try to assign a country to the city that is not in our database. On this part of the homework can be considered completed - forward to the new one :)

SELECT statement

Well, it doesn't seem so scary anymore, does it? I want to note once again that for Java developers, knowledge of the database is a must have. No DB anywhere. Yes, I already want to start writing an application, I agree. But it is necessary. So we will continue like this. Using the SELECT statement, we get data from the database. That is, this is a typical DML operation (have you already forgotten what it is?...))) Re-read the articles BEFORE). Why are relational databases good? They have a huge functionality for aggregating and getting data. This is what the SELECT statement is for. It would seem, but what could be difficult there, right? But it turns out that it’s still decent to understand) It is important for us to understand the basics from which it will already be possible to build on. The simplest query with a SELECT statement is to select all data from one table. I really liked the description from the wiki about

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

Here you can see that you cannot put the GROUP BY operator first, and then the WHERE operator. This must be remembered so that later there is no resentment for mistakes that are not clear where they come from. $ SELECT * FROM city; But it’s clearly not cool for us to scoop out all the data. This is exactly the same if we wanted to hammer nails with a microscope [1] , [2] . Since the database performs much faster filtering, sorting and aggregation operations than Java code, it is better to leave the database at its mercy. Therefore, by complicating the tasks, we will open a new functionality.

WHERE Parameter

To filter the selection, the WHERE word is used . This should be interpreted as follows: SELECT * FROM tablename (select all fields from table tablename) WHERE talbe_row = 1 (where table_row is 1 in the records). It is important to note that the order of keywords in a query is important. You cannot write WHERE a =1 FROM table_name SELECT *. For the Russian language, this is ok, and for some it may not seem so zashkvar, but for SQL this is unacceptable. We write the following query: $ SELECT * FROM city WHERE country_id = 1; And we chose Ukrainian cities. Not bad, right? And if we want not only Ukrainian, but also Belarusian? For this case, we can enumerate the collection of values ​​that the field can take: $ SELECT * FROM city WHERE country_id IN (1, 3);And already in the answer we have cities from two countries. But what if there are multiple conditions for filtering? Let's say we want a city with a population of more than two million? To do this, use the words OR and AND : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000; Great, but what if we need to add one more condition - to search for names through a regular expression (I will not describe regular expressions here: here is a person who did it “shortly” in 4 parts )? For example, we remember how the city is spelled, but not completely ... To do this, you can add the LIKE keyword in the filter expression : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%”;And in this way we also got Kharkov. As a result, we can say that we have a very good search turned out. But I would like to sort not by ID-shnik, but by population, but how? Yes, it's very simple...

ORDER BY parameter

With the help of ORDER BY, we can sort the records that we received by a specific field. It sorts both numbers and strings. Extend the previous query to sort by population by adding ORDER BY population: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%” ORDER BY population; As we can see, the sorting took place in natural order, that is, in ascending order. What if we want the opposite? To do this, add the word DESC: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%” ORDER BY population DESC; Now sorting is in order to decrease the population. And the database does this very quickly: there are no Collections.sortare not compared. Now sort by row, by name in reverse order: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%” ORDER BY name DESC;

GROUP BY parameter

Used to group records by specific fields. Usually it is necessary to apply aggregate functions... And what are aggregate functions?)) It makes sense to group by some fields if they are the same for different records. Let's see what we mean with our example. Let's say cities have foreign keys - country IDs. So, the ID is the same for cities from the same country. Therefore, you can take and group records by them: $ SELECT country_id, COUNT(*) FROM city GROUP BY country_id; But without aggregating functions, it looks somehow faded, agree. So let's take a look at some of the most common features:

• COUNT - the number of records, without grouping, used as COUNT(*) . In the case of grouping by some field - COUNT(groupped_field);
• MAX - finds the maximum value for a specific field;
• MIN - finds the minimum value for a specific field;
• SUM - finds the amount for a specific field;
• AVG - finds the average value.

In general, these functions can be used without grouping, only then only one field will be displayed. Let's try them for our population of cities: $ SELECT COUNT(*) FROM city; What they asked for, they got. Just the number of entries. Sometimes this is useful. For example, if we need to find out the number of articles by some author. No need to rake them out of the database and count them already. You can just use COUNT(). $ SELECT AVG(population) FROM city; $ SELECT MIN(population) FROM city; And this is where grouping comes into play. For example, the task is to get the smallest city in the country. Already know how to do it? Try it yourself, then see: $ SELECT country_id as Country, MIN(population) FROM city WHERE GROUP BY country_id;So far, we only see the country's ID, but it doesn't matter - next time we'll do everything. And so there is already a result, and we got what we wanted - the smallest city in the country with ID = 1. The rest of the functions will be the same. It is important to note that it will not work to rake out all fields through * when using grouping and aggregation! Think about it ;)

Homework

Based on the results of previous articles, it is clear that homework is coming in, so let's continue)) Yes, everyone who makes homework - we continue to put “+” in the comments. It is important for me that the topic of homework is interesting for you, so that I continue to do it further. Yes, I read your comments regularly. I answer, of course, less often. I saw that they asked to give more difficult tasks in SQL. Until we learn the joins, there will be no interesting problems, so there will be those that I need for further material.

Tasks:

Understand the HAVING operator and write an example query for the tables from our example. If you need to add some fields or more values ​​​​to make it clearer - add. Who wants to - write your example solution in the comments: so I can also check it if I have time.
1. Install MySQL Workbench to work with the database through the UI. I think we've had enough practice with working from the console. Connect to DB. If you use something else to work with the database, feel free to skip this task. Hereinafter, I will only use MySQL Workbench.
2. Write requests for obtaining according to our data:
   1. the smallest/most numerous country;
   2. the average number of inhabitants in the country;
   3. the average number of inhabitants in countries whose names end with “a”;
   4. the number of countries with a population of more than four million;
   5. sort countries by decreasing number of inhabitants;
   6. sort countries by name in natural order.

Conclusion

Today we have analyzed in detail the homework from the last lesson. Moreover, I consider this important both for those who did not make it, and for those who did. For the first, this is an opportunity to find out the answer, and for the second - to check with your result. Subscribe to my github account to be aware of the changes in the project. I will maintain the entire code base there. Everything will take place in this organization . Next, we discussed the SELECT statement. It is the most important for us. It is through it that all requests for obtaining data will pass, and we must understand it. The most important thing is to remember the order in which parameters are added (WHERE, ORDER BY, GROUP BY, and so on). Yes, I did not tell everything that is possible, but I did not set such a goal for myself. Yes, I know that you are already eager to write an application. Be patient, it's all you need. Both for the project and for your professional growth. While you wait, make sure Git is already familiar to you. I will use it by default, as a well-known tool. Thanks everyone for reading. In the next article, we'll talk about database relationships and joins. That's where the cool tasks will be))

List of all materials in the series at the beginning of this article.