MariaDB Create Database And User

This is an introductory guide to the MariaDB database for new users. You will learn how to create databases, create database users, and assign permissions to users in this tutorial by following the below topics:

• How to create a database and user in MariaDB
• How to create a database Grant User
• MariaDB Create Database And Table
• MariaDB How to Create a User
• How to Create a Database in MariaDB

Also, check the latest MariaDB tutorial: How To Create Views In MariaDB Group By

MariaDB Create Database And User

We’ll go over how to build a MariaDB database and user for your apps. To build a database and user for your applications, there are a few steps to take.

The local user can be utilized for local connections that come from the same host if we wish to establish a database for the local user.

We will first create a database using the following syntax:

CREATE DATABASE database_name;

• CREATE DATABASE database_name: A database with the supplied name (database_name) is created by the CREATE DATABASE statement. You must have the CREATE privilege for the database in order to use this statement.

Use the below query to create a new database in MariaDB.

CREATE DATABASE Salary;

USE Salary;

The CREATE DATABASE line was used in the above query to create a new database called Salary. Then used the command Salary to use or work on the created database.

• MariaDB offers users a variety of functions, one of which is the ability to create a new MariaDB account by using a user statement. To add new users to the MariaDB server, we need global permission of creating users or the inserts privilege.

To create a new user with local access, and to give this user permissions on the new database use the below query:

GRANT ALL PRIVILEGES ON Salary.* TO 'James'@'localhost' identified by 'James@6999';

With the new user named James, who will be identifiable by the password James@6999, we have granted all privileges on the current database, such as Salary, using the GRANT ALL command in the above query.

We hope you fully grasped the subtopic “MariaDB Create Database And User” by using the CREATE DATABASE and GRANT ALL statements query to add a new database and user in MariaDB.

Read: MariaDB Alter Table If Exists

MariaDB Create Database And Table

We already know how to create a database from the above subsection “MariaDB Create Database And Table”. Here first, we will create a new database, then create a new table within that database.

We are already familiar with the command CREATE DATABASE to create a new database, so use the below query to create a database named Customer.

CREATE DATABASE Customer; 

To create a new table in that database, first, we need to use or select the database in which we will create a new table. Use the below query.

USE Customer; 

Use the below syntax to create a new table.

CREATE TABLE [IF NOT EXISTS] tbl_name(
    column_name_1,
    column_name_2,
    ...,
)

• CREATE TABLE: Following the CREATE TABLE keywords, first, specify the name of the table you wish to create. Each database must have a distinct table name.
• IF NOT EXISTS: To create the new table only if it does not already exist, use the IF NOT EXISTS option in the second step.
• column_name_1: Third, put a list of columns for the table inside the parenthesis, separating them with commas (,).

Create a new table named customer_information using the below code.

CREATE TABLE customer_information(
STATE_ID INT AUTO_INCREMENT PRIMARY KEY, 
STATE_NAME VARCHAR(80),
STATE_ZIPCODE INT,
FULL_NAME VARCHAR(50));

Insert the following records into the table customer_information using the below code.

INSERT INTO customer_information (STATE_NAME, STATE_ZIPCODE, FULL_NAME) 
VALUES ( 'North Carolina', 8750, 'Olenka Voules'),
('Michigan', 94768, 'Cate Marchant'),
('Texas', 8650, 'Kip Lyngsted'),
('New York', 6766, 'Alisa Sier'),
('Florida', 6733, 'Kip Garbutt');

View the newly created table customer_information using the below code.

SELECT * FROM customer_information;

We hope that you have understood the subtopic “MariaDB Create Database and Table” by using the CREATE DATABASE and CREATE TABLE statements in the MariaDB server. We have used an example and described it in depth, for better execution.

Read: MariaDB Unique Key [Useful Guide]

MariaDB How to Create a User

We are going to learn how to create a user in MariaDB Server using the statement or command CREATE USER. You need the global CREATE USER privilege or the MySQL database’s INSERT privilege to use it.

