sqlServer2005的操作语句

 
/*
--sql server 2005 无法连接到数据库引擎--
确定服务器器名称、帐号、密码无误后：
1.先开启服务器：开始-运行-输入“net start mssqlserver”，关闭为“net stop mssqlserver”；
或者开始-  所有程序 - micorsoft  sql server 2005  -  配置工具 -  SQL Server 外围应用配置器-服务和连接的外围应用配置器 -  MSSQLSERVER  -  Database Engine  -  服务 -  启动类型 - 改为“自动”，然后确定。
2.Database Engine  的远程连接：开始-  所有程序 - micorsoft  sql server 2005  -  配置工具 -  SQL Server 外围应用配置器 -  服务和连接的外围应用配置器 -  MSSQLSERVER  -  Database Engine  -  远程连接 -  按钮“本地连接和远程连接” -   按钮“仅使用TCP/IP”   ，然后确定。
*/
-- 获取用户创建的对象信息--
SELECT [name],[id],crdate FROM sysobjects where xtype='V' 
/*  
xtype 的表示参数类型，通常包括如下这些  
C = CHECK 约束  
D = 默认值或 DEFAULT 约束  
F = FOREIGN KEY 约束  
L = 日志  
FN = 标量函数  
IF = 内嵌表函数  
P = 存储过程  
PK = PRIMARY KEY 约束（类型是 K）  
RF = 复制筛选存储过程  
S = 系统表  
TF = 表函数  
TR = 触发器  
U = 用户表  
UQ = UNIQUE 约束（类型是 K）  
V = 视图  
X = 扩展存储过程  
*/ 

--建库--
use master
go
if exists (select * from sys.databases where name = 'pubs')
drop database pubs
go
create database pubs
go
--建表--
if exists (select * from sys.objects where name = 'company')
drop table company
go
create table company
(
	id        int identity(2,2),--自动增长列(从2开始，每次增2个)
	names     varchar(10)      ,--人员姓名
    sex       char(1)          ,--性别
	age       int			   ,--年龄
	birthday  datetime         ,--生日
	explain   text             ,--个人说明
	email     varchar(40)      ,--邮箱
	telephone varchar(20)       --电话
)
go
--查询--
select * from company
go

--插入--
insert into company (names,sex,birthday,explain,email,telephone) values('apple','1','2000-01-04','sweet','13412345678@qq.com','13412345678');
insert into company (names,sex) values('apple','1');
insert into company (names,birthday) values('apple','2000/10/05');                                                    
---sql2008 r2中这样查年会有问题，年要插入四个数字
--insert into company (names,birthday) values('apple','20/10/06');
--insert into company (names,birthday) values('apple','20/10/06 10:56:30');
insert into company (names,birthday) values('apple','2222/10/06 10:56:30');
insert into company (names,birthday) values('二','1900/10/06 10:56:30');
insert into company (names,birthday) values('一','1800/07/21 10:56:30');
insert into company (names,age) values('六',21);
insert into company (names,age) values('apple2',22);
insert into company (names,age) values('apple3',23);
insert into company (names,age) values('apple4',24);
insert into company (names,age) values('apple5',25);
insert into company values('apple','1',23,'2000-01-01','sweet','13412345678@sina.com','13412345678');
insert into company values('apple','1',23,'2000-01-01','sweet','13412345678@sina.com','13419626253');
insert into company values('apple','1',23,'2000-01-01','beautiful','13412345678@sina.com','13412345678');
go
--初始化表--
truncate table company
go

--循环插入表数据--
declare @intName int
set @intName=1
while @intName<10
begin
	insert into company (names) values (@intName)
	set @intName=@intName+1
end
go

declare @remarkName varchar(10)
declare @intName int
set @remarkName='a'
set @intName=1
while @intName<10
begin
	insert into company (names) values (@intName)
	set @intName=@intName+@intName+1
end
go

--修改--
update company set names='apple' where id=1
update company set names='apple', sex='0' where id=1
update company set names='apple', sex='0',explain='strong' where id=1
update company set birthday='2000/1/1' where telephone='13512345678'                                                          
go
--删除--
delete from company where id=1
delete from company where id=2 and names='wendy'
delete from company where id in(0,3,4,5)--根据id批量删除                                                                        
delete from company where id >= 6 and  id <= 50
go
--查找--
select * from company where explain like '%beautiful%'
select * from company where explain like '%beautiful%' or telephone like '%253%'
go    
                                                                                                                            
--查询sex是空的数据--   
select * from company where sex is null

--查询sex不是空的数据--   
select * from company where sex is not null 
 go
--between的用法：between限制查询数据范围时包括了边界值，not between 不包括--
select * from company where birthday between '1900/10/06 10:56:30' and '2222/10/06 10:56:30'
select * from company where birthday not between '1900/10/06 10:56:30' and '2222/10/06 10:56:30'
go

--in的使用方法--
select * from company where names not in('apple');--查询names不是apple的所有数据
go

--按姓氏笔画排序--
select * from company order by names collate Chinese_prc_stroke_ci_as
go

--排序--
select * from company order by id asc --升序（不写的话默认为升序）
select * from company order by id desc--降序
go

