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 CREATE DATABASE CREATE DATABASE database-name CREATE DATABASE database-name drop DATABASE dbname Device USE master EXEC SP_addumpDevice ‘disk’, ‘testBack’, ‘\ mSSql7backup \ myNwind_1.dat’ — BACKUP BACKUP DATABASE pubs TO testBack 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 B: create table tab_new as select COL1, COL2… Alter table tabname add column col type Alter table tabname add column col type Columns added cannot be deleted. The column type cannot be changed in DB2. The only change is to increase the length of the VARCHAR type. Alter table tabname add primary key(col) Alter table tabname drop primary key(col) Create [unique] index idxname on tabname(col… .). Drop index: drop index idxname Note: the index cannot be changed. Create view viewname as select statement drop view viewname Select * from table1 where table1(field1,field2) values(value1,value2) Delete from table1 where field1=value1 where field1=value1 Select * from table1 where field1 like ‘%value1%’ Select * from table1 order by field1,field2 [desc] select count * as totalcount from table1 Select avg(field1) as avgvalue from table1 select avg(field1) as avgvalue from table1 Select Max (field1) as maxValue from table1 select min(field1) as minvalue from table1 The UNION operator derives a result table by combining two other result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When ALL is used with UNION (i.e., UNION ALL), duplicate lines are not eliminated. In both cases, each row of 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, duplicate lines are not eliminated. C: INTERSECT operator The INTERSECT operator derives a result table by including only rows in TABLE1 and TABLE2 and eliminating all duplicate rows. Duplicate lines are not eliminated when ALL is used with INTERSECT (INTERSECT ALL). Note: Several query result lines using operands must be consistent. Select * from left outer JOIN (select * from left outer join (select * from left outer join (select * from left outer join)); SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.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: not only the matching rows of the symbolic join table, but also all the records in the two join tables. Select * from b where 1<>1 select * from b where 1<>1 select * from b where 2 <>1 select * from b where 1<>1 Insert into b(a, b, c) select d,e,f from b; select top 0 * into b from a; Insert into b(a, b, c) select D,e,f from B in ‘db’ where… insert into B (a, b, c) select D,e,f from B in ‘DB’ The from b “in” & Server. MapPath (“. “&” \ data. The MDB “&” ‘where… Select a,b,c from a where a IN (select d from b); Select a,b,c from a where a IN (1,2,3) According to the article, submit and reply the last time the select a.t itle, a.u sername, b.a dddate from table a, (select Max (adddate) adddate from table where Table. title= a.table) b 6, 解 决 : select * from table where table.title= a.table) b 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 a ) select * from (SELECT a,b,c FROM a) T where t.a > 1; 8. Description: Select * from table1 where time between time1 and time2 select a,b,c, From table1 where a not between 1 and 2 Select * from table1 where a [not] in (‘ 表 1 ‘, ‘表 2’, ‘表 4’, ‘表 6’) Two associative tables, Delete from table1 where not exists (select * from table2 where table1.field1=table2.field1) 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 in advance to remind SQL: Select * from datediff(‘ minute ‘,f start time, getDate ())>5 Select top 10 B.* from (select top 20 from 表名 order by desc) a,表名 B where b. Primary key field = a. primary key field order by a. sort field 14, description: 10 * 10 records the select top before form table1 15 where range, specification: Select all information corresponding to a’s largest record in each set of data with the same B value (similar usage can be used for forum monthly leaderboards, monthly best-selling product analysis, ranking by subject scores, etc.). Select a,b,c from tablename ta where a=(select Max (a) from tablename TB where tb.b=ta.b) Include all rows in TableA but not in TableB and TableC and derive a result table (select a from TableA) except (select a from TableB) except by eliminating all duplicate rows 17, 表 示 : select top 10 * from tablename order by newid() Select newid() from newid() Delete from tablename where id not in (select Max (id) from tablename group by COL1, COL2…) Select name from sysobjects where type= ‘U’ Select name from syscolumns where id=object_id(‘ TableName ‘) List the type, vender, PCS fields, in order of type fields, case can be easily realized multiple selection, 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 Type Vender PCS A 1 PC A 1 CD B 2 CD A 2 phone B 3 phone C 3

