11-12笔记

--子查询：就是把一个查询结果作为另一个查询的数据源
SELECT * FROM MyStudents
--－－（1） 独立子查询
SELECT * FROM
(select FName,FAge,FGender
from MyStudents
where FAge<=24 and FAge>=18) as Tbl1
where FGender='女' and FName LIKE '张%'
--
select
 最高分=(select MAX(FMath) from MyStudents),
 最低分=(select MIN(FMath) from MyStudents),
 平均分=(select AVG(FMath) from MyStudents)
----查询高二二班的所有学生,学生姓名\性别\学号...
Use School
select * from Student
select * from Class
--在where中 应用子查询
select * from Student
where sClassId=
(select clsId
from Class where cName='高二二班')
--相关子查询
select * from Student
where
exists(select *
from Class
where cName='高二二班' and Class.clsId=Student.sClassId)

 

----查询高二二班和高二一班的所有学生,学生姓名\性别\学号...
select * from Student
where sClassId in
(select clsId
from Class where cName='高二二班' or cName='高二一班')
select * from Student
--查询刘备、关羽、张飞的成绩
select * from Score

select * from Score
where studentId IN
(Select sId from Student where sName in('刘备','关羽','张飞'))

select * from Score
where studentId IN
(Select sId from Student where sName='刘备' or sName='关羽' or sName='张飞')
--删除刘备、关羽、张飞的成绩
delete from Score
where studentId in
(Select sId from Student where sName='刘备' or sName='关羽' or sName='张飞')
--子查询可以应用在select中之外，还可以应用在delete、update中。
--exists 判断其当中的查询语句是否有数据返回，如果有其值为真，否则为假
--T-Sql
if(exists(select * from Student))
begin--{
   print '有结果'
end--}
else
begin--{
   print '没结果'
end--}
--三\分页
use Test
select * FROM  MyStudents
--分页，每页5条记录
--前提：将记录排序
select * from MyStudents
order by FId
 DESC
--第一页
select top 5 * from MyStudents
order by FId
--第二页
select top 5 * from MyStudents
where FId not in
(select top 5 FId from MyStudents
order by FId )
order by FId
--第五页
select top 5 * from MyStudents
where FId not in
(select top (5*4) FId from MyStudents
order by FId )
order by FId
---以上这种方法是sql2000以及之前的版本用的方法
---在sql2005中 就 有了一个函数 row_Number

select * from MyStudents

delete from MyStudents where FId in(3,6,13,15,22)
--如果能给表添加一列,这列中放着记录的序号这样再分页就可以按这个序号来计算:
--假设查询第7页的数据(每页5条记录)
--5*6+1 ----31为第7页第1条数据的序号
--5*7-------35为第7页第最后1条数据的序号
---查询第十页的数据
--5*9+1
--5*10

--5*2+1
--5*3

select
*,
ROW_NUMBER()over(order by FId asc) as Rnumber
from MyStudents

--第七页

select * from
(
select *,
ROW_NUMBER()over(order by FId asc) as Rnumber
from MyStudents
)
as Tbl3
where Rnumber between (4*5+1) and 4*6
--------------开窗函数OVER(),与聚合函数一起使用时，可以给每条记录一个聚合的值。

select *,COUNT(*) over() as 总记录条数
 from MyStudents
 
 --查询系统中所有的数据库
 select * from sys.databases
 --我们在新建数据库的时候,是不是应该判断一下要建的数据库是否存在.
 --select * from sys.databases where name='School'
 if(exists( select * from sys.databases where name='Schoolxxx'))
 begin
   print '存在'
 end
 else
 begin
   print '不存在'
 end
 
 --------四表联结(Jion)---------------------------
 --Union什么啊？将两个表进行联合。联结的是 行（记录）。
 --使用join联结的是列（字段）
 --查询所有学生的姓名、年龄和班级
 use School
 select * from student
 select * from Class
 select
   sName,
   sAge,
   cName
   from Student as TS
   inner join Class TC
   on TC.clsId=TS.sClassId
   --当要查询的多个列在不同的表中，进行跨表查询使用inner join/

