LU06a - SQL-DDL: DB Management

In this session, we will learn some thrilling 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 display all currently available instances.
3. How to connect to one specific db-instance.
4. how you can delete obsolete or no longer required databases.
5. How to import pre-pared databases that are ready for immediate use.

There are many ways how we can connect to our database instance. 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 database system
2. The already existing connections to our database instance
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 for our 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:

Most database systems usually have more than one instance (project areas) running, e.g. DIGITECH.CH and GALAXUS.CH, these two database instances (storage areas for different purposes) are running on the same database systems, but organize their data separately.

As a database administrator, we cannot work on different instances at the same time, so we have to choose which instance we actually want to work on. The SQL statement in question is the USE command.

As a database administrator, we cannot work on different instances at the same time, so we have to choose which instance we actually want to work on. The SQL statement in question is the USE command.

Volkan Demir