Black Friday 2023 at Serverspace. 50% cash back on Cloud Servers
October 26, 2023
Updated October 26, 2023

The base SQL commands


What is this article about?

In this tutorial, we will cover the basic SQL commands, provide examples to illustrate their usage and potential applications. We have compiled a table listing SQL commands along with a brief description of what each SQL query does, which we will show below. Then, we will describe each command in more detail.

Table of basic SQL commands

Command Result
Databases manipulation
show databases; Show all the databases available in the system
create database <database_name>; Create a database using a name of your choice
use <database_name>; The "focus" on a specific database, all subsequent commands will pertain to that database
source <file_location>; Execute the commands from the file one-by-one
drop database <database_name>; Removing specific database with all content inside
Tables manipulation
show tables; Show all the tables existing in the currently selected database
create table <table_name> (column# data_type); Create an empty database inside the current database
alter table <table_name> <operation>; Changing of table structure (such as adding a column). Look at detailed example further
drop <table_name>; Table removing
Data operations
select <condition> from <table_name>; This command is used to fetch the data stored in the specified table
insert into <table_name> (columns)

values (one-by-one);

To insert a row into a table, follow the format of "columns one after another - values one after another"
update <table_name> set <column> = <value> where <row>; Updating data in the table. All data matching the condition will be replaced
order by <column> <condition>; Output data sorting. The operator asc (ascending) or desc (descending) is used usually
group by <column> <condition>; The output data is grouped and displayed in alphabetical order based on the selected column
distinct To display only the unique values in a column, you can use a command in conjunction with the select statement.
like The operator used in conjunction with the select command to show "patternized" data
inner join By using the select statement in conjunction with a specific operator, data can be retrieved from two tables. The output is generated based on the condition that data from the first table is checked against the data of the second table, and the resulting output includes rows for which the selection condition is true.

Databases operations

Next, we will look at each specified operator with examples. The initial connection to the database service is carried out with the command:

mysql -u<username> -p

Then type your MySQL sever password.

Logging into the database

Then create an empty database:

create database <database_name>;

database creation

To illustrate the one-time execution of multiple SQL commands, let's create a file with the commands, and then use the source operator

cat <<EOF > <file_location>
create database test1;
create database test2;
create database test3;
mysql -u<username> -p
source <file_location>;

Using multiple sql commands

To check the result, let’s look at the list of available databases, then delete the unnecessary ones:

show databases;
drop database <database_name>;

Checking the results

Table operations

Next, we will use the previously created class database. We will make it active and create several tables in it:

use <database_name>;
create table <table_name>(options);

Creating tables in the database

Then fill a few data in the tables:

insert into <table_name> (columns)
values (one_per_row);

Заполнение таблиц данными

Lets look at the result:

select * from <table_name>;

results view

Let's replace a value in the third table (let's pretend that teacher Ivan Baev now teaches a different subject) and immediately check the result.

update <table_name>
set <column> = <value>
where <condition> = <value>;

select <column> from <table_name>  where <condition> = <value>;

Replacing values in the table

Delete the table:

drop table <table_name>

Deleting a table

Retrieving data from tables

To show all table content use this command:

select * from <table_name>;

Viewing the table

Also you may show "partial" data via command like this:

select <columns> from <table_name> where <condition>;

For example, the further query will display all boys older than 14 years:

Selective viewing

To describe join operator using, let's add an additional column to the boys and girls tables using the alter table statement. Then, in the lists of girls and boys, we will specify their favorite subjects using the update operator as we made above:

alter table <table_name> add column <column> <data_type> <column_position>;
update <table_name> set <column> = <value> where <condition>;

Adding a column to an existing table

Let's display all the hobbies of classmates (only unique values):

select distinct <column> from <table>

view all values in tables in one column

Look, that a zero value is in the output. To 'refine' the selection, let's add the IS NOT NULL parameter to the query:

Output of non-zero values

Next step we will illustrate the work of join operator. We will fetch all the students from two tables with the same favorite subject:

FROM girls INNER JOIN boys
ON boys.FavoriteDiscipline = girls.FavoriteDiscipline;

Example of join operation

Let's sort the girls in alphabetical order viа order operator:

alphabetical sorting of values

Lastly, it's worth mentioning that the SQL query language supports mathematical functions. For example, lets calculate children's average age:

select avg(<column>) as <any_name_of_result_column> from <table>;

sql maths function


In this article, we have described the basic SQL query commands with examples of their usage. We have discussed working with databases, tables, and sorting data

5 out of 5
Аverage rating : 5
Rated by: 1
1101 CT Amsterdam The Netherlands, Herikerbergweg 292
+31 20 262-58-98
700 300
700 300

You might also like...

We use cookies to make your experience on the Serverspace better. By continuing to browse our website, you agree to our
Use of Cookies and Privacy Policy.