Basic tutorial on SQL language (1)

Basic tutorial on SQL language (1)
The Database Query Language (SQL) is a standard language for relational databases and is supported by many types of databases. This article will take VB and Dao as an example to introduce
Basic SQL syntax and usage. This chapter describes the basic query statements in SQL with examples.
To better read this article, you need to have a basic understanding of the basic structure and terminology of the database, and it is best to have some experience in VB Database Programming.
Unless otherwise stated, the database used in this article is the biblio. mdb database attached to VB.

1. Select... from... where statement
The statement syntax is as follows:
Select [All | * | distinct column1, column2]
From Table1 [, Table2]
Where [condition1 | expression1] [and condition2 | rxpression2]
In the above syntax, the statements outside [] are mandatory, while the statements in [] are optional. For the |-separated operators, it indicates that the syntax must be separated from |
Select one operator. The following syntax is described above.
In the Select... from... where statement, select specifies the field to be retrieved, from specifies the table to be queried, and where specifies the conditions for selecting records,
In addition, the order by statement can be included to set sorting records. Syntax:
Order by column1 | integer [ASC | DESC]
Column1 can specify the sorting fields or use the index specified by integer to sort the fields. ASC is ascending and desc is descending.

Example 1: Find the title of all titles in the titles table that were published after December 1, 1990 and contain the beginner
Create a new project and add the DaO definition Library (click Project | references in the menu, and select Microsoft Dao 3.51 Object Library in the list
(Or a later version of 3.60, if installed), then add a ListBox control to the form. Add the following code to the form1 code window:
Private sub form_load ()
Dim rstemp as recordset
Dim dbtemp as database
Dim astr as string

Set dbtemp = dbengine (0). opendatabase ("E:/program files/Microsoft Visual Studio/vb98/Biblio. mdb ",_
Dbopensnapshot)
Astr = "select [title] from [titles] Where [year published]> 1990 "&_
"And title like '* beginner *' order by title DESC"
Set rstemp = dbtemp. openrecordset (astr)
If rstemp. recordcount> 0 then
Rstemp. movefirst
Do until rstemp. EOF
List1.additem rstemp! [Title]
Rstemp. movenext
Loop
End if
End sub
When you run the program, list1 will list the titles of all the books that have been published since January 1, 1990 and contain the beginner, and sort the titles in descending order.
In the where statement of the preceding SELECT statement, we use the operator symbol> to compare the size and use like for matching. In the where statement
The following operators are used:
1. Comparison Operators
Including =, <>,>, <, >=, <=, and so on
2. logical operators
It contains the following categories:
Like, in, between, unique, is null, exists, all, and any
The like operator compares a value with other values by using wildcards. In the SQL query of VB, the wildcards that can be used are * and? * Represents
Meaning several characters, while? Indicates a character. For example, the like '* ininner * in the preceding statement indicates searching for all values containing the string beginner. No
When using * and? As a wildcard, It is a patent of VB. The real wildcards of SQL are % and _. % represents any number of characters ,_
For example, in VB, data environment uses % and _ as the query wildcard. In addition, Microsoft Jet Engine
The operator also provides other filtering options. For details, see the like operator entry in the msdn index.
The in operator is used to compare whether a value is equal to one of several values. For example, the following statement:
"Select [title], [year published] from [titles] Where [year published] In (1990,1991, 1995 )"
All books published in 1990, 1991, and 1995 are selected from the titles table.
The between operator is brave enough to search between two values. For example, where [year published] between 1989 and 1991 must specify the publication date
It is between 1989 and 1991.
Logical operators can be combined with the not operator to change the direction of query conditions. For example, where [year published] Not in (1990,1991, 1995)
3. Connector
The connectors in where include and or. When using and, all queries must be true, and the condition is true. When using or
If a query is true, the condition is true. As in the above example, only the title containing "Beginner" and records published after 1990 will be
Found.
When using the comparison operator, note that when operating the database engine in VB, you must use single or double quotation marks to enclose the string. For example
For example, where [name] = 'shanghai '. For data of the date type, use # to enclose the date, for example, where [birthday]> #19980-10-01 #
When using wildcards and like operators, you need to use quotation marks instead of #. For example, where [birthday] Like "1990-01 -*". Quotes tell database reference
The date is processed as a string, and the # Tells the database engine to process the date as a number. For the logical operators used by the where statement
To mention.

Example 2: how to add statistics to a data table
SQL provides a certain amount of statistics and computing functions. The statistical functions mainly include:
Count calculates the number of records in a field.
Sum calculates the sum of all values in the field
Max obtains the maximum value of all values in the field.
Min to obtain the minimum values of all values in the field
AVG calculates the average value of all values in a field.
There are four types of operators: +,-, *, And. The usage of these computing and statistical functions is as follows:
Functionname fieldname as outfieldname
Functionname defines the function name, fieldname defines the field to be operated, and outfieldname defines the name of the field to save the output result. The following is an example:
Program, first create a database, save the database as C:/db4.mdb. Add a table named db1 to the database. The table structure is as follows:

Field name product number cost ex-factory price sales quantity
A00020 $89.95 $95.00 900
A00056 ￥54.00 ￥89.00 2400
A00021 $2,300.00 $2,896.00 600
A000016 ￥120.00 ￥209.00 1200
A00987 ￥907.00 ￥1,090.00 1000

The product number is text type, the cost and ex-factory price fields are currency type, and the sales quantity is long integer data. Save the table and add Dao definition to the VB project.
Library, add a ListBox control to form1, and add the following code in the form1 code window:
Private sub form_load ()
Dim rstemp as recordset
Dim dbtemp as database
Dim astr as string

Set dbtemp = dbengine (0). opendatabase ("C:/db4.mdb", dbopensnapshot)
Astr = "select db1. Product NO. (db1. ex-factory price-db1. cost)/db1. cost) as drate ,"&_
"(Db1. ex-factory price * db1. sales volume) as etotal from db1 order by db1. product no"

Set rstemp = dbtemp. openrecordset (astr)
If rstemp. recordcount> 0 then
Rstemp. movefirst
Do until rstemp. EOF
List1.additem rstemp! [Product no.] & "& rstemp! [Drate] & _
"" & Rstemp! [Etotal]
Rstemp. movenext
Loop
End if
End sub

In the preceding program segment, a field exists in the computation character operation table, and the result is output to the output field for saving.

2. Join... on... statement
The join... on... statement is a statement used to connect multiple tables in an SQL query. the syntax of this statement is:
From Table1 [inner | outer | left | right] Join Table2 on table1.field1 compopr table2.field2
Tabel1 indicates the table to be queried, tabel2 indicates the table connected to tabel1, field1, and field2 specify the connection field name,
Compopr specifies the relational comparison operator, which can be greater than, less than, equal to, or not equal.
Example 3: List all books and their authors
In Biblio, the book name is located in the title field of the titles table, while the author of the book is located in the autoor field of the authors table, and there is no
If there are related fields, we need to join the Title Author table in the database, use the au_id field of the table to associate with the au_id field in the authors table, and
The ISBN field is associated with the ISBN field in the titles table, for example:
Private sub form_load ()
Dim rstemp as recordset
Dim dbtemp as database
Dim astr as string

Set dbtemp = dbengine (0). opendatabase ("E:/program files/Microsoft Visual Studio/vb98/Biblio. mdb", dbopensnapshot)
Astr = "select authors. Author, titles. Title from "&_
"([Title Author] inner join titles on [Title Author]. ISBN = titles. ISBN )"&_
"Inner join authors on [Title Author]. au_id = authors. au_id "&_
"Where titles. Title like '* beginner *'"

Set rstemp = dbtemp. openrecordset (astr)
If rstemp. recordcount> 0 then
Rstemp. movefirst
Do until rstemp. EOF
List1.additem rstemp! [Title] & "& rstemp! [Author]
Rstemp. movenext
Loop
End if
End sub
In the preceding example, we use two inner jion pairs to connect the au_id field in the authors table and the ISBN field in the titles table to the Title Author
In the Title Author table, find the record containing the string "Beginner" in the title field and output the title field and author field.

Www.applevb.com