Skip to content

DML

In this section, you'll learn about possible ways to manipulate data in existing tables. Following keywords will be discussed INSERT, SELECT, UPDATE and DELETE.

Insert

Entering data into the created tables is possible by using the INSTER INTO command. The basic version of the query looks like this:

INSERT INTO table [(column1, column2, ..., columnN)] VALUES (value1, value2, ..., valueN);

This command allows you to enter a new row into the table, where each value corresponds to a column with an analogous position:

  • value1 -> column1
  • value2 -> column2
  • valueN -> column3

For the following table:

CREATE TABLE Product
(
    ProductId INTEGER PRIMARY KEY,
    Name VARCHAR(40),
    Description VARCHAR(255)
);

An example of entering data will look like this:

INSERT INTO Product (ProductId, Name, Description) VALUES (1, 'Macbook Pro 16', 'Late 2019');

It is worth noting that if you want to enter data that are literal characters (i.e. enter data into the column with type, i.e. VARCHAR or NVARCHAR) we have to enter them inside the characters '. In addition we are not obliged to set the value of all columns (e.g. value [default] can be used (DDL.md#default) or automatically generated), however, the order of the values is important.

Data can also be entered without providing column names, e.g.:

INSERT INTO Product VALUES (1, 'Macbook Pro 16', 'Late 2019');

However, in the above case, the values must be passed in the order of columns entered when creating the table (which can be obtained by using the Description data commands here).

Alternative form of the INSERT instruction

The second version of the INSERT statement looks like this:

INSERT INTO table SET column1=value , column2=value2, columnN=valueN;

The instruction in this form allows you to set specific fields without going into the order of the columns and values:

INSERT INTO Product SET ProductId=2, Name='Macbook Pro 13', Description='Early 2018';

Inserting many records

Using the INSERT instruction,also have the option of entering multiple rows (but this way of entering data is not supported by all relational databases). The instruction in this form looks almost identical to the basic one, however subsequent records must be enclosed in parentheses (), i.e.:

INSERT INTO Table_name (column1, column2, ..., columnN) VALUES
(value1A, value2A, ..., valueNA),
(value1B, value2B, ..., valueNB);

The actual example may look like this:

INSERT INTO Product (ProductId, Description, Name) VALUES
(3, 'Dell XPS 13', 'New 2020 model'),
(4, 'Iphone XS', 'Flag Apple from 2018');

Select

All data stored in the tables can be retrieved using the SELECT statement. The instruction itself can be very extensive due to the large number of clauses available.

SELECT column1, column2, ..., columnN FROM table [WHERE condition] [ORDER BY column1, column2, ..., columnN [ASC | DESC]];

The above instruction means that we want to get a set of entered values for the columns: column1, column2, ..., columnN from the table named table, where individual records must meet condition, and the results should be sorted by the indicated columns in the ORDER BY clause in ascending order ASC or decreasing DESC.

Further examples will be implemented based on the table:

CREATE TABLE Product
(
    ProductId INTEGER PRIMARY KEY,
    Name VARCHAR(40),
    Description VARCHAR(255)
);

INSERT INTO Product (ProductId, Name, Description) VALUES
(3, 'Dell XPS 13', 'New 2020 model'),
(4, 'Iphone XS', 'Flag Apple from 2018');


Display the entire contents of the table

To display the entire contents of the table, execute the command:

SELECT * FROM Product;

The operator * means that we include the entire structure of the Product table.

The same command can be performed with sorting in ascending order:

SELECT * FROM Product ORDER BY Name ASC;

Sorting itself can be done on multiple columns. In this case, the columns and the sorting type are separated by commas, e.g.

SELECT * FROM Product ORDER BY Name ASC, Description DESC;

ATTENTION: ASC is the default sort type, i.e. ORDER BY column means the same asORDER BY column ASC.


Display the table based on selected columns

In order to display data from specific columns, indicate their names, separated by a comma, e.g.

SELECT Name, Description FROM Product;

The above query will display data only for the specified columns, unaccounted columns will be ignored.

Aliases

As part of the SELECT queries, you can modify the names of the displayed columns, which is implemented using aliases. We define the alias by adding the keyword AS and the name we choose after the column name, e.g.

SELECT Name AS Name_Productu, Description AS Description_Productu FROM Product;

The above query will display Product_name and Product_description instead of the actual names of these columns in the returned result.

Criteria for extracted data

Specifying specific data collection criteria is carried out using the WHERE clause. As part of it, we can use many operators, both relational and logical.

Relational operators

SQL operators

ATTENTION: In SQL, the inequality can also be represented by the characters <>.

Logical operators

SQL operators

Examples

SELECT * FROM Product WHERE Name='Macbook';

The above query is responsible for searching all records whose Name is Macbook.

SELECT * FROM Product WHERE Name='Macbook' AND Description LIKE 'Late%';

The above query is responsible for downloading all records that have the name Macbook and Description indicate that value starts with the charactersLate. The LIKE operator, in addition to the % character, which means many (also zero) of any characters, also allows the use of the character _, which represents any single character.

SELECT * FROM Product WHERE ProductId BETWEEN 3 and 10;

The above query will allow to find all Products that have ProductId in the range of 3 and 10. It is noteworthy that both the lower and upper limit values are included in the search.

SELECT * FROM Product WHERE Name IN('Macbook', 'Dell');

As part of the query above, you can download all records whose Name is one of those defined under theIN operator. Remember that by using many values inside the set IN, the time taken to execute such a query may increase significantly.


Limiting the number of records

Restricting returned records can be done using the LIMIT clause, e.g.

SELECT * FROM Product LIMIT 10;

The above query will allow you to return the first 10 records from the Product table.

Under the same clause, we can limit data collection from a specific item:

SELECT * FROM Product LIMIT 4,3; -- the first number indicates the position, the second number records

The above query allows you to return records from the Product table starting from item 4.

Update

The data in the tables can be modified or changed using the UPDATE statement, which looks like this:

UPDATE table SET column1=value1, column2=value2, ..., columnN=valueN [WHERE condition];

The above query allows you to set specific values for the columns: column1, column2, column3 with optional fulfillment of the condition.

The following command will allow you to change the name of each Product from the Product table to the specific value indicated in the query:

UPDATE Product SET Name='Macbook Pro';

To update a specific row of the table, use the WHERE clause:

UPDATE Product SET Name='Macbook Pro' where Name='Macbook';

In the example above, the Name will change only those records that have the name Macbook.

Delete

Deleting data from the table is possible using the DELETE statement, which structurally looks like this:

DELETE FROM Product [WHERE condition];

This command allows you to remove content from the Product table under an optional condition defined as condition.

DELETE FROM Product;

The command above removes all records from the Product table. The command below will delete all records that have the text Macbook in the name.

DELETE FROM Product WHERE Name LIKE `Macbook%`;

As part of the WHERE clause, we can use any relational or logical operators, e.g.

DELETE FROM Product WHERE Name='Macbook' AND ProductID IN(2,3,10);