1. 有时候查看SQL的时候表名或者字段名需要加[]，这是因为有时候你的表名或者字段名正好与sqlserver的保留字段重了
比如:有一个user表,直接select会报错
select * from user;
正确语法如下:
select * from [user];
2. SQL的子查询需要加 as 别名，否则编译报错;而oracle和mysql的子查询可以不加 as 别名
select * from (select * from [user])as a;
3.SQLServer的字符串也是用单引号包裹，双引号会报错
4.SQLServer的字符串拼接用concat()函数，如下
select CONCAT('1','2');
select CONCAT('1','2','3','4');
5.SQLServer触发器
参考:https://www.cnblogs.com/wangprince2017/p/7827091.html
例如:表结构
1. 创建触发器: (修改指定列name1的时候修改name2列的值为name1列的新值)
-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================CREATE TRIGGER [dbo].[trigger1] ON [dbo].[test] AFTER UPDATEAS print'修改name1触发触发器执行1';IF UPDATE([name1])BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; begin DECLARE @data_id integer set @data_id=(select [ID] from inserted) DECLARE @name1 nvarchar(50) set @name1=(select name1 from inserted) print'修改name1触发触发器执行2'; update test set name2 = @name1 where id = @data_id; print'修改name1触发触发器执行3'; end -- Insert statements for trigger hereENDprint'修改name1触发触发器执行4';return;
测试SQL:
update test set name2 = '';
结果:
修改name1触发触发器执行1
修改name1触发触发器执行4
(1 行受影响)
注意:
(1)IF语句如果不加{}也是对后面第一条语句生效(Begin-end视为一条语句);
(2)inserted是一个临时表，存的是更新后的新值，deleted也是一个临时表，存的是更新前的值；
(3)print @var可以打印变量信息;if update(column)表示修改指定列。
2.查看数据库所有的触发器以及与表的对应关系的SQL如下:
select triggers.name as [触发器],tables.name as [表名],triggers.is_disabled as [是否禁用],
triggers.is_instead_of_trigger AS [触发器类型],
case when triggers.is_instead_of_trigger = 1 then 'INSTEAD OF'
when triggers.is_instead_of_trigger = 0 then 'AFTER'
else null
end as [触发器类型描述]
from sys.triggers triggers
inner join sys.tables tables on triggers.parent_id = tables.object_id
where triggers.type ='TR'
order by triggers.create_date
3.根据触发器名称单独查看某个触发器的创建语句
exec sp_helptext 'triggername'
6. SQLServer查看监听的端口
exec sys.sp_readerrorlog 0, 1, 'listening'
