sql语句查询两个表怎么弄
SQL语句多表查询
SQL语句多表查询：
问题及描述：
--1.学生表
Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号--3.教师表
Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名
--4.成绩表
SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数
*/
--创建测试数据
create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values('01' , N'；赵雷' , '1990-01-01' , N'；男') insert into Student values('02' , N'；钱电' , '1990-12-21' , N'；男') insert into Student values('03' , N'；孙风' , '1990-05-20' , N'；男') insert into Student values('04' , N'；李云' , '1990-08-06' , N'；男') insert into Student values('05' , N'；周梅' , '1991-12-01' , N'；女') insert into Student values('06' , N'；吴兰' , '1992-03-01' , N'；女') insert into Student values('07' , N'；郑竹' , '1989-07-01' , N'；女') insert into Student values('08' , N'；王菊' , '1990-01-20' , N'；女') create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10)) insert into Course values('01' , N'；语文' , '02')
insert into Course values('02' , N'；数学' , '01')
insert into Course values('03' , N'；英语' , '03')
create table Teacher(T# varchar(10),Tname nvarchar(10))
insert into Teacher values('01' , N'；张三')
insert into Teacher values('02' , N'；李四')
insert into Teacher values('03' , N'；王五') create table SC(S# varchar(10),C# varchar(10),score decimal(18,1)) insert into SC values('01' , '01' , 80)
insert into SC values('01' , '02' , 90)
insert into SC values('01' , '03' , 99)
insert into SC values('02' , '01' , 70)
insert into SC values('02' , '02' , 60)
insert into SC values('02' , '03' , 80)
insert into SC values('03' , '01' , 80)
insert into SC values('03' , '02' , 80)
insert into SC values('03' , '03' , 80)
insert into SC values('04' , '01' , 50)
insert into SC values('04' , '02' , 30)
insert into SC values('04' , '03' , 20)
insert into SC values('05' , '01' , 76)
insert into SC values('05' , '02' , 87)
insert into SC values('06' , '01' , 31)
insert into SC values('06' , '03' , 34)
insert into SC values('07' , '02' , 89)
insert into SC values('07' , '03' , 98)
go
--1、查询01课程比02课程成绩高的学生的信息及课程分数
--1.1、查询同时存在01课程和02课程的情况
select a.* , b.score [课程'01'；的分数],c.score [课程'02'；的分数] from Student a , SC b , SC c
Student a left join SC b on a.S# = b.S# and b.C# = '01'
left join SC c on a.S# = c.S# and c.C# = '02'
Student a , SC b , SC c
Student a left join SC b on a.S# = b.S# and b.C# = '01'
left join SC c on a.S# = c.S# and c.C# = '02'
e , cast(avg(b.score) as decimal(18,2)) avg_score from Student a , sc b e
having cast(avg(b.score) as decimal(18,2)) >= 60
order by a.S#
--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
--4.1、查询在sc表存在成绩的学生信息的SQL语句。
select a.S# , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score from Student a , sc b
e
having cast(avg(b.score) as decimal(18,2)) < 60
order by a.S#
--4.2、查询在sc表中不存在成绩的学生信息的SQL语句。
select a.S# , a.Sname , isnull(cast(avg(b.score) as decimal(18,2)),0) avg_score from Student a left join sc b
on a.S# = b.S#
group by a.S# , a.Sname
having isnull(cast(avg(b.score) as decimal(18,2)),0) < 60
order by a.S#
--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
--5.1、查询所有有成绩的SQL。
select a.S# [学生编号], a.Sname [学生姓名], count(b.C#) 选课总数, sum(score) [所有课程的总成绩]
from Student a , SC b
e
order by a.S#
--5.2、查询所有(包括有成绩和无成绩)的SQL。
select a.S# [学生编号], a.Sname [学生姓名], count(b.C#) 选课总数, sum(score) [所有课程的总成绩]
from Student a left join SC b
on a.S# = b.S#
group by a.S#,a.Sname
order by a.S#
-
-6、查询李姓老师的数量
--方法1
select count(Tname) [李姓老师的数量] from Teacher e like N'；李%'
--方法2
select count(Tname) [李姓老师的数量] from Teacher e,1) = N'；李'
/*
李姓老师的数量
-----------
1
*/
--7、查询学过张三老师授课的同学的信息
select distinct Student.* from Student , SC , Course , Teacher
e = N'；张三'
order by Student.S#
--8、查询没学过张三老师授课的同学的信息
select m.* from Student m SC , Course , Teacher e = N'；张三') order by m.S#
--9、查询学过编号为01并且也学过编号为02的课程的同学的信息
--方法1
select Student.* from Student , SC SC SC_2 ary key identity(1,1), name varchar(50)
)
create table grade(
id int primary key identity(1,1),
score money
)
alter table grade add sid int
select * from grade
alter table grade add constraintfk_sid foreign key(sid) references student(id)
insert into student values('；哈哈')
insert into student values('；喜喜')
insert into student values('；呵呵')
insert into student values('；郁闷')
insert into student values('；小强')
insert into student values('；小马')
insert into grade values(90,1)
insert into grade values(80,2)
insert into grade values(70,null)
select * from student
select * from grade
内连接查询方式有两种:
1.select s.*,g.* from student s ,grade g student s inner join grade g on s.id=g.sid;
我们看到我们主表没有主表没有匹配信息的时候其他的记录没有显示
下面我们来看外连接：主要以从表信息为主从表没有主表记录则显示为null
select s.*,g.* from student s left join grade g on s.id=g.sid
select s.*,g.* from student s right join grade g on s.id=g.sid
还可以是
select s.*,g.* from student s left outer join grade g on s.id=g.sid
select s.*,g.* from student s right outer join grade g on s.id=g.sid
看他是左还是右
sql语句查询不包含下面来看看完整连接：将两表的左和右基础数据全部查询出来
当一个表没有另一个表的记录的时候则为nul 反之右表也是一样select s.* ,g.* from student s full join grade g on s.id=g.sid
select s.* ,g.* from student s full outer join grade g on s.id=g.sid
交叉连接查询：先返回左表所有行，左表行在与右表行一一组合，等于两个表相乘
select s.*,g.* from student s cross join grade g
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论