Sql commands you should know as a computer geek

Being a computer geek, there is a lot of information to learn each day it passes. Lately I am experimenting a little with the structured query language, a language which is a domain specific one. According to the information shared on Wikipedia, sql is a language which is being put to use to manage data in a relational database management system.

In other words, the structured query language can be utilized by the programmer to perform specific operations in the database management system found in the backend. Operations may include the followings:

• Create new records in the database.
• Update the existing records.
• Delete the existing records.
• Select the existing records.

We are going to cover the operations shown above through this article. Before going any further, make sure you have a database management system installed on your local machine. I have MySQL server version 5.6.37 running on my local machine.

How to show the available databases within the database management system

Once you have managed to setup a database management system on your local machine, it is time to experiment with the structured query language. The first thing which we are going to experiment with is the sql code which helps to show all the available databases within the database management system.

show databases;

Once I executed the above sql code on the console of my MySQL server, I got the following output.

information_schema
bWAPP
books
bookstore
employees
hacking
library
mysql
performance_schema
students

The databases being shown above are currently present in the MySQL server which is installed on my local machine.

How to use a specific database within the database management system

Once you have managed to print a list of the available databases in your MySQL server, you can easily select to make use of a specific one by making use of the sql code which is being shown below.

use books;

As you can see from the above piece of sql code, the database which I want to make use of is called books. You can replace it with your own.

How to list all records within a table

As you may already know, a database is composed of tables. Each one of the tables in the database is utilized to store data. One can easily list all the rows within a table by making use of the following sql command.

select * from database_name.table_name;

How to create a fresh database within the database server

There are many cases in which you need to create a new database. The sql command for creating a fresh database within the database server is being shown below.

create database pupils;

Once you have managed to create a fresh database within your database server, just do a listing to make sure everything went fine.

show databases;

How to create a new table within the database

Once you have managed to create a fresh database within your database server, you can easily create a custom table within the database by making use of the following command.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

Let’s put the above piece of syntax code in usage through a real world scenario. Supposing I want to store pupils of a class within the custom table pupils on the database pupils which I have already created.

I have to write and execute the following code on my database server.

CREATE TABLE pupils (
    name varchar(255),
    lastname varchar(255),
    age int
);

How to create a new record within the table

Now that we have managed to have a custom database within the database server, we can easily populate its tables by making use of the structured query language.

The sql syntax for inserting data within the table of a database is being shown below.

INSERT INTO pupils (name, lastname, age)
VALUES ('oltjano', 'terpollari', 27);

Once I executed the above sql code on the console of my database server, I got a row inserted in the table called pupils.

How to select a row based on a specific column

There are many cases during which you have to select a table row which matches a specific criteria. The sql syntax for doing so is being shown below.

select * from database_name.table_name where column_name='';

For example if I have to select a pupil based on his or her name, I use the following.

select * from pupils.pupils where name ='oltjano';

The above sql query asks the database for a pupil within the pupils.pupils table, a pupil whose name is ‘oltjano’. If the pupil exists and matches the criteria, the database will return the row.

How to update an existing record within the database

There are many times when you need to update an existing record. The sql syntax for doing so is being shown below.

 
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

And the following is a practical usage of the above syntax.

update pupils set lastname='smith' where name='oltjano';

How to delete an existing record

The sql syntax for deleting an existing record is being shown below.

delete from table_name where condition;

The practical example is being shown below.

delete from pupils where name='oltjano';

Final thoughts

Knowing a bit of sql does not hurt. It is a requirement for anyone who is interested in developing database oriented applications. Through this article your learned some of the basics, assuming you had zero knowledge on such topic.