-- 查询区分大小写： 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