LU06a - SQL-DDL: DB Management

In this session, we will learn some exciting details about how to properly manage our databases. To achieve this, we need to know more about …

1. How to create database instances.
2. How to connect to the db-instance.
3. How to display all currently available instances.
4. How to delete obsolete databases.
5. How to import prepared databases that are ready for immediate use.

There are many ways how we can connect to our database installation. One way is from the terminal or commandline. Instead, during the module 290 we are going to use do the management with the code editor WEBSTORM, which is pictured below.

1. New connection to our MySQL installation
2. The already existing connections to our database
3. New terminal window to the database to enter sql commands (leads to 4.)
4. Terminal to the database to enter our SQL commands
5. Execute button that sends the SQL command to the database

Source: Alle verfügbaren Datenbanken anzeigen lassen

To show all currently available datases we use the SHOW command. The basic syntax is as shown belon:

 SHOW DATABASES;
 

After executing this command, we will receive the list of currently installed database instances, as shown in the following picture.

Source: CREATE DB

A new application is basicly constructed within a new enviroment. Therefore we need to create a new database instance for us. To achive this, which is done by the following command syntax.

CREATE DATABASE myDatabase;

So, if we want to create a new database for a webshop of the customer „Demir“, we need to type in:

CREATE DATABASE DemirsDB;

SHOW DATABASES;

After executing both commands, our result-set would look like:

Source: DROP DB

It can happen, that our database is corrupted, obsolete or elsewise not required any more. Thus, we have to delete it, which is done by the command

DROP DATABASE myDatabase;

So, if we want to delete demirsDB, we need to type in the following SQL commands.

DROP DATABASE demirsDB;
SHOW DATABASES;

After, executing both commands, the result-set would look like:

Volkan Demir