- 博客(191)
- 资源 (6)
- 收藏
- 关注
RSS订阅原创 db-使用事务同时提交多个数据表
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-31 17:59:35
427
原创 db-使用触发器删除相关联的两表间的数据
--判断是否存在名为‘tri_delete_laborage’的触发器if exists(select name from sysobjects where name='tri_delete_laborage'and type='TR')drop trigger tri_delete_laborage--删除已经存在的触发器gocreate trigger tri_delete_la...
2019-07-31 17:59:27
268
原创 db-使用递归触发器实现每次删除一条记录
/*判断表中是否有名为“delete_employee”的触发器*/if EXISTS (SELECT name FROM sysobjects WHERE name = 'delete_employee' AND type = 'TR')/*如果已经存在则删除*/drop trigger delete_employeego create trigg...
2019-07-31 17:59:20
504
原创 db-触发器的嵌套使用
/*为tb_teacher表创建DELETE触发器*/ create trigger teacher_delete on tb_teacher after delete as declare @rowcount int select @rowcount=@@rowcount /*删除操作所涉及的记录行数*/ if @rowcount >1 ...
2019-07-31 17:58:49
111
原创 db-delete触发器
create TRIGGER [trig_DeleteInfo] on [dbo].[tb_Employee]FOR deleteASif exists(select ID from deleted where ID in(select ID from tb_Salary))begindelete from tb_Salary where ID=(select ID from delet...
2019-07-31 17:58:33
101
原创 db-update触发器在系统日志中的应用
create TRIGGER 管理员日志 ON dbo.系统管理员表FOR UPDATEAS insert into 系统日志表(备注) select'用户名：'+inserted.用户名称 +' —密码：'+ inserted.密码 from inserted...
2019-07-31 17:57:25
86
原创 db-使用insert触发器
/*判断表中是否有名为“[trig_InsertInfo]”的触发器*/if EXISTS (SELECT name FROM sysobjects WHERE name = '[trig_InsertInfo]' AND type = 'TR')/*如果已经存在则删除*/drop trigger [trig_InsertInfo]gocreate T...
2019-07-31 17:57:03
173
原创 db-获取数据库中的触发器
select name as 触发器名称,crdate as 创建时间,refdate as最后一次修改时间 from sysobjects where xtype ='TR'
2019-07-31 17:56:32
173
原创 db-查看存储过程的相关信息
EXEC sp_helptext proc_AutoID--查看存储过程的定义文本EXEC sp_depends proc_AutoID--查看存储过程的相关性EXEC sp_help proc_AutoID--查看查看存储过程的属性
2019-07-31 17:56:25
296
原创 db-在存储过程中使用事务
--判断proc_TransInProc存储过程是否存在，如果存在将它删除if exists(select name from sysobjectswhere name='proc_TransInProc'and type='p') drop proc proc_TransInProc --删除存储过程GOcreate procedure proc_TransInProcasd...
2019-07-31 17:56:13
114
转载 db-在存储过程中使用return定义返回值
--判断是否存在所要创建的存储过程名称IF EXISTS(SELECT name FROM sysobjectsWHERE name='Proc_Return'AND type='P')--存在所要创建的存储过程则删除DROP PROCEDURE Proc_ReturnGOCREATE PROCEDURE Proc_Return@ID varchar(20),@Na...
2019-07-30 08:28:09
621
转载 db-使用视图生成计算列
--创建视图create view V_waregoods(编号, 商品名称,数量,单价,进价,销售额,利润)as--定义SELECT查询语句select 编号, 商品名称,数量,单价,进价,(单价*数量) AS 销售额, (单价*数量-进价*数量) AS 利润from tb_xsbgo--查询视图select * from V_waregoods...
2019-07-30 08:28:01
157
转载 db-对视图定义文本进行加密
--创建视图CREATE VIEW Lesson_Profession_viewWITH ENCRYPTION--加密视图AS--定义SELECT查询语句SELECT a.Name,a.JoinTime, b.PreName, a.IDFROM tb_Lesson AS a INNER JOIN tb_Profession AS b ON a.ofProfes...
2019-07-30 08:27:53
2126
转载 db-通过视图格式化检索到的数据
--创建视图create view v_warebooksas--定义SELECT查询语句并格式化日期select 图书名称,图书分类,出版日期, convert(varchar(10) , cast(出版日期 as smalldatetime),120)as 格式化日期from tb_Csharpbookgo--查询所创建的视图中的数据select ...
2019-07-30 08:27:48
105
转载 db-通过视图过滤数据
CREATE VIEW v_wareAS SELECT 商品编号,金额,销售票号from 销售表goselect * from v_ware
2019-07-30 08:27:43
197
转载 db-获取数据库的全部用户视图
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using System.Linq;using System.Dat...
2019-07-30 08:27:36
245
转载 db-RAISERROR的使用
GODECLARE @err varcharSET @err = 1IF @err <> 0 RAISERROR ('用户自定义错误调试', 16, 1)
2019-07-30 08:27:31
103
转载 db-try catch使用
use db_TomeTwoBEGIN Try SELECT 1/0;END TryBEGIN catch SELECT ERROR_NUMBER() AS ErrorNumber,--返回错误号 ERROR_SEVERITY() AS ErrorSeverity,--返回严重性 ERROR_STATE() AS ErrorSt...
2019-07-30 08:27:25
154
转载 db-用goto语句实现跳转
GODECLARE @MONTH INTSET @MONTH = 1LABEL1:SELECT * FROM dbo.tb_工资数据表 WHERE 工资月份 = @MONTHSET @MONTH = @MONTH+2IF @MONTH<12 GOTO LABEL1
2019-07-30 08:27:19
167
转载 db-WAITFOR的使用
GOBEGIN WAITFOR TIME '09:29:00' SELECT * FROM dbo.tb_工资数据表ENDGO
2019-07-30 08:27:13
137
转载 db-RETURN的使用
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PERSONSEL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[PERSONSEL]GOCREATE P...
2019-07-29 12:02:55
117
转载 db-case 的使用
GOSELECT 人员编号,人员姓名,部门名称,实发合计,工资年, CASE 工资月份 WHEN 1 THEN '1月份工资' WHEN 2 THEN '2月份工资' WHEN 3 THEN '3月份工资' WHEN 4 THEN '4月份工资' WHEN 5 THEN '5月份工资' ...
2019-07-29 12:02:47
179
转载 db-使用while
GODECLARE @SUM INTDECLARE @I INTSET @SUM = 0SET @I = 0WHILE @I>=0BEGIN SET @I=@I+1 IF @I>100 BEGIN PRINT '1到100之间的奇数和: '+CAST(@sum AS CHAR(10)) BREAK E...
2019-07-29 12:02:20
147
转载 db-使用IF EXISTS检测数据是否存在
GODECLARE @NAME VARCHAR(10)DECLARE @DEPT VARCHAR(20)SET @NAME = '高岩'SET @DEPT = '系统分析部'IF EXISTS (SELECT * FROM dbo.tb_工资数据表 WHERE 人员姓名=@NAME AND 部门名称=@DEPT)BEGIN PRINT '存在该人员的工资'ENDELSE I...
2019-07-29 12:02:10
726
转载 db-使用if指定执行条件
GODECLARE @DEPT VARCHAR(20)SET @DEPT = '系统分析部'IF (SELECT COUNT(*) FROM dbo.tb_工资数据表 WHERE 部门名称=@DEPT)>0BEGIN PRINT '该部门已经发过工资'ENDELSEBEGIN PRINT '该部门没有发过工资'END...
2019-07-29 12:02:04
243
转载 db-使用begin end控制批处理
GODECLARE @NAME VARCHAR(50)DECLARE @COUNT INTSET @NAME = '矿外单位'SET @COUNT = 0SET @COUNT=(SELECT COUNT(*) FROM tb_MR_NUMBER)IF @COUNT>5BEGIN PRINT '该单位电话大于5部 'ENDELSEBEGIN PRINT '该...
2019-07-29 12:01:58
544
转载 db-Having子句应用在多表查询中
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-29 12:01:53
141
转载 db-使用Having子句过滤分组数据
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-29 12:01:39
428
转载 db-使用存储过程删除表中的数据
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-29 12:01:30
915
转载 db-使用存储过程向表中添加数据
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-29 12:01:23
884
转载 db-使用存储过程查询多表中的数据
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-28 10:03:59
466
转载 db-创建和删除索引
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-28 10:03:52
111
转载 db-使用索引视图查询数据
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-28 10:03:40
116
转载 db-使用clustered关键字创建聚簇索引
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-28 10:03:06
666
转载 db-使用unique关键字创建索引
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-28 10:03:00
210
转载 db-静态交叉表
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-28 10:02:54
113
转载 db-使用not in引入子查询
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-28 10:02:37
198
转载 db-使用in引入子查询限定查询范围
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-28 10:02:30
358
转载 db-用in查询表中的记录信息
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-28 10:02:23
314
转载 db-使用外连接进行多表联合查询
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Dat...
2019-07-28 10:02:12
178
ASP.NET MVC 5高级编程 第5版（中文版带书签）
2018-07-09
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人