Menu & Search
Creating MySQL Databases & Tables (with added tips)

Creating MySQL Databases & Tables (with added tips)

This is a post on creating databases/tables in MySQL on both Windows and Linux, with added tips to help with general usage.

I’ll cover the following in this one;
# Creating a MySQL Database.
# The ‘USE <database>’ Command.
# MySQL Semicolons.
# Quit.


Creating a MySQL Database

The syntax a simple – creating a MySQL Database uses the same command as other database software, CREATE DATABASE.

This example is using the GUI option, MySQL WorkBench.

MySQL WorkBench Create Database

After running the above, refresh Schemas within the Navigator to the left and open up your new ‘database’.

If you’re instance running on Linux, database names are case sensitive, whereas on Windows this isn’t an issue. Below, I’m creating a database on a local MySQL install (Windows 10) and then create a table within it using a Capital letter on the schema/database name.

MySQL WorkBench Create Table and Insert Data

Now here’s an example of the same on Linux using the MySQL Command-Line Client.

Connection…

SSH Connect to Amazon Linux

Create new table using a Capital letter on schema/database name.

MySQL Create Table and Insert Data

A common best practise is to stick to the one case for object naming for this reason. For example, use finance_database over Finance_Database… or FINANCE_DATABASE may also be fine. 


The ‘USE <database>’ Command

USE <DATABASE> is used to set your statements to use the specified database as your default (current) database. It’s a thing I think easier explained via screenshots…

In MySQL Workbench below, I create a new database/table and insert a row of data. Each statement I run must to include the schema name (butter)… I intentionally run SELECT without including the schema name to show the error.

MySQL WorkBench Create Table and Insert Data (without schema)

The error message here informs that you can set a default database on the left-hand sidebar.

MySQL WorkBench Set Default Database

That’s one solution… the other is to use USE – all I’m adding to the code below is ‘USE butter;

MySQL WorkBench Create Table and Insert Data (with USE)

All queries after USE will assume the database specified if the schema has not been included. 


MySQL Semicolons

Semicolons are a way to determine when your statement ends within a query. It’s not required at the end of all queries, so here’s a few examples to help explain when it’s needed.

First I’ll start off with MySQL CLI – a quick example of when we do need to exit queries with the semicolon.

Below I type DROP DATABASE and hit Enter a few times in attempt to run it. Nothing happens until I add the semicolon.

MySQL Drop Database Delayed Exit

My next example using is MySQL WorkBench, attempting to create a table and insert some data.

MySQL WorkBench Create Table and Insert Data (no exit)

Nothing got created there, so I’ll now add the semicolon at the end of the CREATE TABLE line.

MySQL WorkBench Create Table and Insert Data (with exit)

The sequence of queries was successful this time!

If you’re running queries line-by-line then the semicolon isn’t as much required – For more information, see this MySQL documentation link.


Quit

To bug out of the MySQL CLI window and blog post, the word is QUIT.

MySQL Status and Quit