Understanding databases and SQL. (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
• Entering the MySQL command line
• Writing our first lines in SQL
  • Create your database and table
  • Working with a table
• Homework
• Results

Good health, dear readers. Today we will talk little and write more, because hands remember much better than eyes. You might have a question: why do we use MySQL? There are, for example, the same PostgreSQL and other solutions. Everything is simple here. First, 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 the task - to install MySQL on my machine and go into it. Through the console, or in some other way. All done? Who did - well done! It doesn't matter what level you are in CodeGym - it is 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. Who tried, but did not succeed - is also not scary. It doesn’t always happen the first time: sometimes you need to take a break and try again with fresh energy. Who installed MySQL himself - write "+" in the comments so that I know that someone is doing it. I will do it myself too. Since I no longer have a laptop on Windows, I will show on a macbook. I do not think that the installation process will be very 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 run. However, things didn't go right for me right away. It turned out that Apple does not skip the installation of the latest version: You can, of course, disable this business, but the MacBook is working, and I will not go into its settings. Therefore, by trial and error, I found a version that can be installed - this is 5.7.21 . To find other versions, I went to the Archive tab and selected the required version: Next, run the downloaded file. Click next, next, next, but at the end, be careful! There will be a username and password to access the database. It looked like this for me: It says that there will be a user namedroot and password Vac/zto=.24q . This completes the installation.

Entering the MySQL command line

To log in, you need to make sure that the MySQL server is enabled. To do this, go to System Preferences , find the MySQL icon there: Go to it and see that the server must first be enabled. To do this, simply click Start MySQL Server and that's it. Yes, you can still check the box so that the server rises in the background every time the machine starts. It is important to note that I will begin all requests in the terminal with the $ symbol to make it clear. Next, you need to directly connect to the MySQL server through 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 the server is installed and retracted. You just need to pass the correct command to it.
2. If the answer is command not found: mysql , it's okay - there is simply 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 more - we definitely won’t work through the terminal all the time.

3. MySQL is not installed, do not fool your computer.

Next, use the username and password provided during installation, and write: $ mysql --user=root -p There we write the password, and we will be happy like this: Just the last line says that we entered the MySQL terminal server. But this password for root turned out to be one-time, and it needs to be changed to another one. For security reasons, of course. If you do not change and try to create a new database in the server, we will get the following response: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement . Forced, so to speak. Well, okay, what is it for us, is it difficult or what? Let's make the password simpler (this is only for local work, for real work the password should be complex, of course). Let's say root∫, like the username. To change the password, enter the line: ALTER USER 'root'@'localhost' IDENTIFIED BY 'root'; And the answer will be: To make sure 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 is created on the server, let's 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 is a separate respect. Who did not - do not worry, the manuals are for all types of installation:

• for Windows _
• for macos ,
• for linux .

There everything is described quite sane. 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, writing commands in capital letters is considered good manners. Next, to drop the 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 have already created a test database called test_db , so let's drop it. To do this, write: $ DROP DATABASE test; It's worth noting that the Query OK, 0 rows affected (0.02 sec) response is very important because it confirms that the query was successful. In addition, it says how many rows in the tables this query has changed and for how long. In general, the word DROPis responsible for deleting something. In this case, we have deleted the database. But it can also be used for other purposes (like dropping a table). And now, to trust me to 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 database and table

Create the cities database: $ CREATE DATABASE cities; To work in a specific database, you need to log into it. To do this, use the USE database_name command , after which all commands for working with tables will be carried out in this particular database. $ USE cities; As you can see from the response - Database changed , the transition to this database was successful. The next step is to create a table. Which? Well, for starters, let's create a table with countries. Why is that? Because cities need a foreign key of 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 see the list of tables: $ SHOW TABLES; Here it is, our table. Now let's learn how to delete tables. To do this, write: $ DROP TABLE country; It is important to note that once a table or database is deleted, it cannot be restored!

Working with a 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 information about the table is written here. To add something to a table, you need to use the INSERT INTO table_name VALUES() command . Let's add three entries 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 the simplest query to get 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 would be: $ SELECT * FROM country; Where * means to take all fields. Here is the beauty we have. If we want to get only a specific field, it’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 have little information on it. The population would obviously not hurt, right? And how to do it?

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

For the first one, 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 it 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; $ DESCcountry;As you can see from the figure, we have successfully added a new field for the table and with the second command we checked that the field is in the schema. The second step is to update the records in the table and assign values. We 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 talk about 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; This is where we used the WHERE keyword for the first time.. It is needed to add values ​​for filtering. For example, this time we said that we only need to update 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 did not specify filtering at all, then this value would be written to all table records. For Russia, Belarus and other countries, update yourself ;) And now let's try to get data from the table for the field name and population, provided that id is greater than 1. $ SELECT name, population FROM country WHERE id >1; Thus, we received only those fields that we ourselves wanted. Through INSERT INTO, you can specify which fields we want to add and which ones to leave empty. To do this, 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 record is bad, and the population, of course, is taken out of your head, so you need to delete it. Use the command DELETE FROM table_name : $ DELETE FROM country WHERE name = 'Georgia'; Thus, we learned 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 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, which will contain all the described fields. 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.

The task is interesting, for those who are in the subject, it will be quick and uncomplicated. For those who are not, it will serve as an excellent consolidation of the material from this article.

Summing up

In this article, we have done a lot of things. First, we installed and started the MySQL database server. Secondly, we learned a whole list of keywords and queries with 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 database
• CREATE TABLE table_name (id INT, ....); - create a table
• SHOW TABLES; - view list of tables
• DESC TABLE table_name; - view table schema
• 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 the 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 under the condition for another field
• DELETE FROM table_name WHERE table_row1 = value1; - remove a field (or fields) from the table under a certain condition.

I think we did a great job with you. To everyone who likes my work, I suggest subscribing to my GitHub account , where I post interesting projects and developments that happen in my career. To everyone who is interested in working in open source, I suggest participating in my projects (repositories). Thank you all for your attention. Stay tuned for the next article on databases coming soon.

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