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.
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
- Checking homework
- Installing MySQL on MacOS
- Login to the MySQL command line
- Writing our first lines in SQL
- Homework
- Results
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:- 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.
- 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.
- MySQL is not installed, don't fool your computer.
Writing our first lines in SQL
As you may have noticed above, we know two commands:- CREATE DATABASE - creates a database on the server;
- SHOW DATABASES - Shows all created and available databases on the server.
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?- Add a new field to the table schema - population.
- Update existing entries.
Homework
Homework will be as follows:- You need to add a primary key (PRIMARY KEY) from the ID field to the country table schema.
- Add another country to the country table - Moldova.
- 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.
- Add a primary key to the city table.
- Add a foreign key to the city table.
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.
GO TO FULL VERSION