SQL Database Commands

Since now we have MySQL installed on our system, we can move ahead to work doing some real work. Now we will be writing codes and will be creating databases. We will have our first real interaction with SQL queries and understand the most basic ones as for now.

Before we get started, we should know where we are going to write our codes. For the purpose of writing our codes, we have two option available to us:

• MySQL Shell
• MySQL Command Line Client

Let’s understand what is the difference between the two and how to use them.

 

MySQL Shell

The MySQL Shell, as we read in the last tutorial, is actually a unified interface to operate MySQL Server using scripting languages like JavaScript and Python. The MySQL shell on startup looks something like this:

While you can work with JavaScript and Python, you can also use MySQL but before that, you need to convert it into a SQL environment. Let’s see how we can do that:

 

Changing the language

The Shell starts up with JavaScript as the default language, we first need to switch to SQL. To switch between the various scripting languages, we have a certain set of commands to do that. Let’s take a look at these commands:

• \sql: The \sql command actually help us to change the execution language to SQL, in our case MySQL.
• \js: If you want to switch your execution language to JavaScript, you use the \js command.
• \py: Python is the third language available for execution in the shell and using the \py command, we can switch our environment for executing the Python files.

Since we are only concerned with MySQL. we will use the \sql command to change the execution language to SQL. After using this command, the Shell looks something like this:

So, now as we have got the execution language of our choice, you may think that we are ready to write codes. But, that’s not the case just yet.

 

Connecting to Server

MySQL Shell is not connected to the MySQL server by default. We need to do the task manually. This is by far the most important step of using the Shell and is needed before writing any line of code as execution without the connection is impossible. There are a number of methods available to set up the connection like:

• Create a Node session, using the X Protocol.
• Create a Classic session, enabling you to use the MySQL Protocol to issue SQL commands directly on a server.

The Node Session with X Dev API, using X protocol is actually based upon the connection set up using Nodejs. Unlike MySQL which is an open source relational database, the X Dev API relational tables, and JSON Documents. The Nodejs Connector can be looked upon as an asynchronous promise-based client library for the X DevAPI.

Since we are only concerned with the MySQL commands or SQL commands, we will be creating a Classic session, enabling you to use the MySQL Protocol to issue SQL commands directly on a server. For this purpose, we need to follow the following command:

Here, the -c option specifies that we are using a Classic Session. Apart from that, we can easily notice the username root and localhost host define the hostname for your computer. After this, the Shell window ask you the password to the server as you can see in the image below:

After entering the password, the Shell window looks like this, where you can see the hostname with the port number 3306.

Note: The username and password for the Server Connection are same as the username and password set up during installation at Accounts and Roles.

With all this done, we are now able to run the MySQL commands quite easily in the Shell. But, how is the MySQL Command Line different to this?

MySQL Command Line Client

While we had to go through various steps to finally start using the Shell, the Command Line Client is quite simple. The MySQL Command Line starts by setting up the connection with the server. The MySQL Command Line Client actually asks you to fill in the password at the very start so as to make a successful connection. The window looks something like this:

After entering the password, the window is ready for execution and look like this:

So, for my easy, I will be using the MySQL Command Line Client. You are free to use any of the two available products.

 

Individual Databases in a Server

Before going into the commands, we need to understand that what happens inside a server. A server contains a number of databases and each database has its own attributes which do not have anything to do with the other databases present on the server.

Consider the box below as a server containing four databases, which are, University, Library, Bank, and Parking:

The server contains various databases and it may happen that a University student may be using the Parking. Thus, the student has his details stored in both the databases, but what would have happened if the databases were not individually walled off? How would this affect the server? Let’s say both the University and the Parking databases have a fee attribute, where the fee details are stored of University and Parking respectively. If the databases were not individually walled off, the two payments would have got mixed up and determining which payment belongs to which database would have become quite a headache.

This leads us to the concept of creating Individual Databases contained in a server.

