How to Create a Database in MySQL

By Rosa in Learning SQL 0 Comment

Introduction

When you build up an application, you need a database (db) to save your data. It could be about your order, member, or transactional data. It really depends on business needs from the application that you build. Another purpose is you can initiate improvements based on huge data that you’ve already saved.

Based on Wikipedia, a database is an organized collection of data, generally stored and accessed electronically from a computer system. Where databases are more complex they are often developed using formal design and modeling techniques Wikipedia.

There are many great databases these days, one of it is MySQL. In this section, we will learn from the beginning how to create a database, tables, and query data with MySQL.

Why MySQL:

  • It is open source. However, there are a personal and enterprise version.
  • Fast. Of course with the right indexes when you have huge amount of rows data.
  • Scalability, maintainability.
  • Suitable for web-based application. E-commerce, warehouse, logging, and many more.

Before we start, to create or manage your MySQL database, you need database client/IDE.

Three IDE options:

Personally, I find Sequel Pro is very helpful and powerful for my day-to-day use.

Start and Login to MySQL on your local machine (Mac OS X).

  1. Go to your System Preferences
  2. Find MySQL
  3. Choose to Start MySQL Server

After the MySQL database started, you can log in.

  1. Go to your database client, in this example I am using Sequel Pro.
  2. Connect to your localhost. You need to provide the username and password before login.
  3. Once you connect, you will be able to create your database.

Create new Database:

Create Database Statements
CREATE DATABASE database_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name];

Example:

CREATE DATABASE learning_mysql 
    CHARACTER SET utf8
    COLLATE utf8_general_ci;

Using Functionality Provided by IDE
  • Go to Database menu, select Add Database…

  • Then fill in the database name

For common cases and non latin, use UTF-8 for character set, and you can use utf8_general_ci for the collation.

Your database is now ready to use. Ensure you choose the right database that you want to manage. The second step is to prepare tables as per your business needs, to save the data from your application.

Happy exploring!

How do I create MySQL database programmatically in Java?

By Wayan in JDBC 4 Comments

There are times that you might need to create a database or tables right after you run your program instead of manually creating it. In this example, I will show you how you can do this using JDBC and MySQL database. The first thing we need to do as usual when creating a JDBC program is to define a JDBC URL. One thing that you’ll notice here is that we don’t define the database name in the URL. So the URL will be like jdbc:mysql://localhost.

After defining the URL, we need to create a connection to the database. We issued the DriverManager.getConnection() method and pass the URL, username and password as the arguments. The next step is to create a PreparedStatement. When we call the preparedStatement() method we pass an SQL command to create the database, which is CREATE DATABASE IF NOT EXISTS demodb. This will create a new database if there is no database with demodb name exists in the database. Finally, call the PreparedStatement‘s execute() method

Now you can try for your self, start typing the following code snippet in your text editor or IDE, and execute it to create the database.

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class CreateMySQLDatabaseExample {
    public static void main(String[] args) {
        // Defines the JDBC URL. As you can see, we are not specifying
        // the database name in the URL.
        String url = "jdbc:mysql://localhost";

        // Defines username and password to connect to database server.
        String username = "root";
        String password = "root";

        // SQL command to create a database in MySQL.
        String sql = "CREATE DATABASE IF NOT EXISTS demodb";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

After you are executing the code snippet above you will find a new database named demodb created in your MySQL database server.

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central