SQL语句对一些表与字段的基本操作及提高、技巧（国外英文资料）.doc

SQL语句对一些表与字段的基本操作及提高、技巧（国外英文资料） A basis, Note: create a database The CREATE DATABASE DATABASE - name Note: delete the database The drop database dbname 3, note: The backup of SQL server - create the device that backs up the data USE the master EXEC sp_addumpdevice disk, testBack, c: mssql7backupmynwind_1.dat - - start backing up BACKUP the DATABASE pubs TO testBack Explanation: create a new table Create table tabname (col1 type1 [not null], col2 type2 [not null],..) Create a new table based on the existing table: A: create table tab_new like tab_old (using the old table to create A new table) B: create table tab_new as select col1, col2... The from tab_old definition only Note: delete the new table Drop table tabname 6, note: Add a column: Alter table tabname add column col type Note: the column will not be deleted when added. The column and the data type in DB2 are not changed, and the only thing that can change is the length of the varchar type. 7, note: Add primary key: Alter table tabname add primary key (col) Explanation: delete the primary key: Alter table tabname drop primary key (col) 8, description: Create the index: create [unique] index idxname on tabname (col...) Delete the index: drop index idxname Note: indexes are immutable and want to change must be deleted. 9, description: Create the view: create view viewname as the select statement Delete view: drop view view name Note: a few simple basic SQL statements Select * from table1 where scope Insert into table1 (field1, field2) values (value1, value2) Delete: delete from table1 where scope Update: update table1 set field1 = value1 where scope Search: select * from table1 where field1 like % value1 % - like syntax is subtle, check the information! Select * from table1 order by field1, field2 [desc] Total: 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: