1、Case 子查询连接查询
create database demo
use demo
create table [user]
(
[uId] int identity( 1,1 ) primary key,
[name] varchar ( 50),
[level] int --1骨灰大虾菜鸟
)
insert into [user] ( name,level ) values (' 犀利哥 ',1 )
insert into [user] ( name,level ) values (' 小月月 ',2 )
insert into [user] ( name,level ) values (' 芙蓉姐姐 ',3 )
--case end 单值判断 相当于 switch case
--then 后面的返回值类型必须一致
select [name],
case [level]
when 1 then '骨灰 '
when 2 then '大虾 '
when 3 then '菜鸟 '
end as '等级 '
from [user]
use MySchool
select * from score
--case end 第二种用法，相当于多重 if 语句
select studentId,
case
when english >=90 then ' 优 '
when english >=80 and english <90 then ' 良 '
when english >=70 and english < 80 then ' 中 '
when english >= 60 and english < 70 then ' 可 '
else ' 差 '
end as '成绩 '
from score
order by english -- 表中有A B C 三列,用 sql 语句实现：当 A列大于 B 列时选择A 列否则选择 B 列，当B 列大于 C列时选择 B 列否则选择 C列。
select
case
when a > b then a
else b
end,
case
when b > c then b
else c
end
from T -- 练习
create table test
(
number varchar ( 10),
amount int
)
insert into test( number,amount ) values ( 'RK1',10 )
insert into test( number,amount ) values ( 'RK2',20 )
insert into test( number,amount ) values ( 'RK3',- 30 )
insert into test( number,amount ) values ( 'RK4',- 10 )
select number,
case
when amount > 0 then amount
else 0
end as '收入 ',
case
when amount < 0 then abs ( amount)
else 0
end as '支出 '
from test
--结果如下
use demo
CREATE TABLE student0 ( name nvarchar (10 ),subject nvarchar (10 ),result int )
INSERT INTO student0 VALUES (' 张三 ',' 语文',80)
INSERT INTO student0 VALUES (' 张三 ',' 数学',90)
INSERT INTO student0 VALUES (' 张三 ',' 物理',85)
INSERT INTO student0 VALUES (' 李四 ',92)
INSERT INTO student0 VALUES (' 李四 ',null)
select * from student0
select [name],
isnull (sum ( case subject
when ' 语文 ' then result
end ),0 ) as '语文 ',
isnull (sum ( case subject
when ' 数学 ' then result
end ),0 ) as '数学 ',
isnull (sum ( case subject
when ' 物理 ' then result
end ),0 ) as '物理 '
from student0
group by [name]
-- 被当作结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。
use myschool
select sName from ( select * from student ) as t
select 1,( select sum ( english) from score ) as ' 和 ',( select avg ( sAge) from student ) as ' 平均年龄 '
-- 查询高一一班所有的学生
select * from student where sClassId =
( select cId from class where cName = '高一一班 ' )
-- 查询高一一班 高二一班所有的学生
-- 子查询返回的值不止一个。当子查询跟随在 = 、!= 、 <、 <= 、> 、 >= 之后
-- 子查询跟在比较运算符之后，要求子查询只返回一个值
-- 如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合。可以使用 in 关键字代替 =号
select * from student where sClassId =
( select cId from class where cName in ( '高一一班 ',' 高二一班 '))
select * from student where sClassId in
( select cId from class where cName in ( '高一一班 ',' 高二一班 '))
-- 查询刘关张的成绩
select * from score where studentId in
( select sId from student where sName in ( '刘备 ',' 关羽 ',' 张飞' ))
-- 删除刘关张
delete from score where studentId in
( select sId from student where sName in ( '刘备 ',' 张飞' )) -- 实现分页
-- 最近入学的个学生
select top 3 * from student
order by sId desc
-- 查询第到个学生
select top 3 * from student
where sId not in ( select top 3 sId from student order by sId desc)
order by sId desc
-- 查询到的学生
select top 3 * from student
where sId not in ( select top 6 sId from student order by sId desc)
order by sId desc
-- 上面是sql 2000 以前的实现方式。 sqlServer2005 后增加了Row_Number 函数简化实现。
--sql 2005 中的分页
select * from
( select row_number () over (order by sId desc ) as num,* from student ) as t
where num between 1 and 3
select * from
( select row_number () over (order by sId desc ) as num,* from student ) as t
where num between 4 and 6
select * from
( select row_number () over (order by sId desc ) as num,* from student ) as t
where num between 7 and 9
select * from
( select row_number () over (order by sId desc ) as num,* from student ) as t
where num between 3 *( 3- 1 ) + 1 and 3 *3
-- 表连接
-- 交叉连接cross join
select * from student
cross join class
-- 内连接inner join...on...
select * from student
inner join class on sClassId = cId
select * from class
-- 查询所有学生的姓名、年龄及所在班级
select sName,sAge,cName,sSex from student
inner join class on sClassId = cId
where sSex =' 女 '
-- 查询年龄超过岁的学生的姓名、年龄及所在班级
select sName,cName from class
inner join student on sClassId = cId
where sAge > 20
-- 外连接
--left join...on...
select sName,cName from class
