All versions of the SQL language are characterized by three sets of commands. Manipulation commands, definition commands, and control commands.
These commands are used through queries without much emphasis, so the division can be considered logical, more than real.
Set of SQL control commands
Control commands are used to handle server rights. These are the GRANT and REVOKE orders.
For example, we can assign rights to a user with the following command:
grant select, update, create, delete on *. * to ‘sqlDeveloper’ @ ’%’
which is a typical control command.
Set of SQL commands for definition
These are the CREATE, ALTER, and DROP commands. These commands are used to handle objects on the server, that is, everything that can be created, modified, or deleted: databases, tables, stored procedures, views…
Since it is a matter of creating objects on the server, this set, like the previous one, is usually only available to server administrators(for everyone above the user base level), so when a user gets the right to use the database, he can do most things within his database but does not have the ability to delete it or delete other server databases.
For the same reason, control commands are not available to ordinary users. Because, if the user had the opportunity to handle control commands, he could give himself high rights and lead to the mentioned interventions(deleting his own or other databases on the server).
An example of a definition command is:
create database myDB;
create table myTable
(id int primary key auto_increment,
The abbreviation DDL(Data Definition Language) is often used in practice.
If someone asks you for the DDL of your database, they are actually looking for a script to create that database.
If, for example, a database is called myBase and has one table(therefore, the database from the previous example), the DDL for that database will also be the code from the example(database creation and table creation).
A set of SQL statements to manipulate
This set of commands is the heart of SQL.
These are commands that are used often and in large quantities. We will certainly not create databases every day (unless we are database administrators), nor their structure (tables), users, etc.
This is a job that is done once(at least its key parts).
On the other hand, we perform data manipulation, which is the subject of manipulation commands, constantly.
Data manipulation commands are SELECT, INSERT, UPDATE, and DELETE.
We have already had the opportunity to get to know SELECT through a simple example in one of the previous lessons.
And the other orders of the set are no more complicated.
Example SELECT statement, which lists table rows:
select * from myTable
Example of an INSERT command that enters a row in a table:
insert into myTable (id,name) values ("","Test")
Example of a DELETE command that deletes table rows:
delete from myTable where name = "Test“
Example of an UPDATE command that updates table rows:
update myTable set name="SQL Develop" where name="Test"
The manipulation set is a bit freer when it comes to user rights. For example, we can allow most users to scroll through the rows of tables.
But you have to be careful here too. DELETE, UPDATE, and even INSERT commands in the wrong hands can cause considerable data damage.
SQL command options
We notice that none of the commands go alone. Usually(apart from, of course, parameters) some additional options follow.
These options vary from case to case and from set of commands to sets. For example, the keyword where is characteristic of a manipulation set, while we or for will often be encountered in cases of definition and control.
Also, some of the commands have a completely different syntax in different cases:
The CREATE command sounds completely different in the context of creating a database, and different in the context of creating a TABLE, although, in both cases, it is a definition command.
Other SQL functionalities
There are also many commands(keywords) that cannot logically be grouped. For example, we will use the operator-function IN (), which checks for the existence of an element in an array.
Although it can be placed in any query, this function is not an integral part of any set of commands but is a separate function.
In addition to the built-in functions, the SQL server also knows the stored procedures, which we can create ourselves and thus, practically, create new commands.
SQL commands are often capitalized:
SELECT * FROM myTable WHERE id=24;
We have already mentioned that this is a rule that we simply do not have to follow because SQL is not case sensitive(unless we use some collation that implies this sensitivity).