The five-flowers of SQL products have to live, perhaps you don't have to open your sleeves. But if you want to use ASP and SQL, you may be dizzy. MySQL, SQL Server and MSQL are excellent SQL tools, but unfortunately, in the ASP environment you can't use them to create a practical SQL statement. However, you can use the Access knowledge and the corresponding Access skills, plus our tips and techniques, I believe you can successfully add SQL in your ASP page.
SELECT statement
In the world of SQL, the most basic operation is the SELECT statement. When using SQL directly under the database tool, many people will be familiar with the following: SELECT WhatFROM Whichtablewhere Criteria
Executing the above statement will create a query that stores its results.
On the ASP page file, you can also adopt the above general grammar, but the situation is slightly different, when the ASP programming, the content of the SELECT statement will be assigned to a variable as a character: SQL = "SELECT What from Whichtable where criteria"
Ok, I understand the way the SQL "talk" under the ASP, so that you can make your needs, the traditional SQL query mode and conditional query can be used.
For example, you may wish to assume that there is a data table in your database, the name is Products, now you want to take out all the records in this table. Then you have written the following code: SQL = "SELECT * from Products"
The above code - the role of the SQL statement is to take all the data in the table - all records within the data table will be selected. However, if you just want to take a particular column from the table, such as p_name. Then you can't use * wildcards, you have to type the name of a particular column, the code is as follows: SQL = "SELECT P_NAME FROM PROMTS"
After performing the above query, the contents of the P_Name column will be selected.
2. Set the query condition with WHERE clause
Sometimes you take out all database records may just meet your requirements, but in most cases we usually just get some records. How do I design inquiry? Of course, it will be more brainstorming, let alone this article also surprised you to use the Robs of the Raushzi.
For example, if you only plan to take out the p_name record, and these records must be headed in letter W, then you will use the following WHERE clause: SQL = "SELECT P_NAME from Products where p_name like 'w%' "
The after the WHERE keyword is followed by the conditions used to filter data. With these conditions, it will only be queried with data that satisfy certain standards. In the above example, the results of the query will only get the name of the name in the W header.
In the above example, the meaning of the percent symbol (%) is that the query returns all W letters and back is the record entry for any data or no data. So, when executing the above query, West and Willow will be selected from the Products table and store it in the query.
Just as you can see, just carefully design the SELECT statement, you can limit the amount of information returned in the Recordset, and ponder how to meet your requirements.
These, but it is just that SQL use is just starting. To help you gradually master the complex SELECT statement usage, let's take a look at the key standard terms: compare operators, these stations are often used when building their SELECT strings to get specific data. When the WHERE clause basis starts to create a WHERE clause, the simplest way is to adopt standard comparison symbols, which are <, <=,>,> =, <> and =. Obviously, you will soon understand the meaning of the following code and the specific operation: SELECT * from products where p_price> = 199.95select * from products where p_price <> 19.95select * from products where p_version = '4'
Note: Here you will notice that the numbers in the last example are added with single quotes. The reason is that the '4' in this example is a text type rather than a digital type. Because you will put the SELECT statement in the quotation mark to assign it to the variable as a value, you can also use quotation marks in the statement.
The comparison operator comparison operator specifies the scope of the content from the table. You can use them to create filters to narrow the range of Recordset, which prompted to save only information you care about when you have given a given task.
3. Like, Not Like and Between
You have seen the LIKE usage in the example of remaining the W header record. The Like decision is a very useful symbol. However, in many cases it uses it to bring you too much data, so it is best to open your mind before you use it. Suppose you want to take a 5-digit SKU number, and the beginning is 1 end is 5, then you can use the underman (_) replace the% symbol: SQL = "SELECT * from Products where p_sku like '1___5'"
The underscription indicates any character. So in the case of entering "1 _ _ _ 5", your search is limited to the 5-digit range that meets the specific mode.
If you want to be against it, you have to find out the SKU entry that does not match the "1_ _ _ 5" mode. Then you only need to add NOT in front of the LIKE in the examples just now.
Between assumes that you want to take out some range of data, and you know the starting point and end point in advance, then you may wish to use BetWeen judgment. Let us now assume that you want to select the records between 1 and 10 in a given table. You can use Between: ... WHERE ID BETWEEN 1 and 10
Or you can also use a familiar mathematical judgment sentence: ... WHERE ID> = 1 and ID> = 10
4. Joint statement
We have relatively simple so far, and if you can reach the standard RECORDSET loop query, then these statements can also meet some more complex requirements. However, why bother to be on the basics of the shallow taste? You can add additional symbols, such as And, OR, and NOT to complete more powerful features.
The following SQL statement is case: SQL = "SELECT C_FIRSTNAME, C_LASTNAME, C_EMAIL from Customers Where C_Email ISNOT NULL AND C_PURCHASE = '1' or c_purchase = '2' and c_lastname like'a% '
With the SQL knowledge you have now, the above examples are not difficult to explain, but the above statement does not understand how to see how the conditional sentence is glued in a single SQL statement. When the multi-line statement does not understand the SQL statement, you may wish to break down the entire statement into multi-line code, and then gradually increase the components of the query statement on the basis of existing variables and exist in the same variable: sql = " SELECT c_firstname, c_lastname, c_emailaddress, c_phone "SQL = SQL &" FROM customers "SQL = SQL &" WHERE c_firstname LIKE 'a%' and c_emailaddress NOT NULL "SQL = SQL &" ORDER BY c_lastname, c_firstname "to the last one, SQL variable contains the following complete SELECT statement: "SELECT c_firstname, c_lastname, c_emailaddress, c_phone FROM customersWHERE c_firstname LIKE 'a%' and c_emailaddress NO NULL ORDER BY c_lastname, c_firstname"
After the whole sentence is broken down, it is obviously more read! When debugging, you may be more pleasant to more than a few characters to make the program better read. But you have to remember, you need to increase space before closing quotes or after opening quotes, so that you don't have a few words when you connect. 5. Start execution
After learning the construction and use of the SELECT statement, you should learn how to use it. Under the database tool you have, this may mean you have to press some buttons written by "execution". On the ASP page, you can perform SQL statements immediately or as a stored procedure call.
Once you have created a SQL statement, you have to try to access its query results. Obviously, the key here is ASP Recordset. When using non-SQL RecordSet, creating a Recordset code is usually as follows: DIM ObjRecset Objrec = Server.createObject ("AdoDb.Recordset") Objrec.Open "Customers", Objconn, 0, 1, 2
If you are more familiar with the ASP, you can not be strange to you, you should know "customers" means that you open the name of a data table in the database.
Open RecordSet To take advantage of your more familiar SQL skills, you need to adjust the most commonly used Recordset on regular ASP page: DIM objRecset Objrec = Server.createObject ("AdoDb.Recordset") Objrec.Open SQL, Objconn, 0, 1 , 2
The only modification here is in Objrec.Open, after which the variable containing the SQL statement is later replaced by the name of the data table to query.
One of this method is that you can specify a cursor type (as shown above 0, 1, 2).
Execute SQL You can perform a SQL statement with a compact line code to create a Recordset. The following is the syntax: DIM objRecset Objrec = Objconn.execute (SQL)
In the above example, the SQL you see is the variable you store your own SQL SELECT statement. The code line "run" SQL statement (or query the database), select the data and store the data in the Recordset, in the above example is the variable ObjRec. The main disadvantage of this method is that you can't choose the type of cursor you want to use. Instead, Recordset always opens forward to the cursor. Because of the cursor, you may intend to be familiar with two ways to create a Recordset. Direct execution query saves the time consumed by typing characters, but if you have the default cursor, it is possible to encounter a problem that you often have normal operation. No matter which method you specifically use, the biggest difference between the two is nothing more about code. Don't consider what fields do you have, what is your standard? Regardless of how you store data, use SQL RECORDSET in volume than the standard Recordset that is opened on the ASP, not to mention Easy. After all, by filtering data, you eliminate the time IF-THEN test and the possible loop.
Writing tests With SQL here there is a trick, many professional ASP programmers are used to "writing" their own SQL statements when testing the web page. Do this helps you debug code because you can see the string that passes to the server. And what you have to do is adding response.writeYourvariable to display the information on the screen. You should attach this information when you submit questions related to SQL to the ASP discussion group.
6. Storage inquiry
When your query is relatively simple, every time you start creating a SQL statement from scratch, it's different, but the complex queries are different, and you will have a lot of development errors from the head. Therefore, once you let SQL run smoothly, you'd better save them, call them again when needed. This, even if you are a simple query, you can use the query statement stored at any time.
Suppose you have a report to the team every week, pointing out the current business support issues, these data need to be selected from your database, and to select records according to the date, simultaneous sorting based on the support issues you used by your team . Once you have designed this inquiry, why have you repeated once a week? Don't create a query on your HTML page, you should use your database tool to create queries and save it.
Then you can use the ActiveCommand property to insert queries into your ASP page. The first one or two times, you may feel that there is no meaning, in fact, a few lines of code: set objsq = server.createObject ("adodb.command") objsq.activeconnection = "databasename"
Objsq.commandtext = "storedQueryName" objsq.commandtype = adcmdstoredproc
Set objrec = Objsq.execute
Note that adcmdStoredProc indicates that you have already included an adovbs.inc file on the page. This file defines an Access constant that you can access by name instead of numbers. Just need to contain the file on the page (), then you can use the adcmdstoredproc this name. This way, what is easier to understand when you see it again, what is the meaning of the above stored queries.
7. ORDER BY
Selecting the most fascinating thing to record from the Access database, how do they enter the database in the database in order. Even if you use Sort By in the Access environment to change the record view, the record order in the data table has not changed.
If you are using AspRecordset to write records on the web, you may know more painful things. But you may have to face this problem, because there is no simple and convenient solution. It is good to simplify this problem in ORDER BY. In order to sort your results, just add the order by the end of the SELECT statement, then specify the reference list you need to sort. So if you want to sort by the customer's last name, you can write the following query statement: SQL = "SELECT C_LASTNAME, C_Firstname, c_email from customers order by c_lastname"
In this way, as long as you build Recordset and start writing the result to the screen, you will see that the data is arranged in alphabetical order.
Multi-level sorting is actually not only in the SQL statement. In fact, in many cases, you may want to specify the data sorting of two to three depth depths. Suppose you have the following data sheets, as follows:
Previous single stage using ORDER BY sort data is extracted in the following order: Absurdly Assuredabsurd@assured.comAbsolutely Assuredabsolutely@assured.comCrazed Codercrazy@coder.netLoosely Fringeloose@fringe.toLunatic Fringelune@fringe.toHands Onhands@yes.org apparent ORDER By has a role. Under the actual table structure, ABSURDLY Assured is the last entry, but it is ranked at the top of the search result. The Hands ON record ranked last because O was in the alphabet in the above list. Obviously, Absolutely is preferably ranked before Absurdly in accordance with the alphabet. To do this, you need to take the Level 2 Order By Sort Standard, to be sorted with reference to the second column: SQL = "SELECT C_LASTNAME, C_FirstName, C_Email from Customers ORDER BYC_LASTNAME, C_Firstname" The result will first follow the c_lastname column and sort by c_firstname column . If your data table contains more records, carefully designed sorting will make the output result arrangement more reasonable. Initial use If you like the most programmers like yourself, you will be able to grasp the enthusiasm of the new technology. Why not transfer from the length of the ASP to try SQL encoding? Below we will explore the questions for ASP programming and how to use SQL statements in ASP.
11. Record statistics
Determine how many records in the database, or how many records have reached some standards, which are not difficult to do with ASP. If you use the correct cursor type, you can use the RecordCount property to get the number of records. Of course, you can also use the Recordset. However, there is a simpler way, this is a count (*) in your own SELECT statement, the code as follows: SQL = "SELECT Count (*) from customers" or sql = "select count (*) from customer WHERE C_LASTNAME LIKE 'A%' "example, the following code will select some records and the total number of these records: SQL =" SELECT C_FIRSTNAME, C_LASTNAME, COUNT (*) from customer's a% '", but you can't achieve Ourselves. The "count" function used here is actually a collection function, meaning only to return to a single line of information: answer your questions. For the first SELECT statement, the problem is "How many records in the client table?" The query returns a single value as a response, so it cannot be combined with your regular query. If you want to get other data, you need to use RecordCount. The set function includes AVG, Min, Max, and Sum, except "count".
12. Connection
Anyone who is familiar with SQL and relational databases meets a lot of connection types. The simplest saying that the connection (JOIN) combines the contents of the two tables into a virtual table or the RECORDSET. If the data table is valid, you may always select a specific information from a table to select the associated information from another table. This requires simple "Equijoin". In order to understand the actual connection operation, let us assume that a certain type of software is stored in a database. A table (Software) contains the name of the software product, the version of the software, and other relevant details:
Another table (Releases) stores information about software release history, including publishing date and publishing status, etc. (such as beta, current version, outdated, etc.):
A column is also included in the above table, and the content points to the ID number used in the software table. So, through this way of indexing software table, you know the release table
Software is Rome, equal to 2.
You use connection combination information so that you don't need to come back and forth between two tables. However, in addition to combining information, the relevant information can be combined by connection. In this way, just publish
Software_ID Match the ID in the software table, you put the matching information into a record.
code show as below:
SQL = "SELECT * from Software, Release Desofware.id = Releases.softwareID"
Carefully analyze the above statement, first notice that the two tables are listed.
Back behind. According to the connection used, you may also find the grammar to change (or change type), but the above grammar is the most basic, showing the joint selection of the data. here
The WHERE clause is used to compare a particular ID value. in
Software table, existence
ID column. same,
Releases table
Software_ID column. In order to explicit you
The value of the WHERE clause is compared, you use the table name as a prefix, and a little bit is added (.). The following is the result after the connection is selected:
Note: Carefully consider the columns to select the data carefully when creating a connection. The above code uses * Wildcards to make readers pay attention to other parts of the SELECT code line. However, as you can see from above, you can't elect the SoftwareID column because this column is not added as an added value as the Recordset section. Its role is to use for WHERE clauses.
