创建测试表MyStudentInfo CREATE table MyStudentInfo ( Id int not null primary key, Name varcha
CREATE table MyStudentInfo
(
Id int not null primary key,
Name varchar(16),
Age int,
Gender varchar(2),
Phone varchar(16),
Address varchar(50),
GradeId int
)
INSERT INTO MyStudentInfo
SELECT 1,'张三',20,'1','15801258912','上海',1 UNION
SELECT 2,'李四',22,'1','12345678901','北京',1 UNioN
SELECT 3,'王五',16,'1','13976891234','天津',2 UNION
SELECT 4,'赵六',19,'1','18676891234','重庆',3 UNION
SELECT 5,'小红',21,'2','17776891234','广州',4 UNION
SELECT 6,'小王',25,'2','13176891234','深圳',5 UNION
SELECT 7,'小刘',18,'2','13374591234','南京',6 UNION
SELECT 8,'小张',16,'1','13974596734','长沙',6 UNION
SELECT 9,'小罗',27,'1','13175122786','武汉',7 UNION
SELECT 10,'小袁',21,'2','17715872346','石家庄',8
CREATE table GradeInfo
(
Id int not null primary key,
GradeName varchar(16)
)
联合插入多条数据
INSERT INTO GradeInfo
SELECT 1,'.net' UNION
SELECT 2,'Android' UNION
SELECT 3,'PHP' UNION
SELECT 4,'UI' UNION
SELECT 5,'HTML5' UNION
SELECT 6,'JAVA' UNION
SELECT 9,'hadoop' UNION
SELECT 10,'大数据'
内链接是从结果表中删除其他被链接表中没有匹配的所有行,所以我们说内链接可能丢失数据
SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeName
FROM MyStudentInfo s INNER JOIN GradeInfo g ON s.GradeId=g.Id
左外联接,关键字LEFT JOIN
左外联接,结果集中包括左表中的所有行,如果左表的某一行在右表中没有匹配行,则在关联的结果集行中,
来自右表的所有选择列表均为NULL
SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeName
FROM MyStudentInfo s LEFT JOIN GradeInfo g ON s.GradeId=g.Id
GIGHT JOIN进行联接
是左外联接的反向联接,将返回右表的所有行,如果右表的某一行在左表中没有匹配行,则将为左表返回NULL
SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeName
FROM MyStudentInfo s RIGHT JOIN GradeInfo g ON s.GradeId=g.Id
关键字是FULL JOIN
完整外联接使用了FULL JOIN进行联接,当某一行在另一表中没有匹配行,另一表的选择列表将返回NULL
SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeName
FROM MyStudentInfo s FULL JOIN GradeInfo g ON s.GradeId=g.Id
CORSS JOIN 没有where 也没有on,返回行数是两表行数的乘积
SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeName
FROM MyStudentInfo s CROSS JOIN GradeInfo g
UNION必须遵循
SELECT Age,Name FROM MyStudentInfo
UNION
SELECT Id,GradeName FROM GradeInfo
ORDER BY Age DESC
SELECT Age,Name,Address FROM MyStudentInfo
UNION ALL
SELECT Id,GradeName,NULL FROM GradeInfo
ORDER BY Age DESC
到此这篇关于SQL Server实现连接与合并查询的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持编程网。
--结束END--
本文标题: SQLSERVER实现连接与合并查询
本文链接: https://www.lsjlt.com/news/140350.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-03-01
2024-03-01
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0