SQL query Statement learning experience

Data query

A (1) The SELECT statement makes a data query in the general form of:

SELECT [All DISTINCT] < target column expression > [, target column expression;] .....

from< table name or view name >

[where< conditional expression;]

[GROUP by < column name 1> [having< conditional expression;]]

[ORDER by< column name 2>[ASC or DESC]];

The order in which they are executed is:

From----WHERE-----GROUP by-----ORDER by----SELECT

(2) Common query conditions:

Query criteria	Predicate
Comparison	=, >,<,>=,<=,!=,<>,
Determine scope	Between and, not between and
Determining the Collection	In No in
Character matching	Like, isn't like
Null value	Is null, was NOT NULL
Multiple conditions	And, OR, not

(3) The SELECT DISTINCT statement is used to return only different values.

SQL SELECT DISTINCT statement

in a table, a column may contain multiple duplicate values, and sometimes you might want to list only different (distinct) values.

The DISTINCT keyword is used to return only different values. SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name

from table_name;

(4) SQL WHERE clause the WHERE clause is used to extract records that meet the specified criteria.

SQL WHERE Syntax

SELECT Column_name,column_name

From table_name

WHERE column_name operator value;

(5) SQL and & OR operators The and & OR operators are used to filter records based on more than one condition. SQL and & OR operators

if both the first condition and the second condition are true, the The AND operator displays a record. If only one of the first and second conditions is true, the OR operator displays a record.

the following The SQL statement selects all customers from the "Customers" table with the country as "Germany" and the city as "Berlin" or "München":

Example

the following The SQL statement selects all customers from the "Customers" table with the country as "Germany" and the city as "Berlin" or "München":

SELECT * from Customers

WHERE country= ' Germany '

and (city= ' Berlin ' OR city= ' München ');

(6) SQL ORDER by keyword

The ORDER BY keyword is used to sort the result set by one column or multiple columns.

The order by keyword sorts records by default in ascending order. If you need to sort records in descending order, you can use the DESC keyword.

SQL ORDER by syntax

SELECT Column_name,column_name

From table_name

ORDER by Column_name,column_name asc| DESC;

(7) SQL UPDATE Statement The UPDATE statement is used for updating records that already exist in the table.

SQL UPDATE Syntax

UPDATE table_name

SET column1=value1,column2=value2,...

WHERE Some_column=some_value;

Please note The WHERE clause in the SQL UPDATE statement!
The WHERE clause specifies which record or records need to be updated. If you omit the WHERE clause, all the records will be updated!

(8) SQL DELETE Statement The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

DELETE from table_name

WHERE Some_column=some_value;

Please note The WHERE clause in the SQL DELETE statement!
The WHERE clause specifies which record or records need to be deleted. If you omit the WHERE clause, all records will be deleted!

(Ten) SQL Aggregate Functions

The SQL Aggregate function calculates the value obtained from the column and returns a single value.

Useful for Aggregate function:

• AVG ()-return average
• COUNT ()-Returns the number of rows
• First ()-Returns the value of the number one record
• Last ()-Returns the value of the final record
• Max ()-Returns the maximum value
• MIN ()-Returns the minimum value
• SUM ()-Returns the sum

(one) SQL SELECT TOP clause

The SELECT TOP clause is used to specify the number of records to return.

SQL SELECT TOP instance The following SQL statement selects the first two records from the "Customers" table:

Instance

SELECT TOP 2 * from Customers;

(a) SQL Like operator

The LIKE operator is used to search for a specified pattern in a column in a WHERE clause.

SQL Like operator instance

the following The SQL statement selects all customers with City start with the letter "s":

Instance

SELECT * from Customers

WHERE city like ' s% ';

(in) In operator

The in operator allows you to specify multiple values in the WHERE clause.

In operator Instance

the following SQL Statement Select all customers in City as "Paris" or "London":

Example

SELECT * from Customers

WHERE City in (' Paris ', ' London ');

two · Master of the Knowledge points

SELECT clause

From clause

WHERE clause

GROUP BY clause

ORDER by clause

Having clause

Sub-query

Set operator

SQL query Statement learning experience