Basic SQL Commands

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.

Types of SQL commands

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.

General concepts

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

Integrity Constraints in DBMS

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.

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

Basic SQL Commands

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.

SELECT

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

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

FROM specifies a list of tables from which to retrieve the data.

WHERE

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.

GROUP BY

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.

HAVING

This operator is similar to WHERE and is related to the previous operator. However, it is used exclusively with aggregate data.

ORDER BY

This operator sorts data in ascending (ASC) or descending (DESC) order. The first one is used by default.

LIMIT

This operator restricts the number of records to be selected from the table.

INSERT

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

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

DELETE

To remove some data from a table, use DELETE. 

DELETE FROM 'Table Name' 
WHERE 'Restriction Rules';

-

Data Management Commands

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

Transaction Control Commands

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

What to remember

• 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.