SQL Server高级内容之子查询和表链接概述及使用

1.子查询概念 (1)就是在查询的where子句中的判断依据是另一个查询的结果,如此就构成了一个外部的查询和一个内部的查询,这个内部的查询就是自查询。 (2)自查询的分类 1)独立子查询 -独立单值(标量)子查询(=) 复制代码 代码如下: Select testID,stuID,testBase,testBeyond,testPro from Score where stuID=( select stuID from Student where stuName='Kencery' ) -独立多值子查询(in) 复制代码 代码如下: Select testID,stuID,testBase,testBeyond,testPro from Score where stuID in( select stuID from Student where stuName='Kencery' ) 2)相关子查询 (3)写子查询的注意事项 1)子查询用一个圆括号阔气,有必要的时候需要为表取别名,使用“as 名字”即可。 2.表连接 (1)表链接就是将多个表合成为一个表,但是不是向union一样做结果集的合并操作,但是表链接可以将不同的表合并,并且共享字段。 (2)表连接之交叉连接 1)创建两张表 复制代码 代码如下: use Test go create table testNum1 ( Num1 int ); create table testNum2 ( Num2 int ); insert into testNum1 values(1),(2),(3) insert into testNum2 values(4),(5) 2) 执行交叉连接的SQL语句 select * from testNum1 cross join testNum2 3)注解 交叉连接就是将第一张表中的所有数据与第二张表中的所有数据挨个匹配一次,构成一个新表。 4)自交叉的实现 执行插入SQL语句: 复制代码 代码如下: insert into testNum1 values(4),(5),(6),(7),(8),(9),(0) 执行自交叉的SQL语句: 复制代码 代码如下: select t1.num1,t2.num2 from testNum1 as t1 cross join testNum2 as t2 5)另外一种写法: select * from testNum1,testNum2不提倡使用,首先是有比较新的语法,缺陷是逗号不明确,并且这个语法与内连接和外连接都可以使用,如果使用join声明,那么语法错误的时候可以报错,但是使用这个语法,可能因为部分语法的错误,会被SQL Server解释为交叉连接而跳过这个语法的检查 (3)表连接之内连接 1)内链接是在交叉连接的基础之上添加一个约束条件 2)语法:select * from 表1 inner join 表2 on 表1.字段=表2.字段 复制代码 代码如下: Selects1.stuID, s1.stuName, s1.stuSex, s2.testBase, s2.testBeyond from Student as s1 inner join Score as s2 on s1.stuID=s2.stuID where s1.stuIsDel=0; (4)表连接之外连接 1)执行下面的SQL语句 复制代码 代码如下: create table tblMain ( ID int, name nvarchar(20), fid int ); create table tblOther ( ID int, name nvarchar(20) ) insert into tblMain values(1,'张三',1),(2,'李四',2) insert into tblOther values(1,'C '),(2,'.net'),(3,'java') select * from tblMain as t1 inner join tblOther as t2 on t1.fid=t2.id 2)在内连接的基础之上,在做一件事儿,就是将tblOther中的Java也显示出来,这时候就要使用到外连接,外连接有左外连接和右外连接。 3)左连接和右连接有什么区别呢??区别就是**连接就是以**表为主表,在内连接的基础之上,将没有数据的那张表的信息还是要显示出来供用户查看,那么这个主表就是要显示的那张表。左外连接和右外连接的分别是在前面的这张表就是左表,在后面的那张表就是右表,左连接使用left join ,有连接使用right join。 4)上面重新执行下面的SQL语句,就会显示出tblOther表中的Java。 复制代码 代码如下: select * from tblMain as t1 right join tblOther as t2 on t1.fid=t2.id

select语句
语法:select distinct | top 数字 [percent] 字段1 as 别名 ,包含字段表达式,函数,常量
from 表或结果集
where 逻辑条件 | 模糊处理 | 范围处理 | null值处理
group by 分组字段
having 筛选条件
order by 排序依据;

执行流程:
from子句 -> where子句 ->group by子句 ->having子句 ->select子句 ->order by子句

-- 名字
-- 作用(例子)
-- 语法

-- 子查询
-- 就是在一个查询中嵌套一个查询
-- 一般作用就是利用多张表查询一个信息
-- 例如查询"濮阳语儿"的成绩
select * from TestDataBase..Student;
select * from TestDataBase..Score;
-- 在学生表中查得stuId,然后再到分数表中查询分数
select stuId from TestDataBase..Student where stuName ='濮阳语儿';

select * from TestDataBase..Score where stuId = 5723;

-- 外部查询
select *
from TestDataBase..Score
where stuId in
( -- 子查询、内部查询
select stuId from TestDataBase..Student where stuName ='濮阳语儿'
);
-- 外部查询(子查询)

-- 将一个查询的结果作为另一个查询的条件

-- 考试成绩与课程查出来 Course
select * from TestDataBase..Course;

select className from TestDataBase..Course where classId in
(
select top 1 classId from TestDataBase..Student where stuName='濮阳语儿'
);

-- 多个单值 外部查询 where 字段 in (子查询)
select '濮阳语儿' , (select className from TestDataBase..Course where classId in
(
select top 1 classId from TestDataBase..Student where stuName='濮阳语儿'
));

-- 表值 select * from (子查询) as 别名
select * from (
select stuName, case stuSex when 'f' then '女' else '男' end as stuSex, DATEDIFF(YEAR, stuBirthdate, GETDATE()) as stuAge from TestDataBase..Student where stuId <= 10
) as t
where t.stuAge between 20 and 30;

--
-- 员工编号 基本工资 请假扣款 补贴 绩效奖金 项目奖金 社保扣款
/*
select
来自员工表的查询
, 来自工资级别表的查询
, 考勤表的查询
... ...
*/

