sqlserver

新建表

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[car]') AND type IN ('U'))
    DROP TABLE [dbo].[car]
GO

CREATE TABLE [dbo].[car] (
  [id] int NOT NULL,
  [cid] int NULL,
  [uid] int NULL,
  [creadtime] datetime NOT NULL
)
GO

ALTER TABLE [dbo].[car] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [car]
-- ----------------------------
INSERT INTO [dbo].[car]  VALUES (N'1', N'1', N'2', N'2018-02-16 00:00:00.000')
GO

INSERT INTO [dbo].[car]  VALUES (N'2', N'2', N'0', N'2018-02-08 00:00:00.000')
GO

INSERT INTO [dbo].[car]  VALUES (N'7', NULL, NULL, N'2018-01-01 00:00:00.000')
GO


-- ----------------------------
-- Primary Key structure for table car
-- ----------------------------
ALTER TABLE [dbo].[car] ADD CONSTRAINT [PK__car__3213E83F329FF062] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF)  
ON [PRIMARY]
GO

声明变量查询，每句后面不要加分号，否则会报错变量不存在，计算平均值函数AVG()不包含值为NULL的值，0包括；convert()为转换函数

DECLARE @sStandard DECIMAL(6,2)
SELECT @sStandard=AVG(uid)  FROM [dbo].[car]
SELECT '平均数:'+CONVERT(nvarchar(50),@sStandard) as '平均数'

结果：

between  and，不包括NULL,包括边界值

select * from car where uid between 0 and 6

count(uid)对某列计数不包括NULL，包括0；count(*)、count(1)计数包括NULL和0

select count(uid) from car

批量更新 从一个表的字段更新到另一个表中

update S_USER set account=p.account from S_PERSON p where p.id=S_USER.person_id;

批量插入数据，从一个表中的数据插入到另一个表中

insert into Index_HospitalInfo(id , Year, CityName, Hid, CarryOutDialysisDate, PatientsCount, BedsCount, OrderBy, CreatedDate)
select NEWID(), Year, CityName, Hid, CarryOutDialysisDate, PatientsCount, BedsCount, OrderBy, CreatedDate  from Index_HospitalInfo where id='75F519CF-8110-463D-BDD8-AE49C5721172'

 

--获取当前时间

select getdate()

 

--获取一个guid

select NEWID()

 

创建表

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[HDIS_DialysisBloodPressure]') AND type IN ('U'))
    DROP TABLE [dbo].[HDIS_DialysisBloodPressure]
GO

CREATE TABLE [dbo].[HDIS_DialysisBloodPressure] (
  [Id] varchar(36) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [Pid] varchar(36) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [MeasureDate] datetime NOT NULL,
  [MeasureType] int NOT NULL,
  [MeasureMethod] int NOT NULL,
  [SystolicPressure] int NOT NULL,
  [DiastolicPressure] int NOT NULL,
  [MeanArterialPressure] int NULL,
  [PulseRate] int NOT NULL,
  [Breath] int NULL,
  [State] bit NOT NULL,
  [CreatedBy] varchar(36) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [CreatedDate] datetime NOT NULL,
  [ModifedBy] varchar(36) COLLATE Chinese_PRC_CI_AS NULL,
  [ModifedDate] datetime NULL
)
GO

ALTER TABLE [dbo].[HDIS_DialysisBloodPressure] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'透前透后血压',
'SCHEMA', N'dbo',
'TABLE', N'HDIS_DialysisBloodPressure'
GO


-- ----------------------------
-- Records of [HDIS_DialysisBloodPressure]
-- ----------------------------
INSERT INTO [dbo].[HDIS_DialysisBloodPressure]  VALUES (N'f7568a18-7ac4-42bd-bb45-b1b46a36bb7d', N'219fa7bb-5d43-42f4-a213-c0b8b430f1fc', N'2018-05-28 00:00:00.000', N'0', N'2', N'211', N'102', NULL, N'91', NULL, N'1', N'34f510d3-f280-44a4-b8d8-2f3c5025c304', N'2018-06-22 15:08:07.577', N'', NULL)
GO

