分类目录归档：SQL

SQL

mysql日期的加减

留下评论

DATE_ADD和DATE_SUB

语法为：DATE_ADD(date,interval expr type)、DATE_SUB(date,interval expr type)
其中常用的type的类型有：second、minute、hour、day、month、year等

DATE_ADD是对日期的增加，如果天数为负数时，则表示对日期减少，
DATE_SUB是对日期的减少，如果天数为负数时，则表示对日期增加

详细说明请查看官方文档

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

可灵活使用

例如：

-- 获取当前日期 2020-04-07
SELECT CURDATE();
-- 获取当前时间 15:24:01
SELECT CURTIME();
-- 获取当前日期加时间 2020-04-07 23:10:30
SELECT NOW();
-- 获取明天的日期 2020-04-08 获取日期时间格式将CURDATE()替换成NOW()即可
SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY);
-- 或者 其他获取月、年方法同样就不重复写了
SELECT DATE_SUB(CURDATE(), INTERVAL -1 DAY);
# 获取下个月、明年将DAY替换成对应MONTH、YEAR即可
SELECT DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
-- 获取明年的日期 2021-04-07
SELECT DATE_ADD(CURDATE(), INTERVAL 1 YEAR);
-- 获取减去一天2小时
SELECT DATE_ADD('2022-02-04 20:00:00', INTERVAL '-1 2' DAY_HOUR);
# -> '2022-02-03 18:00:00'
# 获取减去一天1小时1分钟1秒
SELECT DATE_SUB('2025-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
# -> '2024-12-30 22:58:59'
SQL

sql server 慢查询 的sql 语句内容查询

留下评论

SELECT TOP 20 TEXT AS ‘SQL Statement’,last_execution_time AS ‘Last Execution Time’
,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
,execution_count AS “Execution Count”
,qp.query_plan AS “Query Plan”
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp –加上这个，执行时间有点长。
ORDER BY total_elapsed_time / execution_count DESC

SELECT TOP 20
total_worker_time/1000 AS [总消耗CPU 时间(ms)],
execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],
max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,(CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的语法],
qt.text [完整语法],
dbname=db_name(qt.dbid),
object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY total_worker_time DESC

SQL

SQL server getdate()时间格式化

留下评论

Select CONVERT(varchar(45), GETDATE(), 0): 08 24 2018 08:22AM
Select CONVERT(varchar(45), GETDATE(), 1): 08/24/18
Select CONVERT(varchar(45), GETDATE(), 2): 18.08.24
Select CONVERT(varchar(45), GETDATE(), 3): 24/08/24
Select CONVERT(varchar(45), GETDATE(), 4): 24.08.18
Select CONVERT(varchar(45), GETDATE(), 5): 24-08-18
Select CONVERT(varchar(45), GETDATE(), 6): 24 08 18
Select CONVERT(varchar(45), GETDATE(), 7): 08 24, 18
Select CONVERT(varchar(45), GETDATE(), 8): 08:22:46
Select CONVERT(varchar(45), GETDATE(), 9): 08 24 2018 08:22:46:625AM
Select CONVERT(varchar(45), GETDATE(), 10): 08-24-18
Select CONVERT(varchar(45), GETDATE(), 11): 18/08/24
Select CONVERT(varchar(45), GETDATE(), 12): 180824
Select CONVERT(varchar(45), GETDATE(), 13): 24 08 2018 08:22:46:862
Select CONVERT(varchar(45), GETDATE(), 14): 08:22:46:967
Select CONVERT(varchar(45), GETDATE(), 20): 2018-08-24 08:22:47
Select CONVERT(varchar(45), GETDATE(), 21): 2018-08-24 08:22:47.157
Select CONVERT(varchar(45), GETDATE(), 22): 08/24/18 08:22:47 AM
Select CONVERT(varchar(45), GETDATE(), 23): 2018-08-24
Select CONVERT(varchar(45), GETDATE(), 24): 08:22:47
Select CONVERT(varchar(45), GETDATE(), 25): 2018-08-24 08:22:47.350
Select CONVERT(varchar(45), GETDATE(), 100): 08 24 2018 08:22AM
Select CONVERT(varchar(45), GETDATE(), 101): 08/24/2018
Select CONVERT(varchar(45), GETDATE(), 102): 2018.08.24
Select CONVERT(varchar(45), GETDATE(), 103): 24/08/2018
Select CONVERT(varchar(45), GETDATE(), 104): 24.08.2018
Select CONVERT(varchar(45), GETDATE(), 108): 24-08-2018
Select CONVERT(varchar(45), GETDATE(), 118): 24 08 2018
Select CONVERT(varchar(45), GETDATE(), 107): 08 24, 2018
Select CONVERT(varchar(45), GETDATE(), 108): 08:22:49
Select CONVERT(varchar(45), GETDATE(), 109): 08 24 2018 08:22:49:153AM
Select CONVERT(varchar(45), GETDATE(), 110): 08-24-2018
Select CONVERT(varchar(45), GETDATE(), 111): 2018/08/24
Select CONVERT(varchar(45), GETDATE(), 112): 20180824
Select CONVERT(varchar(45), GETDATE(), 113): 24 08 2018 08:22:49:426
Select CONVERT(varchar(45), GETDATE(), 114): 08:22:47:375
Select CONVERT(varchar(45), GETDATE(), 120): 2018-08-24 08:22:47
Select CONVERT(varchar(45), GETDATE(), 121): 2018-08-24 08:22:47.655
Select CONVERT(varchar(45), GETDATE(), 126): 2018-08-24T08:22:47.125

