create database student

on--创建库的时候必须写

（

name=student,

filename='E:\database\student.mdf'--关于存放路径问题与本计算机的系统有关，最好创建在文件夹下

）

drop database student

use student

go

create table stuinfo

(

stuId int identity primary key,

stuName nvarchar(20) not null,

stuSex nvarchar(1) not null,

stuAge int not null

)

insert into --添加

--stuinfo 表格

--values(添加的内容)

--添加的内容规则：内容数量与表结构列名一致，顺序一致

--添加的内容：特别的地方、自动增长列除外

--insert into stuinfo values(添加内容)

insert into stuinfo values('sss','男',22)

insert into stuinfo

select 'aa','男','' union

select 'aa','男','' union

select 'aa','男','' union

select 'aa','男','' 

select * from stuinfo

delete stuinfo --删除表的所有数据

delete stuinfo where stuAge>18 --删除年龄大于18岁的学生

truncate table stuinfo --当数据库清空时，才有必要，把自动增长列归0

update stuinfo set  Stuage=15 where  Stuage>18 --将学生年龄大于18的改为15岁

select top 5 stuName as '前5名的学生' from stuinfo

select  top 50 percent * from stuinfo

select * from stuinfo stuID between 2 and 5

select * from stuinfo where stuName like '%李%'

 delete  [SS_OLMS_SZ].[dbo].[SZ_SCADAPoint] where 序号 is  NULL

INSERT INTO SS_MonitorStation(M_ID,M_Name,GISLongitude,GISLatitude,M_StationNo,M_Description,M_StationTypeID)

SELECT 序号,测点名称1,[北坐标（X）],[东坐标（Y）],数据,备注,TypeID FROM SZ_SCADAPoint

update a set a.TagName = b.TagName from b where a.DataID=b.DataID

declare @num int

 set @num =1

 while(@num<5)

 begin

 set @num = @num+1

  insert student (name,age,address) values('a',@num,'b')

 end

use student

GO
 declare @sql varchar(8000)
 while (select count(*) from sysobjects where type='U')>0
 begin SELECT @sql='drop table ' + name FROM sysobjects
    WHERE (type = 'U') ORDER BY 'drop table ' + name exec(@sql)
 end

select DATEPART(YEAR,Birthday),COUNT(*)FROM stuinfo  GROUP BY DATEPART(YEAR,Birthday)

sp_rename '表名.旧列名','新列名','column'

declare @i int

declare @s nvarchar(100)

set @i=129

while @i<143

begin

Set @s='drop table dbo.Initial_'+cast(@i as varchar)

print @s

exec(@s)

set @i=@i+1

end

select * into 新表 from 旧表