--查询表中31到第40个记录--
select top 10 * from company where id not in (select top 5 id from company)
--一条sql搞定数据库分页--
select top 10 * from (select top 20 id ,names from company order by names desc) company
go

--查询最新的十条数据
select * from company ORDER BY createdTime DESC LIMIT 10 ;

--随机取出十条数据--
select top 10 * from company order by newId()
go
--随机数--
select newId()
go

--删除字段中字符个数为5的前五个字符--
select * from company
update company set names = substring(names, 6, len(names)) where len(names) = 5                                               
go
--删除重复names,age--
delete from company where id not in (select max(id) from company group by names,age)
go
--删除重复names--
delete from company where id not in (select max(id) from company group by names)
go
---删除重复的记录，保留一条记录！
delete a from  company a  where id > (select min(id) from company b where a.names=b.names) 
go

--判断列是否是自增列--
if 
 columnproperty(object_id('company'),'id','IsIdentity')=1 
print '自增列'
else 
print '不是自增列'
go

--判断表中是否存在索引
if exists(select * from sys.indexes where name=object_id('company') and name='id') 
print '存在' 
else 
print '不存在' 
go

--判断列是否存在,存在就删除-- 
if exists(select * from sys.columns where name='id') alter table company drop column id 
go

--添加一个增长列--
alter table companyadd id int identity(1,1)
go 

--修改某个表某列的类型-- 
alter table MD_Tank alter column EquipStatusId int null

--总数--
select count(names) as totalNames from companyselect count(*) totalNames from company where names='wendy'
go
--求和--
select sum(age) as 'sumAge' from company
go
--平均--
select avg(age) as 'avgAge' from company
go
--最大--
select max(age) from company
go
--最小--
select min(age) from company
go
--复制表：只复制表结构（源表：company，新表：newCompany）--
select * into newCompany from company where 1<>1
--不插入company表的数据
drop table newCompany
go
select * into newCompany from company where 1=1
--插入company表的数据
go
drop table newCompany
go
select top 0 * into newCompany from company
go
drop table newCompany
go
select top 2 * into newCompany from company
go
select * from newCompany
go
--两张关联表，删除主表中已经在副表中没有的数据--
delete from company where not exists (select * from newCompany where company.id=newCompany.id)
--两张关联表，删除主表中已经在副表中存在的数据--
delete from company where exists (select * from newCompany where company.id=newCompany.id)
go
select * into newCompanys from company where 1=1
go
--查询两张表里相同的数据--
Select company. * From newCompanys,company Where newCompany.id = company.id;
--根据条件查询两张表里相同的数据--
Select company.* From company Inner Join newCompany On company.id =newCompany.id where newCompany.id =1 ;
go
--包括所有在company中但不在newCompany中的行并消除所有重复行而派生出一个结果表--
(select names from company) except (select names from newCompany) 
go
--包括所有在company中但不在newCompany、newCompanys中的行并消除所有重复行而派生出一个结果表-- 
(select names from company) except (select names from newCompany) except (select names from newCompanys) 
go --数据库的变量定义和赋值-- 
declare @dt1 datetimedeclare @dt2 datetimeset @dt1='1900/10/06 10:56:30'set @dt2='2222/10/06 10:56:30' 
go 
--生日年份到今年相差100的数据-- 
select * from company where datediff(Year,birthday,getdate())>100 
go 
--今年的第一天-- 
select DATEADD(yy,DATEDIFF(yy,0,getdate()),0) as '今年的第一天' 
go 
--计算一个月的第一天-- 
select DATEADD(mm,DATEDIFF(mm,0,getdate()),0) 
--本周的星期一-- 
select DATEADD(wk,DATEDIFF(wk,0,getdate()),0) 
--在当前日期基础上添加两天-- 
select dateadd(dd,2,getdate()) 
/*DATEDIFF 语法简述： datediff(date-part,date-expression1,date-expression2) date-part :year | quarter | month | week | day | hour | minute | second | millisecond date-part 
指定要计算其间隔的日期部分。 
date-expression1 某一间隔的起始日期。 
从 date-expression2 中减去该值，返回两个参数之间 date-parts 的数值。 
date-expression2 某一间隔的结束日期。从该值中减去 Date-expression1，返回两个参数之间 date-parts 的数值。 
功能：返回两个日期之间的间隔 */ 
/*DATEADD语法简述： DATEADD(datepart，number，date） datepart 年yy，yyyy | 季度qq，q | 月mm，m | 年中的日dy，y | 日dd，d | 周wk，ww | 星期dw，w | 小时hh | 分钟mi，n | 秒ss，s | 毫秒ms |微妙mcs |纳秒ns date 
参数是合法的日期表达式 number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。 
功能：函数在日期中添加或减去指定的时间间隔。 */ 
--查询最近10天要过生日的会员--- 
select * from (select *,NextBD=case when dateadd(yy,datediff(yy,birthday,getdate()),birthday)> getdate() then dateadd(yy,datediff(yy,birthday,getdate()),birthday) else dateadd(yy,datediff(yy,birthday,getdate())+1,birthday) end from company) as tb where datediff(d,getdate(),NextBD) between 0 and 10 and datepart(d,birthday)=datepart(d,NextBD)