SQL is a programming language for working with data in relational databases (RDBMS). With SQL, you use structured queries to extract and process data from the database
There are different RDBMSs, like MySQL, PostgreSQL, Microsoft SQL Server, and others, but despite their variety, the data management is similar.
This article will review the basic SQL commands and describe their syntax and operation principles.
There are four types of SQL commands for databases: DDL, DML, DCL, and TCL.
DDL (data definition language) is a list of commands for creating database objects and describing their structure.
DML (data manipulation language) is a list of operators for data corrections.
DCL (data control language) lists specialized commands to grant and restrict access to the data for the database users.
TCL (transaction control language) commands refer to the transaction management process.
Databases in SQL are a set of interrelated records stored in tables, which, in turn, are divided into columns and rows. The former describe the stored data, and the latter store it.
The first group (DDL) includes:
CREATE
is responsible for creating database objects (databases and tables):
CREATE DATABASE 'Database Name';
CREATE TABLE 'Table Name';
ALTER
is used for correcting the created database objects. For example, you can edit the name of a table that already exists:
ALTER TABLE 'Old name' RENAME TO 'New name';
DROP
deletes database objects.
DROP DATABASE 'Database name';
DROP TABLE 'Table name';
You can have multiple databases with multiple tables in it. To see a list of all existing databases, run:
SHOW DATABASES;
To select the database to continue working with it, run:
USE 'database name';
To list all the tables of a particular database, use:
SHOW TABLES 'Database name';
And this way you can see all the info about the columns of the selected table:
DESCRIBE 'Table Name';
With integrity constraints, you specify which types of data can be entered into the table.
Below, we'll list the main integrity constraints with brief descriptions and examples.
Constraint type |
Description |
Example (SQL Server / MySQL) |
|
Passes the default value if nothing was specified during data entry. |
|
|
Prohibits NULL values. |
|
|
Ensures that all values within the table are unique. |
|
|
This type combines NOT NULL and UNIQUE. It is the basis for creating indexes. |
|
|
Used for connecting two tables. |
|
|
Sets restrictions on the values passed to the table. |
|
You can add integrity constraints when creating the table. Using the ALTER
command, you can also edit them afterwards, including adding names to the constraints.
The syntax is:
ALTER TABLE 'Table Name' ADD CONSTRAINT 'Constraint Name' 'Constraint Type';
ALTER TABLE 'Table Name' DROP 'Restriction Name';
For example, let's add a constraint on the values to be passed:
ALTER TABLE 'salaries' ADD CONSTRAINT 'Check_wages' CHECK (wages>=20000);
Now let's remove it:
ALTER TABLE 'salaries' DROP 'Check_wages';
The four basic SQL commands are SELECT
, INSERT
, UPDATE
, and DELETE
. All of them belong to the second type of commands, DML. Let's look closer at each of them.
This is the most important SQL command that allows you to retrieve data from a table. Its syntax is as follows:
SELECT [DISTINCT | ALL] 'Table Columns'
FROM 'Table List'
[WHERE 'Filter Rules']
[GROUP BY 'Grouping Rules']
[HAVING 'Grouped Record Filtering Rules']]
[ORDER BY 'Sort Data']
[LIMIT 'Filter the number of records to be selected'];
This command has a large number of operators. The ones in the square brackets are optional.
Let's look at each of the operators separately.
DISTINCT
and ALL
filter the output records when executing the SELECT
command. The first allows you to retrieve unique strings without repetition. The second outputs all rows without exceptions and is applied by default.
FROM
specifies a list of tables from which to retrieve the data.
WHERE
adds rules to extract the required information from the table. You can specify comparison, special, or logical operators as rules. Here are the main operators and their descriptions.
Type |
Operators and description |
Comparison operators |
Equal To and Not Equal To;
Less Than and Greater Than;
Less Than or Equal To and Greater Than or Equal To |
Special operators |
|
Logical operators |
|
This operator groups data from one or more columns and is often used with the following functions.
COUNT
returns the number of records of a column or the whole table;
MAX
and MIN
return the maximum and minimum value of the selected column;
SUM
returns the sum of values of the selected column;
AVG
returns the average value of a table column.
This operator is similar to WHERE
and is related to the previous operator. However, it is used exclusively with aggregate data.
This operator sorts data in ascending (ASC
) or descending (DESC
) order. The first one is used by default.
This operator restricts the number of records to be selected from the table.
INSERT
adds data to a table. The syntax is as follows:
INSERT INTO 'Table Name' [('Column Name 1', ..., 'Column Name N')]]
VALUES ('Column 1 values', ..., 'Column N values);
UPDATE
allows you to update the existing data in the table.
The syntax of the command is:
UPDATE 'Table Name'
SET 'Field 1' = 'Field 1 values', ..., 'Field N' = 'Field N values'
WHERE 'Restriction Rules';
To remove some data from a table, use DELETE
.
DELETE FROM 'Table Name'
WHERE 'Restriction Rules';
-
GRANT
and REVOKE
commands that belong to the DCL type are used to manage user privileges.
GRANT
is for granting privileges.
GRANT 'System Privilege' ON 'Table Name' TO 'User Name';
REVOKE
, in turn, is for revoking privileges. Its syntax is similar to the GRANT
command:
REVOKE 'System Privilege' ON 'Table Name' FROM 'User Name';
These commands belong to the TCL type and control transactions executed in the database. Note that If you use MySQL Workbench, you'll need to disable the automatic change commit function to work with this command.
Let's look at the three main commands of this type:
COMMIT
ROLLBACK
SAVEPOINT
The first one commits a transaction, which basically means that all changes are permanently saved. The second command rolls back the transaction, and the third one specifies a logical save point to divide all transactions into blocks so that you can return to one of them.
'Performing database operations’
COMMIT;
'Executing database operations'
ROLLBACK;
SAVEPOINT 'Savepoint Name';
To return to the required savepoint, you must run:
ROLLBACK TO 'Savepoint Name';
To permanently remove a savepoint, run:
RELEASE SAVEPOINT 'Savepoint Name' 'Savepoint Name';
SQL is a programming language for working with data in relational databases (RDBMS). With SQL, you use structured queries to extract and process data from the database.
Despite the variety of DBMSs, the SQL commands you'd use are mostly the same.
There are four types of SQL commands: DDL (data definition language), DML (data manipulation language), DCL (data control language), and TCL (transaction control language).
You can specify the data types that can be entered into the table by using integrity constraints.
The most essential SQL command is SELECT
, as it is used to extract data from a table. It has a lot of different operators, which we also went over in this article.