Mssql,access SQL Classic SQL statement Encyclopedia _ database Other

The following statements are part of a MSSQL statement and cannot 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, briefly introduce the underlying statement:
1, Description: Create a database
CREATE DATABASE Database-name
2, Note: Delete the database
Drop Database dbname
3, Description: Backup SQL Server
Device to create backup data---
Use master
EXEC sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat '
---start Backup
BACKUP DATABASE pubs to Testback
4, Description: Create a new table
CREATE TABLE TabName (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..)
To create a new table from an existing table:
A:create table tab_new like Tab_old (Create a new table using the old table)
B:create table tab_new as Select Col1,col2 ... from tab_old definition only
5. Description:
Delete new table: Drop table TabName
6. Description:
Add one column: Alter table tabname add column col type
Note: The column will not be deleted after it has increased. The data type can not be changed when the column in the DB2 is added, and the only change is the length of the varchar type.
7. Description:
Add primary key: Alter table TabName Add primary key (COL)
Description
Delete primary key: Alter table tabname drop primary key (COL)
8. Description:
Creating index: Create [unique] index idxname on tabname (col ...)
Deleting indexes: Drop INDEX Idxname
Note: The index is not to be changed and you want to change the rebuild must be deleted.
9. Description:
Creating a View: Create VIEW viewname AS SELECT statement
Delete view: Drop View ViewName
10, Description: A few simple basic SQL statements
Selection: SELECT * FROM table1 where
Inserting: INSERT INTO table1 (field1,field2) VALUES (value1,value2)
Delete: Delete from table1 where scope
Update: UPDATE table1 set field1=value1 where scope
Find: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, look for information!
Sort: SELECT * from table1 ordered by FIELD1,FIELD2 [DESC]
Total: SELECT Count * as TotalCount from table1
Sum: Select SUM (field1) as Sumvalue from table1
Average: Select AVG (field1) as Avgvalue from table1
Max: Select Max (field1) as MaxValue from table1
Min: select min (field1) as MinValue from table1
11, Description: Several advanced query operation words
A:union operator
The UNION operator derives a result table by combining the other two result tables (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.
B:except operator
The EXCEPT operator derives a result table by including all rows that are in TABLE1 but not in TABLE2, and all duplicate rows are eliminated. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.
C:intersect operator
The INTERSECT operator derives a result table by including only the rows in TABLE1 and TABLE2 and eliminates all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.
Note: Several query result rows that use an operator must be consistent.
12, Description: Use of external connections
A, LEFT OUTER join:
Left OUTER join (left connection): The result set includes a matching row for the join table and all rows of the left-attached table.
Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left-out JOIN b on a.a = B.C
B:right outer join:
Right outer join (right connection): The result set includes both matching connection rows for the join table and all rows of the right join table.
C:full outer join:
All outer joins include not only matching rows for symbolic join tables, but also all records in two connected tables.
Second, let's look at some good SQL statements
1, Description: Replication table (only copy structure, source table name: A new table name: B) (Access available)
Law one: SELECT * into B from a where 1 <>1
Method Two: Select top 0 * into B from a
2, Description: Copy table (copy data, source table name: A target table name: B) (Access available)
Insert into B (A, B, c) select d,e,f from B;
3, note: cross-database copy of the table (specific data using absolute path) (Access available)
Insert into B (A, B, c) Select d,e,f from B in ' specific database ' where condition
Example:.. From B in ' "&server.mappath (". ") & "\data.mdb" & "where.
4, Description: Subquery (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 where a (1,2,3)
5. Description: Display article, author and final reply 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: External connection query (table name 1:a table name 2:b)
Select A.a, A.B, A.C, B.C, B.D, B.f from-a left-out JOIN b on a.a = B.C
7, Description: Online view query (table name 1:a)
SELECT * FROM (select A,b,c from a) T where t.a > 1;
8, Description: Between use, between limit the query data range includes the boundary value, not between does not include
SELECT * FROM table1 where time between time1 and time2
Select A,b,c, from table1 where a is not between numeric 1 and value 2
9, Description: In the use of methods
SELECT * FROM table1 where a [isn't] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')
10, Description: Two related tables, delete the main table has not been in the secondary table of information
Delete from table1 where does exists (SELECT * from table2 where table1.field1=table2.field1)
11, note: Four table joint Inquiry question:
SELECT * from a left INNER join B on a.a=b.b right inner join C on A.A=C.C inner join D on A.A=D.D where ....
12. Description: Schedule five minutes advance reminder
Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5
13, Description: A SQL statement to handle the database paging
Select Top b.* from (select Top 20 primary key field, sort field from table name order by sort field desc) A, table name B where B. primary key field = A. primary key field order by a. Sort fields
14, Description: The first 10 records
Select top * form table1 where scope
15, Description: Select in each group B value of the same data in the corresponding a the largest record of all information (similar to the use can be used for the forum monthly rankings, monthly hot product analysis, ranking by subject results, etc.)
Select A,b,c from tablename ta where a= (select Max (a) from TableName TB where tb.b=ta.b)
16. Description: Include all rows in TableA but not in TableB and TableC and eliminate all duplicate rows and derive a result table
(select a from TableA) except (select a-TableB) except (select a from TableC)
17. Description: Randomly remove 10 data
Select Top * FROM tablename ORDER by NEWID ()
18, Description: Random selection of records
Select NEWID ()
19, Note: Delete duplicate records
Delete from TableName where ID. (SELECT max (ID) from tablename GROUP by Col1,col2,...)
20, Description: List all the table names in the database
Select name from sysobjects where type= ' U '
21, Description: List of all the
Select name from syscolumns where id=object_id (' tablename ')
22, Description: Listed type, Vender, PCs fields, sorted by Type field, case can easily implement multiple choices, similar to the case in select.
Select Type,sum (Case vender when ' A ' then pcs else 0), sum (case vender when ' C ' then pcs else 0), sum (case vender WH En ' B ' then PCs else 0 end] from tablename GROUP By type
Show Results:
Type Vender pcs
Computer A 1
Computer A 1
CD B 2
CD A 2
Mobile B 3
Mobile C 3
23, Description: Initialization of the table table1
TRUNCATE TABLE table1
24, Description: Select from 10 to 15 records
Select Top 5 * "from" (select top * to table ORDER by ID ASC) Table_ alias ORDER by id DESC
Methods of randomly selecting database records (using the Randomize function, implemented through SQL statements)
For data stored in the database, the random number feature gives the effect, but they may be too slow. You can't ask an ASP to "find a random number" and print it out. In fact, the common solution is to create a loop that looks like this:
Randomize
Rnumber = Int (rnd*499) +1
　
While not objrec.eof
If Objrec ("ID") = Rnumber THEN
... Here is the execution script ...
End If
Objrec.movenext
Wend
It's easy to understand. First, you take out a random number from 1 to 500 (assuming 500 is the total number of records in the database). Then, you traverse each record to test the ID's value and check to see if it matches the rnumber. The code that starts with the then keyword is executed if the condition is met. If your rnumber equals 495, then it takes a long time to cycle through the database. Although the 500 figure looks bigger, it's a small database compared to a more robust enterprise solution, which typically contains tens of thousands of records in a single database. Is this the right time to die?
With SQL, you can quickly find the exact record and open a recordset that contains only the record, as follows:
Randomize
Rnumber = Int (rnd*499) + 1
　
SQL = "SELECT * from Customers WHERE ID =" & Rnumber
　
Set Objrec = objConn.Execute (SQL)
Response.writernumber & "=" & Objrec ("ID") & "& Objrec (" C_email ")
You don't have to write Rnumber and IDs, you just need to check the match. As long as you are satisfied with the above code work, you can operate "random" records on demand. The recordset does not contain other content, so you will soon be able to find the records you need so that the processing time is greatly reduced.
Talk about random number again
Now that you are determined to squeeze the last drop of the random function, you may be able to take out more than one random record at a time or want to use a certain random range of records. By extending the standard random example above, you can use SQL to deal with both of these situations.
To take a few randomly selected records and store them in the same recordset, you can store three random numbers and then query the database for records that match these numbers:
SQL = "SELECT * from Customers WHERE id =" & rnumber & "or id =" & RNumber2 & "or id =" & rnumber 3
If you want to select 10 records (perhaps a list of 10 links each time the page is loaded), you can use between or mathematical equations to select the first record and the appropriate number of incremental records. This can be done in several ways, but the SELECT statement only shows one possibility (where the ID is an automatically generated number):
SQL = "SELECT * from Customers WHERE ID BETWEEN" & Rnumber & "and" & Rnumber & "+ 9"
Note: The above code is not intended to check whether there are 9 concurrent records in the database.
Randomly read several records, tested
Access syntax: SELECT top * FROM table name ORDER by Rnd (ID)
SQL server:select Top n * FROM table name ORDER by NEWID ()
MySQL select * FROM table name ORDER by rand () Limit n
Access left connection syntax (recently developed to use left-side connection, Access Help nothing, no access to the Internet SQL instructions, only the test itself, now write down for later)
Syntax select TABLE1.FD1,TABLE1,FD2,TABLE2.FD2 from table1 left join table2 on TABLE1.FD1,TABLE2.FD1 where ...
Use SQL statements with ... Instead of too long string display
Grammar:
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 (>2,left (field,2) + ' ... ', field) from TableName;
Conn.execute description
Execute method
This method is used to execute the SQL statement. The format of the method is divided into the following two categories, based on whether the recordset is returned after execution of the SQL statement:
1. When the SQL query statement is executed, the recordset that the query gets is returned. Usage is:
Set object Variable name = Connection object. Execute (SQL Query Language)
After the Execute method call, the Recordset object is created automatically, and the query results are stored in the Record object, and the recordset is assigned to the specified object by the set method, and then the object variable represents the Recordset object.
2. When executing an operational language for SQL, there is no return of recordsets. At this point the usage is:
The Connection object. Execute "SQL Operational Statement" [, recordaffected][, Option]
· Recordaffected is optional, this can place a variable, after the execution of the SQL statement, the number of records in effect is automatically saved to the variable. By accessing the variable, you know how many records have been manipulated by the SQL statement team.
· option, which is usually adCmdText, is used to tell ADO that the first character after the Execute method should be interpreted as the command text. By specifying this parameter, you can make execution more efficient.
· BeginTrans, RollbackTrans, CommitTrans methods
These three methods are the methods that the connection object provides for transaction processing. BeginTrans is used to start a thing; RollbackTrans is used to roll back a transaction, and CommitTrans to commit all the transaction results, that is, to confirm the processing of a transaction.
A transaction can treat a set of actions as a whole, and only if all statements succeed, the transaction is successful, and if one of the statements fails, the entire process fails, and the status of the former is restored.
BeginTrans and CommitTrans are used to mark the start and end of a transaction, and the statement between the two is the statement that is handled as a transaction. To determine whether the transaction is successful, it can be realized by the error set of the connection object, if the number of members of the error set is not 0, then the error occurs and the transaction processing fails. Each of the error objects in the error collection represents an incorrect message.
SQL Statement Encyclopedia Essentials
2006/10/26 13:46
Delete statement
Delete statement: Used to create a delete query that deletes records from one or more tables listed in the FROM clause, and that clause satisfies the conditions in the WHERE clause, you can use Delete to delete multiple records.
Syntax: DELETE [table.*] from table WHERE criteria
Syntax: DELETE * FROM table WHERE criteria= ' query words '
Description: The table parameter is used to specify the name of the tables from which records are deleted.
The criteria argument is an expression that specifies which records should be deleted.
You can discard an entire table from the database using the Execute method and a drop statement. However, if you delete a table in this way, you lose the table's structure. The difference is that when you use Delete, only the data is deleted, and the structure of the table and all the properties of the table remain, such as field properties and indexes.

UPDATE
About update, urgent!!!!!!!!!!!
In the Oracle database
Table A (ID, Firstname,lastname)
Table B (Id,lastname)
The data in table A originally id,firstname two fields is complete
The data in table B that originally id,lastname two fields is complete
Now you want to fill the corresponding data of the LastName field in table B into the table a lastname the corresponding position. The ID fields in the two tables are interrelated.
Thank you first,!!!!.
Update a set a.lastname= (select B.lastname from B where a.id=b.id)

Master SQL four most basic data manipulation statements: insert,select,update and delete.
Mastering SQL is a valuable asset for database users. In this article, we will guide you through the core functions of the four most basic data manipulation statements-sql-to introduce the comparison operator, the selection assertion, and the three-valued logic in turn. After you have completed these studies, you are clearly beginning to be proficient in SQL.
Before we start, use the CREATE TABLE statement to make a table (as shown in Figure 1). DDL statements define database objects such as tables, columns, and vision. They do not process rows in a table because DDL statements do not handle the actual data in the database. The work is handled by another type of SQL statement-Data Manipulation Language (DML) statement.
There are four basic DML operations in SQL: Insert,select,update and delete. Since this is a frequent use of most SQL users, it is necessary to have a one by one description of them here. In Figure 1 we give a table named employees. Each of these lines corresponds to a specific employee record. Please familiarize yourself with this form, which we will use later in the example.