Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to manage MySQL database from CLI", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "how to manage MySQL database from CLI" this article.

precondition

Before you begin, make sure that you have the following:

Valid Aliyun account.

An ECS instance running an operating system (such as Ubuntu or Centos) and a MySQL database server or ApsaraDB for RDS instance. You can choose either of these two configurations.

If you are using ApsaraDB, use the MySQL primary account username and password

If the MySQL database has been deployed on the ECS instance, non-root users with sudo privileges.

Step 1: connect to your MySQL instance

First, if you are connecting to the ApsaraDB for RDS, make sure that you have created a whitelist group for the computer's IP address.

If you run MySQL from the Alibaba ECS instance, you need to SSH the server using its public IP address.

Regardless of your connection method and product type (ApsaraDB or ECS instance with MySQL server), the basic syntax for connecting to MySQL is as follows:

MySQL connection syntax when connecting to an ECS instance:

$mysql-uroot-p

MySQL connection syntax when connecting to ApsaraDB for RDS:

$mysql-uroot-h-p

Example

$mysql-uroot-hserver-name. Mysql.rds.aliyuncs.com-p

Enter the password when prompted, and then press Enter to continue:

Step 2: manage the MySQL database on the command line

Once connected to the MySQL database, you can execute SQL (structured query language) commands to manage MySQL instances, including creation; users, databases, tables, columns / fields. You can also perform CRUD (create read updates and deletes) operations on any database table.

MySQL display database command

You can run the following command to view the available databases on Alibaba's MySQL server

Mysql > show databases; +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | +-+ 4-line collections (0.01s) MySQL create database command

To create a database on your Alibaba MySQL server, use the following command:

Mysql > create database test1

Remember to replace "sample2" with the preferred name of the database.

Switch databases in MySQL

You can switch from one database to another in MySQL with the 'use' command. For example, to switch to the database created above, use the following command:

Mysql > create tables using test1MySQL command

After switching to the database, you can create a table using the following syntax

Mysql > create table (field1 data type, field2 data type)

MySQL supports multiple data types. You can refer to the complete list here.

You should use the correct data type according to the needs of your application.

For example, to create a table named students that contains two fields (student_id and student_name), we can use the following syntax:

Mysql > CREATE TABLE student (id INT NOT NULL,student_name VARCHAR (30) NOT NULL); MySQL Show Tables command

To view all tables in the selected database, use the following syntax:

Mysql > show tables; +-+ | Tables_in_test1 | +-+ | Student | +-+ 1 collection in line (0.01s) MySQL description table command

To get the structure of the table created above, you can use the following SQL statement

Mysql > describe the student +-- + | Domain | input | empty | key | default | extra | +-+ -+ | id | int (11) | No | | NULL | | student_name | varchar (30) | No | | NULL | | +-+ -+ 2 in group (0.02s) step 3: MySQL CRUD operation

You can manage records on MySQL database tables from the command line. CRUD is the initials of Create,Read,Update and Delete.

Create a record in the MySQL table

To insert a record in a database table, use the following syntax:

Insert into

(column 1, 2, 3), value (column1value,column2value,column3value)

Note that the number of columns and values should match.

For example, to insert three students into the student table, we can use the following SQL command:

MySQL > insert into student (ID,student_name) value ('1', 'John Doe'); query OK,1 row affected (0.02s) MySQL > insert student (ID,student_name) value ('2', 'Richard Roy'); query OK,1 row affected (0.01s) MySQL > insert student (ID,student_name) value ('3', 'Jane Doe') Query OK,1 rows affected (0.01s) retrieve records from the MySQL table

Retrieving records is part of the MySQL CRUD operation. To read the record, use the select statement.

Mysql > Select column1,column2,.. from the table. Columnx

For example, you can retrieve all students and all columns from the student table using the following command:

Mysql > select * from students;+-+-+ | id | student_name | +-+-+ | 1 | John Doe | 2 | Richardson | | 3 | Jane Doe | +-+-+ 3 in group (0.02s)

Sometimes, you may only want to retrieve certain columns from the table. For example, to retrieve the ID of a student without a name, we can use the following syntax:

Mysql > Select id from students; +-+ | id | +-+ | 1 | | 2 | | 3 | +-+ 3 filter records in group (0.01s)

You can use the 'where' clause to filter records when retrieving them. For example, you can retrieve only students with an ID of "2" using the following syntax:

MySQL > Select * from among the students ID ='2'; +-+-+ | id | student_name | +-+-+ | 2 | Richard Rowe | +-+-+ 1 collections in lines (0.01s) sort MySQL records in ascending or descending order

When retrieving records from the Alibaba MySQL database, you may feel the need to sort them in ascending or descending order with the following syntax:

Mysql > SELECT expression FROM [WHERE condition] ORDER BY expression [ASC | DESC]

Using the student table above, we can arrange the students' names in alphabetical order.

Mysql > select * from students order by student_name asc; +-+-+ | id | student_name | +-+-+ | 3 | Jane Doe | | 1 | John Doe | 2 | Richard Luo | +-+-+ 3 Edit and update MySQL records in group (0.01s)

In a production environment, editing and updating MySQL records is inevitable. To do this, use the following command:

Mysql > Update table_name SET column1 = value1,column2 = value2. WHERE condition

For example, you can update the student ID "2" and change the name to James Smith using the following SQL command:

Mysql > Update student settings student_name = 'James Smith', where id =' 2'; query OK,1 rows affected (0.02s) matching number of rows: 1 changed: 1 warning: 0

If you run the select statement again, you will see that the student name has been updated successfully:

Mysql > select * from students;+-+-+ | id | student_name | +-+-+ | 1 | John Doe | 2 | James Smith | | 3 | Jane Doe | +-+-+ 3 delete table records from MySQL in group (0.02s)

