Commands often used in MySQL operations

The following commands for working with MySQL are summarized.

・ Login to MySQL ・ Display database list ・ Display table list ・ Display column list

Log in to MySQL

mysql -u username-p

After entering, enter the password. Be careful not to make a mistake because the password is not displayed on the CLI.

Although deprecated, you can log in with a single shot by passing the password in the -p argument.

View database list

SHOW DATABASES;

The list is displayed like this.

mysql> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myapp_development  |
| myapp_test         |
| mysql              |
| performance_schema |
| root               |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

Display table list

First, select the database you want to use.

use database name;
ex)use users;
SHOW TABLES;

You can get a list of tables in the database.

mysql> SHOW TABLES;
+----------------------+
| Tables_in_myapp_test |
+----------------------+
| posts                |
| schema_migrations    |
| users                |
+----------------------+
3 rows in set (0.01 sec)
show tables from database name;

You can get it at.

Get the list of columns in the table.

Select the database as before. Execute the following command.

DESCRIBE table name;
show columns from table name;

ex)show columns from users;

You can get the column list in the table like this.

mysql> show columns from users;
+-----------------------------+--------------+------+-----+---------+----------------+
| Field                       | Type         | Null | Key | Default | Extra          |
+-----------------------------+--------------+------+-----+---------+----------------+
| id                          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| email                       | varchar(255) | NO   | UNI | NULL    |                |
| created_at                  | datetime     | NO   |     | NULL    |                |
| updated_at                  | datetime     | NO   |     | NULL    |                |
| name                        | varchar(255) | NO   |     | NULL    |                |
| image                       | varchar(255) | YES  |     | NULL    |                |
+-----------------------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

Connect with MySQL in Docker environment

-Launch the container with docker-compose up. -Docker-compose exec db bash -Connect to the DB container with mysql -u username -p

Connect to RDS from ECS / EC2 container

・ Launch a task ・ Connect to EC2 with ssh -Docker exec -it container ID sh · Mysql -h endpoint -u username -p to connect to RDS

Recommended Posts

Commands often used in MySQL operations
Gem often used in Rails
Matcher often used in RSpec
Syntax examples often used in Java
About methods often used in devise
Test API often used in AssertJ
Ruby methods often used in Rails
Edit Mysql with commands in Docker environment
Stumble in MySQL 5.5-> 5.7
[Gradle] Build operations often performed in Java projects
Summary of frequently used commands in Rails and Docker
Docker Frequently used commands
Ping commands in Java
Personal summary of the guys often used in JUnit 4
rails console Frequently used operations
Specify mysql socket in Hanami
Mechanism and characteristics of Collection implementation class often used in Java
[Rails] DB settings and operations (mysql), Rubocop in VSCode, useful extensions