小白学语句：SQL笔记2

空值处理函数
isnull(expression,value):如果expression不为空则返回
expression，否则返回value。select isnull(Name,’匿名’) as姓
名from dbo_Person1

CASE函数用法
单值判断，相当于switch case
case expression
when value1 then returnvalue1
when value2 then returnvalue2
when value3 then returnvalue3
else defaultreturnvalue
end

例子
select Name,
(case FLevel when 1 then ‘VIP客户’
when 2 then ‘高级客户’
when 3 then ‘普通客户’
else ‘客户类型错误’
end) as FLevelName
from T_Customer

select Name
(case
when FSalary<3500 then ‘低收入’
when FSalary>=3500 and FSalary<5000 then ‘中等收入’
else ‘高收入’
end)as 收入水平
from dbo_Person1

练习1：
单号 金额
Rk1 10
Rk2 20
Rk3 -30
Rk4 -10
将上表格输出以下格式
单号 收入 支出
Rk1 10 0
Rk2 20 0
Rk3 0 30
Rk4 0 10
添加行
Insert into T_Text1(FNumber,FAmourt)Values(‘Rk2’,20)
Insert into T_Text1(FNumber,’FAmourt)Values(‘Rk3’,-30)
Insert into T_Text1(FNumber,FAmourt)Values(‘Rk4’,-10)
select FNumber as 单号,
(case when FAmourt>0 then FAmourt else 0 end)as 收入,
(case when FAmourt<0 then ABS(FAmourt) else 0 end)as 支出,
from T_Text1

练习2：
有一张表T_Scores,记录比赛成绩
Date Name Score
2018-9-11 拜仁 胜
2018-9-12 奇才 胜
2018-9-13 湖人 胜
2018-9-14 拜仁 负
2018-9-15 拜仁 负
2018-9-16 奇才 胜

要求输出以下格式
Name 胜 负
拜仁 1 2
奇才 2 0
湖人 1 0

添加表和数据

Create Table T_Scores(Date date null,Name nvarchar(50),Score nvarchar(10))
insert into T_Scores(Date,Name,Score)values(cast('2018-9-11'as datetime),N'拜仁',N'胜')
insert into T_Scores(Date,Name,Score)values(cast('2018-9-12'as datetime),N'奇才',N'胜')
insert into T_Scores(Date,Name,Score)values(cast('2018-9-13'as datetime),N'湖人',N'胜')
insert into T_Scores(Date,Name,Score)values(cast('2018-9-14'as datetime),N'拜仁',N'负')
insert into T_Scores(Date,Name,Score)values(cast('2018-9-15'as datetime),N'拜仁',N'负')
insert into T_Scores(Date,Name,Score)values(cast('2018-9-16'as datetime),N'奇才',N'胜')

首先定义如果是“胜”，那么输出1，否则输出0，定义为胜
然后定义如果是“负”，那么输出1，否则输出0，定义为负
select Name,
(case Score when N’胜’ then 1 else 0 end)as 胜,
(case Score when N’负’ then 1 else 0 end)as 负
from T_Scores

最后把每个名字下结果加起来sum
select Name,
SUM(case Score when N’胜’ then 1 else 0 end)as 胜,
SUM(case Score when N’负’ then 1 else 0 end)as 负
from T_Scores
group by Name

表连接Join
有客户表（T_Customers）和订单表（T_Orders）两个表，客户表字

段为：Id、Name、Age,订单表字段为：Id、BillNo、CustomerId,订

单表通CustomerId关联客户表
select o.BillNo,c.Name,c.Age
from T_Orders as o
join T_Customers as c on o.CustomerId=C.Id
要求显示所有年龄大于15岁的顾客购买的订单号、客户姓名、客户年龄
要求显示年龄大于平均年龄的顾客购买的订单
inner join 、Left join、Right join

添加表和数据

create table [T_Customers](
[Id] [int] not null,
[Name] [nvarchar](50) collate Chinese_PRC_CI_AS null,[Age] [int] null);
insert [T_Customers]([Id],[Name],[Age])values(1,N'Tom',10);
insert [T_Customers]([Id],[Name],[Age])values(2,N'Jerry',10);
insert [T_Customers]([Id],[Name],[Age])values(3,N'John',10);
insert [T_Customers]([Id],[Name],[Age])values(4,N'Lily',10);
insert [T_Customers]([Id],[Name],[Age])values(5,N'Lucy',10);

create table [T_Orders](
[Id] [int] not null,
[BillNo] [nvarchar](50) collate Chinese_PRC_CI_AS null,[CustomersId] [int] null);
insert [T_Orders]([Id],[BillNo],[CustomersId])values(1,N'001',1);
insert [T_Orders]([Id],[BillNo],[CustomersId])values(2,N'002',1);
insert [T_Orders]([Id],[BillNo],[CustomersId])values(3,N'003',3);
insert [T_Orders]([Id],[BillNo],[CustomersId])values(4,N'004',2);
insert [T_Orders]([Id],[BillNo],[CustomersId])values(5,N'005',2);
insert [T_Orders]([Id],[BillNo],[CustomersId])values(6,N'006',5);
insert [T_Orders]([Id],[BillNo],[CustomersId])values(7,N'007',4);
insert [T_Orders]([Id],[BillNo],[CustomersId])values(8,N'008',5);

建立两表的关系：
select o.BillNo ,c.Name,c.Age
from T_Orders as o join T_Customers as c on o.CustomersId=c.Id
–定义T_Orders的副名为o,定义T_Customers的副名为c，
o的CustomersId等于c的Id

–要求显示所有年龄大于15岁的顾客购买的订单号、客户姓名、客户年龄
select o.BillNo ,c.Name,c.Age
from T_Orders as o join T_Customers as c on o.CustomersId=c.Id
where c.Age>15