-- 独立子查询(标量、多值)

-- 相关子查询
-- 查询濮阳语儿的三科平均分
select AVG(testBase), AVG(testBeyond), AVG(testPro) from TestDataBase..Score where stuId = (select top 1 stuId from TestDataBase..Student where stuName='濮阳语儿');

select
stuName
, (select AVG(TestBase) from TestDataBase..Score where stuId = t.stuId) as 基础平均分
, (select AVG(testBeyond) from TestDataBase..Score where stuId = t.stuId) as 中级平均分
, (select AVG(testPro) from TestDataBase..Score where stuId = t.stuId) as 高级平均分
from
TestDataBase..Student as t
where
stuName = '濮阳语儿';


use HeiMa8;

create table Score
(
学号 nvarchar(10),
课程 nvarchar(10),
成绩 int
)

insert into Score values('0001','语文',87);
insert into Score values('0001','数学',79);
insert into Score values('0001','英语',95);
insert into Score values('0002','语文',69);
insert into Score values('0002','数学',84);

case表达式:
--if-else结构
case
when 条件1 then 值1
when 条件2 then 值2
。。。
else 值n
end

--switch-case结构
case 字段
when 匹配1 then 值1
when 匹配2 then 值2
。。。
else 值n
end
Set statistics io on;--打开监视磁盘IO操作
Set statistics time on;

select * from Score;
-- 分组
select 学号, '语文', '数学', '英语' from Score group by 学号;
--第一种结构示例:switch--case
select
学号
, case when 课程='语文' then 成绩 else 0 end as '语文'
, case when 课程='数学' then 成绩 else 0 end as '数学'
, case when 课程='英语' then 成绩 else 0 end as '英语'

from Score

select
学号
, sum(case when 课程='语文' then 成绩 else 0 end) as '语文'
, sum(case when 课程='数学' then 成绩 else 0 end) as '数学'
, sum(case when 课程='英语' then 成绩 else 0 end) as '英语'
from Score
group by 学号;

第二种结构示例:if--else
select
sum(case when T.充值金额>=500 then T.充值金额 end) as '鲸鱼用户'
,sum(case when T.充值金额>=100 and T.充值金额<500 then T.充值金额 end) as '海豚用户'
,sum(case when T.充值金额>=10 and T.充值金额<100 then T.充值金额 end) as '小鱼用户'
from
(
select [ChannelUserKey] as 用户ID,sum(convert(float,[RechargeAmount])/100) as 充值金额,sum([RechargeCount]) as 充值用户
from [dbo].[FactRecharge]
where datekey>=20141201 and datekey<=20141210
and ChannelKey=1
group by [ChannelUserKey]
) T


-- 透视变换

select * from Score pivot(
sum(成绩) for 课程 in (语文,数学,英语)
) as t


-- 表连接
-- 作用:将多张表变成一张表
-- 用法与分类(案例)
-- 分类:交叉连接、内连接、外连接

create table joinPerson
(
pId int identity(1,1) not null
, pName nvarchar(10) not null
, titleId int null
);
alter table joinPerson
add constraint PK_joinPerson_pId primary key(pId);

create table joinTitle
(
titleId int identity(1,1) not null
, titleName varchar(10) not null
);
alter table joinTitle
add constraint PK_joinTitle_titleId primary key(titleId);

insert into joinTitle(titleName) values('Teacher'),('Master');
insert into joinPerson(pName, titleId) values('牛亮亮', 1),('苏坤', 2),('杨中科', NULL);

select * from joinPerson;
select * from joinTitle;

select pName, titleName from joinPerson cross join joinTitle;
-- 如果两章表中有重名的字段,就会出问题,就需要给表加别名
select t1.pName, t2.titleName from joinPerson as t1 cross join joinTitle as t2;

-- 内连接
select
*
from
joinPerson as t1
inner join
joinTitle as t2
on t1.titleId = t2.titleId;

-- 左外连接
select
*
from
joinPerson as t1
left join
joinTitle as t2
on t1.titleId = t2.titleId;

-- 右外连接
insert into joinTitle(titleName) values('班主任');

select
*
from
joinPerson as t1
right join
joinTitle as t2
on t1.titleId = t2.titleId;

-- 全连接
select
*
from
joinPerson as t1
full join
joinTitle as t2
on t1.titleId = t2.titleId;

-- 表表达式
-- 就是通过表与表的运算,得到一个结果集作为from后面的数据源
-- 1、派生表 返回结果集的子查询
-- 语法: select ... from (select 查询) as 别名;
-- 注意: 不能使用游标
-- 2、公用表表达式CTE
-- 3、视图
-- 4、内联表值函数

-- 查询学生信息
select * from
TestDataBase..Student as t1
inner join
TestDataBase..Course as t2
on t1.classId = t2.classId
inner join
TestDataBase..Score as t3
on t1.stuId = t3.stuId
where
stuName = '濮阳语儿';


select * from
(
select
t1.stuId
, t1.stuName
, case t1.stuSex when 'f' then '女' else '男' end as stuSex
篮球世界杯在哪买球,, datediff(year, t1.stuBirthdate, GETDATE()) as stuAge
, t1.stuEmail
, t1.stuAddress
, t1.stuPhone
, t2.className
, t3.testBase
, t3.testBeyond
, t3.testPro
from
TestDataBase..Student as t1
inner join
TestDataBase..Course as t2
on t1.classId = t2.classId
inner join
TestDataBase..Score as t3
on t1.stuId = t3.stuId
) as t
where t.stuName = '濮阳语儿';


本文由美洲杯在哪买球发布于计算机教程,转载请注明出处:SQL Server高级内容之子查询和表链接概述及使用

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。