MySQL CREATE USER syntax

The CREATE USER query creates a new user in the database server.
Below is the syntax of the CREATE USER statement:
CREATE USER [IF NOT EXISTS] 'username'@'hostname'
IDENTIFIED BY 'password';

In this syntax:
The username is the name of the user. And hostname is the name of the host from which the user connects to the MySQL Server.
The hostname part of the account name is optional. If you omit it, the user can connect from any host.
An account name without a hostname is equivalent to:
[email protected]%

If the username and hostname contains special characters such as space or -, you need to quote the username and hostname separately as follows:
'username'@'hostname'

Besides the single quote ('), you can use backticks ( `) or double quotation mark ( ").
Second, specify the password for the user after the IDENTIFIED BY keywords.
The IF NOT EXISTS option conditionally create a new user only if it does not exist.
Note that the CREATE USER statement creates a new user without any privileges. To grant privileges to the user, you use the GRANT statement.
MySQL CREATE USER example
1. connect to the MySQL Server using the mysql client tool:

mysql -u root -p
Enter the password for the root account and press Enter:

Enter password: ********

2. show users from the current MySQL Server:

mysql> select user from mysql.user;    


+------------------+
| user             |
+------------------+
| mysql.infoschema |
| mysql.saession   |
| mysql.sys        |
| root             |
+------------------+    
3. create a new user called azi:
mysql> create user 'azi'@'localhost' identified by 'Secure1pass!';
4. show all users again:
mysql> select user from mysql.user;        


+------------------+
| user             |
+------------------+
| azi              |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
5 rows in set (0.00 sec)

The user azi has been created successfully.
5. open a second session and log in to the MySQL as azi:
mysql -u azi -p
Input the password for azi and press Enter:

Enter password: ********
6. show the databases that azi has access:
mysql> show databases;
--Here is the list of databases that azi can access:

+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)
7. go to the session of the user root and create a new database called azidb:
mysql> create database azidb;
8. select the database azidb:
mysql> use azidb;
9. create a new table called lists:
mysql> create table lists(
-> id int auto_increment primary key,
-> todo varchar(100) not null,
-> completed bool default false);
Notice that when you press Enter, instead of showing the mysql> command, the mysql tool shows the -> that accepts new clause of the statement.
10. grant all privileges on the azidb to azi:
mysql> grant all privileges on azidb.* to [email protected];
Note that you will learn how to grant privileges to a user in the GRANT tutorial.
11. go to the azi’s session and show databases:
mysql> show databases;


+--------------------+
| Database           |
+--------------------+
| azidb              |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)
12. select the database azidb:
mysql> use azidb;
13. show the tables from the azidb database:
mysql> show tables;


+-----------------+
| Tables_in_azidb |
+-----------------+
| lists           |
+-----------------+
1 row in set (0.00 sec)
14. insert a row into the lists table:
mysql> insert into lists(todo) values('Learn MySQL');
15. query data from the lists table:
mysql> select * from lists;


+----+-------------+-----------+
| id | todo        | completed |
+----+-------------+-----------+
|  1 | Learn MySQL |         0 |
+----+-------------+-----------+
1 row in set (0.00 sec)    
So the user azi can do everything in the azidb database.
16. disconnect from the MySQL Server from both sessions:
mysql> exit