This is a post on creating databases/tables in MySQL on both Windows and Linux, with added tips to help with general usage.
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.
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.
Now here’s an example of the same on Linux using the MySQL Command-Line Client.
Create new table using a Capital letter on schema/database name.
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.
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.
The error message here informs that you can set a default database on the left-hand sidebar.
That’s one solution… the other is to use USE – all I’m adding to the code below is ‘USE butter;’
All queries after USE will assume the database specified if the schema has not been included.
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.
My next example using is MySQL WorkBench, attempting to create a table and insert some data.
Nothing got created there, so I’ll now add the semicolon at the end of the CREATE TABLE line.
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.
To bug out of the MySQL CLI window and blog post, the word is QUIT.