A summary of all commands (functions, operators) in SQL database

Structured Query Language (Structured Query Language) is a database query and programming language used to access data, query, update and manage relational database systems. The sql statement is a language that operates on a database.

1. SELECT selection statement

This SELECT statement is used to select data from the database. The returned data is stored in a result table, called the result set.
SELECT syntax:

SELECT column1, column2, ...

Here, column1, column2,… are the field names of the table from which data is to be selected. If you want to select all available fields in the table, use the following syntax:

SELECT * FROM table_name;

SELECT column1, column2, ...

2. INSERT INTO insert statement

This INSERT INTO statement is used to insert new records in the table.
INSERT syntax
There are two ways to write statements in INSERT INTO:
1- Specify the column name and value to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

2-If you want to add values ​​to all columns of a table, you do not need to specify the column name in the SQL query. However, make sure the order of values ​​is the same as the order of columns in the table. Here, the INSERT INTO syntax is as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

3. UPDATE update statement

The UPDATE statement is used to modify existing records in a table.
UPDATE syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Note: Be careful when updating records in the table! Note the clause UPDATE in the WHERE statement. The WHERE clause specifies which records should be updated. If the WHERE clause is omitted, all records in the table will be updated!

4. DELETE statement

This DELETE statement is used to delete existing records in the table.
Delete syntax

DELETE FROM table_name WHERE condition;

Note: Be careful when deleting records in the table! Note the clause DELETE in the WHERE statement. This WHERE clause specifies which records should be deleted. If the WHERE clause is omitted, all records in the table will be deleted!

5. ORDER BY keywords

The ORDER BY keyword is used to sort the result set in ascending or descending order. ORDER BY By default, keywords sort records in ascending order. To sort records in descending order, use the DESC keyword.
ORDER BY Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

6. TOP, LIMIT, FETCH FIRST or ROWNUM restriction clause

The SELECT TOP clause is used to specify the number of records to be returned. The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records will affect performance.
Note: Not all database systems support this SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST n ROWSONLYROWNUM

Server/MS access syntax:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

MySQL syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Oracle 12 Syntax:

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;

7. Find the maximum and minimum values ​​of MIN() and MAX() functions

The MIN() function returns the minimum value of the selected column. The MAX() function returns the maximum value of the selected column.

MIN() syntax

SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX() Syntax

SELECT MAX(column_name)
FROM table_name
WHERE condition;

8. COUNT(), AVG() and SUM() functions

The COUNT() function returns the number of rows that match the specified condition.

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

The AVG() function returns the average value of a numeric column.

SELECT AVG(column_name)
FROM table_name
WHERE condition;

The SUM() function returns the sum of the numeric columns.

SELECT SUM(column_name)
FROM table_name
WHERE condition;

9. GROUP BY statement

This GROUP BY statement groups rows with the same value into summary rows, such as "Find Number of Customers per Country".
The GROUP BY statement is usually used with the aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result set by one or more columns.
grammar:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

10. HAVING clause

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

11. AND, OR and NOT operators

The WHERE clause can be combined with AND, OR and NOT operations. The AND and OR operations are used to filter records based on multiple conditions:
The AND operation displays a record if all conditions satisfy AND is true.
The OR operation displays a record if any of the conditions satisfy the OR is true.
The NOT operation shows that if condition (S) is incorrectly recorded.

AND Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT syntax

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

12. LIKE operator

This LIKE operator is used in the WHERE clause to search for specified patterns in columns. There are two wildcards that are often used in conjunction with the LIKE operator:
Percent sign (%) represents zero, one or more characters
Underscore () represents a single character
But note: MS Access uses asterisks (*) instead of percent signs (%), and question marks (?) instead of underscores ()
Of course, percentage signs and underscores can also be used in combination!

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

Tip: You can also combine any number of conditions using AND or OR operators.

13. IN operator

The IN operator allows you to specify multiple values ​​in the WHERE clause.
The IN operation is for multiple shorthand OR conditions.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

14. Operator between BETWEEN

The BETWEEN operator selects a value within a given range. The value can be a number, text, or date.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

15. JOIN connection

The JOIN clause is used to combine rows from two or more tables according to the relevant columns between them.

INNER JOIN Connect keywords

INNER JOIN keyword selects records with matching values ​​in both tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

LEFT JOIN Left Connection Keyword

The LEFT JOIN keyword returns all records in the left table (table1) and match records in the right table (table2). If there is no match, the result is 0 records on the right.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

RIGHT JOIN Right Connect Keyword

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

FULL OUTER JOIN Keywords

FULL OUTER JOIN and FULL JOIN are the same.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

16. Use of AS alias

Alias ​​List Syntax

SELECT column_name AS alias_name
FROM table_name;

Alias ​​table syntax

SELECT column_name(s)
FROM table_name AS alias_name;

17. EXISTS operator

The EXISTS operator is used to test whether any records exist in the subquery.
The EXISTS operator returns true if the subquery returns one or more records.

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

18. NULL null value

A field with a NULL value is a field with no value. If a field in the table is optional, a new record can be inserted or updated without adding a value to the field. The field will then be saved as a NULL value.
Note: The NULL value is different from a zero value or a field containing a space. Fields with NULL values ​​are fields that are left blank during the record creation!
How to test NULL values?
The NULL value cannot be tested using the comparison operator (for example, =, < or <>). We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

19. Wildcard*? ! wait

SQL wildcards can replace one or more characters when searching for data in a database.

SQL wildcards must be used with the LIKE operator.

In SQL, the following wildcards are available:

Use % wildcard

We want to select people living in cities starting with "Ne" from the "Persons" table above:

SELECT * FROM Persons
WHERE City LIKE 'Ne%'

Use _ wildcard

We want the last name of this record selected from the "Persons" table to start with "C", then an arbitrary character, then "r", then an arbitrary character, then "er":

SELECT * FROM Persons
WHERE LastName LIKE 'C_r_er'

Use the [charlist] wildcard

We want to select the people who live in the city starting with "A" or "L" or "N" from the "Persons" table above:

SELECT * FROM Persons
WHERE City LIKE '[ALN]%'

We want to select people who live in a city that does not start with "A" or "L" or "N" from the "Persons" table above:

SELECT * FROM Persons
WHERE City LIKE '[!ALN]%'

This is the article about the summary of all commands (functions, operators) in SQL database. For more related SQL command content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!