How to Create MySQL Databases & Tables

Setting up MySQL databases and tables is a foundational skill for database administrators and developers. Whether you’re using Windows, Linux, or a cloud environment, this guide is aimed to help you create databases and tables in MySQL.

Topics covered:
> Create a MySQL Database
> The USE DATABASE Command
> Closing MySQL Queries with Semicolons
> How to Exit from MySQL CLI

Create a MySQL Database

To create a new database in MySQL, you use the CREATE DATABASE SQL statement. In MySQL, the terms “database” and “schema” are often used interchangeably.

Example Using MySQL Workbench:

1. Open MySQL Workbench.
2. Run the following command to create a database:

CREATE DATABASE example_database;

3. Refresh the Schemas panel in the navigator to view the new database.

Create Database MySQL WorkBench

Note: On Linux systems, database names are case-sensitive, unlike Windows systems. To maintain consistency, stick to a consistent naming convention such as finance_database rather than mixing cases like Finance_Database.

Below, I’m creating a database on a local MySQL install (Windows 10) and creating a table with a capital letter on the schema/database name.

MySQL Create Table and Insert Data

Example using MySQL Command-Line Client:

mysql -u root -p
CREATE DATABASE example_database;
SSH Connect to Amazon Linux

The above shows logging in with the ec2-user to an Amazon Linux EC2 instance. Next, I’m going to create another new table using a capital letter on the database name:

MySQL Create Table and Insert Data

ERROR 1049 (42000) : Unknown database 'Butter'

Great to show this error example, which is happening because we’re not using lower-case for the database name.

2. The USE DATABASE Command

The USE command sets the default database for subsequent operations.

Example: Imagine we have already created a butter database and need to create a table within it. If you forget to specify the schema when running queries, MySQL may return the following error:

MySQL WorkBench Create Table and Insert Data (without schema)

Error Code: 1046: No database selected

Resolve this by setting the default schema either by double-clicking its name in the Schemas list or using the USE statement:

-- use database example 
USE butter;
MySQL WorkBench Set Default Database

3. Closing MySQL Queries with Semicolons

In MySQL, semicolons (;) indicate the end of a statement. Without a semicolon, the query will not execute.

Example:
The screenshot below shows a Linux client connected to MySQL. The first SQL command includes a semicolon and returns a list of databases. The second command does not include a semi-colon:

MySQL Drop Database Delayed Exit

Adding a semicolon ensures the table is created and the row is inserted without errors.

Here’s an example the GUI way, in MySQL Workbench:

MySQL WorkBench Create Table and Insert Data (no exit)

Note: If executing queries line-by-line, semicolons may not be required.

4. Exiting MySQL CLI

As a final tip for this blog post, I’m showing you how to bug out of the MySQL terminal window.

To exit MySQL CLI run the following:

QUIT;
How to exit out of MySQL in Terminal


Recent Posts
Categories
Tags

Always On Availability Groups (AAG) (4) AWS (4) AWS Redshift (6) Certificates & Encryption (3) Change Data Capture (CDC) (2) Database Admin (72) Database Backups & Recovery (14) Database Mirroring (2) Deleting Data (1) Error Messages (5) Git Commands (6) Importing & Exporting Data (2) Linked Servers (3) Linux Admin (2) Logging & Monitoring (1) Measuring Databases (10) Microsoft Patching (2) MySQL (4) Postgres (6) PowerShell Scripts (1) SQL Server Agent (5) SQL Server Database Files (1) SQL Server Data Types (2) SQL Server Management Studio (SSMS) (15) SQL Server Network Connectivity (3) SQL Server on Linux (1) SQL Server Patching (2) SQL Server Performance (6) SQL Server Permissions (2) SQL Server Processes (SPIDs) (7) SQL Server Replication (2) SQL Server Scripts (13) Windows Admin (21) Windows Authentication (2) Windows Automation (1) Windows Events (2) Windows Firewall (4) Windows Subsystem for Linux (WSL) (18)