Beginners guide to MySQL and MariaDB

There are various types of queries for specific tasks that can be used to accomplish those tasks. One of them is to create a user. This is a basic task that has to be accomplished from day to day.

Let's check how to create a database user

Setting Up a MySQL User Account:

Adding new user into MySQL involves adding new entry into user table in mysql database. Add new user guest with INSERT, SELECT and UPDATE privileges with password guest121.

USE mysql;

INSERT INTO user (host, user, password,select_priv, insert_priv, update_priv,ssl_cipher,x509_issuer,x509_subject,authentication_string)            VALUES ('localhost', 'guest',PASSWORD('guest121'), 'Y', 'Y', 'Y','','','','');

USE mysql basically is telling the mysql server that we want to work with database named mysql database, which holds user information and other related meta data related in form of tables.

Here is a screenshot showing successful completion of our query.

FLUSH PRIVELEGES will refresh the grant table, this means that you will start using the newly created user straight away. Other way is to restart your MySQL service, which we doubt if an efficient way.

There are other privileges to a new user by setting values of following columns in user table to 'Y' when executing INSERT query or can be update later using UPDATE query.

• Select_priv
• Insert_priv
• Update_priv
• File_priv
• Grant_priv
• References_priv
• Index_priv
• Alter_priv
• Delete_priv
• Create_priv
• Drop_priv
• Reload_priv
• Shutdown_priv
• Process_priv

You can use GRANT statement on an existing user to update their privileges, to get an idea please check this link for official MySQL document on GRANT statement

Some of other useful statements that you will end up using are

Administrative MySQL Command:

The list of important MySQL commands are:

• USE Database name: This command is used to select particular database in MySQL workarea.
• SHOW DATABASES: Shows list of databases.
• SHOW TABLES: Shows list of tables in the database.
• SHOW COLUMNS FROM tablename: Shows attributes, key information, types of attributes whether NULL is permitted, defaults and other information for a table.
• SHOW INDEX FROM tablename: Shows details of all indexes on table, including PRIMARY KEY.

For a full coverage please check this document up

In the next part of this tutorial, we will explore MySQL connections using MySQL client, PHP and C#