mysql-image-itgranules

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!

Quick MySQL Database Commands Reference

Post navigation


Leave a Reply

Your email address will not be published. Required fields are marked *