Creating a database

In this lesson we will create a database. The SQL commands are not case sensitive, but I will be using uppercase letters for SQL commands and lowercase for database names, table names, and field names. Also we will not be using spaces in the names of databases, tables, or fields, but rather underscores to separate logical words.

Many of the database systems require a semicolon at the end of each SQL statement. The semicolon will allow you to add more than one SQL statement at a time. Although single SQL statements will work without a semicolon in many systems, we will be using them in this course. If you forget to end the statement with a semicolon you will most likely change the prompt to a sub menu; to exit just enter a completing semicolon and press enter.

Creating the database

Once logged into the database systems we can execute the CREATE DATABASE statement followed by the name of the database we want to create. Below is an example statement:

CREATE DATABASE database_name;

MySQL and other enterprise level database systems will allow you to create more than one database to work with. If you are a student, this will allow you to create a new database for every project or class you are working on all on the same system.

Viewing your database(s)

To get a list of all the databases that are on your system you can use the SHOW DATABASE command. Depending on your database installation, you may have several databases already installed on your system. To view your databases use the command:

SHOW DATABASES;

Working with a database

Once your database is created we can then work inside of it to create tables and run queries on the data. To select the database to work with we will use the USE command followed the the name of the database. Below is an example of the statement:

USE database_name;

If you want to see what is the currently selected we can use the SELECT DATABASES() command. This command uses parenthesis directly after the work DATABASES. Below is an example:

SELECT DATABASE();

Removing a database

When working with databases we drop databases and tables, and delete fields. So to remove a database we will use the DROP DATABASE command followed by the name of the database. For example:

DROP DATABASE database_name

If the database did not exist we may get an error. To run the DROP DATABASE command only if the database exists we can use the following command:

DROP DATABASE IF EXISTS database_name;

Lesson Activities

  1. Log into MySQL and create a new database named school using the command:
    Click to see code
    CREATE DATABASE school;
  2. Try to repeat the same command. You should get an error because it already exists.
  3. Modify the command for creating the school database to only create it if it does not exist by using the command:
    Click to see code
    CREATE DATABASE IF NOT EXISTS school;
  4. View the databases that are on your system by using the command:
    Click to see code
    SHOW DATABASES;
  5. Select the school database for use by using the command:
    Click to see code
    USE school;
  6. Now to view your selected database use the command:
    Click to see code
    SELECT DATABASE();
  7. Now lets drop the database by using the command:
    Click to see code
    DROP DATABASE school;
  8. View the list of databases again to see if the school database has been removed by using the command:
    Click to see code
    SHOW DATABASES;
  9. Create the school database and select it for use in the next lesson:
    Click to see code
    CREATE DATABASE IF NOT EXISTS school;
    Click to see code
    USE school;

Questions