How to Create a New Database in MySQL

How to Create a New Database in MySQL

Creating a new database in MySQL is a fundamental task for anyone working with databases. This tutorial will provide a launching point for the steps needed to create a new database using the MySQL command line and using the Workbench GUI.

Prerequisites

In order to create a new database, you will need to have:

  • MySQL Server on your system
  • Access to the MySQL command line interface
  • Basic knowledge of SQL syntax and commands

Creating a New Database with the Command Line Interface

1. Access the MySQL command line. Using a terminal or command prompt, log into your MySQL server.

mysql -u root -p

2. After logging in, use the create database command and the name of your new database. For this tutorial, the new database will be called newdata.

CREATE DATABASE newdata;

3. You can confirm that the database has been successfully created by listing all the databases in your MySQL environment. The new database should appear in the list of all databases.

SHOW DATABASES;

4. To enter the database and start working with it, select it with the use command.

USE newdata;

5. To create a table within the database, use the create table command. The basic syntax includes listing all the column names, their data type such as int or date, and optionally any constraints that may apply to that column, such as not null or unique. Additional options may be included for each column as well.

As an example, you can create a table for job titles that includes a unique title ID number that is also the primary key, the name, and the median salary for someone with that title. You can also set foreign keys, such as the department the job title would be under. Note that if you want to reference a foreign key, the table for which that ID is the primary key should already exist in your database.

 CREATE TABLE job_titles (
 job_id INT PRIMARY KEY,
 Job_name VARCHAR(100) NOT NULL,
 Median_salary DECIMAL(10,2),
 department_ID INT,
 FOREIGN KEY (department_ID) references departments(department_ID)
 );

6. To confirm your table was created, you can show all the tables. You can also describe it to make sure the parameters you set were successfully applied.

SHOW TABLES;

DESCRIBE job_titles;

Creating a New Database with the Workbench GUI

1. Launch the MySQL Workbench application and connect to the MySQL server with your username (root by default) and password.

2. In the Database menu, select Create Database and enter the new database name.

3. In the Navigator panel, click the Refresh button and make sure your new database populates.

4. Expand the newly created database, right click on Table, then select Create Table. Here you can define all the columns, data types, and constraints. Click Apply to create the table, which will generate the relevant command line script. Click Apply again and Finish to run the code and complete the process.

5. In the Navigator panel, click the Refresh button to see the new table.

Leave a Reply

Your email address will not be published. Required fields are marked *