SQL Query Statements

The database used in this article is db_test and the data table is employee

I. SELECT statement BASIC structure

Sentence syntax is simply summed up as:

SELECT select_list [into new_table_name] [from table_source] [WHERE search_condition] [GROUP by group_by_expression]

[Having search_condition] [ORDER by order_expression [ASC | DESC]]

Note: The red font is a custom parameter and is filled in according to the actual query.

Two. With clause

The WITH clause is used to specify a temporary named common expression that is defined within the execution scope of a single statement (SELECT, INSERT, UPDATE, DELETE);

Example: Create a common expression that calculates the number of employees for each age in the employee table;

Three. SELECT ... From ... Clause

Example: In the employee inquires all female employee's name, the age information, and also lists the name;

There are three ways to list the names of others:

1. Column name as new column name

2. Column name = new column name

3. Column name new column name

PS: I personally compare love with AS

Four. INTO clause

Creates a new table and inserts the query result row into the table.

Example: Create a new table with into, named Tb_empage, containing the name and age two columns

Select name,age to Tb_empage from Employee

Five. WHERE clause

Due to the complexity of the WHERE clause, the following parameters are presented in order of precedence;

1. Logical operators (not, and, or)

Not: Take the reverse;

and: Logic and;

Or: logical OR;

Example: Use and to inquire about all female employees who are younger than 30 years old in the employee table;

Use not to query information about all female employees aged 30 years or older

Example: Using combination to query the information of male employees who are not older than 40 or older than 30 female employees;

2. Comparison operators (=, <>,! =, >, >=,!>, <, <=,!<)

This operator is easy to use and is no longer in space.

3.LIKE keywords

For fuzzy queries, wildcard characters are%, _, [], [^]

%: Can be followed by 0 or more characters

_: Matches any single character

[]: Query for a range of individual characters, including both ends of the data

[^]: Represents a single character that is not within a certain range, including both ends of the data

Example: The employee who queried the bear's name and had only two words

Example: Query employee information for age 23-27 years

4.BETWEEN keywords

Between...and and not between and

Example: Find information about female employees aged 23-27 years

5.is (not) NULL keyword

In the WHERE clause, null values need to be judged with IS (not) null, and cannot be used = to determine null values

6.in keywords

The criteria used to specify the list search;

Example: Querying employee information with a ID of

7.ALL SOME any keyword

All three are used between comparison operators and subqueries, typically with nested queries, some and any equivalents

See examples for specific functions. All is greater than the maximum, and any is less than the smallest person

Example: Querying employee information older than Bear and Kumaji

8.exists keywords

Acting on a subquery, specifying whether a row exists

9.Group BY clause

Used to group tables

10.Having clause

Specifies the search criteria for a group or aggregation that can only be used with SELECT.

Example: Query employee information for older than 2 people of the same age

11.ORDER by

Sorts in the results returned by the Select, and only if the top condition is specified can work in views, inline functions, derived tables, and subqueries.

ORDER

Example: Arranging people in a table in descending order of age

Description: Order By default is in ascending order

12.COMPUTE clause

Generate totals, appearing in the result column

Example: Calculating the average age of an employee in a table

13.DISTINCT keywords

Remove duplicate records from the select result

To remove the repetition of an age.

14.TOP keywords

Example: Displaying the first 4 rows from the Select result

SQL Query Statements