Shulou(Shulou.com)06/01 Report--

Today, I would like to share with you about the SQL sentence, the usage and syntax of T-SQL, as well as some examples. The purpose is to help you review the basic sentences of T-SQL so that they can be used more quickly in the project. Of course, it is more important for beginners to learn and summarize. I personally feel that this aspect of database is very important for programmers and must be mastered by JAVA programmers, DOTNET programmers and other programmers. In order to help you better review T-SQL, I use my spare time to sort out the sentences and syntax of T-SQL. I hope it works for all of you!

1. Description: create a database

CREATE DATABASE database-name

2. Description: delete the database

Drop database dbname

3. Description: backup sql server-create a device for backing up data

USE master EXEC sp_addumpdevice 'disk',' testBack', 'cbank Mssql7backupUniverse MyNwindshield 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 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 the new table

Drop table tabname

6. Description: add a column

Alter table tabname add column col type

Note: columns cannot be deleted after they are added. The data type cannot be changed after the column is added in DB2, and the only thing that can change is to increase the length of the varchar type. 7. Description: add primary key:

[html] view plain copy print?

Alter table tabname add primary key (col)

Description: delete the primary key:

Alter table tabname drop primary key (col)

8. Description: create an index:

Create [unique] index idxname on tabname (col … (.)

Delete the index:

[html] view plain copy print?

Drop index idxname

Note: the index is immutable and must be deleted and rebuilt if you want to change it. 9. Description: create a view:

Create view viewname as select statement

Delete the view:

Drop view viewname

10. Description: a few simple basic sql statement choices:

Select * from table1 where range

Insert:

Insert into table1 (field1,field2) values (value1,value2)

Delete:

Delete from table1 where range

Update:

Update table1 set field1=value1 where range

Find:

Select * from table1 where field1 like'% value1%'-like's syntax is very exquisite, look up 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

Minimum:

[html] view plain copy print?

Select min (field1) as minvalue from table1

Description: several advanced query operators A: UNION operator 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 lines are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2. B: the 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 lines are not eliminated. The C: INTERSECT operator INTERSECT operator derives a result table by including only the rows in both TABLE1 and TABLE2 and eliminating all duplicate rows. When ALL is used with INTERSECT (INTERSECT ALL), duplicate lines are not eliminated. Note: several query result rows that use operands must be consistent. 12. Description: use outer join A, left outer join: left outer join (left join): the result set includes the matching rows of the join table, as well as all rows of the left join 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 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 join table, but also all records in the two join 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 11

Law II:

Select top 0 * into b from a

2. Description: copy table (copy data, source table name: a target table name: B) (available for Access)

Insert into b (a, b, c) select d from b

3. Description: copy of tables across databases (use absolute path for specific data) (Access is available)

Insert into b (a, b, c) select djiggy e from b in 'specific database' where condition

Example:.. from b in'"& Server.MapPath (". ") &" / data.mdb "&" 'where.. " 4. Description: sub-query (table name 1VLA, table name 2RB)

Select a dagger bjorc from a where an IN (select d from b) or: select a recital b recital c from a where an IN (1m 2pm 3)

5. Description: displays the article, author and last reply time

[html] view plain copy print?

Select a.title adddate from table where table.title=a.title a.username from table a b.adddate, (select max (title) adddate from table where table.title=a.title) b

6. Description: external join query (table name 1VR a, table name 2RB)

[html] view plain copy print?

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 1not between) 8. Description: the use of between. Boundary values are included when between limits the scope of query data, while not between does not include

Select * from table1 where time between time1 and time2 select a dint, from table1 where a not between number 1 and number 2

9. Description: how to use in

Select * from table1 where a [not] in ('value 1', value 2', 'value 4', 'value 6')

10. Description: two associated tables to delete information that has not been found in the secondary table in the primary table

Delete from table1 where not exists (select * from table2 where table1.field1=table2.field1)

11. Description: joint check of the four tables:

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. Note: schedule five minutes in advance to remind SQL:

Select * from schedule where datediff ('minute',f start time, getdate ()) > 5

13. Description: a sql statement completes database paging

Select top 10 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: the first 10 records

Select top 10 * form table1 where range

15. Description: select all the information corresponding to the largest record of an in each set of data with the same b value (similar usage can be used for forum monthly rankings, monthly best-selling product analysis, ranking by subject scores, etc.).

Select a dint bjorn c from tablename ta where a = (select max (a) from tablename tb where tb.b=ta.b)

Description: derives a result table by including all rows in TableA but not in TableB and TableC and eliminating all duplicate rows

(select a from tableA) except (select a from tableB) except (select a from tableC)

17. Description: 10 pieces of data are randomly selected

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,...)

Description: list all the table names in the database

Select name from sysobjects where type='U'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.