Welcome to our MySQL command reference blog, your ultimate guide to mastering the MySQL database management system. Whether you’re a seasoned developer or just starting your journey into databases, having a solid understanding of MySQL commands is essential for efficient database management and development. In this guide, we’ll cover everything from basic commands to advanced techniques, helping you become proficient in MySQL.
MySql Authentication
MySql Login with password
mysql -u root -p
MySql Login without password
mysql -u root
MySQL Databases
Display the list of all databases in MySql
show databases;
Create a new MySql database
create database [Database Name];
Drop and existing MySql database
drop database [Database Name];
Select existing MySql database
use [Database Name];
Create, Update, and Delete MySQL Users
Managing MySQL users is a critical task for database administration. Whether you’re setting up a new user, changing their credentials, or removing access, this guide will walk you through the essential MySQL commands for user management.
Creating a MySQL User
To create a new MySQL user, you use the CREATE USER statement. This command requires specifying the username, host, and optionally a password. Here’s the syntax:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
For instance, if you want to create a user named newuser with the password password123 who can connect from localhost, you would run:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';
Updating a MySQL User
Updating a user’s password or other attributes is done with the ALTER USER statement.
Changing a Password
To change a user’s password, the syntax is:
ALTER USER 'username'@'host' IDENTIFIED BY 'newpassword';
For example, to update newuser‘s password to newpassword123:
ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword123';
Deleting a MySQL User
When a user is no longer needed, you can remove them with the DROP USER statement:
DROP USER 'username'@'host';
To delete the newuser account:
DROP USER 'newuser'@'localhost';
Granting and Revoking Privileges for MySQL User
After creating a user, you need to grant them the necessary privileges to interact with the database.
Granting Privileges
Use the GRANT statement to assign privileges:
GRANT ALL PRIVILEGES ON database.* TO 'username'@'host';
For example, to give newuser full access to the mydatabase database:
GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'localhost';
Remember to reload the privilege tables to apply the changes:
FLUSH PRIVILEGES;
Revoking Privileges
To revoke a user’s privileges, use the REVOKE statement:
REVOKE ALL PRIVILEGES ON database.* FROM 'username'@'host';
For example, to remove all privileges from newuser on the mydatabase database:
REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'newuser'@'localhost';
Viewing MySQL User Privileges
To see what privileges a user has, the SHOW GRANTS statement is used:
SHOW GRANTS FOR 'username'@'host';
For example, to view the privileges of newuser:
SHOW GRANTS FOR 'newuser'@'localhost';
MySql Database Tables
Once an existing database has been selected, you can use the following commands to work on the tables.
Show all tables in MySql Database
show tables;
Display table structure in MySql Database
desc [Table Name];
Display all table records or data in MySql Database
select * from [Table Name];
Display specific columns from table records or data in MySql Database
select [Column1] , [Column2] from [Table Name];
Display top 10 records from a MySql Datbase Table
select [Column1] , [Column2] from [Table Name] limit 10;
Display top 10 records from a MySql Datbase Table in descending order
select [Column1] , [Column2] from [Table Name] order by [Column1] desc;
Display records from a MySql Datbase Table matching a column value
select [Column1] , [Column2] from [Table Name] where [Column3] = [Some Value];
Display records from a MySql Datbase Table matching some text in a string. This works like a contains operator
select [Column1] , [Column2] from [Table Name] where [Column3] like '%[Some Value]%';
MySql Database Permissions
Giving access to a new user in MySql Database
GRANT ALL ON [Database Name].* TO 'test_dbuser'@'localhost' IDENTIFIED BY 'YourPassword';
Reloading new privileges
FLUSH PRIVILEGES;
MySql Database Import and Export
Importing MYSQL Database:
mysql -u root -p [Database Name] < /home/admin/Desktop/MySqlFile.sql
Exporting MySQL Database:
mysqldump -u root -p [Database Name] > /home/admin/Desktop/MySqlFile.sql
Alterting MySql Database
Add column in existing MySQL Table:
ALTER TABLE [Table Name] ADD COLUMN [Column Name] varchar(255) DEFAULT NULL;
Mastering MySQL commands is crucial for efficient database management and development. We hope this guide has provided you with the knowledge and skills to become proficient in MySQL. Remember to practice regularly and explore further resources to deepen your understanding. Happy querying!