INSERT INTO [dbo].[HDIS_DialysisBloodPressure]  VALUES (N'741d918a-c310-4ae4-9d82-77b6af53f09c', N'219fa7bb-5d43-42f4-a213-c0b8b430f1fc', N'2018-04-02 00:00:00.000', N'0', N'2', N'170', N'90', NULL, N'80', NULL, N'1', N'34f510d3-f280-44a4-b8d8-2f3c5025c304', N'2018-06-22 15:08:07.623', N'', NULL)
GO

INSERT INTO [dbo].[HDIS_DialysisBloodPressure]  VALUES (N'90ab6ef9-4686-47b9-a435-e5c027b66b7b', N'217814cc-6516-40d3-b467-9b78e7c7afab', N'2018-04-18 00:00:00.000', N'0', N'2', N'114', N'71', NULL, N'111', NULL, N'1', N'34f510d3-f280-44a4-b8d8-2f3c5025c304', N'2018-06-22 15:08:04.613', N'', NULL)
GO

INSERT INTO [dbo].[HDIS_DialysisBloodPressure]  VALUES (N'1f562e50-6ec9-4375-a532-5e8aedb22be1', N'217814cc-6516-40d3-b467-9b78e7c7afab', N'2018-04-18 00:00:00.000', N'2', N'2', N'142', N'98', NULL, N'82', NULL, N'1', N'34f510d3-f280-44a4-b8d8-2f3c5025c304', N'2018-06-22 15:08:04.660', N'', NULL)
GO


-- ----------------------------
-- Primary Key structure for table HDIS_DialysisBloodPressure
-- ----------------------------
ALTER TABLE [dbo].[HDIS_DialysisBloodPressure] ADD CONSTRAINT [PK_HDIS_DIALYSISBLOODPRESSURE] PRIMARY KEY NONCLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO

按照患者主键pid分组取最新一条数据

  SELECT  a.* FROM HDIS_DialysisBloodPressure a INNER JOIN 
  (
 SELECT pid,MAX(MeasureDate) MeasureDate FROM HDIS_DialysisBloodPressure WHERE 
 MeasureDate >='2018-06-01' AND  MeasureType=0
  GROUP BY pid ) b
  ON a.pid=b.pid AND a.MeasureDate =b.MeasureDate
   WHERE MeasureType=0   order by pid
     AND SystolicPressure BETWEEN 90 AND 130
 AND diastolicpressure BETWEEN 60 AND 89

DATALENGTH（）和len()的区别

--汽车

select DATALENGTH (Name) from [dbo].[Basic_City]  --长度为4

select len (Name) from [dbo].[Basic_City]   --长度为2

 

->select查询详解***********

select distinct\top ... --(5)投影

from -- （1）找到表

where -- （2）过滤掉我们不需要的数据

group by --（3）分组操作

having --（4）对分组后的数据进行过滤。

order by --（6）排序操作

 

 

--返回当前数据库的版本号

--select @@VERSION

 

--返回从SQL Server启动后到目前为止的累计时间

--select @@CPU_BUSY

 

--返回错误数

--select @@ERROR

 

--distinct只能紧跟这select后面，而且是对后面的所有的列都进行去重复操作(当两列都重复时去重)

--select distinct title,MiddleName from [SalesLT].[Customer] order by Title,MiddleName

 

模糊查询

--select * from SalesLT.Customer

--where FirstName like 'Jac%'

--where CompanyName like '%''%'--两个单引号表示一个单引号

--where CompanyName like '%[0-9]%'

--where CompanyName like '%[[]%' --escape '\'

 

--查询为middlename 不是null的数据

--select * from SalesLT.Customer where MiddleName is not null

 

--类型转换

--> Convert(目标类型，转换的表达式，格式规范)

--> Cast（表达 as 类型）

select Convert(nvarchar(32), CustomerId)+Title from SalesLT.Customer
select cast(CustomerId as nvarchar(32))+Title from SalesLT.Customer

快速复制表（包括表结构和数据）

select * into car2 from car

union  会进行去重操作，union all(不会去重)

select * from car union select * from car2
select * from car union all select * from car2

--添加一天 2015-03-02 00:00:00.000

--select dateadd(day,1,'2015-3-1')

 

--获取月的差数

--select DATEDIFF(MONTH,'2015-4-1','2015-5-9')

 

--获取月

--select datepart(MONTH,'2014-4-3')

 

--把字符转化成小写