--使用子查询(相关子查询)完成以上例题
select
sName,
sAge,
班级名称=
(select cName from Class where Class.clsId=Student.sClassId)
from Student

--使用inner join实现的查询,就能用子查询,exists.但是反过来不成立.
--查询年龄超过20岁的姓名\年龄\所在班级

select
   sName,
   sAge,
   cName
   from Student as TS
   inner join Class TC
   on TC.clsId=TS.sClassId
   where sAge>20
  
   --查询学生姓名\年龄\班级\成绩
   select * from Score
   SELECT * FROM Student
select
   sName,
   sAge,
   cName,
   english,
   math
   from Student as TS
   inner join Class TC on TC.clsId=TS.sClassId
   inner join Score TSC ON TSC.studentId=TS.sId
--
delete from Score
WHERE studentId=1 OR studentId=4

--查询出所有参加考试的同学的学生编号，姓名，考试成绩。
use School

select
sId,
sName,
english,
math
from Student
inner join Score on Score.studentId=Student.sId
--查询出所有没有参加考试的同学的学生编号，姓名，考试成绩。
--笛卡尔积
--1
select
sId,
sName,
english,
math
from Student
inner join Score on Score.studentId<>Student.sId
--2
select
sId,
sName,
english,
math
from Student
inner join Score on 1=1
-----------------------------------
--使用子查询实现命题查询出所有没有参加考试的同学的学生编号，姓名。
select
sId,
sName
FROM Student
  WHERE sId not in (select studentId FROM Score)

 

 

select
sId,
sName
from Student
inner join Score on Score.studentId<>Student.sId
--得到7*5-5=30条记录
select * from Student as TS
INNER JOIN Score ON 1=1
--得到7*5=35条记录
--笛卡尔集
--------------------------------------------
USE School
select
sId,
sName
FROM Student
  where sId not in(select studentId from Score)
  --1内连接（两个表中相匹配的那些记录）
  --2外联接
  --2.1左外联接(把左表中 left join关键字左边的表)中的全部记录都显示出来，对于右表中能找到匹配的记录，显示对应匹配数据，对右表中找不到的匹配记录显示为null。
    --2.2右外联接 left [outter] join,right [outter] join.
    --左外联接和右外联接都是分两步查询出结果的,第一:找到匹配数据,第二:填充不匹配的数据为null。（注意，是有先后顺序的。）
   
    ----Cross Join交叉联接------
    select
      *
    from Student,Score
   
    ----自联接------------------
   
    select
    *
    from Student
    inner join
    (select sName,sAge,sId FROM Student
    where sAge>10
    )as TS1
    ON Student.sId=TS1.sId
   
   -- -7----report 2------
   
   
   
  SELECT
     *
     FROM 
    (select
       sId,
       sName,
       sAge,
       Score.*
    from Student
       left outer join Score
       ON Score.studentId=Student.sId
       ) as Tbl
       WHERE ScoreId is null
 
 ---右外--
 select
       sId,
       sName,
       sAge,
       Score.*
    from Student
       right outer join Score
       ON Score.studentId=Student.sId
 --- ---------------
 select
       sId,
       sName,
       sAge,
       Score.*
    from Score
       right outer join Student
       ON Score.studentId=Student.sId
 
  select
  sId,
  sName,
  Score.*
  FROM Student
  LEFT OUTER JOIN Score
  on Score.studentId=Student.sId
  --重做：查询出所有没有参加考试的同学的学生编号，姓名，考试成绩。
   select
  sId,
  sName,
  Score.*
  FROM Student
  LEFT OUTER JOIN Score
  on Score.studentId=Student.sId
  where Score.scoreId is null
  --2.2右外联接
  select
    Score.*,
    sId,
    sName
    from Student
  right outer join Score on
  Score.scoreId=Student.sId
  ------------
  select
    Score.*,
    sId,
    sName
    from Score
  right outer join Student on
  Score.scoreId=Student.sId
  ----Cross Join 交叉联结-------
  select * from
  Student,Score
  -------自联结------------------
  select *
  from Student
  inner join(select sId,sName,sAge from Student where sAge>=10)as Tbl1
  on Student.sId=Tbl1.sId
  --report to
 
 
