This blog post is a guide on how to create databases and tables in MySQL, from a Windows and Linux computer. The information included here should help beginners who are new to MySQL.
To create a new database in MySQL, and many other relational database systems we use the CREATE DATABASE SQL Statement.
The meaning behind the word ‘database’ can vary, and often be referred to the same thing as a Schema. In MySQL, Databases and Schemas are both one of the same things.
The following MySQL tips are covered in this post:
# Create a MySQL Database
# The USE DATABASE Command
# Close MySQL Queries with Semicolons
# How to Exit from MySQL CLI
Create a MySQL Database
We can create a MySQL Database by running CREATE DATABASE databaseName
The create database example below is being done using the GUI option, MySQL WorkBench. For more information on SQL syntax and parameters of the CREATE DATABASE statement in MySQL, we can refer to the MySQL Dev Docs.
After running the above, refresh Schemas within the Navigator to the left and open the 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 creating a MySQL table within it using a Capital letter on the schema/database name.
Below is an example of doing the same on Linux using the MySQL Command-Line Client.
The above is us logging in with the ec2-user to an Amazon Linux EC2 instance. Then we create a new table using a Capital letter on the database schema name.
A common best practice is to stick to the one case for object naming in SQL for this reason. For example, use finance_database instead of Finance_Database, or use FINANCE_DATABASE.
The USE DATABASE Command
The USE DATABASE command in SQL is used to set your statements to use the specified database as your default (current) database.
To show an example of USE in SQL, I create a new database and table MySQL Workbench below. The CREATE TABLE and INSERT INTO statements include the schema name (butter), and then I attempt to run a SELECT statement on the table without including the schema name.
The returns an error – Error Code: 1046. No database selected. Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.
As described in this error message, we can set our default schema in the MySQL Workbench Navigator.
That’s one solution. The other is to use the USE DATABASE statement. I’m adding USE butter; to the TSQL in the MySQL Workbench query window.
All queries executed after running the USE command will assume the database specified when running queries without a schema name.
Close MySQL Queries with Semicolons
Semicolons are a way to determine when your SQL statement ends in MySQL.
A query does not run by hitting return in a MySQL terminal, it requires a semicolon to know when the SQL statement is complete.
The screenshot below shows a Linux client connected to MySQL. The first command includes a semicolon and returns a list of databases. The second command (DROP DATABASE) displays the example of a command not being executed.
The command does not run until we add a semicolon and hit return, as marked with a red cross on the screenshot.
To further help understand semicolons in MySQL I also will show an example in MySQL Workbench. I’m creating a new table and inserting a row of data into the table on the next row.
The CREATE TABLE statement returns an error message – Error Code: 1064: You have an error in your SQL syntax; check the manual that the corresponds to your MySQL...
The table did not get created here. If I add the semicolon at the end of the CREATE TABLE line the table will get created and a row will be inserted.
If you’re running queries line-by-line then the semicolon isn’t as much required. For more information, see this MySQL documentation link.
How to Exit MySQL CLI
This part shows you how to bug out of MySQL CLI.
To exit MySQL CLI, type QUIT and hit return in your terminal window.
