sql查询

创建数据库

create database database-name;

删除数据库

drop database dbname;

 

增加一个列：

alter table tablename add column columnname type;

创建视图：

create view viewname as select statement;

删除视图：

drop view viewname;

复制表：

select * into b from a where 1<>1（仅用于SQlServer）

 

常用：

• 选择：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='表名'

 

 

转自：https://blog.csdn.net/hundan_520520/article/details/53580289