The basic method of database query, there are a lot of not all places, learn to use their own

Simple query:

First, query table name and column name

Select *from Table Name

Select column 1, column 2 From table name

Select DISTINCT column name from table name de-weight can only be a column

Second, screening

Select top three rows from table name query table

Select top 3 column name from table name where age >22 query for the first three rows older than 22 years old

1. Equivalence and non-equivalence

Select *from table name where column name = (! =, >,<,>=,<=) value

2. Multi-condition and range

Select*from table name where Condition 1 and OR OR condition 2

Select*from table name where between and

Select*from table name where column in (value)

Third, sort

Select *from table name order BY column name Asc/desc a column in ascending or descending order

Select*from table name where age<25 order by age ASC, code DESC to be less than 25 years old in ascending order, if there is the same age, then the same age in descending order

Iv. Grouping

Aggregation functions

Count (), Max (), Min (), SUM (), AVG ()

Select COUNT (*) from table name where column name count total rows

Select count (column name) the From table name counts only non-null values in this column, and if one is empty, a row is counted less

Select min (column name) from table name find the minimum value for this column

Select AVG (column name) from table name calculates the average of this column

Group by....having .....

1, group by followed by the column name

2, once the group by group, then select and from the middle can not use asterisks, can contain only two kinds of things, one is group by the column name, and the other is the statistical function

Select column name, AVG (column name) from table name Group BY column name

Having a statistical function followed by the following, further screening based on the results of the grouping

Select Column name from table name Group BY column name having COUNT (*) >1 can duplicate the find out, and show there are several identical

subquery (nested query)

Use a query statement to query a column of data, and then use it as a parameter in the query criteria for other queries

Internal connection: Select*from Table 1

Join table 2 on table 1 column = Table 2 column

Join table 3 on table 2 column = Table 3 column

Outer joins: Left and right connections

Merging data

Select Sname,ssex, Sbirthdy from student
Union
Select Tname,tsex,tbirthday from Teacher

1. All select lists in the Union must have the same number of columns, similar data types, and the same order

2. Column names from the result set are from the first SELECT statement

Connection query is an extension of columns

Union query is an extension of rows

The basic method of database query, there are a lot of not all places, learn to use their own