--子查询:就是把一个查询结果作为另一个查询的数据源
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