--select LOWER(stuName) from tblStudent

 

--获取对应的数字

--select ASCII('a')

 

--从左开始获取前四位

--select LEFT('123456',4)

 

--从右开始获取四位

--select Right('123456',4)

 

--去除右边的空格

--select RTRIM(' 123 ')

 

--结果：abxxx，用第三个字符串代替第一个中存在的字符串

SELECT REPLACE('abcde','cde','xxx')

 

 创建呼叫中心员工表

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[callrecords]') AND type IN ('U'))
    DROP TABLE [dbo].[callrecords]
GO

CREATE TABLE [dbo].[callrecords] (
  [id] int IDENTITY(1,1) NOT NULL,
  [callernumber] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
  [telnum] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
  [startdatetime] datetime NULL,
  [enddatetime] datetime DEFAULT (getdate()) NULL
)
GO

ALTER TABLE [dbo].[callrecords] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'三位数字，呼叫中心员工编号（工号）',
'SCHEMA', N'dbo',
'TABLE', N'callrecords',
'COLUMN', N'callernumber'
GO

EXEC sp_addextendedproperty
'MS_Description', N'结束时间要大于开始时间,默认当前时间',
'SCHEMA', N'dbo',
'TABLE', N'callrecords',
'COLUMN', N'enddatetime'
GO


-- ----------------------------
-- Records of [callrecords]
-- ----------------------------
SET IDENTITY_INSERT [dbo].[callrecords] ON
GO

INSERT INTO [dbo].[callrecords] ([id], [callernumber], [telnum], [startdatetime], [enddatetime]) VALUES (N'1', N'001', N'0208888888', N'2010-07-10 10:00:00.000', N'2010-07-10 10:05:03.000')
GO

INSERT INTO [dbo].[callrecords] ([id], [callernumber], [telnum], [startdatetime], [enddatetime]) VALUES (N'2', N'001', N'0208888888', N'2010-07-11 13:00:00.000', N'2010-07-11 13:01:10.000')
GO

INSERT INTO [dbo].[callrecords] ([id], [callernumber], [telnum], [startdatetime], [enddatetime]) VALUES (N'3', N'001', N'89898989', N'2010-07-11 14:06:00.000', N'2010-07-11 14:09:00.000')
GO

INSERT INTO [dbo].[callrecords] ([id], [callernumber], [telnum], [startdatetime], [enddatetime]) VALUES (N'4', N'002', N'98987676', N'2010-07-13 21:06:00.000', N'2010-07-13 21:08:08.000')
GO

INSERT INTO [dbo].[callrecords] ([id], [callernumber], [telnum], [startdatetime], [enddatetime]) VALUES (N'5', N'002', N'02188839389', N'2010-06-29 20:11:00.000', N'2010-06-29 20:16:06.000')
GO

INSERT INTO [dbo].[callrecords] ([id], [callernumber], [telnum], [startdatetime], [enddatetime]) VALUES (N'6', N'001', N'767676766', N'2010-07-15 13:16:00.000', N'2010-07-15 13:26:00.000')
GO

INSERT INTO [dbo].[callrecords] ([id], [callernumber], [telnum], [startdatetime], [enddatetime]) VALUES (N'7', N'003', N'0227864656', N'2010-07-13 11:16:00.000', N'2010-07-13 11:17:09.000')
GO

INSERT INTO [dbo].[callrecords] ([id], [callernumber], [telnum], [startdatetime], [enddatetime]) VALUES (N'8', N'003', N'676765777', N'2010-07-19 19:26:02.000', N'2010-07-19 19:30:33.000')
GO

INSERT INTO [dbo].[callrecords] ([id], [callernumber], [telnum], [startdatetime], [enddatetime]) VALUES (N'9', N'001', N'89977653', N'2010-06-19 15:16:02.000', N'2010-06-19 15:26:10.000')
GO

INSERT INTO [dbo].[callrecords] ([id], [callernumber], [telnum], [startdatetime], [enddatetime]) VALUES (N'10', N'004', N'400400400', N'2010-06-19 15:16:02.000', N'2010-06-19 15:26:10.000')
GO

SET IDENTITY_INSERT [dbo].[callrecords] OFF
GO


