常用经典SQL语句大全完整新版-权威人士总结出的-详解+实例---共12页.pdf

Mssql access SQL DDL—(CREATE ALTER DROP DECLARE) DML—(SELECT DELETE UPDATE INSERT) DCL—(GRANT REVOKE COMMIT ROLLBACK) , 1 CREATE DATABASE database-name 2 drop database dbname 3sql server device USE master EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:\mssql7backup\MyNwind_1.dat’ BACKUP DATABASE pubs TO testBack 4 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) A create table tab_new like tab_old () B create table tab_new as select col1,col2… from tab_old definition only 5 drop table tabname 6 Alter table tabname add column col type DB2 varchar 7 Alter table tabname add primary key(col) Alter table tabname drop primary key(col) 8 create [unique] index idxname on tabname(col….) drop index idxname 9 create view viewname as select statement drop view viewname 10sql select * from table1 where insert into table1(field1,field2) values(value1,value2) delete from table1 where update table1 set field1=value1 where select * from table1 where field1 like ’%value1%’ like ! select * from table1 order by field1,field2 [desc] select count * as totalcount from table1 select sum(field1) as sumvalue from table1 select avg(field1) as avgvalue from table1 select max(field1) as maxvalue from table1 select min(field1) as minvalue from table1 11 A UNION UNION TABLE1 TABLE2 ALL UNION UNION ALL TABLE1 TABLE2 B EXCEPT EXCEPT TABLE1 TABLE2 ALL EXCEPT (EXCEPT ALL) C INTERSECT INTERSECT TABLE1 TABLE2 ALL INTERSECT (INTERSECT ALL) 12 Aleft 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.c B right outer join: () C full outer join sql 1(,a b) (