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