If you want to delete the record completely from the table, use the following SQL syntax:

Mysql > remove WHERE column = value from table_name

For example, you can delete a student with an ID of 3 using the following command

Mysql > removed from students with id ='3'; query OK,1 row affected (0.02 seconds) delete all records from MySQL table

If you have a large number of records and want to erase the entire table in a single command, use the truncate statement:

Mysql > truncate [Table name]

To delete all records in the student table, type:

Mysql > truncate students; query is normal, 0 rows are affected (0.02 seconds)

If you try to retrieve the record again, MySQL will display an empty set:

Mysql > select * from students; empty set (0.02s) modify columns in the MySQL table

You can add columns to an existing table using the following syntax:

Mysql > Alter table_name add [column name] [data type]

For example, to add a column named "class" using Varchar as the data type and add a length value of 30 to the student's table, use the following command:

Mysql > Alter table student add class Varchar (30); query normal, 0 rows affected (0.02s) record: 0 repeat: 0 warning: 0 add primary key to MySQL table

To uniquely identify each record in the table, add a primary key (contention). You should add it to the most unique column and there can be no duplicates.

Mysql > Alter table add primary key (column_name)

For example, in our student table, the id field is unique because no two students can share the same identification number.

Mysql > Alter table students add primary key (id); query normal, 0 rows affected (0.02s) record: 0 repeat: 0 warning: 0MySQL auto increment field

You can instruct MySQL to use the Auto Increment feature to automatically assign serial numbers to columns. For example, students can get an ID value when they enroll. This will be done automatically after it is added to the database table.

MySQL automatic increment has the following syntax:

Mysql > Alter table [table name] modify column [column name] auto_increment

To assign it to the student table, type:

Mysql > Alter table student modify column id int auto_increment; query normal, 0 rows affected (0.02 seconds) record: 0 repeat: 0 warning: 0

You can use the description statement to check the new structure of the table to confirm the changes:

Mysql > describe the student +-- + | Domain | input | Null | key | default | extra | +-+- -+-- +-+ | id | int (11) | No | PRI | NULL | auto_increment | student_name | varchar (30) | No | NULL | | Class | varchar (30) | Yes | | NULL | | +-+-- -+ 3 in group (0.01 seconds)

Delete MySQL database table

If you no longer need tables in the database, use the following command

Mysql > Drop table table_name; delete database

To completely delete the database from the MySQL server, use the following command

Mysql > drop database [database_name]

Example:

Mysql > drop database test1

Please note that deleting a table or database is irreversible; you should first back up the database to avoid deleting the wrong properties, especially in a production environment

Step 4: manage MySQL database users

You can manage multiple users and assign them different permissions depending on the roles you want them to perform on the database.

List MySQL database users

You can view the available users in Alibaba's MySQL database by typing the following command

Mysql > Select user from mysql. User, host; +-+ | user | host | +-+-+ | Aurora |% | | aurora_proxy |% | | Root |% | _ rds_outer_user | 127.0. 0.1 | | aliyun_root | 127.0. | 0.1 | | mysql .session | localhost | +-+-+ 6-line collections (0.01s) create MySQL database users

To create a MySQL database user, use the following syntax:

Mysql > create user'[user name]''[hostname] 'identified by' PASSWORD'

Remember to replace "sample" with your user name and use a strong password value.

Mysql > the user who created the 'PASSWORD' identity' example_user' @ 'localhost'

If you want users to connect from any host, use'% 'instead of localhost. For example

Mysql > create the user 'example_user' @' for the 'PASSWORD' identity'; assign permissions to the MySQL user

Permissions are permissions that allow users to perform certain tasks in database tables. To assign permissions to a user, use the following syntax:

Mysql > GRANT

ON TO user_name @'; mysql > FLUSH PRIVILEGES

You can use commas to combine multiple permissions, such as

Mysql > GRANT Select, insert, delete ON TO user_name @'

To assign all permissions to a user, use the following syntax:

Mysql > GRANT ALL ON *. * TO'[username]'@'[hostname]'; mysql > FLUSH PRIVILEGES

To assign the permissions of the MySQL user to a specific database, use the following syntax:

Mysql > GRANT ALL ON. * TO'[username]'@'[hostname]'; mysql > FLUSH PRIVILEGES; check the permissions granted by the user on MySQL

To view the granted permissions assigned to a user, use the following syntax:

The MySQL > shows that the subsidy is [user name]

Example:

Mysql > show grants for root

| | sponsored root @% | GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,PROCESS,REFERENCES,INDEX,ALTER,CREATE TEMATEARY TABLES,LOCK TABLES,EXECUTE,REPLICATION SLAVE,REPLICATION CLIENT,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE, create user, event, TRIGGER ON *. | * TO 'root' @'% 'WITH GRANT OPTION | set in +-1 line (0.01s) revoke the permissions of the MySQL user

If you assign permissions incorrectly, or if you no longer want users to perform certain tasks on the MySQL server, you can revoke the permissions of the MySQL user at any time. To do this, use the following command:

Mysql > undo * from'[username]''[hostname]'. [permissions] on *

You can revoke multiple permissions by separating them with commas:

MySQL > unselect, insert, delete from'[user name]'@'[host name]'

To revoke all permissions, use the following syntax:

MySQL > revoke all permissions on *. * from'[user name]'@'[hostname]'

Example

Mysql > undo the sample2 from 'example_user' @'. All permissions of *. Delete MySQL user mysql > drop user'[username]'@'[hostname]'

Example:

Mysql > Delete the user 'example_user' @'; the above is all the content of the article "how to manage MySQL Database from CLI". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.