SQL学习笔记（待续）

把自己所学的sql语句整理一下，顺便练练笔，好久时间没有写了。

 

 一，基础SQl

1，创建数据库

   CREATE DATABASE database_name;

 2,删除数据库

    DROP DATABASE database_name;

 

3，备份数据库

use master
go
exec sp_addumpdevice 'disk','testbackup','e:/dp4/dp4.bak'
exec sp_dropdevice 'testbackup'

backup database dp4 to testbackup

 

4,附加数据库

exec sp_attach_dp @dpname=N'fileName’

@FileName1 =N‘d:/dp5.mdf’

@FileName2 = N'd/dp5_log.ldf'

exec sp_dpoption 'filesystem', 'readonly', 'false'

 

5,还原数据库

 

restore filelistonly from disk = 'd:/dp5_dp'
use master
--记得改数据库名
restore database foodstar from disk ='d:/dp5_dp'
with replace , move 'dp5_data'to 'd:/dp5_dp/foodstar.mdf',
 replace , move 'dp5_log' to 'd:/dp5_dp/foodstar_log.ldf'

 

4 创建新表

create table tab_new (col1 type1 [not null] [primary key],col2 type2[not null],...)

--根据已有的表创建新表--

A：create table tab_new like tab_old(使用旧表创建新表)；

B：create table tab_new  as select col1,col2....from tab_old definition only

 

5 删除表

。。

6 增加一列

alter table tabname add column identity(1,1)

 

 

 

 

----------------------------删除重复出勤数据-------------------------

delete  from DP_AttendanceEntries

where entryid in (   

select a.entryid from (select max(entryid) as entryid,mobile,typeid,attendance,jointime,count(*) as countd

from DP_AttendanceEntries

where jointime >= convert (varchar(10),getdate()-2,102) and corpid =10)a)

group by mobile,typid,attenddate,jointime

having count(*)>1)

 

--------------------- 删除重复销量-----------------------

while (

            (select max(a.countd) from (

                select max(pdid) as pdid,mobile,code,distribution,recvtime,count(*) as countd

                from DP_DistributionEntries

                where recvtime >=convert(varchar(7),getdate(),102)+'01'

                and corpid = @corpid

                group by mobile,code,distribution,recvtime

                having count(*)>0

                )a)>1

)begin