We analyze databases and the SQL language. (Part 2) - "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
• Installing MySQL on MacOS
• Login to the MySQL command line
• Writing our first lines in SQL
  • Create your own database and table
  • Working with the table
• Homework
• Results

Good health, dear readers. Today we will talk a little and write more, because our hands remember much better than our eyes. You might be wondering: why do we use MySQL? There are, for example, the same PostgreSQL and other solutions. Everything is simple here. First of all, it's free. Secondly, it has a convenient UI interface (we will talk about it in a separate article). Thirdly, she is familiar and pleasant to me. In addition to all of the above, MySQL is also very common.

Checking homework

Last time I gave a task - install MySQL on my machine and log into it. Via the console, or in some other way. Is everything done? Well done to those who did it! It doesn’t matter what level you are in JavaRush, it’s important to be able to install and configure the necessary tools for work according to the manuals. And I assure you, there will be many of them. Those who tried, but did not succeed, are also not afraid. This doesn’t always happen the first time: sometimes you need to take a break and try again with fresh strength. Who installed MySQL yourself - write “+” in the comments so that I know that someone is doing it. I will do this myself too. Since I no longer have a Windows laptop, I will show it on a MacBook. I don't think the installation process will be much different. For those who want to install it themselves using the English manual, please click here .

Installing MySQL on MacOS

First of all, go to the following link , select the operating system, be it Windows, Ubuntu or MacOS, as shown below: Download and launch. However, everything didn’t go right for me right away. It turned out that Apple does not allow installation of the latest version: You can, of course, disable this, but the MacBook is working, and I won’t mess with its settings. Therefore, through trial and error, I found a version that can be installed - this is 5.7.21 . To find other versions, go to the Archive tab and select the desired version: Next, run the downloaded file. Click next, next, next, but at the end, be careful! There will be a username and password to log into the database. For me it looked like this: It says here that there will be a user with the name root and the password Vac/zto=.24q . This completes the installation.

Login to the MySQL command line

To log in, you need to make sure that the MySQL server is turned on. To do this, go to System Preferences , find the MySQL icon there: Go to it and see that the server must first be turned on. To do this, just click Start MySQL Server and that’s it. Yes, you can also check the box so that the server starts up in the background every time the machine starts. It is important to note that I will start all requests in the terminal with the $ symbol to make it clear. Next, you need to directly connect to the MySQL server via the command line. To do this, open the terminal and write: $ mysql And there are two options:

1. If the response is “ERROR 1045 (28000): Access denied for user 'roman_beskrovnyi'@'localhost' (using password: NO" , then this means that the server is installed and is being recalled. You just need to pass the correct command to it.
2. If the answer is command not found: mysql , it’s okay - there’s just no link to the server. To do this, you will need to make an alias with the mysql variable, so we write the following line:

   $ alias mysql=/usr/local/mysql/bin/mysql

   This will only help for this terminal session. But we don’t need any more - we definitely won’t work through the terminal all the time.

3. MySQL is not installed, don't fool your computer.

Next, we use the name and password provided during installation and write: $ mysql --user=root -p There we write the password, and we will be happy in this form: Just the last line says that we are logged into the MySQL terminal server. But this password for root turned out to be a one-time password, and it needs to be changed to another one. For security reasons, of course. If we do not change this and try to create a new database on the server, we will receive the following response: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement . They are forced, so to speak. Well, okay, is it difficult for us? Let's make the password simpler (this is only for local work; for real work, the password must be complex, of course). Let's say root∫ , like the username. To change the password, you need to enter the line: ALTER USER 'root'@'localhost' IDENTIFIED BY 'root'; And the answer will be like this: To make sure that everything works correctly, let's try to create a new database using the following syntax : $ CREATE DATABASE test_db; To make sure that such a database has been created on the server, write the command: $ SHOW DATABASES; And as a result we get the following: That’s all :) It’s impossible to say that it was simple and fast. Therefore, whoever did it himself - special respect. For those who haven’t done it, don’t worry, there are manuals for all types of installation:

• for Windows ,
• for MacOs ,
• for Linux .

Everything is described there quite sanely. The only thing is that everything is in English. Now we write our first lines in SQL.

Writing our first lines in SQL

As you may have noticed above, we know two commands:

1. CREATE DATABASE - creates a database on the server;
2. SHOW DATABASES - Shows all created and available databases on the server.

I note that SQL does not care how we write their commands - either all in capital letters or not. However, it is considered good manners to write commands in capital letters. Next, to delete a database, you need to write the command DROP DATABASE database_name ; where database_name is the name of the database we want to delete. We've already created a test database called test_db , so let's delete it. To do this we write: $ DROP DATABASE test; It is worth noting that the Query OK, 0 rows affected (0.02 sec) response is very important, as it confirms that the request was successful. In addition, it tells you how many rows in the tables this query changed and over what time. In general, the word DROP is responsible for removing something. In this case, we deleted the database. But this can also be used for other purposes (for example, dropping a table). And now, to trust me , check the result, let's see how many databases are left: $ SHOW DATABASES; As we can see, there is no database named test_db. CHTD :D

Create your own database and table

Create a cities database: $ CREATE DATABASE cities; To work in a specific database, you need to log in to it. To do this, use the USE database_name command , after which all commands for working with tables will be carried out in this database. $ USE cities; As can be seen from the response - Database changed , the transition to this database was successful. The next step is creating the table. Which? Well, first, let's create a table with countries. Why is that? Because cities need a foreign key to the country in which it is located. And it can be obtained after these countries generally begin to be in the database (hereinafter - abbreviated from the database). As we remember from the previous article , in the table where countries are described, we have two fields - ID and name, where ID is a unique identifier, and name is the name of the country. Therefore, creating such a database is as easy as shelling pears. We write: $ CREATE TABLE country (id INT, name VARCHAR(30)); As you can see, everything went well. By analogy with SHOW DATABASES, we can look at the list of tables: $ SHOW TABLES; Here it is, our table. Now let's learn how to delete tables. To do this we write: $ DROP TABLE country; It is important to note that once a table or database is deleted, it cannot be restored!

Working with the table

Let's restore the table we created. To do this, you just need to repeat what you have already done. To look at the table structure, you need to use the following command - DESC table_name : $ DESC country; As you can see, all the information about the table is recorded here. To add something to a table, you need to use the INSERT INTO table_name VALUES() command . Let's add three records for three countries: $ INSERT INTO country VALUES (1, 'Ukraine'); $ INSERT INTO country VALUES (2, 'Russia'); $ INSERT INTO country VALUES(3, 'Belorus'); To look at what is stored in the database, we will use a simple request to obtain data. To do this, we want to get the data of all fields from the country table. Remember the previous sentence carefully because the sql query will be like this: $ SELECT * FROM country; Where * means you need to take all fields. This is how beautiful we got. If we want to get only a specific field, that’s not a problem: instead of a star, we write the names of the fields that should be separated by commas. Try it yourself ;) We have already created a table, but somehow we don’t have enough information on it. The population size certainly wouldn't hurt, right? And how to do it?

