Mssql statement following statement in part, can not be used in access.
SQL Category:
DDL-Data Definition Language (CREATE, ALTER, DROP, DECLARE)
DML-Data Manipulation Language (SELECT, DELETE, UPDATE, INSERT)
DCL-Data Control Language (GRANT, REVOKE, COMMIT, ROLLBACK)
First, a brief description based on the statement:
1, Description: Create a database
CREATE DATABASE database-name
2, Description: delete the database
drop database dbname
3, Description: backup sql server
--- Create a backup of the data device
USE master
EXEC sp_addumpdevice disk, testBack, c: mssql7backupMyNwind_1.dat
--- Start Backup
BACKUP DATABASE pubs TO testBack
4 Note: Create new table
create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null ],..)
According to the existing table to create a new table:
A: create table tab_new like tab_old (create a new table using old table)
B: create table tab_new as select col1, col2 ... from tab_old definition only
5, Description:
Delete the new table: drop table tabname
6 Description:
Add a column: Alter table tabname add column col type
Note: The column will not be removed to increase. After the DB2 data types in the list with no change, the only change is to increase the length of varchar type.
7, Description:
Add the primary key: Alter table tabname add primary key (col)
Description:
Remove the primary key: Alter table tabname drop primary key (col)
8, Description:
Create index: create [unique] index idxname on tabname (col ....)
Remove the index: drop index idxname
Note: The index is not changed, want to change to remove the re-construction.
9, Description:
Create view: create view viewname as select statement
Remove view: drop view viewname
10 Note: a few simple basic sql statement
Select: select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table1 where scope
Insert: insert into table1 (field1, field2) values (value1, value2)
Delete: delete from table1 where scope
Update: update table1 set field1 = value1 where the scope of
Find: select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table1 where field1 like '% value1%' --- like the syntax is very subtle, to find information!
Sort: select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table1 order by field1, field2 [desc]
Total: select count backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp as totalcount from table1
Sum: select sum (field1) as sumvalue from table1
Average: select avg (field1) as avgvalue from table1
Maximum: select max (field1) as maxvalue from table1
Minimum: select min (field1) as minvalue from table1
11, Description: several high-level query term operation
A: UNION operator
UNION operator results through a combination of the two other tables (for example, TABLE1 and TABLE2) and eliminate any duplicate rows in the table derives a result table. When the UNION ALL when used in conjunction with (or UNION ALL), not eliminate duplicate rows. In both cases, derived not from each row of the table is from TABLE1 TABLE2.
B: EXCEPT Operator
EXCEPT operator by including all but not in TABLE2 TABLE1 in the line and remove all duplicate lines and derive a result table. ALL EXCEPT when used in conjunction with the time (EXCEPT ALL), not eliminate duplicate rows.
C: INTERSECT operator
INTERSECT operator by only TABLE1 and TABLE2 in both the line and remove all duplicate lines and derive a result table. When ALL INTERSECT when used in conjunction with (INTERSECT ALL), not eliminate duplicate rows.
Note: Use the operation of several query results word line must be the same.
12 Description: Use outer join
A, left outer join:
Left outer join (left link): The result set includes the connection table matches several rows, also left all the lines connecting the table.
SQL: select aa, ab, ac, bc, bd, bf from a LEFT OUT JOIN b ON aa = bc
B: right outer join:
Right outer join (right link): The result set includes both connection lines to connect the matching table, the table also includes the right to connect all the lines.
C: full outer join:
All external connections: not only match the symbolic link table rows, the table also includes two connecting all the records.
Secondly, we see some good sql statement
1, Description: Copy table (only copy the structure, the source table name: a New Table Name: b) (Access available)
Method 1: select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp into b from a where 1 <> 1
Method 2: select top 0 backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp into b from a
2 Note: Copy table (copy the data source table name: a target table name: b) (Access available)
insert into b (a, b, c) select d, e, f from b;
3, Description: cross-database copy between the tables (the specific data using an absolute path) (Access available)
insert into b (a, b, c) select d, e, f from b in 'specific database' where conditions
Examples: .. from b in "& Server.MapPath (".")&" data.mdb" & "where ..
4, Description: sub-query (table name 1: a table name 2: b)
select a, b, c from a where a IN (select d from b) or: select a, b, c from a where a IN (1,2,3)
5, Note: Display the article, the author and the final response time
select a.title, a.username, b.adddate from table a, (select max (adddate) adddate from table where table.title = a.title) b
6 Description: outer join query (table name 1: a table name 2: b)
select aa, ab, ac, bc, bd, bf from a LEFT OUT JOIN b ON aa = bc
7, Description: Online view query (table name 1: a)
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from (SELECT a, b, c FROM a) T where ta> 1;
8 shows that: between the use, between the data limits the scope of inquiry, including the boundary values, not between excluding
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table1 where time between time1 and time2
select a, b, c, from table1 where a not between Value 1 and Value 2
9, note: in the use
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table1 where a [not] in ('value 1', 'value 2', 'value of 4', 'value of 6')
10 Note: Two related tables, delete the main table, the table has no information that the Deputy
delete from table1 where not exists (select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table2 where table1.field1 = table2.field1)
11, Description: Four tables together into the problem:
select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from a left inner join b on aa = bb right inner join c on aa = cc inner join d on aa = dd where .....
12, Note: the schedule at least five minutes to remind
SQL: select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from schedule where datediff (minute, f start time, getdate ())> 5
13, Description: A database sql statement buttoned page
select top 10 b. * from (select top 20 primary key fields, sort fields from table name order by sort field desc) a, b where b. name of the table primary key field = a. primary key field, sort order by a. field
14, Description: 10 records
select top 10 backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp form table1 where scope
15, Description: Select the value in each group b the same data in a corresponding record of the largest of all information (like this forum for a monthly usage can be used to list, selling products of every month, according to subject performance ranking and so on.)
select a, b, c from tablename ta where a = (select max (a) from tablename tb where tb.b = ta.b)
16, Note: includes all the TableA in but not TableB and TableC the line and remove all duplicate lines and derive a result table
(Select a from tableA) except (select a from tableB) except (select a from tableC)
17, Note: Remove the 10 random data
select top 10 backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from tablename order by newid ()
18, Description: randomly selected records
select newid ()
19, Description: Delete duplicate records
Delete from tablename where id not in (select max (id) from tablename group by col1, col2 ,...)
20, Description: list of all database table names
select name from sysobjects where type = U
21, Description: list all table
select name from syscolumns where id = object_id (TableName)
22, Description: shows the type, vender, pcs field to field type arrangement, case can easily achieve multiple choice, similar to the select in the case.
select type, sum (case vender when A then pcs else 0 end), sum (case vender when C then pcs else 0 end), sum (case vender when B then pcs else 0 end) FROM tablename group by type
Display results:
type vender pcs
Computer A 1
Computer A 1
Disc B 2
A 2 CD
Mobile B 3
Mobile C 3
23, Description: Initialize the table table1
TRUNCATE TABLE table1
24, Description: Select the record from 10 to 15
select top 5 backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from (select top 15 backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table order by id asc ) table_ Alias order by id desc
Database records the method of random selection (using the Randomize function, achieved through the SQL statement)
On the data stored in the database, the random number feature can give the above effect, but they may slow a bit. You can not ask ASP "to find a random number" and then print them out. In fact common solution is to build the cycle as follows:
Randomize
RNumber = Int (Rnd * 499) +1
While Not objRec.EOF
If objRec ("ID") = RNumber THEN
... Here is the script ...
end if
objRec.MoveNext
Wend
It is easy to understand. First, you remove the 1 to 500 within the scope of a random number (assuming 500 is the total number of database records). Then you loop through each record to test the value of ID to check whether it matches RNumber. Meet the conditions, if the implementation of the THEN keyword for the beginning a code. If your RNumber equal to 495, then loop again to the database may take a long time. Although this number seems too big 500, but more stable than an enterprise solution that is a small database, and the latter is usually within a database that contains thousands of records. This time not on the dead?
Using SQL, you can quickly find accurate records and open a record contains only the recordset, as follows:
Randomize
RNumber = Int (Rnd * 499) + 1
SQL = "SELECT backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp FROM Customers WHERE ID =" & RNumber
set objRec = ObjConn.Execute (SQL)
Response.WriteRNumber & "=" & objRec ("ID") & "" & objRec ("c_email")
Do not write RNumber and ID, you only need to check the matching condition can be. As long as you are satisfied with the work of the above code, you can demand action from the "random" records. Recordset does not contain other elements, so you can quickly find the records you need so greatly reduce the processing time.
Talk about random numbers
Now you drained Random function is determined to the last drop of oil, then you may take out more than a random record or want to use the records within a certain random. Random example of the above criteria can be expanded about the two cases above, the SQL response.
To remove a few random selection of records and stored within the same recordset, you can store three random numbers, and then query the database were matched records of these numbers:
SQL = "SELECT backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp FROM Customers WHERE ID =" & RNumber & "OR ID =" & RNumber2 & "OR ID = "& RNumber3
If you want to elect 10 record (perhaps when each page load the list of 10 links), you can use BETWEEN or mathematical equation selected the first record and the appropriate number of incremental records. This operation can be accomplished in several ways, but the SELECT statement shows only a possible (where ID is an automatically generated number):
SQL = "SELECT backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp FROM Customers WHERE ID BETWEEN" & RNumber & "AND" & RNumber & "+ 9"
Note: The above code is not intended to check the implementation of the database have nine concurrent records.
Random reads some records, tested
Access syntax: SELECT top 10 backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp From table name ORDER BY Rnd (id)
Sql server: select top n backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from table name order by newid ()
mysql select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp From Table Name Order By rand () Limit n
Access the left to connect syntax (use the left to connect the recent development, Access to help nothing, online there is no Access the SQL instructions, only their own tests, and now record for later inspection)
Grammar select table1.fd1, table1, fd2, table2.fd2 From table1 left join table2 on table1.fd1, table2.fd1 where ...
Using SQL statements instead of long strings with ... show
Syntax:
SQL database: select case when len (field)> 10 then left (field, 10 )+... else field end as news_name, news_id from tablename
Access database: SELECT iif (len (field)> 2, left (field, 2 )+..., field) FROM tablename;
Conn.Execute Help
Execute method
The method used to execute SQL statements. SQL statement is executed according to whether to return record set, use the format of the method is divided into the following two:
1. Implementation of the SQL query, the query will return the resulting recordset. Usage is:
Set the object variable name = connection object. Execute ("SQL Query Language")
Execute method call, it will automatically create a record set object, and query results are stored in the object record through the Set method, the Recordset object is assigned to the specified save, after an object variable represents the Recordset object.
2. The operational implementation of SQL language, no records set to return. At this point usage is:
Connection object. Execute "SQL operational statement" [, RecordAffected] [, Option]
· RecordAffected is optional, this can be put out of a variable, SQL statement is executed, the effective number of records are automatically saved to the variable. By accessing the variable, you can know how many SQL statements team records the operation.
· Option option, the parameter value usually adCMDText, it used to tell ADO, should be after the Execute method of the first character interpreted as a command text. By specifying the parameters could make the implementation more efficient.
· BeginTrans, RollbackTrans, CommitTrans method
This is the connection object provides three methods for transaction processing methods. BeginTrans for the start of a thing; RollbackTrans used to roll back the transaction; CommitTrans submit all the transactions for the results, that confirm transactions.
Transaction can be a set of operations as a whole, all statements are successful only after the execution of the transaction be successful; If one has a statement fails, then the whole deal even if the failure to restore everywhere in the state before.
BeginTrans and CommitTrans for marking the begin and end, between these two statements, that is, as the transaction statements. To judge the success of the transaction is available through connection object Error collection to achieve, 若 Error number 不 collection of members 0, Yue Ming An error has occurred Transactions fail. Error Error for each collection object, representing an error message.