Access SQL syntax

Like query *
Select *
From Flash
Where filename like '* HTTP *';

Delete *
From company_guangxi
Where organization name not like '* company *' and organization name not like '* factory *';

Delete Query
Delete * from order where order. Order Date <= #1/1/2002 #
Update Query
Update product set product. Suggested price = [suggested price] * 1.2 where product. Brand Name = "Motorola"

Access SQL statement Query
Generate serial number during Query
Select (select count ([xlh]. [AA]) as autonum from xlh where (xlh. aa) <= xlh_alias.aa);) as sequence number, xlh. AA
From xlh as xlh_alias inner join xlh on xlh_alias.aa = xlh. AA
Order by xlh. AA;

Multi-Table SQL query
Select Test. AA as the first field, test1.bb as the second field, test1.cc
From test, test1
Where test. AA = test1.aa;

Multi-Table SQL query 1
Select a. AA, B. BB, B. CC, B. CC * 100 as total
From test as a, test1 as B
Where a. AA = B. AA;

Multi-Table SQL query sorting
Select a. AA, B. BB, B. cc as Third Field
From test as a, test1 as B
Where a. AA = B. AA
Order by B. CC;

Query example
Select a. dhhm
From xl11a as A, xl919 as B
Where a. dhhm = B. dhhm and AA <> "1 ";

The date and time Delimiter is # Instead of quotation marks
Select * From tab1 where [date]> #2002-1-1 #;

 

Functions and computing
Total groups, calculation formula and conditions
Select order. customer ID, sum (Order. total as tax price, last (Order. order Date) as order date, date ()-[last order date] As days ago from order where (Order. order Date> = #12/1/2006 #) group by order. customer ID
11-2 query command
Group
Condition
Multiple computing Columns
Multi-Table query command
Inner join-the most basic join method
Select customer. company name, Order. Order No., Order. Order date from customer inner join order on customer. Customer No. = order. Customer No.
11-2 query command
Multi-Table query command
Left and right join
Select customer. company name, customer. Contact Name, order. Customer No. From customer left join order on customer. Customer No. = order. Customer No. Where (order. Customer No. Is null)
11-2 query command
Four action query commands
Create a data table
Select customer. Customer No., customer. company name, Order. Order Date, order. Tax-included price into customer and order from customer inner join order on customer. Customer No. = order. Customer No.
Add Query
Insert into customer and order (customer number, company name, order date, tax-included price) Select customer. customer ID, customer. company Name, order. order Date, order. from the customer inner join order on the customer. customer ID = order. customer ID
11-3 Action query command
Four action query commands

 

The syntax differences between SQL Server and access are listed below to facilitate Query when you change the program database.

Date separator number
Access: pound sign (#)
SQL Server: marker (')

Boolean constant
Access: True, false; On, off; yes, no; INTEGER:-1 (true), 0 (false ).
SQL Server: INTEGER: 1 (true), 0 (false)

String connection
Access: and (&)
SQL Server: plus sign (+)

Wildcard
Access: The asterisk (*) matches zero or more characters.
Question mark (?) Matches a single character.
Exclamation point (!) This means that the instance is not in the list.
The pound sign (#) indicates a single number.
SQL Server: percent sign (%) matches zero or more characters.
The underscore (_) matches a single character.
The prefix (^) indicates that it is not in the list.
No character corresponding to the pound character.

Drop Index
Access: drop index <index Name> On <Table Name>
SQL Server: drop index <Table Name>. <index Name>

Add an ID column to a table
Access: alter table <Table Name> Add <column Name> counter (1, 1)
SQL Server: ALTER TABLE <Table Name> Add <column Name> bigint identity (1, 1) not null