MySQL how to work with the database

This article is rather a small note on working with a database, which contains basic commands with examples and answers some of the questions on MySQL how to do this or that action and is aimed at those who are just starting to work with MySQL. You can look at the full documentation on working with the database on the official website.

How to connect to the database

To connect to mysql the following command is used:

$ mysql -u root -p

where

-u root – connect as root user
-p – use password when connecting

After that, you will be prompted to enter a password for the root user:

$ mysql -u root -p
Enter password: 

If mysql server is just installed, then by default the password is an empty field. If you have already changed it, then you must enter the password you specified. If you do not remember the password, you can connect as root by logging in under it in linux (su command), on which the mysql server is installed:

# mysql

You will see a welcome screen and wait for the commands to be entered:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5123
Server version: 5.7.33-36 Percona Server (GPL), Release 36, Revision 7e403c5

Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

If you need to connect to a remote server, then the -h argument is used with the ip or domain name of the server:

$ mysql -u root -p -h pocketadmin.tech

You can immediately specify the database to which you want to connect, for example mysql:

$ mysql -u root -p -h pocketadmin.tech mysql

How to view databases

After connecting to the server, you can see the existing databases:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

How to choose a database

mysql> use mysql;
Database changed

How to view tables in a database

Let’s see what tables are present in the selected database:

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

How to view the names of columns in a table

In order to find out the name of the columns in a specific table, for example the servers table:

mysql> show columns from servers;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| Server_name | char(64) | NO   | PRI |         |       |
| Host        | char(64) | NO   |     |         |       |
| Db          | char(64) | NO   |     |         |       |
| Username    | char(64) | NO   |     |         |       |
| Password    | char(64) | NO   |     |         |       |
| Port        | int(4)   | NO   |     | 0       |       |
| Socket      | char(64) | NO   |     |         |       |
| Wrapper     | char(64) | NO   |     |         |       |
| Owner       | char(64) | NO   |     |         |       |
+-------------+----------+------+-----+---------+-------+
9 rows in set (0.00 sec)

How to create a user

To create a user user and allow him to connect only from the host on which the mysql server is installed (localhost) using the password mypassword, use the following command:

mysql> CREATE USER 'user@'localhost' IDENTIFIED BY 'mypassword';

If the user will connect remotely, then it is necessary to specify the ip-address from which he will do it:

mysql> CREATE USER 'user@'192.168.88.100' IDENTIFIED BY 'mypassword';

If there is a need to allow the user to connect from any ip-address, then the % sign is used:

mysql> CREATE USER 'user@'%' IDENTIFIED BY 'mypassword';

How to grant rights to a user

After creating a user, he needs to grant the rights to perform certain actions. The command syntax is as follows:

mysql> GRANT ALL PRIVILEGES ON mydb.mytb TO 'user'@'localhost';

After its execution, we granted the user user all privileges on the mytb table, which is located in the mydb database. In this case, the user will be able to work with the table only from localhost.

If you need to grant rights to the entire database, instead of specifying all tables, you can use the * symbol:

mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost';

In the previous example, we gave the user all rights (ALL PRIVILEGES), but usually this is not required. The user can be given the following rights:

CREATE – create table
SELECT – select date from table
INSERT – adding date to table
UPDATE – update date in table
DELETE – delete date from table
DROP – drop table

These rights can be combined, for example, to enable the user to select and update records in the database:

mysql> GRANT SELECT, UPDATE ON mydb.* TO 'user'@'localhost';

How to make a select

To get all the records that are stored in the mytb table, you need to run the query:

mysql> SELECT * FROM mytb;

You can select only certain columns with data, for example:

mysql> SELECT column1, column2 FROM mytb;

You can make a selection according to certain conditions:

mysql> SELECT column1, column2 FROM mytb WHERE column1 > 10;

The console also has the ability to use multi-line input, ending it with a semicolon:

mysql> SELECT column1, column2
    -> FROM mytb
    -> WHERE column1 > 10;

Of course, sampling from a database is a very broad topic that cannot be covered in a separate article. Therefore, we will not dwell on this, we considered the topic of sampling in MySQL how to a basis, the first steps.

MySQL how to create a database

To create the mydb database in mysql, just run the command:

mysql> CREATE DATABASE mydb;

As a rule, you need to immediately set the encoding (CHARACTER SET) of the database being created, for example utf8, as well as the method of comparing and ordering (COLLATE) data in the database:

mysql> CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_general_ci;

How to create a table

When creating the mytb table, it is necessary to describe all the columns that will be present in it. For example, let our table store the name (name is the name of the column, VARCHAR(20) is the data type, a variable-length string with a maximum size of 20 bytes) and the date of birth (birth is the name of the column, DATE is the data type, date):

mysql> CREATE TABLE mytb (name VARCHAR(20), birth DATE);

How to add a record to a table

To add data to the mydb table, you must list the fields to be filled and specify values ​​for them:

mysql> INSERT mytb (name, birth)
    -> VALUES ('Jon', '1990-02-13');

You can add multiple entries at once:

mysql> INSERT mytb (name, birth)
    -> VALUES
    -> ('Jon', '1990-02-13'),
    -> ('Ivan', '1991-12-03');

How to update data in a table

To update data in the mydb table, for example date, you need to specify a new value and a condition by which we find and update the corresponding record:

mysql> UPDATE mytb
    -> SET date = '1990-12-03'
    -> WERE name = 'Ivan';

How to delete data in a table

The following syntax is used to conditionally delete specific data:

mysql> DELETE FROM mytb
    -> WHERE name = 'Jon';

You can delete all records if you do not specify the deletion condition:

mysql> DELETE FROM mytb;

How to clear a table

To delete all data or clear a table, you can use the following command:

mysql> TRUNCATE mytb;

MySQL how to drop a table

To drop an entire table, use the DROP command:

mysql> DROP mytb;

How to drop a database

The syntax is the same as for dropping a table, but specifying the database:

mysql> DROP mydb;

It should be borne in mind that the user must have the appropriate rights to query, insert, update, delete, and other operations.