DDL DML DCL

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 underlying statement:

1. Description: Create DATABASE Database-name

2. Description: Delete database drop DB dbname

3. Description: Backup SQL Server---Create backup data for device use master EXEC sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat ' ---Start backing up backup DATABASE pubs to Testback

4. Description: Create a new Table CREATE table tabname (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..) creates a new table from an existing table: A:create table Ta B_new like Tab_old (create new table with 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 a column Alter table tabname add column col Type note: The column will not be deleted after it is added. DB2 the column plus the data type can not be changed, the only change is to increase 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: Create the index: [unique] index idxname on tabname (col ...) Delete index: DROP index idxname Note: The index is immutable and you must remove the rebuild if you want to change it. 9. Description: CREATE VIEW: Creation View viewname As SELECT statement Delete view: Drop View ViewName

10, Description: A few simple basic SQL statement selection: SELECT * FROM table1 the WHERE range insert: INSERT INTO table1 (field1,field2) VALUES (value1,value2) Remove: Delete From table1 where scope updated: Update table1 set field1=value1 where scope lookup: SELECT * FROM table1 where field1 like '%value1% '--- Like the syntax is very subtle, check the information! Sort: SELECT * FROM table1 ORDER by FIELD1,FIELD2 [desc] Total: SELECT Count * as TotalCount from table1 summation: select SUM (field1) as Sumvalue from table1 average: Select AVG (field1) as Avgvalue from table1 maximum: select Max (field1) as MaxValue from table1 min: Selec T min (field1) as MinValue from table1

11. Description: Several advanced query operators the a:union operator UNION operator derives a result table by combining the other two result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with the 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. The b:except operator EXCEPT operator derives a result table by including all rows in TABLE1 but not in TABLE2 and eliminating all duplicate rows. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated. The INTERSECT operator of the c:intersect operator derives a result table by including only rows in TABLE1 and TABLE2 and eliminating all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated. Note: Several query result rows that use an operation word must be consistent.

12. Description: Use outer connection A, left OUTER join: Outer join (left connection): The result set includes a matching row for the join table, and also includes all rows of the left join table. Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C b:right outer JOIN: Right outer join (right connection): Result set includes both matching join rows for join table , including all rows of the right join table. C:full outer join: Full outer joins: Includes not only the matching rows of the symbolic join table, but also all the records in the two join tables.

Next, let's look at some nice SQL statements

1. Description: Copy table (copy structure only, source table name: A new table name: B) (Access available) Law one: SELECT * into B from a where 1<>1 Law II: 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 of table across databases (exact 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: 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 (three-in-one)

5, Description: Display the article, the author and the 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: 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 usage, 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 value 1 and value 2

9, Description: In the method of use SELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

10, Description: Two related tables, delete the main table is already in the secondary table does not have the information delete from table1 where is NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1 )

11, Description: Four table joint search problem: 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 do 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 field

14. Description: Top 10 Records select top * form table1 where range

15, Description: Select the same data in each group B value corresponding to the largest record of all the information (similar to the use of this can be used for the forum monthly leaderboard, monthly hot product analysis, ranked by the subjects, and so on.) Select A,b,c from TableName ta where a= ( Select Max (a) from TableName TB where tb.b=ta.b)

16. Description: Includes all rows in TableA but not in TableB and TableC and eliminates all duplicate rows to derive a result table (select a from TableA) except (select a from TableB) except (s Elect a from TableC)

17, Description: Randomly remove 10 data select top * FROM tablename ORDER by NEWID ()

18, Description: Random selection record select NEWID ()

19. Description: Delete duplicate record 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 select name from syscolumns where id=object_id (' TableName ')

22, Description: List the type, Vender, PCs fields, arranged in the Type field, case can easily implement multiple choices, similar to case in select. Select Type,sum (case vender if ' A ' then the PCs else 0 end), sum (case vender if ' C ' then PCs else 0 end), sum (case vender WH En ' B ' then PCs else 0 end) from tablename group By type display result: type vender pcs pc a 1 pc a 1 Disc B 2 disc a 2 mobile B 3 Mobile phone C 3

23. Description: Initialize table table1 TRUNCATE tables table1

24. Description: Select records from 10 to 15 select Top 5 * FROM (select top [from table] ORDER by ID ASC) Table_ alias ORDER by id DESC

Random selection of database records (using the Randomize function, implemented by SQL statements) for data stored in the database, the random number characteristics can give the above effect, but they may be too slow. You can't ask the ASP to "find a random number" and print it out. In fact, a common solution is to create a loop like the following: Randomize Rnumber = Int (rnd*499) +1

While not objrec.eof If Objrec ("ID") = Rnumber then ... Here is the Execute script ...

DDL DML DCL