Intermediate Data Manipulation Language "Basic Microsoft Jet SQL for Access 2000" article tells us how to use SQL to retrieve and manage information stored in the database. The rear section of this article will discuss the Intermediate Data Manipulation Language (DML) statement, which will enable the user to better control information retrieval and processing. The predicate word refers to the clause of a SELECT statement, similar to the WHERE clause, but the predicate is defined before the list of writing columns. The predicate can further limit the record set extracted by the user, in some cases, filter out any possible repetition values. All keywords In the SQL statement, if no predicates are defined, the default all keyword will be used. It means that all records of the conditions set by the SQL statement will be extracted. Back to our invoice database, extract the domain from the customer table is as follows: Select * from TBLCustomers Note Although the all keywords here are not defined, it is default. We can also write this statement as follows: SELECT ALL * from the version of the future DISTINCT keyword is used to control the value of the value of the result set how to process, and the rows of the same value as the specified column will be filtered out. If more than the specified column is greater than one, the combination of all specified columns will be used as the filter condition. For example, if the user queries a different record in the Customers table, the returned value will be unique, any name of any repetitive last name will be as a result of the result set as its result. SELECT DISTINCT [LAST NAME] from TBLCUSTOMERS, especially not to note, the result set returned by the query using the DistINCT keyword cannot be updated, ie read-only. The DistINCTROW keyword DistINCTROW keyword is similar to the DistINCT keyword, but the former is based on the entire row rather than individual domains. He is only useful when processing multiple tables and is only useful when the user selects the data domain from a certain but not all table. If the user's query is based on a table, or if you want to select a data field from all tables, the DISTINCTROW keyword is essentially the same as the all keyword. For example, in our invoice database, each customer may not, there is one or more invoice records. Suppose we hope to find customers with more than one invoice, you can use the DistINCTROW keyword to choose. Select DistINCTROW [Last Name], [First Name] from tblcustomers inner Join Tblinvoiceson TBLCUSTOMERS.CUSTOMERID = TBLINVOICES.CUSTOMERID If we don't use the DistINCTROW keyword, you get the row of all the invoice records for each customer. (Here the Inner Join statement will explain in the back part). Top keyword TOP keywords are used to return some rows at the top or bottom of the data line specified by the ORDER BY clause. The ORDER BY clause is used to specify that these data columns are arranged in ascending or descending order. If there is a phase equivalent, the TOP keyword will return all values equal.
For example, we want to determine the three three invoice records in our invoice database, you can write SQL statements as follows: SELECT TOP 3 InvoiceDate, AmountFrom TBLINVOICESORDER BY AMOUNT DESC We can also use the Percent keyword and TOP keywords to return by Order BY clause, a certain proportion of a certain ratio in the top or bottom of the data line, as follows: Select Top 25 Percent InvoiceDate, AmountFrom TBLINVOICESORDER BY AMOUNT DESC Note If the user does not define the Order By clause, the TOP key will not Significance, returning is some rows of random sampling. To learn more about predicates, enter all, Distinct, etc. in the Office Assistant or in the tab of the Microsoft Access Help, the tabs, and click Find. SQL Expression A SQL expression is a string as part of the SQL statement and you can get a value. You can build your SQL expression any combination of operators, constants, text values, functions, domain names, control, and properties. The article "Microsoft Jet SQL for Access 2000 Advanced" describes how to use expressions in the WHERE clause to limit the SQL statement; and in this article, we will learn from SQL that can be used for expressions. Operator. The In Operation IN operation is used to determine if a value of an expression belongs to a value in a specified list. If this expression is equal to a value in the list, the return value of the IN operation is TRUE. And when not found, the IN operation returns the value of false. Let us assume that we want to find all members of the sales department living in Washington or Georgia. We can write a SQL statement with a long WHERE clause and use the AND logic operator, and use the IN operator to shorten the statement we write. Select * from TBLShippingwhere State In (WA, GA) By using the NOT logic operator, we can retrieve the anti-campaign of the in action, this statement will return all members who can't live in Washington. Select * from TBLshippingwhere State Not In (WA) BetWeen Operation BETWEEN operation is used to determine if the value of an expression is between a specific range. If this expression is between this particular range, including the range start and end, this BetWeen action returns True. If this expression is not worth this range, the BETWEEN operation returns value false. Suppose we want to find all the invoices between 50 cubes to 100 mate. We'd better use the BetWeen operation and keyword and keyword. Select * from TBLINVOICESWHERE AMOUNT BETWEEN 50 and 100 By using the NOT logic operator, we can retrieve the anti-campaign of BetWeen operation, find the number of invoices that are not in the scope. SELECT NOT BETWEEN 50 AND 100 Note This range can be set to the opposite order and still get the same result (Between 100 and 50), but many databases for ODBC require this range to follow the order from beginning to end . If you design your app to be compatible or upgraded to an ODBC database, you should always be used in accordance with the method from beginning to end. LIKE Actions LIKE Operation Operator is used to determine if The value of an expression component to this of a pattern is a full string or a part of a string containing one or more wildcards.
By using the LIKE action, you can find a domain in a result set and find all values that meet special styles. Select * from tblcustomerswhere [Last Name] Like Johnson In order to return all the names at the beginning of the letter J, we use an asterisk wildcard. Select * from tblcustomerswhere [Last Name] Like J * With the NOT logic operator, we can retrieve the anti-operation of the LIKE operation and filter out all Johnsons in the list. Select * from tblcustomerswhere [Last Name] NOT LIKE JOHNSON You can use a variety of wildcards in the Like action style, as shown in the following table: Wildcard Description * (asterisk) matches all characters and can be used anywhere in the structure string. % (Percent) criticized any character and can be used anywhere in the structure string. (Only for ADO and THE JET OLE DB Provider)? (Question) Matched any single character and can be used anywhere in the structure string. _ (Underline) Match any single character and can be used anywhere in the structure string. (Only for ADO and THE JET OLE DB Provider) # (Number Symbol) Match any single number and can be used anywhere in the structural string. [] (Square bracket) matches any single character included in square brackets and can be used anywhere in the structure string. ! (Exclamation number) Match any single character in the list of characters contained in square brackets. Matches Any Single Character NOT IN The List That Is Enclosed Wtem Brackets. - (Connecting Character) Matched any character in square brackets. Note: The "%" and "_" wildcards described above can only run through Jet OLE DB Provider or ADO. If you pass. The Access SQL View user interface runs them will get an empty result set. If you want more wildcard information, enter Wildcard Characters in the Answer Wizard flag of the Office Assistant or Microsoft Access Help Form, and click Search. Is NULL Operating Air value refers to no value or unknown value. The is NULL operator is used to determine if the value of an expression is equal to an null value. Select * from TBLINVOICESWHERE AMOUNT IS NULL can retrieve the reverse operation of the is NULL operator by adding a NOT logic operator. In this example, the SQL statement will remove all invoice records that contain missing or unknown values. Select * from TBLINVOICESWHERE AMOUNT IS NOT NULL SELECT INTO statement Select INTO statement, you can also understand that a form creates a query that can be used to create a new worksheet from one or more existing worksheets. The worksheet it creates can be based on any valid SELECT statement. SELECT INTO statements can be used to store records, create a backup form or create a new worksheet in an external database. When you create a new worksheet with the SELECT INTO statement, all new worksheets are inherited in the original worksheet. However, there is no other working table properties, such as primary keywords or indexes are created in the new worksheet. Once the new worksheet is created, you can of course use the ALTER TABLE statement to add these properties. If you want to create a new worksheet, you can use a SELECT INTO statement with the domain list with the columns you want to include and the name of your new worksheet, and provide data resources in the FROM clause.
Select * INTO TBLNEWCUSTOMERSFROM TBLCUSTOMERS In order to specify a determined domain for a new workpiece, use the domain name of the original worksheet in the domain name with an asterisk, and use the AS keyword to name the columns in the new worksheet. Select [First Name] & & & (Last Name] As FullNameInto TBLNewCustomerNameSfrom TBLCUSTOMERS If you want to create a new worksheet in an existing external database, you can use the in keyword. If the external database does not exist, the SELECT INTO statement will return an error message. Select * INTO TBLNEWCUSTOMERSIN C: /CUSTOMERS.MDBFROM TBLCUSTOMERS Subsessment subquery is in the SELECT statement inside another SELECT, SELECT INTO, INSETT INTO, DELETE, or UPDATE statement. It can help you further restrictions on the results of another result set. This is called embedding, and because a subquery is a SELECT statement, you can also embed a subquery into another subquery. When you use a subquery in a SQL statement, it can be used as a domain list, where clause, or part of the haVing clause. Here are three basic subquery forms, and each of them use different kinds of predicates. IN sub-query IN sub-query is used to retrieve a set of values, namely the value of a column recorded is included in another worksheet or a column in the query. It can only return a column from other worksheets, which is a restriction condition. If you return more than one column, you will generate an error. Using invoice database examples, we can write a SQL statement that returns all the list of customers with invoices. Select * from TBLCUSTOMERSWHERE CUSTOMERIDIN (SELECT CUSTOMERID FROM TBLINVOICES) By using the NOT logic operator, we can retrieve records with the in sub-query, so you can get a list of customers without any invoices. Select * from TBLCUSTOMERSWHERE CUSTOMERIDNOT IN (SELECT CUSTOMERID FROM TBLINVOICES) ANY / SOME / ALL Subridden ANY, SOME and ALL Sub Query Pretty words are used to compare multiple output records of records of primary queries and subquers. Any and Some predicates are synonyms and can be replaced. When you need to retrieve any recording that meets the comparison conditions in the primary query, you can use ANY or SOME predicates. The predicate should be in front of the brackets started at the beginning of the subquery. Select * from tblcustomerswhere customerid = Any (select customerid from tblinvoices) Note the same as the result set returned by the above SQL statement and the IN sub-query example. The difference from ANY and Some predicates is that they can be used as an operator other than (=), such as greater than (>) and less than (<). Select * from tblcustomerswhere Customerid> Any (Select Customerid from TBLINVOICES) When we want to retrieve all records that satisfy subquery comparison conditions in the primary query, use predicate all. Select * from TBLCUSTOMERSWHERE CUSTOMERID> All (Select Customerid from TBLINVOICES) EXISTS Subridden Exists predicate is a record of the subquery to check in a result set. In other words, if the child is not returned to any line, this is relatively false.
And if it returns a row or more, this is True. Select * from tblcustomers as awhere exists (Select * from tblinvoiceswhere a.customerid = tblinvoices.customerid) Note that in the previous SQL statement, the TBLCUSTOMERS worksheet uses an alias. This is why we can reference its reason in the later sub-queries. When a subquery is connected to a primary query in this format, it is called the relevant query. By using the NOT logic operator, we can retrieve the opposite records of the Exists subquery, so you can get all the result sets of customers without any invoices. SELECT * FROM tblCustomers AS AWHERE NOT EXISTS (SELECT * FROM tblInvoicesWHERE A.CustomerID = tblInvoices.CustomerID) If you want more information about subqueries, helping form in the Office Assistant or Microsoft Access Answer Wizard sign enter SQL subqueries And click Search. Connecting in a related database system like Access, you often need to pick information records from multiple worksheets. This can be implemented by using a SQL JOIN statement. The JOIN statement allows you to retrieve records from a worksheet that has already defined each other, and the relationship between the recording and worksheet is one-on-one, one more, or more. The internal connection inside is also understood as a peer-to-peer connection, which is the most general form of the connection used. This connection retrieves records from two or more worksheets by matching a domain value common in various worksheets. The domain you connect must have similar data types, but you cannot connect MOMO and oleObject data types. In order to create an Inner Join statement, use the Inner Join keyword in the SELECT statement from the FROM clause. Let us use Inner Join to build all the results set of the invoice, and bring the time and amount of those invoices. Select [Last Name], InvoiceDate, AmountFrom TBLCUSTOMERS Inner Join TBLINVOICESON TBLCUSTOMERS.CUSTOMERID = TBLINVOICES.CUSTOMERIDORDER BY INVOICEDATE Note The work table name is separated by the INNER JOIN keyword, and the associated comparison is behind the ON keyword. For related comparisons, you can also use the <,>, <=,> = or <> operator, and you can also use the BetWeen keyword. At the same time, pay attention to the Id domains used in the compare relationship, they are not the composition of the final result set. If you want further restrictions, SELECT statements we can use the WHERE clauses after comparison connection in the ON clause. In the example below, we narrow the results set by including the invoice after January 1, 1998. SELECT [Last Name], InvoiceDate, AmountFROM tblCustomers INNER JOIN tblInvoicesON tblCustomers.CustomerID = tblInvoices.CustomerIDWHERE tblInvoices.InvoiceDate> # 01/01/1998 # ORDER BY InvoiceDate want to connect multiple sheets in the case, you can embed INNER JOIN Clause. In this example, we will generate our result set on the basis of a Select statement in the past, but we will also add Inner Join to the TBLSHIPPING Works to make the results in the city and the country of each customer.
SELECT [Last Name], InvoiceDate, Amount, City, StateFROM (tblCustomers INNER JOIN tblInvoicesON tblCustomers.CustomerID = tblInvoices.CustomerID) INNER JOIN tblShippingON tblCustomers.CustomerID = tblShipping.CustomerIDORDER BY InvoiceDate first note is a JOIN clause included in parentheses Separate from logically and second JOIN clauses. The alias used in the FROM clause uses a second worksheet to connect a worksheet to itself. Let us assume that we want to find all customer records with the same name. We can implement it by creating an alias "A" for the second worksheet and find different records of our last name. SELECT tblCustomers. [Last Name], tblCustomers. [First Name] FROM tblCustomers INNER JOIN tblCustomers AS AON tblCustomers. [Last Name] = A. [Last Name] WHERE tblCustomers. [First Name] <> A. [First Name] ORDER By TBLCUSTOMERS. [Last Name] The external connection is used to record retrieval when the record is saved in a worksheet, even if there is no match in other worksheets. The Jet database engine supports two types of external connections. Consider two mutually similar worksheets, one on the left and the other on the right. Left external connections will select all rows and left works in the right worksheet, and all rows in the left work table, even if there is no match record in the right worksheet. The right outer connection is a simple reverse reverse of the left external connection; that is, all rows in the right worksheet will be saved. As an example, let us assume that we want to determine all the number of invoices of each customer, but if a customer has no invoice, we hope to display its information by the message "None". SELECT [Last Name] &, & [First Name] AS Name, IIF (Sum (Amount) IS NULL, NONE, Sum (Amount)) AS TotalFROM tblCustomers LEFT OUTER JOIN tblInvoicesON tblCustomers.CustomerID = tblInvoices.CustomerIDGROUP BY [Last Name] &, & [First name] There are still several problems in the previous SQL statement. The first is the use of the string connection operator "&", which allows you to connect two or more domains to form a string together. The second is an IMMEDIATE IF (IIF) statement, which checks if the merged string is empty. If it is empty, this statement will return message "none", and if the combination is not empty, the combined value will be returned. The last point is the external connection clause. Use the left external connection to save the left worksheet line to let us see all customers, including those who have no invoices in the account. In a multi-work table, external connections can be nested in an internal connection, but the internal connection cannot be nested in an external connection. Cartesian product often encounters one term that is often encountered when the connection is the product of Cartesia. The definition of Cartesian product is "completely merge all the lines of all forms." For example, if you don't have any constraints, you will complete a Cartesian product. Select * from tblcustomers, TBLINVoices is not a good thing, especially when you have a worksheet that you want to process. So you should avoid the product of Cartesia by constraining your connection.
