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触发器
例如:表结构
1. 创建触发器: (修改指定列name1的时候修改name2列的值为name1列的新值)
-- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE TRIGGER [dbo].[trigger1] ON [dbo].[test] AFTER UPDATE AS 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 here END print'修改name1触发触发器执行4'; return;
测试SQL:
update test set name2 = '4';
结果:
修改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'