The following statements are Mssql statements and cannot be used in access.
SQL classification:
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 introduction to the basic statements:
1. Instructions: Create a database
CREATE DATABASE database-name
2. Description: Delete the database
drop database dbname
3. Description: Back up sql server
--- Create device for backup data
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_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],..)
Create a new table based on an existing table:
A: create table tab_new like tab_old (use the old table to create a new table)
B: create table tab_new as select col1,col2… from tab_old definition only
5. Description:
Drop a new table: drop table tabname
6. Description:
Add a column: Alter table tabname add column col type
Note: Once a column is added, it cannot be deleted. In DB2, the data type cannot be changed after the column is added. The only thing that can be changed is to increase the length of the varchar type.
7. Description:
Add primary key: Alter table tabname add primary key(col)
illustrate:
Delete primary key: Alter table tabname drop primary key(col)
8. Description:
Create an index: create [unique] index idxname on tabname(col….)
Delete index: drop index idxname
Note: The index cannot be changed. If you want to change it, you must delete it and rebuild it.
9. Description:
Create a view: create view viewname as select statement
Delete a view: drop view viewname
10. Description: Several simple basic SQL statement selections: select * from table1 where range insertion: insert into table1(field1,field2) values(value1,value2)
Delete: delete from table1 where range update: update table1 set field1=value1 where range search: select * from table1 where field1 like '%value1%' ---like's syntax is very sophisticated, check the information!
Sorting: select * from table1 order by field1,field2 [desc]
Total count: select count * 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 advanced query operators A: UNION operator The UNION operator derives a result table by combining two other 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, every row in the derived table comes from either TABLE1 or TABLE2.
B: EXCEPT operator The EXCEPT operator derives a result table by including all rows that are in TABLE1 but not in TABLE2 and eliminating all duplicate rows. 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 rows that are in both TABLE1 and TABLE2 and eliminating any duplicate rows. When ALL is used with INTERSECT (INTERSECT ALL), duplicate rows are not eliminated.
Note: Several query result rows using operator words must be consistent.
12. Description: Use outer join A, left outer join:
Left outer join (left join): The result set includes matching rows of the joined table and all rows of the left joined 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 join): The result set includes both the matching join rows of the join table and all rows of the right join table.
C: full outer join:
Full outer join: includes not only the matching rows of the symbolic connection table, but also all records in the two joined tables.
Secondly, let’s take a look at some good SQL statements 1. Description: Copy the table (only copy the structure, source table name: a, new table name: b) (Access available)
Method 1: select * into b from a where 1<>1
Method 2: 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. Description: Copy tables between databases (use absolute paths for specific data) (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 IN (1,2,3)
5. Description: Display the article, submitter and last 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: 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 * from (SELECT a,b,c FROM a) T where ta > 1;
8. Description: The usage of between, between limits the query data range and includes boundary values, not between does not include
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between value 1 and value 2
9. Description: How to use in
select * from table1 where a [not] in ('value1','value2','value4','value6')
10. Description: Two related tables, delete the information in the main table that is not in the secondary table
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11. Description: Four-table joint query problem:
select * from a left inner join b on aa=bb right inner join c on aa=cc inner join d on aa=dd where .....
12. Description: Schedule reminder five minutes in advance
SQL: select * from schedule where datediff('minute',f start time,getdate())>5
13. Description: One sql statement completes database paging
select top 10 b.* from (select top 20 primary key field, sorting field from table name order by sorting field desc) a, table name b where b. Primary key field = a. Primary key field order by a. Sorting field 14. Description: First 10 records
select top 10 * form table1 where range 15. Description: Select all the information of the record with the largest a in each group of data with the same b value (similar usage can be used for monthly forum rankings, monthly hot sales Product analysis, ranking by subject performance, 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 to derive a result table
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17. Description: Randomly take out 10 pieces of data
select top 10 * from tablename order by newid()
18. Description: Randomly select 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 all table names in the database
select name from sysobjects where type='U'
21. Description: List all the items in the table
select name from syscolumns where id=object_id('TableName')
22. Description: List the type, vendor, and pcs fields, arranged by the type field. Case can easily implement multiple selections, similar to the case in select.
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
Disc A 2
Mobile phone B 3
Mobile C 3
23. Description: Initialize table table1
TRUNCATE TABLE table1
24. Instructions: Select records from 10 to 15
select top 5 * from (select top 15 * from table order by id asc) table_alias order by id desc
Method of randomly selecting database records (using the Randomize function, implemented through SQL statements)
For data stored in a database, random number features can give the above effect, but they may be too slow. You can't ask ASP to "find a random number" and print it out. A common solution is actually to create a loop 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 in the range of 1 to 500 (assuming that 500 is the total number of records in the database). Then, you iterate through each record to test the value of the ID to see if it matches the RNumber. If the condition is met, the block of code starting with the THEN keyword will be executed. If your RNumber is equal to 495, it will take a long time to cycle through the database. While 500 may seem like a large number, it's still a small database compared to more robust enterprise solutions, which often contain thousands of records within a single database. Isn’t he dead now?
Using SQL, you can quickly find the exact record and open a recordset containing only that 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")
Instead of writing out the RNumber and ID, you just check for a match. As long as you are satisfied with the working of the above code, you can manipulate the "random" records as needed. Recordset contains no other content, so you can quickly find the records you need, which greatly reduces processing time.
Let’s talk about random numbers again. Now that you are determined to squeeze out the last drop of oil from the Random function, you may take out multiple random records at once or want to use records within a certain random range. Extending the above standard Random example, you can use SQL to deal with the above two situations.
To retrieve several randomly selected records and store them in the same recordset, you can store three random numbers and then query the database for records matching these numbers:
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & RNumber2 & " OR ID = " & RNumber3
If you want to select 10 records (perhaps a list of 10 links each time the page loads), you can use BETWEEN or a mathematical equation to select the first record and the appropriate number of incrementing records. This operation can be done in several ways, but the SELECT statement only shows one possibility (the ID here is an automatically generated number):
SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber & " AND " & RNumber & "+ 9"
Note: The purpose of executing the above code is not to check whether there are 9 concurrent records in the database.
Randomly read several records and tested
Access syntax: SELECT top 10 * 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 join syntax (recent development requires the use of left joins. There is nothing in the Access help. There is no SQL description of Access on the Internet. I can only test it by myself. I will write it down now for future reference.)
Syntax select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where ...
Use SQL statements to replace excessively long string display syntax with...:
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 Description Execute method This method is used to execute SQL statements. Depending on whether the record set is returned after the SQL statement is executed, the usage format of this method is divided into the following two types:
1. When a SQL query statement is executed, the record set obtained by the query will be returned. Usage is:
Set object variable name = connection object.Execute("SQL Query Language")
After the Execute method is called, a recordset object will be automatically created and the query results will be stored in the record object. Through the Set method, the recordset is assigned to the specified object for storage. In the future, the object variable will represent the recordset object.
2. When executing the operational language of SQL, no recordset is returned. The usage at this time is:
Connection object.Execute "SQL operational statement" [, RecordAffected][, Option]
·RecordAffected is optional. A variable can be placed here. After the SQL statement is executed, the number of effective records will be automatically saved in the variable. By accessing this variable, you can know how many records the SQL statement has operated on.
·Option optional, the value of this parameter is usually adCMDText, which is used to tell ADO that the first character after the Execute method should be interpreted as command text. By specifying this parameter, you can make execution more efficient.
·The three methods BeginTrans, RollbackTrans, and CommitTrans are methods provided by the connection object for transaction processing. BeginTrans is used to start a transaction; RollbackTrans is used to roll back the transaction; CommitTrans is used to submit all transaction processing results, that is, to confirm the processing of the transaction.
Transaction processing can treat a group of operations as a whole. The transaction processing will be considered successful only when all statements are successfully executed; if one statement fails to execute, the entire processing will fail and return to the previous state.
BeginTrans and CommitTrans are used to mark the beginning and end of a transaction. The statements between these two are used as transaction processing statements. Determining whether the transaction processing is successful can be achieved by connecting the Error collection of the object. If the number of members of the Error collection is not 0, it means that an error occurred and the transaction processing failed. Each Error object in the Error collection represents an error message.
SQL statement encyclopedia essentials
2006/10/26 13:46
DELETE statement
DELETE statement: used to create a delete query that can delete records from one or more tables listed in the FROM clause, and the 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 table from which records are deleted.
The criteria parameter is an expression used to specify which records should be deleted.
You can use the Execute method with a DROP statement to drop an entire table from the database. However, if you delete the table in this way, you will lose the structure of the table. The difference is that when DELETE is used, only the data will be deleted; the structure of the table and all attributes of the table are still retained, such as field attributes and indexes.
UPDATE
Regarding UPDATE, urgent! ! ! ! ! ! ! ! ! ! !
Table A (ID,FIRSTNAME,LASTNAME) in ORACLE database
Table B(ID,LASTNAME)
The data in the original ID and FIRSTNAME fields in table A are complete. The data in the original ID and LASTNAME fields in table B are complete. Now we need to fill in the corresponding data of the LASTNAME field in table B into the LASTNAME in table A. corresponding location. The ID fields in the two tables are related to each other.
Thanks in advance!!!!
update a set a.lastname=(select b.lastname from b where a.id=b.id)
Master the four most basic data operation statements of SQL: Insert, Select, Update and Delete.
Practicing SQL is a valuable asset for database users. In this article, we will guide you to master the four most basic data manipulation statements—the core functions of SQL—to introduce comparison operators, selection assertions, and three-valued logic in sequence. When you complete these studies, it is obvious that you have begun to be proficient in SQL.
Before we begin, use the CREATE TABLE statement to create a table (as shown in Figure 1). DDL statements define database objects such as tables, columns, and views. They do not process rows in the table because DDL statements do not process actual data in the database. These tasks are handled by another type of SQL statement—Data Manipulation Language (DML) statements.
There are four basic DML operations in SQL: INSERT, SELECT, UPDATE and DELETE. Since these are commonly used by most SQL users, it is necessary for us to explain them one by one here. In Figure 1 we give a table named EMPLOYEES. Each row corresponds to a specific employee record. Please familiarize yourself with this table, we will use it in the following examples