-- ----------------------------
-- Checks structure for table callrecords
-- ----------------------------
ALTER TABLE [dbo].[callrecords] ADD CONSTRAINT [ck_callrecords] CHECK ([callernumber] like '[0-9][0-9][0-9]')
GO

ALTER TABLE [dbo].[callrecords] ADD CONSTRAINT [ck_callrecords_enddatetime] CHECK ([enddatetime]>[startdatetime])
GO


-- ----------------------------
-- Primary Key structure for table callrecords
-- ----------------------------
ALTER TABLE [dbo].[callrecords] ADD CONSTRAINT [pk_callrecords] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO

 查询通话时间最长的5条记录 

select  top 5 id,CallerNumber,datediff(second,startdatetime,enddatetime) as se  from callrecords order by se desc

查询以0开头的通话总时,以秒为计算单位

select 
sum(datediff(second,startdatetime,enddatetime))
from CallRecords
where TelNum like '0%'

查询2010年7月通话总时长最多的前两个呼叫员的编号

select  top 2 CallerNumber,sum(datediff(second,startdatetime,enddatetime)) as totalTime,count(*) as 电话总数 from callrecords where datediff(MONTH,'2010-07-01',startdatetime)=0
group by CallerNumber order by totalTime desc

时间段查询

select * from callrecords R where CAST(R.startdatetime  AS TIME)
 between '10:10:00'and '18:10:00'

每天的开始时间和结束时间

DECLARE @begin_date DATETIME,@end_date DATETIME
SELECT @begin_date = CONVERT(DATETIME,CONVERT(CHAR(10), DATEADD(DAY,-0,GETDATE()),120) + ' 00:00:00',120)
SELECT @end_date = CONVERT(DATETIME,CONVERT(CHAR(10), GETDATE(),120) + ' 23:59:59',120)
SELECT @begin_date AS 开始时间,@end_date AS 结束时间

 分页sql

declare @pageindex int,@pagesize int
set @pageindex=1
set @pagesize=5
SELECT TOP (@pagesize) * 
FROM
    (
        SELECT ROW_NUMBER() OVER (ORDER BY id desc) AS RowNumber,* FROM callrecords
    )AS  A  
WHERE RowNumber >@pagesize*(@pageindex-1) 

条件转换

SELECT *,CASE month
WHEN '1' THEN 'Jan'
WHEN '2' THEN 'Feb'
WHEN '3' THEN 'Mar'
WHEN '4' THEN 'Apr'
WHEN '5' THEN 'May'
WHEN '6' THEN 'Jun'
WHEN '7' THEN 'Jul'
WHEN '8' THEN 'Aug'
WHEN '9' THEN 'Sep'
WHEN '10' THEN 'Oct'
WHEN '11' THEN 'Nov'
WHEN '12' THEN 'Dec'
ELSE '其他' END AS MONTHen FROM dbo.TBEvent a WHERE YearID=@YearID ORDER BY  CAST(a.month AS INT) DESC

SQL多条件查询

declare @a nvarchar(50),@b nvarchar(50)
IF (@a!='')and(@b!='')
BEGIN
  SELECT * from tbUsService WHERE lang='cn' and  Industry=1 and Service=1  ORDER BY SortID DESC
END
ELSE if(@a!='')and(@b='')
 BEGIN
   SELECT * from tbUsService WHERE lang='cn' and  Industry=1   ORDER BY SortID DESC
 END
ELSE if(@a='')and(@b!='')
 BEGIN
   SELECT * from tbUsService WHERE lang='cn'  and Service=1  ORDER BY SortID DESC
  END
ELSE if(@a='')and(@b='')
 BEGIN
   SELECT * from tbUsService WHERE lang='cn'  ORDER BY SortID DESC
 END

union all模糊搜索

select  
name AS Name,intro AS intro,ID, '/Product/'+cast(ID As nvarchar(50)) as link,'product' AS searchtype,CreateTime AS publishdate from dbo.TBProduct
 where name like '%'+@Search+'%' or Intro like '%'+@Search+'%'
 union all 
 (select  
Title AS Name,Proface AS intro,ID ,'/News/'+cast(ID As nvarchar(50)) as link,'news' AS searchtype,PublishTime AS publishdate from TBNews
 where Title like '%'+@Search+'%' or Proface like '%'+@Search+'%' ) ORDER BY publishdate DESC