MySQL Create Database Functionality: Step by Step Process

In the previous lesson we already established why database management systems are important. Now, it might be a great time to learn how to use MySQL to create databases and use them correctly.

The first question we should answer is simple: how to create a database in MySQL? This step is necessary to take before you can start saving or accessing your data.

To create MySQL database (in other words, to execute an SQL query) is not hard, and in this tutorial, we will mainly view common MySQL create database examples. As you go through them, you will understand what statements should be used and what steps have to be taken.

MySQL Create Database: Main Tips

• An SQL database is a collection of data tables which consist of columns (properties) and rows (individual entries).
• Before you start learning how to create a database in MySQL (or delete it), you should check whether you have create privileges.
• To create MySQL database, you need to execute an SQL query.

Pros

• Easy to use with a learn-by-doing approach
• Offers quality content
• Gamified in-browser coding experience
• The price matches the quality
• Suitable for learners ranging from beginner to advanced

Main Features

• Free certificates of completion
• Focused on data science skills
• Flexible learning timetable

GET 25% OFF

Pros

• Simplistic design (no unnecessary information)
• High-quality courses (even the free ones)
• Variety of features

Main Features

• Nanodegree programs
• Suitable for enterprises
• Paid Certificates of completion

UP TO 70% OFF

Pros

• A wide range of learning programs
• University-level courses
• Easy to navigate
• Verified certificates
• Free learning track available

Main Features

• University-level courses
• Suitable for enterprises
• Verified certificates of completion

FREE COURSES

Statement for Creating Database

In these examples, we will be showing you how to use the create database MySQL statement to create a new database named my_data:

<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  // Create connection
  $conn = new mysqli($host, $user, $pass);  	
  // Check connection  	
  if ($conn->connect_error) {      
    die("Failed to connect: " . $conn->connect_error);
  }
  // Create database  	
  $sql = "CREATE DATABASE my_data";
  if ($conn->query($sql) === TRUE) {      	
    echo "Database creation successful";
  } else {
    echo "Failed to create database: " . $conn->error;
  }
  $conn->close();  
?>

When you use MySQL to create databases from scratch, only the first three arguments are required for the MySQLi.

In case you want to use a specific port when you create MySQL database, you should add an empty string as an argument in its place. It should look like this:

new mysqli(host, user, pass, "", port)

<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  // Create connection  
  $conn = mysqli_connect($host, $user, $pass);  	
  // Check connection  	
  if (!$conn) {      
    die("Failed to connect: " . mysqli_connect_error());
  }
  // Create database  	
  $sql = "CREATE DATABASE my_data";  	
  if (mysqli_query($conn, $sql)) {    
    echo "Database creation successful.";
  } else { 
    echo "Failed to create database: " . mysqli_error($conn);
  }
  mysqli_close($conn);  
?>

The example below shows how the script for creating database PDO:

<?php
  $host = 'host';
  $user = 'user';  	
  $pass = 'pass';
  try {
    $conn = new PDO("mysql:host=$host;dbname=my_data", $user, $pass);      	
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
    $sql = "CREATE DATABASE my_data";    
    // use exec() because no results are returned    
    $conn->exec($sql);      	
    echo "Database creation successful.<br>";
  } catch(PDOException $e)
  { 
    echo $sql . "<br>" . $e->getMessage();
  }
  $conn = null;  
?>

One of the greatest advantages of PDO is its exception class, which allows handling common PHP database query errors. In case we find an exception thrown inside {} segment, the current block of code stops being executed and continues at the first catch() (where we have set up an error alert).

MySQL Create Database: Summary

• Any SQL database consists of data tables made of columns and rows.
• Columns depict parameters and every row is an individual entry.
• To run a query and make MySQL create new database, first make sure you have creation privileges. If you do, use create database MySQL statement