这里有新鲜出炉的 SQL Server 教程,程序狗速度看过来!
SQL 是英文 Structured Query Language 的缩写,意思为结构化查询语言。SQL 语言的主要功能就是同各种数据库建立联系,进行沟通。按照 ANSI(美国国家标准协会) 的规定,SQL 被作为关系型数据库管理系统的标准语言。
SQL Server 即 Microsoft SQL Server 。
这篇文章主要介绍了 SQL Server 2012 多表连接查询功能实例代码, 需要的朋友可以参考下
废话不多说了,直接给大家贴代码了,具体代码如下所示:
- -- 交叉连接产生笛卡尔值 (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 多表连接查询功能实例代码,希望对大家有所帮助,如果大家有任何疑问欢迎给我留言,小编会及时回复大家的!
来源: http://www.phperz.com/article/17/0819/339168.html