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 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
- Checking homework
- Installing MySQL on MacOS
- Entering the MySQL command line
- Writing our first lines in SQL
- Homework
- Results
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:- 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.
- 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.
- MySQL is not installed, do not 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 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?- Add a new field to the table schemas - 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 schema of the country table.
- 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 the following: id, name, country_id, population.
- Add a primary key to the city table.
- Add a foreign key to the city table.
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.
GO TO FULL VERSION