MySQL is popularly known as an open-source relational DBMS. It is easily one of the most famous open-source database systems across the globe.
Moreover, MySQL is an extremely powerful and flexible program. It is a part of the LAMP (Linux-based operating system, the Apache web server, MySQL database, and PHP for processing) technology stack.
You can use MySQL for various purposes including storing or retrieving the data in several websites, applications, or services.
Now, when we talk about database management, it is all about retrieving data from a specific database. There are several ways to retrieve information. One of the most common ways out of a ton of them is via executing queries in the command line!
But what are queries? Let us understand!
A query is simply a command that is used to retrieve any data from a table. In SQL, there are tons of queries used to perform various functions.
Most of the queries initialise with the select option.
Here is a prototype for you to understand the basic syntax of SQL queries.
But first, here are some prerequisites to fulfil the demand.
- Ubuntu machine with sudo privileges
- MySQL installed
As the basic foundation of SQL is a database, let’s learn to create one first!
How to create a simple database?
We will start with the queries but first, it is important to understand how to create a database with some tables. We will fill these tables with some data and then execute queries.
Now, let us create a database for a company that has n number of employees.
Open the MySQL prompt and the first query is as follows:
The next step is to create a database, use the following command:
CREATE DATABASE ‘company’;
To select this database, type this command:
The next step is to create tables in this database along with several columns. Let’s do that!
CREATE TABLE employee (
Now, when you execute this query, the output will be displayed on your screen as given below:
Query OK, 0 rows affected (0.00 sec)
The next step is to insert the values for each column in the employee table. Take a look at the sample data:
INSERT INTO employee(name,age,address,department)
VALUES ('Ana’, '27’, '24 abc street, 'seo’),
('Joe’, '24’, '2 bakers street, 'testing’),
('Phoebe’, '39’, '26 park avenue, 'marketing’),
('Rob’, '21’, '108 tryst avenue, 'sales’),
('Charles’, '45’, 'royal street, 56, 'seo’);
After you fill in the information the output will be as shown below:
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
Now, let us hop onto our core topic and understand the procedure to create a user in MySQL.
How to create a user in MySQL?
Head towards your terminal window. The next step is to launch your MySQL shell as the root user.
Use the following command:
mysql -u root -p
The next step is to type the root password for your account. Tap enter to proceed.
Moving on, use the below-stated command to create a new MySQL user:
CREATE USER ‘username’ IDENTIFIED BY ‘password’;
Set the username and password as per your wish.
Additionally, there is an alternative method for you to achieve the same task. Take a look.
To define the user you can use username@localhost on your machine with MySQL.
If you wish to establish a remote connection, then you can use username@ip_address. And, at the place of ip_address, use the original address of your remote system that hosts MySQL.
Now to execute it, your command would look like this:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
Well, moving on to the next segment!
Whenever you log in with a new account it is necessary to set the permissions for the user. Let us understand the procedure to conclude the same.
How to grant permissions?
Permissions are nothing but the actions that you, as a user, can take to perform several actions in the database. Take a look at the permissions given below:
- All privileges: user has full access to the database
- Insert: user can insert rows in the table
- Create: user can create new tables as well as databases
- Delete: the user is able to delete any row in the table
- Select: user can read the data of the databases
- Update: user can easily update rows of tables
- Grant: user can modify the account privileges of another user
When we talk about the grant option here is the syntax, to grant privileges to another account:
GRANT permission_type ON database.table TO 'username'@'localhost';
Now, there comes a situation where you wish to take the privileges of the user away. To do that, here are some commands.
How can you list MySQL user account privileges?
If you wish to know about the current privileges of the user, you have to follow this command as stated below:
SHOW GRANTS username;
Now, to grant all the privileges to a database using this command:
GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';
Moreover, if you want to grant all the privileges on a particular database to a user account, here is the command for you:
GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
To narrow it down a little further, what if you want to grant all the privileges to a user account in a particular table of a database? Well, type this command:
GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';
Lastly, if you wish to remove a user account completely you can use the below-mentioned command:
DROP USER 'username'@'localhost';
MySQL is a vast area that has immense features and functions.
Along with creating a user in MySQL, this article helps you to execute some additional basic commands too.
Execute them to create tables, databases, and users.
With this, you have successfully learnt the basics of creating a MySQL user along with other important commands. We hope that the information in this article serves you in the best way possible.
Keep exploring, keep evolving!