Appearance
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.