SQL is the abbreviation for Structured Query Language, which has established itself as the standard language for communicating with two-dimensional, relational databases. With the help of SQL, data tables can be created, linked and edited. The database language functions as a kind of interface between the databases programmed in languages such as C, C++ or Java by translating external user queries into commands that the database can understand.
SQL syntax
The database language is not case-sensitive and follows a uniform syntax with only minor differences. For example, some database systems require a semicolon after each SQL entry. The semicolon is a common command used to separate individual SQL input in databases that allow multiple SQL queries to be entered in parallel.
Common SQL commands
By default, each syntax variant understands these common SQL commands:
- select – this selects data from the database that will be stored in a results table:
select ”column1, column2, …”
from ”tablename;”
Column1 and column2 , like tablename , are placeholders at this point. The column names of the table (e.g. name, place of residence) from which you want to select the data would be inserted here. If one wanted to select all data from a table, the command would follow this syntax :
select * from ”table_name”
- update – updates all existing data in a database in the following version:
update ”table name”
set ”column1 = value1, column2 = value2, …”
However, if you only want to update certain entries, the SQL command must be extended by an attribute that determines where updates are made in the database. That would look something like this:
update ”table name”
set ”column1 = value1, column2 = value2, …”
where ”condition;”
Such a condition could be a customer ID in a merchandise management system.
- delete – deletes data tables or single data; depending on how specific the command syntax is:
- With the command
delete from ”table_name”
the entire data table is deleted. If you only want to delete selected columns, rows or entries, the SQL input needs an addition in the form of a where attribute.
- insert into – inserts new entries into the database. Appropriate attribute additions can be used to specify where which new values are to be stored.
insert into ”tablename (column1, column2, column3, …)”
values ”(value1, value2, value3, …);”
only adds the given values in the specifically executed columns of the mentioned table. If you want to add values to all columns of the table, you don’t necessarily have to list the column names in the SQL command. However, the values to be added should be listed in the same order as the particular columns in which they are to be inserted. That would look like this:
insert into ”table_name”
values ”(value1, value2, value3, …);”
- create database – creates a new SQL database. For example, you can create a new database named TestDB with the following syntax:
create database ”TestDB;”
- create table – creates a new data table within the database. With the help of additional column parameters, the column names can also be defined within the table. And datatype complements determine what kind of data should be contained in the column:
create table ”table_name” (
” column1 data type,
” column2 data type,
” column3 data type,
…”
) ;
- alter table – used to add, delete, or otherwise alter columns in an existing data table. It can also be used to set or remove various conditions or restrictions.
alter table ”table_name”
add ”columnname datatype;”
This can be used to add another column to a data table, for example. Similarly, columns can be deleted or changed with the following SQL entries:
alter table ”table_name”
drop column ”column name;”
alter table ”table_name”
alter column ”column name data type;”
It should be noted here that the alter command must be replaced by a modified command, depending on the system. For example, SQL Server and MS Access understand the alter command, but in MySQL, the modify command is required.
- drop table – deletes a data table:
drop table ”table_name;”
- create an index – creates an index within a data table. Indexes allow data to be filtered out of the databases more quickly. Although the user cannot see the indices, they significantly speed up the search queries. However, updating a data table with an index can take longer than updating a table without an index. It is therefore recommended to create an index only in the columns that are searched frequently. Multiple values can be specified at the same time in the create-index-SQL command:
create index ”indexname”
on ”table name (column1, column2, …);”
- drop index – deletes the selected index. Here again, the system within which the command is to be executed must be taken into account. In MySQL, the index delete command must look like this:
alter table ”table_name”
drop index ”indexname;”
In MS Access, on the other hand, the command is analogous to creating an index:
drop index ”indexname”
on ”table name;”
SQL command supplements
The basic commands can also be detailed with any additional parameters. This helps to filter data tables more specifically. Examples of such additions would be the parameters:
- where
- and, or, not
- order by
Using the where attribute in SQL
Data can be filtered using the where attribute. For example, only the results that meet certain conditions, which are specified with the help of the where attribute, are returned for search queries :
select ”column1, column2, …”
from ”table_name”
where ”condition;”
The attribute can not only be used in select commands but also in update, delete and other inputs.
Application of and, or and not attributes
and, or and not, in conjunction with the where attribute, enable even more specific searches within the database. And you can specify different or multiple conditions at the same time, which the filter should take into account when searching for data.
The addition of and prints all information to which the conditions listed separately by the command apply:
select ”column1, column2, …”
from ”table_name”
where ”condition1 and condition2 and condition3 …;”
Adding an or command to the where attribute returns all data that meets at least one of the conditions or properties separated by or:
select ”column1, column2, …”
from ”table_name”
where ”condition1 or condition2 or condition3 …;”
The not constraint, however, only spits out results that do not meet the specified conditions or characteristics:
select ”column1, column2, …”
from ”table_name”
where not ”condition;”
Application of the order-by attribute
order by is a SQL command attribute that can be used to specify the order in which the search results should be presented. The pure order-by attribute returns the results in ascending order by default:
select ”column1, column2, …”
from ”table_name”
order by ”column1, column2, …”
If you want to specify a descending order, you must also add a desc command to the SQL command:
select ”column1, column2, …”
from ”table_name”
order by ”column1, column2, …”
desc;