--  练习1：查询所有英语及格的学生姓名、年龄及成绩
select * FROM Student
SELECT * FROM Score
insert into Score (studentId,math)values(10,99)
insert into Score (studentId,math)values(11,100)

INSERT INTO Student VALUES(5,'貂蝉',18,'女',1211312,'1994-01-01')
INSERT INTO Student VALUES(4,'吕布',20,'男',1211312,'1992-01-01')

select
 sName,
 sAge,
 english
 from Student
 inner join Score
 ON Score.studentId=Student.sId
 WHERE english>=60

--练习2：查询所有参加考试的(english分数不为null)学生姓名、年龄及成绩

select
sName,
sAge,
english,
math
from Student
inner join Score
ON Student.sId=Score.studentId
WHERE english IS NOT NULL

 

 

--练习3：查询所有学生(报考的和未报考的)的学生姓名、年龄、成绩，如果报考了，但是没有参加考试显示缺考,如果小于english&math小于60分显示不及格，如果没有报考显示没有报考（添加两列 ，“是否报考”，“是否合格”）
SELECT * FROM Score
select * from Student
select
 sName,
 sAge,
 --scoreId,
 case
   when english IS null then '缺考'
   else convert(varchar(50),english)
 end as english
 ,
 math=case
   when math IS null then '缺考'
   else convert(varchar(50),math)
 end ,
 是否报考=
     case
         when scoreId IS null then '未报考'
         else '以报考'
     end,
 是否合格=
     case
         when english>=60 and math>=60 then '合格'
         else '不合格'
      end
 from Student
 left join Score
 on Score.studentId=Student.sId
 ---临时表---
 create table #MyStudents
 (
 sName nvarchar(50),
 sAge int
 )
 insert into #MyStudents
 select FName,FAge from MyStudents
 
 select * from #MyStudents

 delete FROM #MyStudents
-- where sName='康凯' or sName='李昂' or sName='李辰'
  where sName in('康凯' ,'李昂' ,'李辰')
  ---
  create table ##MyStudents
 (
 sName nvarchar(50),
 sAge int
 )
 insert into ##MyStudents
 select FName,FAge from MyStudents
 
 select * from ##MyStudents
 
 ------表变量：---
 declare @varTbl table(col1 int,col2 varchar(30))
 insert into @varTbl values(100,'A')
  insert into @varTbl values(101,'B')
 select * from @varTbl
 ---视图---------------------------------------------------
alter view vw_StudentScore
as
select * from Student
 
 select * from vw_StudentScore
 
 update Student set sName='山西关羽' where sName='关羽'
 --我们可以把很长很长的查询语句,放在一个视图中,这样我们在要得到长长的查询语句的结果时候,就可以,不在执行这段语句,而是查询一下相应的视图即可.
 --视图类似于一个表,这个表和临时表不同,他不会在会话结束后释放掉,每次都能用.
 --视图中不存放数据，视图中只存放查询。
 --视图的每一列都要有列名。
 use School
 create view vw9
 as
   select
     sName,
     case
        when sAge>13 and sAge<16 then '豆蔻年华'
        when sAge>=50 then 'oldman'
        else '青壮年'
      end as 生存状态
     from Student
    
    
     select * from vw9
     --drop view vw9
     --alter view vw9
     --as
     --create table test
     --(
     --A int,
     --B int
     --)
    
     create view vw11
     as
     select top  3 *
     from Student
     order by sAge DESC
    
     SELECT * FROM VW11
     SELECT * FROM Student
   --出错！ TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
   --order by...成了游标，不能作为数据源！
 
     CREATE VIEW VW12
     AS
     SELECT * FROM Student
     ORDER BY sAge DESC
     ---------------------
      CREATE VIEW VW12
     AS
     SELECT * FROM Student
    
     select * from vw12 ORDER BY sAge DESC
     -----------------------------------------
      CREATE VIEW VW13
     AS
     SELECT top 100 percent * FROM Student
     ORDER BY sAge DESC
    
     select * from vw13
     ----!!!order by 后，必须有 top才能作为视图的查询语句
     --##注意，在子查询和视图中都不能有order by，除非。。。。
    ---索引视图（*）
