As a MuleSoft developer, working with MySQL databases is a common task, whether it's setting up integrations, testing APIs, or managing data. Familiarity with key MySQL commands can significantly improve our productivity and ensure seamless database management.
In this blog post, we'll explore a list of essential MySQL commands for managing databases and performing CRUD (Create, Read, Update, Delete) operations on tables.
Managing Databases
1. Create a Database
Creates a new database in our MySQL instance.CREATE DATABASE <database_name>;Example:
CREATE DATABASE sales_db;Options:
<database_name>: Name of the database to be created.IF NOT EXISTS: Prevents an error if the database already exists.
Example:
CREATE DATABASE IF NOT EXISTS sales_db;2. Show Databases
Lists all available databases in the MySQL server.SHOW DATABASES;3. Use a Database
Selects a specific database to use in the current session.USE <database_name>;Example:
USE sales_db;Options:
<database_name>: Specifies the database to use in the current session.
Managing Users
1. Create a New User
Creates a new user with specified credentials.CREATE USER '<username>'@'<host>' IDENTIFIED BY '<password>';Example:
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'securepassword';Options:
<username>: Name of the user to create.<host>: Host from which the user can connect (e.g.,localhost,%for any host).<password>: Password for the user.
2. Grant Privileges to a User
Grants specified privileges to a user on a database or table.GRANT <privileges> ON <database>.<table> TO '<username>'@'<host>';Example:
GRANT ALL PRIVILEGES ON sales_db.* TO 'test_user'@'localhost';Options:
<privileges>: Privileges to grant (e.g.,ALL PRIVILEGES,SELECT,INSERT).<database>.<table>: Specifies the database and table (use*for all).
3. Show User Privileges
Displays the privileges granted to a specific user.SHOW GRANTS FOR '<username>'@'<host>';Example:
SHOW GRANTS FOR 'test_user'@'localhost';Options:
<username>: Name of the user.<host>: Host associated with the user.
4. Revoke User Privileges
Revokes specific privileges from a user.REVOKE <privileges> ON <database>.<table> FROM '<username>'@'<host>';Example:
REVOKE SELECT ON sales_db.* FROM 'test_user'@'localhost';Options:
<privileges>: Privileges to revoke.<database>.<table>: Specifies the database and table.
5. Delete a User
Removes a user from the MySQL server.DROP USER '<username>'@'<host>';Example:
DROP USER 'test_user'@'localhost';Options:
<username>: Name of the user to delete.<host>: Host associated with the user.
CRUD Operations on Tables
1. Create a Table
Creates a new table within the selected database with specified columns and constraints.CREATE TABLE <table_name> (
<column_name> <data_type> <constraints>,
...
);Example:
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Options:
<table_name>: Name of the table to create.<data_type>: Data type for the column (e.g., INT, VARCHAR, TIMESTAMP).<constraints>: Constraints likePRIMARY KEY,NOT NULL,DEFAULT.
2. Insert Data into a Table
Inserts data into a specified table.INSERT INTO <table_name> (<columns>) VALUES (<values>);Example:
INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');Options:
<columns>: List of columns to insert data into.<values>: Values corresponding to the specified columns.
3. Read Data from a Table
Retrieves data from a table, optionally filtered by a condition.SELECT <columns> FROM <table_name> [WHERE <condition>];Example:
SELECT * FROM customers WHERE email = 'john.doe@example.com';Options:
<columns>: Specify columns to retrieve (e.g.,*for all columns).WHERE: Filters results based on the condition.
4. Update Data in a Table
Updates existing data in a table based on specified conditions.UPDATE <table_name> SET <column=value> [WHERE <condition>];Example:
UPDATE customers SET email = 'john.updated@example.com' WHERE id = 1;Options:
SET: Specifies the column and value to update.WHERE: Limits the update to rows matching the condition.
5. Delete Data from a Table
Deletes data from a table, optionally filtered by a condition.DELETE FROM <table_name> [WHERE <condition>];Example:
DELETE FROM customers WHERE id = 1;Options:
WHERE: Limits the delete operation to rows matching the condition. Omitting it deletes all rows.
Additional Useful Commands
1. Show Table Structure
Displays the structure of a table, including columns, data types, and constraints.DESCRIBE <table_name>;Example:
DESCRIBE customers;Options:
<table_name>: Name of the table to describe.
2. Drop a Table
Deletes a table from the database.DROP TABLE <table_name>;Example:
DROP TABLE customers;Options:
IF EXISTS: Prevents an error if the table does not exist.
Example:
DROP TABLE IF EXISTS customers;3. Backup a Database
Creates a backup of a database and saves it to a specified file.mysqldump -u <username> -p <database_name> > <backup_file.sql>Example:
mysqldump -u root -p sales_db > sales_db_backup.sqlOptions:
-u <username>: Specifies the MySQL username.-p: Prompts for the MySQL password.<database_name>: Name of the database to back up.> <backup_file.sql>: Redirects the output to a backup file.
4. Restore a Database
Restores a database from a backup file.mysql -u <username> -p <database_name> < <backup_file.sql>Example:
mysql -u root -p sales_db < sales_db_backup.sqlOptions:
-u <username>: Specifies the MySQL username.-p: Prompts for the MySQL password.<database_name>: Name of the database to restore.< <backup_file.sql>: Redirects the backup file into the database.