-- 查询区分大小写： WHERE COLLATE Chinese_PRC_CS_AI
-- 建立索引
CREATE INDEX IDX_DRP_HN_RLB_1
ON DRP_HN_RLB (SHOP_CODE, FSCL_DATE)
CREATE INDEX IDX_DRP_HN_RLB_2
ON DRP_HN_RLB (SHOP_CODE, V5_ORDER_NUM)
CREATE INDEX IDX_DRP_HN_RLB_DTL_1
ON DRP_HN_RLB_DTL (V5_ORDER_NUM, PROD_CODE)
CREATE INDEX IDX_DRP_HN_RLB_PAY_1
ON DRP_HN_RLB_PAY (V5_ORDER_NUM)
-- 删除主键约束
ALTER TABLE DRP_HN_RLB DROP CONSTRAINT DRP_HNN_RLB
-- 增加主键约束
ALTER TABLE DRP_HN_RLB ADD CONSTRAINT PK_DRP_HN_RLB PRIMARY KEY(SHOP_CODE,V5_ORDER_NUM)
-- 增加字段
ALTER TABLE DRP_HN_RLB_DTL ADD SHOP_CODE VARCHAR(25)
ALTER TABLE DRP_HN_RLB_PAY ADD SHOP_CODE VARCHAR(25)
-- ALTER TABLE DRP_HN_RLB_DTL ADD CONSTRAINT PK_DRP_HN_RLB_DTL PRIMARY KEY(SHOP_CODE,V5_ORDER_NUM)
-- ALTER TABLE DRP_HN_RLB_PAY ADD CONSTRAINT PK_DRP_HN_RLB_PAY PRIMARY KEY(SHOP_CODE,V5_ORDER_NUM)
-- 增加字段指定值约束
ALTER TABLE DRP_HN_RLB WITH CHECK ADD CONSTRAINT [CKC_RLB_HANDLED] CHECK (([HANDLED]='F' OR [HANDLED]='T'))
ALTER TABLE DRP_HN_RLB CHECK CONSTRAINT [CKC_RLB_HANDLED]
ALTER TABLE DRP_HN_RLB_DTL WITH CHECK ADD CONSTRAINT [CKC_RLB_DTL_HANDLED] CHECK (([HANDLED]='F' OR [HANDLED]='T'))
ALTER TABLE DRP_HN_RLB_DTL CHECK CONSTRAINT [CKC_RLB_DTL_HANDLED]
ALTER TABLE DRP_HN_RLB_PAY WITH CHECK ADD CONSTRAINT [CKC_RLB_PAY_HANDLED] CHECK (([HANDLED]='F' OR [HANDLED]='T'))
ALTER TABLE DRP_HN_RLB_PAY CHECK CONSTRAINT [CKC_RLB_PAY_HANDLED]
-----------------------
alter table SCM_UNIT_GRID alter column ROW_BG VARCHAR(1000);
DROP INDEX IDX_MTU_2 ON MTU;
CREATE INDEX IDX_MTU_2 ON dbo.MTU (DELIV_UNIT_ID, DELIV_WAREH_ID);
alter table mtu alter column deliv_wareh_id numeric(6) NOT NULL;
-------------------- for xml path -----------------------
SELECT b.code_type,ccc FROM
( SELECT code_type,
( SELECT description+' | ' FROM SYS_CODE_DTL WHERE CODE_TYPE = a.code_type FOR XML path('')
) AS ccc
FROM SYS_CODE_DTL A
GROUP BY code_type
) B
-------------END-----------
---- 查询结果排序 ---------
SELECT '昨日' AS S_DD,'昨至今前十门店: ' AS S_NAME,convert(VARCHAR(10),rn)+'. '+ shop_name AS V_SHOP,qty AS S_NUM,val AS S_VAL,DISC as DISC
FROM (
SELECT row_number() OVER(ORDER BY val DESC) rn,c.* -- 按金额降序
FROM (
SELECT shop_name,sum(qty) qty,sum(val) val,avg(DISC_rate) DISC FROM dao_au_sale
WHERE convert(varchar(10),doc_date,120) >= convert(VARCHAR(10),getdate()-1,120)
GROUP BY shop_name
) c
) c WHERE rn <= 11 -- 展现前 11 行
-------------------END-----------------------------
------------- 数值转字符 | 时段|环比|同比 -------------- BETWEEN :FROM_DATE AND :TO_DATE [时段参数]
SELECT '时段' AS S_DD,'日期选择: ' AS S_NAME,
'SALE 其中 V: '+ (SELECT convert(VARCHAR(50),cast(sum(qty) as int)) + ' / '+convert(VARCHAR(50),sum(val)) -- 数值转字符
FROM dao_au_sale
WHERE doc_date BETWEEN :FROM_DATE AND :TO_DATE
AND prsnl_code IS NOT NULL
) AS V_SHOP,
sum(qty) AS S_NUM,sum(val) AS S_VAL,avg(DISC_rate) DISC FROM dao_au_sale WHERE doc_date BETWEEN :FROM_DATE AND :TO_DATE
UNION ALL
SELECT '环比' AS S_DD,'日期选择: ' AS S_NAME,
'SALE 其中 V: '+ (SELECT convert(VARCHAR(50),cast(sum(qty) as int)) + ' / '+convert(VARCHAR(50),sum(val))
FROM dao_au_sale
WHERE doc_date BETWEEN DateAdd(month, -1, :FROM_DATE) AND DateAdd(month, -1, :TO_DATE) --- 环比【上月同一时段】
AND prsnl_code IS NOT NULL
) AS V_SHOP,
sum(qty) AS S_NUM,sum(val) AS S_VAL,avg(DISC_rate) DISC FROM dao_au_sale WHERE doc_date BETWEEN DateAdd(month, -1, :FROM_DATE) AND DateAdd(month, -1, :TO_DATE)
UNION ALL
SELECT '同比' AS S_DD,'日期选择: ' AS S_NAME,
'SALE 其中 V: '+ (SELECT convert(VARCHAR(50),cast(sum(qty) as int)) + ' / '+convert(VARCHAR(50),sum(val))
FROM dao_au_sale
WHERE doc_date BETWEEN DateAdd(year, -1, :FROM_DATE) AND DateAdd(year, -1, :TO_DATE) --- 同比 【上年同一时段】
AND prsnl_code IS NOT NULL
) AS V_SHOP,
sum(qty) AS S_NUM,sum(val) AS S_VAL,avg(DISC_rate) DISC FROM dao_au_sale WHERE doc_date BETWEEN DateAdd(year, -1, :FROM_DATE) AND DateAdd(year, -1, :TO_DATE)
----------------------- END --------------------------------------------------********************
是数字 NOT LIKE '%[^0-9]%'
是字母 NOT LIKE '%[^A-Za-z]%'
-- < 日期 >
-- getdate() 获得当前日期函数
SELECT getdate()
-- dateadd() 指定增加日期函数
-- 在指定的日期值上增加指定的日期值 ( mm 月 ，yy 年 ，dd 日 )
select dateadd(mm,3,getdate())
select dateadd(dd,40,getdate())
select dateadd(mm,-3,getdate())
-- datediff(,,)差异日期函数 | 获得两个指定日期之间的差异值
select Age, Classid,datediff(yyyy,Birthday,getdate()) from Teacher
-- 查询年龄在15-20岁之间的女生信息
select * from Teacher where datediff(yyyy,Birthday,getdate()) between 15 and 20 and Gender = 'False'
select datediff(ss,'1999-9-9',getdate())
-- dateName()返回日期中部分字符串函数 | 得到日期的字符串部分
select Datename(weekday,getdate())
select Datename(month,getdate())
-- datepart()返回日期中指定日期部分的整数形式函数 | 日期中指定日期部分的整数形式
select DatePart(day,getdate())
select datepart(day,'2018-10-16')
今天的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())=0
昨天的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())=1
7天内的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())<=7
30天内的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())<=30
本月的所有数据：select * from 表名 where DateDiff(mm,datetime类型字段,getdate())=0
本年的所有数据：select * from 表名 where DateDiff(yy,datetime类型字段,getdate())=0
查询今天是今年的第几天： select datepart(dayofyear,getDate())
查询今天是本月的第几天：1. select datepart(dd, getDate())
2.select day(getDate())
查询本周的星期一日期是多少 （注意：指定日期不能是周日，如果是周日会计算到下周一去。所以如果是周日要减一天） SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0)
查询昨天日期：select convert(char,dateadd(DD,-1,getdate()),111) //111是样式号，（100-114）
查询本月第一天日期：Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) as firstday
查询本月最后一天日期：Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) as lastday //修改-3的值会有相应的变化
本月有多少天：select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast((cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' ) as datetime ))))
求两个时间段相差几天：select datediff(day,'2012/8/1','2012/8/20') as daysum
在指定的日期上±N天：select convert(char,dateadd(dd,1,'2012/8/20'),111) as riqi //输出2012/8/21
在指定的日期上±N分钟：select dateadd(mi,-15,getdate()) //查询当前时间15分钟之前的日期
---------------------
-- **************************************************************************************** --
-- 输出转换 cast() | convert()
PRINT 1+1
PRINT 56/8
PRINT '我的成绩是：' + cast(100 as char(13)) + 'into '
print '我的生日是：'+'1999-9-9'
print'我的生日是：'+convert(char(20),'1999-9-9',100)
print'我的生日是：'+convert(char(20),getdate(),100) --100 日期显示的格式不同
print'我的生日是：'+convert(char(20),getdate(),101)
print'我的生日是：'+convert(char(20),getdate(),102)
print'我的生日是：'+convert(char(20),getdate(),103)
-- 字符串函数 charindex() | len() | upper() / lower() 大小写转换 | ltrim() / rtrim() 左右去空格 |
select charindex('am','I am a boy',0)
select charindex('am','I am a boy',3)
select charindex('am','I am a boy',5)
select charindex('atm','I am a boy')
select len('I am a boy')
-- right()
select right ('I am a boy' ,3)
-- substring()
select substring ('I trust you will say I am a boy',charindex('am','Trust me ,I am really a boy!'),2)
-- replace()
select REPLACE('I am a smart boy ', 'smart', 'clever')
-- stuff() 在一个字符串中，从指定位置删除指定长度的字符，并在该位置插入一个新的字符 ,从1开始。参数1：字符串 参数2：指定位置，参数3：删除指定长度，参数4：插入的新字符串
select stuff('I am a clever boy',8,6,'elegant')
-- 声明变量
DECLARE @age INT
-- 变量赋值
SET @age = 26
-- while 循环
DECLARE @i int
SET @i= 1
WHILE @i <= 10
BEGIN
@i= @i + 1
PRINT @i
END
declare @x int
set @x = 1
while(@i <= 3)
begin
set @x = @x + 1
print @x
end
-- if else
if @i>10
begin
print '大于10'
end
else if @i>5
begin
print '大于5'
end
else
begin
print '小于等于5'
END
-- 系统变量
@@version: 返回 SQL Server的当前安装的系统和生成信息。
@@error: 上一条sql语句出错，会有错误号；上一条sql执行没出错，则为0。
@@lanuage: 返回当前所用语言的名称。
@@max_connections: 返回 SQL Server实例允许同时进行的最大用户连接数。(实际允许的用户连接数还依赖于所安装的 SQL Server的版本以及应用程序和硬件的限制)
@@Rowcount: 上一条sql语句影响的行数。
@@servername: 返回正在运行 SQL Server的本地服务器的名称。
@@connections： 此函数返回 SQL Server自上次启动以来尝试的连接数，无论连接是成功还是失败。
-- 事务
一个事务中，不能一个sql语句执行成功，一个执行失败。只要有一个sql语句执行失败，就是失败。
自动提高事务：当执行一条sql语句，数据库自动打开一个事务，执行成功->自动提交，执行失败->自动回滚。
隐式事务：当执行一条sql语句，数据库自动打开一个事务，需要手动提交，手动回滚。（打开隐式事务：set implicit_Transactions ON）
显式事务：需要手动打开事务，手动提交，手动回滚。
打开一个事务：
begin transaction
declare @sum int=0
update tableA set Name='大胡子' where id=2
set @sum=@sum+@@error
update tableB set Name='大胡子' where id=2
set @sum=@sum+@@error
if @sum<>0
begin
rollback --回滚，也可写rollback transaction
end
else
begin
commit --提交,也可写commit transaction
END
-- 游标 cursor
declare Mycursor cursor for
select UserName,Age,Email from tbUsers
open Mycursor
declare @uName varchar(50)
declare @uAge int
declare @uEmail varchar(50)
fetch next from Mycursor into @uName,@uAge,@uEmail
if (@@fetch_status=0)
begin
print @uName
print @uAge
print @uEmail
end
close Mycursor
-- 触发器 trigger
触发器是数据库服务器中发生事件时自动执行的一种特殊存储过程。
DML触发器：如果用户要通过数据操作语言 (DML) 事件编辑数据，则执行 DML 触发器。
表或视图的 insert, delete, update语句(不支持select)。
DDL触发器：DDL 触发器用于响应各种数据定义语言 (DDL) 事件。
这些事件主要对应于 Transact-SQL Create、Alter 和 Drop 语句，以及执行类似 DDL 操作的某些系统存储过程。
登录触发器： 登录触发器在遇到 Login 事件时触发，该事件是在建立用户会话时引发的。
创建触发器：
create trigger trg_In ON tbUsers
after delete
as
begin
insert into tbUsers_backup(Name,Age,Email) select Name,Age,Email from deleted
end
-- ******************************************************** --
-- 获取本周周一
SELECT DATEADD(Day,(@i+1)-(DATEPART(Weekday,getdate())+@@DATEFIRST-1)%7,getdate())
---- datediff()
datepart 缩写
年 yy, yyyy
季度 qq, q
月 mm, m
年中的日 dy, y
日 dd, d
周 wk, ww
星期 dw, w
小时 hh
分钟 mi, n
秒 ss, s
毫秒 ms
微妙 mcs
纳秒 ns
-----------------
全局变量使用@@来表示，一般都是系统预定义的一些全局变量。常用的全局变量有
@@ERROR 最后一个SQL错误的错误号
@@IDENTITY 最后一次插入的标识值
@@LANGUAGE 当前使用的语言的名称
@@MAX_CONNECTIONS 可以创建的同时连接的最大数目
@@ROWCOUNT 受上一个SQL语句影响的行数
@@SERVERNAME 本地服务器的名称
@@TRANSCOUNT 当前连接打开的事物数
@@VERSION SQL Server的版本信息
----------------------
PRINT @@VERSION
SELECT @@TRANCOUNT
SELECT @@ERROR
------- SQL 透视用法 ------
SELECT <非透视的列>,
[第一个透视的列] AS <列名称>,
[第二个透视的列] AS <列名称>,
...
[最后一个透视的列] AS <列名称>,
FROM
TABLE_NAME
PIVOT
(
<聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
IN ([第一个透视的列], [第二个透视的列],
... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>
-- 例:
SELECT [2020春],[2020夏]
FROM (SELECT season,sum(qty) AS qty FROM dao_au_sale WHERE datediff(dd,DOC_DATE,getdate()) = 1 GROUP BY season) a
PIVOT
(sum(qty) FOR season IN ([2020春],[2020夏])) pt
浙公网安备 33010602011771号