MariaDB Create Database And User

In this MariaDB tutorial, we will discuss MariaDB Create Database and User and look at several examples. There are lists of the topic that comes under discussion:

  • MariaDB Create Database And User
  • MariaDB Create Database Grant User
  • MariaDB Create Database And Table
  • MariaDB How to Create a User
  • MariaDB Create Database Example
  • 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

Here we will learn and understand how to create MariaDB Database And User for your applications. There are some steps to be followed to create a database and user for your applications.

If we want to create a database for the local user then the local user can be used for local connections which are originated from the same host. First, we will create a database by the query:

CREATE DATABASE MariaDBTips;

USE MariaDBTips;

A new database has been created called MariaDBTips in the previous query, by using the CREATE DATABASE statement.

To create a new user for local access and grant privileges to this user on the new database:

GRANT ALL PRIVILEGES ON MariaDBTips.* TO 'MariaDB_User'@'localhost' identified by 'mariadbtips';

In this query, we have used the GRANT ALL statement to provide all privileges on current_Database as MariaDBTips with the new user as MariaDB_user and which will be identified by password as mariadbtips.

MariaDB create database grant user example
Example of MariaDB Create Database and User

We hope that you have understood the subtopic “MariaDB Create Database And User” by using the CREATE DATABASE and GRANT ALL statements on the table by the query. We have used an example and explained it in depth, for better understanding.

Read: MariaDB Alter Table If Exists

MariaDB Create Database And Table

We will learn and understand the subtopic “MariaDB Create Database And Table” by using the CREATE DATABASE and CREATE TABLE statements in MariaDB, which will be explained with the help of an illustrated example.

EXAMPLE:

CREATE DATABASE MariaDB_Database; 

USE MariaDB_Database; 

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

INSERT INTO USA_STATES (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');

SELECT * FROM USA_STATES;

in the query explanation:

  • First, we have created a database called MariaDB_Database by using the CREATE DATABASE statement.
  • Second, we will use the new database as current_database called MariaDB_Database for the creation of a new table and to work inside the new database.
  • Third, we have created a table called USA_STATES with new columns as STATE_ID, STATE_NAME, STATE_ZIPCODE, and FULL_NAME by using the CREATE TABLE statement.
  • Then we inserted some records into the USA_STATES table by using the INSERT INTO statement. If we want to retrieve all records from the USA_STATES table then we will use the SELECT statement.
MariaDB create database and table example
Example of creating MariaDB Database and Table by using CREATE DATABASE and CREATE TABLE statements

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

In this MariaDB subtopic tutorial, we will learn how to create a user in MariaDB Server by query, which is explained with the help of an illustrated example.

As we have already created a database called MariaDBTips by using the CREATE TABLE statement. If we want to create a new MariaDB User, by the following query:

EXAMPLE:

CREATE USER 'MariaDB'@localhost IDENTIFIED BY 'Tsinfo@12345';

SELECT USER FROM Mysql.User;

GRANT ALL PRIVILEGES ON *.* TO 'MariaDB'@localhost IDENTIFIED BY 'Tsinfo@12345';

In this case, we will use the ‘localhost‘ hostname but not the Server IP. As we have created a user called MariaDB and we have to check the new user from the USER column in the Mysql.user by using the SELECT statement. If we want to grant all privileges to MariaDB (Host-Name), then we write the third query above.

But if you want to grant all privileges only for current_database as MariaDBTips then we will write the following query:

GRANT ALL PRIVILEGES ON 'MariaDBTips'.* TO 'MariaDB'@localhost;

We hope that you have understood the subtopic “MariaDB How to create a User” by using the MariaDB CREATE USER and GRANT ALL statements in the MariaDB localhost. For a better explanation, we have used an example and explained it in depth.

Read: MariaDB Difference Between Two Dates

MariaDB Create Database Example

In this section, we will learn how to create a database in the MariaDB by query, which will be explained with the help of an illustrated example.

If we want to create a new database in the MariaDB Server then we use the CREATE DATABASE statement for the following syntax:

CREATE [OR REPLACE] DATABASE [IF NOT EXISTS] YOUR_DATABASE_NAME
[COLLATE = COLLATION_NAME]
[CHARACTER SET= CHARSET_NAME];

Here is the syntax explanation:

  • First, specify the database name that we want to create after using the CREATE DATABASE keyword. And the database name should be unique in every MariaDB server instance. But if we create a database that has the same name as an existing database then it will throw an error.
  • Second, we want to create a database with IF NOT EXIST statement to create a database conditionally. In another way, if we create a database which already exists with IF NOT EXISTS statement, MariaDB will do nothing.
  • Third, the optional or replace clause helps to instruct MariaDB to drop a database if not exist before creating a database. It is the shortcut of the following statement:
DROP DATABASE [IF EXISTS] YOUR_DATABASE_NAME;
CREATE DATABASE YOUR_DATABASE_NAME;

Here is a sample example of MariaDB CREATE DATABASE by the following query:

EXAMPLE:

CREATE DATABASE MARIADBTIPS;

In the above query, we have used the CREATE DATABASE statement to create a database called MARIADBTIPS from the MariaDB server. Once we create a new database, we can select a new database as a current database to work with.

We hope that you have understood how to create a database by using the CREATE DATABASE statement. For a better explanation, we have used an example and explained it in depth.

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 MySQL Client by the following query:

EXAMPLE by CMD Prompt:

mysql -u root -p

CREATE DATABASE MariaDB_Database; 

In the above query, we have first connected to localhost as ROOT and given the password of the user by ENTER button on the keyboard. Then we created a database called MariaDB_Database by using the CREATE DATABASE method.

How to create a database in MariaDB tutorial
Example of How to create a database in MariaDB by using MySQL Client

EXAMPLE by MySQL Client:

Enter Password: **********

CREATE DATABASE MariaDB_Database;

USE MariaDB_Database;

in the first query, first, we entered the password for the localhost ROOT as my password is “G@meison00”. Then we created a database called MariaDB_Database by using the CREATE DATABASE statement. If we want to use a new database as CURRENT_DATABASE then we have to use the USE statement in the query. After that, start a new procedure to create a table in that current database.

How to create a database in MariaDB example
Example of How to create a database in MariaDB by using MySQL Client

We hope that you have understood the subtopic “how to create a database in MariaDB” by using CREATE DATABASE statement in MariaDB. For a better understanding, we have used an example and explained it in depth.

You may also like to read the following MariaDB tutorials.

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
  • MariaDB Create Database Example
  • How to Create a Database in MariaDB