创建数据库
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