create view vw_ix_T1 WITH SCHEMABINDING
as
select autoid,uname,usrId from dbo.T1

create unique clustered index ix_vw_t1 on vw_ix_T1(autoId)
---=-------------------------------------------------

--一、在T—Sql中使用变量----
--（一）局部变量-----------------------------
--1声明变量
declare @name varchar(20)
declare @age int
---2赋值
--(1)方法1
--set @age=18
--set @name='yqq'
--(2)方法2
select @age=27
select @name='ydx'

print @age
print @name
------使用set和select 为变量赋值的区别-----
declare @rcount int
--set @rcount=(select COUNT(*) from Student)--先查询，后赋值
select @rcount=COUNT(*) from Student--即查询有赋值
print @rcount
select top 5 sAge from Student

declare @sAge int
----------------------------------
--SET @sAge=(select sAge from Student)--出错！子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

select  @sAge= sAge from Student--当查询返回多值的时候，是把 最后一个值赋值给变量。
select @sAge
-------------------------------
declare @Fage int ---只定义，没有赋值，因此@Fage为null
set @Fage=@Fage+1
select @Fage--这里输出的就是null
-----------
declare @userName varchar(10)
declare @userAge int
set @userAge=18
set @userName='hb'
--select @userAge,@userName
print @userName+cast(@userAge as varchar(10))
print @userName+cast(@userAge as varchar(10))
--print 输出的是某一种数据类型的数值
-------------------------------------------------
--------(二)全局变量----------
select * from Student
insert into Student (sName,sAge,sSex,sNo)values('韩根',33,'男',23231131)
print @@identity--
print @@version
print @@language
print @@servername
print @@error--如果返回值为0，说明上一条语句没有错误！如果上一条语句出错，返回错误消息序号。
print 'hello gay!'+222
print @@error
print @@error
---IF ELSE 条件语句---
declare @Hage int
set @Hage=50
if @Hage>10
  begin
     print '长大了！'
  end
else
  begin
     print '还小！！'
  end
select * from Score
--计算score表中english平均成绩，如果〉=60，查询正数前三名，否则找出倒数前三名。
select avg(ENGLISH) FROM Score
--使用isnull()函数计算平均值
 select avg(isnull(ENGLISH,0))
 FROM Score
--练习使用变量计算平均值
declare @count int
select @count=COUNT(*) from Score
declare @sum float
select @sum=SUM(ENGLISH) FROM Score
declare @avg float
set @avg=(@sum/@count)
if @avg>=60
   begin
      select top 3 * from Score order by english desc
   end
else
   begin
      select top 3 * from Score order by english asc
   end
  
 ------三、while 循环
 --通过while 计算1---100之间奇数的和。
 declare @sum2 int =0
 declare @i int =1
 
 while @i<=100
   begin
      if @i%2<>0
     begin
      set @sum2=@sum2+@i
     end
     set @i=@i+1
   end
   print @sum2
  --作业：通过while 计算1---100之间偶数的和。
  --例题：如果english不及格的人超过半数，则给每个人增加2分,循环加，直到不及格的人数少于一半。
 
  --1,计算总人数
  declare @count2 int =(select COUNT(*)from Score)
  --2,计算英语不及格人数
  declare @loster int=(select COUNT(*)from Score where english<60)
 
  declare @harfcount int=floor(@count2/2)
  while @harfcount<@loster
    begin
       update Score set english=english+2 where english<60
       set @loster=(select COUNT(*)from Score where english<60)
    end
 ----------五.事务
 --转账问题：
