26.10.2023

The base SQL commands

VPS Servers With MySQLDeploy a server with MySQL
Run MySQL server in 1 click
from €4/mo

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.

Then create an empty database:

create database <database_name>;

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;
EOF

mysql -u<username> -p
source <file_location>;

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

show databases;
drop database <database_name>;

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);

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>;

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>;

Delete the table:

drop table <table_name>

Retrieving data from tables

To show all table content use this command:

select * from <table_name>;

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:

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>;

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

select distinct <column> from <table>

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

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

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

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

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>;

Conclusion

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