We analyze databases and the SQL language. (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
  • Add a foreign key to cities
  • Bonus part. Testing
• SELECT statement
  • WHERE parameter
  • ORDER BY parameter
  • GROUP BY parameter
• Homework
• Conclusion

Hello, ladies and gentlemen, let's continue to talk about databases, SQL and other things. Today's material will contain 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 anything worked out with the remote sensing. In my opinion, half of it could have been done only based on the previous article. It turned out that in order to properly assemble 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

Huge respect to everyone who successfully completed the tasks. This means you understand that only you need this and it only helps 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 country table schema.
2. Add another country to the country table - Moldova.
3. According to the scheme of the previous article, create a table city, which will contain all the fields that are described. The field names will be as follows: 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 previous article and go to the database terminal.

Adding a primary key

You can add a primary key (PRIMARY KEY) in two ways: immediately when creating a table, or after creation, 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 adding a primary key:

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

In general, nothing complicated. After declaring the variables, the following part PRIMARY KEY (id) is added , where the name of the field that will be the primary key is passed in parentheses. And let's see how the table schema has changed: $ DESC country; As you can see, the value PRI 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 run this example in our cities database :

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

  $USE cities;

• Let’s check that we are definitely in our database (there should be another field there - population). To do this we write:

  $ DESC population;

everything is correct, the table is ours. Let's write the following:

$ ALTER TABLE country ADD PRIMARY KEY (id);

• and check it immediately with the command:

  $DESC country;

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

Adding Moldova

First we need to decide what we will record. Our 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 data request, I immediately determined which field would be searched, so we only got one record, which is what we needed.

Create the cities table

Using the diagram from the first article about the database, we get the necessary information about the table. It will contain the following fields:

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

It’s a bit stressful to write a unique ID every time, don’t you think? I would like to leave this to the MySQL authorities . And there is such a way - AUTO INCREMENT . We need to add this to the digital field, and if we do not explicitly pass the values, MySQL itself will increase the ID by one compared to the previous one. Therefore, creating a table will 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 diagram to see if everything was done correctly: $ DESC city; As you can see from the table diagram, we have a new description for the id field - auto_increment. So we did everything right. Let's check the data on a fully configured table. To do this, we will do the last part of the task - the foreign key.

Add a foreign key to cities

For a foreign key there will be this command: $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); And let’s immediately check what’s wrong with the table schema: has it changed over the course of an hour? $DESC city;

Bonus part. Testing

I forgot to add it to the task - fill in the data that was in the screenshot 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’ll check ;) There were Kharkov, Kyiv, Minsk, Odessa, Voronezh, and we’ll also add Chisinau. But this time we will not transmit IDs, we will 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, you can make several entries simultaneously using one INSERT INTO command. A handy thing, remember) And immediately let’s see what’s in the table: $ SELECT * FROM city; AUTO_INCREMENT - worked exactly as we wanted. The ID files are all filled out, even though we didn’t submit them. A foreign key is a dependent thing. To check whether it works correctly, you can try to write a foreign key that does not exist 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, add the city - Astana: $ INSERT INTO city (name, country_id, population) VALUES ('Astana', 5, 1136156); And we naturally get the 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. This part of the homework can be considered completed - forward to the new one :)

SELECT statement

Well, everything doesn’t seem so scary anymore, right? I would like to note once again that for Java developers, knowledge of the database is a must have. Without a database you can't go anywhere. Yes, I already want to start writing an application, I agree. But it's necessary. So we will continue this way. Using the SELECT statement, we retrieve data from the database. That is, this is a typical DML operation (have you already forgotten what it is?...))) Re-read the articles BEFORE). What are the benefits of relational databases? They have great functionality for aggregating and retrieving data. This is what the SELECT statement is used for. It would seem that there could be nothing complicated about it, right? But it turns out that there’s still a lot to understand) It’s important for us to understand the basics from which we can 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 exactly what order the operators should go in a SELECT query, so I’ll brazenly copy it here:

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 needs to be remembered so that later there will be no resentment for mistakes that are not clear where they come from. $SELECT * FROM city; But scraping out all the data is clearly not fun for us. This is exactly the same if we wanted to hammer nails with a microscope [1] , [2] . Since the database performs filtering, sorting and aggregation operations much faster than Java code, it is better to leave this matter to the database. Therefore, by complicating the tasks we will open new functionality.