create table Bank
(
  Cid char(4) primary key,
  balance money
)
alter table Bank
add constraint CH_balance check (balance>=10)

insert into Bank values('0001',1000)
insert into Bank values('0002',10)

delete from Bank

--假设要从0001账户转1000块到0002账户
update Bank set balance=balance-1000 where Cid='0001'
update Bank set balance=balance+1000 where Cid='0002'
--事务，就是把一系列操作作为一件事处理，要么都完成，要么都不完成！
--begin transaction--打开事务
begin tran--打开事务
begin try
declare @errorSum int =0
update Bank set balance=balance-900 where Cid='0001'
set @errorSum=@errorSum+@@error
update Bank set balance=balance+900 where Cid='0002'
set @errorSum=@errorSum+@@error
commit
print '提交！！'

end try
begin catch
   rollback
   print '回滚！'
end catch

--if @errorSum=0
--  begin
--  commit tran
--  print '提交！！！'
--  end
--else
--  begin
--  rollback
--  print '回滚！！！'
--  end
 

commit tran--提交事务
rollback tran--回滚事务
-----'自动提交事务'：系统检测sql语句是否出错，如果没有错误就自动提交--
insert into Bank values('0003',1000)
--如果希望手动提交，可以回滚在执行插入等操作的时候：
begin tran--打开事务
insert into Bank values('0004',4200000)
rollback--手动回滚或提交事务
--commit
--"隐式事务"：默认情况为关，如果打开了则不自动提交，学要手动提交。
set implicit_Transactions on
delete from Bank
rollback
select * from Bank
--如果隐式事务打开，然后删除某个表，在事务没有结束前，其它查询不能访问该表。
--这就是“锁”，由于只是执行了一个sql的操作，没有结束事务，就会把表锁住，不让别人在对他进行操作。放置并发问题出现。
set implicit_Transactions off

----可以给事务起名，当多个事务存在时用以区分。
begin tran tran1
rollback tran1
commit tran1

--如果没有打开的事务，执行rollback时，就会报错。

--六、存储过程！！
--与C#中的方法一样。存储过程 有 名/可以有参数/可以有返回值。

--6.1系统存储过程
--放在系统数据库 master 中的--可编程性---存储过错--系统存储过程中。
  --select * from sys.databases
  exec sp_databases
  ----sp_databases的 内部 代码
   select
        DATABASE_NAME   = db_name(s_mf.database_id),
        DATABASE_SIZE   = convert(int,
                                    case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...
                                    when convert(bigint, sum(s_mf.size)) >= 268435456
                                    then null
                                    else sum(s_mf.size)*8 -- Convert from 8192 byte pages to Kb
                                    end),
        REMARKS         = convert(varchar(254),null)
    from
        sys.master_files s_mf
    where
        s_mf.state = 0 and -- ONLINE
        has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
    group by s_mf.database_id
    order by 1
---使用 sp_helptext 来显示一个 存储过程 中的 代码 。
exec sp_helptext sp_databases
exec sp_helptext sp_helptext
--给数据库重命名：
exec sp_renamedb 'Test','Test123'
--查询当前数据库中有多少个表
exec sp_tables
--查询当前数据库中的列信息
exec sp_columns 'Student'
-------------------------------------------------
--以上是 几个 系统的存储过程：以sp_ 开头，exec
--自定义存储过程一般是以 usp_开头
create proc usp_Helloworld
as
 begin
   print 'hello world!'
 end
 
 exec usp_Helloworld
 --创建一个计算2个数的和的存储过程
 
 
 
 
 
 
 
 
 
   select * from Score  
   update Score set english=69 where english is null  
  update Score set english=22 where scoreId=9 
  ---7个人一半按3算，7/2向下取正。
print floor(7/2.0)
print ceiling(7/2.0)
print 7/2