1. Add a new field to the table schema - population.
2. Update existing entries.

For the first, we will use the ALTER TABLE table_name ADD COLUMN command . In general, ALTER TABLE is a set of commands for DDL operations (remember what this is? It was in the previous article). By the way, SELECT refers to a DML operation. Let's write the following: $ ALTER TABLE country ADD COLUMN population INT; $ DESC country; As you can see from the figure, we have successfully added a new field to the table and checked with the second command that the field is in the schema. The second step is to update the entries in the table and assign values. Let's take the number from Wikipedia. Not that I trust them, but nevertheless, this is the fastest and easiest way. To do this, use another DML command. But first, let’s say what we want to do: we want to update the country table, make the population field equal to 41806221 in the record where id = 1. And here’s what this construction looks like: UPDATE table_name SET table_row1 = value WHERE table_row2 = value2; Based on it, we write: $ UPDATE country SET population = 41806221 WHERE id = 1; Here we used the WHERE keyword for the first time . It is needed to add values ​​for filtering. For example, this time we said that it is necessary to update only for the field (fields) whose id is equal to one (until we told sql that this is the primary key - there may be several of them). We could also specify a condition like WHERE id > 2. If we had not specified filtering at all, this value would have been written to all table records. For Russia, Belarus and other countries, update yourself ;) Now let's try to get data from the table for the name and population fields, provided that the id is greater than 1. $ SELECT name, population FROM country WHERE id >1; Thus, we received only those fields that we ourselves wanted. Using INSERT INTO we can specify which fields we want to add and which ones we want to leave empty. To do this, we write: $ INSERT INTO country (name, population) VALUES('Georgia', 1234566); To check what happened, let's look at the list of all fields: $ SELECT * FROM country; Without an id, the entry is bad, and the population, of course, was taken from the head, so you need to delete it. Use the DELETE FROM table_name command : $ DELETE FROM country WHERE name = 'Georgia'; This way we learned how to delete a field from the table.

Homework

Homework will be as follows:

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 described fields. 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.

The task is interesting, for those who are in the know, it will be quick and uncomplicated. For those who haven’t, it will serve as an excellent reinforcement of the material from this article.

Let's sum it up

We did a lot of things in this article. First, we installed and started the MySQL database server. Secondly, we learned a whole list of keywords and queries, with the help of which we can now create/delete databases and tables. We learned how to add data to tables, update and delete them. Here are all the commands we used, along with their descriptions:

• CREATE DATABASE database_name; - create a new database
• SHOW DATABASES; — view the list of databases
• DROP DATABASE database_name; - delete database
• USE database_name; - work in the database
• CREATE TABLE table_name (id INT, ....); - create a table
• SHOW TABLES; — view the list of tables
• DESC TABLE table_name; — see the table diagram
• DROP TABLE table_name; - delete table
• INSERT INTO table_name VALUES (); - add a field to the table
• SELECT * FROM table_name WHERE table_row = value; — get fields in the table under the condition after WHERE
• SELECT * FROM table_name; - get all fields in the table
• ALTER TABLE table_name ADD COLUMN column_name COLUMN_TYPE; — add a new field field to the table schema
• UPDATE talbe_name SET talbe_row1 = value1 WHERE table_row2 = value2; — update records in the table: set the values ​​of one field subject to the condition for another field
• DELETE FROM table_name WHERE table_row1 = value1; — remove a field (or fields) from a table under a certain condition.

I think we did an excellent job with you. I invite everyone who likes my work to subscribe to my GitHub account , where I post interesting projects and developments that are happening in my career. I invite everyone who is interested in working in open source to participate in my projects (repositories). Thank you all for your attention. Stay tuned for the next article on databases coming soon.

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