MySQL commands are essential tools for anyone working with databases. They allow users to perform various operations such as creating, updating, and querying data efficiently. Understanding these commands is crucial for managing databases effectively, whether for personal projects or professional applications.
Here’s a list of the top 10 most useful MySQL commands, with examples to get you started!
1. Connect to a Database
To start using MySQL, connect to the MySQL server. You’ll use the mysql command-line tool.
Command:
mysql -u username -pExplanation: This command initiates a connection to MySQL with the specified username. The -p option will prompt you to enter the password.
Example:
mysql -u root -pExpected Output:
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.21 MySQL Community Server - GPL
2. Run a Single Command
Run a specific SQL command without opening the MySQL shell using the -e option.
Command:
mysql -u username -p -e "SELECT DATABASE();"Explanation: This runs a single command, SELECT DATABASE();, to show the current database. Replace "SELECT DATABASE();" with any SQL command to execute it.
Example:
mysql -u root -p -e "SHOW DATABASES;"Expected Output:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
3. List All Databases
Use SHOW DATABASES; to list all databases available on the server.
Command:
SHOW DATABASES;Explanation: This command lists all databases that the user has access to.
+--------------------+
| Database |
+--------------------+
| information_schema |
| example_db |
| mysql |
| performance_schema |
| sys |
+--------------------+
4. Switch to Another Database
Select a specific database to run commands within it using USE.
Command:
USE example_db;Explanation: Changes the context to example_db, making it the active database.
Expected Output:
Database changed5. List All Tables in a Database
Use SHOW TABLES; to see all tables within the currently active database.
Command:
SHOW TABLES;Explanation: Lists the tables in the current database.
Expected Output:
+----------------+
| Tables_in_db |
+----------------+
| customers |
| orders |
| products |
+----------------+
6. Describe Table Schema
View the schema of a table, showing its columns, types, and constraints.
Command:
DESCRIBE customers;Explanation: DESCRIBE (or DESC) displays the structure of the customers table.
Expected Output:
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| email | varchar(100)| YES | | NULL | |
| created_at | datetime | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
7. List Users and Grants
Use SHOW GRANTS to view the privileges of the current user or a specified user.
Command:
SHOW GRANTS;Explanation: Shows the privileges granted to the current user. To see grants for a specific user, use SHOW GRANTS FOR 'username'@'host';.
+-----------------------------------------------------+
| Grants for root@localhost |
+-----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' |
+-----------------------------------------------------+
8. Show Active Connections
See all current MySQL connections with SHOW PROCESSLIST;.
Command:
SHOW PROCESSLIST;Explanation: Displays each connection’s Id, User, Host, db, Command, Time, State, and Info.
Expected Output:
+----+------+-----------+-----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-----------+---------+------+-------+------------------+
| 10 | root | localhost | example_db| Sleep | 5 | | NULL |
| 11 | root | localhost | example_db| Query | 0 | init | SHOW PROCESSLIST |
+----+------+-----------+-----------+---------+------+-------+------------------+9. Kill a Connection
If you need to terminate a problematic or idle connection, use the KILL command with the process ID.
Command:
KILL 10;Explanation: This terminates the connection with Id 10.
Expected Output:
Query OK, 0 rows affected10. Quit the MySQL Shell
Exit the MySQL command-line tool.
Command:
\qExplanation: Typing \q, exit, or quit closes the MySQL session.
Expected Output:
ByeAdditional Tips
Display Query Results Vertically: Append \G to view results in a vertical format, which is useful for wide tables.
SHOW GRANTS\G;Expected Output:
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'
Summary Table
| Command | Description |
| mysql -u username -p | Connect to MySQL |
| SHOW DATABASES; | List all databases |
| USE dbname; | Switch to a specific database |
| SHOW TABLES; | List all tables in the database |
| DESCRIBE tablename; | View the schema of a table |
| SHOW GRANTS; | View user privileges |
| SHOW PROCESSLIST; | Show current connections |
| KILL process_id; | Terminate a specific connection |
\q, exit, quit | Exit MySQL shell |
Using these MySQL commands will help you manage your databases efficiently and troubleshoot common issues!
For more advanced MySQL commands and techniques, check out our database optimization guide.
For further reading on SQL queries and data manipulation, visit our SQL Basics page.
Conclusion
Understanding MySQL commands is essential for anyone looking to manage databases effectively. From basic commands like CREATE and INSERT to advanced operations like UPDATE and DELETE, mastering these commands will enhance your database management skills.
For more information on MySQL commands, check our MySQL tutorial. If you have questions or need assistance, don’t hesitate to contact us or learn more. Start your journey into database management today!