• CREATE USER adds a new, unprivileged row to “mysql.user” (till MariaDB 10.3 a table, from MariaDB 10.4) or “mysql.global_priv_table” (as of MariaDB 10.4) for each account.

Let’s take an example by following the below steps:

CREATE USER 'Robert'@localhost IDENTIFIED BY 'Usa@3009';

In the above query, the statement CREATE USER creates a new user named Robert at the localhost computer using the keyword @localhost. This @localhost can be any IP address of the MariaDB server like @server_ip where the new user needs to be created.

Then the next keyword IDENTIFIED BY is used to set the password which is “Usa@3008” for the new user named Robert.

To check the newly created user or all the users, then access the mysql.user table using the below query.

SELECT USER FROM Mysql.User;

Both accessing the MariaDB shell and managing databases are not permitted for the newly created user. So use the below query to give access to the database to the user.

GRANT ALL PRIVILEGES ON *.* TO 'Robert'@localhost IDENTIFIED BY 'Usa@3009';

The database or table for which the user has permission is indicated in the statement by the (*.*) symbol. The (*.*) symbol represents that all databases on the server are accessible with this specific command for the user Robert.

However, if you only want to allow access to a single database such as “Customer” database that we have created in the first subsection of this tutorial, you should use the following query:

GRANT ALL PRIVILEGES ON 'Customer'.* TO 'Robert'@localhost IDENTIFIED BY 'Usa@3009';

Once new privileges have been granted using the above command, It’s critical to update the privileges. So use the below command.

FLUSH PRIVILEGES;

After following the above steps, The newly created user Robert is now fully authorized and has access to the designated database and tables.

Read: MariaDB Difference Between Two Dates

Read: MariaDB Check Empty String

How to Create a Database in MariaDB

Here we will learn how to create a database in MariaDB by command prompt or using the GUI application HeidiSQL.

To know about the syntax for creating the database, please refer to the subsection “MariaDB Create Database And User”.

First, we will create a database using the command prompt, by following the below query.

Open a command prompt on your computer and login into the MariaDB server if it asks for a password, then enter the password for the user using the below command.

mysql -u root -p

Use the statement CREATE DATABASE to create a new database named USA_CENSUS.

CREATE DATABASE USA_CENSUS;

Now select the database using the below query.

USE USA_CENSUS;

Check the created database using the below query.

SHOW DATABASES;

To create a database in MariaDB using a GUI application like HeidiSQL follow the below steps:

First, open the HeidiSQL and enter the required username and password as shown in the below picture.

After opening the session manager, a window will appear.

• To create a database select MariaDB from the left side below “Database filter” and Right-click on that to show the properties.

• From the properties Click on “Create new”, then click on “Database” as shown in the below picture.

After clicking on the “Database”, a new window name “Create database” will appear. In that window specify the name of the database “USA_CENSUS” that we want to create and leave everything as it is.

To check the created database use the below picture for reference.

By using the CREATE DATABASE statement in MariaDB, we hope you have grasped the subtopic “how to create a database in MariaDB.” We have provided a detailed explanation and included an example to aid in understanding.

We have covered how to create a database and user, and also define the access rights for the user using the statement GRANT ALL PRIVILEGES ON.

You may also like to read the following MariaDB tutorials.

• MariaDB Check String Length
• How to Show Tables in MariaDB
• MariaDB Order By Multiple Columns
• MariaDB Insert Into Select
• MariaDB Insert If Not Exists
• MariaDB Add Auto Increment Column
• MariaDB Delete From Statement

In this MariaDB lesson, we covered the use of the MariaDB Create Database And User by various sample instances related to it. There are several listings of the topics that are discussed:

• MariaDB Create Database And User
• MariaDB Create Database Grant User
• MariaDB Create Database And Table
• MariaDB How to Create a User
• How to Create a Database in MariaDB