第六章 表的联合查询

一：表的联合

       执行外部联合

       执行内部联合

       执行左连接

       执行右连接

       执行等值连接

       执行全连接

 

二：语句   

use test
create table student(
	id int identity primary key,
	username nvarchar(10),
	age int,
	address nvarchar(20)
)
insert into student select '张三',21,'汉口'
insert into student select '李四',22,'武昌'
insert into student select '王五',23,'武昌'
insert into student select '赵六',24,'汉口'
insert into student select '林奇',25,'武昌'
insert into student select '小明',26,'汉口'
insert into student values('小王',20,'武昌')

create table mark(
	id int identity primary key,
	sname nvarchar(10),
	htmlMark numeric(4,1),
	javaMark numeric(4,1),
	networkMark numeric(4,1)
)
insert into mark values('张三',65.5,85,98)
insert into mark select'李四',75.5,85.5,99
insert into mark select'王五',88,85,100
insert into mark select'赵六',98,99.5,65
insert into mark select'林奇',75,86,85.5
insert into mark select'小明',98,95,96
insert into mark select'小红',99,86,100
drop table student
select * from student 
select * from mark
delete from mark

--两张表的连接
select * from student,mark
select s.id,s.username,s.age,s.address,m.htmlMark,m.javaMark, m.networkMark from student as s,mark as m
select s.id,s.username,s.age,s.address,m.htmlMark,m.javaMark,m.networkMark from student s,mark m where s.username=m.sname

--等值连接
select s.id,s.username,s.age,s.address,m.htmlMark,m.javaMark,m.networkMark from student s inner join mark m on s.username = m.sname

--左连接
select s.id,s.username,s.age,s.address,m.htmlMark,m.javaMark,m.networkMark from student s left join mark m on s.username = m.sname

--右连接
select s.id,s.username,s.age,s.address,m.htmlMark,m.javaMark,m.networkMark from student s right join mark m on s.username = m.sname

--全连接
select s.id,s.username,s.age,s.address,m.htmlMark,m.javaMark,m.networkMark from student s full join mark m on s.username = m.sname

--top
--取出前两条记录
select top 2 * from student
select top 2 * from student where(id not in(select top(2*(3-1))id from student))