Chapter 2: Defining the Database

 

Now that we have a basic understand of MySQL, let’s start learning some SQL commands.

 

How to Use this Book

 

Throughout this book, we’ll be building a database called companyHR that has two tables - employees and mentorships.

 

The database will be used to demonstrate most of the concepts covered. Hence, you are strongly encouraged to follow along and try out the various examples.

 

To do that, first create a folder on your desktop and name it MySQLExamples.

 

Next, launch MySQL Workbench and create a new file by clicking on File > New Query Tab and save this file as practice.sql (File > Save Script As... ) in the MySQLExamples folder.

 

*** Examples that you should try will be presented in bold. Whenever you see code presented in bold, you should type them into practice.sql and execute them yourself to try them out (even when not prompted to do so). ***

 

The source file for all code presented in bold can be downloaded from https://www.learncodingfast.com/sql.

 

Code that is not in bold is purely for demonstration...

Creating the Database

 

The first thing that we need to learn is to create a database.

 

A database is a collection of related tables, queries and views etc.

 

You can think of it as being similar to an Excel workbook. An Excel workbook contains related worksheets and charts while a database contains related tables, queries and other objects (such as views and stored routines).

 

To create a database in MySQL, we use the CREATE DATABASE keywords. A keyword is a word that has a predefined meaning in SQL. In other words, if you want to create a database, you have to type CREATE DATABASE, you cannot be creative and type other words like MAKE DATABASE or CREATE COLLECTION etc.

 

Keywords are generally not case sensitive in SQL. Hence, you can also write create database or CREATE database. However, the common practice is to use uppercase for keywords. That’s the convention that we’ll be following in this book. For all the syntaxes presented...

Using the Database

 

After we create a database, we have to let the DBMS know that we want to use this database. This is because the DBMS may be managing more than one databases concurrently. We have to let it know that all subsequent code that we write applies to the stated database.

 

To do that, we use the following syntax:

 

USE name_of_database;

 

For instance, to use the companyHR database, we write

 

USE companyHR;

 

Deleting the Database

 

Now, suppose after you create your database, you realise that you have typed the name wrongly. There is no easy way to rename a database in MySQL. What you can do is create a new database and delete the old database. To delete a database, we use the syntax

 

DROP DATABASE [IF EXISTS] name_of_database;

 

You can see that we used square brackets [ ] in the DROP DATABASE syntax above. These brackets will be used throughout the book to indicate optional content.

 

In other words, when deleting a database, the IF EXISTS keywords are optional. We use them to prevent an error from occurring when we accidentally try to delete a database that does not exist.

 

For instance, to delete a database called wrongDB, we write

 

DROP DATABASE IF EXISTS wrongDB;

 

However, if we are certain that wrongDB exists, we can simply write

 

DROP DATABASE wrongDB;