Now, since we know about the execution environment, it is time we move forward to start our MySQL journey and write our first code.

 

Creating Databases

The MySQL Command Line Client actually executes the SQL queries to be performed upon the database. We learnt about these queries in the first tutorial, Introduction to SQL. You can refer to that for a few more details.

Note: Every command in MySQL ends with “;“, i.e., a semicolon. You should get into a habit of using it.

Now without wasting more time, we will jump onto the commands:

 

1. Show databases: 

The first command we are going through shows the existing databases. This command helps us to look at the databases present in the server. Some of the databases are predefined and are not created by us, they are installed during the Samples and Example part. The command looks something like this for execution:

After running the command you’ll get a result like this:

You may notice some difference in the databases shown in your Command Line Client compared to the one here. There is no issue if there is a difference. You will find the pre-defined databases present on your system. Once you start creating your own, they will be reflected here too.

 

2. Create database:

This is the command that gets you started, this command helps you create a new database. The syntax of the command is something like this:

create database <name>;

The <name> is actually the name you want to give to your database, which can be used for future reference. We may look at an example for a library database. The command looks like this:

If everything goes correctly, you’ll get an output like this:

The output says Query OK, 1 row affected (0.20 sec). To notice the change, you’ll need to run the show databases command, which we will give an output like:

Here, you can see that a new database, library comes into existence and hence our command was successfully executed.

Note: While naming the database, I recommend you to use Snake case, i.e., you should avoid using spaces in the name. Also, you are not allowed to use a period(.), backward slash(\) or a forward slash(/). For example, a database named hithere is permitted while hi.there, hi/there and hi\there are not permitted. In the similar fashion, we can also decide for hello_there, hello.there, hello\there and hello/there. As you may figure out only hello_there is possible out of the four but not the rest of them.

 

3. Drop database:

Now that we have created our own database, we now need to know how to delete a database. The task is pretty simple and follows a simple command, which is:

drop database <name>;

The <name>  is same as the create command, i.e., the name of the database you want to delete. Let’s take an example and delete the library database we just created. The command looks like as following:

If it is successfully executed, it will show the following output:

The output is similar to the one we got with create database, i.e., Query OK, 1 row affected (0.72 sec). 

Note: The drop command not only works with databases but also for other things like tables. It is quite like a default deletion command. We will look at other uses of this command later.

 

Now, to verify that our command worked successfully, let’s again use show databases and then notice the change:

Notice how the library database no more appears in the list of databases available. Therefore, our command work successfully and we got a perfect result.

Note: You may notice that in the show databases command I actually missed the semicolon, but got no error. Well, SQL allows multi-line queries and thus a query doesn’t execute until you put a semicolon, whether in the same line or the next. The semicolon actually specifies that you are done with your command and know it can execute.

 

4. Use database:

As we now know about the creation and deletion of the database. The next task for us is to understand, how to use a database? This actually is used to convey to MySQL which database we may be working with as we will be having many databases. It helps us to separate out the one database out of the various available ones. The command looks like this:

use <database_name>

So, for an example, I’ll create a new database school and I’ll use it here. Since you’re familiar with creating databases, I won’t show you the create command but just the use command. The command looks like this:

After successful execution of both the commands, the Command Line looks like this:

Note: Using the use command we actually switch from the server to the database that we selected. This means now the changes will occur inside the database.

Here, the final output says: Database changed. This actually signifies that we are using the selected database, but still, we are not sure which database we are using. To counter this, we need to study a new command, which is:

5. Select database:

This command actually helps us to determine which database we are using. The command syntax is simple and execution is easy:

If we use the command for the previous example, we will get an output like:

Here, you can see the database we are using is clearly visible.

Note: There is no deselect command in MySQL, you can only switch between databases using the use command. If you delete the database you are using, then select database() will give a null as an output, so beware of that.

So, these are the few of the most basic commands related to database, especially related to creating databases. In the next tutorial, we will take a look at tables, the most important part of a database.