龘爨龗,北京网站建设公司 蓝纤科技,中国教育风险预控网
废话不多说了,直接给大家贴代码了,具体代码如下所示:
-- 交叉连接产生笛卡尔值 (x*y) select * from student cross join dbo.classinfo --另外一种写法 select * from student , classinfo -- 内连接 (inner 可以省略) select * from student join dbo.classinfo on dbo.student.class = dbo.classinfo.id; -- inner join select * from student inner join dbo.classinfo on dbo.student.class = dbo.classinfo.id; -- on 条件,通常是 主外键,但是不限于主外键 -- on 条件,允许有多个,而且可能是针对某个表的 select * from student inner join dbo.classinfo on dbo.student.class = dbo.classinfo.id and dbo.student.class in (1,2) -- 针对student表增加查询条件 -- 不等于 (笛卡尔值减去 相等的值) select * from student inner join dbo.classinfo on dbo.student.class <> dbo.classinfo.id; --自连接 从class表中查询到class所在的系 select t1.* ,t2.classname from dbo.classinfo as t1 inner join dbo.classinfo as t2 on t1.pid=t2.id -- left join select * from student left join dbo.classinfo on dbo.student.class = dbo.classinfo.id; -- right join select * from student right join dbo.classinfo on dbo.student.class = dbo.classinfo.id; -- 全外连接 select * from student full outer join dbo.classinfo on dbo.student.class = dbo.classinfo.id; -- 外连接和内连接的区别是: -- 内连接,on条件不符合的话,会过滤掉数据 -- 外连接,以保留表为主,on条件成立显示数据,否则显示null --union 纵向连接 select stuid,stuname,stuenname,stuage,stubirthday from student where stuid<=2 union select stuid,stuname,stuenname,stuage,stubirthday from dbo.student where stuid>2 --union 去重复 select stusex from student where stuid<=2 union select stusex from dbo.student where stuid>2 --union 显示全部 select stusex from student where stuid<=2 union all select stusex from dbo.student where stuid>2 --except 差集,排除 select stuid,stuname,stuenname,stuage,stubirthday from student except select stuid,stuname,stuenname,stuage,stubirthday from dbo.student where stuid<=2 --intersect 交集 select stuid,stuname,stuenname,stuage,stubirthday from student where stuid>=2 except select stuid,stuname,stuenname,stuage,stubirthday from dbo.student where stuid<=3
以上所述是小编给大家介绍的sql server 2012 多表连接查询功能实例代码,希望对大家有所帮助
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论