SQL

用SQL语句查找包含有某个关键字的存储过程、触发器、函数等等

留下评论

 

select name 
from sysobjects o, syscomments s 
where o.id = s.id 
and text like '%Extent1%' 
--and o.xtype = 'P'  
或者
 SELECT OBJECT_NAME(id) AS 存储过程,
        id
 FROM syscomments
 WHERE id IN (
                 SELECT OBJECT_ID(name) FROM dbo.sysobjects WHERE xtype = 'P' --存储过程为P
             )
       AND text LIKE '%FieldName%' --关键字
 GROUP BY id;

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 = 扩展存储过程

 

 

例如:

select b.name
from iqsys.dbo.syscomments a, iqsys.dbo.sysobjects b
where a.id=b.id and b.xtype=’p’ and a.text like ‘%黑名单%’
order by name

SQL、挨踢

SQL去除回车符，换行符，空格和水平制表符

留下评论

QL去除回车符，换行符，空格和水平制表符，参考下面语句，一般情况是SQL接受富文本或是textarea的内容。在数据库接收到这些数据之后，还是对其做一些处理。
 
REPLACE(REPLACE(REPLACE(REPLACE([fieldName],CHAR(13),”),CHAR(10),”),CHAR(9),”),’ ‘,”)
 
其中：
char(9)     水平制表符 
char(10)   换行
char(13)   回车
去掉空格
replace(‘字段’,’ ‘,”)
去掉回车换行符号
replace(‘字段’,char(13)+char(10),”)

SQL

no manifiest section for signature file entry com/microsoft/sqlserver/jdbc/InputStreamGetterArgs.class

留下评论

Exception in thread “main” java.lang.SecurityException: invalid SHA1 signature file digest for com/microsoft/sqlserver/jdbc/SQLServerException.class
插件打成的jar包运行出现这个异常，异常处理办法

1、把源sqljdbc.jar驱动解压到sqljdbc目录

2、删除 sqljdbc\META-INF\目录中出mainfest.mf，zigbert.rsa 以外的文件。

3、修改 sqljdbc\META-INF\目录中的mainfest.mf ，保留第一行，其余删除

4、重新打包：进入sqljdbc目录,执行:jar cvf sqljdbc.jar . 注意这最后的一个点号不能掉(有空格)

5、将新打包的文件加入项目工程中即可。

重新打包运行，OK

SQL

查询指定时间段内的工作日

留下评论

SELECT dbo.IT_T_WORKDAY(‘2017-01-12′,’2018-01-12’,”)

 

ALTER FUNCTION [dbo].[IT_T_WORKDAY]

–指定日期范围内工作日. by lzp
(
@sdate varchar(50),
@edate varchar(50),
@str VARCHAR(5000)
)
RETURNS VARCHAR(5000)
AS
BEGIN

SELECT @str=COUNT(*) FROM (SELECT t.yyyymmdd
FROM ( SELECT CONVERT (CHAR(10), DATEADD(dd, number, @sdate), 120) AS yyyymmdd,
DATEPART(dw, CONVERT (CHAR(10), DATEADD(dd, number, @sdate), 120)) wn
FROM master.dbo.spt_values AS spt
WHERE type = ‘p’
AND CONVERT (CHAR(10), DATEADD(dd, number, @sdate), 120) < = @edate ) t
WHERE t.wn IN ( 2, 3, 4, 5, 6 )
AND t.yyyymmdd NOT IN ( SELECT HDAY
FROM dbo.IT_T_HDAY )
UNION
SELECT td.TDAY
FROM dbo.IT_T_TDAY td
WHERE td.TDAY >= @sdate
AND td.TDAY <= @edate) t WHERE 1=1

RETURN @str
END

 

 

 

 

/*
Navicat SQL Server Data Transfer

工作日休假表

Target Server Type : SQL Server
Target Server Version : 105000
File Encoding : 65001

Date: 2018-01-26 10:59:35
*/

— —————————-
— Table structure for IT_T_HDAY
— —————————-
DROP TABLE [dbo].[IT_T_HDAY]
GO
CREATE TABLE [dbo].[IT_T_HDAY] (
[ID] int NOT NULL ,
[HDAY] date NULL ,
[REMARK] varchar(50) NULL
)

GO

