Select from where format detailed

One

SELECT

The complete syntax for the statement is:

select[all| Distinct| distinctrow| Top]

{*|talbe.*| [table.] Field1[as alias1][,[table.] Field2[as alias2][,...]]}

From tableexpression[,...] [In Externaldatabase]

[WHERE ...]

[GROUP by ...]

[Having ...]

[Order BY ...]

[With Owneraccess OPTION]

Description

With brackets

([])

The enclosed part representation is optional,

With curly braces

({})

The enclosed part is a representation that must be selected from

One of them.

1 from

Clause

From

clause specifies the

SELECT

The source of the field in the statement.

From

After the clause is a table that contains one or more

Tatsu-style

(

Separated by commas

)

, where the expression can be a single table name, a saved query, or an

INNER

JOIN

、

Left JOIN

Or

Right JOIN

The resulting composite result.

If a table or query is stored in an external database,

In

In

Child

The full path is indicated after the sentence.

Example: The following

Sql

Statement to return all customers with orders:

SELECT Orderid,customer.customerid

From Orders Customers

WHERE Orders.customerid=customers.customeersid

2 All

、

DISTINCT

、

Distinctrow

、

Top

Predicate

(1) All

Return to meet

Sql

All records for the statement condition. If this verb is not indicated, the default is

All

。

Cases:

SELECT All Firstname,lastname

From

Employees

(2) DISTINCT

If there are multiple records with the same data in the selection field, only one is returned.

(3) Distinctrow

If there is a duplicate record, just return one

(4) Top

Displays several records of the query's tail.

You can also return a percentage of the record,

This is to use

Top N PERCENT

Child

Sentence (of which

N

Percent expressed)

Example: Return

5%

Orders with the largest order volume

SELECT Top 5 percent*

from [Order Details]

Order by unitprice*quantity* (1-discount) DESC

3

Use

As

clause to alias a field

If you want to take a new title for the returned column,

Or

After the calculation or summary of the field,

Has produced a new

Value and want to put it in a new column display, then use the

As

Keep.

Example: Return

FirstName

Field alias is

Nickname

SELECT

FirstName as nickname, LastName, City

From

Employees

Example: Returns a new column showing inventory value

SELECT ProductName, UnitPrice, UnitsInStock, Unitprice*unitsinstock as Valueinstock

From

Products

Two

. WHERE

clause to specify a query condition

1

Comparison operators

Comparison operators

Meaning

=

Equals

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

<>

Not equal to

!>

Not greater than

!<

Not less than

Example: Return

96

Years

1

Month's order

SELECT OrderID, CustomerID, OrderDate

From Orders

WHERE orderdate> #1/1/96# and orderdate< #1/30/96#

Attention:

Mcirosoft JET SQL

In, date with

„#‟

Delimitation. Dates can also be used

Datevalue ()

function to replace the. In the comparison word

Character data, enclose a single quote

‟‟

, trailing spaces are ignored in the comparison.

Cases:

WHERE orderdate> #96 -1-1#

Can also be expressed as:

WHERE

Orderdate>datevalue ("1/1/96‟")

Use

Not

Negation of the expression.

Example: View

96

Years

1

Month

1

Orders after the day

WHERE not orderdate<= #1/1/96#

2

Range

BETWEEN

And

Not BETWEEN

）

BETWEEN ... And ...

operator specifies a closed interval to search for.

Example: Return

96

Years

1

Months to

96

Years

2

Month's order.

WHERE OrderDate Between #1/1/96# and #2/1/96#

3

List

In

，

Not in

）

In

operator to match any one of the values in the list.

In

Clause can be substituted for the

OR

A series of bars connected by a clause

Thing

For example: to find a living

London

、

Paris

Or

Berlin

Of all Customers

SELECT CustomerID, CompanyName, ContactName, city

From Customers

WHERE City in ("london‟,‟paris‟,‟berlin‟")

4

Pattern matching

(like)

Like

Operator verifies whether a field value that contains string data matches a specified pattern.

Like

Wildcard characters used in an operator

Wildcard characters

Meaning

。

Any single character

*

Characters of any length

# 0~9

A single digit between

[

Character List

]

Any value in the list of characters

[

。 Character List

]

Any value that is not in the list of characters

-

Specify a range of characters, with values on both sides being their upper and lower bounds

Example: Returns a postal code in (

171

）

555-0000

To

171

）

555-9999

Between the customer

SELECT CustomerID, Companyname,city

, Phone

From Customers

WHERE Phone like "(171) 555

-

####‟

Like

Some of the styles and meanings of operators

Style

Meaning

does not meet

Like "A*‟a

Character followed by any length

bc,c255

Like‟5[*]‟5*5 555

Like‟5?5‟5

And

5

There is any one character between

55,5wer5

like‟5# #5 ‟5235

，

5005 5kd5,5346

Like‟[a

-

Z]‟a

-Z

Any one of the characters between

5,%

Like‟[!0

-

9]‟

Non -

0-9

Any one of the characters between

0,1

like‟[[]‟

1,*

Three

.

Use

ORDER BY

Clause sort result

Order

clause by one or more (up to

16

) field to sort the query results, which can be ascending (

Asc

) can also be

In descending order (

DESC

）

, the default is ascending.

Order

Clauses are usually placed in the

Sql

The end of the statement.