T-SQL查询学习 及 语言元素

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;