T-SQL 查询学习及语言元素
一、select
1、select 常规
表示例（学生表）student1：
检索行和列：
select * from student1
select 搭配列计算：
select sname,age+1 as age_update from student1
结果：
2、 使用 SELECT INTO 创建表
创建临时表：
select *
into #temp_student1
from student1
where grade='grade1';
-- into 后面 表要加#号（那么，临时表生命周期什么时候结束？）
创建永久表：
select *
into student1_grade1
from student1
where grade='grade1';
-- into 后面 表不加#号
3、多表查询，查询效率
给定两个表
学生表student1：
成绩表：
查询Tom的语文成绩：
-- 联接查询
select score
from student1,score
where student1.sno = score.sno
and student1.sname='Tom'
and score.course='语文'
-- 返回 78.5
-- 嵌套查询（ in 谓词）（嵌套查询 效率更高）
select score
from score
where course='语文'
and sno in (select sno from student1 where sname = 'Tom') -- 括号里的select查询是【相关子查询】
-- 返回 78.5
-- 其中 in 是谓词，还有 exists（存在） ，all，some，unique
-- 嵌套查询 （exists 谓词）
select score
from score s1
where course='语文'
and exists ( select *from student1 s2 where s1.sno = s2.sno and sname='Tom') ’
-- 返回 78.5
-- 这里的 子查询 不能脱离主查询 单独运行，它用到了主查询的表 s1
4、having()子句
功能：指定搜索条件
例子：
-- 查 平均成绩70以上(having)的学生的学号，姓名,平均分
select t.sno,sname,t.avg_score from (
select t.sno,cast(avg(score) as decimal(3,1)) as avg_score
from score t
group by sno
having avg(score)>=80
) t
left join student1 on student1.sno = t.sno
结果：
5、OVER（）子句
例子：还用上面的例子，我要选出，每个年级grade（要分区），语文最高分（要排序）的，姓名和分数
student1表：
成绩表score：
代码：
select t.sname,t.score
from (
select s1.sname,s1.grade,score,ROW_NUMBER () over(partition by s1.grade order by score desc) as row_num
from student1 s1
inner join score s2
on s1.sno = s2.sno
and s2.course='语文') t
where t.row_num = '1'
-- 分两步走，姓名和分数在不同的表，肯定是要先联接的（inner join），联接的同时，筛选一下“语文”，按分数排一下行号（row_number())
-- 然后外层一个select 把想要的筛选出来
-- 有更简单的写法，欢迎留言探讨。
结果：
例子：我想按年级（分区），计算数学平均分（avg），结果列出，年级和平均分
代码：
select s1.grade ,cast (avg(s2.score) as decimal(3,1))
from student1 s1
inner join score s2
on s1.sno = s2.sno
and s2.course='数学'
group by s1.grade
-- Oops ！ 好像没用到 over
结果：
6、from子句
先列出两个表情况（表中分别有另一个表没有的学生号）：
student2：
score2：
- inner join
-- 查询学生表student2里 ，有语文成绩(score2)的学生的，学号，姓名和成绩
select t1.sno,t1.sname,t2.score
from student2 t1
inner join score2 t2
on t1.sno = t2.sno
where t2.course = '语文'
-- 结果中没有1004 ，也没有1005，inner join 是取on子句中两个表的sno的交集
result：
- left join
-- 查student2表中所有学生的学号，姓名，课程，成绩
select t1.sno,t1.sname,t2.course ,t2.score
from student2 t1
left join score2 t2
on t1.sno = t2.sno
-- 结果中 student2表中所有人都列出来，1004 在成绩表中没成绩，也列出来
-- 这里不能再限定 where子句了，否则 1004学生 也显示不出来了
result：
- left join 加上where子句
select t1.sno,t1.sname,t2.course ,t2.score
from student2 t1
left join score2 t2
on t1.sno = t2.sno
WHERE t2.sno is NULL
-- 结果中只有 1004 学生 ，相当于 找出在student2表中 而不在score表中的元素，也可以用except关键字
reuslt:
- except
select sno from student2
EXCEPT
select sno from score2
-- 结果是 1004
-- 用 excpet 来查 ，在A表 不在B表的问题
- right join
-- 把成绩表中所有成绩显示出来，在student2中没找到学号 姓名的，会显示NULL
select t1.sno,t1.sname,t2.course ,t2.score
from student2 t1
right join score2 t2
on t1.sno = t2.sno
reuslt：
- full outer join
select t1.sno,t1.sname,t2.course ,t2.score
from student2 t1
full outer join score2 t2
on t1.sno = t2.sno
result:
-- 查在A中不在B中 和 ，在B中 不在A中的 元素
select t1.sno,t1.sname,t2.course ,t2.score
from student2 t1
full outer join score2 t2
on t1.sno = t2.sno
where t1.sno is NULL
or t2.sno is NULL -- 这里用or 不用and
-- 结果中把1004 和 1005 查出来了
result：
二、语言元素
1、控制流
- （1）begin...end
功能：包括一系列t-sql语句，可以执行一组t-sql语句。
declare @iteration integer = 0;
while @iteration < 3
begin
select * from student1 where sno='1001';
set @iteration += 1;
end
-- while循环，如果没有 begin...end ，这段语句将会循环查下去，相当于循环的是select 。
- （2）break , while ,waitfor
-- break
DECLARE @myvar int
set @myvar = 0
while @myvar<5
begin
print 'begin end 里面';
if @myvar = 3 break; -- break 通常用再if语句中
set @myvar+=1;
WAITFOR delay '00:00:10' -- waitfor 延迟10s ，还可以到某个时间触发，比如，waitfor time '11:32:23'
end
-- while 循环 只针对下面的一句代码，或者 begin end 代码段
- （3）if...else
if DATENAME(WEEKDAY,GETDATE()) in (N'Saturday',N'Sunday')
SELECT 'Weekend'; --select 的结果显示在 results界面框，print结果显示在 messages界面框
ELSE
select 'Weekday';
- （4）try...catch
BEGIN TRY
SELECT 1/0; -- 搞个0 做除数的例子
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber -- 显示 错误代码
,ERROR_MESSAGE() AS ErrorMessage; -- 显示 错误信息
END CATCH
结果：
- （5）case 语句
筛选出Jerry同学的成绩，成绩大于等于80的标明“优秀”，大于等于60的标明“良好”，剩下的标“差劲”
select t1.sno,t2.sname,t1.course, score, 'Level' =
case
when t1.score >= 80 then '优秀'
when t1.score >= 60 and t1.score < 80 then '良好'
else '差劲'
END
from score t1
inner join student1 t2
on t1.sno = t2.sno
and t2.sname = 'Jerry';
result：
2、事务 begin transaction,commit transaction, rollback transaction
功能：标记一个事务开始， @@trancount 值要加1
显式事务：
begin transaction;
insert into student1 values('1004','Tyler','grade1','女','19');
commit;
-- 打开一个显式事务，然后commit，每commit一下，@@trancount 就减少1，rollback 一下，@@trancount 直接减到0
回滚事务：
begin transaction;
delete from student1 where sno = '1004'
rollback;
-- 手工回滚事务，begin 和 rollback 中间的所有操作都无效
命名事务：
declare @tranname varchar(50);
select @tranname = 'mytransaction';
begin transaction @tranname;
update student1 set age='18' where sno='1004'
commit transaction @tranname;
