sql常用基本语句

• 选择：select * from table1 where 范围

• 插入：insert into table1(field1,field2) value(value1,value2);

• 更新：update table1 set field1=value1 where 范围

• 查找：select * from table1 where field1 like ‘%value%’ –like语句的模糊查询

• 排序：select * from table1 order by field1,field [desc/asc]

• 总数：select count(*) as tatolcount from table1

• 求和：select sum(field1) as sumvalue from table1

• 最大：select max(field1) as maxvalue from table1

• 最小：select min(field1) as minvalue from table1

修改数据库名称: sp_renamedb 'old_name','new_name';

子查询：select a,b,c from a where a IN(select d from b)

between和not between 的用法：select * from table1 where time between time1 and time2-----------select a,b,c from table1 where a not between 数值1 and 数值2

找出所有的表：select name from sysobjects where type='U' //U代表用户

表的所有列名：select name from syscolumns where id=object_id('tablename');

分离数据库：sp_detach_db;

附加数据库：sp_attach_db 后接表名（附加需要完整的路径名）

技巧：

"where 1=1" 是表示选择全部，“where 1=2”全部不选

按笔画数排序：select * from TableName Order by by CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多

查看当前数据库中所有存储过程：select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

查询一个表的字段和数据类型：select column_name,data_type from information_schema.columns where table_name='表名'