Access Grammar Encyclopedia

Access Grammar Encyclopedia

Like query with *
SELECT *
From Flash
WHERE filename like ' *http* ';

DELETE *
From Company_guangxi
WHERE organization Name not LIKE ' * Company * ' and agency name not like ' * factory * ';

Delete Query
DELETE * FROM Order WHERE order. Next single date <= #1/1/2002#

Update Query
UPDATE product SET product. Suggested Selling Price = [Recommended price]*1.2 WHERE product. Factory brand = "MOTOROLA"

Access SQL statement query
generate ordinal when query
Select (select Count ([xlh].[ AA]) as Autonum from Xlh WHERE ((XLH.AA) <=XLH_ALIAS.AA);) As serial 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 the third field
From Test as a, test1 as B
WHERE A.AA=B.AA
Order BY b.cc;

Query Examples
SELECT A.DHHM
From xl11a as a, xl919 as B
WHERE A.DHHM=B.DHHM and AA <> "1";

Date Time separator is # instead of quotes
Select * from Tab1 Where [date]> #2002 -1-1#;

function and calculation
Aggregated groups, expressions, and conditions
SELECT order. Customer number, Sum (order. Included) as the total tax price, last (order. Order date) as the final sum of the date of order, date ()-[the last of the order date as the current number of days from the order WHERE (order. Order Date >=# 12/1/2006#) GROUP by order. Customer number
11-2 Query Instructions
Group
Conditions
Multiple calculated fields
Multi-Table Query instructions
INNER join-The most basic way to JOIN
SELECT customer. Company name, order. Order number, order, order date from customer INNER JOIN order on customer. Customer number = order. Customer number
11-2 Query Instructions
Multi-Table Query instructions
Left and right JOIN
SELECT customer. Company name, customer. Contact person name, order. Customer number from customer left JOIN order on customer. Customer number = order. Customer number WHERE (order. Customer number is Null)
11-2 Query Instructions
Four kinds of action query instructions
Made into a table of data
SELECT customer. Customer number, customer, company name, order. Order date, orders. Include tax price into customer and order from customer INNER JOIN order on customer. Customer number = order. Customer number
New query
INSERT into customer and order (customer number, company name, next date, including tax price) SELECT customer. Customer number, customer. Company name, order. The order date, the orders. Tax price from Customer INNER JOIN order on customer. Customer number = Order . Customer number
11-3 action Query Instructions
Four kinds of action query instructions

The syntax differences between SQL Server and access are listed below to facilitate queries when replacing the program database.

Date separator Symbol
Access: Pound sterling character (#)
SQL Server: apostrophe (')

Boolean Constants
Access:true, False;on, Off;yes, No; integers:-1 (true), 0 (false).
SQL Server: Integer: 1 (True), 0 (false)

string concatenation
Access: And number (&)
SQL Server: Plus (+)

Wildcard characters
Access: An asterisk (*) matches 0 or more characters.
The question mark (?) matches a single character.
The exclamation mark (!) means not in the list.
The pound sign (#) means a single number.
SQL Server: Percent semicolon (%) matches 0 or more characters.
The underscore (_) matches a single character.
The upper caret (^) means that it is not in the list.
There are no characters corresponding to the Sterling (#) character.

DROP INDEX
Access:drop Index < index name > on < table name >
SQL Server:drop Index < table name. < index name >

Table Add identity column
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

1. Basic SQL statements
Select field, ' String ' from table name where condition ORDER by DESC/ASC
Update table name Set field = value Where condition
Delete from table name where condition
Insert into table name (field name 1, field name 2) VALUES (' Value 1 ', ' Value 2 ')
Note: Field names, table names are best used [] to avoid encountering reserved word errors even if not reserved words use the parentheses below to develop a good habit.
I use the site maintenance management system here to demonstrate
First set up a database
SELECT * FROM [public]
The most basic statement shows all the fields in the table if we just display a text, what if we need to display something else? Here's the good thing is that access's SQL statement can do simple data processing, like some data is too long. We just need to show 20 characters below.
What's the effect? Field names can be processed by a variable to invoke some basic functions these functions can refer to the help of access
See it.
So is it possible to output strings directly? I set up a constant column to do the string
But this kind of thing can not use this tool to change directly after all, I this is used to black station is not to do experiments ...
Then in the actual application can be directly processed into the HTML code directly output.
Let's see, for example, we need this <a href= "? id= user id> User name </a> Such data format the general practice is to set the database from the ID and user name and then into the ASP, respectively, 2 variable processing then we look at the operation directly in SQL. Use the "&" symbol before and after the string with single quotes
It's coming out, right?
SELECT ' <a href=?id= ' &id& ' > ' &user& ' </a> ' from [user]
I analyzed the "Constants" & Variables & ' Constants ' After the database fields were processed by variables after the ' <a href=?id= '.
Well, this should be familiar. After that, it's important to make a list of the variables that are processed in text format. If more than 255 of the data will be lost, so in the actual use should be aware of the decomposition length of a long column into multiple columns, such as
To use, to indicate a separate call after a column.
followed by the condition table name followed by the where order is the DESC reverse/asc Order
That's clear, isn't it?
Update table name Set field = value Where condition
It's easier to modify the condition, not to use the character type, low efficiency and error prone.
Delete is the same. Delete from [test] WHERE name= ' test '
This data is deleted.
(Field name 1, field name 2) This is omitted to indicate that all fields are inserted sequentially (field name 1, field name 2)
INSERT INTO Test VALUES (' Test ', 2,now)
Note that this now is also a function that represents the current time in general the value of time is greater than the probability of the present is not specifically set
Sql= "SELECT * from data table where field name = field value order BY field name [desc]"

Sql= "SELECT * from data table where field name like '% field value ' Order by field name [desc]"

Sql= "SELECT top * from data table where field name order by field name [desc]"

Sql= "SELECT * from data table where field name in (' Value 1 ', ' Value 2 ', ' Value 3 ')"

Sql= "SELECT * from data table where field name between value 1 and value 2"

(2) Update data records:

sql= "Update data table set field name = field value where Condition expression"

Sql= Update data Table set field 1= value 1, field 2= value 2 ... field n= value n Where Condition expression "

(3) Delete data records:

Sql= "Delete from data table where condition expression"

Sql= "Delete from data table" (delete all records from datasheet)

(4) Adding data records:

sql= INSERT into Data table (field 1, Field 2, Field 3 ...) valuess (value 1, value 2, value 3 ...)

Sql= INSERT INTO Target datasheet SELECT * from source datasheet (add a record from the source datasheet to the destination datasheet)

(5) Data record statistic function:

AVG (field name) to derive a table column average
Count (*| field name) statistics on the number of data rows or the number of data rows that have a value for a column
Max (field name) gets the maximum value of a table column
Min (field name) gets the smallest value of a table column
sum (field name) adds the value of the data bar

How to reference the above function:

Sql= "Select sum (field name) as Alias from data table where condition expression"
Set Rs=conn.excute (SQL)

Using RS ("Alias") to obtain the value of the unified, other functions using the same.

(5) The establishment and deletion of the data table:

CREATE Table datasheet Name (field 1 type 1 (length), Field 2 type 2 (length) ...)

Example: CREATE TABLE tab01 (name varchar, datetime default Now ())

drop table datasheet name (permanently deletes a data table)