Skip to content
On this page

Managing MySQL

To manage MySQL, you can use the visual interface MySQL Workbench.

MySQL Workbench allows you to query, create, and modify database tables visually; however, ultimately, it is a graphical client that operates by sending and executing SQL statements. Thus, both MySQL Workbench and the MySQL Client command line are clients, and the sole interface for interacting with MySQL is SQL.

MySQL provides a wealth of SQL statements for management. While MySQL Workbench can directly manage MySQL, often, when connecting via SSH, you can only use SQL commands. Therefore, understanding and mastering common SQL management operations is essential.

Database

On a server running MySQL, you can create multiple databases. To list all databases, use the command:

sql
mysql> SHOW DATABASES;

+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | shici | | sys | | test | | school | +--------------------+

Among these, information_schema, mysql, performance_schema, and sys are system databases; do not modify them. The others are user-created databases.

Note: After entering SQL in the MySQL command line client, remember to add a ; to signify the end of the SQL statement, then press Enter to execute it. Although some SQL commands can execute without ;, commands like SELECT will cause the MySQL client to wait for more input without it. In graphical interfaces or application development, ; is not needed.

To create a new database, use the command:

sql
mysql> CREATE DATABASE test;

Query OK, 1 row affected (0.01 sec)

To delete a database, use the command:

sql
mysql> DROP DATABASE test;

Query OK, 0 rows affected (0.01 sec)

Note: Deleting a database will result in the removal of all tables within that database.

To operate on a database, switch to it first:

sql
mysql> USE test;

Database changed

Table

To list all tables in the current database, use the command:

sql
mysql> SHOW TABLES;

+---------------------+ | Tables_in_test | +---------------------+ | classes | | statistics | | students | | students_of_class1 | +---------------------+

To view the structure of a table, use the command:

sql
mysql> DESC students;

+----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | class_id | bigint(20) | NO | | NULL | | | name | varchar(100) | NO | | NULL | | | gender | varchar(1) | NO | | NULL | | | score | int(11) | NO | | NULL | | +----------+--------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)

You can also use the following command to view the SQL statement that created the table:

sql
mysql> SHOW CREATE TABLE students;

+----------+-------------------------------------------------------+ | students | CREATE TABLE students ( | | | id bigint(20) NOT NULL AUTO_INCREMENT, | | | class_id bigint(20) NOT NULL, | | | name varchar(100) NOT NULL, | | | gender varchar(1) NOT NULL, | | | score int(11) NOT NULL, | | | PRIMARY KEY (id) | | | ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 | +----------+-------------------------------------------------------+ 1 row in set (0.00 sec)

Creating a table uses the CREATE TABLE statement, while deleting a table uses the DROP TABLE statement:

sql
mysql> DROP TABLE students;

Query OK, 0 rows affected (0.01 sec)

Modifying a table is more complex. To add a new column birth to the students table, use:

sql
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

To modify the birth column, for example, to rename it to birthday and change its type to VARCHAR(20):

sql
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

To delete a column, use:

sql
ALTER TABLE students DROP COLUMN birthday;

Exit MySQL

To exit MySQL, use the EXIT command:

sql
mysql> EXIT

Bye

Note: EXIT only disconnects the client from the server; the MySQL server continues running.

Managing MySQL has loaded