WHERE parameter

To filter a selection, the WHERE word is used . This should be interpreted as follows: SELECT * FROM tablename (select all fields from the table tablename) WHERE talbe_row = 1 (where in the records the table_row field is equal to 1). It is important to note that the order of the keywords in the query is important. You cannot write WHERE a =1 FROM table_name SELECT *. For the Russian language this is ok, and to some it may not seem so bad, 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? What if we want not only Ukrainian, but also Belarusian? For this purpose, we can list the collection of values ​​that the field can take: $SELECT * FROM city WHERE country_id IN(1, 3); And we already have cities from two countries in response. What if there are multiple conditions to filter? Let's say we want cities 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 won’t describe regular expressions here: here’s a person who did this “briefly” in 4 parts )? For example, we remember how to spell a city, but not completely... To do this, you can add the LIKE keyword to the filtering 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 our search turned out to be very good. But I would like to sort not by ID, but by population, but how? Yes very simple...

ORDER BY parameter

Using ORDER BY, we can sort the records we received by a specific field. It sorts both numbers and strings. Let's expand the previous query, sort by population, 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 occurred in natural order, that is, in ascending order. What if we want the opposite? To do this, you need to 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 the sorting is based on population reduction. And the database does this very quickly: no Collections.sort there can be compared. Now let's 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. This is usually needed to use aggregate functions... What are aggregate functions?)) It makes sense to group by some fields if they are the same for different records. Let's look at what this means using our example. Let's say that 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 aggregation functions it looks a bit lackluster, you must admit. Therefore, let's look at a few of the most common functions:

• COUNT - number of records, can be used 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 sum 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 city population: $ SELECT COUNT(*) FROM city; What they asked for was what they got. Just the number of records. Sometimes this is useful. For example, if we need to find out the number of articles of a certain author. There is no need to rake them out of the database and count them. You can simply use COUNT(). $ SELECT AVG(population) FROM city; $ SELECT MIN(population) FROM city; And this is where grouping comes into force. For example, the task is to get the smallest city in the country. Already know how to do this? Try it yourself, then watch: $ 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 raking out all fields through * when using grouping and aggregation will not work! Think about it ;)

Homework

Based on the results of the previous articles, it is clear that the homework is being done, so let’s continue)) Yes, everyone who does the homework will continue to put “+” in the comments. It is important for me that the topic of homework is interesting to you, so that I continue to do it in the future. Yes, I read your comments regularly. Of course, I answer less often. I saw that they asked to give more difficult SQL problems. Until we learn 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 more clear, add them. If anyone wants to, write your example solution in the comments: this way 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 already had enough practice with working from the console. Connect to the database. If you use something else to work with the database, feel free to skip this task. Here and further I will use only MySQL Workbench.
2. Write requests to receive using our data:
   1. the smallest/most populous country;
   2. average number of inhabitants in the country;
   3. 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 discussed in detail the homework from the last lesson. Moreover, I consider this important both for those who did not do it and for those who did. For the former, this is an opportunity to find out the answer, and for the latter, to compare it with your result. Subscribe to my GitHub account to stay updated on changes to the project. I will maintain the entire code base there. Everything will take place in this organization . Next, we discussed the SELECT statement. He is the most important for us. It is through it that all requests for data will go through, 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 didn’t tell everything that was possible, but I didn’t set such a goal for myself. Yes, I know that you are already eager to write an application. Be patient, this is all you need. Both for the project and for your professional growth. While you wait, make sure that 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 will talk about database connections and joins. That's where the cool tasks will be))

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