Alter TABLE table1 TRUNCATE TABLE table1 Select top 5 * from (select top 15 * from table order by id ASC) table_ alias order by ID desc Random selection of database records (using the Randomize function, implemented in SQL statements) can do the same for data stored in a database, but they may be too slow. You can’t ask ASP to “find a random number” and print it out. In fact, a common solution is to create a loop like this: Randomize RNumber = Int(Rnd499) +1 While Not objrec.eof If objRec(” ID “) = RNumber THEN… Here is the execution script… End if objrec. MoveNext Wend this is easy to understand. First, you pick a random number in the range 1 to 500 (assuming 500 is the total number of records in the database). You then iterate over each record to test the value of the ID and check that it matches the RNumber. If the condition is met, the piece of code starting with the THEN keyword is executed. If your RNumber is 495, it will take a long time to loop through the database. While 500 May seem large, it is still a small database compared to more robust enterprise solutions, which typically contain thousands of records in a single database. Isn’t that the time to die? With SQL, you can quickly find the exact record and open a recordset containing only that record, as shown below: Randomize RNumber = Int(Rnd499) + 1 SQL = “SELECT * FROM Customers WHERE ID = ” & RNumber set objRec = Objconn.execute (SQL) Response.WriteRNumber & “=” & objRec(” ID “) & “” & objRec(” c_email”) You just need to check for matches. As long as you are satisfied with how the above code works, you can manipulate the “random” recording on demand. The Recordset contains nothing else, so you can quickly find the record that you need so that the processing time is greatly reduced. Now that you’re determined to squeeze the last drop out of the Random function, you might want to pull out more than one Random record at a time, or you might want to use records within a certain Random range. Extending the standard Random example above allows SQL to handle both cases. To retrieve several randomly selected records and store them in the same recordset, you can store three random numbers and query the database for records that match those 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 per page load), you can use BETWEEN or a mathematical equation to select the first record and an appropriate number of incremental records. This can be done in several ways, but the SELECT statement shows only one possibility (where the ID is an automatically generated number) : SQL = “SELECT * FROM Customers WHERE ID BETWEEN “&rnumber &” AND “&rnumber &” + 9 “SQL = “SELECT * FROM Customers WHERE ID BETWEEN” &rnumber & “AND” &rnumber & “+ 9” Read several records randomly and test 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 Rnd(id) Sql server: SELECT top N * From table name ORDER BY newid() mysql SELECT * From table name ORDER By rand() Limit n Access (rand() Limit n Access (rand() Limit n Access)) Now write down for later check grammar select table1. The fd1 and fd2, table2. Table1, fd2 From table1 left join table2 on table1. The fd1 and table2. Fd1 where… Use SQL statements with… 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)+’… ‘, the field) FROM tablename; Conn.Execute Indicates the Execute method. This method is used to Execute SQL statements. Depending on whether the recordset is returned after the SQL statement is executed, this method can be used in the following two formats: 1. When an SQL query statement is executed, the recordset resulting from the query is returned. Set object variable name = connection object. Execute(SQL Query Language) After the Execute method is invoked, a recordset object is automatically created and the query result is stored in the recordset object. You can use the Set method to assign the recordset to a specified object for storage. The object variable represents the recordset object. 2. When SQL is executed in the operant language, there is no recordset return. This is used to connect objects. Execute “RecordAffected statement” [, RecordAffected][, Option] ·RecordAffected is optional. RecordAffected variables are placed at your disposal. By accessing this variable, you can know how many records the SQL statement operates 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, the execution is more efficient. ·BeginTrans, RollbackTrans, and CommitTrans methods are methods provided by connection objects for transaction processing. BeginTrans is used to start something; RollbackTrans is used to roll back transactions; CommitTrans is used to commit all transaction results, that is, to confirm the processing of a transaction. A transaction can treat a set of operations as a whole. A transaction is successful only when all statements are successfully executed. If one of the statements fails, the entire processing fails and the state before the whole process is restored. BeginTrans and CommitTrans are used to mark the start and end of a transaction, and statements in between are processed as transactions. Whether the transaction is successful can be determined by connecting the Error set of the object. If the number of members of the Error set is not 0, it indicates that an Error has occurred and the transaction fails. Each Error object in the Error collection represents an Error message. DELETE statement DELETE statement: Use to create a DELETE query that removes records FROM one or more tables that are listed in the FROM clause if the clause meets the conditions in the WHERE clause, you can DELETE multiple records using DELETE. Syntax: DELETE [table.*] FROM table WHERE criteria Syntax: DELETE * FROM table WHERE criteria= ‘Query word’ Description: The table parameter is used to specify the name of the table FROM which records are to be deleted. The criteria parameter is an expression that specifies which records should be deleted. You can DROP an entire table from the database using the Execute method and a DROP statement. However, if you drop the table in this way, you lose the structure of the table. The difference is that when DELETE is used, only the data is deleted; The structure of the table and all attributes of the table, such as field attributes and indexes, remain. UPDATE about UPDATE, urgent ！！！！！！！！！！！ Table A (ID,FIRSTNAME,LASTNAME); table B(ID,LASTNAME); table A (ID,FIRSTNAME,LASTNAME) Now fill the corresponding value of the LASTNAME field in table B into the corresponding value of LASTNAME in table A. The ID fields in the two tables are related. Thanks in advance!! Update a set A.astname =(select B.astname from B where A.id = B.ID) Practicing SQL is the valuable wealth of database users. In this article, we’ll guide you through the four most basic data manipulation statements — the core functionality of SQL — by introducing comparison operators, selection assertions, and three-valued logic in turn. By the time you’re done, it’s clear that you’re starting to become 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 the actual data in the database. This work is handled by another class of SQL statements, data Manipulation Language (DML) statements. There are four basic DML operations in SQL: INSERT, SELECT, UPDATE and DELETE. Since these are the ones most SQL users use regularly, it’s worth explaining them here. In Figure 1 we have a table named EMPLOYEES. Each of these rows corresponds to a specific employee record. Familiarize yourself with this table, which we will use in a later example.