— —————————-
— Indexes structure for table IT_T_HDAY
— —————————-

— —————————-
— Primary Key structure for table IT_T_HDAY
— —————————-
ALTER TABLE [dbo].[IT_T_HDAY] ADD PRIMARY KEY ([ID])
GO

 

 

 

/*
Navicat SQL Server Data Transfer

周末调休表

Target Server Type : SQL Server
Target Server Version : 105000
File Encoding : 65001

Date: 2018-01-26 10:59:54
*/

— —————————-
— Table structure for IT_T_TDAY
— —————————-
DROP TABLE [dbo].[IT_T_TDAY]
GO
CREATE TABLE [dbo].[IT_T_TDAY] (
[ID] int NOT NULL ,
[TDAY] date NULL ,
[REMARK] varchar(50) NULL
)

GO

— —————————-
— Indexes structure for table IT_T_TDAY
— —————————-

— —————————-
— Primary Key structure for table IT_T_TDAY
— —————————-
ALTER TABLE [dbo].[IT_T_TDAY] ADD PRIMARY KEY ([ID])
GO

 

 

 

 

IQSYS/framework

SQL

SQL Server 触发器 的 增删改查

留下评论

一: 触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约`束。

二: SQL Server为每个触发器都创建了两个专用表:Inserted表和Deleted表。这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后﹐与该触发器相关的这两个表也被删除。
Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。
Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。
三:Instead of 和 After触发器
SQL Server2000提供了两种触发器:Instead of 和After 触发器。这两种触发器的差别在于他们被激活的同:

Instead of触发器用于替代引起触发器执行的T-SQL语句。除表之外﹐Instead of 触发器也可以用于视图﹐用来扩展视图可以支持的更新操作。

After触发器在一个Insert,Update或Deleted语句之后执行﹐进行约束检查等动作都在After触发器被激活之前发生。After触发器只能用于表。

一个表或视图的每一个修改动作(insert,update和delete)都可以有一个instead of 触发器﹐一个表的每个修改动作都可以有多个After触发器。
四:触发器的执行过程
如果一个Insert﹑update或者delete语句违反了约束﹐那幺After触发器不会执行﹐因为对约束的检查是在After触发器被激动之前发生的。所以After触发器不能超越约束。

Instead of 触发器可以取代激发它的操作来执行。它在Inserted表和Deleted表刚刚建立﹐其它任何操作还没有发生时被执行。因为Instead of 触发器在约束之前执行﹐所以它可以对约束进行一些预处理。

五:使用T-SQL语句来创建触发器

基本语句如下:

create trigger trigger_name
on {table_name view_name}
{for After Instead of }
[ insert, update,delete ]
as
sql_statement

六:删除触发器：

基本语句如下:

drop trigger trigger_name

七：查看数据库中已有触发器：

— 查看数据库已有触发器
use jxcSoftware
go
select * from sysobjects where xtype=’TR’

— 查看单个触发器
exec sp_helptext ‘触发器名’

八:修改触发器：

基本语句如下:

alter trigger trigger_name
on {table_name view_name}
{for After Instead of }
[ insert, update,delete ]
as
sql_statement

九:相关示例:
1:在Orders表中建立触发器﹐当向Orders表中插入一条订单记录时﹐检查goods表的货品状态status是否为1(正在整理)﹐是﹐则不能往Orders表加入该订单。

create trigger orderinsert
on orders
after insert
as
if (select status from goods,inserted
where goods.name=inserted.goodsname)=1
begin
print ‘the goods is being processed’
print ‘the order cannot be committed’
rollback transaction –回滚﹐避免加入
end

2:在Orders表建立一个插入触发器﹐在添加一条订单时﹐减少Goods表相应的货品记录中的库存。

create trigger orderinsert1
on orders
after insert
as
update goods set storage=storage-inserted.quantity
from goods,inserted
where
goods.name=inserted.goodsname

3:在Goods表建立删除触发器﹐实现Goods表和Orders表的级联删除。

create trigger goodsdelete
on goods
after delete
as
delete from orders
where goodsname in
(select name from deleted)

4:在Orders表建立一个更新触发器﹐监视Orders表的订单日期(OrderDate)列﹐使其不能手工修改.

create trigger orderdateupdate
on orders
after update
as
if update(orderdate)
begin
raiserror(‘ orderdate cannot be modified’,10,1)
rollback transaction
end

5:在Orders表建立一个插入触发器﹐保证向Orders表插入的货品名必须要在Goods表中一定存在。

create trigger orderinsert3
on orders
after insert
as
if (select count(*) from goods,inserted where goods.name=inserted.goodsname)=0
begin
print ‘ no entry in goods for this order’
rollback transaction
end

 

6：Orders表建立一个插入触发器，保证向Orders表插入的货品信息要在Order表中添加

alter trigger addOrder
on Orders
for insert
as
insert into Order
select inserted.Id, inserted.goodName,inserted.Number from inserted