基于sqlserver的常用查询、视图、触发器、存储过程用法

首先在这申明，sqlserver是不区分大小写的。

SqlServer插入语句

insert into <表名>  [<属性1><属性2><属性3>] values(<常量1><常量1><常量1>)

SqlServer修改数据语句

update <表名> set <列名>=<表达式>,<列名>=<表达式> [where <条件>]

SqlServer删除数据语句

delete from <表名> [where<条件>]

查询中消除重复的行

select distinct Sno from sc;

where子句后面常跟的查询条件

比较—（=、<、>、>=、<=、!=、!>、!<(不小于)、）
确定范围—-（between and、not between and）
确定集合—–（in、not in）
字符匹配——（like、not like）
空值——（is null，is not null）
多重条件—–（and、or、not）

下面是上述的例子

select Sname from student where sdept='cs';

select Sname from student where sage between 20 and 30;（如果用not between and 就是不在这个范围的）

//使用谓词来查找属性是否属于指定集合元祖
select sname  from student where sdept in('cs','ma','is');

select sname from student where sname like '刘%'; 
select sname from student where sname like '_勇'; 

//  %用来代表任意长度的字符串
//  _下划线代表是任意字符

select sno from sc where grade is  not null;

select sname from  student where sdept='cs' and sage<20;

order by子句

用户可以用order by子句对查询结果按照一个或多个属性列进行排序，默认是升序。

select sno ,grade from sc where cno='3' order by grade desc;//升序是asc

聚集函数

count（*） ——-统计元祖的个数
count（distinct 列名）——统计某个列值的个数
sum（distinct 列名）——–统计列的总和
avg（distinct 列名）——-计算列的平均值
max（distinct 列名）——-计算列的最大值
min（distinct 列名）——-计算列的最小值

select AVG(grade) from sc ;

注意：

where 子句是不能用聚集函数作为条件表达式，聚集函数只能用于select 子句和group by 的having子句中。

group by子句

顾名思义，这个子句将查询结果按照某一列或者多列值分组，值相等的为一组。对查询结果分组是为了细化聚集函数作用对象，分组后，聚集函数将作用于每一组，每一组都有一个函数值。

select cno,COUNT(sno) from SC group by Cno;
//查询课程号下对应的选课人数。
// *凡是在group by后面出现的字段，必须要在select后面出现，或者该字段以聚合函数出现在select后面*  详细的理由如下：

//这句话的意思，就是将相同学号的grade列的属性值求平均值
select sno ,avg(grade) from sc group by sno;

正确的结果：