The Execute method executes a specified query, SQL statement, stored procedure, or provider-specific text. Execute Execute a query, statement, program, or technology that provides the detailed text of the object [provider].

The results are stored in a new Recordset object if it is a row-returning query. A closed Recordset object will be returned if it is not a row-returning query. If a row- RETURNING query is returned, the results will be stored in a new record object. If it is not a row-RETURNING query, it will return a closed record object.

Note: The returned Recordset is always a read-only, forward-only Recordset! Note: The returned Recordset is a read-only, forward-compatible Recordset.

Tip: To create a Recordset with more functionality, first create a Recordset object. Set the desired properties, And then use the Recordset object’s Open method to execute the query. Tip: When you first create a Recordset object, you need to create it as a more functional Recordset object. Set a desired property and use the Recordset object’s Open method to execute the query. Syntax for the row – returning row – returning line [return] Syntax. | Set objrs = objconn Execute (commandtext, ra, options) | Syntax for Non – row – returning non – row – returning [not return rows] Syntax | – Syntax for non – row – returning non – row – returning [not return rows] Syntax | | objconn. Execute Commandtext, ra, the options | | | Parameter parameters Description describes | | – commandtext

– | – Required. The SQL statement, stored procedure, or The provider – specific text to execute The necessary parameters. Specify the SQL statement to execute, Existing programs or the technical detailed text object (the provider) – | | ra | Optional. Sets how the provider should evaluate the commandtext parameter. Can be One or more CommandTypeEnum or ExecuteOptionEnum values. Default is adCmdUnspecified This parameter is optional. Sets how the technology provider object [provider] should evaluate the functionality of the CommandText property. It can be one or more CommandTypeEnum or ExecuteOptionEnum values. The default value is adCmdUnspecified | | Example case < %

SQL = “SELECT companyname FROM Customers”

Set rs=conn.Execute(sql)

% > |