数据库基础命令

1、创建数据库

Create database database_name;

2、删除数据库

Drop database db_name;

3、sql server 备份

backup database db_name to disk=';d:\dbn.aa';//指定需要备份数据库的路径和文件名；

4、sql server恢复

use master

restore database dbname from disk=';d:\dbn.aa' with replace;

---kill掉数据库的某个连接

CREATE PROC KillSpid(@DBName varchar)
AS
  BEGIN
   DECLARE @SQL varchar
   DECLARE @SPID int
   SET @SQL='DECLARE CurrentID CURSOR FOR
   SELECT spid FROM sysprocesses WHERE dbid=db_id('''+@DBName+''') '
   FETCH NEXT FROM CurrentID INTO @SPID
   WHILE @@FETCH_STATUS <>-1
    BEGIN
         exec('KILL '+@SPID)
    FETCH NEXT FROM CurrentID INTO @SPID
    END
    CLOSE CurrentID
    DEALLOCATE CurrentID
END

5、创建新表

Create table tab_new like tab_old(用旧表创建新表)

Create table tab_new as select col1,col2...from tab_old definition only;

6、删除新表

Drop table table_name;

7、新增列

Alter table tab_name add column_name varchar(30);

8、修改列类型

Alter table tab_name alter old_column_name new_column_name varchar(30);

9、修改列名称

exec sp_rename 'tablename.column1','column2'//把1列名修改为列名2；

10、删除列

Alter table table_name drop column_name;

11、添加主键

Alter table table_name add primary key(id);//id添加为主键；

12、删除主键

Alter table table_name drop primary key(id)

13、创建索引

Alter table table_name add index index_name (column_list) ||

Create [unique] index index_name on table_name(column_list)

14、删除索引

Drop index index_name;

15、创建视图

Create view viewname as select statement(条件)

例如：Create view view_name as select id=1 from user;

16、删除视图

Drop view view_name;

17。查询语句：

>>select * from table where range（范围）;

>>insert into table1(field1,field2)values(value1,value2);插入值

>>delete from table1 where range(范围);删除

>>update table1 set field1=value1 where range（范围);更新值

>>select * from table1 where field like '%value1%'；//Like主要用于模糊查询，查询field字段中包含value1的内容；

select * from table where field like 'li*'//查询表中field字段中以li开头的内容；

select * from table where field like '%[0-9]%'查询表中field中包含有数字的内容；

select * from table where field like '%[!0-9]%'查询表中field中不包含有数字的内容；

select * from table where field like '%[a-z]%'查询表中field中包含有字母的内容；

>>select * from table1 order by field1 desc;//表中的field字段从大到小排序；

select * from table1 order by field1 asc;//表中的field字段从小到大排序；

>>select count as totalcount from table1//总数；

>>select sum(field1) as sumvalue from table//求和；

>>select avg(field1)as avgvalue from table//求平均数

>>select max(field1) as maxvalue from table//求最大值

>>select min(field1) as minvalue from table//求最小值

18、联合查询

>>select column_name(s) from table_name1 union select column_name(s) from table_name2；//union 查询结果是去重的；

>>select column_name(s) from table_name1 union all select column_name(s) from table_name2;//union all 查询全部的结果，未去除重复；

19、except 从左查询中返回右查询没有找到的所有非重复值；返回两个结果集的差

>>select * from table1 except select * from table2;

20、intersect返回两个结果集的交集

>>select * from table1 intersect select * from table2;

21、使用外连接

>>left outer join(左外联接，左连接)

select * from table1 left join table2 on table1.id=table.id;

//结果集包括left join中指定的左表的所有行，而不仅仅是联接所匹配的行，如果左表的某行在右边没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值（null）；

select A.name,A.age,B.name,B.score from table_A A left join table_B B on A.name=B.name;//(table_A/table_B是表名，A,B是别名)；

select A.name,A.age,B.name,B.score from table_A A left outer join table_B B on A.name=B.name;

>>right outer join(右外联接)//返回右表中的所有行，如果右表的某行在左表中没有匹配行，则将为左表返回空值；

select A.name,A.age,B.name,B.score from table_A A right join  table_B B on A.name=B.name;

select A.name,A.age,B.name,B.score from table_A A left outer join table_B B on A.name=B.name;

>>full outer join(完整外联接)

select A.name,A.age,B.name,B.score from table_A A full join table_B B on A.name=B.name;

select A.name,A.age,B.name,B.score from table_A A full outer join table_B B on A.name=B.name;

>>Cross join 交叉联接

select A.name,A.age,B.name,B.score from table_A A cross join table_B B;

>>自联接

select A.name,A.age,B.name,B.score from table_A A,table_B,B where A.name=B.name+1

22、inner join 内联接

select A.name,A.age,B.name,B.score from table_A A inner join table_B B on A.name=B.name;

select A.name,A.age,B.name,B.score from table_A A join table_B B on A.name=B.name;

存储过程：

杀掉连接的用户

CREATE PROCEDURE dbo.killrubbishprocess AS
DECLARE @spidnum int
DECLARE rubbish_CURSOR CURSOR FOR
select spid
from master.dbo.sysprocesses
where spid > 10 and spid <= 32767 and status='sleeping' and loginame='sa'
order by spid

OPEN rubbish_CURSOR

FETCH NEXT FROM rubbish_CURSOR
INTO @spidnum
select s_kill = "kill " + cast(@spidnum as char(5))
WHILE @@FETCH_STATUS=0
BEGIN
EXEC (s_kill)
FETCH NEXT FROM rubbish_CURSOR
INTO @spidnum

END

CLOSE rubbish_CURSOR
DEALLOCATE rubbish_CURSOR
GO