![这里写图片描述](https://img-blog.csdn.net/20171104185913643?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdmVudXMzMjE=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)

如果这时你不写sno，没问题，你自己知道是个啥就ok。

但是如果你这时写了个下面这个

select grade ,avg(grade) from sc group by sno;
//就会产生错误选择列表中的列 ‘sc.Grade’ 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
//很明显呀，你是按照学号排，你把分组之后的结果和grade放在一起，你觉得符合人之常情吗，明白了没，这么通俗易懂！！！！！！

分组重要的是一一匹配，如果这个不理解可以阅读下面文章
http://blog.163.com/xiaopengyan_109/blog/static/149832173201081983042803/

having

having一般是跟在group by子句后面，是对分组之后的信息筛选。

where作用对象是基本表或者是视图，而having作用于组，从中选择满足条件的组。

select sno from sc group by having count（cno）>2;
//查询选修课程两名以上的学号。

两表等值连接查询

连接查询，连接主要通过他们的公共属性。

select sname ,cno from SC,student where SC.Sno = Student.Sno;
//*注意在连接查询的时候，一定要注意如果出现的属性两个表都有一定要加表名区分*

两表自身连接

是在一个表中进行自身的连接。主要通过给表起两个不同名字，让相关心的字段等值连接。

例子：在一个表中要查每门课的先修课的先修课。

select first.cno,second.cno from Course first,Course second where first.Cno = second.Cpno;
//*在自身连接中如果查询条件中和select后面出现的字段一致，前面以示区分的表别名要不一致，否则有问题。*

tip

在这里我说一下，sql查询是采用的嵌套循环连接算法思想，就是在一个表中，我查询到一条数据，然后从另一个表，一一去寻找等值关系的数据，在返回。

外连接

就是在两个表进行连接查询，如果查询的结果有一方面是为空的，那么该结果是不保存的。

例子：比如查询考生的选课情况，及成绩，如果该同学没有选课，那么就查不到该学生，所以要是想显示该学生，必须按照学生表为主，显示。

select Student.Sno,sname,grade,cno from student,SC where Student.Sno  =SC.Sno;//它会直接忽略没有选课成绩的学生信息

select Student.Sno,sname,grade,cno from student left outer join sc on( Student.Sno  =SC.Sno);//这种查询就是外连接查询。//注意哪个表在前就是以哪个表为主
//其实有外连接就是换个说法，顺序不一样，自己看吧。

多表连接

其实对于已经掌握了两个表连接，在多几张表，其实道理都是一样的。它本质，还是两个表进行连接，每两个连接好了，在连接第三个等。

select student.sno,sc.cno,grade,cname from Student,SC,Course where Student.Sno = SC.Sno and SC.Cno = Course.Cno;

嵌套查询（可以多层嵌套）

嵌套查询语句可以在where子句和having中，需要注意的是嵌套查询中不能在使用group by，因为group by只能对最终结果排序。

嵌套查询如果子查询的结果是一个值，可以用=代替in，如果是多个还是用in。

查询没有选修“计算机导论”或者 “高等数学”的学生。

思路：我们要查询的学生的学号应该不在选修“计算机导论”或者 “高等数学”的学生学号中。

select sno from Student where Sno not in (select Sno from SC where Cno in(select Cno from Course where Cname in('计算机导论','高等数学')));

不相关子查询

子类查询的条件不依赖于父类，一般先执行子查询，然后根据子查询的结果用于父类查询的条件。

select sname from Student where Sdept in (select Sdept from Student where Sname='刘晨')
//查询和刘晨一个系的学生
//自身连接
select first.sname from Student first ,Student second where first.Sdept = second.Sdept and second.Sname='刘晨'
//查询选修信息系统的学生信息
select sc.sno,sname from SC,Student where Cno in(select Cno from Course where Cname='信息系统') and SC.sno=Student.sno
select student.sno,sname from SC,Student,Course where Student.Sno = SC.Sno and SC. cno= Course.Cno and Cname='信息系统'

带有any（some）、all谓词子查询

>any 大于子查询结果中的某个值
>all 大于子查询的所有值
！=any 不等于子查询结果中的某个值
！=all 不等于子查询结果中的所有值

//查询非计算机系中比计算机系任意一个学生年级小的学生。
select sname,sno from student where sage<any(select sage from student where sdept='cs') and sdept!='cs';

//查询非计算机系中比计算机系所有学生年级小的学生。
select sname,sno from student where sage<any(select sage from student where sdept='cs') and sdept!='cs';

带谓词（Exists）的子查询

Exists代表存在量词，带Exists的谓词子查询不返回任何数据，只产生逻辑真和逻辑假。

//连接查询
select sname from SC,Student where SC.Sno = Student.Sno and Cno=1;

//谓词查询
select sname from Student where exists(select * from sc where SC.sno = student.sno and cno=1)
//他的执行过程，先从student表中每一条记录拿学号和对应的sc.sno并且cno =1，如果符合条件将student中的sname保存到结果集。
//如果内层查询结果非空，则外层where子句返回真值，否则返回假值。
//由于exists引出的子查询，其目标列通常用*表示，因为exists子查询不返回真值，给出列名无意义。

//与exist对应的是no exists(不存在)
select sname from Student where no exists(select * from sc where SC.sno = student.sno and cno=1)

查询同时选修“计算机导论”或者 “高等数学”的学生

思考:这两门课没有一门是他没有选修的。

select sno,sname from Student where not exists (select * from Course where   Cname in ('计算机导论','高等数学') and not exists (select * from SC where Sno =Student.Sno and Cno = Course.cno));

集合查询

主要包括：并（union）、交（intersect）、差（except）。

//其实我并不明白存在的意义，明明可以用or、and处理，也许我还没发现其中奥妙。
在集合操作union会自动去除重复的，而or是所有的包括重复，在集合中如果不要重复union all

差运算就是返回除去条件后的所有结果集。

//查询选修课程1或者课程2的学生
select sno from SC  where cno=1 union select sno from SC where Cno =2 
select sno from SC where Cno =1 or Cno =2;

基于派生表的查询

子查询可以在from子句出现，这时子查询生成临时表，成为主查询对象。

//查询超过他选修所有课程成绩平均值的学生
select sc.sno,cno from SC ,(select sno,AVG(grade) from SC group by(sno)) as avg_1(sno,avg_grade)
where avg_1.sno = SC.Sno and grade>avg_1.avg_grade

//该执行过程，在执行到from后面将结果集保存在临时表中，然后在进行条件比较，最终返回符合的结果。

//*如果子查询中没有聚合函数，或者计算式，可以无需指定别名*

视图

视图是从一个或几个基本表（或视图导出的表），它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍然放在原来的基本表中，所以基本表数据一旦发生改变，视图中查询的数据也会随着改变。

视图可以和基本表一样被查询、删除，也可以在新的视图上定义新的视图，但是对视图的更新、修改则有规定。

记住往视图中插数据、更改数据、删除数据，都是和基本表一样，只不过换了一个套路，将表名换成视图名。

创建视图

create view [<列名><列名><列名><列名>]  as <子查询> [with check option]

//with check option  表示对视图进行update、insert、delete的时候保证操作的，满足子查询的条件表达式

//组成视图的属性列要么全部省略要么全部指定，不存在其他，如果省略，那么列名就是子查询select后面的列组成。
下面3中情况必须指明视图的所有列名
 1. 子查询中不是单纯的属性名，而是聚合函数或者是列表达式。
 2. 多表连接的时候，选出同名列作为视图的字段。
 3. 需要为视图起一个更合适的名字。

查询视图

SELECT 字段 FROM 视图名
//这里字段如果你创建视图给了字段名，就直接用，不然还是子查询的select后面的属性列名。

视图的检查

create view view3 as select sno,sname,sage,sdept from Student where sdept ='信息系' with check option;
//在后面对视图的操作中，增删改，读必须有条件就是部门是信息系，最简单你不能往视图中加一个别的系的，这个还懂吗！！！！！！！！！！！

视图上的建立

建立选修c01并且grade>90视图

create view view5 as select sname from view4 where grade >90;
//这个视图就是基于view4之上,view4中有grade字段。

虚拟列视图

创建学生的姓名，出生日期的视图

该查询中包含计算公式，所以必须创建一个新的列，所以整个查询的都要显示的赋一个名字。

create view view6(sname,sbirth) as select sname,2017-sage from Student;

基于分组的视图

查询每一个学生多门选修的平均成绩的视图

该查询中包含聚合函数，所以要显示指定每一个属性列。

create view view7 (sno,savg) as select sno,AVG(grade) from SC group by Sno;

删除视图

Drop view <视图名> [CASCADE]

如果该视图上海导出其他视图，那么需要加上级联操作，这样才可以将级联的视图一起删除。

注意：删除视图，只是从数据字典中删除，并不影响基本本的数据

视图的查询

在view1中查询年纪小于22的学生信息

select * from view1  where sage <22;//你可以利用视图中属性列名去做你需要的条件。

视图是可以和基本表进行连接的

select grade from sc,view4 where view4.sno  =SC.Sno;

视图的修改

由于视图是不实际存储数据的虚表，因此对视图的更新最终还是要转化为对基本表的更新操作

修改学号为9521102学生成绩为100

update view5 set grade =100 where sno='9521102'

在view中增加一个学生信息

insert into view7(sno,sname) values ('99999','老枪1');

删除学生信息

delete from view7 where sno='99999'

触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊过程。它主要实现更复杂的约束。

当特定事件（如对一个表的增删改，事务的结束等）发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。规则中的动作体可以很复杂，通常是一段SQL存储过程。

触发器可以建立基本表或者视图上。

触发器的优点

触发器是自动的
触发器可以通过数据库中的相关表进行层叠更改。
触发器可以强制限制，这些限制比用check约束更复杂。

  create trigger <触发器名>  on {table|view}
 [with encryption] for {after|instead of} {insert|update|delete}
as 
sql_statement
//触发器激活后所作的操作，例如检查、回滚，插入，删除

触发器after、instead of区别

AFTER:触发发生在SQL操作后，若发生错误，可利用回滚恢复原数据。

INSTEAD OF：触发发生在SQL操作前，且如何操作由触发器操作来实现。

INSTEAD OF 触发器的主要优点是可以使不能更新的视图支持更新。（为什么说INSTEAD OF对于视图的更新有很大作用，首先我们都知道视图是虚表，不可以对基本表进行修改，所以在视图上修改就是不可能，那么你用触发器的INSTEAD OF，就是代替执行，可以转化为对基本表的执行）基于多个基表的视图必须使用 INSTEAD OF 触发器来支持引用多个表中数据的插入、更新和删除操作。

每个表只能有一个同类型的INSTEAD OF触发器

After触发器：这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用Rollback Transaction语句来回滚本次的操作。

Instead Of触发器：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作。

创建trigger的注意点

After只能用于数据表上，instead of可以用于表与视图上允许建立多个同类操作的after触发器，但同类的insteadof触发器只能有一个
只有after触发器才能设置执行次序可以设置为first 或 last
可以用系统存储过程sp_settriggerorder设置 sp_settriggerorder @triggername = ‘MyTrigger’, @order = ‘first’, @stmttype = ‘UPDATE’
在对基础表进行更新前激发 INSTEAD OF 触发器 ALTER TRIGGER 语句更改了第一个或最后一个触发器，则将除去 First 或 Last 特性
两个触发器完全相同，会先执行后建立的触发器。执行的顺序与建立的顺序相反。

DELETED表和INSERTED表(内存表)

DELETED 表用于存储 DELETE 和 UPDATE 语句所影响的行的副本。

INSERTED 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。INSERTED 表中的行是触发器表中新行的副本。

触发器例子

创建一个触发器禁止插入，修改课程表，出现相同的课程名

create trigger courese_trigger on course for update ,insert 
as
if((select count(*) from course ,inserted where course.cname=inserted.cname)>1)//sql语言的编写，后面会讲，你看看吧。
//这个inserted表，会保存你插入的信息，从这个内存表中找到呢要插入的cname，来判断表中要没有此过程。
  begin
  print '存在相同的课程名,操作无法执行'//打印出错信息
  rollback transaction//事务回滚，不做任何操作
  end

创建触发器，学生选修同一门课程不能超过5人

  create trigger sc_trigger on sc for insert 
  as

  if((select count(*) from sc,inserted where sc.cno = inserted.cno)>5)
  begin
  print '该课程选修人数超过5人，该操作无法执行'
  rollback transaction//注意对于条件约束检查，如果不满足一定要执行回滚事件，不然该操作还是会执行。
  end

创建触发器在订单表(ORDER)中插入或修改订购数量(NUM),必须满足客户相应等级的数量上限和下限。

CREATE TRIGGER [CHECK_NUM] ON [ORDER] //在写该题有个注意点，就是sqlserver如果你自己定义和保留字一样，那么需要加[]以区分，注意以下。
FOR INSERT, UPDATE
AS
IF UPDATE(NUM)//如果你这样指定的话，只有修改num列的时候才会触发该触发器
BEGIN
//*由该例子我们可以看到if后面的条件我们要删除转化为存在、不存在、count(*)的比较*
   IF NOT EXISTS( 
     SELECT * FROM  INSERTED ,CUSTOMER,CUSTOMERLEVEL 
     WHERE INSERTED.CID=CUSTOMER.CID AND
     CUSTOMERLEVEL.CLEVEL=CUSTOMER.CLEVEL AND 
     NUM NOT BETWEEN CUSTOMERLEVEL .NUMDOWN AND
     CUSTOMERLEVEL .NUMUP ) 
     //该实例判断也可这样
     /*if((select count(*) from customerlevel,inserted ,customer,[order] where inserted.cid = customer.cid and customer.clevel= customerlevel.clevel and inserted.num between numdown and numup)<1)*/
    BEGIN
      PRINT  '订购数量违反了客户的限额！'
        ROLLBACK
    END
END

基于instead of 的触发器

create view view8 as select sno,sname from student//创建视图

select * from view8;

create trigger studnet_trigger on view8 
instead of insert//在插入视图前操作
as
 begin
 insert into Student (Sno,sname)values ((select sno from inserted),(select sname from inserted))//通过这个可以看出，触发器还是转化对基本表的操作
 end

 insert into view8(sno,sname) values('88888','枪仔')//往视图中插入数据，和往表中插入数据一致。

Transact-SQL

SQL作为结构化查询语言，是标准的关系型数据库通用的标准语言；T-SQL是在SQL基础上扩展的SQL Server中使用的语言。你自己联想oracle的pl/sql，和这个就在一个层面上。有了这个你就可以在sqlserver上面写程序了。

Transact-SQL 变量是 Transact-SQL 批处理和脚本中能够保存数据值的对象。声明或定义变量后，批处理中的一个 Transact-SQL 语句可以设置变量值，而该批处理中后面的语句可以从变量获得此值。

T-SQL 常用语法

如果你要完整的学习该语法

https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/variables-transact-sql

变量申明

指定一个名称。名称的第一个字符必须为一个 @。

DECLARE @MyCounter int;

一个简单例子：

求和

 declare @x int// declare不能少。
 declare @y int
 declare @z int

 set @x  =10//赋值给常数需要加set
 set @y  = 20

 set @z = @x+@y
 select @x,@y,@z;

这里写图片描述

是不是很神奇啦啦

指定查询学生的学号

 declare @sno char(7)//这边的数据类型，你最好对应表的数据类型一致。

 set @sno  ='9512101'

 select * from student where sno = @sno;


//需要注意的是，你必须一起运行上述代码，否则就会出现你没申明该变量的错误，注意一下。

begin..end

BEGIN  
    { sql_statement | statement_block }   
END

定义一系列一起执行的 Transact-SQL 语句

虽然所有的 Transact-SQL 语句在 BEGIN…END 块内都有效，但有些 Transact-SQL 语句不应分组在同一批处理或语句块中。

if….else

IF Boolean_expression   
     { sql_statement | statement_block }   
[ ELSE   
     { sql_statement | statement_block } ]

在判断语句中可以没有else，另外这个中是不需要{}，所以我们要把if放在begin….end代码块中。

存储过程

我们之前写的T-SQL语言都是你匿名块，每次执行的时候，都需要进行编译，并且不能存储到数据库中。而过程和函数是命名块，他们被编译后保存在数据库中，称为持久化存储块，可以反复调用，运行速度快。

存储过程是由过程化sql语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，使用时调用即可。

存储过程定义

 create procedure ([procedure_name]  [参数1]
[参数2] [参数3] [参数n])
as
sql_statement

创建不带参数的存储过程

 create procedure student1 
 as
 begin
 select distinct sname,sc.cno,grade from student,sc,course where student.sno = sc.sno and   sc.cno = course.cno and sdept='计算机系'
 end

 exec student1//执行存储过程

创建带参数的存储过程

  create procedure student2(@sdept char(20))//里面变量,这里不加declare可能定义存储过程的底层已经有了
 as

 begin
 select distinct sname,sc.cno,grade from student,sc,course where student.sno = sc.sno and sc.cno = course.cno and sdept=@sdept//里面赋值,这里不加set可能定义存储过程的底层已经有了
 end

  exec student2 '数学系'//传入真正执行的参数

创建参数有默认值的存储过程

  create procedure student3(@sdept char(20)='数学系')

 as
 begin
 select distinct sname,sc.cno,grade from student,sc,course where student.sno = sc.sno and sc.cno = course.cno and sdept=@sdept
 end
   exec student3

创建输出存储过程的值

 create procedure produce1 (@cname varchar(20),@avg int output)//创建一个输出参数
 as 
 begin
 select @avg = avg(grade) from sc,course where sc.cno = course.cno and cname=@cname
 //将执行结果的值赋值给输出参数
 end

 declare @return_avg int//申明要打印的参数

 exec produce1 'vb' ,@return_avg  output//后面的语句执行会将存储过程的输出值赋值给定义的变量然后打印，output不能少。

 print @return_avg

存储过程稍微复杂例子

create procedure proceduce2 (@sno char(7),@cno char(10))

as
begin
if((select count(*) from sc where sc.cno = @cno)>5)
 return  0
else
  begin
   if((select COUNT(*) from SC where Sno=@sno)>3)
   return -1//可以使用return标志执行结束
   else
   insert into SC(Sno,cno) values(@sno,@cno)
  return 1
 end
end

declare @out int
exec @out = proceduce2 '9512101','C05' //可以直接将存储过程的返回值赋值给变量
print @out